cxinv/app/command/stat.php

<?php
declare (strict_types = 1);

namespace app\command;

use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\facade\Cache;
use think\facade\Db;

class stat extends Command
{
	protected function configure()
	{   $this->setName('stat')
	         ->setDescription('the good command');
	}

	protected function execute(Input $input, Output $output)
	{
		// 指令输出
		$sttime = Cache::get("departTips");
		if($sttime==1) return '';
		Cache::set("departTips",1,1800);
		$daprtid = [52,53,56,57];
		$companyNo='GS2203161855277894' ;
		$date=date("Y-m-d");
		$depart= Db::connect("mysql_sys")->name("company_item")->where(["id"=>$daprtid])->column("name","id");
		$company= Db::connect("mysql_sys")->name("business")->where(["companyNo"=>$companyNo])->column
		("company","companyNo");
		foreach ($depart as $itmeid=>$depart_name){
			$ist=Db::name("depart_everyday")
			->where(["depart_id"=>$itmeid,'companyNo'=>$companyNo,"day_time"=>$date])
			->findOrEmpty();
			$uidArr =getUidByDepartId($itmeid);
			$sale=$this->GetData($uidArr,$companyNo,$date);
			$cgd=$this->GetCgd($uidArr,$companyNo,$date);
			$zy=$this->GetData($uidArr,$companyNo,$date,1);
			$channel=$this->GetData($uidArr,$companyNo,$date,2);
			if($ist){
				$ist['sale_total']=$sale['sale_total']??0;
				$ist['th_total']=$sale['th_total']??0;
				$ist['cgd_total']=$cgd['cgd_total']??0;
				$ist['cgd_th_total']=$cgd['th_cgd']??0;
				$ist['zy_sale_total']=$zy['sale_total']-$zy['th_total'];
				$ist['channel_sale_total']=$channel['sale_total']-$channel['th_total'];
			}else{
				$ist=[
					"depart_id"=>$itmeid,
					"depart_name"=>$depart_name,
					"day_time"=>date("Y-m-d"),
					"sale_total"=>$sale['sale_total']??0,
					"th_total"=>$sale['th_total']??0,
					"cgd_total"=>$cgd['cgd_total']??0,
					"cgd_th_total"=>$cgd['th_cgd']??0,
					"zy_sale_total"=>$zy['sale_total']-$zy['th_total'],
					"channel_sale_total"=>$channel['sale_total']-$channel['th_total'],
					"companyNo"=>$companyNo,
					"companyName"=>$company[$companyNo],
				];
			}

			Db::name("depart_everyday")->save($ist);
		}
		$date=date('Y-m-d H:i:s');
		$output->writeln("[{$date}] 更新数据成功");
	}
	//各部门统计数据

	/**
	 * @param $uid
	 * @param $companyNo
	 * @param $date
	 * @return array
	 */
	public  function GetData($uid,$companyNo,$date,$type=0):array{

		$where=[];
		if($type==1) $where[]=["cxCode","=",''];
		if($type==2) $where[]=["cxCode","<>",''];
		$day_total =Db::name("qrd_info")->where([["ownerid","in",$uid],["is_del","=",0],['companyNo',"=",$companyNo]])
		                         ->whereDay("createdTime",$date)
		                         ->where($where)
		              ->sum(Db::raw('totalPrice+th_fee'));
		$day_thtotal=Db::name("th_source")->alias("a")
		               ->leftJoin("qrd_info b","a.th_qrdNo=b.sequenceNo or a.th_qrdNo=b.cxCode")
		               ->where([["b.ownerid","in",$uid],['b.companyNo',"=",$companyNo]])
		               ->where($where)
		               ->whereDay("a.createtime",$date)->sum(Db::raw('a.th_num*b.goodPrice'));
		return ["sale_total"=>$day_total,"th_total"=>$day_thtotal];
	}

	 public function GetCgd($uid,$companyNo,$date){
			$where=[];
			$day_total =Db::name("qrd_info")->alias("a")
			->leftJoin('cgd_info b', 'b.sequenceNo=a.cgdNo and b.is_del=0')
			->where([["a.ownerid","in",$uid],["a.is_del","=",0],['a.companyNo',"=",$companyNo]])
		    ->whereDay("a.createdTime",$date)
		    ->where($where)
		    ->sum(Db::raw("(ifnull(b.totalPrice,if(a.total_origin_price=0,a.total_plan_price,a.total_origin_price))+ifnull(b.th_fee,0))"));
		$day_thtotal=Db::name("th_source")->alias("a")
		               ->leftJoin("qrd_info b","a.th_qrdNo=b.sequenceNo or a.th_qrdNo=b.cxCode")
		               ->leftJoin('cgd_info c', 'c.sequenceNo=b.cgdNo and c.is_del=0')
		               ->where([["b.ownerid","in",$uid],['b.companyNo',"=",$companyNo]])
		               ->where($where)
		               ->whereDay("a.createtime",$date)->sum(Db::raw('a.th_num*ifnull(c.goodPrice,0)'));
		return ["cgd_total"=>$day_total,"th_cgd"=>$day_thtotal];
	 }
}