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 reset(Request $request)
{
$postData = $request->all();
$id = $postData['nominationId'];
$pdCargo = PdCargo::getTableName();
$pdCargoNomination = PdCargoNomination::getTableName();
$nomi_row = PdCargoNomination::find($id);
if (!$nomi_row) {
throw new DataInputException("cargo nomination id {$id} not existed");
}
$code = "OK";
$message = "reset successfully";
TerminalTimesheetData::whereIn('PARENT_ID', function ($query) use($id) {
$query->select('ID')->from(PdCargoLoad::getTableName())->where('NOMINATION_ID', $id);
})->where('IS_LOAD', 1)->delete();
PdCargoLoad::where('NOMINATION_ID', $id)->delete();
TerminalTimesheetData::whereIn('PARENT_ID', function ($query) use($id) {
$query->select('ID')->from(PdCargoUnload::getTableName())->where('NOMINATION_ID', $id);
})->where('IS_LOAD', 0)->delete();
PdCargoUnload::where('NOMINATION_ID', $id)->delete();
PdVoyageDetail::whereIn('VOYAGE_ID', function ($query) use($id) {
$query->select('ID')->from(PdVoyage::getTableName())->where('NOMINATION_ID', $id);
})->delete();
PdVoyage::where('NOMINATION_ID', $id)->delete();
PdCargoSchedule::where('NOMINATION_ID', $id)->delete();
$nomi_row->CARGO_STATUS = 1;
$nomi_row->save();
$result = ['code' => $code, 'message' => $message];
return response()->json($result);
}
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;
}
