public bool SaveTPR(TPRData thisTPR)
        {
            int  rows;
            bool y = false;
            //資料庫連線字串
            TPECHDBService tpech = TPECHDBService.getInstance();
            DbConnection   conn  = tpech.GetTPECHConnection("B");

            conn.Open();

            try
            {
                //SQL INSERT 指令
                DbCommand command = conn.CreateCommand();
                command.CommandText = "INSERT INTO INPTPRM" +
                                      "(PAT_NO, MONITOR_DATE, MONITOR_TIME, TEMPERATURE, WEIGHT, PULSE, BREATH, DBP, SBP, SPO2, OP_DATE)" +
                                      "VALUES ('" + thisTPR.PatNO + "', '" + thisTPR.MonitorDate + "', '" + thisTPR.MonitorTime + "', " + thisTPR.Temperature + ", " + thisTPR.Weight + ", " + thisTPR.Pulse + ", " + thisTPR.Breath + ", " + thisTPR.DBP + ", " + thisTPR.SBP + ", " + thisTPR.SPO2 + ", sysdate)";
                rows = command.ExecuteNonQuery();
                //如果有異動的資料改變布林值
                if (rows > 0)
                {
                    y = true;
                }
            }
            finally
            {
                conn.Close();
            }
            //回傳是否有資料異動之布林值
            return(y);
        }
        public PatientInfo GetPatientData(string patNO)
        {
            //資料的格式
            PatientInfo info = new PatientInfo();

            //connect to database
            TPECHDBService tpech = TPECHDBService.getInstance();
            DbConnection   conn  = tpech.GetTPECHConnection("B");

            conn.Open();

            //selectSQL
            string    sqlselect = "SELECT * FROM MEDRECM WHERE PAT_NO = :PAT_NO";
            DbCommand command   = conn.CreateCommand();

            command.CommandText = sqlselect;
            command.CommandType = System.Data.CommandType.Text;

            DbParameter param = command.CreateParameter();

            param.Value         = patNO;
            param.DbType        = DbType.String;
            param.ParameterName = "PAT_NO";
            command.Parameters.Add(param);
            //資料庫交易
            DbTransaction trans = conn.BeginTransaction();

            try
            {
                DbDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        info.PatNO    = reader["PAT_NO"].ToString();
                        info.PatID    = reader["PAT_ID"].ToString();
                        info.PatName  = reader["PAT_NAME"].ToString();
                        info.Sex      = reader["SEX"].ToString();
                        info.Birthday = reader["BIRTHDAY"].ToString();
                    }
                }
                DataTable table = new DataTable();
                table.Load(reader);
                reader.Close();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            finally
            {
                conn.Close();
            }
            return(info);
        }
        public Inventories[] GetUnCheckInventory(string deptId)
        {
            TPECHDBService tpech = TPECHDBService.getInstance();
            DbConnection   conn  = tpech.GetTPECHConnection("B");

            conn.Open();

            string    sql     = "SELECT a.HOSP_ID,a.OWNER,a.ASS_NO,a.ASS_SERNO,a.CURKEEP_DEPT,a.CURKEEP_DEPT,b.dept_name CURKEEP_DEPT_NAME,a.CURKEEPER,a.CURPLACE,a.BUY_DATE,a.ASS_PNAME FROM VW_ASSCHKM a,sysdepm b where a.HOSP_ID = 'B' and a.CURKEEP_DEPT = :DEPTID and a.CURKEEP_DEPT = b.dept_code and a.CHK_RESULT = 'N'";
            DbCommand commamd = conn.CreateCommand();

            commamd.CommandText = sql;
            commamd.CommandType = System.Data.CommandType.Text;

            DbParameter param = commamd.CreateParameter();

            param.Value         = deptId;
            param.DbType        = System.Data.DbType.String;
            param.ParameterName = ":DEPTID";
            commamd.Parameters.Add(param);

            List <Inventories> temp = new List <Inventories>();

            temp.Clear();

            try
            {
                DbDataReader reader = commamd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Inventories data = new Inventories();
                        data.HOSP_ID           = reader["HOSP_ID"].ToString();
                        data.OWNER             = reader["OWNER"].ToString();
                        data.ASS_NO            = reader["ASS_NO"].ToString();
                        data.ASS_SERNO         = reader["ASS_SERNO"].ToString();
                        data.CURKEEP_DEPT      = reader["CURKEEP_DEPT"].ToString();
                        data.CURKEEP_DEPT_NAME = reader["CURKEEP_DEPT_NAME"].ToString();
                        data.CURKEEPER         = reader["CURKEEPER"].ToString();
                        data.CURPLACE          = reader["CURPLACE"].ToString();
                        data.BUY_DATE          = reader["BUY_DATE"].ToString();
                        data.ASS_PNAME         = reader["ASS_PNAME"].ToString();
                        temp.Add(data);
                    }
                }
                reader.Close();
            }
            finally
            {
                conn.Close();
            }
            Inventories[] uncheck = temp.ToArray();
            return(uncheck);
        }
        public bool DeleteTPR(TPRData tprData)
        {
            TPECHDBService tpech = TPECHDBService.getInstance();
            DbConnection   conn  = tpech.GetTPECHConnection("B");

            conn.Open();

            string sqlinsert = "DELETE FROM INPTPRM WHERE PAT_NO = :PAT_NO AND MONITOR_DATE = :MONITOR_DATE AND MONITOR_TIME = :MONITOR_TIME";

            DbCommand command = conn.CreateCommand();

            command.CommandText = sqlinsert;
            command.CommandType = System.Data.CommandType.Text;

            DbTransaction trans = conn.BeginTransaction();

            try
            {
                DbParameter param_no = command.CreateParameter();
                param_no.Value         = tprData.PatNO;
                param_no.DbType        = DbType.String;
                param_no.ParameterName = "PAT_NO";
                command.Parameters.Add(param_no);

                DbParameter param_date = command.CreateParameter();
                param_date.Value         = tprData.MonitorDate;
                param_date.DbType        = DbType.String;
                param_date.ParameterName = "MONITOR_DATE";
                command.Parameters.Add(param_date);

                DbParameter param_time = command.CreateParameter();
                param_time.Value         = tprData.MonitorTime;
                param_time.DbType        = DbType.String;
                param_time.ParameterName = "MONITOR_TIME";
                command.Parameters.Add(param_time);

                return(command.ExecuteNonQuery() > 0);
            }
            catch
            {
                trans.Rollback();
                return(false);
            }
            finally
            {
                trans.Commit();
                conn.Close();
            }
        }
        public PatientInfo GetPatientData(string patNo)
        {
            //資料庫連線字串
            TPECHDBService tpech = TPECHDBService.getInstance();
            DbConnection   conn  = tpech.GetTPECHConnection("B");

            conn.Open();
            //SQL SELECT指令與執行
            string sql = "select * from MEDRECM where PAT_NO =";


            DbCommand command = conn.CreateCommand();

            //command.Parameters.Add("", OracleType.VarChar, 3).Value = "a"; ;
            command.CommandText = sql + patNo;
            command.CommandType = CommandType.Text;

            try
            {
                //讀取資料
                DbDataReader reader  = command.ExecuteReader();
                PatientInfo  Patient = new PatientInfo();
                //將資料放進Patient物件
                while (reader.Read())
                {
                    Patient.PatNO    = patNo;
                    Patient.PatID    = reader.GetString(1);
                    Patient.PatName  = reader.GetString(2);
                    Patient.Sex      = reader.GetString(3);
                    Patient.Birthday = reader.GetString(4);
                }
                reader.Close();
                //回傳值(Patient物件)
                return(Patient);
            }
            finally
            {
                conn.Close();
            }
        }
        public InventoryGroup[] GetUnCheckInventoryGroupByDept()
        {
            TPECHDBService tpech = TPECHDBService.getInstance();
            DbConnection   conn  = tpech.GetTPECHConnection("B");

            conn.Open();

            string    sql     = "SELECT a.CURKEEP_DEPT,b.dept_name,count(*) cnt FROM VW_ASSCHKM a,sysdepm b where a.HOSP_ID = 'B' and a.CURKEEP_DEPT = b.dept_code and a.CHK_RESULT = 'N' group by a.CURKEEP_DEPT,b.dept_name";
            DbCommand commamd = conn.CreateCommand();

            commamd.CommandText = sql;
            commamd.CommandType = System.Data.CommandType.Text;

            List <InventoryGroup> temp = new List <InventoryGroup>();

            temp.Clear();

            try
            {
                DbDataReader reader = commamd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        InventoryGroup data = new InventoryGroup();
                        data.DeptID   = reader["CURKEEP_DEPT"].ToString();
                        data.DeptName = reader["DEPT_NAME"].ToString();
                        data.Amount   = Convert.ToInt16(reader["CNT"]);
                        temp.Add(data);
                    }
                }
                reader.Close();
            }
            finally
            {
                conn.Close();
            }
            InventoryGroup[] inv = temp.ToArray();
            return(inv);
        }
        public List <TPRData> GetTPRs(string patNO, string begDate, string endDate)
        {
            //資料庫連線字串
            TPECHDBService tpech = TPECHDBService.getInstance();
            DbConnection   conn  = tpech.GetTPECHConnection("B");

            conn.Open();
            //SQL SELECT語法語讀取
            DbCommand command = conn.CreateCommand();

            command.CommandText = "select Monitor_Date||Monitor_Time as time,Temperature,Weight,Pulse,Breath,DBP,SBP,SPO2 from INPTPRM where PAT_NO ='" + patNO + "' AND MONITOR_DATE BETWEEN '" + begDate + "' and '" + endDate + "'";
            command.CommandType = CommandType.Text;
            DbDataReader reader = command.ExecuteReader();
            //使用動態陣列存取物件資料



            List <TPRData> TPRD = new List <TPRData>();

            while (reader.Read())
            {
                TPRD.Add(new TPRData()
                {
                    Time        = reader.GetString(0),
                    Temperature = reader.GetDouble(1),
                    Weight      = reader.GetDouble(2),
                    Pulse       = reader.GetDouble(3),
                    Breath      = reader.GetDouble(4),
                    DBP         = reader.GetDouble(5),
                    SBP         = reader.GetDouble(6),
                    SPO2        = reader.GetDouble(7),
                });
            }
            conn.Close();
            //回傳TPRD物件
            return(TPRD);
        }
        public bool UpdateTPR(TPRData tprData)
        {
            TPECHDBService tpech = TPECHDBService.getInstance();
            DbConnection   conn  = tpech.GetTPECHConnection("B");

            conn.Open();

            string sqlinsert = "UPDATE INPTPRM SET TEMPERATURE = :TEMPERATURE, WEIGHT = :WEIGHT, PULSE = :PULSE, BREATH = :BREATH, DBP = :DBP, SBP = :SBP, SPO2 = :SPO2, OP_DATE = sysdate " +
                               "WHERE PAT_NO = :PAT_NO AND MONITOR_DATE = :MONITOR_DATE AND MONITOR_TIME = :MONITOR_TIME";

            DbCommand command = conn.CreateCommand();

            command.CommandText = sqlinsert;
            command.CommandType = System.Data.CommandType.Text;

            DbTransaction trans = conn.BeginTransaction();

            try
            {
                DbParameter param_temp = command.CreateParameter();
                param_temp.Value         = tprData.Temperature;
                param_temp.DbType        = DbType.Double;
                param_temp.ParameterName = "TEMPERATURE";
                command.Parameters.Add(param_temp);

                DbParameter param_weight = command.CreateParameter();
                param_weight.Value         = tprData.Weight;
                param_weight.DbType        = DbType.Double;
                param_weight.ParameterName = "WEIGHT";
                command.Parameters.Add(param_weight);

                DbParameter param_pulse = command.CreateParameter();
                param_pulse.Value         = tprData.Pulse;
                param_pulse.DbType        = DbType.Double;
                param_pulse.ParameterName = "PULSE";
                command.Parameters.Add(param_pulse);

                DbParameter param_breath = command.CreateParameter();
                param_breath.Value         = tprData.Breath;
                param_breath.DbType        = DbType.Double;
                param_breath.ParameterName = "BREATH";
                command.Parameters.Add(param_breath);

                DbParameter param_dbp = command.CreateParameter();
                param_dbp.Value         = tprData.DBP;
                param_dbp.DbType        = DbType.Double;
                param_dbp.ParameterName = "DBP";
                command.Parameters.Add(param_dbp);

                DbParameter param_sbp = command.CreateParameter();
                param_sbp.Value         = tprData.SBP;
                param_sbp.DbType        = DbType.Double;
                param_sbp.ParameterName = "SBP";
                command.Parameters.Add(param_sbp);

                DbParameter param_spo2 = command.CreateParameter();
                param_spo2.Value         = tprData.SPO2;
                param_spo2.DbType        = DbType.Double;
                param_spo2.ParameterName = "SPO2";
                command.Parameters.Add(param_spo2);

                DbParameter param_no = command.CreateParameter();
                param_no.Value         = tprData.PatNO;
                param_no.DbType        = DbType.String;
                param_no.ParameterName = "PAT_NO";
                command.Parameters.Add(param_no);

                DbParameter param_date = command.CreateParameter();
                param_date.Value         = tprData.MonitorDate;
                param_date.DbType        = DbType.String;
                param_date.ParameterName = "MONITOR_DATE";
                command.Parameters.Add(param_date);

                DbParameter param_time = command.CreateParameter();
                param_time.Value         = tprData.MonitorTime;
                param_time.DbType        = DbType.String;
                param_time.ParameterName = "MONITOR_TIME";
                command.Parameters.Add(param_time);

                return(command.ExecuteNonQuery() > 0);
            }
            catch
            {
                trans.Rollback();
                return(false);
            }
            finally
            {
                trans.Commit();
                conn.Close();
            }
        }
        public TPRData GetTPRData(string patNO, string date, string time)
        {
            TPRData tpr = new TPRData();

            //connect to database
            TPECHDBService tpech = TPECHDBService.getInstance();
            DbConnection   conn  = tpech.GetTPECHConnection("B");

            conn.Open();

            //selectSQL
            string    sqlselect = "SELECT * FROM INPTPRM WHERE PAT_NO = :PAT_NO AND MONITOR_DATE = :MONITOR_DATE AND MONITOR_TIME = :MONITOR_TIME";
            DbCommand command   = conn.CreateCommand();

            command.CommandText = sqlselect;
            command.CommandType = System.Data.CommandType.Text;

            DbParameter param_no = command.CreateParameter();

            param_no.Value         = patNO;
            param_no.DbType        = DbType.String;
            param_no.ParameterName = "PAT_NO";
            command.Parameters.Add(param_no);

            DbParameter param_date = command.CreateParameter();

            param_date.Value         = date;
            param_date.DbType        = DbType.String;
            param_date.ParameterName = "MONITOR_DATE";
            command.Parameters.Add(param_date);

            DbParameter param_time = command.CreateParameter();

            param_time.Value         = time;
            param_time.DbType        = DbType.String;
            param_time.ParameterName = "MONITOR_TIME";
            command.Parameters.Add(param_time);

            //資料庫交易
            DbTransaction trans = conn.BeginTransaction();

            try
            {
                DbDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        tpr.Temperature = Math.Round(Convert.ToDouble(reader["TEMPERATURE"]), 2);
                        tpr.Weight      = Convert.ToDouble(reader["WEIGHT"]);
                        tpr.Pulse       = Convert.ToDouble(reader["PULSE"]);
                        tpr.Breath      = Convert.ToDouble(reader["BREATH"]);
                        tpr.DBP         = Convert.ToDouble(reader["DBP"]);
                        tpr.SBP         = Convert.ToDouble(reader["SBP"]);
                        tpr.SPO2        = Convert.ToDouble(reader["SPO2"]);
                    }
                }
                DataTable table = new DataTable();
                table.Load(reader);
                reader.Close();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            finally
            {
                conn.Close();
            }
            return(tpr);
        }
        public List <TPRData> GetTPRs(string patNO, string begDate, string endDate)
        {
            TPECHDBService tpech = TPECHDBService.getInstance();
            DbConnection   conn  = tpech.GetTPECHConnection("B");

            conn.Open();

            string    sqlselect = "SELECT * FROM INPTPRM WHERE PAT_NO = :PAT_NO AND MONITOR_DATE BETWEEN :begDate AND :endDate ORDER BY MONITOR_DATE ASC, MONITOR_TIME ASC";
            DbCommand command   = conn.CreateCommand();

            command.CommandText = sqlselect;
            command.CommandType = System.Data.CommandType.Text;

            DbParameter param_no = command.CreateParameter();

            param_no.Value         = patNO;
            param_no.DbType        = DbType.String;
            param_no.ParameterName = "PAT_NO";
            command.Parameters.Add(param_no);

            DbParameter param_beg = command.CreateParameter();

            param_beg.Value         = begDate;
            param_beg.DbType        = DbType.String;
            param_beg.ParameterName = "begDate";
            command.Parameters.Add(param_beg);

            DbParameter param_end = command.CreateParameter();

            param_end.Value         = endDate;
            param_end.DbType        = DbType.String;
            param_end.ParameterName = "endDate";
            command.Parameters.Add(param_end);

            DbTransaction trans = conn.BeginTransaction();

            //use list to put tprdata
            List <TPRData> json = new List <TPRData>();

            json.Clear();

            try
            {
                DbDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        TPRData data = new TPRData();
                        data.PatNO       = reader["PAT_NO"].ToString();
                        data.MonitorDate = reader["MONITOR_DATE"].ToString();
                        data.MonitorTime = reader["MONITOR_TIME"].ToString();
                        data.Temperature = Math.Round(Convert.ToDouble(reader["TEMPERATURE"]), 2);
                        data.Weight      = Convert.ToDouble(reader["WEIGHT"]);
                        data.Pulse       = Convert.ToDouble(reader["PULSE"]);
                        data.Breath      = Convert.ToDouble(reader["BREATH"]);
                        data.DBP         = Convert.ToDouble(reader["DBP"]);
                        data.SBP         = Convert.ToDouble(reader["SBP"]);
                        data.SPO2        = Convert.ToDouble(reader["SPO2"]);
                        json.Add(data);
                    }
                }
                reader.Close();
            }
            finally
            {
                conn.Close();
            }
            return(json);
        }