/**
* 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
}
