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