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

class Qqbaby_ds_promote_operation extends Report {

    private $level=1;
    private $type=3;
    private $code='code1594952182';

    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);
        $recTime = microtime(true).'<br />';

        $data = input();

        $info = db('crm_s_report_form')->where('code', $data['code'])->field('code,report_name,report_class_name')->find();
        if (!$info || empty($data['code'])) {
            $this->error('没有此报表。');
        }
        $shop_code = $data['shop_code'] ? $data['shop_code'] : session('shop_code');

        $start = $data['start'] ? $data['start'] : date('Y-m-d',strtotime());
        $end = $data['end'] ? $data['end'] : date('Y-m-d',strtotime());

        $start_t = str_replace("-", "", $start);
        $end_t = str_replace("-", "", $end);

        $d1 = strtotime($start);
        $d2 = strtotime($end);
        $Days = round(($d2-$d1)/3600/24);
        if ($Days > 31) {
            $return['code'] = 100;
            $return['msg'] = '日期范围请选择一个月31天内。';
            return json($return);
        }
        $where['is_status'] = 1;
        $where['c_from'] = 1;
        $where['c.add_shop_code'] = $shop_code;
        // 订单用sql
        $whereSql = " c.c_from=1 AND c.is_status=1 AND c.add_shop_code='$shop_code' AND p.paymentdate BETWEEN '$start_t' AND '$end_t' and o.payment_money <> 0";
        // 实收用sql
        $payStart = date('Ymd',strtotime($start));
        $payEnd = date('Ymd',strtotime($end));
        $whereSqlP= " AND c.c_from=1 AND c.is_status=1 AND c.add_shop_code='$shop_code' AND p.paymentdate BETWEEN '$payStart' AND '$payEnd'";
        // 城市
        if($data['city']){
            $where['c.city_id'] = $data['city'];
            $costWhere['c.city_id'] = ['in',$data['city']];
            // 订单用sql
            $city_id = $data['city'];
            $whereSql .= " AND c.city_id=$city_id";
            $whereSqlP.= " AND c.city_id=$city_id";
        }
        // 登记人
        if($data['dj_staff']){
            $where['c.dj_staff_id'] = ['in',$data['dj_staff']];
            $costWhere['c.staff_id'] = ['in',$data['dj_staff']];
            // 订单用sql
            $djStaffArr = $data['dj_staff'];
            $djStaffStr = '';
            foreach ($djStaffArr as $k => $v) {
                $djStaffStr .= $v.',';
            }
            $djStaffStr = trim($djStaffStr,',');
            $whereSql .= " AND c.dj_staff_id IN ($djStaffStr)";
            $whereSqlP.= " AND c.dj_staff_id IN ($djStaffStr)";
        }
        // 渠道
        if($data['channel']){
            $where['c.customer_channel'] = ['in', $data['channel']];
            $costWhere['c.channel_name'] = ['in', $data['channel']];
            // 订单用sql
            $channelArr = explode(',',$data['channel']);
            $channelstr = '';
            foreach ($channelArr as $k => $v) {
                $channelstr .= "'".$v."',";
            }
            $channelstr = trim($channelstr,',');
            $whereSql .= " AND c.customer_channel IN ($channelstr)";
            $whereSqlP.= " AND c.customer_channel IN ($channelstr)";
        }
        //品牌
        if($data['b_id']){
            $where['c.b_id'] = $data['b_id'];
            $costWhere['c.b_id'] = ['in',$data['b_id']];
            // 订单用sql
            $bid = $data['b_id'];
            $whereSql .= " AND c.b_id=$bid";
            $whereSqlP.= " AND c.b_id=$bid";
        }
        //消费类型
        if($data['consultation_type_id']){
            $where['c.consultation_type_id'] = $data['consultation_type_id'];
            // 订单用sql
            $consultation_type_id = $data['consultation_type_id'];
            $whereSql .= " AND c.consultation_type_id=$consultation_type_id";
            $whereSqlP.= " AND c.consultation_type_id=$consultation_type_id";
        }
        $recTime .= microtime(true).'<br />';
        $returnData = [];
        // 毛客资
        $customerinfo = db('crm_c_customerinfo')->alias("c")
            ->join('crm_s_channel ch', 'ch.channel_name=c.customer_channel','left')
            ->where($where)
            ->where('c.dj_day','between',[$start,date('Y-m-d 23:59:59',strtotime($end))])
            ->field('c.id,ch.channel_info_name,c.customer_channel,c.dj_staff')
            ->group('c.id')
            ->order('ch.order_sort ASC, ch.id ASC')
            ->select();
        $sql1 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        $order = [];
        foreach ($customerinfo as $k => $v) {
            $returnData[$v['customer_channel'].$v['dj_staff']]['qd_ej'] = $v['channel_info_name'];
            $returnData[$v['customer_channel'].$v['dj_staff']]['qd_sj'] = $v['customer_channel'];
            $returnData[$v['customer_channel'].$v['dj_staff']]['dj_staff'] = $v['dj_staff'];
            $returnData[$v['customer_channel'].$v['dj_staff']]['kz'] += 1;
        }
        // 已分配
        $customerinfo_fp = db('crm_c_customerinfo')->alias("c")
            ->join('crm_s_channel ch', 'ch.channel_name=c.customer_channel','left')
            ->where($where)
            ->where('c.dj_day','between',[$start,date('Y-m-d 23:59:59',strtotime($end))])
            ->where('c.is_fp','=',1)
            ->field('c.id,ch.channel_info_name,c.customer_channel,c.dj_staff')
            ->group('c.id')
            ->order('ch.order_sort ASC, ch.id ASC')
            ->select();
        // $sql6 = db()->getLastSql();
        // $recTime .= microtime(true).'<br />';
        $order_fp = [];
        foreach ($customerinfo_fp as $k => $v) {
            $returnData[$v['customer_channel'].$v['dj_staff']]['fp'] += 1;
        }
        // 有效客资
        $yxInfo = db('crm_c_customerinfo')->alias("c")
            ->join('crm_s_channel ch', 'ch.channel_name=c.customer_channel','left')
            ->where($where)->where('is_valid',1)
            ->where('c.dj_day','between',[$start,date('Y-m-d 23:59:59',strtotime($end))])
            // ->where('c.yx_day','between',[$start,$end])
            ->group('c.id')
            ->field('c.id,ch.channel_info_name,c.customer_channel,c.dj_staff')
            ->select();
        $sql2 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        foreach ($yxInfo as $k => $v) {
            if ($v['customer_channel']&&$v['dj_staff']) {
                $returnData[$v['customer_channel'].$v['dj_staff']]['qd_ej'] = $v['channel_info_name'];
                $returnData[$v['customer_channel'].$v['dj_staff']]['qd_sj'] = $v['customer_channel'];
                $returnData[$v['customer_channel'].$v['dj_staff']]['dj_staff'] = $v['dj_staff'];
                $returnData[$v['customer_channel'].$v['dj_staff']]['kz_yx'] += 1;
            }
        }
        // 花费
        $channelCost = db('crm_s_channel_cost')->alias("c")
            ->join('crm_s_channel ch', 'ch.channel_name=c.channel_name','left')
            ->where('c.cost_date','between',[$start,$end])
            ->where($costWhere)
            ->field('c.cost_staff,c.channel_name,ch.channel_info_name,c.cost_date,c.cost')->select();
        $sql3 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        foreach ($channelCost as $k => $v) {
            if ($v['channel_name'] && $v['cost_staff']) {
                $returnData[$v['channel_name'].$v['cost_staff']]['qd_ej'] = $v['channel_info_name'];
                $returnData[$v['channel_name'].$v['cost_staff']]['qd_sj'] = $v['channel_name'];
                $returnData[$v['channel_name'].$v['cost_staff']]['dj_staff'] = $v['cost_staff'];
                $returnData[$v['channel_name'].$v['cost_staff']]['hf'] += $v['cost'];
            }
        }
        // 订单 
        // $field='c.customer_channel,c.dj_staff,co.target_time,co.order_sn,o.orderId,o.total_money,o.payment_money,o.nopayment_money,o.bargain_money,o.yixiaokakou,o.yixiaoshishou';
        // // echo $whereSql;die;
        // $sql4 = 'SELECT '.$field.' FROM crm_c_customerinfo c LEFT JOIN crm_c_customer_order co ON c.id=co.customer_id LEFT JOIN c_customerorder o ON co.order_sn = o.orderId  WHERE c.dj_staff!="" AND o.id IS NOT NULL and '.$whereSql;
        $field='c.customer_channel,c.dj_staff,o.orderId,o.total_money,o.payment_money,o.nopayment_money,o.bargain_money,o.yixiaokakou,o.yixiaoshishou';
        $sql4 = 'SELECT '.$field.' FROM crm_c_customerinfo c LEFT JOIN c_customerorder o ON c.id = o.crm_customer_id LEFT JOIN c_paymentcontent p ON o.orderId=p.orderId  WHERE c.dj_staff!="" AND o.id IS NOT NULL and '.$whereSql;
        
        $orderData = db()->query($sql4);
        $recTime .= microtime(true).'<br />';
        $orderIds = '';
        foreach ($orderData as $k => $v) {
            // $returnData[$v['customer_channel'].$v['dj_staff']]['qd_ej'] = '';
            $returnData[$v['customer_channel'].$v['dj_staff']]['qd_sj'] = $v['customer_channel'];
            $returnData[$v['customer_channel'].$v['dj_staff']]['dj_staff'] = $v['dj_staff'];

            $returnData[$v['customer_channel'].$v['dj_staff']]['dd_zong']+= 1;
            // 一销金额
            $returnData[$v['customer_channel'].$v['dj_staff']]['yx_tx'] += $v['bargain_money'];
            // 一销欠款
            $returnData[$v['customer_channel'].$v['dj_staff']]['yx_qk'] += $v['nopayment_money'];
            $returnData[$v['customer_channel'].$v['dj_staff']]['ids'][] = $v['orderId'];
            $orderIds .= $v['orderId'].',';
        }
        $orderIds = trim($orderIds,',');
        // 区分实收订单
        $payInfosOrder = db('c_paymentcontent')->alias('p')
            ->join('c_paymenttype t', 't.typecode=p.paytype','left')
            ->where('p.orderId','in',$orderIds)
            ->field('p.orderId,p.payment_money,t.typename')
            ->select();
        foreach ($returnData as $key => $val) {
            $ids = $val['ids'];
            foreach ($payInfosOrder as $k => $v) {
                if ( in_array($v['orderId'],$ids) ) {
                    if ($v['typename'] != 'VIP卡') {
                        $returnData[$key]['ssadd'][$v['orderId']] = 1;
                    }
                }
            }
        }
        // 收款 只算一销
        $yjstart = date('Ymd',strtotime($start));
        $yjend = date('Ymd',strtotime($end));
        // $sql5 = 'SELECT `c`.`customer_channel`,`c`.`dj_staff`,`c`.`dj_day`,`p`.`orderId`,`p`.`payment_money`,`p`.`paytype`,`p`.`isshishou` FROM crm_c_customerinfo c LEFT JOIN crm_c_customer_order co ON `c`.`id`=`co`.`customer_id` LEFT JOIN `c_paymentcontent` p ON `co`.`order_sn`=`p`.`orderId`  WHERE `p`.`id` IS NOT NULL  AND `p`.`suoshu` ="前期款" '.$whereSqlP;
        $sql5 = 'SELECT `c`.`customer_channel`,`c`.`dj_staff`,`c`.`dj_day`,`p`.`orderId`,`p`.`payment_money`,`p`.`paytype`,`p`.`isshishou` FROM crm_c_customerinfo c LEFT JOIN c_customerorder o ON `c`.`id`=`o`.`crm_customer_id` LEFT JOIN `c_paymentcontent` p ON `o`.`orderId`=`p`.`orderId`  WHERE `p`.`id` IS NOT NULL  AND `p`.`suoshu` ="前期款" '.$whereSqlP;
        $payInfos = db()->query($sql5);
        foreach ($payInfos as $k => $v) {
        	if ($v['paytype'] == 'VIP卡') {
        		$returnData[$v['customer_channel'].$v['dj_staff']]['vip'] += $v['payment_money'];
        	}
        	// if ($v['paytype'] == '刷卡') {
         //        $returnData[$v['customer_channel'].$v['dj_staff']]['ssje'] += $v['payment_money'];
         //    }
         //    if ($v['paytype'] == '现金') {
         //        $returnData[$v['customer_channel'].$v['dj_staff']]['ssje'] += $v['payment_money'];
         //    }
         //    if ($v['paytype'] == '网络付款') {
         //        $returnData[$v['customer_channel'].$v['dj_staff']]['ssje'] += $v['payment_money'];
         //    }
         //    if ($v['paytype'] == '免单') {
         //        $returnData[$v['customer_channel'].$v['dj_staff']]['ssje'] += $v['payment_money'];
         //    }
            if ($v['isshishou'] == '实收') {
                $returnData[$v['customer_channel'].$v['dj_staff']]['ssje'] += $v['payment_money'];

                if (strtotime($v['dj_day']) < $d1) {
                    $returnData[$v['customer_channel'].$v['dj_staff']]['ssje_hd'] += $v['payment_money'];
                }else{
                    $returnData[$v['customer_channel'].$v['dj_staff']]['ssje_dy'] += $v['payment_money'];
                }
            }
        }

        //邀约进店量和实际进店量
        $jindian = db('crm_c_customer_intostore i')
            ->join('crm_c_customerinfo c','c.id=i.cid','LEFT')
            ->where($where)
            ->where('c.dj_day','between',[$start,date('Y-m-d 23:59:59',strtotime($end))])
            ->field('c.customer_channel,c.dj_staff,i.yjd_day,i.yjd_day,i.is_intostore')
            ->select();
        $sql6 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        foreach ($jindian as $k => $v) {
            //邀约进店量
            if ($v['yjd_day']) {
                $returnData[$v['customer_channel'].$v['dj_staff']]['yjd'] += 1;
            }
            //实际进店量
            if ($v['syjd_day']) {
                $returnData[$v['customer_channel'].$v['dj_staff']]['syjd'] += 1;
            }
        }

        foreach ($returnData as $k => $v) {

            // 实收订单
            $returnData[$k]['ssdd'] = count($v['ssadd']);
            // VIP订单
            $returnData[$k]['kkdd'] = $v['dd_zong'] - count($v['ssadd']);
            // 客资有效率
            $returnData[$k]['kz_yxl'] = $returnData[$k]['kz_yx'] ? (sprintf('%.2f', $returnData[$k]['kz_yx']/$returnData[$k]['kz']*100)).'%' : '0%';
            // 毛客资成本
            $returnData[$k]['kz_cb'] = $returnData[$k]['hf'] ? (sprintf('%.2f', $returnData[$k]['hf']/$returnData[$k]['kz'])) : '0';
            // 有效客资成本
            $returnData[$k]['kz_yx_cb'] = $returnData[$k]['hf'] ? (sprintf('%.2f', $returnData[$k]['hf']/$returnData[$k]['kz_yx'])) : '0';
            // '毛客资转实收订单率', = 实收订单/毛客资
            $returnData[$k]['ssddl'] = $returnData[$k]['ssdd'] ? (sprintf('%.2f', $returnData[$k]['ssdd']/$returnData[$k]['kz']*100)).'%' : '0%';
            // '毛客资转卡扣订单率', 卡扣订单/毛客资
            $returnData[$k]['kkddl'] = $returnData[$k]['kkdd'] ? (sprintf('%.2f', $returnData[$k]['kkdd']/$returnData[$k]['kz']*100)).'%' : '0%';
            // '毛客资转单率', = 订单总数/毛客资
            $returnData[$k]['kzzhl'] = $returnData[$k]['dd_zong'] ? (sprintf('%.2f', $returnData[$k]['dd_zong']/$returnData[$k]['kz']*100)).'%' : '0%';
            // '有效客资转实收订单率', = 实收订单/有效客资
            $returnData[$k]['ssddl_yx'] = $returnData[$k]['ssdd'] ? (sprintf('%.2f', $returnData[$k]['ssdd']/$returnData[$k]['kz_yx']*100)).'%' : '0%';
            // '有效客资转卡扣订单率', = 卡扣订单/有效客资
            $returnData[$k]['kkddl_yx'] = $returnData[$k]['kkdd'] ? (sprintf('%.2f', $returnData[$k]['kkdd']/$returnData[$k]['kz_yx']*100)).'%' : '0%';
            // '有效客资转单率', = 订单总数/有效客资
            $returnData[$k]['kzzhl_yx'] = $returnData[$k]['dd_zong'] ? (sprintf('%.2f', $returnData[$k]['dd_zong']/$returnData[$k]['kz_yx']*100)).'%' : '0%';
            // {field: 'ssjj', title: '实收均价', =实收业绩/实收订单
            $returnData[$k]['ssjj'] = $returnData[$k]['ssje'] ? (sprintf('%.2f', $returnData[$k]['ssje']/$returnData[$k]['ssdd'])) : '0';
            // {field: 'zjjdl', title: '均价', =一销金额/订单总数
            $returnData[$k]['zjjdl'] = $returnData[$k]['yx_tx'] ? (sprintf('%.2f', $returnData[$k]['yx_tx']/$returnData[$k]['dd_zong'])) : '0';
            // {field: 'rqi', title: 'ROI', 实收业绩/花费 ? 花费/实收业绩
            $returnData[$k]['rqi'] = $returnData[$k]['ssje'] ? (sprintf('%.2f', $returnData[$k]['ssje']/$returnData[$k]['hf'])) : '0';

            // 客资分配率
            $returnData[$k]['fp_lv'] = $returnData[$k]['fp'] ? (sprintf('%.2f', $returnData[$k]['fp']/$returnData[$k]['kz']*100)).'%' : '0%';

            $returnData[$k]['yjd'] = $v['yjd'] ?: 0;
            $returnData[$k]['syjd'] = $v['syjd'] ?: 0;

            // 总计各种数据
            $all['qd_ej'] = '';
            $all['qd_sj'] = '';
            $arr['dj_staff'] = '总计';
            $arr['hf'] += $v['hf'];
            $arr['kz'] += $v['kz'];
            $arr['fp'] += $v['fp'];
            $arr['kz_yx'] += $v['kz_yx'];
            $arr['dd_zong'] += $v['dd_zong'];
            $arr['ssdd'] += $returnData[$k]['ssdd'];
            $arr['kkdd'] += $returnData[$k]['kkdd'];
            $arr['yx_tx'] += $v['yx_tx'];
            $arr['yx_qk'] += $v['yx_qk'];
            $arr['ssje'] += $returnData[$k]['ssje'];
            $arr['ssje_dy'] += $returnData[$k]['ssje_dy'];
            $arr['ssje_hd'] += $returnData[$k]['ssje_hd'];
            $arr['vip'] += $returnData[$k]['vip'];
            $arr['ssjj'] += $v['ssjj'];
            $arr['zjjdl'] += $v['zjjdl'];
            $arr['yjd'] += $v['yjd'];
            $arr['syjd'] += $v['syjd'];
        }
        $returnData = array_values($returnData);
        
        // 总计各种率及成本
        $arr['kz_yxl'] = $arr['kz_yx'] ? (sprintf('%.2f', $arr['kz_yx']/$arr['kz']*100)).'%' : '0%';
        $arr['kz_cb'] = $arr['hf'] ? (sprintf('%.2f', $arr['hf']/$arr['kz'])) : '0';
        $arr['kz_yx_cb'] = $arr['hf'] ? (sprintf('%.2f', $arr['hf']/$arr['kz_yx'])) : '0';
        $arr['ssddl'] = $arr['ssdd'] ? (sprintf('%.2f', $arr['ssdd']/$arr['kz']*100)).'%' : '0%';
        $arr['kkddl'] = $arr['kkdd'] ? (sprintf('%.2f', $arr['kkdd']/$returnData[$k]['kz']*100)).'%' : '0%';
        $arr['kzzhl'] = $arr['dd_zong'] ? (sprintf('%.2f', $arr['dd_zong']/$arr['kz']*100)).'%' : '0%';
        $arr['ssddl_yx'] = $arr['ssdd'] ? (sprintf('%.2f', $arr['ssdd']/$arr['kz_yx']*100)).'%' : '0%';
        $arr['kkddl_yx'] = $arr['kkdd'] ? (sprintf('%.2f', $arr['kkdd']/$arr['kz_yx']*100)).'%' : '0%';
        $arr['kzzhl_yx'] = $arr['dd_zong'] ? (sprintf('%.2f', $arr['dd_zong']/$arr['kz_yx']*100)).'%' : '0%';
        $arr['rqi'] = $arr['ssje'] ? (sprintf('%.2f', $arr['ssje']/$arr['hf'])) : '0';
        array_push($returnData, $arr);

        foreach ($returnData as $k => $v) {
            if(empty($v['qd_ej']) && empty($v['qd_sj']) && empty($v['dj_staff'])){
                unset($returnData[$k]);
            }
        }

        $return['sql1'] = $sql1;
        $return['sql2'] = $sql2;
        $return['sql3'] = $sql3;
        $return['sql4'] = $sql4;
        $return['sql5'] = $sql5;
        $return['sql6'] = $sql6;
        $return['recTime'] = $recTime;
        $return['code'] = 0;
        $return['msg']  = '';
        $return['data'] = $returnData;
        $return['count']= count($returnData);
        return json($return);
    }
    public function show(){
        $menu_code = strtolower(input('param.menu_code'));
        $info = db('crm_s_report_form')->where('code', $menu_code)->field('code,report_name,report_class_name')->find();
        if (!$info) {
            $this->error('没有此报表。');
        }

        $shop_code = session('shop_code');
        $shop= controller('crm/Customer','lib')->get_shop(['shop_code'=>$shop_code]);
        // 来源渠道
        $channel = getChannelJson($shop_code);
        $this->assign("channel", json_encode($channel));
        // crm省 列表
        $data['type']=1;
        $data['shop_code']= $shop_code;
        $province=controller("crm/Customer","lib")->address($data);
        $this->assign("province",$province);
        // 只有一个省 查这个省下的城市
        if (count($province)==1) {
            $data['type'] = 2;
            $data['pid']  = $province[0]['id'];
            $staCity = controller("crm/Customer", "lib")->address($data);
            $this->assign("staCity",$staCity);
        }
        // 品牌
        $brandArr = db('crm_s_brandset')->select();
        $this->assign('brandArr',$brandArr);
        // 消费类型
        $consumptionType = controller("Customer", "lib")->consumptionType(['brandclass_id'=>session('brandclass_id')]);
        $this->assign("consumptionType", $consumptionType);

        $mc = new \app\common\lib\mcInterface();
        // 部门
        $departmentList = $mc->getDepartmentByShopcode($shop_code);
        foreach ($departmentList as $key => $value) {
            if ($value['department_name']==session('department_name')) {
                $departmentList[$key]['selected']=1;
                $departmentId = $value['id'];
            }
        }
        $this->assign("departmentList",$departmentList);
        $staffParam['department_id'] = $departmentId;
        $staffParam['get_main'] = 1;
        // 登记人
        $djStaffList = $mc->getStaffByDepartment($staffParam);
        $this->assign("djStaffList",$djStaffList);
        // 追踪人
        // $salesStaffList = $mc->getStaffByDepartment($staffParam);
        // $this->assign("salesStaffList",$salesStaffList);

        $display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.strtolower($info['report_class_name']).'.html';

        $this->assign('title', $shop[0]['shop_name'].'-'.$info['report_name']);
        $this->assign('code', $info['code']);
        // 默认本月
        $this->assign('start', date('Y-m-01'));
        $this->assign('end', date('Y-m-d'));
        return $this->fetch($display);
    }

}