<?php

class Erpt_kstok_rm_mdl extends Base_Model {

    function __construct() {

        parent::__construct();
    }

	function getAll($page,$rows,$key='',$dtdari='',$dtsampai='',$prodcard_id='',$lot_no=''){
		$dtdari = $this->dmys2ymd($dtdari);
		$dtsampai = $this->dmys2ymd($dtsampai);
		
		$offset = ($page-1)*$rows;
		$result = array();
		
		$mat_type='1';
		
		$q01="SELECT MAX(opname_date) AS beg_date 
			  FROM mat_opnamehdr 
			  WHERE mat_type='$mat_type' 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.`kd_barang`,mat.`nm_barang`,mat.kd_uom,
		  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 mt_barang mat 
		   LEFT JOIN (SELECT mat_id,SUM(tr_det.`weight`) AS qty_beg_bal
					  FROM `mat_opnamedet` tr_det
					  INNER JOIN `mat_opnamehdr` tr_head
					  ON tr_det.`opname_id` = tr_head.`opname_id`
					  WHERE tr_head.`mat_type` =  '$mat_type' 
					  AND tr_det.prodcard_id LIKE '%$prodcard_id%'
					  AND tr_det.lot_no LIKE '%$lot_no%'
					  AND tr_head.`opname_date` =  '$beg_date'
					  GROUP BY mat_id) AS beg_bal
		   ON mat.`kd_barang` = beg_bal.mat_id
		   LEFT JOIN (SELECT mat_id,SUM(tr_det.`weight`) AS qty_in
					  FROM `mat_incdet` tr_det INNER JOIN `mat_inchdr` tr_head
					  ON tr_det.`matin_id` = tr_head.`matin_id`
					  WHERE tr_head.`mat_type` =  '$mat_type' 
					  AND tr_head.prodcard_id LIKE '%$prodcard_id%'
					  AND tr_det.lot_no LIKE '%$lot_no%'
					  AND tr_head.`matin_date` > '$beg_date' AND tr_head.`matin_date` < '$dtdari'
					  GROUP BY mat_id) AS tr_in		  
		   ON mat.`kd_barang` = tr_in.mat_id 
		   LEFT JOIN (SELECT mat_id,SUM(tr_det.`weight`) AS qty_out
					  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` =  '$mat_type' 
					  AND tr_head.prodcard_id LIKE '%$prodcard_id%'
					  AND tr_head.lot_no LIKE '%$lot_no%'
					  AND tr_head.`matout_date` > '$beg_date' AND tr_head.`matout_date` < '$dtdari'
					  GROUP BY mat_id) AS tr_out
		   ON mat.`kd_barang` = tr_out.mat_id 	 
		   WHERE mat.kd_barang = '$key'
		   GROUP BY mat.kd_barang
		   ORDER BY kd_barang 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 kd_barang,matin_no AS trans_no,matin_date AS date,DATE_FORMAT(matin_date,'%d/%m/%Y') AS dmy,
		matin_name AS tipe,
		IF(NmPrshn!='',NmPrshn,montir) AS buspart,
		weight AS debit,
		FORMAT(weight,2) AS fdebit,
		0 AS credit,
		0 AS fcredit,
		0 AS saldo,
		'd' AS dbcd
		FROM `mat_incdet` tr_det 
		INNER JOIN `mat_inchdr` tr_head	ON tr_det.`matin_id` = tr_head.`matin_id`
		LEFT JOIN ms_in_type min ON min.matin_type=tr_head.matin_type
		LEFT JOIN ms_perusahaan pt ON pt.KdPrshn=tr_head.supplier
		WHERE tr_head.`mat_type` =  '$mat_type' 
		AND mat_id='$key' 
		AND tr_head.prodcard_id LIKE '%$prodcard_id%'
		AND tr_det.lot_no LIKE '%$lot_no%'
		AND tr_head.`matin_date` BETWEEN  '".$dtdari."' AND '".$dtsampai."'
	UNION ALL
		SELECT mat_id AS kd_barang,matout_no AS trans_no,matout_date AS date,DATE_FORMAT(matout_date,'%d/%m/%Y') AS dmy,
		matout_name AS tipe,
		IF(NmPrshn!='',NmPrshn,penerima) AS buspart,
		0 AS debit,
		0 AS fdebit,
		weight AS credit,
		FORMAT(weight,2) AS fcredit,
		0 AS saldo,
		'c' AS dbcd
		FROM `mat_outdet` tr_det
		INNER JOIN `mat_outhdr` tr_head ON tr_det.`matout_id` = tr_head.`matout_id`
		LEFT JOIN ms_out_type mout ON mout.matout_type=tr_head.matout_type
		LEFT JOIN ms_perusahaan pt ON pt.KdPrshn=tr_head.cust
		WHERE tr_head.`mat_type` =  '$mat_type' 
		AND mat_id='$key' 
		AND tr_head.prodcard_id LIKE '%$prodcard_id%'
		AND tr_head.lot_no LIKE '%$lot_no%'
		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(		
			'kd_barang'	=> '',
			'trans_no'	=> '',
			'date'		=> '',
			'dmy'		=> $period_from,
			'tipe'	=> 'Last Balance',
			'buspart'	=> '',
			'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;
	}
	
}