static public int getFeeMedicine(string code)
{
DataTable dt = Active.select("SELECT medicine_id, unit_id, amount FROM prescription WHERE code = '" + code + "'");
int n = dt.Rows.Count;
int price = 0;
if (n > 0)
{
for (int i = 0; i < n; i++)
{
int medicineID = Convert.ToInt32(dt.Rows[i]["medicine_id"]);
int unitID = Convert.ToInt32(dt.Rows[i]["unit_id"]);
int amount = Convert.ToInt32(dt.Rows[i]["amount"]);
int unitPrice = 0;
DataTable dt2 = Active.select("SELECT unit_price FROM unit_price_medicine WHERE medicine_id = " + medicineID + " and unit_id = " + unitID);
int m = dt.Rows.Count;
if (m > 0)
{
unitPrice = Convert.ToInt32(dt2.Rows[0]["unit_price"]);
}
price += amount * unitPrice;
}
}
return(price);
}
static public string getExamCode(int patientID)
{
DataTable dt = Active.select("SELECT code FROM medical_exam WHERE patient_id = " + patientID + " and date_exam = '" + DateTime.Now.ToString("yyyy-MM-dd") + "'");
string code = dt.Rows[0]["code"].ToString();
return(code);
}
//dem phieu kham benh theo ma loai benh
static public int countMedicalExamBySickID(int id)
{
int quantity = 0;
DataTable dt = Active.select("select * from medical_exam where sick_id = " + id);
quantity = dt.Rows.Count;
return(quantity);
}
//dem so don thuoc theo ma cach dung
static public int countPrescriptionByUseID(int id)
{
int quantity = 0;
DataTable dt = Active.select("select * from prescription where use_id = " + id);
quantity = dt.Rows.Count;
return(quantity);
}
//dem don gia thuoc theo ma don vi thuoc
static public int countUnitPriceMedicineByUnitID(int id)
{
int quantity = 0;
DataTable dt = Active.select("select * from unit_price_medicine where unit_id = " + id + " and is_delete = " + 0);
quantity = dt.Rows.Count;
return(quantity);
}
//Phân quyền người dùng
static public List <int> getRoleFunction(int roleid)
{
DataTable dt = Active.select("select function from role_function where role=" + roleid + " and is_delete = " + 0);
List <int> listFunc = new List <int>();
for (int i = 0; i < dt.Rows.Count; i++)
{
listFunc.Add(Convert.ToInt32(dt.Rows[i]["function"]));
}
return(listFunc);
}
static public int getTurnover()
{
DataTable dt = Active.select("SELECT SUM(me.fee_exam) + SUM(me.fee_medicine) as turnover FROM medical_exam as me WHERE MONTH(me.`date_exam`) = MONTH(CURRENT_DATE()) ");
int result = 0;
if (dt.Rows[0]["turnover"].ToString() != "")
{
result = Convert.ToInt32(dt.Rows[0]["turnover"]);
}
return(result);
}
static public int countMedicalExam()
{
DataTable dt = Active.select("SELECT COUNT(code) totalCode from medical_exam where date_exam = '" + DateTime.Now.ToString("yyyy-MM-dd") + "'");
if (dt.Rows[0]["totalCode"] == null)
{
return(-1);
}
int id = Convert.ToInt32(dt.Rows[0]["totalCode"]);
return(id);
}
static public int getSicknessID(string sicknessName)
{
DataTable dt = Active.select("SELECT id from sickness where name = '" + sicknessName + "'");
if (dt.Rows.Count == 0)
{
return(-1);
}
int id = Convert.ToInt32(dt.Rows[0]["id"]);
return(id);
}
static public int getUnitID(string unitName)
{
DataTable dt = Active.select("SELECT id from unit_medicine where name = '" + unitName + "'");
if (dt.Rows.Count == 0)
{
return(-1);
}
int id = Convert.ToInt32(dt.Rows[0]["id"]);
return(id);
}
//Người dùng
static public User login(string username, string pw)
{
DataTable dt = Active.select("select id, role_id from user where user='******' and pw='" + pw + "' and is_delete = " + 0);
User user = new User();
for (int i = 0; i < dt.Rows.Count && dt != null; i++)
{
user.id = Convert.ToInt32(dt.Rows[i]["id"]);
user.role_id = Convert.ToInt32(dt.Rows[i]["role_id"]);
}
return(user);
}
static public List <int> getPatientIdInExam()
{
List <int> listID = new List <int>();
DataTable dt = Active.select("SELECT patient_id FROM medical_exam WHERE date_exam = '" + DateTime.Now.ToString("yyyy-MM-dd") + "' and status = 1");
for (int i = 0; i < dt.Rows.Count; i++)
{
int id = Convert.ToInt32(dt.Rows[i]["patient_id"]);
listID.Add(id);
}
return(listID);
}
static public List <string> getListSicknessName()
{
List <string> listSickName = new List <string>();
DataTable dt = Active.select("select name from sickness where is_delete = " + 0);
for (int i = 0; i < dt.Rows.Count; i++)
{
string s = "";
s = dt.Rows[i]["name"].ToString();
listSickName.Add(s);
}
return(listSickName);
}
static public List <UnitMedicine> getAllUnit()
{
DataTable dt = Active.select("select * from unit_medicine where is_delete = " + 0);
List <UnitMedicine> listUnit = new List <UnitMedicine>();
for (int i = 0; i < dt.Rows.Count; i++)
{
UnitMedicine unit = new UnitMedicine();
unit.id = Convert.ToInt32(dt.Rows[i]["id"]);
unit.name = dt.Rows[i]["name"].ToString();
listUnit.Add(unit);
}
return(listUnit);
}
//Quan ly cach dung
static public List <UserMedicine> getAllUseMedicine()
{
DataTable dt = Active.select("select * from use_medicine where is_delete = " + 0);
List <UserMedicine> listUseMedicine = new List <UserMedicine>();
for (int i = 0; i < dt.Rows.Count; i++)
{
UserMedicine use = new UserMedicine();
use.id = Convert.ToInt32(dt.Rows[i]["id"]);
use.name = dt.Rows[i]["name"].ToString();
use.detail = dt.Rows[i]["detail"].ToString();
listUseMedicine.Add(use);
}
return(listUseMedicine);
}
static public List <Sickness> getAllSickness()
{
DataTable dt = Active.select("select * from sickness where is_delete = " + 0);
List <Sickness> listSickness = new List <Sickness>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Sickness sick = new Sickness();
sick.id = Convert.ToInt32(dt.Rows[i]["id"]);
sick.name = dt.Rows[i]["name"].ToString();
sick.noted = dt.Rows[i]["noted"].ToString();
listSickness.Add(sick);
}
return(listSickness);
}
//lay thong tin user theo user_id
static public User getUserWithID(int id)
{
User u = new User();
DataTable dt = Active.select("SELECT * FROM user WHERE id = " + id + " and is_delete = " + 0);
if (dt.Rows.Count != 0)
{
u.id = id;
u.name = dt.Rows[0]["user"].ToString();
u.full_name = dt.Rows[0]["name"].ToString();
u.email = dt.Rows[0]["email"].ToString();
return(u);
}
u.id = -1;
return(u);
}
static public List <ChangeRegulation> getAllRegulation()
{
DataTable dt = Active.select(" select * from change_reg ");
List <ChangeRegulation> listRegulation = new List <ChangeRegulation>();
for (int i = 0; i < dt.Rows.Count; i++)
{
ChangeRegulation reg = new ChangeRegulation();
reg.id_function = Convert.ToInt32(dt.Rows[i]["id_function"]);
reg.name_function = dt.Rows[i]["name_function"].ToString();
reg.value_old = Convert.ToInt32(dt.Rows[i]["value_old"]);
reg.date_apply = Convert.ToDateTime(dt.Rows[i]["date_apply"]);
reg.value_apply = Convert.ToInt32(dt.Rows[i]["value_new"]);
listRegulation.Add(reg);
}
return(listRegulation);
}
static public Patient getPatientWithID(int id)
{
Patient p = new Patient();
DataTable dt = Active.select("SELECT * FROM patient WHERE id = " + id + " and is_delete = " + 0);
if (dt.Rows.Count != 0)
{
p.id = id;
p.full_name = dt.Rows[0]["name"].ToString();
p.sex = Convert.ToInt32(dt.Rows[0]["sex"]) == 1 ? "Nam" : "Nữ";
p.address = dt.Rows[0]["address"].ToString();
p.year_of_birth = Convert.ToInt32(dt.Rows[0]["yob"]);
return(p);
}
p.id = -1;
return(p);
}
static public List <Patient> getListPatient()
{
List <Patient> listPatient = new List <Patient>();
DataTable dt = Active.select("select p.* from patient p, medical_exam m where p.is_delete = " + 0 + " and m.patient_id = p.id and m.fee_exam = 0 and m.date_exam = '" + DateTime.Now.ToString("yyyy-MM-dd") + "'");
for (int i = 0; i < dt.Rows.Count; i++)
{
Patient p = new Patient();
p.id = Convert.ToInt32(dt.Rows[i]["id"]);
p.full_name = dt.Rows[i]["name"].ToString();
p.sex = Convert.ToInt32(dt.Rows[i]["sex"]) == 1 ? "Nam" : "Nữ";
p.year_of_birth = Convert.ToInt32(dt.Rows[i]["yob"]);
p.address = dt.Rows[i]["address"].ToString();
listPatient.Add(p);
}
return(listPatient);
}
static public List <User> getAllUser()
{
DataTable dt = Active.select("select * from user where is_delete = " + 0);
List <User> listUser = new List <User>();
for (int i = 0; i < dt.Rows.Count; i++)
{
User u = new User();
u.id = Convert.ToInt32(dt.Rows[i]["id"]);
u.name = dt.Rows[i]["user"].ToString();
u.full_name = dt.Rows[i]["name"].ToString();
u.password = dt.Rows[i]["pw"].ToString();
u.email = dt.Rows[i]["email"].ToString();
u.role_id = Convert.ToInt32(dt.Rows[i]["role_id"]);
listUser.Add(u);
}
return(listUser);
}
//Quan ly thuoc
static public List <FullMedicine> getAllMedicine()
{
DataTable dt = Active.select("select medicine.id, medicine.`name`, medicine.another_name, unit_price_medicine.unit_id, unit_medicine.`name` as unit, unit_price_medicine.unit_price, unit_price_medicine.num_smallest_unit from medicine, unit_price_medicine, unit_medicine where medicine.id = unit_price_medicine.medicine_id and unit_price_medicine.unit_id = unit_medicine.id and medicine.is_delete = " + 0);
List <FullMedicine> listFullMedicine = new List <FullMedicine>();
for (int i = 0; i < dt.Rows.Count; i++)
{
FullMedicine medicine = new FullMedicine();
medicine.id = Convert.ToInt32(dt.Rows[i]["id"]);
medicine.name = dt.Rows[i]["name"].ToString();
medicine.another_name = dt.Rows[i]["another_name"].ToString();
medicine.unit_id = Convert.ToInt32(dt.Rows[i]["unit_id"]);
medicine.unit_name = dt.Rows[i]["unit"].ToString();
medicine.unit_price = Convert.ToInt64(dt.Rows[i]["unit_price"]);
medicine.num_smallest_unit = Convert.ToInt32(dt.Rows[i]["num_smallest_unit"]);
listFullMedicine.Add(medicine);
}
return(listFullMedicine);
}
static public int countAllUser()
{
DataTable dt = Active.select("SELECT * FROM user");
return(dt.Rows.Count);
}
static public int countAllPatient()
{
DataTable dt = Active.select("SELECT COUNT(DISTINCT p.id) totalPatient FROM patient p, medical_exam me WHERE p.id = me.patient_id and MONTH(me.`date_exam`) = MONTH(CURRENT_DATE()) ");
return(Convert.ToInt32(dt.Rows[0]["totalPatient"]));
}
static public int getLastPatientID()
{
DataTable dt = Active.select("select MAX(id) id from patient where is_delete = 0");
return(Convert.ToInt32(dt.Rows[0]["id"]));
}
static public int getMedicalExamStatus(int patientID)
{
DataTable dt = Active.select("select status from medical_exam where patient_id = " + patientID + " and date_exam = '" + DateTime.Now.ToString("yyyy-MM-dd") + "'");
return(Convert.ToInt32(dt.Rows[0]["status"]));
}