public static StockMovementViewModel ConvertToViewModel(StockMovement dbModel)
{
var viewModel = new StockMovementViewModel
{
Id = dbModel.Id,
product = ProductDto.FromModel(dbModel.product),
DocumentID = dbModel.DocumentID,
Quantity = dbModel.Quantity,
StockMovementType = StockMovementTypeDto.FromModel(dbModel.StockMovementType),
createdBy = dbModel.createdBy,
};
return viewModel;
}
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);
}