From e8f5424413d241fed127fae243225bc055b8d913 Mon Sep 17 00:00:00 2001
From: fomichev <vladimir.fomichev@erp-flowers.ru>
Date: Mon, 23 Dec 2024 13:58:37 +0300
Subject: [PATCH] =?utf8?q?=D0=97=D0=B0=D0=BF=D0=BE=D0=BB=D0=BD=D0=B5=D0=BD?=
 =?utf8?q?=D0=B8=D0=B5=20=D1=82=D0=B0=D0=B1=D0=BB=D0=B8=D1=86=D1=8B=20?=
 =?utf8?q?=D0=BF=D0=BE=20=D0=B7=D0=B0=D0=B4=D0=B0=D1=87=D0=B5=2035?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 ...241220_131428_create_sent_kogort_table.php |   1 +
 erp24/records/SentKogort.php                  |  11 +-
 erp24/records/Users.php                       | 214 +++++++++++++++++-
 .../tasks/task_35_form_target_kogort.php      | 145 ++++++++++++
 erp24/views/scheduler_task/test.php           |   2 +-
 5 files changed, 369 insertions(+), 4 deletions(-)
 create mode 100644 erp24/scripts/tasks/task_35_form_target_kogort.php

diff --git a/erp24/migrations/m241220_131428_create_sent_kogort_table.php b/erp24/migrations/m241220_131428_create_sent_kogort_table.php
index 11c8c17e..24389c02 100644
--- a/erp24/migrations/m241220_131428_create_sent_kogort_table.php
+++ b/erp24/migrations/m241220_131428_create_sent_kogort_table.php
@@ -19,6 +19,7 @@ class m241220_131428_create_sent_kogort_table extends Migration
             $this->createTable(self::TABLE_NAME, [
                 'id' => $this->primaryKey(),
                 'phone' => $this->string(15)->notNull()->comment('Телефон пользователя'),
+                'kogort_number' => $this->integer()->notNull()->comment('Номер типа когорты когорты'),
                 'kogort_date' => $this->date()->notNull()->comment('Дата когорты'),
                 'kogort_unixtime' => $this->integer()->notNull()->comment('UNIX-время когорты'),
                 'status' => $this->tinyInteger(1)->notNull()->defaultValue(1)->comment('Вхождение в когорту'),
diff --git a/erp24/records/SentKogort.php b/erp24/records/SentKogort.php
index 575d59a8..eedc2fbc 100644
--- a/erp24/records/SentKogort.php
+++ b/erp24/records/SentKogort.php
@@ -11,6 +11,7 @@ use Yii;
  * @property string $phone Телефон пользователя
  * @property string $kogort_date Дата когорты
  * @property int $kogort_unixtime UNIX-время когорты
+ * @property int $kogort_number Номер типа когорты когорты
  * @property int $status Вхождение в когорту
  * @property int $contact Контакт с клиентом
  * @property int $purchase Покупка
@@ -18,6 +19,11 @@ use Yii;
  */
 class SentKogort extends \yii\db\ActiveRecord
 {
+    public const KOGORT_NUMBERS = [
+        'target' => 1,
+        'whatsapp' => 2,
+        'call' => 3,
+    ];
     /**
      * {@inheritdoc}
      */
@@ -32,10 +38,10 @@ class SentKogort extends \yii\db\ActiveRecord
     public function rules()
     {
         return [
-            [['phone', 'kogort_date', 'kogort_unixtime', 'created_at'], 'required'],
+            [['phone', 'kogort_date', 'kogort_unixtime', 'kogort_number', 'created_at'], 'required'],
             [['kogort_date', 'created_at'], 'safe'],
             [['kogort_unixtime', 'status', 'contact', 'purchase'], 'default', 'value' => null],
-            [['kogort_unixtime', 'status', 'contact', 'purchase'], 'integer'],
+            [['kogort_unixtime', 'status', 'contact', 'purchase', 'kogort_number'], 'integer'],
             [['phone'], 'string', 'max' => 15],
         ];
     }
@@ -49,6 +55,7 @@ class SentKogort extends \yii\db\ActiveRecord
             'id' => 'ID',
             'phone' => 'Телефон пользователя',
             'kogort_date' => 'Дата когорты',
+            'kogort_number' => 'Номер типа когорты когорты',
             'kogort_unixtime' => 'UNIX-время когорты',
             'status' => 'Вхождение в когорту',
             'contact' => 'Контакт с клиентом',
diff --git a/erp24/records/Users.php b/erp24/records/Users.php
index 182120be..e0a72540 100755
--- a/erp24/records/Users.php
+++ b/erp24/records/Users.php
@@ -425,4 +425,216 @@ class Users extends \yii\db\ActiveRecord
         return $data;
     }
 
-}
\ No newline at end of file
+    public static function formKogortByDateAndType($startDate = null, $type = 'target'): array
+    {
+        $startDate = $startDate ?? date('Y-m-d');
+
+        // Если тип whatsapp, проверяем наличие target выборки
+        if ($type === 'whatsapp') {
+            $existingTargetKogort = SentKogort::find()
+                ->select('phone')
+                ->where([
+                    'kogort_date' => $startDate,
+                    'number_kogort' => SentKogort::KOGORT_NUMBERS['target']
+                ])
+                ->column();
+
+            if (!empty($existingTargetKogort)) {
+                return self::processWhatsappKogort($existingTargetKogort, $startDate);
+            }
+        }
+
+        // Если тип call, проверяем наличие whatsapp выборки
+        if ($type === 'call') {
+            $existingWhatsappKogort = SentKogort::find()
+                ->select('phone')
+                ->where([
+                    'kogort_date' => $startDate,
+                    'number_kogort' => SentKogort::KOGORT_NUMBERS['whatsapp']
+                ])
+                ->column();
+
+            if (!empty($existingWhatsappKogort)) {
+                return self::processCallKogort($existingWhatsappKogort, $startDate);
+            }
+        }
+
+        $monthDay = date('m-d', strtotime($startDate));
+
+        $messagesSettings = UsersMessageManagement::find()
+            ->where(['active' => 1])
+            ->one();
+
+        $step1 = $messagesSettings ? $messagesSettings->day_before_step1 : 10;
+
+        $salesPhone = Sales::find()
+            ->where(new \yii\db\Expression("TO_CHAR(date, 'MM-DD') = :monthDay", [':monthDay' => $monthDay]))
+            ->distinct('phone')
+            ->select('phone')
+            ->column();
+
+        $memorableDate = UsersEvents::find()
+            ->where(new \yii\db\Expression("TO_CHAR(date, 'MM-DD') = :monthDay", [':monthDay' => $monthDay]))
+            ->distinct('phone')
+            ->select('phone')
+            ->column();
+
+        $usersArray = array_unique(array_merge($salesPhone, $memorableDate));
+
+
+        $phonesSent = SentKogort::find()
+            ->select('phone')
+            ->where(['between', 'kogort_date',
+                date('Y-m-d', strtotime("$startDate -$step1 days")),
+                date('Y-m-d', strtotime("$startDate -1 days"))])
+            ->column();
+
+        $filteredUsers = array_diff($usersArray, $phonesSent);
+        $kogortData = array_values($filteredUsers);
+
+
+        if ($type === 'target') {
+            return $kogortData;
+        }
+
+
+        return $type === 'whatsapp'
+            ? self::processWhatsappKogort($kogortData, $startDate)
+            : self::processCallKogort($kogortData, $startDate);
+    }
+
+    /**
+     * Обработка выборки для WhatsApp когорты
+     */
+    private static function processWhatsappKogort(array $phones, string $startDate): array
+    {
+        $messagesSettings = UsersMessageManagement::find()
+            ->where(['active' => 1])
+            ->one();
+
+        $step1 = $messagesSettings ? $messagesSettings->day_before_step1 : 10;
+        $step2 = $messagesSettings ? $messagesSettings->day_before_step2 : 4;
+
+        // Подзапрос базы пользователей с учетом подписки на телеграм
+        $usersQuery = (new \yii\db\Query())
+            ->select(['phone'])
+            ->from('users')
+            ->where(['phone' => $phones])
+            ->andWhere(['telegram_is_subscribed' => 0]);
+
+        // Исключаем телефоны с продажами в период от 10 до 4 дней до даты
+        $excludeSalesPhone = Sales::find()
+            ->where(['>=', 'date', date('Y-m-d', strtotime("$startDate -$step1 days"))])
+            ->andWhere(['<=', 'date', date('Y-m-d', strtotime("$startDate -$step2 days"))])
+            ->distinct('phone')
+            ->select('phone')
+            ->column();
+
+        $usersQuery->andWhere(['not in', 'phone', $excludeSalesPhone]);
+
+        return $usersQuery->column();
+    }
+
+    /**
+     * Обработка выборки для Call когорты
+     */
+    private static function processCallKogort(array $phones, string $startDate): array
+    {
+        $messagesSettings = UsersMessageManagement::find()
+            ->where(['active' => 1])
+            ->one();
+
+        $step2 = $messagesSettings ? $messagesSettings->day_before_step2 : 4;
+        $step3 = $messagesSettings ? $messagesSettings->day_before_step3 : 2;
+
+        // Подзапрос базы пользователей с учетом подписки на телеграм
+        $usersQuery = (new \yii\db\Query())
+            ->select(['phone'])
+            ->from('users')
+            ->where(['phone' => $phones])
+            ->andWhere(['telegram_is_subscribed' => 0]);
+
+        // Исключаем телефоны с продажами в период от 4 до 2 дней до даты
+        $excludeSalesPhone = Sales::find()
+            ->where(['>=', 'date', date('Y-m-d', strtotime("$startDate -$step2 days"))])
+            ->andWhere(['<=', 'date', date('Y-m-d', strtotime("$startDate -$step3 days"))])
+            ->distinct('phone')
+            ->select('phone')
+            ->column();
+
+        $usersQuery->andWhere(['not in', 'phone', $excludeSalesPhone]);
+
+        return $usersQuery->column();
+    }
+
+    public static function saveKogort(array $phones, string $startDate, int $kogort_number): array
+    {
+        $data = [];
+        $kogortPhones = SentKogort::find()
+            ->select('phone')
+            ->where(['kogort_date' => $startDate])
+            ->column();
+
+        foreach ($phones as $phone) {
+            if ($phone && !in_array($phone, $kogortPhones)) {
+                $sentKogort = new SentKogort();
+                $sentKogort->phone = $phone;
+                $sentKogort->kogort_date = $startDate;
+                $sentKogort->kogort_unixtime = (int)strtotime($startDate . ' 00:00:00');
+                $sentKogort->status = 1;
+                $sentKogort->kogort_number = $kogort_number;
+                $sentKogort->save(false);
+            }
+
+            $data[] = ['phone' => $phone];
+        }
+        return $data;
+    }
+
+    public static function prepareDataForExport(array $phones, string $startDate, string $type = 'call'): array
+    {
+        if ($type !== 'call') {
+            return array_map(function ($phone) {
+                return ['phone' => $phone];
+            }, $phones);
+        }
+
+        $users = Users::find()
+            ->where(['phone' => $phones])
+            ->all();
+
+        $data = [];
+        foreach ($users as $user) {
+            $memorableDateExists = UsersEvents::find()
+                ->where(['phone' => $user['phone']])
+                ->exists() ? 1 : 0;
+
+            $lastSale = Sales::find()
+                ->where(['phone' => $user['phone']])
+                ->orderBy(['date' => SORT_DESC])
+                ->one();
+
+            $lastDate = $lastSale ? date('d-m-Y', strtotime($lastSale->date)) : 'нет данных';
+
+            $dealsCount = Sales::find()
+                ->where(['phone' => $user['phone']])
+                ->count();
+
+            $totalSum = Sales::find()
+                ->where(['phone' => $user['phone']])
+                ->sum('summ') ?? 0;
+
+            $data[] = [
+                'phone' => $user['phone'],
+                'last_date' => $lastDate,
+                'deals_count' => $dealsCount,
+                'total_sum' => $totalSum,
+                'memorable_date' => $memorableDateExists,
+                'name' => $user['name']
+            ];
+        }
+
+        return $data;
+    }
+
+}
diff --git a/erp24/scripts/tasks/task_35_form_target_kogort.php b/erp24/scripts/tasks/task_35_form_target_kogort.php
new file mode 100644
index 00000000..26ad26b8
--- /dev/null
+++ b/erp24/scripts/tasks/task_35_form_target_kogort.php
@@ -0,0 +1,145 @@
+<?php
+
+/**
+ * @var $time integer|null
+ */
+
+use yii\helpers\Json;
+use yii_app\records\SchedulerTaskLog;
+use yii_app\records\SentKogort;
+use yii_app\records\Users;
+use yii_app\records\UsersMessageManagement;
+
+ini_set('max_execution_time', (string)(60 * 60 * 1)); // 1 час
+ini_set('display_errors', 'on');
+ini_set('display_errors', 1);
+ini_set('display_startup_errors', 1);
+error_reporting(E_ALL);
+
+$time = $time ?? time(); // Использовать текущее время, если параметр не передан
+
+echo "time2_" . $time . "_time2 ";
+$taskNum = 34;
+$dateTask = date('Y-m-d H:i:s', $time);
+$dateTaskStart = null;
+$dateTaskStop = null;
+$log = '';
+$error = '';
+$infoError = '';
+$infoText = '';
+$description = '';
+$result = 0;
+
+$enable = true;
+$start = false;
+$force = false;
+
+$minuetTimeInTask = date('i', $time);
+$fullTimeInTask = date('Y-m-d H:i:s', $time);
+
+try {
+    if (
+        (
+            (date('j', $time) == 1 && date('H:i', $time) == "00:00") || // Старт: 1-й день месяца в 00:00
+            (date('j', $time) == 21 && date('H:i', $time) == "00:00") // Старт: 21-й день месяца в 00:00
+            || $force
+        )
+        && $enable
+    ) {
+        $dateTaskStart = date('Y-m-d H:i:s', $time);
+        $info = ' ================ test Task ' . $taskNum . ' start ================';
+        echo $info;
+        $log .= $info;
+        $log .= $time;
+
+        $schedulerTaskLog = new SchedulerTaskLog();
+        $schedulerTaskLog->setTaskNum($taskNum)
+            ->setName('Task ' . $taskNum)
+            ->setDate($dateTask)
+            ->setDateStart($dateTaskStart);
+        $validate = $schedulerTaskLog->validate();
+        if ($validate) {
+            $schedulerTaskLog->save();
+        }
+
+        // === Логика формирования когорт ===
+        $currentYear = date('Y', $time);
+        $currentMonth = date('m', $time);
+        $daysInMonth = cal_days_in_month(CAL_GREGORIAN, $currentMonth, $currentYear);
+
+        if (date('j', $time) == 1) {
+            $startDay = 1;
+            $endDay = $daysInMonth;
+        }
+
+
+        if (date('j', $time) == 21) {
+            $startDay = 1;
+            $endDay = 10;
+            $currentMonth = ($currentMonth == 12) ? 1 : (int)$currentMonth + 1;
+            $currentYear = ($currentMonth == 1) ? (int)$currentYear + 1 : $currentYear;
+        }
+
+        for ($day = $startDay; $day <= $endDay; $day++) {
+            $currentDate = sprintf('%04d-%02d-%02d', $currentYear, $currentMonth, $day);
+
+
+            if (SentKogort::find()->where(['kogort_date' => $currentDate])->exists()) {
+                $info = "Kogort for date {$currentDate} already exists. Skipping...\n";
+                echo $info;
+                $log .= $info;
+                continue;
+            }
+
+            $kogortPhones = Users::formKogortByDateAndType($currentDate, 'target');
+
+            if (!empty($kogortPhones)) {
+                Users::saveKogort($kogortPhones, $currentDate, SentKogort::KOGORT_NUMBERS['target']);
+                $info = "Kogort for date {$currentDate} saved successfully.\n";
+                echo $info;
+                $log .= $info;
+            } else {
+                $info = "No phones found for kogort on date {$currentDate}.\n";
+                echo $info;
+                $log .= $info;
+            }
+        }
+        // === Конец логики формирования когорт ===
+
+        $info = ' ================ test Task ' . $taskNum . ' stop ================';
+        echo $info;
+        $log .= $info;
+        $log .= ' date >= ' . strtotime("-1 week", $time);
+        $dateTaskStop = date('Y-m-d H:i:s', $time);
+    } else {
+        $info = '   Task ' . $taskNum . ' skip   ';
+        echo $info;
+        $log .= $info;
+    }
+} catch (Exception $e) {
+    $error = 'Exception: ' . $e->getMessage() . ' ' . $e->getFile() . ' >>> ' . $e->getLine();
+}
+
+if (empty($schedulerTaskLog)) {
+    $schedulerTaskLog = new SchedulerTaskLog();
+    $schedulerTaskLog->setTaskNum($taskNum)
+        ->setName('Task ' . $taskNum)
+        ->setDate($dateTask)
+        ->setDateStart($dateTaskStart)
+        ->setDateStop($dateTaskStop)
+        ->setDescription($description)
+        ->setError($error)
+        ->setInfo($infoText)
+        ->setLog($log);
+} else {
+    $schedulerTaskLog->setDateStop($dateTaskStop)
+        ->setDescription($description)
+        ->setError($error)
+        ->setInfo($infoText)
+        ->setLog($log);
+}
+$validate = $schedulerTaskLog->validate();
+if ($validate) {
+    $schedulerTaskLog->save();
+}
+
diff --git a/erp24/views/scheduler_task/test.php b/erp24/views/scheduler_task/test.php
index 2968b9ea..4214dbbc 100644
--- a/erp24/views/scheduler_task/test.php
+++ b/erp24/views/scheduler_task/test.php
@@ -34,4 +34,4 @@ $this->title = 'Тестовый запуск задачи';
 
         <button type="submit" class="btn btn-primary">Запустить задачу</button>
     </form>
-</div>
\ No newline at end of file
+</div>
-- 
2.39.5