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; }