book_admin/application/admin/command/UpdateReferralOrder.php

<?php
/**
 * Created by PhpStorm.
 * User: lytian
 * Date: 2019/5/16
 * Time: 15:07
 */

namespace app\admin\command;


use app\common\library\Redis;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Db;
use think\Log;
use think\Request;

class UpdateReferralOrder extends Command
{
    protected $message = '';

    protected function configure()
    {
        $this->setName('UpdateReferralOrder')
            ->setDescription('刷新推广链接表订单数');
    }

    //获取公众号
    protected function execute(Input $input, Output $output)
    {
        //$output->writeln("UpdateReferralOrder---开始");
        Log::info("UpdateReferralOrder---开始");
        Request::instance()->module('admin');
        try {
            //拉取订单
            $redis = Redis::instance();
            $key = 'RFUO';
            //$redis->set($key, 0);

            while (true) {
                $lastId = (int)$redis->get($key);
                $count = Db::table("referral")->where('id', '>', $lastId)->where('money', '>', 0)->count();

                if ($count < 1) {
                    break;
                } else {
                    $max = ceil($count/100);

                    for($i = 0; $i < $max; $i++) {
                        $lastId = (int)$redis->get($key);
                        $rows =  Db::table('referral')->field('id')->where('id', '>', $lastId)->where('money','>',0)->order('id', 'asc')->limit(100)->select();
                        $referrids = array_column($rows, 'id');
                        $referridsStr = implode(',', $referrids);

                        $sql = "select referral_id, count(id) as orders_num from (select id, referral_id from orders where referral_id in (".$referridsStr.") and state = '1' and deduct = 0 union select id, referral_id_permanent as referral_id from orders where referral_id_permanent in (".$referridsStr.") and state = '1' and deduct = 0) as a group by referral_id";

                        $datas = Db::query($sql);
                        $referrid = 0;

                        foreach ($datas as $data) {
                            //查询订单数
                            $referrid = $data['referral_id'];
                            $orderNums = (int)$data['orders_num'];

                            $sql = "update referral set orders_num= ".$orderNums." where id =".$referrid;
                            Db::execute($sql);
                        }

                        $redis->set($key, $referrid);
                        unset($rows);
                        unset($datas);
                    }
                }
            }

            Redis::instance()->del($key);

            Log::info("UpdateReferralOrder---脚本执行成功 end");
            //$output->writeln('UpdateReferralOrder---脚本执行成功 end');
        } catch (\Exception $e) {
            //$output->writeln('UpdateReferralOrder---脚本执行失败' . $e->getMessage());
            Log::error('UpdateReferralOrder---脚本执行成功' . $e->getMessage());
        }
    }
}