public List <SysChemicalItem> GetChemicalItemWithStock(byte _RequisitionFrom, byte _RequisitionTo)
{
var FinalStock = DalChemicalStock.ItemWiseStockInSpecificChemicalStore(_RequisitionTo);
var FinalStockProductionFloor = DalChemicalStock.ItemWiseStockInSpecificProductionFloor(_RequisitionFrom);
var Data = (from c in _context.Sys_ChemicalItem.AsEnumerable()
where c.IsActive == true
join fs in FinalStock on c.ItemID equals fs.ItemID into FinalStocks
from fs in FinalStocks.DefaultIfEmpty()
join ps in FinalStockProductionFloor on c.ItemID equals ps.ItemID into ProductionStock
from ps in ProductionStock.DefaultIfEmpty()
join it in _context.Sys_ItemType on c.ItemTypeID equals it.ItemTypeID into Items
from it2 in Items.DefaultIfEmpty()
orderby c.ItemName
select new SysChemicalItem
{
ItemID = c.ItemID,
ItemName = c.ItemName,
ItemCategory = DalCommon.ReturnChemicalItemCategory(c.ItemCategory),
ItemTypeID = c.ItemTypeID,
ItemTypeName = (it2 == null ? null : it2.ItemTypeName),
StockQty = (fs == null ? 0 : fs.ClosingQty),
SafetyStock = ps == null ? 0 : Convert.ToInt32(ps.ClosingQty) //Actually Production Stock
}).ToList();
return(Data);
}
public List <SysChemicalItem> GetAvailableChemicalInStock(byte _RequisitionAt)
{
var FinalStock = DalChemicalStock.ItemWiseStockInSpecificChemicalStore(_RequisitionAt);
var data = (from s in FinalStock
where s.ClosingQty > 0
join c in _context.Sys_ChemicalItem on s.ItemID equals c.ItemID into Chemicals
from c in Chemicals.DefaultIfEmpty()
join i in _context.Sys_ItemType on(c.ItemTypeID == null? null: c.ItemTypeID) equals i.ItemTypeID into ItemTypes
from i in ItemTypes.DefaultIfEmpty()
select new SysChemicalItem
{
ItemID = Convert.ToInt32(s.ItemID),
ItemName = c == null ? null : c.ItemName,
//ItemCategory = DalCommon.ReturnChemicalItemCategory(c.ItemCategory),
//ItemTypeName = DalCommon.GetItemTypeName(Convert.ToByte(c.ItemTypeID)),
ItemCategory = c.ItemCategory,
ItemTypeName = (i == null? null: i.ItemTypeName),
StockQty = s.ClosingQty
}).ToList();
foreach (var item in data)
{
item.ItemCategory = DalCommon.ReturnChemicalItemCategory(item.ItemCategory);
}
return(data);
}
// After Save & Update to get fresh data into page grid
public List <PRDChemProdReqItem> GetRequisitionItemList(int _RequisitionID, byte _StoreID)
{
using (var context = new BLC_DEVEntities())
{
var FinalStock = DalChemicalStock.ItemWiseStockInSpecificChemicalStore(_StoreID);
var Data = (from r in context.PRD_ChemProdReqItem.AsEnumerable()
where r.RequisitionID == _RequisitionID
from i in context.Sys_ChemicalItem.Where(x => x.ItemID == r.ItemID).DefaultIfEmpty()
from u in context.Sys_Unit.Where(x => x.UnitID == r.RequiredUnit).DefaultIfEmpty()
from su in context.Sys_Unit.Where(x => x.UnitID == r.SizeUnit).DefaultIfEmpty()
from rq in context.Sys_Unit.Where(x => x.UnitID == r.RequisitionUnit).DefaultIfEmpty()
from ap in context.Sys_Unit.Where(x => x.UnitID == r.ApproveUnit).DefaultIfEmpty()
from size in context.Sys_Size.Where(x => x.SizeID == r.PackSize).DefaultIfEmpty()
join s in FinalStock on i.ItemID equals s.ItemID into StockInfo
from item in StockInfo.DefaultIfEmpty()
join sup in context.Sys_Supplier on(item == null ? 0 : item.SupplierID) equals sup.SupplierID into badhon2
from finalitem in badhon2.DefaultIfEmpty()
select new PRDChemProdReqItem
{
RequisitionItemID = r.RequisitionItemID,
ItemID = r.ItemID,
ItemName = (i == null ? null : i.ItemName),
RequiredQty = Convert.ToDecimal(r.RequiredQty),
RequiredUnit = Convert.ToByte(r.RequiredUnit),
RequiredUnitName = (u == null ? null : u.UnitName),
PackSize = Convert.ToByte(r.PackSize),
PackSizeName = (size == null ? null : size.SizeName),
SizeUnit = Convert.ToByte(r.SizeUnit),
SizeUnitName = (su == null ? null : su.UnitName),
PackQty = Convert.ToInt32(r.PackQty),
RequsitionQty = r.RequsitionQty,
RequisitionUnit = r.RequisitionUnit,
RequisitionUnitName = (rq == null ? null : rq.UnitName),
ApproveQty = Convert.ToDecimal(r.ApproveQty),
ApproveUnit = Convert.ToByte(r.ApproveUnit),
ApproveUnitName = (ap == null ? null : ap.UnitName),
StockQty = (item == null ? 0 : (item.ClosingQty)),
//StockUnitName = (su == null ? null : su.UnitName),
SupplierID = (finalitem == null ? 0 : finalitem.SupplierID),
SupplierName = (finalitem == null ? null : finalitem.SupplierName)
}).ToList();
return(Data);
}
}
// For Recipe LOV
public List <PRDChemProdReqItem> GetRecipeItemListForFixedRecipe(int _RecipeID, byte _RequisitionTo, byte _RequisitionFrom)
{
try
{
var FinalStock = DalChemicalStock.ItemWiseStockInSpecificChemicalStore(_RequisitionTo);
var FinalStockProductionFloor = DalChemicalStock.ItemWiseStockInSpecificProductionFloor(_RequisitionFrom);
if (FinalStock != null)
{
var Data = (from s in _context.PRD_RecipeItem.AsEnumerable()
where s.RecipeID == _RecipeID
from i in _context.Sys_ChemicalItem.Where(x => x.ItemID == s.ItemID).DefaultIfEmpty()
from u in _context.Sys_Unit.Where(x => x.UnitID == s.UnitID).DefaultIfEmpty()
join st in FinalStock on i.ItemID equals st.ItemID into badhon
from item in badhon.DefaultIfEmpty()
join sup in _context.Sys_Supplier on(item == null ? 0 : item.SupplierID) equals sup.SupplierID into badhon2
from finalitem in badhon2.DefaultIfEmpty()
join ps in FinalStockProductionFloor on i.ItemID equals ps.ItemID into ProductionStock
from ps in ProductionStock.DefaultIfEmpty()
select new PRDChemProdReqItem
{
ItemID = s.ItemID,
ItemName = (i == null ? null : i.ItemName),
RequiredQty = s.RequiredQty,
RequiredUnit = s.UnitID,
RequiredUnitName = (u == null ? null : u.UnitName),
RequsitionQty = s.RequiredQty,
ApproveQty = s.RequiredQty,
ApproveUnit = s.UnitID,
ApproveUnitName = (u == null ? null : u.UnitName),
PackSizeName = "",
PackQty = 0,
SizeUnitName = "",
RequisitionUnit = s.UnitID,
RequisitionUnitName = (u == null ? null : u.UnitName),
StockQty = (item == null ? 0 : item.ClosingQty),
//StockUnitName = (su == null ? null : su.UnitName),
ProductionStock = (ps == null? "0": ps.ClosingQty.ToString()),
SupplierID = (finalitem == null ? 0 : finalitem.SupplierID),
SupplierName = (finalitem == null ? null : finalitem.SupplierName),
ItemSource = "Via Requisition"
}).ToList();
return(Data);
}
else
{
var Data = (from s in _context.PRD_RecipeItem.AsEnumerable()
where s.RecipeID == _RecipeID
from i in _context.Sys_ChemicalItem.Where(x => x.ItemID == s.ItemID).DefaultIfEmpty()
from u in _context.Sys_Unit.Where(x => x.UnitID == s.UnitID).DefaultIfEmpty()
join ps in FinalStockProductionFloor on i.ItemID equals ps.ItemID into ProductionStock
from ps in ProductionStock.DefaultIfEmpty()
select new PRDChemProdReqItem
{
ItemID = s.ItemID,
ItemName = (i == null ? null : i.ItemName),
RequiredQty = s.RequiredQty,
RequiredUnit = s.UnitID,
RequiredUnitName = (u == null ? null : u.UnitName),
ProductionStock = (ps == null ? "0" : ps.ClosingQty.ToString()),
RequsitionQty = s.RequiredQty,
ApproveQty = s.RequiredQty,
ApproveUnit = s.UnitID,
ApproveUnitName = (u == null ? null : u.UnitName),
PackSizeName = "",
PackQty = 0,
SizeUnitName = "",
RequisitionUnit = s.UnitID,
RequisitionUnitName = (u == null ? null : u.UnitName),
StockQty = 0,
ItemSource = "Via Requisition"
}).ToList();
return(Data);
}
}
catch (Exception e)
{
return(null);
}
}
public List <PRDChemProdReqItem> RecalculateItemQuantity(int _RecipeID, decimal _Factor, byte _StoreID)
{
var FinalStock = DalChemicalStock.ItemWiseStockInSpecificChemicalStore(_StoreID);
if (FinalStock.Count > 0)
{
var Data = (from s in _context.PRD_RecipeItem.AsEnumerable()
where s.RecipeID == _RecipeID
from i in _context.Sys_ChemicalItem.Where(x => x.ItemID == s.ItemID).DefaultIfEmpty()
from u in _context.Sys_Unit.Where(x => x.UnitID == s.UnitID).DefaultIfEmpty()
join st in FinalStock on i.ItemID equals st.ItemID into badhon
from item in badhon.DefaultIfEmpty()
join sup in _context.Sys_Supplier on(item == null ? 0 : item.SupplierID) equals sup.SupplierID into badhon2
from finalitem in badhon2.DefaultIfEmpty()
select new PRDChemProdReqItem
{
ItemID = s.ItemID,
ItemName = (i == null ? null : i.ItemName),
RequiredQty = s.RequiredQty,
RequiredUnit = s.UnitID,
RequiredUnitName = (u == null ? null : u.UnitName),
RequsitionQty = s.RequiredQty,
ApproveQty = s.RequiredQty,
ApproveUnit = s.UnitID,
ApproveUnitName = (u == null ? null : u.UnitName),
PackSizeName = "",
PackQty = 0,
SizeUnitName = "",
RequisitionUnit = s.UnitID,
RequisitionUnitName = (u == null ? null : u.UnitName),
StockQty = (item == null ? 0 : item.ClosingQty),
//StockUnitName = (su == null ? null : su.UnitName),
SupplierID = (finalitem == null ? 0 : finalitem.SupplierID),
SupplierName = (finalitem == null ? null : finalitem.SupplierName),
ItemSource = "Via Requisition"
}).ToList();
foreach (var item in Data)
{
var Qty = Decimal.Round(Convert.ToDecimal(item.RequiredQty) * (_Factor), 2);
item.RequiredQty = Qty;
item.RequsitionQty = Qty;
item.ApproveQty = Qty;
}
return(Data);
}
else
{
var Data = (from s in _context.PRD_RecipeItem.AsEnumerable()
where s.RecipeID == _RecipeID
from i in _context.Sys_ChemicalItem.Where(x => x.ItemID == s.ItemID).DefaultIfEmpty()
from u in _context.Sys_Unit.Where(x => x.UnitID == s.UnitID).DefaultIfEmpty()
select new PRDChemProdReqItem
{
ItemID = s.ItemID,
ItemName = (i == null ? null : i.ItemName),
RequiredQty = s.RequiredQty,
RequiredUnit = s.UnitID,
RequiredUnitName = (u == null ? null : u.UnitName),
RequsitionQty = s.RequiredQty,
ApproveQty = s.RequiredQty,
ApproveUnit = s.UnitID,
ApproveUnitName = (u == null ? null : u.UnitName),
PackSizeName = "",
PackQty = 0,
SizeUnitName = "",
RequisitionUnit = s.UnitID,
RequisitionUnitName = (u == null ? null : u.UnitName),
StockQty = 0,
ItemSource = "Via Requisition"
}).ToList();
foreach (var item in Data)
{
var Qty = Decimal.Round(Convert.ToDecimal(item.RequiredQty) * (_Factor), 2);
item.RequiredQty = Qty;
item.RequsitionQty = Qty;
item.ApproveQty = Qty;
}
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);
}
}