function getProjects($f_order_by = NULL, $f_status_min = 1, $f_status_max = 4)
{
global $auth;
$prefix = confGet('DB_TABLE_PREFIX');
$status_min = intval($f_status_min);
$status_max = intval($f_status_max);
#"SELECT * FROM {$prefix}project WHERE company = \"$this->id\" AND state=1 ORDER BY name"
### all projects ###
if ($auth->cur_user->user_rights & RIGHT_PROJECT_ASSIGN) {
$str = "SELECT p.* from {$prefix}project p\r\n WHERE\r\n p.status <= {$status_max}\r\n AND p.status >= {$status_min}\r\n AND p.company = {$this->id}\r\n AND p.state = 1\r\n\r\n " . getOrderByString($f_order_by, 'name');
} else {
$str = "SELECT p.* from {$prefix}project p, {$prefix}projectperson upp\r\n WHERE\r\n upp.person = {$auth->cur_user->id}\r\n AND upp.state = 1 /* all projectpeople of user */\r\n\r\n\r\n AND p.id = upp.project /* all projects of user */\r\n AND p.company = {$this->id} /* all project of this company */\r\n AND p.status <= {$status_max}\r\n AND p.status >= {$status_min}\r\n AND p.state = 1\r\n\r\n " . getOrderByString($f_order_by, 'name');
}
$dbh = new DB_Mysql();
$sth = $dbh->prepare($str);
$sth->execute("", 1);
$tmp = $sth->fetchall_assoc();
$projects = array();
foreach ($tmp as $t) {
$projects[] = new Project($t);
}
return $projects;
}
static function getItemChanges($args = NULL)
{
global $auth;
$prefix = confGet('DB_TABLE_PREFIX');
### default params ###
$item = NULL;
$date_min = NULL;
$date_max = NULL;
$person = NULL;
$field = NULL;
$project = NULL;
$order_by = 'modified';
### filter params ###
if ($args) {
foreach ($args as $key => $value) {
if (!isset(${$key}) && !is_null(${$key}) && !${$key} === "") {
trigger_error("unknown parameter", E_USER_NOTICE);
} else {
${$key} = $value;
}
}
}
$str_project = $project ? "AND c.project= " . intval($project) : '';
$str_item = $item ? "AND c.item=" . intval($item) : '';
$str_date_min = $date_min ? "AND c.modified >= '" . asCleanString($date_min) . "'" : '';
$str_date_max = $date_max ? "AND c.modified <= '" . asCleanString($date_max) . "'" : '';
$str_field = $field ? "AND c.field ='" . asCleanString($field) . "'" : '';
$str_person = $person ? "AND c.modified_by = " . intval($person) : '';
### show all ###
$str_query = "SELECT c.* from {$prefix}itemchange c\r\n WHERE 1\r\n {$str_project}\r\n {$str_item}\r\n {$str_person}\r\n {$str_field}\r\n {$str_date_max}\r\n {$str_date_min}\r\n " . getOrderByString($order_by);
$dbh = new DB_Mysql();
$sth = $dbh->prepare($str_query);
$sth->execute("", 1);
$tmp = $sth->fetchall_assoc();
$item_changes = array();
foreach ($tmp as $t) {
$c = new ItemChange($t);
$item_changes[] = $c;
}
return $item_changes;
}
/**
* return tasks of project
*
*
*
* @params
* show_folders=true,
* order_by=NULL,
* status_min=2,
* status_max=4,
* visible_only=true,
* alive_only=true,
* parent_task=NULL) # if NULL parent-task is ignored
*/
static function getAll($args = NULL)
{
global $auth;
$prefix = confGet('DB_TABLE_PREFIX');
### default params ###
$project = NULL;
$show_folders = true;
$order_by = "is_folder DESC, parent_task, prio ASC,project,name";
$status_min = STATUS_NEW;
$status_max = STATUS_BLOCKED;
$visible_only = NULL;
# use project rights settings
$alive_only = true;
# ignore deleted
$parent_task = NULL;
#
$sort_hierarchical = false;
$use_collapsed = false;
# by default ignore collapsed folders
$issues_only = false;
$folders_only = false;
$level = 0;
# hierarchical depth in trees
$assigned_to_person = 0;
# skip by default
$search = NULL;
$name = NULL;
$is_milestone = NULL;
$for_milestone = NULL;
$resolved_version = NULL;
$is_released_min = NULL;
$is_released_max = NULL;
$id = NULL;
$modified_by = NULL;
$not_modified_by = NULL;
$resolve_reason_min = NULL;
$category = NULL;
$category_in = NULL;
$label = NULL;
$person = 0;
$is_news = NULL;
### filter params ###
if ($args) {
foreach ($args as $key => $value) {
if (!isset(${$key}) && !is_null(${$key}) && !${$key} === "") {
trigger_error("unknown parameter", E_USER_NOTICE);
} else {
${$key} = $value;
}
}
}
if ($sort_hierarchical && is_null($parent_task)) {
$parent_task = 0;
}
$str_project = $project ? 'AND upp.project=' . intval($project) : '';
$str_project2 = $project ? 'AND i.project=' . intval($project) : '';
$str_is_alive = $alive_only ? 'AND i.state=' . ITEM_STATE_OK : '';
$str_is_issue = $issues_only ? 'AND t.issue_report!=0' : '';
$str_is_folder = $show_folders ? '' : 'AND t.is_folder=0';
$str_modified_by = $modified_by ? 'AND i.modified_by =' . intval($modified_by) : '';
$str_not_modified_by = $not_modified_by ? 'AND i.modified_by !=' . intval($not_modified_by) : '';
$str_id = $id ? 'AND t.id=' . intval($id) : '';
if (!is_null($label)) {
$str_label = 'AND t.label=' . intval($label);
} else {
$str_label = '';
}
if (!is_null($is_milestone)) {
$str_is_milestone = $is_milestone ? 'AND t.is_milestone=1' : 'AND t.is_milestone=0';
} else {
$str_is_milestone = '';
}
if (!is_null($category)) {
$str_category = 'AND t.category=' . intval($category);
} else {
$str_category = '';
}
if (!is_null($category_in)) {
$clean_array = array();
foreach ($category_in as $c) {
$clean_array[] = intval($c);
}
$str_category_in = 'AND t.category IN(' . join(",", $clean_array) . ')';
} else {
$str_category_in = '';
}
if (!is_null($is_news)) {
$str_is_news = 'AND t.is_news=' . intval($is_news);
} else {
$str_is_news = '';
}
$str_is_released_min = $is_released_min ? 'AND t.is_released >= ' . intval($is_released_min) : '';
if ($resolve_reason_min !== NULL) {
$str_resolve_reason_min = $resolve_reason_min ? 'AND t.resovle_reason >= ' . intval($resolve_reason_min) : '';
} else {
$str_reasolve_reason_min = '';
}
$str_is_released_max = $is_released_max ? 'AND t.is_released <= ' . intval($is_released_max) : '';
$str_has_name = $name ? "AND (t.name='" . asSecureString($name) . "' or t.short='" . asSecureString($name) . "')" : "";
if (!is_null($for_milestone)) {
$str_for_milestone = 'AND t.for_milestone=' . intval($for_milestone);
} else {
$str_for_milestone = '';
}
if (!is_null($resolved_version)) {
$str_resolved_version = 'AND t.resolved_version=' . intval($resolved_version);
} else {
$str_resolved_version = '';
}
if ($folders_only) {
$str_is_folder = 'AND t.is_folder=1';
}
$str_parent_task = !is_null($parent_task) ? 'AND t.parent_task=' . intval($parent_task) : '';
$str_match = $search ? "AND MATCH (t.name,t.short,t.description) AGAINST ('" . asMatchString($search) . "*' IN BOOLEAN MODE)" : '';
$str_person = $person ? $person : $auth->cur_user->id;
if (is_null($visible_only)) {
$visible_only = $auth->cur_user && $auth->cur_user->user_rights & RIGHT_VIEWALL ? false : true;
}
if ($visible_only) {
### only filter assigned to person ###
if ($assigned_to_person) {
$str_query = "SELECT i.*, t.* from {$prefix}item i, {$prefix}task t, {$prefix}taskperson tp, {$prefix}projectperson upp, {$prefix}item itp\r\n WHERE\r\n\r\n upp.person = {$auth->cur_user->id}\r\n\t\t\t\t\t/*upp.person = {$str_person}*/\r\n {$str_project}\r\n AND i.type = '" . ITEM_TASK . "'\r\n AND i.project=upp.project\r\n {$str_is_alive}\r\n {$str_project2}\r\n {$str_modified_by}\r\n {$str_not_modified_by}\r\n\r\n {$str_is_issue}\r\n\r\n AND ( i.pub_level >= upp.level_view\r\n OR\r\n /*i.created_by = {$auth->cur_user->id}*/\r\n\t\t\t\t\t\t i.created_by = {$str_person}\r\n )\r\n\r\n AND t.id = i.id\r\n {$str_id}\r\n {$str_category}\r\n {$str_category_in}\r\n {$str_is_folder}\r\n {$str_is_issue}\r\n {$str_label}\r\n {$str_parent_task}\r\n {$str_has_name}\r\n {$str_is_milestone}\r\n {$str_is_released_min}\r\n {$str_is_released_max}\r\n {$str_for_milestone}\r\n {$str_resolved_version}\r\n {$str_is_news}\r\n AND t.status >= " . intval($status_min) . "\r\n AND t.status <= " . intval($status_max) . "\r\n\r\n AND i.id = tp.task\r\n AND tp.person = " . intval($assigned_to_person) . "\r\n AND itp.id = tp.id\r\n AND itp.state = 1\r\n " . "\r\n {$str_match}\r\n\r\n " . getOrderByString($order_by);
} else {
$str_query = "SELECT i.*, t.* from {$prefix}item i, {$prefix}task t, {$prefix}projectperson upp\r\n WHERE\r\n /*upp.person = {$auth->cur_user->id}*/\r\n\t\t\t\t\t\tupp.person = {$str_person}\r\n {$str_project}\r\n AND i.type = '" . ITEM_TASK . "'\r\n AND i.project = upp.project\r\n {$str_is_alive}\r\n {$str_project2}\r\n {$str_category}\r\n {$str_category_in}\r\n {$str_modified_by}\r\n {$str_not_modified_by}\r\n {$str_is_issue}\r\n {$str_is_milestone}\r\n {$str_is_released_min}\r\n {$str_is_released_max}\r\n {$str_for_milestone}\r\n {$str_label}\r\n {$str_resolved_version}\r\n {$str_is_news}\r\n AND ( i.pub_level >= upp.level_view\r\n OR\r\n /*i.created_by = {$auth->cur_user->id}*/\r\n\t\t\t\t\t\t i.created_by = {$str_person}\r\n )\r\n\r\n AND t.id = i.id\r\n {$str_is_folder}\r\n {$str_is_issue}\r\n {$str_parent_task}\r\n {$str_has_name}\r\n {$str_id}\r\n AND t.status >= " . intval($status_min) . "\r\n AND t.status <= " . intval($status_max) . "\r\n {$str_match}\r\n\r\n " . getOrderByString($order_by);
}
} else {
if ($assigned_to_person) {
$str_query = "SELECT i.*, t.* from {$prefix}item i, {$prefix}task t, {$prefix}taskperson tp ,{$prefix}item itp\r\n WHERE\r\n i.type = '" . ITEM_TASK . "'\r\n {$str_project2}\r\n {$str_is_alive}\r\n {$str_modified_by}\r\n {$str_not_modified_by}\r\n\r\n AND t.id = i.id\r\n {$str_id}\r\n {$str_is_folder}\r\n {$str_is_issue}\r\n {$str_category}\r\n {$str_category_in}\r\n {$str_parent_task}\r\n {$str_has_name}\r\n {$str_label}\r\n {$str_is_milestone}\r\n {$str_is_released_min}\r\n {$str_is_released_max}\r\n {$str_for_milestone}\r\n {$str_is_news}\r\n {$str_resolved_version}\r\n AND t.status >= " . intval($status_min) . "\r\n AND t.status <= " . intval($status_max) . "\r\n {$str_match}\r\n AND i.id = tp.task\r\n AND tp.person = " . intval($assigned_to_person) . "\r\n AND tp.id = itp.id\r\n AND itp.state = 1\r\n " . getOrderByString($order_by);
} else {
$str_query = "SELECT i.*, t.* from {$prefix}item i, {$prefix}task t\r\n WHERE\r\n i.type = '" . ITEM_TASK . "'\r\n {$str_project2}\r\n {$str_is_alive}\r\n {$str_modified_by}\r\n {$str_not_modified_by}\r\n\r\n AND t.id = i.id\r\n {$str_category}\r\n {$str_category_in}\r\n {$str_id}\r\n {$str_is_folder}\r\n {$str_is_issue}\r\n {$str_is_milestone}\r\n {$str_for_milestone}\r\n {$str_label}\r\n {$str_resolved_version}\r\n {$str_is_news}\r\n\r\n {$str_is_released_min}\r\n {$str_is_released_max}\r\n {$str_parent_task}\r\n {$str_has_name}\r\n AND t.status >= " . intval($status_min) . "\r\n AND t.status <= " . intval($status_max) . "\r\n {$str_match}\r\n\r\n " . getOrderByString($order_by);
}
}
$dbh = new DB_Mysql();
$sth = $dbh->prepare($str_query);
$sth->execute("", 1);
$tmp = $sth->fetchall_assoc();
$tasks = array();
foreach ($tmp as $t) {
$task = new Task($t);
$task->level = $level;
$tasks[] = $task;
### hierarchical / recursive sorting ###
if ($sort_hierarchical && $task->is_folder && (!$use_collapsed || !$task->view_collapsed)) {
if ($sub_tasks = Task::getAll(array('sort_hierarchical' => true, 'use_collapsed' => $use_collapsed, 'parent_task' => $task->id, 'order_by' => $order_by, 'visible_only' => $visible_only, 'alive_only' => $alive_only, 'issues_only' => $issues_only, 'status_min' => $status_min, 'status_max' => $status_max, 'level' => $level + 1, 'folders_only' => $folders_only, 'project' => $project))) {
foreach ($sub_tasks as &$st) {
$tasks[] = $st;
}
}
}
}
return $tasks;
}
/**
* return files attached to project
* @@@ todo:
* - refacture status_min/max evaluation only if !is_null
*
*/
static function getAll($args = NULL)
{
global $auth;
$prefix = confGet('DB_TABLE_PREFIX');
### default params ###
$project = NULL;
$latest_only = true;
$order_by = "name";
$status_min = STATUS_UNDEFINED;
$status_max = STATUS_CLOSED;
$visible_only = true;
# use project rights settings
$alive_only = true;
# ignore deleted
$parent_item = NULL;
#
$images_only = false;
$date_min = NULL;
$date_max = NULL;
$org_file = NULL;
$id = NULL;
$created_by = NULL;
### filter params ###
if ($args) {
foreach ($args as $key => $value) {
if (!isset(${$key}) && !is_null(${$key}) && !${$key} === "") {
trigger_error("unknown parameter", E_USER_NOTICE);
} else {
${$key} = $value;
}
}
}
$str_project = $project ? 'AND i.project=' . intval($project) : '';
$str_project2 = $project ? 'AND upp.project=' . intval($project) : '';
$str_is_alive = $alive_only ? 'AND i.state=' . ITEM_STATE_OK : '';
$str_date_min = $date_min ? "AND i.modified >= '" . asCleanString($date_min) . "'" : '';
$str_date_max = $date_max ? "AND i.modified <= ' " . asCleanString($date_max) . "'" : '';
$str_is_image = $images_only ? 'AND f.is_image!=0' : '';
$str_latest_only = $latest_only ? 'AND f.is_latest!=0' : '';
$str_created_by = $created_by ? 'AND i.modified_by =' . intval($created_by) : '';
$str_parent_item = !is_null($parent_item) ? 'AND f.parent_item=' . intval($parent_item) : '';
$str_org_file = $org_file ? "AND f.org_file = '" . intval($org_file) . "'" : "";
$str_id = $id ? "AND f.id = " . intval($id) : "";
if ($auth->cur_user->user_rights & RIGHT_VIEWALL) {
$str_projectperson = "";
} else {
$str_projectperson = "AND upp.person = {$auth->cur_user->id}";
}
if ($visible_only) {
$str_query = "SELECT DISTINCT i.*, f.* from {$prefix}item i, {$prefix}file f, {$prefix}projectperson upp\r\n WHERE\r\n i.type = '" . ITEM_FILE . "'\r\n {$str_project}\r\n {$str_projectperson}\r\n {$str_project2}\r\n\r\n {$str_is_alive}\r\n AND ( i.pub_level >= upp.level_view\r\n OR\r\n i.created_by = {$auth->cur_user->id}\r\n )\r\n\r\n AND i.id = f.id\r\n\t\t\t\t {$str_id}\r\n {$str_created_by}\r\n {$str_is_image}\r\n {$str_parent_item}\r\n {$str_org_file}\r\n {$str_latest_only}\r\n AND f.status >= {$status_min}\r\n AND f.status <= {$status_max}\r\n {$str_date_max}\r\n {$str_date_min}\r\n\r\n " . getOrderByString($order_by);
} else {
$str_query = "SELECT i.*, f.* from {$prefix}item i, {$prefix}file f\r\n WHERE\r\n i.type = '" . ITEM_FILE . "'\r\n {$str_project}\r\n {$str_is_alive}\r\n\r\n AND i.id = f.id\r\n\t\t\t {$str_id}\r\n {$str_created_by}\r\n {$str_parent_item}\r\n {$str_latest_only}\r\n AND f.status >= {$status_min}\r\n AND f.status <= {$status_max}\r\n {$str_org_file}\r\n {$str_date_max}\r\n {$str_date_min}\r\n\r\n " . getOrderByString($order_by);
}
$dbh = new DB_Mysql();
$sth = $dbh->prepare($str_query);
$sth->execute("", 1);
$tmp = $sth->fetchall_assoc();
$files = array();
require_once confGet('DIR_STREBER') . 'db/class_file.inc.php';
foreach ($tmp as $t) {
$file = new File($t);
$files[] = $file;
}
return $files;
}
/**
* get list of items from database
*
* This function is used for getting changed items for projects or by user, etc.
*/
static function getAll($args = array())
{
global $auth;
$prefix = confGet('DB_TABLE_PREFIX');
### default params ###
$project = NULL;
$order_by = "modified DESC";
$status_min = STATUS_UNDEFINED;
$status_max = STATUS_CLOSED;
$visible_only = NULL;
# use project rights settings
$alive_only = true;
# hide deleted
$date_min = NULL;
$date_max = NULL;
$modified_by = NULL;
$not_modified_by = NULL;
$show_issues = false;
$limit_rowcount = NULL;
$limit_offset = NULL;
$unviewed_only = NULL;
$type = NULL;
### filter params ###
if ($args) {
foreach ($args as $key => $value) {
if (!isset(${$key}) && !is_null(${$key}) && !${$key} === "") {
trigger_error("unknown parameter", E_USER_NOTICE);
} else {
${$key} = $value;
}
}
}
$str_show_issues = $show_issues ? '' : 'AND i.type != ' . ITEM_ISSUE;
$str_project = $project ? 'AND i.project=' . intval($project) : '';
$str_project2 = $project ? 'AND upp.project=' . intval($project) : '';
$str_state = $alive_only ? 'AND i.state=' . ITEM_STATE_OK : '';
$str_date_min = $date_min ? "AND i.modified >= '" . asCleanString($date_min) . "'" : '';
$str_date_max = $date_max ? "AND i.modified <= '" . asCleanString($date_max) . "'" : '';
$str_modified_by = $modified_by ? 'AND i.modified_by=' . intval($modified_by) : '';
$str_not_modified_by = $not_modified_by ? 'AND i.modified_by != ' . intval($not_modified_by) : '';
if (is_array($type)) {
$str_type = "AND i.type in ( " . implode(",", $type) . ")";
} else {
$str_type = $type ? "AND i.type = {$type}" : "";
}
if (!is_null($limit_offset) && !is_null($limit_rowcount)) {
$str_limit = " LIMIT " . intval($limit_offset) . "," . intval($limit_rowcount);
} else {
if ($limit_rowcount) {
$str_limit = " LIMIT " . intval($limit_rowcount);
} else {
$str_limit = '';
}
}
if (is_null($visible_only)) {
$visible_only = $auth->cur_user && $auth->cur_user->user_rights & RIGHT_VIEWALL ? false : true;
}
### only visibile for current user ###
if ($visible_only) {
$s_query = "SELECT i.* from {$prefix}item i, {$prefix}projectperson upp\r\n WHERE\r\n upp.person = {$auth->cur_user->id}\r\n AND upp.state = 1\r\n AND upp.project = i.project\r\n {$str_state}\r\n {$str_type}\r\n {$str_show_issues}\r\n {$str_project}\r\n {$str_project2}\r\n {$str_modified_by}\r\n {$str_not_modified_by}\r\n {$str_date_min}\r\n {$str_date_max}\r\n\r\n AND ( i.pub_level >= upp.level_view\r\n OR\r\n i.created_by = {$auth->cur_user->id}\r\n )\r\n\r\n " . getOrderByString($order_by) . $str_limit;
} else {
$s_query = "SELECT i.* from\r\n {$prefix}item i\r\n WHERE 1\r\n\r\n {$str_state}\r\n {$str_type}\r\n {$str_project}\r\n {$str_show_issues}\r\n {$str_modified_by}\r\n {$str_not_modified_by}\r\n {$str_date_min}\r\n {$str_date_max}\r\n\r\n " . getOrderByString($order_by) . $str_limit;
}
require_once confGet('DIR_STREBER') . 'db/class_projectperson.inc.php';
$dbh = new DB_Mysql();
$sth = $dbh->prepare($s_query);
$sth->execute("", 1);
$tmp = $sth->fetchall_assoc();
$items = array();
if ($unviewed_only) {
require_once confGet('DIR_STREBER') . "db/db_itemperson.inc.php";
$viewed_items = array();
foreach (ItemPerson::getAll(array('person' => $auth->cur_user->id)) as $vi) {
$viewed_items[$vi->item] = $vi;
}
foreach ($tmp as $n) {
$item = new DbProjectItem($n);
if ($item->modified > $auth->cur_user->date_highlight_changes && (!isset($viewed_items[$item->id]) || $item->modified > $viewed_items[$item->id]->viewed_last)) {
$items[] = $item;
}
}
} else {
foreach ($tmp as $n) {
$item = new DbProjectItem($n);
$items[] = $item;
}
}
return $items;
}
/**
* get all open & Visible projects from db
*/
public static function getAll($args = NULL)
{
global $auth;
$prefix = confGet('DB_TABLE_PREFIX');
if ($args && !is_array($args)) {
trigger_error("requires array as parameter", E_USER_WARNING);
return;
}
### default params ###
$order_by = "prio, name";
$status_min = STATUS_UNDEFINED;
$status_max = STATUS_OPEN;
$company = NULL;
$visible_only = $auth->cur_user->user_rights & RIGHT_VIEWALL ? false : true;
$search = NULL;
$id = NULL;
$person = NULL;
$limit = NULL;
### filter params ###
if ($args) {
foreach ($args as $key => $value) {
if (!isset(${$key}) && !is_null(${$key}) && !${$key} === "") {
trigger_error("unknown parameter", E_USER_NOTICE);
} else {
${$key} = $value;
}
}
}
$AND_id = $id ? 'AND p.id=' . intval($id) : '';
$AND_match = $search ? "AND (MATCH (p.name,p.status_summary,p.description) AGAINST ('" . asMatchString($search) . "*' IN BOOLEAN MODE))" : '';
if (!is_null($company)) {
$AND_company = 'AND p.company=' . intval($company);
} else {
$AND_company = "";
}
if (!is_null($person)) {
$AND_person_all_part1 = " {$prefix}projectperson upp, ";
$AND_person_all_part2 = "AND upp.person = '" . intval($person) . "' \r\n AND upp.state = 1\r\n AND upp.project = p.id";
$AND_person_visible_part1 = " {$prefix}projectperson upp2, ";
$AND_person_visible_part2 = "AND upp.project = upp2.project\r\n AND upp2.person = '" . intval($person) . "'";
} else {
$AND_person_all_part1 = "";
$AND_person_all_part2 = "";
$AND_person_visible_part1 = "";
$AND_person_visible_part2 = "";
}
$str_limit = $limit ? " LIMIT " . intval($limit) . " " : "";
/**
* @@@ NOTE: using a distinct select here is not nice...
*/
### only assigned projects ###
if ($visible_only) {
$str = "SELECT DISTINCT i.*, p.* from {$prefix}item i, {$prefix}projectperson upp, {$AND_person_visible_part1} {$prefix}project p left join {$prefix}company c on p.company = c.id\r\n WHERE\r\n upp.person = '{$auth->cur_user->id}'\r\n AND upp.state = 1\r\n AND upp.project = p.id\r\n {$AND_person_visible_part2}\r\n AND p.status <= " . intval($status_max) . "\r\n AND p.status >= " . intval($status_min) . "\r\n AND p.state = 1\r\n AND i.id = p.id\r\n AND (p.company = c.id OR p.company = 0)\r\n {$AND_company}\r\n {$AND_match}\r\n {$AND_id}\r\n " . getOrderByString($order_by) . $str_limit;
} else {
$str = "SELECT DISTINCT i.*, p.* from {$prefix}item i, {$AND_person_all_part1} {$prefix}project p left join {$prefix}company c on p.company = c.id\r\n\r\n WHERE\r\n p.status <= " . intval($status_max) . "\r\n AND p.status >= " . intval($status_min) . "\r\n AND p.state = 1\r\n AND i.id = p.id\r\n AND (p.company = 0 OR p.company = c.id)\r\n {$AND_company}\r\n {$AND_match}\r\n {$AND_id}\r\n {$AND_person_all_part2}\r\n " . getOrderByString($order_by) . $str_limit;
}
$projects = self::queryFromDb($str);
return $projects;
}
/**
* return efforts depending on filter options
* @@@ todo:
* - refacture status_min/max evaluation only if !is_null
*
*/
static function getAll($args = NULL)
{
global $auth;
$prefix = confGet('DB_TABLE_PREFIX');
### default params ###
$project = NULL;
$person = NULL;
$order_by = 'e.time_start DESC';
$visible_only = true;
# use project rights settings
$alive_only = true;
# ignore deleted
$task = NULL;
# for a parent task?
$date_min = NULL;
$date_max = NULL;
$search = NULL;
# search query
$effort_status_min = NULL;
$effort_status_max = NULL;
$effort_time_min = NULL;
$effort_time_max = NULL;
### filter params ###
if ($args) {
foreach ($args as $key => $value) {
if (!isset(${$key}) && !is_null(${$key}) && !${$key} === "") {
trigger_error("unknown parameter", E_USER_NOTICE);
} else {
${$key} = $value;
}
}
}
$str_project = $project ? 'AND i.project=' . intval($project) : '';
$str_project2 = $project ? 'AND upp.project=' . intval($project) : '';
$str_is_alive = $alive_only ? 'AND i.state=' . ITEM_STATE_OK : '';
$str_date_min = $date_min ? "AND i.modified >= '" . asCleanString($date_min) . "'" : '';
$str_date_max = $date_max ? "AND i.modified <= ' " . asCleanString($date_max) . "'" : '';
$str_status_min = $effort_status_min ? "AND e.status >= '" . asCleanString($effort_status_min) . "'" : '';
$str_status_max = $effort_status_max ? "AND e.status <= ' " . asCleanString($effort_status_max) . "'" : '';
$str_effort_time_min = $effort_time_min ? "AND e.time_start >= ' " . asCleanString($effort_time_min) . "'" : '';
$str_effort_time_max = $effort_time_max ? "AND e.time_end <= ' " . asCleanString($effort_time_max) . "'" : '';
$str_task = !is_null($task) ? 'AND e.task=' . intval($task) : '';
$str_person = $person ? 'AND e.person=' . intval($person) : '';
if ($auth->cur_user->user_rights & RIGHT_VIEWALL) {
$str_projectperson = "";
} else {
$str_projectperson = "AND upp.person = {$auth->cur_user->id}";
}
$str_match = $search ? "AND MATCH (e.description) AGAINST ('" . asMatchString($search) . "*' IN BOOLEAN MODE)" : '';
/**
* note: project p required for sorting
*/
if ($visible_only) {
$str_query = "SELECT DISTINCT i.*, e.* from {$prefix}item i, {$prefix}effort e, {$prefix}project p, {$prefix}projectperson upp\r\n WHERE\r\n\r\n i.type = '" . ITEM_EFFORT . "'\r\n {$str_project}\r\n {$str_projectperson}\r\n {$str_project2}\r\n {$str_person}\r\n {$str_is_alive}\r\n AND ( i.pub_level >= upp.level_view\r\n OR\r\n i.created_by = {$auth->cur_user->id}\r\n )\r\n AND i.project = p.id\r\n\r\n AND i.id = e.id\r\n {$str_task}\r\n {$str_date_max}\r\n {$str_date_min}\r\n {$str_status_max}\r\n {$str_status_min}\r\n {$str_effort_time_min}\r\n {$str_effort_time_max}\r\n {$str_match}\r\n\r\n " . getOrderByString($order_by);
} else {
$str_query = "SELECT i.*, e.* from {$prefix}item i, {$prefix}effort e, {$prefix}project p\r\n WHERE\r\n i.type = '" . ITEM_EFFORT . "'\r\n {$str_project}\r\n {$str_is_alive}\r\n\r\n AND i.project = p.id\r\n\r\n AND i.id = e.id\r\n {$str_task}\r\n {$str_date_max}\r\n {$str_date_min}\r\n {$str_match}\r\n\r\n " . getOrderByString($order_by);
}
$dbh = new DB_Mysql();
$sth = $dbh->prepare($str_query);
$sth->execute("", 1);
$tmp = $sth->fetchall_assoc();
$efforts = array();
foreach ($tmp as $t) {
$effort = new Effort($t);
$efforts[] = $effort;
}
return $efforts;
}
/**
* getComments($project=false)
*/
static function getAll($args = array())
{
global $auth;
$prefix = confGet('DB_TABLE_PREFIX');
require_once confGet('DIR_STREBER') . 'db/class_comment.inc.php';
### default params ###
$order_by = 'c.name';
$visible_only = true;
# use project rights settings
$alive_only = true;
# ignore deleted
$project = NULL;
$task = NULL;
$person = NULL;
$date_min = NULL;
$date_max = NULL;
$search = NULL;
$parent_comment = NULL;
### filter params ###
if ($args) {
foreach ($args as $key => $value) {
if (!isset(${$key}) && !is_null(${$key}) && !${$key} === "") {
trigger_error("unknown parameter", E_USER_NOTICE);
} else {
${$key} = $value;
}
}
}
$dbh = new DB_Mysql();
$str_is_alive = $alive_only ? 'AND i.state=1' : '';
$AND_person = $person ? 'AND i.created_by=' . intval($person) : '';
$AND_task = $task ? 'AND c.task=' . intval($task) : '';
$AND_match = $search ? "AND (MATCH (c.name,c.description) AGAINST ('" . asMatchString($search) . "*' IN BOOLEAN MODE))" : '';
$AND_project1 = $project ? "AND upp.project= {$project}" : "";
$AND_project2 = $project ? "AND i.project= {$project}" : "";
$AND_date_min = $date_min ? "AND i.modified >= '" . asCleanString($date_min) . "'" : '';
$AND_date_max = $date_max ? "AND i.modified <= '" . asCleanString($date_max) . "'" : '';
if (!is_null($parent_comment)) {
$AND_comment = 'AND c.comment = ' . intval($parent_comment);
} else {
$AND_comment = '';
}
if ($visible_only) {
$str_query = "SELECT i.*, c.* from {$prefix}item i, {$prefix}comment c, {$prefix}projectperson upp\r\n WHERE\r\n upp.person = {$auth->cur_user->id}\r\n {$AND_project1}\r\n AND upp.state = 1\r\n\r\n AND i.type = '" . ITEM_COMMENT . "'\r\n AND i.project = upp.project\r\n {$AND_project2}\r\n {$str_is_alive}\r\n {$AND_person}\r\n {$AND_date_min}\r\n {$AND_date_max}\r\n AND ( i.pub_level >= upp.level_view\r\n OR\r\n i.created_by = {$auth->cur_user->id}\r\n )\r\n\r\n AND c.id = i.id\r\n {$AND_task}\r\n {$AND_match}\r\n {$AND_comment}\r\n\r\n " . getOrderByString($order_by);
} else {
$str_query = "SELECT i.*, c.* from {$prefix}item i, {$prefix}comment c\r\n WHERE\r\n i.type = '" . ITEM_COMMENT . "'\r\n {$AND_project2}\r\n {$str_is_alive}\r\n {$AND_person}\r\n {$AND_date_min}\r\n {$AND_date_max}\r\n\r\n AND c.id = i.id\r\n {$AND_task}\r\n {$AND_comment}\r\n {$AND_match}\r\n\r\n " . getOrderByString($order_by);
}
$sth = $dbh->prepare($str_query);
$sth->execute("", 1);
$tmp = $sth->fetchall_assoc();
$comments = array();
foreach ($tmp as $n) {
$comment = new Comment($n);
$comments[] = $comment;
}
return $comments;
}
static function getAll($args = NULL)
{
global $auth;
$prefix = confGet('DB_TABLE_PREFIX');
$dbh = new DB_Mysql();
## default parameter ##
$id = NULL;
$person = $auth->cur_user->id;
$item = NULL;
$viewed = NULL;
$is_bookmark = NULL;
$feedback_requested_by = NULL;
$notify_on_change = NULL;
$notify_if_unchanged_min = NULL;
$notify_if_unchanged_max = NULL;
$order_by = "ip.created DESC";
### filter params ###
if ($args) {
foreach ($args as $key => $value) {
if (!isset(${$key}) && !is_null(${$key}) && !${$key} === "") {
trigger_error("unknown parameter", E_USER_NOTICE);
} else {
${$key} = $value;
}
}
}
$str_id = $id ? "AND ip.id = " . $id . "" : "";
$str_item = $item ? "AND ip.item = " . $item . "" : "";
if (is_null($is_bookmark)) {
$str_bookmark = "";
} else {
if ($is_bookmark) {
$str_bookmark = "AND ip.is_bookmark = 1";
} else {
$str_bookmark = "AND ip.is_bookmark = 0";
}
}
if (is_null($feedback_requested_by)) {
$str_feedback = "";
} else {
if (is_int($feedback_requested_by) || is_string($feedback_requested_by)) {
$str_feedback = "AND ip.feedback_requested_by = int({$feedback_requested_by})";
} else {
$str_feedback = "AND ip.feedback_requested_by != 0";
}
}
if (!is_null($notify_on_change)) {
$str_notify_on_change = 'AND ip.notify_on_change = ' . $notify_on_change;
} else {
$str_notify_on_change = '';
}
if (!is_null($notify_if_unchanged_min)) {
$str_notify_if_unchanged_min = 'AND ip.notify_if_unchanged >= ' . $notify_if_unchanged_min;
} else {
$str_notify_if_unchanged_min = '';
}
if (!is_null($notify_if_unchanged_max)) {
$str_notify_if_unchanged_max = 'AND ip.notify_if_unchanged <= ' . $notify_if_unchanged_max;
} else {
$str_notify_if_unchanged_max = '';
}
if (!is_null($person)) {
$str_query = "SELECT ip.* FROM {$prefix}itemperson ip, {$prefix}item i\r\n WHERE ip.person = " . $person . "\r\n AND i.id = ip.item\r\n {$str_item}\r\n {$str_bookmark}\r\n {$str_id}\r\n {$str_feedback}\r\n {$str_notify_on_change}\r\n {$str_notify_if_unchanged_min}\r\n {$str_notify_if_unchanged_max}" . getOrderByString($order_by);
$sth = $dbh->prepare($str_query);
$sth->execute("", 1);
$tmp = $sth->fetchall_assoc();
$itempeople = array();
foreach ($tmp as $t) {
$itemperson = new ItemPerson($t);
$itempeople[] = $itemperson;
}
return $itempeople;
} else {
return NULL;
}
}
/**
* get Projects
*
* @@@ this should be refactured into Project::getProject()
*/
public function getProjects($f_order_by = NULL, $f_status_min = STATUS_UPCOMING, $f_status_max = STATUS_COMPLETED)
{
global $auth;
$prefix = confGet('DB_TABLE_PREFIX');
$status_min = intval($f_status_min);
$status_max = intval($f_status_max);
### all projects ###
if ($auth->cur_user->user_rights & RIGHT_VIEWALL) {
$str = "SELECT p.* from {$prefix}project p, {$prefix}projectperson pp\r\n WHERE\r\n p.status <= {$status_max}\r\n AND p.status >= {$status_min}\r\n AND p.state = 1\r\n\r\n AND pp.person = {$this->id}\r\n AND pp.project = p.id\r\n AND pp.state=1\r\n " . getOrderByString($f_order_by, 'prio, name');
} else {
$str = "SELECT p.* from {$prefix}project p, {$prefix}projectperson upp , {$prefix}projectperson pp\r\n WHERE\r\n upp.person = {$auth->cur_user->id}\r\n AND upp.state = 1\r\n AND upp.project = pp.project\r\n\r\n AND pp.person = {$this->id}\r\n AND pp.project = p.id\r\n AND pp.state=1\r\n\r\n AND p.id = upp.project\r\n AND p.status <= {$status_max}\r\n AND p.status >= {$status_min}\r\n AND p.state = 1\r\n\r\n " . getOrderByString($f_order_by, 'prio, name');
}
$dbh = new DB_Mysql();
$sth = $dbh->prepare($str);
$sth->execute("", 1);
$tmp = $sth->fetchall_assoc();
$projects = array();
foreach ($tmp as $n_array) {
require_once confGet('DIR_STREBER') . 'db/class_project.inc.php';
if ($n_array['id']) {
if ($proj = Project::getById($n_array['id'])) {
$projects[] = $proj;
}
}
}
return $projects;
}
