# IF YOU ADD A NEW SECTION OF REPORTS, You will need to make # sure that section is also added to the all_reports hashtable # near the bottom #Please try to keep this clean" billing_reports = [ { "uuid":"b24f08c0-d41f-4c63-93a5-25e18a8513c2", "title":"Ticket Work Summary", "version":21, "sql":""" 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 (#STATUSES#) 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 (#STATUSES#) 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 """ },#END Ticket work summary { "uuid":"af13564f-0e36-4a17-96c0-632dc68d8d14", "title":"Milestone Work Summary", "version":18, "sql":""" 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 (#STATUSES#) 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 (#STATUSES#) 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 """ },#END Milestone work summary { "uuid":"7bd4b0ce-da6d-4b11-8be3-07e65b540d99", "title":"Developer Work Summary", "version":18, "sql":""" SELECT author as __group__,__style__, ticket, summary, newvalue as Work_added, time as datetime, _ord FROM( SELECT '' as __style__, author, t.id as ticket, t.summary as summary, 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 (#STATUSES#) AND billable.value in ($BILLABLE, $UNBILLABLE) AND ticket_change.time >= $STARTDATE AND ticket_change.time < $ENDDATE UNION SELECT 'background-color:#DFE;' as __style__, author, 0 as ticket, Null as summary, 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 (#STATUSES#) AND billable.value in ($BILLABLE, $UNBILLABLE) AND ticket_change.time >= $STARTDATE AND ticket_change.time < $ENDDATE GROUP By author ) as tbl ORDER BY author, _ord ASC, time """ },#END Hours Per Developer ] th_version =16 ticket_hours_reports = [ { "uuid":"8d785cdb-dcf5-43c9-b2a6-216997b0011a", "title": "Ticket Hours", "version":th_version, "sql": """ SELECT __color__, __style__, ticket, summary, component ,version, severity, milestone, status, owner, Estimated_work, Total_work, billable,_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 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 (#STATUSES#) 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 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 (#STATUSES#) AND billable.value in ($BILLABLE, $UNBILLABLE) ) as tbl ORDER BY _ord ASC, ticket """ }, #END Ticket Hours { "uuid":"71e7c36d-e512-4d0b-b499-087d4d20ff0b", "title": "Ticket Hours with Description", "version":th_version, "sql": """ 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 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 (#STATUSES#) 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 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 (#STATUSES#) AND billable.value in ($BILLABLE, $UNBILLABLE) ) as tbl ORDER BY _ord ASC, ticket """ }, #END Ticket Hours { "uuid":"5f33b102-e6a6-47e8-976c-ac7a6794a909", "title":"Ticket Hours Grouped By Component", "version":th_version, "sql": """ SELECT __color__, __group__, __style__, ticket, summary, __component__ ,version, severity, milestone, status, owner, Estimated_work, total_work, billable, _ord FROM ( SELECT p.value AS __color__, t.component AS __group__, '' as __style__, t.id AS ticket, summary AS summary, -- ## Break line here component as __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 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 (#STATUSES#) AND billable.value in ($BILLABLE, $UNBILLABLE) UNION SELECT '1' AS __color__, t.component AS __group__, 'background-color:#DFE;' as __style__, 0 as ticket, 'Total work' AS summary, t.component 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 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 (#STATUSES#) AND billable.value in ($BILLABLE, $UNBILLABLE) GROUP BY t.component ) as tbl ORDER BY __component__, _ord ASC,ticket """ }, # END Ticket Hours GROUPED BY COMPONENT { "uuid":"7816f034-a174-4a94-aed6-358fb648b2fc", "title":"Ticket Hours Grouped By Component with Description", "version":th_version, "sql": """ 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.component AS __group__, '' as __style__, t.id AS ticket, summary AS summary, -- ## Break line here component as __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 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 (#STATUSES#) AND billable.value in ($BILLABLE, $UNBILLABLE) UNION SELECT '1' AS __color__, t.component AS __group__, 'background-color:#DFE;' as __style__, 0 as ticket, 'Total work' AS summary, t.component 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 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 (#STATUSES#) AND billable.value in ($BILLABLE, $UNBILLABLE) GROUP BY t.component ) as tbl ORDER BY __component__, _ord ASC, ticket """ }, # END Ticket Hours Grouped BY Component with Description { "uuid":"03815803-7688-4f3a-8e65-8d254cc1d1fb", "title":"Ticket Hours Grouped By Milestone", "version":th_version, "sql": """ SELECT __color__, __group__, __style__, ticket, summary, component ,version, severity, __milestone__, status, owner, Estimated_work, Total_work, billable, _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 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 (#STATUSES#) 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 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 (#STATUSES#) AND billable.value in ($BILLABLE, $UNBILLABLE) GROUP BY t.milestone ) as tbl ORDER BY __milestone__, _ord ASC, ticket """ }, #END Ticket Hours Grouped By MileStone { "uuid":"040c9025-7641-4d18-96ad-2b26b4095566", "title":"Ticket Hours Grouped By MileStone with Description", "version":th_version, "sql": """ 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 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 (#STATUSES#) 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 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 (#STATUSES#) AND billable.value in ($BILLABLE, $UNBILLABLE) GROUP BY t.milestone ) as tbl ORDER BY __milestone__, _ord ASC, ticket """ } #END Ticket Hours Grouped By MileStone with Description ] all_reports = [ {"title":"Billing Reports", "description": "Currently the billing reports are the only time based reports, and are therefore useful for getting an idea of what tickets had times (and totals), and which developers spent their time where. ", "reports":billing_reports}, {"title":"Ticket/Hour Reports", "description": "These reports are useful for reviewing estimates on a large scale or getting an idea of the project at large. These reports currently ignore the start/end dates. ", "reports": ticket_hours_reports} ]