/**
* {@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)
{
if ($this->getStartDate()) {
$queryBuilder->where('user.created_at >= ?d', [$this->getStartDate()]);
}
if ($this->getEndDate()) {
$queryBuilder->where('user.created_At <= ?d', [$this->getEndDate()]);
}
}
/**
* Load the tags via the query builder
*
* @throws \LogicException Throws exception if query builder is not set yet
*
* @return array
*/
private function _load()
{
if (null === $this->_queryBuilder) {
throw new \LogicException('Cannot load tags, query builder not set!');
}
$tags = $this->_queryBuilder->getQuery()->run()->flatten();
$this->_queryBuilder = null;
return $tags;
}
/**
* {@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)
{
$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);
}
/**
* Run query and use result data to build an instance of BundleProxy
*
* @param bool $returnAsArray Will return an array of Bundles if set to true, will return the first value
* of the array if false
* @return array | BundleProxy
*/
private function _load($returnAsArray = true)
{
if (null === $this->_queryBuilder) {
throw new \LogicException('No query builder set!');
}
$result = $this->_queryBuilder->getQuery()->run();
$bundles = [];
foreach ($result as $row) {
$bundle = new BundleProxy($this->_loaders, $this->_defaultCurrency);
$bundle->setID((int) $row->id);
$bundle->setName($row->name);
$bundle->setAllowCodes((bool) $row->allowCodes);
if ($row->start) {
$bundle->setStart(new DateTimeImmutable(date('c', $row->start)));
}
if ($row->end) {
$bundle->setEnd(new DateTimeImmutable(date('c', $row->end)));
}
if ($row->imageID) {
$bundle->setImageID($row->imageID);
}
$bundle->getAuthorship()->create(new DateTimeImmutable(date('c', $row->createdAt)), $this->_userLoader->getByID($row->createdBy));
if ($row->updatedAt) {
$bundle->getAuthorship()->update(new DateTimeImmutable(date('c', $row->updatedAt)), $this->_userLoader->getByID($row->updatedBy));
}
if ($row->deletedAt) {
$bundle->getAuthorship()->delete(new DateTimeImmutable(date('c', $row->deletedAt)), $this->_userLoader->getByID($row->deletedAt));
}
$bundles[$bundle->getID()] = $bundle;
}
return $returnAsArray ? $bundles : array_shift($bundles);
}
/**
* Loads the file data out of the table and loads in into a File Object.
*
* @return File|false return instance of the file is loaded else false
*/
protected function _loadFromQuery()
{
if (null === $this->_queryBuilder) {
throw new \LogicException('Cannot load files, query builder not set');
}
$result = $this->_queryBuilder->getQuery()->run();
if (count($result)) {
return $this->_loadFile($result);
}
$this->_queryBuilder = null;
return false;
}
/**
* Loop through loaded data and assign to fields in profile instances as appropriate
*
* @param bool $returnAsArray
*
* @return array|mixed
*/
private function _load($returnAsArray = false)
{
if (null === $this->_queryBuilder) {
throw new \LogicException('Query builder not set!');
}
$result = $this->_queryBuilder->getQuery()->run();
$this->_queryBuilder = null;
$profiles = [];
foreach ($result->collect('group') as $groupName => $rows) {
foreach ($rows as $row) {
if (!array_key_exists($row->userID, $profiles)) {
$profiles[$row->userID] = $this->_factory->getProfile($row->type);
}
$profile = $profiles[$row->userID];
if ($groupName) {
$group = $profile->{$groupName};
if (!$group) {
continue;
}
if ($group instanceof Field\RepeatableContainer) {
while (!$group->get($row->sequence)) {
$group->add();
}
$group = $group->get($row->sequence);
}
try {
$field = $group->{$row->field};
} catch (\OutOfBoundsException $e) {
continue;
}
} else {
$field = $profile->{$row->field};
}
if (!isset($field)) {
continue;
}
if ($field instanceof Field\MultipleValueField) {
$field->setValue($row->dataName, $row->value);
} elseif ($field instanceof Field\BaseField) {
$field->setValue($row->value_string);
}
}
}
return $returnAsArray ? $profiles : array_shift($profiles);
}
private function _loadFromQuery(Product $product = null)
{
if (null === $this->_queryBuilder) {
throw new \LogicException('Cannot load from query as query has not been built yet');
}
$result = $this->_queryBuilder->run();
$units = [];
foreach ($result as $row) {
if (!array_key_exists($row->id, $units)) {
$unit = new UnitProxy($this->_entityLoaderCollection, $this->_locale, $this->_prices, $this->_defaultCurrency);
$unit->id = $row->id;
$unit->barcode = $row->barcode;
$unit->weight = $row->weight;
$unit->supplierRef = $row->supplierRef;
$unit->revisionID = $row->revisionID;
$unit->options = [];
$unit->setSKU($row->sku);
$unit->setVisible((bool) $row->visible);
if ($product) {
$unit->setProduct($product);
} else {
$unit->setProductID($row->productID);
}
$unit->authorship->create(new DateTimeImmutable(date('c', $row->createdAt)), $row->createdBy);
if ($row->updatedAt) {
$unit->authorship->update(new DateTimeImmutable(date('c', $row->updatedAt)), $row->updatedBy);
}
if ($row->deletedAt) {
$unit->authorship->delete(new DateTimeImmutable(date('c', $row->deletedAt)), $row->deletedBy);
}
$units[$row->id] = $unit;
}
$unit = $units[$row->id];
if ($row->optionName && $row->optionValue && !array_key_exists($row->optionName, $unit->options)) {
$unit->options[$row->optionName] = $row->optionValue;
}
if (!array_key_exists($row->stockLocation, $unit->stock)) {
$unit->stock[$row->stockLocation] = $row->stock;
}
$unit->setPrice($row->price, $row->priceType, $row->currencyID);
}
return $this->_returnArray ? $units : array_shift($units);
}
public function testEleanorsQuery()
{
$forQuerys = [];
$q = new QueryBuilder($this->_connect, $this->_parser);
$forQuerys[] = $q->select('item.created_at AS date')->select('(IFNULL(item.net, 0)) AS net')->select('(IFNULL(item.tax, 0)) AS tax')->select('(IFNULL(item.gross, 0)) AS gross')->select('CONCAT(order_summary.type," Sale") AS `type`')->select('item.item_id AS item_id')->select('item.order_id AS order_id')->select('item.product_name AS product')->select('item.options AS `option`')->from('order_item AS item')->join('order_summary', 'item.order_id = order_summary.order_id')->leftJoin('return_item', 'return_item.exchange_item_id = item.item_id')->where('order_summary.status_code >= 0')->where('item.product_id NOT IN (9)')->where('return_item.exchange_item_id IS NULL')->where('item.created_at BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND UNIX_TIMESTAMP(NOW())');
$q = new QueryBuilder($this->_connect, $this->_parser);
$forQuerys[] = $q->select('order_summary.created_at AS date')->select('(IFNULL(net, 0)) AS net')->select('(IFNULL(tax, 0)) AS tax')->select('(IFNULL(gross, 0)) AS gross')->select('"Shipping In" AS `type`')->select('"" AS item_id')->select('order_shipping.order_id AS order_id')->select('"" AS product')->select('"" AS `option`')->from('order_shipping')->join('order_summary', 'order_shipping.order_id = order_summary.order_id')->where('order_summary.status_code >= 0')->where('order_summary.created_at BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND UNIX_TIMESTAMP(NOW())');
$q = new QueryBuilder($this->_connect, $this->_parser);
$forQuerys[] = $q->select('completed_at AS date')->select('(IFNULL(item.net, 0)) AS net')->select('(IFNULL(item.tax, 0)) AS tax')->select('(IFNULL(item.gross, 0)) AS gross')->select('"Exchange item" AS `type`')->select('item.item_id AS item_id')->select('item.order_id AS order_id')->select('item.product_name AS product')->select('item.options AS `option`')->from('order_item AS item')->join('order_summary', 'order_item.order_id = order_summary.order_id')->join('return_item', 'return_item.exchange_item_id = item.item_id')->join('ois', 'ois.item_id = item.item_id AND ois.status_code = 0', 'order_item_status')->where('order_summary.status_code >= 0')->where('item.product_id NOT IN (9)')->where('item.created_at BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND UNIX_TIMESTAMP(NOW())');
$q = new QueryBuilder($this->_connect, $this->_parser);
$forQuerys[] = $q->select('item.completed_at AS date')->select('-(IFNULL(net, 0)) AS net')->select('-(IFNULL(tax, 0)) AS tax')->select('-(IFNULL(gross, 0)) AS gross')->select('"Return" AS `type`')->select('item.item_id AS item_id')->select('item.order_id AS order_id')->select('item.product_name AS product')->select('item.options AS `option`')->from('return_item AS item')->join('`return`', 'return_item.return_id = return.order_id')->where('accepted = 1')->where('status_code >= 2200')->where('item.product_id NOT IN (9)')->where('item.completed_at BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND UNIX_TIMESTAMP(NOW())');
$fromQuery = new QueryBuilder($this->_connect, $this->_parser);
foreach ($forQuerys as $query) {
$fromQuery->unionAll($query);
}
$query = new QueryBuilder($this->_connect, $this->_parser);
$query->select('date AS date')->select('SUM(all.net) AS net')->select('SUM(all.tax) AS tax')->select('SUM(all.gross) AS gross')->select('all.type AS `type`')->select('all.order_id AS `order_id`')->select('all.item_id AS item_id')->select('all.product')->select('all.option')->select('country_id AS country')->from('all', $fromQuery);
$expected = 'SELECT
date AS date,
SUM(all.net) AS net,
SUM(all.tax) AS tax,
SUM(all.gross) AS gross,
all.type AS `type`,
all.order_id AS `order_id`,
all.item_id AS item_id,
all.product,
all.option,
country_id AS country
FROM (SELECT
item.created_at AS date,
(IFNULL(item.net, 0)) AS net,
(IFNULL(item.tax, 0)) AS tax,
(IFNULL(item.gross, 0)) AS gross,
CONCAT(order_summary.type," Sale") AS `type`,
item.item_id AS item_id,
item.order_id AS order_id,
item.product_name AS product,
item.options AS `option`
FROM order_item AS item
JOIN order_summary ON item.order_id = order_summary.order_id
LEFT JOIN return_item ON return_item.exchange_item_id = item.item_id
WHERE
order_summary.status_code >= 0
AND item.product_id NOT IN (9)
AND return_item.exchange_item_id IS NULL
AND item.created_at BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND UNIX_TIMESTAMP(NOW())
UNION ALL
SELECT
order_summary.created_at AS date,
(IFNULL(net, 0)) AS net,
(IFNULL(tax, 0)) AS tax,
(IFNULL(gross, 0)) AS gross,
"Shipping In" AS `type`,
"" AS item_id,
order_shipping.order_id AS order_id,
"" AS product,
"" AS `option`
FROM order_shipping
JOIN order_summary ON order_shipping.order_id = order_summary.order_id
WHERE
order_summary.status_code >= 0
AND order_summary.created_at BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND UNIX_TIMESTAMP(NOW())
UNION ALL
SELECT
completed_at AS date,
(IFNULL(item.net, 0)) AS net,
(IFNULL(item.tax, 0)) AS tax,
(IFNULL(item.gross, 0)) AS gross,
"Exchange item" AS `type`,
item.item_id AS item_id,
item.order_id AS order_id,
item.product_name AS product,
item.options AS `option`
FROM order_item AS item
JOIN order_summary ON order_item.order_id = order_summary.order_id
JOIN return_item ON return_item.exchange_item_id = item.item_id
JOIN order_item_status ois ON ois.item_id = item.item_id AND ois.status_code = 0
WHERE
order_summary.status_code >= 0
AND item.product_id NOT IN (9)
AND item.created_at BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND UNIX_TIMESTAMP(NOW())
UNION ALL
SELECT
item.completed_at AS date,
-(IFNULL(net, 0)) AS net,
-(IFNULL(tax, 0)) AS tax,
-(IFNULL(gross, 0)) AS gross,
"Return" AS `type`,
item.item_id AS item_id,
item.order_id AS order_id,
item.product_name AS product,
item.options AS `option`
FROM return_item AS item
JOIN `return` ON return_item.return_id = return.order_id
WHERE
accepted = 1
AND status_code >= 2200
AND item.product_id NOT IN (9)
AND item.completed_at BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND UNIX_TIMESTAMP(NOW())) all';
$this->assertEquals(trim(preg_replace('/\\s+/', ' ', $expected)), trim(preg_replace('/\\s+/', ' ', $query->getQueryString())));
}
public function apply(DB\QueryBuilder $queryBuilder)
{
if ($this->getChoices()) {
$queryBuilder->where('address.country_id IN (?js)', [$this->getChoices()]);
}
}
