Subversion Repositories DevTools

-- =====================================================
-- WorkInProgressByUser.sql
-- Keep in step with 'UserWip.sql'
--
--   Used by index.asp to list Releases and Package Versions 
--   that the user is working on
-- =====================================================
SELECT usr.USER_ID,
           usr.FULL_NAME,
           usr.USER_EMAIL,
           COUNT(usr.USER_ID) AS COUNTER
  FROM (
  
                /* Get WIP Packages */
        SELECT wip.PV_ID, rt.RTAG_ID, pv.MODIFIER_ID
                  FROM WORK_IN_PROGRESS wip,
                           RELEASE_TAGS rt,
                           PACKAGE_VERSIONS pv
                 WHERE wip.RTAG_ID = rt.RTAG_ID
                   AND rt.OFFICIAL In ('N','R','C')
                   AND wip.PV_ID = pv.PV_ID
                   AND pv.DLOCKED != 'Y'
       AND pv.MODIFIER_ID = :USER_ID
                
                UNION 
                   
                /* Get WIP Patches */
        SELECT pp.PATCH_ID AS PV_ID, rt.RTAG_ID, pv.MODIFIER_ID
                  FROM RELEASE_CONTENT rc,
                           RELEASE_TAGS rt,
                           PACKAGE_PATCHES pp,
                           PACKAGE_VERSIONS pv,
         WORK_IN_PROGRESS wip
                 WHERE rc.RTAG_ID = rt.RTAG_ID
                   AND rt.OFFICIAL In ('N','R','C')     
                   AND pp.PV_ID = rc.PV_ID
                   AND pp.PATCH_ID = pv.PV_ID
                   AND pv.DLOCKED != 'Y'
                   AND pv.IS_OBSOLETE IS NULL
       AND pv.MODIFIER_ID = :USER_ID
                   AND wip.RTAG_ID = rt.RTAG_ID 
       AND wip.PV_ID = pp.PATCH_ID         
                 
           ) rel,
           USERS usr
 WHERE rel.MODIFIER_ID = usr.USER_ID   
 GROUP BY  usr.USER_ID, usr.FULL_NAME, usr.USER_EMAIL