/// <summary>
/// Fetch IP and PORT number from database by DEVICE_ID
/// </summary>
/// <param name="delim"> This char will be used for data separation </param>
/// <returns></returns>
public static string FindIPAndPortFromId(DataClass dataPtr, char delim)
{
string commmand = "SELECT IP,PORT FROM DEVICE WHERE ID=" + dataPtr.deviceID.ToString();
string result = DatabaseControllerClass.FetchValuesFromDatabase(DatabaseSettingsClass.ReadConnectionString(), commmand, delim);
return(result);
}
private void buttonGetLocations_Click(object sender, EventArgs e)
{
string res = ""; // it will hold data from database
// "SELECT ID,NAME,MAC FROM device"
// SELECT READ_TIME FROM DATA_HISTORY WHERE LOCATION_ID=30 AND ROWNUM <= 50;
// SELECT IPAL FROM DATA_HISTORY WHERE LOCATION_ID=65 AND ROWNUM <= 50 AND READ_TIME >= TO_DATE('2015/11/01 8:15', 'YYYY/MM/DD HH:MI') AND READ_TIME <= TO_DATE('2015/11/01 8:16', 'YYYY/MM/DD HH:MI');
// SELECT EPTC,EQTC FROM DATA_HISTORY WHERE LOCATION_ID=37 AND READ_TIME >= TO_DATE('2017/05/1 0:0', 'YYYY/MM/DD HH24:MI') AND READ_TIME <= TO_DATE('2017/05/1 0:1', 'YYYY/MM/DD HH24:MI') AND ROWNUM <= 1
// SELECT DEVICE_ID, DESCRIPTION FROM LOCATION ORDER BY DEVICE_ID ASC;
char delim = '|';
try
{
res = DatabaseControllerClass.FetchValuesFromDatabase(DatabaseSettingsClass.ReadConnectionString(), "SELECT DEVICE_ID, NAME, DESCRIPTION FROM LOCATION ORDER BY NAME ASC", delim);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
dataGridView1.Rows.Clear();
// Filter res first from bad values (new lines)
res = res.Replace("\r\n", "");
// Split monolithic string to data lines
//string[] dataLines = res.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
string[] dataLines = res.Split(delim);
// We need to filter dataLines from bad inputs... APARENTLY
StringBuilder sb = new StringBuilder();
foreach (var item in dataLines)
{
if (item.Equals(""))
{
continue;
}
sb.Append(item + "|");
}
dataLines = sb.ToString().Split('|'); // dataLines should be filtered now from empty lines
string[] rowInfo;
// Now fill data grid based on results
for (int i = 0; i < dataLines.Length - 1; i++)
{
rowInfo = dataLines[i].Split(';');
dataGridView1.Rows.Add(false, rowInfo[0], rowInfo[1], rowInfo[2], null);
}
}
private void buttonGetLocationsDataIntegrity_Click(object sender, EventArgs e)
{
char delim = '|';
string res = ""; // it will hold data from database
try
{
res = DatabaseControllerClass.FetchValuesFromDatabase(DatabaseSettingsClass.ReadConnectionString(), "SELECT DEVICE_ID, NAME, DESCRIPTION FROM LOCATION ORDER BY NAME ASC", delim);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
dataGridView2.Rows.Clear();
// Split monolithic string to data lines
string[] dataLines = res.Split(delim);
// We need to filter dataLines from bad inputs... APARENTLY
StringBuilder sb = new StringBuilder();
foreach (var item in dataLines)
{
if (item.Equals(""))
{
continue;
}
sb.Append(item + "|");
}
dataLines = sb.ToString().Split('|'); // dataLines should be filtered now from empty lines
allData = new DataClass[dataLines.Length]; // Every row will have data class for storing data from database
string[] rowInfo;
// Now fill data grid based on results
for (int i = 0; i < dataLines.Length - 1; i++)
{
rowInfo = dataLines[i].Split(';');
dataGridView2.Rows.Add(false, rowInfo[0], rowInfo[1], rowInfo[2], null);
}
}
/// <summary>
/// Remove duplicates for all 15min periods, duplicates are removed if all measurment values are same
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void removeDuplicatesToolStripMenuItem_Click(object sender, EventArgs e)
{
#region OLD WAY
//List<string> commands = new List<string>(); // List of database commands
//// Loop thru every 15min point, and generate remove database queries for true duplicate data
//foreach (Data15MinClass d15 in dataPtr.data)
//{
// if (d15.numberOfPoints < 2)
// {
// continue; // If point have less than 2 values ignore
// }
// var uniqueValues = new List<FloatValues>(); // Create new list which will hold unique values
// var all15minValues = d15.GetAllValues(); // Some 15min point can have multiple values, duplicates
// // First value is always unique
// uniqueValues.Add(all15minValues[0]);
// // Generate remove command for each duplicated value, leave values that are different in float values
// for (int i = 1; i < d15.GetAllValues().Count; i++) // Skip first
// {
// bool equalFlag = false; // This will be set to true if any of data point is true duplicate
// foreach (var item2 in uniqueValues)
// {
// if (FloatHelper.CompareDuplicateValues(item2, all15minValues[i])) // Check if they are equal
// {
// equalFlag = true;
// // Generate command for row removal from database and add it to list of commands
// commands.Add(GenerateDatabaseRemoveCommand(all15minValues[i]));
// }
// }
// if (!equalFlag)
// {
// // This garantiues unique value
// uniqueValues.Add(all15minValues[i]);
// }
// equalFlag = false;
// }
//}
#endregion old way
List <string> commands = CreateDBRemoveCommands();
// Make dialog box, for connfirming deletion
DialogResult dialogResult = MessageBox.Show("Are you sure you want to delete: " + commands.Count + " items from database?", "Warning", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.No)
{
return;
}
// Yes was selected
Action action = () =>
{
//Console.WriteLine("Starting request");
foreach (var item in commands)
{
FormCustomConsole.WriteLine("Sending command: " + item);
try
{
#warning Deleteing items from database can be configured here
DatabaseControllerClass.SendCommandToDatabase(DatabaseSettingsClass.ReadConnectionString(), item);
FormCustomConsole.WriteLine("Successfully executed command:" + item);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
};
ICancelableForm cf = new FormWaitMarquee(this);
HelperInterruptibleWorker worker = new HelperInterruptibleWorker(this, cf);
worker.ExecuteTask(action);
}
// Update selected rows in slow way while keeping results on other rows
private void UpdateValuesFromDB(ICancelableForm fw)
{
string oracleDateStart;
string oracleDateStartOffset;
string oracleDateEnd;
string oracleDateEndOffset;
string dateStringTimestamp = "";
string querryStart;
string querryEnd;
string resStart = "";
string resEnd = "";
bool startValid = false;
bool endValid = false;
// If row is enabled, process it
foreach (DataGridViewRow item in dataGridView1.Rows)
{
if (Convert.ToBoolean(item.Cells[0].Value) == true)
{
// Selected element is enabled
// First intialize time
TimeFormatterClass tf = new TimeFormatterClass();
do
{
// Form querry string for time
try
{
dateStringTimestamp = GetStartYear() + "/" + GetStartMonth() + "/" + tf.GetDay() + " " + tf.GetHour() + ":" + tf.GetMin() + "-" + GetEndYear() + "/" + GetEndMonth() + "/" + tf.GetDay() + " " + tf.GetHour() + ":" + tf.GetMin();
oracleDateStart = HelperClass.ConvertToOracleTime(GetStartYear(), GetStartMonth(), tf.GetDay(), tf.GetHour(), tf.GetMin());
oracleDateStartOffset = HelperClass.ConvertToOracleTime(GetStartYear(), GetStartMonth(), tf.GetDay(), tf.GetHour(), (Convert.ToInt32(tf.GetMin()) + 1).ToString());
oracleDateEnd = HelperClass.ConvertToOracleTime(GetEndYear(), GetEndMonth(), tf.GetDay(), tf.GetHour(), tf.GetMin());
oracleDateEndOffset = HelperClass.ConvertToOracleTime(GetEndYear(), GetEndMonth(), tf.GetDay(), tf.GetHour(), (Convert.ToInt32(tf.GetMin()) + 1).ToString());
}
catch (Exception)
{
break; // This means all time periods are tried, abort this
}
querryStart = "SELECT EPTC,EQTC FROM DATA_HISTORY WHERE DEVICE_ID=" + item.Cells[1].Value + " AND READ_TIME >= " + oracleDateStart + " AND READ_TIME <= " + oracleDateStartOffset + "AND ROWNUM <= 1";
querryEnd = "SELECT EPTC,EQTC FROM DATA_HISTORY WHERE DEVICE_ID=" + item.Cells[1].Value + " AND READ_TIME >= " + oracleDateEnd + " AND READ_TIME <= " + oracleDateEndOffset + "AND ROWNUM <= 1";
char delim = '|';
try
{
resStart = DatabaseControllerClass.FetchValuesFromDatabase(DatabaseSettingsClass.ReadConnectionString(), querryStart, delim);
resEnd = DatabaseControllerClass.FetchValuesFromDatabase(DatabaseSettingsClass.ReadConnectionString(), querryEnd, delim);
}
catch (Exception)
{
Task.Run(() => MessageBox.Show("Timeout for device ID:" + item.Cells[1].Value));
return;
}
// Now we should have a results, but we must check for validity
startValid = HelperClass.CheckDataValidity(resStart);
endValid = HelperClass.CheckDataValidity(resEnd);
if (!(startValid && endValid)) // If one of each is not valid calculate next time
{
tf.CalculateNextValues();
if (Convert.ToInt32(tf.GetDay()) == ConfigClass.other.daysToCheck) // If we reached maximum days
{
break;
}
}
} while (!(startValid && endValid)); // Will run until both values are valid or unitl end is reached
// Update percentage bar and check if aborted
if (fw.AlreadyClosed)
{
return;
}
fw.ProgresStep(); // Update progress bar
// Result have active and reactive component
if (startValid && endValid == true)
{
// We have both values, lets process them
bool warning; // will warn if negative active power
string valueRes = FormatEnergyValues(resStart, resEnd, out warning);
string[] temp = valueRes.Split('/');
item.Cells[4].Value = temp[0].Trim();
item.Cells[5].Value = temp[1].Trim();
string[] tempTime = dateStringTimestamp.Split('-');
item.Cells[6].Value = tempTime[0].Trim();
item.Cells[7].Value = tempTime[1].Trim();
if (warning == true)
{
item.Cells[4].Style.BackColor = Color.Yellow;
item.Cells[5].Style.BackColor = Color.Yellow;
item.Cells[6].Style.BackColor = Color.Yellow;
item.Cells[7].Style.BackColor = Color.Yellow;
}
else
{
item.Cells[4].Style.BackColor = Color.White;
item.Cells[5].Style.BackColor = Color.White;
item.Cells[6].Style.BackColor = Color.White;
item.Cells[7].Style.BackColor = Color.White;
}
}
else
{
item.Cells[4].Value = VALUE_MISSING;
item.Cells[4].Style.BackColor = Color.Red;
item.Cells[5].Value = VALUE_MISSING;
item.Cells[5].Style.BackColor = Color.Red;
item.Cells[6].Value = VALUE_MISSING;
item.Cells[6].Style.BackColor = Color.Red;
item.Cells[7].Value = VALUE_MISSING;
item.Cells[7].Style.BackColor = Color.Red;
}
}
else // Cell is not selected, erase value
{
// Nothing i just want to update
}
}
}
// TODO: Make sorting disabled while updating
public void ReplaceDataPoint(int deviceID)
{
DataGridViewRow dataGridItem = null;
int dataGridSelectedIndex = -1;
// Find row with matching ID
foreach (DataGridViewRow item in dataGridView2.Rows)
{
dataGridSelectedIndex = Convert.ToInt32(item.Cells[1].Value);
if (dataGridSelectedIndex == deviceID)
{
dataGridItem = item;
break;
}
}
if (dataGridItem == null)
{
throw new Exception("Item in DataGrid with that deviceID doesn't exist");
}
// Find matching ID in allData
var tuple = GetInstanceFromAllDataByDeviceID(deviceID);
if (tuple == null)
{
throw new Exception("Data item in allData with that deviceID doesn't exist");
}
// Extract information from data object
DataClass data = tuple.Item1;
DateTime dateStart = data.startDate;
DateTime dateEnd = data.endDate;
string oracleDateStart = HelperClass.ConvertToOracleTime(data.startDate);
string oracleDateEnd = HelperClass.ConvertToOracleTime(data.endDate);
// Generate oracle time selection string
string result = "";
//int currentRow = _index;
string querryCommand = "SELECT VALID,IPAA,IPBA,IPCA,VABA,VBCA,VCAA, READ_TIME, ID FROM DATA_HISTORY WHERE DEVICE_ID=" + dataGridSelectedIndex + " AND READ_TIME >= " + oracleDateStart + " AND READ_TIME < " + oracleDateEnd + "AND ROWNUM <= " + ConfigClass.MAX_ROWS_GET.ToString();
char delim = '|';
try
{
result = DatabaseControllerClass.FetchValuesFromDatabase(DatabaseSettingsClass.ReadConnectionString(), querryCommand, delim);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
finally
{
//fw.Close();
}
// Instantiate dataclass for current row
//data = new DataClass(dateEnd.Subtract(dateStart).Days, dataGridItem.Index, dateStart, dateEnd);
var newData = new DataClass(dateEnd.Subtract(dateStart).Days, tuple.Item2, dateStart, dateEnd);
// Fill it with values from database
newData.FillDataRow(result, dataGridSelectedIndex, delim);
// Process data row for: null values, zero values, duplicate values and generaly some way of corrupted data
newData.ProcessData();
newData.RunAnalysis(); // This will calculate all needed info
string tempSummary = newData.GenerateSummary();
string[] summary = tempSummary.Split(delim);
// Fill cells
dataGridItem.Cells[4].Value = summary[0]; // valid
dataGridItem.Cells[5].Value = summary[1]; // flag status
dataGridItem.Cells[6].Value = dateStart.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
dataGridItem.Cells[7].Value = dateEnd.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
dataGridItem.Cells[8].Value = "Ready";
dataGridItem.Cells[8].Style.BackColor = Color.Lavender;
// Finaly replace data instance in allData array
ReplaceDataPoint(newData);
}
private void ProcessDataIntegrity(ICancelableForm fw)
{
string sysFormat = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
string sysUIFormat = CultureInfo.CurrentUICulture.DateTimeFormat.ShortDatePattern;
FormCustomConsole.WriteLine("CurrentCulture: " + sysFormat);
FormCustomConsole.WriteLine("CurrentUICulture: " + sysUIFormat);
DateTime dateStart = GetDateFiltered(dateTimePickerStartDataIntegrity);
DateTime dateEnd = GetDateFiltered(dateTimePickerEndDataIntegrity);
// Check if day selection is valid
TimeSpan dayDiff = dateEnd.Subtract(dateStart); // It will hold days difference
if (dayDiff.Days <= 0)
{
MessageBox.Show("Select valid time!");
return;
}
string oracleDateStart;
string oracleDateEnd;
string querryCommand;
string result = "";
int currentRow = -1;
// If row is enabled, process it
foreach (DataGridViewRow item in dataGridView2.Rows)
{
currentRow++; // Start from -1 so it is 0 at first iteration
if (Convert.ToBoolean(item.Cells[0].Value) == true)
{
// Selected element is enabled
// Create oracle time selection string
oracleDateStart = HelperClass.ConvertToOracleTime(dateStart);
oracleDateEnd = HelperClass.ConvertToOracleTime(dateEnd);
// Create command
querryCommand = "SELECT VALID,IPAA,IPBA,IPCA,VABA,VBCA,VCAA, READ_TIME, ID FROM DATA_HISTORY WHERE DEVICE_ID=" + item.Cells[1].Value + " AND READ_TIME >= " + oracleDateStart + " AND READ_TIME < " + oracleDateEnd + "AND ROWNUM <= " + ConfigClass.MAX_ROWS_GET.ToString();
char delim = '|';
try
{
result = DatabaseControllerClass.FetchValuesFromDatabase(DatabaseSettingsClass.ReadConnectionString(), querryCommand, delim);
}
catch (Exception)
{
Task.Run(() => MessageBox.Show("Timeout for device ID:" + item.Cells[1].Value));
return;
}
// Instantiate dataclass for current row
allData[currentRow] = new DataClass(dayDiff.Days, currentRow, dateStart, dateEnd);
// Fill it with values from database
allData[currentRow].FillDataRow(result, Convert.ToInt32(item.Cells[1].Value), delim);
// Process data row for: null values, zero values, duplicate values and generaly some way of corrupted data
allData[currentRow].ProcessData();
allData[currentRow].RunAnalysis(); // This will calculate all needed info
string tempSummary = allData[currentRow].GenerateSummary();
string[] summary = tempSummary.Split(delim);
// Fill cells
item.Cells[4].Value = summary[0]; // valid
item.Cells[5].Value = summary[1]; // flag status
item.Cells[6].Value = dateStart.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
item.Cells[7].Value = dateEnd.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
item.Cells[8].Value = "Ready";
item.Cells[8].Style.BackColor = Color.Lavender;
// Update percentage bar and check if aborted
fw.ProgresStep(); // Update progress bar
if (fw.AlreadyClosed)
{
return;
}
}
else // Cell is not selected
{
}
}
}
