phpstock/app/report/controller/Stats.php

<?php
declare (strict_types = 1);

namespace app\report\controller;

use app\report\model\GoodBasic;
use app\model\AccountItem;
use app\report\model\ConsultBids;
use app\report\model\ConsultInfo;
use app\report\model\ConsultOrder;
use app\report\model\GoodStock;use app\report\model\OrderOut;
use app\report\model\OrderOutChild;
use app\report\model\Purchease;
use app\report\model\PurcheaseOrder;
use app\report\model\Sale;

use think\App;
class Stats extends Base
{
	private $saleDerpart=[52=>'客服部@百辰荣达',53=>'项目部@普润心堂',56=>'网络部@泓源广诚',57=>'平台部@万宇恒通',59=>'财务部@万宇恒通']; //  客服部@百辰荣达,
	//项目部@普润心堂,网络部@泓源广诚,平台部@万宇恒通,财务部@万宇恒通
	private $cgdDerpart=[58=>"万宇供应链资源部"];//万宇供应链资源部
	public function __construct(App $app) {parent::__construct($app);}
	
	//咨询单
    public function zxorder()
    {
    	$param =$this->request->param(["start"=>date("Y-m-01 00:00:00"),"end"=>date("Y-m-t 23:59:59")]);
    	$time = [$param["start"],$param['end']];
    	$zxModel =new ConsultOrder();
    	$temp=[];
    	foreach ($this->saleDerpart as $departid=>$depart){
    	    $uid =$this->GetDepartId($departid);
    	    $temp[$departid]["name"] =$depart;
    	    $temp[$departid]["num"] =$zxModel->alias("a")->leftJoin("consult_info b","wsm_consult_order.zxNo=b.zxNo")
    	    ->whereBetween('b.addtime',$time)
    	    ->whereIn('createrid',$uid)
    	    ->where(["a.is_del"=>0,"b.is_del"=>0])
    	    ->count();
    	}
    	
         $this->success("获取成功",array_values($temp));
    }

    /**
     * 销售单.
     *
     * @return \think\Response
     */
    public function saleOrder()
    {
    	$param =$this->request->param(['start'=>date('Y-m-01 00:00:00'),'end'=>date('Y-m-t 23:59:59')]);
    	$time = [$param['start'],$param['end']];
    	$Model =new Sale();
    	$temp=[];
    	foreach ($this->saleDerpart as $departid=>$depart){
    	    $uid =$this->GetDepartId($departid);
    	    $temp[$departid]['name'] =$depart;
    	    $temp[$departid]['result'] =$Model->group("order_type")
    	    ->whereBetween('addtime',$time)
    	    ->whereIn('apply_id',$uid)
    	    ->where(['is_del'=>0])
    	    ->column("count(1) num,order_type","order_type");
    	}
    	
         $this->success('获取成功',array_values($temp));
    }

    /**
     * 报备单
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */
    public function bkOrder()
    {
       $param =$this->request->param(['start'=>date('Y-m-01 00:00:00'),'end'=>date('Y-m-t 23:59:59')]);
    	$time = [$param['start'],$param['end']];
    	$Model =new Purchease();
    	$temp=[];
    	foreach ($this->saleDerpart as $departid=>$depart){
    	    $uid =$this->GetDepartId($departid);
    	    $temp[$departid]['name'] =$depart;
    	    $temp[$departid]['num'] =$Model
    	    ->whereBetween('addtime',$time)
    	    ->whereIn('apply_id',$uid)
    	    ->where(['is_del'=>0,"status"=>[1,2]])
    	    ->count();
    	}
    	
         $this->success('获取成功',array_values($temp));
    }

    /**
     * 采购部门采购情况
     */
    public function cgdOrder()
    {
     $param =$this->request->param(['start'=>date('Y-m-01 00:00:00'),'end'=>date('Y-m-t 23:59:59')]);
     $time = [$param['start'],$param['end']];
    	$Model =new PurcheaseOrder();
    	$temp=[];
    	
    	foreach ($this->cgdDerpart as $departid=>$depart){
    	    $uid =$this->GetDepartId($departid);
    	    $temp[$departid]['name'] =$depart;
    	    
    	    $data =$Model->alias("a")->leftJoin("order_num b","a.cgdNo=b.cgdNo")
    	    ->leftJoin("sale c","c.orderCode=b.orderCode and c.is_del=0")
    	    ->leftJoin("platform d","d.id=c.platform_id and d.is_del=0")
    	    ->field("a.companyNo,a.companyName,a.order_type,d.use_type,count(1) num")
    	    ->group("a.companyNo,a.companyName,order_type,use_type")
    	     ->whereBetween('a.addtime',$time)
//    	    ->whereIn('a.cgder_id',$uid)
    	    ->where('a.is_del',0)
    	    ->select();
    	    $result=[];
    	    foreach ($data as $key=>$val){
    	    	if(!isset($result[$val['companyNo']])){
    	    		 $result[$val['companyNo']]= [  'cgder'=>$val['companyName'],
	                    'total_cgd' => 0,//订单总量
	                    'tag_1' => 0,//库存品
	                    'tag_2' => 0,//非库存品
	                    'tag_zx' => 0,//咨询采反总数
	                    'tag_c' => 0,//to C
	                    'tag_b' => 0,//to B
                    ];
    	    	}
    	    	 $result[$val['companyNo']]['total_cgd']+=$val['num'];
    	    	switch ($val['order_type']) {
	                case 1:
	                    //库存品
//	                    if ($val['order_source'] == 0) {
	                        $result[$val['companyNo']]['tag_1']+=$val['num'];
//	                    }
	                    break;
	
	                case 2:
	                    //非库存品
	                 $result[$val['companyNo']]['tag_2']+=$val['num'];
					break;
	                case 3:
	                    //咨询采反
						$result[$val['companyNo']]['tag_zx']+=$val['num'];
	                    break;
                }
                
              switch ($val['use_type']) {
	                case 1:
	                    //to B
	                    $result[$val['companyNo']]['tag_b']+=$val['num'];
	                    
	                    break;
	
	                case 2:
	                    //to C
	                      $result[$val['companyNo']]['tag_c']+=$val['num'];
	                    break;
                }
    	    }
    	    $temp[$departid]['result'] =$result;
    	    
    	}
         $this->success('获取成功',array_values($temp));
    }

    /**
     * 显示编辑资源表单页.
     *
     * @param  int  $id
     * @return \think\Response
     */
    public function bidsOrder()
    {
     $param =$this->request->param(['start'=>date('Y-m-01 00:00:00'),'end'=>date('Y-m-t 23:59:59')]);
     $time = [$param['start'],$param['end']];
     $model = new ConsultBids();
     $order =new ConsultInfo();
     $temp=[];
      foreach ($this->cgdDerpart as $departid=>$depart){
//            $uid =$this->GetDepartId($departid);
    	    $temp[$departid]['name'] =$depart;
    	    $list = $model->whereBetween("addtime",$time)->where('is_del',0)->group("createrid,creater")->field
    	    ("creater,createrid,count(1) num")->select()->toArray();
    	    $list[]= ["creater"=>"竞价单总数","creater_id"=>"","num"=>$order->whereBetween('addtime',$time)->where('is_del',0)->count()];
    	    
    	    $temp[$departid]['result'] =$list;
      }
      
      $this->success('获取成功',array_values($temp));
    }

    /**
     * 咨询单空返数据
     *
     * @param  \think\Request  $request
     * @param  int  $id
     * @return \think\Response
     */
    public function bidsFail()
    {$param =$this->request->param(['start'=>date('Y-m-01 00:00:00'),'end'=>date('Y-m-t 23:59:59')]);
     $time = [$param['start'],$param['end']];
     $model = new ConsultOrder();
     $temp=[];
//     $uid =	 (new AccountItem())->GetUidByDepartId(array_keys($this->saleDerpart));
     $list = $model->alias('a')
    	    ->leftJoin('consult_info b','wsm_consult_order.zxNo=b.zxNo')
    	    ->leftJoin('consult_bids c','b.infoNo=c.infoNo')
    	    ->whereBetween('a.addtime',$time)
    	    ->whereNull("c.id")
    	    ->where(['a.is_del'=>0,'b.is_del'=>0])
//    	    ->where('b.status','<>',5)
    	    ->group('b.status')
    	    ->column('b.status,count(1) num','b.status');
    	    $temp['result'] =$list;
      $this->success('获取成功',$temp);
    }

    /**
     * 发货工单
     */
    public function outChildOrder()
    {
      $param =$this->request->param(['start'=>date('Y-m-01 00:00:00'),'end'=>date('Y-m-t 23:59:59')]);
      $time = [$param['start'],$param['end']];
      $model = new OrderOutChild();
      foreach ($this->saleDerpart as $departid=>$sale){
      	$uid = $this->GetDepartId($departid);
      	 $temp[$departid]['name'] =$sale;
      	   $build=  $model->alias('a')
//      	 ->whereBetween('a.addtime',$time)
      	 ->whereIn('a.apply_id',$uid)
      	 ->where('a.is_del',0)
      	 	 ->whereIn('a.status',[0,1])
      	 ->field('order_type,DATEDIFF(now(), a.addtime ) as expire,"" stage')
      	 ->withAttr('stage',function ($v,$row){
      	 	if($row['expire']>=0 && $row['expire']<=1) return 1;
      	 	elseif($row['expire']<=2) return 2;
      	 	elseif($row['expire']<=7) return 3;
      	 	elseif($row['expire']<=14) return 4;
      	 	elseif($row['expire']<=30) return 5;
      	 	else return 6;
      	 })->select();
      	  $tlist=[];
      	  $order_type_name = ["1"=>"库存品","2"=>"非库存品","3"=>"咨询采返",'4'=>'报备'];
      	foreach ($build as $item){
      	    if(!isset($tlist[$item['order_type']])){
      	    	$tlist[$item['order_type']]=[$order_type_name[$item['order_type']]?:"",0,0,0,0,0,0];
      	    }
      	    $tlist[$item['order_type']][$item['stage']]+=1;
      	}
      	 $temp[$departid]['result'] =$tlist?:[];
      }
      $this->success('获取成功',array_values($temp));
    }
    
    /** 发货单数据统计
	* @throws \think\db\exception\DataNotFoundException
	* @throws \think\db\exception\DbException
	* @throws \think\db\exception\ModelNotFoundException
    */
    public function outOrder()
    {
      $param =$this->request->param(['start'=>date('Y-m-01 00:00:00'),'end'=>date('Y-m-t 23:59:59')]);
      $time = [$param['start'],$param['end']];
      $model = new OrderOut();
      foreach ($this->saleDerpart as $departid=>$sale){
      	$uid = $this->GetDepartId($departid);
      	 $temp[$departid]['name'] =$sale;
      	  $build=  $model->alias('a')
//      	 ->whereBetween('a.addtime',$time)
      	 ->whereIn('a.apply_id',$uid)
      	 ->where('a.is_del',0)
      	 ->where('a.status',"=",0)
      	 ->field('order_type,DATEDIFF(ifnull( a.sendtime, NOW()), a.addtime ) as expire,"" stage')
      	 ->withAttr('stage',function ($v,$row){
      	 if($row['expire']>=0 && $row['expire']<=1) return 1;
      	 	elseif($row['expire']<=2) return 2;
      	 	elseif($row['expire']<=7) return 3;
      	 	elseif($row['expire']<=14) return 4;
      	 	elseif($row['expire']<=30) return 5;
      	 	else return 6;
      	 })->select();
      	  $tlist=[];
      	   $order_type_name = ['1'=>'库存品','2'=>'非库存品','3'=>'咨询采返','4'=>'报备'];
      		foreach ($build as $item){
      	    if(!isset($tlist[$item['order_type']])){
      	    	$tlist[$item['order_type']]=[$order_type_name[$item['order_type']]?:'',0,0,0,0,0,0];
      	    }
      	    $tlist[$item['order_type']][$item['stage']]+=1;
      	}
      	 $temp[$departid]['result'] =$tlist;
      }
      $this->success('获取成功',array_values($temp));
    }
    //未发货数据
    public function wsendOrder(){
    	$param =$this->request->param(['start_y'=>date('Y')]);
    	$temp=[];
    	$model =new Sale();
		$userArr=[];
    	foreach ($this->saleDerpart as $departid=>$depart){
    		$uid = $this->GetDepartId($departid);
    		$userArr=array_merge($userArr,$uid);
    		$temp[$departid]["name"] = $depart;
    	    $start = 1;
    	    $end = $param['start_y']<date('Y')?12: date("m");
    		while ($start<=$end){
        	    $list = $model->alias('a')
    	    	->whereIn('status',[0,1])
    			->whereIn('send_status',[1,2])
    			->where('is_del',0)
	            ->whereIn('apply_id',$uid)
	            ->whereMonth('addtime',$param['start_y'].'-'.str_pad($start.'' , 2,'0',0))
	            ->where('good_num-th_num > ifnull((select sum(receipt_quantity) from wsm_order_addr where orderCode=a.orderCode and is_del=0),0)')
	            ->group('send_status')
	            ->column('count(1) num ,send_status','send_status');
//        	 echo    $param['start'].'-'.str_pad($start.'' , 2,'0',0);
    			$temp[$departid]['result'][$start]=$list;
    			$start++;
    		}
    		
    	}
    	$noArr=[];
    	$noArr['name'] = "无地址订单";
    	 $start = 1;
    	 $end = $param['start_y']<date('Y')?12: date('m');
    		while ($start<=$end){
//    			$month = $start;a
    			$list = $model->whereIn('status',[0,1])
    			->whereIn('send_status',[1,2])
	            ->whereIn('apply_id',$userArr)
	            ->where('is_del',0)
	            ->whereMonth('addtime',$param['start_y'].'-'.str_pad($start.'' , 2,'0',0))
	            ->field('sale_price,orderCode,good_num,total_price,0 addr_num, 0 before_th')
	            ->withAttr('addr_num',function($v,$data){
	            	$num =  Sale::name('order_addr')->where(['orderCode'=>$data['orderCode'],'is_del'=>0])->sum
	            	('receipt_quantity');
	            	return bcmul($data['sale_price'],strval($num),2);
	            })
				->withAttr('before_th' , function ($v , $data) {
					    $num= Sale::name('sale_return')->where(['orderCode'=>$data['orderCode'] , 'is_del'=>0,
							    'status'=>4])->sum('num');
					return bcmul($data['sale_price'],strval($num),2);
				    }
				    )
	            ->select();
				if($list->isEmpty())$noArr['result'][$start]=['total_price'=>0];
				else{
					$total =strval(array_sum(array_column($list->toArray() , 'total_price')));
					$before_th =strval(array_sum(array_column($list->toArray() , 'before_th')));
					$addr_num =strval(array_sum(array_column($list->toArray() , 'addr_num')));
					$noArr['result'][$start]['total_price']=bcsub(bcsub($total,$before_th,2),$addr_num,2);
				}

    			$start++;
    		}
    	  $this->success('获取成功',["result"=>$temp,"noAddr"=>$noArr]);
    }
    
    
    
    //发货单维度商品每日库存
    public function goodStock(){
    	$param =$this->request->param(['spuCode'=>'','good_name'=>'','companyNo'=>'','page'=>1,
    	'size'=>10]);
      $where=[["is_del","=",0]];
      $param['spuCode']==''?: $where[]=["spuCode","like","%{$param['spuCode']}%"];
      $param['good_name']==''?: $where[]=["good_name","like","%{$param['good_name']}%"];
      $param['companyNo']==''?: $where[]=["companyNo","like","%{$param['companyNo']}%"];
      $model = new GoodBasic();
      $stock = new GoodStock();
      $list=$model->where($where)->visible(["spuCode","good_name",'spec_info','cat_info','usable_stock',
        'total_stock','wait_in_stock','wait_out_stock',"child"])->withAttr("child",function($v,$data) use($stock ){
        return	$stock->withJoin(["wareinfo"],"left")
        ->where("spuCode",$data['spuCode'])
        ->column("wareinfo.name,contactor_name,supplierName,companyName,usable_stock+wait_out_stock total_stock,usable_stock,wait_out_stock,wait_in_stock");
        
        })->paginate(["list_rows"=>$param['size'], "page"=>$param["page"]]);
        
        $this->success('获取成功',['result'=>$list->items(),"count"=>$list->total()]);
    }
    
    
        //发货单维度商品每日库存
    public function outChildStock(){
        $param =$this->request->param(['start'=>date('Y-m-01 00:00:00'),'end'=>date('Y-m-t 23:59:59'),'page'=>1,
    	'size'=>10]);
      $time = [$param['start'],$param['end']];
      $model = new OrderOutChild();
      $list = $model
      ->where(["status"=>2,"order_type"=>1,"is_del"=>0])
      ->whereBetween("sendtime",$time)
      ->field("spuCode,good_name,companyName,sum(num) num")
      ->group('good_name,companyName,spuCode')
      ->paginate(['list_rows'=>$param['size'], 'page'=>$param['page']]);
	    foreach ($list->items() as &$value){
	        $value['stock_num'] = (new GoodStock())->where('spuCode',$value['spuCode'])->sum('usable_stock');
			$value['good_price'] = (new PurcheaseOrder())->where(['spuCode'=>$value['spuCode'],'order_source'=>0])->value('good_price',0);
			$value['total_fee'] = bcmul(strval($value['good_price']),strval($value['num']),2);
	      }
       $this->success('获取成功',['result'=>$list->items(),'count'=>$list->total()]);
      
    }
}