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

class Dlnx_channel_data extends Report {

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

    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')->where('f.code',$data['menu_code'])->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['add_shop_code'] = $shop_code;
        // 消费类型
        if($data['consultation_type_id']){
            $where['consultation_type_id'] = $data['consultation_type_id'];
        }
        // 省市
        if ($data['province']) {
            $where['province_id'] = (int)$data['province'];
        }
        if ($data['city']) {
            $where['city_id'] = (int)$data['city'];
        }
        // 二级渠道 即  来源渠道
        if ($data['channel_info']) {
            $channel_infos = trim($data['channel_info']);
            $tempColumn = explode(',',$channel_infos);
            foreach ($tempColumn as $k => $v) {
                $column[$k]['channel_info'] = $v;
            }
            // 客资
            $where['sc.channel_info_name'] = ['in',$channel_infos];
            $group = 'sc.channel_info_name';
            // 花费
            $whereCost['sc.channel_info_name'] = ['in',$channel_infos];
            $groupCost = 'sc.channel_info_name';
        }
        // 三级渠道 即 渠道名称
        else if ($data['customer_channel']) { 
            $channel_names = trim($data['customer_channel']);
            $channels = db('crm_s_channel')->where("channel_name","in",$data['customer_channel'])->order("order_sort")->field('channel_info_name,channel_name')->select();
            foreach ($channels as $k => $v) {
                $column[$k]['channel_info'] = $v['channel_info_name'];
                $column[$k]['channel_name'] = $v['channel_name'];
            }
            // 客资
            $where['c.customer_channel'] = ['in',$channel_names];
            $group = 'c.customer_channel';
            // 花费
            $whereCost['sc.channel_name'] = ['in',$channel_names];
            $groupCost = 'sc.channel_name';
        }
        // 默认三级渠道 即 渠道名称
        else{ 
            $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
            $channel_names = array_column($channels, 'channel_name');
            foreach ($channels as $k => $v) {
                $column[$k]['channel_info'] = $v['channel_info_name'];
                $column[$k]['channel_name'] = $v['channel_name'];
            }
            // 客资
            $where['c.customer_channel'] = ['in',$channel_names];
            $group = 'c.customer_channel';
            // 花费
            $whereCost['sc.channel_name'] = ['in',$channel_names];
            $groupCost = 'sc.channel_name';
        }
        // 客资状态取值设定
        $yxconfig = db_config('yx_config') ? db_config('yx_config') : "'1','5'";
        $wxconfig = db_config('wx_config') ? db_config('wx_config') : "'3','4'";
        $ddconfig = db_config('dd_config') ? db_config('dd_config') : "'2'";
        $sql  = 'sc.channel_info_name,sc.channel_name';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'") as mkz';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.is_valid in ('.$yxconfig.')) as yx';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.is_valid in ('.$wxconfig.')) as wx';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.is_valid in ('.$ddconfig.')) as dd';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.wechat_status="2") as yjwx';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.wechat_status="3") as wjwx';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.wechat_status="1") as dtgwx';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.step_name="预约拍照") as yypz';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.step_name="周末拍照") as zmpz';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.step_name="非周末拍照") as fzmpz';
        // 基本数据 
        $info = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel sc', 'sc.channel_name=c.customer_channel','left')
            ->where($where)
            ->group($group)
            ->column($sql, $group);
        $sql1 = db()->getLastSql();
        // 订单
        $ddNum = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel sc', 'sc.channel_name=c.customer_channel','left')
            ->join('c_customerorder o', 'o.crm_customer_id=c.id','left')
            ->where($where)->where('o.orderyouxiaodate','between',[$start,$end])
            ->group($group)
            ->column('sc.channel_info_name,sc.channel_name,count(o.id) as ddNum,sum(o.total_money) as total,sum(o.bargain_money) as yixiao',$group);
        $sql2 = db()->getLastSql();
        // 收款
        $money = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel sc', 'sc.channel_name=c.customer_channel','left')
            ->join('c_customerorder o', 'o.crm_customer_id=c.id','left')
            ->join('c_paymentcontent p', 'p.orderId=o.orderId','left')
            ->where($where)->where('p.isshishou','实收')
            ->where('p.paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
            ->group($group)
            ->column('sc.channel_info_name,sc.channel_name,sum(p.payment_money) as ssje',$group);
        $sql3 = db()->getLastSql();
        // 花费 
        $channelCost = db('crm_s_channel_cost')->alias('sc')
            ->where($whereCost)
            ->where('sc.cost_date', 'between', [$start, $end])
            ->where('sc.shop_code', $shop_code)
            ->group($groupCost)
            ->column('sum(sc.cost) as cost,sc.id,sc.channel_class_name,sc.channel_info_name,sc.channel_info_id',$groupCost);
        $sql4 = db()->getLastSql();
        
        // 响应率  第一次响应客资的时间 c_star_time jr_day  5分钟  /  毛客资  按系统录入时间算，指定时间内（一般可以自定义为佳）是否有操作。
        $xyCus =  db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel sc', 'sc.channel_name=c.customer_channel','left')
            ->where($where)->where('c.jr_day','between',[$start, $end])
            ->where("DATE_FORMAT(c_star_day,'%Y%m%d%H%i')-DATE_FORMAT(jr_day,'%Y%m%d%H%i')<=5")
            ->group($group)
            ->column('sc.channel_info_name,sc.channel_name,count(c.id) as lastTrac',$group);
        // 微信通过率 已经加上微信的客资数量 / 毛客资

        // 维护率 取消
        foreach ($column as $k => $v) {
            $returnData[$k]['channel_info_name'] = $v['channel_info'];
            if ($group=='sc.channel_info_name') {
                $returnData[$k]['channel_name'] = '';
                $key = $v['channel_info'];
                $arr['channel_info_name'] = '总计';
            }else{
                $returnData[$k]['channel_name'] = $v['channel_name'];
                $key = $v['channel_name'];
                $arr['channel_name'] = '总计';
            }
            $returnData[$k]['mkz']   = $info[$key]['mkz'] ? $info[$key]['mkz']: 0;
            $returnData[$k]['yx']    = $info[$key]['yx'] ? $info[$key]['yx']: 0;
            $returnData[$k]['wx']    = $info[$key]['wx'] ? $info[$key]['wx']: 0;
            $returnData[$k]['dd']    = $info[$key]['dd'] ? $info[$key]['dd']: 0;
            // 微信
            $returnData[$k]['yjwx']  = $info[$key]['yjwx'] ? $info[$key]['yjwx']: 0;
            $returnData[$k]['wjwx']  = $info[$key]['wjwx'] ? $info[$key]['wjwx']: 0;
            $returnData[$k]['dtgwx'] = $info[$key]['dtgwx'] ? $info[$key]['dtgwx']: 0;
            $returnData[$k]['wxRate']= $info[$key]['yjwx'] ? sprintf('%.2f', $info[$key]['yjwx']/$info[$key]['mkz']*100).'%' : '0%';
            // 维护率 算法：指定时间段内该回访且已经回访的客资量/指定时间段内应该二次或三次或以上该跟客资回访的客资量
            // $returnData[$k]['whRate']= $yhf[$key]['yhf'] ? sprintf('%.2f', $yhf[$key]['yhf']/$yihf[$key]['yihf']*100).'%' : '0%';
            // 响应量
            $returnData[$k]['xy']= $xyCus[$key]['lastTrac'] ? $xyCus[$key]['lastTrac'] : '0';
            // 响应率
            $returnData[$k]['xyRate']= $xyCus[$key]['lastTrac'] ? sprintf('%.2f', $xyCus[$key]['lastTrac']/$info[$key]['mkz']*100).'%' : '0%';
            // 预约
            $returnData[$k]['yypz']  = $info[$key]['yypz'] ? $info[$key]['yypz'] : 0;
            // 进店
            $returnData[$k]['intoNum'] = $info[$key]['zmpz']+$info[$key]['fzmpz'] ? $info[$key]['zmpz']+$info[$key]['fzmpz'] : 0;
            // 订单
            $returnData[$k]['orderNum']= $ddNum[$key]['ddNum'] ? $ddNum[$key]['ddNum'] : 0;
            // 有效、无效、待定 率
            $returnData[$k]['yxRate']= $info[$key]['yx'] ? sprintf('%.2f', $info[$key]['yx']/$info[$key]['mkz']*100).'%' : '0%';
            $returnData[$k]['wxRate']= $info[$key]['wx'] ? sprintf('%.2f', $info[$key]['wx']/$info[$key]['mkz']*100).'%' : '0%';
            $returnData[$k]['ddRate']= $info[$key]['dd'] ? sprintf('%.2f', $info[$key]['dd']/$info[$key]['mkz']*100).'%' : '0%';
            // 进店率
            $returnData[$k]['mkzIntoRate'] = $returnData[$k]['intoNum'] ? sprintf('%.2f', $returnData[$k]['intoNum']/$info[$key]['mkz'] * 100).'%' : '0%';
            $returnData[$k]['yxIntoRate'] = $returnData[$k]['intoNum'] ? sprintf('%.2f', $returnData[$k]['intoNum']/$info[$key]['yx'] * 100).'%' : '0%';
            // 成交率
            $returnData[$k]['mkzOrderRate'] = $returnData[$k]['orderNum'] ? sprintf('%.2f', $returnData[$k]['orderNum']/$returnData[$k]['mkz']*100).'%' : '0%';
            $returnData[$k]['yxOrderRate']  = $returnData[$k]['orderNum'] ? sprintf('%.2f', $returnData[$k]['orderNum']/$returnData[$k]['yx']*100).'%' : '0%';
            $returnData[$k]['intoOrderRate']= $returnData[$k]['orderNum'] ? sprintf('%.2f', $returnData[$k]['orderNum']/$returnData[$k]['intoNum']*100).'%' : '0%';
            // 花费
            $returnData[$k]['cost'] = $channelCost[$key]['cost'] ? sprintf('%.2f',$channelCost[$key]['cost']) : '0.00';
            // 成本
            $returnData[$k]['mkzCb'] = $returnData[$k]['cost'] ? sprintf('%.2f', $returnData[$k]['cost']/$returnData[$k]['mkz']) : '0.00';
            $returnData[$k]['yxCb']  = $returnData[$k]['cost'] ? sprintf('%.2f', $returnData[$k]['cost']/$returnData[$k]['yx']) : '0.00';
            $returnData[$k]['intoCb']= $returnData[$k]['cost'] ? sprintf('%.2f', $returnData[$k]['cost']/$returnData[$k]['intoNum']) : '0.00';
            $returnData[$k]['ddCb']  = $returnData[$k]['cost'] ? sprintf('%.2f', $returnData[$k]['cost']/$returnData[$k]['orderNum']) : '0.00';
            // 营业额 一销
            $returnData[$k]['yixiao']= $ddNum[$key]['yixiao'] ? $ddNum[$key]['yixiao'] : '0.00';
            // 实收金额
            $returnData[$k]['ssje'] = $money[$key]['ssje'] ? sprintf('%.2f',$money[$key]['ssje']) : '0.00';
            // 均价
            $returnData[$k]['orderAvg']= $returnData[$k]['yixiao'] ? sprintf('%.2f', $returnData[$k]['yixiao']/$returnData[$k]['orderNum']) : '0.00';
            // ROI
            $returnData[$k]['ROI'] = $returnData[$k]['ssje'] ? sprintf('%.2f',intval($returnData[$k]['ssje'])/intval($returnData[$k]['cost'])) : '';
            // 总计
            $arr['mkz']   += $returnData[$k]['mkz'];
            $arr['yx']    += $returnData[$k]['yx'];
            $arr['wx']    += $returnData[$k]['wx'];
            $arr['dd']    += $returnData[$k]['dd'];
            $arr['yjwx']  += $returnData[$k]['yjwx'];
            $arr['wjwx']  += $returnData[$k]['wjwx'];
            $arr['dtgwx'] += $returnData[$k]['dtgwx'];
            $arr['yypz']  += $returnData[$k]['yypz'];
            $arr['intoNum']+= $returnData[$k]['intoNum'];
            $arr['orderNum']+= $returnData[$k]['orderNum'];
            $arr['cost']  += $returnData[$k]['cost'];
            $arr['yixiao']+= $returnData[$k]['yixiao'];
            $arr['ssje']  += $returnData[$k]['ssje'];
            $arr['yhf']   += $yhf[$key]['yhf'];
            $arr['yihf']  += $yihf[$key]['yihf'];
            $arr['lastTrac']+=$lastTrac[$key]['lastTrac'];
        }
        // 加微率
        $arr['wxRate'] = $arr['yjwx'] ? sprintf('%.2f', $arr['yjwx']/$arr['yx']*100).'%' : '0%';
        // 有效、无效、待定 率
        $arr['yxRate'] = $arr['yx'] ? sprintf('%.2f', $arr['yx']/$arr['mkz']*100).'%' : '0%';
        $arr['wxRate'] = $arr['wx'] ? sprintf('%.2f', $arr['wx']/$arr['mkz']*100).'%' : '0%';
        $arr['ddRate'] = $arr['dd'] ? sprintf('%.2f', $arr['dd']/$arr['mkz']*100).'%' : '0%';
        // 进店率
        $arr['mkzIntoRate']= $returnData[$k]['intoNum'] ? sprintf('%.2f', $returnData[$k]['intoNum']/$info[$key]['mkz'] * 100).'%' : '0%';
        $arr['yxIntoRate'] = $returnData[$k]['intoNum'] ? sprintf('%.2f', $returnData[$k]['intoNum']/$info[$key]['yx'] * 100).'%' : '0%';
        // 成交率
        $arr['mkzOrderRate'] = $arr['orderNum'] ? sprintf('%.2f', $arr['orderNum']/$arr['mkz']*100).'%' : '0%';
        $arr['yxOrderRate']  = $arr['orderNum'] ? sprintf('%.2f', $arr['orderNum']/$arr['yx']*100).'%' : '0%';
        $arr['intoOrderRate']= $arr['orderNum'] ? sprintf('%.2f', $arr['orderNum']/$arr['intoNum']*100).'%' : '0%';
        // 成本
        $arr['mkzCb'] = $arr['cost'] ? sprintf('%.2f', $arr[$k]['cost']/$arr['mkz']*100).'%' : '0%';
        $arr['yxCb']  = $arr['cost'] ? sprintf('%.2f', $arr[$k]['cost']/$arr['yx']*100).'%' : '0%';
        $arr['intoCb']= $arr['cost'] ? sprintf('%.2f', $arr[$k]['cost']/$arr['intoNum']*100).'%' : '0%';
        $arr['ddCb']  = $arr['cost'] ? sprintf('%.2f', $arr[$k]['cost']/$arr['orderNum']*100).'%' : '0%';
        // 均价
        $arr['orderAvg']= $arr['yixiao'] ? sprintf('%.2f', $arr['yixiao']/$arr['orderNum']*100) : '0%';
        // ROI
        $arr['ROI'] = $arr['ssje'] ? sprintf('%.2f',intval($arr['ssje'])/intval($arr['cost'])) : '';
        // 维护率 算法：指定时间段内该回访且已经回访的客资量/指定时间段内应该二次或三次或以上该跟客资回访的客资量
        // $arr['whRate']= $arr['yhf'] ? sprintf('%.2f', $arr['yhf']/$arr['yihf']*100).'%' : '0%';
        // 响应率 算法：按系统录入时间算，指定时间内（一般可以自定义为佳）是否有操作。
        $arr['xyRate']= $arr['lastTrac'] ? sprintf('%.2f', $arr['lastTrac']/$arr['mkz']*100).'%' : '0%';
        array_push($returnData,$arr);
        if($data['need'] == 1){
            $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['sql1'] = $sql1;
        $return['sql2'] = $sql2;
        $return['sql3'] = $sql3;

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

    public function show(){
        /*
        $did = strtolower(input('param.did'));
        $info = db('crm_s_report_form')->alias('f')->join('crm_s_report_detail d','d.form_id=f.id')->where('d.id',$did)->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name')->find();*/
        $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();

        $city = db('crm_s_shop_address')->where('shop_code',$info['shop_code'])->where('type',2)->select();

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

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

        //来源渠道
        $channel = getChannelJson($info['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'=>$info['shop_code']]);
        $this->assign('title', $title);
        $this->assign('did', $info['id']);
        $this->assign('menu_code', $menu_code);
        return $this->fetch($display);
    }

}