<?php
class Rmonwip2_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();
		
		$wsubkon='';
		if ($kd_cmpy != ''){
			  $wsubkon .= " AND tr_head.kd_cmpy = '".$kd_cmpy."'";
		}
		
		$q01="SELECT MAX(opname_date) AS beg_date 
			  FROM mat_opnamehdr_sub tr_head WHERE mat_type='".$this->mat_type."' 
			  $wsubkon
			  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.`WIP2`AS Proses,mat.`NmBarang`,mat.Sat,mat.PartNo,
			  SUM(IFNULL(beg_bal_mat.`qty`,0) + IFNULL(tr_in1.`qty`,0) + IFNULL(tr_in2.`qty`,0) - IFNULL(tr_out.`qty`,0)) AS Fqty_wh,
			  SUM(IFNULL(beg_bal_pr.`qty`,0) + IFNULL(tr_out2.`qty`,0) + IFNULL(tr_in3.`qty`,0) - IFNULL(tr_bom.`qty`,0)) AS Fqty_pr";
		$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.`opname_date` =  '".$beg_date."'
						  AND tr_head.opname_type='WIP2'
			   			 $wsubkon
						  GROUP BY mat_id) AS beg_bal_mat
			   ON mat.`KdBarang` = beg_bal_mat.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 tr_head.opname_type='WIP2'
						  $wsubkon
						  AND tr_head.`opname_date` =  '".$beg_date."'
						  GROUP BY mat_id) AS beg_bal_pr
			   ON mat.`KdBarang` = beg_bal_pr.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  kd_cmpy = '9221'
						  AND cust = '".$kd_cmpy."'
						  AND tr_head.`matout_date` >= '".$beg_date."' AND tr_head.`matout_date` <= '".$dtsampai."'
						  GROUP BY mat_id) AS tr_in1
			   ON mat.`KdBarang` = tr_in1.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.`matin_date` >=  '".$beg_date."' AND tr_head.`matin_date` <=  '".$dtsampai."'
						  $wsubkon
						  GROUP BY mat_id) AS tr_in2
			   ON mat.`KdBarang` = tr_in2.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.`matin_date` >=  '".$beg_date."' AND tr_head.`matin_date` <=  '".$dtsampai."'
					AND tr_head.mat_type='0'
					AND tr_head.opname_type='WIP2'
					$wsubkon
					GROUP BY mat_id) AS tr_in3
		      ON mat.`KdBarang` = tr_in3.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.matout_type='0'	
						  $wsubkon
						  AND tr_head.`matout_date` >= '".$beg_date."' AND tr_head.`matout_date` <= '".$dtsampai."'
						  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_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` <= '".$dtsampai."'
						  AND tr_head.proses='WIP2'
						  $wsubkon
						  GROUP BY mat_id) AS tr_out2
			   ON mat.`KdBarang` = tr_out2.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.proses='FG-NP'
					  $wsubkon
					  AND tr_head.`matin_date` >= '".$beg_date."' AND tr_head.`matin_date` <= '".$dtsampai."'
					  GROUP BY bomd.mat_id) AS tr_bom
			   ON mat.`KdBarang` = tr_bom.mat_id
			   LEFT JOIN mst_line mline ON mline.MatGroup=mat.MatGroup 	   	    
			   WHERE (mat.KdBarang LIKE '%$key%' OR mat.NmBarang LIKE '%$key%')
			   and mat.TpBarang='1'
			   and mat.WIP2 !=0
			   and mat.MatGroup='NB'
			   GROUP BY mat.KdBarang
			   ORDER BY KdBarang ASC";
		$q .= " LIMIT $offset,$rows"; 		
		//echo $q;
		$rs=$this->db->query($q)->result();
		
		$q02 = "SELECT COUNT(WIP2) AS jml 
				FROM mst_barang WHERE TpBarang='1' AND (KdBarang LIKE '%$key%' OR NmBarang LIKE '%$key%')";
		$rstot=$this->db->query($q02)->result();
		
		$result["total"] = $rstot[0]->jml;
		$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();
		
		$wsubkon='';
		if ($kd_cmpy != ''){
			  $wsubkon .= " AND tr_head.kd_cmpy = '".$kd_cmpy."'";
		}
		
		$q01="SELECT MAX(opname_date) AS beg_date 
			  FROM mat_opnamehdr_sub tr_head WHERE mat_type='".$this->mat_type."' 
			  $wsubkon
			  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.`WIP2`,mat.`NmBarang`,mat.Sat,'WIP2' as Proses,mat.PartNo,
		FORMAT(SUM(IFNULL(beg_bal_mat.`qty`,0) + IFNULL(tr_in1.`qty`,0) + IFNULL(tr_in2.`qty`,0) - IFNULL(tr_out.`qty`,0)),2) AS Fqty_wh,
		FORMAT(SUM(IFNULL(beg_bal_pr.`qty`,0) + IFNULL(tr_out2.`qty`,0) + IFNULL(tr_in3.`qty`,0)- IFNULL(tr_bom.`qty`,0)),2) AS Fqty_pr";
  $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.`opname_date` =  '".$beg_date."'
					AND tr_head.opname_type='WIP2'
					  $wsubkon
					GROUP BY mat_id) AS beg_bal_mat
		 ON mat.`KdBarang` = beg_bal_mat.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 tr_head.opname_type='WIP2'
					$wsubkon
					AND tr_head.`opname_date` =  '".$beg_date."'
					GROUP BY mat_id) AS beg_bal_pr
		 ON mat.`KdBarang` = beg_bal_pr.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  kd_cmpy = '9221'
					AND cust = '".$kd_cmpy."'
					AND tr_head.`matout_date` >= '".$beg_date."' AND tr_head.`matout_date` <= '".$dtsampai."'
					GROUP BY mat_id) AS tr_in1
		 ON mat.`KdBarang` = tr_in1.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.`matin_date` >=  '".$beg_date."' AND tr_head.`matin_date` <=  '".$dtsampai."'
					$wsubkon
					GROUP BY mat_id) AS tr_in2
		 ON mat.`KdBarang` = tr_in2.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.`matin_date` >=  '".$beg_date."' AND tr_head.`matin_date` <=  '".$dtsampai."'
					AND tr_head.mat_type='0'
					$wsubkon
					GROUP BY mat_id) AS tr_in3
		 ON mat.`KdBarang` = tr_in3.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.matout_type='0'	
					$wsubkon
					AND tr_head.`matout_date` >= '".$beg_date."' AND tr_head.`matout_date` <= '".$dtsampai."'
					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_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` <= '".$dtsampai."'
					and tr_head.proses='WIP2'
					$wsubkon
					GROUP BY mat_id) AS tr_out2
		 ON mat.`KdBarang` = tr_out2.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.proses='FG-NP'
				$wsubkon
				AND tr_head.`matin_date` >= '".$beg_date."' AND tr_head.`matin_date` <= '".$dtsampai."'
				GROUP BY bomd.mat_id) AS tr_bom
		 ON mat.`KdBarang` = tr_bom.mat_id
		 LEFT JOIN mst_line mline ON mline.MatGroup=mat.MatGroup 		  	    
		 WHERE (mat.KdBarang LIKE '%$key%' OR mat.NmBarang LIKE '%$key%')
		 and mat.TpBarang='1'
		 and mat.WIP2 !=0
		 and mat.MatGroup='NB'
		 GROUP BY mat.KdBarang
		 ORDER BY KdBarang ASC";
		// $q .= " LIMIT $offset,$rows"; 		
		//echo $q;
		$rs=$this->db->query($q)->result();
		
		$q02 = "SELECT COUNT(WIP2) AS jml 
				FROM mst_barang WHERE TpBarang='1' AND (KdBarang LIKE '%$key%' OR NmBarang LIKE '%$key%')";
		$rstot=$this->db->query($q02)->result();
		
		$result["total"] = $rstot[0]->jml;
		$result["rows"] = $rs;
		
		return $result;
	}
	
}