RestaurantApp/ReservationRepository.cs

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

namespace RestaurantApp.DAL
{
    public class ReservationRepository
    {
        public List<Reservation> GetAll(string statusFilter = "", DateTime? dateFrom = null, DateTime? dateTo = null)
        {
            var list = new List<Reservation>();
            using (var conn = Database.GetConnection())
            {
                var sql = @"SELECT r.reservation_id, r.table_id,
                           h.name||' стол №'||dt.table_number as table_info,
                           r.client_id, COALESCE(c.last_name||' '||c.first_name,'') as client_name,
                           r.employee_id, COALESCE(e.last_name||' '||e.first_name,'') as emp_name,
                           r.reserved_at, r.duration_min, r.guests_count,
                           r.status, r.notes, r.created_at
                    FROM reservation r
                    JOIN dining_table dt ON dt.table_id = r.table_id
                    JOIN hall h ON h.hall_id = dt.hall_id
                    LEFT JOIN client c ON c.client_id = r.client_id
                    LEFT JOIN employee e ON e.employee_id = r.employee_id
                    WHERE 1=1 ";

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

                if (dateFrom.HasValue)
                    sql += " AND r.reserved_at >= @df";
                if (dateTo.HasValue)
                    sql += " AND r.reserved_at <= @dt";

                sql += " ORDER BY r.reserved_at DESC LIMIT 500";

                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    if (!string.IsNullOrWhiteSpace(statusFilter) && statusFilter != "Все")
                        cmd.Parameters.AddWithValue("st", statusFilter);
                    if (dateFrom.HasValue)
                        cmd.Parameters.AddWithValue("df", dateFrom.Value);
                    if (dateTo.HasValue)
                        cmd.Parameters.AddWithValue("dt", dateTo.Value);

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

        public void Insert(Reservation res)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"INSERT INTO reservation(table_id, client_id, employee_id, reserved_at, duration_min, guests_count, status, notes)
          VALUES(@t, @c, @e, @ra, @dur, @g, @st, @n)", conn))
            {
                cmd.Parameters.AddWithValue("t", res.TableId);
                cmd.Parameters.AddWithValue("c", res.ClientId.HasValue ? (object)res.ClientId.Value : DBNull.Value);
                cmd.Parameters.AddWithValue("e", res.EmployeeId.HasValue ? (object)res.EmployeeId.Value : DBNull.Value);
                cmd.Parameters.AddWithValue("ra", res.ReservedAt);
                cmd.Parameters.AddWithValue("dur", res.DurationMin);
                cmd.Parameters.AddWithValue("g", res.GuestsCount);
                cmd.Parameters.AddWithValue("st", res.Status);
                cmd.Parameters.AddWithValue("n", string.IsNullOrEmpty(res.Notes) ? (object)DBNull.Value : res.Notes);
                cmd.ExecuteNonQuery();
            }
        }

        public void Update(Reservation res)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"UPDATE reservation SET table_id=@t, client_id=@c, employee_id=@e, 
          reserved_at=@ra, duration_min=@dur, guests_count=@g, 
          status=@st, notes=@n
          WHERE reservation_id=@id", conn))
            {
                cmd.Parameters.AddWithValue("id", res.ReservationId);
                cmd.Parameters.AddWithValue("t", res.TableId);
                cmd.Parameters.AddWithValue("c", res.ClientId.HasValue ? (object)res.ClientId.Value : DBNull.Value);
                cmd.Parameters.AddWithValue("e", res.EmployeeId.HasValue ? (object)res.EmployeeId.Value : DBNull.Value);
                cmd.Parameters.AddWithValue("ra", res.ReservedAt);
                cmd.Parameters.AddWithValue("dur", res.DurationMin);
                cmd.Parameters.AddWithValue("g", res.GuestsCount);
                cmd.Parameters.AddWithValue("st", res.Status);
                cmd.Parameters.AddWithValue("n", string.IsNullOrEmpty(res.Notes) ? (object)DBNull.Value : res.Notes);
                cmd.ExecuteNonQuery();
            }
        }

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

        private void SetParams(NpgsqlCommand cmd, Reservation res)
        {
            cmd.Parameters.AddWithValue("t", res.TableId);
            cmd.Parameters.AddWithValue("c", res.ClientId.HasValue ? (object)res.ClientId.Value : DBNull.Value);
            cmd.Parameters.AddWithValue("e", res.EmployeeId.HasValue ? (object)res.EmployeeId.Value : DBNull.Value);
            cmd.Parameters.AddWithValue("ra", res.ReservedAt);
            cmd.Parameters.AddWithValue("dur", res.DurationMin);
            cmd.Parameters.AddWithValue("g", res.GuestsCount);
            cmd.Parameters.AddWithValue("st", res.Status);
            cmd.Parameters.AddWithValue("n", string.IsNullOrEmpty(res.Notes) ? (object)DBNull.Value : res.Notes);
        }

        private Reservation MapRes(NpgsqlDataReader r) => new Reservation
        {
            ReservationId = r.GetInt32(0),
            TableId = r.GetInt32(1),
            TableInfo = r.GetString(2),
            ClientId = r.IsDBNull(3) ? (int?)null : r.GetInt32(3),
            ClientName = r.GetString(4),
            EmployeeId = r.IsDBNull(5) ? (int?)null : r.GetInt32(5),
            EmployeeName = r.GetString(6),
            ReservedAt = r.GetDateTime(7),
            DurationMin = r.GetInt32(8),
            GuestsCount = r.GetInt32(9),
            Status = r.GetString(10),
            Notes = r.IsDBNull(11) ? "" : r.GetString(11),
            CreatedAt = r.GetDateTime(12)
        };
    }
}