#筛选数据
public function search_user(){
$assign = [];
$where['_string'] = ' 1=1 '; #必须，否则刚跳转到页面时无查询条件，会报错
$having = ' 1=1 '; #聚合函数条件
if($_POST){
$add_time = isset($_POST['add_time']) ? I('post.add_time') : '';
$golb = isset($_POST['golb']) ? I('post.golb') : '';
$sex = isset($_POST['sex']) ? I('post.sex') : '';
$phone = isset($_POST['phone']) ? I('post.phone') : '';
#储值余额
$single_balance = isset($_POST['single_balance']) ? I('post.single_balance') : '';
#手动输入搜索范围
$start_balance = isset($_POST['start_balance']) ? I('post.start_balance') : '';
$end_balance = isset($_POST['end_balance']) ? I('post.end_balance') : '';
$pay_time = isset($_POST['pay_time']) ? I('post.pay_time') : '';
#消费总额 sum(pay_order.total_prices)
$single_total_prices = isset($_POST['single_total_prices'])?I('post.single_total_prices'):'';
#手动输入搜索范围
$start_total_prices = isset($_POST['start_total_prices']) ? I('post.start_total_prices') : '';
$end_total_prices = isset($_POST['end_total_prices']) ? I('post.end_total_prices') : '';
#平均消费水平 avg(pay_order.total_prices)
$single_avg_consume = isset($_POST['single_avg_consume']) ? I('post.single_avg_consume') : '';
#手动输入搜索范围
$start_avg_consume = isset($_POST['start_avg_consume']) ? I('post.start_avg_consume') : '';
$end_avg_consume = isset($_POST['end_avg_consume']) ? I('post.end_avg_consume') : '';
$avg_shopping_count = isset($_POST['avg_shopping_count']) ? I('post.avg_shopping_count') : '';
}else{ #if($_GET){}
$add_time = isset($_GET['add_time']) ? I('get.add_time') : '';
$golb = isset($_GET['golb']) ? I('get.golb') : '';
$sex = isset($_GET['sex']) ? I('get.sex') : '';
$phone = isset($_GET['phone']) ? I('get.phone') : '';
#储值余额
$single_balance = isset($_GET['single_balance']) ? I('get.single_balance') : '';
#手动输入搜索范围
$start_balance = isset($_GET['start_balance']) ? I('get.start_balance') : '';
$end_balance = isset($_GET['end_balance']) ? I('get.end_balance') : '';
$pay_time = isset($_GET['pay_time']) ? I('get.pay_time') : '';
#消费总额
$single_total_prices = isset($_GET['single_total_prices'])?I('get.single_total_prices'):'';
#手动输入搜索范围
$start_total_prices = isset($_GET['start_total_prices']) ? I('get.start_total_prices') : '';
$end_total_prices = isset($_GET['end_total_prices']) ? I('get.end_total_prices') : '';
#平均消费水平
$single_avg_consume = isset($_GET['single_avg_consume']) ? I('get.single_avg_consume') : '';
#手动输入搜索范围
$start_avg_consume = isset($_GET['start_avg_consume']) ? I('get.start_avg_consume') : '';
$end_avg_consume = isset($_GET['end_avg_consume']) ? I('get.end_avg_consume') : '';
$avg_shopping_count = isset($_GET['avg_shopping_count']) ? I('get.avg_shopping_count') : '';
}
#注册时间
//$add_time = isset($_POST['add_time']) ? I('post.add_time') : I('get.add_time');
if(!empty($add_time)){
$assign['add_time'] = $add_time;
$add_time = explode('-', $add_time);
if(count($add_time)>=2){ #add_time 中包含有字符-
$where['_string'] .= ' and (u.addtime between '.strtotime('-'.$add_time[1]).' and '.strtotime('-'.$add_time[0]).')';
}else if(count($add_time)==1){ #add_time 中没有包含有字符- ,但可能有>3
$str = html_entity_decode($add_time[0]);
$where['_string'] .= ' and u.addtime'.substr($str,0,1).strtotime('-'.substr($str,1));
$assign['add_time'] = $add_time = html_entity_decode($add_time[0]);
}
}
#金币数量 user.golb
//$golb = isset($_POST['golb']) ? I('post.golb') : I('get.golb');
if(!empty($golb)){
$assign['golb'] = $golb;
$golb = explode('-', $golb);
if(count($golb)>=2){ #consume_time 中包含有字符-
$where['_string'] .= ' and (u.golb between '.$golb[0].' and '.$golb[1].')';
}else if(count($golb)==1){ #consume_time 中没有包含有字符-
$where['_string'] .= ' and u.golb'.html_entity_decode($golb[0]);
$assign['golb'] = $golb = html_entity_decode($golb[0]);
}
}
#会员性别 user.sex
//$sex = isset($_POST['sex']) ? I('post.sex') : I('get.sex');
if(!empty($sex)){
$assign['sex'] = $sex;
$where['_string'] .= " and u.sex='".$sex."'";
}
#电话号码 user.phone
//$phone = isset($_POST['phone']) ? I('post.phone') : I('get.phone');
if($phone!=''){
$assign['phone'] = $phone;
if($phone == 1){
$where['_string'] .= " and (u.phone != '0')"; #不为空
}else{
$where['_string'] .= " and u.phone = '0' "; #为空
}
}
#储值余额 count(vip_record.balance)
//$single_balance = isset($_POST['single_balance']) ? I('post.single_balance') : I('get.single_balance');
#手动输入搜索范围
//$start_balance = isset($_POST['start_balance']) ? I('post.start_balance') : I('get.start_balance');
//$end_balance = isset($_POST['end_balance']) ? I('post.end_balance') : I('get.end_balance');
if($single_balance!=''){
$assign['single_balance'] = html_entity_decode($single_balance); #有>0等情况
}
if($start_balance!=''){
$assign['start_balance'] = $start_balance;
}
if($end_balance!=''){
$assign['end_balance'] = $end_balance;
}
if($start_balance!='' || $end_balance!=''){
if($start_balance=='') $start_balance=0;
if($end_balance=='') $end_balance=0;
$where['_string'] .= ' and up.balance between '.($start_balance<=$end_balance?$start_balance:$end_balance).' and '.($end_balance>=$start_balance?$end_balance:$start_balance);
}else{
if($single_balance!=''){ #single_balance 中包含有字符-
$balance = explode('-', $single_balance);
if(count($balance)>=2){
$where['_string'] .= ' and up.balance between '.($balance[0]<=$balance[1]?$balance[0]:$balance[1]).' and '.($balance[1]>=$balance[0]?$balance[1]:$balance[0]);
}else{
$where['_string'] .= ' and up.balance'.html_entity_decode($balance[0]);
}
}
}
//分析的是当前商户下的已支付订单
//$having .= " and po.sid =".parent::$adminid." and po.pay_status = 1";
#到店分析 --- 在这个时间段内有消费的pay_order表 并且m!=0
//$pay_time = isset($_POST['pay_time']) ? I('post.pay_time') : I('get.pay_time');
if(!empty($pay_time)){
$assign['pay_time'] = $pay_time;
$having .= " and po.mid != 0";
$pay_time = explode('-', $pay_time);
if(count($pay_time)>=2){ #consume_time 中包含有字符-
$having .= ' and (po.pay_time between '.strtotime('-'.$pay_time[1]).' and '.strtotime('-'.$pay_time[0]).')';
}else if(count($pay_time)==1){ #consume_time 中没有包含有字符-
$str = html_entity_decode($pay_time[0]);
$having .= ' and po.pay_time'.substr($str,0,1).strtotime('-'.substr($str,1));
$assign['pay_time'] = $pay_time = html_entity_decode($pay_time[0]);
}
}
#消费总额 sum(pay_order.total_prices)
//$single_total_prices = isset($_POST['single_total_prices']) ? I('post.single_total_prices') : I('get.single_total_prices');
#手动输入搜索范围
//$start_total_prices = isset($_POST['start_total_prices']) ? I('post.start_total_prices') : I('get.start_total_prices');
//$end_total_prices = isset($_POST['end_total_prices']) ? I('post.end_total_prices') : I('get.end_total_prices');
if($single_total_prices!=''){
$assign['single_total_prices'] = html_entity_decode($single_total_prices); #有>0等情况
}
if($start_total_prices!=''){
$assign['start_total_prices'] = $start_total_prices;
}
if($end_total_prices!=''){
$assign['end_total_prices'] = $end_total_prices;
}
if($start_total_prices!='' || $end_total_prices!=''){
if($start_total_prices=='') $start_total_prices=0;
if($end_total_prices=='') $end_total_prices=0;
$having .= ' and sum(po.total_prices) between '.($start_total_prices<=$end_total_prices?$start_total_prices:$end_total_prices).' and '.($end_total_prices>=$start_total_prices?$end_total_prices:$start_total_prices);
}else{
if($single_total_prices!=''){ #single_balance 中包含有字符-
$total_prices = explode('-', $single_total_prices);
if(count($total_prices)>=2){
$having .= ' and sum(po.total_prices) between '.($total_prices[0]<=$total_prices[1]?$total_prices[0]:$total_prices[1]).' and '.($total_prices[1]>=$total_prices[0]?$total_prices[1]:$total_prices[0]);
}else{
$having .= ' and sum(po.total_prices)'.html_entity_decode($total_prices[0]);
}
}
}
#平均消费水平 avg(pay_order.total_prices)
//$single_avg_consume = isset($_POST['single_avg_consume']) ? I('post.single_avg_consume') : I('get.single_avg_consume');
#手动输入搜索范围
//$start_avg_consume = isset($_POST['start_avg_consume']) ? I('post.start_avg_consume') : I('get.start_avg_consume');
//$end_avg_consume = isset($_POST['end_avg_consume']) ? I('post.end_avg_consume') : I('get.end_avg_consume');
if($single_avg_consume!=''){
$assign['single_avg_consume'] = html_entity_decode($single_avg_consume); #有>0等情况
}
if($start_avg_consume!=''){
$assign['start_avg_consume'] = $start_avg_consume;
}
if($end_avg_consume!=''){
$assign['end_avg_consume'] = $end_avg_consume;
}
if($start_avg_consume!='' || $end_avg_consume!=''){
if($start_avg_consume=='') $start_avg_consume=0;
if($end_avg_consume=='') $end_avg_consume=0;
$having .= ' and avg(po.total_prices) between '.($start_avg_consume<=$end_avg_consume?$start_avg_consume:$end_avg_consume).' and '.($end_avg_consume>=$start_avg_consume?$end_avg_consume:$start_avg_consume);
}else{
if($single_avg_consume!=''){ #single_balance 中包含有字符-
$avg_consume = explode('-', $single_avg_consume);
if(count($avg_consume)>=2){
$having .= ' and avg(po.total_prices) between '.($avg_consume[0]<=$avg_consume[1]?$avg_consume[0]:$avg_consume[1]).' and '.($avg_consume[1]>=$avg_consume[0]?$avg_consume[1]:$avg_consume[0]);
}else{
$having .= ' and avg(po.total_prices)'.html_entity_decode($avg_consume[0]);
}
}
}
#月均购买频次 count(pay_order.total_prices) pay_order.pay_type=2(商城下单) 已支付成功 pay_order.pay_type=0(门店结账) 已支付成功
//$avg_shopping_count = isset($_POST['avg_shopping_count']) ? I('post.avg_shopping_count') : I('get.avg_shopping_count');
if(!empty($avg_shopping_count)){
$assign['avg_shopping_count'] = html_entity_decode($avg_shopping_count);
$having .= ' and count(*)/TIMESTAMPDIFF(MONTH,FROM_UNIXTIME(u.addtime),now())'.html_entity_decode($avg_shopping_count);
}
#如果查询条件中有 到店分析、消费总额、平均消费水平、月均购买频次
unset($total_prices,$avg_consume);
$total_prices = $assign['single_total_prices'].$assign['start_total_prices'].$assign['end_total_prices'];
$avg_consume = $assign['single_avg_consume'].$assign['start_avg_consume'].$assign['end_avg_consume'];
if(!empty($assign['pay_time']) || !empty($total_prices) || !empty($avg_consume) || !empty($assign['avg_shopping_count'])){
$having .= " and po.sid =".parent::$adminid." and po.pay_status = 1";
}
#默认排序是按id升序排列
$sort_by=$_POST['sort_by'] ? I('post.sort_by') : I('get.sort_by');
$sort_order=$_POST['sort_order'] ? I('post.sort_order') : I('get.sort_order');
if(empty($sort_by)) $sort_by = 'id'; //默认情况
if(empty($sort_order)) $sort_order = 'ASC'; //默认情况
$assign['sort_by'] = $sort_by;
$assign['sort_order'] = $sort_order;
$this->assign('assign',$assign); #直接转为json格式
$this->assign('data',json_encode($assign));
/*$str = '';
foreach ($assign as $key => $value) {
$str .= $key.'：'.$value.'；';
}
$this->ajaxReturn(['status'=>1,'msg'=>$str ]);*/
$assign['where'] = $where;
$assign['having'] = $having;
return $assign;
}
/*会员分析查询*/
public function index(){
$search = $this->search_user();
#条件
$where = $search['where'];
$having = $search['having'];
unset($search['where'],$search['having']);
//dump('这个是search：'); dump($search);
#测试使用
/*$str = '';
foreach ($search as $key => $value) {
$str.= $key.'：'.$value.'；';
}
$this->ajaxReturn(['status'=>1,'msg'=>$str]);*/
$pageSize = 10;
$limit = getLimit($pageSize);
$Userp = M('user_property');
$state = I('post.state');
if($state) $limit = '';
#默认排序是按id升序排列
$order=$search['sort_by'].' '.$search['sort_order'];
//$this->ajaxReturn(['status'=>1,'msg'=>$order]); #测试使用
/*select
p.id,p.realname,p.sex,p.phone,p.golb,p.addtime,p.balance,sum(po.total_prices) total_prices,
AVG(po.total_prices) as avg_consume,count(*)/TIMESTAMPDIFF(MONTH,FROM_UNIXTIME(p.addtime),now()) as avg_shopping_count
from (
select U.ID,u.realname,u.sex,u.phone,u.golb,u.addtime, sum(up.balance) as balance from zcm_user as u left join zcm_user_property as up on u.id=up.id
WHERE 1=1 and up.sid = 62 group by up.id
)as p
left join zcm_pay_order as po on po.uid=p.id
WHERE 1=1 and po.sid=62 and po.pay_status = 1 group by po.uid order by p.balance asc*/
#select U.ID,u.realname,u.sex,u.phone,u.golb,u.addtime, sum(up.balance) as balance from zcm_user as u left join zcm_user_property as up on u.id=up.id WHERE 1=1 and up.sid = 62 group by up.id
$full_user_property = C('DB_PREFIX').'user_property';
$subQuery = M('user')->alias('u')->field('U.ID,u.headimgurl,u.nickname,u.realname,u.sex,u.phone,u.golb,u.addtime,sum(up.balance) as balance')->join('LEFT JOIN '.$full_user_property.' as up ON up.id = U.ID')->group('U.ID')->where($where)->buildSql();
$full_pay_order = C('DB_PREFIX').'pay_order';
// 利用子查询进行查询
$info = M()->table($subQuery.' u')->field("U.ID,u.headimgurl,u.nickname,u.realname,u.sex,u.phone,u.golb,FROM_UNIXTIME(u.addtime, '%Y-%m-%d %H:%i:%S') add_time,u.balance,sum(po.total_prices) as total_prices,AVG(po.total_prices) as avg_consume,count(*)/TIMESTAMPDIFF(MONTH,FROM_UNIXTIME(u.addtime),now()) as avg_shopping_count,po.mid,FROM_UNIXTIME(po.pay_time, '%Y-%m-%d %H:%i:%S') paytime,po.sid,po.pay_status,po.pay_time,u.addtime")->join('LEFT JOIN '.$full_pay_order.' as po ON po.uid = U.ID')->where(['po.sid'=>parent::$adminid])->group('U.ID')->having($having)->order($order)->limit($limit)->select();
//['po.sid'=>parent::$adminid,'po.pay_status'=>1]
// 分页查询时计算总数据量，子表
$squery = M()->table($subQuery.' u')->field('U.ID,u.headimgurl,u.nickname,u.realname,u.sex,u.phone,u.golb,u.addtime,u.balance,sum(po.total_prices) as total_prices,AVG(po.total_prices) as avg_consume,count(*)/TIMESTAMPDIFF(MONTH,FROM_UNIXTIME(u.addtime),now()) as avg_shopping_count,po.mid,po.pay_time,po.sid,po.pay_status')->join('LEFT JOIN '.$full_pay_order.' as po ON po.uid = U.ID')->where(['po.sid'=>parent::$adminid])->group('U.ID')->having($having)->order($order)->buildSql();
#选择排序时,发起ajax请求
if(isset($_POST['sort_by'])&&isset($_POST['sort_order'])){
$count = M()->table($squery.' u')->count();
$Page = new \Think\Page($count,$pageSize);
foreach ($search as $key => $value) {
$Page->parameter[$key] = ($value);
}
$show = $Page->show();
#有搜索条件才展示数据 可导出数据
if(!( trim($where['_string']) == '1=1' && trim($having) == '1=1' ))
$this->ajaxReturn(['status'=>1,'msg'=>$info,'count'=>$count,'page'=>$show]);
else
$this->ajaxReturn(['status'=>0,'msg'=>'请您先设置查询条件！']);
}
/*$sort = ['sort_by'=>$sort_by,'sort_order'=>$sort_order];
$this->assign('sort',$sort);*/
#有搜索条件才展示数据 可导出数据
if(trim($where['_string']) == '1=1' && trim($having) == '1=1'){
exit($this->display());
}
//导出数据
if($state) exit($this->ExportUserinfo($info));
$this->assign('user',$info);
#分页
$count = M()->table($squery.' u')->count();
/*
#Page的构造方法
public function __construct($totalRows, $listRows=20, $parameter = array()) {
$this->parameter = empty($parameter) ? $_GET : $parameter;
}*/
$Page = new \Think\Page($count,$pageSize,$search);
/*
#这样添加的话会保留原来的get信息
foreach ($search as $key => $value) {
$Page->parameter[$key] = ($value);
}*/
$show = $Page->show();
$this->assign('page',$show);
$this->assign('count',$count);
//$this->pageLimit(['_string'=>'1=1'],M()->table($squery.' u'),$pageSize,$search); #分页
$this->display();
}
#tp框架中自带的分页处理代码段
public function pageLimit0($where='',$Model,$num=25,$search=[]){
$count = $Model->where($where)->count();
$this->assign('count',$count);
$Page = new \Think\Page($count,$num);
unset($search['_string']);
foreach ($search as $key => $value) {
$Page->parameter[$key] = ($value);
}
$show = $Page->show();
$this->assign('page',$show);
}
扫一扫
11-15
193
193
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
