Procedures for exporting to .CSV
1. Access Edit > Tags > Objects, create five new tags as shown in table 1.
Name |
Type |
Array |
StartDateTime |
DateTime |
|
EndDateTime |
DateTime |
|
OutputFilename |
Text |
|
SelectedFileName |
Text |
|
Value |
Integer |
10 |
Table 1 – Tags to be created.
2. At Edit, access Alarms > Items and fill in as indicated in table 2.
TagName |
Condition |
Limit |
Group |
Priority |
Message |
Value[1] |
HiHi |
25 |
Warning |
0 |
Value[1] está maior que 25 |
Value[2] |
HiHi |
50 |
Warning |
0 |
Value[2] está maior que 50 |
Value[3] |
HiHi |
85 |
Warning |
0 |
Value[3] está maior que 85 |
Table 2 – Configuration for the Items tab.
3. At Edit, access Scripts > Classes and create a new class as shown in table 3.
Name |
Code |
Domain |
ExportToCSV |
CSharp |
Client |
Table 3 – Configuration for the Classes tab.
4. Double-click on the new created class and add the following code in the CodeEditor:
public void TreatAlarmCSV(string inFile, string outFile)
{
if(!System.IO.File.Exists(inFile))
return;
System.IO.FileStream fs = System.IO.File.Create(outFile);
fs.Close();
System.IO.StreamReader sr = new System.IO.StreamReader(inFile);
System.IO.StreamWriter sw = new System.IO.StreamWriter(outFile, true, Encoding.ASCII);
if(sr == null || sw == null)
return;
while(!sr.EndOfStream)
{
string line = sr.ReadLine();
string [] values = line.Split(',');
//value[0] - ActiveTime
//value[1] - NormTime
//value[2] - AckTime
//value[3] - AckStatus
//value[4] - AckRequired
//value[5] - TagName
//value[6] - Condition
//value[7] - Value
//value[8] - Message
//value[9] - Area
//value[10] - State
//value[11] - Priority
//value[12] - UserName
//value[13] - Comments
//value[14] - ColorFG
//value[15] - ColorBG
if(values.Length > 8)
{
string message = values[0] + "," + values[5] + "," + values[6] + "," + values[7] + "," + values[8];
sw.WriteLine(message);
}
}
sw.Flush();
sw.Close();
sr.Close();
}
5. The display that will be used for testing will be as shown in Figure 1. Each part will be explained individually.
Figure 1 – Test display.
6. Part 1 modifies the values of the tags related to the alarms to trigger them, besides the RESET button to zero the value of each respective tag. Use the configuration shown in figure 2 for the vertical sliders. Change the Value [2] and Value [3] tags to the other sliders. The configuration for the RESET button is shown in figure 3.
Figure 2 – Sliders config.
Figure 3 – RESET button config.
7. Part 2 shows alarms triggered and the date and time at which alarms can be filtered when the file is generated.
- AlarmWindow is in the left menu and does not need to be configured.
- The Date picker control and DateTime textbox are also in the left menu. To configure them, click only once on them and insert the StartDateTime tags into the SelectedDate field for the start date and EndDateTime controls for the end date.
8. Part 3 is in the file name that will be created, as well as in the export itself. To configure it, create a textbox that is in the left menu and configure it as indicated in figure 4.
Figure 4 – Textbox config.
To configure the Export button, double-click on it and select Action > RunScript > New and add the following code in the method related to the button:
String fileName = @Info.ExecutionPath + @Tag.OutputFilename + ".txt";
String fileName2 = @Info.ExecutionPath + @Tag.OutputFilename + ".csv";
@Alarm.Group.Warning.SaveToTextFile(@Tag.StartDateTime.LocalDateTime, @Tag.EndDateTime.LocalDateTime, fileName, ',', true, 6, null);
@Script.Class.ExportToCSV.TreatAlarmCSV(fileName, fileName2);
MessageBox.Show("Alarmes exportados para a pasta de projetos ActionNET. \nNome do arquivo: " + @Tag.OutputFilename+".csv");
Procedures for Exporting to HTML
1. In the same project, add three new tags as indicated in table 4.
Name |
Type |
Array |
filterAlarm |
Text |
|
valueRow |
Text |
10 |
DBStatus |
Text |
Table 4 – Tags to be created.
2. Access Edit > Datasets > Tables and create a new table as indicated in table 5.
Name |
DB |
TableName |
WhereCondition |
Access |
MyTable |
AlarmHistorian |
Alarms |
{Tag.filterAlarm} |
Read |
Table 5 – Configuration of the table to be created.
In Mapping, click "..." and set up as shown in figure 5.
Figure 5 – Mapping config.
3. Access Edit > Scripts > Classes and double-click the ClientMain class. Enter the following code into CodeEditor:
// Create HTML from the Dataset
public void TreatAlarmDBToHtmlTable(string outFile)
{
System.IO.FileStream fs = System.IO.File.Create(outFile);
fs.Close();
System.IO.StreamWriter sw = new System.IO.StreamWriter(outFile, true, Encoding.UTF8);
if(sw == null)
return;
string header = "<HTML><!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 Transitional//EN\"><HTML><HEAD><META http-equiv=Content-Type content=\"text/html; charset=utf-8\"><META content=\"MSHTML 6.00.6000.16788\" name=GENERATOR></HEAD><BODY STYLE=\"font-family:Tahoma;font-size:12;\"><DIV STYLE=\"text-align:Center;font-family:tahoma;font-size:11;\"><SPAN STYLE=\"font-style:italic;font-size:20;font-family:tahoma;\">";
header += "Relatório";
header += "</SPAN></DIV><BR /><DIV><HR /></DIV><BR />";
header += "<DIV><SPAN STYLE=\"font-family:tahoma;font-size:11;\"> Data Inicial : " + @Tag.StartDateTime.ToString() + "</SPAN></DIV>";
header += "<DIV><SPAN STYLE=\"font-family:tahoma;font-size:11;\"> Data Final : " + @Tag.EndDateTime.ToString() + "</SPAN></DIV>";
header += "<BR /><DIV><HR /></DIV><BR />";
header += "<TABLE width=\"691\" align=\"Center\" vAlign=\"top\" STYLE=\"border-width:2 2 2 2;border-color:#000000;border-style:solid;mso-element:para-border-div;font-family:tahoma;font-size:11;\">";
header += "<TBODY>";
sw.WriteLine(header);
@Tag.filterAlarm = @Tag.StartDateTime.Value.UtcDateTime.Ticks.ToString() + " < [ActiveTime_Ticks] AND " + @Tag.EndDateTime.Value.UtcDateTime.Ticks.ToString() + " > [ActiveTime_Ticks]";
int status = 0;
string statusMessage = "";
@Tag.DBStatus = "";
DataTable table = @Dataset.Table.MyTable.SelectCommandWithStatus(out status, out statusMessage);
if (status == 0)
{
if(table.Rows.Count == 0)
{
@Tag.DBStatus = "";
}
for (int i = 0; i < @Dataset.Table.MyTable.RowCount; i++)
{
DateTime activeTime = new DateTime(TConvert.To<long>(@Tag.valueRow[0]));
DateTime normTime = new DateTime(TConvert.To<long>(@Tag.valueRow[1]));
DateTime ackTime = new DateTime(TConvert.To<long>(@Tag.valueRow[2]));
string message = "<TR>";
message += "<TD width=\"171\" align=\"left\" vAlign=\"top\" STYLE=\"border-width:1 1 1 1;border-color:#000000;border-style:solid;mso-element:para-border-div;font-family:tahoma;font-size:11;\"><DIV STYLE=\"text-align:Center;font-family:tahoma;font-size:11;\"><SPAN STYLE=\"font-family:tahoma;font-size:11;\">" + activeTime.ToLocalTime().ToString() + "</SPAN></DIV></TD>";
message += "<TD width=\"159\" align=\"left\" vAlign=\"top\" STYLE=\"border-width:1 1 1 1;border-color:#000000;border-style:solid;mso-element:para-border-div;font-family:tahoma;font-size:11;\"><DIV STYLE=\"text-align:Center;font-family:tahoma;font-size:11;\"><SPAN STYLE=\"font-family:tahoma;font-size:11;\">" + normTime.ToLocalTime().ToString() + "</SPAN></DIV></TD>";
message += "<TD width=\"152\" align=\"left\" vAlign=\"top\" STYLE=\"border-width:1 1 1 1;border-color:#000000;border-style:solid;mso-element:para-border-div;font-family:tahoma;font-size:11;\"><DIV STYLE=\"text-align:Center;font-family:tahoma;font-size:11;\"><SPAN STYLE=\"font-family:tahoma;font-size:11;\">" + ackTime.ToLocalTime().ToString() + "</SPAN></DIV></TD>";
message += "<TD width=\"100\" align=\"left\" vAlign=\"top\" STYLE=\"border-width:1 1 1 1;border-color:#000000;border-style:solid;mso-element:para-border-div;font-family:tahoma;font-size:11;\"><DIV STYLE=\"text-align:Center;font-family:tahoma;font-size:11;\"><SPAN STYLE=\"font-family:tahoma;font-size:11;\">" + @Tag.valueRow[3] + "</SPAN></DIV></TD>";
message += "<TD width=\"109\" align=\"left\" vAlign=\"top\" STYLE=\"border-width:1 1 1 1;border-color:#000000;border-style:solid;mso-element:para-border-div;font-family:tahoma;font-size:11;\"><DIV STYLE=\"text-align:Center;font-family:tahoma;font-size:11;\"><SPAN STYLE=\"font-family:tahoma;font-size:11;\">" + @Tag.valueRow[4] + "</SPAN></DIV></TD>";
message += "<TD width=\"300\" align=\"left\" vAlign=\"top\" STYLE=\"border-width:1 1 1 1;border-color:#000000;border-style:solid;mso-element:para-border-div;font-family:tahoma;font-size:11;\"><DIV STYLE=\"text-align:Center;font-family:tahoma;font-size:11;\"><SPAN STYLE=\"font-family:tahoma;font-size:11;\">" + @Tag.valueRow[5] + "</SPAN></DIV></TD>";
message += "</TR>";
sw.WriteLine(message);
@Dataset.Table.MyTable.NextCommand();
}
}
else
{
@Tag.DBStatus = statusMessage;
}
string footer = "</TBODY></TABLE><BR /><BR /><BR /></BODY></HTML>";
sw.WriteLine(footer);
sw.Flush();
sw.Close();
}
4. The display that will be used for testing will be like the display to convert to .csv, but with some extra elements, as shown in figure 6.
Figure 6 – Part of the conversion test display for HTML.
5. Unlike the display in figure 1, it has a button to export to HTML, as well as a WebBrowser Window and a textbox indicating the path where the file was saved.
- Configure the button by double clicking it, go to Action > Run Script > New and add the following code in the method related to the button:
String fileName2 = @Info.ExecutionPath + @Tag.OutputFilename + ".html";
@Script.Class.ClientMain.TreatAlarmDBToHtmlTable(fileName2);
@Tag.SelectedFileName = fileName2;
MessageBox.Show("Alarmes exportados para a pasta de projetos \nNome do arquivo: " + @Tag.OutputFilename+".html"); - Configure WebBrowser Window by setting file:\\{Tag.SelectedFileName} at Url, which is in the left corner of the screen.
- Configure textbox with tag Tag.SelectedFileName and increase the MaxLength field to 256.
Results
If the procedure succeeds, the generated .csv file will be saved in the projects folder as well as the HTML file (which will also appear on the SCADA screen) and will have the structure shown in figures 7 and 8.
Figure 7 – Expected result of the generated file in .csv.
Figure 8 – Expected result of the generated HTML file.
If this does not happen, repeat the procedure in the Procedures section and try again.