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");
            //检测海柔的有效料箱并剔除有任务待处理的料箱
            list($codes,$notCodes) = $this->checkHaiQ($boxCodes);
            if (!$codes)continue;
            //剔除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;
    }

    public function checkHaiQ(array $boxCodes)
    {
        $haiBoxes = DB::connection("mysql_haiRobotics")->table("ks_bin")
            ->where("status",1)->whereIn("ks_bin_code",$boxCodes)->get();
        if ($haiBoxes->count()==0)return array(null,null);
        $codes = [];
        $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();
        $notCodes = [];
        $haiBoxes->each(function ($haiBox)use(&$notCodes){$notCodes[$haiBox->bin_code] = true;});
        return array($codes,$notCodes);
    }

    /**
     * 获取所有者排序的空盒子
     *
     * @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
     * @param bool $sqlQuery
     * @param array $blacklist
     *
     * @return Collection|string|null|array
     */
    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->select("id")->whereHas("performTask"));
        if ($blacklist)$boxes = $boxes->whereNotIn("id",$blacklist);
        $boxes = $boxes->get();

        //筛选下海柔可用箱
        list($codes,$notCodes) = $this->checkHaiQ(array_column($boxes->toArray(),"code"));
        if (!$codes)$boxes = new \Illuminate\Database\Eloquent\Collection();
        $boxes = $boxes->filter(function ($box)use($codes,$notCodes){
            if (!isset($codes[$box->code]) || isset($notCodes[$box->code]))return false;
            return true;
        });

        if (!$sqlQuery)return $boxes;
        if ($boxes->count()==0)return array(null,null);
        $boxCodes = "";
        $map = [];
        foreach ($boxes as $box){
            $boxCodes .= "'".$box->code."',";
            $map[$box->code] = $box->id;
        }
        return array(rtrim($boxCodes,","),$map);
    }
}