Laola/WpfTest1/SQLite/SQLiteModel.cs

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);
        }

        #endregion

        #region 数据库整体操作
        /// <summary>
        /// optmizeDatabase:从数据库中清空空闲列表
        /// </summary>
        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);
        }
        /// <summary>
        /// getDeletedPatients:从Patient表中获取所有已被标记为删除的数据的id
        /// </summary>
        public static List<int> getDeletedPatients()
        {
            string sql = "SELECT p_id FROM Patient WHERE p_delete_flag = 1";
            SQLiteHelper db = new SQLiteHelper(dbPath);
            SQLiteParameter[] parameters = new SQLiteParameter[] { };
            List<int> result = new List<int>();
            using (SQLiteDataReader reader = db.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    int id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
                    result.Add(id);
                }
            }
            return result;
        }
        /// <summary>
        /// realDeleteRecords:从Patient表中真实删除记录
        /// </summary>
        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<Question> getQuestions(string type = "filter")
        {
            List<Question> questions = new List<Question>();
            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<Answer> getAnswersByQid(int qid)
        {
            List<Answer> questions = new List<Answer>();
            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.next_q_id = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
                    temp.a_description_text = reader.IsDBNull(5) ? "" : reader.GetString(5);
                    temp.a_description_img = reader.IsDBNull(6) ? "" : reader.GetString(6);
                    temp.a_suggestion_text = reader.IsDBNull(7) ? "" : reader.GetString(7);
                    temp.a_suggestion_img = reader.IsDBNull(8) ? "" : reader.GetString(8);
                    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.next_q_id = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
                    temp.a_description_text = reader.IsDBNull(5) ? "" : reader.GetString(5);
                    temp.a_description_img = reader.IsDBNull(6) ? "" : reader.GetString(6);
                    temp.a_suggestion_text = reader.IsDBNull(7) ? "" : reader.GetString(7);
                    temp.a_suggestion_img = reader.IsDBNull(8) ? "" : reader.GetString(8);
                    return temp;
                }
            }
            return null;
        }
        #endregion

        #region Record表相关操作

        /// <summary>
        /// 插入报告一份，并返回报告的ID
        /// </summary>
        /// <param name="r_type">1-筛查，2-评估</param>
        /// <param name="p_id">病例ID</param>
        /// <param name="d_id">医生ID</param>
        /// <param name="filter_id">如是评估报告，那么其遵顼的筛查记录ID</param>
        /// <param name="r_time">报告时间</param>
        /// <param name="r_count">报告计数</param>
        /// <param name="r_selection">报告内容</param>
        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;
        }

        /// <summary>
        /// 根据ID返回一份报告记录
        /// </summary>
        /// <param name="r_id">记录ID</param>
        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;
        }

        /// <summary>
        /// 根据ID返回一份报告记录
        /// </summary>
        /// <param name="r_id">记录ID</param>
        public static List<Record> getRecordsByPid(int p_id)
        {
            string sql = "SELECT * FROM Record WHERE p_id=@p_id";
            SQLiteHelper db = new SQLiteHelper(dbPath);
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                                                               new SQLiteParameter("@p_id",p_id),
                                                               };
            List<Record> results = new List<Record>();
            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;
        }

        /// <summary>
        /// 选取近days里某一病例的type类报告一份
        /// </summary>
        /// <param name="p_id">病例ID</param>
        /// <param name="r_type">1-筛查,2-评估</param>
        /// <param name="days">前多少天内有效</param>
        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 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;
        }

        /// <summary>
        /// 将r_id对应的记录生成计数+1
        /// </summary>
        /// <param name="r_id">记录ID</param>
        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);
        }

        /// <summary>
        /// 删除病例pid对应的所有记录
        /// </summary>
        /// <param name="r_id">记录ID</param>
        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<string, string> 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<string, double> temp = new KeyValuePair<string, double>(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 以下是测试功能，请在生产环境之前删除这些功能
        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
    }
}