public void Save(Treatment aTreatment)
{
SqlQuery = "SELECT quantity FROM tbl_medicines_of_centerS WHERE center_id=" + aTreatment.CenterId +
" AND medicine_id=" + aTreatment.MedicineId + ";";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
DbSqlDataReader.Read();
var quantity = (int)DbSqlDataReader["quantity"];
DbSqlConnection.Close();
if (quantity >= aTreatment.Quantity)
{
SqlQuery = "INSERT INTO tbl_treatments VALUES('" + aTreatment.Observation + "','" + aTreatment.Date +
"'," +
aTreatment.DoctorId + "," + aTreatment.DiseaseId + "," + aTreatment.MedicineId + ",'" +
aTreatment.Dose + "'," + aTreatment.Quantity + ",'" + aTreatment.Note + "','" +
aTreatment.DoseRules + "'," + aTreatment.CenterId + "," + aTreatment.ServiceTakenId + ");";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlCommand.ExecuteNonQuery();
DbSqlConnection.Close();
MedicineStock aMedicineStock = new MedicineStock()
{
CenterId = aTreatment.CenterId,
MedicineId = aTreatment.MedicineId,
Quantity = aTreatment.Quantity
};
aMedicineStockGateway.DecreaseMedicineQuantity(aMedicineStock);
}
}
public Member Find(string number)
{
string query = "SELECT * FROM tbl_member WHERE number='" + number + "';";
DBSqlConnection = new SqlConnection(ConnectionString);
DBSqlConnection.Open();
DbSqlCommand = new SqlCommand(query, DBSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
if (DbSqlDataReader.HasRows)
{
Member aMember = new Member();
while (DbSqlDataReader.Read())
{
aMember.Id = Convert.ToInt32(DbSqlDataReader["id"]);
aMember.Number = DbSqlDataReader["number"].ToString();
}
DBSqlConnection.Close();
return(aMember);
}
else
{
DBSqlConnection.Close();
return(null);
}
}
public List <PatientHistory> GetAllHistory(string voterId)
{
SqlQuery = "SELECT * FROM view_patient_information WHERE voter_id='" + voterId + "'";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
List <PatientHistory> patientHistories = new List <PatientHistory>();
if (DbSqlDataReader.HasRows)
{
while (DbSqlDataReader.Read())
{
PatientHistory aPatientHistory = new PatientHistory();
aPatientHistory.VoterId = DbSqlDataReader["voter_id"].ToString();
aPatientHistory.Center = DbSqlDataReader["center_name"].ToString();
aPatientHistory.Date = Convert.ToDateTime(DbSqlDataReader["date"]);
aPatientHistory.Disease = DbSqlDataReader["disease_name"].ToString();
aPatientHistory.Doctor = DbSqlDataReader["doctor_name"].ToString();
aPatientHistory.Dose = DbSqlDataReader["dose"].ToString();
aPatientHistory.Rule = DbSqlDataReader["dose_rules"].ToString();
aPatientHistory.Medicine = DbSqlDataReader["medicine_name"] + "," + DbSqlDataReader["power"] +
DbSqlDataReader["type"];
aPatientHistory.Observation = DbSqlDataReader["observation"].ToString();
aPatientHistory.Quantity = (int)DbSqlDataReader["quantity"];
patientHistories.Add(aPatientHistory);
}
}
DbSqlConnection.Close();
return(patientHistories);
}
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 void Save(Patient aPatient)
{
int patientId;
DbSqlConnection = new SqlConnection(ConnectionString);
Patient patientFound = Find(aPatient.VoterId);
if (patientFound == null)
{
SqlQuery = "INSERT INTO tbl_patients VALUES('" + aPatient.VoterId + "');";
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlCommand.ExecuteNonQuery();
DbSqlConnection.Close();
SqlQuery = "SELECT MAX(id) FROM tbl_patients;";
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
DbSqlDataReader.Read();
patientId = (int)DbSqlDataReader[0];
DbSqlConnection.Close();
}
else
{
patientId = patientFound.Id;
}
DbSqlConnection.Open();
SqlQuery = "INSERT INTO tbl_service_taken VALUES(" + patientId + ");";
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlCommand.ExecuteNonQuery();
DbSqlConnection.Close();
}
public Disease Find(string name)
{
string query = "SELECT * FROM tbl_diseases WHERE name='" + name + "'";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(query, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
if (DbSqlDataReader.HasRows)
{
Disease aDisease = new Disease();
while (DbSqlDataReader.Read())
{
aDisease.Id = Convert.ToInt32(DbSqlDataReader["id"]);
aDisease.Name = DbSqlDataReader["name"].ToString();
aDisease.Description = DbSqlDataReader["description"].ToString();
aDisease.TreatementProcedure = DbSqlDataReader["treatement_procedure"].ToString();
aDisease.PreferedMedicine = DbSqlDataReader["prefered_medicine"].ToString();
}
DbSqlConnection.Close();
return(aDisease);
}
DbSqlConnection.Close();
return(null);
}
public int GetLastServiceTakenId()
{
SqlQuery = "SELECT MAX(id) FROM tbl_service_taken;";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
DbSqlDataReader.Read();
int serviceTakenId = Convert.ToInt32(DbSqlDataReader[0]);
DbSqlConnection.Close();
return(serviceTakenId);
}
public int GetTotalServiceTaken(string voterId)
{
SqlQuery =
"SELECT COUNT(*) FROM tbl_service_taken ser JOIN tbl_patients pat ON ser.patient_id=pat.id WHERE pat.voter_id='" +
voterId + "';";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
DbSqlDataReader.Read();
int total = (int)DbSqlDataReader[0];
DbSqlConnection.Close();
return(total);
}
public District Find(string name)
{
string query = "SELECT * FROM tbl_districts WHERE name='" + name + "';";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(query, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
District aDistrict = new District();
DbSqlDataReader.Read();
aDistrict.Id = Convert.ToInt32(DbSqlDataReader["id"]);
aDistrict.Name = DbSqlDataReader["name"].ToString();
DbSqlDataReader.Close();
DbSqlConnection.Close();
return(aDistrict);
}
public List <MedicineStockReport> GetAllStock(int centerId)
{
SqlQuery = "SELECT tbl_medicines.name medicinename, tbl_medicines.power as medicinePower,tbl_medicines.type as medicineType, tbl_medicines_of_centers.quantity medicinequantity FROM tbl_medicines JOIN tbl_medicines_of_centers ON tbl_medicines.id=tbl_medicines_of_centers.medicine_id WHERE tbl_medicines_of_centers.center_id='" + centerId + "' ";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
List <MedicineStockReport> aMedicineStockReports = new List <MedicineStockReport>();
while (DbSqlDataReader.Read())
{
MedicineStockReport aMedicineStockReport = new MedicineStockReport();
aMedicineStockReport.MedicineName = DbSqlDataReader["medicinename"] + "," + DbSqlDataReader["medicinePower"] + DbSqlDataReader["medicineType"];
aMedicineStockReport.MedicineQuantity = Convert.ToInt32(DbSqlDataReader["medicinequantity"]);
aMedicineStockReports.Add(aMedicineStockReport);
}
DbSqlConnection.Close();
return(aMedicineStockReports);
}
public List <District> GetDistricts()
{
List <District> districtList = new List <District>();
string query = "SELECT * FROM tbl_districts";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(query, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
while (DbSqlDataReader.Read())
{
District aDistrict = new District();
aDistrict.Id = Convert.ToInt32(DbSqlDataReader["id"]);
aDistrict.Name = DbSqlDataReader["name"].ToString();
districtList.Add(aDistrict);
}
DbSqlDataReader.Close();
DbSqlConnection.Close();
return(districtList);
}
public List <Thana> GetThanas(int districtId)
{
List <Thana> thanaList = new List <Thana>();
string query = "SELECT * FROM tbl_thanas WHERE district_id=" + districtId + " ORDER BY name;";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(query, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
while (DbSqlDataReader.Read())
{
Thana aThana = new Thana();
aThana.Id = Convert.ToInt32(DbSqlDataReader["id"]);
aThana.Name = DbSqlDataReader["name"].ToString();
aThana.DistrictId = Convert.ToInt32(DbSqlDataReader["district_id"]);
thanaList.Add(aThana);
}
DbSqlDataReader.Close();
DbSqlConnection.Close();
return(thanaList);
}
public List <Medicine> GetAll()
{
SqlQuery = "SELECT * FROM tbl_medicines;";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
List <Medicine> medicines = new List <Medicine>();
while (DbSqlDataReader.Read())
{
Medicine aMedicine = new Medicine();
aMedicine.Name = DbSqlDataReader["name"].ToString();
aMedicine.Id = Convert.ToInt32(DbSqlDataReader["id"]);
aMedicine.Power = DbSqlDataReader["power"].ToString();
aMedicine.Type = DbSqlDataReader["type"].ToString();
medicines.Add(aMedicine);
}
DbSqlConnection.Close();
return(medicines);
}
public List <BookNotReturne> NotReturnBookList()
{
BookNotReturne aBookNotReturne;
List <BookNotReturne> bookNotReturnList = new List <BookNotReturne>();
string query = "SELECT * FROM v_no_of_book_not_return";
DBSqlConnection = new SqlConnection(ConnectionString);
DBSqlConnection.Open();
DbSqlCommand = new SqlCommand(query, DBSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
while (DbSqlDataReader.Read())
{
aBookNotReturne = new BookNotReturne();
aBookNotReturne.MemberNumber = DbSqlDataReader["number"].ToString();
aBookNotReturne.MemberName = DbSqlDataReader["name"].ToString();
aBookNotReturne.NoOfBookNotReturned = (int)DbSqlDataReader["No_of_books_not_returned"];
bookNotReturnList.Add(aBookNotReturne);
}
DBSqlConnection.Close();
return(bookNotReturnList);
}
public Patient Find(string voterId)
{
SqlQuery = "SELECT * FROM tbl_patients WHERE voter_id='" + voterId + "';";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
Patient aPatient = new Patient();
if (DbSqlDataReader.HasRows)
{
while (DbSqlDataReader.Read())
{
aPatient.Id = (int)DbSqlDataReader["id"];
aPatient.VoterId = DbSqlDataReader["voter_id"].ToString();
}
DbSqlConnection.Close();
return(aPatient);
}
DbSqlConnection.Close();
return(null);
}
public List <Book> GetAll()
{
string query = "SELECT * FROM tbl_book;";
DBSqlConnection = new SqlConnection(ConnectionString);
DBSqlConnection.Open();
DbSqlCommand = new SqlCommand(query, DBSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
List <Book> books = new List <Book>();
while (DbSqlDataReader.Read())
{
Book aBook = new Book();
aBook.Id = Convert.ToInt32(DbSqlDataReader["id"]);
aBook.Title = DbSqlDataReader["title"].ToString();
aBook.Author = DbSqlDataReader["author"].ToString();
aBook.Publisher = DbSqlDataReader["publisher"].ToString();
books.Add(aBook);
}
DBSqlConnection.Close();
return(books);
}
public List <Center> Find(int districtId, int thanaId)
{
string query = "SELECT * FROM tbl_centers WHERE district_id=" + districtId + " AND thana_id=" + thanaId +
";";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(query, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
List <Center> centers = new List <Center>();
while (DbSqlDataReader.Read())
{
Center aCenter = new Center();
aCenter.Id = (int)DbSqlDataReader["id"];
aCenter.Name = DbSqlDataReader["name"].ToString();
aCenter.DistrictId = Convert.ToInt32(DbSqlDataReader["district_id"]);
aCenter.ThanaId = Convert.ToInt32(DbSqlDataReader["thana_id"]);
centers.Add(aCenter);
}
DbSqlConnection.Close();
return(centers);
}
public Center Find(string code, string password)
{
SqlQuery = "SELECT cen.id,cen.district_id,cen.name,cen.thana_id FROM tbl_center_login log JOIN tbl_centers cen ON log.center_id=cen.id WHERE log.code = '" + code + "' AND log.password= '******'";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
if (DbSqlDataReader.HasRows)
{
Center aCenter = new Center();
while (DbSqlDataReader.Read())
{
aCenter.Id = Convert.ToInt32(DbSqlDataReader["id"]);
aCenter.Name = DbSqlDataReader["name"].ToString();
aCenter.DistrictId = Convert.ToInt32(DbSqlDataReader["district_id"]);
aCenter.ThanaId = Convert.ToInt32(DbSqlDataReader["thana_id"]);
}
DbSqlConnection.Close();
return(aCenter);
}
DbSqlConnection.Close();
return(null);
}
public List <Disease> GetAll()
{
List <Disease> diseases = new List <Disease>();
SqlQuery = "SELECT * FROM tbl_diseases;";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
while (DbSqlDataReader.Read())
{
Disease aDisease = new Disease()
{
Id = (int)DbSqlDataReader["id"],
Description = DbSqlDataReader["description"].ToString(),
Name = DbSqlDataReader["name"].ToString(),
PreferedMedicine = DbSqlDataReader["prefered_medicine"].ToString(),
TreatementProcedure = DbSqlDataReader["treatement_procedure"].ToString()
};
diseases.Add(aDisease);
}
DbSqlConnection.Close();
return(diseases);
}
public Book Find(int id)
{
string query = "SELECT * FROM tbl_book WHERE id=" + id + ";";
DBSqlConnection = new SqlConnection(ConnectionString);
DBSqlConnection.Open();
DbSqlCommand = new SqlCommand(query, DBSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
if (DbSqlDataReader.HasRows)
{
Book aBook = new Book();
while (DbSqlDataReader.Read())
{
aBook.Id = Convert.ToInt32(DbSqlDataReader["id"]);
aBook.Title = DbSqlDataReader["title"].ToString();
aBook.Author = DbSqlDataReader["author"].ToString();
aBook.Publisher = DbSqlDataReader["publisher"].ToString();
}
DBSqlConnection.Close();
return(aBook);
}
DBSqlConnection.Close();
return(null);
}
public List <Book> GetSelectedBookList(string number)
{
Book aBook;
List <Book> bookList = new List <Book>();
string query =
"SELECT tbl_member.number memberNumber, tbl_book.title bookTitle, tbl_book.id bookId, tbl_book.author author, tbl_book.publisher publisher, tbl_borrow.is_returned isReturned FROM tbl_book JOIN tbl_borrow ON tbl_book.id=tbl_borrow.book_id JOIN tbl_member ON tbl_member.id=tbl_borrow.member_id WHERE tbl_member.number='" + number + "' AND tbl_borrow.is_returned='False'";
DBSqlConnection = new SqlConnection(ConnectionString);
DBSqlConnection.Open();
DbSqlCommand = new SqlCommand(query, DBSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
while (DbSqlDataReader.Read())
{
aBook = new Book();
aBook.Id = (int)DbSqlDataReader["bookId"];
aBook.Title = DbSqlDataReader["bookTitle"].ToString();
aBook.Author = DbSqlDataReader["author"].ToString();
aBook.Publisher = DbSqlDataReader["publisher"].ToString();
bookList.Add(aBook);
}
DBSqlConnection.Close();
return(bookList);
}
public MedicineStock Find(MedicineStock aMedicineStock)
{
SqlQuery = "SELECT * FROM tbl_medicines_of_centers WHERE center_id=" + aMedicineStock.CenterId +
" AND medicine_id=" + aMedicineStock.MedicineId + ";";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
if (DbSqlDataReader.HasRows)
{
MedicineStock aStock = new MedicineStock();
while (DbSqlDataReader.Read())
{
aStock.Id = Convert.ToInt32(DbSqlDataReader["id"]);
aStock.CenterId = Convert.ToInt32(DbSqlDataReader["center_id"]);
aStock.MedicineId = Convert.ToInt32(DbSqlDataReader["medicine_id"]);
aStock.Quantity = Convert.ToInt32(DbSqlDataReader["quantity"]);
}
DbSqlConnection.Close();
return(aStock);
}
DbSqlConnection.Close();
return(null);
}
public List <Doctor> GetDoctorsByCenter(int centerId)
{
List <Doctor> doctors = new List <Doctor>();
SqlQuery = "SELECT * FROM tbl_doctors WHERE center_id=" + centerId + ";";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
while (DbSqlDataReader.Read())
{
Doctor aDoctor = new Doctor()
{
Id = (int)DbSqlDataReader["id"],
Name = DbSqlDataReader["name"].ToString(),
CenterId = (int)DbSqlDataReader["center_id"],
Degree = DbSqlDataReader["degree"].ToString(),
Specialization = DbSqlDataReader["specialization"].ToString()
};
doctors.Add(aDoctor);
}
DbSqlConnection.Close();
return(doctors);
}
public Center Find(Center aCenter)
{
string query = "SELECT * FROM tbl_centers WHERE name='" + aCenter.Name + "' AND district_id='" + aCenter.DistrictId + "' AND thana_id='" + aCenter.ThanaId + "'";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(query, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
if (DbSqlDataReader.HasRows)
{
Center center = new Center();
while (DbSqlDataReader.Read())
{
center.Id = (int)DbSqlDataReader["id"];
center.Name = DbSqlDataReader["name"].ToString();
center.DistrictId = Convert.ToInt32(DbSqlDataReader["district_id"]);
center.ThanaId = Convert.ToInt32(DbSqlDataReader["thana_id"]);
}
DbSqlConnection.Close();
return(center);
}
DbSqlConnection.Close();
return(null);
}
public Medicine Find(string name)
{
SqlQuery = "SELECT * FROM tbl_medicines WHERE name='" + name + "'";
DbSqlConnection = new SqlConnection(ConnectionString);
DbSqlConnection.Open();
DbSqlCommand = new SqlCommand(SqlQuery, DbSqlConnection);
DbSqlDataReader = DbSqlCommand.ExecuteReader();
if (DbSqlDataReader.HasRows)
{
Medicine aMedicine = new Medicine();
while (DbSqlDataReader.Read())
{
aMedicine.Name = DbSqlDataReader["name"].ToString();
aMedicine.Id = Convert.ToInt32(DbSqlDataReader["id"]);
aMedicine.Power = DbSqlDataReader["power"].ToString();
aMedicine.Type = DbSqlDataReader["type"].ToString();
}
DbSqlConnection.Close();
return(aMedicine);
}
DbSqlConnection.Close();
return(null);
}
