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

class Cd_dj_staff_data extends Report {

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

    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', strtotime($data['start']));
        $end = date('Y-m-d 23:59:59', strtotime($data['end']));

        $det = db('crm_s_report_form')->alias('f')->join('crm_s_report_detail d','d.form_id=f.id')->where('d.id',$data['did'])->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name')->find();

        //店
        if($data['shop_code']){
            $shop_code = $data['shop_code'];
        }elseif($det['shop_code']){
            $shop_code = $det['shop_code'];
        }else{
            $shop_code = session('shop_code');
        }

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

        $group = 's.channel_name';
        if ($data['channel_name']) {
            $where['s.channel_name'] = ['in',$data['channel_name']];
            // 最后循环的时候用
            $channel = explode(',',$data['channel_name']);
            // var_dump($channel);die;
            // 补充二级渠道用
            $channelInfos = db('crm_s_channel')
                ->where('channel_name','in',$data['channel_name'])->where("FIND_IN_SET('$shop_code',share_shops)")
                ->column('channel_class_name,channel_info_name','channel_name');
        }elseif($data['channel_info_name']){
            $group = 's.channel_info_name';
            $where['s.channel_info_name'] = ['in',$data['channel_info_name']];
            // 最后循环的时候用
            $channel = explode(',',$data['channel_info_name']);
            // var_dump($channel);die;
        }else{
            // 来源渠道
            $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
            $allChannel = array_column($channels, 'channel_name');
            // var_dump($allChannel);die;
            $where['s.channel_name'] = ['in',$allChannel];
            // 最后循环的时候用
            $channel = $allChannel;
            // 补充二级渠道用
            $channelInfos = db('crm_s_channel')
                ->where('channel_name','in',$allChannel)->where("FIND_IN_SET('$shop_code',share_shops)")
                ->column('channel_class_name,channel_info_name','channel_name');
        }
        // 登记人
        if ($data['dj_staff']) {
            $allStaffs = explode(',',$data['dj_staff']);
            $where['c.dj_staff'] = ['in',$data['dj_staff']];
        }else{
            $staffs = getShopStaffs(['shop_code'=>$shop_code]);
            $allStaffs = array_column($staffs,'staffname');
            $where['c.dj_staff'] = ['in',$allStaffs];
        }
        $returnData = [];
        foreach ($allStaffs as $k => $value) {
            foreach ($channel as $kk => $v) {
                $key = $value.'_'.$v;
                if ($data['channel_name']) {
                    $returnData[$key]['dj_staff'] = $value;
                    $returnData[$key]['channel_info_name'] = $channelInfos[$v]['channel_info_name'];
                    $returnData[$key]['channel_name'] = $v;
                }elseif($data['channel_info_name']){
                    $returnData[$key]['dj_staff'] = $value;
                    $returnData[$key]['channel_info_name'] = $v;
                    $returnData[$key]['channel_name'] = '';
                }else{
                    $returnData[$key]['dj_staff'] = $value;
                    $returnData[$key]['channel_info_name'] = $channelInfos[$v]['channel_info_name'];
                    $returnData[$key]['channel_name'] = $v;
                }
                if($data['access_model']){
                    $returnData[$key]['access_model'] = $data['access_model'];
                }else{
                    $returnData[$key]['access_model'] = '';
                }
                $returnData[$key]['mkz'] = '0';
                $returnData[$key]['yxkz'] = '0';
                $returnData[$key]['wxkz'] = '0';
                $returnData[$key]['ddkz'] = '0';
                $returnData[$key]['dryy'] = '0';
                $returnData[$key]['yyl'] = '0';
                $returnData[$key]['drcj'] = '0';
                $returnData[$key]['cjl'] = '0';
                $returnData[$key]['yxrate'] = '0.00%';
                $returnData[$key]['wxrate'] = '0.00%';
                $returnData[$key]['ddrate'] = '0.00%';
                $returnData[$key]['mkzorderrate'] = '0.00%';
                $returnData[$key]['yxorderrate'] = '0.00%';
                $returnData[$key]['cost'] = '0.00';
                $returnData[$key]['mkzcost'] = '0.00';
                $returnData[$key]['yxcost'] = '0.00';
                $returnData[$key]['ordercost'] = '0.00';
                $returnData[$key]['orderrate'] = '0.00';
                $returnData[$key]['yye'] = '0.00';
                $returnData[$key]['ysje'] = '0.00';
                $returnData[$key]['roi'] = '0';
            }
        }

        $sql = 'c.dj_staff,c.access_model,s.channel_info_name,s.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 (\'1\',\'5\')) as yxkz';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.is_valid in (\'3\',\'4\')) as wxkz';
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.is_valid=2) as ddkz';
        // 当日预约量
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.sign_name="已预约" and DATE_FORMAT(c.dj_day, "%Y%m%d")=DATE_FORMAT(c.sign_time,"%Y%m%d")) as dryy';
        // 预约量
        $sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.sign_name="已预约") as yyl';
        $info = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)->where($whereBase)
            ->group('c.dj_staff,'.$group)
            ->field($sql)->select();
        $return['sqlInfo'] = db()->getLastSql();
        // 当日成交
        $orderNumDayTemp = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->join('c_customerorder erp_order', 'erp_order.crm_customer_id=c.id','left')
            ->where($where)->where($whereBase)
            ->where('erp_order.orderId is not null')
            ->where('erp_order.blankoutannal','<>',1)
            ->where('erp_order.payment_money','>',0)
            ->where('erp_order.crm_order_type',1)
            ->where('c.dj_day', 'between time', [$start, $end])
            ->where('DATE_FORMAT(c.dj_day, "%Y%m%d")=DATE_FORMAT(erp_order.targetdate,"%Y%m%d")')
            ->group('c.dj_staff,'.$group)
            ->field('c.dj_staff,count(c.id) as num,s.channel_name,s.channel_info_name')->select();
        $orderNumDay = [];
        foreach ($orderNumDayTemp as $k => $v) {
            if ($data['channel_name']) {
                $key = $v['dj_staff'].'_'.$v['channel_name'];
                $orderNumDay[$key]['num'] = $v['num'];
            }elseif($data['channel_info_name']){
                $key = $v['dj_staff'].'_'.$v['channel_info_name'];
                $orderNumDay[$key]['num'] = $v['num'];
            }else{
                $key = $v['dj_staff'].'_'.$v['channel_name'];
                $orderNumDay[$key]['num'] = $v['num'];
            }
        }
        $return['sqlOrderDay'] = db()->getLastSql();
        // 成交量
        $orderNumTemp = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->join('c_customerorder erp_order', 'erp_order.crm_customer_id=c.id','left')
            ->where($where)->where($whereBase)
            ->where('erp_order.orderId is not null')
            ->where('erp_order.blankoutannal','<>',1)
            ->where('erp_order.payment_money','>',0)
            ->where('erp_order.crm_order_type',1)
            ->where('erp_order.targetdate', 'between', [date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
            ->group('c.dj_staff,'.$group)
            ->field('c.dj_staff,count(c.id) as num,sum(erp_order.bargain_money) as total_money,s.channel_name,s.channel_info_name')->select();
        $orderNum = [];
        foreach ($orderNumTemp as $k => $v) {
            if ($data['channel_name']) {
                $key = $v['dj_staff'].'_'.$v['channel_name'];
                $orderNum[$key]['num'] = $v['num'];
                $orderNum[$key]['total_money'] = $v['total_money'];
            }elseif($data['channel_info_name']){
                $key = $v['dj_staff'].'_'.$v['channel_info_name'];
                $orderNum[$key]['num'] = $v['num'];
                $orderNum[$key]['total_money'] = $v['total_money'];
            }else{
                $key = $v['dj_staff'].'_'.$v['channel_name'];
                $orderNum[$key]['num'] = $v['num'];
                $orderNum[$key]['total_money'] = $v['total_money'];
            }
        }
        $return['sqlOrder'] = db()->getLastSql();
        // 实际收入
        $paymentMoneyTemp = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->join('c_customerorder erp_order', 'erp_order.crm_customer_id=c.id','left')
            ->join('c_paymentcontent erp_pay','erp_pay.orderId = erp_order.orderId','left')
            ->where($where)->where($whereBase)
            ->where('erp_order.crm_order_type',1)
            ->where('erp_pay.shop_code',$shop_code)
            ->where('erp_pay.paymentdate', 'between', [date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
            ->where('erp_pay.suoshu', 'in', ['前期款','前期补款'])
            ->group('c.dj_staff,'.$group)
            ->field('c.dj_staff,sum(erp_pay.payment_money) as payment_money,s.channel_name,s.channel_info_name')->select();
        $paymentMoney = [];
        foreach ($paymentMoneyTemp as $k => $v) {
            if ($data['channel_name']) {
                $key = $v['dj_staff'].'_'.$v['channel_name'];
                $paymentMoney[$key]['payment_money'] = $v['payment_money'];
            }elseif($data['channel_info_name']){
                $key = $v['dj_staff'].'_'.$v['channel_info_name'];
                $paymentMoney[$key]['payment_money'] = $v['payment_money'];
            }else{
                $key = $v['dj_staff'].'_'.$v['channel_name'];
                $paymentMoney[$key]['payment_money'] = $v['payment_money'];
            }
        }
        $return['sqlPay'] = db()->getLastSql();
        // 花费
        $tempChannelCost = db('crm_s_channel_cost')->alias('c')
            ->join('crm_s_channel ch','c.channel_id=ch.id','left')
            ->where($whereC)
            ->where('c.cost_date', 'between', [date('Y-m-d',strtotime($start)), date('Y-m-d',strtotime($end))])
            ->where('c.shop_code', $shop_code)
            ->field('ch.rebates,ch.rebates_start_time,ch.rebates_end_time,c.cost,c.cost_date,c.channel_class_id,c.channel_class_name,c.channel_info_id,c.channel_info_name,c.channel_id,c.channel_name')
            ->select();
        // var_dump($tempChannelCost);die;
        $channelCost = [];
        foreach ($tempChannelCost as $k => $v) {
            // 是否设置了 返点
            if ($v['rebates']>0 && 
                date('Ymd',strtotime($v['rebates_start_time']))<=date('Ymd',strtotime($v['cost_date'])) && 
                date('Ymd',strtotime($v['rebates_start_time']))>=date('Ymd',strtotime($v['cost_date'])) 
            ) {
                $cost = $v['cost'] ? sprintf( "%.4f", $v['cost']/(1+($v['rebates']/100)) ) : 0;
            }else{
                $cost = $v['cost'];
            }
            if ($costKey=='c.channel_info_name') {
                $channelCost[$v['channel_info_name']]['channel_class_name'] = $v['channel_class_name'];
                $channelCost[$v['channel_info_name']]['channel_info_name'] = $v['channel_info_name'];
                $channelCost[$v['channel_info_name']]['channel_name'] = $v['channel_name'];
                $channelCost[$v['channel_info_name']]['cost'] += $cost;
            }else{
                $channelCost[$v['channel_name']]['channel_class_name'] = $v['channel_class_name'];
                $channelCost[$v['channel_name']]['channel_info_name'] = $v['channel_info_name'];
                $channelCost[$v['channel_name']]['channel_name'] = $v['channel_name'];
                $channelCost[$v['channel_name']]['cost'] += $cost;
            }
        }
        $return['sqlCost'] = db()->getLastSql();
        $cusNum = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($whereBase)
            ->group($group)
            ->column('count(c.id) as num',$group);
        $return['sqlChannelCus'] = db()->getLastSql();
        // 单个客资成本
        $allChannelInfo = [];
        foreach ($channelCost as $key => $value) {
            $allChannelInfo[$key]['num'] = $cusNum[$key];
            $allChannelInfo[$key]['cb']  = $v['cost'] / $cusNum[$key];
        }
        // 员工渠道客资量
        $staffCusNum = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)->where($whereBase)
            ->group('c.dj_staff,'.$group)
            ->field('c.dj_staff,count(c.id) as num,s.channel_name,s.channel_info_name')
            ->select();
        $sql6 = db()->getLastSql();
        // 员工客资花费
        $staffCost = [];
        foreach ($staffCusNum as $k => $v) {
            if ($data['channel_name']) {
                $key = $v['dj_staff'].'_'.$v['channel_name'];
                $sKey= $v['channel_name'];
            }elseif($data['channel_info_name']){
                $key = $v['dj_staff'].'_'.$v['channel_info_name'];
                $sKey= $v['channel_info_name'];
            }else{
                $key = $v['dj_staff'].'_'.$v['channel_name'];
                $sKey= $v['channel_name'];
            }
            $staffCusNum[$key]['cost'] += $allChannelInfo[$sKey]['cb']*$v['num'];
        }
        $arr = [];
        $k = 0;
        
        foreach($info as $k=>$v){
            if ($data['channel_name']) {
                $key = $v['dj_staff'].'_'.$v['channel_name'];
                $returnData[$key]['dj_staff'] = $v['dj_staff'];
                $returnData[$key]['channel_info_name'] = $v['channel_info_name'];
                $returnData[$key]['channel_name'] = $v['channel_name'];
            }elseif($data['channel_info_name']){
                $key = $v['dj_staff'].'_'.$v['channel_info_name'];
                $returnData[$key]['dj_staff'] = $v['dj_staff'];
                $returnData[$key]['channel_info_name'] = $v['channel_info_name'];
                $returnData[$key]['channel_name'] = '';
            }else{
                $key = $v['dj_staff'].'_'.$v['channel_name'];
                $returnData[$key]['dj_staff'] = $v['dj_staff'];
                $returnData[$key]['channel_info_name'] = $v['channel_info_name'];
                $returnData[$key]['channel_name'] = $v['channel_name'];
            }
            if($data['access_model']){
                $returnData[$key]['access_model'] = $data['access_model'];
            }else{
                $returnData[$key]['access_model'] = '';
            }
            $returnData[$key]['mkz'] = $v['mkz'] ? (int)$v['mkz'] : 0;
            $returnData[$key]['yxkz']= $v['yxkz'] ? (int)$v['yxkz'] : 0;
            $returnData[$key]['wxkz']= $v['wxkz'] ? (int)$v['wxkz'] : 0;
            $returnData[$key]['ddkz']= $v['ddkz'] ? (int)$v['ddkz'] : 0;
            $returnData[$key]['dryy']= $v['dryy'] ? (int)$v['dryy'] : 0;
            $returnData[$key]['yyl'] = $v['yyl'] ? (int)$v['yyl'] : 0;
            $returnData[$key]['drcj']= $orderNumDay[$key]['num'] ? (int)$orderNumDay[$key]['num'] : 0;
            $returnData[$key]['cjl'] = $orderNumDay[$key]['num'] ? (int)$orderNumDay[$key]['num'] : 0;
            // 各种率
            $returnData[$key]['yxrate'] = $returnData[$key]['yxkz'] ? (sprintf('%.2f', $returnData[$key]['yxkz']/$returnData[$key]['mkz'] * 100)).'%' : '0.00%';
            $returnData[$key]['wxrate'] = $returnData[$key]['wxkz'] ? (sprintf('%.2f', $returnData[$key]['wxkz']/$returnData[$key]['mkz'] * 100)).'%' : '0.00%';
            $returnData[$key]['ddrate'] = $returnData[$key]['ddkz'] ? (sprintf('%.2f', $returnData[$key]['ddkz']/$returnData[$key]['mkz'] * 100)).'%' : '0.00%';
            $returnData[$key]['mkzorderrate'] = $returnData[$key]['cjl'] ? (sprintf('%.2f', $returnData[$key]['cjl']/$returnData[$key]['mkz'] * 100)).'%' : '0.00%';
            $returnData[$key]['yxorderrate'] = $returnData[$key]['cjl'] ? (sprintf('%.2f', $returnData[$key]['cjl']/$returnData[$key]['yxkz'] * 100)).'%' : '0.00%';
            // 花费 及 成本
            $returnData[$key]['cost'] = $staffCost[$key]['cost'] ? sprintf('%.2f',$staffCost[$key]['cost']) : '0.00';
            $returnData[$key]['mkzcost'] = $staffCost[$key]['cost'] ? sprintf('%.2f',$staffCost[$key]['cost']/ $returnData[$key]['mkz']) : '0.00';
            $returnData[$key]['yxcost'] = $staffCost[$key]['cost'] ? sprintf('%.2f',$staffCost[$key]['cost']/ $returnData[$key]['yxkz']) : '0.00';
            $returnData[$key]['ordercost'] = $staffCost[$key]['cost'] ? sprintf('%.2f',$staffCost[$key]['cost']/ $returnData[$key]['cjl']) : '0.00';
            // 金额相关
            $returnData[$key]['yye'] = $orderNum[$key]['total_money'] ? sprintf('%.2f',$orderNum[$key]['total_money']) : '0.00';
            $returnData[$key]['ysje'] = $paymentMoney[$key]['payment_money'] ? sprintf('%.2f',$paymentMoney[$key]['payment_money']) : '0.00';
            $returnData[$key]['orderrate'] = $returnData[$key]['yye'] ? sprintf('%.2f', $returnData[$key]['yye']/$returnData[$key]['cjl']) : '0.00';
            $returnData[$key]['roi'] = '';
            // 总计
            $arr['dj_staff'] = '总计';
            $arr['mkz']  += $returnData[$key]['mkz'];
            $arr['yxkz'] += $returnData[$key]['yxkz'];
            $arr['wxkz'] += $returnData[$key]['wxkz'];
            $arr['ddkz'] += $returnData[$key]['ddkz'];
            $arr['dryy'] += $returnData[$key]['dryy'];
            $arr['yyl']  += $returnData[$key]['yyl'];
            $arr['drcj'] += $returnData[$key]['drcj'];
            $arr['cjl']  += $returnData[$key]['cjl'];
            $arr['yye']  += $returnData[$key]['yye'];
            $arr['ysje'] += $returnData[$key]['ysje'];
            $arr['cost'] += $returnData[$key]['cost'];
        }
        // var_dump($returnData);die;
        // die;
        if(!empty($arr)){
            $arr['yxrate'] = $arr['yxkz'] ? (sprintf('%.2f', $arr['yxkz']/$arr['mkz'] * 100)).'%' : '0.00%';
            $arr['wxrate'] = $arr['wxkz'] ? (sprintf('%.2f', $arr['wxkz']/$arr['mkz'] * 100)).'%' : '0.00%';
            $arr['ddrate'] = $arr['ddkz'] ? (sprintf('%.2f', $arr['ddkz']/$arr['mkz'] * 100)).'%' : '0.00%';
            $arr['mkzorderrate'] = $arr['cjl'] ? (sprintf('%.2f', $arr['cjl']/$arr['mkz'] * 100)).'%' : '0.00%';
            $arr['yxorderrate'] = $arr['cjl'] ? (sprintf('%.2f', $arr['cjl']/$arr['yxkz'] * 100)).'%' : '0.00%';
            $arr['orderrate'] = $arr['yye'] ? sprintf('%.2f', $arr['yye']/$arr['cjl']) : '0.00';

            $arr['mkzcost'] = $arr['cost'] ? sprintf('%.2f', $arr['cost']/$arr['mkz']) : '0.00';
            $arr['yxcost'] = $arr['cost'] ? sprintf('%.2f', $arr['cost']/$arr['yxkz']) : '0.00';
            $arr['ordercost'] = $arr['cost'] ? sprintf('%.2f', $arr['cost']/$arr['cjl']) : '0.00';

            $arr['roi'] = '';
            array_unshift($returnData,$arr);
        }

        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($returnData);
        $return['data'] = $returnData;
       
        return json($return);
    }
    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,f.report_name')->find();

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

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

        //获取方式
        $accessModel = controller("crm/Customer", "lib")->accessModel(['brandclass_id'=>session('brandclass_id')]);
        $this->assign("accessModel", $accessModel);

        //事业部下店面
        $mc = new \app\common\lib\mcInterface;
        $shopList = $mc->getWorkShop(['staff_id'=>session('staff_id'),'brandclass_id'=>session('brandclass_id')]);
        $this->assign('shopList',$shopList);
        $this->assign("shop_code", session('shop_code'));

        // 登记人 即 店下所有人
        $djStaffList = $mc->getShopStaff(['shop_code'=>session('shop_code')]);
        $this->assign('djStaffList',$djStaffList);

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

        //表格隐藏列
        $this->assign('hiddenColumns',explode('|',cookie('report_cd_customer_access_hidden')));

        $this->assign('title', session('shop_name').'-'.$info['report_name']);
        $this->assign('did', $info['id']);
        $this->assign('start',date('Y-m-01'));
        $this->assign('end',date('Y-m-d'));
        //表格隐藏列
        $this->assign('hiddenColumns',explode('|',cookie('report_cd_dj_staff_data_hidden')));
        return $this->fetch($display);
    }

}