<?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_wx_yj_data extends Report {

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

	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'=>'请选择店面');
		}

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

		$group = 'sales_staff_id';
		if ($data['sales_staff']) {
			$staffs = $data['sales_staff'];
		}else{
			// $allStaffs = db('crm_c_customerinfo')->alias('c')
			// 	->where('c.dj_day','between',[$start,$end])
			// 	->where($where)->where('shop_code',$shop_code)
			// 	->where('sales_staff_id','>',0)
			// 	->group('sales_staff_id')
			// 	->field('sales_staff_id')->select();
			// $staffs = [];
			// foreach ($allStaffs as $key => $value) {
			// 	array_push($staffs, $value['sales_staff_id']);
			// }
			// 可操作店 
			$mc = new \app\common\lib\mcInterface();
			$tempStaffs = $mc->getStaffInfo(['shop_code'=>$shop_code]);
			$salesStaffs = [];
			foreach ($tempStaffs as $k => $v) {
				if (strstr($v['work_types'],(string)RECEIVE_WORK_TYPE)) {
					$staffs[] = $v['id'];
				}
			}
		}
		// 渠道
        if($data['channel']){
            $channels = explode(',',$data['channel']);
        }else{
            // 来源渠道
            $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
            $channels = array_column($channels,'channel_name');
        }

		$sql  = 'c.sales_staff as staff,c.sales_department_name as department_name,';
		$fieldO = 'c.sales_staff as staff,c.sales_staff_id as staff_id,o.package_name,c.consultation_type,o.total_money,o.bargain_money,fc.allfc';
		$fieldP = 'c.sales_staff as staff,c.sales_staff_id as staff_id,p.payment_money';
		$fieldJ = 'c.sales_staff as staff,c.sales_staff_id as staff_id,j.staffname,j.yj_money';
		$where['c.sales_staff_id']  = ['in',$staffs];
		$where['c.customer_channel']= ['in',$channels];

		$yxconfig = db_config('yx_config') ? db_config('yx_config') : "'1','5'";
		$wxconfig = db_config('wx_config') ? db_config('wx_config') : "'3','4'";
		$ddconfig = db_config('dd_config') ? db_config('dd_config') : "'2'";

		$sql .= ' sum(c.dj_day between "'.$start.'" and "'.$end.'") as mkz';
		$sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.is_valid in ('.$yxconfig.')) as yx';
		// 孕儿亲全(消费:除了新生儿，满月以外)总客资数 有效客资数
		$sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.consultation_type not in ("新生儿","满月照") ) as yeqqmkz';
		$sql .= ',sum(c.consultation_type not in ("新生儿","满月照") and c.dj_day between "'.$start.'" and "'.$end.'" and c.is_valid in ('.$yxconfig.') ) as yeqqyx';
		// 新生儿 总客资数 有效客资数
		$sql .= ',sum(c.dj_day between "'.$start.'" and "'.$end.'" and c.consultation_type in ("新生儿","满月照")) as xsemkz';
		$sql .= ',sum(c.is_valid in ('.$yxconfig.') and c.consultation_type in ("新生儿","满月照") and c.dj_day between "'.$start.'" and "'.$end.'") as xseyx';
		// var_dump($sql);die;
		// 客资基本数据
		$info = db('crm_c_customerinfo')->alias('c')
			->where($where)
			->group($group)->column($sql, $group);
		$sql1 = db()->getLastSql();
		// 收款数据
		$payInfo = db('crm_c_customerinfo')->alias('c')
			->join('c_customerorder o','c.id=o.crm_customer_id','left')
			->join('c_paymentcontent p','p.orderId=o.orderId','left')
			->where($where)
			->where('p.paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
			->where('o.crm_customer_id','not null')
			// ->where('o.blankoutannal',0)
			->where('p.suoshu','in',['前期款','前期补款','一销退款'])
			->field($fieldP)->select();
		$payData = [];
		foreach ($payInfo as $k => $v) {
			$payData[$v['staff_id']]['fact'] += $v['payment_money'];
		}
		$sql2 = db()->getLastSql();
		// 业绩数据
		$yjInfo = db('crm_c_customerinfo')->alias('c')
			->join('c_customerorder o','c.id=o.crm_customer_id','left')
			->join('c_paymentcontent p','p.orderId=o.orderId','left')
			->join('c_jobscore j','j.orderId=o.orderId and j.yj_pyid=p.id and c.sales_staff=j.staffname','left')
			->where($where)
			->where('p.paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
			->where('o.crm_customer_id','not null')
			// ->where('o.blankoutannal',0)
			->field($fieldJ)->select();
		$yjData = [];
		foreach ($yjInfo as $k => $v) {
			$yjData[$v['staff_id']]['yj_money'] += $v['yj_money'];
		}
		$sql3 = db()->getLastSql();
		// 客资订单数据
		$order = db('crm_c_customerinfo')->alias('c')
			->join('c_customerorder o','c.id=o.crm_customer_id','left')
			->join('c_customerorder_servicefc fc', 'o.orderId=fc.orderId', 'left')
			->where($where)->where('fc.currentfc',1)
			->where('o.targetdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
			->where('o.crm_customer_id','not null')
			->where('o.blankoutannal',0)
			->field($fieldO)->select();
		$orderData = [];
		foreach ($order as $k => $v) {
			$orderData[$v['staff_id']]['yxtx'] += $v['bargain_money'];
			if ($v['consultation_type']!='新生儿' && $v['consultation_type']!='满月照' && $v['package_name']!='网络团购套系') {
				$orderData[$v['staff_id']]['yeqqddl']  += 1;
				$orderData[$v['staff_id']]['yeqqzfc']  += $v['allfc'];
				$orderData[$v['staff_id']]['yeqqztxje']+= $v['bargain_money'];
			}
			if ( ($v['consultation_type']=='新生儿' || $v['consultation_type']=='满月照') && $v['package_name']=='网络团购套系') {
				$orderData[$v['staff_id']]['xseddl'] += 1;
			}
		}
		$sql4 = db()->getLastSql();
		// 新生儿转换订单金额
		$xsePayInfo = db('crm_c_customerinfo')->alias('c')
			->join('c_customerorder o','c.id=o.crm_customer_id','left')
			->join('c_paymentcontent p','p.orderId=o.orderId','left')
			->where($where)
			->where('o.crm_customer_id','not null')
			// ->where('o.blankoutannal',0)
			->where('p.paymentdate','between',[date('Ymd',strtotime($start)),date('Ymd',strtotime($end))])
			->where('p.currentfc',1)
			->where('p.fundname','in',['后期续订'])
			->field($fieldP)->select();
		$xsePay = [];
		foreach ($xsePayInfo as $k => $v) {
			$xsePay[$v['staff_id']]['xsrFact'] += $v['payment_money'];
		}
		$sql5 = db()->getLastSql();

		// 渠道花费
        $cost = db('crm_s_channel_cost')
            ->where('cost_date', 'between time', [$start, $end])
            ->where($whereCost)
            ->where('channel_name','in',$channels)
            ->group('channel_name')
            ->column('sum(cost) as cost','channel_name');
        // 渠道客资
        $cusNum = db('crm_c_customerinfo')->alias('c')
            ->where('c.dj_day', 'between time', [$start, $end])
            ->where($where)
            ->where('c.customer_channel','in',$channels)
            ->group('c.customer_channel')
            ->column('count(c.id) as num','customer_channel');
        $allChannelInfo = [];
        foreach ($cost as $key => $value) {
            $allChannelInfo[$key]['channel_name'] = $key;
            $allChannelInfo[$key]['cost'] = $value;
            $allChannelInfo[$key]['num'] = $cusNum[$key];
            $allChannelInfo[$key]['cb']  = $value / $cusNum[$key];
        }
        // 员工渠道客资量
        $staffCusNum = db('crm_c_customerinfo')->alias('c')
            ->where('c.dj_day', 'between time', [$start, $end])
            ->where($where)
            ->where('c.customer_channel','in',$channels)
            ->group('c.sales_staff,c.customer_channel')
            ->field('c.sales_staff_id,c.customer_channel,count(c.id) as num')
            ->select();
        $sql6 = db()->getLastSql();
        // 员工客资花费
        $staffCost = [];
        foreach ($staffCusNum as $key => $value) {
            $staffCost[$value['sales_staff_id']] += $allChannelInfo[$value['customer_channel']]['cb']*$value['num'];
        }

		foreach ($staffs as $k => $v) {
			if (!$info[$v]['staff']) {
				$newstaff = getStaffInfoById($v);
				$returnData[$k]['staff'] = $newstaff['staffname'];
				unset($newstaff);
			}else{
				$returnData[$k]['staff'] = $info[$v]['staff'];
			}
			$returnData[$k]['zss'] = $payData[$v]['fact'] ? (double)sprintf('%.2f',$payData[$v]['fact']) : '0';
			$returnData[$k]['zyj'] = $yjData[$v]['yj_money'] ? (double)sprintf('%.2f',$yjData[$v]['yj_money']) : '0';

			$returnData[$k]['mkz']    = $info[$v]['mkz'] ? $info[$v]['mkz'] : '0';
			$returnData[$k]['yxkz']   = $info[$v]['yx'] ? $info[$v]['yx'] : '0';
			$returnData[$k]['yxrate'] = $info[$v]['yx'] ? sprintf('%.2f',$info[$v]['yx']/$info[$v]['mkz']*100).'%' : '0%';
			$returnData[$k]['yeqqmkz']= $info[$v]['yeqqmkz'] ? $info[$v]['yeqqmkz'] : '0';
			$returnData[$k]['yeqqyx'] = $info[$v]['yeqqyx'] ? $info[$v]['yeqqyx'] : '0';
			$returnData[$k]['xsemkz'] = $info[$v]['xsemkz'] ? $info[$v]['xsemkz'] : '0';
			$returnData[$k]['xseyx']  = $info[$v]['xseyx'] ? $info[$v]['xseyx'] : '0';

			$returnData[$k]['yeqqddl']   = $orderData[$v]['yeqqddl'] ? $orderData[$v]['yeqqddl'] : '0';
			$returnData[$k]['yeqqzfc']   = $orderData[$v]['yeqqzfc'] ? $orderData[$v]['yeqqzfc'] : '0';
			$returnData[$k]['yeqqztxje'] = $orderData[$v]['yeqqztxje'] ? (double)sprintf('%.2f',$orderData[$v]['yeqqztxje']) : '0'; 
			$returnData[$k]['yeqqddrate']= $orderData[$v]['yeqqddl'] ? sprintf('%.2f',$orderData[$v]['yeqqddl']/$info[$v]['yeqqmkz']*100).'%' : '0%';
			$returnData[$k]['yeqqztxrate']= $orderData[$v]['yeqqztxje'] ? (double)sprintf('%.2f',$orderData[$v]['yeqqztxje']/$orderData[$v]['yeqqddl']) : '0.00';
			$returnData[$k]['xseddl'] = $orderData[$v]['xseddl'] ? $orderData[$v]['xseddl'] : 0;
			$returnData[$k]['xseddrate'] = $orderData[$v]['xseddl'] ? sprintf('%.2f',$orderData[$v]['xseddl']/$info[$v]['xsemkz']*100).'%' : '0%';
			$returnData[$k]['xsexdpay'] = $xsePay[$v]['xsrFact'] ? $xsePay[$v]['xsrFact'] : 0;

			$returnData[$k]['wxpay'] = $staffCost[$v] ? (double)sprintf('%.2f',$staffCost[$v]) : '0.00';
			$returnData[$k]['yxtx'] = $orderData[$v]['yxtx'] ? (double)sprintf('%.2f',$orderData[$v]['yxtx']) : '0.00';
			$returnData[$k]['ROI'] = $orderData[$v]['yxtx'] ? sprintf('%.2f',$orderData[$v]['yxtx']/$$staffCost[$v]) : '';
			// 总计
			$arr['zss']      += $returnData[$k]['zss'];
			$arr['zyj']      += $returnData[$k]['zyj'];
			$arr['mkz']      += $returnData[$k]['mkz'];
			$arr['yxkz']     += $returnData[$k]['yxkz'];
			$arr['yeqqmkz']  += $returnData[$k]['yeqqmkz'];
			$arr['yeqqyx']   += $returnData[$k]['yeqqyx'];
			$arr['xsemkz']   += $returnData[$k]['xsemkz'];
			$arr['xseyx']    += $returnData[$k]['xseyx'];
			$arr['yeqqddl']  += $returnData[$k]['yeqqddl'];
			$arr['yeqqzfc']  += $returnData[$k]['yeqqzfc'];
			$arr['yeqqztxje']+= $returnData[$k]['yeqqztxje'];
			$arr['xseddl']   += $returnData[$k]['xseddl'];
			$arr['xsexdpay'] += $returnData[$k]['xsexdpay'];

			$arr['yxtx'] += $returnData[$k]['yxtx'];
			$arr['wxpay']+= $returnData[$k]['wxpay'];
		}
		
		$arr['staff']  = '总计';
		$arr['yxrate'] = $arr['yxkz'] ? sprintf('%.2f',$arr['yxkz']/$arr['mkz']*100).'%' : '0%';
		$arr['yeqqddrate'] = $arr['yeqqddl'] ? sprintf('%.2f',$arr['yeqqddl']/$arr['yeqqmkz']*100).'%' : '0%';
		$arr['yeqqztxrate'] = $arr['yeqqztxje'] ? (double)sprintf('%.2f',$arr['yeqqztxje']/$arr['yeqqddl']) : '0.00';
		$arr['xseddrate'] = $arr['xseddl'] ? sprintf('%.2f',$arr['xseddl']/$arr['xsemkz']*100).'%' : '0%';
		$arr['ROI'] = $arr['yxtx'] ? sprintf('%.2f',$arr['yxtx']/$arr['wxpay']) : '';
		$returnData[] = $arr;

		$return['sql1'] = $sql1;
		$return['sql2'] = $sql2;
		$return['sql3'] = $sql3;
		$return['sql4'] = $sql4;
		$return['sql5'] = $sql5;
		$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)RECEIVE_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));
		$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);
	}
	
}