<?php
class Dashboard_model extends CI_Model {
 
 public function record_count($subdist,$tahun) {     
    $keyword = ' 1 = 1';  
    if($subdist !='0' && $subdist !='')
  	  $keyword .= " AND b.kd_subdist = '".$subdist."'"; 
    else if ($tahun !='0' && $tahun !='') 
	  $keyword .= " AND year = '".$tahun."'"; 
	  
	 $data['id'] = $_COOKIE['oc_username'];
     if(!empty($data['id'])){
      $keyword .= " AND kd_reg IN ('".$data['id']."')";
     }
   
	/*$this->db->select('*');
    $this->db->from('mst_subdis a');
    $this->db->join('week_backup b', 'b.kd_subdist = a.kd_subdist','left');
    $this->db->where($keyword);*/
    $query = $this->db->query("SELECT a.kd_subdist AS subdist_id,a.subdist_name,b.*
			  from mst_subdis a LEFT JOIN week_backup b ON b.kd_subdist = a.kd_subdist WHERE" . $keyword);
    //$query = $this->db->get();
	
    if($query->num_rows() > 0)
      return $query->num_rows();
    else
     return NULL;
  
  }
	
 public function Mrecord_count($subdist,$tahun) {     
    $keyword = '1 = 1';  
    if($subdist !='0' && $subdist !='')
  	  $keyword .= " AND mtrx_monitoring.kd_subdist = '".$subdist."'"; 
    else if ($tahun !='0' && $tahun !='') 
	  $keyword .= " AND year = '".$tahun."'"; 
	  
	 $data['id'] = $_COOKIE['oc_username'];
     if(!empty($data['id'])){
      $keyword .= " AND kd_reg IN ('".$data['id']."')";
     }
	 $this->db->order_by('mtrx_monitoring.kd_subdist', 'asc' );
     $this->db->select('mtrx_monitoring.kd_subdist,year,GROUP_CONCAT(DISTINCT backup_date ORDER BY backup_date ASC SEPARATOR "|" ) as backup_date');
     $this->db->from('mtrx_monitoring');
     $this->db->join('mst_subdis', 'mtrx_monitoring.kd_subdist = mst_subdis.kd_subdist');
     $this->db->where($keyword);
     $this->db->group_by('kd_subdist,year');
     $query = $this->db->get();
	
    if($query->num_rows() > 0)
      return $query->num_rows();
    else
     return NULL;
  
  }	
   
 function fetch_data($limit, $offset,$subdist,$tahun) {   
  $keyword = ' 1 = 1 ';
  if($subdist !='0' && $subdist !='')
  	$keyword .= " AND b.kd_subdist = '".$subdist."'";   
 
  if($tahun !='0' && $tahun !='')
  	$keyword .= " AND b.year = '".$tahun."'";   
  //else
  //   $keyword .= " AND b.year IS NULL ";   
		
   $data['id'] =$_COOKIE['oc_username'];

   if(!empty($data['id'])){
     $keyword .= " AND kd_reg IN ('".$data['id']."')";
   }
	
 /*$this->db->order_by('b.kd_subdist', 'asc' );
  $this->db->select('*');
  $this->db->from('mst_subdis a');
  $this->db->join('week_backup b', 'b.kd_subdist = a.kd_subdist','left');
  $this->db->where($keyword);*/
  //die(print_r($start));
  $qry = "select a.kd_subdist AS subdist_id,a.subdist_name,b.*			
		  from mst_subdis a LEFT JOIN week_backup b ON b.kd_subdist = a.kd_subdist WHERE " . $keyword;

     $qry = $qry. " ORDER BY a.subdist_name " ;
     if($limit!='0')	
     	 $qry = $qry. "LIMIT ".$offset.", ".$limit;
     
	 $query = $this->db->query($qry); 


  return $query; // = $this->db->get();
 }

function Mfetch_data($limit, $start,$subdist,$tahun) {   
  $keyword = '1 = 1';
  
  if($subdist !='0' && $subdist !='')
  	$keyword .= " AND mtrx_monitoring.kd_subdist = '".$subdist."'";   
 
  if($tahun !='0' && $tahun !='')
  	$keyword .= " AND year = '".$tahun."'";   
 
   $data['id'] =$_COOKIE['oc_username'];

   if(!empty($data['id'])){
     $keyword .= " AND kd_reg IN ('".$data['id']."')";
   }
	
  if($limit!='0')	
     $this->db->limit($limit, $start);
  
  $this->db->order_by('mtrx_monitoring.kd_subdist', 'asc' );
  $this->db->select('mtrx_monitoring.kd_subdist,year,subdist_name,GROUP_CONCAT(DISTINCT backup_date ORDER BY backup_date ASC SEPARATOR "|" ) as backup_date');
  $this->db->from('mtrx_monitoring');
  $this->db->join('mst_subdis', 'mtrx_monitoring.kd_subdist = mst_subdis.kd_subdist');
  $this->db->where($keyword);
  $this->db->group_by('kd_subdist,year,subdist_name');
  
  return $query = $this->db->get();
 }

 
 function get_curr_week(){
  $this->db->select("fiscal_year,week,month,start_date,end_date,week"); 
  $this->db->from('mst_calendar');
  
  $where = 'start_date<=CURDATE() AND end_date >=CURDATE()';
  $this->db->where($where);  
  return  $this->db->get();    
 }
 
 function get_week($startweek,$endweek,$tahun){
  //$this->db->select("fiscal_year,week,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
   if (($startweek != '0' && $endweek != '0' ) && ($startweek != '' && $endweek != ''))
     $where = ' week>='.$startweek.' AND week <= '.$endweek;
   else if ($tahun!='' && $tahun !='0')
	   $where = ' week>='.$startweek.' AND week <= '.$endweek;
   else	 
      $where = 'start_date<=CURDATE() AND end_date >=CURDATE()';

  $query = $this->db->query("SELECT fiscal_year,week,month,start_date,end_date,week FROM mst_calendar WHERE " . $where); 

  //$this->db->where($where);
  return $query; // $this->db->get();    
 }

 function get_countweek($startweek,$endweek,$tahun){
  //$this->db->select("fiscal_year,week,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
  if (($startweek != '0' && $endweek != '0' ) && ($startweek != '' && $endweek != ''))
     $where = ' week>='.$startweek.' AND week <= '.$endweek;
  else if ($tahun!='' && $tahun !='0')
	   $where = ' week>='.$startweek.' AND week <= '.$endweek;
  else	 
      $where = '  start_date<=CURDATE() AND end_date >=CURDATE()';
	  
  $query = $this->db->query("SELECT fiscal_year,week,month,start_date,end_date,week FROM mst_calendar WHERE " . $where); 
  //$this->db->where($where);
  //$query =  $this->db->get();    
  if( $query->num_rows() > 0 ){
    foreach ( $query->result() as $row ) {
       $data[] = $row;
    }
   return $data;
  }
  return false;
 } 

function fetch_count_subdist($tahun){
   $oc_username =$_COOKIE['oc_username'];
   
   /*$keyword = 'active = 1';
   if(!empty($oc_username)){
     $keyword .= " AND kd_reg IN ('".$oc_username."')";
   }
   
 $this->db->where($keyword);
 $this->db->order_by('kd_subdist', 'asc' );  
 $query = $this->db->get('dashboard.mst_subdis' );*/
 
  $keyword = ' active = 1 ';
  if($tahun !='0' && $tahun !='')
  	$keyword .= " AND b.year = '".$tahun."'";   
 
   if(!empty($oc_username))
     $keyword .= " AND kd_reg IN ('".$oc_username."')";
 
 $query = $this->db->query("select a.kd_subdist from mst_subdis a LEFT JOIN week_backup b ON b.kd_subdist = a.kd_subdist WHERE" . $keyword); 
 
 if($query->num_rows() > 0)
      return $query->num_rows();
    else
     return NULL;
 }	 
  
function fetch_data_subdist(){
   $oc_username =$_COOKIE['oc_username'];
   
   $keyword = 'active = 1';
   if(!empty($oc_username)){
     $keyword .= " AND kd_reg IN ('".$oc_username."')";
   }
   
 $this->db->where($keyword);
 $this->db->order_by('subdist_name', 'asc' );  
 $query = $this->db->get('Dashboard.mst_subdis' );
 
 if($query->num_rows() > 0 ){
    foreach ( $query->result() as $row ) {
       $data[] = $row;
  }
   return $data;
  }
  return false;
 } 
 
 function fetch_all($subdist,$startweek,$endweek,$tahun) {   
   $keyword = 'WHERE 1 = 1';  
   $keyword_subdist = '';
   $keyword_year = '';
   $keyword_prd='';
   $keyword_reg = '';
   
  if($subdist !='0' && $subdist !='')
  	$keyword_subdist = " AND b.kd_subdist = '".$subdist."'";   
 
  if($tahun !='0' && $tahun !='')
  	$keyword_year = " AND b.year = '".$tahun."'";   
   
   if (($startweek != '0' && $endweek != '0' && $tahun != '0') && ($startweek != '' && $endweek != '' && $tahun!= '' ))
     $keyword_prd =  ' AND a.week between '.$startweek.' AND '.$endweek.' AND fiscal_year=' .$tahun;
   else if ($startweek!='' && $endweek!='' )
	   $keyword_prd = ' AND a.week between '.$startweek.' AND '.$endweek;
   else	 
      $keyword_prd = ' AND start_date<=CURDATE() AND end_date >=CURDATE()';

	
   $data['id'] =$_COOKIE['oc_username'];

   if(!empty($data['id'])){
     $keyword_reg = " AND kd_reg IN ('".$data['id']."')" ;
   }
   
   $keyall = $keyword . $keyword_subdist. $keyword_prd .$keyword_year.$keyword_reg;
   $keyprd = $keyword . $keyword_prd;
   
	$sql = "SELECT * FROM (
	SELECT COUNT(b.kd_subdist) AS jml,a.week,period  FROM mst_calendar a 
	LEFT JOIN backup b ON a.week = b.week
	LEFT JOIN mst_subdis c ON b.kd_subdist = c.kd_subdist ".$keyall."
	GROUP BY a.week,period
	
	UNION ALL
	SELECT 0,week,period FROM mst_calendar a ".$keyprd." 
	) A GROUP BY week,period
	ORDER BY week ASC";
	   
   //die(print_r($sql));	   
   return $query = $this->db->query($sql);   
 }
 
}
?>
