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); }