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; }