Example #1
0
 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));
 }
Example #2
0
 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);
 }