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

<?php

namespace App\Console\Commands\Temp;

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

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '服务号订阅数据';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        /**
         * CREATE TABLE `official_account_day_sub_stats` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `appid` varchar(255) DEFAULT NULL,
        `official_account_name` varchar(255) DEFAULT NULL,
        `date` date DEFAULT NULL,
        `reward_balance` int(11) DEFAULT NULL,
        `charge_balance` int(11) DEFAULT NULL,
        `fee` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;*
         *
         */
        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-06-01');

        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 = [];


            for ($i=0;$i<512;$i++)
            {
                //章节订购
                $chapter_table = 'zsy_sub.chapter_orders'.$i;
                $force_user_table = 'yueduyun.force_subscribe_users';
                $users = DB::connection('chapter_order_mysql')->select("select appid,{$chapter_table}.distribution_channel_id,{$chapter_table}.uid,sum(fee) sum_fee,sum(charge_balance) sum_charge_balance,sum(reward_balance) sum_reward_balance from {$chapter_table} left join {$force_user_table} on {$chapter_table}.uid = {$force_user_table}.uid  where {$chapter_table}.created_at >= '{$date}' and {$chapter_table}.created_at < '{$end}'  group by {$chapter_table}.uid");
                print_r("select appid,{$chapter_table}.distribution_channel_id,{$chapter_table}.uid,sum(fee) sum_fee,sum(charge_balance) sum_charge_balance,sum(reward_balance) sum_reward_balance from {$chapter_table} left join {$force_user_table} on {$chapter_table}.uid = {$force_user_table}.uid  where {$chapter_table}.created_at >= '{$date}' and {$chapter_table}.created_at < '{$end}'  group by {$chapter_table}.uid" . "\n");

                foreach ($users as $user) {

                    if(!$user->appid)
                    {
                        //获取第一个appid
                        $official_account = DB::connection('chapter_order_mysql')->table('yueduyun.official_accounts')->where('distribution_channel_id',$user->distribution_channel_id)->first();
                        if($official_account)
                        {
                            $user->appid = $official_account->appid;
                        }else{
                            $user->appid = 'no';
                        }

                    }
                    if($user->appid)
                    {
                        @$data[$user->appid]['fee'] += $user->sum_fee;
                        @$data[$user->appid]['charge_balance'] += $user->sum_charge_balance;
                        @$data[$user->appid]['reward_balance'] += $user->sum_reward_balance;
                    }

                }
            }


            //按本订购
            $chapter_table = 'yueduyun.book_orders';
            $force_user_table = 'yueduyun.force_subscribe_users';
            $users = DB::connection('chapter_order_mysql')->select("select appid,{$chapter_table}.distribution_channel_id,{$chapter_table}.uid,sum(fee) sum_fee,sum(charge_balance) sum_charge_balance,sum(reward_balance) sum_reward_balance from {$chapter_table} left join {$force_user_table} on {$chapter_table}.uid = {$force_user_table}.uid  where {$chapter_table}.created_at >= '{$date}' and {$chapter_table}.created_at < '{$end}'  group by {$chapter_table}.uid");
            print_r("select appid,{$chapter_table}.distribution_channel_id,{$chapter_table}.uid,sum(fee) sum_fee,sum(charge_balance) sum_charge_balance,sum(reward_balance) sum_reward_balance from {$chapter_table} left join {$force_user_table} on {$chapter_table}.uid = {$force_user_table}.uid  where {$chapter_table}.created_at >= '{$date}' and {$chapter_table}.created_at < '{$end}'  group by {$chapter_table}.uid" . "\n");

            foreach ($users as $user) {

                if(!$user->appid)
                {
                    //获取第一个appid
                    $official_account = DB::connection('chapter_order_mysql')->table('yueduyun.official_accounts')->where('distribution_channel_id',$user->distribution_channel_id)->first();
                    if($official_account)
                    {
                        $user->appid = $official_account->appid;
                    }else{
                        $user->appid = 'no';
                    }

                }
                if($user->appid)
                {
                    @$data[$user->appid]['fee'] += $user->sum_fee;
                    @$data[$user->appid]['charge_balance'] += $user->sum_charge_balance;
                    @$data[$user->appid]['reward_balance'] += $user->sum_reward_balance;
                }

            }

            foreach ($data as $appid=>$_item)
            {
                $_data = [];
                $_data['appid'] = $appid;
                $_data['fee'] = $_item['fee'];
                $_data['date'] = $date;
                $_data['charge_balance'] = $_item['charge_balance'];
                $_data['reward_balance'] = $_item['reward_balance'];
                $official_account = DB::connection('chapter_order_mysql')->table('yueduyun.official_accounts')->where('appid',$appid)->first();
                $_data['official_account_name'] = $official_account ? $official_account->nickname : '';
                DB::connection('chapter_order_mysql')->table('yueduyun.official_account_day_sub_stats')->insert($_data);
            }



            Log::info("======服务号订阅数据 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n"));
            print_r("======服务号订阅数据 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n"));


        }
    }
}