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