baoshi-was/app/Services/StoreItemService.php

<?php

namespace App\Services;

use App\OracleDOCASNDetail;
use App\Owner;
use App\Services\common\BatchUpdateService;
use App\Services\common\DataHandlerService;
use App\Store;
use App\StoreItem;
use App\Traits\ServiceAppAop;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;


class StoreItemService
{
    use ServiceAppAop;
    protected $modelClass=StoreItem::class;

    public function storeItemCreateByWms($asnHerders)
    {
        if (!$asnHerders) return null;
        $asnDetails = $this->getAsnDetailsByAsnHerder($asnHerders);
        $this->createStoreItem($asnDetails);
    }

    public function storeItemUpdateByWms($asnHerders)
    {
        if (!$asnHerders) return null;
        $asnDetails = $this->getAsnDetailsByAsnHerder($asnHerders);
        $this->updateStoreItem($asnDetails);
    }

    public function getAsnDetailsByAsnHerder($asnHerders)
    {
        $asnnos = collect();
        foreach ($asnHerders as $asnHerder) {
            $asnnos->add($asnHerder->asnno);
        }
        $asnnos = $asnnos->unique()->toArray();
        $asnDetails=OracleDOCASNDetail::query()
            ->with(['lineStatus', 'qualityStatus'])
            ->whereIn('asnno',$asnnos)
            ->select('asnno','asnlineno','customerid','sku','skudescrc','linestatus','lotatt08','lotatt05','receivedqty','expectedqty','addtime','edittime')
            ->get();
        //dd($asnDetails->first());
//        $db = DB::connection('oracle');
//        $sql = <<<sql
//            SELECT d.asnno,d.ASNLINENO,d.SKUDESCRC,d.CUSTOMERID,d.SKU,c.CODENAME_C as lotatt08,b.CODENAME_C as linestatus,d.lotatt05,
//            d.receivedqty,d.expectedqty,d.addtime,d.edittime FROM DOC_ASN_DETAILS d
//            LEFT JOIN BAS_CODES b ON d.linestatus = b.code AND 'ASN_STS' = b.codeid
//            LEFT JOIN BAS_CODES c ON d.lotatt08 = c.code AND 'QLT_STS' = c.codeid WHERE d.ASNNO in (
//sql;
//        $asno_str = '';
//        foreach ($asnnos as $str) $asno_str .= "'" . $str . "',";
//        $asno_str = rtrim($asno_str, ',');
//        $sql=$sql.$asno_str.")";
//        $asnDetails = $db->select(DB::raw($sql));
//        $asnDetails=collect($asnDetails);


//        $asnDetails = collect();
//        $asnHerders->each(function ($asnHeader) use ($asnDetails) {
//            $asnHeader->asnDetails->each(function ($asnDetail) use ($asnDetails) {
//                $asnDetails->add($asnDetail);
//            });
//        });
        return $asnDetails;
    }

    public function createStoreItem($asnDetails)
    {
        if ($asnDetails->isEmpty()) return null;
        ini_set('memory_limit', '512M');
        $stores = Store::query()->whereIn('asn_code', array_unique(data_get($asnDetails, '*.asnno')))->get();

        $store_asn_code_map = [];
        foreach ($stores as $store) {
            $store_asn_code_map[$store->asn_code] = $store;
        }
        $params = $this->getParamsByAsnDetails($asnDetails, $store_asn_code_map);
        if (count($params) > 0) $this->insertStoreItem($params);

    }

    public function getParamsByAsnDetails($asnDetails, $store_asn_code_map)
    {
        /**
         * @var DataHandlerService $dataHandlerService
         * @var CommodityService $commodityService
         * @var OwnerService $ownerService
         */
        $dataHandlerService = app(DataHandlerService::class);
        $commodityService = app(CommodityService::class);
        $ownerService = app(OwnerService::class);
        $owners = $ownerService->getOwnerByCodes(array_unique(data_get($asnDetails,'*.customerid')));
        $owner_map=[];
        foreach ($owners as $owner){
            $owner_map[$owner->code]=$owner->id;
        }
        $maps = [];
        foreach ($asnDetails as $asnDetail) {
            $value = [
                'owner_code' => $asnDetail->customerid,
                'sku' => $asnDetail->sku,
            ];
            $maps[json_encode($value)] = $value;
        }
        $commodities=$commodityService->getCommoditiesByMaps($maps);
        $commodities_map= $dataHandlerService->dataHeader(['owner_id','sku'], $commodities);
        $storeItems = $this->getByWms($asnDetails);
        $storeItem_map = $dataHandlerService->dataHeader(['store_asn_code', 'asn_line_code', 'commodity_id'], $storeItems);
        $params = [];
        foreach ($asnDetails as $asnDetail) {
            $commodity=$dataHandlerService->getKeyValue(['owner_id'=>$owner_map[$asnDetail->customerid],'sku'=>$asnDetail->sku],$commodities_map);
            if (!$commodity)continue;
            $storeItem = $dataHandlerService
                ->getKeyValue(['store_asn_code' => $asnDetail->asnno, 'asn_line_code' => $asnDetail->asnlineno, 'commodity_id' => $commodity->id], $storeItem_map);
            if ($storeItem ?? false) continue;
            $status = null;
            if ($asnDetail->lineStatus && $asnDetail->lineStatus->codename_c == '完全收货') $status = '已入库';
            if ($asnDetail->lineStatus && $asnDetail->lineStatus->codename_c == '订单创建') $status = '未入库';
            //if (empty($store_asn_code_map[$asnDetail->asnno])) continue;
            $params[] = [
                'store_id' => $store_asn_code_map[$asnDetail->asnno]['id'],
                'asn_line_code' => (string)$asnDetail->asnlineno,
                'commodity_id' => $commodity->id,
                'name' => $asnDetail->skudescrc,
                'sku' => $asnDetail->sku,
                'amount' => $asnDetail->receivedqty ?? 0,
                'expected_amount' => $asnDetail->expectedqty ?? 0,
                'quality' => $asnDetail->qualityStatus ? $asnDetail->qualityStatus->codename_c :'',
                'status' => $status ? $status : $asnDetail->lineStatus->codename_c,
                'created_at' => $asnDetail->addtime ?? null,
                'updated_at' => $asnDetail->edittime ?? null,
            ];
        }
        return $params;
    }

    public function insertStoreItem(array $params)
    {
        if (count($params) === 0) return;
        foreach (array_chunk($params, 1000) as $item) {
            try {
                $bool = $this->insert($item);
                if ($bool) {
                    app('LogService')->log(__METHOD__, __FUNCTION__, "批量创建 store_item success " . count($item) . ' || ' . json_encode($item));
                } else app('LogService')->log(__METHOD__, __FUNCTION__, "批量添加 store_item FAILED " . ' || ' . json_encode($item));
            } catch (\Exception $e) {
                app('LogService')->log(__METHOD__, __FUNCTION__, "批量添加 store_item ERROR " . ' || ' . json_encode($params) . ' || ' . json_encode($e->getMessage()) . ' || ' . json_encode($e->getTraceAsString()));
            }
        }
    }

    public function updateStoreItem($asnDetails)
    {
        if (!$asnDetails || $asnDetails->count() == 0) return null;
        /**
         * @var DataHandlerService $dataHandlerService
         * @var CommodityService $commodityService
         * @var OwnerService $ownerService
         */
        $dataHandlerService = app(DataHandlerService::class);
        $commodityService = app(CommodityService::class);
        $ownerService = app(OwnerService::class);
        $owners = $ownerService->getOwnerByCodes(array_unique(data_get($asnDetails,'*.customerid')));
        $owner_map=[];
        foreach ($owners as $owner){
            $owner_map[$owner->id]=$owner->code;
        }
        $maps = [];
        foreach ($asnDetails as $asnDetail) {
            $value = [
                'owner_code' => $asnDetail->customerid,
                'sku' => $asnDetail->sku,
            ];
            $maps[json_encode($value)] = $value;
        }
        $commodities=$commodityService->getCommoditiesByMaps($maps);
        $commodities_map= $dataHandlerService->dataHeader(['id'], $commodities);
        $storeItems = $this->getByWms($asnDetails);
        $asnDetails_map = $dataHandlerService->dataHeader(['asnno', 'asnlineno','customerid','sku'], $asnDetails);
        $updateParams = [[
            'id', 'store_id', 'asn_line_code', 'name', 'sku', 'barcode', 'amount','expected_amount', 'quality', 'status', 'created_at', 'updated_at'
        ]];
        $delete_storeItems = [];
        foreach ($storeItems as $storeItem) {
            $commodity= $dataHandlerService->getKeyValue(['id'=>$storeItem->commodity_id],$commodities_map);if (!$commodity) continue;
            $asnDetail = $dataHandlerService
                ->getKeyValue(['asnno' => $storeItem->store_asn_code, 'asnlineno' => $storeItem->asn_line_code,'customerid'=>$owner_map[$commodity->owner_id],'sku' => $commodity->sku], $asnDetails_map);
            if (!$asnDetail) {
                array_push($delete_storeItems, $storeItem);
                continue;
            }
            $status = null;
            if ($asnDetail['lineStatus'] && $asnDetail['lineStatus']['codename_c'] == '完全收货') $status = '已入库';
            if ($asnDetail['lineStatus'] && $asnDetail['lineStatus']['codename_c'] == '订单创建') $status = '未入库';
            if ($storeItem->updated_at != $asnDetail['edittime']
                || $storeItem->expected_amount!=$asnDetail['expectedqty']
            ) {
                $updateParams[] = [
                    'id' => $storeItem->id,
                    'store_id' => $storeItem->store->id,
                    'asn_line_code' => (string)$asnDetail['asnlineno'],
                    'name' => $asnDetail['skudescrc'],
                    'sku' => $asnDetail['sku'],
                    'barcode' => $asnDetail['basSku'] ? $asnDetail['basSku']['alternate_sku1'] : '',
                    'amount' => $asnDetail['receivedqty'] ?? 0,
                    'expected_amount' => $asnDetail['expectedqty'] ?? 0,
                    'quality' => $asnDetail['qualityStatus'] ? $asnDetail['qualityStatus']['codename_c'] : '',
                    'status' => $status ? $status : $asnDetail['lineStatus']['codename_c'],
                    'created_at' => $asnDetail['addtime'] ?? null,
                    'updated_at' => $asnDetail['edittime'],
                ];
            }
        }
        if (count($updateParams) > 1) $this->batchUpdate($updateParams);
        if (count($delete_storeItems) > 0) $this->deleteStoreItem($delete_storeItems);
        unset($updateParams, $asnDetails, $delete_storeItems);
    }

    public function batchUpdate($params)
    {
        return app(BatchUpdateService::class)->batchUpdate('store_items', $params);
    }

    public function getByWms($asnDetails)
    {
        if (!$asnDetails) {
            return null;
        }
        return StoreItem::query()->with('store')->whereIn('store_id', function ($query) use ($asnDetails) {
            $query->from('stores')->select('id')->whereIn('asn_code', array_unique(data_get($asnDetails, '*.asnno')));
        })->get();
    }

    public function deleteStoreItem(array $delete_storeItems)
    {
        $storeItemIds = [];
        foreach ($delete_storeItems as $delete_storeItem) {
            array_push($storeItemIds, $delete_storeItem->id);
        }
        try {
            StoreItem::query()->whereIn('id', $storeItemIds)->delete();
            LogService::log(__METHOD__, __FUNCTION__, '删除多余StoreItems ' . count($delete_storeItems) . json_encode($delete_storeItems), null);
        } catch (\Exception $e) {
            LogService::log(__METHOD__, __FUNCTION__, '删除多余StoreItems error' . count($delete_storeItems) . json_encode($delete_storeItems) . $e->getMessage() . $e->getTraceAsString(), null);
        }
    }

    /**
     * 获取最大可上架数的详情项
     *
     * @param string $asn
     * @param string $barCode
     *
     * @return StoreItem|Model|null
     */
    public function getMaxAvailableDetail(string $asn,string $barCode):?StoreItem
    {
        $sql = <<<SQL
SELECT DOC_ASN_DETAILS.ASNLINENO,SUM(TSK_TASKLISTS.FMQTY) QTY FROM DOC_ASN_DETAILS LEFT JOIN TSK_TASKLISTS ON DOC_ASN_DETAILS.ASNNO = TSK_TASKLISTS.DOCNO AND DOC_ASN_DETAILS.ASNLINENO = TSK_TASKLISTS.DOCLINENO
LEFT JOIN BAS_SKU ON DOC_ASN_DETAILS.SKU = BAS_SKU.SKU AND DOC_ASN_DETAILS.CUSTOMERID = BAS_SKU.CUSTOMERID
WHERE DOC_ASN_DETAILS.ASNNO = ? AND DOC_ASN_DETAILS.LINESTATUS IN ('30','40')
  AND (BAS_SKU.ALTERNATE_SKU1 = ? OR ALTERNATE_SKU2 = ? AND ALTERNATE_SKU3 = ?)
  AND TSK_TASKLISTS.TASKTYPE = 'PA' AND TASKPROCESS = '00'
GROUP BY (DOC_ASN_DETAILS.ASNLINENO,DOC_ASN_DETAILS.ASNNO) ORDER BY QTY DESC
SQL;
        $detail = DB::connection("oracle")->selectOne(DB::raw($sql),[$asn,$barCode,$barCode,$barCode]);
        if (!$detail)return null;
        return StoreItem::query()->whereHas("store",function (Builder $query)use($asn){
            $query->where("asn_code",$asn);
        })->where("asn_line_code",$detail->asnlineno)->first();
    }
}