/// <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 surgery data /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool create(Data data) { dbc = new DBC(); dbc.open(); Object SurgeryID = dbc.select("SELECT COUNT(SurgeryID) + 1 AS MAX_ID FROM DoctorsSurgery", data).get("MAX_ID"); data.add("SurgeryID", SurgeryID); dbc.update(@"INSERT INTO DoctorsSurgery (DoctorID, SurgeryID, type, UserID, UserDOS, UserROS, UserSD, UserSSE, Memo, state) VALUES (@DoctorID, @SurgeryID, @type, @UserID, convert(datetime, @DOS, 103), @ROS, @surgery_description, @surgeryse, @Memo, 0)", data); data.add("historyId", dbc.select(@"SELECT MAX(historyId) +1 AS MAX_ID FROM history", data).get("MAX_ID")); // based on the type, patient's payment price will be decided. dbc.update(@"INSERT INTO history (patientId, historyId, staffId, memo, type, cntId, date, payId) VALUES (@UserID, @historyId, @DoctorID, @Memo, 2, @SurgeryID, CONVERT(datetime, @DOS, 103), @type)", data); dbc.close(); return true; }
/// <summary> /// create new data for nurse form and update patient history /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool create(Data data) { String today = DateTime.Now.Date.ToString().Substring(0, 10); data.add("today", today); dbc = new DBC(); dbc.open(); Object ID = dbc.select("SELECT COUNT(id) + 1 AS MAX_ID FROM NursesForm", data).get("MAX_ID"); data.add("id", ID); dbc.update(@"INSERT INTO NursesForm (UserID, id, head, ear, drum, nose, sinus, mouth, eye, opthal, pupil, ocular, lung, heart, vascular, abdomen, memo, date, state) VALUES (@UserID, @id, @head, @ear, @drum, @nose, @sinus, @mouth, @eye, @opthal, @pupil, @ocular, @lung, @heart, @vascular, @abdomen, @memo, convert(datetime, @today, 103), 0)", data); data.add("historyId", dbc.select(@"SELECT MAX(historyId) +1 AS MAX_ID FROM history", data).get("MAX_ID")); dbc.update(@"INSERT INTO history (patientId, historyId, staffId, memo, type, cntId, date, payId) VALUES (@UserID, @historyId, @nurseID, @memo, 3, @id, getDate(), 14)", data); dbc.close(); return true; }
/// <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> /// 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> /// create a new test /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool create(Data data) { dbc = new DBC(); dbc.open(); Object TestResultId = dbc.select("SELECT COUNT(TestResultID) + 1 AS MAX_ID FROM DoctorsTestResult", data).get("MAX_ID"); String XrayMan = dbc.select("SELECT UserID FROM Users WHERE JobCOde = 17", new Data()).getString("UserID"); String MRIMan = dbc.select("SELECT UserID FROM Users WHERE JobCOde = 21", new Data()).getString("UserID"); data.add("TestResultID", TestResultId); data.add("XrayMan", XrayMan); data.add("MRIMan", MRIMan); dbc.update(@"INSERT INTO DoctorsTestResult (DoctorID, TestResultID, UserID, UserOR1, UserOR2, UserOR3, UserOR4, UserOR5, UserOR6,UserORT1,UserORT2,UserORT3,UserORT4,UserORT5,UserORT6,other_abnormalities, DOT, Memo, state) VALUES (@DoctorID, @TestResultID, @UserID, @OR1, @OR2, @OR3, @OR4, @OR5, @OR6,@ORT1, @ORT2, @ORT3, @ORT4, @ORT5, @ORT6, @other_abnormalities, CONVERT(datetime, @DOT, 103), @Memo, 0)", data); data.add("historyId", dbc.select(@"SELECT MAX(historyId) +1 AS MAX_ID FROM history", data).get("MAX_ID")); dbc.update(@"INSERT INTO history (patientId, historyId, staffId, memo, type, cntId, date, payId) VALUES (@UserID, @historyId, @DoctorID, @Memo, 1, @TestResultID, CONVERT(datetime, @DOT, 103), 12)", data); //order Xray if ("True".Equals(data.getString("Xray"))) { String xRayMemo = "[" + data.getString("UserID") + "] FROM " + data.getString("DoctorID") + "\n" + data.getString("Memo"); data.add("xRayMemo", xRayMemo); data.add("xHistoryId", int.Parse(data.getString("historyId")) + 1); dbc.update(@"INSERT INTO history (patientId, historyId, staffId, memo, type, cntId, date, payId) VALUES (@UserID, @xHistoryId, @XrayMan, @xRayMemo, -1, @TestResultID, CONVERT(datetime, @DOT, 103), 17)", data); } //order MRI if ("True".Equals(data.getString("MRI"))) { String xRayMemo = "[" + data.getString("UserID") + "] FROM " + data.getString("DoctorID") + "\n" + data.getString("Memo"); data.add("xRayMemo", xRayMemo); data.add("mHistoryId", int.Parse(data.getString("xHistoryId")) + 1); dbc.update(@"INSERT INTO history (patientId, historyId, staffId, memo, type, cntId, date, payId) VALUES (@UserID, @mHistoryId, @MRIMan, @xRayMemo, -1, @TestResultID, CONVERT(datetime, @DOT, 103), 18)", 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> /// 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 book /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool create(Data data) { String getNewId = "SELECT COUNT(historyId) + 1 AS MAX_ID FROM history"; String query = @"INSERT INTO history (historyId, patientId, staffId, memo, type, cntId,date) VALUES(@historyId, @patientId, @staffId, @memo, -1, -1, CONVERT(datetime, @date, 103))"; dbc = new DBC(); dbc.open(); Object id = dbc.select(getNewId, new Data()).get("MAX_ID"); data.add("historyId", id); dbc.update(query, data); dbc.close(); dbc.open(); return true; }
/// <summary> /// create a relationship between patient and room /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool create(Data data) { String getPId = "SELECT patientId FROM history WHERE historyId = @historyId"; DBC dbc = new DBC(); dbc.open(); String pateintId = dbc.select(getPId, data).getString("patientId"); data.add("PatientID", pateintId); dbc.update(@"INSERT INTO PatientRoom (PatientID, Room, BedNumber, Level, DeptName, StartDate, EndDate) VALUES (@PatientID, @Room, @BedNumber, @Level, @DeptName, convert(datetime, @StartDate, 103), convert(datetime, @EndDate, 103))", data); dbc.update(@"UPDATE history SET status = 1 WHERE historyId = @historyId" , data); dbc.close(); return true; }
/// <summary> /// create a report for payment /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool create(Data data) { String date = DateTime.Now.Date.ToString().Substring(0, 10); data.add("date", date); data.add("total", float.Parse(data.getString("total"))); String getId = "SELECT COUNT(*) + 1 AS NEW_ID FROM PaymentReport"; String query = "UPDATE history SET payState = 1 WHERE PatientID = @PatientID"; String mQuery = "UPDATE Medicine SET state = 1 WHERE historyId in (SELECT historyId FROM history WHERE PatientID = @PatientID)"; String createQuery = @"INSERT INTO PaymentReport (PaymentReportID, UserID, reportFilePath, total, date) VALUES (@PaymentReportID, @PatientID, @reportFilePath, @total, convert(datetime, @date, 103))"; dbc = new DBC(); dbc.open(); Object id = dbc.select(getId, new Data()).get("NEW_ID"); data.add("PaymentReportID", id); dbc.update(query, data); dbc.update(mQuery, data); dbc.update(createQuery, data); dbc.close(); return true; }
/// <summary> /// create a new order data /// </summary> /// <param name="data"></param> /// <returns></returns> public override bool create(Data data) { String medication = data.getString("UsermedCheck"); String UserNAA = data.getString("UserNAA"); String UserStay = data.getString("UserStay"); String doctorType = data.getString("JobCode"); String payId = "11"; DateTime xToday = DateTime.Now.Date; if ("19".Equals(doctorType)) { payId = "15"; } else if ("20".Equals(doctorType)) { payId = "16"; } data.add("PDate", xToday.ToString().Substring(0, 10)); dbc = new DBC(); dbc.open(); Object orderId = dbc.select("SELECT COUNT(orderId) + 1 AS MAX_ID FROM DoctorsOrder", data).get("MAX_ID"); data.add("orderId", orderId); dbc.update(@"INSERT INTO DoctorsOrder (DoctorID, UserID, orderId, UserOD, UsermedCheck, Usermed, Usernas, Userdosage, Userside, UserNAA, Memo, state) VALUES (@DoctorID, @UserID, @orderId, @UserOD, @UsermedCheck, @Usermed, @Usernas, @Userdosage, @Userside, @UserNAA, @Memo, 0)", data); data.add("historyId", dbc.select(@"SELECT MAX(historyId) +1 AS MAX_ID FROM history", data).get("MAX_ID")); data.add("PDate", xToday.ToString().Substring(0, 10)); dbc.update(@"INSERT INTO history (patientId, historyId, staffId, memo, type, cntId, date, payId) VALUES (@UserID, @historyId, @DoctorID, @Memo, 0, @orderId, CONVERT(datetime, @PDate, 103), " + payId + ")", data); // create a new appointment if ("True".Equals(UserNAA)) { data.add("historyId", int.Parse(data.getString("historyId")) + 1); dbc.update(@"INSERT INTO history (patientId, historyId, staffId, memo, type, cntId, date) VALUES (@UserID, @historyId, @DoctorID, @Memo, -1, @orderId, CONVERT(datetime, @date, 103))", data); } // order medicine if ("True".Equals(medication)) { String getPharmarcist = "SELECT UserID FROM Users WHERE JobCode = 16"; String pharmacist = dbc.select(getPharmarcist, data).getString("UserID"); data.add("staffID", pharmacist); data.add("historyId", int.Parse(data.getString("historyId")) + 1); dbc.update(@"INSERT INTO history (patientId, historyId, staffId, memo, type, cntId, date) VALUES (@UserID, @historyId, @staffID, @Usermed, -1, @orderId, CONVERT(datetime, @PDate, 103))", data); } // order user to stay hospital for a while if ("True".Equals(UserStay)) { String getNurse = "SELECT UserID FROM Users WHERE JobCode = 18"; String nurse = dbc.select(getNurse, data).getString("UserID"); data.add("staffID", nurse); data.add("historyId", int.Parse(data.getString("historyId")) + 1); dbc.update(@"INSERT INTO history (patientId, historyId, staffId, memo, type, cntId, date) VALUES (@UserID, @historyId, @staffID, @Memo, -1, @orderId, CONVERT(datetime, @PDate, 103))", data); } dbc.close(); return true; }
/// <summary> /// get a default data to create a new user /// </summary> /// <param name="data"></param> /// <returns></returns> public List<Data> defaultData(Data data) { DBC dbc = new DBC(); List<Data> list = new List<Data>(); dbc.open(); list.Add(dbc.select("SELECT * FROM Occupation", data)); list.Add(dbc.select("SELECT * FROM Marital", data)); list.Add(dbc.select("SELECT * FROM Roles", data)); list.Add(dbc.select("SELECT * FROM Countries", data)); list.Add(dbc.select("SELECT * FROM Title", data)); list.Add(dbc.select("SELECT * FROM Gender", data)); list.Add(dbc.select("SELECT * FROM AusState", data)); list.Add(dbc.select("SELECT * FROM insurance", data)); dbc.close(); return list; }
/// <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; }