public List <SummaryDHdata> GetHeatMapdata(string dataset) { List <SummaryDHdata> listResult = new List <SummaryDHdata>(); List <DataSetDate> datasets = GetListDataSetDate().ToList(); DataSetDate selecteddataset = datasets.Where(x => x.code.Equals(dataset)).FirstOrDefault(); var listtemp = rpGeneric2nd.FindByNativeSQL("select * from datahub_summary where schooltype = 32 and Year = " + selecteddataset.year + " and month = " + selecteddataset.month); foreach (var itemrow in listtemp) { SummaryDHdata temp = new SummaryDHdata(); if (itemrow != null) { temp.year = Convert.ToInt16(itemrow[0].ToString()); temp.month = Convert.ToInt16(itemrow[1].ToString()); temp.seedcode = itemrow[2].ToString(); temp.listdata = new List <GenericData>(); temp.listdata.Add(new GenericData("Participating Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[4]))); temp.listdata.Add(new GenericData("Non-Participating Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[5]))); temp.listdata.Add(new GenericData("Unknown Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[6]))); temp.sdataset = datasets.Where(x => x.month.Equals(temp.month.ToString()) && x.year.Equals(temp.year.ToString())).FirstOrDefault(); listResult.Add(temp); } } return(listResult.OrderBy(x => x.year).ThenBy(x => x.month).ToList()); }
public List <SummaryDHdata> GetSummaryData(string dataset, string centretype) { IList <DatahubCentre> listCentres = null; List <SummaryDHdata> listResult = new List <SummaryDHdata>(); List <DataSetDate> datasets = GetListDataSetDate().ToList(); DataSetDate selecteddataset = datasets.Where(x => x.code.Equals(dataset)).FirstOrDefault(); string query = "select * from datahub_summary where schooltype = " + centretype + " and Year = " + selecteddataset.year + " and month = " + selecteddataset.month; if (centretype.Equals("32")) { listCentres = GetListNeighbourhoodsname(rpGeneric2nd); listCentres.Add(new DatahubCentre("1002", "Aberdeen City", "1")); //add city data to neighbourhoods query = query + " union select * from datahub_summary where seedcode = 1002 and schooltype = 3 and Year = " + selecteddataset.year + " and month = " + selecteddataset.month; } else { listCentres = GetListSchoolname(); listCentres.Add(new DatahubCentre("1002", "Aberdeen City", "1")); } var listtemp = rpGeneric2nd.FindByNativeSQL(query); foreach (var itemrow in listtemp) { SummaryDHdata temp = new SummaryDHdata(); if (itemrow != null) { temp.year = Convert.ToInt16(itemrow[0].ToString()); temp.month = Convert.ToInt16(itemrow[1].ToString()); temp.seedcode = itemrow[2].ToString(); temp.centrename = listCentres.Where(x => x.seedcode.Equals(temp.seedcode)).Select(x => x.name).First().ToString(); temp.listdata = new List <GenericData>(); temp.listdata.Add(new GenericData("Positive Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[4]))); temp.listdata.Add(new GenericData("Non-Positive Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[5]))); temp.listdata.Add(new GenericData("Unknown Destination", NumberFormatHelper.ConvertObjectToFloat(itemrow[6]))); temp.sdataset = datasets.Where(x => x.month.Equals(temp.month.ToString()) && x.year.Equals(temp.year.ToString())).FirstOrDefault(); listResult.Add(temp); } } return(listResult.OrderBy(x => x.centrename).ToList()); }