<?php
namespace App\Models\results;

use CodeIgniter\Model;

class PersonModel 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 getAreaList($sido = '', $gugun = '')
    {

        if (!empty($gugun)) {
            $gugun = substr($gugun, '0', '5');

            $sql = "SELECT a.region_cd, TRIM(REPLACE(a.region_nm, b.region_nm, '')) region_nm" .
                " FROM region_codes a" .
                " LEFT JOIN region_codes b ON b.region_cd = CONCAT(SUBSTR(a.region_cd,1,5),'00000')" .
                " WHERE a.region_cd LIKE concat(?, '%')" .
                " AND a.region_cd NOT LIKE '%00000'" .
                " AND a.region_cd LIKE '%00'" .
                " AND a.use_yn = 'Y'" .
                " ORDER BY a.region_nm ASC";

            $query = $this->db->query($sql, [$gugun]);

        } else if (!empty($sido)) {
            $chk_sido = substr($sido, '0', '2');

            if ($chk_sido === '36') {
                $sido = substr($sido, '0', '4');
                $sql = "SELECT a.region_cd, TRIM(REPLACE(a.region_nm, b.region_nm, '')) region_nm " .
                    "FROM region_codes a " .
                    "LEFT JOIN region_codes b ON b.region_cd = CONCAT(SUBSTR(a.region_cd,1,4),'000000') " .
                    "WHERE a.region_cd LIKE concat(?, '%') " .
                    "AND a.region_cd NOT LIKE '%000000' " .
                    "AND a.region_cd LIKE '%00' " .
                    "AND a.use_yn = 'Y' " .
                    "AND EXISTS (SELECT 'x' FROM region_codes c WHERE c.region_cd LIKE CONCAT(SUBSTR(a.region_cd,1,5),'%') AND c.region_cd > CONCAT(SUBSTR(a.region_cd,1,5),'00000')) " .
                    "ORDER BY a.region_nm ASC";
            } else {
                $sido = substr($sido, '0', '2');
                $sql = "SELECT a.region_cd, TRIM(REPLACE(a.region_nm, b.region_nm, '')) region_nm" .
                    " FROM region_codes a" .
                    " LEFT JOIN region_codes b ON b.region_cd = CONCAT(SUBSTR(a.region_cd,1,2),'00000000')" .
                    " WHERE a.region_cd LIKE concat(?, '%')" .
                    " AND a.region_cd NOT LIKE '%00000000'" .
                    " AND a.region_cd LIKE '%00000'" .
                    " AND a.use_yn = 'Y'" .
                    " AND EXISTS (SELECT 'x' FROM region_codes c WHERE c.region_cd LIKE CONCAT(SUBSTR(a.region_cd,1,5),'%') AND c.region_cd > CONCAT(SUBSTR(a.region_cd,1,5),'00000'))" .
                    " ORDER BY a.region_nm ASC";
            }

            $query = $this->db->query($sql, [$sido]);
        } else {
            $sql = "SELECT a.region_cd, a.region_nm " .
                "FROM region_codes a " .
                "WHERE (a.region_cd LIKE '%00000000' " .
                "AND a.use_yn = 'Y') " .
                "OR region_cd = 3611000000;";

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


        return $query->getResultArray();


    }

    public function getTotalCount($data)
    {
        $sql = "SELECT 
                    COUNT(*) AS cnt
                FROM result a
                INNER JOIN users b ON b.usr_sq = a.usr_sq
                INNER JOIN departments c ON c.dept_sq = a.dept_sq
                INNER JOIN receipt d ON d.rcpt_sq = a.rcpt_sq
                WHERE 1=1 ";

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

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

        if (!empty($data['schDateGb'])) {
            if ($data['schDateGb'] === "1") {

                if (!empty($data['sdate'])) {
                    $sql .= "AND a.rsrv_date >= CONCAT('{$data['sdate']} 00:00:00') ";
                }

                if (!empty($data['edate'])) {
                    $sql .= "AND a.rsrv_date <= CONCAT('{$data['edate']} 23:59:59') ";
                }

            } else if ($data['schDateGb'] === "2") {
                if (!empty($data['sdate'])) {
                    $sql .= "AND d.rcpt_tm >= CONCAT('{$data['sdate']} 00:00:00') ";
                }

                if (!empty($data['edate'])) {
                    $sql .= "AND d.rcpt_tm <= CONCAT('{$data['edate']} 23:59:59') ";
                }
            }
        }

        if (!empty($data['srchTxt'])) {
            if ($data['srchType'] === "1") {
                $sql .= "AND usr_id like CONCAT('%', '{$data['srchTxt']}', '%' ) ";
            } else if ($data['srchType'] === "2") {
                $sql .= "AND usr_nm like CONCAT('%', '{$data['srchTxt']}', '%' ) ";
            } else {
                $sql .= "AND (
                            usr_id like CONCAT('%', '{$data['srchTxt']}', '%' ) 
                            OR usr_nm like CONCAT('%', '{$data['srchTxt']}', '%' )
                            ) ";
            }
        }

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

        return $query->getRow()->cnt;
    }

    public function getUserList($start, $end, $data)
    {
        $sql = "SELECT 
                    b.usr_nm, b.usr_id, b.usr_sq, c.dept_sq, c.pdept_sq, c.depth
                    , count(distinct a.rcpt_sq) rcpt_cnt
                    , COUNT(DISTINCT CASE WHEN a.cancel_dt IS NOT NULL THEN CASE WHEN a.result_cd2 IN ('9010','9020') THEN a.rcpt_sq END END) resv_cancel_cnt
                    , COUNT(DISTINCT CASE WHEN a.assign_save_dt IS NOT NULL THEN a.rcpt_sq END) resv_assign_cnt
                    , COUNT(DISTINCT CASE WHEN a.cancel_dt IS NOT NULL THEN CASE WHEN a.result_cd2 = '9030' THEN a.rcpt_sq END END) prev_visit_cnt
                    , COUNT(DISTINCT CASE WHEN a.cancel_dt IS NOT NULL THEN CASE WHEN a.result_cd2 = '9040' THEN a.rcpt_sq END END) next_visit_cnt
                    , COUNT(DISTINCT CASE WHEN a.cancel_dt IS NOT NULL THEN CASE WHEN a.result_cd2 = '9045' THEN a.rcpt_sq END END) next_shoot_cnt
                    , COUNT(DISTINCT CASE WHEN a.photo_save_dt IS NOT NULL THEN a.rcpt_sq END) shoot_cnt
                    , COUNT(DISTINCT CASE WHEN a.result_cd1 = '70' THEN a.rcpt_sq END) delay_confirm_cnt
                    , COUNT(DISTINCT CASE WHEN a.result_cd2 = '9050' THEN a.rcpt_sq END) fail_confirm_cnt
                    , COUNT(DISTINCT CASE WHEN a.result_cd1 = '60' THEN a.rcpt_sq END) confirm_cnt
                FROM result a
                INNER JOIN users b ON b.usr_sq = a.usr_sq
                INNER JOIN departments c ON c.dept_sq = a.dept_sq
                INNER JOIN receipt d ON d.rcpt_sq = a.rcpt_sq
                WHERE 1=1 ";

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

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

        if (!empty($data['schDateGb'])) {
            if ($data['schDateGb'] === "1") {

                if (!empty($data['sdate'])) {
                    $sql .= "AND a.rsrv_date >= CONCAT('{$data['sdate']} 00:00:00') ";
                }

                if (!empty($data['edate'])) {
                    $sql .= "AND a.rsrv_date <= CONCAT('{$data['edate']} 23:59:59') ";
                }

            } else if ($data['schDateGb'] === "2") {
                if (!empty($data['sdate'])) {
                    $sql .= "AND d.rcpt_tm >= CONCAT('{$data['sdate']} 00:00:00') ";
                }

                if (!empty($data['edate'])) {
                    $sql .= "AND d.rcpt_tm <= CONCAT('{$data['edate']} 23:59:59') ";
                }
            }
        }

        if (!empty($data['srchTxt'])) {
            if ($data['srchType'] === "1") {
                $sql .= "AND usr_id like CONCAT('%', '{$data['srchTxt']}', '%' ) ";
            } else if ($data['srchType'] === "2") {
                $sql .= "AND usr_nm like CONCAT('%', '{$data['srchTxt']}', '%' ) ";
            } else {
                $sql .= "AND (
                            usr_id like CONCAT('%', '{$data['srchTxt']}', '%' ) 
                            OR usr_nm like CONCAT('%', '{$data['srchTxt']}', '%' )
                            ) ";
            }
        }


        $sql .= "GROUP BY b.usr_id, b.usr_nm ";

        $sql .= "LIMIT {$start}, {$end}";

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

        return $query->getResultArray();
    }

    // 엑셀 다운로드
    public function getExcelUserList($data)
    {
        $sql = "SELECT 
                    b.usr_id AS '아이디'
                    , b.usr_nm AS '이름'
                    , count(distinct a.rcpt_sq) AS '접수'
                    , COUNT(DISTINCT CASE WHEN a.cancel_dt IS NOT NULL THEN CASE WHEN a.result_cd2 IN ('9010','9020') THEN a.rcpt_sq END END) AS '예약취소'
                    , COUNT(DISTINCT CASE WHEN a.assign_save_dt IS NOT NULL THEN a.rcpt_sq END) AS '예약확인'
                    , COUNT(DISTINCT CASE WHEN a.cancel_dt IS NOT NULL THEN CASE WHEN a.result_cd2 = '9030' THEN a.rcpt_sq END END) AS '방문전취소'
                    , COUNT(DISTINCT CASE WHEN a.cancel_dt IS NOT NULL THEN CASE WHEN a.result_cd2 = '9040' THEN a.rcpt_sq END END) AS '방문후취소'
                    , COUNT(DISTINCT CASE WHEN a.cancel_dt IS NOT NULL THEN CASE WHEN a.result_cd2 = '9045' THEN a.rcpt_sq END END) AS '촬영후취소'
                    , COUNT(DISTINCT CASE WHEN a.photo_save_dt IS NOT NULL THEN a.rcpt_sq END) AS '촬영'
                    , COUNT(DISTINCT CASE WHEN a.result_cd1 = '70' THEN a.rcpt_sq END) AS '검수지연'
                    , COUNT(DISTINCT CASE WHEN a.result_cd2 = '9050' THEN a.rcpt_sq END) AS '검수실패'
                    , COUNT(DISTINCT CASE WHEN a.result_cd1 = '60' THEN a.rcpt_sq END) AS '검수완료'
                FROM result a
                INNER JOIN users b ON b.usr_sq = a.usr_sq
                INNER JOIN departments c ON c.dept_sq = a.dept_sq
                INNER JOIN receipt d ON d.rcpt_sq = a.rcpt_sq
                WHERE 1=1 ";

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

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

        if (!empty($data['schDateGb'])) {
            if ($data['schDateGb'] === "1") {

                if (!empty($data['sdate'])) {
                    $sql .= "AND a.rsrv_date >= CONCAT('{$data['sdate']} 00:00:00') ";
                }

                if (!empty($data['edate'])) {
                    $sql .= "AND a.rsrv_date <= CONCAT('{$data['edate']} 23:59:59') ";
                }

            } else if ($data['schDateGb'] === "2") {
                if (!empty($data['sdate'])) {
                    $sql .= "AND d.rcpt_tm >= CONCAT('{$data['sdate']} 00:00:00') ";
                }

                if (!empty($data['edate'])) {
                    $sql .= "AND d.rcpt_tm <= CONCAT('{$data['edate']} 23:59:59') ";
                }
            }
        }

        if (!empty($data['srchTxt'])) {
            if ($data['srchType'] === "1") {
                $sql .= "AND usr_id like CONCAT('%', '{$data['srchTxt']}', '%' ) ";
            } else if ($data['srchType'] === "2") {
                $sql .= "AND usr_nm like CONCAT('%', '{$data['srchTxt']}', '%' ) ";
            } else {
                $sql .= "AND (
                            usr_id like CONCAT('%', '{$data['srchTxt']}', '%' ) 
                            OR usr_nm like CONCAT('%', '{$data['srchTxt']}', '%' )
                            ) ";
            }
        }


        $sql .= "GROUP BY b.usr_id, b.usr_nm ";

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

        return $query->getResultArray();
    }
}