Exemple #1
0
        /// <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);
        }
Exemple #4
0
        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);
        }
Exemple #5
0
        //, 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);
        }
Exemple #6
0
        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);
        }