wangdu_public_api/app/Console/Commands/ChapterOrderTotal.php

<?php

namespace App\Console\Commands;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '更新章节订购统计';

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

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $option = $this->option('type');
        if(!$option){
            $this->sub1();
        }
        if($option == 'cp'){
            $this->cpChargeAndRewardV2();
        }
        if($option == 'order'){
            $this->orderDayStat();
        }
    }


    private function sub1(){
        $start = date('Y-m-d',time()-86400);
        $end = date('Y-m-d');
        $sql = 'call cp_data_temp("'.$start.'","'.$end.'");';
        DB::connection('chapter_order_mysql')->select($sql);
        $bids = DB::table('book_configs')->select('bid')->get();
        $data = [];
        $day = date('Y--m-d',time()-86400);
        foreach ($bids as $book){
            $fee = Redis::hget('wap:chapterandbookorder:bid:'.$book->bid,$day);
            if(!$fee) $fee = 0;
            $data[] = ['bid'=>$book->bid,'day'=>$day,'fee'=>$fee,'created_at'=>date('Y-m-d H:i:s'),'updated_at'=>date('Y-m-d H:i:s')];
            if(count($data) == 100){
                DB::table('book_order_statistical')->insert($data);
                $data = [];
            }
            Redis::hdel('wap:chapterandbookorder:bid:'.$book->bid,$day);
        }
        DB::table('book_order_statistical')->insert($data);
        $end2 = $start.' 23:59:59';

        $sql2 = 'call chapter_orders("'.$start.'","'.$end2.'");';
        DB::connection('chapter_order_mysql')->select($sql2);
    }


    private function cpChargeAndReward(){
        $date = date('Y-m-d',time()-86400);
        $start_date = $date;
        $end_date = date('Y-m-d');
        $sql_format = "select bid,date(created_at) as date,sum(charge_balance) as sum_charge_balance ,sum(reward_balance) as sum_reward_balance from book_orders where created_at >'%s' AND created_at <'%s' GROUP by bid,date(created_at)";
        echo sprintf($sql_format,$start_date,$end_date);
        $book = DB::select(sprintf($sql_format,$start_date,$end_date));
        foreach ($book as $v){
            DB::table('book_order_statistical')->where('bid',$v->bid)->where('day',$v->date)->increment('charge_balance',(int)$v->sum_charge_balance);
            DB::table('book_order_statistical')->where('bid',$v->bid)->where('day',$v->date)->increment('reward_balance',(int)$v->sum_reward_balance);
        }

        $sql = "select bid,date,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,date";
        $chapter = DB::connection('chapter_order_mysql')->select(sprintf($sql,$date));
        foreach ($chapter as $v){
            DB::table('book_order_statistical')->where('bid',$v->bid)->where('day',$v->date)->increment('charge_balance',(int)$v->sum_charge_balance);
            DB::table('book_order_statistical')->where('bid',$v->bid)->where('day',$v->date)->increment('reward_balance',(int)$v->sum_reward_balance);
        }
    }


    private function cpChargeAndRewardV2(){
        $date = date('Y-m-d',time()-86400);
        $max_bid_info = DB::table('books')->select('id')->orderBy('id','desc')->first();
        $max_bid = $max_bid_info->id;
        $start_date = $date;
        $end_date = date('Y-m-d');
        $sql_format = "select bid,date(created_at) as date,sum(charge_balance) as sum_charge_balance ,sum(reward_balance) as sum_reward_balance from book_orders where created_at >'%s' AND created_at <'%s' GROUP by bid,date(created_at)";

        $book = DB::select(sprintf($sql_format,$start_date,$end_date));
        foreach ($book as $v){
            DB::table('book_order_statistical')->where('bid',$v->bid)->where('day',$v->date)->increment('charge_balance',(int)$v->sum_charge_balance);
            DB::table('book_order_statistical')->where('bid',$v->bid)->where('day',$v->date)->increment('reward_balance',(int)$v->sum_reward_balance);
        }
        $sql = "select bid,date,sum(sum_charge_balance) as sum_charge_balance, sum(sum_reward_balance) as sum_reward_balance from sub_bak5 where date='%s' and bid=%s";
        for($i =0;$i <= $max_bid ;$i++){
            $temp = DB::connection('chapter_order_mysql')->select(sprintf($sql,$date,$i));
            $temp = $temp[0];
            if($temp->bid){
                DB::table('book_order_statistical')->where('bid',$i)->where('day',$date)->increment('charge_balance',(int)$temp->sum_charge_balance);
                DB::table('book_order_statistical')->where('bid',$i)->where('day',$date)->increment('reward_balance',(int)$temp->sum_reward_balance);
            }
        }

    }

    private function orderDayStat(){
        DB::table('order_day_stats')->select('id', 'distribution_channel_id', 'date')->where('date',date('Y-m-d',time()-86400))->orderBy('id')->chunk(1000, function ($res) {
            foreach ($res as $v) {
                $chapter_sql = "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 distribution_channel_id=%s and date='%s'";
                $chapter_data = DB::connection('chapter_order_mysql')->select(sprintf($chapter_sql,$v->distribution_channel_id,$v->date));

                $update_sql = "update send_orders_stats set sum_fee=sum_fee+".(int)$chapter_data[0]->sum_fee.',sum_charge_balance=sum_charge_balance+'.(int)$chapter_data[0]->sum_charge_balance.',sum_reward_balance=sum_reward_balance+'.(int)$chapter_data[0]->sum_reward_balance.' where id='.$v->id;
                DB::update($update_sql);

                $end_date = date('Y-m-d',strtotime($v->date)+86400);
                $book_sql = "select sum(fee) as sum_fee,sum(charge_balance) as sum_charge_balance,sum(reward_balance) as sum_reward_balance from book_orders where distribution_channel_id={$v->distribution_channel_id} and created_at >='{$v->date}' and created_at < '{$end_date}'";
                $chapter_data = DB::select($book_sql);

                $update_sql = "update send_orders_stats set sum_fee=sum_fee+".(int)$chapter_data[0]->sum_fee.',sum_charge_balance=sum_charge_balance+'.(int)$chapter_data[0]->sum_charge_balance.',sum_reward_balance=sum_reward_balance+'.(int)$chapter_data[0]->sum_reward_balance.' where id='.$v->id;
                DB::update($update_sql);
            }
        });
    }

}