Subversion Repositories DevTools

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
5377 dpurdie 1 
-- =====================================================
2 
-- WorkInProgressByUser.sql
3 
-- Keep in step with 'UserWip.sql'
4 
--
5 
--   Used by index.asp to list Releases and Package Versions
6 
--   that the user is working on
7 
-- =====================================================
119 ghuddy 8 
SELECT usr.USER_ID,
9 
	   usr.FULL_NAME,
10 
	   usr.USER_EMAIL,
11 
	   COUNT(usr.USER_ID) AS COUNTER
12 
  FROM (
13 
 
14 
  		/* Get WIP Packages */
15 
        SELECT wip.PV_ID, rt.RTAG_ID, pv.MODIFIER_ID
16 
		  FROM WORK_IN_PROGRESS wip,
17 
		  	   RELEASE_TAGS rt,
18 
			   PACKAGE_VERSIONS pv
19 
		 WHERE wip.RTAG_ID = rt.RTAG_ID
5377 dpurdie 20 
		   AND rt.OFFICIAL In ('N','R','C')
119 ghuddy 21 
		   AND wip.PV_ID = pv.PV_ID
22 
		   AND pv.DLOCKED != 'Y'
5377 dpurdie 23 
       AND pv.MODIFIER_ID = :USER_ID
119 ghuddy 24 
 
25 
		UNION
26 
 
27 
		/* Get WIP Patches */
28 
        SELECT pp.PATCH_ID AS PV_ID, rt.RTAG_ID, pv.MODIFIER_ID
29 
		  FROM RELEASE_CONTENT rc,
30 
		  	   RELEASE_TAGS rt,
31 
			   PACKAGE_PATCHES pp,
5377 dpurdie 32 
			   PACKAGE_VERSIONS pv,
33 
         WORK_IN_PROGRESS wip
119 ghuddy 34 
		 WHERE rc.RTAG_ID = rt.RTAG_ID
5377 dpurdie 35 
		   AND rt.OFFICIAL In ('N','R','C')
119 ghuddy 36 
		   AND pp.PV_ID = rc.PV_ID
37 
		   AND pp.PATCH_ID = pv.PV_ID
38 
		   AND pv.DLOCKED != 'Y'
39 
		   AND pv.IS_OBSOLETE IS NULL
5377 dpurdie 40 
       AND pv.MODIFIER_ID = :USER_ID
41 
		   AND wip.RTAG_ID = rt.RTAG_ID
42 
       AND wip.PV_ID = pp.PATCH_ID
119 ghuddy 43 
 
44 
  	   ) rel,
45 
  	   USERS usr
46 
 WHERE rel.MODIFIER_ID = usr.USER_ID
47 
 GROUP BY  usr.USER_ID, usr.FULL_NAME, usr.USER_EMAIL
5377 dpurdie 48