<?php
namespace App\Models\manage;

use CodeIgniter\Model;

class SmsModel extends Model
{

    public function getTotalCount($data)
    {
        $params = [];

        $sql = "SELECT
                    COUNT(*) AS cnt
                FROM
                    ums_log a
                LEFT OUTER JOIN result b ON a.etc1 = b.rsrv_sq
                LEFT OUTER JOIN departments c ON	b.dept_sq = c.dept_sq
                LEFT OUTER JOIN users d ON b.usr_sq = d.usr_sq
                WHERE
                    a.`status` IS NOT NULL ";

        if (!empty($data['start_dt']) && !empty($data['end_dt'])) {
            $nextDay = date('Y-m-d', strtotime($data['end_dt'] . ' +1 day'));

            $sql .= " AND a.send_time >= ? ";
            $sql .= " AND a.send_time <  ? ";

            array_push($params, $data['start_dt'] . " 00:00:00");
            array_push($params, $nextDay . " 00:00:00");
        } else {
            if (!empty($data['start_dt'])) {
                $sql .= "AND a.send_time >= DATE(?) ";

                array_push($params, $data['start_dt'] . " 00:00:00");
            }

            if (!empty($data['end_dt'])) {
                $nextDay = date('Y-m-d', strtotime($data['end_dt'] . ' +1 day'));

                $sql .= " AND a.send_time < ? ";
                array_push($params, $nextDay . " 00:00:00");
            }
        }


        if (!empty($data['srchTxt'])) {
            switch ($data['srchType']) {

                case '1':
                    $sql .= "AND a.send_name LIKE CONCAT('%', ?, '%') ";
                    array_push($params, $data['srchTxt']);
                    break;
                case '2':
                    $sql .= "AND a.dest_name LIKE CONCAT('%', ?, '%') ";
                    array_push($params, $data['srchTxt']);
                    break;
                case '3':
                    $sql .= "AND a.dest_phone LIKE CONCAT('%', ?, '%') ";
                    array_push($params, $data['srchTxt']);
                    break;
                default:
                    $sql .= "AND (
                                a.send_name LIKE CONCAT('%', ?, '%')
                                OR a.dest_name LIKE CONCAT('%', ?, '%')
                                OR a.dest_phone LIKE CONCAT('%', ?, '%')
                                )";

                    array_push($params, $data['srchTxt']);
                    array_push($params, $data['srchTxt']);
                    array_push($params, $data['srchTxt']);
                    break;

            }
        }

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

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

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

        $sql = "SELECT
                    a.cmid
                    , a.dest_phone
                    , a.dest_name
                    , a.send_phone
                    , a.send_name
                    , a.request_time
                    , a.send_time
                    , a.report_time
                    , a.subject
                    , a.msg_body
                    , (select cd_nm from codes where category = 'SMS_STATUS' AND cd = a.status) as status_nm
                    , (CASE WHEN a.status = 1 THEN '성공' ELSE '실패' END) AS status_nm
                    , status
                    , a.etc1
                    , a.etc2
                    , (SELECT category_nm FROM codes WHERE category = 'SMS_MSG_TYPE' AND cd = a.etc2) as cate_nm
                    , a.etc3
                    , b.rsrv_sq
                    , c.dept_nm
                    , d.usr_nm
                FROM
                    ums_log a
                LEFT OUTER JOIN result b ON a.etc1 = b.rsrv_sq
                LEFT OUTER JOIN departments c ON	b.dept_sq = c.dept_sq
                LEFT OUTER JOIN users d ON b.usr_sq = d.usr_sq
                WHERE
                    a.`status` IS NOT NULL ";


        if (!empty($data['start_dt']) && !empty($data['end_dt'])) {
            $nextDay = date('Y-m-d', strtotime($data['end_dt'] . ' +1 day'));

            $sql .= " AND a.send_time >= ? ";
            $sql .= " AND a.send_time <  ? ";

            array_push($params, $data['start_dt'] . " 00:00:00");
            array_push($params, $nextDay . " 00:00:00");
        } else {
            if (!empty($data['start_dt'])) {
                $sql .= "AND a.send_time >= DATE(?) ";

                array_push($params, $data['start_dt'] . " 00:00:00");
            }

            if (!empty($data['end_dt'])) {
                $nextDay = date('Y-m-d', strtotime($data['end_dt'] . ' +1 day'));

                $sql .= " AND a.send_time < ? ";
                array_push($params, $nextDay . " 00:00:00");
            }
        }


        if (!empty($data['srchTxt'])) {
            switch ($data['srchType']) {

                case '1':
                    $sql .= "AND a.send_name LIKE CONCAT('%', ?, '%') ";
                    array_push($params, $data['srchTxt']);
                    break;
                case '2':
                    $sql .= "AND a.dest_name LIKE CONCAT('%', ?, '%') ";
                    array_push($params, $data['srchTxt']);
                    break;
                case '3':
                    $sql .= "AND a.dest_phone LIKE CONCAT('%', ?, '%') ";
                    array_push($params, $data['srchTxt']);
                    break;
                default:
                    $sql .= "AND (
                                a.send_name LIKE CONCAT('%', ?, '%')
                                OR a.dest_name LIKE CONCAT('%', ?, '%')
                                OR a.dest_phone LIKE CONCAT('%', ?, '%')
                                )";

                    array_push($params, $data['srchTxt']);
                    array_push($params, $data['srchTxt']);
                    array_push($params, $data['srchTxt']);
                    break;

            }
        }


        $sql .= "
                ORDER BY a.request_time DESC
                LIMIT ?, ? ";

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

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

        return $query->getResultArray();
    }


    public function getExcelList($data)
    {
        $params = [];

        $sql = "SELECT
                    a.send_name AS '발신자'
                    , a.dest_name AS '수신자'
                    , a.send_time AS '발송일자'
                    , (SELECT category_nm FROM codes WHERE category = 'SMS_MSG_TYPE' AND cd = a.etc2) AS '발송구분'
                    , a.send_phone AS '발신번호'
                    , a.dest_phone AS '수신번호'
                    , (select cd_nm from codes where category = 'SMS_STATUS' AND cd = a.status) AS '발송상태'
                FROM
                    ums_log a
                LEFT OUTER JOIN result b ON a.etc1 = b.rsrv_sq
                LEFT OUTER JOIN departments c ON	b.dept_sq = c.dept_sq
                LEFT OUTER JOIN users d ON b.usr_sq = d.usr_sq
                WHERE
                    a.`status` IS NOT NULL ";


        if (!empty($data['start_dt']) && !empty($data['end_dt'])) {
            $nextDay = date('Y-m-d', strtotime($data['end_dt'] . ' +1 day'));

            $sql .= " AND a.send_time >= ? ";
            $sql .= " AND a.send_time <  ? ";

            array_push($params, $data['start_dt'] . " 00:00:00");
            array_push($params, $nextDay . " 00:00:00");
        } else {
            if (!empty($data['start_dt'])) {
                $sql .= "AND a.send_time >= DATE(?) ";

                array_push($params, $data['start_dt'] . " 00:00:00");
            }

            if (!empty($data['end_dt'])) {
                $nextDay = date('Y-m-d', strtotime($data['end_dt'] . ' +1 day'));

                $sql .= " AND a.send_time < ? ";
                array_push($params, $nextDay . " 00:00:00");
            }
        }


        if (!empty($data['srchTxt'])) {
            switch ($data['srchType']) {

                case '1':
                    $sql .= "AND a.send_name LIKE CONCAT('%', ?, '%') ";
                    array_push($params, $data['srchTxt']);
                    break;
                case '2':
                    $sql .= "AND a.dest_name LIKE CONCAT('%', ?, '%') ";
                    array_push($params, $data['srchTxt']);
                    break;
                case '3':
                    $sql .= "AND a.dest_phone LIKE CONCAT('%', ?, '%') ";
                    array_push($params, $data['srchTxt']);
                    break;
                default:
                    $sql .= "AND (
                                a.send_name LIKE CONCAT('%', ?, '%')
                                OR a.dest_name LIKE CONCAT('%', ?, '%')
                                OR a.dest_phone LIKE CONCAT('%', ?, '%')
                                )";

                    array_push($params, $data['srchTxt']);
                    array_push($params, $data['srchTxt']);
                    array_push($params, $data['srchTxt']);
                    break;

            }
        }


        $sql .= "
                ORDER BY a.request_time DESC
                ";

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

        return $query->getResultArray();
    }


    // 문자발송저장
    public function sendSms($data)
    {
        $sql = "INSERT INTO ums_data ";
        $sql .= "(
                dest_phone, 
                dest_name, 
                send_phone, 
                send_name, 
                subject, 
                msg_body,
                request_time,
                send_time,
                etc2,
                etc3
                )";

        $sql .= "(
                {$data['dest_phone']},
                {$data['dest_name']},
                {$data['send_phone']},
                {$data['send_name']},
                {$data['subject']},
                {$data['msg_body']},
                NOW(),
                NOW(),
                {$data['etc2']},
                {$data['etc3']}
                )";


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

}