PHP DBQuery::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 DBQuery();
        $q->addTable('tasks');
        $q->addWhere("task_project = {$project_id} AND !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();
    }
}

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

$q = new DBQuery();
$q->addTable('forum_messages');
$q->addQuery('forum_messages.*, user_username');
$q->addJoin('users', 'u', 'message_author = u.user_id');
$q->addWhere('message_id = ' . ($message_id ? $message_id : $message_parent));
$res = $q->exec();
echo db_error();
$message_info = $q->fetchRow();
$q->clear();
//pull message information from last response
if ($message_parent != -1) {
    $q->addTable('forum_messages');
    $q->addWhere('message_parent = ' . ($message_id ? $message_id : $message_parent));
    $q->addOrder('message_id DESC');
    // fetch last message first
    $q->setLimit(1);
    $res = $q->exec();
    echo db_error();
    $last_message_info = $q->fetchRow();
    if (!$last_message_info) {
        // if it's first response, use original message
        $last_message_info =& $message_info;
        $last_message_info["message_body"] = wordwrap(@$last_message_info["message_body"], 50, "\n> ");
    } else {
        $last_message_info["message_body"] = str_replace("\n", "\n> ", @$last_message_info["message_body"]);
    }
    $q->clear();
}
$crumbs = array();
$crumbs["?m=forums"] = "forums list";
$crumbs["?m=forums&a=viewer&forum_id={$forum_id}"] = "topics for this forum";

function displayFiles($folder)
{
    global $m, $a, $tab, $AppUI, $xpg_min, $xpg_pagesize;
    global $deny1, $deny2, $project_id, $task_id, $showProject, $file_types, $cfObj;
    global $xpg_totalrecs, $xpg_total_pages, $page;
    global $company_id, $allowed_companies, $current_uri, $dPconfig;
    $canEdit = !getDenyEdit($m, $folder);
    $canRead = !getDenyRead($m, $folder);
    $df = $AppUI->getPref('SHDATEFORMAT');
    $tf = $AppUI->getPref('TIMEFORMAT');
    // SETUP FOR FILE LIST
    $q = new DBQuery();
    $q->addTable('files');
    $q->addQuery('files.*,count(file_version) as file_versions,round(max(file_version), 2) as file_lastversion,file_folder_id, file_folder_name,project_name, project_color_identifier,contact_first_name, contact_last_name,task_name,task_id');
    $q->addJoin('projects', 'p', 'p.project_id = file_project');
    $q->addJoin('users', 'u', 'u.user_id = file_owner');
    $q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact');
    $q->addJoin('tasks', 't', 't.task_id = file_task');
    $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder');
    $q->addWhere('file_folder = ' . $folder);
    if (count($deny1) > 0) {
        $q->addWhere('file_project NOT IN (' . implode(',', $deny1) . ')');
    }
    if (count($deny2) > 0) {
        $q->addWhere('file_task NOT IN (' . implode(',', $deny2) . ')');
    }
    if ($project_id) {
        $q->addWhere('file_project = ' . $project_id);
    }
    if ($task_id) {
        $q->addWhere('file_task = ' . $task_id);
    }
    if ($company_id) {
        $q->innerJoin('companies', 'co', 'co.company_id = p.project_company');
        $q->addWhere('company_id = ' . $company_id);
        $q->addWhere('company_id IN (' . $allowed_companies . ')');
    }
    $q->addGroup('file_folder');
    $q->addGroup('project_name');
    $q->addGroup('file_name');
    $q->addOrder('file_folder');
    $q->addOrder('project_name');
    $q->addOrder('file_name');
    $q->setLimit($xpg_pagesize, $xpg_min);
    $files_sql = $q->prepare();
    $q->clear();
    $q = new DBQuery();
    $q->addTable('files');
    $q->addQuery('files.file_id, file_version, file_project, file_name, file_task, file_description, user_username as file_owner, file_size, file_category, file_type, file_date, file_folder_name');
    $q->addJoin('projects', 'p', 'p.project_id = file_project');
    $q->addJoin('users', 'u', 'u.user_id = file_owner');
    $q->addJoin('tasks', 't', 't.task_id = file_task');
    $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder');
    $q->addWhere('file_folder = ' . $folder);
    if ($project_id) {
        $q->addWhere('file_project = ' . $project_id);
    }
    if ($task_id) {
        $q->addWhere('file_task = ' . $task_id);
    }
    if ($company_id) {
        $q->innerJoin('companies', 'co', 'co.company_id = p.project_company');
        $q->addWhere('company_id = ' . $company_id);
        $q->addWhere('company_id IN (' . $allowed_companies . ')');
    }
    $file_versions_sql = $q->prepare();
    $q->clear();
    $files = array();
    $file_versions = array();
    if ($canRead) {
        $files = db_loadList($files_sql);
        $file_versions = db_loadList($file_versions_sql);
    }
    if ($files === array()) {
        return 0;
    }
    ?>
	<table width="100%" border="0" cellpadding="2" cellspacing="1" class="tbl">
	<tr>
		<th nowrap="nowrap"><?php 
    echo $AppUI->_('File Name');
    ?>
</th>
		<th><?php 
    echo $AppUI->_('Description');
    ?>
</th>
		<th><?php 
    echo $AppUI->_('Versions');
    ?>
</th>
	    <th><?php 
    echo $AppUI->_('Category');
    ?>
</th>
		<th nowrap="nowrap"><?php 
    echo $AppUI->_('Task Name');
    ?>
</th>
		<th><?php 
//.........这里部分代码省略.........

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

 function getCriticalTasks($project_id = NULL, $limit = 1)
 {
     $project_id = !empty($project_id) ? $project_id : $this->project_id;
     $q = new DBQuery();
     $q->addTable('tasks');
     if ($project_id) {
         $q->addWhere('task_project = ' . $project_id);
     }
     $q->addWhere("!isnull(task_end_date) AND task_end_date !=  '0000-00-00 00:00:00'");
     $q->addOrder('task_end_date DESC');
     $q->setLimit($limit);
     return $q->loadList();
 }

if (count($allowedFolders)) {
    $q2->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
}
if ($category_filter) {
    $q2->addWhere($category_filter);
}
if ($company_id) {
    $q2->addWhere('p.project_company = ' . $company_id);
}
if ($project_id) {
    $q2->addWhere('f.file_project = ' . $project_id);
}
if ($task_id) {
    $q2->addWhere('f.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('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');

    $first_task = current($tasks);
    $actual_project_id = 0;
    $first_task = true;
    $task_log = array();
    echo "<table class='tbl' width='80%' summary='task log'>";
    echo "<tr><th>" . $AppUI->_("Task name") . "</th><th>" . $AppUI->_("T.Owner") . "</th><th>" . $AppUI->_("H.Alloc.") . "</th><th>" . $AppUI->_("Task end date") . "</th><th>" . $AppUI->_("Last activity date") . "</th><th>" . $AppUI->_("Done") . "?</th></tr>";
    $hrs = $AppUI->_("hrs");
    // To avoid calling $AppUI each row
    foreach ($tasks as $task) {
        if ($actual_project_id != $task["task_project"]) {
            echo "<tr><td colspan='6'><b>" . $task["project_name"] . "</b></td>";
            $actual_project_id = $task["task_project"];
        }
        if (!$q instanceof DBQuery) {
            //only create if wasn't already present as it may have been created above
            $q = new DBQuery();
        }
        $q->addTable('task_log');
        $q->addQuery('*');
        $q->addWhere('task_log_task = ' . $task['task_id']);
        $q->addOrder('task_log_date desc');
        $q->setLimit('1');
        $task_log = $q->loadHash();
        $done_img = $task["task_percent_complete"] == 100 ? "Yes" : "No";
        echo "<tr><td>&nbsp;&nbsp;&nbsp;" . $task["task_name"] . "</td><td>" . $task["user_username"] . "</td><td>" . $task["task_duration"] * $task["task_duration_type"] . " {$hrs}</td><td>" . $task["task_end_date"] . "</td><td>" . $task_log["task_log_date"] . "</td><td align='center'>{$done_img}</td></tr>";
    }
}
?>
		

    $r->addWhere('file_project = ' . $project_id);
    $project_files = implode(',', $r->loadColumn());
    $r->clear();
    if (!empty($project_tasks)) {
        $project_tasks = " OR (history_table = 'tasks' AND history_item IN ({$project_tasks})) ";
    }
    if (!empty($project_files)) {
        $project_files = " OR (history_table = 'files' AND history_item IN ({$project_files})) ";
    }
    $filter .= ($filter ? ' AND ' : '') . ("((history_table = 'projects' AND history_item = " . $project_id . ')' . $project_tasks . $project_files . ')');
}
if ($filter) {
    $q->addWhere($filter);
}
$q->addOrder('history_date DESC');
$q->setLimit($limit, $offset);
$my_history = $q->loadList();
$history = $my_history;
$count = $q->foundRows();
$pages = (int) ($count / $limit) + 1;
$max_pages = 20;
$first_page = $pages > $max_pages ? max($page - (int) ($max_pages / 2), 1) : 1;
$last_page = $pages > $max_pages ? min($first_page + $max_pages - 1, $pages) : $pages;
?>

<table width="100%" cellspacing="1" cellpadding="0" border="0">
  <tr>
	<td nowrap align="right">
	  <form name="filter" action="?m=history" method="post" >
	  <?php 
echo $AppUI->_('Changes to');

 /**
  * This gets tricky and is there mainly for the compatibility layer
  * for getDeny functions.
  * If we get an ACL ID, and we get allow = false, then the item is
  * actively denied.	Any other combination is a soft-deny (i.e. not
  * strictly allowed, but not actively denied.
  */
 function checkModuleItemDenied($module, $op, $item, $user_id = null)
 {
     if (!$user_id) {
         $user_id = $GLOBALS['AppUI']->user_id;
     }
     $q = new DBQuery();
     $q->addQuery('allow');
     $q->addTable('dotpermissions');
     $q->addWhere("permission='{$op}' AND axo='{$item}' AND user_id='{$user_id}' and section='{$module}'");
     $q->addOrder('priority ASC, acl_id DESC');
     $q->setLimit(1);
     $arr = $q->loadHash();
     if ($arr && !$arr['allow']) {
         return true;
     } else {
         return false;
     }
 }

 public static function getContactByEmail($email)
 {
     $q = new DBQuery();
     $q->addTable('users');
     $q->addQuery('contact_first_name, contact_last_name');
     $q->addJoin('contacts', 'con', 'contact_id = user_contact', 'inner');
     $q->addWhere("contact_email = '{$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;
 }

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