/**
* @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);
}
/**
* @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;
}