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

class Ds_qd_month extends Report {

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

    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();
        $year = trim($data['timeArea'],',');
        $shop_code = $data['shop_code'];
        $where['is_status'] = 1;
        $where['c_from'] = 1;
        $where['customer_type'] = 1;
        $where['add_shop_code'] = $shop_code;
        $shop= controller('crm/Customer','lib')->get_shop(['shop_code'=>$shop_code]);
//        if($shop['shop_type'] == 1 || $shop['shop_type'] == 3){
            $whereT['consultation_type_id'] = HS_TYPE;
//        }
        // 来源渠道
        $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
        $channelIds = array_column($channels,'id');
        $all = [];
        $total = 0;
        switch ($data['mode']) {
            //花费
            case 1:
                $cost = db('c_customer_cost')
                    ->where('shop_code', $shop_code)
                    ->where('channel_id', 'in',$channelIds)
                    ->where('DATE_FORMAT(cost_time,"%Y")=' .$year)
                    ->group('channel_id,month')
                    ->field('channel_id,DATE_FORMAT(cost_time,"%m") month,sum(cost_avg) as cost')
                    ->select();
                foreach($cost as $k=>$v){
                    $all[intval($v['month']).'_'.$v['channel_id']] = sprintf('%.2f',$v['cost']);
                }
                break;
            //有效
            case 2:
                $valid = db('c_customerinfo')
                    ->where($where)
                    ->where($whereT)
                    ->where('is_valid',1)
                    ->where('customer_channel_id', 'in',$channelIds)
                    ->where('DATE_FORMAT(yx_day,"%Y")=' .$year)
                    ->group('customer_channel_id,month')
                    ->field('customer_channel_id,count(*) num,DATE_FORMAT(yx_day,"%m") month')
                    ->select();
                foreach($valid as $k=>$v){
                    $all[intval($v['month']).'_'.$v['customer_channel_id']] = $v['num'];
                }
                break;
            //进店
            case 3:
                $jd = db('c_customerinfo')
                    ->where($where)
                    ->where($whereT)
                    ->where('is_intostore', 'in','2,3')
                    ->where('customer_channel_id', 'in',$channelIds)
                    ->where('DATE_FORMAT(syjd_day,"%Y")=' .$year)
                    ->group('customer_channel_id,month')
                    ->field('customer_channel_id,count(*) num,DATE_FORMAT(syjd_day,"%m") month')
                    ->select();
                foreach($jd as $k=>$v){
                    $all[intval($v['month']).'_'.$v['customer_channel_id']] = $v['num'];
                }
                break;
            //新单
            case 4:
                $order = db('c_customerinfo')
                    ->where($where)
                    ->where($whereT)
                    ->where('is_success', 1)
                    ->where('customer_channel_id', 'in',$channelIds)
                    ->where('DATE_FORMAT(order_day,"%Y")=' .$year)
                    ->group('customer_channel_id,month')
                    ->field('customer_channel_id,count(*) num,DATE_FORMAT(order_day,"%m") month')
                    ->select();

                foreach($order as $k=>$v){
                    $all[intval($v['month']).'_'.$v['customer_channel_id']] = $v['num'];
                }
                break;
            //有效获取成本
            case 5:
                $cost = db('c_customer_cost')
                    ->where('shop_code', $shop_code)
                    ->where('channel_id', 'in',$channelIds)
                    ->where('DATE_FORMAT(cost_time,"%Y")=' .$year)
                    ->group('channel_id,month')
                    ->field('channel_id,DATE_FORMAT(cost_time,"%m") month,sum(cost_avg) cost')
                    ->select();
                $valid = db('c_customerinfo')
                    ->where($where)
                    ->where($whereT)
                    ->where('is_valid',1)
                    ->where('customer_channel_id', 'in',$channelIds)
                    ->where('DATE_FORMAT(yx_day,"%Y")=' .$year)
                    ->group('customer_channel_id,month')
                    ->field('customer_channel_id,count(*) num,DATE_FORMAT(yx_day,"%m") month')
                    ->select();
                $hf = [];
                foreach($cost as $k=>$v){
                    $hf[intval($v['month']).'_'.$v['channel_id']] = $v['cost'];
                }
                foreach($valid as $k=>$v){
                    $yx[intval($v['month']).'_'.$v['customer_channel_id']] = $v['num'];
                }
                foreach($hf as $kk=>$vv){
                    $all[$kk] = sprintf('%.2f',$vv/$yx[$kk]);
                }
                break;
            //新单成本
            case 6:
                $cost = db('c_customer_cost')
                    ->where('shop_code', $shop_code)
                    ->where('channel_id', 'in',$channelIds)
                    ->where('DATE_FORMAT(cost_time,"%Y")=' .$year)
                    ->group('channel_id,month')
                    ->field('channel_id,DATE_FORMAT(cost_time,"%m") month,sum(cost_avg) cost')
                    ->select();
                $order = db('c_customerinfo')
                    ->where($where)
                    ->where($whereT)
                    ->where('is_success', 1)
                    ->where('customer_channel_id', 'in',$channelIds)
                    ->where('DATE_FORMAT(order_day,"%Y")=' .$year)
                    ->group('customer_channel_id,month')
                    ->field('customer_channel_id,count(*) num,DATE_FORMAT(order_day,"%m") month')
                    ->select();
                $hf = [];
                foreach($cost as $k=>$v){
                    $hf[intval($v['month']).'_'.$v['channel_id']] = $v['cost'];
                }
                foreach($order as $k=>$v){
                    $dd[intval($v['month']).'_'.$v['customer_channel_id']] = $v['num'];
                }
                foreach($hf as $kk=>$vv){
                    $all[$kk] = sprintf('%.2f',$vv/$dd[$kk]);
                }
                break;
            //转换率 订单/有效客资
            case 7:
                $order = db('c_customerinfo')
                    ->where($where)
                    ->where($whereT)
                    ->where('is_success', 1)
                    ->where('customer_channel_id', 'in',$channelIds)
                    ->where('DATE_FORMAT(order_day,"%Y")=' .$year)
                    ->group('customer_channel_id,month')
                    ->field('customer_channel_id,count(*) num,DATE_FORMAT(order_day,"%m") month')
                    ->select();
                $valid = db('c_customerinfo')
                    ->where($where)
                    ->where($whereT)
                    ->where('is_valid',1)
                    ->where('customer_channel_id', 'in',$channelIds)
                    ->where('DATE_FORMAT(yx_day,"%Y")=' .$year)
                    ->group('customer_channel_id,month')
                    ->field('customer_channel_id,count(*) num,DATE_FORMAT(yx_day,"%m") month')
                    ->select();
                $dd = [];
                foreach($order as $k=>$v){
                    $dd[intval($v['month']).'_'.$v['customer_channel_id']] = $v['num'];
                }
                foreach($valid as $k=>$v){
                    $yx[intval($v['month']).'_'.$v['customer_channel_id']] = $v['num'];
                }
                foreach($dd as $kk=>$vv){
                    $all[$kk] = sprintf('%.2f',$vv/$yx[$kk]*100).'%';
                }
                break;
        }
        // 渠道总计
        if($data['mode'] == 5){
            $totalCost = db('c_customer_cost')
                ->where('shop_code', $shop_code)
                ->where('channel_id', 'in',$channelIds)
                ->where('DATE_FORMAT(cost_time,"%Y")=' .$year)
                ->group('channel_id')
                ->column('channel_id,sum(cost_avg) cost','channel_id');
            $totalValid = db('c_customerinfo')
                ->where($where)
                ->where($whereT)
                ->where('is_valid',1)
                ->where('customer_channel_id', 'in',$channelIds)
                ->where('DATE_FORMAT(yx_day,"%Y")=' .$year)
                ->group('customer_channel_id')
                ->column('customer_channel_id,count(*) num','customer_channel_id');
        }elseif($data['mode']==6){
            $totalCost = db('c_customer_cost')
                ->where('shop_code', $shop_code)
                ->where('channel_id', 'in',$channelIds)
                ->where('DATE_FORMAT(cost_time,"%Y")=' .$year)
                ->group('channel_id')
                ->column('channel_id,sum(cost_avg) cost','channel_id');
            $totalOrder = db('c_customerinfo')
                ->where($where)
                ->where($whereT)
                ->where('is_success',1)
                ->where('customer_channel_id', 'in',$channelIds)
                ->where('DATE_FORMAT(order_day,"%Y")=' .$year)
                ->group('customer_channel_id')
                ->column('customer_channel_id,count(*) num','customer_channel_id');
        }elseif($data['mode']==7){
            $totalValid = db('c_customerinfo')
                ->where($where)
                ->where($whereT)
                ->where('is_valid',1)
                ->where('customer_channel_id', 'in',$channelIds)
                ->where('DATE_FORMAT(yx_day,"%Y")=' .$year)
                ->group('customer_channel_id')
                ->column('customer_channel_id,count(*) num','customer_channel_id');
            $totalOrder = db('c_customerinfo')
                ->where($where)
                ->where($whereT)
                ->where('is_success',1)
                ->where('customer_channel_id', 'in',$channelIds)
                ->where('DATE_FORMAT(order_day,"%Y")=' .$year)
                ->group('customer_channel_id')
                ->column('customer_channel_id,count(*) num','customer_channel_id');
        }
        $returnData = [];
        $lastTotal['channel_class_name'] = '';
        $lastTotal['channel_info_name'] = '';
        $lastTotal['channel_name'] = '总计';
       foreach($channels as $k=>$v){
           $arr = [];
           $arr['channel_class_name'] = $v['channel_class_name'];
           $arr['channel_info_name'] = $v['channel_info_name'];
           $arr['channel_name'] = $v['channel_name'];
           $arr['month1'] = $all['1_'.$v['id']];
           $arr['month2'] = $all['2_'.$v['id']];
           $arr['month3'] = $all['3_'.$v['id']];
           $arr['month4'] = $all['4_'.$v['id']];
           $arr['month5'] = $all['5_'.$v['id']];
           $arr['month6'] = $all['6_'.$v['id']];
           $arr['month7'] = $all['7_'.$v['id']];
           $arr['month8'] = $all['8_'.$v['id']];
           $arr['month9'] = $all['9_'.$v['id']];
           $arr['month10'] = $all['10_'.$v['id']];
           $arr['month11'] = $all['11_'.$v['id']];
           $arr['month12'] = $all['12_'.$v['id']];
           if($data['mode']<=4){
               $arr['total'] = $arr['month1']+ $arr['month2']+ $arr['month3']+ $arr['month4']+ $arr['month5']+ $arr['month6']+ $arr['month7']+ $arr['month8']+ $arr['month9']+ $arr['month10']+ $arr['month11']+ $arr['month12'];
               //总计
               $lastTotal['month1'] += $arr['month1'];
               $lastTotal['month2'] += $arr['month2'];
               $lastTotal['month3'] += $arr['month3'];
               $lastTotal['month4'] += $arr['month4'];
               $lastTotal['month5'] += $arr['month5'];
               $lastTotal['month6'] += $arr['month6'];
               $lastTotal['month7'] += $arr['month7'];
               $lastTotal['month8'] += $arr['month8'];
               $lastTotal['month9'] += $arr['month9'];
               $lastTotal['month10'] += $arr['month10'];
               $lastTotal['month11'] += $arr['month11'];
               $lastTotal['month12'] += $arr['month12'];
               $lastTotal['total'] += $arr['total'];
           }elseif($data['mode'] == 5){
               $arr['total'] = sprintf('%.2f',$totalCost[$v['id']]/$totalValid[$v['id']]);
           }elseif($data['mode'] == 6){
               $arr['total'] = sprintf('%.2f',$totalCost[$v['id']]/$totalOrder[$v['id']]);
           }elseif($data['mode'] == 7){
               $arr['total'] = sprintf('%.2f',$totalOrder[$v['id']]/$totalValid[$v['id']]*100).'%';
           }

           array_push($returnData,$arr);
       }
       // 每月总计
       if($data['mode']==5 || $data['mode'] == 6 || $data['mode'] == 7){
           $cost = db('c_customer_cost')
               ->where('shop_code', $shop_code)
               ->where('channel_id', 'in',$channelIds)
               ->where('DATE_FORMAT(cost_time,"%Y")=' .$year)
               ->group('month')
               ->field('sum(cost_avg) cost,DATE_FORMAT(cost_time,"%Y-%m") month')
                ->select();

           $lastCost = [];
           foreach($cost as $k=>$v){
               $lastCost[intval(substr($v['month'],5))] = $v;
           }
           if($data['mode'] == 5){
               $valid = db('c_customerinfo')
                   ->where($where)
                   ->where($whereT)
                   ->where('is_valid',1)
                   // ->where('consultation_type_id',HS_TYPE)
                   ->where('customer_channel_id', 'in',$channelIds)
                   ->where('DATE_FORMAT(yx_day,"%Y")=' .$year)
                   ->group('month')
                   ->field('customer_channel_id,count(*) num,DATE_FORMAT(yx_day,"%Y-%m") month')
                   ->select();
           }elseif($data['mode'] == 6){
               $order = db('c_customerinfo')
                   ->where($where)
                   ->where($whereT)
                   ->where('is_success',1)
                   ->where('customer_channel_id', 'in',$channelIds)
                   ->where('DATE_FORMAT(order_day,"%Y")=' .$year)
                   ->group('month')
                   ->field('customer_channel_id,count(*) num,DATE_FORMAT(order_day,"%Y-%m") month')
                   ->select();
           }else{
               $valid = db('c_customerinfo')
                   ->where($where)
                   ->where($whereT)
                   ->where('is_valid',1)
                   // ->where('consultation_type_id',HS_TYPE)
                   ->where('customer_channel_id', 'in',$channelIds)
                   ->where('DATE_FORMAT(yx_day,"%Y")=' .$year)
                   ->group('month')
                   ->field('customer_channel_id,count(*) num,DATE_FORMAT(yx_day,"%Y-%m") month')
                   ->select();
               $order = db('c_customerinfo')
                   ->where($where)
                   ->where($whereT)
                   ->where('is_success',1)
                   ->where('customer_channel_id', 'in',$channelIds)
                   ->where('DATE_FORMAT(order_day,"%Y")=' .$year)
                   ->group('month')
                   ->field('customer_channel_id,count(*) num,DATE_FORMAT(order_day,"%Y-%m") month')
                   ->select();
           }

           $lastValid = [];
           foreach($valid as $k=>$v){
               $lastValid[intval(substr($v['month'],5))] = $v;
           }
           $lastOrder = [];
           foreach($order as $k=>$v){
               $lastOrder[intval(substr($v['month'],5))] = $v;
           }

           if($data['mode']==5){
              //总计
               $lastTotal['month1'] = sprintf('%.2f',$lastCost[1]['cost']/$lastValid[1]['num']);
               $lastTotal['month2'] = sprintf('%.2f',$lastCost[2]['cost']/$lastValid[2]['num']);
               $lastTotal['month3'] = sprintf('%.2f',$lastCost[3]['cost']/$lastValid[3]['num']);
               $lastTotal['month4'] = sprintf('%.2f',$lastCost[4]['cost']/$lastValid[4]['num']);
               $lastTotal['month5'] = sprintf('%.2f',$lastCost[5]['cost']/$lastValid[5]['num']);
               $lastTotal['month6'] = sprintf('%.2f',$lastCost[6]['cost']/$lastValid[6]['num']);
               $lastTotal['month7'] = sprintf('%.2f',$lastCost[7]['cost']/$lastValid[7]['num']);
               $lastTotal['month8'] = sprintf('%.2f',$lastCost[8]['cost']/$lastValid[8]['num']);
               $lastTotal['month9'] = sprintf('%.2f',$lastCost[9]['cost']/$lastValid[9]['num']);
               $lastTotal['month10'] = sprintf('%.2f',$lastCost[10]['cost']/$lastValid[10]['num']);
               $lastTotal['month11'] = sprintf('%.2f',$lastCost[11]['cost']/$lastValid[11]['num']);
               $lastTotal['month12'] = sprintf('%.2f',$lastCost[12]['cost']/$lastValid[12]['num']);
               $c = array_column($lastCost,'cost');
               $val = array_column($lastValid,'num');
               $lastTotal['total'] = sprintf('%.2f',array_sum($c)/array_sum($val));
           }else if ($data['mode'] == 6) {
              //总计
               $lastTotal['month1'] = sprintf('%.2f',$lastCost[1]['cost']/$lastOrder[1]['num']);
               $lastTotal['month2'] = sprintf('%.2f',$lastCost[2]['cost']/$lastOrder[2]['num']);
               $lastTotal['month3'] = sprintf('%.2f',$lastCost[3]['cost']/$lastOrder[3]['num']);
               $lastTotal['month4'] = sprintf('%.2f',$lastCost[4]['cost']/$lastOrder[4]['num']);
               $lastTotal['month5'] = sprintf('%.2f',$lastCost[5]['cost']/$lastOrder[5]['num']);
               $lastTotal['month6'] = sprintf('%.2f',$lastCost[6]['cost']/$lastOrder[6]['num']);
               $lastTotal['month7'] = sprintf('%.2f',$lastCost[7]['cost']/$lastOrder[7]['num']);
               $lastTotal['month8'] = sprintf('%.2f',$lastCost[8]['cost']/$lastOrder[8]['num']);
               $lastTotal['month9'] = sprintf('%.2f',$lastCost[9]['cost']/$lastOrder[9]['num']);
               $lastTotal['month10'] = sprintf('%.2f',$lastCost[10]['cost']/$lastOrder[10]['num']);
               $lastTotal['month11'] = sprintf('%.2f',$lastCost[11]['cost']/$lastOrder[11]['num']);
               $lastTotal['month12'] = sprintf('%.2f',$lastCost[12]['cost']/$lastOrder[12]['num']);
               $c = array_column($lastCost,'cost');
               $val = array_column($lastOrder,'num');
               $lastTotal['total'] = sprintf('%.2f',array_sum($c)/array_sum($val));
           }elseif($data['mode']==7){
               //总计
               $lastTotal['month1'] = sprintf('%.2f',$lastOrder[1]['num']/$lastValid[1]['num']*100).'%';
               $lastTotal['month2'] = sprintf('%.2f',$lastOrder[2]['num']/$lastValid[2]['num']*100).'%';
               $lastTotal['month3'] = sprintf('%.2f',$lastOrder[3]['num']/$lastValid[3]['num']*100).'%';
               $lastTotal['month4'] = sprintf('%.2f',$lastOrder[4]['num']/$lastValid[4]['num']*100).'%';
               $lastTotal['month5'] = sprintf('%.2f',$lastOrder[5]['num']/$lastValid[5]['num']*100).'%';
               $lastTotal['month6'] = sprintf('%.2f',$lastOrder[6]['num']/$lastValid[6]['num']*100).'%';
               $lastTotal['month7'] = sprintf('%.2f',$lastOrder[7]['num']/$lastValid[7]['num']*100).'%';
               $lastTotal['month8'] = sprintf('%.2f',$lastOrder[8]['num']/$lastValid[8]['num']*100).'%';
               $lastTotal['month9'] = sprintf('%.2f',$lastOrder[9]['num']/$lastValid[9]['num']*100).'%';
               $lastTotal['month10'] = sprintf('%.2f',$lastOrder[10]['num']/$lastValid[10]['num']*100).'%';
               $lastTotal['month11'] = sprintf('%.2f',$lastOrder[11]['num']/$lastValid[11]['num']*100).'%';
               $lastTotal['month12'] = sprintf('%.2f',$lastOrder[12]['num']/$lastValid[12]['num']*100).'%';
               $c = array_column($lastOrder,'num');
               $val = array_column($lastValid,'num');
               $lastTotal['total'] = sprintf('%.2f',array_sum($c)/array_sum($val)*100).'%';
           }

       }
        array_push($returnData,$lastTotal);
        // 导出
        if ($data['need'] == 1) {
            $title = array( '渠道大类', '渠道小类', '渠道名称', '1月', '2月', '3月', '4月', '5月', '6月', '7月', '8月', '9月', '10月', '11月', '12月','总计');
            $code = $this->getCode();
            $ff = db('s_report_form')->where('code', $code)->value('report_name');
            $shop = controller('crm/Customer','lib')->get_shop(['shop_code'=>$data['shop_code']]);
            $file = $shop[0]['shop_name'].'-'.$ff.'.xlsx';
            controller("crm/Customer", "lib")->export($title, $returnData, $file);
        } else {
            $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('s_report_form')->alias('f')->join('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']]);
        $this->assign('shop', $shop);

        $display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.strtolower($info['report_class_name']).'.html';
        $title = db('s_report_form')->where('code', $code)->value('report_name');
        $this->assign('title', $shop[0]['shop_name'].'-'.$title);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}