1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
<?php
/**
* Refer to LICENSE.txt distributed with the Temando Shipping module for notice of license
*/
namespace Temando\Shipping\Model\ResourceModel\Batch;
use Magento\Sales\Api\Data\OrderInterface;
use Magento\Sales\Model\ResourceModel\Order\Collection;
use Temando\Shipping\Setup\SetupSchema;
/**
* Temando Batch Order Collection
*
* @package Temando\Shipping\Model
* @author Benjamin Heuer <benjamin.heuer@netresearch.de>
* @author Christoph Aßmann <christoph.assmann@netresearch.de>
* @license https://opensource.org/licenses/osl-3.0.php Open Software License (OSL 3.0)
* @link https://www.temando.com/
*/
class OrderCollection extends Collection
{
/**
* @param string $carrierCode
*/
public function addCarrierFilter($carrierCode)
{
$this->addFieldToFilter('shipping_method', ['like' => ["$carrierCode%"]]);
}
/**
* Filter orders which can be shipped. This is not complete. Filtering
* remaining items in a loop is still necessary.
* @see \Magento\Sales\Model\Order::canShip()
*
* @link https://magento.stackexchange.com/a/72600
*
* @return void
*/
public function addCanShipFilter()
{
$orderItemTable = $this->getTable('sales_order_item');
$pickupLocationTable = $this->getTable(SetupSchema::TABLE_ORDER_PICKUP_LOCATION);
$select = $this->getSelect();
$select->join(
['order_item' => $orderItemTable],
'main_table.entity_id = order_item.order_id',
[]
);
$select->joinLeft(
['pickup_location' => $pickupLocationTable],
'main_table.shipping_address_id = pickup_location.recipient_address_id',
[]
);
$this->addFieldToFilter(sprintf('main_table.%s', OrderInterface::IS_VIRTUAL), 0);
$this->addFieldToFilter(OrderInterface::STATE, ['nin' => [
\Magento\Sales\Model\Order::STATE_HOLDED,
\Magento\Sales\Model\Order::STATE_PAYMENT_REVIEW,
\Magento\Sales\Model\Order::STATE_CANCELED,
]]);
$this->addExpressionFieldToSelect(
'qty_to_ship',
'FLOOR(SUM({{qty_ordered}}) - SUM({{qty_shipped}}))',
[
'qty_ordered' => 'order_item.qty_ordered',
'qty_shipped' => 'order_item.qty_shipped',
]
);
$this->addExpressionFieldToSelect(
'locked',
'SUM(COALESCE(locked_do_ship, 0))',
[
'qty_ordered' => 'order_item.qty_ordered',
'qty_shipped' => 'order_item.qty_shipped',
]
);
$select->group(['main_table.entity_id']);
$select->having('qty_to_ship > 0 AND locked = 0');
$select->where('pickup_location.pickup_location_id IS NULL');
}
/**
* Retain GROUP BY, use sub-select for counting.
*
* @return \Magento\Framework\DB\Select
*/
public function getSelectCountSql()
{
$this->_renderFilters();
$select = clone $this->getSelect();
$select->reset(\Magento\Framework\DB\Select::ORDER);
$select->reset(\Magento\Framework\DB\Select::LIMIT_COUNT);
$select->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET);
$countSelect = $this->_conn->select();
$countSelect->from(['s' => $select]);
$countSelect->reset(\Magento\Framework\DB\Select::COLUMNS);
$countSelect->columns(new \Zend_Db_Expr('COUNT(*)'));
return $countSelect;
}
}