zhuishuyun_quickapp/app/Console/Commands/Sub/GenerateCpSubDayStat.php

<?php
/**
 * Created by PhpStorm.
 * User: songdb
 * Date: 2017/12/26
 * Time: 下午5:26
 */

namespace App\Console\Commands\Sub;

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

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'CP日订阅统计数据生成';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        print_r("======CP日订阅统计数据生成 【任务执行开始】=====".date("y-m-d H:i:s"."\n"));

        Log::info("======CP日订阅统计数据生成 【任务执行开始】=====".date("y-m-d H:i:s"."\n"));

        $cps = [
            'mianhua','guijj'
        ];

        $end_date = "2018-01-01";

        foreach ($cps as $cp)
        {
            $data = [];
            //取cp下所有bid
            $books = DB::select("select bid,books.name from books,book_configs where books.id = book_configs.bid and cp_source = '{$cp}' ");

            dump('books');dump($books);

            $book_data = [];
            foreach ($books as $book)
            {
                $book_data[$book->bid] = $book->name;
                $data[$book->bid]['book_name'] = $book->name;
                $data[$book->bid]['count'] = 0;
                $data[$book->bid]['charge_balance'] = 0;
                $data[$book->bid]['book_count'] = 0;
                $data[$book->bid]['book_charge_balance'] = 0;
            }

            dump('book_data');dump($book_data);

            $bids = implode(',',array_keys($book_data));

            dump('bids');dump($bids);

            //遍历所有订阅表查寻章节订购数、金额
            for($i=0;$i<512;$i++)
            {
                $table_datas = DB::connection('chapter_order_mysql')->select("select bid,count(1) count,sum(charge_balance) charge_balance from chapter_orders{$i} where created_at <'{$end_date}' and bid in ({$bids}) group by bid");

                foreach ($table_datas as $item)
                {
                    $data[$item->bid]['count'] += $item->count;
                    $data[$item->bid]['charge_balance'] += $item->charge_balance;
                }
            }

            $charge_book_data = DB::select("select bid,count(1) count,sum(charge_balance) charge_balance from book_orders where created_at <'{$end_date}' and bid in ({$bids}) group by bid");
            foreach ($charge_book_data as $item_book)
            {
                $data[$item_book->bid]['book_count'] += $item_book->count;
                $data[$item_book->bid]['book_charge_balance'] += $item_book->charge_balance;
            }

            foreach ($data as $bid=>$_item)
            {
                $_data = [
                    'date'=>'2017-12-12',
                    'cp_name'=>$cp,
                    'bid'=>$bid,
                    'book_name'=>$book_data[$bid],
                    'book_name'=>$book_data[$bid],
                    'chapter_sub_num'=>$_item['count'],
                    'chapter_sub_amount'=>$_item['charge_balance'],
                    'book_sub_num'=>$_item['book_count'],
                    'book_sub_amount'=>$_item['book_charge_balance']
                ];
                DB::table('cp_day_subs')->insert($_data);
            }

        }


        Log::info("======CP日订阅统计数据生成 【任务执行结束】=====".date("y-m-d H:i:s"."\n"));
        print_r("======CP日订阅统计数据生成 【任务执行结束】=====".date("y-m-d H:i:s"."\n"));
    }
}