public int Update_Est_Refusal_Reply(IDbConnection conn
, IDbTransaction trx
, object comp_id
, object est_id
, object estterm_ref_id
, object estterm_sub_id
, object estterm_step_id
, object tgt_dept_id
, object tgt_emp_id
, object reply_emp_id
, object reply_comment
, object update_user_ref_id)
{
string query = @"
UPDATE EST_REFUSAL
SET REPLY_EMP_ID = @REPLY_EMP_ID
, REPLY_COMMENT = @REPLY_COMMENT
, REPLY_DATE = GETDATE()
, UPDATE_USER = @UPDATE_USER
, UPDATE_DATE = GETDATE()
WHERE
COMP_ID = @COMP_ID
AND EST_ID = @EST_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND ESTTERM_SUB_ID = @ESTTERM_SUB_ID
AND ESTTERM_STEP_ID = @ESTTERM_STEP_ID
AND TGT_DEPT_ID = @TGT_DEPT_ID
AND TGT_EMP_ID = @TGT_EMP_ID
";
IDbDataParameter[] paramArray = CreateDataParameters(10);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[4] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[4].Value = estterm_step_id;
paramArray[5] = CreateDataParameter("@TGT_DEPT_ID", SqlDbType.Int);
paramArray[5].Value = tgt_dept_id;
paramArray[6] = CreateDataParameter("@TGT_EMP_ID", SqlDbType.Int);
paramArray[6].Value = tgt_emp_id;
paramArray[7] = CreateDataParameter("@REPLY_EMP_ID", SqlDbType.Int);
paramArray[7].Value = reply_emp_id;
paramArray[8] = CreateDataParameter("@REPLY_COMMENT", SqlDbType.NVarChar);
paramArray[8].Value = reply_comment;
paramArray[9] = CreateDataParameter("@UPDATE_USER", SqlDbType.Int);
paramArray[9].Value = update_user_ref_id;
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
public int Insert(IDbConnection conn
, IDbTransaction trx
, int comp_id
, string est_id
, object up_est_id
, string est_name
, string header_color
, string grade_confirm_yn
, string bias_yn
, object bias_dept_use_yn
, string tgt_opinion_yn
, string feedback_yn
, int estterm_sub
, int estterm_step
, string fixed_weight_use_yn
, double fixed_weight
, int point_ctrl_step
, int grade_ctrl_step
, string owner_type
, string est_style_id
, string link_est_id
, string weight_type
, string scale_type
, string status_style_id
, string q_style_id
, string q_item_desc_use_yn
, object q_tgt_pos_biz_use_yn
, object all_step_visible_yn
, object emp_com_dept_yn
, string bias_type_id
, string visible_past_point_yn
, string est_qtt_mbo_yn
, string mbo_score_estimate_yn
, string dashboard_type
, string question_previous_step_yn
, string use_yn
, DateTime create_date
, int create_user)
{
string query = @"INSERT INTO EST_INFO(COMP_ID
,EST_ID
,UP_EST_ID
,EST_NAME
,HEADER_COLOR
,GRADE_CONFIRM_YN
,BIAS_YN
,BIAS_DEPT_USE_YN
,TGT_OPINION_YN
,FEEDBACK_YN
,ESTTERM_SUB
,ESTTERM_STEP
,FIXED_WEIGHT_USE_YN
,FIXED_WEIGHT
,POINT_CTRL_STEP
,GRADE_CTRL_STEP
,OWNER_TYPE
,EST_STYLE_ID
,LINK_EST_ID
,Q_STYLE_ID
,WEIGHT_TYPE
,SCALE_TYPE
,STATUS_STYLE_ID
,Q_ITEM_DESC_USE_YN
,Q_TGT_POS_BIZ_USE_YN
,ALL_STEP_VISIBLE_YN
,EMP_COM_DEPT_YN
,BIAS_TYPE_ID
,VISIBLE_PAST_POINT_YN
,EST_QTT_MBO_YN
,MBO_SCORE_ESTIMATE_YN
,DASHBOARD_TYPE
,Q_PREVIOUS_STEP_VISIBLE_YN
,USE_YN
,CREATE_DATE
,CREATE_USER
)
VALUES (@COMP_ID
,@EST_ID
,@UP_EST_ID
,@EST_NAME
,@HEADER_COLOR
,@GRADE_CONFIRM_YN
,@BIAS_YN
,@BIAS_DEPT_USE_YN
,@TGT_OPINION_YN
,@FEEDBACK_YN
,@ESTTERM_SUB
,@ESTTERM_STEP
,@FIXED_WEIGHT_USE_YN
,@FIXED_WEIGHT
,@POINT_CTRL_STEP
,@GRADE_CTRL_STEP
,@OWNER_TYPE
,@EST_STYLE_ID
,@LINK_EST_ID
,@Q_STYLE_ID
,@WEIGHT_TYPE
,@SCALE_TYPE
,@STATUS_STYLE_ID
,@Q_ITEM_DESC_USE_YN
,@Q_TGT_POS_BIZ_USE_YN
,@ALL_STEP_VISIBLE_YN
,@EMP_COM_DEPT_YN
,@BIAS_TYPE_ID
,@VISIBLE_PAST_POINT_YN
,@EST_QTT_MBO_YN
,@MBO_SCORE_ESTIMATE_YN
,@DASHBOARD_TYPE
,@Q_PREVIOUS_STEP_VISIBLE_YN
,@USE_YN
,@CREATE_DATE
,@CREATE_USER
)" ;
IDbDataParameter[] paramArray = CreateDataParameters(36);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 20);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@UP_EST_ID", SqlDbType.NVarChar, 20);
paramArray[2].Value = up_est_id;
paramArray[3] = CreateDataParameter("@EST_NAME", SqlDbType.NVarChar, 200);
paramArray[3].Value = est_name;
paramArray[4] = CreateDataParameter("@HEADER_COLOR", SqlDbType.NVarChar, 20);
paramArray[4].Value = header_color;
paramArray[5] = CreateDataParameter("@GRADE_CONFIRM_YN", SqlDbType.NChar);
paramArray[5].Value = grade_confirm_yn;
paramArray[6] = CreateDataParameter("@BIAS_YN", SqlDbType.NChar);
paramArray[6].Value = bias_yn;
paramArray[7] = CreateDataParameter("@BIAS_DEPT_USE_YN", SqlDbType.NChar);
paramArray[7].Value = bias_dept_use_yn;
paramArray[8] = CreateDataParameter("@TGT_OPINION_YN", SqlDbType.NChar);
paramArray[8].Value = tgt_opinion_yn;
paramArray[9] = CreateDataParameter("@FEEDBACK_YN", SqlDbType.NChar);
paramArray[9].Value = feedback_yn;
paramArray[10] = CreateDataParameter("@ESTTERM_SUB", SqlDbType.Int);
paramArray[10].Value = estterm_sub;
paramArray[11] = CreateDataParameter("@ESTTERM_STEP", SqlDbType.Int);
paramArray[11].Value = estterm_step;
paramArray[12] = CreateDataParameter("@FIXED_WEIGHT_USE_YN", SqlDbType.NChar);
paramArray[12].Value = fixed_weight_use_yn;
paramArray[13] = CreateDataParameter("@FIXED_WEIGHT", SqlDbType.Decimal);
paramArray[13].Value = fixed_weight;
paramArray[14] = CreateDataParameter("@POINT_CTRL_STEP", SqlDbType.Int);
paramArray[14].Value = point_ctrl_step;
paramArray[15] = CreateDataParameter("@GRADE_CTRL_STEP", SqlDbType.Int);
paramArray[15].Value = grade_ctrl_step;
paramArray[16] = CreateDataParameter("@OWNER_TYPE", SqlDbType.NChar);
paramArray[16].Value = owner_type;
paramArray[17] = CreateDataParameter("@EST_STYLE_ID", SqlDbType.NVarChar);
paramArray[17].Value = est_style_id;
paramArray[18] = CreateDataParameter("@LINK_EST_ID", SqlDbType.NVarChar);
paramArray[18].Value = link_est_id;
paramArray[19] = CreateDataParameter("@WEIGHT_TYPE", SqlDbType.NVarChar);
paramArray[19].Value = weight_type;
paramArray[20] = CreateDataParameter("@SCALE_TYPE", SqlDbType.NVarChar);
paramArray[20].Value = scale_type;
paramArray[21] = CreateDataParameter("@STATUS_STYLE_ID", SqlDbType.NVarChar);
paramArray[21].Value = status_style_id;
paramArray[22] = CreateDataParameter("@Q_STYLE_ID", SqlDbType.NVarChar);
paramArray[22].Value = q_style_id;
paramArray[23] = CreateDataParameter("@Q_ITEM_DESC_USE_YN", SqlDbType.NChar);
paramArray[23].Value = q_item_desc_use_yn;
paramArray[24] = CreateDataParameter("@Q_TGT_POS_BIZ_USE_YN", SqlDbType.NChar);
paramArray[24].Value = q_tgt_pos_biz_use_yn;
paramArray[25] = CreateDataParameter("@ALL_STEP_VISIBLE_YN", SqlDbType.NChar);
paramArray[25].Value = all_step_visible_yn;
paramArray[26] = CreateDataParameter("@EMP_COM_DEPT_YN", SqlDbType.NChar);
paramArray[26].Value = emp_com_dept_yn;
paramArray[27] = CreateDataParameter("@BIAS_TYPE_ID", SqlDbType.NVarChar);
paramArray[27].Value = bias_type_id;
paramArray[28] = CreateDataParameter("@VISIBLE_PAST_POINT_YN", SqlDbType.NChar);
paramArray[28].Value = visible_past_point_yn;
paramArray[29] = CreateDataParameter("@EST_QTT_MBO_YN", SqlDbType.NChar);
paramArray[29].Value = est_qtt_mbo_yn;
paramArray[30] = CreateDataParameter("@MBO_SCORE_ESTIMATE_YN", SqlDbType.NChar);
paramArray[30].Value = mbo_score_estimate_yn;
paramArray[31] = CreateDataParameter("@DASHBOARD_TYPE", SqlDbType.NChar);
paramArray[31].Value = dashboard_type;
paramArray[32] = CreateDataParameter("@Q_PREVIOUS_STEP_VISIBLE_YN", SqlDbType.NChar);
paramArray[32].Value = question_previous_step_yn;
paramArray[33] = CreateDataParameter("@USE_YN", SqlDbType.NChar);
paramArray[33].Value = use_yn;
paramArray[34] = CreateDataParameter("@CREATE_DATE", SqlDbType.DateTime);
paramArray[34].Value = create_date;
paramArray[35] = CreateDataParameter("@CREATE_USER", SqlDbType.Int);
paramArray[35].Value = create_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
public int Update(IDbConnection conn
, IDbTransaction trx
, int comp_id
, string est_id
, int estterm_ref_id
, int estterm_sub_id
, int estterm_step_id
, int est_dept_id
, int est_emp_id
, int tgt_dept_id
, int tgt_emp_id
, int seq
, string comment
, string est_tgt_type
, string confirm_type
, DateTime update_date
, int update_user)
{
string query = @"UPDATE EST_QUESTION_COMMENT
SET COMMENT = @COMMENT
,EST_TGT_TYPE = @EST_TGT_TYPE
,CONFIRM_TYPE = @CONFIRM_TYPE
,UPDATE_DATE = @UPDATE_DATE
,UPDATE_USER = @UPDATE_USER
WHERE COMP_ID = @COMP_ID
AND EST_ID = @EST_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND ESTTERM_SUB_ID = @ESTTERM_SUB_ID
AND ESTTERM_STEP_ID = @ESTTERM_STEP_ID
AND EST_DEPT_ID = @EST_DEPT_ID
AND EST_EMP_ID = @EST_EMP_ID
AND TGT_DEPT_ID = @TGT_DEPT_ID
AND TGT_EMP_ID = @TGT_EMP_ID
AND SEQ = @SEQ" ;
IDbDataParameter[] paramArray = CreateDataParameters(15);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[4] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[4].Value = estterm_step_id;
paramArray[5] = CreateDataParameter("@EST_DEPT_ID", SqlDbType.Int);
paramArray[5].Value = est_dept_id;
paramArray[6] = CreateDataParameter("@EST_EMP_ID", SqlDbType.Int);
paramArray[6].Value = est_emp_id;
paramArray[7] = CreateDataParameter("@TGT_DEPT_ID", SqlDbType.Int);
paramArray[7].Value = tgt_dept_id;
paramArray[8] = CreateDataParameter("@TGT_EMP_ID", SqlDbType.Int);
paramArray[8].Value = tgt_emp_id;
paramArray[9] = CreateDataParameter("@SEQ", SqlDbType.Int);
paramArray[9].Value = seq;
paramArray[10] = CreateDataParameter("@COMMENT", SqlDbType.NVarChar, 4000);
paramArray[10].Value = comment;
paramArray[11] = CreateDataParameter("@EST_TGT_TYPE", SqlDbType.NVarChar, 6);
paramArray[11].Value = est_tgt_type;
paramArray[12] = CreateDataParameter("@CONFIRM_TYPE", SqlDbType.NVarChar, 6);
paramArray[12].Value = confirm_type;
paramArray[13] = CreateDataParameter("@UPDATE_DATE", SqlDbType.DateTime);
paramArray[13].Value = update_date;
paramArray[14] = CreateDataParameter("@UPDATE_USER", SqlDbType.Int);
paramArray[14].Value = update_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
protected internal bool AddPDTAndAHPStgEstDeptData_Dac(IDbConnection conn
, IDbTransaction trx
, int ver_id
, int estterm_ref_id
, int est_dept_ref_id
, int stg_ref_id
, string check_yn
, string check_note
, DateTime create_date
, int create_user)
{
string query = @"
/*
IF EXISTS (SELECT * FROM BSC_PDT_AHP_STG_EST_DEPT_DATA
WHERE VER_ID = @VER_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND STG_REF_ID = @STG_REF_ID)
BEGIN
UPDATE BSC_PDT_AHP_STG_EST_DEPT_DATA
SET CHECK_YN = @CHECK_YN
,CHECK_NOTE = @CHECK_NOTE
,UPDATE_DATE = GETDATE()
,UPDATE_USER = @CREATE_USER
WHERE VER_ID = @VER_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND STG_REF_ID = @STG_REF_ID
END
ELSE
BEGIN
*/
INSERT INTO BSC_PDT_AHP_STG_EST_DEPT_DATA(VER_ID
,ESTTERM_REF_ID
,EST_DEPT_REF_ID
,STG_REF_ID
,CHECK_YN
,CHECK_NOTE
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
)
VALUES (@VER_ID
,@ESTTERM_REF_ID
,@EST_DEPT_REF_ID
,@STG_REF_ID
,@CHECK_YN
,@CHECK_NOTE
,@CREATE_DATE
,@CREATE_USER
,@CREATE_DATE
,@CREATE_USER
)
/*END*/
";
IDbDataParameter[] paramArray = CreateDataParameters(8);
paramArray[0] = CreateDataParameter("@VER_ID", SqlDbType.Int);
paramArray[0].Value = ver_id;
paramArray[1] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = estterm_ref_id;
paramArray[2] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[2].Value = est_dept_ref_id;
paramArray[3] = CreateDataParameter("@STG_REF_ID", SqlDbType.Int);
paramArray[3].Value = stg_ref_id;
paramArray[4] = CreateDataParameter("@CHECK_YN", SqlDbType.Char);
paramArray[4].Value = check_yn;
paramArray[5] = CreateDataParameter("@CHECK_NOTE", SqlDbType.VarChar);
paramArray[5].Value = check_note;
paramArray[6] = CreateDataParameter("@CREATE_DATE", SqlDbType.DateTime);
paramArray[6].Value = create_date;
paramArray[7] = CreateDataParameter("@CREATE_USER", SqlDbType.Int);
paramArray[7].Value = create_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return((affectedRow > 0) ? true : false);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 평가기간 수정
/// </summary>
/// <param name="iestterm_ref_id">평가기간 ID</param>
/// <param name="iestterm_name">기간명</param>
/// <param name="iest_startdate">시작일자</param>
/// <param name="iest_compdate">종료일자</param>
/// <param name="imonthly_close_day">월마감일</param>
/// <param name="ipre_close_day">예비마감일</param>
/// <param name="iyearly_close_yn">년평가종료여부</param>
/// <param name="iscore_valuation_type">점수평가방식</param>
/// <param name="iest_desc">평가기타사항</param>
/// <param name="iest_status">평가상태</param>
/// <param name="iclose_rate_complete_yn">월평가평가 완료마감가능여부</param>
/// <param name="itxr_user">처리자</param>
/// <returns>처리건수</returns>
public string UpdateData(int iestterm_ref_id
, string iestterm_name
, DateTime iest_startdate
, DateTime iest_compdate
, int imonthly_close_day
, int ipre_close_day
, int ikpi_qlt_close_day
, string iscore_valuation_type
, string iest_desc
, bool iest_status
, bool iclose_rate_complete_yn
, string iexternal_score_use_yn
, string iexternal_score_type
, int itxr_user)
{
IDbDataParameter[] paramArray = CreateDataParameters(17);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.VarChar);
paramArray[0].Value = "U";
paramArray[1] = CreateDataParameter("@iESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = iestterm_ref_id;
paramArray[2] = CreateDataParameter("@iESTTERM_NAME", SqlDbType.VarChar);
paramArray[2].Value = iestterm_name;
paramArray[3] = CreateDataParameter("@iEST_STARTDATE", SqlDbType.DateTime);
paramArray[3].Value = iest_startdate;
paramArray[4] = CreateDataParameter("@iEST_COMPDATE", SqlDbType.DateTime);
paramArray[4].Value = iest_compdate;
paramArray[5] = CreateDataParameter("@iMONTHLY_CLOSE_DAY", SqlDbType.Int);
paramArray[5].Value = imonthly_close_day;
paramArray[6] = CreateDataParameter("@iPRE_CLOSE_DAY", SqlDbType.Int);
paramArray[6].Value = ipre_close_day;
paramArray[7] = CreateDataParameter("@iKPI_QLT_CLOSE_DAY", SqlDbType.Int);
paramArray[7].Value = ikpi_qlt_close_day;
paramArray[8] = CreateDataParameter("@iSCORE_VALUATION_TYPE", SqlDbType.VarChar);
paramArray[8].Value = iscore_valuation_type;
paramArray[9] = CreateDataParameter("@iEST_DESC", SqlDbType.VarChar);
paramArray[9].Value = iest_desc;
paramArray[10] = CreateDataParameter("@iEST_STATUS", SqlDbType.Int);
paramArray[10].Value = (iest_status) ? 1 : 0;
paramArray[11] = CreateDataParameter("@iCLOSE_RATE_COMPLETE_YN", SqlDbType.Int);
paramArray[11].Value = (iclose_rate_complete_yn) ? 1 : 0;
paramArray[12] = CreateDataParameter("@iEXTERNAL_SCORE_USE_YN", SqlDbType.Char);
paramArray[12].Value = iexternal_score_use_yn;
paramArray[13] = CreateDataParameter("@iEXTERNAL_SCORE_TYPE", SqlDbType.Char);
paramArray[13].Value = iexternal_score_type;
paramArray[14] = CreateDataParameter("@iTXR_USER", SqlDbType.Int);
paramArray[14].Value = itxr_user;
paramArray[15] = CreateDataParameter("@oRTN_MSG", SqlDbType.VarChar, 1000);
paramArray[15].Direction = ParameterDirection.Output;
paramArray[16] = CreateDataParameter("@oRTN_COMPLETE_YN", SqlDbType.VarChar, 1);
paramArray[16].Direction = ParameterDirection.Output;
IDataParameterCollection col;
int affectedRow = DbAgentObj.ExecuteNonQuery(GetQueryStringByDb("usp_EST_TERM_INFO", "PKG_EST_TERM_INFO.PROC_UPDATE"), paramArray, CommandType.StoredProcedure, out col);
string Transaction_Result = GetOutputParameterValueBySP(col, "@oRTN_COMPLETE_YN").ToString();
string Transaction_Message = GetOutputParameterValueBySP(col, "@oRTN_MSG").ToString();
string rtnResult = Transaction_Result + "\t" + Transaction_Message;
return(rtnResult);
}
public int Update(IDbConnection conn
, IDbTransaction trx
, string ctrl_id
, int comp_id
, int estterm_ref_id
, int estterm_sub_id
, int ctrl_emp_id
, float scope
, string point_grade_type
, string scope_unit_id
, string all_est_yn
, string all_est_dept_yn
, string confirm_emp_yn
, int ctrl_order
, DateTime update_date
, int update_user)
{
string query = @"UPDATE EST_CTRL_INFO
SET ESTTERM_REF_ID = @ESTTERM_REF_ID
,ESTTERM_SUB_ID = @ESTTERM_SUB_ID
,CTRL_EMP_ID = @CTRL_EMP_ID
,SCOPE = @SCOPE
,POINT_GRADE_TYPE = @POINT_GRADE_TYPE
,SCOPE_UNIT_ID = @SCOPE_UNIT_ID
,ALL_EST_YN = @ALL_EST_YN
,ALL_EST_DEPT_YN = @ALL_EST_DEPT_YN
,CONFIRM_EMP_YN = @CONFIRM_EMP_YN
,CTRL_ORDER = @CTRL_ORDER
,UPDATE_DATE = @UPDATE_DATE
,UPDATE_USER = @UPDATE_USER
WHERE COMP_ID = @COMP_ID
AND CTRL_ID = @CTRL_ID
AND POINT_GRADE_TYPE= @POINT_GRADE_TYPE";
IDbDataParameter[] paramArray = CreateDataParameters(14);
paramArray[0] = CreateDataParameter("@CTRL_ID", SqlDbType.NVarChar, 20);
paramArray[0].Value = ctrl_id;
paramArray[1] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[1].Value = comp_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[4] = CreateDataParameter("@CTRL_EMP_ID", SqlDbType.Int);
paramArray[4].Value = ctrl_emp_id;
paramArray[5] = CreateDataParameter("@SCOPE", SqlDbType.Float);
paramArray[5].Value = scope;
paramArray[6] = CreateDataParameter("@POINT_GRADE_TYPE", SqlDbType.NVarChar, 20);
paramArray[6].Value = point_grade_type;
paramArray[7] = CreateDataParameter("@SCOPE_UNIT_ID", SqlDbType.NVarChar, 6);
paramArray[7].Value = scope_unit_id;
paramArray[8] = CreateDataParameter("@ALL_EST_YN", SqlDbType.NChar);
paramArray[8].Value = all_est_yn;
paramArray[9] = CreateDataParameter("@ALL_EST_DEPT_YN", SqlDbType.NChar);
paramArray[9].Value = all_est_dept_yn;
paramArray[10] = CreateDataParameter("@CONFIRM_EMP_YN", SqlDbType.NChar);
paramArray[10].Value = confirm_emp_yn;
paramArray[11] = CreateDataParameter("@CTRL_ORDER", SqlDbType.Int);
paramArray[11].Value = ctrl_order;
paramArray[12] = CreateDataParameter("@UPDATE_DATE", SqlDbType.DateTime);
paramArray[12].Value = update_date;
paramArray[13] = CreateDataParameter("@UPDATE_USER", SqlDbType.Int);
paramArray[13].Value = update_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 부서의 월별 점수
/// </summary>
/// <param name="sum_type">TS:누적점수, MS:월별점수</param>
public DataTable Select_Kpi_Monthly_Score(object estterm_ref_id
, object dept_ref_id
, object sum_type)
{
string strQuery = @"
SELECT TC.ESTTERM_REF_ID
,TC.YMD
,TC.EST_DEPT_REF_ID
,TC.DEPT_NAME
,TC.SCORE
FROM (
SELECT TB.ESTTERM_REF_ID as ESTTERM_REF_ID
,TB.YMD as YMD
,TB.EST_DEPT_REF_ID as EST_DEPT_REF_ID
,TB.DEPT_NAME as DEPT_NAME
,ROUND(TB.SCORE,4) as SCORE
FROM (
SELECT TA.ESTTERM_REF_ID
,TA.YMD
,TA.EST_DEPT_REF_ID
,TA.DEPT_NAME
,CASE WHEN @SUM_TYPE = 'MS'
THEN ROUND(SUM(TA.SCORE_MS),4)
ELSE ROUND(SUM(TA.SCORE_TS),4)
END as SCORE
FROM (
SELECT KW.ESTTERM_REF_ID
,KW.YMD
,KW.EST_DEPT_REF_ID
,ED.DEPT_NAME
,KW.VIEW_REF_ID
,ISNULL(KW.WEIGHT3,0)* (CASE WHEN TS.POINTS_MS='-' OR TS.POINTS_MS IS NULL THEN 0 ELSE TO_NUMBER(TS.POINTS_MS)*0.01 END) as SCORE_MS
,ISNULL(KW.SWEIGHT3,0)*(CASE WHEN TS.POINTS_TS='-' OR TS.POINTS_TS IS NULL THEN 0 ELSE TO_NUMBER(TS.POINTS_TS)*0.01 END) as SCORE_TS
FROM BSC_KPI_WEIGHT KW
LEFT JOIN BSC_KPI_SCORE TS
ON KW.ESTTERM_REF_ID = TS.ESTTERM_REF_ID
AND KW.KPI_REF_ID = TS.KPI_REF_ID
AND KW.YMD = TS.YMD
AND KW.ESTTERM_REF_ID = @ESTTERM_REF_ID
LEFT JOIN EST_DEPT_INFO ED
ON KW.ESTTERM_REF_ID = ED.ESTTERM_REF_ID
AND KW.EST_DEPT_REF_ID = ED.EST_DEPT_REF_ID
WHERE ED.EST_DEPT_REF_ID = @DEPT_REF_ID
) TA
GROUP BY TA.ESTTERM_REF_ID
,TA.YMD
,TA.EST_DEPT_REF_ID
,TA.DEPT_NAME
) TB
GROUP BY TB.ESTTERM_REF_ID
,TB.YMD
,TB.EST_DEPT_REF_ID
,TB.DEPT_NAME
,TB.SCORE
) TC
ORDER BY TC.ESTTERM_REF_ID
,TC.EST_DEPT_REF_ID
,TC.YMD
";
IDbDataParameter[] paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = estterm_ref_id;
paramArray[1] = CreateDataParameter("@DEPT_REF_ID", SqlDbType.Int);
paramArray[1].Value = dept_ref_id;
paramArray[2] = CreateDataParameter("@SUM_TYPE", SqlDbType.NVarChar);
paramArray[2].Value = sum_type;
DataTable dt = DbAgentObj.FillDataSet(strQuery, "BSC_KPI_MONTHLY_SCORE", null, paramArray, CommandType.Text).Tables[0];
return(dt);
}
public DataSet SelectBiasGroupEmp_DB(object comp_id
, object est_id
, object estterm_ref_id
, object bias_grp_id)
{
// string strQuery = @"
//
//SELECT B.DEPT_REF_ID AS SFID
// ,B.UP_DEPT_ID AS PTID
// ,B.DEPT_NAME AS NAME
// -- ,B.DEPT_TYPE AS LVL
// ,B.DEPT_LEVEL +1 AS LVL
// ,ISNULL(D.EMP_REF_ID, 0) AS EMP_REF_ID
// ,ISNULL(D.EMP_NAME, '') AS EMP_NAME
// ,CASE WHEN ISNULL(E.BIAS_GRP_ID, 0) = @BIAS_GRP_ID THEN 'Y' ELSE 'N' END AS ISCHECK
// ,CASE WHEN F.YEARLY_CLOSE_YN = 1 THEN 'F' ELSE CASE WHEN ISNULL(E.BIAS_GRP_ID, 0) = @BIAS_GRP_ID THEN 'Y' ELSE CASE WHEN ISNULL(E.BIAS_GRP_ID, 0) = 0 THEN 'Y' ELSE 'N' END END END AS ISENABLED
//FROM COM_DEPT_INFO B
//
//LEFT OUTER JOIN REL_DEPT_EMP C
// ON C.DEPT_REF_ID = B.DEPT_REF_ID
//LEFT OUTER JOIN COM_EMP_INFO D
// ON D.EMP_REF_ID = C.EMP_REF_ID
//LEFT OUTER JOIN EST_BIAS_GRP_USER E
// ON E.COMP_ID = @COMP_ID
// AND E.EST_ID = @EST_ID
// AND E.ESTTERM_REF_ID = @ESTTERM_REF_ID
// AND E.EMP_REF_ID = D.EMP_REF_ID
//LEFT OUTER JOIN EST_TERM_INFO F
// ON F.ESTTERM_REF_ID = @ESTTERM_REF_ID
//WHERE C.REL_STATUS > CASE WHEN F.YEARLY_CLOSE_YN = 0 THEN 0 ELSE -1 END
//ORDER BY B.SORT_ORDER, B.DEPT_REF_ID, D.EMP_NAME
//";
string strQuery = @"
SELECT B.COM_DEPT_REF_ID AS SFID
,B.COM_UP_DEPT_ID AS PTID
,B.COM_DEPT_NAME AS NAME
-- ,B.DEPT_TYPE AS LVL
,D.DEPT_LEVEL +1 AS LVL
,NVL(B.EMP_REF_ID, 0) AS EMP_REF_ID
,NVL(B.EMP_NAME, '') AS EMP_NAME
,CASE WHEN NVL(E.BIAS_GRP_ID, 0) = @BIAS_GRP_ID THEN 'Y' ELSE 'N' END AS ISCHECK
,CASE WHEN F.YEARLY_CLOSE_YN = 1 THEN 'F' ELSE CASE WHEN NVL(E.BIAS_GRP_ID, 0) = @BIAS_GRP_ID THEN 'Y' ELSE CASE WHEN NVL(E.BIAS_GRP_ID, 0) = 0 THEN 'Y' ELSE 'N' END END END AS ISENABLED
FROM VIW_EMP_COMDEPT B
LEFT OUTER JOIN REL_DEPT_EMP C
ON C.DEPT_REF_ID = B.COM_DEPT_REF_ID AND C.EMP_REF_ID = B.EMP_REF_ID
LEFT OUTER JOIN VIW_COM_DEPT_INFO_BYTREE D
ON D.DEPT_REF_ID = B.COM_DEPT_REF_ID
LEFT OUTER JOIN EST_BIAS_GRP_USER E
ON E.COMP_ID = @COMP_ID
AND E.EST_ID = @EST_ID
AND E.ESTTERM_REF_ID = @ESTTERM_REF_ID
AND E.EMP_REF_ID = B.EMP_REF_ID
LEFT OUTER JOIN EST_TERM_INFO F
ON F.ESTTERM_REF_ID = @ESTTERM_REF_ID
WHERE C.REL_STATUS > CASE WHEN F.YEARLY_CLOSE_YN = 0 THEN 0 ELSE -1 END
ORDER BY D.SORT_ORD, B.EMP_NAME ";
//strQuery = string.Format(strQuery, root_dept_ref_id);
IDbDataParameter[] dtParam = CreateDataParameters(4);
dtParam[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
dtParam[0].Value = comp_id;
dtParam[1] = CreateDataParameter("@EST_ID", SqlDbType.VarChar);
dtParam[1].Value = est_id;
dtParam[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
dtParam[2].Value = estterm_ref_id;
dtParam[3] = CreateDataParameter("@BIAS_GRP_ID", SqlDbType.Int);
dtParam[3].Value = bias_grp_id;
return(DbAgentObj.FillDataSet(strQuery, "BIAS_GROUP_EMP", null, dtParam, CommandType.Text));
}
public int EditEmpInfo(
int aiEMP_REF_ID
, string asLOGINID
, string asLOGINIP
, string asEMP_NAME
, string asPOSITION_CLASS_CODE
, string asPOSITION_GRP_CODE
, string asPOSITION_RANK_CODE
, string asPOSITION_DUTY_CODE
, string asPOSITION_STAT_CODE
, string asPOSITION_KIND_CODE
, string asEMP_EMail
, string asDAILY_PHONE
, string asCELL_PHONE
, string asFAX_NUMBER
, int aiJOB_STATUS
, string asZIPCODE
, string asADDR_1
, string asADDR_2
, string asSIGN_PATH
, string asSTAMP_PATH
, int aiCREATE_TYPE
, int aiCREATE_EMP_ID
, int aiMODIFY_TYPE
, int aiMODIFY_EMP_ID
)
{
string s_query = @"
UPDATE COM_EMP_INFO
SET
EMP_CODE = @LOGINID ,
LOGINID = @LOGINID ,
EMP_NAME = @EMP_NAME ,
EMP_EMail = @EMP_EMail ,
POSITION_CLASS_CODE = @POSITION_CLASS_CODE ,
POSITION_GRP_CODE = @POSITION_GRP_CODE ,
POSITION_RANK_CODE = @POSITION_RANK_CODE ,
POSITION_DUTY_CODE = @POSITION_DUTY_CODE ,
POSITION_STAT_CODE = @POSITION_STAT_CODE ,
POSITION_KIND_CODE = @POSITION_KIND_CODE ,
DAILY_PHONE = @DAILY_PHONE ,
CELL_PHONE = @CELL_PHONE ,
FAX_NUMBER = @FAX_NUMBER ,
JOB_STATUS = @JOB_STATUS ,
ZIPCODE = @ZIPCODE ,
ADDR_1 = @ADDR_1 ,
ADDR_2 = @ADDR_2 ,
SIGN_PATH = @SIGN_PATH ,
STAMP_PATH = @STAMP_PATH ,
CREATE_TYPE = @CREATE_TYPE ,
CREATE_EMP_ID = @CREATE_EMP_ID ,
MODIFY_TYPE = @MODIFY_TYPE ,
MODIFY_DATE = GETDATE() ,
MODIFY_EMP_ID = @MODIFY_EMP_ID ,
LOGINIP = @LOGINIP
WHERE EMP_REF_ID = @EMP_REF_ID
";
string o_query = @"
UPDATE COM_EMP_INFO
SET
EMP_CODE = @LOGINID ,
LOGINID = @LOGINID ,
EMP_NAME = @EMP_NAME ,
EMP_EMail = @EMP_EMail ,
POSITION_CLASS_CODE = @POSITION_CLASS_CODE ,
POSITION_GRP_CODE = @POSITION_GRP_CODE ,
POSITION_RANK_CODE = @POSITION_RANK_CODE ,
POSITION_DUTY_CODE = @POSITION_DUTY_CODE ,
POSITION_STAT_CODE = @POSITION_STAT_CODE ,
POSITION_KIND_CODE = @POSITION_KIND_CODE ,
DAILY_PHONE = @DAILY_PHONE ,
CELL_PHONE = @CELL_PHONE ,
FAX_NUMBER = @FAX_NUMBER ,
JOB_STATUS = @JOB_STATUS ,
ZIPCODE = @ZIPCODE ,
ADDR_1 = @ADDR_1 ,
ADDR_2 = @ADDR_2 ,
SIGN_PATH = @SIGN_PATH ,
STAMP_PATH = @STAMP_PATH ,
CREATE_TYPE = @CREATE_TYPE ,
CREATE_EMP_ID = @CREATE_EMP_ID ,
MODIFY_TYPE = @MODIFY_TYPE ,
MODIFY_DATE = SYSDATE ,
MODIFY_EMP_ID = @MODIFY_EMP_ID ,
LOGINIP = @LOGINIP
WHERE EMP_REF_ID = @EMP_REF_ID
";
IDbDataParameter[] paramArray = CreateDataParameters(24);
paramArray[0] = CreateDataParameter("@LOGINID", SqlDbType.VarChar);
paramArray[1] = CreateDataParameter("@EMP_NAME", SqlDbType.VarChar);
paramArray[2] = CreateDataParameter("@POSITION_CLASS_CODE", SqlDbType.VarChar);
paramArray[3] = CreateDataParameter("@POSITION_GRP_CODE", SqlDbType.VarChar);
paramArray[4] = CreateDataParameter("@POSITION_RANK_CODE", SqlDbType.VarChar);
paramArray[5] = CreateDataParameter("@POSITION_DUTY_CODE", SqlDbType.VarChar);
paramArray[6] = CreateDataParameter("@POSITION_STAT_CODE", SqlDbType.VarChar);
paramArray[7] = CreateDataParameter("@POSITION_KIND_CODE", SqlDbType.VarChar);
paramArray[8] = CreateDataParameter("@EMP_EMail", SqlDbType.VarChar);
paramArray[9] = CreateDataParameter("@DAILY_PHONE", SqlDbType.VarChar);
paramArray[10] = CreateDataParameter("@CELL_PHONE", SqlDbType.VarChar);
paramArray[11] = CreateDataParameter("@FAX_NUMBER", SqlDbType.VarChar);
paramArray[12] = CreateDataParameter("@JOB_STATUS", SqlDbType.Int);
paramArray[13] = CreateDataParameter("@ZIPCODE", SqlDbType.VarChar);
paramArray[14] = CreateDataParameter("@ADDR_1", SqlDbType.VarChar);
paramArray[15] = CreateDataParameter("@ADDR_2", SqlDbType.VarChar);
paramArray[16] = CreateDataParameter("@SIGN_PATH", SqlDbType.VarChar);
paramArray[17] = CreateDataParameter("@STAMP_PATH", SqlDbType.VarChar);
paramArray[18] = CreateDataParameter("@CREATE_TYPE", SqlDbType.Int);
paramArray[19] = CreateDataParameter("@CREATE_EMP_ID", SqlDbType.Int);
paramArray[20] = CreateDataParameter("@MODIFY_TYPE", SqlDbType.Int);
paramArray[21] = CreateDataParameter("@MODIFY_EMP_ID", SqlDbType.Int);
paramArray[22] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int);
paramArray[23] = CreateDataParameter("@LOGINIP", SqlDbType.VarChar);
paramArray[0].Value = asLOGINID;
paramArray[1].Value = asEMP_NAME;
paramArray[2].Value = asPOSITION_CLASS_CODE;
paramArray[3].Value = asPOSITION_GRP_CODE;
paramArray[4].Value = asPOSITION_RANK_CODE;
paramArray[5].Value = asPOSITION_DUTY_CODE;
paramArray[6].Value = asPOSITION_STAT_CODE;
paramArray[7].Value = asPOSITION_KIND_CODE;
paramArray[8].Value = asEMP_EMail;
paramArray[9].Value = asDAILY_PHONE;
paramArray[10].Value = asCELL_PHONE;
paramArray[11].Value = asFAX_NUMBER;
paramArray[12].Value = aiJOB_STATUS;
paramArray[13].Value = asZIPCODE;
paramArray[14].Value = asADDR_1;
paramArray[15].Value = asADDR_2;
paramArray[16].Value = asSIGN_PATH;
paramArray[17].Value = asSTAMP_PATH;
paramArray[18].Value = aiCREATE_TYPE;
paramArray[19].Value = aiCREATE_EMP_ID;
paramArray[20].Value = aiMODIFY_TYPE;
paramArray[21].Value = aiMODIFY_EMP_ID;
paramArray[22].Value = aiEMP_REF_ID;
paramArray[23].Value = asLOGINIP;
return(DbAgentObj.ExecuteNonQuery(GetQueryStringByDb(s_query, o_query), paramArray));
}
public int Insert(IDbConnection conn
, IDbTransaction trx
, object comp_id
, object estterm_ref_id
, object dept_ref_id
, object est_id
, object seq
, object pos_id
, object pos_value
, object weight
, object create_date
, object create_user)
{
string query = @"INSERT INTO EST_DEPT_POS_DETAIL( COMP_ID
,ESTTERM_REF_ID
,DEPT_REF_ID
,EST_ID
,SEQ
,POS_ID
,POS_VALUE
,WEIGHT
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
)
VALUES (@COMP_ID
,@ESTTERM_REF_ID
,@DEPT_REF_ID
,@EST_ID
,@SEQ
,@POS_ID
,@POS_VALUE
,@WEIGHT
,@CREATE_DATE
,@CREATE_USER
,NULL
,NULL
)" ;
IDbDataParameter[] paramArray = CreateDataParameters(10);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = estterm_ref_id;
paramArray[2] = CreateDataParameter("@DEPT_REF_ID", SqlDbType.Int);
paramArray[2].Value = dept_ref_id;
paramArray[3] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[3].Value = est_id;
paramArray[4] = CreateDataParameter("@SEQ", SqlDbType.Int);
paramArray[4].Value = seq;
paramArray[5] = CreateDataParameter("@POS_ID", SqlDbType.NVarChar, 6);
paramArray[5].Value = pos_id;
paramArray[6] = CreateDataParameter("@POS_VALUE", SqlDbType.NVarChar, 6);
paramArray[6].Value = pos_value;
paramArray[7] = CreateDataParameter("@WEIGHT", SqlDbType.Float);
paramArray[7].Value = weight;
paramArray[8] = CreateDataParameter("@CREATE_DATE", SqlDbType.DateTime);
paramArray[8].Value = create_date;
paramArray[9] = CreateDataParameter("@CREATE_USER", SqlDbType.Int);
paramArray[9].Value = create_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
public int Update(IDbConnection conn
, IDbTransaction trx
, object comp_id
, object estterm_ref_id
, object dept_ref_id
, object est_id
, object seq
, object seq_new
, object pos_id
, object pos_value
, object weight
, object update_date
, object update_user)
{
string query = @"UPDATE EST_DEPT_POS_DETAIL
SET SEQ = CASE WHEN @SEQ_NEW IS NULL THEN SEQ ELSE @SEQ_NEW END
,POS_ID = CASE WHEN @POS_ID IS NULL THEN POS_ID ELSE @POS_ID END
,POS_VALUE = CASE WHEN @POS_VALUE IS NULL THEN POS_VALUE ELSE @POS_VALUE END
,WEIGHT = CASE WHEN @WEIGHT IS NULL THEN WEIGHT ELSE @WEIGHT END
,UPDATE_DATE = CASE WHEN @UPDATE_DATE IS NULL THEN UPDATE_DATE ELSE @UPDATE_DATE END
,UPDATE_USER = CASE WHEN @UPDATE_USER IS NULL THEN UPDATE_USER ELSE @UPDATE_USER END
WHERE (COMP_ID = @COMP_ID OR @COMP_ID = 0)
AND (ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0)
AND (DEPT_REF_ID = @DEPT_REF_ID OR @DEPT_REF_ID = 0)
AND (EST_ID = @EST_ID OR @EST_ID ='' )
AND (SEQ = @SEQ OR @SEQ = 0)" ;
IDbDataParameter[] paramArray = CreateDataParameters(11);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = estterm_ref_id;
paramArray[2] = CreateDataParameter("@DEPT_REF_ID", SqlDbType.Int);
paramArray[2].Value = dept_ref_id;
paramArray[3] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[3].Value = est_id;
paramArray[4] = CreateDataParameter("@SEQ", SqlDbType.Int);
paramArray[4].Value = seq;
paramArray[5] = CreateDataParameter("@SEQ_NEW", SqlDbType.Int);
paramArray[5].Value = seq_new;
paramArray[6] = CreateDataParameter("@POS_ID", SqlDbType.NVarChar, 6);
paramArray[6].Value = pos_id;
paramArray[7] = CreateDataParameter("@POS_VALUE", SqlDbType.NVarChar, 6);
paramArray[7].Value = pos_value;
paramArray[8] = CreateDataParameter("@WEIGHT", SqlDbType.Float);
paramArray[8].Value = weight;
paramArray[9] = CreateDataParameter("@UPDATE_DATE", SqlDbType.DateTime);
paramArray[9].Value = update_date;
paramArray[10] = CreateDataParameter("@UPDATE_USER", SqlDbType.Int);
paramArray[10].Value = update_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// GetSearchData
/// : 결재승인 정보 추출
/// </summary>
/// <param name="asEmpID"></param>
/// <param name="bIsRep">상신검색(true) / 결재검색(false)</param>
/// <returns></returns>
public DataSet GetSearchData(string asEmpID, string asAppGubun, string asEstTerm, string asAppStatus, bool bIsRep)
{
IDbDataParameter[] paramArray = null;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT \n");
sbQuery.Append(" A.APP_CODE V_APP_CODE , -- 문서타입 HIDDEN \n");
sbQuery.Append(" E.BIZ_TYPE_NAME V_APP_TYPE_NAME , -- 문서타입명 \n");
sbQuery.Append(" A.APP_REF_ID V_APP_REF_ID , -- 문서번호 \n");
sbQuery.Append(" G.KPI_REF_ID V_KPI_REF_ID , -- KPI_REF_ID \n");
sbQuery.Append(" G.KPI_CODE V_KPI_CODE , -- KPI 코드 (기간동안 UNIQUE한 KEY) \n");
sbQuery.Append(" G.KPI_NAME V_KPI_NAME , -- KPI NAME \n");
sbQuery.Append(" CASE A.APP_CODE WHEN 'KPIRST' THEN \n");
sbQuery.Append(" ( \n");
sbQuery.Append(" SELECT ISNULL(MAX(RESULT), 0) \n");
sbQuery.Append(" FROM KPI_RESULT \n");
sbQuery.Append(" WHERE KPI_REF_ID = A.EVENT_ID \n");
sbQuery.Append(" AND TMCODE = A.EVENT_ADD_ID \n");
sbQuery.Append(" ) \n");
sbQuery.Append(" ELSE NULL \n");
sbQuery.Append(" END V_KPI_RESULT , -- KPI 실적 \n");
sbQuery.Append(" A.EVENT_ID V_EVENT_ID , -- KPI_REF_ID HIDDEN \n");
sbQuery.Append(" A.EVENT_ADD_ID V_EVENT_ADD_ID , -- KPI 관련 정보 HIDDEN \n");
sbQuery.Append(" B.APP_STEP V_APP_STEP , -- 현재결재단계 HIDDEN \n");
sbQuery.Append(" F.MAX_APP_STEP V_MAX_APP_STEP , -- 전체결재단계 HIDDEN \n");
sbQuery.Append(" A.REP_TITLE V_REP_TITLE , -- 상신 제목 \n");
sbQuery.Append(" A.REP_EMP_ID V_REP_EMP_ID , -- 상신자 아이디 HIDDEN \n");
sbQuery.Append(" C.EMP_NAME V_REP_EMP_NAME , -- 상신자 명 \n");
sbQuery.Append(" B.APP_EMP_ID V_APP_EMP_ID , -- 결재자 아이디 HIDDEN \n");
sbQuery.Append(" D.EMP_NAME V_APP_EMP_NAME , -- 결재자 명 \n");
sbQuery.Append(" B.APP_REMARK V_REMARK , -- 취소사유 \n");
sbQuery.Append(" CASE B.APP_STATUS WHEN 'P' THEN '대기' \n");
sbQuery.Append(" WHEN 'E' THEN '승인' \n");
sbQuery.Append(" WHEN 'C' THEN '취소' \n");
sbQuery.Append(" END V_CUR_APP_STATUS , -- 현재결재상태 \n");
sbQuery.Append(" CASE A.APP_STATUS WHEN 'P' THEN '대기' \n");
sbQuery.Append(" WHEN 'E' THEN '승인' \n");
sbQuery.Append(" WHEN 'C' THEN '취소' \n");
sbQuery.Append(" END V_ALL_APP_STATUS , -- 전체결재상태 \n");
sbQuery.Append(" A.APPTERM_REF_ID V_TERM_REF_ID , -- 평가기간 HIDDEN \n");
sbQuery.Append(" B.APP_STATUS V_CUR_APP_STATUS_CD , -- 현재결재상태 코드 HIDDEN \n");
sbQuery.Append(" A.APP_STATUS V_ALL_APP_STATUS_CD , -- 전체결재상태 코드 HIDDEN \n");
sbQuery.Append(" A.REP_DATE V_REP_DATE , -- 상신일 \n");
sbQuery.Append(" A.APP_COMPDATE V_APP_COMPDATE -- 결재일 \n");
sbQuery.Append(" FROM COM_APPROVAL_INFO A, -- 결재정보 \n");
sbQuery.Append(" COM_APPROVAL_PRC B, -- 결재 HISTORY 정보 \n");
sbQuery.Append(" COM_EMP_INFO C, -- 상신자 정보 \n");
sbQuery.Append(" COM_EMP_INFO D, -- 결재자 정보 \n");
sbQuery.Append(" COM_BIZ_INFO E, -- 결재타입 정보 \n");
sbQuery.Append(" ( \n");
sbQuery.Append(" SELECT BIZ_TYPE_CODE, \n");
sbQuery.Append(" MAX(APP_STEP) MAX_APP_STEP \n");
sbQuery.Append(" FROM COM_APPROVAL_LINE \n");
sbQuery.Append(" GROUP BY \n");
sbQuery.Append(" BIZ_TYPE_CODE \n");
sbQuery.Append(" ) F, -- 결재선 정보 \n");
sbQuery.Append(" KPI_INFO G -- KPI 정의 \n");
sbQuery.Append(" WHERE 1=1 \n");
sbQuery.Append(" AND A.APP_REF_ID = B.APP_REF_ID \n");
sbQuery.Append(" AND A.CUR_APP_STEP = B.APP_STEP \n");
sbQuery.Append(" AND A.REP_EMP_ID = C.EMP_REF_ID \n");
sbQuery.Append(" AND B.APP_EMP_ID = D.EMP_REF_ID \n");
sbQuery.Append(" AND A.APP_CODE = E.BIZ_TYPE_CODE \n");
sbQuery.Append(" AND E.BIZ_TYPE_CODE = F.BIZ_TYPE_CODE \n");
sbQuery.Append(" AND A.EVENT_ID = G.KPI_REF_ID \n");
if (bIsRep)
{
sbQuery.Append(" AND a.REP_EMP_ID = @APP_EMP_ID \n");
}
else
{
sbQuery.Append(" AND B.APP_EMP_ID = @APP_EMP_ID \n");
}
if (asAppGubun != "")
{
sbQuery.Append(" AND E.BIZ_TYPE_CODE = @BIZ_TYPE_CODE \n");
}
if (asEstTerm != "")
{
sbQuery.Append(" AND A.APPTERM_REF_ID = CONVERT(INT, @APPTERM_REF_ID) \n");
}
if (asAppStatus != "")
{
sbQuery.Append(" AND A.APP_STATUS = @APP_STATUS \n");
}
paramArray = CreateDataParameters(4);
paramArray[0] = CreateDataParameter("@APP_EMP_ID", SqlDbType.VarChar);
paramArray[1] = CreateDataParameter("@BIZ_TYPE_CODE", SqlDbType.VarChar);
paramArray[2] = CreateDataParameter("@APPTERM_REF_ID", SqlDbType.VarChar);
paramArray[3] = CreateDataParameter("@APP_STATUS", SqlDbType.VarChar);
paramArray[0].Value = asEmpID;
paramArray[1].Value = asAppGubun;
paramArray[2].Value = asEstTerm;
paramArray[3].Value = asAppStatus;
return(DbAgentObj.Fill(sbQuery.ToString(), paramArray));
}
/// <summary>
/// SetInfoRep
/// : 결재정보 테이블 상신 처리
/// </summary>
/// <param name="asKpiRefID"></param>
/// <param name="asAppRefID"></param>
/// <param name="asEventAddID"></param>
/// <param name="asAppCode"></param>
/// <param name="asRepEmpID"></param>
/// <param name="asTermRefID"></param>
/// <returns></returns>
public int SetInfoRep(
string asKpiRefID
, string asAppRefID
, string asEventAddID
, string asAppCode
, string asRepEmpID
, string asTermRefID
)
{
IDbDataParameter[] paramArray = null;
StringBuilder sbQuery = new StringBuilder();
string sRepTitle = "";
string sEventAddID = "";
if (asAppCode == "KPIINF")
{
sRepTitle = "KPI 정의서 결재를 바랍니다.";
sEventAddID = "";
}
else if (asAppCode == "KPIRST")
{
sRepTitle = asEventAddID + "월 KPI 실적 결재를 바랍니다.";
sEventAddID = asEventAddID;
}
else
{
return(0);
}
sbQuery.Append("INSERT INTO COM_APPROVAL_INFO \n");
sbQuery.Append(" ( \n");
sbQuery.Append(" APP_REF_ID , EVENT_ID , EVENT_ADD_ID , \n");
sbQuery.Append(" APP_CODE , REP_EMP_ID , APP_STATUS , \n");
sbQuery.Append(" CUR_APP_STEP , REP_TITLE , REP_DESC , \n");
sbQuery.Append(" REP_DATE , APPTERM_REF_ID \n");
sbQuery.Append(" ) \n");
sbQuery.Append("SELECT @APP_REF_ID , KPI_REF_ID , @EVENT_ADD_ID , \n");
sbQuery.Append(" @APP_CODE , CONVERT(INT, @REP_EMP_ID) , 'P' , \n");
sbQuery.Append(" 1 , @REP_TITLE , KPI_CODE + ' : ' + KPI_NAME , \n");
sbQuery.Append(" GETDATE(), CONVERT(INT, @APPTERM_REF_ID) \n");
sbQuery.Append(" FROM KPI_INFO \n");
sbQuery.Append(" WHERE KPI_REF_ID = @KPI_REF_ID \n");
paramArray = CreateDataParameters(7);
paramArray[0] = CreateDataParameter("@APP_REF_ID", SqlDbType.VarChar);
paramArray[1] = CreateDataParameter("@EVENT_ADD_ID", SqlDbType.VarChar);
paramArray[2] = CreateDataParameter("@APP_CODE", SqlDbType.VarChar);
paramArray[3] = CreateDataParameter("@REP_EMP_ID", SqlDbType.VarChar);
paramArray[4] = CreateDataParameter("@REP_TITLE", SqlDbType.VarChar);
paramArray[5] = CreateDataParameter("@APPTERM_REF_ID", SqlDbType.VarChar);
paramArray[6] = CreateDataParameter("@KPI_REF_ID", SqlDbType.VarChar);
paramArray[0].Value = asAppRefID;
paramArray[1].Value = sEventAddID;
paramArray[2].Value = asAppCode;
paramArray[3].Value = asRepEmpID;
paramArray[4].Value = sRepTitle;
paramArray[5].Value = asTermRefID;
paramArray[6].Value = asKpiRefID;
return(DbAgentObj.ExecuteNonQuery(sbQuery.ToString(), paramArray));
}
public int Insert_Est_Refusal(IDbConnection conn
, IDbTransaction trx
, object comp_id
, object est_id
, object estterm_ref_id
, object estterm_sub_id
, object estterm_step_id
, object tgt_dept_id
, object tgt_emp_id
, object refusal_comment
, object grade_id
, object create_user_ref_id)
{
string query = @"
INSERT INTO EST_REFUSAL
(
COMP_ID
, EST_ID
, ESTTERM_REF_ID
, ESTTERM_SUB_ID
, ESTTERM_STEP_ID
, TGT_DEPT_ID
, TGT_EMP_ID
, REFUSAL_COMMENT
, REFUSAL_DATE
, GRADE_ID
, CREATE_USER
, CREATE_DATE
)
VALUES
(
@COMP_ID
, @EST_ID
, @ESTTERM_REF_ID
, @ESTTERM_SUB_ID
, @ESTTERM_STEP_ID
, @TGT_DEPT_ID
, @TGT_EMP_ID
, @REFUSAL_COMMENT
, GETDATE()
, @GRADE_ID
, @CREATE_USER
, GETDATE()
)
";
IDbDataParameter[] paramArray = CreateDataParameters(10);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[4] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[4].Value = estterm_step_id;
paramArray[5] = CreateDataParameter("@TGT_DEPT_ID", SqlDbType.Int);
paramArray[5].Value = tgt_dept_id;
paramArray[6] = CreateDataParameter("@TGT_EMP_ID", SqlDbType.Int);
paramArray[6].Value = tgt_emp_id;
paramArray[7] = CreateDataParameter("@REFUSAL_COMMENT", SqlDbType.NVarChar);
paramArray[7].Value = refusal_comment;
paramArray[8] = CreateDataParameter("@GRADE_ID", SqlDbType.NVarChar);
paramArray[8].Value = grade_id;
paramArray[9] = CreateDataParameter("@CREATE_USER", SqlDbType.Int);
paramArray[9].Value = create_user_ref_id;
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
public int Insert(IDbConnection conn
, IDbTransaction trx
, int comp_id
, string est_id
, int estterm_ref_id
, int estterm_sub_id
, int estterm_step_id
, int est_dept_id
, int est_emp_id
, int prj_ref_id
, string status_id
, DateTime create_date
, int create_user)
{
string query = @"INSERT INTO PRJ_EMP_EST_PRJ_MAP( COMP_ID
,EST_ID
,ESTTERM_REF_ID
,ESTTERM_SUB_ID
,ESTTERM_STEP_ID
,EST_DEPT_ID
,EST_EMP_ID
,PRJ_REF_ID
,STATUS_ID
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
)
VALUES (@COMP_ID
,@EST_ID
,@ESTTERM_REF_ID
,@ESTTERM_SUB_ID
,@ESTTERM_STEP_ID
,@EST_DEPT_ID
,@EST_EMP_ID
,@PRJ_REF_ID
,@STATUS_ID
,@CREATE_DATE
,@CREATE_USER
,NULL
,NULL
)" ;
IDbDataParameter[] paramArray = CreateDataParameters(11);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[4] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[4].Value = estterm_step_id;
paramArray[5] = CreateDataParameter("@EST_DEPT_ID", SqlDbType.Int);
paramArray[5].Value = est_dept_id;
paramArray[6] = CreateDataParameter("@EST_EMP_ID", SqlDbType.Int);
paramArray[6].Value = est_emp_id;
paramArray[7] = CreateDataParameter("@PRJ_REF_ID", SqlDbType.Int);
paramArray[7].Value = prj_ref_id;
paramArray[8] = CreateDataParameter("@STATUS_ID", SqlDbType.NVarChar, 12);
paramArray[8].Value = status_id;
paramArray[9] = CreateDataParameter("@CREATE_DATE", SqlDbType.DateTime);
paramArray[9].Value = create_date;
paramArray[10] = CreateDataParameter("@CREATE_USER", SqlDbType.Int);
paramArray[10].Value = create_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
public int AddEmpInfo(
int aiEMP_REF_ID
, string asLOGINID
, string asLOGINIP
, string asPASSWD
, string asEMP_NAME
, string asPOSITION_CLASS_CODE
, string asPOSITION_GRP_CODE
, string asPOSITION_RANK_CODE
, string asPOSITION_DUTY_CODE
, string asPOSITION_STAT_CODE
, string asPOSITION_KIND_CODE
, string asEMP_EMail
, string asDAILY_PHONE
, string asCELL_PHONE
, string asFAX_NUMBER
, int aiJOB_STATUS
, string asZIPCODE
, string asADDR_1
, string asADDR_2
, string asSIGN_PATH
, string asSTAMP_PATH
, int aiCREATE_TYPE
, int aiCREATE_EMP_ID
, int aiMODIFY_TYPE
, int aiMODIFY_EMP_ID
)
{
string s_query = @"
INSERT INTO COM_EMP_INFO
(
EMP_REF_ID , EMP_CODE , LOGINID ,
PASSWD , EMP_NAME , EMP_EMail ,
POSITION_CLASS_CODE , POSITION_GRP_CODE , POSITION_RANK_CODE ,
POSITION_DUTY_CODE , POSITION_STAT_CODE , POSITION_KIND_CODE , DAILY_PHONE ,
CELL_PHONE , FAX_NUMBER , JOB_STATUS ,
ZIPCODE , ADDR_1 , ADDR_2 ,
SIGN_PATH , STAMP_PATH , CREATE_TYPE ,
CREATE_DATE , CREATE_EMP_ID , MODIFY_TYPE ,
MODIFY_DATE , MODIFY_EMP_ID , LOGINIP
)
SELECT
@EMP_REF_ID , @LOGINID , @LOGINID ,
@PASSWD , @EMP_NAME , @EMP_EMail ,
@POSITION_CLASS_CODE, @POSITION_GRP_CODE , @POSITION_RANK_CODE ,
@POSITION_DUTY_CODE , @POSITION_STAT_CODE , @POSITION_KIND_CODE , @DAILY_PHONE ,
@CELL_PHONE , @FAX_NUMBER , @JOB_STATUS ,
@ZIPCODE , @ADDR_1 , @ADDR_2 ,
@SIGN_PATH , @STAMP_PATH , @CREATE_TYPE ,
GETDATE() , @CREATE_EMP_ID , @MODIFY_TYPE ,
GETDATE() , @MODIFY_EMP_ID , @LOGINIP
";
string o_query = @"
INSERT INTO COM_EMP_INFO
(
EMP_REF_ID , EMP_CODE , LOGINID ,
PASSWD , EMP_NAME , EMP_EMail ,
POSITION_CLASS_CODE , POSITION_GRP_CODE , POSITION_RANK_CODE ,
POSITION_DUTY_CODE , POSITION_STAT_CODE , POSITION_KIND_CODE , DAILY_PHONE ,
CELL_PHONE , FAX_NUMBER , JOB_STATUS ,
ZIPCODE , ADDR_1 , ADDR_2 ,
SIGN_PATH , STAMP_PATH , CREATE_TYPE ,
CREATE_DATE , CREATE_EMP_ID , MODIFY_TYPE ,
MODIFY_DATE , MODIFY_EMP_ID , LOGINIP
)
SELECT
@EMP_REF_ID , @LOGINID , @LOGINID ,
@PASSWD , @EMP_NAME , @EMP_EMail ,
@POSITION_CLASS_CODE, @POSITION_GRP_CODE , @POSITION_RANK_CODE ,
@POSITION_DUTY_CODE , @POSITION_STAT_CODE , @POSITION_KIND_CODE , @DAILY_PHONE ,
@CELL_PHONE , @FAX_NUMBER , @JOB_STATUS ,
@ZIPCODE , @ADDR_1 , @ADDR_2 ,
@SIGN_PATH , @STAMP_PATH , @CREATE_TYPE ,
SYSDATE , @CREATE_EMP_ID , @MODIFY_TYPE ,
SYSDATE , @MODIFY_EMP_ID , @LOGINIP
FROM DUAL
";
IDbDataParameter[] paramArray = CreateDataParameters(25);
paramArray[0] = CreateDataParameter("@LOGINID", SqlDbType.VarChar);
paramArray[1] = CreateDataParameter("@PASSWD", SqlDbType.VarChar);
paramArray[2] = CreateDataParameter("@EMP_NAME", SqlDbType.VarChar);
paramArray[3] = CreateDataParameter("@POSITION_CLASS_CODE", SqlDbType.VarChar);
paramArray[4] = CreateDataParameter("@POSITION_GRP_CODE", SqlDbType.VarChar);
paramArray[5] = CreateDataParameter("@POSITION_RANK_CODE", SqlDbType.VarChar);
paramArray[6] = CreateDataParameter("@POSITION_DUTY_CODE", SqlDbType.VarChar);
paramArray[7] = CreateDataParameter("@POSITION_STAT_CODE", SqlDbType.VarChar);
paramArray[8] = CreateDataParameter("@POSITION_KIND_CODE", SqlDbType.VarChar);
paramArray[9] = CreateDataParameter("@EMP_EMail", SqlDbType.VarChar);
paramArray[10] = CreateDataParameter("@DAILY_PHONE", SqlDbType.VarChar);
paramArray[11] = CreateDataParameter("@CELL_PHONE", SqlDbType.VarChar);
paramArray[12] = CreateDataParameter("@FAX_NUMBER", SqlDbType.VarChar);
paramArray[13] = CreateDataParameter("@JOB_STATUS", SqlDbType.Int);
paramArray[14] = CreateDataParameter("@ZIPCODE", SqlDbType.VarChar);
paramArray[15] = CreateDataParameter("@ADDR_1", SqlDbType.VarChar);
paramArray[16] = CreateDataParameter("@ADDR_2", SqlDbType.VarChar);
paramArray[17] = CreateDataParameter("@SIGN_PATH", SqlDbType.VarChar);
paramArray[18] = CreateDataParameter("@STAMP_PATH", SqlDbType.VarChar);
paramArray[19] = CreateDataParameter("@CREATE_TYPE", SqlDbType.Int);
paramArray[20] = CreateDataParameter("@CREATE_EMP_ID", SqlDbType.Int);
paramArray[21] = CreateDataParameter("@MODIFY_TYPE", SqlDbType.Int);
paramArray[22] = CreateDataParameter("@MODIFY_EMP_ID", SqlDbType.Int);
paramArray[23] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int);
paramArray[24] = CreateDataParameter("@LOGINIP", SqlDbType.VarChar);
paramArray[0].Value = asLOGINID;
paramArray[1].Value = asPASSWD;
paramArray[2].Value = asEMP_NAME;
paramArray[3].Value = asPOSITION_CLASS_CODE;
paramArray[4].Value = asPOSITION_GRP_CODE;
paramArray[5].Value = asPOSITION_RANK_CODE;
paramArray[6].Value = asPOSITION_DUTY_CODE;
paramArray[7].Value = asPOSITION_STAT_CODE;
paramArray[8].Value = asPOSITION_KIND_CODE;
paramArray[9].Value = asEMP_EMail;
paramArray[10].Value = asDAILY_PHONE;
paramArray[11].Value = asCELL_PHONE;
paramArray[12].Value = asFAX_NUMBER;
paramArray[13].Value = aiJOB_STATUS;
paramArray[14].Value = asZIPCODE;
paramArray[15].Value = asADDR_1;
paramArray[16].Value = asADDR_2;
paramArray[17].Value = asSIGN_PATH;
paramArray[18].Value = asSTAMP_PATH;
paramArray[19].Value = aiCREATE_TYPE;
paramArray[20].Value = aiCREATE_EMP_ID;
paramArray[21].Value = aiMODIFY_TYPE;
paramArray[22].Value = aiMODIFY_EMP_ID;
paramArray[23].Value = aiEMP_REF_ID;
paramArray[24].Value = asLOGINIP;
return(DbAgentObj.ExecuteNonQuery(GetQueryStringByDb(s_query, o_query), paramArray));
}
public DataSet Select(int comp_id
, string est_id
, int estterm_ref_id
, int estterm_sub_id
, int estterm_step_id
, int est_dept_id
, int est_emp_id
, int prj_ref_id)
{
string query = @"SELECT MAP.COMP_ID
, MAP.EST_ID
, MAP.ESTTERM_REF_ID
, MAP.ESTTERM_SUB_ID
, MAP.ESTTERM_STEP_ID
, MAP.EST_DEPT_ID
, MAP.EST_EMP_ID
, MAP.PRJ_REF_ID
, MAP.STATUS_ID
, MAP.CREATE_DATE
, MAP.CREATE_USER
, MAP.UPDATE_DATE
, MAP.UPDATE_USER
, PI.PRJ_CODE
, PI.PRJ_NAME
, PI.DEFINITION
, PI.REF_STG
, PI.EFFECTIVENESS
, PI.RANGE
, PI.OWNER_DEPT_ID
, CD.DEPT_NAME as OWNER_DEPT_NAME
, PI.OWNER_EMP_ID
, CE.EMP_NAME as OWNER_EMP_NAME
, PI.REQUEST_DEPT
, PI.PRIORITY
, PI.TOTAL_BUDGET
, PI.PRJ_TYPE
, COM.CODE_NAME as PRJ_TYPE_NAME
, PI.INTERESTED_PARTIES
, PI.PLAN_START_DATE
, PI.PLAN_END_DATE
, PI.ACTUAL_START_DATE
, PI.ACTUAL_END_DATE
, PI.ISDELETE
, CI.EMP_NAME AS EST_EMP_NAME
, DI.DEPT_NAME AS EST_DEPT_NAME
, ES.ESTTERM_SUB_NAME
, ET.ESTTERM_STEP_NAME
FROM PRJ_EMP_EST_PRJ_MAP MAP
LEFT JOIN PRJ_INFO PI
ON PI.PRJ_REF_ID = MAP.PRJ_REF_ID
LEFT JOIN COM_EMP_INFO CE
ON PI.OWNER_EMP_ID = CE.EMP_REF_ID
LEFT JOIN COM_DEPT_INFO CD
ON PI.OWNER_DEPT_ID = CD.DEPT_REF_ID
LEFT JOIN COM_CODE_INFO COM
ON PI.PRJ_TYPE = COM.ETC_CODE
LEFT JOIN COM_EMP_INFO CI
ON MAP.EST_EMP_ID = CI.EMP_REF_ID
LEFT JOIN COM_DEPT_INFO DI
ON MAP.EST_DEPT_ID = DI.DEPT_REF_ID
JOIN EST_TERM_SUB ES
ON (MAP.COMP_ID = ES.COMP_ID
AND MAP.ESTTERM_SUB_ID = ES.ESTTERM_SUB_ID)
JOIN EST_TERM_STEP ET
ON (MAP.COMP_ID = ET.COMP_ID
AND MAP.ESTTERM_STEP_ID = ET.ESTTERM_STEP_ID)
WHERE (MAP.COMP_ID = @COMP_ID OR @COMP_ID = 0)
AND (MAP.EST_ID = @EST_ID OR @EST_ID ='' )
AND (MAP.ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0)
AND (MAP.ESTTERM_SUB_ID = @ESTTERM_SUB_ID OR @ESTTERM_SUB_ID = 0)
AND (MAP.ESTTERM_STEP_ID = @ESTTERM_STEP_ID OR @ESTTERM_STEP_ID = 0)
AND (MAP.EST_DEPT_ID = @EST_DEPT_ID OR @EST_DEPT_ID = 0)
AND (MAP.EST_EMP_ID = @EST_EMP_ID OR @EST_EMP_ID = 0)
AND (MAP.PRJ_REF_ID = @PRJ_REF_ID OR @PRJ_REF_ID = 0) ";
IDbDataParameter[] paramArray = CreateDataParameters(8);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[4] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[4].Value = estterm_step_id;
paramArray[5] = CreateDataParameter("@EST_DEPT_ID", SqlDbType.Int);
paramArray[5].Value = est_dept_id;
paramArray[6] = CreateDataParameter("@EST_EMP_ID", SqlDbType.Int);
paramArray[6].Value = est_emp_id;
paramArray[7] = CreateDataParameter("@PRJ_REF_ID", SqlDbType.Int);
paramArray[7].Value = prj_ref_id;
DataSet ds = DbAgentObj.FillDataSet(query, "PRJEMPESTPRJMAPGET", null, paramArray, CommandType.Text);
return(ds);
}
internal protected int UpdateData_Dac
(IDbConnection conn, IDbTransaction trx,
int iexec_ref_id, int itask_ref_id, int iitem_ref_id, string iitem_ymd, string iitem_name, string iitem_desc, string iitem_unit, string iitem_tgt, string iitem_rst, string iadd_file, string iuse_yn, int itxr_user)
{
IDbDataParameter[] paramArray = CreateDataParameters(15);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.VarChar);
paramArray[0].Value = "U";
paramArray[1] = CreateDataParameter("@iEXEC_REF_ID", SqlDbType.Int);
paramArray[1].Value = iexec_ref_id;
paramArray[2] = CreateDataParameter("@iTASK_REF_ID", SqlDbType.Int);
paramArray[2].Value = itask_ref_id;
paramArray[3] = CreateDataParameter("@iITEM_REF_ID", SqlDbType.Int);
paramArray[3].Value = iitem_ref_id;
paramArray[4] = CreateDataParameter("@iITEM_YMD", SqlDbType.VarChar, 8);
paramArray[4].Value = iitem_ymd;
paramArray[5] = CreateDataParameter("@iITEM_NAME", SqlDbType.VarChar, 100);
paramArray[5].Value = iitem_name;
paramArray[6] = CreateDataParameter("@iITEM_DESC", SqlDbType.VarChar, 1000);
paramArray[6].Value = iitem_desc;
paramArray[7] = CreateDataParameter("@iITEM_UNIT", SqlDbType.VarChar, 20);
paramArray[7].Value = iitem_unit;
paramArray[8] = CreateDataParameter("@iITEM_TGT", SqlDbType.VarChar, 100);
paramArray[8].Value = iitem_tgt;
paramArray[9] = CreateDataParameter("@iITEM_RST", SqlDbType.VarChar, 100);
paramArray[9].Value = iitem_rst;
paramArray[10] = CreateDataParameter("@iADD_FILE", SqlDbType.NVarChar, 200);
paramArray[10].Value = iadd_file;
paramArray[11] = CreateDataParameter("@iUSE_YN", SqlDbType.Char);
paramArray[11].Value = iuse_yn;
paramArray[12] = CreateDataParameter("@iTXR_USER", SqlDbType.Int);
paramArray[12].Value = itxr_user;
paramArray[13] = CreateDataParameter("@oRTN_MSG", SqlDbType.VarChar, 1000);
paramArray[13].Direction = ParameterDirection.Output;
paramArray[14] = CreateDataParameter("@oRTN_COMPLETE_YN", SqlDbType.VarChar, 1);
paramArray[14].Direction = ParameterDirection.Output;
IDataParameterCollection col;
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, GetQueryStringByDb("usp_BSC_WORK_ITEM", "PKG_BSC_WORK_ITEM.PROC_UPDATE"), paramArray, CommandType.StoredProcedure, out col);
this.Transaction_Message = GetOutputParameterValueBySP(col, "@oRTN_MSG").ToString();
this.Transaction_Result = GetOutputParameterValueBySP(col, "@oRTN_COMPLETE_YN").ToString();
return(affectedRow);
}
public DataTable GetInterfaceDataForDayResult(object dicode, object ymd, object sumtype)
{
string ymd1 = string.Empty;
string ymd3 = string.Empty;
if (ymd.ToString().Substring(4, 2) == "01")
{
ymd1 = (DataTypeUtility.GetToInt32(ymd.ToString().Substring(0, 4)) - 1).ToString() + "12";
ymd3 = ymd.ToString().Substring(0, 4) + (DataTypeUtility.GetToInt32(ymd.ToString().Substring(4, 2)) + 1).ToString("00");
}
else if (ymd.ToString().Substring(4, 2) == "12")
{
ymd1 = ymd.ToString().Substring(0, 4) + (DataTypeUtility.GetToInt32(ymd.ToString().Substring(4, 2)) - 1).ToString("00");
ymd3 = (DataTypeUtility.GetToInt32(ymd.ToString().Substring(0, 4)) + 1).ToString() + "01";
}
else
{
ymd1 = ymd.ToString().Substring(0, 4) + (DataTypeUtility.GetToInt32(ymd.ToString().Substring(4, 2)) - 1).ToString("00");
ymd3 = ymd.ToString().Substring(0, 4) + (DataTypeUtility.GetToInt32(ymd.ToString().Substring(4, 2)) + 1).ToString("00");
}
string strQuery = @"
SELECT A.YEARMONTH, A.YMD
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '01' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY01
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '02' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY02
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '03' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY03
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '04' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY04
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '05' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY05
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '06' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY06
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '07' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY07
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '08' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY08
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '09' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY09
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '10' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY10
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '11' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY11
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '12' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY12
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '13' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY13
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '14' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY14
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '15' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY15
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '16' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY16
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '17' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY17
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '18' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY18
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '19' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY19
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '20' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY20
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '21' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY21
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '22' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY22
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '23' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY23
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '24' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY24
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '25' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY25
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '26' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY26
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '27' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY27
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '28' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY28
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '29' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY29
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '30' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY30
,SUM(ISNULL(CASE WHEN B.RDDATE {0} '31' THEN B.DVALUES1 ELSE 0 END, 0)) AS DAY31
FROM (
SELECT SUBSTRING(@YMD1, 1, 4) + '년 ' + SUBSTRING(@YMD1, 5, 2) + '월' AS YEARMONTH, @YMD1 AS YMD
UNION
SELECT SUBSTRING(@YMD2, 1, 4) + '년 ' + SUBSTRING(@YMD2, 5, 2) + '월' AS YEARMONTH, @YMD2 AS YMD
UNION
SELECT SUBSTRING(@YMD3, 1, 4) + '년 ' + SUBSTRING(@YMD3, 5, 2) + '월' AS YEARMONTH, @YMD3 AS YMD
) A
LEFT OUTER JOIN BSC_INTERFACE_DATA B ON B.DICODE = @DICODE
AND B.RDTERM = A.YMD
GROUP BY A.YEARMONTH, A.YMD ORDER BY A.YMD
";
strQuery = string.Format(strQuery, (sumtype.ToString() == "1" ? "=" : "<="));
IDbDataParameter[] paramArray = CreateDataParameters(4);
paramArray[0] = CreateDataParameter("@DICODE", SqlDbType.VarChar);
paramArray[0].Value = dicode;
paramArray[1] = CreateDataParameter("@YMD1", SqlDbType.VarChar);
paramArray[1].Value = ymd1;
paramArray[2] = CreateDataParameter("@YMD2", SqlDbType.VarChar);
paramArray[2].Value = ymd.ToString();
paramArray[3] = CreateDataParameter("@YMD3", SqlDbType.VarChar);
paramArray[3].Value = ymd3;
return(DbAgentObj.FillDataSet(strQuery, "BSC_INTERFACE_DATA", null, paramArray, CommandType.Text).Tables[0]);
}
public int Insert(IDbConnection conn
, IDbTransaction trx
, int comp_id
, string est_id
, int seq
, string col_name
, string col_style_id
, string col_key
, string caption
, int width
, string data_type
, string halign
, string back_color
, string format
, string formula
, string default_value
, string col_desc
, string back_color_yn
, string format_yn
, string formula_yn
, string default_value_yn
, string visible_yn
, string col_emp_visible_yn
, DateTime create_date
, int create_user)
{
string query = @"INSERT INTO EST_COLUMN_INFO(COMP_ID
,EST_ID
,SEQ
,COL_NAME
,COL_STYLE_ID
,COL_KEY
,CAPTION
,WIDTH
,DATA_TYPE
,HALIGN
,BACK_COLOR
,FORMAT
,FORMULA
,DEFAULT_VALUE
,COL_DESC
,BACK_COLOR_YN
,FORMAT_YN
,FORMULA_YN
,DEFAULT_VALUE_YN
,VISIBLE_YN
,COL_EMP_VISIBLE_YN
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
)
VALUES (@COMP_ID
,@EST_ID
,@SEQ
,@COL_NAME
,@COL_STYLE_ID
,@COL_KEY
,@CAPTION
,@WIDTH
,@DATA_TYPE
,@HALIGN
,@BACK_COLOR
,@FORMAT
,@FORMULA
,@DEFAULT_VALUE
,@COL_DESC
,@BACK_COLOR_YN
,@FORMAT_YN
,@FORMULA_YN
,@DEFAULT_VALUE_YN
,@VISIBLE_YN
,@COL_EMP_VISIBLE_YN
,@CREATE_DATE
,@CREATE_USER
,NULL
,NULL
)" ;
IDbDataParameter[] paramArray = CreateDataParameters(23);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@SEQ", SqlDbType.Int);
paramArray[2].Value = seq;
paramArray[3] = CreateDataParameter("@COL_NAME", SqlDbType.NVarChar, 200);
paramArray[3].Value = col_name;
paramArray[4] = CreateDataParameter("@COL_STYLE_ID", SqlDbType.NVarChar, 20);
paramArray[4].Value = col_style_id;
paramArray[5] = CreateDataParameter("@COL_KEY", SqlDbType.NVarChar, 200);
paramArray[5].Value = col_key;
paramArray[6] = CreateDataParameter("@CAPTION", SqlDbType.NVarChar, 200);
paramArray[6].Value = caption;
paramArray[7] = CreateDataParameter("@WIDTH", SqlDbType.Int);
paramArray[7].Value = width;
paramArray[8] = CreateDataParameter("@DATA_TYPE", SqlDbType.NVarChar, 200);
paramArray[8].Value = data_type;
paramArray[9] = CreateDataParameter("@HALIGN", SqlDbType.NVarChar, 20);
paramArray[9].Value = halign;
paramArray[10] = CreateDataParameter("@BACK_COLOR", SqlDbType.NVarChar, 100);
paramArray[10].Value = back_color;
paramArray[11] = CreateDataParameter("@FORMAT", SqlDbType.NVarChar, 200);
paramArray[11].Value = format;
paramArray[12] = CreateDataParameter("@FORMULA", SqlDbType.NVarChar, 200);
paramArray[12].Value = formula;
paramArray[13] = CreateDataParameter("@DEFAULT_VALUE", SqlDbType.NVarChar);
paramArray[13].Value = default_value;
paramArray[14] = CreateDataParameter("@COL_DESC", SqlDbType.NVarChar, 2000);
paramArray[14].Value = col_desc;
paramArray[15] = CreateDataParameter("@BACK_COLOR_YN", SqlDbType.NChar);
paramArray[15].Value = back_color_yn;
paramArray[16] = CreateDataParameter("@FORMAT_YN", SqlDbType.NChar);
paramArray[16].Value = format_yn;
paramArray[17] = CreateDataParameter("@FORMULA_YN", SqlDbType.NChar);
paramArray[17].Value = formula_yn;
paramArray[18] = CreateDataParameter("@DEFAULT_VALUE_YN", SqlDbType.NChar);
paramArray[18].Value = formula_yn;
paramArray[19] = CreateDataParameter("@VISIBLE_YN", SqlDbType.NChar);
paramArray[19].Value = visible_yn;
paramArray[20] = CreateDataParameter("@COL_EMP_VISIBLE_YN", SqlDbType.NChar);
paramArray[20].Value = col_emp_visible_yn;
paramArray[21] = CreateDataParameter("@CREATE_DATE", SqlDbType.DateTime);
paramArray[21].Value = create_date;
paramArray[22] = CreateDataParameter("@CREATE_USER", SqlDbType.Int);
paramArray[22].Value = create_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
protected internal bool ModifyPDTAndAHPWeight_Dac(IDbConnection conn
, IDbTransaction trx
, int ver_id
, int estterm_ref_id
, int est_dept_ref_id
, int stg_ref_id
, float multiply
, float geomean
, float weight
, float sum_value
, float ws
, float ci
, float cr
, int update_user)
{
string query = @"UPDATE BSC_PDT_AHP_STG_EST_DEPT_DATA
SET MULTIPLY = @MULTIPLY
, GEOMEAN = @GEOMEAN
, WEIGHT = @WEIGHT
, SUM_VALUE = @SUM_VALUE
, WS = @WS
, CI = @CI
, CR = @CR
, UPDATE_DATE = GETDATE()
, UPDATE_USER = @UPDATE_USER
WHERE VER_ID = @VER_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND STG_REF_ID = @STG_REF_ID" ;
IDbDataParameter[] paramArray = CreateDataParameters(12);
paramArray[0] = CreateDataParameter("@VER_ID", SqlDbType.Int);
paramArray[0].Value = ver_id;
paramArray[1] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = estterm_ref_id;
paramArray[2] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[2].Value = est_dept_ref_id;
paramArray[3] = CreateDataParameter("@STG_REF_ID", SqlDbType.Int);
paramArray[3].Value = stg_ref_id;
paramArray[4] = CreateDataParameter("@MULTIPLY", SqlDbType.Float);
paramArray[4].Value = multiply;
paramArray[5] = CreateDataParameter("@GEOMEAN", SqlDbType.Float);
paramArray[5].Value = geomean;
paramArray[6] = CreateDataParameter("@WEIGHT", SqlDbType.Float);
paramArray[6].Value = weight;
paramArray[7] = CreateDataParameter("@SUM_VALUE", SqlDbType.Float);
paramArray[7].Value = sum_value;
paramArray[8] = CreateDataParameter("@WS", SqlDbType.Float);
paramArray[8].Value = ws;
paramArray[9] = CreateDataParameter("@CI", SqlDbType.Float);
paramArray[9].Value = ci;
paramArray[10] = CreateDataParameter("@CR", SqlDbType.Float);
paramArray[10].Value = cr;
paramArray[11] = CreateDataParameter("@UPDATE_USER", SqlDbType.Int);
paramArray[11].Value = update_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return((affectedRow > 0) ? true : false);
}
catch (Exception ex)
{
throw ex;
}
}
public int Update(IDbConnection conn
, IDbTransaction trx
, string board_id
, string board_category_id
, string subject_text
, string content_text
, int read_count
, string attach_no
, string p_board_id
, string group_id
, int level_id
, int seq_id
, object start_date
, object end_date
, int menu_ref_id
, string pop_up_yn
, int write_emp_id
, DateTime update_date
, int update_user)
{
string query = @"UPDATE EST_BOARD
SET BOARD_CATEGORY_ID = @BOARD_CATEGORY_ID
,SUBJECT_TEXT = @SUBJECT_TEXT
,CONTENT_TEXT = @CONTENT_TEXT
,READ_COUNT = @READ_COUNT
,ATTACH_NO = @ATTACH_NO
,P_BOARD_ID = @P_BOARD_ID
,GROUP_ID = @GROUP_ID
,LEVEL_ID = @LEVEL_ID
,SEQ_ID = @SEQ_ID
,START_DATE = @START_DATE
,END_DATE = @END_DATE
,MENU_REF_ID = @MENU_REF_ID
,POP_UP_YN = @POP_UP_YN
,WRITE_EMP_ID = @WRITE_EMP_ID
,UPDATE_DATE = @UPDATE_DATE
,UPDATE_USER = @UPDATE_USER
WHERE BOARD_ID = @BOARD_ID" ;
IDbDataParameter[] paramArray = CreateDataParameters(17);
paramArray[0] = CreateDataParameter("@BOARD_ID", SqlDbType.NVarChar, 20);
paramArray[0].Value = board_id;
paramArray[1] = CreateDataParameter("@BOARD_CATEGORY_ID", SqlDbType.NVarChar, 20);
paramArray[1].Value = board_category_id;
paramArray[2] = CreateDataParameter("@SUBJECT_TEXT", SqlDbType.NVarChar, 200);
paramArray[2].Value = subject_text;
paramArray[3] = CreateDataParameter("@CONTENT_TEXT", SqlDbType.NVarChar, 4000);
paramArray[3].Value = content_text;
paramArray[4] = CreateDataParameter("@READ_COUNT", SqlDbType.Int);
paramArray[4].Value = read_count;
paramArray[5] = CreateDataParameter("@ATTACH_NO", SqlDbType.NVarChar, 200);
paramArray[5].Value = attach_no;
paramArray[6] = CreateDataParameter("@P_BOARD_ID", SqlDbType.NVarChar, 100);
paramArray[6].Value = p_board_id;
paramArray[7] = CreateDataParameter("@GROUP_ID", SqlDbType.NVarChar, 100);
paramArray[7].Value = group_id;
paramArray[8] = CreateDataParameter("@LEVEL_ID", SqlDbType.Int);
paramArray[8].Value = level_id;
paramArray[9] = CreateDataParameter("@SEQ_ID", SqlDbType.Int);
paramArray[9].Value = seq_id;
paramArray[10] = CreateDataParameter("@START_DATE", SqlDbType.DateTime);
paramArray[10].Value = start_date;
paramArray[11] = CreateDataParameter("@END_DATE", SqlDbType.DateTime);
paramArray[11].Value = end_date;
paramArray[12] = CreateDataParameter("@MENU_REF_ID", SqlDbType.Int);
paramArray[12].Value = menu_ref_id;
paramArray[13] = CreateDataParameter("@POP_UP_YN", SqlDbType.NChar);
paramArray[13].Value = pop_up_yn;
paramArray[14] = CreateDataParameter("@WRITE_EMP_ID", SqlDbType.Int);
paramArray[14].Value = write_emp_id;
paramArray[15] = CreateDataParameter("@UPDATE_DATE", SqlDbType.DateTime);
paramArray[15].Value = update_date;
paramArray[16] = CreateDataParameter("@UPDATE_USER", SqlDbType.Int);
paramArray[16].Value = update_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable Select_DB_Join_Kpi_Info(object estterm_ref_id
, object est_type
, object dept_ref_id
, object champion_emp_ref_id
, object is_team_kpi_yn
, object kpi_code
, object kpi_name)
{
string query = @"
SELECT KI.KPI_REF_ID
, KI.KPI_CODE
, KP.KPI_NAME
, KP.KPI_DESC
, KI.CHAMPION_EMP_ID
, EMP.EMP_NAME
, DEPT.DEPT_NAME
, RATIO.EQT
, RATIO.EQL
, CASE WHEN IS_TEAM_KPI='Y' THEN '조직KPI'
ELSE '개인KPI' END AS IS_TEAM_KPI
FROM BSC_KPI_INFO KI
LEFT OUTER JOIN BSC_KPI_POOL KP
ON KI.KPI_POOL_REF_ID = KP.KPI_POOL_REF_ID
LEFT OUTER JOIN BSC_KPI_EQT_EQL RATIO
ON (
KI.KPI_REF_ID = RATIO.KPI_REF_ID
AND KI.ESTTERM_REF_ID = RATIO.ESTTERM_REF_ID
)
LEFT OUTER JOIN COM_EMP_INFO EMP
ON KI.CHAMPION_EMP_ID = EMP.EMP_REF_ID
LEFT OUTER JOIN REL_DEPT_EMP REL
ON EMP.EMP_REF_ID = REL.EMP_REF_ID
LEFT OUTER JOIN COM_DEPT_INFO DEPT
ON DEPT.DEPT_REF_ID = REL.DEPT_REF_ID
WHERE
( KI.ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0 )
AND ( KP.BASIS_USE_YN = @EST_TYPE OR @EST_TYPE ='' )
AND ( DEPT.DEPT_REF_ID = @DEPT_REF_ID OR @DEPT_REF_ID = 0 )
AND ( EMP.EMP_REF_ID = @EMP_REF_ID OR @EMP_REF_ID = 0 )
AND ( KI.IS_TEAM_KPI = @IS_TEAM_KPI_YN OR @IS_TEAM_KPI_YN ='' )
AND KI.KPI_CODE LIKE @KPI_CODE
AND KP.KPI_NAME LIKE @KPI_NAME
";
IDbDataParameter[] paramArray = CreateDataParameters(7);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = estterm_ref_id;
paramArray[1] = CreateDataParameter("@EST_TYPE", SqlDbType.NVarChar);
paramArray[1].Value = est_type;
paramArray[2] = CreateDataParameter("@DEPT_REF_ID", SqlDbType.Int);
paramArray[2].Value = dept_ref_id;
paramArray[3] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int);
paramArray[3].Value = champion_emp_ref_id;
paramArray[4] = CreateDataParameter("@IS_TEAM_KPI_YN", SqlDbType.NChar);
paramArray[4].Value = is_team_kpi_yn;
paramArray[5] = CreateDataParameter("@KPI_CODE", SqlDbType.NVarChar);
paramArray[5].Value = "%" + kpi_code + "%";
paramArray[6] = CreateDataParameter("@KPI_NAME", SqlDbType.NVarChar);
paramArray[6].Value = "%" + kpi_name + "%";
DataTable dt = DbAgentObj.Fill(query, paramArray).Tables[0];
return(dt);
}
public int Insert_Sum_Point(IDbConnection conn, IDbTransaction trx
, object admin_dept_ref_id
, object admin_emp_ref_id
, object comp_id
, object estterm_ref_id
, object estterm_sub_id
, object tgt_dept_id
, object tgt_emp_id)
{
string query = @"
INSERT INTO EST_DATA
(
COMP_ID
, EST_ID
, ESTTERM_REF_ID
, ESTTERM_SUB_ID
, ESTTERM_STEP_ID
, EST_DEPT_ID
, EST_EMP_ID
, TGT_DEPT_ID
, TGT_EMP_ID
, TGT_POS_CLS_ID
, TGT_POS_CLS_NAME
, TGT_POS_DUT_ID
, TGT_POS_DUT_NAME
, TGT_POS_GRP_ID
, TGT_POS_GRP_NAME
, TGT_POS_RNK_ID
, TGT_POS_RNK_NAME
, TGT_POS_KND_ID
, TGT_POS_KND_NAME
, DIRECTION_TYPE
, POINT_ORG
, POINT_AVG
, POINT_STD
, POINT_CTRL_ORG
, POINT
, STATUS_ID
, CREATE_USER
, CREATE_DATE
, UPDATE_USER
, UPDATE_DATE
)
(
SELECT
COMP_ID
, EST_ID
, ESTTERM_REF_ID
, ESTTERM_SUB_ID
, @ESTTERM_STEP_ID
, @ADMIN_DEPT_REF_ID --EST_DEPT_ID
, @ADMIN_EMP_REF_ID --EST_EMP_ID
, TGT_DEPT_ID
, TGT_EMP_ID
, TGT_POS_CLS_ID
, TGT_POS_CLS_NAME
, TGT_POS_DUT_ID
, TGT_POS_DUT_NAME
, TGT_POS_GRP_ID
, TGT_POS_GRP_NAME
, TGT_POS_RNK_ID
, TGT_POS_RNK_NAME
, TGT_POS_KND_ID
, TGT_POS_KND_NAME
, DIRECTION_TYPE
, AVG(POINT_ORG) AS POINT_ORG
, AVG(POINT_AVG) AS POINT_AVG
, AVG(POINT_STD) AS POINT_STD
, AVG(POINT_CTRL_ORG) AS POINT_CTRL_ORG
, AVG(POINT) AS POINT
, 'E'
, @ADMIN_EMP_REF_ID
, GETDATE()
, @ADMIN_EMP_REF_ID
, GETDATE()
FROM EST_DATA
WHERE EST_ID = @EST_ID
AND COMP_ID = @COMP_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND ESTTERM_SUB_ID = @ESTTERM_SUB_ID
AND (TGT_DEPT_ID = @TGT_DEPT_ID OR @TGT_DEPT_ID = 0)
AND (TGT_EMP_ID = @TGT_EMP_ID OR @TGT_EMP_ID = 0)
GROUP BY
COMP_ID
, EST_ID
, ESTTERM_REF_ID
, ESTTERM_SUB_ID
, ESTTERM_STEP_ID
, TGT_DEPT_ID
, TGT_EMP_ID
, TGT_POS_CLS_ID
, TGT_POS_CLS_NAME
, TGT_POS_DUT_ID
, TGT_POS_DUT_NAME
, TGT_POS_GRP_ID
, TGT_POS_GRP_NAME
, TGT_POS_RNK_ID
, TGT_POS_RNK_NAME
, TGT_POS_KND_ID
, TGT_POS_KND_NAME
, DIRECTION_TYPE
)
";
IDbDataParameter[] paramArray = CreateDataParameters(9);
paramArray[0] = CreateDataParameter("@ADMIN_EMP_REF_ID", SqlDbType.Int);
paramArray[0].Value = admin_emp_ref_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[1].Value = EST_ID;
paramArray[2] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[2].Value = comp_id;
paramArray[3] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[3].Value = estterm_ref_id;
paramArray[4] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[4].Value = estterm_sub_id;
paramArray[5] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[5].Value = 1;
paramArray[6] = CreateDataParameter("@TGT_DEPT_ID", SqlDbType.Int);
paramArray[6].Value = tgt_dept_id;
paramArray[7] = CreateDataParameter("@TGT_EMP_ID", SqlDbType.Int);
paramArray[7].Value = tgt_emp_id;
paramArray[8] = CreateDataParameter("@ADMIN_DEPT_REF_ID", SqlDbType.Int);
paramArray[8].Value = admin_dept_ref_id;
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray);
return(affectedRow);
}
public int Update(IDbConnection conn
, IDbTransaction trx
, object comp_id
, object est_id
, object up_est_id
, object est_name
, object header_color
, object grade_confirm_yn
, object bias_yn
, object bias_dept_use_yn
, object tgt_opinion_yn
, object feedback_yn
, object estterm_sub
, object estterm_step
, object fixed_weight_use_yn
, object fixed_weight
, object point_ctrl_step
, object grade_ctrl_step
, object owner_type
, object est_style_id
, object link_est_id
, object weight_type
, object scale_type
, object status_style_id
, object q_style_id
, object q_item_desc_use_yn
, object q_tgt_pos_biz_use_yn
, object all_step_visible_yn
, object emp_com_dept_yn
, object bias_type_id
, object visible_past_point_yn
, object est_qtt_mbo_yn
, object mbo_score_estimate_yn
, object dashboard_type
, object question_previous_step_yn
, object use_yn
, DateTime update_date
, int update_user)
{
string query = @"UPDATE EST_INFO
SET EST_NAME = CASE WHEN @EST_NAME IS NULL THEN EST_NAME ELSE @EST_NAME END
,HEADER_COLOR = CASE WHEN @HEADER_COLOR IS NULL THEN HEADER_COLOR ELSE @HEADER_COLOR END
,GRADE_CONFIRM_YN = CASE WHEN @GRADE_CONFIRM_YN IS NULL THEN GRADE_CONFIRM_YN ELSE @GRADE_CONFIRM_YN END
,BIAS_YN = CASE WHEN @BIAS_YN IS NULL THEN BIAS_YN ELSE @BIAS_YN END
,BIAS_DEPT_USE_YN = CASE WHEN @BIAS_DEPT_USE_YN IS NULL THEN BIAS_DEPT_USE_YN ELSE @BIAS_DEPT_USE_YN END
,TGT_OPINION_YN = CASE WHEN @TGT_OPINION_YN IS NULL THEN TGT_OPINION_YN ELSE @TGT_OPINION_YN END
,FEEDBACK_YN = CASE WHEN @FEEDBACK_YN IS NULL THEN FEEDBACK_YN ELSE @FEEDBACK_YN END
,ESTTERM_SUB = CASE WHEN @ESTTERM_SUB IS NULL THEN ESTTERM_SUB ELSE @ESTTERM_SUB END
,ESTTERM_STEP = CASE WHEN @ESTTERM_STEP IS NULL THEN ESTTERM_STEP ELSE @ESTTERM_STEP END
,FIXED_WEIGHT_USE_YN = CASE WHEN @FIXED_WEIGHT_USE_YN IS NULL THEN FIXED_WEIGHT_USE_YN ELSE @FIXED_WEIGHT_USE_YN END
,FIXED_WEIGHT = CASE WHEN @FIXED_WEIGHT IS NULL THEN FIXED_WEIGHT ELSE @FIXED_WEIGHT END
,POINT_CTRL_STEP = CASE WHEN @POINT_CTRL_STEP IS NULL THEN POINT_CTRL_STEP ELSE @POINT_CTRL_STEP END
,GRADE_CTRL_STEP = CASE WHEN @GRADE_CTRL_STEP IS NULL THEN GRADE_CTRL_STEP ELSE @GRADE_CTRL_STEP END
,OWNER_TYPE = CASE WHEN @OWNER_TYPE IS NULL THEN OWNER_TYPE ELSE @OWNER_TYPE END
,EST_STYLE_ID = CASE WHEN @EST_STYLE_ID IS NULL THEN EST_STYLE_ID ELSE @EST_STYLE_ID END
,LINK_EST_ID = CASE WHEN @LINK_EST_ID IS NULL THEN LINK_EST_ID ELSE @LINK_EST_ID END
,WEIGHT_TYPE = CASE WHEN @WEIGHT_TYPE IS NULL THEN WEIGHT_TYPE ELSE @WEIGHT_TYPE END
,SCALE_TYPE = CASE WHEN @SCALE_TYPE IS NULL THEN SCALE_TYPE ELSE @SCALE_TYPE END
,STATUS_STYLE_ID = CASE WHEN @STATUS_STYLE_ID IS NULL THEN STATUS_STYLE_ID ELSE @STATUS_STYLE_ID END
,Q_STYLE_ID = CASE WHEN @Q_STYLE_ID IS NULL THEN Q_STYLE_ID ELSE @Q_STYLE_ID END
,Q_ITEM_DESC_USE_YN = CASE WHEN @Q_ITEM_DESC_USE_YN IS NULL THEN Q_ITEM_DESC_USE_YN ELSE @Q_ITEM_DESC_USE_YN END
,Q_TGT_POS_BIZ_USE_YN = CASE WHEN @Q_TGT_POS_BIZ_USE_YN IS NULL THEN Q_TGT_POS_BIZ_USE_YN ELSE @Q_TGT_POS_BIZ_USE_YN END
,ALL_STEP_VISIBLE_YN = CASE WHEN @ALL_STEP_VISIBLE_YN IS NULL THEN ALL_STEP_VISIBLE_YN ELSE @ALL_STEP_VISIBLE_YN END
,EMP_COM_DEPT_YN = CASE WHEN @EMP_COM_DEPT_YN IS NULL THEN EMP_COM_DEPT_YN ELSE @EMP_COM_DEPT_YN END
,BIAS_TYPE_ID = CASE WHEN @BIAS_TYPE_ID IS NULL THEN BIAS_TYPE_ID ELSE @BIAS_TYPE_ID END
,VISIBLE_PAST_POINT_YN = CASE WHEN @VISIBLE_PAST_POINT_YN IS NULL THEN VISIBLE_PAST_POINT_YN ELSE @VISIBLE_PAST_POINT_YN END
,EST_QTT_MBO_YN = CASE WHEN @EST_QTT_MBO_YN IS NULL THEN EST_QTT_MBO_YN ELSE @EST_QTT_MBO_YN END
,MBO_SCORE_ESTIMATE_YN = CASE WHEN @MBO_SCORE_ESTIMATE_YN IS NULL THEN MBO_SCORE_ESTIMATE_YN ELSE @MBO_SCORE_ESTIMATE_YN END
,DASHBOARD_TYPE = CASE WHEN @DASHBOARD_TYPE IS NULL THEN DASHBOARD_TYPE ELSE @DASHBOARD_TYPE END
,Q_PREVIOUS_STEP_VISIBLE_YN = CASE WHEN @Q_PREVIOUS_STEP_VISIBLE_YN IS NULL THEN Q_PREVIOUS_STEP_VISIBLE_YN ELSE @Q_PREVIOUS_STEP_VISIBLE_YN END
,USE_YN = CASE WHEN @USE_YN IS NULL THEN USE_YN ELSE @USE_YN END
,UPDATE_DATE = @UPDATE_DATE
,UPDATE_USER = @UPDATE_USER
WHERE COMP_ID = @COMP_ID
AND EST_ID = @EST_ID
AND @TEMP = 0
--AND @UP_EST_ID != '00' -- 2011.12.29 박효동 : 허성덕과장과 협의하여 왜그런지 몰르지만 최상위수정못하게 막아놓은거 빼버림
";
string query2 = @"UPDATE EST_INFO
SET EST_NAME = nvl(@EST_NAME,EST_NAME)
,HEADER_COLOR = nvl(@HEADER_COLOR,HEADER_COLOR)
,GRADE_CONFIRM_YN = nvl(@GRADE_CONFIRM_YN,GRADE_CONFIRM_YN )
,BIAS_YN = nvl(@BIAS_YN,BIAS_YN)
,BIAS_DEPT_USE_YN = nvl(@BIAS_DEPT_USE_YN,BIAS_DEPT_USE_YN)
,TGT_OPINION_YN = nvl(@TGT_OPINION_YN,TGT_OPINION_YN)
,FEEDBACK_YN = nvl(@FEEDBACK_YN,FEEDBACK_YN)
,ESTTERM_SUB = nvl(@ESTTERM_SUB, ESTTERM_SUB)
,ESTTERM_STEP = nvl(@ESTTERM_STEP,ESTTERM_STEP)
,FIXED_WEIGHT_USE_YN = nvl(@FIXED_WEIGHT_USE_YN,FIXED_WEIGHT_USE_YN )
,FIXED_WEIGHT = nvl(@FIXED_WEIGHT,FIXED_WEIGHT)
,POINT_CTRL_STEP = nvl( @POINT_CTRL_STEP,POINT_CTRL_STEP )
,GRADE_CTRL_STEP = nvl(@GRADE_CTRL_STEP,GRADE_CTRL_STEP )
,OWNER_TYPE = nvl(@OWNER_TYPE, OWNER_TYPE )
,EST_STYLE_ID = nvl( @EST_STYLE_ID , EST_STYLE_ID )
,LINK_EST_ID = nvl(@LINK_EST_ID ,LINK_EST_ID )
,WEIGHT_TYPE = nvl(@WEIGHT_TYPE, WEIGHT_TYPE )
,SCALE_TYPE = nvl(@SCALE_TYPE,SCALE_TYPE )
,STATUS_STYLE_ID = nvl(@STATUS_STYLE_ID , STATUS_STYLE_ID )
,Q_STYLE_ID = nvl(@Q_STYLE_ID , Q_STYLE_ID )
,Q_ITEM_DESC_USE_YN = nvl(@Q_ITEM_DESC_USE_YN ,Q_ITEM_DESC_USE_YN )
,Q_TGT_POS_BIZ_USE_YN = nvl(@Q_TGT_POS_BIZ_USE_YN , Q_TGT_POS_BIZ_USE_YN )
,ALL_STEP_VISIBLE_YN = nvl( @ALL_STEP_VISIBLE_YN ,ALL_STEP_VISIBLE_YN )
,EMP_COM_DEPT_YN = nvl(@EMP_COM_DEPT_YN ,EMP_COM_DEPT_YN )
,BIAS_TYPE_ID = nvl(@BIAS_TYPE_ID, BIAS_TYPE_ID )
,VISIBLE_PAST_POINT_YN = nvl(@VISIBLE_PAST_POINT_YN,VISIBLE_PAST_POINT_YN )
,EST_QTT_MBO_YN = nvl(@EST_QTT_MBO_YN , EST_QTT_MBO_YN )
,MBO_SCORE_ESTIMATE_YN = nvl(@MBO_SCORE_ESTIMATE_YN ,MBO_SCORE_ESTIMATE_YN )
,DASHBOARD_TYPE = nvl(@DASHBOARD_TYPE ,DASHBOARD_TYPE )
,Q_PREVIOUS_STEP_VISIBLE_YN = nvl(@Q_PREVIOUS_STEP_VISIBLE_YN , Q_PREVIOUS_STEP_VISIBLE_YN )
,USE_YN = nvl(@USE_YN, USE_YN )
,UPDATE_DATE = @UPDATE_DATE
,UPDATE_USER = @UPDATE_USER
WHERE COMP_ID = @COMP_ID
AND EST_ID = @EST_ID
AND @TEMP = 0
--AND @UP_EST_ID != '00' -- 2011.12.29 박효동 : 허성덕과장과 협의하여 왜그런지 몰르지만 최상위수정못하게 막아놓은거 빼버림
";
IDbDataParameter[] paramArray = CreateDataParameters(36);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[1].Value = est_id;
//paramArray[2] = CreateDataParameter("@UP_EST_ID", SqlDbType.NVarChar, 20);
//paramArray[2].Value = up_est_id;
paramArray[2] = CreateDataParameter("@TEMP", SqlDbType.Int);
paramArray[2].Value = 0;
paramArray[3] = CreateDataParameter("@EST_NAME", SqlDbType.NVarChar);
paramArray[3].Value = est_name;
paramArray[4] = CreateDataParameter("@HEADER_COLOR", SqlDbType.NVarChar);
paramArray[4].Value = header_color;
paramArray[5] = CreateDataParameter("@GRADE_CONFIRM_YN", SqlDbType.NChar);
paramArray[5].Value = grade_confirm_yn;
paramArray[6] = CreateDataParameter("@BIAS_YN", SqlDbType.NChar);
paramArray[6].Value = bias_yn;
paramArray[7] = CreateDataParameter("@BIAS_DEPT_USE_YN", SqlDbType.NChar);
paramArray[7].Value = bias_dept_use_yn;
paramArray[8] = CreateDataParameter("@TGT_OPINION_YN", SqlDbType.NChar);
paramArray[8].Value = tgt_opinion_yn;
paramArray[9] = CreateDataParameter("@FEEDBACK_YN", SqlDbType.NChar);
paramArray[9].Value = feedback_yn;
paramArray[10] = CreateDataParameter("@ESTTERM_SUB", SqlDbType.Int);
paramArray[10].Value = estterm_sub;
paramArray[11] = CreateDataParameter("@ESTTERM_STEP", SqlDbType.Int);
paramArray[11].Value = estterm_step;
paramArray[12] = CreateDataParameter("@FIXED_WEIGHT_USE_YN", SqlDbType.NChar);
paramArray[12].Value = fixed_weight_use_yn;
paramArray[13] = CreateDataParameter("@FIXED_WEIGHT", SqlDbType.Decimal);
paramArray[13].Value = fixed_weight;
paramArray[14] = CreateDataParameter("@POINT_CTRL_STEP", SqlDbType.Int);
paramArray[14].Value = point_ctrl_step;
paramArray[15] = CreateDataParameter("@GRADE_CTRL_STEP", SqlDbType.Int);
paramArray[15].Value = grade_ctrl_step;
paramArray[16] = CreateDataParameter("@OWNER_TYPE", SqlDbType.NChar);
paramArray[16].Value = owner_type;
paramArray[17] = CreateDataParameter("@EST_STYLE_ID", SqlDbType.NVarChar);
paramArray[17].Value = est_style_id;
paramArray[18] = CreateDataParameter("@LINK_EST_ID", SqlDbType.NVarChar);
paramArray[18].Value = link_est_id;
paramArray[19] = CreateDataParameter("@WEIGHT_TYPE", SqlDbType.NVarChar);
paramArray[19].Value = weight_type;
paramArray[20] = CreateDataParameter("@SCALE_TYPE", SqlDbType.NVarChar);
paramArray[20].Value = scale_type;
paramArray[21] = CreateDataParameter("@STATUS_STYLE_ID", SqlDbType.NVarChar);
paramArray[21].Value = status_style_id;
paramArray[22] = CreateDataParameter("@Q_STYLE_ID", SqlDbType.NVarChar);
paramArray[22].Value = q_style_id;
paramArray[23] = CreateDataParameter("@Q_ITEM_DESC_USE_YN", SqlDbType.NChar);
paramArray[23].Value = q_item_desc_use_yn;
paramArray[24] = CreateDataParameter("@Q_TGT_POS_BIZ_USE_YN", SqlDbType.NChar);
paramArray[24].Value = q_tgt_pos_biz_use_yn;
paramArray[25] = CreateDataParameter("@ALL_STEP_VISIBLE_YN", SqlDbType.NChar);
paramArray[25].Value = all_step_visible_yn;
paramArray[26] = CreateDataParameter("@EMP_COM_DEPT_YN", SqlDbType.NChar);
paramArray[26].Value = emp_com_dept_yn;
paramArray[27] = CreateDataParameter("@BIAS_TYPE_ID", SqlDbType.NVarChar);
paramArray[27].Value = bias_type_id;
paramArray[28] = CreateDataParameter("@VISIBLE_PAST_POINT_YN", SqlDbType.NChar);
paramArray[28].Value = visible_past_point_yn;
paramArray[29] = CreateDataParameter("@EST_QTT_MBO_YN", SqlDbType.NChar);
paramArray[29].Value = est_qtt_mbo_yn;
paramArray[30] = CreateDataParameter("@MBO_SCORE_ESTIMATE_YN", SqlDbType.NChar);
paramArray[30].Value = mbo_score_estimate_yn;
paramArray[31] = CreateDataParameter("@DASHBOARD_TYPE", SqlDbType.NVarChar);
paramArray[31].Value = dashboard_type;
paramArray[32] = CreateDataParameter("@Q_PREVIOUS_STEP_VISIBLE_YN", SqlDbType.NChar);
paramArray[32].Value = question_previous_step_yn;
paramArray[33] = CreateDataParameter("@USE_YN", SqlDbType.NChar);
paramArray[33].Value = use_yn;
paramArray[34] = CreateDataParameter("@UPDATE_DATE", SqlDbType.DateTime);
paramArray[34].Value = update_date;
paramArray[35] = CreateDataParameter("@UPDATE_USER", SqlDbType.Int);
paramArray[35].Value = update_user;
//IDbDataParameter[] paramArray = CreateDataParameters(5);
//paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
//paramArray[0].Value = comp_id;
//paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
//paramArray[1].Value = est_id;
////paramArray[2] = CreateDataParameter("@TEMP", SqlDbType.Int);
////paramArray[2].Value = 0;
//paramArray[2] = CreateDataParameter("@EST_NAME", SqlDbType.NVarChar);
//paramArray[2].Value = est_name;
//paramArray[3] = CreateDataParameter("@UPDATE_DATE", SqlDbType.DateTime);
//paramArray[3].Value= update_date;
//paramArray[4] = CreateDataParameter("@UPDATE_USER", SqlDbType.Int);
//paramArray[4].Value= update_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, GetQueryStringByDb(query, query2), paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 피평가자별 평가/미평가 현황, N:미평가, P:평가중, E:평가완료
/// </summary>
public DataTable Select_Est_Stat_GroupByEmp(object est_id
, object estterm_ref_id
, object estterm_sub_id
, object tgt_dept_id)
{
string query = @"
SELECT TGT_DEPT_ID, TGT_DEPT_NAME, TGT_EMP_ID, TGT_EMP_NAME
, TOTAL_EST_CNT, STATUS_N_CNT, STATUS_P_CNT, STATUS_E_CNT
FROM
(
SELECT DEPT.DEPT_REF_ID AS TGT_DEPT_ID
, DEPT.DEPT_NAME AS TGT_DEPT_NAME
, CASE WHEN EMP.EMP_REF_ID IS NULL THEN ED.TGT_EMP_ID
ELSE EMP.EMP_REF_ID END AS TGT_EMP_ID
, CASE WHEN EMP.EMP_NAME IS NULL THEN CAST(ED.TGT_EMP_ID AS varchar(100))
ELSE EMP.EMP_NAME END AS TGT_EMP_NAME
, SUM(CASE WHEN ED.EST_ID IS NOT NULL THEN 1 ELSE 0 END) AS TOTAL_EST_CNT
, SUM(CASE WHEN ED.STATUS_ID='N' THEN 1 ELSE 0 END) AS STATUS_N_CNT
, SUM(CASE WHEN ED.STATUS_ID='P' THEN 1 ELSE 0 END) AS STATUS_P_CNT
, SUM(CASE WHEN ED.STATUS_ID='E' THEN 1 ELSE 0 END) AS STATUS_E_CNT
FROM COM_EMP_INFO EMP
FULL OUTER JOIN (
SELECT EST_ID, TGT_DEPT_ID, TGT_EMP_ID, STATUS_ID
FROM EST_DATA
WHERE EST_ID = @EST_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND ESTTERM_SUB_ID = @ESTTERM_SUB_ID
AND ESTTERM_STEP_ID <> 1
) ED
ON EMP.EMP_REF_ID = ED.TGT_EMP_ID
LEFT OUTER JOIN COM_DEPT_INFO DEPT
ON ED.TGT_DEPT_ID = DEPT.DEPT_REF_ID
WHERE DEPT.DEPT_REF_ID = @TGT_DEPT_ID
GROUP BY DEPT.DEPT_REF_ID
, DEPT.DEPT_NAME
, CASE WHEN EMP.EMP_REF_ID IS NULL THEN ED.TGT_EMP_ID
ELSE EMP.EMP_REF_ID END
, CASE WHEN EMP.EMP_NAME IS NULL THEN CAST(ED.TGT_EMP_ID AS varchar(100))
ELSE EMP.EMP_NAME END
)
ORDER BY TGT_EMP_ID
";
IDbDataParameter[] paramArray = CreateDataParameters(4);
paramArray[0] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[0].Value = est_id;
paramArray[1] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = estterm_ref_id;
paramArray[2] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[2].Value = estterm_sub_id;
paramArray[3] = CreateDataParameter("@TGT_DEPT_ID", SqlDbType.Int);
paramArray[3].Value = tgt_dept_id;
DataTable dt = DbAgentObj.FillDataSet(query, "EST_STAT_EMP", null, paramArray, CommandType.Text).Tables[0];
return(dt);
}
public int Insert(IDbConnection conn
, IDbTransaction trx
, int comp_id
, string rel_grp_id
, string est_id
, int estterm_ref_id
, string rel_grp_name
, string rel_grp_desc
, DateTime create_date
, int create_user)
{
string query = @"INSERT INTO EST_REL_GROUP_INFO( COMP_ID
,REL_GRP_ID
,EST_ID
,ESTTERM_REF_ID
,REL_GRP_NAME
,REL_GRP_DESC
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
)
VALUES (@COMP_ID
,@REL_GRP_ID
,@EST_ID
,@ESTTERM_REF_ID
,@REL_GRP_NAME
,@REL_GRP_DESC
,@CREATE_DATE
,@CREATE_USER
,NULL
,NULL
)" ;
IDbDataParameter[] paramArray = CreateDataParameters(8);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@REL_GRP_ID", SqlDbType.NVarChar);
paramArray[1].Value = rel_grp_id;
paramArray[2] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar);
paramArray[2].Value = est_id;
paramArray[3] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[3].Value = estterm_ref_id;
paramArray[4] = CreateDataParameter("@REL_GRP_NAME", SqlDbType.NVarChar);
paramArray[4].Value = rel_grp_name;
paramArray[5] = CreateDataParameter("@REL_GRP_DESC", SqlDbType.NVarChar);
paramArray[5].Value = rel_grp_desc;
paramArray[6] = CreateDataParameter("@CREATE_DATE", SqlDbType.DateTime);
paramArray[6].Value = create_date;
paramArray[7] = CreateDataParameter("@CREATE_USER", SqlDbType.Int);
paramArray[7].Value = create_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
public int Update(IDbConnection conn
, IDbTransaction trx
, int comp_id
, string est_id
, int estterm_ref_id
, int estterm_sub_id
, int estterm_step_id
, int est_dept_id
, int est_emp_id
, int prj_ref_id
, string status_id
, DateTime update_date
, int update_user)
{
string query = @"UPDATE PRJ_EMP_EST_PRJ_MAP
SET STATUS_ID = @STATUS_ID
,UPDATE_DATE = @UPDATE_DATE
,UPDATE_USER = @UPDATE_USER
WHERE COMP_ID = @COMP_ID
AND EST_ID = @EST_ID
AND ESTTERM_REF_ID = @ESTTERM_REF_ID
AND ESTTERM_SUB_ID = @ESTTERM_SUB_ID
AND ESTTERM_STEP_ID = @ESTTERM_STEP_ID
AND EST_DEPT_ID = @EST_DEPT_ID
AND EST_EMP_ID = @EST_EMP_ID
AND PRJ_REF_ID = @PRJ_REF_ID";
IDbDataParameter[] paramArray = CreateDataParameters(10);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[3] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[3].Value = estterm_step_id;
paramArray[4] = CreateDataParameter("@EST_DEPT_ID", SqlDbType.Int);
paramArray[4].Value = est_dept_id;
paramArray[5] = CreateDataParameter("@EST_EMP_ID", SqlDbType.Int);
paramArray[5].Value = est_emp_id;
paramArray[6] = CreateDataParameter("@PRJ_REF_ID", SqlDbType.Int);
paramArray[6].Value = prj_ref_id;
paramArray[7] = CreateDataParameter("@STATUS_ID", SqlDbType.NVarChar, 12);
paramArray[7].Value = status_id;
paramArray[8] = CreateDataParameter("@UPDATE_DATE", SqlDbType.DateTime);
paramArray[8].Value = update_date;
paramArray[9] = CreateDataParameter("@UPDATE_USER", SqlDbType.Int);
paramArray[9].Value = update_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
public int Insert(IDbConnection conn
, IDbTransaction trx
, int comp_id
, string est_id
, int estterm_ref_id
, int estterm_sub_id
, int estterm_step_id
, int est_dept_id
, int est_emp_id
, int tgt_dept_id
, int tgt_emp_id
, int seq
, string comment
, string est_tgt_type
, string confirm_type
, DateTime create_date
, int create_user)
{
string query = @"INSERT INTO EST_QUESTION_COMMENT(COMP_ID
,EST_ID
,ESTTERM_REF_ID
,ESTTERM_SUB_ID
,ESTTERM_STEP_ID
,EST_DEPT_ID
,EST_EMP_ID
,TGT_DEPT_ID
,TGT_EMP_ID
,SEQ
,COMMENT
,EST_TGT_TYPE
,CONFIRM_TYPE
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
)
VALUES (@COMP_ID
,@EST_ID
,@ESTTERM_REF_ID
,@ESTTERM_SUB_ID
,@ESTTERM_STEP_ID
,@EST_DEPT_ID
,@EST_EMP_ID
,@TGT_DEPT_ID
,@TGT_EMP_ID
,@SEQ
,@COMMENTS
,@EST_TGT_TYPE
,@CONFIRM_TYPE
,@CREATE_DATE
,@CREATE_USER
,NULL
,NULL
)" ;
IDbDataParameter[] paramArray = CreateDataParameters(15);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = est_id;
paramArray[2] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[2].Value = estterm_ref_id;
paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[3].Value = estterm_sub_id;
paramArray[4] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[4].Value = estterm_step_id;
paramArray[5] = CreateDataParameter("@EST_DEPT_ID", SqlDbType.Int);
paramArray[5].Value = est_dept_id;
paramArray[6] = CreateDataParameter("@EST_EMP_ID", SqlDbType.Int);
paramArray[6].Value = est_emp_id;
paramArray[7] = CreateDataParameter("@TGT_DEPT_ID", SqlDbType.Int);
paramArray[7].Value = tgt_dept_id;
paramArray[8] = CreateDataParameter("@TGT_EMP_ID", SqlDbType.Int);
paramArray[8].Value = tgt_emp_id;
paramArray[9] = CreateDataParameter("@SEQ", SqlDbType.Int);
paramArray[9].Value = seq;
paramArray[10] = CreateDataParameter("@COMMENTS", SqlDbType.NVarChar, 4000);
paramArray[10].Value = comment;
paramArray[11] = CreateDataParameter("@EST_TGT_TYPE", SqlDbType.NVarChar, 6);
paramArray[11].Value = est_tgt_type;
paramArray[12] = CreateDataParameter("@CONFIRM_TYPE", SqlDbType.NVarChar, 6);
paramArray[12].Value = confirm_type;
paramArray[13] = CreateDataParameter("@CREATE_DATE", SqlDbType.DateTime);
paramArray[13].Value = create_date;
paramArray[14] = CreateDataParameter("@CREATE_USER", SqlDbType.Int);
paramArray[14].Value = create_user;
try
{
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
catch (Exception ex)
{
throw ex;
}
}
public int InsertApprovalPrc_DB(IDbConnection conn
, IDbTransaction trx
, object app_ref_id
, object version_no
, object line_step
, object app_emp_id
, object complete_yn
, object comments
, object return_reason
, object line_type
, object app_method
, object create_user
, object create_date)
{
string query = @"
INSERT INTO COM_APPROVAL_PRC(
APP_REF_ID
,VERSION_NO
,LINE_STEP
,APP_EMP_ID
,COMPLETE_YN
,COMMENTS
,RETURN_REASON
,LINE_TYPE
, APP_METHOD
, CREATE_USER
, CREATE_DATE
, UPDATE_USER
, UPDATE_DATE
)
VALUES (
@APP_REF_ID
,@VERSION_NO
,@LINE_STEP
,@APP_EMP_ID
,@COMPLETE_YN
,@COMMENTS
,@RETURN_REASON
,@LINE_TYPE
, @APP_METHOD
, @CREATE_USER
, @CREATE_DATE
, NULL
, NULL
)";
IDbDataParameter[] paramArray = CreateDataParameters(11);
paramArray[0] = CreateDataParameter("@APP_REF_ID", SqlDbType.Int);
paramArray[0].Value = app_ref_id;
paramArray[1] = CreateDataParameter("@VERSION_NO", SqlDbType.Int);
paramArray[1].Value = version_no;
paramArray[2] = CreateDataParameter("@LINE_STEP", SqlDbType.VarChar);
paramArray[2].Value = line_step;
paramArray[3] = CreateDataParameter("@APP_EMP_ID", SqlDbType.Int);
paramArray[3].Value = app_emp_id;
paramArray[4] = CreateDataParameter("@COMPLETE_YN", SqlDbType.VarChar);
paramArray[4].Value = complete_yn;
paramArray[5] = CreateDataParameter("@COMMENTS", SqlDbType.VarChar);
paramArray[5].Value = comments;
paramArray[6] = CreateDataParameter("@RETURN_REASON", SqlDbType.VarChar);
paramArray[6].Value = return_reason;
paramArray[7] = CreateDataParameter("@LINE_TYPE", SqlDbType.VarChar);
paramArray[7].Value = line_type;
paramArray[8] = CreateDataParameter("@APP_METHOD", SqlDbType.VarChar);
paramArray[8].Value = app_method;
paramArray[9] = CreateDataParameter("@CREATE_USER", SqlDbType.Int);
paramArray[9].Value = create_user;
paramArray[10] = CreateDataParameter("@CREATE_DATE", SqlDbType.DateTime);
paramArray[10].Value = create_date;
int affectedRow = DbAgentObj.ExecuteNonQuery(conn, trx, query, paramArray, CommandType.Text);
return(affectedRow);
}
