Kevin
d21d94a060
Admin-Bereich "Newsletter-Listen" (Plan dev/_tickets/entwicklungsplan- newsletter-listen.md): Berater und Kunden nach Filtern zusammenstellen, als Liste speichern und wieder laden, Export als XLSX/CSV. Jeder Export wird protokolliert (newsletter_list_exports). Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
573 lines
22 KiB
PHP
573 lines
22 KiB
PHP
<?php
|
||
|
||
namespace App\Services\Newsletter;
|
||
|
||
use App\Models\Country;
|
||
use App\Models\ShoppingUser;
|
||
use App\Models\UserAbo;
|
||
use App\Models\UserAccount;
|
||
use App\Models\UserLevel;
|
||
use App\Services\HTMLHelper;
|
||
use App\User;
|
||
use Illuminate\Database\Eloquent\Builder;
|
||
use Illuminate\Support\Collection;
|
||
use Illuminate\Support\Facades\Cache;
|
||
|
||
/**
|
||
* Stellt Newsletter-Empfängerlisten aus Kunden (shopping_users) und Beratern
|
||
* (users + user_accounts) zusammen. Pflichtausschlüsse (gelöscht, anonymisiert,
|
||
* Fake-Mails, Test-Datensätze) sind fest verdrahtet, alles andere kommt aus dem Filter.
|
||
* Doppelte E-Mails werden über beide Quellen hinweg entfernt.
|
||
*/
|
||
class NewsletterListService
|
||
{
|
||
public const SOURCE_CUSTOMER = 'customer';
|
||
|
||
public const SOURCE_CONSULTANT = 'consultant';
|
||
|
||
public const OPTIN_YES = 'yes';
|
||
|
||
public const OPTIN_NO = 'no';
|
||
|
||
public const OPTIN_CONTRACT = 'contract';
|
||
|
||
public const STATUS_ACTIVE = 'active';
|
||
|
||
public const STATUS_INACTIVE = 'inactive';
|
||
|
||
private const CACHE_TTL_SECONDS = 300;
|
||
|
||
private const CHUNK_SIZE = 1000;
|
||
|
||
/**
|
||
* @var array<string, array<string, string>>|null
|
||
*/
|
||
private ?array $labels = null;
|
||
|
||
private ?string $fallbackLocale = null;
|
||
|
||
/**
|
||
* Spaltenschlüssel in Export-Reihenfolge. Die ersten neun entsprechen der Vorlage
|
||
* "Kundenliste" (rapidmail), der Rest sind Zusatzspalten.
|
||
*
|
||
* @var string[]
|
||
*/
|
||
private const TEMPLATE_COLUMNS = [
|
||
'email', 'firstname', 'lastname', 'salutation_label', 'street', 'zipcode', 'city', 'company', 'consultant',
|
||
];
|
||
|
||
/**
|
||
* @var string[]
|
||
*/
|
||
private const EXTRA_COLUMNS = [
|
||
'country', 'language', 'source_label', 'number', 'consultant_number', 'optin_label', 'status_label', 'created_at',
|
||
];
|
||
|
||
public function build(NewsletterListFilter $filter): NewsletterListResult
|
||
{
|
||
$stats = [
|
||
'customers_raw' => 0,
|
||
'consultants_raw' => 0,
|
||
'invalid' => 0,
|
||
'duplicates' => 0,
|
||
'total' => 0,
|
||
'customers' => 0,
|
||
'consultants' => 0,
|
||
];
|
||
$valid = 0;
|
||
/** @var array<string, array<string, mixed>> $best */
|
||
$best = [];
|
||
|
||
$collect = function (array $row) use (&$best, &$valid, &$stats): void {
|
||
if (! self::isValidEmail($row['email'])) {
|
||
$stats['invalid']++;
|
||
|
||
return;
|
||
}
|
||
|
||
$valid++;
|
||
$email = $row['email'];
|
||
if (! isset($best[$email]) || self::outranks($row, $best[$email])) {
|
||
$best[$email] = $row;
|
||
}
|
||
};
|
||
|
||
if ($filter->includesConsultants()) {
|
||
foreach ($this->consultantQuery($filter)->toBase()->lazyById(self::CHUNK_SIZE, 'users.id', 'id') as $user) {
|
||
$stats['consultants_raw']++;
|
||
$collect($this->mapConsultant($user));
|
||
}
|
||
}
|
||
|
||
if ($filter->includesCustomers()) {
|
||
foreach ($this->customerQuery($filter)->toBase()->lazyById(self::CHUNK_SIZE, 'shopping_users.id', 'id') as $shoppingUser) {
|
||
$stats['customers_raw']++;
|
||
$collect($this->mapCustomer($shoppingUser));
|
||
}
|
||
}
|
||
|
||
ksort($best, SORT_NATURAL | SORT_FLAG_CASE);
|
||
|
||
$stats['duplicates'] = $valid - count($best);
|
||
$stats['total'] = count($best);
|
||
foreach ($best as $row) {
|
||
$row['source'] === self::SOURCE_CUSTOMER ? $stats['customers']++ : $stats['consultants']++;
|
||
}
|
||
|
||
return new NewsletterListResult(collect(array_values($best)), $stats);
|
||
}
|
||
|
||
/**
|
||
* Dedupe-Priorität: Berater vor Kunde, Opt-in vor ohne, Käufer vor Nicht-Käufer, dann neuester Datensatz.
|
||
*
|
||
* @param array<string, mixed> $candidate
|
||
* @param array<string, mixed> $current
|
||
*/
|
||
private static function outranks(array $candidate, array $current): bool
|
||
{
|
||
if ($candidate['priority'] !== $current['priority']) {
|
||
return $candidate['priority'] > $current['priority'];
|
||
}
|
||
|
||
return $candidate['id'] > $current['id'];
|
||
}
|
||
|
||
/**
|
||
* Zwischengespeicherte Variante für die Vorschau-Datatable, damit Blättern und
|
||
* Sortieren nicht jedes Mal die komplette Liste neu berechnen.
|
||
*/
|
||
public function cached(NewsletterListFilter $filter, int $userId, bool $refresh = false): NewsletterListResult
|
||
{
|
||
$key = 'newsletter_list:'.$userId.':'.$filter->cacheKey();
|
||
|
||
if ($refresh) {
|
||
Cache::forget($key);
|
||
}
|
||
|
||
return Cache::remember($key, self::CACHE_TTL_SECONDS, fn () => $this->build($filter));
|
||
}
|
||
|
||
/**
|
||
* @return Builder<ShoppingUser>
|
||
*/
|
||
public function customerQuery(NewsletterListFilter $filter): Builder
|
||
{
|
||
$countryName = 'bc.'.$this->countryNameColumn();
|
||
|
||
$query = ShoppingUser::query()
|
||
->select([
|
||
'shopping_users.id', 'shopping_users.billing_email', 'shopping_users.billing_firstname', 'shopping_users.billing_lastname',
|
||
'shopping_users.billing_salutation', 'shopping_users.billing_address', 'shopping_users.billing_address_2',
|
||
'shopping_users.billing_zipcode', 'shopping_users.billing_city', 'shopping_users.billing_company',
|
||
'shopping_users.language', 'shopping_users.number', 'shopping_users.subscribed', 'shopping_users.has_buyed',
|
||
'shopping_users.created_at',
|
||
'bc.code as country_code', $countryName.' as country_name',
|
||
'ma.first_name as consultant_firstname', 'ma.last_name as consultant_lastname', 'ma.m_account as consultant_number',
|
||
])
|
||
->leftJoin('countries as bc', 'bc.id', '=', 'shopping_users.billing_country_id')
|
||
->leftJoin('users as m', 'm.id', '=', 'shopping_users.member_id')
|
||
->leftJoin('user_accounts as ma', 'ma.id', '=', 'm.account_id')
|
||
->whereNull('shopping_users.user_deleted_at')
|
||
->where(fn ($q) => $q->whereNull('shopping_users.auth_user_id')->orWhere('shopping_users.auth_user_id', 0))
|
||
->where(fn ($q) => $q->whereNull('shopping_users.faker_mail')->orWhere('shopping_users.faker_mail', 0))
|
||
->whereNotNull('shopping_users.billing_email')
|
||
->where('shopping_users.billing_email', '!=', '')
|
||
->where('shopping_users.billing_email', 'not like', '%@dsgvo.invalid')
|
||
->where('shopping_users.billing_email', 'not like', '%faker@mivita.care')
|
||
->where(fn ($q) => $q->whereNull('shopping_users.mode')->orWhere('shopping_users.mode', 'live'));
|
||
|
||
$this->applyLanguage($query, 'shopping_users.language', $filter->language);
|
||
|
||
if ($filter->countryIds !== []) {
|
||
$query->whereIn('shopping_users.billing_country_id', $filter->countryIds);
|
||
}
|
||
|
||
if ($filter->optin === NewsletterListFilter::OPTIN_SUBSCRIBED) {
|
||
$query->where('shopping_users.subscribed', 1);
|
||
} elseif ($filter->optin === NewsletterListFilter::OPTIN_UNSUBSCRIBED) {
|
||
$query->where(fn ($q) => $q->whereNull('shopping_users.subscribed')->orWhere('shopping_users.subscribed', 0));
|
||
}
|
||
|
||
$this->applyPurchase($query, $filter);
|
||
|
||
if ($filter->memberIdIsNone()) {
|
||
$query->where(fn ($q) => $q->whereNull('shopping_users.member_id')->orWhere('shopping_users.member_id', 0));
|
||
} elseif ($filter->memberIdValue() !== null) {
|
||
$query->where('shopping_users.member_id', $filter->memberIdValue());
|
||
}
|
||
|
||
$this->applyCreatedRange($query, 'shopping_users.created_at', $filter);
|
||
$this->applyCompany($query, 'shopping_users.billing_company', $filter->company);
|
||
|
||
return $query;
|
||
}
|
||
|
||
/**
|
||
* @return Builder<User>
|
||
*/
|
||
public function consultantQuery(NewsletterListFilter $filter): Builder
|
||
{
|
||
$countryName = 'c.'.$this->countryNameColumn();
|
||
|
||
$query = User::query()
|
||
->select([
|
||
'users.id', 'users.email', 'users.lang', 'users.active', 'users.created_at',
|
||
'a.salutation', 'a.first_name', 'a.last_name', 'a.address', 'a.address_2', 'a.zipcode', 'a.city', 'a.company',
|
||
'a.m_account as number',
|
||
'c.code as country_code', $countryName.' as country_name',
|
||
'sa.first_name as consultant_firstname', 'sa.last_name as consultant_lastname', 'sa.m_account as consultant_number',
|
||
'l.name as level',
|
||
])
|
||
->leftJoin('user_accounts as a', 'a.id', '=', 'users.account_id')
|
||
->leftJoin('countries as c', 'c.id', '=', 'a.country_id')
|
||
->leftJoin('users as s', 's.id', '=', 'users.m_sponsor')
|
||
->leftJoin('user_accounts as sa', 'sa.id', '=', 's.account_id')
|
||
->leftJoin('user_levels as l', 'l.id', '=', 'users.m_level')
|
||
->whereNull('users.pre_deleted_at')
|
||
->where(fn ($q) => $q->whereNull('users.blocked')->orWhere('users.blocked', 0))
|
||
->where('users.admin', '<', $filter->includeAdmins ? 5 : 2)
|
||
->where(fn ($q) => $q->whereNull('users.test_mode')->orWhere('users.test_mode', 0))
|
||
->whereNotNull('users.email')
|
||
->where('users.email', '!=', '')
|
||
->where('users.email', 'not like', 'delete-%');
|
||
|
||
$this->applyLanguage($query, 'users.lang', $filter->language);
|
||
|
||
if ($filter->countryIds !== []) {
|
||
$query->whereIn('a.country_id', $filter->countryIds);
|
||
}
|
||
|
||
if ($filter->consultantStatus === NewsletterListFilter::CONSULTANT_STATUS_ACTIVE) {
|
||
$query->where('users.active', 1);
|
||
} elseif ($filter->consultantStatus === NewsletterListFilter::CONSULTANT_STATUS_INACTIVE) {
|
||
$query->where(fn ($q) => $q->whereNull('users.active')->orWhere('users.active', 0));
|
||
}
|
||
|
||
if ($filter->levelIds !== []) {
|
||
$query->whereIn('users.m_level', $filter->levelIds);
|
||
}
|
||
|
||
if ($filter->memberIdIsNone()) {
|
||
$query->where(fn ($q) => $q->whereNull('users.m_sponsor')->orWhere('users.m_sponsor', 0));
|
||
} elseif ($filter->memberIdValue() !== null) {
|
||
$query->where('users.m_sponsor', $filter->memberIdValue());
|
||
}
|
||
|
||
$this->applyCreatedRange($query, 'users.created_at', $filter);
|
||
|
||
$this->applyCompany($query, 'a.company', $filter->company);
|
||
|
||
return $query;
|
||
}
|
||
|
||
/**
|
||
* Kopfzeile für XLSX/CSV.
|
||
*
|
||
* @return string[]
|
||
*/
|
||
public function exportHeaders(bool $templateOnly): array
|
||
{
|
||
return array_map(
|
||
fn (string $column) => __('newsletter.export.'.$column),
|
||
$this->exportColumns($templateOnly)
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Zeilen für XLSX/CSV in Kopfzeilen-Reihenfolge.
|
||
*
|
||
* @param Collection<int, array<string, mixed>> $rows
|
||
* @return array<int, array<int, string>>
|
||
*/
|
||
public function exportRows(Collection $rows, bool $templateOnly): array
|
||
{
|
||
$columns = $this->exportColumns($templateOnly);
|
||
|
||
return $rows
|
||
->map(fn (array $row) => array_map(fn (string $column) => (string) ($row[$column] ?? ''), $columns))
|
||
->values()
|
||
->all();
|
||
}
|
||
|
||
/**
|
||
* @return string[]
|
||
*/
|
||
public function exportColumns(bool $templateOnly): array
|
||
{
|
||
return $templateOnly ? self::TEMPLATE_COLUMNS : array_merge(self::TEMPLATE_COLUMNS, self::EXTRA_COLUMNS);
|
||
}
|
||
|
||
/**
|
||
* Lesbare Kurzfassung eines Filters für Listen-Übersicht und Protokoll,
|
||
* z. B. "Kunden · Sprache: DE · Land: AT, CH · Opt-in: nur mit Opt-in".
|
||
*/
|
||
public function describe(NewsletterListFilter $filter): string
|
||
{
|
||
$parts = [__('newsletter.source_option.'.$filter->source)];
|
||
$parts[] = __('newsletter.filter.language').': '.($filter->language === null ? __('newsletter.filter.all') : strtoupper($filter->language));
|
||
|
||
if ($filter->countryIds !== []) {
|
||
$codes = Country::query()->whereIn('id', $filter->countryIds)->orderBy('code')->pluck('code')->all();
|
||
$parts[] = __('newsletter.filter.country').': '.implode(', ', $codes);
|
||
}
|
||
|
||
if ($filter->includesCustomers()) {
|
||
if ($filter->optin !== NewsletterListFilter::OPTIN_ALL) {
|
||
$parts[] = __('newsletter.filter.optin').': '.__('newsletter.optin_option.'.$filter->optin);
|
||
}
|
||
if ($filter->purchase !== NewsletterListFilter::PURCHASE_ALL) {
|
||
$parts[] = __('newsletter.filter.purchase').': '.__('newsletter.purchase_option.'.$filter->purchase);
|
||
}
|
||
}
|
||
|
||
if ($filter->memberIdIsNone()) {
|
||
$parts[] = __('newsletter.filter.member').': '.__('newsletter.filter.none_member');
|
||
} elseif ($filter->memberIdValue() !== null) {
|
||
$account = UserAccount::query()
|
||
->join('users', 'users.account_id', '=', 'user_accounts.id')
|
||
->where('users.id', $filter->memberIdValue())
|
||
->first(['user_accounts.first_name', 'user_accounts.last_name']);
|
||
$parts[] = __('newsletter.filter.member').': '.($account ? self::fullName($account->first_name, $account->last_name) : '#'.$filter->memberIdValue());
|
||
}
|
||
|
||
if ($filter->createdFrom !== null || $filter->createdTo !== null) {
|
||
$parts[] = __('newsletter.filter.created_from').': '.($filter->createdFrom?->format('d.m.Y') ?? '…').' – '.($filter->createdTo?->format('d.m.Y') ?? '…');
|
||
}
|
||
|
||
if ($filter->company !== NewsletterListFilter::COMPANY_ALL) {
|
||
$parts[] = __('newsletter.filter.company').': '.__('newsletter.company_option.'.$filter->company);
|
||
}
|
||
|
||
if ($filter->includesConsultants()) {
|
||
$parts[] = __('newsletter.filter.consultant_status').': '.__('newsletter.consultant_status_option.'.$filter->consultantStatus);
|
||
if ($filter->levelIds !== []) {
|
||
$names = UserLevel::query()->whereIn('id', $filter->levelIds)->orderBy('pos')->pluck('name')->all();
|
||
$parts[] = __('newsletter.filter.level').': '.implode(', ', $names);
|
||
}
|
||
if ($filter->includeAdmins) {
|
||
$parts[] = __('newsletter.filter.include_admins');
|
||
}
|
||
}
|
||
|
||
return implode(' · ', $parts);
|
||
}
|
||
|
||
public static function normalizeEmail(?string $email): string
|
||
{
|
||
return mb_strtolower(trim((string) $email));
|
||
}
|
||
|
||
public static function isValidEmail(string $email): bool
|
||
{
|
||
return $email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
|
||
}
|
||
|
||
/**
|
||
* @return array<string, mixed>
|
||
*/
|
||
private function mapCustomer(object $row): array
|
||
{
|
||
$subscribed = (bool) $row->subscribed;
|
||
$hasBuyed = (bool) $row->has_buyed;
|
||
|
||
return $this->row([
|
||
'id' => (int) $row->id,
|
||
'source' => self::SOURCE_CUSTOMER,
|
||
'email' => self::normalizeEmail($row->billing_email),
|
||
'firstname' => trim((string) $row->billing_firstname),
|
||
'lastname' => trim((string) $row->billing_lastname),
|
||
'salutation' => (string) $row->billing_salutation,
|
||
'street' => self::joinAddress($row->billing_address, $row->billing_address_2),
|
||
'zipcode' => trim((string) $row->billing_zipcode),
|
||
'city' => trim((string) $row->billing_city),
|
||
'company' => trim((string) $row->billing_company),
|
||
'consultant' => self::fullName($row->consultant_firstname, $row->consultant_lastname),
|
||
'consultant_number' => (string) ($row->consultant_number ?? ''),
|
||
'country' => (string) ($row->country_code ?? ''),
|
||
'country_name' => (string) ($row->country_name ?? ''),
|
||
'language' => $this->languageOrFallback($row->language),
|
||
'number' => (string) ($row->number ?? ''),
|
||
'optin' => $subscribed ? self::OPTIN_YES : self::OPTIN_NO,
|
||
'status' => self::STATUS_ACTIVE,
|
||
'created_at' => self::dateOnly($row->created_at),
|
||
'priority' => 100 + ($subscribed ? 10 : 0) + ($hasBuyed ? 1 : 0),
|
||
]);
|
||
}
|
||
|
||
/**
|
||
* @return array<string, mixed>
|
||
*/
|
||
private function mapConsultant(object $row): array
|
||
{
|
||
$active = (bool) $row->active;
|
||
|
||
return $this->row([
|
||
'id' => (int) $row->id,
|
||
'source' => self::SOURCE_CONSULTANT,
|
||
'email' => self::normalizeEmail($row->email),
|
||
'firstname' => trim((string) $row->first_name),
|
||
'lastname' => trim((string) $row->last_name),
|
||
'salutation' => (string) ($row->salutation ?? ''),
|
||
'street' => self::joinAddress($row->address, $row->address_2),
|
||
'zipcode' => trim((string) $row->zipcode),
|
||
'city' => trim((string) $row->city),
|
||
'company' => trim((string) $row->company),
|
||
'consultant' => self::fullName($row->consultant_firstname, $row->consultant_lastname),
|
||
'consultant_number' => (string) ($row->consultant_number ?? ''),
|
||
'country' => (string) ($row->country_code ?? ''),
|
||
'country_name' => (string) ($row->country_name ?? ''),
|
||
'language' => $this->languageOrFallback($row->lang),
|
||
'number' => (string) ($row->number ?? ''),
|
||
'optin' => self::OPTIN_CONTRACT,
|
||
'status' => $active ? self::STATUS_ACTIVE : self::STATUS_INACTIVE,
|
||
'level' => (string) ($row->level ?? ''),
|
||
'created_at' => self::dateOnly($row->created_at),
|
||
'priority' => 1000 + ($active ? 1 : 0),
|
||
]);
|
||
}
|
||
|
||
/**
|
||
* Ergänzt die sprachabhängigen Anzeige-Spalten, damit Vorschau und Export dieselben Werte zeigen.
|
||
*
|
||
* @param array<string, mixed> $row
|
||
* @return array<string, mixed>
|
||
*/
|
||
private function row(array $row): array
|
||
{
|
||
$labels = $this->labels();
|
||
$row['salutation_label'] = $labels['salutation'][$row['salutation']] ?? '';
|
||
$row['source_label'] = $labels['source'][$row['source']] ?? '';
|
||
$row['optin_label'] = $labels['optin'][$row['optin']] ?? '';
|
||
$row['status_label'] = $labels['status'][$row['status']] ?? '';
|
||
$row['level'] ??= '';
|
||
|
||
return $row;
|
||
}
|
||
|
||
/**
|
||
* Übersetzungen einmal pro Lauf auflösen statt pro Zeile (bei 70k Zeilen spürbar).
|
||
*
|
||
* @return array<string, array<string, string>>
|
||
*/
|
||
private function labels(): array
|
||
{
|
||
return $this->labels ??= [
|
||
'salutation' => [
|
||
'mr' => HTMLHelper::getSalutationLang('mr'),
|
||
'ms' => HTMLHelper::getSalutationLang('ms'),
|
||
'di' => HTMLHelper::getSalutationLang('di'),
|
||
],
|
||
'source' => [
|
||
self::SOURCE_CUSTOMER => __('newsletter.source.customer'),
|
||
self::SOURCE_CONSULTANT => __('newsletter.source.consultant'),
|
||
],
|
||
'optin' => [
|
||
self::OPTIN_YES => __('newsletter.optin_value.yes'),
|
||
self::OPTIN_NO => __('newsletter.optin_value.no'),
|
||
self::OPTIN_CONTRACT => __('newsletter.optin_value.contract'),
|
||
],
|
||
'status' => [
|
||
self::STATUS_ACTIVE => __('newsletter.status_value.active'),
|
||
self::STATUS_INACTIVE => __('newsletter.status_value.inactive'),
|
||
],
|
||
];
|
||
}
|
||
|
||
private static function joinAddress(?string $address, ?string $address2): string
|
||
{
|
||
return trim(implode(', ', array_filter([trim((string) $address), trim((string) $address2)])));
|
||
}
|
||
|
||
private static function fullName(?string $firstname, ?string $lastname): string
|
||
{
|
||
return trim(trim((string) $firstname).' '.trim((string) $lastname));
|
||
}
|
||
|
||
private function languageOrFallback(?string $language): string
|
||
{
|
||
$language = trim((string) $language);
|
||
|
||
return $language !== '' ? $language : ($this->fallbackLocale ??= (string) config('app.fallback_locale', 'de'));
|
||
}
|
||
|
||
private static function dateOnly(mixed $value): string
|
||
{
|
||
if ($value === null || $value === '') {
|
||
return '';
|
||
}
|
||
|
||
return substr((string) $value, 0, 10);
|
||
}
|
||
|
||
/**
|
||
* Spalte der countries-Tabelle mit dem Ländernamen in der aktuellen Oberflächensprache.
|
||
*/
|
||
private function countryNameColumn(): string
|
||
{
|
||
$locale = (string) app()->getLocale();
|
||
|
||
return in_array($locale, ['de', 'en', 'es', 'fr', 'it', 'ru'], true) ? $locale : 'de';
|
||
}
|
||
|
||
/**
|
||
* Sprache "de" schließt Datensätze ohne gesetzte Sprache ein, weil das System
|
||
* dort auf die Fallback-Sprache zurückfällt.
|
||
*/
|
||
private function applyLanguage(Builder $query, string $column, ?string $language): void
|
||
{
|
||
if ($language === null) {
|
||
return;
|
||
}
|
||
|
||
if ($language === config('app.fallback_locale', 'de')) {
|
||
$query->where(fn ($q) => $q->where($column, $language)->orWhereNull($column)->orWhere($column, ''));
|
||
|
||
return;
|
||
}
|
||
|
||
$query->where($column, $language);
|
||
}
|
||
|
||
private function applyPurchase(Builder $query, NewsletterListFilter $filter): void
|
||
{
|
||
$months = $filter->purchaseMonths();
|
||
|
||
if ($months !== null) {
|
||
$query->whereHas('shopping_orders', fn ($q) => $q
|
||
->where('paid', 1)
|
||
->where('created_at', '>=', now()->subMonths($months)));
|
||
|
||
return;
|
||
}
|
||
|
||
match ($filter->purchase) {
|
||
NewsletterListFilter::PURCHASE_HAS_BUYED => $query->where('shopping_users.has_buyed', 1),
|
||
NewsletterListFilter::PURCHASE_NONE => $query->where(fn ($q) => $q->whereNull('shopping_users.has_buyed')->orWhere('shopping_users.has_buyed', 0)),
|
||
NewsletterListFilter::PURCHASE_ACTIVE_ABO => $query->whereIn(
|
||
'shopping_users.id',
|
||
UserAbo::query()->activeAbo()->whereNotNull('shopping_user_id')->select('shopping_user_id')
|
||
),
|
||
default => null,
|
||
};
|
||
}
|
||
|
||
private function applyCreatedRange(Builder $query, string $column, NewsletterListFilter $filter): void
|
||
{
|
||
if ($filter->createdFrom !== null) {
|
||
$query->where($column, '>=', $filter->createdFrom);
|
||
}
|
||
|
||
if ($filter->createdTo !== null) {
|
||
$query->where($column, '<=', $filter->createdTo);
|
||
}
|
||
}
|
||
|
||
private function applyCompany(Builder $query, string $column, string $company): void
|
||
{
|
||
if ($company === NewsletterListFilter::COMPANY_WITH) {
|
||
$query->whereNotNull($column)->where($column, '!=', '');
|
||
} elseif ($company === NewsletterListFilter::COMPANY_WITHOUT) {
|
||
$query->where(fn ($q) => $q->whereNull($column)->orWhere($column, ''));
|
||
}
|
||
}
|
||
}
|