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

class Brand_channel_data extends Report {

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

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

        if($data['brandclass_id']){
            $whereT['c.consultation_type_id'] = intval($data['consultation_type_id']);
        }

        $channels = db('s_channel')->where('brandclass_id', $bid)->group('channel_name')->select();

        // 花费
        $channelCost = db('s_channel_cost')->alias('c')
            ->where('c.cost_date', 'between time', [$start, $end])
            ->where('c.brandclass_id', $bid)
            ->group('c.channel_name')
            ->column('sum(c.cost) as cost','c.channel_name');

        $sql = '';
        $sql .= 'sum(yx_day between "'.$start.'" and "'.$end.'" and is_valid=1) as valid,';
        $sql .= 'sum(order_day between "'.$start.'" and "'.$end.'" and is_success=1) as `order`';
        $info = db('c_customerinfo')
            ->where($where)
            ->where($whereT)
            ->group('customer_channel')
            ->column($sql, 'customer_channel');

        $whereC['c.is_status'] = 1;
        $whereC['c.c_from'] = 1;
        $whereC['c.customer_type'] = 1;
        $whereC['c.brandclass_id'] = $bid;
        // 退单
        $refund = db('c_customerinfo')->alias('c')->join('c_customer_order o', 'o.customer_id=c.id')
            ->where($whereC)
            ->where('o.unvalid_time','between time', [$start, $end])
            ->group('c.customer_channel')
            ->column('count(c.id) as num', 'c.customer_channel');

        // 退单套系
        $refundAmount = db('c_customerinfo')->alias('c')->join('c_customer_order o','o.customer_id=c.id')
            ->where($whereC)
            ->where('c.is_success', 1)
            ->where('o.unvalid_time','not null')
            ->where('o.unvalid_time', 'between time', [$start, $end])
            ->group('c.customer_channel')
            ->column('sum(o.order_amount) as amount', 'c.customer_channel');
        // 退单实收
        $refundMoney = db('c_customerinfo')->alias('c')->join('c_customer_order o','o.customer_id=c.id')->join('c_order_detail d','d.order_sn=o.order_number')
            ->where($whereC)
            ->where('d.pay_type',1)
            ->where('o.unvalid_time','not null')
            ->where('o.unvalid_time', 'between time', [$start, $end])
            ->group('c.customer_channel')
            ->column('sum(d.order_fact) as fact_money', 'c.customer_channel');
        // 净订单套系
        $clearAmount = db('c_customerinfo')->alias('c')->join('c_customer_order o','o.customer_id=c.id')
            ->where($whereC)
            ->where('c.is_success', 1)
            ->where('o.unvalid_time','not null')
            ->where('c.order_day', 'between time', [$start, $end])
            ->group('c.customer_channel')
            ->column('sum(o.order_amount) as amount', 'c.customer_channel');
        // 净订单实收
        $clearMoney = db('c_customerinfo')->alias('c')->join('c_customer_order o','o.customer_id=c.id')->join('c_order_detail d','d.order_sn=o.order_number')
            ->where($whereC)
            ->where('d.pay_type',1)
            ->where('o.unvalid_time','not null')
            ->where('d.pay_time', 'between time', [$start, $end])
            ->group('c.customer_channel')
            ->column('sum(d.order_fact) as fact_money', 'c.customer_channel');
        $returnData = [];
        $arr = [];
        $arr['id'] = '';
        $arr['customer_channel'] = '总计';
        foreach ($channels as $k => $v) {
            $returnData[$k]['id'] = $v['id'];
            $returnData[$k]['channel_name'] = $v['channel_name'];

            $returnData[$k]['channelCost'] = $channelCost[$v['channel_name']] ? sprintf('%.2f',$channelCost[$v['channel_name']]) : 0;
            $returnData[$k]['valid'] = $info[$v['channel_name']]['valid'] ? $info[$v['channel_name']]['valid'] : 0;
            $returnData[$k]['order'] = $info[$v['channel_name']]['order'] ? $info[$v['channel_name']]['order'] : 0;
            $returnData[$k]['validCost'] = $channelCost[$v['channel_name']] ? sprintf('%.2f',$channelCost[$v['channel_name']]/$info[$v['channel_name']]['valid']) : '';
            $returnData[$k]['orderCost'] = $channelCost[$v['channel_name']] ? sprintf('%.2f',$channelCost[$v['channel_name']]/$info[$v['channel_name']]['order']) : '';
            //退单
            $returnData[$k]['refund'] = $refund[$v['channel_name']] ? $refund[$v['channel_name']] : 0;
            //退单套系
            $returnData[$k]['refundAmount'] = $refundAmount[$v['channel_name']] ? $refundAmount[$v['channel_name']] : 0;
            //退单实收
            $returnData[$k]['refundMoney'] = $refundMoney[$v['channel_name']] ? $refundMoney[$v['channel_name']] : 0;
            //净订单
            $returnData[$k]['clearOrder'] = $refund[$v['channel_name']] ? $info[$v['channel_name']]['order']-$refund[$v['channel_name']] : 0;
            //净转换率
            $returnData[$k]['clearOrderRate'] = $refund[$v['channel_name']] ? sprintf('%.2f',$returnData[$k]['clearOrder']/$info[$v['channel_name']]['order']*100).'%' : '0%';
            // 净订单套系
            $returnData[$k]['clearOrderAmount'] = $clearAmount[$v['channel_name']] ? $clearAmount[$v['channel_name']] : 0;
            // 净订单实收
            $returnData[$k]['clearOrderMoney'] = $clearMoney[$v['channel_name']] ? $clearMoney[$v['channel_name']] : 0;
            // 净订单成本
            $returnData[$k]['clearOrderCost'] = $channelCost[$v['channel_name']] ? sprintf('%.2f',$channelCost[$v['channel_name']]['order']/$returnData[$k]['clearOrder']) : '';


            $arr['channelCost'] += $channelCost[$v['channel_name']];   // 花费总计
            $arr['valid'] += $returnData[$k]['valid'];   // 有效总计
            $arr['order'] += $returnData[$k]['order'];   // 新单
            $arr['validCost'] = '';
            $arr['orderCost'] = '';
            $arr['refund']      += $returnData[$k]['refund'];        // 退单
            $arr['refundAmount']     += $returnData[$k]['refundAmount'];       // 退单套系
            $arr['refundMoney']     += $returnData[$k]['refundMoney'];       // 退单实收
            $arr['clearOrder']    +=  $returnData[$k]['clearOrder'];      // 净订单总计
            $arr['clearOrderRate'] = '';
            $arr['clearOrderAmount']   += $returnData[$k]['clearOrderAmount']; // 净订单套系
            $arr['clearOrderMoney']   += $returnData[$k]['clearOrderMoney']; // 净订单套系
            $arr['clearOrderCost']   = ''; // 净订单成本
        }
        //渠道为空的情况
        $null['id'] = '';
        $null['channel_name']  = '渠道未填写';
        $null['channelCost'] = '';
        $whereN['is_status'] = 1;
        $whereN['c_from'] = 1;
        $whereN['customer_type'] = 1;
        $whereN['brandclass_id'] = $bid;
        $null['valid'] = db('c_customerinfo')
            ->where($whereN)
            ->where($whereT)
            ->where('is_valid',1)
            ->where('yx_day','between',[$start,$end])
            ->where('customer_channel is null or customer_channel=""')
            ->count();
        $null['order'] = db('c_customerinfo')
            ->where($whereN)
            ->where($whereT)
            ->where('is_success',1)
            ->where('order_day','between',[$start,$end])
            ->where('customer_channel is null or customer_channel=""')
            ->count();
        $null['validCost'] = '';
        $null['orderCost'] = '';
        $whereNC['c.is_status'] = 1;
        $whereNC['c.c_from'] = 1;
        $whereNC['c.brandclass_id'] = 1;
        // 退单
        $null['refund'] = db('c_customerinfo')->alias('c')->join('c_customer_order o', 'o.customer_id=c.id')
            ->where($whereNC)
            ->where('o.unvalid_time','between time', [$start, $end])
            ->where('c.customer_channel is null or c.customer_channel=""')
            ->count();

        // 退单套系
        $null['refundAmount'] = db('c_customerinfo')->alias('c')->join('c_customer_order o','o.customer_id=c.id')
            ->where($whereNC)
            ->where('c.is_success', 1)
            ->where('o.unvalid_time','not null')
            ->where('o.unvalid_time', 'between time', [$start, $end])
            ->where('c.customer_channel is null or c.customer_channel=""')
            ->sum('o.order_amount');
        // 退单实收
        $null['refundMoney'] = db('c_customerinfo')->alias('c')->join('c_customer_order o','o.customer_id=c.id')->join('c_order_detail d','d.order_sn=o.order_number')
            ->where($whereNC)
            ->where('d.pay_type',1)
            ->where('o.unvalid_time','not null')
            ->where('o.unvalid_time', 'between time', [$start, $end])
            ->where('c.customer_channel is null or c.customer_channel=""')
            ->sum('d.order_fact');
        //净订单
        $null['clearOrder'] = $null['order']-$null['refund'];
        //净转换率
        $null['clearOrderRate'] = $null['clearOrder'] ? sprintf('%.2f',$null['clearOrder']/$null['order']*100).'%' : '0%';
        // 净订单套系
        $null['clearAmount'] = db('c_customerinfo')->alias('c')->join('c_customer_order o','o.customer_id=c.id')
            ->where($whereNC)
            ->where('c.is_success', 1)
            ->where('o.unvalid_time','not null')
            ->where('c.order_day', 'between time', [$start, $end])
            ->where('c.customer_channel is null or c.customer_channel=""')
            ->sum('o.order_amount');
        // 净订单实收
        $null['clearMoney'] = db('c_customerinfo')->alias('c')->join('c_customer_order o','o.customer_id=c.id')->join('c_order_detail d','d.order_sn=o.order_number')
            ->where($whereNC)
            ->where('d.pay_type',1)
            ->where('o.unvalid_time','not null')
            ->where('d.pay_time', 'between time', [$start, $end])
            ->where('c.customer_channel is null or c.customer_channel=""')
            ->sum('d.order_fact');

        $null['clearOrderCost'] = '';

        array_push($returnData,$null);
        //加上渠道为空的数量
        $arr['channelCost']  = $arr['channelCost']+0;
        $arr['valid'] = $arr['valid']+$null['valid'];
        $arr['order'] = $arr['order']+$null['order'];
        $arr['validCost']      = $arr['channelCost'] ? sprintf('%.2f',$arr['channelCost']/$arr['valid']) : '';
        $arr['orderCost']      = $arr['channelCost'] ? sprintf('%.2f',$arr['channelCost']/$arr['order']) : '';
        $arr['refund'] += $arr['refund']+$null['refund'];
        $arr['refundAmount'] += $arr['refundAmount']+$null['refundAmount'];
        $arr['refundMoney'] += $arr['refundMoney']+$null['refundMoney'];
        $arr['clearOrder'] += $arr['clearOrder']+$null['clearOrder'];
        $arr['clearOrderRate'] = $arr['clearOrder'] ? sprintf('%.2f',$arr['clearOrder']/$arr['order']*100).'%' : '0%';
        $arr['clearOrderAmount'] += $arr['clearOrderAmount']+$null['clearOrderAmount'];
        $arr['clearOrderMoney'] += $arr['clearOrderMoney']+$null['clearOrderMoney'];
        $arr['clearOrderRate'] = $arr['clearOrder'] ? sprintf('%.2f',$arr['channelCost']/$arr['clearOrder']) : '';
        array_push($returnData,$arr);


        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($channels);
        $return['data'] = $returnData;
        // 导出
        if ($data['need'] == 1) {
            $title = array('序号', '渠道', '花费', '有效客资', '新单', '有效获取成本', '新单成本', '退单', '退单金额', '退实收', '净订单', '净转换率', '净套系金额', '净实收', '净订单成本');
            $code = $this->getCode();
            $ff = db('s_report_form')->where('code', $code)->value('report_name');
            $shop = controller('crm/Customer','lib')->getBrands(['brandclass_id'=>$bid]);
            $file = $shop[0]['brandclass'].'-'.$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();
        $shop= controller('crm/Customer','lib')->getBrands(['brandclass_id'=>$info['brandclass_id']]);
        //消费类型
        $consumptionType = controller("Customer", "lib")->consumptionType(['brandclass_id'=>$info['brandclass_id']]);
        $this->assign("consumptionType", $consumptionType);
        $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');
        $this->assign('title', $shop[0]['brandclass'].'-'.$title);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}