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

class Lm_ds_department_data extends Report {

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

    public function getLevel(){
        return $this->level;

    }
    public function getType(){
        return $this->type;
    }
    public function getCode(){
        return $this->code;
    }
    public function data()
    {
        $data = input();
        $start = date('Y-m-d 00:00:00', strtotime($data['start']));
        $end = date('Y-m-d 23:59:59', strtotime($data['end']));

        $id = db('s_report_form')->where('code', $this->getCode())->value('id');
        $det = db('s_report_detail')->where('form_id',$id)->field('shop_code,brandclass_id')->find();
        $shop_code = $det['shop_code'];
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }
        $where['is_status'] = 1;
        $where['c_from'] = 1;
        $where['customer_type'] = 1;
        $where['add_shop_code'] = $shop_code;

        $whereD = [];
        if($data['department_id']){
            $whereD['department_id'] = $data['department_id'];
            $where['dj_department_id'] = $data['department_id'];
        }
        $whereC['c.is_status'] = 1;
        $whereC['c.c_from'] = 1;
        $whereC['c.customer_type'] = 1;
        $whereC['c.add_shop_code'] = $shop_code;

        //渠道
        $channel = controller('crm/Customer','lib')->getChannel($shop_code);

        //花费
        $channelCost = db('s_channel_cost')
            ->where('cost_date', 'between time', [$start, $end])
            ->where('shop_code', $shop_code)
            ->where($whereD)
            ->group('channel_name')
            ->column('sum(cost) as cost','channel_name');
        $sql = '';
        $sql .= "sum(dj_day between '$start' and '$end') as mkz,";//毛客资
        $sql .= "sum(consultation_type_id = ".HS_TYPE." and is_valid=1 and yx_day between '$start' and '$end') as hsValid,";//有效
        $sql .= "sum(consultation_type_id = ".XZ_TYPE." and is_valid=1 and yx_day between '$start' and '$end') as xzValid,";//有效
        $sql .= "sum(is_valid=3 and yx_day between '$start' and '$end') as unValid,";//无效
        $sql .= "sum(consultation_type_id = ".HS_TYPE." and is_valid=1 and yx_day between '$start' and '$end' and (customer_tel is not null and customer_tel <> '')) as hsTel,";//婚纱手机

        $sql .= "sum(is_intostore in (2,3) 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(consultation_type_id = ".HS_TYPE." and is_success=1 and order_day between '$start' and '$end') as hsOrder,";//有效
        $sql .= "sum(consultation_type_id = ".XZ_TYPE." and is_success=1 and order_day between '$start' and '$end') as xzOrder";//有效
        //新单
        $info = db('c_customerinfo')
            ->where($where)
            ->group('customer_channel')
            ->column($sql,'customer_channel');
//        echo db()->getLastSql();die;
        $returnData = [];
        $arr = [];
        foreach($channel as $k=>$v){
            $returnData[$k]['channel_name'] = $v['channel_name'];
            $returnData[$k]['cost'] = $channelCost[$v['channel_name']] ? sprintf('%.2f',$channelCost[$v['channel_name']]) : '';
            $returnData[$k]['mkz'] = $info[$v['channel_name']]['mkz'];
            $returnData[$k]['hsValid'] = $info[$v['channel_name']]['hsValid'];
            $returnData[$k]['xzValid'] = $info[$v['channel_name']]['xzValid'];
            $returnData[$k]['unValid'] = $info[$v['channel_name']]['unValid'];
            $returnData[$k]['hsValidRate'] = $returnData[$k]['hsValid'] ? sprintf('%.2f',$returnData[$k]['hsValid']/$returnData[$k]['mkz']*100).'%' : '0%';//婚纱有效率
            $returnData[$k]['hsTel'] = $info[$v['channel_name']]['hsTel'];//婚纱有效手机数
            $returnData[$k]['hsValidCost'] = $returnData[$k]['hsValid'] ? sprintf('%.2f',$returnData[$k]['cost']/$returnData[$k]['hsValid']) : '';//婚纱有效成本
            $returnData[$k]['hsValidTelCost'] = $returnData[$k]['hsValid'] ? sprintf('%.2f',$returnData[$k]['cost']/$returnData[$k]['hsTel']) : '';//婚纱有效成本
            $returnData[$k]['hsTelRate'] = $returnData[$k]['hsTel'] ? sprintf('%.2f',$returnData[$k]['hsTel']/$returnData[$k]['hsValid']*100).'%' : '0%';//婚纱电话转换率
            $returnData[$k]['jd'] = $info[$v['channel_name']]['jd'];
            $returnData[$k]['hsOrder'] = $info[$v['channel_name']]['hsOrder'];
            $returnData[$k]['xzOrder'] = $info[$v['channel_name']]['xzOrder'];
            $returnData[$k]['orderCost'] = $returnData[$k]['hsOrder'] ? sprintf('%.2f',$returnData[$k]['cost']/$returnData[$k]['hsOrder']) : '';//婚纱订单成本
            $returnData[$k]['jdRate'] = $returnData[$k]['jd'] ? sprintf('%.2f',$returnData[$k]['jd']/$returnData[$k]['hsValid']*100).'%' : '';//婚纱进店率
            $returnData[$k]['hsSuccRate'] = $returnData[$k]['hsOrder'] ? sprintf('%.2f',$returnData[$k]['hsOrder']/$returnData[$k]['jd']*100).'%' : '';//婚纱成交率
            $returnData[$k]['hsZHRate'] = $returnData[$k]['hsOrder'] ? sprintf('%.2f',$returnData[$k]['hsOrder']/$returnData[$k]['hsValid']*100).'%' : '';//婚纱转换率
            //总计
            $arr['channel_name'] = '总计';
            $arr['cost'] += $channelCost[$v['channel_name']];
            $arr['mkz'] += $info[$v['channel_name']]['mkz'];
            $arr['hsValid'] += $info[$v['channel_name']]['hsValid'];
            $arr['xzValid'] += $info[$v['channel_name']]['xzValid'];
            $arr['unValid'] += $info[$v['channel_name']]['unValid'];
            $arr['hsValidRate'] = '';//婚纱有效率
            $arr['hsTel'] += $info[$v['channel_name']]['hsTel'];//婚纱有效手机数
            $arr['hsValidCost'] += $arr['hsValid'] ? sprintf('%.2f',$arr['cost']/$arr['hsValid']) : '';//婚纱有效成本
            $arr['hsValidTelCost'] += $arr['hsValid'] ? sprintf('%.2f',$arr['cost']/$arr['hsTel']) : '';//婚纱有效电话成本
            $arr['hsTelRate'] = '';//婚纱电话转换率
            $arr['jd'] += $info[$v['channel_name']]['jd'];
            $arr['hsOrder'] += $info[$v['channel_name']]['hsOrder'];
            $arr['xzOrder'] += $info[$v['channel_name']]['xzOrder'];
            $arr['orderCost'] += $arr['hsOrder'] ? sprintf('%.2f',$arr['cost']/$arr['hsOrder']) : '';//婚纱订单成本
            $arr['jdRate'] = '';//婚纱进店率
            $arr['hsSuccRate'] = '';//婚纱成交率
            $arr['hsZHRate'] = '';//婚纱转换率
        }
        //未填写渠道的客资信息
        $null = [];
        $null['channel_name'] = '未填写';
        $null['cost'] = '';
        $null['mkz'] = $info['']['mkz'];
        $null['hsValid'] = $info['']['hsValid'];
        $null['xzValid'] = $info['']['xzValid'];
        $null['unValid'] = $info['']['unValid'];
        $null['hsValidRate'] = $null['hsValid'] ? sprintf('%.2f',$null['hsValid']/$null['mkz']*100).'%' : '0%';//婚纱有效率
        $null['hsTel'] = $info['']['hsTel'];//婚纱有效手机数
        $null['hsValidCost'] = $null['hsValid'] ? sprintf('%.2f',$null['cost']/$null['hsValid']) : '';//婚纱有效成本
        $null['hsValidTelCost'] = $null['hsValid'] ? sprintf('%.2f',$null['cost']/$null['hsTel']) : '';//婚纱有效成本
        $null['hsTelRate'] = $null['hsTel'] ? sprintf('%.2f',$null['hsTel']/$null['hsValid']*100).'%' : '0%';//婚纱电话转换率
        $null['jd'] = $info['']['jd'];
        $null['hsOrder'] = $info['']['hsOrder'];
        $null['xzOrder'] = $info['']['xzOrder'];
        $null['orderCost'] = $null['hsOrder'] ? sprintf('%.2f',$null['cost']/$null['hsOrder']*100) : '';//婚纱订单成本
        $null['jdRate'] = $null['jd'] ? sprintf('%.2f',$null['jd']/$null['hsValid']*100).'%' : '';//婚纱进店率
        $null['hsSuccRate'] = $null['hsOrder'] ? sprintf('%.2f',$null['hsOrder']/$null['jd']*100).'%' : '';//婚纱成交率
        $null['hsZHRate'] = $null['hsOrder'] ? sprintf('%.2f',$null['hsOrder']/$null['hsValid']*100).'%' : '';//婚纱转换率
        $returnData[] = $null;

        $arr['mkz'] = $arr['mkz']+$null['mkz'];
        $arr['hsValid'] = $arr['hsValid']+$null['hsValid'];
        $arr['xzValid'] = $arr['xzValid']+$null['xzValid'];
        $arr['unValid'] = $arr['unValid']+$null['unValid'];
        $arr['hsTel'] = $arr['hsTel']+$null['hsTel'];
        $arr['hsValidCost'] = $arr['hsValid'] ? sprintf('%.2f',$arr['cost']/$arr['hsValid']) : '';//婚纱有效成本
        $arr['hsValidTelCost'] = $arr['hsValid'] ? sprintf('%.2f',$arr['cost']/$arr['hsTel']) : '';//婚纱有效电话成本
        $arr['jd'] = $arr['jd']+$null['jd'];
        $arr['hsOrder'] = $arr['hsOrder']+$null['hsOrder'];
        $arr['xzOrder'] = $arr['xzOrder']+$null['xzOrder'];
        $arr['orderCost'] = $arr['hsOrder'] ? sprintf('%.2f',$arr['cost']/$arr['hsOrder']) : '';//婚纱订单成本

        $arr['hsValidRate'] = $arr['hsValid'] ? sprintf('%.2f',$arr['hsValid']/$arr['mkz']*100).'%' : '0%';//婚纱有效率
        $arr['hsTelRate'] = $arr['hsTel'] ? sprintf('%.2f',$arr['hsTel']/$arr['hsValid']*100).'%' : '0%';//婚纱电话转换率
        $arr['jdRate'] = $arr['jd'] ? sprintf('%.2f',$arr['jd']/$arr['hsValid']*100).'%' : '';//婚纱进店率
        $arr['hsSuccRate'] = $arr['hsOrder'] ? sprintf('%.2f',$arr['hsOrder']/$arr['jd']*100).'%' : '';//婚纱成交率
        $arr['hsZHRate'] = $arr['hsOrder'] ? sprintf('%.2f',$arr['hsOrder']/$arr['hsValid']*100).'%' : '';//婚纱转换率
        $returnData[] = $arr;
        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($channel);
        $return['data'] = $returnData;

        // 导出
        if ($data['need'] == 1) {
            $title = array('渠道', '花费', '毛客资', '婚纱有效','写真有效', '无效', '婚纱有效率', '婚纱有效手机数', '有效婚纱成本','有效婚纱手机成本', '婚纱电话转换率', '进店', '婚纱订单', '写真订单', '婚纱订单成本', '进店率', '婚纱成交率', '婚纱转换率');
            $code = $this->getCode();
            $ff = db('s_report_form')->where('code', $code)->value('report_name');
            $file = session('department_name').'-'.$ff.'.xlsx';
            controller("crm/Customer", "lib")->export($title, $returnData, $file);
        } else {
            return json($return);
        }
    }

    public function show(){
        $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();

        $code = $this->getCode();
        $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');
        $shopInfo = controller('crm/Customer','lib')->get_shop(['shop_code'=>$info['shop_code']]);
        $this->assign('title', $shopInfo[0]['shop_name'].'-'.$title);
        //店下部门
        $department = controller('crm/Setting','lib')->department_list(['shop_code'=>$info['shop_code']]);
        $this->assign('department', $department);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}