Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT '%c%' as Chapter,
- (SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status IN ('new','assigned') ) AS 'New',
- (SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='document_interface' ) AS 'Document
- Interface',
- (SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='interface_development' ) AS 'Inter
- face Development',
- (SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='interface_check' ) AS 'Interface C
- heck',
- (SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='document_routine' ) AS 'Document R
- outine',
- (SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='full_development' ) AS 'Full Devel
- opment',
- (SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='peer_review_1' ) AS 'Peer Review O
- ne',
- (SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%'AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='peer_review_2' ) AS 'Peer Review Tw
- o',
- (SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='qa' ) AS 'QA',
- (SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%'AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='closed' ) AS 'Closed',
- count(id) AS Total,
- ticket.id AS _id
- FROM engine.ticket
- INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine'
- SELECT '%c%' as Chapter,
- SUM(CASE WHEN ticket.status IN ('new','assigned') THEN 1 ELSE 0 END) as `New`,
- ...
- SUM(CASE WHEN ticket.status='closed' THEN 1 ELSE 0 END) as 'Closed',
- count(id) AS Total,
- ticket.id AS _id
- FROM engine.ticket
- INNER JOIN engine.ticket_custom
- ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter'
- AND ticket_custom.value LIKE '%c%'
- AND type='New material'
- AND milestone='1.1.12'
- AND component NOT LIKE 'internal_engine'
- GROUP BY ticket.id
- ;
- select '%c%' as Chapter,
- sum(case when ticket.status IN ('new','assigned') then 1 else 0 end) as 'New',
- sum(case when ticket.status='document_interface' then 1 else 0 end) as 'DocumentInterface',
- sum(case when ticket.status='interface_development' then 1 else 0 end) as 'Interface Development',
- sum(case when ticket.status='interface_check' then 1 else 0 end) as 'Interface Check',
- sum(case when ticket.status='document_routine' then 1 else 0 end) as 'Document Routine',
- sum(case when ticket.status='full_development' then 1 else 0 end) as 'Full Development',
- sum(case when ticket.status='peer_review_1' then 1 else 0 end) as 'Peer Review One',
- sum(case when ticket.status='peer_review_2' then 1 else 0 end) as 'Peer Review Two',
- sum(case when ticket.status='qa' then 1 else 0 end) as 'QA',
- sum(case when ticket.status='closed' then 1 else 0 end) as 'Closed',
- count(id) as Total,
- ticket.id as _id
- from
- engine.ticket
- inner join engine.ticket_custom on ticket.id = ticket_custom.ticket
- where
- ticket_custom.name='chapter' and
- ticket_custom.value LIKE '%c%' and
- type='New material' and
- milestone='1.1.12' and
- component NOT LIKE 'internal_engine'
- = Custom Report =
- My custom report, as a wiki page
- == Tickets for Milestone A ==
- [[TicketQuery(milestone=MilestoneA,status!=closed,group=status,format=table)]]
- == Tickets for Milestone B ==
- [[TicketQuery(milestone=MilestoneB,status!=closed,group=status,format=table)]]
- ...
- ||= **Id** =||= **Enhancements** =||= **Defects** =||= **Tasks** =||
- ||[milestone:v1.0 v1.0] || [[TicketQuery(milestone=v1.0,type=enhancement,format=count)]] || [[TicketQuery(milestone=v1.0,type=defect,format=count)]] || [[TicketQuery(milestone=v1.0,type=task,format=count)]] ||
- ... repeat for each milestone ...
- SELECT count(ticket.id) AS Matches, ticket_custom.name, ticket.status
- FROM engine.ticket
- INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
- WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='qa' AND (ticket.status IN (........))
- GROUP BY ticket.id, ticket_custom.name
- data = array()
- while(row = fetch($result)) {
- data[ticket.id]][ticket.status] = row[ticket.status];
- }
Add Comment
Please, Sign In to add comment