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;
}
}
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;
}
}
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;
}
}
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;
}
}
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;
}
}