mall/jobs/migration/ExportJob.php

<?php

namespace app\jobs\migration;

use yii\base\BaseObject;
use yii\queue\JobInterface;
use app\models\Store;
use app\models\Migration;

class ExportJob extends BaseObject implements JobInterface
{
    public $id;
    public $store_id;
    public function execute($queue)
    {
        $store = Store::findOne($this->store_id);
        if (!$store) {
            return;
        }
        $migration = Migration::findOne($this->id);
        if (!$migration) {
            return;
        }
        $migration->status = 1;
        $migration->save();
        $this->backSql($store->id);
        $this->backUpload($store->id);
        $path = $this->toZip($store->id);
        if (!$path) {
            $migration->status = 3;
            $migration->save();
            return;
        }
        $migration->status = 2;
        $migration->down_url = $path;
        $migration->save();
    }

    public function backSql($store_id)
    {
        $defaultStoreId = DEFAULT_STORE_ID;
        $tables = \Yii::$app->db->schema->getTableNames();
        // 黑名单，遇到跳过
        $blacklist = [
            'cyy_action_log',
            'cyy_browse_log',
            'cyy_cloud',
            'cyy_color',
            'cyy_common_operation',
            'cyy_country',
            'cyy_district',
            'cyy_express',
            'cyy_notice_err',
            'cyy_old_user_tree_path',
            'cyy_plugins',
            'cyy_purchase',
            'cyy_salesman',
            'cyy_salesman_new_store',
            'cyy_store',
            'cyy_supplier',
            'cyy_supplier_withdraw',
            'cyy_ui_pay_merchant_info',
            'cyy_zone',
            'cyy_user_tree_path',
            'cyy_business_right_info',
        ];

        $blackMatchList = [
            'cyy_agent_*',
            'cyy_aggregate_*',
            'cyy_ali_*',
            'cyy_alipay_*',
            'cyy_business_*',
            'cyy_cloud_*',
            'cyy_front_*',
            'cyy_purchase_*',
            'cyy_saas_*',
            'cyy_store_*',
        ];

        // 白名单
        $whitelist = [
            'cyy_saas_user',
        ];
        $path = \Yii::$app->basePath . '/runtime/migration/store_' . $store_id . '/export/sql/';
        // 先删除文件夹
        if (is_dir($path)) {
            $files = scandir($path);
            foreach ($files as $file) {
                if ($file != '.' && $file != '..') {
                    unlink($path. $file);
                }
            }
            rmdir($path);
        }
        if (!is_dir($path)) {
            mkdir($path, 0777, true);
        }

        foreach ($tables as $table) {
            // 检查是否在白名单中
            if (!in_array($table, $whitelist)) {
                // 不在白名单中时，检查黑名单
                if (in_array($table, $blacklist)) {
                    continue;
                }
                foreach ($blackMatchList as $blackMatch) {
                    if (fnmatch($blackMatch, $table)) {
                        continue 2;
                    }
                }
            }

            $hasStoreId = \has_column($table, 'store_id');
            
            // 检查表是否有数据
            if ($hasStoreId) {
                $count = \Yii::$app->db->createCommand("SELECT COUNT(*) FROM $table WHERE store_id = :store_id")
                    ->bindValue(':store_id', $store_id)
                    ->queryScalar();
            } else {
                $count = \Yii::$app->db->createCommand("SELECT COUNT(*) FROM $table")->queryScalar();
            }

            if ($count == 0) {
                continue;
            }

            // 使用游标分批获取数据，减少内存占用
            $batchSize = 1000;
            $offset = 0;
            $fp = fopen($path . $table . '.sql', 'a');

            while (true) {
                if ($hasStoreId) {
                    $query = \Yii::$app->db->createCommand("SELECT * FROM $table WHERE store_id = :store_id LIMIT :limit OFFSET :offset")
                        ->bindValue(':store_id', $store_id)
                        ->bindValue(':limit', $batchSize)
                        ->bindValue(':offset', $offset);
                } else {
                    $query = \Yii::$app->db->createCommand("SELECT * FROM $table LIMIT :limit OFFSET :offset")
                        ->bindValue(':limit', $batchSize)
                        ->bindValue(':offset', $offset);
                }
                
                $rows = $query->queryAll();
                
                if (empty($rows)) {
                    break;
                }

                if ($offset === 0) {
                    // 只在第一批写入INSERT语句头部
                    $columns = array_keys($rows[0]);
                    $headerSql = "INSERT INTO $table (`" . implode('`,`', $columns) . "`) VALUES ";
                    fwrite($fp, $headerSql);
                }

                // 逐行处理数据并写入文件
                foreach ($rows as $i => $row) {
                    if ($hasStoreId) {
                        $row['store_id'] = $defaultStoreId; // 替换store_id字段的值为默认store_id
                    }
                    
                    $values = array_map(function($val) {
                        if ($val === null) {
                            return 'NULL';
                        }
                        return is_numeric($val) ? $val : "'" . addslashes($val) . "'";
                    }, array_values($row));
                    
                    fwrite($fp, ($offset === 0 && $i === 0 ? '' : ',') . "\n(" . implode(',', $values) . ")");
                }

                $offset += $batchSize;
                
                // 释放内存
                unset($rows);
                gc_collect_cycles();
            }

            // 完成后写入分号
            fwrite($fp, ";\n");
            fclose($fp);
        }
    }

    public function backUpload($store_id)
    {
        $from_image_path = \Yii::$app->basePath . '/web/uploads/images/store_' . $store_id . '/';
        $from_video_path = \Yii::$app->basePath . '/web/uploads/videos/store_' . $store_id . '/';
        $to_path = \Yii::$app->basePath . '/runtime/migration/store_' . $store_id . '/export/uploads/';

        // 判断图片路径是否存在，如果存在就整个复制到目标路径，注意，存在二级目录
        if (is_dir($from_image_path)) {
            $to_image_path = $to_path . 'images/';
            if (!is_dir($to_image_path)) {
                mkdir($to_image_path, 0777, true);
            }
            $this->copyDirectory($from_image_path, $to_image_path);
        }
        // 判断视频路径是否存在，如果存在就整个复制到目标路径，注意，存在二级目录
        if (is_dir($from_video_path)) {
            $to_video_path = $to_path . 'videos/';
            if (!is_dir($to_video_path)) {
                mkdir($to_video_path, 0777, true);
            }
            $this->copyDirectory($from_video_path, $to_video_path);
        }
    }

    public function copyDirectory($source, $destination)
    {
        if (!is_dir($source)) {
            return;
        }
        if (!is_dir($destination)) {
            mkdir($destination, 0777, true);
        }
        $files = scandir($source);
        foreach ($files as $file) {
            if ($file != '.' && $file != '..') {
                if (is_dir($source . '/' . $file)) {
                    $this->copyDirectory($source . '/' . $file, $destination . '/' . $file);
                } else {
                    copy($source . '/' . $file, $destination . '/' . $file);
                }
            }
        }
    }
    
    // 压缩文件
    public function zipFiles($path, $zipFileName)
    {
        $zip = new \ZipArchive();
        if ($zip->open($zipFileName, \ZipArchive::CREATE) !== true) {
            return false;
        }

        $files = new \RecursiveIteratorIterator(
            new \RecursiveDirectoryIterator($path),
            \RecursiveIteratorIterator::LEAVES_ONLY
        );

        $pathLength = strlen(rtrim(realpath($path), '/\\') . DIRECTORY_SEPARATOR);
        
        foreach ($files as $file) {
            if (!$file->isDir()) {
                $filePath = $file->getRealPath();
                // Get relative path to maintain directory structure
                $relativePath = substr($filePath, $pathLength);
                $zip->addFile($filePath, $relativePath);
            }
        }
        
        $zip->close();
        return true;
    }

    // 压缩
    public function toZip($store_id)
    {
        try {
            $path = \Yii::$app->basePath . '/runtime/migration/store_' . $store_id . '/export/';
            $fileName = 'export_store_'.$store_id.'_'.date('YmdHis').'.zip';
            $zipFileName = \Yii::$app->basePath . '/runtime/migration/store_' . $store_id . '/' . $fileName;
            if (file_exists($zipFileName)) {
                unlink($zipFileName);
            }
            $this->zipFiles($path, $zipFileName);
            // 删除文件夹
            $delPath = \Yii::$app->basePath . '/runtime/migration/store_' . $store_id . '/export/';
            $this->deleteDirectory($delPath);
            return '/runtime/migration/store_' . $store_id . '/' . $fileName;
        } catch (\Exception $e) {
            debug_log('ExportJob toZip error: ' . $e->getMessage(), 'syan.log');
            return false;
        }
    }

    // 删除文件夹
    public function deleteDirectory($dir)
    {
        if (!is_dir($dir)) {
            return;
        }
        $files = scandir($dir);
        foreach ($files as $file) {
            if ($file != '.' && $file != '..') {
                if (is_dir($dir . '/' . $file)) {
                    $this->deleteDirectory($dir . '/' . $file);
                } else {
                    unlink($dir . '/' . $file);
                }
            }
        }
        rmdir($dir);
    }
}