public function getByUserID($userID)
{
if (!is_numeric($userID)) {
throw new \InvalidArgumentException('User ID must be numeric');
}
$result = $this->_queryBuilderFactory->getQueryBuilder()->select('type')->from(self::TYPE_TABLE)->where('user_id = ?i', [$userID])->getQuery()->run();
$type = $result->count() ? $result->value() : 'none';
return $this->_userTypes->get($type);
}
/**
* {@inheritDoc}
*/
public function load(ConfigProxy $config)
{
$result = $this->_qbFactory->getQueryBuilder()->select($this->_columns)->from('refer_a_friend_reward_trigger')->where('reward_config_id = :id?i', ['id' => $config->getID()])->getQuery()->run();
$triggers = new Collection();
foreach ($result as $row) {
$triggers->add($this->_triggers->get($row->name));
}
return $triggers;
}
/**
* Load prices for bundle and return as an associative array
*
* @param BundleProxy $bundle
*
* @return array
*/
public function getPrices(BundleProxy $bundle)
{
$result = $this->_queryBuilderFactory->getQueryBuilder()->select($this->_columns)->from(self::TABLE_NAME)->where('bundle_id = ?i', [$bundle->getID()])->getQuery()->run();
$prices = [];
foreach ($result as $row) {
$prices[$row->currency] = $row->price;
}
return $prices;
}
/**
* Sets the query builder with the appropriate SELECT and FROM statement
*/
private function _setQueryBuilder()
{
$this->_queryBuilder = $this->_queryBuilderFactory->getQueryBuilder()->select(['file.file_id AS id', 'file.url AS url', 'file.name AS `name`', 'file.extension AS extension', 'file.file_size AS fileSize', 'file.created_at AS createdAt', 'file.created_by AS createdBy', 'file.updated_at AS updatedAt', 'file.updated_by AS updatedBy', 'file.deleted_at AS deletedAt', 'file.deleted_by AS deletedBy', 'file.type_id AS typeID', 'file.checksum AS checksum', 'file.preview_url AS previewUrl', 'file.dimension_x AS dimensionX', 'file.dimension_y AS dimensionY', 'file.alt_text AS altText', 'file.duration AS duration'])->from('file')->orderBy('file.created_at DESC');
if (!$this->_loadDeleted) {
$this->_queryBuilder->where('file.deleted_at IS NULL');
}
}
private function _buildQuery($revisionID = null)
{
$getRevision = $revisionID ?: $this->_queryBuilderFactory->getQueryBuilder()->select('MAX(revision_id)')->from('product_unit_info')->where('unit_id = product_unit.unit_id');
$this->_queryBuilder = $this->_queryBuilderFactory->getQueryBuilder()->select(['product_unit.product_id AS productID', 'product_unit.unit_id AS id', 'IFNULL(product_unit.weight_grams, product.weight_grams) AS weight', 'product_unit_info.sku AS sku', 'product_unit.barcode AS barcode', 'product_unit.visible AS visible', 'product_unit.created_at AS createdAt', 'product_unit.created_by AS createdBy', 'product_unit.updated_at AS updatedAt', 'product_unit.updated_by AS updatedBy', 'product_unit.deleted_at AS deletedAt', 'product_unit.deleted_by AS deletedBy', 'product_unit.supplier_ref AS supplierRef', 'IFNULL(product_unit_info.revision_id,1) AS revisionID', 'product_unit_info.sku AS sku', 'product_unit_stock.stock AS stock', 'product_unit_stock.location AS stockLocation', 'product_price.type AS priceType', 'product_price.currency_id AS currencyID', 'IFNULL(product_unit_price.price, product_price.price) AS price', 'product_unit_option.option_name AS optionName', 'product_unit_option.option_value AS optionValue'])->from('product_unit')->join('product', 'product_unit.product_id = product.product_id');
if (is_numeric($getRevision)) {
$this->_queryBuilder->leftJoin('product_unit_info', '
product_unit_info.unit_id = product_unit.unit_id AND
revision_id = :revisionID?i
')->addParams(['revisionID' => $getRevision]);
} else {
$this->_queryBuilder->leftJoin('product_unit_info', '
product_unit_info.unit_id = product_unit.unit_id AND
revision_id = (:revisionID?q)
')->addParams(['revisionID' => $getRevision]);
}
$this->_queryBuilder->leftJoin('product_unit_stock', 'product_unit.unit_id = product_unit_stock.unit_id')->leftJoin('product_price', 'product_unit.product_id = product_price.product_id')->leftJoin('product_unit_price', '
product_unit.unit_id = product_unit_price.unit_id AND
product_price.type = product_unit_price.type AND
product_price.currency_id = product_unit_price.currency_id
')->leftJoin('product_unit_option', '
product_unit_option.unit_id = product_unit.unit_id AND
product_unit_option.revision_id = product_unit_info.revision_id
');
if (!$this->_loadInvisible) {
$this->_queryBuilder->where('product_unit.visible = ?b', [true]);
}
if (!$this->_loadOutOfStock) {
$this->_queryBuilder->where('product_unit_stock.stock > 0');
}
if (!$this->_loadDeleted) {
$this->_queryBuilder->where('product_unit.deleted_at IS NULL');
}
}
/**
* Get the minimum left position from the nested set
*
* @return int
*/
private function _getMinPositionLeft()
{
$queryBuilder = $this->_queryBuilderFactory->getQueryBuilder()->select('MIN(`position_left`)')->from('page');
if (!$this->_loadDeleted) {
$queryBuilder->where('deleted_at IS NULL');
}
return $queryBuilder->getQuery()->run()->value();
}
/**
* Get basic select statement query builder with all appropriate columns
*
* @param null | string $status If set, a WHERE statement will be added to the query to filter by status
*
* @return \Message\Cog\DB\QueryBuilder
*/
private function _getSelect($status = null)
{
$select = $this->_qbFactory->getQueryBuilder()->select($this->_columns)->from('refer_a_friend_referral')->where('deleted_at IS NULL');
if (null !== $status) {
$select->where('status = :status?s', ['status' => $status]);
}
return $select;
}
/**
* Build query without where statement
*/
private function _buildQuery()
{
$queryBuilder = $this->_queryBuilderFactory->getQueryBuilder()->select($this->_columns)->from('b', self::TABLE_NAME)->leftJoin('bi', 'b.bundle_id = bi.bundle_id', self::IMAGE_TABLE_NAME);
if (false === $this->_includeDeleted) {
$queryBuilder->where('deleted_at IS NULL');
}
$this->_queryBuilder = $queryBuilder;
}
/**
* Load product data and create instances of ProductRow to assign to bundle
*
* @param BundleProxy $bundle
*
* @return array
*/
public function getProductRows(BundleProxy $bundle)
{
$result = $this->_queryBuilderFactory->getQueryBuilder()->select($this->_columns)->from('p', self::PRODUCT_TABLE)->leftJoin('o', 'p.product_row_id = o.product_row_id', self::OPTION_TABLE)->where('p.bundle_id = ?i', [$bundle->getID()])->orderBy('p.product_row_id ASC')->getQuery()->run();
$productRowData = [];
$productRows = [];
// Reorganise data into mutlidimensional array split into product rows to allow for multiple options per row
foreach ($result as $row) {
if (!array_key_exists($row->id, $productRowData)) {
$productRowData[$row->id] = ['product_id' => $row->product_id, 'options' => $this->_getRowOptionsArray($row->option_name, $row->option_value), 'quantity' => $row->quantity];
}
$productRowData[$row->id]['options'] = $productRowData[$row->id]['options'] + $this->_getRowOptionsArray($row->option_name, $row->option_value);
}
foreach ($productRowData as $id => $data) {
$productRow = new ProductRow($data['product_id'], $data['options'], $data['quantity']);
$productRow->setID($id);
$productRows[] = $productRow;
}
return $productRows;
}
/**
* Load choices for form field if not already set, and if there is an instance of QueryBuilderFactory set
* against the filter class
*/
private function _setChoices()
{
if (null === $this->_queryBuilderFactory || !empty($this->getOptions()['choices'])) {
return;
}
$queryBuilder = $this->_queryBuilderFactory->getQueryBuilder()->select('page_content.value_string', true)->from('page_content')->join('page', 'page_content.page_id = page.page_id')->where('page_content.field_name = ?s', [$this->_field])->where('page.deleted_at IS NULL')->where('page.publish_at <= UNIX_TIMESTAMP()')->where('(page.unpublish_at IS NULL OR page.unpublish_at > UNIX_TIMESTAMP())');
if (null !== $this->_group) {
$queryBuilder->where('group_name = ?s', [$this->_group]);
} else {
$queryBuilder->where('(group_name IS NULL OR group_name = \'\')');
}
$result = $queryBuilder->getQuery()->run()->flatten();
$choices = [];
foreach ($result as $value) {
$value = (string) $value;
if ($value !== '') {
$choices[$value] = $value;
}
}
$this->setOptions(['choices' => $choices]);
}
/**
* Get an instance of the QueryBuilder with the correct columns and table already set
*
* @return \Message\Cog\DB\QueryBuilder
*/
private function _getSelect()
{
return $this->_qbFactory->getQueryBuilder()->select($this->_columns)->from('refer_a_friend_reward_config');
}
/**
* Set up a new instance of the query builder with the SELECT and FROM statements set
*/
private function _setQueryBuilder()
{
$this->_queryBuilder = $this->_queryBuilderFactory->getQueryBuilder()->select('file_tag.tag_name')->from('file_tag');
}
private function _getAuthors()
{
return $this->_qbFactory->getQueryBuilder()->select('value', true)->from('product_detail')->where('name = ?s', ['author'])->getQuery()->run()->flatten();
}
/**
* Gets the payments created through a gateway.
*
* @param GatewayInterface $gateway The gateway
* @return array[Payment\Payment] The payments for the gateway
*/
public function getPaymentsByGateway(GatewayInterface $gateway)
{
$result = $this->_queryBuilderFactory->getQueryBuilder()->select('`payment_id`')->from('`payment_gateway`')->where('`gateway` = ?s', [$gateway->getName()]);
return $this->_paymentLoader->getByIDs($result->flatten());
}
/**
* Build the basic query for loading profiles
*/
private function _buildQuery()
{
$this->_queryBuilder = $this->_queryBuilderFactory->getQueryBuilder()->select($this->_columns)->from('p', self::PROFILE_TABLE)->leftJoin('t', 't.user_id = p.user_id', TypeLoader::TYPE_TABLE);
}
/**
* Get QueryBuilder instance with appropriate selected fields
*
* @return \Message\Cog\DB\QueryBuilder
*/
private function _getSelect()
{
return $this->_queryBuilderFactory->getQueryBuilder()->select($this->_selectFields)->from(self::TABLE_NAME);
}
/**
* @param UserTypeInterface $type
*
* @return array | User\User
*/
public function getByType(UserTypeInterface $type)
{
$ids = $this->_queryBuilderFactory->getQueryBuilder()->select('user_id')->from('user_type')->where('type = ?s', [$type->getName()])->run()->flatten();
return $this->_baseLoader->getByID($ids);
}
