public function getSurveillanceSetting(Request $request)
{
$data = $request->all();
$cfgFieldProps = array();
$tags = array();
$surs = array();
$objType_id = -100;
$objectType = "";
$tag_other = array();
$other = "";
$strMessage = null;
if (isset($data['OBJECT_TYPE'])) {
$objectType = $data['OBJECT_TYPE'];
}
if (isset($data['OBJECT_ID'])) {
$objType_id = $data['OBJECT_ID'];
}
if (isset($data['SUR'])) {
$surs = explode('@', $data['SUR']);
}
$cfgFields = CfgFieldProps::where(['USE_DIAGRAM' => 1])->where('TABLE_NAME', 'like', $objectType . '%')->orderBy('TABLE_NAME', 'COLUMN_NAME')->get(['COLUMN_NAME', 'TABLE_NAME', 'LABEL']);
if (count($cfgFields) > 0) {
foreach ($cfgFields as $v) {
$value = $v->TABLE_NAME . "/" . $v->COLUMN_NAME;
$checked = in_array($value, $surs, TRUE) ? "checked" : "";
$v['CHECK'] = $checked;
array_push($cfgFieldProps, $v);
}
}
$intConnection = IntConnection::all(['ID', 'NAME']);
$intTagMapping = IntTagMapping::getTableName();
$intObjectType = IntObjectType::getTableName();
//\DB::enableQueryLog ();
$vTags = DB::table($intTagMapping . ' AS a')->join($intObjectType . ' AS b', 'a.OBJECT_TYPE', '=', 'b.ID')->where(['b.CODE' => $objectType])->where(function ($q) use($objType_id) {
if ($objType_id != -100) {
$q->where(['a.OBJECT_ID' => $objType_id]);
}
})->distinct()->orderBy('a.TAG_ID')->get(['a.TAG_ID', 'a.TAG_ID AS CHECK']);
//\Log::info ( \DB::getQueryLog () );
if (count($vTags) > 0) {
foreach ($vTags as $t) {
$checked = in_array($t->TAG_ID, $surs, TRUE) ? "checked" : "";
$t->CHECK = $checked;
array_push($tags, $t);
}
}
if (count($vTags) <= 0) {
$names = IntObjectType::where(['CODE' => $objectType])->select('NAME')->first();
$sname = count($names) > 0 ? $names->NAME : "";
$strMessage = '<br><br><br><br><center><font color="#88000">No tag configured for <b>' . $sname . '</b>.</font><br><br><input type="button" style="width:145px;" id="btnTagsMapping" value="Config Tag Mapping"></center>';
}
if ($objType_id == -100) {
$strMessage = '<br><br><br><br><center><font color="#880000">No tag displayed because object is not mapped.</font><br><br><input type="button" style="width:160px;" id="openSurveillanceSetting" value="Object Mapping"></center>';
}
return response()->json(['cfgFieldProps' => $cfgFieldProps, 'intConnection' => $intConnection, 'tags' => $tags, 'strMessage' => $strMessage]);
}
public function doImport()
{
$files = Input::all();
$tabIndex = $files['tabIndex'];
$tagColumn = $files['tagColumn'];
$timeColumn = $files['timeColumn'];
$valueColumn = $files['valueColumn'];
$rowStart = $files['rowStart'];
$rowFinish = $files['rowFinish'];
$cal_method = $files['cal_method'];
$date_begin = $files['date_begin'];
$date_begin = Carbon::parse($date_begin);
$date_end = $files['date_end'];
$date_end = Carbon::parse($date_end);
$update_db = $files['update_db'];
$update_db = $update_db == 1;
$path = "";
$tmpFilePath = '/fileUpload/';
$error = false;
$str = "";
if (count($files) > 0) {
$file = $files['file'];
$tmpFileName = $file->getClientOriginalName();
$fileName = $tmpFileName;
$v = explode('.', $tmpFileName);
$tmpFileName = $v[0] . '_' . time() . '.' . $v[1];
$data = [];
$file = $file->move(public_path() . $tmpFilePath, $tmpFileName);
if ($file) {
$path = public_path() . $tmpFilePath . $tmpFileName;
ini_set('max_execution_time', 300);
$xxx = Excel::selectSheets($tabIndex)->load($path, function ($reader) use($data, $tagColumn, $timeColumn, $valueColumn, $tabIndex, $rowStart, $rowFinish, $date_begin, $date_end, $fileName, $update_db, $cal_method, $str, $path) {
// $reader->calculate();
// $results = $reader->get()->toArray();
// $reader->skip($rowStart-1)->take($rowFinish-$rowStart+1);
// $reader->setDateColumns(array($timeColumn));
// $reader->formatDates(true,$timeFormat);
// $reader->formatDates(true);
$objExcel = $reader->getExcel();
$sheet = $objExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
if ($rowFinish > $highestRow) {
$rowFinish = $highestRow;
}
$current_username = '';
if (auth()->user() != null) {
$current_username = auth()->user()->username;
}
$condition = array('ID' => -1);
$begin_time = date('Y-m-d H:i:s');
$obj['FILE_NAME'] = $fileName;
$obj['FILE_SIZE'] = $highestRow;
$obj['BEGIN_TIME'] = $begin_time;
$obj['USER_NAME'] = $current_username;
$int_import_log = IntImportLog::updateOrCreate($condition, $obj);
$log_id = $int_import_log->ID;
$tags_read = 0;
$tags_override = 0;
$tags_loaded = 0;
$tags_rejected = 0;
$tags_addnew = 0;
$html = "";
$datatype = "";
if (!$datatype) {
$datatype = "NUMBER";
}
// $db_schema = ENV('DB_DATABASE');
$db_schema = "energy_builder";
for ($row = $rowStart; $row <= $rowFinish; $row++) {
$arr = [];
$tags_read++;
$hasError = false;
$err = "";
$statusCode = "Y";
try {
$dateTimeVL = $this->getCellValue($sheet, $timeColumn . $row, true);
$unixTime = PHPExcel_Shared_Date::ExcelToPHP($dateTimeVL);
// $carbonDate = $this->proDate($time);
$carbonDate = Carbon::createFromTimestamp($unixTime);
$date = $carbonDate->format('m/d/Y');
if ($carbonDate && $carbonDate->gte($date_begin) && $date_end->gte($carbonDate)) {
// $tagID = $sheet->rangeToArray($tagColumn.$row)[0][0];
// $sheet->rangeToArray($valueColumn.$row)[0][0];
$tagID = $this->getCellValue($sheet, $tagColumn . $row);
$value = $this->getCellValue($sheet, $valueColumn . $row);
if (!$tagID || $tagID == "") {
$hasError = true;
if ($date && $date != "" || $value && $value != "") {
$statusCode = "NTG";
$err = "No tag ID";
} else {
$statusCode = "NT";
$err = "No tag";
}
} else {
if ($datatype == "NUMBER" && !is_numeric($value)) {
$hasError = true;
$statusCode = "NF";
$err = "Not a number: {$value}";
}
}
if (!$hasError) {
if (!$date || $date == "") {
$hasError = true;
$statusCode = "ND";
$err = "No date";
} else {
$Y = $carbonDate->year;
if ($Y == 1970) {
$hasError = true;
$statusCode = "NWD";
$err = "Wrong date";
}
}
}
$impSQL = "";
$sqls = [];
if (!$hasError) {
$r_t = IntTagMapping::where(['TAG_ID' => $tagID])->get();
if ($r_t->count() <= 0) {
$hasError = true;
$statusCode = "NG";
$err = "Tag mapping not found";
} else {
foreach ($r_t as $r) {
$table_name = strtoupper($r->TABLE_NAME);
$column_name = strtoupper($r->COLUMN_NAME);
$cc = \DB::table('INFORMATION_SCHEMA.TABLES')->where('TABLE_SCHEMA', '=', $db_schema)->where('TABLE_NAME', '=', $table_name)->select("TABLE_NAME")->first();
if ($cc) {
$cc = \DB::table('INFORMATION_SCHEMA.COLUMNS')->where('TABLE_SCHEMA', '=', $db_schema)->where('TABLE_NAME', '=', $table_name)->where('COLUMN_NAME', '=', $column_name)->select("COLUMN_NAME")->first();
if (!$cc) {
$hasError = true;
$statusCode = "NC";
$err = "Column not found ({$column_name})";
}
} else {
$hasError = true;
$statusCode = "NT";
$err = "Table not found ({$table_name})";
}
if (!$hasError) {
$objIDField = $this->getObjectIDFiledName($table_name);
$values = [$objIDField => $r->OBJECT_ID];
$attributes = [$objIDField => $r->OBJECT_ID];
$sF = "";
$sV = "";
// $dateString = $date->format('m/d/Y');
$dateString = $date;
if (substr($table_name, 0, 12) == "ENERGY_UNIT_") {
$sF .= ",FLOW_PHASE";
$sV .= ",{$r->FLOW_PHASE}";
$sF .= ",EVENT_TYPE";
$sV .= ",{$r->EVENT_TYPE}";
$attributes["FLOW_PHASE"] = $r->FLOW_PHASE;
$attributes["EVENT_TYPE"] = $r->EVENT_TYPE;
$values["FLOW_PHASE"] = $r->FLOW_PHASE;
$values["EVENT_TYPE"] = $r->EVENT_TYPE;
}
if ($table_name == "ENERGY_UNIT_DATA_ALLOC") {
$sF .= ",ALLOC_TYPE";
$sV .= ",{$r->ALLOC_TYPE}";
$attributes["ALLOC_TYPE"] = $r->ALLOC_TYPE;
$values["ALLOC_TYPE"] = $r->ALLOC_TYPE;
}
$mdl = \Helper::getModelName($table_name);
if ($update_db) {
$attributes["OCCUR_DATE"] = $carbonDate;
$values["OCCUR_DATE"] = $carbonDate;
$values[$column_name] = $value;
$entry = $mdl::updateOrCreate($attributes, $values);
if ($entry->wasRecentlyCreated) {
$tags_addnew++;
$sSQL = "insert into `{$table_name}`(`{$objIDField}`,OCCUR_DATE,`{$column_name}`{$sF}) values({$r->OBJECT_ID},'{$date}','{$value}'{$sV})";
} else {
$tags_override++;
$rID = $entry->ID;
$sSQL = "update `{$table_name}` set `{$column_name}`='{$value}' where ID={$rID}";
}
} else {
$entry = $mdl::where($attributes)->whereDate("OCCUR_DATE", "=", $carbonDate)->get();
if ($entry) {
$sSQL = "insert into `{$table_name}`(`{$objIDField}`,OCCUR_DATE,`{$column_name}`{$sF}) values({$r->OBJECT_ID},'{$date}','{$value}'{$sV})";
} else {
$rID = $entry->ID;
$sSQL = "update `{$table_name}` set `{$column_name}`='{$value}' where ID={$rID}";
}
}
$sqls[] = $sSQL;
$impSQL .= ($impSQL ? "<bt>" : "") . $sSQL;
$tags_loaded++;
}
}
}
}
if ($hasError) {
$tags_rejected++;
}
if ($tagID && $tagID !== "" && $value && $value !== "" && $date && $date !== "") {
$load_time = date('Y-m-d H:i:s');
IntTagTrans::insert(['LOG_ID' => $log_id, 'TAG_ID' => $tagID, 'VALUE' => $value, 'DATE' => $date, 'DATA_TYPE' => $datatype, 'LOAD_TIME' => $load_time, 'STATUS_CODE' => $err]);
}
$html .= "<tr><td>{$tagID}</td><td>{$value}</td><td>{$date}</td><td>{$statusCode}</td><td>{$err}</td><td>{$impSQL}</td></tr>";
} else {
$hasError = true;
$statusCode = "DOR";
$err = "Date/time out of range";
}
} catch (Exception $e) {
$hasError = true;
$statusCode = $e->getMessage();
}
}
$end_time = date('Y-m-d H:i:s');
IntImportLog::where(['ID' => $log_id])->update(['END_TIME' => $end_time, 'TAGS_READ' => $tags_read, 'TAGS_LOADED' => $tags_loaded, 'TAGS_REJECTED' => $tags_rejected, 'TAGS_OVERRIDE' => $tags_override]);
$str .= "<h3>Import log</h3>";
$str .= "<input type='button' style='display:none' value='Back' onclick=\"document.location.href='/doimport';\" />";
$str .= "<table>";
$str .= "<tr><td>Filename</td><td>: <b>" . $fileName . "</b></td><td> Filesize</td><td>: <b> " . $highestRow . "</b></td></tr>";
$str .= "<tr><td>From date</td><td>: " . $date_begin . "</td></tr>";
$str .= "<tr><td>To date</td><td>: " . $date_end . "</td></tr>";
$str .= "<tr><td>Tab</td><td>: " . $tabIndex . "</td></tr>";
$str .= "<tr><td>Tag Column</td><td>: " . $tagColumn . "</td></tr>";
$str .= "<tr><td>Time column</td><td>: " . $timeColumn . "</td></tr>";
$str .= "<tr><td>Value column</td><td>: " . $valueColumn . "</td></tr>";
$str .= "<tr><td>Row start</td><td>: " . $rowStart . "</td></tr>";
$str .= "<tr><td>Row finish</td><td>: " . $rowFinish . "</td></tr>";
$str .= "<tr><td>Update database</td><td>: <b>" . ($update_db ? "Yes" : "No") . "</b></td></tr>";
$str .= "<tr><td>Data method</td><td>: " . $cal_method . "</td></tr>";
$str .= "<tr><td></td></tr>";
$str .= "<tr><td>Tags read</td><td>: " . $tags_read . "</td></tr>";
$str .= "<tr><td>Tags loaded</td><td>: " . $tags_loaded . "</td></tr>";
$str .= "<tr><td>Tags rejected</td><td>: " . $tags_rejected . "</td></tr>";
$str .= "<tr><td>Tags override</td><td>: " . $tags_override . "</td></tr>";
$str .= "<tr><td>Tags added</td><td>: " . $tags_addnew . "</td></tr>";
$str .= "<tr><td>Begin time</td><td>: " . $begin_time . "</td></tr>";
$str .= "<tr><td>End time</td><td>: " . $end_time . "</td></tr>";
$str .= "</table>";
$str .= "<br>";
$str .= "<table><tr>";
$str .= "<td><b>Tag</b></td><td><b>Value</b></td><td><b>Date/time</b></td>";
$str .= "<td><b>Code</b></td><td><b>Status</b></td><td><b>Command</b></td>";
$str .= "</tr> " . $html . "\t</table>";
$reader->select(['str' => $str])->first();
});
ini_set('max_execution_time', 30);
}
}
if (file_exists($path)) {
unlink($path);
}
return response()->json($xxx);
}
