public IQueryable <ComboBoxResult> GetPendingMaterialPlanHelpList(int Id, string term) { //var PurchaseIndent = new PurchaseIndentHeaderService(_unitOfWork).Find(Id); //var settings = new PurchaseIndentSettingService(_unitOfWork).GetPurchaseIndentSettingForDocument(PurchaseIndent.DocTypeId, PurchaseIndent.DivisionId, PurchaseIndent.SiteId); //string[] contraDocTypes = null; //if (!string.IsNullOrEmpty(settings.filterContraDocTypes)) { contraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray()); } //else { contraDocTypes = new string[] { "NA" }; } //var list = (from p in db.ViewMaterialPlanBalance // where (string.IsNullOrEmpty(term) ? 1 == 1 : p.MaterialPlanNo.ToLower().Contains(term.ToLower())) && p.BalanceQty > 0 // // && (string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(p.DocTypeId.ToString())) // group new { p } by p.MaterialPlanHeaderId into g // select new MaterialPlanLineHelpListViewModel // { // DocNo = g.Max(m => m.p.MaterialPlanNo), // MaterialPlanHeaderId = g.Key, // DocumentTypeName = g.Max(m => m.p.DocType.DocumentTypeName) // // DocumentTypeName=g.Max(p=>p.p.DocumentTypeShortName) // } // ).Take(20); //return list.ToList(); var ProdOrderHeader = new ProdOrderHeaderService(_unitOfWork).Find(Id); var settings = new ProdOrderSettingsService(_unitOfWork).GetProdOrderSettingsForDocument(ProdOrderHeader.DocTypeId, ProdOrderHeader.DivisionId, ProdOrderHeader.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 list = (from p in db.ViewMaterialPlanBalance join t in db.MaterialPlanHeader on p.MaterialPlanHeaderId equals t.MaterialPlanHeaderId join Prod in db.Product on p.ProductId equals Prod.ProductId where (string.IsNullOrEmpty(term) ? 1 == 1 : p.MaterialPlanNo.ToLower().Contains(term.ToLower())) && p.BalanceQty > 0 && (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) ? t.SiteId == CurrentSiteId : contraSites.Contains(t.SiteId.ToString())) && (string.IsNullOrEmpty(settings.filterContraDivisions) ? t.DivisionId == CurrentDivisionId : contraDivisions.Contains(t.DivisionId.ToString())) group new { p } by p.MaterialPlanHeaderId into g orderby g.Max(m => m.p.MaterialPlanDate) select new ComboBoxResult { text = g.Max(m => m.p.MaterialPlanNo) + " | " + g.Max(m => m.p.DocType.DocumentTypeShortName), id = g.Key.ToString(), } ); return(list); }
public IEnumerable <ProdOrderLineViewModel> GetProdOrderForFilters(ProdOrderLineFilterViewModel vm) { var ProdOrderHeader = new ProdOrderHeaderService(_unitOfWork).Find(vm.ProdOrderHeaderId); var settings = new ProdOrderSettingsService(_unitOfWork).GetProdOrderSettingsForDocument(ProdOrderHeader.DocTypeId, ProdOrderHeader.DivisionId, ProdOrderHeader.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"]; string[] ProductIdArr = null; if (!string.IsNullOrEmpty(vm.ProductId)) { ProductIdArr = vm.ProductId.Split(",".ToCharArray()); } else { ProductIdArr = new string[] { "NA" }; } string[] SaleOrderIdArr = null; if (!string.IsNullOrEmpty(vm.MaterialPlanHeaderId)) { SaleOrderIdArr = vm.MaterialPlanHeaderId.Split(",".ToCharArray()); } else { SaleOrderIdArr = new string[] { "NA" }; } string[] ProductGroupIdArr = null; if (!string.IsNullOrEmpty(vm.ProductGroupId)) { ProductGroupIdArr = vm.ProductGroupId.Split(",".ToCharArray()); } else { ProductGroupIdArr = new string[] { "NA" }; } var temp = (from p in db.ViewMaterialPlanBalance join t in db.MaterialPlanHeader on p.MaterialPlanHeaderId equals t.MaterialPlanHeaderId into table from tab in table.DefaultIfEmpty() join product in db.Product on p.ProductId equals product.ProductId into table2 join t1 in db.MaterialPlanLine on p.MaterialPlanLineId equals t1.MaterialPlanLineId into table1 from tab1 in table1.DefaultIfEmpty() from tab2 in table2.DefaultIfEmpty() where (string.IsNullOrEmpty(vm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) && (string.IsNullOrEmpty(vm.MaterialPlanHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.MaterialPlanHeaderId.ToString())) && (string.IsNullOrEmpty(vm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) && p.BalanceQty > 0 && (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(tab2.ProductGroupId.ToString())) && (string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : filterProductTypes.Contains(tab2.ProductGroup.ProductTypeId.ToString())) && (string.IsNullOrEmpty(settings.filterContraSites) ? tab.SiteId == CurrentSiteId : contraSites.Contains(tab.SiteId.ToString())) && (string.IsNullOrEmpty(settings.filterContraDivisions) ? tab.DivisionId == CurrentDivisionId : contraDivisions.Contains(tab.DivisionId.ToString())) select new ProdOrderLineViewModel { Dimension1Name = tab1.Dimension1.Dimension1Name, Dimension2Name = tab1.Dimension2.Dimension2Name, Dimension3Name = tab1.Dimension3.Dimension3Name, Dimension4Name = tab1.Dimension4.Dimension4Name, Specification = tab1.Specification, Dimension1Id = tab1.Dimension1Id, Dimension2Id = tab1.Dimension2Id, Dimension3Id = tab1.Dimension3Id, Dimension4Id = tab1.Dimension4Id, PlanBalanceQty = p.BalanceQty, Qty = p.BalanceQty, MaterialPlanHeaderDocNo = tab.DocNo, ProductName = tab2.ProductName, ProductId = p.ProductId, ProdOrderHeaderId = vm.ProdOrderHeaderId, MaterialPlanLineId = p.MaterialPlanLineId, //UnitId = tab2.UnitId, } ); return(temp); }