Beispiel #1
0
 /**
  * @param Menu $menu
  * @return array
  * @todo remove redundant join `LEFT JOIN menus m ON m.id = mp.menu_id`
  */
 public function getPricePerOutlet(Menu $menu)
 {
     $this->assert(intval($menu->getId()) > 0);
     $sql = "SELECT" . "\n            o.id AS outlet_id,\n            o.name AS outlet_name,\n            mp.id AS price_id,\n            mp.price AS price,\n            mp.serving_unit AS serving_unit,\n            SUM(ms.sale_quantity) AS sale_quantity,\n            SUM(ms.sale_subtotal) AS sale_subtotal,\n            SUM(ms.sale_total) AS sale_total\n        FROM outlets o\n        LEFT JOIN (\n            SELECT mp.id, mp.outlet_id, mp.price, m.serving_unit\n            FROM  menu_prices mp\n            LEFT JOIN menus m ON m.id = mp.menu_id\n            WHERE m.id = ?\n            GROUP BY mp.id\n        ) mp ON mp.outlet_id = o.id\n        LEFT JOIN (\n            SELECT ms.id AS menu_sale_id,\n                ms.menu_price_id AS menu_price_id,\n                si.quantity AS sale_quantity,\n                si.subtotal AS sale_subtotal,\n                si.total AS sale_total\n            FROM  menu_sales ms\n            LEFT JOIN sale_items si ON si.id = ms.sale_item_id\n            LEFT JOIN sales s ON s.id = si.sale_id\n            WHERE s.ordered_at BETWEEN ? AND ?\n            GROUP BY ms.id\n        ) ms ON ms.menu_price_id = mp.id\n        GROUP BY o.id\n        ";
     $stmt = $this->db()->prepare($sql);
     $stmt->bindValue(1, $menu->getId());
     $today = new \DateTime();
     $last30days = clone $today;
     $last30days->modify('-30 days');
     $stmt->bindValue(2, $last30days->format('Y-m-d 00:00:00'));
     $stmt->bindValue(3, $today->format('Y-m-d 23:59:59'));
     $stmt->execute();
     return $stmt->fetchAll(\PDO::FETCH_ASSOC);
 }
Beispiel #2
0
 /**
  * @return array
  */
 public function getParentMenuOptions()
 {
     $builder = $this->em()->createQueryBuilder()->select('m')->from(Menu::class, 'm')->where('m.parent is null');
     if ($this->existingRecord) {
         $builder->andWhere('m.id <> :id');
         $builder->setParameter('id', $this->existingRecord->getId());
     }
     /** @var Menu[] $results */
     $results = $builder->getQuery()->getResult();
     $options = [];
     foreach ($results as $result) {
         $options[] = $result->getId();
     }
     return $options;
 }