<?php

class Muwip_mdl extends Mu_mdl {

    function __construct() {

        parent::__construct();
		$this->pk = 'matin_id';
    }
	
	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 kd_cmpy = '".$kd_cmpy."'
			  AND opname_date < '$dtdari' 
			  ORDER BY opname_date DESC 
			  LIMIT 1 ";
		$rssa=$this->db->query($q01)->result();
		$beg_date=($rssa[0]->beg_date=="")?"0000-00-00":$rssa[0]->beg_date;

		$q = "SELECT mat.`KdBarang`,mat.`NmBarang`,mat.Sat,
			  FORMAT(SUM(IFNULL(beg_bal.`qty`,0) + IFNULL(tr_in.`qty`,0) - IFNULL(tr_out.`qty`,0) - IFNULL(tr_sc.`qty`,0)),2) AS Fqty_beg, 	  
			  FORMAT(SUM(IFNULL(tr_in_periode.qty,0)),2) AS Fqty_in,
			  FORMAT(SUM(IFNULL(tr_out_periode.qty,0)),2) AS Fqty_out,
			  FORMAT(SUM(IFNULL(tr_sc_periode.qty,0)),2) AS Fqty_sc,
			  FORMAT(SUM(IFNULL(beg_bal.`qty`,0) + IFNULL(tr_in.`qty`,0) - IFNULL(tr_out.`qty`,0) + IFNULL(tr_in_periode.qty,0)-IFNULL(tr_out_periode.qty,0) - IFNULL(tr_sc_periode.`qty`,0)),2) AS Fqty_end,
			  FORMAT(SUM(IFNULL(tr_opname.qty,0)),2) AS Fqty_opn";
		$q .=" FROM mst_barang mat 
			   LEFT JOIN (SELECT mat_id,SUM(tr_det.`qty`) AS qty
						  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.mat_type='".$this->mat_type."' 
						  AND kd_cmpy = '".$kd_cmpy."'
						  AND tr_head.`opname_date` =  '$beg_date'
						  GROUP BY mat_id) AS beg_bal
			   ON mat.`KdBarang` = beg_bal.mat_id
			   LEFT JOIN (SELECT mat_id,SUM(tr_det.`qty`) AS qty
						  FROM `mat_outdet` tr_det
						  INNER JOIN `mat_outhdr` tr_head
						  ON tr_det.`matout_id` = tr_head.`matout_id`
						  WHERE tr_head.`mat_type` =  '".$this->tp_barang."' 
						  AND cust = '".$kd_cmpy."'	
						  AND tr_head.`matout_date` > '$beg_date' AND tr_head.`matout_date` < '$dtdari'
						  GROUP BY mat_id) AS tr_in		  
			   ON mat.`KdBarang` = tr_in.mat_id 
			   LEFT JOIN (SELECT oa.mat_id,SUM(oa.qty*tr_det.`qty`) AS qty
					  FROM ppic_fgmatcon oa 
					  LEFT JOIN mat_incdet_sub tr_det ON tr_det.mat_id=oa.matcon_id
					  LEFT JOIN mat_inchdr_sub tr_head ON tr_head.matin_id=tr_det.matin_id 
					  WHERE tr_head.`mat_type` =  '0' 
   					  AND tr_head.matin_type =  '5' 
					  AND kd_cmpy = '".$kd_cmpy."'
					  AND tr_head.`matin_date` > '$beg_date' AND tr_head.`matin_date` < '$dtdari'
					  GROUP BY mat_id) AS tr_out
			   ON mat.`KdBarang` = tr_out.mat_id 	  		  	    
			   LEFT JOIN (SELECT mat_id,SUM(tr_det.`qty`) AS qty
						  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.mat_type='12'
						  AND kd_cmpy = '".$kd_cmpy."' 
						  AND tr_head.`matin_date` >  '".$beg_date."' AND tr_head.`matin_date` <  '".$dtdari."'
						  GROUP BY mat_id) AS tr_sc
			   ON mat.`KdBarang` = tr_sc.mat_id  
			   LEFT JOIN (SELECT mat_id,SUM(tr_det.`qty`) AS qty
						  FROM `mat_outdet` tr_det
						  INNER JOIN `mat_outhdr` tr_head
						  ON tr_det.`matout_id` = tr_head.`matout_id`
						  WHERE tr_head.`mat_type` =  '".$this->tp_barang."' 
						  AND cust = '".$kd_cmpy."'	
						  AND tr_head.`matout_date` > '".$dtdari."' AND tr_head.`matout_date` <= '".$dtsampai."'
						  GROUP BY mat_id) AS tr_in_periode
			   ON mat.`KdBarang` = tr_in_periode.mat_id 
			   LEFT JOIN (SELECT oa.mat_id,SUM(oa.qty*tr_det.`qty`) AS qty
						  FROM ppic_fgmatcon oa 
						  LEFT JOIN mat_incdet_sub tr_det ON tr_det.mat_id=oa.matcon_id
						  LEFT JOIN mat_inchdr_sub tr_head ON tr_head.matin_id=tr_det.matin_id 
						  WHERE tr_head.`mat_type` =  '0' 
						  AND tr_head.matin_type =  '5' 
						  AND kd_cmpy = '".$kd_cmpy."'
						  AND tr_head.`matin_date` >= '".$dtdari."' AND tr_head.`matin_date` <= '".$dtsampai."'
						  GROUP BY mat_id) AS tr_out_periode
			   ON mat.`KdBarang` = tr_out_periode.mat_id
			   LEFT JOIN (SELECT mat_id,SUM(tr_det.`qty`) AS qty
						  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.mat_type='12'
						  AND kd_cmpy = '".$kd_cmpy."' 
						  AND tr_head.`matin_date` >=  '".$dtdari."' AND tr_head.`matin_date` <=  '".$dtsampai."'
						  GROUP BY mat_id) AS tr_sc_periode
			   ON mat.`KdBarang` = tr_sc_periode.mat_id  
			   LEFT JOIN (SELECT mat_id,SUM(tr_det.`qty`) AS qty
						  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.mat_type='".$this->mat_type."' 
						  AND kd_cmpy = '".$kd_cmpy."'
						  AND tr_head.`opname_date` = '".$dtsampai."'
						  GROUP BY mat_id) as tr_opname
			   ON mat.`KdBarang` = tr_opname.mat_id  
			   WHERE mat.TpBarang='".$this->tp_barang."' AND (mat.KdBarang LIKE '%$key%' OR mat.NmBarang LIKE '%$key%')
			   GROUP BY mat.KdBarang
			   ORDER BY KdBarang ASC";
		$q .= " LIMIT $offset,$rows"; 		
		//echo $q;
		$rs=$this->db->query($q)->result();
		
		$q02 = "SELECT COUNT(KdBarang) AS jml 
				FROM mst_barang WHERE TpBarang='".$this->tp_barang."'
				AND (KdBarang LIKE '%$key%' OR NmBarang LIKE '%$key%')";
		$rstot=$this->db->query($q02)->result();
		
		$result["total"] = $rstot[0]->jml;
		$result["rows"] = $rs;
		
		return $result;
	}
	
}