309 lines
13 KiB
C#
309 lines
13 KiB
C#
using CPRNIMS.Domain.Contracts.Inventory;
|
|
using CPRNIMS.Infrastructure.Database;
|
|
using CPRNIMS.Infrastructure.Dto.Inventory;
|
|
using CPRNIMS.Infrastructure.Dto.Inventory.Request;
|
|
using CPRNIMS.Infrastructure.Dto.Inventory.Response;
|
|
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<TransactContextDto?> GetTransactContextAsync(int inventoryId, CancellationToken ct)
|
|
{
|
|
var inv = await _dbContext.Inventories
|
|
.Where(i => i.InventoryId == inventoryId && i.IsActive)
|
|
.Select(i => new
|
|
{
|
|
i.InventoryId,
|
|
i.QtyOnHand,
|
|
i.QtyIn,
|
|
i.QtyOut,
|
|
|
|
LotNo = i.Lot != null ? i.Lot.LotName : null,
|
|
|
|
Department = i.User != null && i.User.Department != null
|
|
? i.User.Department.Department
|
|
: null,
|
|
i.ItemNo,
|
|
FirstDetail = i.InventTrans
|
|
.Where(t => t.IsActive)
|
|
.SelectMany(t => t.InventTransDetails)
|
|
.Where(d => d.PRDetails != null
|
|
&& d.PRDetails.PRs != null
|
|
&& d.IsActive)
|
|
.Select(d => new
|
|
{
|
|
d.PRDetails!.ItemName,
|
|
PRNo = d.PRDetails.PRs!.PRNo
|
|
})
|
|
.FirstOrDefault()
|
|
})
|
|
.FirstOrDefaultAsync(ct);
|
|
|
|
if (inv == null) return null;
|
|
|
|
// Computed property QtyAvailableToReturn can't be used in EF Where,
|
|
// so filter after projection using a raw expression in the query.
|
|
var openRIS = await _dbContext.RIS
|
|
.Where(r => r.InventoryId == inventoryId
|
|
&& r.Status == 1)
|
|
.Select(r => new RISReferenceDto
|
|
{
|
|
RISId = r.RISId,
|
|
RISNo = r.RISNo,
|
|
QtyIssued = r.QtyIssued,
|
|
TotalReturned = r.MaterialReturns
|
|
.Where(m => m.Status != 2)
|
|
.Sum(m => (int?)m.QtyReturned) ?? 0,
|
|
DisciplineName = r.Discipline.DisciplineName,
|
|
CreatedDate = r.CreatedDate
|
|
})
|
|
|
|
// Can't use the computed property here — EF won't translate it
|
|
// so we repeat the expression inline
|
|
.Where(r => r.QtyIssued - r.TotalReturned > 0)
|
|
.OrderByDescending(r => r.CreatedDate)
|
|
.ToListAsync(ct);
|
|
|
|
var disciplines = await GetDisciplinesAsync(ct);
|
|
|
|
return new TransactContextDto
|
|
{
|
|
InventoryId = inv.InventoryId,
|
|
ItemName = inv.FirstDetail?.ItemName ?? "—",
|
|
PRNo = inv.FirstDetail.PRNo,
|
|
ItemNo = inv.ItemNo,
|
|
LotNo = inv.LotNo,
|
|
Department = inv.Department,
|
|
QtyOnHand = inv.QtyOnHand,
|
|
QtyIn = inv.QtyIn,
|
|
QtyOut = inv.QtyOut,
|
|
Disciplines = disciplines,
|
|
OpenRISList = openRIS
|
|
};
|
|
}
|
|
public async Task<IEnumerable<DisciplineDto>> GetDisciplinesAsync(CancellationToken ct)
|
|
{
|
|
return await _dbContext.Disciplines
|
|
.OrderBy(d => d.DisciplineName)
|
|
.Select(d => new DisciplineDto
|
|
{
|
|
DisciplineId = d.DisciplineId,
|
|
DisciplineName = d.DisciplineName
|
|
})
|
|
.ToListAsync(ct);
|
|
}
|
|
public async Task<List<ItemDetail>> GetInventoryById(InventoryDto itemDto)
|
|
{
|
|
var allItems = await _dbContext.ItemDetails
|
|
.FromSqlRaw($"EXEC GetInventoryById @UserId = '{itemDto.UserId}',@InventoryId = '{itemDto.InventoryId}'")
|
|
.ToListAsync();
|
|
|
|
return allItems ?? new List<Infrastructure.Entities.Inventory.ItemDetail>();
|
|
}
|
|
public async Task<PagedResult<InventoryResponse>> GetInventory(InventoryRequest request, CancellationToken ct)
|
|
{
|
|
var parameters = new[]
|
|
{
|
|
new SqlParameter("@UserId", request.UserId),
|
|
new SqlParameter("@SearchPRNo", request.SearchPRNo ?? ""),
|
|
new SqlParameter("@SearchItemNo", request.SearchItemNo ?? ""),
|
|
new SqlParameter("@SearchItemName", request.SearchItemName ?? ""),
|
|
new SqlParameter("@SearchDept", request.SearchDept ?? ""),
|
|
new SqlParameter("@SearchProjectCode",request.SearchProjectCode ?? ""),
|
|
new SqlParameter("@PageNumber", request.PageNumber),
|
|
new SqlParameter("@PageSize", request.PageSize)
|
|
};
|
|
|
|
var departmentList = new List<string>();
|
|
int totalCount = 0;
|
|
var items = new List<InventoryResponse>();
|
|
|
|
await using var conn = _dbContext.Database.GetDbConnection();
|
|
await conn.OpenAsync(ct);
|
|
|
|
using var cmd = conn.CreateCommand();
|
|
cmd.CommandText = @"EXEC GetInventory @UserId, @SearchPRNo, @SearchItemNo, @SearchItemName, @SearchDept,@SearchProjectCode, @PageNumber, @PageSize";
|
|
foreach (var p in parameters) cmd.Parameters.Add(p);
|
|
cmd.CommandTimeout = 60;
|
|
|
|
using var reader = await cmd.ExecuteReaderAsync(ct);
|
|
|
|
while (await reader.ReadAsync(ct))
|
|
departmentList.Add(reader.GetString(0));
|
|
|
|
await reader.NextResultAsync(ct);
|
|
if (await reader.ReadAsync(ct))
|
|
totalCount = reader.GetInt32(0);
|
|
|
|
await reader.NextResultAsync(ct);
|
|
while (await reader.ReadAsync(ct))
|
|
{
|
|
items.Add(new InventoryResponse
|
|
{
|
|
InventoryId = reader.GetInt32(reader.GetOrdinal("InventoryId")),
|
|
QtyIn = reader["QtyIn"] as decimal? ?? 0,
|
|
QtyOut = reader["QtyOut"] as decimal? ?? 0,
|
|
QtyOnHand = reader["QtyOnHand"] as decimal? ?? 0,
|
|
LotNo = reader["LotNo"]?.ToString(),
|
|
PRNo = Convert.ToInt64(reader["PRNo"]),
|
|
UserId = reader["UserId"]?.ToString(),
|
|
ItemName = reader["ItemName"]?.ToString(),
|
|
ItemNo = Convert.ToInt64(reader["ItemNo"]),
|
|
ItemDescription = reader["ItemDescription"]?.ToString(),
|
|
ItemCategoryName = reader["ItemCategoryName"]?.ToString(),
|
|
Department = reader["Department"]?.ToString(),
|
|
ProjectCode = reader["ProjectCode"]?.ToString(),
|
|
CreatedDate = reader["CreatedDate"] == DBNull.Value
|
|
? DateTime.MinValue
|
|
: Convert.ToDateTime(reader["CreatedDate"])
|
|
});
|
|
}
|
|
|
|
await conn.CloseAsync();
|
|
|
|
return new PagedResult<InventoryResponse>
|
|
{
|
|
Data = items,
|
|
TotalCount = totalCount,
|
|
PageNumber = request.PageNumber,
|
|
PageSize = request.PageSize,
|
|
DepartmentList = departmentList
|
|
};
|
|
}
|
|
public async Task<List<InventoryByIdResponse>> GetInventoryById(InventoryRequest itemDto,CancellationToken ct)
|
|
{
|
|
var allItems = await _dbContext.InventoryByIdResponses
|
|
.FromSqlRaw($"EXEC GetInventoryById @InventoryId",
|
|
new SqlParameter("@InventoryId", itemDto.InventoryId))
|
|
.ToListAsync(ct);
|
|
|
|
return allItems ?? new List<InventoryByIdResponse>();
|
|
}
|
|
public async Task<List<Infrastructure.Entities.Inventory.Inventory>>GetInventoryByUserId(InventoryDto itemDto)
|
|
{
|
|
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>();
|
|
}
|
|
|
|
public async Task<List<Lot>> GetLotNoById(InventoryDto itemDto)
|
|
{
|
|
var allItems = await _dbContext.Lots
|
|
.FromSqlRaw($"EXEC GetLotById @UserId = '{itemDto.UserId}'")
|
|
.ToListAsync();
|
|
|
|
return allItems ?? new List<Lot>();
|
|
}
|
|
|
|
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)
|
|
{
|
|
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();
|
|
}
|
|
|
|
public async Task<Infrastructure.Entities.Inventory.Inventory> PostPutReqApproval(InventoryDto itemDto)
|
|
{
|
|
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();
|
|
}
|
|
|
|
public async Task<ItemDetail> PostPutLotBin(InventoryDto itemDto)
|
|
{
|
|
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();
|
|
}
|
|
|
|
public async Task<RequestItem> PostPutReqItems(InventoryDto itemDto)
|
|
{
|
|
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();
|
|
}
|
|
|
|
public async Task<List<RequestItemDetail>> GetRequestedItemByUserId(InventoryDto itemDto)
|
|
{
|
|
var allItems = await _dbContext.RequestItemDetails
|
|
.FromSqlRaw($"EXEC GetRequestedItemByUserId @UserId = '{itemDto.UserId}', " +
|
|
$"@RequestItemId = '{itemDto.RequestItemId}',@WithoutStocks = '{itemDto.WithoutStocks}'")
|
|
.ToListAsync();
|
|
|
|
return allItems ?? new List<RequestItemDetail>();
|
|
}
|
|
|
|
public async Task<List<LotQtyByItem>> GetLotQtyByItem(InventoryDto itemDto)
|
|
{
|
|
var allItems = await _dbContext.LotQtyByItems
|
|
.FromSqlRaw($"EXEC GetLotQtyByItem @UserId = '{itemDto.UserId}', " +
|
|
$"@ItemNo = '{itemDto.ItemNo}',@LotId = '{itemDto.LotId}'")
|
|
.ToListAsync();
|
|
|
|
return allItems ?? new List<LotQtyByItem>();
|
|
}
|
|
}
|
|
}
|