<?php
class Trxpo_mdl extends Base_Model {
    function __construct() {
		parent::__construct();
		$this->tbl = 'pur_pohdr';
		$this->tbl2 = 'pur_podet';
		$this->pk = 'po_id';
	}
	
	 function buat_kodeSo()   {
		$this->db->select("RIGHT(pur_pohdr.po_no,4) as kode", FALSE);
		$po_no = "po_type = '2'";
		$this->db->where($po_no);
		$this->db->order_by('po_no','DESC');    
		$this->db->limit(1);    
		$query = $this->db->get('pur_pohdr');      //cek dulu apakah ada sudah ada kode di tabel.    
		if($query->num_rows() <> 0){      
		 //jika kode ternyata sudah ada.      
		 $data = $query->row();      
		 $kode = intval($data->kode) + 1;    
		}
		else {      
		 //jika kode belum ada      
		 $kode = 1;    
		}
		$kodemax = str_pad($kode, 4, "0", STR_PAD_LEFT); // angka 4 menunjukkan jumlah digit angka 0
		$kodejadi = "SO-".date('Ym')."-".$kodemax;    // hasilnya SO-TahunTanggal-0001 dst.
		return $kodejadi;  
  }
   function buat_kodePo()   {
	$this->db->select("RIGHT(pur_pohdr.po_no,4) as kode", FALSE);
	$po_no = "po_type = '0'";
	$this->db->where($po_no);
	$this->db->order_by('po_no','DESC');    
	$this->db->limit(1);    
	$query = $this->db->get('pur_pohdr');      //cek dulu apakah ada sudah ada kode di tabel.    
	if($query->num_rows() <> 0){      
	 //jika kode ternyata sudah ada.      
	 $data = $query->row();      
	 $kode = intval($data->kode) + 1;    
	}
	else {      
	 //jika kode belum ada      
	 $kode = 1;    
	}
	$kodemax = str_pad($kode, 4, "0", STR_PAD_LEFT); // angka 4 menunjukkan jumlah digit angka 0
	$kodejadi = "PO-".date('Ym')."-".$kodemax;    // hasilnya PO-TahunTanggal-0001 dst.
	return $kodejadi;  
}
	
	// function getAutoNoPo()
	// {
	// 	$q = "SELECT count(po_no)+1 AS no FROM pur_pohdr WHERE po_type = '0'";
	// 	$rs = $this->db->query($q)->result();
	// 	return $rs;
	// }
	// function getAutoNoSo()
    // {
    //     $q = "SELECT count(po_no)+1 as no FROM pur_pohdr WHERE po_type = '2'";
    //     $rs = $this->db->query($q)->result();
    //     return $rs;
    // }
	function getHdr($page,$rows,$key=''){
		$offset = ($page-1)*$rows;
		$result = array();
		
		$q = "SELECT distinct a.*,a.".$this->pk." AS id, format(a.tot_amount,2) tot_amount,
			  DATE_FORMAT(po_date,'%d/%m/%Y') AS Fpo_date,
			  DATE_FORMAT(dlv_date,'%d/%m/%Y') AS Fdlv_date,
			  d.NmPrshn,d.AlmtPrshn
			  FROM ".$this->tbl." a
			  LEFT JOIN ".$this->tbl4." d ON d.".$this->pk4."=a.supplier
			  WHERE po_type = '".$this->po_type."'";
		$q .= " AND (a.po_no LIKE '%$key%' OR d.NmPrshn LIKE '%$key%')
			  ORDER BY po_date DESC, po_no DESC";
		$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 getRow($key=''){		
		$q = "SELECT a.*,a.".$this->pk." AS id,
			  DATE_FORMAT(po_date,'%d/%m/%Y') AS Fpo_date,
			  DATE_FORMAT(dlv_date,'%d/%m/%Y') AS Fdlv_date,
			  d.NmPrshn,d.AlmtPrshn
			  FROM ".$this->tbl." a
			  LEFT JOIN ".$this->tbl4." d ON d.".$this->pk4."=a.supplier
			  WHERE a.".$this->pk."='$key'
			  LIMIT 1";
		$rs=$this->db->query($q)->result();
		
		return $rs[0];
	}
	
	function getDet($page,$rows,$po_id='',$key=''){
		$offset = ($page-1)*$rows;
		$result = array();
		
		$q="SELECT a.*,
			FORMAT(qty, 2) AS Fqty,
			FORMAT(price, 5) AS Fprice,
			FORMAT(amount, 2) AS amount,
			b.NmBarang,b.Sat,b.PartNo,b.CF
		  	FROM ".$this->tbl2." a 
		  	LEFT JOIN ".$this->tbl5." b ON KdBarang = mat_id 
				WHERE ".$this->pk."='$po_id'
				AND (mat_id LIKE '%$key%' OR NmBarang LIKE '%$key%' )
		  	ORDER BY child_no ASC";
		$rstot=$this->db->query($q)->result();
		// die($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.*,po_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(po_date,'%d/%m/%Y') AS Fpo_date,
				DATE_FORMAT(dlv_date,'%d/%m/%Y') AS Fdlv_date,
				b.*,
				FORMAT(qty, 2) AS Fqty,
				FORMAT(price, 4) AS Fprice,
				FORMAT(qty*price, 2) AS amount,
				d.NmPrshn,d.AlmtPrshn,
				e.NmBarang,e.Sat
				FROM ".$this->tbl." a
				LEFT JOIN ".$this->tbl2." b ON b.po_id = a.".$this->pk." 
				LEFT JOIN ".$this->tbl4." d ON d.".$this->pk4."=a.supplier
				LEFT JOIN ".$this->tbl5." e ON e.".$this->pk5."= b.mat_id 
				WHERE po_type = '".$this->po_type."'";
			  
		if ($key != ""){  
			$q .= " AND (a.po_no LIKE '%$key%' OR d.NmPrshn LIKE '%$key%' OR KdBarang LIKE '%$key%')";
		}	
			  
		if($dtdari != '' && $dtsampai != ''):
			$q .= " AND po_date BETWEEN '".$this->dmys2ymd($dtdari)."' AND '".$this->dmys2ymd($dtsampai)."'";
		endif;
		
		$q .= " ORDER BY po_date, a.po_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;
	}

		// CHECK FOR DUPLICATE ITEM --iqro
		function checkNo($po_no=''){		
			$q = "SELECT COUNT(po_no) as po_no
							FROM ".$this->tbl."
							WHERE po_no = '$po_no'
							LIMIT 1";
			$rs=$this->db->query($q)->result();		
			return $rs;
		}
	
	function add($data){
		$run = $this->db->insert($this->tbl, $data); 
		if ($this->db->affected_rows()==0){	
			$rs=false;	
		} else {
			$rs=true;
			$log=$this->addLog($data['nama_user'],"Tambah Header ".$this->title.", NO = ".$data['po_no']);
		}
		
		return $rs;
	}

	// CHECK FOR DUPLICATE ITEM --ir
	// function checkPo($mat_id=''){		
	// 	$q = "SELECT COUNT(mat_id) as mat_id
	// 					FROM ".$this->tbl2."
	// 					WHERE mat_id = '$mat_id'
	// 					LIMIT 1";
	// 	$rs=$this->db->query($q)->result();		
	// 	return $rs;
	// }

	// GET DETAIL ITEM FROM MATOUT NUMBER --ir
	function getTotAmount($key=''){		
		$q = "SELECT SUM(amount) as tot_amount
						FROM pur_podet
						WHERE po_id = '$key'";
		$rs=$this->db->query($q)->result();
		// print_r($rs);		
		return $rs;
	}

	// GET DETAIL ITEM FROM MATOUT NUMBER --ir
	function getTotQty($key=''){		
		$q = "SELECT SUM(z.qty) as tot_qty
						FROM pur_podet z
						WHERE z.po_id = '$key'";
		$rs=$this->db->query($q)->result();		
		return $rs;
	}
	
	function addDet($data){
		unset($data['NmBarang']);
		unset($data['Sat']);
		// unset($data['amount']);
		// print_r($data);
		$run = $this->db->insert($this->tbl2, $data); 
		if ($this->db->affected_rows()>0){
			$rs=true;
			$log=$this->addLog($data['nama_user'],"Tambah Detail ".$this->title.", ID = ".$data['po_id'].", MAT ID = ".$data['mat_id'].", Part No = ".$data['PartNo']);	
		} 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->addLog($id,"Ubah ".$this->title." Header, ID = ".$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->addLog($pk,"Ubah ".$this->title." Detail, ID = ".$pk['po_id'].", MAT ID = ".$data['mat_id']);	
		} else {
			$rs=false;
		}
		
		return $rs;
	}
	function delDet1($id){
		$this->db->delete($this->tbl2, array($this->pk2 => $id)); 		
		if ($this->db->affected_rows()>0){
			$rs=true;
			$log=$this->addLog($id,"Hapus ".$this->title." Detail, ID = ".$id);	
		} else {
			$rs=false;
		}		
		
		return $rs;		
	}
	function delPo($id){
		$rhdr = $this->getRow($id);
		$url = 'http://'.$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'];
		$rsm = "";
		$q = "SELECT b.po_id 
					FROM pur_pohdr a, mat_inchdr_sub b 
					WHERE a.po_id='$id' 
					AND b.po_id = '$id'
					AND a.po_type = '0'";
		$row = $this->db->query($q)->result();
		if(empty($row)){
			// $this->db->delete($this->tbl, array($this->pk => $id)); 		
			// $this->db->delete($this->tbl2, array($this->pk => $id)); 		
			// if ($this->db->affected_rows()>0){
			if ($this->db->delete($this->tbl, array($this->pk => $id)) && $this->db->delete($this->tbl2, array($this->pk => $id))){
				$no = $rhdr->po_no;
				$rs="success";
				$log=$this->addLog($id,"Hapus ".$this->title.", ID = ".$id.", NO = ".$no);	
			} else {
				$rs="failed";
			}
			return $rs;		
		}else{
			$rs="reject trxin";
			return $rs;
		}
	}
	function delSo($id){
		$rhdr = $this->getRow($id);
		$url = 'http://'.$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'];
		$rsm = "";
		$q = "SELECT b.ref_id 
						FROM pur_pohdr a, mat_outhdr_sub b 
						WHERE a.po_id='$id' 
						AND b.ref_id = '$id'
						AND a.po_type = '2'";
		$row = $this->db->query($q)->result();
		if(empty($row)){
			// $this->db->delete($this->tbl, array($this->pk => $id)); 		
			// $this->db->delete($this->tbl2, array($this->pk => $id)); 		
			// if ($this->db->affected_rows()>0){
			if ($this->db->delete($this->tbl, array($this->pk => $id)) && $this->db->delete($this->tbl2, array($this->pk => $id))){
				$no = $rhdr->po_no;
				$rs="success";
				$log=$this->addLog($id,"Hapus ".$this->title.", ID = ".$id.", NO = ".$no);	
			} else {
				$rs="failed";
			}
			return $rs;		
		}else{
			$rs="reject trxout";
			return $rs;
		}
	}
	
}