<?php
namespace App\Models\results;

use CodeIgniter\Model;

class DeptModel extends Model
{

    // 소속본부조회
    public function getBonbuList()
    {
        $sql = "SELECT dept_sq, pdept_sq, dept_nm, dept_desc, dept_head, use_yn, depth, insert_tm, insert_usr, update_tm, update_usr, lft, rgt" .
            "  FROM departments" .
            " WHERE depth = 1" .
            "   AND use_yn = 'Y'" .
            " ORDER BY lft";

        $query = $this->db->query($sql);


        return $query->getResultArray();
    }

    // 소속팀 조회
    public function getTeamList()
    {
        $sql = "SELECT dept_sq, pdept_sq, dept_nm" .
            "  FROM departments" .
            " WHERE depth = 2" .
            "   AND use_yn = 'Y'" .
            " ORDER BY dept_nm";

        $query = $this->db->query($sql);


        return $query->getResultArray();
    }

    public function st_d01($data)
    {
        $sql_where2 = '';
        if ($data['schDateGb'] == '2') {
            $sql_where = " AND c.insert_tm between DATE('{$data['sdate']} 00:00:00') and DATE('{$data['edate']} 23:59:59')";
        } else {
            $sql_where = " AND c.rsrv_date between DATE('{$data['sdate']} 00:00:00') and DATE('{$data['edate']} 23:59:59') ";
        }

        $sql = "SELECT 
	                a.dept_sq, CONCAT(SPACE(4*(a.depth-1)), a.dept_nm) dept_nm, a.depth, a.pdept_sq
				    , (SELECT COUNT(*) FROM region_codes d WHERE CONCAT(',',GROUP_CONCAT(DISTINCT b.dept_sq), ',') LIKE CONCAT('%,',d.dept_sq,',%') AND d.region_cd NOT LIKE '%00000') AS region_cnt
                    , COUNT(DISTINCT e.rcpt_sq) rcpt_cnt
                    , COUNT(DISTINCT CASE WHEN c.cancel_dt IS NOT NULL THEN CASE WHEN c.result_cd2 IN ('9010','9020') THEN c.rcpt_sq END END) resv_cancel_cnt
                    , COUNT(DISTINCT CASE WHEN c.assign_save_dt IS NOT NULL THEN c.rcpt_sq END) resv_assign_cnt
                    , COUNT(DISTINCT CASE WHEN c.cancel_dt IS NOT NULL THEN CASE WHEN c.result_cd2 = '9030' THEN c.rcpt_sq END END) prev_visit_cnt
                    , COUNT(DISTINCT CASE WHEN c.cancel_dt IS NOT NULL THEN CASE WHEN c.result_cd2 = '9040' THEN c.rcpt_sq END END) next_visit_cnt
                    , COUNT(DISTINCT CASE WHEN c.cancel_dt IS NOT NULL THEN CASE WHEN c.result_cd2 = '9045' THEN c.rcpt_sq END END) next_shoot_cnt
                    , COUNT(DISTINCT CASE WHEN c.photo_save_dt IS NOT NULL THEN c.rcpt_sq END) shoot_cnt
                    , COUNT(DISTINCT CASE WHEN c.result_cd1 = '70' THEN c.rcpt_sq END) delay_confirm_cnt
                    , COUNT(DISTINCT CASE WHEN c.result_cd2 = '9050' THEN c.rcpt_sq END) fail_confirm_cnt
                    , COUNT(DISTINCT CASE WHEN c.result_cd1 = '60' THEN c.rcpt_sq END) confirm_cnt
				 FROM departments a
				 LEFT JOIN departments b ON b.lft BETWEEN a.lft AND a.rgt
				 LEFT JOIN result c ON c.dept_sq = b.dept_sq  {$sql_where}				 
				 LEFT JOIN receipt e ON e.rcpt_sq = c.rcpt_sq 
                 WHERE a.pdept_sq != 0 AND a.use_yn = 'Y' ";

        if (!empty($data['bonbu'])) {
            $sql .= "AND b.pdept_sq = {$data['bonbu']} ";

            if (!empty($data['dept_sq'])) {
                $sql .= "AND a.dept_sq = {$data['dept_sq']} ";
            }
        }

        $sql .= "GROUP BY a.dept_sq ORDER BY a.lft";

        $query = $this->db->query($sql);

        return $query->getResultArray();
    }


    public function getExcelList($data)
    {
        $sql_where2 = '';
        if ($data['schDateGb'] == '2') {
            $sql_where = " AND c.insert_tm between DATE('{$data['sdate']} 00:00:00') and DATE('{$data['edate']} 23:59:59')";
        } else {
            $sql_where = " AND c.rsrv_date between DATE('{$data['sdate']} 00:00:00') and DATE('{$data['edate']} 23:59:59') ";
        }

        $sql = "SELECT 
                    CONCAT(SPACE(4*(a.depth-1)), a.dept_nm) AS '조직명'
				    , (SELECT COUNT(*) FROM region_codes d WHERE CONCAT(',',GROUP_CONCAT(DISTINCT b.dept_sq), ',') LIKE CONCAT('%,',d.dept_sq,',%') AND d.region_cd NOT LIKE '%00000') AS '관할지역'
                    , COUNT(DISTINCT e.rcpt_sq) '접수'
                    , COUNT(DISTINCT CASE WHEN c.cancel_dt IS NOT NULL THEN CASE WHEN c.result_cd2 IN ('9010','9020') THEN c.rcpt_sq END END) AS '예약취소'
                    , COUNT(DISTINCT CASE WHEN c.assign_save_dt IS NOT NULL THEN c.rcpt_sq END) AS '예약확인'
                    , COUNT(DISTINCT CASE WHEN c.cancel_dt IS NOT NULL THEN CASE WHEN c.result_cd2 = '9030' THEN c.rcpt_sq END END) AS '방문전취소'
                    , COUNT(DISTINCT CASE WHEN c.cancel_dt IS NOT NULL THEN CASE WHEN c.result_cd2 = '9040' THEN c.rcpt_sq END END) AS '방문후취소'
                    , COUNT(DISTINCT CASE WHEN c.cancel_dt IS NOT NULL THEN CASE WHEN c.result_cd2 = '9045' THEN c.rcpt_sq END END) AS '촬영후취소'
                    , COUNT(DISTINCT CASE WHEN c.photo_save_dt IS NOT NULL THEN c.rcpt_sq END) AS '촬영'
                    , COUNT(DISTINCT CASE WHEN c.result_cd1 = '70' THEN c.rcpt_sq END) AS '검수지연'
                    , COUNT(DISTINCT CASE WHEN c.result_cd2 = '9050' THEN c.rcpt_sq END) AS '검수실패'
                    , COUNT(DISTINCT CASE WHEN c.result_cd1 = '60' THEN c.rcpt_sq END) AS '검수완료'
				 FROM departments a
				 LEFT JOIN departments b ON b.lft BETWEEN a.lft AND a.rgt
				 LEFT JOIN result c ON c.dept_sq = b.dept_sq  {$sql_where}				 
				 LEFT JOIN receipt e ON e.rcpt_sq = c.rcpt_sq 
                 WHERE a.pdept_sq != 0 AND a.use_yn = 'Y' ";

        if (!empty($data['bonbu'])) {
            $sql .= "AND b.pdept_sq = {$data['bonbu']} ";

            if (!empty($data['dept_sq'])) {
                $sql .= "AND a.dept_sq = {$data['dept_sq']} ";
            }
        }

        $sql .= "GROUP BY a.dept_sq ORDER BY a.lft";


        $query = $this->db->query($sql);

        return $query->getResultArray();
    }


}