PHP w2p_Database_Query::prepare方法代码示例

$q->addQuery('task_description, task_owner, task_status');
$q->addQuery('usernames.user_username, usernames.user_id');
$q->addQuery('assignees.user_username as assignee_username');
$q->addQuery('count(distinct assignees.user_id) as assignee_count');
$q->addQuery('co.contact_first_name, co.contact_last_name');
$q->addQuery('CONCAT(co.contact_first_name,\' \', co.contact_last_name) AS owner');
$q->addQuery('task_milestone');
$q->addQuery('count(distinct f.file_task) as file_count');
$q->addQuery('tlog.task_log_problem');
$q->addQuery('task_access');
//subquery the parent state
$sq = new w2p_Database_Query();
$sq->addTable('tasks', 'stasks');
$sq->addQuery('COUNT(stasks.task_id)');
$sq->addWhere('stasks.task_id <> tasks.task_id AND stasks.task_parent = tasks.task_id');
$subquery = $sq->prepare();
$sq->clear();
$q->addQuery('(' . $subquery . ') AS task_nr_of_children');
$q->addTable('tasks');
$mods = $AppUI->getActiveModules();
if (!empty($mods['history']) && canView('history')) {
    $q->addQuery('MAX(history_date) as last_update');
    $q->leftJoin('history', 'h', 'history_item = tasks.task_id AND history_table=\'tasks\'');
}
$q->addJoin('projects', 'p', 'p.project_id = task_project', 'inner');
$q->leftJoin('users', 'usernames', 'task_owner = usernames.user_id');
$q->leftJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
$q->leftJoin('users', 'assignees', 'assignees.user_id = ut.user_id');
$q->leftJoin('contacts', 'co', 'co.contact_id = usernames.user_contact');
$q->leftJoin('task_log', 'tlog', 'tlog.task_log_task = tasks.task_id AND tlog.task_log_problem > 0');
$q->leftJoin('files', 'f', 'tasks.task_id = f.file_task');

 public function notifyContacts($notifyContacts)
 {
     global $AppUI, $w2Pconfig, $locale_char_set;
     if ($notifyContacts) {
         //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 w2p_Utilities_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 w2p_Database_Query();
                 $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);
             } else {
                 $q = new w2p_Database_Query();
                 $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();
             $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 '';
         }
     }
 }

 /**
  * 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)
 {
     $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 w2p_Extensions_Permissions();
     }
     if (!$GLOBALS['acl']->checkLogin($user_id)) {
         dprint(__FILE__, __LINE__, 1, 'Permission check failed');
         return false;
     }
     $q = new w2p_Database_Query();
     $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, user_type');
     $q->addJoin('contacts', 'con', 'con.contact_id = user_contact', 'inner');
     /* Begin Hack */
     /*
      * This is a particularly annoying hack but I don't know of a better
      *   way to resolve #457. In v2.0, there was a refactoring to allow for
      *   muliple contact methods which resulted in the contact_email being
      *   removed from the contacts table. If the user is upgrading from
      *   v1.x and they try to log in before applying the database, crash.
      *   Info: http://bugs.web2project.net/view.php?id=457
      * This hack was deprecated in dbVersion 26 for v2.2 in December 2010.
      */
     $qTest = new w2p_Database_Query();
     $qTest->addTable('w2pversion');
     $qTest->addQuery('max(db_version)');
     $dbVersion = $qTest->loadResult();
     if ($dbVersion >= 21 && $dbVersion < 26) {
         $q->leftJoin('contacts_methods', 'cm', 'cm.contact_id = con.contact_id');
         $q->addWhere("cm.method_name = 'email_primary'");
         $q->addQuery('cm.method_value AS user_email');
     } else {
         $q->addQuery('contact_email AS user_email');
     }
     /* End Hack */
     $q->addWhere('user_id = ' . (int) $user_id . ' AND user_username = \'' . $username . '\'');
     $sql = $q->prepare();
     $q->loadObject($this);
     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 (canView('admin')) {
         $this->user_is_admin = 1;
     }
     return true;
 }

/**
 * @return String
 */
function __extract_from_tasks1()
{
    //subquery the parent state
    $sq = new w2p_Database_Query();
    $sq->addTable('tasks', 'stasks');
    $sq->addQuery('COUNT(stasks.task_id)');
    $sq->addWhere('stasks.task_id <> tasks.task_id AND stasks.task_parent = tasks.task_id');
    $subquery = $sq->prepare();
    return $subquery;
}

 public function gc()
 {
     global $AppUI;
     $max = $this->convertTime('max_lifetime');
     $idle = $this->convertTime('idle_time');
     // First pass is to kill any users that are logged in at the time of the session.
     $where = 'UNIX_TIMESTAMP() - UNIX_TIMESTAMP(session_updated) > ' . $idle . ' OR UNIX_TIMESTAMP() - UNIX_TIMESTAMP(session_created) > ' . $max;
     $q = new w2p_Database_Query();
     $q->addTable('user_access_log');
     $q->addUpdate('date_time_out', $q->dbfnNowWithTZ());
     $q2 = new w2p_Database_Query();
     $q2->addTable('sessions');
     $q2->addQuery('session_user');
     $q2->addWhere($where);
     $q->addWhere('user_access_log_id IN ( ' . $q2->prepare() . ' )');
     $q->exec();
     $q->clear();
     $q2->clear();
     // Now we simply delete the expired sessions.
     $q->setDelete('sessions');
     $q->addWhere($where);
     $q->exec();
     $q->clear();
     if (w2PgetConfig('session_gc_scan_queue')) {
         // We need to scan the event queue.  If $AppUI isn't created yet
         // And it isn't likely that it will be, we create it and run the
         // queue scanner.
         if (!isset($AppUI)) {
             $AppUI = new w2p_Core_CAppUI();
             $queue = new w2p_System_EventQueue();
             $queue->scan();
         }
     }
     return true;
 }