public MedicineStockInCenter FindInCenter(MedicineStockInCenter aMedicineStockInCenter)
{
string query = "SELECT * FROM tbl_medicine_stock_center WHERE medicine_id = '" + aMedicineStockInCenter.MedicineId + "' AND center_id='" + aMedicineStockInCenter.CenterId + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
MedicineStockInCenter medicineStockInCenter;
if (ASqlDataReader.HasRows)
{
medicineStockInCenter = new MedicineStockInCenter();
ASqlDataReader.Read();
medicineStockInCenter.Id = (int)ASqlDataReader["id"];
medicineStockInCenter.CenterId = (int)ASqlDataReader["center_id"];
medicineStockInCenter.MedicineId = (int)ASqlDataReader["medicine_id"];
medicineStockInCenter.Quantity = (int)ASqlDataReader["quantity"];
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(medicineStockInCenter);
}
else
{
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(null);
}
}
public List <Thana> GetSelectedThanas(int districtId)
{
List <Thana> thanaList = new List <Thana>();
string query = "SELECT * FROM tbl_thana WHERE district_id='" + districtId + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
Thana aThana;
while (ASqlDataReader.Read())
{
aThana = new Thana();
aThana.Id = (int)ASqlDataReader["id"];
aThana.Name = ASqlDataReader["name"].ToString();
aThana.DistrictId = (int)ASqlDataReader["district_id"];
thanaList.Add(aThana);
}
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(thanaList);
}
public Center FindById(int centerId)
{
string query = "SELECT * FROM tbl_center WHERE id='" + centerId + "'";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
if (ASqlDataReader.HasRows)
{
Center aCenter = new Center();
ASqlDataReader.Read();
aCenter.Id = (int)ASqlDataReader["id"];
aCenter.Name = ASqlDataReader["name"].ToString();
aCenter.DistrictId = Convert.ToInt16(ASqlDataReader["district_id"]);
aCenter.ThanaId = Convert.ToInt16(ASqlDataReader["thana_id"]);
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(aCenter);
}
else
{
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(null);
}
}
public Center GetCenter(int id)
{
ASqlConnection.Open();
string query = "SELECT * FROM tbl_center where id = '" + id + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
if (ASqlDataReader.HasRows)
{
Center aCenter = new Center();
ASqlDataReader.Read();
aCenter.Id = Convert.ToInt32(ASqlDataReader["id"]);
aCenter.Name = ASqlDataReader["name"].ToString();
aCenter.DistrictId = Convert.ToInt32(ASqlDataReader["district_id"]);
aCenter.ThanaId = Convert.ToInt32(ASqlDataReader["thana_id"]);
ASqlDataReader.Close();
ASqlConnection.Close();
return(aCenter);
}
else
{
ASqlDataReader.Close();
ASqlConnection.Close();
return(null);
}
}
public Center CheckCodePassword(Center aCenter)
{
string query = "SELECT * FROM tbl_center_login WHERE center_code = '" + aCenter.Code + "' AND password='******'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
if (ASqlDataReader.HasRows)
{
Center bCenter = new Center();
ASqlDataReader.Read();
bCenter.Id = (int)ASqlDataReader["center_id"];
bCenter.Code = ASqlDataReader["center_code"].ToString();
bCenter.Password = ASqlDataReader["password"].ToString();
ASqlDataReader.Close();
ASqlConnection.Close();
return(bCenter);
}
else
{
ASqlDataReader.Close();
ASqlConnection.Close();
return(null);
}
}
public Center UniqueChecker(Center centerToBeChecked)
{
string query = "SELECT * FROM tbl_center WHERE name= '" + centerToBeChecked.Name + "' AND district_id='" +
centerToBeChecked.DistrictId + "'";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
if (ASqlDataReader.HasRows)
{
ASqlDataReader.Read();
Center aCenter = new Center();
aCenter.Name = ASqlDataReader["name"].ToString();
aCenter.DistrictId = Convert.ToInt16(ASqlDataReader["district_id"]);
aCenter.ThanaId = Convert.ToInt16(ASqlDataReader["thana_id"]);
ASqlDataReader.Close();
ASqlConnection.Close();
return(aCenter);
}
else
{
ASqlDataReader.Close();
ASqlConnection.Close();
return(null);
}
}
public List <ViewPatientHistory> GetPatient(long voterId)
{
int i = 0;
ASqlConnection.Open();
string query = "SELECT * FROM v_patient_history WHERE voter_id = '" + voterId + "' ORDER BY date";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
List <ViewPatientHistory> aViewPatientHistoryList = new List <ViewPatientHistory>();
while (ASqlDataReader.Read())
{
i++;
ViewPatientHistory aViewPatientHistory = new ViewPatientHistory();
aViewPatientHistory.CenterName = ASqlDataReader["name"].ToString();
aViewPatientHistory.HistoryNo = i;
aViewPatientHistoryList.Add(aViewPatientHistory);
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(aViewPatientHistoryList);
}
public Center GetCenterById(int id)
{
string query = "SELECT *FROM tbl_center WHERE id='" + id + "';";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
if (ASqlDataReader.HasRows)
{
Center aCenter = new Center();
while (ASqlDataReader.Read())
{
aCenter.Id = Convert.ToInt32(ASqlDataReader["id"]);
aCenter.Name = ASqlDataReader["name"].ToString();
aCenter.DistrictId = Convert.ToInt32(ASqlDataReader["district_id"]);
aCenter.ThanaId = Convert.ToInt32(ASqlDataReader["thana_id"]);
aCenter.Code = ASqlDataReader["code"].ToString();
aCenter.Password = ASqlDataReader["password"].ToString();
}
ASqlConnection.Close();
return(aCenter);
}
ASqlConnection.Close();
return(null);
}
public List <District> GetAllDistricts()
{
List <District> districtList = new List <District>();
string query = "SELECT * FROM tbl_district";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
while (ASqlDataReader.Read())
{
District aDistrict = new District();
aDistrict.Id = (int)ASqlDataReader["id"];
aDistrict.Name = ASqlDataReader["name"].ToString();
districtList.Add(aDistrict);
}
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(districtList);
}
public List <ViewMedicineStockInCenter> GetMedicineStockInCenters(int centerId)
{
List <ViewMedicineStockInCenter> stockMedicineList = new List <ViewMedicineStockInCenter>();
string query = "SELECT * FROM v_medicine_stock_report WHERE center_id='" + centerId + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
while (ASqlDataReader.Read())
{
ViewMedicineStockInCenter aStockInCenter = new ViewMedicineStockInCenter();
aStockInCenter.CenterId = (int)ASqlDataReader["center_id"];
aStockInCenter.Name = ASqlDataReader["name"].ToString();
aStockInCenter.Quantity = (int)ASqlDataReader["quantity"];
stockMedicineList.Add(aStockInCenter);
}
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(stockMedicineList);
}
public List <Center> GetSelectedCenters(int thanaId)
{
List <Center> centerList = new List <Center>();
string query = "SELECT * FROM tbl_center WHERE thana_id='" + thanaId + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
Center aCenter;
while (ASqlDataReader.Read())
{
aCenter = new Center();
aCenter.Id = (int)ASqlDataReader["id"];
aCenter.Name = ASqlDataReader["name"].ToString();
aCenter.DistrictId = (int)ASqlDataReader["district_id"];
aCenter.ThanaId = (int)ASqlDataReader["thana_Id"];
centerList.Add(aCenter);
}
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(centerList);
}
public Patient Find(long voterId)
{
string query = "SELECT * FROM tbl_patient WHERE voter_id='" + voterId + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
if (ASqlDataReader.HasRows)
{
Patient aPatient = new Patient();
ASqlDataReader.Read();
aPatient.Id = (int)ASqlDataReader["id"];
aPatient.VoterId = (long)ASqlDataReader["voter_id"];
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(aPatient);
}
else
{
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(null);
}
}
public List <Doctor> GetSelectedDoctors(int centerId)
{
List <Doctor> doctorList = new List <Doctor>();
string query = "SELECT * FROM tbl_doctor WHERE center_id='" + centerId + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
Doctor aDoctor;
while (ASqlDataReader.Read())
{
aDoctor = new Doctor();
aDoctor.Id = (int)ASqlDataReader["id"];
aDoctor.Name = ASqlDataReader["name"].ToString();
aDoctor.Degree = ASqlDataReader["degree"].ToString();
aDoctor.Specialization = ASqlDataReader["specialization"].ToString();
aDoctor.CenterId = (int)ASqlDataReader["center_id"];
doctorList.Add(aDoctor);
}
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(doctorList);
}
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 <Medicine> GetSelectedMedicines(int centerId)
{
List <Medicine> medicineList = new List <Medicine>();
string query = "SELECT * FROM tbl_medicine med JOIN tbl_medicine_stock_center med_stock ON med.id = med_stock.medicine_id WHERE med_stock.center_id='" + centerId + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
while (ASqlDataReader.Read())
{
Medicine aMedicine = new Medicine();
aMedicine.Id = (int)ASqlDataReader["id"];
aMedicine.Name = ASqlDataReader["name"].ToString();
aMedicine.Power = Convert.ToDecimal(ASqlDataReader["power"]);
aMedicine.Type = ASqlDataReader["type"].ToString();
medicineList.Add(aMedicine);
}
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(medicineList);
}
public List <Disease> GetAllDiseases()
{
List <Disease> diseaseList = new List <Disease>();
string query = "SELECT * FROM tbl_disease";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
Disease aDisease;
while (ASqlDataReader.Read())
{
aDisease = new Disease();
aDisease.Id = (int)ASqlDataReader["id"];
aDisease.Name = ASqlDataReader["name"].ToString();
aDisease.Description = ASqlDataReader["description"].ToString();
aDisease.TreatmentProcedure = ASqlDataReader["treatment_procedure"].ToString();
aDisease.PreferredDrug = ASqlDataReader["preferred_drug"].ToString();
diseaseList.Add(aDisease);
}
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(diseaseList);
}
public Medicine Find(string name)
{
string query = "SELECT * FROM tbl_medicine WHERE name='" + name + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
if (ASqlDataReader.HasRows)
{
Medicine aMedicine = new Medicine();
ASqlDataReader.Read();
aMedicine.Id = (int)ASqlDataReader["id"];
aMedicine.Name = ASqlDataReader["name"].ToString();
aMedicine.Power = Convert.ToDecimal(ASqlDataReader["power"]);
aMedicine.Type = ASqlDataReader["type"].ToString();
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(aMedicine);
}
else
{
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(null);
}
}
public List <Medicine> GetAllMedicines()
{
List <Medicine> medicineList = new List <Medicine>();
string query = "SELECT * FROM tbl_medicine";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
while (ASqlDataReader.Read())
{
Medicine aMedicine = new Medicine();
aMedicine.Id = (int)ASqlDataReader["id"];
aMedicine.Name = ASqlDataReader["name"].ToString();
aMedicine.Power = Convert.ToDecimal(ASqlDataReader["power"]);
aMedicine.Type = ASqlDataReader["type"].ToString();
medicineList.Add(aMedicine);
}
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(medicineList);
}
public List <Stock> GetAll(int centerId)
{
string query = "SELECT *FROM tbl_stock where centerId=" + centerId;
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
List <Stock> stockList = new List <Stock>();
while (ASqlDataReader.Read())
{
MedicineDBGateway aGateway = new MedicineDBGateway();;
Stock aStock = new Stock();
Medicine aMedicine = aGateway.Find(Convert.ToInt32(ASqlDataReader["id"]));
aStock.MedicineName = aMedicine.Name + "_" + aMedicine.MgMl;
aStock.Quantity = (int)ASqlDataReader["quantity"];
stockList.Add(aStock);
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(stockList);
}
public List <ViewStrory> ViewStory(string name)
{
DiseaseDBGateway aDiseaseDbGateway = new DiseaseDBGateway();
DistrictDBGateway aDistrictDbGateway = new DistrictDBGateway();
ThanaDBGateway aThanaDbGateway = new ThanaDBGateway();
CenterDBGateway aCenterDbGateway = new CenterDBGateway();
string query = "SELECT *FROM tbl_patient WHERE name='" + name + "'";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
List <ViewStrory> viewHistories = new List <ViewStrory>();
while (ASqlDataReader.Read())
{
ViewStrory aViewHistory = new ViewStrory();
aViewHistory.Id = Convert.ToInt32(ASqlDataReader["id"]);
aViewHistory.Name = ASqlDataReader["name"].ToString();
aViewHistory.Diseases = aDiseaseDbGateway.FindId(Convert.ToInt32(ASqlDataReader["diseaseId"])).Name;
aViewHistory.Destrict = aDistrictDbGateway.Find(Convert.ToInt32(ASqlDataReader["districtId"])).Name;
aViewHistory.Thana = aThanaDbGateway.Find(Convert.ToInt32(ASqlDataReader["thanaId"])).Name;
aViewHistory.Center = aCenterDbGateway.GetCenterById(Convert.ToInt32(ASqlDataReader["centerId"])).Name;
viewHistories.Add(aViewHistory);
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(viewHistories);
}
public int GetServiceTakenId()
{
string query = "SELECT MAX(id) FROM tbl_service_taken";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
ASqlDataReader.Read();
int serviceTakenId = (int)ASqlDataReader[0];
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(serviceTakenId);
}
public int GetDistrictIdByCenterId(int centerId)
{
string query = "SELECT district_id FROM tbl_center WHERE id = '" + centerId + "'";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
Treatment aTreatment = new Treatment();
while (ASqlDataReader.Read())
{
aTreatment.DistrictId = (int)ASqlDataReader["district_id"];
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(aTreatment.DistrictId);
}
public District Find(int id)
{
District aDistrict = new District();
string query = "SELECT *FROM tbl_district WHERE id = '" + id + "'";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
while (ASqlDataReader.Read())
{
aDistrict.Id = Convert.ToInt32(ASqlDataReader["id"]);
aDistrict.Name = ASqlDataReader["name"].ToString();
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(aDistrict);
}
public int GetTotalNumberOfService(long voterId)
{
string query = "SELECT COUNT(patient.voter_id) FROM tbl_service_taken serviceTaken" +
" JOIN tbl_patient patient ON patient.id = serviceTaken.patient_id" +
" WHERE patient.voter_id='" + voterId + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
ASqlDataReader.Read();
int totalNumberOfService = (int)ASqlDataReader[0];
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return(totalNumberOfService);
}
public Center Search(int id)
{
Center aCenter = new Center();
string query = "SELECT *FROM tbl_center WHERE id = '" + id + "'";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
while (ASqlDataReader.Read())
{
aCenter.Id = Convert.ToInt32(ASqlDataReader["id"]);
aCenter.Name = ASqlDataReader["name"].ToString();
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(aCenter);
}
public Thana Find(int id)
{
Thana aThana = new Thana();
string query = "SELECT *FROM tbl_thana WHERE id = '" + id + "'";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
while (ASqlDataReader.Read())
{
aThana.Id = Convert.ToInt32(ASqlDataReader["id"]);
aThana.Name = ASqlDataReader["name"].ToString();
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(aThana);
}
public int Count(int patientId)
{
string query = "Select Count (patientid) As serviceGiven from tbl_treatment where patientId=" + patientId;
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
int service = 0;
while (ASqlDataReader.Read())
{
service = (int)ASqlDataReader["serviceGiven"];
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(service);
}
public Medicine Find(string name)
{
string query = "SELECT *FROM tbl_medicine where Name='" + name + "';";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
Medicine aMedicine = new Medicine();
while (ASqlDataReader.Read())
{
aMedicine.Id = Convert.ToInt32(ASqlDataReader["id"]);
aMedicine.Name = ASqlDataReader["Name"].ToString();
aMedicine.MgMl = ASqlDataReader["mg_ml"].ToString();
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(aMedicine);
}
public List <Center> GetAllCenterByThanaId(int id)
{
List <Center> aCenterList = new List <Center>();
string query = "SELECT *FROM tbl_center WHERE thana_id = '" + id + "'";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
while (ASqlDataReader.Read())
{
Center aCenter = new Center();
aCenter.Id = Convert.ToInt32(ASqlDataReader["id"]);
aCenter.Name = ASqlDataReader["name"].ToString();
aCenterList.Add(aCenter);
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(aCenterList);
}
public Patient Search(string name)
{
string query = "SELECT *FROM tbl_patient where name='" + name + "';";
ASqlConnection.Open();
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlDataReader = ASqlCommand.ExecuteReader();
Patient aPatient = new Patient();
while (ASqlDataReader.Read())
{
aPatient.Id = Convert.ToInt32(ASqlDataReader["id"]);
aPatient.Name = ASqlDataReader["Name"].ToString();
aPatient.CenterId = Convert.ToInt32(ASqlDataReader["centerId"]);
}
ASqlDataReader.Close();
ASqlConnection.Close();
return(aPatient);
}