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

class Qqbaby_telemarketing extends Report {

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

    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'";
        //零选量sql
        $sqllxl= " sp.sptend_day  BETWEEN '$payStart' AND '$payEnd' AND b.blankoutannal <> 1 and a.c_from=1 AND a.is_status=1 AND a.add_shop_code='$shop_code'";

        //消费类型
        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";
            $sqllxl .= " AND a.consultation_type_id=$consultation_type_id";
        }

        //客户分区
        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";
            $sqllxl .= " AND a.consultation_type_id=$consultation_type_id";
        }

        //人员
        if($data['sales_staff_id']){
            $where['c.sales_staff_id'] = $data['sales_staff_id'];
            // 订单用sql
            $sales_staff_id = $data['sales_staff_id'];
            $whereSql .= " AND c.sales_staff_id=$sales_staff_id";
            $whereSqlP .= " AND c.sales_staff_id=$sales_staff_id";
            $sqllxl .= " AND a.sales_staff_id=$sales_staff_id";
        }

        //渠道
        if($data['channel_id']){
            $channels = explode(',',$data['channel_id']);
            $channelss ='"'.implode('","',$channels).'"';

            $where['c.customer_channel'] = ['in',$channels];
            // 订单用sql
            $whereSql .= " AND c.customer_channel in($channelss)";
            $whereSqlP .= " AND c.customer_channel in($channelss)";
            $sqllxl .= " AND a.customer_channel in($channelss)";
        }
        
        $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.sales_staff,c.shop_name,c.sign_name')
            ->group('c.id')
            ->order('ch.order_sort ASC, ch.id ASC')
            ->select();
        $sql1 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        $order = [];
        $yuangong = '';
        foreach ($customerinfo as $k => $v) {
            //店面
            $returnData[$v['customer_channel'].$v['sales_staff']]['shop_name'] = $v['shop_name'];
            //姓名
            $returnData[$v['customer_channel'].$v['sales_staff']]['sales_staff'] = $v['sales_staff'];
            //二级渠道
            $returnData[$v['customer_channel'].$v['sales_staff']]['qd_ej'] = $v['channel_info_name'];
            //三级渠道
            $returnData[$v['customer_channel'].$v['sales_staff']]['qd_sj'] = $v['customer_channel'];
            //毛客资
            $returnData[$v['customer_channel'].$v['sales_staff']]['kz'] += 1;
            //客资标记 A
            if ($v['sign_name'] == "A") {
                $returnData[$v['customer_channel'].$v['sales_staff']]['kz_bj_a'] += 1;
            }
            //客资标记 B
            if ($v['sign_name'] == "B") {
                $returnData[$v['customer_channel'].$v['sales_staff']]['kz_bj_b'] += 1;
            }
            //客资标记 C
            if ($v['sign_name'] == "C") {
                $returnData[$v['customer_channel'].$v['sales_staff']]['kz_bj_c'] += 1;
            }
            //客资标记 D
            if ($v['sign_name'] == "D") {
                $returnData[$v['customer_channel'].$v['sales_staff']]['kz_bj_d'] += 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))])
            ->group('c.id')
            ->field('c.id,ch.channel_info_name,c.customer_channel,c.sales_staff')
            ->select();
        $sql2 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        foreach ($yxInfo as $k => $v) {
            if ($v['customer_channel']&&$v['sales_staff']) {
                $returnData[$v['customer_channel'].$v['sales_staff']]['qd_ej'] = $v['channel_info_name'];
                $returnData[$v['customer_channel'].$v['sales_staff']]['qd_sj'] = $v['customer_channel'];
                $returnData[$v['customer_channel'].$v['sales_staff']]['sales_staff'] = $v['sales_staff'];
                //有效客资量
                $returnData[$v['customer_channel'].$v['sales_staff']]['kz_yx'] += 1;
            }
        }


        // 订单 
        $field='c.customer_channel,c.sales_staff,o.orderId,o.total_money,o.payment_money,o.nopayment_money,o.bargain_money,o.yixiaokakou,o.yixiaoshishou,c.dj_day,c.customer_channel';
        $sql3 = '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.sales_staff!="" AND o.id IS NOT NULL and '.$whereSql;
        
        $orderData = db()->query($sql3);
        $recTime .= microtime(true).'<br />';
        $orderIds = '';
        foreach ($orderData as $k => $v) {
            // $returnData[$v['customer_channel'].$v['sales_staff']]['qd_ej'] = '';
            $returnData[$v['customer_channel'].$v['sales_staff']]['qd_sj'] = $v['customer_channel'];
            $returnData[$v['customer_channel'].$v['sales_staff']]['sales_staff'] = $v['sales_staff'];

            // 总订单
            $returnData[$v['customer_channel'].$v['sales_staff']]['dd_zong']+= 1;

            if (strtotime($v['dj_day']) < $d1) {
                //回单
                $returnData[$v['customer_channel'].$v['sales_staff']]['hd'] += 1;
            }else{
                //当月订单
                $returnData[$v['customer_channel'].$v['sales_staff']]['dy'] += 1;
            }

            //转介绍订单
            if ($v['customer_channel'] == "转介绍") {
                $returnData[$v['customer_channel'].$v['sales_staff']]['zjs'] += 1;
            }
        }

        // 收款 只算一销
        $yjstart = date('Ymd',strtotime($start));
        $yjend = date('Ymd',strtotime($end));

        $sql4 = 'SELECT `c`.`customer_channel`,`c`.`sales_staff`,`c`.`dj_day`,`p`.`orderId`,`p`.`payment_money`,`p`.`paytype`,`p`.`isshishou`,`c`.`customer_channel` 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($sql4);
        foreach ($payInfos as $k => $v) {
            //总卡销
        	if ($v['paytype'] == 'VIP卡') {
        		$returnData[$v['customer_channel'].$v['sales_staff']]['vip'] += $v['payment_money'];
        	}

            if ($v['isshishou'] == '实收') {
                //总实收
                $returnData[$v['customer_channel'].$v['sales_staff']]['ssje'] += $v['payment_money'];

                if (strtotime($v['dj_day']) < $d1) {
                    //回单实收
                    $returnData[$v['customer_channel'].$v['sales_staff']]['ssje_hd'] += $v['payment_money'];
                }else{
                    //当月订单实收
                    $returnData[$v['customer_channel'].$v['sales_staff']]['ssje_dy'] += $v['payment_money'];
                }
            }

            //转介绍订单实收
            if ($v['customer_channel'] == "转介绍") {
                $returnData[$v['customer_channel'].$v['sales_staff']]['zjs_ss'] += $v['payment_money'];
            }
        }

        //上门
        $sanmen = db('crm_c_customerinfo')->alias('c')
            ->join('crm_l_pc_log l','c.id=l.cid', 'left')
            ->join('c_customerorder o','l.id=o.crm_visit_id','LEFT')
            ->join('c_paymentcontent p','p.orderId=o.orderId','LEFT')
            ->where($where)
            ->where('l.sm_type','拍摄')
            ->where('l.pc_type',2)//上门拍
            ->where('l.pc_time','between',[$start,$end])
            ->field('c.customer_channel,c.sales_staff,l.status,p.paytype,p.isshishou,p.payment_money,o.id as oid')
            ->select();
        $sql5 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        foreach ($sanmen as $key => $value) {
            //上门邀约量
            $returnData[$v['customer_channel'].$v['sales_staff']]['smyyl'] += 1;
            //上门拍摄量
            if ($v['status'] == 2) {
                $returnData[$v['customer_channel'].$v['sales_staff']]['smpsl'] += 1;
            }
            //上门订单量
            if (!empty($v['oid'])) {
                $returnData[$v['customer_channel'].$v['sales_staff']]['smddl'] += 1;
            }
            //邀约上门卡扣金额
            if ($v['paytype'] == 'VIP卡') {
                $returnData[$v['customer_channel'].$v['sales_staff']]['yysm_vip'] += $v['payment_money'];
            }
            //邀约上门实收金额
            if ($v['isshishou'] == '实收') {
                $returnData[$v['customer_channel'].$v['sales_staff']]['yysm_ss'] += $v['payment_money'];
            }
        }

        //引客
        $yingke = db('crm_c_customerinfo')->alias("c")
            ->join('crm_l_pc_log l','c.id=l.cid', 'left')
            ->join('c_customerorder o','l.id=o.crm_visit_id','LEFT')
            ->join('c_paymentcontent p','p.orderId=o.orderId','LEFT')
            ->where($where)
            ->where('l.sm_type','拍摄')
            ->where('l.pc_type',1)//到店拍
            ->where('c.yjd_day','between',[$start,date('Y-m-d 23:59:59',strtotime($end))])
            ->field('c.customer_channel,c.sales_staff,l.status,p.paytype,p.isshishou,p.payment_money,p.suoshu')
            ->group('c.id')
            ->select();
        $sql6 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        foreach ($customerinfo as $k => $v) {
            //引客预约量
            $returnData[$v['customer_channel'].$v['sales_staff']]['ykyyl'] += 1;
            //引客拍摄量
            if ($v['status'] == 2) {
                $returnData[$v['customer_channel'].$v['sales_staff']]['ykpsl'] += 1;
            }
            //引客进店前期实收金额
            if ($v['suoshu'] == '前期款' || $v['suoshu'] == '前期补款') {
                $returnData[$v['customer_channel'].$v['sales_staff']]['yk_qq'] += $v['payment_money'];
            }
            //引客进店后期实收金额
            if ($v['suoshu'] == '后期款' || $v['suoshu'] == '后期补款') {
                $returnData[$v['customer_channel'].$v['sales_staff']]['yk_hq'] += $v['payment_money'];
            }

        }

        // 选片 
        $xuanpian = db('c_customerorder')->alias('o')
            ->join('crm_c_customerinfo c', 'c.id=o.crm_customer_id','left')
            ->join('c_customersp sp', 'sp.orderId=o.orderId','left')
            ->join('c_paymentcontent p','p.orderId=o.orderId and p.currentfc = sp.currentfc','LEFT')
            ->where('sp.sptend_day','between',[$start_t,$end_t])
            ->field('c.customer_channel,c.sales_staff,p.isshishou,p.payment_money,p.fundname,sp.sptend_day,sp.currentfc')
            ->select();
        $sql7 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        foreach ($todaySp as $key => $value) {
            // 总选片量
            $returnData[$value['customer_channel'].$v['sales_staff']]['xpl'] += 1;
            //总选片实收
            if ($v['isshishou'] == '实收') {
                $returnData[$v['customer_channel'].$v['sales_staff']]['xpl_ss'] += $v['payment_money'];
            }

            // 续订量
            if ($v['fundname']=='后期续定' && $v['payment_money'] > 0) {
                $returnData[$v['customer_channel'].$v['sales_staff']]['xdl'] += 1;
                //续订金额
                $returnData[$v['customer_channel'].$v['sales_staff']]['xd_je'] += $v['payment_money'];
            }

            // 升套量
            if ($v['fundname']=='后期升套' && $v['payment_money'] > 0) {
                $returnData[$v['customer_channel'].$v['sales_staff']]['stl'] += 1;
                //加选金额
                $returnData[$v['customer_channel'].$v['sales_staff']]['st_je'] += $v['payment_money'];
            }

            // 加选量
            if ($v['fundname']=='加选金额' && $v['payment_money'] > 0) {
                $returnData[$v['customer_channel'].$v['sales_staff']]['jxl'] += 1;
                //加选金额
                $returnData[$v['customer_channel'].$v['sales_staff']]['jx_je'] += $v['payment_money'];
            }
        }

        //零选量
        $sql8 = "SELECT a.customer_channel,a.sales_staff,p.fuwutype,
    (
        SELECT
            ifnull(sum(payment_money), 0)
        FROM
            c_paymentcontent pay
        WHERE
            pay.orderId = b.orderId
        AND pay.currentfc = sp.currentfc
        AND suoshu IN (
            '后期款',
            '后期补款',
            '二销退款'
        )
        AND isshishou = '实收'
        AND pay.paymentdate BETWEEN '$payStart' AND '$payEnd'
    ) AS sp_shishou
FROM
    c_customerorder b
JOIN crm_c_customerinfo a ON b.crm_customer_id = a.id
JOIN c_customersp sp ON sp.orderId = b.orderId
JOIN c_customerphoto p ON p.orderId = b.orderId
AND p.currentfc = sp.currentfc
AND p.islast = '1' 
WHERE 1=1 and ".$sqllxl;

        $lxl = db()->query($sql8);
        $recTime .= microtime(true).'<br />';
        foreach ($lxl as $k => $value) {
            if ($v['fuwutype'] == "VIP")
            {
                if ($v['sp_shishou'] < 500)
                {
                    $returnData[$v['customer_channel'].$v['sales_staff']]['lxl'] += 1;
                }
            }
            else
            {
                if (sp_shishou < 200)
                {
                    $returnData[$v['customer_channel'].$v['sales_staff']]['lxl'] += 1;
                }
            }
        }


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

            $returnData[$k]['kz_bj_a'] = $v['kz_bj_a'] ?: 0;
            $returnData[$k]['kz_bj_b'] = $v['kz_bj_b'] ?: 0;
            $returnData[$k]['kz_bj_c'] = $v['kz_bj_c'] ?: 0;
            $returnData[$k]['kz_bj_d'] = $v['kz_bj_d'] ?: 0;
            $returnData[$k]['kz_yx'] = $v['kz_yx'] ?: 0;
            $returnData[$k]['hd'] = $v['hd'] ?: 0;
            $returnData[$k]['dy'] = $v['dy'] ?: 0;
            $returnData[$k]['zjs'] = $v['zjs'] ?: 0;
            $returnData[$k]['smpsl'] = $v['smpsl'] ?: 0;
            $returnData[$k]['vip'] = $v['vip'] ?: 0;
            $returnData[$k]['ssje'] = $v['ssje'] ?: 0;
            $returnData[$k]['ssje_hd'] = $v['ssje_hd'] ?: 0;
            $returnData[$k]['ssje_dy'] = $v['ssje_dy'] ?: 0;
            $returnData[$k]['zjs_ss'] = $v['zjs_ss'] ?: 0;
            $returnData[$k]['smyyl'] = $v['smyyl'] ?: 0;
            $returnData[$k]['smddl'] = $v['smddl'] ?: 0;
            $returnData[$k]['yysm_vip'] = $v['yysm_vip'] ?: 0;
            $returnData[$k]['yysm_ss'] = $v['yysm_ss'] ?: 0;
            $returnData[$k]['ykpsl'] = $v['ykpsl'] ?: 0;
            $returnData[$k]['yk_qq'] = $v['yk_qq'] ?: 0;
            $returnData[$k]['yk_hq'] = $v['yk_hq'] ?: 0;
            $returnData[$k]['xpl'] = $v['xpl'] ?: 0;
            $returnData[$k]['xpl_ss'] = $v['xpl_ss'] ?: 0;
            $returnData[$k]['xdl'] = $v['xdl'] ?: 0;
            $returnData[$k]['xd_je'] = $v['xd_je'] ?: 0;
            $returnData[$k]['stl'] = $v['stl'] ?: 0;
            $returnData[$k]['st_je'] = $v['st_je'] ?: 0;
            $returnData[$k]['jxl'] = $v['jxl'] ?: 0;
            $returnData[$k]['jx_je'] = $v['jx_je'] ?: 0;
            $returnData[$k]['lxl'] = $v['lxl'] ?: 0;

            // 客资有效率
            $returnData[$k]['kz_yxl'] = $returnData[$k]['kz_yx'] ? (sprintf('%.2f', $returnData[$k]['kz_yx']/$returnData[$k]['kz']*100)).'%' : '0%';

            //总订单均价
            $returnData[$k]['zdd_jj'] = $returnData[$k]['ssje'] ? (sprintf('%.2f', $returnData[$k]['ssje']/$returnData[$k]['dd_zong'])) : '0';
            //回单均价
            $returnData[$k]['hd_jj'] = $returnData[$k]['ssje_hd'] ? (sprintf('%.2f', $returnData[$k]['ssje_hd']/$returnData[$k]['hd'])) : '0';
            //当月订单均价
            $returnData[$k]['dydd_jj'] = $returnData[$k]['ssje_dy'] ? (sprintf('%.2f', $returnData[$k]['ssje_dy']/$returnData[$k]['dy'])) : '0';
            //转介绍订单均价
            $returnData[$k]['zjsdd_jj'] = $returnData[$k]['zjs_ss'] ? (sprintf('%.2f', $returnData[$k]['zjs_ss']/$returnData[$k]['zjs'])) : '0';


            //上门均价
            $returnData[$k]['sm_jj'] = $returnData[$k]['yysm_ss'] ? (sprintf('%.2f', $returnData[$k]['yysm_ss']/$returnData[$k]['smddl'])) : '0';
            //上门成交率  = 上门订单量 / 上门拍摄量
            $returnData[$k]['sm_cjl'] = $returnData[$k]['smpsl'] ? (sprintf('%.2f', $returnData[$k]['smddl']/$returnData[$k]['smpsl']*100)).'%' : '0%';

            //引客进店前期实收均价
            $returnData[$k]['yk_qq_jj'] = $returnData[$k]['yk_qq'] ? (sprintf('%.2f', $returnData[$k]['yk_qq']/$returnData[$k]['ykpsl'])) : '0';
            //引客进店后期实收均价
            $returnData[$k]['yk_hq_jj'] = $returnData[$k]['yk_hq'] ? (sprintf('%.2f', $returnData[$k]['yk_hq']/$returnData[$k]['ykpsl'])) : '0';

            //毛转当月订单率 = 当月客资订单量/毛客资
            $returnData[$k]['kz_dy_ddl'] = $returnData[$k]['dy'] ? (sprintf('%.2f', $returnData[$k]['dy']/$returnData[$k]['kz']*100)).'%' : '0%';
            //有效转当月订单率 =  当月客资订单量/订单总数
            $returnData[$k]['yxkz_dy_zhl'] = $returnData[$k]['dy'] ? (sprintf('%.2f', $returnData[$k]['dy']/$returnData[$k]['dd_zong']*100)).'%' : '0%';
            //毛客资转化率 = 总订单/毛客资
            $returnData[$k]['kz_zhl'] = $returnData[$k]['dd_zong'] ? (sprintf('%.2f', $returnData[$k]['dd_zong']/$returnData[$k]['kz']*100)).'%' : '0%';
            //有效客资转化率 = 有效客资/订单总数
            $returnData[$k]['yxkz_zhl'] = $returnData[$k]['kz_yx'] ? (sprintf('%.2f', $returnData[$k]['kz_yx']/$returnData[$k]['dd_zong']*100)).'%' : '0%';


            //零选率
            $returnData[$k]['lxl_l'] = $returnData[$k]['lxl'] ? (sprintf('%.2f', $returnData[$k]['lxl']/$returnData[$k]['smpsl']*100)).'%' : '0%';

            //续订率
            $returnData[$k]['xdl_l'] = $returnData[$k]['xdl'] ? (sprintf('%.2f', $returnData[$k]['xdl']/$returnData[$k]['smpsl']*100)).'%' : '0%';

            //升套率
            $returnData[$k]['stl_l'] = $returnData[$k]['stl'] ? (sprintf('%.2f', $returnData[$k]['stl']/$returnData[$k]['smpsl']*100)).'%' : '0%';

            //加选率
            $returnData[$k]['jxl_l'] = $returnData[$k]['jxl'] ? (sprintf('%.2f', $returnData[$k]['jxl']/$returnData[$k]['smpsl']*100)).'%' : '0%';

            // 总计各种数据
            $all['shop_name'] = '';
            $arr['sales_staff'] = '总计';
            $all['qd_ej'] = '';
            $all['qd_sj'] = '';

            $arr['kz'] += $v['kz'];
            $arr['kz_yx'] += $v['kz_yx'];
            $arr['kz_bj_a'] += $v['kz_bj_a'];
            $arr['kz_bj_b'] += $v['kz_bj_b'];
            $arr['kz_bj_c'] += $v['kz_bj_c'];
            $arr['kz_bj_d'] += $v['kz_bj_d'];
            $arr['ssje'] += $v['ssje'];
            $arr['vip'] += $v['vip'];
            $arr['ssje_hd'] += $v['ssje_hd'];
            $arr['ssje_dy'] += $v['ssje_dy'];
            $arr['zjs_ss'] += $v['zjs_ss'];
            $arr['dd_zong'] += $v['dd_zong'];
            $arr['dy'] += $v['dy'];
            $arr['hd'] += $v['hd'];
            $arr['zjs'] += $v['zjs'];
            $arr['smyyl'] += $v['smyyl'];
            $arr['smddl'] += $v['smddl'];
            $arr['yysm_ss'] += $v['yysm_ss'];
            $arr['yysm_vip'] += $v['yysm_vip'];
            $arr['ykyyl'] += $v['ykyyl'];
            $arr['ykpsl'] += $v['ykpsl'];
            $arr['yk_qq'] += $v['yk_qq'];
            $arr['xpl'] += $v['xpl'];
            $arr['xpl_ss'] += $v['xpl_ss'];
            $arr['lxl'] += $v['lxl'];
            $arr['xdl'] += $v['xdl'];
            $arr['xd_je'] += $v['xd_je'];
            $arr['xdl'] += $v['xdl'];
            $arr['xd_je'] += $v['xd_je'];
            $arr['stl'] += $v['stl'];
            $arr['st_je'] += $v['st_je'];

        }
        $returnData = array_values($returnData);
        
        // 总计各种率及成本
        $arr['kz_yxl'] = $arr['kz_yx'] ? (sprintf('%.2f', $arr['kz_yx']/$arr['kz']*100)).'%' : '0%';
        $arr['zdd_jj'] = $arr['zdd_jj'] ? (sprintf('%.2f', $arr['ssje']/$arr['dd_zong'])) : '0';
        $arr['hd_jj'] = $arr['hd_jj'] ? (sprintf('%.2f', $arr['ssje_hd']/$arr['hd'])) : '0';
        $arr['dydd_jj'] = $arr['dydd_jj'] ? (sprintf('%.2f', $arr['ssje_dy']/$arr['dy'])) : '0';
        $arr['zjsdd_jj'] = $arr['zjsdd_jj'] ? (sprintf('%.2f', $arr['zjs_ss']/$arr['zjs'])) : '0';
        $arr['sm_jj'] = $arr['sm_jj'] ? (sprintf('%.2f', $arr['yysm_ss']/$arr['smddl'])) : '0';
        $arr['sm_cjl'] = $arr['sm_cjl'] ? (sprintf('%.2f', $arr['smddl']/$arr['smpsl']*100)).'%' : '0%';
        $arr['yk_qq_jj'] = $arr['yk_qq_jj'] ? (sprintf('%.2f', $arr['yk_qq']/$arr['ykpsl'])) : '0';
        $arr['yk_hq_jj'] = $arr['yk_hq_jj'] ? (sprintf('%.2f', $arr['yk_hq']/$arr['ykpsl'])) : '0';
        $arr['kz_dy_ddl'] = $arr['kz_dy_ddl'] ? (sprintf('%.2f', $arr['dy']/$arr['kz']*100)).'%' : '0%';
        $arr['yxkz_dy_zhl'] = $arr['yxkz_dy_zhl'] ? (sprintf('%.2f', $arr['dy']/$arr['dd_zong']*100)).'%' : '0%';
        $arr['kz_zhl'] = $arr['kz_zhl'] ? (sprintf('%.2f', $arr['dd_zong']/$arr['kz']*100)).'%' : '0%';
        $arr['yxkz_zhl'] = $arr['yxkz_zhl'] ? (sprintf('%.2f', $arr['kz_yx']/$arr['smpsl']*100)).'%' : '0%';
        $arr['lxl_l'] = $arr['lxl_l'] ? (sprintf('%.2f', $arr['lxl']/$arr['smpsl']*100)).'%' : '0%';
        $arr['xdl_l'] = $arr['xdl_l'] ? (sprintf('%.2f', $arr['xdl']/$arr['smpsl']*100)).'%' : '0%';
        $arr['xdl_l'] = $arr['xdl_l'] ? (sprintf('%.2f', $arr['xdl']/$arr['smpsl']*100)).'%' : '0%';
        $arr['stl_l'] = $arr['stl_l'] ? (sprintf('%.2f', $arr['stl']/$arr['smpsl']*100)).'%' : '0%';

        array_push($returnData, $arr);

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

        $return['sql1'] = $sql1;
        $return['sql2'] = $sql2;
        $return['sql3'] = $sql3;
        $return['sql4'] = $sql4;
        $return['sql5'] = $sql5;
        $return['sql6'] = $sql6;
        $return['sql7'] = $sql7;
        $return['sql8'] = $sql8;
        $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]);

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

        //客户分区
        $mcInterface = new mcInterface();
        $customerArea = $mcInterface->getCustomerArea(['shop_code'=>$shop_code]);
        $this->assign("customerarea", $customerArea);

        //追踪人
        $tempStaffs = $mcInterface->getStaffInfo(['shop_code'=>$shop_code]);
        $djStaffs = [];
        $salesStaffs = [];
        foreach ($tempStaffs as $k => $v) {
            // 登记人
            $djTemp['staff_id'] = $v['id'];
            $djTemp['staff_name'] = $v['staffname'];
            array_push($djStaffs, $djTemp);
            unset($djTemp);
            // 追踪人
            if (strstr($v['work_types'],(string)RECEIVE_WORK_TYPE)) {
                $salesTemp['staff_id'] = $v['id'];
                $salesTemp['staff_name'] = $v['staffname'];
            }
            if ($salesTemp) {
                array_push($salesStaffs, $salesTemp);
                unset($salesTemp);
            }
        }
        $this->assign('djStaffs',$djStaffs);
        $this->assign('salesStaffs',$salesStaffs);

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

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

        $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);
    }

}