public function apply(DB\QueryBuilder $queryBuilder)
{
if ($this->getStartDate()) {
$queryBuilder->where('user.created_at >= ?d', [$this->getStartDate()]);
}
if ($this->getEndDate()) {
$queryBuilder->where('user.created_At <= ?d', [$this->getEndDate()]);
}
}
/**
* {@inheritDoc}
*/
public function apply(DB\QueryBuilder $queryBuilder)
{
if ($this->getStartDate()) {
$queryBuilder->where('email_subscription.created_at >= ?d', [$this->getStartDate()]);
}
if ($this->getEndDate()) {
$queryBuilder->where('email_subscription.created_at <= ?d', [$this->getEndDate()]);
}
}
public function apply(DB\QueryBuilder $queryBuilder)
{
$queryString = $queryBuilder->getQueryString();
if (strpos($queryString, 'JOIN product') || strpos($queryString, 'FROM product')) {
// Filter brand
if ($brand = $this->getChoices()) {
is_array($brand) ? $queryBuilder->where('product.brand IN (?js)', [$brand]) : $queryBuilder->where('product.brand = (?s)', [$brand]);
}
}
if (strpos($queryString, 'JOIN order_shipping') || strpos($queryString, 'FROM order_shipping')) {
$queryBuilder->where('1 = 2');
}
}
public function apply(DB\QueryBuilder $queryBuilder)
{
$addressType = $this->getChoices();
if (is_array($addressType)) {
$addressType = array_shift($addressType);
}
$and = true;
if (!$addressType || $addressType === 'all') {
$queryBuilder->where('address.type IS NULL');
$addressType = 'delivery';
$and = false;
}
$queryBuilder->where('address.type = ?s', [$addressType], $and);
}
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');
}
}
/**
* 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');
}
}
/**
* Load a bundle by its name
*
* @param $name
*
* @return BundleProxy
*/
public function getByName($name)
{
if (!is_string($name)) {
throw new \LogicException('Bundle name must be a string');
}
$this->_buildQuery();
$this->_queryBuilder->where('b.name = ?s', [$name]);
return $this->_load(false);
}
/**
* {@inheritDoc}
*/
public function apply(DB\QueryBuilder $queryBuilder)
{
$choice = $this->getChoices();
if (is_array($choice)) {
$choice = array_shift($choice);
}
if (!$choice) {
return;
}
$queryBuilder->where($this->_getStatement($choice));
}
public function apply(DB\QueryBuilder $queryBuilder)
{
if ($this->getChoices()) {
$queryBuilder->where('address.country_id IN (?js)', [$this->getChoices()]);
}
}
/**
* Get tags belonging to a file
*
* @param File $file
*
* @return array
*/
public function getByFile(File $file)
{
$this->_setQueryBuilder();
$this->_queryBuilder->where('file_tag.file_id = ?i', [$file->id]);
return $this->_load();
}
/**
* Load the profile by the user
*
* @param User $user
*
* @return array|mixed
*/
public function getByUser(User $user)
{
$this->_buildQuery();
$this->_queryBuilder->where('p.user_id = ?i', [$user->id]);
return $this->_load() ?: $this->_factory->getProfile($this->_typeLoader->getByUser($user));
}
