<?php
namespace App\Models\listfax;

use CodeIgniter\Model;

class ListFaxModel extends Model
{

    public function getTotalCount($data)
    {
        $sql = "SELECT
                COUNT(*) AS cnt
            FROM
                fax_imgs a	
            WHERE		
                    a.RECV_TIME >= CONCAT(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 7 DAY), 'Y-m-d'), ' 00:00:00')
                    AND a.RECV_TIME <= CONCAT(DATE_FORMAT(NOW(), 'Y-m-d'), ' 23:59:59')
                    AND a.caller_no LIKE CONCAT('%', REPLACE(?, '-', ''), '%')
                    
            GROUP BY a.`MID`
        ";


        $query = $this->db->query($sql . [$data['caller_no']]);

        $row = $query->getRow();
        return $row ? $row->cnt : 0;
    }

    public function getResultList($start, $end, $data)
    {
        $sql = "
            SELECT
                a.fax_sq, a.mid, GROUP_CONCAT(a.file_name) AS file_name, a.file_path, a.CALLER_NO, a.CALLEE_NO, a.RECV_TIME, a.SAVE_TIME, COUNT(a.file_name) AS cnt
            FROM
                fax_imgs a	
            WHERE		
                    a.RECV_TIME >= CONCAT(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 7 DAY), 'Y-m-d'), ' 00:00:00')
                    AND a.RECV_TIME <= CONCAT(DATE_FORMAT(NOW(), 'Y-m-d'), ' 23:59:59')
                    AND a.caller_no LIKE CONCAT('%', REPLACE(?, '-', ''), '%')
                    
            GROUP BY a.`MID`
            ORDER BY a.fax_sq DESC, a.RECV_TIME DESC

            LIMIT {$start}, {$end}
        ";

        $query = $this->db->query($sql, [$data['caller_no']]);

        return $query->getResultArray();
    }

}