NonInventPurchasingSystem/CPRNIMS.Domain/Services/Inventory/Inventory.cs

using CPRNIMS.Domain.Contracts.Inventory;
using CPRNIMS.Infrastructure.Database;
using CPRNIMS.Infrastructure.Dto.Inventory;
using CPRNIMS.Infrastructure.Dto.Items;
using CPRNIMS.Infrastructure.Entities.Finance;
using CPRNIMS.Infrastructure.Entities.Inventory;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPRNIMS.Domain.Services.Inventory
{
    public class Inventory : IInventory
    {
        private readonly NonInventoryDbContext _dbContext;
        public Inventory(NonInventoryDbContext dbContext)
        {
            _dbContext = dbContext;
        }
        public async Task<List<Infrastructure.Entities.Inventory.ItemDetail>> GetInventoryById(InventoryDto itemDto)
        {
            try
            {
                var allItems = await _dbContext.ItemDetails
                    .FromSqlRaw($"EXEC GetInventoryById @UserId = '{itemDto.UserId}',@InventoryId = '{itemDto.InventoryId}'")
                    .ToListAsync();

                return allItems ?? new List<Infrastructure.Entities.Inventory.ItemDetail>();
            }
            catch (SqlException ex)
            {
                ex.ToString();
                throw;
            }
        }

        public async Task<List<Infrastructure.Entities.Inventory.Inventory>> 
            GetInventoryByUserId(InventoryDto itemDto)
        {
            try
            {
                if(itemDto.IsSorting == false)
                {
                    itemDto.DateFrom=DateTime.Now;
                    itemDto.DateTo = DateTime.Now;
                }
                var allItems = await _dbContext.Inventories
                    .FromSqlRaw($"EXEC GetInventoryByUserId @UserId,@DateFrom,@DateTo,@IsSorting",
                    new SqlParameter("@UserId", itemDto.UserId),
                    new SqlParameter("@DateFrom", itemDto.DateFrom),
                    new SqlParameter("@DateTo", itemDto.DateTo),
                    new SqlParameter("@IsSorting", itemDto.IsSorting))
                    .ToListAsync();

                return allItems ?? new List<Infrastructure.Entities.Inventory.Inventory>();
            }
            catch (SqlException ex)
            {
                ex.ToString();
                throw;
            }
        }

        public async Task<List<Lot>> GetLotNoById(InventoryDto itemDto)
        {
            try
            {
                var allItems = await _dbContext.Lots
                    .FromSqlRaw($"EXEC GetLotById @UserId = '{itemDto.UserId}'")
                    .ToListAsync();

                return allItems ?? new List<Lot>();
            }
            catch (SqlException ex)
            {
                ex.ToString();
                throw;
            }
        }

        public async Task<List<Lot>> GetLotNo(InventoryDto itemDto)
        {
            var allItems = await _dbContext.Lots
                   .FromSqlRaw("EXEC GetLots @UserId",
                   new SqlParameter("@UserId", itemDto.UserId))
                   .ToListAsync();

            return allItems ?? new List<Lot>();
        }

        public async Task<Lot> PostPutLotNo(InventoryDto itemDto)
        {
            try
            {
                await _dbContext.Database
                   .ExecuteSqlRawAsync("EXEC PostPutLotNo @UserId, @LotId, @LotTypeId,@LotName",
                    new SqlParameter("@LotId", itemDto.LotId != null ? itemDto.LotId : 0L),
                    new SqlParameter("@UserId", itemDto.UserId),
                    new SqlParameter("@LotTypeId", itemDto.LotTypeId),
                     new SqlParameter("@LotName", itemDto.LotName));
                return new Lot();
            }
            catch (SqlException ex)
            {
                ex.ToString();
                throw;
            }
        }

        public async Task<Infrastructure.Entities.Inventory.Inventory> PostPutReqApproval(InventoryDto itemDto)
        {
            try
            {
                await _dbContext.Database
                   .ExecuteSqlRawAsync("EXEC PostPutReqApproval @UserId, @ItemNo, @Status, @Remarks",
                    new SqlParameter("@ItemNo", itemDto.InventoryId != null ? itemDto.InventoryId : 0L),
                    new SqlParameter("@UserId", itemDto.UserId),
                    new SqlParameter("@Status", itemDto.Status),
                    new SqlParameter("@Remarks", itemDto.Remarks ?? "N/A"));
                return new Infrastructure.Entities.Inventory.Inventory();
            }
            catch (SqlException ex)
            {
                ex.ToString();
                throw;
            }
        }

        public async Task<ItemDetail> PostPutLotBin(InventoryDto itemDto)
        {
            try
            {
                await _dbContext.Database
                   .ExecuteSqlRawAsync("EXEC PostPutLotBin @UserId, @LotId, @InventoryId",
                    new SqlParameter("@InventoryId", itemDto.InventoryId),
                    new SqlParameter("@UserId", itemDto.UserId),
                    new SqlParameter("@LotId", itemDto.LotId));
                return new ItemDetail();
            }
            catch (SqlException ex)
            {
                ex.ToString();
                throw;
            }
        }

        public async Task<RequestItem> PostPutReqItems(InventoryDto itemDto)
        {
            try
            {
                if(itemDto.QtyReceived == null || itemDto.QtyReceived == 0)
                {
                    itemDto.QtyReceived = 0;
                }
                if(itemDto.LotId == null || itemDto.LotId == 0)
                {
                    itemDto.LotId = 0;
                }
                await _dbContext.Database
                   .ExecuteSqlRawAsync("EXEC PostPutReqItems @UserId, @RequestItemId, @ItemNo, @QtyRequest,@Status,@IsApproved,@QtyReceived,@LotId",
                    new SqlParameter("@UserId", itemDto.UserId),
                    new SqlParameter("@ItemNo", itemDto.ItemNo),
                    new SqlParameter("@RequestItemId", itemDto.RequestItemId != null ? itemDto.RequestItemId : 0L), 
                    new SqlParameter("@QtyRequest", itemDto.QtyRequest),
                    new SqlParameter("@Status", itemDto.Status),
                    new SqlParameter("@IsApproved", itemDto.IsApproved),
                     new SqlParameter("@QtyReceived", itemDto.QtyReceived),
                    new SqlParameter("@LotId", itemDto.LotId));
                return new RequestItem();
            }
            catch (SqlException ex)
            {
                ex.ToString();
                throw;
            }
        }

        public async Task<List<RequestItemDetail>> GetRequestedItemByUserId(InventoryDto itemDto)
        {
            try
            {
                var allItems = await _dbContext.RequestItemDetails
                    .FromSqlRaw($"EXEC GetRequestedItemByUserId @UserId = '{itemDto.UserId}', " +
                    $"@RequestItemId = '{itemDto.RequestItemId}',@WithoutStocks = '{itemDto.WithoutStocks}'")
                    .ToListAsync();

                return allItems ?? new List<RequestItemDetail>();
            }
            catch (SqlException ex)
            {
                ex.ToString();
                throw;
            }
        }

        public async Task<List<LotQtyByItem>> GetLotQtyByItem(InventoryDto itemDto)
        {
            try
            {
                var allItems = await _dbContext.LotQtyByItems
                    .FromSqlRaw($"EXEC GetLotQtyByItem @UserId = '{itemDto.UserId}', " +
                    $"@ItemNo = '{itemDto.ItemNo}',@LotId = '{itemDto.LotId}'")
                    .ToListAsync();

                return allItems ?? new List<LotQtyByItem>();
            }
            catch (SqlException ex)
            {
                ex.ToString();
                throw;
            }
        }
    }
}