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

class Lm_wx_data extends Report {

    private $level=1;
    private $type=2;
    private $code='code1537843449';

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

    }
    public function getType(){
        return $this->type;
    }
    public function getCode(){
        return $this->code;
    }
    public function data()
    {
        $data = input();
        $start = date('Y-m-d 00:00:00', strtotime($data['start']));
        $end = date('Y-m-d 23:59:59', strtotime($data['end']));

        $id = db('s_report_form')->where('code', $this->getCode())->value('id');
        $det = db('s_report_detail')->where('form_id',$id)->field('shop_code,brandclass_id')->find();
        $shop_code = $det['shop_code'];
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }
        $where['is_status'] = 1;
        $where['is_loss'] = 2;
        $where['c_from'] = 1;
        $where['customer_type'] = 1;
        $where['consultation_type_id'] = HS_TYPE;
        $where['shop_code'] = $shop_code;
        $whereG = [];
        $whereD = [];
        $whereC = [];
        if($data['department_id']){
            $where['sales_department_id'] = intval($data['department_id']);
            $whereG['department_id'] = intval($data['department_id']);
            $whereD['department_id'] = $data['department_id'];
            $whereC['c.dj_department_id'] = intval($data['department_id']);
        }

        $whereC['c.is_status'] = 1;
        $whereC['c.is_valid'] = 1;
        $whereC['c.is_loss'] = 2;
        $whereC['c.c_from'] = 1;
        $whereC['c.shop_code'] = $shop_code;
        $whereC['c.consultation_type_id'] = HS_TYPE;
        //小组
        $group = db('c_staff_group')->alias('g')->join('c_staff_group_relation r','r.group_id=g.id')
            ->where('g.shop_code',$shop_code)
            ->where($whereG)
            ->select();
        $sql = '';
        $sql .= "sum(is_valid=1 and yx_day between '$start' and '$end') as valid,";//婚纱数据
        $sql .= "sum(is_valid=1 and customer_tel <> '' and customer_tel is not null and customer_tel <> 0 and yx_day between '$start' and '$end') as tel,";//婚纱电话
        $sql .= "sum(is_intostore in (2,3) and is_valid=1 and syjd_day between '$start' and '$end') as hsJd,";//婚纱进店
        $sql .= "sum(is_valid=1 and is_success=1 and order_day between '$start' and '$end') as hsOrder ";//婚纱订单

        $info = db('c_customerinfo')
            ->where($where)
            ->where('sales_staff_id > 0')
            ->group('sales_staff_id')
            ->column($sql,'sales_staff_id');

//        echo db()->getLastSql();die;
        //老客户进店
        $intJd = db('c_customerinfo')
            ->where($where)
            ->where('is_valid',1)
            ->where('is_intostore','in',[2,3])
            ->where('syjd_day','between',[$start,$end])
            ->group('dj_staff_id')
            ->column('count(id) num','dj_staff_id');
        //老客户订单
        $intOrder = db('c_customerinfo')
            ->where($where)
            ->where('is_valid',1)
            ->where('is_success',1)
            ->where('order_day','between',[$start,$end])
            ->group('dj_staff_id')
            ->column('count(id) num','dj_staff_id');

        //退单
        $td = db('c_customerinfo')->alias('c')->join('c_customer_order o', 'o.customer_id=c.id')
            ->where($whereC)
            ->where('o.unvalid_time','between time', [$start, $end])
            ->group('c.sales_staff_id')
            ->column('count(o.order_sn) num','c.sales_staff_id');

        //婚纱有效套系
        $total = db('c_customerinfo')->alias('c')->join('c_customer_order o','o.customer_id=c.id')
            ->where($whereC)
            ->where('c.is_success', 1)
            ->where('o.unvalid_time',null)
            ->where('c.order_day', 'between time', [$start, $end])
            ->group('c.sales_staff_id')
            ->column('sum(o.order_amount) as amount', 'c.sales_staff_id');
        $returnData = [];
        $arr = [];
        $group_id = $group[0]['id'];
        $department_id = $group[0]['department_id'];
        $last = end($group);
        $last_group_id = $last['id'];
        foreach($group as $k=>$v){
            //小组总计
            if($group_id == $v['id']){
                $groupTotal[$group_id]['department_name'] = '';
                $groupTotal[$group_id]['group_name'] = '';
                $groupTotal[$group_id]['staff_name'] = '<span style="color: #8a6de9">小组总计</span>';
                $groupTotal[$group_id]['valid'] += intval($info[$v['staff_id']]['valid']);
                $groupTotal[$group_id]['tel'] += intval($info[$v['staff_id']]['tel']);
                $groupTotal[$group_id]['hsJd'] += intval($info[$v['staff_id']]['hsJd']);
                $groupTotal[$group_id]['hsOrder'] += intval($info[$v['staff_id']]['hsOrder']);
                $groupTotal[$group_id]['intJd'] += intval($intJd[$v['staff_id']]);
                $groupTotal[$group_id]['intOrder'] += intval($intOrder[$v['staff_id']]);
                $groupTotal[$group_id]['td'] += intval($td[$v['staff_id']]['td']);
                $groupTotal[$group_id]['jd'] += $info[$v['staff_id']]['jd']+intval($intJd[$v['staff_id']]);
                $groupTotal[$group_id]['order'] += $info[$v['staff_id']]['hsOrder']+intval($intOrder[$v['staff_id']]);
                $groupTotal[$group_id]['total'] += $total[$v['staff_id']];
            }

            if($group_id != $v['id']){
                $groupTotal[$group_id]['avg'] =  $groupTotal[$group_id]['total']/$groupTotal[$group_id]['order']>0 ? sprintf('%.2f',$groupTotal[$group_id]['total']/$groupTotal[$group_id]['order']) : '';
                $groupTotal[$group_id]['telRate'] = $groupTotal[$group_id]['tel']/$groupTotal[$group_id]['valid']>0 ? sprintf('%.2f',$groupTotal[$group_id]['tel']/$groupTotal[$group_id]['valid']*100).'%' : '';
                $groupTotal[$group_id]['jdRate'] = $groupTotal[$group_id]['jd']/$groupTotal[$group_id]['tel']>0 ? sprintf('%.2f',$groupTotal[$group_id]['jd']/$groupTotal[$group_id]['tel']*100).'%' : '';
                $groupTotal[$group_id]['succRate'] = $groupTotal[$group_id]['order']/$groupTotal[$group_id]['jd']>0 ? sprintf('%.2f',$groupTotal[$group_id]['order']/$groupTotal[$group_id]['jd']*100).'%' : '';
                $groupTotal[$group_id]['zhRate'] = $groupTotal[$group_id]['order']/$groupTotal[$group_id]['valid']>0 ? sprintf('%.2f',$groupTotal[$group_id]['order']/$groupTotal[$group_id]['valid']*100).'%' : '';
                $groupTotal[$group_id]['tdRate'] = $groupTotal[$group_id]['td']/$groupTotal[$group_id]['order']>0 ? sprintf('%.2f',$groupTotal[$group_id]['td']/$groupTotal[$group_id]['order']*100).'%' : '';
                $returnData[] = $groupTotal[$group_id];
                $group_id = $v['id'];
            }
            if($group_id == $last_group_id){
                $groupTotal['last']['department_name'] = '';
                $groupTotal['last']['group_name'] = '';
                $groupTotal['last']['staff_name'] = '<span style="color: #8a6de9">小组总计</span>';
                $groupTotal['last']['valid'] += intval($info[$v['staff_id']]['valid']);
                $groupTotal['last']['tel'] += intval($info[$v['staff_id']]['tel']);
                $groupTotal['last']['hsJd'] += intval($info[$v['staff_id']]['hsJd']);
                $groupTotal['last']['hsOrder'] += intval($info[$v['staff_id']]['hsOrder']);
                $groupTotal['last']['intJd'] += intval($intJd[$v['staff_id']]);
                $groupTotal['last']['intOrder'] += intval($intOrder[$v['staff_id']]);
                $groupTotal['last']['td'] += intval($td[$v['staff_id']]['td']);
                $groupTotal['last']['jd'] += $info[$v['staff_id']]['jd']+intval($intJd[$v['staff_id']]);
                $groupTotal['last']['order'] += $info[$v['staff_id']]['hsOrder']+intval($intOrder[$v['staff_id']]);
                $groupTotal['last']['total'] += $total[$v['staff_id']];
            }
            /*//部门总计
            if($department_id == $v['department_id']){
                $groupTotal[$department_id]['department_name'] = '';
                $groupTotal[$department_id]['group_name'] = '';
                $groupTotal[$department_id]['staff_name'] = '<span style="color: #0000FF">部门总计</span>';
                $groupTotal[$department_id]['valid'] += intval($info[$v['staff_id']]['valid']);
                $groupTotal[$department_id]['tel'] += intval($info[$v['staff_id']]['tel']);
                $groupTotal[$department_id]['hsJd'] += intval($info[$v['staff_id']]['hsJd']);
                $groupTotal[$department_id]['hsOrder'] += intval($info[$v['staff_id']]['hsOrder']);
                $groupTotal[$department_id]['intJd'] += intval($intJd[$v['staff_id']]);
                $groupTotal[$department_id]['intOrder'] += intval($intOrder[$v['staff_id']]);
                $groupTotal[$department_id]['td'] += intval($td[$v['staff_id']]['td']);
                $groupTotal[$department_id]['jd'] += $info[$v['staff_id']]['jd']+intval($intJd[$v['staff_id']]);
                $groupTotal[$department_id]['order'] += $info[$v['staff_id']]['hsOrder']+intval($intOrder[$v['staff_id']]);
                $groupTotal[$department_id]['total'] += $total[$v['staff_id']];
            }

            if($department_id != $v['department_id']){
                $groupTotal[$department_id]['avg'] =  $groupTotal[$department_id]['total']/$groupTotal[$department_id]['order']>0 ? sprintf('%.2f',$groupTotal[$department_id]['total']/$groupTotal[$department_id]['order']) : '';
                $groupTotal[$department_id]['telRate'] = $groupTotal[$department_id]['tel']/$groupTotal[$department_id]['valid']>0 ? sprintf('%.2f',$groupTotal[$department_id]['tel']/$groupTotal[$department_id]['valid']*100).'%' : '';
                $groupTotal[$department_id]['jdRate'] = $groupTotal[$department_id]['jd']/$groupTotal[$department_id]['tel']>0 ? sprintf('%.2f',$groupTotal[$department_id]['jd']/$groupTotal[$department_id]['tel']*100).'%' : '';
                $groupTotal[$department_id]['succRate'] = $groupTotal[$department_id]['order']/$groupTotal[$department_id]['jd']>0 ? sprintf('%.2f',$groupTotal[$department_id]['order']/$groupTotal[$department_id]['jd']*100).'%' : '';
                $groupTotal[$department_id]['zhRate'] = $groupTotal[$department_id]['order']/$groupTotal[$department_id]['valid']>0 ? sprintf('%.2f',$groupTotal[$department_id]['order']/$groupTotal[$department_id]['valid']*100).'%' : '';
                $groupTotal[$department_id]['tdRate'] = $groupTotal[$department_id]['td']/$groupTotal[$department_id]['order']>0 ? sprintf('%.2f',$groupTotal[$department_id]['td']/$groupTotal[$department_id]['order']*100).'%' : '';
                $returnData[] = $groupTotal[$department_id];
                $department_id = $v['department_id'];
            }*/


            $temp['department_name'] = $v['department_name'];
            $temp['group_name'] = $v['group_name'];
            $temp['staff_name'] = $v['staff_name'];
            $temp['valid'] = $info[$v['staff_id']]['valid'] ? $info[$v['staff_id']]['valid'] : '';
            $temp['tel'] = $info[$v['staff_id']]['tel'] ? $info[$v['staff_id']]['tel'] : '';
            $temp['hsJd'] = $info[$v['staff_id']]['hsJd'] ? $info[$v['staff_id']]['hsJd'] : '';
            $temp['hsOrder'] = $info[$v['staff_id']]['hsOrder'] ? $info[$v['staff_id']]['hsOrder'] : '';
            $temp['intJd'] = $intJd[$v['staff_id']] ? $intJd[$v['staff_id']] : '';
            $temp['intOrder'] = $intOrder[$v['staff_id']] ? $intOrder[$v['staff_id']] : '';
            $temp['td'] = $td[$v['staff_id']] ? $td[$v['staff_id']] : '';
            $temp['jd'] = $intJd[$v['staff_id']]+$info[$v['staff_id']]['hsJd']>0 ? $intJd[$v['staff_id']]+$info[$v['staff_id']]['hsJd'] : '';
            $temp['order'] = $intOrder[$v['staff_id']]+$info[$v['staff_id']]['hsOrder']>0 ? $intOrder[$v['staff_id']]+$info[$v['staff_id']]['hsOrder'] : '';
            $temp['total'] = $total[$v['staff_id']] ? $total[$v['staff_id']] : '';
            $temp['avg'] = $total[$v['staff_id']]/$temp['order']>0 ? sprintf('%.2f',$total[$v['staff_id']]/$temp['order']) : '';
            $temp['telRate'] = $temp['tel']/$temp['valid']>0 ? sprintf('%.2f',$temp['tel']/$temp['valid']*100).'%' : '';
            $temp['jdRate'] = $temp['jd']/$temp['tel']>0 ? sprintf('%.2f',$temp['jd']/$temp['tel']*100).'%' : '';
            $temp['succRate'] = $temp['order']/$temp['jd']>0 ? sprintf('%.2f',$temp['order']/$temp['jd']*100).'%' : '';
            $temp['zhRate'] = $temp['order']/$temp['valid']>0 ? sprintf('%.2f',$temp['order']/$temp['valid']*100).'%' : '';
            $temp['tdRate'] = $temp['td']/$temp['order']>0 ? sprintf('%.2f',$temp['td']/$temp['order']*100).'%' : '';
            $returnData[] = $temp;
            //总计
            $arr['department_name'] = '';
            $arr['group_name'] = '';
            $arr['staff_name'] = '总计';
            $arr['valid'] += $temp['valid'];
            $arr['tel'] += $temp['tel'];
            $arr['hsJd'] += $temp['hsJd'];
            $arr['hsOrder'] += $temp['hsOrder'];
            $arr['intJd'] += intval($temp['intJd']);
            $arr['intOrder'] += intval($temp['intOrder']);
            $arr['td'] += $temp['td'];
            $arr['jd'] += $temp['jd'];
            $arr['order'] += $temp['order'];
            $arr['total'] += $temp['total'];
            $arr['avg'] = '';
            $arr['telRate'] = '';
            $arr['jdRate'] = '';
            $arr['succRate'] = '';
            $arr['zhRate'] = '';
            $arr['tdRate'] = '';
        }
        $arr['avg'] =  $arr['total']/$arr['order']>0 ? sprintf('%.2f',$arr['total']/$arr['order']) : '';
        $arr['telRate'] = $arr['tel']/$arr['valid']>0 ? sprintf('%.2f',$arr['tel']/$arr['valid']*100).'%' : '';
        $arr['jdRate'] = $arr['jd']/$arr['tel']>0 ? sprintf('%.2f',$arr['jd']/$arr['tel']*100).'%' : '';
        $arr['succRate'] = $arr['order']/$arr['jd']>0 ? sprintf('%.2f',$arr['order']/$arr['jd']*100).'%' : '';
        $arr['zhRate'] = $arr['order']/$arr['valid']>0 ? sprintf('%.2f',$arr['order']/$arr['valid']*100).'%' : '';
        $arr['tdRate'] = $arr['td']/$arr['order']>0 ? sprintf('%.2f',$arr['td']/$arr['order']*100).'%' : '';

        $groupTotal['last']['avg'] =  $groupTotal['last']['total']/$groupTotal['last']['order']>0 ? sprintf('%.2f',$groupTotal['last']['total']/$groupTotal['last']['order']) : '';
        $groupTotal['last']['telRate'] = $groupTotal['last']['tel']/$groupTotal['last']['valid']>0 ? sprintf('%.2f',$groupTotal['last']['tel']/$groupTotal['last']['valid']*100).'%' : '';
        $groupTotal['last']['jdRate'] = $groupTotal['last']['jd']/$groupTotal['last']['tel']>0 ? sprintf('%.2f',$groupTotal['last']['jd']/$groupTotal['last']['tel']*100).'%' : '';
        $groupTotal['last']['succRate'] = $groupTotal['last']['order']/$groupTotal['last']['jd']>0 ? sprintf('%.2f',$groupTotal['last']['order']/$groupTotal['last']['jd']*100).'%' : '';
        $groupTotal['last']['zhRate'] = $groupTotal['last']['order']/$groupTotal['last']['valid']>0 ? sprintf('%.2f',$groupTotal['last']['order']/$groupTotal['last']['valid']*100).'%' : '';
        $groupTotal['last']['tdRate'] = $groupTotal['last']['td']/$groupTotal['last']['order']>0 ? sprintf('%.2f',$groupTotal['last']['td']/$groupTotal['last']['order']*100).'%' : '';
        $returnData[] = $groupTotal['last'];
        $returnData[] = $arr;


        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($group);
        $return['data'] = $returnData;

        // 导出
        if ($data['need'] == 1) {
            $title = array('部门', '组别', '销售姓名', '婚纱数据','婚纱电话', '婚纱进店', '婚纱订单', '老客户进店', '老客户订单','退单', '进店', '订单', '婚纱套系', '婚纱均价', '电化率', '进店率', '成交率', '转换率','退单率');
            $code = $this->getCode();
            $ff = db('s_report_form')->where('code', $code)->value('report_name');
            $file = session('department_name').'-'.$ff.'.xlsx';
            controller("crm/Customer", "lib")->export($title, $returnData, $file);
        } else {
            return json($return);
        }
    }

    public function show(){
        $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();

        $code = $this->getCode();
        $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');
        $shopInfo = controller('crm/Customer','lib')->get_shop(['shop_code'=>$info['shop_code']]);
        $this->assign('title', $shopInfo[0]['shop_name'].'-'.$title);
        //店下部门
        $department = controller('crm/Setting','lib')->department_list(['shop_code'=>$info['shop_code']]);
        $this->assign('department', $department);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}