drug-report/app/Console/Commands/SyncDatabaseCommand.php

<?php


namespace App\Console\Commands;


use Illuminate\Console\Command;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use App\Facades\Servers\Logs\Log;

class SyncDatabaseCommand extends Command
{
    /**
     * 任务名称
     *
     * @var string
     */
    protected $signature = 'sync:database';

    /**
     * 任务描述
     *
     * @var string
     */
    protected $description = '开邻的数据复制到Saas';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    public function handle()
    {
        $dataBaseInfos = [
            //kailin
            'remote' => [
                'host'          => '39.108.116.125',
                'port'          => 3306,
                'db_name'       => 'kailin_migrate_test',
                'db_username'   => 'kailin_migrate_test',
                'db_password'   => '4bCtNLjfsLJ5Fhbw'
            ],

            //saas-system
            'system' => [
                'host'          => '120.24.49.2',
                'port'          => 3306,
                'db_name'       => 'system',
                'db_username'   => 'system',
                'db_password'   => 'tCDnjKmRRPsHikZw'
            ],

            //saas-company
            'company' => [
                'host'          => '120.24.49.2',
                'port'          => 3306,
                'db_name'       => 'company',
                'db_username'   => 'company',
                'db_password'   => 'ifAJ7XaDGBZBpLdc'
            ]

            //测试用 start
//            'system' => [
//                //'host'          => '127.0.0.1',
//                'host'          => '39.108.116.125',
//                'port'          => 3306,
//                'db_name'       => 'system_migrate_test',
//                'db_username'   => 'system_migrate_test',
//                'db_password'   => 'wwaxK4AxTkZe3tBa'
//            ],
//
//            'company' => [
//                'host'          => '39.108.116.125',
//                'port'          => 3306,
//                'db_name'       => 'company_migrate_0620',
//                'db_username'   => 'company_migrate_0620',
//                'db_password'   => 'JhbycESDw7HDMfh2'
//            ]

            //测试用 end




        ];

        $remoteConnectionConfig = [
            'driver'        => 'mysql',
            'host'          => $dataBaseInfos['remote']['host'],
            'port'          => $dataBaseInfos['remote']['port'],
            'database'      => $dataBaseInfos['remote']['db_name'],
            'username'      => $dataBaseInfos['remote']['db_username'],
            'password'      => $dataBaseInfos['remote']['db_password'],
            'charset'       => 'utf8mb4',
            'collation'     => 'utf8mb4_unicode_ci',
            'unix_socket'   => env('DB_SOCKET', ''),
            'prefix'        => env('DB_PREFIX', ''),
            'strict' => false,
            'sticky' => true,
            'engine' => null,
            'options' => [],
        ];

        Config::set("database.connections.remote", $remoteConnectionConfig);

        $systemConnectionConfig = [
            'driver'        => 'mysql',
            'host'          => $dataBaseInfos['system']['host'],
            'port'          => $dataBaseInfos['system']['port'],
            'database'      => $dataBaseInfos['system']['db_name'],
            'username'      => $dataBaseInfos['system']['db_username'],
            'password'      => $dataBaseInfos['system']['db_password'],
            'charset'       => 'utf8mb4',
            'collation'     => 'utf8mb4_unicode_ci',
            'unix_socket'   => env('DB_SOCKET', ''),
            'prefix'        => env('DB_PREFIX', ''),
            'strict' => false,
            'sticky' => true,
            'engine' => null,
            'options' => [],
        ];

        Config::set("database.connections.system", $systemConnectionConfig);

        $companyConnectionConfig = [
            'driver'        => 'mysql',
            'host'          => $dataBaseInfos['company']['host'],
            'port'          => $dataBaseInfos['company']['port'],
            'database'      => $dataBaseInfos['company']['db_name'],
            'username'      => $dataBaseInfos['company']['db_username'],
            'password'      => $dataBaseInfos['company']['db_password'],
            'charset'       => 'utf8mb4',
            'collation'     => 'utf8mb4_unicode_ci',
            'unix_socket'   => env('DB_SOCKET', ''),
            'prefix'        => env('DB_PREFIX', ''),
            'strict' => false,
            'sticky' => true,
            'engine' => null,
            'options' => [],
        ];

        Config::set("database.connections.company", $companyConnectionConfig);

        // 获取远程数据库的表名
        $tables = DB::connection('remote')->select('SHOW TABLES');

        $systemTables = ['kailin_business']; // 远端是kailin_business 在系统库中是kailin_shop
        $notExecuteTables = [
            //'kailin_article', 'kailin_article_event',
            //'kailin_lottery_recruitment', 'kailin_lottery_recruitment_record',
            //'kailin_lottery_recruitment_reward', 'kailin_redpacket_active', 'kailin_redpacket_active_record',
            //'kailin_redpacket_active_reward',
            'kailin_short_link', 'kailin_video_answer',
            'kailin_video_course', 'kailin_video_course_service', 'kailin_video_course_type',
            'kailin_video_exam_answer', 'kailin_video_exam_question', 'kailin_video_exam_record',
            'kailin_video_learn_answer', 'kailin_video_learn_question', 'kailin_video_learn_record',
            'kailin_video_question', 'kailin_video_vip', 'kailin_weiban_external',
            'kailin_work_external', 'kailin_weiban_follow', 'kailin_weiban_qrcode',
            'kailin_weiban_sync', 'kailin_weiban_taglist', 'kailin_weiban_tags',
            'kailin_work_state', 'kailin_work_sync', 'kailin_work_tag',
            'kailin_work_user',
            'kailin_admin', 'kailin_admin_history', 'kailin_admin_rule',
            'kailin_auth_group', 'kailin_auth_group_access', 'kailin_auth_rule',
            'kailin_city', 'kailin_company', 'kailin_menu',
            'kailin_score_orders_product', 'kailin_pay_city'
        ];

        $dataCount = count($tables);

        $this->info("总共有数据表：{$dataCount} 个");

        $this->info('开始处理...');

        $bar = $this->output->createProgressBar($dataCount);

        foreach ($tables as $table) {
            $tableName = (array) $table;

            $tableName = array_values($tableName)[0];
            //测试代码start
//            if ($tableName == 'kailin_business') {
//                // 获取远程表的数据
//                $data = DB::connection('remote')->table($tableName)->get();
//
//                foreach ($data as $row) {
//                    $insertData = (array)$row;
//                    $insertData['id'] = $row['id'];
//
//                    DB::connection('system')->table('kailin_shop')->updateOrInsert(
//                        ['id' => $row['id']], // 检查是否存在的条件
//                        $insertData         // 需要插入或更新的数据
//                    );
//                    Log::info('sync_database', "Synced data from remote table {$tableName} to local system database: " . json_encode($row));
//                }
//            } else {
//                $bar->advance();
//            }
            //测试代码end

            if (!in_array($tableName, $notExecuteTables)) {
                // 获取远程表的数据
                $data = DB::connection('remote')->table($tableName)->get();
                $this->info("\n");
                $this->info("当前处理的数据表：{$tableName} ");
                // 删除本地数据库中的数据
                if (in_array($tableName, $systemTables)) {
                    if ($tableName == 'kailin_business') {
                        //kailin_shop表的数据不删除
                        //$this->truncateTable('kailin_shop', 'system');
                    } else {
                        $this->truncateTable($tableName, 'system');
                    }
                } else {
                    $this->truncateTable($tableName, 'company');
                }

                // 同步到本地数据库
                $tableCountData = count($data);
                $this->info("当前数据表的数据条数：{$tableCountData} ");
                $bar1 = $this->output->createProgressBar($tableCountData);
                foreach ($data as $row) {
                    try {
                        if (in_array($tableName, $systemTables)) {
                            if ($tableName == 'kailin_business') {
                                //DB::connection('system')->table('kailin_shop')->insert((array)$row);
                                $insertData = (array)$row;
                                $insertData['id'] = $row['id'];
                                DB::connection('system')->table('kailin_shop')->updateOrInsert(
                                    ['id' => $row['id']], // 检查是否存在的条件
                                    $insertData         // 需要插入或更新的数据
                                );
                            } else {
                                DB::connection('system')->table($tableName)->insert((array)$row);
                            }
                            Log::info('sync_database', "Synced data from remote table {$tableName} to local system database: " . json_encode($row));
                        } else {
                            DB::connection('company')->table($tableName)->insert((array) $row);
                            Log::info('sync_database',"Synced data from remote table {$tableName} to local company database: " . json_encode($row));
                        }

                    } catch (\Exception $e) {
                        Log::error('sync_database_error',"Failed to sync data from remote table {$tableName}: " . $e->getMessage());
                    }
                    $bar1->advance();
                }
                $bar1->finish();
                $bar->advance();
            } else {
                $bar->advance();
            }
        }

        $bar->finish();
        $this->info('');
        $this->info('处理完成');

    }


    protected function truncateTable($tableName, $connection)
    {
        try {
            DB::connection($connection)->table($tableName)->truncate();
            Log::info('sync_database',"Truncated table {$tableName} in {$connection} database.");
        } catch (\Exception $e) {
            Log::error('sync_database',"Failed to truncate table {$tableName} in {$connection} database: " . $e->getMessage());
        }
    }


}