4d_admin_backend/app/Http/Services/Admin/Task/AgentStatisticService.php

<?php
/**
 * @Name
 * @Description
 * @Author 刘学玺
 * @Date 2023/11/23 20:57
 */

namespace App\Http\Services\Admin\Task;

use App\Http\Services\BaseService;
use App\Models\Menu;
use App\Models\Order;
use App\Models\StatisticIncome;
use Illuminate\Support\Facades\DB;

class AgentStatisticService extends BaseService
{
    public function daily($data)
    {
        try {
            if (empty($data['date']))
                $yesterday = date('Y-m-d', strtotime("-1 day")); // 获取昨天日期
            else
                $yesterday = $data['date'];
            $startTime = strtotime($yesterday . ' 00:00:00'); // 设置开始时间为昨天0点
            $endTime = strtotime($yesterday . ' 23:59:59'); // 设置结束时间为昨天24点
            $order = DB::table('js_order as order')
                ->whereBetween('create_time', [$startTime, $endTime])
                ->whereIn('order.status', ['3', '4'])
                ->select(['province', 'city', 'district'])
//                ->selectRaw("DATE_FORMAT(MAX(create_time), '%Y-%m-%d') as time")
                ->selectRaw("DATE_FORMAT(FROM_UNIXTIME(MAX(create_time)), '%Y-%m-%d') as time")
                ->selectRaw("COUNT(*) as order_count, SUM(pay_price) as pay_price")
                ->selectRaw("SUM(xh_user_money_log.money) as artificer_income")
                ->selectRaw("SUM(pay_price - IFNULL(xh_user_money_log.money, 0) - IFNULL(xh_promotion_log.money, 0)) as total_income")
                ->selectRaw("SUM(pay_price - IFNULL(xh_user_money_log.money, 0) - IFNULL(xh_promotion_log.money, 0)) * 0.4 as platform_income")
                ->selectRaw("SUM(pay_price - IFNULL(xh_user_money_log.money, 0) - IFNULL(xh_promotion_log.money, 0)) * 0.4 as agent_income")
                ->selectRaw("SUM(pay_price - IFNULL(xh_user_money_log.money, 0) - IFNULL(xh_promotion_log.money, 0)) * 0.2 as market_income")
                ->selectRaw("SUM(IFNULL(xh_promotion_log.money, 0)) as promotion_income")
                ->leftJoin('user_money_log as user_money_log', function ($join) {
//                    $join->on('user_money_log.order_sn', '=', 'order.order_sn')->where('user_money_log.type', 8);
                    $join->on('user_money_log.obj_id', '=', 'order.id')->where('user_money_log.type', 8);
                })
                ->leftJoin('user_money_log as promotion_log', function ($join) {
                    $join->on('promotion_log.obj_id', '=', 'order.id')
                        ->where('promotion_log.is_type', 1)
                        ->whereIn('promotion_log.type', [3, 4]);
                })
                ->groupBy(['province', 'city', 'district'])
                ->get()->map(function ($value) {
                    return (array)$value;
                })->toArray();
            StatisticIncome::query()->insert($order);
            return $yesterday . '-统计完成';
        } catch (\Exception $e) {
            return $yesterday . '-统计失败';
        }
    }

}