<?php
namespace App\Models\results;

use CodeIgniter\Model;

class SummaryModel extends Model
{
    public function st_s01($data)
    {
        $sql = "SELECT 
                    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 receipt b ON b.rcpt_sq = a.rcpt_sq
                WHERE 1=1 ";

        if ($data['schDateGb'] === "1") {
            $sql .= " AND a.rsrv_date BETWEEN {$data['sdate']} AND {$data['edate']}";
        } else {
            $sql .= " AND b.rcpt_tm BETWEEN CONCAT({$data['sdate']}, ' 00:00:00') AND CONCAT({$data['edate']}, ' 23:59:59')";
        }


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

        return $query->getResultArray();
    }

    public function st_s01_2($data)
    {
        $sql = "SELECT
                    a.agent_id, a.agent_nm, count(a.agent_id) rcpt_count, sum(case when b.photo_save_dt is null then 0 else 1 end) shoot_count
                FROM
                    receipt AS a
                INNER JOIN result AS b ON b.rcpt_sq = a.rcpt_sq		
                WHERE 
                    a.rcpt_tm >= DATE('{$data['sdate']} 00:00:00')
                    AND a.rcpt_tm <= DATE('{$data['edate']} 23:59:59')
                GROUP BY a.agent_id, a.agent_nm
                HAVING count(a.agent_id) > 80

                ORDER BY rcpt_count DESC ";


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

        return $query->getResultArray();
    }
}