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;

        try {
            $limit = isset($request['LIMIT']) ? $request['LIMIT'] : null;

            $builder = $db->table('ITEM_ORDER_LIST D')
                ->select('D.*, I.NAME as ITEM_NAME, I.COMPANY_NUMBER, I.CONTACT_INF, U.NICK_NAME as INF_NICK_NAME, U.NAME as INF_NAME')
                ->join('ITEM_LIST I', 'D.ITEM_SEQ = I.SEQ', 'left')
                ->join('USER_LIST U', 'I.CONTACT_INF = U.SEQ', 'left')
                ->where('D.DELI_COMP', '') // 배송 정보가 등록되지 않은 정보만 신규 주문으로 판단
                ->where('I.DEL_YN', 'N')
                ->orderBy('D.REG_DATE', 'DESC');

            // 사용자 타입별 필터링
            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);
//            }

            // 총 개수 먼저 구하기 (LIMIT 적용 전)
            $totalCount = $builder->countAllResults(false);

            // LIMIT이 있으면 적용
            if ($limit) {
                $builder->limit($limit);
            }

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

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

    // 진행중인 공동구매 리스트 조회
    public function activeItems()
    {
        $db = \Config\Database::connect();

        // POST JSON 파라미터 받기
        $request = $this->request->getJSON(true);

        $itemType = isset($request['TYPE']) ? $request['TYPE'] : null;
        $showYn = isset($request['SHOW_YN']) ? $request['SHOW_YN'] : null;
        $status = isset($request['STATUS']) ? $request['STATUS'] : null;
        $memberType = isset($request['MEMBER_TYPE']) ? $request['MEMBER_TYPE'] : null;
        $companyNumber = isset($request['COMPANY_NUMBER']) ? $request['COMPANY_NUMBER'] : null;
        $memberSeq = isset($request['MEMBER_SEQ']) ? $request['MEMBER_SEQ'] : null;
        $count = isset($request['LIMIT']) ? (int)$request['LIMIT'] : null;

        // 쿼리 빌더 (VENDOR_LIST, BRAND_LIST 조인 추가)
        $builder = $db->table('ITEM_LIST I')
            ->select('I.*, COALESCE(V.COMPANY_NAME, B.COMPANY_NAME) as COMPANY_NAME')
            ->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');

        // 노출중, 비노출 여부 확인
        $builder->where('I.SHOW_YN', $showYn);
        $builder->where('I.TYPE', $itemType);

        // 사용자 타입별 필터링
        if ($memberType === 'INFLUENCER' && !empty($memberSeq)) {
            // 인플루언서의 경우: 공동구매 담당 제품만
            $builder->where('I.CONTACT_INF', $memberSeq);
        } else {
            if($memberType === 'VENDOR'){
                // 벤더사의 경우: 자사 제품만 조회
                $builder->where('I.COMPANY_NUMBER', $companyNumber);
            } else {
                $builder->groupStart()
                    ->where('I.COMPANY_NUMBER', $companyNumber)
                    ->orWhere('I.CONTACT_BRD', $memberSeq)
                    ->groupEnd();
            }
        }

        if($status !== null) {
            $builder->where('I.STATUS', $status);
        }

        // 업데이트 날짜 기준으로 정렬
        $builder->orderBy('I.REGDATE', 'DESC');

        // 총 개수 먼저 구하기 (LIMIT 적용 전)
        $totalCount = $builder->countAllResults(false);

        // 대시보드 - 진행중인 공동구매의 경우 limit 설정
        if ($count !== null && $count > 0) {
            $builder->limit($count);
        }

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

        return $this->respond([
            'data' => $lists,
            'total' => $totalCount
        ], 200);
    }

    // 인플루언서별 판매된 주문 건수 통계
    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('ITEM_ORDER_LIST D')
                ->select('I.CONTACT_INF as INF_SEQ, INF.NICK_NAME as INF_NAME, INF.NAME as INF_REAL_NAME, INF.PHONE, COUNT(D.SEQ) as ORDER_COUNT')
                ->join('ITEM_LIST I', 'D.ITEM_SEQ = I.SEQ', 'inner')
                ->join('USER_LIST INF', 'I.CONTACT_INF = INF.SEQ', 'left')
                ->where('I.DEL_YN', 'N')
                ->where('I.CONTACT_INF >', 0) // 인플루언서가 배정된 항목만
                ->groupBy('I.CONTACT_INF, INF.NICK_NAME')
                ->orderBy('ORDER_COUNT', 'DESC');

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

            // 총 개수 먼저 구하기 (LIMIT 적용 전)
            $totalCount = $builder->countAllResults(false);

            // LIMIT이 있으면 적용
            if ($limit) {
                $builder->limit($limit);
            }

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

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

    // 인플루언서별 판매된 주문 내역
    public function influencerOrderDetail()
    {
        // 한국 시간으로 설정
        date_default_timezone_set('Asia/Seoul');

        $db = \Config\Database::connect();
        $request = $this->request->getJSON(true);
        
        $contactInf = isset($request['CONTACT_INF']) ? $request['CONTACT_INF'] : null;
        $companyNumber = isset($request['COMPANY_NUMBER']) ? $request['COMPANY_NUMBER'] : null;
        
        try {
            $builder = $db->table('ITEM_LIST I')
                ->select('I.*, COALESCE(SUM(D.QTY), 0) as QTY, COALESCE(COUNT(D.SEQ), 0) as ORDER_COUNT')
                ->join('ITEM_ORDER_LIST D', 'I.SEQ = D.ITEM_SEQ', 'left')
                ->where('I.DEL_YN', 'N')
                ->groupBy('I.SEQ')
                ->orderBy('I.ORDER_END_DATE', 'DESC');
                
            if (!empty($contactInf) && !empty($companyNumber)) {
                $builder->where('I.CONTACT_INF', $contactInf)
                       ->where('I.COMPANY_NUMBER', $companyNumber);
            }
            
            $items = $builder->get()->getResultArray();
            
            return $this->respond([
                'data' => $items,
                'total' => count($items)
            ], 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('ITEM_ORDER_LIST D')
                ->join('ITEM_LIST I', 'D.ITEM_SEQ = I.SEQ', 'inner')
                ->where('I.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('ITEM_ORDER_LIST D')
                ->join('ITEM_LIST I', 'D.ITEM_SEQ = I.SEQ', 'inner')
                ->where('I.DEL_YN', 'N')
                ->where($whereConditions)
                ->countAllResults();

            // 활성 인플루언서 수 (주문이 있는 인플루언서)
            $activeInfluencersCount = $db->table('ITEM_ORDER_LIST D')
                ->select('I.CONTACT_INF')
                ->join('ITEM_LIST I', 'D.ITEM_SEQ = I.SEQ', 'inner')
                ->where('I.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);
        }
    }

}