public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
{
return(BadRequest());
}
var Query = (from Category in this.Context.Itmcateg
join aText in this.Context.Atextra on new { code1 = Category.Tclcod0, code2 = "TCLAXX" } equals new { code1 = aText.Ident10, code2 = aText.Zone0 } into AText
from nAText in AText.DefaultIfEmpty()
select new
{
Category,
nAText
}).AsQueryable();
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.Split(null);
foreach (string temp in filters)
{
string keyword = temp.ToLower().Trim();
Query = Query.Where(x => x.Category.Tclcod0.ToLower().Trim().Contains(keyword) ||
x.nAText.Texte0.ToLower().Trim().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "BankNumber":
if (Scroll.SortOrder == -1)
{
Query = Query.OrderByDescending(x => x.Category.Tclcod0);
}
else
{
Query = Query.OrderBy(x => x.Category.Tclcod0);
}
break;
case "Description":
if (Scroll.SortOrder == -1)
{
Query = Query.OrderByDescending(x => x.nAText.Texte0);
}
else
{
Query = Query.OrderBy(x => x.nAText.Texte0);
}
break;
default:
Query = Query.OrderBy(x => x.Category.Tclcod0);
break;
}
// Get TotalRow
Scroll.TotalRow = await Query.CountAsync();
// Query Data
var QueryData = await Query.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 15).AsNoTracking().ToListAsync();
var mapDatas = new List <CategoryViewModel>();
foreach (var item in QueryData)
{
var mapData = new CategoryViewModel
{
CategoryCode = item.Category.Tclcod0,
Rowid = (int)item.Category.Rowid,
CategoryName = item.nAText.Texte0,
};
mapDatas.Add(mapData);
}
return(new JsonResult(new ScrollDataViewModel <CategoryViewModel>(Scroll, mapDatas), this.DefaultJsonSettings));
}
private async Task <List <StockMovementViewModel> > GetData(ScrollViewModel Scroll)
{
#region Query
var QueryData = (from ProductMaster in this.sageContext.Itmmaster
//join StockJou in this.sageContext.Stojou on ProductMaster.Itmref0 equals StockJou.Itmref0 into StockJou2
//from nStockJou in StockJou2.DefaultIfEmpty()
join ProductCate in this.sageContext.Itmcateg on ProductMaster.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 = ProductMaster.Purtex0 } equals new { bText.Code0 } into bText2
from fullText in bText2.DefaultIfEmpty()
select new
{
// nStockJou,
ProductMaster,
nProductCate,
nAText,
fullText,
}).Where(x => this.sageContext.Stojou.Any(z => z.Itmref0 == x.ProductMaster.Itmref0)).AsQueryable();
// .Where(x => x.nStockJou != null)
#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.ProductMaster.Itmdes10.ToLower().Contains(keyword) ||
x.ProductMaster.Itmdes20.ToLower().Contains(keyword) ||
x.ProductMaster.Itmdes30.ToLower().Contains(keyword) ||
x.ProductMaster.Itmref0.ToLower().Contains(keyword));
}
// Product Category
if (Scroll.WhereBanks.Any())
{
QueryData = QueryData.Where(x => Scroll.WhereBanks.Contains(x.ProductMaster.Tclcod0));
}
#endregion Filter
#region Scroll
switch (Scroll.SortField)
{
case "ItemCode":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.ProductMaster.Itmref0);
}
else
{
QueryData = QueryData.OrderBy(x => x.ProductMaster.Itmref0);
}
break;
case "ItemDescFull":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.ProductMaster.Itmdes10);
}
else
{
QueryData = QueryData.OrderBy(x => x.ProductMaster.Itmdes10);
}
break;
case "Uom":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.ProductMaster.Stu0);
}
else
{
QueryData = QueryData.OrderBy(x => x.ProductMaster.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;
default:
QueryData = QueryData.OrderBy(x => x.ProductMaster.Itmref0);
break;
}
#endregion Scroll
Scroll.TotalRow = await QueryData.CountAsync();
var Message = "";
try
{
var Datasource = await QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 15).AsNoTracking().ToListAsync();
var MapDatas = new List <StockMovementViewModel>();
var Purchase = new List <int>()
{
6, 8
};
var Stock = new List <int>()
{
19, 20, 31, 32
};
var Sale = new List <int>()
{
5, 13
};
foreach (var item in Datasource)
{
var MapData = new StockMovementViewModel()
{
Category = item?.nProductCate?.Tclcod0 ?? "",
CategoryDesc = item?.nAText?.Texte0 ?? "",
ItemCode = item?.ProductMaster?.Itmref0,
ItemDesc = item?.ProductMaster?.Itmdes10,
Uom = string.IsNullOrEmpty(item?.ProductMaster?.Pcu0.Trim()) ? item?.ProductMaster?.Stu0 : item?.ProductMaster?.Pcu0,
};
//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 ?? "-";
}
var StockJoc = await this.repository.GetToListAsync(x => x, x => x.Itmref0 == MapData.ItemCode && x.Regflg0 == 1);
foreach (var item2 in StockJoc.GroupBy(x => new {
x.Vcrnum0,
x.Vcrtyp0,
x.Iptdat0,
x.Loc0,
}))
{
MapData.StockMovement2s.Add(new StockMovement2ViewModel
{
Bom = item2?.FirstOrDefault()?.Cce1 ?? "",
DocNo = item2?.Key?.Vcrnum0 ?? "",
Location = item2?.FirstOrDefault()?.Loc0 ?? "",
MovementDate = item2?.Key?.Iptdat0,
MovementType = Purchase.Contains(item2.Key.Vcrtyp0) ? "Purchase" :
(Stock.Contains(item2.Key.Vcrtyp0) ? "Stock" :
(Sale.Contains(item2.Key.Vcrtyp0) ? "Sale" : "Stock")),
Project = item2?.FirstOrDefault()?.Cce2 ?? "",
WorkGroup = item2?.FirstOrDefault()?.Cce3 ?? "",
QuantityIn = (double)item2?.Where(x => x.Qtypcu0 > 0)?.Sum(x => x?.Qtypcu0 ?? (decimal)0),
QuantityOut = (double)item2?.Where(x => x.Qtypcu0 <= 0)?.Sum(x => x?.Qtypcu0 ?? (decimal)0),
});
}
MapDatas.Add(MapData);
}
return(MapDatas);
}
catch (Exception ex)
{
Message = $"{ex.ToString()}";
}
return(null);
}
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);
}