PHP w2p_Database_Query::addGroup方法代码示例

 public static function getDepartments(CAppUI $AppUI, $companyId)
 {
     $perms = $AppUI->acl();
     if ($AppUI->isActiveModule('departments') && canView('departments')) {
         $q = new w2p_Database_Query();
         $q->addTable('departments');
         $q->addQuery('departments.*, COUNT(contact_department) dept_users');
         $q->addJoin('contacts', 'c', 'c.contact_department = dept_id');
         $q->addWhere('dept_company = ' . (int) $companyId);
         $q->addGroup('dept_id');
         $q->addOrder('dept_parent, dept_name');
         $department = new CDepartment();
         $department->setAllowedSQL($AppUI->user_id, $q);
         return $q->loadList();
     }
 }

function projects_list_data($user_id = false)
{
    global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $w2Pconfig, $orderby, $orderdir, $tasks_problems, $owner, $projectTypeId, $search_text, $project_type;
    $addProjectsWithAssignedTasks = $AppUI->getState('addProjWithTasks') ? $AppUI->getState('addProjWithTasks') : 0;
    // get any records denied from viewing
    $obj = new CProject();
    $deny = $obj->getDeniedRecords($AppUI->user_id);
    // Let's delete temproary tables
    $q = new w2p_Database_Query();
    $q->setDelete('tasks_problems');
    $q->exec();
    $q->clear();
    $q->setDelete('tasks_users');
    $q->exec();
    $q->clear();
    // support task problem logs
    $q->addInsertSelect('tasks_problems');
    $q->addTable('tasks');
    $q->addQuery('task_project, task_log_problem');
    $q->addJoin('task_log', 'tl', 'tl.task_log_task = task_id', 'inner');
    $q->addWhere('task_log_problem = 1');
    $q->addGroup('task_project');
    $tasks_problems = $q->exec();
    $q->clear();
    if ($addProjectsWithAssignedTasks) {
        // support users tasks
        $q->addInsertSelect('tasks_users');
        $q->addTable('tasks');
        $q->addQuery('task_project');
        $q->addQuery('ut.user_id');
        $q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
        if ($user_id) {
            $q->addWhere('ut.user_id = ' . (int) $user_id);
        }
        $q->addOrder('task_end_date DESC');
        $q->addGroup('task_project');
        $tasks_users = $q->exec();
        $q->clear();
    }
    // add Projects where the Project Owner is in the given department
    if ($addPwOiD && isset($department)) {
        $owner_ids = array();
        $q->addTable('users');
        $q->addQuery('user_id');
        $q->addJoin('contacts', 'c', 'c.contact_id = user_contact', 'inner');
        $q->addWhere('c.contact_department = ' . (int) $department);
        $owner_ids = $q->loadColumn();
        $q->clear();
    }
    if (isset($department)) {
        //If a department is specified, we want to display projects from the department, and all departments under that, so we need to build that list of departments
        $dept_ids = array();
        $q->addTable('departments');
        $q->addQuery('dept_id, dept_parent');
        $q->addOrder('dept_parent,dept_name');
        $rows = $q->loadList();
        addDeptId($rows, $department);
        $dept_ids[] = isset($department->dept_id) ? $department->dept_id : 0;
        $dept_ids[] = $department > 0 ? $department : 0;
    }
    $q->clear();
    // retrieve list of records
    // modified for speed
    // by Pablo Roca (pabloroca@mvps.org)
    // 16 August 2003
    // get the list of permitted companies
    $obj = new CCompany();
    $companies = $obj->getAllowedRecords($AppUI->user_id, 'companies.company_id,companies.company_name', 'companies.company_name');
    if (count($companies) == 0) {
        $companies = array();
    }
    $q->addTable('projects', 'pr');
    $q->addQuery('pr.project_id, project_status, project_color_identifier,
		project_type, project_name, project_description, project_scheduled_hours as project_duration,
		project_parent, project_original_parent, project_percent_complete,
		project_color_identifier, project_company,
        company_name, project_status, project_last_task as critical_task,
        tp.task_log_problem, user_username, project_active');
    $fields = w2p_Core_Module::getSettings('projects', 'index_list');
    unset($fields['department_list']);
    // added as an alias below
    foreach ($fields as $field => $text) {
        $q->addQuery($field);
    }
    $q->addQuery('CONCAT(ct.contact_first_name, \' \', ct.contact_last_name) AS owner_name');
    $q->addJoin('users', 'u', 'pr.project_owner = u.user_id');
    $q->addJoin('contacts', 'ct', 'ct.contact_id = u.user_contact');
    $q->addJoin('tasks_problems', 'tp', 'pr.project_id = tp.task_project');
    if ($addProjectsWithAssignedTasks) {
        $q->addJoin('tasks_users', 'tu', 'pr.project_id = tu.task_project');
    }
    if (!isset($department) && $company_id && !$addPwOiD) {
        $q->addWhere('pr.project_company = ' . (int) $company_id);
    }
    if ($project_type > -1) {
        $q->addWhere('pr.project_type = ' . (int) $project_type);
    }
    if (isset($department) && !$addPwOiD) {
        $q->addWhere('project_departments.department_id in ( ' . implode(',', $dept_ids) . ' )');
    }
//.........这里部分代码省略.........

 public function getAllowedProjects($userId, $activeOnly = true)
 {
     $q = new w2p_Database_Query();
     $q->addTable('projects', 'pr');
     $q->addQuery('pr.project_id, project_color_identifier, project_name, project_start_date, project_end_date, project_company, project_parent');
     if ($activeOnly) {
         $q->addWhere('project_active = 1');
     }
     $q->addGroup('pr.project_id');
     $q->addOrder('project_name');
     $this->setAllowedSQL($userId, $q, null, 'pr');
     return $q->loadHashList('project_id');
 }

 public function _buildQuery()
 {
     $q = new w2p_Database_Query();
     if ($this->table_alias) {
         $q->addTable($this->table, $this->table_alias);
     } else {
         $q->addTable($this->table);
     }
     $q->addQuery('DISTINCT(' . $this->table_key . ')');
     if (isset($this->table_key2)) {
         $q->addQuery($this->table_key2);
     }
     //--MSy--
     foreach ($this->table_joins as $join) {
         $q->addJoin($join['table'], $join['alias'], $join['join']);
     }
     foreach ($this->display_fields as $fld) {
         $q->addQuery($fld);
     }
     $q->addOrder($this->table_orderby);
     if ($this->table_groupby) {
         $q->addGroup($this->table_groupby);
     }
     if ($this->table_extra) {
         $q->addWhere($this->table_extra);
     }
     $ignore = w2PgetSysVal('FileIndexIgnoreWords');
     $ignore = explode(',', $ignore['FileIndexIgnoreWords']);
     $this->keywords = array_diff(array_keys($this->keywords), $ignore);
     $sql = '';
     foreach ($this->keywords as $keyword) {
         $sql .= '(';
         foreach ($this->search_fields as $field) {
             //OR treatment to each keyword
             // Search for semi-colons, commas or spaces and allow any to be separators
             $or_keywords = preg_split('/[\\s,;]+/', $keyword);
             foreach ($or_keywords as $or_keyword) {
                 if ($this->search_options['ignore_specchar'] == 'on') {
                     $tmppattern = recode2regexp_utf8($or_keyword);
                     if ($this->search_options['ignore_case'] == 'on') {
                         $sql .= ' ' . $field . ' REGEXP \'' . $tmppattern . '\' or ';
                     } else {
                         $sql .= ' ' . $field . ' REGEXP BINARY \'' . $tmppattern . '\' or ';
                     }
                 } else {
                     if ($this->search_options['ignore_case'] == 'on') {
                         $sql .= ' ' . $field . ' LIKE "%' . $or_keyword . '%" or ';
                     } else {
                         $sql .= ' ' . $field . ' LIKE BINARY "%' . $or_keyword . '%" or ';
                     }
                 }
             }
         }
         // foreach $field
         $sql = substr($sql, 0, -4);
         if ($this->search_options['all_words'] == 'on') {
             $sql .= ') and ';
         } else {
             $sql .= ') or ';
         }
     }
     // foreach $keyword
     //--MSy--
     $sql = substr($sql, 0, -4);
     if ($sql) {
         $q->addWhere($sql);
         return $q;
     } else {
         return null;
     }
 }

    $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);
}
if ($project_id > 0) {
    $q2->addWhere('project_id = ' . $project_id);
}
$q2->addGroup('project_id');
$perms =& $AppUI->acl();
$projects = array();
$canViewTask = canView('tasks');
if ($canViewTask) {
    $prc = $q->exec();
    echo db_error();
    while ($row = $q->fetchRow()) {
        $projects[$row['project_id']] = $row;
    }
    $prc2 = $q2->fetchRow();
    echo db_error();
    while ($row2 = $q2->fetchRow()) {
        if ($projects[$row2['project_id']]) {
            array_push($projects[$row2['project_id']], $row2);
        }

 public static function getLogs($userId, $startDate, $endDate)
 {
     $q = new w2p_Database_Query();
     $q->addTable('user_access_log', 'ual');
     $q->addTable('users', 'u');
     $q->addTable('contacts', 'c');
     $q->addQuery('ual.*, u.*, c.*');
     $q->addWhere('ual.user_id = u.user_id');
     $q->addWhere('user_contact = contact_id ');
     if ($userId > 0) {
         $q->addWhere('ual.user_id = ' . (int) $userId);
     }
     $q->addWhere("ual.date_time_in  >= '{$startDate}'");
     $q->addWhere("ual.date_time_out <= '{$endDate}'");
     $q->addGroup('ual.date_time_last_action DESC');
     return $q->loadList();
 }

function w2PgetUsersHashList($stub = null, $where = null, $orderby = 'contact_first_name, contact_last_name')
{
    global $AppUI;
    $q = new w2p_Database_Query();
    $q->addTable('users');
    $q->addQuery('DISTINCT(user_id), user_username, contact_last_name, contact_first_name,
		 company_name, contact_company, dept_id, dept_name, CONCAT(contact_first_name,\' \',contact_last_name) contact_name, user_type');
    $q->addJoin('contacts', 'con', 'con.contact_id = user_contact', 'inner');
    if ($stub) {
        $q->addWhere('(UPPER(user_username) LIKE \'' . $stub . '%\' or UPPER(contact_first_name) LIKE \'' . $stub . '%\' OR UPPER(contact_last_name) LIKE \'' . $stub . '%\')');
    } elseif ($where) {
        $where = $q->quote('%' . $where . '%');
        $q->addWhere('(UPPER(user_username) LIKE ' . $where . ' OR UPPER(contact_first_name) LIKE ' . $where . ' OR UPPER(contact_last_name) LIKE ' . $where . ')');
    }
    $q->addQuery('contact_email');
    $q->addGroup('user_id');
    $q->addOrder($orderby);
    // get CCompany() to filter by company
    $obj = new CCompany();
    $companies = $obj->getAllowedSQL($AppUI->user_id, 'company_id');
    $q->addJoin('companies', 'com', 'company_id = contact_company');
    if ($companies) {
        $q->addWhere('(' . implode(' OR ', $companies) . ' OR contact_company=\'\' OR contact_company IS NULL OR contact_company = 0)');
    }
    $dpt = new CDepartment();
    $depts = $dpt->getAllowedSQL($AppUI->user_id, 'dept_id');
    $q->addJoin('departments', 'dep', 'dept_id = contact_department');
    if ($depts) {
        $q->addWhere('(' . implode(' OR ', $depts) . ' OR contact_department=0)');
    }
    return $q->loadHashList('user_id');
}

$allowedProjects = $project->getAllowedSQL($AppUI->user_id);
$working_hours = $w2Pconfig['daily_working_hours'] ? $w2Pconfig['daily_working_hours'] : 8;
$q->addQuery('projects.project_id, project_color_identifier, project_name');
$q->addQuery('SUM(task_duration * task_percent_complete * IF(task_duration_type = 24, ' . $working_hours . ', task_duration_type)) / SUM(task_duration * IF(task_duration_type = 24, ' . $working_hours . ', task_duration_type)) AS project_percent_complete');
$q->addQuery('company_name');
$q->addTable('projects');
$q->leftJoin('tasks', 't1', 'projects.project_id = t1.task_project');
$q->leftJoin('companies', 'c', 'company_id = project_company');
$q->leftJoin('project_departments', 'project_departments', 'projects.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('t1.task_id = t1.task_parent');
$q->addWhere('projects.project_id=' . $project_id);
if (count($allowedProjects)) {
    $q->addWhere($allowedProjects);
}
$q->addGroup('projects.project_id');
$q2 = new w2p_Database_Query();
$q2 = $q;
$q2->addQuery('projects.project_id, COUNT(t1.task_id) as total_tasks');
$perms =& $AppUI->acl();
$projects = array();
if ($canViewTasks) {
    $prc = $q->exec();
    echo db_error();
    while ($row = $q->fetchRow()) {
        $projects[$row['project_id']] = $row;
    }
    $prc2 = $q2->exec();
    echo db_error();
    while ($row2 = $q2->fetchRow()) {
        $projects[$row2['project_id']] = !$projects[$row2['project_id']] ? array() : $projects[$row2['project_id']];

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

$q->addTable('resources', 'a');
$q->addJoin('resource_tasks', 'b', 'b.resource_id = a.resource_id', 'inner');
$q->addJoin('resource_types', 'c', 'c.resource_type_id = a.resource_type', 'inner');
$q->addWhere('b.task_id = ' . (int) $task_id);
$resources = $q->loadHashList('resource_id');
// Determine any other clashes.
$resource_tasks = array();
if (count($resources)) {
    $q->clear();
    $q->addQuery('b.resource_id, sum(b.percent_allocated) as total_allocated');
    $q->addTable('tasks', 'a');
    $q->addJoin('resource_tasks', 'b', 'b.task_id = a.task_id', 'inner');
    $q->addWhere('b.resource_id IN (' . implode(',', array_keys($resources)) . ')');
    $q->addWhere('task_start_date <= \'' . $obj->task_end_date . '\'');
    $q->addWhere('task_end_date >= \'' . $obj->task_start_date . '\'');
    $q->addGroup('resource_id');
    $resource_tasks = $q->loadHashList();
}
?>
<table class="std" width="100%" cellpadding="4" cellspacing="1">
<tr><th>Type</th><th>Resource</th><th>Allocation</th><th>&nbsp;</th></tr>
<?php 
foreach ($resources as $res) {
    $output = '<tr><td class="hilite">' . $res['resource_type_name'] . '</td>
	<td class="hilite">' . $res['resource_name'] . '</td>
	<td class="hilite">' . $res['percent_allocated'] . '%</td><td class="warning">';
    if (isset($resource_tasks[$res['resource_id']]) && $resource_tasks[$res['resource_id']] > $res['resource_max_allocation']) {
        $output .= 'OVERALLOCATED';
    }
    $output .= '&nbsp;</td></tr>';
    echo $output;

 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();
 }

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

    $q2->addWhere('file_project = ' . (int) $project_id);
}
if ($task_id) {
    $q2->addWhere('file_task = ' . (int) $task_id);
}
if ($company_id) {
    $q2->addWhere('project_company = ' . (int) $company_id);
}
if ($catsql) {
    $q2->addWhere($catsql);
}
$q2->setLimit($xpg_pagesize, $xpg_min);
// Adding an Order by that is different to a group by can cause
// performance issues. It is far better to rearrange the group
// by to get the correct ordering.
$q2->addGroup('p.project_id');
$q2->addGroup('file_version_id DESC');
$q3 = new w2p_Database_Query();
$q3->addTable('files');
$q3->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');
$q3->addQuery('file_folder_id, file_folder_name');
$q3->addJoin('projects', 'p', 'p.project_id = file_project');
$q3->addJoin('users', 'u', 'u.user_id = file_owner');
$q3->addJoin('contacts', 'con', 'con.contact_id = u.user_contact');
$q3->addJoin('tasks', 't', 't.task_id = file_task');

function showcompany($company, $restricted = false)
{
    global $AppUI, $allpdfdata, $log_start_date, $log_end_date, $log_all;
    $q = new w2p_Database_Query();
    $q->addTable('projects');
    $q->addQuery('project_id, project_name');
    $q->addWhere('project_company = ' . (int) $company);
    $projects = $q->loadHashList();
    $q->clear();
    $q->addTable('companies');
    $q->addQuery('company_name');
    $q->addWhere('company_id = ' . (int) $company);
    $company_name = $q->loadResult();
    $q->clear();
    $table = '<h2>Company: ' . $company_name . '</h2>
    	<table cellspacing="1" cellpadding="4" border="0" class="tbl">';
    $project_row = '
        <tr>
                <th>' . $AppUI->_('Project') . '</th>';
    $pdfth[] = $AppUI->_('Project');
    $project_row .= '<th>' . $AppUI->_('Total') . '</th></tr>';
    $pdfth[] = $AppUI->_('Total');
    $pdfdata[] = $pdfth;
    $hours = 0.0;
    $table .= $project_row;
    foreach ($projects as $project => $name) {
        $pdfproject = array();
        $pdfproject[] = $name;
        $project_hours = 0;
        $project_row = '<tr><td>' . $name . '</td>';
        $q->addTable('projects');
        $q->addTable('tasks');
        $q->addTable('task_log');
        $q->addQuery('task_log_costcode, SUM(task_log_hours) as hours');
        $q->addWhere('project_id = ' . (int) $project);
        $q->addWhere('project_active = 1');
        if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
            $q->addWhere('project_status <> ' . (int) $template_status);
        }
        if ($log_start_date != 0 && !$log_all) {
            $q->addWhere('task_log_date >=' . $log_start_date);
        }
        if ($log_end_date != 0 && !$log_all) {
            $q->addWhere('task_log_date <=' . $log_end_date);
        }
        if ($restricted) {
            $q->addWhere('task_log_creator = ' . (int) $AppUI->user_id);
        }
        $q->addWhere('project_id = task_project');
        $q->addWhere('task_id = task_log_task');
        $q->addGroup('project_id');
        $task_logs = $q->loadHashList();
        $q->clear();
        foreach ($task_logs as $task_log) {
            $project_hours += $task_log;
        }
        $project_row .= '<td style="text-align:right;">' . sprintf('%.2f', round($project_hours, 2)) . '</td></tr>';
        $pdfproject[] = round($project_hours, 2);
        $hours += $project_hours;
        if ($project_hours > 0) {
            $table .= $project_row;
            $pdfdata[] = $pdfproject;
        }
    }
    if ($hours > 0) {
        $pdfdata[] = array($AppUI->_('Total'), round($hours, 2));
        $allpdfdata[$company_name] = $pdfdata;
        echo $table;
        echo '<tr><td>' . $AppUI->_('Total') . '</td><td style="text-align:right;">' . sprintf('%.2f', round($hours, 2)) . '</td></tr></table>';
    }
    return $hours;
}

$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);
$topics = $q->loadList();
$crumbs = array();
$crumbs['?m=forums'] = 'forums list';
?>
<br />
<?php 
if (function_exists('styleRenderBoxTop')) {
    echo styleRenderBoxTop();
}
?>
<form name="watcher" action="?m=forums&a=viewer&forum_id=<?php 
echo $forum_id;
?>
&f=<?php