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

class Mkss_aysm_order_data extends Report {

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

	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();

		$start= date('Y-m-d 00:00:00', strtotime($data['start']));
		$end  = date('Y-m-d 23:59:59', strtotime($data['end']));

		//可操作店
		if($data['shop_code']){
			$shop_code = $data['shop_code'];
		}else{
			$shop_code = session('shop_code');
		}
		if(empty($shop_code)){
			return array('code'=>100,'msg'=>'请选择店面');
		}
		$mc = new \app\common\lib\mcInterface();

		$where['c.is_status'] = 1;
		$where['c.c_from'] = 1;

		// 上门导师
		if ($data['sales_staff']) {
			$staffs = $data['sales_staff'];
		}else{
			// 可操作店 
			$tempStaffs = $mc->getStaffInfo(['shop_code'=>$shop_code]);
			$salesStaffs = [];
			foreach ($tempStaffs as $k => $v) {
				if (strstr($v['work_types'],(string)DS_WORK_TYPE)) {
					$staffs[] = $v['staffname'];
				}
			}
		}
		// 渠道
        if($data['channel']){
            $channels = explode(',',$data['channel']);
        }else{
            // 来源渠道
            $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
            $channels = array_column($channels,'channel_name');
        }
        // 拍摄量
        $where['c.is_status'] = 1;
		$where['c.c_from'] = 1;
		$where['c.customer_channel']= ['in',$channels];
		$info = db('crm_c_customerinfo')->alias('c')
			->join('crm_l_pc_log l','c.id=cid','left')
			->where($where)
			->where('l.tutor_name', 'in', $staffs)->where('l.status', 'in', ['2','3'])
			->where('l.pc_time','between',[date('Y-m-d',strtotime($start)),date('Y-m-d',strtotime($end))])
			->group('l.tutor_name')->column('count(l.id)', 'l.tutor_name');
		$infoSql = db()->getLastSql();
		// 订单量 
		$payInfo1 = db('crm_c_customerinfo')->alias('c')
			->join('crm_l_pc_log l','c.id=cid','left')
			->join('c_customerorder o','l.id=o.crm_visit_id','left')
			->join('c_customerorder_servicefc fc','fc.orderId=o.orderId','left')
			->join('c_paymentcontent p','p.orderId=o.orderId','left')
			->where('l.sm_type','拍照')
			->where($where)
			->where('fc.currentfc','1')
			->where('l.tutor_name', 'in', $staffs)->where('l.status', '2')
			->where('p.paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
			->where('o.crm_customer_id','not null')
			->where('p.fundname','in',['后期续订','后期升套'])
			->group('p.orderId')
			->field('l.tutor_name,o.orderId,fc.allfc')->select();
		$payInfo1Sql = db()->getLastSql();
		$payData = [];
		foreach ($payInfo1 as $k => $v) {
			$payData[$v['tutor_name']]['ddl']  += 1;
			$payData[$v['tutor_name']]['allfc']+= $v['allfc'];
		}
		// 二销个数
		$payInfo2 = db('crm_c_customerinfo')->alias('c')
			->join('crm_l_pc_log l','c.id=cid','left')
			->join('c_customerorder o','l.id=o.crm_visit_id','left')
			->join('c_paymentcontent p','p.orderId=o.orderId','left')
			->where('l.sm_type','拍照')
			->where($where)
			->where('l.tutor_name', 'in', $staffs)->where('l.status', '2')
			->where('p.paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
			->where('o.crm_customer_id','not null')
			->where('p.fundname','后期款')
			->group('p.orderId')
			->field('l.tutor_name,o.orderId')->select();
		$payInfo2Sql = db()->getLastSql();
		foreach ($payInfo2 as $k => $v) {
			$payData[$v['tutor_name']]['exgs'] += 1;
		}
		// 订单总数
		$payInfo3 = db('crm_c_customerinfo')->alias('c')
			->join('crm_l_pc_log l','c.id=cid','left')
			->join('c_customerorder o','l.id=o.crm_visit_id','left')
			->join('c_paymentcontent p','p.orderId=o.orderId','left')
			->where($where)
			->where('l.tutor_name', 'in', $staffs)->where('l.status', '2')
			->where('p.paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
			->where('o.crm_customer_id','not null')->group('p.orderId')
			->field('l.tutor_name,o.orderId')->select();
		foreach ($payInfo3 as $k => $v) {
			$payData[$v['tutor_name']]['alldd'] += 1;
		}
		$payInfo3Sql = db()->getLastSql();
		// 订单和二销的金额 及总业绩
		$payInfo = db('crm_c_customerinfo')->alias('c')
			->join('crm_l_pc_log l','c.id=cid','left')
			->join('c_customerorder o','l.id=o.crm_visit_id','left')
			->join('c_paymentcontent p','p.orderId=o.orderId','left')
			->where('l.sm_type','拍照')
			->where($where)
			->where('l.tutor_name', 'in', $staffs)->where('l.status', '2')
			->where('p.paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
			->where('o.crm_customer_id','not null')
			->field('l.tutor_name,o.orderId,p.fundname,p.payment_money')->select();
		$payInfoSql = db()->getLastSql();
		foreach ($payInfo as $k => $v) {
			if ($v['fundname']=='后期续订' || $v['fundname']=='后期升套') {
				$payData[$v['tutor_name']]['fact'] += $v['payment_money'];
			}
			if ($v['fundname']=='后期款') {
				$payData[$v['tutor_name']]['exje'] += $v['payment_money'];
			}
			// 总业绩
			$payData[$v['tutor_name']]['zyj'] += $v['payment_money'];
		}
		// 三宝业绩
		$sbYj = db('crm_c_customerinfo')->alias('c')
			->join('crm_l_pc_log l','c.id=cid','left')
			->join('c_customerorder o','l.id=o.crm_visit_id','left')
			->join('c_consumptioncontent t','o.orderId=t.orderId','left')
			->where($where)
			->where('categories','三宝')
			->where('l.tutor_name', 'in', $staffs)->where('l.status', '2')
			->group('l.tutor_name')->column('sum(t.total) as sbyj', 'l.tutor_name');
		$sbYjSql = db()->getLastSql();
		// 店内套餐服务个数
		$ddtcs = db('crm_c_customerinfo')->alias('c')
			->join('crm_l_pc_log l','c.id=cid','left')
			->join('c_customerorder o','l.id=o.crm_visit_id','left')
			->where($where)
			->where('o.bargain_money','>=',1000)
			->where('l.tutor_name', 'in', $staffs)->where('l.status', '2')
			->group('l.tutor_name')->column('count(o.orderId) as ddtcs', 'l.tutor_name');
		$ddtcsSql = db()->getLastSql();
		// 转介绍订单个数
		$zjsInfo = db('crm_c_customerinfo')->alias('c')
			->join('c_customerorder o','c.id=o.crm_customer_id','left')
			->where($where)->where('c.dj_staff', 'in', $staffs)
			->where('c.customer_channel','爱婴转介绍')
			->group('c.dj_staff')
			->where('o.targetdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
			->column('count(o.id) as zjsnum','c.dj_staff');
		$zjsInfoSql = db()->getLastSql();
		// 三宝推荐量
		$sbInfo = db('crm_c_customerinfo')->alias('c')
			->join('c_customerorder o','c.id=o.crm_customer_id','left')
			->where($where)->where('c.dj_staff', 'in', $staffs)
			->where('o.package_name','三宝推荐单')
			->group('c.dj_staff')
			->where('o.targetdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
			->column('count(o.id) as sbtjnum','c.dj_staff');
		$sbInfoSql = db()->getLastSql();
		foreach ($staffs as $k => $v) {
			$returnData[$k]['staff'] = $v;
			$returnData[$k]['psl'] = $info[$v] ? $info[$v] : 0;
			$returnData[$k]['ddl'] = $payData[$v]['ddl'] ? $payData[$v]['ddl'] : 0;
			$returnData[$k]['allfc'] = $payData[$v]['allfc'] ? $payData[$v]['allfc'] : 0;
			$returnData[$k]['ddrate'] = $returnData[$k]['ddl'] ? sprintf('%.2f',$returnData[$k]['ddl']/$returnData[$k]['psl']*100).'%' : '0%';
			$returnData[$k]['fact'] = $payData[$v]['fact'] ? $payData[$v]['fact'] : 0;
			// 订单件均 订单量/拍照量
			$returnData[$k]['factrate'] = $returnData[$k]['ddl'] ? (double)sprintf('%.2f',$returnData[$k]['fact']/$returnData[$k]['ddl']) : '0.00';
			$returnData[$k]['exgs'] = $payData[$v]['exgs'] ? $payData[$v]['exgs'] : 0;
			$returnData[$k]['exje'] = $payData[$v]['exje'] ? $payData[$v]['exje'] : 0;
			// 空单量 
			$returnData[$k]['kdl'] = $payData[$v]['alldd'] ? $payData[$v]['alldd']-($returnData[$k]['ddl']+$returnData[$k]['exgs']) : 0;
			// 空单率 空单量 / 拍摄量
			$returnData[$k]['kdrate'] = $returnData[$k]['kdl'] ? sprintf('%.2f',$returnData[$k]['kdl']/$returnData[$k]['psl']*100).'%' : '0%';
			$returnData[$k]['sbyj'] = $sbYj[$v] ? (double)sprintf('%.2f',$sbYj[$v]) : '0.00';
			$returnData[$k]['zyj'] = $payData[$v]['zyj'] ? (double)sprintf('%.2f',$payData[$v]['zyj']) : '0.00';
			// 件均
			$returnData[$k]['jj'] = $returnData[$k]['zyj'] ? (double)sprintf('%.2f',$returnData[$k]['zyj']/$returnData[$k]['psl']) : '0.00';
			$returnData[$k]['zjsgs'] = $zjsInfo[$v] ? $zjsInfo[$v] : 0;
			$returnData[$k]['ddtcs'] = $ddtcs[$v] ? $ddtcs[$v] : 0;
			$returnData[$k]['sbtjl'] = $sbInfo[$v] ? $sbInfo[$v] : 0;
			// 总计
			$arr['psl']  += $returnData[$k]['psl'];
			$arr['ddl']  += $returnData[$k]['ddl'];
			$arr['allfc']+= $returnData[$k]['allfc'];
			$arr['fact'] += $returnData[$k]['fact'];
			$arr['exgs'] += $returnData[$k]['exgs'];
			$arr['exje'] += $returnData[$k]['exje'];
			$arr['kdl']  += $returnData[$k]['kdl'];
			$arr['sbyj'] += $returnData[$k]['sbyj'];
			$arr['zyj']  += $returnData[$k]['zyj'];
			// $arr['jsyj'] += $returnData[$k]['jsyj'];
			$arr['zjsgs']+= $returnData[$k]['zjsgs'];
			$arr['ddtcs']+= $returnData[$k]['ddtcs'];
			$arr['sbtjl']+= $returnData[$k]['sbtjl'];
		}
		$arr['staff']  = '总计';
		$arr['ddrate']  += $arr['ddl'] ? sprintf('%.2f',$arr['ddl']/$arr['psl']*100).'%' : '0%';
		$arr['factrate']+= $arr['ddl'] ? (double)sprintf('%.2f',$arr['fact']/$arr['ddl']) : '0.00';
		$arr['kdrate']  += $arr['kdl'] ? sprintf('%.2f',$arr['kdl']/$arr['psl']*100).'%' : '0%';
		$arr['jj'] += $arr['zyj'] ? (double)sprintf('%.2f',$arr['zyj']/$arr['psl']) : '0.00';
		$returnData[] = $arr;

		$return['infoSql'] = $infoSql;
		$return['payInfo1Sql'] = $payInfo1Sql;
		$return['payInfo2Sql'] = $payInfo2Sql;
		$return['payInfo3Sql'] = $payInfo3Sql;
		$return['payInfoSql'] = $payInfoSql;
		$return['sbYjSql'] = $sbYjSql;
		$return['ddtcsSql'] = $ddtcsSql;
		$return['zjsInfoSql'] = $zjsInfoSql;
		$return['sbInfoSql'] = $sbInfoSql;
		$return['code'] = 0;
		$return['msg']  = '';
		$return['count']= count($returnData);
		$return['data'] = $returnData;
		return json($return);
	}

	public function show(){
		$menu_code = strtolower(input('param.menu_code'));
		$info = db('crm_s_report_form')->alias('f')->where('f.code',$menu_code)->field('f.report_class_name')->find();
		$shop_code = session('shop_code');
		// 可操作店 
		$mc = new \app\common\lib\mcInterface();
		$tempShop = $mc->getWorkShop(['staff_id'=>session('staff_id'),'brandclass_id'=>session('brandclass_id')]);
		$shopData = [];
		foreach ($tempShop as $key => $v) {
			$temp['shop_code'] = $v['shop_code'];
			$temp['shop_name'] = $v['shop_name'];
			if ($v['shop_code']==$shop_code) {
				$temp['selected'] = 1;
			}
			array_push($shopData, $temp);
			unset($temp);
		}
		$this->assign('shopData',$shopData);
		if ($shop_code) {
			$tempStaffs = $mc->getStaffInfo(['shop_code'=>$shop_code]);
			$salesStaffs = [];
			foreach ($tempStaffs as $k => $v) {
				if (strstr($v['work_types'],(string)DS_WORK_TYPE)) {
					$salesTemp['staff_id'] = $v['id'];
					$salesTemp['staff_name'] = $v['staffname'];
					array_push($salesStaffs, $salesTemp);
					unset($salesTemp);
				}
			}
			$this->assign('salesStaffs',$salesStaffs);
		}
		//来源渠道
		$channel = getChannelJson($info['shop_code']);
		$this->assign("channel", json_encode($channel));
		// 消费类型
        $consumptionType = controller("Customer", "lib")->consumptionType(['brandclass_id'=>$info['brandclass_id']]);
        $this->assign("consumptionType", $consumptionType);
		$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');
		$this->assign('title', $title);

		return $this->fetch($display);
	}
	
}