using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.Common; using System.Data.SQLite; namespace WpfTest1.SQLite { public class SQLiteModel { public static string dbPath = Toolkits.Constants.dbPath; #region 各类建表操作 //Patient表的创建 //Patient表的创建 public static void CreatePatientTable() { //如果不存在改数据库文件,则创建该数据库文件 if (!System.IO.File.Exists(dbPath)) { SQLiteHelper.CreateDB(dbPath); //Console.WriteLine("OK"); } SQLiteHelper db = new SQLiteHelper(dbPath); string sql = @"CREATE TABLE Patient( id integer primary key autoincrement not null, record_id varchar(100), idNum varchar(20), name varchar(20), height float, weight float, base_heartrate integer, pregnancy_times integer, birth_times integer, pregnancy_date Date, mobile varchar(20), profession varchar(40), address varchar(140), category varchar(40), group_1 varchar(40), firstLetterPY varchar(10), description varchar(200), lastRecordDate datetime, delete_flag boolean )"; db.ExecuteNonQuery(sql, null); string sql_1 = "CREATE INDEX Patient_idNum on Patient(idNum)"; db.ExecuteNonQuery(sql_1, null); } //创建记录表 public static void CreateRecordTable() { //如果不存在改数据库文件,则创建该数据库文件 if (!System.IO.File.Exists(dbPath)) { SQLiteHelper.CreateDB(dbPath); } SQLiteHelper db = new SQLiteHelper(dbPath); string sql = @"CREATE TABLE Record( id integer primary key autoincrement not null , patientId integer, recordTime datetime , dataLeft blob, dataRight blob, dataWrist blob, topBP integer, bottomBP integer, heartRate integer, eigenValueSaved integer , comments varchar(200) )"; db.ExecuteNonQuery(sql, null); } //创建医生表 public static void CreateDoctorTable() { //如果不存在改数据库文件,则创建该数据库文件 if (!System.IO.File.Exists(dbPath)) { SQLiteHelper.CreateDB(dbPath); } SQLiteHelper db = new SQLiteHelper(dbPath); string sql = @"CREATE TABLE Doctor( id integer primary key autoincrement not null , name varchar(20), pwd_hash varchar(32), phone_number varchar(20), director_flag boolean, delete_flag boolean)"; db.ExecuteNonQuery(sql, null); } //创建CommonWords表 public static void CreateCommonWordsTable() { //如果不存在改数据库文件,则创建该数据库文件 if (!System.IO.File.Exists(dbPath)) { SQLiteHelper.CreateDB(dbPath); } SQLiteHelper db = new SQLiteHelper(dbPath); string sql = @"CREATE TABLE CommonWords( id integer primary key autoincrement not null , content varchar(200), describe varchar(20))"; db.ExecuteNonQuery(sql, null); } //创建表达式表 public static void CreateExpressionTable() { //如果不存在改数据库文件,则创建该数据库文件 if (!System.IO.File.Exists(dbPath)) { SQLiteHelper.CreateDB(dbPath); } SQLiteHelper db = new SQLiteHelper(dbPath); string sql = @"CREATE TABLE Expression( id integer primary key autoincrement not null , expression_content varchar(500), expression_description varchar(500) )"; db.ExecuteNonQuery(sql, null); } //创建配置表 public static void CreateConfigTable() { //如果不存在改数据库文件,则创建该数据库文件 if (!System.IO.File.Exists(dbPath)) { SQLiteHelper.CreateDB(dbPath); } SQLiteHelper db = new SQLiteHelper(dbPath); string sql = @"CREATE TABLE Config( key varchar(100) primary key not null , value varchar(500) )"; db.ExecuteNonQuery(sql, null); } //创建治疗方案表 public static void CreateTreatmentTable() { //如果不存在改数据库文件,则创建该数据库文件 if (!System.IO.File.Exists(dbPath)) { SQLiteHelper.CreateDB(dbPath); } SQLiteHelper db = new SQLiteHelper(dbPath); string sql = @"CREATE TABLE Treatment ( t_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, t_disease TEXT NOT NULL, t_body_parts TEXT NOT NULL, t_rate1 real, t_rate2 real, t_rate3 real, t_rate4 real, t_rate5 real, t_rate6 real, t_rate7 real, t_rate8 real, t_rate9 real, t_rate10 real, t_num1 integer, t_num2 integer, t_num3 integer, t_num4 integer, t_num5 integer, t_num6 integer, t_num7 integer, t_num8 integer, t_num9 integer, t_num10 integer, t_interval_time real, t_repeat_times integer )"; db.ExecuteNonQuery(sql, null); } //创建诊疗记录表 public static void CreateTreatmentRecordTable() { //如果不存在改数据库文件,则创建该数据库文件 if (!System.IO.File.Exists(dbPath)) { SQLiteHelper.CreateDB(dbPath); } SQLiteHelper db = new SQLiteHelper(dbPath); string sql = @"CREATE TABLE Treatment_record( id integer primary key autoincrement not null , patient_id integer, strength integer, duration integer, start_time datetime )"; db.ExecuteNonQuery(sql, null); } #endregion #region 数据库整体操作 /// /// optmizeDatabase:从数据库中清空空闲列表 /// public static int optmizeDatabase() { string sql = "vacuum"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[] { }; return db.ExecuteNonQuery(sql, parameters); } #endregion #region Patient表各类操作 //Patient表的插入 public static void InsertPatientData(string record_id,string name, string gender, double height, double weight,int pregnancy_times,DateTime birth_date,string phone,string address,string history,string diagnosis, string name_py) { string sql = "INSERT INTO Patient(p_record_id,p_name,p_gender,p_height,p_weight,p_pregnancy_time,p_birthdate,p_phone,p_address,p_history,p_diagnosis, p_name_py)"+ "values(@record_id,@name,@gender,@height,@weight,@pregnancy_times,@birthdate,@phone,@address,@history,@diagnosis,@name_py)"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@record_id",record_id), new SQLiteParameter("@name",name), new SQLiteParameter("@gender",gender), new SQLiteParameter("@height",height), new SQLiteParameter("@weight",weight), new SQLiteParameter("@pregnancy_times",pregnancy_times), new SQLiteParameter("@birthdate",birth_date), new SQLiteParameter("@phone",phone), new SQLiteParameter("@address",address), new SQLiteParameter("@history",history), new SQLiteParameter("@diagnosis",diagnosis), new SQLiteParameter("@name_py",name_py), }; db.ExecuteNonQuery(sql, parameters); } //Patient表的更新 public static void UpdatePatientData(string id, string record_id, string name, string gender, double height, double weight, int pregnancy_times, DateTime birth_date, string phone, string address, string history, string diagnosis, string name_py) { //Boolean a = false; string sql = "UPDATE Patient SET p_record_id=@record_id,p_name=@name,p_gender=@gender,p_height=@height,p_weight=@weight,p_pregnancy_time=@pregnancy_times,p_birthdate=@birth_date,p_phone=@phone,p_address=@address,p_history=@history,p_diagnosis=@diagnosis,p_name_py=@name_py WHERE p_id=@id"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@record_id",record_id), new SQLiteParameter("@name",name), new SQLiteParameter("@gender",gender), new SQLiteParameter("@height",height), new SQLiteParameter("@weight",weight), new SQLiteParameter("@pregnancy_times",pregnancy_times), new SQLiteParameter("@birth_date",birth_date), new SQLiteParameter("@phone",phone), new SQLiteParameter("@address",address), new SQLiteParameter("@history",history), new SQLiteParameter("@diagnosis",diagnosis), new SQLiteParameter("@name_py",name_py), new SQLiteParameter("@id",id), }; //System.Console.WriteLine(sql); db.ExecuteNonQuery(sql, parameters); } //Patient表的仅更新最后XX时间的方法 //type 为 filter 或 evaluation public static void UpdatePatientDataWithLastDate(int id, string type, DateTime lastRecordDate) { string colum_select = "p_last_filter_time"; if(type == "evaluation") colum_select = "p_last_evaluation_time"; string sql = "UPDATE Patient SET " + colum_select + "=@lastRecordDate WHERE p_id=@id"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@lastRecordDate",lastRecordDate), new SQLiteParameter("@id",id) }; db.ExecuteNonQuery(sql, parameters); } //Patient表记录的删除 public static int DeletePatientItem(string id) { string sql = "UPDATE Patient SET p_delete_flag=1 WHERE p_id=@id"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@id",id) }; return db.ExecuteNonQuery(sql, parameters); } /// /// getDeletedPatients:从Patient表中获取所有已被标记为删除的数据的id /// public static List getDeletedPatients() { string sql = "SELECT p_id FROM Patient WHERE p_delete_flag = 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[] { }; List result = new List(); using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { int id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); result.Add(id); } } return result; } /// /// realDeleteRecords:从Patient表中真实删除记录 /// public static int realDeleteRecords() { string sql = "DELETE from Patient WHERE p_delete_flag=1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ }; return db.ExecuteNonQuery(sql, parameters); } //检查Patient表是否重复，条件提病例号.重复返回true，否则返回false public static bool checkRepeatPatient(string record_id) { string sql = "SELECT * FROM Patient WHERE p_record_id = @record_id AND p_delete_flag = 0"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@record_id",record_id) }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { reader.Close(); return true; } } return false; } //通过id主键查找Patient public static Patient getPatientById(string id) { string sql = "SELECT * FROM Patient WHERE p_id = @id AND p_delete_flag = 0 LIMIT 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@id",id), }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { Patient temp = new Patient(); temp.p_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); temp.p_name = reader.IsDBNull(1) ? "" : reader.GetString(1); temp.p_name_py = reader.IsDBNull(2) ? "" : reader.GetString(2); temp.p_record_id = reader.IsDBNull(3) ? "" : reader.GetString(3); temp.p_gender = reader.IsDBNull(4) ? "" : reader.GetString(4); temp.p_birthdate = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5); temp.p_height = reader.IsDBNull(6) ? 0 : reader.GetDouble(6); temp.p_weight = reader.IsDBNull(7) ? 0 : reader.GetDouble(7); temp.p_pregnancy_time = reader.IsDBNull(8) ? 0 : reader.GetInt32(8); temp.p_phone = reader.IsDBNull(9) ? "" : reader.GetString(9); temp.p_address = reader.IsDBNull(10) ? "" : reader.GetString(10); temp.p_history = reader.IsDBNull(11) ? "" : reader.GetString(11); temp.p_diagnosis = reader.IsDBNull(12) ? "" : reader.GetString(12); temp.doc_id = reader.IsDBNull(13) ? 0 : reader.GetInt32(13); temp.p_last_filter_time = reader.IsDBNull(14) ? DateTime.MinValue : reader.GetDateTime(14); temp.p_last_evaluation_time = reader.IsDBNull(15) ? DateTime.MinValue : reader.GetDateTime(15); temp.p_delete_flag = reader.IsDBNull(16) ? false : reader.GetBoolean(16); ; return temp; } } return null; } #endregion #region Question_template表的相关操作 public static List getQuestions(string type = "filter") { List questions = new List(); string condition = "q_type = 1"; if(type == "evaluation") { condition = "q_type = 2"; } string sql = "SELECT * FROM Question_template WHERE " + condition + " order by q_number asc"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{}; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { Question temp = new Question(); temp.q_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); temp.q_type = reader.IsDBNull(1) ? 0 : reader.GetInt32(1); temp.q_number = reader.IsDBNull(2) ? 0 : reader.GetInt32(2); temp.q_title = reader.IsDBNull(3) ? "" : reader.GetString(3); temp.q_content = reader.IsDBNull(4) ? "" : reader.GetString(4); temp.prior_a_id = reader.IsDBNull(5) ? 0 : reader.GetInt32(5); questions.Add(temp); } } return questions; } public static Question getQuestionById(int qid) { string sql = "SELECT * FROM Question_template WHERE q_id=@qid LIMIT 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@qid",qid), }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { Question temp = new Question(); temp.q_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); temp.q_type = reader.IsDBNull(1) ? 0 : reader.GetInt32(1); temp.q_number = reader.IsDBNull(2) ? 0 : reader.GetInt32(2); temp.q_title = reader.IsDBNull(3) ? "" : reader.GetString(3); temp.q_content = reader.IsDBNull(4) ? "" : reader.GetString(4); temp.prior_a_id = reader.IsDBNull(5) ? 0 : reader.GetInt32(5); return temp; } } return null; } #endregion #region Answer_template表相关操作 public static List getAnswersByQid(int qid) { List questions = new List(); string sql = "SELECT * FROM Answer_template WHERE q_id=@qid order by a_id asc"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@qid",qid), }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { Answer temp = new Answer(); temp.a_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); temp.q_id = reader.IsDBNull(1) ? 0 : reader.GetInt32(1); temp.a_content = reader.IsDBNull(2) ? "" : reader.GetString(2); temp.a_value = reader.IsDBNull(3) ? 0 : reader.GetInt32(3); temp.a_pain = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); temp.next_q_id = reader.IsDBNull(5) ? 0 : reader.GetInt32(5); temp.a_description_text = reader.IsDBNull(6) ? "" : reader.GetString(6); temp.a_description_img = reader.IsDBNull(7) ? "" : reader.GetString(7); temp.a_suggestion_text = reader.IsDBNull(8) ? "" : reader.GetString(8); temp.a_suggestion_img = reader.IsDBNull(9) ? "" : reader.GetString(9); questions.Add(temp); } } return questions; } public static Answer getAnswerById(int aid) { string sql = "SELECT * FROM Answer_template WHERE a_id=@aid LIMIT 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@aid",aid), }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { Answer temp = new Answer(); temp.a_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); temp.q_id = reader.IsDBNull(1) ? 0 : reader.GetInt32(1); temp.a_content = reader.IsDBNull(2) ? "" : reader.GetString(2); temp.a_value = reader.IsDBNull(3) ? 0 : reader.GetInt32(3); temp.a_pain = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); temp.next_q_id = reader.IsDBNull(5) ? 0 : reader.GetInt32(5); temp.a_description_text = reader.IsDBNull(6) ? "" : reader.GetString(6); temp.a_description_img = reader.IsDBNull(7) ? "" : reader.GetString(7); temp.a_suggestion_text = reader.IsDBNull(8) ? "" : reader.GetString(8); temp.a_suggestion_img = reader.IsDBNull(9) ? "" : reader.GetString(9); return temp; } } return null; } public static int loopInsertAnswers() { int start_a_id = 182; for(int i = 1; i <= 6; ++i) { int new_i = 9 + (i - 1) * 3; for(int j = 1; j <= 10; ++j) { string sql = "INSERT INTO Answer_template(a_id,q_id,a_content,a_value,a_description_text)" + "values(@a_id,@q_id,@a_content,@a_value,@a_description_text)"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@a_id",start_a_id), new SQLiteParameter("@q_id",new_i), new SQLiteParameter("@a_content","疼痛："+j+"分"), new SQLiteParameter("@a_value",2.ToString()), //new SQLiteParameter("@next_q_id",), new SQLiteParameter("@a_description_text",(27+(i-1)*6).ToString()+".pdf") }; db.ExecuteNonQuery(sql, parameters); ++start_a_id; } } return 0; } #endregion #region Record表相关操作 /// /// 插入报告一份，并返回报告的ID /// /// 1-筛查，2-评估 /// 病例ID /// 医生ID /// 如是评估报告，那么其遵顼的筛查记录ID /// 报告时间 /// 报告计数 /// 报告内容 public static int insertRecord(int r_type, int p_id, int d_id, int filter_id, DateTime r_time, int r_count, string r_selection) { string sql = "INSERT INTO Record(r_type,p_id,d_id,filter_id,r_time,r_count,r_selection)" + "values(@r_type,@p_id,@d_id,@filter_id,@r_time,@r_count,@r_selection)"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@r_type",r_type), new SQLiteParameter("@p_id",p_id), new SQLiteParameter("@d_id",d_id), new SQLiteParameter("@filter_id",filter_id), new SQLiteParameter("@r_time",r_time), new SQLiteParameter("@r_count",r_count), new SQLiteParameter("@r_selection",r_selection) }; db.ExecuteNonQuery(sql, parameters); //返回所插入的r_id sql = "SELECT MAX(r_id) FROM Record;"; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { int max_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); return max_id; } } return 0; } /// /// 根据ID返回一份报告记录 /// /// 记录ID public static Record getRecordByID(int r_id) { string sql = "SELECT * FROM Record WHERE r_id=@r_id LIMIT 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@r_id",r_id), }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { Record temp = new Record(); temp.r_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); temp.r_type = reader.IsDBNull(1) ? 0 : reader.GetInt32(1); temp.p_id = reader.IsDBNull(2) ? 0 : reader.GetInt32(2); temp.d_id = reader.IsDBNull(3) ? 0 : reader.GetInt32(3); temp.filter_id = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); temp.r_time = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5); temp.r_count = reader.IsDBNull(6) ? 0 : reader.GetInt32(6); temp.r_selection = reader.IsDBNull(7) ? "" : reader.GetString(7); return temp; } } return null; } /// /// 根据ID返回一份报告记录 /// /// 记录ID public static List getRecordsByPid(int p_id, bool evaluation_only) { string condition = ""; if (evaluation_only) { condition = "and r_type=2 "; } string sql = "SELECT * FROM Record WHERE p_id=@p_id "+ condition + "order by r_time desc"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@p_id",p_id), }; List results = new List(); using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { Record temp = new Record(); temp.r_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); temp.r_type = reader.IsDBNull(1) ? 0 : reader.GetInt32(1); if (temp.r_type == 2) temp.r_type_d = "评测报告"; else temp.r_type_d = "筛查报告"; temp.p_id = reader.IsDBNull(2) ? 0 : reader.GetInt32(2); temp.d_id = reader.IsDBNull(3) ? 0 : reader.GetInt32(3); temp.filter_id = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); temp.r_time = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5); temp.r_count = reader.IsDBNull(6) ? 0 : reader.GetInt32(6); temp.r_selection = reader.IsDBNull(7) ? "" : reader.GetString(7); results.Add(temp); } } return results; } /// /// 选取近days里某一病例的type类报告一份 /// /// 病例ID /// 1-筛查,2-评估 /// 前多少天内有效 public static Record getLatestXTypeRecordInYDays(int p_id, int r_type, int days) { DateTime dt = DateTime.Now; string sql = "SELECT * FROM Record WHERE p_id=@p_id and r_time >=@r_time and r_type=@r_type ORDER BY r_time desc LIMIT 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@p_id",p_id), new SQLiteParameter("@r_type",r_type), new SQLiteParameter("@r_time",dt.AddDays(-days)), }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { Record temp = new Record(); temp.r_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); temp.r_type = reader.IsDBNull(1) ? 0 : reader.GetInt32(1); temp.p_id = reader.IsDBNull(2) ? 0 : reader.GetInt32(2); temp.d_id = reader.IsDBNull(3) ? 0 : reader.GetInt32(3); temp.filter_id = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); temp.r_time =reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5); temp.r_count = reader.IsDBNull(6) ? 0 : reader.GetInt32(6); temp.r_selection = reader.IsDBNull(7) ? "" : reader.GetString(7); return temp; } } return null; } /// /// 将r_id对应的记录生成计数+1 /// /// 记录ID public static int plusOneCountOnRecordByRid(int r_id) { string sql = "UPDATE Record SET r_count=r_count+1 WHERE r_id=@r_id"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@r_id",r_id) }; return db.ExecuteNonQuery(sql, parameters); } /// /// 删除病例pid对应的所有记录 /// /// 记录ID public static int deleteRecordByPid(int p_id) { string sql = "DELETE FROM Record WHERE p_id=@p_id"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@p_id",p_id) }; return db.ExecuteNonQuery(sql, parameters); } #endregion #region doctor表相关的操作 //医生登录操作 public static doctor doctorLogin(string name,string passwordHash) { string sql = "SELECT * FROM Doctor WHERE name = @name and pwd_hash=@passwordHash and delete_flag =0 LIMIT 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@name",name), new SQLiteParameter("@passwordHash",passwordHash), }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { doctor oneDoctor = new doctor(); oneDoctor.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); oneDoctor.name = name; oneDoctor.phone_number = reader.IsDBNull(3) ? "" : reader.GetString(3); oneDoctor.director_flag = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); oneDoctor.privilege_flag = reader.IsDBNull(6) ? -1 : reader.GetInt32(6); return oneDoctor; } } return null; } //主任登录操作 public static doctor directorLogin(string name, string passwordHash) { string sql = "SELECT * FROM Doctor WHERE name = @name and pwd_hash=@passwordHash and delete_flag = 0 and director_flag = 1 LIMIT 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@name",name), new SQLiteParameter("@passwordHash",passwordHash), }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { doctor oneDoctor = new doctor(); oneDoctor.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); oneDoctor.name = name; oneDoctor.phone_number = reader.IsDBNull(3) ? "" : reader.GetString(3); oneDoctor.director_flag = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); return oneDoctor; } } return null; } //厂商登录操作 public static doctor OEMLogin(string name, string passwordHash) { string sql = "SELECT * FROM Doctor WHERE name = @name and pwd_hash=@passwordHash and delete_flag = 0 and privilege_flag = 1 LIMIT 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@name",name), new SQLiteParameter("@passwordHash",passwordHash), }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { doctor oneDoctor = new doctor(); oneDoctor.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); oneDoctor.name = name; oneDoctor.phone_number = reader.IsDBNull(3) ? "" : reader.GetString(3); oneDoctor.director_flag = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); return oneDoctor; } } return null; } //查询医生用户名是否存在 存在返回true public static bool checkRepeatDoctor(string name) { string sql = "SELECT * FROM Doctor WHERE name = @name AND delete_flag = 0"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@name",name) }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { reader.Close(); return true; } } return false; } //通过id主键查找Doctor public static doctor getDoctorById(string id) { string sql = "SELECT * FROM Doctor WHERE id = @id AND delete_flag = 0 LIMIT 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@id",id), }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { doctor temp = new doctor(); temp.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); temp.name = reader.IsDBNull(1) ? "" : reader.GetString(1); temp.pwd_hash = reader.IsDBNull(2) ? "" : reader.GetString(2); temp.phone_number = reader.IsDBNull(3) ? "" : reader.GetString(3); temp.director_flag = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); return temp; } } return null; } //查找Doctor 返回目前还使用的医生id public static int getDoctorIdByNoDelete() { string sql = "SELECT * FROM Doctor WHERE delete_flag = 0 LIMIT 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{}; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { doctor temp = new doctor(); temp.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); temp.name = reader.IsDBNull(1) ? "" : reader.GetString(1); temp.pwd_hash = reader.IsDBNull(2) ? "" : reader.GetString(2); temp.phone_number = reader.IsDBNull(3) ? "" : reader.GetString(3); temp.director_flag = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); return temp.id; } } return -1; } //Doctort表的插入 public static void InsertDoctorData(string name, string pwd_hash, string phone_number, Boolean director_flag = false, Boolean delete_flag = false) { string sql = "INSERT INTO Doctor(name,pwd_hash,phone_number,director_flag,delete_flag)values(@name,@pwd_hash,@phone_number,@director_flag,@delete_flag)"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@name",name), new SQLiteParameter("@pwd_hash",pwd_hash), new SQLiteParameter("@phone_number",phone_number), new SQLiteParameter("@director_flag",director_flag), new SQLiteParameter("@delete_flag",delete_flag) }; db.ExecuteNonQuery(sql, parameters); } //Doctor表的更新 public static void UpdateDoctorData(string id, string name, string pwd_hash, string phone_number, Boolean director_flag = false, Boolean delete_flag = false) { //Boolean a = false; string sql = "UPDATE Doctor SET name=@name,pwd_hash=@pwd_hash,phone_number=@phone_number,director_flag=@director_flag,delete_flag=@delete_flag WHERE id=@id"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@name",name), new SQLiteParameter("@pwd_hash",pwd_hash), new SQLiteParameter("@phone_number",phone_number), new SQLiteParameter("@director_flag",director_flag), new SQLiteParameter("@delete_flag",delete_flag), new SQLiteParameter("@id",id) }; db.ExecuteNonQuery(sql, parameters); } //Doctor表记录的删除 public static int DeleteDoctorItem(string id) { string sql = "UPDATE Doctor SET delete_flag=1 WHERE id=@id"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@id",id) }; return db.ExecuteNonQuery(sql, parameters); } #endregion #region 配置表Config的相关操作 //查找所有的expression的content列 public static void GetAllConfigContent(Dictionary target) { target.Clear(); string sql = "SELECT key,value FROM Config WHERE 1"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { string tempKey = reader.IsDBNull(0) ? "" : reader.GetString(0); string tempValue = reader.IsDBNull(1) ? "" : reader.GetString(1); if (tempKey == "") { break; } //KeyValuePair temp = new KeyValuePair(tempKey,0); target.Add(tempKey, tempValue); } } } //更新激活码信息 public static void UpdateOrganizationName(string name) { //Boolean a = false; string sql = "UPDATE Config SET value=@name WHERE key='organization_name'"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@name",name) }; db.ExecuteNonQuery(sql, parameters); } #endregion #region Treatment治疗方案表 // 增加 public static void insertTreatment(string disease, string bodyParts, double rate1, double rate2, double rate3, double rate4, double rate5, double rate6, double rate7, double rate8, double rate9, double rate10, int num1, int num2, int num3, int num4, int num5, int num6, int num7, int num8, int num9, int num10, double intervalTime, int repeatTimes) { string sql = "INSERT INTO Treatment(t_disease, t_body_parts, t_rate1, t_rate2, t_rate3, t_rate4, t_rate5, t_rate6, t_rate7, t_rate8, t_rate9, t_rate10, t_num1, t_num2, t_num3, t_num4, t_num5, t_num6, t_num7, t_num8, t_num9, t_num10, t_interval_time, t_repeat_times)" + "values(@disease, @bodyParts, @rate1, @rate2, @rate3, @rate4, @rate5, @rate6, @rate7, @rate8, @rate9, @rate10, @num1, @num2, @num3, @num4, @num5, @num6, @num7, @num8, @num9, @num10, @intervalTime, @repeatTimes)"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@disease", disease), new SQLiteParameter("@bodyParts", bodyParts), new SQLiteParameter("@rate1", rate1), new SQLiteParameter("@rate2", rate2), new SQLiteParameter("@rate3", rate3), new SQLiteParameter("@rate4", rate4), new SQLiteParameter("@rate5", rate5), new SQLiteParameter("@rate6", rate6), new SQLiteParameter("@rate7", rate7), new SQLiteParameter("@rate8", rate8), new SQLiteParameter("@rate9", rate9), new SQLiteParameter("@rate10", rate10), new SQLiteParameter("@num1", num1), new SQLiteParameter("@num2", num2), new SQLiteParameter("@num3", num3), new SQLiteParameter("@num4", num4), new SQLiteParameter("@num5", num5), new SQLiteParameter("@num6", num6), new SQLiteParameter("@num7", num7), new SQLiteParameter("@num8", num8), new SQLiteParameter("@num9", num9), new SQLiteParameter("@num10", num10), new SQLiteParameter("@intervalTime", intervalTime), new SQLiteParameter("@repeatTimes", repeatTimes), }; db.ExecuteNonQuery(sql, parameters); } // 删除 public static int deleteTreatment(int id) { string sql = "DELETE from Treatment WHERE t_id = @id"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@id", id), }; return db.ExecuteNonQuery(sql, parameters); } // 修改 public static void updateTreatment(int id, string disease, string bodyParts, double rate1, double rate2, double rate3, double rate4, double rate5, double rate6, double rate7, double rate8, double rate9, double rate10, int num1, int num2, int num3, int num4, int num5, int num6, int num7, int num8, int num9, int num10, double intervalTime, int repeatTimes) { string sql = "UPDATE Treatment SET " + "t_disease = @disease, " + "t_body_parts = @bodyParts, " + "t_rate1 = @rate1, " + "t_rate2 = @rate2, " + "t_rate3 = @rate3, " + "t_rate4 = @rate4, " + "t_rate5 = @rate5, " + "t_rate6 = @rate6, " + "t_rate7 = @rate7, " + "t_rate8 = @rate8, " + "t_rate9 = @rate9, " + "t_rate10 = @rate10, " + "t_num1 = @num1, " + "t_num2 = @num2, " + "t_num3 = @num3, " + "t_num4 = @num4, " + "t_num5 = @num5, " + "t_num6 = @num6, " + "t_num7 = @num7, " + "t_num8 = @num8, " + "t_num9 = @num9, " + "t_num10 = @num10, " + "t_interval_time = @intervalTime, " + "t_repeat_times = @repeatTimes " + "WHERE t_id = @id "; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@id", id), new SQLiteParameter("@disease", disease), new SQLiteParameter("@bodyParts", bodyParts), new SQLiteParameter("@rate1", rate1), new SQLiteParameter("@rate2", rate2), new SQLiteParameter("@rate3", rate3), new SQLiteParameter("@rate4", rate4), new SQLiteParameter("@rate5", rate5), new SQLiteParameter("@rate6", rate6), new SQLiteParameter("@rate7", rate7), new SQLiteParameter("@rate8", rate8), new SQLiteParameter("@rate9", rate9), new SQLiteParameter("@rate10", rate10), new SQLiteParameter("@num1", num1), new SQLiteParameter("@num2", num2), new SQLiteParameter("@num3", num3), new SQLiteParameter("@num4", num4), new SQLiteParameter("@num5", num5), new SQLiteParameter("@num6", num6), new SQLiteParameter("@num7", num7), new SQLiteParameter("@num8", num8), new SQLiteParameter("@num9", num9), new SQLiteParameter("@num10", num10), new SQLiteParameter("@intervalTime", intervalTime), new SQLiteParameter("@repeatTimes", repeatTimes), new SQLiteParameter("@id", id), }; //System.Console.WriteLine(sql); db.ExecuteNonQuery(sql, parameters); } // 查询 public static Treatment getTreatmentById(int id) { string sql = "SELECT * FROM Treatment WHERE t_id = @id"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@id",id), }; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { Treatment temp = new Treatment(); temp.id = reader.IsDBNull( 0) ? 0 : reader.GetInt32( 0); temp.disease = reader.IsDBNull( 1) ? "" : reader.GetString( 1); temp.bodyParts = reader.IsDBNull( 2) ? "" : reader.GetString( 2); temp.rate1 = reader.IsDBNull( 3) ? 0 : reader.GetDouble( 3); temp.rate2 = reader.IsDBNull( 4) ? 0 : reader.GetDouble( 4); temp.rate3 = reader.IsDBNull( 5) ? 0 : reader.GetDouble( 5); temp.rate4 = reader.IsDBNull( 6) ? 0 : reader.GetDouble( 6); temp.rate5 = reader.IsDBNull( 7) ? 0 : reader.GetDouble( 7); temp.rate6 = reader.IsDBNull( 8) ? 0 : reader.GetDouble( 8); temp.rate7 = reader.IsDBNull( 9) ? 0 : reader.GetDouble( 9); temp.rate8 = reader.IsDBNull(10) ? 0 : reader.GetDouble(10); temp.rate9 = reader.IsDBNull(11) ? 0 : reader.GetDouble(11); temp.rate10 = reader.IsDBNull(12) ? 0 : reader.GetDouble(12); temp.num1 = reader.IsDBNull(13) ? 0 : reader.GetInt32( 13); temp.num2 = reader.IsDBNull(14) ? 0 : reader.GetInt32( 14); temp.num3 = reader.IsDBNull(15) ? 0 : reader.GetInt32( 15); temp.num4 = reader.IsDBNull(16) ? 0 : reader.GetInt32( 16); temp.num5 = reader.IsDBNull(17) ? 0 : reader.GetInt32( 17); temp.num6 = reader.IsDBNull(18) ? 0 : reader.GetInt32( 18); temp.num7 = reader.IsDBNull(19) ? 0 : reader.GetInt32( 19); temp.num8 = reader.IsDBNull(20) ? 0 : reader.GetInt32( 20); temp.num9 = reader.IsDBNull(21) ? 0 : reader.GetInt32( 21); temp.num10 = reader.IsDBNull(22) ? 0 : reader.GetInt32( 22); temp.intervalTime = reader.IsDBNull(23) ? 0 : reader.GetDouble(23); temp.repeatTimes = reader.IsDBNull(24) ? 0 : reader.GetInt32( 24); return temp; } } return null; } public static List getALLTreatment() { string sql = "SELECT * FROM Treatment"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[] { }; List result = new List(); using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { Treatment temp = new Treatment(); temp.id = reader.IsDBNull( 0) ? 0 : reader.GetInt32( 0); temp.disease = reader.IsDBNull( 1) ? "" : reader.GetString( 1); temp.bodyParts = reader.IsDBNull( 2) ? "" : reader.GetString( 2); temp.rate1 = reader.IsDBNull( 3) ? 0 : reader.GetDouble( 3); temp.rate2 = reader.IsDBNull( 4) ? 0 : reader.GetDouble( 4); temp.rate3 = reader.IsDBNull( 5) ? 0 : reader.GetDouble( 5); temp.rate4 = reader.IsDBNull( 6) ? 0 : reader.GetDouble( 6); temp.rate5 = reader.IsDBNull( 7) ? 0 : reader.GetDouble( 7); temp.rate6 = reader.IsDBNull( 8) ? 0 : reader.GetDouble( 8); temp.rate7 = reader.IsDBNull( 9) ? 0 : reader.GetDouble( 9); temp.rate8 = reader.IsDBNull(10) ? 0 : reader.GetDouble(10); temp.rate9 = reader.IsDBNull(11) ? 0 : reader.GetDouble(11); temp.rate10 = reader.IsDBNull(12) ? 0 : reader.GetDouble(12); temp.num1 = reader.IsDBNull(13) ? 0 : reader.GetInt32( 13); temp.num2 = reader.IsDBNull(14) ? 0 : reader.GetInt32( 14); temp.num3 = reader.IsDBNull(15) ? 0 : reader.GetInt32( 15); temp.num4 = reader.IsDBNull(16) ? 0 : reader.GetInt32( 16); temp.num5 = reader.IsDBNull(17) ? 0 : reader.GetInt32( 17); temp.num6 = reader.IsDBNull(18) ? 0 : reader.GetInt32( 18); temp.num7 = reader.IsDBNull(19) ? 0 : reader.GetInt32( 19); temp.num8 = reader.IsDBNull(20) ? 0 : reader.GetInt32( 20); temp.num9 = reader.IsDBNull(21) ? 0 : reader.GetInt32( 21); temp.num10 = reader.IsDBNull(22) ? 0 : reader.GetInt32( 22); temp.intervalTime = reader.IsDBNull(23) ? 0 : reader.GetDouble(23); temp.repeatTimes = reader.IsDBNull(24) ? 0 : reader.GetInt32( 24); result.Add(temp); } } return result; } #endregion #region 以下是测试功能，请在生产环境之前删除这些功能 public static byte[] getRecordData() { string dbPathTest = @"D:\\sourceCode\\github\\Junde_New\bin\\Debug\\data\\data.db"; string sql = "SELECT Data FROM Record WHERE id = 22"; SQLiteHelper db = new SQLiteHelper(dbPathTest); Console.WriteLine("database ok!"); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { byte[] buffer = null; if (reader.HasRows) { reader.Read(); long len = reader.GetBytes(reader.GetOrdinal("Data"), 0, null, 0, 0); Console.WriteLine("len is :" + len.ToString()); buffer = new byte[len]; len = reader.GetBytes(reader.GetOrdinal("Data"), 0, buffer, 0, (int)len); for(int i =0;i<100;i++) { Console.WriteLine(buffer[i]); } return buffer; //System.IO.MemoryStream ms = new System.IO.MemoryStream(buffer); //System.Drawing.Image iamge = System.Drawing.Image.FromStream(ms); //pictureBox1.Image = iamge; } } return null; } #endregion //Powered BY: WC /// /// 插入诊疗记录一份，并返回记录的ID /// /// 患者id /// 治疗强度 /// 治疗持续时间 /// 治疗开始时间 public static int insertTreatmentRecord(TreatmentRecord treatmentRecordToInsert) { string sql = "INSERT INTO Treatment_record(patient_id,patient_record_id,strength,duration,start_time)" + "values(@p_id,@patient_record_id,@strength,@duration,@s_time)"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[]{ new SQLiteParameter("@p_id",treatmentRecordToInsert.patient_id), new SQLiteParameter("@strength",treatmentRecordToInsert.strength), new SQLiteParameter("@patient_record_id",treatmentRecordToInsert.patient_record_id), new SQLiteParameter("@duration",treatmentRecordToInsert.duration), new SQLiteParameter("@s_time",treatmentRecordToInsert.start_time), }; db.ExecuteNonQuery(sql, parameters); //返回所插入的id sql = "SELECT MAX(id) FROM Treatment_record;"; using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { int max_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); return max_id; } } return 0; } //Powered BY: WC /// /// 查出对应患者的所有诊疗记录 /// /// 待查询诊疗记录列表的患者的id public static List getAllTreatmentRecordByPatientId(int patient_id) { string sql = "SELECT * FROM Treatment_record where patient_id = @patient_id"; SQLiteHelper db = new SQLiteHelper(dbPath); SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@patient_id",patient_id) }; List treatmentRecordResultList = new List(); using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters)) { while (reader.Read()) { TreatmentRecord tempTreatmentRecord = new TreatmentRecord(); tempTreatmentRecord.id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); tempTreatmentRecord.patient_id = reader.IsDBNull(1) ? 0 : reader.GetInt32(1); tempTreatmentRecord.strength = reader.IsDBNull(2) ? 0 : reader.GetInt32(2); tempTreatmentRecord.duration = reader.IsDBNull(3) ? 0 : reader.GetDouble(3); tempTreatmentRecord.start_time = reader.IsDBNull(4) ? new DateTime() : reader.GetDateTime(4); tempTreatmentRecord.patient_record_id = reader.IsDBNull(5) ? "" : reader.GetString(5); treatmentRecordResultList.Add(tempTreatmentRecord); } } return treatmentRecordResultList; } } }