// Get patient by patientid
public static Patient GetPatient(int patientID)
{
DataTable patientDataTable;
Patient newPatient = new Patient();
string sqlSelect = @"SELECT PATIENTID, FIRSTNAME, LASTNAME, BIRTHDAY, GENDER, ICN, PROFESSION, ADDRESS, DEPOSIT, STATE
FROM PATIENT
WHERE PATIENTID = @PatientID";
SqlParameter[] sqlParameters = { new SqlParameter("@PatientID", patientID) };
patientDataTable = SqlResult.ExecuteQuery(sqlSelect, sqlParameters);
// If select query have row then set to new patient
if (patientDataTable.Rows.Count > 0)
{
newPatient.PatientID = Convert.ToInt32(patientDataTable.Rows[0]["PATIENTID"].ToString());
newPatient.FirstName = (string)patientDataTable.Rows[0]["FIRSTNAME"];
newPatient.LastName = (string)patientDataTable.Rows[0]["LASTNAME"];
newPatient.BirthDay = (DateTime)patientDataTable.Rows[0]["BIRTHDAY"];
newPatient.Gender = (int)patientDataTable.Rows[0]["GENDER"];
newPatient.ICN = (decimal)patientDataTable.Rows[0]["ICN"];
newPatient.Profession = (string)patientDataTable.Rows[0]["PROFESSION"];
newPatient.Address = (string)patientDataTable.Rows[0]["ADDRESS"];
newPatient.Deposit = (decimal)patientDataTable.Rows[0]["DEPOSIT"];
newPatient.State = (int)patientDataTable.Rows[0]["STATE"];
}
return(newPatient);
}
public static Staff GetStaff(int staffID)
{
Staff newStaff = new Staff();
DataTable staffDataTable;
string sqlSelect = @"SELECT STAFFID, DEPARTMENTID, MAJORID, ROLEID, PASSWORD, FIRSTNAME, LASTNAME, BIRTHDAY,
GENDER, ICN, ADDRESS, STATE
FROM STAFF
WHERE (STAFFID = @StaffID)";
SqlParameter[] sqlParameters = { new SqlParameter("@StaffID", staffID) };
staffDataTable = SqlResult.ExecuteQuery(sqlSelect, sqlParameters);
if (staffDataTable.Rows.Count > 0)
{
newStaff.StaffID = Convert.ToInt32(staffDataTable.Rows[0]["STAFFID"].ToString());
newStaff.DepartmentID = Convert.ToInt32(staffDataTable.Rows[0]["DEPARTMENTID"].ToString());
newStaff.MajorID = Convert.ToInt32(staffDataTable.Rows[0]["MAJORID"].ToString());
newStaff.RoleID = Convert.ToInt32(staffDataTable.Rows[0]["ROLEID"].ToString());
newStaff.Password = (string)staffDataTable.Rows[0]["PASSWORD"];
newStaff.FirstName = (string)staffDataTable.Rows[0]["FIRSTNAME"];
newStaff.LastName = (string)staffDataTable.Rows[0]["LASTNAME"];
newStaff.BirthDay = (DateTime)staffDataTable.Rows[0]["BIRTHDAY"];
newStaff.Gender = (int)staffDataTable.Rows[0]["GENDER"];
newStaff.ICN = (decimal)staffDataTable.Rows[0]["ICN"];
newStaff.Address = (string)staffDataTable.Rows[0]["ADDRESS"];
newStaff.State = (int)staffDataTable.Rows[0]["STATE"];
}
return(newStaff);
}
public static DataTable GetListMajor()
{
string sqlSelect = @"SELECT MAJORID, MAJORNAME
FROM MAJOR";
DataTable dataTable = SqlResult.ExecuteQuery(sqlSelect);
return(dataTable);
}
public static DataTable GetListPatientID()
{
string sqlSelect = @"SELECT PATIENTID
FROM PATIENT";
patientTable = SqlResult.ExecuteQuery(sqlSelect);
return(patientTable);
}
// Get list patient
public static DataTable GetListPatient()
{
string sqlSelect = @"SELECT PATIENTID, FIRSTNAME, LASTNAME, BIRTHDAY, GENDER, ICN, PROFESSION, ADDRESS, DEPOSIT, STATE
FROM PATIENT";
patientTable = SqlResult.ExecuteQuery(sqlSelect);
return(patientTable);
}
public static DataTable GetListBillType()
{
DataTable dtB = new DataTable();
string sqlSelect = @"SELECT BILLTYPEID, TYPENAME
FROM BILLTYPE";
dtB = SqlResult.ExecuteQuery(sqlSelect);
dtB.Columns[0].ColumnName = "Mã loại hóa đơn";
dtB.Columns[1].ColumnName = "Tên loại hóa đơn";
return(dtB);
}
public static DataTable GetListTestType()
{
DataTable dtT = new DataTable();
string sqlSelect = @"SELECT TESTTYPEID, TYPENAME
FROM TESTTYPE";
dtT = SqlResult.ExecuteQuery(sqlSelect);
//dtT.Columns[0].ColumnName = "Mã loại xét nghiệm";
//dtT.Columns[1].ColumnName = "Tên loại xét nghiệm";
return(dtT);
}
public static DataTable GetListDepartment()
{
DataTable dtDepartment = new DataTable();
string sqlSelect = @"SELECT DEPARTMENTID, DEPARTMENTNAME
FROM DEPARTMENT";
dtDepartment = SqlResult.ExecuteQuery(sqlSelect);
//dtDepartment.Columns[0].ColumnName = "Mã khoa";
//dtDepartment.Columns[1].ColumnName = "Tên khoa";
return(dtDepartment);
}
public static DataTable GetListRole()
{
DataTable dtRole = new DataTable();
string sqlSelect = @"SELECT ROLEID, ROLENAME
FROM ROLE";
dtRole = SqlResult.ExecuteQuery(sqlSelect);
//dtRole.Columns[0].ColumnName = "Mã phân quyền";
//dtRole.Columns[1].ColumnName = "Tên phân quyền";
return(dtRole);
}
public static DataTable GetListHospitalBed()
{
DataTable dtHB = new DataTable();
string sqlSelect = @"SELECT BEDID,PATIENT, STATE
FROM HOSPITALBED";
dtHB = SqlResult.ExecuteQuery(sqlSelect);
//dtHB.Columns[0].ColumnName = "Mã giường bệnh";
//dtHB.Columns[1].ColumnName = "Mã bệnh nhân";
//dtHB.Columns[2].ColumnName = "Trạng thái";
return(dtHB);
}
public static DataTable GetListFunction()
{
DataTable dtF = new DataTable();
string sqlSelect = @"SELECT FUNCTIONID, FUNCTIONNAME, BUTTON
FROM ROLEFUNCTION";
dtF = SqlResult.ExecuteQuery(sqlSelect);
//dtF.Columns[0].ColumnName = "Mã chức năng";
//dtF.Columns[1].ColumnName = "Tên chức năng";
//dtF.Columns[2].ColumnName = "Nút kích hoạt";
return(dtF);
}
public static DataTable GetListService()
{
DataTable dtS = new DataTable();
string sqlSelect = @"SELECT SERVICEID, SERVICENAME, PRICE
FROM SERVICE";
dtS = SqlResult.ExecuteQuery(sqlSelect);
//dtS.Columns[0].ColumnName = "Mã dịch vụ";
//dtS.Columns[1].ColumnName = "Tên dịch vụ";
//dtS.Columns[2].ColumnName = "Đơn giá";
return(dtS);
}
public static DataTable GetListDisease()
{
DataTable dtDisease = new DataTable();
string sqlSelect = @"SELECT DISEASEID,DISEASENAME,SYMPTOM
FROM DISEASE";
dtDisease = SqlResult.ExecuteQuery(sqlSelect);
//dtDisease.Columns[0].ColumnName = "Mã bệnh";
//dtDisease.Columns[1].ColumnName = "Tên bệnh";
//dtDisease.Columns[2].ColumnName = "Triệu chứng";
return(dtDisease);
}
public static DataTable GetListPrescription()
{
DataTable dtP = new DataTable();
string sqlSelect = @"SELECT PRESCRIPTIONID, STAFFID, PATIENTID, DATE
FROM PRESCRIPTION";
dtP = SqlResult.ExecuteQuery(sqlSelect);
//dtP.Columns[0].ColumnName = "Mã đơn thuốc";
//dtP.Columns[1].ColumnName = "Mã nhân viên";
//dtP.Columns[2].ColumnName = "Mã bệnh nhân";
//dtP.Columns[3].ColumnName = "Ngày lập";
return(dtP);
}
public static DataTable GetPatientIDInPrescription(int pID)
{
DataTable dtPD;
string sqlSelect = @"SELECT PATIENTID
FROM PRESCRIPTION
WHERE PRESCRIPTIONID=@PRESCRIPTIONID";
SqlParameter[] sqlParameters = { new SqlParameter("@PRESCRIPTIONID", pID) };
dtPD = SqlResult.ExecuteQuery(sqlSelect, sqlParameters);
return(dtPD);
}
public static DataTable GetListAssignment()
{
DataTable dtAssignment = new DataTable();
string sqlSelect = @"SELECT ASSIGNID, PATIENTID, DATE, DISCHARGEDDATE, HOPITALIZATEDATE
FROM ASSIGNMENT";
dtAssignment = SqlResult.ExecuteQuery(sqlSelect);
//dtAssignment.Columns[0].ColumnName = "Mã phân công";
//dtAssignment.Columns[1].ColumnName = "Ngày lập";
//dtAssignment.Columns[2].ColumnName = "Ngày xuất viện";
//dtAssignment.Columns[3].ColumnName = "Ngày nhập viện";
return(dtAssignment);
}
public static DataTable GetListStaff()
{
string sqlSelect = @"SELECT STAFF.STAFFID, DEPARTMENT.DEPARTMENTNAME, MAJOR.MAJORNAME, ROLE.ROLENAME, STAFF.PASSWORD
, STAFF.FIRSTNAME, STAFF.LASTNAME, STAFF.BIRTHDAY, STAFF.GENDER, STAFF.ICN, STAFF.ADDRESS, STAFF.STATE
FROM STAFF INNER JOIN
DEPARTMENT ON STAFF.DEPARTMENTID = DEPARTMENT.DEPARTMENTID INNER JOIN
MAJOR ON STAFF.MAJORID = MAJOR.MAJORID INNER JOIN
ROLE ON STAFF.ROLEID = ROLE.ROLEID";
staffTable = SqlResult.ExecuteQuery(sqlSelect);
return(staffTable);
}
public static DataTable GetListMedicine()
{
DataTable dtM = new DataTable();
string sqlSelect = @"SELECT MEDICINEID, MEDICINENAME, QUANTITY, PRICE
FROM MEDICINE";
dtM = SqlResult.ExecuteQuery(sqlSelect);
//dtM.Columns[0].ColumnName = "Mã thuốc";
//dtM.Columns[1].ColumnName = "Tên thuốc";
//dtM.Columns[2].ColumnName = "Số lượng";
//dtM.Columns[3].ColumnName = "Đơn giá";
return(dtM);
}
public static DataTable GetPatientNotPayBill(int patientID)
{
DataTable patientBillTable = new DataTable();
string sqlSelect = @"SELECT BILLID, BILLTYPEID, PATIENTID, STAFFID, DATE, TOTALPRICE, STATE
FROM BILL
WHERE (PATIENTID=@PATIENTID) AND STATE = 0";
SqlParameter[] sqlParameters = { new SqlParameter("@PATIENTID", patientID) };
patientBillTable = SqlResult.ExecuteQuery(sqlSelect, sqlParameters);
return(patientBillTable);
}
public static DataTable GetListMaterial()
{
DataTable dtM = new DataTable();
string sqlSelect = @"SELECT MATERIALID, MATERIALNAME, QUANTITY, PRICE
FROM MATERIAL";
dtM = SqlResult.ExecuteQuery(sqlSelect);
//dtM.Columns[0].ColumnName = "Mã vật tư";
//dtM.Columns[1].ColumnName = "Tên vật tư";
//dtM.Columns[2].ColumnName = "Số lượng";
//dtM.Columns[3].ColumnName = "Đơn giá";
return(dtM);
}
public static DataTable GetListSurgical()
{
DataTable dtS = new DataTable();
string sqlSelect = @"SELECT SURGICALID, PATIENTID, DATE, DESCRIPTION, STATE
FROM SURGICAL";
dtS = SqlResult.ExecuteQuery(sqlSelect);
//dtS.Columns[0].ColumnName = "Mã ca phẩu thuật";
//dtS.Columns[1].ColumnName = "Mã bệnh nhân";
//dtS.Columns[2].ColumnName = "Ngày thực hiện";
//dtS.Columns[3].ColumnName = "Mô tả";
//dtS.Columns[4].ColumnName = "Trạng thái";
return(dtS);
}
public static DataTable GetListTC()
{
DataTable dtTC = new DataTable();
string sqlSelect = @"SELECT TCID, PATIENTID, STAFFID, DATE, STATE
FROM TESTCERTIFICATE";
dtTC = SqlResult.ExecuteQuery(sqlSelect);
//dtTC.Columns[0].ColumnName = "Mã phiếu xét nghiệm";
//dtTC.Columns[1].ColumnName = "Mã bệnh nhân";
//dtTC.Columns[2].ColumnName = "Mã nhân viên";
//dtTC.Columns[3].ColumnName = "Ngày lập";
//dtTC.Columns[4].ColumnName = "Trạng thái";
return(dtTC);
}
public static DataTable GetListDC()
{
DataTable dtEC = new DataTable();
string sqlSelect = @"SELECT DCID, STAFFID, PATIENTID, DATE, STATE
FROM DISCHARGEDCERTIFICATE";
dtEC = SqlResult.ExecuteQuery(sqlSelect);
//dtEC.Columns[0].ColumnName = "Mã giấy xuất viện";
//dtEC.Columns[1].ColumnName = "Mã nhân viên";
//dtEC.Columns[2].ColumnName = "Mã bệnh nhân";
//dtEC.Columns[3].ColumnName = "Ngày lập";
//dtEC.Columns[4].ColumnName = "Trạng thái";
return(dtEC);
}
public static DataTable GetListStaffFunction(int roleID)
{
DataTable dtRoleDetail = new DataTable();
string sqlSelect = @"SELECT ROLEFUNCTION.FUNCTIONID, ROLEFUNCTION.FUNCTIONNAME, ROLEFUNCTION.BUTTON
FROM ROLEDETAIL INNER JOIN ROLEFUNCTION ON ROLEDETAIL.FUNCTIONID = ROLEFUNCTION.FUNCTIONID
WHERE ROLEDETAIL.ROLEID=@ROLEID";
SqlParameter[] sqlParameters = { new SqlParameter("@ROLEID", roleID) };
dtRoleDetail = SqlResult.ExecuteQuery(sqlSelect, sqlParameters);
dtRoleDetail.Columns[0].ColumnName = "Mã chức năng";
dtRoleDetail.Columns[1].ColumnName = "Tên chức năng";
dtRoleDetail.Columns[2].ColumnName = "Nút kích hoạt";
return(dtRoleDetail);
}
public static DataTable GetListBill()
{
string sqlSelect = @"SELECT BILL.BILLID, BILLTYPE.TYPENAME, PATIENT.FIRSTNAME AS PATIENTFIRSTNAME,
PATIENT.LASTNAME AS PATIENTLASTNAME, BILL.BILLTYPEID, BILL.PATIENTID, BILL.STAFFID,
BILL.DATE, BILL.TOTALPRICE,BILL.STATE, STAFF.LASTNAME AS STAFFLASTNAME,
STAFF.FIRSTNAME AS STAFFFIRSTNAME
FROM BILL INNER JOIN
BILLTYPE ON BILL.BILLTYPEID = BILLTYPE.BILLTYPEID INNER JOIN
PATIENT ON BILL.PATIENTID = PATIENT.PATIENTID INNER JOIN
STAFF ON BILL.STAFFID = STAFF.STAFFID";
billTable = SqlResult.ExecuteQuery(sqlSelect);
return(billTable);
}
public static Boolean IsPatientHadDC(int patientID)
{
DataTable dtDC = new DataTable();
string sqlSelect = @"SELECT DCID, STAFFID, PATIENTID, DATE, STATE
FROM DISCHARGEDCERTIFICATE
WHERE PATIENTID=@PATIENTID";
SqlParameter[] sqlParameters = { new SqlParameter("@PATIENTID", patientID) };
dtDC = SqlResult.ExecuteQuery(sqlSelect, sqlParameters);
if (dtDC.Rows.Count > 0)
{
return(true);
}
return(false);
}
public static DataTable GetListEC()
{
DataTable dtEC = new DataTable();
string sqlSelect = @"SELECT ECID, PATIENTID, STAFFID, DATE, RESULT, STATE
FROM EXAMINATIONCERTIFICATE";
dtEC = SqlResult.ExecuteQuery(sqlSelect);
//dtEC.Columns[0].ColumnName = "Mã phiếu khám bệnh";
//dtEC.Columns[1].ColumnName = "Mã bệnh nhân";
//dtEC.Columns[2].ColumnName = "Mã nhân viên khám";
//dtEC.Columns[3].ColumnName = "Ngày lập";
//dtEC.Columns[4].ColumnName = "Kết quả";
//dtEC.Columns[5].ColumnName = "Trạng thái";
return(dtEC);
}
public static DataTable GetListHIC(int patientID)
{
DataTable dtHIC = new DataTable();
string sqlSelect = @"SELECT HICID, PATIENTID, EXPIREDATE, ISSUEDATE
FROM HIC
WHERE PATIENTID=@PATIENTID";
SqlParameter[] sqlParameters = { new SqlParameter("@PATIENTID", patientID) };
dtHIC = SqlResult.ExecuteQuery(sqlSelect, sqlParameters);
dtHIC.Columns[0].ColumnName = "Số thẻ bảo hiểm";
dtHIC.Columns[1].ColumnName = "Mã bệnh nhân";
dtHIC.Columns[2].ColumnName = "Ngày hết hạn";
dtHIC.Columns[3].ColumnName = "Ngày phát hành";
return(dtHIC);
}
public static DataTable GetListPrescriptionDetailWithMedicine(int pID)
{
DataTable dtPD;
string sqlSelect = @"SELECT MEDICINE.MEDICINEID, MEDICINE.MEDICINENAME, PRESCRIPTIONDETAIL.QUANTITY, MEDICINE.PRICE * PRESCRIPTIONDETAIL.QUANTITY AS PRICE
FROM PRESCRIPTIONDETAIL INNER JOIN
MEDICINE ON PRESCRIPTIONDETAIL.MEDICINEID = MEDICINE.MEDICINEID
WHERE (PRESCRIPTIONDETAIL.PRESCRIPTIONID = @PRESCRIPTIONID)";
SqlParameter[] sqlParameters = { new SqlParameter("@PRESCRIPTIONID", pID) };
dtPD = SqlResult.ExecuteQuery(sqlSelect, sqlParameters);
return(dtPD);
}
public static Boolean IsPatientHadAssignment(int patientID)
{
DataTable dtA = new DataTable();
string sqlSelect = @"SELECT ASSIGNID, DATE, HOPITALIZATEDATE, DISCHARGEDDATE, PATIENTID
FROM ASSIGNMENT
WHERE PATIENTID=@PATIENTID";
SqlParameter[] sqlParameters = { new SqlParameter("@PATIENTID", patientID) };
dtA = SqlResult.ExecuteQuery(sqlSelect, sqlParameters);
if (dtA.Rows.Count > 0)
{
return(true);
}
return(false);
}