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

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

    public function getLevel(){
        return $this->level;
    }
    public function getType(){
        return $this->type;
    }
    public function getCode(){
        return $this->code;
    }
    public function data()
    {

        set_time_limit(0);
        $data = input();

        $start = date('Y-m-d 00:00:00', strtotime($data['start']));
        $end = date('Y-m-d 23:59:59', strtotime($data['end']));
        $det = db('crm_s_report_form')->alias('f')->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'=>'请选择店面');
        }
        
        if ($data['customer_channel']) {
            $channels = explode(',',$data['customer_channel']);
            
        }elseif($data['erji_channel_id']){
            // 来源渠道
            $channels = explode(',',$data['erji_channel_id']);
        }else{
            $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
            $channels = array_column($channels,'channel_name');
        }

        $where['is_status'] = 1;
        $where['c_from'] = 1;
        $where['c.add_shop_code'] = $shop_code;
        //$where['fp_day'] = ['between time',[$start, $end]];

        if($data['staff_id']){
            $mcInterface = new mcInterface();
            $staff[] = $mcInterface->getStaffById($data['staff_id']);
        }else{
            if($data['group_id']){
                $mcParams['group_id'] =  $data['group_id'];
            }

            if($data['department_id']){
                $mcParams['department_id'] =  $data['department_id'];
            }

            if($data['shop_code']){
                $mcParams['shop_code'] =  $shop_code;
            }
            
            $mcParams['work_type'] = RECEIVE_WORK_TYPE;
            $staff = getShopStaffs($mcParams);
        }

        $staffIds = array_column($staff,'id');
        $where['sales_staff_id'] = ['in',$staffIds];

        $group = 'sales_staff';
        $cKey = 'sales_staff';

        //总客资：当月总客资量（当月分配给网销的客资量）
        $cusNum = db('crm_c_customerinfo c')
            ->where($where)
            ->where('dj_day','between time',[$start, $end])
            ->where('customer_channel','in',$channels)
            ->group($group)
            ->column('count(id) as num', $cKey);
        $return['mkz'] = db()->getLastSql();

        //有效客资：当月有效客资+当月预约客资+当月已订单客资
        $validNums = db('crm_c_customerinfo')
            ->alias('c')
            ->where($where)
            ->where("is_valid","in",['1','5'])
            ->where('dj_day','between time',[$start, $end])
            ->where('c.customer_channel','in',$channels)
            ->group($group)
            ->column('count(c.id) as num', $cKey);
        $return['yxkz'] = db()->getLastSql();

        // 成交量  (实收金额大于等于500)
        $orderNums = db('crm_c_customerinfo')->alias('c')
            ->join('c_customerorder erp_order', 'erp_order.crm_customer_id=c.id')
            ->where($where)
            ->where('erp_order.targetdate','between',[date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
            ->where('erp_order.orderId is not null')
            ->where('erp_order.blankoutannal','<>',1)
            // ->where('erp_order.yixiaoshishou','>',0)
            ->where('erp_order.payment_money','>',0)
            ->where('erp_order.crm_order_type',1)
            ->group($group)
            ->column('count(c.id) as num', $cKey);
        $return['cjkz'] = db()->getLastSql();

        // 应收业绩（订单总额）
        $totalMoneys = db('crm_c_customerinfo')->alias('c')
            ->join('c_customerorder erp_order','erp_order.crm_customer_id = c.id')
            ->where($where)
            ->where('c.customer_channel','in',$channels)
            ->where('erp_order.orderId is not null')
            ->where('erp_order.blankoutannal','<>',1)
            // ->where('erp_order.yixiaoshishou','>',0)
            ->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($group)
            ->column('sum(erp_order.bargain_money) as total_money', $cKey);
        $return['yinghsoue'] = db()->getLastSql();

        // 总业绩（实收金额总和）
        $paymentMoneys = db('crm_c_customerinfo')->alias('c')
            ->join('c_customerorder erp_order','erp_order.crm_customer_id = c.id')
            ->join('c_paymentcontent pay','erp_order.orderId = pay.orderId')
            ->where($where)
            ->where('c.customer_channel','in',$channels)
            ->where('erp_order.crm_order_type',1)
            ->where('pay.shop_code',$shop_code)
            ->where('pay.suoshu', 'in', ['前期款','前期补款'])
            ->where('pay.paymentdate','between',[date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
            ->group($group)
            ->column('sum(erp_order.yixiaoshishou) as payment_money', $cKey);
        $return['shijishoukuan'] = db()->getLastSql();

        //花费
        // 渠道花费
        $whereCost['shop_code'] = $shop_code;
        // $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
        // $channels = array_column($channels,'channel_name');
        $cost = db('crm_s_channel_cost')
            ->where('cost_date', 'between time', [$start, $end])
            ->where($whereCost)
            ->where('channel_name','in',$channels)
            ->group('channel_name')
            ->column('sum(cost) as cost','channel_name');
        $return['cost'] = db()->getLastSql();
        $sql4 = db()->getLastSql();
        // var_dump($cost);die;
        // 渠道客资
        $cusChannelNum = db('crm_c_customerinfo')->alias('c')
            ->where('dj_day', 'between time', [$start, $end])
            ->where($where)
            ->where('customer_channel','in',$channels)
            ->group('customer_channel')
            ->column('count(id) as num','customer_channel');
        $sql5 = db()->getLastSql();

        $allChannelInfo = [];
        foreach ($cost as $key => $value) {
            $allChannelInfo[$key]['channel_name'] = $key;
            $allChannelInfo[$key]['cost'] = $value;
            $allChannelInfo[$key]['num'] = $cusChannelNum[$key];
            $allChannelInfo[$key]['cb']  = $value / $cusChannelNum[$key];
        }
        // 员工渠道客资量
        $staffCusNum = db('crm_c_customerinfo')->alias('c')
            ->where('c.dj_day', 'between time', [$start, $end])
            ->where($where)
            ->where('c.customer_channel','in',$channels)
            ->group('c.sales_staff,c.customer_channel')
            ->field('c.sales_staff,c.customer_channel,count(c.id) as num')
            ->select();
        $sql6 = db()->getLastSql();
        // 员工客资花费
        $staffCost = [];
        foreach ($staffCusNum as $key => $value) {
            $staffCost[$value['sales_staff']] += $allChannelInfo[$value['customer_channel']]['cb']*$value['num'];
        }

        $returnData = [];

        $cus = 0;    //录入总计
        $validNum  = 0;  //有效总计
        $orderNum  = 0;  //订单数总计
        $totalMoney  = 0;  //营业额总计
        $paymentMoney = 0;  //实收金额总计
        $cost  = 0;

        $all = [];

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

            $returnData[$k]['sales_staff'] = $v['staffname'];

            $returnData[$k]['orderNum'] = $orderNums[$v['staffname']] ? $orderNums[$v['staffname']] : 0;
            $returnData[$k]['paymentMoney'] = $paymentMoneys[$v['staffname']] ? $paymentMoneys[$v['staffname']] : '0.00';//实收总业绩
            $returnData[$k]['totalMoney'] = $totalMoneys[$v['staffname']] ? $totalMoneys[$v['staffname']] : '0.00';//应收业绩
            $returnData[$k]['cus'] = $cusNum[$v['staffname']] ? $cusNum[$v['staffname']] : 0;
            $returnData[$k]['validNum'] = $validNums[$v['staffname']] ? $validNums[$v['staffname']] : 0;

            // 有效率（ 有效获取 / 毛客资 ）
            $returnData[$k]['validRate'] = (sprintf('%.2f', $returnData[$k]['validNum'] / $returnData[$k]['cus'] * 100)) . '%';

            // 有效客资成交率（ 新单 / 有效客资 ）
            $returnData[$k]['validorderRate'] = (sprintf('%.2f', $returnData[$k]['orderNum'] / $returnData[$k]['validNum'] * 100)) . '%';

            //毛客资成交率(成交/总客)
            $returnData[$k]['orderRate'] = (sprintf('%.2f', $returnData[$k]['orderNum'] / $returnData[$k]['cus'] * 100)) . '%';

            //花费
            $returnData[$k]['cost'] = $staffCost[$v['staffname']] ? round($staffCost[$v['staffname']],2) : '0.00';

            //投入产出比
            $returnData[$k]['ROI'] = sprintf('%.2f',$returnData[$k]['paymentMoney'] / $returnData[$k]['cost']);

            //均值（应收业绩/总订单）
            $returnData[$k]['avg'] = sprintf('%.2f',$returnData[$k]['totalMoney'] / $returnData[$k]['orderNum']);


            $cus  += $returnData[$k]['cus'];    //录入总计
            $validNum += $returnData[$k]['validNum']; //有效总计
            $orderNum += $returnData[$k]['orderNum']; //订单数总计
            $totalMoney   += $returnData[$k]['totalMoney']; //营业额总计
            $paymentMoney  += $returnData[$k]['paymentMoney']; //实收金额总计
            $cost  += $returnData[$k]['cost']; //花费

        }

        

        $all['sales_staff'] = '总计';
        $all['orderNum'] = $orderNum;
        $all['paymentMoney'] = $paymentMoney ? $paymentMoney : '0.00';
        $all['totalMoney'] = $totalMoney ? $totalMoney : '0.00';
        $all['cus'] = $cus;
        $all['validNum'] = $validNum;

        // 有效率（ 有效获取 / 毛客资 ）
        $all['validRate'] = (sprintf('%.2f', $all['validNum'] / $all['cus'] * 100)) . '%';

        // 有效客资成交率（ 新单 / 有效客资 ）
        $all['validorderRate'] = (sprintf('%.2f', $all['orderNum'] / $all['validNum'] * 100)) . '%';

        //毛客资成交率(成交/总客)
        $all['orderRate'] = (sprintf('%.2f', $all['orderNum'] / $all['cus'] * 100)) . '%';

        //花费
        $all['cost'] = $cost ? $cost : '0.00';

        //投入产出比
        $all['ROI'] = sprintf('%.2f',$all['paymentMoney'] / $all['cost']);

        //均值（营收业绩/总订单）
        $all['avg'] = sprintf('%.2f',$all['totalMoney'] / $all['orderNum']);


        array_push($returnData,$all);


        $num = count($staff);
        $avg = [];
        $avg['sales_staff'] = '均值';
        $avg['orderNum'] = intval($orderNum / $num);
        $avg['paymentMoney'] = sprintf('%.2f',$paymentMoney / $num );
        $avg['totalMoney'] = sprintf('%.2f',$totalMoney / $num );
        $avg['cus'] = intval($cus / $num);
        $avg['validNum'] = intval($validNum / $num);

        // 有效率（ 有效获取 / 毛客资 ）
        $avg['validRate'] = (sprintf('%.2f', $avg['validNum'] / $avg['cus'] * 100)) . '%';

        // 有效客资成交率（ 新单 / 有效客资 ）
        $avg['validorderRate'] = (sprintf('%.2f', $avg['orderNum'] / $avg['validNum'] * 100)) . '%';

        //毛客资成交率(成交/总客)
        $avg['orderRate'] = (sprintf('%.2f', $avg['orderNum'] / $avg['cus'] * 100)) . '%';

        //花费
        $avg['cost'] = sprintf('%.2f',$cost / $num);

        //投入产出比
        $avg['ROI'] = sprintf('%.2f',$avg['paymentMoney'] / $avg['cost']);

        //均值（应收业绩/总订单）
        $avg['avg'] = sprintf('%.2f',$avg['totalMoney'] / $avg['orderNum']);


        array_push($returnData,$avg);
  

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

        return json($return);

    }

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

        $brandclass_id = session('brandclass_id');
        $brandclass = session('brandclass');
        $shop_code = session('shop_code');
        $department_id = session('department_id');
        $group_id = session('group_id');

        //店
        $mcInterface = new mcInterface();
        $shop = $mcInterface->getWorkShop(['staff_id'=>session('staff_id'),'brandclass_id'=>session('brandclass_id')]);
        $this->assign("shop", $shop);
        $this->assign("shop_code", $info['shop_code']?$info['shop_code']:$shop_code);

        //部门
        $department = $mcInterface->getDepartments(['shop_code'=>$shop_code]);
        $this->assign("department", $department);

        //组
        $group = $mcInterface->getGroupInfoByDid(['department_id'=>$department_id]);
        $this->assign("group", $group);

        //组下人
        $staff = getShopStaffs(['shop_code'=>$shop_code,'group_id'=>$group_id,'work_type'=>RECEIVE_WORK_TYPE]);
        $this->assign("staff", $staff);

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

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

        //报表名称
        $code = $this->getCode();
        $display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.strtolower($info['report_class_name']).'.html';
        $title = db('crm_s_report_form')->where('code', $code)->value('report_name');
        $this->assign('title', $title);

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

        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}