<?php
class CT_bc23 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->model("exim/mdl_bc23", 'mdl_bc23');
	}

	function index(){
		
		$this->load->view('exim/view_bc23');
	
	}
	
	function loaddata(){
		$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'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " 1=1 and typedok='BC23' ";
		if (isset($_POST['tglawal']) and isset($_POST['tglakhir'])){
			$tglawal=$_POST['tglawal'];
			$tglakhir=$_POST['tglakhir'];
			$where .= " and BC23Tg between '$tglawal' and '$tglakhir' ";
		}
				
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "nobl" ) {
						$searchString = $k->data;
						$where .= " and nobl like  '%".$searchString."%'"; 
					} else if ( $k->field == "tglbl" ) {
						$searchString = $k->data;
						$where .= " and tglbl =  '$searchString'"; 
					} else if ( $k->field == "CAR" ) {
						$searchString = $k->data;
						$where .= " and CAR like  '%".$searchString."%'"; 
					} else if ( $k->field == "KdKpbc" ) {
						$searchString = $k->data;
						$where .= " and KdKpbc like  '%".$searchString."%'"; 
					} else if ( $k->field == "PasokNama" ) {
						$searchString = $k->data;
						$where .= " and PasokNama 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 == "JmBrg" ) {
						$searchString = $k->data;
						$where .= " and JmBrg = $searchString"; 
					} else if ( $k->field == "Status" ) {
						$searchString = $k->data;
						$where .= " and Status like  '%".$searchString."%'"; 
					}  else if ( $k->field == "statusdok" ) {
						$searchString = $k->data;
						$where .= " and statusdok like  '%".$searchString."%'"; 
					} 
				}
			}
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_bc23->bc23hdrdataAll($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_bc23->bc23hdrdataCountAll($where);
	//	var_dump($records); die();
		
		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){
			if($r->Status == null){
				$status = '-';
				
			} else { $status=$r->Status;  }
			
			$responce->rows[$i]['id_bc_in_hdr']=$r->id_bc_in_hdr; 
			$responce->rows[$i]['CAR']=$r->CAR; 
			$responce->rows[$i]['KdKpbc']=$r->KdKpbc; 
			$responce->rows[$i]['Tujuan']=$r->Tujuan; 
			$responce->rows[$i]['JnsBarang']=$r->JnsBarang; 
			$responce->rows[$i]['TujuanKirim']=$r->TujuanKirim; 
			$responce->rows[$i]['PasokNama']=$r->PasokNama; 
			$responce->rows[$i]['PasokAlmt']=$r->PasokAlmt; 
			$responce->rows[$i]['PasokNeg']=$r->PasokNeg; 
			$responce->rows[$i]['UsahaId']=$r->UsahaId; 
			$responce->rows[$i]['UsahaNpwp']=$r->UsahaNpwp; 
			$responce->rows[$i]['KdTPB']=$r->KdTPB; 
			$responce->rows[$i]['Registrasi']=$r->Registrasi; 
			$responce->rows[$i]['UsahaNama']=$r->UsahaNama; 
			$responce->rows[$i]['UsahaAlmt']=$r->UsahaAlmt; 
			$responce->rows[$i]['UsahaStatus']=$r->UsahaStatus; 
			$responce->rows[$i]['ApiKd']=$r->ApiKd; 
			$responce->rows[$i]['ApiNo']=$r->ApiNo; 
			$responce->rows[$i]['PpjkId']=$r->PpjkId; 
			$responce->rows[$i]['PpjkNpwp']=$r->PpjkNpwp; 
			$responce->rows[$i]['PpjkNama']=$r->PpjkNama; 
			$responce->rows[$i]['PpjkAlmt']=$r->PpjkAlmt; 
			$responce->rows[$i]['PpjkNo']=$r->PpjkNo; 
			$responce->rows[$i]['PpjkTg']=$r->PpjkTg; 
			$responce->rows[$i]['IndId']=$r->IndId; 
			$responce->rows[$i]['IndNpwp']=$r->IndNpwp; 
			$responce->rows[$i]['IndNama']=$r->IndNama; 
			$responce->rows[$i]['IndAlmt']=$r->IndAlmt; 
			$responce->rows[$i]['Moda']=$r->Moda; 
			$responce->rows[$i]['AngkutNama']=$r->AngkutNama; 
			$responce->rows[$i]['AngkutNo']=$r->AngkutNo; 
			$responce->rows[$i]['AngkutFl']=$r->AngkutFl; 
			$responce->rows[$i]['PelBkr']=$r->PelBkr; 
			$responce->rows[$i]['PelMuat']=$r->PelMuat; 
			$responce->rows[$i]['PelTransit']=$r->PelTransit; 
			$responce->rows[$i]['BC23No']=$r->BC23No; 
			$responce->rows[$i]['BC23Tg']=$r->BC23Tg; 
			$responce->rows[$i]['KdKpbcBongkar']=$r->KdKpbcBongkar; 
			$responce->rows[$i]['KdKpbcAwas']=$r->KdKpbcAwas; 
			$responce->rows[$i]['DokTupKd']=$r->DokTupKd; 
			$responce->rows[$i]['DokTupNo']=$r->DokTupNo; 
			$responce->rows[$i]['DokTupTg']=$r->DokTupTg; 
			$responce->rows[$i]['PosNo']=$r->PosNo; 
			$responce->rows[$i]['PosSub']=$r->PosSub; 
			$responce->rows[$i]['TmpTbn']=$r->TmpTbn; 
			$responce->rows[$i]['KdVal']=$r->KdVal; 
			$responce->rows[$i]['Ndpbm']=$r->Ndpbm; 
			$responce->rows[$i]['NilInv']=$r->NilInv; 
			$responce->rows[$i]['Freight']=$r->Freight; 
			$responce->rows[$i]['BTambahan']=$r->BTambahan; 
			$responce->rows[$i]['Diskon']=$r->Diskon; 
			$responce->rows[$i]['KdAss']=$r->KdAss; 
			$responce->rows[$i]['Asuransi']=$r->Asuransi; 
			$responce->rows[$i]['KdHrg']=$r->KdHrg; 
			$responce->rows[$i]['Fob']=$r->Fob; 
			$responce->rows[$i]['Cif']=$r->Cif; 
			$responce->rows[$i]['Bruto']=$r->Bruto; 
			$responce->rows[$i]['Netto']=$r->Netto; 
			$responce->rows[$i]['JmCont']=$r->JmCont; 
			$responce->rows[$i]['JmBrg']=$r->JmBrg; 
			$responce->rows[$i]['TglTtd']=$r->TglTtd; 
			$responce->rows[$i]['KotaTtd']=$r->KotaTtd; 
			$responce->rows[$i]['Namattd']=$r->Namattd; 
			$responce->rows[$i]['ContakPerson']=$r->ContakPerson; 
			$responce->rows[$i]['NoPhone']=$r->NoPhone; 
			$responce->rows[$i]['NoFax']=$r->NoFax; 
			$responce->rows[$i]['Email']=$r->Email; 
			$responce->rows[$i]['Status']=$status;//$r->Status; 
			$responce->rows[$i]['Snrf']=$r->Snrf; 
			$responce->rows[$i]['Lengkap']=$r->Lengkap; 
			$responce->rows[$i]['PosSubSub']=$r->PosSubSub; 
			$responce->rows[$i]['NoKontrak']=$r->NoKontrak; 
			$responce->rows[$i]['TgKontrak']=$r->TgKontrak; 
			$responce->rows[$i]['HargaRp']=$r->HargaRp; 
			$responce->rows[$i]['TmpPeriksaFisik']=$r->TmpPeriksaFisik; 
			$responce->rows[$i]['AlmtPeriksaFisik']=$r->AlmtPeriksaFisik; 
			$responce->rows[$i]['TgPeriksaFisik']=$r->TgPeriksaFisik; 
			$responce->rows[$i]['WkPeriksaFisik']=$r->WkPeriksaFisik; 
			$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; 
			$responce->rows[$i]['typedok']=$r->typedok; 
			$responce->rows[$i]['statusdok']=$r->statusdok; 
			$responce->rows[$i]['noinv']=$r->noinv; 
			$responce->rows[$i]['tglinv']=$r->tglinv;
			$responce->rows[$i]['inv_hdr_id']=$r->inv_hdr_id;
			$responce->rows[$i]['nobl']=$r->nobl;
			$responce->rows[$i]['tglbl']=$r->tglbl;
			
			$i++;
		}
		echo json_encode($responce);
		
	}
	
	function frmgetdatahdrbc23(){
		$car = (isset($_POST['car']) && !empty($_POST['car']))?$_POST['car']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$noinv = (isset($_POST['noinv']) && !empty($_POST['noinv']))?$_POST['noinv']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$tglinv = (isset($_POST['tglinv']) && !empty($_POST['tglinv']))?$_POST['tglinv']:die('{"sts":"ERROR","desc":" tglinv Param Tidak Ditemukan"}');
		$idhdrbc23 = (isset($_POST['idhdrbc23']) && !empty($_POST['idhdrbc23']))?$_POST['idhdrbc23']:die('{"sts":"ERROR","desc":" idhdrbc23 Param Tidak Ditemukan"}');
		
		$data["noinv"] = $noinv;
		$data["car"] = $car;
		$data["tglinv"] = $tglinv;
		$data["idhdrbc23"] = $idhdrbc23;
		
		$this->load->view('exim/view_bc23_get_modul', $data);
		
	}
	
	function view_bc23_matching(){
	
		$this->load->view('exim/view_bc23_matching');
	
	}
	
	function datamodul_detil(){
		
		$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	
		
		$tglawalbc23car = (isset($_POST['tglawalbc23car']) && !empty($_POST['tglawalbc23car']))?$_POST['tglawalbc23car']:date('Y-m-d');
		$tglakhirbc23car = (isset($_POST['tglakhirbc23car']) && !empty($_POST['tglakhirbc23car']))?$_POST['tglakhirbc23car']:date('Y-m-d');
		
		$car = (isset($_POST['car']) && !empty($_POST['car']))?$_POST['car']:'';
		
		$where = " 1=1 ";
		
		$where = " dbo.DateSerial(SUBSTRING(a.car,13,4),SUBSTRING(a.car,17,2),SUBSTRING(a.car,19,2)) between '$tglawalbc23car' and '$tglakhirbc23car' ";
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "dokno" ) {
						$searchString = $k->data;
						$wheredok = " dokkd='380' and dokno like  '%".$searchString."%'"; 
						$this->db->query("
							SET ANSI_NULLS ON
							SET ANSI_WARNINGS ON
						");
						$qdok = $this->db->query("
							
							select car from
							OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
										'Data Source=$bc23dir;
										Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
										)...tblBC23dok 
							where $wheredok 
						");
						$rs = $qdok->result_array();
						foreach ( $rs as $r ){
							
							$car = $r["car"];
						
						}
						
					} 
				}
			}
		}
		
		
		if (!empty($car)){
			
			$where = " a.car like '%$car%'  ";
		}
		//echo $car; die();
		//$bc23dir = $this->config->item('APP_modulbc23');
		
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		
		/* $query = $this->db->query("
			
			select COUNT(*) as cnt from
			OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23Hdr a 
			left outer join dbo.tbl_bc_in_hdr b on a.car = b.car
			inner join OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23dok c on a.car=c.car
			where $where 
			and c.dokkd='380'
		
		"); */
		$query = $this->db->query("
			
			select COUNT(*) as cnt from
			OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23Hdr a 
			left outer join dbo.tbl_bc_in_hdr b on a.car = b.car
			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->db->query("
			select top $limit car, convert(varchar, xdate, 101) as xdate,  carx, sts, dokno, convert(varchar,doktg, 101) as doktg, jnsbarang from (
				select TOP $batas a.car as CAR, dbo.DateSerial(SUBSTRING(a.car,13,4),SUBSTRING(a.car,17,2),SUBSTRING(a.car,19,2)) as xdate
				,b.car as carx, 
				a.status as sts, c.dokno, c.doktg, a.jnsbarang from
				OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
							'Data Source=$bc23dir;
							Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
							)...tblBC23Hdr a 
				left outer join dbo.tbl_bc_in_hdr b on a.car = b.car
				inner join OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
							'Data Source=$bc23dir;
							Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
							)...tblBC23dok c on a.car=c.car
				where $where
				and c.dokkd='380'
				order by $sidx desc
			)  t1 Order By car $sord
			
			"); */
		 $q = $this->db->query("
			select top $limit car, convert(varchar, xdate, 101) as xdate,  carx, sts,jnsbarang from (
				select TOP $batas a.car as CAR, dbo.DateSerial(SUBSTRING(a.car,13,4),SUBSTRING(a.car,17,2),SUBSTRING(a.car,19,2)) as xdate
				,b.car as carx, 
				a.status as sts, a.jnsbarang from
				OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
							'Data Source=$bc23dir;
							Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
							)...tblBC23Hdr a 
				left outer join dbo.tbl_bc_in_hdr b on a.car = b.car
				where $where
				order by $sidx desc
			)  t1 Order By car $sord
			
			"); 
		$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]["CAR"]=$r["car"];
			$responce->rows[$i]["carx"]=$r["carx"];
			$responce->rows[$i]["xdate"]=$r["xdate"];
		/* 	$responce->rows[$i]["dokno"]=$r["dokno"];
			$responce->rows[$i]["doktg"]=$r["doktg"]; */
			$responce->rows[$i]["sts"]=$r["sts"];
			$responce->rows[$i]["jnsbarang"]=$r["jnsbarang"];
			$i++;
			
		}
		
		echo json_encode($responce);
		die();
	}
	
	function cekdokmacthing(){
		$car = (isset($_POST['car']) && !empty($_POST['car']))?$_POST['car']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$bc23dir = $this->config->item('APP_modulbc23');
		
		
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
         $q = $this->db->query("
			select dokno, convert(varchar, doktg, 103) as doktg, dokkd
			 from
				OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
							'Data Source=$bc23dir;
							Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
							)...tblBC23dok
				
				where car='$car'
			"); 
		$rs = $q->result_array();
		$i=0;	
		foreach ($rs as $r){
			
		 	$responce[$i]["dokno"]=$r["dokno"];
			$responce[$i]["doktg"]=$r["doktg"]; 
			$responce[$i]["dokkd"]=$r["dokkd"]; 
			$i++;
			
		}
		
		echo json_encode($responce);
		
	
	
	}
	
	function proses_bc23(){
		$car = (isset($_POST['carproses_matchbc23']) && !empty($_POST['carproses_matchbc23']))?$_POST['carproses_matchbc23']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$jnsbarang = (isset($_POST['jnsbarang']) && !empty($_POST['jnsbarang']))?$_POST['jnsbarang']:die('{"sts":"ERROR","desc":" jnsbarang: Param Tidak Ditemukan"}');
		$username=$this->session->userdata('DX_username');
		$bc23dir = $this->config->item('APP_modulbc23');
		$dok_in_from_in = 'INV HIB';
		if ($jnsbarang == '01'){
			$dok_in_from_in = 'INV HIB';
		
		} else if($jnsbarang == '03' ){
		
			$dok_in_from_in = 'INVMPK';
		} else if($jnsbarang == '07' ){
		
			$dok_in_from_in = 'INVREIM';
		} else if($jnsbarang == '02' ){
		
			$dok_in_from_in = 'INVBP';
		}
		//echo " exec('[sp_get_from_module_bc23]  @car =''$car'', @username=''$username'', @conn=''$bc23dir'', @dok_in_from_in=''HIB'' ') "; die();
		$matchcar = $this->db->query("
			
			BEGIN TRY
			
			 exec('[sp_get_from_module_bc23]  @car =''$car'', @username=''$username'', @conn=''$bc23dir'', @dok_in_from_in=''$dok_in_from_in'' ')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			");
		$rs = $matchcar->result_array();
        //$r = $matchcar->result();		
		//var_dump($rs);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
	}
	function view_bc23_tarikmesin(){
	
		$this->load->view('exim/view_bc23_tarikmesin');	
	
	}
	function loaddatabc23mesin(){
		$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	
		
		$tglawalbc23car = (isset($_POST['tglawalbc23car']) && !empty($_POST['tglawalbc23car']))?$_POST['tglawalbc23car']:date('Y-m-d');
		$tglakhirbc23car = (isset($_POST['tglakhirbc23car']) && !empty($_POST['tglakhirbc23car']))?$_POST['tglakhirbc23car']:date('Y-m-d');
		
		$car = (isset($_POST['car']) && !empty($_POST['car']))?$_POST['car']:'';
		
		$where = " 1=1 ";
		
		$where = " dbo.DateSerial(SUBSTRING(a.car,13,4),SUBSTRING(a.car,17,2),SUBSTRING(a.car,19,2)) between '$tglawalbc23car' and '$tglakhirbc23car' ";
		
		if (!empty($car)){
			
			$where = " a.car like '%$car%'  ";
		}
		//echo $car; die();
		$bc23dir = $this->config->item('APP_modulbc23');
		
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		
		/*
		 $query = $this->db->query("
			select COUNT(*) as cnt from
			OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23Hdr a 
			inner join OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23dok c on a.car=c.car and a.jnsbarang='03'
			where $where 
			and c.dokkd='380'
		 
		 
		 ");
		*/
		//echo $this->db->last_query(); die();
		 $query = $this->db->query("
			
			select COUNT(*) as cnt from
			OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23Hdr a 
			left outer join dbo.tbl_bc_in_hdr b on a.car = b.car
			inner join OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23dok c on a.car=c.car and a.jnsbarang='03'
			where $where 
			and c.dokkd='380'
		
		"); 
		$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->db->query("
			select top $limit car, convert(varchar, xdate, 101) as xdate, sts, dokno, convert(varchar,doktg, 101) as doktg from (
			select TOP $batas a.car as CAR, dbo.DateSerial(SUBSTRING(a.car,13,4),SUBSTRING(a.car,17,2),SUBSTRING(a.car,19,2)) as xdate
			, 
			a.status as sts, c.dokno, c.doktg from
			OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23Hdr a 
			inner join OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23dok c on a.car=c.car and a.jnsbarang='03' 
			where $where
			and c.dokkd='380'
			order by $sidx desc
		)  t1 Order By car $sord
			
		
		");
		*/
				
		 $q = $this->db->query("
			select top $limit car, convert(varchar, xdate, 101) as xdate,  carx, sts, dokno, convert(varchar,doktg, 101) as doktg from (
				select TOP $batas a.car as CAR, dbo.DateSerial(SUBSTRING(a.car,13,4),SUBSTRING(a.car,17,2),SUBSTRING(a.car,19,2)) as xdate
				,b.car as carx, 
				a.status as sts, c.dokno, c.doktg from
				OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
							'Data Source=$bc23dir;
							Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
							)...tblBC23Hdr a 
				left outer join dbo.tbl_bc_in_hdr b on a.car = b.car
				inner join OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
							'Data Source=$bc23dir;
							Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
							)...tblBC23dok c on a.car=c.car and a.jnsbarang='03' 
				where $where
				and c.dokkd='380'
				order by $sidx desc
			)  t1 Order By car $sord
			
			"); 
		
		$rs = $q->result_array();
		//die($this->db->last_query());
		// var_dump($rs);
		// die(); 
		
		$responce->page = $page;
		$responce->total = $total_pages;
		$responce->records =(integer) $count;
		$i=0;
		
		foreach ($rs as $r){
			
			$responce->rows[$i]["CAR"]=$r["car"];
			$responce->rows[$i]["carx"]=$r["carx"];
			$responce->rows[$i]["xdate"]=$r["xdate"];
			$responce->rows[$i]["dokno"]=$r["dokno"];
			$responce->rows[$i]["doktg"]=$r["doktg"];
			$responce->rows[$i]["sts"]=$r["sts"];
			$i++;
			
		}
		
		echo json_encode($responce);
		die();
	}
	
	function view_bc23_invoicemesin(){
	
		$this->load->view('exim/view_bc23_invoicemesin');		
	
	}
	
	function view_bc23_detil_invoicemesin(){
	
		$postData = (isset($_POST['postData']) && !empty($_POST['postData']))?$_POST['postData']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan or not selected"}');
		//$dataarray = json_decode($postData);
/* 		echo "<pre>";
		var_dump($dataarray); */
		/* $where = '';
		$where1 = '';
		foreach ($dataarray as $r ){
			$where .= "'" . $r->invoice_no . "'" .',';
			$where1 .= $r->order_no  . ',';
		}
		
		$where = substr($where, 0, strlen($where) - 1);
		$where1 = substr($where1, 0, strlen($where1) - 1);
		
		$data['where'] = '('.$where.')';
		$data['where1'] = '('.$where1.')';  */		
		//echo $postData;
		$data['dataarray'] =  $postData;
		$this->load->view('exim/view_bc23_detil_invoicemesin', $data);		
		
		
	}
	
	function proses_inv_mesinsparepart(){
		$id_inv_mesin = (isset($_POST['id_inv_mesin']) && !empty($_POST['id_inv_mesin']))?$_POST['id_inv_mesin']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$uid = $this->session->userdata('DX_username');
		
		// echo " exec('[spi_invoice_mesinsparepart]  @id_inv_mesin = $id_inv_mesin, @tipe=''INVMPK'', @username=''$uid'' ') ";
		// die();
		$qry_inv_mesin = $this->db->query("
			
			BEGIN TRY
			
			 exec('[spi_invoice_mesinsparepart]  @id_inv_mesin = $id_inv_mesin, @tipe=''INVMPK'', @username=''$uid'' ')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			");
		$rs = $qry_inv_mesin->result_array();
        //$r = $qry_inv_mesin->result();		
		//var_dump($rs);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
	
	
	}
	function loaddata_proses_inv_mesinsparepart(){
		$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_invoice_mesinsparepart'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		$dimana = (isset($_POST['where']) && !empty($_POST['where']))?$_POST['where']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$dimana = str_replace("<font color='red'>", "", $dimana);
		$rep = array('<font color="red">', '<font color="blue">','</font>');
		$dimana = str_replace($rep, "", $dimana);
		$dataarray = json_decode($dimana);
		/* 
		print_r($dataarray);
		die();
		 */
		$whereinvoice_no = '';
		$whereorder_no = '';
		foreach ($dataarray as $r ){
			$whereinvoice_no .= "'" . $r->invoice_no . "'" .',';
			$whereorder_no .= "'" . $r->order_no . "'"  . ',';
		}
		
				
		$whereinvoice_no = substr($whereinvoice_no, 0, strlen($whereinvoice_no) - 1);
		$whereorder_no = substr($whereorder_no, 0, strlen($whereorder_no) - 1);
		
		$whereinvoice_no = '('.$whereinvoice_no.')';
		$whereorder_no = '('.$whereorder_no.')';
		
		 		
				
		$where = " 1=1 and status_data='new' and [invoice_no] in $whereinvoice_no and order_no in $whereorder_no  ";
			
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "invoice_no" ) {
						$searchString = $k->data;
						$where .= " and invoice_no like  '%".$searchString."%'"; 
					} else if ( $k->field == "part_name" ) {
						$searchString = $k->data;
						$where .= " and part_name like  '%".$searchString."%'"; 
					} 
				}
			}
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_bc23->bc23_mesin_sparepartAll($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_bc23->bc23_mesin_sparepartCountAll($where);
	//	var_dump($records); die();
		
		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_invoice_mesinsparepart']=$r->id_invoice_mesinsparepart;
			$responce->rows[$i]['part_name']=$r->part_name;
			$responce->rows[$i]['part_no']=$r->part_no;
			$responce->rows[$i]['kode_barang']=$r->kode_barang;
			$responce->rows[$i]['kode_supplier']=$r->kode_supplier;
			$responce->rows[$i]['maker']=$r->maker;
			$responce->rows[$i]['order_qty']=$r->order_qty;
			$responce->rows[$i]['order_no']=$r->order_no;
			$responce->rows[$i]['order_date']=$r->order_date;
			$responce->rows[$i]['req_eta_date']=$r->req_eta_date;
			$responce->rows[$i]['shipping_by']=$r->shipping_by;
			$responce->rows[$i]['invoice_no']=$r->invoice_no;
			$responce->rows[$i]['invoice_date']=$r->invoice_date;
			$responce->rows[$i]['inv_qty']=$r->inv_qty;
			$responce->rows[$i]['inv_unit']=$r->inv_unit;
			$responce->rows[$i]['status_inv']=$r->status_inv;
			$responce->rows[$i]['rec_qty']=$r->rec_qty;
			$responce->rows[$i]['rec_date']=$r->rec_date;
			$responce->rows[$i]['amount']=$r->amount;
			$responce->rows[$i]['remark']=$r->remark;
			$responce->rows[$i]['nobl']=$r->nobl;
			$responce->rows[$i]['tglbl']=$r->tglbl;
			$responce->rows[$i]['nocar']=$r->nocar;
			$responce->rows[$i]['tglaju']=$r->tglaju;
			$responce->rows[$i]['status_data']=$r->status_data;
			$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 loaddata_proses_inv_mesinsparepart_hdr(){
		$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']:'order_date'; // 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 == "invoice_no" ) {
						$searchString = $k->data;
						$where .= " and invoice_no like  '%".$searchString."%'"; 
					} else if ( $k->field == "part_name" ) {
						$searchString = $k->data;
						$where .= " and part_name like  '%".$searchString."%'"; 
					}  else if ( $k->field == "status_data" ) {
						$searchString = $k->data;
						$where .= " and status_data like  '%".$searchString."%'"; 
						if ($searchString == 'all'){
						
							$where = " 1=1 ";
						}
					}
				}
			}
		} else {
		
			$where = " 1=1 and status_data='new' ";
		
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_bc23->bc23_mesin_sparepart_hdrAll($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_bc23->bc23_mesin_sparepart_hdrCountAll($where);
	//	var_dump($records); die();
		
		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]['kode_supplier']=$r->kode_supplier;
			$responce->rows[$i]['order_no']=$r->order_no;
			$responce->rows[$i]['order_date']=$r->order_date;
			$responce->rows[$i]['invoice_no']=$r->invoice_no;
			$responce->rows[$i]['invoice_date']=$r->invoice_date;
			$responce->rows[$i]['status_data']=$r->status_data;
			$responce->rows[$i]['nobl']=$r->nobl;
			$responce->rows[$i]['tglbl']=$r->tglbl;
			
			$i++;
		}
		echo json_encode($responce);
	}
	
	function proses_update_inv_mesinsparepart(){
		$this->load->library('arraytoxml');
		$uid = $this->session->userdata('DX_username');
		$postData = (isset($_POST['postData']) && !empty($_POST['postData']))?$_POST['postData']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan or not selected"}');
		
		$rep = array('<font color="red">', '<font color="blue">','</font>');
		$postData = str_replace($rep, "", $postData);
		
		$array = json_decode($postData);
		$array = objectToArray($array);
		
		$postdata = $this->arraytoxml->toXml($array);
		//$xxx = str_replace("&Acirc;&nbsp;", "", $postdata);		
		$search = array('&acirc;��','&acirc;��','&acirc;��','&acirc;��','&Acirc;&pound;','&Acirc;&not;','&acirc;�&cent;','&Acirc;','&nbsp;','&Acirc;&nbsp;','&lt;font color="red"&gt;new');
		$xxx = str_replace($search, "", $postdata);
			
		//echo $xxx1;
			
 		/*echo "exec('[spu_invoice_mesinsparepart]  @username=''$uid'', @xmldata=''$xxx'' ')";
		die(); 
		*/
		$uid = $this->session->userdata('DX_username');
		$qry_inv_mesin = $this->db->query("
			
			BEGIN TRY
			
			 exec('[spu_invoice_mesinsparepart]  @username=''$uid'', @xmldata=''$xxx'' ')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			");
		$rs = $qry_inv_mesin->result_array();
        //$r = $qry_inv_mesin->result();		
		//var_dump($rs);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
		
	
		
	}
	
	function proses_update_bl_mesinsparepart(){
		$this->load->library('arraytoxml');
		$uid = $this->session->userdata('DX_username');
		$postData = (isset($_POST['postData']) && !empty($_POST['postData']))?$_POST['postData']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan or not selected"}');
		$no_bl_mesinsparepart = (isset($_POST['no_bl_mesinsparepart']) && !empty($_POST['no_bl_mesinsparepart']))?$_POST['no_bl_mesinsparepart']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan or not selected"}');
		$date_bl_mesinsparepart = (isset($_POST['date_bl_mesinsparepart']) && !empty($_POST['date_bl_mesinsparepart']))?$_POST['date_bl_mesinsparepart']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan or not selected"}');
		
		$rep = array('<font color="red">', '<font color="blue">','</font>');
		$postData = str_replace($rep, "", $postData);
		
		$array = json_decode($postData);
		$array = objectToArray($array);
		
		$postdata = $this->arraytoxml->toXml($array);
		//$xxx = str_replace("&Acirc;&nbsp;", "", $postdata);		
		$search = array('&acirc;��','&acirc;��','&acirc;��','&acirc;��','&Acirc;&pound;','&Acirc;&not;','&acirc;�&cent;','&Acirc;','&nbsp;','&Acirc;&nbsp;');
		$xxx = str_replace($search, "", $postdata);
			
		
		//echo $xxx1;
			
 	 /* 	echo " exec('[spu_bl_mesinsparepart]  @username=''$uid'', @xmldata=''$xxx'',@no_bl_mesinsparepart=''$no_bl_mesinsparepart'',@date_bl_mesinsparepart=''$date_bl_mesinsparepart'' ')";
		die(); */ 
		
		$uid = $this->session->userdata('DX_username');
		$qry_inv_mesin = $this->db->query("
			
			BEGIN TRY
			
			 exec('[spu_bl_mesinsparepart]  @username=''$uid'', @xmldata=''$xxx'',@no_bl_mesinsparepart=''$no_bl_mesinsparepart'',@date_bl_mesinsparepart=''$date_bl_mesinsparepart'' ')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			");
		$rs = $qry_inv_mesin->result_array();
        //$r = $qry_inv_mesin->result();		
		//var_dump($rs);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
		
	
		
	}
	
	function proses_bc23_mesinsparepart(){
		$this->load->library('arraytoxml');
		$uid = $this->session->userdata('DX_username');
		$postData = (isset($_POST['postData']) && !empty($_POST['postData']))?$_POST['postData']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan or not selected"}');
		
		$array = json_decode($postData);
		$array = objectToArray($array);
		
		$postdata = $this->arraytoxml->toXml($array);
		//$xxx = str_replace("&Acirc;&nbsp;", "", $postdata);		
		$search = array('&acirc;��','&acirc;��','&acirc;��','&acirc;��','&Acirc;&pound;','&Acirc;&not;','&acirc;�&cent;','&Acirc;','&nbsp;'
		,'&Acirc;&nbsp;','&lt;font color="red"&gt;new&lt;/font&gt;');
		$xxx = str_replace($search, "", $postdata);
			
		//echo $xxx1;
		$typebc = 'BC23';	
 	 	
		
		 //echo "  exec('[spi_proses_mesinsparepart_bc23]  @username=''$uid'', @xmldata=''$xxx'', @typebc=''$typebc'' ') ') ";
		 //die(); 
		
		$uid = $this->session->userdata('DX_username');
		$qry_inv_mesin = $this->db->query("
			
			BEGIN TRY
			
			 exec('[spi_proses_mesinsparepart_bc23]  @username=''$uid'', @xmldata=''$xxx'', @typebc=''$typebc'' ')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			");
		$rs = $qry_inv_mesin->result_array();
        //$r = $qry_inv_mesin->result();		
		//var_dump($rs);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
		
	
	}
	
	function updatenobl(){
		$idbc23 = (isset($_POST['idbc23']) && !empty($_POST['idbc23']))?$_POST['idbc23']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan or not selected"}');
		$nobl_baru = (isset($_POST['nobl_baru']) && !empty($_POST['nobl_baru']))?$_POST['nobl_baru']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan or not selected"}');
		$uid = $this->session->userdata('DX_username');	
		/* echo " exec('[spu_update_nobl_bc23]  @username=''$uid'', @nobl_baru=''$nobl_baru'', @idbc23=$idbc23 ') ";
		die(); */
		$qry_updatebl = $this->db->query("
			BEGIN TRY
			
			 exec('[spu_update_nobl_bc23]  @username=''$uid'', @nobl_baru=''$nobl_baru'', @idbc23=$idbc23 ')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
		");
		$rs = $qry_updatebl->result_array();
        //$r = $qry_updatebl->result();		
		//var_dump($rs);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
	
	}
	
	
	function updatenocar(){
		$idbc23 = (isset($_POST['idbc23']) && !empty($_POST['idbc23']))?$_POST['idbc23']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan or not selected"}');
		$nocar_baru = (isset($_POST['nocar_baru']) && !empty($_POST['nocar_baru']))?$_POST['nocar_baru']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan or not selected"}');
		$uid = $this->session->userdata('DX_username');	
		/* echo " exec('[spu_update_nobl_bc23]  @username=''$uid'', @nobl_baru=''$nobl_baru'', @idbc23=$idbc23 ') ";
		die(); */
		if (trim($nocar_baru) == '' || strlen($nocar_baru) != 26){
			die('{"sts":"ERROR","desc":" Param Tidak Ditemukan or salah length"}');
			
		}
		$qry_updatebl = $this->db->query("
			BEGIN TRY
			
			 declare @id_bc_in_hdr bigint
			 declare @car varchar(50) 
			 declare @carbaru varchar(50), @typedok varchar(50)
			 
			 set @id_bc_in_hdr = $idbc23
			 set @carbaru = '$nocar_baru'
			  
			  if (select count(*)  from  [dbo].[tbl_data_in_hdr] where id_terkait =@id_bc_in_hdr) > 0
			  begin
				select '07' as status, ' Data Sudah Ada di gudang, silahkan cancel dahulu' as description
				return
			  end 
			  if (select count(*)  from  [dbo].[tbl_bc_in_hdr] where statusdok =  'DATA SENT TO INVENTORY' and id_bc_in_hdr = @id_bc_in_hdr) > 0
			  begin
				select '07' as status, ' Data Sudah Ada di gudang, silahkan cancel dahulu' as description
				return
			  end 

			  if (select count(*)  from  [dbo].[tbl_bc_in_hdr] where BC23No is not null and id_bc_in_hdr = @id_bc_in_hdr) > 0
			  begin
				select '07' as status, ' Data Mendapat SPPB' as description
				return
			  end 

			  select @car=CAR, @typedok=typedok FROM [dbo].[tbl_bc_in_hdr] where id_bc_in_hdr=@id_bc_in_hdr 
				
			  if (select count(*)  from  [dbo].[tbl_bc_in_hdr] where car=@car) > 0
			  begin
				select '07' as status, ' Data CAR SUDAH ADA' as description
				return
			  end 		
				
			  update [dbo].[tbl_bc_in_hdr] set CAR=@carbaru where id_bc_in_hdr = @id_bc_in_hdr and typedok=@typedok
			  update [dbo].tbl_bc_in_dtl set CAR=@carbaru where id_bc_in_hdr = @id_bc_in_hdr
			  update [dbo].[tbl_bc_in_dok] set CAR=@carbaru where id_bc_in_hdr = @id_bc_in_hdr
			  update [dbo].[tbl_bc_in_con] set CAR=@carbaru where id_bc_in_hdr = @id_bc_in_hdr
			  
			  select '00' as status, 'CAR UPDATED, TUTUP DULU MENU INI' as description
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
		");
		$rs = $qry_updatebl->result_array();
        //$r = $qry_updatebl->result();		
		//var_dump($rs);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
	
	}
	
	function view_bc23_kurir_load(){
		
		$data['xxx'] = 'xxx';
		$this->load->view('exim/view_bc23_kurir_load', $data);		
	
	}
	
	function proses_kurirbc23(){
		$id_kurir_upload = (isset($_POST['id_kurir_upload']) && !empty($_POST['id_kurir_upload']))?$_POST['id_kurir_upload']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$uid = $this->session->userdata('DX_username');
		
		echo "  exec('[spi_kurir] @id_kurir_upload = $id_kurir_upload, @tipe=''kurir'', @username=''$uid'' ') "; 
		die();
		$qry_inv_mesin = $this->db->query("
			
			BEGIN TRY
			
			 exec('[spi_kurir] @id_kurir_upload = $id_kurir_upload, @tipe=''kurir'', @username=''$uid'' ')
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			");
		$rs = $qry_inv_mesin->result_array();
        //$r = $qry_inv_mesin->result();		
		//var_dump($rs);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
	
	
	
	}
	
	function view_bc23_search(){
	
		$this->load->view('exim/view_bc23_search');
	
	}
	function update_no_tgl_bc23_manual(){
		$id_bc_in_hdr = (isset($_POST['id_bc_in_hdr']) && !empty($_POST['id_bc_in_hdr']))?$_POST['id_bc_in_hdr']:die('{"sts":"ERROR","desc":" Param ID Tidak Ditemukan"}');
		$car = (isset($_POST['car']) && !empty($_POST['car']))?$_POST['car']:die('{"sts":"ERROR","desc":" Param car Tidak Ditemukan"}');
		$tgdaft = (isset($_POST['tgdaft']) && !empty($_POST['tgdaft']))?$_POST['tgdaft']:die('{"sts":"ERROR","desc":" Param car Tidak Ditemukan"}');
		$nodaft = (isset($_POST['nodaft']) && !empty($_POST['nodaft']))?$_POST['nodaft']:die('{"sts":"ERROR","desc":" Param car Tidak Ditemukan"}');
		
		$in_inv = $this->db->query("
			
			BEGIN TRY
				update [dbo].[tbl_bc_in_hdr] set [CAR] = '$car', [BC23No]='$nodaft', [BC23Tg]='$tgdaft' where id_bc_in_hdr=$id_bc_in_hdr	
				declare @bc_in_from_in varchar(10) 
			   set @bc_in_from_in = (select bc_in_from_in from tbl_bc_in_hdr where id_bc_in_hdr = $id_bc_in_hdr)
			   if @bc_in_from_in = 'COO' 
			   begin
				update tbl_coo_data set [car] = '$car',  [nobc] = '$nodaft',  [tglbc] = '$tgdaft' where [id_bc_in_hdr] = $id_bc_in_hdr
			   end	
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			select '00' as status, ' SUKSES UPDATED' as description
			
		");
		$rs = $in_inv->result_array();
        //$r = $in_inv->result();		
		//var_dump($r);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
	
	}
	
	function view_coo_bc23_ceisa(){
		$this->mysql =$this->load->database('mysql', TRUE);	
		
		$id_bc_in_hdr = (isset($_POST['id_bc23_hdr_ceisa']) && !empty($_POST['id_bc23_hdr_ceisa']))?$_POST['id_bc23_hdr_ceisa']:die('{"sts":"ERROR","desc":" Param ID Tidak Ditemukan"}');
		$q = $this->db->query("select car, dokno, convert(char(10),doktg,126) as doktg from tbl_bc_in_dok where id_bc_in_hdr = $id_bc_in_hdr and dokkd='380'  ");
		
		$q_last_car = $this->mysql->query("
			select NOMOR_AJU from tpb_header where KODE_DOKUMEN_PABEAN = '23'  and SUBSTR(NOMOR_AJU, 21, 6) < 5000 order by NOMOR_AJU desc limit 1
		");
		$last_car = "";
		foreach($q_last_car->result() as $r){
			$last_car = $r->NOMOR_AJU;
		}
		$no_inv = "";
		$pre = "";
		$carr = "";
		foreach($q->result() as $r){
			$pre = "<br />";
			$no_inv .= $r->dokno . " # " . $r->doktg . $pre;
		}
		
		$q_car = $this->db->query("
			select car from tbl_bc_in_hdr where id_bc_in_hdr = $id_bc_in_hdr
		");
		foreach ($q_car->result() as $rcar){
			$carr = $rcar->car;
		}
		$no_reg = substr($last_car,0,12);
		$aju_last = substr($last_car,-6);
		$aju_next = sprintf('%06d', ((int) $aju_last + 1));
		$next_car = $no_reg . date("Ymd") . $aju_next; 
		$data['id_bc_in_hdr'] = $id_bc_in_hdr;
		$data['no_inv'] = $no_inv;
		$data['last_car'] = $last_car;
		$data['next_car'] = $next_car;
		$data['car'] = $carr;
		$this->load->view('exim/view_coo_bc23_ceisa', $data);
	
	}
	
	function proses_bc23_ceisa(){
		$this->mysql =$this->load->database('mysql', TRUE);	
		$id_bc_in_hdr = (isset($_POST['id_bc_in_hdr']) && !empty($_POST['id_bc_in_hdr']))?$_POST['id_bc_in_hdr']:die('{"sts":"ERROR","desc":" Param ID Tidak Ditemukan"}');
		
		$q = $this->db->query("select dokno, convert(char(8),doktg,103) as doktg from tbl_bc_in_dok where id_bc_in_hdr = $id_bc_in_hdr and dokkd='380'  ");
		
		$q_last_car = $this->mysql->query("
			select NOMOR_AJU from tpb_header  where KODE_DOKUMEN_PABEAN = '23'  and SUBSTR(NOMOR_AJU, 21, 6) < 5000 order by NOMOR_AJU desc limit 1
		");
		$last_car = "";
		foreach($q_last_car->result() as $r){
			$last_car = $r->NOMOR_AJU;
		}
		
		$ID_MODUL = (int)substr($last_car, 6,6);
		
		$no_inv = "";
		$pre = "";
		foreach($q->result() as $r){
			$pre = "<br />";
			$no_inv .= $r->dokno . " # " . $r->doktg . $pre;
			
		}
		$no_reg = substr($last_car,0,12);
		$aju_last = substr($last_car,-6);
		$aju_next = sprintf('%06d', ((int) $aju_last + 1));
		$next_car = $no_reg . date("Ymd") . $aju_next; 
		
		$my_ref_pengusaha = $this->mysql->query("
			SELECT
			referensi_pengusaha.ID,
			referensi_pengusaha.ALAMAT,
			referensi_pengusaha.CONTACT_PERSON,
			referensi_pengusaha.EMAIL,
			referensi_pengusaha.FAX,
			referensi_pengusaha.ID_PENGENAL,
			referensi_pengusaha.JENISTPB,
			referensi_pengusaha.KODE_ID,
			referensi_pengusaha.KODE_KANTOR,
			referensi_pengusaha.NAMA,
			referensi_pengusaha.NOMOR_PENGENAL,
			referensi_pengusaha.NOMOR_SKEP,
			referensi_pengusaha.NPWP,
			referensi_pengusaha.STATUS_IMPORTIR,
			referensi_pengusaha.TANGGAL_SKEP,
			referensi_pengusaha.TELEPON
			FROM
			referensi_pengusaha limit 1
			
		");
		$ALAMAT = "";
		$KODE_ID = "";
		$NAMA = "";
		$NOMOR_SKEP = "";
		$TANGGAL_SKEP = "";
		$NPWP = "";
		$ID = "";
		foreach ($my_ref_pengusaha->result() as $r){
			$ALAMAT = $r->ALAMAT;
			$KODE_ID = $r->KODE_ID;
			$NAMA = $r->NAMA;
			$NOMOR_SKEP = $r->NOMOR_SKEP;
			$TANGGAL_SKEP = $r->TANGGAL_SKEP;
			$NPWP = $r->NPWP;
			$ID = $r->ID;
		}
		
		
		$q_bc23_hdr=$this->db->query("
			select
			  id_bc_in_hdr
			  ,[CAR]
			  ,[KdKpbc]
			  ,[Tujuan]
			  ,[JnsBarang]
			  ,[TujuanKirim]
			  ,[PasokNama]
			  ,[PasokAlmt]
			  ,[PasokNeg]
			  ,[UsahaId]
			  ,[UsahaNpwp]
			  ,[KdTPB]
			  ,[Registrasi]
			  ,[UsahaNama]
			  ,[UsahaAlmt]
			  ,[UsahaStatus]
			  ,[ApiKd]
			  ,[ApiNo]
			  ,[PpjkId]
			  ,[PpjkNpwp]
			  ,[PpjkNama]
			  ,[PpjkAlmt]
			  ,[PpjkNo]
			  ,[PpjkTg]
			  ,[IndId]
			  ,[IndNpwp]
			  ,[IndNama]
			  ,[IndAlmt]
			  ,[Moda]
			  ,[AngkutNama]
			  ,[AngkutNo]
			  ,[AngkutFl]
			  ,[PelBkr]
			  ,[PelMuat]
			  ,[PelTransit]
			  ,[BC23No]
			  ,CONVERT(varchar, [BC23Tg], 126) as BC23Tg
			  ,[KdKpbcBongkar]
			  ,[KdKpbcAwas]
			  ,[DokTupKd]
			  ,[DokTupNo]
			  ,CONVERT(varchar, [DokTupTg], 126) as DokTupTg 
			  ,[PosNo]
			  ,[PosSub]
			  ,[TmpTbn]
			  ,[KdVal]
			  ,[Ndpbm]
			  ,[NilInv]
			  ,[Freight]
			  ,[BTambahan]
			  ,[Diskon]
			  ,[KdAss]
			  ,[Asuransi]
			  ,[KdHrg]
			  ,[Fob]
			  ,[Cif]
			  ,[Bruto]
			  ,[Netto]
			  ,[JmCont]
			  ,[JmBrg]
			  ,[TglTtd]
			  ,[KotaTtd]
			  ,[Namattd]
			  ,[ContakPerson]
			  ,[NoPhone]
			  ,[NoFax]
			  ,[Email]
			  ,[Status]
			  ,[Snrf]
			  ,[Lengkap]
			  ,[PosSubSub]
			  ,[NoKontrak]
			  ,[TgKontrak]
			  ,[HargaRp]
			  ,[TmpPeriksaFisik]
			  ,[AlmtPeriksaFisik]
			  ,[TgPeriksaFisik]
			  ,[WkPeriksaFisik]
			  ,(case when (isnull(isnull(create_by, update_by), (select top 1 [createby] FROM [dbo].[v_bc_in_hdr_h_status] where id_bc_in_hdr=[tbl_bc_in_hdr].id_bc_in_hdr))) = 'admin' then 'nurhadi' else (isnull(isnull(create_by, update_by), (select top 1 [createby] FROM [dbo].[v_bc_in_hdr_h_status] where id_bc_in_hdr=[tbl_bc_in_hdr].id_bc_in_hdr))) end) as create_by
			  ,(case when (isnull(isnull(create_by, update_by), (select top 1 [createby] FROM [dbo].[v_bc_in_hdr_h_status] where id_bc_in_hdr=[tbl_bc_in_hdr].id_bc_in_hdr))) = 'admin' then 'nurhadi' else (isnull(isnull(create_by, update_by),  (select top 1 [createby] FROM [dbo].[v_bc_in_hdr_h_status] where id_bc_in_hdr=[tbl_bc_in_hdr].id_bc_in_hdr))) end) as update_by
			   ,convert(varchar, isnull(isnull(create_date, update_date),  (select top 1 create_date  FROM [dbo].[v_bc_in_hdr_h_status]  where id_bc_in_hdr=[tbl_bc_in_hdr].id_bc_in_hdr)), 103) as create_date
	           ,convert(varchar, isnull(isnull(create_date, update_date),  (select top 1 create_date  FROM [dbo].[v_bc_in_hdr_h_status]  where id_bc_in_hdr=[tbl_bc_in_hdr].id_bc_in_hdr)), 103) as update_date
			  ,[typedok]
			  ,[statusdok]
			  ,[noinv]
			  ,CONVERT(varchar, [tginv], 126) as tglinv
			  ,inv_hdr_id
			  ,nobl
			  ,CONVERT(varchar, [tglbl], 126) as tglbl
			  ,bc_in_from_in
			  ,CONVERT(varchar, tgaju, 126) as tgaju
			  from 
			  tbl_bc_in_hdr
			  where  id_bc_in_hdr = $id_bc_in_hdr
		");
		
		
		$hdr_array = array();
		foreach($q_bc23_hdr->result() as $r){
			$kode_harga = 'FOB';
			if ($r->KdHrg == '1'){
				$kode_harga = 'CIF';
			}  else if ($r->KdHrg == '2'){
				$kode_harga = 'CNF';
			}
			$KODE_KANTOR_BONGKAR = '050300';
			if($r->Moda == '4'){
				$KODE_KANTOR_BONGKAR = '050100';
			} else if ($r->Moda == '1'){
				$KODE_KANTOR_BONGKAR = '040300';
			}
			
			$hdr_array[] = array(
				"NOMOR_AJU" => $next_car,
				"KODE_KANTOR" => "050100",
				"KODE_TUJUAN_TPB" => "1",
				"NAMA_PEMASOK" => $r->PasokNama,
				"ALAMAT_PEMASOK" => $r->PasokAlmt,
				"KODE_NEGARA_PEMASOK" => $r->PasokNeg,
				"KODE_JENIS_API_PENGUSAHA" => $r->ApiKd,
				"KODE_CARA_ANGKUT" => $r->Moda,
				"NAMA_PENGANGKUT" => $r->AngkutNama,
				"NOMOR_VOY_FLIGHT" => $r->AngkutNo,
				"KODE_BENDERA" => $r->AngkutFl,
				"KODE_KANTOR_BONGKAR" => $KODE_KANTOR_BONGKAR,
				"KODE_PEL_MUAT" => $r->PelMuat,
				"KODE_PEL_TRANSIT" => $r->PelTransit,
				"NOMOR_DAFTAR" => $r->BC23No,
				"TANGGAL_DAFTAR" => $r->BC23Tg,
				"KODE_PEL_BONGKAR" => $r->PelBkr,
				"KODE_KANTOR" => $r->KdKpbcAwas,
				"NOMOR_BC11" => $r->DokTupNo,
				"TANGGAL_BC11" => $r->DokTupTg,
				"POS_BC11" => $r->PosNo,
				"SUBPOS_BC11" => $r->PosSub,
				"KODE_TPS" => $r->TmpTbn,
				"KODE_VALUTA" => $r->KdVal,
				"NDPBM" => $r->Ndpbm,
				"HARGA_INVOICE" => $r->NilInv,
				"FREIGHT" => $r->Freight,
				"BIAYA_TAMBAHAN" => $r->BTambahan,
				"DISKON" => $r->Diskon,
				"KODE_ASURANSI" => $r->KdAss,
				"ASURANSI" => $r->Asuransi,
				"KODE_HARGA" => $kode_harga, //$r->KdHrg,
				"FOB" => $r->Fob,
				"CIF" => $r->Cif,
				"BRUTO" => $r->Bruto,
				"NETTO" => $r->Netto,
				"JUMLAH_KONTAINER" => $r->JmCont,
				"JUMLAH_BARANG" => $r->JmBrg,
				"TANGGAL_TTD" => Date("Y-m-d"),
				"KOTA_TTD" => $r->KotaTtd,
				"NAMA_TTD" => $r->Namattd,
				"KODE_DOKUMEN_PABEAN" => "23", 
				"JABATAN_TTD" =>"KUASA DIREKSI",
				"API_PEMILIK" => $r->ApiNo,
				"API_PENGUSAHA" => $r->ApiNo,
				"KODE_STATUS" => "00",
				"VERSI_MODUL" => "3.1.5",
				"ID_MODUL" => $ID_MODUL,
				
				"ALAMAT_PENGUSAHA" => $ALAMAT,
				"KODE_ID_PENGUSAHA" => $ID,
				"ID_PENGUSAHA" => $NPWP,
				"NAMA_PENGUSAHA" => $NAMA,
				"NOMOR_IJIN_TPB" => $NOMOR_SKEP,
				
				"JUMLAH_KEMASAN" => "1"
				
				//"ID_PEMILIK" => $r->UsahaNpwp,
				//"PERUSAHAAN" => $r->UsahaNama,
				//"ALAMAT_PEMILIK" => $r->UsahaAlmt,
				
				
				
			);
		}
		
		$q_bc23_dtl=$this->db->query("
			select
			 [id_bc_in_dtl]
			  ,[id_bc_in_hdr]
			  ,[CAR]
			  ,[Serial]
			  ,[JnsBarangDtl]
			  ,[TujuanKirimDtl]
			  ,[NoHs]
			  ,[SeriTrp]
			  ,[BrgUrai]
			  ,[Merk]
			  ,[Tipe]
			  ,[SpfLain]
			  ,[KdBrg]
			  ,[Penggunaan]
			  ,[KemasJn]
			  ,[KemasJm]
			  ,[KdFasDtl]
			  ,[BrgAsal]
			  ,[DNilInv]
			  ,[DCif]
			  ,[KdSat]
			  ,[JmlSat]
			  ,[NettoDtl]
			  ,[SatBmJm]
			  ,[SatCukJm]
			  ,[DtlOk]
			  ,[create_date]
			  ,[create_by]
			  ,[update_date]
			  ,[update_by]
			  from 
			  tbl_bc_in_dtl
			  where  id_bc_in_hdr = $id_bc_in_hdr
		");
		
		
		$tpb_barang = array();
		foreach($q_bc23_dtl->result() as $r){
			$HARGA_SATUAN = (float)$r->DNilInv / (float)$r->JmlSat;
			$tpb_barang[] = array(
				"KODE_BARANG" => $r->KdBrg,
				"TIPE" => $r->Tipe,
				"URAIAN" => $r->BrgUrai,
				"NETTO" => $r->NettoDtl,
				"KODE_SATUAN" => $r->KdSat,
				"JUMLAH_SATUAN" => $r->JmlSat,
				"JUMLAH_KEMASAN" => $r->KemasJm,
				"SERI_BARANG" => $r->Serial,
				"HARGA_INVOICE" => $r->DNilInv,
				"CIF" => $r->DNilInv,
				"FOB" => $r->DNilInv,
				"HARGA_SATUAN" => $HARGA_SATUAN,
				"KODE_NEGARA_ASAL" => $r->BrgAsal,
				"POS_TARIF" => $r->NoHs,
				"KATEGORI_BARANG" => "11",
				"KODE_KEMASAN" => $r->KemasJn,
				"ASURANSI" => "0.00",
				"KODE_STATUS" => "02"
				
			);
		}	
		
		//var_dump($tpb_barang);
		//die();
		
		$q_bc23_dok=$this->db->query("
			select
			 [id_bc_in_dok]
		 	 ,[id_bc_in_hdr]
			  ,[CAR]
		 	 ,[DokKd]
		 	 ,[DokNo]
		 	 ,CONVERT(varchar, [DokTg], 126) as DokTg
			  from 
			  tbl_bc_in_dok
			  where  id_bc_in_hdr = $id_bc_in_hdr 
		");
		
		
		$tpb_dokumen = array();
		$i = 1;
		foreach($q_bc23_dok->result() as $r){
			$tpb_dokumen[] = array(
				"KODE_JENIS_DOKUMEN" => $r->DokKd,
				"NOMOR_DOKUMEN" => $r->DokNo,
				"TANGGAL_DOKUMEN" => $r->DokTg,
				"TIPE_DOKUMEN" => "02",
				"SERI_DOKUMEN" => $i
				
			);
			$i++;
		}	
		
		
		$q_bc23_kms=$this->db->query("
			select [id_bc_in_kms]
		      ,[id_bc_in_hdr]
		      ,[CAR]
		      ,[JnKemas]
		      ,[JmKemas]
		      ,[merkkemas]
		     from [tbl_bc_in_kms] 
			  where  id_bc_in_hdr = $id_bc_in_hdr 
		");
		
		
		$tpb_kms = array();
		$i = 1;
		foreach($q_bc23_kms->result() as $r){
			$tpb_kms[] = array(
				"JUMLAH_KEMASAN" => $r->JmKemas,
				"KODE_JENIS_KEMASAN" => $r->JnKemas,
				"MERK_KEMASAN" => $r->merkkemas
				
			);
			$i++;
		}	
		
		$this->mysql->trans_start();
		$msg = $this->mysql->insert('tpb_header', $hdr_array[0]);
		$insert_id = $this->mysql->insert_id();
		$this->mysql->trans_complete();
		
		for ($x = 0; $x <= count($tpb_barang) - 1; $x++ ){
			$this->mysql->trans_start();
			$this->mysql->insert("tpb_barang", array_merge($tpb_barang[$x], array("ID_HEADER" => $insert_id)));
			$insert_id_dtl = $this->mysql->insert_id();
			$this->mysql->trans_complete();
			
		}
		
		for ($x = 0; $x <= count($tpb_dokumen) - 1; $x++ ){
			$this->mysql->insert("tpb_dokumen", array_merge($tpb_dokumen[$x], array("ID_HEADER" => $insert_id)));
		}
		for ($x = 0; $x <= count($tpb_kms) - 1; $x++ ){
			$this->mysql->insert("tpb_kemasan", array_merge($tpb_kms[$x], array("ID_HEADER" => $insert_id)));
		}
		
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		
		$q=$this->db->query("
			
			declare @KODE_FASILITAS varchar(50), @KODE_TARIF varchar(50)
			declare @SERI_BARANG bigint, @TARIF decimal(38, 2), @TARIF_FASILITAS decimal(38, 2)
			declare @ID_BARANG bigint, @ID_HEADER bigint, @POS_TARIF varchar(50)

			DECLARE db_cursor CURSOR FOR
			select ID, ID_HEADER, SERI_BARANG, POS_TARIF  from OPENQUERY(TPBDB, 'select * from tpb_barang') where id_header = $insert_id
			OPEN db_cursor
				FETCH NEXT FROM db_cursor INTO @ID_BARANG, @ID_HEADER , @SERI_BARANG, @POS_TARIF
				WHILE @@FETCH_STATUS = 0
				BEGIN
					if (select count(*) from OPENQUERY(TPBDB, 'select * from tpb_barang_tarif') where id_header = @ID_HEADER and id_barang=@ID_BARANG and SERI_BARANG=@SERI_BARANG ) = 0
					begin
						insert into OPENQUERY(TPBDB, 'select JENIS_TARIF, KODE_FASILITAS, KODE_TARIF, SERI_BARANG, TARIF, 
						TARIF_FASILITAS, ID_BARANG, ID_HEADER from tpb_barang_tarif')
						select top 1 'BM', '2', '1', @SERI_BARANG, TARIF_BM, 100.00, @ID_BARANG, @ID_HEADER  
						from OPENQUERY(TPBDB, 'select * from referensi_pos_tarif') where nomor_hs = @POS_TARIF
						
						insert into OPENQUERY(TPBDB, 'select JENIS_TARIF, KODE_FASILITAS, KODE_TARIF, SERI_BARANG, TARIF, 
						TARIF_FASILITAS, ID_BARANG, ID_HEADER from tpb_barang_tarif')
						select top 1 'PPN', '5', NULL, @SERI_BARANG, TARIF_PPN, 100.00, @ID_BARANG, @ID_HEADER  
						from OPENQUERY(TPBDB, 'select * from referensi_pos_tarif') where nomor_hs = @POS_TARIF
						
						insert into OPENQUERY(TPBDB, 'select JENIS_TARIF, KODE_FASILITAS, KODE_TARIF, SERI_BARANG, TARIF, 
						TARIF_FASILITAS, ID_BARANG, ID_HEADER from tpb_barang_tarif')
						select top 1 'PPH', '5', NULL, @SERI_BARANG, TARIF_PPH, 100.00, @ID_BARANG, @ID_HEADER  
						from OPENQUERY(TPBDB, 'select * from referensi_pos_tarif') where nomor_hs = @POS_TARIF
					 



					end 
				FETCH NEXT FROM db_cursor INTO @ID_BARANG, @ID_HEADER , @SERI_BARANG, @POS_TARIF
				END
			CLOSE db_cursor
			DEALLOCATE db_cursor
		");
		
		
		if( !$msg )
		{
		   $errNo   = $this->mysql->_error_number();
		   $errMess = $this->mysql->_error_message();
		   echo json_encode(array('sts' => $errNo, 'desc' => $errMess));
		   die();		
		}
		$stsa = '00';
		$msga = $msg . ' DATA CREATED..';
		if ($stsa != 1){
			$status = '06';
			$msg = $stsa;
		}
		
		$q=$this->db->query("
			update tbl_bc_in_hdr set CAR='$next_car',  statusdok = 'DATA PROCESSED' where id_bc_in_hdr = $id_bc_in_hdr
		");
		
		echo json_encode(array('sts' => $stsa, 'desc' => $msga, 'insert_id' => $insert_id ));	
	}
	
	function view_bc23_matching_ceisa(){
		$data["param"] = "";
		$this->load->view('exim/view_bc23_matching_ceisa', $data);
	}
	
	function datamodul_detil_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	
		
		$tglawalbc23_ceisacar = (isset($_POST['tglawalbc23_ceisacar']) && !empty($_POST['tglawalbc23_ceisacar']))?$_POST['tglawalbc23_ceisacar']:date('Y-m-d');
		$tglakhirbc23_ceisacar = (isset($_POST['tglakhirbc23_ceisacar']) && !empty($_POST['tglakhirbc23_ceisacar']))?$_POST['tglakhirbc23_ceisacar']:date('Y-m-d');
		
		$car = (isset($_POST['car']) && !empty($_POST['car']))?$_POST['car']:'';
		
		$where = " 1=1 ";
		
		$where = " CAST(SUBSTRING(a.NOMOR_AJU,21,6) as int) < 500000 and c.KODE_DOKUMEN = '23' and  a.KODE_DOKUMEN_PABEAN ='23' and  convert(varchar(10),a.TANGGAL_AJU,126) between '$tglawalbc23_ceisacar' and '$tglakhirbc23_ceisacar' ";
		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 a.NOMOR_AJU like  '%".$searchString."%'"; 
					} else if ( $k->field == "carx" ) {
						$searchString = $k->data;
						$where .= " and b.car like  '%".$searchString."%'"; 
					}  
				}
			}
		}
		
		
		if (!empty($car)){
			
			$where = " CAST(SUBSTRING(a.NOMOR_AJU,21,6) as int) < 500000 and c.KODE_DOKUMEN = '23' and a.NOMOR_AJU like '%$car%' and a.KODE_DOKUMEN_PABEAN ='23'  ";
		}
		
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		
		$query = $this->db->query("
			
			select count(*) as cnt from openquery(TPBDB,'select * from tpb_header') a
			left outer join tbl_bc_in_hdr on a.NOMOR_AJU = tbl_bc_in_hdr.CAR
			inner join openquery(TPBDB,'select * from referensi_status')   c on c.KODE_STATUS = a.KODE_STATUS
			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->db->query("
			select top $limit car, convert(varchar(10), xdate, 126) as xdate,  carx, 
			sts,
			jnsbarang,NOMOR_DAFTAR,TANGGAL_DAFTAR,id_bc_in_hdr, ID,
			URAIAN_STATUS
			 from (
				select TOP $batas a.NOMOR_AJU as CAR, a.TANGGAL_AJU as xdate
				,b.car as carx, 
				a.KODE_STATUS as sts, a.KODE_DOKUMEN_PABEAN as jnsbarang 
				,a.NOMOR_DAFTAR as NOMOR_DAFTAR, CONVERT(varchar(10),a.TANGGAL_DAFTAR, 126) as TANGGAL_DAFTAR
				,b.id_bc_in_hdr ,a.ID as ID, c.URAIAN_STATUS
				from openquery(TPBDB,'select * from tpb_header') a 
				inner join openquery(TPBDB,'select * from referensi_status') c on c.KODE_STATUS = a.KODE_STATUS
				left outer join dbo.tbl_bc_in_hdr b on a.NOMOR_AJU = b.car
				where $where
				order by $sidx desc
			)  t1 Order By car $sord
			
			"); 
		$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]["CAR"]=$r["car"];
			$responce->rows[$i]["carx"]=$r["carx"];
			$responce->rows[$i]["xdate"]=$r["xdate"];
	    	$responce->rows[$i]["NOMOR_DAFTAR"]=$r["NOMOR_DAFTAR"];
			$responce->rows[$i]["TANGGAL_DAFTAR"]=$r["TANGGAL_DAFTAR"]; 
			$responce->rows[$i]["sts"]=$r["sts"];
			$responce->rows[$i]["jnsbarang"]=$r["jnsbarang"];
			$responce->rows[$i]["id_bc_in_hdr"]=$r["id_bc_in_hdr"];
			$responce->rows[$i]["ID"]=$r["ID"];
			$responce->rows[$i]["URAIAN_STATUS"]=$r["URAIAN_STATUS"];
			$i++;
			
		}
		
		echo json_encode($responce);
		die();
	}
	
	function proses_get_bc23_ceisa(){
		$id_bc_in_hdr = (isset($_POST['id_bc_in_hdr']) && !empty($_POST['id_bc_in_hdr']))?$_POST['id_bc_in_hdr']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$username=$this->session->userdata('DX_username');
		
		
		
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		//echo " exec('[sp_get_from_module_bc23]  @car =''$car'', @username=''$username'', @conn=''$bc23dir'', @dok_in_from_in=''HIB'' ') "; die();
		$matchcar = $this->db->query("
			declare @car varchar(50)
			declare @NOMOR_DAFTAR varchar(50)
			declare @TANGGAL_DAFTAR date
			
			set @car = (select car from tbl_bc_in_hdr where id_bc_in_hdr = $id_bc_in_hdr) 
			
			BEGIN TRY
			if	(select 
				count(*)	
				from openquery(TPBDB,'select * from tpb_header')
				where NOMOR_AJU = @car and KODE_DOKUMEN_PABEAN ='23') <> 1
			begin
				SELECT 
				'06' AS status
				,' CAR TIDAK DITEMUKAN ATAU LEBIH DARI SATU NOMOR AJU SAMA, GUNAKAN TARIK MANUAL' AS description
				return
			end	
			
			select 
				@NOMOR_DAFTAR=NOMOR_DAFTAR,
				@TANGGAL_DAFTAR=TANGGAL_DAFTAR	
			from openquery(TPBDB,'select * from tpb_header')
			where NOMOR_AJU = @car and KODE_DOKUMEN_PABEAN ='23'	
			
			update tbl_bc_in_hdr set BC23No=@NOMOR_DAFTAR, BC23Tg=@TANGGAL_DAFTAR where id_bc_in_hdr = $id_bc_in_hdr  
			update tbl_coo_data set [car]=@car, [nobc]=@NOMOR_DAFTAR, [tglbc]=@TANGGAL_DAFTAR where  id_bc_in_hdr = $id_bc_in_hdr
			SELECT 
				'00' AS status
				,' NO DAFTAR : ' + @NOMOR_DAFTAR AS description;
			END TRY
			BEGIN CATCH 
				SELECT 
				'06' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			");
			
		$rs = $matchcar->result_array();
        //$r = $matchcar->result();		
		//var_dump($rs);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
	}
	
	function proses_get_replace_bc23_ceisa(){
		$car = (isset($_POST['carproses_matchbc23_ceisa']) && !empty($_POST['carproses_matchbc23_ceisa']))?$_POST['carproses_matchbc23_ceisa']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$id_hdr_ceisa = (isset($_POST['id_hdr_ceisa']) && !empty($_POST['id_hdr_ceisa']))?$_POST['id_hdr_ceisa']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$id_bc_in_hdr = (isset($_POST['id_bc_in_hdr']) && !empty($_POST['id_bc_in_hdr']))?$_POST['id_bc_in_hdr']:'';
		
		$username=$this->session->userdata('DX_username');
		
		
		
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		if ($id_bc_in_hdr != ''){
			$matchcar = $this->db->query("
				declare @car varchar(50)
				declare @NOMOR_DAFTAR varchar(50)
				declare @TANGGAL_DAFTAR date
				
				set @car = (select car from tbl_bc_in_hdr where id_bc_in_hdr = $id_bc_in_hdr) 
				
				BEGIN TRY
				if	(select 
					count(*)	
					from openquery(TPBDB,'select * from tpb_header')
					where NOMOR_AJU = @car and KODE_DOKUMEN_PABEAN ='23' and ID = $id_hdr_ceisa) <> 1
				begin
					SELECT 
					'06' AS status
					,' CAR TIDAK DITEMUKAN ATAU LEBIH DARI SATU NOMOR AJU SAMA, GUNAKAN TARIK MANUAL' AS description
					return
				end	
				
				select 
					@NOMOR_DAFTAR=NOMOR_DAFTAR,
					@TANGGAL_DAFTAR=TANGGAL_DAFTAR	
				from openquery(TPBDB,'select * from tpb_header')
				where NOMOR_AJU = @car and KODE_DOKUMEN_PABEAN ='23'	
				
				update tbl_bc_in_hdr set BC23No=@NOMOR_DAFTAR, BC23Tg=@TANGGAL_DAFTAR where id_bc_in_hdr = $id_bc_in_hdr  
				update tbl_coo_data set [car]=@car, [nobc]=@NOMOR_DAFTAR, [tglbc]=@TANGGAL_DAFTAR where  id_bc_in_hdr = $id_bc_in_hdr
				SELECT 
					'00' AS status
					,' NO DAFTAR : ' + @NOMOR_DAFTAR AS description;
				END TRY
				BEGIN CATCH 
					SELECT 
					'06' AS status
					,ERROR_MESSAGE() AS description;
					
				END CATCH;
				
			");
		} else {
			$matchcar = $this->db->query("
				declare @car varchar(50)
				declare @NOMOR_DAFTAR varchar(50)
				declare @TANGGAL_DAFTAR date
				set @car = '$car'				
				BEGIN TRY
				if	(select 
					count(*)	
					from openquery(TPBDB,'select * from tpb_header')
					where NOMOR_AJU = @car and KODE_DOKUMEN_PABEAN ='23' and ID = $id_hdr_ceisa) <> 1
				begin
					SELECT 
					'06' AS status
					,' CAR TIDAK DITEMUKAN ATAU LEBIH DARI SATU NOMOR AJU SAMA, GUNAKAN TARIK MANUAL' AS description
					return
				end	
				declare @tbl_id_coo table(id_coo bigint not null)
				
				declare @TBLKATEGORI_BARANG Table (KATEGORI_BARANG varchar(50) null) 
				insert into @TBLKATEGORI_BARANG (
					KATEGORI_BARANG
				)
				select 
					KATEGORI_BARANG
				from openquery(TPBDB,'select * from tpb_barang')
				where ID_HEADER = $id_hdr_ceisa
				group by KATEGORI_BARANG
				
				declare @tbl_kode_brg table(kode_brg varchar(50))
				insert into @tbl_kode_brg (
					kode_brg
				)
				select 
					KODE_BARANG
				from openquery(TPBDB,'select * from tpb_barang')
				where ID_HEADER = $id_hdr_ceisa
				group by KODE_BARANG
				
				DECLARE @tblno_inv table(nomor varchar(50) null, tgl date)
				insert into  @tblno_inv(
					nomor
					,tgl
				) select
				NOMOR_DOKUMEN
				,TANGGAL_DOKUMEN
				from openquery(TPBDB,'select * from tpb_dokumen')
				where ID_HEADER = $id_hdr_ceisa and KODE_JENIS_DOKUMEN = '380'

				------CEK DATA -----
				declare @jnsbrg varchar(30)
				if (select count(*) from @TBLKATEGORI_BARANG where KATEGORI_BARANG is not null) = 1
				begin
					declare @katbrg varchar(50) 
					DECLARE db_cursor_kat CURSOR FOR 
					select KATEGORI_BARANG from @TBLKATEGORI_BARANG where KATEGORI_BARANG is not null					
					OPEN db_cursor_kat 
					FETCH NEXT FROM db_cursor_kat INTO @katbrg
					WHILE @@FETCH_STATUS = 0  
					BEGIN 
						if @katbrg = '11' 
						begin
							if (select count(*) from [tbl_kode_barang] b inner join @tbl_kode_brg a on a.kode_brg = b.kode_barang) > 0
							begin
								declare @x_inv_no varchar(50), @x_inv_tgl date, @null_inv_no varchar(max), @xno_invoice varchar(50)
								set @null_inv_no = ''
								DECLARE db_cursor CURSOR FOR 
								select a.nomor, a.tgl, [no_invoice] from @tblno_inv a
								left outer join [tbl_coo_data] on a.nomor=[tbl_coo_data].no_invoice   where nomor is not null and [no_invoice] is null
								OPEN db_cursor  
								FETCH NEXT FROM db_cursor INTO @x_inv_no,  @x_inv_tgl, @xno_invoice
								WHILE @@FETCH_STATUS = 0  
								BEGIN  
									set @null_inv_no = @null_inv_no + @x_inv_no + '  '
									FETCH NEXT FROM db_cursor INTO @x_inv_no,  @x_inv_tgl, @xno_invoice  
								END  

								CLOSE db_cursor  
								DEALLOCATE db_cursor
					
								if dbo.trim(@null_inv_no) <> ''
								begin
									select '06' as status, @null_inv_no + ' INVOICE COO TIDAK DITEMUKAN'  as description
									RETURN
								end
								declare @id_coo bigint
								DECLARE db_cursor CURSOR FOR 
								select id_coo_data from @tblno_inv a
								left outer join [tbl_coo_data] on a.nomor=[tbl_coo_data].no_invoice   where nomor is not null and [no_invoice] is not null
								OPEN db_cursor  
								FETCH NEXT FROM db_cursor INTO @id_coo
								WHILE @@FETCH_STATUS = 0  
								BEGIN  
									insert into @tbl_id_coo (id_coo) values (@id_coo)
									FETCH NEXT FROM db_cursor INTO @id_coo 
								END  

								CLOSE db_cursor  
								DEALLOCATE db_cursor
								set @jnsbrg = 'PART'
							end
							else 
							begin
								set @jnsbrg = 'NONCOO'
							end
						end
						if @katbrg = '12' 
						begin
							set @jnsbrg = 'KEMAS'
						end
						if @katbrg = '13' 
						begin
							set @jnsbrg = 'ALATBANGUNAN'
						end
						if @katbrg = '14' 
						begin
							set @jnsbrg = 'BARANGMODAL'
						end
						if @katbrg = '15' 
						begin
							set @jnsbrg = 'BARANGCONTOH'
						end
						if @katbrg = '16' 
						begin
							set @jnsbrg = 'BARANGJADI'
						end
						if @katbrg = '17' 
						begin
							set @jnsbrg = 'INVREIM'
						end
						if @katbrg = '18' 
						begin
							set @jnsbrg = 'ALATKANTOR'
						end
					FETCH NEXT FROM db_cursor_kat INTO @katbrg  
					END  

					CLOSE db_cursor_kat  
					DEALLOCATE db_cursor_kat
					
				end				
				
				if (select count(*) from @TBLKATEGORI_BARANG where KATEGORI_BARANG is not null) =0
				begin
					select '06' as status, ' KATEGORY TIDAK DITEMUKAN'  as description
					RETURN
				end


				if (select count(*) from @TBLKATEGORI_BARANG where KATEGORI_BARANG is not null) > 1
				begin
					set @jnsbrg = 'GABUNGAN'
				end

				declare @id_bc_in_hdr bigint
				select top 1
					@id_bc_in_hdr=id_bc_in_hdr
				from tbl_bc_in_hdr
				where car=@car and typedok = 'BC23'

				if(select count(*) from tbl_data_in_hdr where id_terkait = @id_bc_in_hdr) > 0
				begin
					SELECT 
					'06 ' AS status
					,' NO CAR SUDAH ADA : ' + @car + ' PERIKSA DULU.. BATALKAN DULU JIKA AKAN DITARIK ' AS description;
				end
				------END CEK DATA -----
				-------PART (KATEGRI 11)
				
				declare @nobl varchar(30), @tglbl date 
				select top 1
					@nobl=NOMOR_DOKUMEN
				,@tglbl=TANGGAL_DOKUMEN
				from openquery(TPBDB,'select * from tpb_dokumen')
				where ID_HEADER = $id_hdr_ceisa and (KODE_JENIS_DOKUMEN = '705' or KODE_JENIS_DOKUMEN = '740')

				if @nobl is null and @tglbl is null
				begin
					SELECT 
					'06 ' AS status
					,' BL atau AWB tidak ditemukan ' AS description;
					return

				end

				
				--- part
				if @jnsbrg = 'PART'
				begin	
					if (select COUNT(*) as cnt  from dbo.tbl_bc_in_hdr where dbo.tbl_bc_in_hdr.nobl = @nobl and dbo.tbl_bc_in_hdr.tglbl = @tglbl ) > 0
					begin
						select '04' as status, 'Data Sudah Ada di data BC'  as [description]
						return	
					end	
					
					declare @jmldetil int
					set @jmldetil = (
						select count(*)  from (
						select count(*) as cnt from [v_coo_data] where id_coo_data in (select id_coo from @tbl_id_coo)
						group by 
							ITEM_NUMBER, 
							kd_neg, 
							UOM
							,[DESCRIPTION]
							,HS_CODE
							,hskode 
							) x
					)

					
					if (  select count(*)
					FROM
					  dbo.[v_coo_data]
					WHERE
					  id_coo_data in (select id_coo from @tbl_id_coo) and [part_no] is null 
					) > 0
					begin
						select '01' as status, 'Part Number tidak ditemukan. Isikan dahulu di Data Part HS'  as [description]
						return	
					end

					if (  select count(*) from  (
						 select
							[supplier_country]
										FROM
										  dbo.[v_coo_data]
						 where id_coo_data in (select id_coo from @tbl_id_coo)  and ([supplier_country] <> 'ID')
						 group by  [supplier_country] 
						 ) x
					) <> 1
					begin
						select '01' as status, ' SUPPLIER BC23 TIDAK BOLEH DARI INDONESIA (ID) atau NULL PERBAIKI DATA SUPPLIER DAHULU'  as [description]
						return	
					end

					if( select count(kd_neg) from [v_coo_data] where id_coo_data in (select id_coo from @tbl_id_coo) and ISNUMERIC(kd_neg) = 1 ) > 0
					begin
						select '01' as status, 'ADA DATA NEGARA YANG SALAH..MOHON DILENGKAPI'  as [description]
						return	
					end
				
					insert into tbl_bc_in_hdr (
					CAR
					,KdKpbc
					,Tujuan
					,PasokNama
					,PasokAlmt
					,PasokNeg
					,UsahaNpwp
					,Registrasi
					,UsahaNama
					,ApiKd
					,ApiNo
					,Moda
					,AngkutNama
					,AngkutNo
					,AngkutFl
					,PelBkr
					,PelMuat
					,PelTransit
					,BC23No
					,BC23Tg
					--,DokTupKd
					,DokTupTg
					,PosNo
					,PosSub
					,TmpTbn
					,KdVal
					,Ndpbm
					,NilInv
					,Freight
					,BTambahan
					,Diskon
					,KdAss
					,Asuransi
					--,KdHrg
					,Fob
					,Cif
					,Bruto
					,Netto
					,JmCont
					,JmBrg
					,TglTtd
					,KotaTtd
					,Namattd
					,[Status]
					--,PosSubSub
					,nobl
					,tglbl
					,[typedok]	
					,[bc_in_from_in]
					,statusdok
					,[create_date]
					,[create_by]
					,[update_date]
					,[update_by]
    
				)
					select
						NOMOR_AJU
						,KODE_KANTOR
						,KODE_TUJUAN_TPB
						,NAMA_PEMASOK
						,ALAMAT_PEMASOK
						,KODE_NEGARA_PEMASOK
						,ID_PENGUSAHA
						,NOMOR_IJIN_TPB
						,NAMA_PENGUSAHA
						,KODE_JENIS_API_PENGUSAHA
						,API_PENGUSAHA
						,KODE_CARA_ANGKUT
						,NAMA_PENGANGKUT
						,NOMOR_VOY_FLIGHT
						,KODE_BENDERA
						,KODE_PEL_BONGKAR
						,KODE_PEL_MUAT
						,KODE_PEL_TRANSIT
						,NOMOR_DAFTAR
						,convert(varchar(10), TANGGAL_DAFTAR, 126)
						--,NOMOR_BC11
						,convert(varchar(10), TANGGAL_BC11, 126)
						,POS_BC11
						,SUBPOS_BC11
						,KODE_TPS
						,KODE_VALUTA
						,NDPBM
						,HARGA_INVOICE
						,FREIGHT
						,BIAYA_TAMBAHAN
						,DISKON
						,KODE_ASURANSI
						,ASURANSI
						--,KODE_HARGA
						,FOB
						,CIF
						,BRUTO
						,NETTO
						,JUMLAH_KONTAINER
						,JUMLAH_BARANG
						,convert(varchar(10), TANGGAL_TTD, 126)
						,KOTA_TTD
						,NAMA_TTD
						,KODE_STATUS
						--,SUBSUBPOS_BC11
						,@nobl
						,@tglbl
						,'BC23'
						,'COO'
						,'DATA PROCESSED'
						,GETDATE()
						,'$username'
						,GETDATE()
						,'$username'
					from openquery(TPBDB,'select * from tpb_header')
					where ID = $id_hdr_ceisa
					set @id_bc_in_hdr = SCOPE_IDENTITY() 
					
					update tbl_coo_data set 
					[car]=@car, 
					[nobc]=@NOMOR_DAFTAR, 
					[tglbc]=@TANGGAL_DAFTAR,
					id_bc_in_hdr = @id_bc_in_hdr,
					nobl=@nobl,
					tgbl=@tglbl,
					type_bc='BC23',
					status_inv = 'BCPROSES'
					where id_coo_data in (select id_coo from @tbl_id_coo)
					
					insert into tbl_bc_in_dtl_partnumber (
   					   [inv_dtl]
					  ,[inv_hdr_id]
					  ,[id_bc_in_hdr]
					  ,[noinv]
					  ,[car]
					  ,[nobc]
					  ,[tglbc]
					  ,part_no2
					  ,[part_number]
					  ,[part_name]
					  ,[part_name_customs]
					  ,[bm]
					  ,[qty]
					  ,[unit]
					  ,[harga]
					  ,[kdval]
					  ,[box]
					  ,[pack]
					  ,hscode
					  ,id_partnumber
					  ,seri_detil
   	
   					) select
   					  id_coo
   					  ,id_coo_data
   					  ,@id_bc_in_hdr
					  ,INVOICE_NO
					  ,NULL
					  ,nobc
					  ,tglbc
					  ,REPLACE(ITEM_NUMBER, char(32),'')
					  ,ITEM_NUMBER
					  ,part_name
					  ,part_name_customs
					  ,null as bm
					  ,ARRIV_PLAN_NUMBER
					  ,UOM
					  ,PRICE
					  ,CURRENT_MONEY
					  ,CTN_QUANTITY
					  ,PACKING_QTY
					  ,hskode
					  ,NULL
					  ,(select top 1 Serial from tbl_bc_in_dtl where id_bc_in_hdr = @id_bc_in_hdr and KdBrg=ITEM_NUMBER)
   					FROM
					  [dbo].[v_coo_data]
					where nobl = @nobl and tgbl = @tglbl

					insert into tbl_bc_in_dtl (
						id_bc_in_hdr
						,CAR
						,Serial
						,JnsBarangDtl
						--,TujuanKirimDtl
						,NoHs
						,SeriTrp
						,BrgUrai
						,Merk
						,Tipe
						,SpfLain
						,KdBrg
						,Penggunaan
						,KemasJn
						,KemasJm
						--,KdFasDtl
						,BrgAsal
						,DNilInv
						,DCif
						,KdSat
						,JmlSat
						,NettoDtl
					) select
						@id_bc_in_hdr
						,@car
						,SERI_BARANG
						,KATEGORI_BARANG
						--,KODE_GUNA
						,POS_TARIF
						,SERI_POS_TARIF
						,URAIAN
						,MERK
						,TIPE
						,SPESIFIKASI_LAIN
						,KODE_BARANG
						,KODE_GUNA
						,KODE_KEMASAN
						,JUMLAH_KEMASAN
						--,KODE_SKEMA_TARIF
						,KODE_ASAL_BARANG
						,HARGA_INVOICE
						,CIF
						,KODE_SATUAN
						,JUMLAH_SATUAN
						,NETTO
					from openquery(TPBDB,'select * from tpb_barang')
					where ID_HEADER = $id_hdr_ceisa
					
					insert into tbl_bc_in_dok (
						id_bc_in_hdr
						,CAR
						,DokKd
						,DokNo
						,DokTg

					) select 
						@id_bc_in_hdr
						,@car
						,KODE_JENIS_DOKUMEN
						,SUBSTRING(NOMOR_DOKUMEN,1,25) 
						,convert(varchar(10), TANGGAL_DOKUMEN, 126)
					from openquery(TPBDB,'select * from tpb_dokumen')
					where ID_HEADER = $id_hdr_ceisa

					insert into tbl_bc_in_con (
						id_bc_in_hdr
						,CAR
						,ContNo
						,ContUkur
						,ContTipe

					) select 
						@id_bc_in_hdr
						,@car
						,NOMOR_KONTAINER
						,KODE_UKURAN_KONTAINER
						,KODE_TIPE_KONTAINER

					from openquery(TPBDB,'select * from tpb_kontainer')
					where ID_HEADER = $id_hdr_ceisa

					SELECT 
					'00' AS status
					,' NOMOR : ' + @car AS description;
				end
				-- end part
				--- non part ---
				if @jnsbrg <> 'PART'
				begin	
					if (select COUNT(*) as cnt  from dbo.tbl_bc_in_hdr where dbo.tbl_bc_in_hdr.nobl = @nobl and dbo.tbl_bc_in_hdr.tglbl = @tglbl ) > 0
					begin
						select '04' as status, 'Data Sudah Ada di data BC'  as [description]
						return	
					end	
					insert into tbl_bc_in_hdr (
					CAR
					,KdKpbc
					,Tujuan
					,PasokNama
					,PasokAlmt
					,PasokNeg
					,UsahaNpwp
					,Registrasi
					,UsahaNama
					,ApiKd
					,ApiNo
					,Moda
					,AngkutNama
					,AngkutNo
					,AngkutFl
					,PelBkr
					,PelMuat
					,PelTransit
					,BC23No
					,BC23Tg
					--,DokTupKd
					,DokTupTg
					,PosNo
					,PosSub
					,TmpTbn
					,KdVal
					,Ndpbm
					,NilInv
					,Freight
					,BTambahan
					,Diskon
					,KdAss
					,Asuransi
					--,KdHrg
					,Fob
					,Cif
					,Bruto
					,Netto
					,JmCont
					,JmBrg
					,TglTtd
					,KotaTtd
					,Namattd
					,[Status]
					--,PosSubSub
					,nobl
					,tglbl
					,[typedok]	
					,[bc_in_from_in]
					,statusdok
					,[create_date]
					,[create_by]
					,[update_date]
					,[update_by]	
				)
					select
						NOMOR_AJU
						,KODE_KANTOR
						,KODE_TUJUAN_TPB
						,NAMA_PEMASOK
						,ALAMAT_PEMASOK
						,KODE_NEGARA_PEMASOK
						,ID_PENGUSAHA
						,NOMOR_IJIN_TPB
						,NAMA_PENGUSAHA
						,KODE_JENIS_API_PENGUSAHA
						,API_PENGUSAHA
						,KODE_CARA_ANGKUT
						,NAMA_PENGANGKUT
						,NOMOR_VOY_FLIGHT
						,KODE_BENDERA
						,KODE_PEL_BONGKAR
						,KODE_PEL_MUAT
						,KODE_PEL_TRANSIT
						,NOMOR_DAFTAR
						,convert(varchar(10), TANGGAL_DAFTAR, 126)
						--,NOMOR_BC11
						,convert(varchar(10), TANGGAL_BC11, 126)
						,POS_BC11
						,SUBPOS_BC11
						,KODE_TPS
						,KODE_VALUTA
						,NDPBM
						,HARGA_INVOICE
						,FREIGHT
						,BIAYA_TAMBAHAN
						,DISKON
						,KODE_ASURANSI
						,ASURANSI
						--,KODE_HARGA
						,FOB
						,CIF
						,BRUTO
						,NETTO
						,JUMLAH_KONTAINER
						,JUMLAH_BARANG
						,convert(varchar(10), TANGGAL_TTD, 126)
						,KOTA_TTD
						,NAMA_TTD
						,KODE_STATUS
						--,SUBSUBPOS_BC11
						,@nobl
						,@tglbl
						,'BC23'
						,'CEISANONCOO'
						,'DATA PROCESSED'
						,GETDATE()
						,'$username'
						,GETDATE()
						,'$username'
						
					from openquery(TPBDB,'select * from tpb_header')
					where ID = $id_hdr_ceisa
					set @id_bc_in_hdr = SCOPE_IDENTITY() 
					
					insert into tbl_bc_in_dtl (
						id_bc_in_hdr
						,CAR
						,Serial
						,JnsBarangDtl
						--,TujuanKirimDtl
						,NoHs
						,SeriTrp
						,BrgUrai
						,Merk
						,Tipe
						,SpfLain
						,KdBrg
						,Penggunaan
						,KemasJn
						,KemasJm
						--,KdFasDtl
						,BrgAsal
						,DNilInv
						,DCif
						,KdSat
						,JmlSat
						,NettoDtl
					) select
						@id_bc_in_hdr
						,@car
						,SERI_BARANG
						,KATEGORI_BARANG
						--,KODE_GUNA
						,POS_TARIF
						,SERI_POS_TARIF
						,URAIAN
						,MERK
						,TIPE
						,SPESIFIKASI_LAIN
						,KODE_BARANG
						,KODE_GUNA
						,KODE_KEMASAN
						,JUMLAH_KEMASAN
						--,KODE_SKEMA_TARIF
						,KODE_ASAL_BARANG
						,HARGA_INVOICE
						,CIF
						,KODE_SATUAN
						,JUMLAH_SATUAN
						,NETTO
					from openquery(TPBDB,'select * from tpb_barang')
					where ID_HEADER = $id_hdr_ceisa
					
					insert into tbl_bc_in_dok (
						id_bc_in_hdr
						,CAR
						,DokKd
						,DokNo
						,DokTg

					) select 
						@id_bc_in_hdr
						,@car
						,KODE_JENIS_DOKUMEN
						,SUBSTRING(NOMOR_DOKUMEN,1,25) 
						,convert(varchar(10), TANGGAL_DOKUMEN, 126)
					from openquery(TPBDB,'select * from tpb_dokumen')
					where ID_HEADER = $id_hdr_ceisa

					insert into tbl_bc_in_con (
						id_bc_in_hdr
						,CAR
						,ContNo
						,ContUkur
						,ContTipe

					) select 
						@id_bc_in_hdr
						,@car
						,NOMOR_KONTAINER
						,KODE_UKURAN_KONTAINER
						,KODE_TIPE_KONTAINER

					from openquery(TPBDB,'select * from tpb_kontainer')
					where ID_HEADER = $id_hdr_ceisa



					SELECT 
					'00' AS status
					,' NOMOR : ' + @car +  ' INVOICE : CEISANONCOO '  AS description;

				end


				--- end non part ---
 
				
				END TRY
				BEGIN CATCH 
					SELECT 
					'06' AS status
					,ERROR_MESSAGE() AS description;
					
				END CATCH;
				
			");
		}	
		$rs = $matchcar->result_array();
        //$r = $matchcar->result();		
		//var_dump($rs);die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
	}
	
	function cekdokmacthing_ceisa(){
		$car = (isset($_POST['car']) && !empty($_POST['car']))?$_POST['car']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$bc23dir = $this->config->item('APP_modulbc23');
		
		
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
         $q = $this->db->query("
			select a.NOMOR_DOKUMEN as dokno, convert(varchar(10), a.TANGGAL_DOKUMEN, 126) as doktg, a.KODE_JENIS_DOKUMEN as dokkd
			 from
				openquery(TPBDB,'select * from tpb_dokumen') a inner join openquery(TPBDB,'select * from tpb_header') b
				on a.ID_HEADER = b.ID
				
				
				where NOMOR_AJU='$car'
			"); 
		$rs = $q->result_array();
		$i=0;	
		foreach ($rs as $r){
			
		 	$responce[$i]["dokno"]=$r["dokno"];
			$responce[$i]["doktg"]=$r["doktg"]; 
			$responce[$i]["dokkd"]=$r["dokkd"]; 
			$i++;
			
		}
		
		echo json_encode($responce);
		
	
	
	}
	
	function cekdetilmacthing_ceisa(){
		$car = (isset($_POST['car']) && !empty($_POST['car']))?$_POST['car']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$bc23dir = $this->config->item('APP_modulbc23');
		
		
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
         $q = $this->db->query("
			select a.KATEGORI_BARANG as KATEGORI_BARANG,
			 a.KODE_BARANG as KODE_BARANG, a.TIPE, a.URAIAN as URAIAN, 
			a.JUMLAH_SATUAN, a.HARGA_SATUAN, a.POS_TARIF 
			 from
				openquery(TPBDB,'select * from tpb_barang') a inner join openquery(TPBDB,'select * from tpb_header') b
				on a.ID_HEADER = b.ID
				
				
				where NOMOR_AJU='$car'
			"); 
		$rs = $q->result_array();
		$i=0;	
		$k = 1;
		foreach ($rs as $r){
			$responce[$i]["nomor"]=$k;
		 	$responce[$i]["KATEGORI_BARANG"]=$r["KATEGORI_BARANG"];
			$responce[$i]["KODE_BARANG"]=$r["KODE_BARANG"]; 
			$responce[$i]["TIPE"]=$r["TIPE"]; 
			$responce[$i]["URAIAN"]=$r["URAIAN"]; 
			$responce[$i]["JUMLAH_SATUAN"]=$r["JUMLAH_SATUAN"]; 
			$responce[$i]["HARGA_SATUAN"]=$r["HARGA_SATUAN"]; 
			$responce[$i]["POS_TARIF"]=$r["POS_TARIF"]; 
			$i++;
			$k++;
			
		}
		echo json_encode($responce);
	}
	
 }