public IEnumerable <ComboBoxList> GetPendingRequisitionsForFilters(int id, string term, int Limit)
{
var RequisitionCancel = new RequisitionCancelHeaderService(_unitOfWork).Find(id);
var settings = new RequisitionSettingService(_unitOfWork).GetRequisitionSettingForDocument(RequisitionCancel.DocTypeId, RequisitionCancel.DivisionId, RequisitionCancel.SiteId);
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 list = (from p in db.ViewRequisitionBalance
join t in db.RequisitionHeader on p.RequisitionHeaderId equals t.RequisitionHeaderId
where (string.IsNullOrEmpty(term) ? 1 == 1 : p.RequisitionNo.ToLower().Contains(term.ToLower())) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == RequisitionCancel.SiteId : ContraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == RequisitionCancel.DivisionId : ContraDivisions.Contains(p.DivisionId.ToString()))
group p by p.RequisitionHeaderId into g
orderby g.Max(m => m.RequisitionNo)
select new ComboBoxList
{
PropFirst = g.Max(m => m.RequisitionNo),
Id = g.Key,
}
).Take(Limit);
return(list.ToList());
}
public IQueryable <ComboBoxResult> GetPendingProductsForFilters(int id, string term)
{
var RequisitionCancel = new RequisitionCancelHeaderService(_unitOfWork).Find(id);
var settings = new RequisitionSettingService(_unitOfWork).GetRequisitionSettingForDocument(RequisitionCancel.DocTypeId, RequisitionCancel.DivisionId, RequisitionCancel.SiteId);
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" };
}
var Query = (from p in db.ViewRequisitionBalance
join t in db.RequisitionHeader on p.RequisitionHeaderId equals t.RequisitionHeaderId
join prod in db.Product on p.ProductId equals prod.ProductId
join pg in db.ProductGroups on prod.ProductGroupId equals pg.ProductGroupId
into table
from pgtable in table.DefaultIfEmpty()
where p.BalanceQty > 0
//where (string.IsNullOrEmpty(term) ? 1 == 1 : p.Product.ProductName.ToLower().Contains(term.ToLower()))
//&& (string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains(p.Product.ProductGroup.ProductTypeId.ToString()))
//&& (string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == RequisitionCancel.SiteId : ContraSites.Contains(p.SiteId.ToString()))
//&& (string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == RequisitionCancel.DivisionId : ContraDivisions.Contains(p.DivisionId.ToString()))
group new { p, pgtable, prod } by p.ProductId into g
orderby g.Max(m => m.prod.ProductName)
select new
{
ProductName = g.Max(m => m.prod.ProductName),
ProductId = g.Key,
ProductTypeId = g.Max(m => m.pgtable.ProductTypeId),
SiteId = g.Max(m => m.p.SiteId),
DivisionId = g.Max(m => m.p.DivisionId),
});
if (!string.IsNullOrEmpty(term))
{
Query = Query.Where(m => m.ProductName.ToLower().Contains(term.ToLower()));
}
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
Query = Query.Where(m => ProductTypes.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 == 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.ProductId.ToString(),
text = m.ProductName,
}));
}
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,
}));
}
public IEnumerable <RequisitionCancelProductHelpList> GetPendingProductsForOrder(int id, string term, int Limit)
{
var RequisitionCancel = new RequisitionCancelHeaderService(_unitOfWork).Find(id);
var settings = new RequisitionSettingService(_unitOfWork).GetRequisitionSettingForDocument(RequisitionCancel.DocTypeId, RequisitionCancel.DivisionId, RequisitionCancel.SiteId);
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" };
}
var list = (from p in db.ViewRequisitionBalance
join t in db.RequisitionHeader on p.RequisitionHeaderId equals t.RequisitionHeaderId
join t2 in db.RequisitionLine on p.RequisitionLineId equals t2.RequisitionLineId
where (string.IsNullOrEmpty(term) ? 1 == 1 : p.Product.ProductName.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : t2.Specification.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.RequisitionNo.ToLower().Contains(term.ToLower())
) &&
(string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains(p.Product.ProductGroup.ProductTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == RequisitionCancel.SiteId : ContraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == RequisitionCancel.DivisionId : ContraDivisions.Contains(p.DivisionId.ToString()))
orderby t.DocDate, t.DocNo
select new RequisitionCancelProductHelpList
{
ProductName = p.Product.ProductName,
ProductId = p.ProductId,
Specification = t2.Specification,
Dimension1Name = p.Dimension1.Dimension1Name,
Dimension2Name = p.Dimension2.Dimension2Name,
RequisitionDocNo = p.RequisitionNo,
RequisitionLineId = p.RequisitionLineId,
BalanceQty = p.BalanceQty,
}
).Take(Limit);
return(list.ToList());
}