<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class StoreOutReviewController extends Controller
{
    public function index(Request $request)
    {
        return view("storeOut.storeOutReview.index");
    }


    public function apiGetOrderDetail(Request $request)
    {
        $orderno = $request->input('orderno');
        $orderDetail = DB::connection("oracle")->select(
            "
            SELECT
        rtrim( 'ACT_ALLOCATION_DETAILS.SKU=' || cast( act_allocation_details.SKU AS VARCHAR2 ( 100 ) ) ) || ',' || rtrim( 'ACT_ALLOCATION_DETAILS.ALLOCATIONDETAILSID=' || cast( act_allocation_details.ALLOCATIONDETAILSID AS VARCHAR2 ( 100 ) ) ) PKEY,
        act_allocation_details.OrderNo,
        act_allocation_details.Orderlineno,
        act_allocation_details.skulineno,
        act_allocation_details.customerId,
        act_allocation_details.SKU,
        bas_sku.DESCR_C SKUDESCRC,
        bas_sku.DESCR_E SKUDESCRE,
        doc_order_header.ROUTE,
        DOC_Order_Details.ERPCancelFlag,
        act_allocation_details.PACKID,
        b1.descr UOM_EACH_NAME,
        act_allocation_details.Palletize,
        doc_order_header.notes AS ORDERNOTES,
        doc_order_header.CarrierID,
        doc_order_header.OrderType,
        rdc.CartonGroup AS CARTONGROUP1,
        act_allocation_details.PICKTOTRACEID,
        act_allocation_details.LOCATION,
        act_allocation_details.AllocationDetailsID,
        bas_sku.SerialNoCatch,
        bas_sku.OutboundSerialNoQtyControl,
        bas_sku.SecondSerialNoCatch,
        bas_sku.CHK_SCN_UOM,
        bas_sku.ScanWhenCasePicking,
        bas_sku.SCANWHENCHECK,
        bas_sku.SKU_Group1,
        bas_sku.GROSSWEIGHT,
        bas_sku.ScanWhenReceive,
        bas_sku.ScanWhenPutaway,
        bcf.SerialNo_RuleID,
        bcf.SubSerialNo_RuleID,
        bcf.SN_BCH,
        bcf.SN_INC,
        doc_order_header.SerialNoCatch AS ORDERSERIALNOCATCH,
        doc_order_header.InvoicePrintFlag,
        doc_order_header.OrderHandleInstruction,
        doc_order_header.CartonGroup AS CARTONGROUPORDERHEADER,
        doc_order_header.CartonID AS CARTONIDORDERHEADER,
        act_allocation_details.LOTNUM,
        act_allocation_details.qty_each AS QTY,
        act_allocation_details.LOTNUM,
        act_allocation_details.qty_each AS UNCHECKQTY,
        act_allocation_details.notes,
        bas_sku.CARTONGROUP,
        act_allocation_details.status,
        c5.CODENAME_C AS ROUTE_NAME,
        c.CODENAME_C AS STATUS_NAME,
        bas_sku.ALTERNATE_SKU1,
        bas_sku.ALTERNATE_SKU2,
        bas_sku.ALTERNATE_SKU3,
        bas_sku.ALTERNATE_SKU4,
        bas_sku.ALTERNATE_SKU5,
        act_allocation_details.PACKFLAG,
        bas_sku.ImageAddress,
        bas_sku.cube,
        '' AS SKU_LABEL,
        SUM( CASE WHEN act_allocation_details.PackFlag = 'Y' THEN act_allocation_details.Qty_Each ELSE 0 END ) over ( partition BY act_allocation_details.orderno ) AS PACKEDQTY,
        SUM( CASE WHEN act_allocation_details.PackFlag = 'Y' THEN 0 ELSE act_allocation_details.Qty_Each END ) over ( partition BY act_allocation_details.orderno ) AS UNPACKQTY,
        SUM( CASE WHEN act_allocation_details.PackFlag = 'Y' THEN 0 ELSE act_allocation_details.Cubic END ) over ( partition BY act_allocation_details.orderno ) AS TOTALCUBIC,
        INV_LOT_ATT.QCREPORTFILENAME AS QCREPORTFILENAME,
        INV_LOT_ATT.LotAtt01 AS LotAtt01,
        INV_LOT_ATT.LotAtt02 AS LotAtt02,
        INV_LOT_ATT.LotAtt03 AS LotAtt03,
        INV_LOT_ATT.LotAtt04 AS LotAtt04,
        INV_LOT_ATT.LotAtt05 AS LotAtt05,
        INV_LOT_ATT.LotAtt06 AS LotAtt06,
        INV_LOT_ATT.LotAtt07 AS LotAtt07,
        INV_LOT_ATT.LotAtt08 AS LotAtt08,
        INV_LOT_ATT.LotAtt09 AS LotAtt09,
        INV_LOT_ATT.LotAtt10 AS LotAtt10,
        INV_LOT_ATT.LotAtt11 AS LotAtt11,
        INV_LOT_ATT.LotAtt12 AS LotAtt12,
        doc_order_header.CONSIGNEEID,
        doc_order_header.CONSIGNEENAME CONSIGNEEID_NAME,
        BAS_Customer.IMAGEADDRESS AS CONSIGNEEIMAGEADDRESS,
        act_allocation_details.uom,
        doc_order_header.CustomerID,
        BAS_Customer_OW.Descr_C AS CUSTOMERID_NAME,
        doc_order_header.CONSIGNEENAME AS CONSIGNEENAME,
        doc_order_header.C_Address1 AS C_ADDRESS1,
        doc_order_header.C_Contact AS CONSIGNEECONTACT,
        doc_order_header.C_Tel1 AS CONSIGNEETEL1,
        doc_order_header.SOREFERENCE1,
        doc_order_header.SOREFERENCE2,
        doc_order_header.SOREFERENCE3,
        doc_order_header.SOREFERENCE4,
        doc_order_header.SOREFERENCE5,
        BAS_Customer_OW.SKUANALYSISFIELDS,
        bas_sku.LotID,
        DOC_Order_Details.FreeGift,
        doc_order_header.Warehouseid,
        doc_order_header.waveNo,
        act_allocation_details.pickedWho,
        TO_CHAR( act_allocation_details.pickedtime, 'yyyy-MM-dd HH24:MI' ) AS PICKEDTIME,
        BAS_Location.WorkingArea
FROM
    ACT_Allocation_Details act_allocation_details
        LEFT JOIN BAS_SKU bas_sku ON bas_sku.SKU = act_allocation_details.SKU
        AND bas_sku.CUSTOMERID = act_allocation_details.CUSTOMERID
        LEFT JOIN INV_LOT_ATT INV_LOT_ATT ON INV_LOT_ATT.LOTNUM = act_allocation_details.LOTNUM
        LEFT JOIN view_cod_so_sts c ON act_allocation_details.status = c.code
        LEFT JOIN doc_order_header doc_order_header ON doc_order_header.OrderNo = act_allocation_details.OrderNo
        LEFT JOIN DOC_Order_Details DOC_Order_Details ON act_allocation_details.OrderNo = DOC_Order_Details.OrderNo
        AND act_allocation_details.OrderLineNo = DOC_Order_Details.OrderLineNo
        LEFT JOIN BAS_Customer BAS_Customer ON doc_order_header.CONSIGNEEID = BAS_Customer.CustomerID
        AND BAS_Customer.Customer_Type = 'CO'
        LEFT JOIN BAS_Customer BAS_Customer_OW ON doc_order_header.CustomerID = BAS_Customer_OW.CustomerID
        AND BAS_Customer_OW.Customer_Type = 'OW'
        LEFT JOIN VIEW_COD_ROU_COD c5 ON doc_order_header.ROUTE = c5.code
        LEFT JOIN view_uom b1 ON b1.uom = 'EA'
        AND act_allocation_details.packid = b1.packid
        LEFT JOIN BAS_CustomerFreight bcf ON bas_sku.customerid = bcf.customerid
        AND bas_sku.FreightClass = bcf.FreightCode
        LEFT JOIN RUL_Delivery_Config rdc ON doc_order_header.Warehouseid = rdc.WarehouseID
        AND doc_order_header.CustomerID = rdc.CustomerID
        AND doc_order_header.CarrierID = rdc.CarrierID
        LEFT JOIN BAS_Location BAS_Location ON act_allocation_details.Location = BAS_Location.locationId
WHERE
        1 = 1
  AND ( act_allocation_details.Status >= '40' AND act_allocation_details.Status <= '60' )   --分配完成 | 部分拣货 |拣货完成
  AND act_allocation_details.ORDERNO = ?
  AND act_allocation_details.PackFlag <> 'Y'
ORDER BY
    act_allocation_details.PACKFLAG,
    act_allocation_details.status DESC,
    act_allocation_details.sku,
    act_allocation_details.ALLOCATIONDETAILSID
", [$orderno]);
        return ['success' => true, 'data' => $orderDetail];
    }

    public function review(Request $request)
    {
        DB::transaction(function ($request) {
            //普通复核的长宽高由WAS回写
            //               ('202108160001','SO21081000004','',1.00000,3,0,0,0,1.00000,sysdate,'liutengfei',sysdate,'liutengfei')
            $TRACEID = $request->input('traceid');
            $ORDERNO = $request->input('orderno');
            $CARTONGROUP = '';
            $GROSSWEIGHT = $request->input('grossweight');//总重
            $QTY = $request->input('qty');//数量
            $LENGTH = 0;
            $WIDTH = 0;
            $HEIGHT = 0;
            $STANDARDWEIGHT = $request->input('standardweight');
            $ADDTIME = now()->toDateTimeString();
            $ADDWHO = auth()->user()->name;
            $EDITTIME = now()->toDateTimeString();
            $EDITWHO = auth()->user()->name;
            DB::insert("
        INSERT INTO DOC_Order_Packing_Summary
            (TRACEID,ORDERNO,CARTONGROUP,GROSSWEIGHT,QTY,LENGTH,WIDTH,HEIGHT,STANDARDWEIGHT,ADDTIME,ADDWHO,EDITTIME,EDITWHO)
        VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", [$TRACEID, $ORDERNO, $CARTONGROUP, $GROSSWEIGHT, $QTY, $LENGTH, $WIDTH, $HEIGHT, $STANDARDWEIGHT, $ADDTIME, $ADDWHO, $EDITTIME, $EDITWHO,]);
        });
    }
}