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