wanyue_app/app/admin/service/statistics/MemberService.php

<?php

namespace app\admin\service\statistics;

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

class MemberService
{
    public static function getMember()
    {
        $data = [];

        // 会员数量
        $data['memberCount'] = Member::where('member_is_owner', 'N')->where('member_mobile', '<>', '0000')->count();
        // 业主数量
        $data['ownerCount'] = Member::where('member_is_owner', 'Y')->count();
        // 用户总数
        $data['userCount'] = $data['memberCount'] + $data['ownerCount'];
        // 今日新增用户数
        $todayTime = strtotime(date('Y-m-d 00:00:00'));
        $data['todayCount'] = Member::where('member_addtimes', '>', $todayTime)->where('member_is_owner', 'N')->count();
        // 本月新增用户数
        $monthTime = strtotime(date('Y-m-01 00:00:00'));
        $monthEndTime = strtotime(date('y-m-t 23:59:59'));
        $data['monthCount'] = Member::whereBetween('member_addtimes', [$monthTime, $monthEndTime])
            ->where('member_is_owner', 'N')
            ->where('member_mobile', '<>', '0000')
            ->count();

        // 折线图-会员
        $newAddition = Db::select("
            SELECT
                DATE_FORMAT(FROM_UNIXTIME(member_addtimes), '%Y/%m') AS `month`,
                count(*) as `num` 
            FROM app_member 
            where member_is_owner='N' and member_mobile != '0000' and member_addtimes >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) and member_addtimes <= UNIX_TIMESTAMP()
            group by month 
            order by month asc ");
        $newAddition12Month = [];
        $months = month_12();
        foreach ($months as $key => $month) {
            foreach ($newAddition as $item) {
                if ($month == $item->month) {
                    $newAddition12Month[$key] = $item;
                }
            }
            if (!isset($newAddition12Month[$key])) {
                $std = new \stdClass();
                $std->month = $month;
                $std->num = 0;

                $newAddition12Month[$key] = $std;
            }
        }

        $category = [];
        foreach ($newAddition12Month as $item) {
            $category[] = $item->month;
//            $newAdditionData['member']['category'][] = $item->month;
//            $newAdditionData['member']['data'][] = $item->num;
        }
        // 折线图-业主
        $newAdditionOwner = Db::select("SELECT DATE_FORMAT(FROM_UNIXTIME(member_addtimes), '%Y/%m') AS `month`,count(*) as `num` FROM app_member where member_is_owner='Y' group by month order by month asc ");
        foreach ($newAdditionOwner as $item) {
            if (!in_array($item->month, $category)) {
                $category[] = $item->month;
            }
//            $newAdditionData['owner']['category'][] = $item->month;
//            $newAdditionData['owner']['data'][] = $item->num;
        }
        sort($category);
        $newAdditionData = [];
        foreach ($category as $item) {
            $isHaveMember = false;
            $isHaveOwner = false;
            foreach ($newAddition12Month as $item2) {
                if ($item == $item2->month) {
                    $isHaveMember = true;
                    $newAdditionData['member']['category'][] = $item;
                    $newAdditionData['member']['data'][] = $item2->num;
                }
            }
            foreach ($newAdditionOwner as $item3) {
                if ($item == $item3->month) {
                    $isHaveOwner = true;
                    $newAdditionData['owner']['category'][] = $item;
                    $newAdditionData['owner']['data'][] = $item3->num;
                }
            }
            if (!$isHaveOwner) {
                $newAdditionData['owner']['category'][] = $item;
                $newAdditionData['owner']['data'][] = 0;
            }
            if (!$isHaveMember) {
                $newAdditionData['member']['category'][] = $item;
                $newAdditionData['member']['data'][] = 0;
            }
        }

        $data['addition'] = $newAdditionData;

        // 饼图
        $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 '41-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 '61-70'
                WHEN TIMESTAMPDIFF(YEAR, member_cert_birth, CURDATE()) BETWEEN 71 AND 80 THEN '71-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['additionBing'] = $newAdditionBingData;

        return $data;
    }
}