wangdu_public_api/app/Console/Commands/UserRegisterDayStat.php

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

namespace App\Console\Commands;

use App\Modules\Statistic\Services\SendStatsEmailService;
use App\Modules\Trade\Services\OrderService;
use App\Modules\User\Models\RegisterUserDayStat;
use App\Modules\User\Models\User;
use DB;
use GuzzleHttp\Client;
use Illuminate\Console\Command;
use Log;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '获取每日注册的用户的地域信息';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        Log::info('UserRegisterDayStat begin');

        /*  for ($index = -443; $index <= -2; $index++) {
              $date = date("Y-m-d", strtotime($index . " day"));
              $this->loadInfo($date);
          }*/

        $date = date("Y-m-d", strtotime("-2 day"));
        $this->loadInfo($date);

        //如果是 周一
        if (date("l") == 'Monday') {
            $this->sendEmail();
        }
    }

    function loadInfo($date)
    {
        $begin_time = $date;
        $end_time = $begin_time . ' 23:59:59';
        $idIndex = User::getIdIndex(compact('begin_time', 'end_time'));

        if ($idIndex) {
            $page_size = 2000;
            $max_id = $idIndex->max_id;
            $min_id = $idIndex->min_id;
            echo 'max_id: ' . $max_id . "\r\n";
            echo 'min_id: ' . $min_id . "\r\n";

            $begin_index = $min_id;
            while ($begin_index < $max_id) {
                //最后一页获取剩下的条数
                if ($begin_index + $page_size > $max_id) {
                    $page_size = $max_id - $begin_index;
                }

                //如果是访问后几页的数据，id 就从上一次的后一个开始获取/**/
                if ($begin_index > $min_id) {
                    $begin_index = $begin_index + 1;
                }

                $users = DB::table('users')->where('id', '>=', $begin_index)->where('created_at', '<=', $end_time)->limit($page_size)->get();
                //获取上一次访问的用户的最大的id
                $lastUserId = $this->loadUserInfos($users, $begin_time);
                $begin_index = $lastUserId;
            }
        }
    }

    function loadUserInfos($users, $begin_time)
    {
        $userId = 0;
        foreach ($users as $user) {
            $userId = $user->id;
            $register_ip = $user->register_ip;

            $city = '未知';
            $country = '未知';
            $province = '未知';

            if ($register_ip) {
                //获取ip所在的城市
                $area = $this->getIpBelongArea($register_ip);
                if ($area) {
                    if ('未知' != $area) {
                        $country = $area[0];
                        $province = $area[1];
                        $city = $area[2];
                    }
                    //查询用户的24小时内的充值
                    $orderNum = $this->getOneDayRechargeAmount($user->id, $user->created_at);

                    $this->saveDetail($user->id, $country, $province, $city, $orderNum, $begin_time);
                    $model = RegisterUserDayStat::getRecord($begin_time, $country, $province, $city);
                    if ($model) {
                        $model->register_user_num = $model->register_user_num + 1;
                        $model->recharge_in_one_day = $model->recharge_in_one_day + $orderNum;
                        $model->arpu_in_one_day = round($model->recharge_in_one_day / $model->register_user_num, 2);
                        $model->save();
                    } else {
                        $data = ['date' => $begin_time, 'country' => $country,
                            'province' => $province, 'city' => $city,
                            'register_user_num' => 1, 'recharge_in_one_day' => $orderNum,
                            'arpu_in_one_day' => $orderNum];
                        RegisterUserDayStat::addItem($data);
                    }
                }
            }
        }
        return $userId;
    }

    public function saveDetail($uid, $country, $province, $city, $recharge_in_one_day, $date)
    {
        $created_at = date('Y-m-d H:i:s');
        $updated_at = date('Y-m-d H:i:s');
        $data = compact('uid', 'country', 'province', 'city',
            'recharge_in_one_day', 'date', 'updated_at', 'created_at');
        DB::table('promotion_day_city_user_detail_stats')->insert($data);
    }

    /**
     * 获取用户24小时内的充值
     * @param $uid 用户id
     * @param $time 开始时间
     * @return float 充值金额
     */
    function getOneDayRechargeAmount($uid, $time)
    {
        $order_benin_time = date('Y-m-d H:i:s', strtotime($time));
        $order_end_time = date('Y-m-d H:i:s', strtotime($time) + 86400);
        $order_params = ['uid' => $uid, 'begin_time' => $order_benin_time, 'end_time' => $order_end_time];
        return OrderService::getAmount($order_params);
    }

    /**
     * 获取ip 所归属的省份、城市信息
     * @param $ip ip
     * @return string  省份城市
     */
    function getIpBelongArea($ip)
    {
        $area = '未知';
        $finalIp = $this->getFinalIp($ip);
        if ($finalIp) {
            $city = new \ipip\db\City(app_path('Libs/ipip/ipipfree.ipdb'));
            try {
                $city = $city->find($ip, 'CN');
                if ($city && count($city) == 3) {
                    //$area = $city[1] . $city[2];
                    $area = $city;
                }
            } catch (\Exception $e) {
                \Log::info($e->getMessage());
            }
        }
        return $area;
    }

    function getFinalIp($ip)
    {
        $finalIp = '';
        $reg = "/(?:(?:2[0-4][0-9]\.)|(?:25[0-5]\.)|(?:1[0-9][0-9]\.)|(?:[1-9][0-9]\.)|(?:[0-9]\.)){3}(?:(?:2[0-5][0-5])|(?:25[0-5])|(?:1[0-9][0-9])|(?:[1-9][0-9])|(?:[0-9]))/";
        preg_match_all($reg, $ip, $matches);
        if ($matches && count($matches) > 0) {
            if ($matches[0] && count($matches[0] > 0)) {
                $finalIp = $matches[0][0];
            }
        }
        return $finalIp;
    }

    function sendEmail()
    {
        $begin_date = date("Y-m-d", strtotime("-8 day"));
        $end_date = date("Y-m-d", strtotime("-2 day"));
        $result = RegisterUserDayStat::getWeekTotalInfo($begin_date, $end_date);

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


            $content = "<table border='1' cellpadding='10' cellspacing='0'><tr><td align='center'>序号</td><td align='center'>地区</td><td align='center'>注册人数</td><td align='center'>注册用户24小时充值</td><td align='center'>注册用户24小时arpu值</td></tr>";
            $index = 0;
            foreach ($result as $item) {
                $index++;
                $arpu = round($item['sum_arup'], 2);
                $aera = $item['country'] . '-' . $item['province'];
                $content .= "<tr><td align='center'>{$index}</td><td align='center'>{$aera}</td><td align='center'>{$item['sum_register_user_num']}</td><td align='center'>{$item['sum_recharge_in_one_day']}</td><td align='center'>{$arpu}</td></tr>";
            }
            $content .= "</table>";
            SendStatsEmailService::SendHtmlEmailWithAcce($to_user, ['subject' => '网读云'.$begin_date . '至' . $end_date . "  注册用户信息汇总情况", 'body' => $content]);
        }
    }
}