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

class Cszm_ay_month_data extends Report {

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

    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();
        $returnData = [];
        
        $start = date('Y-m-d', strtotime($data['start']));
        $end = date('Y-m-d', strtotime($data['end']));
        // 日期数组
        $dateArea = getDateFromRange($start,$end);
        foreach ($dateArea as $k => $v) {
            $returnData[$v]['date'] = $v;
        }
        
        //可操作店
        if($data['shop_code']){
            $shop_code = $data['shop_code'];
        }else{
            $shop_code = session('shop_code');
        }
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'请选择店面');
        }
        //------------------------ 金额部分 开始 ------------------------//
            // 实收现金
            $ssxjArr = db('c_paymentcontent')
                ->where('paytype','<>','VIP卡')->where('paytype','<>','代金券')->where('shop_code',$shop_code)
                ->where('paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
                ->field('paymentdate,payment_money,suoshu,isshishou')
                ->select();
            $return['ssxjSql'] = db()->getLastSql();
            foreach ($ssxjArr as $k => $v) {
                $returnData[date('Y-m-d',strtotime($v['paymentdate']))]['ssxj'] += $v['payment_money'];
            }
            // 卡销金额
            $kxjeArr = db('c_paymentcontent')
                ->where('paytype','VIP卡')->where('shop_code',$shop_code)
                ->where('paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
                ->field('paymentdate,payment_money,suoshu,isshishou')
                ->select();
            $return['kxjeSql'] = db()->getLastSql();
            foreach ($kxjeArr as $k => $v) {
                $returnData[date('Y-m-d',strtotime($v['paymentdate']))]['kxje'] += $v['payment_money'];
            }
            // 代金券
            $djqArr = db('c_paymentcontent')
                ->where('paytype','代金券')->where('shop_code',$shop_code)
                ->where('paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
                ->field('paymentdate,payment_money,suoshu,isshishou')
                ->select();
            $return['djqSql'] = db()->getLastSql();
            foreach ($djqArr as $k => $v) {
                $returnData[date('Y-m-d',strtotime($v['paymentdate']))]['djq'] += $v['payment_money'];
            }
            // 营收业绩
            $ysyjArr = db('c_paymentcontent')
                // ->where('isshishou','实收')
                ->where('shop_code',$shop_code)
                ->where('paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
                ->field('paymentdate,payment_money,suoshu,isshishou')
                ->select();
            $return['ysyjSql'] = db()->getLastSql();
            foreach ($ysyjArr as $k => $v) {
                $returnData[date('Y-m-d',strtotime($v['paymentdate']))]['ysyj'] += $v['payment_money'];
            }
        //------------------------ 金额部分 开始 ------------------------//

        //------------------------ 套系订单 开始 ------------------------//
            $txArr = db('c_customerorder')->alias('o')
                ->join('c_customerorder_servicefc fc','o.orderId=fc.orderId','left')
                ->where('fc.currentfc','1')
                ->where('o.blankoutannal','<>','1')
                ->where('o.total_money','>',0)
                ->where('o.shop_code',$shop_code)
                ->where('o.targetdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
                ->field('o.orderId,o.targetdate,o.total_money,o.payment_money,o.nopayment_money,o.bargain_money,o.supplementary_money,fc.allfc')
                ->select();
            $return['txSql'] = db()->getLastSql();
            foreach ($txArr as $k => $v) {
                // 套系订单（包含成长+单次）订单金额 及 单量
                $returnData[date('Y-m-d',strtotime($v['targetdate']))]['tx_ddje']+= $v['total_money'];
                $returnData[date('Y-m-d',strtotime($v['targetdate']))]['tx_ddl'] += 1;
                // 成长套系订单 订单金额 及 单量
                if ($v['allfc']>1) {
                    $returnData[date('Y-m-d',strtotime($v['targetdate']))]['cz_ddje']+= $v['total_money'];
                    $returnData[date('Y-m-d',strtotime($v['targetdate']))]['cz_ddl'] += 1;
                }
                // 单次订单 订单金额 及 单量
                else if ($v['allfc']==1) {
                    $returnData[date('Y-m-d',strtotime($v['targetdate']))]['dc_ddje']+= $v['total_money'];
                    $returnData[date('Y-m-d',strtotime($v['targetdate']))]['dc_ddl'] += 1;
                }
            }
            // 三个均单值
            foreach ($returnData as $key => $value) {
                $returnData[$key]['tx_jdz'] = $value['tx_ddl'] ? sprintf('%.2f', $value['tx_ddje'] / $value['tx_ddl']) : '';
                $returnData[$key]['cz_jdz'] = $value['cz_ddl'] ? sprintf('%.2f', $value['cz_ddje'] / $value['cz_ddl']) : '';
                $returnData[$key]['dc_jdz'] = $value['dc_ddl'] ? sprintf('%.2f', $value['dc_ddje'] / $value['dc_ddl']) : '';
            }
            /*
            // 第一次服务
            $dyArr = db('c_customerorder')->alias('o')
                ->join('c_paymentcontent p','o.orderId=p.orderId','left')->where('o.targetdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
                ->where('o.blankoutannal','<>','1')->where('o.shop_code',$shop_code)
                ->where('p.fundname','前期全款')
                ->field('o.targetdate,o.orderId,p.payment_money')
                ->select();
            $return['dySql'] = db()->getLastSql();
            foreach ($dyArr as $k => $v) {
                // 第一次服务金额
                $returnData[date('Y-m-d',strtotime($v['targetdate']))]['dy_ddje']+= $v['payment_money'];
                $returnData[date('Y-m-d',strtotime($v['targetdate']))]['dyOrderId'][$v['orderId']] = $v['orderId'];
            }
            foreach ($returnData as $key => $value) {
                
            }
            */
            // 第一次服务 三宝业绩 预约金 套系补款
            $dyArr = db('c_customerorder')->alias('o')
                ->join('c_paymentcontent p','o.orderId=p.orderId','left')->where('o.targetdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
                ->where('o.blankoutannal','<>','1')->where('o.shop_code',$shop_code)
                ->where('p.fundname','in',['前期全款','三宝全款','前期订金','前期补款'])
                ->field('o.targetdate,o.orderId,p.payment_money,p.fundname')
                ->select();
            $return['dySql'] = db()->getLastSql();
            foreach ($dyArr as $k => $v) {
                // 第一次服务
                if ($v['fundname']=='前期全款') {
                    // 第一次服务金额
                    $returnData[date('Y-m-d',strtotime($v['targetdate']))]['dy_ddje']+= $v['payment_money'];
                    $returnData[date('Y-m-d',strtotime($v['targetdate']))]['dyOrderId'][$v['orderId']] = $v['orderId'];
                }
                // 三宝订单金额
                else if ($v['fundname']=='三宝全款') {
                    $returnData[date('Y-m-d',strtotime($v['targetdate']))]['sb_ddje']+= $v['payment_money'];
                    $returnData[date('Y-m-d',strtotime($v['targetdate']))]['sbOrderId'][$v['orderId']] = $v['orderId'];
                }
                // 预约金
                else if ($v['fundname']=='前期订金') {
                    $returnData[date('Y-m-d',strtotime($v['targetdate']))]['yyj'] += $v['payment_money'];
                }
                // 套系补款
                else if ($v['fundname']=='前期补款') {
                    $returnData[date('Y-m-d',strtotime($v['targetdate']))]['txbk'] += $v['payment_money'];
                }
            }
            foreach ($returnData as $key => $value) {
                // 第一次服务订单量
                $returnData[$key]['dy_ddl'] = count($value['dyOrderId']) ? count($value['dyOrderId']) : '';
                // 第一次服务订单均值
                $returnData[$key]['dy_jdz'] = $returnData[$key]['dy_ddl'] ? sprintf('%.2f', $value['sb_ddje'] / $returnData[$key]['dy_ddl']) : '';
                // 三宝订单量
                $returnData[$key]['sb_ddl'] = count($value['sbOrderId']) ? count($value['sbOrderId']) : '';
                // 三宝订单均值
                $returnData[$key]['sb_jdz'] = $returnData[$key]['sb_ddl'] ? sprintf('%.2f', $value['sb_ddje'] / $returnData[$key]['sb_ddl']) : '';
            }
        //------------------------ 套系订单 结束 ------------------------//

        //------------------------ 客资数据 开始 ------------------------//
            $whereCus['is_status'] = 1;
            $whereCus['c_from'] = 1;
            $whereCus['add_shop_code'] = $shop_code;
            $whereCus['dj_day'] = ['between',[date('Y-m-d 00:00:00',strtotime($start)),date('Y-m-d 23:59:59',strtotime($end))]];
            $cusArr = db('crm_c_customerinfo')->where($whereCus)->field('id,dj_day,is_valid')->select();
            $return['cusSql'] = db()->getLastSql();
            foreach ($cusArr as $k => $v) {
                $returnData[date('Y-m-d',strtotime($v['dj_day']))]['cjl'] += 1;
                if ($v['is_valid']==1) {
                    $returnData[date('Y-m-d',strtotime($v['dj_day']))]['yxl'] += 1;
                }
            }
            $whereYy['c.is_status'] = 1;
            $whereYy['c.c_from'] = 1;
            $whereYy['l.pc_shop_code'] = $shop_code;
            $whereYy['l.pc_type'] = 2;
            $whereYy['l.is_delete'] = 2;
            $whereYy['l.is_gq'] = 1;
            $whereYy['l.pc_time'] = ['between',[$start,$end]];
            $yyArr = db('crm_c_customerinfo')->alias('c')
                ->join('crm_l_pc_log l','c.id=l.cid','left')
                ->where($whereYy)
                ->field('cid,pc_time,status')->select();
            $return['yySql'] = db()->getLastSql();
            foreach ($yyArr as $k => $v) {
                // 预约量
                $returnData[date('Y-m-d',strtotime($v['pc_time']))]['yyl'] += 1;
                // 入户量
                if ($v['status']==2 || $v['status']==3) {
                    $returnData[date('Y-m-d',strtotime($v['pc_time']))]['rhl'] += 1;
                }
                // 拍摄量
                if ($v['status']==2 || $v['status']==3) {
                    $returnData[date('Y-m-d',strtotime($v['pc_time']))]['psl'] += 1;
                }
                // 订单量
                // if ($v['status']==2) {
                //     $returnData[date('Y-m-d',strtotime($v['pc_time']))]['ddl'] += 1;
                // }
            }
            // 客资 订单量 款项 后期续订
            $kzddArr = db('c_customerorder')->alias('o')
                ->join('c_paymentcontent p','o.orderId=p.orderId','left')->where('o.targetdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
                ->where('o.blankoutannal','<>','1')->where('o.shop_code',$shop_code)
                ->where('p.fundname','后期续订')
                ->group('p.orderId')
                ->field('o.targetdate,o.orderId,p.payment_money,p.fundname')
                ->select();
            $return['kzddSql'] = db()->getLastSql();
            foreach ($kzddArr as $k => $v) {
                $returnData[date('Y-m-d',strtotime($v['targetdate']))]['ddl'] += 1;
            }
        //------------------------ 客资数据 结束 ------------------------//

        //------------------------ 取件数据 开始 ------------------------//
            // 产品门店到件（产品整件完成）
            $mddjArr = db('c_customerorder_servicefc')->alias('fc')
                ->join('c_customersp s','fc.orderId = s.orderId AND fc.currentfc = s.currentfc','left')
                ->where('fc.whethercantakedate','between',[$start,$end])
                ->where('s.shop_code',$shop_code)
                ->field('DISTINCT fc.orderId,fc.whethercantakedate')
                ->select();
            $return['mddjSql'] = db()->getLastSql();
            foreach ($mddjArr as $k => $v) {
                $returnData[date('Y-m-d',strtotime($v['whethercantakedate']))]['mddj']+= 1;
            }
            // 取件 （取件完成）
            $qjArr = db('c_customerorder_servicefc')->alias('fc')
                ->join('c_customersp s','fc.orderId = s.orderId AND fc.currentfc = s.currentfc','left')
                ->where('fc.alreadytakedate','between',[$start,$end])
                ->where('s.shop_code',$shop_code)
                ->field('DISTINCT fc.orderId,fc.alreadytakedate')
                ->select();
            $return['qjSql'] = db()->getLastSql();
            foreach ($qjArr as $k => $v) {
                $returnData[date('Y-m-d',strtotime($v['alreadytakedate']))]['qj']+= 1;
            }
            // 误稿件 （？？）
            // $wgjArr = db('c_customerorder_servicefc')->alias('fc')
            //     ->join('c_customersp s','fc.orderId = s.orderId AND fc.currentfc = s.currentfc','left')
            //     ->where('fc.alreadytakedate','between',[$start,$end])
            //     ->where('s.shop_code',$shop_code)
            //     ->field('fc.alreadytakedate,DISTINCT fc.orderId')
            //     ->select();
            // $return['wgjSql'] = db()->getLastSql();
            // foreach ($wgjArr as $k => $v) {
            //     $returnData[date('Y-m-d',strtotime($v['alreadytakedate']))]['wgj']+= 1;
            // }
            // 误件 
            $wjArr = db('c_customerorder_servicefc')->alias('fc')
                ->join('c_customersp s','fc.orderId = s.orderId AND fc.currentfc = s.currentfc','left')
                ->where('fc.whethercantakedate','')
                ->where('fc.qj_getDate','between',[date('Y-m-d 00:00:00',strtotime($start)),date('Y-m-d 23:59:59',strtotime($end))])
                ->where('s.shop_code',$shop_code)
                ->field('DISTINCT fc.orderId,fc.qj_getDate')
                ->select();
            $return['wjSql'] = db()->getLastSql();
            foreach ($wjArr as $k => $v) {
                $returnData[date('Y-m-d',strtotime($v['qj_getDate']))]['wj']+= 1;
            }
            // 返工 
            $fgArr = db('c_foundryprojecttable')->alias('tb')
                ->join('c_customersp s','tb.orderId = s.orderId AND tb.fc = s.currentfc','left')
                ->where('tb.Reject',1)
                ->where('tb.returndate','between',[date('Y-m-d 00:00:00',strtotime($start)),date('Y-m-d 23:59:59',strtotime($end))])
                ->where('s.shop_code',$shop_code)
                ->field('DISTINCT s.orderId,tb.returndate')
                ->select();
            $return['fgSql'] = db()->getLastSql();
            foreach ($fgArr as $k => $v) {
                $returnData[date('Y-m-d',strtotime($v['returndate']))]['fg']+= 1;
            }
        //------------------------ 取件数据 结束 ------------------------//

        //------------------------ 完相数据 开始 ------------------------//
            $whereWx['c.is_status'] = 1;
            $whereWx['c.c_from'] = 1;
            $whereWx['l.pc_shop_code'] = $shop_code;
            $whereWx['l.pc_type'] = 2;
            $whereWx['l.is_delete'] = 2;
            $whereWx['l.is_gq'] = 1;
            $whereWx['l.pc_time'] = ['between',[$start,$end]];
            $wxArr = db('crm_c_customerinfo')->alias('c')
                ->join('crm_l_pc_log l','c.id=l.cid','left')
                ->where($whereWx)->where('l.smgroup','<>','')->where('l.smgroup','not null')
                ->group('pc_time,smgroup')
                ->field('cid,pc_time,status,smgroup')->select();
            $return['wxSql'] = db()->getLastSql();
            foreach ($wxArr as $k => $v) {
                $returnData[date('Y-m-d',strtotime($v['pc_time']))]['syzs'] += 1;
            }
            foreach ($returnData as $key => $value) {
                // 入户量/组
                $returnData[$key]['rhlz'] = $value['syzs'] ? sprintf('%.2f', $value['rhl'] / $value['syzs']) : '';
                // 入户比率
                $returnData[$key]['rhbl'] = $value['syzs'] ? sprintf('%.2f', $value['rhl'] / $value['syzs'] * 4) : '';
            }
        //------------------------ 完相数据 结束 ------------------------//

        $array = array_values($returnData);
        $return['code'] = 0;
        $return['msg']  = '';
        $return['count']= count($array);
        $return['data'] = $array;
        return json($return);
    }

    public function show(){
        $menu_code = strtolower(input('param.menu_code'));
        $info = db('crm_s_report_form')->alias('f')->join('crm_s_report_detail d','d.form_id=f.id','left')->where('f.code',$menu_code)->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name')->find();

        $brandclass_id = $info['brandclass_id'] ? (int)$info['brandclass_id'] : session('brandclass_id');
        $shop_code = $info['shop_code'] ? $info['shop_code'] : session('shop_code');
        
        // 可操作店 
        $mc = new \app\common\lib\mcInterface();
        $shopData = $mc->getWorkShop(['staff_id'=>session('staff_id'),'brandclass_id'=>$brandclass_id]);
        $this->assign('shopData',$shopData);
        $this->assign("shop_code", $shop_code);

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

        //消费类型
        $consumptionType = controller("Customer", "lib")->consumptionType(['brandclass_id'=>$info['brandclass_id']]);
        $this->assign("consumptionType", $consumptionType);

        //来源渠道
        $channel = getChannelJson($shop_code);
        $this->assign("channel", json_encode($channel));

        //渠道一二级
        $channel2 = getChannelJson12($shop_code);
        $this->assign("channel2",json_encode($channel2));

        $code = $this->getCode();
        $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');
        $shopInfo = controller('crm/Customer','lib')->get_shop(['shop_code'=>$shop_code]);
        $this->assign('title', $shopInfo[0]['shop_name'].'-'.$title);
        $this->assign('did', 0);
        $this->assign('menu_code', $menu_code);
        $this->assign('start', date('Y-m-01'));
        $this->assign('end', date('Y-m-d'));
        return $this->fetch($display);
    }
}