<?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,a.subdist_name,year,
	wk_01,
wk_02,
wk_03,
wk_04,
wk_05,
wk_06,
wk_07,
wk_08,
wk_09,
wk_10,
wk_11,
wk_12,
wk_13,
wk_14,
wk_15,
wk_16,
wk_17,
wk_18,
wk_19,
wk_20,
wk_21,
wk_22,
wk_23,
wk_24,
wk_25,
wk_26,
wk_27,
wk_28,
wk_29,
wk_30,
wk_31,
wk_32,
wk_33,
wk_34,
wk_35,
wk_36,
wk_37,
wk_38,
wk_39,
wk_40,
wk_41,
wk_42,
wk_43,
wk_44,
wk_45,
wk_46,
wk_47,
wk_48,
wk_49,
wk_50,
wk_51,
wk_52,
wk_53,
loc_01,
loc_02,
loc_03,
loc_04,
loc_05,
loc_06,
loc_07,
loc_08,
loc_09,
loc_10,
loc_11,
loc_12,
loc_13,
loc_14,
loc_15,
loc_16,
loc_17,
loc_18,
loc_19,
loc_20,
loc_21,
loc_22,
loc_23,
loc_24,
loc_25,
loc_26,
loc_27,
loc_28,
loc_29,
loc_30,
loc_31,
loc_32,
loc_33,
loc_34,
loc_35,
loc_36,
loc_37,
loc_38,
loc_39,
loc_40,
loc_41,
loc_42,
loc_43,
loc_44,
loc_45,
loc_46,
loc_47,
loc_48,
loc_49,
loc_50,
loc_51,
loc_52,
loc_53,
loc_01,
loc_02,
loc_03,
loc_04,
loc_05,
loc_06,
loc_07,
loc_08,
loc_09,
loc_10,
loc_11,
loc_12,
loc_13,
loc_14,
loc_15,
loc_16,
loc_17,
loc_18,
loc_19,
loc_20,
loc_21,
loc_22,
loc_23,
loc_24,
loc_25,
loc_26,
loc_27,
loc_28,
loc_29,
loc_30,
loc_31,
loc_32,
loc_33,
loc_34,
loc_35,
loc_36,
loc_37,
loc_38,
loc_39,
loc_40,
loc_41,
loc_42,
loc_43,
loc_44,
loc_45,
loc_46,
loc_47,
loc_48,
loc_49,
loc_50,
loc_51,
loc_52,
loc_53,
loc_01,
loc_02,
loc_03,
loc_04,
loc_05,
loc_06,
loc_07,
loc_08,
loc_09,
loc_10,
loc_11,
loc_12,
loc_13,
loc_14,
loc_15,
loc_16,
loc_17,
loc_18,
loc_19,
loc_20,
loc_21,
loc_22,
loc_23,
loc_24,
loc_25,
loc_26,
loc_27,
loc_28,
loc_29,
loc_30,
loc_31,
loc_32,
loc_33,
loc_34,
loc_35,
loc_36,
loc_37,
loc_38,
loc_39,
loc_40,
loc_41,
loc_42,
loc_43,
loc_44,
loc_45,
loc_46,
loc_47,
loc_48,
loc_49,
loc_50,
loc_51,
loc_52,
loc_53,
st_01,
st_02,
st_03,
st_04,
st_05,
st_06,
st_07,
st_08,
st_09,
st_10,
st_11,
st_12,
st_13,
st_14,
st_15,
st_16,
st_17,
st_18,
st_19,
st_20,
st_21,
st_22,
st_23,
st_24,
st_25,
st_26,
st_27,
st_28,
st_29,
st_30,
st_31,
st_32,
st_33,
st_34,
st_35,
st_36,
st_37,
st_38,
st_39,
st_40,
st_41,
st_42,
st_43,
st_44,
st_45,
st_46,
st_47,
st_48,
st_49,
st_50,
st_51,
st_52,
st_53,
st_01,
st_02,
st_03,
st_04,
st_05,
st_06,
st_07,
st_08,
st_09,
st_10,
st_11,
st_12,
st_13,
st_14,
st_15,
st_16,
st_17,
st_18,
st_19,
st_20,
st_21,
st_22,
st_23,
st_24,
st_25,
st_26,
st_27,
st_28,
st_29,
st_30,
st_31,
st_32,
st_33,
st_34,
st_35,
st_36,
st_37,
st_38,
st_39,
st_40,
st_41,
st_42,
st_43,
st_44,
st_45,
st_46,
st_47,
st_48,
st_49,
st_50,
st_51,
st_52,
st_53,
log  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, $start,$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']."')";
   }
	
   if($limit!='0')	
     $this->db->limit($limit, $start);

  /*$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);*/
  $query = $this->db->query("select a.kd_subdist,a.subdist_name,year,
	wk_01,
wk_02,
wk_03,
wk_04,
wk_05,
wk_06,
wk_07,
wk_08,
wk_09,
wk_10,
wk_11,
wk_12,
wk_13,
wk_14,
wk_15,
wk_16,
wk_17,
wk_18,
wk_19,
wk_20,
wk_21,
wk_22,
wk_23,
wk_24,
wk_25,
wk_26,
wk_27,
wk_28,
wk_29,
wk_30,
wk_31,
wk_32,
wk_33,
wk_34,
wk_35,
wk_36,
wk_37,
wk_38,
wk_39,
wk_40,
wk_41,
wk_42,
wk_43,
wk_44,
wk_45,
wk_46,
wk_47,
wk_48,
wk_49,
wk_50,
wk_51,
wk_52,
wk_53,
loc_01,
loc_02,
loc_03,
loc_04,
loc_05,
loc_06,
loc_07,
loc_08,
loc_09,
loc_10,
loc_11,
loc_12,
loc_13,
loc_14,
loc_15,
loc_16,
loc_17,
loc_18,
loc_19,
loc_20,
loc_21,
loc_22,
loc_23,
loc_24,
loc_25,
loc_26,
loc_27,
loc_28,
loc_29,
loc_30,
loc_31,
loc_32,
loc_33,
loc_34,
loc_35,
loc_36,
loc_37,
loc_38,
loc_39,
loc_40,
loc_41,
loc_42,
loc_43,
loc_44,
loc_45,
loc_46,
loc_47,
loc_48,
loc_49,
loc_50,
loc_51,
loc_52,
loc_53,
loc_01,
loc_02,
loc_03,
loc_04,
loc_05,
loc_06,
loc_07,
loc_08,
loc_09,
loc_10,
loc_11,
loc_12,
loc_13,
loc_14,
loc_15,
loc_16,
loc_17,
loc_18,
loc_19,
loc_20,
loc_21,
loc_22,
loc_23,
loc_24,
loc_25,
loc_26,
loc_27,
loc_28,
loc_29,
loc_30,
loc_31,
loc_32,
loc_33,
loc_34,
loc_35,
loc_36,
loc_37,
loc_38,
loc_39,
loc_40,
loc_41,
loc_42,
loc_43,
loc_44,
loc_45,
loc_46,
loc_47,
loc_48,
loc_49,
loc_50,
loc_51,
loc_52,
loc_53,
loc_01,
loc_02,
loc_03,
loc_04,
loc_05,
loc_06,
loc_07,
loc_08,
loc_09,
loc_10,
loc_11,
loc_12,
loc_13,
loc_14,
loc_15,
loc_16,
loc_17,
loc_18,
loc_19,
loc_20,
loc_21,
loc_22,
loc_23,
loc_24,
loc_25,
loc_26,
loc_27,
loc_28,
loc_29,
loc_30,
loc_31,
loc_32,
loc_33,
loc_34,
loc_35,
loc_36,
loc_37,
loc_38,
loc_39,
loc_40,
loc_41,
loc_42,
loc_43,
loc_44,
loc_45,
loc_46,
loc_47,
loc_48,
loc_49,
loc_50,
loc_51,
loc_52,
loc_53,
st_01,
st_02,
st_03,
st_04,
st_05,
st_06,
st_07,
st_08,
st_09,
st_10,
st_11,
st_12,
st_13,
st_14,
st_15,
st_16,
st_17,
st_18,
st_19,
st_20,
st_21,
st_22,
st_23,
st_24,
st_25,
st_26,
st_27,
st_28,
st_29,
st_30,
st_31,
st_32,
st_33,
st_34,
st_35,
st_36,
st_37,
st_38,
st_39,
st_40,
st_41,
st_42,
st_43,
st_44,
st_45,
st_46,
st_47,
st_48,
st_49,
st_50,
st_51,
st_52,
st_53,
st_01,
st_02,
st_03,
st_04,
st_05,
st_06,
st_07,
st_08,
st_09,
st_10,
st_11,
st_12,
st_13,
st_14,
st_15,
st_16,
st_17,
st_18,
st_19,
st_20,
st_21,
st_22,
st_23,
st_24,
st_25,
st_26,
st_27,
st_28,
st_29,
st_30,
st_31,
st_32,
st_33,
st_34,
st_35,
st_36,
st_37,
st_38,
st_39,
st_40,
st_41,
st_42,
st_43,
st_44,
st_45,
st_46,
st_47,
st_48,
st_49,
st_50,
st_51,
st_52,
st_53,
log  from mst_subdis a LEFT JOIN week_backup b ON b.kd_subdist = a.kd_subdist WHERE" . $keyword .' ORDER BY a.subdist_name ASC '); 
  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,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_week($startperiod,$endperiod,$tahun){
  //$this->db->select("fiscal_year,period,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
   if (($startperiod != '0' && $endperiod != '0' ) && ($startperiod != '' && $endperiod != ''))
     $where = ' period>='.$startperiod.' AND period <= '.$endperiod;
   else if ($tahun!='' && $tahun !='0')
	   $where = ' period>='.$startperiod.' AND period <= '.$endperiod;
   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($startperiod,$endperiod,$tahun){
  //$this->db->select("fiscal_year,period,month,start_date,end_date,week"); 
  //$this->db->from('mst_calendar');
  
  if (($startperiod != '0' && $endperiod != '0' ) && ($startperiod != '' && $endperiod != ''))
     $where = ' period>='.$startperiod.' AND period <= '.$endperiod;
  else if ($tahun!='' && $tahun !='0')
	   $where = ' period>='.$startperiod.' AND period <= '.$endperiod;
  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($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('kd_subdist', '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,$startperiod,$endperiod,$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 (($startperiod != '0' && $endperiod != '0' && $tahun != '0') && ($startperiod != '' && $endperiod != '' && $tahun!= '' ))
     $keyword_prd =  ' AND period>='.$startperiod.' AND period <= '.$endperiod.' AND fiscal_year=' .$tahun;
   else if ($startperiod!='' && $endperiod!='' )
	   $keyword_prd = ' AND period>='.$startperiod.' AND period <= '.$endperiod;
   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_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";
	   
   return $query = $this->db->query($sql);   
 }
 
}
?>
