コード例 #1
0
 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];
 }
コード例 #2
0
ファイル: CargoUnloadController.php プロジェクト: hunglmtb/eb
 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];
 }
コード例 #3
0
ファイル: EuTestController.php プロジェクト: hunglmtb/eb
 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];
 }
コード例 #4
0
ファイル: VoyageController.php プロジェクト: hunglmtb/eb
 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];
 }
コード例 #5
0
ファイル: DefermentController.php プロジェクト: hunglmtb/eb
 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];
 }
コード例 #6
0
ファイル: TicketController.php プロジェクト: hunglmtb/eb
 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];
 }
コード例 #7
0
ファイル: LiftMonthlyController.php プロジェクト: hunglmtb/eb
 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];
 }
コード例 #8
0
 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];
 }
コード例 #9
0
 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];
 }
コード例 #10
0
ファイル: TableDataController.php プロジェクト: hunglmtb/eb
 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];
 }
コード例 #11
0
ファイル: AuditController.php プロジェクト: hunglmtb/eb
 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];
 }
コード例 #12
0
ファイル: FormulaController.php プロジェクト: hunglmtb/eb
 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);
 }
コード例 #13
0
 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);
 }
コード例 #14
0
ファイル: PreosController.php プロジェクト: hunglmtb/eb
 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);
 }
コード例 #15
0
 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);
 }
コード例 #16
0
 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];
 }
コード例 #17
0
ファイル: AdminController.php プロジェクト: hunglmtb/eb
 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));
 }
コード例 #18
0
ファイル: QualityController.php プロジェクト: hunglmtb/eb
 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];
 }
コード例 #19
0
ファイル: LiftDailyController.php プロジェクト: hunglmtb/eb
 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"; */
 }
コード例 #20
0
ファイル: graphController.php プロジェクト: hunglmtb/eb
 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]);
 }
コード例 #21
0
 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];
 }
コード例 #22
0
ファイル: CodeController.php プロジェクト: hunglmtb/eb
 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);
 }