<?php

class M_general extends CI_Model{		
	public function get_tgl()
	{
		return date("Y-m-d");
	}
	public function get_timestamp()
	{
		return date("Y-m-d H:i:s");
	}	
	public function format_date($format="Y-m-d", $tgl='')
	{
		if($tgl==''){ $tgl = date("Y-m-d"); }
		return date($format, strtotime($tgl));
	}	
	public function add_date($tgl)
	{
		return date("Y-m-d", strtotime("+1 day", strtotime($tgl)));
	}
	public function roman($bln)
	{
		if($bln == '1' ) return 'I';
		else if($bln == '2' ) return 'II';
		else if($bln == '3' ) return 'III';
		else if($bln == '4' ) return 'IV';
		else if($bln == '5' ) return 'V';
		else if($bln == '6' ) return 'VI';
		else if($bln == '7' ) return 'VII';
		else if($bln == '8' ) return 'VIII';
		else if($bln == '9' ) return 'IX';
		else if($bln == '10' ) return 'X';
		else if($bln == '11' ) return 'XI';
		else if($bln == '12' ) return 'XII';
		
	}
	

	public function no_urut_otomatis($id_sms, $id_smt)
	{
		$this->db->where('id_sms',$id_sms);
		$this->db->where('mulai_smt',$id_smt);
		$count = $this->Content_m->select_db('pddikti_tr_mahasiswa_pt')->num_rows();
		//echo $this->db->last_query();
		return str_pad($count+1, 3, '0', STR_PAD_LEFT);
	}

	public function number($n = '')
	{
		return ($n === '') ? '' : number_format( (float) $n, 0, ',', '.');
	}
	public function currency($n = 0)
	{
		$cur = number_format( (float) abs($n), 2, '.', ',');
		if($n >=0){ return "<span>Rp. ".$cur."</span>";}
		else{ return "<span class='red'>- Rp. ".$cur."</span>";}
	}
	public function currency_invoices($n = 0)
	{
		$cur = number_format( (float) abs($n), 0, '.', ',');
		if($n >=0){ return "<span>Rp. ".$cur."</span>";}
		else{ return "<span class='red'>- Rp. ".$cur."</span>";}
	}

	public function currency_edit($n = 0)
	{
		$cur = number_format( (float) abs($n), 0, '.', ',');
		return $cur;
	}
	public function currency_ammount($n = 0)
	{
		$cur = number_format( (float) abs($n), 2, '.', ',');
		return "$cur";
	}
	public function currency_total_ammount($n = 0)
	{
		$cur = number_format( (float) abs($n), 2, '.', ',');
		return "Rp. $cur";
	}
	
	public function msg_box($class, $msg)
	{
		
		if($class=='success'){
			$this->session->set_flashdata('msg',"<div class='alert alert-success'><strong>Success</strong><br>$msg</div>");
		}
		else{ $this->session->set_flashdata('msg',"<div class='alert alert-warning'><strong>Something when wrong !!</strong><br>$msg</div>"); }
	}
	
	//genric query
	public function select_db($tbl, $sort_col=NULL, $sort='ASC')
	{
		$this->db->where('deleted','0');
		if($sort_col != NULL) {$this->db->order_by($sort_col, $sort); }
		return $this->db->get($tbl);
	}

	public function select_db_custom($tbl, $where, $id, $sort_col=NULL, $sort='ASC')
	{
		$this->db->where($where,$id);
		if($sort_col != NULL) {$this->db->order_by($sort_col, $sort); }
		return $this->db->get($tbl);
	}

	public function get_row_custom2($tbl,$where, $id, $where2, $id2)
	{
		$this->db->where('deleted','0');
		$this->db->where($where,$id);
		$this->db->where($where2,$id2);
		$query = $this->db->get($tbl);
		return $query->row();	
	}

	public function select_all($tbl, $sort_col=NULL, $sort='ASC')
	{
		$this->db->where('deleted','0');
		if($sort_col != NULL) {$this->db->order_by($sort_col, $sort); }
		return $this->db->get($tbl);
	}

	public function select($tbl, $sort_col=NULL, $sort='ASC')
	{
		if($sort_col != NULL) {$this->db->order_by($sort_col, $sort); }
		return $this->db->get($tbl);
	}

	public function get_row($tbl, $id)
	{
		$this->db->where('id',$id);
		$query = $this->db->get($tbl);
		return $query->row();	
	}
	public function get_row_custom($tbl,$where, $id)
	{
		$this->db->where('deleted','0');
		$this->db->where($where,$id);
		$query = $this->db->get($tbl);
		return $query->row();	
	}
	public function get_data($tbl, $id, $col)
	{
		if($id==NULL || $id==''){ return '';}
		else if(!$this->exists($tbl, 'id', $id)){ return '';  }
		$this->db->where('id',$id);
		$query = $this->db->get($tbl);
		return $query->row()->$col;
	}
	public function get_value($tbl=NULL, $col=NULL, $val=NULL, $field=NULL)
	{
		if($tbl==NULL || $col==NULL || $val==NULL || $field==NULL){ return '';}
		else if(!$this->exists($tbl, $col, $val)){ return '';  }
		$this->db->where($col,$val);
		$query = $this->db->get($tbl);
		return $query->row()->$field;
	}
	public function get_id($tbl, $col, $val)
	{
		$this->db->where($col,$val);
		$query = $this->db->get($tbl);
		return $query->row()->id;
	}
	function get_ai($tbl)
	{		
		$row = $this->db->query("SHOW TABLE STATUS LIKE '$tbl' ")->row();		
		return $row->Auto_increment;
	}
	

	public function get_no_otomatis($tbl)
	{
		  $i=1;
		  $cek_akhir=0;
		  $all_inv = count($this->select_db($tbl)->result());
		  //echo $all_inv;
		  foreach ($this->select_db($tbl)->result() as $key ) {
		      // IF DEPAN ADA TULISAN INV BARU JALANIN KODE DI BAWAH INI BIAR GA ERROR NNTI  
		      // IF DEPAN ADA TULISAN INV BARU JALANIN KODE DI BAWAH INI BIAR GA ERROR NNTI  
		      //echo $this->db->last_query();

		      //$aas = explode("/", $key->no_inv);
		      //$no = str_replace("INV", "", $aa[0]);
		      $ii = sprintf("%03d",$i);

		      $yo = 0; 
		      foreach ($this->select_db($tbl)->result() as $keyb ) {
		          $aa = explode("/", $keyb->no_inv);

		          if($tbl == "invoice")
		         	$no = str_replace("INV", "", $aa[0]);
		      	  else if($tbl == "journal_entry")
		      	  	$no = str_replace("JU", "", $aa[0]);
		      	  else 
		      	  	$no = str_replace("PI", "", $aa[0]);
		          
		          //echo $yo." ".$ii." ".$no."<br>";
		          if($ii == $no)
		          {
		              //echo "ada $ii<br>";
		              //echo "input pake no $ii"."<br>";
		              //$cek_akhir=1;
		              break;
		          }
		          $yo++;

		          if($yo == $all_inv)
		          {
		            //echo "inv ini yang di pake $ii <br>";
		            break;
		          }
		      }

		      if($yo == $all_inv)
		      {
		        break;
		      }

		      $i++;
		     
		  }

		  //echo $i;

		  if($cek_akhir == 0)
		  {
		    $ii = sprintf("%03d",$i);
		    return $ii;
		    //echo "inputs pake no $ii"."<br>";
		  }


	}


	public function get_no_otomatis_spend($tbl, $dari=NULL)
	{
		  $i=1;
		  $cek_akhir=0;
		  $all_inv = count($this->select_db($tbl)->result());
		  //echo $all_inv;
		  foreach ($this->select_db($tbl)->result() as $key ) {
		      // IF DEPAN ADA TULISAN INV BARU JALANIN KODE DI BAWAH INI BIAR GA ERROR NNTI  
		      // IF DEPAN ADA TULISAN INV BARU JALANIN KODE DI BAWAH INI BIAR GA ERROR NNTI  
		      //echo $this->db->last_query();

		      //$aas = explode("/", $key->no_inv);
		      //$no = str_replace("INV", "", $aa[0]);
		      $ii = sprintf("%03d",$i);

		      $yo = 0; 
		      foreach ($this->select_db($tbl)->result() as $keyb ) {
		          $aa = explode("/", $keyb->ref);

		          if($tbl == "receipt" && $dari == "spend money")
		      	  	$no = str_replace("SMO", "", $aa[0]);
		      	  else if($tbl == "receipt" && $dari == "receive money")
		      	  	$no = str_replace("RMO", "", $aa[0]);
		      	  else 
		      	  	$no = str_replace("ETC", "", $aa[0]);
		          
		         // echo $dari."<br>";
		          //echo $yo." ".$ii." ".$no."<br>";
		          if($ii == $no)
		          {
		              //echo "ada $ii<br>";
		              //echo "input pake no $ii"."<br>";
		              //$cek_akhir=1;
		              break;
		          }
		          $yo++;

		          if($yo == $all_inv)
		          {
		            //echo "inv ini yang di pake $ii <br>";
		            break;
		          }
		      }

		      if($yo == $all_inv)
		      {
		        break;
		      }

		      $i++;
		     
		  }

		  //echo $i;

		  if($cek_akhir == 0)
		  {
		    $ii = sprintf("%03d",$i);
		    return $ii;
		    //echo "inputs pake no $ii"."<br>";
		  }


	}


	public function insert_db($tabel, $data)
	{
		if($this->db->insert($tabel,$data))
		return true;
		return false;
	}

	public function insert_batch_db($tabel, $data)
	{
		if($this->db->insert_batch($tabel,$data))
		return true;
		return false;
	}

	public function update_batch_db($tabel, $data, $id)
	{
		if($this->db->update_batch($tabel, $data, $id))
		return true;
		return false;
	}

	function update_db($tabel, $id, $data)
	{
		$this->db->where('id',$id);
		if($this->db->update($tabel,$data))
		return true;
		return false;
	}

	function update_db_custom($tabel, $where, $id, $data)
	{
		$this->db->where($where,$id);
		if($this->db->update($tabel,$data))
		return true;
		return false;
	}

	function delete_db($tabel, $id)
	{
		$this->db->where('id', $id);
		if($this->db->delete($tabel))
		return true;
		return false;
	}

	function delete_db_custom($tabel, $where, $id)
	{
		$this->db->where($where, $id);
		if($this->db->delete($tabel))
		return true;
		return false;
	}
	public function exists($tabel,$col,$value)
	{
		$this->db->where($col,$value);
		$query = $this->db->get($tabel);		
		return ($query->num_rows()==1);
	}
	
	public function num_row($tabel,$col,$value)
	{
		$this->db->where('deleted','0');
		$this->db->where($col,$value);
		$query = $this->db->get($tabel);		
		return $query->num_rows();
	}

	public function get_pending()
	{

		$this->db->select('siak_tr_receipt_item.*, siak_re_account_chart.grup');    
		$this->db->from('siak_tr_receipt_item');
		$this->db->join('siak_re_account_chart', 'siak_re_account_chart.id = siak_tr_receipt_item.acc');
		$ignore = array(5,4,3,1);
		$this->db->where_not_in('siak_re_account_chart.grup', $ignore);
		$this->db->where('siak_tr_receipt_item.id_inv','0');
		$this->db->where('siak_tr_receipt_item.id_supp','0');
		$ignore2 = array('starting balance debit','starting balance credit');
		$this->db->where_not_in('dari',$ignore2);
		
		return $this->db->get();


	}		

	public function get_pending_purchase()
	{
		$query = "SELECT * FROM siak_tr_purchase_item WHERE id_inv = 0";
		
		return $this->db->query($query);
	}

	public function get_all_receipt()
	{
		$this->db->select('siak_tr_receipt_item.*, siak_re_account_chart.grup');    
		$this->db->from('siak_tr_receipt_item');
		$this->db->join('siak_re_account_chart', 'siak_re_account_chart.id = siak_tr_receipt_item.acc');
		
		return $this->db->get();
	}

	public function get_all_journal_entry()
	{
		$this->db->select('siak_tr_journal_entry_item.*, siak_re_account_chart.grup');    
		$this->db->from('siak_tr_journal_entry_item');
		$this->db->join('siak_re_account_chart', 'siak_re_account_chart.id = siak_tr_journal_entry_item.acc');
		
		return $this->db->get();
	}


	public function get_subtotal($id_inv)
	{
		//echo $id_inv."<br>";

		$inv_ttl = $this->m_akun->inv_ttl($id_inv);
		
		$subtotal=$inv_ttl;
		foreach ($this->get_table_receipt('siak_tr_receipt_item', $id_inv)->result() as $key ) {
			///echo $key->id." ";
			//echo $key->price." ";
			if($key->dari == "spend money")
			{
				$subtotal = $subtotal + $key->price;
			}
			else if ($key->dari == "receive money")
			{
				$subtotal = $subtotal - $key->price;
			}
			
		}


		return $subtotal;
		

	}

	public function select_all_expense($tbl, $sort_col=NULL, $sort='ASC')
	{
		$this->db->where('grup','2');
		if($sort_col != NULL) {$this->db->order_by($sort_col, $sort); }
		return $this->db->get($tbl);
	}

	public function get_purchase($id , $from=null , $to = null, $accounting_method=null)
	{
		if($from == null){
			$query = "SELECT siak_tr_purchase.tgl, siak_tr_purchase.id_supp , siak_tr_purchase.no_inv , siak_tr_purchase_item.* FROM siak_tr_purchase 
			inner join siak_tr_purchase_item on (siak_tr_purchase.id = siak_tr_purchase_item.id_purchase)
			where siak_tr_purchase_item.acc = $id and siak_tr_purchase_item.deleted='0'";
		}else if($accounting_method == "accural_basis"){
			$query = "SELECT siak_tr_purchase.tgl, siak_tr_purchase.id_supp , siak_tr_purchase.no_inv , siak_tr_purchase_item.* FROM siak_tr_purchase 
			inner join siak_tr_purchase_item on (siak_tr_purchase.id = siak_tr_purchase_item.id_purchase)
			where (siak_tr_purchase_item.acc = $id and siak_tr_purchase.tgl >= $from and siak_tr_purchase.tgl <= $to) and siak_tr_purchase_item.deleted='0'";
		}else if($accounting_method == "cash_basis"){
			//BELUM
			$query = "SELECT siak_tr_purchase.tgl, siak_tr_purchase.id_supp , siak_tr_purchase.no_inv , siak_tr_purchase_item.* FROM siak_tr_purchase 
			inner join siak_tr_purchase_item on (siak_tr_purchase.id = siak_tr_purchase_item.id_purchase)
			where siak_tr_purchase_item.acc = -1 and siak_tr_purchase_item.deleted='0'";
		}
		
		
		return $this->db->query($query);
	}

	public function get_receipt_item($id , $from=null, $to=null, $accounting_method=null)
	{
		if($from == null){
			$query = "SELECT siak_tr_receipt.tgl, siak_tr_receipt.acc_id , siak_tr_receipt.ref , siak_tr_receipt_item.* FROM siak_tr_receipt 
			inner join siak_tr_receipt_item on (siak_tr_receipt.id = siak_tr_receipt_item.id_receipt)
			where siak_tr_receipt_item.acc = $id and siak_tr_receipt_item.deleted='0'";
		}else if($accounting_method == "accural_basis"){
			$query = "SELECT siak_tr_receipt.tgl, siak_tr_receipt.acc_id , siak_tr_receipt.ref , siak_tr_receipt_item.* FROM siak_tr_receipt 
			inner join siak_tr_receipt_item on (siak_tr_receipt.id = siak_tr_receipt_item.id_receipt)
			where (siak_tr_receipt_item.acc = $id and siak_tr_receipt.tgl >= $from and siak_tr_receipt.tgl <= $to) and siak_tr_receipt_item.deleted='0'";
		}else if($accounting_method == "cash_basis"){
			//BELUM
			$query = "SELECT siak_tr_receipt.tgl, siak_tr_receipt.acc_id , siak_tr_receipt.ref , siak_tr_receipt_item.* FROM siak_tr_receipt 
			inner join siak_tr_receipt_item on (siak_tr_receipt.id = siak_tr_receipt_item.id_receipt)
			where siak_tr_receipt_item.acc = -1 and siak_tr_receipt_item.deleted='0'";
		}

		
		return $this->db->query($query);
		
	}

	public function get_receipt_item_tax()
	{
		$query = "SELECT siak_tr_receipt.tgl, siak_tr_receipt.acc_id , siak_tr_receipt.ref , siak_tr_receipt_item.* FROM siak_tr_receipt 
		inner join siak_tr_receipt_item on (siak_tr_receipt.id = siak_tr_receipt_item.id_receipt)
        where tax > 0 and siak_tr_receipt_item.deleted='0'";
		
		return $this->db->query($query);
		
	}

	public function get_receipt_item_starting_balance()
	{
		$query = "SELECT siak_tr_receipt.tgl, siak_tr_receipt.acc_id , siak_tr_receipt.ref , siak_tr_receipt_item.* FROM siak_tr_receipt 
		inner join siak_tr_receipt_item on (siak_tr_receipt.id = siak_tr_receipt_item.id_receipt)
		WHERE (dari = 'starting balance debit' or dari = 'starting balance credit' or siak_tr_receipt_item.acc = 30) and siak_tr_receipt_item.deleted='0'";

        return $this->db->query($query);
	}

	public function get_sales_invoice($id ,$from=null, $to=null, $accounting_method=null)
	{
		if($from == null){
			$query = "SELECT siak_tr_invoice.id as id_invo, siak_tr_invoice.tgl, siak_tr_invoice.id_cust , siak_tr_invoice.no_inv , siak_tr_inv_item.* FROM siak_tr_invoice 
			inner join siak_tr_inv_item on (siak_tr_invoice.id = siak_tr_inv_item.id_inv)
			where siak_tr_inv_item.acc = $id and siak_tr_inv_item.deleted='0'";
		}else{
			$query = "SELECT siak_tr_invoice.id as id_invo, siak_tr_invoice.tgl, siak_tr_invoice.id_cust , siak_tr_invoice.no_inv , siak_tr_inv_item.* FROM siak_tr_invoice 
			inner join siak_tr_inv_item on (siak_tr_invoice.id = siak_tr_inv_item.id_inv)
			where siak_tr_inv_item.acc = $id and ( siak_tr_invoice.tgl >= $from and siak_tr_invoice.tgl <= $to ) and siak_tr_inv_item.deleted='0'";
		}
		
		
		return $this->db->query($query);
	}

	public function get_sales_invoice_tax()
	{
		$query = "SELECT siak_tr_invoice.id as id_invo, siak_tr_invoice.tgl, siak_tr_invoice.id_cust , siak_tr_invoice.no_inv , siak_tr_inv_item.* 
		FROM siak_tr_invoice 
		inner join siak_tr_inv_item on (siak_tr_invoice.id = siak_tr_inv_item.id_inv) 
		where siak_tr_inv_item.tax != 0 and siak_tr_inv_item.deleted='0'";
		
		return $this->db->query($query);	
	}

	/*
	public function get_customer()
	{
		$query = "SELECT siak_tr_invoice.id as id_invo, siak_tr_invoice.tgl, siak_tr_invoice.id_cust , siak_tr_invoice.no_inv , siak_tr_inv_item.* , siak_tr_customer.id as id_customer, siak_tr_customer.name
		FROM siak_tr_invoice 
		inner join siak_tr_inv_item on (siak_tr_invoice.id = siak_tr_inv_item.id_inv)
		inner join siak_tr_customer on (siak_tr_customer.id = siak_tr_invoice.id_cust)
		WHERE siak_tr_inv_item.deleted='0'";

		return $this->db->query($query);
	}
	*/


	
	public function get_receipt_item_customer($id)
	{
		$query = "SELECT siak_tr_receipt.tgl, siak_tr_receipt.acc_id , siak_tr_receipt.ref , siak_tr_receipt_item.* FROM siak_tr_receipt 
		inner join siak_tr_receipt_item on (siak_tr_receipt.id = siak_tr_receipt_item.id_receipt)
		where siak_tr_receipt_item.id_cust = '$id' and siak_tr_receipt_item.deleted='0'";
		
		return $this->db->query($query);
		
	}

	public function get_sales_invoice_customer($id)
	{
		$query = "SELECT siak_tr_invoice.id as id_invo, siak_tr_invoice.tgl, siak_tr_invoice.id_cust , siak_tr_invoice.no_inv , siak_tr_inv_item.* FROM siak_tr_invoice 
		inner join siak_tr_inv_item on (siak_tr_invoice.id = siak_tr_inv_item.id_inv)
		where siak_tr_invoice.id_cust = '$id' and siak_tr_inv_item.deleted='0'";
		
		return $this->db->query($query);
	}


	public function get_cash_detail($id)
	{
		$query = "SELECT siak_tr_receipt.tgl, siak_tr_receipt.acc_id , siak_tr_receipt.ref , siak_tr_receipt_item.* , siak_tr_cash_account.id as id_cash, siak_tr_cash_account.name
		FROM siak_tr_receipt 
		inner join siak_tr_receipt_item on (siak_tr_receipt.id = siak_tr_receipt_item.id_receipt)
		inner join siak_tr_cash_account on (siak_tr_cash_account.id = siak_tr_receipt.acc_id)
		where siak_tr_cash_account.id = '$id' and siak_tr_receipt_item.deleted='0'";

		return $this->db->query($query);
	}

	public function get_purchase_invoice($id)
	{
		$query = "SELECT siak_tr_purchase.id as id_purchase, siak_tr_purchase.tgl, siak_tr_purchase.no_inv, siak_tr_purchase.id_supp, siak_tr_purchase_item.* 
		FROM siak_tr_purchase
		inner join siak_tr_purchase_item on ( siak_tr_purchase.id = siak_tr_purchase_item.id_purchase )
		where id_supp = '$id' and siak_tr_purchase_item.deleted='0'";

		return $this->db->query($query);
	}

	
	public function get_account_payable($id ,$id_supp)
	{
		$query = "SELECT siak_tr_receipt.tgl, siak_tr_receipt.acc_id , siak_tr_receipt.ref , siak_tr_receipt_item.* FROM siak_tr_receipt 
		inner join siak_tr_receipt_item on (siak_tr_receipt.id = siak_tr_receipt_item.id_receipt)
        where acc = '$id' AND id_supp='$id_supp' and siak_tr_receipt_item.deleted='0'";

		return $this->db->query($query);
	}



	public function get_account_payable_detail_purchase($id,$id_purchase)
	{
		$query = "SELECT siak_tr_receipt.tgl, siak_tr_receipt.acc_id , siak_tr_receipt.ref , siak_tr_receipt_item.* FROM siak_tr_receipt 
		inner join siak_tr_receipt_item on (siak_tr_receipt.id = siak_tr_receipt_item.id_receipt)
        where siak_tr_receipt_item.id_purchase= '$id_purchase' and siak_tr_receipt_item.deleted='0'";

		return $this->db->query($query);
	}

	public function get_purchase_tax()
	{
		$query = "SELECT siak_tr_purchase.tgl, siak_tr_purchase.id_supp , siak_tr_purchase.no_inv , siak_tr_purchase_item.* 
		FROM siak_tr_purchase 
		inner join siak_tr_purchase_item on (siak_tr_purchase.id = siak_tr_purchase_item.id_purchase) 
		where siak_tr_purchase_item.tax != '0' and siak_tr_purchase_item.deleted='0'";

		return $this->db->query($query);
	}

	public function get_id_account($id)
	{
		$query = "SELECT siak_tr_receipt.tgl, siak_tr_receipt.acc_id , siak_tr_receipt.ref , siak_tr_receipt_item.* FROM siak_tr_receipt 
		inner join siak_tr_receipt_item on (siak_tr_receipt.id = siak_tr_receipt_item.id_receipt)
        
        where acc = '30' and siak_tr_receipt_item.id = '$id' and siak_tr_receipt_item.deleted='0'";

		return $this->db->query($query)->row()->acc_id;
		
	}

	public function get_journal_entry($id,$table=null,$where=null, $from=null, $to=null, $accounting_method=null)
	{
		if($from == null){
			if($table != null){
				$query = "SELECT siak_tr_journal_entry.* , siak_tr_journal_entry_item.* 
				FROM siak_tr_journal_entry 
				inner join siak_tr_journal_entry_item on (siak_tr_journal_entry.id_journal_entry = siak_tr_journal_entry_item.id_journal_entry) 
		        WHERE acc = '$id' AND $table = '$where' AND siak_tr_journal_entry_item.deleted='0'";
			}else{
				$query = "SELECT siak_tr_journal_entry.* , siak_tr_journal_entry_item.* 
				FROM siak_tr_journal_entry 
				inner join siak_tr_journal_entry_item on (siak_tr_journal_entry.id_journal_entry = siak_tr_journal_entry_item.id_journal_entry) 
		        WHERE acc = '$id' AND siak_tr_journal_entry_item.deleted='0'";
			}
		}else if($accounting_method == "accural_basis"){
			$query = "SELECT siak_tr_journal_entry.* , siak_tr_journal_entry_item.* 
			FROM siak_tr_journal_entry 
			inner join siak_tr_journal_entry_item on (siak_tr_journal_entry.id_journal_entry = siak_tr_journal_entry_item.id_journal_entry) 
	        WHERE acc = '$id' AND siak_tr_journal_entry_item.deleted='0'";
		}else if($accounting_method == "cash_basis"){
			//BELUM
			$query = "SELECT siak_tr_journal_entry.* , siak_tr_journal_entry_item.* 
			FROM siak_tr_journal_entry 
			inner join siak_tr_journal_entry_item on (siak_tr_journal_entry.id_journal_entry = siak_tr_journal_entry_item.id_journal_entry) 
	        WHERE acc = '-1' AND siak_tr_journal_entry_item.deleted='0'";
		}
		

		return $this->db->query($query);
		
	}

	public function get_journal_entry_inv_balance($id,$table=null,$where=null , $table2=null, $where2=null)
	{
		$query = "SELECT siak_tr_journal_entry.* , siak_tr_journal_entry_item.* 
		FROM siak_tr_journal_entry 
		inner join siak_tr_journal_entry_item on (siak_tr_journal_entry.id_journal_entry = siak_tr_journal_entry_item.id_journal_entry) 
        WHERE acc = '$id' AND $table = '$where' AND $table2 = '$where2' AND siak_tr_journal_entry_item.deleted='0'";

        return $this->db->query($query);
	}

	/*
	public function get_pending()
	{
		$this->db->query("SELECT receipt_item.* , account_chart.grup FROM receipt_item INNER JOIN account_chart ON (receipt_item.acc = account_chart.id) WHERE receipt_item.id_inv = '0' ")
		
		
		$query = $this->db->get();

	}		
	*/

	public function get_account_delete_starting_balance($id_cash_account)
	{
		$query = "SELECT siak_tr_receipt.*, siak_tr_receipt_item.*, siak_tr_cash_account.*
		from siak_tr_receipt
		inner join siak_tr_receipt_item on (siak_tr_receipt.id = siak_tr_receipt_item.id_receipt)
		inner join siak_tr_cash_account on (siak_tr_cash_account.id = siak_tr_receipt.acc_id)
		where siak_tr_cash_account.id='$id_cash_account' and siak_tr_cash_account.deleted='0' and id_asset='0' and dari = 'starting balance debit'";
		
		return $this->db->query($query);
	}

	public function get_format_date($date)
	{
		return $date;
	}

	public function get_total_ikut_bhs_inggris()
	{
		$this->db->select('dm.id_rekomendasi');
		$this->db->from('permohonan_daftar_mhs dm');
		$this->db->where('id_rekomendasi','1');
		$q = $this->db->get();
		return $q->num_rows();
	}

	public function get_total_tidak_ikut_bhs_inggris()
	{
		$this->db->select('dm.id_rekomendasi');
		$this->db->from('permohonan_daftar_mhs dm');
		$this->db->where('id_rekomendasi','2');
		$q = $this->db->get();
		return $q->num_rows();
	}

	public function get_notif_reg($id_cabang, $gel)
	{
		$this->db->select('*');
		$this->db->from('permohonan_daftar_mhs mhs');
		$this->db->where('status_complete','1');
		$where = "(bukti_transfer = '' OR bukti_transfer is null)";
		$this->db->where($where);
		$this->db->where('gelombang', $gel);
		$this->db->where('id_cabang',$id_cabang);
		$q = $this->db->get();
		return $q->num_rows();
	}

	public function get_notif_ulg($id_cabang, $gel)
	{
		$this->db->select('*');
		$this->db->from('permohonan_daftar_mhs mhs');
		$this->db->where('status_complete','3');
		$where = "(bukti_transfer2 = '' OR bukti_transfer2 is null)";
		$this->db->where($where);
		$this->db->where('gelombang', $gel);
		$this->db->where('id_cabang',$id_cabang);
		$q = $this->db->get();
		return $q->num_rows();
	}

	public function get_notif_registrasi($id_cabang, $gel)
	{
		$this->db->select('*');
		$this->db->from('permohonan_daftar_mhs mhs');
		$this->db->where('status_complete','1');
		$where = "(bukti_transfer != '' OR bukti_transfer is not null)";
		$this->db->where($where);
		$this->db->where('gelombang', $gel);
		$this->db->where('id_cabang',$id_cabang);
		$q = $this->db->get();
		return $q->num_rows();
	}

	public function get_notif_daftar_ulang($id_cabang, $gel)
	{
		$this->db->select('*');
		$this->db->from('permohonan_daftar_mhs mhs');
		$this->db->where('status_complete','3');
		$where = "(bukti_transfer2 != '' OR bukti_transfer2 is not null)";
		$this->db->where($where);
		$this->db->where('gelombang', $gel);
		$this->db->where('id_cabang',$id_cabang);
		$q = $this->db->get();
		return $q->num_rows();
	}

	public function multibahasa($bahasa, $text)
	{
		if($this->session->userdata('lang') != ''){
			return $this->M_general->get_value('multilanguage', 'text', $text, $bahasa);
		}else{
			return $this->M_general->get_value('multilanguage', 'text', $text, 'english');
		}
		
	}          

	public function get_wilayah($id)
	{
		$this->db->_protect_identifiers=false;
		$this->db->select('kec.id_wil as id, concat(kec.nm_wil," - ",kab.nm_wil," - ",prov.nm_wil) as text');
		$this->db->join('pddikti_re_wilayah kab', 'kec.id_induk_wilayah = kab.id_wil and kab.id_level_wil="2"');
		$this->db->join('pddikti_re_wilayah prov', 'kab.id_induk_wilayah = prov.id_wil and prov.id_level_wil="1"');
		$this->db->where(array('kec.id_level_wil'=>'3','kec.id_wil'=>$id));
		$q = $this->db->get('pddikti_re_wilayah kec');
		
		if($q->num_rows() > 0){
			$data['mhs_wilayah']=preg_replace('/\s+/', ' ', $q->row()->text);
			//echo $data['mhs_wilayah'];
			return $data['mhs_wilayah'];
		}else{
			return '';
		}

	}


	public function generic_query($dari=null, $id_client=null)
	{
		$this->db->select('*');
		$this->db->from('t_employee e');
		$this->db->order_by('e.active','desc');
		//$this->db->where('e.active','1');
		$this->db->where('e.id_pool',$this->session->userdata('id_pool'));
		if($id_client!=null){
			$this->db->where('e.id_client',$id_client);
		}
		if($dari!=null){
			if($dari == 'applicant'){
				$array_in = array('1','2');
				$this->db->where('status_prescreening','17');
				$this->db->where_in('status_applicant',$array_in);
			}
			if($dari == 'candidate'){
				$array_in = array('4','5');
				$this->db->where('status_prescreening','17');
				$this->db->where('status_applicant','3');
				$this->db->where_in('status_candidate',$array_in);
			}
			if($dari == 'crewmember'){
				$this->db->where('status_prescreening','17');
				$this->db->where('status_applicant','3');
				$this->db->where('status_candidate','6');
			}
		}
	    $q = $this->db->get();
		return $q;
	}
	
	public function get_status($status)
	{
		$str = $this->M_general->get_value('r_status','id_status',$status,'status');
		$color = $this->M_general->get_value('r_status','id_status',$status,'status_color');
		return "<span style='background:$color; color:#fff'>".$str."</span>";
		
	}

	public function departure()
	{
		$this->db->select('ea.*, e.first_name, e.last_name, e.status_crew_member, e.id_cust');
		$this->db->from('t_employee_application ea');
		$this->db->join('t_employee e','e.id_employee = ea.employee_id');
		//$this->db->where('ea.active','1');
		$this->db->where('e.id_pool',$this->session->userdata('id_pool'));
		$this->db->order_by('ea.active','desc');
		return $this->db->get();
	}

	public function historyticket($application_id=null)
	{
		$this->db->select('t.*, e.first_name, e.last_name');
		$this->db->from('t_employee_ticket t');
		$this->db->join('t_employee_application a','t.application_id = a.id_application');
		$this->db->join('t_employee e', 'a.employee_id = e.id_employee');
		//$this->db->where('t.active','1');
		if($application_id!=null){
			$this->db->where('t.application_id',$application_id);
		}
		$this->db->where('e.id_pool',$this->session->userdata('id_pool'));
		return $this->db->get();
	}

	public function manual()
	{
		$this->db->select('*');
		$this->db->from('t_manual m');
		$this->db->where('m.active','1');
		$this->db->where('m.id_pool',$this->session->userdata('id_pool'));
		return $this->db->get();
	}

	public function user()
	{
		$this->db->select('*');
		$this->db->from('bbmc_re_staff s');
		$this->db->where('active','1');
		$this->db->where('s.deleted','0');
		return $this->db->get();
	}
	

	public function logsystem()
	{
		$this->db->select('*');
		$this->db->from('a_log l');
		$this->db->order_by('log_date','desc');
		return $this->db->get();
	}

	public function get_setting($untuk)
	{
		$this->db->select('*');
		$this->db->from($untuk);
		//$this->db->where('active','1');
		return $this->db->get();
	}

	public function get_custom_setting($query)
	{
		$query = $this->db->query($query);
		//$this->db->where('active','1');
		return $query;
	}

	public function get_kolom($untuk, $custom=null)
	{	
		$query = $this->db->query("SELECT * FROM $untuk");		
		$array = $query->list_fields();
		//unset()
		//var_dump($array);
		//$array = $this->db->list_fields($untuk);


		/*
		foreach($array as $key => $one) {
		    if(strpos($one, 'id_') !== false)
		        unset($array[$key]);
		}
		*/

		return $array;
	}

	public function get_custom_column($query)
	{
		$query = $this->db->query($query);		
		$array = $query->list_fields();
		return $array;
	}

	public function get_text($field)
	{
		if($field == 'id_bidang'){
			$pp = 'BIDANG';
		}else{
			$p = str_replace("_", " ", $field); 
        	$pp = str_replace("id", "", $p);
		}
		

        return strtoupper($pp);
	}

	public function get_kolom_value($kolom, $valu_kolom)
	{
		if($kolom == 'id_jabatan'){
          echo strtoupper($this->M_general->get_value('global_jabatan','id_global_jabatan',$valu_kolom,'nama_jabatan'));
        }else if($kolom == 'tindakan_lab'){
          echo strtoupper($this->M_general->get_value('siak_re_account_chart','id',$valu_kolom,'name'));
        }else if($kolom == 'id_bidang'){
          echo strtoupper($this->M_general->get_value('bbmc_re_bidang','id_bidang',$valu_kolom,'bidang'));
        }else if($kolom == 'document_type_id'){
          echo strtoupper($this->M_general->get_value('r_document_type','id_document_type',$valu_kolom,'document_type'));
        }else if($kolom == 'price_umum' || $kolom == 'price_bpjs' || $kolom == 'price_shhc' || $kolom == 'price_inhealth' || $kolom == 'price_mega'){
          echo $this->Content_m->currency2($valu_kolom);
        }else if($kolom == 'country_id'){
          echo strtoupper($this->M_general->get_value('r_country','id_country',$valu_kolom,'country'));
        }else if($kolom == 'ship_id'){
          echo strtoupper($this->M_general->get_value('r_ship','id_ship',$valu_kolom,'ship_name'));
        }else if($kolom == 'id_bhp'){
          echo $this->M_general->get_value('bbmc_re_bahan_habis_pakai','id_re_bahan_habis_pakai',$valu_kolom,'nama_item');
        }else if($kolom == 'id_unit'){
          echo $this->M_general->get_value('bbmc_re_unit','id_unit',$valu_kolom,'unit');
        }else if($kolom == 'ship_type_id'){
          echo strtoupper($this->M_general->get_value('r_ship_type','id_ship_type',$valu_kolom,'ship_type'));
        }else if($kolom == 'active'){
          echo $valu_kolom=='0'?'INACTIVE':'<i style="color:green" class="fa fa-check"></i>';
        }else if($kolom == 'document_file' || $kolom == 'charge_file' ||  $kolom == 'certificate_file' || $kolom == 'reference_file' || $kolom == 'contract_file' || $kolom == 'ticket_file'){
          $upload_path = $this->Content_m->get_value('global_data','id_global_data','1','data_value');
          echo "<a onclick='return alert('Delete this data?')' target='_Blank' style='color:blue; text-decoration:underline' href='".site_url($upload_path.$valu_kolom)."'>".strtoupper($valu_kolom)."</a>";
        }else if($kolom == 'addresstype_id'){
          echo strtoupper($this->M_general->get_value('r_address_type','id_address_type',$valu_kolom,'address_type'));
        }else if($kolom == 'city'){
          echo strtoupper($this->M_general->get_value('r_city','id_city',$valu_kolom,'city'));
        }else if($kolom == 'phonetype_id'){
          echo strtoupper($this->M_general->get_value('r_phone_type','id_phone_type',$valu_kolom,'phone_type'));
        }else if($kolom == 'relationship_id'){
          echo strtoupper($this->M_general->get_value('r_relationship','id_relationship',$valu_kolom,'relationship'));
        }else if($kolom == 'interviewer_id'){
          echo strtoupper($this->M_general->get_value('r_interviewer','id_interviewer',$valu_kolom,'name'));
        }else if($kolom == 'interview_type_id'){
          echo strtoupper($this->M_general->get_value('r_interview_type','id_interview_type',$valu_kolom,'name'));
        }else if($kolom == 'charge_status_id' || $kolom == 'interview_status_id' || $kolom == 'document_status_id' || $kolom=='ticket_status_id' || $kolom == 'employee_status_id'){
          echo strtoupper($this->M_general->get_value('r_status','id_status',$valu_kolom,'status'));
        }else if($kolom == 'ship_position_id'){
          echo strtoupper($this->M_general->get_value('r_ship_position','id_ship_position',$valu_kolom,'position'));
        }else if($kolom == 'port_id' || $kolom == 'to_port_id' || $kolom == 'from_port_id' ){
          echo strtoupper($this->M_general->get_value('r_port','id_port',$valu_kolom,'port_name'));
        }else if($kolom == 'reason_id'){
          echo strtoupper($this->M_general->get_value('r_reason','id_reason',$valu_kolom,'reason'));
        }else if($kolom == 'harga_jual' || $kolom == 'harga_bpjs' || $kolom == 'harga_shhc' || $kolom == 'harga_inhealth' || $kolom == 'harga_mega'){
          echo $this->currency($valu_kolom);
        }else{
          echo strtoupper($valu_kolom);
        }
	}

	public function string_explode($untuk)
	{
		$go = explode("_", $untuk);
		$a = $go[1];
		$b = isset($go[2])?$go[2]:'';
		//echo $a;
		return $a." ".$b;
	}
	public function string_explode2($untuk)
	{
		$go = explode("_", $untuk);
		$a = $go[0];
		$b = isset($go[1])?$go[1]:'';
		//echo $a;
		return $a." ".$b;
	}


	public function get_count_client($id_client)
	{
		$this->db->select('*');
		$this->db->from('t_employee');
		$this->db->where('active','1');
		$this->db->where('id_client',$id_client);
		$q = $this->db->get();
		$q_count = $q->num_rows();

		return $q_count;
	}

	public function select_db2($tbl, $sort_col=NULL, $sort='ASC')
	{
		$this->db->where('active','1');
		if($sort_col != NULL) {$this->db->order_by($sort_col, $sort); }
		return $this->db->get($tbl);
	}

	public function get_jumlah_pasien_lab($tgl, $tipe_pasien)
	{
		$where = "(`status_perawatan` = '4' OR `pemeriksaan_khusus` LIKE '%cek_laboratorium%')";
		$this->db->where($where);
		$this->db->where('batal_periksa','0');
		$this->db->like('tgl_kunjungan',$tgl);
		$this->db->where('id_type_pasien',$tipe_pasien);
		$this->db->order_by('tgl_kunjungan');
		$q = $this->Content_m->get_kunjungan_pasien();

		return $q->num_rows();
	}

	public function get_query_pendapatan_pasien_lab($tgl, $tipe_pasien)
	{
		$arr = array();
		if($tipe_pasien == '1'){
			$this->db->where_in('id_tindakan',array('2'));
		}else if($tipe_pasien == '2'){
			$this->db->where_in('id_tindakan',array('14'));
		}else if($tipe_pasien == '3'){
			$this->db->where_in('id_tindakan',array('15'));
		}else if($tipe_pasien == '4'){
			$this->db->where_in('id_tindakan',array('16'));
		}else if($tipe_pasien == '5'){
			$this->db->where_in('id_tindakan',array('17'));
		}else{
			$this->db->where_in('id_tindakan',array('2','14','15','16','17'));
		}

		foreach ($this->M_general->select_db('siak_re_account_chart')->result() as $key ) {
			$arr[] = $key->id;
		}
		
		$this->db->select('*');
		$this->db->from('siak_tr_invoice i');
		$this->db->join('siak_tr_inv_item ii','i.id = ii.id_inv');
		$this->db->where('i.deleted','0');
		$this->db->where('ii.deleted','0');
		$this->db->where_in('ii.acc',$arr);
		$this->db->like('tgl',$tgl);
		$q = $this->db->get();

		return $q; 
	}

	public function get_pendapatan_pasien_lab($tgl, $tipe_pasien)
	{
		
		$ttl = 0;
		$q = $this->get_query_pendapatan_pasien_lab($tgl, $tipe_pasien);
		foreach ($q->result() as $key ) {
			$price = ($key->qty * $key->price) - (($key->qty * $key->price) * $key->disc / 100);
			$ttl=$ttl+$price;
		}
		
		return $ttl;

	}

	public function get_query_pengeluaran_pasien_lab($tgl, $tipe_pasien)
	{

		$this->db->select('ps.*, bhp.harga_jual as price, bhp.nama_item, p.id_type_pasien');
		$this->db->from('bbmc_tr_laboratorium_pengeluaran_stok ps');
		$this->db->join('bbmc_re_bahan_habis_pakai bhp','ps.id_bhp = bhp.id_re_bahan_habis_pakai');
		$this->db->join('bbmc_tr_kunjungan k','k.id_re_kunjungan = ps.id_re_kunjungan');
		$this->db->join('bbmc_tr_pasien p','p.id_re_pasien = k.id_re_pasien');
		$this->db->where('ps.deleted','0');
		$this->db->like('tgl',$tgl);
		$this->db->where('id_type_pasien',$tipe_pasien);
		$q = $this->db->get();

		return $q;
	}


	public function get_pengeluaran_pasien_lab($tgl, $tipe_pasien)
	{
		/*
		select ps.*, bhp.harga_jual, bhp.nama_item, p.id_type_pasien 
		from bbmc_tr_laboratorium_pengeluaran_stok ps
		join bbmc_re_bahan_habis_pakai bhp on  ps.id_bhp = bhp.id_re_bahan_habis_pakai 
		join bbmc_tr_kunjungan k on k.id_re_kunjungan = ps.id_re_kunjungan
		join bbmc_tr_pasien p on p.id_re_pasien = k.id_re_pasien
		where ps.deleted = '0'
		*/
		
		$ttl = 0;
		$q = $this->get_query_pengeluaran_pasien_lab($tgl, $tipe_pasien);
		foreach ($q->result() as $key ) {
			$price = ($key->qty * $key->price) ;
			$ttl = $ttl+$price;
		}

		return $ttl;


	}

	public function get_stok_bhp_lab($id_bhp)
	{

		$this->db->select('sum(qty) as jmlh');
	    $this->db->from('bbmc_tr_laboratorium_penyesuaian_stok lps');
	    $this->db->where('lps.deleted','0');
	    $this->db->where('lps.id_bhp',$id_bhp);
	    $q0 = @$this->db->get()->row()->jmlh;


		$this->db->select('sum(stok_keluar) as jmlh');
	    $this->db->from('bbmc_tr_amprahan a');
	    $this->db->join('bbmc_re_bahan_habis_pakai bhp','a.id_bhp = bhp.id_re_bahan_habis_pakai');
	    $this->db->where('a.deleted','0');
	    $this->db->where('a.id_unit','9');
	    $this->db->where('a.id_bhp',$id_bhp);
	    $q = @$this->db->get()->row()->jmlh;

	    /*
	    select * 
		from bbmc_tr_amprahan_request_new ar 
		join bbmc_tr_amprahan_request_new_detail ard on ar.id_amprahan_request_new = ard.id_amprahan_request_new 
		where ar.deleted = '0'
		and ar.id_unit = '9' and ar.approve = '1' and id_re_bahan_habis_pakai = '113'
		*/
		
		$this->db->select('sum(qty) as jmlh');
		$this->db->from('bbmc_tr_amprahan_request_new ar');
		$this->db->join('bbmc_tr_amprahan_request_new_detail ard','ar.id_amprahan_request_new = ard.id_amprahan_request_new');
		$this->db->where('ar.deleted','0');
		$this->db->where('ar.id_unit','9');
		$this->db->where('ar.approve','1');
		$this->db->where('id_re_bahan_habis_pakai',$id_bhp);
		$q2 = @$this->db->get()->row()->jmlh;


		$this->db->select('sum(qty) as jmlh');
	    $this->db->from('bbmc_tr_laboratorium_pengeluaran_stok lps');
	    $this->db->where('lps.deleted','0');
	    $this->db->where('lps.id_bhp',$id_bhp);
	    $q3 = @$this->db->get()->row()->jmlh;


	    return $q0+$q+$q2-$q3;
	}

	public function get_bhp_stok_lab(){
		
		/*
		$this->db->select('bhp.nama_item, a.id_bhp');
        $this->db->from('bbmc_tr_amprahan a');
        $this->db->join('bbmc_re_bahan_habis_pakai bhp','a.id_bhp = bhp.id_re_bahan_habis_pakai');
        $this->db->where('a.deleted','0');
        //$this->db->where('a.id_unit','9');
        $this->db->group_by('a.id_bhp');
        $q = $this->db->get();
		*/

		$this->db->select('bhp.nama_item, bhp.id_re_bahan_habis_pakai as id_bhp');
		$this->db->from('bbmc_re_bahan_habis_pakai bhp');
		$this->db->where('deleted','0');
		$this->db->where('nama_item !=','');
		$q = $this->db->get();



        return $q;
	}

	public function get_bhp_stok_lab_amprahan()
	{
		$this->db->select('*');
		$this->db->from('bbmc_tr_amprahan_request_new ar');
		$this->db->join('bbmc_tr_amprahan_request_new_detail ard','ar.id_amprahan_request_new = ard.id_amprahan_request_new');
		$this->db->where('ar.deleted','0');
		$this->db->where('ar.id_unit','9');
		$this->db->where('ar.approve','1');
		$this->db->where('id_re_bahan_habis_pakai',$id_bhp);
		$q = $this->db->get();

        return $q;

	}
}

?>