book_admin/application/admin/command/CampaignStatistics.php

<?php


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;

/**
 * 消耗活动统计订单的脚本（废弃-订单表将迁移至polardb）
 * Class CampaignStatistics
 * @package app\admin\command
 */
class CampaignStatistics extends Command
{
    /**
     * 配置指令
     */
    protected function configure()
    {
        $this->setName('CampaignStatistics')
            ->addArgument('date', Argument::REQUIRED, "时间");
    }
    protected function execute(Input $input, Output $output)
    {
        $date = $input->getArgument('date');
        //cli模式下无法获取到当前的项目模块，手动指定一下
        Request::instance()->module('admin');
        if ( !$date ){
            $date = date( 'Ymd', time()-3600*24*8 );
        }
        $sql = "select user_id,money,FROM_UNIXTIME(createtime) as ordertime,createtime from orders_extend e JOIN orders o on e.order_id=o.id where finishtime>0 and FROM_UNIXTIME(createtime,'%Y%m%d')=".$date;
        $data = model('orders_extend')->query($sql);
        $title = 'user_id,money,ordertime,ordertime,isCheckIn,isCharge'.PHP_EOL;
        file_put_contents(LOG_PATH.'/order'.$date.'.csv',$title,FILE_APPEND);
        if ( !empty($data) ){
            foreach ($data as $k=>$v){
                $str = '';
                $userMatch = $this->dbConnect($v['user_id'],'user')->table('user_match')
                    ->field('id,createtime')
                    ->where('user_id','eq',$v['user_id'] )
                    ->where('match_date','eq',$date )
                    ->find();
                $recharge = $this->dbConnect($v['user_id'],'shard')->table('consume')->where('createtime','<',$v['createtime'])->find();
                $v['isCheckIn'] = empty($userMatch) ? 0 : $userMatch['createtime'];
                $v['isRcharge'] = empty($recharge) ? 0 : date('Y-m-d H:i:s',$recharge['createtime'] );
               foreach($v as $kk =>$vv ){
                   $str .= $vv.',';
               }
              $str = trim($str,',').PHP_EOL;
                file_put_contents(LOG_PATH.'/order'.$date.'.csv',$str,FILE_APPEND);
                log::info('消耗活动订单和报名对比:'.json_encode($v));
            }
            echo 'success';
        }else{
            echo '没有消耗活动类型订单';
        }

    }
    /**
     * 获取数据库连接
     * @param $param 编号
     * @param string $deploy 业务
     * @return \think\db\Connection
     * @throws \think\Exception
     */
    private function dbConnect($param, $deploy)
    {
        $db_config = $this->get_db_deploy($param, $deploy);
        if (empty($this->dbConnects[$db_config['database']])) {
            Log::info(sprintf('打开数据库连接，database:%s', $db_config['database']));
            $this->dbConnects[$db_config['database']] = Db::connect($db_config);
        }
        return $this->dbConnects[$db_config['database']];
    }

    /**
     * 关闭数据库连接
     */
    private function closeDbConnect()
    {
        foreach ($this->dbConnects as $database => $dbConnect) {
            Log::info(sprintf('关闭数据库连接，database:%s', $database));
            $dbConnect->close();
        }
        $this->dbConnects = [];
    }

    /**
     * 获取db分库的配置参数
     *
     * @param string|int $param 取模值
     * @param string $deploy 分库前缀
     * @return array
     */
    function get_db_deploy($param, $deploy = 'shard')
    {
        $db = Config::get('db');
        $mod = $param % $db[$deploy . '_num'];
        $mod = abs($mod);
        $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];
                        }
                        $database['database'] = str_replace('$mod', $mod, $db[$deploy . '_database']);
                        return $database;
                    }
                }
            }
        }
        Log::error("分库获取失败！");
        return [];
    }
}