public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
{
$date_end = $postData['date_end'];
$date_end = \Helper::parseDate($date_end);
$mdlName = $postData[config("constants.tabTable")];
$mdl = "App\\Models\\{$mdlName}";
// \DB::enableQueryLog();
$dataSet = $mdl::whereDate("{$dcTable}.BEGIN_DATE", '>=', $occur_date)->whereDate("{$dcTable}.BEGIN_DATE", '<=', $date_end)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.*")->get();
// \Log::info(\DB::getQueryLog());
return ['dataSet' => $dataSet];
}
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 getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
{
$mdlName = $postData[config("constants.tabTable")];
$mdl = "App\\Models\\{$mdlName}";
$object_id = $postData['EnergyUnit'];
$date_end = $postData['date_end'];
$date_end = \Helper::parseDate($date_end);
$euWheres = ['EU_ID' => $object_id];
// \DB::enableQueryLog();
$dataSet = $mdl::where($euWheres)->whereBetween('EFFECTIVE_DATE', [$occur_date, $date_end])->select("ID as DT_RowId", "{$dcTable}.*")->orderBy('EFFECTIVE_DATE')->get();
// \Log::info(\DB::getQueryLog());
return ['dataSet' => $dataSet];
}
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}";
$pdVoyage = PdVoyage::getTableName();
// \DB::enableQueryLog();
$dataSet = $mdl::join($pdVoyage, "{$dcTable}.VOYAGE_ID", '=', "{$pdVoyage}.ID")->whereDate("{$pdVoyage}.SCHEDULE_DATE", '>=', $occur_date)->whereDate("{$pdVoyage}.SCHEDULE_DATE", '<=', $date_end)->where("{$pdVoyage}.STORAGE_ID", '=', $storage_id)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.*")->get();
// \Log::info(\DB::getQueryLog());
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);
$dataSet = null;
$codeDeferGroupType = CodeDeferGroupType::getTableName();
$where = ['FACILITY_ID' => $facility_id];
$dataSet = $mdl::leftJoin($codeDeferGroupType, "{$dcTable}.DEFER_GROUP_TYPE", '=', "{$codeDeferGroupType}.ID")->where($where)->whereDate("{$dcTable}.BEGIN_TIME", '>=', $occur_date)->whereDate("{$dcTable}.END_TIME", '<=', $date_end)->select("{$dcTable}.ID as {$dcTable}", "{$codeDeferGroupType}.CODE as DEFER_GROUP_CODE", "{$dcTable}.ID as DT_RowId", "{$dcTable}.ID", "{$dcTable}.*")->get();
$bunde = ['FACILITY_ID' => $facility_id];
$extraDataSet = $this->getExtraDataSet($dataSet, $bunde);
return ['dataSet' => $dataSet, 'extraDataSet' => $extraDataSet];
}
public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
{
$mdlName = $postData[config("constants.tabTable")];
$mdl = "App\\Models\\{$mdlName}";
$object_id = $postData['Tank'];
$date_end = $postData['date_end'];
$date_end = \Helper::parseDate($date_end);
$tank = Tank::getTableName();
$wheres = ['TANK_ID' => $object_id];
// \DB::enableQueryLog();
$dataSet = $mdl::join($tank, "{$dcTable}.TANK_ID", '=', "{$tank}.ID")->where($wheres)->whereBetween('OCCUR_DATE', [$occur_date, $date_end])->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.TANK_ID as OBJ_ID", "{$tank}.PRODUCT as FLOW_PHASE", "{$dcTable}.ID as DT_RowId", "{$dcTable}.OCCUR_DATE as T_OCCUR_DATE", "{$dcTable}.*")->orderBy("{$dcTable}.OCCUR_DATE")->orderBy("{$dcTable}.LOADING_TIME")->orderBy("{$dcTable}.TICKET_NO")->get();
// \Log::info(\DB::getQueryLog());
return ['dataSet' => $dataSet];
}
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();
// $sSQL="SELECT a.ID, $fields FROM PD_LIFTING_ACCOUNT_MTH_DATA a WHERE LIFTING_ACCOUNT_ID = $accountId order by BALANCE_MONTH";
// \Log::info(\DB::getQueryLog());
$pdLiftingAccountMthData = PdLiftingAccountMthData::getTableName();
$query = PdLiftingAccountMthData::where("LIFTING_ACCOUNT_ID", $accountId)->select("{$pdLiftingAccountMthData}.*", "{$pdLiftingAccountMthData}.ID as {$pdLiftingAccountMthData}", "{$pdLiftingAccountMthData}.ID as DT_RowId")->orderBy("BALANCE_MONTH");
$dataSet = $query->get();
return ['dataSet' => $dataSet];
}
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}.REQUEST_DATE", '<=', $date_end)->whereDate("{$dcTable}.REQUEST_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)
{
$date_end = array_key_exists('date_end', $postData) ? $postData['date_end'] : null;
if ($date_end) {
$date_end = \Helper::parseDate($date_end);
}
$mdlName = $postData[config("constants.tabTable")];
$mdl = "App\\Models\\{$mdlName}";
$storage = Storage::getTableName();
$pdCargoNomination = PdCargoNomination::getTableName();
// \DB::enableQueryLog();
$query = $mdl::join($storage, "{$dcTable}.STORAGE_ID", '=', "{$storage}.ID")->leftJoin($pdCargoNomination, "{$pdCargoNomination}.CARGO_ID", '=', "{$dcTable}.ID")->where(["{$storage}.FACILITY_ID" => $facility_id])->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$pdCargoNomination}.ID as IS_NOMINATED", "{$dcTable}.*");
// ->orderBy('EFFECTIVE_DATE')
// ->get();
if ($date_end) {
$query->whereDate("{$dcTable}.REQUEST_DATE", '<=', $date_end);
}
if ($occur_date) {
$query->whereDate("{$dcTable}.REQUEST_DATE", '>=', $occur_date);
}
$dataSet = $query->get();
// \Log::info(\DB::getQueryLog());
return ['dataSet' => $dataSet];
}
public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
{
$date_end = $postData['date_end'];
$date_end = \Helper::parseDate($date_end);
$auditTrail = AuditTrail::getTableName();
$codeAuditReason = CodeAuditReason::getTableName();
$beginDate = $occur_date;
if ($postData['IntObjectType'] > 0) {
$objectName = IntObjectType::where("ID", $postData['IntObjectType'])->select("CODE")->first();
$objectName = $objectName ? $objectName->CODE : "";
$objectType = strtoupper(str_replace(' ', '_', $objectName)) . '_%';
} else {
$objectType = '%';
}
// $result = array();
// \DB::enableQueryLog();
$dataSet = AuditTrail::leftjoin($codeAuditReason, "{$auditTrail}.REASON", '=', "{$codeAuditReason}.ID")->where(["{$auditTrail}.FACILITY_ID" => $facility_id])->whereDate("{$auditTrail}.WHEN", '>=', $occur_date)->whereDate("{$auditTrail}.WHEN", '<=', $date_end)->where('TABLE_NAME', 'like', $objectType)->select(['ACTION', 'WHO', 'WHEN', 'TABLE_NAME', 'COLUMN_NAME', 'RECORD_ID', 'OBJECT_DESC', 'OLD_VALUE', 'NEW_VALUE', "{$codeAuditReason}.NAME AS REASON"])->get();
// \Log::info(\DB::getQueryLog());
/*
foreach ($loadAudittrail as $v){
$v->WHEN = date('m-d-Y', strtotime($v->WHEN));
array_push($result, $v);
}
return response ()->json ( array (
'result' => $result
) ); */
/* $dataSet = $mdl::whereDate("$dcTable.BEGIN_DATE",'>=',$occur_date)
->whereDate("$dcTable.BEGIN_DATE",'<=',$date_end)
->select(
"$dcTable.ID as $dcTable",
"$dcTable.ID as DT_RowId",
"$dcTable.*")
->get(); */
return ['dataSet' => $dataSet];
}
public function testformula(Request $request)
{
$data = $request->all();
$str = "";
$fid = $data['fid'];
$occur_date = $data['occur_date'];
if (!$occur_date) {
$result = FoVar::where(['formula_id' => $fid])->orderBy('ORDER')->select('*')->get();
$need_occur_date = false;
foreach ($result as $row) {
if (strpos($row->STATIC_VALUE, '@OCCUR_DATE') !== false) {
$need_occur_date = true;
break;
}
}
if ($need_occur_date) {
$str = "need_occur_date";
return response()->json($str);
}
}
if (!$occur_date) {
$occur_date = Carbon::now();
} else {
$occur_date = \Helper::parseDate($occur_date);
}
$param = Formula::find($fid);
$v = \FormulaHelpers::evalFormula($param, $occur_date, true);
//echo $v;
return response()->json($v);
}
public function run(Request $request)
{
$postData = $request->all();
$date_end = $postData['date_end'];
$date_end = \Helper::parseDate($date_end);
$object_id = $postData['EnergyUnit'];
$phase_type = $postData['ExtensionPhaseType'];
$value_type = $postData['ExtensionValueType'];
$data_source = $postData['ExtensionDataSource'];
$table = $postData['EnergyUnit'];
$mdl = \Helper::getModelName($table);
$cb_update_db = $postData['cb_update_db'];
$a = $postData['a'];
$b = $postData['b'];
$u = $postData['u'];
$l = $postData['l'];
$m = $postData['m'];
$c1 = $postData['c1'];
$c2 = $postData['c2'];
$date_begin = $postData['date_begin'];
$date_begin = \Helper::parseDate($date_begin);
$date_from = $postData['f_from_date'];
$date_from = \Helper::parseDate($date_from);
$date_to = $postData['f_to_date'];
$date_to = \Helper::parseDate($date_to);
$from_date = $date_begin;
$mkey = "_" . date("Ymdhis_") . rand(100, 1000);
$data = "";
$continous = true;
$lastT = null;
if (array_key_exists('forecast', $postData)) {
$txt_modify_data = $postData['forecast'];
$ds = explode("\n", $txt_modify_data);
foreach ($ds as $line) {
if ($line) {
$ls = explode(",", $line);
if (count($ls) >= 2) {
$t = trim($ls[0]);
$v = trim($ls[1]);
$data .= ($data ? "\r\n" : "") . "{$t},{$v}";
if ($lastT && $t - $lastT != 1 && $continous) {
$continous = false;
}
$lastT = $t;
}
}
}
} else {
$qData = $this->getDataSet($postData, null, null, $date_begin, null);
$dataSet = $qData['dataSet'];
foreach ($dataSet as $row) {
$occur_date = $row->OCCUR_DATE;
$time = $occur_date->diffInDays($from_date);
$value = $row->V;
$data .= ($data ? "\r\n" : "") . "{$time},{$value}";
if ($lastT && $time - $lastT != 1 && $continous) {
$continous = false;
}
$lastT = $time;
}
}
file_put_contents("data{$mkey}.txt", $data);
//$end = '2013-08-29';
//$start = '2013-08-25';
/* $d1 = strtotime($date_from) - strtotime($date_begin);
$d1 = floor($d1/(60*60*24));
$d2 = strtotime($date_to) - strtotime($date_begin);
$d2 = floor($d2/(60*60*24)); */
$d1 = $date_from->timestamp - $date_begin->timestamp;
$d1 = floor($d1 / (60 * 60 * 24));
$d2 = $date_to->timestamp - $date_begin->timestamp;
$d2 = floor($d2 / (60 * 60 * 24));
$timeForecast = "";
for ($i = $d1; $i < $d2 + 1; $i++) {
$timeForecast .= ($timeForecast ? "\r\n" : "") . $i;
if ($lastT && $i - $lastT != 1 && $continous) {
$continous = false;
}
$lastT = $i;
}
$sqls = [];
$warning = '';
if (!$continous) {
$warning = "Timing is not continuous";
}
file_put_contents("t{$mkey}.txt", $timeForecast);
// echo "<b>Time forecast:</b> ".$timeForecast."<br>";
if ($a === "0" || $a === "1") {
$params = "{$a},{$b},0,0,0,0,0";
} else {
if ($c2 > 0) {
$params = "{$a},{$b},0,0,0,{$c1},{$c2}";
} else {
$params = "{$a},{$b},{$l},{$u},{$m},{$c1},0";
}
}
file_put_contents("prop{$mkey}.txt", $params);
$error = [];
$results = [];
if (!file_exists('pdforecast.exe')) {
$error[] = "Exec file not found";
} else {
if (file_exists("data{$mkey}.txt") && file_exists("t{$mkey}.txt") && file_exists("prop{$mkey}.txt")) {
set_time_limit(300);
exec("pdforecast.exe {$mkey}");
if (file_exists("error{$mkey}.txt")) {
$error[] = file_get_contents("error{$mkey}.txt", true);
}
if (file_exists("forecast_q{$mkey}.csv")) {
// echo "<b>Result:</b><br>";
$file = fopen("forecast_q{$mkey}.csv", "r");
$configuration = auth()->user()->getConfiguration();
$format = $configuration['time']['DATE_FORMAT_CARBON'];
//'m/d/Y';
while (!feof($file)) {
$line = fgets($file);
// echo $line;
// $result.= $line;
$result = ['value' => $line];
if ($line) {
$xs = explode(",", $line);
if (count($xs >= 2)) {
$x_time = trim($xs[0]);
$x_value = trim($xs[1]);
if ($x_time >= $d1) {
// $x_time=($x_time)*60*60*24+strtotime($date_begin);
$beginTimeStamp = $date_begin->timestamp;
$x_time = $x_time * 60 * 60 * 24 + $beginTimeStamp;
// $x_date=date('Y-m-d',$x_time);
$x_date = Carbon::createFromTimestamp($x_time);
// $x_date = $x_date->createFromTimestamp($x_time);
// echo " ($x_date) ";
$rxDate = $x_date ? $x_date->format($format) : $x_date;
// $result.= " ($rxDate) ";
$result['date'] = $rxDate;
if ($cb_update_db == 'true') {
$field = "EU_DATA_{$value_type}";
$field = strtoupper($field);
$attributes = ['EU_ID' => $object_id, 'OCCUR_DATE' => $x_date, "FLOW_PHASE" => $phase_type];
$values = ['EU_ID' => $object_id, 'OCCUR_DATE' => $x_date, "FLOW_PHASE" => $phase_type, $field => $x_value];
\DB::enableQueryLog();
EnergyUnitDataForecast::updateOrCreate($attributes, $values);
$result['sql'] = \Helper::logger();
}
}
}
}
$results[] = $result;
// echo "<br>";
}
\DB::disableQueryLog();
fclose($file);
} else {
// logError("Result file not found");
$error[] = "Result file not found";
}
} else {
// logError("Input files not found");
$error[] = "Input files not found";
}
}
$finalResults = ['data' => $data, 'warning' => $warning, 'params' => $params, 'time' => $timeForecast, 'result' => $results, 'error' => $error, 'key' => $mkey];
$this->cleanFiles($mkey);
return response()->json($finalResults);
}
public function run(Request $request)
{
$postData = $request->all();
$phase_type = $postData['ExtensionPhaseType'];
$cb_update_db = $postData['cb_update_db'];
$occur_date = $postData['date_begin'];
$value_type = $postData['ExtensionValueType'];
$occur_date = \Helper::parseDate($occur_date);
$inputDataSet = $this->getInputDataSet($postData, $occur_date);
$objdata = $inputDataSet['data'];
$objinfo = $inputDataSet['info'];
$mkey = "";
// $mkey = "_".date("Ymdhis_").rand(100,1000)/* ."hung_test" */;
$preos = "";
$files = ['gas' => "{$preos}" . "prvap.exe", 'oil' => "{$preos}" . "prliq.exe", 'data' => "{$preos}" . "data{$mkey}.txt", 'm_ij' => "{$preos}" . "m_ij{$mkey}.txt", 'prop' => "{$preos}" . "prop{$mkey}.txt", 'error' => "{$preos}" . "error{$mkey}.txt", 'PR_single_V' => "{$preos}" . "PR_single_V{$mkey}.csv", 'PR_single_L' => "{$preos}" . "PR_single_L{$mkey}.csv"];
$cc = count($objdata);
if ($cc <= 0) {
return response('empty input data', 401);
}
//['error'=>"empty input data"];
$ele = array_values($objdata)[0];
$data = "";
$inputData = [];
foreach ($ele as $key => $value) {
$ss = [];
foreach ($objdata as $source => $objValue) {
if ($objValue[$key] !== "") {
$ss[] = $objValue[$key];
$inputData[] = $objValue[$key] . " <- [{$source}][{$key}]";
}
}
if (count($ss) > 0) {
$data .= ($data ? "\r\n" : "") . implode(",", $ss);
}
}
file_put_contents($files['data'], $data);
$error = [];
$results = [];
$sqls = [];
//Gas
$exe = $phase_type == 2 ? $files['gas'] : $files['oil'];
if (!file_exists($exe)) {
$error[] = "Exec {$exe} file not found";
} else {
if (file_exists($files['data'])) {
set_time_limit(300);
exec("{$exe} {$mkey}");
if (file_exists($files['error'])) {
$error[] = file_get_contents($files['error'], true);
}
if (file_exists($files['PR_single_V'])) {
$fileName = $files['PR_single_V'];
$file = fopen($fileName, "r");
$lastline = "";
$result = [];
while (!feof($file)) {
$line = fgets($file);
$result[] = $line;
if ($line) {
if ($line) {
$lastline = $line;
}
}
}
$results[$fileName] = $result;
fclose($file);
$fileName = $files['PR_single_L'];
$file = fopen($fileName, "r");
$lastline = "";
$result = [];
while (!feof($file)) {
$line = fgets($file);
$result[] = $line;
if ($line) {
if ($line) {
$lastline = $line;
}
}
}
$results[$fileName] = $result;
fclose($file);
if ($lastline && $cb_update_db == 'true') {
$xs = explode(",", $lastline);
$i = 0;
foreach ($xs as $svol) {
if ($i < count($objinfo)) {
$src = $objinfo[$i]["src"];
$pre = $objinfo[$i]["pre"];
$table = $src . "_DATA_VALUE";
$field = $pre . "_DATA_{$value_type}";
$field = strtoupper($field);
$attributes = ["OCCUR_DATE" => $occur_date];
if ($src == "ENERGY_UNIT") {
$attributes['FLOW_PHASE'] = $phase_type;
}
$attributes["{$pre}" . "_ID"] = $objinfo[$i]["obj_id"];
$values = $attributes;
$values[$field] = $svol;
$mdl = \Helper::getModelName($table);
\DB::enableQueryLog();
$mdl::updateOrCreate($attributes, $values);
$sqls[] = \Helper::logger();
}
$i++;
}
\DB::disableQueryLog();
}
} else {
$error[] = "Result file not found";
}
} else {
$error[] = "Input files not found";
}
}
$finalResults = ['data' => $inputData, 'warning' => '', 'result' => $results, 'error' => $error, 'key' => $mkey, 'exe' => $exe, 'sqls' => $sqls];
// $this->cleanFiles($mkey);
return response()->json($finalResults);
}
public function gen(Request $request)
{
try {
$postData = $request->all();
$result = \DB::transaction(function () use($postData) {
$contract_id = $postData['contract_id'];
$storage_id = $postData['PdContractQtyFormula'];
$code1st = array_key_exists("code1st", $postData) ? $postData["code1st"] : 0;
$year1 = array_key_exists("year1", $postData) ? $postData["year1"] : 0;
$code2nd = array_key_exists("code2nd", $postData) ? $postData["code2nd"] : 0;
$year = array_key_exists("year", $postData) ? $postData["year"] : 0;
$month = array_key_exists("month", $postData) ? $postData["month"] : 0;
$day = array_key_exists("day", $postData) ? $postData["day"] : "";
$seq = array_key_exists("seq", $postData) ? $postData["seq"] : 0;
$liftacc = array_key_exists("PdLiftingAccount", $postData) ? $postData["PdLiftingAccount"] : 0;
$priority = array_key_exists("PdCodeCargoPriority", $postData) ? $postData["PdCodeCargoPriority"] : 0;
$qtytype = array_key_exists("PdCodeCargoQtyType", $postData) ? $postData["PdCodeCargoQtyType"] : 0;
$date1st = array_key_exists("date1st", $postData) ? $postData["date1st"] : 0;
$avgqty = array_key_exists("avgqty", $postData) ? $postData["avgqty"] : 0;
$uom = array_key_exists("PdCodeMeasUom", $postData) ? $postData["PdCodeMeasUom"] : 0;
$adjtime = array_key_exists("PdCodeTimeAdj", $postData) ? $postData["PdCodeTimeAdj"] : 0;
$tolerance = array_key_exists("PdCodeQtyAdj", $postData) ? $postData["PdCodeQtyAdj"] : 0;
$qty = array_key_exists("qty", $postData) ? $postData["qty"] : 0;
$n = strlen($seq);
$num = $seq + 1 - 1;
$count = 0;
$x_qty = 0;
if ($qty <= 0) {
return "Quantity value must be greater than zero";
}
if ($avgqty <= 0) {
return "Average quantity value must be greater than zero";
}
$date1st = \Helper::parseDate($date1st);
$requestDate = $date1st;
while (true) {
$x_qty += $avgqty;
$exit = $x_qty >= $qty;
$code = $code1st . $year1 . $code2nd . $year . $month . $day . str_pad($num, $n, '0', STR_PAD_LEFT);
PdCargo::where("CODE", $code)->delete();
PdCargo::insert(["CODE" => $code, "NAME" => $code, "LIFTING_ACCT" => $liftacc, "STORAGE_ID" => $storage_id, "REQUEST_DATE" => $requestDate, "REQUEST_QTY" => $avgqty, "REQUEST_UOM" => $uom, "PRIORITY" => $priority, "QUANTITY_TYPE" => $qtytype, "CONTRACT_ID" => $contract_id]);
$num++;
$requestDate = $requestDate->addMonths(6);
if ($exit) {
break;
}
}
return "Sucess";
});
} catch (\Exception $e) {
$result = "could not generate cargo";
\Log::info("\n---gen cargo--\nException wher run transation\n ");
\Log::info($e->getMessage());
\Log::info($e->getTraceAsString());
// return response($e->getMessage(), 400);
}
return response()->json($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];
}
public function loadUserLog(Request $request)
{
$data = $request->all();
$date_from = \Helper::parseDate($data['DATE_FROM']);
$date_to = \Helper::parseDate($data['DATE_TO']);
$username = trim($data['USERNAME']);
$result = array();
$logUser = LogUser::getTableName();
$loadUserLog = DB::table($logUser . ' AS a')->whereDate('a.LOGIN_TIME', '>=', $date_from)->whereDate('a.LOGIN_TIME', '<=', $date_to)->where(function ($q) use($username) {
if ($username != "All") {
$q->where(['a.USERNAME' => $username]);
}
})->select(['a.USERNAME', 'a.LOGIN_TIME', 'a.LOGOUT_TIME', 'a.IP'])->get();
return response()->json(array('result' => $loadUserLog));
}
public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
{
$mdlName = $postData[config("constants.tabTable")];
$mdl = "App\\Models\\{$mdlName}";
$src_type_id = $postData['CodeQltySrcType'];
$date_end = $postData['date_end'];
$date_end = \Helper::parseDate($date_end);
$filterBy = $postData['cboFilterBy'];
$extraDataSet = [];
$dataSet = null;
$codeQltySrcType = CodeQltySrcType::getTableName();
// $qltData = $mdl::getTableName();
$uoms = $properties['uoms'];
$sourceTypekey = array_search('CodeQltySrcType', array_column($uoms, 'id'));
$sourceTypes = $uoms[$sourceTypekey]['data'];
$objectType = null;
$src_type_ids = $src_type_id == 0 ? [1, 2, 3, 4, 5, 6] : [$src_type_id];
$query = null;
// \DB::enableQueryLog();
foreach ($src_type_ids as $srcTypeId) {
$where = ['SRC_TYPE' => $srcTypeId];
switch ($srcTypeId) {
case 1:
case 2:
case 3:
case 4:
$objectType = $sourceTypes->find($srcTypeId);
$objectType = $objectType->CODE;
$cquery = $mdl::join($objectType, function ($query) use($objectType, $facility_id, $dcTable) {
$query->on("{$objectType}.ID", '=', "{$dcTable}.SRC_ID")->where("{$objectType}.FACILITY_ID", '=', $facility_id);
})->where($where)->whereDate("{$dcTable}.{$filterBy}", '>=', $occur_date)->whereDate("{$dcTable}.{$filterBy}", '<=', $date_end)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.ID", "{$dcTable}.*")->orderBy($dcTable);
// ->get();
$query = $query == null ? $cquery : $query->union($cquery);
break;
case 5:
$objectType = $sourceTypes->find($srcTypeId);
$objectType = $objectType->CODE;
$storage = Storage::getTableName();
$pdVoyageDetail = PdVoyageDetail::getTableName();
$pdVoyage = PdVoyage::getTableName();
$cquery = $mdl::join($pdVoyageDetail, "{$dcTable}.SRC_ID", '=', "{$pdVoyageDetail}.ID")->join($pdVoyage, "{$pdVoyageDetail}.VOYAGE_ID", '=', "{$pdVoyage}.ID")->join($storage, function ($query) use($storage, $facility_id, $pdVoyage) {
$query->on("{$storage}.ID", '=', "{$pdVoyage}.STORAGE_ID")->where("{$storage}.FACILITY_ID", '=', $facility_id);
})->where($where)->whereDate($filterBy, '>=', $occur_date)->whereDate($filterBy, '<=', $date_end)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.ID", "{$dcTable}.*")->orderBy($dcTable);
// ->get();
$query = $query == null ? $cquery : $query->union($cquery);
break;
case 6:
$objectType = $sourceTypes->find($srcTypeId);
$objectType = $objectType->CODE;
$cquery = $mdl::where($where)->whereDate($filterBy, '>=', $occur_date)->whereDate($filterBy, '<=', $date_end)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.ID", "{$dcTable}.*")->orderBy($dcTable);
// ->get();
$query = $query == null ? $cquery : $query->union($cquery);
break;
}
}
if ($query != null) {
$dataSet = $query->get();
}
// \Log::info(\DB::getQueryLog());
$sourceColumn = 'SRC_TYPE';
if ($dataSet && $dataSet->count() > 0) {
if ($src_type_id > 0) {
$srcTypeData = $this->getExtraDatasetBy($objectType, $facility_id);
if ($srcTypeData) {
$extraDataSet[$sourceColumn] = [];
$extraDataSet[$sourceColumn][$src_type_id] = $srcTypeData;
}
} else {
// \DB::enableQueryLog();
$bySrcTypes = $dataSet->groupBy('SRC_ID');
// \Log::info(\DB::getQueryLog());
if ($bySrcTypes) {
$extraDataSet[$sourceColumn] = [];
foreach ($bySrcTypes as $key => $srcType) {
$srcTypeID = $srcType[0]->SRC_TYPE;
$table = $sourceTypes->find($srcTypeID);
$table = $table->CODE;
$srcTypeData = $this->getExtraDatasetBy($table, $facility_id);
if ($srcTypeData) {
$extraDataSet[$sourceColumn][$srcTypeID] = $srcTypeData;
}
}
}
}
}
return ['dataSet' => $dataSet, 'extraDataSet' => $extraDataSet];
}
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"; */
}
public function loadChart(Request $request)
{
$options = $request->only('title', 'minvalue', 'maxvalue', 'date_begin', 'date_end', 'input', "bgcolor");
$title = $options['title'];
$minvalue = $options['minvalue'];
$maxvalue = $options['maxvalue'];
$date_begin = $options['date_begin'];
$date_end = $options['date_end'];
$input = $options['input'];
$bgcolor = $options["bgcolor"];
$isrange = is_numeric($minvalue) && $maxvalue > $minvalue;
$date_begin = \Helper::parseDate($date_begin);
$date_end = \Helper::parseDate($date_end);
$ss = explode(",", $input);
$k = 0;
$maxV = 0;
$minV = PHP_INT_MAX;
$strData = "";
foreach ($ss as $s) {
$tmp = [];
$phase_type = -1;
$xs = explode(":", $s);
$chart_name = $xs[5];
$chart_type = $xs[4];
$types = explode("~", $xs[3]);
$vfield = $types[0];
$chart_color = $xs[count($xs) - 1];
if (!(substr($chart_color, 0, 1) == "#" && strlen($chart_color) > 1)) {
$chart_color = "";
}
$datefield = "OCCUR_DATE";
$is_eutest = false;
$is_deferment = false;
$obj_type_id_field = null;
if ($xs[0] == "TANK") {
$obj_type_id_field = "TANK_ID";
} else {
if ($xs[0] == "STORAGE") {
$obj_type_id_field = "STORAGE_ID";
} else {
if ($xs[0] == "FLOW") {
$obj_type_id_field = "FLOW_ID";
} else {
if ($xs[0] == "EU_TEST") {
$obj_type_id_field = "EU_ID";
} else {
if ($xs[0] == "ENERGY_UNIT") {
$obj_type_id_field = "EU_ID";
$chart_type = $xs[5];
$chart_name = $xs[6];
$vfield = $xs[3];
$types = explode("~", $xs[4]);
$phase_type = $types[0];
}
}
}
}
}
if (!$obj_type_id_field) {
continue;
}
$pos = strpos($xs[3], "@");
if ($pos > 0) {
$xs[3] = substr($xs[3], $pos + 1);
}
$table_name = $xs[2];
$entity = strtolower(str_replace('_', ' ', $table_name));
$entity = ucwords($entity);
$entity = str_replace(' ', '', $entity);
if (strtolower(substr($entity, 0, strlen("EuTest"))) == "eutest") {
$is_eutest = true;
$datefield = "EFFECTIVE_DATE";
} else {
if (strtolower($entity) == "deferment") {
$is_deferment = true;
$datefield = "BEGIN_TIME";
$obj_type_id_field = "DEFER_TARGET";
}
}
$model = 'App\\Models\\' . $entity;
$va = $xs[0];
$pa1 = $xs[1];
//\DB::enableQueryLog ();
$tmp = $model::where([$obj_type_id_field => $pa1])->where(function ($q) use($va, $is_eutest, $phase_type) {
if ($va == "ENERGY_UNIT" && !$is_eutest) {
$q->where(['FLOW_PHASE' => $phase_type]);
}
})->whereDate($datefield, '>=', $date_begin)->whereDate($datefield, '<=', $date_end)->orderBy($datefield)->take(300)->get([$vfield . ' AS V', "{$datefield}"]);
//\Log::info ( \DB::getQueryLog () );
$i = 0;
// DB::raw('DATE_FORMAT('.$datefield.', "%Y,%m-1,%d")');
if ($k > 0) {
$strData .= ",{";
} else {
$strData .= "{";
}
$strData .= "type: '" . $chart_type . "',\n";
$strData .= "name: '" . preg_replace('/\\s+/', '_@', $chart_name) . "',\n";
$strData .= "type: '" . $chart_type . "',\n";
$strData .= $chart_color ? "color: '{$chart_color}',\n" : "";
//$strData .= "name: '".$chart_name."',";
$strData .= "data: [";
foreach ($tmp as $row) {
if ($row->V == "") {
$row->V = 0;
}
if ($row->V > $maxV) {
$maxV = $row->V;
}
if ($row->V < $minV) {
$minV = $row->V;
}
if ($i > 0) {
$strData .= ",\r\n";
}
$dateTime = $row->{$datefield};
$dateTimeText = sprintf("%d,%d,%d", $dateTime->year, $dateTime->month - 1, $dateTime->day);
$strData .= "[Date.UTC(" . $dateTimeText . "), " . $row->V . "]";
$i++;
}
$strData .= "]}\r\n";
$k++;
}
$min1 = $minV < 0 ? $minV : 0;
$div = 5;
if ($isrange) {
$min1 = $minvalue;
$max1 = $maxvalue;
} else {
$x = ceil($maxV);
$xs = strval($x);
$xl = strlen($xs) - 1;
$n = (int) $xs[0];
$t = pow(10, $xl);
$x = ceil(2 * $maxV / $t) / 2;
$max1 = $x * $t;
if ($max1 / $div * ($div - 1) > $maxV) {
$max1 = $max1 / $div * ($div - 1);
$div -= 1;
}
}
$tickInterval1 = ($max1 - ($min1 > 0 ? $min1 : 0)) / $div;
$tickInterval2 = 0;
$min2 = 0;
$max2 = 0;
$x = $this->convertUOM($tickInterval1, 'kL', 'm3');
if (is_numeric($x)) {
$tickInterval2 = $x;
if ($isrange) {
$min2 = $this->convertUOM($min1, 'kL', 'm3');
}
}
if ($tickInterval2 > 0) {
$max2 = ($min2 < 0 ? 0 : $min2) + $tickInterval2 * $div;
}
return view('front.graph_loadchart', ['min1' => $min1, 'max1' => $max1, 'min2' => $min2, 'max2' => $max2, 'title' => $title != "null" ? $title : "", 'series' => $strData]);
}
public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties)
{
$flow_phase = $postData['ExtensionPhaseType'];
$object_id = $postData['ObjectName'];
$source_type = $postData['IntObjectTypeName'];
$date_from = $occur_date;
$date_to = $postData['date_end'];
$date_to = \Helper::parseDate($date_to);
if ($object_id <= 0) {
return response("Object Name {$object_id} not okay", 401);
}
$obj_id_prefix = $source_type;
$field_prefix = $source_type;
$idField = $source_type;
$modelName = $this->getModelName($source_type, $postData);
$mdl = "App\\Models\\" . $modelName;
$selects = ["ID as DT_RowId", "OCCUR_DATE"];
$where = [];
if ($source_type == "ENERGY_UNIT") {
$obj_id_prefix = "EU";
$idField = $obj_id_prefix;
$where["FLOW_PHASE"] = $flow_phase;
$selects[] = "FLOW_PHASE as EU_FLOW_PHASE";
$selects[] = "EU_ID";
} else {
if ($source_type == "FLOW") {
$obj_id_prefix = "FL";
$selects[] = "FLOW_ID";
} else {
$selects[] = "{$idField}" . "_ID";
}
}
if ($source_type == "FLOW" || $source_type == "ENERGY_UNIT") {
$field_prefix = $obj_id_prefix . "_DATA";
}
$selects[] = "{$field_prefix}" . "_GRS_VOL";
$selects[] = "{$field_prefix}" . "_GRS_MASS";
$selects[] = "{$field_prefix}" . "_GRS_ENGY";
$selects[] = "{$field_prefix}" . "_GRS_PWR";
$where["{$idField}" . "_ID"] = $object_id;
// \DB::enableQueryLog();
$dataSet = $mdl::where($where)->whereBetween('OCCUR_DATE', [$date_from, $date_to])->select($selects)->orderBy('OCCUR_DATE')->get();
// \Log::info(\DB::getQueryLog());
return ['dataSet' => $dataSet];
}
public function save(Request $request)
{
// sleep(2);
// return response()->json('[]');
// throw new Exception("not Save");
$postData = $request->all();
if (!array_key_exists('editedData', $postData) && !array_key_exists('deleteData', $postData)) {
return response()->json('no data 2 update!');
}
if (!array_key_exists('editedData', $postData)) {
$editedData = false;
} else {
$editedData = $postData['editedData'];
}
$facility_id = null;
if (array_key_exists('Facility', $postData)) {
$facility_id = $postData['Facility'];
}
$occur_date = null;
if (array_key_exists('date_begin', $postData)) {
$occur_date = $postData['date_begin'];
$occur_date = \Helper::parseDate($occur_date);
}
$affectedIds = [];
$this->preSave($editedData, $affectedIds, $postData);
try {
$resultTransaction = \DB::transaction(function () use($postData, $editedData, $affectedIds, $occur_date, $facility_id) {
$this->deleteData($postData);
if (!$editedData) {
return [];
}
$lockeds = [];
$ids = [];
$resultRecords = [];
// \DB::enableQueryLog();
foreach ($editedData as $mdlName => $mdlData) {
$modelName = $this->getModelName($mdlName, $postData);
$mdl = "App\\Models\\" . $modelName;
if ($mdl::$ignorePostData) {
unset($editedData[$mdlName]);
continue;
}
$ids[$mdlName] = [];
$resultRecords[$mdlName] = [];
$tableName = $mdl::getTableName();
$locked = \Helper::checkLockedTable($tableName, $occur_date, $facility_id);
if ($locked) {
$lockeds[$mdlName] = "Data of {$modelName} with facility {$facility_id} was locked on {$occur_date} ";
unset($editedData[$mdlName]);
continue;
}
foreach ($mdlData as $key => $newData) {
$columns = $mdl::getKeyColumns($newData, $occur_date, $postData);
$originNewData = $mdlData[$key];
$mdlData[$key] = $newData;
$returnRecord = $mdl::updateOrCreateWithCalculating($columns, $newData);
if ($returnRecord) {
$affectRecord = $returnRecord->updateDependRecords($occur_date, $originNewData, $postData);
$returnRecord->updateAudit($columns, $newData, $postData);
$ids[$mdlName][] = $returnRecord['ID'];
$resultRecords[$mdlName][] = $returnRecord;
if ($affectRecord) {
$ids[$mdlName][] = $affectRecord['ID'];
$resultRecords[$mdlName][] = $affectRecord;
}
}
}
$editedData[$mdlName] = $mdlData;
}
// \Log::info(\DB::getQueryLog());
// $objectIds = array_unique($objectIds);
//doFormula in config table
$affectColumns = [];
foreach ($editedData as $mdlName => $mdlData) {
$modelName = $this->getModelName($mdlName, $postData);
$cls = \FormulaHelpers::doFormula($modelName, 'ID', $ids[$mdlName]);
if (is_array($cls) && count($cls) > 0) {
$affectColumns[$mdlName] = $cls;
}
}
foreach ($resultRecords as $mdlName => $records) {
foreach ($records as $key => $returnRecord) {
$returnRecord->afterSaving($postData);
}
}
if ($this->isApplyFormulaAfterSaving) {
//get affected object with id
$objectWithformulas = [];
foreach ($editedData as $mdlName => $mdlData) {
$mdl = "App\\Models\\" . $mdlName;
foreach ($mdlData as $key => $newData) {
$columns = array_keys($newData);
if (array_key_exists($mdlName, $affectColumns)) {
$columns = array_merge($columns, $affectColumns[$mdlName]);
}
$uColumns = $mdl::getKeyColumns($newData, $occur_date, $postData);
$columns = array_diff($columns, $uColumns);
$aFormulas = $this->getAffectedObjects($mdlName, $columns, $newData);
$objectWithformulas = array_merge($objectWithformulas, $aFormulas);
}
}
$objectWithformulas = array_unique($objectWithformulas);
//apply Formula in formula table
$applieds = \FormulaHelpers::applyAffectedFormula($objectWithformulas, $occur_date);
if ($applieds && count($applieds)) {
foreach ($applieds as $apply) {
$mdlName = $apply->modelName;
if (!array_key_exists($mdlName, $ids)) {
$ids[$mdlName] = [];
}
$ids[$mdlName][] = $apply->ID;
$ids[$mdlName] = array_unique($ids[$mdlName]);
$resultRecords[$mdlName][] = $apply;
$resultRecords[$mdlName] = array_unique($resultRecords[$mdlName]);
}
}
}
$this->afterSave($resultRecords, $occur_date);
$resultTransaction = [];
if (count($lockeds) > 0) {
$resultTransaction['lockeds'] = $lockeds;
}
$resultTransaction['ids'] = $ids;
return $resultTransaction;
});
} catch (\Exception $e) {
\Log::info("\n----------------------hehe--------------------------------------------------------------------------\nException wher run transation\n ");
// \Log::info($e->getTraceAsString());
// return response($e->getMessage(), 400);
throw $e;
}
//get updated data after apply formulas
$updatedData = [];
if (array_key_exists('ids', $resultTransaction)) {
foreach ($resultTransaction['ids'] as $mdlName => $updatedIds) {
// $updatedData[$mdlName] = $mdl::findMany($objectIds);
$modelName = $this->getModelName($mdlName, $postData);
$mdl = "App\\Models\\" . $modelName;
$updatedData[$mdlName] = $mdl::findManyWithConfig($updatedIds);
}
}
// \Log::info(\DB::getQueryLog());
$results = ['updatedData' => $updatedData, 'postData' => $postData];
if (array_key_exists('lockeds', $resultTransaction)) {
$results['lockeds'] = $resultTransaction['lockeds'];
}
return response()->json($results);
}