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,
}));
}
