-- ## 13: Ticket Hours with Description ## --

-- Reports Must Be Accessed From the Management Screen


SELECT __color__,  __style__,  ticket, summary, component ,version, severity,
 milestone, status, owner, Estimated_work, Total_work, billable
-- ## ,created,  modified,         -- ## Dates are formatted
,_description_
-- ## _changetime,
-- ## _reporter
,_ord

FROM (
SELECT p.value AS __color__,
       '' as __style__,
       t.id AS ticket, summary AS summary,             -- ## Break line here
       component,version, severity, 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__,
       'background-color:#DFE;' as __style__,
       0 as ticket, 'Total' AS summary,
       NULL as component,NULL as version, NULL as severity, NULL 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)
)  as tbl
ORDER BY _ord ASC, ticket