<?php

class Muwip2_mdl extends Base_Model {

    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 opname_date < '".dmys2ymd($dtdari)."' AND opname_type='WIP2' ORDER BY opname_date DESC LIMIT 1 ";
		// echo $q01;exit();
		$run=$this->db->query($q01)->row();
		// echo $run->beg_date;exit();
		$beg_date=($run->beg_date=="")?"0000-00-00":$run->beg_date;
		
		$q = "SELECT mat.`WIP2` AS WIP2,mat.`NmBarang`,mat.Sat,mat.PartNo,
		SUM(IFNULL(beg_bal.`qty_beg_bal`,0) + IFNULL(tr_in.`qty_in`,0)
		- IFNULL(tr_out.`qty`,0) + IFNULL(beg_adjust.`qty_beg_adjust`,0)) AS qty_beg, 	  
		SUM(IFNULL(tr_in_periode.qty_in_periode,0) + IFNULL(adjust_period.`qty_adjust_in`,0)) AS qty_in,
		SUM(IFNULL(tr_out_periode.qty,0) + IFNULL(adjust_period.`qty_adjust_out`,0)) AS qty_out,
		SUM(IFNULL(end_bal.qty_end_bal,0)) AS qty_bal,
		SUM(IFNULL(tr_qty_opname.qty_opn,0)) AS qty,
		0 AS qty_end, FORMAT(0,2) AS qty_diff FROM mst_barang mat 
		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_beg_bal
					FROM mat_opnamedet_sub tr_det
					left 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'
					GROUP BY tr_det.`mat_id`) AS beg_bal
		 ON mat.`WIP2` = beg_bal.mat_id
		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_in
					FROM `mat_outdet_sub` tr_det 
					  left JOIN `mat_outhdr_sub` tr_head
					ON tr_det.`matout_id` = tr_head.`matout_id`
					WHERE tr_head.proses='WIP2'
					AND tr_head.`matout_date` > '".$beg_date."' AND tr_head.`matout_date` < '".dmys2ymd($dtdari)."'
					GROUP BY tr_det.`mat_id`) AS tr_in		  
		 ON mat.`WIP2` = tr_in.mat_id 
		 LEFT JOIN (SELECT bomd.mat_id,SUM(bomd.qty*(tr_det.`qty`+ tr_det.`ng_qty`)) AS qty
				FROM mat_bomhdr_sub bomh 
				left 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` < '".dmys2ymd($dtdari)."'
				GROUP BY bomd.mat_id) AS tr_out
		 ON mat.`WIP2` = tr_out.mat_id 	 
		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in` - tr_det.`qty_out`) AS qty_beg_adjust
					FROM `mat_opnamedet_sub` tr_det
					left 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'
					GROUP BY tr_det.`mat_id`) AS beg_adjust   
		 ON mat.`WIP2` = beg_adjust.mat_id 		  
		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_in_periode
					FROM `mat_outdet_sub` tr_det 
					  left JOIN `mat_outhdr_sub` tr_head
					ON tr_det.`matout_id` = tr_head.`matout_id`
					WHERE tr_head.proses='WIP2'
					AND tr_head.`matout_date` >=  '".dmys2ymd($dtdari)."' AND tr_head.`matout_date` <=  '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS tr_in_periode
		 ON mat.`WIP2` = tr_in_periode.mat_id 
		LEFT JOIN (SELECT bomd.mat_id,SUM(bomd.qty*(tr_det.`qty`+ tr_det.`ng_qty`)) AS qty
				FROM mat_bomhdr_sub bomh 
				left 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_out_periode
		 ON mat.`KdBarang` = tr_out_periode.mat_id 	  
		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in`) AS qty_adjust_in,SUM(tr_det.`qty_out`) AS qty_adjust_out
					FROM `mat_opnamedet_sub` tr_det
					left JOIN `mat_opnamehdr_sub` tr_head
					ON tr_det.`opname_id` = tr_head.`opname_id`
					WHERE tr_head.opname_type='WIP2' 
					AND tr_head.`opname_date` >=  '".dmys2ymd($dtdari)."' AND tr_head.`opname_date` <=  '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS adjust_period
		 ON mat.`WIP2` = adjust_period.mat_id 
		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in` - tr_det.`qty_out`) AS qty_end_bal
					FROM `mat_opnamedet_sub` tr_det
					left JOIN `mat_opnamehdr_sub` tr_head
					ON tr_det.`opname_id` = tr_head.`opname_id`
					WHERE tr_head.opname_type='WIP2'
					AND tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS end_bal
		 ON mat.`WIP2` = end_bal.mat_id 
		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_opn
					FROM `mat_opnamedet_sub` tr_det
					left JOIN `mat_opnamehdr_sub` tr_head
					ON tr_det.`opname_id` = tr_head.`opname_id`
					WHERE tr_head.opname_type='WIP2' 
					AND tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) as tr_qty_opname
		 ON mat.`WIP2` = tr_qty_opname.mat_id  
		 WHERE mat.TpBarang ".$this->tp_barang." " ; 
		
		if ($key != ""){  
			$q .= " AND (mat.WIP2 LIKE '%$key%' OR mat.NmBarang LIKE '%$key%') ";
		}
		
		// if($dtdari != '' && $dtsampai != ''):
		// 	$q .= " AND TgDaf BETWEEN '".$this->dmys2ymd($dtdari)."' AND '".$this->dmys2ymd($dtsampai)."' ";
		// endif;
		// die($q);
		$q .= " GROUP BY mat.WIP2
	   			ORDER BY WIP2 ASC";

		// echo $q;exit();
		
		$rstot=$this->db->query($q)->result();
	
		//$q .= " LIMIT $offset,$rows";
		$rs=$this->db->query($q)->result();
		
		$result["total"] = count($rstot);
		$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();

		$q01="SELECT MAX(opname_date) AS beg_date FROM mat_opnamehdr_sub 
		WHERE mat_type='".$this->mat_type."' AND opname_date < '".dmys2ymd($dtdari)."' 
		AND opname_type='WIP2' 
		ORDER BY opname_date DESC LIMIT 1 ";
		// echo $q01;exit();
		$run=$this->db->query($q01)->row();
		// echo $run->beg_date;exit();
		$beg_date=($run->beg_date=="")?"0000-00-00":$run->beg_date;
		
		$q = "SELECT mat.`WIP2` AS WIP2,mat.`NmBarang`,mat.Sat,mat.PartNo,
		IFNULL(beg_bal.`qty_beg_bal`,0) 
		+ IFNULL(tr_in.`qty_in`,0) 
		+ IFNULL(tr_in2.`qty_in2`,0)
		+ IFNULL(tr_in3.`qty_in3`,0)
		+ IFNULL(tr_in4.`qty_in4`,0)
		- IFNULL(tr_out2.qty_out2,0)
		- IFNULL(tr_out.`qty`,0) + IFNULL(beg_adjust.`qty_beg_adjust`,0) AS qty_beg, 	  

		IFNULL(tr_in_periode.qty_in_periode,0)
		+ IFNULL(tr_in_periode2.qty_in_periode2,0) 
		+ IFNULL(tr_in_periode3.qty_in_periode3,0) 
		+ IFNULL(tr_in_periode4.qty_in_periode4,0) 
		+ IFNULL(adjust_period.`qty_adjust_in`,0) AS qty_in,

		IFNULL(tr_out_periode.qty,0) 
		+ IFNULL(tr_out_periode2.qty_out_periode2,0) 
		+ IFNULL(adjust_period.`qty_adjust_out`,0) AS qty_out,

		IFNULL(end_bal.qty_end_bal,0) AS qty_bal,
		IFNULL(tr_qty_opname.qty_opn,0) AS qty,
		0 AS qty_end, FORMAT(0,2) AS qty_diff FROM mst_barang mat 

		 LEFT JOIN (SELECT tr_det.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.`opname_date` =  '".$beg_date."'
					AND tr_head.opname_type='WIP2'
					GROUP BY tr_det.`mat_id`) AS beg_bal
		 ON mat.`WIP2` = beg_bal.mat_id

		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_in
					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.proses='WIP2'
					AND tr_head.`matout_date` > '".$beg_date."' AND tr_head.`matout_date` < '".dmys2ymd($dtdari)."'
					GROUP BY tr_det.`mat_id`) AS tr_in		  
		 ON mat.`WIP2` = tr_in.mat_id 

		 LEFT JOIN (Select tr_det.mat_id, sum(tr_det.ng_qty) as qty_in2
				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 = '1'
				and tr_head.matout_type = '9'
				and tr_head.from_proses = 'NG-RMT'
				and tr_head.proses = 'WIP2'
				AND tr_head.`matout_date` > '".$beg_date."' AND tr_head.`matout_date` < '".dmys2ymd($dtdari)."'
				GROUP BY tr_det.`mat_id`) AS tr_in2
		ON mat.`KdBarang` = tr_in2.mat_id

		LEFT JOIN (Select tr_det.mat_id, sum(tr_det.ng_qty) as qty_in3
				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 = '1'
				and tr_head.matout_type = '9'
				and tr_head.from_proses is null
				and tr_head.proses = 'WIP2'
				AND tr_head.`matout_date` > '".$beg_date."' AND tr_head.`matout_date` < '".dmys2ymd($dtdari)."'
				GROUP BY tr_det.`mat_id`) AS tr_in3
		ON mat.`KdBarang` = tr_in3.mat_id

		LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_in4
				FROM `mat_incdet_sub` tr_det 
				left JOIN `mat_inchdr_sub` tr_head
				ON tr_det.`matin_id` = tr_head.`matin_id`
				WHERE tr_head.proses in ('WIP2')
				AND tr_head.from_proses is null
				AND tr_head.matin_type = 8
				AND tr_head.`matin_date` > '".$beg_date."' AND tr_head.`matin_date` < '".dmys2ymd($dtdari)."'
				GROUP BY tr_det.`mat_id`) AS tr_in4
		ON mat.`KdBarang` = tr_in4.mat_id

		 LEFT JOIN (SELECT bomd.mat_id,SUM(bomd.qty*(tr_det.`qty`+ tr_det.`ng_qty`)) AS qty
				FROM mat_bomhdr_sub bomh 
				left 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'
				AND tr_head.`matin_date` > '".$beg_date."' AND tr_head.`matin_date` < '".dmys2ymd($dtdari)."'
				$wsubkon
				GROUP BY bomd.mat_id) AS tr_out
		 ON mat.`WIP2` = tr_out.mat_id 	 

		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`ng_qty`) AS qty_out2
		 FROM mat_outdet_sub tr_det
				INNER JOIN mat_outhdr_sub tr_head ON tr_head.matout_id=tr_det.matout_id 
				WHERE tr_head.`mat_type` in (1) 
				AND tr_head.matout_type in  ('0','1') 
				AND tr_head.from_proses =  'W2' 
				AND tr_head.proses in ('NG RMT', '')
				AND tr_head.`matout_date` > '".$beg_date."' AND tr_head.`matout_date` < '".dmys2ymd($dtdari)."'
				GROUP BY tr_det.mat_id) AS tr_out2
		ON mat.`KdBarang` = tr_out2.mat_id 	  

		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in` - tr_det.`qty_out`) AS qty_beg_adjust
					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'
					GROUP BY tr_det.`mat_id`) AS beg_adjust   
		 ON mat.`WIP2` = beg_adjust.mat_id 		  

		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_in_periode
					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.proses='WIP2'
					AND tr_head.`matout_date` between '".dmys2ymd($dtdari)."' AND '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS tr_in_periode
		 ON mat.`WIP2` = tr_in_periode.mat_id 

		 LEFT JOIN (Select tr_det.mat_id, sum(tr_det.ng_qty) as qty_in_periode2
					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 = '1'
					and tr_head.matout_type = '9'
					and tr_head.from_proses = 'NG-RMT'
					and tr_head.proses = 'WIP2'
					AND tr_head.`matout_date` between  '".dmys2ymd($dtdari)."' AND '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS tr_in_periode2
			ON mat.`KdBarang` = tr_in_periode2.mat_id

		 LEFT JOIN (Select tr_det.mat_id, sum(tr_det.ng_qty) as qty_in_periode3
					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 = '1'
					and tr_head.matout_type = '9'
					and tr_head.from_proses is null
					and tr_head.proses = 'WIP2'
					AND tr_head.`matout_date` between  '".dmys2ymd($dtdari)."' AND '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS tr_in_periode3
			ON mat.`KdBarang` = tr_in_periode3.mat_id

			LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_in_periode4
					FROM `mat_incdet_sub` tr_det 
					left JOIN `mat_inchdr_sub` tr_head
					ON tr_det.`matin_id` = tr_head.`matin_id`
					WHERE tr_head.proses in ('WIP2')
					AND tr_head.from_proses is null
					AND tr_head.matin_type = 8
					AND tr_head.`matin_date` between  '".dmys2ymd($dtdari)."' AND '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS tr_in_periode4
			ON mat.`KdBarang` = tr_in_periode4.mat_id

		LEFT JOIN (SELECT bomd.mat_id,SUM(bomd.qty*(tr_det.`qty`+ tr_det.`ng_qty`)) AS qty
				FROM mat_bomhdr_sub bomh 
				left 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` in (0) 
				AND tr_head.matin_type =  '5' 
				AND tr_head.proses='FG-NP'
				$wsubkon
				AND tr_head.`matin_date` between '".dmys2ymd($dtdari)."' AND '".dmys2ymd($dtsampai)."'
				GROUP BY bomd.mat_id) AS tr_out_periode
		 ON mat.`KdBarang` = tr_out_periode.mat_id 	  

		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`ng_qty`) AS qty_out_periode2
		 FROM mat_outdet_sub tr_det
				INNER JOIN mat_outhdr_sub tr_head ON tr_head.matout_id=tr_det.matout_id 
				WHERE tr_head.`mat_type` in (1) 
				AND tr_head.matout_type in  ('0','1') 
				AND tr_head.from_proses =  'W2' 
				AND tr_head.proses in ('NG RMT', '')
				AND tr_head.`matout_date` between '".dmys2ymd($dtdari)."' AND '".dmys2ymd($dtsampai)."'
				GROUP BY tr_det.mat_id) AS tr_out_periode2
		ON mat.`KdBarang` = tr_out_periode2.mat_id 	  

		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in`) AS qty_adjust_in,
		 			SUM(tr_det.`qty_out`) AS qty_adjust_out
					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_type='WIP2' 
					AND tr_head.`opname_date` between '".dmys2ymd($dtdari)."' AND '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS adjust_period
		 ON mat.`WIP2` = adjust_period.mat_id 
		 
		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty_in` - tr_det.`qty_out`) AS qty_end_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.opname_type='WIP2'
					AND tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS end_bal
		 ON mat.`WIP2` = end_bal.mat_id 

		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`) AS qty_opn
					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_type='WIP2' 
					AND tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) as tr_qty_opname
		 ON mat.`WIP2` = tr_qty_opname.mat_id  
		 WHERE mat.TpBarang = ".$this->tp_barang." " ; 
		
		if ($key != ""){  
			$q .= " AND (mat.WIP2 LIKE '%$key%' OR mat.NmBarang LIKE '%$key%') ";
		}
		
		// if($dtdari != '' && $dtsampai != ''):
		// 	$q .= " AND TgDaf BETWEEN '".$this->dmys2ymd($dtdari)."' AND '".$this->dmys2ymd($dtsampai)."' ";
		// endif;
		// die($q);
		$q .= " GROUP BY mat.WIP2
	   			ORDER BY TpBarang ASC, WIP2 ASC";

		// echo $q;exit();
		
		$rstot=$this->db->query($q)->result();
	
		$q .= " LIMIT $offset,$rows";
		// echo $q;exit();
		$rs=$this->db->query($q)->result();
		
		$result["total"] = count($rstot);
		$result["rows"] = $rs;
		
		return $result;
	}
	
}