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