public IQueryable <ComboBoxResult> GetPendingProductsForProdOrderCancel(int filter, string term)//DocTypeId { var HEader = new ProdOrderCancelHeaderService(_unitOfWork).Find(filter); var settings = new ProdOrderSettingsService(_unitOfWork).GetProdOrderSettingsForDocument(HEader.DocTypeId, HEader.DivisionId, HEader.SiteId); string[] contraDocTypes = null; if (!string.IsNullOrEmpty(settings.filterContraDocTypes)) { contraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray()); } else { contraDocTypes = new string[] { "NA" }; } string[] contraSites = null; if (!string.IsNullOrEmpty(settings.filterContraSites)) { contraSites = settings.filterContraSites.Split(",".ToCharArray()); } else { contraSites = new string[] { "NA" }; } string[] contraDivisions = null; if (!string.IsNullOrEmpty(settings.filterContraDivisions)) { contraDivisions = settings.filterContraDivisions.Split(",".ToCharArray()); } else { contraDivisions = new string[] { "NA" }; } string[] filterProducts = null; if (!string.IsNullOrEmpty(settings.filterProducts)) { filterProducts = settings.filterProducts.Split(",".ToCharArray()); } else { filterProducts = new string[] { "NA" }; } string[] filterProductTypes = null; if (!string.IsNullOrEmpty(settings.filterProductTypes)) { filterProductTypes = settings.filterProductTypes.Split(",".ToCharArray()); } else { filterProductTypes = new string[] { "NA" }; } string[] filterProductGroups = null; if (!string.IsNullOrEmpty(settings.filterProductGroups)) { filterProductGroups = settings.filterProductGroups.Split(",".ToCharArray()); } else { filterProductGroups = new string[] { "NA" }; } int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var Query = from p in db.ViewProdOrderBalance join t in db.ProdOrderHeader on p.ProdOrderHeaderId equals t.ProdOrderHeaderId into ProdTable from ProTab in ProdTable.DefaultIfEmpty() join Prod in db.Product on p.ProductId equals Prod.ProductId where p.BalanceQty > 0 select new { ProductName = Prod.ProductName, DocTypeId = p.DocTypeId, ProductId = p.ProductId, ProductGroupId = Prod.ProductGroupId, ProductTypeId = Prod.ProductGroup.ProductTypeId, SiteId = ProTab.SiteId, DivisionId = ProTab.DivisionId, }; if (!string.IsNullOrEmpty(term)) { Query = Query.Where(m => m.ProductName.ToLower().Contains(term.ToLower())); } if (!string.IsNullOrEmpty(settings.filterContraDocTypes)) { Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString())); } if (!string.IsNullOrEmpty(settings.filterProducts)) { Query = Query.Where(m => filterProducts.Contains(m.ProductId.ToString())); } if (!string.IsNullOrEmpty(settings.filterProductGroups)) { Query = Query.Where(m => filterProductGroups.Contains(m.ProductGroupId.ToString())); } if (!string.IsNullOrEmpty(settings.filterProductTypes)) { Query = Query.Where(m => filterProductTypes.Contains(m.ProductTypeId.ToString())); } if (!string.IsNullOrEmpty(settings.filterContraSites)) { Query = Query.Where(m => contraSites.Contains(m.SiteId.ToString())); } else { Query = Query.Where(m => m.SiteId == CurrentSiteId); } if (!string.IsNullOrEmpty(settings.filterContraDivisions)) { Query = Query.Where(m => contraDivisions.Contains(m.DivisionId.ToString())); } else { Query = Query.Where(m => m.DivisionId == CurrentDivisionId); } return(from p in Query group p by p.ProductId into g orderby g.Max(m => m.ProductName) select new ComboBoxResult { id = g.Key.ToString(), text = g.Max(m => m.ProductName), }); }
public IEnumerable <ProdOrderLineBalance> GetPendingProdOrdersWithPatternMatch(int HeaderId, string term, int Limiter) { var ProdordercancelHeader = new ProdOrderCancelHeaderService(_unitOfWork).Find(HeaderId); var settings = new ProdOrderSettingsService(_unitOfWork).GetProdOrderSettingsForDocument(ProdordercancelHeader.DocTypeId, ProdordercancelHeader.DivisionId, ProdordercancelHeader.SiteId); string[] contraDocTypes = null; if (!string.IsNullOrEmpty(settings.filterContraDocTypes)) { contraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray()); } else { contraDocTypes = new string[] { "NA" }; } string[] contraSites = null; if (!string.IsNullOrEmpty(settings.filterContraSites)) { contraSites = settings.filterContraSites.Split(",".ToCharArray()); } else { contraSites = new string[] { "NA" }; } string[] contraDivisions = null; if (!string.IsNullOrEmpty(settings.filterContraDivisions)) { contraDivisions = settings.filterContraDivisions.Split(",".ToCharArray()); } else { contraDivisions = new string[] { "NA" }; } string[] filterProducts = null; if (!string.IsNullOrEmpty(settings.filterProducts)) { filterProducts = settings.filterProducts.Split(",".ToCharArray()); } else { filterProducts = new string[] { "NA" }; } string[] filterProductTypes = null; if (!string.IsNullOrEmpty(settings.filterProductTypes)) { filterProductTypes = settings.filterProductTypes.Split(",".ToCharArray()); } else { filterProductTypes = new string[] { "NA" }; } string[] filterProductGroups = null; if (!string.IsNullOrEmpty(settings.filterProductGroups)) { filterProductGroups = settings.filterProductGroups.Split(",".ToCharArray()); } else { filterProductGroups = new string[] { "NA" }; } int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var Qry = (from p in db.ViewProdOrderBalance join t in db.ProdOrderLine on p.ProdOrderLineId equals t.ProdOrderLineId into table from tab in table.DefaultIfEmpty() join t1 in db.MaterialPlanLine on tab.MaterialPlanLineId equals t1.MaterialPlanLineId into table1 from tab1 in table1.DefaultIfEmpty() join t2 in db.MaterialPlanHeader on tab1.MaterialPlanHeaderId equals t2.MaterialPlanHeaderId into table2 from tab2 in table2.DefaultIfEmpty() join ProTab in db.ProdOrderHeader on p.ProdOrderHeaderId equals ProTab.ProdOrderHeaderId join Prod in db.Product on p.ProductId equals Prod.ProductId where p.BalanceQty > 0 && ((string.IsNullOrEmpty(term) ? 1 == 1 : p.ProdOrderNo.ToLower().Contains(term.ToLower())) || (string.IsNullOrEmpty(term) ? 1 == 1 : p.Dimension1.Dimension1Name.ToLower().Contains(term.ToLower())) || (string.IsNullOrEmpty(term) ? 1 == 1 : p.Dimension2.Dimension2Name.ToLower().Contains(term.ToLower())) || (string.IsNullOrEmpty(term) ? 1 == 1 : p.Product.ProductName.ToLower().Contains(term.ToLower()))) && (string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(p.DocTypeId.ToString())) && (string.IsNullOrEmpty(settings.filterProducts) ? 1 == 1 : filterProducts.Contains(p.ProductId.ToString())) && (string.IsNullOrEmpty(settings.filterProductGroups) ? 1 == 1 : filterProductGroups.Contains(Prod.ProductGroupId.ToString())) && (string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : filterProductTypes.Contains(Prod.ProductGroup.ProductTypeId.ToString())) && (string.IsNullOrEmpty(settings.filterContraSites) ? ProTab.SiteId == CurrentSiteId : contraSites.Contains(ProTab.SiteId.ToString())) && (string.IsNullOrEmpty(settings.filterContraDivisions) ? ProTab.DivisionId == CurrentDivisionId : contraDivisions.Contains(ProTab.DivisionId.ToString())) select new ProdOrderLineBalance { ProdOrderDocNo = p.ProdOrderNo, ProdOrderLineId = p.ProdOrderLineId, MaterialPlanDocNo = tab2.DocNo, ProductId = p.ProductId, ProductName = p.Product.ProductName, Dimension1Name = p.Dimension1.Dimension1Name, Dimension2Name = p.Dimension2.Dimension2Name, BalanceQty = p.BalanceQty }).Take(Limiter); return(Qry); }
public IEnumerable <ProdOrderCancelLineViewModel> GetProdOrderLineForMultiSelect(ProdOrderCancelFilterViewModel svm) { var Header = new ProdOrderCancelHeaderService(_unitOfWork).Find(svm.ProdOrderCancelHeaderId); var settings = new ProdOrderSettingsService(_unitOfWork).GetProdOrderSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId); string[] ProductIdArr = null; if (!string.IsNullOrEmpty(svm.ProductId)) { ProductIdArr = svm.ProductId.Split(",".ToCharArray()); } else { ProductIdArr = new string[] { "NA" }; } string[] Dimension2IdArr = null; if (!string.IsNullOrEmpty(svm.Dimension2Id)) { Dimension2IdArr = svm.Dimension2Id.Split(",".ToCharArray()); } else { Dimension2IdArr = new string[] { "NA" }; } string[] Dimension1IdArr = null; if (!string.IsNullOrEmpty(svm.Dimension1Id)) { Dimension1IdArr = svm.Dimension1Id.Split(",".ToCharArray()); } else { Dimension1IdArr = new string[] { "NA" }; } string[] SaleOrderIdArr = null; if (!string.IsNullOrEmpty(svm.ProdOrderId)) { SaleOrderIdArr = svm.ProdOrderId.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[] contraDocTypes = null; if (!string.IsNullOrEmpty(settings.filterContraDocTypes)) { contraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray()); } else { contraDocTypes = new string[] { "NA" }; } string[] contraSites = null; if (!string.IsNullOrEmpty(settings.filterContraSites)) { contraSites = settings.filterContraSites.Split(",".ToCharArray()); } else { contraSites = new string[] { "NA" }; } string[] contraDivisions = null; if (!string.IsNullOrEmpty(settings.filterContraDivisions)) { contraDivisions = settings.filterContraDivisions.Split(",".ToCharArray()); } else { contraDivisions = new string[] { "NA" }; } var temp = (from p in db.ViewProdOrderBalance join product in db.Product on p.ProductId equals product.ProductId into table2 from tab2 in table2.DefaultIfEmpty() join t3 in db.Units on tab2.UnitId equals t3.UnitId into table3 from tab3 in table3.DefaultIfEmpty() join t2 in db.ProdOrderLine on p.ProdOrderLineId equals t2.ProdOrderLineId where p.BalanceQty > 0 select new { BalanceQty = p.BalanceQty, Qty = p.BalanceQty, ProdOrderNo = p.ProdOrderNo, ProductName = tab2.ProductName, ProductId = p.ProductId, ProdOrderCancelHeaderId = svm.ProdOrderCancelHeaderId, ProdOrderLineId = p.ProdOrderLineId, unitDecimalPlaces = (tab3 == null ? 0 : tab3.DecimalPlaces), Dimension1Name = t2.Dimension1.Dimension1Name, Dimension2Name = t2.Dimension2.Dimension2Name, Specification = t2.Specification, UnitName = tab2.Unit.UnitName, ProdOrderHeaderId = p.ProdOrderHeaderId, ProductGroupId = tab2.ProductGroupId, OrderDocDate = p.IndentDate, DocTypeId = p.DocTypeId, SiteId = p.SiteId, DivisionId = p.DivisionId, Dimension1Id = p.Dimension1Id, Dimension2Id = p.Dimension2Id, }); if (!string.IsNullOrEmpty(svm.ProductId)) { temp = temp.Where(m => ProductIdArr.Contains(m.ProductId.ToString())); } if (!string.IsNullOrEmpty(svm.Dimension2Id)) { temp = temp.Where(m => Dimension2IdArr.Contains(m.Dimension2Id.ToString())); } if (!string.IsNullOrEmpty(svm.Dimension1Id)) { temp = temp.Where(m => Dimension1IdArr.Contains(m.Dimension1Id.ToString())); } if (!string.IsNullOrEmpty(svm.ProdOrderId)) { temp = temp.Where(m => SaleOrderIdArr.Contains(m.ProdOrderHeaderId.ToString())); } if (!string.IsNullOrEmpty(svm.ProductGroupId)) { temp = temp.Where(m => ProductGroupIdArr.Contains(m.ProductGroupId.ToString())); } if (!string.IsNullOrEmpty(settings.filterContraDocTypes)) { temp = temp.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString())); } if (!string.IsNullOrEmpty(settings.filterContraSites)) { temp = temp.Where(m => contraSites.Contains(m.SiteId.ToString())); } else { temp = temp.Where(m => m.SiteId == Header.SiteId); } if (!string.IsNullOrEmpty(settings.filterContraDivisions)) { temp = temp.Where(m => contraDivisions.Contains(m.DivisionId.ToString())); } else { temp = temp.Where(m => m.DivisionId == Header.DivisionId); } if (svm.UpToDate.HasValue) { temp = temp.Where(m => m.OrderDocDate <= svm.UpToDate); } return(from p in temp orderby p.OrderDocDate descending, p.ProdOrderNo descending select new ProdOrderCancelLineViewModel { BalanceQty = p.BalanceQty, Qty = p.BalanceQty, ProdOrderNo = p.ProdOrderNo, ProductName = p.ProductName, ProductId = p.ProductId, ProdOrderCancelHeaderId = svm.ProdOrderCancelHeaderId, ProdOrderLineId = p.ProdOrderLineId, unitDecimalPlaces = p.unitDecimalPlaces, Dimension1Name = p.Dimension1Name, Dimension2Name = p.Dimension2Name, Specification = p.Specification, UnitName = p.UnitName, ProdOrderHeaderId = p.ProdOrderHeaderId, ProductGroupId = p.ProductGroupId, OrderDocDate = p.OrderDocDate, }); }