<?php

namespace App\Services;

use App\OracleActAllocationDetails;
use App\OracleDOCOrderHeader;
use App\OracleDOCOrderPackings;
use App\Traits\ServiceAppAop;
use Doctrine\DBAL\Driver\PDOConnection;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use PDO;

class ReviewService
{
    use ServiceAppAop;

    //单品复核


    /**
     * @param $IN_Warehouse
     * @param $IN_CustomerID
     * @param $IN_WaveNo
     * @param $IN_SKU
     * @param $IN_CheckModule
     * @param $IN_WorkStation
     * @param $IN_Language
     * @param $IN_UserID
     * @param $OUT_AllocationDetailsID
     * @param $OUT_TOID
     * @param $OUT_OrderNo
     * @param $OUT_Return_Code
     */
    public function single($IN_Warehouse, $IN_CustomerID, $IN_WaveNo, $IN_SKU, $IN_CheckModule, $IN_WorkStation, $IN_Language, $IN_UserID, $OUT_AllocationDetailsID, $OUT_TOID, $OUT_OrderNo, $OUT_Return_Code)
    {

        DB::beginTransaction();


        try {
            $r_CurrentTime = now()->toDateTimeString();
            $r_SGL_ORD_PCK = $this->getsys_configuration($IN_Warehouse, '*', '*', 'SGL_ORD_PCK');//防止并发错误
            $sql = <<<sql
Select a.OrderNo,AllocationDetailsID,a.SKU,PickToTraceID
                          ,c.CarrierID, requiredeliveryno, nvl(c.channel,' * ')
                         ,c.Erpcancelflag
                   From ACT_Allocation_Details a inner join
                    DOC_Order_Details b on a.OrderNo=b.OrderNo and a.OrderLineNo=b.OrderLineNo inner join
                     DOC_Order_Header c on b.OrderNo=c.OrderNo
sql;
            if ((!empty($IN_WaveNo)) && $IN_WaveNo !== '*') {
                $sql .= " inner join DOC_Wave_Details d on d.OrderNo=c.OrderNo  and d.WaveNo=:WaveNo  and a.WaveNo=:WaveNo ";
            }
            $sql .= " Where a.CustomerID=:CustomerID and a.SKU=:SKU and c.SingleMatch='Y'
                     and b.CommingleSKU='N' and a.PackFlag='N'
                     and c.releasestatus <> 'H'
                     and c.Warehouseid = :Warehouse
                     AND c.Sostatus < '80' ";
            if ($r_SGL_ORD_PCK === 'N' || $r_SGL_ORD_PCK === 'NO') {
                $sql .= " and a.status <'80' ";
            } else {
                $sql .= " and a.status <'60' ";
            }
            $sql .= " and not exists(select 1 from SYS_Check_Exception where AllocationDetailsID = a.AllocationDetailsID and  taskprocess<>'99' )
                     and rownum = 1 for update";
            $result = DB::connection('oracle')->selectOne(DB::raw($sql), [
                'WaveNo' => $IN_WaveNo,
                'CustomerID' => $IN_CustomerID,
                'Warehouse' => $IN_Warehouse,
            ]);
            $OUT_OrderNo = $result->OrderNo;
            $OUT_AllocationDetailsID = $result->AllocationDetailsID;
            $r_SKU = $result->SKU;
            $r_PickToTraceID = $result->PickToTraceID;
            $r_CarrierID = $result->CarrierID;
            $r_requiredeliveryno = $result->requiredeliveryno;
            $r_channel = $result->channel;
            $r_Erpcancelflag = $result->Erpcancelflag;
            //更新分配详情
            OracleActAllocationDetails::query()
                ->where('allocationdetailsid', $OUT_AllocationDetailsID)
                ->update([
                    'CheckModule' => $IN_CheckModule,
                ]);
            if ($r_Erpcancelflag === 'Y') {
                $OUT_Return_Code = '000CANCEL';
            }
            //如果复核时没有使用新的ID，则取分配明细的ID
            if (empty($OUT_TOID) || $OUT_TOID === '*') {
                $r_DEL_DOC_SN = $this->lock($IN_Warehouse, $IN_CustomerID, '*', 'DEL_DOC_SN#');
                if ($r_DEL_DOC_SN === 'C' && $r_requiredeliveryno === 'Y') {
                    $OUT_Return_Code = '*_*';
                    $r_ParaTemp = 'DELIVERYNO' . $OUT_OrderNo;
                    // 获取数据表的ID(PO, ASN, SO...)
                    list($IN_Warehouse, $IN_Language, $r_ParaTemp, $OUT_TOID, $OUT_Return_Code) = $this->SPCOM_GetIDSequence($IN_Warehouse, $IN_Language, $r_ParaTemp, $OUT_TOID, $OUT_Return_Code);
                    if (substr($OUT_Return_Code, 0, 3) !== '000') {
                        return;
                    }
                } else {
                    $OUT_TOID = $r_PickToTraceID;
                }
            }
            //取消装箱确认
            OracleDOCOrderPackings::query()
                ->where('TraceID', $OUT_TOID)
                ->delete();

            OracleDOCOrderPackings::query()->create([
                'TraceID' => $OUT_TOID,
                'SKU' => $IN_SKU,
                'OrderNo' => $OUT_OrderNo,
                'Qty' => 1,
                'AddTime' => $r_CurrentTime,
                'AddWho' => $IN_UserID,
                'EditTime' => $r_CurrentTime,
                'EditWho' => $IN_UserID,
                'AllocationDetailsID' => $OUT_AllocationDetailsID,
                'FROMTRACEID' => $OUT_TOID,
            ]);
            DB::commit();
            $OUT_Return_Code = '*_*SINGLE';


            list ($IN_Warehouse, $IN_CHK_TYP, $IN_TraceID, $IN_CheckModule, $IN_WorkStation, $IN_Language, $IN_UserID, $OUT_Return_Code) =
                $this->SPSO_Cartonization_Process($IN_Warehouse, 'ORDER', $OUT_TOID, $IN_CheckModule, $IN_WorkStation, $IN_Language, $IN_UserID, $OUT_Return_Code);
            if (substr($OUT_Return_Code, 1, 3) !== '000') {
                DB::rollBack();
                return;
            }
            if (!OracleActAllocationDetails::query()->where('orderno', $OUT_OrderNo)->where('PackFlag', 'N')->where('rownum', 1)->exists()) {
                OracleDOCOrderHeader::query()
                    ->where('orderno', $OUT_OrderNo)
                    ->update([
                        'ReleaseStatus' => 'Y',
                    ]);
            }
            DB::commit();
            $OUT_Return_Code = '000';
        } catch (\Exception $e) {
            DB::rollBack();
        }
    }

    public function getsys_configuration($IN_WarehouseID, $IN_CustomerID, $IN_OrderType, $IN_ConfigID, $IN_DefaultValue = 'N', $IN_ValueType = 'C')
    {
        $conn = $this->getOciConnection();
        $sql_sp = "begin GETSYS_configuration(
        :IN_WarehouseID,
        :IN_CustomerID,
        :IN_OrderType,
        :IN_ConfigID,
        :IN_DefaultValue,
        :IN_ValueType
        ); end;";
        $stmt = oci_parse($conn, $sql_sp);
        oci_bind_by_name($stmt, ':IN_WarehouseID', $IN_WarehouseID);
        oci_bind_by_name($stmt, ':IN_CustomerID', $IN_CustomerID);
        oci_bind_by_name($stmt, ':IN_OrderType', $IN_OrderType);
        oci_bind_by_name($stmt, ':IN_ConfigID', $IN_ConfigID);
        oci_bind_by_name($stmt, ':IN_DefaultValue', $IN_DefaultValue);
        oci_bind_by_name($stmt, ':IN_ValueType', $IN_ValueType);
        $r_SGL_ORD_PCK =  oci_execute($stmt);
        oci_close($conn);
        return $r_SGL_ORD_PCK;
    }

    public function SPSO_Cartonization_Process($IN_Warehouse, $IN_CHK_TYP, $IN_TraceID, $IN_CheckModule, $IN_WorkStation, $IN_Language, $IN_UserID, $OUT_Return_Code): array
    {
        $conn = $this->getOciConnection();
        $sql_sp = "begin SPCOM_GetIDSequence(
        :IN_Warehouse,
        :IN_CHK_TYP,
        :IN_TraceID,
        :IN_CheckModule,
        :IN_WorkStation
        :IN_Language
        :IN_UserID
        :OUT_Return_Code
        ); end;";
        $stmt = oci_parse($conn, $sql_sp);
        oci_bind_by_name($stmt, ':IN_Warehouse', $IN_Warehouse);
        oci_bind_by_name($stmt, ':IN_CHK_TYP', $IN_CHK_TYP);
        oci_bind_by_name($stmt, ':IN_TraceID', $IN_TraceID);
        oci_bind_by_name($stmt, ':IN_CheckModule', $IN_CheckModule);
        oci_bind_by_name($stmt, ':IN_WorkStation', $IN_WorkStation);
        oci_bind_by_name($stmt, ':IN_Language', $IN_Language);
        oci_bind_by_name($stmt, ':IN_UserID', $IN_UserID);
        oci_bind_by_name($stmt, ':OUT_Return_Code', $OUT_Return_Code);
        oci_execute($stmt);
        oci_close($conn);
        return array($IN_Warehouse, $IN_CHK_TYP, $IN_TraceID, $IN_CheckModule, $IN_WorkStation, $IN_Language, $IN_UserID, $OUT_Return_Code);
    }


    /**
     * 获取数据表的ID(PO, ASN, SO...)
     * @param $IN_Warehouse
     * @param $IN_Language
     * @param $r_ParaTemp
     * @param $OUT_TOID
     * @param $OUT_Return_Code
     * @return array
     */
    public function SPCOM_GetIDSequence($IN_Warehouse, $IN_Language, $r_ParaTemp, $OUT_TOID, $OUT_Return_Code): array
    {
        $conn = $this->getOciConnection();
        $sql_sp = "begin SPCOM_GetIDSequence(
        :IN_Warehouse,
        :IN_Language,
        :r_ParaTemp,
        :OUT_TOID,
        :OUT_Return_Code
        ); end;";
        $stmt = oci_parse($conn, $sql_sp);
        oci_bind_by_name($stmt, ':IN_Warehouse', $IN_Warehouse);
        oci_bind_by_name($stmt, ':IN_Language', $IN_Language);
        oci_bind_by_name($stmt, ':r_ParaTemp', $r_ParaTemp);
        oci_bind_by_name($stmt, ':OUT_TOID', $OUT_TOID);
        oci_bind_by_name($stmt, ':OUT_Return_Code', $OUT_Return_Code);
        oci_execute($stmt);
        oci_close($conn);
        return array($IN_Warehouse, $IN_Language, $r_ParaTemp, $OUT_TOID, $OUT_Return_Code);
    }

    /**
     * @return false|resource
     */
    private function getOciConnection()
    {
        $username = config('database.connections.oracle.username');
        $password = config('database.connections.oracle.password');
        $host = config('database.connections.oracle.host');
        $service_name = config('database.connections.oracle.service_name');
        return oci_connect($username, $password, $host . '/' . $service_name, 'utf8'); //连接oracle数据库
    }
}