public List <PRDChemProdReqItem> GetTransactionItemListForStockAdjust(long _TransactionID, string _IssueFrom)
{
using (var context = new BLC_DEVEntities())
{
var StockResult = DalChemicalStock.CombinationWiseStockInSpecificProductionFloor(Convert.ToByte(_IssueFrom));
var Data = (from i in context.INV_StoreTransItem.AsEnumerable()
where i.TransactionID == _TransactionID
join s in StockResult on new { ItemID = (i.ItemID == null? 0: i.ItemID), SupplierID = (i.SupplierID == null? 0 : i.SupplierID),
UnitID = (i.TransactionUnit == null?0:i.TransactionUnit) } equals
new { ItemID = s.ItemID, SupplierID = s.SupplierID, UnitID = s.UnitID } into StockInfo
from item in StockInfo.DefaultIfEmpty()
join su in context.Sys_Unit on(item == null ? 0 : item.UnitID) equals su.UnitID into StockUnits
from su in StockUnits.DefaultIfEmpty()
join sup in context.Sys_Supplier on(item == null ? 0 : item.SupplierID) equals sup.SupplierID into Suppliers
from sup in Suppliers.DefaultIfEmpty()
from it in context.Sys_ChemicalItem.Where(x => x.ItemID == i.ItemID).DefaultIfEmpty()
from iu in context.Sys_Unit.Where(x => x.UnitID == i.TransactionUnit).DefaultIfEmpty()
select new PRDChemProdReqItem
{
TransItemID = i.TransItemID,
ItemID = i.ItemID,
ItemName = (it == null ? null : it.ItemName),
StockQty = (item == null ? 0 : item.ClosingQty),
StockUnitName = (su == null ? null : su.UnitName),
SupplierID = (sup == null ? 0 : sup.SupplierID),
SupplierName = (sup == null ? null : sup.SupplierName),
IssueQty = i.TransactionQty,
IssueUnit = i.TransactionUnit,
IssueUnitName = (iu == null ? "" : iu.UnitName)
}).ToList();
return(Data);
}
}
public List <PRQChemicalPI> GetPIInformationForSearch()
{
using (_context)
{
var Data = (from p in _context.PRQ_ChemicalPI.AsEnumerable()
//from s in _context.Sys_Supplier.Where(x => x.SupplierID == p.SupplierID).DefaultIfEmpty()
join s in _context.Sys_Supplier on(p.SupplierID == null? null: p.SupplierID) equals s.SupplierID into Suppliers
from s in Suppliers.DefaultIfEmpty()
//from la in _context.Sys_Supplier.Where(x => x.SupplierID == p.LocalAgent).DefaultIfEmpty()
join la in _context.Sys_Supplier on(p.LocalAgent == null ? null : p.SupplierID) equals la.SupplierID into LocalAgents
from la in LocalAgents.DefaultIfEmpty()
//from fa in _context.Sys_Supplier.Where(x => x.SupplierID == p.ForeignAgent).DefaultIfEmpty()
join fa in _context.Sys_Supplier on(p.ForeignAgent == null ? null : p.SupplierID) equals fa.SupplierID into ForeignAgents
from fa in ForeignAgents.DefaultIfEmpty()
orderby p.PIID descending
select new PRQChemicalPI
{
PIID = p.PIID,
PINo = p.PINo,
PIDate = (Convert.ToDateTime(p.PIDate)).ToString("dd'/'MM'/'yyyy"),
PICategory = (p.PICategory == "PI" ? "Proforma Invoice" : "Indent Order"),
SupplierName = (s == null ? null : s.SupplierName),
LocalAgentName = (la == null ? null : la.SupplierName),
ForeignAgentName = (fa == null ? null : fa.SupplierName),
RecordStatus = DalCommon.ReturnRecordStatus(p.RecordStatus)
}).ToList();
return(Data);
}
}
public List <Sys_Supplier> FindSupplierForAdjust(byte leatherType, byte ReceiveStore)
{
var SuppliersInStock = (from p in _context.Inv_StockSupplier
where p.LeatherType == leatherType && p.StoreID == ReceiveStore
group p by new
{
p.SupplierID,
p.ItemTypeID,
p.PurchaseID
} into g
select new
{
SupplierID = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.SupplierID).FirstOrDefault(),
ClosingQty = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.ClosingQty).FirstOrDefault()
}).ToList();
//var FinalList = SuppliersInStock.GroupBy(x => x.SupplierID).Select(group => group.First());
var SupplierList = (from s in SuppliersInStock
where s.ClosingQty > 0
join sn in _context.Sys_Supplier on s.SupplierID equals sn.SupplierID into Suppliers
from sn in Suppliers.DefaultIfEmpty()
select new Sys_Supplier
{
SupplierID = s.SupplierID,
SupplierCode = sn.SupplierCode,
SupplierName = sn.SupplierName
}).GroupBy(x => x.SupplierID).Select(group => group.First());
//var FinalList = SupplierList.GroupBy(x => x.SupplierID).Select(group => group.First());
return(SupplierList.ToList());
}
//For LOV in Grid
public List <PRDChemProdReqItem> GetStoreWiseChemicalItemStock(string _IssueFrom)
{
using (_context)
{
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()
}).ToList();
var Data = (from s in StockResult
where s.ClosingQty > 0
join i in _context.Sys_ChemicalItem on(s == null ? null : s.ItemID) equals i.ItemID into Items
from i in Items.DefaultIfEmpty()
join sup in _context.Sys_Supplier on(s == null ? 0 : s.SupplierID) equals sup.SupplierID into Suppliers
from sup in Suppliers.DefaultIfEmpty()
join si in _context.Sys_Size on(s == null ? 0 : s.PackSize) equals si.SizeID into Sizes
from si in Sizes.DefaultIfEmpty()
join siu in _context.Sys_Unit on(s == null ? 0 : s.SizeUnit) equals siu.UnitID into Units
from siu in Units.DefaultIfEmpty()
join su in _context.Sys_Unit on(s == null ? 0 : s.StockUnit) equals su.UnitID into StockUnits
from su in StockUnits.DefaultIfEmpty()
orderby(i == null ? null : i.ItemName)
select new PRDChemProdReqItem
{
ItemID = s.ItemID,
ItemName = (i == null ? null : i.ItemName),
SupplierID = s.SupplierID,
SupplierName = (sup == null ? null : sup.SupplierName),
PackSize = s.PackSize,
PackSizeName = (si == null ? null : si.SizeName),
SizeUnit = s.SizeUnit,
SizeUnitName = (siu == null ? null : siu.UnitName),
StockQty = s.ClosingQty,
StockUnit = s.StockUnit,
StockUnitName = (su == null ? null : su.UnitName),
PackQty = (Int32)(s.PackQty)
}).ToList();
return(Data);
}
}
//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);
}
}