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