<?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');
}
?>