public IEnumerable <ChannelDataViewModel> GetChannelData(string channelId, string start, string end)
{
string dtFormat = "yyyy-MM-dd HH:mm";
DateTime dStart = DateTime.FromOADate(double.Parse(start.Replace('_', '.')));
DateTime dEnd = DateTime.FromOADate(double.Parse(end.Replace('_', '.')));
dStart = dStart.AddMinutes(1);
dEnd = dEnd.AddMinutes(1);
List <ChannelDataViewModel> lst = new List <ChannelDataViewModel>();
string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string cmdText = "select [timestamp],[value] from [t_Data_Logger_" + channelId + "] where [timestamp] between '"
+ dStart.ToString(dtFormat)
+ "' and '"
+ dEnd.ToString(dtFormat)
+ "' order by [timestamp]";
SqlCommand cmd = new SqlCommand(cmdText, connection);
connection.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
ChannelDataViewModel d = new ChannelDataViewModel();
d.Timestamp = (DateTime)rd["timestamp"];
if (rd["value"] != DBNull.Value)
{
d.Value = (double)rd["value"];
}
lst.Add(d);
}
connection.Close();
}
return(lst);
}
public IEnumerable <ChannelDataViewModel> GetChannelDataYearly(string siteId, string start, string end)
{
var ds = UnixToLocalDate(long.Parse(start));
var de = UnixToLocalDate(long.Parse(end));
List <ChannelDataViewModel> lst = new List <ChannelDataViewModel>();
string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand("p_Calculate_One_Site_Yearly_Output", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(
new SqlParameter("@SiteId", siteId));
cmd.Parameters.Add(
new SqlParameter("@Start", ds));
cmd.Parameters.Add(
new SqlParameter("@End", de));
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
ChannelDataViewModel d = new ChannelDataViewModel();
d.Timestamp = (DateTime)rd["timestamp"];
if (rd["value"] != DBNull.Value)
{
d.Value = (double)rd["value"];
}
lst.Add(d);
}
connection.Close();
}
return(lst);
}
