baoshi-was/app/Http/Controllers/InventoryController.php

<?php

namespace App\Http\Controllers;

use App\Exports\Export;
use App\OracleBasCustomer;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Gate;
use Maatwebsite\Excel\Facades\Excel;

class InventoryController extends Controller
{

    private function conditionQuery(Request $request,$page=null,$paginate=null){
        $date_start=$request->input('date_start');
        $range = $request->input('range');
        if ($range)$date_start=date('Y-m-d',strtotime('-'.$range." day"));
        $date_end=$request->input('date_end');
        $TOLocation=$request->input('TOLocation');
        $LotAtt05=$request->input('LotAtt05');
        $LotAtt02_start=$request->input('LotAtt02_start');
        $descr_c=$request->input('descr_c');
        $SKU=$request->input('SKU');
        $ALTERNATE_SKU1=$request->input('ALTERNATE_SKU1');
        $LotAtt02_end=$request->input('LotAtt02_end');
        $sql='select * from (select result.*,rownum rn from (';
        $sql.=' select customer.Descr_C as 货主,stockLog.客户 客户, 库位, 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 > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
        if ($TOLocation)$sql .= " and TOLocation like '".$TOLocation."' ";
        if ($SKU)$sql.=" and FMSKU like '".$SKU."' ";
        $sql.=' group by TOCustomerID, TOLocation,FMSKU,FMLotNum union all ';
        $sql.=' select FMLotNum,FMSKU,FMCUSTOMERID 客户,sum(FMQty_Each) as 移出数量, 0 as 移入数量, FMLOCATION as 库位 ';
        $sql.=" from ACT_Transaction_Log where TransactionType='SO' ";
        if ($date_start) $sql.=" and addtime > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
        if ($TOLocation)$sql .= " and FMLOCATION like '".$TOLocation."' ";
        if ($SKU)$sql.=" and FMSKU like '".$SKU."' ";
        $sql.=' group by FMCustomerID, FMLocation,FMSKU,FMLotNum union all ';
        $sql.=' select FMLotNum,FMSKU,FMCUSTOMERID 客户,sum(FMQty_Each) as 移出数量,0 as 移入数量, FMLocation as 库位 ';
        $sql.=" from ACT_Transaction_Log  where TransactionType='MV' ";
        if ($date_start) $sql.=" and addtime > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
        if ($TOLocation)$sql .= " and FMLocation like '".$TOLocation."' ";
        if ($SKU)$sql.=" and FMSKU like '".$SKU."' ";
        $sql.=' group by FMLocation,FMCUSTOMERID,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 > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
        if ($TOLocation)$sql .= " and TOLocation like '".$TOLocation."' ";
        if ($SKU)$sql.=" and FMSKU like '".$SKU."' ";
        $sql.=' group by TOLocation,TOCustomerID,FMSKU,FMLotNum)stockLog ';
        $sql.=' left join BAS_Customer customer on customer.CustomerID=stockLog.客户 ';
        $sql.=' left join BAS_SKU sku on sku.SKU=stockLog.FMSKU and sku.CUSTOMERID=stockLog.客户 ';
        $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.=' and storeStatus.LocationID=stockLog.库位 ';
        $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,stockLog.客户  ';
        $sql.=' )result where 1=1 ';
        if ($LotAtt05)$sql .=" and 属性仓 like '".$LotAtt05."' ";
        if ($LotAtt02_start)$sql.=" and 失效日期 >='".$LotAtt02_start." 00:00:00' ";
        if ($LotAtt02_end)$sql.=" and 失效日期 <='".$LotAtt02_end." 23:59:59' ";
        if ($descr_c){
            $sql .= ' and 货主 in (';
            $descr_cs = explode(',',$descr_c);
            foreach ($descr_cs as $index => $descr_c){
                if ($index != 0)$sql .= ',';
                $sql .= "'".$descr_c."'";
            }
            $sql .= ') ';
        }
        if ($ALTERNATE_SKU1)$sql.=" and 产品条码 like '".$ALTERNATE_SKU1."' ";
        if ($page&&$paginate)$sql.="  and ROWNUM<='".$page*$paginate."'";
        $sql.=' )  ';
        if ($page&&$paginate)$sql.="  where rn>'".($page-1)*$paginate."'";
        return DB::connection('oracle')->select($sql);
    }
    private function conditionQueryAllInventory(Request $request,$page=null,$paginate=null){
        $date_start=$request->date_start;
        $range = $request->range;
        if ($range)$date_start=date('Y-m-d',strtotime('-'.$range." day"));
        $date_end=$request->date_end;
        $TOLocation=$request->TOLocation;
        $LotAtt05=$request->LotAtt05;
        $LotAtt02_start=$request->LotAtt02_start;
        $descr_c=$request->descr_c;
        $SKU=$request->SKU;
        $ALTERNATE_SKU1=$request->ALTERNATE_SKU1;
        $LotAtt02_end=$request->LotAtt02_end;
        $sql='select * from (select result.*,rownum rn from (';
        $sql.=' select customer.Descr_C as 货主,storeStatus.CUSTOMERID 客户,storeStatus.LocationID 库位, sku.SKU 产品编码, sku.ALTERNATE_SKU1 产品条码, ';
        $sql.=' sku.Descr_C 商品名称, lot.LotAtt05 属性仓, lot.LotAtt08 质量状态, lot.LotAtt02 失效日期, storeStatus.ADDTIME 创建时间, ';
        $sql.=' lot.LotAtt04 批号 ';
        $sql.=' , storeStatus.QTY 在库数量, storeStatus.QtyAllocated 占用数量,count(1) over () as sum from ';
        $sql.=' INV_LOT_LOC_ID storeStatus';
        $sql.=' left join BAS_Customer customer on customer.CustomerID=storeStatus.CUSTOMERID ';
        $sql.=' left join BAS_SKU sku on sku.SKU=storeStatus.SKU and sku.CUSTOMERID=storeStatus.CUSTOMERID ';
        $sql.=' left join INV_LOT_ATT lot on lot.LOTNUM = storeStatus.LOTNUM AND lot.CUSTOMERID = storeStatus.CUSTOMERID ';
        $sql.=' group by storeStatus.LocationID,customer.Descr_C,sku.SKU,sku.ALTERNATE_SKU1 ';
        $sql.=' ,sku.Descr_C,lot.LotAtt05,lot.LotAtt08,lot.LotAtt02,lot.LotAtt04 ';
        $sql.=' , storeStatus.QTY, storeStatus.QtyAllocated,storeStatus.CUSTOMERID,storeStatus.ADDTIME  ';
        $sql.=' )result where 1=1 ';
        if ($TOLocation)$sql .= " and 库位 like '".$TOLocation."' ";
        if ($SKU)$sql.=" and 产品编码 like '".$SKU."' ";
        if ($LotAtt05)$sql .=" and 属性仓 like '".$LotAtt05."' ";
        if ($date_start)$sql.=" and 创建时间 > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end)$sql.=" and 创建时间 < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
        if ($LotAtt02_start)$sql.=" and 失效日期 >='".$LotAtt02_start." 00:00:00' ";
        if ($LotAtt02_end)$sql.=" and 失效日期 <='".$LotAtt02_end." 23:59:59' ";
        if ($descr_c){
            $sql .= ' and 货主 in (';
            $descr_cs = explode(',',$descr_c);
            foreach ($descr_cs as $index => $descr_c){
                if ($index != 0)$sql .= ',';
                $sql .= "'".$descr_c."'";
            }
            $sql .= ') ';
        }
        if ($ALTERNATE_SKU1)$sql.=" and 产品条码 like '".$ALTERNATE_SKU1."' ";
        if ($page&&$paginate)$sql.="  and ROWNUM<='".$page*$paginate."'";
        $sql.=' )  ';
        if ($page&&$paginate)$sql.="  where rn>'".($page-1)*$paginate."'";
        return DB::connection('oracle')->select($sql);
    }
    public function changeInventory(Request $request){
        if(!Gate::allows("库存管理-库存")){ return redirect(url('/'));  }
        $page=$request->input('page')??1;
        $paginate=$request->input('paginate')??50;
        $oracleActTransactingLogs=$this->conditionQuery($request,$page,$paginate);
        $oracleActTransactingLogs=json_encode($oracleActTransactingLogs);
        $owners=OracleBasCustomer::select('descr_c')->where('customer_type','OW')->where('active_flag','Y')->get();
        $isTotalStock=false;
        return view('inventory.statement.changeInventory',compact('oracleActTransactingLogs','page','owners','isTotalStock'));
    }
    public function allInventory(Request $request){
        if(!Gate::allows("库存管理-库存")){ return redirect(url('/'));  }
        $page=$request->page??1;
        $paginate=$request->input('paginate')??50;
        $oracleActTransactingLogs=$this->conditionQueryAllInventory($request,$page,$paginate);
        $oracleActTransactingLogs=json_encode($oracleActTransactingLogs);
        $owners=OracleBasCustomer::select('descr_c')->where('customer_type','OW')->where('active_flag','Y')->get();
        $isTotalStock=true;
        return view('inventory.statement.changeInventory',compact('oracleActTransactingLogs','page','owners','isTotalStock'));
    }
    public function exportData(Request $request){
        if(!Gate::allows("库存管理-库存")){ return redirect(url('/'));  }
        if (!$request->checkAllSign){
            $oracleActTransactingLogs=json_decode($request->input('data'),true);
            return $this->export($oracleActTransactingLogs);
        }
        $oracleActTransactingLogs=$this->conditionQuery($request);
        $oracleActTransactingLogs=json_encode($oracleActTransactingLogs);
        $oracleActTransactingLogs=json_decode($oracleActTransactingLogs,true);
        return $this->export($oracleActTransactingLogs);
    }

    public function export($oracleActTransactingLogs){
        $row=[[
            'customer_descr_c'=>'货主',
            'TOLocation'=>'库位',
            'LotAtt05'=>'属性仓',
            'LotAtt02'=>'失效日期',
            'SKU'=>'产品编码',
            'ALTERNATE_SKU1'=>'产品条码',
            'sku_Descr_C'=>'商品名称',
            'LotAtt08'=>'质量状态',
            'LotAtt04'=>'批号',
            'come_sum'=>'移出数量',
            'join_sum'=>'移入数量',
            'QTY'=>'在库数量',
            'QtyAllocated'=>'占用数量',
        ]];
        $list=[];
        for ($i=0; $i<count($oracleActTransactingLogs);$i++){
            $w=[
                'customer_descr_c'=>$oracleActTransactingLogs[$i]['货主'],
                'TOLocation'=>$oracleActTransactingLogs[$i]['库位'],
                'LotAtt05'=>$oracleActTransactingLogs[$i]['属性仓'],
                'LotAtt02'=>$oracleActTransactingLogs[$i]['失效日期'],
                'SKU'=>$oracleActTransactingLogs[$i]['产品编码'],
                'ALTERNATE_SKU1'=>$oracleActTransactingLogs[$i]['产品条码'],
                'sku_Descr_C'=>$oracleActTransactingLogs[$i]['商品名称'],
                'LotAtt08'=>$oracleActTransactingLogs[$i]['质量状态'],
                'LotAtt04'=>$oracleActTransactingLogs[$i]['批号'],
                'come_sum'=>isset($oracleActTransactingLogs[$i]['移出数量'])?$oracleActTransactingLogs[$i]['移出数量']:'',
                'join_sum'=>isset($oracleActTransactingLogs[$i]['移入数量'])?$oracleActTransactingLogs[$i]['移入数量']:'',
                'QTY'=>$oracleActTransactingLogs[$i]['在库数量'],
                'QtyAllocated'=>$oracleActTransactingLogs[$i]['占用数量'],
            ];
            $list[$i]=$w;
        }
        return Excel::download(new Export($row,$list),date('YmdHis', time()).'-动库报表单.xlsx');
    }
}