public IQueryable <ComboBoxResult> GetCustomProducts(int Id, string term) { var MaterialPlan = new MaterialPlanHeaderService(_unitOfWork).Find(Id); var settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(MaterialPlan.DocTypeId, MaterialPlan.DivisionId, MaterialPlan.SiteId); string[] ProductTypes = null; if (!string.IsNullOrEmpty(settings.filterProductTypes)) { ProductTypes = settings.filterProductTypes.Split(",".ToCharArray()); } else { ProductTypes = new string[] { "NA" }; } string[] Products = null; if (!string.IsNullOrEmpty(settings.filterProducts)) { Products = settings.filterProducts.Split(",".ToCharArray()); } else { Products = new string[] { "NA" }; } string[] ProductGroups = null; if (!string.IsNullOrEmpty(settings.filterProductGroups)) { ProductGroups = settings.filterProductGroups.Split(",".ToCharArray()); } else { ProductGroups = new string[] { "NA" }; } return(from p in db.Product where (string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains(p.ProductGroup.ProductTypeId.ToString())) && (string.IsNullOrEmpty(settings.filterProducts) ? 1 == 1 : Products.Contains(p.ProductId.ToString())) && (string.IsNullOrEmpty(settings.filterProductGroups) ? 1 == 1 : ProductGroups.Contains(p.ProductGroupId.ToString())) && (string.IsNullOrEmpty(term) ? 1 == 1 : p.ProductName.ToLower().Contains(term.ToLower())) orderby p.ProductName select new ComboBoxResult { id = p.ProductId.ToString(), text = p.ProductName, }); }
public IEnumerable <SaleOrderLineListViewModel> GetSaleOrdersForDocumentType(int HeaderId, string term) { //return (from p in db.SaleOrderHeader // where DocTypeIds.Contains(p.DocTypeId.ToString()) // orderby p.DocDate descending, p.DocNo descending // select new SaleOrderLineListViewModel // { // DocNo = p.DocNo, // SaleOrderHeaderId = p.SaleOrderHeaderId // } // ); var Header = new MaterialPlanHeaderService(_unitOfWork).Find(HeaderId); var Settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId); SqlParameter SqlParameterDocType = new SqlParameter("@PlanningDocumentType", Header.DocTypeId); SqlParameter SqlParameterSite = new SqlParameter("@Site", Header.SiteId); SqlParameter SqlParameterDivision = new SqlParameter("@Division", Header.DivisionId); SqlParameter SqlParameterBuyer = new SqlParameter("@BuyerId", Header.BuyerId.HasValue ? Header.BuyerId : (object)DBNull.Value); string ProcName = Settings.PendingProdOrderList; if (string.IsNullOrEmpty(ProcName)) { throw new Exception("Pending ProdOrders not configured"); } IEnumerable <PendingSaleOrderFromProc> CalculationLineList = db.Database.SqlQuery <PendingSaleOrderFromProc>("" + ProcName + " @PlanningDocumentType, @Site, @Division, @BuyerId", SqlParameterDocType, SqlParameterSite, SqlParameterDivision, SqlParameterBuyer).ToList(); var list = (from p in CalculationLineList where p.SaleOrderNo.ToLower().Contains(term.ToLower()) group new { p } by p.SaleOrderHeaderId into g select new SaleOrderLineListViewModel { DocNo = g.Max(m => m.p.SaleOrderNo), SaleOrderHeaderId = g.Key } ); return(list.ToList()); }
public IEnumerable <MaterialPlanForSaleOrderViewModel> GetSaleOrdersForFilters(MaterialPlanForLineFilterViewModel svm) { string[] ProductIdArr = null; if (!string.IsNullOrEmpty(svm.ProductId)) { ProductIdArr = svm.ProductId.Split(",".ToCharArray()); } else { ProductIdArr = new string[] { "NA" }; } string[] SaleOrderIdArr = null; if (!string.IsNullOrEmpty(svm.SaleOrderHeaderId)) { SaleOrderIdArr = svm.SaleOrderHeaderId.Split(",".ToCharArray()); } else { SaleOrderIdArr = new string[] { "NA" }; } string[] ProductGroupIdArr = null; if (!string.IsNullOrEmpty(svm.ProductGroupId)) { ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray()); } else { ProductGroupIdArr = new string[] { "NA" }; } string[] Dimension1IdArr = null; if (!string.IsNullOrEmpty(svm.Dimension1Id)) { Dimension1IdArr = svm.Dimension1Id.Split(",".ToCharArray()); } else { Dimension1IdArr = new string[] { "NA" }; } string[] Dimension2IdArr = null; if (!string.IsNullOrEmpty(svm.Dimension2Id)) { Dimension2IdArr = svm.Dimension2Id.Split(",".ToCharArray()); } else { Dimension2IdArr = new string[] { "NA" }; } string[] BuyerIdArr = null; if (!string.IsNullOrEmpty(svm.BuyerId)) { BuyerIdArr = svm.BuyerId.Split(",".ToCharArray()); } else { BuyerIdArr = new string[] { "NA" }; } var Header = new MaterialPlanHeaderService(_unitOfWork).Find(svm.MaterialPlanHeaderId); var settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId); string ProcName = settings.PendingProdOrderList; if (string.IsNullOrEmpty(ProcName)) { throw new Exception("Pending ProdOrders Not Configured"); } SqlParameter SqlParameterDocType = new SqlParameter("@PlanningDocumentType", svm.DocTypeId); SqlParameter SqlParameterSite = new SqlParameter("@Site", Header.SiteId); SqlParameter SqlParameterDivision = new SqlParameter("@Division", Header.DivisionId); SqlParameter SqlParameterBuyer = new SqlParameter("@BuyerId", (Header.BuyerId.HasValue ? Header.BuyerId : (object)DBNull.Value)); IEnumerable <PendingSaleOrderFromProc> PendingSaleOrders = db.Database.SqlQuery <PendingSaleOrderFromProc>(" " + ProcName + " @PlanningDocumentType, @Site, @Division, @BuyerId", SqlParameterDocType, SqlParameterSite, SqlParameterDivision, SqlParameterBuyer).ToList(); //var ProductIds=(PendingSaleOrders.Select(m=>m.ProductId)).ToArray(); //var ProductGroupIds = (from p in db.Product.Where(m => ProductIdArr.Contains(m.ProductId.ToString())) // select p.ProductGroupId).ToArray(); var resu = (from p in PendingSaleOrders where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) && (string.IsNullOrEmpty(svm.SaleOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleOrderHeaderId.ToString())) && (string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(p.ProductGroupId.ToString())) && (string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dimension1IdArr.Contains(p.Dimension1Id.ToString())) && (string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dimension2IdArr.Contains(p.Dimension2Id.ToString())) && (string.IsNullOrEmpty(svm.BuyerId) ? 1 == 1 : BuyerIdArr.Contains(p.BuyerId.ToString())) && p.BalanceQty > 0 orderby p.Sr select new MaterialPlanForSaleOrderViewModel { BalanceQtyForPlan = p.BalanceQty, Qty = p.BalanceQty, SaleOrderDocNo = p.SaleOrderNo, ProductId = p.ProductId, ProductName = p.ProductName, Dimension1Id = p.Dimension1Id, Dimension1Name = p.Dimension1Name, Dimension2Id = p.Dimension2Id, Dimension2Name = p.Dimension2Name, MaterialPlanHeaderId = svm.MaterialPlanHeaderId, SaleOrderLineId = p.SaleOrderLineId, Specification = p.Specification, UnitName = p.UnitName, BomDetailExists = p.BomDetailExists, }).ToList(); return(resu); }
public IEnumerable <MaterialPlanForSaleOrderViewModel> GetProdOrdersForFilters(MaterialPlanLineForProductionFilterViewModel svm) { string[] ProductIdArr = null; if (!string.IsNullOrEmpty(svm.ProductId)) { ProductIdArr = svm.ProductId.Split(",".ToCharArray()); } else { ProductIdArr = new string[] { "NA" }; } string[] SaleOrderIdArr = null; if (!string.IsNullOrEmpty(svm.ProdOrderHeaderId)) { SaleOrderIdArr = svm.ProdOrderHeaderId.Split(",".ToCharArray()); } else { SaleOrderIdArr = new string[] { "NA" }; } string[] ProductGroupIdArr = null; if (!string.IsNullOrEmpty(svm.ProductGroupId)) { ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray()); } else { ProductGroupIdArr = new string[] { "NA" }; } var Header = new MaterialPlanHeaderService(_unitOfWork).Find(svm.MaterialPlanHeaderId); string ProcName = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId).PendingProdOrderList; SqlParameter SqlParameterProductId = new SqlParameter("@MaterialPlanHeaderId", svm.MaterialPlanHeaderId); IEnumerable <ProdOrderBalanceViewModel> StockAvailableForPacking = db.Database.SqlQuery <ProdOrderBalanceViewModel>("" + ProcName + " @MaterialPlanHeaderId", SqlParameterProductId).ToList(); var ProductIds = StockAvailableForPacking.Select(m => m.ProductId).ToArray(); var ProdOrderLineIds = StockAvailableForPacking.Select(m => m.ProdOrderLineId).ToArray(); var temp1 = from p in StockAvailableForPacking where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) && (string.IsNullOrEmpty(svm.ProdOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.ProdOrderHeaderId.ToString())) && (string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(p.ProductGroupId.ToString())) && p.BalanceQty > 0 && (svm.DocDate.HasValue ? p.ProdOrderDate.Date <= svm.DocDate.Value.Date : 1 == 1) select new MaterialPlanForSaleOrderViewModel { BalanceQtyForPlan = p.BalanceQty, Qty = p.BalanceQty, ProdOrderDocNo = p.ProdOrderNo, ProductId = p.ProductId, ProductName = p.ProductName, MaterialPlanHeaderId = svm.MaterialPlanHeaderId, ProdOrderLineId = p.ProdOrderLineId, Dimension1Id = p.Dimension1Id, Dimension2Id = p.Dimension2Id, Dimension1Name = p.Dimension1Name, Dimension2Name = p.Dimension2Name, UnitName = p.UnitName, ProcessId = p.ProcessId, ProcessName = p.ProcessName, BomDetailExists = p.IsBomExist, }; var temp2 = (from p in StockAvailableForPacking where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) && (string.IsNullOrEmpty(svm.ProdOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.ProdOrderHeaderId.ToString())) && (string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(p.ProductGroupId.ToString())) && p.BalanceQty > 0 && (svm.DocDate.HasValue ? p.ProdOrderDate.Date <= svm.DocDate.Value.Date : 1 == 1) select new MaterialPlanForSaleOrderViewModel { BalanceQtyForPlan = p.BalanceQty, Qty = p.BalanceQty, ProdOrderDocNo = p.ProdOrderNo, ProductId = p.ProductId, ProductName = p.ProductName, MaterialPlanHeaderId = svm.MaterialPlanHeaderId, ProdOrderLineId = p.ProdOrderLineId, Dimension1Id = p.Dimension1Id, Dimension2Id = p.Dimension2Id, Dimension1Name = p.Dimension1Name, Dimension2Name = p.Dimension2Name, UnitName = p.UnitName, ProcessId = p.ProcessId, ProcessName = p.ProcessName, BomDetailExists = p.IsBomExist, }).ToList(); var DocTypeIds = StockAvailableForPacking.Select(m => m.DocTypeId).ToArray(); return(temp2); }