public function query($params) { $query = parent::query($params); $query->addSelect("\"Discount\".*")->selectField("\"Title\"", "Name")->selectField("COUNT(DISTINCT \"Order\".\"ID\")", 'Entered')->addLeftJoin("Product_OrderItem_Discounts", "\"Product_OrderItem_Discounts\".\"DiscountID\" = \"Discount\".\"ID\"")->addLeftJoin("OrderDiscountModifier_Discounts", "\"OrderDiscountModifier_Discounts\".\"DiscountID\" = \"Discount\".\"ID\"")->addInnerJoin("OrderAttribute", implode(" OR ", array("\"Product_OrderItem_Discounts\".\"Product_OrderItemID\" = \"OrderAttribute\".\"ID\"", "\"OrderDiscountModifier_Discounts\".\"OrderDiscountModifierID\" = \"OrderAttribute\".\"ID\"")))->addInnerJoin("Order", "\"OrderAttribute\".\"OrderID\" = \"Order\".\"ID\""); $query->setGroupBy("\"Discount\".\"ID\""); $query->setLimit("50"); return $query; }
public function query($params) { $query = parent::query($params); $query->selectField($this->periodfield, "FilterPeriod")->addSelect("SearchLog.Query")->selectField("Count(SearchLog.ID)", "Quantity")->selectField("Max(SearchLog.Created)", "MostRecent")->selectField("Max(SearchLog.NumResults)", "NumResults"); $query->addGroupby("SearchLog.Query"); $query->addWhere("\"SearchLog\".\"Filters\" is null AND \"SearchLog\".\"ParentSearchID\" = '0'"); $query->setOrderBy("Quantity", "DESC"); return $query; }
public function query($params) { $query = parent::query($params); $query->selectField($this->periodfield, "FilterPeriod")->addSelect(array("Member.ID", "Member.FirstName", "Member.Surname", "Member.Email", "NumVisit", "Member.Created"))->selectField("Count(Order.ID)", "Orders")->selectField("Sum(Order.Total)", "Spent"); $query->addInnerJoin("Order", "Member.ID = Order.MemberID"); $query->addGroupBy("Member.ID"); if (!$query->getOrderBy()) { $query->setOrderBy("Spent DESC,Orders DESC"); } $query->setLimit("50"); return $query; }
public function query($params) { $query = parent::query($params); $query->selectField($this->periodfield, "FilterPeriod")->addSelect(array("Product.ID", "SiteTree.ClassName", "SiteTree.Title", "Product.BasePrice", "SiteTree.Created"))->selectField("Count(OrderItem.Quantity)", "Quantity")->selectField("Sum(OrderAttribute.CalculatedTotal)", "Sales"); $query->addInnerJoin("SiteTree", "Product.ID = SiteTree.ID"); $query->addLeftJoin("Product_OrderItem", "Product.ID = Product_OrderItem.ProductID"); $query->addLeftJoin("OrderItem", "Product_OrderItem.ID = OrderItem.ID"); $query->addLeftJoin("OrderAttribute", "Product_OrderItem.ID = OrderAttribute.ID"); $query->addLeftJoin("Order", "OrderAttribute.OrderID = Order.ID"); $query->addGroupby("Product.ID"); $query->addWhere("\"Order\".\"Paid\" IS NOT NULL OR \"Product_OrderItem\".\"ID\" IS NULL"); return $query; }
public function query($params) { return parent::query($params)->selectField("Count(\"Order\".\"ID\")", "Count")->selectField("Sum(\"Order\".\"Total\")", "TotalValue")->addWhere("\"Status\" = 'Cart'"); }
public function query($params) { return parent::query($params)->selectField("Count(\"Order\".\"ID\")", "Count")->selectField("Sum(\"Order\".\"Total\")", "Sales"); }
public function query($params) { return parent::query($params)->addInnerJoin("OrderAttribute", "OrderAttribute.OrderID = Order.ID AND OrderAttribute.ClassName like '%TaxModifier'")->addInnerJoin("OrderModifier", "OrderModifier.ID = OrderAttribute.ID")->selectField("Count(\"Order\".\"ID\")", "Count")->selectField("Sum(\"OrderModifier\".\"Amount\")", "Tax")->selectField("Sum(\"Order\".\"Total\")", "Sales"); }