public List <PRDChemProdReqItem> GetStockPackSizeInfo(string _StoreID, int _ItemID)
{
using (var context = new BLC_DEVEntities())
{
var StockResult = (from p in context.INV_ChemStockSupplier
where (p.StoreID).ToString() == _StoreID && p.ItemID == _ItemID
group p by new
{
p.SupplierID,
p.PackSize,
p.SizeUnit
} into g
select new
{
TransectionID = g.Max(p => p.TransectionID),
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(),
UnitID = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.UnitID).FirstOrDefault(),
SupplierID = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.SupplierID).FirstOrDefault(),
PackClosingQty = 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(),
}).ToList();
var Data = (from s in StockResult
where s.ClosingQty > 0
from si in context.Sys_Size.AsEnumerable().Where(x => x.SizeID == Convert.ToByte(s.PackSize)).DefaultIfEmpty()
from su in context.Sys_Unit.AsEnumerable().Where(x => x.UnitID == Convert.ToByte(s.SizeUnit)).DefaultIfEmpty()
join stockUnit in context.Sys_Unit on s.UnitID equals stockUnit.UnitID into StockUnits
from stockUnit in StockUnits.DefaultIfEmpty()
from sup in context.Sys_Supplier.AsEnumerable().Where(x => x.SupplierID == Convert.ToInt16(s.SupplierID)).DefaultIfEmpty()
select new PRDChemProdReqItem
{
PackSize = Convert.ToByte(s.PackSize),
PackSizeName = si.SizeName,
SizeUnit = Convert.ToByte(s.SizeUnit),
SizeUnitName = su.UnitName,
SupplierID = s.SupplierID,
SupplierName = sup.SupplierName,
PackQty = Convert.ToInt32(s.PackClosingQty),
StockQty = s.ClosingQty,
IssueUnit = Convert.ToByte(s.UnitID),
IssueUnitName = (stockUnit == null ? null : stockUnit.UnitName)
}).ToList();
return(Data);
}
}
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);
}
}
//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 Requisition LOV
public PRDChemProdReq GetRequisitionDetailsFromFixedStore(int _RequisitionID, byte _RequisitionAt)
{
var FinalStock = DalChemicalStock.ItemWiseStockInSpecificChemicalStore(_RequisitionAt);
var model = new PRDChemProdReq();
var RequisitionInfo = (from r in _context.PRD_ChemProdReq.AsEnumerable()
where r.RequisitionID == _RequisitionID
from u in _context.Users.Where(x => x.UserID == r.ReqRaisedBy).DefaultIfEmpty()
select new PRDChemProdReq
{
RequisitionID = r.RequisitionID,
RequisitionNo = r.RequisitionNo,
JobOrderID = Convert.ToInt16(r.JobOrderID),
JobOrderNo = r.JobOrderNo,
ReqRaisedOn = (Convert.ToDateTime(r.ReqRaisedOn)).ToString("dd'/'MM'/'yyyy"),
ReqRaisedBy = Convert.ToInt16(r.ReqRaisedBy),
ReqRaisedByName = (u == null ? null : (u.FirstName + " " + u.MiddleName + " " + u.LastName)),
RequiredByTime = Convert.ToByte(r.RequiredByTime),
RequisitionCategory = DalCommon.ReturnRequisitionCategory(r.RequisitionCategory),
RequisitionType = DalCommon.ReturnOrderType(r.RequisitionType),
IssueTo = r.RequisitionFrom,
IssueFrom = _RequisitionAt
}).FirstOrDefault();
model.RequisitionInfo = RequisitionInfo;
var RequisitionItemList = (from i in _context.PRD_ChemProdReqItem.AsEnumerable()
where i.RequisitionID == _RequisitionID
from it in _context.Sys_ChemicalItem.Where(x => x.ItemID == i.ItemID).DefaultIfEmpty()
join s in FinalStock on i.ItemID equals s.ItemID into badhon
from item in badhon.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 badhon2
from finalitem in badhon2.DefaultIfEmpty()
from siu in _context.Sys_Unit.Where(x => x.UnitID == i.SizeUnit).DefaultIfEmpty()
from ru in _context.Sys_Unit.Where(x => x.UnitID == i.RequisitionUnit).DefaultIfEmpty()
from ps in _context.Sys_Size.Where(x => x.SizeID == i.PackSize).DefaultIfEmpty()
select new PRDChemProdReqItem
{
ItemID = i.ItemID,
ItemName = (it == null ? null : it.ItemName),
StockQty = (item == null ? 0 : item.ClosingQty),
StockUnitName = (su == null ? null : su.UnitName),
SupplierID = (finalitem == null ? 0 : finalitem.SupplierID),
SupplierName = (finalitem == null ? null : finalitem.SupplierName),
PackSizeName = (ps == null ? null : ps.SizeName),
SizeUnitName = (siu == null ? null : siu.UnitName),
PackQty = Convert.ToInt16(i.PackQty),
RequsitionQty = i.RequsitionQty,
RequisitionUnitName = (ru == null ? null : ru.UnitName),
IssuePackSizeName = "Press F9",
IssueUnitName = "",
//IssuePackSize = Convert.ToByte(i.PackSize),
//IssuePackSizeName = (ps == null ? null : ps.SizeName),
//IssueSizeUnit = Convert.ToByte(i.SizeUnit),
//IssueSizeUnitName = (siu == null ? null : siu.UnitName),
//IssuePackQty = Convert.ToInt16(i.PackQty),
//IssueQty = i.RequsitionQty,
//IssueUnit = i.RequisitionUnit,
//IssueUnitName = (ru == null ? null : ru.UnitName),
ItemSource = "Via Requisition"
}).ToList();
model.RequisitionItemList = RequisitionItemList;
return(model);
}
public List <PRDChemProdReqItem> GetTransactionItemList(long _TransactionID, string _IssueFrom)
{
using (var context = new BLC_DEVEntities())
{
var FinalStock = DalChemicalStock.ItemWiseStockInSpecificChemicalStore(Convert.ToByte(_IssueFrom));
var RequisitionID = (from r in context.INV_StoreTransRequest.AsEnumerable()
where r.TransactionID == _TransactionID
select r.RequisitionID
).FirstOrDefault();
var Data = (from i in context.INV_StoreTransItem.AsEnumerable()
where i.TransactionID == _TransactionID
from r in context.PRD_ChemProdReqItem.Where(x => x.RequisitionID == RequisitionID & x.ItemID == i.ItemID).DefaultIfEmpty()
join reqsize in context.Sys_Size on(r == null?null:r.PackSize) equals reqsize.SizeID into ReqPackSizes
from reqsize in ReqPackSizes.DefaultIfEmpty()
join reqsizeunit in context.Sys_Unit on(r == null ? null : r.SizeUnit) equals reqsizeunit.UnitID into ReqPackSizeUnits
from reqsizeunit in ReqPackSizeUnits.DefaultIfEmpty()
join requnit in context.Sys_Unit on(r == null ? 0 : r.RequisitionUnit) equals requnit.UnitID into ReqUnits
from requnit in ReqUnits.DefaultIfEmpty()
join s in FinalStock on i.ItemID equals s.ItemID into badhon
from item in badhon.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 badhon2
from finalitem in badhon2.DefaultIfEmpty()
from it in context.Sys_ChemicalItem.Where(x => x.ItemID == i.ItemID).DefaultIfEmpty()
from sup in context.Sys_Supplier.Where(x => x.SupplierID == i.SupplierID).DefaultIfEmpty()
from iu in context.Sys_Unit.Where(x => x.UnitID == i.TransactionUnit).DefaultIfEmpty()
from issuesize in context.Sys_Size.Where(x => x.SizeID == i.PackSize).DefaultIfEmpty()
from issuesizeunit in context.Sys_Unit.Where(x => x.UnitID == i.SizeUnit).DefaultIfEmpty()
select new PRDChemProdReqItem
{
TransItemID = i.TransItemID,
ItemID = Convert.ToInt16(i.ItemID),
ItemName = (it == null ? null : it.ItemName),
StockQty = (item == null ? 0 : item.ClosingQty),
StockUnitName = (su == null ? null : su.UnitName),
SupplierID = (finalitem == null ? 0 : finalitem.SupplierID),
SupplierName = (finalitem == null ? null : finalitem.SupplierName),
PackSizeName = (reqsize == null ? null : reqsize.SizeName),
SizeUnitName = (reqsizeunit == null ? null : reqsizeunit.UnitName),
PackQty = (r == null ? 0 : Convert.ToInt16(r.PackQty)),
RequsitionQty = (r == null ? 0 : r.RequsitionQty),
RequisitionUnitName = (requnit == null ? null : requnit.UnitName),
IssuePackSize = Convert.ToByte(i.PackSize),
IssuePackSizeName = (issuesize == null ? null : issuesize.SizeName),
IssueSizeUnit = Convert.ToByte(i.SizeUnit),
IssueSizeUnitName = (issuesizeunit == null ? null : issuesizeunit.UnitName),
IssuePackQty = Convert.ToInt16(i.PackQty),
IssueQty = Convert.ToDecimal(i.TransactionQty),
IssueUnitName = (iu == null ? null : iu.UnitName),
ItemSource = DalCommon.ReturnItemSource(i.ItemSource)
}).ToList();
foreach (var item in Data)
{
if (item.ItemSource == "From Order")
{
item.PackSizeName = null;
item.SizeUnitName = null;
item.PackQty = 0;
item.RequsitionQty = 0;
item.RequisitionUnitName = null;
}
}
return(Data);
}
}