-- ## 9: Ticket Work Summary ## --

-- Reports Must Be Accessed From the Management Screen


SELECT __ticket__ as __group__, __style__, ticket,
newvalue as Work_added, author, time as datetime, _ord
FROM(
  SELECT '' as __style__, author,
  t.summary as __ticket__,
  t.id as ticket,
  CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
    ELSE CAST( newvalue AS DECIMAL ) END AS newvalue,
  ticket_change.time as time, 0 as _ord
  FROM ticket_change
  JOIN ticket t on t.id = ticket_change.ticket
  LEFT JOIN ticket_custom as billable on billable.ticket = t.id
    and billable.name = 'billable'
  WHERE field = 'hours' and
    t.status IN ($ASSIGNED, $ACCEPTED, $CLOSED, $NEW, $REOPENED)
      AND billable.value in ($BILLABLE, $UNBILLABLE)
      AND ticket_change.time >= $STARTDATE
      AND ticket_change.time < $ENDDATE

  UNION

  SELECT 'background-color:#DFE;' as __style__,
    'Total work done on the ticket in the selected time period ' as author,
    t.summary as __ticket__,
    t.id as ticket,
  SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
         ELSE CAST( newvalue AS DECIMAL ) END ) as newvalue,
    NULL as time, 1 as _ord
  FROM ticket_change
  JOIN ticket t on t.id = ticket_change.ticket
  LEFT JOIN ticket_custom as billable on billable.ticket = t.id
    and billable.name = 'billable'
  WHERE field = 'hours' and
    t.status IN ($ASSIGNED, $ACCEPTED, $CLOSED, $NEW, $REOPENED)
      AND billable.value in ($BILLABLE, $UNBILLABLE)
      AND ticket_change.time >= $STARTDATE
      AND ticket_change.time < $ENDDATE
  GROUP By t.id, t.summary
)  as tbl
ORDER BY __ticket__, _ord ASC, time ASC