<?php

class Trxout_sub_mdl extends Base_Model {

    function __construct() {

        parent::__construct();
		$this->pk = 'matout_id';
    }

	function getHdr($page,$rows,$key=''){
		$offset = ($page-1)*$rows;
		$result = array();
		
		$q = "SELECT a.*,a.".$this->pk." AS id,
			  DATE_FORMAT(matout_date,'%d/%m/%Y') AS Fmatout_date,
			  DATE_FORMAT(TgDaf,'%d/%m/%Y') AS FTgDaf,
			  c.matout_name,d.NmPrshn,f.UrJnsDok,
			  g.NmPrshn AS company  
			  FROM ".$this->tbl." a
			  LEFT JOIN ".$this->tbl3." c ON c.".$this->pk3."=a.".$this->pk3."
			  LEFT JOIN ".$this->tbl4." d ON d.".$this->pk4."=a.cust
			  LEFT JOIN ".$this->tbl6." f ON f.".$this->pk6."=a.".$this->pk6."
			  LEFT JOIN ".$this->tbl4." g ON g.".$this->pk4."=a.kd_cmpy
			  WHERE mat_type = '".$this->mat_type."'";
		if ($this->session->userdata('kd_cmpy') != "0001"){
			$q .= " AND kd_cmpy = '".$this->session->userdata('kd_cmpy')."'";	
		}
		$q .= " AND (a.matout_no LIKE '%$key%' OR d.NmPrshn LIKE '%$key%')
			  ORDER BY matout_date, matout_no DESC";
		$rstot=$this->db->query($q)->result();
		//echo $q;
		$q .= " LIMIT $offset,$rows";
		$rs=$this->db->query($q)->result();
		
		$result["total"] = count($rstot);
		$result["rows"] = $rs;
		
		return $result;
	}
	
	function getRow($key=''){		
		$q = "SELECT a.*,a.".$this->pk." AS id,
			  DATE_FORMAT(matout_date,'%d/%m/%Y') AS Fmatout_date,
			  DATE_FORMAT(TgDaf,'%d/%m/%Y') AS FTgDaf,
			  c.matout_name,d.NmPrshn,f.UrJnsDok  
			  FROM ".$this->tbl." a
			  LEFT JOIN ".$this->tbl3." c ON c.".$this->pk3."=a.".$this->pk3."
			  LEFT JOIN ".$this->tbl4." d ON d.".$this->pk4."=a.cust
			  LEFT JOIN ".$this->tbl6." f ON f.".$this->pk6."=a.".$this->pk6."
			  WHERE a.".$this->pk."='$key'
			  LIMIT 1";
		$rs=$this->db->query($q)->result();
		
		return $rs[0];
	}
	
	function getDet($page,$rows,$key=''){
		$offset = ($page-1)*$rows;
		$result = array();
		
		$q="SELECT a.*,matout_id AS id,
			  DATE_FORMAT(lot_no,'%d/%m/%Y') AS lot_no,
			FORMAT(qty, 2) AS Fqty,
			FORMAT(price, 4) AS Fprice,
			FORMAT(qty*price, 2) AS amount,
			b.NmBarang,b.Sat
		  	FROM ".$this->tbl2." a 
		  	LEFT JOIN ".$this->tbl5." b ON KdBarang = mat_id 
		  	WHERE ".$this->pk."='$key' 
		  	ORDER BY child_no ASC";
		$rstot=$this->db->query($q)->result();
		//echo $q;
		$q .= " LIMIT $offset,$rows";
		$rs=$this->db->query($q)->result();
		
		$result["total"] = count($rstot);
		$result["rows"] = $rs;
		
		return $result;
	}
	
	function getDetAll($key=''){
		
		$q="SELECT a.*,matout_id AS id,
			FORMAT(qty, 2) AS Fqty,
			FORMAT(price, 4) AS Fprice,
			FORMAT(qty*price, 2) AS amount,
			b.NmBarang,b.Sat
		  	FROM ".$this->tbl2." a 
		  	LEFT JOIN ".$this->tbl5." b ON KdBarang = mat_id 
		  	WHERE ".$this->pk."='$key' 
		  	ORDER BY child_no ASC";
		$rs=$this->db->query($q)->result();
		
		return $rs;
	}
	
	function getHdrDet($page,$rows,$key='',$dtdari='',$dtsampai=''){
		$offset = ($page-1)*$rows;
		$result = array();
		
		$q="SELECT a.*,a.".$this->pk." AS id,
			DATE_FORMAT(matout_date,'%d/%m/%Y') AS Fmatout_date,
			DATE_FORMAT(TgDaf,'%d/%m/%Y') AS FTgDaf,
			b.*, DATE_FORMAT(lot_no,'%d/%m/%Y') AS lot_no,
			FORMAT(qty, 2) AS Fqty,
			FORMAT(price, 4) AS Fprice,
			FORMAT(qty*price, 2) AS amount,
			c.matout_name,d.NmPrshn,f.UrJnsDok,
			e.NmBarang,e.Sat,
			g.NmPrshn AS company
			FROM ".$this->tbl." a
			LEFT JOIN ".$this->tbl2." b ON b.".$this->pk."=a.".$this->pk."
			LEFT JOIN ".$this->tbl3." c ON c.".$this->pk3."=a.matout_type
			LEFT JOIN ".$this->tbl4." d ON d.".$this->pk4."=a.cust
			LEFT JOIN ".$this->tbl5." e ON e.".$this->pk5."= b.mat_id 
			LEFT JOIN ".$this->tbl6." f ON f.".$this->pk6."= a.KdJnsDok
			  LEFT JOIN ".$this->tbl4." g ON g.".$this->pk4."=a.kd_cmpy
			WHERE mat_type = '".$this->mat_type."'";
			
		if ($this->session->userdata('kd_cmpy') != "0001"){
			$q .= " AND kd_cmpy = '".$this->session->userdata('kd_cmpy')."'";	
		}
			  
		if ($key != ""){  
			$q .= " AND (a.matout_no LIKE '%$key%' OR d.NmPrshn LIKE '%$key%' OR KdBarang LIKE '%$key%')";
		}	
			  
		if($dtdari != '' && $dtsampai != ''):
			$q .= " AND matout_date BETWEEN '".$this->dmys2ymd($dtdari)."' AND '".$this->dmys2ymd($dtsampai)."'";
		endif;
		
		$q .= " ORDER BY matout_date, a.matout_no DESC";
		$rstot=$this->db->query($q)->result();
		//echo $q;
		$q .= " LIMIT $offset,$rows";
		$rs=$this->db->query($q)->result();
		
		$result["total"] = count($rstot);
		$result["rows"] = $rs;
		
		return $result;
	}
	
	function getNewNo(){
		$q="SELECT MAX(".$this->pk.") AS auto_no FROM ".$this->tbl." LIMIT 1";
		$rs=$this->db->query($q)->result();
		$autono=($rs)?$rs[0]->auto_no+1:1;
		
		return $autono;
	}
	
	function dmys2ymd($date){
		if ($date!=""){
			list($day, $month, $year) = explode('/', $date);		
			return $year."-".str_pad($month, 2, "0", STR_PAD_LEFT)."-".str_pad($day, 2, "0", STR_PAD_LEFT);
		} else {
			return "0000-00-00";
		}
	}
	
	function getStock($post_data){
		$rhdr=$this->getRow($post_data['matout_id']);
		$date = $rhdr->matout_date;
		$KdBarang=($post_data['mat_id']=='undefined')?'':$post_data['mat_id'];
		$lot_no=($post_data['lot_no']=='undefined')?'':$post_data['lot_no'];
		$qty=($post_data['qty']=='undefined')?'':$post_data['qty'];
		
			$q00 = "SELECT TpBarang
				  FROM mst_barang 
				  WHERE KdBarang='$KdBarang' LIMIT 1";	
			$rs0=$this->db->query($q00)->result_array();
			$TpBarang=$rs0[0]['TpBarang'];
			if ($TpBarang=="1" || $TpBarang=="2"){
				$mat_type='1';
			} else if ($TpBarang=="0") {
				$mat_type='0';
			} else if ($TpBarang=="12") {
				$mat_type='12';	
			} else {
				$mat_type='3';
			}
			
			$q01="SELECT MAX(opname_date) AS beg_date 
				  FROM mat_opnamehdr_sub 
				  WHERE mat_type='$mat_type' AND opname_date < '$date'  
				  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`,0) + IFNULL(tr_in.`qty`,0) - IFNULL(tr_out.`qty`,0)) AS qty_end";
		$q .= " FROM mst_barang mat 
			   LEFT JOIN (SELECT tr_det.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'
						  GROUP BY tr_det.`mat_id`) AS beg_bal
			   ON mat.`KdBarang` = beg_bal.mat_id
			   LEFT JOIN (SELECT tr_det.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'
						  GROUP BY tr_det.`mat_id`) AS tr_in		  
			   ON mat.`KdBarang` = tr_in.mat_id 
			   LEFT JOIN (SELECT tr_det.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'
						  GROUP BY tr_det.`mat_id`) AS tr_out
			   ON mat.`KdBarang` = tr_out.mat_id 	 
			   WHERE mat.KdBarang = '$KdBarang'
			   GROUP BY mat.KdBarang
			   ORDER BY KdBarang ASC";
			$rows=$this->db->query($q)->result_array();//sql saldo
			$qty_end=$rows[0]['qty_end'];
			
			if($qty <= $qty_end){
				return array("success"=>true,"msg"=>"Stok ada $qty_end.");
			} else {
				return array("success"=>false,"msg"=>"Qty tidak boleh melebihi stok. <br> Stok akhir $KdBarang = $qty_end.");
			}
	}
	
	function getStockLot($post_data){
		$rhdr=$this->getRow($post_data['matout_id']);
		$date = $rhdr->matout_date;
		$KdBarang=($post_data['mat_id']=='undefined')?'':$post_data['mat_id'];
		$lot_no=($post_data['lot_no']=='undefined')?'':$post_data['lot_no'];
		$qty=($post_data['qty']=='undefined')?'':$post_data['qty'];
		
			$q00 = "SELECT TpBarang
				  FROM mst_barang 
				  WHERE KdBarang='$KdBarang' LIMIT 1";	
			$rs0=$this->db->query($q00)->result_array();
			$TpBarang=$rs0[0]['TpBarang'];
			if ($TpBarang=="1" || $TpBarang=="2"){
				$mat_type='1';
			} else if ($TpBarang=="0") {
				$mat_type='0';
			} else if ($TpBarang=="12") {
				$mat_type='12';	
			} else {
				$mat_type='3';
			}
			
			$q01="SELECT MAX(opname_date) AS beg_date 
				  FROM mat_opnamehdr_sub 
				  WHERE mat_type='$mat_type' AND opname_date < '$date'  
				  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`,0) + IFNULL(tr_in.`qty`,0) - IFNULL(tr_out.`qty`,0)) AS qty_end";
		$q .= " FROM mst_barang mat 
			   LEFT JOIN (SELECT tr_det.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_det.lot_no='".$lot_no."'
						  AND tr_head.`opname_date` =  '$beg_date'
						  GROUP BY tr_det.`mat_id`) AS beg_bal
			   ON mat.`KdBarang` = beg_bal.mat_id
			   LEFT JOIN (SELECT tr_det.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_det.lot_no='".$lot_no."'
						  AND tr_head.`matin_date` > '$beg_date'
						  GROUP BY tr_det.`mat_id`) AS tr_in		  
			   ON mat.`KdBarang` = tr_in.mat_id 
			   LEFT JOIN (SELECT tr_det.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_det.lot_no='".$lot_no."'
						  AND tr_head.`matout_date` > '$beg_date'
						  GROUP BY tr_det.`mat_id`) AS tr_out
			   ON mat.`KdBarang` = tr_out.mat_id 	 
			   WHERE mat.KdBarang = '$KdBarang'
			   GROUP BY mat.KdBarang
			   ORDER BY KdBarang ASC";
			$rows=$this->db->query($q)->result_array();//sql saldo
			$qty_end=$rows[0]['qty_end'];
			
			if($qty <= $qty_end){
				return array("success"=>true,"msg"=>"Stok ada $qty_end.");
			} else {
				return array("success"=>false,"msg"=>"Qty tidak boleh melebihi stok. <br> Stok akhir $KdBarang = $qty_end.");
			}
	}
	
	function add($data){
		$run = $this->db->insert($this->tbl, $data); 
		if ($this->db->affected_rows()>0){
			$rs=true;
			$log=$this->addLogSubkon($data['nama_user'],"Tambah ".$this->title." ".$data[$this->pkh]);	
		} else {
			$rs=false;
		}
		
		return $rs;
	}
	
	function addDet($data){
		unset($data['NmBarang']);
		unset($data['Sat']);
		unset($data['amount']);
		$run = $this->db->insert($this->tbl2, $data); 
		if ($this->db->affected_rows()>0){
			$rs=true;
			$log=$this->addLogSubkon($data['nama_user'],"Tambah ".$this->title." ".$data[$this->pk]);	
		} else {
			$rs=false;
		}
		
		return $rs;
	}
	
	function editHdr($data,$id){
		$run = $this->db->update($this->tbl, $data, array($this->pk => $id)); 
		if ($this->db->affected_rows()>0){
			$rs=true;
			$log=$this->addLogSubkon($id,"Ubah ".$this->title." Header".$id);	
		} else {
			$rs=false;
		}		
		
		return $rs;
	}
	
	function editDet($data,$pk){
		unset($data['NmBarang']);
		unset($data['Sat']);
		unset($data['amount']);
		//print_r($data);
		$run = $this->db->update($this->tbl2, $data, $pk); 
		//echo $this->db->last_query();
		if ($this->db->affected_rows()>0){
			$rs=true;
			$log=$this->addLogSubkon($pk,"Ubah ".$this->title." Detail".$pk);	
		} else {
			$rs=false;
		}
		
		return $rs;
	}
	
}