public List<DiseaseReportViewModel> GetAllDemographicData(string fromDate, string toDate,string DiseaseName) { string query = string.Format("select distinctCheck.DistrictName,COUNT(*) as NoOfPatient,DistrictID,distinctCheck.population,distinctCheck.pct,distinctCheck.DeseaseName from (select fin.DistrictName,NoOfPatient,Districts.DistrictID,Districts.population,(NoOfPatient/Districts.Population)*100 as pct,DeseaseName from (select DistrictName,COUNT(voternumber) as NoOfPatient,DeseaseName from (select DistrictName,ThanaName,ClinicName,voterNumber,DeseaseName from CommunityClinics,Districts,Deseases,Thanas,(SELECT * FROM Treatments WHERE date >= '{0}' AND date < '{1}') a where a.DeseaseID=Deseases.DeseaseID and a.CenterID=CommunityClinics.CommunityClinicID and CommunityClinics.ThanaID=Thanas.ThanaID and Thanas.DistrictID=Districts.DistrictID)abc group by DistrictName,DeseaseName,voterNumber ) fin,Districts where fin.DistrictName=Districts.DistrictName and fin.DeseaseName='{2}') distinctCheck group by distinctCheck.DistrictName,DistrictID,distinctCheck.population,distinctCheck.pct,distinctCheck.DeseaseName", fromDate, toDate, DiseaseName); aSqlConnection.Open(); aSqlCommand = new SqlCommand(query, aSqlConnection); SqlDataReader aDataReader = aSqlCommand.ExecuteReader(); List<DiseaseReportViewModel> aList = new List<DiseaseReportViewModel>(); while (aDataReader.Read()) { //Designation aDesignation = new Designation(); DiseaseReportViewModel aDiseaseModel = new DiseaseReportViewModel(); aDiseaseModel.DistrictName = aDataReader[0].ToString(); aDiseaseModel.DistrictID = Convert.ToInt32(aDataReader[2]); aDiseaseModel.Population = Convert.ToInt32(aDataReader[3]); aDiseaseModel.NoOfPatient = Convert.ToInt32(aDataReader["NoOfPatient"]); aDiseaseModel.DeseaseName = Convert.ToString(aDataReader["DeseaseName"]); aList.Add(aDiseaseModel); } aDataReader.Close(); aSqlConnection.Close(); return aList; }
public List<DiseaseReportViewModel> GetDemographicData(string fromDate, string toDate, int DistrictId, string DiseaseName) { string q1 = "select fin.DistrictName,NoOfPatient,Districts.DistrictID,Districts.population,(NoOfPatient/Districts.Population)*100 as pct,DeseaseName from (select DistrictName,COUNT(voternumber) as NoOfPatient,DeseaseName"; String q2 = "from (select DistrictName,ThanaName,ClinicName,voterNumber,DeseaseName from CommunityClinics,Districts,Deseases,Thanas,(SELECT * FROM Treatments"; String q3 = string.Format("WHERE date >= '{0}'AND date < '{1}') a where a.DeseaseID=Deseases.DeseaseID and a.CenterID=CommunityClinics.CommunityClinicID and CommunityClinics.ThanaID=Thanas.ThanaID and Thanas.DistrictID=Districts.DistrictID)abc group by DistrictName,DeseaseName ) fin,Districts where fin.DistrictName=Districts.DistrictName and Districts.DistrictID={2}", fromDate, toDate, DistrictId); string query = q1 + q2 + q3; string qyery1 = string.Format("select fin.DistrictName,NoOfPatient,Districts.DistrictID,Districts.population,(NoOfPatient/Districts.Population)*100 as pct,DeseaseName from (select DistrictName,COUNT(voternumber) as NoOfPatient,DeseaseName from (select DistrictName,ThanaName,ClinicName,voterNumber,DeseaseName from CommunityClinics,Districts,Deseases,Thanas,(SELECT * FROM Treatments WHERE date >= '{0}' AND date < '{1}') a where a.DeseaseID=Deseases.DeseaseID and a.CenterID=CommunityClinics.CommunityClinicID and CommunityClinics.ThanaID=Thanas.ThanaID and Thanas.DistrictID=Districts.DistrictID)abc group by DistrictName,DeseaseName ) fin,Districts where fin.DistrictName=Districts.DistrictName and Districts.DistrictId={2} and fin.DeseaseName='{3}'", fromDate, toDate, DistrictId, DiseaseName); string queryDistinctVoter = string.Format("select fin.DistrictName,NoOfPatient,Districts.DistrictID,Districts.population,(NoOfPatient/Districts.Population)*100 as pct,DeseaseName from (select DistrictName,COUNT(voternumber) as NoOfPatient,DeseaseName from (select DistrictName,ThanaName,ClinicName,voterNumber,DeseaseName from CommunityClinics,Districts,Deseases,Thanas,(SELECT * FROM Treatments WHERE date >= '{0}' AND date < '{1}') a where a.DeseaseID=Deseases.DeseaseID and a.CenterID=CommunityClinics.CommunityClinicID and CommunityClinics.ThanaID=Thanas.ThanaID and Thanas.DistrictID=Districts.DistrictID)abc group by DistrictName,DeseaseName,voterNumber ) fin,Districts where fin.DistrictName=Districts.DistrictName and Districts.DistrictId={2} and fin.DeseaseName='{3}'",fromDate, toDate, DistrictId, DiseaseName); aSqlConnection.Open(); aSqlCommand = new SqlCommand(qyery1, aSqlConnection); SqlDataReader aDataReader = aSqlCommand.ExecuteReader(); List<DiseaseReportViewModel> aList = new List<DiseaseReportViewModel>(); while (aDataReader.Read()) { //Designation aDesignation = new Designation(); DiseaseReportViewModel aDiseaseModel = new DiseaseReportViewModel(); aDiseaseModel.DistrictName = aDataReader[0].ToString(); aDiseaseModel.DistrictID = Convert.ToInt32(aDataReader[2]); aDiseaseModel.Population = Convert.ToInt32(aDataReader[3]); aDiseaseModel.NoOfPatient = Convert.ToInt32(aDataReader["NoOfPatient"]); aDiseaseModel.DeseaseName = Convert.ToString(aDataReader["DeseaseName"]); aList.Add(aDiseaseModel); } aDataReader.Close(); aSqlConnection.Close(); List<DiseaseReportViewModel> aList2 = new List<DiseaseReportViewModel>(); { aSqlConnection.Open(); aSqlCommand = new SqlCommand(queryDistinctVoter, aSqlConnection); SqlDataReader aDataReader1 = aSqlCommand.ExecuteReader(); List<DiseaseReportViewModel> aList1 = new List<DiseaseReportViewModel>(); while (aDataReader1.Read()) { //Designation aDesignation = new Designation(); DiseaseReportViewModel aDiseaseModel = new DiseaseReportViewModel(); aDiseaseModel.DistrictName = aDataReader1[0].ToString(); aDiseaseModel.DistrictID = Convert.ToInt32(aDataReader1[2]); aDiseaseModel.Population = Convert.ToInt32(aDataReader1[3]); aDiseaseModel.NoOfPatient = Convert.ToInt32(aDataReader1["NoOfPatient"]); aDiseaseModel.DeseaseName = Convert.ToString(aDataReader1["DeseaseName"]); aList1.Add(aDiseaseModel); } DiseaseReportViewModel aDiseaseModel1 = new DiseaseReportViewModel(); if (aList1.Count > 0) { aDiseaseModel1.NoOfPatient = aList1.Count; aDiseaseModel1.DistrictName = aList1.First().DistrictName; aDiseaseModel1.DistrictID = aList1.First().DistrictID; aDiseaseModel1.Population = aList1.First().Population; aDiseaseModel1.Percentage = (aList1.Count/aList1.First().Population)*100; aDiseaseModel1.DeseaseName = aList1.First().DeseaseName; } aList2.Add(aDiseaseModel1); aDataReader1.Close(); aSqlConnection.Close(); } //if voter not distinct //return aList; //if voter distinct return aList2; }