<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class CT_accounting_report extends CI_Controller {
    function __construct()
    {
       parent::__construct();
    if (!$this->dx_auth->is_logged_in()) {
           header('HTTP/1.1 401 Unauthorize');die('expire'); 
        }
        
        $uid = $this->session->userdata('DX_user_id');
        
        if(!permissionAuth(2) && $uid != RID){ 
            header('HTTP/1.1 401 Unauthorize');die('noaccess');
        } 
		$this->load->helper('obj2array');
		$this->load->helper(array('url', 'form'));
	    $this->load->library(array('session', 'encrypt'));
		$this->load->model("accounting_report/mdl_accounting_report", 'mdl_accounting_report');
		$this->mysql =$this->load->database('mysql', TRUE);	
    }
    
    function coa(){
	
		$this->load->view("accounting_report/view_coa");
	
	}
	
	
	
	function balance_sheet(){
		
		$this->load->view("accounting_report/view_balance_sheet");
		
	}
	
	function income(){
		
		$this->load->view("accounting_report/view_profit_and_loss");
		
	}
	
	function equity(){
		
		$this->load->view("accounting_report/view_equity");
		
	}
	
	function cash_flow(){
		
		$this->load->view("accounting_report/view_cash_flow");
		
	}
	
	
	function kas_out_bc_in(){
		
		$this->load->view("accounting_report/view_kas_out_bc_in");
		
	}
	
	
	function kas_in_bc_out(){
		
		$this->load->view("accounting_report/view_kas_in_bc_out");
		
	}
	
	function validasi_tax_ap(){
		
		//$this->load->view("accounting_report/view_validasi_tax_ap");
		$this->load->view("accounting_report/view_validasi_tax_ar");
		
	}
	
	
	function validasi_tax_ar(){
		
		$this->load->view("accounting_report/view_validasi_tax_ar");
	
	}
	
	function data_efaktur_ap(){
		
		$this->load->view("accounting_report/view_efaktur_ap");
		//$this->load->view("accounting_report/view_validasi_tax_ar");
		
	}
	
	
	function data_efaktur_ar(){
		
		$this->load->view("accounting_report/view_efaktur_ar");
		
	}
	
	
	function invoice_image_upload_ap(){
		
		$this->load->view("accounting_report/view_invoice_upload_image_ap");
		
	}
	
	
	function invoice_image_upload_ar(){
		
		$this->load->view("accounting_report/view_invoice_upload_image_ar");
		
	}
	
	function faktur_image_upload_ap(){
		
		$this->load->view("accounting_report/view_faktur_upload_image_ap");
		
	}
	
	
	function faktur_image_upload_ar(){
		
		$this->load->view("accounting_report/view_faktur_upload_image_ar");
		
	}
	
	function surat_jalan_image_upload_ap(){
		
		$this->load->view("accounting_report/view_srtjln_upload_image_ap");
		
	}
	
	
	function surat_jalan_image_upload_ar(){
		
		$this->load->view("accounting_report/view_srtjln_upload_image_ar");
		
	}
	
	function data_coa(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_coa'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " 1=1 ";
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "coa_sap" ) {
						$searchString = $k->data;
						$where .= " and coa_sap like '%".$searchString."%'"; 
					}  else if ( $k->field == "name_account" ) {
						$searchString = $k->data;
						$where .= " and name_account like  '%".$searchString."%'"; 
					}  else if ( $k->field == "target_group1" ) {
						$searchString = $k->data;
						$where .= " and target_group1 like  '%".$searchString."%'"; 
					} else if ( $k->field == "target_group2" ) {
						$searchString = $k->data;
						$where .= " and target_group2 like  '%".$searchString."%'"; 
					} else if ( $k->field == "section1" ) {
						$searchString = $k->data;
						$where .= " and section1 like  '%".$searchString."%'"; 
					}  else if ( $k->field == "section2" ) {
						$searchString = $k->data;
						$where .= " and section2 like  '%".$searchString."%'"; 
					}  else if ( $k->field == "target_report" ) {
						$searchString = $k->data;
						$where .= " and target_report like  '%".$searchString."%'"; 
					}   else if ( $k->field == "active" ) {
						$searchString = $k->data;
						$where .= " and active like  '%".$searchString."%'"; 
					} 
				}
			}
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->coaAll($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->coaCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['id_coa']=$r->id_coa;
			$responce->rows[$i]['coa_sap']=$r->coa_sap;
			$responce->rows[$i]['name_account']=$r->name_account;
			$responce->rows[$i]['target_group1']=$r->target_group1;
			$responce->rows[$i]['target_group2']=$r->target_group2;
			$responce->rows[$i]['target_report']=$r->target_report;
			$responce->rows[$i]['periode']=$r->periode;
			$responce->rows[$i]['trx_amount']=$r->trx_amount;
			$responce->rows[$i]['section1']=$r->section1;;
			$responce->rows[$i]['section2']=$r->section2;
			$responce->rows[$i]['active']=$r->active;
			$responce->rows[$i]['create_date']=$r->create_date;
			$responce->rows[$i]['create_by']=$r->create_by;
			$responce->rows[$i]['update_date']=$r->update_date;
			$responce->rows[$i]['update_by']=$r->update_by;
			$i++;
		}
		echo json_encode($responce);
		
	
	}
	
	function view_coa_update(){
		$command = (isset($_POST['command']) && !empty($_POST['command']))?$_POST['command']:die('{"sts":"ERROR","desc":" Param  filename Tidak Ditemukan"}');
		if ($command == 'CREATE'){
			$id_coa='';
			$coa_sap = '';
			$name_account = '';
			$target_group1 = '';
			$target_group2 = '';
			$target_report = '';
			$section1 = '';
			$section2 = '';
			$active = '';
			$data['command'] = $command;
			$data['id_coa'] = $id_coa;
			$data['coa_sap'] = $coa_sap;
			$data['name_account'] = $name_account;
			$data['target_group1'] = $target_group1;
			$data['target_group2'] = $target_group2;
			$data['target_report'] = $target_report;
			$data['section1'] = $section1;
			$data['section2'] = $section2;
			$data['active'] = $active;
			$this->load->view('accounting_report/view_coa_update', $data);
			
			
		}
		
		if ($command == 'UPDATE'){
			$id_coa = (isset($_POST['id']) && !empty($_POST['id']))?$_POST['id']:die('{"sts":"ERROR","desc":" Param  filename Tidak Ditemukan"}');
		
			//$id_coa='';
			$coa_sap = '';
			$name_account = '';
			$target_group1 = '';
			$target_group2 = '';
			$target_report = '';
			$section1 = '';
			$section2 = '';
			$active = '';
			
		    $qry = $this->db->query("
				SELECT TOP 1 [id_coa]
					  ,[coa_sap]
					  ,[name_account]
					  ,[target_group1]
					  ,[target_group2]
					  ,[target_report]
					  ,[periode]
					  ,[trx_amount]
					  ,[section1]
					  ,[section2]
					  ,[active]
					  ,[create_date]
					  ,[create_by]
					  ,[update_date]
					  ,[update_by]
				  FROM [dbo].[tbl_acc_coa]
				where [id_coa] = $id_coa
			
			");
			
			foreach($qry->result() as $r){
				
				$coa_sap = $r->coa_sap;
				$name_account = $r->name_account;
				$target_group1 = $r->target_group1;
				$target_group2 = $r->target_group2;
				$target_report = $r->target_report;
				$section1 = $r->section1;
				$section2 = $r->section2;
				//$active = $r->coa_sap;	
				
			}	
			
			$data['command'] = $command;
			$data['id_coa'] = $id_coa;
			$data['coa_sap'] = $coa_sap;
			$data['name_account'] = $name_account;
			$data['target_group1'] = $target_group1;
			$data['target_group2'] = $target_group2;
			$data['target_report'] = $target_report;
			$data['section1'] = $section1;
			$data['section2'] = $section2;
			$data['active'] = $active;
			$this->load->view('accounting_report/view_coa_update', $data);
			
			
		}
		
		
		
		
	}
	
	function data_coa_trx(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_coa'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " 1=1 ";
		
		if (isset($_POST['bulan']) and isset($_POST['tahun'])){
			$bulan=$_POST['bulan'];
			$tahun=$_POST['tahun'];
			$where .= " and YEAR(periode)  = '$tahun' ";
		}
		
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "coa_sap" ) {
						$searchString = $k->data;
						$where .= " and coa_sap like '%".$searchString."%'"; 
					}  else if ( $k->field == "name_account" ) {
						$searchString = $k->data;
						$where .= " and name_account like  '%".$searchString."%'"; 
					}  else if ( $k->field == "trx_amount" ) {
						$searchString = $k->data;
						$where .= " and trx_amount like  '%".$searchString."%'"; 
					} else if ( $k->field == "periode" ) {
						$searchString = $k->data;
						$where .= " and periode like  '%".$searchString."%'"; 
					} 
				}
			}
		}
		
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->v_coatrxAll($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->v_coatrxCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['coa_sap']=$r->coa_sap;
			$responce->rows[$i]['name_account']=$r->name_account;
			$responce->rows[$i]['periode']=$r->periode;
			$responce->rows[$i]['trx_amount']=$r->trx_amount;
			
			$i++;
		}
		echo json_encode($responce);
		
	
	}
	
	function save_data_coa(){
		$command = (isset($_POST['command']) && !empty($_POST['command']))?$_POST['command']:die('{"sts":"ERROR","desc":" Param  filename Tidak Ditemukan"}');
		$id_coa_x = (isset($_POST['id_coa_x']))?$_POST['id_coa_x']:'';
		$txt_coa_sap = (isset($_POST['txt_coa_sap']))?$_POST['txt_coa_sap']:'';
		$txt_name_account = (isset($_POST['txt_name_account']))?$_POST['txt_name_account']:'';
		$txt_section1 = (isset($_POST['txt_section1']))?$_POST['txt_section1']:'';
		$txt_section2 = (isset($_POST['txt_section2']))?$_POST['txt_section2']:'';
		$txt_target_group1 = (isset($_POST['txt_target_group1']))?$_POST['txt_target_group1']:'';
		$txt_target_group2 = (isset($_POST['txt_target_group2']))?$_POST['txt_target_group2']:'';
		$txt_target_report = (isset($_POST['txt_target_report']))?$_POST['txt_target_report']:'';
		$username = $this->session->userdata('DX_username');
		if ($command == 'CREATE'){
			
			$qry = $this->db->query("
								
				BEGIN TRY
					SELECT 
					'00' AS status
					,' SUKSES' AS description				
				
				END TRY
				BEGIN CATCH 
					SELECT 
					'error' AS status
					,ERROR_MESSAGE() AS description;
					
				END CATCH; 
				SELECT 
					'00' AS status
					,' SUKSES' AS description
					
			
			");
			 $rs = $qry->result_array();
			 //// $r = $qry->result();		
			//// var_dump($r);die();
			 foreach ($rs as $row){
				 $responce = array("sts" => $row['status'], "desc" => $row['description']);
			 }  
			 echo json_encode($responce);
			
		} else if ($command == 'UPDATE'){
			
			
		
		
			$qry = $this->db->query("
								
				BEGIN TRY
					UPDATE tbl_acc_coa SET 
						[coa_sap] = '$txt_coa_sap'
					  ,[name_account] = '$txt_name_account'
					  ,[target_group1] = '$txt_target_group1'
					  ,[target_group2] = '$txt_target_group2'
					  ,[target_report] = '$txt_target_report'
						,[section1] = '$txt_section1'
						,[section2] = '$txt_section2'
						,update_by = '$username'
						,update_date =GETDATE()
						,active =''
					where [id_coa] = 	$id_coa_x
					
				
				
				END TRY
				BEGIN CATCH 
					SELECT 
					'error' AS status
					,ERROR_MESSAGE() AS description;
					
				END CATCH; 
				SELECT 
					'00' AS status
					,' SUKSES' AS description
					
			
			");
			 $rs = $qry->result_array();
			 //// $r = $qry->result();		
			//// var_dump($r);die();
			 foreach ($rs as $row){
				 $responce = array("sts" => $row['status'], "desc" => $row['description']);
			 }  
			 echo json_encode($responce);
			
			
			
			
			
			
		}
		
		
		
	}
	
	function balance_sheet_view_xls(){
		error_reporting(E_ALL ^ E_NOTICE);
		//$this->load->file("application/libraries/excel_reader2.php", true);
		
		//require_once 'excel_reader2.php';
		//$this->load->library('Spreadsheet_Excel_Reader');
		include_once ( APPPATH."libraries/excel_reader2.php");
		
		$filename = (isset($_POST['filename']) && !empty($_POST['filename']))?$_POST['filename']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$apppath = $this->config->item('APP_xls_acc_report');
		$path=$apppath.''.$filename;
		$data = new Spreadsheet_Excel_Reader($path, false);
		
		echo $data->dump(true,true);
		
		
	}
	
	
	function balance_sheet_rpt(){
		$periode = (isset($_POST['periode']) && !empty($_POST['periode']))?$_POST['periode']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		
		$dateToTest = "2015-03-01";
		$lastday = date('t',strtotime($dateToTest));
		
		$data = "PT. EDS MANUFACTURING INDONESIA" . "<br />";
		$data .= "LAPORAN POSISI KEUANGAN" . "<br />";
		$data .= $lastday . $periode;		
		
		$dateToTest = "2015-02-01";
		$lastday = date('t',strtotime($dateToTest));
		
				
		echo $data;
	}
	
	
	function profit_and_loss_view_xls(){
		error_reporting(E_ALL ^ E_NOTICE);
		//$this->load->file("application/libraries/excel_reader2.php", true);
		
		//require_once 'excel_reader2.php';
		//$this->load->library('Spreadsheet_Excel_Reader');
		include_once ( APPPATH."libraries/excel_reader2.php");
		
		$filename = (isset($_POST['filename']) && !empty($_POST['filename']))?$_POST['filename']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$apppath = $this->config->item('APP_xls_acc_report');
		$path=$apppath.''.$filename;
		$data = new Spreadsheet_Excel_Reader($path, false);
		
		echo $data->dump(true,true);
		
		
	}
	
	
	function data_acc_ar(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_coa'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		
		$where = " 1=1 ";
		$periode = " 1=1 ";
		if (isset($_POST['periode'])){
			$periode=$_POST['periode'];
			
			$where .= " and PERIOD  like '%$periode%' ";
		}
		
		if (isset($_POST['awal'])){
			$awal = $_POST['awal'];
			if($awal =='1') {
				$thn = date('Y');
				$where .= " and PERIOD  like '%$thn%' ";
			} 
			
		}	
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "INVNO" ) {
						$searchString = $k->data;
						$where .= " and INVNO like '%".$searchString."%'"; 
					}  else if ( $k->field == "INVDATE" ) {
						$searchString = $k->data;
						$where .= " and INVDATE like  '%".$searchString."%'"; 
					}  else if ( $k->field == "GL_ACC" ) {
						$searchString = $k->data;
						$where .= " and GL_ACC like  '%".$searchString."%'"; 
					} else if ( $k->field == "PERIOD" ) {
						$searchString = $k->data;
						$where .= " and PERIOD like  '%".$searchString."%'"; 
					} else if ( $k->field == "JOURNAL_DESC" ) {
						$searchString = $k->data;
						$where .= " and JOURNAL_DESC like  '%".$searchString."%'"; 
					}  else if ( $k->field == "POSTING_KEY" ) {
						$searchString = $k->data;
						$where .= " and POSTING_KEY like  '%".$searchString."%'"; 
					}  
				}
			}
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->v_acc_arall($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->v_acc_arCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
		
		$DEBIT = 0;	
		$CREDIT = 0;
		$BALANCE = 0;
		$DEBIT_USD = 0;
		$CREDIT_USD = 0;
		$BALANCE_USD = 0;
		
		$qry = $this->db->query("
			SELECT 
		sum(isnull([DEBIT], 0)) as [DEBIT]
      ,sum(isnull([CREDIT], 0)) as [CREDIT]
      ,sum(isnull([BALANCE], 0)) as [BALANCE]
      ,sum(isnull([DEBIT_USD], 0)) as [DEBIT_USD]
      ,sum(isnull([CREDIT_USD], 0)) as [CREDIT_USD]
      ,sum(isnull([BALANCE_USD], 0)) as [BALANCE_USD]
      
  FROM [dbo].[v_acc_ar]
			  
			  where $where
			  
			  
		");
		
		foreach ($qry->result() as $r){
				
			$DEBIT = $r->DEBIT;	
			$CREDIT = $r->CREDIT;	
			$BALANCE = $r->BALANCE;	
			$DEBIT_USD =$r->DEBIT_USD;	
			$CREDIT_USD = $r->CREDIT_USD;	
			$BALANCE_USD =$r->BALANCE_USD;	
					
		}
		
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['id_apar']=$r->id_apar;
			$responce->rows[$i]['DataType']=$r->DataType;
			$responce->rows[$i]['INVNO']=$r->INVNO;
			$responce->rows[$i]['INVDATE']=$r->INVDATE;
			$responce->rows[$i]['GL_ACC']=$r->GL_ACC;
			$responce->rows[$i]['PERIOD']=$r->PERIOD;
			$responce->rows[$i]['SAP_SOURCE']=$r->SAP_SOURCE;
			$responce->rows[$i]['JOURNAL_ID']=$r->JOURNAL_ID;
			$responce->rows[$i]['JOURNAL_DATE']=$r->JOURNAL_DATE;;
			$responce->rows[$i]['JOURNAL_DESC']=$r->JOURNAL_DESC;
			$responce->rows[$i]['POSTING_KEY']=$r->POSTING_KEY;
			$responce->rows[$i]['CURR']=$r->CURR;
			$responce->rows[$i]['DEBIT']=$r->DEBIT;
			$responce->rows[$i]['CREDIT']=$r->CREDIT;
			$responce->rows[$i]['BALANCE']=$r->BALANCE;
			$responce->rows[$i]['DEBIT_USD']=$r->DEBIT_USD;
			$responce->rows[$i]['CREDIT_USD']=$r->CREDIT_USD;
			$responce->rows[$i]['BALANCE_USD']=$r->BALANCE_USD;
			$responce->rows[$i]['IMPORT_DATE']=$r->IMPORT_DATE;
			$responce->rows[$i]['EXTRACT_DATE']=$r->EXTRACT_DATE;
			$responce->rows[$i]['id_bc']=$r->id_bc;
			$responce->rows[$i]['bc_type']=$r->bc_type;
			$i++;
		}
		
		
		$responce->userdata['DEBIT'] = $DEBIT;
		$responce->userdata['CREDIT'] = $CREDIT;
		$responce->userdata['BALANCE'] = $BALANCE;
		$responce->userdata['DEBIT_USD'] = $DEBIT_USD;
		$responce->userdata['CREDIT_USD'] = $CREDIT_USD;
		$responce->userdata['BALANCE_USD'] = $BALANCE_USD;
		
		echo json_encode($responce);
		
	
	}
	
	function data_acc_ap(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_coa'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " 1=1 ";
		$periode = " 1=1 ";
		
		if (isset($_POST['periode'])){
			$periode=$_POST['periode'];
			
			$where .= " and PERIOD  like '%$periode%' ";
		}
		
		if (isset($_POST['awal'])){
			$awal = $_POST['awal'];
			if($awal =='1') {
				$thn = date('Y');
				$where .= " and PERIOD  like '%$thn%' ";
			} 
			
		}	
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "INVNO" ) {
						$searchString = $k->data;
						$where .= " and INVNO like '%".$searchString."%'"; 
					}  else if ( $k->field == "INVDATE" ) {
						$searchString = $k->data;
						$where .= " and INVDATE like  '%".$searchString."%'"; 
					}  else if ( $k->field == "GL_ACC" ) {
						$searchString = $k->data;
						$where .= " and GL_ACC like  '%".$searchString."%'"; 
					} else if ( $k->field == "PERIOD" ) {
						$searchString = $k->data;
						$where .= " and PERIOD like  '%".$searchString."%'"; 
					} else if ( $k->field == "JOURNAL_DESC" ) {
						$searchString = $k->data;
						$where .= " and JOURNAL_DESC like  '%".$searchString."%'"; 
					}  else if ( $k->field == "POSTING_KEY" ) {
						$searchString = $k->data;
						$where .= " and POSTING_KEY like  '%".$searchString."%'"; 
					}  
				}
			}
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->v_acc_apall($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->v_acc_apCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
		
		$DEBIT = 0;	
		$CREDIT = 0;
		$BALANCE = 0;
		$DEBIT_USD = 0;
		$CREDIT_USD = 0;
		$BALANCE_USD = 0;
		
		$qry = $this->db->query("
			SELECT 
		sum(isnull([DEBIT], 0)) as [DEBIT]
      ,sum(isnull([CREDIT], 0)) as [CREDIT]
      ,sum(isnull([BALANCE], 0)) as [BALANCE]
      ,sum(isnull([DEBIT_USD], 0)) as [DEBIT_USD]
      ,sum(isnull([CREDIT_USD], 0)) as [CREDIT_USD]
      ,sum(isnull([BALANCE_USD], 0)) as [BALANCE_USD]
      
  FROM [dbo].[v_acc_ap]
			  
			  where $where
			  
			  
		");
		
		foreach ($qry->result() as $r){
				
			$DEBIT = $r->DEBIT;	
			$CREDIT = $r->CREDIT;	
			$BALANCE = $r->BALANCE;	
			$DEBIT_USD =$r->DEBIT_USD;	
			$CREDIT_USD = $r->CREDIT_USD;	
			$BALANCE_USD =$r->BALANCE_USD;	
					
		}
		
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['id_apar']=$r->id_apar;
			$responce->rows[$i]['DataType']=$r->DataType;
			$responce->rows[$i]['INVNO']=$r->INVNO;
			$responce->rows[$i]['INVDATE']=$r->INVDATE;
			$responce->rows[$i]['GL_ACC']=$r->GL_ACC;
			$responce->rows[$i]['PERIOD']=$r->PERIOD;
			$responce->rows[$i]['SAP_SOURCE']=$r->SAP_SOURCE;
			$responce->rows[$i]['JOURNAL_ID']=$r->JOURNAL_ID;
			$responce->rows[$i]['JOURNAL_DATE']=$r->JOURNAL_DATE;;
			$responce->rows[$i]['JOURNAL_DESC']=$r->JOURNAL_DESC;
			$responce->rows[$i]['POSTING_KEY']=$r->POSTING_KEY;
			$responce->rows[$i]['CURR']=$r->CURR;
			$responce->rows[$i]['DEBIT']=$r->DEBIT;
			$responce->rows[$i]['CREDIT']=$r->CREDIT;
			$responce->rows[$i]['BALANCE']=$r->BALANCE;
			$responce->rows[$i]['DEBIT_USD']=$r->DEBIT_USD;
			$responce->rows[$i]['CREDIT_USD']=$r->CREDIT_USD;
			$responce->rows[$i]['BALANCE_USD']=$r->BALANCE_USD;
			$responce->rows[$i]['IMPORT_DATE']=$r->IMPORT_DATE;
			$responce->rows[$i]['EXTRACT_DATE']=$r->EXTRACT_DATE;
			$responce->rows[$i]['id_bc']=$r->id_bc;
			$responce->rows[$i]['bc_type']=$r->bc_type;
			$i++;
		}
		
		
		$responce->userdata['DEBIT'] = $DEBIT;
		$responce->userdata['CREDIT'] = $CREDIT;
		$responce->userdata['BALANCE'] = $BALANCE;
		$responce->userdata['DEBIT_USD'] = $DEBIT_USD;
		$responce->userdata['CREDIT_USD'] = $CREDIT_USD;
		$responce->userdata['BALANCE_USD'] = $BALANCE_USD;
		echo json_encode($responce);
		
	
	}
	
	
	
	function data_v_acc_ap_inv(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_coa'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		//$where = " car is not null ";
		$where = "1=1 ";
		if (isset($_POST['periode'])){
			$periode=$_POST['periode'];
			
			$where .= " and PERIOD  like '%$periode%' ";
		}
		
		if (isset($_POST['awal'])){
			$awal = $_POST['awal'];
			if($awal =='1') {
				$thn = date('Y');
				$where .= " and PERIOD  like '%$thn%' ";
			} 
			
		}	
		
		
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "INVNO" ) {
						$searchString = $k->data;
						$where .= " and INVNO like '%".$searchString."%'"; 
					}  else if ( $k->field == "INVDATE" ) {
						$searchString = $k->data;
						$where .= " and INVDATE like  '%".$searchString."%'"; 
					}  else if ( $k->field == "GL_ACC" ) {
						$searchString = $k->data;
						$where .= " and GL_ACC like  '%".$searchString."%'"; 
					} else if ( $k->field == "PERIOD" ) {
						$searchString = $k->data;
						$where .= " and PERIOD like  '%".$searchString."%'"; 
					} else if ( $k->field == "JOURNAL_DESC" ) {
						$searchString = $k->data;
						$where .= " and JOURNAL_DESC like  '%".$searchString."%'"; 
					}  else if ( $k->field == "POSTING_KEY" ) {
						$searchString = $k->data;
						$where .= " and POSTING_KEY like  '%".$searchString."%'"; 
					}  
				}
			}
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->v_acc_ap_invall($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->v_acc_ap_invCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['INVNO']=$r->INVNO;
			$responce->rows[$i]['INVDATE']=$r->INVDATE;
			$responce->rows[$i]['GL_ACC']=$r->GL_ACC;
			$responce->rows[$i]['DEBIT']=$r->DEBIT;
			$responce->rows[$i]['CREDIT']=$r->CREDIT;
			$responce->rows[$i]['BALANCE']=$r->BALANCE;
			$responce->rows[$i]['DEBIT_USD']=$r->DEBIT_USD;
			$responce->rows[$i]['CREDIT_USD']=$r->CREDIT_USD;
			$responce->rows[$i]['BALANCE_USD']=$r->BALANCE_USD;
			$responce->rows[$i]['typedok']=$r->typedok;
			$responce->rows[$i]['DokNo']=$r->DokNo;
			$responce->rows[$i]['DokTg']=$r->DokTg;
			$responce->rows[$i]['CAR']=$r->CAR;
			$responce->rows[$i]['BC23No']=$r->BC23No;
			$responce->rows[$i]['BC23Tg']=$r->BC23Tg;
			$responce->rows[$i]['PERIOD']=$r->PERIOD;
			
			$i++;
		}
		echo json_encode($responce);
		
	
	}
	
	function data_v_acc_ar_inv(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_coa'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		//$where = " car is not null ";
		
		$where = " 1=1 ";
		$periode = " 1=1 ";
		if (isset($_POST['periode'])){
			$periode=$_POST['periode'];
			
			$where .= " and PERIOD  like '%$periode%' ";
		}
		
		if (isset($_POST['awal'])){
			$awal = $_POST['awal'];
			if($awal =='1') {
				$thn = date('Y');
				$where .= " and PERIOD  like '%$thn%' ";
			} 
			
		}	
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "INVNO" ) {
						$searchString = $k->data;
						$where .= " and INVNO like '%".$searchString."%'"; 
					}  else if ( $k->field == "INVDATE" ) {
						$searchString = $k->data;
						$where .= " and INVDATE like  '%".$searchString."%'"; 
					}  else if ( $k->field == "GL_ACC" ) {
						$searchString = $k->data;
						$where .= " and GL_ACC like  '%".$searchString."%'"; 
					} else if ( $k->field == "PERIOD" ) {
						$searchString = $k->data;
						$where .= " and PERIOD like  '%".$searchString."%'"; 
					} else if ( $k->field == "JOURNAL_DESC" ) {
						$searchString = $k->data;
						$where .= " and JOURNAL_DESC like  '%".$searchString."%'"; 
					}  else if ( $k->field == "POSTING_KEY" ) {
						$searchString = $k->data;
						$where .= " and POSTING_KEY like  '%".$searchString."%'"; 
					}  
				}
			}
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->v_acc_ar_invall($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->v_acc_ar_invCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['INVNO']=$r->INVNO;
			$responce->rows[$i]['INVDATE']=$r->INVDATE;
			$responce->rows[$i]['GL_ACC']=$r->GL_ACC;
			$responce->rows[$i]['DEBIT']=$r->DEBIT;
			$responce->rows[$i]['CREDIT']=$r->CREDIT;
			$responce->rows[$i]['BALANCE']=$r->BALANCE;
			$responce->rows[$i]['DEBIT_USD']=$r->DEBIT_USD;
			$responce->rows[$i]['CREDIT_USD']=$r->CREDIT_USD;
			$responce->rows[$i]['BALANCE_USD']=$r->BALANCE_USD;
			$responce->rows[$i]['CAR']=$r->CAR;
			$responce->rows[$i]['NODAFT']=$r->NODAFT;
			$responce->rows[$i]['TGDAFT']=$r->TGDAFT;
			$responce->rows[$i]['typedok']=$r->typedok;
			$responce->rows[$i]['NilInv']=$r->NilInv;
			$responce->rows[$i]['NoDok']=$r->NoDok;
			$responce->rows[$i]['TgDok']=$r->TgDok;
			$responce->rows[$i]['PERIOD']=$r->PERIOD;
			
			$i++;
		}
		echo json_encode($responce);
		
	
	}
	
	
	function data_v_acc_bs_pl(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_coa'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " 1=1 ";
		$periode = " 1=1 ";
		if (isset($_POST['periode'])){
			$periode=$_POST['periode'];
			
			$where .= " and PERIOD  like '%$periode%' ";
			
		}
		
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "GL_ACC" ) {
						$searchString = $k->data;
						$where .= " and GL_ACC like '%".$searchString."%'"; 
					}  else if ( $k->field == "GL_NAME" ) {
						$searchString = $k->data;
						$where .= " and GL_NAME like  '%".$searchString."%'"; 
					}  else if ( $k->field == "PERIOD" ) {
						$searchString = $k->data;
						$where .= " and PERIOD like  '%".$searchString."%'"; 
					} else if ( $k->field == "AMOUNT" ) {
						$searchString = $k->data;
						$where .= " and AMOUNT like  '%".$searchString."%'"; 
					} else if ( $k->field == "coa_sap" ) {
						$searchString = $k->data;
						$where .= " and coa_sap like  '%".$searchString."%'"; 
					}  else if ( $k->field == "section1" ) {
						$searchString = $k->data;
						$where .= " and section1 like  '%".$searchString."%'"; 
					}    else if ( $k->field == "section2" ) {
						$searchString = $k->data;
						$where .= " and section2 like  '%".$searchString."%'"; 
					}    else if ( $k->field == "target_group1" ) {
						$searchString = $k->data;
						$where .= " and target_group1 like  '%".$searchString."%'"; 
					}      else if ( $k->field == "target_group2" ) {
						$searchString = $k->data;
						$where .= " and target_group2 like  '%".$searchString."%'"; 
					}   else if ( $k->field == "target_report" ) {
						$searchString = $k->data;
						$where .= " and target_report like  '%".$searchString."%'"; 
					}  
				}
			}
		}
		
		$jml = 0;		
		$qry = $this->db->query("
			SELECT 
			  isnull(sum([AMOUNT]),0) as [jml]
			  FROM [dbo].[v_acc_bs_pl]
			  where $where 
			  
			  
			  
			  
		");
		
		foreach ($qry->result() as $r){
			$jml = $r->jml;				
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->v_acc_bs_plall($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->v_acc_bs_plCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['id_bs_pl']=$r->id_bs_pl;
			$responce->rows[$i]['GL_ACC']=$r->GL_ACC;
			$responce->rows[$i]['GL_NAME']=$r->GL_NAME;
			$responce->rows[$i]['PERIOD']=$r->PERIOD;
			$responce->rows[$i]['AMOUNT']=$r->AMOUNT;
			$responce->rows[$i]['coa_sap']=$r->coa_sap;
			$responce->rows[$i]['section1']=$r->section1;
			$responce->rows[$i]['section2']=$r->section2;
			$responce->rows[$i]['target_group1']=$r->target_group1;;
			$responce->rows[$i]['target_group2']=$r->target_group2;
			$responce->rows[$i]['target_report']=$r->target_report;
			$responce->rows[$i]['name_account']=$r->name_account;
			
			$i++;
		}
		$responce->userdata['AMOUNT'] = $jml;
		echo json_encode($responce);
		
	
	}
	
	
	function data_v_acc_income(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_coa'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " target_report like  '%PL%' ";
		$periode=" 1=1 ";
		if (isset($_POST['periode'])){
			$periode=$_POST['periode'];
			
			$where .= " and 
			PERIOD  like '%$periode%' ";
		}
		
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "GL_ACC" ) {
						$searchString = $k->data;
						$where .= " and GL_ACC like '%".$searchString."%'"; 
					}  else if ( $k->field == "GL_NAME" ) {
						$searchString = $k->data;
						$where .= " and GL_NAME like  '%".$searchString."%'"; 
					}  else if ( $k->field == "PERIOD" ) {
						$searchString = $k->data;
						$where .= " and PERIOD like  '%".$searchString."%'"; 
					} else if ( $k->field == "AMOUNT" ) {
						$searchString = $k->data;
						$where .= " and AMOUNT like  '%".$searchString."%'"; 
					} else if ( $k->field == "coa_sap" ) {
						$searchString = $k->data;
						$where .= " and coa_sap like  '%".$searchString."%'"; 
					}  else if ( $k->field == "section1" ) {
						$searchString = $k->data;
						$where .= " and section1 like  '%".$searchString."%'"; 
					}    else if ( $k->field == "section2" ) {
						$searchString = $k->data;
						$where .= " and section2 like  '%".$searchString."%'"; 
					}    else if ( $k->field == "target_group1" ) {
						$searchString = $k->data;
						$where .= " and target_group1 like  '%".$searchString."%'"; 
					}      else if ( $k->field == "target_group2" ) {
						$searchString = $k->data;
						$where .= " and target_group2 like  '%".$searchString."%'"; 
					}   else if ( $k->field == "target_report" ) {
						$searchString = $k->data;
						$where .= " and target_report like  '%".$searchString."%'"; 
					}  
				}
			}
		}
		
		$jml = 0;
		
		$qry = $this->db->query("
			SELECT
			isnull(sum([AMOUNT]),0) as [jml]
		  FROM [dbo].[v_acc_bs_pl]
			  
			  where  $where
			  
			  
		");
		
		foreach ($qry->result() as $r){
			$jml = $r->jml;	
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->v_acc_bs_plall($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->v_acc_bs_plCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['id_bs_pl']=$r->id_bs_pl;
			$responce->rows[$i]['GL_ACC']=$r->GL_ACC;
			$responce->rows[$i]['GL_NAME']=$r->GL_NAME;
			$responce->rows[$i]['PERIOD']=$r->PERIOD;
			$responce->rows[$i]['AMOUNT']=$r->AMOUNT;
			$responce->rows[$i]['coa_sap']=$r->coa_sap;
			$responce->rows[$i]['section1']=$r->section1;
			$responce->rows[$i]['section2']=$r->section2;
			$responce->rows[$i]['target_group1']=$r->target_group1;;
			$responce->rows[$i]['target_group2']=$r->target_group2;
			$responce->rows[$i]['target_report']=$r->target_report;
			$responce->rows[$i]['name_account']=$r->name_account;
			
			$i++;
		}
		
		$responce->userdata['AMOUNT'] = $jml;
		echo json_encode($responce);
		
	
	}
	
	
	function data_v_acc_equity(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_coa'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " target_report like  '%EQUITY%' ";
		$periode=" 1=1 ";
		if (isset($_POST['periode'])){
			$periode=$_POST['periode'];
			
			$where .= " and 
			PERIOD  like '%$periode%' ";
		}
		
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "GL_ACC" ) {
						$searchString = $k->data;
						$where .= " and GL_ACC like '%".$searchString."%'"; 
					}  else if ( $k->field == "GL_NAME" ) {
						$searchString = $k->data;
						$where .= " and GL_NAME like  '%".$searchString."%'"; 
					}  else if ( $k->field == "PERIOD" ) {
						$searchString = $k->data;
						$where .= " and PERIOD like  '%".$searchString."%'"; 
					} else if ( $k->field == "AMOUNT" ) {
						$searchString = $k->data;
						$where .= " and AMOUNT like  '%".$searchString."%'"; 
					} else if ( $k->field == "coa_sap" ) {
						$searchString = $k->data;
						$where .= " and coa_sap like  '%".$searchString."%'"; 
					}  else if ( $k->field == "section1" ) {
						$searchString = $k->data;
						$where .= " and section1 like  '%".$searchString."%'"; 
					}    else if ( $k->field == "section2" ) {
						$searchString = $k->data;
						$where .= " and section2 like  '%".$searchString."%'"; 
					}    else if ( $k->field == "target_group1" ) {
						$searchString = $k->data;
						$where .= " and target_group1 like  '%".$searchString."%'"; 
					}      else if ( $k->field == "target_group2" ) {
						$searchString = $k->data;
						$where .= " and target_group2 like  '%".$searchString."%'"; 
					}   else if ( $k->field == "target_report" ) {
						$searchString = $k->data;
						$where .= " and target_report like  '%".$searchString."%'"; 
					}  
				}
			}
		}
		
		$jml = 0;
		
		$qry = $this->db->query("
			SELECT
			isnull(sum([AMOUNT]),0) as [jml]
		  FROM [dbo].[v_acc_bs_pl]
			  
			  where  $where 
			  
			  
		");
		
		foreach ($qry->result() as $r){
			$jml = $r->jml;	
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->v_acc_bs_plall($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->v_acc_bs_plCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['id_bs_pl']=$r->id_bs_pl;
			$responce->rows[$i]['GL_ACC']=$r->GL_ACC;
			$responce->rows[$i]['GL_NAME']=$r->GL_NAME;
			$responce->rows[$i]['PERIOD']=$r->PERIOD;
			$responce->rows[$i]['AMOUNT']=$r->AMOUNT;
			$responce->rows[$i]['coa_sap']=$r->coa_sap;
			$responce->rows[$i]['section1']=$r->section1;
			$responce->rows[$i]['section2']=$r->section2;
			$responce->rows[$i]['target_group1']=$r->target_group1;;
			$responce->rows[$i]['target_group2']=$r->target_group2;
			$responce->rows[$i]['target_report']=$r->target_report;
			$responce->rows[$i]['name_account']=$r->name_account;
			
			$i++;
		}
		
		$responce->userdata['AMOUNT'] = $jml;
		echo json_encode($responce);
		
	
	}
	
	
	function data_v_acc_cf(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_coa'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " 1=1 ";
		$periode=" 1=1 ";
		if (isset($_POST['periode'])){
			$periode=$_POST['periode'];
			
			$where .= " and PERIOD  like '%$periode%' ";
		}
		
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "GL_ACC" ) {
						$searchString = $k->data;
						$where .= " and GL_ACC like '%".$searchString."%'"; 
					}  else if ( $k->field == "GL_NAME" ) {
						$searchString = $k->data;
						$where .= " and GL_NAME like  '%".$searchString."%'"; 
					}  else if ( $k->field == "PERIOD" ) {
						$searchString = $k->data;
						$where .= " and PERIOD like  '%".$searchString."%'"; 
					} else if ( $k->field == "DEBIT_AMOUNT" ) {
						$searchString = $k->data;
						$where .= " and DEBIT_AMOUNT like  '%".$searchString."%'"; 
					} else if ( $k->field == "CREDIT_AMOUNT" ) {
						$searchString = $k->data;
						$where .= " and CREDIT_AMOUNT like  '%".$searchString."%'"; 
					} else if ( $k->field == "coa_sap" ) {
						$searchString = $k->data;
						$where .= " and coa_sap like  '%".$searchString."%'"; 
					}  else if ( $k->field == "section1" ) {
						$searchString = $k->data;
						$where .= " and section1 like  '%".$searchString."%'"; 
					}    else if ( $k->field == "section2" ) {
						$searchString = $k->data;
						$where .= " and section2 like  '%".$searchString."%'"; 
					}    else if ( $k->field == "target_group1" ) {
						$searchString = $k->data;
						$where .= " and target_group1 like  '%".$searchString."%'"; 
					}      else if ( $k->field == "target_group2" ) {
						$searchString = $k->data;
						$where .= " and target_group2 like  '%".$searchString."%'"; 
					}   else if ( $k->field == "target_report" ) {
						$searchString = $k->data;
						$where .= " and target_report like  '%".$searchString."%'"; 
					}     else if ( $k->field == "REASON_CODE" ) {
						$searchString = $k->data;
						$where .= " and REASON_CODE like  '%".$searchString."%'"; 
					}     else if ( $k->field == "REASON_NAME" ) {
						$searchString = $k->data;
						$where .= " and REASON_NAME like  '%".$searchString."%'"; 
					}  
				}
			}
		}
		
		
		$jml1 = 0;
		$jml2 = 0;		
		$qry = $this->db->query("
			SELECT
			isnull(sum(DEBIT_AMOUNT), 0) as DEBIT_AMOUNT,
			isnull(sum(CREDIT_AMOUNT), 0) as CREDIT_AMOUNT
		  FROM [dbo].[v_acc_cf]
			  
			  where $where
			  
			  
		");
		
		foreach ($qry->result() as $r){
			$jml1 = $r->DEBIT_AMOUNT;
			$jml2 = $r->CREDIT_AMOUNT;			
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->v_acc_cfall($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->v_acc_cfCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['id_cf']=$r->id_cf;
			$responce->rows[$i]['GL_ACC']=$r->GL_ACC;
			$responce->rows[$i]['GL_NAME']=$r->GL_NAME;
			$responce->rows[$i]['PERIOD']=$r->PERIOD;
			$responce->rows[$i]['REASON_CODE']=$r->REASON_CODE;
			$responce->rows[$i]['REASON_NAME']=$r->REASON_NAME;
			$responce->rows[$i]['DEBIT_AMOUNT']=$r->DEBIT_AMOUNT;
			$responce->rows[$i]['CREDIT_AMOUNT']=$r->CREDIT_AMOUNT;
			$responce->rows[$i]['coa_sap']=$r->coa_sap;
			$responce->rows[$i]['section1']=$r->section1;
			$responce->rows[$i]['section2']=$r->section2;
			$responce->rows[$i]['target_group1']=$r->target_group1;;
			$responce->rows[$i]['target_group2']=$r->target_group2;
			$responce->rows[$i]['target_report']=$r->target_report;
			$responce->rows[$i]['name_account']=$r->name_account;
			
			$i++;
		}
		
		$responce->userdata['DEBIT_AMOUNT'] = $jml1;
		$responce->userdata['CREDIT_AMOUNT'] = $jml2;
		
		echo json_encode($responce);
		
	
	}
	
	function get_data_sap(){
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		
		$qry = $this->db->query("
			BEGIN TRY
			
			 exec('[sp_acc_ap_ar]')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			");
		 		 
		 $rs = $qry->result_array();
		 // $r = $qry->result();		
		 // var_dump($r);die();
		 foreach ($rs as $row){
			 $status = $row['status'];
			 $msg = $row['description'];
		 }

		echo json_encode(array('sts' => $status, 'desc' => $msg));	
		
	}
	
	function get_data_sap_sts(){
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		
		$qry = $this->db->query("
			BEGIN TRY
			
			 exec('[sp_acc_ap_ar_sts]')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			");
		 		 
		 $rs = $qry->result_array();
		 // $r = $qry->result();		
		 // var_dump($r);die();
		 foreach ($rs as $row){
			 $status = $row['status'];
			 $msg = $row['description'];
		 }

		echo json_encode(array('sts' => $status, 'desc' => $msg));	
		
	}
	
	function bs_excel(){
		
		
		$periode = (isset($_GET['periode']) && !empty($_GET['periode']))?$_GET['periode']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
	   
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('BALANCE SHEET');
		$phpExcel->getActiveSheet()->SetCellValue("A1", "PT. EDS MANUFACTURING INDONESIA");
		$phpExcel->getActiveSheet()->SetCellValue("A2", "LAPORAN POSISI KEUANGAN");
		$phpExcel->getActiveSheet()->setCellValueExplicit("A3", "PERIODE " . $periode, PHPExcel_Cell_DataType::TYPE_STRING); 
		
		$phpExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true)->setSize(12);
		$phpExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true)->setSize(12);
		$phpExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true)->setSize(12);
		
		$phpExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
		$phpExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
		$phpExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
		$phpExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
		
		
		$qseg1 = $this->db->query("
			SELECT        v_acc_bs_pl.section1, v_acc_bs_pl.section2, v_acc_bs_pl.target_group1, SUM(v_acc_bs_pl.AMOUNT) AS amount, tbl_acc_table.tag
			FROM            v_acc_bs_pl INNER JOIN
									 tbl_acc_table ON v_acc_bs_pl.target_group1 = tbl_acc_table.nama
			WHERE        (v_acc_bs_pl.section1 IS NOT NULL)  
						and tbl_acc_table.kdtab = 'target_group1' 
			GROUP BY v_acc_bs_pl.section1, v_acc_bs_pl.section2, v_acc_bs_pl.target_group1, tbl_acc_table.tag
			order by cast(tag as int)
		");
		
		
		
		$i = 5;
		$k=5;
		$j=1;
		$d = 0;
		$amount1 = 0;
		$seg1cek = '';

		foreach($qseg1->result() as $rseg1){
			
			$seg1 = $rseg1->section1;
			$seg2= $rseg1->section2;
			$seg3= $rseg1->target_group1;
			
			if($seg1 != $seg1cek){
				$d = $d+1;
				$phpExcel->getActiveSheet()->setCellValueExplicit("A"."$i", $seg1 , PHPExcel_Cell_DataType::TYPE_STRING);
				
				$qseg1cnt1 = $this->db->query("
					select count(*) as cnt from (
						SELECT        v_acc_bs_pl.section1, v_acc_bs_pl.section2, SUM(v_acc_bs_pl.AMOUNT) AS amount
						FROM            v_acc_bs_pl INNER JOIN
												 tbl_acc_table ON v_acc_bs_pl.target_group1 = tbl_acc_table.nama
						WHERE        (v_acc_bs_pl.section1 IS NOT NULL) 
						and tbl_acc_table.kdtab = 'target_group1' 
						and v_acc_bs_pl.section1 = '$seg1'
						
						GROUP BY v_acc_bs_pl.section1, v_acc_bs_pl.section2

						) x
				");
				foreach ($qseg1cnt1->result()  as $rcnt1){
					$s = $rcnt1->cnt;					
				}
				
				
				
				$i = $i+($s * 3);
				
				$qseg1cnt = $this->db->query("
					select count(*) as cnt from (
						SELECT        v_acc_bs_pl.section1, v_acc_bs_pl.section2, v_acc_bs_pl.target_group1, SUM(v_acc_bs_pl.AMOUNT) AS amount, tbl_acc_table.tag
						FROM            v_acc_bs_pl INNER JOIN
												 tbl_acc_table ON v_acc_bs_pl.target_group1 = tbl_acc_table.nama
						WHERE        (v_acc_bs_pl.section1 IS NOT NULL)   
						and tbl_acc_table.kdtab = 'target_group1' 
						and v_acc_bs_pl.section1 = '$seg1'
						
						GROUP BY v_acc_bs_pl.section1, v_acc_bs_pl.section2, v_acc_bs_pl.target_group1, tbl_acc_table.tag

						) x
				");
				foreach ($qseg1cnt->result()  as $r){
					$q = $r->cnt;					
				}
				
				
				
				$j=$j+1;
				
				
				if($d > 1){
					$k = $k+1 + $q+$s + ($j+1);
					$phpExcel->getActiveSheet()->setCellValueExplicit("A"."$k", "TOTAL " . $seg1  , PHPExcel_Cell_DataType::TYPE_STRING);
					
					$q_amount = $this->db->query("
					SELECT         SUM(v_acc_bs_pl.AMOUNT) AS amount
						FROM            v_acc_bs_pl INNER JOIN
										 tbl_acc_table ON v_acc_bs_pl.target_group1 = tbl_acc_table.nama
						WHERE        (v_acc_bs_pl.section1 IS NOT NULL)  
						 and tbl_acc_table.kdtab = 'target_group1' 
						and  section1 = '$seg1'			
				
					");
					foreach($q_amount->result() as $r_amount ){
						$amount1 = $r_amount->amount;
					}
					$phpExcel->getActiveSheet()->SetCellValue("D"."$k", $amount1   , PHPExcel_Cell_DataType::TYPE_STRING);
					
					$phpExcel->getActiveSheet()->getStyle("D"."$k")->getFont()->setBold(true)->setSize(12);
					$phpExcel->getActiveSheet()->getStyle("D"."$k")->getNumberFormat()->setFormatCode('#,##0.00');
					
				} else {
					$k = $k+1 + $q+$s + ($j);
					$phpExcel->getActiveSheet()->setCellValueExplicit("A"."$k", "TOTAL " . $seg1  , PHPExcel_Cell_DataType::TYPE_STRING);
					
					$q_amount = $this->db->query("
					SELECT         SUM(v_acc_bs_pl.AMOUNT) AS amount
						FROM            v_acc_bs_pl INNER JOIN
										 tbl_acc_table ON v_acc_bs_pl.target_group1 = tbl_acc_table.nama
						WHERE        (v_acc_bs_pl.section1 IS NOT NULL)  
						 and tbl_acc_table.kdtab = 'target_group1' 
						and  section1 = '$seg1'			
				
					");
					foreach($q_amount->result() as $r_amount ){
						$amount1 = $r_amount->amount;
					}
					$phpExcel->getActiveSheet()->SetCellValue("D"."$k", $amount1   , PHPExcel_Cell_DataType::TYPE_STRING);
					$phpExcel->getActiveSheet()->getStyle("D"."$k")->getFont()->setBold(true)->setSize(12);
					$phpExcel->getActiveSheet()->getStyle("D"."$k")->getNumberFormat()->setFormatCode('#,##0.00');
				}
					
				
			}
			
			$i=$i+1;
			$seg1cek = $seg1;
			
		}
		
		
		$i = 6;
		$k=6;
		$j=0;
		
		$seg1cek = '';
		$seg2cek = '';
		foreach($qseg1->result() as $rseg1){
			
			$seg1 = $rseg1->section1;
			$seg2= $rseg1->section2;
			$seg3= $rseg1->target_group1;
			if($seg1 != $seg1cek){
				$j=$j+1;				
			}	
			if($seg2 != $seg2cek){
				if ($j > 1){
					$i = $i+$j;
					$phpExcel->getActiveSheet()->setCellValueExplicit("B"."$i", $seg2, PHPExcel_Cell_DataType::TYPE_STRING);
				} else {
					
					$phpExcel->getActiveSheet()->setCellValueExplicit("B"."$i", $seg2, PHPExcel_Cell_DataType::TYPE_STRING);
					$i = $i+2;
				}
				
				$qseg1cnt = $this->db->query("
					select count(*) as cnt from (
						SELECT        v_acc_bs_pl.section1, v_acc_bs_pl.section2, v_acc_bs_pl.target_group1, SUM(v_acc_bs_pl.AMOUNT) AS amount, tbl_acc_table.tag
						FROM            v_acc_bs_pl INNER JOIN
												 tbl_acc_table ON v_acc_bs_pl.target_group1 = tbl_acc_table.nama
						WHERE        (v_acc_bs_pl.section1 IS NOT NULL)   
						 and tbl_acc_table.kdtab = 'target_group1' 
						and v_acc_bs_pl.section1 = '$seg1' and  v_acc_bs_pl.section2 = '$seg2'
						
						GROUP BY v_acc_bs_pl.section1, v_acc_bs_pl.section2, v_acc_bs_pl.target_group1, tbl_acc_table.tag

						) x
				");
				
				foreach ($qseg1cnt->result()  as $r){
					$q = $r->cnt;					
				}
				if ($j > 1){
					$q = $q + $k+1+$j;
					$phpExcel->getActiveSheet()->setCellValueExplicit("B"."$q", "TOTAL " . $seg2, PHPExcel_Cell_DataType::TYPE_STRING);
					
					$q_amount = $this->db->query("
					SELECT         SUM(v_acc_bs_pl.AMOUNT) AS amount
						FROM            v_acc_bs_pl INNER JOIN
										 tbl_acc_table ON v_acc_bs_pl.target_group1 = tbl_acc_table.nama
						WHERE        (v_acc_bs_pl.section1 IS NOT NULL) 
						 and tbl_acc_table.kdtab = 'target_group1' 
						and  section1 = '$seg1' and section2 = '$seg2'			
				
					");
					foreach($q_amount->result() as $r_amount ){
						$amount1 = $r_amount->amount;
					}
					$phpExcel->getActiveSheet()->SetCellValue("D"."$q", $amount1   , PHPExcel_Cell_DataType::TYPE_STRING);
					$phpExcel->getActiveSheet()->getStyle("D"."$q")->getFont()->setBold(true)->setSize(12);
					$phpExcel->getActiveSheet()->getStyle("D"."$q")->getNumberFormat()->setFormatCode('#,##0.00');
					
					
				} else {	
					$q = $q + $k+1;
					$phpExcel->getActiveSheet()->setCellValueExplicit("B"."$q", "TOTAL " . $seg2, PHPExcel_Cell_DataType::TYPE_STRING);
					
					$q_amount = $this->db->query("
					SELECT         SUM(v_acc_bs_pl.AMOUNT) AS amount
						FROM            v_acc_bs_pl INNER JOIN
										 tbl_acc_table ON v_acc_bs_pl.target_group1 = tbl_acc_table.nama
						WHERE        (v_acc_bs_pl.section1 IS NOT NULL) 
						 and tbl_acc_table.kdtab = 'target_group1' 
						and  section1 = '$seg1' and section2 = '$seg2'			
				
					");
					foreach($q_amount->result() as $r_amount ){
						$amount1 = $r_amount->amount;
					}
					$phpExcel->getActiveSheet()->SetCellValue("D"."$q", $amount1   , PHPExcel_Cell_DataType::TYPE_STRING);
					$phpExcel->getActiveSheet()->getStyle("D"."$q")->getFont()->setBold(true)->setSize(12);
					$phpExcel->getActiveSheet()->getStyle("D"."$q")->getNumberFormat()->setFormatCode('#,##0.00');
					
				}
			}
			
			
			
			
			$i=$i+1;
			$seg1cek = $seg1;
			$seg2cek = $seg2;
			$k=$i;
			
		}
		

		$i = 5;
		$k=7;
		$j=0;
		$d = 0;
		$seg1cek = '';
		$seg2cek = '';
		foreach($qseg1->result() as $rseg1){
			
			$seg1 = $rseg1->section1;
			$seg2= $rseg1->section2;
			$seg3= $rseg1->target_group1;
			$amount= $rseg1->amount;
			if($seg1 != $seg1cek){
				$d=$d+1;				
			}	
				
			//if ($d == 1){
			//	$j=$j+2;
			//}
			if($seg2 != $seg2cek){
				$i = $i+2;
				
			}	
			
			if ($d == 2){
				$i = $i+2;
				$d = 0;
			}
			
			$phpExcel->getActiveSheet()->setCellValueExplicit("C"."$i", $seg3 , PHPExcel_Cell_DataType::TYPE_STRING);		
			$phpExcel->getActiveSheet()->SetCellValue("D"."$i", $amount   , PHPExcel_Cell_DataType::TYPE_STRING);		
			$phpExcel->getActiveSheet()->getStyle("D"."$i")->getNumberFormat()->setFormatCode('#,##0.00');		
			
			$i=$i+1;
			$seg1cek = $seg1;
			$seg2cek = $seg2;
			
		}
		
		

		$filename = 'balance_sheet_pemi.xls';
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
		
	}
	
	function bs_excel_data(){
	
		$periode = (isset($_GET['periode']) && !empty($_GET['periode']))?$_GET['periode']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
	   
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('BALANCE SHEET');
		$phpExcel->getActiveSheet()->SetCellValue("A1", "PT. EDS MANUFACTURING INDONESIA");
		$phpExcel->getActiveSheet()->SetCellValue("A2", "LAPORAN POSISI KEUANGAN");
		$phpExcel->getActiveSheet()->setCellValueExplicit("A3", "PERIODE " . $periode, PHPExcel_Cell_DataType::TYPE_STRING); 
		
		$phpExcel->getActiveSheet()->SetCellValue("A4", "PERIOD");
		$phpExcel->getActiveSheet()->SetCellValue("B4", "GROUP");
		$phpExcel->getActiveSheet()->SetCellValue("C4", "SUB GROUP");
		$phpExcel->getActiveSheet()->SetCellValue("D4", "SUB2 GROUP");
		$phpExcel->getActiveSheet()->SetCellValue("E4", "GL CODE");
		$phpExcel->getActiveSheet()->SetCellValue("F4", "GL NAME");
		$phpExcel->getActiveSheet()->SetCellValue("G4", "AMOUNT");
		$q = $this->db->query("
			SELECT 
				[PERIOD]
				,	[section1]
					,[section2]
					 ,[coa_sap]
					  ,[GL_NAME]
					  
					  ,[AMOUNT]
					 ,target_group1
					  
					  
				  FROM [dbo].[v_acc_bs_pl]
				  where PERIOD like '%$periode%'
				  order by PERIOD, GL_ACC
		
		");
		
		$i = 5;
		$k=5;
		$j=1;
		foreach($q->result() as $r){
			$phpExcel->getActiveSheet()->setCellValueExplicit("A" . "$i", $r->PERIOD , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("B" . "$i", $r->section1 );
			$phpExcel->getActiveSheet()->SetCellValue("C" . "$i", $r->section2 );
			$phpExcel->getActiveSheet()->SetCellValue("D" . "$i", $r->target_group1 );
			$phpExcel->getActiveSheet()->setCellValueExplicit("E" . "$i", $r->coa_sap , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("F" . "$i", $r->GL_NAME );
			$phpExcel->getActiveSheet()->SetCellValue("G" . "$i", $r->AMOUNT );
			$phpExcel->getActiveSheet()->getStyle("G"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$i=$i+1;			
		}
		
		
		$filename = 'balance_sheet_pemi.xls';
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
		
		
	}	
	
	function income_excel(){
		$periode = (isset($_GET['periode']) && !empty($_GET['periode']))?$_GET['periode']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$objTpl = PHPExcel_IOFactory::load(APPPATH."libraries/PHPExcel/templates/report/income.xlsx");	
		$objTpl->setActiveSheetIndex(0);  //set first sheet as active
		
		$objTpl->getActiveSheet()->setCellValue('E11', '100');
		
		
		$datenow = date('Ymd');
		$filename =  "PROFIT_" . $datenow;
		
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($objTpl, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
		
		
		
	}
	
	function income_excel_data(){
	
		$periode = (isset($_GET['periode']) && !empty($_GET['periode']))?$_GET['periode']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
	   
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('INCOME');
		$phpExcel->getActiveSheet()->SetCellValue("A1", "PT. EDS MANUFACTURING INDONESIA");
		$phpExcel->getActiveSheet()->SetCellValue("A2", "LAPORAN POSISI KEUANGAN");
		$phpExcel->getActiveSheet()->setCellValueExplicit("A3", "PERIODE " . $periode, PHPExcel_Cell_DataType::TYPE_STRING); 
		
		$phpExcel->getActiveSheet()->SetCellValue("A4", "PERIOD");
		$phpExcel->getActiveSheet()->SetCellValue("B4", "GROUP");
		$phpExcel->getActiveSheet()->SetCellValue("C4", "SUB GROUP");
		$phpExcel->getActiveSheet()->SetCellValue("D4", "SUB GROUP2");
		$phpExcel->getActiveSheet()->SetCellValue("E4", "GL CODE");
		$phpExcel->getActiveSheet()->SetCellValue("F4", "GL NAME");
		$phpExcel->getActiveSheet()->SetCellValue("G4", "AMOUNT");
		$q = $this->db->query("
			SELECT 
				[PERIOD]
				,	[section1]
					,[section2]
					 ,[coa_sap]
					  ,[GL_NAME]
					  
					  ,[AMOUNT]
					 ,target_group1
					  
					  
				  FROM [dbo].[v_acc_bs_pl]
				  where PERIOD like '%$periode%' and  target_report like  '%PL%' 
				  order by PERIOD, GL_ACC
		
		");
		
		$i = 5;
		$k=5;
		$j=1;
		foreach($q->result() as $r){
			$phpExcel->getActiveSheet()->setCellValueExplicit("A" . "$i", $r->PERIOD , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("B" . "$i", $r->section1 );
			$phpExcel->getActiveSheet()->SetCellValue("C" . "$i", $r->section2 );
			$phpExcel->getActiveSheet()->SetCellValue("D" . "$i", $r->target_group1 );
			$phpExcel->getActiveSheet()->setCellValueExplicit("E" . "$i", $r->coa_sap , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("F" . "$i", $r->GL_NAME );
			$phpExcel->getActiveSheet()->SetCellValue("G" . "$i", $r->AMOUNT );
			$phpExcel->getActiveSheet()->getStyle("G"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$i=$i+1;			
		}
		
		
		$filename = 'income_pemi.xls';
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
		
		
	}	
	
	function equity_excel(){
		$periode = (isset($_GET['periode']) && !empty($_GET['periode']))?$_GET['periode']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$objTpl = PHPExcel_IOFactory::load(APPPATH."libraries/PHPExcel/templates/report/equity.xlsx");	
		$objTpl->setActiveSheetIndex(0);  //set first sheet as active
		
		$objTpl->getActiveSheet()->setCellValue('E11', '100');
		
		
		$datenow = date('Ymd');
		$filename =  "PROFIT_" . $datenow;
		
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($objTpl, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
		
		
		
	}
	
	function equity_excel_data(){
	
		$periode = (isset($_GET['periode']) && !empty($_GET['periode']))?$_GET['periode']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
	   
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('EQUITY');
		$phpExcel->getActiveSheet()->SetCellValue("A1", "PT. EDS MANUFACTURING INDONESIA");
		$phpExcel->getActiveSheet()->SetCellValue("A2", "LAPORAN POSISI KEUANGAN");
		$phpExcel->getActiveSheet()->setCellValueExplicit("A3", "PERIODE " . $periode, PHPExcel_Cell_DataType::TYPE_STRING); 
		
		$phpExcel->getActiveSheet()->SetCellValue("A4", "PERIOD");
		$phpExcel->getActiveSheet()->SetCellValue("B4", "GROUP");
		$phpExcel->getActiveSheet()->SetCellValue("C4", "SUB GROUP");
		$phpExcel->getActiveSheet()->SetCellValue("D4", "SUB2 GROUP");
		$phpExcel->getActiveSheet()->SetCellValue("E4", "GL CODE");
		$phpExcel->getActiveSheet()->SetCellValue("F4", "GL NAME");
		$phpExcel->getActiveSheet()->SetCellValue("G4", "AMOUNT");
		$q = $this->db->query("
			SELECT 
				[PERIOD]
				,	[section1]
					,[section2]
					 ,[coa_sap]
					  ,[GL_NAME]
					  
					  ,[AMOUNT]
					 ,target_group1
					  
					  
				  FROM [dbo].[v_acc_bs_pl]
				  where PERIOD like '%$periode%' and  target_report like  '%EQUITY%' 
				  order by PERIOD, GL_ACC
		
		");
		
		$i = 5;
		$k=5;
		$j=1;
		foreach($q->result() as $r){
			$phpExcel->getActiveSheet()->setCellValueExplicit("A" . "$i", $r->PERIOD , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("B" . "$i", $r->section1 );
			$phpExcel->getActiveSheet()->SetCellValue("C" . "$i", $r->section2 );
			$phpExcel->getActiveSheet()->SetCellValue("D" . "$i", $r->target_group1 );
			$phpExcel->getActiveSheet()->setCellValueExplicit("E" . "$i", $r->coa_sap , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("F" . "$i", $r->GL_NAME );
			$phpExcel->getActiveSheet()->SetCellValue("G" . "$i", $r->AMOUNT );
			$phpExcel->getActiveSheet()->getStyle("G"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$i=$i+1;			
		}
		
		
		$filename = 'equity_pemi.xls';
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
		
		
	}
	
	function cash_flow_excel(){
		$periode = (isset($_GET['periode']) && !empty($_GET['periode']))?$_GET['periode']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$objTpl = PHPExcel_IOFactory::load(APPPATH."libraries/PHPExcel/templates/report/cashflow.xlsx");	
		$objTpl->setActiveSheetIndex(0);  //set first sheet as active
		
		$objTpl->getActiveSheet()->setCellValue('E11', '100');
		
		
		$datenow = date('Ymd');
		$filename =  "PROFIT_" . $datenow;
		
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($objTpl, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
		
		
		
	}
	
	function cash_flow_excel_data(){
		
		
		$periode = (isset($_GET['periode']) && !empty($_GET['periode']))?$_GET['periode']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
	   
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('CASH FLOW');
		$phpExcel->getActiveSheet()->SetCellValue("A1", "PT. EDS MANUFACTURING INDONESIA");
		$phpExcel->getActiveSheet()->SetCellValue("A2", "LAPORAN CASH FLOW");
		$phpExcel->getActiveSheet()->setCellValueExplicit("A3", "PERIODE " . $periode, PHPExcel_Cell_DataType::TYPE_STRING); 
		
		$phpExcel->getActiveSheet()->SetCellValue("A4", "PERIOD");
		$phpExcel->getActiveSheet()->SetCellValue("B4", "GROUP");
		$phpExcel->getActiveSheet()->SetCellValue("C4", "SUB GROUP");
		$phpExcel->getActiveSheet()->SetCellValue("D4", "SUB GROUP2");
		$phpExcel->getActiveSheet()->SetCellValue("E4", "GL CODE");
		$phpExcel->getActiveSheet()->SetCellValue("F4", "GL NAME");
		$phpExcel->getActiveSheet()->SetCellValue("G4", "DEBIT AMOUNT");
		$phpExcel->getActiveSheet()->SetCellValue("H4", "CREDIT AMOUNT");
		
		$q = $this->db->query("
			SELECT 
				[PERIOD]
				,	[section1]
					,[section2]
					 ,[coa_sap]
					  ,[GL_NAME]
					  
					  ,[DEBIT_AMOUNT]
					  ,[CREDIT_AMOUNT]
					 ,target_group1
					  
					  
				  FROM [dbo].[v_acc_cf]
				  where PERIOD like '%$periode%'
				  order by PERIOD, GL_ACC
		
		");
		
		$i = 5;
		$k=5;
		$j=1;
		foreach($q->result() as $r){
			$phpExcel->getActiveSheet()->setCellValueExplicit("A" . "$i", $r->PERIOD , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("B" . "$i", $r->section1 );
			$phpExcel->getActiveSheet()->SetCellValue("C" . "$i", $r->section2 );
			$phpExcel->getActiveSheet()->SetCellValue("D" . "$i", $r->target_group1 );
			$phpExcel->getActiveSheet()->setCellValueExplicit("E" . "$i", $r->coa_sap , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("F" . "$i", $r->GL_NAME );
			$phpExcel->getActiveSheet()->SetCellValue("G" . "$i", $r->DEBIT_AMOUNT );
			$phpExcel->getActiveSheet()->SetCellValue("H" . "$i", $r->CREDIT_AMOUNT );
			$phpExcel->getActiveSheet()->getStyle("G"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$phpExcel->getActiveSheet()->getStyle("H"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$i=$i+1;			
		}
		
		
		$filename = 'cash_flow_pemi.xls';
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
		
		
	}
	
	function kas_in_bc_out_excel(){
		$periode = (isset($_GET['periode']) && !empty($_GET['periode']))?$_GET['periode']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
	   
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('ACCOUNT PAYABLE TRANSACTION');
		$phpExcel->getActiveSheet()->SetCellValue("A1", "PT. EDS MANUFACTURING INDONESIA");
		$phpExcel->getActiveSheet()->SetCellValue("A2", "ACCOUNT PAYABLE TRANSACTION");
		$phpExcel->getActiveSheet()->setCellValueExplicit("A3", "PERIODE " . $periode, PHPExcel_Cell_DataType::TYPE_STRING); 
		
		$phpExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true)->setSize(12);
		$phpExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true)->setSize(12);
		$phpExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true)->setSize(12);
		
		$phpExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
		$phpExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
		$phpExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
		$phpExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
		
		$q = $this->db->query("
				SELECT  [INVNO]
				  ,[INVDATE]
				  ,[GL_ACC]
				  ,[DEBIT]
				  ,[CREDIT]
				  ,[BALANCE]
				  ,[CURR]
				  ,[DEBIT_USD]
				  ,[CREDIT_USD]
				  ,[BALANCE_USD]
				  ,[CAR]
				  ,NODAFT
				  ,convert(varchar(10),[TGDAFT], 126) as TGDAFT
				  ,[typedok]
				  ,[NilInv]
				  ,[FREIGHT]
				  ,[NoDok]
				  ,convert(varchar(10),[TgDok], 126) as TgDok
				  ,[PERIOD]
				  ,tbl_acc_coa.name_account, tbl_acc_coa.section1
			 FROM            v_acc_ar_inv LEFT OUTER JOIN
                         tbl_acc_coa ON v_acc_ar_inv.GL_ACC = tbl_acc_coa.coa_sap
			  where period like '%$periode%'
		");
		
		$INVNO='';
		$INVDATE='';
		$GL_ACC='';
		$DEBIT='';
		$CREDIT='';
		$BALANCE='';
		$CURR='';
		$DEBIT_USD='';
		$CREDIT_USD='';
		$BALANCE_USD='';
		$typedok='';
		$DokNo='';
		$DokTg='';
		$CAR='';
		$BC23No='';
		$BC23Tg='';
		$PERIOD='';
		$name_account='';
		$section1='';
		
		$phpExcel->getActiveSheet()->SetCellValue("A4", "NO INVOICE");
		$phpExcel->getActiveSheet()->SetCellValue("B4", "TGL INVOICE");
		$phpExcel->getActiveSheet()->SetCellValue("C4", "GL ACC");
		$phpExcel->getActiveSheet()->SetCellValue("D4", "GL NAME");
		$phpExcel->getActiveSheet()->SetCellValue("E4", "DEBIT");
		$phpExcel->getActiveSheet()->SetCellValue("F4", "CREDIT");
		$phpExcel->getActiveSheet()->SetCellValue("G4", "BALANCE");
		$phpExcel->getActiveSheet()->SetCellValue("H4", "CURR");
		$phpExcel->getActiveSheet()->SetCellValue("I4", "DEBIT_USD");
		$phpExcel->getActiveSheet()->SetCellValue("J4", "CREDIT_USD");
		$phpExcel->getActiveSheet()->SetCellValue("K4", "BALANCE_USD");
		$phpExcel->getActiveSheet()->SetCellValue("L4", "CAR");
		$phpExcel->getActiveSheet()->SetCellValue("M4", "BC NO");
		$phpExcel->getActiveSheet()->SetCellValue("N4", "BC TG");
		$phpExcel->getActiveSheet()->SetCellValue("O4", "TYPE DOK");
		
		$i = 5;
		$k=5;
		
		foreach($q->result() as $r){
			$phpExcel->getActiveSheet()->setCellValueExplicit("A" . "$i", $r->INVNO , PHPExcel_Cell_DataType::TYPE_STRING); 	
			$phpExcel->getActiveSheet()->setCellValueExplicit("B" . "$i", $r->INVDATE , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("C" . "$i", $r->GL_ACC , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("D" . "$i", $r->name_account , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("E" . "$i", $r->DEBIT); 
			$phpExcel->getActiveSheet()->SetCellValue("F" . "$i", $r->CREDIT); 
			$phpExcel->getActiveSheet()->SetCellValue("G" . "$i", $r->BALANCE); 
			$phpExcel->getActiveSheet()->SetCellValue("H" . "$i", $r->CURR); 
			$phpExcel->getActiveSheet()->SetCellValue("I" . "$i", $r->DEBIT_USD); 
			$phpExcel->getActiveSheet()->SetCellValue("J" . "$i", $r->CREDIT_USD); 
			$phpExcel->getActiveSheet()->SetCellValue("K" . "$i", $r->BALANCE_USD); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("L" . "$i", $r->CAR , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("M" . "$i", $r->NODAFT , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("N" . "$i", $r->TGDAFT , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("O" . "$i", $r->typedok , PHPExcel_Cell_DataType::TYPE_STRING); 
			
			$i  = $i + 1;
		}
		
		
		$filename = 'kas_in_bc_out_excel.xls';
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
	}
	
	function kas_out_bc_in_excel(){
		$periode = (isset($_GET['periode']) && !empty($_GET['periode']))?$_GET['periode']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
	   
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('ACCOUNT PAYABLE TRANSACTION');
		$phpExcel->getActiveSheet()->SetCellValue("A1", "PT. EDS MANUFACTURING INDONESIA");
		$phpExcel->getActiveSheet()->SetCellValue("A2", "ACCOUNT PAYABLE TRANSACTION");
		$phpExcel->getActiveSheet()->setCellValueExplicit("A3", "PERIODE " . $periode, PHPExcel_Cell_DataType::TYPE_STRING); 
		
		$phpExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true)->setSize(12);
		$phpExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true)->setSize(12);
		$phpExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true)->setSize(12);
		
		$phpExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
		$phpExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
		$phpExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
		$phpExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
		
		$q = $this->db->query("
				SELECT  [INVNO]
				  ,[INVDATE]
				  ,[GL_ACC]
				  ,[DEBIT]
				  ,[CREDIT]
				  ,[BALANCE]
				  ,[CURR]
				  ,[DEBIT_USD]
				  ,[CREDIT_USD]
				  ,[BALANCE_USD]
				  ,[typedok]
				  ,[DokNo]
				  ,[DokTg]
				  ,[CAR]
				  ,[BC23No]
				  ,convert(varchar(10),[BC23Tg], 126) as BC23Tg
				  ,[PERIOD]
				  ,tbl_acc_coa.name_account, tbl_acc_coa.section1
			 FROM            v_acc_ap_inv LEFT OUTER JOIN
                         tbl_acc_coa ON v_acc_ap_inv.GL_ACC = tbl_acc_coa.coa_sap
			  where period like '%$periode%'
		");
		
		$INVNO='';
		$INVDATE='';
		$GL_ACC='';
		$DEBIT='';
		$CREDIT='';
		$BALANCE='';
		$CURR='';
		$DEBIT_USD='';
		$CREDIT_USD='';
		$BALANCE_USD='';
		$typedok='';
		$DokNo='';
		$DokTg='';
		$CAR='';
		$BC23No='';
		$BC23Tg='';
		$PERIOD='';
		$name_account='';
		$section1='';
		
		$phpExcel->getActiveSheet()->SetCellValue("A4", "NO INVOICE");
		$phpExcel->getActiveSheet()->SetCellValue("B4", "TGL INVOICE");
		$phpExcel->getActiveSheet()->SetCellValue("C4", "GL ACC");
		$phpExcel->getActiveSheet()->SetCellValue("D4", "GL NAME");
		$phpExcel->getActiveSheet()->SetCellValue("E4", "DEBIT");
		$phpExcel->getActiveSheet()->SetCellValue("F4", "CREDIT");
		$phpExcel->getActiveSheet()->SetCellValue("G4", "BALANCE");
		$phpExcel->getActiveSheet()->SetCellValue("H4", "CURR");
		$phpExcel->getActiveSheet()->SetCellValue("I4", "DEBIT_USD");
		$phpExcel->getActiveSheet()->SetCellValue("J4", "CREDIT_USD");
		$phpExcel->getActiveSheet()->SetCellValue("K4", "BALANCE_USD");
		$phpExcel->getActiveSheet()->SetCellValue("L4", "CAR");
		$phpExcel->getActiveSheet()->SetCellValue("M4", "BC NO");
		$phpExcel->getActiveSheet()->SetCellValue("N4", "BC TG");
		$phpExcel->getActiveSheet()->SetCellValue("O4", "TYPE DOK");
		
		$i = 5;
		$k=5;
		
		foreach($q->result() as $r){
			$phpExcel->getActiveSheet()->setCellValueExplicit("A" . "$i", $r->INVNO , PHPExcel_Cell_DataType::TYPE_STRING); 	
			$phpExcel->getActiveSheet()->setCellValueExplicit("B" . "$i", $r->INVDATE , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("C" . "$i", $r->GL_ACC , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("D" . "$i", $r->name_account , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("E" . "$i", $r->DEBIT); 
			$phpExcel->getActiveSheet()->SetCellValue("F" . "$i", $r->CREDIT); 
			$phpExcel->getActiveSheet()->SetCellValue("G" . "$i", $r->BALANCE); 
			$phpExcel->getActiveSheet()->SetCellValue("H" . "$i", $r->CURR); 
			$phpExcel->getActiveSheet()->SetCellValue("I" . "$i", $r->DEBIT_USD); 
			$phpExcel->getActiveSheet()->SetCellValue("J" . "$i", $r->CREDIT_USD); 
			$phpExcel->getActiveSheet()->SetCellValue("K" . "$i", $r->BALANCE_USD); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("L" . "$i", $r->CAR , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("M" . "$i", $r->BC23No , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("N" . "$i", $r->BC23Tg , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("O" . "$i", $r->typedok , PHPExcel_Cell_DataType::TYPE_STRING); 
			
			$i  = $i + 1;
		}
		
		
		
		$filename = 'kas_out_bc_in_excel.xls';
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
	}
	
	public function upload_file()
	{
		
	   $status = "";
	   $msg = "";
	   $file_element_name = 'img_invoice_upload';
	   #print_r($file_element_name);die();
	    //$username = $this->session->userdata('DX_user_id');
	   
	   $tipe = (isset($_POST['tipe']) && !empty($_POST['tipe']))?$_POST['tipe']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
	   $no_inv = (isset($_POST['no_inv']) && !empty($_POST['no_inv']))?$_POST['no_inv']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
	   $tgl_inv = (isset($_POST['tgl_inv']) && !empty($_POST['tgl_inv']))?$_POST['tgl_inv']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');	   
	   $username = $this->session->userdata('DX_username');
	   //$tipe = 'VGI';
	   if ($status != "error")
	   {
		  $apppath = $this->config->item('APP_upload_path');
		  $config['upload_path'] = $apppath.'image_upload/';
		  $config['allowed_types'] = 'gif|jpg|png';
		  $config['max_size']  = 1024 * 8;
		  $config['encrypt_name'] = false;
		  $config['overwrite']=true;
		  $config['remove_spaces']=true;
		  $this->load->library('upload', $config);
		  #print_r($_FILES);
		 if (!$this->upload->do_upload($file_element_name))
		  {
			 $status = 'error';
			 $msg = $this->upload->display_errors('', '');
			 #echo "test";
		  }
		  else
		  {
			 $data = $this->upload->data();
			 
			 if($data)
			 {
				//$file_id = $this->mdl_production->insert_file($data['file_name'], $username, $tipe);
				$file_id = $this->mdl_accounting_report->insert_file($no_inv,$tgl_inv ,0,$username, $tipe, base_url() . 'loaderfiles/image_upload/' . $data['file_name'],$data['full_path']);
				//insert_file($no_inv,$tgl_inv ,$amount_inv ,$username, $tipe,$folder)
				$status = "success";
				$msg = "File successfully uploaded";
			 }
			 else
			 {
				unlink($data['full_path']);
				$status = "error";
				$msg = "Something went wrong when saving the file, please try again.";
			 }
		  }
		  @unlink($_FILES[$file_element_name]);
	   }
	   echo json_encode(array('status' => $status, 'msg' => $msg));
	}
	
	function delete_upload_inv(){
		$folder = (isset($_POST['folder']) && !empty($_POST['folder']))?$_POST['folder']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$tipe = (isset($_POST['tipe']) && !empty($_POST['tipe']))?$_POST['tipe']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$id_upload_inv = (isset($_POST['id_upload_inv']) && !empty($_POST['id_upload_inv']))?$_POST['id_upload_inv']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$filename = (isset($_POST['filename']) && !empty($_POST['filename']))?$_POST['filename']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		
		 //$username = $this->session->userdata('DX_user_id');
	   
		@unlink($filename);
		$this->db->query("
			delete from tbl_acc_upload_gbr_invoice where id_upload_inv = 	$id_upload_inv		
		");
		echo json_encode(array('sts' => '00', 'desc' => 'file deleted'));
		die();
			
		
	//	if (!file_exists($folder)) {
	//		
	//	}
		
		
		
		
		
	
	}
	
	function data_upload_inv(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_upload_inv'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " 1=1 ";
		
		$tipe = isset($_POST['tipe'])?$_POST['tipe']:'tipe';
		$where .= " and tipe='$tipe'  ";
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "no_inv" ) {
						$searchString = $k->data;
						$where .= " and no_inv like '%".$searchString."%'"; 
					}  else if ( $k->field == "tgl_inv" ) {
						$searchString = $k->data;
						$where .= " and tgl_inv like  '%".$searchString."%'"; 
					}  else if ( $k->field == "folder" ) {
						$searchString = $k->data;
						$where .= " and folder like  '%".$searchString."%'"; 
					}  else if ( $k->field == "tipe" ) {
						$searchString = $k->data;
						$where .= " and tipe like  '%".$searchString."%'"; 
					} 
				}
			}
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->upload_gbr_invoiceall($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->upload_gbr_invoiceCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['id_upload_inv']=$r->id_upload_inv;
			$responce->rows[$i]['no_inv']=$r->no_inv;
			$responce->rows[$i]['tgl_inv']=$r->tgl_inv;
			$responce->rows[$i]['amount_inv']=$r->amount_inv;
			$responce->rows[$i]['folder']=  '<a href="' . $r->folder . '" target="_blank">' . $r->no_inv . '</a>';
			$responce->rows[$i]['tipe']=$r->tipe;
			$responce->rows[$i]['filename']=$r->filename;
			$responce->rows[$i]['create_date']=$r->create_date;
			$responce->rows[$i]['create_by']=$r->create_by;
			$responce->rows[$i]['update_date']=$r->update_date;
			$responce->rows[$i]['update_by']=$r->update_by;
			
			$i++;
		}
		echo json_encode($responce);
		
	
	}
	
	function data_v_acc_inv_in(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_upload_inv'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " 1=1 ";
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "CAR" ) {
						$searchString = $k->data;
						$where .= " and CAR like '%".$searchString."%'"; 
					}  else if ( $k->field == "BC23No" ) {
						$searchString = $k->data;
						$where .= " and BC23No like  '%".$searchString."%'"; 
					}  else if ( $k->field == "BC23Tg" ) {
						$searchString = $k->data;
						$where .= " and BC23Tg like  '%".$searchString."%'"; 
					}  else if ( $k->field == "typedok" ) {
						$searchString = $k->data;
						$where .= " and typedok like  '%".$searchString."%'"; 
					}  else if ( $k->field == "statusdok" ) {
						$searchString = $k->data;
						$where .= " and statusdok like  '%".$searchString."%'"; 
					}   else if ( $k->field == "bc_in_from_in" ) {
						$searchString = $k->data;
						$where .= " and bc_in_from_in like  '%".$searchString."%'"; 
					}    else if ( $k->field == "PasokNama" ) {
						$searchString = $k->data;
						$where .= " and PasokNama like  '%".$searchString."%'"; 
					}     else if ( $k->field == "nobl" ) {
						$searchString = $k->data;
						$where .= " and nobl like  '%".$searchString."%'"; 
					}   else if ( $k->field == "KdVal" ) {
						$searchString = $k->data;
						$where .= " and KdVal like  '%".$searchString."%'"; 
					} 
				}
			}
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->v_acc_inv_inall($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->v_acc_inv_inCountAll($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['CAR']=$r->CAR;
			$responce->rows[$i]['BC23No']=$r->BC23No;
			$responce->rows[$i]['BC23Tg']=$r->BC23Tg;
			$responce->rows[$i]['Fob']=$r->Fob;
			$responce->rows[$i]['Cif']=$r->Cif;
			$responce->rows[$i]['NilInv']=$r->NilInv;
			$responce->rows[$i]['DokNo']=$r->DokNo;
			$responce->rows[$i]['DokTg']=$r->DokTg;
			$responce->rows[$i]['typedok']=$r->typedok;
			$responce->rows[$i]['statusdok']=$r->statusdok;
			$responce->rows[$i]['bc_in_from_in']=$r->bc_in_from_in;
			$responce->rows[$i]['nobl']=$r->nobl;
			$responce->rows[$i]['tglbl']=$r->tglbl;
			$responce->rows[$i]['PasokNama']=$r->PasokNama;
			$responce->rows[$i]['KdVal']=$r->KdVal;
			$i++;
		}
		echo json_encode($responce);
		
	
	}
	
	function csv_efaktur(){
		//$tglawal = (isset($_GET['tglawal']) && !empty($_GET['tglawal']))?$_GET['tglawal']:die('{"sts":"ERROR","desc":" idhdrbc30 Param Tidak Ditemukan"}');
		//$tglakhir = (isset($_GET['tglakhir']) && !empty($_GET['tglakhir']))?$_GET['tglakhir']:die('{"sts":"ERROR","desc":" idhdrbc30 Param Tidak Ditemukan"}');
		
		
		$data1 = array();
		$data1[] = "FK,	KD_JENIS_TRANSAKSI,	FG_PENGGANTI,	NOMOR_FAKTUR,	MASA_PAJAK,	TAHUN_PAJAK,	TANGGAL_FAKTUR,	NPWP,	NAMA,	ALAMAT_LENGKAP,	JUMLAH_DPP,	JUMLAH_PPN,	JUMLAH_PPNBM,	ID_KETERANGAN_TAMBAHAN,	FG_UANG_MUKA,	FG_UANG_MUKA_DPP,	UANG_MUKA_PPN,	UANG_MUKA_PPNBM,	REFERENSI,";
		$data1[] .= "FK,	KD_JENIS_TRANSAKSI,	FG_PENGGANTI,	NOMOR_FAKTUR,	MASA_PAJAK,	TAHUN_PAJAK,	TANGGAL_FAKTUR,	NPWP,	NAMA,	ALAMAT_LENGKAP,	JUMLAH_DPP,	JUMLAH_PPN,	JUMLAH_PPNBM,	ID_KETERANGAN_TAMBAHAN,	FG_UANG_MUKA,	FG_UANG_MUKA_DPP,	UANG_MUKA_PPN,	UANG_MUKA_PPNBM,	REFERENSI,";
		
		$result = $data1;		
		header('Content-Type: application/excel');
		header('Content-Disposition: attachment; filename="sample.csv"');
		
		$fp = fopen('php://output', 'w');
		foreach ( $result as $line ) {
			$val = explode(",", $line);
			fputcsv($fp, $val);
		}
		fclose($fp);
		
	}
	
	
	//-------------------pajak
	
	function data_validasi_tax_ar(){
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'id_b123'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " 1=1 ";
		$tipe_pajak = isset($_POST['tipe_pajak'])?$_POST['tipe_pajak']:'0';
		if ($tipe_pajak != '0'){
			$where = " tipe_pajak = '$tipe_pajak' "	;		
		}
		
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "pemberi_jkp" ) {
						$searchString = $k->data;
						$where .= " and pemberi_jkp like '%".$searchString."%'"; 
					}  else if ( $k->field == "npwp" ) {
						$searchString = $k->data;
						$where .= " and npwp like  '%".$searchString."%'"; 
					}  else if ( $k->field == "kode_no_seri" ) {
						$searchString = $k->data;
						$where .= " and kode_no_seri like  '%".$searchString."%'"; 
					}  else if ( $k->field == "tgl" ) {
						$searchString = $k->data;
						$where .= " and tgl like  '%".$searchString."%'"; 
					}  else if ( $k->field == "car" ) {
						$searchString = $k->data;
						$where .= " and car like  '%".$searchString."%'"; 
					}   else if ( $k->field == "nobc" ) {
						$searchString = $k->data;
						$where .= " and nobc like  '%".$searchString."%'"; 
					}    else if ( $k->field == "tipe_bc" ) {
						$searchString = $k->data;
						$where .= " and tipe_bc like  '%".$searchString."%'"; 
					}
				}
			}
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_accounting_report->tbl_pajak_b123_all($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_accounting_report->tbl_pajak_b123_all($where);
		if( $count > 0 ) { $total_pages = ceil($count/$limit); } else { $total_pages = 0; } 
        if ($page > $total_pages) $page=$total_pages;
        
        $responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =  $count;
		$i=0;
		foreach($records as $r){
			$responce->rows[$i]['id_b123']=$r->id_b123;
			$responce->rows[$i]['pemberi_jkp']=$r->pemberi_jkp;
			$responce->rows[$i]['npwp']=$r->npwp;
			$responce->rows[$i]['kode_no_seri']=$r->kode_no_seri;
			$responce->rows[$i]['tgl']=$r->tgl;
			$responce->rows[$i]['dpp']=$r->dpp;
			$responce->rows[$i]['ppn']=$r->ppn;
			$responce->rows[$i]['ppnbm']=$r->ppnbm;
			$responce->rows[$i]['car']=$r->car;
			$responce->rows[$i]['nobc']=$r->nobc;
			$responce->rows[$i]['tglbc']=$r->tglbc;
			$responce->rows[$i]['tipe_bc']=$r->tipe_bc;
			$responce->rows[$i]['tipe_pajak']=$r->tipe_pajak;
			
			$i++;
		}
		echo json_encode($responce);
		
	
	}
	
	function view_validasi_ceisa(){
		$bulan = isset($_POST['bulan'])?$_POST['bulan']:date('m'); 
		$tahun = isset($_POST['tahun'])?$_POST['tahun']:date('Y');
		
		$data['param'] = 'aaa';
		$data['bulan'] = $bulan;
		$data['tahun'] = $tahun;
		$this->load->view("accounting_report/view_validasi_ceisa", $data);
		
	}
	
	
	function view_validasi_ceisa_ar(){
		$bulan = isset($_POST['bulan'])?$_POST['bulan']:date('m'); 
		$tahun = isset($_POST['tahun'])?$_POST['tahun']:date('Y');
		
		$data['param'] = 'aaa';
		$data['bulan'] = $bulan;
		$data['tahun'] = $tahun;
		$this->load->view("accounting_report/view_validasi_ceisa_ar", $data);
		
	}
	
	function datamodul_ceisa(){
		$bc23dir = $this->config->item('APP_modulbc23');
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'car'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$tglawalvalidasi_ceisa_ceisacar = (isset($_POST['tglawalvalidasi_ceisa_ceisacar']) && !empty($_POST['tglawalvalidasi_ceisa_ceisacar']))?$_POST['tglawalvalidasi_ceisa_ceisacar']:date('Y-m-d');
		$tglakhirvalidasi_ceisa_ceisacar = (isset($_POST['tglakhirvalidasi_ceisa_ceisacar']) && !empty($_POST['tglakhirvalidasi_ceisa_ceisacar']))?$_POST['tglakhirvalidasi_ceisa_ceisacar']:date('Y-m-d');
		
		
		
		$car = (isset($_POST['car']) && !empty($_POST['car']))?$_POST['car']:'';
		
		$where = " NOMOR_DAFTAR <> '' and (KODE_DOKUMEN_PABEAN in (23, 40, 262) ) ";
		
		if (isset($_POST['search_validasi_month_a1']) && isset($_POST['search_validasi_year_a1']) ){
			$bulan = $_POST['search_validasi_month_a1'];
			$year = $_POST['search_validasi_year_a1'];
			
			 $where .= 	" and ( YEAR(TANGGAL_DAFTAR) = '$year'  and MONTH(TANGGAL_DAFTAR) = '$bulan' ) ";		
		}
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					if ( $k->field == "NOMOR_AJU" ) {
						$searchString = $k->data;
						$where .= " and NOMOR_AJU like  '%".$searchString."%'"; 
					} else if ( $k->field == "KODE_DOKUMEN_PABEAN" ) {
						$searchString = $k->data;
						$where .= " and KODE_DOKUMEN_PABEAN like  '%".$searchString."%'"; 
					}  
				}
			}
		}
		
		
		
		$query = $this->mysql->query("
			select count(*) as cnt from tpb_header where 
			$where
		");
		
		
		$result = $query->result_array();
		
		
		
		$cnt = 0;
		foreach ($result as $row){
		
			$cnt = $row['cnt'];
		
		}
		
		$count=$cnt;
		if( $count >0 ) { $total_pages = ceil($count/$limit); } 
			else { $total_pages = 0; } 
		if ($page > $total_pages) $page=$total_pages; 
		$start = $limit*$page - $limit; // do not put $limit*($page - 1) if ($start<0) $start = 0;
		//$batas = $count - (($page - 1) * $limit);
		$q = $this->mysql->query("
			SELECT tpb_header.ID as ID, NOMOR_AJU,
			DATE_FORMAT(TANGGAL_AJU,'%Y-%m-%d') as TANGGAL_AJU, NOMOR_DAFTAR, DATE_FORMAT(TANGGAL_DAFTAR,'%Y-%m-%d') as TANGGAL_DAFTAR, NAMA_PEMASOK, NAMA_PENGIRIM, KODE_DOKUMEN_PABEAN
			,KODE_VALUTA,NDPBM,CIF,CIF_RUPIAH, ID_PENGIRIM , JENIS_TARIF, NILAI_PUNGUTAN
			from tpb_header 
			inner join tpb_pungutan on tpb_header.ID = tpb_pungutan.ID_HEADER
			where  $where 
			ORDER BY $sidx $sord LIMIT $start , $limit
			
		");
		$rs = $q->result_array();
		//echo $this->db->last_query(); die();
		
		// var_dump($rs);
		// die(); 
		
		$responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =(integer) $count;
		$i=0;
		
		foreach ($rs as $r){
			
			$responce->rows[$i]["NOMOR_AJU"]=$r["NOMOR_AJU"];
			$responce->rows[$i]["TANGGAL_AJU"]=$r["TANGGAL_AJU"];
			$responce->rows[$i]["NOMOR_DAFTAR"]=$r["NOMOR_DAFTAR"];
			$responce->rows[$i]["TANGGAL_DAFTAR"]=$r["TANGGAL_DAFTAR"]; 
			$responce->rows[$i]["ID_PENGIRIM"]=$r["ID_PENGIRIM"];
			$responce->rows[$i]["NAMA_PEMASOK"]=$r["NAMA_PEMASOK"];
			$responce->rows[$i]["NAMA_PENGIRIM"]=$r["NAMA_PENGIRIM"];
			$responce->rows[$i]["KODE_VALUTA"]=$r["KODE_VALUTA"];
			$responce->rows[$i]["KODE_DOKUMEN_PABEAN"]=$r["KODE_DOKUMEN_PABEAN"];
			$responce->rows[$i]["NDPBM"]=$r["NDPBM"];
			$responce->rows[$i]["CIF"]=$r["CIF"];
			$responce->rows[$i]["CIF_RUPIAH"]=$r["CIF_RUPIAH"];
			$responce->rows[$i]["JENIS_TARIF"]=$r["JENIS_TARIF"];
			$responce->rows[$i]["NILAI_PUNGUTAN"]=$r["NILAI_PUNGUTAN"];
			$i++;
			
		}
		
		echo json_encode($responce);
		
		
		
	}
	
	function datamodul_ceisa_ar(){
		$bc23dir = $this->config->item('APP_modulbc23');
		$search = isset($_POST['_search'])?$_POST['_search']:false;
        $searchField = isset($_POST['searchField'])?$_POST['searchField']:'';
        $searchString = isset($_POST['searchString'])?$_POST['searchString']:'';
        $filters =  isset($_POST['filters'])?$_POST['filters']:'';
		
		$page = isset($_POST['page'])?$_POST['page']:1; // get the requested page
        $limit = isset($_POST['rows'])?$_POST['rows']:20; // get how many rows we want to have into the grid
        $sidx = isset($_POST['sidx'])?$_POST['sidx']:'car'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$tglawalvalidasi_ceisa_ceisacar = (isset($_POST['tglawalvalidasi_ceisa_ceisacar']) && !empty($_POST['tglawalvalidasi_ceisa_ceisacar']))?$_POST['tglawalvalidasi_ceisa_ceisacar']:date('Y-m-d');
		$tglakhirvalidasi_ceisa_ceisacar = (isset($_POST['tglakhirvalidasi_ceisa_ceisacar']) && !empty($_POST['tglakhirvalidasi_ceisa_ceisacar']))?$_POST['tglakhirvalidasi_ceisa_ceisacar']:date('Y-m-d');
		
		
		
		$car = (isset($_POST['car']) && !empty($_POST['car']))?$_POST['car']:'';
		
		$where = " NOMOR_DAFTAR <> '' and (KODE_DOKUMEN_PABEAN in (25) ) ";
		
		if (isset($_POST['search_validasi_month_b1']) && isset($_POST['search_validasi_year_b1']) ){
			$bulan = $_POST['search_validasi_month_b1'];
			$year = $_POST['search_validasi_year_b1'];
			
			 $where .= 	" and ( YEAR(TANGGAL_DAFTAR) = '$year'  and MONTH(TANGGAL_DAFTAR) = '$bulan' ) ";		
		}
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					if ( $k->field == "NOMOR_AJU" ) {
						$searchString = $k->data;
						$where .= " and NOMOR_AJU like  '%".$searchString."%'"; 
					} else if ( $k->field == "KODE_DOKUMEN_PABEAN" ) {
						$searchString = $k->data;
						$where .= " and KODE_DOKUMEN_PABEAN like  '%".$searchString."%'"; 
					}  
				}
			}
		}
		
		
		
		$query = $this->mysql->query("
			select count(*) as cnt from tpb_header where 
			$where
		");
		
		
		$result = $query->result_array();
		
		
		
		$cnt = 0;
		foreach ($result as $row){
		
			$cnt = $row['cnt'];
		
		}
		
		$count=$cnt;
		if( $count >0 ) { $total_pages = ceil($count/$limit); } 
			else { $total_pages = 0; } 
		if ($page > $total_pages) $page=$total_pages; 
		$start = $limit*$page - $limit; // do not put $limit*($page - 1) if ($start<0) $start = 0;
		//$batas = $count - (($page - 1) * $limit);
		$q = $this->mysql->query("
			SELECT tpb_header.ID as ID, NOMOR_AJU,
			DATE_FORMAT(TANGGAL_AJU,'%Y-%m-%d') as TANGGAL_AJU, NOMOR_DAFTAR, DATE_FORMAT(TANGGAL_DAFTAR,'%Y-%m-%d') as TANGGAL_DAFTAR, NAMA_PENGUSAHA, NAMA_PENERIMA_BARANG, KODE_DOKUMEN_PABEAN
			,KODE_VALUTA,NDPBM,CIF,CIF_RUPIAH,HARGA_PENYERAHAN ,ID_PENERIMA_BARANG , JENIS_TARIF, NILAI_PUNGUTAN
			from tpb_header 
			inner join tpb_pungutan on tpb_header.ID = tpb_pungutan.ID_HEADER
			where  $where 
			ORDER BY $sidx $sord LIMIT $start , $limit
			
		");
		$rs = $q->result_array();
		//echo $this->db->last_query(); die();
		
		// var_dump($rs);
		// die(); 
		
		$responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =(integer) $count;
		$i=0;
		
		foreach ($rs as $r){
			
			$responce->rows[$i]["NOMOR_AJU"]=$r["NOMOR_AJU"];
			$responce->rows[$i]["TANGGAL_AJU"]=$r["TANGGAL_AJU"];
			$responce->rows[$i]["NOMOR_DAFTAR"]=$r["NOMOR_DAFTAR"];
			$responce->rows[$i]["TANGGAL_DAFTAR"]=$r["TANGGAL_DAFTAR"]; 
			$responce->rows[$i]["ID_PENERIMA_BARANG"]=$r["ID_PENERIMA_BARANG"];
			$responce->rows[$i]["NAMA_PENERIMA_BARANG"]=$r["NAMA_PENERIMA_BARANG"];
			$responce->rows[$i]["NAMA_PENGUSAHA"]=$r["NAMA_PENGUSAHA"];
			$responce->rows[$i]["KODE_VALUTA"]=$r["KODE_VALUTA"];
			$responce->rows[$i]["KODE_DOKUMEN_PABEAN"]=$r["KODE_DOKUMEN_PABEAN"];
			$responce->rows[$i]["NDPBM"]=$r["NDPBM"];
			$responce->rows[$i]["CIF"]=$r["CIF"];
			$responce->rows[$i]["CIF_RUPIAH"]=$r["CIF_RUPIAH"];
			$responce->rows[$i]["HARGA_PENYERAHAN"]=$r["HARGA_PENYERAHAN"];
			$responce->rows[$i]["JENIS_TARIF"]=$r["JENIS_TARIF"];
			$responce->rows[$i]["NILAI_PUNGUTAN"]=$r["NILAI_PUNGUTAN"];
			$i++;
			
		}
		
		echo json_encode($responce);
		
		
		
	}
	
	function validasi_tax_ap_b1(){
	
		$bulan = (isset($_GET['bulan']) && !empty($_GET['bulan']))?$_GET['bulan']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
		$tahun = (isset($_GET['tahun']) && !empty($_GET['tahun']))?$_GET['tahun']:die('{"sts":"ERROR","desc":" Param  tgawal1 Tidak Ditemukan"}');
		$tipe_bc = (isset($_GET['tipe_bc']) && !empty($_GET['tipe_bc']))?$_GET['tipe_bc']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
		
		
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('PAJAK CEISA');
		$phpExcel->getActiveSheet()->SetCellValue("A1", "NOMOR AJU");
		$phpExcel->getActiveSheet()->SetCellValue("B1", "TGL AJU");
		//$phpExcel->getActiveSheet()->setCellValueExplicit("A3", "PERIODE " . $periode, PHPExcel_Cell_DataType::TYPE_STRING); 
		$phpExcel->getActiveSheet()->SetCellValue("C1", "NO DAFTAR");
		$phpExcel->getActiveSheet()->SetCellValue("D1", "TGL DAFTAR");
		$phpExcel->getActiveSheet()->SetCellValue("E1", "NPWP");
		$phpExcel->getActiveSheet()->SetCellValue("F1", "PEMASOK");
		$phpExcel->getActiveSheet()->SetCellValue("G1", "NILAI CIF");
		$phpExcel->getActiveSheet()->SetCellValue("H1", "NILAI PENYERAHAN");
		$phpExcel->getActiveSheet()->SetCellValue("I1", "BM");
		$phpExcel->getActiveSheet()->SetCellValue("J1", "PPN");
		$phpExcel->getActiveSheet()->SetCellValue("K1", "PPH");
		$phpExcel->getActiveSheet()->SetCellValue("L1", "NDPBM");
		$phpExcel->getActiveSheet()->SetCellValue("M1", "TIPE BC");
		$phpExcel->getActiveSheet()->SetCellValue("N1", "NO FAKTUR");
		$phpExcel->getActiveSheet()->SetCellValue("O1", "TG FAKTUR");
		$phpExcel->getActiveSheet()->SetCellValue("P1", "NO INVOICE");
		$phpExcel->getActiveSheet()->SetCellValue("Q1", "TG INVOICE");
		$phpExcel->getActiveSheet()->SetCellValue("R1", "KD VAL");
		
		$where = " NOMOR_DAFTAR <> '' and (KODE_DOKUMEN_PABEAN in ($tipe_bc) ) ";
		$where .= 	" and ( YEAR(TANGGAL_DAFTAR) = '$tahun'  and MONTH(TANGGAL_DAFTAR) = '$bulan' ) ";		
		
		
		$q = $this->mysql->query("
			SELECT tpb_header.ID as ID, NOMOR_AJU,
			DATE_FORMAT(TANGGAL_AJU,'%Y-%m-%d') as TANGGAL_AJU, NOMOR_DAFTAR, DATE_FORMAT(TANGGAL_DAFTAR,'%Y-%m-%d') as TANGGAL_DAFTAR, NAMA_PEMASOK, NAMA_PENGIRIM, KODE_DOKUMEN_PABEAN
			,KODE_VALUTA,NDPBM,CIF,CIF_RUPIAH, ID_PENGIRIM, HARGA_PENYERAHAN  
			from tpb_header 
			where  $where 
			
			
		");
		
		//$rs = $q->result_array(); , JENIS_TARIF, NILAI_PUNGUTAN
		$i = 2;
		
		foreach($q->result() as $r){
			$id = $r->ID;
			
			$q_bm = $this->mysql->query("
				select 
				NILAI_PUNGUTAN
				from tpb_pungutan 
				where  JENIS_TARIF = 'BM' and ID_HEADER = $id
			");
			$bm = 0;
			foreach($q_bm->result() as $rs){
				$bm = $rs->NILAI_PUNGUTAN;				
			}
			
			$q_ppn = $this->mysql->query("
				select 
				NILAI_PUNGUTAN
				from tpb_pungutan 
				where  JENIS_TARIF = 'PPN' and ID_HEADER = $id
			");
			$ppn = 0;
			foreach($q_ppn->result() as $rs){
				$ppn= $rs->NILAI_PUNGUTAN;				
			}
			
			$q_pph = $this->mysql->query("
				select 
				NILAI_PUNGUTAN
				from tpb_pungutan 
				where  JENIS_TARIF = 'PPH' and ID_HEADER = $id
			");
			$pph = 0;
			foreach($q_pph->result() as $rs){
				$pph = $rs->NILAI_PUNGUTAN	;			
			}
			
			
			$q_faktur = $this->mysql->query("
				select 
				NOMOR_DOKUMEN, DATE_FORMAT(TANGGAL_DOKUMEN,'%Y-%m-%d') as TANGGAL_DOKUMEN
				from tpb_dokumen 
				where  ID_HEADER = $id and kode_jenis_dokumen = '388'
			");
			$nofaktur = '';
			$tgl_dokumen = '';
			foreach($q_faktur->result() as $rs){
				$nofaktur .= $rs->NOMOR_DOKUMEN	. '#';	
				$tgl_dokumen .= $rs->TANGGAL_DOKUMEN	. '#';			
			}
			
			
			$phpExcel->getActiveSheet()->setCellValueExplicit("A" . "$i", $r->NOMOR_AJU , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("B" . "$i", $r->TANGGAL_AJU );
			$phpExcel->getActiveSheet()->setCellValueExplicit("C" . "$i", $r->NOMOR_DAFTAR , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("D" . "$i", $r->TANGGAL_DAFTAR );
			
			if($r->ID_PENGIRIM == '' ){
				$phpExcel->getActiveSheet()->setCellValueExplicit("E" . "$i", "-" , PHPExcel_Cell_DataType::TYPE_STRING); 
			} else {
				$phpExcel->getActiveSheet()->setCellValueExplicit("E" . "$i",  $r->ID_PENGIRIM , PHPExcel_Cell_DataType::TYPE_STRING); 
			}
			
			if($r->NAMA_PEMASOK != '' ){
				$phpExcel->getActiveSheet()->setCellValueExplicit("F" . "$i", $r->NAMA_PEMASOK , PHPExcel_Cell_DataType::TYPE_STRING); 
			} else {
				$phpExcel->getActiveSheet()->setCellValueExplicit("F" . "$i",  $r->NAMA_PENGIRIM , PHPExcel_Cell_DataType::TYPE_STRING); 
			}
			
			$phpExcel->getActiveSheet()->SetCellValue("G" . "$i", $r->CIF  );
			$phpExcel->getActiveSheet()->getStyle("G"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			
			
			$penyerahan = $r->HARGA_PENYERAHAN;//($r->CIF_RUPIAH != 0? $r->CIF_RUPIAH : $r->HARGA_PENYERAHAN );
			$phpExcel->getActiveSheet()->SetCellValue("H" . "$i",   $penyerahan  );
			$phpExcel->getActiveSheet()->getStyle("H"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			
			$phpExcel->getActiveSheet()->SetCellValue("I" . "$i", $bm );
			$phpExcel->getActiveSheet()->getStyle("I"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$phpExcel->getActiveSheet()->SetCellValue("J" . "$i", $ppn );
			$phpExcel->getActiveSheet()->getStyle("J"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$phpExcel->getActiveSheet()->SetCellValue("K" . "$i", $pph );
			$phpExcel->getActiveSheet()->getStyle("K"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			
			$phpExcel->getActiveSheet()->SetCellValue("L" . "$i", $r->NDPBM  );
			$phpExcel->getActiveSheet()->getStyle("L"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			
			
			$phpExcel->getActiveSheet()->setCellValueExplicit("M" . "$i", $r->KODE_DOKUMEN_PABEAN , PHPExcel_Cell_DataType::TYPE_STRING);
			
			$phpExcel->getActiveSheet()->setCellValueExplicit("N" . "$i",  substr($nofaktur, 0, (strlen($nofaktur)-1) ) , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("O" . "$i",  substr($tgl_dokumen, 0, (strlen($tgl_dokumen)-1) ) , PHPExcel_Cell_DataType::TYPE_STRING); 
			
			$phpExcel->getActiveSheet()->setCellValueExplicit("R" . "$i",  $r->KODE_VALUTA , PHPExcel_Cell_DataType::TYPE_STRING); 
			
			$q_faktur = $this->mysql->query("
				select 
				NOMOR_DOKUMEN, DATE_FORMAT(TANGGAL_DOKUMEN,'%Y-%m-%d') as TANGGAL_DOKUMEN
				from tpb_dokumen 
				where  ID_HEADER = $id and kode_jenis_dokumen = '380'
			");
			$nofaktur = '';
			$tgl_dokumen = '';
			foreach($q_faktur->result() as $rs){
				$nofaktur .= $rs->NOMOR_DOKUMEN	. '#';	
				$tgl_dokumen .= $rs->TANGGAL_DOKUMEN	. '#';			
			}
			$phpExcel->getActiveSheet()->setCellValueExplicit("P" . "$i",  substr($nofaktur, 0, (strlen($nofaktur)-1) ) , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("Q" . "$i",  substr($tgl_dokumen, 0, (strlen($tgl_dokumen)-1) ) , PHPExcel_Cell_DataType::TYPE_STRING); 
			
			
			
			//$phpExcel->getActiveSheet()->setCellValueExplicit("E" . "$i", $r->coa_sap , PHPExcel_Cell_DataType::TYPE_STRING); 
			//$phpExcel->getActiveSheet()->SetCellValue("G" . "$i", $r->AMOUNT );
			//$phpExcel->getActiveSheet()->getStyle("G"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$i=$i+1;			
		}
		
		
		$filename = 'dataPajakceisa_ar.xls';
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
		
		
	}
	
	function validasi_tax_ap_a1(){
		$this->mysql =$this->load->database('mysql', TRUE);	
		$bulan = (isset($_GET['bulan']) && !empty($_GET['bulan']))?$_GET['bulan']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
		$tahun = (isset($_GET['tahun']) && !empty($_GET['tahun']))?$_GET['tahun']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
		$tipe = (isset($_GET['tipe']) && !empty($_GET['tipe']))?$_GET['tipe']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
		
		include_once ( APPPATH."libraries/PHPExcel.php");	
		include_once ( APPPATH."libraries/PHPExcel/IOFactory.php");	
		$phpExcel = new PHPExcel();
		$phpExcel->setActiveSheetIndex(0);
		$phpExcel->getActiveSheet()->setTitle('PAJAK CEISA');
		$phpExcel->getActiveSheet()->SetCellValue("A1", "NOMOR AJU");
		$phpExcel->getActiveSheet()->SetCellValue("B1", "TGL AJU");
		//$phpExcel->getActiveSheet()->setCellValueExplicit("A3", "PERIODE " . $periode, PHPExcel_Cell_DataType::TYPE_STRING); 
		$phpExcel->getActiveSheet()->SetCellValue("C1", "NO DAFTAR");
		$phpExcel->getActiveSheet()->SetCellValue("D1", "TGL DAFTAR");
		$phpExcel->getActiveSheet()->SetCellValue("E1", "NPWP");
		$phpExcel->getActiveSheet()->SetCellValue("F1", "PENERIMA");
		$phpExcel->getActiveSheet()->SetCellValue("G1", "NILAI CIF");
		$phpExcel->getActiveSheet()->SetCellValue("H1", "NILAI_CIF_RP/PENYERAHAN");
		$phpExcel->getActiveSheet()->SetCellValue("I1", "BM");
		$phpExcel->getActiveSheet()->SetCellValue("J1", "PPN");
		$phpExcel->getActiveSheet()->SetCellValue("K1", "PPH");
		$phpExcel->getActiveSheet()->SetCellValue("L1", "NDPBM");
		$phpExcel->getActiveSheet()->SetCellValue("M1", "TIPE BC");
		$phpExcel->getActiveSheet()->SetCellValue("N1", "NO FAKTUR");
		$phpExcel->getActiveSheet()->SetCellValue("O1", "TG FAKTUR");
		$phpExcel->getActiveSheet()->SetCellValue("P1", "NO INVOICE");
		$phpExcel->getActiveSheet()->SetCellValue("Q1", "TG INVOICE");
		$phpExcel->getActiveSheet()->SetCellValue("R1", "KD VAL");
		
		$where = " NOMOR_DAFTAR <> '' and (KODE_DOKUMEN_PABEAN in ($tipe) ) ";
		if ($bulan == '-') {
			$where .= 	" and ( YEAR(TANGGAL_DAFTAR) = '$tahun' ) ";
		} else {
			$where .= 	" and ( YEAR(TANGGAL_DAFTAR) = '$tahun'  and MONTH(TANGGAL_DAFTAR) = '$bulan' )";		
		}
		
		$q = $this->mysql->query("
			SELECT tpb_header.ID as ID, NOMOR_AJU,
			DATE_FORMAT(TANGGAL_AJU,'%Y-%m-%d') as TANGGAL_AJU, NOMOR_DAFTAR, DATE_FORMAT(TANGGAL_DAFTAR,'%Y-%m-%d') as TANGGAL_DAFTAR, NAMA_PEMASOK, NAMA_PENERIMA_BARANG, KODE_DOKUMEN_PABEAN
			,KODE_VALUTA,NDPBM,CIF,CIF_RUPIAH, ID_PENERIMA_BARANG, HARGA_PENYERAHAN  
			from tpb_header 
			where  $where 
			
			
		");
		
		//$rs = $q->result_array(); , JENIS_TARIF, NILAI_PUNGUTAN
		$i = 2;
		
		foreach($q->result() as $r){
			$id = $r->ID;
			
			$q_bm = $this->mysql->query("
				select 
				NILAI_PUNGUTAN
				from tpb_pungutan 
				where  JENIS_TARIF = 'BM' and ID_HEADER = $id
			");
			$bm = 0;
			foreach($q_bm->result() as $rs){
				$bm = $rs->NILAI_PUNGUTAN;				
			}
			
			$q_ppn = $this->mysql->query("
				select 
				NILAI_PUNGUTAN
				from tpb_pungutan 
				where  JENIS_TARIF = 'PPN' and ID_HEADER = $id
			");
			$ppn = 0;
			foreach($q_ppn->result() as $rs){
				$ppn= $rs->NILAI_PUNGUTAN;				
			}
			
			$q_pph = $this->mysql->query("
				select 
				NILAI_PUNGUTAN
				from tpb_pungutan 
				where  JENIS_TARIF = 'PPH' and ID_HEADER = $id
			");
			$pph = 0;
			foreach($q_pph->result() as $rs){
				$pph = $rs->NILAI_PUNGUTAN	;			
			}
			
			
			$q_faktur = $this->mysql->query("
				select 
				NOMOR_DOKUMEN, DATE_FORMAT(TANGGAL_DOKUMEN,'%Y-%m-%d') as TANGGAL_DOKUMEN
				from tpb_dokumen 
				where  ID_HEADER = $id and kode_jenis_dokumen = '388'
			");
			$nofaktur = '';
			$tgl_dokumen = '';
			foreach($q_faktur->result() as $rs){
				$nofaktur .= $rs->NOMOR_DOKUMEN	. '#';	
				$tgl_dokumen .= $rs->TANGGAL_DOKUMEN	. '#';			
			}
			
			
			$phpExcel->getActiveSheet()->setCellValueExplicit("A" . "$i", $r->NOMOR_AJU , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("B" . "$i", $r->TANGGAL_AJU );
			$phpExcel->getActiveSheet()->setCellValueExplicit("C" . "$i", $r->NOMOR_DAFTAR , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->SetCellValue("D" . "$i", $r->TANGGAL_DAFTAR );
			
			if($r->ID_PENERIMA_BARANG == '' ){
				$phpExcel->getActiveSheet()->setCellValueExplicit("E" . "$i", "-" , PHPExcel_Cell_DataType::TYPE_STRING); 
			} else {
				$phpExcel->getActiveSheet()->setCellValueExplicit("E" . "$i",  $r->ID_PENERIMA_BARANG , PHPExcel_Cell_DataType::TYPE_STRING); 
			}
			
			if($r->ID_PENERIMA_BARANG == '' ){
				$phpExcel->getActiveSheet()->setCellValueExplicit("F" . "$i", "-" , PHPExcel_Cell_DataType::TYPE_STRING); 
			} else {
				$phpExcel->getActiveSheet()->setCellValueExplicit("F" . "$i",  $r->NAMA_PENERIMA_BARANG , PHPExcel_Cell_DataType::TYPE_STRING); 
			}
			
			$phpExcel->getActiveSheet()->SetCellValue("G" . "$i", $r->CIF  );
			$phpExcel->getActiveSheet()->getStyle("G"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			
			
			$penyerahan = ($r->CIF_RUPIAH != 0? $r->CIF_RUPIAH : $r->HARGA_PENYERAHAN );
			$phpExcel->getActiveSheet()->SetCellValue("H" . "$i",   $penyerahan  );
			$phpExcel->getActiveSheet()->getStyle("H"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			
			$phpExcel->getActiveSheet()->SetCellValue("I" . "$i", $bm );
			$phpExcel->getActiveSheet()->getStyle("I"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$phpExcel->getActiveSheet()->SetCellValue("J" . "$i", $ppn );
			$phpExcel->getActiveSheet()->getStyle("J"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$phpExcel->getActiveSheet()->SetCellValue("K" . "$i", $pph );
			$phpExcel->getActiveSheet()->getStyle("K"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			
			$phpExcel->getActiveSheet()->SetCellValue("L" . "$i", $r->NDPBM  );
			$phpExcel->getActiveSheet()->getStyle("L"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$phpExcel->getActiveSheet()->setCellValueExplicit("R" . "$i",  $r->KODE_VALUTA , PHPExcel_Cell_DataType::TYPE_STRING); 
			
			
			$phpExcel->getActiveSheet()->setCellValueExplicit("M" . "$i", $r->KODE_DOKUMEN_PABEAN , PHPExcel_Cell_DataType::TYPE_STRING);
			
			$phpExcel->getActiveSheet()->setCellValueExplicit("N" . "$i",  substr($nofaktur, 0, (strlen($nofaktur)-1) ) , PHPExcel_Cell_DataType::TYPE_STRING); 
			
			$phpExcel->getActiveSheet()->setCellValueExplicit("O" . "$i",  substr($tgl_dokumen, 0, (strlen($tgl_dokumen)-1) ) , PHPExcel_Cell_DataType::TYPE_STRING); 
			
			
			$q_faktur = $this->mysql->query("
				select 
				NOMOR_DOKUMEN, DATE_FORMAT(TANGGAL_DOKUMEN,'%Y-%m-%d') as TANGGAL_DOKUMEN
				from tpb_dokumen 
				where  ID_HEADER = $id and kode_jenis_dokumen = '380'
			");
			$nofaktur = '';
			$tgl_dokumen = '';
			foreach($q_faktur->result() as $rs){
				$nofaktur .= $rs->NOMOR_DOKUMEN	. '#';	
				$tgl_dokumen .= $rs->TANGGAL_DOKUMEN	. '#';			
			}
			$phpExcel->getActiveSheet()->setCellValueExplicit("P" . "$i",  substr($nofaktur, 0, (strlen($nofaktur)-1) ) , PHPExcel_Cell_DataType::TYPE_STRING); 
			$phpExcel->getActiveSheet()->setCellValueExplicit("Q" . "$i",  substr($tgl_dokumen, 0, (strlen($tgl_dokumen)-1) ) , PHPExcel_Cell_DataType::TYPE_STRING); 
			
			
			
			//$phpExcel->getActiveSheet()->setCellValueExplicit("E" . "$i", $r->coa_sap , PHPExcel_Cell_DataType::TYPE_STRING); 
			//$phpExcel->getActiveSheet()->SetCellValue("G" . "$i", $r->AMOUNT );
			//$phpExcel->getActiveSheet()->getStyle("G"."$i")->getNumberFormat()->setFormatCode('#,##0.00');	
			$i=$i+1;			
		}
		
		
		$filename = 'dataPajakceisa_ar.xls';
		header("Content-type:application/vnd.ms-excel");
		header("Content-disposition:attachment; filename=$filename"); 
		header("Pragma:no-cache");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Expires:0");

		$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
		$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
		 
		exit; //done.. exiting!	
		
		
	}
	
	
		
	
	
	
	
	
}
    



?>