public override IOrderedQueryable <inventory_View> GetSearchQuery()
{
var Query = from x in DC.Set <pop>().AsNoTracking()
.Include("Group")
.CheckEqual(Searcher.PopID, x => x.ID)
join I in DC.Set <order_pop>().AsNoTracking()
.Include("ContractPop").Include("ContractPop.Contract")
.DPWhere(LoginUserInfo?.DataPrivileges, x => x.ContractPop.Contract.DCID)
.CheckBetween(Searcher.InvDate?.GetStartTime(), Searcher.InvDate?.GetEndTime(), x => x.CreateTime)
.CheckEqual(Searcher.PopID, x => x.ContractPop.PopID)
.CheckEqual(Searcher.DCID, x => x.ContractPop.Contract.DCID)
.GroupBy(x => x.ContractPop.PopID)
.Select(x => new order_pop
{
ContractPopID = x.Key,
OrderQty = x.Sum(x => x.OrderQty),
RecQty = x.Sum(x => x.RecQty),
RecTime = x.Max(x => x.RecTime)
})
on x.ID equals I.ContractPopID into INs
from IN in INs.DefaultIfEmpty()
join O in DC.Set <ship_pop>().AsNoTracking()
.DPWhere(LoginUserInfo?.DataPrivileges, x => x.User.DCID)
.CheckBetween(Searcher.InvDate?.GetStartTime(), Searcher.InvDate?.GetEndTime(), x => x.CreateTime)
.CheckEqual(Searcher.PopID, x => x.PopID)
.CheckEqual(Searcher.DCID, x => x.User.DCID)
.GroupBy(x => x.PopID)
.Select(x => new ship_pop_View
{
PopID = x.Key,
AlcQty = x.Sum(x => x.AlcQty),
CreateTime = x.Max(x => x.CreateTime)
})
on x.ID equals O.PopID into OUTs
from OUT in OUTs.DefaultIfEmpty()
select new inventory_View
{
ID = x.ID,
PopGroup = x.Group.Name,
PopName = x.PopName,
InDate = IN.RecTime,
OutDate = OUT.CreateTime,
OrderQty = IN.OrderQty,
OnHandQty = IN.OrderQty - IN.RecQty,
Stock = IN.RecQty,
UsedQty = OUT.AlcQty
};
return(Query.AsQueryable().OrderBy(r => r.PopName));
}
public async Task <List <SearchedOrgPosition> > Handle(SearchOrgPositionQuery request, CancellationToken cancellationToken)
{
List <SearchedOrgPosition> result = new List <SearchedOrgPosition>();
List <SearchedOrgPosition> data = await(from OP in _context.OrgPosition
join PT in _context.PositionType on OP.PositionTypeId equals PT.Id into OPTs
from resultOrgPositions in OPTs.DefaultIfEmpty()
join OUT in _context.OrgUnitType on OP.OrgUnitTypeId equals OUT.Id into OUTs
from resultOrgUnits in OUTs.DefaultIfEmpty()
join R in _context.Rank on OP.RankId equals R.Id into Rs
from resultRanks in Rs.DefaultIfEmpty()
select new SearchedOrgPosition
{
Id = OP.Id,
PositionTypeId = OP.PositionTypeId,
OrgUnitTypeId = OP.OrgUnitTypeId,
ParentId = OP.ParentId,
RankId = OP.RankId,
RankText = resultRanks.Name,
PositionTypeText = resultOrgPositions.Name,
OrgUnitText = resultOrgUnits.Name
}).ToListAsync(cancellationToken);
if (request.Id != null)
{
if (!request.Children)
{
result = data.Where(c => c.Id == request.Id).ToList();
}
else
{
result = data.Where(c => c.ParentId == request.Id).ToList();
}
}
else
{
result = data;
}
return(result);
}