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