lachang/app/store/controller/Order.php

<?php
// +----------------------------------------------------------------------
// | 萤火商城系统 [ 致力于通过产品和服务，帮助商家高效化开拓市场 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2017~2021 https://www.yiovo.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed 这不是一个自由软件，不允许对程序代码以任何形式任何目的的再发行
// +----------------------------------------------------------------------
// | Author: 萤火科技 <admin@yiovo.com>
// +----------------------------------------------------------------------
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){//30<D≤60
                $data['day2'] += 1;
            }else{//D>60
                $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_time<unix_timestamp(now())-7*86400

                and pay_time>unix_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_time<unix_timestamp(now())-7*86400

                and pay_time>unix_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_time<unix_timestamp(now())-7*86400

                and pay_time>unix_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<unix_timestamp(now())-60*86400

                GROUP BY user_id HAVING count(order_id)=1";
        $r3f2 = "select user_id,count(order_id) as c from yoshop_order
                where pay_status=20

                and pay_time<unix_timestamp(now())-60*86400

                GROUP BY user_id HAVING count(order_id)>=2 and count(order_id)<5";

        $r3f3 = "select user_id,count(order_id) as c from yoshop_order
                where pay_status=20

                and pay_time<unix_timestamp(now())-60*86400

                GROUP BY user_id HAVING count(order_id)>=5";   


        $result11 = Db::query($r1f1);        

        $result12 = Db::query($r1f2); 

        $result13 = Db::query($r1f3); 


        $result21 = Db::query($r2f1);        

        $result22 = Db::query($r2f2); 

        $result23 = Db::query($r2f3); 



        $result31 = Db::query($r3f1);        

        $result32 = Db::query($r3f2); 

        $result33 = Db::query($r3f3); 


        // var_dump(count($result11));
        // var_dump(count($result12));
        // var_dump(count($result13));



        $r11 = count($result11);
        $r12 = count($result12);
        $r13 = count($result13);

        // echo json_encode($result12);
        // echo "---";
        //  echo json_encode($result13);
        // die();


        $r21 = count($result21);
        $r22 = count($result22);
        $r23 = count($result23);


        // var_dump(count($result21));
        // var_dump(count($result22));
        // var_dump(count($result23));


        // var_dump(count($result31));
        // var_dump(count($result32));
        // var_dump(count($result33));

        $r31 = count($result31);
        $r32 = count($result32);
        $r33 = count($result33);


        $data[] = ['r1'=>$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<unix_timestamp(now())-7*86400

                GROUP BY user_id HAVING sum(pay_price+rice_card_money)<200";


        $r2f2 ="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<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";

        $r2f3 = "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<unix_timestamp(now())-7*86400

                GROUP BY user_id HAVING sum(pay_price+rice_card_money)>=1000";


         $r3f1 = "select user_id,sum(pay_price+rice_card_money) from yoshop_order
                where pay_status=20

             
                and pay_time<unix_timestamp(now())-60*86400

                GROUP BY user_id HAVING sum(pay_price+rice_card_money)<200";

                
        $r3f2 ="select user_id,sum(pay_price+rice_card_money) from yoshop_order
                where pay_status=20

                and pay_time<unix_timestamp(now())-60*86400

                GROUP BY user_id HAVING sum(pay_price+rice_card_money)>=200  and sum(pay_price+rice_card_money)<1000";

        $r3f3 = "select user_id,sum(pay_price+rice_card_money) from yoshop_order
                where pay_status=20

               and pay_time<unix_timestamp(now())-60*86400

                GROUP BY user_id HAVING sum(pay_price+rice_card_money)>=1000";

      

        $result11 = Db::query($r1f1);        

        $result12 = Db::query($r1f2); 

        $result13 = Db::query($r1f3); 


        $result21 = Db::query($r2f1);        

        $result22 = Db::query($r2f2); 

        $result23 = Db::query($r2f3); 



        $result31 = Db::query($r3f1);        

        $result32 = Db::query($r3f2); 

        $result33 = Db::query($r3f3); 


        // var_dump(count($result11));
        // var_dump(count($result12));
        // var_dump(count($result13));

        $r11 = count($result11);
        $r12 = count($result12);
        $r13 = count($result13);



        $r21 = count($result21);
        $r22 = count($result22);
        $r23 = count($result23);


        // var_dump(count($result21));
        // var_dump(count($result22));
        // var_dump(count($result23));


        // var_dump(count($result31));
        // var_dump(count($result32));
        // var_dump(count($result33));

        $r31 = count($result31);
        $r32 = count($result32);
        $r33 = count($result33);


        $data[] = ['r1'=>$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"));

    }
    
}