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

<?php

namespace app\admin\controller\statistics;

use app\model\Member;
use app\model\MemberCert;
use app\model\MemberRole;
use app\model\PayDetail;
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
                    WHERE member_mobile != '0000'
                    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
                    WHERE member_mobile != '0000'
                    GROUP BY month
                ) t2 ON t1.month >= t2.month
                GROUP BY t1.month;
        ");
        dump($newAddition);
        $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
            ];
        }
        // 认证数
        $authCount = MemberCert::where('member_cert_nbr', '<>', '')->count();
        $unAuthCount = $memberCount - $authCount;
        $authBingData = [
            [
                'name' => '已认证',
                'value' => $authCount
            ],
            [
                'name' => '未认证',
                'value' => $unAuthCount
            ]
        ];
        // 各身份数量
        $memberIdentityBingData = [];
        $basicMemberCount = $memberCount;
        // 1.等级
        $memberRoles = MemberRole::select('member_role_id', 'member_role_name')->get()->toArray();
        foreach ($memberRoles as $role) {
            $memberRoleCount = Member::where('join_member_role_id', $role['member_role_id'])->count();
            if ($memberRoleCount < 1) {
                continue;
            }
            $basicMemberCount -= $memberRoleCount;

            $isHave = false;
            foreach ($memberIdentityBingData as $key => $item) {
                if ($item['name'] == $role['member_role_name']) {
                    $isHave = true;
                    $memberIdentityBingData[$key]['value'] += $memberRoleCount;
                }
            }
            if (!$isHave) {
                $memberIdentityBingData[] = [
                    'name' => $role['member_role_name'],
                    'value' => $memberRoleCount
                ];
            }
        }
        // 2.康养城会员
        $vipCount = Member::where('member_is_vip', 'Y')->count();
        $basicMemberCount -= $vipCount;
        $memberIdentityBingData[] = [
            'name' => '康养城VIP',
            'value' => $vipCount
        ];
        // 3.会员合伙人
        $partnerCount = Member::where('member_is_partner', 'Y')->count();
        $basicMemberCount -= $partnerCount;
        $memberIdentityBingData[] = [
            'name' => '会员合伙人',
            'value' => $partnerCount
        ];
        // 4.康养推荐官
        $referrerCount = Member::where('member_is_referrer', 'Y')->count();
        $basicMemberCount -= $referrerCount;
        $memberIdentityBingData[] = [
            'name' => '康养推荐官',
            'value' => $referrerCount
        ];
        // 5.加盟合伙人
        $franchiseeCount = Member::where('member_is_franchisee', 'Y')->count();
        $basicMemberCount -= $franchiseeCount;
        $memberIdentityBingData[] = [
            'name' => '加盟合伙人',
            'value' => $franchiseeCount
        ];
        $memberIdentityAllBingData = $memberIdentityBingData;
        $memberIdentityAllBingData[] = [
            'name' => '普通会员',
            'value' => $basicMemberCount
        ];

        // 消费账户占比
        $memberConsumptionBingData = [
            ['name' => '未消费', 'value' => 0],
            ['name' => '1-500', 'value' => 0],
            ['name' => '501-1000', 'value' => 0],
            ['name' => '1001-5000', 'value' => 0],
            ['name' => '5001-10000', 'value' => 0],
            ['name' => '10001-50000', 'value' => 0],
            ['name' => '50000以上', 'value' => 0],
        ];
        $memberConsumptionCount = $memberCount;
        // 散客ID
        $sankeMemberId = Member::where('member_mobile', '0000')->value('member_id');
        $payDetails = PayDetail::selectRaw('SUM(pay_amount) as amount,join_pay_member_id')
            ->where('pay_amount', '>', 0)
            ->where('pay_status', 'SUCCESS')
            ->where('join_pay_member_id', '<>', $sankeMemberId)
            ->whereIn('pay_category', ['GOODS', 'SERVICE', 'CHNMED', 'CHNNCD', 'MEALS', 'PACKAGE'])
            ->groupBy('join_pay_member_id')
            ->get()
            ->toArray();
        foreach ($payDetails as $detail) {
            $memberConsumptionCount -= 1;
            if ($detail['amount'] >= 1 && $detail['amount'] <= 500 && isset($memberConsumptionBingData[1])) {
                $memberConsumptionBingData[1]['value'] += 1;
            } elseif ($detail['amount'] >= 501 && $detail['amount'] <= 1000 && isset($memberConsumptionBingData[2])) {
                $memberConsumptionBingData[2]['value'] += 1;
            } elseif ($detail['amount'] >= 1001 && $detail['amount'] <= 5000 && isset($memberConsumptionBingData[3])) {
                $memberConsumptionBingData[3]['value'] += 1;
            } elseif ($detail['amount'] >= 5001 && $detail['amount'] <= 10000 && isset($memberConsumptionBingData[4])) {
                $memberConsumptionBingData[4]['value'] += 1;
            } elseif ($detail['amount'] >= 10001 && $detail['amount'] <= 50000 && isset($memberConsumptionBingData[5])) {
                $memberConsumptionBingData[5]['value'] += 1;
            } elseif ($detail['amount'] >= 50001 && isset($memberConsumptionBingData[6])) {
                $memberConsumptionBingData[6]['value'] += 1;
            }
        }
        if ($memberConsumptionBingData[6]['value'] == 0) {
            unset($memberConsumptionBingData[6]);
        }
        $memberConsumptionBingData[0]['value'] = $memberConsumptionCount;

        // 充值
        $rechargeBingData = [];
        $rechargePaydetail = PayDetail::whereIn('pay_category', ['FRANCHISEE', 'RECHARGE', 'REFERRER', 'PARTNER', 'COMBINE'])
            ->where('pay_status', 'SUCCESS')
            ->where('join_pay_order_id', 'like', '%OD%');
        $recharge1Model = clone $rechargePaydetail;
        $rechargeBingData[] = [
            'name' => '1-1000',
            'value' => $recharge1Model->whereBetween('pay_amount', [1, 1000])->count()
        ];

        $recharge1Model = clone $rechargePaydetail;
        $rechargeBingData[] = [
            'name' => '1001-5000',
            'value' => $recharge1Model->whereBetween('pay_amount', [1001, 5000])->count()
        ];

        $recharge1Model = clone $rechargePaydetail;
        $rechargeBingData[] = [
            'name' => '5001-10000',
            'value' => $recharge1Model->whereBetween('pay_amount', [5001, 10000])->count()
        ];

        $recharge1Model = clone $rechargePaydetail;
        $rechargeBingData[] = [
            'name' => '10001-50000',
            'value' => $recharge1Model->whereBetween('pay_amount', [10001, 50000])->count()
        ];

        $recharge1Model = clone $rechargePaydetail;
        $rechargeBingData[] = [
            'name' => '50000以上',
            'value' => $recharge1Model->where('pay_amount', '>', 50000)->count()
        ];

        $data = [
            'memberCount' => $memberCount,
            'ownerCount' => $ownerCount,
            'userCount' => $userCount,
            'todayCount' => $todayCount,
            'monthCount' => $monthCount,
            'newAdditionData' => $newAdditionData,
            'newAdditionBingData' => $newAdditionBingData,
            'authBingData' => $authBingData,
            'memberIdentityInfoBingData' => $memberIdentityBingData,
            'memberIdentityAllBingData' => $memberIdentityAllBingData,
            'memberConsumptionBingData' => $memberConsumptionBingData,
            'rechargeBingData' => $rechargeBingData
        ];

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