/// <summary>
/// 최상위 객체의 정보를 가지고 온다. (UP_STG_ID 이 NULL 인 개체)
/// </summary>
/// <returns></returns>
public DataSet GetTopParents(int isContainType)
{
// -1->최상위부모가아님, 0->전체, 1->자식이존재함, 2->자식이존재하지않음
string query = @"
SELECT * FROM (
SELECT A.ESTTERM_REF_ID
, A.EST_DEPT_REF_ID
, A.STG_REF_ID
, NULL AS UP_STG_ID
, A.VIEW_REF_ID AS STG_MAP_TYPE
, B.VIEW_SEQ
, A.SORT_ORDER
, CASE WHEN
(SELECT COUNT(*)
FROM BSC_MAP_STG_PARENT
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID
AND UP_STG_REF_ID = A.STG_REF_ID) > 0
THEN
1
ELSE
2
END AS ISCONTAIN
FROM BSC_MAP_STG A
JOIN BSC_VIEW_INFO B ON A.VIEW_REF_ID = B.VIEW_REF_ID
WHERE A.ESTTERM_REF_ID = @ESTTERM_REF_ID
AND A.EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND A.MAP_VERSION_ID = @MAP_VERSION_ID
AND A.STG_REF_ID NOT IN (SELECT STG_REF_ID
FROM BSC_MAP_STG_PARENT
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID)
) X
WHERE (X.ISCONTAIN = @ISCONTAIN OR @ISCONTAIN = 0)
ORDER BY X.VIEW_SEQ, X.SORT_ORDER";
IDbDataParameter[] paramArray = CreateDataParameters(4);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = _estterm_ref_id;
paramArray[1] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[1].Value = _est_dept_ref_id;
paramArray[2] = CreateDataParameter("@ISCONTAIN", SqlDbType.Int);
paramArray[2].Value = isContainType;
paramArray[3] = CreateDataParameter("@MAP_VERSION_ID", SqlDbType.Int);
paramArray[3].Value = StgMaps_Data.GetMapVersionID(_estterm_ref_id, _est_dept_ref_id, _est_month, _map_version_id);
DataSet ds = DbAgentObj.Fill(query, paramArray, CommandType.Text);
return(ds);
}
public double GetTotalPointByStgMapType(int stg_map_type)
{
string s_query = @"SELECT A.EST_DEPT_REF_ID
, ISNULL(SUM(A.WEIGHT), 0) AS WEIGHT
FROM BSC_MAP_KPI A
JOIN BSC_MAP_STG B ON (A.ESTTERM_REF_ID = B.ESTTERM_REF_ID
AND A.EST_DEPT_REF_ID = B.EST_DEPT_REF_ID
AND A.STG_REF_ID = B.STG_REF_ID
AND A.MAP_VERSION_ID = B.MAP_VERSION_ID)
WHERE A.ESTTERM_REF_ID = @ESTTERM_REF_ID
AND A.EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND B.VIEW_REF_ID = @STG_MAP_TYPE
AND B.MAP_VERSION_ID = @MAP_VERSION_ID
GROUP BY A.EST_DEPT_REF_ID" ;
string o_query = @"SELECT A.EST_DEPT_REF_ID
, NVL(SUM(A.WEIGHT), 0) AS WEIGHT
FROM BSC_MAP_KPI A
JOIN BSC_MAP_STG B ON (A.ESTTERM_REF_ID = B.ESTTERM_REF_ID
AND A.EST_DEPT_REF_ID = B.EST_DEPT_REF_ID
AND A.STG_REF_ID = B.STG_REF_ID
AND A.MAP_VERSION_ID = B.MAP_VERSION_ID)
WHERE A.ESTTERM_REF_ID = @ESTTERM_REF_ID
AND A.EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND B.VIEW_REF_ID = @STG_MAP_TYPE
AND B.MAP_VERSION_ID = @MAP_VERSION_ID
GROUP BY A.EST_DEPT_REF_ID" ;
IDbDataParameter[] paramArray = CreateDataParameters(4);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = _estterm_ref_id;
paramArray[1] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[1].Value = _est_dept_ref_id;
paramArray[2] = CreateDataParameter("@STG_MAP_TYPE", SqlDbType.Int);
paramArray[2].Value = stg_map_type;
paramArray[3] = CreateDataParameter("@MAP_VERSION_ID", SqlDbType.Int);
paramArray[3].Value = StgMaps_Data.GetMapVersionID(_estterm_ref_id, _est_dept_ref_id, _est_month, _map_version_id);
DataSet ds = DbAgentObj.FillDataSet(GetQueryStringByDb(s_query, o_query), "DataSet", null, paramArray, CommandType.Text);
if (ds.Tables[0].Rows.Count > 0)
{
return(Convert.ToDouble(ds.Tables[0].Rows[0]["WEIGHT"].ToString()));
}
return(0);
}
public DataSet GetKpiList(int stg_map_id)
{
string query = @"SELECT A.ESTTERM_REF_ID
, A.KPI_REF_ID
, A.KPI_CODE
, C.KPI_NAME
--, A.EST_DEPT_ID
, A.CHAMPION_EMP_ID
, A.USE_YN
, A.MEASUREMENT_PURPOSE
, A.RESULT_INPUT_TYPE
, A.CALC_FORM_MS
, A.CALC_FORM_TS
, A.WORD_DEFINITION
, A.RELATED_ISSUE
--, A.CHECK_METHOD
, A.RESULT_MEASUREMENT_STEP
, A.RESULT_TS_CALC_TYPE
, A.RESULT_ACHIEVEMENT_TYPE
--, A.NUJUK_USE
, A.RESULT_TERM_TYPE
, A.MEASUREMENT_GRADE_TYPE
, A.DATA_GETHERING_METHOD
, A.GRAPH_TYPE
, A.UNIT_TYPE_REF_ID
, A.APP_REF_ID
, A.EXCEL_DNUSE
, A.ADD_FILE
, A.APPROVAL_STATUS
, A.MEASUREMENT_EMP_ID
, A.APPROVAL_EMP_ID
, A.KPI_POOL_REF_ID
--, A.KPI_TARGET_VERSION_ID
, A.CREATE_DATE
, A.CREATE_USER
, A.UPDATE_DATE
, A.UPDATE_USER
, B.STG_REF_ID
, ISNULL(B.MAP_KPI_TYPE, '') AS MAP_KPI_TYPE
, ISNULL(D.CODE_NAME, '') AS MAP_KPI_TYPE_NAME
, ISNULL(D.CODE_DESC, '') AS MAP_KPI_TYPE_DESC
FROM BSC_KPI_INFO A
JOIN BSC_MAP_KPI B ON (A.KPI_REF_ID = B.KPI_REF_ID
AND A.ESTTERM_REF_ID = B.ESTTERM_REF_ID)
JOIN BSC_KPI_POOL C ON (A.KPI_POOL_REF_ID = C.KPI_POOL_REF_ID)
LEFT OUTER JOIN COM_CODE_INFO D ON D.CATEGORY_CODE='BS0017' AND D.ETC_CODE = B.MAP_KPI_TYPE
WHERE A.ESTTERM_REF_ID = @ESTTERM_REF_ID
AND B.EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND B.MAP_VERSION_ID = @MAP_VERSION_ID
AND B.STG_REF_ID = @STG_REF_ID
ORDER BY B.SORT_ORDER, C.KPI_NAME ";
IDbDataParameter[] paramArray = CreateDataParameters(4);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = _estterm_ref_id;
paramArray[1] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[1].Value = _est_dept_ref_id;
paramArray[2] = CreateDataParameter("@STG_REF_ID", SqlDbType.Int);
paramArray[2].Value = stg_map_id;
paramArray[3] = CreateDataParameter("@MAP_VERSION_ID", SqlDbType.Int);
paramArray[3].Value = StgMaps_Data.GetMapVersionID(_estterm_ref_id, _est_dept_ref_id, _est_month, _map_version_id);
DataSet ds = DbAgentObj.FillDataSet(query, "DataSet", null, paramArray, CommandType.Text);
return(ds);
}
public bool IsAllRelationed(int stg_map_id)
{
string s_query = @"
SELECT CASE WHEN TA.ALL_STG = 0
THEN 'F'
ELSE
CASE WHEN (TA.ALL_STG - TB.UP_STG) = 0
THEN 'T'
ELSE 'F'
END
END AS RTN
FROM (
SELECT COUNT(STG_REF_ID) AS ALL_STG
FROM BSC_MAP_STG
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID
AND VIEW_REF_ID = (SELECT TOP 1 TB.VIEW_REF_ID
FROM BSC_MAP_STG TA
LEFT JOIN BSC_VIEW_INFO TB
ON TA.VIEW_REF_ID = TB.VIEW_REF_ID
WHERE TA.ESTTERM_REF_ID = @ESTTERM_REF_ID
AND TA.EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND TA.MAP_VERSION_ID = @MAP_VERSION_ID
AND TB.VIEW_SEQ < ( SELECT B.VIEW_SEQ
FROM BSC_MAP_STG A
LEFT JOIN BSC_VIEW_INFO B
ON A.VIEW_REF_ID = B.VIEW_REF_ID
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID
AND STG_REF_ID = @STG_REF_ID)
ORDER BY TB.VIEW_SEQ DESC)
) TA,
(SELECT COUNT(UP_STG_REF_ID) AS UP_STG
FROM BSC_MAP_STG_PARENT
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID
AND STG_REF_ID = @STG_REF_ID) TB
WHERE 1 = 1 ";
string o_query = @"
SELECT
CASE
WHEN TA.ALL_STG = 0 THEN 'F'
ELSE
CASE WHEN (TA.ALL_STG - TB.UP_STG) = 0 THEN 'T' ELSE 'F' END
END AS RTN
FROM (
SELECT COUNT(STG_REF_ID) AS ALL_STG
FROM BSC_MAP_STG
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID
AND VIEW_REF_ID = ( SELECT VIEW_REF_ID FROM
( SELECT
TB.VIEW_REF_ID,
ROWNUM AS RNUM
FROM BSC_MAP_STG TA
LEFT JOIN BSC_VIEW_INFO TB
ON TA.VIEW_REF_ID = TB.VIEW_REF_ID
WHERE TA.ESTTERM_REF_ID = @ESTTERM_REF_ID
AND TA.EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND TA.MAP_VERSION_ID = @MAP_VERSION_ID
AND TB.VIEW_SEQ < (SELECT B.VIEW_SEQ
FROM BSC_MAP_STG A
LEFT JOIN BSC_VIEW_INFO B
ON A.VIEW_REF_ID = B.VIEW_REF_ID
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID
AND STG_REF_ID = @STG_REF_ID)
ORDER BY TB.VIEW_SEQ DESC )
WHERE RNUM = 1 )
) TA ,(SELECT COUNT(UP_STG_REF_ID) AS UP_STG
FROM BSC_MAP_STG_PARENT
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID
AND STG_REF_ID = @STG_REF_ID) TB
WHERE 1 = 1 ";
IDbDataParameter[] paramArray = CreateDataParameters(4);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = _estterm_ref_id;
paramArray[1] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[1].Value = _est_dept_ref_id;
paramArray[2] = CreateDataParameter("@STG_REF_ID", SqlDbType.Int);
paramArray[2].Value = stg_map_id;
paramArray[3] = CreateDataParameter("@MAP_VERSION_ID", SqlDbType.Int);
paramArray[3].Value = StgMaps_Data.GetMapVersionID(_estterm_ref_id, _est_dept_ref_id, _est_month, _map_version_id);
string outValue = Convert.ToString(DbAgentObj.ExecuteScalar(GetQueryStringByDb(s_query, o_query), paramArray, CommandType.Text));
if (outValue == "T")
{
return(true);
}
return(false);
}
//, vertical_id.ToString()
/// <summary>
/// 전략맵인과관계를 가지고 옴 (오버로드 2)
/// </summary>
/// <param name="stg_map_id"></param>
/// <param name="stg_map_type"></param>
/// <returns></returns>
public DataSet GetStgMapList(int stg_map_id, int stg_map_type)
{
IDbDataParameter[] paramArray = CreateDataParameters(5);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = _estterm_ref_id;
paramArray[1] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[1].Value = _est_dept_ref_id;
paramArray[2] = CreateDataParameter("@STG_REF_ID", SqlDbType.Int);
paramArray[2].Value = stg_map_id;
paramArray[3] = CreateDataParameter("@STG_MAP_TYPE", SqlDbType.Int);
paramArray[3].Value = stg_map_type;
paramArray[4] = CreateDataParameter("@MAP_VERSION_ID", SqlDbType.Int);
paramArray[4].Value = StgMaps_Data.GetMapVersionID(_estterm_ref_id, _est_dept_ref_id, _est_month, _map_version_id);
DataSet rDs = new DataSet();
//string providertype = GetProviderType();
string s_query = "";
string o_query = "";
s_query = @"SELECT ESTTERM_REF_ID
, EST_DEPT_REF_ID
, STG_REF_ID
, UP_STG_ID
, STG_MAP_TYPE
, STG_NAME
, STG_SET_DESC
, SORT_ORDER
, STG_MAP_LEVEL
FROM dbo.fn_GetStgMapByLevel_Version(@ESTTERM_REF_ID, @EST_DEPT_REF_ID, @STG_REF_ID, @MAP_VERSION_ID)
WHERE (STG_MAP_TYPE = @STG_MAP_TYPE OR @STG_MAP_TYPE = 0)
ORDER BY SORT_ORDER, UP_STG_ID, STG_REF_ID";
o_query = @"SELECT TZ.ESTTERM_REF_ID
,TZ.EST_DEPT_REF_ID
,TZ.STG_MAP_TYPE
,TZ.STG_REF_ID
,TZ.UP_STG_ID
,MAX(TZ.STG_NAME)
,'' as STG_SET_DESC
,MAX(TZ.STG_MAP_LEVEL) as STG_MAP_LEVEL
FROM (
SELECT TT.ESTTERM_REF_ID
,TT.EST_DEPT_REF_ID
,TT.STG_MAP_TYPE
,TT.STG_REF_ID
,TT.STG_NAME
,TT.UP_STG_ID
,TT.STG_SET_DESC as STG_SET_DESC
,TT.UP_STG_REF_ID
,TT.SORT_ORDER as SORT_ORDER
,TT.STG_MAP_LEVEL as STG_MAP_LEVEL
,ROWNUM as ALL_ORDER
FROM (
SELECT TA.ESTTERM_REF_ID
, TA.EST_DEPT_REF_ID
, TA.STG_REF_ID, TB.STG_NAME
, TA.UP_STG_REF_ID as UP_STG_ID
, TC.VIEW_REF_ID as STG_MAP_TYPE
, ' ' as STG_SET_DESC
, TA.UP_STG_REF_ID
, TC.SORT_ORDER
, LEVEL as STG_MAP_LEVEL
, VI.VIEW_SEQ
FROM (
SELECT @ESTTERM_REF_ID as ESTTERM_REF_ID
, @EST_DEPT_REF_ID as EST_DEPT_REF_ID
, @MAP_VERSION_ID as MAP_VERSION_ID
, @STG_REF_ID as STG_REF_ID
, NULL as UP_STG_REF_ID
FROM DUAL
UNION ALL
SELECT ESTTERM_REF_ID
, EST_DEPT_REF_ID
, MAP_VERSION_ID
, STG_REF_ID
, UP_STG_REF_ID
FROM BSC_MAP_STG_PARENT
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID
) TA
LEFT JOIN BSC_STG_INFO TB
ON TA.STG_REF_ID = TB.STG_REF_ID
LEFT JOIN BSC_MAP_STG TC
ON TA.ESTTERM_REF_ID = TC.ESTTERM_REF_ID
AND TA.EST_DEPT_REF_ID = TC.EST_DEPT_REF_ID
AND TA.MAP_VERSION_ID = TC.MAP_VERSION_ID
AND TA.STG_REF_ID = TC.STG_REF_ID
LEFT JOIN BSC_VIEW_INFO VI
ON TC.VIEW_REF_ID = VI.VIEW_REF_ID
WHERE (TC.VIEW_REF_ID = @STG_MAP_TYPE OR @STG_MAP_TYPE=0)
START WITH TA.UP_STG_REF_ID IS NULL
CONNECT BY PRIOR TA.STG_REF_ID = TA.UP_STG_REF_ID
ORDER BY TA.ESTTERM_REF_ID
, TA.EST_DEPT_REF_ID
, VI.VIEW_SEQ
, TC.SORT_ORDER
) TT
) TZ
GROUP BY TZ.ESTTERM_REF_ID
,TZ.EST_DEPT_REF_ID
,TZ.STG_MAP_TYPE
,TZ.STG_REF_ID
,TZ.UP_STG_ID
ORDER BY MAX(TZ.ALL_ORDER)";
// o_query = @"
// SELECT TC.ESTTERM_REF_ID
// ,TC.EST_DEPT_REF_ID
// ,TC.VIEW_REF_ID as STG_MAP_TYPE
// ,TC.VIEW_NAME
// ,TC.STG_REF_ID
// ,TC.STG_NAME
// ,'' as STG_SET_DESC
// ,TC.UP_STG_REF_ID as UP_STG_ID
// ,TC.VIEW_SEQ
// ,TC.SORT_ORDER
// ,TC.STG_LEVEL as STG_MAP_LEVEL
// ,TC.ALL_SORT_ORDER
// FROM (
// SELECT TB.ESTTERM_REF_ID as ESTTERM_REF_ID
// ,TB.EST_DEPT_REF_ID as EST_DEPT_REF_ID
// ,TB.VIEW_REF_ID as VIEW_REF_ID
// ,TB.VIEW_NAME as VIEW_NAME
// ,TB.STG_REF_ID as STG_REF_ID
// ,TB.STG_NAME as STG_NAME
// ,TB.UP_STG_REF_ID as UP_STG_REF_ID
// ,MAX(TB.VIEW_SEQ) as VIEW_SEQ
// ,MAX(TB.SORT_ORDER) as SORT_ORDER
// ,MAX(TB.STG_LEVEL) as STG_LEVEL
// ,MAX(TB.ALL_SORT_ORDER) as ALL_SORT_ORDER
// FROM (
// SELECT TA.ESTTERM_REF_ID
// ,TA.EST_DEPT_REF_ID
// ,TA.VIEW_REF_ID
// ,TA.STG_REF_ID
// ,TA.STG_NAME
// ,TA.UP_STG_REF_ID
// ,TA.VIEW_SEQ
// ,TA.SORT_ORDER
// ,TA.VIEW_NAME
// ,LEVEL as STG_LEVEL
// ,ROWNUM as ALL_SORT_ORDER
// FROM (
// SELECT MS.ESTTERM_REF_ID
// , MS.EST_DEPT_REF_ID
// , MS.VIEW_REF_ID
// , MS.STG_REF_ID
// , SI.STG_NAME
// , NVL(SP.UP_STG_REF_ID,0) as UP_STG_REF_ID
// , VI.VIEW_SEQ
// , MS.SORT_ORDER
// , VI.VIEW_NAME
// FROM BSC_MAP_STG MS
// INNER JOIN BSC_STG_INFO SI
// ON MS.ESTTERM_REF_ID = SI.ESTTERM_REF_ID
// AND MS.STG_REF_ID = SI.STG_REF_ID
// INNER JOIN BSC_VIEW_INFO VI
// ON MS.VIEW_REF_ID = VI.VIEW_REF_ID
// LEFT JOIN BSC_MAP_STG_PARENT SP
// ON MS.ESTTERM_REF_ID = SP.ESTTERM_REF_ID
// AND MS.EST_DEPT_REF_ID = SP.EST_DEPT_REF_ID
// AND MS.STG_REF_ID = SP.STG_REF_ID
// AND MS.MAP_VERSION_ID = SP.MAP_VERSION_ID
// AND MS.MAP_VERSION_ID = @MAP_VERSION_ID
// WHERE MS.ESTTERM_REF_ID = @ESTTERM_REF_ID
// AND MS.EST_DEPT_REF_ID = @EST_DEPT_REF_ID
// ) TA
// START WITH (TA.STG_REF_ID = :STG_REF_ID OR :STG_REF_ID = 0)
// CONNECT BY PRIOR TA.STG_REF_ID =TA.UP_STG_REF_ID
// ) TB
// GROUP BY TB.ESTTERM_REF_ID
// ,TB.EST_DEPT_REF_ID
// ,TB.VIEW_REF_ID
// ,TB.VIEW_NAME
// ,TB.STG_REF_ID
// ,TB.STG_NAME
// ,TB.UP_STG_REF_ID
// ) TC
// WHERE (TC.VIEW_REF_ID = @STG_MAP_TYPE OR @STG_MAP_TYPE = 0)
// ORDER BY TC.ALL_SORT_ORDER
// ";
rDs = DbAgentObj.FillDataSet(GetQueryStringByDb(s_query, o_query), "DataSet", null, paramArray, CommandType.Text);
return(rDs);
}
public DataSet GetMapinfo(int stg_map_id, int up_stg_map_id, int stg_map_type)
{
string query = @"
SELECT X.ESTTERM_REF_ID
, X.EST_DEPT_REF_ID
, X.STG_REF_ID
, X.UP_STG_ID
, X.VIEW_REF_ID
, X.SORT_ORDER
, Y.STG_NAME
, Y.STG_SET_DESC
, X.VIEW_SEQ
FROM (
SELECT BMS.ESTTERM_REF_ID
, BMS.EST_DEPT_REF_ID
, BMS.STG_REF_ID
, NULL AS UP_STG_ID
, BMS.VIEW_REF_ID
, BMS.SORT_ORDER
, BVI.VIEW_SEQ
FROM BSC_MAP_STG BMS
LEFT JOIN BSC_VIEW_INFO BVI ON BMS.VIEW_REF_ID = BVI.VIEW_REF_ID
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID
AND STG_REF_ID NOT IN (SELECT STG_REF_ID
FROM BSC_MAP_STG_PARENT
WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID
AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND MAP_VERSION_ID = @MAP_VERSION_ID)
UNION
SELECT TA.ESTTERM_REF_ID
, TA.EST_DEPT_REF_ID
, TA.STG_REF_ID
, TA.UP_STG_REF_ID
, TB.VIEW_REF_ID
, TB.SORT_ORDER
, TI.VIEW_SEQ
FROM BSC_MAP_STG_PARENT TA
LEFT JOIN BSC_MAP_STG TB
ON TA.ESTTERM_REF_ID = TB.ESTTERM_REF_ID
AND TA.EST_DEPT_REF_ID = TB.EST_DEPT_REF_ID
AND TA.MAP_VERSION_ID = TB.MAP_VERSION_ID
AND TA.STG_REF_ID = TB.STG_REF_ID
LEFT JOIN BSC_VIEW_INFO TI ON TB.VIEW_REF_ID = TI.VIEW_REF_ID
WHERE TA.ESTTERM_REF_ID = @ESTTERM_REF_ID
AND TA.EST_DEPT_REF_ID = @EST_DEPT_REF_ID
AND TA.MAP_VERSION_ID = @MAP_VERSION_ID
) X
LEFT JOIN BSC_STG_INFO Y ON (X.ESTTERM_REF_ID = Y.ESTTERM_REF_ID AND X.STG_REF_ID = Y.STG_REF_ID)
WHERE (Y.STG_REF_ID = @STG_REF_ID OR @STG_REF_ID = 0)
AND (X.UP_STG_ID = @UP_STG_ID OR @UP_STG_ID = 0)
AND (X.VIEW_REF_ID = @STG_MAP_TYPE OR @STG_MAP_TYPE = 0)
ORDER BY X.VIEW_SEQ, X.SORT_ORDER";
IDbDataParameter[] paramArray = CreateDataParameters(6);
paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int);
paramArray[0].Value = _estterm_ref_id;
paramArray[1] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int);
paramArray[1].Value = _est_dept_ref_id;
paramArray[2] = CreateDataParameter("@STG_REF_ID", SqlDbType.Int);
paramArray[2].Value = stg_map_id;
paramArray[3] = CreateDataParameter("@UP_STG_ID", SqlDbType.Int);
paramArray[3].Value = up_stg_map_id;
paramArray[4] = CreateDataParameter("@STG_MAP_TYPE", SqlDbType.Int);
paramArray[4].Value = stg_map_type;
paramArray[5] = CreateDataParameter("@MAP_VERSION_ID", SqlDbType.Int);
paramArray[5].Value = StgMaps_Data.GetMapVersionID(_estterm_ref_id, _est_dept_ref_id, _est_month, _map_version_id);
DataSet ds = DbAgentObj.FillDataSet(query, "MAPINFOGET", null, paramArray, CommandType.Text);
return(ds);
}