public IQueryable <ComboBoxResult> GetPendingProductsForSaleDelivery(int id, string term)//DocTypeId
{
var SaleDelivery = new SaleDeliveryHeaderService(_unitOfWork).Find(id);
var settings = new SaleDeliverySettingService(_unitOfWork).GetSaleDeliverySettingForDocument(SaleDelivery.DocTypeId, SaleDelivery.DivisionId, SaleDelivery.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" };
}
return(from p in db.ViewSaleInvoiceBalanceForDelivery
join t in db.Product on p.ProductId equals t.ProductId into ProdTable
from ProTab in ProdTable.DefaultIfEmpty()
where p.BalanceQty > 0 && p.SaleToBuyerId == SaleDelivery.SaleToBuyerId &&
(string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == SaleDelivery.SiteId : ContraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == SaleDelivery.DivisionId : ContraDivisions.Contains(p.DivisionId.ToString())) &&
(string.IsNullOrEmpty(term) ? 1 == 1 : ProTab.ProductName.ToLower().Contains(term.ToLower()))
group new { p, ProTab } by p.ProductId into g
orderby g.Key descending
select new ComboBoxResult
{
id = g.Key.ToString(),
text = g.Max(m => m.ProTab.ProductName)
}
);
}
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 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> 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 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 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());
}
public IEnumerable <JobOrderCancelLineViewModel> GetJobOrderLineForMultiSelect(JobOrderCancelFilterViewModel svm)
{
JobOrderCancelHeader Header = db.JobOrderCancelHeader.Find(svm.JobOrderCancelHeaderId);
var Settings = new JobOrderSettingsService(_unitOfWork).GetJobOrderSettingsForDocument(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[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.JobOrderId))
{
SaleOrderIdArr = svm.JobOrderId.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[] Dime1IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Dime1IdArr = svm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dime1IdArr = new string[] { "NA" };
}
string[] Dime2IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Dime2IdArr = svm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dime2IdArr = new string[] { "NA" };
}
string[] Dime3IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension3Id))
{
Dime3IdArr = svm.Dimension3Id.Split(",".ToCharArray());
}
else
{
Dime3IdArr = new string[] { "NA" };
}
string[] Dime4IdArr = null;
if (!string.IsNullOrEmpty(svm.Dimension4Id))
{
Dime4IdArr = svm.Dimension4Id.Split(",".ToCharArray());
}
else
{
Dime4IdArr = 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.ViewJobOrderBalance
join t in db.JobOrderLine on p.JobOrderLineId equals t.JobOrderLineId into table
from tab in table.DefaultIfEmpty()
join product in db.Product on p.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(svm.JobWorkerId == 0 ? 1 == 1 : p.JobWorkerId == svm.JobWorkerId) &&
(string.IsNullOrEmpty(svm.JobOrderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.JobOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dime1IdArr.Contains(p.Dimension1Id.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dime2IdArr.Contains(p.Dimension2Id.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension3Id) ? 3 == 3 : Dime3IdArr.Contains(p.Dimension3Id.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension4Id) ? 3 == 3 : Dime4IdArr.Contains(p.Dimension4Id.ToString())) &&
(string.IsNullOrEmpty(Settings.filterContraSites) ? p.SiteId == Header.SiteId : ContraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(Settings.filterContraDivisions) ? p.DivisionId == Header.DivisionId : ContraDivisions.Contains(p.DivisionId.ToString())) &&
p.BalanceQty > 0 && p.JobWorkerId == svm.JobWorkerId
orderby p.OrderDate, p.JobOrderNo, tab.Sr
select new JobOrderCancelLineViewModel
{
BalanceQty = p.BalanceQty,
Qty = p.BalanceQty,
JobOrderDocNo = p.JobOrderNo,
ProductName = tab2.ProductName,
ProductId = p.ProductId,
JobOrderCancelHeaderId = svm.JobOrderCancelHeaderId,
JobOrderLineId = p.JobOrderLineId,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
Dimension3Id = p.Dimension3Id,
Dimension4Id = p.Dimension4Id,
Dimension1Name = p.Dimension1.Dimension1Name,
Dimension2Name = p.Dimension2.Dimension2Name,
Dimension3Name = p.Dimension3.Dimension3Name,
Dimension4Name = p.Dimension4.Dimension4Name,
Specification = tab.Specification,
UnitId = tab2.UnitId,
UnitName = tab2.Unit.UnitName,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = tab.DealUnit.DecimalPlaces,
ProductUidName = (tab.ProductUidHeaderId == null ? tab.ProductUid.ProductUidName : "")
});
return(temp);
}