1 <?php # vim:ts=2:sw=2:et:
2 /* For licensing and copyright terms, see the file named LICENSE */
6 public $summary = null;
7 public $description = null;
9 public $changed = null;
11 static function loadByID($id) {
12 return new MTrackReport($id);
15 static function loadBySummary($summary) {
16 list($row) = MTrackDB::q('select rid from reports where summary = ?',
17 $summary)->fetchAll();
19 return new MTrackReport($row[0]);
24 function __construct($id = null) {
27 $q = MTrackDB::q('select * from reports where rid = ?', $this->rid);
28 foreach ($q->fetchAll() as $row) {
29 $this->summary = $row['summary'];
30 $this->description = $row['description'];
31 $this->query = $row['query'];
32 $this->changed = (int)$row['changed'];
35 throw new Exception("report $id not found");
39 function save(MTrackChangeset $changeset) {
42 /* figure what we actually changed */
43 $q = MTrackDB::q('select * from reports where rid = ?', $this->rid);
44 list($row) = $q->fetchAll();
46 $changeset->add("report:" . $this->rid . ":summary",
47 $row['summary'], $this->summary);
48 $changeset->add("report:" . $this->rid . ":description",
49 $row['description'], $this->description);
50 $changeset->add("report:" . $this->rid . ":query",
51 $row['query'], $this->query);
53 $q = MTrackDB::q('update reports set summary = ?, description = ?, query = ?, changed = ? where rid = ?',
54 $this->summary, $this->description, $this->query,
55 $changeset->cid, $this->rid);
57 $q = MTrackDB::q('insert into reports (summary, description, query, changed) values (?, ?, ?, ?)',
58 $this->summary, $this->description, $this->query,
60 $this->rid = MTrackDB::lastInsertId('reports', 'rid');
61 $changeset->add("report:" . $this->rid . ":summary",
62 null, $this->summary);
63 $changeset->add("report:" . $this->rid . ":description",
64 null, $this->description);
65 $changeset->add("report:" . $this->rid . ":query",
70 static function renderReport($repstring, $passed_params = null,
73 static $jquery_init = false;
75 $db = MTrackDB::get();
77 /* process the report string; any $PARAM in there is recognized
78 * as a parameter and the query munged accordingly to pass in the data */
82 $n = preg_match_all("/\\$([A-Z]+)/m", $repstring, $matches);
83 for ($i = 1; $i <= $n; $i++) {
84 /* default the parameter to no value */
85 $params[$matches[$i][0]] = '';
86 /* replace with query placeholder */
87 $repstring = str_replace('$' . $matches[$i][0], ':' . $matches[$i][0],
91 /* now to summon parameters */
92 if (isset($params['USER'])) {
93 $params['USER'] = MTrackAuth::whoami();
95 foreach ($params as $p => $v) {
96 if (isset($_GET[$p])) {
97 $params[$p] = $_GET[$p];
100 if (is_array($passed_params)) {
101 foreach ($params as $p => $v) {
102 if (isset($passed_params[$p])) {
103 $params[$p] = $passed_params[$p];
108 $q = $db->prepare($repstring);
109 $q->execute($params);
111 $results = $q->fetchAll(PDO::FETCH_ASSOC);
112 } catch (Exception $e) {
113 return "<div class='error'>" . $e->getMessage() . "<br>" .
114 htmlentities($repstring, ENT_QUOTES, 'utf-8') . "</div>";
119 if (count($results) == 0) {
120 return "No records matched";
123 /* figure out the table headings */
127 foreach ($results[0] as $name => $value) {
128 if (preg_match("/^__.*__$/", $name)) {
129 if ($format == 'html') {
130 /* special meaning, not a column */
134 $captions[$name] = preg_replace("/^_(.*)_$/", "\\1", $name);
136 /* for spanning purposes, calculate the longest row */
139 foreach ($captions as $name => $caption) {
140 if ($name[0] == '_' && substr($name, -1) == '_') {
145 if ($width > $max_width) {
148 if (substr($name, -1) == '_') {
154 foreach ($results as $nrow => $row) {
155 $starting_new_group = false;
158 $starting_new_group = true;
159 } else if ($format == 'html' &&
160 (isset($row['__group__']) && $group !== $row['__group__'])) {
161 $starting_new_group = true;
164 if ($starting_new_group) {
165 /* starting a new group */
167 /* close the old one */
168 if ($format == 'html') {
169 $out .= "</tbody></table>\n";
172 if ($format == 'html' && isset($row['__group__'])) {
173 $out .= "<h2 class='reportgroup'>" .
174 htmlentities($row['__group__'], ENT_COMPAT, 'utf-8') .
176 $group = $row['__group__'];
179 if ($format == 'html') {
180 $out .= "<table class='report'><thead><tr>";
183 foreach ($captions as $name => $caption) {
185 /* figure out sort info for javascript bits */
187 switch (strtolower($caption)) {
191 $sort = strtolower($caption);
197 $sort = 'mtrackdate';
210 $caption = ucfirst($caption);
211 if ($name[0] == '_' && substr($name,-1) == '_') {
212 if ($format == 'html') {
213 $out .= "</tr><tr><th colspan='$max_width'>$caption</th></tr><tr>";
214 } else if ($format == 'tab') {
215 $out .= "$caption\t";
217 } elseif ($name[0] == '_') {
220 if ($format == 'html') {
222 if ($sort !== null) {
223 $out .= " class=\"{sorter: '$sort'}\"";
225 $out .= ">$caption</th>";
226 if (substr($name, -1) == '_') {
229 } else if ($format == 'tab') {
230 $out .= "$caption\t";
234 if ($format == 'html') {
235 $out .= "</tr></thead><tbody>\n";
236 } else if ($format == 'tab') {
241 /* and now the column data itself */
242 if (isset($row['__style__'])) {
243 $style = " style=\"$row[__style__]\"";
247 $class = $nrow % 2 ? "even" : "odd";
248 if (isset($row['__color__'])) {
249 $class .= " color$row[__color__]";
251 if (isset($row['__status__'])) {
252 $class .= " status$row[__status__]";
255 if ($format == 'html') {
256 $begin_row = "<tr class=\"$class\"$style>";
261 /* determine if we should link to something for this row */
262 if (isset($row['ticket'])) {
263 $href = $ABSWEB . "ticket.php/$row[ticket]";
266 foreach ($captions as $name => $caption) {
269 /* apply special formatting rules */
270 if ($format == 'html') {
271 switch (strtolower($caption)) {
279 $v = mtrack_date($v);
283 $v = MTrackWiki::format_to_html($v);
286 $v = mtrack_username($v, array('no_image' => true));
290 $v = mtrack_ticket($row);
294 $v = htmlentities($v, ENT_QUOTES, 'utf-8');
295 $v = "<a href=\"$href\">$v</a>";
297 $v = htmlentities($v, ENT_QUOTES, 'utf-8');
303 foreach (preg_split("/\s*,\s*/", $oldv) as $m) {
304 if (!strlen($m)) continue;
305 $v .= "<span class='milestone'>" .
306 "<a href=\"{$ABSWEB}milestone.php/" .
307 urlencode($m) . "\">" .
308 htmlentities($m, ENT_QUOTES, 'utf-8') .
315 foreach (preg_split("/\s*,\s*/", $oldv) as $m) {
316 if (!strlen($m)) continue;
317 $v .= mtrack_keyword($m) . ' ';
321 $v = htmlentities($v, ENT_QUOTES, 'utf-8');
323 } else if ($format == 'tab') {
324 $v = trim(preg_replace("/[\t\n\r]+/sm", " ", $v));
327 if ($name[0] == '_' && substr($name, -1) == '_') {
328 if ($format == 'html') {
329 $out .= "</tr>$begin_row<td class='$caption' colspan='$max_width'>$v</td></tr>$begin_row";
330 } else if ($format == 'tab') {
333 } elseif ($name[0] == '_') {
334 if ($format == 'tab') {
340 if ($format == 'html') {
341 $out .= "<td class='$caption'>$v</td>";
342 if (substr($name, -1) == '_') {
343 $out .= "</tr>$begin_row";
345 } else if ($format == 'tab') {
350 if ($format == 'html') {
352 } else if ($format == 'tab') {
356 if ($format == 'html') {
357 $out .= "</tbody></table>";
358 } else if ($format == 'tab') {
359 $out = str_replace("\t\n", "\n", $out);
365 static function macro_RunReport($name, $url_style_params = null) {
367 parse_str($url_style_params, $params);
368 $rep = self::loadBySummary($name);
370 if (MTrackACL::hasAllRights("report:" . $rep->rid, 'read')) {
371 return $rep->renderReport($rep->query, $params);
373 return "Not authorized to run report $name";
376 return "Unable to find report $name";
380 static function parseQuery()
382 $macro_params = array(
394 'col' => array('ticket', 'summary', 'state',
396 'owner', 'type', 'component',
398 'order' => array('pri.value'),
399 'desc' => array('0'),
403 $args = func_get_args();
404 foreach ($args as $arg) {
405 if ($arg === null) continue;
406 $p = explode('&', $arg);
410 preg_match('/^([a-zA-Z_]+)(!?(?:=|~=|\^=|\$=))(.*)$/', $a, $M);
414 $pat = explode('|', $M[3]);
416 if (isset($macro_params[$k])) {
418 } else if (isset($params[$k])) {
419 if ($params[$k][0] == $op) {
420 // compatible operator; add $pat to possible set
421 $params[$k][1] = array_merge($pat, $params[$k][1]);
426 $params[$k] = array($op, $pat);
430 return array($params, $mparams);
433 static function macro_TicketQuery()
435 $args = func_get_args();
436 list($params, $mparams) = call_user_func_array(array(
437 'MTrackReport', 'parseQuery'), $args);
439 /* compose that info into a query */
443 'ticket' => '(case when t.nsident is null then t.tid else t.nsident end) as ticket',
444 'component' => '(select mtrack_group_concat(name) from ticket_components
445 tcm left join components c on (tcm.compid = c.compid)
446 where tcm.tid = t.tid) as component',
447 'keyword' => '(select mtrack_group_concat(keyword) from ticket_keywords
448 tk left join keywords k on (tk.kid = k.kid)
449 where tk.tid = t.tid) as keyword',
450 'type' => 'classification as type',
451 'remaining' => "(case when t.status = 'closed' then 0 else (t.estimated - (select sum(expended) from effort where effort.tid = t.tid)) end) as remaining",
452 'state' => "(case when t.status = 'closed' then coalesce(t.resolution, 'closed') else t.status end) as state",
453 'milestone' => '(select mtrack_group_concat(name) from ticket_milestones
454 tmm left join milestones tmmm on (tmm.mid = tmmm.mid)
455 where tmm.tid = t.tid) as milestone',
459 ' pri.value as __color__ ',
460 ' (case when t.nsident is null then t.tid else t.nsident end) as ticket ',
461 " t.status as __status__ ",
464 foreach ($mparams['col'] as $colname) {
465 if ($colname == 'ticket') {
468 if (isset($colmap[$colname])) {
469 $cols[$colname] = $colmap[$colname];
471 if (!preg_match("/^[a-zA-Z_]+$/", $colname)) {
472 throw new Exception("column name $colname is invalid");
474 $cols[$colname] = $colname;
478 $sql .= join(', ', $cols);
480 if (!isset($params['milestone'])) {
485 left join priorities pri on (t.priority = pri.priorityname)
486 left join severities sev on (t.severity = sev.sevname)
495 left join ticket_milestones tm on (m.mid = tm.mid)
496 left join tickets t on (tm.tid = t.tid)
497 left join priorities pri on (t.priority = pri.priorityname)
498 left join severities sev on (t.severity = sev.sevname)
506 'milestone' => 'm.name',
512 foreach ($params as $k => $v) {
513 list($op, $values) = $v;
515 if (isset($critmap[$k])) {
523 $op = substr($op, 1);
529 if ($k == 't.tid' && count($values) == 1 &&
530 preg_match('/[,-]/', $values[0])) {
533 foreach (explode(',', $values[0]) as $range) {
534 list($rfrom, $rto) = explode('-', $range, 2);
536 if (!ctype_digit($rfrom)) {
537 $rfrom = MTrackDB::esc($rfrom);
541 if (!ctype_digit($rto)) {
542 $rto = MTrackDB::esc($rto);
545 $crit[] = "(cast(t.tid as $type) between $rfrom and $rto)";
546 $crit[] = "(cast(t.nsident as $type) between $rfrom and $rto)";
548 $crit[] = "(t.tid = $rfrom)";
549 $crit[] = "(t.nsident = $rfrom)";
552 $sql .= join(' OR ', $crit);
553 } else if (count($values) == 1) {
554 $sql .= " $k = " . MTrackDB::esc($values[0]) . " ";
558 foreach ($values as $i => $val) {
559 $values[$i] = MTrackDB::esc($val);
561 $sql .= join(', ', $values) . ") ";
564 /* variations on like */
568 } else if ($op == '^=') {
578 foreach ($values as $val) {
579 $crit[] = "($k LIKE " . MTrackDB::esc("$start$val$end") . ")";
581 $sql .= join(" OR ", $crit);
587 if (isset($mparams['group'])) {
588 $g = $mparams['group'][0];
589 if (!ctype_alpha($g)) {
590 throw new Exception("group $g is not alpha");
592 $sql .= ' GROUP BY ' . $g;
595 if (isset($mparams['order'])) {
596 $k = $mparams['order'][0];
601 $sql .= ' ORDER BY ' . $k;
602 if (isset($mparams['desc']) && $mparams['desc'][0]) {
607 if (isset($mparams['max'])) {
608 $sql .= ' LIMIT ' . (int)$mparams['max'][0];
610 # return htmlentities($sql);
611 # return var_export($sql, true);
613 return self::renderReport($sql);
619 MTrackWiki::register_macro('RunReport',
620 array('MTrackReport', 'macro_RunReport'));
622 MTrackWiki::register_macro('TicketQuery',
623 array('MTrackReport', 'macro_TicketQuery'));
625 MTrackACL::registerAncestry('report', 'Reports');