influence/backend/app/Controllers/Dashboard.php

<?php

namespace App\Controllers;

use CodeIgniter\RESTful\ResourceController;

class Dashboard extends ResourceController
{
    // 최근 들어온 주문 현황 조회
    public function recentOrders()
    {
        // 한국 시간으로 설정
        date_default_timezone_set('Asia/Seoul');
        
        $db = \Config\Database::connect();
        $request = $this->request->getJSON(true);
        
        $companyNumber = isset($request['COMPANY_NUMBER']) ? $request['COMPANY_NUMBER'] : null;
        $memberType = isset($request['MEMBER_TYPE']) ? $request['MEMBER_TYPE'] : null;
        $memberSeq = isset($request['MEMBER_SEQ']) ? $request['MEMBER_SEQ'] : null;
        $limit = isset($request['LIMIT']) ? $request['LIMIT'] : 10;

        try {
            $builder = $db->table('DELI_LIST D')
                ->select('D.*, I.NAME as ITEM_NAME, I.COMPANY_NUMBER')
                ->join('ITEM_LIST I', 'D.ITEM_SEQ = I.SEQ', 'left')
                ->where('D.DEL_YN', 'N')
                ->orderBy('D.REGDATE', 'DESC')
                ->limit($limit);

            // 사용자 타입별 필터링
            if ($memberType === 'VENDOR' && !empty($companyNumber)) {
                $builder->where('I.COMPANY_NUMBER', $companyNumber);
            } elseif ($memberType === 'BRAND' && !empty($memberSeq)) {
                $builder->where('I.CONTACT_BRD', $memberSeq);
            } elseif ($memberType === 'INFLUENCER' && !empty($memberSeq)) {
                $builder->where('I.CONTACT_INF', $memberSeq);
            }

            $orders = $builder->get()->getResultArray();

            return $this->respond($orders, 200);
        } catch (\Exception $e) {
            return $this->respond([
                'status' => 'fail',
                'message' => 'DB 오류: ' . $e->getMessage()
            ], 500);
        }
    }

    // 진행중인 공동구매 리스트 조회
    public function activeItems()
    {
        // 한국 시간으로 설정
        date_default_timezone_set('Asia/Seoul');
        
        $db = \Config\Database::connect();
        $request = $this->request->getJSON(true);
        
        $companyNumber = isset($request['COMPANY_NUMBER']) ? $request['COMPANY_NUMBER'] : null;
        $memberType = isset($request['MEMBER_TYPE']) ? $request['MEMBER_TYPE'] : null;
        $memberSeq = isset($request['MEMBER_SEQ']) ? $request['MEMBER_SEQ'] : null;
        $limit = isset($request['LIMIT']) ? $request['LIMIT'] : 10;

        try {
            $today = date('Y-m-d');
            
            $builder = $db->table('ITEM_LIST I')
                ->select('I.*, COALESCE(V.COMPANY_NAME, B.COMPANY_NAME) as COMPANY_NAME, 
                         (SELECT COUNT(*) FROM DELI_LIST D WHERE D.ITEM_SEQ = I.SEQ AND D.DEL_YN = "N") as ORDER_COUNT')
                ->join('VENDOR_LIST V', 'I.COMPANY_NUMBER = V.COMPANY_NUMBER', 'left')
                ->join('BRAND_LIST B', 'I.COMPANY_NUMBER = B.COMPANY_NUMBER', 'left')
                ->where('I.DEL_YN', 'N')
                ->where('I.STATUS', 0) // 진행중
                ->where('I.SHOW_YN', 'Y')
                ->where('I.TYPE', 'GROUP_BUY') // 공동구매만
                ->where('I.ORDER_END_DATE >=', $today) // 마감일이 지나지 않은 것만
                ->orderBy('I.ORDER_END_DATE', 'ASC')
                ->limit($limit);

            // 사용자 타입별 필터링
            if ($memberType === 'VENDOR' && !empty($companyNumber)) {
                $builder->where('I.COMPANY_NUMBER', $companyNumber);
            } elseif ($memberType === 'BRAND' && !empty($memberSeq)) {
                $builder->where('I.CONTACT_BRD', $memberSeq);
            } elseif ($memberType === 'INFLUENCER' && !empty($memberSeq)) {
                $builder->where('I.CONTACT_INF', $memberSeq);
            }

            $items = $builder->get()->getResultArray();

            return $this->respond($items, 200);
        } catch (\Exception $e) {
            return $this->respond([
                'status' => 'fail',
                'message' => 'DB 오류: ' . $e->getMessage()
            ], 500);
        }
    }

    // 인플루언서별 판매된 주문 건수 통계
    public function influencerStats()
    {
        // 한국 시간으로 설정
        date_default_timezone_set('Asia/Seoul');
        
        $db = \Config\Database::connect();
        $request = $this->request->getJSON(true);
        
        $companyNumber = isset($request['COMPANY_NUMBER']) ? $request['COMPANY_NUMBER'] : null;
        $memberType = isset($request['MEMBER_TYPE']) ? $request['MEMBER_TYPE'] : null;
        $memberSeq = isset($request['MEMBER_SEQ']) ? $request['MEMBER_SEQ'] : null;
        $limit = isset($request['LIMIT']) ? $request['LIMIT'] : 10;

        try {
            $builder = $db->table('DELI_LIST D')
                ->select('I.CONTACT_INF as INF_SEQ, INF.NICK_NAME as INF_NAME, COUNT(D.SEQ) as ORDER_COUNT')
                ->join('ITEM_LIST I', 'D.ITEM_SEQ = I.SEQ', 'inner')
                ->join('INFLUENCER_LIST INF', 'I.CONTACT_INF = INF.SEQ', 'left')
                ->where('D.DEL_YN', 'N')
                ->where('I.DEL_YN', 'N')
                ->where('I.CONTACT_INF >', 0) // 인플루언서가 배정된 항목만
                ->groupBy('I.CONTACT_INF, INF.NICK_NAME')
                ->orderBy('ORDER_COUNT', 'DESC')
                ->limit($limit);

            // 사용자 타입별 필터링
            if ($memberType === 'VENDOR' && !empty($companyNumber)) {
                $builder->where('I.COMPANY_NUMBER', $companyNumber);
            } elseif ($memberType === 'BRAND' && !empty($memberSeq)) {
                $builder->where('I.CONTACT_BRD', $memberSeq);
            }

            $stats = $builder->get()->getResultArray();

            return $this->respond($stats, 200);
        } catch (\Exception $e) {
            return $this->respond([
                'status' => 'fail',
                'message' => 'DB 오류: ' . $e->getMessage()
            ], 500);
        }
    }

    // 대시보드 전체 통계 요약
    public function summary()
    {
        // 한국 시간으로 설정
        date_default_timezone_set('Asia/Seoul');
        
        $db = \Config\Database::connect();
        $request = $this->request->getJSON(true);
        
        $companyNumber = isset($request['COMPANY_NUMBER']) ? $request['COMPANY_NUMBER'] : null;
        $memberType = isset($request['MEMBER_TYPE']) ? $request['MEMBER_TYPE'] : null;
        $memberSeq = isset($request['MEMBER_SEQ']) ? $request['MEMBER_SEQ'] : null;

        try {
            $today = date('Y-m-d');
            $weekAgo = date('Y-m-d', strtotime('-7 days'));

            // 기본 조건 설정
            $whereConditions = ['I.DEL_YN' => 'N'];
            
            if ($memberType === 'VENDOR' && !empty($companyNumber)) {
                $whereConditions['I.COMPANY_NUMBER'] = $companyNumber;
            } elseif ($memberType === 'BRAND' && !empty($memberSeq)) {
                $whereConditions['I.CONTACT_BRD'] = $memberSeq;
            } elseif ($memberType === 'INFLUENCER' && !empty($memberSeq)) {
                $whereConditions['I.CONTACT_INF'] = $memberSeq;
            }

            // 최근 7일 주문 수
            $recentOrdersCount = $db->table('DELI_LIST D')
                ->join('ITEM_LIST I', 'D.ITEM_SEQ = I.SEQ', 'inner')
                ->where('D.DEL_YN', 'N')
                ->where('D.REGDATE >=', $weekAgo . ' 00:00:00')
                ->where($whereConditions)
                ->countAllResults();

            // 진행중인 공동구매 수
            $activeItemsCount = $db->table('ITEM_LIST I')
                ->where('I.STATUS', 0)
                ->where('I.SHOW_YN', 'Y')
                ->where('I.TYPE', 'GROUP_BUY')
                ->where('I.ORDER_END_DATE >=', $today)
                ->where($whereConditions)
                ->countAllResults();

            // 총 주문 건수
            $totalSalesCount = $db->table('DELI_LIST D')
                ->join('ITEM_LIST I', 'D.ITEM_SEQ = I.SEQ', 'inner')
                ->where('D.DEL_YN', 'N')
                ->where($whereConditions)
                ->countAllResults();

            // 활성 인플루언서 수 (주문이 있는 인플루언서)
            $activeInfluencersCount = $db->table('DELI_LIST D')
                ->select('I.CONTACT_INF')
                ->join('ITEM_LIST I', 'D.ITEM_SEQ = I.SEQ', 'inner')
                ->where('D.DEL_YN', 'N')
                ->where('I.CONTACT_INF >', 0)
                ->where($whereConditions)
                ->distinct()
                ->countAllResults();

            $summary = [
                'recent_orders_count' => $recentOrdersCount,
                'active_items_count' => $activeItemsCount,
                'total_sales_count' => $totalSalesCount,
                'active_influencers_count' => $activeInfluencersCount
            ];

            return $this->respond($summary, 200);
        } catch (\Exception $e) {
            return $this->respond([
                'status' => 'fail',
                'message' => 'DB 오류: ' . $e->getMessage()
            ], 500);
        }
    }
}