PHP DBQuery::leftJoin方法代码示例

 public function getProjectTaskLinksByCategory($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 DBQuery();
     $q->addQuery('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();
 }

function sendNewPass()
{
    global $AppUI;
    $_live_site = dPgetConfig('base_url');
    $_sitename = dPgetConfig('company_name');
    // ensure no malicous sql gets past
    $checkusername = trim(dPgetParam($_POST, 'checkusername', ''));
    $checkusername = db_escape($checkusername);
    $confirmEmail = trim(dPgetParam($_POST, 'checkemail', ''));
    $confirmEmail = mb_strtolower(db_escape($confirmEmail));
    $q = new DBQuery();
    $q->addTable('users', 'u');
    $q->addQuery('u.user_id');
    $q->addWhere('user_username=\'' . $checkusername . '\' AND LOWER(contact_email)=\'' . $confirmEmail . '\'');
    $q->leftJoin('contacts', 'c', 'u.user_contact = c.contact_id');
    if (!($user_id = $q->loadResult()) || !$checkusername || !$confirmEmail) {
        $AppUI->setMsg('Invalid username or email.', UI_MSG_ERROR);
        $AppUI->redirect();
    }
    $newpass = makePass();
    $message = $AppUI->_('sendpass0', UI_OUTPUT_RAW) . ' ' . $checkusername . ' ' . $AppUI->_('sendpass1', UI_OUTPUT_RAW) . ' ' . $_live_site . ' ' . $AppUI->_('sendpass2', UI_OUTPUT_RAW) . ' ' . $newpass . ' ' . $AppUI->_('sendpass3', UI_OUTPUT_RAW);
    $subject = "{$_sitename} :: " . $AppUI->_('sendpass4', UI_OUTPUT_RAW) . " - {$checkusername}";
    $m = new Mail();
    // create the mail
    $m->From("dotProject@" . dPgetConfig('site_domain'));
    $m->To($confirmEmail);
    $m->Subject($subject);
    $m->Body($message, isset($GLOBALS['locale_char_set']) ? $GLOBALS['locale_char_set'] : "");
    // set the body
    $m->Send();
    // send the mail
    $newpass = md5($newpass);
    $q->clear();
    $q->addTable('users');
    $q->addUpdate('user_password', $newpass, true);
    $q->addWhere('user_id=\'' . $user_id . '\'');
    $cur = $q->exec();
    if (!$cur) {
        die('SQL error' . $database->stderr(true));
    } else {
        $AppUI->setMsg('New User Password created and emailed to you');
        $AppUI->redirect();
    }
}

    foreach ($arr as $L) {
        $let .= $L['L'];
    }
}
// optional fields shown in the list (could be modified to allow breif and verbose, etc)
$showfields = array("contact_company" => "contact_company", "company_name" => "company_name", "contact_phone" => "contact_phone", "contact_email" => "contact_email");
require_once $AppUI->getModuleClass('companies');
$company = new CCompany();
$allowedCompanies = $company->getAllowedSQL($AppUI->user_id);
// assemble the sql statement
$q = new DBQuery();
$q->addQuery('contact_id, contact_order_by');
$q->addQuery($showfields);
$q->addQuery('contact_first_name, contact_last_name, contact_phone');
$q->addTable('contacts', 'a');
$q->leftJoin('companies', 'b', 'a.contact_company = b.company_id');
foreach ($search_map as $search_name) {
    $where_filter .= " OR {$search_name} LIKE '{$where}%'";
}
$where_filter = mb_substr($where_filter, 4);
$q->addWhere("({$where_filter} {$additional_filter})");
$q->addWhere("\n\t(contact_private=0\n\t\tOR (contact_private=1 AND contact_owner={$AppUI->user_id})\n\t\tOR contact_owner IS NULL OR contact_owner = 0\n\t)");
if (count($allowedCompanies)) {
    $comp_where = implode(' AND ', $allowedCompanies);
    $q->addWhere('((' . $comp_where . ') OR contact_company = 0)');
}
$q->addOrder('contact_order_by');
$carr[] = array();
$carrWidth = 4;
$carrHeight = 4;
$sql = $q->prepare();

$task_sort_type2 = w2PgetParam($_GET, 'task_sort_type2', '');
$task_sort_order1 = intval(w2PgetParam($_GET, 'task_sort_order1', 0));
$task_sort_order2 = intval(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->getAllowedRecords($AppUI->user_id, 'project_id, project_name');
$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 DBQuery();
$q2 = $q;
$q2->addQuery('projects.project_id, COUNT(t1.task_id) as total_tasks');
$perms =& $AppUI->acl();
$projects = array();
if ($canViewTasks) {

        $q->addTable('companies', 'c');
        $q->addQuery('c.company_name');
        $q->addWhere('company_id = ' . $company_id);
        $company_name = $q->loadResult();
        $q->clear();
        /*
        	$sql = "select c.company_name from companies as c where company_id = $company_id";
        	$company_name = db_loadResult($sql);
        */
        $company_name_sql = db_escape($company_name);
        $where = " (contact_company = '{$company_name_sql}' or contact_company = '{$company_id}')";
    }
}
// This should now work on company ID, but we need to be able to handle both
$q->addTable('contacts', 'a');
$q->leftJoin('companies', 'b', 'b.company_id = a.contact_company');
$q->leftJoin('departments', 'c', 'c.dept_id = a.contact_department');
$q->leftJoin('users', 'u', 'u.user_contact=a.contact_id');
$q->addQuery('a.contact_id, a.contact_first_name, a.contact_last_name,' . ' a.contact_company, a.contact_department');
$q->addQuery('b.company_name');
$q->addQuery('c.dept_name');
$q->addQuery('u.user_id');
if ($where) {
    // Don't assume where is set. Change needed to fix Mantis Bug 0002056
    $q->addWhere($where);
}
$q->addWhere('(contact_owner = ' . $AppUI->user_id . ' OR contact_private = 0)');
//May need to review this order.
$q->addOrder('company_name, contact_company, dept_name, contact_department' . ', contact_last_name');
$contacts = $q->loadHashList('contact_id');
global $task_id, $project_id;

function dPgetSysVal($title)
{
    $q = new DBQuery();
    $q->addTable('sysvals');
    $q->leftJoin('syskeys', 'sk', 'syskey_id = sysval_key_id');
    $q->addQuery('syskey_type, syskey_sep1, syskey_sep2, sysval_value');
    $q->addWhere("sysval_title = '{$title}'");
    $q->exec();
    $row = $q->fetchRow();
    $q->clear();
    // type 0 = list
    $sep1 = $row['syskey_sep1'];
    // item separator
    $sep2 = $row['syskey_sep2'];
    // alias separator
    // A bit of magic to handle newlines and returns as separators
    // Missing sep1 is treated as a newline.
    if (!isset($sep1) || empty($sep1)) {
        $sep1 = "\n";
    }
    if ($sep1 == "\\n") {
        $sep1 = "\n";
    }
    if ($sep1 == "\\r") {
        $sep1 = "\r";
    }
    $temp = explode($sep1, $row['sysval_value']);
    $arr = array();
    // We use trim() to make sure a numeric that has spaces
    // is properly treated as a numeric
    foreach ($temp as $item) {
        if ($item) {
            $sep2 = empty($sep2) ? "\n" : $sep2;
            $temp2 = explode($sep2, $item);
            if (isset($temp2[1])) {
                $arr[trim($temp2[0])] = trim($temp2[1]);
            } else {
                $arr[trim($temp2[0])] = trim($temp2[0]);
            }
        }
    }
    return $arr;
}

function displayFiles($folder_id)
{
    global $AppUI, $m, $a, $tab, $page;
    global $current_uri;
    global $canAccess, $canRead, $canEdit, $canAuthor, $canDelete;
    global $canAccess_folders, $canRead_folders, $canEdit_folders;
    global $canAuthor_folders, $canDelete_folders;
    global $company_id, $project_id, $task_id;
    global $allowedCompanies, $allowedProjects, $allowedTasks, $allowedFolders;
    global $showProject, $cfObj, $dPconfig;
    $df = $AppUI->getPref('SHDATEFORMAT');
    $tf = $AppUI->getPref('TIMEFORMAT');
    $file_types = dPgetSysVal('FileType');
    $xpg_pagesize = 30;
    //TODO?: Set by System Config Value ...
    $xpg_totalrecs = countFiles($folder_id);
    //get file count for folder
    $xpg_total_pages = $xpg_totalrecs > $xpg_pagesize ? ceil($xpg_totalrecs / $xpg_pagesize) : 1;
    $xpg_min = $xpg_pagesize * ($page - 1);
    // This is where we start our record set from
    $q = new DBQuery();
    // most recent version info per file_project and file_version_id
    $q->createTemp('files_count_max' . $folder_id);
    $q->addTable('files', 'f');
    $q->addQuery('DISTINCT count(f.file_id) as file_versions' . ', max(f.file_version) as file_lastversion' . ', file_version_id, f.file_project');
    $q->addJoin('projects', 'p', 'p.project_id = f.file_project');
    $q->addJoin('tasks', 't', 't.task_id = f.file_task');
    $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
    $q->addWhere('f.file_folder = ' . $folder_id);
    if (count($allowedProjects)) {
        $q->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
    }
    if (count($allowedTasks)) {
        $q->addWhere('((' . implode(' AND ', $allowedTasks) . ') OR f.file_task = 0)');
    }
    if (count($allowedFolders)) {
        $q->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
    }
    if ($company_id) {
        $q->innerJoin('companies', 'co', 'co.company_id = p.project_company');
        $q->addWhere('co.company_id = ' . $company_id);
        if (count($allowedCompanies)) {
            $q->addWhere('(' . implode(' AND ', $allowedCompanies) . ')');
        }
    }
    $q->addGroup('f.file_version_id');
    $q->addGroup('f.file_project');
    $file_version_max_counts = $q->exec();
    $q->clear();
    // most recent version
    $q->addTable('files', 'f');
    $q->addQuery('f.*, fmc.file_versions, round(fmc.file_lastversion, 2) as file_lastversion' . ', u.user_username as file_owner, ff.file_folder_name' . ', ff.file_folder_id, ff.file_folder_name, p.project_name' . ', p.project_color_identifier, p.project_owner, c.contact_first_name' . ', c.contact_last_name, t.task_name, u.user_username as file_owner' . ', cc.contact_first_name as checkout_first_name' . ', cc.contact_last_name as checkout_last_name');
    $q->addJoin('files_count_max' . $folder_id, 'fmc', '(fmc.file_lastversion=f.file_version AND fmc.file_version_id=f.file_version_id' . ' AND fmc.file_project=f.file_project)', 'inner');
    $q->addJoin('projects', 'p', 'p.project_id = f.file_project');
    $q->addJoin('users', 'u', 'u.user_id = f.file_owner');
    $q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact');
    $q->addJoin('tasks', 't', 't.task_id = f.file_task');
    $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
    $q->leftJoin('users', 'cu', 'cu.user_id = f.file_checkout');
    $q->leftJoin('contacts', 'cc', 'cc.contact_id = cu.user_contact');
    $q->addWhere('f.file_folder = ' . $folder_id);
    if (count($allowedProjects)) {
        $q->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
    }
    if (count($allowedTasks)) {
        $q->addWhere('((' . implode(' AND ', $allowedTasks) . ') OR f.file_task = 0)');
    }
    if (count($allowedFolders)) {
        $q->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
    }
    if ($project_id) {
        $q->addWhere('f.file_project = ' . $project_id);
    }
    if ($task_id) {
        $q->addWhere('f.file_task = ' . $task_id);
    }
    if ($company_id) {
        $q->innerJoin('companies', 'co', 'co.company_id = p.project_company');
        $q->addWhere('co.company_id = ' . $company_id);
        if (count($allowedCompanies)) {
            $q->addWhere('(' . implode(' AND ', $allowedCompanies) . ')');
        }
    }
    $q->addOrder('p.project_name');
    $q->setLimit($xpg_pagesize, $xpg_min);
    $files_sql = $q->prepare();
    $q->clear();
    // all versions
    $q->addTable('files', 'f');
    $q->addQuery('f.*, ff.file_folder_id, ff.file_folder_name, p.project_name' . ', p.project_color_identifier, p.project_owner, c.contact_first_name' . ', c.contact_last_name, t.task_name, u.user_username as file_owner');
    $q->addJoin('projects', 'p', 'p.project_id = f.file_project');
    $q->addJoin('users', 'u', 'u.user_id = f.file_owner');
    $q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact');
    $q->addJoin('tasks', 't', 't.task_id = f.file_task');
    $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
    $q->addWhere('f.file_folder = ' . $folder_id);
    if (count($allowedProjects)) {
        $q->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
    }
    if (count($allowedTasks)) {
//.........这里部分代码省略.........

    $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 DBQuery();
$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);

$q->addTable('users');
$q->addWhere('user_id = ' . (int) $user_id);
$contact_id = $q->loadResult();
$q->addQuery('distinct project_id, project_name');
$q->addTable('projects');
$q->addWhere('project_owner = ' . (int) $user_id);
$projects += $q->loadHashList();
$q->addQuery('distinct prj.project_id, prj.project_name');
$q->addTable('projects', 'prj');
$q->innerJoin('project_contacts', 'prc', array('project_id'));
$q->addWhere('prc.contact_id = ' . (int) $contact_id);
$projects += $q->loadHashList();
$q->addQuery('distinct prj.project_id, prj.project_name');
$q->addTable('tasks', 't');
$q->innerJoin('projects', 'prj', 'prj.project_id = t.task_project');
$q->leftJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
$q->addWhere('t.task_owner = ' . (int) $user_id . ' OR ut.user_id = ' . (int) $user_id);
$projects += $q->loadHashList();
$q->addQuery('distinct prj.project_id, prj.project_name');
$q->addTable('tasks', 't');
$q->innerJoin('projects', 'prj', 'prj.project_id = t.task_project');
$q->innerJoin('task_contacts', 'tc', 'tc.task_id = t.task_id');
$q->addWhere('tc.contact_id = ' . (int) $contact_id);
$projects += $q->loadHashList();
$q->addQuery('user_id, concat(u.user_username, \' (\', c.contact_first_name, \' \', c.contact_last_name, \')\') as username');
$q->addTable('users', 'u');
$q->leftJoin('contacts', 'c', 'c.contact_id = u.user_contact');
$q->addWhere('u.user_id != ' . (int) $user_id);
$q->addOrder('u.user_username');
$users = $q->loadHashList();
?>

$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('f.file_version_id DESC');
$q3 = new DBQuery();
$q3->addQuery('f.file_id, f.file_version, f.file_version_id, f.file_project, f.file_name' . ', f.file_task, t.task_name, f.file_description, f.file_checkout, f.file_co_reason' . ', u.user_username as file_owner, f.file_size, f.file_category, f.file_type' . ', f.file_date, cu.user_username as co_user, p.project_name' . ', p.project_color_identifier, p.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('ff.*');
$q3->addTable('files', 'f');
$q3->addJoin('users', 'u', 'u.user_id = file_owner');
$q3->addJoin('contacts', 'con', 'con.contact_id = u.user_contact');
$q3->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
$q3->addJoin('projects', 'p', 'p.project_id = f.file_project');
$q3->addJoin('tasks', 't', 't.task_id = f.file_task');
$q3->leftJoin('users', 'cu', 'cu.user_id = f.file_checkout');
$q3->leftJoin('contacts', 'co', 'co.contact_id = cu.user_contact');
if (count($allowedProjects)) {
    $q3->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
}
if (count($allowedTasks)) {
    $q3->addWhere('((' . implode(' AND ', $allowedTasks) . ') OR f.file_task = 0)');
}
if (count($allowedFolders)) {
    $q3->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
}
if ($category_filter) {
    $q3->addWhere($category_filter);
}
if ($company_id) {
    $q3->addWhere('p.project_company = ' . $company_id);

?>
" class="text" disabled="disabled" />
		<a href="#" onclick="javascript:popCalendar('end_date')">
			<img src="./images/calendar.gif" width="24" height="12" alt="<?php 
echo $AppUI->_('Calendar');
?>
" border="0" />
		</a>
	</td>

	<td nowrap='nowrap'>
	   <?php 
$q = new DBQuery();
$q->addTable('users', 'u');
$q->addQuery('u.user_id, concat_ws(\' \', c.contact_first_name, c.contact_last_name)');
$q->leftJoin('permissions', 'p', '(u.user_id = p.permission_user)');
$q->leftJoin('contacts', 'c', '(u.user_contact = c.contact_id)');
$q->addWhere('!isnull(p.permission_user)');
$users = array(0 => $AppUI->_("All")) + $q->loadHashList();
echo arraySelect($users, "user_id", "class='text'", $user_id);
?>
	</td>
	
	<td align="right" width="50%" nowrap="nowrap">
		<input class="button" type="submit" name="do_report" value="<?php 
echo $AppUI->_('submit');
?>
" />
	</td>
</tr>

echo $AppUI->_('submit');
?>
" />
	</td>
</tr>
</table>
</form>

<?php 
if ($do_report) {
    $q = new DBQuery();
    $q->addQuery('p.project_id, p.project_name, t.*, 
		CONCAT_WS(\' \',contact_first_name,contact_last_name) AS creator,
		if (bc.billingcode_name is null, \'\', bc.billingcode_name) as billingcode_name');
    $q->addTable('task_log', 't');
    $q->leftJoin('billingcode', 'bc', 'bc.billingcode_id = t.task_log_costcode');
    $q->leftJoin('users', 'u', 'user_id = task_log_creator');
    $q->leftJoin('contacts', 'c', 'u.user_contact = contact_id');
    $q->innerJoin('tasks', 'tsk', 't.task_log_task = tsk.task_id');
    $q->leftJoin('projects', 'p', 'p.project_id = task_project');
    if ($project_id != 0) {
        $q->addWhere('task_project = ' . (int) $project_id);
    }
    if (!$log_all) {
        $q->addWhere('task_log_date >= \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\'');
        $q->addWhere('task_log_date <= \'' . $end_date->format(FMT_DATETIME_MYSQL) . "'");
    }
    if ($log_ignore) {
        $q->addWhere('task_log_hours > 0');
    }
    if ($log_userfilter) {

if ($project_id) {
    $q2->addWhere("file_project = {$project_id}");
}
if ($task_id) {
    $q2->addWhere("file_task = {$task_id}");
}
$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('project_id');
$q2->addGroup('file_version_id DESC');
$q3 = new DBQuery();
$q3->addQuery("file_id, file_version, file_version_id, file_project, file_name, file_task, task_name, file_description, file_checkout, file_co_reason, u.user_username as file_owner, file_size, file_category, file_type, file_date, cu.user_username as co_user, project_name, project_color_identifier, project_active, project_owner, contact_first_name, contact_last_name");
$q3->addTable('files');
$q3->leftJoin('users', 'cu', 'cu.user_id = file_checkout');
$q3->leftJoin('users', 'u', 'u.user_id = file_owner');
$q3->leftJoin('contacts', 'con', 'con.contact_id = u.user_contact');
//$q3->leftJoin('tasks', 't', 't.task_id = file_task');
//$q3->leftJoin('projects', 'p', 'p.project_id = file_project');
$project->setAllowedSQL($AppUI->user_id, $q3, 'file_project');
$task->setAllowedSQL($AppUI->user_id, $q3, 'file_task');
if ($project_id) {
    $q3->addWhere("file_project = {$project_id}");
}
if ($task_id) {
    $q3->addWhere("file_task = {$task_id}");
}
$files = array();
$file_versions = array();
if ($canRead) {

 $text .= sprintf("%s", "\"Directory Server\",\"E-mail Address\",\"E-mail Type\",\"E-mail Display Name\",\"E-mail 2 Address\",");
 // Fields 61 - 65
 $text .= sprintf("%s", "\"E-mail 2 Type\",\"E-mail 2 Display Name\",\"E-mail 3 Address\",\"E-mail 3 Type\",\"E-mail 3 Display Name\",");
 // Fields 66 - 70
 $text .= sprintf("%s", "\"Gender\",\"Government ID Number\",\"Hobby\",\"Initials\",\"Internet Free Busy\",");
 // Fields 71 - 75
 $text .= sprintf("%s", "\"Keywords\",\"Language\",\"Location\",\"Manager's Name\",\"Mileage\",");
 // Fields 76 - 80
 $text .= sprintf("%s", "\"Notes\",\"Office Location\",\"Organizational ID Number\",\"PO Box\",\"Priority\",");
 // Fields 81 - 85
 $text .= sprintf("%s", "\"Private\",\"Profession\",\"Referred By\",\"Sensitivity\",\"Spouse\",");
 // Fields 86 - 90
 $text .= sprintf("%s\r\n", "\"User 1\",\"User 2\",\"User 3\",\"User 4\",\"Web Page\"");
 $q = new DBQuery();
 $q->addTable('contacts', 'con');
 $q->leftJoin('companies', 'co', 'co.company_id = con.contact_company');
 $q->leftJoin('departments', 'de', 'de.dept_id = con.contact_department');
 $q->addQuery('con.*');
 $q->addQuery('co.company_name');
 $q->addQuery('de.dept_name');
 $contacts = $q->loadList();
 foreach ($contacts as $row) {
     // Fields 1- 10
     $text .= sprintf("\"\",\"%s\",\"\",\"%s\",\"\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",", $row['contact_first_name'], $row['contact_last_name'], $row['company_name'], $row['dept_name'], $row['contact_title'], $row['contact_address1'], $row['contact_address2']);
     // Fields 11- 20
     //$text .= sprintf("\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",");
     $text .= sprintf(",\"%s\",\"%s\",\"%s\",,,,,,,", $row['contact_city'], $row['contact_state'], $row['contact_zip']);
     // Fields 21- 30
     $text .= sprintf(",,,,,,,,,,");
     // Fields 31- 40
     settype($row['contact_phone'], 'string');

$filter_module_tables = array();
$denied_tables = '';
foreach ($available_modules as $my_mod => $my_mod_data) {
    $my_mod_table = $my_mod_data['permissions_item_table'];
    $filter_options[$my_mod]['Name'] = $my_mod_data['mod_name'];
    $filter_options[$my_mod]['Table'] = $my_mod_table;
    $filter_options[$my_mod]['Table_ID'] = $my_mod_data['permissions_item_field'];
    $filter_options[$my_mod]['Table_ID_Name'] = $my_mod_data['permissions_item_label'];
    $filter_module_tables[$my_mod] = $my_mod_table;
    if ($my_mod_table && !getPermission($my_mod, 'view')) {
        $denied_tables .= ($denied_module_list ? "','" : '') . $my_mod_table;
    }
}
$q->includeCount();
$q->addTable('history', 'h');
$q->leftJoin('users', 'u', 'u.user_id = h.history_user');
$q->addQuery('h.*, u.*');
if ($in_filter) {
    $filter .= ($filter ? ' AND ' : '') . "(h.`history_table` LIKE '" . $in_filter . "%')";
}
if ($denied_tables) {
    $filter .= ($filter ? ' AND ' : '') . "(NOT h.`history_table` IN ('" . $denied_tables . "'))";
}
if (!empty($_REQUEST['project_id'])) {
    $project_id = $_REQUEST['project_id'];
    $r = new DBQuery();
    $r->addTable('tasks');
    $r->addQuery('task_id');
    $r->addWhere('task_project = ' . $project_id);
    $project_tasks = implode(',', $r->loadColumn());
    $r->clear();