<?php
class Ar_mdl extends Base_Model {
    function __construct() {
        parent::__construct();
				$this->pk = 'id';
	}
	
	public function buat_kodeAr()   {
		$this->db->select("RIGHT(acc_araphdr.no,4) as kode", FALSE);
		$ap = "type = 'ar'";
		$this->db->where($ap);
		$this->db->order_by('no','DESC');    
		$this->db->limit(1);    
		$query = $this->db->get('acc_araphdr');      //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 = "AR-".date('m-Y')."-".$kodemax;    // hasilnya AR-TahunTanggal-0001 dst.
		
		return $kodejadi;  
  }
	
	function countAr($month)
	{
	$q = "SELECT count(no) AS no FROM acc_araphdr WHERE type = 'ar' AND MONTH(date)='".$month."'
	LIMIT 1";
	$rs = $this->db->query($q)->row();
	return $rs;
	 }

	function getHdr($page,$rows,$key=''){
		$offset = ($page-1)*$rows;
		$result = array();
		
		$q = "SELECT a.*,a.".$this->pk." AS id,
				DATE_FORMAT(a.date,'%d/%m/%Y') AS Fdate,
				FORMAT(a.tot_amount, 2) AS ftot_amount,
				CONCAT(a.accountd_id,' ',a.acctnamed) AS facctnamed,
			  c.NmPrshn, e.matout_no
			  FROM ".$this->tbl." a
			  LEFT JOIN ".$this->tbl3." c ON c.".$this->pk3."=a.partner_id
			  LEFT JOIN ".$this->tbl5." e ON e.".$this->pk5."=a.ref_id
				WHERE type = '".$this->type."'";
				
		$q .= " AND (a.no LIKE '%$key%' OR c.NmPrshn LIKE '%$key%'
					or e.matout_no LIKE '%$key%')
			  ORDER BY a.id desc";
		$rstot=$this->db->query($q)->result();
		// dd($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(a.date,'%d/%m/%Y') AS Fdate,
				FORMAT(a.tot_amount, 2) AS ftot_amount,
				CONCAT(a.accountd_id,' ',a.acctnamed) AS facctnamed,
			  c.NmPrshn, e.matout_no, c.AlmtPrshn
			  FROM ".$this->tbl." a
			  LEFT JOIN ".$this->tbl3." c ON c.".$this->pk3."=a.partner_id
			  LEFT JOIN ".$this->tbl5." e ON e.".$this->pk5."=a.ref_id
			  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 b.*,
				FORMAT(qty, 2) AS Fqty,
				FORMAT(price, 4) AS Fprice,
				FORMAT(amount, 2) AS Famount,
				d.*,
				d.KdBarang AS KdBarang2, d.NmBarang AS NmBarang2, d.Sat AS Sat2
		  	FROM ".$this->tbl2." b 
		  	LEFT JOIN ".$this->tbl4." d ON KdBarang = item_id 
		  	WHERE b.arap_id = '$key' 
		  	ORDER BY id 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 b.*,
				FORMAT(qty, 2) AS Fqty,
				FORMAT(price, 4) AS Fprice,
				FORMAT(amount, 2) AS Famount,
				d.*,
				d.KdBarang AS KdBarang2, d.NmBarang AS NmBarang2, d.Sat AS Sat2
		  	FROM ".$this->tbl2." b 
		  	LEFT JOIN ".$this->tbl4." d ON d.KdBarang = b.item_id 
		  	WHERE b.arap_id = '$key' 
		  	ORDER BY b.id 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(a.date,'%d/%m/%Y') AS Fdate,
				b.*,
				FORMAT(qty, 2) AS Fqty,
				FORMAT(price, 4) AS Fprice,
				FORMAT(b.amount, 2) AS Famount,
			  c.NmPrshn, 
				d.*,
				d.KdBarang AS KdBarang2, d.NmBarang AS NmBarang2, d.Sat AS Sat2,
				e.matout_no
				FROM ".$this->tbl." a
				LEFT JOIN ".$this->tbl2." b ON b.".$this->pk2." = a.id
			  LEFT JOIN ".$this->tbl3." c ON c.".$this->pk3." = a.partner_id
		  	LEFT JOIN ".$this->tbl4." d ON d.".$this->pk4." = b.item_id 
			  LEFT JOIN ".$this->tbl5." e ON e.".$this->pk5." = a.ref_id
				WHERE type = '".$this->type."'";
			  
		if ($key != ""){  
			$q .= " AND (a.no LIKE '%$key%' OR c.NmPrshn LIKE '%$key%' OR b.item_id LIKE '%$key%')";
		}	
			  
		if($dtdari != '' && $dtsampai != ''):
			$q .= " AND date BETWEEN '".$this->dmys2ymd($dtdari)."' AND '".$this->dmys2ymd($dtsampai)."'";
		endif;
		
		$q .= " ORDER BY date, a.no DESC";
		$rstot=$this->db->query($q)->result();
		// dd($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 AR
	function checkAR($ar_no=''){		
		$q = "SELECT COUNT(no) as no
						FROM ".$this->tbl."
						WHERE no = '$ar_no'
						LIMIT 1";
		$rs=$this->db->query($q)->result();		
		return $rs;
	}

	// CHECK FOR DUPLICATE DO --ir
	function checkDO2($ref_id=''){		
		$q = "SELECT COUNT(ref_id) as ref_id
						FROM ".$this->tbl."
						WHERE ref_id = '$ref_id'
						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=true;
			$log=$this->addLog($data['nama_user'],"Tambah Header ".$this->title.", NO AR = ".$data['no']);	
		} else {
			$rs=false;
		}
		
		return $rs;
	}
	// GET DETAIL ITEM FROM MATOUT NUMBER --ir
	function getMatoutdet($key=''){		
		$q = "SELECT y.*, x.matout_id, z.id, z.tot_amount
						FROM mat_outhdr_sub x, mat_outdet_sub y, acc_araphdr z
						WHERE x.matout_id = y.matout_id
						AND z.ref_id = y.matout_id
						AND x.matout_id = '$key'";
		$rs=$this->db->query($q)->result();		
		return $rs;
	}
	// GET DETAIL ITEM FROM MATOUT NUMBER --ir
	function getTotAmount($key=''){		
		$q = "SELECT z.tot_amount
						FROM acc_araphdr z
						WHERE z.ref_id = '$key'";
		$rs=$this->db->query($q)->result();		
		return $rs;
	}
	
	function addDet($data){
		unset($data['NmBarang']);
		unset($data['Sat']);
		$run = $this->db->insert($this->tbl2, $data); 
		if ($this->db->affected_rows()>0){
			// $this->setTotalAmount($data['arap_id']);
			$rs=true;
			$log=$this->addLog($data['nama_user'],"Tambah Detail ".$this->title.", AR ID = ".$data['arap_id'].", ITEM CODE = ".$data['item_id']);	
		} 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);	
		} else {
			$rs=false;
		}		
		
		return $rs;
	}
	function editJournal($data,$id){
		$rhdr = $this->getRow($id);
		$run = $this->db->update('acc_araphdr', $data, array($this->pk => $id)); 
		$accdb = $this->load->database('accounting', TRUE);
		$data = array (
			'slipnum' 	=> $rhdr->no
		);
		$run2 = $accdb->delete('accsliphdr', $data);
		if ($run && $run2){
			$rs=true;
			$log=$this->addLog($id,"Reset Journal ".$this->title.", AR ID : ".$id.", AR NO : ".$data['slipnum']);	
		} else {
			$rs=false;
		}	
		return $rs;
	}
	function editAmount($tot_amount,$id){
		$run = $this->db->update($this->tbl, $tot_amount, array($this->pk => $id));
		if ($this->db->affected_rows()>0){
			$rs=true;
			$log=$this->addLog($id,"Ubah ".$this->title." Header".$id);	
		} else {
			$rs=false;
		}		
		
		return $rs;
	}
	
	function editDet($data,$pk){
		unset($data['NmBarang']);
		unset($data['Sat']);
		//print_r($data);
		$run = $this->db->update($this->tbl2, $data, $pk); 
		//echo $this->db->last_query();
		if ($this->db->affected_rows()>0){
			// $this->setTotalAmount($data['arap_id']);
			$rs=true;
			$log=$this->addLog($pk,"Ubah ".$this->title." Detail".$pk);	
		} else {
			$rs=false;
		}
		
		return $rs;
	}
	function createJournal($id){
		$rhdr = $this->getRow($id);
		$rdet = $this->getDetAll($rhdr->id);
		
		$accdb = $this->load->database('accounting', TRUE);
		$data = array (
			'kd_cmpy' => '01',
			'slipdate' => $rhdr->date,
			'sliptype' => '1',
			'slipcode' => 'SJ',
			'slipnum' 	=> $rhdr->no,
			'slipcurr' => $rhdr->currency,
			'slipdesc' => $rhdr->description
		);
		$run = $accdb->insert('accsliphdr', $data); 
		$tax = $rhdr->tax;
		$tot_amount = $rhdr->tot_amount;
		$tot_amount_credit 	= $rhdr->tot_amount;
		$tot_amount_debit 	= $rhdr->tot_amount;
		
		if ($tax == "ppn"){
			$tot_amount_tax 		= $tot_amount * 0.1;
			$tot_amount_credit 	= $tot_amount;
			$tot_amount_debit 	= $tot_amount + $tot_amount_tax;
			$acctnum						= "1909.1";
			$desc = "Tax Ppn";
		}else if ($tax == "pph21"){
			$tot_amount_tax 		= $tot_amount * 0.02;
			$tot_amount_credit 	= $tot_amount;
			$tot_amount_debit 	= $tot_amount + $tot_amount_tax;
			$acctnum						= "1902.1";
			$desc = "Tax Pph21";
		}else if ($tax == "pph23"){
			$tot_amount_tax 		= $tot_amount * 0.02;
			$tot_amount_credit 	= $tot_amount;
			$tot_amount_debit 	= $tot_amount + $tot_amount_tax;
			$acctnum						= "1903.2";
			$desc = "Tax Pph23";
		}else if ($tax == "pph22_Oil"){
			$tot_amount_tax 		= $tot_amount * 0.003;
			$tot_amount_debit 	= $tot_amount;			
			$tot_amount_credit 	= $tot_amount + $tot_amount_tax;
			$acctnum						= "311000134";
			$desc = "Tax Pph22 Oil";
		}else if ($tax == "pph22_Import"){
			$tot_amount_tax 		= $tot_amount * 0.025;
			$tot_amount_debit 	= $tot_amount;			
			$tot_amount_credit 	= $tot_amount + $tot_amount_tax;
			$acctnum						= "311000134";
			$desc = "Tax Pph22 Import";
		}else if ($tax == "ppn+pph23"){
			$tot_amount_tax 		= $tot_amount * 0.1;
			$tot_amount_tax1 		= $tot_amount * 0.02;
			$tot_amount_credit 	= $tot_amount;
			$tot_amount_debit 	= $tot_amount + $tot_amount_tax + $tot_amount_tax1;
			$acctnum						= "1909.1";
			$acctnum1						= "1903.2";
			$desc = "Tax Ppn";
			$desc1 = "Tax Pph23";
		}else{
			$tot_amount;
			$tot_amount_credit;
		}
		
		if ($accdb->affected_rows()>0){
			$slipid = $accdb->insert_id();
			$data_det_d = array (
				'header_id' => $slipid,
				'acctnum' => $rhdr->accountd_id,
				'currency' => $rhdr->currency,
				'kurs' => 'pajak',
				'rate' => $rhdr->rate,
				'dbcd' => 'D',
				'desc' => $rhdr->acctnamed,
				'amount' => $tot_amount_debit,
				'valconvert' => $tot_amount_debit * $rhdr->rate
			);
			$run = $accdb->insert('accslipdet', $data_det_d);
			
			$data_det_c = array (
				'header_id' => $slipid,
				'acctnum' => '6102',
				'currency' => $rhdr->currency,
				'kurs' => 'pajak',
				'rate' => $rhdr->rate,
				'dbcd' => 'C',
				'desc' => 'Sales Of Production',
				'amount' => $tot_amount_credit,
				'valconvert' => $tot_amount_credit * $rhdr->rate
			);
			$run = $accdb->insert('accslipdet', $data_det_c);
			if ($tax != "none"){
				$data_det_c_tax = array (
					'header_id' => $slipid,
					'acctnum' => $acctnum,
					'currency' => $rhdr->currency,
					'kurs' => 'pajak',
					'rate' => $rhdr->rate,
					'dbcd' => 'C',
					'desc' => $desc,
					'amount' => $tot_amount_tax,
					'valconvert' => $tot_amount_tax * $rhdr->rate
				);
				$run = $accdb->insert('accslipdet', $data_det_c_tax);				
			}
			
			if ($tax == "ppn+pph23"){
				$data_det_c_tax = array (
					'header_id' => $slipid,
					'acctnum' => $acctnum1,
					'currency' => $rhdr->currency,
					'kurs' => 'pajak',
					'rate' => $rhdr->rate,
					'dbcd' => 'C',
					'desc' => $desc1,
					'amount' => $tot_amount_tax1,
					'valconvert' => $tot_amount_tax1 * $rhdr->rate
				);
				$run = $accdb->insert('accslipdet', $data_det_c_tax);
			}
			
			$this->load->database('default', TRUE);
			$data = array (
				'created_journal' => 'yes'
			);
		$pk[$this->pk] = $id;
		$no = array('no' => $rhdr->no); 
		$run = $this->db->update($this->tbl, $data, $pk);
		$log=$this->addLog($id,"Create Journal ".$this->title.", AR ID : ".$id.", NO AR : ".$no['no']);
			$rs=true;
		} else {
			$rs=false;
		}
		
		return $rs;
	}
	function setTotalAmount($id){
		$q = "SELECT SUM(amount) AS tot_amount FROM ".$this->tbl2." WHERE arap_id='$id'";
		$tot_amount = $this->db->query($q)->row()->tot_amount;
		
		$data = array (
			'tot_amount' => $tot_amount
		);
		$pk[$this->pk] = $id; 
		
		$run = $this->db->update($this->tbl, $data, $pk); 
		if ($this->db->affected_rows()>0){
			$rs = true;
		} else {
			$rs = true;
		}
		
		return $rs;
	}
	function checkDO($key=''){	
		$q = "SELECT MAX(ID) as ID from tpb_barang";
		$rs=$tpbdb->query($q)->result();
		
		return $rs[0];
	}
	function del($id){
		// $this->db->delete($this->tbl, array('id' => $id)); 		
		// $this->db->delete($this->tbl2, array('arap_id' => $id)); 
		// if ($this->db->affected_rows()>0){
		if ($this->db->delete($this->tbl, array('id' => $id)) && $this->db->delete($this->tbl2, array('arap_id' => $id))){
			$rs="success";
			$log=$this->addLog($id,"Hapus ".$this->title." ".$id);	
		} else {
			$rs="failed";
		}		
		return $rs;		
	}
}