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