-- ## 10: Milestone Work Summary ## --

-- Reports Must Be Accessed From the Management Screen



SELECT
  milestone as __group__, __style__,  ticket, summary, newvalue as Work_added,
  time  as datetime, _ord
FROM(
  SELECT '' as __style__, t.id as ticket,
    SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
         ELSE CAST( newvalue AS DECIMAL ) END) as newvalue, t.summary as summary,
    MAX(ticket_change.time) as time, t.milestone as milestone, 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
  GROUP BY t.milestone, t.id, t.summary

  UNION

  SELECT 'background-color:#DFE;' as __style__, 0 as ticket,
    sum( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
         ELSE CAST( newvalue AS DECIMAL ) END) as newvalue, 'Total work done' as summary,
    NULL as time, t.milestone as milestone, 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.milestone
)  as tbl
ORDER BY milestone,  _ord ASC, ticket, time