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