function get_list_summary($_FORM = array())
{
//$_FORM["fromDate"] = "2010-08-20";
//$_FORM["projectID"] = "22";
$_FORM["maxCommentLength"] or $_FORM["maxCommentLength"] = 500;
list($filter1, $filter2, $filter3) = comment::get_list_summary_filter($_FORM);
is_array($filter1) && count($filter1) and $filter1 = " AND " . implode(" AND ", $filter1);
is_array($filter2) && count($filter2) and $filter2 = " AND " . implode(" AND ", $filter2);
is_array($filter3) && count($filter3) and $filter3 = " AND " . implode(" AND ", $filter3);
if ($_FORM["clients"]) {
$client_join = " LEFT JOIN clientContact on comment.commentCreatedUserClientContactID = clientContact.clientContactID";
$client_fields = " , clientContact.clientContactName";
}
$q = prepare("SELECT commentID as id\n , commentCreatedUser as personID\n , UNIX_TIMESTAMP(commentCreatedTime) as sortDate\n , date(commentCreatedTime) as date\n , commentCreatedTime as displayDate\n , commentMasterID as taskID\n , task.taskName\n , SUBSTRING(comment.comment,1,%d) AS comment_text\n , commentCreatedUserText\n " . $client_fields . "\n FROM comment\n LEFT JOIN task on comment.commentMasterID = task.taskID\n " . $client_join . "\n WHERE commentMaster = 'task'\n " . $filter1 . "\n ORDER BY commentCreatedTime, commentCreatedUser", $_FORM["maxCommentLength"]);
$q .= " ";
$people =& get_cached_table("person");
$db = new db_alloc();
$db->query($q);
while ($row = $db->row()) {
$row["icon"] = 'icon-comments-alt';
$row["id"] = "comment_" . $row["id"];
$row["personID"] and $row["person"] = $people[$row["personID"]]["name"];
$row["clientContactName"] and $row["person"] = $row["clientContactName"];
$row["person"] or list($e, $row["person"]) = parse_email_address($row["commentCreatedUserText"]);
$row["displayDate"] = format_date("Y-m-d g:ia", $row["displayDate"]);
if (!$tasks[$row["taskID"]]) {
$t = new task();
$t->set_id($row["taskID"]);
$t->set_value("taskName", $row["taskName"]);
$tasks[$row["taskID"]] = $t->get_task_link(array("prefixTaskID" => true));
}
$rows[$row["taskID"]][$row["sortDate"]][] = $row;
}
// Note that timeSheetItemID is selected twice so that the perms checking can work
// timeSheetID is also used by the perms checking.
$q2 = prepare("SELECT timeSheetItemID as id\n ,timeSheetItemID\n ,timeSheetID\n ,timeSheetItem.personID\n ,dateTimeSheetItem as date\n ,UNIX_TIMESTAMP(CONCAT(dateTimeSheetItem, ' 23:59:58')) as sortDate\n ,dateTimeSheetItem as displayDate\n ,timeSheetItem.taskID\n ,task.taskName\n ,timeSheetItemDuration as duration\n ,SUBSTRING(timeSheetItem.comment,1,%d) AS comment_text\n FROM timeSheetItem\n LEFT JOIN task on timeSheetItem.taskID = task.taskID\n WHERE 1\n " . $filter2 . "\n ORDER BY dateTimeSheetItem", $_FORM["maxCommentLength"]);
$db->query($q2);
while ($row = $db->row()) {
$timeSheetItem = new timeSheetItem();
if (!$timeSheetItem->read_row_record($row)) {
continue;
}
$row["icon"] = 'icon-time';
$row["id"] = "timeitem_" . $row["id"];
$row["person"] = $people[$row["personID"]]["name"];
if (!$tasks[$row["taskID"]]) {
$t = new task();
$t->set_id($row["taskID"]);
$t->set_value("taskName", $row["taskName"]);
$tasks[$row["taskID"]] = $t->get_task_link(array("prefixTaskID" => true));
}
$totals[$row["taskID"]] += $row["duration"];
$rows[$row["taskID"]][$row["sortDate"]][] = $row;
}
// get manager's guestimates about time worked from tsiHint table
$q3 = prepare("SELECT tsiHintID as id\n ,tsiHintID\n ,tsiHint.personID\n ,tsiHint.date\n ,UNIX_TIMESTAMP(CONCAT(tsiHint.date,' 23:59:59')) as sortDate\n ,tsiHint.date as displayDate\n ,tsiHint.taskID\n ,tsiHint.duration\n ,tsiHint.tsiHintCreatedUser\n ,SUBSTRING(tsiHint.comment,1,%d) AS comment_text\n ,task.taskName\n FROM tsiHint\n LEFT JOIN task on tsiHint.taskID = task.taskID\n WHERE 1\n " . $filter3 . "\n ORDER BY tsiHint.date", $_FORM["maxCommentLength"]);
$db->query($q3);
while ($row = $db->row()) {
//$tsiHint = new tsiHint();
//if (!$tsiHint->read_row_record($row))
// continue;
$row["icon"] = 'icon-bookmark-empty';
$row["id"] = "tsihint_" . $row["id"];
$row["person"] = $people[$row["personID"]]["name"];
$row["comment_text"] .= ' [by ' . $people[$row["tsiHintCreatedUser"]]["name"] . ']';
if (!$tasks[$row["taskID"]]) {
$t = new task();
$t->set_id($row["taskID"]);
$t->set_value("taskName", $row["taskName"]);
$tasks[$row["taskID"]] = $t->get_task_link(array("prefixTaskID" => true));
}
$totals_tsiHint[$row["taskID"]] += $row["duration"];
$rows[$row["taskID"]][$row["sortDate"]][] = $row;
}
// If there is a time sheet entry for 2010-10-10 but there is no comment entry
// for that date, then the time sheet entry will appear out of sequence i.e. at
// the very end of the whole list. So we need to manually sort them.
foreach ((array) $rows as $tid => $arr) {
ksort($arr, SORT_NUMERIC);
$rows[$tid] = $arr;
}
foreach ((array) $rows as $taskID => $dates) {
$rtn .= comment::get_list_summary_header($tasks[$taskID], $totals[$taskID], $totals_tsiHint[$taskID], $_FORM);
foreach ($dates as $date => $more_rows) {
foreach ($more_rows as $row) {
$rtn .= comment::get_list_summary_body($row);
}
}
$rtn .= comment::get_list_summary_footer($rows, $tasks);
}
return $rtn;
}
