public void GetChannelDataNet(string siteId, string siteAliasName, string loc, string loggerId, DateTime start, DateTime end, ref List <MMVModel> data)
{
double?max = null;
double?min = null;
double?avg = null;
string frwdId = null;
string rvrsId = null;
string cnnStr = ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString;
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select [channelId] from t_channel_configurations where [forwardFlow]=1 and [loggerId]='" + loggerId + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd[0] != DBNull.Value)
{
frwdId = (string)rd[0];
}
}
cnn.Close();
}
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select [channelId] from t_channel_configurations where [reverseFlow]=1 and [loggerId]='" + loggerId + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd[0] != DBNull.Value)
{
rvrsId = (string)rd[0];
}
}
cnn.Close();
}
if (string.IsNullOrEmpty(frwdId) && string.IsNullOrEmpty(rvrsId))
{
return;
}
if (!string.IsNullOrEmpty(frwdId) && string.IsNullOrEmpty(rvrsId))
{
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select max([value]) as mx,min([value]) as mn,avg([value]) as vg from t_data_logger_" + frwdId + " where [timestamp] between '"
+ start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd["mx"] != DBNull.Value)
{
max = (double)rd["mx"];
}
if (rd["mn"] != DBNull.Value)
{
min = (double)rd["mn"];
}
if (rd["vg"] != DBNull.Value)
{
avg = (double)rd["vg"];
}
}
cnn.Close();
}
if (max != null)
{
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select [timestamp],[value] from t_data_logger_" + frwdId + " where [value]=" + max + " and [timestamp] between '"
+ start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
MMVModel d = new MMVModel();
if (rd["timestamp"] != DBNull.Value)
{
d.Tmstmp = (DateTime)rd["timestamp"];
}
if (rd["value"] != DBNull.Value)
{
d.Val = (double)rd["value"];
}
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Unit = "m3/h";
d.Type = "net";
d.TypeVal = "Max";
data.Add(d);
}
cnn.Close();
}
}
if (min != null)
{
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select [timestamp],[value] from t_data_logger_" + frwdId + " where [value]=" + min + " and [timestamp] between '"
+ start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
MMVModel d = new MMVModel();
if (rd["timestamp"] != DBNull.Value)
{
d.Tmstmp = (DateTime)rd["timestamp"];
}
if (rd["value"] != DBNull.Value)
{
d.Val = (double)rd["value"];
}
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Unit = "m3/h";
d.Type = "net";
d.TypeVal = "Min";
data.Add(d);
}
cnn.Close();
}
}
if (avg != null)
{
MMVModel d = new MMVModel();
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Unit = "m3/h";
d.Type = "net";
d.TypeVal = "Avg";
d.Val = avg;
data.Add(d);
}
}
if (string.IsNullOrEmpty(frwdId) && !string.IsNullOrEmpty(rvrsId))
{
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select max(-[value]) as mx,min(-[value]) as mn,avg(-[value]) as vg from t_data_logger_" + rvrsId + " where [timestamp] between '"
+ start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd["mx"] != DBNull.Value)
{
max = (double)rd["mx"];
}
if (rd["mn"] != DBNull.Value)
{
min = (double)rd["mn"];
}
if (rd["vg"] != DBNull.Value)
{
avg = (double)rd["vg"];
}
}
cnn.Close();
}
if (max != null)
{
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select [timestamp],(-[value]) as [value] from t_data_logger_" + rvrsId + " where (-[value])=" + max + " and [timestamp] between '"
+ start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
MMVModel d = new MMVModel();
if (rd["timestamp"] != DBNull.Value)
{
d.Tmstmp = (DateTime)rd["timestamp"];
}
if (rd["value"] != DBNull.Value)
{
d.Val = (double)rd["value"];
}
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Unit = "m3/h";
d.Type = "net";
d.TypeVal = "Max";
data.Add(d);
}
cnn.Close();
}
}
if (min != null)
{
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select [timestamp],(-[value]) as [value] from t_data_logger_" + frwdId + " where (-[value])=" + min + " and [timestamp] between '"
+ start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
MMVModel d = new MMVModel();
if (rd["timestamp"] != DBNull.Value)
{
d.Tmstmp = (DateTime)rd["timestamp"];
}
if (rd["value"] != DBNull.Value)
{
d.Val = (double)rd["value"];
}
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Unit = "m3/h";
d.Type = "net";
d.TypeVal = "Min";
data.Add(d);
}
cnn.Close();
}
}
if (avg != null)
{
MMVModel d = new MMVModel();
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Unit = "m3/h";
d.Type = "net";
d.TypeVal = "Avg";
d.Val = avg;
data.Add(d);
}
}
if (!string.IsNullOrEmpty(frwdId) && !string.IsNullOrEmpty(rvrsId))
{
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select max(net) as mx,min(net) as mn,avg(net) as vg from (select t1.[timestamp], t1.[value] as frwd, t2.[value] as rvrs, isnull(t1.[value],0)-isnull(t2.[value],0) as net from t_data_logger_"
+ @frwdId + " t1 left join t_data_logger_" + @rvrsId + " t2 on t1.[timestamp]=t2.[timestamp]) t where [timestamp] between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd["mx"] != DBNull.Value)
{
max = (double)rd["mx"];
}
if (rd["mn"] != DBNull.Value)
{
min = (double)rd["mn"];
}
if (rd["vg"] != DBNull.Value)
{
avg = (double)rd["vg"];
}
}
cnn.Close();
}
if (max != null)
{
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select [timestamp],[net] as [value] from (select t1.[timestamp], t1.[value] as frwd, t2.[value] as rvrs, isnull(t1.[value],0)-isnull(t2.[value],0) as net from t_data_logger_"
+ @frwdId + " t1 left join t_data_logger_" + @rvrsId + " t2 on t1.[timestamp]=t2.[timestamp]) t where [net]=" + max + " and [timestamp] between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
MMVModel d = new MMVModel();
if (rd["timestamp"] != DBNull.Value)
{
d.Tmstmp = (DateTime)rd["timestamp"];
}
if (rd["value"] != DBNull.Value)
{
d.Val = (double)rd["value"];
}
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Unit = "m3/h";
d.Type = "net";
d.TypeVal = "Max";
data.Add(d);
}
cnn.Close();
}
}
if (min != null)
{
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select [timestamp],[net] as [value] from (select t1.[timestamp], t1.[value] as frwd, t2.[value] as rvrs, isnull(t1.[value],0)-isnull(t2.[value],0) as net from t_data_logger_"
+ @frwdId + " t1 left join t_data_logger_" + @rvrsId + " t2 on t1.[timestamp]=t2.[timestamp]) t where [net]=" + min + " and [timestamp] between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
MMVModel d = new MMVModel();
if (rd["timestamp"] != DBNull.Value)
{
d.Tmstmp = (DateTime)rd["timestamp"];
}
if (rd["value"] != DBNull.Value)
{
d.Val = (double)rd["value"];
}
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Unit = "m3/h";
d.Type = "net";
d.TypeVal = "Min";
data.Add(d);
}
cnn.Close();
}
}
if (avg != null)
{
MMVModel d = new MMVModel();
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Unit = "m3/h";
d.Type = "net";
d.TypeVal = "Avg";
d.Val = avg;
data.Add(d);
}
}
}
public void GetChannelData(string siteId, string siteAliasName, string loc, string loggerId, string typeStr, string type, DateTime start, DateTime end, ref List <MMVModel> data)
{
string chnId = "";
string chnName = "";
string unit = "";
string cnnStr = ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString;
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select [channelId],[channelName],[unit] from t_channel_configurations where [loggerId]='" + loggerId + "' and " + typeStr;
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd["channelId"] != DBNull.Value)
{
chnId = (string)rd["channelId"];
}
if (rd["channelName"] != DBNull.Value)
{
chnName = (string)rd["channelName"];
}
if (rd["unit"] != DBNull.Value)
{
unit = (string)rd["unit"];
}
}
cnn.Close();
}
if (!string.IsNullOrEmpty(chnId))
{
double?max = null;
double?min = null;
double?avg = null;
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select max([value]) as mx,min([value]) as mn,avg([value]) as vg from t_Data_Logger_" + chnId
+ " where [timestamp] between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd["mx"] != DBNull.Value)
{
max = (double)rd["mx"];
}
if (rd["mn"] != DBNull.Value)
{
min = (double)rd["mn"];
}
if (rd["vg"] != DBNull.Value)
{
avg = (double)rd["vg"];
}
}
cnn.Close();
}
if (max != null)
{
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select [timestamp],[value] from t_Data_Logger_" + chnId + " where [value]=" + max + " and [timestamp] between '"
+ start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
MMVModel d = new MMVModel();
if (rd["timestamp"] != DBNull.Value)
{
d.Tmstmp = (DateTime)rd["timestamp"];
}
if (rd["value"] != DBNull.Value)
{
d.Val = (double)rd["value"];
}
d.ChnId = chnId;
d.ChnName = chnName;
d.Unit = unit;
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Type = type;
d.TypeVal = "Max";
data.Add(d);
}
cnn.Close();
}
}
if (min != null)
{
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
string cmdText = "select [timestamp],[value] from t_Data_Logger_" + chnId + " where [value]=" + min + " and [timestamp] between '"
+ start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
SqlCommand cmd = new SqlCommand(cmdText, cnn);
cnn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
MMVModel d = new MMVModel();
if (rd["timestamp"] != DBNull.Value)
{
d.Tmstmp = (DateTime)rd["timestamp"];
}
if (rd["value"] != DBNull.Value)
{
d.Val = (double)rd["value"];
}
d.ChnId = chnId;
d.ChnName = chnName;
d.Unit = unit;
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Type = type;
d.TypeVal = "Min";
data.Add(d);
}
cnn.Close();
}
}
if (avg != null)
{
MMVModel d = new MMVModel();
d.Loc = loc;
d.LoggerId = loggerId;
d.SiteAliasName = siteAliasName;
d.SiteId = siteId;
d.Type = type;
d.TypeVal = "Avg";
d.Unit = unit;
d.Val = avg;
data.Add(d);
}
}
}