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

class Qqbaby_tg_target extends Report {

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

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

        $info = db('crm_s_report_form')->where('code', $data['code'])->field('code,report_name,report_class_name')->find();
        if (!$info || empty($data['code'])) {
            $this->error('没有此报表。');
        }
        $shop_code = $data['shop_code'] ? $data['shop_code'] : session('shop_code');

        $start = $data['start'] ? date('Y-m-01',strtotime($data['start'])) : date('Y-m-01',strtotime());
        $end = $data['start'] ? date('Y-m-t',strtotime($data['start'])) : date('Y-m-t',strtotime());
        
        $where['is_status'] = 1;
        $where['c_from'] = 1;
        // $where['c.add_shop_code'] = $shop_code;

        $where_sks['sks.shop_code'] = $shop_code;
        $wheres['shop_code'] = $shop_code;

        // 实收用sql
        $payStart = date('Ymd',strtotime($start));
        $payEnd = date('Ymd',strtotime($end));
        $whereSqlP= " AND c.c_from=1 AND c.is_status=1 AND c.add_shop_code='$shop_code' AND p.paymentdate BETWEEN '$payStart' AND '$payEnd'";
        // 城市
        if($data['city']){
            //目标
            $where_sks['sks.city_id'] = $data['city'];
            $wheres['city_id'] = $data['city'];

            // 订单用sql
            $city_id = $data['city'];
            $whereSqlP.= " AND c.city_id=$city_id";
        }

        //2021-06-16 @丽丽 推广部目标报表 增加 一个条件 取当前的部门
        $mc = new \app\common\lib\mcInterface();
        // 部门
        $dj_staff_id = [];
        $department = $mc->getAllStaffListByShopcode(['shop_code'=>$shop_code,'department_name'=>session('department_name')]);
        foreach ($department as $key => $value) {
            $dj_staff_id[] = $value['id'];
        }
        $where['c.dj_staff_id'] = ['in',$dj_staff_id];
        $dj_staff_ids = "'".implode("','", $dj_staff_id)."'";
        $whereSqlP.= " AND c.dj_staff_id in($dj_staff_ids)";

        // // 渠道
        // 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)";
        //     $whereSqlP.= " AND c.customer_channel IN ($channelstr)";
        // }


        //今日 数量 本月时间时 显示
        if (date("Y-m") == date('Y-m',strtotime($data['start']))) {
            $jr = 1;
            $tianshu = diffBetweenTwoDays($start,$end);
        }else{
            $jr = 2;
        }

        $recTime .= microtime(true).'<br />';
        $returnData = [];
        // 目标
        $customerinfo = db('crm_s_kpi_staffgoal')->alias("sks")
            ->join("crm_s_channel cl","sks.channel_id = cl.id","left")
            ->where($where_sks)
            ->where('month',date('Y-m',strtotime($start)))
            ->where('kpi_id in ("budgetAmount","newCustomerNum","valueCustomerRate","performanceAmount")')
            ->field('cl.channel_info_name,sks.channel_name,kpi_id,kpi_value,city_name')
            ->select();
        $sql1 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        $order = [];
        foreach ($customerinfo as $k => $v) {
            $returnData[$v['channel_name']]['qd_ej'] = $v['channel_info_name'];
            $returnData[$v['channel_name']]['qd'] = $v['channel_name'];
            if ($v['kpi_id'] == 'budgetAmount') {
                $returnData[$v['channel_name']]['ys_mb'] = $v['kpi_value'];
            }
            if ($v['kpi_id'] == 'newCustomerNum') {
                $returnData[$v['channel_name']]['mkz_mb'] = $v['kpi_value'];
            }
            if ($v['kpi_id'] == 'valueCustomerRate') {
                $returnData[$v['channel_name']]['kz_yxl_mb'] = $v['kpi_value'];
            }
            if ($v['kpi_id'] == 'performanceAmount') {
                $returnData[$v['channel_name']]['yj_mb'] = $v['kpi_value'];
            }
        }

        // 毛客资
        $customerinfo = db('crm_c_customerinfo')->alias("c")
            ->join("crm_s_channel cl","c.customer_channel_id = cl.id","left")
            ->where($where)
            ->where('c.dj_day','between',[$start,date('Y-m-d 23:59:59',strtotime($end))])
            ->field('cl.channel_info_name,c.customer_channel,sales_staff_id,dj_day')
            ->select();
        $sql2 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        $order = [];
        foreach ($customerinfo as $k => $v) {
            // $returnData[$v['customer_channel']]['city'] = $v['city'];
            $returnData[$v['customer_channel']]['qd_ej'] = $v['channel_info_name'];
            $returnData[$v['customer_channel']]['qd'] = $v['customer_channel'];
            $returnData[$v['customer_channel']]['mkz'] += 1;
            //南宁 cc 定的规则 2021-05-19  有效客咨只要有【追踪人】就算 
            if ($v['sales_staff_id']) {
                $returnData[$v['customer_channel']]['kz_yx'] += 1;
            }

            //今日 数量 本月时间时 显示
            if ($jr == 1 && $v['dj_day'] >= date('Y-m-d 00:00:00',strtotime($start)) && $v['dj_day'] <= date('Y-m-d 23:59:59',strtotime($end))) {
                $returnData[$v['customer_channel']]['kz_jr'] += 1;

                if ($v['sales_staff_id']) {
                    $returnData[$v['customer_channel']]['kz_yx_jr'] += 1;
                }
            }
        }
       
        // 花费
        $channelCost = db('crm_s_channel_cost')
            ->where('cost_date','between',[$start,$end])
            ->where($wheres)
            ->field('channel_info_name,channel_name,cost_date,cost,city_name')
            ->select();
        $sql4 = db()->getLastSql();
        $recTime .= microtime(true).'<br />';
        foreach ($channelCost as $k => $v) {
            $returnData[$v['channel_name']]['qd_ej'] = $v['channel_info_name'];
            $returnData[$v['channel_name']]['city'] = $v['city_name'];
            $returnData[$v['channel_name']]['qd'] = $v['channel_name'];
            $returnData[$v['channel_name']]['hf'] += $v['cost'];
        }
        
        // 收款 只算一销
        $yjstart = date('Ymd',strtotime($start));
        $yjend = date('Ymd',strtotime($end));
        // $sql5 = 'SELECT `c`.`customer_channel`,`c`.`dj_staff`,`c`.`dj_day`,`p`.`orderId`,`p`.`payment_money`,`p`.`paytype`,`p`.`isshishou` FROM crm_c_customerinfo c LEFT JOIN crm_c_customer_order co ON `c`.`id`=`co`.`customer_id` LEFT JOIN `c_paymentcontent` p ON `co`.`order_sn`=`p`.`orderId`  WHERE `p`.`id` IS NOT NULL  AND `p`.`suoshu` ="前期款" '.$whereSqlP;
        $sql5 = 'SELECT `c`.`customer_channel`,`c`.`dj_day`,`p`.`orderId`,`p`.`payment_money`,`p`.`paytype`,`p`.`isshishou` FROM crm_c_customerinfo c LEFT JOIN c_customerorder o ON `c`.`id`=`o`.`crm_customer_id` LEFT JOIN `c_paymentcontent` p ON `o`.`orderId`=`p`.`orderId`  WHERE `p`.`id` IS NOT NULL  AND `p`.`suoshu` ="前期款" '.$whereSqlP;
        $payInfos = db()->query($sql5);
        $recTime .= microtime(true).'<br />';
        foreach ($payInfos as $k => $v) {
        	// if ($v['paytype'] == 'VIP卡') {
        	// 	$returnData[$v['customer_channel'].$v['dj_staff']]['vip'] += $v['payment_money'];
        	// }

            if ($v['isshishou'] == '实收') {
                $returnData[$v['customer_channel']]['ssje'] += $v['payment_money'];

                if (strtotime($v['dj_day']) < $d1) {
                    $returnData[$v['customer_channel']]['yj_hd'] += $v['payment_money'];
                }else{
                    $returnData[$v['customer_channel']]['yj_dy'] += $v['payment_money'];
                }
            }
        }

        foreach ($returnData as $k => $v) {
            $returnData[$k]['city'] = $data['city_name'];

            $returnData[$k]['ys_mb'] = $v['ys_mb'] ?: 0;
            $returnData[$k]['mkz_mb'] = $v['mkz_mb'] ?: 0;
            $returnData[$k]['kz_yxl_mb'] = $v['kz_yxl_mb'] ?: 0;
            $returnData[$k]['yj_mb'] = $v['yj_mb'] ?: 0;

            //有效客资目标 = 毛客资 * 有效率
            $returnData[$k]['kz_yx_mb'] = intval($returnData[$k]['mkz_mb']*$returnData[$k]['kz_yxl_mb']/100);

            //毛成本目标 = 总预算 / 毛客资
            $returnData[$k]['mcb_mb'] = sprintf('%.2f', $returnData[$k]['ys_mb']/$returnData[$k]['mkz_mb']);
            
            //有效成本 = 总预算 / 有效率
            $returnData[$k]['yxcb_mb'] = sprintf('%.2f', $returnData[$k]['ys_mb']/$returnData[$k]['kz_yx_mb']);

            $returnData[$k]['mkz'] = $v['mkz'] ?: 0;
            $returnData[$k]['kz_yx'] = $v['kz_yx'] ?: 0;

            //剩余有效目标 = 有效客资目标 - 有效客资
            $returnData[$k]['sy_yx'] = $returnData[$k]['kz_yx_mb'] - $returnData[$k]['kz_yx'];

            $returnData[$k]['hf'] = $v['hf'] ?: 0;

            //毛成本 = 花费 / 毛客资
            $returnData[$k]['mcb'] = sprintf('%.2f', $returnData[$k]['hf']/$returnData[$k]['mkz']);

            //有效成本 = 花费 / 有效客资
            $returnData[$k]['yxcb'] = sprintf('%.2f', $returnData[$k]['hf']/$returnData[$k]['kz_yx']);

            //有效率 = 有效客资 / 毛客资
            $returnData[$k]['kz_yxl'] = sprintf('%.2f', $returnData[$k]['kz_yx']/$returnData[$k]['mkz'])*100;

            $returnData[$k]['yj_hd'] = $v['yj_hd'] ?: 0;

            if ($jr == 1 && $tianshu) {
                $returnData[$k]['jr_mb'] = intval($returnData[$k]['mkz_mb']/$tianshu);
                $returnData[$k]['tianshu'] = $tianshu;
            }

            $returnData[$k]['kz_yxl_mb'] = $returnData[$k]['kz_yxl_mb']."%";
            $returnData[$k]['kz_yxl'] = $returnData[$k]['kz_yxl']."%";

            // 总计各种数据
            $arr['city'] = '总计';
            $all['qd'] = '';
            $arr['ys_mb'] += $v['ys_mb'];
            $arr['mkz_mb'] += $v['mkz_mb'];
            $arr['kz_yxl_mb'] += $v['kz_yxl_mb'];
            $arr['yj_mb'] += $v['yj_mb'];
            $arr['mkz'] += $v['mkz'];
            $arr['kz_yx'] += $v['kz_yx'];
            $arr['hf'] += $v['hf'];
            $arr['yj_hd'] += $v['yj_hd'];
            $arr['yj_dy'] += $v['yj_dy'];
            $arr['jr_mb'] += $returnData[$k]['jr_mb'];
            $arr['kz_jr'] += $v['kz_jr'];
            $arr['kz_yx_jr'] += $v['kz_yx_jr'];
        }
        $returnData = array_values($returnData);
        
        // 总计各种率及成本
        $arr['kz_yx_mb'] = intval($arr['mkz_mb']*$arr['kz_yxl_mb']/100);
        $arr['mcb_mb'] = $arr['ys_mb'] ? (sprintf('%.2f', $arr['ys_mb']/$arr['mkz_mb'])) : '0';
        $arr['yxcb_mb'] = $arr['ys_mb'] ? (sprintf('%.2f', $arr['ys_mb']/$arr['kz_yx_mb'])) : '0';
        $arr['sy_yx'] = $arr['kz_yx_mb'] - $arr['kz_yx'];
        $arr['mcb'] = $arr['hf'] ? (sprintf('%.2f', $arr['hf']/$arr['mkz'])) : '0';
        $arr['yxcb'] = $arr['hf'] ? (sprintf('%.2f', $arr['hf']/$arr['kz_yx'])) : '0';
        $arr['kz_yxl'] = $arr['kz_yx'] ? (sprintf('%.2f', $arr['kz_yx']/$arr['mkz'])*100)."%" : '0%';
        $arr['kz_yxl_mb'] = $arr['kz_yxl_mb']."%";
        array_push($returnData, $arr);

        // foreach ($returnData as $k => $v) {
        //     if(empty($v['qd_ej']) && empty($v['qd_sj']) && empty($v['dj_staff'])){
        //         unset($returnData[$k]);
        //     }
        // }

        $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'] = $returnData;
        $return['count']= count($returnData);
        return json($return);
    }
    public function show(){
        $menu_code = strtolower(input('param.menu_code'));
        $info = db('crm_s_report_form')->where('code', $menu_code)->field('code,report_name,report_class_name')->find();
        if (!$info) {
            $this->error('没有此报表。');
        }

        $shop_code = session('shop_code');
        $shop= controller('crm/Customer','lib')->get_shop(['shop_code'=>$shop_code]);
        // 来源渠道
        $channel = getChannelJson($shop_code);
        $this->assign("channel", json_encode($channel));

        // 省
        // $data['type']=1;
        // $province=controller("Customer","lib")->address($data);
        // $this->assign("province",$province);
        //城市
        $data['type']=2;
        $address=controller("Customer","lib")->address($data);
        $this->assign("address",$address);

        // 品牌
        $brandArr = db('crm_s_brandset')->select();
        $this->assign('brandArr',$brandArr);
        // 消费类型
        $consumptionType = controller("Customer", "lib")->consumptionType(['brandclass_id'=>session('brandclass_id')]);
        $this->assign("consumptionType", $consumptionType);

        $mc = new \app\common\lib\mcInterface();
        // 部门
        $departmentList = $mc->getDepartmentByShopcode($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->getStaffByDepartment($staffParam);
        $this->assign("djStaffList",$djStaffList);
        // 追踪人
        // $salesStaffList = $mc->getStaffByDepartment($staffParam);
        // $this->assign("salesStaffList",$salesStaffList);

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

        $this->assign('title', $shop[0]['shop_name'].'-'.$info['report_name']);
        $this->assign('code', $info['code']);
        // 默认本月
        $this->assign('start', date('Y-m'));
        return $this->fetch($display);
    }

}