book_admin/application/admin/command/UpdateVipTime.php

<?php
/**
 * Created by PhpStorm.
 * User: wanggb
 * Date: 2019/1/2
 * Time: 14:13
 */

namespace app\admin\command;

use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\console\input\Argument;
use think\Config;
use think\Db;
use think\Log;
use think\Request;

class UpdateVipTime extends Command
{
    const VIP_RECHARGE = '2';
    const SYS_VIP_RECHARGE = '4';
    const PAGE_NUM = 2000; // 每次执行N条数据
    const DB_NUM = 512; //数据库个数
    protected function configure()
    {
        $this->setName('UpdateVipTime')
            ->addArgument('startDbNum', Argument::REQUIRED, "第几个数据库开始")
            ->addArgument('endDbNum', Argument::REQUIRED, "第几个数据库结束")
            ->setDescription('更新所有user库，Recharge表中的字段vip_starttime,仅需要在项目上线时执行一次，此后不需要再执行,命令格式  php think UpdateVipTime startDbNum endDbNum');
    }

    protected function execute(Input $input, Output $output)
    {
        Request::instance()->module('admin');
        $startDbNum = $input->getArgument('startDbNum');
        $endDbNum = $input->getArgument('endDbNum');
        $output->writeln('开始数据库'.$startDbNum);
        $output->writeln('结束数据库'.$endDbNum);
        if($startDbNum >511 || $endDbNum > 511){
            $output->writeln('参数错误，数据库开始序号和结束序号不能大于511');
            die();
        }

        Log::info("开始刷vip_starttime，开始时间:" . date("Y-m-d H:i:s", time()));
        for ($idx = $startDbNum; $idx <= $endDbNum; $idx++) {
            $output->writeln($idx);
            Log::info("***************************************************************************");
            $db = $this->dbConnect($idx);
            Log::info("数据库" . $db->getConfig('database') . "中的信息，开始执行");
            //$total 获取数据总个数
            $total = $this->getTotalNum($output, $db);
            Log::info("获取数据总个数:" . $total . "条数据");
            try {
                if ($total > 0) {
                    $pagenum = self::PAGE_NUM;
                    for ($i = 0; $i < $total / $pagenum; $i++) {
                        $startNum = $i * $pagenum;
                        $sql = "SELECT * FROM recharge WHERE type IN('" . self::VIP_RECHARGE . "' , '" . self::SYS_VIP_RECHARGE . "') ORDER BY id LIMIT " . $startNum . ", " . $pagenum;
                        $result = $db->query($sql);
                        if ($result) {
                            foreach ($result as $key => $value) {
                                $lastDataSql = "SELECT * FROM recharge WHERE user_id = " . $value['user_id'] . "  AND type IN('" . self::VIP_RECHARGE . "' , '" . self::SYS_VIP_RECHARGE . "') AND id <" . $value['id'] . " ORDER BY id DESC LIMIT 1";
                                $lastDataResult = $db->query($lastDataSql);

                                if(!$lastDataResult){
                                    $update_sql = "UPDATE recharge SET vip_starttime = createtime WHERE id = " . $value['id'];
                                }else{
                                    $lastData = $lastDataResult[0];
                                    $days = $lastData['day'];
                                    $target_vip_time = strtotime("+$days day", $lastData['vip_starttime']);

                                    if($value['createtime'] > $target_vip_time){
                                        $update_sql = "UPDATE recharge SET vip_starttime = createtime WHERE id = " . $value['id'];
                                    }else{
                                        $update_sql = "UPDATE recharge SET vip_starttime = ".$target_vip_time." WHERE id = " . $value['id'];
                                    }
                                }
                                $db->query($update_sql);
                            }
                        }
                        $output->writeln($db->getConfig('database') . "数据库，Recharge表中第" . $startNum . "到" . ($startNum + $pagenum) . "条数据更新完成");
                        $output->writeln("======================");
                    }
                    $output->writeln($db->getConfig('database') . "数据库，Recharge表中数据更新完成");
                }
                $output->writeln($db->getConfig('database') . "数据库，Recharge表中没有数据，程序结束！");
                Log::info("数据库" . $db->getConfig('database') . "中的信息，顺利执行完成");
            } catch (\Exception $exception) {
                Log::error($exception->getMessage());
                Log::error("数据库" . $db->getConfig('database') . "中的信息，出错了,需要重新执行");
            }
        }
        Log::info("刷vip_starttime完成，结束时间:" . date("Y-m-d H:i:s", time()));
    }

    // 获取recharge表中数据总条数
    private function getTotalNum(Output $output, $db)
    {
        $num_sql = "SELECT COUNT(*) as total FROM recharge WHERE type IN('".self::VIP_RECHARGE."', '".self::SYS_VIP_RECHARGE."')";
        $num_result = $db->query($num_sql);
        return $num_result[0]['total'] ?? 0;
    }

    //链接数据库
    private function dbConnect($db_num)
    {
        Log::info("开始刷vip_starttime，开始时间:".date("Y-m-d H:i:s", time()));
        $db_config = $this->charge_get_db_deploy($db_num);
        $db = Db::connect($db_config);
        return $db;
    }

    //分库 0-512
    private function charge_get_db_deploy($mod, $deploy = 'user')
    {
        $db = Config::get('db');
        $list = explode(';', $db[$deploy . '_list']);
        foreach ($list as $item) {
            $con = explode(':', $item); // 0=0-191库编号 1=192.168.1.149主IP 2=3306主端口 3=192.168.1.150从IP 4=3306从端口
            if (count($con) >= 3) {
                $c = explode('-', $con[0]); //库编号  0开始 1结束
                if (count($c) >= 2) {
                    if ($c[0] <= $mod && $mod <= $c[1]) {
                        $database = Config::get('database');
                        if ($database['deploy'] == 1 && count($con) >= 5) { //开启主从 & 带主从配置
                            $database['hostname'] = $con[1] . ',' . $con[3]; //192.168.1.149,192.168.1.150
                            $database['hostport'] = $con[2] . ',' . $con[4]; //3306,3306
                        } else { //只有主库
                            $database['hostname'] = $con[1];
                            $database['hostport'] = $con[2];
                        }
                        Log::info("分库获取成功 IP：{$database['hostname']} port: {$database['hostport']}");
                        $database['database'] = str_replace('$mod', $mod, $db[$deploy . '_database']);
                        return $database;
                    }
                }
            }
        }
        Log::error("分库获取失败！");
        return [];
    }

}