private List <DataLayer.ItemDimensionAdjustmentSearchModel> GetItemDimensionsOrCreate(List <DataLayer.ItemDimensionAdjustmentSearchModel> itemsList)
{
using (var context = new WorkFlowManagerDBEntities())
{
foreach (var item in itemsList)
{
item.ItemDimFromIserial = context.FindOrCreateItemDim(item.ItemId, item.ItemType, item.ColorFromId,
item.SizeFrom, item.BatchNoFrom, item.SiteFromIserial).FirstOrDefault().Iserial;
}
return(itemsList);
}
}
private DataLayer.ItemDimensionSearchModel GetItemToQuantities(
DataLayer.ItemDimensionSearchModel item, string warehouseCode)
{
using (var context = new WorkFlowManagerDBEntities())
{
var temp = context.FindOrCreateItemDim(item.ItemId, item.ItemType,
item.ColorToId, item.SizeTo, item.BatchNoTo, item.SiteToIserial);
var itemdim = temp.FirstOrDefault();
item.ItemDimToIserial = itemdim.Iserial;
item.AvailableToQuantity = GetItemQuantityByWarehouse(context, warehouseCode, item.ItemDimToIserial);
item.PendingToQuantity = GetItemPendingByWarehouse(context, warehouseCode, item.ItemDimToIserial);
return(item);
}
}
private static TblAdjustmentDetail PrepareDetail(WorkFlowManagerDBEntities entities, TblAdjustmentHeader header,
ImportedItemDimensionModel item, List <string> errors)
{
string itemInfo = string.Format("Item code:{0}, Color code:{1}, Batch:{2}, Size:{3}, Quantity:{4}, Cost:{5}"
, item.ItemCode, item.Color, item.BatchNo, item.Size, item.Qty, item.Cost);
var items = entities.FabricAccSearches.Where(i => i.Code == item.ItemCode).ToList();
if (items.Count != 1)
{
if (items.Count == 0)
{
errors.Add(string.Format("{1} -->> Item Code not Found. More info -->> {0}",
itemInfo, DateTime.Now));
}
else
{
errors.Add(string.Format("{1} -->> Item Code found for more than one item. More info -->> {0}",
itemInfo, DateTime.Now));
}
return(null);
}
var colors = entities.TblColors.Where(i => i.Code == item.Color && i.TblLkpColorGroup != 24);
if (items.FirstOrDefault().ItemGroup.ToLower().Contains("acc"))
{
colors = entities.TblColors.Where(i => i.Code == item.Color && i.TblLkpColorGroup == 24);
}
if (colors.Count() != 1)
{
if (colors.Count() == 0)
{
errors.Add(string.Format("{1} -->> Color Code not Found. More info -->> {0}",
itemInfo, DateTime.Now));
}
else
{
errors.Add(string.Format("{1} -->> Color Code found for more than one item. More info -->> {0}",
itemInfo, DateTime.Now));
}
return(null);
}
if (string.IsNullOrWhiteSpace(item.Size) && string.IsNullOrWhiteSpace(item.BatchNo))
{
errors.Add(string.Format("{1} -->> Every item must have size or batch no. More info -->> {0}",
itemInfo, DateTime.Now)); return(null);
}
// Find Or create ItemDim
var warehouse = entities.TblWarehouses.FirstOrDefault(s =>
s.Iserial == header.WarehouseIserial);
var iserial = entities.FindOrCreateItemDim(items.FirstOrDefault().Iserial,
items.FirstOrDefault().ItemGroup, colors.FirstOrDefault().Iserial,
item.Size, item.BatchNo, warehouse.TblSite).FirstOrDefault().Iserial;
var detail = new TblAdjustmentDetail()
{
ItemDimIserial = iserial,
AvailableQuantity = 0, // الـ StoredProcedure هتملاه
CountedQuantity = item.Qty,
DifferenceQuantity = 0, // الـ StoredProcedure هتملاه
Cost = item.Cost,
};
detail.ItemAdjustment.AvailableQuantity = 0;
detail.ItemAdjustment.CountedQuantity = item.Qty;
detail.ItemAdjustment.DifferenceQuantity = 0;
detail.ItemAdjustment.Cost = item.Cost;
return(detail);
}
private List <DataLayer.ItemDimensionAdjustmentSearchModel> GetItemDimensionsQuantitiesByDate(
out int fullCount, string warehouseCode, int?itemIserial, string itemType,
int?colorIserial, string size, string rollBatch, DateTime docDate)
{
using (var entities = new WorkFlowManagerDBEntities())
{
// هنا ده جزء الـ Relations
IQueryable <TblInventTran> temp = entities.TblInventTrans.Include(nameof(TblInventTran.TblInventType1))
.Include(nameof(TblInventTran.TblWarehouse1)).Include(nameof(TblInventTran.TblItemDim1))
.Include(string.Format("{0}.{1}", nameof(TblInventTran.TblItemDim1), nameof(TblItemDim.TblColor1)));
// ده الشروط
var predicate = PredicateBuilder.True <TblInventTran>();
predicate = predicate.And(i => i.TblWarehouse1.Code == warehouseCode);
predicate = predicate.And(i => i.TblItemDim1.ItemIserial == itemIserial);
predicate = predicate.And(i => i.TblItemDim1.ItemType == itemType);
predicate = predicate.And(i => i.DocDate <= docDate);
if (colorIserial.HasValue)
{
predicate = predicate.And(i => i.TblItemDim1.TblColor == colorIserial);
}
if (!string.IsNullOrWhiteSpace(size))
{
predicate = predicate.And(i => i.TblItemDim1.Size == size);
}
if (!string.IsNullOrWhiteSpace(rollBatch))
{
predicate = predicate.And(i => i.TblItemDim1.BatchNo == rollBatch);
}
// هجيب كل الى محتاجه علشان جزئية الكمية والـ Nature
var query = temp.AsExpandable().Where(predicate).Select(it =>
new DataLayer.ItemDimensionAdjustmentSearchModel()
{
ItemId = it.TblItemDim1.ItemIserial,
ItemName = entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType) == null ? "" : entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType).Name,
ItemCode = entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType).Code,
ItemType = itemType ?? "",
AvailableQuantity = it.Qty * it.TblInventType1.Nature,
DifferenceQuantity = 0,
CountedQuantity = 0,
SiteFromIserial = it.TblItemDim1.TblSite,
BatchNoFrom = it.TblItemDim1.BatchNo ?? "",
SizeFrom = it.TblItemDim1.Size ?? "",
ColorFromCode = it.TblItemDim1.TblColor1.Ename,
ColorFromId = it.TblItemDim1.TblColor,
IsAcc = itemType.ToLower().Contains("acc"),
ItemDimFromIserial = it.TblItemDim,
TransferredQuantity = 0,
Cost = 0, // هو هيدخلها بنفسه
}
// هعمل جروب باى بيه
).GroupBy(i => new {
i.ItemId,
i.ItemCode,
i.ItemDimFromIserial,
i.SiteFromIserial,
i.BatchNoFrom,
i.SizeFrom,
i.ColorFromCode,
i.ColorFromId,
});
fullCount = query.Count();
var result = new List <DataLayer.ItemDimensionAdjustmentSearchModel>();
if (fullCount > 0)
{
// هنا هرجع اجيب الى محتاجه واعمل المجموع واشغل الاستعلام
var tempQ = query.Select(it =>
new DataLayer.ItemDimensionAdjustmentSearchModel()
{
ItemId = it.Key.ItemId,
ItemName = (entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType) == null ? "" :
entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType).Name),
ItemCode = it.Key.ItemCode,
ItemType = itemType ?? "",
AvailableQuantity = it.Sum(i => (i.AvailableQuantity)),
DifferenceQuantity = 0,
CountedQuantity = 0,
SiteFromIserial = it.Key.SiteFromIserial,
BatchNoFrom = it.Key.BatchNoFrom ?? "",
SizeFrom = it.Key.SizeFrom ?? "",
ColorFromCode = it.Key.ColorFromCode,
ColorFromId = it.Key.ColorFromId,
IsAcc = itemType.ToLower().Contains("acc"),
ItemDimFromIserial = it.Key.ItemDimFromIserial,
TransferredQuantity = 0,
Cost = 0,
}
);
// هجيب الداتا
result = tempQ.ToList();
}
else if (itemIserial.HasValue && colorIserial.HasValue &&
!string.IsNullOrWhiteSpace(itemType) && ((itemType.ToLower().Contains("acc") &&
!string.IsNullOrWhiteSpace(size)) || (!itemType.ToLower().Contains("acc") &&
!string.IsNullOrWhiteSpace(rollBatch))))
{
var siteIserial = entities.TblWarehouses.FirstOrDefault(w => w.Code == warehouseCode).TblSite;
var itemdim = entities.FindOrCreateItemDim(itemIserial, itemType, colorIserial, size, rollBatch, siteIserial).FirstOrDefault();
var color = entities.TblColors.FirstOrDefault(c => c.Iserial == itemdim.TblColor);
result = new List <DataLayer.ItemDimensionAdjustmentSearchModel>()
{
new DataLayer.ItemDimensionAdjustmentSearchModel()
{
ItemId = itemdim.ItemIserial,
ItemName = (entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType) == null ? "" :
entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType).Name),
ItemCode = (entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType) == null ? "" :
entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType).Code),
ItemType = itemdim.ItemType,
AvailableQuantity = 0,
DifferenceQuantity = 0,
CountedQuantity = 0,
SiteFromIserial = itemdim.TblSite,
BatchNoFrom = itemdim.BatchNo,
SizeFrom = itemdim.Size,
ColorFrom = new DataLayer.TblColor()
{
Iserial = itemdim.TblColor,
Code = color.Code,
Ename = color.Ename,
Aname = color.Aname,
},
ColorFromCode = color.Code,
ColorFromId = itemdim.TblColor,
IsAcc = itemdim.ItemType.ToLower().Contains("acc"),
ItemDimFromIserial = itemdim.Iserial,
TransferredQuantity = 0,
Cost = 0,
}
};
}
return(result);
}
}
public List <DataLayer.ItemDimensionAdjustmentSearchModel> GetAccWarehouseRows(string warehouseCode,
string itemType, int?itemIserial, int?colorIserial, string size, string rollBatch)
{
using (var entities = new WorkFlowManagerDBEntities())
{
var predicate = PredicateBuilder.True <TblStock>();
predicate = predicate.And(i => i.TblWarehouse1.Code == warehouseCode);
if (!string.IsNullOrWhiteSpace(itemType))
{
predicate = predicate.And(i => i.TblItemDim1.ItemType == itemType);
}
if (itemIserial.HasValue)
{
predicate = predicate.And(i => i.TblItemDim1.ItemIserial == itemIserial);
}
if (colorIserial.HasValue)
{
predicate = predicate.And(i => i.TblItemDim1.TblColor == colorIserial);
}
if (!string.IsNullOrWhiteSpace(size))
{
predicate = predicate.And(i => i.TblItemDim1.Size == size);
}
if (!string.IsNullOrWhiteSpace(rollBatch))
{
predicate = predicate.And(i => i.TblItemDim1.BatchNo == rollBatch);
}
var query = entities.TblStocks.Include(nameof(TblInventTran.TblItemDim1))
.Include(string.Format("{0}.{1}", nameof(TblInventTran.TblItemDim1), nameof(TblItemDim.TblColor1)))
.AsExpandable().Where(predicate).Select(s =>
new DataLayer.ItemDimensionAdjustmentSearchModel()
{
ItemId = s.TblItemDim1.ItemIserial,
ItemName = (entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == s.TblItemDim1.ItemIserial && i.ItemGroup == s.TblItemDim1.ItemType) == null ? "" :
entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == s.TblItemDim1.ItemIserial && i.ItemGroup == s.TblItemDim1.ItemType).Name),
ItemType = s.TblItemDim1.ItemType ?? "",
ItemCode = entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == s.TblItemDim1.ItemIserial && i.ItemGroup == s.TblItemDim1.ItemType).Code,
AvailableQuantity = s.Qty,
PendingQuantity = s.PendingQty,
DifferenceQuantity = 0,
CountedQuantity = 0,
SiteFromIserial = s.TblItemDim1.TblSite,
BatchNoFrom = s.TblItemDim1.BatchNo ?? "",
SizeFrom = s.TblItemDim1.Size ?? "",
ColorFromId = s.TblItemDim1.TblColor,
ColorFrom = new DataLayer.TblColor(),
ColorFromCode = s.TblItemDim1.TblColor1.Code,
IsAcc = s.TblItemDim1.ItemType.ToLower().Contains("acc"),
ItemDimFromIserial = s.TblItemDim,
TransferredQuantity = 0,
SiteToIserial = s.TblItemDim1.TblSite,
BatchNoTo = s.TblItemDim1.BatchNo ?? "",
SizeTo = s.TblItemDim1.Size ?? "",
ColorToId = s.TblItemDim1.TblColor,
ColorPerRow = new DataLayer.TblColor(),
ItemDimToIserial = s.TblItemDim,
});
var result = query.ToList();
if (result.Count > 0)
{
foreach (var item in result)
{
var t = entities.TblColors.FirstOrDefault(c => c.Iserial == item.ColorToId);
if (t != null)
{
item.ColorPerRow.InjectFrom(t);
}
var f = entities.TblColors.FirstOrDefault(c => c.Iserial == item.ColorFromId);
if (f != null)
{
item.ColorFrom.InjectFrom(f);
}
}
}
else if (itemIserial.HasValue && colorIserial.HasValue &&
!string.IsNullOrWhiteSpace(itemType) && ((itemType.ToLower().Contains("acc") &&
!string.IsNullOrWhiteSpace(size)) || (!itemType.ToLower().Contains("acc") &&
!string.IsNullOrWhiteSpace(rollBatch))))
{
var siteIserial = entities.TblWarehouses.FirstOrDefault(w => w.Code == warehouseCode).TblSite;
var itemdim = entities.FindOrCreateItemDim(itemIserial, itemType, colorIserial, size, rollBatch, siteIserial).FirstOrDefault();
var color = entities.TblColors.FirstOrDefault(c => c.Iserial == itemdim.TblColor);
result = new List <DataLayer.ItemDimensionAdjustmentSearchModel>()
{
new DataLayer.ItemDimensionAdjustmentSearchModel()
{
ItemId = itemdim.ItemIserial,
ItemName = (entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType) == null ? "" :
entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType).Name),
ItemCode = (entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType) == null ? "" :
entities.FabricAccSearches.FirstOrDefault(i =>
i.Iserial == itemIserial && i.ItemGroup == itemType).Code),
ItemType = itemdim.ItemType,
AvailableQuantity = 0,
DifferenceQuantity = 0,
CountedQuantity = 0,
SiteFromIserial = itemdim.TblSite,
BatchNoFrom = itemdim.BatchNo,
SizeFrom = itemdim.Size,
ColorFrom = new DataLayer.TblColor()
{
Iserial = itemdim.TblColor,
Code = color.Code,
Ename = color.Ename,
Aname = color.Aname,
},
ColorFromCode = color.Code,
ColorFromId = itemdim.TblColor,
IsAcc = itemdim.ItemType.ToLower().Contains("acc"),
ItemDimFromIserial = itemdim.Iserial,
TransferredQuantity = 0,
}
};
}
return(result);
}
}