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

class Qqbaby_ds_wx_yeji extends Report {

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

    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();

        $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();
        $shop_code = $det['shop_code'] ? $det['shop_code'] : session('shop_code');
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }

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

        $where['is_status'] = 1;
        $where['c_from'] = 1;
        $where['c.add_shop_code'] = $shop_code;
        // $where['c.sales_staff_id'] = ['>',0];
        // 订单用sql
        $whereSql = " AND c.c_from=1 AND c.is_status=1 AND c.add_shop_code='$shop_code' AND co.target_time BETWEEN '$start' AND '$end'";
        // 业绩用sql
        $yjStart = date('Ymd',strtotime($start));
        $yjEnd = date('Ymd',strtotime($end));
        $whereSqlJ = " AND c.c_from=1 AND c.is_status=1 AND c.add_shop_code='$shop_code' AND j.yj_paymentdate BETWEEN '$yjStart' AND '$yjEnd'";
        // 城市
        if($data['city']){
            $where['city_id'] = $data['city'];
            $costWhere['c.city_id'] = ['in',$data['dj_staff']];
            // 订单用sql
            $city_id = $data['city'];
            $whereSql .= " AND c.city_id=$city_id";
            $whereSqlJ.= " AND c.city_id=$city_id";
        }
        // 追踪人
        if($data['sales_staff']){
            $where['c.sales_staff_id'] = ['in',$data['sales_staff']];
            // 订单用sql
            $salesStaffArr = $data['sales_staff'];
            $salesStaffStr = '';
            foreach ($salesStaffArr as $k => $v) {
                $salesStaffStr .= $v.',';
            }
            $salesStaffStr = trim($salesStaffStr,',');
            $whereSql .= " AND c.sales_staff_id IN ($salesStaffStr)";
            $whereSqlJ.= " AND c.sales_staff_id IN ($salesStaffStr)";
        }
        // 渠道
        if($data['channel']){
            $where['c.customer_channel'] = ['in', $data['channel']];
            $costWhere['c.channel_name'] = ['in', $data['channel']];
            // 订单用sql
            $channelArr = explode(',',$data['channel']);
            $channelstr = '';
            foreach ($channelArr as $k => $v) {
                $channelstr .= "'".$v."',";
            }
            $channelstr = trim($channelstr,',');
            $whereSql .= " AND c.customer_channel IN ($channelstr)";
            $whereSqlJ.= " AND c.customer_channel IN ($channelstr)";
        }
        //品牌
        if($data['b_id']){
            $where['c.b_id'] = $data['b_id'];
            $costWhere['c.b_id'] = ['in',$data['b_id']];
            // 订单用sql
            $bid = $data['b_id'];
            $whereSql .= " AND c.b_id=$bid";
            $whereSqlJ.= " AND c.b_id=$bid";
        }
        //消费类型
        if($data['consultation_type_id']){
            $where['c.consultation_type_id'] = $data['consultation_type_id'];
            // 订单用sql
            $consultation_type_id = $data['consultation_type_id'];
            $whereSql .= " AND c.consultation_type_id=$consultation_type_id";
            $whereSqlJ.= " AND c.consultation_type_id=$consultation_type_id";
        }
        $recTime .= microtime(true).'<br />';
        $returnData = [];
        // 毛客资
        $customerinfo = db('crm_c_customerinfo')->alias("c")
            ->where($where)
            ->where('c.dj_day','between',[$start,date('Y-m-d 23:59:59',strtotime($end))])
            ->field('c.id,c.city,c.sales_staff')
            ->select();
        $sql1 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        foreach ($customerinfo as $k => $v) {
            $returnData[$v['city'].$v['sales_staff']]['city'] = $v['city'];
            $returnData[$v['city'].$v['sales_staff']]['sales_staff'] = $v['sales_staff'];
            $returnData[$v['city'].$v['sales_staff']]['kz'] += 1;
        }
        // 有效客资
        $yxInfo = db('crm_c_customerinfo')->alias("c")
            ->where($where)->where('is_valid',1)
            ->where('c.dj_day','between',[$start,date('Y-m-d 23:59:59',strtotime($end))])
            // ->where('c.yx_day','between',[$start,date('Y-m-d 23:59:59',strtotime($end))])
            ->field('c.id,c.city,c.sales_staff')
            ->select();
        $sql2 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        foreach ($yxInfo as $k => $v) {
            if ($v['city']&&$v['sales_staff']) {
                $returnData[$v['city'].$v['sales_staff']]['city'] = $v['city'];
                $returnData[$v['city'].$v['sales_staff']]['sales_staff'] = $v['sales_staff'];
                $returnData[$v['city'].$v['sales_staff']]['kz_yx'] += 1;
            }
        }
        /*
        // 花费(这个城市，这个渠道，这个品牌，每天，花费)
        $channelCost = db('s_channel_cost')->alias("c")
            ->where('c.cost_date','between',[$start,$end])
            ->where($costWhere)
            ->field('c.city_name,c.channel_name,b_name,c.cost_date,c.cost')->select();
        $tempCost = [];
        foreach ($channelCost as $k => $v) {
            $tempCost[$v['city_name'].$v['channel_name'].$v['b_name'].$v['cost_date']] = $v['cost'];
        }
        $sql3 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        // 客资(这个城市，这个渠道，这个品牌，每天，客资)
        $costCus = db('c_customerinfo')->alias("c")
            ->where($where)
            ->where('c.dj_day','between',[$start,date('Y-m-d 23:59:59',strtotime($end))])
            ->field('c.city,c.customer_channel,c.b_name,DATE_FORMAT(dj_day,"%Y-%m-%d") as dj_day')
            ->select();
        $tempCus = [];
        foreach ($costCus as $k => $v) {
            $tempCus[$v['city'].$v['customer_channel'].$v['b_name'].$v['dj_day']] += 1;
        }
        */
        // 订单
        $field='c.city,c.sales_staff,co.target_time,co.order_sn,o.orderId,o.total_money,o.payment_money,o.nopayment_money,o.bargain_money,o.yixiaokakou,o.yixiaoshishou';
        // echo $whereSql;die;
        $sql4 = 'SELECT '.$field.' FROM crm_c_customerinfo c LEFT JOIN crm_c_customer_order co ON c.id=co.customer_id LEFT JOIN  c_customerorder o ON co.order_sn = o.orderId  WHERE o.id IS NOT NULL '.$whereSql;
        $orderData = db()->query($sql4);
        $recTime .= microtime(true).'<br />';
        $orderIds = '';
        foreach ($orderData as $k => $v) {
            $returnData[$v['city'].$v['sales_staff']]['city'] = $v['city'];
            $returnData[$v['city'].$v['sales_staff']]['sales_staff'] = $v['sales_staff'];
            $returnData[$v['city'].$v['sales_staff']]['dd_zong']+= 1;
            // 一销金额
            $returnData[$v['city'].$v['sales_staff']]['yx_tx'] += $v['bargain_money'];
            // 订单欠款
            $returnData[$v['city'].$v['sales_staff']]['yx_qk'] += $v['nopayment_money'];
            $returnData[$v['city'].$v['sales_staff']]['ids'][] = $v['orderId'];
            $orderIds .= $v['orderId'].',';
        }
        $orderIds = trim($orderIds,',');
        // 业绩
        $sql5 = 'SELECT `c`.`city`,`c`.`sales_staff`,`j`.`orderId`,`j`.`staffname`,`j`.`yj_money`,`t`.`typename` FROM crm_c_customerinfo c LEFT JOIN crm_c_customer_order co ON `c`.`id`=`co`.`customer_id` LEFT JOIN `c_jobscore` j ON `co`.`order_sn`=`j`.`orderId` AND `c`.`sales_staff`=`j`.`staffname` LEFT JOIN `c_paymenttype` t ON `t`.`typecode`=`j`.`yj_paytype` WHERE `j`.`id` IS NOT NULL '.$whereSqlJ;
        $yjInfos = db()->query($sql5);
        // echo $sql5;
        // var_dump($yjInfos);die;
        foreach ($yjInfos as $k => $v) {
            $returnData[$v['city'].$v['sales_staff']]['city'] = $v['city'];
            $returnData[$v['city'].$v['sales_staff']]['sales_staff'] = $v['sales_staff'];

            if ($v['typename'] == 'VIP卡') {
                $returnData[$v['city'].$v['sales_staff']]['vip'] += $v['yj_money'];
            }else{
                $returnData[$v['city'].$v['sales_staff']]['ssadd'][$v['orderId']] = 1;
            }
            if ($v['typename'] == '刷卡') {
                $returnData[$v['city'].$v['sales_staff']]['ssje'] += $v['yj_money'];
            }
            if ($v['typename'] == '现金') {
                $returnData[$v['city'].$v['sales_staff']]['ssje'] += $v['yj_money'];
            }
            if ($v['typename'] == '网络付款') {
                $returnData[$v['city'].$v['sales_staff']]['ssje'] += $v['yj_money'];
            }
            if ($v['typename'] == '免单') {
                $returnData[$v['city'].$v['sales_staff']]['ssje'] += $v['yj_money'];
            }
        }

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

        $recTime .= microtime(true).'<br />';
        $return['sql1'] = $sql1;
        $return['sql2'] = $sql2;
        $return['sql3'] = $sql3;
        $return['sql4'] = $sql4;
        $return['sql5'] = $sql5;
        $return['recTime'] = $recTime;
        $return['code'] = 0;
        $return['msg'] = '';
        $return['data'] = array_values($returnData);
        $return['count'] = count($returnData);
        return json($return);
        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();
        $shop= controller('crm/Customer','lib')->get_shop(['shop_code'=>$info['shop_code']]);
        // 事业部下所有可操作店
        $shopParam['staff_id'] = session('staff_id');
        $shopParam['brandclass_id'] = $info['brandclass_id'];
        $myShops = getShops($shopParam);
        $this->assign("myShops", $myShops);
        // var_dump($myShops);die;
        // 来源渠道
        $channel = getChannelJson($info['shop_code']);
        $this->assign("channel", json_encode($channel));
        // crm省 列表
        $data['type']=1;
        $data['shop_code']= $info['shop_code'] ? $info['shop_code'] : session('shop_code');
        $province=controller("crm/Customer","lib")->address($data);
        $this->assign("province",$province);
        // 只有一个省 查这个省下的城市
        if (count($province)==1) {
            $data['type'] = 2;
            $data['pid']  = $province[0]['id'];
            $staCity = controller("crm/Customer", "lib")->address($data);
            $this->assign("staCity",$staCity);
        }
        // 品牌
        $brandArr = db('crm_s_brandset')->select();
        $this->assign('brandArr',$brandArr);
        // 消费类型
        $consumptionType = controller("Customer", "lib")->consumptionType(['brandclass_id'=>$info['brandclass_id']]);
        $this->assign("consumptionType", $consumptionType);

        $mc = new \app\common\lib\mcInterface();
        $data['shop_code']= $info['shop_code'] ? $info['shop_code'] : session('shop_code');
        // 部门
        $departmentList = $mc->getDepartmentByShopcode($data['shop_code']);
        foreach ($departmentList as $key => $value) {
            if ($value['department_name']==session('department_name')) {
                $departmentList[$key]['selected']=1;
                $departmentId = $value['id'];
            }
        }
        $this->assign("departmentList",$departmentList);
        $staffParam['department_id'] = $departmentId;
        $staffParam['get_main'] = 1;
        // 登记人
        $djStaffList = $mc->getDepartmentStaffs($staffParam);
        $this->assign("djStaffList",$djStaffList);
        // 追踪人
        $salesStaffList = $mc->getDepartmentStaffs($staffParam);
        $this->assign("salesStaffList",$salesStaffList);
        // 标题
        $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', $shop[0]['shop_name'].'-'.$title);
        $this->assign('did', $info['id']);
        // 默认本月
        $this->assign('start', date('Y-m-01'));
        $this->assign('end', date('Y-m-d'));
        return $this->fetch($display);
    }
}