PHP w2p_Database_Query::setLimit方法代码示例

/** Retrieve tasks with first task_end_dates within given project
 * @param int Project_id
 * @param int SQL-limit to limit the number of returned tasks
 * @return array List of criticalTasks
 */
function getCriticalTasksInverted($project_id = null, $limit = 1)
{
    if (!$project_id) {
        $result = array();
        $result[0]['task_end_date'] = '0000-00-00 00:00:00';
        return $result;
    } else {
        $q = new w2p_Database_Query();
        $q->addTable('tasks');
        $q->addWhere('task_project = ' . (int) $project_id . ' AND NOT ISNULL( task_end_date ) AND task_end_date <>  \'0000-00-00 00:00:00\'');
        $q->addOrder('task_start_date ASC');
        $q->setLimit($limit);
        return $q->loadList();
    }
}

 public static function getContactByEmail($email)
 {
     $q = new w2p_Database_Query();
     $q->addTable('users');
     $q->addQuery('contact_first_name, contact_last_name');
     $q->addJoin('contacts', 'con', 'contact_id = user_contact', 'inner');
     $q->leftJoin('contacts_methods', 'cm', 'cm.contact_id = user_contact');
     $q->addWhere("cm.method_value = '{$email}'");
     //TODO: add primary email
     $q->setLimit(1);
     $r = $q->loadResult();
     $result = is_array($r) ? $r[0]['contact_first_name'] . ' ' . $r[0]['contact_last_name'] : 'User Not Found';
     return $result;
 }

		</table>
<?php 
    }
    ?>
	</td>
	<?php 
    if (w2PgetParam($_REQUEST, 'tab', 0) == 0) {
        ?>
	<td>
	       <?php 
        $q = new w2p_Database_Query();
        $q->addTable('user_access_log', 'ual');
        $q->addQuery('user_access_log_id, ( unix_timestamp( \'' . $q->dbfnNowWithTZ() . '\' ) - unix_timestamp( date_time_in ) ) / 3600 as 		hours, ( unix_timestamp( \'' . $q->dbfnNowWithTZ() . '\' ) - unix_timestamp( date_time_last_action ) ) / 3600 as idle, if(isnull(date_time_out) or date_time_out =\'0000-00-00 00:00:00\',\'1\',\'0\') as online');
        $q->addWhere('user_id = ' . (int) $row['user_id']);
        $q->addOrder('user_access_log_id DESC');
        $q->setLimit(1);
        $user_logs = $q->loadList();
        if ($user_logs) {
            foreach ($user_logs as $row_log) {
                if ($row_log['online'] == '1') {
                    echo '<span style="color: green">' . $row_log['hours'] . ' ' . $AppUI->_('hrs.') . '( ' . $row_log['idle'] . ' ' . $AppUI->_('hrs.') . ' ' . $AppUI->_('idle') . ') - ' . $AppUI->_('Online');
                } else {
                    echo '<span style="color: red">' . $AppUI->_('Offline');
                }
            }
        } else {
            echo '<span style="color: grey">' . $AppUI->_('Never Visited');
        }
        echo '</span>';
    }
    ?>

 /** Retrieve tasks with latest task_end_dates within given project
  * @param int Project_id
  * @param int SQL-limit to limit the number of returned tasks
  * @return array List of criticalTasks
  */
 public function getCriticalTasks($project_id = null, $limit = 1)
 {
     $project_id = !empty($project_id) ? $project_id : $this->project_id;
     $q = new w2p_Database_Query();
     $q->addTable('tasks');
     $q->addWhere('task_project = ' . (int) $project_id . ' AND task_end_date IS NOT NULL AND task_end_date <>  \'0000-00-00 00:00:00\'');
     $q->addOrder('task_end_date DESC');
     $q->setLimit($limit);
     return $q->loadList();
 }

/**
 * @param $row
 *
 * @return Array
 */
function __extract_from_vw_usr($row)
{
    $q = new w2p_Database_Query();
    $q->addTable('user_access_log', 'ual');
    $q->addQuery('user_access_log_id, ( unix_timestamp( \'' . $q->dbfnNowWithTZ() . '\' ) - unix_timestamp( date_time_in ) ) / 3600 as 		hours, ( unix_timestamp( \'' . $q->dbfnNowWithTZ() . '\' ) - unix_timestamp( date_time_last_action ) ) / 3600 as idle, if(isnull(date_time_out) or date_time_out =\'0000-00-00 00:00:00\',\'1\',\'0\') as online');
    $q->addWhere('user_id = ' . (int) $row['user_id']);
    $q->addOrder('user_access_log_id DESC');
    $q->setLimit(1);
    $user_logs = $q->loadList();
    return $user_logs;
}

    $q->addWhere('history_user = user_id');
    $q->addTable('contacts');
    $q->addWhere('contact_id = user_contact');
    $q->addWhere($filter);
    $count = (int) $q->loadResult();
    $q = new w2p_Database_Query();
    $q->addQuery('history_date, history_id, history_item, history_table, history_description, history_action');
    $q->addQuery('CONCAT(contact_first_name, \' \', contact_last_name) AS history_user_name');
    $q->addTable('history', 'h');
    $q->addTable('users');
    $q->addWhere('history_user = user_id');
    $q->addTable('contacts');
    $q->addWhere('contact_id = user_contact');
    $q->addWhere($filter);
    $q->addOrder('history_date DESC');
    $q->setLimit($limit, $offset);
    $history = $q->loadList();
} else {
    $history = array();
}
$pages = (int) ($count / $limit) + 1;
$max_pages = 20;
if ($pages > $max_pages) {
    $first_page = max($page - (int) ($max_pages / 2), 1);
    $last_page = min($first_page + $max_pages - 1, $pages);
} else {
    $first_page = 1;
    $last_page = $pages;
}
?>

if (count($allowedTasks)) {
    $q2->addWhere('( ( ' . implode(' AND ', $allowedTasks) . ') OR file_task = 0 )');
}
if ($project_id) {
    $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');

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');
//.........这里部分代码省略.........