wangdu_public_api/app/Console/Commands/SubscribeDataStats.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use DB;
use Log;
class SubscribeDataStats extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'book:subds {--all}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $options = $this->option('all');
        if($options){
            $this->resetAll();
        }else{
            $this->start();
        }
    }

    private function _update($data){
        $sql_fromat = 'select sum(sum_fee) as sum_fee, sum(sum_charge_balance) as sum_charge_balance, sum(sum_reward_balance) as sum_reward_balance from sub_bak5 where bid=%s and send_order_id=%s';
        $sql = sprintf($sql_fromat,$data['bid'],$data['send_order_id']);
        $chapter = DB::connection('chapter_order_mysql')->select($sql);

        $sqls_fromat = 'select sum(fee) as sum_fee, sum(charge_balance) as sum_charge_balance, sum(reward_balance) as sum_reward_balance from book_orders where bid=%s and send_order_id=%s';
        $sqls = sprintf($sqls_fromat,$data['bid'],$data['send_order_id']);
        $book = DB::select($sqls);

        $total_fee = 0;
        $charge_balance = 0;
        $rewrad_blance = 0;
        if($chapter && isset($chapter[0])){
            $total_fee += (int)$chapter[0]->sum_fee;
            $charge_balance += (int)$chapter[0]->sum_charge_balance;
            $rewrad_blance += (int)$chapter[0]->sum_reward_balance;
        }

        if($book && isset($book[0])){
            $total_fee += (int)$book[0]->sum_fee;
            $charge_balance += (int)$book[0]->sum_charge_balance;
            $rewrad_blance += (int)$book[0]->sum_reward_balance;
        }

        DB::table('send_orders_stats')->where('id',$data['id'])->update([
            'sum_fee'=>$total_fee,
            'sum_charge_balance'=>$charge_balance,
            'sum_reward_balance'=>$rewrad_blance
        ]);
    }

    public function start(){
        $date = date('Y-m-d',time()-86400);
        $sql_fromat = 'select bid,send_order_id,sum(sum_fee) as sum_fee, sum(sum_charge_balance) as sum_charge_balance, sum(sum_reward_balance) as sum_reward_balance from sub_bak5 where date="%s"  group BY bid,send_order_id ';
        $sql = sprintf($sql_fromat,$date);
        $chapter = DB::connection('chapter_order_mysql')->select($sql);

        foreach ($chapter as $c){
            $update_temp = 'update send_orders_stats set sum_fee=sum_fee+'.(int)$c->sum_fee.', sum_charge_balance=sum_charge_balance+'.(int)$c->sum_charge_balance.',sum_reward_balance=sum_reward_balance+'.(int)$c->sum_reward_balance.' where bid='.(int)$c->bid.' and send_order_id='.(int)$c->send_order_id;
            DB::update($update_temp);
        }
        $start_date = $date;
        $end_date = date('Y-m-d');
        $sqls = "select bid,send_order_id,sum(fee) as sum_fee, sum(charge_balance) as sum_charge_balance, sum(reward_balance) as sum_reward_balance from book_orders where created_at >='{$start_date}' and created_at <'{$end_date}'  GROUP BY bid,send_order_id";
        $book = DB::select($sqls);

        foreach ($book as $b){
            $update_temp = 'update send_orders_stats set sum_fee=sum_fee+'.(int)$b->sum_fee.', sum_charge_balance=sum_charge_balance+'.(int)$b->sum_charge_balance.',sum_reward_balance=sum_reward_balance+'.(int)$b->sum_reward_balance.' where bid='.(int)$b->bid.' and send_order_id='.(int)$b->send_order_id;
            DB::update($update_temp);
        }
    }

    public function resetAll()
    {
        DB::table('send_orders_stats')->select('id', 'send_order_id', 'bid')->orderBy('id')->chunk(1000, function ($res) {
            foreach ($res as $v) {
                if ($v->send_order_id && $v->bid) {
                    $this->_update([
                        'id' => $v->id,
                        'send_order_id' => $v->send_order_id,
                        'bid' => $v->bid
                    ]);
                }
            }
        });
    }


}