<?php
class CT_inv_draft 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->model("exim/mdl_bc23", 'mdl_bc23');
		$this->load->model("exim/mdl_invoice", 'mdl_invoice');
		$this->load->helper('afunction');
	}

	function index(){
		$q = $this->db->query("
			SELECT     v_noinv_dtl.noinv
			FROM         v_noinv_dtl LEFT OUTER JOIN
								  tbl_data_part_hs ON replace(v_noinv_dtl.partnumber,' ','') = tbl_data_part_hs.part_no2
			where tbl_data_part_hs.part_no is null
			group by v_noinv_dtl.noinv
		");
		$rs = $q->result_array();
		$datanull='';
		foreach ($rs as $row){
			$datanull.=$row['noinv'].'|';		
		}
		if ($datanull != ''){
			$data["test"] = 'Part Number di Data Part HS Not Match Di Invoice Berikut, Silahkan Cek Di View Data : ' . $datanull;
		} else {
			$data["test"]=$datanull;
		}
		
		$this->load->view('exim/view_inv_draft', $data);
	
	}
	
	function loaddatadownloaded(){
		$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_upload'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		//$tipe = (isset($_POST['tipe']) && !empty($_POST['tipe']))?$_POST['tipe']:die('{"sts":"ERROR","desc":" Param tipe Tidak Ditemukan"}');
		$tipe = (isset($_POST['tipe']) && !empty($_POST['tipe']))?$_POST['tipe']:'HIB';
		
		$where = " 1=1 and tipe='$tipe' ";
		
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "filename" ) {
						$searchString = $k->data;
						$where .= " and filename like  '%".$searchString."%'"; 
					} else if ( $k->field == "date_upload" ) {
						$searchString = $k->data;
						$where .= " and date_upload  =  '$searchString'"; 
					} else if ( $k->field == "status_upload" ) {
						$searchString = $k->data;
						$where .= " and status_upload like  '%".$searchString."%'"; 
					}  else if ( $k->field == "create_by" ) {
						$searchString = $k->data;
						$where .= " and create_by like  '%".$searchString."%'"; 
					} 
				}
			}
		}
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_invoice->upload_invoiceAll($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_invoice->upload_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_invoice_upload']=$r->id_invoice_upload; 
			$responce->rows[$i]['date_upload']=$r->date_upload; 
			$responce->rows[$i]['filename']=$r->filename; 
			$responce->rows[$i]['status_upload']=$r->status_upload;
			$responce->rows[$i]['create_date']=$r->create_date;
			$responce->rows[$i]['create_by']=$r->create_by;
			$responce->rows[$i]['tipe']=$r->tipe;
		    $responce->rows[$i]['catatan']=$r->catatan;
			
			$i++;
		}
		echo json_encode($responce);
	}
	
	
	function cekloaderbc23(){
		$filename = (isset($_POST['filename']) && !empty($_POST['filename']))?$_POST['filename']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$tipe = (isset($_POST['tipe']) && !empty($_POST['tipe']))?$_POST['tipe']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		$id_invoice_upload = (isset($_POST['id_invoice_upload']) && !empty($_POST['id_invoice_upload']))?$_POST['id_invoice_upload']:die('{"sts":"ERROR","desc":" Param Tidak Ditemukan"}');
		

		$apppath = $this->config->item('APP_upload_path');
		$path=$apppath.'invoice/hib/'.$filename;
		
		//echo $filename; die();
		
		if (!file_exists($path)) {
			
			$responce = array("sts" => '06', "desc" => " Filename ".$filename." tidak ditemukan");
			echo json_encode($responce);
			die();
		}
		
		
		$fh = fopen($path, 'r');
		$theData = fread($fh, 4);
		fclose($fh);
		$theData=preg_replace('/\s+/', '', $theData);  
			
		$ketemu = strpos($theData, 'R1');
		
		if($ketemu === false){
			$responce = array("sts" => '06', "desc" => " Format Data Salah, Lakukan Upload Ulang");
			echo json_encode($responce);
			die();			
		
		}
		//echo " EXEC ('sp_insert_data_hib @filename=''$path'', @file=''$filename'' ') ";
		//die();
				
		$qry = $this->db->query("
		
			BEGIN TRY
				EXEC ('sp_insert_data_hib @filename=''$path'', @file=''$filename'' ')
			END TRY
			BEGIN CATCH 
				SELECT 
				'error' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
			
				
		");
		
		//EXEC sp_insert_data_hib
			
		$rs = $qry->result_array();	
		
		//var_dump($rs);
		//die();
		foreach ($rs as $row){
			$responce = array("sts" => '00', "desc" => $row['description']);
		}  
		echo json_encode($responce);
	}
	
	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']:'inv_hdr_id'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		//$where = " a.status_inv=0 ";
		$where = " 1=1 ";
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "r3" ) {
						$searchString = $k->data;
						$where .= " and r3 like  '%".$searchString."%'"; 
					}  else if ( $k->field == "supplier_name" ) {
						$searchString = $k->data;
						$where .= " and b.supplier_name like  '%".$searchString."%'"; 
					}  else if ( $k->field == "r4" ) {
						$searchString = $k->data;
						$where .= " and r4 like  '%".$searchString."%'"; 
					}  else if ( $k->field == "r20" ) {
						$searchString = $k->data;
						$where .= " and r20 like  '%".$searchString."%'"; 
					}  else if ( $k->field == "nobl" ) {
						$searchString = $k->data;
						$where .= " and nobl like  '%".$searchString."%'"; 
					}  else if ( $k->field == "tgbl" ) {
						$searchString = $k->data;
						$where .= " and tgbl =  '$searchString'"; 
					} else if ( $k->field == "r2" ) {
						$searchString = $k->data;
						$where .= " and r2 like  '%".$searchString."%'"; 
					} else if ( $k->field == "status_inv" ) {
						$searchString = $k->data;
							if ($searchString==1){ 
								$where = " a.status_inv=$searchString  "; 
							} else if ($searchString==2){ 
								$where .= " "; 
								//$where .= " or a.status_inv=1  "; 
							} else if ($searchString=='-'){ 
								$where .= " and a.status_inv=0  "; 
							} else {
								$where .= " and (a.status_inv=0  or a.status_inv='$searchString') "; 
							}
					} else if ( $k->field == "status_inv" && $k->field == "r2" )  {
						$searchString = $k->data;
						$where = " r3 like  '%".$searchString."%'"; 
					
					}
				
				}
			}
		} else {
		
			$where .= " and a.status_inv=0 ";
		}
		
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_invoice->inv_all($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_invoice->inv_CountAll($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){
			if ($r->status_inv == 0){ $stat = "New"; } else { $stat = "Not New"; };
			if ($r->tgbl =='1900-10-23'){ $tgbl = ""; } else { $tgbl = $r->tgbl; };	
			$responce->rows[$i]['inv_hdr_id']=$r->inv_hdr_id; 
			$responce->rows[$i]['r3']=$r->r3; 
			$responce->rows[$i]['r2']=$r->r2; 
			$responce->rows[$i]['r4']=$r->r4;
			$responce->rows[$i]['r5']= inv_parsedate($r->r5); //$r->r5;
			$responce->rows[$i]['r20']=$r->r20;
			$responce->rows[$i]['supplier_name']=$r->supplier_name; 
			$responce->rows[$i]['date_create']=$r->date_create; 
			$responce->rows[$i]['status_inv']=$stat;
			$responce->rows[$i]['nobl']=$r->nobl;
			$responce->rows[$i]['tgbl']=$tgbl;
			
			$i++;
		}
		echo json_encode($responce);
		
	}
	
	function loaddatanonobl(){
		$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']:'inv_hdr_id'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:'';
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:'';
		
		
		//$where = " a.status_inv=0 ";
		$where = " 1=1 ";
		$where .= " and nobl =  '".$nobl."'";
		$where .= " and tgbl =  '".$tgbl."'";
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_invoice->inv_all($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_invoice->inv_CountAll($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){
		    if ($r->status_inv == 0){
					  $status_inv = 'INVOICE NEW ADDED';
			} else if ($r->status_inv == 1){
				 $status_inv = 'DATA ALREADY PROCESSED';
			}
			$responce->rows[$i]['inv_hdr_id']=$r->inv_hdr_id; 
			$responce->rows[$i]['invno']=$r->r3; 
			$responce->rows[$i]['blawbno']=$r->nobl; 
			$responce->rows[$i]['tglblawb']=$r->tgbl;
			$responce->rows[$i]['status_inv']=$status_inv;
			$responce->rows[$i]['status_invx']=$r->status_inv;
			
			
			$i++;
		}
		echo json_encode($responce);
		
	}
	
	
	
	
	function view_bc23_prosesinsertinv(){
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param tgbl Tidak Ditemukan"}');
		$data["tgbl"] = $tgbl;
		$data["nobl"] = $nobl;
		$this->load->view('exim/view_bc23_prosesinsertinv',$data);
	
	}
	
	function ceklastcar(){
	
		$bc23dir = $this->config->item('APP_modulbc23');
		$dokno = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param no bl Tidak Ditemukan"}');;
		
		$lascarx = "-";
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		
		$query = $this->db->query("
		
			select max(car) as maxofcar from 
			OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23Dok
			 where dbo.trim(DokNo) = '$dokno' 
		");
		$rs = $query->result_array();
		
		
		foreach ($rs as $row){
		
			$lascarx = $row['maxofcar'];
		
		}
		
		if($lascarx != ''){
			echo $lascar;
			echo "<br />";
			echo "NO BL Sudah ada dengan nomor CAR: $lascarx";
			echo "<br />";
			die();	
		}
		
		
		$lascar = "-";
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		
		$query = $this->db->query("
		
			select max(car) as lastofcar from  
			OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23Hdr
			
		");
		$rs = $query->result_array();
		
		
		foreach ($rs as $row){
		
			$lascar = $row['lastofcar'];
		
		}
		
		$noajunext = substr($lascar, 20, 6) * 1 + 1; 
		$noreg = $this->config->item('APP_noregbc23');
		$nextcar = $noreg.date("Ymd").sprintf('%06d', $noajunext);
		echo $lascar;
		echo "<br />";
		echo "Kemungkinan aju Selanjutnya: $nextcar";
		echo "<br />";
		
		/*$bc23dir = $this->config->item('APP_modulbc23');
		$conn = new COM("ADODB.Connection") or die("Cannot start ADO");  
		$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$bc23dir;Password=MumtazFarisHana;");
		$q = "select max(car) as lastofcar from tblBC23Hdr ";
		$rs = $conn->Execute($q) or die("Error in query: $q. " . $conn->ErrorMsg(). "in LINE __" . __LINE__);
		$lascar = @$rs->Fields("lastofcar")->value;
		$noajunext = substr($lascar, 20, 6) * 1 + 1; 
		$noreg = $this->config->item('APP_noregbc23');
		$nextcar = $noreg.date("Ymd").sprintf('%06d', $noajunext);
		echo $lascar;
		echo "<br />";
		echo "Kemungkinan aju Selanjutnya: $nextcar";
		echo "<br />";
		$rs->Close();
		$conn->Close();

		$rs = null;
		$conn = null; */
	
	}
	
	function sendtomodulbc23(){
		$id = (isset($_POST['id']) && !empty($_POST['id']))?$_POST['id']:die('{"sts":"ERROR","desc":" Param ID Tidak Ditemukan"}');
		//$noinv = (isset($_POST['noinv']) && !empty($_POST['noinv']))?$_POST['noinv']:die('{"sts":"ERROR","desc":" Param INVx Tidak Ditemukan"}');
		$noinv = (isset($_POST['noinv']) && !empty($_POST['noinv']))?$_POST['noinv']:'';
		//$userid=$this->session->userdata('DX_user_id');
		$userid=$this->session->userdata('DX_username');
		$bc23dir = $this->config->item('APP_modulbc23');
		$bc23dirx = 'C:\BeaCukai\TPB25_PDE_metec\dbTPB.mdb';
		$conn = new COM("ADODB.Connection") or die("Cannot start ADO");  
		$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$bc23dir;Password=MumtazFarisHana;");
		$q = "select max(car) as lastofcar from tblBC23Hdr ";
		$rs = $conn->Execute($q) or die("Error in query: $q. " . $conn->ErrorMsg(). "in LINE __" . __LINE__);
		$lascar = @$rs->Fields("lastofcar")->value;
		$noajunext = substr($lascar, 20, 6) * 1 + 1; 
		$noreg = $this->config->item('APP_noregbc23');
		$nextcar = $noreg.date("Ymd").sprintf('%06d', $noajunext);
		$rs->Close();
		$conn->Close();
		$rs = null;
		$conn = null;	
		
		//echo "exec('sp_insertmodulbc23 @car =N''$nextcar'', @idx=$id, @conn=''$bc23dir'', @userid=''$userid'' ')"; die();		
		/* $this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		"); */
		$qryinsmodul = $this->db->query("
			BEGIN TRY
				exec('sp_insertmodulbc23 @car =N''$nextcar'', @idx=$id, @conn=''$bc23dir'', @userid=''$userid'' ')
			END TRY
			BEGIN CATCH 
				SELECT 
				'08' AS status
				,ERROR_MESSAGE() AS hasil;
				
			END CATCH;	
			
			");
				
		//$qryinsmodul->result();
		$rs = $qryinsmodul->result_array();	
		//var_dump($rs);
		//die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['hasil']);
		}  
		echo json_encode($responce);

	}
	
	function ceknocarnobl(){
		$bc23dir = $this->config->item('APP_modulbc23');
		$dokno = (isset($_POST['dokno']) && !empty($_POST['dokno']))?$_POST['dokno']:die('{"sts":"ERROR","desc":" Param no Tidak Ditemukan"}');;
		$doktg = (isset($_POST['doktg']) && !empty($_POST['doktg']))?$_POST['doktg']:die('{"sts":"ERROR","desc":" Param date Tidak Ditemukan"}');;
		
		$lascar = "-";
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		
		$query = $this->db->query("
		
			select max(car) as maxofcar from 
			OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23Dok
			 where dbo.trim(DokNo) = '$dokno' 
		");
		$rs = $query->result_array();
		
		
		foreach ($rs as $row){
		
			$lascar = $row['maxofcar'];
		
		}
		
		foreach ($rs as $row){
			$responce = array("sts" => '00', "desc" => $lascar);
		}  
		echo json_encode($responce);
	
		
		
	}
	
	function sendtomodulbc23manual(){
		$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']:'';
		$nodaft = (isset($_POST['nodaft']) && !empty($_POST['nodaft']))?$_POST['nodaft']:'';
		//$userid=$this->session->userdata('DX_user_id');
		$userid=$this->session->userdata('DX_username');
		$qryinsmodul = $this->db->query("
			BEGIN TRY
				exec('sp_insertmodulbc23_manual @car=''$car'', @idx=$id_bc_in_hdr, @nodaft=''$nodaft'',@tgdaft=''$tgdaft'',  @userid=''$userid'' ')
			END TRY
			BEGIN CATCH 
				SELECT 
				'08' AS status
				,ERROR_MESSAGE() AS hasil;
				
			END CATCH;	
			
		");
		$rs = $qryinsmodul->result_array();	
		//var_dump($rs);
		//die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['hasil']);
		}  
		echo json_encode($responce);
	
	
	}
	
	function view_bc23_nonhib(){
	
		$q = $this->db->query("
			SELECT    v_nonhib_inv_dtl.INVOICE_NO, v_nonhib_inv_dtl.partnumberx, tbl_data_part_hs.part_no
			FROM         v_nonhib_inv_dtl LEFT OUTER JOIN
								  tbl_data_part_hs ON v_nonhib_inv_dtl.partnumberx = tbl_data_part_hs.part_no
			where tbl_data_part_hs.part_no is null
			group by v_nonhib_inv_dtl.INVOICE_NO, v_nonhib_inv_dtl.partnumberx, tbl_data_part_hs.part_no
		");
		$rs = $q->result_array();
		$datanull='';
		foreach ($rs as $row){
			$datanull.=$row['INVOICE_NO'].'|';		
		}
		if ($datanull != ''){
			$data["test"] = 'Part Number di Data Part HS Not Match Di Invoice Berikut, Silahkan Cek Di View Data : ' . $datanull;
		} else {
			$data["test"]=$datanull;
		}
		
		$this->load->view('exim/view_bc23_nonhib',$data);
	
	}
	
	function loaddata_bc23_nonhib(){
		$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_nonhib_hdr'; // 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 == "noinv" ) {
						$searchString = $k->data;
						$where .= " and noinv like  '%".$searchString."%'"; 
					} else if ( $k->field == "tglinv" ) {
						$searchString = $k->data;
						$where .= " and tglinv  =  '$searchString'"; 
					} else if ( $k->field == "tglinv" ) {
						$searchString = $k->data;
						$where .= " and tglinv  =  '$searchString'"; 
					} else if ( $k->field == "nobl" ) {
						$searchString = $k->data;
						$where .= " and nobl like  '%".$searchString."%'"; 
					}  else if ( $k->field == "status" ) {
						$searchString = $k->data;
						if($searchString !=''){
							$where .= " and status =  '$searchString' "; 
						} else { 
							$where .= "  and status =  'New' ";  
						}
						if ($searchString=='2'){
							$where = ' 1=1 ';
						}
					} 
				}
			}
		} else {
			$where .= "  and status =  'New' ";
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_invoice->bc23datanonhibhdrAll($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_invoice->bc23datanonhibhdrcountAll($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){
			if ($r->status == 0){ $stat = "New"; } else { $stat = "Not New"; };
			if ($r->tgbl == '1900-10-23'){ $tgbl = ""; } else { $tgbl = $r->tgbl; };	
				
			$responce->rows[$i]['id_nonhib_hdr']=$r->id_nonhib_hdr; 
			$responce->rows[$i]['from_file_name']=$r->from_file_name; 
			$responce->rows[$i]['totalitemfile']=$r->totalitemfile; 
			$responce->rows[$i]['exec_date']=$r->exec_date;
			$responce->rows[$i]['status']= $r->status;
			$responce->rows[$i]['itemcount']=$r->itemcount;
			$responce->rows[$i]['noinv']=$r->noinv;
			$responce->rows[$i]['tglinv']=$r->tglinv;
			$responce->rows[$i]['tglinv2']=$r->tglinv2;
			$responce->rows[$i]['nobl']=trim($r->nobl);
			$responce->rows[$i]['tgbl']=$tgbl;
			$responce->rows[$i]['vendor']=$r->vendor;
			$i++;
		}
		echo json_encode($responce);
		
	
	}
	
	
	 
	
	function execdatanonhibbc23(){
	
	
			
		
		$qry = $this->db->query("
		
			BEGIN TRY
			
			 exec(' sp_insert_data_non_hib ')
			
			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){
			$responce = array("sts" => $row['status'], "desc" => $row['description'].' File/Files Loaded');
		}  
		echo json_encode($responce);
	
	}
	
	function view_bc23_nonhibdtl(){
		$id_datanonhibbc23 = (isset($_POST['id_datanonhibbc23']) && !empty($_POST['id_datanonhibbc23']))?$_POST['id_datanonhibbc23']:die('{"sts":"ERROR","desc":" Param ID Tidak Ditemukan"}');
		$data["id_datanonhibbc23"] = $id_datanonhibbc23;
		
		$q = $this->db->query("
			SELECT    v_nonhib_inv_dtl.id_nonhib_hdr, v_nonhib_inv_dtl.partnumberx, tbl_data_part_hs.part_no
			FROM         v_nonhib_inv_dtl LEFT OUTER JOIN
								  tbl_data_part_hs ON v_nonhib_inv_dtl.partnumberx = tbl_data_part_hs.part_no
			where id_nonhib_hdr = $id_datanonhibbc23 and tbl_data_part_hs.part_no is null
			group by v_nonhib_inv_dtl.id_nonhib_hdr, v_nonhib_inv_dtl.partnumberx, tbl_data_part_hs.part_no
		");
		$rs = $q->result_array();
		$datanull='';
		foreach ($rs as $row){
			$datanull.=$row['partnumberx'].'|';		
		}
		if ($datanull != ''){
			$data["test"] = 'Data Part Tidak Match dengan Part HS. Mohon Ditambahkan di Master Data : ' . $datanull;
		} else {
			$data["test"]=$datanull;
		}
		
		$this->load->view('exim/view_bc23_nonhibdtl', $data);
	
	}
	
	function loaddata_viewdtl_nonhib(){
		$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']:'tbl_data_out_hdr.id_data_out_hdr'; // get index row - i.e. user click to sort
		$sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$id = (isset($_POST['id_datanonhibbc23']) && !empty($_POST['id_datanonhibbc23']))?$_POST['id_datanonhibbc23']:die('{"sts":"ERROR","desc":" Param ID Tidak Ditemukan"}');
		
		$where = " id_nonhib_hdr=$id ";
		
	
		if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "CONTNO" ) {
						$searchString = $k->data;
						$where .= " and CONTNO like  '%".$searchString."%'"; 
					} else if ( $k->field == "INVOICE_NO" ) {
						$searchString = $k->data;
						$where .= " and INVOICE_NO like  '%".$searchString."%'"; 
					} else if ( $k->field == "partnumber" ) {
						$searchString = $k->data;
						$where .= " and partnumber like  '%".$searchString."%'"; 
					} else if ( $k->field == "partnamecustoms" ) {
						$searchString = $k->data;
						$where .= " and partnamecustoms like  '%".$searchString."%'"; 
					} else if ( $k->field == "INVQTY" ) {
						$searchString = $k->data;
						$where .= " and INVQTY = $searchString "; 
					} else if ( $k->field == "PACK" ) {
						$searchString = $k->data;
						$where .= " and PACK =  $searchString"; 
					} else if ( $k->field == "CNO" ) {
						$searchString = $k->data;
						$where .= " and CNO like  '%".$searchString."%'"; 
					} else if ( $k->field == "ORDERNO" ) {
						$searchString = $k->data;
						$where .= " and ORDERNO like  '%".$searchString."%'"; 
					} 
					else if ( $k->field == "partname" ) {
						$searchString = $k->data;
						$where .= " and partname like  '%".$searchString."%'"; 
					} 
				}
			}
		}
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_invoice->bc23datanonhibdtlAll($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_invoice->bc23datanonhibdtlcountAll($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_nonhib_dtl']=$r->id_nonhib_dtl; 
			$responce->rows[$i]['id_nonhib_hdr']=$r->id_nonhib_hdr; 
			$responce->rows[$i]['INVOICE_NO']=$r->INVOICE_NO; 
			$responce->rows[$i]['partnumber']=$r->partnumber;
			$responce->rows[$i]['CONTNO']= $r->CONTNO;
			$responce->rows[$i]['INVQTY']=$r->INVQTY;
			$responce->rows[$i]['PACK']=$r->PACK;
			$responce->rows[$i]['UOMPFX']=$r->UOMPFX;
			$responce->rows[$i]['CNO']=$r->CNO;
			$responce->rows[$i]['ORDERNO']=$r->ORDERNO;
			$responce->rows[$i]['VENDOR']=$r->VENDOR;
			$responce->rows[$i]['partname']=$r->partname;
			$responce->rows[$i]['partnamecustoms']=$r->partnamecustoms;
			$i++;
		}
		echo json_encode($responce);
		
	
	
	
	}
	
	function prosesdatabl(){
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}'); 
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}');
		
		$datadiff = (isset($_POST['datadiff']) && !empty($_POST['datadiff']))?$_POST['datadiff']:'0'; 
		$datadiff=json_decode($datadiff);
		$datacsv = '';
		foreach ($datadiff as $data ){
			$inv_hdr_id = (isset($data->inv_hdr_id) && !empty($data->inv_hdr_id ))?$data->inv_hdr_id :die('{"sts":"ERROR","desc":" Param inv_hdr_id Tidak Ditemukan"}');
			$invno = (isset($data->invno) && !empty($data->invno ))?$data->invno :die('{"sts":"ERROR","desc":" Param invno Tidak Ditemukan"}');
			$blawbno = (isset($data->blawbno) && !empty($data->blawbno ))?$data->blawbno :die('{"sts":"ERROR","desc":" Param blawbno Tidak Ditemukan"}');
			$tglblawb = (isset($data->tglblawb) && !empty($data->tglblawb ))?$data->tglblawb :die('{"sts":"ERROR","desc":" Param tglblawb Tidak Ditemukan"}');
			
			$datacsv .= $inv_hdr_id . ',' . $invno . ',' . $blawbno . ',' . $tglblawb; 
			$datacsv .= "|";
		}
		// echo $datacsv;	
		// var_dump($datadiff); die();
		
		
		$qry = $this->db->query("
		
			BEGIN TRY
			
				EXEC ('spi_invoiceupdatebl @datadiff=''$datacsv'', @nobl=''$nobl'', @tgbl=''$tgbl'', @cmd=''proses''  ') 
			END TRY
			BEGIN CATCH 
				SELECT 
				'error' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
		 ");
		 $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 canceldatabl(){
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}'); 
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}');
		
		$datadiff = (isset($_POST['datadiff']) && !empty($_POST['datadiff']))?$_POST['datadiff']:'0'; 
		$datadiff=json_decode($datadiff);
		$datacsv = '';
		foreach ($datadiff as $data ){
			$datacsv .= $data->inv_hdr_id . ',' . $data->invno . ',' . '-' . ',' . '1900-10-23'; 
			$datacsv .= "|";
		}
		 // echo $datacsv;	
		// var_dump($datadiff); die();
		
		// echo "
		
			 // EXEC ('spi_invoiceupdatebl @datadiff=''$datacsv'', @nobl=''$nobl'', @tgbl=''$tgbl'', @cmd=''cancel'' ') 
			
		 // "; die();
		
		$qry = $this->db->query("
			BEGIN TRY	
			 EXEC ('spi_invoiceupdatebl @datadiff=''$datacsv'', @nobl=''$nobl'', @tgbl=''$tgbl'', @cmd=''cancel'' ') 
			END TRY
			BEGIN CATCH 
				SELECT 
				'error' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
		 ");
		$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 recalculatedatabl(){
	
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}'); 
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}');
		
		$datadiff = (isset($_POST['datadiff']) && !empty($_POST['datadiff']))?$_POST['datadiff']:'0'; 
		$datadiff=json_decode($datadiff);
		$datacsv = '';
		foreach ($datadiff as $data ){
			$inv_hdr_id = (isset($data->inv_hdr_id) && !empty($data->inv_hdr_id ))?$data->inv_hdr_id :die('{"sts":"ERROR","desc":" Param inv_hdr_id Tidak Ditemukan"}');
			$invno = (isset($data->invno) && !empty($data->invno ))?$data->invno :die('{"sts":"ERROR","desc":" Param invno Tidak Ditemukan"}');
			$blawbno = (isset($data->blawbno) && !empty($data->blawbno ))?$data->blawbno :die('{"sts":"ERROR","desc":" Param blawbno Tidak Ditemukan"}');
			$tglblawb = (isset($data->tglblawb) && !empty($data->tglblawb ))?$data->tglblawb :die('{"sts":"ERROR","desc":" Param tglblawb Tidak Ditemukan"}');
			
			$datacsv .= $inv_hdr_id . ',' . $invno . ',' . $blawbno . ',' . $tglblawb; 
			$datacsv .= "|";
		}
		// echo $datacsv;	
		// var_dump($datadiff); die();
		
		
		$qry = $this->db->query("
			BEGIN TRY
			 EXEC ('spu_insertbc23_bl @datadiff=''$datacsv'', @nobl=''$nobl'', @tgbl=''$tgbl''  ') 
			END TRY
			BEGIN CATCH 
				SELECT 
				'error' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
		 ");
		 $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 updatestatusproses(){
		$typebc = 'BC23';
		$noinv = (isset($_POST['noinv']) && !empty($_POST['noinv']))?$_POST['noinv']:die('{"sts":"ERROR","desc":" Param INV Tidak Ditemukan"}');
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
		$tgbl= (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param tg bl Tidak Ditemukan"}');
		
		$noinv=json_decode($noinv);
		$noinvcsv = '';
		foreach ($noinv as $data ){
			$inv_hdr_id = (isset($data->inv_hdr_id) && !empty($data->inv_hdr_id ))?$data->inv_hdr_id :die('{"sts":"ERROR","desc":" Param inv_hdr_id Tidak Ditemukan"}');
			$invno = (isset($data->invno) && !empty($data->invno ))?$data->invno :die('{"sts":"ERROR","desc":" Param invno Tidak Ditemukan"}');
			$noinvcsv .= $inv_hdr_id. ',' . $invno; 
			$noinvcsv .= "|";
		}
		$listinv = substr($noinvcsv, 0,  strlen($noinvcsv) -1); 
		 // echo "EXEC  [sp_insertbc23] @nobl = N'$nobl', @tgbl = '$tgbl', @typebc = N'$typebc',@noinv = '$listinv'";
		 // var_dump($noinv); die();
		$typebc = 'BC23';
		$qry = $this->db->query("
			BEGIN TRY
				EXEC  [sp_insertbc23] @nobl = N'$nobl', @tgbl = '$tgbl', @typebc = N'$typebc',@noinv = '$listinv'
			END TRY
			BEGIN CATCH 
				SELECT 
				'error' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
		");
		$rs = $qry->result_array();	
		// var_dump($rs);
		// die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
		
		
		
	}
	
	function recalculate_new_inv_bl(){
		$typebc = 'BC23';
		$noinv = (isset($_POST['noinv']) && !empty($_POST['noinv']))?$_POST['noinv']:die('{"sts":"ERROR","desc":" Param INV Tidak Ditemukan"}');
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
		$tgbl= (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param tg bl Tidak Ditemukan"}');
		
		$noinv=json_decode($noinv);
		$noinvcsv = '';
		foreach ($noinv as $data ){
			$inv_hdr_id = (isset($data->inv_hdr_id) && !empty($data->inv_hdr_id ))?$data->inv_hdr_id :die('{"sts":"ERROR","desc":" Param inv_hdr_id Tidak Ditemukan"}');
			$invno = (isset($data->invno) && !empty($data->invno ))?$data->invno :die('{"sts":"ERROR","desc":" Param invno Tidak Ditemukan"}');
			$noinvcsv .= $inv_hdr_id. ',' . $invno; 
			$noinvcsv .= "|";
		}
		$listinv = substr($noinvcsv, 0,  strlen($noinvcsv) -1); 
		 //echo "EXEC  [sp_insertcb23_recalculate_new_inv_bl] @nobl = N'$nobl', @tgbl = '$tgbl', @typebc = N'$typebc',@noinv = '$listinv'";
		 // var_dump($noinv); die();
		$typebc = 'BC23';
		$qry = $this->db->query("
			EXEC [sp_insertcb23_recalculate_new_inv_bl] @nobl = N'$nobl', @tgbl = '$tgbl', @typebc = N'$typebc',@noinv = '$listinv'
		");
		$rs = $qry->result_array();	
		//var_dump($rs);
		//die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
		
	}
	
	function loaddata_bc23_nonhib_nonobl(){
		$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_nonhib_hdr'; // get index row - i.e. user click to sort
		$sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		//$where = " status='new' ";
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:'';
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:'';
		
		
		//$where = " a.status_inv=0 ";
		$where = " 1=1 ";
		$where .= " and nobl =  '".$nobl."'";
		$where .= " and tgbl =  '".$tgbl."'";
		
			
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_invoice->bc23datanonhibhdrAll($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_invoice->bc23datanonhibhdrcountAll($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){
			if ($r->status == 0){ $stat = "New"; } else { $stat = "Not New"; };
			if ($r->tgbl =='1900-10-23'){ $tgbl = ""; } else { $tgbl = $r->tgbl; };	

			 if ($r->status == 'new'){
					  $status = 'INVOICE NEW ADDED';
			} else if ($r->status == 'DATA PROCESSED'){
				 $status = 'DATA ALREADY PROCESSED';
			}	
			
			if ($r->status == 'new'){
					  $statusx = 0;
			} else {
				 $statusx = 1;
			}	
			
			$responce->rows[$i]['id_nonhib_hdr']=$r->id_nonhib_hdr; 
			$responce->rows[$i]['from_file_name']=$r->from_file_name; 
			$responce->rows[$i]['totalitemfile']=$r->totalitemfile; 
			$responce->rows[$i]['exec_date']=$r->exec_date;
			$responce->rows[$i]['status']= $status;//$r->status;
			$responce->rows[$i]['itemcount']=$r->itemcount;
			$responce->rows[$i]['noinv']=$r->noinv;
			$responce->rows[$i]['tglinv']=$r->tglinv;
			$responce->rows[$i]['tglinv2']=$r->tglinv2;
			$responce->rows[$i]['nobl']=$r->nobl;
			$responce->rows[$i]['tgbl']=$r->tgbl;
			$responce->rows[$i]['statusx']=$statusx;
			$i++;
		}
		echo json_encode($responce);
		
	}
	
	
	function view_bc23_nonhib_summary(){
		$id_datanonhibbc23 = (isset($_POST['id_datanonhibbc23']) && !empty($_POST['id_datanonhibbc23']))?$_POST['id_datanonhibbc23']:die('{"sts":"ERROR","desc":" Param ID Tidak Ditemukan"}');
		$data["id_datanonhibbc23"] = $id_datanonhibbc23;
		$this->load->view('exim/view_bc23_nonhib_summary', $data);
	
	}
	
	function loaddata_viewdtl_nonhib_summary(){
		$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']:'tbl_data_out_hdr.id_data_out_hdr'; // get index row - i.e. user click to sort
		$sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		$id = (isset($_POST['id_datanonhibbc23']) && !empty($_POST['id_datanonhibbc23']))?$_POST['id_datanonhibbc23']:die('{"sts":"ERROR","desc":" Param ID Tidak Ditemukan"}');
		
		$where = " id_nonhib_hdr=$id ";
		
	
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_invoice->datanonhibsummary_all($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_invoice->datanonhibsummary_CountAll($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]['partname']=$r->partname; 
			$responce->rows[$i]['INVOICE_NO']=$r->INVOICE_NO; 
			$responce->rows[$i]['sumqty']=$r->sumqty;
			$responce->rows[$i]['sumpack']= $r->sumpack;
			
			$i++;
		}
		echo json_encode($responce);
	
	}
	
	function updatestatusproses_nonhib(){
		$datanoinv = (isset($_POST['noinv']) && !empty($_POST['noinv']))?$_POST['noinv']:die('{"sts":"ERROR","desc":" Param INV Tidak Ditemukan"}');
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
		$tgbl= (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param tg bl Tidak Ditemukan"}');
		$typebc = 'BC23';
		$datanoinv=json_decode($datanoinv);
		$datacsv = '';
		foreach ($datanoinv as $data ){
			$datacsv .= $data->id_nonhib_hdr . ',' . trim($data->noinv); 
			$datacsv .= "|";
		}
		// echo $datacsv;	
		// var_dump($datanoinv); die();
		//echo " EXEC [sp_nonhib_insert] @datanoinv ='$datacsv', @typebc='$typebc', @tgbl='$tgbl', @nobl='$nobl'";
		//die();
		$datacsv = substr($datacsv, 0,  strlen($datacsv) -1); 
		// echo "EXEC [sp_nonhib_insert] @datanoinv ='$datacsv', @typebc='$typebc', @tgbl='$tgbl', @nobl='$nobl' "; die();
		$qry = $this->db->query(" EXEC [sp_nonhib_insert] @datanoinv ='$datacsv', @typebc='$typebc', @tgbl='$tgbl', @nobl='$nobl' ");
		$rs = $qry->result_array();	
		// var_dump($rs);
		// die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
	}
	
	function recalculate_new_inv_bl_nonhib(){
		$typebc = 'BC23';
		$noinv = (isset($_POST['noinv']) && !empty($_POST['noinv']))?$_POST['noinv']:die('{"sts":"ERROR","desc":" Param INV Tidak Ditemukan"}');
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
		$tgbl= (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param tg bl Tidak Ditemukan"}');
		
		$noinv=json_decode($noinv);
		
		$noinvcsv = '';
		foreach ($noinv as $data ){
			if ($data->statusx == 0){
				$id_nonhib_hdr = (isset($data->id_nonhib_hdr) && !empty($data->id_nonhib_hdr ))?$data->id_nonhib_hdr :die('{"sts":"ERROR","desc":" Param id_nonhib_hdr Tidak Ditemukan"}');
				$invno = (isset($data->noinv) && !empty($data->noinv ))?$data->noinv :die('{"sts":"ERROR","desc":" Param noinv Tidak Ditemukan"}');
				$noinvcsv .= $id_nonhib_hdr. ',' . $invno; 
				$noinvcsv .= "|";
			}
		}
		$listinv = substr($noinvcsv, 0,  strlen($noinvcsv) -1); 
		// echo "
			// EXEC [sp_nonhib_insertcb23_recalculate_new_inv_bl] @nobl = N'$nobl', @tgbl = '$tgbl', @typebc = N'$typebc',@noinv = '$listinv'
		// ";
		// var_dump($noinv); die();
		 // die();
		$qry = $this->db->query("
			
			EXEC [sp_nonhib_insertcb23_recalculate_new_inv_bl] @nobl = N'$nobl', @tgbl = '$tgbl', @typebc = N'$typebc',@noinv = '$listinv'
			
		");
		$rs = $qry->result_array();	
		// var_dump($rs);
		// die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
		
	}

	
	
	function view_bc23_nonhibaddbldata(){
	
		$this->load->view('exim/view_bc23_nonhibaddbldata');
	
	}
	
	function prosesdatabl_nonhib(){
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}'); 
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}');
		
		$datadiff = (isset($_POST['datadiff']) && !empty($_POST['datadiff']))?$_POST['datadiff']:'0'; 
		$datadiff=json_decode($datadiff);
		$datacsv = '';
		foreach ($datadiff as $data ){
			$inv_hdr_id = (isset($data->id_nonhib_hdr) && !empty($data->id_nonhib_hdr ))?$data->id_nonhib_hdr :die('{"sts":"ERROR","desc":" Param inv_hdr_id Tidak Ditemukan"}');
			$invno = (isset($data->noinv) && !empty($data->noinv ))?$data->noinv :die('{"sts":"ERROR","desc":" Param invno Tidak Ditemukan"}');
			$blawbno = (isset($data->nobl) && !empty($data->nobl ))?$data->nobl :die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
			$tglblawb = (isset($data->tgbl) && !empty($data->tgbl ))?$data->tgbl :die('{"sts":"ERROR","desc":" Param tgbl Tidak Ditemukan"}');
			
			$datacsv .= $inv_hdr_id . ',' . $invno . ',' . $blawbno . ',' . $tglblawb; 
			$datacsv .= "|";
		}
		// echo $datacsv;	
		// var_dump($datadiff); die();
		
		
		$qry = $this->db->query("
		
			 EXEC ('spu_nonhib_bl @datadiff=''$datacsv'', @nobl=''$nobl'', @tgbl=''$tgbl'', @cmd=''proses''   ') 
			
		 ");
		 $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 canceldatabl_nonhib(){
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}'); 
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}');
		
		$datadiff = (isset($_POST['datadiff']) && !empty($_POST['datadiff']))?$_POST['datadiff']:'0'; 
		$datadiff=json_decode($datadiff);
		$datacsv = '';
		foreach ($datadiff as $data ){
			$datacsv .= $data->id_nonhib_hdr . ',' . $data->noinv . ',' . '-' . ',' . '1900-10-23'; 
			$datacsv .= "|";
		}
		 // echo $datacsv;	
		// var_dump($datadiff); die();
		// echo "
		
			 // EXEC ('spu_nonhib_bl @datadiff=''$datacsv'', @nobl=''$nobl'', @tgbl=''$tgbl'' ') 
			
		 // ";
		 // die();
		$qry = $this->db->query("
		
			 EXEC ('spu_nonhib_bl @datadiff=''$datacsv'', @nobl=''$nobl'', @tgbl=''$tgbl'', @cmd=''cancel'' ') 
			
		 ");
		$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 recalculatedatabl_nonhib(){
	
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}'); 
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}');
		
		$datadiff = (isset($_POST['noinv']) && !empty($_POST['noinv']))?$_POST['noinv']:'0'; 
		$datadiff=json_decode($datadiff);
		
		$datacsv = '';
		foreach ($datadiff as $data ){
			$inv_hdr_id = (isset($data->id_nonhib_hdr) && !empty($data->id_nonhib_hdr ))?$data->id_nonhib_hdr :die('{"sts":"ERROR","desc":" Param inv_hdr_id Tidak Ditemukan"}');
			$invno = (isset($data->noinv) && !empty($data->noinv ))?$data->noinv :die('{"sts":"ERROR","desc":" Param invno Tidak Ditemukan"}');
			$blawbno = (isset($data->nobl) && !empty($data->nobl ))?$data->nobl :die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
			$tglblawb = (isset($data->tgbl) && !empty($data->tgbl ))?$data->tgbl :die('{"sts":"ERROR","desc":" Param tgbl Tidak Ditemukan"}');
			
			$datacsv .= $inv_hdr_id . ',' . $invno . ',' . $blawbno . ',' . $tglblawb; 
			$datacsv .= "|";
		}
		// echo $datacsv;	
		// var_dump($datadiff); die();
		
		// echo " 		
			 // EXEC ('spu_nonhib_bl_recalculate @datadiff=''$datacsv'', @nobl=''$nobl'', @tgbl=''$tgbl''  ') 
			
		 // "; die();
		$qry = $this->db->query("
		
			 EXEC ('spu_nonhib_bl_recalculate @datadiff=''$datacsv'', @nobl=''$nobl'', @tgbl=''$tgbl''  ') 
			
		 ");
		 $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 view_inv_datauploaded(){
	
		$this->load->view('exim/view_inv_datauploaded');
	}
	
	function view_bc23_addbldata(){
		
			$this->load->view('exim/view_bc23_addbldata');
	
		
	}
	
	
	function view_bc23_nonhibprosesinsertinv(){
		
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param tgbl Tidak Ditemukan"}');
		$data["tgbl"] = $tgbl;
		$data["nobl"] = $nobl;
		
		$this->load->view('exim/view_bc23_nonhibprosesinsertinv', $data);
	}
	
	function data_bc23_inv_bl_blank(){
	
	
	
	}
	
	function view_cek_invoice(){
		$no_invoice = (isset($_POST['no_invoice']) && !empty($_POST['no_invoice']))?$_POST['no_invoice']:die('{"sts":"ERROR","desc":" Param No INV nobl Tidak Ditemukan"}');
		$inv_hdr_id = (isset($_POST['inv_hdr_id']) && !empty($_POST['inv_hdr_id']))?$_POST['inv_hdr_id']:die('{"sts":"ERROR","desc":" Param No INV nobl Tidak Ditemukan"}');
		
		$data['no_invoice'] = $no_invoice;
		$data['inv_hdr_id'] = $inv_hdr_id;
		
		$this->load->view('exim/view_cek_invoice', $data);
	}
	function updatenoinvoice(){
		$noinvoicebaru = (isset($_POST['noinvoicebaru']) && !empty($_POST['noinvoicebaru']))?trim($_POST['noinvoicebaru']):die('{"sts":"ERROR","desc":" Param No INV  Tidak Ditemukan"}');
		$inv_hdr_id = (isset($_POST['inv_hdr_id']) && !empty($_POST['inv_hdr_id']))?trim($_POST['inv_hdr_id']):die('{"sts":"ERROR","desc":" Param id Tidak Ditemukan"}');
		$noinvoicelama = (isset($_POST['noinvoicelama']) && !empty($_POST['noinvoicelama']))?trim($_POST['noinvoicelama']):die('{"sts":"ERROR","desc":" Param No INV LAMA  Tidak Ditemukan"}');
		$bc23dir = $this->config->item('APP_modulbc23');
		
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		
		
		
		$qry = $this->db->query("
		
		    declare @invoicebaru varchar(100), @invoicelama varchar(100), @inv_hdr_id bigint, @carmodul varchar(100)
			if (select count(*) FROM [dbo].[tbl_inv_hdr]  where [inv_hdr_id]=$inv_hdr_id and r3='$noinvoicelama' and status_inv = 1 and nobl <> '-' ) > 0
			begin
				select '05' as status, ' Data Sudah Di Proses' as description
				return
			end
			
			if (select count(*) FROM [dbo].[tbl_bc_in_dok] where DokNo like '%$noinvoicelama%') > 0
			begin
				select '05' as status, ' Data Sudah Di BC23' as description
				return
			end			
			
						
			update [dbo].[tbl_inv_hdr] set r3 = '$noinvoicebaru' where [inv_hdr_id]=$inv_hdr_id and r3='$noinvoicelama' and status_inv = 0 
			
			select '00' as status, ' Data Updated' 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 cekinvoicemodul(){
		$noinvoicelama = (isset($_POST['noinvoicelama']) && !empty($_POST['noinvoicelama']))?trim($_POST['noinvoicelama']):die('{"sts":"ERROR","desc":" Param No INV LAMA  Tidak Ditemukan"}');
		$bc23dir = $this->config->item('APP_modulbc23');
		
		$this->db->query("
			SET ANSI_NULLS ON
			SET ANSI_WARNINGS ON
		");
		
		
		
		$qry = $this->db->query("
		
		    declare @carmodul varchar(100), @carcc varchar(100)
				
			
			set @carmodul = (select top 1 car from  
			OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
						'Data Source=$bc23dir;
						Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'
						)...tblBC23Dok where DokNo like '%$noinvoicelama%') 
			
			set @carcc = (
				select top 1 car FROM [dbo].[tbl_bc_in_dok] where DokNo like '%$noinvoicelama%'
			)
			
			select '00' as status, ' Data Find' as description, @carmodul as carmodul, @carcc as carcc
			
		 ");
		 $rs = $qry->result_array();
        // $r = $qry->result();		
		// var_dump($r);die();
		$carcc = '';
		$carmodul = '';
		foreach ($rs as $row){
		    $carcc = $row['carcc'];
			$carmodul = $row['carmodul'];
			if ( trim($carcc) == '' &&  trim($carmodul) != '' ){
			
				$carcc = 'Data di CC masih kosong, bisa ditarik(di matching)';
			} else if ( trim($carcc) == '' &&  trim($carmodul) == '' ){
			
				$carcc = 'Mungkin Invoice Untuk Dokumen BC Lain';
			}
			
			$responce = array("sts" => $row['status'], "desc" => $row['description'], "carmodul" => $carmodul, "carcc" => $carcc );
		}  
		echo json_encode($responce);
		
	}
	
	function cekinvoicestatus(){
		$noinvoicelama = (isset($_POST['noinvoicecek']) && !empty($_POST['noinvoicecek']))?trim($_POST['noinvoicecek']):die('{"sts":"ERROR","desc":" Param No INV LAMA  Tidak Ditemukan"}');
		$noinv_hdr_id = (isset($_POST['noinv_hdr_id']) && !empty($_POST['noinv_hdr_id']))?trim($_POST['noinv_hdr_id']):die('{"sts":"ERROR","desc":" Param No INV LAMA  Tidak Ditemukan"}');
		
		$qry = $this->db->query("
			SELECT        tbl_inv_hdr.r3, tbl_inv_hdr.status_inv, tbl_bc_in_dok.DokKd, tbl_bc_in_hdr.CAR, tbl_bc_in_hdr.nobl, tbl_bc_in_hdr.tglbl, tbl_bc_in_hdr.typedok
			FROM            tbl_bc_in_hdr INNER JOIN
									 tbl_bc_in_dok ON tbl_bc_in_hdr.id_bc_in_hdr = tbl_bc_in_dok.id_bc_in_hdr RIGHT OUTER JOIN
									 tbl_inv_hdr ON tbl_bc_in_dok.DokNo = tbl_inv_hdr.r3
			WHERE        (tbl_bc_in_dok.DokKd = '380') AND (tbl_inv_hdr.status_inv = 0) and (tbl_inv_hdr.inv_hdr_id = $noinv_hdr_id) 
		 ");
		 $rs = $qry->result_array();
        // $r = $qry->result();		
		// var_dump($r);die();
		$nocar = '';
		$nobl = '';
		$typedok = '';
		$statusinv = '';
		$tglbl = '';
		foreach ($rs as $row){
		    $nocar = $row['CAR'];
			$nobl  = $row['nobl'];
			$typedok = $row['typedok']; 
			$statusinv = $row['status_inv'];
			$tglbl = $row['tglbl'];
			
			$responce = array("sts" => "00", "nocar" => $nocar, "nobl" => $nobl,  "statusinv" => $statusinv,  "typedok" => $typedok, "tglbl" =>  $tglbl );
		}  
			
		echo json_encode($responce);
	
	}
	
	function updatenoinvoiceblstatus(){
		$noinvoicelama = (isset($_POST['noinvoicelama'])  && !empty($_POST['noinvoicelama']))?trim($_POST['noinvoicelama']):die('{"sts":"ERROR","desc":" Param No INV LAMA  Tidak Ditemukan"}');
		$noinv_hdr_id = (isset($_POST['inv_hdr_id'])  && !empty($_POST['inv_hdr_id']))?trim($_POST['inv_hdr_id']):die('{"sts":"ERROR","desc":" Param No HDR Tidak Ditemukan"}');
		$nobl = (isset($_POST['noblsts'])  && !empty($_POST['noblsts']))?trim($_POST['noblsts']):die('{"sts":"ERROR","desc":" Param No BL Tidak Ditemukan"}');
		$tglbl= (isset($_POST['tglblsts'])  && !empty($_POST['tglblsts']))?trim($_POST['tglblsts']):die('{"sts":"ERROR","desc":" Param tgl BL  Tidak Ditemukan"}');
		$qry = $this->db->query("
			BEGIN TRY
			BEGIN TRANSACTION
				update tbl_inv_hdr set nobl = '$nobl', status_inv = 1, tgbl = '$tglbl' where tbl_inv_hdr.inv_hdr_id = $noinv_hdr_id and tbl_inv_hdr.r3 = '$noinvoicelama'
			COMMIT
			END TRY
			BEGIN CATCH
			-- Whoops, there was an error
			IF @@TRANCOUNT > 0
			 ROLLBACK

			-- Raise an error with the details of the exception
			DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
			Set @ErrMsg = ERROR_MESSAGE()
			set @ErrSeverity = ERROR_SEVERITY()
			select @ErrMsg as status,@ErrSeverity as description
			RAISERROR(@ErrMsg, @ErrSeverity, 1)
			END CATCH
			
			
			select '00' as status, ' SUKSES UPDATE' as description
			
		 ");
		 $rs = $qry->result_array();
		$description = "";
		$status = "";
		foreach ($rs as $row){
			$description = $row["description"];
			$status =  $row["status"];
		}  
		$responce = array("sts" => $status, "desc" => $description );	
		echo json_encode($responce);
	
	}
	
	function view_btnbc23dl_outstanding(){
	
		$this->load->view('exim/view_btnbc23dl_outstanding');
			
	}
	
	function downloadoutstandingpxls(){
		$tgawal = (isset($_GET['tgawal']) && !empty($_GET['tgawal']))?$_GET['tgawal']:die('{"sts":"ERROR","desc":" Param  tgawal Tidak Ditemukan"}');
	    $tgakhir = (isset($_GET['tgakhir']) && !empty($_GET['tgakhir']))?$_GET['tgakhir']:die('{"sts":"ERROR","desc":" Param  tglakhir Tidak Ditemukan"}');
	    
		$q = $this->db->query("
			select 
			a.[inv_hdr_id]
			  ,a.[r1]
			  ,a.[r2]
			  ,a.[r3]
			  ,a.[r4]
			  ,a.[r5]
			  ,a.[r6]
			  ,a.[r7]
			  ,a.[r8]
			  ,a.[r9]
			  ,a.[r10]
			  ,a.[r11]
			  ,a.[r12]
			  ,a.[r13]
			  ,a.[r14]
			  ,a.[r15]
			  ,a.[r16]
			  ,a.[r17]
			  ,a.[r18]
			  ,a.[r19]
			  ,a.[r20]
			  ,convert(varchar, a.[date_create], 101) as date_create
			  ,a.[status_inv]
			  ,a.[nobl]
			  ,a.[tgbl]
			  ,b.supplier_name
			  from
			  tbl_inv_hdr as a inner join dbo.tbl_data_supplier AS b on a.r2 = b.subcode_as400
			  
			  where a.[status_inv] = 0  
			  --and CONVERT(char(10), a.[r5],126) between '$tgawal' and '$tgakhir'
		
		");
		$rs = $q->result_array();
		$data1 = array("NO,NO_INV,TGL_INV,SUPP_CODE,SUPP_NAME,CONTAINER"  );
		$i = 1;
		foreach ($rs as $row){
			$data1[] = $i . "," . $row['r3'] . "," . $row['r5'] . ","  . $row['r2']  . "," . $row['supplier_name'] .  "," . $row['r4'] ;
			$i++;
		}
		$result = $data1;		
		header('Content-Type: text/csv');
		header('Content-Disposition: attachment; filename="rekap_inv_outstanding.csv"');
		
		$fp = fopen('php://output', 'w');
		foreach ( $result as $line ) {
			$val = explode(",", $line);
			fputcsv($fp, $val);
		}
		fclose($fp);
	
		
	}
	
	function bc23dl_deleteinvoice(){
		$inv_hdr_id = (isset($_POST['inv_hdr_id']) && !empty($_POST['inv_hdr_id']))?$_POST['inv_hdr_id']:die('{"sts":"ERROR","desc":" Param  ID invoice Tidak Ditemukan"}');
	    $qry = $this->db->query("
			declare @noiv varchar(100)
			declare @id_bc_in_hdr varchar(100)
			select @noiv=r3 from [dbo].[tbl_inv_hdr] where [inv_hdr_id] = $inv_hdr_id 
			select top 1 @id_bc_in_hdr=id_bc_in_hdr from tbl_bc_in_dok where [DokNo] like '%@noiv%'
			if @id_bc_in_hdr is not null
			begin
				select '06' as status,  ' TYPE ' +  (select typedok from tbl_bc_in_hdr where id_bc_in_hdr=@id_bc_in_hdr) + ' DATA SUDAH ADA DI BC CAR ' + (select CAR from tbl_bc_in_hdr where id_bc_in_hdr=@id_bc_in_hdr)  as description
				return
			end
			if (select [status_inv] from [tbl_inv_hdr] where inv_hdr_id=$inv_hdr_id) <> 0
			begin
				select '06' as status, ' Data Sudah Pernah Di Proses ' as description
				return
			end
			
			BEGIN TRY
			BEGIN TRANSACTION
				delete from [tbl_inv_hdr] where [inv_hdr_id] = $inv_hdr_id
				delete from [tbl_inv_dtl] where [inv_hdr_id] = $inv_hdr_id
			COMMIT
			END TRY
			BEGIN CATCH
			-- Whoops, there was an error
			IF @@TRANCOUNT > 0
			 ROLLBACK

			-- Raise an error with the details of the exception
			DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
			Set @ErrMsg = ERROR_MESSAGE()
			set @ErrSeverity = ERROR_SEVERITY()
			select @ErrMsg as status,@ErrSeverity as description
			RAISERROR(@ErrMsg, @ErrSeverity, 1)
			END CATCH
			
			
			select '00' as status, ' SUKSES DELETED' as description
			
		 ");
		 $rs = $qry->result_array();
		$description = "";
		$status = "";
		foreach ($rs as $row){
			$description = $row["description"];
			$status =  $row["status"];
		}  
		$responce = array("sts" => $status, "desc" => $description );	
		echo json_encode($responce);
	
	}
	
	function view_coo_inv_bc23(){
		$q = $this->db->query("
			SELECT [INVOICE_NO]
			from [v_coo_data] where part_no is null 
			group by INVOICE_NO
		");
		$rs = $q->result_array();
		$datanull='';
		foreach ($rs as $row){
			$datanull.=$row['INVOICE_NO'].'|';		
		}
		if ($datanull != ''){
			$data["test_null_inv"] = 'Part Number di Data Part HS Not Match Di Invoice Berikut, Silahkan Cek Di View Data : ' . $datanull;
		} else {
			$data["test_null_inv"]=$datanull;
		}
		$this->load->view('exim/view_coo_inv_bc23',$data);
	}
	function view_coo_inv_bc271(){
		$q = $this->db->query("
			SELECT [INVOICE_NO]
			from [v_coo_data] where part_no is null 
			group by INVOICE_NO
		");
		$rs = $q->result_array();
		$datanull='';
		foreach ($rs as $row){
			$datanull.=$row['INVOICE_NO'].'|';		
		}
		if ($datanull != ''){
			$data["test_null_inv"] = 'Part Number di Data Part HS Not Match Di Invoice Berikut, Silahkan Cek Di View Data : ' . $datanull;
		} else {
			$data["test_null_inv"]=$datanull;
		}
		$this->load->view('exim/view_coo_inv_bc271',$data);
	}
	function view_coo_groupbl_bc23(){
		$data["test"] = "";
		$this->load->view('exim/view_coo_groupbl_bc23',$data);
	}
	
	function view_coo_groupbl_bc271(){
		$data["test"] = "";
		$this->load->view('exim/view_coo_groupbl_bc271',$data);
	}
	
	function view_coo_inv_bc23_dtl(){
		$id_coo_data = (isset($_POST['id_coo_data']) && !empty($_POST['id_coo_data']))?$_POST['id_coo_data']:'';
				
		$q = $this->db->query("
			SELECT ITEM_NUMBER
			from [v_coo_data] where part_no is null and id_coo_data=$id_coo_data
		");
		$rs = $q->result_array();
		$datanull='';
		foreach ($rs as $row){
			$datanull.=$row['ITEM_NUMBER'].'|';		
		}
		if ($datanull != ''){
			$data["test_null_inv"] = 'Data Part Tidak Match dengan Part HS : '.$datanull;
		} else {
			$data["test_null_inv"]=$datanull;
		}
		$data["id_coo_data"]=$id_coo_data;
		$this->load->view('exim/view_coo_inv_bc23_dtl',$data);
	}
	
	function view_coo_inv_bc271_dtl(){
		$id_coo_data = (isset($_POST['id_coo_data']) && !empty($_POST['id_coo_data']))?$_POST['id_coo_data']:'';
				
		$q = $this->db->query("
			SELECT ITEM_NUMBER
			from [v_coo_data] where part_no is null and id_coo_data=$id_coo_data
		");
		$rs = $q->result_array();
		$datanull='';
		foreach ($rs as $row){
			$datanull.=$row['ITEM_NUMBER'].'|';		
		}
		if ($datanull != ''){
			$data["test_null_inv"] = 'Data Part Tidak Match dengan Part HS : '.$datanull;
		} else {
			$data["test_null_inv"]=$datanull;
		}
		$data["id_coo_data"]=$id_coo_data;
		$this->load->view('exim/view_coo_inv_bc271_dtl',$data);
	}
	
	function view_coo_inv_bc40(){
		$q = $this->db->query("
			SELECT [INVOICE_NO]
			from [v_coo_data] where part_no is null 
			group by INVOICE_NO
		");
		$rs = $q->result_array();
		$datanull='';
		foreach ($rs as $row){
			$datanull.=$row['INVOICE_NO'].'|';		
		}
		if ($datanull != ''){
			$data["test_null_inv"] = 'Part Number di Data Part HS Not Match Di Invoice Berikut, Silahkan Cek Di View Data : ' . $datanull;
		} else {
			$data["test_null_inv"]=$datanull;
		}
		$this->load->view('exim/view_coo_inv_bc40',$data);
	}
	
	function view_coo_inv_bc40_dtl(){
		$id_coo_data = (isset($_POST['id_coo_data']) && !empty($_POST['id_coo_data']))?$_POST['id_coo_data']:'';
				
		$q = $this->db->query("
			SELECT ITEM_NUMBER
			from [v_coo_data] where part_no is null and id_coo_data=$id_coo_data
		");
		$rs = $q->result_array();
		$datanull='';
		foreach ($rs as $row){
			$datanull.=$row['ITEM_NUMBER'].'|';		
		}
		if ($datanull != ''){
			$data["test_null_inv"] = 'Data Part Tidak Match dengan Part HS : '.$datanull;
		} else {
			$data["test_null_inv"]=$datanull;
		}
		$data["id_coo_data"]=$id_coo_data;
		$this->load->view('exim/view_coo_inv_bc40_dtl',$data);
	}
	
	function view_coo_groupbl_bc40(){
		$data["test"] = "";
		$this->load->view('exim/view_coo_groupbl_bc40',$data);
	}
	
	function v_coo_data(){
		$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_coo'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$where = " 1=1 ";
		$id_coo_data = (isset($_POST['id_coo_data1']) && !empty($_POST['id_coo_data1']))?$_POST['id_coo_data1']:'';
		$where = " id_coo_data=$id_coo_data ";
		if ($search){
		   	if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					$searchString = $k->data;
					$where .= " and " . $k->field . " like  '%".$searchString."%'"; 
				}
			}
		}
		
		/*if ($search == 'true'){
			if (!empty($filters)){
				$array = json_decode($filters);
				foreach($array->rules as $arr=>$k){
					
					if ( $k->field == "filename" ) {
						$searchString = $k->data;
						$where .= " and filename like  '%".$searchString."%'"; 
					} else if ( $k->field == "date_upload" ) {
						$searchString = $k->data;
						$where .= " and CONVERT(VARCHAR(10),[date_upload],101)  =  '$searchString'"; 
					} else if ( $k->field == "status_upload" ) {
						$searchString = $k->data;
						$where .= " and status_upload like  '%".$searchString."%'"; 
					}  else if ( $k->field == "create_by" ) {
						$searchString = $k->data;
						$where .= " and create_by like  '%".$searchString."%'"; 
					} 
				}
			}
		}*/
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_invoice->v_coo_dataAll($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_invoice->v_coo_data_countAll($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_coo_data']=$r->id_coo_data;
			$responce->rows[$i]['VENDOR']=$r->VENDOR;
			$responce->rows[$i]['INVOICE_NO']=$r->INVOICE_NO;
			$responce->rows[$i]['CONTIANER_NO']=$r->CONTIANER_NO;
			$responce->rows[$i]['INVOICE_DATE']=$r->INVOICE_DATE;
			$responce->rows[$i]['ETD_VEBDER']=$r->ETD_VEBDER;
			$responce->rows[$i]['ETD_PORT']=$r->ETD_PORT;
			$responce->rows[$i]['ETA_PORT']=$r->ETA_PORT;
			$responce->rows[$i]['ETA_FACT']=$r->ETA_FACT;
			$responce->rows[$i]['TRANSPORT_WAY']=$r->TRANSPORT_WAY;
			$responce->rows[$i]['CARA_BAYAR']=$r->CARA_BAYAR;
			$responce->rows[$i]['ONEROUS']=$r->ONEROUS;
			$responce->rows[$i]['SHIP_FROM']=$r->SHIP_FROM;
			$responce->rows[$i]['VIA']=$r->VIA;
			$responce->rows[$i]['SHIP_TO']=$r->SHIP_TO;
			$responce->rows[$i]['PAY_BY']=$r->PAY_BY;
			$responce->rows[$i]['SEAL_NO']=$r->SEAL_NO;
			$responce->rows[$i]['SHIP_NAME']=$r->SHIP_NAME;
			$responce->rows[$i]['SHIP_COMPANY']=$r->SHIP_COMPANY;
			$responce->rows[$i]['ORDER_NO']=$r->ORDER_NO;
			$responce->rows[$i]['ITEM_NUMBER']=$r->ITEM_NUMBER;
			$responce->rows[$i]['DESCRIPTION']=$r->DESCRIPTION;
			$responce->rows[$i]['ARRIV_PLAN_NUMBER']=$r->ARRIV_PLAN_NUMBER;
			$responce->rows[$i]['UOM']=$r->UOM;
			$responce->rows[$i]['PRICE']=$r->PRICE;
			$responce->rows[$i]['CURRENT_MONEY']=$r->CURRENT_MONEY;
			$responce->rows[$i]['CTN_NO_PREFIX']=$r->CTN_NO_PREFIX;
			$responce->rows[$i]['CTN_NO_FROM']=$r->CTN_NO_FROM;
			$responce->rows[$i]['CTN_NO_TO']=$r->CTN_NO_TO;
			$responce->rows[$i]['CTN_QUANTITY']=$r->CTN_QUANTITY;
			$responce->rows[$i]['PACKING_QTY']=$r->PACKING_QTY;
			$responce->rows[$i]['NET_WEIGHT']=$r->NET_WEIGHT;
			$responce->rows[$i]['GROSS_WEIGHT']=$r->GROSS_WEIGHT;
			$responce->rows[$i]['WEIGHT_UOM']=$r->WEIGHT_UOM;
			$responce->rows[$i]['VOLUME']=$r->VOLUME;
			$responce->rows[$i]['VOLUME_UOM']=$r->VOLUME_UOM;
			$responce->rows[$i]['BL_DATE']=$r->BL_DATE;
			$responce->rows[$i]['BL_NO']=$r->BL_NO;
			$responce->rows[$i]['FREIGHT']=$r->FREIGHT;
			$responce->rows[$i]['EURO_NO']=$r->EURO_NO;
			$responce->rows[$i]['PALET_NO']=$r->PALET_NO;
			$responce->rows[$i]['HS_CODE']=$r->HS_CODE;
			$responce->rows[$i]['COUNTRY_OF']=$r->COUNTRY_OF;
			$responce->rows[$i]['AKHIR']=$r->AKHIR;
			$responce->rows[$i]['file_name']=$r->file_name;
			$responce->rows[$i]['status_inv']=$r->status_inv;
			$responce->rows[$i]['type_bc']=$r->type_bc;
			$responce->rows[$i]['car']=$r->car;
			$responce->rows[$i]['nobc']=$r->nobc;
			$responce->rows[$i]['tglbc']=$r->tglbc;
			$responce->rows[$i]['nobl']=$r->nobl;
			$responce->rows[$i]['tgbl']=$r->tgbl;
			$responce->rows[$i]['upload_by']=$r->upload_by;
			$responce->rows[$i]['upload_date']=$r->upload_date;
			$responce->rows[$i]['id_coo']=$r->id_coo;
			$responce->rows[$i]['part_no']=$r->part_no;
			$responce->rows[$i]['part_name']=$r->part_name;
			$responce->rows[$i]['part_name_customs']=$r->part_name_customs;
			$responce->rows[$i]['hskode']=$r->hskode;
			$responce->rows[$i]['kode_barang']=$r->kode_barang;
			$responce->rows[$i]['supplier_name']=$r->supplier_name;
			$responce->rows[$i]['supplier_address']=$r->supplier_address;
			$responce->rows[$i]['subcode_as400']=$r->subcode_as400;
			$responce->rows[$i]['supplier_country']=$r->supplier_country;
			$responce->rows[$i]['kd_negara']=$r->kd_negara;
			$responce->rows[$i]['kd_negara2']=$r->kd_negara2;
			$responce->rows[$i]['kd_neg']=$r->kd_neg;
			$responce->rows[$i]['amount']=$r->amount;
			
			$i++;
		}
		echo json_encode($responce);	
	
	}
	
	function loaddatanonobl_coo(){
		$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_coo_data'; // get index row - i.e. user click to sort
        $sord = isset($_POST['sord'])?$_POST['sord']:'0'; // get the direction	
		
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:'';
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:'';
		
		
		//$where = " a.status_inv=0 ";
		$where = " 1=1 ";
		$where .= " and nobl =  '".$nobl."'";
		$where .= " and tgbl =  '".$tgbl."'";
		
		
		$start = $limit*$page - $limit;
		if ($start<0) $start=0;
		
		$records = $this->mdl_invoice->tbl_coo_dataAll($limit,$start,$sidx,$sord, $where);
        $count = $this->mdl_invoice->tbl_coo_data_countAll($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){
		    if ($r->status_inv == 'NEW'){
					  $status_inv = 'INVOICE NEW ADDED';
			} else if ($r->status_inv != 'NEW'){
				 $status_inv = 'DATA ALREADY PROCESSED';
			}
			$responce->rows[$i]['id_coo_data']=$r->id_coo_data; 
			$responce->rows[$i]['invno']=$r->no_invoice; 
			$responce->rows[$i]['blawbno']=$r->nobl; 
			$responce->rows[$i]['tglblawb']=$r->tgbl;
			$responce->rows[$i]['status_inv']=$status_inv;
			$responce->rows[$i]['status_invx']=$r->status_inv;
			$responce->rows[$i]['vendor']=$r->vendor;
			$responce->rows[$i]['type_bc']=$r->type_bc;
			
			$i++;
		}
		echo json_encode($responce);
		
	}
	
	function prosesdatabl_coo(){
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}'); 
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}');
		
		$datadiff = (isset($_POST['datadiff']) && !empty($_POST['datadiff']))?$_POST['datadiff']:'0'; 
		$datadiff=json_decode($datadiff);
		$datacsv = '';
		$previous_vend = '';
		$previous_bl = '';
		foreach ($datadiff as $data ){
			if ($previous_vend == ''){
				$previous_vend = (isset($data->vendor) && !empty($data->vendor ))?$data->vendor :die('{"sts":"ERROR","desc":" Param tglblawb Tidak Ditemukan"}');
			}
			//if ($previous_vend != $data->vendor){
			//	echo '{"sts":"ERROR","desc":" VENDOR TIDAK SAMA ' . $previous_vend . " <> "  . $data->vendor  .  '"}';
			//	die();
			//}
			
			if ($previous_bl == ''){
				$previous_bl = (isset($data->blawbno) && !empty($data->blawbno ))?$data->blawbno :die('{"sts":"ERROR","desc":" Param blawbno Tidak Ditemukan"}');
			}
			if ($previous_bl != $data->blawbno){
				echo '{"sts":"ERROR","desc":" NO BL TIDAK SAMA ' . $previous_bl . " <> "  . $data->blawbno  .  '"}';
				die();
			}
			
			$id_coo_data = (isset($data->id_coo_data) && !empty($data->id_coo_data ))?$data->id_coo_data :die('{"sts":"ERROR","desc":" Param id_coo_data Tidak Ditemukan"}');
			$invno = (isset($data->invno) && !empty($data->invno ))?$data->invno :die('{"sts":"ERROR","desc":" Param invno Tidak Ditemukan"}');
			$blawbno = (isset($data->blawbno) && !empty($data->blawbno ))?$data->blawbno :die('{"sts":"ERROR","desc":" Param blawbno Tidak Ditemukan"}');
			$tglblawb = (isset($data->tglblawb) && !empty($data->tglblawb ))?$data->tglblawb :die('{"sts":"ERROR","desc":" Param tglblawb Tidak Ditemukan"}');
			$vendor = (isset($data->vendor) && !empty($data->vendor ))?$data->vendor :die('{"sts":"ERROR","desc":" Param tglblawb Tidak Ditemukan"}');
			$type_bc = (isset($data->type_bc) && !empty($data->type_bc ))?$data->type_bc :die('{"sts":"ERROR","desc":" Param tglblawb Tidak Ditemukan"}');
			$previous_vend = $vendor;
			$datacsv .= $id_coo_data . ',' . $invno . ',' . $blawbno . ',' . $tglblawb . ',' . $type_bc; 
			$datacsv .= "|";
		}
		//echo $datacsv;	
		//var_dump($datadiff); 
		//die();
		
		
		$qry = $this->db->query("
			declare @datadiff varchar(max), @nobl varchar(50), @tgbl date
			declare  @datasplit varchar(8000)
			declare @noblc varchar(50), @type_bcc varchar(50)
    		declare @tgblc date
			set @datadiff = '$datacsv'
			set @nobl = '$nobl'
			set @tgbl = '$tgbl'
			if (select COUNT(*) as cnt from dbo.tbl_bc_in_hdr where nobl=@nobl and tglbl=@tgbl) > 0 
		    begin
				select '02' as status, ' Data sudah diproses menjadi data bc, '+CHAR(13)+CHAR(10)+' silahkan recalculate data jika ingin mengubah invoice' as description	
				return								
		    end
			 
			BEGIN TRY
				DECLARE a_cursor CURSOR FOR select f_result from dbo.fn_ParseCSVString (@datadiff, '|')
				OPEN a_cursor
				FETCH NEXT FROM a_cursor INTO @datasplit
				WHILE @@FETCH_STATUS = 0 
				BEGIN -- begin here
					
					set @noblc = (select data from dbo.Split(@datasplit, ',') where id=3)
					set @tgblc = (select data from dbo.Split(@datasplit, ',') where id=4)
					set @type_bcc = (select data from dbo.Split(@datasplit, ',') where id=5)
					update dbo.tbl_coo_data set  nobl = @noblc, tgbl =  @tgblc, type_bc=@type_bcc   where [id_coo_data] = (select data from dbo.Split(@datasplit, ',') where id=1)
				FETCH NEXT FROM a_cursor INTO @datasplit
				END
				CLOSE a_cursor
				DEALLOCATE a_cursor	
				
				select '00' as status, ' SUKSES UPDATED' as description
				
					
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'error' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
		 ");
		 $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 canceldatabl_coo(){
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}'); 
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param  Tidak Ditemukan"}');
		
		$datadiff = (isset($_POST['datadiff']) && !empty($_POST['datadiff']))?$_POST['datadiff']:'0'; 
		$datadiff=json_decode($datadiff);
		$datacsv = '';
		$previous_vend = '';
		$previous_bl = '';
		foreach ($datadiff as $data ){
			if ($previous_vend == ''){
				$previous_vend = (isset($data->vendor) && !empty($data->vendor ))?$data->vendor :die('{"sts":"ERROR","desc":" Param tglblawb Tidak Ditemukan"}');
			}
			//if ($previous_vend != $data->vendor){
			//	echo '{"sts":"ERROR","desc":" VENDOR TIDAK SAMA ' . $previous_vend . " <> "  . $data->vendor  .  '"}';
			//	die();
			//}
			if ($previous_bl == ''){
				$previous_bl = (isset($data->blawbno) && !empty($data->blawbno ))?$data->blawbno :die('{"sts":"ERROR","desc":" Param blawbno Tidak Ditemukan"}');
			}
			if ($previous_bl != $data->blawbno){
				echo '{"sts":"ERROR","desc":" NO BL TIDAK SAMA ' . $previous_bl . " <> "  . $data->blawbno  .  '"}';
				die();
			}
			
			$datacsv .= $data->id_coo_data . ',' . $data->invno . ',' . '' . ',' . ''; 
			$datacsv .= "|";
		}
		
		
		$qry = $this->db->query("
			declare @datadiff varchar(max), @nobl varchar(50), @tgbl date
			declare  @datasplit varchar(8000)
			declare @noblc varchar(50)
    		declare @tgblc date
			set @datadiff = '$datacsv'
			set @nobl = '$nobl'
			set @tgbl = '$tgbl'
			
			BEGIN TRY	
				declare @id_coo_data bigint
				DECLARE b_cursor CURSOR FOR select f_result from dbo.fn_ParseCSVString (@datadiff, '|')
				OPEN b_cursor
				FETCH NEXT FROM b_cursor INTO @datasplit
				WHILE @@FETCH_STATUS = 0 
				BEGIN -- begin here
					set @id_coo_data = (select data from dbo.Split(@datasplit, ',') where id=1)
					if (select COUNT(*) as cnt from dbo.tbl_coo_data where id_coo_data=@id_coo_data and status_inv = 'BCPROSES') >0
					begin
						select '02' as status, ' Ada data yang sudah diproses menjadi data bc, '+CHAR(13)+CHAR(10)+' silahkan recalculate data jika ingin mengubah invoice '+CHAR(13)+CHAR(10)+(select data from dbo.Split(@datasplit, ',') where id=2) as description	
						CLOSE b_cursor
						DEALLOCATE b_cursor
						return	
					end
				
				FETCH NEXT FROM b_cursor INTO @datasplit
				END
				CLOSE b_cursor
				DEALLOCATE b_cursor	
				
				
				DECLARE a_cursor CURSOR FOR select f_result from dbo.fn_ParseCSVString (@datadiff, '|')
				OPEN a_cursor
				FETCH NEXT FROM a_cursor INTO @datasplit
				WHILE @@FETCH_STATUS = 0 
				BEGIN -- begin here
					
					set @noblc = (select data from dbo.Split(@datasplit, ',') where id=3)
					set @tgblc = (select data from dbo.Split(@datasplit, ',') where id=4)
					update dbo.tbl_coo_data set  nobl = @noblc, tgbl =  NULL, type_bc=NULL where [id_coo_data] = (select data from dbo.Split(@datasplit, ',') where id=1)
				FETCH NEXT FROM a_cursor INTO @datasplit
				END
				CLOSE a_cursor
				DEALLOCATE a_cursor	
				select '00' as status, ' SUKSES CANCLED' as description
				
				
			END TRY
			BEGIN CATCH 
				SELECT 
				'error' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
			
		 ");
		$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 view_coo_bc23_prosesinsertinv(){
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param tgbl Tidak Ditemukan"}');
		$data["tgbl"] = $tgbl;
		$data["nobl"] = $nobl;
		$this->load->view('exim/view_coo_bc23_prosesinsertinv',$data);
	
	}
	
	function view_coo_bc271_prosesinsertinv(){
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param tgbl Tidak Ditemukan"}');
		$data["tgbl"] = $tgbl;
		$data["nobl"] = $nobl;
		$this->load->view('exim/view_coo_bc271_prosesinsertinv',$data);
	
	}
	
	function view_coo_bc40_prosesinsertinv(){
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
		$tgbl = (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param tgbl Tidak Ditemukan"}');
		$data["tgbl"] = $tgbl;
		$data["nobl"] = $nobl;
		$this->load->view('exim/view_coo_bc40_prosesinsertinv',$data);
	
	}
	
	function updatestatusproses_coo(){
		$userid=$this->session->userdata('DX_username');
		
		//$typebc = 'BC23';
		$noinv = (isset($_POST['noinv']) && !empty($_POST['noinv']))?$_POST['noinv']:die('{"sts":"ERROR","desc":" Param INV Tidak Ditemukan"}');
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
		$tgbl= (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param tg bl Tidak Ditemukan"}');
		$typebc = (isset($_POST['typebc']) && !empty($_POST['typebc']))?$_POST['typebc']:die('{"sts":"ERROR","desc":" Param TIPE BC Tidak Ditemukan"}');
		$kode_vendor = (isset($_POST['kode_vendor']) && !empty($_POST['kode_vendor']))?$_POST['kode_vendor']:die('{"sts":"ERROR","desc":" Param Kode Vendor Tidak Ditemukan"}');
		
		$noinv=json_decode($noinv);
		$noinvcsv = '';
		$prefix = "";
		foreach ($noinv as $data ){
			$id_coo_data = (isset($data->id_coo_data) && !empty($data->id_coo_data ))?$data->id_coo_data :die('{"sts":"ERROR","desc":" Param inv_hdr_id Tidak Ditemukan"}');
			$invno = (isset($data->invno) && !empty($data->invno ))?$data->invno :die('{"sts":"ERROR","desc":" Param invno Tidak Ditemukan"}');
			$noinvcsv .= $prefix . $id_coo_data. ',' . $invno; 
			$prefix = "|";
		}
		//echo $noinvcsv;
		//die();
		//$listinv = substr($noinvcsv, 0,  strlen($noinvcsv) -1); 
		 // echo "EXEC  [sp_insertbc23] @nobl = N'$nobl', @tgbl = '$tgbl', @typebc = N'$typebc',@noinv = '$listinv'";
		// var_dump($noinv); die();
		$typebc = 'BC23';
		$qry=$this->db->query("
		    declare @nobl as varchar(20), @tgbl as date, @typebc as varchar(5), @noinv as varchar(MAX) 
		    declare @kode_vendor varchar(20)
		    set @nobl = '$nobl'  	
		    set @tgbl = '$tgbl'
		    set @typebc = '$typebc'
		    set @noinv = '$noinvcsv' 
		    set @kode_vendor = '$kode_vendor'
		    
		    if (select count(*) from [tbl_data_supplier] where subcode_as400 = @kode_vendor)<1
		    begin
		    	SELECT 
				'error' AS status
				,' DATA SUPLIER TIDAK DITEMUKAN' AS description;
		    end
		    
		    declare @supplier_name varchar(50), @supplier_address varchar(100), @supplier_country varchar(10)
		    select top 1 
		    	@supplier_name=supplier_name
		    	,@supplier_address=supplier_address
		    	,@supplier_country=supplier_country
		    	
		    from 
		    tbl_data_supplier  where subcode_as400 = @kode_vendor		    
		    
		    if @supplier_address is null or @supplier_country is null
		    begin
		    	SELECT 
				'error' AS status
				,' DATA SUPLIER TIDAK DITEMUKAN, LENGKAPI DAHULU DATA SUPPLIER <br />' + @kode_vendor AS description;
				return
		    end
		    
		    declare @tbl_listinv_bl table  ( id_hdr bigint,noinv varchar(60))
			BEGIN TRY
				declare @idata varchar(50)
				DECLARE a_cursor CURSOR FOR 
				select data from  dbo.Split( @noinv, CHAR(124))
				OPEN a_cursor
				FETCH NEXT FROM a_cursor INTO @idata
				WHILE @@FETCH_STATUS = 0 
				BEGIN -- begin here
					insert into @tbl_listinv_bl (id_hdr, noinv) values((select data from  dbo.Split( @idata, ',') where ID = 1), (select data from  dbo.Split( @idata, ',') where ID = 2))
				FETCH NEXT FROM a_cursor INTO @idata
				END
				CLOSE a_cursor
				DEALLOCATE a_cursor
				declare @ihdr bigint, @inoinv varchar(50)
	
				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 = @tgbl ) > 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  nobl = @nobl and tgbl = @tgbl
					group by 
						ITEM_NUMBER, 
						kd_neg, 
						UOM
						,[DESCRIPTION]
						,HS_CODE
						,hskode 
						) x
				)

				DECLARE i_cursor CURSOR FOR 
				select id_hdr, noinv from @tbl_listinv_bl
				OPEN i_cursor
				FETCH NEXT FROM i_cursor INTO @ihdr, @inoinv 
				WHILE @@FETCH_STATUS = 0 
				BEGIN -- begin here
					if (select COUNT(*) as cnt from dbo.tbl_coo_data where nobl = @nobl and id_coo_data=@ihdr) = 0
					begin
						select '01' as status, 'Data Not Found '+@nobl+' idhdr= '+cast(@ihdr as varchar)+' noinv= '+@inoinv  as [description]
						return	
					end	
	
				FETCH NEXT FROM i_cursor INTO @ihdr, @inoinv
				END
				CLOSE i_cursor
				DEALLOCATE i_cursor

				if (  select count(*)
				FROM
				  dbo.[v_coo_data]
				WHERE
				  nobl = @nobl and tgbl = @tgbl 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 nobl = @nobl and tgbl = @tgbl  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 nobl = @nobl and tgbl = @tgbl and ISNUMERIC(kd_neg) = 1 ) > 0
				begin
					select '01' as status, 'ADA DATA NEGARA YANG SALAH..MOHON DILENGKAPI'  as [description]
					return	
				end
				
				declare @pelmuat varchar(20), @pelbkr varchar(20),  @peltransit varchar(20)
				
				set @pelmuat = (
					ISNULL((SELECT        TOP (1) kd_pel_bc
                                 FROM            dbo.tbl_coo_pelln
                                 WHERE        (nama_pel = (select top 1 x.SHIP_FROM from [v_coo_data] x where x.nobl = @nobl and x.tgbl = @tgbl and  x.SHIP_FROM is not null))),  (select top 1 x.SHIP_FROM from [v_coo_data] x where nobl = @nobl and tgbl = @tgbl and  x.SHIP_FROM is not null))
				)
				
				set @pelbkr = (
					(CASE WHEN (select top 1 x.TRANSPORT_WAY from [v_coo_data] x where x.nobl = @nobl and x.tgbl = @tgbl and  x.TRANSPORT_WAY is not null) = 'A' THEN 'IDCGK' WHEN (select top 1 x.TRANSPORT_WAY from [v_coo_data] x where x.nobl = @nobl and x.tgbl = @tgbl and  x.TRANSPORT_WAY is not null) = 'B' THEN 'IDTPP' ELSE (select top 1 x.SHIP_TO from [v_coo_data] x where x.nobl = @nobl and x.tgbl = @tgbl and  x.SHIP_FROM is not null) END)
				) 
				
				set @peltransit = (
					ISNULL((SELECT        TOP (1) kd_pel_bc
                                 FROM            dbo.tbl_coo_pelln
                                 WHERE        (nama_pel = (select top 1 x.VIA from [v_coo_data] x where x.nobl = @nobl and x.tgbl = @tgbl and  x.VIA is not null))),  (select top 1 x.VIA from [v_coo_data] x where nobl = @nobl and tgbl = @tgbl and  x.VIA is not null))
				)
				
				insert into dbo.tbl_bc_in_hdr (
					[CAR]
					,statusdok 
					,typedok
					,bc_in_from_in
					,KdKpbc
					,Tujuan
					,JnsBarang
					,TujuanKirim
					,PasokNama
					,PasokAlmt
					,PasokNeg
					,AngkutNama
					,AngkutFl
					,Moda
					,KdVal
					,NilInv
					,KdHrg
					,JmBrg
					,Registrasi
					,UsahaId
					,UsahaNpwp
					,UsahaNama
					,UsahaAlmt
					,UsahaStatus
					,KdTPB
					,ApiKd
					,ApiNo
					,KdKpbcBongkar
					,KdKpbcAwas
					,Namattd
					,KotaTtd
					,nobl
					,tglbl
					,Ndpbm
					,Fob
					,PelBkr
					,PelMuat
					,PelTransit
					,TglTtd
					,Bruto
					,Netto
					,[create_date]
				      ,[create_by]
				      ,[update_date]
				      ,[update_by]
					) 
				select 
					'-'
					,'DATA BARU' 
					,@typebc
					,'COO' --
					,'150300'
					,'1'
					,'01'
					,'02'
					,@supplier_name
		    		,@supplier_address
		    		,@supplier_country
					 ,SHIP_NAME
					,NULL
					,(case when TRANSPORT_WAY = 'T' then '3' when TRANSPORT_WAY = 'B' then '1' else '4' end)
					,[CURRENT_MONEY]
					,sum(amount) as NilInv
					,(case when CARA_BAYAR = 'FOB' then '3' when CARA_BAYAR = 'CIF' then '1' else '2' end)
					,@jmldetil
					,(select top 1 Registrasi from tblImportir)
					,(select top 1 IMPID from tblImportir)
					,(select top 1 IMPNPWP from tblImportir)
					,(select top 1 IMPNAMA from tblImportir)
					,(select top 1 impalmt from tblImportir)
					,(select top 1 usahastatus from tblImportir)
					,(select top 1 KdTPB from tblImportir)
					,(select top 1 ApiKd from tblImportir)
					,(select top 1 ApiNo from tblImportir)
					,'150300'
					,'150300'
					,(select top 1 Namattd from tblImportir)
					,(select top 1 KotaTtd from tblImportir)
					,@nobl
					,@tgbl
					,NULL as Ndpbm
					,sum(amount) as Fob
					, @pelbkr AS PelBkr 
					, @pelmuat  as PelMuat
					, @peltransit  as PelTransit
					,GETDATE() as TglTtd
					,isnull(sum(isnull(GROSS_WEIGHT,0)),0) as Bruto
					,isnull(sum(isnull(NET_WEIGHT,0)),0) as Netto
					,GETDATE()
					,'$userid'
					,GETDATE()
					,'$userid'
				from 
				[dbo].[v_coo_data]
				where nobl = @nobl and tgbl = @tgbl
				 group by 
				SHIP_NAME
			   ,TRANSPORT_WAY
			   ,CARA_BAYAR
			   ,[CURRENT_MONEY]

			   declare  @idhdr bigint
			   set @idhdr = @@IDENTITY
			  
			   INSERT INTO tbl_bc_in_hdr_h_status
						 (id_bc_in_hdr, statusdok, status_date)
			   VALUES  (@idhdr, (select top 1 statusdok from tbl_bc_in_hdr where [id_bc_in_hdr] = @idhdr), GETDATE())
			   
			   -- detil inv begin here
				insert into dbo.tbl_bc_in_dtl (
					[CAR]
					,Serial
					,BrgUrai
					,DNilInv
					,NoHs
					,id_bc_in_hdr
					,JnsBarangDtl
					,TujuanKirimDtl
					,SeriTrp
					,Merk
					,Tipe
					,SpfLain
					,KdBrg
					,JmlSat
					,KdSat
					,BrgAsal
					,Penggunaan
					,KemasJn
					,KemasJm
					,NettoDtl
				) select 
				  '-'
				  ,row_number() over(order by (select 0))	
				  ,isnull([DESCRIPTION],(select top 1 part_name from tbl_data_part_hs where part_no = [v_coo_data].ITEM_NUMBER ))   as BrgUrai
				  ,sum(amount)
				  ,dbo.fnRemoveNonNumericCharacters(ISNULL(hskode, HS_CODE))
				  ,@idhdr
				  ,'01'
				  ,'02'
				  ,'0'
				  ,NULL
				  ,ITEM_NUMBER
				  ,NULL
				  ,kode_barang
				  ,SUM(CASE ISNUMERIC(ARRIV_PLAN_NUMBER) WHEN 1 THEN CAST(ARRIV_PLAN_NUMBER AS float) ELSE null END )
				  ,isnull((select top 1 [kdsat] from [tbl_coo_satuan] where [kode_sat_coo] = [v_coo_data].UOM), [v_coo_data].UOM) as kdsat
				  ,kd_neg
				  ,'1'
				  ,'CT'
				  ,SUM(CASE ISNUMERIC(CTN_QUANTITY) WHEN 1 THEN CAST(CTN_QUANTITY AS float) ELSE null END) 
				  ,SUM(CASE ISNUMERIC(NET_WEIGHT) WHEN 1 THEN CAST(NET_WEIGHT AS float) ELSE null END)
	  
				from 
				[dbo].[v_coo_data]
				where nobl = @nobl and tgbl = @tgbl
				group by 
				ITEM_NUMBER, 
				kd_neg, 
				UOM
				,[DESCRIPTION]
				,HS_CODE
				,hskode
				,kode_barang
				-- end detil
			   -- update dokumen
				insert into dbo.tbl_bc_in_dok (
					CAR
					,DokKd
					,DokNo
					,DokTg		
					,id_bc_in_hdr
				)select 
				  '-'
				  ,'380'
				  ,INVOICE_NO
				  ,INVOICE_DATE
				  ,@idhdr
				from [dbo].[v_coo_data]
				where nobl = @nobl and tgbl = @tgbl
			  	group by INVOICE_NO, INVOICE_DATE	
			
			insert into dbo.tbl_bc_in_dok (
				CAR
					,DokKd
					,DokNo
					,DokTg		
					,id_bc_in_hdr
			) values (
				'-'
				,(CASE WHEN (select top 1 x.TRANSPORT_WAY from [v_coo_data] x where x.nobl = @nobl and x.tgbl = @tgbl and  x.TRANSPORT_WAY is not null) = 'A' THEN '740' WHEN (select top 1 x.TRANSPORT_WAY from [v_coo_data] x where x.nobl = @nobl and x.tgbl = @tgbl and  x.TRANSPORT_WAY is not null) = 'B' THEN '705' ELSE '640' END)
				,@nobl
				,@tgbl
				,@idhdr
			)
			
			
			 -- insert to partname detil bc in
   				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
   				  ,@idhdr
				  ,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 = @idhdr and KdBrg=ITEM_NUMBER)
   				FROM
				  [dbo].[v_coo_data]
				where nobl = @nobl and tgbl = @tgbl
 				
 				insert into tbl_bc_in_kms (
 					[id_bc_in_hdr]
 					,[JnKemas]
				      ,[JmKemas]
				      ,[merkkemas]
 				) select 
 					@idhdr
 				    ,'CT'
 				    ,SUM(CASE ISNUMERIC(CTN_QUANTITY) WHEN 1 THEN CAST(CTN_QUANTITY AS float) ELSE null END) 
				    ,'-'
				from    
 				[dbo].[v_coo_data]
				where nobl = @nobl and tgbl = @tgbl
 				
				update tbl_coo_data set type_bc = @typebc, id_bc_in_hdr = @idhdr, status_inv = 'BCPROSES' 
 				where nobl = @nobl and tgbl = @tgbl
				select '00' as status, ' SUKSES DATA GROUP/BL/SURAT JALAN ' + @nobl as description

			END TRY
			BEGIN CATCH 
				SELECT 
				'error' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
		");
		$rs = $qry->result_array();	
		// var_dump($rs);
		// die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
		
		
		
	}
	
	
	function updatestatusproses_coo_lokal(){
		$userid=$this->session->userdata('DX_username');
		
		//$typebc = 'BC23';
		$noinv = (isset($_POST['noinv']) && !empty($_POST['noinv']))?$_POST['noinv']:die('{"sts":"ERROR","desc":" Param INV Tidak Ditemukan"}');
		$nobl = (isset($_POST['nobl']) && !empty($_POST['nobl']))?$_POST['nobl']:die('{"sts":"ERROR","desc":" Param nobl Tidak Ditemukan"}');
		$tgbl= (isset($_POST['tgbl']) && !empty($_POST['tgbl']))?$_POST['tgbl']:die('{"sts":"ERROR","desc":" Param tg bl Tidak Ditemukan"}');
		$typebc = (isset($_POST['typebc']) && !empty($_POST['typebc']))?$_POST['typebc']:die('{"sts":"ERROR","desc":" Param TIPE BC Tidak Ditemukan"}');
		
		$noinv=json_decode($noinv);
		$noinvcsv = '';
		$prefix = "";
		foreach ($noinv as $data ){
			$id_coo_data = (isset($data->id_coo_data) && !empty($data->id_coo_data ))?$data->id_coo_data :die('{"sts":"ERROR","desc":" Param inv_hdr_id Tidak Ditemukan"}');
			$invno = (isset($data->invno) && !empty($data->invno ))?$data->invno :die('{"sts":"ERROR","desc":" Param invno Tidak Ditemukan"}');
			$noinvcsv .= $prefix . $id_coo_data. ',' . $invno; 
			$prefix = "|";
		}
		//echo $noinvcsv;
		//die();
		//$listinv = substr($noinvcsv, 0,  strlen($noinvcsv) -1); 
		 // echo "EXEC  [sp_insertbc23] @nobl = N'$nobl', @tgbl = '$tgbl', @typebc = N'$typebc',@noinv = '$listinv'";
		// var_dump($noinv); die();
		$qry=$this->db->query("
		    declare @nobl as varchar(20), @tgbl as date, @typebc as varchar(5), @noinv as varchar(MAX) 
		    set @nobl = '$nobl'  	
		    set @tgbl = '$tgbl'
		    set @typebc = '$typebc'
		    set @noinv = '$noinvcsv' 
		    declare @tbl_listinv_bl table  ( id_hdr bigint,noinv varchar(60))
			BEGIN TRY
				declare @idata varchar(50)
				DECLARE a_cursor CURSOR FOR 
				select data from  dbo.Split( @noinv, CHAR(124))
				OPEN a_cursor
				FETCH NEXT FROM a_cursor INTO @idata
				WHILE @@FETCH_STATUS = 0 
				BEGIN -- begin here
					insert into @tbl_listinv_bl (id_hdr, noinv) values((select data from  dbo.Split( @idata, ',') where ID = 1), (select data from  dbo.Split( @idata, ',') where ID = 2))
				FETCH NEXT FROM a_cursor INTO @idata
				END
				CLOSE a_cursor
				DEALLOCATE a_cursor
				declare @ihdr bigint, @inoinv varchar(50)
	
				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 = @tgbl ) > 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  nobl = @nobl and tgbl = @tgbl
					group by 
						ITEM_NUMBER, 
						kd_neg, 
						UOM
						,[DESCRIPTION]
						,HS_CODE
						,hskode 
						) x
				)

				DECLARE i_cursor CURSOR FOR 
				select id_hdr, noinv from @tbl_listinv_bl
				OPEN i_cursor
				FETCH NEXT FROM i_cursor INTO @ihdr, @inoinv 
				WHILE @@FETCH_STATUS = 0 
				BEGIN -- begin here
					if (select COUNT(*) as cnt from dbo.tbl_coo_data where nobl = @nobl and id_coo_data=@ihdr) = 0
					begin
						select '01' as status, 'Data Not Found '+@nobl+' idhdr= '+cast(@ihdr as varchar)+' noinv= '+@inoinv  as [description]
						return	
					end	
	
				FETCH NEXT FROM i_cursor INTO @ihdr, @inoinv
				END
				CLOSE i_cursor
				DEALLOCATE i_cursor

				if (  select count(*)
				FROM
				  dbo.[v_coo_data]
				WHERE
				  nobl = @nobl and tgbl = @tgbl 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 nobl = @nobl and tgbl = @tgbl  and ([supplier_country] = 'ID')
					 group by  [supplier_country] 
					 ) x
				) <> 1
				begin
					select '01' as status, 'SUPPLIER $typebc TIDAK BOLEH DARI LUAR INDONESIA'  as [description]
					return	
				end

				if( select count(kd_neg) from [v_coo_data] where nobl = @nobl and tgbl = @tgbl and ISNUMERIC(kd_neg) = 1 ) > 0
				begin
					select '01' as status, 'ADA DATA NEGARA YANG SALAH..MOHON DILENGKAPI'  as [description]
					return	
				end

				insert into dbo.tbl_bc_in_hdr (
					[CAR]
					,statusdok 
					,typedok
					,bc_in_from_in
					,KdKpbc
					,Tujuan
					,JnsBarang
					,TujuanKirim
					,PasokNama
					,PasokAlmt
					,PasokNeg
					,AngkutNama
					,AngkutFl
					,Moda
					,KdVal
					,NilInv
					,KdHrg
					,JmBrg
					,Registrasi
					,UsahaId
					,UsahaNpwp
					,UsahaNama
					,UsahaAlmt
					,UsahaStatus
					,KdTPB
					,ApiKd
					,ApiNo
					,KdKpbcBongkar
					,KdKpbcAwas
					,Namattd
					,KotaTtd
					,nobl
					,tglbl
					,Ndpbm
					,Fob
					,PelBkr
					,TglTtd
					,Bruto
					,Netto
					,[create_date]
				      ,[create_by]
				      ,[update_date]
				      ,[update_by]
					) 
				select top 1 
					'-'
					,'DATA BARU' 
					,@typebc
					,'COO' --
					,'150300'
					,'1'
					,'01'
					,'02'
					 ,[supplier_name] 
					 ,[supplier_address]
					 ,[supplier_country]
					 ,SHIP_NAME
					,NULL
					,case when TRANSPORT_WAY = 'T' then '3' when TRANSPORT_WAY = 'B' then '1' else '4' end
					,[CURRENT_MONEY]
					,sum(amount)
					,case when CARA_BAYAR = 'FOB' then '3' when CARA_BAYAR = 'CIF' then '1' else '2' end
					,@jmldetil
					,(select top 1 Registrasi from tblImportir)
					,(select top 1 IMPID from tblImportir)
					,(select top 1 IMPNPWP from tblImportir)
					,(select top 1 IMPNAMA from tblImportir)
					,(select top 1 impalmt from tblImportir)
					,(select top 1 usahastatus from tblImportir)
					,(select top 1 KdTPB from tblImportir)
					,(select top 1 ApiKd from tblImportir)
					,(select top 1 ApiNo from tblImportir)
					,'150300'
					,'150300'
					,(select top 1 Namattd from tblImportir)
					,(select top 1 KotaTtd from tblImportir)
					,@nobl
					,@tgbl
					,NULL
					,sum(amount)
					,NULL
					,GETDATE()
					,isnull(sum(isnull(GROSS_WEIGHT,0)),0) as Bruto
					,isnull(sum(isnull(NET_WEIGHT,0)),0) as Netto
					,GETDATE()
					,'$userid'
					,GETDATE()
					,'$userid'
				from 
				[dbo].[v_coo_data]
				where nobl = @nobl and tgbl = @tgbl
				 group by 
				[supplier_name]
			   ,[supplier_address]
			   ,[supplier_country]
			   ,SHIP_NAME
			   ,TRANSPORT_WAY
			   ,CARA_BAYAR
			   ,[CURRENT_MONEY]

			   declare  @idhdr bigint
			   set @idhdr = @@IDENTITY
			  
			   INSERT INTO tbl_bc_in_hdr_h_status
						 (id_bc_in_hdr, statusdok, status_date)
			   VALUES  (@idhdr, (select top 1 statusdok from tbl_bc_in_hdr where [id_bc_in_hdr] = @idhdr), GETDATE())
			   
			   -- detil inv begin here
				insert into dbo.tbl_bc_in_dtl (
					[CAR]
					,Serial
					,BrgUrai
					,DNilInv
					,NoHs
					,id_bc_in_hdr
					,JnsBarangDtl
					,TujuanKirimDtl
					,SeriTrp
					,Merk
					,Tipe
					,SpfLain
					,KdBrg
					,JmlSat
					,KdSat
					,BrgAsal
					,Penggunaan
					,KemasJn
					,KemasJm
					,NettoDtl
				) select 
				  '-'
				  ,row_number() over(order by (select 0))	
				  ,isnull([DESCRIPTION],(select top 1 part_name from tbl_data_part_hs where part_no = [v_coo_data].ITEM_NUMBER ))   as BrgUrai
				  ,sum(amount)
				  ,dbo.fnRemoveNonNumericCharacters(ISNULL(HS_CODE, hskode))
				  ,@idhdr
				  ,'01'
				  ,'02'
				  ,'0'
				  ,NULL
				  ,ITEM_NUMBER
				  ,NULL
				  ,kode_barang
				  ,SUM(CASE ISNUMERIC(ARRIV_PLAN_NUMBER) WHEN 1 THEN CAST(ARRIV_PLAN_NUMBER AS float) ELSE null END )
				  ,isnull((select top 1 [kdsat] from [tbl_coo_satuan] where [kode_sat_coo] = [v_coo_data].UOM), [v_coo_data].UOM) as kdsat
				  ,kd_neg
				  ,'1'
				  ,'CT'
				  ,SUM(CASE ISNUMERIC(CTN_QUANTITY) WHEN 1 THEN CAST(CTN_QUANTITY AS float) ELSE null END) 
				  ,SUM(CASE ISNUMERIC(NET_WEIGHT) WHEN 1 THEN CAST(NET_WEIGHT AS float) ELSE null END)
	  
				from 
				[dbo].[v_coo_data]
				where nobl = @nobl and tgbl = @tgbl
				group by 
				ITEM_NUMBER,
				kode_barang,	
				kd_neg, 
				UOM
				,[DESCRIPTION]
				,HS_CODE
				,hskode
				-- end detil
			   -- update dokumen
				insert into dbo.tbl_bc_in_dok (
					CAR
					,DokKd
					,DokNo
					,DokTg		
					,id_bc_in_hdr
				)select 
				  '-'
				  ,'380'
				  ,INVOICE_NO
				  ,INVOICE_DATE
				  ,@idhdr
				from [dbo].[v_coo_data]
				where nobl = @nobl and tgbl = @tgbl
			  	group by INVOICE_NO, INVOICE_DATE	
			
			 -- insert to partname detil bc in
   				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
   				  ,@idhdr
				  ,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 = @idhdr and KdBrg=ITEM_NUMBER)
   				FROM
				  [dbo].[v_coo_data]
				where nobl = @nobl and tgbl = @tgbl
 				
 				insert into tbl_bc_in_kms (
 					[id_bc_in_hdr]
 					,[JnKemas]
				      ,[JmKemas]
				      ,[merkkemas]
 				) select 
 					@idhdr
 				    ,'CT'
 				    ,SUM(CASE ISNUMERIC(CTN_QUANTITY) WHEN 1 THEN CAST(CTN_QUANTITY AS float) ELSE null END) 
				    ,'-'
				from    
 				[dbo].[v_coo_data]
				where nobl = @nobl and tgbl = @tgbl
 				
				update tbl_coo_data set type_bc = @typebc, id_bc_in_hdr = @idhdr, status_inv = 'BCPROSES' 
 				where nobl = @nobl and tgbl = @tgbl
				select '00' as status, ' SUKSES DATA GROUP/BL/SURAT JALAN ' + @nobl as description

			END TRY
			BEGIN CATCH 
				SELECT 
				'error' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
		");
		$rs = $qry->result_array();	
		// var_dump($rs);
		// die();
		foreach ($rs as $row){
			$responce = array("sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
		
		
		
	}
	
	function generate_group(){
		$tipe = (isset($_POST['tipe']) && !empty($_POST['tipe']))?$_POST['tipe']:die('{"sts":"ERROR","desc":" Param INV Tidak Ditemukan"}');
		$qry=$this->db->query("
			declare @nobl varchar(50)
		    declare @tgbl date	
		    BEGIN TRY
		    set @tgbl = GETDATE()
			declare @hasil varchar(50)
			exec [dbo].[spu_seq] @nomor_untuk = N'noajubc40', @hasil = @hasil OUTPUT
			set @nobl = '$tipe'+@hasil 	
			
			select @nobl as nobl,@tgbl as tglbl, '00' as status, ' SUKSES' as description 
			
			END TRY
			BEGIN CATCH 
				SELECT 
				'error' AS status
				,ERROR_MESSAGE() AS description;
				
			END CATCH;
		");
		$rs = $qry->result_array();	
		// var_dump($rs);
		// die();
		$nobl = "";
		$tglbl = "";
		foreach ($rs as $row){
			$nobl = $row["nobl"];
			$tglbl = $row["tglbl"]; 	
			$responce = array("nobl" =>$nobl, "tglbl" =>$tglbl, "sts" => $row['status'], "desc" => $row['description']);
		}  
		echo json_encode($responce);
			
		
	}
  
 }