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;
}
/**
* Load locations data by string from database.
*
* <code>
* $string = "Plovdiv";
*
* $locations = new CrowdFundingLocations(JFactory::getDbo());
* $locations->loadByString($string);
*
* foreach($locations as $location) {
* echo $location["id"];
* echo $location["name"];
* }
* </code>
*
* @param string $string
* @param int $mode Filter mode.
*
* Example:
*
* # Filter modes
* 0 = "string";
* 1 = "string%";
* 2 = "%string";
* 3 = "%string%";
*/
public function loadByString($string, $mode = 1)
{
$query = $this->db->getQuery(true);
switch ($mode) {
case 1:
// Beginning
$searchFilter = $this->db->escape($string, true) . '%';
break;
case 2:
// End
$searchFilter = '%' . $this->db->escape($string, true);
break;
case 3:
// Both
$searchFilter = '%' . $this->db->escape($string, true) . '%';
break;
default:
// NONE
$searchFilter = $this->db->escape($string, true);
break;
}
$search = $this->db->quote($searchFilter);
$caseWhen = ' CASE WHEN ';
$caseWhen .= $query->charLength('a.state_code', '!=', '0');
$caseWhen .= ' THEN ';
$caseWhen .= $query->concatenate(array('a.name', 'a.state_code', 'a.country_code'), ', ');
$caseWhen .= ' ELSE ';
$caseWhen .= $query->concatenate(array('a.name', 'a.country_code'), ', ');
$caseWhen .= ' END as name';
$query->select("a.id, " . $caseWhen)->from($this->db->quoteName("#__crowdf_locations", "a"))->where($this->db->quoteName("a.name") . " LIKE " . $search);
$this->db->setQuery($query, 0, 8);
$results = $this->db->loadAssocList();
if (!$results) {
$results = array();
}
$this->items = $results;
}
/**
* Load a associative array of associative database rows or column values.
*
* @param string $key The name of a field on which to key the result array
* @param string $column [optional] column name. If not null: Instead of the whole row, only this column value will be in the result array
* @return array If $key is null: Sequential array of returned records/values, Otherwise: Keyed array
*
* @throws \RuntimeException
*/
public function loadAssocList($key = null, $column = null)
{
return $this->_nullToArray($this->_db->loadAssocList($key, $column));
}
/**
* Load a assoc list of database rows
*
* @param string The field name of a primary key
* @return array If <var>key</var> is empty as sequential list of returned records.
*/
function loadAssocList($key = null)
{
if ($key == '' || checkJversion() >= 0) {
$resultArray = $this->_db->loadAssocList($key);
return $this->_nullToArray($resultArray);
} else {
// mambo 4.5.2 - 4.6.2 has a bug in key:
if (!($cur = $this->query())) {
return null;
}
$array = array();
while (is_array($row = $this->m_fetch_assoc($cur))) {
if ($key) {
$array[$row[$key]] = $row;
// $row->key is not an object, but an array
} else {
$array[] = $row;
}
}
$this->m_free_result($cur);
return $array;
}
}
