<?
defined('BASEPATH') OR exit('No direct script access allowed');

class m_news_comment extends CI_Model{

	// 생성자, 해당 모델이 생성될때 항상 실행되는 초기화작업
	function __construct(){
		parent::__construct();

        $this->load->database();
        $this->load->library('session');
	}

    #---------------------------------------------------------------------------
    # PURPOSE :	list - total count
    # CREATE  : 2018-03-12
    #---------------------------------------------------------------------------
	function news_comment_list_total_count($req_data){
	    # request
	    $search_type            = $req_data['search_type'];
        $search_keyword         = $req_data['search_keyword'];
        $search_news_type       = $req_data['search_news_type'];
        $search_comment_type    = $req_data['search_comment_type'];
        $search_category_code   = $req_data['search_category_code'];
        $search_theme_type      = $req_data['search_theme_type'];
        $content_type           = $req_data['content_type'];
        $search_today           = $req_data['search_today'];
        $search_today_best      = $req_data['search_today_best'];


        $now_date   = date("Y-m-d")." 00:00:00";
        $where_query = "";
        if($search_type!='' && $search_keyword!=''){
            $where_query.=" and a.".$search_type." like '%".$search_keyword."%'";
        }
        if($search_news_type!='')       $where_query.=" and c.news_type = '".$search_news_type."'";
        if($search_comment_type!='')    $where_query.=" and a.is_best = '".$search_comment_type."'";
        if($search_category_code!='')   $where_query.=" and c.category_code = '".$search_category_code."'";
        if($search_today!='')           $where_query.=" and a.reg_date >= '".$now_date."'";
        if($search_today_best!='')      $where_query.=" and a.reg_date >= '".$now_date."' and a.is_best = 'Y'";

        if($search_theme_type!='') {
            $sql = "select * from (select *, (select GROUP_CONCAT(DISTINCT theme_type ORDER BY theme_type DESC SEPARATOR ',') from theme where is_del='N' and content_idx=content_idx and  content_type='".$content_type."') as theme_types
                                    from comment
                                   where is_del = 'N' and content_type='".$content_type."') as a  
                    join news as c on (a.content_idx = c.idx)
                   where a.theme_types like '%".$search_theme_type."%' ".$where_query;
        } else {
            $sql = "select *
                      from comment as a
                      join news as c on (a.content_idx = c.idx)
                     where a.is_del = 'N' and a.content_type='".$content_type."' ".$where_query;

        }
        $res = $this->db->query($sql);
        $row = $res->num_rows();

        return $row;

    }


	#---------------------------------------------------------------------------
    # PURPOSE :	list
    # CREATE  :	2018-03-07
    #---------------------------------------------------------------------------
	function news_comment_list($req_data){
        # request
        $offset                 = $req_data['offset'];
        $limit                  = $req_data['limit'];

        $search_type            = $req_data['search_type'];
        $search_keyword         = $req_data['search_keyword'];
        $search_news_type       = $req_data['search_news_type'];
        $search_comment_type    = $req_data['search_comment_type'];
        $search_category_code   = $req_data['search_category_code'];
        $search_theme_type      = $req_data['search_theme_type'];
        $content_type           = $req_data['content_type'];
        $search_today           = $req_data['search_today'];
        $search_today_best      = $req_data['search_today_best'];

        $now_date   = date("Y-m-d")." 00:00:00";

        $where_query = "";
        if($search_type!='' && $search_keyword!=''){
            $where_query.=" and a.".$search_type." like '%".$search_keyword."%'";
        }
        if($search_news_type!='')       $where_query.=" and c.news_type = '".$search_news_type."'";
        if($search_comment_type!='')    $where_query.=" and a.is_best = '".$search_comment_type."'";
        if($search_category_code!='')   $where_query.=" and c.category_code = '".$search_category_code."'";
        if($search_today!='')           $where_query.=" and a.reg_date >= '".$now_date."'";
        if($search_today_best!='')      $where_query.=" and a.reg_date >= '".$now_date."' and a.is_best = 'Y'";

        if($search_theme_type!='') {
            $sql = "select a.*, c.title, c.press_name, c.reporter_name, c.news_type, a.theme_types, (select category_name from news_section where category_code = SUBSTRING(c.category_code, 1, 2)) as category_name1, (select category_name from news_section where category_code = SUBSTRING(c.category_code, 3, 2)) as category_name2, (select category_name from news_section where category_code = SUBSTRING(c.category_code, 5, 2)) as category_name3, (select comment from comment where is_del='N' and idx=a.p_idx and s_idx > 1) as p_comment, d.member_img
                      from (select *, (select GROUP_CONCAT(DISTINCT theme_type ORDER BY theme_type DESC SEPARATOR ',') from theme where is_del='N' and content_idx=content_idx and  content_type='".$content_type."') as theme_types
                                    from comment
                                   where is_del = 'N' and content_type='".$content_type."') as a  
                     join news as c on (a.content_idx = c.idx)
                     join member as d on (d.member_id = a.write_id)
                    where a.theme_types like '%".$search_theme_type."%' ".$where_query." order by a.reg_date desc limit $offset, $limit ";
        } else {
            $sql = "select a.*, c.title, c.press_name, c.reporter_name, c.news_type, (select GROUP_CONCAT(DISTINCT theme_type ORDER BY theme_type DESC SEPARATOR ',') from theme where is_del='N' and content_idx=content_idx and  content_type='".$content_type."') as theme_types, (select category_name from news_section where category_code = SUBSTRING(c.category_code, 1, 2)) as category_name1, (select category_name from news_section where category_code = SUBSTRING(c.category_code, 3, 2)) as category_name2, (select category_name from news_section where category_code = SUBSTRING(c.category_code, 5, 2)) as category_name3, (select comment from comment where is_del='N' and idx=a.p_idx and s_idx > 1) as p_comment, d.member_img
                      from comment as a
                      join news as c on (a.content_idx = c.idx)
                      join member as d on (d.member_id = a.write_id)
                     where a.is_del = 'N' and a.content_type='".$content_type."' ".$where_query." order by a.reg_date desc limit $offset, $limit ";

        }
        $res = $this->db->query($sql);
        $row = $res->result_array();

        return $row;




    }

    #---------------------------------------------------------------------------
    # PURPOSE :	new count
    # CREATE  :	2018-03-08
    #---------------------------------------------------------------------------
	function news_comment_new_count($req_data){
	    # request
	    $search_type            = $req_data['search_type'];
        $search_keyword         = $req_data['search_keyword'];
        $search_news_type       = $req_data['search_news_type'];
        $search_comment_type    = $req_data['search_comment_type'];
        $search_theme_type      = $req_data['search_theme_type'];
        $content_type           = $req_data['content_type'];
        $search_category_code   = $req_data['search_category_code'];

        $now_date   = date("Y-m-d")." 00:00:00";

        $where_query = "";
        if($search_type!='' && $search_keyword!=''){
            $where_query.=" and a.".$search_type." like '%".$search_keyword."%'";
        }
        if($search_news_type!='')       $where_query.=" and c.news_type = '".$search_news_type."'";
        if($search_comment_type!='')    $where_query.=" and a.is_best = '".$search_comment_type."'";
        if($search_category_code!='')   $where_query.=" and c.category_code = '".$search_category_code."'";
        if($search_theme_type!='') {
            $sql = "select * from (select *, (select GROUP_CONCAT(DISTINCT theme_type ORDER BY theme_type DESC SEPARATOR ',') from theme where is_del='N' and content_idx=content_idx and  content_type='".$content_type."') as theme_types
                                    from comment
                                   where is_del = 'N' and content_type='".$content_type."') as a  
                    join `news` as c on (a.content_idx = c.idx)
                   where a.theme_types like '%".$search_theme_type."%' 
                     and a.reg_date >='".$now_date."' ".$where_query;
        } else {
            $sql = "select *
                      from comment as a
                      join `news` as c on (a.content_idx = c.idx)
                     where a.is_del = 'N'
                       and a.content_type='".$content_type."' 
                       and a.reg_date >='".$now_date."' ".$where_query;
        }
        $res = $this->db->query($sql);
        $row = $res->num_rows();

        return $row;
    }


    #---------------------------------------------------------------------------
    # PURPOSE :	is_best_count
    # CREATE  :	2018-03-08
    #---------------------------------------------------------------------------
	function news_comment_is_best_count($req_data){
	    # request
	    $search_type            = $req_data['search_type'];
        $search_keyword         = $req_data['search_keyword'];
        $search_news_type       = $req_data['search_news_type'];
        $search_theme_type      = $req_data['search_theme_type'];
        $content_type           = $req_data['content_type'];
        $search_category_code   = $req_data['search_category_code'];

        $where_query = "";
        if($search_type!='' && $search_keyword!=''){
            $where_query.=" and a.".$search_type." like '%".$search_keyword."%'";
        }
        if($search_news_type!='')       $where_query.=" and c.news_type = '".$search_news_type."'";
        if($search_category_code!='')   $where_query.=" and c.category_code = '".$search_category_code."'";
        if($search_theme_type!='') {
            $sql = "select * from (select *, (select GROUP_CONCAT(DISTINCT theme_type ORDER BY theme_type DESC SEPARATOR ',') from theme where is_del='N' and content_idx=content_idx and  content_type='".$content_type."') as theme_types
                                    from comment
                                   where is_del = 'N' and content_type='".$content_type."') as a  
                    join `news` as c on (a.content_idx = c.idx)
                   where a.theme_types like '%".$search_theme_type."%' 
                     and a.is_best ='Y' ".$where_query;
        } else {
            $sql = "select *
                      from comment as a
                      join `news` as c on (a.content_idx = c.idx)
                     where a.is_del = 'N'
                       and a.content_type='".$content_type."' 
                       and a.is_best ='Y' ".$where_query;
        }
        $res = $this->db->query($sql);
        $row = $res->num_rows();

        return $row;
    }


    #---------------------------------------------------------------------------
    # PURPOSE :	is_best new count
    # CREATE  :	2018-03-08
    #---------------------------------------------------------------------------
	function news_comment_is_best_new_count($req_data){
	    # request
	    $search_type            = $req_data['search_type'];
        $search_keyword         = $req_data['search_keyword'];
        $search_news_type       = $req_data['search_news_type'];
        $search_theme_type      = $req_data['search_theme_type'];
        $content_type           = $req_data['content_type'];
        $search_category_code   = $req_data['search_category_code'];

        $now_date   = date("Y-m-d")." 00:00:00";

        $where_query = "";
        if($search_type!='' && $search_keyword!=''){
            $where_query.=" and a.".$search_type." like '%".$search_keyword."%'";
        }
        if($search_news_type!='')       $where_query.=" and c.news_type = '".$search_news_type."'";
        if($search_category_code!='')   $where_query.=" and c.category_code = '".$search_category_code."'";
        if($search_theme_type!='') {
            $sql = "select * from (select *, (select GROUP_CONCAT(DISTINCT theme_type ORDER BY theme_type DESC SEPARATOR ',') from theme where is_del='N' and content_idx=content_idx and  content_type='".$content_type."') as theme_types
                                    from comment
                                   where is_del = 'N' and content_type='".$content_type."') as a  
                    join `news` as c on (a.content_idx = c.idx)
                   where a.theme_types like '%".$search_theme_type."%' 
                     and a.is_best ='Y'
                     and a.reg_date >='".$now_date."' ".$where_query;
        } else {
            $sql = "select *
                      from comment as a
                      join `news` as c on (a.content_idx = c.idx)
                     where a.is_del = 'N'
                       and a.is_best ='Y'
                       and a.content_type='".$content_type."' 
                       and a.reg_date >='".$now_date."' ".$where_query;
        }
        $res = $this->db->query($sql);
        $row = $res->num_rows();

        return $row;
    }



    #---------------------------------------------------------------------------
    # PURPOSE :	is_del update
    # CREATE  : 2018-03-09
    #---------------------------------------------------------------------------
	function news_comment_is_del_update($req_data){
        # request
	    $idx            = $req_data['idx'];
        $comment_status = $req_data['comment_status'];
        $comment        = $req_data['comment'];

	    $user_id    = $this->session->userdata('Adm_Id');

        $sql = "update comment 
                   set comment_org = comment
                     , comment = '".$comment."'
                     , is_del = 'Y'
                     , comment_status = '".$comment_status."'
                     , update_id= '".$user_id."'
                     , update_date= now()
                 where idx= '".$idx."'";
        $row = $this->db->query($sql);


        return $row;

    }
    



}
?>