Location: PHPKode > projects > MembersGear > membersgear/admin/admin_search_user.php
<?php
  require_once "../config/config.php";
  require_once  INCLUDE_DIR."/phpmailer/Functions.php";
  require_once INCLUDE_DIR."/users_class.php"; 
  if(isset($_GET['action']))
   $_action = $_GET['action'];                                                                                        
 else
  $_action="";
   
  $_action = strtoupper($_action);                                                                                   
  $users=new users_class();
         if($users->IsAdminhavePermission($_SESSION[SESSION_PREFIX.'Admin_UserID'],'Can search users')==false)
        {
        Redirect('admin_nopermission.php',true);  
          
        }
         
        $lssearchmanageuser=true;
        if($users->IsAdminhavePermission($_SESSION[SESSION_PREFIX.'Admin_UserID'],'Can browse user profiles')==false)
        {
        //Redirect('admin_nopermission.php',true);  
         $lssearchmanageuser=false;   
        }
        
        if(isset($_GET['letter']) and $_GET['letter'] != 'all')
              {
                    $lsSearchLetter = $_GET['letter'];
              }
              else
              {
                    $lsSearchLetter = '';   
              }
              
  switch($_action) {
    case 'SEARCH':
       
       if($_POST) 
          $_SESSION[SESSION_PREFIX.'search']=$_POST;

        $method="";
        if(isset($_POST['search_method1']))
         $method="method1";
        else if(isset($_POST['search_method2']))
          $method="method2"; 
        else if(isset($_POST['search_method3']))
        $method="method3";
       else
        $method="method1";

        
         if($method=="method1" or isset( $_SESSION[SESSION_PREFIX.'search']['method1']))
          {
             $lsSearchString=trim($_POST['User_search']);
             if(!isset($lsSearchString) or $lsSearchString=="")
                $lsSearchString=$_SESSION[SESSION_PREFIX.'search']['User_search'] ;
                
             $lsSearchType=$_POST['q_where'];
             
             
              if(!isset($lsSearchType) or $lsSearchType=="")
                  $lsSearchType=$_SESSION[SESSION_PREFIX.'search']['q_where'] ;
                  
              if($lsSearchType=="anywhere")
               {     
                   
                   
                   
                   /*$sql="select CheckToDelete,createon,productid,country,state,city,address1,address2,zipcode,payment,id,isapp,cipadd,mipadd,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,cast(GROUP_CONCAT( concat(ProductStatusCount,'-',products) separator '<br />') as char(500)) ProductMembershipStatus,
                      username,email,isapproved,islockedout,status,userfullname,isapproved,role,userpaymentsubscription,actionlink,AccessLog1,loginas
                      ,`LastLoginTime`
                      from
                      (
                         select  u.id as `userid`, concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`,
                         concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`,
                          productid,u.id as id,CONCAT(u.firstname, ' ',u.lastname) as userfullname,tlc.name as country,tls.name as state,phone_office,tlbc.name as billing_country,tlbs.name as billing_state,billing_city,billing_zip,billing_phone,billing_address,billing_name,u.modifiedipaddress as mipadd,phone_mobile,u.createdipaddress as cipadd,phone_main,phone_home,u.islockedout as lockout,u.city as city ,u.isapproved as isapp,zipcode,address1,address2,count(tpu.productmembershipstatusid) ProductStatusCount,
                          case when pmvp.value = 'Expire' then
                           concat('<span style=\"color:red;font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Active' then
                            concat('<span style=\"font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Pending' or pmvp.value ='Inactive' then pmvp.value end as  products,
                           u.username as username,
                           (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                           (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                           (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                            WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\"><font color=\"red\"><b>','Yes','</b></font></td></tr></table>')
                            WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                            ELSE '' END
                            ) as islockedout,
                           (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                           (SELECT replace(FORMAT(sum(amount),2),',','') as payment 
                          from ".TABLE_PREFIX."product_users   where   userid=tpu.userid  and productmembershipstatusid  in (SELECT mv.id  FROM
                            ".TABLE_PREFIX."master_values mv join ".TABLE_PREFIX."master m on m.id = mv.masterID where
                           m.Name = 'productmembershipstatus' and  mv.value!='Pending' 
                          )) as payment,
                            ur.roleid as role,
                            concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                            concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                            concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                            (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2) then
                              concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                            else
                                concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                            end) as `loginas`,IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'
                           from ".TABLE_PREFIX."users u
                            join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer') 
                            left join ".TABLE_PREFIX."product_users tpu on u.id=tpu.userid
                            left join ".TABLE_PREFIX."location tlc on tlc.id=ifnull(u.country,0)
                            left join ".TABLE_PREFIX."location tls on tls.id=ifnull(u.state,0)
                            left join ".TABLE_PREFIX."location tlbc on tlbc.id=ifnull(u.billing_country,0)
                            left join ".TABLE_PREFIX."location tlbs on tlbs.id=ifnull(u.billing_state,0)
                            left join ".TABLE_PREFIX."master_values pmvp on pmvp.id = tpu.productmembershipstatusid
                            group by u.id,tpu.userid,productmembershipstatusid
                      ) a   where username like ('".$lsSearchLetter."%') 
                            and 
                            (userfullname like ('%".$lsSearchString."%') 
                             or  username like ('%".$lsSearchString."%')
                             or  email like ('%".$lsSearchString."%')
                             or  country like ('%".$lsSearchString."%')
                             or  state like ('%".$lsSearchString."%')
                             or  city like ('%".$lsSearchString."%')
                              or  address1 like ('%".$lsSearchString."%')
                               or address2 like ('%".$lsSearchString."%')
                                or zipcode like ('%".$lsSearchString."%')
                                 or isapproved like ('%".$lsSearchString."%')   
                                  or islockedout like ('%".$lsSearchString."%') 
                                   or phone_main like ('%".$lsSearchString."%')      
                                   or phone_home like ('%".$lsSearchString."%')
                                    or phone_office like ('%".$lsSearchString."%')
                                     or phone_mobile like ('%".$lsSearchString."%')
                                      or cipadd like ('%".$lsSearchString."%')
                                       or mipadd like ('%".$lsSearchString."%') 
                                        or billing_country like ('%".$lsSearchString."%')
                                            or billing_state like ('%".$lsSearchString."%')
                                              or billing_city like ('%".$lsSearchString."%')
                                                or billing_address like ('%".$lsSearchString."%')
                                                  or billing_name like ('%".$lsSearchString."%')
                                                  or billing_phone like ('%".$lsSearchString."%')
                                                  or billing_zip like ('%".$lsSearchString."%'))       
                            group by username,email,isapproved,islockedout,status,userfullname,payment,role
                            order by userfullname,1 desc";  */     
                            
                            
                        $sql="SELECT distinct u.id,concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`, username, concat(firstname, ' ',lastname) as `userfullname`,
                        (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                        country,state,city,address1,address2,zipcode,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,
                        concat('<div id=\"payment-',u.id,'\"','name=\"payment[]\"', '>','<center><img',' id=\"payment-load',u.id,'\"' ,'src=\"images/loading1.gif\"></center></div>') as payment,
                        concat('<div id=\"products-',u.id,'\">','<center><img',' id=\"products-load',u.id,'\"' , 'src=\"images/loading1.gif\"></center></div>') as ProductMembershipStatus,
                        concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                        (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                        (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                                                WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\"><font color=\"red\"><b>','Yes','</b></font></td></tr></table>')
                                                WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                                                ELSE '' END
                                                ) as islockedout,

                        (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,

                        concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                        concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                        concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                        (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2)  then
                        concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                        else
                        concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                        end) as `loginas`,
                        IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'

                    FROM  ".TABLE_PREFIX."users u 
                    join  ".TABLE_PREFIX."users_in_roles ur on ur.userid = u.id and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer')  
                    where username like ('".$lsSearchLetter."%')
                    and  
                    (concat(firstname, ' ',lastname) like ('%".$lsSearchString."%')
                    or  username like ('%".$lsSearchString."%')
                    or  email like ('%".$lsSearchString."%')
                    or  country like ('%".$lsSearchString."%')
                    or  state like ('%".$lsSearchString."%')
                    or  city like ('%".$lsSearchString."%')
                    or  address1 like ('%".$lsSearchString."%')
                    or address2 like ('%".$lsSearchString."%')
                    or zipcode like ('%".$lsSearchString."%')
                    or isapproved like ('%".$lsSearchString."%')   
                    or islockedout like ('%".$lsSearchString."%') 
                    or phone_main like ('%".$lsSearchString."%')      
                    or phone_home like ('%".$lsSearchString."%')
                    or phone_office like ('%".$lsSearchString."%')
                    or phone_mobile like ('%".$lsSearchString."%')
                    or billing_country like ('%".$lsSearchString."%')
                    or billing_state like ('%".$lsSearchString."%')
                    or billing_city like ('%".$lsSearchString."%')
                    or billing_address like ('%".$lsSearchString."%')
                    or billing_name like ('%".$lsSearchString."%')
                    or billing_phone like ('%".$lsSearchString."%')
                    or billing_zip like ('%".$lsSearchString."%'))";       
                            
                            
                            
                            
                     /* $sql="select   u.id as id,u.username as username,concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') as email,
                               (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table                           width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                               (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') 
                                WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                                WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                                ELSE '' END) as islockedout,
                               (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')                         ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                                CONCAT(firstname, ' ',lastname) as usrefullname,' ' as payment, ur.roleid as role  
                                from ".TABLE_PREFIX."users u 
                                join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer')                             where username like ('%".$lsSearchString."%') or  firstname like ('%".$lsSearchString."%') or  lastname like ('%".$lsSearchString."%') or    email like ('%".$lsSearchString."%') or  country like ('%".$lsSearchString."%') or  state like ('%".$lsSearchString."%') 
                                or  city like ('%".$lsSearchString."%') or  address1 like ('%".$lsSearchString."%') 
                                or address2 like ('%".$lsSearchString."%') 
                                or zipcode like ('%".$lsSearchString."%')
                                or isapproved like ('%".$lsSearchString."%') 
                                or islockedout like ('%".$lsSearchString."%') 
                                 or phone_main like ('%".$lsSearchString."%') 
                                  or phone_home like ('%".$lsSearchString."%') 
                                   or phone_office like ('%".$lsSearchString."%') 
                                    or phone_mobile like ('%".$lsSearchString."%') 
                                      or u.createdipaddress like ('%".$lsSearchString."%')
                                        or u.modifiedipaddress like ('%".$lsSearchString."%')
                                          or billing_country like ('%".$lsSearchString."%')
                                            or billing_state like ('%".$lsSearchString."%')
                                              or billing_city like ('%".$lsSearchString."%')
                                                or billing_address like ('%".$lsSearchString."%')
                                                  or billing_name like ('%".$lsSearchString."%')
                                                  or billing_phone like ('%".$lsSearchString."%')
                                                  or billing_zip like ('%".$lsSearchString."%')
                                                  and username like '".$lsSearchLetter."%'  
                                                  group by u.id ";  */  
                
               }
               else if($lsSearchType=="byUsername")
               {
                 
                     /* $sql="select CheckToDelete,createon,productid,payment,id,cast(GROUP_CONCAT( concat(ProductStatusCount,'-',products) separator '<br />') as char(500)) ProductMembershipStatus,
                      username,email,isapproved,islockedout,status,userfullname,role,userpaymentsubscription,actionlink,AccessLog1,loginas
                      ,`LastLoginTime`
                      from
                      (
                        select u.id as `userid`, concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`,
                         concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                         productid,u.id as id,CONCAT(u.firstname, ' ',u.lastname) as userfullname,count(tpu.productmembershipstatusid) ProductStatusCount,
                          case when pmvp.value = 'Expire' then
                           concat('<span style=\"color:red;font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Active' then
                            concat('<span style=\"font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Pending' or pmvp.value ='Inactive' then pmvp.value end as  products,
                                               u.username as username,
                           (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                           (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                           (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                            WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                            WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                            ELSE '' END
                            ) as islockedout,
                           (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                           (SELECT replace(FORMAT(sum(amount),2),',','') as payment 
                            from ".TABLE_PREFIX."product_users   where   userid=tpu.userid  and productmembershipstatusid  in (SELECT mv.id  FROM
                           ".TABLE_PREFIX."master_values mv join ".TABLE_PREFIX."master m on m.id = mv.masterID where
                           m.Name = 'productmembershipstatus' and  mv.value!='Pending'    
                           
                          )) as payment,
                            ur.roleid as role,
                            concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                            concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                            concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                            (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2) then
                              concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                            else
                                concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                            end) as `loginas`,IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'
                           from ".TABLE_PREFIX."users u
                            join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer') left join
                             ".TABLE_PREFIX."product_users tpu on u.id=tpu.userid
                            left join ".TABLE_PREFIX."master_values pmvp on pmvp.id = tpu.productmembershipstatusid
                            group by u.id,tpu.userid,productmembershipstatusid
                      ) a   where username like ('".$lsSearchLetter."%') and username like ('%".$lsSearchString."%')  group by username,email,isapproved,islockedout,status,userfullname,payment,role
                            order by userfullname,1 desc"; */
                            
                        $sql="SELECT distinct u.id,concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`, username, concat(firstname, ' ',lastname) as `userfullname`,
                        (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                        country,state,city,address1,address2,zipcode,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,
                        concat('<div id=\"payment-',u.id,'\"','name=\"payment[]\"', '>','<center><img',' id=\"payment-load',u.id,'\"' ,'src=\"images/loading1.gif\"></center></div>') as payment,
                        concat('<div id=\"products-',u.id,'\">','<center><img',' id=\"products-load',u.id,'\"' , 'src=\"images/loading1.gif\"></center></div>') as ProductMembershipStatus,
                        concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                        (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                        (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                                                WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\"><font color=\"red\"><b>','Yes','</b></font></td></tr></table>')
                                                WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                                                ELSE '' END
                                                ) as islockedout,

                        (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,

                        concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                        concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                        concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                        (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2)  then
                        concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                        else
                        concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                        end) as `loginas`,
                        IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'

                    FROM  ".TABLE_PREFIX."users u 
                    join  ".TABLE_PREFIX."users_in_roles ur on ur.userid = u.id and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer')  
                    where username like ('".$lsSearchLetter."%') and username like ('%".$lsSearchString."%')  ";        
                   
                   /* $sql="select   u.id as id,u.username as username,concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') as email,
                       (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table                           width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                       (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') 
                        WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                        WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                        ELSE '' END) as islockedout,
                       (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')                         ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                        CONCAT(firstname, ' ',lastname) as usrefullname,' ' as payment, ur.roleid as role  
                        from ".TABLE_PREFIX."users u 
                        join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer')                             where username like ('%".$lsSearchString."%') and username like '".$lsSearchLetter."%'  group by u.id";
                          */
               }
               else if($lsSearchType=="byFullname")
                {
                  /*$sql="select CheckToDelete,createon,productid,city,address1,address2,zipcode,payment,id,isapp,cipadd,mipadd,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,cast(GROUP_CONCAT( concat(ProductStatusCount,'-',products) separator '<br />') as char(500)) ProductMembershipStatus,
                      username,email,isapproved,islockedout,status,userfullname,isapproved,role,userpaymentsubscription,actionlink,AccessLog1,loginas
                       ,`LastLoginTime`
                      from
                      (
                         select  u.id as `userid`, concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`,
                         concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                         productid,u.id as id,CONCAT(u.firstname, ' ',u.lastname) as userfullname,phone_office,billing_country,billing_city,billing_zip,billing_phone,billing_address,billing_name,billing_state,u.modifiedipaddress as mipadd,phone_mobile,u.createdipaddress as cipadd,phone_main,phone_home,u.islockedout as lockout,u.city as city ,u.isapproved as isapp,zipcode,address1,address2,count(tpu.productmembershipstatusid) ProductStatusCount,
                          case when pmvp.value = 'Expire' then
                           concat('<span style=\"color:red;font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Active' then
                            concat('<span style=\"font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Pending' or pmvp.value ='Inactive' then pmvp.value end as  products,
                           u.username as username,
                           (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                           (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                           (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                            WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                            WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                            ELSE '' END
                            ) as islockedout,
                           (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                           (SELECT replace(FORMAT(sum(amount),2),',','') as payment 
                          from ".TABLE_PREFIX."product_users   where   userid=tpu.userid  and productmembershipstatusid  in (SELECT mv.id  FROM
                           ".TABLE_PREFIX."master_values mv join ".TABLE_PREFIX."master m on m.id = mv.masterID where
                           m.Name = 'productmembershipstatus' and  mv.value!='Pending' 
                          )) as payment,
                            ur.roleid as role,
                            concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                            concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                            concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                            (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2) then
                              concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                            else
                                concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                            end) as `loginas`,IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'
                           from ".TABLE_PREFIX."users u
                            join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer') left join
                             ".TABLE_PREFIX."product_users tpu on u.id=tpu.userid
                            left join ".TABLE_PREFIX."master_values pmvp on pmvp.id = tpu.productmembershipstatusid
                            group by u.id,tpu.userid,productmembershipstatusid
                      ) a   where username like ('".$lsSearchLetter."%') 
                            and 
                            (userfullname like ('%".$lsSearchString."%') )       
                            group by username,email,isapproved,islockedout,status,userfullname,payment,role
                            order by userfullname,1 desc";  */
                            
                            
                            $sql="SELECT distinct u.id,concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`, username, concat(firstname, ' ',lastname) as `userfullname`,
                        (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                        country,state,city,address1,address2,zipcode,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,
                        concat('<div id=\"payment-',u.id,'\"','name=\"payment[]\"', '>','<center><img',' id=\"payment-load',u.id,'\"' ,'src=\"images/loading1.gif\"></center></div>') as payment,
                        concat('<div id=\"products-',u.id,'\">','<center><img',' id=\"products-load',u.id,'\"' , 'src=\"images/loading1.gif\"></center></div>') as ProductMembershipStatus,
                        concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                        (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                        (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                                                WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\"><font color=\"red\"><b>','Yes','</b></font></td></tr></table>')
                                                WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                                                ELSE '' END
                                                ) as islockedout,

                        (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,

                        concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                        concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                        concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                        (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2)  then
                        concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                        else
                        concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                        end) as `loginas`,
                        IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'

                    FROM  ".TABLE_PREFIX."users u 
                    join  ".TABLE_PREFIX."users_in_roles ur on ur.userid = u.id and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer')  
                    where username like ('".$lsSearchLetter."%') and concat(firstname, ' ',lastname) like ('%".$lsSearchString."%')  ";    
               
                
                }
                else if($lsSearchType=="byemail")
                {
                   /*$sql="select CheckToDelete,createon,productid,city,address1,address2,zipcode,payment,id,isapp,cipadd,mipadd,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,cast(GROUP_CONCAT( concat(ProductStatusCount,'-',products) separator '<br />') as char(500)) ProductMembershipStatus,
                      username,email,isapproved,islockedout,status,userfullname,isapproved,role,userpaymentsubscription,actionlink,AccessLog1,loginas
                       ,`LastLoginTime`
                      from
                      (
                         select  u.id as `userid`, concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`,
                         concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                         productid,u.id as id,CONCAT(u.firstname, ' ',u.lastname) as userfullname,phone_office,billing_country,billing_city,billing_zip,billing_phone,billing_address,billing_name,billing_state,u.modifiedipaddress as mipadd,phone_mobile,u.createdipaddress as cipadd,phone_main,phone_home,u.islockedout as lockout,u.city as city ,u.isapproved as isapp,zipcode,address1,address2,count(tpu.productmembershipstatusid) ProductStatusCount,
                          case when pmvp.value = 'Expire' then
                           concat('<span style=\"color:red;font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Active' then
                            concat('<span style=\"font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Pending' or pmvp.value ='Inactive' then pmvp.value end as  products,
                           u.username as username,
                           (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                           (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                           (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                            WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                            WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                            ELSE '' END
                            ) as islockedout,
                           (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                           (SELECT replace(FORMAT(sum(amount),2),',','') as payment 
                          from ".TABLE_PREFIX."product_users   where   userid=tpu.userid  and productmembershipstatusid  in (SELECT mv.id  FROM
                           ".TABLE_PREFIX."master_values mv join ".TABLE_PREFIX."master m on m.id = mv.masterID where
                           m.Name = 'productmembershipstatus' and  mv.value!='Pending' 
                          )) as payment,
                            ur.roleid as role,
                            concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                            concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                            concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                            (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2) then
                              concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                            else
                                concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                            end) as `loginas`,IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'
                           from ".TABLE_PREFIX."users u
                            join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer') left join
                             ".TABLE_PREFIX."product_users tpu on u.id=tpu.userid
                            left join ".TABLE_PREFIX."master_values pmvp on pmvp.id = tpu.productmembershipstatusid
                            group by u.id,tpu.userid,productmembershipstatusid
                      ) a   where username like ('".$lsSearchLetter."%') 
                            and 
                            (email like ('%".$lsSearchString."%') )       
                            group by username,email,isapproved,islockedout,status,userfullname,payment,role
                            order by userfullname,1 desc";  */
                            
                                  $sql="SELECT distinct u.id,concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`, username, concat(firstname, ' ',lastname) as `userfullname`,
                        (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                        country,state,city,address1,address2,zipcode,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,
                        concat('<div id=\"payment-',u.id,'\"','name=\"payment[]\"', '>','<center><img',' id=\"payment-load',u.id,'\"' ,'src=\"images/loading1.gif\"></center></div>') as payment,
                        concat('<div id=\"products-',u.id,'\">','<center><img',' id=\"products-load',u.id,'\"' , 'src=\"images/loading1.gif\"></center></div>') as ProductMembershipStatus,
                        concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                        (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                        (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                                                WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\"><font color=\"red\"><b>','Yes','</b></font></td></tr></table>')
                                                WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                                                ELSE '' END
                                                ) as islockedout,

                        (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,

                        concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                        concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                        concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                        (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2)  then
                        concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                        else
                        concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                        end) as `loginas`,
                        IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'

                    FROM  ".TABLE_PREFIX."users u 
                    join  ".TABLE_PREFIX."users_in_roles ur on ur.userid = u.id and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer')  
                    where username like ('".$lsSearchLetter."%') and email like ('%".$lsSearchString."%')  ";      
               
                
                } 
                 
               
              
          }  
           if($method=="method2" or isset( $_SESSION[SESSION_PREFIX.'search']['method2'])) 
            {
                $curentTimeStamp= strtotime(date("Y/m/dH:i:s"));
                $prod_id       = $_POST['product_id'];
                if(!isset($_POST['product_id']))
                    $prod_id=$_SESSION[SESSION_PREFIX.'search']['product_id'];
                    
                 $subscriptioin = $_POST['subscription'];
                 
                 if(!isset($_POST['subscription']))
                      $subscriptioin=$_SESSION[SESSION_PREFIX.'search']['subscription'];
              
                   if($subscriptioin=="all")
                    {
                        
                          $sql="select CheckToDelete,createon,productid,city,address1,address2,zipcode,payment,id,isapp,cipadd,mipadd,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,cast(GROUP_CONCAT( concat(ProductStatusCount,'-',products) separator '<br />') as char(500)) ProductMembershipStatus,
                      username,email,isapproved,islockedout,status,userfullname,isapproved,role,userpaymentsubscription,actionlink,AccessLog1,loginas
                      ,`LastLoginTime`
                      from
                      (
                         select  u.id as `userid`, concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`,
                         concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                         productid,u.id as id,CONCAT(u.firstname, ' ',u.lastname) as userfullname,phone_office,billing_country,billing_city,billing_zip,billing_phone,billing_address,billing_name,billing_state,u.modifiedipaddress as mipadd,phone_mobile,u.createdipaddress as cipadd,phone_main,phone_home,u.islockedout as lockout,u.city as city ,u.isapproved as isapp,zipcode,address1,address2,count(tpu.productmembershipstatusid) ProductStatusCount,
                          case when pmvp.value = 'Expire' then
                           concat('<span style=\"color:red;font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Active' then
                            concat('<span style=\"font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Pending' or pmvp.value ='Inactive' then pmvp.value end as  products,
                           u.username as username,
                           (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                           (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                           (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                            WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                            WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                            ELSE '' END
                            ) as islockedout,
                           (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                           (SELECT replace(FORMAT(sum(amount),2),',','') as payment 
                          from ".TABLE_PREFIX."product_users   where   userid=tpu.userid  and productmembershipstatusid  in (SELECT mv.id  FROM
                           ".TABLE_PREFIX."master_values mv join ".TABLE_PREFIX."master m on m.id = mv.masterID where
                           m.Name = 'productmembershipstatus' and  mv.value!='Pending' 
                          )) as payment,
                            ur.roleid as role,
                            concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                            concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                            concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                            (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2) then
                              concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                            else
                                concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                            end) as `loginas`,IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'
                           from ".TABLE_PREFIX."users u
                            join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer') 
                            left join ".TABLE_PREFIX."product_users tpu on u.id=tpu.userid  
                            left join ".TABLE_PREFIX."master_values pmvp on pmvp.id = tpu.productmembershipstatusid
                            where productid=$prod_id
                            group by u.id,tpu.userid,productmembershipstatusid
                      ) a   where  username like ('".$lsSearchLetter."%')  
                            group by username,email,isapproved,islockedout,status,userfullname,payment,role
                            order by userfullname,1 desc";
                                                 
                               /*$sql="select   u.id as id,u.username as username,concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') as email,
                                   (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table                           width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                                   (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') 
                                    WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                                    WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                                    ELSE '' END) as islockedout,
                                   (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')                         ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                                    CONCAT(firstname, ' ',lastname) as usrefullname,' ' as payment, ur.roleid as role  
                                    from ".TABLE_PREFIX."users u 
                                    join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer') 
                                    join ".TABLE_PREFIX."product_users pro on u.id=pro.userid   and pro.productid=$prod_id and username like '".$lsSearchLetter."%' 
                                                                group by u.id";      */
                    }
                   else if($subscriptioin=="active")
                    {
                        
                        $payGearstatusValue="Active";
                        $masterclassObj=new master_class();
                        $masterId=$masterclassObj->getMasterId("productmembershipstatus");
                        $masterValuesId=$masterclassObj->getMasterValuesId($payGearstatusValue,$masterId); 
                         
                         $sql="select CheckToDelete,createon,productid,city,address1,address2,fromdate,uptodate,zipcode,payment,id,isapp,islifetime,cipadd,mipadd,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,cast(GROUP_CONCAT( concat(ProductStatusCount,'-',products) separator '<br />') as char(500)) ProductMembershipStatus,
                         username,email,isapproved,islockedout,status,userfullname,isapproved,role,userpaymentsubscription,actionlink,AccessLog1,loginas
                         ,`LastLoginTime`
                        from
                        (
                         select  u.id as `userid`, concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`,
                         concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                         productid,u.id as id,CONCAT(u.firstname, ' ',u.lastname) as userfullname,fromdate,islifetime,uptodate,phone_office,billing_country,billing_city,billing_zip,billing_phone,billing_address,billing_name,billing_state,u.modifiedipaddress as mipadd,phone_mobile,u.createdipaddress as cipadd,phone_main,phone_home,u.islockedout as lockout,u.city as city ,u.isapproved as isapp,zipcode,address1,address2,count(tpu.productmembershipstatusid) ProductStatusCount,
                          case when pmvp.value = 'Expire' then
                           concat('<span style=\"color:red;font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Active' then
                            concat('<span style=\"font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Pending' or pmvp.value ='Inactive' then pmvp.value end as  products,
                           u.username as username,
                           (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                           (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                           (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                            WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                            WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                            ELSE '' END
                            ) as islockedout,
                           (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                           (SELECT replace(FORMAT(sum(amount),2),',','') as payment 
                          from ".TABLE_PREFIX."product_users   where   userid=tpu.userid  and productmembershipstatusid  in (SELECT mv.id  FROM
                           ".TABLE_PREFIX."master_values mv join ".TABLE_PREFIX."master m on m.id = mv.masterID where
                           m.Name = 'productmembershipstatus' and  mv.value!='Pending' 
                          )) as payment,
                            ur.roleid as role,
                            concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                            concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                            concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                            (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2) then
                              concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                            else
                                concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                            end) as `loginas`,IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'
                           from ".TABLE_PREFIX."users u
                            join  ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer') left join
                             ".TABLE_PREFIX."product_users tpu on u.id=tpu.userid 
                            left join ".TABLE_PREFIX."master_values pmvp on pmvp.id = tpu.productmembershipstatusid
                            where   productid=$prod_id  and productmembershipstatusid=$masterValuesId
                            group by u.id,tpu.userid,productmembershipstatusid
                      ) a   where  username like ('".$lsSearchLetter."%') 
                            group by username,email,isapproved,islockedout,status,userfullname,payment,role
                            order by userfullname,1 desc";
                    
                            /* $sql="select   u.id as id,u.username as username,concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') as email,
                                   (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table                           width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                                   (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') 
                                    WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                                    WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                                    ELSE '' END) as islockedout,
                                   (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')                         ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                                    CONCAT(firstname, ' ',lastname) as usrefullname,' ' as payment, ur.roleid as role  
                                    from ".TABLE_PREFIX."users u 
                                    join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer') 
                                    join ".TABLE_PREFIX."product_users pro on u.id=pro.userid   and  (pro.uptodate>$curentTimeStamp or pro.islifetime=1) and pro.productid=$prod_id and username like '".$lsSearchLetter."%' 
                                                                group by u.id";    */
                        
                    
                    
                    
                    }
                   
                   else if($subscriptioin=="expired")
                    {
                        $payGearstatusValue="Expire";
                        $masterclassObj=new master_class();
                        $masterId=$masterclassObj->getMasterId("productmembershipstatus");
                        $masterValuesId=$masterclassObj->getMasterValuesId($payGearstatusValue,$masterId); 
                        
                            $sql="select CheckToDelete,createon,productid,city,address1,address2,fromdate,uptodate,zipcode,payment,id,isapp,islifetime,cipadd,mipadd,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,cast(GROUP_CONCAT( concat(ProductStatusCount,'-',products) separator '<br />') as char(500)) ProductMembershipStatus,
                      username,email,isapproved,islockedout,status,userfullname,isapproved,role,userpaymentsubscription,actionlink,AccessLog1,loginas
                       ,`LastLoginTime`
                      from
                      (
                         select  u.id as `userid`, concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`,
                         concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                         productid,u.id as id,CONCAT(u.firstname, ' ',u.lastname) as userfullname,fromdate,islifetime,uptodate,phone_office,billing_country,billing_city,billing_zip,billing_phone,billing_address,billing_name,billing_state,u.modifiedipaddress as mipadd,phone_mobile,u.createdipaddress as cipadd,phone_main,phone_home,u.islockedout as lockout,u.city as city ,u.isapproved as isapp,zipcode,address1,address2,count(tpu.productmembershipstatusid) ProductStatusCount,
                          case when pmvp.value = 'Expire' then
                           concat('<span style=\"color:red;font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Active' then
                            concat('<span style=\"font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Pending' or pmvp.value ='Inactive' then pmvp.value end as  products,
                           u.username as username,
                           (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                           (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                           (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                            WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                            WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                            ELSE '' END
                            ) as islockedout,
                           (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                           (SELECT replace(FORMAT(sum(amount),2),',','') as payment 
                          from ".TABLE_PREFIX."product_users   where   userid=tpu.userid  and productmembershipstatusid  in (SELECT mv.id  FROM
                           ".TABLE_PREFIX."master_values mv join ".TABLE_PREFIX."master m on m.id = mv.masterID where
                           m.Name = 'productmembershipstatus' and  mv.value!='Pending' 
                          )) as payment,
                            ur.roleid as role,
                            concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                            concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                            concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                            (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2) then
                              concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                            else
                                concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                            end) as `loginas`,IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'
                           from ".TABLE_PREFIX."users u
                            join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer') left join
                             ".TABLE_PREFIX."product_users tpu on u.id=tpu.userid 
                            left join ".TABLE_PREFIX."master_values pmvp on pmvp.id = tpu.productmembershipstatusid
                            where   productid=$prod_id  and productmembershipstatusid=$masterValuesId
                            group by u.id,tpu.userid,productmembershipstatusid
                      ) a   where  username like ('".$lsSearchLetter."%') 
                            group by username,email,isapproved,islockedout,status,userfullname,payment,role
                            order by userfullname,1 desc";   
                    
              
                    }
                                                             
                                                                
            }
            if($method=="method3" or isset( $_SESSION[SESSION_PREFIX.'search']['method3']))
            {
                  $searchType=$_POST['search_type'];
          
                  if(!isset($_POST['search_type']))
                        $searchType=$_SESSION[SESSION_PREFIX.'search']['search_type'];
                   
                
                $unixTimeStamp=strtotime($_POST['search_date']);
                if(!isset($_POST['search_date']))
                        $unixTimeStamp=strtotime($_SESSION[SESSION_PREFIX.'search']['search_date']);
                
                         
               //echo $_POST['search_date'];
               /*$sql="select   u.id as id,u.username as username,concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') as email,
                                   (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table                           width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                                   (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') 
                                    WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                                    WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                                    ELSE '' END) as islockedout,
                                   (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')                         ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                                    CONCAT(firstname, ' ',lastname) as usrefullname,' ' as payment, ur.roleid as role  
                                    from ".TABLE_PREFIX."users u 
                                    join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer') 
                                    join ".TABLE_PREFIX."product_users pro on u.id=pro.userid";  */
                                    
                                    
                                    
                                      /*$sql="select CheckToDelete,createon,productid,city,address1,address2,fromdate,uptodate,zipcode,payment,id,isapp,islifetime,cipadd,mipadd,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,cast(GROUP_CONCAT( concat(ProductStatusCount,'-',products) separator '<br />') as char(500)) ProductMembershipStatus,
                      username,email,isapproved,islockedout,status,userfullname,isapproved,role,userpaymentsubscription,actionlink,AccessLog1,loginas
                      ,`LastLoginTime`
                     from
                      (
                         select  u.id as `userid`, concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`,
                         concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                         productid,u.id as id,CONCAT(u.firstname, ' ',u.lastname) as userfullname,fromdate,islifetime,uptodate,phone_office,billing_country,billing_city,billing_zip,billing_phone,billing_address,billing_name,billing_state,u.modifiedipaddress as mipadd,phone_mobile,u.createdipaddress as cipadd,phone_main,phone_home,u.islockedout as lockout,u.city as city ,u.isapproved as isapp,zipcode,address1,address2,count(tpu.productmembershipstatusid) ProductStatusCount,
                          case when pmvp.value = 'Expire' then
                           concat('<span style=\"color:red;font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Active' then
                            concat('<span style=\"font-weight:bold\">',pmvp.value,'</span>')
                          when pmvp.value = 'Pending' or pmvp.value ='Inactive' then pmvp.value end as  products,
                           u.username as username,
                           (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                           (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                           (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                            WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>')
                            WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                            ELSE '' END
                            ) as islockedout,
                           (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,
                           (SELECT replace(FORMAT(sum(amount),2),',','') as payment 
                          from ".TABLE_PREFIX."product_users   where   userid=tpu.userid  and productmembershipstatusid  in (SELECT mv.id  FROM
                           ".TABLE_PREFIX."master_values mv join ".TABLE_PREFIX."master m on m.id = mv.masterID where
                           m.Name = 'productmembershipstatus' and  mv.value!='Pending' 
                          )) as payment,
                            ur.roleid as role,
                            concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                            concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                            concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                            (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2) then
                              concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                            else
                                concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                            end) as `loginas`,IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'
                           from ".TABLE_PREFIX."users u
                            join ".TABLE_PREFIX."users_in_roles ur on u.id=ur.userid and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer') left join
                              ".TABLE_PREFIX."product_users tpu on u.id=tpu.userid 
                            left join ".TABLE_PREFIX."master_values pmvp on pmvp.id = tpu.productmembershipstatusid where ";
                            
                           $str2= "group by u.id,tpu.userid,productmembershipstatusid
                      ) a   where  username like ('".$lsSearchLetter."%') 
                            group by username,email,isapproved,islockedout,status,userfullname,payment,role
                            order by userfullname,1 desc";   */
                            
              
                        
                        $sql="SELECT distinct u.id,concat('<input type=\"checkbox\" id=\"ckhDeleteItem\" name=\"chkDeleteItem[]\" onclick=\"return CheckAll(this.id)\" value=\"',u.id,'\" />') as `CheckToDelete`, username, concat(firstname, ' ',lastname) as `userfullname`,
                        (case when LENGTH(u.Email) >= 15 then concat('<a href=\"mailto:',u.Email,'\">',concat(SUBSTR(u.Email,1,15),'...'),'</a>') else concat('<a href=\"mailto:',u.Email,'\">',u.Email,'</a>') end) as email,
                        country,state,city,address1,address2,zipcode,phone_main,phone_office,billing_country,billing_zip,billing_state,billing_city,billing_phone,billing_name,billing_address,phone_home,phone_mobile,
                        concat('<div id=\"payment-',u.id,'\"','name=\"payment[]\"', '>','<center><img',' id=\"payment-load',u.id,'\"' ,'src=\"images/loading1.gif\"></center></div>') as payment,
                        concat('<div id=\"products-',u.id,'\">','<center><img',' id=\"products-load',u.id,'\"' , 'src=\"images/loading1.gif\"></center></div>') as ProductMembershipStatus,
                        concat('<span style=\"display:none;\">',from_unixtime(u.createdon),'</span>',date_format(from_unixtime(u.createdon),\"%d %b, %Y\")) as `createon`, 
                        (CASE WHEN  u.isapproved=1 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Yes','</td></tr></table>') else concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>') END) as isapproved,
                        (CASE u.islockedout WHEN 0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','No','</td></tr></table>')
                                                WHEN 1 THEN concat('<table width=\"100%\"><tr><td align=\"center\"><font color=\"red\"><b>','Yes','</b></font></td></tr></table>')
                                                WHEN 2 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Disable auto-lock','</td></tr></table>')
                                                ELSE '' END
                                                ) as islockedout,

                        (CASE WHEN u.isapproved=1 and u.islockedout=0 THEN concat('<table width=\"100%\"><tr><td align=\"center\">','Active','</td></tr></table>')ELSE concat('<table width=\"100%\"><tr><td align=\"center\">','Inactive','</td></tr></table>') END) as status,

                        concat('<a href=\"admin_user_subscription_v.php?userid=',u.id,'\"><img src=\"images/payment.gif\" title=\"User Payment Subscription\" alt=\"User Payment Subscription\"></a>') as `userpaymentsubscription`,
                        concat('<a href=\"admin_user_action.php?userid=',u.id,'\"><img src=\"images/action.gif\" title=\"Action\" alt=\"Action\"></a>') as `actionlink`,
                        concat('<a href=\"admin_user_log_v.php?userid=',u.id,'\"><img src=\"images/accesslog.gif\" title=\"Access Log\" alt=\"Access Log\"></a>') as `AccessLog1`,
                        (case when (u.isapproved=0 and islockedout=0) or  (u.isapproved=0 and islockedout=1) or (u.isapproved=1 and islockedout=1) or (u.isapproved=0 and islockedout=2)  then
                        concat('<img src=\"images/logindiable.gif\" title=\"Loginas\" alt=\"Loginas\">')
                        else
                        concat('<a href=\"../user_login.php?action=dologin&username=',username,'&useremail=',email,'\" target=\"_blank\"><img src=\"images/login.gif\" title=\"Loginas\" alt=\"Loginas\"></a>')
                        end) as `loginas`,
                        IFNULL((from_unixtime(`last_login_date`,'%M %d, %Y %h:%i:%S %p')),'Never') as 'LastLoginTime'

                        FROM  ".TABLE_PREFIX."users u 
                        join  ".TABLE_PREFIX."users_in_roles ur on ur.userid = u.id and ur.roleid =(select id from  ".TABLE_PREFIX."roles where name='customer')  
                        left join ".TABLE_PREFIX."product_users tpu on u.id=tpu.userid
                        where username like ('".$lsSearchLetter."%') and ";            
              
                                          
                       if($searchType=="begin_date_before")                     
                        {
                           $strCond=" from_unixtime(fromdate,\"%m/%d/%Y\") < from_unixtime($unixTimeStamp,\"%m/%d/%Y\")   ";
                          $sql=$sql.$strCond.$str2; 
                        }
                        if($searchType=="begin_date")                     
                        {
                           $strCond=" from_unixtime(fromdate,\"%m/%d/%Y\") = from_unixtime($unixTimeStamp,\"%m/%d/%Y\")   ";
                          $sql=$sql.$strCond; 
                        }
                         if($searchType=="begin_date_after")                     
                        {
                           $strCond="  from_unixtime(fromdate,\"%m/%d/%Y\") > from_unixtime($unixTimeStamp,\"%m/%d/%Y\") ";
                           $sql=$sql.$strCond;  
                        }
                        if($searchType=="expire_date_before")                     
                        {
                           $strCond="  from_unixtime(uptodate,\"%m/%d/%Y\") < from_unixtime($unixTimeStamp,\"%m/%d/%Y\")   and islifetime<>1 ";
                           $sql=$sql.$strCond.$str2;  
                        } 
                         if($searchType=="expire_date")                     
                        {
                           $strCond=" from_unixtime(uptodate,\"%m/%d/%Y\") = from_unixtime($unixTimeStamp,\"%m/%d/%Y\")  and islifetime<>1 ";
                          $sql=$sql.$strCond;   
                        }
                          if($searchType=="expire_date_after")                     
                        {
                           $strCond="  from_unixtime(uptodate,\"%m/%d/%Y\") > from_unixtime($unixTimeStamp,\"%m/%d/%Y\") and islifetime<>1 ";
                           $sql=$sql.$strCond;  
                        }  
                
               
            }     
              
           echo($smarty->fetch('admin_header.php'));
           /* Added By Asmita*/
           $quest="&";
            if(isset($_GET) && !empty($_GET))
             {
                 foreach($_GET as $keyname => $value) {
                      if(strtoupper($keyname)!='ACTION')
                         $quest.=$keyname."=".$value."&";
                    }     
             
             }
            echo "<form name=\"frmDeleteLog\" action=\"admin_users_m.php?action=delete_selected&from=searchPage$quest\" method=\"POST\">"; 
           echo "<div id=\"content\" class=\"setdiv\"> ";
             echo "
                    <script language=\"JavaScript\">
                        function CheckAll(elementID)
                        {
                            var CheckSelect = document.getElementById('chkSelectAll');
                            var LabelCheckSelect = document.getElementById('lblSelect');
                            var AllCheckBox = document.getElementsByName('chkDeleteItem[]');
                            //alert(CheckSelect.checked);
                            //alert(AllCheckBox.length);
                            
                            switch(elementID)
                            {
                                case 'chkSelectAll':
                                        if(CheckSelect.checked)
                                        {
                                             //alert(AllCheckBox.length);
                                             LabelCheckSelect.innerHTML = 'Uncheck All';
                                            for(i=0;i<AllCheckBox.length;i++)
                                            {
                                                AllCheckBox[i].checked = 'true';
                                            }
                                        }
                                        else
                                        {
                                            LabelCheckSelect.innerHTML = 'Check All';
                                            for(i=0;i<AllCheckBox.length;i++)
                                            {
                                                AllCheckBox[i].checked = '';
                                            }
                                        }
                                        break;
                                case 'ckhDeleteItem':
                                        var flag = 1;
                                        for(i=0;i<AllCheckBox.length;i++)
                                        {
                                            if(!AllCheckBox[i].checked)
                                            {
                                                  flag = 0;
                                            }
                                        }
                                        
                                        if(flag == 0)
                                        {
                                             LabelCheckSelect.innerHTML = 'Check All';
                                             CheckSelect.checked = '';
                                        }
                                        else
                                        {
                                             LabelCheckSelect.innerHTML = 'Uncheck All';
                                             CheckSelect.checked = 'true';
                                        }
                            }
            
                        }
                        
                        function ValidateCheckbox(str,str1)
                        {
                              var AllCheckBox = document.getElementsByName('chkDeleteItem[]');
                              //alert(str1);   
                              var flag = 0;  
                                for(i=0;i<AllCheckBox.length;i++)
                                {
                                        if(AllCheckBox[i].checked)
                                        {
                                                  flag = 1;
                                        }
                                }
                                
                                if(flag == 0 && str == 'selected')
                                {
                                    if(str1=='btnEmailSelected')
                                    {
                                        alert('Please select atleast one record to send a Email');
                                    }
                                    else if(str1=='btnDeleteSelected')
                                    {
                                        alert('Please select atleast one record to delete!'); 
                                    }
                                    return false;
                                }
                                else
                                {   if(str1=='btnDeleteSelected')
                                    {
                                        return confirm('Are you sure to delete '+str+' users?');
                                    }
                                    else
                                    {
                                        return true;
                                    }
                                    //return true;
                                } 
                                
                        }
                        function Formsubmit()
                        {              
                            document.frmDeleteLog.action='admin_send_email.php?action=senduser'; 
                            document.frmDeleteLog.submit();
                        }
                    </script>
                
                 ";
          
           
           
           
           
           
           /****End***********/
           
           
           
           //echo "<div id=\"content\" class=\"setdiv\"> ";
           if($message->IsMessageExists())
           {    
                $lsmsg=$message->GetMessage();
                echo "<div class=\"succmsg\" style=\"width:400px\">$lsmsg</div>";
           }
            echo "<div id=\"full-contentdiv\">"; 
           
            echo "<div class=\"righttop\">";
            echo "<h2 align=\"center\">";
            echo "Search Result";
            echo "</h2>";
            echo "</div>";
            echo "<div class=\"whole-page clear\">";
            
             echo "<div style=\"text-align:center;margin:2px;\">";
            echo "<div style=\"margin-left:3px;margin-top:3px;text-align:left;font-weight:bold !important;float:left;width:120px;\">";
           // if($lsmanageemaillog==true)
            {
               echo "<input type=\"checkbox\" class=\"chkcss\" id=\"chkSelectAll\" name=\"chkSelectAll\" onclick=\"return CheckAll(this.id)\"> <label id=\"lblSelect\" for=\"chkSelectAll\">Check All</label>";              
            }
              echo "</div>";
           
            /****************************************************************************
            * Updated By Jayesh : for display character based paging
            *****************************************************************************/
            //echo "<div style=\"float: left;margin-left:110px\">";  
            echo "<div class=\"divABCDPaging\">";
            echo PrintABCDPaging();
            echo "</div>";
            echo "</div>";
            
          
            
              
              $db->execute($sql);
              $totalrowcount=$db->rowCount();

                # creates a new phpSortColumn object for ID´s 
                //$col_0 = new phpSortColumn("id", "ID");
                
                #creates a new phpSortcolunm objects for name
                 
                 $col_0 = new phpSortColumn("CheckToDelete", ""); 
                $col_1 = new phpSortColumn("username", "Login");
                
                 $col_2 = new phpSortColumn("userfullname", "Name"); 
                
                # creates a new phpSortColumn object for Tile
                $col_3 = new phpSortColumn("email", "Email");

                # creates a new phpSortColumn object for Field Type
                $col_4 = new phpSortColumn("payment", "Payment(\$)");

                # creates a new phpSortColumn object for Desplay Type   
                $col_5 = new phpSortColumn("ProductMembershipStatus", "Membership");
                
                
                $col_6=new  phpSortColumn("createon", "Member Since"); 
                
                $col_7 = new phpSortColumn("isapproved", "Approved");  
                
                $col_8 = new phpSortColumn("islockedout", "Lockedout");
                
                $col_9 = new phpSortColumn("LastLoginTime", "Last Login");
                
                $col_10 = new phpSortColumn("userpaymentsubscription","");
                 
                $col_11 = new phpSortColumn("actionlink", "");
                  
                $col_12 = new phpSortColumn("AccessLog1", "");
                   
                $col_13 = new phpSortColumn("loginas", "");
                
                  

                # creates a new phpSortColumn object for validation function   
               
                 
                # add the columns to an array 
                $columns = array(0=>$col_0, 1=>$col_1, 2=>$col_2, 3=>$col_3,4=>$col_4,5=>$col_5,6=>$col_6,7=>$col_7,8=>$col_8,9=>$col_9,10=>$col_10,11=>$col_11,12=>$col_12,13=>$col_13);
                
                # create a new phpSortable object with the abstracted db object
                # and the array of column object
                $sortable = new phpSortable($db, $columns);

                # define the table format: cellpadding, cellspacing, border
                $sortable->setTableFormat ("2", "2", "0");

                # define the table title
                $sortable->tableTitle = "";

                # Encrypt Edit and Delete Link
                $sortable->encryptlink = false;
                
                # define the link for edit of entries
                $sortable->editLink = "admin_users_m.php?action=edit";

                # open the edit page in a new window
                //$sortable->editTarget = "_blank";

                # window format for the edit page
               // $sortable->editWindowFormat = "'width=300,height=400'";

                # define the link for delete of entries
                

                # define the link for add new entries
                $sortable->addLink = "admin_users_m.php";
                
                # set visibility of add,edit,delete link of the grid
                if($users->IsAdminhavePermission($_SESSION[SESSION_PREFIX.'Admin_UserID'],'Can add users')==false)
                {
                    $sortable->showaddLink=false; 
                }
                if($users->IsAdminhavePermission($_SESSION[SESSION_PREFIX.'Admin_UserID'],'Can edit users')==false)
                {
                    $sortable->showeditLink=false; 
                    
                }
                if($lssearchmanageuser==false)
                {
                    $sortable->showdeleteLink=false;  
                
                }

                # define the hint to be displyed over the add button
                $sortable->addText = "Add New User";

                # define the key name to be attached to links
                $sortable->urlKeyName = "id";

                # define the column name of to be attached to links as key value
                $sortable->tableKeyName = "id";

                # define the prompt message to be diplayed on clicking the edit button            
                //$sortable->editPromptMsg = "Are you sure you want to edit this affiliate?";

                # define the prompt message to be diplayed on clicking the delete button
                $sortable->deletePromptMsg = "Are you sure you want to delete this User?";

                # define the message to be diplayed if there are no results found
                $sortable->emptyMsg = "No User found.";

                #define the deafult row number to be displayed
                $sortable->defaultRowsNum = "10";

                $quest="&";
                if(isset($_GET) && !empty($_GET))
                 {
                     foreach($_GET as $keyname => $value) {
                          $quest.=$keyname."=".$value."&";
                        }     
                 
                 }
                $sortable->deleteLink = "admin_users_m.php?$quest&action=delete&from=searchPage";  
                #define the path for the css file
                $sortable->cssFilePath = ADMIN_CSS_DIR."/phpSortable.css";

                #define the path for of the images directory
                $sortable->imgFilePath = ADMIN_IMAGE_DIR."/phpgrid/";

                # print the table            
                                $sortable->printTable();
                if($totalrowcount>0)
                {
                   if($lssearchmanageuser==true)
                   {  
                     echo '<input style="margin:5px;" type="submit" value="Delete Selected Users" name="btnDeleteSelected" id="btnDeleteSelected" class="btncss" onclick="return ValidateCheckbox(\'selected\',this.id);"/>';
                   }
                    echo '<input style="margin:5px;" type="submit" value="Send Email to Selected Users " name="btnEmailSelected" id="btnEmailSelected" class="btncss" onclick="var result=ValidateCheckbox(\'selected\',this.id); if(result==true){Formsubmit();}else{return false;}"/>';
                } 
               if($totalrowcount<=0 )
               {
                 echo "<script>";
                 echo "document.getElementById('chkSelectAll').style.display='none';";
                echo "document.getElementById('lblSelect').style.display='none';";
                 //echo "document.getElementById('btnDeleteSelected').style.display='none';";
                 //echo "document.getElementById('btnDeleteAll').style.display='none';";
                // echo "document.getElementById('btnEmailSelected').style.display='none';";  
                 echo "</script>";
               } 
              
               
    
              echo "<input  style=\"margin:5px;\" type=\"button\" name=\"back\" value=\"Click here to Search again\" class=\"btncss\" onclick=\"javascript:location.href='./admin_search_user.php';\">";  
            
              echo "</div>";
              echo "</div>";
              echo "</div>";
                
              //For display footer.php
              echo($smarty->fetch('admin_footer.php'));
       
            if($lsSearchType=="anywhere" or $lsSearchType=="byUsername" or $lsSearchType=="byemail" or $lsSearchType=="byFullname" or($method=="method3" or isset( $_SESSION[SESSION_PREFIX.'search']['method3']))){
    ?>        
             <script language="javascript" type="text/javascript">     
                 var $j = jQuery.noConflict();  
                 var dataString="userId=("      
                 $j(document).ready(function(){  
                          var allrecs=$j("div[name='payment[]']") ;
                          var AllrecLen=allrecs.length;
                          AllrecLen= Number(AllrecLen)-1;
                           
                          allrecs.each(function (i,domEle) {   
                                   var htmlId=domEle.id;
                                   splitRec=htmlId.split('-');
                                   var userId=splitRec[1];
                                   dataString=dataString + userId ;
                                   if(AllrecLen!=i)
                                      dataString=dataString + ",";  
                                   
                             });  
                            
                             dataString= dataString + ")"; 
                               
                               $j.ajax({
                                       url: "<?php echo WEB_URL; ?>"+"/admin/admin_user_grid_get_payment_info.php",
                                       datatype: 'html',
                                       type: 'POST',
                                       data: dataString,
                                       success: function(msg){
                                        if(msg!=''){
                                               
                                               dataarr = msg.split('|||');  
                                                $j.each(
                                                    dataarr,
                                                     function( intIndex, objValue ){
                                                     
                                                      allrecs.each(function (i,domEle) {   
                                                               var htmlId=domEle.id;
                                                               splitRec=htmlId.split('-');
                                                               var userId=splitRec[1];
                                                               $j("#payment-load"+userId).hide();
                                                               $j("#products-load"+userId).hide();
                                                               
                                                         });  
                                                       var subArr=objValue.split('||');
                                                          if(subArr[0]!="")
                                                          {
                                                            $j("#payment-"+subArr[0]).html(subArr[1]);
                                                            $j("#products-"+subArr[0]).html(subArr[2]);
                                                          }
                                                          
                                                     }
                                                  )                     
                                  
                                         } 
                                       }
                                   });  
                           
                           
                         

                  
                      
                  
                  });   
                </script>
    <?php          
            }
            
        break; 
   default :
        require_once INCLUDE_DIR."/product_class.php";
        $product=new product_class();  
        $alProd=$product->GetUserProduct();
        $smarty->assign('products',$alProd);
        $smarty->display('admin_search_user.tpl'); 
  }   
?>
Return current item: MembersGear