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); }
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 ValidationMsg ConfirmIssueToProductionTransaction(long _TransactionID, string _CheckComment, int _UserID) { ValidationMsg Msg = new ValidationMsg(); Msg = DalChemicalStock.SubtractChemicalFromChemicalStore(_TransactionID, _CheckComment, _UserID, "Store Transfer Issue"); return(Msg); }
// 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); } }
public ValidationMsg ConfirmIssueToProductionTransaction(long _TransactionID, string _CheckComment, int _UserID) { ValidationMsg Msg = new ValidationMsg(); try { using (TransactionScope Transaction = new TransactionScope()) { Msg = DalChemicalStock.SubtractChemicalFromChemicalStore(_TransactionID, _CheckComment, _UserID, "Issue To Production"); if (Msg.ReturnId != 0) { Msg = DalChemicalStock.AddChemicalInProductionFloor(_TransactionID, _CheckComment, _UserID, "Issue To Production"); } if (Msg.ReturnId == 1) { using (var context = new BLC_DEVEntities()) { var RequisitionList = (from r in context.INV_StoreTransRequest where r.TransactionID == _TransactionID select r).ToList(); foreach (var rec in RequisitionList) { var Requisition = (from r in context.PRD_ChemProdReq where r.RequisitionID == rec.RequisitionID select r).FirstOrDefault(); Requisition.RequisitionState = "CMP"; } context.SaveChanges(); } } Transaction.Complete(); } return(Msg); } catch (Exception e) { Msg.ReturnId = 0; Msg.Msg = "Confirmation Failed"; return(Msg); } }
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 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); } }