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