private IQueryable <AffiliateReportData> BuildReportQuery()
{
var orders = _strategy.GetQuery <hcc_Order>()
.Where(o => o.StoreId == _storeId)
.Where(o => o.TimeOfOrder >= _criteria.StartDateUtc && o.TimeOfOrder <= _criteria.EndDateUtc);
var signups = _strategy.GetQuery()
.Where(i => i.CreationDate >= _criteria.StartDateUtc && i.CreationDate <= _criteria.EndDateUtc);
var payments = _strategy.GetQuery <hcc_AffiliatePayments>()
.Where(i => i.PaymentDate >= _criteria.StartDateUtc && i.PaymentDate <= _criteria.EndDateUtc);
var affiliates = _strategy.GetQuery().Where(a => a.StoreId == _storeId);
if (!string.IsNullOrEmpty(_criteria.ReferralAffiliateID))
{
affiliates = affiliates.Where(a => a.ReferralID == _criteria.ReferralAffiliateID);
}
if (_criteria.ShowOnlyNonApproved)
{
affiliates = affiliates.Where(a => !a.Approved);
}
var query = affiliates.GroupJoin(orders, a => a.Id, o => o.AffiliateId,
(a, oColl) => new { Affiliate = a, Orders = oColl })
.GroupJoin(signups, ao => ao.Affiliate.AffiliateID, s => s.ReferralID,
(ao, sColl) => new { ao.Affiliate, ao.Orders, Signups = sColl })
.GroupJoin(payments, aos => aos.Affiliate.Id, p => p.AffiliateId,
(aos, pColl) => new { aos.Affiliate, aos.Orders, aos.Signups, Payments = pColl })
.Select(aosp => new AffiliateReportData
{
Id = aosp.Affiliate.Id,
AffiliateId = aosp.Affiliate.AffiliateID,
UserId = aosp.Affiliate.UserId,
OrdersCount = aosp.Orders.Count(),
SignupsCount = aosp.Signups.Count(),
SalesAmount =
_applyVATRules
? aosp.Orders.Sum(o => (decimal?)o.SubTotal - (decimal?)o.ItemsTax) ?? 0
: aosp.Orders.Sum(o => (decimal?)o.SubTotal + (decimal?)o.OrderDiscounts) ?? 0,
// TODO: Old Affiliate Report use TotalOrderBeforeDiscounts
Commission = aosp.Affiliate.CommissionType == 2
? Math.Round(aosp.Affiliate.CommissionAmount * aosp.Orders.Count(), 2)
: Math.Round(
aosp.Affiliate.CommissionAmount / 100 *
(_applyVATRules
? aosp.Orders.Sum(o => (decimal?)o.SubTotal - (decimal?)o.ItemsTax) ?? 0
: aosp.Orders.Sum(o => (decimal?)o.SubTotal + (decimal?)o.OrderDiscounts) ?? 0), 2),
CommissionPayed = aosp.Payments.Sum(p => (decimal?)p.PaymentAmount) ?? 0,
PaymentsCount = aosp.Payments.Count()
});
if (_criteria.ShowCommissionOwed)
{
query = query.Where(i => ((decimal?)(i.Commission - i.CommissionPayed) ?? 0) > 0);
}
return(query);
}
protected IQueryable <JoinedItem <T, TT> > GetJoinedQuery(IRepoStrategy <T> strategy,
Expression <Func <T, bool> > predicate = null, bool singleCulture = false)
{
var mainCulture = Context.MainContentCulture;
var fallbackCulture = Context.FallbackContentCulture;
var objSet = predicate == null?strategy.GetQuery() : strategy.GetQuery(predicate);
var translationSet =
strategy.GetQuery <TT>(
it => it.Culture == mainCulture || (!singleCulture && it.Culture == fallbackCulture));
return(objSet.GroupJoin(translationSet, ItemKeyExp, ItemTranslationKeyExp,
(i, it) =>
new JoinedItem <T, TT>
{
Item = i,
ItemTranslation = it.OrderBy(iit => iit.Culture == mainCulture ? 1 : 2).FirstOrDefault()
}));
}
private IQueryable <JoinedItem <hcc_Product, hcc_ProductTranslation> > BuildCriteriaQuery(
ProductSearchCriteria criteria, IRepoStrategy <hcc_Product> s)
{
var items = GetSecureQueryForCurrentStore(s);
var categoryGuid = DataTypeHelper.BvinToNullableGuid(criteria.CategoryId);
var productTypeGuid = DataTypeHelper.BvinToNullableGuid(criteria.ProductTypeId);
var manufacturerGuid = DataTypeHelper.BvinToNullableGuid(criteria.ManufacturerId);
var vendorGuid = DataTypeHelper.BvinToNullableGuid(criteria.VendorId);
if (criteria.NotCategorized)
{
items = items.Where(y => !y.Item.hcc_ProductXCategory.Any());
}
else if (categoryGuid.HasValue)
{
items =
items.Where(
y =>
y.Item.hcc_ProductXCategory.Where(z => z.CategoryId == categoryGuid).FirstOrDefault() !=
null);
}
// Display Inactive
if (!criteria.DisplayInactiveProducts)
{
items = items.Where(y => y.Item.Status == 1);
}
// Display Bundles
if (!criteria.DisplayBundles)
{
items = items.Where(y => !y.Item.IsBundle);
}
// Display Recurring
if (!criteria.DisplayRecurring)
{
items = items.Where(y => !y.Item.IsRecurring);
}
// Display Gift Cards
if (!criteria.DisplayGiftCards)
{
items = items.Where(y => !y.Item.IsGiftCard);
}
// Status
if (criteria.Status != ProductStatus.NotSet)
{
items = items.Where(y => y.Item.Status == (int)criteria.Status);
}
// Inventory Status
if (criteria.InventoryStatus != ProductInventoryStatus.NotSet)
{
if (criteria.InventoryStatus == ProductInventoryStatus.Available)
{
// show products, excluding those that are not available due to inventory settings
items =
items.Where(
y =>
y.Item.IsAvailableForSale ||
y.Item.OutOfStockMode != (int)ProductInventoryMode.WhenOutOfStockHide);
}
else if (criteria.InventoryStatus == ProductInventoryStatus.NotAvailable)
{
// show only products that are not currently availabe in the store due to inventory settings
items =
items.Where(
y =>
!y.Item.IsAvailableForSale &&
y.Item.OutOfStockMode == (int)ProductInventoryMode.WhenOutOfStockHide);
}
else
{
// filter by products with inventory levels that are set (ignore zeroes)
items =
items.Where(
y =>
y.Item.hcc_ProductInventory.Where(
z =>
z.QuantityOnHand <= z.LowStockPoint &&
z.QuantityOnHand != 0 & z.LowStockPoint != 0).Any());
}
}
// Product Type
if (productTypeGuid.HasValue)
{
items = items.Where(y => y.Item.ProductTypeId == productTypeGuid);
}
// Manufacturer
if (manufacturerGuid.HasValue)
{
items = items.Where(y => y.Item.ManufacturerID == manufacturerGuid);
}
// Vendor
if (vendorGuid.HasValue)
{
items = items.Where(y => y.Item.VendorID == vendorGuid);
}
// Keywords
if (!string.IsNullOrEmpty(criteria.Keyword))
{
items = items.Where(y => y.Item.SKU.Contains(criteria.Keyword) ||
y.ItemTranslation.ProductName.Contains(criteria.Keyword) ||
y.ItemTranslation.MetaDescription.Contains(criteria.Keyword) ||
y.ItemTranslation.MetaKeywords.Contains(criteria.Keyword) ||
y.ItemTranslation.ShortDescription.Contains(criteria.Keyword) ||
y.ItemTranslation.LongDescription.Contains(criteria.Keyword) ||
y.ItemTranslation.Keywords.Contains(criteria.Keyword) ||
y.ItemTranslation.HiddenSearchKeywords.Contains(criteria.Keyword)
);
}
if (!criteria.DisplayProductWithChoice)
{
var optionQry = s.GetQuery <hcc_ProductXOption>();
items = items.Where(y => optionQry.Where(x => x.ProductBvin == y.Item.bvin).Count() <= 0)
.Where(
y =>
y.Item.hcc_BundledProducts.Where(
x => optionQry.Any(xx => xx.ProductBvin == x.BundledProductId)).Count() <= 0);
}
switch (criteria.CategorySort)
{
case CategorySortOrder.ProductName:
items = items.OrderBy(y => y.ItemTranslation.ProductName);
break;
case CategorySortOrder.ProductNameDescending:
items = items.OrderByDescending(y => y.ItemTranslation.ProductName);
break;
case CategorySortOrder.ProductPriceAscending:
items = items.OrderBy(y => y.Item.SitePrice);
break;
case CategorySortOrder.ProductPriceDescending:
items = items.OrderByDescending(y => y.Item.SitePrice);
break;
case CategorySortOrder.ProductSKUAscending:
items = items.OrderBy(y => y.Item.SKU);
break;
case CategorySortOrder.ProductSKUDescending:
items = items.OrderByDescending(y => y.Item.SKU);
break;
default:
if (categoryGuid.HasValue)
{
items =
items.OrderBy(
y =>
y.Item.hcc_ProductXCategory.Where(z => z.CategoryId == categoryGuid)
.Select(z => z.SortOrder)
.FirstOrDefault());
}
else
{
items = items.OrderBy(y => y.ItemTranslation.ProductName);
}
break;
}
return(items);
}
