PHP DBQuery::addTable方法代码示例

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 install()
    {
        $q = new DBQuery();
        $q->createTable('risks');
        $sql = '(
			`risk_id` int(10) unsigned NOT NULL auto_increment,
			`risk_name` varchar(50) default NULL,
			`risk_description` text,
			`risk_probability` tinyint(3) default 100,
			`risk_status` text default NULL,
			`risk_owner` int(10) default NULL,
			`risk_project` int(10) default NULL,
			`risk_task` int(10) default NULL,
			`risk_impact` int(10) default NULL,
			`risk_duration_type` tinyint(10) default 1,
			`risk_notes` text,
			PRIMARY KEY  (`risk_id`),
			UNIQUE KEY `risk_id` (`risk_id`),
			KEY `risk_id_2` (`risk_id`))
			TYPE=MyISAM';
        $q->createDefinition($sql);
        $q->exec();
        $q->clear();
        $q->createTable('risk_notes');
        $sql = '(
			`risk_note_id` int(11) NOT NULL auto_increment,
			`risk_note_risk` int(11) NOT NULL default \'0\',
			`risk_note_creator` int(11) NOT NULL default \'0\',
			`risk_note_date` datetime NOT NULL default \'0000-00-00 00:00:00\',
			`risk_note_description` text NOT NULL,
			PRIMARY KEY  (`risk_note_id`)
			) TYPE=MyISAM';
        $q->createDefinition($sql);
        $q->exec();
        $q->clear();
        $q->addTable('sysvals');
        $q->addInsert('sysval_key_id', 1);
        $q->addInsert('sysval_title', 'RiskProbability');
        $q->addInsert('sysval_value', "0|Not Specified\n1|Low\n2|Medium\n3|High");
        $q->exec();
        $q->clear();
        $q->addTable('sysvals');
        $q->addInsert('sysval_key_id', 1);
        $q->addInsert('sysval_title', 'RiskStatus');
        $q->addInsert('sysval_value', "0|Not Specified\n1|Open\n2|Closed\n3|Not Applicable");
        $q->exec();
        $q->clear();
        $q->addTable('sysvals');
        $q->addInsert('sysval_key_id', 1);
        $q->addInsert('sysval_title', 'RiskImpact');
        $q->addInsert('sysval_value', "0|Not Specified\n1|Low\n2|Medium\n3|High\n4|Super High");
        $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);
         } 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;
     }
 }

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

/**
 * postsave functions are only called after a succesful save.  They are
 * used to perform database operations after the event.
 */
function resource_postsave()
{
    global $other_resources;
    global $obj;
    $task_id = $obj->task_id;
    dprint(__FILE__, __LINE__, 5, "saving resources, {$other_resources}");
    if (isset($other_resources)) {
        $value = array();
        $reslist = explode(';', $other_resources);
        foreach ($reslist as $res) {
            if ($res) {
                list($resource, $perc) = explode('=', $res);
                $value[] = array($task_id, $resource, $perc);
            }
        }
        // first delete any elements already there, then replace with this
        // list.
        $q = new DBQuery();
        $q->setDelete('resource_tasks');
        $q->addWhere('task_id = ' . $obj->task_id);
        $q->exec();
        $q->clear();
        if (count($value)) {
            foreach ($value as $v) {
                $q->addTable('resource_tasks');
                $q->addInsert('task_id,resource_id,percent_allocated', $v, true);
                $q->exec();
                $q->clear();
            }
        }
    }
}

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

 public function store()
 {
     $this->w2PTrimAll();
     $msg = $this->check();
     if ($msg) {
         return get_class($this) . '::store-check failed - ' . $msg;
     }
     $values = parseFormatSysval($this->sysval_value, $this->sysval_key_id);
     //lets delete the old values
     $q = new DBQuery();
     if ($this->sysval_key_id && $this->sysval_title) {
         $q->setDelete('sysvals');
         $q->addWhere('sysval_key_id = ' . (int) $this->sysval_key_id);
         $q->addWhere('sysval_title = \'' . $this->sysval_title . '\'');
         if (!$q->exec()) {
             $q->clear();
             return get_class($this) . '::store failed: ' . db_error();
         }
     }
     foreach ($values as $key => $value) {
         $q->addTable('sysvals');
         $q->addInsert('sysval_key_id', $this->sysval_key_id);
         $q->addInsert('sysval_title', $this->sysval_title);
         $q->addInsert('sysval_value_id', $key);
         $q->addInsert('sysval_value', $value);
         if (!$q->exec()) {
             $q->clear();
             return get_class($this) . '::store failed: ' . db_error();
         }
         $q->clear();
     }
     return null;
 }

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

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 insertCompany($company_name)
{
    $q = new DBQuery();
    $q->addTable("companies");
    $q->addInsert('company_name', $company_name);
    db_exec($q->prepareInsert());
    return db_insert_id();
}

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

 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 _buildQuery()
 {
     $q = new DBQuery();
     $q->addTable($this->table);
     $q->addQuery('*');
     $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 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();
 }