private async Task <List <StockOnHandViewModel> > GetData(ScrollViewModel Scroll)
{
#region Query
var QueryData = (from ProductsSites in this.sageContext.Itmfacilit
join ProductTotal in this.sageContext.Itmmvt on ProductsSites.Itmref0 equals ProductTotal.Itmref0 into ProductStock
from nProductStock in ProductStock.DefaultIfEmpty()
join ProductMaster in this.sageContext.Itmmaster on ProductsSites.Itmref0 equals ProductMaster.Itmref0 into ProductMaster2
from nProductMaster in ProductMaster2.DefaultIfEmpty()
join ProductCate in this.sageContext.Itmcateg on nProductMaster.Tclcod0 equals ProductCate.Tclcod0 into ProductCate2
from nProductCate in ProductCate2.DefaultIfEmpty()
join aText in this.sageContext.Atextra on new { code1 = nProductCate.Tclcod0, code2 = "TCLAXX" } equals new { code1 = aText.Ident10, code2 = aText.Zone0 } into AText
from nAText in AText.DefaultIfEmpty()
join bText in this.sageContext.Texclob on new { Code0 = nProductMaster.Purtex0 } equals new { bText.Code0 } into bText2
from fullText in bText2.DefaultIfEmpty()
select new
{
ProductsSites,
nProductStock,
nProductMaster,
nProductCate,
nAText,
fullText,
}).AsQueryable();
#endregion Query
#region Filter
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.Split(null);
foreach (string temp in filters)
{
string keyword = temp.ToLower();
QueryData = QueryData.Where(x => x.nAText.Texte0.ToLower().Contains(keyword) ||
x.nProductMaster.Itmdes10.ToLower().Contains(keyword) ||
x.nProductMaster.Itmdes20.ToLower().Contains(keyword) ||
x.nProductMaster.Itmdes30.ToLower().Contains(keyword) ||
x.ProductsSites.Itmref0.ToLower().Contains(keyword));
}
// Product Category
if (Scroll.WhereBanks.Any())
{
QueryData = QueryData.Where(x => Scroll.WhereBanks.Contains(x.nProductMaster.Tclcod0));
}
QueryData = QueryData.Where(x => x.nProductStock.Physto0 > 0 || x.ProductsSites.Ofs0 > 0);
#endregion Filter
#region Scroll
switch (Scroll.SortField)
{
case "ItemCode":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.nProductMaster.Itmref0);
}
else
{
QueryData = QueryData.OrderBy(x => x.nProductMaster.Itmref0);
}
break;
case "ItemDescFull":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.nProductMaster.Itmdes10);
}
else
{
QueryData = QueryData.OrderBy(x => x.nProductMaster.Itmdes10);
}
break;
case "Uom":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.nProductMaster.Stu0);
}
else
{
QueryData = QueryData.OrderBy(x => x.nProductMaster.Stu0);
}
break;
case "Category":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.nProductCate.Tclcod0);
}
else
{
QueryData = QueryData.OrderBy(x => x.nProductCate.Tclcod0);
}
break;
case "CategoryDesc":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.nAText.Texte0);
}
else
{
QueryData = QueryData.OrderBy(x => x.nAText.Texte0);
}
break;
case "InternelStockString":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.nProductStock.Physto0);
}
else
{
QueryData = QueryData.OrderBy(x => x.nProductStock.Physto0);
}
break;
case "OnOrderString":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.ProductsSites.Ofs0);
}
else
{
QueryData = QueryData.OrderBy(x => x.ProductsSites.Ofs0);
}
break;
default:
QueryData = QueryData.OrderByDescending(x => x.nProductMaster.Itmref0);
break;
}
#endregion Scroll
Scroll.TotalRow = await QueryData.CountAsync();
var Message = "";
try
{
var Datasource = await QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take != -1 ? (Scroll.Take ?? 15) : (Scroll.TotalRow ?? 15)).AsNoTracking().ToListAsync();
var MapDatas = new List <StockOnHandViewModel>();
foreach (var item in Datasource)
{
var MapData = new StockOnHandViewModel()
{
Category = item?.nProductCate?.Tclcod0,
CategoryDesc = item?.nAText?.Texte0,
InternelStock = (double)(item?.nProductStock?.Physto0 ?? 0),
ItemCode = item?.nProductMaster?.Itmref0,
ItemDesc = item?.nProductMaster?.Itmdes10,
OnOrder = (double)(item?.nProductStock?.Ordsto0 ?? 0),
Uom = string.IsNullOrEmpty(item?.nProductMaster?.Pcu0.Trim()) ? item?.nProductMaster?.Stu0 : item?.nProductMaster?.Pcu0,
};
// Set Stock
var ListStock = await this.repositoryStock.GetToListAsync(x => x, x => x.Itmref0 == MapData.ItemCode);
if (ListStock != null && ListStock.Any())
{
foreach (var stock in ListStock.GroupBy(x => new { x.Loc0, x.Pcu0, x.Pjt0, x.Lot0, x.Bpslot0, x.Palnum0 }))
{
var itemStock = new StockLocationViewModel
{
LocationCode = stock.Key.Loc0,
Uom = stock.Key.Pcu0,
Project = stock.Key.Pjt0,
LotNo = stock.Key.Lot0,
HeatNo = stock.Key.Bpslot0,
Origin = stock.Key.Palnum0,
Quantity = (double)(stock?.Sum(z => z.Qtypcu0) ?? (decimal)0),
};
if (!string.IsNullOrEmpty(itemStock.LotNo))
{
var stock_lot = await this.repositoryStockLot.GetFirstOrDefaultAsync
(x => x, x => x.Itmref0 == MapData.ItemCode && x.Lot0 == itemStock.LotNo);
if (stock_lot != null)
{
if (stock_lot.Shldat0.Year < 2600)
{
itemStock.ExpDate = stock_lot.Shldat0;
}
}
}
MapData.StockLocations.Add(itemStock);
}
}
//ItemName
if (item.fullText?.Texte0 != null)
{
if (item.fullText.Texte0.StartsWith("{\\rtf1"))
{
MapData.ItemDescFull = Rtf.ToHtml(item.fullText?.Texte0);
}
else
{
MapData.ItemDescFull = item?.fullText?.Texte0;
}
}
else
{
MapData.ItemDescFull = item?.fullText?.Texte0 ?? "-";
}
MapDatas.Add(MapData);
}
return(MapDatas);
}
catch (Exception ex)
{
Message = $"{ex.ToString()}";
}
return(null);
}