public List <PRQChemFrgnPurcOrdr> GetOrderInformationForLOV() { var Orders = (from o in _context.PRQ_ChemFrgnPurcOrdr.AsEnumerable() where o.RecordStatus == "CNF" & o.OrderState == "RNG" & o.OrderCategory == "FPO" from s1 in _context.Sys_Supplier.Where(x => x.SupplierID == o.SupplierID).DefaultIfEmpty() join sa in _context.Sys_SupplierAddress.Where(x => x.IsActive && !x.IsDelete) on o.SupplierID equals sa.SupplierID into SupplierAddress from sa2 in SupplierAddress.DefaultIfEmpty() from s2 in _context.Sys_Supplier.Where(x => x.SupplierID == o.LocalAgent).DefaultIfEmpty() join la in _context.Sys_SupplierAddress.Where(x => x.IsActive && !x.IsDelete) on o.LocalAgent equals la.SupplierID into LocalAgentAddress from la2 in LocalAgentAddress.DefaultIfEmpty() from s3 in _context.Sys_Supplier.Where(x => x.SupplierID == o.ForeignAgent).DefaultIfEmpty() join fa in _context.Sys_SupplierAddress.Where(x => x.IsActive && !x.IsDelete) on o.ForeignAgent equals fa.SupplierID into ForeignAgentAddress from fa2 in ForeignAgentAddress.DefaultIfEmpty() orderby o.OrderID descending select new PRQChemFrgnPurcOrdr { OrderID = o.OrderID, OrderNo = o.OrderNo, OrderTo = DalCommon.ReturnOrderTo(o.OrderTo), OrderDate = (Convert.ToDateTime(o.OrderDate)).ToString("dd'/'MM'/'yyyy"), OrderType = DalCommon.ReturnOrderType(o.OrderType), SupplierID = Convert.ToInt32((s1 == null ? null : (s1.SupplierID).ToString())), SupplierName = (s1 == null ? null : s1.SupplierName), SupplierCode = (s1 == null ? null : s1.SupplierCode), SupplierAddress = (sa2 == null? null: sa2.Address), SupplierContactNumber = (sa2 == null ? null : sa2.ContactNumber), LocalAgent = Convert.ToInt32((la2 == null ? null : (la2.SupplierID).ToString())), LocalAgentName = (s2 == null ? null : s2.SupplierName), LocalAgentCode = (s2 == null ? null : s2.SupplierCode), LocalAgentAddress = (la2 == null ? null : la2.Address), LocalAgentContactNumber = (la2 == null ? null : la2.ContactNumber), ForeignAgent = Convert.ToInt32((s3 == null ? null : (s3.SupplierID).ToString())), ForeignAgentName = (s3 == null ? null : s3.SupplierName), ForeignAgentCode = (s3 == null ? null : s3.SupplierCode), ForeignAgentAddress = (fa2 == null ? null : fa2.Address), ForeignAgentContactNumber = (fa2 == null ? null : fa2.ContactNumber), OrderNote = o.OrderNote }).ToList(); return(Orders); }
//For Requisition LOV public List <PRDChemProdReq> GetRequisitionFromFixedStore(string _RequisitionFrom) { var Data = (from r in _context.PRD_ChemProdReq.AsEnumerable() where (r.RequisitionTo).ToString() == _RequisitionFrom && r.RequisitionState == "RNG" && r.RecordStatus == "APV" join s in _context.SYS_Store on r.RequisitionFrom equals s.StoreID into Stores from s in Stores.DefaultIfEmpty() orderby r.RequisitionID descending select new PRDChemProdReq { RequisitionID = r.RequisitionID, RequisitionNo = r.RequisitionNo, RequisitionCategory = DalCommon.ReturnRequisitionCategory(r.RequisitionCategory), RequisitionType = DalCommon.ReturnOrderType(r.RequisitionType), RequiredByTime = Convert.ToByte(r.RequiredByTime), RequisitionFrom = r.RequisitionFrom, RequisitionFromName = (s == null? "": s.StoreName), ReqRaisedOn = Convert.ToDateTime(r.ReqRaisedOn).ToString("dd'/'MM'/'yyyy"), }).ToList(); return(Data); }
public INVStoreTrans GetTransactionDetailsAfterSearch(long _TransactionID) { var model = new INVStoreTrans(); using (_context) { var RequisitionID = (from r in _context.INV_StoreTransRequest where r.TransactionID == _TransactionID select r.RequisitionID ).FirstOrDefault(); if (RequisitionID != null) { var TransactionInfo = (from t in _context.INV_StoreTrans.AsEnumerable() where t.TransactionID == _TransactionID join req in _context.INV_StoreTransRequest on t.TransactionID equals req.TransactionID into Requests from req in Requests.DefaultIfEmpty() //from req in _context.INV_StoreTransRequest.Where(x => x.TransactionID == _TransactionID).DefaultIfEmpty() join r in _context.PRD_ChemProdReq on RequisitionID equals r.RequisitionID into Requisitions from r in Requisitions.DefaultIfEmpty() //from r in _context.PRD_ChemProdReq.Where(x => x.RequisitionID == RequisitionID).DefaultIfEmpty() join u in _context.Users on(r == null ? null : r.ReqRaisedBy) equals u.UserID into UserInfo from u in UserInfo.DefaultIfEmpty() //from u in _context.Users.Where(x => x.UserID == r.ReqRaisedBy).DefaultIfEmpty() select new INVStoreTrans { TransactionID = t.TransactionID, TransactionNo = t.TransactionNo, IssueFrom = t.TransactionFrom, IssueTo = t.TransactionTo, TransactionDate = (Convert.ToDateTime(t.TransactionDate)).ToString("dd'/'MM'/'yyyy"), RecordStatus = t.RecordStatus, RequisitionID = (r == null ? 0 : r.RequisitionID), RequisitionNo = (r == null ? null : r.RequisitionNo), RequisitionCategory = (r == null ? null : DalCommon.ReturnRequisitionCategory(r.RequisitionCategory)), RequisitionType = (r == null ? null : DalCommon.ReturnOrderType(r.RequisitionType)), RequiredByTime = (r == null ? null : (r.RequiredByTime).ToString()), ReqRaisedOn = (r == null ? null : (Convert.ToDateTime(r.ReqRaisedOn)).ToString("dd'/'MM'/'yyyy")), ReqRaisedByName = (u == null ? null : (u.FirstName + " " + u.MiddleName + " " + u.LastName)), JobOrderNo = (r == null ? null : r.JobOrderNo), Remark = (req == null ? null : req.Remark), TransactionType = t.TransactionType }).FirstOrDefault(); model.TransactionInfo = TransactionInfo; var TransactionItemList = GetTransactionItemList(_TransactionID, TransactionInfo.IssueFrom); model.TransactionItemList = TransactionItemList; return(model); } else { var TransactionInfo = (from t in _context.INV_StoreTrans.AsEnumerable() where t.TransactionID == _TransactionID join req in _context.INV_StoreTransRequest on t.TransactionID equals req.TransactionID into Requests from req in Requests.DefaultIfEmpty() //from req in _context.INV_StoreTransRequest.Where(x => x.TransactionID == _TransactionID).DefaultIfEmpty() select new INVStoreTrans { TransactionID = t.TransactionID, TransactionNo = t.TransactionNo, IssueFrom = t.TransactionFrom, IssueTo = t.TransactionTo, TransactionDate = (Convert.ToDateTime(t.TransactionDate)).ToString("dd'/'MM'/'yyyy"), RecordStatus = t.RecordStatus, Remark = (req == null ? null : req.Remark), ReqToDate = (Convert.ToDateTime(req.ReqToDate)).ToString("dd'/'MM'/'yyyy"), ReqFromDate = (Convert.ToDateTime(req.ReqFromDate)).ToString("dd'/'MM'/'yyyy"), TransactionType = t.TransactionType }).FirstOrDefault(); model.TransactionInfo = TransactionInfo; if (TransactionInfo.TransactionType == "ADI") { var TransactionItemList = GetTransactionItemListForStockAdjust(_TransactionID, TransactionInfo.IssueFrom); model.TransactionItemList = TransactionItemList; } else { var TransactionItemList = GetTransactionItemList(_TransactionID, TransactionInfo.IssueFrom); model.TransactionItemList = TransactionItemList; } return(model); } } }
// 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); }