public List <InfoBetweenDate> getInfoMinEachHour(string parameter, string cityName, DateTime dateTime)
{
string query;
bool allcity = false;
int cityId = -1;
if (dateTime == null)
{
return(null);
}
if (cityName == null)
{
query = string.Format(@"
SELECT MIN(Value) AS Minimum,
(CONVERT(varchar, DateTime, 23) + ' ' + LEFT(CONVERT(varchar, DateTime, 108), 2) + ':00:00') as Hour,
City_Name AS City
FROM {0} s JOIN {1} c ON s.CityId = c.Id
WHERE Param = @userParam AND CONVERT(varchar, DateTime, 23) = @datetime
GROUP BY (CONVERT(varchar, DateTime, 23) + ' ' + LEFT(CONVERT(varchar, DateTime, 108),2) + ':00:00'), City_Name
ORDER BY 2 DESC", DatabaseTableConstant.tableSensorData, DatabaseTableConstant.tableCity);
allcity = true;
}
else
{
query = string.Format(@"
SELECT MIN(Value) AS Minimum,
(CONVERT(varchar, DateTime, 23) + ' ' + LEFT(CONVERT(varchar, DateTime, 108), 2) + ':00:00') as Hour,
City_Name AS City
FROM {0} s JOIN {1} c ON s.CityId = c.Id
WHERE Param = @userParam AND CityId = @cityId AND CONVERT(varchar, DateTime, 23) = @datetime
GROUP BY (CONVERT(varchar, DateTime, 23) + ' ' + LEFT(CONVERT(varchar, DateTime, 108),2) + ':00:00'), City_Name
ORDER BY 2 DESC", DatabaseTableConstant.tableSensorData, DatabaseTableConstant.tableCity);
cityId = fetchCityIdFromName(cityName);
if (cityId == -1)
{
return(null);
}
}
List <InfoBetweenDate> listValues = new List <InfoBetweenDate>();
using (connection)
{
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("userParam", parameter.ToUpper());
if (!allcity)
{
command.Parameters.AddWithValue("cityId", cityId);
}
command.Parameters.AddWithValue("datetime", dateTime.ToString("yyyy-MM-dd") + "");
try
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
InfoBetweenDate infoBetweenDate = new InfoBetweenDate
{
Value = int.Parse(reader["Minimum"] + ""),
Date = reader["Hour"] + "",
City = reader["City"] + "",
};
listValues.Add(infoBetweenDate);
}
;
}
}
catch (Exception e)
{
Debug.WriteLine("Error while fetching min for each hour: " + e.Message);
}
}
return(listValues);
}
public List <InfoBetweenDate> getInfoMinBetweenDates(string parameter, string cityName, DateTime startDate, DateTime endDate)
{
string query;
bool allcity = false;
int cityId = -1;
if (startDate == null || endDate == null || startDate > endDate)
{
return(null);
}
if (cityName == null)
{
query = string.Format(@"
SELECT MIN(Value) AS Minimum, CONVERT(varchar, DateTime, 23) AS Day, City_Name AS City
FROM {0} s JOIN {1} c ON s.CityId = c.Id
WHERE Param = @userParam
AND DateTime >= @startDate AND DateTime <= @endDate
GROUP BY CONVERT(varchar, DateTime, 23), City_Name
ORDER BY 2 DESC", DatabaseTableConstant.tableSensorData, DatabaseTableConstant.tableCity);
allcity = true;
}
else
{
query = string.Format(@"
SELECT MIN(Value) as Minimum, CONVERT(varchar, DateTime, 23) as Day, City_Name AS City
FROM {0} s JOIN {1} c ON s.CityId = c.Id
WHERE Param = @userParam AND CityId = @cityId
AND DateTime >= @startDate AND DateTime <= @endDate
GROUP BY CONVERT(varchar, DateTime, 23), City_Name
ORDER BY 2 DESC", DatabaseTableConstant.tableSensorData, DatabaseTableConstant.tableCity);
cityId = fetchCityIdFromName(cityName);
if (cityId == -1)
{
return(null);
}
}
List <InfoBetweenDate> listValues = new List <InfoBetweenDate>();
using (connection)
{
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("userParam", parameter.ToUpper());
if (!allcity)
{
command.Parameters.AddWithValue("cityId", cityId);
}
command.Parameters.AddWithValue("startDate", startDate.ToString("yyyy-MM-dd 00:00:00"));
command.Parameters.AddWithValue("endDate", endDate.ToString("yyyy-MM-dd 23:59:59"));
try
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
InfoBetweenDate infoBetweenDate = new InfoBetweenDate
{
Value = int.Parse(reader["Minimum"] + ""),
Date = reader["Day"] + "",
City = reader["City"] + "",
};
listValues.Add(infoBetweenDate);
}
;
}
}
catch (Exception e)
{
Debug.WriteLine("Error while fetching min for each day: " + e.Message);
}
}
return(listValues);
}