<?php

class Muwip1_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='WIP1'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.`WIP1` AS WIP1,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
					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='WIP1'
					GROUP BY tr_det.`mat_id`) AS beg_bal
		 ON mat.`WIP1` = 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='WIP1'
					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.`WIP1` = tr_in.mat_id 
		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`)+ SUM(tr_det.`ng_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.from_proses = 'W1'
					AND tr_head.`matout_date` > '".$beg_date."' AND tr_head.`matout_date` < '".dmys2ymd($dtdari)."'
					GROUP BY tr_det.`mat_id`) AS tr_out
		 ON mat.`WIP1` = 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
					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='WIP1'
					GROUP BY tr_det.`mat_id`) AS beg_adjust   
		 ON mat.`WIP1` = 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='WIP1'
					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.`WIP1` = tr_in_periode.mat_id 
		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`)+ SUM(tr_det.`ng_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.from_proses = 'W1'
					AND tr_head.`matout_date` between  '".dmys2ymd($dtdari)."' AND '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS tr_out_periode
		 ON mat.`WIP1` = 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
					INNER JOIN `mat_opnamehdr_sub` tr_head
					ON tr_det.`opname_id` = tr_head.`opname_id`
					WHERE tr_head.opname_type='WIP1' 
					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.`WIP1` = 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='WIP1'
					AND tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS end_bal
		 ON mat.`WIP1` = 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='WIP1' 
					AND tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) as tr_qty_opname
		 ON mat.`WIP1` = tr_qty_opname.mat_id  
		 WHERE mat.TpBarang ".$this->tp_barang." " ; 
		
		if ($key != ""){  
			$q .= " AND (mat.WIP1 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.WIP1
	   			ORDER BY WIP1 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='WIP1'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.`WIP1` AS WIP1,mat.`NmBarang`,mat.Sat,mat.PartNo,
		SUM(IFNULL(beg_bal.`qty_beg_bal`,0) 
		+ IFNULL(tr_in.`qty_in`,0)
		+ IFNULL(tr_in2.`qty_in`,0) 
		-- + IFNULL(tr_in1.`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(tr_in_periode1.qty_in_periode,0) 
		+ IFNULL(tr_in_periode2.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
					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='WIP1'
					GROUP BY tr_det.`mat_id`) AS beg_bal
		 ON mat.`WIP1` = 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='WIP1'
					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.`WIP1` = tr_in.mat_id 

		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`ng_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='WIP1'
					and tr_head.matout_type = '9'
					and tr_head.from_proses = 'NG-RMT'
					AND tr_head.`matout_date` >= '".$beg_date."' AND tr_head.`matout_date` <= '".dmys2ymd($dtdari)."'
					GROUP BY tr_det.`mat_id`) AS tr_in1	  
		 ON mat.`WIP1` = tr_in1.mat_id 

		 LEFT JOIN (SELECT CASE WHEN tr_det.mat_id = 4080600 THEN 1080600 
					ELSE tr_det.mat_id + 0
					END mat_id,
		 			SUM(tr_det.`qty`) AS qty_in
					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 ('WIP1')
					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_in2
			ON mat.`KdBarang` = tr_in2.mat_id

		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`)+ SUM(tr_det.`ng_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.from_proses = 'W1'
					AND tr_head.`matout_date` > '".$beg_date."' AND tr_head.`matout_date` < '".dmys2ymd($dtdari)."'
					GROUP BY tr_det.`mat_id`) AS tr_out
		 ON mat.`WIP1` = 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
					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='WIP1'
					GROUP BY tr_det.`mat_id`) AS beg_adjust   
		 ON mat.`WIP1` = 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='WIP1'
					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.`WIP1` = tr_in_periode.mat_id 

		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`ng_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='WIP1'
					and tr_head.from_proses = 'NG-RMT'
					and tr_head.matout_type = '9'
					AND tr_head.`matout_date` >=  '".dmys2ymd($dtdari)."' AND tr_head.`matout_date` <=  '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS tr_in_periode1
		 ON mat.`WIP1` = tr_in_periode1.mat_id 

		 LEFT JOIN (SELECT CASE WHEN tr_det.mat_id = 4080600 THEN 1080600 
					ELSE tr_det.mat_id + 0
					END mat_id,
		 			SUM(tr_det.`qty`) AS qty_in_periode
					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 ('WIP1')
					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_periode2
			ON mat.`KdBarang` = tr_in_periode2.mat_id

		 LEFT JOIN (SELECT tr_det.mat_id,SUM(tr_det.`qty`)+ SUM(tr_det.`ng_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.from_proses = 'W1'
					AND tr_head.`matout_date` between  '".dmys2ymd($dtdari)."' AND '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS tr_out_periode
		 ON mat.`WIP1` = 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
					INNER JOIN `mat_opnamehdr_sub` tr_head
					ON tr_det.`opname_id` = tr_head.`opname_id`
					WHERE tr_head.opname_type='WIP1' 
					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.`WIP1` = 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='WIP1'
					AND tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) AS end_bal
		 ON mat.`WIP1` = 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='WIP1' 
					AND tr_head.`opname_date` =  '".dmys2ymd($dtsampai)."'
					GROUP BY tr_det.`mat_id`) as tr_qty_opname
		 ON mat.`WIP1` = tr_qty_opname.mat_id  
		 WHERE mat.TpBarang = ".$this->tp_barang." " ; 
		
		if ($key != ""){  
			$q .= " AND (mat.WIP1 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.WIP1
	   			ORDER BY TpBarang ASC, WIP1 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;
	}
	
}