-- ## 21: Tickets By Group ## --

-- A script that updates all empty custom fields that are filled in the Group ticket of the correspondig ticket:
-- {{{
-- UPDATE trac.ticket_custom y 
-- SET    value = r.value 
-- FROM  ( SELECT 	q.ticket, 
-- 		q.summary, 
-- 		q.name, 
-- 		w.value 
-- 	FROM  ( SELECT  t.ticket, 
-- 			a.summary, 
-- 			a.name, 
-- 			t.value AS group  
-- 		FROM trac.ticket_custom t, 
-- 		     (  SELECT  ti.summary, 
-- 				c.* 
-- 			FROM    trac.ticket ti, 
-- 				trac.ticket_custom c 
-- 			WHERE   c.ticket = ti.id AND 
-- 				c.value = '' ) a 
-- 		WHERE  	t.ticket = a.ticket AND 
-- 			t.value like 'GROUP%' 
-- 		ORDER BY t.ticket ) q 
-- 	JOIN  ( SELECT  t.summary, 
-- 			c.*  
-- 		FROM    trac.ticket t, 
-- 			trac.ticket_custom c 
-- 		WHERE 	t.id = c.ticket AND 
-- 			t.summary like 'GROUP%' AND 
-- 			c.value != '' ) w 
-- 	ON 	w.summary = q.group AND
-- 		w.name = q.name) r 
-- WHERE   y.ticket = r.ticket AND 
-- 	y.name = r.name;
-- }}}

SELECT  a.group as __GROUP__ ,
        a.id as ticket,
	a.name as summary,
	a.status as status,
	a.cc as cc,
        a.an_owners, 
	a.an_reviewers, 
	a.an_score, 
	a.de_owners, 
	a.de_reviewers, 
	a.de_score, 
	a.im_owners, 
	a.im_reviewers, 
	a.im_score
FROM (  SELECT  t.id as id,
                t.summary as name,
		t.summary as group, 
		t.status as status,
		t.cc as cc,
		1 as sort_num,
		( SELECT c.value 
    		  FROM ticket_custom c 
   		  WHERE c.name = 'analysis_owners' 
          		AND c.ticket = t.id ) AS  an_owners,
		( SELECT c.value 
		  FROM ticket_custom c 
		  WHERE c.name = 'analysis_reviewers' 
		        AND c.ticket = t.id) AS an_reviewers,
		( SELECT c.value 
 		  FROM ticket_custom c 
 		  WHERE c.name = 'analysis_score' 
      		        AND c.ticket = t.id) AS an_score,
		( SELECT c.value 
		  FROM ticket_custom c 
  		  WHERE c.name = 'design_owners' 
     		        AND c.ticket = t.id) AS de_owners,
  		( SELECT c.value 
   		  FROM ticket_custom c 
   		  WHERE c.name = 'design_reviewers' 
    		        AND c.ticket = t.id) AS de_reviewers,
 		( SELECT c.value 
   		  FROM ticket_custom c 
    		  WHERE c.name = 'design_score' 
    		        AND c.ticket = t.id) AS de_score,
 		( SELECT c.value 
  		  FROM ticket_custom c 
   		  WHERE c.name = 'implementation_owners' 
  		        AND c.ticket = t.id) AS im_owners,
 		( SELECT c.value 
  		  FROM ticket_custom c 
   		  WHERE c.name = 'implementation_reviewers' 
   		        AND c.ticket = t.id) as im_reviewers,
 		( SELECT c.value 
  		  FROM ticket_custom c 
   		  WHERE c.name = 'implementation_score' 
   		        AND c.ticket = t.id) AS im_score
        FROM ticket t, ticket_custom c 
	WHERE   t.summary like 'GROUP%' 
		AND t.id = c.ticket 
		AND c.name = 'ticket_group' 
	UNION
	SELECT  t.id as id,
                t.summary as name, 
		c.value as group, 
		t.status as status,
		t.cc as cc,
		2 as sort_num,
		( SELECT c.value 
  		  FROM ticket_custom c 
  		  WHERE c.name = 'analysis_owners' 
         		AND c.ticket = t.id ) AS  an_owners,
		( SELECT c.value 
  		  FROM ticket_custom c 
  		  WHERE c.name = 'analysis_reviewers' 
			AND c.ticket = t.id) AS an_reviewers,
		( SELECT c.value 
  		  FROM ticket_custom c 
  		  WHERE c.name = 'analysis_score' 
			AND c.ticket = t.id) AS an_score,
		( SELECT c.value 
  		  FROM ticket_custom c 
  		  WHERE c.name = 'design_owners' 
			AND c.ticket = t.id) AS de_owners,
		( SELECT c.value 
  		  FROM ticket_custom c 
  		  WHERE c.name = 'design_reviewers' 
			AND c.ticket = t.id) AS de_reviewers,
		( SELECT c.value 
  		  FROM ticket_custom c 
  		  WHERE c.name = 'design_score' 
			AND c.ticket = t.id) AS de_score,
		( SELECT c.value 
  		  FROM ticket_custom c 
  		  WHERE c.name = 'implementation_owners' 
			AND c.ticket = t.id) AS im_owners,
		(SELECT c.value 
  		  FROM ticket_custom c 
  		  WHERE c.name = 'implementation_reviewers' 
			AND c.ticket = t.id) as im_reviewers,
		( SELECT c.value 
  		  FROM ticket_custom c 
  		  WHERE c.name = 'implementation_score' 
			AND c.ticket = t.id) AS im_score
	FROM ticket t, ticket_custom c 
	WHERE   t.id = c.ticket 
		AND c.name = 'ticket_group' 
		AND c.value != '') a 
ORDER BY a.group,a.sort_num