public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties) { $date_end = array_key_exists('date_end', $postData) ? $postData['date_end'] : null; if ($date_end) { $date_end = \Helper::parseDate($date_end); } $storageId = $postData['Storage']; $pd_voyage = PdVoyage::getTableName(); $pd_cargo = PdCargo::getTableName(); $pd_voyage_detail = PdVoyageDetail::getTableName(); $column = array(); $ObjColumn = $properties['properties']; foreach ($ObjColumn as $p) { array_push($column, "{$pd_voyage}.{$p->data}"); } array_push($column, "{$pd_voyage_detail}.ID AS DT_RowId"); array_push($column, "{$pd_voyage}.ID AS VOYAGE_ID"); array_push($column, "{$pd_voyage_detail}.PARCEL_NO as MASTER_NAME"); $dataSet = PdVoyage::join($pd_cargo, "{$pd_voyage}.CARGO_ID", '=', "{$pd_cargo}.ID")->join($pd_voyage_detail, "{$pd_voyage_detail}.VOYAGE_ID", '=', "{$pd_voyage}.ID")->where(["{$pd_cargo}.STORAGE_ID" => $storageId])->whereDate('SCHEDULE_DATE', '>=', $occur_date)->whereDate('SCHEDULE_DATE', '<=', $date_end)->orderBy("DT_RowId")->get($column); return ['dataSet' => $dataSet]; }
public function gentransport(Request $request) { $postData = $request->all(); $voyage_id = $postData['VOYAGE_ID']; $pdVoyageDetail = PdVoyageDetail::getTableName(); $pdVoyage = PdVoyage::getTableName(); $pdTransitCarrier = PdTransitCarrier::getTableName(); $dataSet = PdVoyageDetail::join($pdVoyage, "{$pdVoyageDetail}.VOYAGE_ID", '=', "{$pdVoyage}.ID")->join($pdTransitCarrier, "{$pdVoyage}.CARRIER_ID", '=', "{$pdTransitCarrier}.ID")->where("{$pdVoyage}.ID", '=', $voyage_id)->orderBy("{$pdVoyageDetail}.ID")->select("{$pdVoyage}.CODE as VOYAGE_CODE", "{$pdVoyage}.NAME as VOYAGE_NAME", "{$pdVoyage}.QUANTITY_TYPE as VOYAGE_QTY_TYPE", "{$pdTransitCarrier}.TRANSIT_TYPE", "{$pdVoyageDetail}.*")->get(); try { $resultTransaction = \DB::transaction(function () use($dataSet, $voyage_id) { $attributes = ['VOYAGE_ID' => $voyage_id]; foreach ($dataSet as $ro) { $attributes['PARCEL_NO'] = $ro->PARCEL_NO; switch ($ro->TRANSIT_TYPE) { case 3: $pdTransportShipDetail = PdTransportShipDetail::where($attributes)->first(); if (!$pdTransportShipDetail) { $values = ['VOYAGE_ID' => $voyage_id]; $values['CODE'] = "SH_{$ro->VOYAGE_CODE}" . "_{$ro->PARCEL_NO}"; $values['NAME'] = "SH_{$ro->VOYAGE_NAME}" . "_{$ro->PARCEL_NO}"; $values['CARGO_ID'] = $ro->CARGO_ID; $values['PARCEL_NO'] = $ro->PARCEL_NO; $values['RECEIPT_QTY'] = $ro->LOAD_QTY; $values['QTY_TYPE'] = $ro->VOYAGE_QTY_TYPE; $values['QTY_UOM'] = $ro->LOAD_UOM; $pdTransportShipDetail = PdTransportShipDetail::insert($values); $pdShipPortInformation = PdShipPortInformation::insert($attributes); } break; case 4: $pdTransportPipelineDetail = PdTransportPipelineDetail::where($attributes)->first(); if (!$pdTransportPipelineDetail) { $values = ['VOYAGE_ID' => $voyage_id]; $values['CODE'] = "PP_{$ro->VOYAGE_CODE}" . "_{$ro->PARCEL_NO}"; $values['NAME'] = "PP_{$ro->VOYAGE_NAME}" . "_{$ro->PARCEL_NO}"; $values['CARGO_ID'] = $ro->CARGO_ID; $values['PARCEL_NO'] = $ro->PARCEL_NO; $values['QUANTITY'] = $ro->LOAD_QTY; $values['QUANTITY_UOM'] = $ro->LOAD_UOM; $pdTransportPipelineDetail = PdTransportPipelineDetail::insert($values); } break; default: $pdTransportGroundDetail = PdTransportGroundDetail::where($attributes)->first(); if (!$pdTransportGroundDetail) { $values = ['VOYAGE_ID' => $voyage_id]; $values['CODE'] = "GR_{$ro->VOYAGE_CODE}" . "_{$ro->PARCEL_NO}"; $values['NAME'] = "GR_{$ro->VOYAGE_NAME}" . "_{$ro->PARCEL_NO}"; $values['CARGO_ID'] = $ro->CARGO_ID; $values['PARCEL_NO'] = $ro->PARCEL_NO; $values['QUANTITY'] = $ro->LOAD_QTY; $values['QUANTITY_UOM'] = $ro->LOAD_UOM; $pdTransportGroundDetail = PdTransportGroundDetail::insert($values); } break; } } }); } catch (\Exception $e) { return response()->json('error when insert data'); } return response()->json('success'); }
public function getExtraDatasetBy($objectType, $facility_id) { $srcTypeData = null; if ($objectType == "PARCEL") { $storage = Storage::getTableName(); $pdVoyageDetail = PdVoyageDetail::getTableName(); $pdVoyage = PdVoyage::getTableName(); $srcTypeData = PdVoyage::join($storage, function ($query) use($storage, $facility_id, $pdVoyage) { $query->on("{$pdVoyage}.STORAGE_ID", '=', "{$storage}.ID")->where("{$storage}.FACILITY_ID", '=', $facility_id); })->join($pdVoyageDetail, "{$pdVoyage}.ID", '=', "{$pdVoyageDetail}.VOYAGE_ID")->select("{$pdVoyageDetail}.ID", "{$pdVoyageDetail}.ID as CODE", "{$pdVoyage}.NAME as NAME", "{$pdVoyage}.ID as value", "{$pdVoyage}.NAME as text", "{$pdVoyageDetail}.PARCEL_NO as PARCEL_NO")->orderBy("{$pdVoyage}.ID")->orderBy("{$pdVoyageDetail}.PARCEL_NO")->get(); } else { if ($objectType == "RESERVOIR") { $srcTypeData = \DB::table($objectType)->get(['ID', 'CODE', 'NAME', 'ID as value', 'NAME as text']); } else { if ($objectType) { $srcTypeData = \DB::table($objectType)->where("FACILITY_ID", $facility_id)->get(['ID', 'CODE', 'NAME', 'ID as value', 'NAME as text']); } } } return $srcTypeData; }