duanju_manage/modules/Statistic/Http/Controllers/ROITJController.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 ROITJController 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',
            'ranse_id' => 'nullable|integer|min:1',
            'miniprogram_id' => 'nullable|integer|min:1'
        ]);

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

        return DB::table('tj_promotion_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($ranseId, function ($query, $ranseId) {
                return $query->where('promotion_id', $ranseId);
            })->when($miniprogramId, function ($query, $miniprogramId) {
                return $query->where('miniprogram_id', $miniprogramId);
            })->when($puserId, function ($query, $puserId) {
                return $query->where('puser_id', $puserId);
            })->when($userId, function ($query, $userId) {
                return $query->where('user_id', $userId);
            })->orderBy('id', 'desc');
    }

    // 推广链接投入产出每日列表
    public function list(Request $request)
    {
        $sql = $this->_sql($request)->select(
            'day_at', 'user_id', 'puser_id', 'new_user_num', 'new_user_pay_uv',
            'cost_money', 'new_user_pay_money', 'new_user_vip_pay_money', 'new_user_common_pay_money',
            'new_user_vip_pay_uv', 'new_user_common_pay_uv', 'miniprogram_id', 'promotion_id','id'
        );
        $isExport = $request->integer('is_export', false);
        if ($isExport) {
            $result = $sql->get();
        } else {
            $result = $sql->paginate($request->input('limit', 20));
        }
        if ($result->count()) {
            $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');
            $companyUserName = DB::table('users')
                ->where('id', $result->first()->puser_id)->value('username');
            $promotions = DB::table('promotions')
                ->whereIn('id', $result->pluck('promotion_id'))
                ->select('id', 'name')
                ->get()->keyBy('id');
            foreach ($result as $item) {
                // 商户
                $item->company_username = $companyUserName;
                // 优化师
                $item->optimizer_name = $users->get($item->user_id)->username ?? '';
                // 小程序
                $item->miniprogram_name = $miniprograms->get($item->miniprogram_id)->name ?? '';
                // 推广链接
                $item->promotion_name = $promotions->get($item->promotion_id)->name ?? '';
                // 回本
                $item->huiben = $item->cost_money !== '0.00' ? intval($item->new_user_pay_money * 10000 / $item->cost_money) / 100 . '%' : '0%';
                // 新增用户成本
                $item->new_user_cost_money = $item->new_user_num ? intval($item->cost_money * 100 / $item->new_user_num) / 100 : 0;
                // 新增用户人均充值
                $item->new_user_pay_money_per = $item->new_user_pay_uv ? intval($item->new_user_pay_money * 100 / $item->new_user_pay_uv) / 100 : 0;
                // 会员成本,累计
                $item->vip_money_per = $item->new_user_vip_pay_uv ? intval($item->cost_money * 100 / $item->new_user_vip_pay_uv) / 100 : 0;
                // 充值成本,累计
                $item->common_money_per = $item->new_user_common_pay_uv ? intval($item->cost_money * 100 / $item->new_user_common_pay_uv) / 100 : 0;
                // 会员转化率
                $item->zhuanhua_vip = $item->new_user_num ? intval($item->new_user_vip_pay_uv * 10000 / $item->new_user_num) / 100 . '%' : '0%';
                // 充值转化率
                $item->zhuanhua_common = $item->new_user_num ? intval($item->new_user_common_pay_uv * 10000 / $item->new_user_num) / 100 . '%' : '0%';
                // 总计转化率
                $item->zhuanhua_all = $item->new_user_num ? intval($item->new_user_pay_uv * 10000 / $item->new_user_num) / 100 . '%' : '0%';
            }

        }

        return $result;
    }

    // 推广链接投入产出每日列表综合
    public function listTotal(Request $request)
    {
        $sql = $this->_sql($request)->select('new_user_pay_money', 'cost_money');
        $result = $sql->select(
            DB::raw("sum(new_user_pay_money) as pay_money"),
            DB::raw("sum(cost_money) as cost_money"),
        )->first();
        if ($result) {
            return [
                'pay_money' => $result->pay_money,
                'cost_money' => $result->cost_money,
            ];
        } else {
            return [];
        }
    }

    // 更新投放成本
    public function updateCostmoney(Request $request) {
        $this->validate($request, [
            'id' => 'required',
            'cost_money' => 'required|min:0|max:1000000000'
        ]);
        DB::table('tj_promotion_day_charge')
            ->where([
                'id' => $request->input('id'),
                'user_id' => $this->getLoginUserId(),
            ])->update([
                'cost_money' => $request->input('cost_money'),
                'updated_at' => date('Y-m-d H:i:s')
            ]);

        return 'ok';
    }
}