Inventory/AvaloniaApplication14_Inventory_300326/Models/DataBase/EquipmentRepository.cs

using System;
using System.Collections.Generic;
using AvaloniaApplication14_autoTest_190326.Models;
using AvaloniaApplication14_Di_test_1125.Models;
using AvaloniaApplication14_Inventory_300326.Models.Models;
using Microsoft.Extensions.Options;
using MySqlConnector;

namespace AvaloniaApplication14_Inventory_300326.Models.DataBase;

public class EquipmentRepository : BaseRepository<Equipment>, IDisposable
{
    public EquipmentRepository(IOptions<DatabaseSettings> ConnectionString) : base(ConnectionString)
    {
        OpenConnection();
    }

    public override List<Equipment>? GetAll()
    {
        string sql = "select * from `Equipment`";
        List<Equipment> result = new List<Equipment>();
        try
        {
            using var mc = new MySqlCommand(sql, connection);
            mc.ExecuteNonQuery();
            using var reader = mc.ExecuteReader();
            while (reader.Read())
            {
                Equipment equipment = new Equipment()
                {
                    Id = reader.GetInt32("Id"),
                    Name = reader.GetString("Name"),
                    InvNumber = reader.GetString("InvNumber"),
                    Date = reader.GetDateOnly("PurchaseDate"),
                    Cost = reader.GetDecimal("Cost"),
                    IsWrittenOff = reader.GetBoolean("IsWrittenOff"),
                    CurrentEmployeeId = reader.GetInt32("CurrentEmployeeId")
                };
                result.Add(equipment);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }
        return result;
    }

    public override Equipment? GetById(int id)
    {
        throw new NotImplementedException();
    }

    public override bool Delete(int id)
    {
        throw new NotImplementedException();
    }

    public override bool Update(Equipment item)
    {
        string sql = "UPDATE TechInventory.Equipment SET InvNumber=@InvNum, Name=@Name, PurchaseDate=@Date, Cost=@Cost, IsWrittenOff=@IsWrittenOff, CurrentEmployeeId=@CurrentEmployeeId WHERE Id=@Id;";
        try
        {
            using var mc = new MySqlCommand(sql, connection);
            mc.Parameters.AddWithValue("@Id", item.Id);
            mc.Parameters.AddWithValue("@InvNum", item.InvNumber);
            mc.Parameters.AddWithValue("@Name", item.Name);
            mc.Parameters.AddWithValue("@Date", item.Date);
            mc.Parameters.AddWithValue("@Cost", item.Cost);
            mc.Parameters.AddWithValue("@IsWrittenOff", item.IsWrittenOff?1:0);
            mc.Parameters.AddWithValue("@CurrentEmployeeId", item.CurrentEmployeeId);
            mc.ExecuteNonQuery();

            return true;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            return false;
        }
    }

    public List<Equipment> DoesEmployeeHaveEquipments(int EmployeeId)
    {
        List<Equipment> result = new List<Equipment>();
        string sql = "select * from `Equipment` where CurrentEmployeeId=@CurrentEmployeeId";
        try
        {
            using (var mc = new MySqlCommand(sql, connection))
            {
                mc.Parameters.AddWithValue("@CurrentEmployeeId", EmployeeId);
                using (var reader = mc.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Equipment item = new Equipment()
                        {
                            Id = reader.GetInt32("Id"),
                            Name = reader.GetString("Name"),
                            InvNumber = reader.GetString("InvNumber"),
                            Date = reader.GetDateOnly("PurchaseDate"),
                            Cost = reader.GetDecimal("Cost"),
                            IsWrittenOff = reader.GetBoolean("IsWrittenOff"),
                            CurrentEmployeeId = reader.GetInt32("CurrentEmployeeId")
                        };
                        result.Add(item);
                    }
                }
            }
            return result;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            return null;
        }
    }
    public override bool Add(Equipment item)
    {
        string sql = "INSERT INTO TechInventory.Equipment (InvNumber, Name, PurchaseDate, Cost, IsWrittenOff, CurrentEmployeeId) VALUES(@InvNum, @Name, @Date, @Cost, @IsWrittenOff, @CurrentEmployeeId)";
        try
        {
            var mc = new MySqlCommand(sql, connection);
            mc.Parameters.AddWithValue("@InvNum", item.InvNumber);
            mc.Parameters.AddWithValue("@Name", item.Name);
            mc.Parameters.AddWithValue("@Date", item.Date);
            mc.Parameters.AddWithValue("@Cost", item.Cost);
            mc.Parameters.AddWithValue("@IsWrittenOff", item.IsWrittenOff);
            mc.Parameters.AddWithValue("@CurrentEmployeeId", item.CurrentEmployeeId);
            mc.ExecuteNonQuery();
            
            return true;
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            return false;
        }
    }

    public bool ValidateInvNumber(string invNumber)
    {
        string sql = "select * from `Equipment` where InvNumber=@invNumber";
        using(var mc = new MySqlCommand(sql, connection))
        {
            mc.Parameters.AddWithValue("@invNumber", invNumber);
            using (var reader = mc.ExecuteReader())
            {
                while (reader.Read())
                {
                    return false;
                }
            }
        }
        return true;
    }
}