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

class Jw_ds_channel_day extends Report {

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

    public function getLevel(){
        return $this->level;
    }
    public function getType(){
        return $this->type;
    }
    public function getCode(){
        return $this->code;
    }
    public function data(){
        $param = input();
        $det = db('s_report_form')->alias('f')->join('s_report_detail d','d.form_id=f.id')->where('d.id',$param['did'])->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name')->find();
        $shopCode = $det['shop_code'];
        if(empty($shopCode)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }
        $channels = db('s_channel')->where('shop_code', $shopCode)->select();
        $channel_name = db('s_channel')->where('id', $param['channel_id'])->value('channel_name');

        $start = date('Y-m-d', strtotime($param['start']));
        $end = date('Y-m-d', strtotime($param['end']));
        $time = getWeekByTime($start,$end);
        $where['add_shop_code'] = $shopCode;
        $where['is_status'] = 1;
        $where['c_from'] = 1;
        $where['customer_type'] = 1;
        $where['customer_channel'] = $channel_name;

        $returnData = [];
        $total = [];
        $i = 0;
        foreach($time as $k=>$v) {
            $arr = [];
            foreach ($v as $key => $val){
                $starttime = date('Y-m-d 00:00:00', strtotime($val));
                $endtime = date('Y-m-d 23:59:59', strtotime($val));
            //花费
            $cost = db('s_channel_cost')
                ->where('channel_id', $param['channel_id'])
                ->where('cost_date', 'between time', [reset($v), end($v)])
                ->group('cost_date')
                ->column('sum(cost) as cost,cost_date', 'cost_date');
            //毛客资
            $mkz = db('c_customerinfo')
                ->where($where)
                ->where('dj_day', 'between', [reset($v), end($v) . ' 23:59:59'])
                ->group('days')
                ->column('DATE_FORMAT(dj_day,"%Y-%m-%d") days,count(id) as num');
            //有效客资
            $valid = db('c_customerinfo')
                ->where($where)
                ->where('is_valid', 1)
                ->where('yx_day', 'between', [reset($v), end($v) . ' 23:59:59'])
                ->group('days')
                ->column('DATE_FORMAT(yx_day,"%Y-%m-%d") days,count(id) as num');
            //进店
            $jd = db('c_customerinfo')
                ->where($where)
                ->where('is_intostore', 'in', '2,3')
                ->where('syjd_day', 'between', [reset($v), end($v) . ' 23:59:59'])
                ->group('days')
                ->column('DATE_FORMAT(syjd_day,"%Y-%m-%d") days,count(id) as num');
            //订单
            $order = db('c_customerinfo')
                ->where($where)
                ->where('is_success', 1)
                ->where('order_day', 'between', [reset($v), end($v) . ' 23:59:59'])
                ->group('days')
                ->column('DATE_FORMAT(order_day,"%Y-%m-%d") days,count(id) as num');

            $returnData[$i]['time'] = $val;
            $returnData[$i]['cost'] = $cost[$val] ? sprintf('%.2f',$cost[$val]) : '';
            $returnData[$i]['mkz'] = $mkz[$val] ? $mkz[$val] : '';
            $returnData[$i]['valid'] = $valid[$val] ? $valid[$val] : '';
            $returnData[$i]['validRate'] = $valid[$val] ? sprintf('%.2f',$valid[$val]/$mkz[$val]*100).'%' : '0%';
            $returnData[$i]['jd'] = $jd[$val] ? $jd[$val] : '';
            $returnData[$i]['order'] = $order[$val] ? $order[$val] : '';
            $returnData[$i]['jdRate'] = $jd[$val] ? sprintf('%.2f',$jd[$val]/$valid[$val]*100).'%' : '0%';
            $returnData[$i]['orderRate'] = $order[$val] ? sprintf('%.2f',$order[$val]/$jd[$val]*100).'%' : '0%';
            $returnData[$i]['orderObtain'] = $order[$val] ? sprintf('%.2f',$cost[$val]/$order[$val]) : '';
                $arr['time'] = '周总';
                $arr['cost'] += $cost[$val];
                $arr['mkz'] += $mkz[$val];
                $arr['valid'] += $valid[$val];
                $arr['validRate'] = '';
                $arr['jd'] += $jd[$val];
                $arr['order'] += $order[$val];
                $arr['jdRate'] = '';
                $arr['orderRate'] = '';
                $arr['orderObtain'] = '';

                $total['time'] = '总计';
                $total['cost'] += $cost[$val];
                $total['mkz'] += $mkz[$val];
                $total['valid'] += $valid[$val];
                $total['validRate'] = '';
                $total['jd'] += $jd[$val];
                $total['order'] += $order[$val];
                $total['jdRate'] = '';
                $total['orderRate'] = '';
                $total['orderObtain'] = '';
            $i++;
            }
            $arr['validRate'] = $arr['valid'] ? sprintf('%.2f',$arr['valid']/$arr['mkz']*100).'%' : '0%';
            $arr['jdRate'] = $arr['jd'] ? sprintf('%.2f',$arr['jd']/$arr['valid']*100).'%' : '0%';
            $arr['orderRate'] = $arr['order'] ? sprintf('%.2f',$arr['order']/$arr['jd']*100).'%' : '0%';
            $arr['orderObtain'] = $arr['order'] ? sprintf('%.2f',$arr['cost']/$arr['order']) : '';
            $returnData[$i] = $arr;
            $i++;
        }
        $total['validRate'] = $total['valid'] ? sprintf('%.2f',$total['valid']/$total['mkz']*100).'%' : '0%';
        $total['jdRate'] = $total['jd'] ? sprintf('%.2f',$total['jd']/$total['valid']*100).'%' : '0%';
        $total['orderRate'] = $total['order'] ? sprintf('%.2f',$total['order']/$total['jd']*100).'%' : '0%';
        $total['orderObtain'] = $total['order'] ? sprintf('%.2f',$total['cost']/$total['order']) : '';
        $returnData[$i] = $total;

        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($channels);
        $return['data'] = $returnData;
        // 导出
        if ($param['need'] == 1) {
            $title = array('序号', '花费', '毛客资', '有效客获取', '有效率', '进店', '订单', '进店率', '成交率', '订单成本');
            $code = $this->getCode();
            $ff = db('s_report_form')->where('code', $code)->value('report_name');
            $shop = controller('crm/Customer','lib')->get_shop(['shop_code'=>$shopCode]);
            $file = $shop[0]['shop_name'].'-'. $channel_name.'-'.$ff.'.xlsx';
            controller("crm/Customer", "lib")->export($title, $returnData, $file);
        } else {
            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();

        //来源渠道
        $channel = controller("Customer", "lib")->getChannel($info['shop_code']);
        $this->assign("channel", $channel);

        $shopInfo = controller('crm/Customer','lib')->get_shop(['shop_code'=>$info['shop_code']]);
        $title = db('s_report_form')->where('code', $code)->value('report_name');
        $this->assign('title', $shopInfo[0]['shop_name'].'-'.$title);

        $this->assign('shop_code', $info['shop_code']);
        $display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.strtolower($info['report_class_name']).'.html';
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}