public static int deleteRecord(Magazine obj) { string strSQL = "delete from magazine where magazineId='" + obj.magazineId + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static int insertshenqingRecord(yuyuehuiyi obj) { string strSQL = "insert into shenqinghuiyi(huiyiname,shijian,shenhe) values('" + obj.huiyiname + "','" + obj.shijian + "','" + "0" + "')"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
//public static int delRecord(yuyuehuiyi obj) //{ // string strSQL = "delete from shenqinghuiyi where huiyiid='" + obj.huiyiid + "'"; // int iRet = DBOper.execNonQueryBySQLText(strSQL); // return iRet; //} public static int updateshenheRecord(yuyuehuiyi obj) { string strSQL = "update shenqinghuiyi set shenhe='" + "1" + "' where huiyiname='" + obj.huiyiname + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static int insertRecord(UpImage obj) { string strSQL = "insert into upImage(imageId,savepath,magazineId,sequence,imageDesc,uptime,articleId) values('" + obj.imageId + "','" + obj.savepath + "','" + obj.magazineId + "'," + obj.sequence + ",'" + obj.imageDesc + "','" + obj.uptime + "','" + obj.articleId + "')"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static int updateRecord(UpImage obj) { string strSQL = "update upImage set savepath='" + obj.savepath + "',magazineId='" + obj.magazineId + "',sequence=" + obj.sequence + ",imageDesc='" + obj.imageDesc + "',uptime='" + obj.uptime + "',articleId='" + obj.articleId + "' where imageId='" + obj.imageId + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static int deleteRecord(Users obj) { string strSQL = "delete from tbusers where userid='" + obj.userid + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public string SaveDBWithTime(string time) { LoadJson load = new LoadJson(this._name, time, ""); string sRes = load.LoadJsonLikeTime(); string DBcommand = ""; if (sRes != "[]") //已经有数据 { DBcommand = @"UPDATE [EPInfoSystem].[dbo].[JsonTable] SET [内容] ='" + _jsonobj + @"' where [时间]= '" + time + "' AND [表名]='" + this._name + "'"; } else { DBcommand = @"INSERT INTO [EPInfoSystem].[dbo].[JsonTable] ([表名] ,[时间] ,[类型] ,[内容]) VALUES ('" + _name + "','" + time + "','json','" + _jsonobj + "')"; } string ret; string source = ConfigurationManager.ConnectionStrings["EPInfoSys"].ConnectionString.ToString(); DBOper DBop = new DBOper(source, DBcommand); ret = DBop.ReturnRows(); return("数据库录入成功,影响" + ret); }
public static int deleteRecord(roomBook obj) { string strSQL = "delete from roombook where username='******' and roomid = '" + obj.roomid + "' and begintime = '" + obj.begintime + "' and endtime = '" + obj.endtime + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static int insertRecord(Admin obj) { string strSQL = "insert into admin(adminname,adminkey,adminphone) values('" + obj.adminname + "','" + obj.adminkey + "','" + obj.adminphone + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static DataTable selectDataTable1() { string strSQL = "select* from roombook"; DataTable dt = DBOper.execQueryBySQLText(strSQL); return(dt); }
public static int insertRecord(roomBook obj) { string strSQL = "insert into roombook(username,roomid,begintime,endtime) values('" + obj.username + "','" + obj.roomid + "','" + obj.begintime + "','" + obj.endtime + "')"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) { if (DropDownList1.SelectedValue != "" && DropDownList2.SelectedValue == "") { DropDownList2.Items.Clear(); ListItem item = new ListItem(); item.Text = "请选择"; item.Value = ""; DropDownList2.Items.Insert(0, item); DataTable dt = DBOper.execQueryBySQLText("Select * from article where magazineId='" + DropDownList1.SelectedValue + "'"); DropDownList2.DataSource = dt; DropDownList2.DataTextField = "articleName"; DropDownList2.DataValueField = "articleName"; DropDownList2.DataBind(); } //else //{ // DropDownList2.Items.Clear(); // ListItem item = new ListItem(); // item.Text = "请选择"; // item.Value = ""; // DropDownList2.Items.Insert(0, item); // DataTable dt = DBOper.execQueryBySQLText("Select * from article"); // DropDownList2.DataSource = dt; // DropDownList2.DataTextField = "articleName"; // DropDownList2.DataValueField = "articleName"; // DropDownList2.DataBind(); //} }
public static bool InsertCheckOutRoomInfo(CheckOutRoom checkOutRoomInfo) { string sqlInsert = @"insert into checkout values(@outId,@inId,@outTime,@roomId,@clientName,@inTime,@price,@foregift,@account,@note,@oper)"; string sqlUpdateRegister = "update checkin set delMark=1 where inId=@inId"; string sqlUpdateRoom = "update room set inPerson=0 where roomId=@roomId"; MySqlParameter p1 = new MySqlParameter("@outId", checkOutRoomInfo.OutId); MySqlParameter p2 = new MySqlParameter("@inId", checkOutRoomInfo.InId); MySqlParameter p3 = new MySqlParameter("@roomId", checkOutRoomInfo.RoomId); MySqlParameter p4 = new MySqlParameter("@price", checkOutRoomInfo.Price.ToString()); MySqlParameter p5 = new MySqlParameter("@foregift", checkOutRoomInfo.Foregift.ToString()); MySqlParameter p6 = new MySqlParameter("@total", checkOutRoomInfo.Total.ToString()); MySqlParameter p7 = new MySqlParameter("@account", checkOutRoomInfo.Account.ToString()); MySqlParameter p8 = new MySqlParameter("@inTime", checkOutRoomInfo.InTime); MySqlParameter p9 = new MySqlParameter("@outTime", checkOutRoomInfo.OutTime); MySqlParameter p10 = new MySqlParameter("@clientName", checkOutRoomInfo.ClietnName); MySqlParameter p11 = new MySqlParameter("@oper", checkOutRoomInfo.Oper); MySqlParameter p12 = new MySqlParameter("@note", checkOutRoomInfo.Note); MySqlParameter[] paramArray = new MySqlParameter[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12 }; MySqlParameter n1 = new MySqlParameter("@inId", checkOutRoomInfo.InId); MySqlParameter n2 = new MySqlParameter("@roomId", checkOutRoomInfo.RoomId); if (DBOper.ExecuteCommand(sqlInsert, paramArray) == 1 && DBOper.ExecuteCommand(sqlUpdateRegister, n1) == 1 && DBOper.ExecuteCommand(sqlUpdateRoom, n2) == 1) { return(true); } else { return(false); } }
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 int updateRecord(Magazine obj) { string strSQL = "update magazine set magazineName='" + obj.magazineName + "',mdescription='" + obj.mdescription + "',magazineKey='" + obj.magazineKey + "' where magazineId='" + obj.magazineId + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static int deleteRecord(Admin obj) { string strSQL = "delete from admin where adminid='" + obj.adminname + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static int insertRecord(Users obj) { string strSQL = "insert into tbusers(userid,username,userpwd) values('" + obj.userid + "','" + obj.username + "','" + obj.userpwd + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static DataTable GetNullBedNum() { string sql = @"SELECT COUNT(*) FROM ROOM WHERE PATIENTNAME IS NULL"; DataTable dataTable = DBOper.GetDataTable(sql); return(dataTable); }//得到空置的床位数量
public static int updateRecord(Users obj) { string strSQL = "update tbusers set username='******' where userid='" + obj.userid + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
}//得到空置的床位数量 public static DataTable GetAllBedNum() { string sql = @"SELECT COUNT(*) FROM ROOM"; DataTable dataTable = DBOper.GetDataTable(sql); return(dataTable); }
public static string ReadAccess(string order, string classify) { string ret; string DBcommand; string source = ConfigurationManager.ConnectionStrings["EPInfoSys"].ConnectionString.ToString(); if (order == "-1") { DBcommand = @"SELECT TOP 1 [时间] ,[标题] ,[内容] ,[Id] FROM [EPInfoSystem].[dbo].[NewInfo] WHERE [分类]='" + classify + @"' order by [时间] desc" ; } else { DBcommand = @"SELECT [时间] ,[标题] ,[内容] ,[Id] FROM [EPInfoSystem].[dbo].[NewInfo] WHERE [Id]=" + order; } ret = DBOper.ReturnJson(source, DBcommand); return(ret); }
//insert public static bool InsertIntoPatient(Patient p) { string sqlInsert = @"insert into patient values( @patientName,@age,@sex,@idCard_Num,@in_HospitalNum,@major_Doctor, @main_Symptom,@phone_Num,@in_Date )"; MySqlParameter[] paramArray = new MySqlParameter[] { new MySqlParameter("@patientname", p.PatientName), new MySqlParameter("@age", p.Age), new MySqlParameter("@sex", p.Sex), new MySqlParameter("@idCard_Num", p.IdCard_Num), new MySqlParameter("@in_HospitalNum", p.In_HospitalNum), new MySqlParameter("@major_Doctor", p.Major_Doctor), new MySqlParameter("@main_Symptom", p.Main_Symptom), new MySqlParameter("@phone_Num", p.Phone_Num), new MySqlParameter("@in_Date", p.In_Date) }; if (DBOper.ExecuteCommand(sqlInsert, paramArray) == 1) { return(true); } else { return(false); } }
public static int deleteRecord(UpImage obj) { string strSQL = "delete from upImage where imageId='" + obj.imageId + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static bool UpdatePatient(Patient p) { string sqlUpdate = @"Update Patient set age=@age, sex=@sex,idcard_num=@idCard_Num,in_hospitalnum=@in_HospitalNum, major_doctor=@major_Doctor, main_symptom=@main_Symptom, phone_num=@phone_Num, in_date=@in_Date where PatientName=@patientName "; MySqlParameter[] param = new MySqlParameter[] { new MySqlParameter("@age", p.Age), new MySqlParameter("@sex", p.Sex), new MySqlParameter("@idCard_Num", p.IdCard_Num), new MySqlParameter("@in_HospitalNum", p.In_HospitalNum), new MySqlParameter("@major_Doctor", p.Major_Doctor), new MySqlParameter("@main_Symptom", p.Main_Symptom), new MySqlParameter("@phone_Num", p.Phone_Num), new MySqlParameter("@in_Date", p.In_Date) }; MySqlParameter pIn = new MySqlParameter("@patientName", p.PatientName); if (DBOper.ExecuteCommand(sqlUpdate, pIn, param) == 1) { return(true); } else { return(false); } }
public static bool InsertRoomInfo(CheckInRoom registerRoomInfo) { string sqlInsert = @"insert into checkin values(null,@roomId,@price,@foregift,@inTime,@outTime,@clientName, @sex,@phone,@cerType,@certId,@address,@personNum,@Oper,@deMark)"; MySqlParameter p1 = new MySqlParameter("@roomId", registerRoomInfo.RoomId); MySqlParameter p2 = new MySqlParameter("@price", registerRoomInfo.Price); MySqlParameter p3 = new MySqlParameter("@foregift", registerRoomInfo.Foregift); MySqlParameter p4 = new MySqlParameter("@inTime", registerRoomInfo.InTime); MySqlParameter p5 = new MySqlParameter("@outTime", registerRoomInfo.OutTime); MySqlParameter p6 = new MySqlParameter("@clientName", registerRoomInfo.ClientName); MySqlParameter p7 = new MySqlParameter("@sex", registerRoomInfo.Sex); MySqlParameter p8 = new MySqlParameter("@phone", registerRoomInfo.Phone); MySqlParameter p9 = new MySqlParameter("@certType", registerRoomInfo.CertType); MySqlParameter p10 = new MySqlParameter("@certId", registerRoomInfo.CertId); MySqlParameter p11 = new MySqlParameter("@address", registerRoomInfo.Address); MySqlParameter p12 = new MySqlParameter("@personNum", registerRoomInfo.PersonNum); MySqlParameter p13 = new MySqlParameter("@Oper", registerRoomInfo.Oper); MySqlParameter p14 = new MySqlParameter("@delMark", registerRoomInfo.DelMark); MySqlParameter[] paramArray = new MySqlParameter[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14 }; string sqlUpdate = "update room set inPerson=@inPerson where roomId=@roomId"; MySqlParameter n1 = new MySqlParameter("@roomId", registerRoomInfo.RoomId); MySqlParameter n2 = new MySqlParameter("@inPerson", registerRoomInfo.PersonNum); if (DBOper.ExecuteCommand(sqlInsert, paramArray) == 1 && DBOper.ExecuteCommand(sqlInsert, n1, n2) == 1) { return(true); } else { return(false); } }
public static int insertRecord(Magazine obj) { string strSQL = "insert into magazine(magazineId,magazineName,mdescription,magazineKey) values('" + obj.magazineId + "','" + obj.magazineName + "','" + obj.mdescription + "','" + obj.magazineKey + "')"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static int deleteshenqingRecord(yuyuehuiyi obj) { string strSQL = "delete from shenqinghuiyi where huiyiname='" + obj.huiyiname + "'"; int iRet = DBOper.execNonQueryBySQLText(strSQL); return(iRet); }
public static DataTable huiyishenqingqb(yuyuehuiyi hy) { string str = "select * from shenqinghuiyi where 1=1 "; DataTable dt = DBOper.execQueryBySQLText(str); return(dt); }
/// <summary> /// 执行SQL文本,返回DataTable /// </summary> /// <param name="strSQLText">SQL文本</param> /// <returns>DataTable</returns> public static DataTable execQueryBySQLText(string strSQLText) { OleDbCommand comm = DBOper.CreateCommandDBCon(); comm.CommandType = CommandType.Text; comm.CommandText = strSQLText; DataTable table = null; try { comm.Connection.Open(); OleDbDataReader reader = comm.ExecuteReader(); table = new DataTable(); table.Load(reader); reader.Close(); } catch (Exception ex) { throw ex; } finally { comm.Connection.Close(); } return(table); }
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 string CreateLineListExternTable(string projectCode) { string result = SUCCESS; try { Logger.Info("Start CreateLineListExternTable"); _DBOper = new DBOper(_DbConnStr); string sql = string.Format(@"CREATE TABLE PDB.TB_LL_EXTN_{0} ( LINE_NO VARCHAR2(40 BYTE), RVSN_NO VARCHAR2(4 BYTE), VER_NO VARCHAR2(4 BYTE), PJT_CD VARCHAR2(20 BYTE), REG_DT VARCHAR2(23 BYTE), REG_EMPNO VARCHAR2(10 BYTE), UPD_DT VARCHAR2(23 BYTE), UPD_EMPNO VARCHAR2(10 BYTE) )", projectCode); _DBOper.SaveData(sql); sql = string.Format(@"CREATE UNIQUE INDEX PDB.TB_LL_EXTN_{0}_PK ON PDB.TB_LL_EXTN_{0} (LINE_NO, RVSN_NO, VER_NO, PJT_CD) LOGGING TABLESPACE PDB PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL", projectCode); _DBOper.SaveData(sql); sql = string.Format(@"ALTER TABLE PDB.TB_LL_EXTN_{0} ADD ( CONSTRAINT TB_LL_EXTN_{0}_PK PRIMARY KEY (LINE_NO, RVSN_NO, VER_NO, PJT_CD) USING INDEX PDB.TB_LL_EXTN_{0}_PK ENABLE VALIDATE)", projectCode); _DBOper.SaveData(sql); return result; } catch (Exception ex) { Logger.Error(ex); result = string.Format("CreateLineListExternTable Error: {0}", ex.Message); return result; } }
public string AddLineListCustomProperty(string projectCode, string propertyName, string maxLength) { string result = SUCCESS; try { Logger.Info("Start AddLineListCustomProperty"); _DBOper = new DBOper(_DbConnStr); string sql = string.Format(@"ALTER TABLE TB_LL_EXTN_{0} ADD ({1} VARCHAR2({2}))", projectCode, propertyName, maxLength); _DBOper.SaveData(sql); return result; } catch (Exception ex) { Logger.Error(ex); result = string.Format("AddLineListCustomProperty Error: {0}", ex.Message); return result; } }
private int InsertIsoList(string dwgNo, string sht, string line_no, string project_code) { int result = 0; try { Logger.Info("Start InsertIsoList"); _DBOper = new DBOper(_DbConnStr); string sql = string.Format(@"Insert into ISO_LIST(dwg_no, sht, status, line_no, reg_dt, reg_empno, upd_dt, upd_empno, project_code) values('{0}', '{1}', '', '{2}', SYSDATE, 'Jorge Yoo', SYSDATE, 'Jorge Yoo', '{3}')" , dwgNo, sht, line_no, project_code); Logger.Info(sql); result = _DBOper.SaveData(sql); } catch (Exception ex) { Logger.Error(string.Format("InsertIsoList Error : {0}", ex.Message)); } return result; }
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; }
private int InsertIsoFiles(string dctmObjectId, string fileExt, string fileName, string dwgNo, string sht, string revision, string project_code) { int result = 0; try { Logger.Info("Start InsertIsoList"); _DBOper = new DBOper(_DbConnStr); string sql = string.Format(@"Insert into ISO_FILES(file_no, file_dest, file_extension, file_name, dwg_no, sht, revision, reg_dt, reg_empno, upd_dt, upd_empno, project_code) values(auto_increment_by_1.NEXTVAL, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', SYSDATE, 'Jorge Yoo', SYSDATE, 'Jorge Yoo', '{6}')" , dctmObjectId, fileExt, fileName, dwgNo, sht, revision, project_code); result = _DBOper.SaveData(sql); } catch (Exception ex) { Logger.Error(string.Format("InsertIsoFiles Error : {0}", ex.Message)); } return result; }
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 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 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 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 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; }
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 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; }
private int InsertIsoRevision(string sht, string revision, string dwgNo, string project_code) { int result = 0; try { Logger.Info("Start InsertIsoList"); _DBOper = new DBOper(_DbConnStr); // Update Previous Revision's Current_Revision Y=>N DataTable dtIsoRevision = GetIsoRevision(); DataRow[] dIsoRows = dtIsoRevision.Select(string.Format(@"{0} = '{1}' AND {2} = '{3}' AND {4} = '{5}' AND {6} = '{7}'" , "SHT", sht, "DWG_NO", dwgNo, "Project_Code", project_code, "CURRENT_REVISION", "Y")); if (dIsoRows.Count() > 0) { foreach (DataRow dRow in dIsoRows) { string updateSql = string.Format(@"Update ISO_REVISION SET {0} = '{1}' WHERE {2} = '{3}' AND {4} = '{5}'" , "CURRENT_REVISION", "N", "SHT", dRow["SHT"], "DWG_NO", dRow["DWG_NO"]); result = _DBOper.SaveData(updateSql); } } string sql = string.Format(@"Insert into ISO_REVISION(sht, revision, revision_date, current_revision, dwg_no, reg_dt, reg_empno, upd_dt, upd_empno, project_code) values('{0}', '{1}', SYSDATE, 'Y', '{2}', SYSDATE, 'Jorge Yoo', SYSDATE, 'Jorge Yoo', '{3}')" , sht, revision, dwgNo, project_code); result = _DBOper.SaveData(sql); } catch (Exception ex) { Logger.Error(string.Format("InsertIsoRevision Error : {0}", ex.Message)); } return result; }
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 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 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 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 GetChangedLineList(string revision, string version) { const string CONST_STATE_COLUMN = "STATE"; _DBOper = new DBOper(_DbConnStr); string curSql = string.Format(""); string prevSql = string.Format(""); DataTable dtChangedLineList = new DataTable(); //DataTable dtCurLineList = _DBOper.GetDataTable(curSql); //DataTable dtPrevLineList = _DBOper.GetDataTable(prevSql); //DataColumn state_dc1 = new DataColumn(CONST_STATE_COLUMN, typeof(String)); //insertDT.Columns.Add(state_dc1); //DataColumn state_dc2 = new DataColumn(CONST_STATE_COLUMN, typeof(String)); //deleteDT.Columns.Add(state_dc2); //dt = insertDT.Clone(); //updateDT = deleteDT.Clone(); //DataRow[] dInsertRows = insertDT.Select(); //foreach (DataRow item in dInsertRows) //{ // int count = deleteDT.Select(string.Format("SP_ID = '{0}'", item["SP_ID"])).Count(); // if (count == 0) // { // item[CONST_STATE_COLUMN] = "Add"; // dt.ImportRow(item); // } // else // { // item[CONST_STATE_COLUMN] = "Current"; // updateDT.ImportRow(item); // } //} //DataRow[] dDeleteRows = deleteDT.Select(); //foreach (DataRow item in dDeleteRows) //{ // int count = insertDT.Select(string.Format("SP_ID = '{0}'", item["SP_ID"])).Count(); // if (count == 0) // { // item[CONST_STATE_COLUMN] = "Delete"; // dt.ImportRow(item); // } // else // { // item[CONST_STATE_COLUMN] = "Previous"; // updateDT.ImportRow(item); // } //} return dtChangedLineList; }