<?php

namespace App\Http\Controllers;

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

class InventoryController extends Controller
{
    public function changeInventory(Request $request){
        $date_start=$request->input('date_start');
        $date_end=$request->input('date_end');
        $start=microtime(true);
        //$sql=' select * from (select ROWNUM r,result.*  from ( ';
        $sql=' select customer.Descr_C 货主, 库位, sku.SKU 产品编码, sku.ALTERNATE_SKU1 产品条码, ';
        $sql.=' sku.Descr_C 商品名称, lot.LotAtt05 属性仓, lot.LotAtt08 质量状态, lot.LotAtt02 失效日期, ';
        $sql.=' lot.LotAtt04 批号 ';
        $sql.=' , sum(移出数量)移出数量, sum(移入数量)移入数量 ';
        $sql.=' , storeStatus.QTY 在库数量, storeStatus.QtyAllocated 占用数量,count(1) over () as sum from ';
        $sql.=' (select FMLotNum,FMSKU,TOCustomerID,0 as 移出数量, sum(TOQty_Each) as 移入数量, TOLocation as 库位 ';
        $sql.=" from ACT_Transaction_Log where TransactionType='PA' ";
        if ($date_start) $sql.=" and addtime > '".$date_start." 00:00:00' ";
        if ($date_end) $sql.=" and addtime < '".$date_end." 23:59:59' ";
        $sql.=' group by TOCustomerID, TOLocation, TOCustomerID,FMSKU,FMLotNum union all ';
        $sql.=' select FMLotNum,FMSKU,TOCustomerID,sum(FMQty_Each) as 移出数量, 0 as 移入数量, FMLOCATION as 库位 ';
        $sql.=" from ACT_Transaction_Log where TransactionType='SO' ";
        if ($date_start) $sql.=" and addtime > '".$date_start." 00:00:00' ";
        if ($date_end) $sql.=" and addtime < '".$date_end." 23:59:59' ";
        $sql.=' group by FMCustomerID, FMLocation, TOCustomerID,FMSKU,FMLotNum union all ';
        $sql.=' select FMLotNum,FMSKU,TOCustomerID,sum(FMQty_Each) as 移出数量,0 as 移入数量, FMLocation as 库位 ';
        $sql.=" from ACT_Transaction_Log  where TransactionType='MV' ";
        if ($date_start) $sql.=" and addtime > '".$date_start." 00:00:00' ";
        if ($date_end) $sql.=" and addtime < '".$date_end." 23:59:59' ";
        $sql.=' group by FMLocation,TOCustomerID,FMSKU,FMLotNum union all ';
        $sql.=' select FMLotNum,FMSKU,TOCustomerID,0 as 移出数量,sum(TOQty_Each)as 移入数量, TOLocation as 库位 ';
        $sql.=" from ACT_Transaction_Log where TransactionType='MV' ";
        if ($date_start) $sql.=" and addtime > '".$date_start." 00:00:00' ";
        if ($date_end) $sql.=" and addtime < '".$date_end." 23:59:59' ";
        $sql.=' group by TOLocation,TOCustomerID,FMSKU,FMLotNum)stockLog ';
        $sql.=' left join BAS_Customer customer on customer.CustomerID=stockLog.TOCUSTOMERID ';
        $sql.=' left join BAS_SKU sku on sku.SKU=stockLog.FMSKU ';
        $sql.=' left join INV_LOT_ATT lot on lot.LOTNUM=stockLog.FMLOTNUM ';
        $sql.=' left join INV_LOT_LOC_ID storeStatus on storeStatus.LOTNUM=stockLog.FMLOTNUM ';
        $sql.=' left join INV_LOT_LOC_ID storeStatus on storeStatus.LOTNUM=stockLog.FMLOTNUM ';
        $sql.=' and storeStatus.LocationID=stockLog.库位 where rownum<=300 ';
        $sql.=' group by 库位,customer.Descr_C,sku.SKU,sku.ALTERNATE_SKU1 ';
        $sql.=' ,sku.Descr_C,FMLotNum,lot.LotAtt05,lot.LotAtt08,lot.LotAtt02,lot.LotAtt04 ';
        $sql.=' , storeStatus.QTY, storeStatus.QtyAllocated ';
        //$sql.=' ) result)data ';
        $page=$request->input('page')??1;
        /*$sql.=" where data.r>".($page-1)*50;
        $sql.=" and data.r<=".$page*50;*/
        $oracleActTransactingLogs=DB::connection('oracle')->select($sql);
        $oracleActTransactingLogs=json_encode($oracleActTransactingLogs);
        $end=microtime(true);
        $date=$end-$start;
        return view('inventory.statement.changeInventory',compact('oracleActTransactingLogs','page','date'));
    }
}