RestaurantApp/OrderRepository.cs

using System;
using System.Collections.Generic;
using System.Data;
using Npgsql;
using RestaurantApp.Models;

namespace RestaurantApp.DAL
{
    public class OrderRepository
    {
        public List<Order> GetAll(string statusFilter = "", string search = "")
        {
            var list = new List<Order>();
            using (var conn = Database.GetConnection())
            {
                var sql = @"SELECT o.order_id, o.table_id,
                           h.name||' стол №'||dt.table_number as table_info,
                           o.employee_id,
                           e.last_name||' '||e.first_name as emp_name,
                           o.client_id,
                           COALESCE(c.last_name||' '||c.first_name, '') as client_name,
                           o.opened_at, o.closed_at, o.status,
                           o.discount_pct, o.total_amount
                    FROM ""order"" o
                    JOIN dining_table dt ON dt.table_id = o.table_id
                    JOIN hall h ON h.hall_id = dt.hall_id
                    JOIN employee e ON e.employee_id = o.employee_id
                    LEFT JOIN client c ON c.client_id = o.client_id
                    WHERE 1=1 ";

                if (!string.IsNullOrWhiteSpace(statusFilter) && statusFilter != "Все")
                    sql += " AND o.status = @st";

                if (!string.IsNullOrWhiteSpace(search))
                    sql += " AND (CAST(o.order_id AS TEXT) LIKE @s OR LOWER(e.last_name) LIKE LOWER(@s) OR LOWER(c.last_name) LIKE LOWER(@s))";

                sql += " ORDER BY o.opened_at DESC LIMIT 500";

                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    if (!string.IsNullOrWhiteSpace(statusFilter) && statusFilter != "Все")
                        cmd.Parameters.AddWithValue("st", statusFilter);
                    if (!string.IsNullOrWhiteSpace(search))
                        cmd.Parameters.AddWithValue("s", $"%{search}%");

                    using (var r = cmd.ExecuteReader())
                        while (r.Read()) list.Add(MapOrder(r));
                }
            }
            return list;
        }

        public List<OrderItem> GetItems(int orderId)
        {
            var list = new List<OrderItem>();
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"SELECT oi.order_item_id,oi.order_id,oi.dish_id,d.name,
                         oi.quantity,oi.unit_price,oi.notes,oi.status
                  FROM order_item oi JOIN dish d ON d.dish_id=oi.dish_id
                  WHERE oi.order_id=@id ORDER BY oi.order_item_id", conn))
            {
                cmd.Parameters.AddWithValue("id", orderId);
                using (var r = cmd.ExecuteReader())
                    while (r.Read())
                        list.Add(new OrderItem
                        {
                            OrderItemId = r.GetInt32(0),
                            OrderId = r.GetInt32(1),
                            DishId = r.GetInt32(2),
                            DishName = r.GetString(3),
                            Quantity = r.GetInt32(4),
                            UnitPrice = r.GetDecimal(5),
                            Notes = r.IsDBNull(6) ? "" : r.GetString(6),
                            Status = r.GetString(7)
                        });
            }
            return list;
        }

        public int InsertOrder(Order o)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"INSERT INTO ""order""(table_id,employee_id,client_id,status,discount_pct,total_amount)
                  VALUES(@t,@e,@c,'open',@d,0) RETURNING order_id", conn))
            {
                cmd.Parameters.AddWithValue("t", o.TableId);
                cmd.Parameters.AddWithValue("e", o.EmployeeId);
                cmd.Parameters.AddWithValue("c", o.ClientId.HasValue ? (object)o.ClientId.Value : DBNull.Value);
                cmd.Parameters.AddWithValue("d", o.DiscountPct);
                return (int)cmd.ExecuteScalar();
            }
        }

        public void AddItem(OrderItem item)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"INSERT INTO order_item(order_id,dish_id,quantity,unit_price,notes,status)
                  VALUES(@o,@d,@q,@p,@n,'ordered')", conn))
            {
                cmd.Parameters.AddWithValue("o", item.OrderId);
                cmd.Parameters.AddWithValue("d", item.DishId);
                cmd.Parameters.AddWithValue("q", item.Quantity);
                cmd.Parameters.AddWithValue("p", item.UnitPrice);
                cmd.Parameters.AddWithValue("n", string.IsNullOrEmpty(item.Notes) ? (object)DBNull.Value : item.Notes);
                cmd.ExecuteNonQuery();
            }
        }

        public void UpdateOrderStatus(int orderId, string status)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"UPDATE ""order"" SET status=@s, closed_at=CASE WHEN @s IN ('closed','cancelled') THEN NOW() ELSE closed_at END
                  WHERE order_id=@id", conn))
            {
                cmd.Parameters.AddWithValue("s", status);
                cmd.Parameters.AddWithValue("id", orderId);
                cmd.ExecuteNonQuery();
            }
        }

        public void UpdateItemStatus(int itemId, string status)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                "UPDATE order_item SET status=@s WHERE order_item_id=@id", conn))
            {
                cmd.Parameters.AddWithValue("s", status);
                cmd.Parameters.AddWithValue("id", itemId);
                cmd.ExecuteNonQuery();
            }
        }

        public void DeleteItem(int itemId)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                "UPDATE order_item SET status='cancelled' WHERE order_item_id=@id", conn))
            {
                cmd.Parameters.AddWithValue("id", itemId);
                cmd.ExecuteNonQuery();
            }
        }

        public void AddPayment(int orderId, decimal amount, string method)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                "INSERT INTO payment(order_id,amount,method) VALUES(@o,@a,@m)", conn))
            {
                cmd.Parameters.AddWithValue("o", orderId);
                cmd.Parameters.AddWithValue("a", amount);
                cmd.Parameters.AddWithValue("m", method);
                cmd.ExecuteNonQuery();
            }
        }

        public DataTable GetTables()
        {
            var dt = new DataTable();
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"SELECT dt.table_id, h.name||' — стол №'||dt.table_number||' ('||dt.seats||' мест)' as info
                  FROM dining_table dt JOIN hall h ON h.hall_id=dt.hall_id
                  WHERE dt.is_active=true ORDER BY h.name, dt.table_number", conn))
            using (var da = new NpgsqlDataAdapter(cmd))
                da.Fill(dt);
            return dt;
        }

        private Order MapOrder(NpgsqlDataReader r) => new Order
        {
            OrderId = r.GetInt32(0),
            TableId = r.GetInt32(1),
            TableInfo = r.GetString(2),
            EmployeeId = r.GetInt32(3),
            EmployeeName = r.GetString(4),
            ClientId = r.IsDBNull(5) ? (int?)null : r.GetInt32(5),
            ClientName = r.GetString(6),
            OpenedAt = r.GetDateTime(7),
            ClosedAt = r.IsDBNull(8) ? (DateTime?)null : r.GetDateTime(8),
            Status = r.GetString(9),
            DiscountPct = r.GetDecimal(10),
            TotalAmount = r.IsDBNull(11) ? 0 : r.GetDecimal(11)
        };
    }
}