source: WineTrackerFinal/WineTrackerWebApi/Helpers/ModelValidation.cs@ 17d6948

using Dapper;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Npgsql;
using WineTrackerWebApi.Models.Employee;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace WineTrackerWebApi.Helpers
{
    public static class ModelValidation
    {
        /// <summary>
        /// Checks if an entity exists in the database with the same name as the one passed in.
        /// The entity name should be unique.
        /// </summary>
        /// <returns></returns>
        public static async Task DoesEntityExistByName(NpgsqlConnection db, NpgsqlTransaction transaction, string dbTable, string valueToCheck, string columnName = "", int entityId = 0)
        {
            var parameters = new { valueToCheck = valueToCheck.ToLower() };

            string EntityExistsinDbQuery = $@"select count(*) from {dbTable} where 
                                              lower({(string.IsNullOrEmpty(columnName) ? dbTable + "_name" : columnName)}) = @valueToCheck 
                                              {(entityId != 0 ? $"and {dbTable}_id != {entityId}":"")}";

            var doesEntityExistInDb = await db.QueryFirstAsync<int>(EntityExistsinDbQuery, parameters, transaction: transaction);

            if (doesEntityExistInDb > 0)
                throw new Exception("The entity with that Name already exists.");
        }

        /// <summary>
        /// Checks if an employee exists in the database with the same name and surname as the one passed in.
        /// The employee name and surname should be unique.
        /// </summary>
        /// <returns></returns>
        public static async Task DoesEmployeeExist(NpgsqlConnection db, NpgsqlTransaction transaction, string name, string surname, int employeeId = 0)
        {
            var parameters = new { name = name.ToLower(), surname = surname.ToLower() };

            string EntityExistsinDbQuery = $@"select count(*) from Employee where lower(employee_name) = @name and lower(employee_surname) = @surname
                                                {(employeeId != 0 ? $"and employee_id != {employeeId}" : "")}";

            var doesEntityExistInDb = await db.QueryFirstAsync<int>(EntityExistsinDbQuery, parameters, transaction: transaction);

            if (doesEntityExistInDb > 0)
                throw new Exception("The Employee with the given Name and Surname already exists.");
        }

        /// <summary>
        /// Checks if the vehicle details already exist.
        /// The entity name should be unique.
        /// </summary>
        /// <returns></returns>
        public static async Task DoVehicleDetailsExist(NpgsqlConnection db, NpgsqlTransaction transaction, string make, string model, int capacity, int vehicleDetailsId = 0)
        {
            var parameters = new { make = make.ToLower(), model = model.ToLower(), capacity = capacity };

            string EntityExistsinDbQuery = $@"select count(*) from vehicle_details where lower(make) = @make and lower(model) = @model and capacity = @capacity                                     
                                                                        {(vehicleDetailsId != 0 ? $"and vehicle_details_id != {vehicleDetailsId}" : "")}";

            var doesEntityExistInDb = await db.QueryFirstAsync<int>(EntityExistsinDbQuery, parameters, transaction: transaction);

            if (doesEntityExistInDb > 0)
                throw new Exception("The Vehicle Details already exist.");
        }

        /// <summary>
        /// Checks if the vehicle is already booked for the day.
        /// </summary>
        /// <param name="db"></param>
        /// <param name="transaction"></param>
        /// <param name="vehicleId"></param>
        /// <param name="shipmentDate"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static async Task IsVehicleAlreadyBooked(NpgsqlConnection db, NpgsqlTransaction transaction, int vehicleId, DateTime shipmentDate, int entityId = 0)
        {
            var parameters = new { vehicleId = vehicleId, shipmentDate = shipmentDate };

            string EntityExistsinDbQuery = $@"select count(*) from shipment where Vehicle_Id = @VehicleId and Shipment_Date = @ShipmentDate 
                                                {(entityId != 0 ? $"and shipment_id != {entityId}" : "")}";

            var doesEntityExistInDb = await db.QueryFirstAsync<int>(EntityExistsinDbQuery, parameters, transaction: transaction);

            if (doesEntityExistInDb > 0)
                throw new Exception("The Vehicle is already booked for the choosen Date. Please choose another vehicle or change the Date.");
        }
    }
}