function get_list_summary_filter($filter = array())
{
// This takes care of projectID singular and plural
has("project") and $projectIDs = project::get_projectID_sql($filter, "task");
$projectIDs and $sql1["projectIDs"] = $projectIDs;
$projectIDs and $sql2["projectIDs"] = $projectIDs;
$filter['taskID'] and $sql1[] = prepare("(task.taskID = %d)", $filter["taskID"]);
$filter['taskID'] and $sql2[] = prepare("(task.taskID = %d)", $filter["taskID"]);
$filter['taskID'] and $sql3[] = prepare("(tsiHint.taskID = %d)", $filter["taskID"]);
$filter["fromDate"] and $sql1[] = prepare("(date(commentCreatedTime) >= '%s')", $filter["fromDate"]);
$filter["fromDate"] and $sql2[] = prepare("(dateTimeSheetItem >= '%s')", $filter["fromDate"]);
$filter["fromDate"] and $sql3[] = prepare("(tsiHint.date >= '%s')", $filter["fromDate"]);
$filter["toDate"] and $sql1[] = prepare("(date(commentCreatedTime) < '%s')", $filter["toDate"]);
$filter["toDate"] and $sql2[] = prepare("(dateTimeSheetItem < '%s')", $filter["toDate"]);
$filter["toDate"] and $sql3[] = prepare("(tsiHint.date < '%s')", $filter["toDate"]);
$filter["personID"] and $sql1["personID"] = prepare("(comment.commentCreatedUser IN (%s))", $filter["personID"]);
$filter["personID"] and $sql2[] = prepare("(timeSheetItem.personID IN (%s))", $filter["personID"]);
$filter["personID"] and $sql3[] = prepare("(tsiHint.personID IN (%s))", $filter["personID"]);
$filter["clients"] or $sql1[] = "(commentCreatedUser IS NOT NULL)";
$filter["clients"] && $filter["personID"] and $sql1["personID"] = prepare("(comment.commentCreatedUser IN (%s) OR comment.commentCreatedUser IS NULL)", $filter["personID"]);
$filter["taskStatus"] and $sql1[] = task::get_taskStatus_sql($filter["taskStatus"]);
$filter["taskStatus"] and $sql2[] = task::get_taskStatus_sql($filter["taskStatus"]);
return array($sql1, $sql2, $sql3);
}
public static function get_list_filter($filter = array())
{
$current_user =& singleton("current_user");
// If they want starred, load up the taskID filter element
if ($filter["starred"]) {
foreach ((array) $current_user->prefs["stars"]["task"] as $k => $v) {
$filter["taskID"][] = $k;
}
is_array($filter["taskID"]) or $filter["taskID"][] = -1;
}
// Filter on taskID
$filter["taskID"] and $sql[] = sprintf_implode("task.taskID = %d", $filter["taskID"]);
// No point continuing if primary key specified, so return
if ($filter["taskID"]) {
return array($sql, "");
}
// This takes care of projectID singular and plural
has("project") and $projectIDs = project::get_projectID_sql($filter);
$projectIDs and $sql["projectIDs"] = $projectIDs;
// project name or project nick name or project id
$filter["projectNameMatches"] and $sql[] = sprintf_implode("project.projectName LIKE '%%%s%%'\n OR project.projectShortName LIKE '%%%s%%'\n OR project.projectID = %d", $filter["projectNameMatches"], $filter["projectNameMatches"], $filter["projectNameMatches"]);
list($ts_open, $ts_pending, $ts_closed) = task::get_task_status_in_set_sql();
// New Tasks
if ($filter["taskDate"] == "new") {
$past = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 2, date("Y"))) . " 00:00:00";
date("D") == "Mon" and $past = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 4, date("Y"))) . " 00:00:00";
$sql[] = prepare("(task.taskStatus NOT IN (" . $ts_closed . ") AND task.dateCreated >= '" . $past . "')");
// Due Today
} else {
if ($filter["taskDate"] == "due_today") {
$sql[] = "(task.taskStatus NOT IN (" . $ts_closed . ") AND task.dateTargetCompletion = '" . date("Y-m-d") . "')";
// Overdue
} else {
if ($filter["taskDate"] == "overdue") {
$sql[] = "(task.taskStatus NOT IN (" . $ts_closed . ")\n AND \n (task.dateTargetCompletion IS NOT NULL AND task.dateTargetCompletion != '' AND '" . date("Y-m-d") . "' > task.dateTargetCompletion))";
// Date Created
} else {
if ($filter["taskDate"] == "d_created") {
$filter["dateOne"] and $sql[] = prepare("(task.dateCreated >= '%s')", $filter["dateOne"]);
$filter["dateTwo"] and $sql[] = prepare("(task.dateCreated <= '%s 23:59:59')", $filter["dateTwo"]);
// Date Assigned
} else {
if ($filter["taskDate"] == "d_assigned") {
$filter["dateOne"] and $sql[] = prepare("(task.dateAssigned >= '%s')", $filter["dateOne"]);
$filter["dateTwo"] and $sql[] = prepare("(task.dateAssigned <= '%s 23:59:59')", $filter["dateTwo"]);
// Date Target Start
} else {
if ($filter["taskDate"] == "d_targetStart") {
$filter["dateOne"] and $sql[] = prepare("(task.dateTargetStart >= '%s')", $filter["dateOne"]);
$filter["dateTwo"] and $sql[] = prepare("(task.dateTargetStart <= '%s')", $filter["dateTwo"]);
// Date Target Completion
} else {
if ($filter["taskDate"] == "d_targetCompletion") {
$filter["dateOne"] and $sql[] = prepare("(task.dateTargetCompletion >= '%s')", $filter["dateOne"]);
$filter["dateTwo"] and $sql[] = prepare("(task.dateTargetCompletion <= '%s')", $filter["dateTwo"]);
// Date Actual Start
} else {
if ($filter["taskDate"] == "d_actualStart") {
$filter["dateOne"] and $sql[] = prepare("(task.dateActualStart >= '%s')", $filter["dateOne"]);
$filter["dateTwo"] and $sql[] = prepare("(task.dateActualStart <= '%s')", $filter["dateTwo"]);
// Date Actual Completion
} else {
if ($filter["taskDate"] == "d_actualCompletion") {
$filter["dateOne"] and $sql[] = prepare("(task.dateActualCompletion >= '%s')", $filter["dateOne"]);
$filter["dateTwo"] and $sql[] = prepare("(task.dateActualCompletion <= '%s')", $filter["dateTwo"]);
}
}
}
}
}
}
}
}
}
// Task status filtering
$filter["taskStatus"] and $sql[] = task::get_taskStatus_sql($filter["taskStatus"]);
$filter["taskTypeID"] and $sql[] = sprintf_implode("task.taskTypeID = '%s'", $filter["taskTypeID"]);
// Filter on %taskName%
$filter["taskName"] and $sql[] = sprintf_implode("task.taskName LIKE '%%%s%%'", $filter["taskName"]);
// If personID filter
$filter["personID"] and $sql["personID"] = sprintf_implode("IFNULL(task.personID,0) = %d", $filter["personID"]);
$filter["creatorID"] and $sql["creatorID"] = sprintf_implode("IFNULL(task.creatorID,0) = %d", $filter["creatorID"]);
$filter["managerID"] and $sql["managerID"] = sprintf_implode("IFNULL(task.managerID,0) = %d", $filter["managerID"]);
// If tags filter
if ($filter["tags"] && is_array($filter["tags"])) {
foreach ((array) $filter["tags"] as $k => $tag) {
$tag and $tags[] = $tag;
}
$tags and $sql[] = sprintf_implode("seltag.name = '%s'", $tags);
$having = prepare("HAVING count(DISTINCT seltag.name) = %d", count($tags));
}
// These filters are for the time sheet dropdown list
if ($filter["taskTimeSheetStatus"] == "open") {
unset($sql["personID"]);
$sql[] = prepare("(task.taskStatus NOT IN (" . $ts_closed . "))");
} else {
if ($filter["taskTimeSheetStatus"] == "mine") {
$current_user =& singleton("current_user");
unset($sql["personID"]);
$sql[] = prepare("((task.taskStatus NOT IN (" . $ts_closed . ")) AND task.personID = %d)", $current_user->get_id());
} else {
if ($filter["taskTimeSheetStatus"] == "not_assigned") {
unset($sql["personID"]);
$sql[] = prepare("((task.taskStatus NOT IN (" . $ts_closed . ")) AND task.personID != %d)", $filter["personID"]);
} else {
if ($filter["taskTimeSheetStatus"] == "recent_closed") {
unset($sql["personID"]);
$sql[] = prepare("(task.dateActualCompletion >= DATE_SUB(CURDATE(),INTERVAL 14 DAY))");
} else {
if ($filter["taskTimeSheetStatus"] == "all") {
}
}
}
}
}
$filter["parentTaskID"] and $sql["parentTaskID"] = sprintf_implode("IFNULL(task.parentTaskID,0) = %d", $filter["parentTaskID"]);
return array($sql, $having);
}
