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