public void Execute(Boolean ReplaceAndExecute, string tagevent, TagHandler Tags)
{
string finalWhere;
string value;
string cmdSend;
Logging log = new Logging("[" + tagevent + "]" + Command + ":" + ColumnName + " ");
SQLHandler sqlGenerator = new SQLHandler();
string cmd = Command.ToLower();
if (cmd.Substring(0, 6) == "insert" || cmd.Substring(0, 6) == "update" || cmd.Substring(0, 6) == "select")
{
if (Where != "")
{
finalWhere = Tags.SolveWhere(Where);
}
else
{
finalWhere = Where;
}
sqlGenerator.InitCommand(cmd.ToString(), ColumnName.ToString(), finalWhere);
if (Columns != null)
{
for (int index = 0; index < Columns.Count; index++)
{
ColumnsCommand column = Columns[index];
log.Command += column.TagAddress + column.name + column.KeepInTag + "=" + column.Formula + "\t";
if (column.Enabled)
{
if (ReplaceAndExecute)
{
string tmpExp = column.Formula;
value = Tags.SolveFormula(tmpExp);
if (column.Format != "")
{
value = String.Format(column.Formula, column.Format);
}
}
else
{
value = column.Formula;
}
column.Value = value.ToString().Trim();
sqlGenerator.SetColumn(column.name, value);
if (column.KeepInTag != "")
{
Tags.UpdateTagsValue(column.KeepInTag, Convert.ToInt32(value));
IntData.SaveRecordsetFlag = true;
}
}
Columns[index] = column;
}
if (ReplaceAndExecute)
{
cmdSend = sqlGenerator.Command;
if (Command.ToString().ToUpper() == "UPDATE")
{
if (AutoInsert(ColumnName.ToString()))
{
cmdSend += IntData.AUTO_INSERT_STR;
}
}
if (Command.ToString().ToUpper() != "SELECT" && ColumnName.ToString() != "" && ColumnName.IndexOf("_Internal") == -1)
{
MQHandler queue = new MQHandler();
queue.SendMsg(cmdSend);
}
if (Command.ToString().ToUpper() == "SELECT")
{
WriteOPC(cmdSend, Tags);
}
}
}
}
log.Success();
}
/// <summary>
/// Based on a specified PredictedObserved item, searches for the corresponding sqlite db file and extracts the
/// datatable information for this PredictedObserved item.
/// </summary>
/// <param name="predictedObservedName">Name of the PredictedObserved table.</param>
/// <param name="databasePath">Path to the .db file.</param>
private static DataTable GetPredictedObservedDataTable(string predictedObservedName, string databasePath)
{
DataTable POdata = new DataTable(predictedObservedName);
try
{
if (File.Exists(databasePath))
{
string dbFileName = Path.GetFileName(databasePath);
using (SQLiteConnection con = new SQLiteConnection("Data Source=" + databasePath))
{
con.Open();
string selectSQL = "SELECT * FROM " + predictedObservedName;
SQLiteCommand cmd = new SQLiteCommand(selectSQL, con);
try
{
SQLiteDataReader rdr = cmd.ExecuteReader();
if (rdr != null)
{
POdata.Load(rdr);
WriteToLogFile(string.Format(" There are {0} Predicted Observed records in database {1}, table {2}.", POdata.Rows.Count, dbFileName, predictedObservedName));
}
}
catch (Exception ex)
{
if (ex.Message.ToString().IndexOf("no such table") > 0)
{
WriteToLogFile(string.Format(" For Database {0}, Table {1} does not exist: {2}", dbFileName, predictedObservedName, ex.ToString()));
}
else
{
WriteToLogFile(string.Format(" ERROR reading database {0}: {1}!", dbFileName, ex.ToString()));
}
}
}
}
else
{
throw new Exception(string.Format("ERROR Database file {0} does not exist", databasePath));
}
string ColumnName, ObservedName, PredictedName;
bool removeCols = false, colRemoved = false;
int cIndex;
//modLMC - 31-Jan-2018 - (as instructed by Dean) if the Predicted column is defined as String, then remove both Predicted & Observed
for (int i = POdata.Columns.Count - 1; i >= 0; i--)
{
ColumnName = POdata.Columns[i].ColumnName.Trim();
//modLMC - 20/02/2018 - as per phone conversation with Dean, remove any columns with CheckpointID
if (ColumnName.IndexOf("CheckpointID") >= 0)
{
//Remove any columns called CheckpointID
POdata.Columns.RemoveAt(i);
//WriteToLogFile(String.Format(" NOTE: {0}.{1} was dropped.");
//i--;
}
else if (ColumnName.StartsWith("Predicted"))
{
//if datatype is not numeric need to remove it, and its corresponding observed column
colRemoved = false;
//check if the "Observed" Column exists, if it doesn't, then delete the Predicted
ObservedName = ColumnName.Replace("Predicted", "Observed");
try
{
cIndex = POdata.Columns[ObservedName].Ordinal;
}
catch (System.NullReferenceException)
{
POdata.Columns.RemoveAt(i);
WriteToLogFile(String.Format(" NOTE: {0}.{1} was dropped the Observed column {2} does not exist.", POdata.TableName, ColumnName, ObservedName));
colRemoved = true;
//i--;
}
if (colRemoved == false)
{
removeCols = false;
if (POdata.Columns[i].DataType == typeof(DateTime))
{
removeCols = true;
}
if (POdata.Columns[i].DataType == typeof(System.String))
{
removeCols = true;
}
if (removeCols == true)
{
WriteToLogFile(String.Format(" NOTE: {0}.{1} dropped as not in correct Format, was of Type {2} is not the correct; it should be a numeric column", POdata.TableName, ColumnName, POdata.Columns[i].DataType));
POdata.Columns.RemoveAt(i);
ObservedName = ColumnName.Replace("Predicted", "Observed");
try
{
cIndex = POdata.Columns[ObservedName].Ordinal;
if (cIndex > 0)
{
POdata.Columns.RemoveAt(cIndex);
WriteToLogFile(String.Format(" NOTE: {0}.{1} was also dropped as {2} was not defined as a numeric column", POdata.TableName, ObservedName, ColumnName));
if (i >= POdata.Columns.Count)
{
//make sure we don't go out of bounds with the columns
i = POdata.Columns.Count;
}
}
}
catch (Exception)
{
}
}
}
}
else if (ColumnName.StartsWith("Observed"))
{
//check if the "Predicted" Column exists, if it doesn't, then delete the Predicted
PredictedName = ColumnName.Replace("Observed", "Predicted");
try
{
cIndex = POdata.Columns[PredictedName].Ordinal;
}
catch (System.NullReferenceException)
{
POdata.Columns.RemoveAt(i);
WriteToLogFile(String.Format(" NOTE: {0}.{1} was dropped the Predicted column {2} does not exist.", POdata.TableName, ColumnName, PredictedName));
//i--;
}
}
}
POdata.AcceptChanges();
//need to ensure that we can convert test/string/char columns to real for all Predicted and Observed Columns
//need to work backwards, just in case we need to delete any columns
for (int i = POdata.Columns.Count - 1; i >= 0; i--)
{
ColumnName = POdata.Columns[i].ColumnName.Trim();
if (ColumnName.StartsWith("Observed") || ColumnName.StartsWith("Predicted"))
{
if (POdata.Columns[i].DataType == typeof(DateTime))
{
POdata.Columns.RemoveAt(i);
}
else if ((POdata.Columns[i].DataType != typeof(System.Double)) && (POdata.Columns[i].DataType != typeof(System.Int64)))
{
try
{
//Update the log file to report incorrect data types
WriteToLogFile(String.Format(" NOTE: {0}.{1} Format Type {2} is not the correct; it should be a numeric column", POdata.TableName, ColumnName, POdata.Columns[i].DataType));
////rename the original
string origCol = "orig" + ColumnName;
POdata.Columns[i].ColumnName = origCol;
//create a new column, with the correct type
POdata.Columns.Add(ColumnName, typeof(System.Double));
for (int ri = 0; ri < POdata.Rows.Count; ri++)
{
double value;
if (double.TryParse(POdata.Rows[ri][i].ToString(), out value))
{
POdata.Rows[ri][ColumnName] = value;
}
else
{
POdata.Rows[ri][ColumnName] = DBNull.Value;
}
}
//now remove the original column
POdata.Columns.RemoveAt(i);
}
catch (Exception ex)
{
WriteToLogFile(String.Format(" ERROR: Unable to convert {0}.{1} to double: {2}", POdata.TableName, ColumnName, ex.ToString()));
}
}
}
}
return(POdata);
}
catch (Exception ex)
{
WriteToLogFile(" ERROR: Unable to access Data: " + ex.ToString());
return(POdata);
}
}
