public function nominate(Request $request)
{
$postData = $request->all();
$cargo_id = $postData['cargoId'];
$pdCargo = PdCargo::getTableName();
$pdCargoNomination = PdCargoNomination::getTableName();
/* $checkNominated = PdCargo::join($pdCargoNomination,"$pdCargoNomination.CARGO_ID", '=', "$pdCargo.ID")
->where("$pdCargo.ID",'=',$cargo_id)
->select("$pdCargo.NAME")
->first(); */
$pdCargoNomination = PdCargoNomination::firstOrNew(["CARGO_ID" => $cargo_id]);
if ($pdCargoNomination) {
if ($pdCargoNomination->exists) {
$result = ['code' => 'EXIST', 'message' => "Cargo id {$cargo_id} has been nominated already."];
} else {
$cargo = PdCargo::find($cargo_id);
if ($cargo) {
$values = ["CARGO_ID" => $cargo_id, "REQUEST_DATE" => $cargo->REQUEST_DATE, "REQUEST_QTY" => $cargo->REQUEST_QTY, "REQUEST_QTY_UOM" => $cargo->REQUEST_UOM];
$insertId = $pdCargoNomination->fill($values)->save();
if ($insertId > 0) {
$result = ['code' => 'DONE', 'message' => "Cargo id {$cargo_id} is nominated successfully!"];
} else {
$result = ['code' => 'ERROR', 'message' => "unsuccessfully!"];
}
} else {
$result = ['code' => 'NOT_EXIST', 'message' => "Cargo id {$cargo_id} not exist!"];
}
}
} else {
$result = ['code' => 'ERROR', 'message' => "unsuccessfully!"];
}
return response()->json($result);
}
public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
{
$storage_id = $postData['Storage'];
$date_end = $postData['date_end'];
$date_end = \Helper::parseDate($date_end);
$mdlName = $postData[config("constants.tabTable")];
$mdl = "App\\Models\\{$mdlName}";
$pdCargo = PdCargo::getTableName();
$dataSet = $mdl::join($pdCargo, "{$dcTable}.CARGO_ID", '=', "{$pdCargo}.ID")->whereDate("{$dcTable}.DATE_UNLOAD", '>=', $occur_date)->whereDate("{$dcTable}.DATE_UNLOAD", '<=', $date_end)->where("{$pdCargo}.STORAGE_ID", '=', $storage_id)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.*")->get();
return ['dataSet' => $dataSet];
}
public function checkAndSave(&$values)
{
$cargo_id = $values['CARGO_ID'];
$pdCargo = PdCargo::getTableName();
$pdCargoNomination = PdCargoNomination::getTableName();
$row = static::join($pdCargo, "{$pdCargoNomination}.CARGO_ID", '=', "{$pdCargo}.ID")->where("{$pdCargoNomination}.CARGO_ID", '=', $cargo_id)->select("{$pdCargo}.NAME")->first();
if ($row == null) {
$this->fill($values)->save();
return $this;
}
throw new DataInputException("Cargo {$row->NAME} nominated already");
}
public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
{
$date_end = $postData['date_end'];
$date_end = \Helper::parseDate($date_end);
$storage_id = $postData['Storage'];
$mdlName = $postData[config("constants.tabTable")];
$mdl = "App\\Models\\{$mdlName}";
$pdCargo = PdCargo::getTableName();
// \DB::enableQueryLog();
$dataSet = $mdl::join($pdCargo, function ($query) use($pdCargo, $storage_id, $dcTable) {
$query->on("{$dcTable}.CARGO_ID", '=', "{$pdCargo}.ID")->where("{$pdCargo}.STORAGE_ID", '=', $storage_id);
})->whereDate("{$dcTable}.SCHEDULE_DATE", '<=', $date_end)->whereDate("{$dcTable}.SCHEDULE_DATE", '>=', $occur_date)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.*")->orderBy("{$dcTable}")->get();
// \Log::info(\DB::getQueryLog());
$extraDataSet = $this->getExtraDataSet($dataSet);
return ['dataSet' => $dataSet, 'extraDataSet' => $extraDataSet];
}
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 getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
{
$mdlName = $postData[config("constants.tabTable")];
$mdl = "App\\Models\\{$mdlName}";
$date_end = $postData['date_end'];
$date_end = \Helper::parseDate($date_end);
$storage_id = $postData['Storage'];
$pdCargoLoad = PdCargoLoad::getTableName();
$terminalTimesheetData = TerminalTimesheetData::getTableName();
$pdCodeLoadActivity = PdCodeLoadActivity::getTableName();
$pdBerth = PdBerth::getTableName();
$pdCargo = PdCargo::getTableName();
$pdCodeDemurrageEbo = PdCodeDemurrageEbo::getTableName();
$pdCargoUnload = PdCargoUnload::getTableName();
$demurrage = Demurrage::getTableName();
$pdContractData = PdContractData::getTableName();
$result = array();
$aryMst = array();
$lquery = PdCargoLoad::whereHas("TerminalTimesheetData", function ($query) use($terminalTimesheetData) {
$query->where("{$terminalTimesheetData}.IS_LOAD", '=', 1);
})->join($pdCargo, function ($query) use($pdCargo, $pdCargoLoad, $storage_id) {
$query->on("{$pdCargo}.ID", '=', "{$pdCargoLoad}.CARGO_ID")->where("{$pdCargo}.STORAGE_ID", '=', $storage_id);
})->leftJoin($pdBerth, "{$pdCargoLoad}.BERTH_ID", '=', "{$pdBerth}.ID")->leftJoin($pdCodeDemurrageEbo, "{$pdCargoLoad}.DEMURRAGE_EBO", '=', "{$pdCodeDemurrageEbo}.ID")->select("{$pdCargoLoad}.CARGO_ID", "{$pdCargoLoad}.BERTH_ID", "{$pdCargoLoad}.ID as ID", "{$pdCargoLoad}.DATE_LOAD", "{$pdCargoLoad}.DEMURRAGE_EBO", "{$pdBerth}.NAME as BERTH_NAME", "{$pdCargo}.NAME as CARGO_NAME", "{$pdCargo}.CONTRACT_ID", "{$pdCodeDemurrageEbo}.NAME as DEMURRAGE_EBO_NAME")->with(["TerminalTimesheetData" => function ($query) use($terminalTimesheetData) {
$query->where("{$terminalTimesheetData}.IS_LOAD", '=', 1);
}])->with(["Demurrage" => function ($query) use($terminalTimesheetData, $demurrage) {
$query->where("{$demurrage}.ACTIVITY_ID", '=', "{$terminalTimesheetData}.ACTIVITY_ID");
}])->with(["PdContractData" => function ($query) use($pdContractData, $pdCargo) {
$query->where("{$pdContractData}.CONTRACT_ID", '=', "{$pdCargo}.CONTRACT_ID");
}]);
$ulquery = PdCargoUnload::whereHas("TerminalTimesheetData", function ($query) use($terminalTimesheetData) {
$query->whereNull("{$terminalTimesheetData}.IS_LOAD")->orWhere("{$terminalTimesheetData}.IS_LOAD", '=', 0);
})->join($pdCargo, function ($query) use($pdCargo, $pdCargoUnload, $storage_id) {
$query->on("{$pdCargo}.ID", '=', "{$pdCargoUnload}.CARGO_ID")->where("{$pdCargo}.STORAGE_ID", '=', $storage_id);
})->leftJoin($pdBerth, "{$pdCargoUnload}.BERTH_ID", '=', "{$pdBerth}.ID")->leftJoin($pdCodeDemurrageEbo, "{$pdCargoUnload}.DEMURRAGE_EBO", '=', "{$pdCodeDemurrageEbo}.ID")->select("{$pdCargoUnload}.CARGO_ID", "{$pdCargoUnload}.BERTH_ID", "{$pdCargoUnload}.ID as ID", "{$pdCargoUnload}.DATE_UNLOAD as DATE_LOAD", "{$pdCargoUnload}.DEMURRAGE_EBO", "{$pdBerth}.NAME as BERTH_NAME", "{$pdCargo}.NAME as CARGO_NAME", "{$pdCargo}.CONTRACT_ID", "{$pdCodeDemurrageEbo}.NAME as DEMURRAGE_EBO_NAME")->with(["TerminalTimesheetData" => function ($query) use($terminalTimesheetData) {
$query->whereNull("{$terminalTimesheetData}.IS_LOAD")->orWhere("{$terminalTimesheetData}.IS_LOAD", '=', 0);
}])->with(["Demurrage" => function ($query) use($terminalTimesheetData, $demurrage) {
$query->where("{$demurrage}.ACTIVITY_ID", '=', "{$terminalTimesheetData}.ACTIVITY_ID");
}])->with(["PdContractData" => function ($query) use($pdContractData, $pdCargo) {
$query->where("{$pdContractData}.CONTRACT_ID", '=', "{$pdCargo}.CONTRACT_ID");
}]);
// $query = $lquery->union($ulquery);//->orderBy("START_TIME","desc");
$ldataSet = $lquery->get();
$uldataSet = $ulquery->get();
$dataSet = $ldataSet->merge($uldataSet);
// $dataSet = $uldataSet;
$DT_RowId = 100;
foreach ($dataSet as $key => $t) {
$t->DT_RowId = $DT_RowId++;
$timesheetDatas = $t->TerminalTimesheetData;
$demurrages = $t->Demurrage;
$pdContractDatas = $t->PdContractData;
$rate = $pdContractDatas && $pdContractDatas->count() > 0 ? $pdContractDatas->first()->ATTRIBUTE_VALUE : 0;
foreach ($timesheetDatas as $index => $tsheet) {
$elapse_time = null;
if ($tsheet->END_TIME && $tsheet->START_TIME) {
$endTime = $tsheet->END_TIME;
$startTime = $tsheet->START_TIME;
$elapse_time = $endTime->diffInHours($startTime);
}
$elapse_time = !$elapse_time || $elapse_time < 0 ? null : $elapse_time;
$tsheet->ELAPSE_TIME = $elapse_time;
$demurrage = $demurrages->where("ACTIVITY_ID", $tsheet->ACTIVITY_ID)->first();
$tsheet->OVERRIDE_AMOUNT = $demurrage ? $demurrage->OVERRIDE_AMT : "";
$amount = $elapse_time ? $elapse_time * $rate : null;
$tsheet->AMOUNT = $amount && $amount != 0 ? $amount : "0.000";
$tsheet->RATE_HOUR = $rate == 0 ? null : $rate;
if ($index == 0) {
$t->TE_ID = $tsheet->ID;
$t->IS_LOAD = $tsheet->IS_LOAD;
$t->ACTIVITY_NAME = $tsheet->ACTIVITY_ID;
$t->START_TIME = $tsheet->getAttributes()["START_TIME"];
$t->END_TIME = $tsheet->getAttributes()["END_TIME"];
$t->ELAPSE_TIME = $tsheet->ELAPSE_TIME;
$t->OVERRIDE_AMOUNT = $tsheet->OVERRIDE_AMOUNT;
$t->RATE_HOUR = $tsheet->RATE_HOUR;
$t->AMOUNT = $tsheet->AMOUNT;
}
}
}
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 getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
{
$accountId = $postData['PdLiftingAccount'];
$date_end = array_key_exists('date_end', $postData) ? $postData['date_end'] : null;
if ($date_end) {
$date_end = \Helper::parseDate($date_end);
}
// \DB::enableQueryLog();
// \Log::info(\DB::getQueryLog());
$pdCargo = PdCargo::getTableName();
$pdCargoNomination = PdCargoNomination::getTableName();
$shipCargoBlmr = ShipCargoBlmr::getTableName();
$flowDataValue = FlowDataValue::getTableName();
$flow = Flow::getTableName();
$pdLiftingAccount = PdLiftingAccount::getTableName();
$query = ShipCargoBlmr::join($pdCargo, function ($query) use($shipCargoBlmr, $accountId, $pdCargo) {
$query->on("{$pdCargo}.ID", '=', "{$shipCargoBlmr}.CARGO_ID")->where("{$pdCargo}.LIFTING_ACCT", '=', $accountId);
})->whereNotNull("{$shipCargoBlmr}.DATE_TIME")->whereDate("{$shipCargoBlmr}.DATE_TIME", '>=', $occur_date)->whereDate("{$shipCargoBlmr}.DATE_TIME", '<=', $date_end)->select("{$shipCargoBlmr}.CARGO_ID", "{$pdCargo}.NAME as cargo_name", "{$shipCargoBlmr}.DATE_TIME as xdate", \DB::raw("null as nom_qty"), "{$shipCargoBlmr}.ITEM_VALUE as b_qty");
$cquery = PdCargoNomination::join($pdCargo, function ($query) use($pdCargoNomination, $accountId, $pdCargo) {
$query->on("{$pdCargo}.ID", '=', "{$pdCargoNomination}.CARGO_ID")->where("{$pdCargo}.LIFTING_ACCT", '=', $accountId);
})->whereDate("{$pdCargoNomination}.NOMINATION_DATE", '>=', $occur_date)->whereDate("{$pdCargoNomination}.NOMINATION_DATE", '<=', $date_end)->select("{$pdCargoNomination}.CARGO_ID", "{$pdCargo}.NAME as cargo_name", "{$pdCargoNomination}.NOMINATION_DATE as xdate", "{$pdCargoNomination}.NOMINATION_QTY as nom_qty", \DB::raw("null as b_qty"));
$query->union($cquery);
$xquery = \DB::table(\DB::raw("({$query->toSql()}) as x"))->select('x.CARGO_ID', 'x.cargo_name', 'x.xdate', \DB::raw('sum(x.nom_qty) as n_qty'), \DB::raw('sum(x.b_qty) as b_qty'))->addBinding($query->getBindings())->groupBy('x.xdate')->groupBy('x.CARGO_ID');
$xxquery = \DB::table(\DB::raw("({$xquery->toSql()}) as x"))->select('x.cargo_name', 'x.xdate', 'x.n_qty', 'x.b_qty', \DB::raw('null as flow_name'), \DB::raw('null as flow_qty'), \DB::raw('-ifnull(x.b_qty,x.n_qty) as cal_qty'))->addBinding($xquery->getBindings());
$flowquery = FlowDataValue::join($flow, "{$flowDataValue}.FLOW_ID", '=', "{$flow}.ID")->join($pdLiftingAccount, function ($query) use($pdLiftingAccount, $accountId, $flow) {
$query->on("{$pdLiftingAccount}.PROFIT_CENTER", '=', "{$flow}.COST_INT_CTR_ID")->where("{$pdLiftingAccount}.ID", '=', $accountId);
})->whereDate("{$flowDataValue}.OCCUR_DATE", '>=', $occur_date)->whereDate("{$flowDataValue}.OCCUR_DATE", '<=', $date_end)->select(\DB::raw("null as cargo_name"), "{$flowDataValue}.OCCUR_DATE as xdate", \DB::raw("null as n_qty"), \DB::raw("null as b_qty"), \DB::raw("concat({$flow}.name,' (',round({$pdLiftingAccount}.INTEREST_PCT),'%)') as flow_name"), \DB::raw("round({$flowDataValue}.FL_DATA_GRS_VOL*{$pdLiftingAccount}.INTEREST_PCT/100,3) as flow_qty"), \DB::raw("round({$flowDataValue}.FL_DATA_GRS_VOL*{$pdLiftingAccount}.INTEREST_PCT/100,3) as cal_qty"))->groupBy("{$flowDataValue}.OCCUR_DATE")->groupBy("{$flow}.ID");
$xxquery->union($flowquery);
$xxxquery = \DB::table(\DB::raw("({$xxquery->toSql()}) as x"))->select('x.*', \DB::raw('case when x.b_qty is null then x.n_qty else null end as n_qty'), \DB::raw('sum(x.flow_qty) as flow_qty'), \DB::raw('ifnull(sum(x.cal_qty),0) cal_qty'), \DB::raw("'' as UOM"))->addBinding($xxquery->getBindings())->groupBy("x.xdate")->groupBy("x.cargo_name")->groupBy("x.flow_name");
$dataSet = $xxxquery->get();
$month = "";
$balance = 0;
foreach ($dataSet as $key => $item) {
$date = Carbon::parse($item->xdate);
$monthOfItem = $date->month;
//$item->xdate;
if ($month != $monthOfItem) {
$monthData = PdLiftingAccountMthData::where("LIFTING_ACCOUNT_ID", $accountId)->whereMonth('BALANCE_MONTH', '=', $monthOfItem)->select("BAL_VOL")->first();
$balance = $monthData != null ? $monthData->BAL_VOL : 0;
$month = $monthOfItem;
}
$balance += $item->cal_qty;
$item->cal_qty = $balance;
$dataSet[$key] = $item;
}
/* $dataSet = $dataSet->each(function ($item, $key) use ($accountId,&$month,&$balance){
}); */
return ['dataSet' => $dataSet];
/* $sSQL="select x.xdate,
DATE_FORMAT(x.xdate,'%m/%d/%Y') sdate,
DATE_FORMAT(x.xdate,'%m/01/%Y') xmonth,
x.cargo_name,
case when x.b_qty is null then x.n_qty else null end
n_qty,
x.b_qty b_qty,
x.flow_name,
sum(x.flow_qty) flow_qty,
ifnull(sum(x.cal_qty),0) cal_qty
from(
select x.cargo_name,
x.xdate,
x.n_qty,
x.b_qty,
null flow_name,
null flow_qty,
-ifnull(x.b_qty,x.n_qty) cal_qty
from(
SELECT x.cargo_name,
x.xdate,
sum(x.nom_qty) n_qty,
sum(x.b_qty) b_qty
from(
select a.cargo_id,
b.name cargo_name,
a.NOMINATION_DATE xdate,
a.NOMINATION_QTY nom_qty,
null b_qty
from pd_cargo_nomination a,
pd_cargo b
where a.cargo_id=b.id
and b.LIFTING_ACCT=$accountId
and a.NOMINATION_DATE between '$date_from' and '$date_to'
union all
select a.cargo_id,
b.name cargo_name,
date(a.DATE_TIME) xdate,
null nom_qty,
a.ITEM_VALUE b_qty
from ship_cargo_blmr a,
pd_cargo b
where a.cargo_id=b.id
and b.LIFTING_ACCT=$accountId
and a.DATE_TIME is not null
and date(a.DATE_TIME)
between '$date_from'
and '$date_to'
) x
group by x.xdate,x.cargo_id
) x
union all
select x.cargo_name,
x.xdate,
null n_qty,
null b_qty,
x.flow_name,
x.flow_qty,
x.flow_qty cal_qty
from(
select null cargo_name,
a.occur_date xdate,
concat(b.name,' (',round(d.INTEREST_PCT),'%)') flow_name,
round(a.FL_DAY_GRS_VOL*d.INTEREST_PCT/100,3) flow_qty
from flow_day_value a,
flow b,
pd_lifting_account d
where d.id=$accountId
and d.PROFIT_CENTER=b.COST_INT_CTR_ID
and b.id=a.FLOW_ID
#and b.DISP in ('PROD_OIL','IMPORT_OIL')
and a.OCCUR_DATE between '$date_from' and '$date_to'
group by a.occur_date,b.id
) x
) x
group by x.xdate,x.cargo_name,x.flow_name"; */
}