/************************************************************
* 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);//리터값
}