-- ## 17: Ticket Hours Grouped By MileStone with Description ## --

-- Reports Must Be Accessed From the Management Screen


SELECT __color__, __group__, __style__,  ticket, summary, component ,version, severity,
 __milestone__, status, owner, Estimated_work, Total_work, billable,
 _description_, _ord

FROM (
SELECT p.value AS __color__,
       t.milestone AS __group__,
       '' as __style__,
       t.id AS ticket, summary AS summary,             -- ## Break line here
       component,version, severity, milestone as __milestone__, status, owner,
       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
         ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work,
       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
         ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work,
       CASE WHEN billable.value = '1' THEN 'Y'
            else 'N'
       END as billable,
       time AS created, changetime AS modified,         -- ## Dates are formatted
       description AS _description_,                    -- ## Uses a full row
       changetime AS _changetime,
       reporter AS _reporter
       ,0 as _ord

  FROM ticket as t
  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'

LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
      AND EstimatedHours.Ticket = t.Id
LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
      AND totalhours.Ticket = t.Id
LEFT JOIN ticket_custom as billable ON billable.name='billable'
      AND billable.Ticket = t.Id

  WHERE t.status IN ($ASSIGNED, $ACCEPTED, $CLOSED, $NEW, $REOPENED)
    AND billable.value in ($BILLABLE, $UNBILLABLE)


UNION

SELECT '1' AS __color__,
       t.milestone AS __group__,
       'background-color:#DFE;' as __style__,
       0 as ticket, 'Total work' AS summary,
       NULL as component,NULL as version, NULL as severity,
       t.milestone as __milestone__,
       'Time Remaining: ' as status,
       CAST(
       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -
       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
         ELSE CAST( totalhours.value AS DECIMAL ) END)
         AS CHAR(512)) as owner,
       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
         ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work,
       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
         ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work,
       NULL as billable,
       NULL as created, NULL as modified,         -- ## Dates are formatted
       NULL AS _description_,
       NULL AS _changetime,
       NULL AS _reporter, 1 as _ord
  FROM ticket as t
  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'

LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
      AND EstimatedHours.Ticket = t.Id

LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
      AND totalhours.Ticket = t.Id

LEFT JOIN ticket_custom as billable ON billable.name='billable'
      AND billable.Ticket = t.Id

  WHERE t.status IN ($ASSIGNED, $ACCEPTED, $CLOSED, $NEW, $REOPENED)
    AND billable.value in ($BILLABLE, $UNBILLABLE)
  GROUP BY t.milestone
)  as tbl
ORDER BY __milestone__, _ord ASC, ticket