/**
* Paginate comments by object
*
* @param ProjectObject $object
* @param integer $page
* @param integer $per_page
* @param integer $min_state
* @param integer $min_visiblity
* @return array
*/
function paginateByObject($object, $page = 1, $per_page = 30, $min_state = STATE_VISIBLE, $min_visiblity = VISIBILITY_NORMAL)
{
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME);
$query = "select * from " . TABLE_PREFIX . "project_objects where parent_id='" . $object->getId() . "' and parent_type='" . $object->getType() . "' and type='Comment' and (position is null or position<='0')";
$result = mysql_query($query);
if (mysql_num_rows($result)) {
$count = 0;
Comments::modify_comments_sorting($object, $count);
}
mysql_close($link);
return ProjectObjects::paginate(array('conditions' => array("type = 'Comment' AND parent_id = ? AND state >= ? AND visibility >= ?", $object->getId(), $min_state, $min_visiblity), 'order' => 'position desc, created_on desc'), $page, $per_page);
}
/**
* Select priority control
*
* Params:
*
* - Commong SELECT attributes
* - Value - Selected priority
*
* @param array $params
* @return string
*/
function smarty_function_select_priority_images($params, &$smarty)
{
$priorities = array(PRIORITY_URGENT => lang('Urgent'), PRIORITY_HIGHEST => lang('Highest'), PRIORITY_HIGH => lang('High'), PRIORITY_NORMAL => lang('Normal'), PRIORITY_LOW => lang('Low'), PRIORITY_LOWEST => lang('Lowest'), PRIORITY_HOLD => lang('Hold'), '-99' => lang('Unknown'));
$priorities_images = array(PRIORITY_URGENT => 'assets/images/icons/priority/urgent.png', PRIORITY_HIGHEST => 'assets/images/icons/priority/highest.gif', PRIORITY_HIGH => 'assets/images/icons/priority/high.gif', PRIORITY_NORMAL => 'assets/images/icons/priority/normal.gif', PRIORITY_LOW => 'assets/images/icons/priority/low.gif', PRIORITY_LOWEST => 'assets/images/icons/priority/lowest.gif', PRIORITY_HOLD => 'assets/images/icons/priority/hold.png', '-99' => 'assets/images/icons/priority/unknown.png');
//BOF:mod
$priority_not_set = false;
if ($params['value'] == '0') {
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME, $link);
$query = "select isnull(priority) as priority_not_set from healingcrystals_project_objects where id='" . (int) $params['name'] . "'";
$result = mysql_query($query, $link);
$info = mysql_fetch_Assoc($result);
if ($info['priority_not_set'] == '1') {
$priority_not_set = true;
}
mysql_close($link);
}
if (!$priority_not_set) {
//EOF:mod
$params['sel-image'] = $priorities_images[$params['value']];
$value = 0;
if (isset($params['value'])) {
$value = (int) $params['value'];
//if($value > PRIORITY_HIGHEST || $value < PRIORITY_HOLD) {
if ($value > PRIORITY_URGENT || $value < PRIORITY_HOLD) {
$value = 0;
}
// if
}
// if
//BOF:mod
} else {
$value = '-99';
$params['sel-image'] = $priorities_images[$value];
}
unset($params['value']);
//EOF:mod
$params['id'] = $params['name'];
$obj = new ProjectObject($params['id']);
$params['name'] = strtolower($obj->getType()) . 'priorityimages_' . $params['name'];
$params['class'] = 'imageList';
$options = array();
$options[] = option_tag('Priority', '-', array('data-skip' => 'true'));
foreach ($priorities as $priority => $priority_text) {
$option_attribites = $priority == $value ? array('selected' => true) : array();
$option_attribites['data-icon'] = $priorities_images[$priority];
$options[] = option_tag($priority_text, $priority, $option_attribites);
}
// if
return select_box($options, $params);
}
function convert_object_to_page()
{
$process_mode = $_GET['process'];
$message = '';
if (empty($process_mode)) {
switch ($this->active_object->getType()) {
case 'Milestone':
$message = 'This action will convert the Project: "' . $this->active_object->getName() . '" to Page.';
break;
case 'Ticket':
$message = 'This action will convert the Ticket: "' . $this->active_object->getName() . '" to Page.';
break;
}
} else {
$error = '';
$page_id = $this->active_object->convertToPage($this->logged_user, $error);
if (!empty($page_id)) {
$this->redirectToUrl(assemble_url('project_page', array('project_id' => $this->active_project->getId(), 'page_id' => $page_id)));
}
}
$this->smarty->assign(array('message' => $message, 'redirect_url' => assemble_url('project_object_convert_to_page', array('project_id' => $this->active_object->getProjectId(), 'object_id' => $this->active_object->getId(), 'process' => '1'))));
}
function move_to()
{
$gettype = $_GET['gettype'];
$team_id = $_GET['team_id'];
$project_id = $_GET['project_id'];
$ticket_id = $_GET['ticket_id'];
$move_to_object_id = $_GET['move_to_object_id'];
if (!empty($gettype)) {
$this->smarty->assign('gettype', $gettype);
switch ($gettype) {
case 'projects':
$projects = array();
if (!empty($team_id)) {
$project_obj = new Project($team_id);
//$projects = Milestones::findByProject($project_obj, $this->logged_user);
$projects = Milestones::findActiveByProject($project_obj, STATE_VISIBLE, VISIBILITY_NORMAL, 'name', '', '-1');
}
$this->smarty->assign('projects', $projects);
break;
case 'tickets':
$tickets = array();
if (!empty($project_id)) {
$milestone_obj = new Milestone($project_id);
//$tickets = Tickets::findByMilestone($milestone_obj, STATE_VISIBLE, $this->logged_user->getVisibility());
$tickets = Tickets::findOpenByMilestone($milestone_obj, STATE_VISIBLE, $this->logged_user->getVisibility());
}
$this->smarty->assign('tickets', $tickets);
break;
case 'pages':
$pages_with_milestone = array();
$pages_with_project = array();
if (!empty($team_id)) {
$project_obj = new Project($team_id);
//$pages_with_project = Pages::findByProjectWithoutMilestoneAssociation($project_obj, STATE_VISIBLE, $this->logged_user->getVisibility());
$pages_with_project = Pages::findUnarchivedByProjectWithoutMilestoneAssociation($project_obj, STATE_VISIBLE, $this->logged_user->getVisibility());
} elseif (!empty($project_id)) {
if (!empty($project_id)) {
$milestone_obj = new Milestone($project_id);
$team_id = $milestone_obj->getProjectId();
//$pages_with_milestone = Pages::findByMilestone($milestone_obj, STATE_VISIBLE, $this->logged_user->getVisibility());
$pages_with_milestone = Pages::findUnarchivedByMilestone($milestone_obj, STATE_VISIBLE, $this->logged_user->getVisibility());
$project_obj = new Project($team_id);
//$pages_with_project = Pages::findByProjectWithoutMilestoneAssociation($project_obj, STATE_VISIBLE, $this->logged_user->getVisibility());
$pages_with_project = Pages::findUnarchivedByProjectWithoutMilestoneAssociation($project_obj, STATE_VISIBLE, $this->logged_user->getVisibility());
}
}
$this->smarty->assign('pages_with_milestone', $pages_with_milestone);
$this->smarty->assign('pages_with_project', $pages_with_project);
break;
case 'action':
$move_to_object = new ProjectObject($move_to_object_id);
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME);
mysql_query("update healingcrystals_attachments set parent_id='" . $move_to_object->getId() . "', parent_type='" . $move_to_object->getType() . "' where id='" . $this->active_attachment->getId() . "'");
mysql_close($link);
$type = $move_to_object->getType();
$obj = new $type($move_to_object_id);
$link = 'Attachment Moved: <a href="' . $obj->getViewUrl() . '">Click to View</a>';
$this->smarty->assign('link', $link);
break;
}
} else {
$teams = Projects::findNamesByUser($this->logged_user);
$this->smarty->assign('teams', $teams);
}
$parent = $this->active_attachment->getParent();
$this->smarty->assign('cur_project_id', $parent->getProjectId());
$this->smarty->assign('cur_attachment_id', $this->active_attachment->getId());
}
function link_unvisited($comment_id)
{
//BOF:mod 20111019 #448
$temp_obj = new ProjectObject($comment_id);
$object_type = $temp_obj->getType();
//EOF:mod 20111019
$is_unvisited = true;
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME, $link);
//BOF:mod 20111019 #448
if ($object_type == 'Comment') {
//EOF:mod 20111019 #448
$query = "select link_is_visited from healingcrystals_assignments_action_request where user_id='" . $this->getId() . "' and comment_id='" . (int) $comment_id . "'";
//BOF:mod 20111019 #448
} else {
$query = "select link_is_visited from healingcrystals_assignments_flag_fyi_actionrequest where user_id='" . $this->getId() . "' and object_id='" . (int) $comment_id . "'";
}
//EOF:mod 20111019 #448
$result = mysql_query($query);
if (mysql_num_rows($result)) {
$info = mysql_fetch_assoc($result);
if ($info['link_is_visited']) {
$is_unvisited = false;
}
}
mysql_close($link);
return $is_unvisited;
}
function move()
{
$new_parent_id = $this->request->post('new_parent_id');
$new_parent_type = $this->request->post('new_parent_type');
$new_parent_url = '';
$move_mode = false;
if (!empty($new_parent_id) && !empty($new_parent_type)) {
$move_mode = true;
$parent_obj = $sql_part = null;
switch ($new_parent_type) {
case 'milestone':
$parent_obj = new MileStone($new_parent_id);
break;
case 'ticket':
$parent_obj = new Ticket($new_parent_id);
break;
case 'page':
$parent_obj = new Page($new_parent_id);
break;
}
if ($parent_obj) {
$body = $this->active_task->getBody();
$doc = new DOMDocument();
if ($doc->loadHTML($body)) {
$anc_tags = $doc->getElementsByTagName('a');
$new_parent_url = '';
foreach ($anc_tags as $anc) {
if ($anc->nodeValue == 'View Task in Full') {
$href = $anc->getAttribute('href');
$fragment = substr($href, strpos($href, '#'));
$anc->setAttribute('href', $parent_obj->getViewUrl() . $fragment);
break;
}
}
if (!empty($fragment)) {
$body_tag = $doc->getElementsByTagName('body');
$body = $doc->saveXML($body_tag->item(0)->firstChild);
$comment_id = str_replace('#comment', '', $fragment);
}
}
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME);
$query = "update \n\t\t\t\t\t\t\thealingcrystals_project_objects \n\t\t\t\t\t\t set \n\t\t\t\t\t\t\tproject_id='" . $parent_obj->getProjectId() . "', \n\t\t\t\t\t\t\tmilestone_id='" . $parent_obj->getMilestoneId() . "', \n\t\t\t\t\t\t\tparent_id='" . $parent_obj->getId() . "', \n\t\t\t\t\t\t\tparent_type='" . $parent_obj->getType() . "', \n\t\t\t\t\t\t\tbody = '" . mysql_real_escape_string($body) . "' \n\t\t\t\t\t\t where\tid='" . $this->active_task->getId() . "'";
mysql_query($query, $link);
if (!empty($comment_id)) {
$comment_query = "update \n\t\t\t\t\t\t\thealingcrystals_project_objects \n\t\t\t\t\t\t set \n\t\t\t\t\t\t\tproject_id='" . $parent_obj->getProjectId() . "', \n\t\t\t\t\t\t\tmilestone_id='" . $parent_obj->getMilestoneId() . "', \n\t\t\t\t\t\t\tparent_id='" . $parent_obj->getId() . "', \n\t\t\t\t\t\t\tparent_type='" . $parent_obj->getType() . "', \n\t\t\t\t\t\t\tposition=null\n\t\t\t\t\t\t where\tid='" . $comment_id . "'";
mysql_query($comment_query, $link);
}
mysql_close($link);
$new_parent_url = $parent_obj->getViewUrl() . '#task' . $this->active_task->getId();
$cache_id = TABLE_PREFIX . 'project_objects_id_' . $this->active_task->getId();
$cache_obj = cache_get($cache_id);
if ($cache_obj) {
cache_remove($cache_id);
}
}
} else {
$listing = array();
switch ($this->active_task_parent->getType()) {
case 'Milestone':
//$listing = Milestones::findByProject($this->active_project, $this->logged_user);
$listing = Milestones::findActiveByProject_custom($this->active_project);
break;
case 'Ticket':
$listing = Tickets::findOpenByProjectByNameSort($this->active_project, STATE_VISIBLE, $this->logged_user->getVisibility());
break;
case 'Page':
$categories = Categories::findByModuleSection($this->active_project, 'pages', 'pages');
$listing = Pages::findByCategories($categories, STATE_VISIBLE, $this->logged_user->getVisibility());
/*foreach($categories as $category){
$listing = array_merge($listing, Pages::findByCategory($category, STATE_VISIBLE, $this->logged_user->getVisibility()));
}*/
break;
}
$this->smarty->assign(array('teams' => Projects::findNamesByUser($this->logged_user), 'listing' => $listing, 'task_parent_id' => $this->active_task_parent->getId()));
}
$this->smarty->assign('new_parent_url', $new_parent_url);
$this->smarty->assign('move_mode', $move_mode);
}
function render_comments()
{
$response = '<table width="100%" style="border:5px solid #dddddd;">';
$user_id = $this->request->get('user_id');
if (empty($user_id)) {
$user_id = $this->request->post('user_id');
if (empty($user_id)) {
$user_id = $this->logged_user->getId();
}
}
$action_type = $_GET['action_type'];
$parent_id = $_GET['parent_id'];
$temp_obj = new ProjectObject($parent_id);
$parenttype = $temp_obj->getType();
$parentobj = new $parenttype($parent_id);
$projectobj = new Project($parentobj->getProjectId());
$milestone_id = $parentobj->getMilestoneId();
if (!empty($milestone_id)) {
$milestoneobj = new Milestone($milestone_id);
}
$assigneesstring = '';
list($assignees, $owner_id) = $parentobj->getAssignmentData();
foreach ($assignees as $assignee) {
$assigneeobj = new User($assignee);
$assigneesstring .= '<a target="_blank" href="' . $assigneeobj->getViewUrl() . '">' . $assigneeobj->getName() . '</a>, ';
unset($assigneeobj);
}
if (!empty($assigneesstring)) {
$assigneesstring = substr($assigneesstring, 0, -2);
}
$dueon = date('F d, Y', strtotime($parentobj->getDueOn()));
if ($dueon == 'January 01, 1970') {
$dueon = '--';
}
if ($milestoneobj) {
$priority = $milestoneobj->getPriority();
if (!empty($priority) || $priority == '0') {
$priority = $milestoneobj->getFormattedPriority();
} else {
$priority = '--';
}
} else {
$priority = '--';
}
$response .= '
<tr><td colspan="2" style="height:20px;"> </td></tr>
<tr>
<td style="width:25%;" valign="top">' . $parenttype . '</td>
<td valign="top">
<a target="_blank" href="' . $parentobj->getViewUrl() . '"><span class="homepageobject">' . $parentobj->getName() . '</span></a>
</td>
</tr>
<tr>
<td valign="top">Team » Project</td>
<td valign="top">
<a target="_blank" href="' . $projectobj->getOverviewUrl() . '"><span class="homepageobject">' . $projectobj->getName() . '</span></a> » ' . ($milestoneobj ? '<a target="_blank" href="' . $milestoneobj->getViewUrl() . '"><span class="homepageobject">' . $milestoneobj->getName() . '</a></span>' : '--') . '</td>
</tr>
<tr>
<td vlaign="top">Project Priority</td>
<td valign="top">' . $priority . '</td>
</tr>
<tr>
<td valign="top">Due on</td>
<td valign="top">' . $dueon . '</td>
</tr>
<tr>
<td valign="top">Assignees</td>
<td valign="top">' . $assigneesstring . '</td>
</tr>
<tr><td colspan="2" style="height:20px;"> </td></tr>';
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME, $link);
if ($action_type == 'actionrequest') {
$query = "(select b.id, d.id as parent_ref , a.date_added as date_value, e.priority as prio, c.name as project_name\n from healingcrystals_assignments_action_request a\n inner join healingcrystals_project_objects b on a.comment_id=b.id\n inner join healingcrystals_project_objects d on b.parent_id=d.id\n left outer join healingcrystals_project_objects e on d.milestone_id=e.id\n inner join healingcrystals_projects c on b.project_id=c.id\n where d.id='" . $parent_id . "' and b.state='" . STATE_VISIBLE . "' and a.user_id='" . $user_id . "' and a.is_action_request='1'\n and d.state='" . STATE_VISIBLE . "' )\n union\n (select '' as id, a.object_id as parent_ref, b.created_on as date_value, e.priority as prio, c.name as project_name\n from healingcrystals_assignments_flag_fyi_actionrequest a\n inner join healingcrystals_project_objects b on a.object_id=b.id\n left outer join healingcrystals_project_objects e on b.milestone_id=e.id\n inner join healingcrystals_projects c on b.project_id=c.id\n where a.user_id='" . $user_id . "' and a.object_id='" . $parent_id . "' and flag_actionrequest='1' and b.state='" . STATE_VISIBLE . "'\n )\n order by prio desc, project_name, date_value desc";
} elseif ($action_type == 'fyi') {
$query = "(select b.id, d.id as parent_ref , a.date_added as date_value, e.priority as prio, c.name as project_name\n from healingcrystals_assignments_action_request a\n inner join healingcrystals_project_objects b on a.comment_id=b.id\n inner join healingcrystals_project_objects d on b.parent_id=d.id\n left outer join healingcrystals_project_objects e on d.milestone_id=e.id\n inner join healingcrystals_projects c on b.project_id=c.id\n where d.id='" . $parent_id . "' and b.state='" . STATE_VISIBLE . "' and a.user_id='" . $user_id . "' and a.is_fyi='1'\n and d.state='" . STATE_VISIBLE . "' )\n union\n (select '' as id, a.object_id as parent_ref, b.created_on as date_value, e.priority as prio, c.name as project_name\n from healingcrystals_assignments_flag_fyi_actionrequest a\n inner join healingcrystals_project_objects b on a.object_id=b.id\n left outer join healingcrystals_project_objects e on b.milestone_id=e.id\n inner join healingcrystals_projects c on b.project_id=c.id\n where a.user_id='" . $user_id . "' and a.object_id='" . $parent_id . "' and flag_fyi='1' and b.state='" . STATE_VISIBLE . "'\n )\n order by prio desc, project_name, date_value desc";
}
$result = mysql_query($query);
$count = 0;
while ($entry = mysql_fetch_assoc($result)) {
$count++;
if (!empty($entry['id'])) {
$temp_obj = new Comment($entry['id']);
$created_by_id = $temp_obj->getCreatedById();
$created_by_user = new User($created_by_id);
$created_on = strtotime($temp_obj->getCreatedOn());
$created_on = date('m-d-y', $created_on);
$temp = $temp_obj->getFormattedBody(true, true);
$comment_body = $temp;
$response .= '
<tr>
<td valign="top" style="vertical-align:top;">
Comment by<br/>' . (!empty($created_by_id) ? '<a target="_blank" href="' . $created_by_user->getViewUrl() . '">' . $created_by_user->getName() . '</a>' : $temp_obj->getCreatedByName()) . '<br/><br/><br/>
<a target="_blank" href="' . $temp_obj->getViewUrl() . '">[view comment]</a><br/> ' . $created_on . '<br/><br/><br/>' . ($action_type == 'actionrequest' ? '<a class="mark_as_complete" count="' . $count . '" href="' . assemble_url('project_comment_action_request_completed', array('project_id' => $temp_obj->getProjectId(), 'comment_id' => $temp_obj->getId())) . '">Mark Action Request Complete</a>' : '') . ($action_type == 'fyi' ? '<a class="mark_as_read" count="' . $count . '" href="' . assemble_url('project_comment_fyi_read', array('project_id' => $temp_obj->getProjectId(), 'comment_id' => $temp_obj->getId())) . '">Mark this Notification<br/>as Read</a>' : '') . '</td>
<td valign="top" style="vertical-align:top;">
<div style="width:600px;overflow:auto;">' . $comment_body . '</div>' . ($show_read_link ? '<a target="_blank" href="' . $temp_obj->getViewUrl() . '">Click here to read the rest of this Comment</a>' : '') . '</td>
</tr>
<tr><td colspan="2" style="height:20px;"> </td></tr>';
} else {
$response .= '
<tr>
<td valign="top" style="vertical-align:top;">
Created by<br/>' . (!empty($created_by_id) ? '<a target="_blank" href="' . $created_by_user->getViewUrl() . '">' . $created_by_user->getName() . '</a>' : $parentobj->getCreatedByName()) . '<br/><br/><br/>
<a target="_blank" href="' . $parentobj->getViewUrl() . '">[view object]</a><br/> ' . $created_on . '<br/><br/><br/>' . ($action_type == 'action_request' ? '<a class="mark_as_complete" count="' . $count . '" href="' . assemble_url('project_object_action_request_completed', array('project_id' => $parentobj->getProjectId())) . '&object_id=' . $parentobj->getId() . '&project_id=' . $parentobj->getProjectId() . '">Mark Action Request Complete</a>' : '') . ($action_type == 'fyi' ? '<a class="mark_as_read" count="' . $count . '" href="' . assemble_url('project_object_fyi_read', array('project_id' => $parentobj->getProjectId())) . '&object_id=' . $parentobj->getId() . '&project_id=' . $parentobj->getProjectId() . '">Mark this Notification<br/>as Read</a>' : '') . '</td>
<td valign="top" style="vertical-align:top;">
<div style="width:600px;overflow:auto;">' . $parentobj->getFormattedBody(true, true) . '</div>
</td>
</tr>
<tr><td colspan="2" style="height:20px;"> </td></tr>';
}
}
mysql_close($link);
$response .= '</table>';
$this->smarty->assign('response', $response);
}
/**
* 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
}
