protected function getSelectFilter()
{
$filter = parent::getSelectFilter();
$id = $this->request->get('id');
if (!is_numeric($id)) {
$id = (int) substr($id, 6);
}
if ($id > 0) {
$filter->mergeCondition(new EqualsCond(new ARFieldHandle('User', 'userGroupID'), $id));
} else {
if ($id == -1) {
// without group
$filter->mergeCondition(new IsNullCond(new ARFieldHandle('User', 'userGroupID')));
} else {
if ($id == -3) {
// online
$filter->mergeHavingCondition(new EqualsOrMoreCond(f('isOnline'), 1));
}
}
}
$filter->addField('(SELECT COUNT(*) > 0 From SessionData WHERE userID=User.ID)', '', 'isOnline');
return $filter;
}
protected function getSelectFilter()
{
$filter = parent::getSelectFilter();
$id = $this->request->get('id');
if (!is_numeric($id)) {
list($foo, $id) = explode('_', $this->request->get('id'));
}
$cond = $this->getTypeCondition($id);
$this->applyFullNameFilter($cond);
$this->applyStateFilter($cond);
$filters = $this->request->get('filters');
$displayedColumns = $this->getDisplayedColumns();
$columns = array_merge(array_keys(is_array($filters) ? $filters : array()), array_keys(is_array($displayedColumns) ? $displayedColumns : array()));
if (in_array('ProductCount', $columns)) {
$filter->addField('(SELECT sum(count) AS ProductCount FROM OrderedItem AS oi WHERE oi.customerOrderID = CustomerOrder.ID)', '', 'ProductCount');
}
if (in_array('UniqueProductCount', $columns)) {
$filter->addField('(SELECT count(*) AS UniqueProductCount FROM OrderedItem AS oi WHERE oi.customerOrderID = CustomerOrder.ID)', '', 'UniqueProductCount');
}
if (in_array('HasUsedCoupon', $columns)) {
$filter->addField('(
SELECT
IF(COUNT(*),1,0) AS HasUsedCoupon
FROM
OrderCoupon AS oc
WHERE
oc.orderID = CustomerOrder.ID
)', '', 'HasUsedCoupon');
}
if (in_array('UsedCouponCount', $columns)) {
$filter->addField('(SELECT count(*) AS UsedCoupon FROM OrderCoupon AS oc WHERE oc.orderID = CustomerOrder.ID)', '', 'UsedCouponCount');
}
if (in_array('ProductSKU', $columns) && !empty($filters['ProductSKU'])) {
$filter->addField('(SELECT 0x' . bin2hex($filters['ProductSKU']) . ' AS ProductSKU FROM OrderedItem AS oi INNER JOIN Product pr on pr.ID=oi.productID AND pr.sku LIKE 0x' . bin2hex('%' . $filters['ProductSKU'] . '%') . ' WHERE oi.customerOrderID = CustomerOrder.ID Limit 1)', '', 'ProductSKU');
}
if (in_array('ProductName', $columns) && !empty($filters['ProductName'])) {
$filter->addField('(SELECT 0x' . bin2hex($filters['ProductName']) . ' AS ProductSKU FROM OrderedItem AS oi INNER JOIN Product pr on pr.ID=oi.productID AND pr.name LIKE 0x' . bin2hex('%' . $filters['ProductName'] . '%') . ' WHERE oi.customerOrderID = CustomerOrder.ID Limit 1)', '', 'ProductName');
}
if (in_array('Manufacturer', $columns) && !empty($filters['Manufacturer'])) {
$filter->addField('(
SELECT
0x' . bin2hex($filters['Manufacturer']) . ' AS Manufacturer
FROM
OrderedItem AS oi
INNER JOIN Product pr on pr.ID=oi.productID
INNER JOIN Manufacturer mf on mf.ID=pr.manufacturerID AND mf.name LIKE 0x' . bin2hex('%' . $filters['Manufacturer'] . '%') . '
WHERE
oi.customerOrderID = CustomerOrder.ID Limit 1
)', '', 'Manufacturer');
}
if (in_array('UsedCouponCode', $columns) && !empty($filters['UsedCouponCode'])) {
$filter->addField('(
SELECT
0x' . bin2hex($filters['UsedCouponCode']) . ' AS UsedCoupon
FROM
OrderCoupon AS oc
WHERE
oc.couponCode LIKE 0x' . bin2hex('%' . $filters['UsedCouponCode'] . '%') . '
AND oc.orderID = CustomerOrder.ID
)', '', 'UsedCouponCode');
}
if (in_array('ProductOption', $columns) && !empty($filters['ProductOption'])) {
$filter->addField('(
SELECT
0x' . bin2hex($filters['ProductOption']) . ' AS ProductOption
FROM
OrderedItemOption
LEFT JOIN OrderedItem ON OrderedItem.ID=OrderedItemOption.orderedItemID
LEFT JOIN ProductOptionChoice ON OrderedItemOption.choiceID=ProductOptionChoice.ID
WHERE
OrderedItem.customerOrderID=CustomerOrder.ID
AND (
(OrderedItemOption.optionText LIKE 0x' . bin2hex('%' . $filters['ProductOption'] . '%') . ')
OR
(ProductOptionChoice.name LIKE 0x' . bin2hex('%' . $filters['ProductOption'] . '%') . ' )
)
)', '', 'ProductOption');
}
if (in_array('OrderMessageText', $columns) && !empty($filters['OrderMessageText'])) {
$filter->addField('(
SELECT
0x' . bin2hex($filters['OrderMessageText']) . ' AS OrderMessageText
FROM
OrderNote AS o
WHERE
o.text LIKE 0x' . bin2hex('%' . $filters['OrderMessageText'] . '%') . '
AND o.orderID = CustomerOrder.ID
)', '', 'OrderMessageText');
}
if (in_array('HasUnreadCustomerMessage', $columns)) {
$filter->addField('(
SELECT
IF(COUNT(*),1,0) AS HasUnreadCustomerMessage
FROM
OrderNote AS o
WHERE
o.isRead=0 AND
o.orderID = CustomerOrder.ID
)', '', 'HasUnreadCustomerMessage');
}
if (in_array('HasUnrespondedCustomerMessage', $columns)) {
$filter->addField('(
SELECT
o.isAdmin AS HasUnrespondedCustomerMessage
FROM
OrderNote AS o
WHERE
o.orderID = CustomerOrder.ID
ORDER BY time DESC
LIMIT 1
)', '', 'HasUnrespondedCustomerMessage');
}
if ($this->request->get('sort_col') == 'User.fullName') {
$this->request->remove('sort_col');
$direction = $this->request->get('sort_dir') == 'DESC' ? ARSelectFilter::ORDER_DESC : ARSelectFilter::ORDER_ASC;
$filter->setOrder(new ARFieldHandle("User", "lastName"), $direction);
$filter->setOrder(new ARFieldHandle("User", "firstName"), $direction);
}
if ($this->request->get('userID')) {
$cond->addAND(new EqualsCond(new ARFieldHandle('CustomerOrder', 'userID'), $this->request->get('userID')));
}
$filter->setCondition($cond);
return $filter;
}
