<?php
class Mu_mdl extends Base_Model {
    function __construct() {
        parent::__construct();
    }
	
	function getAll($page,$rows,$key='',$dtdari='',$dtsampai='',$kd_cmpy=''){
		// $dtdari = $this->dmys2ymd($dtdari);
		// $dtsampai = $this->dmys2ymd($dtsampai);
		
		$offset = ($page-1)*$rows;
		$result = array();
		
		$q01="SELECT MAX(opname_date) AS beg_date FROM mat_opnamehdr_sub WHERE mat_type='".$this->mat_type."' AND opname_date < '".dmys2ymd($dtdari)."' ORDER BY opname_date DESC LIMIT 1 ";
		// echo $q01;exit();
		$run=$this->db->query($q01)->row();
		// echo $run->beg_date;exit();
		$beg_date=($run->beg_date=="")?"0000-00-00":$run->beg_date;
		// Query Pengeluaran -ir
		$tr_out_periode = "
		LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_out_periode
			FROM `mat_outdet_sub` tr_det
			INNER JOIN `mat_outhdr_sub` tr_head
			ON tr_det.`matout_id` = tr_head.`matout_id`
			WHERE tr_head.`matout_date` >= '".dmys2ymd($dtdari)."' AND tr_head.`matout_date` <= '".dmys2ymd($dtsampai)."'
			GROUP BY tr_det.`mat_id`) AS tr_out_periode
		ON mat.`KdBarang` = tr_out_periode.mat_id";
		if($this->url == site_url('mumat')){
		}
		
		if($this->url == site_url('mufg')){
		}
		
		if($this->url == site_url('mumac')){
			
		}
		
		if($this->url == site_url('musc')){
		}
		
		if($this->url == site_url('muwip')){
		}
		
		$q = "SELECT mat.`KdBarang`,mat.`NmBarang`,mat.Sat,
			  mat.PartNo,
			  SUM(IFNULL(beg_bal.`qty_beg_bal`,0) + IFNULL(tr_in.`qty_in`,0) - IFNULL(tr_out.`qty_out`,0) + IFNULL(beg_adjust.`qty_beg_adjust`,0)) AS qty_beg, 	  
			  SUM(IFNULL(tr_in_periode.qty_in_periode,0) + IFNULL(adjust_period.`qty_adjust_in`,0)) AS qty_in,
			  SUM(IFNULL(tr_out_periode.qty_out_periode,0) + IFNULL(adjust_period.`qty_adjust_out`,0)) AS qty_out,
			  SUM(IFNULL(end_bal.qty_end_bal,0)) AS qty_bal,
			  SUM(IFNULL(tr_qty_opname.qty_opn,0)) AS qty,
			  0 AS qty_end, FORMAT(0,2) AS qty_diff FROM mst_barang mat 
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_beg_bal
						  FROM mat_opnamedet_sub tr_det
						  INNER JOIN `mat_opnamehdr_sub` tr_head
						  ON tr_det.`opname_id` = tr_head.`opname_id`
						  WHERE tr_head.`opname_date` =  '".$beg_date."'
						  GROUP BY tr_det.`mat_id`) AS beg_bal
			   ON mat.`KdBarang` = beg_bal.mat_id
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_in
						  FROM `mat_incdet_sub` tr_det 
							INNER JOIN `mat_inchdr_sub` tr_head
						  ON tr_det.`matin_id` = tr_head.`matin_id`
						  WHERE tr_head.`matin_date` > '".$beg_date."' AND tr_head.`matin_date` < '".dmys2ymd($dtdari)."'
						  GROUP BY tr_det.`mat_id`) AS tr_in		  
			   ON mat.`KdBarang` = tr_in.mat_id 
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_out
						  FROM `mat_outdet_sub` tr_det
						  INNER JOIN `mat_outhdr_sub` tr_head
						  ON tr_det.`matout_id` = tr_head.`matout_id`
						  WHERE tr_head.`matout_date` > '".$beg_date."' AND tr_head.`matout_date` < '".dmys2ymd($dtdari)."'
						  GROUP BY tr_det.`mat_id`) AS tr_out
			   ON mat.`KdBarang` = tr_out.mat_id 	 
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in` - tr_det.`qty_out`) AS qty_beg_adjust
						  FROM `mat_opnamedet_sub` tr_det
						  INNER JOIN `mat_opnamehdr_sub` tr_head
						  ON tr_det.`opname_id` = tr_head.`opname_id`
						  WHERE tr_head.`opname_date` =  '".$beg_date."'
						  GROUP BY tr_det.`mat_id`) AS beg_adjust   
			   ON mat.`KdBarang` = beg_adjust.mat_id 		  
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_in_periode
						  FROM `mat_incdet_sub` tr_det 
							INNER JOIN `mat_inchdr_sub` tr_head
						  ON tr_det.`matin_id` = tr_head.`matin_id`
						  WHERE tr_head.`matin_date` >=  '".dmys2ymd($dtdari)."' AND tr_head.`matin_date` <=  '".dmys2ymd($dtsampai)."'
						  GROUP BY tr_det.`mat_id`) AS tr_in_periode
			   ON mat.`KdBarang` = tr_in_periode.mat_id 
				 $tr_out_periode
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in`) AS qty_adjust_in,SUM(tr_det.`qty_out`) AS qty_adjust_out
						  FROM `mat_opnamedet_sub` tr_det
						  INNER JOIN `mat_opnamehdr_sub` tr_head
						  ON tr_det.`opname_id` = tr_head.`opname_id`
						  WHERE tr_head.`opname_date` >=  '".dmys2ymd($dtdari)."' AND tr_head.`opname_date` <=  '".dmys2ymd($dtsampai)."'
						  GROUP BY tr_det.`mat_id`) AS adjust_period
			   ON mat.`KdBarang` = adjust_period.mat_id 
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in` - tr_det.`qty_out`) AS qty_end_bal
						  FROM `mat_opnamedet_sub` tr_det
						  INNER JOIN `mat_opnamehdr_sub` tr_head
						  ON tr_det.`opname_id` = tr_head.`opname_id`
						  WHERE tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
						  GROUP BY tr_det.`mat_id`) AS end_bal
			   ON mat.`KdBarang` = end_bal.mat_id 
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_opn
						  FROM `mat_opnamedet_sub` tr_det
						  INNER JOIN `mat_opnamehdr_sub` tr_head
						  ON tr_det.`opname_id` = tr_head.`opname_id`
						  WHERE tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
						  GROUP BY tr_det.`mat_id`) as tr_qty_opname
		       ON mat.`KdBarang` = tr_qty_opname.mat_id  
			   WHERE mat.TpBarang ".$this->tp_barang." "; 
		
		if ($key != ""){  
			$q .= " AND (mat.KdBarang LIKE '%$key%' OR mat.NmBarang LIKE '%$key%') ";
		}
		
		// if($dtdari != '' && $dtsampai != ''):
		// 	$q .= " AND TgDaf BETWEEN '".$this->dmys2ymd($dtdari)."' AND '".$this->dmys2ymd($dtsampai)."' ";
		// endif;
		// die($q);
		$q .= " GROUP BY mat.KdBarang
	   			ORDER BY KdBarang ASC";
					 
		// echo $q;exit();
		
		$rstot=$this->db->query($q)->result();
	
		//$q .= " LIMIT $offset,$rows";
		$rs=$this->db->query($q)->result();
		
		$result["total"] = count($rstot);
		$result["rows"] = $rs;
		
		return $result;
	}
	function getAll2($page,$rows,$key='',$dtdari='',$dtsampai='',$kd_cmpy=''){
		// $dtdari = $this->dmys2ymd($dtdari);
		// $dtsampai = $this->dmys2ymd($dtsampai);
		
		$offset = ($page-1)*$rows;
		$result = array();
		$q01="SELECT MAX(opname_date) AS beg_date FROM mat_opnamehdr_sub WHERE mat_type='".$this->mat_type."' AND opname_date < '".dmys2ymd($dtdari)."' ORDER BY opname_date DESC LIMIT 1 ";
		// echo $q01;exit();
		$run=$this->db->query($q01)->row();
		// echo $run->beg_date;exit();
		$beg_date=($run->beg_date=="")?"0000-00-00":$run->beg_date;
		// Query Pengeluaran -ir
		$tr_out_periode = "
		LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_out_periode
			FROM `mat_outdet_sub` tr_det
			INNER JOIN `mat_outhdr_sub` tr_head
			ON tr_det.`matout_id` = tr_head.`matout_id`
			WHERE tr_head.`matout_date` >= '".dmys2ymd($dtdari)."' AND tr_head.`matout_date` <= '".dmys2ymd($dtsampai)."'
			GROUP BY tr_det.`mat_id`) AS tr_out_periode
		ON mat.`KdBarang` = tr_out_periode.mat_id";
		if($this->url == site_url('mumat')){
		}
		
		if($this->url == site_url('mufg')){
		}
		
		if($this->url == site_url('mumac')){
			
		}
		
		if($this->url == site_url('musc')){
		}
		
		if($this->url == site_url('muwip')){
		}
		
		$q = "SELECT mat.`KdBarang`,mat.`NmBarang`,mat.Sat,
			  mat.PartNo,
			  SUM(IFNULL(beg_bal.`qty_beg_bal`,0) + IFNULL(tr_in.`qty_in`,0) - IFNULL(tr_out.`qty_out`,0) + IFNULL(beg_adjust.`qty_beg_adjust`,0)) AS qty_beg, 	  
			  SUM(IFNULL(tr_in_periode.qty_in_periode,0) + IFNULL(adjust_period.`qty_adjust_in`,0)) AS qty_in,
			  SUM(IFNULL(tr_out_periode.qty_out_periode,0) + IFNULL(adjust_period.`qty_adjust_out`,0)) AS qty_out,
			  SUM(IFNULL(end_bal.qty_end_bal,0)) AS qty_bal,
			  SUM(IFNULL(tr_qty_opname.qty_opn,0)) AS qty,
			  0 AS qty_end, FORMAT(0,2) AS qty_diff FROM mst_barang mat 
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_beg_bal
						  FROM mat_opnamedet_sub tr_det
						  INNER JOIN `mat_opnamehdr_sub` tr_head
						  ON tr_det.`opname_id` = tr_head.`opname_id`
						  WHERE tr_head.`opname_date` =  '".$beg_date."'
						  GROUP BY tr_det.`mat_id`) AS beg_bal
			   ON mat.`KdBarang` = beg_bal.mat_id
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_in
						  FROM `mat_incdet_sub` tr_det 
							INNER JOIN `mat_inchdr_sub` tr_head
						  ON tr_det.`matin_id` = tr_head.`matin_id`
						  WHERE tr_head.`matin_date` > '".$beg_date."' AND tr_head.`matin_date` < '".dmys2ymd($dtdari)."'
						  GROUP BY tr_det.`mat_id`) AS tr_in		  
			   ON mat.`KdBarang` = tr_in.mat_id 
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_out
						  FROM `mat_outdet_sub` tr_det
						  INNER JOIN `mat_outhdr_sub` tr_head
						  ON tr_det.`matout_id` = tr_head.`matout_id`
						  WHERE tr_head.`matout_date` > '".$beg_date."' AND tr_head.`matout_date` < '".dmys2ymd($dtdari)."'
						  GROUP BY tr_det.`mat_id`) AS tr_out
			   ON mat.`KdBarang` = tr_out.mat_id 	 
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in` - tr_det.`qty_out`) AS qty_beg_adjust
						  FROM `mat_opnamedet_sub` tr_det
						  INNER JOIN `mat_opnamehdr_sub` tr_head
						  ON tr_det.`opname_id` = tr_head.`opname_id`
						  WHERE tr_head.`opname_date` =  '".$beg_date."'
						  GROUP BY tr_det.`mat_id`) AS beg_adjust   
			   ON mat.`KdBarang` = beg_adjust.mat_id 		  
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_in_periode
						  FROM `mat_incdet_sub` tr_det 
							INNER JOIN `mat_inchdr_sub` tr_head
						  ON tr_det.`matin_id` = tr_head.`matin_id`
						  WHERE tr_head.`matin_date` >=  '".dmys2ymd($dtdari)."' AND tr_head.`matin_date` <=  '".dmys2ymd($dtsampai)."'
						  GROUP BY tr_det.`mat_id`) AS tr_in_periode
			   ON mat.`KdBarang` = tr_in_periode.mat_id 
				 $tr_out_periode
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in`) AS qty_adjust_in,SUM(tr_det.`qty_out`) AS qty_adjust_out
						  FROM `mat_opnamedet_sub` tr_det
						  INNER JOIN `mat_opnamehdr_sub` tr_head
						  ON tr_det.`opname_id` = tr_head.`opname_id`
						  WHERE tr_head.`opname_date` >=  '".dmys2ymd($dtdari)."' AND tr_head.`opname_date` <=  '".dmys2ymd($dtsampai)."'
						  GROUP BY tr_det.`mat_id`) AS adjust_period
			   ON mat.`KdBarang` = adjust_period.mat_id 
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in` - tr_det.`qty_out`) AS qty_end_bal
						  FROM `mat_opnamedet_sub` tr_det
						  INNER JOIN `mat_opnamehdr_sub` tr_head
						  ON tr_det.`opname_id` = tr_head.`opname_id`
						  WHERE tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
						  GROUP BY tr_det.`mat_id`) AS end_bal
			   ON mat.`KdBarang` = end_bal.mat_id 
			   LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_opn
						  FROM `mat_opnamedet_sub` tr_det
						  INNER JOIN `mat_opnamehdr_sub` tr_head
						  ON tr_det.`opname_id` = tr_head.`opname_id`
						  WHERE tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
						  GROUP BY tr_det.`mat_id`) as tr_qty_opname
		       ON mat.`KdBarang` = tr_qty_opname.mat_id  
			   WHERE mat.TpBarang ".$this->tp_barang." "; 
		
		if ($key != ""){  
			$q .= " AND (mat.KdBarang LIKE '%$key%' OR mat.NmBarang LIKE '%$key%') ";
		}
		
		// if($dtdari != '' && $dtsampai != ''):
		// 	$q .= " AND TgDaf BETWEEN '".$this->dmys2ymd($dtdari)."' AND '".$this->dmys2ymd($dtsampai)."' ";
		// endif;
		// die($q);
		$q .= " GROUP BY mat.KdBarang
	   			ORDER BY CF, KdBarang ASC";
		// echo $q;exit();
		
		$rstot=$this->db->query($q)->result();
	
		$q .= " LIMIT $offset,$rows";
		// echo $q;exit();
		$rs=$this->db->query($q)->result();
		
		$result["total"] = count($rstot);
		$result["rows"] = $rs;
		
		return $result;
	}
	
}