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

class Sw_cd_customer extends Report {

    private $level=3;
    private $type=4;
    private $code='code1527744704';

    public function getLevel(){
        return $this->level;

    }
    public function getType(){
        return $this->type;
    }
    public function getCode(){
        return $this->code;
    }
    public function data()
    {
        $param = input();
        $start = $param['start'] ? date('Y-m-d', strtotime($param['start'])) : date('Y-m-d');
        $end =  $param['end'] ? date('Y-m-d 23:59:59', strtotime($param['end'])) : date('Y-m-d 23:59:59');
        $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();
        $shop_code = $det['shop_code'];
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }
        $brandclass_id = $det['brandclass_id'];
        $consultation_type_id = $param['consultation_type_id'] ? $param['consultation_type_id'] : HS_TYPE;

        $staffInfo = controller('crm/Customer','lib')->get_staff_lists(['shop_code'=>$shop_code]);
        $id = array_column($staffInfo,'id');
        $groupInfo = db('c_staff_group')->alias('g')->join('c_staff_group_relation r','r.group_id=g.id')->where('r.staff_id','in',$id)->column('r.staff_id,g.group_name,r.staff_name','r.staff_id');
        $inGroup = [];
        $noGroup = [];

        foreach($staffInfo as $k=>$v){
           if($groupInfo[$v['id']]['group_name']){
               $inGroup[$v['id']]['id'] = $v['id'];
               $inGroup[$v['id']]['shop_name'] = $v['shop_name'] ? $v['shop_name'] : '';
               $inGroup[$v['id']]['department_name'] = $v['department_name'] ? $v['department_name'] : '';
               $inGroup[$v['id']]['group_name'] = $groupInfo[$v['id']]['group_name'] ? $groupInfo[$v['id']]['group_name'] : '无小组';
               $inGroup[$v['id']]['staff_name'] = $v['staffname'];
           }else{
               $noGroup[$v['id']]['id'] = $v['id'];
               $noGroup[$v['id']]['shop_name'] = $v['shop_name'] ? $v['shop_name'] : '';
               $noGroup[$v['id']]['department_name'] = $v['department_name'] ? $v['department_name'] : '';
               $noGroup[$v['id']]['group_name'] = $groupInfo[$v['id']]['group_name'] ? $groupInfo[$v['id']]['group_name'] : '无小组';
               $noGroup[$v['id']]['staff_name'] = $v['staffname'];
           }
       }
        $staff = array_merge($inGroup,$noGroup);
        $ids = array_column($staff,'id');
        $where['is_status'] = 1;
        $where['c_from'] = 1;


        $sql = '';
        $sql .= "sum(dj_day between '$start' and '$end') as mkz,";
        $sql .= "sum(consultation_type_id = ".$consultation_type_id." and 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(consultation_type_id = ".$consultation_type_id." and sign_name='A客' and sign_time between '$start' and '$end') as a,";//A客
        $sql .= "sum(consultation_type_id = ".$consultation_type_id." and yjd_day between '$start' and '$end') as yy,";//邀约

        $sql .= "sum(consultation_type_id = ".$consultation_type_id." and is_intostore in (2,3) and syjd_day between '$start' and '$end') as jd,";//婚纱进店
        $sql .= "sum(is_intostore in (2,3) and syjd_day between '$start' and '$end') as alljd,";//总进店
        $sql .= "sum(consultation_type_id = ".$consultation_type_id." and is_success=1 and order_day between '$start' and '$end') as hsOrder,";//婚纱订单
        $sql .= "sum(is_success=1 and order_day between '$start' and '$end') as allOrder";//总订单
        // 录入客资量（分配）
        $info= db('c_customerinfo')
            ->where($where)
            ->where('shop_code','in',$shop_code)
            ->where('sales_staff_id','in',$ids)
            ->group('sales_staff_id')
            ->column($sql,'sales_staff_id');
        $tel= db('c_customerinfo')
            ->where($where)
            ->where('shop_code','in',$shop_code)
            ->where('sales_staff_id','in',$ids)
            ->group('sales_staff_id')
            ->column('sum(tel_visit_num)','sales_staff_id');

       //总成交
        $fact = db('c_customerinfo')->alias('c')->join('c_order_detail d','c.order_number=d.order_sn')
            ->where($where)
            ->where('d.pay_type',1)
            ->where('d.pay_time', 'between time', [$start, $end])
            ->where('c.shop_code','in',$shop_code)
            ->where('c.sales_staff_id','in',$ids)
            ->group('c.sales_staff_id')
            ->column('sum(d.order_fact)', 'c.sales_staff_id');
        //总套系
        $amount = db('c_customerinfo')->alias('c')->join('c_customer_order o','c.order_number=o.order_sn')
            ->where($where)
            ->where('c.order_start_time', 'between time', [$start, $end])
            ->where('c.shop_code','in',$shop_code)
            ->where('c.sales_staff_id','in',$ids)
            ->group('c.sales_staff_id')
            ->column('sum(o.order_amount)', 'c.sales_staff_id');

        $returnData = [];
        $tot = [];
        foreach($staff as $k=>$v){
            $arr = [];
            $arr['shop'] = $v['shop_name'];
            $arr['department'] = $v['department_name'];
            $arr['group'] = $v['group_name'];
            $arr['staffname'] = $v['staff_name'];
            $arr['mkz'] = $info[$v['id']]['mkz'] ? $info[$v['id']]['mkz'] : "";
            $arr['valid'] = $info[$v['id']]['valid'] ? $info[$v['id']]['valid'] : '';
            $arr['dd'] = $info[$v['id']]['dd'] ? $info[$v['id']]['dd'] : '';
            $arr['unvalid'] = $info[$v['id']]['wx'] ? $info[$v['id']]['wx'] : '';
            $arr['tel'] = $tel[$v['id']] ? $tel[$v['id']] : "";
            $arr['a'] = $info[$v['id']]['a'] ? $info[$v['id']]['a'] : '';
            $arr['aRate'] = sprintf('%.2f',$arr['a']/(intval($arr['valid'])+intval($arr['dd']))*100).'%';
            $arr['yy'] = $info[$v['id']]['yy'] ? $info[$v['id']]['yy'] : '';
            $arr['yyRate']  = $arr['a']==0 ? '' : sprintf('%.2f',$arr['yy']/$arr['a']*100).'%';//邀约率  邀约/有效
            $arr['jd'] = $info[$v['id']]['jd'] ? $info[$v['id']]['jd'] : '';
            $arr['jdRate']  = $arr['yy']==0 ? '' : sprintf('%.2f',$arr['jd']/$arr['a']*100).'%';//进店率  进店/邀约
            $arr['HSsucc'] = $info[$v['id']]['hsOrder'] ? $info[$v['id']]['hsOrder'] : '';
            $arr['HSsuccRate']  = $arr['HSsucc']==0 ? '' : sprintf('%.2f',$arr['HSsucc']/$arr['jd']*100).'%';//成交率  成交/进店

            $arr['succ'] = $info[$v['id']]['allOrder'] ? $info[$v['id']]['allOrder'] : '';
            $arr['succRate']  = $arr['succ']==0 ? '' : sprintf('%.2f',$arr['succ']/$info[$v['id']]['alljd']*100).'%';//成交率  成交/进店

            $arr['fact']  = $fact[$v['id']]==0 ? '' : sprintf('%.2f',$fact[$v['id']]);//总实收
            $arr['total']  = $amount[$v['id']] == 0 ? '' : sprintf('%.2f',$amount[$v['id']]);//总套系

            array_push($returnData,$arr);
            //总计
            $tot['mkz'] += $arr['mkz'];
            $tot['valid'] += $arr['valid'];
            $tot['dd'] += $arr['dd'];
            $tot['unvalid'] += $arr['unvalid'];
            $tot['tel'] += $arr['tel'];
            $tot['a'] += $arr['a'];
            $tot['aRate'] = '';
            $tot['yy'] += $arr['yy'];
            $tot['yyRate'] = '';
            $tot['jd'] += $arr['jd'];
            $tot['alljd'] += $info[$v['id']]['alljd'];
            $tot['jdRate'] = '';
            $tot['HSsucc'] += $arr['HSsucc'];
            $tot['HSsuccRate'] = '';
            $tot['succ'] += $arr['succ'];
            $tot['succRate'] = '';
            $tot['fact'] += floatval($arr['fact']);
            $tot['total'] += floatval($arr['total']);
        }
        //总计
        $total = [];
        $total['shop'] = '';
        $total['department'] = '';
        $total['group'] = '';
        $total['staffname'] = '总计';
        $total['mkz'] = $tot['mkz'];
        $total['valid'] = $tot['valid'];
        $total['dd'] = $tot['dd'];
        $total['unvalid'] = $tot['unvalid'];
        $total['tel'] = $tot['tel'];
        $total['a'] = $tot['a'];
        $total['aRate'] = sprintf('%.2f',$tot['a']/($tot['valid']+$tot['dd'])*100).'%';
        $total['yy'] = $tot['yy'];
        $total['yyRate'] = $total['valid']==0 ? '0%' : sprintf('%.2f',$total['yy']/$total['a']*100).'%';//邀约率  邀约/有效
        $total['jd'] = $tot['jd'];
        $total['jdRate']  = $total['yy']==0 ? '0%' : sprintf('%.2f',$total['jd']/$total['a']*100).'%';//进店率  进店/邀约
        $total['HSsucc'] += $tot['HSsucc'];
        $total['HSsuccRate']  = $total['HSsucc']==0 ? '0%' : sprintf('%.2f',$total['HSsucc']/$total['jd']*100).'%';//成交率  成交/进店
        $total['succ'] += $tot['succ'];
        $total['succRate']  = $total['succ']==0 ? '0%' : sprintf('%.2f',$total['succ']/$tot['alljd']*100).'%';//成交率  成交/进店
        $total['fact']  = sprintf('%.2f',$tot['fact']);//毛客资转换率  成交/毛客资
        $total['total']  = sprintf('%.2f',$tot['total']);//有效客资转换率  成交
        array_push($returnData,$total);
        // 导出
        if ($param['need'] == 1) {
            $title = ['店面','部门','小组','员工','毛客资数','有效(婚纱)','待定(全部)','无效(全部)','电访量','A客量(婚纱)','A客转化率(A客/(有效+待定))','邀约(婚纱)','邀约率(邀约/A客)','进店(婚纱)','进店率(进店/A客)','成交量(婚纱)','成交率(成交量/进店)','总订单数 ','总订单率','总实收','总套系'];
            $code = $this->getCode();
            $ff = db('s_report_form')->where('code', $code)->value('report_name');
            $shop = controller('crm/Customer','lib')->get_shop(['shop_code'=>$shop_code]);
            $file = $shop[0]['shop_name'].'-'.$ff.'.xlsx';
            controller("crm/Customer", "lib")->export($title, $returnData, $file);
        } else {
            $return['code'] = 0;
            $return['msg'] = '';
            $return['count'] = count($returnData);
            $return['data'] = $returnData;
            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();
        $shop= controller('crm/Customer','lib')->get_shop(['shop_code'=>$info['shop_code']]);
        $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]['shop_name'].'-'.$title);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}