관리-도구
편집 파일: Segments.php
<?php namespace App\Models; use App\Library\Tool; use App\Library\Traits\HasUid; use Carbon\Carbon; use DB; use Exception; use Illuminate\Database\Eloquent\Model; use Illuminate\Database\Eloquent\Relations\BelongsTo; use Illuminate\Database\Eloquent\Relations\HasMany; use Illuminate\Database\Query\Builder; class Segments extends Model { use HasUid; /** * @var string[] */ protected $fillable = [ 'name', 'matching', ]; /** * Retrieves the ContactGroups model associated with this instance. * * @return BelongsTo The ContactGroups model associated with this instance. */ public function contactGroup(): BelongsTo { return $this->belongsTo('App\Models\ContactGroups'); } /** * A description of the segmentConditions function. */ public function segmentConditions(): HasMany { return $this->hasMany('App\Models\SegmentConditions'); } /** * Returns the type options for the function. * * @return array An array of type options. */ public static function getTypeOptions() { return [ ['text' => __('locale.labels.all'), 'value' => 'all'], ['text' => __('locale.labels.any'), 'value' => 'any'], ]; } /** * Get the list of operators. * * @return array The list of operators. */ public static function operators() { return [ ['text' => __('locale.labels.equal'), 'value' => 'equal'], ['text' => __('locale.labels.not_equal'), 'value' => 'not_equal'], ['text' => __('locale.labels.contains'), 'value' => 'contains'], ['text' => __('locale.labels.not_contains'), 'value' => 'not_contains'], ['text' => __('locale.labels.starts'), 'value' => 'starts'], ['text' => __('locale.labels.ends'), 'value' => 'ends'], ['text' => __('locale.labels.not_starts'), 'value' => 'not_starts'], ['text' => __('locale.labels.not_ends'), 'value' => 'not_ends'], ['text' => __('locale.labels.greater'), 'value' => 'greater'], ['text' => __('locale.labels.less'), 'value' => 'less'], ['text' => __('locale.labels.blank'), 'value' => 'blank'], ['text' => __('locale.labels.not_blank'), 'value' => 'not_blank'], ]; } /** * Returns an array of date operators. * * @return array */ public static function dateOperators() { return [ ['text' => __('locale.labels.later'), 'value' => 'greater'], ['text' => __('locale.labels.earlier'), 'value' => 'less'], ['text' => __('locale.labels.is'), 'value' => 'equal'], ['text' => __('locale.labels.is_not'), 'value' => 'not_equal'], ['text' => __('locale.labels.blank'), 'value' => 'blank'], ['text' => __('locale.labels.not_blank'), 'value' => 'not_blank'], ]; } /** * Generates an array of verification operators. * * @return array An array of verification operators. */ public static function verificationOperators() { return [ ['text' => __('locale.labels.equal'), 'value' => 'verification_equal'], ['text' => __('locale.labels.not_equal'), 'value' => 'verification_not_equal'], ]; } /** * Generates an array of created date operators. * * @return array The array of created date operators. */ public static function createdDateOperators() { return [ ['text' => __('locale.labels.greater_than'), 'value' => 'created_date_greater'], ['text' => __('locale.labels.less_than'), 'value' => 'created_date_less'], ['text' => __('locale.labels.last_x_days'), 'value' => 'created_date_last_x_days'], ]; } /** * Return an array of tag operators. * * @return array An array of tag operators. */ public static function tagOperators() { return [ ['text' => __('locale.labels.contains'), 'value' => 'tag_contains'], ['text' => __('locale.labels.not_contains'), 'value' => 'tag_not_contains'], ]; } /** * Retrieves the conditions for the subscribers. * * @return array|null Returns an array containing the joins and conditions for the subscribers, or null if no conditions exist. * * @throws Exception Throws an exception if an unknown segment condition type (operator) is encountered. */ public function getSubscribersConditions() { if (! $this->segmentConditions()->exists()) { return null; } $conditions = []; $joins = []; foreach ($this->segmentConditions as $condition) { $number = uniqid(); $keyword = $condition->value; $keyword = str_replace('[EMPTY]', '', $keyword); $keyword = str_replace('[DATETIME]', date('Y-m-d H:i:s'), $keyword); $keyword = str_replace('[DATE]', date('Y-m-d'), $keyword); $keyword = trim(strtolower($keyword)); // If conditions with fields if (isset($condition->field_id)) { $type = $condition->field->type; switch ($condition->operator) { case 'equal': if ($type == 'number') { $cond = sprintf('CAST(sf%s.value AS SIGNED) = CAST(%s AS SIGNED)', $number, Tool::db_quote($keyword)); } else { $cond = 'LOWER(sf'.$number.'.value) = '.Tool::db_quote($keyword); } break; case 'not_equal': if ($type == 'number') { $cond = sprintf('CAST(sf%s.value AS SIGNED) != CAST(%s AS SIGNED)', $number, Tool::db_quote($keyword)); } else { $cond = 'LOWER(sf'.$number.'.value) != '.Tool::db_quote($keyword); } break; case 'contains': $cond = 'LOWER(sf'.$number.'.value) LIKE '.Tool::db_quote('%'.$keyword.'%'); break; case 'not_contains': $cond = '(LOWER(sf'.$number.'.value) NOT LIKE '.Tool::db_quote('%'.$keyword.'%').' OR sf'.$number.'.value IS NULL)'; break; case 'starts': $cond = 'LOWER(sf'.$number.'.value) LIKE '.Tool::db_quote($keyword.'%'); break; case 'ends': $cond = 'LOWER(sf'.$number.'.value) LIKE '.Tool::db_quote('%'.$keyword); break; case 'greater': if ($type == 'number') { $cond = sprintf('CAST(sf%s.value AS SIGNED) > CAST(%s AS SIGNED)', $number, Tool::db_quote($keyword)); } else { $cond = 'sf'.$number.'.value > '.Tool::db_quote($keyword); } break; case 'less': if ($type == 'number') { $cond = sprintf('CAST(sf%s.value AS SIGNED) < CAST(%s AS SIGNED)', $number, Tool::db_quote($keyword)); } else { $cond = 'sf'.$number.'.value < '.Tool::db_quote($keyword); } break; case 'not_starts': $cond = 'sf'.$number.'.value NOT LIKE '.Tool::db_quote($keyword.'%'); break; case 'not_ends': $cond = 'LOWER(sf'.$number.'.value) NOT LIKE '.Tool::db_quote('%'.$keyword); break; case 'not_blank': $cond = '(LOWER(sf'.$number.".value) != '' AND LOWER(sf".$number.'.value) IS NOT NULL)'; break; case 'blank': $cond = '(LOWER(sf'.$number.".value) = '' OR LOWER(sf".$number.'.value) IS NULL)'; break; default: throw new Exception('Unknown segment condition type (operator): '.$condition->operator); } // add to joins array $joins[] = [ 'table' => DB::raw(DB::getTablePrefix().'subscriber_fields as sf'.$number), 'ons' => [ [DB::raw('sf'.$number.'.subscriber_id'), DB::raw(DB::getTablePrefix().'contacts.id')], [DB::raw('sf'.$number.'.field_id'), DB::raw($condition->field_id)], ], ]; // add condition $conditions[] = $cond; } else { switch ($condition->operator) { case 'tag_contains': // add condition $conditions[] = '('.DB::getTablePrefix().'contacts.tags LIKE '.Tool::db_quote('%"'.$keyword.'"%').')'; break; case 'tag_not_contains': // add condition $conditions[] = '('.DB::getTablePrefix().'contacts.tags NOT LIKE '.Tool::db_quote('%"'.$keyword.'"%').')'; break; case 'created_date_greater': $ts = Carbon::createFromFormat('Y-m-d H:i:s', $condition->value)->timestamp; $conditions[] = '(UNIX_TIMESTAMP('.DB::getTablePrefix().'contacts.created_at) > '.$ts.')'; break; case 'created_date_less': $ts = Carbon::createFromFormat('Y-m-d H:i:s', $condition->value)->timestamp; $conditions[] = '(UNIX_TIMESTAMP('.DB::getTablePrefix().'contacts.created_at) < '.$ts.')'; break; case 'created_date_last_x_days': $ts = Carbon::now()->subDays($condition->value)->timestamp; $conditions[] = '(UNIX_TIMESTAMP('.DB::getTablePrefix().'contacts.created_at) >= '.$ts.')'; break; default: throw new Exception('Unknown segment condition type (operator): '.$condition->operator); } } } //return $conditions; if ($this->matching == 'any') { $conditions = implode(' OR ', $conditions); } else { $conditions = implode(' AND ', $conditions); } return [ 'joins' => $joins, 'conditions' => $conditions, ]; } /** * Retrieves the subscribers for the contact group. * * @return Builder The query builder for retrieving subscribers. * * @throws Exception */ public function subscribers() { $query = $this->contactGroup->subscribers(); $query->select('contacts.*'); $conditions = $this->getSubscribersConditions(); // Apply the filter if (! empty($conditions['joins'])) { foreach ($conditions['joins'] as $joining) { $query = $query->leftJoin($joining['table'], function ($join) use ($joining) { $join->on($joining['ons'][0][0], '=', $joining['ons'][0][1]); if (isset($joining['ons'][1])) { $join->on($joining['ons'][1][0], '=', $joining['ons'][1][1]); } }); } } if (! empty($conditions['conditions'])) { $query = $query->whereRaw('('.$conditions['conditions'].')'); } return $query; } /** * Checks if a subscriber is included. * * @param Contacts $contact The subscriber to check. * @return bool Returns true if the subscriber is included, false otherwise. * * @throws Exception */ public function isSubscriberIncluded(Contacts $contact) { return $this->subscribers() ->where('uid', $contact->uid) ->exists(); } /** * Updates the conditions of the segment. * * @param array $conditions The conditions to update. */ public function updateConditions(array $conditions) { if ($this->id) { $this->segmentConditions()->delete(); } foreach ($conditions as $param) { $condition = new SegmentConditions(); $condition->fill($param); if (str_starts_with($condition->operator, 'created_date') && $condition->operator !== 'created_date_last_x_days') { $zone = $this->contactGroup->customer->getTimezone(); $date = Carbon::createFromFormat('Y-m-d, H:i', $condition->value, $zone); $condition->value = $date->toDateTimeString(); } $condition->segment_id = $this->id; $field = ContactGroupFields::findByUid($param['field_id']); if ($field) { $condition->field_id = $field->id; } else { $condition->field_id = null; } $condition->save(); } } }