/**
* @param Outlet $outlet
* @return array
*/
public function getMenuInOutlet(Outlet $outlet)
{
$this->assert(intval($outlet->getId()) > 0);
$sql = "SELECT" . "\n m.id AS menu_id,\n m.name AS menu_name,\n m.serving_unit AS serving_unit,\n mp.id AS price_id,\n mp.price AS price,\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 menus m\n LEFT JOIN (\n SELECT mp.id, mp.menu_id, mp.price\n FROM menu_prices mp\n WHERE mp.outlet_id = ?\n GROUP BY mp.id\n ) mp ON mp.menu_id = m.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 m.id\n ";
$stmt = $this->db()->prepare($sql);
$stmt->bindValue(1, $outlet->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);
}
/**
* @param Outlet $outlet
* @return array
*/
private function findMenuInOutlet(Outlet $outlet)
{
return $this->mapper(MenuPrice::class)->filter(function (QueryBuilder $builder) use($outlet) {
$builder->addSelect('m, o');
$builder->leftJoin('mp.menu', 'm');
$builder->leftJoin('mp.outlet', 'o');
$builder->where('o.id = :id and m.status = :status');
$builder->setParameter('id', $outlet->getId());
$builder->setParameter('status', Menu::STATUS_ACTIVE);
});
}
/**
* @return int|null
*/
public function getRecordIdOrNull()
{
return $this->existingRecord ? $this->existingRecord->getId() : null;
}
/**
* @param Outlet $outlet
* @throws \Doctrine\DBAL\DBALException
*/
public function addAllMenu(Outlet $outlet)
{
$this->assert(intval($outlet->getId()) > 0);
$sql = "insert" . " into menu_prices (outlet_id, menu_id, price)\n select %d, id, default_price\n from menus";
$this->db()->executeQuery(sprintf($sql, $outlet->getId()));
}