PHP db_execute_all函数代码示例

 /**
  * chack if $content is spam
  *
  * @param string $content
  */
 function isSpam(&$content)
 {
     $tokens = Bayesian::getTokens($content);
     // if there is no tokens, email is most probably not spam
     if (!is_foreachable($tokens)) {
         return false;
     }
     // if
     $table_name = TABLE_PREFIX . BAYESIAN_TOKENS_TABLENAME;
     $total_count_ham = db_execute_one("SELECT count(*) as count FROM `{$table_name}` WHERE `type`='ham'");
     $total_count_ham = array_var($total_count_ham, 'count', 0);
     $total_count_spam = db_execute_one("SELECT count(*) as count FROM `{$table_name}` WHERE `type`='spam'");
     $total_count_spam = array_var($total_count_spam, 'count', 0);
     // there is no learning data
     if (!$total_count_ham || !$total_count_spam) {
     }
     // if
     $database_tokens = db_execute_all("SELECT * FROM `{$table_name}` WHERE `token` IN (" . implode(',', Bayesian::escapeTokens($tokens)) . ")");
     $probabilities = Bayesian::getIndividualProbabilities($tokens, $database_tokens, $total_count_ham, $total_count_spam);
     return Bayesian::getCombinedProbability($probabilities);
     // if we don't have good sample rate, we need to mark all $content as spam so we can learn something
     if ($count_ham + $count_spam < BAYESIAN_INITIAL_THRESHOLD && false) {
         return true;
     }
     // if
 }

/**
 * Render object assignees list
 *
 * @param array $params
 * @param Smarty $smarty
 * @return string
 */
function smarty_function_object_owner($params, &$smarty)
{
    $object = array_var($params, 'object');
    if (!instance_of($object, 'ProjectObject')) {
        return new InvalidParamError('object', $object, '$object is expected to be an instance of ProjectObject class', true);
    }
    // if
    $users_table = TABLE_PREFIX . 'users';
    $assignments_table = TABLE_PREFIX . 'assignments';
    $rows = db_execute_all("SELECT {$assignments_table}.is_owner AS is_assignment_owner, {$users_table}.id AS user_id, {$users_table}.company_id, {$users_table}.first_name, {$users_table}.last_name, {$users_table}.email FROM {$users_table}, {$assignments_table} WHERE {$users_table}.id = {$assignments_table}.user_id AND {$assignments_table}.object_id = ? and {$assignments_table}.is_owner='1' ORDER BY {$assignments_table}.is_owner DESC", $object->getId());
    if (is_foreachable($rows)) {
        $owner = null;
        foreach ($rows as $row) {
            if (empty($row['first_name']) && empty($row['last_name'])) {
                $user_link = '<a href="' . assemble_url('people_company', array('company_id' => $row['company_id'])) . '#user' . $row['user_id'] . '">' . clean($row['email']) . '</a>';
            } else {
                $user_link = '<a href="' . assemble_url('people_company', array('company_id' => $row['company_id'])) . '#user' . $row['user_id'] . '">' . clean($row['first_name']) . '</a>';
            }
            // if
            $owner .= $user_link . '&nbsp;';
        }
        // foreach
    }
    // if
    if (empty($owner)) {
        $owner = '--';
    }
    // if
    return $owner;
}

 /**
  * Return associative array with config option values by name and given 
  * company
  *
  * @param Company $company
  * @return array
  */
 function getValues($names, $company)
 {
     $result = array();
     // lets get option definition instances
     $options = ConfigOptions::findByNames($names, COMPANY_CONFIG_OPTION);
     if (is_foreachable($options)) {
         // Now we need all company specific values we can get
         $values = db_execute_all('SELECT name, value FROM ' . TABLE_PREFIX . 'company_config_options WHERE name IN (?) AND company_id = ?', $names, $company->getId());
         $foreachable = is_foreachable($values);
         // Populate result
         foreach ($options as $name => $option) {
             if ($foreachable) {
                 foreach ($values as $record) {
                     if ($record['name'] == $name) {
                         $result[$name] = trim($record['value']) != '' ? unserialize($record['value']) : null;
                         break;
                     }
                     // if
                 }
                 // foreach
             }
             // if
             if (!isset($result[$name])) {
                 $result[$name] = $option->getValue();
             }
             // if
         }
         // foreach
     }
     // if
     return $result;
 }

 /**
  * Execute report
  *
  * @param User $user
  * @param TimeReport $report
  * @param Project $project
  * @return array
  */
 function executeReport($user, $report, $project = null)
 {
     $conditions = $report->prepareConditions($user, $project);
     if (empty($conditions)) {
         return null;
     }
     // if
     if ($report->getSumByUser()) {
         $rows = db_execute_all('SELECT SUM(float_field_1) AS total_time, integer_field_1 AS user_id FROM ' . TABLE_PREFIX . 'project_objects WHERE ' . $conditions . ' GROUP BY integer_field_1');
         if (is_foreachable($rows)) {
             $result = array();
             foreach ($rows as $row) {
                 $user = Users::findById($row['user_id']);
                 if (instance_of($user, 'User')) {
                     $result[] = array('user' => $user, 'total_time' => float_format($row['total_time'], 2));
                 }
                 // if
             }
             // foreach
             return $result;
         } else {
             return null;
         }
         // if
     } else {
         return TimeRecords::findBySQL('SELECT * FROM ' . TABLE_PREFIX . 'project_objects WHERE ' . $conditions . ' ORDER BY date_field_1');
     }
     // if
 }

 /**
  * Return array of ticket changes
  *
  * @param Ticket $ticket
  * @param integer $count
  * @return array
  */
 function findByTicket($ticket, $count = null)
 {
     $count = (int) $count;
     if ($count < 1) {
         $rows = db_execute_all('SELECT * FROM ' . TABLE_PREFIX . 'ticket_changes WHERE ticket_id = ? ORDER BY version DESC', $ticket->getId());
     } else {
         $rows = db_execute_all('SELECT * FROM ' . TABLE_PREFIX . "ticket_changes WHERE ticket_id = ? ORDER BY version DESC LIMIT 0, {$count}", $ticket->getId());
     }
     // if
     if (is_foreachable($rows)) {
         $changes = array();
         foreach ($rows as $row) {
             $change = new TicketChange();
             $change->setTicketId($ticket->getId());
             $change->setVersion($row['version']);
             $change->changes = unserialize($row['changes']);
             $change->created_on = $row['created_on'] ? new DateTimeValue($row['created_on']) : null;
             $change->created_by_id = (int) $row['created_by_id'];
             $change->created_by_name = $row['created_by_name'];
             $change->created_by_email = $row['created_by_email'];
             $changes[$row['version']] = $change;
         }
         // foreach
         return $changes;
     } else {
         return null;
     }
     // if
 }

 /**
  * Return object of a specific class by SQL
  *
  * @param string $sql
  * @param array $arguments
  * @param boolean $one
  * @param string $table_name
  * @param string $item_class
  * @return array
  */
 function findBySQL($sql, $arguments = null, $one = false, $table_name = null, $item_class = null)
 {
     if ($arguments !== null) {
         $sql = db_prepare_string($sql, $arguments);
     }
     // if
     $rows = db_execute_all($sql);
     if (is_error($rows)) {
         return $rows;
     }
     // if
     if (!is_array($rows) || !count($rows)) {
         return null;
     }
     // if
     if ($one) {
         $item = new $item_class();
         $item->loadFromRow($rows[0], true);
         return $item;
     } else {
         $items = array();
         foreach ($rows as $row) {
             $item = new $item_class();
             $item->loadFromRow($row, true);
             $items[] = $item;
         }
         // foreach
         return count($items) ? $items : null;
     }
     // if
 }

/**
 * Render select project helper
 * 
 * Parametars:
 * 
 *  - value - Id of selected project
 *  - user - Limit only to projects that can be viewed by User
 *  - optional
 * 
 * @param void
 * @return null
 */
function smarty_function_select_project($params, &$smarty)
{
    $user = array_var($params, 'user', null, true);
    if (!instance_of($user, 'User')) {
        return new InvalidParamError('user', $user, '$user is expected to be an instance of User class', true);
    }
    // if
    $show_all = array_var($params, 'show_all', false) && $user->isProjectManager();
    $value = array_var($params, 'value', null, true);
    $projects_table = TABLE_PREFIX . 'projects';
    $project_users_table = TABLE_PREFIX . 'project_users';
    if ($show_all) {
        $projects = db_execute_all("SELECT {$projects_table}.id, {$projects_table}.name, {$projects_table}.status FROM {$projects_table} WHERE {$projects_table}.type = ? ORDER BY {$projects_table}.name", PROJECT_TYPE_NORMAL);
    } else {
        $projects = db_execute_all("SELECT {$projects_table}.id, {$projects_table}.name, {$projects_table}.status FROM {$projects_table}, {$project_users_table} WHERE {$project_users_table}.user_id = ? AND {$project_users_table}.project_id = {$projects_table}.id AND {$projects_table}.type = ? ORDER BY {$projects_table}.name", $user->getId(), PROJECT_TYPE_NORMAL);
    }
    // if
    $exclude = (array) array_var($params, 'exclude', array(), true);
    $active_options = array();
    $archived_options = array();
    if (is_foreachable($projects)) {
        foreach ($projects as $k => $project) {
            if (in_array($project['id'], $exclude)) {
                continue;
            }
            // if
            $option_attributes = $project['id'] == $value ? array('selected' => true) : null;
            if ($project['status'] == PROJECT_STATUS_ACTIVE) {
                $active_options[] = option_tag($project['name'], $project['id'], $option_attributes);
            } else {
                $archived_options[] = option_tag($project['name'], $project['id'], $option_attributes);
            }
            // if
        }
        // if
    }
    // if
    $optional = array_var($params, 'optional', false, true);
    $options = array();
    if ($optional) {
        $options[] = option_tag(lang(array_var($params, 'optional_caption', '-- Select Project --')), '');
        $options[] = option_tag('', '');
    }
    // if
    if (is_foreachable($active_options)) {
        $options[] = option_group_tag(lang('Active'), $active_options);
    }
    // if
    if (is_foreachable($active_options) && is_foreachable($archived_options)) {
        $options[] = option_tag('', '');
    }
    // if
    if (is_foreachable($archived_options)) {
        $options[] = option_group_tag(lang('Archive'), $archived_options);
    }
    // if
    return select_box($options, $params);
}

 /**
  * Render RSS feed for a spcific filter
  *
  * @param void
  * @return null
  */
 function rss()
 {
     if ($this->active_filter->isNew()) {
         $this->httpError(HTTP_ERR_NOT_FOUND);
     }
     // if
     if (!$this->active_filter->canUse($this->logged_user)) {
         $this->httpError(HTTP_ERR_FORBIDDEN);
     }
     // if
     require_once ANGIE_PATH . '/classes/feed/init.php';
     $feed = new Feed($this->owner_company->getName() . ' - ' . $this->active_filter->getName(), $this->active_filter->getUrl());
     $assignments = AssignmentFilters::executeFilter($this->logged_user, $this->active_filter, false);
     if (is_foreachable($assignments)) {
         $project_ids = array();
         foreach ($assignments as $assignment) {
             if (!in_array($assignment->getProjectId(), $project_ids)) {
                 $project_ids[] = $assignment->getProjectId();
             }
             // if
         }
         // foreach
         $projects = array();
         if (is_foreachable($project_ids)) {
             $rows = db_execute_all('SELECT id, name FROM ' . TABLE_PREFIX . 'projects WHERE id IN (?)', $project_ids);
             if (is_foreachable($rows)) {
                 foreach ($rows as $row) {
                     $projects[$row['id']] = $row['name'];
                 }
                 // foreach
             }
             // if
         }
         // if
         foreach ($assignments as $assignment) {
             $title = '[' . array_var($projects, $assignment->getProjectId()) . '] ' . $assignment->getVerboseType() . ' "' . $assignment->getName() . '"';
             $this->smarty->assign('_assignment', $assignment);
             $body = $this->smarty->fetch(get_template_path('_feed_body', 'assignment_filters', RESOURCES_MODULE));
             $item = new FeedItem($title, $assignment->getViewUrl(), $body, $assignment->getCreatedOn());
             $item->setId($assignment->getViewUrl());
             $feed->addItem($item);
         }
         // foreach
     }
     // if
     print render_rss_feed($feed);
     die;
 }

/**
 * Handle on_project_object_moved event
 *
 * @param ProjectObject $object
 * @param Project $source
 * @param Project $destination
 * @return null
 */
function resources_handle_on_project_object_moved(&$object, &$source, &$destination)
{
    if ($object->can_have_subscribers) {
        $subscribers = $object->getSubscribers();
        if (is_foreachable($subscribers)) {
            foreach ($subscribers as $subscriber) {
                if (!$subscriber->isProjectMember($destination)) {
                    Subscriptions::unsubscribe($subscriber, $object);
                }
                // if
            }
            // foreach
        }
        // if
    }
    // if
    $object_ids = array();
    // Relations with milestones are carried out via milestone_id field
    if (instance_of($object, 'Milestone')) {
        db_execute('UPDATE ' . TABLE_PREFIX . 'project_objects SET milestone_id = 0 WHERE milestone_id = ?', $object->getId());
    }
    // if
    $rows = db_execute_all('SELECT id FROM ' . TABLE_PREFIX . 'project_objects WHERE type IN (?) AND parent_id = ?', array('task', 'comment', 'attachment', 'timerecord'), $object->getId());
    if (is_foreachable($rows)) {
        foreach ($rows as $row) {
            $object_ids[] = (int) $row['id'];
        }
        // foreach
        // Sub-objects (attachments for comments, time records for tasks, tasks for tickets)
        $rows = db_execute_all('SELECT id FROM ' . TABLE_PREFIX . 'project_objects WHERE parent_id IN (?)', $object_ids);
        if (is_foreachable($rows)) {
            foreach ($rows as $row) {
                $object_ids[] = (int) $row['id'];
            }
            // foreach
        }
        // if
        // Update objects and activity logs
        db_execute('UPDATE ' . TABLE_PREFIX . 'project_objects SET project_id = ? WHERE id IN (?)', $destination->getId(), $object_ids);
        db_execute('UPDATE ' . TABLE_PREFIX . 'activity_logs SET project_id = ? WHERE object_id IN (?)', $destination->getId(), $object_ids);
        // Clear cache
        cache_remove_by_pattern(TABLE_PREFIX . 'activity_logs_id_*');
        cache_remove_by_pattern(TABLE_PREFIX . 'project_objects_id_*');
    }
    // if
}

 /**
  * Get all commits related to a project object
  *
  * @param integer $object_id
  * @return array
  */
 function findCommitsByObject($object)
 {
     $parent_object_ids = array();
     $parent_object_ids[] = $object->getId();
     /**
      * Try to find commits related to children objects
      */
     $task_ids = array();
     if (instance_of($object, 'Ticket')) {
         $tasks = db_execute_all("SELECT id FROM " . TABLE_PREFIX . "project_objects WHERE parent_id = " . $object->getid() . " AND `type` = 'Task'");
         if (is_foreachable($tasks)) {
             foreach ($tasks as $task) {
                 $task_ids[] = $task['id'];
             }
             // foreach
         }
         // if
     }
     // if
     $objects_ids = array_merge($parent_object_ids, $task_ids);
     $commit_project_objects = CommitProjectObjects::find(array('conditions' => array("object_id IN(" . implode(',', $objects_ids) . ")"), 'order' => 'repository_id ASC, revision DESC'));
     if (is_foreachable($commit_project_objects)) {
         $commits = array();
         $revisions = array();
         foreach ($commit_project_objects as $commit_project_object) {
             if (!in_array($commit_project_object->getRevision(), $revisions)) {
                 // prevent commits from showing more than once
                 $revisions[] = $commit_project_object->getRevision();
                 $commit = Commits::findByRevision($commit_project_object->getRevision(), Repositories::findById($commit_project_object->getRepositoryId()));
                 if (instance_of($commit, 'Commit')) {
                     $commit->repository = Repositories::findById($commit->getProjectId());
                     $commits[] = $commit;
                 }
                 // if
             }
             // if
         }
         // foreach
         return group_by_date($commits);
     } else {
         return false;
     }
     // if
 }

/**
 * Render object assignees list
 *
 * @param array $params
 * @param Smarty $smarty
 * @return string
 */
function smarty_function_object_owner_selector($params, &$smarty)
{
    $object = array_var($params, 'object');
    if (!instance_of($object, 'ProjectObject')) {
        return new InvalidParamError('object', $object, '$object is expected to be an instance of ProjectObject class', true);
    }
    // if
    $language = array_var($params, 'language', $smarty->get_template_vars('current_language'));
    // maybe we need to print this in a specific language?
    $users_table = TABLE_PREFIX . 'users';
    $assignments_table = TABLE_PREFIX . 'assignments';
    $owner_exists = false;
    $rows = db_execute_all("SELECT {$assignments_table}.is_owner AS is_assignment_owner, {$users_table}.id AS user_id, {$users_table}.company_id, {$users_table}.first_name, {$users_table}.last_name, {$users_table}.email FROM {$users_table}, {$assignments_table} WHERE {$users_table}.id = {$assignments_table}.user_id AND {$assignments_table}.object_id = ? ORDER BY {$assignments_table}.is_owner DESC", $object->getId());
    if (is_foreachable($rows)) {
        $owner = null;
        $other_assignees = array();
        $users_dropdown_for_tickets = '';
        foreach ($rows as $row) {
            if ($row['is_assignment_owner']) {
                $owner_exists = true;
            }
            if (empty($users_dropdown_for_tickets)) {
                $users_dropdown_for_tickets = '<select onchange="modify_responsible_status(this);">';
            }
            $users_dropdown_for_tickets .= '<option value="' . $row['user_id'] . '"' . ($row['is_assignment_owner'] ? ' selected ' : '') . '>';
            if (empty($row['first_name']) && empty($row['last_name'])) {
                $users_dropdown_for_tickets .= clean($row['email']);
            } else {
                $users_dropdown_for_tickets .= clean($row['first_name'] . ' ' . $row['last_name']);
            }
            $users_dropdown_for_tickets .= '</option>';
        }
        // foreach
    }
    if ($owner_exists) {
        $users_dropdown_for_tickets .= '</select>';
        $owner = $users_dropdown_for_tickets;
    } else {
        $owner = '--';
    }
    return $owner;
}

 /**
  * Return ID-s of projects pinned by a given user
  *
  * @param User $user
  * @return array
  */
 function findProjectIdsByUser($user, $use_cache = true)
 {
     if ($use_cache) {
         $cache_value = cache_get('user_pinned_projects_' . $user->getId());
         return is_array($cache_value) ? $cache_value : PinnedProjects::rebuildUserCache($user);
     } else {
         $projects_table = TABLE_PREFIX . 'projects';
         $pinned_projects_table = TABLE_PREFIX . 'pinned_projects';
         $ids = array();
         $rows = db_execute_all("SELECT {$projects_table}.id FROM {$projects_table}, {$pinned_projects_table} WHERE {$projects_table}.id = {$pinned_projects_table}.project_id AND {$pinned_projects_table}.user_id = ? AND {$projects_table}.type = ? ORDER BY {$projects_table}.name", $user->getId(), PROJECT_TYPE_NORMAL);
         if (is_foreachable($rows)) {
             foreach ($rows as $row) {
                 $ids[] = (int) $row['id'];
             }
             // foreach
         }
         // if
         return $ids;
     }
     // if
 }

 /**
  * Update time records
  * 
  * Move boolean_field_1 and boolean_field_2 into integer_field_1
  * 
  * - boolean_field_1 - is_billable
  * - boolean_field_2 - is_billed
  * - integer_field_2 - billable status
  *
  * @param void
  * @return boolean
  */
 function updateTimeRecords()
 {
     $rows = db_execute_all('SELECT id, boolean_field_1, boolean_field_2, integer_field_2 FROM ' . TABLE_PREFIX . 'project_objects WHERE type = ?', 'TimeRecord');
     if (is_foreachable($rows)) {
         foreach ($rows as $row) {
             if ($row['integer_field_2'] > 0) {
                 continue;
                 // in case we are running upgrade after some of the time records that use the new system are posted!
             }
             // if
             if ($row['boolean_field_1']) {
                 $new_status = $row['boolean_field_2'] ? 3 : 1;
             } else {
                 $new_status = 0;
             }
             // if
             db_execute('UPDATE ' . TABLE_PREFIX . 'project_objects SET integer_field_2 = ?, boolean_field_1 = NULL, boolean_field_2 = NULL WHERE id = ?', $new_status, $row['id']);
         }
         // foreach
     }
     // if
     return true;
 }

 /**
  * Copy project items into a destination project
  *
  * @param Project $to
  * @return null
  */
 function copyItems(&$to)
 {
     // Prepare time diff
     $source_starts_on = $this->getStartsOn();
     if (!instance_of($source_starts_on, 'DateValue')) {
         $source_starts_on = $this->getCreatedOn();
     }
     // if
     $target_starts_on = $to->getStartsOn();
     if (!instance_of($target_starts_on, 'DateValue')) {
         $target_starts_on = $to->getCreatedOn();
     }
     // if
     $diff = $target_starts_on->getTimestamp() - $source_starts_on->getTimestamp();
     // Migrate project users
     $project_users = ProjectUsers::findByProject($this);
     if (is_foreachable($project_users)) {
         foreach ($project_users as $project_user) {
             if ($to->getLeaderId() != $project_user->getUserId()) {
                 $user = $project_user->getUser();
                 if (instance_of($user, 'User')) {
                     $to->addUser($user, $project_user->getRole(), $project_user->getPermissions());
                 }
                 // if
             }
             // if
         }
         // foreach
     }
     // if
     // We need to move milestones in order to get milestones map
     $milestones_map = null;
     $milestones = Milestones::findAllByProject($this, VISIBILITY_PRIVATE);
     if (is_foreachable($milestones)) {
         $milestones_map = array();
         foreach ($milestones as $milestone) {
             $copied_milestone = $milestone->copyToProject($to);
             if (instance_of($copied_milestone, 'Milestone')) {
                 $copied_milestone->advance($diff, true);
                 $milestones_map[$milestone->getId()] = $copied_milestone;
             }
             // if
         }
         // foreach
     }
     // if
     // Now move categories
     $categories_map = null;
     $categories = Categories::findByProject($this);
     if (is_foreachable($categories)) {
         foreach ($categories as $category) {
             $copied_category = $category->copyToProject($to, null, null, false);
             if (instance_of($copied_category, 'Category')) {
                 $categories_map[$category->getId()] = $copied_category;
             }
             // if
         }
         // foreach
     }
     // if
     // Let the modules to their thing
     event_trigger('on_copy_project_items', array(&$this, &$to, $milestones_map, $categories_map));
     // Now, lets update due dates
     $completable_types = get_completable_project_object_types();
     if (is_foreachable($completable_types)) {
         foreach ($completable_types as $k => $type) {
             if (strtolower($type) == 'milestone') {
                 unset($completable_types[$k]);
             }
             // if
         }
         // foreach
         if (count($completable_types) > 0) {
             $rows = db_execute_all('SELECT id, due_on FROM ' . TABLE_PREFIX . 'project_objects WHERE project_id = ? AND type IN (?) AND due_on IS NOT NULL', $to->getId(), $completable_types);
             if (is_foreachable($rows)) {
                 foreach ($rows as $row) {
                     $id = (int) $row['id'];
                     $new_date = date(DATE_MYSQL, strtotime($row['due_on']) + $diff);
                     db_execute('UPDATE ' . TABLE_PREFIX . 'project_objects SET due_on = ? WHERE id = ?', $new_date, $id);
                     cache_remove("acx_project_objects_id_{$id}");
                 }
                 // foreach
             }
             // if
         }
         // if
     }
     // if
     // Refresh tasks count, just in case...
     $to->refreshTasksCount();
 }

 /**
  * Return ID-s of viewed objects for a given user
  *
  * @param User $user
  * @return array
  */
 function findViewedObjectIds($user)
 {
     $cache_id = 'object_viewed_by_' . $user->getId();
     $cached_value = array();
     $rows = db_execute_all('SELECT DISTINCT object_id FROM ' . TABLE_PREFIX . 'project_object_views WHERE created_by_id = ?', $user->getId());
     if (is_foreachable($rows)) {
         foreach ($rows as $row) {
             $cached_value[] = (int) $row['object_id'];
         }
         // foreach
     }
     // if
     cache_set($cache_id, $cached_value);
     return $cached_value;
 }