baoshi-was/app/Services/MaterialBoxService.php

<?php


namespace App\Services;


use App\MaterialBox;
use App\MaterialBoxModel;
use App\StationTaskMaterialBox;
use App\Traits\ServiceAppAop;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;


class MaterialBoxService
{
    use ServiceAppAop;
    protected $modelClass=MaterialBox::class;


    /**
     * 获取一个空料箱
     *
     * @param MaterialBoxModel|\stdClass $model
     * @param array $blacklist
     *
     * @return MaterialBox|null
     */
    public function getAnEmptyBox(MaterialBoxModel $model,array $blacklist = []):?MaterialBox
    {
        $id = 0;
        while (true){
            //检测WAS
            $boxes = MaterialBox::query()->select('id',"code")
                ->whereNotIn("id",$blacklist)
                ->where("id",">",$id)->where("code","like","IDE%")
                ->where("status",4)->limit(50)->orderBy("id")
                ->whereNotIn("id",StationTaskMaterialBox::query()->select("material_box_id")
                    ->whereNotIn("status",["完成","取消"])
                    ->where("material_box_model_id",$model->id)->groupBy("material_box_id"));
            $boxes = $boxes->get();
            if ($boxes->count()==0)break;
            $id = $boxes[count($boxes)-1]->id;
            $boxCodes = array_column($boxes->toArray(),"code");
            //检测海柔的有效料箱
            $haiBoxes = DB::connection("mysql_haiRobotics")->table("ks_bin")
                ->where("status",1)->whereIn("ks_bin_code",$boxCodes)->get();
            if ($haiBoxes->count()==0)continue;
            $haiBoxes->each(function ($haiBox)use(&$codes){$codes[$haiBox->ks_bin_code] = true;});
            //剔除有任务待处理的料箱
            $haiBoxes = DB::connection("mysql_haiRobotics_ess")->table("ks_ess_task_detail")
                ->whereIn("bin_code",$boxCodes)
                ->whereNotIn("status",[0,4])->get();
            $haiBoxes->each(function ($haiBox)use(&$notCodes){$notCodes[$haiBox->bin_code] = true;});

            //剔除FLUX已有库存并且库位内种类超出的料箱
            $ides = [];
            $str = "(";
            foreach ($boxes as $box){
                if (!isset($codes[$box->code]) || isset($notCodes[$box->code]))continue;
                $str .= "'".$box->code."',";
                $ides[$box->code] = $box;
            }
            if (!$ides)continue;
            $str = rtrim($str,",").")";
            $sql = <<<sql
SELECT LOCATIONID FROM (SELECT LOCATIONID FROM (SELECT LOCATIONID,SUM(QTY+QTYPA) qty FROM INV_LOT_LOC_ID WHERE LOCATIONID IN {$str} GROUP BY LOCATIONID,LOTNUM,CUSTOMERID,SKU) where qty>0) GROUP BY
                            LOCATIONID HAVING (COUNT(*)>={$model->maximum_kind})
sql;
            foreach (DB::connection("oracle")->select(DB::raw($sql)) as $item)unset($ides[$item->locationid]);
            if ($ides)return current($ides);
        }
        return null;
    }

    /**
     * 获取所有者排序的空盒子
     *
     * @param integer|null $ownerId
     * @param array $blacklist
     *
     * @return MaterialBox|null
     */
    public function getAnEmptyBoxSortedByOwner(?int $ownerId=null, array $blacklist=[]):?MaterialBox
    {
        $models = app("MaterialBoxModelService")->getModelSortedByOwner($ownerId);
        foreach ($models as $model){
            $box = $this->getAnEmptyBox($model,$blacklist);
            if ($box)return $box;
        }
        return null;
    }

    /**
     * 检查可用料箱
     *
     * @param integer $boxId
     *
     * @return bool
     */
    public function checkUsableBox(int $boxId):bool
    {
        /** @var MaterialBox|\stdClass $box */
        if (StationTaskMaterialBox::query()->select(DB::raw(1))->whereNotIn("status",['完成','取消'])->where("material_box_id",$boxId)->first())return false;
        return true;
    }

    /**
     * 获取某个型号下的可用料箱
     *
     * @param integer $modelId
     *
     * @return Collection|string|null
     */
    public function getModelAvailableBox(int $modelId, bool $sqlQuery = false, array $blacklist = [])
    {
        $query = MaterialBox::query()->where("material_box_model_id",$modelId)->where("status",4);
        $boxes = $query->whereNotIn("id",$query->whereHas("performTask"))
            ->whereNotIn("id",$blacklist)->get();
        if (!$sqlQuery)return $boxes;
        if ($boxes->count()==0)return null;
        $boxCodes = "";
        foreach ($boxes as $box){
            $boxCodes .= "'".$box->code."',";
        }
        return rtrim($boxCodes,",");
    }
}