//after Save For Grid Data
public List <PRDChemProdReqItem> GetTransactionItemList(long _TransactionID, string _IssueFrom)
{
using (var context = new BLC_DEVEntities())
{
var StockResult = (from p in context.INV_ChemStockSupplier
where p.StoreID.ToString() == _IssueFrom
group p by new
{
//p.StoreID,
p.SupplierID,
p.PackSize,
p.ItemID,
p.SizeUnit,
p.UnitID
} into g
select new
{
TransectionID = g.Max(p => p.TransectionID),
//StoreID = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.StoreID).FirstOrDefault(),
ItemID = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.ItemID).FirstOrDefault(),
SupplierID = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.SupplierID).FirstOrDefault(),
//UnitID = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.UnitID).FirstOrDefault(),
PackSize = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.PackSize).FirstOrDefault(),
SizeUnit = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.SizeUnit).FirstOrDefault(),
PackQty = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.PackClosingQty).FirstOrDefault(),
ClosingQty = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.ClosingQty).FirstOrDefault(),
StockUnit = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.UnitID).FirstOrDefault()
});
var Data = (from i in context.INV_StoreTransItem
where i.TransactionID == _TransactionID
// Stock Size
from s in StockResult.Where(x => x.ItemID == i.ItemID && x.PackSize == i.PackSize && x.SizeUnit == i.SizeUnit && x.SupplierID == i.SupplierID).DefaultIfEmpty()
join si in context.Sys_Size on(s == null ? null : s.PackSize) equals si.SizeID into StockSize
from si in StockSize.DefaultIfEmpty()
join siu in context.Sys_Unit on(s == null ? null : s.SizeUnit) equals siu.UnitID into StockSizeUnit
from siu in StockSizeUnit.DefaultIfEmpty()
join su in context.Sys_Unit on(s == null ? null : s.StockUnit) equals su.UnitID into StockUnit
from su in StockUnit.DefaultIfEmpty()
// Stock Size
join it in context.Sys_ChemicalItem on(i == null ? null : i.ItemID) equals it.ItemID into Chemicals
from it in Chemicals.DefaultIfEmpty()
join sup in context.Sys_Supplier on(i == null ? null : i.SupplierID) equals sup.SupplierID into Suppliers
from sup in Suppliers.DefaultIfEmpty()
join iu in context.Sys_Unit on(i == null ? null : i.TransactionUnit) equals iu.UnitID into IUnits
from iu in IUnits.DefaultIfEmpty()
join issuesize in context.Sys_Size on(i == null ? null : i.PackSize) equals issuesize.SizeID into IssueSizes
from issuesize in IssueSizes.DefaultIfEmpty()
join issuesizeunit in context.Sys_Unit on(i == null ? null : i.SizeUnit) equals issuesizeunit.UnitID into IssueSizeUnit
from issuesizeunit in IssueSizeUnit.DefaultIfEmpty()
orderby(it == null ? null : it.ItemName)
select new PRDChemProdReqItem
{
TransItemID = i.TransItemID,
ItemID = i.ItemID,
ItemName = (it == null ? null : it.ItemName),
SupplierID = i.SupplierID,
SupplierName = (sup == null ? null : sup.SupplierName),
PackSize = (s == null ? 0 : s.PackSize),
PackSizeName = (si == null ? null : si.SizeName),
SizeUnit = (s == null ? 0 : s.SizeUnit),
SizeUnitName = (siu == null ? null : siu.UnitName),
PackQty = (s == null ? 0 : (Int32)(s.PackQty)),
StockQty = (s == null ? 0 : s.ClosingQty),
StockUnit = (s == null ? 0 : s.StockUnit),
StockUnitName = (su == null ? null : su.UnitName),
IssuePackSize = i.PackSize,
IssuePackSizeName = (issuesize == null ? null : issuesize.SizeName),
IssueSizeUnit = i.SizeUnit,
IssueSizeUnitName = (issuesizeunit == null ? null : issuesizeunit.UnitName),
IssuePackQty = i.PackQty,
IssueQty = i.TransactionQty,
IssueUnitName = (iu == null ? null : iu.UnitName)
}).ToList();
return(Data);
}
}
