PHP DBQuery::addQuery方法代码示例

 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 _buildQuery()
 {
     $q = new DBQuery();
     $q->addTable($this->table);
     $q->addQuery('ticket');
     $q->addQuery('subject');
     $sql = '';
     foreach ($this->search_fields as $field) {
         $sql .= " {$field} LIKE '%{$this->keyword}%' or ";
     }
     $sql = substr($sql, 0, -4);
     $q->addWhere($sql);
     return $q->prepare(true);
 }

 function _buildQuery()
 {
     $q = new DBQuery();
     $q->addTable($this->table);
     $q->addQuery('task_id');
     $q->addQuery('task_name');
     $q->addWhere('task_project != 0');
     $sql = '';
     foreach ($this->search_fields as $field) {
         $sql .= " {$field} LIKE '%{$this->keyword}%' or ";
     }
     $sql = substr($sql, 0, -4);
     $q->addWhere("({$sql})");
     return $q->prepare(true);
 }

function setComplete($id)
{
    global $AppUI;
    $task = new CTask();
    if ($task->load($id)) {
        $q = new DBQuery();
        $q->addTable('user_tasks');
        $q->addQuery('user_id');
        $q->addWhere('task_id = ' . $id);
        $q->addWhere('user_id = ' . $AppUI->user_id);
        $r = $q->loadResult();
        if ($r != $AppUI->user_id) {
            $p = new CProject($task->task_project);
            if (!$p->project_id || $p->getManager() != $AppUI->user_id) {
                return 'Error';
            }
        }
        $q->addTable('tasks');
        $q->addUpdate('task_percent_complete', '100');
        $q->addWhere('task_id = ' . $id);
        $q->exec();
        return 'OK';
    }
    return 'Error';
}

function dPsessionWrite($id, $data)
{
    global $AppUI;
    $q = new DBQuery();
    $q->addQuery('count(*) as row_count');
    $q->addTable('sessions');
    $q->addWhere("session_id = '{$id}'");
    if (($qid =& $q->exec()) && (@$qid->fields['row_count'] > 0 || @$qid->fields[0] > 0)) {
        dprint(__FILE__, __LINE__, 11, "Updating session {$id}");
        $q->query = null;
        $q->addUpdate('session_data', $data);
        if (isset($AppUI)) {
            $q->addUpdate('session_user', $AppUI->last_insert_id);
        }
    } else {
        dprint(__FILE__, __LINE__, 11, "Creating new session {$id}");
        $q->query = null;
        $q->where = null;
        $q->addInsert('session_id', $id);
        $q->addInsert('session_data', $data);
        $q->addInsert('session_created', date('Y-m-d H:i:s'));
    }
    $q->exec();
    $q->clear();
    return true;
}

 function store()
 {
     $msg = $this->check();
     if ($msg) {
         return get_class($this) . "::store-check failed";
     }
     $q = new DBQuery();
     if ($this->user_id) {
         // save the old password
         $perm_func = "updateLogin";
         $q->addTable('users');
         $q->addQuery('user_password');
         $q->addWhere("user_id = {$this->user_id}");
         $pwd = $q->loadResult();
         if ($pwd != $this->user_password) {
             $this->user_password = md5($this->user_password);
             addHistory($this->_tbl, $this->user_id, 'password changed', 'Password changed from IP ' . $_SERVER['REMOTE_ADDR']);
         } else {
             $this->user_password = null;
         }
         $ret = db_updateObject('users', $this, 'user_id', false);
     } else {
         $perm_func = "addLogin";
         $this->user_password = md5($this->user_password);
         $ret = db_insertObject('users', $this, 'user_id');
     }
     if (!$ret) {
         return get_class($this) . "::store failed <br />" . db_error();
     } else {
         $acl =& $GLOBALS['AppUI']->acl();
         $acl->{$perm_func}($this->user_id, $this->user_username);
         return NULL;
     }
 }

 function delete()
 {
     $q = new DBQuery();
     $q->addTable('departments', 'dep');
     $q->addQuery('dep.*');
     $q->addWhere('dep.dept_parent = ' . $this->dept_id);
     $res = $q->exec();
     if (db_num_rows($res)) {
         $q->clear();
         return "deptWithSub";
     }
     $q->clear();
     $q->addTable('projects', 'p');
     $q->addQuery('p.*');
     $q->addWhere('p.project_department = ' . $this->dept_id);
     $res = $q->exec();
     if (db_num_rows($res)) {
         $q->clear();
         return "deptWithProject";
     }
     // $sql = "DELETE FROM departments WHERE dept_id = $this->dept_id";
     $q->clear();
     $q->addQuery('*');
     $q->setDelete('departments');
     $q->addWhere('dept_id = ' . $this->dept_id);
     if (!$q->exec()) {
         $result = db_error();
     } else {
         $result = NULL;
     }
     $q->clear();
     return $result;
 }

function getCompanyDepartment($company_id)
{
    global $AppUI;
    $q = new DBQuery();
    $q->addTable('companies');
    $q->addQuery('company_name');
    $q->addWhere('company_id = ' . $company_id);
    $company_name = $q->loadResult();
    if (!$company_name) {
        $AppUI->setMsg('Company not found', UI_MSG_ERROR);
        echo $AppUI->getMsg();
        return;
    }
    $q->addTable('departments');
    $q->addQuery('dept_id,dept_parent,dept_name');
    $q->addWhere('dept_company = ' . $company_id);
    $q->addOrder('dept_parent, dept_name');
    $depts = $q->loadHashList('dept_id');
    if (!$depts) {
        $AppUI->setMsg('Company [ ' . $company_name . ' ] has no department', UI_MSG_WARNING);
        echo $AppUI->getMsg();
        return;
    }
    include 'modules/public/resources.info.php';
}

function checkCompanyId($company_id)
{
    $q = new DBQuery();
    $q->addTable('companies');
    $q->addQuery('count(*)');
    $q->addWhere("company_id = '{$company_id}'");
    return db_loadResult($q->prepare());
}

 function _buildQuery()
 {
     $q = new DBQuery();
     $q->addTable($this->table);
     $q->addQuery('company_id');
     $q->addQuery('company_name');
     $sql = array();
     foreach ($this->search_fields as $field) {
         $sql[] = "{$field} LIKE '%{$this->keyword}%'";
     }
     if (count($sql)) {
         $q->addWhere(implode(' OR ', $sql));
     }
     $result = $q->prepare();
     $q->clear();
     return $result;
 }

 function testUpdateBD()
 {
     $q = new DBQuery();
     $q->addTable('eap');
     $q->addQuery("id,nome,linha,coluna");
     $q->addUpdate(nome, 'Dot Project');
     $q->addWhere("id = 1");
     $q->prepareUpdate();
     $this->assertEqual($q->exec(), true);
     $q->clear();
 }

 function store()
 {
     $msg = $this->check();
     if ($msg) {
         return get_class($this) . "::store-check failed";
     }
     $q = new DBQuery();
     if ($this->user_id) {
         // save the old password
         $perm_func = "updateLogin";
         $q->addTable('users');
         $q->addQuery('user_password');
         $q->addWhere("user_id = {$this->user_id}");
         $pwd = $q->loadResult();
         if ($pwd != $this->user_password) {
             $this->user_password = md5($this->user_password);
         } else {
             $this->user_password = null;
         }
         $ret = db_updateObject('users', $this, 'user_id', false);
     } else {
         $perm_func = "addLogin";
         $this->user_password = md5($this->user_password);
         $ret = db_insertObject('users', $this, 'user_id');
     }
     if (!$ret) {
         return get_class($this) . "::store failed <br />" . db_error();
     } else {
         $acl =& $GLOBALS['AppUI']->acl();
         $acl->{$perm_func}($this->user_id, $this->user_username);
         //Insert Default Preferences
         //Lets check if the user has allready default users preferences set, if not insert the default ones
         $q->addTable('user_preferences', 'upr');
         $q->addWhere("upr.pref_user = {$this->user_id}");
         $uprefs = $q->loadList();
         $q->clear();
         if (!count($uprefs) && $this->user_id > 0) {
             //Lets get the default users preferences
             $q->addTable('user_preferences', 'dup');
             $q->addWhere("dup.pref_user = 0");
             $dprefs = $q->loadList();
             $q->clear();
             foreach ($dprefs as $dprefskey => $dprefsvalue) {
                 $q->addTable('user_preferences', 'up');
                 $q->addInsert('pref_user', $this->user_id);
                 $q->addInsert('pref_name', $dprefsvalue['pref_name']);
                 $q->addInsert('pref_value', $dprefsvalue['pref_value']);
                 $q->exec();
                 $q->clear();
             }
         }
         return NULL;
     }
 }

 function listCompaniesByType($type)
 {
     global $AppUI;
     $q = new DBQuery();
     $q->addQuery('company_id, company_name');
     $q->addTable('companies');
     foreach ($type as $t) {
         $q->addWhere('company_type =' . $t);
     }
     $this->setAllowedSQL($AppUI->user_id, $q);
     $q->addOrder('company_name');
     return $q->loadHashList();
 }

function getFolderSelectList()
{
    global $AppUI;
    $folders = array(0 => '');
    $q = new DBQuery();
    $q->addTable('file_folders');
    $q->addQuery('file_folder_id, file_folder_name, file_folder_parent');
    $q->addOrder('file_folder_name');
    $sql = $q->prepare();
    //	$sql = "SELECT file_folder_id, file_folder_name, file_folder_parent FROM file_folders";
    $vfolders = arrayMerge(array('0' => array(0, $AppUI->_('Root'), -1)), db_loadHashList($sql, 'file_folder_id'));
    $folders = array_filter($vfolders, "check_perm");
    return $folders;
}

 function _fetchPreviousData()
 {
     $q = new DBQuery();
     $q->addTable($this->table_name);
     $q->addQuery($this->field_name);
     $q->addWhere("{$this->id_field_name} = {$this->row_id}");
     $previous_data = $q->loadResult();
     if ($previous_data != "") {
         $previous_data = unserialize($previous_data);
         $previous_data = !is_array($previous_data) ? array() : $previous_data;
     } else {
         $previous_data = array();
     }
     $this->previous_data = $previous_data;
 }