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

class Ds_datastatistics extends Report {

	private $level= 3;
	private $type = 1;
	private $code = 'Ds_datastatistics';

	public function getLevel(){
		return $this->level;
	}
	public function getType(){
		return $this->type;
	}
	public function getCode(){
		return $this->code;
	}

	public function settitle(){
		$param = input();

		$table_name = strtolower(input('param.id'));
		$searchType = $param['searchType'];

		$tabletitle = db('s_tabletitle_set')
			->where('table_name', $table_name)
			->where('searchType', $searchType)
			->where('shop_code', session('shop_code'))
			->find();
		// echo db()->getLastSql();
		// var_dump($tabletitle);
		// exit;
		$titles = $param['titles'];
		$head = '';
		foreach ($titles as $key => $value) {
			$head .= $value.',';
		}
		$head = trim($head, ',');

		// 如果存在，则 update
		if ($tabletitle) {
			$tid = $tabletitle['id'];
			$tData['titles'] = $head;
			$tData['update_time'] = time();
			$res = db('s_tabletitle_set')->where('id', $tid)->update($tData);
		}
		// 如果不存在 insert
		else{
			$tData['table_name'] = $table_name;
			$tData['searchType'] = $searchType;
			$tData['titles']     = $head;
			$tData['shop_code']  = session('shop_code');
			$tData['brandclass_id']= session('brandclass_id');
			$tData['create_time']= time();
			$res = db('s_tabletitle_set')->insert($tData);
		}

		if ($res === false) {
			return json(array('code' => 100, 'msg' => '设置表头失败', 'data' => db()->getLastSql() ));
		}else{
			return json(array('code' => 0, 'msg' => '设置表头成功'));
		}
	}

	public function data(){
		$param = input();
		$shop_code = session('shop_code');
		// 查询维度 1：渠道； 2：地域；
		$searchType= (int)$param['searchType'];
		// 开始结束时间，如果没有，则为当天
		if (!$param['start']) {
			$start = date('Y-m-d 00:00:00', time());
		}else{
			$start = date('Y-m-d 00:00:00', strtotime($param['start']));
		}
		if (!$param['end']) {
			$end = date('Y-m-d 23:59:59', time());
		}else{
			$end = date('Y-m-d 23:59:59', strtotime($param['end']));
		}

		$getM['start'] = $start;
		$getM['end']   = $end;
		// $months = getMonths($getM);

		// 基础查询条件
		$baseWhere['c_from']        = 1;
		$baseWhere['is_status']     = 1;
		$baseWhere['customer_type'] = 1;
		$baseWhere['add_shop_code'] = $shop_code;

		// 消费类型
		// $where['consultation_type_id'] = HS_TYPE;

		// 省市
		if ($param['province']) {
			$baseWhere['province_id'] = (int)$param['province'];
		}
		if ($param['city']) {
			$baseWhere['city_id'] = (int)$param['city'];
		}

		$table_name = strtolower(input('param.id'));
		$tabletitle = db('s_tabletitle_set')
			->where('table_name', $table_name)
			->where('searchType', $searchType)
			->where('shop_code', session('shop_code'))
			->find();

		$head = [];
		if ($tabletitle) {
			$head = explode(',', $tabletitle['titles']);
		}else{
			if ($searchType == 1) {
				$head = ["渠道","渠道名称","花费","毛客资","婚纱有效","总手机","婚纱进店","婚纱新单","营收","套系均值","有效率","进店率","成交率","转化率","毛客资成本","有效成本","新单成本","ROI(营收:花费)"];
			}else{
				$head = ["省","市","花费","毛客资","婚纱有效","总手机","婚纱进店","婚纱新单","营收","套系均值","有效率","进店率","成交率","转化率","毛客资成本","有效成本","新单成本","ROI(营收:花费)"];
			}
		}
		// var_dump($head);exit;
		// 根据渠道
		if ($searchType == 1) {
			// 来源渠道 及花费
			$startD= date('Y-m-d', strtotime($start));
			$endD  = date('Y-m-d', strtotime($end));
			// echo microtime(true).'<br />';
			$orSql = "c.cost_date BETWEEN '".$startD."' AND '".$endD."' OR c.cost_date IS NULL ";
	        $channelCost = db('s_channel')->alias('s')
	        	->join('s_channel_cost c', 's.id = c.channel_id', 'LEFT')
	        	->where('s.shop_code', $shop_code)
	        	->where($orSql)
	        	->group('s.id')
	        	->order('s.order_sort')
	        	->column('s.channel_info_id,s.channel_info_name,s.id,s.channel_name,SUM(c.cost) AS cost','s.id');
	        // echo microtime(true).'<br />';
	        // 根据来源渠道， 统计 毛客资， 总手机， 有效客资， 进店， 新单
	        $baseSql = "customer_channel_id,customer_channel,";
	        $baseSql.= "SUM(dj_day BETWEEN '".$start."' AND '".$end."') AS mkz,";
	        $baseSql.= "SUM(customer_tel IS NULL AND dj_day BETWEEN '".$start."' AND '".$end."') AS zsj,";
	        $baseSql.= "SUM(is_valid=1 AND consultation_type_id=".HS_TYPE." AND yx_day BETWEEN '".$start."' AND '".$end."') AS yx,";
	        $baseSql.= "SUM(is_intostore<>1 AND consultation_type_id=".HS_TYPE." AND syjd_day BETWEEN '".$start."' AND '".$end."') AS jd,";
	        $baseSql.= "SUM(is_success=1 AND consultation_type_id=".HS_TYPE." AND order_day BETWEEN '".$start."' AND '".$end."') AS xd";
			$baseData = db('c_customerinfo')
				->where($baseWhere)
				->group('customer_channel_id')
				->column($baseSql, 'customer_channel_id');
			// echo microtime(true).'<br />';
			// 查营收
			$allYs = db('c_customerinfo')->alias('c')
				->join('c_order_detail o', 'o.order_sn = c.order_number')
				->where($baseWhere)
				->where('o.pay_time', 'between', [$start, $end])
				->group('c.customer_channel_id')
				->column('c.customer_channel,SUM(o.order_fact) AS ys', 'c.customer_channel_id');
			// echo microtime(true).'<br />';
			// 查套系
			$allTotal = db('c_customerinfo')
				->where($baseWhere)
				->where('consultation_type_id', HS_TYPE)
				->where('is_success', 1)
				->where('order_day','between',[$start, $end])
				->group('customer_channel_id')
				->column('customer_channel,SUM(totalpay_money) AS total', 'customer_channel_id');
			// echo microtime(true).'<br />';
			// 循环渠道花费 填充 baseData 营收 套系
			foreach ($channelCost as $key => $value) {
				$channelCost[$key]['mkz'] = $baseData[$key]['mkz'];
				$channelCost[$key]['zsj'] = $baseData[$key]['zsj'];
				$channelCost[$key]['yx']  = $baseData[$key]['yx'];
				$channelCost[$key]['jd']  = $baseData[$key]['jd'];
				$channelCost[$key]['xd']  = $baseData[$key]['xd'];
				$channelCost[$key]['ys']  = $allYs[$key]['ys'];
				$channelCost[$key]['txjz']= $allTotal[$key]['total'];
			}
			var_dump($channelCost);exit;
		}
		// 根据地域
		else{
			// 615  孙宏宇
		}
		// exit;
		// $allData = [];
		// return json(array('code' => 0, 'msg' => '', 'count' => count($baseStaff), 'data' => $baseStaff));
	}
	public function show(){
		
		$html = strtolower(input('param.id'));
		$display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.$html.'.html';

		$code = $this->getCode();

		// crm省    列表
		$data['type'] = 1;
		$province = controller("Customer", "lib")->address($data);
		$this->assign('province', $province);

		//来源渠道
		// $channel = controller("Customer", "lib")->getChannel();
		// $this->assign("channel", $channel);

		$title = db('s_report_form')->where('code', $code)->value('report_name');
		$this->assign('title', session('department_name').'-'.$title);

		$this->assign('shop_code', session('shop_code'));

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

}