hytc-admin/addons/qingdongams/controller/Statistics.php

<?php

namespace addons\qingdongams\controller;

use addons\qingdongams\model\Achievement;
use addons\qingdongams\model\AreaManagement;
use addons\qingdongams\model\ConsumeDetail;
use addons\qingdongams\model\ContractRatio;
use addons\qingdongams\model\Field;
use addons\qingdongams\model\FormApproval;
use addons\qingdongams\model\PartsStockReload;
use addons\qingdongams\model\Receivables;
use addons\qingdongams\model\Contract;
use addons\qingdongams\model\Event;
use addons\qingdongams\model\Leads;
use addons\qingdongams\model\Customer;
use addons\qingdongams\model\ReceivablesPlan;
use addons\qingdongams\model\Record;
use addons\qingdongams\model\SeaOperation;
use addons\qingdongams\model\Staff;
use addons\qingdongams\model\Workorder;
use addons\qingdongams\model\StaffSignIn;
use addons\qingdongams\model\Product;
use addons\qingdongams\model\Business;
use think\Exception;

/**
 * 统计接口
 */
class Statistics extends StaffApi {
	protected $noNeedLogin = [];
	protected $noNeedRight = [];

    public function _initialize()
    {
        parent::_initialize();
        try{
            \think\Db::execute("SET @@sql_mode='';");
        }catch (Exception $e){

        }
    }

	/**
	 *成交排行
	 */
	public function contractRanding() {
		$date = input('date', date('Y-m'));
		$type = input('type', 0);//0 本人及下属 1 仅本人 2 仅下属 4公司


		//月底
		$endDate = date('Y-m-d', strtotime('+1 month', strtotime(date($date . '-1'))) - 1);

		$contracts = Contract::where([
			'order_date'   => ['between', [$date . '-1', $endDate]],
			'check_status' => 2,
		])->group('owner_staff_id')->field('owner_staff_id,sum(money) as money')->order('money desc')->select();
		$list      = [];
		foreach ($contracts as $v) {
			$list[$v['owner_staff_id']] = $v['money'];
		}
		$contracts = $list;
		$data      = [];
		$staffs    = Staff::getList();
		foreach ($staffs as $v) {
			if (isset($contracts[$v['id']])) {
				$data[$v['id']] = $contracts[$v['id']];
			} else {
				$data[$v['id']] = 0;
			}
		}
		arsort($data);
		$staffs   = Staff::getKeyList();
		$result   = [];
		$i        = 1;
		$oneMoney = 0;
		if ($type == 1) {//本人
			$showStaffIds = [$this->auth->id];
		} elseif ($type == 2) {//下属
			$showStaffIds = Staff::getLowerStaffId();
		} elseif ($type == 3) {//公司
			$showStaffIds = Staff::getLowerStaffId();
		} else {//全部
			$showStaffIds = Staff::getMyStaffIds();
		}


		foreach ($data as $id => $money) {
			if ($i == 1) {
				$oneMoney = $money;
			}
			$val          = $staffs[$id];
			$val['money'] = $money;
			$val['ratio'] = ($money && $oneMoney) ? sprintf("%.2f", $money / $oneMoney * 100) : 0;
			$val['rank']  = $i;
			$i++;
			if(in_array($id,$showStaffIds)){
				$result[]     = $val;
			}
		}
		if (count($result) >= 10) {

			$top    = array_slice($result, 0, 3);
			$bottom = array_slice($result, -3, 3);
			$middle = array_slice($result, 3, 4);
			$result = array_merge($top, $bottom, $middle);
		}

		if ($type == 1) {//本人
			$showStaffIds = [$this->auth->id];
			//业绩目标完成情况
			$achievement = Achievement::getStaffAchievement($date);
		} elseif ($type == 2) {//下属
			$showStaffIds = Staff::getLowerStaffId();
			//业绩目标完成情况
			$achievement = Achievement::getTeamAchievement($date);
		} elseif ($type == 3) {//公司
			$showStaffIds = Staff::getLowerStaffId();
			//业绩目标完成情况
			$achievement = Achievement::getCompanyAchievement($date);
		} else {//全部
			$showStaffIds = Staff::getMyStaffIds();
			//业绩目标完成情况
			$achievement = Achievement::getTeamAchievement($date);
		}

		$contractMoneys = Contract::where([
			'order_date'     => ['between', [$date . '-1', $endDate]],
			'check_status'   => 2,
			'owner_staff_id' => ['in',$showStaffIds],
		])->sum('money');

		$ratio = ($contractMoneys && intval($achievement)) ? round($contractMoneys / $achievement * 100, 2) : 0;
		$data  = [
			'achievement'       => $achievement,
			'contract_moneys'   => $contractMoneys,
			'completion_status' => ($ratio >= 100) ? 1 : 0,//完成状态
			'ratio'             => $ratio,
			'ranking'           => $result
		];

		$this->success('请求成功', $data);
	}


	//合同排行 机型排行
	public function contractRanking() {

		$date = input('date', date('Y-m'));
		$type = input('type', 0);//0 本人及下属 1 仅本人 2 仅下属
		//月底
		$endDate = date('Y-m-d', strtotime('+1 month', strtotime($date . '-1')) - 1);

		$contracts = Contract::where([
			'order_date'   => ['between', [$date . '-1', $endDate]],
			'check_status' => 2,
		])->group('owner_staff_id')->field('owner_staff_id,count(*) as contract_number')->order('contract_number desc')->select();
		$list      = [];
		foreach ($contracts as $v) {
			$list[$v['owner_staff_id']] = $v['contract_number'];
		}
		$contracts = $list;

		$data   = [];
		$staffs = Staff::getList();
		foreach ($staffs as $v) {
			if (isset($contracts[$v['id']])) {
				$data[$v['id']] = $contracts[$v['id']];
			} else {
				$data[$v['id']] = 0;
			}
		}
		arsort($data);
		$staffs    = Staff::getKeyList();
		$result    = [];
		$i         = 1;
		$oneNumber = 0;
		if ($type == 1) {//本人
			$showStaffIds = [$this->auth->id];
		} elseif ($type == 2) {//下属
			$showStaffIds = Staff::getLowerStaffId();
		} else {//全部
			$showStaffIds = Staff::getMyStaffIds();
		}
		foreach ($data as $id => $number) {
			if ($i == 1) {
				$oneNumber = $number;
			}
			$val           = $staffs[$id];
			$val['number'] = $number;
			$val['ratio']  = $oneNumber ? sprintf("%.2f", $number / $oneNumber * 100) : 0;
			$val['rank']   = $i;
			$i++;
			if(in_array($id,$showStaffIds)){
				$result[]     = $val;
			}
		}
		if (count($result) >= 10) {

			$top    = array_slice($result, 0, 3);
			$bottom = array_slice($result, -3, 3);
			$middle = array_slice($result, 3, 4);
			$result = array_merge($top, $bottom, $middle);
		}

		$this->success('请求成功', $result);
	}


	//回款统计
	public function receivablesStatistics() {

		$contract_moneys    = Contract::where(['owner_staff_id' => $this->auth->id, 'check_status' => 2])->sum('money');
		$receivables_moneys = Receivables::where([
			'owner_staff_id' => $this->auth->id,
			'check_status'   => 2,
		])->sum('money');
		$plan_moneys        = ReceivablesPlan::where([
			'owner_staff_id' => $this->auth->id,
			'status'         => 0,
		])->sum('money');
		$no_moneys          = $contract_moneys - $receivables_moneys;
		$startDate          = date('Y-m', strtotime($this->auth->createtime));
		$endDate            = date('Y-m');

		$plans      = ReceivablesPlan::where([
			'owner_staff_id' => $this->auth->id,

		])->group('dates')->field('id,sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(return_date),"%Y-%m") as dates')->select();
		$plans_list = [];
		foreach ($plans as $item) {
			$plans_list[$item['dates']] = $item['money'];
		}
		$receivables      = Receivables::where([
			'owner_staff_id' => $this->auth->id,

		])->group('dates')->field('id,sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(return_time),"%Y-%m") as dates')->select();
		$receivables_list = [];
		foreach ($receivables as $item) {
			$receivables_list[$item['dates']] = $item['money'];
		}
		$data = [];
		for ($startDate; strtotime($startDate) <= strtotime($endDate); $startDate = date('Y-m', strtotime($startDate . ' +1 month'))) {
			$row       = [
				'date'        => $startDate,
				'plan'        => $plans_list[$startDate] ?? 0,
				'receivables' => $receivables_list[$startDate] ?? 0,
			];
			$row['no'] = ($row['plan'] - $row['receivables']) > 0 ? $row['plan'] - $row['receivables'] : 0;
			$data[]    = $row;
		}

		$this->success('请求成功', [
			'contract_moneys'    => $contract_moneys,
			'receivables_moneys' => $receivables_moneys,
			'plan_moneys'        => $plan_moneys,
			'no_moneys'          => $no_moneys,
			'data'               => $data
		]);
	}


	//所属员工回款统计
	public function staffReceivablesStatistics() {
		$ids = Staff::getMyStaffIds();


		$contract_moneys    = Contract::where([
			'owner_staff_id' => ['in', $ids],
			'check_status'   => 2,

		])->sum('money');
		$receivables_moneys = Receivables::where([
			'owner_staff_id' => ['in', $ids],
			'check_status'   => 2,

		])->sum('money');
		$plan_moneys        = ReceivablesPlan::where([
			'owner_staff_id' => ['in', $ids],
			'status'         => 0,

		])->sum('money');
		$no_moneys          = $contract_moneys - $receivables_moneys;
		//$startDate          = date('Y-m', $this->auth->createtime);
		//$endDate            = date('Y-m');

		$contracts      = Contract::where([
			'owner_staff_id' => [
				'in',
				$ids
			],
			'check_status'   => 2,

		])->group('owner_staff_id')->field('id,sum(money) as money,owner_staff_id')->select();
		$contracts_list = [];
		foreach ($contracts as $item) {
			$contracts_list[$item['owner_staff_id']] = $item['money'];
		}

		$plans      = ReceivablesPlan::where([
			'owner_staff_id' => [
				'in',
				$ids
			],
			'status'         => 0,

		])->group('owner_staff_id')->field('id,sum(money) as money,owner_staff_id')->select();
		$plans_list = [];
		foreach ($plans as $item) {
			$plans_list[$item['owner_staff_id']] = $item['money'];
		}

		$receivables      = Receivables::where([
			'owner_staff_id' => [
				'in',
				$ids
			],
			'check_status'   => 2,

		])->group('owner_staff_id')->field('id,sum(money) as money,owner_staff_id')->select();
		$receivables_list = [];
		foreach ($receivables as $item) {
			$receivables_list[$item['owner_staff_id']] = $item['money'];
		}
		$data   = [];
		$ids    = Staff::getMyStaffIds();
		$staffs = Staff::where(['id' => ['in', $ids],'status'=>1])->field('id,name')->select();
		foreach ($staffs as $v) {
			$row       = [
				'id'          => $v['id'],
				'name'        => $v['name'],
				'contracts'        => $contracts_list[$v['id']] ?? 0,
				'plan'        => $plans_list[$v['id']] ?? 0,
				'receivables' => $receivables_list[$v['id']] ?? 0,
			];
			$row['no'] = ($row['contracts'] - $row['receivables']) > 0 ? $row['contracts'] - $row['receivables'] : 0;
			$data[]    = $row;
		}
		$this->success('请求成功', [
			'contract_moneys'    => $contract_moneys,
			'receivables_moneys' => $receivables_moneys,
			'plan_moneys'        => $plan_moneys,
			'no_moneys'          => $no_moneys,
			'data'               => $data
		]);
	}


	//我的业绩目标完成度
	public function staffAchievementStatistics() {
		$year = input('year', date('Y'));
		$type = input('type', 0);
		if ($type == 1) {//本人
			$showStaffIds = [$this->auth->id];
			$yearAchievement = Achievement::getStaffYearAchievement($year);
		} elseif ($type == 2) {//下属
			$showStaffIds = Staff::getLowerStaffId();
			$yearAchievement = Achievement::getTeamYearAchievement($year);
		} elseif ($type == 3) {//公司
			$showStaffIds = Staff::getLowerStaffId();
			$yearAchievement = Achievement::getCompanyYearAchievement($year);
		} else {//全部
			$showStaffIds = Staff::getMyStaffIds();
			$yearAchievement = Achievement::getTeamYearAchievement($year);
		}


		$contracts       = Contract::where([
			'owner_staff_id' => ['in',$showStaffIds],
			'check_status'   => 2,
			'order_date'     => ['like', $year . '%'],

		])->group('month')->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(order_date),"%Y-%m") as month,count(*) as count')->select();
		$contracts_list  = [];
		foreach ($contracts as $v) {
			$contracts_list[$v['month']] = $v;
		}

        $data = [];
		foreach ($yearAchievement as $k => $v) {
			if ($month = Achievement::getFieldMonth($k)) {
				$month             = $year . '-' . $month;
				$row               = [
					'month'       => $month,
					'achievement' => $v,
					'money'       => isset($contracts_list[$month]) ? $contracts_list[$month]['money'] : 0,
					'count'       => isset($contracts_list[$month]) ? $contracts_list[$month]['count'] : 0,
				];
				$row['ratio']      = ($row['money'] && intval($row['achievement'])) ? sprintf("%.2f", $row['money'] / $row['achievement'] * 100) : 0;
				$row['unit_price'] = ($row['money'] && $row['count']) ? sprintf("%.2f", $row['money'] / $row['count']) : 0;

				$data[] = $row;
			}
		}

		$this->success('请求成功', $data);
	}


	//回款数据排行
	public function receivablesRanking() {
		$year = input('year', date('Y'));
		$type = input('type', 0);
		if ($type == 1) {//本人
			$showStaffIds = [$this->auth->id];
		} elseif ($type == 2) {//下属
			$showStaffIds = Staff::getLowerStaffId();
		} else {//全部
			$showStaffIds = Staff::getMyStaffIds();
		}

		$receivables = Receivables::where([
			'check_status' => 2,
			'return_time'  => ['like', $year . '%']
		])->group('owner_staff_id')->with(['staff'])->order('money desc')->field('owner_staff_id,sum(money) as money,count(*) as count')->select();

		$receivablesData = [];
		$list            = [];
		foreach ($receivables as $k => $v) {
			$v['ranking']               = $k + 1;
			if(in_array($v['owner_staff_id'],$showStaffIds)){
				$receivablesData[]          = $v;
			}
			$list[$v['owner_staff_id']] = $v['money'];
		}

		$contracts = $list;

		$data   = [];
		$staffs = Staff::getList();
		foreach ($staffs as $v) {
			if (isset($contracts[$v['id']])) {
				$data[$v['id']] = $contracts[$v['id']];
			} else {
				$data[$v['id']] = 0;
			}
		}
		arsort($data);
		$staffs    = Staff::getKeyList();
		$result    = [];
		$i         = 1;
		$oneNumber = 0;
		foreach ($data as $id => $number) {
			if ($i == 1) {
				$oneNumber = $number;
			}
			$val           = $staffs[$id];
			$val['number'] = $number;
			$val['ratio']  = $oneNumber ? sprintf("%.2f", $number / $oneNumber * 100) : 0;
			$val['rank']   = $i;
			$i++;
			if(in_array($id,$showStaffIds)){
				$result[]     = $val;
			}
		}

		if (count($result) >= 10) {

			$top    = array_slice($result, 0, 3);
			$bottom = array_slice($result, -3, 3);
			$middle = array_slice($result, 3, 4);
			$result = array_merge($top, $bottom, $middle);
		}


		$this->success('请求成功', ['ranking' => $result, 'data' => $receivablesData]);
	}


	//成交数据排行
	public function contractMoneyRanking() {
		$date = input('year', date('Y'));
		$type = input('type', 0);
		if ($type == 1) {//本人
			$showStaffIds = [$this->auth->id];
		} elseif ($type == 2) {//下属
			$showStaffIds = Staff::getLowerStaffId();
		} else {//全部
			$showStaffIds = Staff::getMyStaffIds();
		}

		$receivables = Contract::where([
			'check_status' => 2,
			'order_date'   => ['like', $date . '%']
		])->group('owner_staff_id')->with(['staff'])->order('money desc')->field('owner_staff_id,sum(money) as money,count(*) as count')->select();

		$receivablesData = [];
		$list            = [];
		foreach ($receivables as $k => $v) {
			$v['ranking']               = $k + 1;
			$v['unit_price']            = ($v['money'] && $v['count']) ? sprintf("%.2f", $v['money'] / $v['count']) : 0;
			if(in_array($v['owner_staff_id'],$showStaffIds)){
				$receivablesData[]          = $v;
			}
			$list[$v['owner_staff_id']] = $v['money'];
		}

		$contracts = $list;

		$data   = [];
		$staffs = Staff::getList();
		foreach ($staffs as $v) {
			if (isset($contracts[$v['id']])) {
				$data[$v['id']] = $contracts[$v['id']];
			} else {
				$data[$v['id']] = 0;
			}
		}
		arsort($data);
		$staffs    = Staff::getKeyList();
		$result    = [];
		$i         = 1;
		$oneNumber = 0;
		foreach ($data as $id => $number) {
			if ($i == 1) {
				$oneNumber = $number;
			}
			$val           = $staffs[$id];
			$val['number'] = $number;
			$val['ratio']  = $oneNumber ? sprintf("%.2f", $number / $oneNumber * 100) : 0;
			$val['rank']   = $i;
			$i++;
			if(in_array($id,$showStaffIds)){
				$result[]     = $val;
			}
		}

		if (count($result) >= 10) {

			$top    = array_slice($result, 0, 3);
			$bottom = array_slice($result, -3, 3);
			$middle = array_slice($result, 3, 4);
			$result = array_merge($top, $bottom, $middle);
		}


		$this->success('请求成功', ['ranking' => $result, 'data' => $receivablesData]);
	}


	//新增排行
	public function newRanking() {
		$year      = input('date', date('Y'));
		$between   = [strtotime($year . '-01-01'), strtotime($year.'-1-1 +1 year')];
        $betweenC   = [date('Y-m-d 00:00:00',strtotime($year . '-01-01')), date('Y-m-d 23:59:59',strtotime($year.'-1-1 +1 year'))];
        $customers = Customer::where([
			'createtime' => [
				'between',
				$between
			],

			'create_staff_id' => $this->auth->id
		])->group('month')->field("id,FROM_UNIXTIME(createtime,'%Y-%m') as month,count(*) as count")->select();

		$customers_list = [];
		foreach ($customers as $v) {
			$customers_list[$v['month']] = $v['count'];
		}
		$contracts      = Contract::where([
			'order_date' => [
				'between',
                $betweenC
			],

			'create_staff_id' => $this->auth->id
		])->group('month')->field('id,FROM_UNIXTIME(createtime,"%Y-%m") as month,count(*) as count')->select();

		$contracts_list = [];
		foreach ($contracts as $v) {
			$contracts_list[$v['month']] = $v['count'];
		}
		$leads      = Leads::where([
			'createtime' => [
				'between',
				$between
			],

			'create_staff_id' => $this->auth->id
		])->group('month')->field('id,FROM_UNIXTIME(createtime,"%Y-%m") as month,count(*) as count')->select();
		$leads_list = [];
		foreach ($leads as $v) {
			$leads_list[$v['month']] = $v['count'];
		}
		$data = [];
		for ($i = 1; $i <= 12; $i++) {
			$month  = date('Y-m', strtotime($year . '-' . $i));
			$data[] = [
				'month'     => $month,
				'customers' => $customers_list[$month] ?? 0,
				'contracts' => $contracts_list[$month] ?? 0,
				'leads'     => $leads_list[$month] ?? 0,
			];
		}

		$this->success('请求成功', $data);
	}


	//获取团队新增统计
	public function addCustomerStatistics() {
		$date = input('date', date('Y-m'));
		if(strlen($date) == 4){
			$between = [strtotime($date . '-01-01'), strtotime($date.'-1-1 +1 year') - 1];
            $betweenC = [date('Y-m-d 00:00:00',strtotime($date . '-01-01')), date('Y-m-d 23:59:59',strtotime($date.'-1-1 +1 year') - 1)];
		}else{
			$between = [strtotime($date), strtotime('+1 month', strtotime($date)) - 1];
            $betweenC = [date('Y-m-d 00:00:00',strtotime($date)), date('Y-m-d 23:59:59',strtotime('+1 month', strtotime($date)) - 1)];
		}


		$ids     = Staff::getMyStaffIds();
		$staffs  = Staff::where(['id' => ['in', $ids]])->field('id,name,post,img')->select();

		$c         = Customer::where([
			'createtime'      => [
				'between',
				$between
			],

			'create_staff_id' => ['in', $ids]
		])->group('create_staff_id')->order('count desc')->field("id,create_staff_id,count(*) as count")->select();
		$customers = [];
		foreach ($c as $v) {
			$customers[$v['create_staff_id']] = $v['count'];
		}
		$l     = Leads::where([
			'createtime'      => [
				'between',
				$between
			],

			'create_staff_id' => ['in', $ids]
		])->group('create_staff_id')->field("id,create_staff_id,count(*) as count")->select();
		$leads = [];
		foreach ($l as $v) {
			$leads[$v['create_staff_id']] = $v['count'];
		}
		$t         = Contract::where([
			'order_date'      => [
				'between',
                $betweenC
			],

			'create_staff_id' => ['in', $ids]
		])->group('create_staff_id')->field("id,create_staff_id,count(*) as count")->select();
		$contracts = [];
		foreach ($t as $v) {
			$contracts[$v['create_staff_id']] = $v['count'];
		}
		$total=[];
		$total[0]=[
			'name'=>'数据汇总',
			'id'=>0,
			'leads'=>0,
			'customers'=>0,
			'contracts'=>0,
		];
		$sort=[];
		foreach ($staffs as $k => $v) {

			$v['leads']     = $leads[$v['id']] ?? 0;
			$v['customers'] = $customers[$v['id']] ?? 0;
			$v['contracts'] = $contracts[$v['id']] ?? 0;
			$sort[$v['id']] = $v['leads'] + $v['customers'] + $v['contracts'];
			$total[0]['leads'] += $v['leads'];
			$total[0]['customers'] += $v['customers'];
			$total[0]['contracts'] += $v['contracts'];
			$staffs[$v['id']]     = $v;
		}
		arsort($sort);
		$result=[];
		foreach ($sort as $id=>$v){
			$result[]=$staffs[$id];
		}
		$staffs=array_merge($total,$result);
		$this->success('请求成功',$staffs);
	}


	//客户分析-客户数分析
    public function getCustomerCount()
    {
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'time');
        $where = [];
        $where['createtime'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getOneDepartmentStaffList($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }

        $result = Customer::where($where)
            ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time')
            ->group('time')->select();


        $trade_result = Customer::where($where)->where(['contract_status'=>1])
            ->field('count(*) as count,trade_date')
            ->group('trade_date')->select();

        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['time']] = $v['count'];
        }

        $tradeCount = [];
        foreach ($trade_result as $v) {
            $tradeCount[$v['trade_date']] = $v['count'];
        }
        $dateList = getDateList($times[0], $times[1]);

        $result=[];
        foreach ($dateList as $date) {
            $result['date'][]=$date;
            $result['add_count'][]=$addCount[$date] ?? 0;
            $result['trade_count'][]=$tradeCount[$date] ?? 0;

        }

        $this->success('请求成功', $result);
    }


	//客户分析-老客跟进分析
    public function getCustomerSignIn()
    {
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'time');
        $where = [];
        $where['createtime'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getDepartmentStaffIds($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }

        $result = StaffSignIn::where($where)
            ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time')
            ->group('time,customer_id')->select();

        $trade_result = StaffSignIn::where($where)->where(['customer_status'=>1])//成交
            ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time')
            ->group('time,customer_id')->select();

        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['time']] = $v['count'];
        }

        $tradeCount = [];
        foreach ($trade_result as $v) {
            $tradeCount[$v['time']] = $v['count'];
        }
        $dateList = getDateList($times[0], $times[1]);

        $result=[];
        foreach ($dateList as $date) {
            $result['date'][]=$date;
            $result['count'][]=$addCount[$date] ?? 0;
            $result['trade_count'][]=$tradeCount[$date] ?? 0;

        }

        $this->success('请求成功', $result);
    }

	//客户分析-客户转化率分析
    public function getCustomerTradeRate()
    {
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'time');
        $where = [];
        $where['createtime'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getDepartmentStaffIds($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }

        $result = Customer::where($where)->count();
        //成交
        $trade_result = Customer::where($where)->where(['contract_status' => 1])->count();

        $rate = getRatio($trade_result, $result);
        $this->success('请求成功', ['addCount' => $result, 'tradeCount' => $trade_result, 'rate' => $rate]);
    }

	//客户分析-客户售后满意度分析
    public function getWorkorderSatisfied()
    {
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'time');
        $where = [];
        $where['createtime'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getDepartmentStaffIds($department_id);
            $where['owner_staff_id'] = ['in', $l_ids];//完成人
        }
        if ($staff_id) {
            $where['owner_staff_id'] = $staff_id;//完成人
        }

        $result = Workorder::where($where)->where(['status'=>3])->count();
        //成交
        $satisfied_result = Workorder::where($where)->where(['status' => 3,'comment_score'=>['in',[3,4,5]]])->count();

        $rate = getRatio($satisfied_result, $result);
        $this->success('请求成功', ['addCount' => $result, 'satisfiedCount' => $satisfied_result, 'rate' => $rate]);
    }


	//客户分析-客户成交周期分析
    public function getCustomerTrade()
    {
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'date');
        $where = [];
        $where['trade_date'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getDepartmentStaffIds($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }

        $result = Customer::where($where)->where(['contract_status'=>1])//成交
            ->field('count(*) as count,trade_date')
            ->group('trade_date')->select();

        $date_result = Customer::where($where)->where(['contract_status' => 1])//成交
        ->field('trade_date,createtime')->select();
        $data=[];
        foreach ($date_result as $v){
            $data[$v['trade_date']][]=intval((strtotime($v['trade_date'])-strtotime($v['createtime']))/86400);
        }
        foreach ($data as $k=>$v) {
            $data[$k] = $v ? intval(array_sum($v) / count($v)) : [];
        }
        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['trade_date']] = $v['count'];
        }

        $dateList = getDateList(strtotime($times[0]), strtotime($times[1])+86400-1);

        $result=[];
        foreach ($dateList as $date) {
            $result['date'][]=$date;
            $result['count'][]=$addCount[$date] ?? 0;
            $result['number'][]=$data[$date] ?? 0;

        }

        $this->success('请求成功', $result);
    }

    //售后分析-工单数分析
    public function getWorkorderCount(){
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        $workorder_type = input('workorder_type');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'time');
        $where = [];
        $where['createtime'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getOneDepartmentStaffList($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }
        if ($workorder_type) {
            $where['workorder_type'] = $workorder_type;
        }

        $result = Workorder::where($where)//新增工单
        ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time')
            ->group('time')->select();
        unset($where['createtime']);
        $where['end_time'] = ['between', $times];
        $satisfied_result = Workorder::where($where)->where(['status' => 3])//完成工单
        ->field('count(*) as count,FROM_UNIXTIME(end_time,"%Y-%m-%d") as time')->select();

        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['time']] = $v['count'];
        }
        $satisfiedCount = [];
        foreach ($satisfied_result as $v) {
            $satisfiedCount[$v['time']] = $v['count'];
        }

        $dateList = getDateList($times[0], $times[1]);


        $result=[];
        foreach ($dateList as $date) {
            $result['date'][]=$date;
            $result['count'][]=$addCount[$date] ?? 0;
            $result['satisfied'][]=$satisfiedCount[$date] ?? 0;

        }


        $this->success('请求成功', $result);
    }

    //售后分析-工单金额分析
    public function getWorkorderMoney(){
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        $workorder_type = input('workorder_type');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'time');
        $where = [];
        $where['end_time'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getDepartmentStaffIds($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }
        if ($workorder_type) {
            $where['workorder_type'] = $workorder_type;
        }
        $result = Workorder::where($where)->where(['status' => 3])//完成工单
        ->field('count(*) as count,FROM_UNIXTIME(end_time,"%Y-%m-%d") as time')->select();
        $money_result = Workorder::where($where)->where(['status' => 3])//工单金额
        ->field('sum(money) as money,FROM_UNIXTIME(end_time,"%Y-%m-%d") as time')->select();

        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['time']] = $v['count'];
        }
        $moneyCount = [];
        foreach ($money_result as $v) {
            $moneyCount[$v['time']] = $v['money'];
        }

        $dateList = getDateList($times[0], $times[1]);

        $result=[];
        foreach ($dateList as $date) {
            $result['date'][]=$date;
            $result['count'][]=$addCount[$date] ?? 0;
            $result['money'][]=$moneyCount[$date] ?? 0;

        }

        $this->success('请求成功', $result);
    }

    //销售分析-销售漏斗分析
    public function getStaffCustomer()
    {
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'time');
        $where = [];
        $where['createtime'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getOneGroupStaffIds($department_id);
            $where['owner_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['owner_staff_id'] = $staff_id;
        }
        $status=Field::where(['type'=>'跟进状态'])->column('field');
        $result = Customer::where($where)//成交
        ->field('count(*) as count,follow')
            ->group('follow')->select();

        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['follow']] = $v['count'];
        }
        $customerList = [];
        foreach ($status as $follow) {
            $customerList[] = [
                'name' => $follow,
                'value' => $addCount[$follow] ?? 0,
            ];
        }

        $this->success('请求成功', $customerList);
    }

    //产品分析-产品销售分析
    public function getProductCount(){
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'date');
        $where = [];
        $where['start_time'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getOneDepartmentStaffList($department_id);
            $where['owner_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['owner_staff_id'] = $staff_id;
        }
        $result=Contract::where($where)->field('count(*) as count,start_time')->group('start_time')->select();

        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['start_time']] = $v['count'];
        }
        $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));

        $result=[];
        foreach ($dateList as $date) {
            $result['date'][]=$date;
            $result['count'][]=$addCount[$date] ?? 0;

        }

        $this->success('请求成功', $result);
    }

    //产品分析-部件问题分析
    public function getProductParts()
    {
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'time');
        $where = [];
        $where['w.createtime'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getOneDepartmentStaffList($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }

        $result = Workorder::where($where)->alias('w')->where(['workorder_type'=>'上门维修'])
            ->join('qingdongams_customer_product c', 'c.id=w.customer_product_id')
            ->field('count(*) as count,product_id')->select();
        $result=collection($result)->toArray();
        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['product_id']] = $v['count'];
        }
        $sum=array_sum($addCount);
        $product = Product::where([])->select();
        $result=[];

        foreach ($product as $item) {
            $result['name'][]=$item['name'];
            $result['count'][]=$addCount[$item['id']] ?? 0;
            $result['ratio'][]=getRatio($addCount[$item['id']] ?? 0,$sum);
        }
        $this->success('请求成功', $result?:null);
    }


    //员工业绩分析-合同数分析
    public function getContractCount()
    {
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'date');
        $where = [];
        $where['order_date'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getOneDepartmentStaffList($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }
        $result=Contract::where($where)->field('count(*) as count,order_date')->group('order_date')->select();

        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['order_date']] = $v['count'];
        }

        $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));

        $result=[];

        foreach ($dateList as $date) {
            $result['date'][]=$date;
            $result['count'][]=$addCount[$date] ?? 0;
        }
        $this->success('请求成功', $result);
    }


    //员工业绩分析-合同金额分析
    public function getContractMoney()
    {
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'date');
        $where = [];
        $where['order_date'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getOneDepartmentStaffList($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }
        $result=Contract::where($where)->field('sum(money) as count,order_date')->group('order_date')->select();

        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['order_date']] = $v['count'];
        }

        $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));

        $result=[];

        foreach ($dateList as $date) {
            $result['date'][]=$date;
            $result['count'][]=$addCount[$date] ?? 0;
        }
        $this->success('请求成功', $result);

    }
    //员工业绩分析-回款金额分析
    public function getReceivablesMoney()
    {
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'date');
        $where = [];
        $where['return_time'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getOneDepartmentStaffList($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }
        $result=Receivables::where($where)->field('sum(money) as count,return_time')
            ->group('return_time')->select();

        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['return_time']] = $v['count'];
        }

        $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));

        $result=[];

        foreach ($dateList as $date) {
            $result['date'][]=$date;
            $result['money'][]=intval($addCount[$date] ?? 0);
        }
        $this->success('请求成功', $result);
    }
    //员工业绩分析-合同汇总表
    public function getContractSummary()
    {
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'date');
        $where = [];
        $where['order_date'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getOneDepartmentStaffList($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }
        $result = Contract::where($where)->field('count(*) as count,order_date')
            ->group('order_date')->select();

        $contract_result = Contract::where($where)->field('sum(money) as count,order_date')
            ->group('order_date')->select();
        unset($where['order_date']);
        $where['return_time'] = ['between', $times];
        $receivables_result=Receivables::where($where)->field('sum(money) as count,return_time')
            ->group('return_time')->select();

        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['order_date']] = $v['count'];
        }

        $contractCount = [];
        foreach ($contract_result as $v) {
            $contractCount[$v['order_date']] = $v['count'];
        }

        $receivablesCount = [];
        foreach ($receivables_result as $v) {
            $receivablesCount[$v['return_time']] = $v['count'];
        }

        $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));

        $contractList = [];
        foreach ($dateList as $date) {
            $contractList[] = [
                'date' => $date,
                'count' => $addCount[$date] ?? 0,
                'contract_money' => $contractCount[$date] ?? 0,
                'receivables_money' => $receivablesCount[$date] ?? 0,
            ];
        }
        $this->success('请求成功', $contractList);
    }

    //业绩目标完成分析-合同金额分析
    public function getContractTarget(){
        $year = input('year', date('Y'));
        $team_id = input('team_id');
        $staff_id = input('staff_id');
        if (empty($year)) {
            $this->error('日期不能为空');
        }
        $where = ['check_status'=>2];
        $where['order_date'] = ['like', $year . '%'];
        //默认公司
        $yearAchievement = Achievement::getCompanyYearAchievement($year,Achievement::CONTRACT_TYPE);
        if ($team_id) {//获取团队下成员
            $staff=Staff::where([])->find();
            $l_ids = Staff::getStaffLowerIds($staff);
            $where['owner_staff_id'] = ['in', $l_ids];
            $yearAchievement = Achievement::getTeamYearAchievement($year,Achievement::CONTRACT_TYPE,$team_id);
        }
        if ($staff_id) {
            $where['owner_staff_id'] = $staff_id;
            $yearAchievement = Achievement::getStaffYearAchievement($year,Achievement::CONTRACT_TYPE,$staff_id);
        }
        $contracts       = Contract::where($where)->group('month')
            ->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(order_date),"%Y-%m") as month,count(*) as count')->select();
        $contracts_list  = [];
        foreach ($contracts as $v) {
            $contracts_list[$v['month']] = $v;
        }
        $data = [];


        foreach ($yearAchievement as $k => $v) {
            if ($month = Achievement::getFieldMonth($k)) {
                $month             = $year . '-' . $month;
                $row               = [
                    'month'       => $month,
                    'achievement' => $v,
                    'money'       => isset($contracts_list[$month]) ? $contracts_list[$month]['money'] : 0,
                    'count'       => isset($contracts_list[$month]) ? $contracts_list[$month]['count'] : 0,
                ];
                $row['ratio']      = ($row['money'] && intval($row['achievement'])) ? sprintf("%.2f", $row['money'] / $row['achievement'] * 100) : 0;
                $row['unit_price'] = ($row['money'] && $row['count']) ? sprintf("%.2f", $row['money'] / $row['count']) : 0;

                $data['month'][] = $row['month'];
                $data['achievement'][] = $row['achievement'];
                $data['money'][] = $row['money'];
                $data['ratio'][] = $row['ratio'];
            }
        }

        $this->success('请求成功', $data);
    }

    //业绩目标完成分析-回款金额分析
    public function getReceivablesTarget(){
        $year = input('year', date('Y'));
        $team_id = input('team_id');
        $staff_id = input('staff_id');
        if (empty($year)) {
            $this->error('日期不能为空');
        }
        $where = ['check_status'=>2];
        $where['return_time'] = ['like', $year . '%'];
        //默认公司
        $yearAchievement = Achievement::getCompanyYearAchievement($year,Achievement::RECEIVABLES_TYPE);
        if ($team_id) {//获取团队下成员
            $staff=Staff::where([])->find();
            $l_ids = Staff::getStaffLowerIds($staff);
            $where['owner_staff_id'] = ['in', $l_ids];
            $yearAchievement = Achievement::getTeamYearAchievement($year,Achievement::RECEIVABLES_TYPE,$team_id);
        }
        if ($staff_id) {
            $where['owner_staff_id'] = $staff_id;
            $yearAchievement = Achievement::getStaffYearAchievement($year,Achievement::RECEIVABLES_TYPE,$staff_id);
        }
        $receivables       = Receivables::where($where)->group('month')
            ->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(return_time),"%Y-%m") as month,count(*) as count')->select();
        $receivables_list  = [];
        foreach ($receivables as $v) {
            $receivables_list[$v['month']] = $v;
        }
        $data = [];
        foreach ($yearAchievement as $k => $v) {
            if ($month = Achievement::getFieldMonth($k)) {
                $month             = $year . '-' . $month;
                $row               = [
                    'month'       => $month,
                    'achievement' => $v,
                    'money'       => isset($receivables_list[$month]) ? $receivables_list[$month]['money'] : 0,
                    'count'       => isset($receivables_list[$month]) ? $receivables_list[$month]['count'] : 0,
                ];
                $row['ratio']      = ($row['money'] && intval($row['achievement'])) ? sprintf("%.2f", $row['money'] / $row['achievement'] * 100) : 0;
                $row['unit_price'] = ($row['money'] && $row['count']) ? sprintf("%.2f", $row['money'] / $row['count']) : 0;

                $data['month'][] = $row['month'];
                $data['achievement'][] = $row['achievement'];
                $data['money'][] = $row['money'];
                $data['ratio'][] = $row['ratio'];
            }
        }

        $this->success('请求成功', $data);
    }

    //进销存销售分析-销售汇总分析
    public function getSalesSummary(){
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'date');
        $where = [];
        $where['order_date'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getOneDepartmentStaffList($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }
        $result = Contract::where($where)->field('count(*) as count,order_date')
            ->group('order_date')->select();

        $contract_result = Contract::where($where)->field('sum(money) as count,order_date')
            ->group('order_date')->select();


        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['order_date']] = $v['count'];
        }

        $contractCount = [];
        foreach ($contract_result as $v) {
            $contractCount[$v['order_date']] = $v['count'];
        }

        $dateList = getDateList(strtotime($times[0]), strtotime($times[1]));

        $result=[];

        foreach ($dateList as $date) {
            $result['date'][]=$date;
            $result['count'][]=intval($addCount[$date] ?? 0);
            $result['contract_money'][]=$contractCount[$date] ?? 0;
        }
        $this->success('请求成功', $result);
    }
    //进销存销售分析-产品销售分析
    public function getProductSummary(){
        $times = input('times');
        $department_id = input('department_id');
        $staff_id = input('staff_id');
        if (empty($times)) {
            $this->error('日期不能为空');
        }
        $times = setTimes($times, 'date');

        $where = [];
//        $where['createtime'] = ['between', $times];

        if ($department_id) {
            $l_ids = Staff::getOneDepartmentStaffList($department_id);
            $where['create_staff_id'] = ['in', $l_ids];
        }
        if ($staff_id) {
            $where['create_staff_id'] = $staff_id;
        }

        $result = Contract::where($where) ->alias('w')
            ->join('qingdongams_contract_product c', 'w.id=c.contract_id')
            ->field('count(*) as count,sum(number) as number,product_id,sum(money) as money')->group('product_id')->where(['check_status'=>2])->select();

        $result=collection($result)->toArray();
        $addCount = [];
        foreach ($result as $v) {
            $addCount[$v['product_id']] = [
                'count'=>$v['count'],
                'number'=>$v['number'],
                'money'=>$v['money'],
            ];
        }
        $sum=array_sum($addCount);
        $product = Product::where([])->select();
        $data = [];
        foreach ($product as $item) {
            $data[] = [
                'name' => $item['name'],
                'count' => $addCount[$item['id']]['count'] ?? 0,
                'number' => $addCount[$item['id']]['number'] ?? 0,
                'money' => $addCount[$item['id']]['money'] ?? 0,
            ];
        }
        $this->success('请求成功', $data);
    }

    //获取任务统计
    public function getEventStatistics(){
       $khfwsm= Event::where(['event_type'=>['like',"客户服务_上门%"]])->group('status')->column('count(*) as number','status');
       $khfwdh= Event::where(['event_type'=>"客户服务_电话"])->group('status')->column('count(*) as number','status');
       $khfwwx= Event::where(['event_type'=>"客户服务_微信"])->group('status')->column('count(*) as number','status');
       $shfwsm= Event::where(['event_type'=>['like',"售后服务_上门%"]])->group('status')->column('count(*) as number','status');
        $shfwdh= Event::where(['event_type'=>"售后服务_电话"])->group('status')->column('count(*) as number','status');
        $shfwfcwx= Event::where(['event_type'=>"售后服务_返厂维修"])->group('status')->column('count(*) as number','status');
        $data = [
            [
                'name' => '客户服务-上门',
                'data' => [
                    [
                        'key' => '未开始',
                        'value' => ($khfwsm[0] ?? 0) . '',
                    ],
                    [
                        'key' => '进行中',
                        'value' => ($khfwsm[1] ?? 0) . '',
                    ],
                    [
                        'key' => '已结束',
                        'value' => ($khfwsm[2] ?? 0) . '',
                    ],
                    [
                        'key' => '任务结束',
                        'value' => ($khfwsm[3] ?? 0) . '',
                    ],
                ]
            ],
            [
                'name' => '客户服务-电话',
                'data' => [
                    [
                        'key' => '未开始',
                        'value' => ($khfwdh[0] ?? 0) . '',
                    ],
                    [
                        'key' => '进行中',
                        'value' => ($khfwdh[1] ?? 0) . '',
                    ],
                    [
                        'key' => '已结束',
                        'value' => ($khfwdh[2] ?? 0) . '',
                    ],
                    [
                        'key' => '任务结束',
                        'value' => ($khfwdh[3] ?? 0) . '',
                    ],
                ]
            ],
            [
                'name' => '客户服务-微信',
                'data' => [
                    [
                        'key' => '未开始',
                        'value' => ($khfwwx[0] ?? 0) . '',
                    ],
                    [
                        'key' => '进行中',
                        'value' => ($khfwwx[1] ?? 0) . '',
                    ],
                    [
                        'key' => '已结束',
                        'value' => ($khfwwx[2] ?? 0) . '',
                    ],
                    [
                        'key' => '任务结束',
                        'value' => ($khfwwx[3] ?? 0) . '',
                    ],
                ]
            ],
            [
                'name' => '售后服务-上门',
                'data' => [
                    [
                        'key' => '未开始',
                        'value' => ($shfwsm[0] ?? 0) . '',
                    ],
                    [
                        'key' => '进行中',
                        'value' => ($shfwsm[1] ?? 0) . '',
                    ],
                    [
                        'key' => '已结束',
                        'value' => ($shfwsm[2] ?? 0) . '',
                    ],
                    [
                        'key' => '任务结束',
                        'value' => ($shfwsm[3] ?? 0) . '',
                    ],
                ]
            ],
            [
                'name' => '售后服务-电话',
                'data' => [
                    [
                        'key' => '未开始',
                        'value' => ($shfwdh[0] ?? 0) . '',
                    ],
                    [
                        'key' => '进行中',
                        'value' => ($shfwdh[1] ?? 0) . '',
                    ],
                    [
                        'key' => '已结束',
                        'value' => ($shfwdh[2] ?? 0) . '',
                    ],
                    [
                        'key' => '任务结束',
                        'value' => ($shfwdh[3] ?? 0) . '',
                    ],
                ]
            ],
            [
                'name' => '售后服务-返厂维修',
                'data' => [
                    [
                        'key' => '未开始',
                        'value' => ($shfwfcwx[0] ?? 0) . '',
                    ],
                    [
                        'key' => '进行中',
                        'value' => ($shfwfcwx[1] ?? 0) . '',
                    ],
                    [
                        'key' => '已结束',
                        'value' => ($shfwfcwx[2] ?? 0) . '',
                    ],
                    [
                        'key' => '任务结束',
                        'value' => ($shfwfcwx[3] ?? 0) . '',
                    ],
                ]
            ],
        ];
        $this->success('请求成功',$data);
    }
    //获取客户统计
    public function getCustomerStatistics(){

        $data= Customer::where([])->group('management_id,type')
            ->field('count(*) as number,type,management_id')->select();
        $managements=AreaManagement::where([])->column('name','id');
        $result=[];
        $customer=[];
        foreach ($data as $v){
            $customer[$v['management_id']][$v['type']]=$v['number'];
        }
        foreach ($customer as $mid=>$v){
            if(!isset($managements[$mid])){
                continue;
            }
            $result[]=[
                'name'=>$managements[$mid],
                'data' => [
                    [
                        'key' => 'A类客户',
                        'value' => ($v['A'] ?? 0) . '',
                    ],
                    [
                        'key' => 'B类客户',
                        'value' => ($v['B'] ?? 0) . '',
                    ],
                    [
                        'key' => 'C类客户',
                        'value' => ($v['C'] ?? 0) . '',
                    ],
                    [
                        'key' => 'D类客户',
                        'value' => ($v['D'] ?? 0) . '',
                    ],
                ]
            ];
        }
        $this->success('请求成功',$result);
    }

    //获取审批统计
    public function getApprovalStatistics(){
       $form= FormApproval::where([])->column('name','id');
       $data=[];
        foreach ($form as $id=>$name) {
            $value=[];
            $value['name']=$name;
            $approval=\addons\qingdongams\model\Approval::where(['formapproval_id'=>$id])->group('check_status')->column('count(*) as number','check_status');
            $value['data']=[
                ['key'=>'待审核','value'=>($approval[1]??0).''],
                ['key'=>'审核通过','value'=>($approval[2]??0).''],
                ['key'=>'审核拒绝','value'=>($approval[3]??0).''],
                ['key'=>'撤销审核','value'=>($approval[4]??0).''],
                ['key'=>'终止审批','value'=>($approval[9]??0).''],
            ];
            $data[]=$value;
       }

        $this->success('请求成功',$data);
    }

    //获取费用统计
    public function getConsumeStatistics()
    {
        $consume_types=ConsumeDetail::where([])->group('consume_type')->column('consume_type');
        $data=[];
        foreach ($consume_types as $name){
            $value=[];
            $value['name']=$name;
            $consume_ids=ConsumeDetail::where(['consume_type'=>$name])->column('consume_id');
            $consume=\addons\qingdongams\model\Consume::where(['id'=>['in',$consume_ids]])->group('check_status')->column('count(*) as number','check_status');
            $value['data']=[
                ['key'=>'待审核','value'=>($consume[0]??0).''],
                ['key'=>'审核通过','value'=>($consume[2]??0).''],
                ['key'=>'审核拒绝','value'=>($consume[3]??0).''],
                ['key'=>'撤销审核','value'=>($consume[4]??0).''],
                ['key'=>'终止审批','value'=>($consume[9]??0).''],
            ];
            $data[]=$value;
        }
        $this->success('请求成功',$data);
    }

    //获取出纳统计
    public function getCashierStatistics()
    {
        $consume_types=ConsumeDetail::where([])->group('consume_type')->column('consume_type');
        $data=[];
        foreach ($consume_types as $name){
            $value=[];
            $value['name']=$name;
            $consume_ids=ConsumeDetail::where(['consume_type'=>$name])->column('consume_id');
            $consume=\addons\qingdongams\model\Consume::where(['id'=>['in',$consume_ids],'check_status'=>2])->group('is_cashier')->column('count(*) as number','is_cashier');
            $value['data']=[
                ['key'=>'未打款','value'=>($consume[0]??0).''],
                ['key'=>'已打款','value'=>($consume[1]??0).''],
            ];
            $data[]=$value;
        }
        $this->success('请求成功',$data);
    }
    //获取合同统计
    public function getContractStatistics()
    {
       $contract= Contract::where([])->group('check_status')->column('count(*) as number','check_status');


        $data = [
            [
                'name' => '合同',
                'data' => [
                    ['key'=>'待审核','value'=>($contract[0]??0).''],
                    ['key'=>'审核通过','value'=>($contract[2]??0).''],
                    ['key'=>'审核拒绝','value'=>($contract[3]??0).''],
                    ['key'=>'撤销审核','value'=>($contract[4]??0).''],
                    ['key'=>'终止审批','value'=>($contract[9]??0).''],
                ]
            ],

        ];
        $this->success('请求成功',$data);
    }
    //获取报价单拿统计
    public function getQuoteStatistics()
    {
       $quote= \addons\qingdongams\model\Quote::where([])->group('check_status')->column('count(*) as number','check_status');


        $data = [
            [
                'name' => '报价单',
                'data' => [
                    ['key'=>'待审核','value'=>($quote[0]??0).''],
                    ['key'=>'审核通过','value'=>($quote[2]??0).''],
                    ['key'=>'审核拒绝','value'=>($quote[3]??0).''],
                    ['key'=>'撤销审核','value'=>($quote[4]??0).''],
                    ['key'=>'终止审批','value'=>($quote[9]??0).''],
                ]
            ],

        ];
        $this->success('请求成功',$data);
    }
    //获取备件统计
    public function getPartsStatistics()
    {
        $ru= PartsStockReload::where(['type'=>1])->group('check_status')->column('count(*) as number','check_status');
        $chu= PartsStockReload::where(['type'=>2])->group('check_status')->column('count(*) as number','check_status');


        $data = [
            [
                'name' => '入库审核',
                'data' => [
                    ['key'=>'待审核','value'=>($ru[0]??0).''],
                    ['key'=>'审核通过','value'=>($ru[2]??0).''],
                    ['key'=>'审核拒绝','value'=>($ru[3]??0).''],
                    ['key'=>'撤销审核','value'=>($ru[4]??0).''],
                    ['key'=>'终止审批','value'=>($ru[9]??0).''],
                ]
            ],
            [
                'name' => '出库审核',
                'data' => [
                    ['key'=>'待审核','value'=>($chu[0]??0).''],
                    ['key'=>'审核通过','value'=>($chu[2]??0).''],
                    ['key'=>'审核拒绝','value'=>($chu[3]??0).''],
                    ['key'=>'撤销审核','value'=>($chu[4]??0).''],
                    ['key'=>'终止审批','value'=>($chu[9]??0).''],
                ]
            ],

        ];
        $this->success('请求成功',$data);
    }
    //获取工单统计
    public function getWorkorderStatistics()
    {
        $workorder_types= Workorder::where([])->group('workorder_type')->column('workorder_type');

        $data=[];
        foreach ($workorder_types as $name) {
            if(empty($name)){
                continue;
            }
            $value=[];
            $value['name']=$name;
            $workorder=Workorder::where(['workorder_type'=>$name])->group('check_status')->column('count(*) as number','check_status');
            $value['data']=[
                ['key'=>'待审核','value'=>($workorder[0]??0).''],
                ['key'=>'审核通过','value'=>($workorder[2]??0).''],
                ['key'=>'审核拒绝','value'=>($workorder[3]??0).''],
                ['key'=>'撤销审核','value'=>($workorder[4]??0).''],
                ['key'=>'终止审批','value'=>($workorder[9]??0).''],
            ];
            $data[]=$value;
        }

        $this->success('请求成功',$data);
    }


    /**
     * 客户新增排行
     */
    public function newCustomer() {
        $date = input('date', date('Y-m'));
        $type = input('type', 0);

        if(strlen($date) == 4){
            $between = [strtotime($date . '-01-01'), strtotime($date.'-1-1 +1 year') - 1];
        }else{
            $between = [strtotime($date), strtotime('+1 month', strtotime($date)) - 1];
        }
        if ($type == 1) {//本人
            $ids = [$this->auth->id];
        } elseif ($type == 2) {//下属
            $ids = Staff::getLowerStaffId();
        } else {//全部
            $ids = Staff::getMyStaffIds();
        }
        $c         = Customer::where([
            'createtime'      => [
                'between',
                $between
            ],
            'owner_staff_id' => ['in', $ids]
        ])->group('owner_staff_id')->order('count desc')->field("owner_staff_id,count(*) as count")->select();
        $customers = [];
        foreach ($c as $v) {
            $customers[$v['owner_staff_id']] = $v['count'];
        }

        arsort($customers);
        $staffs    = Staff::getKeyList();
        $result    = [];
        $i         = 1;
        $data=[];
        foreach ($customers as $id => $number) {
            $val           = $staffs[$id];
            $val['number'] = $number;
            $val['rank']   = $i;
            $data[]=[
                'name'=>$val['name'],
                'number'=>$number
            ];
            $i++;
            $result[]     = $val;
        }

        $this->success('请求成功',['ranking' => $result, 'data' => $data]);
    }

    /**
     * 客户跟进排行
     */
    public function newRecord() {
        $date = input('date', date('Y-m'));
        $type = input('type', 0);

        if(strlen($date) == 4){
            $between = [strtotime($date . '-01-01'), strtotime($date.'-1-1 +1 year') - 1];
        }else{
            $between = [strtotime($date), strtotime('+1 month', strtotime($date)) - 1];
        }
        if ($type == 1) {//本人
            $ids = [$this->auth->id];
        } elseif ($type == 2) {//下属
            $ids = Staff::getLowerStaffId();
        } else {//全部
            $ids = Staff::getMyStaffIds();
        }
        //跟进次数
        $r= Record::where([
            'create_staff_id' => ['in',$ids],
            'relation_type' =>1,
            'createtime' => [
                'between',
                $between
            ]
        ])->field("create_staff_id,count(*) as count")->group('create_staff_id')->select();

        $records = [];
        foreach ($r as $v) {
            $records[$v['create_staff_id']] = $v['count'];
        }

        arsort($records);
        $staffs    = Staff::getKeyList();
        $result    = [];
        $data    = [];
        $i         = 1;
        foreach ($records as $id => $number) {
            $val           = $staffs[$id];
            $val['number'] = $number;
            $val['rank']   = $i;
            $data[]=[
                'name'=>$val['name'],
                'number'=>$number
            ];
            $i++;
            $result[]     = $val;
        }

        $this->success('请求成功',['ranking' => $result, 'data' => $data]);
    }

    /**
     * 获取业绩详情
     */
    public function getAchievementDetail()
    {
        $year = input('year', date('Y'));
        $status = input('status', 1);
        $staff_id = input('staff_id', '');
        $type = input('type', 3);//3员工 2团队
        if (empty($staff_id)) {
            $this->error('员工不能为空');
        }
        if($type == 2){
            $staff_id = $this->auth->id;
            $ids = Staff::getMyStaffIds();
        }else{
            $ids = $staff_id;
        }

        $achievements = Achievement::where(['type' => $type, 'obj_id' => ['in', $staff_id],
            'year' => $year, 'status' => $status])
            ->field('sum(january) as january,sum(february) as february,sum(march) as march,sum(april) as april,sum(may) as may,sum(june) as june,sum(july) as july,sum(august) as august,sum(september) as september,sum(october) as october,sum(november) as november,sum(december) as december,sum(yeartarget) as yeartarget')->find()->toArray();
        if ($status == 1) {//合同金额
            $contracts = ContractRatio::where([
                'contract.check_status' => 2, 'contract_ratio.staff_id' => ['in', $ids],
                'contract.order_date' => ['like', $year . '%']
            ])->with(['contract'])->select();
            $contracts = collection($contracts)->toArray();
            $contractData = [];
            foreach ($contracts as $v) {
                $order_date = $v['contract']['order_date'];
                $month = date('Y-m', strtotime($order_date));
                $contractData[$month]['money'][] = $v['ratio_money'];
                $contractData[$month]['contract_id'][] = $v['contract_id'];
            }

            $list = [];
            foreach ($contractData as $month => $v) {
                $list[$month] = ['money' => array_sum($v['money']), 'count' => count($v['contract_id'])];
            }
        } else if ($status == 2) {
            $receivables = Receivables::where([
                'owner_staff_id' => ['in', $ids],
                'check_status' => 2,
                'return_time' => ['like', $year . '%'],
            ])->group('month')
                ->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(return_time),"%Y-%m") as month,count(*) as count')->select();
            $list = [];
            foreach ($receivables as $v) {
                $list[$v['month']] = $v;
            }
        }elseif ($status == 3) {//客户
            $where['createtime'] = ['between',[strtotime($year.'-01-01'),
                strtotime($year.'-12-31')]];
            $where['create_staff_id'] = ['in',$ids];
            $moneys = Customer::where($where)
                ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m") as month')
                ->group('month')->select();
            $list = [];
            foreach ($moneys as $v) {
                $list[$v['month']] = $v;
            }
        } elseif ($status == 4) {//拜访
            $where['createtime'] = ['between',[strtotime($year.'-01-01'),
                strtotime($year.'-12-31')]];
            $where['staff_id'] = ['in',$staff_id];
            $where['type'] = 3;
            $where['status'] = 2;
            $moneys = Event::where($where)->field('count(*) as count,FROM_UNIXTIME(actual_end_time,"%Y-%m") as month')
                ->group('month')->select();
            $list = [];
            foreach ($moneys as $v) {
                $list[$v['month']] = $v;
            }
        }
        $data = [];
        $echartdata = [];
        foreach ($achievements as $k => $v) {
            if ($month = Achievement::getFieldMonth($k)) {
                $month = $year . '-' . $month;
                $row = [
                    'month' => $month,
                    'achievement' => $v ?? 0,
                    'money' => isset($list[$month]) ? $list[$month]['money'] : 0,
                    'count' => isset($list[$month]) ? $list[$month]['count'] : 0,
                ];
                $row['ratio'] = ($row['money'] && intval($row['achievement'])) ? sprintf("%.2f", $row['money'] / $row['achievement'] * 100) : 0;
                $row['unit_price'] = ($row['money'] && $row['count']) ? sprintf("%.2f", $row['money'] / $row['count']) : 0;

                $echartdata['month'][] = $row['month'];
                $echartdata['achievement'][] = $row['achievement']??0;
                $echartdata['money'][] = $row['money'];
                $data[] = $row;
            }
        }
        $money = isset($echartdata['money']) ? array_sum($echartdata['money']) : 0;
        $ratio = ($money && intval($achievements['yeartarget'])) ? sprintf("%.2f", $money / $achievements['yeartarget'] * 100) : 0;
        $yeartarget = [
            'year' => $year,
            'money' => $money,
            'achievement' => $achievements['yeartarget'] ?? 0,
            'ratio' => $ratio
        ];
        $this->success('请求成功', ['data' => $data, 'echartdata' => $echartdata, 'yeartarget' => $yeartarget]);
    }

    //客户统计
    public function customerStatistics(){
        $times = input('times');
        $type = input('type', 2);//2下属 1本人 0团队
        if (empty($times)) {
            $this->error('筛选时间不能为空');
        }
        $times = setTimes($times, 'datetime');
        $startTime = strtotime($times[0]);
        $endTime = strtotime($times[1]);
        $where = ['createtime' => ['between', [$startTime, $endTime]]];
        $whereR = ['createtime' => ['between', [$startTime, $endTime]]];
        if ($type == 1) {
            $where['owner_staff_id'] = $this->auth->id;
            $whereR['create_staff_id'] = $this->auth->id;
        } elseif ($type == 2) {
            $where['owner_staff_id'] = ['in', Staff::getLowerStaffId()];
            $whereR['create_staff_id'] = ['in', Staff::getLowerStaffId()];
        } else {
            $where['owner_staff_id'] = ['in', Staff::getMyStaffIds()];
            $whereR['create_staff_id'] = ['in', Staff::getMyStaffIds()];
        }
        //新增客户数
        $customer = Customer::where($where)->count();
        //跟进客户数
        $record = Record::where($whereR)->where(['relation_type'=>1,'follow_type' => ['neq', '其它']])->count();
        //客户成交数
        $customer_complate = Customer::where($where)->where(['contract_status' => 1])->count();
        //客户未成交数
        $customer_nocomplate = Customer::where($where)->where(['contract_status' => 0])->count();
        //进入公海客户数
        $seain = SeaOperation::where($where)->where(['type'=>1])->count();
        //公海领取客户数
        $seaout = SeaOperation::where($where)->where(['type'=>0])->count();
        $this->success('请求成功', [
            'customer' => $customer,
            'record' => $record,
            'customer_complate' => $customer_complate,
            'customer_nocomplate' => $customer_nocomplate,
            'seain' => $seain,
            'seaout' => $seaout,]);
    }
    //商机统计
    public function businessStatistics(){
        $times = input('times');
        $type = input('type', 2);//2下属 1本人 0团队
        if (empty($times)) {
            $this->error('筛选时间不能为空');
        }
        $times = setTimes($times, 'datetime');
        $startTime = strtotime($times[0]);
        $endTime = strtotime($times[1]);
        $where = ['createtime' => ['between', [$startTime, $endTime]]];
        if ($type == 1) {
            $where['owner_staff_id'] = $this->auth->id;
        } elseif ($type == 2) {
            $where['owner_staff_id'] = ['in', Staff::getLowerStaffId()];
        } else {
            $where['owner_staff_id'] = ['in', Staff::getMyStaffIds()];
        }
        //商机总金额
        $business_money = Business::where($where)->sum('money');
        //商机总数
        $business_num = Business::where($where)->count();
        //赢单金额
        $business_complate_money = Business::where($where)->where(['contract_status' => 1])->sum('money');
        //输单交金额
        $business_nocomplate_money = Business::where($where)->where(['contract_status' => 0])->sum('money');
        //赢单数
        $business_complate = Business::where($where)->where(['contract_status' => 1])->count();
        //输单数
        $business_nocomplate = Business::where($where)->where(['contract_status' => 0])->count();

        $this->success('请求成功', [
            'business_money' => $business_money,
            'business_num' => $business_num,
            'business_complate_money' => $business_complate_money,
            'business_nocomplate_money' => $business_nocomplate_money,
            'business_complate' => $business_complate,
            'business_nocomplate' => $business_nocomplate,]);
    }
    //合同统计
    public function contractStatistics(){
        $times = input('times');
        $type = input('type', 2);//2下属 1本人 0团队
        if (empty($times)) {
            $this->error('筛选时间不能为空');
        }
        if($times == 'thisyear' || $times == 'lastyear' || $times == 'nextyear'){
            $timeinfo ='%Y-%m';
        }else{
            $timeinfo ='%Y-%m-%d';
        }
        $times = setTimes($times, 'datetime');
        $startTime = strtotime($times[0]);
        $endTime = strtotime($times[1]);
        $where = ['order_date' => ['between', [date('Y-m-d 00:00:00',$startTime), date('Y-m-d 23:59:59',$endTime)]]];
        $whereR = ['return_time' => ['between', [date('Y-m-d 00:00:00',$startTime), date('Y-m-d 23:59:59',$endTime)]]];

        if ($type == 1) {
            $where['owner_staff_id'] = $this->auth->id;
            $whereR['owner_staff_id'] = $this->auth->id;
        } elseif ($type == 2) {
            $where['owner_staff_id'] = ['in', Staff::getLowerStaffId()];
            $whereR['owner_staff_id'] = ['in', Staff::getLowerStaffId()];
        } else {
            $where['owner_staff_id'] = ['in', Staff::getMyStaffIds()];
            $whereR['owner_staff_id'] = ['in', Staff::getMyStaffIds()];
        }
        $where['check_status']= 2;
        //合同总金额
        $contract_money = Contract::where($where)->sum('money');
        //签约合同数
        $contract_num = Contract::where($where)->count();
        //回款总金额
        $receivables_money = Receivables::where($whereR)->count();
        //未回款总金额
        $no_money = $contract_money -$receivables_money;
        if($no_money <= 0){
            $no_money = 0;
        }
        $receivables_nomoney = $no_money;
        //已到期的合同
        $contract_over = Contract::where($where)->where(['end_time' => array('gt',date('Y-m-d'))])->sum('money');
        //即将到期的合同
        $contract_now = Contract::where($where)->where(['end_time' => array('elt',date('Y-m-d'))])->count();
        $contract       = Contract::where($where)->group('month')->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(order_date),"'.$timeinfo.'") as month')->select();
        $contracts_list  = [];
        foreach ($contract as $v) {
            $contracts_list[$v['month']] = $v['money'];
        }
        $receivables      = Receivables::where($whereR)->where([
            'check_status' => 2,
        ])->group('dates')->field('sum(money) as money,FROM_UNIXTIME(UNIX_TIMESTAMP(return_time),"'.$timeinfo.'") as dates')->select();
        $receivables_list = [];
        foreach ($receivables as $v) {
            $receivables_list[$v['dates']] = $v['money'];
        }
        $data = [];
        foreach ($contracts_list as $k => $v) {
            $row = array(
                'month'=>$k,
                'contract'=>$v,
                'receivables'=>isset($receivables_list[$k]) ? $receivables_list[$k] : 0,
            );
            $data[] = $row;
        }
        $this->success('请求成功', [
            'contract_money' => $contract_money,
            'receivables_money' => $receivables_money,
            'receivables_nomoney' => $receivables_nomoney,
            'contract_num' => $contract_num,
            'contract_over' => $contract_over,
            'contract_now' => $contract_now,
            'contract' => $data,]);
    }
}