Edgewall Software

Changes between Version 12 and Version 13 of TracIniReportCustomFieldSample


Ignore:
Timestamp:
Mar 11, 2012, 7:06:08 AM (10 years ago)
Author:
signup@…
Comment:

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

Legend:

Unmodified
Added
Removed
Modified
  • TracIniReportCustomFieldSample

    v12 v13  
    171171
    172172{{{
    173 
    174 SELECT p.value AS __color__,
     173SELECT p.value AS __color__,
    175174   (CASE
    176      WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') is not null,
    177 STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) > CURDATE()
     175     WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00',
     176              STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) > CURDATE()
    178177        THEN 'Future Tickets'
    179      WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') is not null,
    180 STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) = CURDATE()
     178     WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00',
     179              STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) = CURDATE()
    181180        THEN 'Today Tickets'
    182181     ELSE 'Over Due Tickets'
    183182    END) AS __group__,
    184    id AS ticket, summary, component, version, milestone, t.type AS type,
    185    owner, status,
    186    time AS created,
    187    changetime AS _changetime, description AS _description,
    188    reporter AS _reporter, IF (STR_TO_DATE(d.value,'%m/%d/%Y') is not null,
    189 STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d'))  AS duedate,
    190 STR_TO_DATE(d.value,'%m/%d/%Y') AS mdy,
    191 STR_TO_DATE(d.value,'%m/%d/%YY') AS mdyy,
    192 STR_TO_DATE(d.value,'%Y/%m/%d') AS ymd,
    193 STR_TO_DATE(d.value,'%YY/%m/%d') AS yymd
     183   id AS ticket, IF (STR_TO_DATE(d.value,'%m/%d/%Y') is not null,
     184   STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) AS 'Due Date',
     185   summary, component, version, milestone, t.type AS type, owner, status,
     186   time AS created, changetime AS _changetime, description AS _description,
     187   reporter AS _reporter
    194188  FROM (ticket t)
    195189  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    196   LEFT JOIN enum d ON d.name = 'duedate'
    197   WHERE status <> 'closed' AND (STR_TO_DATE(d.value,'%Y/%m/%d') is not null
    198   OR STR_TO_DATE(d.value,'%m/%d/%Y') is not null)
     190  LEFT JOIN ticket_custom d ON t.id = d.ticket AND d.name = 'duedate'
     191  WHERE status <> 'closed' AND (STR_TO_DATE(d.value,'%Y/%m/%d') <> '0000-00-00'
     192  OR STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00')
    199193  GROUP BY ticket
    200   ORDER BY __group__, CAST(p.value AS signed), milestone, t.type, time
     194  ORDER BY __group__ DESC, CAST(p.value AS signed), milestone, t.type, time
    201195}}}