public function getDetailData($id, $postData, $properties)
{
$templateId = $postData['templateId'];
$pdContractData = PdContractData::getTableName();
$pdContractTemplateAttribute = PdContractTemplateAttribute::getTableName();
$pdCodeContractAttribute = PdCodeContractAttribute::getTableName();
$pdContractQtyFormula = PdContractQtyFormula::getTableName();
$contractDataSet = PdContractData::join($pdCodeContractAttribute, "{$pdContractData}.ATTRIBUTE_ID", '=', "{$pdCodeContractAttribute}.ID")->leftJoin($pdContractQtyFormula, "{$pdCodeContractAttribute}.FORMULA_ID", '=', "{$pdContractQtyFormula}.ID")->where("{$pdContractData}.CONTRACT_ID", '=', $id)->select("{$pdContractData}.*", "{$pdContractData}.CONTRACT_ID as CONTRACT_ID_INDEX", "{$pdContractData}.ATTRIBUTE_ID as ATTRIBUTE_ID_INDEX", "{$pdCodeContractAttribute}.ID as DT_RowId", "{$pdCodeContractAttribute}.ID as {$pdContractData}", "{$pdCodeContractAttribute}.NAME as CONTRACT_ID", "{$pdCodeContractAttribute}.CODE as ATTRIBUTE_ID", "{$pdCodeContractAttribute}.ID as ID", "{$pdContractQtyFormula}.NAME as FORMULA")->get();
$selects = ["{$pdCodeContractAttribute}.ID as DT_RowId", "{$pdCodeContractAttribute}.ID as {$pdContractData}", "{$pdCodeContractAttribute}.NAME as CONTRACT_ID", "{$pdCodeContractAttribute}.CODE as ATTRIBUTE_ID", "{$pdCodeContractAttribute}.ID", "{$pdCodeContractAttribute}.FORMULA_ID as FORMULA", "{$pdCodeContractAttribute}.ID as ATTRIBUTE_ID_INDEX", "{$pdContractQtyFormula}.NAME as FORMULA", \DB::raw("{$id} as CONTRACT_ID_INDEX")];
foreach ($properties as $property) {
$columnName = $property['data'];
if ($columnName != 'CONTRACT_ID' && $columnName != 'ATTRIBUTE_ID' && $columnName != 'ID') {
$selects[] = \DB::raw("null as {$columnName}");
}
}
$templateQuery = PdContractTemplateAttribute::join($pdCodeContractAttribute, "{$pdContractTemplateAttribute}.ATTRIBUTE", '=', "{$pdCodeContractAttribute}.ID")->leftJoin($pdContractQtyFormula, "{$pdCodeContractAttribute}.FORMULA_ID", '=', "{$pdContractQtyFormula}.ID")->where("{$pdContractTemplateAttribute}.CONTRACT_TEMPLATE", '=', $templateId)->where("{$pdContractTemplateAttribute}.ACTIVE", '=', 1)->select($selects);
// ->get();
$existAttributes = $contractDataSet->pluck('DT_RowId');
if (count($existAttributes) > 0) {
$templateQuery->whereNotIn("{$pdCodeContractAttribute}.ID", $existAttributes);
}
$templateDataSet = $templateQuery->get();
$dataSet = $contractDataSet->merge($templateDataSet);
return $dataSet;
}
function contract_attr($formulaId, $code, $year = '')
{
global $contractIdGlobal;
global $yearGlobal;
if ($year != '') {
$year = $yearGlobal - 1;
$sSQL = " SELECT a.FORMULA_VALUE FROM pd_contract_year a ,pd_contract_calculation b WHERE b.ID = a.CALCULATION_ID AND" . " a.CONTRACT_ID = " . $contractIdGlobal . " AND a.YEAR = '" . $year . "'" . " AND b.FORMULA_ID = '" . $formulaId . "'";
$pdContractYear = PdContractYear::getTableName();
$pdContractCalculation = PdContractCalculation::getTableName();
$contractYear = PdContractYear::join($pdContractCalculation, "{$pdContractYear}.CALCULATION_ID", '=', "{$pdContractCalculation}.ID")->where("{$pdContractYear}.CONTRACT_ID", '=', $contractIdGlobal)->where("{$pdContractYear}.YEAR", '=', $year)->where("{$pdContractCalculation}.FORMULA_ID", '=', $formulaId)->select("{$pdContractYear}.FORMULA_VALUE as ATTRIBUTE_VALUE")->first();
} else {
$sSQL = " SELECT ATTRIBUTE_VALUE \n\t\t\t\tFROM pd_code_contract_attribute a\n\t\t\t\t,pd_contract_data b\n\t\t\t\tWHERE b.ATTRIBUTE_ID = a.ID AND" . " b.CONTRACT_ID = " . $contractIdGlobal . " \n\t\t\t\t\t\tAND a.CODE = '" . $code . "'";
$pdContractData = PdContractData::getTableName();
$pdCodeContractAttribute = PdCodeContractAttribute::getTableName();
$contractYear = PdCodeContractAttribute::join($pdContractData, "{$pdContractData}.ATTRIBUTE_ID", '=', "{$pdCodeContractAttribute}.ID")->where("{$pdCodeContractAttribute}.CODE", '=', $code)->where("{$pdContractData}.CONTRACT_ID", '=', $contractIdGlobal)->select("ATTRIBUTE_VALUE")->first();
}
/* $result=mysql_query($sSQL) or die("fail: ".$sSQL."-> error:".mysql_error());
while($row=mysql_fetch_array($result)) {
return $row['ATTRIBUTE_VALUE'];
} */
if ($contractYear) {
return $contractYear->ATTRIBUTE_VALUE;
}
return 0;
}
public static function findManyWithConfig($updatedIds)
{
$pdContractData = static::getTableName();
$pdCodeContractAttribute = PdCodeContractAttribute::getTableName();
$pdContractQtyFormula = PdContractQtyFormula::getTableName();
$result = PdContractData::join($pdCodeContractAttribute, "{$pdContractData}.ATTRIBUTE_ID", '=', "{$pdCodeContractAttribute}.ID")->leftJoin($pdContractQtyFormula, "{$pdCodeContractAttribute}.FORMULA_ID", '=', "{$pdContractQtyFormula}.ID")->whereIn("{$pdContractData}.ID", $updatedIds)->select("{$pdContractData}.*", "{$pdContractData}.CONTRACT_ID as CONTRACT_ID_INDEX", "{$pdContractData}.ATTRIBUTE_ID as ATTRIBUTE_ID_INDEX", "{$pdCodeContractAttribute}.ID as DT_RowId", "{$pdCodeContractAttribute}.ID as {$pdContractData}", "{$pdCodeContractAttribute}.NAME as CONTRACT_ID", "{$pdCodeContractAttribute}.CODE as ATTRIBUTE_ID", "{$pdCodeContractAttribute}.ID as ID", "{$pdContractQtyFormula}.NAME as FORMULA")->get();
return $result;
}
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];
}