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 <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 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 <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 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 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 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 deletePatient(int pid)
{
int id = Active.update("UPDATE patient SET is_delete = " + 1 + " where id = " + pid);
return(id);
}
static public int countAllUser()
{
DataTable dt = Active.select("SELECT * FROM user");
return(dt.Rows.Count);
}
static public int updateMedicalExamStatus(string code, int status)
{
int num = Active.update("UPDATE medical_exam SET status = " + status + " WHERE code = '" + code + "'");
return(num);
}
static public int updateMedicalExam(string code, int sickID, string prognostic, int status)
{
int num = Active.update("UPDATE medical_exam SET sick_id = " + sickID + ", prognostic = N'" + prognostic + "', status = " + status + " where code = '" + code + "'");
return(num);
}
static public int insertMedicalExam(string code, int patientID, int doctorID)
{
int num = Active.insert("INSERT INTO medical_exam(code, patient_id, physician_id, date_exam, status, fee_exam, fee_medicine) VALUES ('" + code + "', " + patientID + ", " + doctorID + ", '" + DateTime.Now.ToString("yyyy-MM-dd") + "', 1, 0, 0)");
return(num);
}
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"]));
}
static public int insertSickness(Sickness sick, int user_update)
{
int id = Active.insert("INSERT INTO sickness(name, noted, user_change) VALUES (N'" + sick.name + "',N'" + sick.noted + "','" + user_update + "')");
return(id);
}
static public int updateSickness(Sickness sick, int user_change)
{
int id = Active.update("UPDATE sickness SET name = N'" + sick.name + "', noted = N'" + sick.noted + "', user_change = '" + user_change + "' where id = " + sick.id + "");
return(id);
}
static public int updateUnitMedicine(UnitMedicine unit, int user_change)
{
int id = Active.update("UPDATE unit_medicine SET name = N'" + unit.name + "', user_change = '" + user_change + "' where id = " + unit.id + "");
return(id);
}
static public int insertUnitMedicine(UnitMedicine unit, int user_update)
{
int id = Active.insert("INSERT INTO unit_medicine(name, user_change) VALUES (N'" + unit.name + "','" + user_update + "')");
return(id);
}
static public int deleteSickness(int sick_id, int user_change)
{
int id = Active.update("UPDATE sickness SET is_delete = N'" + 1 + "', user_change = N'" + user_change + "' where id = " + sick_id + "");
return(id);
}
static public int updateMedicalExam(string code, long feeExam, int feeMedicine)
{
int num = Active.update("UPDATE medical_exam SET fee_exam = " + feeExam + ", fee_medicine = " + feeMedicine + " where code = '" + code + "'");
return(num);
}
//update user (khong thay doi password)
static public int updateUserNoPass(User u)
{
int id = Active.update("UPDATE user SET user = '******', name = N'" + u.full_name + "', email = '" + u.email + "', role_id = '" + u.role_id + "' where id = " + u.id + "");
return(id);
}
static public int updateUseMedicine(UserMedicine use, int user_change)
{
int id = Active.update("UPDATE use_medicine SET name = N'" + use.name + "', detail = '" + use.detail + "', user_change = '" + user_change + "' where id = " + use.id + "");
return(id);
}
static public int insertUser(User u)
{
int id = Active.insert("INSERT INTO user(user, name, pw, email, role_id) VALUES ('" + u.name + "',N'" + u.full_name + "','" + u.password + "','" + u.email + "','" + u.role_id + "')");
return(id);
}
static public int deleteUseMedicine(int use_id, int user_change)
{
int id = Active.update("UPDATE use_medicine SET is_delete = N'" + 1 + "', user_change = N'" + user_change + "' where id = " + use_id + "");
return(id);
}
static public int insertUseMedicine(UserMedicine use, int user_update)
{
int id = Active.insert("INSERT INTO use_medicine(name, detail, user_change) VALUES (N'" + use.name + "',N'" + use.detail + "','" + user_update + "')");
return(id);
}
static public int insertPrescription(Prescription p, string code)
{
int num = Active.insert("INSERT INTO prescription(code, medicine_id, unit_id, amount, use_id) VALUES ('" + code + "', " + p.medicine_id + ", " + p.unit_id + ", " + p.amount + ", " + p.use_id + ")");
return(num);
}