book_admin/application/admin/command/UpdateRechargeChannelById.php

<?php
/**
 * Created by PhpStorm.
 * User: wanggb
 * Date: 2019/9/24
 * Time: 10:14
 */

namespace app\admin\command;
use think\Config;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\Output;
use think\Db;
use think\Log;
use think\Request;

class UpdateRechargeChannelById extends Command
{
    const VIP_RECHARGE = '2';
    const SYS_VIP_RECHARGE = '4';

    protected function configure()
    {
        $this->setName('UpdateRechargeChannelById')
            ->addArgument('filename', Argument::REQUIRED, 'Rechargeid 文件名，文件绝对路径')
            ->setDescription('根据Recharge表中最后一条记录的ID，刷新 channel_vip_starttime 数据');
    }

    protected function execute(Input $input, Output $output)
    {
        Request::instance()->module('admin');
        $filename = $input->getArgument('filename');

        if(!file_exists($filename)){
            $output->writeln($filename.'文件不存在,脚本终止');
            exit();
        }

        $json_str = file_get_contents($filename);
        $arr = json_decode($json_str, true);

        try{
            if(count($arr) > 0){
                $i= 0;
                foreach ($arr as $db_idx => $recharge_last_id){
                    $i++;
                    $db = $this->dbConnect($db_idx);
                    $t_num_sql = "SELECT count(*) as num FROM recharge WHERE type IN('" . self::VIP_RECHARGE . "' , '" . self::SYS_VIP_RECHARGE . "') AND dd=0 AND id > $recharge_last_id";
                    $t_num_data = $db->query($t_num_sql);
                    if ($t_num_data && ($t_num_data[0]['num'] > 0)) {
                        $output->writeln("第" . $db_idx . "号数据库中有".$t_num_data[0]['num']."条需要刷新的数据");
                        $this->updateRechargeData($db, $recharge_last_id, $t_num_data[0]['num'], $output);
                        $output->writeln("第" . $db_idx . "号数据库数据刷新完成");
                    } else {
                        $output->writeln("第" . $db_idx . "号数据库中没有需要刷新的数据");
                        continue;
                    }
                }
            }
        }catch (\Exception $exception){
            Log::error($exception->getMessage());
            $output->writeln($exception->getMessage().'程序终止');
            exit();
        }
    }

    /**
     * @param $db
     * @param $recharge_last_id   上次执行结束时的ID
     * @param $t_num              有N条新的记录需要刷新
     * @param Output $output
     */
    private function updateRechargeData($db, $recharge_last_id, $t_num, Output $output)
    {
        $sql = "SELECT id,user_id,channel_vip_starttime,`day`,createtime from recharge WHERE type IN('" . self::VIP_RECHARGE . "' , '" . self::SYS_VIP_RECHARGE . "') AND dd=0  AND  id > " . $recharge_last_id;

        $data = $db->query($sql);

        if(count($data)){
            foreach ($data as $key => $value) {
                $lastDataSql = "SELECT * FROM recharge WHERE user_id = " . $value['user_id'] . "  AND type IN('" . self::VIP_RECHARGE . "' , '" . self::SYS_VIP_RECHARGE . "') AND dd=0  AND id <" . $value['id'] . " ORDER BY id DESC LIMIT 1";
                $lastDataResult = $db->query($lastDataSql);

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

                    if($value['createtime'] > $target_vip_time){
                        $update_sql = "UPDATE recharge SET channel_vip_starttime = createtime WHERE id = " . $value['id'];
                    }else{
                        $update_sql = "UPDATE recharge SET channel_vip_starttime = ".$target_vip_time." WHERE id = " . $value['id'];
                    }
                }
                $db->query($update_sql);
            }
        }
    }

    //链接数据库
    private function dbConnect($db_num)
    {
        $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 [];
    }
}