public static DataTable GetNullBedNum()
{
string sql = @"SELECT COUNT(*) FROM ROOM WHERE PATIENTNAME IS NULL";
DataTable dataTable = DBOper.GetDataTable(sql);
return(dataTable);
}//得到空置的床位数量
}//得到空置的床位数量
public static DataTable GetAllBedNum()
{
string sql = @"SELECT COUNT(*) FROM ROOM";
DataTable dataTable = DBOper.GetDataTable(sql);
return(dataTable);
}
public static DataTable GetInID(string roomId)
{
string sql = @"select inId from checkin where delMark=0 and roomId=" + roomId;
DataTable dt = DBOper.GetDataTable(sql);
return(dt);
}
public static DataTable GetRoomInfo()
{
string sql = @"select roomId as 房间号,roomType as 房间类型,roomFloor as 层数,Price as 价格,personNum as 可入住人数,inPerson as 已入住人数,note as 备注 from room where inPerson=0";
DataTable dt = DBOper.GetDataTable(sql);
return(dt);
}
public static DataTable GetRoomInfo(string roomId)
{
string sql = @"select * from room where roomId=" + roomId;
DataTable dt = DBOper.GetDataTable(sql);
return(dt);
}
//setting a patient object to operate all information of one
//public static Patient patient=new Patient();
//read
public static DataTable GetPatientInfoExistRoom()
{
string sql = @"SELECT AGE,In_HospitalNum,In_Date FROM PATIENT WHERE PatientName in (SELECT
PATIENTNAME FROM ROOM)";
DataTable table = DBOper.GetDataTable(sql);
return(table);
}
public static DataTable GetDataTableInfo()
{
string sql = @"SELECT Room_Num as 房间号,Bed_Num as 床位号,PatientName as 姓名,
Sex as 性别,Status_R as 床位状态,AdministratorOffice as 主管科室 from Room";
DataTable dataTable = DBOper.GetDataTable(sql);
return(dataTable);
}
public static DataTable GetDataTableInfoOfNullBednum()
{
string sql = @"SELECT Room_Num as 房间号,Bed_Num as 床位号,
Status_R as 床位状态,
AdministratorOffice as 主管科室 FROM ROOM WHERE PATIENTNAME IS NULL";
DataTable dataTable = DBOper.GetDataTable(sql);
return(dataTable);
}
public static DataTable GetUseRoomInfo()
{
string sql = @"select roomId as 房间号,price as 价格,foregift as 押金,inTime as 入住时间,outTime as 退房时间,clientName as 住客姓名,
sex as 性别,phone as 电话号码,certType as 证件类型,certId as 证件号码,address as 地址,personNum as 入住人数, Oper as 登记者
from checkin where delMark=0";
DataTable dt = DBOper.GetDataTable(sql);
return(dt);
}
public static DataTable GetALLClient(string clientName)
{
string sql = @"select roomId as 房间号,price as 价格,foregift as 押金,inTime as 入住时间,outTime as 退房时间,clientName as 住客姓名,
sex as 性别,phone as 电话号码,certType as 证件类型,certId as 证件号码,address as 地址,personNum as 入住人数,Oper as 等记者,
delMark as 是否退房,inId from checkin where clientName like '%" + clientName + "%'";
DataTable dt = DBOper.GetDataTable(sql);
return(dt);
}
public static DataTable GetDataTableInfo_PatientName(Room room)
{
string sql = @"SELECT Room_Num as 房间号,Bed_Num as 床位号,PatientName as 姓名,
Sex as 性别,Status_R as 床位状态,AdministratorOffice as 主管科室 from Room
where PatientName=@patientName";
MySqlParameter p = new MySqlParameter("@patientName", room.PatientName);
DataTable dataTable = DBOper.GetDataTable(sql, p);
return(dataTable);
}
private void UserManagerForm_Load(object sender, EventArgs e)
{
con = new MySqlConnection(Connstr);
da = new MySqlDataAdapter("select * from user", con);
ds = new DataSet();
da.Fill(ds, "user");
dgvUsers.DataSource = ds.Tables["user"];
string sql = "select * from user";
dgvUsers.DataSource = DBOper.GetDataTable(sql);
}
public static DataTable GetPatientInfo_FromInHospitalNum(Patient p)
{
string sql = @"SELECT PatientName as 姓名,age as 年龄,sex as 性别,
idcard_num as 身份证号,
In_HospitalNum as 住院号,
Major_Doctor as 主管医师,
Main_Symptom as 主要症状,
Phone_Num as 电话号码,
In_Date as 入院日期 from Patient where in_HospitalNum=@in_HospitalNum";
MySqlParameter parameter = new MySqlParameter("@in_HospitalNum", p.In_HospitalNum);
DataTable dataTable = DBOper.GetDataTable(sql, parameter);
return(dataTable);
}
public static DataTable GetDataTableInfo_RoomNumAndBedNum(Room room)
{
string sql = @"SELECT Room_Num as 房间号,Bed_Num as 床位号,PatientName as 姓名,
Sex as 性别,Status_R as 床位状态,AdministratorOffice as 主管科室 from Room
where Room_Num=@room_Num and Bed_Num=@bed_Num";
MySqlParameter[] paramArray = new MySqlParameter[] {
new MySqlParameter("@room_Num", room.Room_Num),
new MySqlParameter("@bed_Num", room.Bed_Num)
};
DataTable dataTable = DBOper.GetDataTable(sql, paramArray);
return(dataTable);
}
public static DataTable GetDataTableInfo_CommonGood(CommonGood cw)
{
string sql = @"
SELECT goodname as 公共物品名,
status_g as 物品状态,
good_num as 物品编号,
user_name as 使用者,
cost as 价格 from CommonGood where user_name=@user_Name
";
MySqlParameter parameter = new MySqlParameter("@user_Name", cw.User_Name);
DataTable table = DBOper.GetDataTable(sql, parameter);
return(table);
}
private ArrayList GetIsoRevisionIndex()
{
try
{
Logger.Info("Start GetIsoRevisionIndex");
_DBOper = new DBOper(_DbConnStr);
string sql = string.Format(@"select rev.revision from iso_revision rev
group by rev.revision
order by revision asc");
DataTable dt = _DBOper.GetDataTable(sql);
DataRow[] dRows = dt.Select();
ArrayList isoRevision = new ArrayList();
foreach (DataRow dRow in dRows)
{
isoRevision.Add(dRow["revision"]);
}
return isoRevision;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
public int SaveLineList(string revision, string version, string projectCode, string user, string description, string type, string arrstringValue)
{
try
{
Logger.Info("Start SaveLineList");
_DBOper = new DBOper(_DbConnStr);
string sqlCheck = string.Empty;
string sql = string.Empty;
int result = 0;
string lineNo = string.Empty;
DataTable dt = new DataTable();
DataTable dtPrevious = new DataTable();
string[] arrValue = arrstringValue.Split(new char[] { '+' });
//Master Insert
sqlCheck = string.Format(@"SELECT * FROM LINELIST_MASTER WHERE PROJECT_CODE = '{0}' AND LINE_NO = '{1}' ", projectCode, arrValue[3].ToString());
dt = _DBOper.GetDataTable(sqlCheck);
if (dt.Rows.Count == 0)
{
sql = string.Format(@"INSERT INTO LINELIST_MASTER(LINE_NO, PROJECT_CODE,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER)
VALUES ( '{0}','{1}',SYSDATE,'{2}',SYSDATE,'{2}')", arrValue[3].ToString(), projectCode, user);
result = _DBOper.SaveData(sql);
}
sqlCheck = string.Format(@"SELECT LINE_SIZE,FLUID_CODE,SEQUENCE_NO,PIPING_SPEC,SCHEDULE,HYDRO_TEST_MEDIUM,
HYDRO_TEST_PRESSURE,INSULATION_SPEC,INSULATION_THICK,SERVICE,FROM_,TO_,DESIGN_TEMPERATURE,
DESIGN_PRESSURE,PHASE,SPECIFIC_GRAVITY,FLOW_RATE,MASS_FLOW,OPERATING_TEMPERATURE,OPERATING_PRESSURE,
VISCOSITY,MASS_DENSITY,VELOCITY,DIFFERENTIAL_PRESSURE,TOTAL_LENGTH,PID_NO,LINE_TYPE,DELETED
FROM LINELIST_BASIC WHERE PROJECT_CODE = '{0}' AND LINE_NO = '{1}' AND LATEST ='Y' ", projectCode, arrValue[3].ToString());
dt = _DBOper.GetDataTable(sqlCheck);
dtPrevious = dt;
if (dt.Rows.Count > 0)
{
sql = string.Format(@"UPDATE LINELIST_BASIC
SET LATEST = 'N',
UPDATE_DATE = SYSDATE,
UPDATE_USER = '******'
WHERE LINE_NO = '{0}' and project_code = '{1}'"
, arrValue[3].ToString(), projectCode, user);
result = _DBOper.SaveData(sql);
}
//Basic Insert
sql = string.Format(@"INSERT INTO LINELIST_BASIC(LINE_NO, LINE_SIZE,FLUID_CODE,SEQUENCE_NO,PIPING_SPEC,SCHEDULE,HYDRO_TEST_MEDIUM,
HYDRO_TEST_PRESSURE,INSULATION_SPEC,INSULATION_THICK,SERVICE,FROM_,TO_,DESIGN_TEMPERATURE,
DESIGN_PRESSURE,PHASE,SPECIFIC_GRAVITY,FLOW_RATE,MASS_FLOW,OPERATING_TEMPERATURE,OPERATING_PRESSURE,
VISCOSITY,MASS_DENSITY,VELOCITY,DIFFERENTIAL_PRESSURE,TOTAL_LENGTH,PID_NO,LINE_TYPE,
REVISION,VERSION,DELETED,LATEST,PROJECT_CODE,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',
'{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}',
'{30}','{31}','{32}',SYSDATE,'{33}',SYSDATE,'{33}')
", arrValue[3].ToString(), arrValue[4].ToString(), arrValue[5].ToString(), arrValue[6].ToString(), arrValue[7].ToString(),
arrValue[8].ToString(), arrValue[9].ToString(), arrValue[10].ToString(), arrValue[11].ToString(), arrValue[12].ToString(),
arrValue[13].ToString(), arrValue[14].ToString(), arrValue[15].ToString(), arrValue[16].ToString(), arrValue[17].ToString(),
arrValue[18].ToString(), arrValue[19].ToString(), arrValue[20].ToString(), arrValue[21].ToString(), arrValue[22].ToString(),
arrValue[23].ToString(), arrValue[24].ToString(), arrValue[25].ToString(), arrValue[26].ToString(), arrValue[27].ToString(),
arrValue[28].ToString(), arrValue[29].ToString(), arrValue[30].ToString(), revision, version, type, 'Y', projectCode, user);
result = _DBOper.SaveData(sql);
//History Detail 쿼리. 지금은 속성 아이디 하드코딩
if (dtPrevious.Rows.Count > 0)
{
for (int i = 0; i < dtPrevious.Columns.Count - 1; i++) //마지막은 deleted 컬럼이기때문에 insert 안한다
{
string currentValue;
if (type == "Y") //Delete되는 값은 히스토리에 NULL로 저장
{
currentValue = null;
}
else
{
currentValue = arrValue[i + 4]; //새로생기거나 바뀐 속성은 속성값으로 insert
}
if (dtPrevious.Rows[0][i].ToString() != currentValue || dtPrevious.Rows[0]["DELETED"].ToString() =="Y")//이전버전에 Linelist가 Delete라면 이전버전과 지금버전의 속성이 같아도 insert
{
sql = string.Format(@"INSERT INTO LINELIST_HISTORY_DETAIL(LINE_NO,REVISION,VERSION,ATTRIBUTE_ID,
PREVIOUS_VALUE,CURRENT_VALUE,PROJECT_CODE,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER)
VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}','{6}',SYSDATE,'{7}',SYSDATE,'{7}')",
arrValue[3].ToString(), revision, version, i + 1, dtPrevious.Rows[0][i].ToString(), currentValue, projectCode, user);
result = _DBOper.SaveData(sql);
}
}
}
else
{
for (int i = 0; i < dtPrevious.Columns.Count; i++)
{
string currentValue;
if (type == "Y") //Delete되는 값
{
currentValue = null;
}
else
{
currentValue = arrValue[i + 4];
}
sql = string.Format(@"INSERT INTO LINELIST_HISTORY_DETAIL(LINE_NO,REVISION,VERSION,ATTRIBUTE_ID,
PREVIOUS_VALUE,CURRENT_VALUE,PROJECT_CODE,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER)
VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}','{6}',SYSDATE,'{7}',SYSDATE,'{7}')",
arrValue[3].ToString(), revision, version, i + 1, null, currentValue, projectCode, user);
result = _DBOper.SaveData(sql);
}
}
return result;
}
catch (Exception ex)
{
Logger.Error(ex);
return 0;
}
}
public int SaveRevisionVersion(string projectCode,string revision, string version, string user, string description)
{
try
{
Logger.Info("Start SaveRevisionVersion");
_DBOper = new DBOper(_DbConnStr);
string sqlCheck = string.Empty;
string sql = string.Empty;
int result = 0;
DataTable dt = new DataTable();
//Revision Version Insert
sqlCheck = string.Format(@"SELECT * FROM LINELIST_HISTORY WHERE PROJECT_CODE = '{0}' AND REVISION = '{1}' AND VERSION = '{2}' ", projectCode, revision, version);
dt = _DBOper.GetDataTable(sqlCheck);
if (dt.Rows.Count == 0)
{
sql = string.Format(@"INSERT INTO LINELIST_HISTORY( PROJECT_CODE, REVISION, VERSION, DESCRIPTION, CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER)
VALUES ( '{0}','{1}','{2}','{3}',SYSDATE,'{4}',SYSDATE,'{4}')", projectCode, revision, version, description, user);
result = _DBOper.SaveData(sql);
}
return result;
}
catch (Exception ex)
{
Logger.Error(ex);
return 0;
}
}
public DataTable LoadRevisionLineList(string projectCode)
{
try
{
Logger.Info("Start LoadRevisionLineList");
_DBOper = new DBOper(_DbConnStr);
string sql = string.Format(@"
SELECT REVISION
FROM (
SELECT
REVISION,
CREATE_DATE,
ROW_NUMBER() OVER(
PARTITION BY REVISION ORDER BY CREATE_DATE) CNT
FROM LINELIST_HISTORY
WHERE PROJECT_CODE = '{0}'
)WHERE CNT = 1
ORDER BY create_date desc", projectCode);
DataTable dt = _DBOper.GetDataTable(sql);
return dt;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
public DataTable LoadVersionLineList(string projectCode, string revision)
{
try
{
Logger.Info("Start LoadVersionLineList");
_DBOper = new DBOper(_DbConnStr);
string sql = string.Format(@"
SELECT VERSION
FROM LINELIST_HISTORY
WHERE PROJECT_CODE = '{0}' AND REVISION = '{1}' ORDER BY CREATE_DATE DESC
", projectCode, revision);
DataTable dt = _DBOper.GetDataTable(sql);
return dt;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
public DataTable LoadLineHistory(string projectCode, string revision, string version)
{
try
{
Logger.Info("Start LoadLineHistory");
_DBOper = new DBOper(_DbConnStr);
string sql = string.Format(@"
SELECT a.LINE_NO, b.ATTRIBUTE_DISPLAY_NAME,a.PREVIOUS_VALUE,a.CURRENT_VALUE
FROM LINELIST_HISTORY_DETAIL a,LINELIST_ATTRIBUTE b
WHERE a.attribute_id = b.attribute_id AND a.PROJECT_CODE = '{0}' AND REVISION = '{1}' AND VERSION = '{2}'
", projectCode, revision, version);
DataTable dt = _DBOper.GetDataTable(sql);
return dt;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
public DataTable LoadLineListHistory(string projectCode,string revision, string version)
{
try
{
Logger.Info("Start LoadLineListHistory");
_DBOper = new DBOper(_DbConnStr);
string sql = string.Format(@" SELECT '' as COLUMNDCNSTATUS,
'' as COLUMNDCNNO,
'' as COLUMNREMARK,
LINE_NO,LINE_SIZE,FLUID_CODE,SEQUENCE_NO,PIPING_SPEC,SCHEDULE,HYDRO_TEST_MEDIUM,
HYDRO_TEST_PRESSURE,INSULATION_SPEC,INSULATION_THICK,SERVICE,FROM_,TO_,DESIGN_TEMPERATURE,
DESIGN_PRESSURE,PHASE,SPECIFIC_GRAVITY,FLOW_RATE,MASS_FLOW,OPERATING_TEMPERATURE,
OPERATING_PRESSURE,VISCOSITY,MASS_DENSITY,VELOCITY,DIFFERENTIAL_PRESSURE,TOTAL_LENGTH,PID_NO,
LINE_TYPE,
'' as COLUMNSTATUS,
'' as COLUMNNUMBER,
'' as COLUMNAFTERSTATUS
FROM linelist_basic
WHERE DELETED = 'N' AND
revision = '{1}' AND
version <= '{2}' AND
LATEST = 'Y' AND
PROJECT_CODE = '{0}'
UNION
SELECT '' as COLUMNDCNSTATUS,
'' as COLUMNDCNNO,
'' as COLUMNREMARK,
LINE_NO,LINE_SIZE,FLUID_CODE,SEQUENCE_NO,PIPING_SPEC,SCHEDULE,HYDRO_TEST_MEDIUM,
HYDRO_TEST_PRESSURE,INSULATION_SPEC,INSULATION_THICK,SERVICE,FROM_,TO_,DESIGN_TEMPERATURE,
DESIGN_PRESSURE,PHASE,SPECIFIC_GRAVITY,FLOW_RATE,MASS_FLOW,OPERATING_TEMPERATURE,
OPERATING_PRESSURE,VISCOSITY,MASS_DENSITY,VELOCITY,DIFFERENTIAL_PRESSURE,TOTAL_LENGTH,PID_NO,
LINE_TYPE,
'' as COLUMNSTATUS,
'' as COLUMNNUMBER,
'' as COLUMNAFTERSTATUS
FROM linelist_basic
WHERE DELETED = 'N' AND
revision < '{1}' AND
LATEST = 'Y' AND
PROJECT_CODE = '{0}'
", projectCode,revision,version);
DataTable dt = _DBOper.GetDataTable(sql);
return dt;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
public DataTable LoadCompareDistinctLineList(string projectCode, string revision, string version)
{
try
{
Logger.Info("Start LoadLineList");
_DBOper = new DBOper(_DbConnStr);
string sql = string.Format(@" SELECT LINE_NO
FROM linelist_basic
WHERE
REVISION = '{1}' AND
VERSION <= '{2}' AND
PROJECT_CODE = '{0}'
GROUP BY LINE_NO
UNION
SELECT LINE_NO
FROM linelist_basic
WHERE
REVISION < '{1}' AND
PROJECT_CODE = '{0}'
GROUP BY LINE_NO
", projectCode, revision, version);
DataTable dt = _DBOper.GetDataTable(sql);
return dt;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
public DataTable LoadLatestRevisionLineList(string projectCode)
{
try
{
Logger.Info("Start LoadLatestVersionLineList");
_DBOper = new DBOper(_DbConnStr);
string sql = string.Format(@"
SELECT a.REVISION,a.VERSION
FROM
(SELECT *
FROM LINELIST_HISTORY
WHERE PROJECT_CODE = '{0}' ORDER BY CREATE_DATE DESC) a
where rownum='1'
", projectCode);
DataTable dt = _DBOper.GetDataTable(sql);
return dt;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
public DataTable LoadBatchIsoList(string batchDate)
{
try
{
Logger.Info("Start LoadBatchIsoList");
_DBOper = new DBOper(_DbConnStr);
string sql = string.Format("select '' as Status, rev.DWG_NO \"ISO No\", rev.SHT \"SH'T\", rev.REVISION , rev.REVISION_DATE \"Rev Date\", lst.line_no \"Line No\", REV.REVISION_REMARK \"Remark\" from iso_revision rev, iso_list lst where revision_date = '{0}' and rev.dwg_no = lst.dwg_no", batchDate);
DataTable dt = _DBOper.GetDataTable(sql);
return dt;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
public DataTable GetMasterIsoList()
{
try
{
ArrayList arIsoRevision = GetIsoRevisionIndex();
string revisionColumn = string.Empty;
foreach (string revision in arIsoRevision)
{
revisionColumn += string.Format("MAX(DECODE(rev.revision, '{0}', rev.revision_date, '')) REV_{0}, ", revision);
}
Logger.Info("Start GetMasterIsoList");
_DBOper = new DBOper(_DbConnStr);
string sql = string.Format(@"select '' as Unit, '' as LineNo, rev.DWG_NO as DrawingNumber, rev.SHT, '' as STATUS
, '' as DCNStatus, '' as DCNNO, '' as CRNNO
, {0} MAX(rev.revision) as Current_Revision, '' as Remark
from ISO_LIST list, ISO_REVISION rev
WHERE list.dwg_no = rev.dwg_no
group by rev.DWG_NO, rev.SHT", revisionColumn);
DataTable dt = _DBOper.GetDataTable(sql);
return dt;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
public DataTable GetLineListMaster()
{
try
{
Logger.Info("Start GetLineListMaster");
_DBOper = new DBOper(_DbConnStr);
string sql = string.Format(@"SELECT * FROM LINELIST_MASTER");
DataTable dt = _DBOper.GetDataTable(sql);
return dt;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
public DataTable GetIsoRevision(string where = "")
{
try
{
Logger.Info("Start GetIsoRevision");
_DBOper = new DBOper(_DbConnStr);
string sql = where.Equals(string.Empty) ? string.Format(@"SELECT * FROM ISO_REVISION")
: string.Format(@"SELECT * FROM ISO_REVISION WHERE {0}", where);
DataTable dt = _DBOper.GetDataTable(sql);
return dt;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
public DataTable GetIsoBatchDate()
{
try
{
Logger.Info("Start GetIsoBatchDate");
_DBOper = new DBOper(_DbConnStr);
string sql = string.Format(@"select DISTINCT(REVISION_DATE) from iso_revision order by REVISION_DATE DESC");
DataTable dt = _DBOper.GetDataTable(sql);
return dt;
}
catch (Exception ex)
{
Logger.Error(ex);
}
return null;
}
