<?php
/**
 * 数据沉淀用
 * User: sx01
 * Date: 2019/2/25
 * Time: 19:17
 */
namespace app\crm\lib;
use think\Cache;
use think\Controller;
use PHPExcel_IOFactory;
use Excel5;
use PHPExcel;
use app\common\lib\redisQueue;
use think\Exception;
class Data extends Base{
    //电商渠道漏斗沉淀数据
    public function DsChannelLdData($shop_code){
        set_time_limit(0);
        $where['is_status'] = 1;
        $where['c_from'] = 1;
        $where['customer_type'] = 1;
        //如果存在，删除上月沉淀
        $last_month_begin = date("Y-m-d",mktime(0, 0 , 0,date("m")-1,1,date("Y")));
        $id = db('crm_d_ds_channel_ld')->where('shop_code',$shop_code)->where('time',$last_month_begin)->value('id');
        if(empty($id)){
            db('crm_d_ds_channel_ld')->where('shop_code',$shop_code)->where('time','like','%'.date('Y-m',strtotime($last_month_begin)).'%')->delete();
        }
        $month = getdateArea('last_month');
        $start = $month[0];
        $end = $month[1];
        $date = getDateFromRange('2019-02-01','2019-02-28');
        $channel = db('crm_s_channel')->where('shop_code',$shop_code)->order('order_sort')->select();
        $time =' 23:59:59';
        $group = 's.channel_name';
        $inset = [];
        foreach($date as $k=>$v){
            $cost = db('crm_s_channel_cost')->alias('c')
                ->where('c.cost_date',$v)
                ->where('c.shop_code', $shop_code)
                ->group('c.channel_name')
                ->column('sum(c.cost) as cost,c.id,c.channel_class_name,c.channel_info_name,c.channel_info_id,cost_date','c.channel_name');
            $sql = 's.channel_class_name,s.channel_info_name,s.id,';
            $sql .= "sum(dj_day between '$v' and '$v.$time') as mkz,";
            $sql .= "sum(is_valid=1 and yx_day between '$start' and '$end') as valid,";//有效
            $sql .= "sum(is_valid=2 and yx_day between '$start' and '$end') as dd,";//待定
            $sql .= "sum(is_valid=1 and c.customer_tel<>'' and yx_day between '$start' and '$end') as tel,";//总手机
            $sql .= "sum(is_intostore in ('2','3','5') and syjd_day between '$start' and '$end') as jd,";
            $sql .= "sum(is_success=1 and order_day between '$start' and '$end') as `order`";
            $info= db('crm_c_customerinfo')->alias('c')->join('crm_s_channel s', 's.id=c.customer_channel_id')
                ->where($where)
                ->where('c.add_shop_code', $shop_code)
                ->group($group)
                ->column($sql,$group);
            // 营收（实际收入  不区分有效无效）
            $fact = db('crm_c_customerinfo')->alias('c')->join('crm_s_channel s', 's.id=c.customer_channel_id')->join('crm_c_order_detail d','d.order_sn=c.order_number')
                ->where($where)
                ->where('d.pay_type',1)
                ->where('d.pay_time', 'between time',[$v, $v.$time])
                ->group($group)
                ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_info_name,s.id',$group);
            // 套系均值（有效单套系应收款均值）
            $amount = db('crm_c_customerinfo')->alias('c')->join('crm_s_channel s', 's.id=c.customer_channel_id')->join('crm_c_customer_order o','o.order_sn=c.order_number')
                ->where($where)
                ->where('c.is_success', 1)
                ->where('c.order_day', 'between time',[$v, $v.$time])
                ->group($group)
                ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_info_name,s.id',$group);
            foreach($channel as $kk=>$vv){
                $arr = [];
                $arr['shop_code'] = $shop_code;
                $arr['shop_name'] = $vv['shop_name'];
                $arr['brandclass_id'] = $vv['brandclass_id'];
                $arr['brandclass'] = $vv['brandclass_name'];
                $arr['time'] = $v;
                $arr['channel_id'] = $vv['id'];
                $arr['channel_name'] = $vv['channel_name'];
                $arr['channel_info_id'] = $vv['channel_info_id'];
                $arr['channel_info_name'] = $vv['channel_info_name'];
                $arr['cost'] = $cost[$vv['channel_name']];
                $arr['mkz'] = $info[$vv['channel_name']]['mkz'];
                $arr['valid'] = $info[$vv['channel_name']]['valid'];
                $arr['dd'] = $info[$vv['channel_name']]['dd'];
                $arr['tel'] = $info[$vv['channel_name']]['tel'];
                $arr['jd'] = $info[$vv['channel_name']]['jd'];
                $arr['order'] = $info[$vv['channel_name']]['order'];
                $arr['fact'] = $fact[$vv['channel_name']];
                $arr['amount'] = $amount[$vv['channel_name']];
                $arr['amountAVG'] = $amount[$vv['channel_name']] ? sprintf('%.2f',$amount[$vv['channel_name']]/$arr['order']) : '';
                $arr['validRate'] = $arr['valid'] ? sprintf('%.2f',$arr['valid']/$arr['mkz']*100).'%' : '';
                $arr['jdRate'] = $arr['jd'] ? sprintf('%.2f',$arr['jd']/$arr['valid']*100).'%' : '';
                $arr['orderRate'] = $arr['order'] ? sprintf('%.2f',$arr['order']/$arr['jd']*100).'%' : '';
                $arr['orderValidRate'] = $arr['order'] ? sprintf('%.2f',$arr['order']/$arr['valid']*100).'%' : '';//有效客资转化率
                $arr['mkzCost'] = $cost[$vv['channel_name']] ? sprintf('%.2f',$arr['cost']/$arr['mkz']) : '';
                $arr['validCost'] = $arr['cost'] ? sprintf('%.2f',$arr['cost']/$arr['valid']) : '';
                $arr['orderCost'] = $arr['cost'] ? sprintf('%.2f',$arr['cost']/$arr['order']) : '';
                $arr['roi'] = sprintf('.%2f,',$arr['fact']/$arr['cost']).':1';
                $inset[] = $arr;
                unset($arr);
            }
        }
        db()->startTrans();
        try{
            db('crm_d_ds_channel_ld')->insertAll($inset);
            $log = [];
            $log['action'] = '电商渠道漏斗--沉淀';
            $log['code'] = 200;
            $log['error_reason'] = null;
            $log['create_time'] = date('Y-m-d H:i:s');
            db('crm_l_error_log')->insert($log);
            db()->commit();
            return true;
        }catch (\Exception $e){
            db()->rollback();
            $log = [];
            $log['action'] = '电商渠道漏斗--沉淀';
            $log['code'] = 100;
            $log['error_reason'] = $e->getMessage();
            $log['create_time'] = date('Y-m-d H:i:s');
            db('crm_l_error_log')->insert($log);
            return false;
        }
    }
    //网销小组数据统计沉淀
    public function WxGroup($shop_code){
        set_time_limit(0);
        $where['is_status'] = 1;
        $where['c_from'] = 1;
        //如果存在，删除上月沉淀
        $last_month_begin = date("Y-m-d",mktime(0, 0 , 0,date("m")-1,1,date("Y")));
        $id = db('crm_d_wx_group')->where('shop_code',$shop_code)->where('time',$last_month_begin)->value('id');
        if(empty($id)){
            db('crm_d_wx_group')->where('shop_code',$shop_code)->where('time','like','%'.date('Y-m',strtotime($last_month_begin)).'%')->delete();
        }
        $month = getdateArea('last_month');
        $start = $month[0];
        $end = $month[1];
        $date = getDateFromRange('2019-02-01','2019-02-28');
        $time =' 23:59:59';
        $group = 'r.staff_id';
        //组内所有人 0609
        $staff = db('c_staff_group')->alias('g')->join('c_staff_group_relation r','g.id=r.group_id')
            ->where('g.shop_code',$shop_code)
            ->field('g.id,g.group_name,r.staff_id,r.staff_name')
            ->select();
        if(!$staff[0]['staff_name']){
            $staffIds = implode(',',array_column($staff,'staff_id'));
            $mc = new \app\common\lib\mcInterface();
            $res = $mc->getStaffByIds($staffIds);
            $staffNames = [];
            if($res['code'] == 200){
                $info = $res['data'];
                foreach($info as $key=>$val){
                    $staffNames[$val['id']] = $val['staffname'];
                }
            }else{
                $log = [];
                $log['action'] = '网销小组数据统计--沉淀';
                $log['code'] = 100;
                $log['error_reason'] = '批量查询小组人员失败';
                $log['create_time'] = date('Y-m-d H:i:s');
                db('crm_l_error_log')->insert($log);
                return false;
            }
        }
        $insert = [];
        foreach($time as $k=>$v){
            $sql = 'g.group_name,c.sales_staff,';
            $sql .= "sum(dj_day between '$v' and '$v.$time') as mkz,";
            $sql .= "sum(jr_day between '$v' and '$v.$time') as jr,";
            $sql .= "sum(is_valid=1 and yx_day between '$start' and '$end') as valid,";//有效
            $sql .= "sum(is_valid=2 and yx_day between '$start' and '$end') as dd,";//待定
            $sql .= "sum(is_valid=3 and yx_day between '$start' and '$end') as wx,";//无效
            $sql .= "sum(is_first_tel is null and dj_day between '$start' and '$end') as notel,";//电商 未获取手机数
            $sql .= "sum(is_first_tel is null and customer_tel<>'' and customer_tel is not null and dj_day between '$start' and '$end') as zhuanTel,";//转手机数
            $sql .= "sum(is_valid=1 and c.customer_tel<>'' and yx_day between '$start' and '$end') as tel,";//总手机
            $sql .= "sum(is_intostore in ('2','3','5') 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(is_loss=1 and loss_day between '$start' and '$end') as loss";
            $info= db('crm_c_customerinfo')->alias('c')->join('c_staff_group_relation r','c.sales_staff_id=r.staff_id')->join
            ('c_staff_group g','g.id=r.group_id')
                ->where($where)
                ->where('c.shop_code', $shop_code)
                ->group($group)
                ->order('g.id desc')
                ->column($sql,'r.staff_id');
            //套系金额 套系金额累计（按订单时间计算）设定类型
            $amount = db('crm_c_customerinfo')->alias('c')->join('c_staff_group_relation r','c.sales_staff_id=r.staff_id')->join
            ('c_staff_group g','g.id=r.group_id')->join('crm_c_customer_order o','c.order_number=o.order_sn')
                ->where($where)
                ->where('c.shop_code','in',$shop_code)
                ->where('c.is_success', 1)
                ->where('o.order_valid', 2)
                ->where('c.order_start_time', 'between time', [$start, $end])
                ->group('r.staff_id')
                ->order('g.id desc')
                ->column('sum(o.order_amount) as sum,c.sales_staff,g.group_name','r.staff_id');
            //实收金额累计(按订单时间计算)
            $fact = db('crm_c_customerinfo')->alias('c')->join('c_staff_group_relation r','c.sales_staff_id=r.staff_id')->join
            ('c_staff_group g','g.id=r.group_id')->join('crm_c_order_detail d','d.order_sn=c.order_number')
                ->where($where)
                ->where('c.shop_code','in',$shop_code)
                ->where('d.pay_type',1)
                ->where('d.pay_time', 'between time', [$start, $end])
                ->group('r.staff_id')
                ->order('g.id desc')
                ->column('sum(d.order_fact) as sum,c.sales_staff,g.group_name','r.staff_id');
            //预付定金 实收金额大于0，低于有效订单标准，的实收累计（订单时间计算）
            $dj = db('crm_c_customerinfo')->alias('c')->join('c_staff_group_relation r','c.sales_staff_id=r.staff_id')->join
            ('c_staff_group g','g.id=r.group_id')->join('crm_c_customer_order o','o.order_sn=c.order_number')
                ->where($where)
                ->where('c.shop_code',$shop_code)
                ->where('c.is_success', 3)
                ->where('o.order_valid', 1)
                ->where('o.target_time', 'between time', [$start, $end])
                ->group('r.staff_id')
                ->order('g.id desc')
                ->column('sum(o.earnest_money) as sum,c.sales_staff,g.group_name','r.staff_id');
            foreach($staff as $kk=>$vv){
                $arr = [];
                $arr['time'] = $v;
                $arr['group_name'] = $v['group_name'];
                $arr['staff_name'] = $v['staff_name'] ? $v['staff_name'] : $staffNames[$v['staff_id']];
                $arr['shop_code'] = $shop_code;
                $arr['shop_name'] = $v['shop_name'];
                $arr['brandclass_id'] = $v['brandclass_id'];
                $arr['brandclass'] = $v['brandclass'];
                $arr['mkz'] = $info[$vv['id']]['mkz'];
                $arr['jr'] = $info[$vv['id']]['jr'];
                $arr['valid'] = $info[$vv['id']]['valid'];
                $arr['dd'] = $info[$vv['id']]['dd'];
                $arr['wx'] = $info[$vv['id']]['wx'];
                $arr['validRate'] = $arr['valid'] ? sprintf('%.2f',$arr['valid']/$arr['mkz']*100).'%' : '';
                $arr['wxRate'] = $arr['wx'] ? sprintf('%.2f',$arr['wx']/$arr['mkz']*100).'%' : '';
                $arr['ddRate'] = $arr['dd'] ? sprintf('%.2f',$arr['dd']/$arr['mkz']*100).'%' : '';
                $arr['notel'] = $info[$vv['id']]['notel'];
                $arr['zhaunTel'] = $info[$vv['id']]['zhaunTel'];
                $arr['zhaunRate'] = $arr['zhaunTel'] ? sprintf('%.2f',$arr['zhaunTel']/$arr['notel']*100).'%' : '';
                $arr['tel'] = $info[$vv['id']]['tel'];
                $arr['jd'] = $info[$vv['id']]['jd'];
                $arr['jdRate'] = $arr['jd'] ? sprintf('%.2f',$arr['jd']/$arr['valid']*100).'%' : '';
                $arr['order'] = $info[$vv['id']]['order'];
                $arr['orderRate'] = $arr['order'] ? sprintf('%.2f',$arr['order']/$arr['jd']*100).'%' : '';
                $arr['loss'] = $info[$vv['id']]['loss'];
                $arr['lossRate'] = $arr['loss'] ? sprintf('%.2f',$arr['loss']/$arr['jd']*100).'%' : '';
                $arr['amount'] = $amount[$vv['id']];
                $arr['fact'] = $fact[$vv['id']];
                $arr['yk'] = $fact[$vv['id']] ? sprintf('%.2f',$arr['amount']-$arr['fact']) : '';
                $arr['dj'] = $dj[$vv['id']]['sum'];
                $arr['orderMkzRate'] = $arr['order'] ? sprintf('%.2f',$arr['order']/$arr['mkz']*100).'%' : '';//总客资成单率
                $arr['orderValidRate'] = $arr['order'] ? sprintf('%.2f',$arr['order']/$arr['valid']*100).'%' : '';//有效客资成单率
                $inset[] = $arr;
                unset($arr);
            }
        }
        db()->startTrans();
        try{
            db('crm_d_wx_group')->insertAll($inset);
            $log = [];
            $log['action'] = '网销小组数据统计--沉淀';
            $log['code'] = 200;
            $log['error_reason'] = null;
            $log['create_time'] = date('Y-m-d H:i:s');
            db('crm_l_error_log')->insert($log);
            db()->commit();
            return true;
        }catch (\Exception $e){
            db()->rollback();
            $log = [];
            $log['action'] = '网销小组数据统计--沉淀';
            $log['code'] = 100;
            $log['error_reason'] = $e->getMessage();
            $log['create_time'] = date('Y-m-d H:i:s');
            db('crm_l_error_log')->insert($log);
            return false;
        }
    }
}