zhuishuyun_quickapp/app/Console/Commands/UserPayUpdateTask.php

<?php
/**
 * Created by PhpStorm.
 * User: tandunzhao
 * Date: 2017/11/20
 * Time: 下午5:26
 */

namespace App\Console\Commands;

use DB;
use Illuminate\Console\Command;
use Log;
use Redis;


class UserPayUpdateTask extends Command
{
    /**
     * 执行命令   php artisan force_user_active
     *
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'userPayUpdateTask';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '更新user_pay表字段';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $min_id = DB::table('global_configs')->where('config','pay_user_orders_max_id')->first();
        $min_id = $min_id->config_value;
        $max_id = DB::table('orders')->max('id');
        //插入user_pay
        while ($min_id+9999 <= $max_id){
            DB::update("insert into user_pay (uid,distribution_channel_id,first_recharge_time,first_recharge_amount) 
                select uid,distribution_channel_id,min(created_at),price from orders
                 where status ='PAID'  and orders.id >=".$min_id." and orders.id<=".($min_id+9999)." and not EXISTS
                 (select 1 from user_pay where uid = orders.uid) GROUP BY uid");
            $min_id = $min_id+9999;
        }
        DB::table('global_configs')->where('config','pay_user_orders_max_id')->update(['config_value'=>$max_id]);

        DB::update("update user_pay set user_pay.sec_recharge_time = (SELECT orders.created_at FROM orders
where user_pay.uid = orders.uid and orders.created_at > user_pay.first_recharge_time and status = 'PAID' LIMIT 1),user_pay.sec_recharge_amount=(SELECT orders.price FROM orders
where user_pay.uid = orders.uid and orders.created_at > user_pay.first_recharge_time and status = 'PAID' LIMIT 1) WHERE user_pay.sec_recharge_time is null");
       //三充时间、金额更新
        DB::update("update user_pay set user_pay.third_recharge_time = (SELECT orders.created_at FROM orders where user_pay.uid = orders.uid and orders.created_at > user_pay.sec_recharge_time AND status = 'PAID' LIMIT 1),
user_pay.third_recharge_amount=(SELECT orders.price FROM orders where user_pay.uid = orders.uid and orders.created_at > user_pay.sec_recharge_time AND status='PAID' LIMIT 1) WHERE user_pay.third_recharge_amount is null");
        //余额更新
        DB::update("update user_pay set user_pay.balance = (select users.balance from users where users.id = user_pay.uid)");
    }


    private function test1()
    {
        $array = ['recent_read','subscribe','text_sign','text_search','readerover','readrecord','daily_sign'];//

//        $array = ['daily_sign'];

        foreach ($array as $v){
            $users = Redis::SMEMBERS("temp_from_where:".$v);
            foreach ($users as $user){
                $sub = DB::connection('chapter_order_mysql')
                    ->table('chapter_orders'.($user%512))
                    ->where('uid',$user)
                    ->whereBetween('created_at',['2018-11-23','2018-12-07 23:59:59'])
                    ->first();
                if($sub){
                    $fee = DB::connection('chapter_order_mysql')
                        ->table($v.'_user')
                        ->select('uid','fee')
                        ->where('uid',$user)
                        ->first();
                    DB::connection('chapter_order_mysql')
                        ->table($v.'_user_sub')
                        ->insert(['uid'=>$user,'fee'=>isset($fee->fee)?$fee->fee:-1]);
                }
            }
             /*DB::connection('chapter_order_mysql')
                 ->table($v.'_user_sub')
                 ->select('uid','fee')
                 ->orderBy('id')
                 ->chunk(10000,function($users) use($v){
                     foreach ($users as $user){
                         $sub = DB::table('chapter_orders'.(($user->uid)%512))
                             ->where('uid',$user->uid)
                             ->whereBetween('created_at',['2018-11-23','2018-12-07 23:59:59'])
                             ->first();
                         if($sub){
                             DB::table($v.'_user_sub')->insert(['uid'=>$user->uid,'fee'=>$user->fee]);
                         }
                     }
            });*/

            //$total_users = $above_five_thousands_total = $above_five_thousands_male = $above_five_thousands_felman = 0;
            //$above_three_thousands_total = $above_three_thousands_male = $above_three_thousands_felman = 0;
            //$users = Redis::SMEMBERS("temp_from_where:".$v);
            /*foreach ($users as $user) {

                DB::connection('chapter_order_mysql')->table($v.'_user')->insert(['uid'=>$user]);
                /*$book_order = DB::table('book_orders')
                    ->where('uid', $user)
                    ->where('created_at', '>=', date('Y-m-d 00:00:00', strtotime('-15 day')))
                    ->first();
                $table_seq = ($user) % 512;
                $chapter_order = DB::connection('chapter_order_mysql')
                    ->table('chapter_orders' . $table_seq)
                    ->where('uid', $user)
                    ->where('created_at', '>=', date('Y-m-d 00:00:00', strtotime('-15 day')))
                    ->first();

                if ($book_order || $chapter_order) {
                    //\Log::info(($user->uid).':'.$subscribe_fee);
                    $total_users++;
                    $chapter_order_sum = DB::connection('chapter_order_mysql')
                        ->table('chapter_orders' . $table_seq)
                        ->where('uid', $user)
                        ->sum('fee');
                    $book_order_sum = DB::table('book_orders')
                        ->where('uid', $user)
                        ->sum('fee');
                    $subscribe_fee = $book_order_sum + $chapter_order_sum;
                    $gender = 0;
                    if ($subscribe_fee >= 3000) {
                        $user_channel = DB::table('force_subscribe_users')
                            ->leftjoin('books', 'books.id', '=', 'force_subscribe_users.bid')
                            ->leftjoin('book_categories', 'book_categories.id', '=', 'books.category_id')
                            ->where('force_subscribe_users.uid', $user)
                            ->first();
                        if ($user_channel) {
                            $gender = $user_channel->pid;
                        } else {
                            \Log::error('user_gender_not_found:uid:' . $user);
                        }
                        $above_three_thousands_total++;
                        if ($gender == 1) {
                            $above_three_thousands_male++;
                        }
                        if ($gender == 2) {
                            $above_five_thousands_felman++;
                        }
                    }

                    if ($subscribe_fee >= 5000) {
                        $user_channel = DB::table('force_subscribe_users')
                            ->leftjoin('books', 'books.id', '=', 'force_subscribe_users.bid')
                            ->leftjoin('book_categories', 'book_categories.id', '=', 'books.category_id')
                            ->where('force_subscribe_users.uid', $user)
                            ->first();
                        if ($user_channel) {
                            $gender = $user_channel->pid;
                        } else {
                            \Log::error('user_gender_not_found:uid:' . $user);
                        }
                        $above_three_thousands_total++;
                        if ($gender == 1) {
                            $above_three_thousands_male++;
                        }
                        if ($gender == 2) {
                            $above_five_thousands_felman++;
                        }
                    }


                //}
            }*/
            //return true;
            /*myLog('temp_from_where')->info($v.'---------------------------------------------------');
            myLog('temp_from_where')->info(compact(
                'v',
                'total_users',
                'above_five_thousands_total',
                'above_five_thousands_male',
                'above_five_thousands_felman',
                'above_three_thousands_total',
                'above_three_thousands_male',
                'above_three_thousands_felman'
            ));*/
        }

    }

    private function test2() {
        $users = DB::table('daily_sign_user_sub')->where('fee',-1)->get();
        foreach ($users as $user){
            $fee = DB::table('chapter_orders'.($user%512))
                ->where('uid',$user->uid)
                ->where('created_at','<=','2018-12-07 23:59:59')
                ->sum('fee');
            $user->fee = $fee;
            $user->save();
        }

    }
}