// +---------------------------------------------------------------------- declare (strict_types = 1); namespace app\store\controller; use app\api\model\za\ZaActivityRelation as ZaActivityRelationModel; use app\api\service\order\Order as OrderService; use app\common\enum\order\Color as ColorEnum; use app\common\enum\order\orderGoods\DeliveryStatus as DeliveryStatusEnum; use app\common\enum\order\orderGoods\ExportStatus as ExportStatusEnum; use app\common\library\helper; use app\common\service\Export as ExportService; use app\store\model\groupbuy\GroupBuyHelp; use app\store\model\groupbuy\GroupBuyJoin; use app\store\model\OrderGoods as OrderGoodsModel; use app\store\model\Express as ExpressModel; use app\store\model\Order as OrderModel; use app\store\model\OrderGoodsPackage as OrderGoodsPackageModel; use app\store\model\OrderRefund as OrderRefundModel; use app\store\model\ActivityDiscount as ActivityDiscountModel; use app\common\model\OrderTj; use app\store\model\OrderAddress as OrderAddressModel; use app\store\model\shop\Shops; use think\facade\Db; use app\store\model\OrderRefund; /** * 订单管理 * Class Order * @package app\store\controller */ class Order extends Controller { /** * 订单列表 * @param string $dataType * @return array */ public function list(string $dataType = OrderModel::LIST_TYPE_ALL) { // 订单列表 $model = new OrderModel; $list = $model->getList($dataType, $this->request->param()); //获取订单颜色列表 $colorArray = ColorEnum::data(); return $this->renderSuccess(compact('dataType', 'list', 'colorArray')); } //订单详情 public function getDetail(string $orderNo){ $order_id = OrderModel::where('order_no',$orderNo)->value('order_id'); if($order_id>0){ $detail = $this->detail($order_id,false); foreach($detail['goods'] as &$row){ $pass_refund_time = false; $refund_time = $row['refund_time']; if($refund_time>0&&time()>$refund_time){ $pass_refund_time = true; } $row['pass_refund_time'] = $pass_refund_time; $refund = OrderRefundModel::where(['order_goods_id'=>$row['order_goods_id']])->find(); $refund_state_text ='-'; $is_refund = false; $is_refund_close = false; if($refund){ $is_refund = true; $refund_state_text = $refund['state_text']; if($refund['status']==40||$refund['status']==30){ $is_refund_close = true; } } $row['is_refund_close'] = $is_refund_close; $row['is_refund'] = $is_refund; $row['refund_state_text'] = $refund_state_text; } return $this->renderSuccess(compact('detail')); } return $this->renderError('未找到该订单记录'); } /** * 导出全部订单列表 * @return array * @throws \PhpOffice\PhpSpreadsheet\Exception * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception * @author: zjwhust * @Time: 2022/6/28 15:29 */ public function orderAllExport(){ $param = $this->request->param(); // if(isset($param['order_ids']) && empty($param['order_ids'])){ // return $this->renderError('请勾选订单后再导出'); // } $model = new OrderModel; $data = $model->orderExport($param); $res = ExportService::export($data['data'],$data['header'],$data['filename'],'列表','Xls'); return $this->renderSuccess($res,'导出成功'); } /** * 订单详情 * @param int $orderId * @return array */ public function detail(int $orderId,bool $isJsonReturn = true) { // 订单详情 $model = new OrderModel; if (!$detail = $model->getDetail($orderId)) { return $this->renderError('未找到该订单记录'); } if ($detail->delivery_type == 20){ $detail->shops = Shops::get($detail->shop_id); } //获取平台让利的 分润金额 $detail['pool_money'] = 0; $detail['commissions_content'] = ''; foreach($detail['commissionsDetail'] as $c_detail){ $detail['pool_money'] += $c_detail['clearing_money']; switch ($c_detail['role']){ case 1: $detail['commissions_content'] .= '普通用户佣金:¥ '; break; case 4: $detail['commissions_content'] .= ' , 店员佣金:¥ '; break; case 3: $detail['commissions_content'] .= ' , 店长佣金:¥ '; break; case 2: $detail['commissions_content'] .= '店老板佣金:¥ '; break; case 5: $detail['commissions_content'] .= ' , 蒸饭厨师佣金:¥ '; break; case 99: if($c_detail['commission_level']==1){ $detail['commissions_content'] .= '推荐官佣金(一级):¥ '; }else{ $detail['commissions_content'] .= '推荐官佣金(二级):¥ '; } break; } $detail['commissions_content'] .= helper::bcadd($c_detail['clearing_money'],'0',2).' '.($c_detail['user']['nick_name']??'').' '.($c_detail['user']['mobile']??''); } $detail['pool_money'] = helper::bcadd($detail['pool_money'],'0',2); unset($detail['commissionsDetail']); $res = []; $i = 0; $mjActIds = []; foreach ($detail['goods'] as &$goods) { if ($goods['is_mj_send'] && $goods['mj_send_activity_id']){ $mjActIds[] = $goods['mj_send_activity_id']; } if($goods['is_split_package']){ foreach ($goods['package'] as $pack){ $res[$i]['express_company'] = $pack['express_company']; $res[$i]['express_no'] = $pack['express_no']; $res[$i]['goods_id'] = $goods['goods_id']; $res[$i]['goods_name'] = $goods['goods_name']; $res[$i]['goods_sku_no'] = $goods['goods_sku_no']; $res[$i]['goods_image'] = $goods['goods_image']; $res[$i]['delivery_time'] = $pack['delivery_time']; $res[$i]['is_split_package'] = $goods['is_split_package']; $res[$i]['total_num'] = $pack['total_num']; $i++; }; }else{ if($goods['express_no']){ $res[$i]['express_company'] = $goods['express_company']; $res[$i]['express_no'] = $goods['express_no']; $res[$i]['goods_id'] = $goods['goods_id']; $res[$i]['goods_name'] = $goods['goods_name']; $res[$i]['goods_sku_no'] = $goods['goods_sku_no']; $res[$i]['goods_image'] = $goods['goods_image']; $res[$i]['delivery_time'] = $goods['delivery_time']; $res[$i]['is_split_package'] = $goods['is_split_package']; $res[$i]['total_num'] = $goods['total_num']; } $i++; } unset($goods['package']); } $detail['express_list'] = array_values($res); $detail['full_send_activity'] = null; //添加满就送活动信息 if ($detail->is_full_send == 1){ $detail['full_send_activity'] = \app\store\model\fullsend\FullSendActivity::where('id',$detail->full_send_activity_id) ->field('name,code,start_time,end_time,status,"满就送" as type')->find(); } //添加满件送活动信息 $detail['mj_send_activities'] = null; if (count($mjActIds)){ $detail['mj_send_activities'] = \app\store\model\mj\MjSendActivity::whereIn('id',$mjActIds) ->field('name,code,start_time,end_time,status,"满件送" as type')->select(); } //添加全场满件送活动信息 $detail['qc_mj_send_activities'] = null; if($detail->is_qc_send_send == 1){ $detail['qc_mj_send_activities'] = \app\store\model\qc\QcMjSendActivity::where('id',$detail->qc_send_activity_id) ->field('name,code,start_time,end_time,status,"全场满件送" as type')->find(); } //拼团活动 $detail['group_buy_activity'] = null; if ($detail['order_source'] == 40 and $detail['order_source_id']){ $group_buy_help = GroupBuyHelp::where('id',$detail['order_source_id'])->field('id,group_buy_activity_id')->find(); $group_buy_id = $group_buy_help->group_buy_activity_id; $detail['group_buy_activity'] = \app\api\model\groupbuy\GroupBuyActivity::with('goodsExcept') ->where('id',$group_buy_id) ->field('id,name,code,start_time,end_time,status,"拼团" as type')->find(); } //添加秒杀活动信息 $detail['miaosha_activities'] = null; if($detail['order_source']==60){ $ms_activity_id = \app\common\model\ms\MsActivityGood::where('id',$detail->order_source_id)->value('ms_activity_id'); if($ms_activity_id){ $detail['miaosha_activities'] = \app\common\model\ms\MsActivity::where('id',$ms_activity_id)->field('name,code,start_time,end_time,"限时秒杀" as type')->find(); } } //添加秒杀活动信息 $detail['za_activities'] = null; if($detail['order_source']==70){ $za_activity_id = \app\common\model\za\ZaActivityGood::where('id',$detail->order_source_id)->value('za_activity_id'); if($za_activity_id){ $detail['za_activities'] = \app\common\model\za\ZaActivity::where('id',$za_activity_id)->field('id,name,code,start_time,end_time,"买一赠一" as type')->find(); } // 获取活动领取人信息 // 1. 改订单是否在[yoshop_za_activity_relations]表中存在child_order_id(领取人的订单号) $zrModel = ZaActivityRelationModel::where('order_id', '=', $orderId)->find(); if ($zrModel['is_pay'] == 1) { // 已经支付, 才能显示领取人 $getOrderId = $zrModel['child_order_id']; $detail['za_activities']['get_order_id'] = $getOrderId; if ($getOrderId > 0) { // 2. 查询地址 $getAddress = OrderAddressModel::where('order_id', '=', $getOrderId)->find(); $detail['za_activities']['get_order_addr'] = $getAddress; } } } //添加拼团裂变活动信息 $detail['group_buy_lb_activities'] = null; if($detail['order_source']==50){ $group_buy_lb_activity_id = \app\common\model\groupbuylb\GroupBuyLbHelp::where('id',$detail->order_source_id)->value('group_buy_lb_activity_id'); if($group_buy_lb_activity_id){ $detail['group_buy_lb_activities'] = \app\common\model\groupbuylb\GroupBuyLbActivity::where('id',$group_buy_lb_activity_id)->field('name,code,start_time,end_time,"拼团裂变" as type')->find(); } } //砍价活动 $detail['kj_activity'] = null; if($detail['order_source']==30){ $activity_id = \app\common\model\kjactivity\KjActivityJoin::where('order_id',$detail['order_id'])->value("activity_id")??0; if($activity_id>0){ $detail['kj_activity'] = \app\common\model\kjactivity\KjActivity::where('id',$activity_id) ->field('name,code,start_time,end_time,"砍价" as type')->select(); } } //找出赠品信息 $gifts = []; foreach ($detail['goods'] as $k=>$g) { //if ($g['is_full_send'] == 1){ if ($g['goods_type'] == 20){ $gifts[] = $g; unset($detail['goods'][$k]); } } $detail = $detail->toArray(); $activity_discount_ids = helper::getArrayColumn($detail['goods'],'activity_discount_id'); $activity_discount_ids = array_unique($activity_discount_ids); $detail['activity_discount_list'] = ActivityDiscountModel::where('id','in',$activity_discount_ids)->select(); $detail['gifts'] = $gifts; if($isJsonReturn){ return $this->renderSuccess(compact('detail')); }else{ return $detail; } } /** * 待发货列表 * @param string * @return array */ public function deliverylList() { // 订单列表 return $this->listss(OrderModel::LIST_TYPE_DELIVERY); } /** * 已发货列表 * @param string * @return array */ public function receiptlList() { // 订单列表 return $this->listss(OrderModel::LIST_TYPE_RECEIPT); } public function listss($dataType){ // 订单列表 $model = new OrderGoodsModel(); $list = $model->getList($dataType,$this->request->param()); return $this->renderSuccess(compact('list')); } /** * 常规发货 * @return array * @throws \Exception * @author: zjwhust * @Time: 2021/10/12 15:24 */ public function delivery(){ $model = new OrderGoodsModel(); if ($model->delivery($this->request->param())) { return $this->renderSuccess('确认发货成功'); } return $this->renderError($model->getError()); } /** * 拆分发货 * @return array * @throws \Exception * @author: zjwhust * @Time: 2021/11/22 13:44 */ public function splitDelivery(){ $model = new OrderGoodsModel(); if ($model->splitDelivery($this->request->param())) { return $this->renderSuccess('确认发货成功'); } return $this->renderError($model->getError()); } /** * 标签备注 * @param $order_id * @param string $color_remark * @param string $color * @return array * @author: zjwhust * @Time: 2021/10/12 18:02 */ public function updColor(){ $model = new OrderModel; if ($model->updColor($this->request->param())) { return $this->renderSuccess('添加备注成功'); } return $this->renderError($model->getError()); } /** * 修改订单收货信息 * @return array * @author: zjwhust * @Time: 2021/10/13 10:55 */ public function updAddress(){ $model = new OrderModel; if ($model->updAddress($this->request->param())) { return $this->renderSuccess('修改收货地址成功'); } return $this->renderError($model->getError()); } /** * 查看物流详情 * @param string $express_no * @param int $is_split_package 是否拆分包裹 0未拆分 1已拆分 * @return array * @throws BaseException * @throws \think\db\exception\DataNotFoundException * @throws \think\db\exception\DbException * @throws \think\db\exception\ModelNotFoundException * @author: zjwhust * @Time: 2021/10/9 9:13 */ public function express(string $express_no,int $is_split_package=1) { if($is_split_package==0){ $model = new OrderGoodsModel(); $detail = $model->where('express_no',$express_no)->find(); if (!$detail || $detail['delivery_status']!=DeliveryStatusEnum::DELIVERED) { return $this->renderError('未发货'); } }else{ $model = new OrderGoodsPackageModel(); $detail = $model->where('express_no',$express_no)->find(); if (!$detail || $detail['delivery_status']!=DeliveryStatusEnum::DELIVERED) { return $this->renderError('未发货'); } } $res['order'] = [ "express_no" => $detail['express_no'], "express_company" => $detail['express_company'], ]; $res['delivery'] = (new ExpressModel)->deliverylist($express_no, $detail['delivery_time'], 1); return $this->renderSuccess(compact('res')); } /** * 导出待发货订单功能 * @return array * @author: zjwhustf * @Time: 2021/10/15 13:43 */ public function orderExport(){ $param = $this->request->param(); if(isset($param['order_goods_id']) && empty($param['order_goods_id'])){ return $this->renderError('请勾选订单商品后再导出'); } $model = new OrderGoodsModel; if ($param['gtype'] == 2){ $data = $model->orderBagExport($param); }else{ $data = $model->orderExport($param); } // dd($data['data']); $res = ExportService::export($data['data'],$data['header'],$data['filename'],'列表','Xls',true); //批量修改导出状态为导出 $model->updateBase(['is_export' => ExportStatusEnum::EXPORT,'export_time' => time()], [['order_goods_id', 'in', $param['order_goods_id']]]); return $this->renderSuccess($res,'导出成功'); } /** * 批量导入发货,导入运单号 * @return array */ public function expressImport(){ $file = request()->file('file'); if (empty($file)){ return $this->renderError('请先上传导入的文件'); } // 上传到本地服务器 $fileName = \think\facade\Filesystem::putFile( 'delivery', $file); $res = ExportService::import($fileName); return $this->renderSuccess($res,'导入成功'); } //订单统计表 public function ordertj(){ $model = new OrderTj; $param = $this->request->param(); $list = $model->getList($param); return $this->renderSuccess(compact('list')); } //订单统计导出 public function ordertjExport(){ $model = new OrderTj; $param = $this->request->param(); $data = $model->listExport($param); $res = ExportService::export($data['data'],$data['header'],$data['filename'],'列表','Xls',true); return $this->renderSuccess($res,'导出成功'); } //复购周期分析 public function repurchase(){ $params = $this->request->param(); if (!empty($params['betweenTime'])) { $times = between_time_format($params['betweenTime']); /* $start_time = $times['start_time']; $end_time = $times['end_time'];*/ $start_time = strtotime(date('Y-m-d 00:00:00',$times['start_time'])); $end_time = strtotime(date('Y-m-d 23:59:59',$times['end_time'])); }else{ $start_time = time()-86400*180; $end_time = time(); } $sql = "SELECT m.user_id, FROM_UNIXTIME(sfo.create_time) AS t1, FROM_UNIXTIME(m.create_time) AS t2, DATEDIFF( FROM_UNIXTIME(m.create_time), FROM_UNIXTIME(sfo.create_time) ) AS days FROM yoshop_order AS m LEFT JOIN yoshop_order AS sfo ON m.user_id = sfo.user_id AND sfo.create_time < m.create_time WHERE m.create_time BETWEEN ".$start_time." AND ".$end_time." AND m.pay_status = 20 AND sfo.create_time BETWEEN ".$start_time." AND ".$end_time." AND sfo.pay_status = 20 AND ( SELECT count(*) FROM yoshop_order AS n WHERE m.user_id = n.user_id AND m.create_time > n.create_time AND sfo.create_time <= n.create_time ) = 1 GROUP BY m.user_id,days"; $result = Db::query($sql); $data = ['day1'=>0,'day2'=>0,'day3'=>0]; $count = []; foreach ($result as $item){ if($item['days']<=30){//0≤D≤30 if(!isset($count[$item['user_id']])){ $data['day1'] += 1; } $count[$item['user_id']] = 1; }elseif($item['days']>30 && $item['days']<=60){//3060 $data['day3'] += 1; } } return $this->renderSuccess(compact('data')); } //统计rf public function rf(){ $r1f1 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and pay_time>unix_timestamp(now())-7*86400 GROUP BY user_id HAVING count(order_id)=1"; $r1f2 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and pay_time>unix_timestamp(now())-7*86400 GROUP BY user_id HAVING count(order_id)>=2 and count(order_id)<5"; $r1f3 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and pay_time>unix_timestamp(now())-7*86400 GROUP BY user_id HAVING count(order_id)>=5"; $r2f1 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and pay_timeunix_timestamp(now())-60*86400 GROUP BY user_id HAVING count(order_id)=1"; $r2f2 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and pay_timeunix_timestamp(now())-60*86400 GROUP BY user_id HAVING count(order_id)>=2 and count(order_id)<5"; $r2f3 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and pay_timeunix_timestamp(now())-60*86400 GROUP BY user_id HAVING count(order_id)>=5"; $r3f1 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and pay_time$r11,"r2"=>$r12,"r3"=>$r13,'r4'=>$r11+$r12+$r13]; $data[] = ['r1'=>$r21,"r2"=>$r22,"r3"=>$r23,'r4'=>$r21+$r22+$r23]; $data[] = ['r1'=>$r31,"r2"=>$r32,"r3"=>$r33,'r4'=>$r31+$r32+$r33]; return $this->renderSuccess(compact('data')); } //统计rm public function rm(){ $r1f1 = "select user_id,sum(pay_price+rice_card_money) from yoshop_order where pay_status=20 and pay_time>unix_timestamp(now())-7*86400 GROUP BY user_id HAVING sum(pay_price+rice_card_money)<200"; $r1f2 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and pay_time>unix_timestamp(now())-7*86400 GROUP BY user_id HAVING sum(pay_price+rice_card_money)>=200 and sum(pay_price+rice_card_money)<1000"; $r1f3 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and pay_time>unix_timestamp(now())-7*86400 GROUP BY user_id HAVING sum(pay_price+rice_card_money)>=1000"; $r2f1 = "select user_id,sum(pay_price+rice_card_money) from yoshop_order where pay_status=20 and pay_time>unix_timestamp(now())-60*86400 and pay_time$r11,"r2"=>$r12,"r3"=>$r13,'r4'=>$r11+$r12+$r13]; $data[] = ['r1'=>$r21,"r2"=>$r22,"r3"=>$r23,'r4'=>$r21+$r22+$r23]; $data[] = ['r1'=>$r31,"r2"=>$r32,"r3"=>$r33,'r4'=>$r31+$r32+$r33]; return $this->renderSuccess(compact('data')); } //消费力 public function clist(){ $start = 10000000; $end = time(); $params = $this->request->param(); //$ftype = $param['ftype']??0; if (!empty($params['betweenTime'])) { $times = between_time_format($params['betweenTime']); $start = strtotime(date('Y-m-d 00:00:00',$times['start_time'])); $end = strtotime(date('Y-m-d 23:59:59',$times['end_time'])); //$start = $times['start_time']; //$end = $times['end_time']; } log_record('start::'.date('Y-m-d H:i:s',$start),'error'); log_record('end::'.date('Y-m-d H:i:s',$end),'error'); //dd($times); //1 日 2 周 3 月 /* if($ftype==1){ $start = strtotime(date("Y-m-d 00:00:00",time())); $end = time(); } if($ftype==2){ $start = mktime(0,0,0,intval(date("m")),date("d")-date("N")+1,intval(date("y"))); $end = time(); } if($ftype==3){ $start = mktime(0,0,0,intval(date("m")),1,intval(date("Y"))); $end = time(); } if($ftype==4){ $start = strtotime(Date("Y",time())."-1"."-1"); $end = time(); }*/ $sqlo1 = "select user_id,sum(pay_price+rice_card_money) from yoshop_order where pay_status=20 and tag=0 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING sum(pay_price+rice_card_money)<200"; $sqlo2 = "select user_id,sum(pay_price+rice_card_money) from yoshop_order where pay_status=20 and tag=0 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING sum(pay_price+rice_card_money)>=200 and sum(pay_price+rice_card_money)<1000"; $sqlo3 = "select user_id,sum(pay_price+rice_card_money) from yoshop_order where pay_status=20 and tag=0 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING sum(pay_price+rice_card_money)>=1000"; $sqln1 = "select user_id,sum(pay_price+rice_card_money) from yoshop_order where pay_status=20 and tag=1 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING sum(pay_price+rice_card_money)<200"; $sqln2 = "select user_id,sum(pay_price+rice_card_money) from yoshop_order where pay_status=20 and tag=1 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING sum(pay_price+rice_card_money)>=200 and sum(pay_price+rice_card_money)<1000"; $sqln3 = "select user_id,sum(pay_price+rice_card_money) from yoshop_order where pay_status=20 and tag=1 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING sum(pay_price+rice_card_money)>=1000"; $resulto1 = Db::query($sqlo1); $resulto2 = Db::query($sqlo2); $resulto3 = Db::query($sqlo3); $resultn1 = Db::query($sqln1); $resultn2 = Db::query($sqln2); $resultn3 = Db::query($sqln3); $data[] = ['title'=>'老客','value'=>[count($resulto1),count($resulto2),count($resulto3)]]; $data[] = ['title'=>'新客','value'=>[count($resultn1),count($resultn2),count($resultn3)]]; return $this->renderSuccess(compact('data')); } //消费频次 public function flist(){ $start = 10000000; $end = time(); $params = $this->request->param(); $ftype = $param['ftype']??0; if (!empty($params['betweenTime'])) { $times = between_time_format($params['betweenTime']); $start = strtotime(date('Y-m-d 00:00:00',$times['start_time'])); $end = strtotime(date('Y-m-d 23:59:59',$times['end_time'])); /* $start = $times['start_time']; $end = $times['end_time'];*/ } //1 日 2 周 3 月 /* if($ftype==1){ $start = strtotime(date("Y-m-d 00:00:00",time())); $end = time(); } if($ftype==2){ $start = mktime(0,0,0,intval(date("m")),date("d")-date("N")+1,intval(date("y"))); $end = time(); } if($ftype==3){ $start = mktime(0,0,0,intval(date("m")),1,intval(date("Y"))); $end = time(); } if($ftype==4){ $start = strtotime(Date("Y",time())."-1"."-1"); $end = time(); }*/ $sqlo1 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and tag=0 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING count(order_id)=1"; $sqlo2 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and tag=0 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING count(order_id)>=2 and count(order_id)<5"; $sqlo3 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and tag=0 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING count(order_id)>=5"; $sqln1 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and tag=1 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING count(order_id)=1"; $sqln2 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and tag=1 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING count(order_id)>=2 and count(order_id)<5"; $sqln3 = "select user_id,count(order_id) as c from yoshop_order where pay_status=20 and tag=1 and pay_time>$start and pay_time<$end GROUP BY user_id HAVING count(order_id)>=5"; $resulto1 = Db::query($sqlo1); $resulto2 = Db::query($sqlo2); $resulto3 = Db::query($sqlo3); $resultn1 = Db::query($sqln1); $resultn2 = Db::query($sqln2); $resultn3 = Db::query($sqln3); $data[] = ['title'=>'老客','value'=>[count($resulto1),count($resulto2),count($resulto3)]]; $data[] = ['title'=>'新客','value'=>[count($resultn1),count($resultn2),count($resultn3)]]; return $this->renderSuccess(compact('data')); } //订单导出 public function orderFxExport(){ $start = 10000000; $end = time(); $param = $this->request->param(); $ftype = $param['ftype']??0; if (!empty($param['betweenTime'])) { $times = between_time_format($param['betweenTime']); $start = $times['start_time']; $end = $times['end_time']; } //1 日 2 周 3 月 if($ftype==1){ $start = strtotime(date("Y-m-d 00:00:00",time())); $end = time(); } if($ftype==2){ $start = mktime(0,0,0,intval(date("m")),date("d")-date("N")+1,intval(date("y"))); $end = time(); } if($ftype==3){ $start = mktime(0,0,0,intval(date("m")),1,intval(date("Y"))); $end = time(); } if($ftype==4){ $start = strtotime(Date("Y",time())."-1"."-1"); $end = time(); } $filter[] = ['create_time', '>=', $start]; $filter[] = ['create_time', '<', $end]; $list = OrderModel::field('count(order_id) as c,sum(pay_price+rice_card_money) as amount,user_id,create_time,group_concat(order_id separator ",") as order_ids')->where($filter)->where("pay_status",20)->group('user_id')->with(['user','goods'])->select(); // return $list; $data['header'] = ['时间', '用户ID', '昵称', '手机号','实际支付件数','支付成功次数','支付总金额']; $data['filename'] = '用户分析'; $data['data'] = []; //'实际支付件数','支付金额' $newdata = []; foreach($list as $row){ $newrow['create_time'] = $row['create_time']; $newrow['user_id'] = $row['user_id']; $newrow['nick_name'] = $row['user']['nick_name']; $newrow['mobile'] = $row['user']['mobile']; // $newrow['order_ids'] = $row['order_ids'];` $newrow['sku'] = OrderGoodsModel::where("order_id",'in',explode(",",$row['order_ids']))->count(); $newrow['order_cnt'] = count(explode(",",$row['order_ids'])); $newrow['amount'] = $row['amount']; $newdata[] = $newrow; } $data['data'] = $newdata; $res = ExportService::export($data['data'],$data['header'],$data['filename'],'列表','Xls',true); return $this->renderSuccess($res,'导出成功'); // return $this->renderSuccess(compact("data")); } }