RestaurantApp/ReportRepository.cs

using System;
using System.Data;
using Npgsql;

namespace RestaurantApp.DAL
{
    public class ReportRepository
    {
        public DataTable GetDailyRevenue(DateTime dateFrom, DateTime dateTo)
        {
            var dt = new DataTable();
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"SELECT date(paid_at) as ""Дата"",
                         COUNT(*) as ""Платежей"",
                         SUM(amount) as ""Выручка (руб.)"",
                         AVG(amount) as ""Средний чек (руб.)""
                  FROM payment
                  WHERE paid_at::date BETWEEN @df AND @dt
                  GROUP BY date(paid_at) ORDER BY date(paid_at) DESC", conn))
            {
                cmd.Parameters.AddWithValue("df", dateFrom.Date);
                cmd.Parameters.AddWithValue("dt", dateTo.Date);
                using (var da = new NpgsqlDataAdapter(cmd)) da.Fill(dt);
            }
            return dt;
        }

        public DataTable GetDishSales(DateTime dateFrom, DateTime dateTo)
        {
            var dt = new DataTable();
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"SELECT d.name as ""Блюдо"",
                         dc.name as ""Категория"",
                         SUM(oi.quantity) as ""Кол-во"",
                         SUM(oi.quantity*oi.unit_price) as ""Выручка (руб.)""
                  FROM order_item oi
                  JOIN dish d ON d.dish_id=oi.dish_id
                  JOIN dish_category dc ON dc.category_id=d.category_id
                  JOIN ""order"" o ON o.order_id=oi.order_id
                  WHERE oi.status NOT IN ('cancelled')
                    AND o.opened_at::date BETWEEN @df AND @dt
                  GROUP BY d.name, dc.name
                  ORDER BY SUM(oi.quantity) DESC", conn))
            {
                cmd.Parameters.AddWithValue("df", dateFrom.Date);
                cmd.Parameters.AddWithValue("dt", dateTo.Date);
                using (var da = new NpgsqlDataAdapter(cmd)) da.Fill(dt);
            }
            return dt;
        }

        public DataTable GetWaiterStats(DateTime dateFrom, DateTime dateTo)
        {
            var dt = new DataTable();
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"SELECT e.last_name||' '||e.first_name as ""Официант"",
                         COUNT(DISTINCT o.order_id) as ""Заказов"",
                         SUM(o.total_amount) as ""Сумма (руб.)"",
                         AVG(o.total_amount) as ""Средний заказ (руб.)""
                  FROM ""order"" o
                  JOIN employee e ON e.employee_id=o.employee_id
                  WHERE o.status='closed'
                    AND o.opened_at::date BETWEEN @df AND @dt
                  GROUP BY e.last_name, e.first_name
                  ORDER BY SUM(o.total_amount) DESC", conn))
            {
                cmd.Parameters.AddWithValue("df", dateFrom.Date);
                cmd.Parameters.AddWithValue("dt", dateTo.Date);
                using (var da = new NpgsqlDataAdapter(cmd)) da.Fill(dt);
            }
            return dt;
        }

        public DataTable GetPaymentMethods(DateTime dateFrom, DateTime dateTo)
        {
            var dt = new DataTable();
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"SELECT method as ""Способ оплаты"",
                         COUNT(*) as ""Кол-во"",
                         SUM(amount) as ""Сумма (руб.)"",
                         ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER(),1) as ""%""
                  FROM payment
                  WHERE paid_at::date BETWEEN @df AND @dt
                  GROUP BY method ORDER BY SUM(amount) DESC", conn))
            {
                cmd.Parameters.AddWithValue("df", dateFrom.Date);
                cmd.Parameters.AddWithValue("dt", dateTo.Date);
                using (var da = new NpgsqlDataAdapter(cmd)) da.Fill(dt);
            }
            return dt;
        }

        public DataTable GetLowStock()
        {
            var dt = new DataTable();
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"SELECT i.name as ""Ингредиент"",
                         i.unit as ""Ед. изм."",
                         i.stock_qty as ""Остаток"",
                         i.min_stock as ""Минимум"",
                         COALESCE(s.name,'') as ""Поставщик""
                  FROM ingredient i
                  LEFT JOIN supplier s ON s.supplier_id=i.supplier_id
                  WHERE i.stock_qty < i.min_stock
                  ORDER BY (i.stock_qty - i.min_stock)", conn))
            using (var da = new NpgsqlDataAdapter(cmd)) da.Fill(dt);
            return dt;
        }
    }
}