<?php
class Bpcp_mdl extends Base_Model {
    function __construct() {
        parent::__construct();
				$this->pk = 'id';
	}
	
	function getHdr($page,$rows,$key=''){
		$offset = ($page-1)*$rows;
		$result = array();
		
		$q = "SELECT b.no as no, b.ref_id, a.no as no_ap, b.".$this->pk." AS id, b.description, b.currency, b.rate,
				b.accountd_id, b.accountc_id, b.acctnamed, b.acctnamec, b.type as type, c.NmPrshn as supplier, b.created_journal,
				DATE_FORMAT(b.date,'%d/%m/%Y') AS date, 
				DATE_FORMAT(a.date,'%d/%m/%Y') AS Fdate,
				FORMAT(a.tot_amount, 2) AS tot_amount,
				FORMAT(a.tax_amount, 2) AS tax_amount,
				FORMAT(a.after_tax, 2) AS after_tax,
				CONCAT(b.accountd_id,' ',b.acctnamed) AS facctnamed,
			  c.NmPrshn, e.matin_no, a.tax
			  FROM ".$this->tbl." a
			  LEFT JOIN ".$this->tbl0." b ON b.".$this->pk0."=a.id
			  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 b.type in ('bp','cp')";
				
		$q .= " AND (a.no LIKE '%$key%' OR c.NmPrshn LIKE '%$key%')
			  ORDER BY b.date 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 b.no as no, a.no as ref_id, b.".$this->pk." AS id, b.description, a.currency, a.rate,
				b.accountd_id, b.accountc_id, b.acctnamed, b.acctnamec, b.type as type, c.NmPrshn as supplier, b.created_journal,
				b.date, 
				a.tot_amount,	
				a.tax_amount,
				a.after_tax,
				CONCAT(b.accountd_id,' ',b.acctnamed) AS facctnamed,
				c.NmPrshn, e.matin_no, a.tax
				FROM ".$this->tbl." a
				LEFT JOIN ".$this->tbl0." b ON b.".$this->pk0."=a.id
				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 b.".$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->tbl." a ON b.arap_id=a.id								
			  LEFT JOIN ".$this->tbl0." c ON c.".$this->pk0."=a.id								
		  	LEFT JOIN ".$this->tbl4." d ON KdBarang = b.item_id 
		  	WHERE c.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 f.*,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.matin_no
				FROM ".$this->tbl." a
				LEFT JOIN ".$this->tbl0." f ON f.ref_id = a.id
				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 f.type in ('bp','cp')";
			  
		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->tbl0." LIMIT 1";
		$rs=$this->db->query($q)->result();
		$autono=($rs)?$rs[0]->auto_no+1:1;
		
		return $autono;
	}
	//AUTO NUMBER BRCR
	function countAP($month)
	{
	$q = "SELECT count(no) AS no FROM acc_brbp WHERE type = 'bp' AND MONTH(date)='".$month."'
	LIMIT 1";
	$rs = $this->db->query($q)->row();
	return $rs;
	}
	// CHECK FOR DUPLICATE AR
	function checkAP($ar_no=''){		
		$q = "SELECT COUNT(no) as no
						FROM ".$this->tbl0."
						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->tbl0."
						WHERE ref_id = '$ref_id'
						LIMIT 1";
		$rs=$this->db->query($q)->result();		
		return $rs;
	}
	
	function add($data){
		$run = $this->db->insert($this->tbl0, $data); 
		if ($this->db->affected_rows()>0){
			$rs=true;
			$log=$this->addLog($data['nama_user'],"Tambah Header ".$this->title.", NO BPCP = ".$data['no']);	
		} else {
			$rs=false;
		}
		
		return $rs;
	}
	// GET DETAIL ITEM FROM MATIN NUMBER --ir
	function getMatindet($key=''){		
		$q = "SELECT y.*, x.matin_id, z.id
						FROM mat_inchdr_sub x, mat_incdet_sub y, acc_araphdr z
						WHERE x.matin_id = y.matin_id
						AND z.ref_id = y.matin_id
						AND x.matin_id = '$key'";
		$rs=$this->db->query($q)->result();		
		return $rs;
	}
	// GET DETAIL ITEM FROM MATIN 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.", BPCP ID = ".$data['arap_id'].", ITEM CODE = ".$data['item_id']);	
		} else {
			$rs=false;
		}
		
		return $rs;
	}
	
	function editHdr($data,$id){
		$run = $this->db->update($this->tbl0, $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($this->tbl0, $data, array($this->pk => $id)); 
		$accdb = $this->load->database('accounting', TRUE);
		$data = array (
			'slipnum' 	=> $rhdr->no
		);
		$run = $accdb->delete('accsliphdr', $data);
		if ($accdb->affected_rows()>0){
			$no = array('no' => $rhdr->no); 
			$rs=true;
			$log=$this->addLog($id,"Reset Journal ".$this->title.", BPCP ID : ".$id.", BPCP NO : ".$no['no']);	
		} 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);

		if($rhdr->type == "bp"){
			$slipcode = "BP";
		}else if ($rhdr->type == "cp"){
			$slipcode = "CP";
		}

		$data = array (
			'kd_cmpy' => '01',
			'slipdate' => $rhdr->date,
			'sliptype' => '1',
			'slipcode' => $slipcode,
			'slipnum' 	=> $rhdr->no,
			'slipcurr' => $rhdr->currency,
			'slipdesc' => $rhdr->description
		);
		$run = $accdb->insert('accsliphdr', $data); 
		$tax = $rhdr->tax;
		$tot_amount = $rhdr->tot_amount;
		$tax_amount = $rhdr->tax_amount;
		$tot_amount_debit = $rhdr->after_tax;
		$tot_amount_credit = $tot_amount;
		
		if ($tax == "ppn"){
			$acctnum						= "311000136";
			$desc = "Tax Ppn";
		}else if ($tax == "pph21"){
			$acctnum						= "311000133";
			$desc = "Tax Pph21";
		}else if ($tax == "pph23"){
			$acctnum						= "311000134";
			$desc = "Tax Pph23";
		}else if ($tax == "pph22_Oil"){
			$acctnum						= "311000134";
			$desc = "Tax pph22 Oil";
		}else if ($tax == "pph22_Import"){
			$acctnum						= "311000134";
			$desc = "Tax Pph22 Import";
		}else if ($tax == "ppn+pph23"){
			$tax_amount 		= $tot_amount * 0.1;
			$tax_amount1 		= $tot_amount * 0.02;
			$acctnum						= "311000136";
			$acctnum1						= "311000134";
			$desc = "Tax Ppn";
			$desc1 = "Tax Pph23";
		}else{
			$tot_amount;
			$tot_amount_debit;
		}
		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);
			
			if($tax != "none" && $tax != "pph23"){
				$data_det_d_tax = array (
					'header_id' => $slipid,
					'acctnum' => $acctnum,
					'currency' => $rhdr->currency,
					'kurs' => 'pajak',
					'rate' => $rhdr->rate,
					'dbcd' => 'C',
					'desc' => $desc,
					'amount' => $tax_amount,
					'valconvert' => $tax_amount * $rhdr->rate
				);
				$run = $accdb->insert('accslipdet', $data_det_d_tax);
			}

			if($tax == "pph23"){
				$data_det_d_tax = array (
					'header_id' => $slipid,
					'acctnum' => $acctnum,
					'currency' => $rhdr->currency,
					'kurs' => 'pajak',
					'rate' => $rhdr->rate,
					'dbcd' => 'D',
					'desc' => $desc,
					'amount' => $tax_amount,
					'valconvert' => $tax_amount * $rhdr->rate
				);
				$run = $accdb->insert('accslipdet', $data_det_d_tax);
			}

			if($tax == "ppn+pph23"){
				$data_det_d_tax = array (
					'header_id' => $slipid,
					'acctnum' => $acctnum1,
					'currency' => $rhdr->currency,
					'kurs' => 'pajak',
					'rate' => $rhdr->rate,
					'dbcd' => 'D',
					'desc' => $desc1,
					'amount' => $tax_amount1,
					'valconvert' => $tax_amount1 * $rhdr->rate
				);
				$run = $accdb->insert('accslipdet', $data_det_d_tax);
			}
			$data_det_c = array (
				'header_id' => $slipid,
				'acctnum' => $rhdr->accountc_id,
				'currency' => $rhdr->currency,
				'kurs' => 'pajak',
				'rate' => $rhdr->rate,
				'dbcd' => 'C',
				'desc' => $rhdr->acctnamec,
				'amount' => $tot_amount_credit,
				'valconvert' => $tot_amount_credit * $rhdr->rate
			);
			$run = $accdb->insert('accslipdet', $data_det_c);
			$this->load->database('default', TRUE);
			$data = array (
				'created_journal' => 'yes'
			);
		$pk[$this->pk] = $id; 
		$no = array('no' => $rhdr->no); 
		$run = $this->db->update($this->tbl0, $data, $pk);
		$log=$this->addLog($id,"Create Journal ".$this->title.", BPCP ID : ".$id.", NO BPCP : ".$no['no']); 
			$rs=true;
		} else {
			$rs=false;
		}
		
		return $rs;
	}

	
	function del($id){
		if ($this->db->delete($this->tbl0, 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;		
	}
}