public IQueryable <ComboBoxResult> GetCostCenters(string term, string DocTypes, string Process) { string[] ContraDocTypes = null; if (!string.IsNullOrEmpty(DocTypes)) { ContraDocTypes = DocTypes.Split(",".ToCharArray()); } else { ContraDocTypes = new string[] { "NA" }; } string[] ContraProcess = null; if (!string.IsNullOrEmpty(Process)) { ContraProcess = Process.Split(",".ToCharArray()); } else { ContraProcess = new string[] { "NA" }; } int SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var temp = (from p in db.CostCenter where (string.IsNullOrEmpty(DocTypes) ? 1 == 1 : ContraDocTypes.Contains(p.DocTypeId.ToString())) && (string.IsNullOrEmpty(term) ? 1 == 1 : p.CostCenterName.ToLower().Contains(term.ToLower())) && (string.IsNullOrEmpty(Process) ? 1 == 1 : ContraProcess.Contains(p.ProcessId.ToString())) && (string.IsNullOrEmpty(p.SiteId.ToString()) ? 1 == 1 : p.SiteId == SiteId) && (string.IsNullOrEmpty(p.DivisionId.ToString()) ? 1 == 1 : p.DivisionId == DivisionId) && p.IsActive == true orderby p.CostCenterName select new ComboBoxResult { text = p.CostCenterName + " | " + p.DocType.DocumentTypeShortName, id = p.CostCenterId.ToString(), }); return(temp); }
public dynamic GetProductHelpListforAC(int Id, string term, int Limit) { var PurchaseQuotation = db.PurchaseQuotationHeader.Find(Id); var settings = new PurchaseQuotationSettingService(_unitOfWork).GetPurchaseQuotationSettingForDocument(PurchaseQuotation.DocTypeId, PurchaseQuotation.DivisionId, PurchaseQuotation.SiteId); //var list = (from p in db.Product // where (string.IsNullOrEmpty(term) ? 1 == 1 : p.ProductName.ToLower().Contains(term.ToLower())) // && (string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains(p.ProductGroup.ProductTypeId.ToString())) // group new { p } by p.ProductId into g // select new ComboBoxList // { // PropFirst = g.Max(m => m.p.ProductName), // Id = g.Key, // // DocumentTypeName=g.Max(p=>p.p.DocumentTypeShortName) // } // ).Take(20); //return list.ToList(); string[] ProductTypes = null; if (!string.IsNullOrEmpty(settings.filterProductTypes)) { ProductTypes = settings.filterProductTypes.Split(",".ToCharArray()); } else { ProductTypes = 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[] ContraDocTypes = null; if (!string.IsNullOrEmpty(settings.filterContraDocTypes)) { ContraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray()); } else { ContraDocTypes = new string[] { "NA" }; } var query = (from p in db.ViewPurchaseIndentBalance join t in db.PurchaseIndentHeader on p.PurchaseIndentHeaderId equals t.PurchaseIndentHeaderId join rl in db.PurchaseIndentLine on p.PurchaseIndentLineId equals rl.PurchaseIndentLineId join prod in db.Product on p.ProductId equals prod.ProductId join pg in db.ProductGroups on prod.ProductGroupId equals pg.ProductGroupId where p.BalanceQty > 0 //t.Status == (int)StatusConstants.Submitted orderby t.DocDate, t.DocNo select new { ProductName = rl.Product.ProductName, ProductId = p.ProductId, Specification = rl.Specification, Dimension1Name = rl.Dimension1.Dimension1Name, Dimension2Name = rl.Dimension2.Dimension2Name, PurchaseIndentNo = p.PurchaseIndentNo, PurchaseIndentLineId = p.PurchaseIndentLineId, Qty = p.BalanceQty, ProductType = pg.ProductTypeId, SiteId = p.SiteId, DivisionId = p.DivisionId, DocTypeId = p.DocTypeId, }); if (!string.IsNullOrEmpty(settings.filterProductTypes)) { query = query.Where(m => ProductTypes.Contains(m.ProductType.ToString())); } if (!string.IsNullOrEmpty(settings.filterContraSites)) { query = query.Where(m => ContraSites.Contains(m.SiteId.ToString())); } else { query = query.Where(m => m.SiteId == PurchaseQuotation.SiteId); } if (!string.IsNullOrEmpty(settings.filterContraDivisions)) { query = query.Where(m => ContraDivisions.Contains(m.DivisionId.ToString())); } else { query = query.Where(m => m.DivisionId == PurchaseQuotation.DivisionId); } if (!string.IsNullOrEmpty(settings.filterContraDocTypes)) { query = query.Where(m => ContraDocTypes.Contains(m.DocTypeId.ToString())); } if (!string.IsNullOrEmpty(term)) { query = query.Where(m => m.ProductName.ToLower().Contains(term.ToLower()) || m.Specification.ToLower().Contains(term.ToLower()) || m.Dimension1Name.ToLower().Contains(term.ToLower()) || m.Dimension2Name.ToLower().Contains(term.ToLower()) || m.PurchaseIndentNo.ToLower().Contains(term.ToLower()) ); } return((from p in query select new { ProductName = p.ProductName, ProductId = p.ProductId, Specification = p.Specification, Dimension1Name = p.Dimension1Name, Dimension2Name = p.Dimension2Name, PurchaseIndentNo = p.PurchaseIndentNo, PurchaseIndentLineId = p.PurchaseIndentLineId, Qty = p.Qty, }).Take(Limit).ToList()); }
public IQueryable <ComboBoxResult> GetPendingCostCentersForFilters(int id, string term) { var RequisitionCancel = new RequisitionCancelHeaderService(_unitOfWork).Find(id); var settings = new RequisitionSettingService(_unitOfWork).GetRequisitionSettingForDocument(RequisitionCancel.DocTypeId, RequisitionCancel.DivisionId, RequisitionCancel.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" }; } var Query = (from p in db.ViewRequisitionBalance join cs in db.CostCenter on p.CostCenterId equals cs.CostCenterId join head in db.RequisitionHeader on p.RequisitionHeaderId equals head.RequisitionHeaderId where p.BalanceQty > 0 group new { p, cs, head } by p.CostCenterId into g orderby g.Max(m => m.cs.CostCenterName) select new { CostCenterName = g.Max(m => m.cs.CostCenterName), CostCenterId = g.Max(m => m.p.CostCenterId), ReferenceDocType = g.Max(m => m.head.DocTypeId), SiteId = g.Max(m => m.p.SiteId), DivisionId = g.Max(m => m.p.DivisionId), PersonId = g.Max(m => m.p.PersonId), }); if (!string.IsNullOrEmpty(term)) { Query = Query.Where(m => m.CostCenterName.ToLower().Contains(term.ToLower())); } if (!string.IsNullOrEmpty(settings.filterContraDocTypes)) { Query = Query.Where(m => ContraDocTypes.Contains(m.ReferenceDocType.ToString())); } if (RequisitionCancel.PersonId != 0) { Query = Query.Where(m => m.PersonId == RequisitionCancel.PersonId); } if (!string.IsNullOrEmpty(settings.filterContraSites)) { Query = Query.Where(m => ContraSites.Contains(m.SiteId.ToString())); } else { Query = Query.Where(m => m.SiteId == RequisitionCancel.SiteId); } if (!string.IsNullOrEmpty(settings.filterContraDivisions)) { Query = Query.Where(m => ContraDivisions.Contains(m.DivisionId.ToString())); } else { Query = Query.Where(m => m.DivisionId == RequisitionCancel.DivisionId); } return(Query.Select(m => new ComboBoxResult { id = m.CostCenterId.ToString(), text = m.CostCenterName, })); }