book_admin/application/admin/command/RefreshOrderCollect.php

<?php

namespace app\admin\command;

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

class RefreshOrderCollect extends Command
{
    protected function configure()
    {
        $this->setName('RefreshOrderCollect')
            ->addOption('current_time', 'c', Option::VALUE_OPTIONAL, '时间节点：2019-08-11', null)
            ->setDescription('刷新转移渠道商订单统计表');
    }

    protected function execute(Input $input, Output $output)
    {
        Request::instance()->module('admin'); //cli模式下无法获取到当前的项目模块，手动指定一下
        $mainSlaveDbConfig = $this->getMainSlaveDbConfig();

        try {
            $configModel = model('Config');
            $config = $configModel->getConfigSiteArr();

            $change_ids = $config['change_ids'] ?? '';

            if (empty($change_ids)) {
                Log::info("转移渠道id为空，程序结束");
                return false;
            }
            Log::info('转移渠道商：' . $change_ids);
            $aChangeIds = explode(',', $change_ids);
            $strTime = $input->getOption('current_time');

            if ($strTime === null) {
                $beginTime = strtotime(date('Y-m-d', strtotime('yesterday')));
                $endTime = strtotime(date('Y-m-d 23:59:59', strtotime('yesterday')));
            } else {
                $beginTime = strtotime($strTime);
                if ($beginTime === false) {
                    Log::info("current_time 参数格式错误");
                    return false;
                }
                $endTime = strtotime($strTime . ' 23:59:59');
            }
            $currentDay = date('Ymd', $beginTime);

            Log::info(sprintf('订单时间段：%s ~ %s', date('Y-m-d H:i:s', $beginTime), date('Y-m-d H:i:s', $endTime)));

            $sql = <<<SQL
SELECT `admin_id` AS `admin_id`, sum(money) AS `sum_money`, sum(money_benefit) AS `sum_money_benefit`
FROM `orders` force index(createtime)
WHERE `admin_id` IN
      ($change_ids)
  AND `state` = '1' 
  AND (`createtime` between $beginTime AND $endTime)
GROUP BY `admin_id`;
SQL;
            if (Config::get('polardb.orders_apply_polardb')) {
                $orderResult = Db::connect(Config::get('polardb'))->query($sql);
            } else {
                $orderResult = Db::connect($mainSlaveDbConfig)->query($sql);
            }

            $selfOrderCollectResult = model('OrdersCollect')->whereIn('admin_id', $aChangeIds)
                ->where('type', '1')
                ->where('flag', '1')
                ->where('createdate', $currentDay)
                ->field(['id', 'admin_id', 'recharge_money', 'recharge_money_benefit'])
                ->select();

            $allOrderCollectResult = model('OrdersCollect')->whereIn('admin_id', $aChangeIds)
                ->where('type', '1')
                ->where('flag', '3')
                ->where('createdate', $currentDay)
                ->field(['id', 'admin_id', 'recharge_money', 'recharge_money_benefit'])
                ->select();
            $updateSelfData = [];
            $updateAllData = [];
            foreach ($orderResult as $oRes) {
                foreach ($selfOrderCollectResult as $socRes) {
                    if ($oRes['admin_id'] == $socRes['admin_id']) {
                        $adminId = $oRes['admin_id'];
                        $subOrderMoney = $oRes['sum_money'] - $socRes['recharge_money'];
                        $subMoneyBenefit = $oRes['sum_money_benefit'] - $socRes['recharge_money_benefit'];
                        Log::info(sprintf('self result: adminId:%s,subOrderMoney:%s,subMoneyBenefit:%s', $adminId,
                            $subOrderMoney, $subMoneyBenefit));
                        if ($subOrderMoney != 0 || $subMoneyBenefit != 0) {
                            $updateSelfData[$adminId] = [
                                'id' => $socRes['id'],
                                'money' => $oRes['sum_money'],
                                'money_benefit' => $oRes['sum_money_benefit'],
                                'old_money' => $socRes['recharge_money'],
                                'old_money_benefit' => $socRes['recharge_money_benefit'],
                            ];
                            break;
                        }
                    }
                }

                foreach ($allOrderCollectResult as $aocRes) {
                    if ($oRes['admin_id'] == $aocRes['admin_id']) {
                        $adminId = $oRes['admin_id'];
                        $subOrderMoney = $oRes['sum_money'] - $aocRes['recharge_money'];
                        $subMoneyBenefit = $oRes['sum_money_benefit'] - $aocRes['recharge_money_benefit'];
                        Log::info(sprintf('all result: adminId:%s,subOrderMoney:%s,subMoneyBenefit:%s', $adminId,
                            $subOrderMoney, $subMoneyBenefit));
                        if ($subOrderMoney != 0 || $subMoneyBenefit != 0) {
                            $updateAllData[$adminId] = [
                                'id' => $aocRes['id'],
                                'money' => $oRes['sum_money'],
                                'money_benefit' => $oRes['sum_money_benefit'],
                                'old_money' => $aocRes['recharge_money'],
                                'old_money_benefit' => $aocRes['recharge_money_benefit'],
                            ];
                            break;
                        }
                    }
                }
            }

            foreach ($updateSelfData as $adminId => $item) {
                Log::info(sprintf('订单统计表，id:%s,old_money:%s,new_money:%s,old_money_benefit:%s,new_money_benefit:%s',
                    $item['id'], $item['old_money'], $item['money'], $item['old_money_benefit'],
                    $item['money_benefit']));

                model('OrdersCollect')->where('id', $item['id'])
                    ->update([
                        'recharge_money' => $item['money'],
                        'recharge_money_benefit' => $item['money_benefit']
                    ]);
            }

            foreach ($updateAllData as $admin => $item) {
                Log::info(sprintf('订单统计表，id:%s,old_money:%s,new_money:%s,old_money_benefit:%s,new_money_benefit:%s',
                    $item['id'], $item['old_money'], $item['money'], $item['old_money_benefit'],
                    $item['money_benefit']));

                model('OrdersCollect')->where('id', $item['id'])
                    ->update([
                        'recharge_money' => $item['money'],
                        'recharge_money_benefit' => $item['money_benefit']
                    ]);
            }

            Log::info('程序结束');
        } catch (\Exception $e) {
            Log::error($e->getMessage() . '  ' . $e->getTraceAsString());
        }
    }

    /**
     * 获取主库的从库配置  从库不存在返回主库
     * @return array
     */
    private function getMainSlaveDbConfig()
    {
        $hostArr = explode(',', Env::get('database.admin_hostname'));
        $portArr = explode(',', Env::get('database.admin_hostport', '3306,3306'));

        //默认主库
        $mainDbConfig = array_merge(Config::get("database"), ['hostname' => $hostArr[0], 'hostport' => $portArr[0]]);
        if (count($hostArr) >= 2) {
            //从库
            $mainDbConfig = array_merge(Config::get("database"),
                ['hostname' => $hostArr[1], 'hostport' => $portArr[1]]);
        }

        return $mainDbConfig;
    }
}