From 7b4ec1b8a5f0ef50d8bc080fcf5f7570dfb5913a Mon Sep 17 00:00:00 2001
From: fomichev <vladimir.fomichev@erp-flowers.ru>
Date: Tue, 24 Dec 2024 19:17:04 +0300
Subject: [PATCH] =?utf8?q?=D0=A1=D0=B8=D0=BD=D1=85=D1=80=D0=BE=D0=BD=D0=B8?=
 =?utf8?q?=D0=B7=D0=B0=D1=86=D0=B8=D1=8F=20=D1=81=20=D0=B1=D0=B0=D0=B7?=
 =?utf8?q?=D0=BE=D0=B9=20=D1=82=D0=B0=D0=B1=D0=BB=D0=B8=D1=86=D1=8B=20?=
 =?utf8?q?=D0=BF=D0=BE=D0=BB=D1=8C=D0=B7=D0=BE=D0=B2=D0=B0=D1=82=D0=B5?=
 =?utf8?q?=D0=BB=D1=8F?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 erp24/commands/CronController.php             | 97 +++++++++++++++++++
 ...column_telegram_chat_id_to_users_table.php | 46 +++++++++
 ...145213_create_users_telegram_log_table.php | 46 +++++++++
 erp24/records/Users.php                       | 23 ++---
 erp24/records/UsersTelegramLog.php            | 54 +++++++++++
 5 files changed, 255 insertions(+), 11 deletions(-)
 create mode 100644 erp24/migrations/m241224_140248_add_column_telegram_chat_id_to_users_table.php
 create mode 100644 erp24/migrations/m241224_145213_create_users_telegram_log_table.php
 create mode 100644 erp24/records/UsersTelegramLog.php

diff --git a/erp24/commands/CronController.php b/erp24/commands/CronController.php
index aa66818c..50f141d2 100644
--- a/erp24/commands/CronController.php
+++ b/erp24/commands/CronController.php
@@ -16,6 +16,7 @@ use yii_app\records\ReplacementInvoiceProducts;
 use yii_app\records\SentKogort;
 use yii_app\records\Users;
 use yii_app\records\UsersMessageManagement;
+use yii_app\records\UsersTelegramLog;
 
 class CronController extends Controller
 {
@@ -341,6 +342,7 @@ class CronController extends Controller
                 );
             }
         }
+        return ExitCode::OK;
     }
 
     public function options($actionID)
@@ -352,4 +354,99 @@ class CronController extends Controller
         return $options;
     }
 
+    public function actionSyncTelegramUsers()
+    {
+        $remoteDb = Yii::$app->dbRemote;
+        $batchSize = 500;
+        $offset = 0;
+
+        while (true) {
+            $chatbotUsers = $remoteDb
+                ->createCommand('SELECT * FROM chatbot_telegram_users LIMIT :limit OFFSET :offset', [
+                ':limit' => $batchSize,
+                ':offset' => $offset,
+            ])->queryAll();
+
+            if (empty($chatbotUsers)) {
+                break;
+            }
+
+            foreach ($chatbotUsers as $remoteUser) {
+                $phone = $remoteUser['phone'];
+                $chatId = $remoteUser['chat_id'];
+                $username = $remoteUser['username'];
+                $isBlocked = (int)$remoteUser['is_blocked'];
+                $isRegistered = (int)$remoteUser['is_registered'];
+
+                $user = Users::findOne(['phone' => $phone]);
+
+                if (!$user) {
+                    $user = new Users([
+                        'phone' => $phone,
+                        'name' => $username ?: 'Клиент из чатбота',
+                        'date' => date('Y-m-d H:i:s'),
+                        'password' => '00000',
+                        'keycode' => '00000',
+                    ]);
+                    $user->save(false);
+                }
+
+                if (empty($user->telegram_chat_id)) {
+                    $user->telegram_chat_id = $chatId;
+                }
+
+                if ($isRegistered && $user->telegram_is_subscribed == 0) {
+                    $user->telegram_is_subscribed = 1;
+                } elseif (!$isRegistered && $user->telegram_is_subscribed == 1) {
+                    $user->telegram_is_subscribed = 0;
+                    $user->telegram_unsubscribed_at = date('Y-m-d H:i:s');
+                }
+
+                $user->save(false);
+
+                $existingLog = UsersTelegramLog::find()
+                    ->where(['phone' => $phone, 'date_end' => null])
+                    ->one();
+
+                if ($existingLog) {
+                    if (
+                        $existingLog->is_blocked != $isBlocked ||
+                        $existingLog->is_registered != $isRegistered
+                    ) {
+                        $existingLog->date_end = date('Y-m-d H:i:s');
+                        $existingLog->save(false);
+
+                        $this->createTelegramLog($phone, $isBlocked, $isRegistered);
+                    }
+                } else {
+                    $this->createTelegramLog($phone, $isBlocked, $isRegistered);
+                }
+            }
+
+            $offset += $batchSize;
+
+            $this->stdout("Обработано записей: {$offset}\n", Console::FG_GREEN);
+        }
+
+        $this->stdout("Синхронизация завершена успешно.\n", Console::FG_GREEN);
+        return ExitCode::OK;
+    }
+
+    /**
+     * Создает новую запись в таблице users_telegram_log.
+     *
+     * @param string $phone
+     * @param int $isBlocked
+     * @param int $isRegistered
+     */
+    protected function createTelegramLog($phone, $isBlocked, $isRegistered)
+    {
+        $log = new UsersTelegramLog([
+            'phone' => $phone,
+            'is_blocked' => $isBlocked,
+            'is_registered' => $isRegistered,
+            'active' => ($isBlocked == 0 && $isRegistered == 1) ? 1 : 0,
+        ]);
+        $log->save(false);
+    }
 }
diff --git a/erp24/migrations/m241224_140248_add_column_telegram_chat_id_to_users_table.php b/erp24/migrations/m241224_140248_add_column_telegram_chat_id_to_users_table.php
new file mode 100644
index 00000000..322e5e6a
--- /dev/null
+++ b/erp24/migrations/m241224_140248_add_column_telegram_chat_id_to_users_table.php
@@ -0,0 +1,46 @@
+<?php
+
+use yii\db\Migration;
+
+/**
+ * Class m241224_140248_add_telegram_chat_id_to_users_table
+ */
+class m241224_140248_add_column_telegram_chat_id_to_users_table extends Migration
+{
+    const TABLE_NAME = 'erp24.users';
+    const COLUMN_NAME = 'telegram_chat_id';
+    /**
+     * {@inheritdoc}
+     */
+    public function safeUp()
+    {
+        if (!$this->db->getTableSchema(self::TABLE_NAME, true)->getColumn(self::COLUMN_NAME)) {
+            $this->addColumn(self::TABLE_NAME, self::COLUMN_NAME, $this->string()->comment('Telegram Chat ID'));
+        }
+    }
+
+    /**
+     * {@inheritdoc}
+     */
+    public function safeDown()
+    {
+        if ($this->db->getTableSchema(self::TABLE_NAME, true)->getColumn(self::COLUMN_NAME)) {
+            $this->dropColumn(self::TABLE_NAME, self::COLUMN_NAME);
+        }
+    }
+
+    /*
+    // Use up()/down() to run migration code without a transaction.
+    public function up()
+    {
+
+    }
+
+    public function down()
+    {
+        echo "m241224_140248_add_telegram_chat_id_to_users_table cannot be reverted.\n";
+
+        return false;
+    }
+    */
+}
diff --git a/erp24/migrations/m241224_145213_create_users_telegram_log_table.php b/erp24/migrations/m241224_145213_create_users_telegram_log_table.php
new file mode 100644
index 00000000..3666327e
--- /dev/null
+++ b/erp24/migrations/m241224_145213_create_users_telegram_log_table.php
@@ -0,0 +1,46 @@
+<?php
+
+use yii\db\Migration;
+
+/**
+ * Handles the creation of table `{{%users_telegram_log}}`.
+ */
+class m241224_145213_create_users_telegram_log_table extends Migration
+{
+    const TABLE_NAME  = 'erp24.users_telegram_log';
+    /**
+     * {@inheritdoc}
+     */
+    public function safeUp()
+    {
+        $tableSchema = $this->db->getTableSchema(self::TABLE_NAME);
+
+        if (!isset($tableSchema)) {
+            $this->createTable(self::TABLE_NAME, [
+                'phone' => $this->string()->notNull()->unique()->comment('Телефон'),
+                'is_blocked' => $this->integer(1)->notNull()->defaultValue(0)->comment(
+                    'Заблокирован: 0 - нет, 1 - да'
+                ),
+                'is_registered' => $this->integer(1)->notNull()->defaultValue(0)->comment(
+                    'Зарегистрирован: 0 - нет, 1 - да'
+                ),
+                'active' => $this->integer(1)->notNull()->defaultValue(0)->comment(
+                    'Активный: 0 - нет, 1 - да'
+                ),
+                'date_end' => $this->date()->null()->comment('Дата окончания активности'),
+
+            ]);
+        }
+    }
+
+    /**
+     * {@inheritdoc}
+     */
+    public function safeDown()
+    {
+        $tableSchema = $this->db->getTableSchema(self::TABLE_NAME);
+        if (isset($tableSchema)) {
+            $this->dropTable(self::TABLE_NAME);
+        }
+    }
+}
diff --git a/erp24/records/Users.php b/erp24/records/Users.php
index ebe374ab..7661aeb9 100755
--- a/erp24/records/Users.php
+++ b/erp24/records/Users.php
@@ -66,6 +66,7 @@ use yii\base\StaticInstanceInterface;
  * @property int $events Импортированы ли события
  * @property string|null $first_minus_balance
  * @property string $info
+ * @property string $telegram_chat_id
  */
 class Users extends \yii\db\ActiveRecord
 {
@@ -180,11 +181,12 @@ class Users extends \yii\db\ActiveRecord
                     'seller_id',
                     'store_id',
                     'first_minus_balance',
-                    'telegram_unsubscribed_at'
+                    'telegram_unsubscribed_at',
+                    'telegram_chat_id'
                 ],
                 'safe'
             ],
-            [['pol', 'info', 'telegram_created_at'], 'string'],
+            [['pol', 'info', 'telegram_created_at', 'telegram_chat_id'], 'string'],
             [
                 [
                     'forgot',
@@ -213,7 +215,7 @@ class Users extends \yii\db\ActiveRecord
             [['name_name', 'name_last', 'kod'], 'string', 'max' => 20],
             [['name_family'], 'string', 'max' => 25],
             [['comment', 'sale_store'], 'string', 'max' => 200],
-            [['email'], 'string', 'max' => 70],
+            [['email', 'telegram_chat_id'], 'string', 'max' => 70],
             [['phone_old'], 'string', 'max' => 250],
             [['card', 'check_id'], 'string', 'max' => 35],
             [['password', 'keycode', 'seller_id'], 'string', 'max' => 36],
@@ -284,6 +286,7 @@ class Users extends \yii\db\ActiveRecord
             'black_list' => 'Black List',
             'events' => 'Events',
             'telegram_unsubscribed_at' => 'Telegram Unsubscribed At',
+            'telegram_chat_id' => 'Telegram ChatID'
         ];
     }
 
@@ -326,7 +329,9 @@ class Users extends \yii\db\ActiveRecord
         //TODO: Проверка что он уже участвовал в когорте раннее
         $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"))])
+            ->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);
@@ -339,9 +344,9 @@ class Users extends \yii\db\ActiveRecord
                 ->where(['kogort_date' => $startDate])
                 ->column();
             foreach ($kogortData as $phone) {
-                if($phone) {
-                    if (!in_array($phone, $kogortPhones) ) {
-                        $sentKogort = new SentKogort;
+                if ($phone) {
+                    if (!in_array($phone, $kogortPhones)) {
+                        $sentKogort = new SentKogort();
                         $sentKogort->phone = $phone;
                         $sentKogort->kogort_date = $startDate;
                         $sentKogort->kogort_unixtime = (int)strtotime($startDate . ' 00:00:00');
@@ -444,7 +449,6 @@ class Users extends \yii\db\ActiveRecord
             }
         }
 
-        // Если тип call, проверяем наличие whatsapp выборки
         if ($type === 'call') {
             $existingWhatsappKogort = SentKogort::find()
                 ->select('phone')
@@ -481,7 +485,6 @@ class Users extends \yii\db\ActiveRecord
 
         $usersArray = array_unique(array_merge($salesPhone, $memorableDate));
 
-
         $phonesSent = SentKogort::find()
             ->select('phone')
             ->where(['between', 'kogort_date',
@@ -492,12 +495,10 @@ class Users extends \yii\db\ActiveRecord
         $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);
diff --git a/erp24/records/UsersTelegramLog.php b/erp24/records/UsersTelegramLog.php
new file mode 100644
index 00000000..5745a157
--- /dev/null
+++ b/erp24/records/UsersTelegramLog.php
@@ -0,0 +1,54 @@
+<?php
+
+namespace yii_app\records;
+
+use Yii;
+
+/**
+ * This is the model class for table "users_telegram_log".
+ *
+ * @property string $phone Телефон
+ * @property int $is_blocked Заблокирован: 0 - нет, 1 - да
+ * @property int $is_registered Зарегистрирован: 0 - нет, 1 - да
+ * @property int $active Активный: 0 - нет, 1 - да
+ * @property string|null $date_end Дата окончания активности
+ */
+class UsersTelegramLog extends \yii\db\ActiveRecord
+{
+    /**
+     * {@inheritdoc}
+     */
+    public static function tableName()
+    {
+        return 'users_telegram_log';
+    }
+
+    /**
+     * {@inheritdoc}
+     */
+    public function rules()
+    {
+        return [
+            [['phone'], 'required'],
+            [['is_blocked', 'is_registered', 'active'], 'default', 'value' => null],
+            [['is_blocked', 'is_registered', 'active'], 'integer'],
+            [['date_end'], 'safe'],
+            [['phone'], 'string', 'max' => 255],
+            [['phone'], 'unique'],
+        ];
+    }
+
+    /**
+     * {@inheritdoc}
+     */
+    public function attributeLabels()
+    {
+        return [
+            'phone' => 'Телефон',
+            'is_blocked' => 'Заблокирован: 0 - нет, 1 - да',
+            'is_registered' => 'Зарегистрирован: 0 - нет, 1 - да',
+            'active' => 'Активный: 0 - нет, 1 - да',
+            'date_end' => 'Дата окончания активности',
+        ];
+    }
+}
-- 
2.39.5