<?php
namespace App\Models\manage;

use CodeIgniter\Model;

class PhoneModel extends Model
{

    public function getCodes()
    {
        $sql = "SELECT category, cd, cd_nm, use_yn FROM codes WHERE category = 'CP_ID' AND use_yn = 'Y' ORDER BY view_odr ASC";

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

        return $query->getResultArray();
    }

    public function getTotalCount($data)
    {
        $params = [];
        $sql = "SELECT
                    COUNT(*) AS cnt
                FROM
                    dupl_phone_list AS a	
                LEFT JOIN codes AS b ON a.cpid = b.cd AND b.category = 'CP_ID'	";

        $sql .= "WHERE 1=1 ";

        if (!empty($data['cpid'])) {
            $sql .= "AND a.cpid = ?";
            array_push($params, $data['cpid']);
        }

        if (!empty($data['s_date'])) {
            $sql .= "AND a.s_date >= DATE(?) ";
            array_push($params, $data['s_date']);
        }

        if (!empty($data['e_date'])) {
            $sql .= "AND a.e_date <= DATE(?) ";
            array_push($params, $data['e_date']);
        }

        if (!empty($data['phone'])) {
            $sql .= "AND REPLACE(a.phone_number, '-', '') LIKE CONCAT('%', REPLACE(?, '-', ''), '%') ";
            array_push($params, $data['phone']);
        }

        if (!empty($data['useYn'])) {
            $sql .= "AND a.use_yn = ? ";
            array_push($params, $data['useYn']);
        }

        $query = $this->db->query($sql, $params ?: []);

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

    public function getDuplPhoneList($start, $end, $data)
    {
        $params = [];

        $sql = "SELECT
                    a.*
                    , (CASE a.use_yn WHEN 'Y' THEN '사용' WHEN 'N' THEN '미사용' END) use_yn_nm 
                    ,	b.cd_nm as cpid_nm
                FROM
                    dupl_phone_list AS a	
                LEFT JOIN codes AS b ON a.cpid = b.cd AND b.category = 'CP_ID' ";

        $sql .= "WHERE 1=1 ";

        if (!empty($data['cpid'])) {
            $sql .= "AND a.cpid = ?";
            array_push($params, $data['cpid']);
        }

        if (!empty($data['s_date'])) {
            $sql .= "AND a.s_date >= DATE(?) ";
            array_push($params, $data['s_date']);
        }

        if (!empty($data['e_date'])) {
            $sql .= "AND a.e_date <= DATE(?) ";
            array_push($params, $data['e_date']);
        }

        if (!empty($data['phone'])) {
            $sql .= "AND REPLACE(a.phone_number, '-', '') LIKE CONCAT('%', REPLACE(?, '-', ''), '%') ";
            array_push($params, $data['phone']);
        }

        if (!empty($data['useYn'])) {
            $sql .= "AND a.use_yn = ? ";
            array_push($params, $data['useYn']);
        }


        $sql .= "ORDER BY a.use_yn ASC, a.s_date DESC ";

        $sql .= " LIMIT ?, ?";

        $params[] = (int) $start;
        $params[] = (int) $end;

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

        return $query->getResultArray();
    }

    public function insertDuplPhone($data)
    {
        $sql = "INSERT INTO dupl_phone_list " .
            "			( phone_number, use_yn, s_date, e_date, address" .
            "			, owner, applicant, relation" .
            "			, cpid, memo, insert_tm, insert_user_id)" .
            "	 VALUES ( ?, ?, ?, ?, ? " .
            "			, ?, ?, ?" .
            "			, ?, ?, SYSDATE(), ?)";

        $this->db->query($sql, $data);

        if ($this->db->transStatus() === false) {
            return [
                'success' => false,
                'msg' => '저장실패',
            ];
        }

        // 성공
        return [
            'success' => true,
        ];
    }

    public function updateDuplPhone($data)
    {
        $sql = " UPDATE dupl_phone_list" .
            "    SET phone_number = ? " .
            "		, use_yn = ? " .
            "		, s_date = ? " .
            "		, e_date = ? " .
            "		, address = ? " .
            "		, owner = ? " .
            "		, applicant = ? " .
            "		, relation = ? " .
            "		, cpid = ? " .
            "		, memo = ? " .
            "		, insert_tm = SYSDATE() " .
            "		, insert_user_id = ? " .
            "  WHERE phone_number = ?";

        $this->db->query($sql, $data);


        if ($this->db->transStatus() === false) {
            return [
                'success' => false,
                'msg' => '저장실패',
            ];
        }

        // 성공
        return [
            'success' => true,
        ];
    }

    // 엑셀다운로드
    function getExcelPhoneList($data)
    {
        $params = [];

        $sql = "SELECT
                    a.phone_number AS '연락처'
                    , a.s_date AS '등록일'
                    , a.e_date AS '만료일'
                    , b.cd_nm AS '매체사'
                    , a.address AS '주소'
                    , a.owner AS '소유자'
                    , a.applicant AS '신청인'
                    , a.relation AS '관계'
                    , (CASE a.use_yn WHEN 'Y' THEN '사용' WHEN 'N' THEN '미사용' END) AS '사용유무'
                    , a.memo AS '메모'
                FROM
                    dupl_phone_list AS a	
                LEFT JOIN codes AS b ON a.cpid = b.cd AND b.category = 'CP_ID' ";

        $sql .= "WHERE 1=1 ";

        if (!empty($data['cpid'])) {
            $sql .= "AND a.cpid = ?";
            array_push($params, $data['cpid']);
        }

        if (!empty($data['s_date'])) {
            $sql .= "AND a.s_date >= DATE(?) ";
            array_push($params, $data['s_date']);
        }

        if (!empty($data['e_date'])) {
            $sql .= "AND a.e_date <= DATE(?) ";
            array_push($params, $data['e_date']);
        }

        if (!empty($data['phone'])) {
            $sql .= "AND REPLACE(a.phone_number, '-', '') LIKE CONCAT('%', REPLACE(?, '-', ''), '%') ";
            array_push($params, $data['phone']);
        }

        if (!empty($data['useYn'])) {
            $sql .= "AND a.use_yn = ? ";
            array_push($params, $data['useYn']);
        }


        $sql .= "ORDER BY a.use_yn ASC, a.s_date DESC ";


        $query = $this->db->query($sql, $params ?: []);

        return $query->getResultArray();
    }
}