<?php
class Routstanding_model extends CI_Model {
 
 public function record_count($subdist,$thn) {     
    $keyword = ' 1 = 1';  
    if($subdist !='0' && $subdist !='')
  	  $keyword .= " AND b.kd_subdist = '".$subdist."'"; 
    else if ($thn !='0' && $thn !='') 
	  $keyword .= " AND year = '".$thn."'"; 
	  
	 $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);
	// print_r($query);
//	die();	
	//$query = $this->db->get();
	
    if($query->num_rows() > 0)
      return $query->num_rows();
    else
     return NULL;

  }
	
 public function Mrecord_count($subdist,$thn) {     
    $keyword = '1 = 1';  
    if($subdist !='0' && $subdist !='')
  	  $keyword .= " AND mtrx_monitoring.kd_subdist = '".$subdist."'"; 
    else if ($thn !='0' && $thn !='') 
	  $keyword .= " AND year = '".$thn."'"; 
	  
	 $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,$thn) {   
  $keyword = ' 1 = 1 ';
  if($subdist !='0' && $subdist !='')
  	$keyword .= " AND b.kd_subdist = '".$subdist."'";   
 
  if($thn !='0' && $thn !='')
  	$keyword .= " AND b.year = '".$thn."'";   
  //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;
//    die(print_r($qry));
	 $query = $this->db->query($qry); 


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

function Mfetch_data($limit, $start,$subdist,$thn) {   
  $keyword = '1 = 1';
  
  if($subdist !='0' && $subdist !='')
  	$keyword .= " AND mtrx_monitoring.kd_subdist = '".$subdist."'";   
 
  if($thn !='0' && $thn !='')
  	$keyword .= " AND year = '".$thn."'";   
 
   $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,period,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_curr_period(){
  $this->db->select(" fiscal_year,period,month,start_date,end_date,week"); 
  $this->db->from('mst_calendar');
  $where = 'start_date<=CURDATE() or end_date >=CURDATE()';
  $this->db->where($where);  
  return  $this->db->get();    
 }
 
 function get_week($period,$thn){
  //$this->db->select("fiscal_year,period,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
   if (($period != '0' ) && ($period != ''))
      $where = ' period ='.$period;
   else if ($thn!='' && $thn !='0')
	  $where = ' period ='.$period;
   else	 
      $where = 'start_date<=CURDATE() AND end_date >=CURDATE()';

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

  //$this->db->where($where);
  return $query; // $this->db->get();    
 }
 
 function get_ytdweek($ytdawal,$thn){
  //$this->db->select("fiscal_year,period,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
   if (($ytdawal != '0' ) && ($ytdawal != ''))
	$where = ' period BETWEEN 1 AND '.$ytdawal;
   else if ($thn!='' && $thn !='0')
	$where = ' period BETWEEN 1 AND '.$ytdawal;
   else	 
	$where = '  start_date<=CURDATE() AND end_date >=CURDATE()';
	  
  $query = $this->db->query("SELECT fiscal_year,period,month,start_date,end_date,week FROM mst_calendar WHERE " . $where); 
	
  //$this->db->where($where);
  return $query; // $this->db->get();    
 }
 
 function get_period($period,$thn){
  //$this->db->select("fiscal_year,period,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
   if (($period != '0' ) && ($period != ''))
      $where = ' period ='.$period;
   else if ($thn!='' && $thn !='0')
	  $where = ' period ='.$period;
   else	 
      $where = 'start_date<=CURDATE() AND end_date >=CURDATE()';

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

 function get_countweek($period,$thn){
  //$this->db->select("fiscal_year,period,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
  if (($period != '0' ) && ($period != ''))
     $where = ' period ='.$period;
  else if ($thn!='' && $thn !='0')
	 $where = ' period ='.$period;
  else	 
     $where = '  start_date<=CURDATE() AND end_date >=CURDATE()';
	  
  $query = $this->db->query("SELECT fiscal_year,period,month,start_date,end_date,week FROM mst_calendar WHERE " . $where); 
  //die(print_r($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 get_cweekytdawal($ytdawal,$thn){
  //$this->db->select("fiscal_year,period,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
  if (($ytdawal != '0' ) && ($ytdawal != ''))
     $where = ' period BETWEEN 1 AND '.$ytdawal;
  else if ($thn!='' && $thn !='0')
	 $where = ' period BETWEEN 1 AND '.$ytdawal;
  else	 
     $where = '  start_date<=CURDATE() AND end_date >=CURDATE()';
	  
  $query = $this->db->query("SELECT fiscal_year,period,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 get_tigabulan($tigabulan,$thn){
  //$this->db->select("fiscal_year,period,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
  if (($tigabulan!= '0' ) && ($tigabulan != ''))
     $where = ' period ='.$tigabulan;
  else if ($thn!='' && $thn !='0')
	 $where = ' period ='.$tigabulan;
  else	 
      $where = '  start_date<=CURDATE() AND end_date >=CURDATE()';
	  
  $query = $this->db->query("SELECT fiscal_year,period,month,start_date,end_date,week FROM mst_calendar WHERE " . $where); 
	
  //$this->db->where($where);
  return $query; // $this->db->get();    
 }

function get_ctigabulan($tigabulan,$thn){
  //$this->db->select("fiscal_year,period,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
   if (($tigabulan!= '0' ) && ($tigabulan != ''))
     $where = ' period ='.$tigabulan;
  else if ($thn!='' && $thn !='0')
	 $where = ' period ='.$tigabulan;
  else	 
     $where = '  start_date<=CURDATE() AND end_date >=CURDATE()';
	  
  $query = $this->db->query("SELECT fiscal_year,period,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 get_countperiod($period,$thn){
  //$this->db->select("fiscal_year,period,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
  if (($period != '0' ) && ($period != ''))
      $where = ' period ='.$period;
  else if ($thn!='' && $thn !='0')
	  $where = ' period ='.$period;
  else	 
      $where = '  start_date<=CURDATE() AND end_date >=CURDATE()';
	  
  $query = $this->db->query("SELECT fiscal_year,period,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($thn){
   $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($thn !='0' && $thn !='')
  	$keyword .= " AND b.year = '".$thn."'";   
 
   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,$period,$thn) {   
   $keyword = 'WHERE 1 = 1';  
   //$keyword2 = ' AND period IN (SELECT z.period FROM mst_calendar z WHERE 1 = 1 AND start_date<= CURDATE() AND end_date >= CURDATE())';
   $keyword_subdist = '';
   $keyword_year = '';
   $keyword_prd='';
   $keyword_reg = '';
   
  if($subdist !='0' && $subdist !='')
  	$keyword_subdist = " AND b.kd_subdist = '".$subdist."'";   
 
  if($thn !='0' && $thn !='')
  	$keyword_year = " AND b.year = '".$thn."'";   
   if (($period != '0' && $thn != '0') && ($period != '' && $thn!= '' ))
     $keyword_prd =  ' AND period ='.$period.' AND fiscal_year=' .$thn;
   else if ($period!='' )
	   $keyword_prd = ' AND period ='.$period;
   else	 
   // $keyword_prd = ' AND start_date<=CURDATE() AND end_date >=CURDATE()';
   $keyword_prd = ' AND period IN (SELECT z.period FROM mst_calendar z WHERE 1 = 1 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;
  // die(print_r($keyprd));	     
	/*$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";
	*/
	
	$sql = "SELECT * FROM (
	SELECT COUNT(c.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);   
 }
 
}
?>
