public List <DiseaseReport> GetDiseaseReport(string fromDate, string toDate, string diseaseName)
{
string query = "SELECT t1.district_name,COUNT(t1.district_name) AS total_patient,t1.population FROM (SELECT v1.district_name,v1.voter_id,v1.population FROM v_district_wise_patient v1 WHERE v1.disease_name='" + diseaseName + "' and v1.date BETWEEN '" + fromDate + "' AND '" + toDate + "') t1 GROUP BY t1.district_name,t1.population";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
List <DiseaseReport> diseaseReportList = new List <DiseaseReport>();
while (ASqlDataReader.Read())
{
DiseaseReport aDiseaseReport = new DiseaseReport();
aDiseaseReport.DistrictName = ASqlDataReader["district_name"].ToString();
aDiseaseReport.TotalPatient = (int)ASqlDataReader["total_patient"];
aDiseaseReport.PercentagePatient = (double)((int)ASqlDataReader["total_patient"] * 100) / (int)ASqlDataReader["population"];
diseaseReportList.Add(aDiseaseReport);
}
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(diseaseReportList);
}
public List <DiseaseReport> GetDiseaseReport(string fromDate, string toDate, string diseaseName)
{
SqlConnection connection = new SqlConnection(connectionstring);
int id = 1;
string query = "select d.district_Name ,Count(distinct(t.treatment_VoterId)) as Total_Patients,d.district_Population From Table_Treatment as t inner join Table_Center as c on t.treatment_CenterId =c.center_Id inner join Table_Thana as th on c.center_ThanaId=th.thana_Id inner join Table_District as d on th.thana_DistrictId=d.district_Id inner join Table_Disease as de on t.treatment_DiseaseName=de.disease_Name where de.disease_Name='" + diseaseName + "' and treatment_Date Between '" + fromDate + "' and '" + toDate + "' GROUP BY d.district_Name,de.disease_Name,d.district_Population";
connection.Open();
SqlCommand command = new SqlCommand(query, connection);
SqlDataReader reader = command.ExecuteReader();
List <DiseaseReport> diseaseReportList = new List <DiseaseReport>();
while (reader.Read())
{
DiseaseReport aDiseaseReport = new DiseaseReport();
aDiseaseReport.Id = id;
aDiseaseReport.DistrictName = reader[0].ToString();
aDiseaseReport.TotalPatient = int.Parse(reader[1].ToString());
aDiseaseReport.PercentagePatient = (double)((int)reader[1] * 100.0) / (int)reader[2];
diseaseReportList.Add(aDiseaseReport);
id++;
}
reader.Close();
command.Dispose();
connection.Close();
return(diseaseReportList);
}
public List <DiseaseReport> GetTotalPatientList(DateTime startDateTime, DateTime endDateTime, int diseaseId)
{
DiseaseReport aDiseaseReport = new DiseaseReport();
List <DiseaseReport> diseaseReports = new List <DiseaseReport>();
List <Patient> patients = aDiseaseDbGateway.GetTotalPatientList(startDateTime, endDateTime, diseaseId);
int totalPatient = patients.Count;
foreach (Patient aPatient in patients)
{
int population = aDistrictAndThanaDbGateway.GetDistrict(aPatient.DistrictId).Population;
aDiseaseReport.TotalPatient = totalPatient;
aDiseaseReport.PercentagePatient = GetPercentageOfPatient(totalPatient, population);
aDiseaseReport.DistrictName = aDistrictAndThanaDbGateway.GetDistrict(aPatient.DistrictId).Name;
diseaseReports.Add(aDiseaseReport);
}
return(diseaseReports);
}
protected void showButton_Click(object sender, EventArgs e)
{
DiseaseReport aDiseaseReport = new DiseaseReport();
aDiseaseReport.DiseaseId = Convert.ToInt32(diseaseDropDownList.SelectedValue);
int centerId = Convert.ToInt16(Session["centerId"]);
aDiseaseReport.DistrictId = aTreatmentManager.GetDistrictIdByCenterId(centerId);
aDiseaseReport.FromTime = fromCalendar.SelectedDate;
aDiseaseReport.From = aDiseaseReport.FromTime.ToString("yyyy-MM-dd");
aDiseaseReport.ToTime = toCalendar.SelectedDate;
aDiseaseReport.To = aDiseaseReport.ToTime.ToString("yyyy-MM-dd");
List <DiseaseReport> getDiseaseReports = aDiseaseManager.GetDiseaseReport(aDiseaseReport);
reportGridView.DataSource = getDiseaseReports;
reportGridView.DataBind();
}
public List <DiseaseReport> GetDiseaseReport(DiseaseReport aDiseaseReport)
{
List <DiseaseReport> aListOfDiseaseReports = new List <DiseaseReport>();
string query = "SELECT tbl_district.name AS Name,tbl_district.population AS Population,COUNT(tbl_treatment.patientId) AS Total from tbl_district LEFT JOIN tbl_treatment ON tbl_treatment.'" + aDiseaseReport.DiseaseId + "' = tbl_district.id WHERE tbl_treatment.districtId='" + aDiseaseReport.DistrictId + "' AND tbl_treatment.diseaseId = '" + aDiseaseReport.DiseaseId + "' AND date BETWEEN '" + aDiseaseReport.From + "' AND '" + aDiseaseReport.To + "' GROUP BY tbl_district.name,tbl_district.population";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
if (ASqlDataReader.HasRows)
{
while (ASqlDataReader.Read())
{
DiseaseReport aReport = new DiseaseReport();
aReport.DistrictName = ASqlDataReader["Name"].ToString();
aReport.Population = Convert.ToInt32(ASqlDataReader["Population"]);
aReport.TotalPatient = Convert.ToInt32(ASqlDataReader["Total"]);
aListOfDiseaseReports.Add(aReport);
}
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(aListOfDiseaseReports);
}
public List <DiseaseReport> GetDiseaseReport(DiseaseReport aDiseaseReport)
{
return(aDbGateway.GetDiseaseReport(aDiseaseReport));
}
