<?php

class Ks_mdl extends Base_Model {

    function __construct() {
        parent::__construct();
    }

	function getAll($page,$rows,$key='',$dtdari='',$dtsampai='',$kd_cmpy='',$line=''){
		$dtdari = $this->dmys2ymd($dtdari);
		$dtsampai = $this->dmys2ymd($dtsampai);
		
		$offset = ($page-1)*$rows;
		$result = array();
		
		$wsubkon='';
		if ($kd_cmpy != ''){
			  $wsubkon .= " AND kd_cmpy = '".$kd_cmpy."'";
		}
		
		$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."' 
			  $wsubkon
			  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_beg_bal`,0) + IFNULL(tr_in.`qty_in`,0) - IFNULL(tr_out.`qty_out`,0)) AS qty_beg,
		  FORMAT(SUM(IFNULL(beg_bal.`qty_beg_bal`,0) + IFNULL(tr_in.`qty_in`,0) - IFNULL(tr_out.`qty_out`,0)),2) AS fqty_beg ";
	$q .= "FROM mst_barang mat 
		   LEFT JOIN (SELECT 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.`mat_type` =  '".$this->mat_type."'
					  $wsubkon
					  $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_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.`mat_type` =  '".$this->mat_type."'
					  $wsubkon
					  $wline
					  AND tr_head.`matin_date` > '$beg_date' AND tr_head.`matin_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_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.`mat_type` =  '".$this->mat_type."' 
					  $wsubkon
					  $wline
					  AND tr_head.`matout_date` > '$beg_date' AND tr_head.`matout_date` < '$dtdari'
					  GROUP BY 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,matin_no AS trans_no,matin_date AS date,DATE_FORMAT(matin_date,'%d/%m/%Y') AS dmy,
		matin_name AS tipe,
		NmPrshn AS buspart,
		tr_head.line,
		qty AS debit,
		FORMAT(qty,2) AS fdebit,
		0 AS credit,
		0 AS fcredit,
		0 AS saldo,
		'd' AS dbcd
		FROM `mat_incdet_sub` tr_det 
		INNER JOIN `mat_inchdr_sub` tr_head	ON tr_det.`matin_id` = tr_head.`matin_id`
		LEFT JOIN mst_in_type min ON min.matin_type=tr_head.matin_type
		LEFT JOIN mst_perusahaan pt ON pt.KdPrshn=tr_head.supplier
		WHERE tr_head.`mat_type` =  '".$this->mat_type."' 
		AND mat_id='$key' 
		$wsubkon
		$wline
		AND tr_head.`matin_date` BETWEEN  '".$dtdari."' AND '".$dtsampai."'
	UNION ALL
		SELECT mat_id AS KdBarang,matout_no AS trans_no,matout_date AS date,DATE_FORMAT(matout_date,'%d/%m/%Y') AS dmy,
		matout_name AS tipe,
		NmPrshn AS buspart,
		tr_head.line,
		0 AS debit,
		0 AS fdebit,
		qty AS credit,
		FORMAT(qty,2) AS fcredit,
		0 AS saldo,
		'c' AS dbcd
		FROM `mat_outdet_sub` tr_det
		INNER JOIN `mat_outhdr_sub` tr_head ON tr_det.`matout_id` = tr_head.`matout_id`
		LEFT JOIN mst_out_type mout ON mout.matout_type=tr_head.matout_type
		LEFT JOIN mst_perusahaan pt ON pt.KdPrshn=tr_head.cust
		WHERE tr_head.`mat_type` =  '".$this->mat_type."' 
		AND mat_id='$key' 
		$wsubkon
		$wline
		AND tr_head.`matout_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,
			'tipe'	=> 'Last Balance',
			'buspart'	=> '',
			'line'	=> '',
			'debit'		=> 	'',
			'fdebit'	=> 	'',
			'credit'	=> '',
			'fcredit'	=> '',
			'saldo'		=> number_format($balance,2)
		);
		array_push($temp,$saldoarr);
		foreach ($rs as $r){		
			$balance=$balance + ($r['debit'] - $r['credit']);
			$r['saldo']=number_format($balance,2);
			array_push($temp,$r);
		}
		
		return $temp;
	}
	
}