Skip to content

Commit a038ae9

Browse files
mgesingmgesing
committed
Resolves smartstore#883 Export: Implement filter criteria for customer export
1 parent edc314b commit a038ae9

7 files changed

Lines changed: 312 additions & 26 deletions

File tree

‎src/Libraries/SmartStore.Core/Domain/DataExchange/ExportFilter.cs‎

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -112,11 +112,55 @@ public class ExportFilter
112112
/// </summary>
113113
public int[] ShippingStatusIds { get; set; }
114114

115+
#endregion
116+
117+
#region Customer
118+
119+
/// <summary>
120+
/// Filter by active or inactive customers
121+
/// </summary>
122+
public bool? IsActiveCustomer { get; set; }
123+
124+
/// <summary>
125+
/// Filter by tax exempt customers
126+
/// </summary>
127+
public bool? IsTaxExempt { get; set; }
128+
115129
/// <summary>
116130
/// Identifiers of customer roles
117131
/// </summary>
118132
public int[] CustomerRoleIds { get; set; }
119133

134+
/// <summary>
135+
/// Filter by billing country identifiers
136+
/// </summary>
137+
public int[] BillingCountryIds { get; set; }
138+
139+
/// <summary>
140+
/// Filter by shipping country identifiers
141+
/// </summary>
142+
public int[] ShippingCountryIds { get; set; }
143+
144+
/// <summary>
145+
/// Filter by last activity date from
146+
/// </summary>
147+
public DateTime? LastActivityFrom { get; set; }
148+
149+
/// <summary>
150+
/// Filter by last activity date to
151+
/// </summary>
152+
public DateTime? LastActivityTo { get; set; }
153+
154+
/// <summary>
155+
/// Filter by at least spent amount
156+
/// </summary>
157+
public decimal? HasSpentAtLeastAmount { get; set; }
158+
159+
/// <summary>
160+
/// Filter by at least placed orders
161+
/// </summary>
162+
public int? HasPlacedAtLeastOrders { get; set; }
163+
120164
#endregion
121165

122166
#region Newsletter Subscription

‎src/Libraries/SmartStore.Data/Migrations/201603121451066_ThirdPartyEmailHandOver.cs‎

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,54 @@ public void MigrateLocaleResources(LocaleResourcesBuilder builder)
8787
"Nur aktive Abonnenten",
8888
"Filter by active or inactive newsletter subscriber.",
8989
"Nach aktiven bzw. inaktiven Newsletter Abonnenten filtern.");
90+
91+
builder.AddOrUpdate("Admin.DataExchange.Export.Filter.IsActiveCustomer",
92+
"Only active customers",
93+
"Nur aktive Kunden",
94+
"Filter by active or inactive customers.",
95+
"Nach aktiven bzw. inaktiven Kunden filtern.");
96+
97+
builder.AddOrUpdate("Admin.DataExchange.Export.Filter.IsTaxExempt",
98+
"Only tax exempt customers",
99+
"Nur MwSt. befreite Kunden",
100+
"Filter by tax exempt customers.",
101+
"Nach MwSt. befreiten Kunden filtern.");
102+
103+
builder.AddOrUpdate("Admin.DataExchange.Export.Filter.BillingCountryIds",
104+
"Billing countries",
105+
"Rechnungsländer",
106+
"Filter by billing countries.",
107+
"Nach Rechnungsländern filtern.");
108+
109+
builder.AddOrUpdate("Admin.DataExchange.Export.Filter.ShippingCountryIds",
110+
"Shipping countries",
111+
"Versandländer",
112+
"Filter by shipping countries.",
113+
"Nach Versandländern filtern.");
114+
115+
builder.AddOrUpdate("Admin.DataExchange.Export.Filter.LastActivityFrom",
116+
"Last activity from",
117+
"Zuletzt aktiv von",
118+
"Filter by date of last store activity.",
119+
"Nach dem Datum der letzten Shop Aktivität filtern.");
120+
121+
builder.AddOrUpdate("Admin.DataExchange.Export.Filter.LastActivityTo",
122+
"Last activity to",
123+
"Zuletzt aktiv bis",
124+
"Filter by date of last store activity.",
125+
"Nach dem Datum der letzten Shop Aktivität filtern.");
126+
127+
builder.AddOrUpdate("Admin.DataExchange.Export.Filter.HasSpentAtLeastAmount",
128+
"Has spent x amount",
129+
"Hat Betrag x ausgegeben",
130+
"Filter by spent amount.",
131+
"Nach dem insgesamt ausgegebenen Betrag filtern.");
132+
133+
builder.AddOrUpdate("Admin.DataExchange.Export.Filter.HasPlacedAtLeastOrders",
134+
"Has x placed orders",
135+
"Hat x Bestellungen",
136+
"Filter by number of placed orders.",
137+
"Nach der Anzahl der getätigten Bestellungen filtern.");
90138
}
91139
}
92140
}

‎src/Libraries/SmartStore.Services/DataExchange/Export/DataExporter.cs‎

Lines changed: 59 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -704,7 +704,7 @@ private IQueryable<Order> GetOrderQuery(DataExporterContext ctx, int skip, int t
704704
null,
705705
null);
706706

707-
if (ctx.Request.EntitiesToExport.Count > 0)
707+
if (ctx.Request.EntitiesToExport.Any())
708708
query = query.Where(x => ctx.Request.EntitiesToExport.Contains(x.Id));
709709

710710
query = query.OrderByDescending(x => x.CreatedOnUtc);
@@ -769,7 +769,7 @@ private IQueryable<Category> GetCategoryQuery(DataExporterContext ctx, int skip,
769769

770770
var query = _categoryService.Value.GetCategories(null, showHidden, null, true, storeId);
771771

772-
if (ctx.Request.EntitiesToExport.Count > 0)
772+
if (ctx.Request.EntitiesToExport.Any())
773773
query = query.Where(x => ctx.Request.EntitiesToExport.Contains(x.Id));
774774

775775
query = query
@@ -804,7 +804,62 @@ private IQueryable<Customer> GetCustomerQuery(DataExporterContext ctx, int skip,
804804
.Expand(x => x.CustomerRoles)
805805
.Where(x => !x.Deleted);
806806

807-
if (ctx.Request.EntitiesToExport.Count > 0)
807+
if (ctx.Filter.IsActiveCustomer.HasValue)
808+
query = query.Where(x => x.Active == ctx.Filter.IsActiveCustomer.Value);
809+
810+
if (ctx.Filter.IsTaxExempt.HasValue)
811+
query = query.Where(x => x.IsTaxExempt == ctx.Filter.IsTaxExempt.Value);
812+
813+
if (ctx.Filter.CustomerRoleIds != null && ctx.Filter.CustomerRoleIds.Length > 0)
814+
query = query.Where(x => x.CustomerRoles.Select(y => y.Id).Intersect(ctx.Filter.CustomerRoleIds).Any());
815+
816+
if (ctx.Filter.BillingCountryIds != null && ctx.Filter.BillingCountryIds.Length > 0)
817+
query = query.Where(x => x.BillingAddress != null && ctx.Filter.BillingCountryIds.Contains(x.BillingAddress.Id));
818+
819+
if (ctx.Filter.ShippingCountryIds != null && ctx.Filter.ShippingCountryIds.Length > 0)
820+
query = query.Where(x => x.ShippingAddress != null && ctx.Filter.ShippingCountryIds.Contains(x.ShippingAddress.Id));
821+
822+
if (ctx.Filter.LastActivityFrom.HasValue)
823+
{
824+
var activityFrom = _dateTimeHelper.Value.ConvertToUtcTime(ctx.Filter.LastActivityFrom.Value, _dateTimeHelper.Value.CurrentTimeZone);
825+
query = query.Where(x => activityFrom <= x.LastActivityDateUtc);
826+
}
827+
828+
if (ctx.Filter.LastActivityTo.HasValue)
829+
{
830+
var activityTo = _dateTimeHelper.Value.ConvertToUtcTime(ctx.Filter.LastActivityTo.Value, _dateTimeHelper.Value.CurrentTimeZone);
831+
query = query.Where(x => activityTo >= x.LastActivityDateUtc);
832+
}
833+
834+
if (ctx.Filter.HasSpentAtLeastAmount.HasValue)
835+
{
836+
query = query
837+
.Join(_orderRepository.Value.Table, x => x.Id, y => y.CustomerId, (x, y) => new { Customer = x, Order = y })
838+
.GroupBy(x => x.Customer.Id)
839+
.Select(x => new
840+
{
841+
Customer = x.FirstOrDefault().Customer,
842+
OrderTotal = x.Sum(y => y.Order.OrderTotal)
843+
})
844+
.Where(x => x.OrderTotal >= ctx.Filter.HasSpentAtLeastAmount.Value)
845+
.Select(x => x.Customer);
846+
}
847+
848+
if (ctx.Filter.HasPlacedAtLeastOrders.HasValue)
849+
{
850+
query = query
851+
.Join(_orderRepository.Value.Table, x => x.Id, y => y.CustomerId, (x, y) => new { Customer = x, Order = y })
852+
.GroupBy(x => x.Customer.Id)
853+
.Select(x => new
854+
{
855+
Customer = x.FirstOrDefault().Customer,
856+
OrderCount = x.Count()
857+
})
858+
.Where(x => x.OrderCount >= ctx.Filter.HasPlacedAtLeastOrders.Value)
859+
.Select(x => x.Customer);
860+
}
861+
862+
if (ctx.Request.EntitiesToExport.Any())
808863
query = query.Where(x => ctx.Request.EntitiesToExport.Contains(x.Id));
809864

810865
query = query.OrderByDescending(x => x.CreatedOnUtc);
@@ -851,7 +906,7 @@ private IQueryable<NewsLetterSubscription> GetNewsLetterSubscriptionQuery(DataEx
851906
query = query.Where(x => createdTo >= x.CreatedOnUtc);
852907
}
853908

854-
if (ctx.Request.EntitiesToExport.Count > 0)
909+
if (ctx.Request.EntitiesToExport.Any())
855910
query = query.Where(x => ctx.Request.EntitiesToExport.Contains(x.Id));
856911

857912
query = query

‎src/Presentation/SmartStore.Web/Administration/Controllers/ExportController.cs‎

Lines changed: 36 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,7 @@ public class ExportController : AdminControllerBase
5353
private readonly ILanguageService _languageService;
5454
private readonly ICurrencyService _currencyService;
5555
private readonly IEmailAccountService _emailAccountService;
56+
private readonly ICountryService _countryService;
5657
private readonly IDateTimeHelper _dateTimeHelper;
5758
private readonly DataExchangeSettings _dataExchangeSettings;
5859
private readonly ITaskScheduler _taskScheduler;
@@ -70,6 +71,7 @@ public ExportController(
7071
ILanguageService languageService,
7172
ICurrencyService currencyService,
7273
IEmailAccountService emailAccountService,
74+
ICountryService countryService,
7375
IDateTimeHelper dateTimeHelper,
7476
DataExchangeSettings dataExchangeSettings,
7577
ITaskScheduler taskScheduler,
@@ -86,6 +88,7 @@ public ExportController(
8688
_languageService = languageService;
8789
_currencyService = currencyService;
8890
_emailAccountService = emailAccountService;
91+
_countryService = countryService;
8992
_dateTimeHelper = dateTimeHelper;
9093
_dataExchangeSettings = dataExchangeSettings;
9194
_taskScheduler = taskScheduler;
@@ -206,6 +209,9 @@ private void PrepareProfileModelForEdit(ExportProfileModel model, ExportProfile
206209
var filter = XmlHelper.Deserialize<ExportFilter>(profile.Filtering);
207210
var projection = XmlHelper.Deserialize<ExportProjection>(profile.Projection);
208211

212+
var language = _services.WorkContext.WorkingLanguage;
213+
var store = _services.StoreContext.CurrentStore;
214+
209215
var allStores = _services.StoreService.GetAllStores();
210216
var allLanguages = _languageService.GetAllLanguages(true);
211217
var allCurrencies = _currencyService.GetAllCurrencies(true);
@@ -223,7 +229,7 @@ private void PrepareProfileModelForEdit(ExportProfileModel model, ExportProfile
223229
model.CreateZipArchive = profile.CreateZipArchive;
224230
model.Cleanup = profile.Cleanup;
225231

226-
model.FileNamePatternExample = profile.ResolveFileNamePattern(_services.StoreContext.CurrentStore, 1, _dataExchangeSettings.MaxFileNameLength);
232+
model.FileNamePatternExample = profile.ResolveFileNamePattern(store, 1, _dataExchangeSettings.MaxFileNameLength);
227233

228234
model.AvailableEmailAccounts = allEmailAccounts
229235
.Select(x => new SelectListItem { Text = x.FriendlyName, Value = x.Id.ToString() })
@@ -286,6 +292,14 @@ private void PrepareProfileModelForEdit(ExportProfileModel model, ExportProfile
286292
WithoutManufacturers = filter.WithoutManufacturers,
287293
ProductTagId = filter.ProductTagId,
288294
FeaturedProducts = filter.FeaturedProducts,
295+
IsActiveCustomer = filter.IsActiveCustomer,
296+
IsTaxExempt = filter.IsTaxExempt,
297+
BillingCountryIds = filter.BillingCountryIds,
298+
ShippingCountryIds = filter.ShippingCountryIds,
299+
LastActivityFrom = filter.LastActivityFrom,
300+
LastActivityTo = filter.LastActivityTo,
301+
HasSpentAtLeastAmount = filter.HasSpentAtLeastAmount,
302+
HasPlacedAtLeastOrders = filter.HasPlacedAtLeastOrders,
289303
ProductType = filter.ProductType,
290304
IdMinimum = filter.IdMinimum,
291305
IdMaximum = filter.IdMaximum,
@@ -351,20 +365,27 @@ private void PrepareProfileModelForEdit(ExportProfileModel model, ExportProfile
351365
.ToList();
352366

353367
}
354-
else if (model.Provider.EntityType == ExportEntityType.Order)
368+
else if (model.Provider.EntityType == ExportEntityType.Customer)
355369
{
356370
var allCustomerRoles = _customerService.GetAllCustomerRoles(true);
371+
var allCountries = _countryService.GetAllCountries(true);
372+
373+
model.Filter.AvailableCustomerRoles = allCustomerRoles
374+
.OrderBy(x => x.Name)
375+
.Select(x => new SelectListItem { Text = x.Name, Value = x.Id.ToString() })
376+
.ToList();
357377

378+
model.Filter.AvailableCountries = allCountries
379+
.Select(x => new SelectListItem { Text = x.GetLocalized(y => y.Name, language.Id, true, false), Value = x.Id.ToString() })
380+
.ToList();
381+
}
382+
else if (model.Provider.EntityType == ExportEntityType.Order)
383+
{
358384
model.Projection.AvailableOrderStatusChange = ExportOrderStatusChange.Processing.ToSelectList(false);
359385

360386
model.Filter.AvailableOrderStates = OrderStatus.Pending.ToSelectList(false).ToList();
361387
model.Filter.AvailablePaymentStates = PaymentStatus.Pending.ToSelectList(false).ToList();
362388
model.Filter.AvailableShippingStates = ShippingStatus.NotYetShipped.ToSelectList(false).ToList();
363-
364-
model.Filter.AvailableCustomerRoles = allCustomerRoles
365-
.OrderBy(x => x.Name)
366-
.Select(x => new SelectListItem { Text = x.Name, Value = x.Id.ToString() })
367-
.ToList();
368389
}
369390

370391
try
@@ -727,6 +748,14 @@ public ActionResult Edit(ExportProfileModel model, bool continueEditing)
727748
WithoutManufacturers = model.Filter.WithoutManufacturers,
728749
ProductTagId = model.Filter.ProductTagId,
729750
FeaturedProducts = model.Filter.FeaturedProducts,
751+
IsActiveCustomer = model.Filter.IsActiveCustomer,
752+
IsTaxExempt = model.Filter.IsTaxExempt,
753+
BillingCountryIds = model.Filter.BillingCountryIds,
754+
ShippingCountryIds = model.Filter.ShippingCountryIds,
755+
LastActivityFrom = model.Filter.LastActivityFrom,
756+
LastActivityTo = model.Filter.LastActivityTo,
757+
HasSpentAtLeastAmount = model.Filter.HasSpentAtLeastAmount,
758+
HasPlacedAtLeastOrders = model.Filter.HasPlacedAtLeastOrders,
730759
ProductType = model.Filter.ProductType,
731760
IdMinimum = model.Filter.IdMinimum,
732761
IdMaximum = model.Filter.IdMaximum,

‎src/Presentation/SmartStore.Web/Administration/Models/DataExchange/ExportFilterModel.cs‎

Lines changed: 34 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,12 @@ public class ExportFilterModel
2020
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.CreatedTo")]
2121
public DateTime? CreatedTo { get; set; }
2222

23+
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.CustomerRoleIds")]
24+
public int[] CustomerRoleIds { get; set; }
25+
public List<SelectListItem> AvailableCustomerRoles { get; set; }
26+
27+
public List<SelectListItem> AvailableCountries { get; set; }
28+
2329
#endregion
2430

2531
#region Product
@@ -72,6 +78,34 @@ public class ExportFilterModel
7278

7379
#endregion
7480

81+
#region Customer
82+
83+
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.IsActiveCustomer")]
84+
public bool? IsActiveCustomer { get; set; }
85+
86+
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.IsTaxExempt")]
87+
public bool? IsTaxExempt { get; set; }
88+
89+
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.BillingCountryIds")]
90+
public int[] BillingCountryIds { get; set; }
91+
92+
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.ShippingCountryIds")]
93+
public int[] ShippingCountryIds { get; set; }
94+
95+
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.LastActivityFrom")]
96+
public DateTime? LastActivityFrom { get; set; }
97+
98+
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.LastActivityTo")]
99+
public DateTime? LastActivityTo { get; set; }
100+
101+
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.HasSpentAtLeastAmount")]
102+
public decimal? HasSpentAtLeastAmount { get; set; }
103+
104+
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.HasPlacedAtLeastOrders")]
105+
public int? HasPlacedAtLeastOrders { get; set; }
106+
107+
#endregion
108+
75109
#region Order
76110

77111
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.OrderStatusIds")]
@@ -86,10 +120,6 @@ public class ExportFilterModel
86120
public int[] ShippingStatusIds { get; set; }
87121
public List<SelectListItem> AvailableShippingStates { get; set; }
88122

89-
[SmartResourceDisplayName("Admin.DataExchange.Export.Filter.CustomerRoleIds")]
90-
public int[] CustomerRoleIds { get; set; }
91-
public List<SelectListItem> AvailableCustomerRoles { get; set; }
92-
93123
#endregion
94124

95125
#region Newsletter Subscription

‎src/Presentation/SmartStore.Web/Administration/Views/Export/Edit.cshtml‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -248,6 +248,7 @@
248248
@helper TabFilter()
249249
{
250250
if (Model.Provider.EntityType == ExportEntityType.Product ||
251+
Model.Provider.EntityType == ExportEntityType.Customer ||
251252
Model.Provider.EntityType == ExportEntityType.Order ||
252253
Model.Provider.EntityType == ExportEntityType.NewsLetterSubscription)
253254
{

0 commit comments

Comments
 (0)