<?php
/**
 * 报表实现类 长沙芝麻 cszm 电商 网络数据统计
 *
 * ============================================================================
 * 版权所有 2017北京素玄科技，并保留所有权利。
 *
 * 网站地址: http://www.suxuantech.com
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件！未经允许的情况下，您不能对本系统代码做任何修改 .
 * 不允许对程序代码以任何形式任何目的的再发布。
 * 如有修改需求，请联系素玄科技有限公司：contact@suxuantech.cn
 * ============================================================================
 * $Author: lilong 2020-10-16 $
 */
namespace app\crm\lib\report;
use app\crm\lib\Report;
use think\Controller;
use think\image\Exception;

class Cszm_ds_wldata extends Report {

    private $level=1;
    private $type=1;
    private $code='code1602815146';

    public function getLevel(){
        return $this->level;

    }
    public function getType(){
        return $this->type;
    }
    public function getCode(){
        return $this->code;
    }
    public function data(){
        set_time_limit(0);
        $data = input();

        $start = date('Y-m-d 00:00:00', strtotime($data['start']));
        $end = date('Y-m-d 23:59:59', strtotime($data['end']));
        $det = db('crm_s_report_form')->alias('f')->join('crm_s_report_detail d','d.form_id=f.id','left')->where('f.code',$data['menu_code'])->field('d.id,d.brandclass_id,f.report_class_name')->find();
        
        //可操作店
        if($data['shop_code']){
            $shop_code = $data['shop_code'];
        }else{
            $shop_code = session('shop_code');
        }

        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'请选择店面');
        }
        $where['is_status'] = 1;
        $where['c_from'] = 1;
        //$where['customer_type'] = 1;
        $where['add_shop_code'] = $shop_code;
        $whereN['add_shop_code'] = $shop_code;

        //不在追踪，2 否
        // $where['is_loss'] = 2;
        if($data['consultation_type_id']){
            $whereT['consultation_type_id'] = $data['consultation_type_id'];
        }

        // 地域id，拼接where条件
        if ($data['province']) {
            $where['province_id'] = (int)$data['province'];
            $whereC['province_id'] = (int)$data['province'];
            $whereN['province_id'] = (int)$data['province'];
        }

        if ($data['city']) {
            $where['city_id'] = (int)$data['city'];
            $whereC['city_id'] = (int)$data['city'];
            $whereN['city_id'] = (int)$data['city'];
        }

        $cKey = 's.channel_info_name';
        $group = 's.channel_info_name';
        $costKey = 'c.channel_info_name';
        $channel_key = 'channel_info_name';

        if ($data['customer_channel']) {
           $channels = explode(',',$data['customer_channel']);
            $channel_data = db('crm_s_channel')->where("channel_name","in",$data['customer_channel'])->order("order_sort")->select();
            $channel_datas = array_column($channel_data, 'channel_info_name');
            // asort($channel_datas);
            $channelData = [];
            foreach($channel_datas as $key=>$value){
                $channelData[] = $channel_data[$key];
            }
            $cKey = 's.channel_name';
            $group = 's.channel_name';
            $costKey = 'c.channel_name';
            $channel_key = 'channel_name';
        }elseif($data['erji_channel_id']){
            // 来源渠道
            $channels = explode(',',$data['erji_channel_id']);
            $channel_data = db('crm_s_channel')->where("channel_info_name","in",$data['erji_channel_id'])->order("order_sort")->select();
            $channel_datas = array_column($channel_data, 'channel_info_name');
            // asort($channel_datas);
            $channel_datas = array_unique($channel_datas);
            $channelData = [];
            foreach($channel_datas as $key=>$value){
                $channelData[] = $channel_data[$key];
            }
        }else{
            // 来源渠道
            $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
            $channel_datas = array_column($channels, 'channel_info_name');
            // asort($channel_datas);
            $channel_datas = array_unique($channel_datas);
            $channelData = [];
            foreach($channel_datas as $key=>$value){
                $channelData[] = $channels[$key];
            }
            $channels = array_column($channels,'channel_info_name');
        }
        // 花费 √
        $channelCost = db('crm_s_channel_cost')->alias('c')
            ->where($whereC)
            ->where('c.cost_date', 'between', [$start, $end])
            ->where('c.shop_code', $shop_code)
            ->group($costKey)
            ->column('sum(c.cost) as cost,c.id,c.channel_class_name,c.channel_info_name,c.channel_info_id',$costKey);
            $return['cost'] = db()->getLastSql();
        // 录入客资量 √
        $allCustomer = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            ->where($whereT)
            ->where('c.dj_day', 'between', [$start, $end])
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_info_name,s.id', $cKey);
        $return['mkz'] = db()->getLastSql();
        // 有效客资量 √
        $validCustomer =db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)->where($whereT)
            ->where('c.is_valid', 'in','1,5')
            ->where('c.dj_day', 'between', [$start, $end])
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_info_name,s.id', $cKey);
        $return['yxkz'] = db()->getLastSql();
        // 无效客资量 √
        $invalidCustomer =db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            ->where($whereT)
            ->where('c.is_valid', 'in','3,4')
            ->where('c.dj_day', 'between', [$start, $end])
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_info_name,s.id', $cKey);
        $return['wxkz'] = db()->getLastSql();
        // 待定客资量  √
        $ddCustomer =db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            ->where($whereT)
            ->where('c.is_valid', 2)
            ->where('c.dj_day', 'between', [$start, $end])
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_info_name,s.id', $cKey);
        $return['ddkz'] = db()->getLastSql();
        //预约量 √
        $yyNum = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)->where($whereT)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.sign_name','已预约')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_info_name,s.id', $cKey);
        $return['yykz'] = db()->getLastSql();
        // 成交量 有效订单数 √
        $orderNum = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->join('c_customerorder erp_order', 'erp_order.crm_customer_id=c.id','left')
            // ->join('c_customerorder erp_order', 'erp_order.crm_customer_number=c.customer_number')
            ->where($where)->where($whereT)
            ->where('erp_order.orderId is not null')
            ->where('erp_order.blankoutannal','<>',1)
            // ->where('erp_order.yixiaoshishou','>',0)
            ->where('erp_order.payment_money','>',0)
            ->where('erp_order.crm_order_type',1)
            // ->where('c.dj_day', 'between time', [$start, $end])
            ->where('erp_order.targetdate', 'between', [date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_info_name,s.id', $cKey);
        $return['cjkz'] = db()->getLastSql();
        $orderSql = db()->getLastSql();
        //在线成交量(crm_order_type  1:线上；2：线下) √
        // $zaixianorderNum = db('crm_c_customerinfo')->alias('c')
        //     ->join('crm_s_channel s', 's.id=c.customer_channel_id')
        //     ->join('c_customerorder erp_order', 'erp_order.crm_customer_id=c.id')
        //     // ->join('c_customerorder erp_order', 'erp_order.crm_customer_number=c.customer_number')
        //     ->where($where)
        //     ->where($whereT)
        //     ->where('erp_order.crm_order_type',1)
        //     ->where('erp_order.orderyouxiaodate', 'between time', [$start, $end])
        //     // ->where('c.dj_day', 'between time', [$start, $end])
        //     //->where('erp_order.targetdate', 'between', [date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
        //     ->group($group)
        //     ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_info_name,s.id', $cKey);
        // 营收额（订单总额） √
        $totalMoney = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->join('c_customerorder erp_order', 'erp_order.crm_customer_id=c.id','left')
            // ->join('c_customerorder erp_order','erp_order.crm_customer_number = c.customer_number')
            ->where($where)->where($whereT)
            // ->where('erp_order.orderyouxiaodate', 'between time', [$start, $end])
            // ->where('c.dj_day', 'between time', [$start, $end])
            ->where('erp_order.orderId is not null')
            ->where('erp_order.blankoutannal','<>',1)
            // ->where('erp_order.yixiaoshishou','>',0)
            ->where('erp_order.payment_money','>',0)
            ->where('erp_order.crm_order_type',1)
            ->where('erp_order.targetdate', 'between', [date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
            ->group($group)
            ->column('sum(erp_order.bargain_money) as total_money,s.channel_name,s.channel_class_name,s.channel_info_name,s.id', $cKey);
        $return['yingye'] = db()->getLastSql();
        $totalSql = db()->getLastSql();
        // 实际收入（实收金额总和） √
        $paymentMoney = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->join('c_customerorder erp_order', 'erp_order.crm_customer_id=c.id','left')
            // ->join('c_customerorder erp_order','erp_order.crm_customer_number = c.customer_number')
            ->join('c_paymentcontent erp_pay','erp_pay.orderId = erp_order.orderId','left')
            ->where($where)->where($whereT)
            ->where('erp_order.crm_order_type',1)
            ->where('erp_pay.shop_code',$shop_code)
            ->where('erp_pay.paymentdate', 'between', [date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
            ->where('erp_pay.suoshu', 'in', ['前期款','前期补款'])
            //->where('erp_order.targetdate', 'between time', [date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
            ->group($group)
            ->column('sum(erp_pay.payment_money) as payment_money,s.channel_name,s.channel_class_name,s.channel_info_name,s.id', $cKey);
        $return['shiji'] = db()->getLastSql();
        $moneySql = db()->getLastSql();
        $returnData = [];
        $channelCostNum = 0;
        $totalMoneyNum = 0;
        $paymentMoneyNum = 0;
        $arr = [];
        $arr['channel_name'] = '总计';
        foreach ($channelData as $k => $v) {
            //$returnData[$k]['id'] = $v['id'];
            if($data['customer_channel']){
                $returnData[$k]['channel_name'] = $v['channel_name'];
            }
            $returnData[$k]['channel_info_name'] = $v['channel_info_name'];

            $returnData[$k]['lrCustomer'] = $allCustomer[$v[$channel_key]]['num'] ? $allCustomer[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['validCustomer'] = $validCustomer[$v[$channel_key]]['num'] ? $validCustomer[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['invalidCustomer'] = $invalidCustomer[$v[$channel_key]]['num'] ? $invalidCustomer[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['dd'] = $ddCustomer[$v[$channel_key]]['num'] ? $ddCustomer[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['orderNum'] = $orderNum[$v[$channel_key]]['num'] ? $orderNum[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['yyNum'] = $yyNum[$v[$channel_key]]['num'] ? $yyNum[$v[$channel_key]]['num'] : 0;//预约量
            $returnData[$k]['zaixianorderNum'] = $zaixianorderNum[$v[$channel_key]]['num'] ? $zaixianorderNum[$v[$channel_key]]['num'] : 0;//在线成交量
            // 有效率（ 有效获取 / 毛客资 ）
            $returnData[$k]['validRate'] = (sprintf('%.2f', $returnData[$k]['validCustomer'] / $returnData[$k]['lrCustomer'] * 100)) . '%';
            //无效率 （ 无效客资 / 毛客资）
            $returnData[$k]['invalidRate'] = (sprintf('%.2f', $returnData[$k]['invalidCustomer'] / $returnData[$k]['lrCustomer'] * 100)) . '%';
            //待定率 （ 待定客资 / 毛客资）
            $returnData[$k]['ddRate'] = (sprintf('%.2f', $returnData[$k]['dd'] / $returnData[$k]['lrCustomer'] * 100)) . '%';
            // 毛客资成交率（ 新单 / 毛客资 ）
            $returnData[$k]['orderRate'] = (sprintf('%.2f', $returnData[$k]['orderNum'] / $returnData[$k]['lrCustomer'] * 100)) . '%';
            // 有效客资成交率（ 新单 / 有效客资 ）
            $returnData[$k]['changeRate'] = (sprintf('%.2f', $returnData[$k]['orderNum'] / $returnData[$k]['validCustomer'] * 100)) . '%';
            //渠道花费
            $returnData[$k]['channelCost'] = $channelCost[$v[$channel_key]]['cost'] ? sprintf('%.2f',$channelCost[$v[$channel_key]]['cost']) : 0;
            // 毛客资成本
            $returnData[$k]['customerCost'] = sprintf('%.2f', $channelCost[$v[$channel_key]]['cost'] / $returnData[$k]['lrCustomer']);
            // 有效获取成本
            $returnData[$k]['validCost'] = sprintf('%.2f', $channelCost[$v[$channel_key]]['cost'] / $returnData[$k]['validCustomer']);
            //成交成本（花费 / 成交量）
            $returnData[$k]['orderCost'] = sprintf('%.2f', $channelCost[$v[$channel_key]]['cost'] / $returnData[$k]['orderNum']);
            //成交均价 （ 营业额 / 订单数 ）
            $returnData[$k]['orderAvg'] = sprintf('%.2f', $returnData[$k]['totalMoney'] / $returnData[$k]['orderNum']);
            //营业额
            $returnData[$k]['totalMoney'] = $totalMoney[$v[$channel_key]]['total_money'] ? sprintf('%.2f',$totalMoney[$v[$channel_key]]['total_money']) : '0.00';
            //已收金额
            $returnData[$k]['paymentMoney'] = $paymentMoney[$v[$channel_key]]['payment_money'] ? sprintf('%.2f',$paymentMoney[$v[$channel_key]]['payment_money']) : '0.00';

            // ROI（实收：花费）(营收)
            $returnData[$k]['ROI'] = sprintf('%.2f',intval($returnData[$k]['paymentMoney'])/intval($returnData[$k]['channelCost']));
            $returnData[$k]['dtg_wechat']  = 0;

            $channelCostNum += $channelCost[$v[$channel_key]]['cost'];   //花费总计
            $arr['lrCustomer']  += $allCustomer[$v[$channel_key]]['num'];    //录入总计
            $arr['validCustomer'] += $validCustomer[$v[$channel_key]]['num'];     //有效总计
            $arr['invalidCustomer'] += $invalidCustomer[$v[$channel_key]]['num']; //无效总计
            $arr['dd'] += $returnData[$k]['dd'];   //待定总计
            $arr['orderNum'] += $returnData[$k]['orderNum']; //订单数总计
            $arr['yyNum']    += $returnData[$k]['yyNum'];    //预约总计
            $arr['zaixianorderNum']   += $returnData[$k]['zaixianorderNum']; //在线成交总计
            $totalMoneyNum   += $returnData[$k]['totalMoney']; //营业额总计
            $paymentMoneyNum  += $returnData[$k]['paymentMoney']; //已收金额总计
        }

        $arr['lrCustomer']  += $null['lrCustomer'];    //录入总计
        $arr['validCustomer'] += $null['validCustomer'];     //有效总计
        $arr['invalidCustomer'] += $null['invalidCustomer']; //无效总计
        $arr['dd'] += $null['dd'];   //待定总计
        $arr['orderNum'] += $null['orderNum']; //订单数总计
        $arr['yyNum']    += $null['yyNum'];    //预约总计
        $arr['zaixianorderNum']   += $null['zaixianorderNum']; //在线成交总计
        $arr['validRate'] = sprintf('%.2f', $arr['validCustomer'] / $arr['lrCustomer'] * 100) . '%';
        $arr['invalidRate'] = sprintf('%.2f', $arr['invalidCustomer'] / $arr['lrCustomer'] * 100) . '%';
        $arr['ddRate'] = sprintf('%.2f', $arr['dd'] / $arr['lrCustomer'] * 100) . '%';
        $arr['orderRate'] = sprintf('%.2f', $arr['orderNum'] / $arr['lrCustomer'] * 100) . '%';
        $arr['changeRate'] = sprintf('%.2f', $arr['orderNum'] / $arr['validCustomer'] * 100) . '%';
        $arr['channelCost'] = $channelCostNum + $null['channelCost'];
        $arr['customerCost'] = sprintf('%.2f', $arr['channelCost'] / $arr['lrCustomer']);//毛客资成本
        $arr['validCost'] = sprintf('%.2f', $arr['channelCost'] / $arr['validCustomer']);//有效客资成本
        $arr['orderCost'] = sprintf('%.2f', $arr['channelCost'] / $arr['orderNum']);//成交成本
        $arr['orderAvg'] = sprintf('%.2f',$arr['totalMoney']/$arr['orderNum']);
        $arr['totalMoney']   = $totalMoneyNum +$null['totalMoney']; //营业额总计
        $arr['paymentMoney'] = $paymentMoneyNum + $null['paymentMoney']; //已收金额总计

        $arr['totalMoney']   = $arr['totalMoney'] ? $arr['totalMoney'] : '0.00'; //营业额总计
        $arr['paymentMoney'] = $arr['paymentMoney'] ? $arr['paymentMoney'] : '0.00'; //已收金额总计
        $arr['ROI'] = sprintf('%.2f',intval($arr['paymentMoney'])/intval($arr['channelCost']));

        $arr['dtg_wechat'] = 0;

        array_push($returnData,$arr);
        // print_r($returnData);die;
        // echo db()->getLastSql();die;

        if($data['need'] == 1){
            if ($data['customer_channel']) {
                $title = ['二级渠道名称','三级渠道名称','总客资','有效量','无效量','待定量','成交量','预约量','在线成交量','有效率','无效率','待定率','毛客资成交率','有效客资成交率','花费','毛客资成本','有效客资成本','成交成本','成交均价','营业额','已收金额','ROI','待通过微信'];
            }else{
                $title = ['二级渠道名称','总客资','有效量','无效量','待定量','成交量','预约量','在线成交量','有效率','无效率','待定率','毛客资成交率','有效客资成交率','花费','毛客资成本','有效客资成本','成交成本','成交均价','营业额','已收金额','ROI','待通过微信'];
            }
            
            $code = $this->getCode();
            $name = db('crm_s_report_form')->where('code', $code)->value('report_name');
            $shopInfo = controller('crm/Customer','lib')->get_shop(['shop_code'=>$shop_code]);
            $file = $shopInfo[0]['shop_name'].'-'.$name.'.xlsx';
            controller("crm/Customer", "lib")->export($title, $returnData, $file);
        }
        $return['orderSql'] = $orderSql;
        $return['totalSql'] = $totalSql;
        $return['moneySql'] = $moneySql;

        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($returnData);
        $return['data'] = $returnData;
        return json($return);
    }

    public function show(){
        $menu_code = strtolower(input('param.menu_code'));
        $info = db('crm_s_report_form')->alias('f')->join('crm_s_report_detail d','d.form_id=f.id','left')->where('f.code',$menu_code)->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name')->find();

        $brandclass_id = $info['brandclass_id'] ? (int)$info['brandclass_id'] : session('brandclass_id');
        $shop_code = $info['shop_code'] ? $info['shop_code'] : session('shop_code');
        
        // 可操作店 
        $mc = new \app\common\lib\mcInterface();
        $shopData = $mc->getWorkShop(['staff_id'=>session('staff_id'),'brandclass_id'=>$brandclass_id]);
        $this->assign('shopData',$shopData);
        $this->assign("shop_code", $shop_code);

        // crm省    列表
        $data['type'] = 1;
        $data['shop_code'] = $shop_code;
        $province = controller("crm/Customer", "lib")->address($data);
        $this->assign('province', $province);

        //消费类型
        $consumptionType = controller("Customer", "lib")->consumptionType(['brandclass_id'=>$info['brandclass_id']]);
        $this->assign("consumptionType", $consumptionType);

        //来源渠道
        $channel = getChannelJson($shop_code);
        $this->assign("channel", json_encode($channel));

        //渠道一二级
        $channel2 = getChannelJson12($shop_code);
        $this->assign("channel2",json_encode($channel2));

        $code = $this->getCode();
        $display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.strtolower($info['report_class_name']).'.html';
        $title = db('crm_s_report_form')->where('code', $code)->value('report_name');
        $shopInfo = controller('crm/Customer','lib')->get_shop(['shop_code'=>$shop_code]);
        $this->assign('title', $shopInfo[0]['shop_name'].'-'.$title);
        $this->assign('did', 0);
        $this->assign('menu_code', $menu_code);
        $this->assign('start', date('Y-m'));
        $this->assign('end', date('Y-m'));
        return $this->fetch($display);
    }
}