noride/noridecrm/service/backend/models/Mail.php

<?php

namespace backend\models;

use backend\helpers\DateTimeHelper;
use backend\helpers\ValidatorHelper;
use Yii;
use yii\helpers\Html;

/**
 * This is the model class for table "crm_mail".
 *
 * @property string $id
 * @property integer $type
 * @property string $subject
 * @property string $content
 * @property integer $state
 * @property string $in_time
 * @property string $other
 */
class Mail extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'crm_mail';
    }

    /**
     * @return \yii\db\Connection the database connection used by this AR class.
     */
    public static function getDb()
    {
        return Yii::$app->get('dbXcrm');
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['type', 'subject', 'content', 'state', 'in_time'], 'required'],
            [['type', 'state', 'in_time'], 'integer'],
            [['content', 'other'], 'string'],
            [['subject'], 'string', 'max' => 255],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'type' => 'Type',
            'subject' => 'Subject',
            'content' => 'Content',
            'state' => 'State',
            'in_time' => 'In Time',
            'other' => 'Other',
        ];
    }
    
    public function sendEvent($event)
    {
        $mail = Mail::findOne(['id' => $event->mailId]);
        $columns = ['subject', 'content', 'receiver', 'status', 'in_time'];
        $members = [];
        // 防止去数据库查询的时候内存报错
        ini_set ('memory_limit', '1024M');
        if ((int)$event->type == 0) {
            $members = Member::find()->select('username')->where(['type' => 1])
                ->asArray()->all();
        } elseif ((int)$event->type == 1) {
            $members = Member::find()->select('username')->where(['type' => 2])
                ->asArray()->all();
        } elseif ((int)$event->type == 2) {
            $members = Member::find()->select('username')->where(['type' => [1, 2]])
                ->asArray()->all();
        } elseif ((int)$event->type == 3) {
            if (!empty($mail->other)) {
                $username = explode(',', $mail->other);
                foreach ($username as $member) {
                    $members[] = ['username' => $member];
                }
            }
        }

        // 解决mysql server has gone away 问题
        if (count($members) > 5000) {
            $this->bigDataInsert($members, $mail);
        } else {
            $dateHelper = new DateTimeHelper();
            $rows = [];
            foreach ($members as $member) {
                if (!ValidatorHelper::isEmail($member['username'])) {
                    continue;
                }
                $rows[] = [
                    $mail->subject,
                    $mail->content,
                    $member['username'],
                    0,
                    $dateHelper->microtime_float(),
                ];
            }
            $connection = MailRecord::getDb()->createCommand();
            $connection->batchInsert(MailRecord::tableName(), $columns, $rows)->execute();
        }
    }

    /**
     * 分批插入数据
     */
    public function bigDataInsert($members, $mail)
    {
        $affectedNum = 0;
        $dateHelper = new DateTimeHelper();
        $chu = (int)(count($members) / 5000);//取整
        $yu = count($members) % 5000;// 取余
        $time = $dateHelper->microtime_float();
        $transaction = $this->getDb()->beginTransaction();
        try {
            // 防止插入的时候内存报错
            ini_set ('memory_limit', '1024M');
            // 设置响应时间
            set_time_limit(0);
            for ($i = 0; $i < $chu; $i++) {
                $rows = [];
                for ($j = $i * 5000; $j < ($i + 1) * 5000; $j++) {
                    if (!ValidatorHelper::isEmail($members[$j]['username'])) {
                        continue;
                    }
                    $rows[] = [
                        'subject' => $mail->subject,
                        'content' => $mail->content,
                        'receiver' => $members[$j]['username'],
                        'status' => 0,
                        'in_time' => $time,
                    ];
                }
                if ($rows) {
                    $command = $this->getDb()->createCommand();
                    $columns = array_combine(array_keys($rows[0]), array_keys($rows[0]));
                    $sql = $command->batchInsert(MailRecord::tableName(), $columns, $rows)->getSql();
                    $affectedNum += $command->setSql(str_ireplace("INSERT INTO", "INSERT IGNORE INTO", $sql))->execute();
                }
            }

            if ($yu > 0) {
                $rows_yu = [];
                for ($m = 0; $m < $yu; $m++) {
                    if (!ValidatorHelper::isEmail($members[$affectedNum+$m]['username'])) {
                        continue;
                    }
                    $rows_yu[] = [
                        'subject' => $mail->subject,
                        'content' => $mail->content,
                        'receiver' => $members[$affectedNum+$m]['username'],
                        'status' => 0,
                        'in_time' => $time,
                    ];
                }
                if ($rows_yu) {
                    $command = $this->getDb()->createCommand();
                    $columns = array_combine(array_keys($rows_yu[0]), array_keys($rows_yu[0]));
                    $sql = $command->batchInsert(MailRecord::tableName(), $columns, $rows_yu)->getSql();
                    $command->setSql(str_ireplace("INSERT INTO", "INSERT IGNORE INTO", $sql))->execute();
                }
            }
            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
        }
    }
}