PHP w2p_Database_Query::leftJoin方法代码示例

 public function getProjectTaskLinksByCategory(CAppUI $AppUI, $project_id = 0, $task_id = 0, $category_id = 0, $search = '')
 {
     // load the following classes to retrieved denied records
     $project = new CProject();
     $task = new CTask();
     // SETUP FOR LINK LIST
     $q = new w2p_Database_Query();
     $q->addQuery('DISTINCT links.*');
     $q->addQuery('contact_first_name, contact_last_name');
     $q->addQuery('project_name, project_color_identifier, project_status');
     $q->addQuery('task_name, task_id');
     $q->addTable('links');
     $q->leftJoin('users', 'u', 'user_id = link_owner');
     $q->leftJoin('contacts', 'c', 'user_contact = contact_id');
     if ($search != '') {
         $q->addWhere('(link_name LIKE \'%' . $search . '%\' OR link_description LIKE \'%' . $search . '%\')');
     }
     if ($project_id > 0) {
         // Project
         $q->addWhere('link_project = ' . (int) $project_id);
     }
     if ($task_id > 0) {
         // Task
         $q->addWhere('link_task = ' . (int) $task_id);
     }
     if ($category_id >= 0) {
         // Category
         $q->addWhere('link_category = ' . $category_id);
     }
     // Permissions
     $project->setAllowedSQL($AppUI->user_id, $q, 'link_project');
     $task->setAllowedSQL($AppUI->user_id, $q, 'link_task and task_project = link_project');
     $q->addOrder('project_name, link_name');
     return $q->loadList();
 }

$proj = new CProject();
$tobj = new CTask();
$allowedProjects = $proj->getAllowedSQL($AppUI->user_id, 'pr.project_id');
$allowedTasks = $tobj->getAllowedSQL($AppUI->user_id, 'ta.task_id');
// query my sub-tasks (ignoring task parents)
$q = new w2p_Database_Query();
$q->addQuery('ta.*');
$q->addQuery('project_name, pr.project_id, project_color_identifier');
$q->addQuery('tp.task_pinned');
$q->addQuery('ut.user_task_priority');
$dateDiffString = $q->dbfnDateDiff('ta.task_end_date', $q->dbfnNow()) . ' AS task_due_in';
$q->addQuery($dateDiffString);
$q->addTable('projects', 'pr');
$q->addTable('tasks', 'ta');
$q->addTable('user_tasks', 'ut');
$q->leftJoin('user_task_pin', 'tp', 'tp.task_id = ta.task_id and tp.user_id = ' . (int) $user_id);
$q->leftJoin('project_departments', 'project_departments', 'pr.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
$q->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL');
$q->addWhere('ut.task_id = ta.task_id');
$q->addWhere('ut.user_id = ' . (int) $user_id);
$q->addWhere('( ta.task_percent_complete < 100 or ta.task_percent_complete is null)');
$q->addWhere('ta.task_status = 0');
$q->addWhere('pr.project_id = ta.task_project');
if (!$showArcProjs) {
    $q->addWhere('project_active = 1');
    if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
        $q->addWhere('project_status <> ' . (int) $template_status);
    }
}
if (!$showLowTasks) {
    $q->addWhere('task_priority >= 0');

 /**
  * Determines whether the currently logged in user can delete this task log.
  *
  * @global AppUI $AppUI global user permissions
  *
  * @param string by ref $msg error msg to be populated on failure
  * @param int optional $oid key to check
  * @param array $joins optional list of tables to join on
  *
  * @return bool
  */
 public function canDelete(&$msg, $oid = null, $joins = null)
 {
     global $AppUI;
     $q = new w2p_Database_Query();
     // First things first.	Are we allowed to delete?
     $acl =& $AppUI->acl();
     if (!canDelete('task_log')) {
         $msg = $AppUI->_('noDeletePermission');
         return false;
     }
     $k = $this->_tbl_key;
     if ($oid) {
         $this->{$k} = (int) $oid;
     }
     if (is_array($joins)) {
         $q->addTable($this->_tbl, 'k');
         $q->addQuery($k);
         $i = 0;
         foreach ($joins as $table) {
             $table_alias = 't' . $i++;
             $q->leftJoin($table['name'], $table_alias, $table_alias . '.' . $table['joinfield'] . ' = ' . 'k' . '.' . $k);
             $q->addQuery('COUNT(DISTINCT ' . $table_alias . '.' . $table['idfield'] . ') AS ' . $table['idfield']);
         }
         $q->addWhere($k . ' = ' . $this->{$k});
         $q->addGroup($k);
         $obj = null;
         $q->loadObject($obj);
         $q->clear();
         if (!$obj) {
             $msg = db_error();
             return false;
         }
         $msg = array();
         foreach ($joins as $table) {
             $k = $table['idfield'];
             if ($obj->{$k}) {
                 $msg[] = $AppUI->_($table['label']);
             }
         }
         if (count($msg)) {
             $msg = $AppUI->_('noDeleteRecord') . ': ' . implode(', ', $msg);
             return false;
         }
     }
     return true;
 }

    if ($tab < 0) {
        $catsql = false;
    } else {
        $catsql = 'file_category = ' . $tab;
    }
}
// Fetch permissions once for all queries
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'file_project');
$allowedTasks = $task->getAllowedSQL($AppUI->user_id, 'file_task');
// SQL text for count the total recs from the selected option
$q = new w2p_Database_Query();
$q->addQuery('count(file_id)');
$q->addTable('files', 'f');
$q->addJoin('projects', 'p', 'p.project_id = file_project');
$q->addJoin('tasks', 't', 't.task_id = file_task');
$q->leftJoin('project_departments', 'project_departments', 'p.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
$q->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL');
if (count($allowedProjects)) {
    $q->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 )');
}
if (count($allowedTasks)) {
    $q->addWhere('( ( ' . implode(' AND ', $allowedTasks) . ') OR file_task = 0 )');
}
if ($catsql) {
    $q->addWhere($catsql);
}
if ($company_id) {
    $q->addWhere('project_company = ' . (int) $company_id);
}
if ($project_id) {
    $q->addWhere('file_project = ' . (int) $project_id);

$task_sort_order1 = (int) w2PgetParam($_GET, 'task_sort_order1', 0);
$task_sort_order2 = (int) w2PgetParam($_GET, 'task_sort_order2', 0);
if (isset($_POST['show_task_options'])) {
    $AppUI->setState('TaskListShowIncomplete', w2PgetParam($_POST, 'show_incomplete', 0));
}
$showIncomplete = $AppUI->getState('TaskListShowIncomplete', 0);
$project = new CProject();
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'p.project_id');
$where_list = count($allowedProjects) ? implode(' AND ', $allowedProjects) : '';
$working_hours = $w2Pconfig['daily_working_hours'] ? $w2Pconfig['daily_working_hours'] : 8;
$q = new w2p_Database_Query();
$q->addTable('projects', 'p');
$q->addQuery('company_name, p.project_id, project_color_identifier, project_name, project_percent_complete');
$q->addJoin('companies', 'com', 'company_id = project_company', 'inner');
$q->addJoin('tasks', 't1', 'p.project_id = t1.task_project', 'inner');
$q->leftJoin('project_departments', 'project_departments', 'p.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
$q->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL');
$q->addWhere($where_list . ($where_list ? ' AND ' : '') . 't1.task_id = t1.task_parent');
$q->addGroup('p.project_id');
if (!$project_id && !$task_id) {
    $q->addOrder('project_name');
}
if ($project_id > 0) {
    $q->addWhere('p.project_id = ' . $project_id);
}
$q2 = new w2p_Database_Query();
$q2->addTable('projects');
$q2->addQuery('project_id, COUNT(t1.task_id) AS total_tasks');
$q2->addJoin('tasks', 't1', 'projects.project_id = t1.task_project', 'inner');
if ($where_list) {
    $q2->addWhere($where_list);

 public function getTaskLogs($taskId, $problem = false)
 {
     $q = new w2p_Database_Query();
     $q->addTable('task_log');
     $q->addQuery('task_log.*, user_username, billingcode_name as task_log_costcode');
     $q->addQuery('CONCAT(contact_first_name, \' \', contact_last_name) AS real_name');
     $q->addWhere('task_log_task = ' . (int) $taskId . ($problem ? ' AND task_log_problem > 0' : ''));
     $q->addOrder('task_log_date');
     $q->addOrder('task_log_created');
     $q->leftJoin('billingcode', '', 'task_log.task_log_costcode = billingcode_id');
     $q->addJoin('users', '', 'task_log_creator = user_id', 'inner');
     $q->addJoin('contacts', 'ct', 'contact_id = user_contact', 'inner');
     return $q->loadList();
 }

 public static function getOwners()
 {
     $q = new w2p_Database_Query();
     $q->addTable('projects', 'p');
     $q->addQuery('user_id, contact_display_name');
     $q->leftJoin('users', 'u', 'u.user_id = p.project_owner');
     $q->leftJoin('contacts', 'c', 'c.contact_id = u.user_contact');
     $q->addOrder('contact_first_name, contact_last_name');
     $q->addWhere('user_id > 0');
     $q->addWhere('p.project_owner IS NOT NULL');
     return $q->loadHashList();
 }

function displayFiles($AppUI, $folder_id, $task_id, $project_id, $company_id)
{
    global $m, $a, $tab, $xpg_min, $xpg_pagesize, $showProject, $file_types, $cfObj, $xpg_totalrecs, $xpg_total_pages, $page, $company_id, $allowed_companies, $current_uri, $w2Pconfig, $canEdit, $canRead;
    $df = $AppUI->getPref('SHDATEFORMAT');
    $tf = $AppUI->getPref('TIMEFORMAT');
    // SETUP FOR FILE LIST
    $q = new w2p_Database_Query();
    $q->addQuery('f.*, max(f.file_id) as latest_id, count(f.file_version) as file_versions, round(max(file_version), 2) as file_lastversion');
    $q->addQuery('ff.*');
    $q->addTable('files', 'f');
    $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder');
    $q->addJoin('projects', 'p', 'p.project_id = file_project');
    $q->addJoin('tasks', 't', 't.task_id = file_task');
    $q->leftJoin('project_departments', 'project_departments', 'p.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
    $q->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL');
    //TODO: apply permissions properly
    $project = new CProject();
    $deny1 = $project->getDeniedRecords($AppUI->user_id);
    if (count($deny1) > 0) {
        $q->addWhere('file_project NOT IN (' . implode(',', $deny1) . ')');
    }
    //TODO: apply permissions properly
    $task = new CTask();
    $deny2 = $task->getDeniedRecords($AppUI->user_id);
    if (count($deny2) > 0) {
        $q->addWhere('file_task NOT IN (' . implode(',', $deny2) . ')');
    }
    if ($project_id) {
        $q->addWhere('file_project = ' . (int) $project_id);
    }
    if ($task_id) {
        $q->addWhere('file_task = ' . (int) $task_id);
    }
    if ($company_id) {
        $q->addWhere('project_company = ' . (int) $company_id);
    }
    $q->setLimit($xpg_pagesize, $xpg_min);
    $q->addWhere('file_folder = ' . (int) $folder_id);
    $q->addGroup('file_version_id DESC');
    $qv = new w2p_Database_Query();
    $qv->addTable('files');
    $qv->addQuery('file_id, file_version, file_project, file_name, file_task,
		file_description, u.user_username as file_owner, file_size, file_category,
		task_name, file_version_id,  file_checkout, file_co_reason, file_type,
		file_date, cu.user_username as co_user, project_name,
		project_color_identifier, project_owner, con.contact_first_name,
		con.contact_last_name, co.contact_first_name as co_contact_first_name,
		co.contact_last_name as co_contact_last_name ');
    $qv->addJoin('projects', 'p', 'p.project_id = file_project');
    $qv->addJoin('users', 'u', 'u.user_id = file_owner');
    $qv->addJoin('contacts', 'con', 'con.contact_id = u.user_contact');
    $qv->addJoin('tasks', 't', 't.task_id = file_task');
    $qv->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder');
    if ($project_id) {
        $qv->addWhere('file_project = ' . (int) $project_id);
    }
    if ($task_id) {
        $qv->addWhere('file_task = ' . (int) $task_id);
    }
    if ($company_id) {
        $qv->addWhere('project_company = ' . (int) $company_id);
    }
    $qv->leftJoin('users', 'cu', 'cu.user_id = file_checkout');
    $qv->leftJoin('contacts', 'co', 'co.contact_id = cu.user_contact');
    $qv->addWhere('file_folder = ' . (int) $folder_id);
    $files = array();
    $file_versions = array();
    $files = $q->loadList();
    $file_versions = $qv->loadHashList('file_id');
    $q->clear();
    $qv->clear();
    if ($files === array()) {
        return 0;
    }
    $s = '
		<table width="100%" border="0" cellpadding="2" cellspacing="1" class="tbl">
		<tr>
			<th nowrap="nowrap">' . $AppUI->_('File Name') . '</th>
			<th>' . $AppUI->_('Description') . '</th>
			<th>' . $AppUI->_('Versions') . '</th>
		    <th>' . $AppUI->_('Category') . '</th>
			<th nowrap="nowrap">' . $AppUI->_('Task Name') . '</th>
			<th>' . $AppUI->_('Owner') . '</th>
			<th>' . $AppUI->_('Size') . '</th>
			<th>' . $AppUI->_('Type') . '</a></th>
			<th>' . $AppUI->_('Date') . '</th>
	    	<th nowrap="nowrap">' . $AppUI->_('co Reason') . '</th>
	    	<th>' . $AppUI->_('co') . '</th>
			<th nowrap="nowrap" width="5%"></th>
			<th nowrap="nowrap" width="1"></th>
		</tr>';
    $fp = -1;
    $file_date = new w2p_Utilities_Date();
    $id = 0;
    foreach ($files as $row) {
        $latest_file = $file_versions[$row['latest_id']];
        $file_date = new w2p_Utilities_Date($latest_file['file_date']);
        if ($fp != $latest_file['file_project']) {
            if (!$latest_file['file_project']) {
                $latest_file['project_name'] = $AppUI->_('Not attached to a project');
//.........这里部分代码省略.........

    $orderdir = $AppUI->getState('ForumVwOrderDir') ? $AppUI->getState('ForumVwOrderDir') == 'asc' ? 'desc' : 'asc' : 'desc';
    $AppUI->setState('ForumVwOrderBy', w2PgetParam($_GET, 'orderby', null));
    $AppUI->setState('ForumVwOrderDir', $orderdir);
}
$orderby = $AppUI->getState('ForumVwOrderBy') ? $AppUI->getState('ForumVwOrderBy') : 'latest_reply';
$orderdir = $AppUI->getState('ForumVwOrderDir') ? $AppUI->getState('ForumVwOrderDir') : 'desc';
//Pull All Messages
$q = new w2p_Database_Query();
$q->addTable('forum_messages', 'fm1');
$q->addQuery('fm1.*');
$q->addQuery('COUNT(distinct fm2.message_id) AS replies');
$q->addQuery('MAX(fm2.message_date) AS latest_reply');
$q->addQuery('user_username, contact_first_name, contact_last_name, watch_user');
$q->addQuery('count(distinct v1.visit_message) as reply_visits');
$q->addQuery('v1.visit_user');
$q->leftJoin('users', 'u', 'fm1.message_author = u.user_id');
$q->leftJoin('contacts', 'con', 'contact_id = user_contact');
$q->leftJoin('forum_messages', 'fm2', 'fm1.message_id = fm2.message_parent');
$q->leftJoin('forum_watch', 'fw', 'watch_user = ' . (int) $AppUI->user_id . ' AND watch_topic = fm1.message_id');
$q->leftJoin('forum_visits', 'v1', 'v1.visit_user = ' . (int) $AppUI->user_id . ' AND v1.visit_message = fm1.message_id');
$q->addWhere('fm1.message_forum = ' . (int) $forum_id);
switch ($f) {
    case 1:
        $q->addWhere('watch_user IS NOT NULL');
        break;
    case 2:
        $q->addWhere('(NOW() < DATE_ADD(fm2.message_date, INTERVAL 30 DAY) OR NOW() < DATE_ADD(fm1.message_date, INTERVAL 30 DAY))');
        break;
}
$q->addGroup('fm1.message_id, fm1.message_parent');
$q->addOrder($orderby . ' ' . $orderdir);

/**
 * @param $where_list
 * @param $project_id
 * @param $task_id
 * @return Array
 */
function __extract_from_tasks4($where_list, $project_id, $task_id)
{
    $q = new w2p_Database_Query();
    $q->addTable('projects', 'p');
    $q->addQuery('company_name, p.project_id, project_color_identifier, project_name, project_percent_complete, project_task_count');
    $q->addJoin('companies', 'com', 'company_id = project_company', 'inner');
    $q->addJoin('tasks', 't1', 'p.project_id = t1.task_project', 'inner');
    $q->leftJoin('project_departments', 'project_departments', 'p.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
    $q->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL');
    $q->addWhere($where_list . ($where_list ? ' AND ' : '') . 't1.task_id = t1.task_parent');
    $q->addGroup('p.project_id');
    if (!$project_id && !$task_id) {
        $q->addOrder('project_name');
    }
    if ($project_id > 0) {
        $q->addWhere('p.project_id = ' . $project_id);
    }
    $projects = $q->loadList(-1, 'project_id');
    return $projects;
}

 public function getCalendarEvents($userId, $days = 30)
 {
     /*
      * This list of fields - id, name, description, startDate, endDate,
      * updatedDate - are named specifically for the iCal creation.
      * If you change them, it's probably going to break.  So don't do that.
      */
     $q = new w2p_Database_Query();
     $q->addQuery('e.event_id as id');
     $q->addQuery('event_title as name');
     $q->addQuery('event_description as description');
     $q->addQuery('event_start_date as startDate');
     $q->addQuery('event_end_date as endDate');
     $q->addQuery("'" . $q->dbfnNowWithTZ() . "' as updatedDate");
     $q->addQuery('CONCAT(\'' . W2P_BASE_URL . '/index.php?m=calendar&a=view&event_id=' . '\', e.event_id) as url');
     $q->addQuery('projects.project_id, projects.project_name');
     $q->addTable('events', 'e');
     $q->leftJoin('projects', 'projects', 'e.event_project = projects.project_id');
     $q->addWhere('(event_start_date > ' . $q->dbfnNow() . ' OR event_end_date > ' . $q->dbfnNow() . ')');
     $q->addWhere('(event_start_date < ' . $q->dbfnDateAdd($q->dbfnNow(), $days, 'DAY') . ' OR event_end_date < ' . $q->dbfnDateAdd($q->dbfnNow(), $days, 'DAY') . ')');
     $q->innerJoin('user_events', 'ue', 'ue.event_id = e.event_id');
     $q->addWhere('ue.user_id = ' . $userId);
     $q->addOrder('event_start_date');
     return $q->loadList();
 }

 public function getFilteredDepartmentList(CAppUI $AppUI = null, $deptType = -1, $searchString = '', $ownerId = 0, $orderby = 'dept_name', $orderdir = 'ASC')
 {
     global $AppUI;
     $orderby = in_array($orderby, array('dept_name', 'dept_type', 'countp', 'inactive')) ? $orderby : 'dept_name';
     $q = new w2p_Database_Query();
     $q->addTable('departments');
     $q->addQuery('departments.*, COUNT(ct.contact_department) dept_users, count(distinct p.project_id) as countp, count(distinct p2.project_id) as inactive, con.contact_first_name, con.contact_last_name');
     $q->addJoin('companies', 'c', 'c.company_id = departments.dept_company');
     $q->addJoin('project_departments', 'pd', 'pd.department_id = dept_id');
     $q->addJoin('projects', 'p', 'pd.project_id = p.project_id AND p.project_active = 1');
     $q->leftJoin('users', 'u', 'dept_owner = u.user_id');
     $q->leftJoin('contacts', 'con', 'u.user_contact = con.contact_id');
     $q->addJoin('projects', 'p2', 'pd.project_id = p2.project_id AND p2.project_active = 0');
     $q->addJoin('contacts', 'ct', 'ct.contact_department = dept_id');
     $q->addGroup('dept_id');
     $q->addOrder('dept_parent, dept_name');
     $oCpy = new CCompany();
     $where = $oCpy->getAllowedSQL($AppUI->user_id, 'c.company_id');
     $q->addWhere($where);
     if ($deptType > -1) {
         $q->addWhere('dept_type = ' . (int) $deptType);
     }
     if ($searchString != '') {
         $q->addWhere("dept_name LIKE '%{$searchString}%'");
     }
     if ($ownerId > 0) {
         $q->addWhere('dept_owner = ' . $ownerId);
     }
     $q->addGroup('dept_id');
     $q->addOrder($orderby . ' ' . $orderdir);
     return $q->loadList();
 }

 public function sendWatchMail($debug = false)
 {
     global $AppUI, $debug, $w2Pconfig;
     $subj_prefix = $AppUI->_('forumEmailSubj', UI_OUTPUT_RAW);
     $body_msg = $AppUI->_('forumEmailBody', UI_OUTPUT_RAW);
     // Get the message from details.
     $q = new w2p_Database_Query();
     $q->addTable('users', 'u');
     $q->addQuery('contact_first_name, contact_last_name, contact_email');
     $q->addJoin('contacts', 'con', 'contact_id = user_contact', 'inner');
     $q->addWhere('user_id = ' . (int) $this->message_author);
     $res = $q->exec();
     if ($row = $q->fetchRow()) {
         $message_from = $row['contact_first_name'] . ' ' . $row['contact_last_name'] . '<' . $row['contact_email'] . '>';
     } else {
         $message_from = 'Unknown user';
     }
     // Get the forum name;
     $q->clear();
     $q->addTable('forums');
     $q->addQuery('forum_name');
     $q->addWhere('forum_id = \'' . $this->message_forum . '\'');
     $res = $q->exec();
     if ($row = $q->fetchRow()) {
         $forum_name = $row['forum_name'];
     } else {
         $forum_name = 'Unknown';
     }
     // SQL-Query to check if the message should be delivered to all users (forced)
     // In positive case there will be a (0,0,0) row in the forum_watch table
     $q->clear();
     $q->addTable('forum_watch');
     $q->addQuery('*');
     $q->addWhere('watch_user = 0 AND watch_forum = 0 AND watch_topic = 0');
     $resAll = $q->exec();
     $AllCount = db_num_rows($resAll);
     $q->clear();
     $q->addTable('users');
     $q->addQuery('DISTINCT user_id, contact_first_name, contact_last_name, contact_email');
     $q->leftJoin('contacts', 'con', 'con.contact_id = user_contact');
     if ($AllCount < 1) {
         //message is only delivered to users that checked the forum watch
         $q->addTable('forum_watch');
         $q->addWhere('user_id = watch_user AND (watch_forum = ' . (int) $this->message_forum . ' OR watch_topic = ' . (int) $this->message_parent . ')');
     }
     if (!($res = $q->exec(ADODB_FETCH_ASSOC))) {
         $q->clear();
         return;
     }
     if (db_num_rows($res) < 1) {
         return;
     }
     $mail = new w2p_Utilities_Mail();
     $mail->Subject($subj_prefix . ' ' . $this->message_title, isset($GLOBALS['locale_char_set']) ? $GLOBALS['locale_char_set'] : '');
     $body = $body_msg;
     $body .= "\n\n" . $AppUI->_('Forum', UI_OUTPUT_RAW) . ': ' . $forum_name;
     $body .= "\n" . $AppUI->_('Subject', UI_OUTPUT_RAW) . ': ' . $this->message_title;
     $body .= "\n" . $AppUI->_('Message From', UI_OUTPUT_RAW) . ': ' . $message_from;
     $body .= "\n\n" . W2P_BASE_URL . '/index.php?m=forums&a=viewer&forum_id=' . $this->message_forum;
     $body .= "\n\n" . $this->message_body;
     $mail->Body($body, isset($GLOBALS['locale_char_set']) ? $GLOBALS['locale_char_set'] : '');
     while ($row = $q->fetchRow()) {
         if ($mail->ValidEmail($row['contact_email'])) {
             $mail->To($row['contact_email'], true);
             $mail->Send();
         }
     }
     $q->clear();
     return;
 }

    public static function getContacts(CAppUI $AppUI, $companyId)
    {
        $results = array();
        $perms = $AppUI->acl();
        if ($AppUI->isActiveModule('contacts') && canView('contacts') && (int) $companyId > 0) {
            $q = new w2p_Database_Query();
            $q->addQuery('a.*');
            $q->addQuery('dept_name');
            $q->addTable('contacts', 'a');
            $q->leftJoin('companies', 'b', 'a.contact_company = b.company_id');
            $q->leftJoin('departments', '', 'contact_department = dept_id');
            $q->addWhere('contact_company = ' . (int) $companyId);
            $q->addWhere('
				(contact_private=0
					OR (contact_private=1 AND contact_owner=' . $AppUI->user_id . ')
					OR contact_owner IS NULL OR contact_owner = 0
				)');
            $department = new CDepartment();
            $department->setAllowedSQL($AppUI->user_id, $q);
            $q->addOrder('contact_first_name');
            $q->addOrder('contact_last_name');
            $results = $q->loadHashList('contact_id');
        }
        return $results;
    }

** there is a different filter approach in todo
** so we have to tweak a little bit,
** also we do not have a special project available
*/
$caller = w2PgetParam($_REQUEST, 'caller', null);
if ($caller == 'todo') {
    $user_id = w2PgetParam($_REQUEST, 'user_id', $AppUI->user_id);
    $projects[$project_id]['project_name'] = $AppUI->_('Todo for') . ' ' . CContact::getContactByUserid($user_id);
    $projects[$project_id]['project_color_identifier'] = 'ff6000';
    $q = new w2p_Database_Query();
    $q->addQuery('t.*');
    $q->addQuery('project_name, project_id, project_color_identifier');
    $q->addQuery('tp.task_pinned');
    $q->addTable('tasks', 't');
    $q->innerJoin('projects', 'pr', 'pr.project_id = t.task_project');
    $q->leftJoin('user_tasks', 'ut', 'ut.task_id = t.task_id AND ut.user_id = ' . (int) $user_id);
    $q->leftJoin('user_task_pin', 'tp', 'tp.task_id = t.task_id and tp.user_id = ' . (int) $user_id);
    $q->addWhere('(t.task_percent_complete < 100 OR t.task_percent_complete IS NULL)');
    $q->addWhere('t.task_status = 0');
    if (!$showArcProjs) {
        $q->addWhere('pr.project_active = 1');
        if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
            $q->addWhere('pr.project_status <> ' . (int) $template_status);
        }
    }
    if (!$showLowTasks) {
        $q->addWhere('task_priority >= 0');
    }
    if (!$showHoldProjs) {
        $q->addWhere('project_active = 1');
    }