wangdu_public_api/app/Console/Commands/SendOrder/GenerateBookUvRegisterStat.php

<?php
/**
 * Created by PhpStorm.
 * User: sogndb
 * Date: 2018/02/05
 * Time: 下午5:26
 */

namespace App\Console\Commands\SendOrder;

use App\Modules\Statistic\Services\SendStatsEmailService;
use DB;
use Illuminate\Console\Command;
use Log;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '内外部派单按书累计数据';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $date_end = date("Y-m-d");
        $date_begin = date("Y-m-d", strtotime(' -1 day'));


        $base_sql = "SELECT book_name,bid,sum(uv) as total_uv, sum(register_user_num) total_register_user_num 
        FROM send_orders_stats WHERE send_time BETWEEN '%s' AND '%s'";
        //内部
        $inside_sql = $base_sql . "AND force_user_num<20 GROUP BY bid ORDER BY total_uv desc LIMIT 10";
        $inside_result = DB::select(sprintf($inside_sql, $date_begin, $date_end));

        //外部
        $outside_sql = $base_sql . "AND force_user_num>=20  GROUP BY bid ORDER BY total_register_user_num desc LIMIT 10";
        $outside_result = DB::select(sprintf($outside_sql, $date_begin, $date_end));

        $to_user = array(
            ['address' => 'songdb@iqiyoo.com', 'name' => 'songdb'],
            ['address' => 'zhaojp@yqsd.net', 'name' => '赵君平'],
            ['address' => 'zhoulj@iqiyoo.com', 'name' => '周灵杰'],
        );

        $content ='';
        if ($inside_result) {
            $content .= "<table border='1' cellpadding='10' cellspacing='0'><tr><td align='center'>类型</td><td align='center'>书本id</td><td align='center'>书名</td><td align='center'>总UV</td><td align='center'>总注册用户数</td></tr>";
            foreach ($inside_result as $item) {
                $content .= "<tr><td align='center'>内部派单</td><td align='center'>{$item->bid}</td><td align='center'>{$item->book_name}</td><td align='center'>{$item->total_uv}</td><td align='center'>{$item->total_register_user_num}</td></tr>";
            }
            $content .= "</table>";
        }

        $content.='</hr>';
        if ($outside_result) {
            $content .= "<table border='1' cellpadding='10' cellspacing='0'><tr><td align='center'>类型</td><td align='center'>书本id</td><td align='center'>书名</td><td align='center'>总UV</td><td align='center'>总注册用户数</td></tr>";
            foreach ($outside_result as $item) {
                $content .= "<tr><td align='center'>外部派单</td><td align='center'>{$item->bid}</td><td align='center'>{$item->book_name}</td><td align='center'>{$item->total_uv}</td><td align='center'>{$item->total_register_user_num}</td></tr>";
            }
            $content .= "</table>";
        }
        SendStatsEmailService::SendHtmlEmailWithAcce($to_user, ['subject' => '网读云'.date("Y-m-d", time()) . "内外部派单按书统计数据", 'body' => $content]);
    }
}