public DataSet Select(int comp_id, string est_sche_id)
{
string query = @"SELECT COMP_ID
,EST_SCHE_ID
,UP_EST_SCHE_ID
,'' AS UP_EST_SCHE_NAME
,EST_SCHE_NAME
,EST_SCHE_DESC
,SORT_ORDER
,EST_ID
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
FROM EST_SCHE_INFO A
WHERE (COMP_ID = @COMP_ID OR @COMP_ID = 0)
AND (EST_SCHE_ID = @EST_SCHE_ID OR @EST_SCHE_ID ='' )";
IDbDataParameter[] paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@EST_SCHE_ID", SqlDbType.NVarChar, 20);
paramArray[1].Value = est_sche_id;
DataSet ds = DbAgentObj.FillDataSet(query, "SCHEINFO", null, paramArray, CommandType.Text);
return(ds);
}
/// <summary>
/// 외부평가를 포함한 조직 순위구하기
/// </summary>
/// <param name="iestterm_ref_id">평가기간</param>
/// <param name="iymd">평가년월</param>
/// <param name="idept_type">조직유형</param>
/// <param name="isum_type">누계유형</param>
/// <param name="iest_dept_ref_id">조직ID</param>
/// <param name="iselecttype">하위조직포함여부</param>
/// <param name="includeExtScore">외부평가점수 반영여부</param>
/// <param name="itxr_user">조회자</param>
/// <returns></returns>
public DataSet GetEstDeptRankList(int iestterm_ref_id, string iymd, int idept_type, string isum_type
, int iest_dept_ref_id, string iselecttype, bool includeExtScore, int itxr_user)
{
IDbDataParameter[] paramArray = CreateDataParameters(8);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.VarChar);
paramArray[0].Value = "KE";
paramArray[1] = CreateDataParameter("@iESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = iestterm_ref_id;
paramArray[2] = CreateDataParameter("@iYMD", SqlDbType.VarChar);
paramArray[2].Value = iymd;
paramArray[3] = CreateDataParameter("@iDEPT_TYPE", SqlDbType.Int);
paramArray[3].Value = idept_type;
paramArray[4] = CreateDataParameter("@iSUM_TYPE", SqlDbType.VarChar);
paramArray[4].Value = isum_type;
paramArray[5] = CreateDataParameter("@iEST_DEPT_REF_ID", SqlDbType.Int);
paramArray[5].Value = iest_dept_ref_id;
paramArray[6] = CreateDataParameter("@iSELECT_TYPE", SqlDbType.VarChar);
paramArray[6].Value = iselecttype;
paramArray[7] = CreateDataParameter("@iTXR_USER", SqlDbType.Int);
paramArray[7].Value = itxr_user;
DataSet ds = new DataSet();
if (includeExtScore)
{
ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_SCORE_CARD", "PKG_BSC_SCORE_CARD.PROC_SELECT_ESTDEPT_RANK_EXT"), "SELECT_ESTDEPT_RANK", null, paramArray, CommandType.StoredProcedure);
}
else
{
ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_SCORE_CARD", "PKG_BSC_SCORE_CARD.PROC_SELECT_ESTDEPT_RANK"), "SELECT_ESTDEPT_RANK", null, paramArray, CommandType.StoredProcedure);
}
return(ds);
}
public DataSet SelectINFO(int comp_id
, int estterm_ref_id
, int estterm_sub_id)
{
string query = @"SELECT INF.EST_SCHE_ID
, INF.UP_EST_SCHE_ID
, INF.EST_SCHE_NAME
, INF.EST_SCHE_DESC
, INF.EST_ID
, DET.ESTTERM_REF_ID
, DET.ESTTERM_SUB_ID
, DET.START_DATE
, DET.END_DATE
, DET.STATUS_ID
FROM EST_SCHE_INFO INF
JOIN EST_SCHE_DETAIL DET ON (INF.COMP_ID = DET.COMP_ID
AND INF.EST_SCHE_ID = DET.EST_SCHE_ID)
WHERE (DET.COMP_ID = @COMP_ID OR @COMP_ID = 0 )
AND (DET.ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID ='' )
AND (DET.ESTTERM_SUB_ID = @ESTTERM_SUB_ID OR @ESTTERM_SUB_ID ='' )
ORDER BY INF.SORT_ORDER" ;
IDbDataParameter[] paramArray = CreateDataParameters(3);
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("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[2].Value = estterm_sub_id;
DataSet ds = DbAgentObj.FillDataSet(query, "SCHEDETAILINFO", null, paramArray, CommandType.Text);
return(ds);
}
// 2011.08.22 [박효동] kpi실적화면, 부서범위로 가져오기
public DataSet getComDeptListByPermit(int emp_ref_id)
{
string strSQL = @" SELECT A.DEPT_REF_ID
, A.DEPT_NAME
, B.TREE_NODE_TYPE
FROM COM_DEPT_INFO A
JOIN dbo.fn_BSC_EMP_COM_DEPT_BY_EMP_ID(@EMP_REF_ID) B
ON (A.DEPT_REF_ID = B.DEPT_REF_ID)
ORDER BY A.DEPT_REF_ID";
string strORA = @"
";
string query = @"
SELECT A.DEPT_REF_ID
, A.DEPT_NAME
, CASE WHEN A.DEPT_REF_ID IS NULL THEN 0 ELSE 1 END TREE_NODE_TYPE
FROM COM_DEPT_INFO A JOIN
(SELECT DEPT_REF_ID FROM BSC_EMP_COM_DEPT_DETAIL WHERE EMP_REF_ID = @EMP_REF_ID ) B
ON (A.DEPT_REF_ID = B.DEPT_REF_ID)
ORDER BY A.DEPT_REF_ID ";
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int);
paramArray[0].Value = emp_ref_id;
//DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb(strSQL, strORA), "ESTTREE_TBL", null, paramArray, CommandType.Text);
DataSet ds = DbAgentObj.FillDataSet(query, "ESTTREE_TBL", null, paramArray, CommandType.Text);
return(ds);
}
public DataTable SelectEstQuestionDataSelfPointResultPoint(int ESTTERM_REF_ID, int ESTTERM_SUB_ID, int ESTTERM_STEP_ID,
string Q_OBJ_ID, int TGT_EMP_ID, string EST_ID)
{
string qry = @"SELECT Q_SBJ_ID, POINT, EST_EMP_ID FROM EST_QUESTION_DATA
WHERE 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 Q_OBJ_ID = @Q_OBJ_ID AND TGT_EMP_ID = @TGT_EMP_ID";
IDbDataParameter[] paramArray = CreateDataParameters(6);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = ESTTERM_REF_ID;
paramArray[1] = CreateDataParameter("@ESTTERM_SUB_ID", SqlDbType.Int);
paramArray[1].Value = ESTTERM_SUB_ID;
paramArray[2] = CreateDataParameter("@ESTTERM_STEP_ID", SqlDbType.Int);
paramArray[2].Value = ESTTERM_STEP_ID;
paramArray[3] = CreateDataParameter("@Q_OBJ_ID", SqlDbType.VarChar);
paramArray[3].Value = Q_OBJ_ID;
paramArray[4] = CreateDataParameter("@TGT_EMP_ID", SqlDbType.VarChar);
paramArray[4].Value = TGT_EMP_ID;
paramArray[5] = CreateDataParameter("@EST_ID", SqlDbType.VarChar);
paramArray[5].Value = EST_ID;
DataTable dt = DbAgentObj.FillDataSet(qry, "EST_DATA", null, paramArray, CommandType.Text).Tables[0];
return(dt);
}
public DataSet Select(string ctrl_id, int est_dept_ref_id)
{
string query = @"SELECT EDM.CTRL_ID
,EDM.EST_DEPT_REF_ID
,EDI.DEPT_NAME
,EDM.CREATE_DATE
,EDM.CREATE_USER
,EDM.UPDATE_DATE
,EDM.UPDATE_USER
FROM EST_CTRL_EST_DEPT_MAP EDM
JOIN EST_DEPT_INFO EDI ON (EDM.EST_DEPT_REF_ID = EDI.EST_DEPT_REF_ID)
WHERE (EDM.CTRL_ID = @CTRL_ID OR @CTRL_ID ='' )
AND (EDM.EST_DEPT_REF_ID = @EST_DEPT_REF_ID OR @EST_DEPT_REF_ID = 0)" ;
IDbDataParameter[] paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@CTRL_ID", SqlDbType.NVarChar, 20);
paramArray[0].Value = ctrl_id;
paramArray[1] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[1].Value = est_dept_ref_id;
DataSet ds = DbAgentObj.FillDataSet(query, "CTRLESTDEPTMAPGET", null, paramArray, CommandType.Text);
return(ds);
}
public DataTable Select_Soosik(object PRJ_ID)
{
string query = @"
SELECT PRJ_COL_ID
, PRJ_COL_NAME
, PRJ_COL_VALUE
, PRJ_COL_TYPE
, PRJ_COL_WEIGHT
, PRJ_COL_ORD
, PRJ_COL_NOTE
FROM
PMS_COL_INFO
WHERE
PRJ_ID = @PRJ_ID
AND PRJ_COL_CUSTOM_YN='S'
AND PRJ_COL_ID = ''SOOSIK_' + @PRJ_ID
ORDER BY
PRJ_COL_ORD ASC
";
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@PRJ_ID", SqlDbType.NVarChar);
paramArray[0].Value = PRJ_ID;
DataTable dt = DbAgentObj.FillDataSet(query, "SOOSIK", null, paramArray, CommandType.Text).Tables[0];
return(dt);
}
public bool Count(string iprj_code, string iprj_name)
{
IDbDataParameter[] paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.NVarChar, 2);
paramArray[0].Value = "CK";
paramArray[1] = CreateDataParameter("@iPRJ_CODE", SqlDbType.NVarChar, 30);
paramArray[1].Value = iprj_code;
paramArray[2] = CreateDataParameter("@iPRJ_NAME", SqlDbType.NVarChar, 100);
paramArray[2].Value = iprj_name;
DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_PRJ_INFO", "PKG_PRJ_INFO.PROC_CHECK"), "PRJ_INFO", null, paramArray, CommandType.StoredProcedure);
if (ds.Tables[0].Rows.Count > 0)
{
int intRtn = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
return((intRtn > 0) ? true : false);
}
else
{
return(false);
}
//return Convert.ToInt32(DbAgentObj.ExecuteScalar(GetQueryStringByDb("usp_PRJ_INFO", "PKG_PRJ_INFO.PROC_CHECK"), paramArray, CommandType.StoredProcedure));
}
public DataSet GetUserAllList(string iprj_code
, string iprj_name
, int iowner_dept_id
, string iowner_emp_name
, string iprj_type
, object iplan_start_date
, object iplan_end_date
, int itxr_user)
{
IDbDataParameter[] paramArray = CreateDataParameters(9);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.VarChar);
paramArray[0].Value = "UA";
paramArray[1] = CreateDataParameter("@iPRJ_CODE", SqlDbType.VarChar);
paramArray[1].Value = iprj_code;
paramArray[2] = CreateDataParameter("@iPRJ_NAME", SqlDbType.VarChar);
paramArray[2].Value = iprj_name;
paramArray[3] = CreateDataParameter("@iOWNER_DEPT_ID", SqlDbType.Int);
paramArray[3].Value = iowner_dept_id;
paramArray[4] = CreateDataParameter("@iOWNER_EMP_NAME", SqlDbType.VarChar);
paramArray[4].Value = iowner_emp_name;
paramArray[5] = CreateDataParameter("@iPRJ_TYPE", SqlDbType.VarChar);
paramArray[5].Value = iprj_type;
paramArray[6] = CreateDataParameter("@iPLAN_START_DATE", SqlDbType.DateTime);
paramArray[6].Value = iplan_start_date;
paramArray[7] = CreateDataParameter("@iPLAN_END_DATE", SqlDbType.DateTime);
paramArray[7].Value = iplan_end_date;
paramArray[8] = CreateDataParameter("@iTXR_USER", SqlDbType.Int);
paramArray[8].Value = itxr_user;
DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_PRJ_INFO", "PKG_PRJ_INFO.PROC_SELECT_USER_ALL"), "PRJ_INFO", null, paramArray, CommandType.StoredProcedure);
return(ds);
}
public DataSet Select_DB(object estterm_ref_id
, object kpi_ref_id
, string ymd)
{
string strQuery = @"
SELECT ESTTERM_REF_ID
,KPI_REF_ID
,YMD
,RESULT_MS
,RESULT_TS
FROM BSC_KPI_RESULT
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND KPI_REF_ID = @KPI_REF_ID
AND YMD = @YMD
ORDER BY KPI_REF_ID DESC
";
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.VarChar);
paramArray[1].Value = kpi_ref_id;
paramArray[2] = CreateDataParameter("@YMD", SqlDbType.VarChar, 20);
paramArray[2].Value = ymd;
DataSet ds = DbAgentObj.FillDataSet(strQuery, "BSC_KPI_INFO", null, paramArray, CommandType.Text);
return(ds);
}
public DataSet GetList(string iprj_code
, string iprj_name
, int iowner_dept_id
, string iowner_emp_name
, int iowner_emp_id
, string iprj_type
, int iprj_year
)
{
IDbDataParameter[] paramArray = CreateDataParameters(8);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.VarChar);
paramArray[0].Value = "SL";
paramArray[1] = CreateDataParameter("@iPRJ_CODE", SqlDbType.VarChar);
paramArray[1].Value = iprj_code;
paramArray[2] = CreateDataParameter("@iPRJ_NAME", SqlDbType.VarChar);
paramArray[2].Value = iprj_name;
paramArray[3] = CreateDataParameter("@iOWNER_DEPT_ID", SqlDbType.Int);
paramArray[3].Value = iowner_dept_id;
paramArray[4] = CreateDataParameter("@iOWNER_EMP_NAME", SqlDbType.VarChar);
paramArray[4].Value = iowner_emp_name;
paramArray[5] = CreateDataParameter("@iOWNER_EMP_ID", SqlDbType.Int);
paramArray[5].Value = iowner_emp_id;
paramArray[6] = CreateDataParameter("@iPRJ_TYPE", SqlDbType.VarChar);
paramArray[6].Value = iprj_type;
paramArray[7] = CreateDataParameter("@iPRJ_YEAR", SqlDbType.VarChar);
paramArray[7].Value = iprj_year;
DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_PRJ_INFO", "PKG_PRJ_INFO.PROC_SELECT_LIST"), "PRJ_INFO", null, paramArray, CommandType.StoredProcedure);
return(ds);
}
public DataSet Select(int comp_id, int estterm_ref_id, int dept_ref_id, string est_id)
{
string query = @"SELECT COMP_ID
,ESTTERM_REF_ID
,DEPT_REF_ID
,EST_ID
,SCALE_ID
,EST_GRP_ID
,WEIGHT
,WEIGHT_TYPE
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
FROM EST_DEPT_EST_DETAIL
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 ='' )" ;
IDbDataParameter[] paramArray = CreateDataParameters(4);
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, 12);
paramArray[3].Value = est_id;
DataSet ds = DbAgentObj.FillDataSet(query, "DEPTESTDETAILGET", null, paramArray, CommandType.Text);
return(ds);
}
public DataTable GetDeptInfo_DepthRow()
{
DataTable dt = new DataTable();
string qry = "SELECT * FROM EST_DEPT_INFO";
return(DbAgentObj.FillDataSet(qry, "depth", null, null, CommandType.Text).Tables[0]);
}
public DataTable GetDeptInfo_Depth()
{
DataTable dt = new DataTable();
string qry = "SELECT DISTINCT DEPT_LEVEL FROM EST_DEPT_INFO ORDER BY DEPT_LEVEL";
return(DbAgentObj.FillDataSet(qry, "depth", null, null, CommandType.Text).Tables[0]);
}
/// <summary>
/// 원래 코드
/// </summary>
public DataSet SelectSignalListPerKpiWithStep(int ikpi_ref_id, string ithreshold_level)
{
string strQuery = @"
SELECT TC.THRESHOLD_ENAME as SIGNAL
,TC.IMAGE_FILE_NAME as IMAG_PATH
,TC.THRESHOLD_KNAME as THRS_DESC
,0 as SET_MIN_VALUE
,0.00 as ACHIEVE_RATE
,TS.POINT as POINT
,TS.BASE_LINE_YN as BASE_LINE_YN
,@iKPI_REF_ID as KPI_REF_ID
,TC.THRESHOLD_REF_ID as THRESHOLD_REF_ID
,TS.THRESHOLD_LEVEL as THRESHOLD_LEVEL
FROM BSC_THRESHOLD_CODE TC
LEFT JOIN BSC_THRESHOLD_STEP TS
ON TC.THRESHOLD_REF_ID = TS.THRESHOLD_REF_ID
WHERE TS.THRESHOLD_LEVEL = @iTHRESHOLD_LEVEL
ORDER BY TS.SEQUENCE
";
IDbDataParameter[] paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("@iKPI_REF_ID", SqlDbType.Int);
paramArray[0].Value = ikpi_ref_id;
paramArray[1] = CreateDataParameter("@iTHRESHOLD_LEVEL", SqlDbType.VarChar);
paramArray[1].Value = ithreshold_level;
DataSet ds = DbAgentObj.FillDataSet(strQuery, "BSC_KPI_THRESHOLD_INFO", null, paramArray, CommandType.Text);
return(ds);
}
public DataTable GetMboScore(object estterm_ref_id, object ymd, object loginid)
{
string query = @"
SELECT A.EMP_NAME, B.DEPT_REF_ID
,(SELECT FN_BSC_MBO_SCORE(@ESTTERM_REF_ID,@YMD, A.EMP_REF_ID, 'MS') FROM DUAL) AS MS_SCORE
,(SELECT FN_BSC_MBO_SCORE(@ESTTERM_REF_ID,@YMD, A.EMP_REF_ID, 'TS') FROM DUAL) AS TS_SCORE
FROM COM_EMP_INFO A
LEFT JOIN REL_DEPT_EMP B ON B.EMP_REF_ID = A.EMP_REF_ID
LEFT JOIN COM_DEPT_INFO C ON B.DEPT_REF_ID = C.DEPT_REF_ID
WHERE A.LOGINID = @LOGINID
";
IDbDataParameter[] paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = estterm_ref_id;
paramArray[1] = CreateDataParameter("@YMD", SqlDbType.VarChar);
paramArray[1].Value = ymd;
paramArray[2] = CreateDataParameter("@LOGINID", SqlDbType.VarChar);
paramArray[2].Value = loginid;
DataTable dt = DbAgentObj.FillDataSet(query, "BSC_INTRO_MBO_SCORE", null, paramArray, CommandType.Text).Tables[0];
return(dt);
}
/// <summary>
/// 지표별 등급구간조회 데이터 확인
/// </summary>
/// <param name="iestterm_ref_id"></param>
/// <param name="ikpi_ref_id"></param>
/// <param name="ithreshold_level"></param>
/// <returns></returns>
public DataSet SelectkpiThresholdInfo(int iestterm_ref_id
, int ikpi_ref_id
, string ithreshold_level)
{
string strQuery = @"
SELECT ESTTERM_REF_ID
, KPI_REF_ID
, THRESHOLD_REF_ID
, THRESHOLD_LEVEL
, SET_MIN_VALUE
, SET_MAX_VALUE
, ACHIEVE_RATE
, CREATE_DATE
, CREATE_USER
, UPDATE_DATE
, UPDATE_USER
FROM BSC_KPI_THRESHOLD_INFO WHERE ESTTERM_REF_ID = @iESTTERM_REF_ID
AND KPI_REF_ID = @iKPI_REF_ID
AND THRESHOLD_LEVEL = @iTHRESHOLD_LEVEL
";
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("@iTHRESHOLD_LEVEL", SqlDbType.VarChar);
paramArray[2].Value = ithreshold_level;
DataSet ds = DbAgentObj.FillDataSet(strQuery, "BSC_KPI_THRESHOLD_INFO", null, paramArray, CommandType.Text);
return(ds);
}
public DataSet GetKpiListExceptMyKpi(int iestterm_ref_id, int iemp_ref_id, string iresult_input_type, string ikpi_code, string ikpi_name, string iuse_yn, string ichampion_name, int iest_dept_ref_id, string ikpi_group_ref_id, string iis_team_kpi, int itxr_user)
{
IDbDataParameter[] paramArray = CreateDataParameters(12);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.VarChar);
paramArray[0].Value = "KT";
paramArray[1] = CreateDataParameter("@iESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = iestterm_ref_id;
paramArray[2] = CreateDataParameter("@iEMP_REF_ID", SqlDbType.Int);
paramArray[2].Value = iemp_ref_id;
paramArray[3] = CreateDataParameter("@iRESULT_INPUT_TYPE", SqlDbType.VarChar);
paramArray[3].Value = iresult_input_type;
paramArray[4] = CreateDataParameter("@iKPI_CODE", SqlDbType.VarChar);
paramArray[4].Value = ikpi_code;
paramArray[5] = CreateDataParameter("@iKPI_NAME", SqlDbType.VarChar);
paramArray[5].Value = ikpi_name;
paramArray[6] = CreateDataParameter("@iUSE_YN", SqlDbType.VarChar);
paramArray[6].Value = iuse_yn;
paramArray[7] = CreateDataParameter("@iCHAMPION_NAME", SqlDbType.VarChar);
paramArray[7].Value = ichampion_name;
paramArray[8] = CreateDataParameter("@iEST_DEPT_REF_ID", SqlDbType.Int);
paramArray[8].Value = iest_dept_ref_id;
paramArray[9] = CreateDataParameter("@iKPI_GROUP_REF_ID", SqlDbType.VarChar);
paramArray[9].Value = ikpi_group_ref_id;
paramArray[10] = CreateDataParameter("@iIS_TEAM_KPI", SqlDbType.VarChar);
paramArray[10].Value = iis_team_kpi;
paramArray[11] = CreateDataParameter("@iTXR_USER", SqlDbType.Int);
paramArray[11].Value = itxr_user;
DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_MBO_KPI_WEIGHT", "PKG_BSC_MBO_KPI_WEIGHT.PROC_SELECT_KPI_TARGET"), "BSC_MBO_KPI_WEIGHT", null, paramArray, CommandType.StoredProcedure);
return(ds);
}
/// <summary>
/// 실적상세데이터 조회
/// </summary>
/// <param name="ikpi_ref_id"></param>
/// <param name="sCurYMD"></param>
/// <returns></returns>
public DataSet GetInterfaceData(int ikpi_ref_id, string sCurYMD, out string sMsg, out bool bIsSuccess)
{
Dac_Bsc_Interface_Kpi_Query objQry = new Dac_Bsc_Interface_Kpi_Query(ikpi_ref_id, "");
DataSet rDs = new DataSet();
sMsg = "";
string sQuery = "";
bIsSuccess = false;
try
{
sQuery = objQry.IQuery_Al;
sQuery = sQuery.Replace(QueryOperator.ParamCurrYmd, "'" + sCurYMD + "'");
sQuery = sQuery.Replace(QueryOperator.ParamFirstYmd, "'" + sCurYMD + "'");
rDs = DbAgentObj.FillDataSet(sQuery, "INTERFACE_DATA");
sMsg = "Success";
bIsSuccess = true;
}
catch (Exception e)
{
sMsg = e.Message;
bIsSuccess = false;
}
return(rDs);
}
public DataSet GetKpiAnalysis(int iestterm_ref_id, int iemp_ref_id, string iymd, string isum_type, string iresult_input_type, string ikpi_code, string ikpi_name, string iuse_yn, string ichampion_name, string ikpi_group_ref_id)
{
IDbDataParameter[] paramArray = CreateDataParameters(11);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.VarChar);
paramArray[0].Value = "KS";
paramArray[1] = CreateDataParameter("@iESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = iestterm_ref_id;
paramArray[2] = CreateDataParameter("@iEMP_REF_ID", SqlDbType.Int);
paramArray[2].Value = iemp_ref_id;
paramArray[3] = CreateDataParameter("@iYMD", SqlDbType.VarChar);
paramArray[3].Value = iymd;
paramArray[4] = CreateDataParameter("@iSUM_TYPE", SqlDbType.VarChar);
paramArray[4].Value = isum_type;
paramArray[5] = CreateDataParameter("@iRESULT_INPUT_TYPE", SqlDbType.VarChar);
paramArray[5].Value = iresult_input_type;
paramArray[6] = CreateDataParameter("@iKPI_CODE", SqlDbType.VarChar);
paramArray[6].Value = ikpi_code;
paramArray[7] = CreateDataParameter("@iKPI_NAME", SqlDbType.VarChar);
paramArray[7].Value = ikpi_name;
paramArray[8] = CreateDataParameter("@iUSE_YN", SqlDbType.VarChar);
paramArray[8].Value = iuse_yn;
paramArray[9] = CreateDataParameter("@iCHAMPION_NAME", SqlDbType.VarChar);
paramArray[9].Value = ichampion_name;
paramArray[10] = CreateDataParameter("@iKPI_GROUP_REF_ID", SqlDbType.VarChar);
paramArray[10].Value = ikpi_group_ref_id;
DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_MBO_KPI_WEIGHT", "PKG_BSC_MBO_KPI_WEIGHT.PROC_SELECT_MBO_KPI_ANALYSIS"), "BSC_MBO_KPI_WEIGHT", null, paramArray, CommandType.StoredProcedure);
return(ds);
}
public DataSet GetPersonalScoreRankAll(int iestterm_ref_id, string iymd, int idept_type, string isum_type, int iemp_ref_id, int idept_ref_id)
{
IDbDataParameter[] paramArray = CreateDataParameters(8);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.VarChar);
paramArray[0].Value = "RK";
paramArray[1] = CreateDataParameter("@iESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = iestterm_ref_id;
paramArray[2] = CreateDataParameter("@iYMD", SqlDbType.VarChar);
paramArray[2].Value = iymd;
paramArray[3] = CreateDataParameter("@iDEPT_TYPE", SqlDbType.Int);
paramArray[3].Value = idept_type;
paramArray[4] = CreateDataParameter("@iSUM_TYPE", SqlDbType.VarChar);
paramArray[4].Value = isum_type;
paramArray[5] = CreateDataParameter("@iEMP_REF_ID", SqlDbType.Int);
paramArray[5].Value = iemp_ref_id;
paramArray[6] = CreateDataParameter("@iDEPT_REF_ID", SqlDbType.Int);
paramArray[6].Value = idept_ref_id;
paramArray[7] = CreateDataParameter("@iHAVE_CHILD_DEPT", SqlDbType.VarChar);
paramArray[7].Value = "Y";
DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_SCORE_CARD_PERSONAL", "PKG_BSC_SCORE_CARD_PERSONAL.PROC_SELECT_PERSONAL_RANK"), "BSC_SCORE_CARD_PERSONAL", null, paramArray, CommandType.StoredProcedure);
return(ds);
}
public DataSet GetQueryMappingStatus(int iestterm_ref_id, string idicode, string ikpi_code, string ikpi_name, string ichampion_name, string iis_team_kpi
, string iuse_yn, string iis_set_query)
{
IDbDataParameter[] paramArray = CreateDataParameters(9);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.VarChar);
paramArray[0].Value = "SM";
paramArray[1] = CreateDataParameter("@iESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = iestterm_ref_id;
paramArray[2] = CreateDataParameter("@iDICODE", SqlDbType.VarChar);
paramArray[2].Value = idicode;
paramArray[3] = CreateDataParameter("@iKPI_CODE", SqlDbType.VarChar);
paramArray[3].Value = ikpi_code;
paramArray[4] = CreateDataParameter("@iKPI_NAME", SqlDbType.VarChar);
paramArray[4].Value = ikpi_name;
paramArray[5] = CreateDataParameter("@iCHAMPION_NAME", SqlDbType.VarChar);
paramArray[5].Value = ichampion_name;
paramArray[6] = CreateDataParameter("@iIS_TEAM_KPI", SqlDbType.VarChar);
paramArray[6].Value = iis_team_kpi;
paramArray[7] = CreateDataParameter("@iUSE_YN", SqlDbType.VarChar);
paramArray[7].Value = iuse_yn;
paramArray[8] = CreateDataParameter("@iIS_SET_QUERY", SqlDbType.VarChar);
paramArray[8].Value = iis_set_query;
DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_INTERFACE_KPI_QUERY", "PKG_BSC_INTERFACE_KPI_QUERY.PROC_SELECT_QRY_MAPPING"), "BSC_INTERFACE_KPI_QUERY", null, paramArray, CommandType.StoredProcedure);
return(ds);
}
public DataSet GetEstDeptByStgMap(int estterm_ref_id)
{
string query = @"
SELECT ED.DEPT_NAME
, TA.EST_DEPT_REF_ID
FROM (SELECT ESTTERM_REF_ID
, EST_DEPT_REF_ID
FROM REL_STG_MAP
GROUP BY ESTTERM_REF_ID
, EST_DEPT_REF_ID
) TA
JOIN EST_DEPT_INFO ED ON TA.ESTTERM_REF_ID = ED.ESTTERM_REF_ID
AND TA.EST_DEPT_REF_ID = ED.EST_DEPT_REF_ID
AND TA.ESTTERM_REF_ID = @ESTTERM_REF_ID
ORDER BY TA.EST_DEPT_REF_ID";
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = estterm_ref_id;
DataSet ds = DbAgentObj.FillDataSet(query, "DataSet", null, paramArray, CommandType.Text);
return(ds);
}
internal protected DataSet GetMBOList(object estterm_ref_id, object kpi_ref_id)
{
string strQuery = @"
SELECT ISNULL(B.ESTTERM_REF_ID, @ESTTERM_REF_ID) AS ESTTERM_REF_ID
, ISNULL(B.KPI_REF_ID, @KPI_REF_ID) AS KPI_REF_ID
, A.YMD
, SUBSTRING(A.YMD, 1, 4) + '/' + SUBSTRING(A.YMD, 5, 2) AS YMD_DESC
, ISNULL(B.CHECK_YN, 'N') AS CHECK_YN
, D.TARGET_MS AS MS_PLAN
, D.TARGET_TS AS TS_PLAN
, A.CLOSE_YN
FROM BSC_TERM_DETAIL A
LEFT OUTER JOIN BSC_KPI_TERM B ON B.ESTTERM_REF_ID = A.ESTTERM_REF_ID AND B.KPI_REF_ID = @KPI_REF_ID AND B.YMD = A.YMD
LEFT OUTER JOIN BSC_KPI_TARGET_VERSION C ON C.ESTTERM_REF_ID = B.ESTTERM_REF_ID AND C.KPI_REF_ID = B.KPI_REF_ID AND C.USE_YN = 'Y'
LEFT OUTER JOIN BSC_KPI_TARGET D ON D.ESTTERM_REF_ID = C.ESTTERM_REF_ID AND D.KPI_REF_ID = C.KPI_REF_ID AND D.YMD = B.YMD AND D.KPI_TARGET_VERSION_ID = C.KPI_TARGET_VERSION_ID
WHERE A.ESTTERM_REF_ID = @ESTTERM_REF_ID
ORDER BY A.YMD
";
IDbDataParameter[] paramArray = CreateDataParameters(2);
paramArray[0] = CreateDataParameter("ESTTERM_REF_ID", SqlDbType.Int);
paramArray[1] = CreateDataParameter("KPI_REF_ID", SqlDbType.Int);
paramArray[0].Value = estterm_ref_id;
paramArray[1].Value = kpi_ref_id;
DataSet ds = DbAgentObj.FillDataSet(strQuery, "BSC_KPI_TERM_AND_TARGET", null, paramArray, CommandType.Text);
return(ds);
}
public DataSet Select(int comp_id, string scale_id, string use_yn)
{
string query = @"SELECT COMP_ID
,SCALE_ID
,SCALE_NAME
,USE_YN
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
FROM EST_SCALE_INFO
WHERE (COMP_ID = @COMP_ID OR @COMP_ID = 0)
AND (SCALE_ID = @SCALE_ID OR @SCALE_ID ='' )
AND (USE_YN = @USE_YN OR @USE_YN ='' )";
IDbDataParameter[] paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int);
paramArray[0].Value = comp_id;
paramArray[1] = CreateDataParameter("@SCALE_ID", SqlDbType.NVarChar);
paramArray[1].Value = scale_id;
paramArray[2] = CreateDataParameter("@USE_YN", SqlDbType.NVarChar);
paramArray[2].Value = use_yn;
DataSet ds = DbAgentObj.FillDataSet(query, "SCALEINFOGET", null, paramArray, CommandType.Text);
return(ds);
}
public string GetNavigateMonth(int iestterm_ref_id, int ikpi_ref_id, string iymd, gNavigator iNavigator)
{
string sNavigator = "";
switch (iNavigator)
{
case gNavigator.FIRST:
sNavigator = "F";
break;
case gNavigator.LAST:
sNavigator = "L";
break;
case gNavigator.NEXT:
sNavigator = "N";
break;
case gNavigator.PREVIOUS:
sNavigator = "P";
break;
default:
sNavigator = "C";
break;
}
IDbDataParameter[] paramArray = CreateDataParameters(5);
paramArray[0] = CreateDataParameter("@iTYPE", SqlDbType.VarChar);
paramArray[0].Value = "GM";
paramArray[1] = CreateDataParameter("@iESTTERM_REF_ID", SqlDbType.Int);
paramArray[1].Value = iestterm_ref_id;
paramArray[2] = CreateDataParameter("@iKPI_REF_ID", SqlDbType.Int);
paramArray[2].Value = ikpi_ref_id;
paramArray[3] = CreateDataParameter("@iYMD", SqlDbType.VarChar);
paramArray[3].Value = iymd;
paramArray[4] = CreateDataParameter("@iNAVIGATOR", SqlDbType.VarChar);
paramArray[4].Value = sNavigator;
DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb("usp_BSC_KPI_TERM", "PKG_BSC_KPI_TERM.PROC_SELECT_EST_MONTH"), "BSC_KPI_TERM", null, paramArray, CommandType.StoredProcedure);
if (ds.Tables.Count < 1)
{
return("-");
}
else
{
if (ds.Tables[0].Rows.Count > 0)
{
return(ds.Tables[0].Rows[0][0].ToString());
}
else
{
return("-");
}
}
//return Convert.ToString(DbAgentObj.ExecuteScalar(GetQueryStringByDb("usp_BSC_KPI_TERM", "PKG_BSC_KPI_TERM.PROC_SELECT_EST_MONTH"), paramArray, CommandType.StoredProcedure));
}
public DataSet Select(int emp_ref_id
, string pos_grp_id
, DateTime start_date)
{
string query = @"SELECT EMP_REF_ID
,ESTTERM_REF_ID
,ESTTERM_SUB_ID
,POS_GRP_ID
,START_DATE
,END_DATE
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
FROM EST_POS_GRP_EMP
WHERE (EMP_REF_ID = @EMP_REF_ID OR @EMP_REF_ID = 0)
AND (POS_GRP_ID = @POS_GRP_ID OR @POS_GRP_ID ='' )
AND START_DATE = @START_DATE";
IDbDataParameter[] paramArray = CreateDataParameters(3);
paramArray[0] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int);
paramArray[0].Value = emp_ref_id;
paramArray[1] = CreateDataParameter("@POS_GRP_ID", SqlDbType.NVarChar, 12);
paramArray[1].Value = pos_grp_id;
paramArray[2] = CreateDataParameter("@START_DATE", SqlDbType.DateTime);
paramArray[2].Value = start_date;
DataSet ds = DbAgentObj.FillDataSet(query, "POSGRPEMPGET", 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);
}
public DataSet Select(string pos_cls_id)
{
string s_query = @"SELECT POS_CLS_ID
,POS_CLS_NAME
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
FROM EST_POSITION_CLS
WHERE (POS_CLS_ID = @POS_CLS_ID OR @POS_CLS_ID ='' )";
// string o_query = @"SELECT POS_CLS_ID
// ,POS_CLS_NAME
// ,CREATE_DATE
// ,CREATE_USER
// ,UPDATE_DATE
// ,UPDATE_USER
// FROM EST_POSITION_CLS
// WHERE (POS_CLS_ID = @POS_CLS_ID OR POS_CLS_ID LIKE @POS_CLS_ID || '%')";
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@POS_CLS_ID", SqlDbType.NVarChar, 12);
paramArray[0].Value = pos_cls_id;
DataSet ds = DbAgentObj.FillDataSet(s_query, "POSITIONCLASSGET", null, paramArray, CommandType.Text);
return(ds);
//DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb(s_query, o_query), "POSITIONCLASSGET", null, paramArray, CommandType.Text);
//return ds;
}
public DataSet Select(string q_style_id)
{
string query = @"SELECT Q_STYLE_ID
,Q_STYLE_NAME
,Q_STYLE_DESC
,Q_STYLE_PAGE_NAME
,Q_YN
,SORT_ORDER
,CREATE_DATE
,CREATE_USER
,UPDATE_DATE
,UPDATE_USER
FROM EST_QUESTION_PAGE_STYLE
WHERE (Q_STYLE_ID = @Q_STYLE_ID OR @Q_STYLE_ID ='' )
ORDER BY SORT_ORDER";
IDbDataParameter[] paramArray = CreateDataParameters(1);
paramArray[0] = CreateDataParameter("@Q_STYLE_ID", SqlDbType.NVarChar);
paramArray[0].Value = q_style_id;
DataSet ds = DbAgentObj.FillDataSet(query, "LAYOUTTYPE", null, paramArray, CommandType.Text);
return(ds);
}
