PHP JDatabaseDriver::loadAssocList方法代码示例

 protected function load()
 {
     $query = $this->db->getQuery(true);
     $query->select('a.id, a.name')->from($this->db->quoteName('#__itpsc_countries', 'a'))->order('a.name ASC');
     // Get the options.
     $this->db->setQuery($query);
     $this->data = $this->db->loadAssocList('id', 'name');
 }

 /**
  * Load the data for all user accounts by userId
  *
  * <code>
  * $userId    = 1;
  *
  * $accounts  = new VirtualCurrencyAccounts(JFactory::getDbo());
  * $accounts->load($userId);
  * </code>
  *
  * @param integer $userId
  */
 public function load($userId)
 {
     $query = $this->db->getQuery(true);
     $query->select("a.id, a.amount, a.note, a.currency_id, a.user_id, " . "b.title, b.code, b.symbol, " . "c.name")->from($this->db->quoteName("#__vc_accounts", "a"))->innerJoin($this->db->quoteName("#__vc_currencies", "b") . " ON a.currency_id = b.id")->innerJoin($this->db->quoteName("#__users", "c") . " ON a.user_id = c.id")->where("a.user_id = " . (int) $userId);
     $this->db->setQuery($query);
     $results = $this->db->loadAssocList();
     if (!empty($results)) {
         $this->items = $results;
     }
 }

 /**
  * Load categories.
  *
  * <code>
  * $parentId = 2;
  *
  * $options = array(
  *    "offset" => 0,
  *    "limit" => 10,
  *    "order_by" => "a.name",
  *    "order_dir" => "DESC",
  * );
  *
  * $categories   = new Crowdfunding\Categories();
  * $categories->setDb(\JFactory::getDbo());
  *
  * $categories->load($parentId);
  * </code>
  *
  * @param null|int $parentId Parent ID or "root".
  * @param array $options
  *
  * @throws \RuntimeException
  */
 public function load($parentId = null, array $options = array())
 {
     $offset = array_key_exists('offset', $options) ? $options['offset'] : 0;
     $limit = array_key_exists('limit', $options) ? $options['limit'] : 0;
     $orderBy = array_key_exists('order_by', $options) ? $options['order_by'] : 'a.title';
     $orderDir = array_key_exists('order_dir', $options) ? $options['order_dir'] : 'ASC';
     $published = array_key_exists('state', $options) ? $options['state'] : null;
     $orderDir = strtoupper($orderDir);
     if (!in_array($orderDir, array('ASC', 'DESC'), true)) {
         $orderDir = 'ASC';
     }
     $query = $this->db->getQuery(true);
     $query->select('a.id, a.title, a.alias, a.description, a.params, ' . $query->concatenate(array('a.id', 'a.alias'), ':') . ' AS slug')->from($this->db->quoteName('#__categories', 'a'))->where('a.extension = ' . $this->db->quote($this->_extension));
     if ($parentId !== null) {
         $query->where('a.parent_id = ' . (int) $parentId);
     }
     // Filter by state.
     if ($published === null) {
         $query->where('a.published IN (0,1)');
     } else {
         $query->where('a.published = ' . (int) $published);
     }
     $query->order($this->db->escape($this->db->quoteName($orderBy) . ' ' . $orderDir));
     $this->db->setQuery($query, (int) $offset, (int) $limit);
     $this->data = (array) $this->db->loadAssocList('id');
 }

 /**
  * Load types data from database.
  *
  * <code>
  * $options = array(
  *  "order_column" => "title", // id or title
  *  "order_direction" => "DESC",
  * );
  *
  * $types    = new CrowdFundingTypes();
  * $types->setDb(JFactory::getDbo());
  * $types->load($options);
  *
  * foreach ($types as $type) {
  *      echo $type["title"];
  *      echo $type["description"];
  * }
  * </code>
  *
  * @param array $options
  */
 public function load($options = array())
 {
     $query = $this->db->getQuery(true);
     $query->select("a.id, a.title, a.description, a.params")->from($this->db->quoteName("#__crowdf_types", "a"));
     // Order by column
     if (isset($options["order_column"])) {
         $orderString = $this->db->quoteName($options["order_column"]);
         // Order direction
         if (isset($options["order_direction"])) {
             $orderString .= strcmp("DESC", $options["order_direction"]) ? " DESC" : " ASC";
         }
         $query->order($orderString);
     }
     $this->db->setQuery($query);
     $results = $this->db->loadAssocList();
     if (!empty($results)) {
         foreach ($results as $result) {
             $type = new CrowdFundingType();
             $type->bind($result);
             $this->types[] = $type;
         }
     } else {
         $this->types = array();
     }
 }

 /**
  * Return information about amounts by transaction statuses.
  *
  * <code>
  * $projectId    = 1;
  *
  * $statistics   = new Crowdfunding\Statistics\Project(\JFactory::getDbo(), $projectId);
  * $payoutInformation = $statistics->getPayoutInformation();
  * </code>
  *
  * @return array
  */
 public function getPayoutStatistics()
 {
     // Create a new query object.
     $query = $this->db->getQuery(true);
     $query->select('a.txn_status, SUM(txn_amount) AS amount')->from($this->db->quoteName('#__crowdf_transactions', 'a'))->group('a.txn_status')->where('a.project_id = ' . (int) $this->id);
     $this->db->setQuery($query);
     return (array) $this->db->loadAssocList('txn_status');
 }

 /**
  * Load log types and prepare them as an array with options.
  *
  * <code>
  * $filters = new Crowdfunding\Filters(\JFactory::getDbo());
  * $options = $filters->getLogTypes();
  * </code>
  *
  * @return array
  */
 public function getLogTypes()
 {
     if (!array_key_exists('log_types', $this->options)) {
         $query = $this->db->getQuery(true);
         $query->select('a.type AS value, a.type AS text')->from($this->db->quoteName('#__crowdf_logs', 'a'))->group('a.type');
         $this->db->setQuery($query);
         $this->options['log_types'] = (array) $this->db->loadAssocList();
     }
     return $this->options['log_types'];
 }

 /**
  * Load log types and prepare them as an array with options.
  *
  * <code>
  * $filters = new Crowdfunding\Filters(\JFactory::getDbo());
  * $options = $filters->getLogTypes();
  * </code>
  *
  * @return array
  */
 public function getLogTypes()
 {
     $query = $this->db->getQuery(true);
     $query->select("a.type AS value, a.type AS text")->from($this->db->quoteName("#__crowdf_logs", "a"))->group("a.type");
     $this->db->setQuery($query);
     $types = $this->db->loadAssocList();
     if (!$types) {
         $types = array();
     }
     return $types;
 }

 /**
  * Load data about user rewards by reward ID.
  *
  * <code>
  * $userId = 1;
  *
  * $rewards   = new CrowdFundingUserRewards(JFactory::getDbo());
  * $rewards->load($userId);
  *
  * foreach($rewards as $reward) {
  *   echo $reward["reward_id"];
  *   echo $reward["reward_name"];
  * }
  *
  * </code>
  *
  * @param int $id Reward ID
  */
 public function loadByRewardId($id)
 {
     $query = $this->getQuery();
     $query->where("a.reward_id = " . (int) $id);
     $this->db->setQuery($query);
     $results = $this->db->loadAssocList();
     if (!$results) {
         $results = array();
     }
     $this->items = $results;
 }

 /**
  * Return information about amounts by transaction statuses.
  *
  * <code>
  * $projectId    = 1;
  *
  * $statistics   = new CrowdfundingStatisticsProject(\JFactory::getDbo(), $projectId);
  * $payoutInformation = $statistics->getPayoutInformation();
  * </code>
  *
  * @return array
  */
 public function getPayoutStatistics()
 {
     // Create a new query object.
     $query = $this->db->getQuery(true);
     $query->select("a.txn_status, SUM(txn_amount) AS amount")->from($this->db->quoteName("#__crowdf_transactions", "a"))->group("a.txn_status")->where("a.project_id = " . (int) $this->id);
     $this->db->setQuery($query);
     $result = $this->db->loadAssocList("txn_status");
     if (!$result) {
         $result = array();
     }
     return $result;
 }

 /**
  * Calculate a project amount for full period of the campaign.
  *
  * <code>
  * $projectId    = 1;
  *
  * $statistics   = new CrowdFundingStatisticsProject(JFactory::getDbo(), $projectId);
  * $amount = $statistics->getFullPeriodAmounts();
  * </code>
  *
  * @return int
  */
 public function getFullPeriodAmounts()
 {
     $query = $this->db->getQuery(true);
     $query->select("a.funding_start, a.funding_end")->from($this->db->quoteName("#__crowdf_projects", "a"))->where("a.id = " . (int) $this->id);
     $this->db->setQuery($query);
     $result = $this->db->loadObject();
     // Validate dates
     jimport("itprism.validator.date");
     $fundingStartDate = new ITPrismValidatorDate($result->funding_start);
     $fundingEndDate = new ITPrismValidatorDate($result->funding_end);
     if (!$fundingStartDate->isValid() or !$fundingEndDate->isValid()) {
         return array();
     }
     $dataset = array();
     jimport("itprism.date");
     $date = new ITPrismDate();
     $date1 = new ITPrismDate($result->funding_start);
     $date2 = new ITPrismDate($result->funding_end);
     $period = $date->getDaysPeriod($date1, $date2);
     $query = $this->db->getQuery(true);
     $query->select("a.txn_date as date, SUM(a.txn_amount) as amount")->from($this->db->quoteName("#__crowdf_transactions", "a"))->where("a.project_id = " . (int) $this->id)->group("DATE(a.txn_date)");
     $this->db->setQuery($query);
     $results = $this->db->loadAssocList();
     if (!$results) {
         $results = array();
     }
     // Prepare data
     $data = array();
     foreach ($results as $result) {
         $date = new JDate($result["date"]);
         $index = $date->format("d.m");
         $data[$index] = $result;
     }
     /** @var $day JDate */
     foreach ($period as $day) {
         $dayMonth = $day->format("d.m");
         if (isset($data[$dayMonth])) {
             $amount = $data[$dayMonth]["amount"];
         } else {
             $amount = 0;
         }
         $dataset[] = array("date" => $dayMonth, "amount" => $amount);
     }
     return $dataset;
 }

 /**
  * Load currencies data by abbreviation from database.
  *
  * <code>
  * $ids = array("GBP", "EUR", "USD");
  * $currencies   = new CrowdFundingCurrencies(JFactory::getDbo());
  * $currencies->loadByAbbr($ids);
  *
  * foreach($currencies as $currency) {
  *   echo $currency["title"];
  *   echo $currency["abbr"];
  * }
  * </code>
  *
  * @param array $ids
  */
 public function loadByAbbr($ids = array())
 {
     // Load project data
     $query = $this->db->getQuery(true);
     $query->select("a.id, a.title, a.abbr, a.symbol, a.position")->from($this->db->quoteName("#__crowdf_currencies", "a"));
     if (!empty($ids)) {
         foreach ($ids as $key => $value) {
             $ids[$key] = $this->db->quote($value);
         }
         $query->where("a.abbr IN ( " . implode(",", $ids) . " )");
     }
     $this->db->setQuery($query);
     $results = $this->db->loadAssocList();
     if (!$results) {
         $results = array();
     }
     $this->items = $results;
 }

 /**
  * Return with all row by attributes
  *
  * @param array       $attributes
  * @param bool|array  $fields
  * @param bool|string $order
  *
  * @return mixed
  */
 public function findAllByAttributes(array $attributes, $fields = false, $order = false)
 {
     $query = $this->db->getQuery(true);
     if ($fields) {
         $query->select($this->db->quoteName($fields));
     } else {
         $query->select('*');
     }
     $query->from($this->db->quoteName($this->tableName));
     foreach ($attributes as $key => $val) {
         $query->where($this->db->quoteName($key) . ' = ' . (is_numeric($val) ? $val : $this->db->quote($val)));
     }
     if ($order) {
         $query->order($order);
     }
     $this->db->setQuery($query);
     return $this->db->loadAssocList();
 }

 /**
  * Load categories.
  *
  * <code>
  * $parentId = 2;
  *
  * $options = array(
  *    "offset" => 0,
  *    "limit" => 10,
  *    "order_by" => "a.name",
  *    "order_dir" => "DESC",
  * );
  *
  * $categories   = new Crowdfunding\Categories();
  * $categories->setDb(\JFactory::getDbo());
  *
  * $categories->load($parentId);
  * </code>
  * 
  * @param null|int $parentId Parent ID or "root".
  * @param array $options
  */
 public function load($parentId = null, $options = array())
 {
     $offset = isset($options["offset"]) ? $options["offset"] : 0;
     $limit = isset($options["limit"]) ? $options["limit"] : 0;
     $orderBy = isset($options["order_by"]) ? $options["order_by"] : "a.title";
     $orderDir = isset($options["order_dir"]) ? $options["order_dir"] : "ASC";
     $orderDir = String::strtoupper($orderDir);
     if (!in_array($orderDir, array("ASC", "DESC"))) {
         $orderDir = "ASC";
     }
     $query = $this->db->getQuery(true);
     $query->select("a.id, a.title, a.alias, a.description, a.params, " . $query->concatenate(array("a.id", "a.alias"), ":") . " AS slug")->from($this->db->quoteName("#__categories", "a"))->where("a.extension = " . $this->db->quote($this->_extension));
     if (!is_null($parentId)) {
         $query->where("a.parent_id = " . (int) $parentId);
     }
     $query->order($this->db->quoteName($orderBy) . " " . $orderDir);
     $this->db->setQuery($query, (int) $offset, (int) $limit);
     $this->data = (array) $this->db->loadAssocList("id");
 }

 /**
  * Load currencies data.
  *
  * <code>
  *  // The state could be 1 = published, 0 = unpublished, null = all
  *  $options = array(
  *      "state" => 1
  *  );
  *
  *  $currencies = new VirtualCurrencyCurrencies();
  *  $currencies->setDb(JFactory::getDbo());
  *  $currencies->load($options);
  * </code>
  *
  * @param array $options
  *
  */
 public function load($options = array())
 {
     $query = $this->db->getQuery(true);
     $query->select("a.id, a.title, a.code, a.symbol, a.params, a.published")->from($this->db->quoteName("#__vc_currencies", "a"));
     $state = JArrayHelper::getValue($options, "state");
     if (!is_null($state)) {
         $state = !$state ? 0 : 1;
         $query->where("a.published = " . (int) $state);
     }
     $this->db->setQuery($query);
     $results = $this->db->loadAssocList();
     if (!empty($results)) {
         foreach ($results as $key => $value) {
             if (!empty($value["params"])) {
                 $results[$key]["params"] = json_decode($value["params"], true);
             }
         }
         $this->items = $results;
     }
 }

 /**
  * Load the placeholders of the categories from database.
  *
  * @param array $categories
  */
 protected function preparePlaceholders($categories)
 {
     $ids = array();
     foreach ($categories as $key => $category) {
         $ids[] = $category['id'];
         if (!array_key_exists('placeholders', $category) or !is_array($category['placeholders'])) {
             $categories[$key]['placeholders'] = array();
         }
     }
     $query = $this->db->getQuery(true);
     $query->select('a.id, a.name, a.description, a.catid, ' . 'b.title AS category')->from($this->db->quoteName('#__emailtemplates_placeholders', 'a'))->leftJoin($this->db->quoteName('#__categories', 'b') . ' ON a.catid = b.id')->where('a.catid IN (' . implode(',', $ids) . ')')->order('a.name ASC');
     $this->db->setQuery($query);
     $results = $this->db->loadAssocList();
     foreach ($categories as $key => $category) {
         foreach ($results as $placeholder) {
             if ((int) $category['id'] === (int) $placeholder['catid']) {
                 $categories[$key]['placeholders'][] = $placeholder;
             }
         }
     }
     return $categories;
 }