zhuishuyun_quickapp/app/Console/Commands/Temp/FansArpu.php

<?php

namespace App\Console\Commands\Temp;

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

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '90天-120天新关粉丝arpu';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        /**
         * CREATE TABLE `fans_arpu` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `date` date DEFAULT NULL,
        `distribution_channel_id` int(11) DEFAULT NULL,
        `appid` varchar(255) DEFAULT NULL,
        `distribution_channel_name` varchar(255) DEFAULT NULL,
        `gzh_name` varchar(255) DEFAULT NULL,
        `fans_num` int(11) DEFAULT NULL,
        `recharge_amount_in_90_days` decimal(11,2) DEFAULT NULL,
        `recharge_amount_in_91_days` decimal(11,2) DEFAULT NULL,
        `recharge_amount_in_92_days` decimal(11,2) DEFAULT NULL,
        `recharge_amount_in_93_days` decimal(11,2) DEFAULT NULL,
        `recharge_amount_in_94_days` decimal(11,2) DEFAULT NULL,
        `recharge_amount_in_95_days` decimal(11,2) DEFAULT NULL,
        `recharge_amount_in_96_days` decimal(11,2) DEFAULT NULL,
        `recharge_amount_in_97_days` decimal(11,2) DEFAULT NULL,
        `created_at` datetime DEFAULT NULL,
        `updated_at` datetime DEFAULT NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
         *
         * update fans_arpu,distribution_channels,official_accounts set fans_arpu.distribution_channel_id = distribution_channels.id,fans_arpu.distribution_channel_name=distribution_channels.nickname,fans_arpu.gzh_name = official_accounts.nickname  where fans_arpu.appid = official_accounts.appid and official_accounts.distribution_channel_id = distribution_channels.id
         *
         *
         */
        print_r("======90天-120天新关粉丝arpu 【任务执行开始】=====".date("y-m-d H:i:s"."\n"));
        Log::info("======90天-120天新关粉丝arpu 【任务执行开始】=====".date("y-m-d H:i:s"."\n"));

        $_start = new \DateTime('2017-12-12');
        $_end = new \DateTime('2017-12-24');

        foreach (new \DatePeriod($_start, new \DateInterval('P1D'), $_end) as $d)
        {

            $date = $d->format('Y-m-d');
            $start = $date;
            $end = date('Y-m-d', strtotime($date) + 86400);

            $offset = 0;
            $limit = 1000;
            $data = [];
            $_m_data = [];

            while (true) {

                print_r("select send_order_id,uid,appid,unsubscribe_time,distribution_channel_id,created_at from force_subscribe_users where created_at > '{$start}' and created_at < '{$end}' limit $offset,$limit" . date("y-m-d H:i:s" . "\n"));
                $users = DB::select("select send_order_id,uid,appid,unsubscribe_time,distribution_channel_id,created_at from force_subscribe_users where created_at > '{$start}' and created_at < '{$end}' limit $offset,$limit");
                print_r("users_count:".count($users));
                if (count($users) == 0) break;

                foreach ($users as $user) {
                    @$data[$user->appid]['fans_num']++;

                    $order = DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->first();
                    if ($order)
                    {
                        @$data[$user->appid]['recharge_amount_in_90_days'] += (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400*90))->sum('price');
                        @$data[$user->appid]['recharge_amount_in_91_days'] += (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400*91))->sum('price');
                        @$data[$user->appid]['recharge_amount_in_92_days'] += (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400*92))->sum('price');
                        @$data[$user->appid]['recharge_amount_in_93_days'] += (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400*93))->sum('price');
                        @$data[$user->appid]['recharge_amount_in_94_days'] += (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400*94))->sum('price');
                        @$data[$user->appid]['recharge_amount_in_95_days'] += (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400*95))->sum('price');
                        @$data[$user->appid]['recharge_amount_in_96_days'] += (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400*96))->sum('price');
                        @$data[$user->appid]['recharge_amount_in_97_days'] += (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400*97))->sum('price');
                    }
                }
                $offset = $offset + $limit;
            }

            foreach ($data as $appid => $item) {
                $_data = [];
                $_data['date'] = $date;
                $_data['appid'] = $appid;
                $_data['fans_num'] = isset($item['fans_num']) ? $item['fans_num'] : 0;
                $_data['recharge_amount_in_90_days'] = isset($item['recharge_amount_in_90_days']) ? $item['recharge_amount_in_90_days'] : 0;
                $_data['recharge_amount_in_91_days'] = isset($item['recharge_amount_in_91_days']) ? $item['recharge_amount_in_91_days'] : 0;
                $_data['recharge_amount_in_92_days'] = isset($item['recharge_amount_in_92_days']) ? $item['recharge_amount_in_92_days'] : 0;
                $_data['recharge_amount_in_93_days'] = isset($item['recharge_amount_in_93_days']) ? $item['recharge_amount_in_93_days'] : 0;
                $_data['recharge_amount_in_94_days'] = isset($item['recharge_amount_in_94_days']) ? $item['recharge_amount_in_94_days'] : 0;
                $_data['recharge_amount_in_95_days'] = isset($item['recharge_amount_in_95_days']) ? $item['recharge_amount_in_95_days'] : 0;
                $_data['recharge_amount_in_96_days'] = isset($item['recharge_amount_in_96_days']) ? $item['recharge_amount_in_96_days'] : 0;
                $_data['recharge_amount_in_97_days'] = isset($item['recharge_amount_in_97_days']) ? $item['recharge_amount_in_97_days'] : 0;

                $_data['created_at'] = $_data['updated_at'] = date('Y-m-d H:i:s');
                $_m_data[] = $_data;
            }
            DB::table('fans_arpu')->insert($_m_data);

            Log::info("======90天-120天新关粉丝arpu 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n"));
            print_r("======90天-120天新关粉丝arpu 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n"));


        }
    }
}