public ExtractorExcel(string mConfigName)
{
ConfigName = mConfigName;
FilePath = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribExcel_FilePath);
Host = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribExcel_Host);
User = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribExcel_User);
Password = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribExcel_Password);
Destination = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribExcel_Destination);
Period = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribExcel_Period);
NamePrefix = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribExcel_NamePrefix);
ModifiedOnly = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribExcel_ModifiedOnly);
timer = new System.Timers.Timer();
int x = 0;
if (Int32.TryParse(Period, out x))
{
timer.Interval = x * 1000;
}
else
{
LogFile.write_LogFile("Error parsing to (int) the Period (string) found at the ConfigFile for configuration: " + ConfigName + ". Defaulting to 30 second extraction period.");
timer.Interval = 30000; //default time = 30 seconds
}
timer.Elapsed += new System.Timers.ElapsedEventHandler(timerTick);
}
public ExtractorPIConfig(string mConfigName)
{
ConfigName = mConfigName;
Host = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribPIConfig_Host);
Port = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribPIConfig_Port);
User = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribPIConfig_User);
Pass = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribPIConfig_Pass);
OutP = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribPIConfig_OutP);
Peri = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribPIConfig_Peri);
Pref = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribPIConfig_Pref);
Mtrx = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribPIConfig_Mtrx);
Inte = ConfigFile.read_attribute(mConfigName, ConfigFile.AttribPIConfig_Inte);
timer = new System.Timers.Timer();
int x = 0;
if (Int32.TryParse(Peri, out x))
{
timer.Interval = x * 1000;
}
else
{
LogFile.write_LogFile("Error parsing to (int) the Period (string) found at the ConfigFile for configuration: " + ConfigName + ". Defaulting to 60 second extraction period.");
timer.Interval = 60000; //default time = 30 seconds
}
timer.Elapsed += new System.Timers.ElapsedEventHandler(timerTick);
}
/// <summary>
/// Returns a List fo Strings with all Config Names from the config File.
/// </summary>
/// <returns></returns>
public static List <string> ConfigNames()
{
List <string> configNames = new List <string>();
string ConfigFile = InstalPath + "\\Configuration.xml";
if (File.Exists(ConfigFile))
{
XmlDocument doc = new XmlDocument();
doc.Load(ConfigFile);
foreach (XmlNode itemNode in doc.SelectNodes("/AllConfig/Config"))
{
if (itemNode.Attributes[Attrib_Name] != null)
{
configNames.Add(itemNode.Attributes[Attrib_Name].Value);
}
}
}
else
{
LogFile.write_LogFile("Config file not found during load of config names. New blank file created.");
File.WriteAllText(ConfigFile, "<AllConfig></AllConfig>");
}
return(configNames);
}
public static string read_attribute(string config_name, string atribute_name)
{
string ConfigFile = InstalPath + "\\Configuration.xml";
if (File.Exists(ConfigFile))
{
XmlDocument doc = new XmlDocument();
doc.Load(ConfigFile);
foreach (XmlNode itemNode in doc.SelectNodes("/AllConfig/Config"))
{
if (itemNode.Attributes[Attrib_Name] != null && itemNode.Attributes[Attrib_Name].Value == config_name)
{
if (itemNode.Attributes[atribute_name] != null)
{
return(itemNode.Attributes[atribute_name].Value);
}
else
{
return("");
}
}
}
LogFile.write_LogFile("Config name \"" + config_name + "\" or attribute \"" + atribute_name + "not found during read.");
return("");
}
LogFile.write_LogFile("Config File not found during read of attribute \"" + atribute_name + "\" for config \"" + config_name + "\".");
return("");
}
//Writes a value to a atribute of a configuration
public static bool write_attribute(string config_name, string atribute_name, string value)
{
string ConfigFile = InstalPath + "\\Configuration.xml";
if (File.Exists(ConfigFile))
{
XmlDocument doc = new XmlDocument();
doc.Load(ConfigFile);
foreach (XmlNode itemNode in doc.SelectNodes("/AllConfig/Config"))
{
if (itemNode.Attributes[Attrib_Name] != null && itemNode.Attributes[Attrib_Name].Value == config_name)
{
if (itemNode.Attributes[atribute_name] != null)
{
itemNode.Attributes[atribute_name].Value = value;
}
else
{
XmlAttribute ConfigName = doc.CreateAttribute(atribute_name);
ConfigName.Value = value;
itemNode.Attributes.Append(ConfigName);
}
doc.Save(ConfigFile);
return(true);
}
}
LogFile.write_LogFile("Config name \"" + config_name + "\" was not found in config file during attributes update.");
return(false);
}
LogFile.write_LogFile("Config file not found during write of attribute \"" + atribute_name + " for config \"" + config_name + "\".");
return(false);
}
public static void DeleteConfig(string m_Configname)
{
string ConfigFile = InstalPath + "\\Configuration.xml";
if (File.Exists(ConfigFile))
{
XmlDocument doc = new XmlDocument();
doc.Load(ConfigFile);
foreach (XmlNode itemNode in doc.SelectNodes("/AllConfig/Config"))
{
if (itemNode.Attributes[Attrib_Name] != null && itemNode.Attributes[Attrib_Name].Value == m_Configname)
{
doc.DocumentElement.RemoveChild(itemNode);
doc.Save(ConfigFile);
return;
}
}
}
else
{
LogFile.write_LogFile("Config file not found during deletion of config: " + m_Configname + ".");
}
}
/// <summary>
/// Returns TRUE if file was modified or if it's new.
/// </summary>
/// <param name="m_filePath">Full file path like: C:\\folder\\file.xls</param>
/// <returns></returns>
public static bool FileModified(string m_filePath)
{
Support.CreateFile(ProcessedFilesLog); // if file already exists, nothing is done.
string[] ProcessedFiles = Support.getFileLines(ProcessedFilesLog);
foreach (string ProcessedFile in ProcessedFiles)
{
if (ProcessedFile.Split(';')[0] == m_filePath && ProcessedFile.Split().Length > 1)
{
try
{
FileInfo FileInfo = new FileInfo(m_filePath);
//LogFile.write_LogFile("FileModified?: " + FileInfo.LastWriteTime.ToString() + "==" + ProcessedFile.Split(';')[1] + " e " + (BuildMd5Checksum(m_filePath) + "==" + ProcessedFile.Split(';')[2]));
if (FileInfo.Name.Contains("~$"))
{
return(false); //file is a temporary file
}
if (FileInfo.LastWriteTime.ToString() == ProcessedFile.Split(';')[1]) // && (BuildMd5Checksum(m_filePath) == ProcessedFile.Split(';')[2]))
{
return(false); //file wasnt modified
}
return(true); //file was modified
}
catch (Exception e)
{
LogFile.write_LogFile("Error verifying file modification for: " + m_filePath + " with message: " + e.Message);
return(false);
}
}
}
//File is new
return(true);
}
private bool runExtraction()
{
string[] FilesToExtract;
string filename = "";
string filefolder = "";
string[] pathParts = Regex.Split(FilePath, @"(.*[\\|\/])([^\\|\/]*)");
if (pathParts.Length != 4)
{
LogFile.write_LogFile("Bad file Path format for: " + FilePath);
return(false);
}
else
{
filename = pathParts[2].Replace("<yyyy>", DateTime.Now.ToString("yyyy"))
.Replace("<yy>", DateTime.Now.ToString("yy"))
.Replace("<MM>", DateTime.Now.ToString("MM"));
filefolder = pathParts[1].Replace("<yyyy>", DateTime.Now.ToString("yyyy"))
.Replace("<yy>", DateTime.Now.ToString("yy"))
.Replace("<MM>", DateTime.Now.ToString("MM"));
}
try
{
FilesToExtract = Directory.GetFiles(filefolder, filename);
}
catch (Exception e)
{
LogFile.write_LogFile("Error trying to get files in: " + FilePath + ". ErrorMessage=" + e.Message);
return(false);
}
//for each file in FilesToExtract check if it has been modified and the run ConvertExcelFile(that file)
//If conversion goes trough, add that file to processedfiles Log.
foreach (string filepath in FilesToExtract)
{
//LogFile.write_LogFile("checking excel file: " +filepath);
if (Support.FileModified(filepath) || ModifiedOnly == "0")
{
FileInfo currentFileInfo = new FileInfo(filepath);
string tempFilePath = Support.InstalPath + "\\temp\\" + currentFileInfo.Name;
File.Copy(filepath, tempFilePath);
if (ConvertExcelFile(tempFilePath))
{
Support.RecordProcessedFile(filepath);
}
File.Delete(tempFilePath);
}
}
return(true);
}
/// <summary>
/// Reads lines from a text file located on instal path.
/// </summary>
/// <param name="FileName"> Name of the file like: FileName.txt</param>
/// <returns></returns>
public static string[] getFileLines(string FileName)
{
try
{
return(System.IO.File.ReadAllLines(InstalPath + "\\" + FileName, Encoding.UTF8));
}
catch
{
LogFile.write_LogFile("Fail trying to read lines from file: " + FileName);
return(new string[] { "" });
}
}
protected override void OnStop()
{
try
{
LogFile.write_LogFile("Services stoped");
foreach (ExtractorInterface Extractor in Extractors)
{
Extractor.Stop();
}
}
catch (Exception e)
{
LogFile.write_LogFile(e);
}
}
/// <summary>
/// Write a line to a text file located on instal path.
/// </summary>
/// <param name="FileName">Name of the file like: FileName.txt</param>
/// <param name="text">Text to be written</param>
/// <param name="append">If append=false, file will be erased before writing.</param>
public static void WriteOneLine(string FileName, string text, bool append = true)
{
try
{
StreamWriter Writer = new StreamWriter(InstalPath + "\\" + FileName, append, Encoding.UTF8);
if (text != "")
{
Writer.WriteLine(text);
}
Writer.Flush();
Writer.Close();
}
catch
{
LogFile.write_LogFile("Fail trying to write line to file: " + FileName);
}
}
private void button_restartService_Click(object sender, EventArgs e)
{
ServiceController serviceController = new ServiceController("MAZE");
try
{
if ((serviceController.Status.Equals(ServiceControllerStatus.Stopped)))
{
serviceController.Start();
button_restartService.Text = "Starting...";
button_restartService.Enabled = false;
serviceController.WaitForStatus(ServiceControllerStatus.Running);
}
}
catch (Exception exc)
{
LogFile.write_LogFile("Error trying to restart service: " + exc.Message);
}
}
private void button_PIConfig_AllTagList_Click(object sender, EventArgs e)
{
try
{
if (listBox_ConfigList.SelectedIndex != -1)
{
string configName = listBox_ConfigList.GetItemText(listBox_ConfigList.SelectedItem);
string TagFilePath = Support.InstalPath + "\\" + Support.AllTagFilePrefix + configName + ".txt";
Support.CreateFile(Support.AllTagFilePrefix + configName + ".txt");
Process p = Process.Start(TagFilePath);
//p.WaitForExit();
return;
}
}
catch (Exception exp)
{
LogFile.write_LogFile("Error trying to open All Tags List: " + exp.Message);
}
}
public override void Uninstall(IDictionary savedState)
{
ServiceController serviceController = new ServiceController("MAZE");
try
{
if ((serviceController.Status.Equals(ServiceControllerStatus.Running)) || (serviceController.Status.Equals(ServiceControllerStatus.StartPending)))
{
serviceController.Stop();
serviceController.WaitForStatus(ServiceControllerStatus.Stopped);
//File.WriteAllText("C:\\Program Files (x86)\\MAZE\\INSTALLOG.txt", "Unistalling service from ProjectINstaller.cs");
}
}
catch (Exception exc)
{
LogFile.write_LogFile("Error trying to stop serv:" + exc.Message);
}
base.Uninstall(savedState);
}
//Methods for checking if files were already processed and updating the record of processed files.
#region Processed Files Log Managing
/// <summary>
/// Returns the unique HASH string for a file.
/// </summary>
/// <param name="filename"></param>
/// <returns></returns>
private static string BuildMd5Checksum(string filename)
{
using (var md5 = MD5.Create())
{
try
{
using (var stream = File.OpenRead(filename))
{
string Hash = BitConverter.ToString(md5.ComputeHash(stream));
stream.Close();
stream.Dispose();
return(Hash);
}
}
catch (IOException ex)
{
LogFile.write_LogFile("Error building HASH for processed file: " + ex.Message);
return(null);
}
}
}
public static void CreateNewConfig(string m_Configname)
{
string ConfigFile = InstalPath + "\\Configuration.xml";
if (!File.Exists(ConfigFile))
{
File.WriteAllText(ConfigFile, "<AllConfig></AllConfig>");
LogFile.write_LogFile("Config file not found. New file created and config " + m_Configname + "was added");
}
XmlDocument doc = new XmlDocument();
doc.Load(ConfigFile);
XmlNode ConfigNode = doc.CreateElement("Config");
XmlAttribute ConfigName = doc.CreateAttribute(Attrib_Name);
doc.DocumentElement.AppendChild(ConfigNode);
ConfigNode.Attributes.Append(ConfigName);
ConfigName.Value = m_Configname;
doc.Save(ConfigFile);
}
protected override void OnStart(string[] args)
{
List <string> configNames = ConfigFile.ConfigNames();
try
{
foreach (string configName in configNames)
{
if (ConfigFile.read_attribute(configName, ConfigFile.Attrib_Type) == ConfigFile.TypeConfig_Excel)
{
LogFile.write_LogFile("Service thread created for config name \"" + configName + "\" of type \"" + ConfigFile.TypeConfig_Excel + "\"");
ExtractorExcel excel = new ExtractorExcel(configName);
excel.Start();
Extractors.Add(excel);
}
else if (ConfigFile.read_attribute(configName, ConfigFile.Attrib_Type) == ConfigFile.TypeConfig_ACCDB)
{
LogFile.write_LogFile("Service thread created for config name \"" + configName + "\" of type \"" + ConfigFile.TypeConfig_ACCDB + "\"");
ExtractorAccdb accdb = new ExtractorAccdb(configName);
accdb.Start();
Extractors.Add(accdb);
}
else if (ConfigFile.read_attribute(configName, ConfigFile.Attrib_Type) == ConfigFile.TypeConfig_PIConfig)
{
LogFile.write_LogFile("Service thread created for config name \"" + configName + "\" of type \"" + ConfigFile.TypeConfig_PIConfig + "\"");
ExtractorPIConfig PIConfig = new ExtractorPIConfig(configName);
PIConfig.Start();
Extractors.Add(PIConfig);
}
}
}
catch (Exception e)
{
LogFile.write_LogFile(e);
}
}
/// <summary>
/// Tries to convert excel file to csv. Returns TRUE in case of success.
/// </summary>
/// <param name="m_filePath"></param>
/// <returns></returns>
public bool ConvertAccdbFile(string m_filePath)
{
string[] tablelistLines;
bool fileinlist = false;
List <string[]> TableFilters = new List <string[]>();
try
{
Support.CreateFile(Support.TablelistFilePrefix + ConfigName + ".txt");
tablelistLines = Support.getFileLines(Support.TablelistFilePrefix + ConfigName + ".txt");
}
catch
{
LogFile.write_LogFile("No WhiteList file was found. All files will be converted.");
tablelistLines = new string[] { "*;*" };
}
FileInfo currentFileInfo = new FileInfo(m_filePath);
if (Regex.IsMatch(currentFileInfo.Extension, @"(.*db$)") || currentFileInfo.Extension == ".oee")
{
try
{
string fileName = currentFileInfo.Name;
foreach (string currentLine in tablelistLines)
{
string[] currentLineSplit = currentLine.Split(';');
if (Regex.IsMatch(fileName.ToLower(), "^" + currentLineSplit[0].ToLower().Replace("*", ".*") + "$"))
{
fileinlist = true;
if (currentLineSplit.Length < 4)
{
//LogFile.write_LogFile("Table filter: "+currentLineSplit[0]);
TableFilters.Add(new string[] { currentLineSplit[1] });
}
else if (currentLineSplit.Length >= 4)
{
//LogFile.write_LogFile("Table filter: "+currentLineSplit[0]+"," + currentLineSplit[1]+"," + currentLineSplit[2]);
TableFilters.Add(new string[] { currentLineSplit[1], currentLineSplit[2], currentLineSplit[3] });
}
}
}
}
catch (Exception e)
{
LogFile.write_LogFile(e);
}
//Don't extract if there's no table defined
if (!fileinlist)
{
//LogFile.write_LogFile("File não tava na lista");
return(false);
}
DataSet outputDS = new DataSet();
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + m_filePath + ";Persist Security Info=False";
OleDbConnection oleCon = null;
bool OneTableExtracted = false;
try
{
using (oleCon = new OleDbConnection(ConnectionString))
{
OneTableExtracted = false;
oleCon.Open();
OleDbDataReader reader = null;
foreach (string[] tableFilter in TableFilters)
{
string SQL_Comand;
if (tableFilter.Length == 1)
{
SQL_Comand = "SELECT * FROM " + tableFilter[0] + ";";
}
else if (tableFilter.Length == 3)
{
SQL_Comand = "SELECT * FROM " + tableFilter[0] + " WHERE " + tableFilter[1] + ">(Select Max(" + tableFilter[1] + ") from " + tableFilter[0] + ")-" + tableFilter[2] + ";";
}
else
{
break;
}
//LogFile.write_LogFile("Conectando com string: " + SQL_Comand);
OleDbCommand command = new OleDbCommand(SQL_Comand, oleCon);
//command.Parameters.AddWithValue("@1", userName);
reader = command.ExecuteReader();
string outputString = "";
var table = reader.GetSchemaTable();
var nameCol = table.Columns["ColumnName"];
outputString = Regex.Replace(table.Rows[0][nameCol].ToString(), "[;|\r\n|\r|\n]", " ");
if (table.Rows.Count > 1)
{
for (int i = 1; i < table.Rows.Count; i++)
{
outputString = outputString + ";" + Regex.Replace(table.Rows[i][nameCol].ToString(), "[;|\r\n|\r|\n]", " ");
}
}
outputString = outputString + Environment.NewLine;
while (reader.Read())
{
outputString = outputString + reader[0];
if (reader.FieldCount > 1)
{
for (int i = 1; i < reader.FieldCount; i++)
{
outputString = outputString + ";" + Regex.Replace(reader[i].ToString(), "[;|\r\n|\r|\n]", " ");
}
}
outputString = outputString + Environment.NewLine;
}
string Outputpath = Regex.Split(Destination, @"(.*[\\|\/])([^\\|\/]*)")[1];
//LogFile.write_LogFile("Saving excel output to: " + currentFileInfo.Name.Split('.')[0] + "_" + Regex.Replace(currentTable.TableName, @"[;\'$]", "") + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".txt");
File.AppendAllText(Outputpath + NamePrefix + currentFileInfo.Name.Split('.')[0] + "_" + Regex.Replace(tableFilter[0], @"[;\'$]", "") + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".csv", outputString.ToString());
OneTableExtracted = true;
}
}
}
catch (Exception e)
{
LogFile.write_LogFile("Error during extraction for config: " + ConfigName + "at file:" + currentFileInfo.Name.Split('.')[0] + " with message: " + e.Message);
}
finally
{
oleCon.Close();
oleCon.Dispose();
oleCon = null;
}
return(OneTableExtracted);
}
return(false);
}
/// <summary>
/// Tries to convert excel file to csv. Returns TRUE in case of success.
/// </summary>
/// <param name="m_filePath"></param>
/// <returns></returns>
public bool ConvertExcelFile(string m_filePath)
{
string[] whitelistLines;
bool fileinlist = false;
List <string[]> SheetFilters = new List <string[]>();
try
{
FileInfo currentFileInfo = new FileInfo(m_filePath);
try
{
Support.CreateFile(Support.WhitelistFilePrefix + ConfigName + ".txt");
whitelistLines = Support.getFileLines(Support.WhitelistFilePrefix + ConfigName + ".txt");
}
catch
{
LogFile.write_LogFile("No WhiteList file was found. All files will be converted.");
whitelistLines = new string[] { "*;*" };
throw;
}
if (currentFileInfo.Extension == ".xlsx" || currentFileInfo.Extension == ".xls")
{
try
{
string fileName = currentFileInfo.Name;
foreach (string currentLine in whitelistLines)
{
string[] currentLineSplit = currentLine.Split(';');
if (Regex.IsMatch(fileName.ToLower(), "^" + currentLineSplit[0].ToLower().Replace("*", ".*") + "$"))
{
fileinlist = true;
if (currentLineSplit.Length == 2)
{
SheetFilters.Add(new string[] { currentLineSplit[1], "" });
}
else if (currentLineSplit.Length >= 3)
{
SheetFilters.Add(new string[] { currentLineSplit[1], currentLineSplit[2] });
}
}
}
}
catch (Exception e)
{
LogFile.write_LogFile("Error reading whitelist: " + e.Message);
//throw;
}
DataSet outputDS = new DataSet();
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + m_filePath + ";" + "Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1;'";
using (OleDbConnection oleCon = new OleDbConnection(ConnectionString))
{
oleCon.Open();
OleDbCommand oleCmd = new OleDbCommand();
oleCmd.Connection = oleCon;
DataTable SheetsTable = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow current_sheet in SheetsTable.Rows)
{
string sheetName = Regex.Replace(current_sheet["TABLE_NAME"].ToString(), @"[;\'$]", "");
if (!fileinlist || SheetFilters.Exists(x => Regex.IsMatch(sheetName.ToLower(), "^" + x[0].ToLower().Replace("*", ".*") + "$")))
{
oleCmd.CommandText = "select * from [" + sheetName + "$]";
DataTable currentDataTable = new DataTable();
OleDbDataAdapter OleDA;
try
{
OleDA = new OleDbDataAdapter(oleCmd);
currentDataTable.TableName = sheetName;
OleDA.Fill(currentDataTable);
}
catch
{
continue;
}
outputDS.Tables.Add(currentDataTable);
}
}
oleCmd = null;
oleCon.Close();
StringBuilder outputString = new StringBuilder();
string cellvalue = "";
foreach (DataTable currentTable in outputDS.Tables)
{
outputString.Clear();
int firstrow = 0;
foreach (string[] currentfilter in SheetFilters)
{
if (Regex.IsMatch(Regex.Replace(currentTable.TableName, @"[;\'$]", ""), "^" + currentfilter[0].Replace("*", ".*") + "$"))
{
if (!int.TryParse(currentfilter[1], out firstrow) && currentfilter[1] != "")
{
for (int i = 0; i < currentTable.Rows.Count; i++)
{
for (int j = 0; j < currentTable.Columns.Count; j++)
{
if (currentfilter[1] == currentTable.Rows[i].ItemArray[j].ToString())
{
firstrow = i;
}
}
}
}
break;
}
}
for (int i = firstrow; i < currentTable.Rows.Count; i++)
{
cellvalue = "";
for (int j = 0; j < currentTable.Columns.Count; j++)
{
cellvalue += Regex.Replace(currentTable.Rows[i].ItemArray[j].ToString(), @"[;\'$|\r\n|\r|\n]", " ") + ";";
}
outputString.AppendFormat("{0}", cellvalue.TrimEnd(';'));
outputString.AppendLine();
}
string Outputpath = Regex.Split(Destination, @"(.*[\\|\/])([^\\|\/]*)")[1];
//LogFile.write_LogFile("Saving excel output to: " + currentFileInfo.Name.Split('.')[0] + "_" + Regex.Replace(currentTable.TableName, @"[;\'$]", "") + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".txt");
try
{
File.AppendAllText(Outputpath + NamePrefix + currentFileInfo.Name.Split('.')[0] + "_" + Regex.Replace(currentTable.TableName, @"[:punct:]", "") + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".csv", outputString.ToString());
}
catch (Exception exx)
{
LogFile.write_LogFile("Error saving output for file " + currentFileInfo.Name.Split('.')[0] + " at sheet " + Regex.Replace(currentTable.TableName, @"[:punct:]", "") + " with message: " + exx.Message);
}
}
return(true);
}
}
return(false);
}
catch (Exception e)
{
LogFile.write_LogFile("Error during excel conversion, with message: " + e.Message);
LogFile.write_LogFile("File was added to list of processed files: " + m_filePath);
return(true);
}
}
//runs the extraction
private bool runExtraction()
{
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo.FileName = Support.InstalPath + "resources\\piconfig.exe";
process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
process.StartInfo.RedirectStandardInput = true;
process.StartInfo.RedirectStandardOutput = true;
process.StartInfo.UseShellExecute = false;
process.Start();
string input = configInput();
process.StandardInput.Write(input);
process.StandardInput.Flush();
process.StandardInput.Close();
string result = (process.StandardOutput.ReadToEnd());
string[] results = result.Split(new string[] { "\r\n" }, StringSplitOptions.None);
string finalresult = "";
string dataline = DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss");
string datalineout = DateTime.Now.ToString("yyyyMMdd_HHmmss");
string datalines = "Timestamp";
if (Mtrx == "0")
{
for (int i = 0; i < results.Length; i++)
{
//Selects only usefull info (removes alerts, erros and other messages)
if (results[i].Contains(",") == true)
{
string[] thisline = results[i].Split(',');
if (thisline.Length == 3)
{
DateTime timestamp;
if (DateTime.TryParse(thisline[1], out timestamp))
{
thisline[1] = timestamp.ToString("yyyyMMdd_HHmmss");
if (Inte == "1")
{
thisline[1] = datalineout;
}
if (thisline[2] == "Digital State")
{
thisline[2] = "";
}
finalresult = finalresult + thisline[0] + ";" + thisline[1] + ";" + thisline[2] + "\r\n";
}
}
}
}
}
else
{
List <string> tags = new List <string>();
List <string> times = new List <string>();
//for each line from response
for (int i = 0; i < results.Length; i++)
{
//if line is data (non data line wont contain the "," char)
if (results[i].Contains(",") == true)
{
string[] thisline = results[i].Split(',');
if (thisline.Length == 3)
{
//add tag to tag array
if (!tags.Contains(thisline[0]))
{
tags.Add(thisline[0]);
}
//add times to time array
string hora = thisline[1];
if (Inte == "1")
{
hora = dataline;
}
if (!times.Contains(hora))
{
times.Add(hora);
}
}
}
}
//creates data table (matrix format)
string[,] table = new string[tags.Count(), times.Count()];
for (int i = 0; i < results.Length; i++)
{
if (results[i].Contains(",") == true)
{
string[] thisline = results[i].Split(',');
if (thisline.Length == 3)
{
//add value to the spot on the table with corresponding tag and time
string hora = thisline[1];
if (Inte == "1")
{
hora = dataline;
}
if (thisline[2] == "Digital State")
{
thisline[2] = "";
}
table[tags.IndexOf(thisline[0]), times.IndexOf(hora)] = thisline[2];
}
}
}
foreach (var tag in tags)
{
datalines += ";" + tag;
}
datalines += "\r\n";
foreach (var time in times)
{
DateTime timestamp;
if (DateTime.TryParse(time, out timestamp))
{
datalines += timestamp.ToString("yyyyMMdd_HHmmss");
foreach (var tag in tags)
{
string value = (table[tags.IndexOf(tag), times.IndexOf(time)] != null) ? table[tags.IndexOf(tag), times.IndexOf(time)] : "";
datalines += ";" + value.Replace(";", "");
}
datalines += "\r\n";
}
}
finalresult = datalines;
}
string Outputpath = Regex.Split(OutP, @"(.*[\\|\/])([^\\|\/]*)")[1];
try
{
string filename = (Pref == "") ? ConfigName : Pref;
File.AppendAllText(Outputpath + filename + "_" + datalineout + ".csv", finalresult);
}
catch (Exception exx)
{
LogFile.write_LogFile("Error saving output for config " + ConfigName + " with message: " + exx.Message);
}
process.WaitForExit();
return(true);
}
private void button_PIConfig_GetAllTagList_Click(object sender, EventArgs e)
{
if (MessageBox.Show(this, "Are you sure you want to extract tag list from PI? \r\nThis operation may take a while...", "Fetching PI tag list", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
//fetch pitags from pi
System.Diagnostics.Process process = new System.Diagnostics.Process();
string configName = listBox_ConfigList.GetItemText(listBox_ConfigList.SelectedItem);
process.StartInfo.FileName = Support.InstalPath + "\\resources\\piconfig.exe";
process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
process.StartInfo.RedirectStandardInput = true;
process.StartInfo.RedirectStandardOutput = true;
process.StartInfo.UseShellExecute = false;
process.Start();
string input = "";
input = input + "@logi " + ConfigFile.read_attribute(configName, ConfigFile.AttribPIConfig_Host) + "," + ConfigFile.read_attribute(configName, ConfigFile.AttribPIConfig_User)
+ "," + ConfigFile.read_attribute(configName, ConfigFile.AttribPIConfig_Pass) + "," + ConfigFile.read_attribute(configName, ConfigFile.AttribPIConfig_Port) + "\r\n";
input = input + "@maxerr 65535" + "\r\n";
input = input + "@table pipoint" + "\r\n";
input = input + "@mode list" + "\r\n";
input = input + "@ostr tag,pointtype" + "\r\n";
input = input + "@ostr ..." + "\r\n";
input = input + "@select tag=*" + "\r\n";
input = input + "@endsection" + "\r\n";
input = input + "@bye";
process.StandardInput.Write(input);
process.StandardInput.Flush();
process.StandardInput.Close();
string result = (process.StandardOutput.ReadToEnd());
string[] results = result.Split(new string[] { "\r\n" }, StringSplitOptions.None);
string finalresult = "";
for (int i = 0; i < results.Length; i++)
{
//test to see if line is valid information or not (alert, error, etc)
if (results[i].Contains(",") == true)
{
finalresult = finalresult + results[i].Split(',')[0] + "\r\n";
}
}
process.WaitForExit();
//save on the alltags file
Support.CreateFile(Support.AllTagFilePrefix + configName + ".txt");
try
{
Support.WriteOneLine(Support.AllTagFilePrefix + configName + ".txt", finalresult, false);
}
catch (Exception exc)
{
LogFile.write_LogFile("Error trying to save list of all PI tags: " + exc.Message);
}
//DateTime time = DateTime.Now; // Use current time.
//string format = "dd/mm/yyyy hh:mm"; // Use this format.
}
}
