/// <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);
        }
Ejemplo n.º 2
0
        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);
            }
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 5
0
        // 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
                }
            }
        }
Ejemplo n.º 6
0
        // 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);
        }
Ejemplo n.º 7
0
        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
                {
                }
            }
        }