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