/// <summary>
/// Fetch data for gauging station and the related weather station to draw correaltion graph
/// </summary>
/// <param name="gaugingStationNumber"></param>
/// <returns></returns>
//public string getCorrelationDataForSelectedStation(string gaugingStationNumber)
//{
// //Fetch list of weather station from the XML file for the selected gauging station
// List< string> listOfWeatherStation = getListOfGaugeRelatedWeatherStation(gaugingStationNumber);
// if (listOfWeatherStation.Count==0)
// {
// return null;
// }
// //Call DB server to get data relevant to that station
// else
// {
// List<Object> weatherGaugeCorrelationData = new List<Object>();
// foreach (string weatherStNumber in listOfWeatherStation)
// {
// using (WaterAnalyticsEntities db = new WaterAnalyticsEntities())
// {
// var result = db.WaterLevels.Join(db.Weathers,
// x => x.Date,
// y => y.DateTime,
// (x, y) => new { x, y })
// .Where(waterL => waterL.x.StationCode == gaugingStationNumber)
// .Where(weath => weath.y.StationCode == weatherStNumber).ToList();
// weatherGaugeCorrelationData.Add(result);
// }
// }
// }
// //Format that data in the required format
// return null;
//}
public static Dictionary<string, object> getCorrelationDataForSelectedStation(string gaugingStationNumber, DateTime start, DateTime end)
{
//Fetch list of weather station from the XML file for the selected gauging station
WaterAnalyticsEntities db = new WaterAnalyticsEntities();
List<string> listOfWeatherStation = getListOfGaugeRelatedWeatherStation(gaugingStationNumber);
List<Object> weatherGaugeCorrelationData = null;
Dictionary<string, object> queryDict = new Dictionary<string, object>();
var endDt = db.WaterLevels.Where(x => x.StationCode == gaugingStationNumber).Max(x => x.Date);
var gaugingStationData = new List<WaterLevel>();
var sDate = endDt.Value.AddYears(-15);
if (endDt.HasValue)
{
gaugingStationData = db.WaterLevels.Where(x => x.StationCode == gaugingStationNumber).Where(x => x.Date >= sDate && x.Date <= endDt).OrderBy(x => x.Date).ToList();
}
//var gaugingStationData = db.WaterLevels.Where(x => x.StationCode == gaugingStationNumber).Where(x => x.Date >= start && x.Date <= end).OrderBy(x => x.Date).ToList();
queryDict.Add("gaugeData", gaugingStationData);
if (listOfWeatherStation.Count != 0)
{
weatherGaugeCorrelationData = new List<Object>();
foreach (string weatherStNumber in listOfWeatherStation)
{
//var endDate = db.Weathers.Where(x => x.StationCode == weatherStNumber).Max(x => x.DateTime);
var result = new List<Weather>();
//var stDate = endDate.Value.AddYears(-5);
if (endDt.HasValue)
{
result = db.Weathers.Where(x => x.StationCode == weatherStNumber && x.DateTime >= sDate && x.DateTime <= endDt).OrderBy(x => x.DateTime).ToList();
}
weatherGaugeCorrelationData.Add(result);
}
queryDict.Add("weatherData", weatherGaugeCorrelationData);
}
//Call DB server to get data relevant to that station
//else
//{
//weatherGaugeCorrelationData = new List<Object>();
//foreach (string weatherStNumber in listOfWeatherStation)
//{
// var endDate = db.Weathers.Where(x => x.StationCode == weatherStNumber).Max(x => x.DateTime);
// var result = new List<Weather>();
// var stDate = endDate.Value.AddYears(-5);
// if (endDate.HasValue)
// {
// result = db.Weathers.Where(x => x.StationCode == weatherStNumber && x.DateTime >= stDate && x.DateTime <= endDate).OrderBy(x => x.DateTime).ToList();
// }
// weatherGaugeCorrelationData.Add(result);
//}
//}
//Format that data in the required format
return queryDict;
}
protected string getWeatherData(string selectedStationCode)
{
float lat = 0;
float longi = 0;
String name = "";
using (WaterAnalyticsEntities db = new WaterAnalyticsEntities())
{
//Query to fetch latitude and longitude for xml data
var query = from c in db.Stations
where c.StationCode.Equals(selectedStationCode)
select c;
foreach (Station station in query)
{
lat = (float)station.Lat;
longi = (float)station.Long;
name = station.Name;
}
}
return (lat+";"+longi+";"+name);//getCurrentWeather(lat.ToString(), longi.ToString());
}
//
// GET: /Gauge/
/// <summary>
/// This method will give historic water level for particalar station code from database
/// </summary>
/// <param name="selectedStationCode"></param>
/// <returns></returns>
protected string getWaterLevel(string selectedStationCode)
{
WaterLevelList = new List<double>();
//Added for the Graph between water level VS Diacharge
WaterLevelListForDVSWL = new List<double>();
DischargeListForDVSWL = new List<double>();
DateListForDVSWL = new List<DateTime>();
using (WaterAnalyticsEntities db = new WaterAnalyticsEntities())
{
string waterLevelData = "";
string year = "";
string day = "";
string month = "";
string stationName = "";
//Added for the Graph between water level VS Diacharge
string yearDVSWL = "";
string dayDVSWL = "";
string monthDVSWL = "";
//get station name from data base
//FIXME:There's no need to query DB (PERFORMANCE)
//var stationNameQuery = from c in db.Stations
// where c.StationCode.Equals(selectedStationCode)
// select c;
var stationNameQuery = db.Stations.Where(x => x.StationCode == selectedStationCode);
//Station name attribute
foreach (Station stationsDetails in stationNameQuery)
{
stationName = stationsDetails.Name.ToString().Trim(' ');
}
var endDt = db.WaterLevels.Where(x => x.StationCode == selectedStationCode).Max(x => x.Date);
var sDate = endDt.Value.AddYears(-15);
//var query = from c in db.WaterLevels
// orderby c.Date ascending
// where c.StationCode.Equals(selectedStationCode)
// select c;
//var dates = db.WaterLevels.Where(x => x.StationCode == selectedStationCode && x.Date >= sDate && x.Date <= endDt).OrderBy(x => x.Date)
// .Select(x => x.Date).Distinct().ToList();
//List<WaterLevel> lstWaterlevels = new List<WaterLevel>();
//foreach (var item in dates)
//{
// lstWaterlevels.Add(db.WaterLevels.FirstOrDefault(x => x.Date == item));
//}
var query = db.WaterLevels.Where(x => x.StationCode == selectedStationCode && x.Date >= sDate && x.Date <= endDt).OrderBy(x => x.Date).ToList();
//added for redundant data
List<DateTime> lstIsProcessed = new List<DateTime>();
foreach (WaterLevel waterlevel in query)
{
if (lstIsProcessed.Contains(waterlevel.Date.Value)) continue;
//Getting First instance date i.e. day, month and year
if (day.Equals(""))
{
DateTime firstInstanceDate = (DateTime)waterlevel.Date;
year = Convert.ToString(firstInstanceDate.Year).Trim(' ');
day = Convert.ToString(firstInstanceDate.Day).Trim(' ');
month = Convert.ToString(firstInstanceDate.Month);
}
if (!waterLevelData.Equals(""))
{
waterLevelData += ",";
}
//Extracting water level from each instance
if (waterlevel.Discharge.ToString().Equals(""))
{
waterLevelData += "0";
}
else
{
waterLevelData += waterlevel.Discharge.ToString();
}
//Added to get waterLevel
if (!waterlevel.WateLevel.Equals(" ") && waterlevel.WateLevel != null)
{
WaterLevelList.Add(Convert.ToDouble(waterlevel.WateLevel));
}
//Added to get discharge vs preceptation
if (!waterlevel.WateLevel.Equals(" ") && waterlevel.WateLevel != null && !waterlevel.Discharge.Equals(" ") && waterlevel.Discharge != null)
{
if (dayDVSWL.Equals(""))
{
DateTime firstInstanceDate = (DateTime)waterlevel.Date;
yearDVSWL = Convert.ToString(firstInstanceDate.Year).Trim(' ');
dayDVSWL = Convert.ToString(firstInstanceDate.Day).Trim(' ');
monthDVSWL = Convert.ToString(firstInstanceDate.Month - 1);
dateDVSWL = yearDVSWL + "," + dayDVSWL + "," + monthDVSWL;
}
WaterLevelListForDVSWL.Add(Convert.ToDouble(waterlevel.WateLevel));
DischargeListForDVSWL.Add(Convert.ToDouble(waterlevel.Discharge));
DateListForDVSWL.Add(((DateTime)waterlevel.Date).Date);
}
lstIsProcessed.Add(waterlevel.Date.Value);
}
//Appending the date value in the string
//Format DAY;MONTH;YEAR;WATERLEVELS
waterLevelData = stationName + ";" + day + ";" + month + ";" + year + ";" + waterLevelData;
return waterLevelData;
}
}
/// <summary>
/// This method returns the last water level row by ordering dates
/// </summary>
/// <param name="selectedStationCode"></param>
/// <returns></returns>
protected String getLastWaterLevel(string selectedStationCode)
{
WaterLevelList.Sort();
String lowerQuartile = GetQuartile(WaterLevelList, 0.25).ToString();
String upperQuartile = GetQuartile(WaterLevelList, 0.75).ToString();
String middleQuartile = GetQuartile(WaterLevelList, 0.50).ToString();
String topQuartile = GetQuartile(WaterLevelList, 1.0).ToString();
String LowestQuartile = GetQuartile(WaterLevelList, 0.0).ToString();
using (WaterAnalyticsEntities db = new WaterAnalyticsEntities())
{
String result = "0";
var query = db.WaterLevels.Where(x => x.StationCode == selectedStationCode);
var lastByDate = query.Where(x => x.Date.HasValue && x.WateLevel.HasValue).OrderByDescending(x => x.Date).Take(1).FirstOrDefault();
if (lastByDate != null)
{
result = lastByDate.WateLevel.ToString();
}
else
{
result = "0";
}
if (lowerQuartile.Equals("0") && upperQuartile.Equals("0") && middleQuartile.Equals("0") && topQuartile.Equals("0") && LowestQuartile.Equals("0"))
{
lowerQuartile = "2";
upperQuartile = "7";
middleQuartile = "5";
topQuartile = "10";
LowestQuartile = "0";
result = "4";
}
return (LowestQuartile + ";" + lowerQuartile + ";" + middleQuartile + ";" + upperQuartile + ";" + topQuartile + ";" + result);
}
}
public Dictionary<string, object> getDataforScatterplot(string gaugingStationNumber, DateTime start, DateTime end)
{
WaterAnalyticsEntities db = new WaterAnalyticsEntities();
List<string> listOfWeatherStation = StaticGetListOfGaugeRelatedWeatherStation(gaugingStationNumber);
Dictionary<string, object> weatherGaugeSationData = new Dictionary<string, object>();
if (listOfWeatherStation.Count == 0)
{
return null;
}
//Call DB server to get data relevant to that station
else
{
foreach (string weatherStNumber in listOfWeatherStation)
{
string year = "";
string color = "";
int gVariant = 0;
var result = db.WaterLevels.Join(db.Weathers,
x => x.Date,
y => y.DateTime,
(x, y) => new { x, y })
.Where(waterL => waterL.x.StationCode == gaugingStationNumber)
.Where(weath => weath.y.StationCode == weatherStNumber).OrderBy(weath => weath.y.DateTime)
.Where(weath => weath.y.DateTime >= start && weath.y.DateTime <= end)
.Where(waterL => waterL.x.Date >= start && waterL.x.Date <= end).ToList();
List<scatterPlotObject> points = new List<scatterPlotObject>();
foreach (var joinValue in result)
{
var waterLevel = joinValue.x;
var weather = joinValue.y;
scatterPlotObject point = new scatterPlotObject();
point.discharge = waterLevel.Discharge;
point.rainfall = weather.Rainfall;
DateTime? datetime = weather.DateTime;
string thisInstanceYear = datetime.Value.Year.ToString();
if (!year.Equals(thisInstanceYear))
{
year = datetime.Value.Year.ToString();
gVariant += 100;
color = "rgba(223, " + gVariant + ", 83, .5)";
}
point.color = color;
point.date = datetime.Value.Date.ToString();
points.Add(point);
}
weatherGaugeSationData.Add(weatherStNumber, points);
}
}
return weatherGaugeSationData;
}
/// <summary>
/// @ author RKB dt: 05/18/2014 time 1:57 p.m.
/// Function return snowfall rainfall total precepeitaion for the specific station number
/// </summary>
/// <param name="selectedStationCode"></param>
/// <returns></returns>
private string getSnowFallRainFallTotalPrecepitation(string selectedStationCode)
{
string result="";
string rainfall = "";
string snowfall = "";
string totPrecp = "";
string day = "";
string month = "";
string year = "";
using (WaterAnalyticsEntities db = new WaterAnalyticsEntities())
{
//Query to fetch Data for recieving the weather detail for the station number received
var query = from c in db.Weathers
orderby c.DateTime ascending
where c.StationCode.Equals(selectedStationCode)
select c;
foreach (Weather weatherDetails in query)
{
if (day.Equals(""))
{
DateTime startDate = (DateTime)weatherDetails.DateTime;
day = startDate.Day.ToString();
month = (startDate.Month -1).ToString();
year = startDate.Year.ToString();
}
rainfall += (rainfall.Equals("") ? weatherDetails.Rainfall.ToString() : ("," + weatherDetails.Rainfall.ToString()));
snowfall += (snowfall.Equals("") ? weatherDetails.Snowfall.ToString() : ("," + weatherDetails.Snowfall.ToString()));
totPrecp += (totPrecp.Equals("") ? weatherDetails.TotalPrecp.ToString() : ("," + weatherDetails.TotalPrecp.ToString()));
}
}
result = day + ";" + month + ";" + year + ";" + rainfall + ";" + snowfall + ";" + totPrecp;
return result;
}
