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

class Customer_channel_data extends Report {

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

    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();
        $det = db('crm_s_report_form')->alias('f')->join('crm_s_report_detail d','d.form_id=f.id')->where('d.id',$data['did'])->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name,f.report_name')->find();
        // if(empty($det)){
        //     return array('code'=>100,'msg'=>'报表配置有误');
        // }
        //可操作店
        if($data['shop_code']){
            $shop_code = $data['shop_code'];
        }elseif($det['shop_code']){
            $shop_code = $det['shop_code'];
        }else{
            $shop_code = session('shop_code');
        }
        /**
        
        毛客资，有效客资

        select 
        c.customer_channel,
        c.dj_staff,sales_staff,
        count(id)
        ,sum(is_valid in (2,3,4)) as wuxiao
        ,sum(is_valid in (1,5)) as youxiao
        from crm_c_customerinfo c 
        where c.dj_day between '2019-12-01 00:00:00' and '2019-12-05 23:59:59'
        and c_from =1 and is_status = 1
        group by c.customer_channel_id 
        ,dj_staff_id,sales_staff_id 
    
        电话量

        select 
c.customer_channel
-- ,c.customer_tel,c.dj_day,c.add_shop_code,c.add_shop_name,c.dj_staff,c.sales_staff
 ,count(t.id)
from crm_c_salestrackinginfo t 
left join crm_c_customerinfo c on c.id = t.customer_id
where t.tel_starttime between '2019-12-01 00:00:00' and '2019-12-05 23:59:59'
and c.c_from =1 and c.is_status = 1
-- and c.customer_channel is null limit 100
group by c.customer_channel_id
-- ,c.dj_staff_id,c.sales_staff_id 

    查网付订单量（邀约量）
    select 
c.customer_channel
,count(o.id)
from c_customerorder o left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
where o.targetdate between '20200101' and '20200101' and crm_order_type = 1
group by c.customer_channel_id

0410：
增加：第1次拍摄金额
     第1次实销金额
     选片量应该取选片完成日期内的选片记录（多服次算多次）--oK
     续订不对--

     少个实销单价（二销实销金额/选片完成订单量）
     

         */
        $start = $data['start'] ? date('Y-m-d 00:00:00', strtotime($data['start'])) : date('Y-m-d 00:00:00');
        $end =  $data['end'] ? date('Y-m-d 23:59:59', strtotime($data['end'])) : date('Y-m-d 23:59:59');
        $start_erp = date('Ymd', strtotime($data['start']));
        $end_erp = date('Ymd', strtotime($data['end']));
        
        $mc = new \app\common\lib\mcInterface();
        if(session('shop_code')){
            $shop = $mc->getShopInfo(['shop_code'=>session('shop_code')]);
            if($shop[0]['is_group'] == 1){
                $shops = 'all';
            }
        }/*else{
            $shops = $mc->getWorkShop(['staff_id'=>session('staff_id')]);
        }*/
        //是否含子单
        if(!$data['sub_order']){
            $suborder = ' and o.orderId = o.sindex';
        }
        //时间针对于有效，按有效时间计算。
        $extwhere = '';
        $extwhereerp = '';
        $extwherecrm = '';
        if(intval($data['dj_staff_id'])){
            $extwhere .= ' and c.dj_staff_id = '.intval($data['dj_staff_id']);
        }elseif ($data['add_shop_code']) {
            $extwhere .= ' and c.add_shop_code = \''.$data['add_shop_code']."'";
        }
        if(intval($data['sales_staff_id'])){
            $extwherecrm .= ' and c.sales_staff_id = '.intval($data['sales_staff_id']);
            $extwhereerp .= ' and o.storeconsuitant2 = \''.$data['sales_staff'].'\' ';
        }elseif ($data['shop_code']) {
            $extwhere .= ' and c.shop_code = \''.$data['shop_code']."'";
        }
        if($shops != 'all' && $extwhere == '' && $extwherecrm == '' ){
            return ['code'=>100,'msg'=>'非集团店员工，必须先择一个查询条件'];
        }
        $sql1 = 'select 
        c.customer_channel,c.customer_channel_id
        ,count(id) as cnt
        ,sum(is_valid in (2,3,4)) as wuxiao
        ,sum(is_valid in (1,5)  and c.yx_day >= \'2020-04-01 00:00:00\') as youxiao
        from crm_c_customerinfo c 
        where c.dj_day between \''.$start.'\' and \''.$end.'\'
        and c_from =1 and is_status = 1 '.$extwhere.$extwherecrm.'
        group by c.customer_channel_id ';
        $list1 = db()->query($sql1);
        foreach ($list1 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['all_number'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['youxiao_number'] = $value['youxiao'];
            $return[(int)$value['customer_channel_id']]['wuxiao_number'] = $value['wuxiao'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //电话量   ---改成打电话的客户量
        $sql2 = '
        select 
            c.customer_channel,c.customer_channel_id
             ,count(t.id) as cnt
            from crm_c_salestrackinginfo t 
            left join crm_c_customerinfo c on c.id = t.customer_id
            where t.tel_starttime between \''.$start.'\' and \''.$end.'\'
             and c.yx_day >= \'2020-04-01 00:00:00\'
            and c.c_from =1 and c.is_status = 1 '.$extwhere.$extwherecrm.'
            group by c.customer_channel_id
        ';
        $list2 = db()->query($sql2);
        foreach ($list2 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['tel_number'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //新单量-邀约
        $sql3 = '
        select 
        c.customer_channel,c.customer_channel_id
        ,count(o.id) cnt
        from c_customerorder o 
        left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
        where o.targetdate between \''.$start_erp.'\' and \''.$end_erp.'\' 
         and c.yx_day >= \'2020-04-01 00:00:00\' 
        and crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
        group by c.customer_channel_id
        ';
        $list3 = db()->query($sql3);
        foreach ($list3 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['order_number'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //拍摄量-到店
        /*
        $sql4 = '
        select 
            c.customer_channel,c.customer_channel_id
            ,count(p.id) cnt
            from c_customerphoto p
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where p.photodate between \''.$start_erp.'\' and \''.$end_erp.'\' and o.crm_order_type = 1 '.$extwhere.'
            group by c.customer_channel_id
        ';
        //*/
        $sql4 = '
        select  c.customer_channel,c.customer_channel_id ,count(o.id) cnt from 
            (select id,`orderId` from c_customerphoto where photodate between \''.$start_erp.'\' and \''.$end_erp.'\' and  (photostate = 1 or photostate = 2) group by `orderId`) p
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where o.crm_order_type = 1   '.$extwhere.$extwhereerp.$suborder.' 
            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id
        ';
        $list4 = db()->query($sql4);
        foreach ($list4 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['photo_number'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //改期量
        $sql5 = '
        select 
            c.customer_channel,c.customer_channel_id
            ,count(p.id) cnt
            from c_customerphotogq p
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where p.photodate between \''.$start_erp.'\' and \''.$end_erp.'\' and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'

            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id
        ';
        $list5 = db()->query($sql5);
        foreach ($list5 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['photo_number_gq'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['photo_number_all'] = $return[(int)$value['customer_channel_id']]['photo_number'] + $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //总业绩
        $sql6 = '
        select 
            c.customer_channel,c.customer_channel_id
            ,sum(p.payment_money) cnt
            from c_paymentcontent p
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where p.isshishou = \'实收\' and p.paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\' and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id
        ';
        $list6 = db()->query($sql6);
        foreach ($list6 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money'] = (float)$value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //一销业绩
        $sql7 = '
        select 
            c.customer_channel,c.customer_channel_id
            ,sum(p.payment_money) cnt
            from c_paymentcontent p
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where (p.isshishou=\'实收\' or p.paytype=\'报销\' or p.paytype=\'VIP卡\' ) and p.suoshu in (\'前期款\',\'前期补款\') and p.paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\' and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id
        ';
        $list7 = db()->query($sql7);
        foreach ($list7 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money_qianqi'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        $sql8 = 'select 
            c.customer_channel,c.customer_channel_id
            ,count(p.id) cnt
            from c_customersp p
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where p.currentfc = 1 and p.sptend_day between \''.$start_erp.'\' and \''.$end_erp.'\' and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id';
        $list8 = db()->query($sql8);
        foreach ($list8 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['sp_number'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //后期实收业绩
        $sql9 = '
            select 
            c.customer_channel,c.customer_channel_id
            ,sum(p.payment_money) cnt
            from c_paymentcontent p
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where p.currentfc = 1 and  p.isshishou = \'实收\' and p.suoshu in (\'后期款\',\'后期补款\') and p.paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\' and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id
        ';
        $list9 = db()->query($sql9);
        foreach ($list9 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money_houqi'] = (float)$value['cnt'];

            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //选片订单量
        $sql10 = '
        select 
        c.customer_channel,c.customer_channel_id,
        count(o.orderid) cnt from c_customerorder o
        left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
        where o.`orderId` in (
        select orderid from c_customersp
        where sptend_day between \''.$start_erp.'\' and \''.$end_erp.'\' group by orderid
        )
        and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
        and c.yx_day >= \'2020-04-01 00:00:00\' 
        group by c.customer_channel_id
        ';
        $list10 = db()->query($sql10);
        foreach ($list10 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['sp_order_number'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        
        //前期收款单数（计算成交率用）
        $sql11 = '
        select 
        c.customer_channel,c.customer_channel_id,
        count(o.orderid) cnt from c_customerorder o
        left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
        where o.`orderId` in (
        select orderid from c_paymentcontent
        where  payment_money > 0 and (isshishou=\'实收\' or paytype=\'报销\' or paytype=\'VIP卡\' ) and suoshu in (\'前期款\',\'前期补款\') and paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\'
        )
        and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
        and c.yx_day >= \'2020-04-01 00:00:00\' 
        group by c.customer_channel_id
        ';
        $list11 = db()->query($sql11);
        foreach ($list11 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money_qianqi_order'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        
        //后期收款单数（计算二销成交率用）
        $sql12 = '
        select 
        c.customer_channel,c.customer_channel_id,
        count(o.orderid) cnt from c_customerorder o
        left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
        where o.`orderId` in (
        select orderid from c_paymentcontent
        where isshishou = \'实收\' and suoshu in (\'后期款\',\'后期补款\') and paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\'
        )
        and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
        and c.yx_day >= \'2020-04-01 00:00:00\' 
        group by c.customer_channel_id
        ';
        $list12 = db()->query($sql12);
        foreach ($list12 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money_houqi_order'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //后期充值业绩
        $sql13 = '
            select 
            c.customer_channel,c.customer_channel_id
            ,sum(p.payment_money) cnt
            from c_paymentcontent p
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where p.payclass = 3 and isshishou=\'实收\' and p.suoshu in (\'后期款\',\'后期补款\') and p.paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\' and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id
        ';
        $list13 = db()->query($sql13);
        foreach ($list13 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money_vip'] = $value['cnt'];

            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //后期充值单数（计算充值均价，充值率用）
        $sql14 = '
        select 
        c.customer_channel,c.customer_channel_id,
        count(o.orderid) cnt from c_customerorder o
        left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
        where o.`orderId` in (
        select orderid from c_paymentcontent
        where payclass = 3 and isshishou=\'实收\' and suoshu in (\'后期款\',\'后期补款\') and paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\'
        )
        and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
        and c.yx_day >= \'2020-04-01 00:00:00\' 
        group by c.customer_channel_id
        ';
        $list14 = db()->query($sql14);
        foreach ($list14 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money_vip_order'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //后期续定业绩
        $sql15 = '
            select 
            c.customer_channel,c.customer_channel_id
            ,sum(p.payment_money) cnt
            from c_paymentcontent p
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where fundname=\'后期续订\' and p.payclass=0 and p.paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\' and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id
        ';
        $list15 = db()->query($sql15);
        foreach ($list15 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money_houqi_xd'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //后期续定单量（计算续定均价和续定率）
        $sql16 = '
            select 
            c.customer_channel,c.customer_channel_id,
            count(o.orderid) cnt from c_customerorder o
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where o.`orderId` in (
            select orderid from c_paymentcontent
            where fundname=\'后期续订\' and  payclass=0 and paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\'
            )
            and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            and c.yx_day >= \'2020-04-01 00:00:00\'
            group by c.customer_channel_id
        ';
        $list16 = db()->query($sql16);
        foreach ($list16 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money_houqi_xd_order'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //后期实销业绩
        $sql17 = '
            select 
            c.customer_channel,c.customer_channel_id
            ,sum(p.payment_money) cnt
            from c_paymentcontent p
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where fundname=\'后期收款\' and p.suoshu in (\'后期款\',\'后期补款\') and p.paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\' and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id
        ';
        $list17 = db()->query($sql17);
        foreach ($list17 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money_houqi_sx'] = (float)$value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //后期实销单量（计算实销均价和实销率）
        $sql18 = '
            select 
            c.customer_channel,c.customer_channel_id,
            count(o.orderid) cnt from c_customerorder o
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where o.`orderId` in (
            select orderid from c_paymentcontent
            where fundname=\'后期收款\' and suoshu in (\'后期款\',\'后期补款\') and paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\'
            )
            and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id
        ';
        $list18 = db()->query($sql18);
        foreach ($list18 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money_houqi_sx_order'] = $value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //一服次总业绩
        $sql19 = '
        select 
            c.customer_channel,c.customer_channel_id
            ,sum(p.payment_money) cnt
            from c_paymentcontent p
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where currentfc = 1 and p.isshishou = \'实收\' and p.paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\' and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id
        ';
        $list19 = db()->query($sql19);
        foreach ($list19 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['payment_money_fc1'] = (float)$value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }
        //第一次拍摄金额
        $sql20 = '
            select c.customer_channel,c.customer_channel_id
            ,sum(p.qqphotomoney) as cnt
            ,(SELECT ifnull(sum(payment_money), 0) FROM c_paymentcontent pay WHERE pay.orderId = p.orderId AND pay.currentfc = p.currentfc AND payclass = 0 AND (paytype IN ("活动卡","报销")) AND suoshu IN ("前期款","前期补款","一销退款")) AS huodongkamoney
            ,count(p.id) as num
            from  c_customerphoto p  
            join c_customersp s on p.orderId=s.orderId and p.currentfc=s.currentfc and p.currentfc=1 and p.islast=1
            left join c_customerorder o on o.`orderId` = p.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where s.sptend_day between \''.$start_erp.'\' and \''.$end_erp.'\'
            
            and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            
            and c.yx_day >= \'2020-04-01 00:00:00\'
            group by c.customer_channel_id
        ';
        $list20 = db()->query($sql20);
        foreach ($list20 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $huodongkamoneySql = '
                select c.customer_channel,c.customer_channel_id
                ,(SELECT ifnull(sum(payment_money), 0) FROM c_paymentcontent pay WHERE pay.orderId = p.orderId AND pay.currentfc = p.currentfc AND payclass = 0 AND (paytype IN ("活动卡","报销")) AND suoshu IN ("前期款","前期补款","一销退款")) AS huodongkamoney
                from  c_customerphoto p  
                join c_customersp s on p.orderId=s.orderId and p.currentfc=s.currentfc and p.currentfc=1 and p.islast=1
                left join c_customerorder o on o.`orderId` = p.`orderId`
                left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
                where s.sptend_day between \''.$start_erp.'\' and \''.$end_erp.'\'
                and c.customer_channel="'.$value['customer_channel'].'"
                and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
                and c.yx_day >= \'2020-04-01 00:00:00\'
            ';
            $huodongkamoneyArr = db()->query($huodongkamoneySql);
            $huodongkamoney = 0;
            foreach ($huodongkamoneyArr as $k => $v) {
                $huodongkamoney += $v['huodongkamoney'];
            }

            $return[(int)$value['customer_channel_id']]['first_payment_money'] = (float)($value['cnt']-$huodongkamoney);
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
            //$return[(int)$value['customer_channel_id']]['payment_money_qianqi_order'] = $value['num'];
        }
        //第一次实销金额
        $sql21 = '
            select c.customer_channel,c.customer_channel_id
            ,sum(pay.currentfcerxiaoshixiaomoney) as cnt
             from  c_paymentcontent pay 
             join c_customerphoto p on pay.orderId=p.orderId and pay.currentfc=p.currentfc and p.currentfc=1 and p.islast=1
            left join c_customerorder o on o.`orderId` = pay.`orderId`
            left join crm_c_customerinfo c on c.customer_number = o.crm_customer_number
            where currentfcerxiaoshixiaomoney > 0 
            and pay.paymentdate between \''.$start_erp.'\' and \''.$end_erp.'\'
            and o.crm_order_type = 1 '.$extwhere.$extwhereerp.$suborder.'
            and c.yx_day >= \'2020-04-01 00:00:00\' 
            group by c.customer_channel_id
        ';
        $list21 = db()->query($sql21);
        foreach ($list21 as $key => $value) {
            $return[(int)$value['customer_channel_id']]['channel_name'] = $value['customer_channel']?$value['customer_channel']:'[空渠道]';
            $return[(int)$value['customer_channel_id']]['first_shixiao'] = (float)$value['cnt'];
            $return[(int)$value['customer_channel_id']]['customer_channel_id'] = $value['customer_channel_id'];
        }

        $ret = [];
        $sum = ['channel_name'=>'总计：'];
        foreach ($return as $key => $value) {
            if($value['sp_order_number'] > 0){
                //后期均价
                $value['payment_money_houqi_avg'] = floatval($value['payment_money_houqi']/$value['sp_number']);
                //选片成交率
                $value['erxiao_rate'] = floatval($value['payment_money_houqi_order']*100/$value['sp_order_number']);
                //选片充值率
                $value['vip_rate'] = sprintf('%.2f',$value['payment_money_vip_order']*100/$value['sp_order_number']);
                //选片续定率
                $value['xd_rate'] = sprintf('%.2f',$value['payment_money_houqi_xd_order']*100/$value['sp_order_number']);
                //选片实销率
                $value['sx_rate'] = floatval($value['payment_money_houqi_sx_order']*100/$value['sp_order_number']);
            }
            if($value['order_number'] > 0){
                $value['payment_money_qianqi_avg'] = sprintf('%.2f',$value['payment_money_qianqi']/$value['order_number']);
            }
            if($value['payment_money_qianqi_order'] > 0){
                $value['yixiao_rate'] = sprintf('%.2f',$value['payment_money_qianqi_order']*100/$value['photo_number']);
            }
            if($value['payment_money_vip_order'] > 0){
                $value['payment_money_vip_avg'] = sprintf('%.2f',$value['payment_money_vip']/$value['payment_money_vip_order']);
            }
            if($value['payment_money_houqi_xd_order'] > 0){
                $value['payment_money_houqi_xd_avg'] = sprintf('%.2f',$value['payment_money_houqi_xd']/$value['payment_money_houqi_xd_order']);
            }
            if($value['sp_number'] > 0){
                $value['payment_money_houqi_sx_avg'] = floatval($value['first_shixiao']/$value['sp_number']);






            }
            $ret[] = $value;
            $sum['all_number'] += (int)$value['all_number'];
            $sum['youxiao_number'] += (int)$value['youxiao_number'];
            $sum['wuxiao_number'] += (int)$value['wuxiao_number'];
            $sum['tel_number'] += (int)$value['tel_number'];
            $sum['order_number'] += (int)$value['order_number'];
            $sum['photo_number'] += (int)$value['photo_number'];
            $sum['photo_number_gq'] += (int)$value['photo_number_gq'];
            $sum['photo_number_all'] += (int)$value['photo_number_all'];
            $sum['payment_money_fc1'] += (float)$value['payment_money_fc1'];
            $sum['payment_money_qianqi'] += (float)$value['payment_money_qianqi'];
            $sum['payment_money_qianqi_order'] += (int)$value['payment_money_qianqi_order'];
            $sum['sp_number'] += (int)$value['sp_number'];
            $sum['sp_order_number'] += (int)$value['sp_order_number'];
            $sum['payment_money_houqi'] += (float)$value['payment_money_houqi'];
            $sum['payment_money_houqi_order'] += (int)$value['payment_money_houqi_order'];
            $sum['payment_money_vip'] += (float)$value['payment_money_vip'];
            $sum['payment_money_vip_order'] += (int)$value['payment_money_vip_order'];
            $sum['payment_money_houqi_xd'] += (float)$value['payment_money_houqi_xd'];
            $sum['payment_money_houqi_xd_order'] += (int)$value['payment_money_houqi_xd_order'];
            $sum['payment_money_houqi_sx'] += (float)$value['payment_money_houqi_sx'];
            $sum['payment_money_houqi_sx_order'] += (int)$value['payment_money_houqi_sx_order'];
            $sum['payment_money'] += (float)$value['payment_money'];
            $sum['first_payment_money'] += (float)$value['first_payment_money'];
            $sum['first_shixiao'] += (float)$value['first_shixiao'];
        }
        if($sum['sp_order_number'] > 0){
            $sum['payment_money_houqi_avg'] = floatval($sum['payment_money_houqi']/$sum['sp_order_number']);
            $sum['erxiao_rate'] = floatval($sum['payment_money_houqi_order']*100/$sum['sp_order_number']);
            $sum['vip_rate'] = sprintf('%.2f',$sum['payment_money_vip_order']*100/$sum['sp_order_number']);
            $sum['xd_rate'] = sprintf('%.2f',$sum['payment_money_houqi_xd_order']*100/$sum['sp_order_number']);
            $sum['sx_rate'] = floatval($sum['payment_money_houqi_sx_order']*100/$sum['sp_order_number']);
        }
        if($sum['order_number'] > 0){
            $sum['payment_money_qianqi_avg'] = sprintf('%.2f',$sum['payment_money_qianqi']/$sum['order_number']);
        }
        if($sum['payment_money_qianqi_order'] > 0){
            $sum['yixiao_rate'] = sprintf('%.2f',$sum['payment_money_qianqi_order']*100/$sum['photo_number']);
        }
        if($sum['payment_money_vip_order'] > 0){
            $sum['payment_money_vip_avg'] = sprintf('%.2f',$sum['payment_money_vip']/$sum['payment_money_vip_order']);
        }
        if($sum['payment_money_houqi_xd_order'] > 0){
            $sum['payment_money_houqi_xd_avg'] = sprintf('%.2f',$sum['payment_money_houqi_xd']/$sum['payment_money_houqi_xd_order']);
        }
        if($sum['sp_number'] > 0){
            $sum['payment_money_houqi_sx_avg'] = floatval($sum['first_shixiao']/$sum['sp_number']);
        }
        $ret[] = $sum;
        reset($sum);
        $returnData['code'] = 0;
        $returnData['msg'] = '';
        $returnData['sql1'] = $sql1;
        $returnData['sql2'] = $sql2;
        $returnData['sql3'] = $sql3;
        $returnData['sql4'] = $sql4;
        $returnData['sql5'] = $sql5;
        $returnData['sql6'] = $sql6;
        $returnData['sql7'] = $sql7;
        $returnData['sql8'] = $sql8;
        $returnData['sql9'] = $sql9;
        $returnData['sql10'] = $sql10;
        $returnData['sql11'] = $sql11;
        $returnData['sql12'] = $sql12;
        $returnData['sql13'] = $sql13;
        $returnData['sql14'] = $sql14;
        $returnData['sql15'] = $sql15;
        $returnData['sql16'] = $sql16;
        $returnData['sql17'] = $sql17;
        $returnData['sql18'] = $sql18;
        $returnData['sql19'] = $sql19;
        $returnData['sql20'] = $sql20;
        $returnData['sql21'] = $sql21;
        $returnData['count'] = count($return);
        $returnData['data'] = $ret;
        return json($returnData);
    }

    public function show(){
        $code = $this->getCode();
        /*
        $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();

        $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');
        if($info['shop_code']){
            $shop= controller('crm/Customer','lib')->get_shop(['shop_code'=>$info['shop_code']]);
            $title = $shop[0]['shop_name'].'-'.$title;
        }
        $mc = new \app\common\lib\mcInterface();
        $allShops = $mc->getWorkShop(['staff_id'=>session('staff_id')]);
        $this->assign('all_shops', $allShops);
        $this->assign('title', $title);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}