/// <summary> /// get a list of all payment data /// </summary> /// <param name="data"></param> /// <returns></returns> public List<Data> list(Data data) { String query = @"SELECT * FROM history H, GeneralPayment G WHERE G.ID = H.payId AND H.patientId = @patientId AND H.payState = 0 AND G.ID > 0 ORDER BY H.date"; String medicineQuery = @"SELECT *, (M.Quantity * G.Price) AS T_Price FROM Medicine M, GeneralPayment G, history H WHERE G.ID = M.ItemId AND M.historyId = H.historyId AND H.patientId = @patientId AND M.state = 0 ORDER BY H.date"; String insuranceQuery = "SELECT * FROM Insurance I, Users U WHERE I.insuranceId = U.InsuranceId AND U.UserID = @patientId"; List<Data> result = new List<Data>(); dbc = new DBC(); dbc.open(); result.Add(dbc.select(query, data)); result.Add(dbc.select(medicineQuery, data)); result.Add(dbc.select(insuranceQuery, data)); dbc.close(); return result; }
/// <summary> /// create a new patient /// </summary> /// <param name="data"></param> /// <returns></returns> public bool createPatient(Data data) { int result = 0; String query = @"INSERT INTO Users (UserID, UserFirstName, UserMiddleName, UserSurName, TitleID, GenderID, Occupation, MaritalID, Address, DOB, StateCode, PostCode, Nationality, PhoneNumber, Email, JobCode, PatientMotherLastName, PatientMotherFirstName, PatientFatherLastName, PatientFatherFirstName, insuranceId) VALUES (@UserID, @UserFirstName, @UserMiddleName, @UserSurName, @TitleID, @GenderID, @Occupation, @MaritalID, @Address, @DOB, @StateCode, @PostCode, @Nationality, @PhoneNumber, @Email, @JobCode, @PatientMotherLastName, @PatientMotherFirstName, @PatientFatherLastName, @PatientFatherFirstName, @insuranceId)"; dbc = new DBC(); dbc.open(); result = dbc.update(query, data); result = dbc.update("INSERT INTO UsersLogIn (UserID, Password) VALUES (@UserID, @Password)", data); dbc.close(); dbc = null; return result != 0 ? true : false; }
/// <summary> /// select patient /// </summary> /// <param name="field"></param> /// <param name="value"></param> /// <param name="data"></param> /// <returns></returns> public Data list(Data data) { String today = DateTime.Now.Date.ToString().Substring(0, 10); data.add("today", today); String query = @"SELECT *, (SELECT COUNT(*) FROM NursesForm N WHERE N.UserID = P.PatientID AND N.state = 0 AND N.date = convert(datetime, @today, 103) )AS Observation FROM Room R, patientRoom P, Users U WHERE P.Room = R.RoomID AND R.UserId = @NurseID AND U.UserID = P.PatientID AND P.StartDate <= convert(datetime, @today, 103) AND P.EndDate >= convert(datetime, @today, 103) ORDER BY P.PatientID ASC"; Data result = select(query, data); int count = result.Count; for (int i = 0; i < count; i++) { result.add(i, "StartDate", result.getString(i, "StartDate").Substring(0,10)); result.add(i, "EndDate", result.getString(i, "EndDate").Substring(0, 10)); } return result; }
/// <summary> /// it will be deleted /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool insert(Data data) { /* if (!Text.isAble(data.getString("UserName"), 32)) { return false; } if (!Text.isAble(data.getString("FirstName"), 32)) { return false; } if (!Text.isAble(data.getString("SurName"), 32)) { return false; } if (!Text.isAble(data.getString("Email"), 256)) { return false; } Data param = new Data(); param.add("UserId", data.get("UserName")); param.add("FirstName", data.get("FirstName")); param.add("SurName", data.get("SurName")); param.add("MiddleName", data.get("MiddleName")); //param.add("DOB", data.get("DOB")); param.add("Email", data.get("Email")); updateSP("registration", data); return true; */ return true; }
/// <summary> /// set query to select patient ID and nurse ID. Search for patient. /// </summary> /// <param name="field"></param> /// <param name="value"></param> /// <param name="data"></param> /// <returns></returns> public Data list(String field, String value,Data data) { String query = "Select * From history A, Users B WHERE A.patientId = B.UserID AND A.patientId = @pId AND A.staffId = @NurseID AND status = '0' <SEARCH> <DATE> ORDER BY historyId DESC"; String date = data.getString("date"); if (field.Equals("")) { query = query.Replace("<SEARCH>", ""); } else if (field.Equals("memo")) { query = query.Replace("<SEARCH>", "AND memo LIKE '%" + value + "%'"); } else if (field.Equals("patientId")) { query = query.Replace("<SEARCH>", "AND patientId LIKE '%" + value + "%'"); } if (date.Equals("")) { query = query.Replace("<DATE>", ""); } else { query = query.Replace("<DATE>", "AND CONVERT(VARCHAR(10), A.date, 103) = '<DATE>'".Replace("<DATE>", date)); } return select(query, data); }
/// <summary> /// get a medicine data /// </summary> /// <param name="data"></param> /// <returns></returns> public override Data view(Data data) { String query = @"SELECT * FROM Medicine M, GeneralPayment G WHERE G.ID = M.ItemId AND historyId = @HistoryId AND STATE = 0 "; return select(query, data); }
/// <summary> /// get a report for items /// </summary> /// <param name="data"></param> /// <returns></returns> public Data getItemReport(Data data) { String startDate = data.getString("startDate"); String endDate = data.getString("endDate"); String today = DateTime.Now.Date.ToString().Substring(0,10); if ("".Equals(startDate)) { data.add("startDate",today); } if ("".Equals(startDate)) { data.add("endDate", today); } String query = @"SELECT G.Item, G.Price, COUNT(M.ItemId) AS How_many_times, SUM(G.Price) AS Total FROM Medicine M, GeneralPayment G, history H WHERE H.historyId = M.historyId AND M.ItemId = G.ID AND M.state < 9 AND H.date >= CONVERT(datetime, @startDate, 103) AND H.date <= CONVERT(datetime, @endDate, 103) GROUP BY G.Item, G.price ORDER BY G.Item ASC"; return select(query, data); }
/// <summary> /// update a room data /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool update(Data data) { DBC dbc = new DBC(); dbc.open(); dbc.update(@"UPDATE Room SET Type = @RoomType, UserId = @RoomOwner, Beds = @Beds WHERE RoomID = @RoomID", data); dbc.close(); return true; }
/// <summary> /// get data with paging /// </summary> /// <param name="query"></param> /// <param name="data"></param> /// <param name="index"></param> /// <returns></returns> protected Data page(String query, Data data, String index) { dbc = new DBC(); dbc.open(); result = dbc.page(query, data, index); dbc.close(); dbc = null; return result; }
/// <summary> /// get data with paging using stored procedure /// </summary> /// <param name="query"></param> /// <param name="data"></param> /// <param name="index"></param> /// <returns></returns> protected Data pageSP(String spName, String spCount, Data data) { dbc = new DBC(); dbc.open(); result = dbc.pageSP(spName, spCount, data); dbc.close(); dbc = null; return result; }
/// <summary> /// get data from database /// </summary> /// <param name="query"></param> /// <param name="data"></param> /// <returns></returns> protected Data select(String query, Data data) { dbc = new DBC(); dbc.open(); result = dbc.select(query, data); dbc.close(); dbc = null; return result; }
/// <summary> /// select data from database using stored procedure /// </summary> /// <param name="spName"></param> /// <param name="data"></param> /// <returns></returns> protected Data selectSP(String spName, Data data) { dbc = new DBC(); dbc.open(); result = dbc.selectSP(spName, data); dbc.close(); dbc = null; return result; }
/// <summary> /// update an item /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool update(Data data) { dbc = new DBC(); dbc.open(); dbc.update(@"UPDATE GeneralPayment SET Item = @ItemTextBox, Type = @TypeDropDownList, Price = @PriceTextBox, insuranceState = @InsuranceStateTextBox WHERE ID = @itemID", data); dbc.close(); return true; }
/// <summary> /// get a surgery data /// </summary> /// <param name="data"></param> /// <returns></returns> public override Data view(Data data) { Data result = select("SELECT * FROM DoctorsSurgery A, Users B WHERE A.UserID = B.UserID AND A.SurgeryID = @surgeryId", data); int count = result.Count; for (int i = 0; i < count; i++) { result.add(i, "UserDOS", result.getString(i, "UserDOS").Substring(0, 10)); } return result; }
/// <summary> /// get a list of new patients /// </summary> /// <param name="data"></param> /// <returns></returns> public Data patientlist(Data data) { //return select("Select * From Users WHERE JobCode = 0 ORDER BY UserID DESC", data); String query = @"SELECT H.historyId, H.patientId FROM history H, Users U WHERE U.UserID = H.staffId AND H.status = 0 AND H.staffId = @CaptID ORDER BY H.patientId ASC"; return select(query, data); }
/// <summary> /// update a sergery data /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool update(Data data) { dbc = new DBC(); dbc.open(); dbc.update(@"UPDATE DoctorsSurgery SET UserDOS = convert(datetime, @DOS, 103), Memo = @Memo, UserROS = @ROS, UserSD = @surgery_description, UserSSE = @surgeryse WHERE SurgeryID = @SurgeryID", data); dbc.update(@"UPDATE history SET Memo = @Memo WHERE type = 2 AND cntId = @SurgeryID", data); dbc.close(); return true; }
/// <summary> /// delete a surgery data /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool delete(Data data) { dbc = new DBC(); dbc.open(); dbc.update(@"UPDATE DoctorsOrder SET state = '9' WHERE SurgeryID = @SurgeryID", data); dbc.update(@"UPDATE history SET status = '9' WHERE type = 2 AND cntId = @SurgeryID", data); dbc.close(); return true; }
/// <summary> /// get a list of new patients /// </summary> /// <param name="data"></param> /// <returns></returns> public Data newPatientsList(Data data) { //return select("Select UserID, Memo From DoctorsOrder WHERE UserStay = 'True'", data); String query = @"SELECT H.historyId, H.patientId, H.memo FROM history H, Users U WHERE U.UserID = H.staffId AND H.status = 0 AND H.staffId = @nurseId ORDER BY H.patientId ASC"; return select(query, data); }
/// <summary> /// delete a patient from a room /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool delete(Data data) { String today = DateTime.Now.Date.ToString().Substring(0, 10); data.add("today", today); String query = "UPDATE PatientRoom SET EndDate = convert(datetime, @today, 103) WHERE Room = @RoomID AND PatientID = @PatientID"; DBC dbc = new DBC(); dbc.open(); dbc.update(query, data); //dbc.update(@"DELETE From PatientRoom WHERE PatientID = @PatientID", data); dbc.close(); return true; }
/// <summary> /// create a new X-ray data /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool create(Data data) { String getId = "SELECT COUNT(*) + 1 AS NEW_ID FROM XRay"; String query = "INSERT INTO XRay (XRayID, TestResultID, name, path) VALUES (@XRayID, @TestResultID, @name, @path)"; dbc = new DBC(); dbc.open(); String id = dbc.select(getId, new Data()).getString("NEW_ID"); data.add("XRayID", id); dbc.update(query, data); dbc.close(); return true; }
/// <summary> /// update nurse form /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool update(Data data) { dbc = new DBC(); dbc.open(); dbc.update(@"UPDATE NursesForm SET head = @head, ear = @ear, drum = @drum, nose = @nose, sinus = @sinus, mouth = @mouth, eye = @eye, opthal = @opthal, pupil = @pupil, ocular = @ocular, lung = @lung, heart = @heart, vascular = @vascular, abdomen = @abdomen ,memo = @memo WHERE id = @ObservationID", data); dbc.update(@"UPDATE history SET memo = @memo WHERE type = 3 AND cntId = @ObservationID", data); dbc.close(); return true; }
/// <summary> /// delete data from nurse form /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool delete(Data data) { dbc = new DBC(); dbc.open(); dbc.update(@"UPDATE history SET status = '9' WHERE cntId = @ObservationID AND type = 3", data); //dbc.update("UPDATE NursesForm SET state = '9' WHERE id = @ObservationID", data); dbc.update("UPDATE NursesForm SET state = '9' WHERE date = convert(datetime, @date, 103) AND UserID = @UserID", data); dbc.close(); return true; }
/// <summary> /// get a patients data /// </summary> /// <param name="data"></param> /// <returns></returns> public override Data view(Data data) { string getCntId = ("SELECT cntId FROM history WHERE historyId = @historyId"); string query = "SELECT * FROM DoctorsOrder A, Users B WHERE A.UserID = B.UserID AND A.orderId = @cntId"; dbc = new DBC(); dbc.open(); string cntId = dbc.select(getCntId, data).getString("cntId"); data.add("cntId", cntId); Data result = dbc.select(query, data); dbc.close(); return result; }
/// <summary> /// update a test data /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool update(Data data) { dbc = new DBC(); dbc.open(); dbc.update(@"UPDATE DoctorsTestResult SET UserOR1 = @OR1, UserOR2 = @OR2, UserOR3 =@OR3, UserOR4 = @OR4, UserOR5 = @OR5, UserOR6 = @OR6, Memo = @Memo, DOT = CONVERT(datetime, @DOT, 103), UserORT1 = @ORT1,UserORT2 = @ORT2, UserORT3 = @ORT3, UserORT4 = @ORT4, UserORT5 = @ORT5, UserORT6 = @ORT6,other_abnormalities = @other_abnormalities WHERE TestResultID = @TestResultID", data); dbc.update(@"UPDATE history SET Memo = @Memo WHERE type = 1 AND cntId = @TestResultID", data); dbc.close(); return true; }
/// <summary> /// get a list of payment items /// </summary> /// <param name="value"></param> /// <param name="data"></param> /// <returns></returns> public Data list(String value, Data data) { String query = "SELECT * FROM GeneralPayment WHERE Type = @Type <SEARCH> ORDER BY Item ASC"; if (value.Equals("")) { query = query.Replace("<SEARCH>", ""); } else { query = query.Replace("<SEARCH>", "AND Item LIKE '%" + value + "%'"); } return select(query, data); }
/// <summary> /// select patient /// </summary> /// <param name="field"></param> /// <param name="value"></param> /// <param name="data"></param> /// <returns></returns> public Data list(Data data) { string today = DateTime.Now.Date.ToString().Substring(0, 10); data.add("today", today); String query = @"Select R.*, (SELECT COUNT(*) FROM PatientRoom WHERE Room = R.RoomID AND StartDate <= convert(datetime, @today, 103) AND EndDate >= convert(datetime, @today, 103)) AS P_COUNT FROM Room R WHERE DeptID = 'Oncology' ORDER BY RoomID DESC"; return select(query, data); }
/// <summary> /// create a medicine data /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool create(Data data) { String getId = "SELECT COUNT(*) + 1 AS NEW_ID FROM Medicine "; String query = @"INSERT INTO Medicine (medicineId, UserID, historyId, Quantity, itemId) VALUES (@medicineId, @UserID, @historyId, @Quantity, @itemId)"; dbc = new DBC(); dbc.open(); Object id = dbc.select(getId, new Data()).get("NEW_ID"); data.add("medicineId", id); dbc.update(query, data); dbc.close(); return true; }
/// <summary> /// create a new insurance data /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool create(Data data) { String getId = "SELECT MAX(insuranceId) + 1 AS NEW_ID FROM Insurance"; String query = @"INSERT INTO Insurance (insuranceId, insuranceName, rate, state) VALUES (@insuranceId, @insuranceName, @rate, 0)"; dbc = new DBC(); dbc.open(); Object id = dbc.select(getId, new Data()).get("NEW_ID"); data.add("insuranceId", id); dbc.update(query, data); dbc.close(); return true; }
/// <summary> /// create a new item /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool create(Data data) { String getId = "SELECT MAX(ID) + 1 AS NEW_ID FROM GeneralPayment"; String query = @"INSERT INTO GeneralPayment (ID, Item, Type, Price) VALUES (@NEW_ID, @ItemTextBox, @TypeDropDownList, @PriceTextBox)"; dbc = new DBC(); dbc.open(); Object id = dbc.select(getId, new Data()).get("NEW_ID"); data.add("NEW_ID", id); dbc.update(query, data); dbc.close(); return true; }
/// <summary> /// get a form vlaues from the request /// </summary> /// <param name="request"></param> /// <returns></returns> public static Data Form(HttpRequest request) { Data parameters = new Data(); int count = request.Form.Count; int pCount = 0; String key = null; String[] values = null; for (int i = 0; i < count; i++) { // get a list of keys key = request.Form.GetKey(i); if (key == null) { continue; } if (key.IndexOf("__") == 0) { continue; } int keyIndex = key.LastIndexOf("$") + 1; if (keyIndex > 0) { key = key.Substring(keyIndex, key.Length - keyIndex); } if (key.Length < 1) { continue; } //get a value using keys values = request.Form.GetValues(i); pCount = values.Length; if (values != null) { if (key.Equals("now")) { parameters.Now = int.Parse(values[0]); } else { for (int j = 0; j < pCount; j++) { parameters.add(j, key, values[j]); } } } } return parameters; }