<?php
namespace App\Models\results;

use CodeIgniter\Model;

class M411Model extends Model
{

    public function getTotalCount($data)
    {
        $sql = "SELECT COUNT(*) AS cnt FROM ( ";
        $sql .= "SELECT 
                    cpid,
                    SUM(t_cnt) t_cnt,
                    SUM(hong1_cnt) hong1_cnt,
                    SUM(hong2_cnt) hong2_cnt,
                    SUM(m_cnt) m_cnt,
                    SUM(v2_cnt) v2_cnt,
                    sum(total) total,
                                
                    SUM(a) t_o,
                    SUM(b) t_x,
                    SUM(c) t_n,
                    SUM(d) t_e,
                    
                    SUM(e) d_o,
                    SUM(f) d_x,

                    (SUM(a)+SUM(e)) dt_o, 
                    (SUM(b)+SUM(f)) dt_x, 
                                
                    SUM(e)+SUM(f) d_tot,
                    SUM(a)+SUM(b)+SUM(c)+SUM(d) t_tot,
                    
                    SUM(g) dt_tot,
                    
                    SUM(h) r_o,
                    SUM(i) r_x,
                    SUM(h) r_e,
                    SUM(k) done,
                    SUM(l) r_tot,
                                
                    SUM(m) s_1,
                    round((SUM(m) / total),2) s_2
                                FROM (
                SELECT a.cpid,
                        COUNT(*) total
                        ,SUM(CASE vrfc_type_sub WHEN 'D1' THEN 1 ELSE 0 END) hong1_cnt
                        ,SUM(CASE vrfc_type_sub WHEN 'D2' THEN 1 ELSE 0 END) hong2_cnt
                        ,SUM(CASE vrfc_type_sub WHEN 'M1' THEN 1 ELSE 0 END) m_cnt
                        ,SUM(CASE vrfc_type_sub WHEN 'O1' THEN 1 ELSE 0 END) v2_cnt
                        ,SUM(CASE vrfc_type_sub WHEN 'T1' THEN 1 ELSE 0 END) t_cnt
                        , 0 a,0 b,0 c,0 d,0 e,0 f,0 g,0 h,0 i,0 j,0 k, 0 l, 0 m
                FROM v2_article_info a
                INNER JOIN v2_vrfc_req b ON a.vr_sq = b.vr_sq
                WHERE b.insert_tm BETWEEN CONCAT('{$data['sdate']} 00:00:00') AND CONCAT('{$data['edate']} 23:59:59')
                GROUP BY a.cpid
                                
                                UNION ALL
                                
                SELECT cpid
                        ,0 ,0 ,0 ,0 ,0 ,0
                        ,t_o,t_x,t_n,t_e
                        ,d_o,d_x
                        ,(t_o+t_x+t_n+t_e+d_o+d_x) AS dt_tot 
                        ,r_o,r_x,r_e
                        ,(d_d+d_t) AS done 
                        ,(r_o+r_x+r_e) AS r_tot 
                        ,(r_o+r_e+d_d+d_t) AS s_1
                FROM (
                    SELECT cpid, 
                    SUM(CASE gbn_cd WHEN 'T0201' THEN cnt ELSE 0 END) t_o, 
                    SUM(CASE gbn_cd WHEN 'T0202' THEN cnt ELSE 0 END) t_x, 
                    SUM(CASE gbn_cd WHEN 'T0203' THEN cnt ELSE 0 END) t_n, 
                    SUM(CASE gbn_cd WHEN 'T0204' THEN cnt ELSE 0 END) t_e, 
                    SUM(CASE gbn_cd WHEN 'D0201' THEN cnt ELSE 0 END) d_o, 
                    SUM(CASE gbn_cd WHEN 'D0202' THEN cnt ELSE 0 END) d_x, 
                    SUM(CASE gbn_cd WHEN 'R0101' THEN cnt ELSE 0 END) r_o, 
                    SUM(CASE gbn_cd WHEN 'R0102' THEN cnt ELSE 0 END) r_x, 
                                        SUM(CASE gbn_cd WHEN 'R0103' THEN cnt ELSE 0 END) r_e, 
                                        SUM(CASE gbn_cd WHEN 'D0205' THEN cnt ELSE 0 END) d_d, 
                                        SUM(CASE gbn_cd WHEN 'T0206' THEN cnt ELSE 0 END) d_t  
                                        FROM v2_st_daily
                                        WHERE st_date BETWEEN CONCAT('{$data['sdate']} 00:00:00') AND CONCAT('{$data['edate']} 23:59:59')
                                        GROUP BY cpid
                                    )A
                                ) table1
                GROUP BY cpid ";

        $sql .= ") AS t";

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

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

    public function getResultList($data)
    {
        $sql = "SELECT 
                    cpid,
                    SUM(t_cnt) t_cnt,
                    SUM(hong1_cnt) hong1_cnt,
                    SUM(hong2_cnt) hong2_cnt,
                    SUM(m_cnt) m_cnt,
                    SUM(v2_cnt) v2_cnt,
                    sum(total) total,
                                
                    SUM(a) t_o,
                    SUM(b) t_x,
                    SUM(c) t_n,
                    SUM(d) t_e,
                    
                    SUM(e) d_o,
                    SUM(f) d_x,

                    (SUM(a)+SUM(e)) dt_o, 
                    (SUM(b)+SUM(f)) dt_x, 
                                
                    SUM(e)+SUM(f) d_tot,
                    SUM(a)+SUM(b)+SUM(c)+SUM(d) t_tot,
                    
                    SUM(g) dt_tot,
                    
                    SUM(h) r_o,
                    SUM(i) r_x,
                    SUM(h) r_e,
                    SUM(k) done,
                    SUM(l) r_tot,
                                
                    SUM(m) s_1,
                    round((SUM(m) / total),2) s_2
                                FROM (
                SELECT a.cpid,
                        COUNT(*) total
                        ,SUM(CASE vrfc_type_sub WHEN 'D1' THEN 1 ELSE 0 END) hong1_cnt
                        ,SUM(CASE vrfc_type_sub WHEN 'D2' THEN 1 ELSE 0 END) hong2_cnt
                        ,SUM(CASE vrfc_type_sub WHEN 'M1' THEN 1 ELSE 0 END) m_cnt
                        ,SUM(CASE vrfc_type_sub WHEN 'O1' THEN 1 ELSE 0 END) v2_cnt
                        ,SUM(CASE vrfc_type_sub WHEN 'T1' THEN 1 ELSE 0 END) t_cnt
                        , 0 a,0 b,0 c,0 d,0 e,0 f,0 g,0 h,0 i,0 j,0 k, 0 l, 0 m
                FROM v2_article_info a
                INNER JOIN v2_vrfc_req b ON a.vr_sq = b.vr_sq
                WHERE b.insert_tm BETWEEN CONCAT('{$data['sdate']} 00:00:00') AND CONCAT('{$data['edate']} 23:59:59')
                GROUP BY a.cpid
                                
                                UNION ALL
                                
                SELECT cpid
                        ,0 ,0 ,0 ,0 ,0 ,0
                        ,t_o,t_x,t_n,t_e
                        ,d_o,d_x
                        ,(t_o+t_x+t_n+t_e+d_o+d_x) AS dt_tot 
                        ,r_o,r_x,r_e
                        ,(d_d+d_t) AS done 
                        ,(r_o+r_x+r_e) AS r_tot 
                        ,(r_o+r_e+d_d+d_t) AS s_1
                FROM (
                    SELECT cpid, 
                    SUM(CASE gbn_cd WHEN 'T0201' THEN cnt ELSE 0 END) t_o, 
                    SUM(CASE gbn_cd WHEN 'T0202' THEN cnt ELSE 0 END) t_x, 
                    SUM(CASE gbn_cd WHEN 'T0203' THEN cnt ELSE 0 END) t_n, 
                    SUM(CASE gbn_cd WHEN 'T0204' THEN cnt ELSE 0 END) t_e, 
                    SUM(CASE gbn_cd WHEN 'D0201' THEN cnt ELSE 0 END) d_o, 
                    SUM(CASE gbn_cd WHEN 'D0202' THEN cnt ELSE 0 END) d_x, 
                    SUM(CASE gbn_cd WHEN 'R0101' THEN cnt ELSE 0 END) r_o, 
                    SUM(CASE gbn_cd WHEN 'R0102' THEN cnt ELSE 0 END) r_x, 
                                        SUM(CASE gbn_cd WHEN 'R0103' THEN cnt ELSE 0 END) r_e, 
                                        SUM(CASE gbn_cd WHEN 'D0205' THEN cnt ELSE 0 END) d_d, 
                                        SUM(CASE gbn_cd WHEN 'T0206' THEN cnt ELSE 0 END) d_t  
                                        FROM v2_st_daily
                                        WHERE st_date BETWEEN CONCAT('{$data['sdate']} 00:00:00') AND CONCAT('{$data['edate']} 23:59:59')
                                        GROUP BY cpid
                                    )A
                                ) table1
                GROUP BY cpid ";


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

        return $query->getResultArray();
    }
}