Exemple #1
0
        public static List<FetchingEnergy> fetchAVGBar(List<int> timeSt, string deviceID)
        {
            List<FetchingEnergy> listObj = new List<FetchingEnergy>();
            try
            {
                using (DbConnection conn = provider.CreateConnection())
                {
                    conn.ConnectionString = connString;

                    using (DbCommand cmd = conn.CreateCommand())
                    {
                        int window = 300;

                        string sqlQuery = "(SELECT TimeStamp, FwdHr, W, meterID FROM Meter_Data WHERE TimeStamp Between " + (timeSt[0]).ToString() + " AND " + (timeSt[0] + window).ToString() + " AND DeviceID = @deviceID GROUP BY meterID)";

                        for (int i = 1; i < timeSt.Count; i++)
                        {
                            sqlQuery = sqlQuery + " UNION (SELECT TimeStamp, FwdHr, W, meterID FROM Meter_Data WHERE TimeStamp Between " + (timeSt[i]).ToString() + " AND " + (timeSt[i] + window).ToString() + " AND DeviceID = @deviceID GROUP BY meterID)";

                        }

                        if (parmPrefix != "@")
                        {
                            sqlQuery = sqlQuery.Replace("@", parmPrefix);
                        }
                        cmd.CommandText = sqlQuery;
                        cmd.CommandType = CommandType.Text;

                        DbParameter dpDeviceID = provider.CreateParameter();
                        dpDeviceID.ParameterName = parmPrefix + "deviceID";
                        dpDeviceID.Value = deviceID;
                        cmd.Parameters.Add(dpDeviceID);

                        conn.Open();

                        using (DbDataReader rdr = cmd.ExecuteReader())
                        {

                            if (rdr.HasRows)
                            {
                                while (rdr.Read())
                                {
                                    FetchingEnergy obj = new FetchingEnergy();

                                    if (!rdr.IsDBNull(0))
                                    {
                                        obj.TimeStamp = rdr.GetInt32(0);
                                    }
                                    if (!rdr.IsDBNull(1))
                                    {
                                        obj.FwdHr = rdr.GetFloat(1);
                                    }
                                    if (!rdr.IsDBNull(2))
                                    {
                                        obj.W = rdr.GetFloat(2);
                                    }
                                    if (!rdr.IsDBNull(3))
                                    {
                                        obj.MeterId = rdr.GetInt32(3);
                                    }
                                    obj.DeviceId = deviceID;

                                    listObj.Add(obj);
                                }

                            }
                            else
                            {
                                return null;
                            }
                        }

                    }
                    conn.Close();
                }
                return listObj;
            }
            catch (Exception e)
            {
                return null;
            }
        }
Exemple #2
0
        public static List<FetchingEnergy> fetchEnergyLining(int fromTime, int toTime, int meterID, string deviceID, int ct)
        {
            List<FetchingEnergy> listObj = new List<FetchingEnergy>();
            try
            {
                using (DbConnection conn = provider.CreateConnection())
                {
                    conn.ConnectionString = connString;

                    using (DbCommand cmd = conn.CreateCommand())
                    {
                        int window = 120;
                        ct = 30;
                        int sample = (toTime-fromTime) / ct;
                        List<int> timeSt = new List<int>();
                        for (int i = fromTime; i <= toTime; i = i + sample)
                        {
                            timeSt.Add(i);
                        }

                        string sqlQuery = "(SELECT TimeStamp, FwdHr, W FROM Meter_Data WHERE TimeStamp Between " + (timeSt[0]).ToString() + " AND " + (timeSt[0] + window).ToString() + " AND MeterID = @meterID AND DeviceID = @deviceID ORDER BY TimeStamp LIMIT 1)";

                        for (int i = 1; i < timeSt.Count; i++)
                        {
                            sqlQuery = sqlQuery + " UNION (SELECT TimeStamp, FwdHr, W FROM Meter_Data WHERE TimeStamp Between " + (timeSt[i]).ToString() + " AND " + (timeSt[i] + window).ToString() + " AND MeterID = @meterID AND DeviceID = @deviceID ORDER BY TimeStamp LIMIT 1)";

                        }

                        if (parmPrefix != "@")
                        {
                            sqlQuery = sqlQuery.Replace("@", parmPrefix);
                        }
                        cmd.CommandText = sqlQuery;
                        cmd.CommandType = CommandType.Text;

                        DbParameter dpMeterID = provider.CreateParameter();
                        dpMeterID.ParameterName = parmPrefix + "meterID";
                        dpMeterID.Value = meterID;
                        cmd.Parameters.Add(dpMeterID);

                        DbParameter dpDeviceID = provider.CreateParameter();
                        dpDeviceID.ParameterName = parmPrefix + "deviceID";
                        dpDeviceID.Value = deviceID;
                        cmd.Parameters.Add(dpDeviceID);

                        conn.Open();

                        using (DbDataReader rdr = cmd.ExecuteReader())
                        {

                            if (rdr.HasRows)
                            {
                                while (rdr.Read())
                                {
                                    FetchingEnergy obj = new FetchingEnergy();

                                    if (!rdr.IsDBNull(0))
                                    {
                                        obj.TimeStamp = rdr.GetInt32(0);
                                    }
                                    if (!rdr.IsDBNull(1))
                                    {
                                        obj.FwdHr = rdr.GetFloat(1);
                                    }
                                    if (!rdr.IsDBNull(2))
                                    {
                                        obj.W = rdr.GetFloat(2);
                                    }
                                    obj.DeviceId = deviceID;
                                    obj.MeterId = meterID;
                                    listObj.Add(obj);
                                }

                            }
                            else
                            {
                                return null;
                            }
                        }

                    }
                    conn.Close();
                }
                return listObj;
            }
            catch (Exception e)
            {
                return null;
            }
        }
Exemple #3
0
        public static List<FetchingEnergy> fetchEnergyALL(int fromTime, int toTime, int meterID, string deviceID)
        {
            List<FetchingEnergy> listObj = new List<FetchingEnergy>();
            try
            {
                using (DbConnection conn = provider.CreateConnection())
                {
                    conn.ConnectionString = connString;

                    using (DbCommand cmd = conn.CreateCommand())
                    {

                         string sqlQuery = "(SELECT TimeStamp, FwdHr, W FROM Meter_Data WHERE TimeStamp Between " + fromTime.ToString() + " AND " + toTime.ToString() + " AND MeterID = @meterID AND DeviceID = @deviceID ORDER BY TimeStamp ASC)";

                        if (parmPrefix != "@")
                        {
                            sqlQuery = sqlQuery.Replace("@", parmPrefix);
                        }
                        cmd.CommandText = sqlQuery;
                        cmd.CommandType = CommandType.Text;

                        DbParameter dpMeterID = provider.CreateParameter();
                        dpMeterID.ParameterName = parmPrefix + "meterID";
                        dpMeterID.Value = meterID;
                        cmd.Parameters.Add(dpMeterID);

                        DbParameter dpDeviceID = provider.CreateParameter();
                        dpDeviceID.ParameterName = parmPrefix + "deviceID";
                        dpDeviceID.Value = deviceID;
                        cmd.Parameters.Add(dpDeviceID);

                        conn.Open();

                        using (DbDataReader rdr = cmd.ExecuteReader())
                        {

                            if (rdr.HasRows)
                            {
                                while (rdr.Read())
                                {
                                    FetchingEnergy obj = new FetchingEnergy();

                                    if (!rdr.IsDBNull(0))
                                    {
                                        obj.TimeStamp = rdr.GetInt32(0);
                                    }
                                    if (!rdr.IsDBNull(1))
                                    {
                                        obj.FwdHr = rdr.GetFloat(1);
                                    }
                                    if (!rdr.IsDBNull(2))
                                    {
                                        obj.W = rdr.GetFloat(2);
                                    }
                                    obj.DeviceId = deviceID;
                                    obj.MeterId = meterID;
                                    listObj.Add(obj);
                                }

                            }
                            else
                            {
                                return null;
                            }
                        }

                    }
                    conn.Close();
                }
                return listObj;
            }
            catch (Exception e)
            {
                return null;
            }
        }
Exemple #4
0
        public static List<FetchingEnergy> fetchEnergyLine(int fromTime, int toTime, int meterID, string deviceID, int ct)
        {
            List<FetchingEnergy> listObj = new List<FetchingEnergy>();
            try
            {
                using (MySqlConnection conn = new MySqlConnection())
                {
                    conn.ConnectionString = connString;

                    using (MySqlCommand cmd = conn.CreateCommand())
                    {

                        cmd.CommandText = "FetchEnergyLine";
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.AddWithValue("From_Time", fromTime);
                        cmd.Parameters["From_Time"].Direction = ParameterDirection.Input;
                        cmd.Parameters.AddWithValue("To_Time", toTime);
                        cmd.Parameters["To_Time"].Direction = ParameterDirection.Input;
                        cmd.Parameters.AddWithValue("Meter_Id", meterID);
                        cmd.Parameters["Meter_Id"].Direction = ParameterDirection.Input;
                        cmd.Parameters.AddWithValue("Device_Id", deviceID);
                        cmd.Parameters["Device_Id"].Direction = ParameterDirection.Input;
                        cmd.Parameters.AddWithValue("ct", ct);
                        cmd.Parameters["ct"].Direction = ParameterDirection.Input;

                        conn.Open();

                        using (MySqlDataReader rdr = cmd.ExecuteReader())
                        {

                            if (rdr.HasRows)
                            {
                                while (rdr.Read())
                                {
                                    FetchingEnergy obj = new FetchingEnergy();

                                    if (!rdr.IsDBNull(0))
                                    {
                                        obj.TimeStamp = rdr.GetInt32(0);
                                    }
                                    if (!rdr.IsDBNull(1))
                                    {
                                        obj.FwdHr = rdr.GetFloat(1);
                                    }
                                    if (!rdr.IsDBNull(2))
                                    {
                                        obj.W = rdr.GetFloat(2);
                                    }
                                    obj.DeviceId = deviceID;
                                    obj.MeterId = meterID;
                                    listObj.Add(obj);
                                }

                            }
                            else
                            {
                                return null;
                            }
                        }

                    }
                    conn.Close();
                }
                return listObj;
            }
            catch (Exception e)
            {
                return null;
            }
        }
Exemple #5
0
        public static List<FetchingEnergy> fetchAVGDashDayEnergy(int timeSt, string deviceID , int sec)
        {
            List<FetchingEnergy> listObj = new List<FetchingEnergy>();
            try
            {
                using (DbConnection conn = provider.CreateConnection())
                {
                    conn.ConnectionString = connString;

                    using (DbCommand cmd = conn.CreateCommand())
                    {
                        int window = sec;

                        string sqlQuery = "(SELECT MAX(FwdHr), MIN(FwdHr) FROM Meter_Data WHERE TimeStamp Between " + (timeSt).ToString() + " AND " + (timeSt + window).ToString() + " AND DeviceID = @deviceID GROUP BY meterID)";

                        if (parmPrefix != "@")
                        {
                            sqlQuery = sqlQuery.Replace("@", parmPrefix);
                        }
                        cmd.CommandText = sqlQuery;
                        cmd.CommandType = CommandType.Text;

                        DbParameter dpDeviceID = provider.CreateParameter();
                        dpDeviceID.ParameterName = parmPrefix + "deviceID";
                        dpDeviceID.Value = deviceID;
                        cmd.Parameters.Add(dpDeviceID);

                        conn.Open();

                        using (DbDataReader rdr = cmd.ExecuteReader())
                        {

                            if (rdr.HasRows)
                            {
                                while (rdr.Read())
                                {
                                    FetchingEnergy obj = new FetchingEnergy();

                                    if (!rdr.IsDBNull(0))
                                    {
                                        obj.fwdHrFinal = rdr.GetFloat(0);
                                    }
                                    if (!rdr.IsDBNull(1))
                                    {
                                        obj.FwdHr = rdr.GetFloat(1);
                                    }

                                    listObj.Add(obj);
                                }

                            }
                            else
                            {
                                return null;
                            }
                        }

                    }
                    conn.Close();
                }
                return listObj;
            }
            catch (Exception e)
            {
                return null;
            }
        }