msy-prod/app/Console/Commands/Data/AdvertsCommand.php

<?php

namespace App\Console\Commands\Data;

use App\Exports\FormExport;
use App\Models\Fpdx\ActivityModel;
use App\Models\Fpdx\PairModel;
use App\Models\OrderModel;
use App\Models\User\UserModel;
use Illuminate\Console\Command;
use Maatwebsite\Excel\Facades\Excel;

class AdvertsCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'data:adverts';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '接单的数据';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //
        $stage_id = $this->ask('选择期数?');
        $activity = ActivityModel::find($stage_id);
        $begin_at = $activity->signend_time - 66 * 3600;
        $end_at = $activity->signend_time;
//        $public_id = 'gh_c94c95866ca5';
        $public_id = 'gh_b598cb7474d8';


        $openids = \DB::table('kddx_user_openid')
            ->where('public_id', $public_id)
            ->whereBetween('subscribe_time', [$begin_at, $end_at])
            ->pluck('openid');


        $users = \DB::table('kddx_user_openid')
            ->whereIn('openid', $openids)
            ->where('uid', '>', 0)
            ->pluck('uid');


        // 用户数据
        $number = $openids->count();
        $subscribe_number = \DB::table('kddx_user_openid')
            ->whereIn('openid', $openids)
            ->where('subscribe', 1)->count();
        $reg_number = $users->count();
        $phone_number = UserModel::whereIn('uid', $users)->whereNotNull('phone')->count();


        // 活动数据
        $pair_number = PairModel::whereBetween('create_time', [$begin_at, $end_at])->where(
            'state',
            '>',
            100
        )->whereIn('uid', $users)->count();
        $pair_man_number = PairModel::whereBetween('create_time', [$begin_at, $end_at])->where(
            'state',
            '>',
            100
        )->whereIn('uid', $users)->where('sex', 1)->count();
        $pair_woman_number = PairModel::whereBetween('create_time', [$begin_at, $end_at])->where(
            'state',
            '>',
            100
        )->whereIn('uid', $users)->where('sex', 2)->count();
        $pair_order_number = PairModel::whereBetween('create_time', [$begin_at, $end_at])->where(
            'state',
            '>',
            100
        )->whereIn('uid', $users)->where('pay', '>', 0)->count();
        $pair_man_order_number = PairModel::whereBetween('create_time', [$begin_at, $end_at])->where(
            'state',
            '>',
            100
        )->whereIn('uid', $users)->where('pay', '>', 0)->where('sex', 1)->count();
        $pair_woman_order_number = PairModel::whereBetween('create_time', [$begin_at, $end_at])->where(
            'state',
            '>',
            100
        )->whereIn('uid', $users)->where('pay', '>', 0)->where('sex', 2)->count();
        $pair_order_amount = PairModel::whereBetween('create_time', [$begin_at, $end_at])->where(
            'state',
            '>',
            100
        )->whereIn('uid', $users)->where('pay', '>', 0)->sum('pay');
        $pair_man_order_amount = PairModel::whereBetween('create_time', [$begin_at, $end_at])->where(
            'state',
            '>',
            100
        )->whereIn('uid', $users)->where('pay', '>', 0)->where('sex', 1)->sum('pay');
        $pair_woman_order_amount = PairModel::whereBetween('create_time', [$begin_at, $end_at])->where(
            'state',
            '>',
            100
        )->whereIn('uid', $users)->where('pay', '>', 0)->where('sex', 2)->sum('pay');


        dump("全部人数：{$number}");
        dump("当前关注人数：{$subscribe_number}");
        dump("注册人数：{$reg_number}");
        dump("绑定手机号人数：{$phone_number}");
        dump("报名人数：{$pair_number}");
        dump("男生报名人数：{$pair_man_number}");
        dump("女生报名人数：{$pair_woman_number}");
        dump("付费报名人数：{$pair_order_number}");
        dump("男生付费报名人数：{$pair_man_order_number}");
        dump("女生付费报名人数：" . $pair_woman_order_number);
        dump("付费报名金额：" . sprintf("%.2f", $pair_order_amount));
        dump("男生付费报名金额：" . sprintf("%.2f", $pair_man_order_amount));
        dump("女生付费报名金额：" . sprintf("%.2f", $pair_woman_order_amount));


        // 消费数据
        $vip_order_amount = OrderModel::where('order_state', 1)->whereBetween(
            'create_at',
            [$begin_at, $end_at]
        )->whereIn('uid', $users)->whereIn('type', [12, 13, 14])->sum('cash_fee');
        $pair_order_amount = OrderModel::where('order_state', 1)->whereBetween(
            'create_at',
            [$begin_at, $end_at]
        )->whereIn('uid', $users)->whereIn('type', [3])->sum('cash_fee');
        dump("vip充值：" . sprintf("%.2f", $vip_order_amount / 100));
        dump("活动充值：" . sprintf("%.2f", $pair_order_amount / 100));


        // 粉丝数据
        $new_user = \DB::table('koudai.kddx_user_openid')->where('public_id', $public_id)
            ->selectRaw("from_unixtime(`subscribe_time`, '%Y-%m-%d') as date, count(*) as count")
            ->whereBetween('subscribe_time', [$begin_at, $end_at])
            ->groupBy("date")
            ->get()
            ->toArray();
        $increase_user = \DB::table('koudai.kddx_user_openid')
            ->where('public_id', $public_id)
            ->selectRaw("from_unixtime(`subscribe_time`, '%Y-%m-%d') as date, count(*) as count")
            ->whereBetween('subscribe_time', [$begin_at, $end_at])
            ->where('subscribe', 1)
            ->groupBy("date")
            ->get()
            ->toArray();

        dump("公众号新增用户：");
        dump($new_user);
        dump("公众号净增用户：");
        dump($increase_user);


        $new_user = \DB::select("SELECT from_unixtime(e.`subscribe_time`, '%Y-%m-%d') as date,
                       count(*) as count
                  FROM(
                SELECT `unionid`, `subscribe_time`
                  FROM `pocket`.`kdgx_fans_5`
                 WHERE `public_id`= 'gh_c94c95866ca5'
                   AND `subscribe_time` between ?
                   and ?) as e
                  INNER JOIN(
                SELECT `unionid`
                  FROM `pocket`.`kdgx_fans_8`
                 WHERE `public_id`= 'gh_b598cb7474d8'
                   AND `subscribe`= 1) as f on e.unionid= f.unionid
                 GROUP BY date", [$begin_at, $end_at]);

        $increase_user = \DB::select("SELECT from_unixtime(e.`subscribe_time`, '%Y-%m-%d') as date,
                       count(*) as count
                  FROM(
                SELECT `unionid`, `subscribe_time`
                  FROM `pocket`.`kdgx_fans_5`
                 WHERE `public_id`= 'gh_c94c95866ca5'
                   AND `subscribe_time` between ?
                   and ?
                   AND `subscribe`= 1) as e
                  INNER JOIN(
                SELECT `unionid`
                  FROM `pocket`.`kdgx_fans_8`
                 WHERE `public_id`= 'gh_b598cb7474d8'
                   AND `subscribe`= 1) as f on e.unionid= f.unionid
                 GROUP BY date", [$begin_at, $end_at]);

        dump("时遇记新增用户&关注分配对象：");
        dump($new_user);
        dump("时遇记净增用户&关注分配对象：");
        dump($increase_user);
        // 小程序分享
        $share_number = \DB::table("koudai.kdgx_partner_charge_fpdx_invite")
            ->selectRaw("from_unixtime(`create_at`, '%Y-%m-%d') as date, count(distinct(invite_uid)) as count")
            ->whereBetween("create_at", [$begin_at, $end_at])
            ->whereIn("invite_uid", $users)
            ->groupBy('date')
            ->get()
            ->toArray();

        $share_count = \DB::table("koudai.kdgx_partner_charge_fpdx_invite")
            ->selectRaw("from_unixtime(`create_at`, '%Y-%m-%d') as date, count(*) as count")
            ->whereBetween("create_at", [$begin_at, $end_at])
            ->whereIn("invite_uid", $users)
            ->groupBy('date')
            ->get()
            ->toArray();
        dump("分享人数：");
        dump($share_number);

        dump("分享次数：");
        dump($share_count);
    }
}