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