playlet/app/Service/Report/AdvertiserSe...

<?php
/**
 * Created by:PhpStorm
 * Author:chenzhiyuan
 * Date: 2022/4/23
 * Time: 8:13 下午
 */

namespace App\Service\Report;


use App\Log;
use App\Models\AuthorizeCorp;
use App\Models\DjOrder;
use App\Models\OfficialAccount;
use App\Models\Report\DjDataCycle;
use App\Models\Report\DjRegUserRangeReport;

/**
 * 广告报表关联统计
 * Class AdvertiserService
 * @package App\Service\Report
 */
class AdvertiserService
{
    /**
     * 循环统计报表
     * @param $cropid_str
     * @param $begin_date
     * @param $end_date
     * @param $page
     * @param $page_size
     * @param $sysGroupId integer
     * @return mixed
     */
    public static function user_reg_range($cropid_str,$begin_date,$end_date,$page,$page_size, $sysGroupId)
    {
        $mpAppIdList = OfficialAccount::getSysGroupMpAppIdList($sysGroupId, 'AdvertiserService.user_reg_range');

        $query = DjRegUserRangeReport::query()
            ->whereBetween("date",[$begin_date,$end_date])
            ->whereIn('mp_app_id', $mpAppIdList);
        //根据主体搜索
        $query->groupBy("date");

        $total = $query->get()->count();

        $query->select("date")
            ->selectRaw("sum(advertiser_cost) as advertiser_cost")  //当日消耗
            ->selectRaw("sum(follow_uv) as follow_uv")  //企微关注数
            ->selectRaw("round(sum(advertiser_cost)/sum(follow_uv),2) as per_follow_cost")  //企微关注成本
            ->selectRaw("sum(first_order_ucnt) as first_order_ucnt")  //下单人数
            ->selectRaw("round(sum(total_convert_amount) / sum(advertiser_cost) * 100 ,2) as total_roi")  //总回收
        ->selectRaw("sum(total_convert_amount) as total_cvt_amt");  //总回收金额

        foreach (DjRegUserRangeReport::count_range_days() as $day){
            $query->selectRaw("sum(d".$day."_cvt_amt) as day".$day."_cvt_amt");
        }

        $list = $query->offset(($page-1)*$page_size)
            ->limit($page_size)
            ->orderBy("date","desc")
            ->get();

        # 查询最大日期和最小日期
        $startDate = $list->min('date');
        $endDate = $list->max('date');
        $payUserData = DjOrder::query()->
        selectRaw("FROM_UNIXTIME(mp_user_register_time/1000, '%Y-%m-%d') as ref_date, count(distinct(`unionid`)) as pay_user_unique")
            ->where('mp_user_register_time', '>=', strtotime($startDate) * 1000)
            ->where('mp_user_register_time', '<=', strtotime($endDate.' 23:59:59') * 1000)
            ->whereRaw('FROM_UNIXTIME(mp_user_register_time/1000, "%Y-%m-%d") = FROM_UNIXTIME(order_pay_time/1000, "%Y-%m-%d")')
            ->where('pay_status', 1)
            ->whereIn('bind_app_id', $mpAppIdList)
            ->where('is_ad_user', 1)
            ->groupBy(['ref_date'])
            ->get();

        $list = $list->toArray();
        foreach ($list as $k=>$item){
            # 查询当日新增用户首单人数
            $firstDayPaidUserInfo = $payUserData->where('ref_date', $item['date'])->first();
            $firstDayPaidUserUnique = $firstDayPaidUserInfo->pay_user_unique ?? 0;

            $advertiser_cost_amount = $list[$k]['advertiser_cost'];
            foreach (DjRegUserRangeReport::count_range_days() as $day) {

                $day_cvt_amt = $list[$k]["day".$day."_cvt_amt"];
                //补充ROI
                if(strtotime($item['date'])+($day-1)*86400 > time() ){    //大于当前时间，才是空字符串
                    $list[$k]["day".$day."_roi"] = '';
                }elseif(round($day_cvt_amt)>0 && round($advertiser_cost_amount)>0){
                    $list[$k]["day".$day."_roi"] = round(round($day_cvt_amt) / round($advertiser_cost_amount)*100,2)."%";
                }else{
                    $list[$k]["day".$day."_roi"] = '0.00%';
                }
                unset($list[$k]["day".$day."_cvt_amt"]);
            }
            // 首日充值人数（去重）
            $list[$k]['pay_user_unique'] = $firstDayPaidUserUnique;
            // 企微下单成本（去重）
            $list[$k]['first_order_cost_unique'] = $firstDayPaidUserUnique ?
                round($list[$k]['advertiser_cost'] / $firstDayPaidUserUnique, 2) : '0.00';
            //企微关注数
            $list[$k]['follow_uv'] = empty( $list[$k]['follow_uv'])? '0' : $list[$k]['follow_uv'];
            //企微下单成本
            $list[$k]['per_follow_cost'] = empty( $list[$k]['per_follow_cost'])? '0' : $list[$k]['per_follow_cost'];
            //总ROi
            $list[$k]['total_roi'] = empty($list[$k]['total_roi']) ? '0.00%' : $list[$k]['total_roi']."%";
            //下单成本
            $list[$k]['first_order_cost'] = empty(round($list[$k]['advertiser_cost'])) || empty(round($list[$k]['first_order_ucnt'])) ?
                '0.00' :
                round(round($list[$k]['advertiser_cost'])/round($list[$k]['first_order_ucnt']),2);
            //总回收金额
            $list[$k]['total_cvt_amt'] = empty($list[$k]['total_cvt_amt']) ? '0.00' : $list[$k]['total_cvt_amt'];
        }


        //补充头列表
        $head = [
            ['column'=>'date'           ,'name'=>'用户注册时间' , 'notes' => ''],
            ['column'=>'advertiser_cost','name'=>'投放消耗', 'notes' => ''],
            ['column'=>'follow_uv'      ,'name'=>'企微关注数', 'notes' => ''],
            ['column'=>'per_follow_cost','name'=>'企微关注成本', 'notes' => ''],
            ['column'=>'first_order_cost','name'=>'下单成本', 'notes' => '投放消耗/当日新增用户首单人数'],
            ['column'=>'first_order_cost_unique','name'=>'下单成本(去重)', 'notes' => '投放消耗/当日新增用户首单人数(去重)'],
            ['column'=>'total_cvt_amt'      ,'name'=>'总回收金额', 'notes' => ''],
            ['column'=>'total_roi'      ,'name'=>'总回收', 'notes' => '总回收金额/投放消耗'],
        ];
        foreach (DjRegUserRangeReport::count_range_days() as $day) {
            $head[] = ['column'=>'day'.$day.'_roi'      ,'name'=>'day'.$day , 'notes' => ''];
        }
        return [$total,$list,$head];
    }

    // 循环统计报表 (2022-08-23)
    public static function userRegRangeNew(
        $beginDate,$endDate,$page,$pageSize, $sysGroupId, $firstOrderCostMin, $firstOrderCostMax, $firstOrderCostUniqueMin,
        $firstOrderCostUniqueMax, $perFollowCostMin, $perFollowCostMax, $totalRoiMin, $totalRoiMax, $firstDayRoiMin, $firstDayRoiMax)
    {
        $offset = ($page - 1) * $pageSize;

        $query = DjDataCycle::query()
            ->select("date", "advertiser_cost", "first_order_ucnt_unique", "first_order_ucnt", "follow_uv",
                "total_cvt_amt", "per_follow_cost", "total_roi", "charge_data", "first_day_charge", "first_day_roi",
                "first_order_cost", "first_order_cost_unique")
            ->where('sys_group_id', $sysGroupId)
            ->where('date', '>=', $beginDate)
            ->where('date', '<=', $endDate)
            ->where(function($query) use ($firstOrderCostMin, $firstOrderCostMax, $firstOrderCostUniqueMin, $firstOrderCostUniqueMax
            , $perFollowCostMin, $perFollowCostMax, $totalRoiMin, $totalRoiMax, $firstDayRoiMin, $firstDayRoiMax){
                if(!is_null($firstOrderCostMin)) $query->where('first_order_cost', '>=', $firstOrderCostMin);
                if(!is_null($firstOrderCostMax)) $query->where('first_order_cost', '<=', $firstOrderCostMax);
                if(!is_null($firstOrderCostUniqueMin)) $query->where('first_order_cost_unique', '>=', $firstOrderCostUniqueMin);
                if(!is_null($firstOrderCostUniqueMax)) $query->where('first_order_cost_unique', '<=', $firstOrderCostUniqueMax);
                if(!is_null($perFollowCostMin)) $query->where('per_follow_cost', '>=', $perFollowCostMin);
                if(!is_null($perFollowCostMax)) $query->where('per_follow_cost', '<=', $perFollowCostMax);
                if(!is_null($totalRoiMin)) $query->where('total_roi', '>=', $totalRoiMin);
                if(!is_null($totalRoiMax)) $query->where('total_roi', '<=', $totalRoiMax);
                if(!is_null($firstDayRoiMin)) $query->where('first_day_roi', '>=', $firstDayRoiMin);
                if(!is_null($firstDayRoiMax)) $query->where('first_day_roi', '<=', $firstDayRoiMax);
            });

        $count = $query->count();
        $data = $query->offset($offset)
            ->limit($pageSize)
            ->orderBy('date', 'desc')
            ->get()
            ->toArray();

        foreach($data as $k=>$val) {
            //总ROi
            $data[$k]['total_roi'] = empty($data[$k]['total_roi']) ? '0.00%' : ($data[$k]['total_roi'] * 100) . "%";
            // 首日roi
            $data[$k]['first_day_roi'] = empty($data[$k]['first_day_roi']) ? '0.00%' : ($data[$k]['first_day_roi'] * 100) . '%';

            $chargeData = json_decode($val['charge_data'], 1);
            foreach($chargeData as $d=>$chargePrice) {
                $day = $d+1;
                $data[$k]['day'.$day.'_roi'] = $val['advertiser_cost'] != 0 ? round($chargePrice/$val['advertiser_cost'], 4) * 100 . '%' : '0.00%';
            }
        }

        //补充头列表
        $head = [
            ['column' => 'date',                      'name' => '用户注册时间', 'notes' => ''],
            ['column' => 'advertiser_cost',           'name' => '投放消耗', 'notes' => ''],
            ['column' => 'follow_uv',                 'name' => '企微关注数', 'notes' => ''],
            ['column' => 'per_follow_cost',           'name' => '企微关注成本', 'notes' => ''],
            ['column' => 'first_order_cost',          'name' => '下单成本', 'notes' => '投放消耗/当日新增用户首单人数'],
            ['column' => 'first_order_cost_unique',   'name' => '下单成本(去重)', 'notes' => '投放消耗/当日新增用户首单人数(去重)'],
            ['column' => 'total_cvt_amt',             'name' => '总回收金额', 'notes' => ''],
            ['column' => 'total_roi',                 'name' => '总回收', 'notes' => '总回收金额/投放消耗'],
        ];

        foreach (DjRegUserRangeReport::count_range_days() as $day) {
            $head[] = ['column'=>'day'.$day.'_roi' ,  'name' => 'day'.$day , 'notes' => ''];
        }

        return [$count, $data, $head];
    }
}