/// <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>
/// 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);
}
protected void onChange(object sender, EventArgs e)
{
Data data = new Data();
data.add("doctorId", UserInfo.getId(Session));
data.add("pID", Param.get("pId"));
data.add("pre", pre.SelectedValue);
DoctorBiz doctor = new DoctorBiz();
Data result = doctor.list(Param.getString("searchFiled"), Param.getString("searchValue"), data);
pId.Value = data.getString("pID");
DoctorID.Value = data.getString("doctorId");
int count = result.Count;
for (int i = 0; i < count; i++)
{
result.add(i, "date", result.getString(i, "date").Substring(0, 10));
}
patienthistory.DataSource = result.Source;
patienthistory.DataBind();
userNameLabel.Text = result.getString("UserSurName") + "," + result.getString("UserFirstName") + ",ID:" + result.getString("UserID");
}
protected override void Fire(object sender, EventArgs e)
{
if (!UserInfo.isDoctor(Session))
{
alertAndGoback("you are not a doctor. Please login");
return;
}
Data data = new Data();
data.add("doctorId", UserInfo.getId(Session));
data.add("pID", Param.get("pId"));
DoctorBiz doctor = new DoctorBiz();
Data result = doctor.list(Param.getString("searchFiled"), Param.getString("searchValue"), data);
pId.Value = data.getString("pID");
DoctorID.Value = data.getString("doctorId");
patienthistory.DataSource = result.Source;
patienthistory.DataBind();
userNameLabel.Text = result.getString("UserSurName") + "," + result.getString("UserFirstName") + ",ID:" + result.getString("UserID");
}
protected override void Fire(object sender, EventArgs e)
{
if (!UserInfo.isDoctor(Session))
{
alertAndGoback("you are not a doctor. Please login");
return;
}
DateTime xToday = DateTime.Now.Date;
DOT.Text = xToday.ToString().Substring(0, 10);
//UserID.Text = Param.getString("pId");
Data result = new Data();
DBC dbc = new DBC();
Data data = new Data();
data.add("pID", Param.get("pId"));
UserID.Value = Param.getString("pId");
TestResultBiz biz = new TestResultBiz();
result = biz.view(data);
userNameLabel.Text = result.getString("UserSurName") + "," + result.getString("UserFirstName") + ",ID:" + result.getString("UserID");
}
/// <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;
}
public Data getPatientAndService(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 H.patientId, G.Item AS Type,
G.Price,
G.Price * (100 - I.rate) / 100 AS Patient_Fee,
G.Price * I.rate / 100 AS Insurance_Fee,
I.InsuranceName, I.rate,
H.date
FROM GeneralPayment G, history H, Users U, Insurance I
WHERE G.ID = H.PayId
AND U.UserID = H.patientId
AND I.insuranceId = U.InsuranceId
AND H.payId > 0
AND H.date >= CONVERT(datetime, @startDate, 103)
AND H.date <= CONVERT(datetime, @endDate, 103)
GROUP BY H.patientId, G.Item, G.Price, I.InsuranceName, I.rate, H.date
ORDER BY Date DESC";
Data result = select(query, data);
int count = result.Count;
for (int i = 0; i < count; i++)
{
result.add(i, "date", result.getString(i, "date").Substring(0, 10));
}
return result;
}
// it will be deleted
public bool checkId(Data data)
{
if (!Text.isAble(data.getString("UserID"), 32))
{
return false;
}
Data param = new Data();
param.add("UserID", data.get("UserID"));
Data result = new Data();
UserData ud = new UserData();
result = ud.checkId(param);
if (result.Count == 0)
{
return true;
}
return false;
}
/// <summary>
/// get a patients list
/// </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 Users U WHERE JobCode = 0 and UserID in (SELECT patientId FROM history WHERE staffId = @doctorId AND state = 0 <DATE>) <SEARCH> ORDER BY UserID DESC";
String query = @"Select * From Users U, history H
WHERE U.UserID = H.patientId
AND U.JobCode = 0
AND H.status = 0
AND H.staffId = @doctorId
<DATE>
<SEARCH> ORDER BY U.UserID DESC";
//searcing
if (field.Equals(""))
{
query = query.Replace("<SEARCH>", "");
}
else if (field.Equals("memo"))
{
query = query.Replace("<SEARCH>", "AND H.memo LIKE '%" + value + "%'");
}
else if (field.Equals("UserID"))
{
query = query.Replace("<SEARCH>", "AND U.UserID LIKE '%" + value + "%'");
}
else if (field.Equals("firstName"))
{
query = query.Replace("<SEARCH>", "AND U.firstName LIKE '%" + value + "%'");
}
else if (field.Equals("surName"))
{
query = query.Replace("<SEARCH>", "AND U.surName LIKE '%" + value + "%'");
}
if (!"".Equals(data.getString("date")))
{
query = query.Replace("<DATE>", "AND H.date = CONVERT(datetime, @date, 103)");
}
else
{
query = query.Replace("<DATE>", "");
}
return select(query, data);
}
/// <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;
}
protected override void Fire(object sender, EventArgs e)
{
if (!UserInfo.isPharmacist(Session))
{
alertAndGoback("you are not a Pharmacist. Please login");
return;
}
Data result = new Data();
Data data = new Data();
MedicineBiz bizz = new MedicineBiz();
data.add("doctorId", UserInfo.getId(Session));
data.add("historyId", Param.get("historyId"));
historyId.Value = Param.getString("historyId");
String rId = Param.getString("rId");
if (!"".Equals(rId))
{
data.add("medicineId", rId);
bizz.delete(data);
}
PharmacistBiz biz = new PharmacistBiz();
result = biz.view(data);
userNameLabel.Text = result.getString("UserSurName") + "," + result.getString("UserFirstName") + ",ID:" + result.getString("UserID");
Usermed.Text = result.getString("UserOD");
Memo.Text = result.getString("Memo");
UserID.Text = result.getString("UserID");
OrderID.Text = result.getString("historyId");
Data itemReslt = bizz.getItems("", data);
Data medicineResult = bizz.view(data);
MedicineDroplist.DataSource = itemReslt.Source;
MedicineDroplist.DataBind();
Medicinelist.DataSource = medicineResult.Source;
Medicinelist.DataBind();
}
/// <summary>
/// get a report for patients number
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public Data getPatientReport(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 staffId, R.JobName, R.GroupName, COUNT(*) AS how_many_times
FROM history H, Users U, Roles R
WHERE U.UserID = H.staffId
AND R.JobCode = U.JobCode
AND H.date >= CONVERT(datetime, @startDate, 103)
AND H.date <= CONVERT(datetime, @endDate, 103)
GROUP BY H.staffId, R.JobName, R.GroupName
ORDER BY H.staffId ASC";
Data result = select(query, data);
return result;
}
/// <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>
/// search a list of payment reports
/// </summary>
/// <param name="searchField"></param>
/// <param name="searchValue"></param>
/// <param name="data"></param>
/// <returns></returns>
public Data reportList(String searchField, String searchValue, Data data)
{
String query = @"SELECT * FROM PaymentReport P, Users U
WHERE U.UserID = P.UserID
AND P.date >= CONVERT(datetime, @startDate, 103)
AND P.date <= CONVERT(datetime, @endDate, 103)
<SEARCH>
ORDER BY date DESC";
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);
}
if ("none".Equals(searchField))
{
query = query.Replace("<SEARCH>", "");
}
else if ("UserID".Equals(searchField))
{
query = query.Replace("<SEARCH>", "AND P.UserID LIKE '%" + searchValue +"%'");
}
else if ("UserFirstName".Equals(searchField))
{
query = query.Replace("<SEARCH>", "AND U.UserFirstName LIKE '%" + searchValue + "%'");
}
else if ("UserSurName".Equals(searchField))
{
query = query.Replace("<SEARCH>", "AND U.UserSurName LIKE '%" + searchValue + "%'");
}
Data result = select(query, data);
int count = result.Count;
for (int i = 0; i < count; i++)
{
result.add(i, "date", result.getString(i, "date").Substring(0, 10));
}
return result;
}
/// <summary>
/// get a list of pateints
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public Data getPatient(Data data)
{
return recData.getPatient(data.getString("searchField"), data.getString("searchValue"));
}
protected override void Fire(object sender, EventArgs e)
{
if (!UserInfo.isDoctor(Session))
{
alertAndGoback("you are not a doctor. Please login");
return;
}
Data result = new Data();
DBC dbc = new DBC();
Data data = new Data();
data.add("doctorId", UserInfo.getId(Session));
data.add("testresultId", Param.get("testresultId"));
/*dbc.open();
result = dbc.select("SELECT * FROM DoctorsTestResult A, Users B WHERE A.UserID = B.UserID AND A.TestResultID = @testresultId", data);
dbc.close();*/
TestResultBiz biz = new TestResultBiz();
result = biz.view(data);
userNameLabel.Text = result.getString("UserSurName") + "，" + result.getString("UserFirstName") + ",ID:" + result.getString("UserID");
DOT.Text = result.getString("DOT");
OR1.SelectedValue = result.getString("UserOR1");
OR2.SelectedValue = result.getString("UserOR2");
OR3.SelectedValue = result.getString("UserOR3");
OR4.SelectedValue = result.getString("UserOR4");
OR5.SelectedValue = result.getString("UserOR5");
OR6.SelectedValue = result.getString("UserOR6");
ORT1.Text = result.getString("UserORT1");
ORT2.Text = result.getString("UserORT2");
ORT3.Text = result.getString("UserORT3");
ORT4.Text = result.getString("UserORT4");
ORT5.Text = result.getString("UserORT5");
ORT6.Text = result.getString("UserORT6");
other_abnormalities.Text = result.getString("other_abnormalities");
Memo.Text = result.getString("Memo");
UserID.Text = result.getString("UserID");
TestResultID.Text = result.getString("TestResultID");
XrayBiz xBiz = new XrayBiz();
Data xData = new Data();
xData.add("TestResultID", Param.get("testresultId"));
Data xrayData = xBiz.view(xData);
xrayView.DataSource = xrayData.Source;
xrayView.DataBind();
MRIBiz mBiz = new MRIBiz();
Data MriData = mBiz.view(xData);
MRIView.DataSource = MriData.Source;
MRIView.DataBind();
}
/// <summary>
/// get a payment report
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public Data getPaymentReport(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 P.UserID, U.UserFirstName, U.UserSurName, I.insuranceName, SUM(total) AS Total_payed, AVG(total) AS Average_Payment
FROM PaymentReport P, Users U, Insurance I
WHERE U.UserID = P.UserID
AND I.insuranceId = U.InsuranceId
AND P.date >= CONVERT(datetime, @startDate, 103)
AND P.date <= CONVERT(datetime, @endDate, 103)
GROUP BY P.UserID, U.UserFirstName, U.UserSurName, I.insuranceName
ORDER BY P.UserID ASC";
Data result = select(query, data);
int count = result.Count;
for (int i = 0; i < count; i++)
{
float price = float.Parse(result.getString(i, "Average_Payment"));
price = (price - price % (float)0.01);
result.add(i, "Average_Payment", price);
}
return result;
}
/// <summary>
/// get a list of patients' books
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public Data list(Data data)
{
String patientId = data.getString("patientId");
String date = data.getString("date");
String searchField = data.getString("searchField");
String searchValue = data.getString("searchValue");
String query = @"SELECT * FROM history A, users B WHERE A.patientId = B.UserID AND A.status in (0,1) <SEARCH> <DATE> ORDER BY payState ASC, historyId DESC";
///searching
if ("".Equals(searchField))
{
query = query.Replace("<SEARCH>", "");
}
else if ("UserID".Equals(searchField))
{
query = query.Replace("<SEARCH>", "AND B.UserID LIKE '%" + searchValue + "%'");
}
else if ("UserFirstName".Equals(searchField))
{
query = query.Replace("<SEARCH>", "AND B.UserFirstName LIKE '%" + searchValue + "%'");
}
else if ("UserSurName".Equals(searchField))
{
query = query.Replace("<SEARCH>", "AND B.UserSurName LIKE '%" + searchValue + "%'");
}
else if ("Email".Equals(searchField))
{
query = query.Replace("<SEARCH>", "AND B.Email LIKE '%" + searchValue + "%'");
}
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>
/// Select nurse form base on current user ID
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public override Data view(Data data)
{
String today = DateTime.Now.Date.ToString().Substring(0, 10);
data.add("today", today);
String query = "SELECT * FROM NursesForm WHERE <SEARCH>";
if (!"".Equals(data.getString("pID")))
{
query = query.Replace("<SEARCH>", "UserID = @pID AND date = CONVERT(datetime, @today, 103)");
}
else
{
query = query.Replace("<SEARCH>", "id = @cntId");
}
Data result = select(query, data);
int count = result.Count;
for(int i =0; i < count; i++) {
result.add(i, "date", result.getString(i, "date").Substring(0,10));
}
return result;
}
/// <summary>
/// get a user information using user id
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public Data view(Data data)
{
Data param = new Data();
if (Text.isEmpty(data.getString("userId")))
{
param.add("userId", data.getString("UserID"));
}
else
{
param.add("userId", data.getString("userId"));
}
UserData ud = new UserData();
//result.add("question", Cryptograph.Decrypt( result.getString("question") ));
//result.add("answer", Cryptograph.Decrypt(result.getString("answer")));
return ud.view(param);
}
/// <summary>
/// get a patient payment report
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public Data getPatientPaymentReport(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 P.UserID, U.UserFirstName, U.UserSurName, I.insuranceName,
P.total * (100 - I.rate) / 100 AS Patient_Fee,
P.total * (100 - I.rate) / 100 AS Insurance_Fee,
P.total
FROM PaymentReport P, Users U, Insurance I
WHERE U.UserID = P.UserID
AND I.insuranceId = U.InsuranceId
AND P.date >= CONVERT(datetime, @startDate, 103)
AND P.date <= CONVERT(datetime, @endDate, 103)
GROUP BY P.UserID, U.UserFirstName, U.UserSurName, I.insuranceName, P.total, I.rate
ORDER BY P.UserID ASC";
Data result = select(query, data);
return result;
}
/// <summary>
/// get a user information using user id
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public override DataTable select(Data data)
{
Data param = new Data();
param.add("userId", data.getString("userId"));
UserData ud = new UserData();
//result.add("question", Cryptograph.Decrypt( result.getString("question") ));
//result.add("answer", Cryptograph.Decrypt(result.getString("answer")));
return ud.view(param).Source;
}
/// <summary>
/// create a new user
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public String registration(Data data)
{
if (!checkId(data))
{
return "Please, choose another ID";
}
if (data.getString("CPassword").Equals(data.getString("Password")))
{
String pwd = Cryptograph.Encrypt(data.getString("Password"));
data.add("Password", pwd);
UserData ud = new UserData();
ud.create(data);
}
return "OK";
}
/// <summary>
/// get a list of 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 Users U WHERE JobCode = 0 and UserID in (SELECT patientId FROM history WHERE staffId = @doctorId AND state = 0 <DATE>) <SEARCH> ORDER BY UserID DESC";
String query = @"Select * From Users U, history H
WHERE U.UserID = H.patientId
AND U.JobCode = 0
AND H.status = 0
AND H.type = -1
AND H.staffId = @doctorId
<DATE>
<SEARCH> ORDER BY U.UserID DESC";
if (field.Equals(""))
{
query = query.Replace("<SEARCH>", "");
}
else if (field.Equals("memo"))
{
query = query.Replace("<SEARCH>", "AND H.memo LIKE '%" + value + "%'");
}
else if (field.Equals("UserID"))
{
query = query.Replace("<SEARCH>", "AND U.UserID LIKE '%" + value + "%'");
}
else if (field.Equals("firstName"))
{
query = query.Replace("<SEARCH>", "AND U.firstName LIKE '%" + value + "%'");
}
else if (field.Equals("surName"))
{
query = query.Replace("<SEARCH>", "AND U.surName LIKE '%" + value + "%'");
}
if (!"".Equals(data.getString("date")))
{
query = query.Replace("<DATE>", "AND H.date = CONVERT(datetime, @date, 103)");
}
else
{
query = query.Replace("<DATE>", "");
}
Data result = select(query, data);
int count = result.Count;
for (int i = 0; i < count; i++)
{
String type = "";
if ("-1".Equals(result.getString(i, "type")))
{
type = "BOOK";
}
else if ("0".Equals(result.getString(i, "type")))
{
type = "ORDER";
}
else if ("1".Equals(result.getString(i, "type")))
{
type = "TEST";
}
else if ("2".Equals(result.getString(i, "type")))
{
type = "SURGERY";
}
else if ("3".Equals(result.getString(i, "type")))
{
type = "OBSERVATION";
}
result.add(i, "type", type);
result.add(i, "date", result.getString(i, "date").Substring(0, 10));
}
return result;
}
/// <summary>
/// login using id and password
/// </summary>
/// <param name="data"></param>
/// <param name="session"></param>
/// <returns></returns>
public HttpSessionState login(Data data, HttpSessionState session)
{
Data result = new Data();
Data param = new Data();
//check the id
if (!Text.isAble(data.getString("id"), 32))
{
result.add(UserInfo.loginState, "Please, check your ID.");
session = UserInfo.setInfo(session, result);
return session;
}
// check the password
else if (!Text.isAble(data.getString("pwd"), 32))
{
result.add(UserInfo.loginState, "Forgot your password?");
session = UserInfo.setInfo(session, result);
return session;
}
param.add("id", data.getString("id"));
param.add("pwd", Cryptograph.Encrypt(data.getString("pwd")));
UserData ud = new UserData();
result = ud.logIn(param);
if( result.Count == 0) {
result.add(UserInfo.loginState, "Forgot your password or ID?");
session = UserInfo.setInfo(session, result);
return session;
}
result.add(UserInfo.loginState, "OK");
return UserInfo.setInfo(session, result);
}
/// <summary>
/// get a history list of patients
/// </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.status < 9
<PRE>
<SEARCH>
<DATE>
ORDER BY date DESC, historyId DESC";//ORDER BY historyId DESC";
String date = data.getString("date");
string pre = data.getString("pre");
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 ("True".Equals(pre))
{
query = query.Replace("<PRE>", "");
}
else
{
query = query.Replace("<PRE>", "AND A.payState = '0' ");
}
if (date.Equals(""))
{
query = query.Replace("<DATE>", "");
}
else
{
query = query.Replace("<DATE>", "AND CONVERT(VARCHAR(10), A.date, 103) = '<DATE>'".Replace("<DATE>", date));
}
Data result = select(query, data);
int count = result.Count;
// check a type of content
for (int i = 0; i < count; i++)
{
String type = "";
if ("-1".Equals(result.getString(i, "type")))
{
type = "BOOK";
}
else if ("0".Equals(result.getString(i, "type")))
{
type = "ORDER";
}
else if ("1".Equals(result.getString(i, "type")))
{
type = "TEST";
}
else if ("2".Equals(result.getString(i, "type")))
{
type = "SURGERY";
}
else if ("3".Equals(result.getString(i, "type")))
{
type = "OBSERVATION";
}
result.add(i, "typeText", type);
result.add(i, "date", result.getString(i, "date").Substring(0, 10));
}
return result;
}
