<?php

class Kswip_mdl extends Ks_mdl {

    function __construct() {

        parent::__construct();
		$this->pk = 'matin_id';
    }
	
	function getAll($page,$rows,$key='',$dtdari='',$dtsampai='',$kd_cmpy='',$line=''){
		//$kd_cmpy=$this->session->userdata('kd_cmpy');
		$dtdari = $this->dmys2ymd($dtdari);
		$dtsampai = $this->dmys2ymd($dtsampai);
		
		$offset = ($page-1)*$rows;
		$result = array();
		
		
		$wline='';
		if ($line != ''){
			  $wline .= " AND line = '".$line."'";
		}
		
		$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_array();
		$beg_date=($rssa[0]['beg_date']=="")?"0000-00-00":$rssa[0]['beg_date'];
	
	
		$q = "SELECT mat.`KdBarang`,mat.`NmBarang`,mat.Sat,
		  SUM(IFNULL(beg_bal.`qty`,0) + IFNULL(tr_in.`qty`,0) - IFNULL(tr_out.`qty`,0) - IFNULL(tr_out_prod.`qty`,0)) AS qty_beg,
		  FORMAT(SUM(IFNULL(beg_bal.`qty`,0) + IFNULL(tr_in.`qty`,0) - IFNULL(tr_out_prod.`qty`,0)),2) AS fqty_beg ";
	$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."'
					  $wline
					  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_sub` tr_det
					  INNER JOIN `mat_outhdr_sub` tr_head
					  ON tr_det.`matout_id` = tr_head.`matout_id`
					  WHERE tr_head.`mat_type` =  '".$this->tp_barang."' 
					  AND tr_head.kd_cmpy = '".$kd_cmpy."'	
					  $wline
					  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 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='".$this->tp_barang."'
					  AND tr_head.matin_type =  '2'
					  AND tr_head.kd_cmpy = '".$kd_cmpy."'
					  $wline
					  AND tr_head.`matin_date` > '$beg_date' AND tr_head.`matin_date` <=  '".$dtdari."'
					  GROUP BY mat_id) AS tr_out_prod
		   ON mat.`KdBarang` = tr_out_prod.mat_id
		   LEFT JOIN (SELECT bomd.mat_id,SUM(bomd.qty*tr_det.`qty`) AS qty
					  FROM mat_bomhdr_sub bomh 
					  INNER JOIN mat_bomdet_sub bomd ON bomd.bom_id=bomh.bom_id
					  LEFT JOIN mat_incdet_sub tr_det ON tr_det.mat_id=bomh.fg_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 tr_head.kd_cmpy = '".$kd_cmpy."'
					  $wline
					  AND tr_head.`matin_date` > '$beg_date' AND tr_head.`matin_date` < '$dtdari'
					  GROUP BY bomd.mat_id) AS tr_out		  
		   ON mat.`KdBarang` = tr_out.mat_id  
		   WHERE mat.KdBarang = '$key'
		   GROUP BY mat.KdBarang
		   ORDER BY KdBarang ASC";
		//echo $q;   
		$rows=$this->db->query($q)->result_array();
		foreach($rows as $r) {
			$balance = $r['qty_beg'];
			$fbalance = $r['fqty_beg'];
		}
	
	$q="SELECT * FROM (
		SELECT mat_id AS KdBarang,matout_no AS trans_no,matout_date AS date,DATE_FORMAT(matout_date,'%d/%m/%Y') AS dmy,
		qty AS qty_receive,
		FORMAT(qty,2) AS fqty_receive,
		0 AS qty_product,
		0 AS fqty_product,
		0 AS qty_bom,
		0 AS fqty_bom,
		0 AS qty_convert,
		0 AS fqty_convert,
		tr_head.line,
		'' AS po_no,
		0 AS saldo,
		'd' AS dbcd
		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.`mat_type` =  '".$this->tp_barang."' 
		AND tr_head.kd_cmpy = '".$kd_cmpy."'
		$wline
		AND mat_id='$key' 
		AND tr_head.`matout_date` BETWEEN '".$dtdari."' AND '".$dtsampai."'
	UNION ALL
		SELECT mat_id AS KdBarang,matin_no AS trans_no,matin_date As date,DATE_FORMAT(matin_date,'%d/%m/%Y') AS dmy,
		0 AS qty_receive,
		0 AS fqty_receive,
		0 AS qty_product,
		0 AS fqty_product,
		0 AS qty_bom,
		0 AS fqty_bom,
		SUM(tr_det.`qty`) AS qty_convert,
		SUM(tr_det.`qty`) AS fqty_convert,
		tr_head.line,
		po_no,
		0 AS saldo,
		'c' AS dbcd
		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='".$this->tp_barang."'
		AND tr_head.matin_type =  '2'
		AND tr_head.kd_cmpy = '".$kd_cmpy."'
		$wline
		AND mat_id='$key'
		AND tr_head.`matin_date` > '$dtdari' AND tr_head.`matin_date` <=  '".$dtsampai."'
  GROUP BY mat_id
	UNION ALL
		SELECT bomd.mat_id AS KdBarang,matin_no AS trans_no,matin_date AS date,DATE_FORMAT(matin_date,'%d/%m/%Y') AS dmy,
		0 AS qty_receive,
		0 AS fqty_receive,
		IFNULL(tr_det.qty,0) AS qty_product,
		IFNULL(tr_det.qty,0) AS fqty_product,
		IFNULL(bomd.qty,0) AS qty_bom,
		IFNULL(bomd.qty,0) AS fqty_bom,
		IFNULL((bomd.qty*tr_det.qty),0) AS qty_convert,
		IFNULL((bomd.qty*tr_det.qty),0) AS fqty_convert,
		tr_head.line,
		po_no,
		0 AS saldo,
		'c' AS dbcd
		FROM mat_bomhdr_sub bomh 
		INNER JOIN mat_bomdet_sub bomd ON bomd.bom_id=bomh.bom_id
		LEFT JOIN mat_incdet_sub tr_det ON tr_det.mat_id=bomh.fg_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 tr_head.kd_cmpy = '".$kd_cmpy."'
		$wline
		AND bomd.mat_id='$key' 
		AND tr_head.`matin_date` BETWEEN  '".$dtdari."' AND '".$dtsampai."'		
		) AS gabungan ORDER BY date ASC, dbcd DESC, trans_no ASC 
		";	
		//echo $q;
	
		$rs=$this->db->query($q)->result_array();
		
		$temp=array();		
		$saldoarr=array(		
			'KdBarang'	=> '',
			'trans_no'	=> '',
			'date'		=> '',
			'dmy'		=> $period_from,
			'qty_receive'	=> '',
			'fqty_receive'	=> '',
			'qty_product'		=> 	'',
			'fqty_product'	=> 	'',
			'qty_bom'	=> '',
			'fqty_bom'	=> '',			
			'qty_convert'	=> '',
			'fqty_convert'	=> '',
			'line'	=> '',
			'po_no'	=> '',
			'saldo'		=> number_format($balance,2)
		);
		array_push($temp,$saldoarr);
		foreach ($rs as $r){		
			$balance=$balance + ($r['qty_receive'] - $r['qty_convert']);
			$r['saldo']=number_format($balance,2);
			array_push($temp,$r);
		}
		
		return $temp;
	}
	
}