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

class Jj_channel_data extends Report {

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

    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();
        // 时间区域
        if (!$data['start'] || !$data['end']) {
            $return = ['code' => 200, 'msg' => '请选择时间区间', 'count' => 0, 'data' => '',];
            return json($return);
        }
        $det = db('s_report_form')->alias('f')->join('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'];
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }
        $start = date('Y-m-d', strtotime($data['start']));
        $end = date('Y-m-d 23:59:59', strtotime($data['end']));
        $where['is_status'] = 1;
        $data['shop_code'] = $data['shop_code'] ? $data['shop_code'] : $shop_code;
        $where['c_from'] = 1;


        // 地域id，拼接where条件
        if ($data['province_id']) {
            $where['province_id'] = (int)$data['province_id'];
            $whereTX['c.province_id'] = (int)$data['province_id'];
        }
        if ($data['city_id']) {
            $where['city_id'] = (int)$data['city_id'];
            $whereTX['c.city_id'] = (int)$data['city_id'];
        }

        if ($data['accessModel_id']) {//获取方式
            $where['access_model_id'] = (int)$data['accessModel_id'];
            $whereTX['c.access_model_id'] = (int)$data['accessModel_id'];
        }
        if ($data['channel_name']) {//渠道
            $channel = db('s_channel')->where('shop_code',$shop_code)->where('channel_name','in',trim($data['channel_name']))->select();
        }else{
            $channel = db('s_channel')->where('shop_code',$shop_code)->select();
        }
        $channel_name = array_column($channel,'channel_name');

        $method = 'customer_channel';
        $code = 'add_shop_code';
        $time = '23:59:59';
        $returnData = [];
        $total = [];

        if($data['flag'] == 'week'){
            $week = array_values(getWeekByTime($start,$end));
            $temp = [];
            $i = 1;
            foreach($week as $k=>$v){
                $temp[] = [reset($v),end($v),'第'.$i.'周'];
                $i++;
            }
            $w= 1;
            foreach($temp as $k=>$v){
                $start = $v[0];
                $end = $v[1];
                $sql = '';
                $sql .= "sum(dj_day between '$start' and '$end$time') as mkz,";
                $sql .= "sum(is_valid=1 and dj_day between '$start' and '$end$time') as valid,";//有效
                $sql .= "sum(is_valid=1 and consultation_type_id=1 and dj_day between '$start' and '$end$time') as validHS,";//婚纱有效
                $sql .= "sum(is_valid=1 and consultation_type_id=1 and dj_day between '$start' and '$end$time') as validXZ,";//写真有效
                $sql .= "sum(is_valid=2 and yx_day between '$start' and '$end$time') as dd,";
                $sql .= "sum(is_valid in('3','4','5') and dj_day between '$start' and '$end$time') as wx,";
                $sql .= "sum(customer_tel<>''and customer_tel is not null and dj_day between '$start' and '$end$time') as tel,";
                $sql .= "sum(is_intostore in ('2','3','5') and syjd_day between '$start' and '$end$time') as jd,";
                $sql .= "sum(is_success=1 and order_day between '$start' and '$end $time') as `order`,";
                $sql .= "sum(is_success=1 and consultation_type_id=1 and order_day between '$start' and '$end $time') as orderHS";

                // 录入客资量
                $info= db('c_customerinfo')
                    ->where($where)
                    ->where($code, $data['shop_code'])
                    ->where('customer_channel', 'in',$channel_name)
                    ->group($method)
                    ->column($sql,$method);
//        echo db()->getLastSql();die;
                //套系
                $whereTx['c.c_from'] = 1;
                $whereTx['c.is_status'] = 1;
                $whereTx['c.is_status'] = 1;

                $amount = db('c_customerinfo')->alias('c')->join('c_customer_order o','c.order_number=o.order_sn')
                    ->where($whereTx)
                    ->where('c.'.$code, $data['shop_code'])
                    ->where('c.customer_channel', 'in',$channel_name)
                    ->where('o.order_valid', 2)
                    ->where('c.consultation_type_id', 1)
                    ->where('c.order_day', 'between time', [$start, $end.$time])
                    ->group('c.'.$method)
                    ->column('sum(o.order_amount) as total','c.'.$method);
                $arr = [];
                $arr['id'] = '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;';
                foreach ($channel as $key => $value) {
                    $array = [];
                    $array['id'] = $w;
                    $array['date'] = $v[2]."($v[0]--$v[1])";
                    $array['channel_name'] = $value['channel_name'];
                    $array['mkz'] = $info[$value['channel_name']]['mkz'];
                    $array['valid'] = $info[$value['channel_name']]['valid'];
                    $array['validHS'] = $info[$value['channel_name']]['validHS'];
                    $array['validXZ'] = $info[$value['channel_name']]['validXZ'];
                    $array['wx'] = $info[$value['channel_name']]['wx'];
                    $array['dd'] = $info[$value['channel_name']]['dd'];
                    $array['tel'] = $info[$value['channel_name']]['tel'];
                    $array['telRate'] = (number_format($array['tel'] / $array['valid']*100, 2)) . '%';
                    $array['ddRate'] = (number_format($array['dd'] / $array['mkz']*100, 2)) . '%';
                    $array['jd'] = $info[$value['channel_name']]['jd'];
                    $array['wxRate'] = (number_format($array['wx'] / $array['mkz']*100, 2)) . '%';
                    $array['order'] = $info[$value['channel_name']]['order'];
                    $array['validOrderRate'] = (number_format($array['order']/$array['valid']*100, 2)) . '%';
                    $array['orderRate'] = (number_format($array['order'] / $array['jd']*100, 2)) . '%';
                    $array['orderHS'] = $info[$value['channel_name']]['orderHS'];
                    $array['amountHS'] = sprintf('%.2f',$amount[$value['channel_name']]);
                    $array['avgHS'] = sprintf('%.2f',$amount[$value['channel_name']]/$info[$value['channel_name']]['orderHS']);
                    $returnData[] = $array;

                    $arr['channel_name'] = '总计';
                    $arr['mkz'] += $info[$value['channel_name']]['mkz'];
                    $arr['valid'] += $info[$value['channel_name']]['valid'];
                    $arr['validHS'] += $info[$value['channel_name']]['validHS'];
                    $arr['validXZ'] += $info[$value['channel_name']]['validXZ'];
                    $arr['wx'] += $info[$value['channel_name']]['wx'];
                    $arr['dd'] += $info[$value['channel_name']]['dd'];
                    $arr['tel'] += $info[$value['channel_name']]['tel'];
                    $arr['telRate'] = '';
                    $arr['ddRate'] = '';
                    $arr['jd'] += $info[$value['channel_name']]['jd'];
                    $arr['wxRate'] = '';
                    $arr['order'] += $info[$value['channel_name']]['order'];
                    $arr['validOrderRate'] = '';
                    $arr['orderRate'] = '';
                    $arr['orderHS'] += $info[$value['channel_name']]['order'];
                    $arr['amountHS'] += $amount[$value['channel_name']];
                    $arr['avgHS'] = '';

                    $total['mkz'] += $info[$value['channel_name']]['mkz'];
                    $total['valid'] += $info[$value['channel_name']]['valid'];
                    $total['validHS'] += $info[$value['channel_name']]['validHS'];
                    $total['validXZ'] += $info[$value['channel_name']]['validXZ'];
                    $total['wx']+= $info[$value['channel_name']]['wx'];
                    $total['dd']+= $info[$value['channel_name']]['dd'];
                    $total['tel'] += $info[$value['channel_name']]['tel'];
                    $total['telRate']  = '';
                    $total['ddRate'] = '';
                    $total['jd'] += $info[$value['channel_name']]['jd'];
                    $total['wxRate']= '';
                    $total['order'] += $info[$value['channel_name']]['order'];
                    $total['validOrderRate'] ='';
                    $total['orderRate']  = '';
                    $total['orderHS']  += $info[$value['channel_name']]['order'];
                    $total['amountHS'] += $amount[$value['channel_name']];
                    $total['avgHS']  = '';
                    $w++;
                }
                $arr['telRate'] = sprintf('%.2f',$arr['tel']/$arr['mkz']*100).'%';
                $arr['ddRate'] = sprintf('%.2f',$arr['dd']/$arr['mkz']*100).'%';
                $arr['wxRate'] = sprintf('%.2f',$arr['wx']/$arr['mkz']*100).'%';
                $arr['ValidOrderRate'] = sprintf('%.2f',$arr['order']/$arr['valid']*100).'%';
                $arr['orderRate'] = sprintf('%.2f',$arr['order']/$arr['jd']*100).'%';
                $arr['validOrderRate'] = sprintf('%.2f',$arr['orderNum']/$arr['invCustomer']*100).'%';
                $arr['avgHS'] = sprintf('%.2f',$arr['amountHS']/$arr['orderHS']);
                array_push($returnData,$arr);
            }
            $total['telRate'] = sprintf('%.2f',$total['tel']/$total['mkz']*100).'%';
            $total['ddRate'] = sprintf('%.2f',$total['dd']/$total['mkz']*100).'%';
            $total['wxRate'] = sprintf('%.2f',$total['wx']/$total['mkz']*100).'%';
            $total['ValidOrderRate'] = sprintf('%.2f',$total['order']/$total['valid']*100).'%';
            $total['orderRate'] = sprintf('%.2f',$total['order']/$total['jd']*100).'%';
            $total['validOrderRate'] = sprintf('%.2f',$total['orderNum']/$total['invCustomer']*100).'%';
            $total['avgHS'] = sprintf('%.2f',$total['amountHS']/$total['orderHS']);
            array_push($returnData,$total);
        }elseif($data['flag'] == 'day'){
            $i = 1;
            $date = getDates($start,$end);
            $arr = [];
            foreach($date as $k=>$v){
               $sql = '';
               $sql .= "sum(dj_day between '$v' and '$v $time') as mkz,";
               $sql .= "sum(is_valid=1 and dj_day between '$v' and '$v $time') as valid,";//有效
               $sql .= "sum(is_valid=1 and consultation_type_id=1 and dj_day between '$v' and '$v $time') as validHS,";//婚纱有效
               $sql .= "sum(is_valid=1 and consultation_type_id=1 and dj_day between '$v' and '$v $time') as validXZ,";//写真有效
               $sql .= "sum(is_valid=2 and yx_day between '$v' and '$v $time') as dd,";
               $sql .= "sum(is_valid in('3','4','5') and dj_day between '$v' and '$v $time') as wx,";
               $sql .= "sum(customer_tel<>''and customer_tel is not null and dj_day between '$v' and '$v $time') as tel,";
               $sql .= "sum(is_intostore in ('2','3','5') and syjd_day between '$v' and '$v $time') as jd,";
               $sql .= "sum(is_success=1 and order_day between '$v' and '$v $time') as `order`,";
               $sql .= "sum(is_success=1 and consultation_type_id=1 and order_day between '$v' and '$v $time') as orderHS";

               // 录入客资量
               $info= db('c_customerinfo')
                   ->where($where)
                   ->where($code, $data['shop_code'])
                   ->where('customer_channel', 'in',$channel_name)
                   ->group($method)
                   ->column($sql,$method);
//        echo db()->getLastSql();die;
               //套系
               $whereTx['c.c_from'] = 1;
               $whereTx['c.is_status'] = 1;
               $whereTx['c.is_status'] = 1;

               $amount = db('c_customerinfo')->alias('c')->join('c_customer_order o','c.order_number=o.order_sn')
                   ->where($whereTx)
                   ->where('c.'.$code, $data['shop_code'])
                   ->where('c.customer_channel', 'in',$channel_name)
                   ->where('o.order_valid', 2)
                   ->where('c.consultation_type_id', 1)
                   ->where('c.order_day', 'between time', [$v, $end])
                   ->group('c.'.$method)
                   ->column('sum(o.order_amount) as total','c.'.$method);
               $arr['id'] = '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;';
               foreach ($channel as $key => $value) {
                   $array['id'] = $i;
                   $array['date'] = $v;
                   $array['channel_name'] = $value['channel_name'];
                   $array['mkz'] = $info[$value['channel_name']]['mkz'];
                   $array['valid'] = $info[$value['channel_name']]['valid'];
                   $array['validHS'] = $info[$value['channel_name']]['validHS'];
                   $array['validXZ'] = $info[$value['channel_name']]['validXZ'];
                   $array['wx'] = $info[$value['channel_name']]['wx'];
                   $array['dd'] = $info[$value['channel_name']]['dd'];
                   $array['tel'] = $info[$value['channel_name']]['tel'];
                   $array['telRate'] = (number_format($array['tel'] / $array['valid']*100, 2)) . '%';
                   $array['ddRate'] = (number_format($array['dd'] / $array['mkz']*100, 2)) . '%';
                   $array['jd'] = $info[$value['channel_name']]['jd'];
                   $array['wxRate'] = (number_format($array['wx'] / $array['mkz']*100, 2)) . '%';
                   $array['order'] = $info[$value['channel_name']]['order'];
                   $array['validOrderRate'] = (number_format($array['order']/$array['valid']*100, 2)) . '%';
                   $array['orderRate'] = (number_format($array['order'] / $array['jd']*100, 2)) . '%';
                   $array['orderHS'] = $info[$value['channel_name']]['orderHS'];

                   $array['amountHS'] = sprintf('%.2f',$amount[$value['channel_name']]);
                   $array['avgHS'] = sprintf('%.2f',$amount[$value['channel_name']]/$info[$value['channel_name']]['orderHS']);
                   $returnData[] = $array;

                   $arr['channel_name'] = '总计';
                   $arr['mkz'] += $info[$value['channel_name']]['mkz'];
                   $arr['valid'] += $info[$value['channel_name']]['valid'];
                   $arr['validHS'] += $info[$value['channel_name']]['validHS'];
                   $arr['validXZ'] += $info[$value['channel_name']]['validXZ'];
                   $arr['wx'] += $info[$value['channel_name']]['wx'];
                   $arr['dd'] += $info[$value['channel_name']]['dd'];
                   $arr['tel'] += $info[$value['channel_name']]['tel'];
                   $arr['telRate'] = '';
                   $arr['ddRate'] = '';
                   $arr['jd'] += $info[$value['channel_name']]['jd'];
                   $arr['wxRate'] = '';
                   $arr['order'] += $info[$value['channel_name']]['order'];
                   $arr['validOrderRate'] = '';
                   $arr['orderRate'] = '';
                   $arr['orderHS'] += $info[$value['channel_name']]['order'];
                   $arr['amountHS'] += $amount[$value['channel_name']];
                   $arr['avgHS'] = '';
                   $i++;
               }
           }
            $arr['telRate'] = sprintf('%.2f',$arr['tel']/$arr['mkz']*100).'%';
            $arr['ddRate'] = sprintf('%.2f',$arr['dd']/$arr['mkz']*100).'%';
            $arr['wxRate'] = sprintf('%.2f',$arr['wx']/$arr['mkz']*100).'%';
            $arr['ValidOrderRate'] = sprintf('%.2f',$arr['order']/$arr['valid']*100).'%';
            $arr['orderRate'] = sprintf('%.2f',$arr['order']/$arr['jd']*100).'%';
            $arr['validOrderRate'] = sprintf('%.2f',$arr['orderNum']/$arr['invCustomer']*100).'%';
            $arr['avgHS'] = sprintf('%.2f',$arr['amountHS']/$arr['orderHS']);
            array_push($returnData,$arr);
        }else{
            $sql = '';
            $sql .= "sum(dj_day between '$start' and '$end') as mkz,";
            $sql .= "sum(is_valid=1 and dj_day between '$start' and '$end') as valid,";//有效
            $sql .= "sum(is_valid=1 and consultation_type_id=1 and dj_day between '$start' and '$end') as validHS,";//婚纱有效
            $sql .= "sum(is_valid=1 and consultation_type_id=1 and dj_day between '$start' and '$end') as validXZ,";//写真有效
            $sql .= "sum(is_valid=2 and yx_day between '$start' and '$end') as dd,";
            $sql .= "sum(is_valid in('3','4','5') and dj_day between '$start' and '$end') as wx,";
            $sql .= "sum(customer_tel<>''and customer_tel is not null and dj_day between '$start' and '$end') as tel,";
            $sql .= "sum(is_intostore in ('2','3','5') and syjd_day between '$start' and '$end') as jd,";
            $sql .= "sum(is_success=1 and order_day between '$start' and '$end') as `order`,";
            $sql .= "sum(is_success=1 and consultation_type_id=1 and order_day between '$start' and '$end') as orderHS";

            // 录入客资量
            $info= db('c_customerinfo')
                ->where($where)
                ->where($code, $data['shop_code'])
                ->where('customer_channel', 'in',$channel_name)
                ->group($method)
                ->column($sql,$method);
//        echo db()->getLastSql();die;
            //套系
            $whereTx['c.c_from'] = 1;
            $whereTx['c.is_status'] = 1;
            $whereTx['c.is_status'] = 1;

            $amount = db('c_customerinfo')->alias('c')->join('c_customer_order o','c.order_number=o.order_sn')
                ->where($whereTx)
                ->where('c.'.$code, $data['shop_code'])
                ->where('c.customer_channel', 'in',$channel_name)
                ->where('o.order_valid', 2)
                ->where('c.consultation_type_id', 1)
                ->where('c.order_day', 'between time', [$start, $end])
                ->group('c.'.$method)
                ->column('sum(o.order_amount) as total','c.'.$method);
            $arr = [];
            $i = 1;
            $arr['id'] = '';
            foreach ($channel as $key => $value) {
                $array['id'] = $i;
                $array['channel_name'] = $value['channel_name'];
                $array['mkz'] = $info[$value['channel_name']]['mkz'];
                $array['valid'] = $info[$value['channel_name']]['valid'];
                $array['validHS'] = $info[$value['channel_name']]['validHS'];
                $array['validXZ'] = $info[$value['channel_name']]['validXZ'];
                $array['wx'] = $info[$value['channel_name']]['wx'];
                $array['dd'] = $info[$value['channel_name']]['dd'];
                $array['tel'] = $info[$value['channel_name']]['tel'];
                $array['telRate'] = (number_format($array['tel'] / $array['valid']*100, 2)) . '%';
                $array['ddRate'] = (number_format($array['dd'] / $array['mkz']*100, 2)) . '%';
                $array['jd'] = $info[$value['channel_name']]['jd'];
                $array['wxRate'] = (number_format($array['wx'] / $array['mkz']*100, 2)) . '%';
                $array['order'] = $info[$value['channel_name']]['order'];
                $array['validOrderRate'] = (number_format($array['order']/$array['valid']*100, 2)) . '%';
                $array['orderRate'] = (number_format($array['order'] / $array['jd']*100, 2)) . '%';
                $array['orderHS'] = $info[$value['channel_name']]['orderHS'];

                $array['amountHS'] = sprintf('%.2f',$amount[$value['channel_name']]);
                $array['avgHS'] = sprintf('%.2f',$amount[$value['channel_name']]/$info[$value['channel_name']]['orderHS']);
                $returnData[] = $array;

                $arr['channel_name'] = '总计';
                $arr['mkz'] += $info[$value['channel_name']]['mkz'];
                $arr['valid'] += $info[$value['channel_name']]['valid'];
                $arr['validHS'] += $info[$value['channel_name']]['validHS'];
                $arr['validXZ'] += $info[$value['channel_name']]['validXZ'];
                $arr['wx'] += $info[$value['channel_name']]['wx'];
                $arr['dd'] += $info[$value['channel_name']]['dd'];
                $arr['tel'] += $info[$value['channel_name']]['tel'];
                $arr['telRate'] = '';
                $arr['ddRate'] = '';
                $arr['jd'] += $info[$value['channel_name']]['jd'];
                $arr['wxRate'] = '';
                $arr['order'] += $info[$value['channel_name']]['order'];
                $arr['validOrderRate'] = '';
                $arr['orderRate'] = '';
                $arr['orderHS'] += $info[$value['channel_name']]['order'];
                $arr['amountHS'] += $amount[$value['channel_name']];
                $arr['avgHS'] = '';
                $i++;
            }
            $arr['telRate'] = sprintf('%.2f',$arr['tel']/$arr['mkz']*100).'%';
            $arr['ddRate'] = sprintf('%.2f',$arr['dd']/$arr['mkz']*100).'%';
            $arr['wxRate'] = sprintf('%.2f',$arr['wx']/$arr['mkz']*100).'%';
            $arr['ValidOrderRate'] = sprintf('%.2f',$arr['order']/$arr['valid']*100).'%';
            $arr['orderRate'] = sprintf('%.2f',$arr['order']/$arr['jd']*100).'%';
            $arr['validOrderRate'] = sprintf('%.2f',$arr['orderNum']/$arr['invCustomer']*100).'%';
            $arr['avgHS'] = sprintf('%.2f',$arr['amountHS']/$arr['orderHS']);
            array_push($returnData,$arr);
        }


        $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);
        //获取方式
        $accessModel = controller("Customer", "lib")->accessModel(array('shop_code' =>$info['shop_code']));
        $this->assign("accessModel", $accessModel);

        //客资地域
        // crm省    列表
        $data['type'] = 1;
        $data['shop_code'] = $info['shop_code'];
        $province = controller("Customer", "lib")->address($data);
        $this->assign('province', $province);

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

        $channels = getChannelJson($info['shop_code']);
        $this->assign('channel', json_encode($channels));
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}
