private string CreateWhereCondition(string originalWhere)
{
var where = new WhereCondition();
where.Where(new WhereCondition(originalWhere)
{
WhereIsComplex = true
});
// Add where conditions from filters
where.Where(new WhereCondition(nameFilter.WhereCondition)
{
WhereIsComplex = true
});
string objType = ValidationHelper.GetString(objTypeSelector.Value, "");
if (!String.IsNullOrEmpty(objType))
{
#pragma warning disable BH2000 // Method 'WhereLike()' or 'WhereNotLike()' should not be used used.
where.WhereLike("VersionObjectType", objType);
#pragma warning restore BH2000 // Method 'WhereLike()' or 'WhereNotLike()' should not be used used.
}
int userId = ValidationHelper.GetInteger(userSelector.Value, 0);
if (userId > 0)
{
where.WhereEquals("VersionDeletedByUserID", userId);
}
// Get older than value
if (DisplayDateTimeFilter)
{
DateTime olderThan = DateTime.Now.Date.AddDays(1);
int dateTimeValue = ValidationHelper.GetInteger(txtFilter.Text, 0);
switch (drpFilter.SelectedIndex)
{
case 0:
olderThan = olderThan.AddDays(-dateTimeValue);
break;
case 1:
olderThan = olderThan.AddDays(-dateTimeValue * 7);
break;
case 2:
olderThan = olderThan.AddMonths(-dateTimeValue);
break;
case 3:
olderThan = olderThan.AddYears(-dateTimeValue);
break;
}
where.WhereLessOrEquals("VersionDeletedWhen", olderThan);
}
return(where.ToString(true));
}
/// <summary>
/// Returns where condition based on webpart fields.
/// </summary>
private WhereCondition GetWhereCondition()
{
var where = new WhereCondition().WhereEquals("SKUSiteID", SiteContext.CurrentSiteID);
// Show products only from current site or global too, based on setting
if (ECommerceSettings.AllowGlobalProducts(SiteContext.CurrentSiteName))
{
where.Where(w => w.WhereEquals("SKUSiteID", SiteContext.CurrentSiteID).Or().WhereNull("SKUSiteID"));
}
// Show/hide product variants - it is based on type of inventory tracking for parent product
string trackByVariants = TrackInventoryTypeEnum.ByVariants.ToStringRepresentation();
where.Where(v => v.Where(w => w.WhereNull("SKUParentSKUID").And().WhereNotEquals("SKUTrackInventory", trackByVariants))
.Or()
.Where(GetParentProductWhereCondition(new WhereCondition().WhereEquals("SKUTrackInventory", trackByVariants))));
// Product type filter
if (!string.IsNullOrEmpty(ProductType) && (ProductType != FILTER_ALL))
{
if (ProductType == PRODUCT_TYPE_PRODUCTS)
{
where.WhereNull("SKUOptionCategoryID");
}
else if (ProductType == PRODUCT_TYPE_PRODUCT_OPTIONS)
{
where.WhereNotNull("SKUOptionCategoryID");
}
}
// Representing filter
if (!string.IsNullOrEmpty(Representing) && (Representing != FILTER_ALL))
{
SKUProductTypeEnum productTypeEnum = Representing.ToEnum<SKUProductTypeEnum>();
string productTypeString = productTypeEnum.ToStringRepresentation();
where.WhereEquals("SKUProductType", productTypeString);
}
// Product number filter
if (!string.IsNullOrEmpty(ProductNumber))
{
where.WhereContains("SKUNumber", ProductNumber);
}
// Department filter
DepartmentInfo di = DepartmentInfoProvider.GetDepartmentInfo(Department, CurrentSiteName);
di = di ?? DepartmentInfoProvider.GetDepartmentInfo(Department, null);
if (di != null)
{
where.Where(GetColumnWhereCondition("SKUDepartmentID", new WhereCondition().WhereEquals("SKUDepartmentID", di.DepartmentID)));
}
// Manufacturer filter
ManufacturerInfo mi = ManufacturerInfoProvider.GetManufacturerInfo(Manufacturer, CurrentSiteName);
mi = mi ?? ManufacturerInfoProvider.GetManufacturerInfo(Manufacturer, null);
if (mi != null)
{
where.Where(GetColumnWhereCondition("SKUManufacturerID", new WhereCondition().WhereEquals("SKUManufacturerID", mi.ManufacturerID)));
}
// Supplier filter
SupplierInfo si = SupplierInfoProvider.GetSupplierInfo(Supplier, CurrentSiteName);
si = si ?? SupplierInfoProvider.GetSupplierInfo(Supplier, null);
if (si != null)
{
where.Where(GetColumnWhereCondition("SKUSupplierID", new WhereCondition().WhereEquals("SKUSupplierID", si.SupplierID)));
}
// Needs shipping filter
if (!string.IsNullOrEmpty(NeedsShipping) && (NeedsShipping != FILTER_ALL))
{
if (NeedsShipping == NEEDS_SHIPPING_YES)
{
where.Where(GetColumnWhereCondition("SKUNeedsShipping", new WhereCondition().WhereTrue("SKUNeedsShipping")));
}
else if (NeedsShipping == NEEDS_SHIPPING_NO)
{
where.Where(GetColumnWhereCondition("SKUNeedsShipping", new WhereCondition().WhereFalse("SKUNeedsShipping").Or().WhereNull("SKUNeedsShipping")));
}
}
// Price from filter
if (PriceFrom > 0)
{
where.WhereGreaterOrEquals("SKUPrice", PriceFrom);
}
// Price to filter
if (PriceTo > 0)
{
where.WhereLessOrEquals("SKUPrice", PriceTo);
}
// Public status filter
PublicStatusInfo psi = PublicStatusInfoProvider.GetPublicStatusInfo(PublicStatus, CurrentSiteName);
if (psi != null)
{
where.Where(GetColumnWhereCondition("SKUPublicStatusID", new WhereCondition().WhereEquals("SKUPublicStatusID", psi.PublicStatusID)));
}
// Internal status filter
InternalStatusInfo isi = InternalStatusInfoProvider.GetInternalStatusInfo(InternalStatus, CurrentSiteName);
if (isi != null)
{
where.Where(GetColumnWhereCondition("SKUInternalStatusID", new WhereCondition().WhereEquals("SKUInternalStatusID", isi.InternalStatusID)));
}
// Allow for sale filter
if (!string.IsNullOrEmpty(AllowForSale) && (AllowForSale != FILTER_ALL))
{
if (AllowForSale == ALLOW_FOR_SALE_YES)
{
where.WhereTrue("SKUEnabled");
}
else if (AllowForSale == ALLOW_FOR_SALE_NO)
{
where.WhereEqualsOrNull("SKUEnabled", false);
}
}
// Available items filter
if (!string.IsNullOrEmpty(AvailableItems))
{
int value = ValidationHelper.GetInteger(AvailableItems, int.MaxValue);
where.WhereLessOrEquals("SKUAvailableItems", value);
}
// Needs to be reordered filter
if (NeedsToBeReordered)
{
where.Where(w => w.Where(v => v.WhereNull("SKUReorderAt").And().WhereLessOrEquals("SKUAvailableItems", 0))
.Or()
.Where(z => z.WhereNotNull("SKUReorderAt").And().WhereLessOrEquals("SKUAvailableItems".AsColumn(), "SKUReorderAt".AsColumn())));
}
return where;
}
/// <summary>
/// Returns where condition based on webpart fields.
/// </summary>
private WhereCondition GetWhereCondition()
{
var where = new WhereCondition().WhereEquals("SKUSiteID", SiteContext.CurrentSiteID);
// Show products only from current site or global too, based on setting
if (ECommerceSettings.AllowGlobalProducts(SiteContext.CurrentSiteName))
{
where.Where(w => w.WhereEquals("SKUSiteID", SiteContext.CurrentSiteID).Or().WhereNull("SKUSiteID"));
}
// Show/hide product variants - it is based on type of inventory tracking for parent product
string trackByVariants = TrackInventoryTypeEnum.ByVariants.ToStringRepresentation();
where.Where(v => v.Where(w => w.WhereNull("SKUParentSKUID").And().WhereNotEquals("SKUTrackInventory", trackByVariants))
.Or()
.Where(GetParentProductWhereCondition(new WhereCondition().WhereEquals("SKUTrackInventory", trackByVariants))));
// Product type filter
if (!string.IsNullOrEmpty(ProductType) && (ProductType != FILTER_ALL))
{
if (ProductType == PRODUCT_TYPE_PRODUCTS)
{
where.WhereNull("SKUOptionCategoryID");
}
else if (ProductType == PRODUCT_TYPE_PRODUCT_OPTIONS)
{
where.WhereNotNull("SKUOptionCategoryID");
}
}
// Representing filter
if (!string.IsNullOrEmpty(Representing) && (Representing != FILTER_ALL))
{
SKUProductTypeEnum productTypeEnum = Representing.ToEnum <SKUProductTypeEnum>();
string productTypeString = productTypeEnum.ToStringRepresentation();
where.WhereEquals("SKUProductType", productTypeString);
}
// Product number filter
if (!string.IsNullOrEmpty(ProductNumber))
{
where.WhereContains("SKUNumber", ProductNumber);
}
// Department filter
DepartmentInfo di = DepartmentInfoProvider.GetDepartmentInfo(Department, CurrentSiteName);
di = di ?? DepartmentInfoProvider.GetDepartmentInfo(Department, null);
if (di != null)
{
where.Where(GetColumnWhereCondition("SKUDepartmentID", new WhereCondition().WhereEquals("SKUDepartmentID", di.DepartmentID)));
}
// Manufacturer filter
ManufacturerInfo mi = ManufacturerInfoProvider.GetManufacturerInfo(Manufacturer, CurrentSiteName);
mi = mi ?? ManufacturerInfoProvider.GetManufacturerInfo(Manufacturer, null);
if (mi != null)
{
where.Where(GetColumnWhereCondition("SKUManufacturerID", new WhereCondition().WhereEquals("SKUManufacturerID", mi.ManufacturerID)));
}
// Supplier filter
SupplierInfo si = SupplierInfoProvider.GetSupplierInfo(Supplier, CurrentSiteName);
si = si ?? SupplierInfoProvider.GetSupplierInfo(Supplier, null);
if (si != null)
{
where.Where(GetColumnWhereCondition("SKUSupplierID", new WhereCondition().WhereEquals("SKUSupplierID", si.SupplierID)));
}
// Needs shipping filter
if (!string.IsNullOrEmpty(NeedsShipping) && (NeedsShipping != FILTER_ALL))
{
if (NeedsShipping == NEEDS_SHIPPING_YES)
{
where.Where(GetColumnWhereCondition("SKUNeedsShipping", new WhereCondition().WhereTrue("SKUNeedsShipping")));
}
else if (NeedsShipping == NEEDS_SHIPPING_NO)
{
where.Where(GetColumnWhereCondition("SKUNeedsShipping", new WhereCondition().WhereFalse("SKUNeedsShipping").Or().WhereNull("SKUNeedsShipping")));
}
}
// Price from filter
if (PriceFrom > 0)
{
where.WhereGreaterOrEquals("SKUPrice", PriceFrom);
}
// Price to filter
if (PriceTo > 0)
{
where.WhereLessOrEquals("SKUPrice", PriceTo);
}
// Public status filter
PublicStatusInfo psi = PublicStatusInfoProvider.GetPublicStatusInfo(PublicStatus, CurrentSiteName);
if (psi != null)
{
where.Where(GetColumnWhereCondition("SKUPublicStatusID", new WhereCondition().WhereEquals("SKUPublicStatusID", psi.PublicStatusID)));
}
// Internal status filter
InternalStatusInfo isi = InternalStatusInfoProvider.GetInternalStatusInfo(InternalStatus, CurrentSiteName);
if (isi != null)
{
where.Where(GetColumnWhereCondition("SKUInternalStatusID", new WhereCondition().WhereEquals("SKUInternalStatusID", isi.InternalStatusID)));
}
// Allow for sale filter
if (!string.IsNullOrEmpty(AllowForSale) && (AllowForSale != FILTER_ALL))
{
if (AllowForSale == ALLOW_FOR_SALE_YES)
{
where.WhereTrue("SKUEnabled");
}
else if (AllowForSale == ALLOW_FOR_SALE_NO)
{
where.WhereEqualsOrNull("SKUEnabled", false);
}
}
// Available items filter
if (!string.IsNullOrEmpty(AvailableItems))
{
int value = ValidationHelper.GetInteger(AvailableItems, int.MaxValue);
where.WhereLessOrEquals("SKUAvailableItems", value);
}
// Needs to be reordered filter
if (NeedsToBeReordered)
{
where.Where(w => w.Where(v => v.WhereNull("SKUReorderAt").And().WhereLessOrEquals("SKUAvailableItems", 0))
.Or()
.Where(z => z.WhereNotNull("SKUReorderAt").And().WhereLessOrEquals("SKUAvailableItems".AsColumn(), "SKUReorderAt".AsColumn())));
}
return(where);
}
/// <summary>
/// Builds a SQL condition for filtering the order list, and returns it.
/// </summary>
/// <returns>A SQL condition for filtering the order list.</returns>
private WhereCondition GetFilterWhereCondition()
{
var condition = new WhereCondition();
// Order status
if (statusSelector.SelectedID > 0)
{
condition.WhereEquals("OrderStatusID", statusSelector.SelectedID);
}
// Order site
if (FilterSiteID > 0)
{
condition.WhereEquals("OrderSiteID", FilterSiteID);
}
// Order identifier
string filterOrderId = txtOrderId.Text.Trim().Truncate(txtOrderId.MaxLength);
if (filterOrderId != String.Empty)
{
int orderId = ValidationHelper.GetInteger(filterOrderId, 0);
// Include also an invoice number
condition.Where(w => w.WhereEquals("OrderID", orderId).Or().WhereContains("OrderInvoiceNumber", filterOrderId));
}
// Customer's properties, applicable only if a valid customer is not specified
if (CustomerFilterEnabled)
{
// First, Last name and Company search
string customerNameOrCompany = txtCustomerNameOrCompany.Text.Trim().Truncate(txtCustomerNameOrCompany.MaxLength);
if (customerNameOrCompany != String.Empty)
{
condition.WhereIn("OrderCustomerID", new IDQuery <CustomerInfo>("CustomerID").WhereContains("CustomerFirstName", customerNameOrCompany)
.Or()
.WhereContains("CustomerLastName", customerNameOrCompany)
.Or()
.WhereContains("CustomerCompany", customerNameOrCompany));
}
}
// Order is paid
switch (drpOrderIsPaid.SelectedValue)
{
case "0":
condition.Where("ISNULL(OrderIsPaid, 0) = 0");
break;
case "1":
condition.Where("ISNULL(OrderIsPaid, 0) = 1");
break;
}
// Advanced Filter
if (AdvancedFilterEnabled)
{
// Specific currency was selected
if (CurrencySelector.SelectedID > 0)
{
condition.WhereEquals("OrderCurrencyID", CurrencySelector.SelectedID);
}
// Specific payment method was selected
if (PaymentSelector.SelectedID > 0)
{
condition.WhereEquals("OrderPaymentOptionID", PaymentSelector.SelectedID);
}
// Specific shipping option was selected
if (ShippingSelector.SelectedID > 0)
{
condition.WhereEquals("OrderShippingOptionID", ShippingSelector.SelectedID);
}
// Specific tracking number was provided
var trackingNumber = txtTrackingNumber.Text.Truncate(txtTrackingNumber.MaxLength);
if (trackingNumber != string.Empty)
{
condition.WhereContains("OrderTrackingNumber", trackingNumber);
}
// Specific created date was selected
if ((dtpCreatedTo.SelectedDateTime < dtpCreatedTo.MaxDate) && (dtpCreatedTo.SelectedDateTime > dtpCreatedTo.MinDate))
{
condition.WhereLessOrEquals("OrderDate", dtpCreatedTo.SelectedDateTime);
}
// Specific from date was selected
if ((dtpFrom.SelectedDateTime < dtpFrom.MaxDate) && (dtpFrom.SelectedDateTime > dtpFrom.MinDate))
{
condition.WhereGreaterOrEquals("OrderDate", dtpFrom.SelectedDateTime);
}
}
return(condition);
}
/// <summary>
/// Builds a SQL condition for filtering the order list, and returns it.
/// </summary>
/// <returns>A SQL condition for filtering the order list.</returns>
private WhereCondition GetFilterWhereCondition()
{
var condition = new WhereCondition();
// Order status
if (statusSelector.SelectedID > 0)
{
condition.WhereEquals("OrderStatusID", statusSelector.SelectedID);
}
// Order site
if (FilterSiteID > 0)
{
condition.WhereEquals("OrderSiteID", FilterSiteID);
}
// Order identifier
string filterOrderId = txtOrderId.Text.Trim().Truncate(txtOrderId.MaxLength);
if (filterOrderId != String.Empty)
{
int orderId = ValidationHelper.GetInteger(filterOrderId, 0);
// Include also an invoice number
condition.Where(w => w.WhereEquals("OrderID", orderId).Or().WhereContains("OrderInvoiceNumber", filterOrderId));
}
// Customer's properties, applicable only if a valid customer is not specified
if (CustomerFilterEnabled)
{
// First, Last name and Company search
string customerNameOrCompany = txtCustomerNameOrCompany.Text.Trim().Truncate(txtCustomerNameOrCompany.MaxLength);
if (customerNameOrCompany != String.Empty)
{
condition.WhereIn("OrderCustomerID", new IDQuery<CustomerInfo>("CustomerID").WhereContains("CustomerFirstName", customerNameOrCompany)
.Or()
.WhereContains("CustomerLastName", customerNameOrCompany)
.Or()
.WhereContains("CustomerCompany", customerNameOrCompany));
}
}
// Order is paid
switch (drpOrderIsPaid.SelectedValue)
{
case "0":
condition.Where("ISNULL(OrderIsPaid, 0) = 0");
break;
case "1":
condition.Where("ISNULL(OrderIsPaid, 0) = 1");
break;
}
// Advanced Filter
if (AdvancedFilterEnabled)
{
// Specific currency was selected
if (CurrencySelector.SelectedID > 0)
{
condition.WhereEquals("OrderCurrencyID", CurrencySelector.SelectedID);
}
// Specific payment method was selected
if (PaymentSelector.SelectedID > 0)
{
condition.WhereEquals("OrderPaymentOptionID", PaymentSelector.SelectedID);
}
// Specific shipping option was selected
if (ShippingSelector.SelectedID > 0)
{
condition.WhereEquals("OrderShippingOptionID", ShippingSelector.SelectedID);
}
// Specific tracking number was provided
var trackingNumber = txtTrackingNumber.Text.Truncate(txtTrackingNumber.MaxLength);
if (trackingNumber != string.Empty)
{
condition.WhereContains("OrderTrackingNumber", trackingNumber);
}
// Specific created date was selected
if ((dtpCreatedTo.SelectedDateTime < dtpCreatedTo.MaxDate) && (dtpCreatedTo.SelectedDateTime > dtpCreatedTo.MinDate))
{
condition.WhereLessOrEquals("OrderDate", dtpCreatedTo.SelectedDateTime);
}
// Specific from date was selected
if ((dtpFrom.SelectedDateTime < dtpFrom.MaxDate) && (dtpFrom.SelectedDateTime > dtpFrom.MinDate))
{
condition.WhereGreaterOrEquals("OrderDate", dtpFrom.SelectedDateTime);
}
}
return condition;
}
private string CreateWhereCondition(string originalWhere)
{
var where = new WhereCondition();
where.Where(new WhereCondition(originalWhere){ WhereIsComplex = true });
// Add where conditions from filters
where.Where(new WhereCondition(nameFilter.WhereCondition) { WhereIsComplex = true });
string objType = ValidationHelper.GetString(objTypeSelector.Value, "");
if (!String.IsNullOrEmpty(objType))
{
where.WhereLike("VersionObjectType", objType);
}
int userId = ValidationHelper.GetInteger(userSelector.Value, 0);
if (userId > 0)
{
where.WhereEquals("VersionDeletedByUserID", userId);
}
// Get older than value
if (DisplayDateTimeFilter)
{
DateTime olderThan = DateTime.Now.Date.AddDays(1);
int dateTimeValue = ValidationHelper.GetInteger(txtFilter.Text, 0);
switch (drpFilter.SelectedIndex)
{
case 0:
olderThan = olderThan.AddDays(-dateTimeValue);
break;
case 1:
olderThan = olderThan.AddDays(-dateTimeValue * 7);
break;
case 2:
olderThan = olderThan.AddMonths(-dateTimeValue);
break;
case 3:
olderThan = olderThan.AddYears(-dateTimeValue);
break;
}
where.WhereLessOrEquals("VersionDeletedWhen", olderThan);
}
return where.ToString(true);
}
