/// <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);
}
//updates the log window
private void updateLOG()
{
Support.CreateFile(LogFile.LogFilename);
FileInfo FileInfo = new FileInfo(LogFile.LogFilePath);
if (FileInfo.LastWriteTime.ToString() != lastWrite)
{
lastWrite = FileInfo.LastWriteTime.ToString();
string text = "";
string[] lines = Support.getFileLines(LogFile.LogFilename);
for (int i = lines.Length - 1; i >= 0; i--)
{
text = text + lines[i] + Environment.NewLine;
}
richTextBox_Log.Text = text;
}
}
//creates the string for input file
private string configInput()
{
string result = "";
result = result + "@logi " + Host + "," + User + "," + Pass + "," + Port + "\r\n";
result = result + "@maxerr 65535" + "\r\n";
result = result + "@table pisnap" + "\r\n";
result = result + "@mode list" + "\r\n";
result = result + "@timf 1,F" + "\r\n";
result = result + "@ostr tag,time,value" + "\r\n";
result = result + "@ostr ..." + "\r\n";
result = result + "@istr tag" + "\r\n";
Support.CreateFile(Support.SelectedTagFilePrefix + ConfigName + ".txt");
result = result + "@input " + Support.InstalPath + "\\" + Support.SelectedTagFilePrefix + ConfigName + ".txt" + "\r\n";
result = result + "@endsection" + "\r\n";
result = result + "@bye";
return(result);
}
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);
}
}
/// <summary>
/// Adds/update file info on processed files log.
/// </summary>
/// <param name="m_filePath"></param>
public static void RecordProcessedFile(string m_filePath)
{
Support.CreateFile(ProcessedFilesLog); // if file already exists, nothing is done.
string[] ProcessedFiles = Support.getFileLines(ProcessedFilesLog);
FileInfo FileInfo = new FileInfo(m_filePath);
for (int i = 0; i < ProcessedFiles.Length; i++)
{
if (ProcessedFiles[i].Split(';')[0] == m_filePath && ProcessedFiles[i].Split().Length > 1)
{
//update existing record for that file
ProcessedFiles[i] = m_filePath + ";" + FileInfo.LastWriteTime.ToString();// + ";" + BuildMd5Checksum(m_filePath);
//rewrite file
WriteAllLines(ProcessedFilesLog, ProcessedFiles, true);
return;
}
}
//add new record to end of file
Support.WriteOneLine(ProcessedFilesLog, m_filePath + ";" + FileInfo.LastWriteTime.ToString(), true);// + ";" + BuildMd5Checksum(m_filePath),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.
}
}
/// <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);
}
}
/// <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);
}
