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

class Qqbaby_ds_data extends Report {

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

    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();
        $det = db('crm_s_report_form')->alias('f')->join('crm_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,f.report_name')->find();
        $shop_code = $det['shop_code'];
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }
        $start = $data['start'] ? date('Y-m-d', strtotime($data['start'])) : date('Y-m-d');
        $end =  $data['end'] ? date('Y-m-d 23:59:59', strtotime($data['end'])) : date('Y-m-d 23:59:59');
        
        $where['is_status'] = 1;
        $where['add_shop_code'] = $shop_code;
        $where['dj_department_id'] = 43;
        //推广部 孕妇照
        $tui_cost = db('crm_s_channel_cost')->where('cost_date','between',[$start,$end])->where('department_id',43)->sum('cost');//推广所有花费
        $citys = db('crm_s_channel_cost')->where('cost_date','between',[$start,$end])->where('department_id',43)->column('city_id');//推广所有花费的投放城市

        //孕妇照 花费  先查推广部下消费类型客资的渠道，在查本段时间里这些渠道的花费
        $tui_yf_channel = db('crm_c_customerinfo')->where($where)->where('consultation_type_id',YF_TYPE)->where('city_id','in',$citys)->where('dj_day','between',[$start,$end])->group('customer_channel')->column('customer_channel');//孕妇渠道
        $tui_cost_yf = db('crm_s_channel_cost')->where('cost_date','between',[$start,$end])->where('department_id',43)->where('city_id','in',$citys)->where('channel_name','in',$tui_yf_channel)->sum('cost');//孕妇花费
        //宝宝照花费
        $tui_cost_bb = $tui_cost-$tui_cost_yf;

        //毛客资
        $tui_mkz = db('crm_c_customerinfo')->where($where)->where('city_id','in',$citys)->where('dj_day','between',[$start,$end])->count('id');
        //孕妇照
        $tui_mkz_yf = db('crm_c_customerinfo')->where($where)->where('city_id','in',$citys)->where('consultation_type_id',YF_TYPE)->where('dj_day','between',[$start,$end])->count('id');

        //宝宝照
        $tui_mkz_bb = $tui_mkz-$tui_mkz_yf;

        //有效客资
        $where['is_valid'] = 1;
        $tui_valid = db('crm_c_customerinfo')->where($where)->where('city_id','in',$citys)->where('dj_day','between',[$start,$end])->count('id');
        //孕妇照
        $tui_valid_yf = db('crm_c_customerinfo')->where($where)->where('city_id','in',$citys)->where('consultation_type_id',YF_TYPE)->where('dj_day','between',[$start,$end])->count('id');
        //宝宝照
        $tui_valid_bb = $tui_valid-$tui_valid_yf;

        //业绩  实收
        unset($where['is_valid']);
        $tui_fact = db('crm_c_customerinfo c')->join('crm_c_order_detail d','c.order_number=d.order_sn')->where('city_id','in',$citys)->where($where)->where('d.pay_type',1)->where('d.pay_time','between',[$start,$end])->sum('d.order_fact');
        //孕妇照
        $tui_fact_yf = db('crm_c_customerinfo c')->join('crm_c_order_detail d','c.order_number=d.order_sn')->where('city_id','in',$citys)->where('consultation_type_id',YF_TYPE)->where($where)->where('d.pay_type',1)->where('d.pay_time','between',[$start,$end])->sum('d.order_fact');
        //宝宝照
        $tui_fact_bb = $tui_fact-$tui_fact_yf;

        //获取成本 实际花费/毛客资量
        $tui_obtain = $tui_cost/$tui_mkz ? sprintf('%.2f',$tui_cost/$tui_mkz) : '';
        $tui_obtain_yf = $tui_cost_yf/$tui_mkz_yf ? sprintf('%.2f',$tui_cost_yf/$tui_mkz_yf) : '';
        $tui_obtain_bb = $tui_cost_bb/$tui_mkz_bb ? sprintf('%.2f',$tui_cost_bb/$tui_mkz_bb) : '';
        
        //有效率 
        $tui_validRate = sprintf('%.2f',$tui_valid/$tui_mkz*100).'%';
        $tui_validRate_yf = sprintf('%.2f',$tui_valid_yf/$tui_mkz_yf*100).'%';
        $tui_validRate_bb = sprintf('%.2f',$tui_valid_bb/$tui_mkz_bb*100).'%';

        //投入产出比 实际花费/毛客资量
        $tui_tcRate = sprintf('%.2f',$tui_fact/$tui_cost*100).'%';
        $tui_tcRate_yf = sprintf('%.2f',$tui_fact_yf/$tui_cost_yf*100).'%';
        $tui_tcRate_bb = sprintf('%.2f',$tui_fact_bb/$tui_cost_bb*100).'%';

        //新媒体部--------------------------------------------------
        $where['dj_department_id'] = 44;
        //推广部 孕妇照
        $xin_cost = db('crm_s_channel_cost')->where('cost_date','between',[$start,$end])->where('department_id',44)->sum('cost');
        $city = db('crm_s_channel_cost')->where('cost_date','between',[$start,$end])->where('department_id',44)->column('city_id');//推广所有花费的投放城市
        //孕妇照 花费  先查推广部下消费类型客资的渠道，在查本段时间里这些渠道的花费
        $xin_yf_channel = db('crm_c_customerinfo')->where($where)->where('city_id','in',$city)->where('consultation_type_id',YF_TYPE)->where('dj_day','between',[$start,$end])->group('customer_channel')->column('customer_channel');
        $xin_cost_yf = db('crm_s_channel_cost')->where('cost_date','between',[$start,$end])->where('city_id','in',$city)->where('department_id',44)->where('channel_name','in',$xin_yf_channel)->sum('cost');
        //宝宝照花费
        $xin_cost_bb = $xin_cost-$xin_cost_yf;

        //毛客资
        $xin_mkz = db('crm_c_customerinfo')->where($where)->where('city_id','in',$city)->where('dj_day','between',[$start,$end])->count('id');
        //孕妇照
        $xin_mkz_yf = db('crm_c_customerinfo')->where($where)->where('city_id','in',$city)->where('consultation_type_id',YF_TYPE)->where('dj_day','between',[$start,$end])->count('id');
        //宝宝照
        $xin_mkz_bb = $xin_mkz-$xin_mkz_yf;

        //有效客资
        $where['is_valid'] = 1;
        $xin_valid = db('crm_c_customerinfo')->where($where)->where('city_id','in',$city)->where('dj_day','between',[$start,$end])->count('id');
        //孕妇照
        $xin_valid_yf = db('crm_c_customerinfo')->where($where)->where('city_id','in',$city)->where('consultation_type_id',YF_TYPE)->where('dj_day','between',[$start,$end])->count('id');
        //宝宝照
        $xin_valid_bb = $xin_valid-$xin_valid_yf;

        //业绩  实收
        unset($where['is_valid']);
        $xin_fact = db('crm_c_customerinfo c')->join('crm_c_order_detail d','c.order_number=d.order_sn')->where('city_id','in',$city)->where($where)->where('d.pay_type',1)->where('d.pay_time','between',[$start,$end])->sum('d.order_fact');
        //孕妇照
        $xin_fact_yf = db('crm_c_customerinfo c')->join('crm_c_order_detail d','c.order_number=d.order_sn')->where('city_id','in',$city)->where('consultation_type_id',YF_TYPE)->where($where)->where('d.pay_type',1)->where('d.pay_time','between',[$start,$end])->sum('d.order_fact');
        //宝宝照
        $xin_fact_bb = $xin_fact-$xin_fact_yf;

        //获取成本 实际花费/毛客资量
        $xin_obtain = $xin_cost/$xin_mkz>0 ? sprintf('%.2f',$xin_cost/$xin_mkz) : '';
        $xin_obtain_yf = $xin_cost_yf/$xin_mkz_yf> 0 ? sprintf('%.2f',$xin_cost_yf/$xin_mkz_yf) : '';
        $xin_obtain_bb = $xin_cost_bb/$xin_mkz_bb>0 ? sprintf('%.2f',$xin_cost_bb/$xin_mkz_bb) : '';
        
        //有效率 
        $xin_validRate = sprintf('%.2f',$xin_valid/$xin_mkz*100).'%';
        $xin_validRate_yf = sprintf('%.2f',$xin_valid_yf/$xin_mkz_yf*100).'%';
        $xin_validRate_bb = sprintf('%.2f',$xin_valid_bb/$xin_mkz_bb*100).'%';

        //投入产出比 实际花费/毛客资量
        $xin_tcRate = sprintf('%.2f',$xin_fact/$xin_cost*100).'%';
        $xin_tcRate_yf = sprintf('%.2f',$xin_fact_yf/$xin_cost_yf*100).'%';
        $xin_tcRate_bb = sprintf('%.2f',$xin_fact_bb/$xin_cost_bb*100).'%';

        //总计
        $total_cost = $tui_cost+$xin_cost;
        $total_cost_yf = $tui_cost_yf+$xin_cost_yf;
        $total_cost_bb = $tui_cost_bb+$xin_cost_bb;

        $total_mkz = $tui_mkz+$xin_mkz;
        $total_mkz_yf = $tui_mkz_yf+$xin_mkz_yf;
        $total_mkz_bb = $tui_mkz_bb+$xin_mkz_bb;

        $total_valid = $tui_valid+$xin_valid;
        $total_valid_yf = $tui_valid_yf+$xin_valid_yf;
        $total_valid_bb = $tui_valid_bb+$xin_valid_bb;

        $total_fact = $tui_fact+$xin_fact;
        $total_fact_yf = $tui_fact_yf+$xin_fact_yf;
        $total_fact_bb = $tui_fact_bb+$xin_fact_bb;

         //获取成本 实际花费/毛客资量
        $total_obtain = $total_cost/$total_mkz>0 ? sprintf('%.2f',$total_cost/$total_mkz) : '';
        $total_obtain_yf = $total_cost_yf/$total_mkz_yf>0 ? sprintf('%.2f',$total_cost_yf/$total_mkz_yf) : '';
        $total_obtain_bb = $total_cost_bb/$total_mkz_bb>0 ? sprintf('%.2f',$total_cost_bb/$total_mkz_bb) : '';
        
        //有效率 
        $total_validRate = sprintf('%.2f',$total_valid/$total_mkz*100).'%';
        $total_validRate_yf = sprintf('%.2f',$total_valid_yf/$total_mkz_yf*100).'%';
        $total_validRate_bb = sprintf('%.2f',$total_valid_bb/$total_mkz_bb*100).'%';

        //投入产出比 实际花费/毛客资量
        $total_tcRate = sprintf('%.2f',$total_fact/$total_cost*100).'%';
        $total_tcRate_yf = sprintf('%.2f',$total_fact_yf/$total_cost_yf*100).'%';
        $total_tcRate_bb = sprintf('%.2f',$total_fact_bb/$total_cost_bb*100).'%';

        $arr = [];
        $arr[0]['cost'] = ['实际花费(元)',$tui_cost,$tui_cost_bb,$tui_cost_yf];
        $arr[0]['mkz'] = ['毛客资(个)',$tui_mkz,$tui_mkz_bb,$tui_mkz_yf];
        $arr[0]['valid'] = ['有效客资(个)',$tui_valid,$tui_valid_bb,$tui_valid_yf];
        $arr[0]['fact'] = ['业绩(元)',$tui_fact,$tui_fact_bb,$tui_fact_yf];
        $arr[0]['obtain'] = ['获客成本(元/个)',$tui_obtain,$tui_obtain_bb,$tui_obtain_yf];
        $arr[0]['validRate'] = ['有效率',$tui_validRate,$tui_validRate_bb,$tui_validRate_yf];
        $arr[0]['tc'] = ['投入产出比',$tui_tcRate,$tui_tcRate_bb,$tui_tcRate_yf];


        $arr[1]['cost'] = ['实际花费(元)',$xin_cost,$xin_cost_bb,$xin_cost_yf];
        $arr[1]['mkz'] = ['毛客资(个)',$xin_mkz,$xin_mkz_bb,$xin_mkz_yf];
        $arr[1]['valid'] = ['有效客资(个)',$xin_valid,$xin_valid_bb,$xin_valid_yf];
        $arr[1]['fact'] = ['业绩(元)',$xin_fact,$xin_fact_bb,$xin_fact_yf];
        $arr[1]['obtain'] = ['获客成本(元/个)',$xin_obtain,$xin_obtain_bb,$xin_obtain_yf];
        $arr[1]['validRate'] = ['有效率',$xin_validRate,$xin_validRate_bb,$xin_validRate_yf];
        $arr[1]['tc'] = ['投入产出比',$xin_tcRate,$xin_tcRate_bb,$xin_tcRate_yf];


        $arr[2]['cost'] = ['实际花费(元)',$total_cost,$total_cost_bb,$total_cost_yf];
        $arr[2]['mkz'] = ['毛客资(个)',$total_mkz,$total_mkz_bb,$total_mkz_yf];
        $arr[2]['valid'] = ['有效客资(个)',$total_valid,$total_valid_bb,$total_valid_yf];
        $arr[2]['fact'] = ['业绩(元)',$total_fact,$total_fact_bb,$total_fact_yf];
        $arr[2]['obtain'] = ['获客成本(元/个)',$total_obtain,$total_obtain_bb,$total_obtain_yf];
        $arr[2]['validRate'] = ['有效率',$total_validRate,$total_validRate_bb,$total_validRate_yf];
        $arr[2]['tc'] = ['投入产出比',$total_tcRate,$total_tcRate_bb,$total_tcRate_yf];

        return json(array('code' =>200,'shop'=>'','data' => $arr));

    }

    public function show(){
        $code = $this->getCode();
        $did = strtolower(input('param.did'));
        $info = db('crm_s_report_form')->alias('f')->join('crm_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();

        $display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.strtolower($info['report_class_name']).'.html';
        $title = db('crm_s_report_form')->where('code', $code)->value('report_name');
        $shop= controller('crm/Customer','lib')->get_shop(['shop_code'=>$info['shop_code']]);

        $this->assign('shop', $shop);
        $this->assign('title', $shop[0]['shop_name'].'-'.$title);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}