public function loadFormSetting(Request $request)
{
$data = $request->all();
$result = array();
$value = $data['value'];
$task_id = isset($data['task_id']) ? $data['task_id'] : 0;
switch ($value) {
case 'ALLOC_CHECK':
case 'ALLOC_RUN':
$network = Network::getTableName();
$allocJob = AllocJob::getTableName();
$tm = [];
$tm = DB::table($network . ' AS a')->join($allocJob . ' AS b', 'a.ID', '=', 'b.NETWORK_ID')->distinct()->get(['a.ID', 'a.NAME']);
$alloc_job = AllocJob::where(['NETWORK_ID' => $tm[0]->ID])->get(['ID', 'NAME']);
$result['network'] = $tm;
$result['allocJob'] = $alloc_job;
break;
case 'VIS_REPORT':
$result = Facility::all(['ID', 'NAME']);
break;
case 'FDC_EU':
$models = ['Facility', 'EnergyUnitGroup', 'CodeReadingFrequency', 'CodeFlowPhase', 'CodeEventType', 'CodeAllocType', 'CodePlanType', 'CodeForecastType'];
foreach ($models as $m) {
$tm = [];
$model = 'App\\Models\\' . $m;
$tm = $model::all(['ID', 'NAME']);
$result[$m] = $tm;
}
break;
case 'FDC_FLOW':
$models = ['Facility', 'CodeReadingFrequency', 'CodeFlowPhase'];
foreach ($models as $m) {
$tm = [];
$model = 'App\\Models\\' . $m;
$tm = $model::all(['ID', 'NAME']);
$result[$m] = $tm;
}
break;
case 'INT_IMPORT_DATA':
$tm = [];
$tm = IntConnection::all(['ID', 'NAME']);
$intTagSet = IntTagSet::where(['CONNECTION_ID' => $tm[0]->ID])->get(['ID', 'NAME']);
$result['IntConnection'] = $tm;
$result['IntTagSet'] = $intTagSet;
break;
default:
$result = [];
}
$task = TmWorkflowTask::where(['ID' => $task_id])->get();
$result['task'] = $task;
$result['value'] = $value;
return response()->json(array('result' => $result));
}
public function pi(Request $request)
{
$data = $request->all();
$connection_id = $data['connection_id'];
$tagset_id = $data['tagset_id'];
$cal_method = $data['cal_method'];
$date_begin = $data['date_begin'];
$date_end = $data['date_end'];
$update_db = $data['update_db'];
$int_connection = IntConnection::where(['ID' => $connection_id])->select('SERVER', 'USER_NAME', 'PASSWORD')->first();
$server = $int_connection->SERVER;
$username = $int_connection->USER_NAME;
$password = $int_connection->PASSWORD;
$intTagSet = IntTagSet::where(['ID' => $tagset_id])->select('TAGS')->first();
$ptags = $intTagSet->TAGS;
$str = "";
if ($update_db && $cal_method == "all") {
return response()->json("<font color='red'>Not allow inport data with method '<b>All</b>'</font>");
}
$tagcondition = "";
$tags = explode("\n", $ptags);
foreach ($tags as $tag) {
if ($tag) {
$tagcondition .= ($tagcondition ? " or " : "") . "tag='{$tag}'";
}
}
if ($cal_method == "max" || $cal_method == "min") {
$sql = "SELECT tt.tag,tt.TIME,tt.value\n\t\t\t\t\tFROM [piarchive].[picomp] tt\n\t\t\t\t\tinner join\n\t\t\t\t\t(\n\t\t\t\t\tSELECT tag tagx,{$cal_method}(value) mvalue\n\t\t\t\t\tFROM [piarchive].[picomp]\n\t\t\t\t\tWHERE ({$tagcondition})\n\t\t\t\t\tAND time BETWEEN '{$date_begin}' AND '{$date_end}' group by tag\n\t\t\t\t\t) grouped on tt.tag=grouped.tagx and tt.value=grouped.mvalue\n\t\t\t\t\tWHERE ({$tagcondition})\n\t\t\t\t\tAND value is not null\n\t\t\t\t\tAND time BETWEEN '{$date_begin}' AND '{$date_end}'";
} else {
if ($cal_method == "first" || $cal_method == "last") {
$func = $cal_method == "first" ? "min" : "max";
$sql = "SELECT tt.tag,tt.TIME,tt.value\n\t\t\t\t\t\tFROM [piarchive].[picomp] tt\n\t\t\t\t\t\tinner join\n\t\t\t\t\t\t(\n\t\t\t\t\t\tSELECT tag tagx,{$func}(time) mtime\n\t\t\t\t\t\tFROM [piarchive].[picomp]\n\t\t\t\t\t\tWHERE ({$tagcondition})\n\t\t\t\t\t\tAND time BETWEEN '{$date_begin}' AND '{$date_end}' group by tag\n\t\t\t\t\t\t) grouped on tt.tag=grouped.tagx and tt.time=grouped.mtime\n\t\t\t\t\t\tWHERE ({$tagcondition})\n\t\t\t\t\t\tAND value is not null\n\t\t\t\t\t\tAND time BETWEEN '{$date_begin}' AND '{$date_end}'";
} else {
if ($cal_method == "average") {
$sql = "SELECT tag, max(TIME) TIME, avg(value) value\n\t\t\t\t\t\tFROM picomp\n\t\t\t\t\t\tWHERE ({$tagcondition}) AND value is not null AND time BETWEEN '{$date_begin}' AND '{$date_end}'\n\t\t\t\t\t\tgroup by tag";
} else {
$sql = "SELECT tag, TIME, value\n\t\t\t\t\t\tFROM picomp\n\t\t\t\t\t\tWHERE ({$tagcondition}) AND value is not null AND time BETWEEN '{$date_begin}' AND '{$date_end}'";
}
}
}
$update_db = 'No';
if ($update_db) {
$supdate_db = 'Yes';
}
$str .= " <b>Import PI data</b><br>";
$str .= " Server: <b>" . $server . "</b><br>";
$str .= " Data method: <b>" . $cal_method . "</b><br>";
$str .= " Update database: <b>" . $supdate_db . "</b><br>";
$str .= " From time: <b>" . $data['date_begin'] . "</b><br>";
$str .= " To time: <b>" . $data['date_end'] . "</b><br><br>";
$connection = new \COM("ADODB.Connection") or die("Cannot start ADO");
$str .= " Open connection " . date('H:i:s') . "<br>";
$connection->Open("Initial Catalog=piarchive;\n\t\t\t\tData Source='localhost';User ID =root;Password='';");
$str .= " Begin command " . date('H:i:s') . "<br>";
$result_set = $connection->Execute($sql);
$result_count = 0;
$labels = array();
$str .= " Begin fetch data " . date('H:i:s') . "<br><br>";
$str .= " <table><tr><td><b>Tag</b></td><td><b>Date/time</b></td><td><b>Value</b></td><td><b>Code</b></td><td><b>Status</b></td><td><b>Command</b></td></tr>";
while (!$result_set->EOF) {
$impSQL = "";
$hasError = false;
$statusCode = "Y";
$err = "";
$tagID = $result_set->fields[0]->value;
$date = $result_set->fields[1]->value;
$value = $result_set->fields[2]->value;
$r_t = int_tag_mapping::where(['TAG_ID' => $tagID])->select('*')->first();
if ($update_db) {
if (count($r_t) <= 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::statement("SELECT TABLE_NAME FROM `INFORMATION_SCHEMA`.`TABLES` WHERE TABLE_SCHEMA='{$db_schema}' and `TABLE_NAME`='{$table_name}' limit 1");
if (!$cc) {
$hasError = true;
$statusCode = "NT";
$err = "Table not found ({$table_name})";
} else {
$cc = DB::statement("SELECT COLUMN_NAME FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA='{$db_schema}' and `TABLE_NAME`='{$table_name}' and COLUMN_NAME='{$column_name}' limit 1");
if (!$cc) {
$hasError = true;
$statusCode = "NC";
$err = "Column not found ({$column_name})";
}
}
if (!$hasError) {
$objIDField = $this->getObjectIDFiledName($table_name);
$sF = "";
$sV = "";
$sWhere = "{$objIDField}={$r['OBJECT_ID']} and OCCUR_DATE=DATE('{$date}')";
if (substr($table_name, 0, 12) == "ENERGY_UNIT_") {
$sWhere .= " and FLOW_PHASE={$r['FLOW_PHASE']}";
$sF .= ",FLOW_PHASE";
$sV .= ",{$r['FLOW_PHASE']}";
}
if ($table_name == "ENERGY_UNIT_DATA_ALLOC") {
$sWhere .= " and ALLOC_TYPE={$r['ALLOC_TYPE']}";
$sF .= ",ALLOC_TYPE";
$sV .= ",{$r['ALLOC_TYPE']}";
}
$tmp = DB::statement("select ID from `{$table_name}` where {$sWhere} limit 1");
if ($tmp) {
$sSQL = "update `{$table_name}` set `{$column_name}`='{$value}' where ID={$rID->ID}";
$sSQL = str_replace("''", "null", $sSQL);
$impSQL .= ($impSQL ? "<bt>" : "") . $sSQL;
if ($update_db) {
DB::update($sSQL) or $html .= "<td>" . mysql_error() . "</td>";
$tags_override++;
}
} else {
$sSQL = "insert into `{$table_name}`(`{$objIDField}`,OCCUR_DATE,`{$column_name}`{$sF}) values({$r['OBJECT_ID']},'{$date}','{$value}'{$sV})";
$sSQL = str_replace("''", "null", $sSQL);
$impSQL .= ($impSQL ? "<bt>" : "") . $sSQL;
if ($update_db) {
DB::insert($sSQL) or $html .= "<td>" . mysql_error() . "</td>";
$tags_addnew++;
}
}
$tags_loaded++;
}
}
}
}
$str .= " <tr><td>" . $result_set->fields[0]->value . '</td><td>' . $result_set->fields[1]->value . '</td><td>' . $result_set->fields[2]->value . "</td><td>{$statusCode}</td><td>{$err}</td><td>{$impSQL}</td></tr>";
$result_count = $result_count + 1;
$result_set->MoveNext();
}
$str .= " </table><br>";
$str .= " Close connection " . date('H:i:s') . "<br>";
$str .= " <br />The number of records retrieved is: " . $result_count . "<br /><br />";
$result_set->Close();
// optional
$connection->Close();
// optional
$str .= " Finished " . date('H:i:s') . "<br>";
return response()->json($str);
}