163 | | duration = text |
164 | | duration.label = Duration |
165 | | |
166 | | customer = text |
167 | | customer.label = Customer |
168 | | }}} |
169 | | |
170 | | This obviously didn't work: |
171 | | |
172 | | '''Custom SQL:''' |
173 | | |
174 | | {{{ |
175 | | SELECT |
176 | | ticket_custom.value AS Duration |
177 | | |
178 | | FROM ticket_custom |
179 | | |
180 | | WHERE ticket_custom.name = 'duration' |
181 | | AND ticket_custom.value = 'Our Customer' |
182 | | }}} |
183 | | |
184 | | What we did was make a "loop" INNER JOIN ticket_custom on ticket_custom.ticket like this: |
185 | | |
186 | | '''Custom SQL:''' |
187 | | |
188 | | {{{ |
189 | | SELECT |
190 | | tc1.value AS Duration |
191 | | |
192 | | FROM ticket_custom tc1 |
193 | | |
194 | | INNER JOIN ticket_custom tc2 |
195 | | ON tc1.ticket = tc2.ticket |
196 | | |
197 | | WHERE tc1.name = 'duration' |
198 | | AND tc2.value = 'Our Customer' |
199 | | }}} |
| 165 | duedate = text |
| 166 | duedate.label = Due Date |
| 167 | }}} |
| 168 | |
| 169 | |
| 170 | '''Custom SQL:''' |
| 171 | |
| 172 | {{{ |
| 173 | |
| 174 | SELECT p.value AS __color__, |
| 175 | (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() |
| 178 | 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() |
| 181 | THEN 'Today Tickets' |
| 182 | ELSE 'Over Due Tickets' |
| 183 | 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 |
| 194 | FROM (ticket t) |
| 195 | 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) |
| 199 | GROUP BY ticket |
| 200 | ORDER BY __group__, CAST(p.value AS signed), milestone, t.type, time |
| 201 | }}} |