public List <DiseaseWiseReport> GetReportBetweenDates(string diseaseName, DateTime startDate, DateTime endDate)
{
SqlQuery =
"SELECT t1.district_name,COUNT(t1.district_name) AS total_patient,t1.population" +
" FROM (" +
"SELECT v1.district_name,v1.voter_id,v1.population " +
"FROM view_district_wise_patients v1 " +
"WHERE v1.disease_name='" + diseaseName + "' and v1.date BETWEEN '" + startDate + "' AND '" + endDate + "' " +
"GROUP BY v1.district_name,v1.voter_id,v1.population) t1 " +
"GROUP BY t1.district_name,t1.population";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
List <DiseaseWiseReport> reports = new List <DiseaseWiseReport>();
if (DbSqlDataReader.HasRows)
{
while (DbSqlDataReader.Read())
{
DiseaseWiseReport aReport = new DiseaseWiseReport();
aReport.DistrictName = DbSqlDataReader["district_name"].ToString();
aReport.TotalPatient = Convert.ToInt32(DbSqlDataReader["total_patient"].ToString());
aReport.PercentageOfPopulation = (aReport.TotalPatient * 100 / Convert.ToInt32(DbSqlDataReader["population"]));
reports.Add(aReport);
}
}
DbSqlConnection.Close();
return(reports);
}
public List <DiseaseWiseReport> GetDiseaseWiseReports(DiseaseDate diseaseDate)
{
List <District> districts = headGateway.GetDistrictList();
List <DiseaseWiseReport> diseaseWiseReports = new List <DiseaseWiseReport>();
foreach (District district in districts)
{
List <Treatment> treatments = new List <Treatment>();
List <int> centerList = headGateway.GetCenterListByDistrictId(district.Id);
foreach (int i in centerList)
{
List <Treatment> newTreatments = headGateway.GetTreatmentListByCenterId(i, diseaseDate.DiseaseId, diseaseDate.BeginDateTime, diseaseDate.EndDateTime);
treatments.AddRange(newTreatments);
}
DiseaseWiseReport diseaseWiseReport = new DiseaseWiseReport();
diseaseWiseReport.DistrictId = district.Id;
diseaseWiseReport.DistrictName = district.Name;
diseaseWiseReport.TotalPatients = treatments.GroupBy(x => x.VoterIdNo).Select(x => x.First()).Count();
diseaseWiseReport.PercentageOfPopulation = ((double)diseaseWiseReport.TotalPatients * 100 / (double)district.Population);
diseaseWiseReports.Add(diseaseWiseReport);
}
return(diseaseWiseReports);
}
