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