wangdu_public_api/app/Console/Commands/OfficialAccountStat/SubUserRecharge.php

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

namespace App\Console\Commands\OfficialAccountStat;

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

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '强关粉丝充值统计';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        /**
         * CREATE TABLE `sub_user_recharge_stat` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `date` date DEFAULT NULL,
        `appid` varchar(255) DEFAULT NULL,
        `fans_num` int(11) DEFAULT NULL,
        `order_sum_in_one_day` decimal(11,2) DEFAULT NULL,
        `order_sum_in_three_day` decimal(11,2) DEFAULT NULL,
        `unsub_user_num_in_one_month` int(11) DEFAULT NULL,
        `order_sum_in_one_month` decimal(11,2) DEFAULT NULL,
        `unsub_user_num_in_two_month` int(11) DEFAULT NULL,
        `pay_user_num_in_two_month` int(11) DEFAULT NULL,
        `order_sum_in_two_month` decimal(11,2) DEFAULT NULL,
        `unsub_user_num_in_three_month` int(11) DEFAULT NULL,
        `order_sum_in_three_month` decimal(11,2) DEFAULT NULL,
        `pay_user_num_in_three_month` int(11) DEFAULT NULL,
        `created_at` datetime DEFAULT NULL,
        `updated_at` datetime DEFAULT NULL,
        `distribution_channel_id` int(11) DEFAULT NULL,
        `gzh_name` varchar(255) DEFAULT NULL,
        `distribution_channel_name` varchar(255) DEFAULT NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
         *
         *
        update sub_user_recharge_stat,distribution_channels,official_accounts set sub_user_recharge_stat.distribution_channel_id = distribution_channels.id,sub_user_recharge_stat.distribution_channel_name=distribution_channels.nickname,sub_user_recharge_stat.gzh_name = official_accounts.nickname  where sub_user_recharge_stat.appid = official_accounts.appid and official_accounts.distribution_channel_id = distribution_channels.id
        select distribution_channel_id,gzh_name,distribution_channel_name,date,fans_num,order_sum_in_one_day,order_sum_in_three_day,unsub_user_num_in_one_month,order_sum_in_one_month,unsub_user_num_in_two_month,pay_user_num_in_two_month,order_sum_in_two_month,unsub_user_num_in_three_month,pay_user_num_in_three_month,order_sum_in_three_month from sub_user_recharge_stat
         */
        print_r("======强关粉丝充值统计生成 【任务执行开始】=====".date("y-m-d H:i:s"."\n"));
        Log::info("======强关粉丝充值统计生成 【任务执行开始】=====".date("y-m-d H:i:s"."\n"));

        $_start = new \DateTime('2017-12-12');
        $_end = new \DateTime('2018-02-18');

        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 = 13000;
            $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) {
                    $is_unsub_in_one_day = false;
                    $is_pay_in_one_day = false;
                    $is_pay_in_two_days = false;
                    $is_pay_in_three_days = false;
                    $is_unsub_in_two_days = false;
                    $is_unsub_in_three_days = false;
                    $is_unsub_in_three_month = false;

                    $order_sum_in_one_day = 0;
                    $order_sum_in_two_day = 0;
                    $order_sum_in_three_day = 0;
                    $order_sum_in_one_month = 0;
                    $order_sum_in_two_month = 0;
                    $order_sum_in_three_month = 0;

                    $order = DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->first();
                    if ($order) {
                        if (strtotime($order->created_at) < strtotime($user->created_at) + 86400)//24小时内
                        {
                            @$data[$user->appid]['pay_user_num_in_one_day']++;
                            $is_pay_in_one_day = true;
                        }

                        if (strtotime($order->created_at) < strtotime($user->created_at) + 86400 * 2)//48小时内
                        {
                            $is_pay_in_two_days = true;
                        }

                        if (strtotime($order->created_at) < strtotime($user->created_at) + 86400 * 3)//72小时内
                        {
                            @$data[$user->appid]['pay_user_num_in_three_days']++;
                            $is_pay_in_three_days = true;
                        }

                        if (strtotime($order->created_at) < strtotime($user->created_at) + 86400 * 30)//30天内
                        {
                            @$data[$user->appid]['pay_user_num_in_one_month']++;
                        }

                        if (strtotime($order->created_at) < strtotime($user->created_at) + 86400 * 60)//60天内
                        {
                            @$data[$user->appid]['pay_user_num_in_two_month']++;
                        }

                        if (strtotime($order->created_at) < strtotime($user->created_at) + 86400 * 90)//90天内
                        {
                            @$data[$user->appid]['pay_user_num_in_three_month']++;
                        }

                        $order_sum_in_one_day = (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))->sum('price');
                        //$order_sum_in_two_day = (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*2))->sum('price');
                        $order_sum_in_three_day = (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*3))->sum('price');
                        $order_sum_in_one_month = (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*30))->sum('price');
                        $order_sum_in_two_month = (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*60))->sum('price');
                        $order_sum_in_three_month = (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]['order_sum_in_one_day'] += $order_sum_in_one_day;
                    @$data[$user->appid]['order_sum_in_three_day'] += $order_sum_in_three_day;
                    @$data[$user->appid]['order_sum_in_one_month'] += $order_sum_in_one_month;
                    @$data[$user->appid]['order_sum_in_two_month'] += $order_sum_in_two_month;
                    @$data[$user->appid]['order_sum_in_three_month'] += $order_sum_in_three_month;

                    @$data[$user->appid]['fans_num']++;

                    if ($user->unsubscribe_time && strtotime($user->unsubscribe_time) < strtotime($user->created_at) + 86400*30)//30天内取注
                    {
                        @$data[$user->appid]['unsub_user_num_in_one_month']++;
                    }

                    if ($user->unsubscribe_time && strtotime($user->unsubscribe_time) < strtotime($user->created_at) + 86400*60)//60天内取注
                    {
                        @$data[$user->appid]['unsub_user_num_in_two_month']++;
                    }

                    if ($user->unsubscribe_time && strtotime($user->unsubscribe_time) < strtotime($user->created_at) + 86400*90)//90天内取注
                    {
                        @$data[$user->appid]['unsub_user_num_in_three_month']++;
                    }

                }

                $offset = $offset + $limit;

            }

            foreach ($data as $appid => $item) {
                $_data = [];
                $_data['appid'] = $appid;
                $_data['date'] = $date;
                $_data['fans_num'] = isset($item['fans_num']) ? $item['fans_num'] : 0;
                $_data['order_sum_in_one_day'] = $item['order_sum_in_one_day'];//24小时充值收益
                $_data['order_sum_in_three_day'] = $item['order_sum_in_three_day'];//72小时充值

                //30天数据
                $_data['unsub_user_num_in_one_month'] = isset($item['unsub_user_num_in_one_month']) ? $item['unsub_user_num_in_one_month'] : 0;//30天内取关粉丝数
                $_data['order_sum_in_one_month'] = $item['order_sum_in_one_month'];//30天充值

                //60天数据
                $_data['unsub_user_num_in_two_month'] = isset($item['unsub_user_num_in_two_month']) ? $item['unsub_user_num_in_two_month'] : 0;//60天取关粉丝数
                $_data['pay_user_num_in_two_month'] = isset($item['pay_user_num_in_two_month']) ? $item['pay_user_num_in_two_month'] : 0;//60天内首充用户数
                $_data['order_sum_in_two_month'] = $item['order_sum_in_two_month'];//60天充值

                //90天数据
                $_data['unsub_user_num_in_three_month'] = isset($item['unsub_user_num_in_three_month']) ? $item['unsub_user_num_in_three_month'] : 0;//90天取关粉丝数
                $_data['order_sum_in_three_month'] = $item['order_sum_in_three_month'];//90天充值
                $_data['pay_user_num_in_three_month'] = isset($item['pay_user_num_in_three_month']) ? $item['pay_user_num_in_three_month'] : 0;//90天内首充用户数

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

            Log::info("======强关粉丝充值统计生成 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n"));
            print_r("======强关粉丝充值统计生成 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n"));


        }
    }
}