/************************************************************
* Function name : SetOpencourseInfo
* Purpose : 개설과정(open course) 신규 항목을 등록하는 처리
* Input :
* Output : string
*************************************************************/
public string SetOpencourseInfo(object[] rParams)
{
Database db = null;
string xRtn = string.Empty;
string xSql = string.Empty;
try
{
db = base.GetDataBase("LMS"); //Database 생성
using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS");
{
xCnnLMS.Open();
OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
OracleCommand xCmdLMS = null;
try
{
xCmdLMS = base.GetSqlCommand(db);
//xCmdLMS.Connection = xCnnLMS;
//xCmdLMS.Transaction = xTransLMS;
OracleParameter[] xPara = null;
// 2. DB 저장 (DB 작업 중에 문제발생 시, 저장된 파일 제거)
xSql = @" INSERT INTO T_OPEN_COURSE
( OPEN_COURSE_ID
, COURSE_ID
, COURSE_YEAR
, COURSE_SEQ
, EDUCATIONAL_ORG
, COURSE_BEGIN_DT
, COURSE_END_DT
, COURSE_BEGIN_APPLY_DT
, COURSE_END_APPLY_DT
, RES_BEGIN_DT
, RES_END_DT
, COURSE_TYPE
, STD_PROGRESS_RATE
, STD_FINAL_EXAM
, STD_REPORT
, PASS_SCORE
, COMPANY_ACCEPT
, MIN_MAN_COUNT
, MAX_MAN_COUNT
, TRAINING_FEE
, TRAINING_SUPPORT_FEE
, TRAINING_SUPPORT_COMP_FEE
, RES_NO
, INS_ID
, INS_DT
, SEND_DT
, SEND_FLG
, USE_FLG
, COURSE_INOUT
, COURSE_PLACE
, COURSE_GUBUN
, MANAGER
) VALUES (
:OPEN_COURSE_ID
, :COURSE_ID
, :COURSE_YEAR
, (SELECT NVL(MAX(COURSE_SEQ) + 1, 1) FROM T_OPEN_COURSE WHERE COURSE_ID = :COURSE_ID)
, :EDUCATIONAL_ORG
, :COURSE_BEGIN_DT
, :COURSE_END_DT
, :COURSE_BEGIN_APPLY_DT
, :COURSE_END_APPLY_DT
, :RES_BEGIN_DT
, :RES_END_DT
, :COURSE_TYPE
, :STD_PROGRESS_RATE
, :STD_FINAL_EXAM
, :STD_REPORT
, :PASS_SCORE
, :COMPANY_ACCEPT
, :MIN_MAN_COUNT
, :MAX_MAN_COUNT
, :TRAINING_FEE
, :TRAINING_SUPPORT_FEE
, :TRAINING_SUPPORT_COMP_FEE
, :RES_NO
, :INS_ID
, SYSDATE
, :SEND_DT
, :SEND_FLG
, :USE_FLG
, :COURSE_INOUT
, :COURSE_PLACE
, :COURSE_GUBUN
, :MANAGER
) ";
vp_l_common_md com = new vp_l_common_md();
string xQID = com.GetMaxIDOfTable(new string[] { "T_OPEN_COURSE", "OPEN_COURSE_ID" });
xPara = new OracleParameter[30];
xPara[0] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xQID);
xPara[1] = base.AddParam("COURSE_ID", OracleType.VarChar, rParams[1]);
xPara[2] = base.AddParam("COURSE_YEAR", OracleType.VarChar, rParams[2]);
//xPara[3] = base.AddParam("COURSE_SEQ", OracleType.Number, rParams[3]);
xPara[3] = base.AddParam("COURSE_INOUT", OracleType.VarChar, rParams[24]);
xPara[4] = base.AddParam("EDUCATIONAL_ORG", OracleType.VarChar, rParams[4]);
xPara[5] = base.AddParam("COURSE_BEGIN_DT", OracleType.DateTime, rParams[12]);
xPara[6] = base.AddParam("COURSE_END_DT", OracleType.DateTime, rParams[13]);
xPara[7] = base.AddParam("COURSE_BEGIN_APPLY_DT", OracleType.DateTime, rParams[10]);
xPara[8] = base.AddParam("COURSE_END_APPLY_DT", OracleType.DateTime, rParams[11]);
xPara[9] = base.AddParam("RES_BEGIN_DT", OracleType.DateTime, rParams[14]);
xPara[10] = base.AddParam("RES_END_DT", OracleType.DateTime, rParams[15]);
xPara[11] = base.AddParam("COURSE_TYPE", OracleType.VarChar, rParams[6]);
xPara[12] = base.AddParam("STD_PROGRESS_RATE", OracleType.Number, rParams[19]);
xPara[13] = base.AddParam("STD_FINAL_EXAM", OracleType.Number, rParams[20]);
xPara[14] = base.AddParam("STD_REPORT", OracleType.Number, DBNull.Value); //현재는 값 넘어오지 않음
xPara[15] = base.AddParam("PASS_SCORE", OracleType.Number, rParams[7]);
xPara[16] = base.AddParam("COMPANY_ACCEPT", OracleType.VarChar, rParams[18]);
xPara[17] = base.AddParam("MIN_MAN_COUNT", OracleType.Number, rParams[16]);
xPara[18] = base.AddParam("MAX_MAN_COUNT", OracleType.Number, rParams[17]);
xPara[19] = base.AddParam("TRAINING_FEE", OracleType.Number, rParams[5]);
xPara[20] = base.AddParam("TRAINING_SUPPORT_FEE", OracleType.Number, rParams[8]);
xPara[21] = base.AddParam("TRAINING_SUPPORT_COMP_FEE", OracleType.Number, rParams[9]);
xPara[22] = base.AddParam("INS_ID", OracleType.VarChar, rParams[22]);
//xPara[11] = base.AddParam("INS_DT", OracleType.VarChar, rParams[11]);
xPara[23] = base.AddParam("SEND_DT", OracleType.VarChar, DBNull.Value);
xPara[24] = base.AddParam("SEND_FLG", OracleType.VarChar, "1");
xPara[25] = base.AddParam("USE_FLG", OracleType.VarChar, rParams[21]);
xPara[26] = base.AddParam("RES_NO", OracleType.VarChar, rParams[23]);
xPara[27] = base.AddParam("COURSE_PLACE", OracleType.VarChar, rParams[25]);
xPara[28] = base.AddParam("COURSE_GUBUN", OracleType.Char, rParams[26]); // 국토해양부 과정여부 추가
xPara[29] = base.AddParam("MANAGER", OracleType.VarChar, rParams[27]); //
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
//선박발송 하는 PACKAGE 호출 부분 추가 필요
//사업주 위탁 교육은 본선으로 발송 하지 않는다!! COURSE_TYPE
if (rParams[6].ToString() != "000002|")
{
OracleParameter[] oOraParams = new OracleParameter[2];
oOraParams[0] = base.AddParam("p_in_table", OracleType.VarChar, "T_OPEN_COURSE");
oOraParams[1] = base.AddParam("p_out_table", OracleType.VarChar, "T_LMS_OPEN_COURSE");
base.Execute(db, CommandType.StoredProcedure, "pkg_lms_datasync.lms_export", oOraParams, xTransLMS);
}
xRtn = xQID;
xTransLMS.Commit(); //트렌잭션 커밋
}
catch (Exception ex)
{
// 트랜잭션 롤백
xTransLMS.Rollback();
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
if (xCnnLMS != null)
{
if (xCnnLMS.State == ConnectionState.Open)
{
xCnnLMS.Close();
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
db = null;
}
return(xRtn);//리터값
}
/************************************************************
* Function name : SetContentsInsert
* Purpose : Contents 신규 항목을 등록하는 처리
* Input : object[] rParams (0: contents_id, 1: contents_type, 2: lang, 3: contents_name, 4: remark,
* 5: contents_file, 6: contents_filename, 7: contents_filepath, 8: ins_id, 9: send_flg)
* Output : string
*************************************************************/
public string SetContentsInsert(object[] rParams)
{
Database db = null;
string xRtn = Boolean.FalseString;
string xSql = "";
string xFilePath = "";
try
{
db = base.GetDataBase("LMS"); //Database 생성
using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS");
{
xCnnLMS.Open();
OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
OracleCommand xCmdLMS = null;
try
{
xCmdLMS = base.GetSqlCommand(db);
// 1. 파일 저장
// 파일이 변경된 경우만, HttpPostedFile 객체가 넘겨져 온다.
if (rParams[5] != null)
{
byte[] xFileData = (byte[])rParams[5];
xFilePath = rParams[11].ToString() + rParams[6].ToString();
FileStream xNewFile = new FileStream(xFilePath, FileMode.Create);
xNewFile.Write(xFileData, 0, xFileData.Length);
xNewFile.Close();
//HttpServerUtility xSU = new HttpServerUtility();
//HttpPostedFile xFile = (HttpPostedFile)rParams[5];
//int xFileLen = xFile.ContentLength; // 파일 사이즈
//byte[] xFileData = new byte[xFileLen]; // 파일 사이즈만큼 byte 배열 설정
//xFile.InputStream.Read(xFileData, 0, xFileLen); // 스트림에서 파일 읽어 바이트 배열에 담는 처리
//// 파일 생성
//string[] xFileNameSector = xFile.FileName.Split(new char[] { '\\' });
//string xFileName = xFileNameSector[xFileNameSector.Length - 1];
//rParams[6] = xFileName;
//xFilePath = rParams[7].ToString() + xFileName;
//FileStream xNewFile = new FileStream(xFilePath, FileMode.Create);
//xNewFile.Write(xFileData, 0, xFileData.Length); // byte 배열 내용을 파일 쓰는 처리
//xNewFile.Close(); // 파일 닫는 처리
}
bool xUpdate = false; //update 할지 여부
OracleParameter[] xPara = null;
if (rParams[10].ToString() == "Y")
{
//subject id를 확인 하여 temp_save_flg = 'Y' 이면 update
//아니면 insert 하기
xSql = " SELECT TEMP_SAVE_FLG FROM t_contents WHERE contents_id = '" + rParams[0] + "' ";
xCmdLMS.CommandText = xSql;
object xobj = base.ExecuteScalar(db, xCmdLMS, xTransLMS);
if (xobj != null && xobj.ToString() == "Y")
{
xUpdate = true;
}
else
{
xUpdate = false; //temp_save_flg = 'Y', send_flg = "1"
}
}
else
{
//무조건 insert
xUpdate = false; //temp_save_flg = 'N', send_flg = "1"
}
string xQID = string.Empty;
if (xUpdate)
{
xQID = rParams[0].ToString();
xSql = @" UPDATE T_CONTENTS
SET
CONTENTS_TYPE = :CONTENTS_TYPE
, CONTENTS_LANG = :CONTENTS_LANG
, CONTENTS_NM = :CONTENTS_NM
, CONTENTS_REMARK = :CONTENTS_REMARK
, CONTENTS_FILE_NM = :CONTENTS_FILE_NM
, UPT_ID = :UPT_ID
, UPT_DT = SYSDATE
WHERE CONTENTS_ID = :CONTENTS_ID ";
xPara = new OracleParameter[7];
xPara[0] = base.AddParam("CONTENTS_TYPE", OracleType.VarChar, rParams[1]);
xPara[1] = base.AddParam("CONTENTS_LANG", OracleType.VarChar, rParams[2]);
xPara[2] = base.AddParam("CONTENTS_NM", OracleType.VarChar, rParams[3]);
xPara[3] = base.AddParam("CONTENTS_REMARK", OracleType.VarChar, rParams[4]);
xPara[4] = base.AddParam("CONTENTS_FILE_NM", OracleType.VarChar, rParams[6]);
xPara[5] = base.AddParam("UPT_ID", OracleType.VarChar, rParams[8]);
xPara[6] = base.AddParam("CONTENTS_ID", OracleType.VarChar, rParams[0]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
}
else
{
xSql = " INSERT INTO t_contents ";
xSql += " (contents_id, contents_type, contents_lang, contents_nm, contents_remark, ";
xSql += " contents_file_nm, ins_id, INS_DT, UPT_ID, UPT_DT, send_flg, TEMP_SAVE_FLG) ";
xSql += " VALUES ( ";
xSql += " :contents_id, :contents_type, :contents_lang, :contents_nm, :contents_remark, ";
xSql += " :contents_file_nm, :ins_id, SYSDATE, :INS_ID, SYSDATE, :send_flg, :TEMP_SAVE_FLG ";
xSql += " ) ";
vp_l_common_md com = new vp_l_common_md();
xQID = com.GetMaxIDOfTable(new string[] { "T_CONTENTS", "contents_id" });
xPara = new OracleParameter[9];
xPara[0] = base.AddParam("CONTENTS_TYPE", OracleType.VarChar, rParams[1]);
xPara[1] = base.AddParam("CONTENTS_LANG", OracleType.VarChar, rParams[2]);
xPara[2] = base.AddParam("CONTENTS_NM", OracleType.VarChar, rParams[3]);
xPara[3] = base.AddParam("CONTENTS_REMARK", OracleType.VarChar, rParams[4]);
xPara[4] = base.AddParam("CONTENTS_FILE_NM", OracleType.VarChar, rParams[6]);
xPara[5] = base.AddParam("INS_ID", OracleType.VarChar, rParams[8]);
xPara[6] = base.AddParam("CONTENTS_ID", OracleType.VarChar, xQID);
xPara[7] = base.AddParam("send_flg", OracleType.VarChar, rParams[9]);
xPara[8] = base.AddParam("TEMP_SAVE_FLG", OracleType.VarChar, rParams[10]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
//// 2. DB 저장 (DB 작업 중에 문제발생 시, 저장된 파일 제거)
//xSql = " INSERT INTO t_contents ";
//xSql += " (contents_id, contents_type, contents_lang, contents_nm, contents_remark, ";
//xSql += " contents_file_nm, ins_id, send_flg) ";
//xSql += " VALUES ( ";
//xSql += string.Format("'{0}', ", rParams[0].ToString()); // contents_id
//xSql += string.Format("'{0}', ", rParams[1].ToString()); // contents_type
//xSql += string.Format("'{0}', ", rParams[2].ToString()); // lang
//xSql += string.Format("'{0}', ", rParams[3].ToString()); // contents_name
//xSql += string.Format("'{0}', ", rParams[4].ToString()); // remark
//xSql += string.Format("'{0}', ", rParams[6].ToString()); // contents_filename
//xSql += string.Format("'{0}', ", rParams[8].ToString()); // ins_id
//xSql += string.Format("'{0}' ", rParams[9].ToString()); // send_flg
//xSql += " ) ";
//xCmdLMS.CommandText = xSql;
//base.Execute("LMS", xCmdLMS, xTransLMS);
}
//temp save 아닐 경우선박으로 발송
if (rParams[10].ToString() == "N")
{
OracleParameter[] oOraParams = null;
oOraParams = new OracleParameter[2];
oOraParams[0] = base.AddParam("p_in_table", OracleType.VarChar, "T_CONTENTS");
oOraParams[1] = base.AddParam("p_out_table", OracleType.VarChar, "T_LMS_CONTENTS");
base.Execute(db, CommandType.StoredProcedure, "pkg_lms_datasync.lms_export", oOraParams, xTransLMS);
}
xRtn = xQID;
xTransLMS.Commit(); //트렌잭션 커밋
}
catch (Exception ex)
{
// 파일 제거
if (xFilePath.Trim() != "")
{
File.Delete(xFilePath);
}
// 트랜잭션 롤백
xTransLMS.Rollback();
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
if (xCnnLMS != null)
{
if (xCnnLMS.State == ConnectionState.Open)
{
xCnnLMS.Close();
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
db = null;
}
return(xRtn);//리터값
}
/************************************************************
* Function name : SetAssessInfo
* Purpose : 평가문제(ASSESS QUESTION) 신규 항목을 등록하는 처리
* Input :
* Output : string
*************************************************************/
public string SetAssessInfo(object[] rParams)
{
Database db = null;
string xRtn = string.Empty;
string xSql = string.Empty;
try
{
db = base.GetDataBase("LMS"); //Database 생성
using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS");
{
xCnnLMS.Open();
OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
OracleCommand xCmdLMS = null;
try
{
xCmdLMS = base.GetSqlCommand(db);
//xCmdLMS.Connection = xCnnLMS;
//xCmdLMS.Transaction = xTransLMS;
OracleParameter[] xPara = null;
bool xUpdate = false; //update 할지 여부
string xQID = string.Empty;
if (rParams[12].ToString() == "Y")
{
//subject id를 확인 하여 temp_save_flg = 'Y' 이면 update
//아니면 insert 하기
xSql = " SELECT TEMP_SAVE_FLG FROM T_ASSESS_QUESTION WHERE QUESTION_ID = '" + rParams[0] + "' ";
xCmdLMS.CommandText = xSql;
object xobj = base.ExecuteScalar(db, xCmdLMS, xTransLMS);
if (xobj != null && xobj.ToString() == "Y")
{
xUpdate = true;
}
else
{
xUpdate = false; //temp_save_flg = 'Y', send_flg = "1"
}
}
else
{
//무조건 insert
xUpdate = false; //temp_save_flg = 'N', send_flg = "1"
}
if (xUpdate)
{
xQID = rParams[0].ToString();
xSql = @" UPDATE T_ASSESS_QUESTION SET
QUESTION_KIND = :QUESTION_KIND
, QUESTION_LANG = :QUESTION_LANG
, QUESTION_TYPE = :QUESTION_TYPE
, COURSE_GROUP = :COURSE_GROUP
, COURSE_FIELD = :COURSE_FIELD
, QUESTION_SCORE = :QUESTION_SCORE
, QUESTION_ANSWER = :QUESTION_ANSWER
, QUESTION_CONTENT = :QUESTION_CONTENT
, QUESTION_EXAMPLE = :QUESTION_EXAMPLE
, QUESTION_DESC = :QUESTION_DESC
, UPT_ID = :INS_ID
, UPT_DT = SYSDATE
, USE_FLG = :USE_FLG
, TEMP_SAVE_FLG = :TEMP_SAVE_FLG
, COURSE_LIST = :COURSE_LIST
, SUBJECT_LIST = :SUBJECT_LIST
WHERE QUESTION_ID = :QUESTION_ID
";
xPara = new OracleParameter[16];
xPara[0] = base.AddParam("QUESTION_ID", OracleType.VarChar, xQID);
xPara[1] = base.AddParam("QUESTION_KIND", OracleType.VarChar, rParams[1]);
xPara[2] = base.AddParam("QUESTION_LANG", OracleType.VarChar, rParams[2]);
xPara[3] = base.AddParam("QUESTION_TYPE", OracleType.VarChar, rParams[3]);
xPara[4] = base.AddParam("COURSE_GROUP", OracleType.VarChar, rParams[4]);
xPara[5] = base.AddParam("COURSE_FIELD", OracleType.VarChar, rParams[5]);
xPara[6] = base.AddParam("QUESTION_SCORE", OracleType.Number, rParams[10]);
xPara[7] = base.AddParam("QUESTION_ANSWER", OracleType.Clob, rParams[6]);
xPara[8] = base.AddParam("QUESTION_CONTENT", OracleType.Clob, rParams[7]);
xPara[9] = base.AddParam("QUESTION_EXAMPLE", OracleType.Clob, rParams[8]);
xPara[10] = base.AddParam("QUESTION_DESC", OracleType.Clob, rParams[9]);
xPara[11] = base.AddParam("INS_ID", OracleType.VarChar, rParams[11]);
xPara[12] = base.AddParam("USE_FLG", OracleType.VarChar, "Y");
xPara[13] = base.AddParam("TEMP_SAVE_FLG", OracleType.VarChar, rParams[12]);
xPara[14] = base.AddParam("COURSE_LIST", OracleType.VarChar, rParams[13]);
xPara[15] = base.AddParam("SUBJECT_LIST", OracleType.VarChar, rParams[14]);
}
else
{
// 2. DB 저장 (DB 작업 중에 문제발생 시, 저장된 파일 제거)
xSql = @" INSERT INTO T_ASSESS_QUESTION
( QUESTION_ID
, QUESTION_KIND
, QUESTION_LANG
, QUESTION_TYPE
, COURSE_GROUP
, COURSE_FIELD
, QUESTION_SCORE
, QUESTION_ANSWER
, QUESTION_CONTENT
, QUESTION_EXAMPLE
, QUESTION_DESC
, INS_ID
, INS_DT
, UPT_ID
, UPT_DT
, SEND_DT
, SEND_FLG
, USE_FLG
, TEMP_SAVE_FLG
, COURSE_LIST
, SUBJECT_LIST
) VALUES (
:QUESTION_ID
, :QUESTION_KIND
, :QUESTION_LANG
, :QUESTION_TYPE
, :COURSE_GROUP
, :COURSE_FIELD
, :QUESTION_SCORE
, :QUESTION_ANSWER
, :QUESTION_CONTENT
, :QUESTION_EXAMPLE
, :QUESTION_DESC
, :INS_ID
, SYSDATE
, :INS_ID
, SYSDATE
, :SEND_DT
, :SEND_FLG
, :USE_FLG
, :TEMP_SAVE_FLG
, :COURSE_LIST
, :SUBJECT_LIST
) ";
vp_l_common_md com = new vp_l_common_md();
xQID = com.GetMaxIDOfTable(new string[] { "T_ASSESS_QUESTION", "QUESTION_ID" });
xPara = new OracleParameter[18];
xPara[0] = base.AddParam("QUESTION_ID", OracleType.VarChar, xQID);
xPara[1] = base.AddParam("QUESTION_KIND", OracleType.VarChar, rParams[1]);
xPara[2] = base.AddParam("QUESTION_LANG", OracleType.VarChar, rParams[2]);
xPara[3] = base.AddParam("QUESTION_TYPE", OracleType.VarChar, rParams[3]);
xPara[4] = base.AddParam("COURSE_GROUP", OracleType.VarChar, rParams[4]);
xPara[5] = base.AddParam("COURSE_FIELD", OracleType.VarChar, rParams[5]);
xPara[6] = base.AddParam("QUESTION_SCORE", OracleType.Number, rParams[10]);
xPara[7] = base.AddParam("QUESTION_ANSWER", OracleType.Clob, rParams[6]);
xPara[8] = base.AddParam("QUESTION_CONTENT", OracleType.Clob, rParams[7]);
xPara[9] = base.AddParam("QUESTION_EXAMPLE", OracleType.Clob, rParams[8]);
xPara[10] = base.AddParam("QUESTION_DESC", OracleType.Clob, rParams[9]);
xPara[11] = base.AddParam("INS_ID", OracleType.VarChar, rParams[11]);
//xPara[8] = base.AddParam("INS_DT", OracleType.VarChar, rParams[7]);
xPara[12] = base.AddParam("SEND_DT", OracleType.DateTime, DBNull.Value);
xPara[13] = base.AddParam("SEND_FLG", OracleType.VarChar, "1");
xPara[14] = base.AddParam("USE_FLG", OracleType.VarChar, "Y");
xPara[15] = base.AddParam("TEMP_SAVE_FLG", OracleType.VarChar, rParams[12]);
xPara[16] = base.AddParam("COURSE_LIST", OracleType.VarChar, rParams[13]);
xPara[17] = base.AddParam("SUBJECT_LIST", OracleType.VarChar, rParams[14]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
xRtn = xQID;
}
//선박발송 하는 PACKAGE 호출 부분 추가 필요
if (rParams[12].ToString() == "N")
{
OracleParameter[] oOraParams = null;
oOraParams = new OracleParameter[2];
oOraParams[0] = base.AddParam("p_in_table", OracleType.VarChar, "T_ASSESS_QUESTION");
oOraParams[1] = base.AddParam("p_out_table", OracleType.VarChar, "T_LMS_ASSESS_QUESTION");
base.Execute(db, CommandType.StoredProcedure, "pkg_lms_datasync.lms_export", oOraParams, xTransLMS);
}
xTransLMS.Commit(); //트렌잭션 커밋
}
catch (Exception ex)
{
// 트랜잭션 롤백
xTransLMS.Rollback();
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
if (xCnnLMS != null)
{
if (xCnnLMS.State == ConnectionState.Open)
{
xCnnLMS.Close();
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
db = null;
}
return(xRtn);//리터값
}
/************************************************************
* Function name : SetTextBookInsert
* Purpose : TextBook 신규 항목을 등록하는 처리
* Input : object[] rParams (0: textbook_type, 1: textbook_lang, 2: course_group, 3: course_field,
* 4: author, 5: price, 6: publisher, 7: textbook_desc, 8: textbook_intro, 9: textbook_nm
* 10: textbook_file, 11: textbook_filename, 12: Ins_ID, 13: send_flg)
* Output : string
*************************************************************/
public string SetTextBookInsert(object[] rParams)
{
Database db = null;
string xRtn = string.Empty;
string xSql = string.Empty;
try
{
db = base.GetDataBase("LMS"); //Database 생성
using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS");
{
xCnnLMS.Open();
OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
OracleCommand xCmdLMS = null;
try
{
xCmdLMS = base.GetSqlCommand(db);
//xCmdLMS.Connection = xCnnLMS;
//xCmdLMS.Transaction = xTransLMS;
OracleParameter[] xPara = null;
// 1. 파일 읽기
// 파일이 변경된 경우만, HttpPostedFile 객체가 넘겨져 온다.
byte[] xFileblob = null;
if (rParams[10] != null)
{
xFileblob = (byte[])rParams[10];
//HttpPostedFile xFile = (HttpPostedFile)rParams[10];
//int xFileLen = xFile.ContentLength; // 파일 사이즈
//byte[] xFileData = new byte[xFileLen]; // 파일 사이즈만큼 byte 배열 설정
//xFile.InputStream.Read(xFileData, 0, xFileLen); // 스트림에서 파일 읽어 바이트 배열에 담는 처리
//string[] xFileNameSector = xFile.FileName.Split(new char[] { '\\' });
//xFileName = xFileNameSector[xFileNameSector.Length - 1];
//xFileblob = xFileData;
}
else
{
xSql = " SELECT TEXTBOOK_FILE FROM t_textbook WHERE TEXTBOOK_ID = '" + rParams[15] + "' ";
xCmdLMS.CommandText = xSql;
object xobj = base.ExecuteScalar(db, xCmdLMS, xTransLMS);
xFileblob = (byte[])xobj;
}
bool xUpdate = false; //update 할지 여부
string xQID = string.Empty;
if (rParams[14].ToString() == "Y")
{
//subject id를 확인 하여 temp_save_flg = 'Y' 이면 update
//아니면 insert 하기
xSql = " SELECT TEMP_SAVE_FLG FROM t_textbook WHERE TEXTBOOK_ID = '" + rParams[15] + "' ";
xCmdLMS.CommandText = xSql;
object xobj = base.ExecuteScalar(db, xCmdLMS, xTransLMS);
if (xobj != null && xobj.ToString() == "Y")
{
xUpdate = true;
}
else
{
xUpdate = false; //temp_save_flg = 'Y', send_flg = "1"
}
}
else
{
//무조건 insert
xUpdate = false; //temp_save_flg = 'N', send_flg = "1"
}
if (xUpdate)
{
xQID = rParams[15].ToString();
xSql = @"
UPDATE T_TEXTBOOK SET
TEXTBOOK_TYPE = :TEXTBOOK_TYPE
, TEXTBOOK_LANG = :TEXTBOOK_LANG
, COURSE_GROUP = :COURSE_GROUP
, COURSE_FIELD = :COURSE_FIELD
, AUTHOR = :AUTHOR
, PRICE = :PRICE
, PUBLISHER = :PUBLISHER
, TEXTBOOK_DESC = :TEXTBOOK_DESC
, TEXTBOOK_INTRO = :TEXTBOOK_INTRO
, TEXTBOOK_NM = :TEXTBOOK_NM
, TEXTBOOK_FILE = :TEXTBOOK_FILE
, TEXTBOOK_FILE_NM = :TEXTBOOK_FILE_NM
, UPT_ID = :INS_ID
, UPT_DT = SYSDATE
, PUB_DT = :PUB_DT
WHERE TEXTBOOK_ID = :TEXTBOOK_ID
";
xPara = new OracleParameter[15];
xPara[0] = base.AddParam("textbook_id", OracleType.VarChar, rParams[15]);
xPara[1] = base.AddParam("textbook_type", OracleType.VarChar, rParams[0]);
xPara[2] = base.AddParam("textbook_lang", OracleType.VarChar, rParams[1]);
xPara[3] = base.AddParam("course_group", OracleType.VarChar, rParams[2]);
xPara[4] = base.AddParam("course_field", OracleType.VarChar, rParams[3]);
xPara[5] = base.AddParam("author", OracleType.VarChar, rParams[4]);
xPara[6] = base.AddParam("price", OracleType.VarChar, rParams[5]);
xPara[7] = base.AddParam("publisher", OracleType.VarChar, rParams[6]);
xPara[8] = base.AddParam("textbook_desc", OracleType.Clob, rParams[7]);
xPara[9] = base.AddParam("textbook_intro", OracleType.Clob, rParams[8]);
xPara[10] = base.AddParam("textbook_nm", OracleType.VarChar, rParams[9]);
//xPara[11] = base.AddParam("textbook_file", OracleType.Blob, rParams[10]);
xPara[11] = base.AddParam("textbook_file", OracleType.Blob, xFileblob);
xPara[12] = base.AddParam("textbook_file_nm", OracleType.VarChar, rParams[11]);
xPara[13] = base.AddParam("ins_id", OracleType.VarChar, rParams[12]);
xPara[14] = base.AddParam("pub_dt", OracleType.VarChar, rParams[16]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
xRtn = xQID;
}
else
{
// 2. DB 저장 (DB 작업 중에 문제발생 시, 저장된 파일 제거)
xSql = @" INSERT INTO t_textbook
(
textbook_id, textbook_type, textbook_lang, course_group,
course_field, author, price, publisher, textbook_desc, textbook_intro,
textbook_nm, textbook_file, textbook_file_nm, ins_id, INS_DT, UPT_ID, UPT_DT, send_flg
, TEMP_SAVE_FLG , PUB_DT
)
VALUES(
:textbook_id, :textbook_type, :textbook_lang, :course_group,
:course_field, :author, :price, :publisher, :textbook_desc, :textbook_intro,
:textbook_nm, :textbook_file, :textbook_file_nm, :ins_id, SYSDATE, :INS_ID, SYSDATE, :send_flg
, :TEMP_SAVE_FLG , :PUB_DT
)";
vp_l_common_md com = new vp_l_common_md();
xQID = com.GetMaxIDOfTable(new string[] { "t_textbook", "textbook_id" });
xPara = new OracleParameter[17];
xPara[0] = base.AddParam("textbook_id", OracleType.VarChar, xQID);
xPara[1] = base.AddParam("textbook_type", OracleType.VarChar, rParams[0]);
xPara[2] = base.AddParam("textbook_lang", OracleType.VarChar, rParams[1]);
xPara[3] = base.AddParam("course_group", OracleType.VarChar, rParams[2]);
xPara[4] = base.AddParam("course_field", OracleType.VarChar, rParams[3]);
xPara[5] = base.AddParam("author", OracleType.VarChar, rParams[4]);
xPara[6] = base.AddParam("price", OracleType.VarChar, rParams[5]);
xPara[7] = base.AddParam("publisher", OracleType.VarChar, rParams[6]);
xPara[8] = base.AddParam("textbook_desc", OracleType.Clob, rParams[7]);
xPara[9] = base.AddParam("textbook_intro", OracleType.Clob, rParams[8]);
xPara[10] = base.AddParam("textbook_nm", OracleType.VarChar, rParams[9]);
//xPara[11] = base.AddParam("textbook_file", OracleType.Blob, rParams[10]);
xPara[11] = base.AddParam("textbook_file", OracleType.Blob, xFileblob);
xPara[12] = base.AddParam("textbook_file_nm", OracleType.VarChar, rParams[11]);
xPara[13] = base.AddParam("ins_id", OracleType.VarChar, rParams[12]);
xPara[14] = base.AddParam("send_flg", OracleType.VarChar, rParams[13]);
xPara[15] = base.AddParam("TEMP_SAVE_FLG", OracleType.VarChar, rParams[14]);
xPara[16] = base.AddParam("PUB_DT", OracleType.VarChar, rParams[16]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
xRtn = xQID;
}
//temp save 아닐 경우선박으로 발송
if (rParams[14].ToString() == "N")
{
OracleParameter[] oOraParams = null;
oOraParams = new OracleParameter[2];
oOraParams[0] = base.AddParam("p_in_table", OracleType.VarChar, "T_TEXTBOOK");
oOraParams[1] = base.AddParam("p_out_table", OracleType.VarChar, "T_LMS_TEXTBOOK");
base.Execute(db, CommandType.StoredProcedure, "pkg_lms_datasync.lms_export", oOraParams, xTransLMS);
}
xTransLMS.Commit(); //트렌잭션 커밋
}
catch (Exception ex)
{
// 트랜잭션 롤백
xTransLMS.Rollback();
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(xRtn);//리터값
}
/************************************************************
* Function name : SetReceivedCourseResult_Del
* Purpose : Course Result 등록
* Input :
* Output : string
*************************************************************/
#region public string SetReceivedCourseResult_Del(object[] rParams)
public string SetReceivedCourseResult_Del(object[] rParams)
{
Database db = null;
string xRtn = string.Empty;
string xSql = string.Empty;
try
{
db = base.GetDataBase("LMS"); //Database 생성
using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS");
{
xCnnLMS.Open();
OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
OracleCommand xCmdLMS = null;
try
{
xCmdLMS = base.GetSqlCommand(db);
OracleParameter[] xPara = null;
ArrayList xarrSMSUser = new ArrayList(); //sms 발송할 user 정보 담기
DataSet dsUser = null;
string[] xUserarr = rParams[2].ToString().Split('|');
for (int i = 0; i < xUserarr.Length; i++)
{
if (xUserarr[i] != string.Empty)
{
xSql = " SELECT USER_ID FROM T_COURSE_RESULT WHERE USER_ID = '" + xUserarr[i] + "' AND OPEN_COURSE_ID = '" + rParams[0] + "' AND COURSE_RESULT_SEQ = 1 ";
xCmdLMS.CommandText = xSql;
object xobj = base.ExecuteScalar(db, xCmdLMS, xTransLMS);
if (xobj != null && xobj.ToString() != string.Empty)
{
//insert
xSql = @" DELETE FROM T_COURSE_RESULT
WHERE OPEN_COURSE_ID = :OPEN_COURSE_ID
AND USER_ID = :USER_ID
AND COURSE_RESULT_SEQ = 1
";
xPara = new OracleParameter[2];
xPara[0] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, rParams[0]);
xPara[1] = base.AddParam("USER_ID", OracleType.VarChar, xUserarr[i]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
//sms 발송할 사용자 정보 담기
xSql = " SELECT USER_ID, USER_NM_KOR, MOBILE_PHONE FROM T_USER WHERE USER_ID = '" + xUserarr[i] + "' ";
xCmdLMS.CommandText = xSql;
dsUser = base.ExecuteDataSet(db, xCmdLMS, xTransLMS);
if (dsUser.Tables[0].Rows.Count > 0)
{
DataRow dr = dsUser.Tables[0].Rows[0];
xarrSMSUser.Add(new string[] { dr["USER_ID"].ToString(), dr["USER_NM_KOR"].ToString(), dr["MOBILE_PHONE"].ToString() });
}
//설문 target 삭제 하는 부분
}
}
}
//=============================================
/*SMS 문자는 데이터 저장 후 발송...*/
string[] xMasterParams = new string[9];
xMasterParams[0] = "한진해운 운항훈련원";
xMasterParams[1] = string.Empty; // SMS 회신번호
xMasterParams[2] = rParams[3] + " [예약취소] " + rParams[4]; // SMS 발송내용
xMasterParams[3] = xarrSMSUser.Count.ToString(); //보낼사람 count
xMasterParams[4] = string.Empty; // 과정코드
xMasterParams[5] = rParams[1].ToString();
xMasterParams[6] = "I"; // 지금 보낼 경우
if (Encoding.Default.GetByteCount(xMasterParams[2]) > 80)
{
xMasterParams[8] = "10"; // 10: 이미지 없는 MMS 코드, 00:일반 문자
}
else
{
xMasterParams[8] = "00"; // 10: 이미지 없는 MMS 코드, 00:일반 문자
}
string[,] xDetailParams = new string[xarrSMSUser.Count, 3];
string[] xDetail = null;
for (int i = 0; i < xarrSMSUser.Count; i++)
{
xDetail = (string[])xarrSMSUser[i];
xDetailParams[i, 0] = xDetail[0];
xDetailParams[i, 1] = xDetail[1];
xDetailParams[i, 2] = xDetail[2].Replace("-", "");
}
object[] xParamsO = new object[2];
xParamsO[0] = (object)xMasterParams;
xParamsO[1] = (object)xDetailParams;
vp_l_common_md sms = new vp_l_common_md();
sms.SetSentSMSBoxInsert(xParamsO);
//=============================================
xRtn = "Y";
xTransLMS.Commit(); //트렌잭션 커밋
}
catch (Exception ex)
{
// 트랜잭션 롤백
xTransLMS.Rollback();
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
if (xCnnLMS != null)
{
if (xCnnLMS.State == ConnectionState.Open)
{
xCnnLMS.Close();
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
db = null;
}
return(xRtn);//리터값
}
/************************************************************
* Function name : SetReceivedCourseResult
* Purpose : Course Result 등록
* Input :
* Output : string
*************************************************************/
#region public string SetReceivedCourseResult(object[] rParams)
public string SetReceivedCourseResult(object[] rParams)
{
Database db = null;
string xRtn = string.Empty;
string xSql = string.Empty;
try
{
db = base.GetDataBase("LMS"); //Database 생성
using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS");
{
xCnnLMS.Open();
OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
OracleCommand xCmdLMS = null;
try
{
xCmdLMS = base.GetSqlCommand(db);
OracleParameter[] xPara = null;
ArrayList xarrSMSUser = new ArrayList(); //sms 발송할 user 정보 담기
DataSet dsUser = null;
string[] xUserarr = rParams[2].ToString().Split('|');
for (int i = 0; i < xUserarr.Length; i++)
{
if (xUserarr[i] != string.Empty)
{
xSql = " SELECT USER_ID FROM T_COURSE_RESULT WHERE USER_ID = '" + xUserarr[i] + "' AND OPEN_COURSE_ID = '" + rParams[0] + "' AND COURSE_RESULT_SEQ = 1 ";
xCmdLMS.CommandText = xSql;
object xobj = base.ExecuteScalar(db, xCmdLMS, xTransLMS);
if (xobj == null || xobj.ToString() == string.Empty)
{
//insert
xSql = @" INSERT INTO T_COURSE_RESULT
( USER_ID
, OPEN_COURSE_ID
, COURSE_RESULT_SEQ
, USER_COMPANY_ID
, USER_DEPT_CODE
, USER_DUTY_STEP
, APPROVAL_FLG
, APPROVAL_DT
, PASS_FLG
, USER_COURSE_BEGIN_DT, USER_COURSE_END_DT
, INSURANCE_FLG
, INSURANCE_DT
, INS_ID ,INS_DT ,UPT_ID ,UPT_DT
, CONFIRM
)
SELECT U.USER_ID
, O.OPEN_COURSE_ID
, 1
, U.COMPANY_ID
, U.DEPT_CODE
, U.DUTY_STEP
, '000003' /*APPROVAL_FLG M_CD = '0019'*/
, SYSDATE
, '000004' /*PASS_FLG M_CD = '0010'*/
, O.COURSE_BEGIN_DT
, O.COURSE_END_DT
, CASE WHEN U.ENTER_DT IS NOT NULL THEN 'Y' ELSE 'N' END INSURANCE_FLG
, U.ENTER_DT
, :ADUSER_ID, SYSDATE, :ADUSER_ID, SYSDATE
, '1' /*확정*/
FROM T_USER U, T_OPEN_COURSE O
WHERE O.OPEN_COURSE_ID = :OPEN_COURSE_ID
AND U.USER_ID = :USER_ID
";
xPara = new OracleParameter[3];
xPara[0] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, rParams[0]);
xPara[1] = base.AddParam("ADUSER_ID", OracleType.VarChar, rParams[1]);
xPara[2] = base.AddParam("USER_ID", OracleType.VarChar, xUserarr[i]);
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xPara, xTransLMS);
//sms 발송할 사용자 정보 담기
xSql = " SELECT USER_ID, USER_NM_KOR, MOBILE_PHONE FROM T_USER WHERE USER_ID = '" + xUserarr[i] + "' ";
xCmdLMS.CommandText = xSql;
dsUser = base.ExecuteDataSet(db, xCmdLMS, xTransLMS);
if (dsUser.Tables[0].Rows.Count > 0)
{
DataRow dr = dsUser.Tables[0].Rows[0];
xarrSMSUser.Add(new string[] { dr["USER_ID"].ToString(), dr["USER_NM_KOR"].ToString(), dr["MOBILE_PHONE"].ToString() });
}
////설문 target 추가 하는 부분
//vp_l_common_md lc = new vp_l_common_md();
//lc.SetSurveyTarget(new string[]{rParams[0].ToString(), xUserarr[i]}, db, xCmdLMS, xTransLMS);
}
}
}
//=============================================
/*SMS 문자는 데이터 저장 후 발송...*/
string[] xMasterParams = new string[9];
xMasterParams[0] = "지마린 운항훈련원";
xMasterParams[1] = string.Empty; // SMS 회신번호
xMasterParams[2] = rParams[3] + " [예약] " + rParams[4]; // SMS 발송내용
xMasterParams[3] = xarrSMSUser.Count.ToString(); //보낼사람 count
xMasterParams[4] = string.Empty; // 과정코드
xMasterParams[5] = rParams[1].ToString();
xMasterParams[6] = "I"; // 지금 보낼 경우
//xMasterParams[8] = "10"; // 10: 이미지 없는 MMS 코드, 00:일반 문자
if (Encoding.Default.GetByteCount(xMasterParams[2]) > 80)
{
xMasterParams[8] = "10"; // 10: 이미지 없는 MMS 코드, 00:일반 문자
}
else
{
xMasterParams[8] = "00"; // 10: 이미지 없는 MMS 코드, 00:일반 문자
}
string[,] xDetailParams = new string[xarrSMSUser.Count, 3];
string[] xDetail = null;
for (int i = 0; i < xarrSMSUser.Count; i++)
{
xDetail = (string[])xarrSMSUser[i];
xDetailParams[i, 0] = xDetail[0]; //수신자id
xDetailParams[i, 1] = xDetail[1]; //수신자 이름
xDetailParams[i, 2] = xDetail[2].Replace("-", ""); //수신자 전화번호
}
object[] xParamsO = new object[2];
xParamsO[0] = (object)xMasterParams;
xParamsO[1] = (object)xDetailParams;
vp_l_common_md sms = new vp_l_common_md();
sms.SetSentSMSBoxInsert(xParamsO);
//=============================================
xRtn = "Y";
xTransLMS.Commit(); //트렌잭션 커밋
}
catch (Exception ex)
{
// 트랜잭션 롤백
xTransLMS.Rollback();
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
if (xCnnLMS != null)
{
if (xCnnLMS.State == ConnectionState.Open)
{
xCnnLMS.Close();
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
db = null;
}
return(xRtn);//리터값
}
