internal protected DataSet GetEstDeptOrgScoreInfo_Dac(int estterm_ref_id, string score_code) { string query = @"SELECT ESTTERM_REF_ID ,SCORE_CODE ,SCORE_NAME ,MIN_VALUE ,MAX_VALUE ,CREATE_USER ,CREATE_DATE ,UPDATE_USER ,UPDATE_DATE FROM BSC_EST_DEPT_ORG_SCORE_INFO WHERE (ESTTERM_REF_ID = CASE WHEN ISNULL(@ESTTERM_REF_ID,0)<1 THEN ESTTERM_REF_ID ELSE @ESTTERM_REF_ID END) AND (SCORE_CODE LIKE (@SCORE_CODE " + DbAgentHelper.GetConcatinationChar() + @" '%' ) OR @SCORE_CODE ='' ) ORDER BY MIN_VALUE DESC"; IDbDataParameter[] paramArray = CreateDataParameters(2); paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int); paramArray[0].Value = estterm_ref_id; paramArray[1] = CreateDataParameter("@SCORE_CODE", SqlDbType.VarChar); paramArray[1].Value = score_code; DataSet ds = DbAgentObj.FillDataSet(DbAgentHelper.GetQueryStringReplace(query), "ESTDEPTORGSCOREINFOGET", null, paramArray, CommandType.Text); return(ds); }
public DataTable SelectData_DB(object iestterm_ref_id , object emp_ref_id , object use_yn) { string query = @" SELECT ESTTERM_REF_ID ,EMP_REF_ID ,KPI_REF_ID ,WEIGHT ,USE_YN FROM BSC_MBO_KPI_WEIGHT WHERE (ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0 ) AND (EMP_REF_ID = @EMP_REF_ID OR @EMP_REF_ID = 0 ) AND (USE_YN = @USE_YN OR @USE_YN ='' ) -- 'Y' "; query = DbAgentHelper.GetQueryStringReplace(query); IDbDataParameter[] paramArray = CreateDataParameters(3); paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int); paramArray[0].Value = iestterm_ref_id; paramArray[1] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int); paramArray[1].Value = emp_ref_id; paramArray[2] = CreateDataParameter("@USE_YN", SqlDbType.VarChar); paramArray[2].Value = use_yn; //DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_KPI_TARGET", "PKG_BSC_KPI_TARGET.PROC_SELECT_ALL"), "BSC_KPI_TARGET", null, paramArray, CommandType.StoredProcedure); DataTable dt = DbAgentObj.FillDataSet(query, "BSC_MBO_KPI_WEIGHT", null, paramArray, CommandType.Text).Tables[0]; return(dt); }
// 장동건 추가(for 성덕여왕) /// <summary> /// 버전별 수정계획 내역보기 (BSC0303M1.ASPX) /// </summary> /// <param name="iestterm_ref_id"></param> /// <param name="ikpi_ref_id"></param> /// <param name="ikpi_target_version_id"></param> /// <returns></returns> /// public DataSet GetAllList_Detail(int iestterm_ref_id, int ikpi_ref_id, int ikpi_target_version_id) { string query = @" SELECT YMD , TARGET_MS --AS '당월계획' , TARGET_TS --AS '누적계획' FROM BSC_KPI_TARGET WHERE ESTTERM_REF_ID = @iESTTERM_REF_ID AND KPI_REF_ID = @iKPI_REF_ID AND (KPI_TARGET_VERSION_ID = @iKPI_TARGET_VERSION_ID OR @iKPI_TARGET_VERSION_ID = 0) "; query = DbAgentHelper.GetQueryStringReplace(query); IDbDataParameter[] paramArray = CreateDataParameters(3); paramArray[0] = CreateDataParameter("@iESTTERM_REF_ID", SqlDbType.Int); paramArray[0].Value = iestterm_ref_id; paramArray[1] = CreateDataParameter("@iKPI_REF_ID", SqlDbType.Int); paramArray[1].Value = ikpi_ref_id; paramArray[2] = CreateDataParameter("@iKPI_TARGET_VERSION_ID", SqlDbType.Int); paramArray[2].Value = ikpi_target_version_id; //DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_KPI_TARGET", "PKG_BSC_KPI_TARGET.PROC_SELECT_ALL"), "BSC_KPI_TARGET", null, paramArray, CommandType.StoredProcedure); DataSet ds = DbAgentObj.FillDataSet(query, "BSC_KPI_TARGET", null, paramArray, CommandType.Text); return(ds); }
/// <summary> /// 전체지표 등급현황 /// </summary> /// <param name="iestterm_ref_id"></param> /// <param name="iymd"></param> /// <param name="isum_type"></param> /// <param name="iest_dept_ref_id"></param> /// <returns></returns> public DataSet GetKpiEntGradeStatus(string isymd, string ieymd, string isum_type, int iest_dept_ref_id) { string sSQL = @"SELECT TD.YMD, TD.THRESHOLD_REF_ID, TD.THRESHOLD_KNAME, TD.THRESHOLD_ENAME, TD.IMAGE_FILE_NAME, TD.SEQUENCE, TD.THRESHOLD_COLOR , TD.CNT_KPI, TD.CNT_ALL , CASE WHEN TD.CNT_ALL=0 THEN 0 ELSE ROUND((TD.CNT_KPI/(TD.CNT_ALL)),4)*100 END as COMP_RATE FROM ( SELECT TB.YMD, TB.THRESHOLD_REF_ID, TC.THRESHOLD_KNAME, TC.THRESHOLD_ENAME, TC.IMAGE_FILE_NAME, TC.SEQUENCE, TC.THRESHOLD_COLOR , (TB.CNT_KPI*0.1)*10 as CNT_KPI , SUM(TB.CNT_KPI) OVER(PARTITION BY TB.YMD) as CNT_ALL FROM ( SELECT TA.YMD, TA.THRESHOLD_REF_ID, COUNT(TA.THRESHOLD_REF_ID) as CNT_KPI FROM ( SELECT KS.YMD , CASE WHEN @iSUM_TYPE='MS' THEN KS.THRESHOLD_REF_ID_MS ELSE KS.THRESHOLD_REF_ID_TS END as THRESHOLD_REF_ID FROM BSC_KPI_SCORE KS INNER JOIN BSC_KPI_INFO KI ON KS.ESTTERM_REF_ID = KI.ESTTERM_REF_ID AND KS.KPI_REF_ID = KI.KPI_REF_ID AND KI.USE_YN = 'Y' AND KI.ISDELETE = 'N' AND KI.IS_TEAM_KPI = 'Y' WHERE KS.YMD BETWEEN @iSYMD AND @iEYMD ) TA GROUP BY TA.YMD, TA.THRESHOLD_REF_ID ) TB INNER JOIN BSC_THRESHOLD_CODE TC ON TB.THRESHOLD_REF_ID = TC.THRESHOLD_REF_ID AND TC.DEFAULT_IMG_YN = 'N' ) TD ORDER BY TD.YMD, TD.SEQUENCE"; IDbDataParameter[] paramArray = CreateDataParameters(3); paramArray[0] = CreateDataParameter("@iSYMD", SqlDbType.VarChar); paramArray[0].Value = isymd; paramArray[1] = CreateDataParameter("@iEYMD", SqlDbType.VarChar); paramArray[1].Value = ieymd; paramArray[2] = CreateDataParameter("@iSUM_TYPE", SqlDbType.VarChar); paramArray[2].Value = isum_type; //DataSet ds = DbAgentObj.FillDataSet(query, "DEPT_TYPE_SORT_LIST", null, paramArray, CommandType.Text); DataSet ds = DbAgentObj.FillDataSet(DbAgentHelper.GetQueryStringReplace(sSQL), "KPI_GRADE", null, paramArray, CommandType.Text); return(ds); }
public DataSet Select_DB(object estterm_ref_id , object kpi_ref_id) { string query = @" SELECT @ESTTERM_REF_ID as ESTTERM_REF_ID ,@KPI_REF_ID as KPI_REF_ID ,1 as KPI_TARGET_VERSION_ID ,TA.YMD as YMD ,(SUBSTR(TA.YMD,1,4) || '/' || SUBSTR(TA.YMD,5,2)) as YMD_DESC ,0 as MS_PLAN ,0 as TS_PLAN ,0 as MM_PLAN ,0 as TM_PLAN ,'당초계획' as ORI_VERSION_NAME ,'수정계획(등록안됨)' as MOD_VERSION_NAME ,'Y' as ORI_CHECK_YN ,'N' as MOD_CHECK_YN ,CLOSE_YN as MONTHLY_CLOSE_YN FROM BSC_TERM_DETAIL TA WHERE TA.ESTTERM_REF_ID = @ESTTERM_REF_ID "; query = DbAgentHelper.GetQueryStringReplace(query); IDbDataParameter[] paramArray = CreateDataParameters(2); paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int); paramArray[0].Value = estterm_ref_id; paramArray[1] = CreateDataParameter("@KPI_REF_ID", SqlDbType.Int); paramArray[1].Value = kpi_ref_id; //DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_KPI_TARGET", "PKG_BSC_KPI_TARGET.PROC_SELECT_ALL"), "BSC_KPI_TARGET_GOAL", null, paramArray, CommandType.StoredProcedure); DataSet ds = DbAgentObj.FillDataSet(query, "BSC_KPI_TARGET_GOAL", null, paramArray, CommandType.Text); return(ds); }
public DataSet GetKpiEntGradeAlert(int iestterm_ref_id, string iymd, string isum_type, int ithreshold_ref_id) { string sSQL = @" SELECT TH.VIEW_REF_ID, TH.VIEW_NAME, TH.CNT_KPI, TH.CNT_ALL ,CASE WHEN TH.CNT_ALL = 0 THEN 0 ELSE ROUND((TH.CNT_KPI/TH.CNT_ALL),4)*100 END as COMP_RATE FROM ( SELECT TG.VIEW_REF_ID, TG.VIEW_NAME, TG.VIEW_SEQ, (TG.CNT_KPI*0.1)*10 as CNT_KPI, SUM(TG.CNT_KPI) OVER() as CNT_ALL FROM ( SELECT TE.VIEW_REF_ID, TE.VIEW_NAME, ISNULL(TF.CNT_KPI,0) as CNT_KPI, TE.VIEW_SEQ FROM BSC_VIEW_INFO TE INNER JOIN ( SELECT TB.YMD, TB.VIEW_REF_ID, COUNT(TB.KPI_REF_ID) as CNT_KPI FROM ( SELECT KS.YMD, KS.KPI_REF_ID, TA.VIEW_REF_ID ,CASE WHEN @iSUM_TYPE='MS' THEN KS.THRESHOLD_REF_ID_MS ELSE KS.THRESHOLD_REF_ID_TS END as THRESHOLD_REF_ID FROM BSC_KPI_SCORE KS INNER JOIN ( SELECT MK.ESTTERM_REF_ID, MV.YMD, MK.KPI_REF_ID, MS.VIEW_REF_ID FROM BSC_MAP_KPI MK INNER JOIN BSC_MAP_TERM MV ON MK.ESTTERM_REF_ID = MV.ESTTERM_REF_ID AND MK.EST_DEPT_REF_ID = MV.EST_DEPT_REF_ID AND MK.MAP_VERSION_ID = MV.MAP_VERSION_ID AND MV.YMD = @iYMD INNER JOIN BSC_MAP_STG MS ON MK.ESTTERM_REF_ID = MS.ESTTERM_REF_ID AND MK.EST_DEPT_REF_ID = MS.EST_DEPT_REF_ID AND MK.MAP_VERSION_ID = MS.MAP_VERSION_ID AND MK.STG_REF_ID = MS.STG_REF_ID WHERE MK.ESTTERM_REF_ID = @iESTTERM_REF_ID GROUP BY MK.ESTTERM_REF_ID, MV.YMD, MK.KPI_REF_ID, MS.VIEW_REF_ID ) TA ON KS.ESTTERM_REF_ID = TA.ESTTERM_REF_ID AND KS.YMD = TA.YMD AND KS.KPI_REF_ID = TA.KPI_REF_ID ) TB INNER JOIN BSC_THRESHOLD_CODE TC ON TB.THRESHOLD_REF_ID = TC.THRESHOLD_REF_ID AND TB.THRESHOLD_REF_ID = @iTHRESHOLD_REF_ID GROUP BY TB.YMD, TB.THRESHOLD_REF_ID, TB.VIEW_REF_ID ) TF ON TE.VIEW_REF_ID = TF.VIEW_REF_ID ) TG ) TH ORDER BY TH.VIEW_SEQ"; IDbDataParameter[] paramArray = CreateDataParameters(4); paramArray[0] = CreateDataParameter("@iESTTERM_REF_ID", SqlDbType.Int); paramArray[0].Value = iestterm_ref_id; paramArray[1] = CreateDataParameter("@iYMD", SqlDbType.VarChar); paramArray[1].Value = iymd; paramArray[2] = CreateDataParameter("@iSUM_TYPE", SqlDbType.VarChar); paramArray[2].Value = isum_type; paramArray[3] = CreateDataParameter("@iTHRESHOLD_REF_ID", SqlDbType.Int); paramArray[3].Value = ithreshold_ref_id; //DataSet ds = DbAgentObj.FillDataSet(query, "DEPT_TYPE_SORT_LIST", null, paramArray, CommandType.Text); DataSet ds = DbAgentObj.FillDataSet(DbAgentHelper.GetQueryStringReplace(sSQL), "KPI_GRADE", null, paramArray, CommandType.Text); return(ds); }
/// <summary> /// </summary> /// <param name="iestterm_ref_id"></param> /// <param name="tgt_emp_id"></param> /// <returns></returns> /// public DataTable SelectGraph_Data(object comp_id , object iestterm_ref_id , object iestterm_sub_id , object tgt_emp_id , object is_team_kpi) { string query = @" SELECT A.KPI_REF_ID ,C.KPI_NAME ,ISNULL(A.WEIGHT, 0) AS WEIGHT ,CASE WHEN ISNULL(G.TARGET_TS, 0) = 0 THEN 0 ELSE ISNULL(H.RESULT_TS / G.TARGET_TS * 100, 0) END AS RESULT ,SUM(ISNULL(ISNULL(CAST(ISNULL(D2.SEGMENT1, 0) AS FLOAT), 0) * ISNULL(E.WEIGHT, 0) / 100, 0)) AS POINT FROM BSC_MBO_KPI_WEIGHT A LEFT OUTER JOIN BSC_KPI_INFO B ON B.ESTTERM_REF_ID = A.ESTTERM_REF_ID AND B.KPI_REF_ID = A.KPI_REF_ID LEFT OUTER JOIN BSC_KPI_POOL C ON C.KPI_POOL_REF_ID = B.KPI_POOL_REF_ID LEFT OUTER JOIN BSC_MBO_KPI_SCORE_DT D ON D.COMP_ID = @COMP_ID AND D.EST_ID = '3GA' AND D.ESTTERM_REF_ID = A.ESTTERM_REF_ID AND D.ESTTERM_SUB_ID = @ESTTERM_SUB_ID AND D.KPI_REF_ID = A.KPI_REF_ID AND D.TGT_EMP_ID = A.EMP_REF_ID LEFT OUTER JOIN COM_CODE_INFO D2 ON D2.CATEGORY_CODE = 'BS015' AND D2.ETC_CODE = D.SCORE_GRADE LEFT OUTER JOIN EST_TERM_STEP_EST_MAP E ON E.COMP_ID = D.COMP_ID AND E.EST_ID = D.EST_ID AND E.ESTTERM_STEP_ID = D.ESTTERM_STEP_ID LEFT OUTER JOIN BSC_KPI_TARGET_VERSION F ON F.ESTTERM_REF_ID = D.ESTTERM_REF_ID AND F.KPI_REF_ID = D.KPI_REF_ID AND F.USE_YN = 'Y' LEFT OUTER JOIN BSC_KPI_TARGET G ON G.ESTTERM_REF_ID = F.ESTTERM_REF_ID AND G.KPI_REF_ID = F.KPI_REF_ID AND G.KPI_TARGET_VERSION_ID = F.KPI_TARGET_VERSION_ID AND G.YMD = D.YMD LEFT OUTER JOIN BSC_KPI_RESULT H ON H.ESTTERM_REF_ID = G.ESTTERM_REF_ID AND H.KPI_REF_ID = G.KPI_REF_ID AND H.YMD = G.YMD WHERE A.ESTTERM_REF_ID = @ESTTERM_REF_ID AND A.EMP_REF_ID = @TGT_EMP_ID AND A.USE_YN = 'Y' AND (B.IS_TEAM_KPI = @IS_TEAM_KPI OR @IS_TEAM_KPI ='' ) GROUP BY A.KPI_REF_ID, C.KPI_NAME, ISNULL(A.WEIGHT, 0), CASE WHEN ISNULL(G.TARGET_TS, 0) = 0 THEN 0 ELSE ISNULL(H.RESULT_TS / G.TARGET_TS * 100, 0) END ORDER BY A.KPI_REF_ID "; query = DbAgentHelper.GetQueryStringReplace(query); IDbDataParameter[] paramArray = CreateDataParameters(5); paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int); paramArray[0].Value = iestterm_ref_id; paramArray[1] = CreateDataParameter("@TGT_EMP_ID", SqlDbType.Int); paramArray[1].Value = tgt_emp_id; paramArray[2] = CreateDataParameter("@IS_TEAM_KPI", SqlDbType.VarChar); paramArray[2].Value = is_team_kpi; paramArray[3] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int); paramArray[3].Value = iestterm_ref_id; paramArray[4] = CreateDataParameter("@COMP_ID", SqlDbType.Int); paramArray[4].Value = comp_id; //DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_KPI_TARGET", "PKG_BSC_KPI_TARGET.PROC_SELECT_ALL"), "BSC_KPI_TARGET", null, paramArray, CommandType.StoredProcedure); DataTable dt = DbAgentObj.FillDataSet(query, "BSC_MBO_KPI_WEIGHT", null, paramArray, CommandType.Text).Tables[0]; return(dt); }
public DataSet Select_DB(object estterm_ref_id , object kpi_ref_id , object kpi_target_version_id) { string query = @" SELECT TA.ESTTERM_REF_ID ,TA.KPI_REF_ID ,TA.KPI_TARGET_VERSION_ID ,TA.YMD ,(SUBSTRING(TA.YMD,1,4) + '/' + SUBSTRING(TA.YMD,5,2)) as YMD_DESC ,TA.TARGET_MS as MS_PLAN ,TA.TARGET_TS as TS_PLAN ,ISNULL(TB.TARGET_MS,0) as MM_PLAN ,ISNULL(TB.TARGET_TS,0) as TM_PLAN ,TA.VERSION_NAME as ORI_VERSION_NAME ,ISNULL(TB.VERSION_NAME,'수정계획(등록안됨)') as MOD_VERSION_NAME ,TA.CHECK_YN as ORI_CHECK_YN ,ISNULL(TB.CHECK_YN,'N') as MOD_CHECK_YN ,TC.CLOSE_YN as MONTHLY_CLOSE_YN FROM (SELECT KT.ESTTERM_REF_ID ,KT.KPI_REF_ID ,KT.KPI_TARGET_VERSION_ID ,KT.YMD ,KT.TARGET_MS ,KT.TARGET_TS ,KT.CREATE_DATE ,KT.CREATE_USER ,KT.UPDATE_DATE ,KT.UPDATE_USER ,KV.VERSION_NAME ,KM.CHECK_YN FROM BSC_KPI_TARGET_GOAL KT INNER JOIN BSC_KPI_TARGET_VERSION KV ON KT.ESTTERM_REF_ID = KV.ESTTERM_REF_ID AND KT.KPI_REF_ID = KV.KPI_REF_ID AND KT.KPI_TARGET_VERSION_ID = KV.KPI_TARGET_VERSION_ID INNER JOIN BSC_KPI_TERM KM ON KT.ESTTERM_REF_ID = KM.ESTTERM_REF_ID AND KT.KPI_REF_ID = KM.KPI_REF_ID AND KT.YMD = KM.YMD WHERE KT.ESTTERM_REF_ID = @ESTTERM_REF_ID AND KT.KPI_REF_ID = @KPI_REF_ID AND KT.KPI_TARGET_VERSION_ID = @KPI_TARGET_VERSION_ID) TA LEFT JOIN (SELECT KT.ESTTERM_REF_ID ,KT.KPI_REF_ID ,KT.KPI_TARGET_VERSION_ID ,KT.YMD ,KT.TARGET_MS ,KT.TARGET_TS ,KT.CREATE_DATE ,KT.CREATE_USER ,KT.UPDATE_DATE ,KT.UPDATE_USER ,KV.VERSION_NAME ,KM.CHECK_YN FROM BSC_KPI_TARGET_GOAL KT INNER JOIN BSC_KPI_TARGET_VERSION KV ON KT.ESTTERM_REF_ID = KV.ESTTERM_REF_ID AND KT.KPI_REF_ID = KV.KPI_REF_ID AND KT.KPI_TARGET_VERSION_ID = KV.KPI_TARGET_VERSION_ID INNER JOIN BSC_KPI_TERM KM ON KT.ESTTERM_REF_ID = KM.ESTTERM_REF_ID AND KT.KPI_REF_ID = KM.KPI_REF_ID AND KT.YMD = KM.YMD WHERE KT.ESTTERM_REF_ID = @ESTTERM_REF_ID AND KT.KPI_REF_ID = @KPI_REF_ID AND KV.KPI_TARGET_VERSION_ID > @KPI_TARGET_VERSION_ID AND KV.USE_YN = 'Y' ) TB ON TA.ESTTERM_REF_ID = TB.ESTTERM_REF_ID AND TA.KPI_REF_ID = TB.KPI_REF_ID AND TA.YMD = TB.YMD LEFT JOIN BSC_TERM_DETAIL TC ON TA.ESTTERM_REF_ID = TC.ESTTERM_REF_ID AND TA.YMD = TC.YMD ORDER BY TA.YMD "; query = DbAgentHelper.GetQueryStringReplace(query); IDbDataParameter[] paramArray = CreateDataParameters(3); paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int); paramArray[0].Value = estterm_ref_id; paramArray[1] = CreateDataParameter("@KPI_REF_ID", SqlDbType.Int); paramArray[1].Value = kpi_ref_id; paramArray[2] = CreateDataParameter("@KPI_TARGET_VERSION_ID", SqlDbType.Int); paramArray[2].Value = kpi_target_version_id; //DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_KPI_TARGET", "PKG_BSC_KPI_TARGET.PROC_SELECT_ALL"), "BSC_KPI_TARGET_GOAL", null, paramArray, CommandType.StoredProcedure); DataSet ds = DbAgentObj.FillDataSet(query, "BSC_KPI_TARGET_GOAL", null, paramArray, CommandType.Text); return(ds); }