cxinv/app/command/companyStats.php

<?php
declare (strict_types = 1);

namespace app\command;

use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\input\Option;
use think\console\Output;
use think\facade\Db;
use think\facade\Cache;

class companyStats extends Command
{
    protected function configure()
    {
        // 指令配置
        $this->setName('companystats')
            ->setDescription('the companystats command');
    }

    protected function execute(Input $input, Output $output)
    {
	    $sttime = Cache::get("compTips");
	    if($sttime==1) return '';
	    Cache::set("compTips",1,1800);
      $supplier = ["GS2302231124114965","GS2302231125079621","GS2302231323386950",'GS2203161855277894',"GS2304031312553746"];
      try{
	      foreach ($supplier as $sp){
		      $this->checkData($sp);
	      }
      }catch (\Exception $e){
      	echo $e->getMessage();
      }

    }

	/**
	 * @param $supplierNo
	 */
    public function checkData($supplierNo):void {
        $sup=Db::connect("mysql_sys")->name("headquarters")->where(["code"=>$supplierNo])->field("code,name,relation_code")->findOrEmpty();
        if (empty($sup)) return;
        $date=date("Y-m-d");
//	    $start="20230501";
//	    $end= date("Ymt",strtotime($start));
//	    $date_tmp=$start;
//	    $temp=[];
//	    while ($date_tmp>=$start && $date_tmp<=$end) {
//		    $date=date("Y-m-d" , strtotime($date_tmp . ''));
		    $d=Db::name("depart_company")->where(["companyNo"=>$supplierNo , "day_time"=>$date])->column("*" , "type");
		    $total=Db::name("qrd_info")
		             ->where(["companyNo"=>$supplierNo , "is_del"=>0 , "cxCode"=>""])
		             ->whereDay("createdTime" , $date)
		             ->field("sum(totalPrice+th_fee) as total")->findOrEmpty();
		    $day_thtotal=Db::name("th_source")->alias("a")
		                   ->leftJoin("qrd_info b" , "a.th_qrdNo=b.sequenceNo")
		                   ->where([['a.th_companyNo' , "=" , $supplierNo] , ["b.is_del" , "=" , 0]])
		                   ->whereDay("a.createtime" , $date)->field('sum(a.th_qrd_fee) as th_total')->findOrEmpty();
		    $cgdtotal = $this->cgdTotal($supplierNo,$date,1);
		    if (isset($d[1])) Db::name("depart_company")->where($d[1])->save([
		    	"sale_total"=>$total['total'] ?? 0 ,
			    "th_total"=>$day_thtotal['th_total'] ?? 0,
			    "cgd_total"=>$cgdtotal['cgd_total']??0,
			    "cgd_th_total"=>$cgdtotal['cgd_th_total']??0,
		    ]);
		    else $temp[]=[
			    "companyNo"=>$supplierNo ,
			    "companyName"=>$sup['name'] ?? "" ,
			    "type"=>1 ,
			    "day_time"=>$date ,
			    "sale_total"=>$total['total'] ?? 0 ,
			    "th_total"=>$day_thtotal['th_total'] ?? 0 ,
			    "cgd_total"=>$cgdtotal['cgd_total']??0,
			    "cgd_th_total"=>$cgdtotal['cgd_th_total']??0,
			    "addtime"=>date("Y-m-d H:i:s")
		    ];
		    $total_cx=Db::name("qrd_info")
		                ->where(["companyNo"=>$supplierNo , "is_del"=>0])
		                ->where("cxCode" , "<>" , "")
		                ->whereDay("createdTime" , $date)
		                ->field("sum(totalPrice+th_fee) as total")->findOrEmpty();
		    $day_thtotal_cx=Db::name("th_source")->alias("a")
		                      ->leftJoin("qrd_info b" , "a.th_qrdNo=b.cxCode")
		                      ->where([['a.th_companyNo' , "=" , $supplierNo] , ["b.is_del" , "=" , 0]])
		                      ->whereDay("a.createtime" , $date)
		                      ->field("sum(a.th_num*b.goodPrice) as th_total")->findOrEmpty();
		    $cgdtotal = $this->cgdTotal($supplierNo,$date,2);
		    if (isset($d[2])) Db::name("depart_company")->where($d[2])->save([
		    	"sale_total"=>$total_cx['total'] ?? 0 ,
			    "th_total"=>$day_thtotal_cx['th_total'] ?? 0,
			     "cgd_total"=>$cgdtotal['cgd_total']??0,
				 "cgd_th_total"=>$cgdtotal['cgd_th_total']??0,
				    ]
		    );
		    else $temp[]=[
			    "companyNo"=>$supplierNo ,
			    "companyName"=>$sup['name'] ?? "" ,
			    "type"=>2 ,
			    "day_time"=>$date ,
			    "sale_total"=>$total_cx['total'] ?? 0 ,
			    "th_total"=>$day_thtotal_cx['th_total'] ?? 0 ,
			    "cgd_total"=>$cgdtotal['cgd_total']??0,
			    "cgd_th_total"=>$cgdtotal['cgd_th_total']??0,
			    "addtime"=>date("Y-m-d H:i:s")
		    ];
		    $cgd=$this->cgdData($sup['relation_code'] , $date);
		    if (isset($d[3])) Db::name("depart_company")->where($d[3])->save(["sale_total"=>$cgd['cgd_total'] ,
			    "th_total"=>$cgd['cgd_thtotal']]
		    );
		    else $temp[]=[
			    "companyNo"=>$supplierNo ,
			    "companyName"=>$sup['name'] ?? "" ,
			    "type"=>3 ,
			    "day_time"=>$date ,
			    "sale_total"=>$cgd['cgd_total'] ,
			    "th_total"=>$cgd['cgd_thtotal'] ,
			    "addtime"=>date("Y-m-d H:i:s")
		    ];
//		    $date_tmp++;
//	    }
		if(!empty($temp))Db::name("depart_company")->insertAll($temp);

    }

	/**
	 * @param $sup
	 * @param $date
	 * @return array
	 */
    private function cgdData($sup,$date): array
    {

		$total =Db::name("cgd_info")
		->where(["supplierNo"=>$sup,"is_del"=>0,"cxCode"=>""])
		->whereDay("createdTime",$date)
		->field("sum(totalPrice+th_fee) as total")->findOrEmpty();
		$day_thtotal=Db::name("th_source")->alias("a")
		 ->leftJoin("cgd_info b","(a.th_cgdNo=b.sequenceNo and a.is_th=1)")
		 ->where([['b.supplierNo',"=",$sup],["b.is_del","=",0]])
		 ->whereDay("a.createtime",$date)
	    ->field("sum(a.th_num*b.goodPrice) as th_total")->findOrEmpty();
		return ["cgd_total"=>$total['total']??0,"cgd_thtotal"=>$day_thtotal['th_total']??0];
    }


    	/**
	 * @param $sup
	 * @param $date
	 * @return array
	 */
    private function cgdTotal($companyNo,$date,$type): array
    {
			$where=[];
			if($type==1)$where=[["a.cxCode","=",'']];
			if($type==2)$where=[["a.cxCode","<>",'']];
			$day_total =Db::name("qrd_info")->alias("a")
			->leftJoin('cgd_info b', 'b.sequenceNo=a.cgdNo and b.is_del=0')
			->where([["a.is_del","=",0],['a.companyNo',"=",$companyNo]])
			->where($where)
		    ->whereDay("a.createdTime",$date)
		    ->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("b")
		               ->leftJoin("qrd_info a","b.th_qrdNo=a.sequenceNo or b.th_qrdNo=a.cxCode")
		               ->leftJoin('cgd_info c', 'c.sequenceNo=a.cgdNo and c.is_del=0')
		               ->where([['a.companyNo',"=",$companyNo]])
		               ->where($where)
		               ->whereDay("b.createtime",$date)->sum(Db::raw('b.th_num*ifnull(c.goodPrice,0)'));
		return ["cgd_total"=>$day_total,"th_cgd"=>$day_thtotal];
    }
}