wanyue_app/app/admin/controller/statistics/MemberController.php

<?php

namespace app\admin\controller\statistics;

use app\model\Member;
use support\Db;

class MemberController
{
    public function index()
    {
        // 会员数量
        $memberCount = Member::where('member_is_owner', 'N')->where('member_mobile','<>','0000')->count();
        // 业主数量
        $ownerCount = Member::where('member_is_owner', 'Y')->count();
        // 用户总数
        $userCount = $memberCount + $ownerCount;
        // 今日新增用户数
        $todayTime = strtotime(date('Y-m-d 00:00:00'));
        $todayCount = Member::where('member_addtimes', '>', $todayTime)->where('member_is_owner', 'N')->count();
        // 本月新增用户数
        $monthTime = strtotime(date('Y-m-01 00:00:00'));
        $monthCount = Member::where('member_addtimes', '>', $monthTime)->where('member_is_owner', 'N')->count();

        // 折线图
        $newAddition = Db::select("
                SELECT t1.month, t1.member_count,
                SUM(t2.member_count) AS member_month_count
                FROM (
                    SELECT DATE_FORMAT(FROM_UNIXTIME(member_addtimes), '%Y/%m') AS month,
                    COUNT(member_id) AS member_count
                    FROM app_member
                    GROUP BY month
                ) t1
                JOIN (
                    SELECT DATE_FORMAT(FROM_UNIXTIME(member_addtimes), '%Y/%m') AS month,
                    COUNT(member_id) AS member_count
                    FROM app_member
                    GROUP BY month
                ) t2 ON t1.month >= t2.month
                GROUP BY t1.month;
        ");
        $newAdditionData = [];
        foreach ($newAddition as $item) {
            $newAdditionData['category'][] = $item->month;
            $newAdditionData['data'][] = $item->member_count;
            $newAdditionData['month_total'][] = $item->member_month_count;
        }

        // 饼图
        $newAdditionBing = Db::select("
            SELECT CASE
                WHEN TIMESTAMPDIFF(YEAR, member_cert_birth, CURDATE()) BETWEEN 0 AND 1 THEN '0'
                WHEN TIMESTAMPDIFF(YEAR, member_cert_birth, CURDATE()) BETWEEN 1 AND 39 THEN '39'
                WHEN TIMESTAMPDIFF(YEAR, member_cert_birth, CURDATE()) BETWEEN 40 AND 50 THEN '40-50'
                WHEN TIMESTAMPDIFF(YEAR, member_cert_birth, CURDATE()) BETWEEN 51 AND 60 THEN '51-60'
                WHEN TIMESTAMPDIFF(YEAR, member_cert_birth, CURDATE()) BETWEEN 61 AND 70 THEN '60-70'
                WHEN TIMESTAMPDIFF(YEAR, member_cert_birth, CURDATE()) BETWEEN 71 AND 80 THEN '70-80'
                ELSE '81以上'
              END AS age_group,
              COUNT(*) AS number_of_people
            FROM
              app_member_cert
            WHERE member_cert_birth != ''
            GROUP BY
              age_group
            ORDER BY
              age_group;
        ");
        $newAdditionBingData = [];
        foreach ($newAdditionBing as $item) {
            if ($item->age_group == '0') {
                $item->age_group = '未知';
            }elseif ($item->age_group == '39'){
                $item->age_group = '40以下';
            }
            $newAdditionBingData[] = [
                'name' => $item->age_group,
                'value' => $item->number_of_people
            ];
        }


        $data = [
            'memberCount' => $memberCount,
            'ownerCount' => $ownerCount,
            'userCount' => $userCount,
            'todayCount' => $todayCount,
            'monthCount' => $monthCount,
            'newAdditionData' => $newAdditionData,
            'newAdditionBingData' => $newAdditionBingData
        ];

        return json_success('', $data);
    }
}