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

class Xaf_ds_tg extends Report {

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

    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();
        $shop_code = $det['shop_code'];
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }
        $where['c_from'] = 1;
        $where['is_status'] = 1;
        $where['c.shop_code'] = $shop_code;

        if ($data['staff_id'] && $data['method'] == "sales") {
            $where['c.sales_staff_id'] = $data['staff_id'];
        }elseif ($data['staff_id'] && $data['method'] == "dj") {
            $where['c.dj_staff_id'] = $data['staff_id'];
        }

        $day = '';
        if(!$data['month']){
            return array('code'=>99,'msg'=>'请选择月份');
        }else{
            $month = getthemonth($data['month']);
        }
        $cost = db('crm_s_channel_cost')
            ->where('shop_code',$shop_code)
            ->where('cost_date','between',[$month[0],$month[1]])
            ->group('channel_name')
            ->column('sum(cost)','channel_name');

        // 毛客资
        $sql.= "sum( c.pinlei='爱婴上门' ) as ayMkz,";
        $sql.= "sum( c.pinlei='小阿福' ) as smMkz,";
        $sql.= "sum( c.pinlei='狄安娜' ) as danMkz,";
        $sql.= "sum( c.pinlei='花童喜宴' ) as htMkz,";
        // 有效
        $sql.= "sum( c.is_valid=1 AND c.pinlei='爱婴上门' ) as ayValid,";
        $sql.= "sum( c.is_valid=1 AND c.pinlei='小阿福' ) as smValid,";
        $sql.= "sum( c.is_valid=1 AND c.pinlei='狄安娜' ) as danValid,";
        $sql.= "sum( c.is_valid=1 AND c.pinlei='花童喜宴' ) as htValid,";
        // 收款订单数量
        $sql.= "sum( o.payment_money>0 AND c.pinlei='爱婴上门' ) as ayOrder,";
        $sql.= "sum( o.payment_money>0 AND c.pinlei='小阿福' ) as smOrder,";
        $sql.= "sum( o.payment_money>0 AND c.pinlei='狄安娜' ) as danOrder,";
        $sql.= "sum( o.payment_money>0 AND c.pinlei='花童喜宴' ) as htOrder,";
        $sql.= "0 as ayMkz,0 as ayMkz,0 as ayMkz,0 as ayMkz,0 as aypayment,0 as smpayment,0 as danpayment, 0 as htpayment";
        $info= db('crm_c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customer_number=o.crm_customer_number','left')
            ->where($where)->where('dj_day','between',[$month[0],$month[1]])
            ->where('c.pinlei','not null')->where('c.pinlei','<>','')
            ->group('c.dj_staff,c.customer_channel')
            ->field('c.dj_staff_id,c.dj_staff,c.customer_channel,'.$sql)->select();
        $returnData['sql1'] = db()->getLastSql();
        
        $paySql = '0 as ayValid,0 as smValid,0 as danValid,0 as htValid,0 as ayOrder,0 as smOrder,0 as danOrder,0 as htOrder,0 as htpayment,';
        $ayPayment = db('crm_c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customer_number=o.crm_customer_number','left')
            ->where($where)->where('dj_day','between',[$month[0],$month[1]])
            ->where('c.pinlei','爱婴上门')
            ->group('c.dj_staff,c.customer_channel')
            ->field('c.dj_staff_id,c.dj_staff,c.customer_channel,'.$paySql.'sum(o.payment_money) as aypayment,0 as smpayment,0 as danpayment, 0 as htpayment')
            ->select();
        $smPayment = db('crm_c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customer_number=o.crm_customer_number','left')
            ->where($where)->where('dj_day','between',[$month[0],$month[1]])
            ->where('c.pinlei','小阿福')
            ->group('c.dj_staff,c.customer_channel')
            ->field('c.dj_staff_id,c.dj_staff,c.customer_channel,'.$paySql.'sum(o.payment_money) as smpayment,0 as aypayment,0 as danpayment, 0 as htpayment')
            ->select();
        $danPayment = db('crm_c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customer_number=o.crm_customer_number','left')
            ->where($where)->where('dj_day','between',[$month[0],$month[1]])
            ->where('c.pinlei','狄安娜')
            ->group('c.dj_staff,c.customer_channel')
            ->field('c.dj_staff_id,c.dj_staff,c.customer_channel,'.$paySql.'sum(o.payment_money) as danpayment,0 as aypayment,0 as smpayment, 0 as htpayment')
            ->select();
        $htPayment = db('crm_c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customer_number=o.crm_customer_number','left')
            ->where($where)->where('dj_day','between',[$month[0],$month[1]])
            ->where('c.pinlei','花童喜宴')
            ->group('c.dj_staff,c.customer_channel')
            ->field('c.dj_staff_id,c.dj_staff,c.customer_channel,'.$paySql.'sum(o.payment_money) as htpayment,0 as aypayment,0 as smpayment, 0 as danpayment')
            ->select();
        $info = array_merge($info,$ayPayment,$smPayment,$danPayment,$htPayment);
        $temp = [];
        foreach ($info as $k => $v) {
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['staff_id'] = $v['dj_staff_id'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['dj_staff'] = $v['dj_staff'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['customer_channel'] = $v['customer_channel'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['channel_cost'] = $cost[$v['customer_channel']] ? $cost[$v['customer_channel']] : 0;

            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['ayMkz'] += $v['ayMkz'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['smMkz'] += $v['smMkz'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['danMkz'] += $v['danMkz'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['htMkz'] += $v['htMkz'];

            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['ayValid'] += $v['ayValid'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['smValid'] += $v['smValid'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['danValid'] += $v['danValid'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['htValid'] += $v['htValid'];

            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['ayOrder'] += $v['ayOrder'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['smOrder'] += $v['smOrder'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['danOrder'] += $v['danOrder'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['htOrder'] += $v['htOrder'];

            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['aypayment'] += $v['aypayment'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['smpayment'] += $v['smpayment'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['danpayment'] += $v['danpayment'];
            $temp[$v['dj_staff'].'_'.$v['customer_channel']]['htpayment'] += $v['htpayment'];
        }
        $return = [];
        foreach ($temp as $k => $v) {
            $temp1=$v;
            $temp1['ayAvg'] =dataFormate($v['aypayment']/$v['ayOrder']);
            $temp1['ayRate']=(dataFormate($v['ayOrder']/$v['ayValid'])*100).'%';

            $temp1['smAvg'] =dataFormate($v['smpayment']/$v['smOrder']);
            $temp1['smRate']=(dataFormate($v['smOrder']/$v['smValid'])*100).'%';

            $temp1['danAvg'] =dataFormate($v['danpayment']/$v['danOrder']);
            $temp1['danRate']=(dataFormate($v['danOrder']/$v['danValid'])*100).'%';

            $temp1['htAvg'] =dataFormate($v['htpayment']/$v['htOrder']);
            $temp1['htRate']=(dataFormate($v['htOrder']/$v['htValid'])*100).'%';


            $temp1['allMkz']  = $v['ayMkz']+$v['smMkz']+$v['danMkz']+$v['htMkz'];
            $temp1['allValid']  = $v['ayValid']+$v['smValid']+$v['danValid']+$v['htValid'];
            $temp1['allOrder']= $v['ayOrder']+$v['smOrder']+$v['danOrder']+$v['htOrder'];
            $temp1['allPay']  = $v['aypayment']+$v['smpayment']+$v['danpayment']+$v['htpayment'];
            $temp1['allRate'] = (dataFormate($temp1['allOrder']/$temp1['allValid'])*100).'%';
            $temp1['mkzRate'] = (dataFormate($temp1['allValid']/$temp1['allMkz'])*100).'%';
            $temp1['mkzCb'] = dataFormate($temp1['channel_cost']/$temp1['allMkz']);
            $temp1['validCb'] = dataFormate($temp1['channel_cost']/$temp1['allValid']);
            $temp1['orderCb'] = dataFormate($temp1['channel_cost']/$temp1['allOrder']);
            $temp1['ROI'] = dataFormate($temp1['channel_cost']/$temp1['allPay']);
            array_push($return, $temp1);
        }
        $returnData['code'] = 0;
        $returnData['msg'] = '';
        $returnData['count'] = count($return);
        $returnData['data'] = $return;
        // $returnData['len'] = count($title);
        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');
        $shop= controller('crm/Customer','lib')->get_shop(['shop_code'=>$info['shop_code']]);

        $this->assign('shop_code', $info['shop_code']);
        $this->assign('title', $shop[0]['shop_name'].'-'.$title);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

    // public function renyuan(){
    //     $data = input();

    //     //网销部人员
    //     $staff = getStaffsByWorkType(['shop_code'=>$shop_code,'work_type'=>21]);
    //     $staffIds = array_column($staff,'id');
    //     if($data['staff_id']){
    //         $where['sales_staff_id'] = ['in',$data['staff_id']];//intval($data['staff_id']);//
    //     }else{
    //         $where['sales_staff_id'] = ['in',$staffIds];
    //     }

    //     if(!$data['month']){
    //         return array('code'=>99,'msg'=>'请选择月份');
    //     }else{
    //         $month = getthemonth($data['month']);
    //     }

    //     $returnData['code'] = 200;

    //     $where['c_from'] = 1;
    //     $where['is_status'] = 1;
    //     $where['is_valid'] = 1;
    //     $where['shop_code'] = $data['shop_code'];
    //     $returnData['youxiao'] = db("crm_c_customerinfo")->field('dj_staff,dj_staff_id')->where($where)->where('dj_day','between',[$month[0],$month[1]])->select();

    //     //推广人员 录网络部客资人员
    //     $where['add_shop_code'] = 'xaf016';
    //     $returnData['tuiguang'] = db("crm_c_customerinfo")->field('dj_staff,dj_staff_id')->where($where)->where('dj_day','between',[$month[0],$month[1]])->select();
        
    //     return $returnData;
    // }

}