PHP DBQuery::prepare方法代码示例

 function load($oid)
 {
     $q = new DBQuery();
     $q->addQuery('*');
     $q->addTable('risks');
     $q->addWhere('risk_id = ' . $oid);
     return db_loadObject($q->prepare(), $this);
 }

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 load($oid)
 {
     $q = new DBQuery();
     $q->addTable('departments', 'dep');
     $q->addQuery('dep.*');
     $q->addWhere('dep.dept_id = ' . $oid);
     $sql = $q->prepare();
     $q->clear();
     return db_loadObject($sql, $this);
 }

 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 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 _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 _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 getDepartmentArrayList($company_id, $checked_array = array(), $dept_parent = 0, $spaces = 0)
{
    global $AppUI;
    $q = new DBQuery();
    $deptsArray = array();
    $coArray = array();
    $distinctCompanyName = "";
    $q->addTable('departments');
    $q->addQuery('dept_id, dept_name, co.company_name');
    $q->addJoin('companies', 'co', 'departments.dept_company = co.company_id');
    $q->addWhere('dept_parent = ' . $dept_parent);
    $q->addOrder('co.company_name');
    //$q->addWhere('dept_company = ' . $company_id);
    require_once $AppUI->getModuleClass('companies');
    $obj = new CCompany();
    $sql = $q->prepare();
    $depts_list = db_loadHashList($sql, 'dept_id');
    $q->clear();
    foreach ($depts_list as $dept_id => $dept_info) {
        if (mb_strlen($dept_info['dept_name']) > 30) {
            $dept_info['dept_name'] = mb_substr($dept_info['dept_name'], 0, 28) . '...';
        }
        $dept_name = str_repeat(' ', $spaces) . $dept_info['dept_name'];
        $deptsArray[$dept_id] = $dept_name;
        if ($distinctCompanyName != $dept_info['company_name']) {
            $coArray[$dept_id] = $dept_info['company_name'];
            $distinctCompanyName = $dept_info['company_name'];
        }
        $childDeptsNCo = getDepartmentArrayList($company_id, $checked_array, $dept_id, $spaces + 5);
        $childDepts = $childDeptsNCo[0];
        if (!empty($childDepts)) {
            foreach ($childDepts as $childDeptId => $childDeptName) {
                $deptsArray[$childDeptId] = $childDeptName;
            }
        }
    }
    $deptsNCoArray = array();
    array_push($deptsNCoArray, $deptsArray, $coArray);
    return $deptsNCoArray;
}

 /**
  *	Overload of the dpObject::getDeniedRecords
  *	to ensure that the projects owned by denied companies are denied.
  *
  *	@author	handco <handco@sourceforge.net>
  *	@see	dpObject::getAllowedRecords
  */
 function getDeniedRecords($uid)
 {
     $aBuf1 = parent::getDeniedRecords($uid);
     $oCpy = new CCompany();
     // Retrieve which projects are allowed due to the company rules
     $aCpiesAllowed = $oCpy->getAllowedRecords($uid, 'company_id,company_name');
     $q = new DBQuery();
     $q->addTable('projects');
     $q->addQuery('project_id');
     if (count($aCpiesAllowed)) {
         $q->addWhere('NOT (project_company IN (' . implode(',', array_keys($aCpiesAllowed)) . '))');
     }
     $sql = $q->prepare();
     $q->clear();
     $aBuf2 = db_loadColumn($sql);
     return array_merge($aBuf1, $aBuf2);
 }

}
$q->clear();
// Tasks:
$q->addUpdate('task_owner', $user_id);
$q->addTable('tasks');
$q->addWhere('task_owner =  ' . $from_user);
$q->addWhere('task_project' . $project_where);
if (!$q->exec()) {
    $AppUI->setMsg('failed to update task owner', UI_MSG_ERROR);
    return;
}
$q->clear();
$q->addQuery('task_id');
$q->addTable('tasks');
$q->addWhere('task_project' . $project_where);
$task_sql = $q->prepare();
$q->clear();
$q->addUpdate('contact_id', $user_id);
$q->addTable('task_contacts');
$q->addWhere('contact_id = ' . $from_user);
$q->addWhere('task_id IN (' . $task_sql . ')');
if (!$q->exec()) {
    $AppUI->setMsg('failed to update task contacts', UI_MSG_ERROR);
    return;
}
$q->clear();
$q->addUpdate('user_id', $user_id);
$q->addTable('user_tasks');
$q->addWhere('user_id = ' . $from_user);
$q->addWhere('task_id IN (' . $task_sql . ')');
if (!$q->exec()) {

 function getDepartmentDetails()
 {
     $result = array('dept_id' => 0, 'dept_name' => '');
     if (!$this->contact_department) {
         return $result;
     }
     $sql = "select dept_id, dept_name from departments";
     $q = new DBQuery();
     $q->addTable('departments');
     $q->addQuery('dept_id, dept_name');
     if ($this->is_alpha($this->contact_department)) {
         $q->addWhere("dept_name = '" . $this->contact_department . "'");
     } else {
         $q->addWhere("dept_id = '" . $this->contact_department . "'");
     }
     $sql = $q->prepare();
     $q->clear();
     db_loadHash($sql, $result);
     return $result;
 }

$q->addQuery("COUNT(distinct tasks.task_id) AS total_tasks");
$q->addWhere('task_project = ' . $project_id);
$hasTasks = $q->loadResult();
$q->clear();
// load the record data
// GJB: Note that we have to special case duration type 24 and this refers to the hours in a day, NOT 24 hours
if ($hasTasks) {
    $q->addTable('projects');
    $q->addQuery("company_name, CONCAT_WS(', ',contact_last_name,contact_first_name) user_name, projects.*," . " SUM(t1.task_duration * t1.task_percent_complete" . " * IF(t1.task_duration_type = 24, {$working_hours}, t1.task_duration_type))" . " / SUM(t1.task_duration * IF(t1.task_duration_type = 24, {$working_hours}, t1.task_duration_type))" . " AS project_percent_complete");
    $q->addJoin('companies', 'com', 'company_id = project_company');
    $q->addJoin('users', 'u', 'user_id = project_owner');
    $q->addJoin('contacts', 'con', 'contact_id = user_contact');
    $q->addJoin('tasks', 't1', 'projects.project_id = t1.task_project');
    $q->addWhere('project_id = ' . $project_id . ' AND t1.task_id = t1.task_parent');
    $q->addGroup('project_id');
    $sql = $q->prepare();
} else {
    $q->addTable('projects');
    $q->addQuery("company_name, CONCAT_WS(' ',contact_first_name,contact_last_name) user_name, projects.*, " . "(0.0) AS project_percent_complete");
    $q->addJoin('companies', 'com', 'company_id = project_company');
    $q->addJoin('users', 'u', 'user_id = project_owner');
    $q->addJoin('contacts', 'con', 'contact_id = user_contact');
    $q->addWhere('project_id = ' . $project_id);
    $q->addGroup('project_id');
    $sql = $q->prepare();
}
$q->clear();
$obj = null;
if (!db_loadObject($sql, $obj)) {
    $AppUI->setMsg('Project');
    $AppUI->setMsg("invalidID", UI_MSG_ERROR, true);

$allowedProjects = $project->getAllowedSQL($AppUI->user_id);
$working_hours = $dPconfig['daily_working_hours'] ? $dPconfig['daily_working_hours'] : 8;
$q->addQuery('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', 'pr');
$q->leftJoin('tasks', 't1', 'pr.project_id = t1.task_project');
$q->leftJoin('companies', 'c', 'company_id = project_company');
$q->addWhere('t1.task_id = t1.task_parent');
$q->addWhere('project_id=' . $project_id);
if (count($allowedProjects)) {
    $q->addWhere($allowedProjects);
}
$q->addGroup('project_id');
$q->addOrder('project_name');
$psql = $q->prepare();
$q->addQuery('project_id, COUNT(t1.task_id) as total_tasks');
$psql2 = $q->prepare();
$q->clear();
$perms =& $AppUI->acl();
$projects = array();
if ($canViewTasks) {
    $prc = db_exec($psql);
    echo db_error();
    while ($row = db_fetch_assoc($prc)) {
        $projects[$row['project_id']] = $row;
    }
    $prc2 = db_exec($psql2);
    echo db_error();
    while ($row2 = db_fetch_assoc($prc2)) {
        $projects[$row2["project_id"]] = !$projects[$row2["project_id"]] ? array() : $projects[$row2["project_id"]];

 /**
  * Login function
  *
  * A number of things are done in this method to prevent illegal entry:
  * <ul>
  * <li>The username and password are trimmed and escaped to prevent malicious
  *     SQL being executed
  * </ul>
  * The schema previously used the MySQL PASSWORD function for encryption.  This
  * Method has been deprecated in favour of PHP's MD5() function for database independance.
  * The check_legacy_password option is no longer valid
  *
  * Upon a successful username and password match, several fields from the user
  * table are loaded in this object for convenient reference.  The style, locales
  * and preferences are also loaded at this time.
  *
  * @param string The user login name
  * @param string The user password
  * @return boolean True if successful, false if not
  */
 public function login($username, $password)
 {
     require_once W2P_BASE_DIR . '/classes/authenticator.class.php';
     $auth_method = w2PgetConfig('auth_method', 'sql');
     if ($_POST['login'] != 'login' && $_POST['login'] != $this->_('login', UI_OUTPUT_RAW) && $_REQUEST['login'] != $auth_method) {
         die('You have chosen to log in using an unsupported or disabled login method');
     }
     $auth =& getauth($auth_method);
     $username = trim(db_escape($username));
     $password = trim($password);
     if (!$auth->authenticate($username, $password)) {
         return false;
     }
     $user_id = $auth->userId($username);
     $username = $auth->username;
     // Some authentication schemes may collect username in various ways.
     // Now that the password has been checked, see if they are allowed to
     // access the system
     if (!isset($GLOBALS['acl'])) {
         $GLOBALS['acl'] = new w2Pacl();
     }
     if (!$GLOBALS['acl']->checkLogin($user_id)) {
         dprint(__FILE__, __LINE__, 1, 'Permission check failed');
         return false;
     }
     $q = new DBQuery();
     $q->addTable('users');
     $q->addQuery('user_id, contact_first_name as user_first_name, contact_last_name as user_last_name, contact_company as user_company, contact_department as user_department, contact_email as user_email, user_type');
     $q->addJoin('contacts', 'con', 'contact_id = user_contact', 'inner');
     $q->addWhere('user_id = ' . (int) $user_id . ' AND user_username = \'' . $username . '\'');
     $sql = $q->prepare();
     $q->loadObject($this);
     $q->clear();
     dprint(__FILE__, __LINE__, 7, 'Login SQL: ' . $sql);
     if (!$this) {
         dprint(__FILE__, __LINE__, 1, 'Failed to load user information');
         return false;
     }
     // load the user preferences
     $this->loadPrefs($this->user_id);
     $this->setUserLocale();
     $this->checkStyle();
     // Let's see if this user has admin privileges
     if (!getDenyRead('admin')) {
         $this->user_is_admin = 1;
     }
     return true;
 }

 public function notifyContacts($notifyContacts)
 {
     global $AppUI, $w2Pconfig, $locale_char_set;
     if ($notifyContacts == '1') {
         //if no project specified than we will not do anything
         if ($this->file_project != 0) {
             $this->_project = new CProject();
             $this->_project->load($this->file_project);
             $mail = new Mail();
             if ($this->file_task == 0) {
                 //notify all developers
                 $mail->Subject($AppUI->_('Project') . ': ' . $this->_project->project_name . '::' . $this->file_name, $locale_char_set);
             } else {
                 //notify all assigned users
                 $this->_task = new CTask();
                 $this->_task->load($this->file_task);
                 $mail->Subject($AppUI->_('Project') . ': ' . $this->_project->project_name . '::' . $this->_task->task_name . '::' . $this->file_name, $locale_char_set);
             }
             $body = $AppUI->_('Project') . ': ' . $this->_project->project_name;
             $body .= "\n" . $AppUI->_('URL') . ':     ' . W2P_BASE_URL . '/index.php?m=projects&a=view&project_id=' . $this->_project->project_id;
             if (intval($this->_task->task_id) != 0) {
                 $body .= "\n\n" . $AppUI->_('Task') . ':    ' . $this->_task->task_name;
                 $body .= "\n" . $AppUI->_('URL') . ':     ' . W2P_BASE_URL . '/index.php?m=tasks&a=view&task_id=' . $this->_task->task_id;
                 $body .= "\n" . $AppUI->_('Description') . ":\n" . $this->_task->task_description;
                 $q = new DBQuery();
                 $q->addTable('project_contacts', 'pc');
                 $q->addQuery('c.contact_email as contact_email, c.contact_first_name as contact_first_name, c.contact_last_name as contact_last_name');
                 $q->addJoin('contacts', 'c', 'c.contact_id = pc.contact_id');
                 $q->addWhere('pc.project_id = ' . (int) $this->_project->project_id);
                 $sql = '(' . $q->prepare() . ')';
                 $q->clear();
                 $sql .= ' UNION ';
                 $q->addTable('task_contacts', 'tc');
                 $q->addQuery('c.contact_email as contact_email, c.contact_first_name as contact_first_name, c.contact_last_name as contact_last_name');
                 $q->addJoin('contacts', 'c', 'c.contact_id = tc.contact_id');
                 $q->addWhere('tc.task_id = ' . (int) $this->_task->task_id);
                 $sql .= '(' . $q->prepare() . ')';
                 $q->clear();
                 $this->_users = $q->loadList();
             } else {
                 $q = new DBQuery();
                 $q->addTable('project_contacts', 'pc');
                 $q->addQuery('pc.project_id, pc.contact_id');
                 $q->addQuery('c.contact_email as contact_email, c.contact_first_name as contact_first_name, c.contact_last_name as contact_last_name');
                 $q->addJoin('contacts', 'c', 'c.contact_id = pc.contact_id');
                 $q->addWhere('pc.project_id = ' . (int) $this->file_project);
                 $this->_users = $q->loadList();
                 $q->clear();
             }
             $body .= "\n\nFile " . $this->file_name . ' was ' . $this->_message . ' by ' . $AppUI->user_first_name . ' ' . $AppUI->user_last_name;
             if ($this->_message != 'deleted') {
                 $body .= "\n" . $AppUI->_('URL') . ':     ' . W2P_BASE_URL . '/fileviewer.php?file_id=' . $this->file_id;
                 $body .= "\n" . $AppUI->_('Description') . ":\n" . $this->file_description;
             }
             //send mail
             $mail->Body($body, isset($GLOBALS['locale_char_set']) ? $GLOBALS['locale_char_set'] : '');
             foreach ($this->_users as $row) {
                 if ($mail->ValidEmail($row['contact_email'])) {
                     $mail->To($row['contact_email'], true);
                     $mail->Send();
                 }
             }
             return '';
         }
     }
 }