1 <?php # vim:ts=2:sw=2:et:
2 /* For licensing and copyright terms, see the file named LICENSE */
4 require_once 'MTrack/Wiki.php';
5 require_once 'MTrack/ACL.php';
11 public $summary = null;
12 public $description = null;
14 public $changed = null;
15 static public $link = null; /// LinkHandler!!
17 static function loadByID($id) {
18 return new MTrack_Report($id);
21 static function loadBySummary($summary) {
22 list($row) = MTrackDB::q('select rid from reports where summary = ?',
23 $summary)->fetchAll();
25 return new MTrack_Report($row[0]);
30 function __construct($id = null) {
33 $q = MTrackDB::q('select * from reports where rid = ?', $this->rid);
34 foreach ($q->fetchAll() as $row) {
35 $this->summary = $row['summary'];
36 $this->description = $row['description'];
37 $this->query = $row['query'];
38 $this->changed = (int)$row['changed'];
41 throw new Exception("report $id not found");
45 function save(MTrackChangeset $changeset) {
48 /* figure what we actually changed */
49 $q = MTrackDB::q('select * from reports where rid = ?', $this->rid);
50 list($row) = $q->fetchAll();
52 $changeset->add("report:" . $this->rid . ":summary",
53 $row['summary'], $this->summary);
54 $changeset->add("report:" . $this->rid . ":description",
55 $row['description'], $this->description);
56 $changeset->add("report:" . $this->rid . ":query",
57 $row['query'], $this->query);
59 $q = MTrackDB::q('update reports set summary = ?, description = ?, query = ?, changed = ? where rid = ?',
60 $this->summary, $this->description, $this->query,
61 $changeset->cid, $this->rid);
63 $q = MTrackDB::q('insert into reports (summary, description, query, changed) values (?, ?, ?, ?)',
64 $this->summary, $this->description, $this->query,
66 $this->rid = MTrackDB::lastInsertId('reports', 'rid');
67 $changeset->add("report:" . $this->rid . ":summary",
68 null, $this->summary);
69 $changeset->add("report:" . $this->rid . ":description",
70 null, $this->description);
71 $changeset->add("report:" . $this->rid . ":query",
79 return self::renderReport($this->query);
84 static function renderReport($repstring, $passed_params = null, $format = 'html')
87 if (empty(MTrack_Wiki_HTMLFormatter::$linkHandler)) {
88 die("MTrack_Wiki_HTMLFormatter::\$link handler not set up");
91 static $jquery_init = false;
93 $db = MTrackDB::get();
95 /* process the report string; any $PARAM in there is recognized
96 * as a parameter and the query munged accordingly to pass in the data */
100 $n = preg_match_all("/\\$([A-Z]+)/m", $repstring, $matches);
101 for ($i = 1; $i <= $n; $i++) {
102 /* default the parameter to no value */
103 $params[$matches[$i][0]] = '';
104 /* replace with query placeholder */
105 $repstring = str_replace('$' . $matches[$i][0], ':' . $matches[$i][0],
109 /* now to summon parameters */
110 if (isset($params['USER'])) {
111 $params['USER'] = MTrackAuth::whoami();
113 foreach ($params as $p => $v) {
114 if (isset($_GET[$p])) {
115 $params[$p] = $_GET[$p];
118 if (is_array($passed_params)) {
119 foreach ($params as $p => $v) {
120 if (isset($passed_params[$p])) {
121 $params[$p] = $passed_params[$p];
126 $q = $db->prepare($repstring);
127 $q->execute($params);
129 $results = $q->fetchAll(PDO::FETCH_ASSOC);
130 } catch (Exception $e) {
131 return "<div class='error'>" . $e->getMessage() . "<br>" .
132 htmlentities($repstring, ENT_QUOTES, 'utf-8') . "</div>";
137 if (count($results) == 0) {
138 return "No records matched";
141 /* figure out the table headings */
145 foreach ($results[0] as $name => $value) {
146 if (preg_match("/^__.*__$/", $name)) {
147 if ($format == 'html') {
148 /* special meaning, not a column */
152 $captions[$name] = preg_replace("/^_(.*)_$/", "\\1", $name);
154 /* for spanning purposes, calculate the longest row */
157 foreach ($captions as $name => $caption) {
158 if ($name[0] == '_' && substr($name, -1) == '_') {
163 if ($width > $max_width) {
166 if (substr($name, -1) == '_') {
172 foreach ($results as $nrow => $row) {
173 $starting_new_group = false;
176 $starting_new_group = true;
177 } else if ($format == 'html' &&
178 (isset($row['__group__']) && $group !== $row['__group__'])) {
179 $starting_new_group = true;
182 if ($starting_new_group) {
183 /* starting a new group */
185 /* close the old one */
186 if ($format == 'html') {
187 $out .= "</tbody></table>\n";
190 if ($format == 'html' && isset($row['__group__'])) {
191 $out .= "<h2 class='reportgroup'>" .
192 htmlentities($row['__group__'], ENT_COMPAT, 'utf-8') .
194 $group = $row['__group__'];
197 if ($format == 'html') {
198 $out .= "<table class='report'><thead><tr>";
201 foreach ($captions as $name => $caption) {
203 /* figure out sort info for javascript bits */
205 switch (strtolower($caption)) {
209 $sort = strtolower($caption);
215 $sort = 'mtrackdate';
228 $caption = ucfirst($caption);
229 if ($name[0] == '_' && substr($name,-1) == '_') {
230 if ($format == 'html') {
231 $out .= "</tr><tr><th colspan='$max_width'>$caption</th></tr><tr>";
232 } else if ($format == 'tab') {
233 $out .= "$caption\t";
235 } elseif ($name[0] == '_') {
238 if ($format == 'html') {
240 if ($sort !== null) {
241 $out .= " class=\"{sorter: '$sort'}\"";
243 $out .= ">$caption</th>";
244 if (substr($name, -1) == '_') {
247 } else if ($format == 'tab') {
248 $out .= "$caption\t";
252 if ($format == 'html') {
253 $out .= "</tr></thead><tbody>\n";
254 } else if ($format == 'tab') {
259 /* and now the column data itself */
260 if (isset($row['__style__'])) {
261 $style = " style=\"$row[__style__]\"";
265 $class = $nrow % 2 ? "even" : "odd";
266 if (isset($row['__color__'])) {
267 $class .= " color$row[__color__]";
269 if (isset($row['__status__'])) {
270 $class .= " status$row[__status__]";
273 if ($format == 'html') {
274 $begin_row = "<tr class=\"$class\"$style>";
279 /* determine if we should link to something for this row */
281 //if (isset($row['ticket'])) {
283 // $href = $ABSWEB . "/Ticket.php/$row[ticket]";
286 foreach ($captions as $name => $caption) {
289 /* apply special formatting rules */
290 if ($format == 'html') {
291 switch (strtolower($caption)) {
299 $v = MTrack_Wiki_HTMLFormatter::$linkHandler->date($v);
303 $v = MTrack_Wiki::format_to_html($v);
306 $v = MTrack_Wiki_HTMLFormatter::$linkHandler->username($v, array('no_image' => true));
311 $v = MTrack_Wiki_HTMLFormatter::$linkHandler->ticket($row['ticket']); // what about doc id?
315 $v = isset($row['ticket']) ?
316 MTrack_Wiki_HTMLFormatter::$linkHandler->ticket($row['ticket'], array('display' => $v)) :
317 $v= htmlspecialchars($v);
322 foreach (preg_split("/\s*,\s*/", $oldv) as $m) {
323 if (!strlen($m)) continue;
324 $v .= MTrack_Wiki_HTMLFormatter::$linkHandler->milestone($m);
326 $v .= "<span class='milestone'>" .
327 "<a href=\"{$ABSWEB}milestone.php/" .
328 urlencode($m) . "\">" .
329 htmlentities($m, ENT_QUOTES, 'utf-8') .
337 foreach (preg_split("/\s*,\s*/", $oldv) as $m) {
338 if (!strlen($m)) continue;
339 $v .= MTrack_Wiki_HTMLFormatter::$linkHandler->keyword($m) . ' ';
343 $v = htmlentities($v, ENT_QUOTES, 'utf-8');
345 } else if ($format == 'tab') {
346 $v = trim(preg_replace("/[\t\n\r]+/sm", " ", $v));
349 if ($name[0] == '_' && substr($name, -1) == '_') {
350 if ($format == 'html') {
351 $out .= "</tr>$begin_row<td class='$caption' colspan='$max_width'>$v</td></tr>$begin_row";
352 } else if ($format == 'tab') {
355 } elseif ($name[0] == '_') {
356 if ($format == 'tab') {
362 if ($format == 'html') {
363 $out .= "<td class='$caption'>$v</td>";
364 if (substr($name, -1) == '_') {
365 $out .= "</tr>$begin_row";
367 } else if ($format == 'tab') {
372 if ($format == 'html') {
374 } else if ($format == 'tab') {
378 if ($format == 'html') {
379 $out .= "</tbody></table>";
380 } else if ($format == 'tab') {
381 $out = str_replace("\t\n", "\n", $out);
387 static function macro_RunReport($name, $url_style_params = null) {
389 parse_str($url_style_params, $params);
390 $rep = self::loadBySummary($name);
392 if (MTrackACL::hasAllRights("report:" . $rep->rid, 'read')) {
393 return $rep->renderReport($rep->query, $params);
395 return "Not authorized to run report $name";
398 return "Unable to find report $name";
402 static function parseQuery()
404 $macro_params = array(
416 'col' => array('ticket', 'summary', 'state',
418 'owner', 'type', 'component',
420 'order' => array('pri.value'),
421 'desc' => array('0'),
425 $args = func_get_args();
426 foreach ($args as $arg) {
427 if ($arg === null) continue;
428 $p = explode('&', $arg);
432 preg_match('/^([a-zA-Z_]+)(!?(?:=|~=|\^=|\$=))(.*)$/', $a, $M);
436 $pat = explode('|', $M[3]);
438 if (isset($macro_params[$k])) {
440 } else if (isset($params[$k])) {
441 if ($params[$k][0] == $op) {
442 // compatible operator; add $pat to possible set
443 $params[$k][1] = array_merge($pat, $params[$k][1]);
448 $params[$k] = array($op, $pat);
452 return array($params, $mparams);
455 static function macro_TicketQuery()
457 $args = func_get_args();
458 list($params, $mparams) = call_user_func_array(array(
459 'MTrack_Report', 'parseQuery'), $args);
461 /* compose that info into a query */
465 'ticket' => '(case when t.nsident is null then t.tid else t.nsident end) as ticket',
466 'component' => '(select mtrack_group_concat(name) from ticket_components
467 tcm left join components c on (tcm.compid = c.compid)
468 where tcm.tid = t.tid) as component',
469 'keyword' => '(select mtrack_group_concat(keyword) from ticket_keywords
470 tk left join keywords k on (tk.kid = k.kid)
471 where tk.tid = t.tid) as keyword',
472 'type' => 'classification as type',
473 'remaining' => "(case when t.status = 'closed' then 0 else (t.estimated - (select sum(expended) from effort where effort.tid = t.tid)) end) as remaining",
474 'state' => "(case when t.status = 'closed' then coalesce(t.resolution, 'closed') else t.status end) as state",
475 'milestone' => '(select mtrack_group_concat(name) from ticket_milestones
476 tmm left join milestones tmmm on (tmm.mid = tmmm.mid)
477 where tmm.tid = t.tid) as milestone',
481 ' pri.value as __color__ ',
482 ' (case when t.nsident is null then t.tid else t.nsident end) as ticket ',
483 " t.status as __status__ ",
486 foreach ($mparams['col'] as $colname) {
487 if ($colname == 'ticket') {
490 if (isset($colmap[$colname])) {
491 $cols[$colname] = $colmap[$colname];
493 if (!preg_match("/^[a-zA-Z_]+$/", $colname)) {
494 throw new Exception("column name $colname is invalid");
496 $cols[$colname] = $colname;
500 $sql .= join(', ', $cols);
502 if (!isset($params['milestone'])) {
507 left join priorities pri on (t.priority = pri.priorityname)
508 left join severities sev on (t.severity = sev.sevname)
517 left join ticket_milestones tm on (m.mid = tm.mid)
518 left join tickets t on (tm.tid = t.tid)
519 left join priorities pri on (t.priority = pri.priorityname)
520 left join severities sev on (t.severity = sev.sevname)
528 'milestone' => 'm.name',
534 foreach ($params as $k => $v) {
535 list($op, $values) = $v;
537 if (isset($critmap[$k])) {
545 $op = substr($op, 1);
551 if ($k == 't.tid' && count($values) == 1 &&
552 preg_match('/[,-]/', $values[0])) {
555 foreach (explode(',', $values[0]) as $range) {
556 list($rfrom, $rto) = explode('-', $range, 2);
558 if (!ctype_digit($rfrom)) {
559 $rfrom = MTrackDB::esc($rfrom);
563 if (!ctype_digit($rto)) {
564 $rto = MTrackDB::esc($rto);
567 $crit[] = "(cast(t.tid as $type) between $rfrom and $rto)";
568 $crit[] = "(cast(t.nsident as $type) between $rfrom and $rto)";
570 $crit[] = "(t.tid = $rfrom)";
571 $crit[] = "(t.nsident = $rfrom)";
574 $sql .= join(' OR ', $crit);
575 } else if (count($values) == 1) {
576 $sql .= " $k = " . MTrackDB::esc($values[0]) . " ";
580 foreach ($values as $i => $val) {
581 $values[$i] = MTrackDB::esc($val);
583 $sql .= join(', ', $values) . ") ";
586 /* variations on like */
590 } else if ($op == '^=') {
600 foreach ($values as $val) {
601 $crit[] = "($k LIKE " . MTrackDB::esc("$start$val$end") . ")";
603 $sql .= join(" OR ", $crit);
609 if (isset($mparams['group'])) {
610 $g = $mparams['group'][0];
611 if (!ctype_alpha($g)) {
612 throw new Exception("group $g is not alpha");
614 $sql .= ' GROUP BY ' . $g;
617 if (isset($mparams['order'])) {
618 $k = $mparams['order'][0];
623 $sql .= ' ORDER BY ' . $k;
624 if (isset($mparams['desc']) && $mparams['desc'][0]) {
629 if (isset($mparams['max'])) {
630 $sql .= ' LIMIT ' . (int)$mparams['max'][0];
632 # return htmlentities($sql);
633 # return var_export($sql, true);
635 return self::renderReport($sql);