Changes between Version 12 and Version 13 of TracIniReportCustomFieldSample

Timestamp:

Mar 11, 2012, 7:06:08 AM (12 years ago)

Author:

signup@…

Comment:

Modified SQL to use a check for 0000-00-00 instead of IS NOT NULL

TracIniReportCustomFieldSample

@@ -171 +171 @@
 
 {{{
-
-SELECT p.value AS __color__,
+SELECT p.value AS __color__, 
    (CASE 
-     WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') is not null, 
-STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) > CURDATE()
+     WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00', 
+              STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) > CURDATE()
         THEN 'Future Tickets' 
-     WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') is not null, 
-STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) = CURDATE() 
+     WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00', 
+              STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) = CURDATE() 
         THEN 'Today Tickets' 
      ELSE 'Over Due Tickets' 
     END) AS __group__,
-   id AS ticket, summary, component, version, milestone, t.type AS type, 
-   owner, status,
-   time AS created,
-   changetime AS _changetime, description AS _description,
-   reporter AS _reporter, IF (STR_TO_DATE(d.value,'%m/%d/%Y') is not null, 
-STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d'))  AS duedate, 
-STR_TO_DATE(d.value,'%m/%d/%Y') AS mdy, 
-STR_TO_DATE(d.value,'%m/%d/%YY') AS mdyy, 
-STR_TO_DATE(d.value,'%Y/%m/%d') AS ymd, 
-STR_TO_DATE(d.value,'%YY/%m/%d') AS yymd 
+   id AS ticket, IF (STR_TO_DATE(d.value,'%m/%d/%Y') is not null, 
+   STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) AS 'Due Date', 
+   summary, component, version, milestone, t.type AS type, owner, status,
+   time AS created, changetime AS _changetime, description AS _description,
+   reporter AS _reporter
   FROM (ticket t)
   LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
-  LEFT JOIN enum d ON d.name = 'duedate' 
-  WHERE status <> 'closed' AND (STR_TO_DATE(d.value,'%Y/%m/%d') is not null 
-  OR STR_TO_DATE(d.value,'%m/%d/%Y') is not null)
+  LEFT JOIN ticket_custom d ON t.id = d.ticket AND d.name = 'duedate'
+  WHERE status <> 'closed' AND (STR_TO_DATE(d.value,'%Y/%m/%d') <> '0000-00-00' 
+  OR STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00')
   GROUP BY ticket
-  ORDER BY __group__, CAST(p.value AS signed), milestone, t.type, time
+  ORDER BY __group__ DESC, CAST(p.value AS signed), milestone, t.type, time
 }}}