/** * Return data for a given day * * @param DateValue $day * @param string $additional_conditions * @param boolean $include_assignments_table * @return array */ function getDayData($day, $additional_conditions, $include_assignments_table = false) { $objects_table = TABLE_PREFIX . 'project_objects'; $assignments_table = TABLE_PREFIX . 'assignments'; $conditions = db_prepare_string("{$objects_table}.due_on = ?", array($day)); if ($additional_conditions) { $conditions .= " AND {$additional_conditions}"; } // if // If we don't have user ID-s filter we can exclude assignments table $tables = $include_assignments_table ? "{$objects_table}, {$assignments_table}" : $objects_table; return ProjectObjects::findBySQL("SELECT DISTINCT {$objects_table}.* FROM {$tables} WHERE {$conditions} ORDER BY priority DESC"); }
/** * Return paginated set of project objects * * @param array $arguments * @param itneger $page * @param integer $per_page * @return array */ function paginate($arguments = null, $page = 1, $per_page = 10) { if (!is_array($arguments)) { $arguments = array(); } // if $arguments['limit'] = $per_page; $arguments['offset'] = ($page - 1) * $per_page; $items = ProjectObjects::findBySQL(DataManager::prepareSelectFromArguments($arguments, TABLE_PREFIX . 'project_objects'), null, array_var($arguments, 'one')); $total_items = ProjectObjects::count(array_var($arguments, 'conditions')); return array($items, new Pager($page, $total_items, $per_page)); }
/** * Execute specific filter * * Possible $paginate values: * * - NULL - base pagination on filter settings * - true - force pagination with given params ($page and $per_page) * - false - don't paginate * * @param User $user * @param AssignmentFilter $filter * @param mixed $paginate * @param integer $page * @param integer $per_page * @return array */ function executeFilter($user, $filter, $paginate = null, $page = null, $per_page = null) { $project_objects_table = TABLE_PREFIX . 'project_objects'; $assignments_table = TABLE_PREFIX . 'assignments'; $conditions = $filter->prepareConditions($user); if (empty($conditions)) { if ($paginate === null) { return $filter->getObjectsPerPage() ? array(null, new Pager(1, 0, $filter->getObjectsPerPage())) : null; } elseif ($paginate) { return array(null, new Pager(1, 0, $filter->getObjectsPerPage())); } else { return null; } // if } // if $order_by = ''; if ($filter->orderedByDueDate()) { $order_by = $filter->orderDescending() ? 'ORDER BY ISNULL(due_on), due_on DESC, priority DESC' : 'ORDER BY ISNULL(due_on), due_on, priority DESC'; } elseif ($filter->getOrderBy()) { $order_by = 'ORDER BY ' . $filter->getOrderBy(); } // if $total = 0; $object_ids = array(); // Lets get object ID-s $rows = db_execute_all("SELECT DISTINCT {$project_objects_table}.id FROM {$project_objects_table} LEFT JOIN {$assignments_table} ON {$project_objects_table}.id = {$assignments_table}.object_id WHERE {$conditions}"); if (is_foreachable($rows)) { $total = count($rows); foreach ($rows as $row) { $object_ids[] = (int) $row['id']; } // foreach } // if // Use filter pagination settings if ($paginate === null || $paginate === true) { if ($total) { $per_page = $paginate === null ? (int) $filter->getObjectsPerPage() : (int) $per_page; if ($per_page < 1) { $per_page = 30; } // if $page = (int) $page; if ($page < 1) { $page = 1; } // if $offset = ($page - 1) * $per_page; $limit = "LIMIT {$offset}, {$per_page}"; return array(ProjectObjects::findBySQL("SELECT * FROM {$project_objects_table} WHERE id IN (?) {$order_by} {$limit}", array($object_ids)), new Pager($page, $total, $per_page)); } else { return array(null, new Pager(1, 0, $filter->getObjectsPerPage())); } // if // Don't paginate } elseif ($paginate === false) { if ($total) { return ProjectObjects::findBySQL("SELECT {$project_objects_table}.* FROM {$project_objects_table} WHERE id IN (?) {$order_by}", array($object_ids)); } else { return null; } // if } // if }
/** * Return starred project objects by $user * * @param User $user * @return array */ function findByUser($user) { $project_objects_table = TABLE_PREFIX . 'project_objects'; $starred_objects_table = TABLE_PREFIX . 'starred_objects'; if ($user->isProjectManager()) { return ProjectObjects::findBySQL("SELECT {$project_objects_table}.* FROM {$project_objects_table}, {$starred_objects_table} WHERE {$starred_objects_table}.object_id = {$project_objects_table}.id AND {$starred_objects_table}.user_id = ? AND {$project_objects_table}.state >= ? ORDER BY {$project_objects_table}.priority DESC", array($user->getId(), STATE_VISIBLE)); } else { $type_filter = ProjectUsers::getVisibleTypesFilter($user, array(PROJECT_STATUS_ACTIVE, PROJECT_STATUS_PAUSED, PROJECT_STATUS_CANCELED, PROJECT_STATUS_COMPLETED)); if ($type_filter) { return ProjectObjects::findBySQL("SELECT {$project_objects_table}.* FROM {$project_objects_table}, {$starred_objects_table} WHERE {$type_filter} AND {$starred_objects_table}.object_id = {$project_objects_table}.id AND {$starred_objects_table}.user_id = ? AND {$project_objects_table}.state >= ? AND {$project_objects_table}.visibility >= ? ORDER BY {$project_objects_table}.priority DESC", array($user->getId(), STATE_VISIBLE, $user->getVisibility())); } // if } // if return null; }
/** * Return open tasks that belong to a given object * * @param ProjectObject $object * @param integer $min_state * @return array */ function findOpenByObject($object, $min_state = STATE_VISIBLE) { //BOF:mod 20120820 /* //EOF:mod 20120820 return ProjectObjects::find(array( 'conditions' => array('parent_id = ? AND type = ? AND state >= ? AND completed_on IS NULL', $object->getId(), 'Task', $min_state), 'order' => 'ISNULL(position) ASC, position, priority DESC, created_on' )); //BOF:mod 20120820 */ //$sql= "select a.*, if(a.due_on is null, b.reminder_date, a.due_on) as final_date from healingcrystals_project_objects a left join healingcrystals_project_object_misc b on(a.id=b.object_id) where a.parent_id = ? AND a.type = ? AND a.state >= ? AND a.completed_on IS NULL order by // isnull(priority) desc, if // ( // priority='" . PRIORITY_ONGOING . "' and final_date is not null and final_date<=DATE_ADD(NOW(), INTERVAL 1 DAY), 2.5, if // ( // priority='" . PRIORITY_ONGOING . "' and final_date is not null and YEAR(final_date)=YEAR(NOW()) and WEEK(final_date, 1)=WEEK(NOW(), 1), 1.5, if // ( // priority='" . PRIORITY_ONGOING . "' and final_date is not null and YEAR(final_date)=YEAR(NOW()) and MONTH(final_date)=MONTH(NOW()), 0.5, if // ( // priority='" . PRIORITY_ONGOING . "' and final_date is not null and YEAR(final_date)=YEAR(NOW()) and QUARTER(final_date)=QUARTER(NOW()), -0.5, if // ( // priority='" . PRIORITY_ONGOING . "' and final_date is not null and YEAR(final_date)=YEAR(NOW()), -1.5, priority // ) // ) // ) // ) // ) DESC, name, ISNULL(position) ASC, position, created_on"; //$sql= "select a.*, if(a.due_on is null, b.reminder_date, a.due_on) as final_date from healingcrystals_project_objects a left join healingcrystals_project_object_misc b on(a.id=b.object_id) where a.parent_id = ? AND a.type = ? AND a.state >= ? AND a.completed_on IS NULL order by // isnull(a.priority) desc, cast(if // ( // a.priority='" . PRIORITY_ONGOING . "' and final_date is not null and final_date<=DATE_ADD(NOW(), INTERVAL 1 DAY), 1.5, if // ( // a.priority='" . PRIORITY_ONGOING . "' and final_date is not null and YEAR(final_date)=YEAR(NOW()) and WEEK(final_date, 1)=WEEK(NOW(), 1), 0.5, if // ( // a.priority='" . PRIORITY_ONGOING . "' and final_date is not null and YEAR(final_date)=YEAR(NOW()) and MONTH(final_date)=MONTH(NOW()), -0.5, if // ( // a.priority='" . PRIORITY_ONGOING . "' and final_date is not null and YEAR(final_date)=YEAR(NOW()) and QUARTER(final_date)=QUARTER(NOW()), -1.5, if // ( // a.priority='" . PRIORITY_ONGOING . "' and final_date is not null and YEAR(final_date)=YEAR(NOW()), -2.5, a.priority // ) // ) // ) // ) // ) as decimal(5,2)) DESC, a.name, ISNULL(a.position) ASC, a.position, a.created_on"; //BOF:mod 20120905 /* //EOF:mod 20120905 $sql= "select a.*, if(a.due_on is null, b.reminder_date, a.due_on) as final_date from healingcrystals_project_objects a left join healingcrystals_project_object_misc b on(a.id=b.object_id) where a.parent_id = ? AND a.type = ? AND a.state >= ? AND a.completed_on IS NULL order by isnull(a.priority) desc, cast(if ( a.priority='-3' and final_date is not null and YEAR(final_date)=YEAR(NOW()) and MONTH(final_date)=MONTH(NOW()) and DAYOFMONTH(final_date)=DAYOFMONTH(NOW()), 1.5, if ( a.priority='-3' and final_date is not null and final_date between DATE_ADD(NOW(), INTERVAL 2 DAY) and DATE_ADD(NOW(), INTERVAL 7 DAY), 0.5, if ( a.priority='-3' and final_date is not null and final_date between DATE_ADD(NOW(), INTERVAL 8 DAY) and DATE_ADD(NOW(), INTERVAL 30 DAY), -0.5, if ( a.priority='-3' and final_date is not null and final_date between DATE_ADD(NOW(), INTERVAL 31 DAY) and DATE_ADD(NOW(), INTERVAL 90 DAY), -1.5, if ( a.priority='-3' and final_date is not null and final_date>=DATE_ADD(NOW(), INTERVAL 91 DAY), -2.5, a.priority ) ) ) ) ) as decimal(5,2)) DESC, a.name, ISNULL(a.position) ASC, a.position, a.created_on"; //BOF:mod 20120905 */ //BOF:mod 20121127 $type = $object->getType(); if ($type == 'Checklist') { return ProjectObjects::find(array('conditions' => array('parent_id = ? AND type = ? AND state >= ? AND completed_on IS NULL', $object->getId(), 'Task', $min_state), 'order' => 'ISNULL(position) ASC, position, priority DESC, created_on')); } else { //EOF:mod 20121127 //BOF:mod 20130410 /* //EOF:mod 20130410 $sql= "select a.*, if ( a.due_on is not null and a.due_on<>'0000-00-00' and b.reminder_date is not null and b.reminder_date<>'0000-00-00 00:00:00' and a.completed_on is null, if ( a.due_on<=b.reminder_date, a.due_on, b.reminder_date ), if ( b.reminder_date is not null and b.reminder_date<>'0000-00-00 00:00:00' and a.completed_on is null, b.reminder_date, a.due_on ) ) as final_date from healingcrystals_project_objects a left join healingcrystals_project_object_misc b on(a.id=b.object_id) where a.parent_id = ? AND a.type = ? AND a.state >= ? AND a.completed_on IS NULL order by isnull(a.priority) desc, cast( if ( final_date is not null and final_date < NOW(), 2.5, if ( final_date is not null and YEAR(final_date)=YEAR(NOW()) and MONTH(final_date)=MONTH(NOW()) and DAYOFMONTH(final_date)=DAYOFMONTH(NOW()) and (a.priority='" .PRIORITY_HIGH . "' or a.priority='" .PRIORITY_NORMAL . "' or a.priority='" .PRIORITY_LOW . "' or a.priority='" .PRIORITY_LOWEST . "' or a.priority='" .PRIORITY_HOLD . "'), 1.5, if ( final_date is not null and final_date<=DATE_ADD(NOW(), INTERVAL 7 DAY) and (a.priority='" .PRIORITY_NORMAL . "' or a.priority='" .PRIORITY_LOW . "' or a.priority='" .PRIORITY_LOWEST . "' or a.priority='" .PRIORITY_HOLD . "') , 0.5, if ( final_date is not null and final_date<=DATE_ADD(NOW(), INTERVAL 30 DAY) and (a.priority='" .PRIORITY_LOW . "' or a.priority='" .PRIORITY_LOWEST . "' or a.priority='" .PRIORITY_HOLD . "'), -0.5, if ( final_date is not null and final_date<=DATE_ADD(NOW(), INTERVAL 90 DAY) and (a.priority='" .PRIORITY_LOWEST . "' or a.priority='" .PRIORITY_HOLD . "'), -1.5, a.priority ) ) ) ) ) as decimal(5,2)) DESC, final_date asc, ISNULL(a.position) ASC, a.position, a.created_on, a.name"; //EOF:mod 20120905 //BOF:mod 20130410 */ $sql = "select \n\t\t\t\t\ta.*, \n\t\t\t\t\tif (\n\t\t\t\t\ta.due_on is not null and \n\t\t\t\t\ta.due_on<>'0000-00-00' and \n\t\t\t\t\tb.email_reminder_unit is not null and \n\t\t\t\t\tb.email_reminder_unit<>'' and \n\t\t\t\t\ta.completed_on is null, \n\t\t\t\t\t\tif (\n\t\t\t\t\t\ta.due_on<=cast(if(b.email_reminder_unit='D', \n\t\t\t\t\t\t\t\t\tconcat(DATE_SUB(a.due_on, interval b.email_reminder_period day), ' ', b.email_reminder_time), \n\t\t\t\t\t\t\t\t\tif(b.email_reminder_unit='W', \n\t\t\t\t\t\t\t\t\t\tconcat(DATE_SUB(a.due_on, interval b.email_reminder_period week), ' ', b.email_reminder_time), \n\t\t\t\t\t\t\t\t\t\tif(b.email_reminder_unit='M', \n\t\t\t\t\t\t\t\t\t\t\tconcat(DATE_SUB(a.due_on, interval b.email_reminder_period month), ' ', b.email_reminder_time), \n\t\t\t\t\t\t\t\t\t\t\tnull\n\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t) as datetime), \n\t\t\t\t\t\t\ta.due_on, \n\t\t\t\t\t\t\tcast(if(b.email_reminder_unit='D', \n\t\t\t\t\t\t\t\tconcat(DATE_SUB(a.due_on, interval b.email_reminder_period day), ' ', b.email_reminder_time), \n\t\t\t\t\t\t\t\tif(b.email_reminder_unit='W', \n\t\t\t\t\t\t\t\t\tconcat(DATE_SUB(a.due_on, interval b.email_reminder_period week), ' ', b.email_reminder_time), \n\t\t\t\t\t\t\t\t\tif(b.email_reminder_unit='M', \n\t\t\t\t\t\t\t\t\t\tconcat(DATE_SUB(a.due_on, interval b.email_reminder_period month), ' ', b.email_reminder_time), \n\t\t\t\t\t\t\t\t\t\tnull\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t) as datetime)\n\t\t\t\t\t\t), \n\t\t\t\t\t\ta.due_on\n\t\t\t\t\t) as final_date\n\t\t\t\tfrom \n\t\t\t\t\thealingcrystals_project_objects a \n\t\t\t\t\tleft join healingcrystals_project_object_misc b on(a.id=b.object_id) \n\t\t\t\twhere \n\t\t\t\t\ta.parent_id = ? AND \n\t\t\t\t\ta.type = ? AND \n\t\t\t\t\ta.state >= ? AND \n\t\t\t\t\ta.completed_on IS NULL \n\t\t\t\torder by \n\t\t\t\t\tisnull(a.priority) desc, \n\t\t\t\t\tcast( if (\n\t\t\t\t\t\tfinal_date is not null and final_date < NOW(), 2.5, if (\n\t\t\t\t\t\t\tfinal_date is not null and YEAR(final_date)=YEAR(NOW()) and MONTH(final_date)=MONTH(NOW()) and DAYOFMONTH(final_date)=DAYOFMONTH(NOW()) and (a.priority='" . PRIORITY_HIGH . "' or a.priority='" . PRIORITY_NORMAL . "' or a.priority='" . PRIORITY_LOW . "' or a.priority='" . PRIORITY_LOWEST . "' or a.priority='" . PRIORITY_HOLD . "'), 1.5, if (\n\t\t\t\t\t\t\t\tfinal_date is not null and final_date<=DATE_ADD(NOW(), INTERVAL 7 DAY) and (a.priority='" . PRIORITY_NORMAL . "' or a.priority='" . PRIORITY_LOW . "' or a.priority='" . PRIORITY_LOWEST . "' or a.priority='" . PRIORITY_HOLD . "') , 0.5, if (\n\t\t\t\t\t\t\t\t\tfinal_date is not null and final_date<=DATE_ADD(NOW(), INTERVAL 30 DAY) and (a.priority='" . PRIORITY_LOW . "' or a.priority='" . PRIORITY_LOWEST . "' or a.priority='" . PRIORITY_HOLD . "'), -0.5, if (\n\t\t\t\t\t\t\t\t\t\tfinal_date is not null and final_date<=DATE_ADD(NOW(), INTERVAL 90 DAY) and (a.priority='" . PRIORITY_LOWEST . "' or a.priority='" . PRIORITY_HOLD . "'), -1.5, a.priority\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t)\n\t\t\t\t\t\t)\n\t\t\t\t\t) as decimal(5,2)) DESC, final_date asc, \n\t\t\t\tISNULL(a.position) ASC, \n\t\t\t\ta.position, \n\t\t\t\ta.created_on, \n\t\t\t\ta.name"; //EOF:mod 20130410 return ProjectObjects::findBySQL($sql, array($object->getId(), 'Task', $min_state)); //BOF:mod 20121127 } //EOF:mod 20121127 //EOF:mod 20120820 }