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