duanju_manage/modules/Statistic/Http/Controllers/ChargeTJController.php

<?php

namespace Modules\Statistic\Http\Controllers;

use Catch\Base\CatchController;
use Illuminate\Foundation\Validation\ValidatesRequests;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Modules\User\Http\Controllers\UserTrait;

class ChargeTJController extends CatchController
{
    use ValidatesRequests;
    use UserTrait;

    private function _sql(Request $request) {
        $this->validate($request, [
            'start_at' => 'nullable|date_format:Y-m-d',
            'end_at' => 'nullable|date_format:Y-m-d|after_or_equal:start_at',
            'user_id' => 'nullable|integer|min:1',
            'miniprogram_id' => 'nullable|integer|min:1'
        ]);

        $startAt = $request->input('start_at');
        $endAt = $request->input('end_at');
        $userId = $request->input('user_id');
        $miniprogramId = $request->input('miniprogram_id');
        $roles = $this->listUserRoles();
        if($roles->contains('company')) {
            $puserId = $this->getLoginUserId();
        } else {
            $puserId = null;
            $userId = $this->getLoginUserId();
        }

        return  DB::table('tj_optimizer_day_charge')
            ->when($startAt, function ($query, $startAt){
                return $query->where('day_at', '>=', $startAt);
            })->when($endAt, function ($query, $endAt){
                return $query->where('day_at', '<=', $endAt);
            })->when($userId, function ($query, $userId){
                return $query->where('user_id', $userId);
            })->when($miniprogramId, function ($query, $miniprogramId) {
                return $query->where('miniprogram_id', $miniprogramId);
            })->when($puserId, function ($query, $puserId){
                return $query->where('puser_id', $puserId);
            })->orderBy('id', 'desc');
    }

    // 充值明细
    public function list(Request $request) {

        $sql = $this->_sql($request);
        $isExport = $request->input('is_export', false);

        if($isExport) {
            $result =  $sql->get();
        } else {
            $result =  $sql->paginate($request->input('limit', 15));
        }

        $users = DB::table('users')
            ->whereIn('id', $result->pluck('user_id'))
            ->select('id', 'username')
            ->get()->keyBy('id');
        $miniprograms = DB::table('miniprogram')
            ->whereIn('id', $result->pluck('miniprogram_id'))
            ->select('id','name')
            ->get()->keyBy('id');

        foreach ($result as $item) {
            $item->common_pay_money_per = $item->common_pay_uv ? bcdiv($item->common_pay_money, $item->common_pay_uv ,2 ) : 0;
            $item->vip_pay_money_per = $item->vip_pay_uv ? bcdiv($item->vip_pay_money, $item->vip_pay_uv ,2 ) : 0;
            $item->common_pay_rate = $item->common_pay_count ? bcdiv($item->common_pay_count,100 * ($item->common_pay_count + $item->common_unpay_count) ,2 ) . '%' : 0 .'%';
            $item->vip_pay_rate = $item->vip_pay_count ? bcdiv($item->vip_pay_count ,100 * ($item->vip_pay_count + $item->vip_unpay_count), 2 ) . '%' : 0 .'%';
            $item->username = $users->get($item->user_id)->username ?? '';
            $item->miniprogram_name = $miniprograms->get($item->miniprogram_id)->name ?? '';
        }

        return $result;
    }

    // 充值明细中的累计
    public function listTotalCharge(Request $request) {
        $sql = $this->_sql($request);
        $info =  $sql->select(
            DB::raw("sum(pay_money) as sum_pay_money"),
            DB::raw("sum(pay_count) as sum_pay_count"),
        )->first();
        if($info) {
            return [
                'sum_pay_money' => $info->sum_pay_money,
                'sum_pay_count' => $info->sum_pay_count,
            ];
        } else {
            return [
                'sum_pay_money' => 0,
                'sum_pay_count' => 0,
            ];
        }
    }

    // 今日充值
    public function todayCharge(Request $request) {
        $date = date('Y-m-d');
        $userId = $this->getLoginUserId();
        if($this->listUserRoles()->contains('company')) {
            $info = DB::table('orders')
                ->whereBetween('created_at', [$date, $date. ' 23:59:59'])
                ->where('puser_id', $userId)
                ->select(
                    DB::raw("sum(if(status = 'unpaid', 0, price)) as pay_money"),
                    DB::raw("sum(if(status <> 'unpaid' and order_type in ('coin', 'first_coin'), 1, 0)) as common_pay_count"),
                    DB::raw("sum(if(status = 'unpaid' and order_type in ('coin', 'first_coin'), 1, 0)) as common_unpay_count"),
                    DB::raw("sum(if(status <> 'unpaid' and order_type not in ('coin', 'first_coin'), 1, 0)) as vip_pay_count"),
                    DB::raw("sum(if(status = 'unpaid' and order_type not in ('coin', 'first_coin'), 1, 0)) as vip_unpay_count"),
                )->first();
        } else {
            $info = DB::table('orders')
                ->whereBetween('created_at', [$date, $date. ' 23:59:59'])
                ->where('user_id', $userId)
                ->where('miniprogram_id', $request->input('miniprogram_id'))
                ->select(
                    DB::raw("sum(if(status = 'unpaid', 0, price)) as pay_money"),
                    DB::raw("sum(if(status <> 'unpaid' and order_type in ('coin', 'first_coin'), 1, 0)) as common_pay_count"),
                    DB::raw("sum(if(status = 'unpaid' and order_type in ('coin', 'first_coin'), 1, 0)) as common_unpay_count"),
                    DB::raw("sum(if(status <> 'unpaid' and order_type not in ('coin', 'first_coin'), 1, 0)) as vip_pay_count"),
                    DB::raw("sum(if(status = 'unpaid' and order_type not in ('coin', 'first_coin'), 1, 0)) as vip_unpay_count"),
                )->first();
        }

        if($info) {
            $info->company_pay_rate = $info->common_pay_count ? bcdiv($info->common_pay_count,($info->common_pay_count + $info->common_unpay_count) * 100,  2) . '%' : '0%';
            $info->vip_pay_rate = $info->vip_pay_count ? bcdiv($info->vip_pay_count, ($info->vip_pay_count + $info->vip_unpay_count) * 100, 2) . '%' : '0%';
        }
        return $info;
    }
    // 本月充值
    public function currentMonthCharge(Request $request) {
        $userId = $this->getLoginUserId();
        if($this->listUserRoles()->contains('company')) {
            $info = DB::table('tj_company_day_charge')
                ->whereBetween('day_at', [date('Y-m-01'), date('Y-m-d', strtotime('yesterday'))])
                ->where('company_uid', $userId)
                ->select(
                    DB::raw("sum(pay_money) as pay_money"),
                    DB::raw("sum(common_pay_count) as common_pay_count"),
                    DB::raw("sum(common_unpay_count) as common_unpay_count"),
                    DB::raw("sum(vip_unpay_count) as vip_unpay_count"),
                    DB::raw("sum(vip_pay_count) as vip_pay_count"),
                )->first();
        } else {
            $info = DB::table('tj_optimizer_day_charge')
                ->whereBetween('day_at', [date('Y-m-01'), date('Y-m-d', strtotime('yesterday'))])
                ->where('user_id', $userId)
                ->where('miniprogram_id', $request->input('miniprogram_id'))
                ->select(
                    DB::raw("sum(pay_money) as pay_money"),
                    DB::raw("sum(common_pay_count) as common_pay_count"),
                    DB::raw("sum(common_unpay_count) as common_unpay_count"),
                    DB::raw("sum(vip_unpay_count) as vip_unpay_count"),
                    DB::raw("sum(vip_pay_count) as vip_pay_count"),
                )->first();
        }
        if($info) {
            $info->company_pay_rate = $info->common_pay_count ? bcdiv($info->common_pay_count,($info->common_pay_count + $info->common_unpay_count) * 100,  2) . '%' : '0%';
            $info->vip_pay_rate = $info->vip_pay_count ? bcdiv($info->vip_pay_count, ($info->vip_pay_count + $info->vip_unpay_count) * 100, 2) . '%' : '0%';
        }
        return $info;
    }
    // 上月充值
    public function lastMonthCharge(Request $request) {
        $userId = $this->getLoginUserId();
        if($this->listUserRoles()->contains('company')) {
            $info = DB::table('tj_company_month_charge')
                ->where('month_at', date('Y-m-d', strtotime(date('Y-m-01')) - 10))
                ->where('company_uid', $userId)
                ->select(
                    DB::raw("sum(pay_money) as pay_money"),
                    DB::raw("sum(common_pay_count) as common_pay_count"),
                    DB::raw("sum(common_unpay_count) as common_unpay_count"),
                    DB::raw("sum(vip_unpay_count) as vip_unpay_count"),
                    DB::raw("sum(vip_pay_count) as vip_pay_count"),
                )->first();
        } else {
            $info = DB::table('tj_optimizer_month_charge')
                ->where('month_at', date('Y-m-d', strtotime(date('Y-m-01')) - 10))
                ->where('user_id', $userId)
                ->where('miniprogram_id', $request->input('miniprogram_id'))
                ->select(
                    DB::raw("sum(pay_money) as pay_money"),
                    DB::raw("sum(common_pay_count) as common_pay_count"),
                    DB::raw("sum(common_unpay_count) as common_unpay_count"),
                    DB::raw("sum(vip_unpay_count) as vip_unpay_count"),
                    DB::raw("sum(vip_pay_count) as vip_pay_count"),
                )->first();
        }
        if($info) {
            $info->company_pay_rate = $info->common_pay_count ? bcdiv($info->common_pay_count,($info->common_pay_count + $info->common_unpay_count) * 100,  2) . '%' : '0%';
            $info->vip_pay_rate = $info->vip_pay_count ? bcdiv($info->vip_pay_count,($info->vip_pay_count + $info->vip_unpay_count) * 100,  2) . '%' : '0%';
        }
        return $info;
    }
    // 累计充值
    public function totalCharge(Request $request) {
        $userId = $this->getLoginUserId();
        if($this->listUserRoles()->contains('company')) {
            $info = DB::table('tj_company_month_charge')
                ->where('month_at','<=', date('Y-m-d', strtotime(date('Y-m-01')) - 10))
                ->where('company_uid', $userId)
                ->select(
                    DB::raw("sum(pay_money) as pay_money"),
                    DB::raw("sum(common_pay_count) as common_pay_count"),
                    DB::raw("sum(common_unpay_count) as common_unpay_count"),
                    DB::raw("sum(vip_unpay_count) as vip_unpay_count"),
                    DB::raw("sum(vip_pay_count) as vip_pay_count"),
                )->first();
        } else {
            $info = DB::table('tj_optimizer_month_charge')
                ->where('month_at', '<=',date('Y-m-d', strtotime(date('Y-m-01')) - 10))
                ->where('user_id', $userId)
                ->where('miniprogram_id', $request->input('miniprogram_id'))
                ->select(
                    DB::raw("sum(pay_money) as pay_money"),
                    DB::raw("sum(common_pay_count) as common_pay_count"),
                    DB::raw("sum(common_unpay_count) as common_unpay_count"),
                    DB::raw("sum(vip_unpay_count) as vip_unpay_count"),
                    DB::raw("sum(vip_pay_count) as vip_pay_count"),
                )->first();
        }

        $currentMonthInfo = $this->currentMonthCharge($request);

        $result = (object)[
            'pay_money' => $info->pay_money ?? 0 + $currentMonthInfo->pay_money ?? 0,
            'common_pay_count' => $info->common_pay_count ?? 0 + $currentMonthInfo->common_pay_count ?? 0,
            'common_unpay_count' => $info->common_unpay_count ?? 0 + $currentMonthInfo->common_unpay_count ?? 0,
            'vip_unpay_count' => $info->vip_unpay_count ?? 0 + $currentMonthInfo->vip_unpay_count ?? 0,
            'vip_pay_count' => $info->vip_pay_count ?? 0 + $currentMonthInfo->vip_pay_count ?? 0,
        ];
        $result->company_pay_rate = $result->common_pay_count ? bcdiv($result->common_pay_count,($result->common_pay_count + $result->common_unpay_count) * 100,  2) . '%' : '0%';
        $result->vip_pay_rate = $result->vip_pay_count ? bcdiv($result->vip_pay_count,($result->vip_pay_count + $result->vip_unpay_count) * 100,  2) . '%' : '0%';

        return $result;
    }
}