<?php
set_time_limit(0);
ini_set('memory_limit', '2200M');
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Migrasi_siadpa extends CI_Controller {
	
	function index(){
		if($this->session->userdata('is_logged_in')==FALSE){
			redirect('login');
		}

		if($this->session->userdata('idgroup')>1){
			show_404();
		}
		$data['page_title'] = 'MIGRASI DATA SIADPA';
		$data['main_body'] = 'migrasi/migrasi_pa';
		
		$data['data_asal'] = 'MSSQL - SIADPA';
		$this->load->vars($data);
		$this->load->view('header');
		$this->load->view('body/body_new');
	}

	function check_progress(){
		echo $this->nativesession->get('update_progress');
	}
	function validateUpdate() { 
		$this->load->helper('text');
		ini_set('odbc.defaultlrl', 65536); //-->for unlimited read mssql var 
		if($this->session->userdata('is_logged_in')==FALSE){
			echo json_encode(array('st'=>0,'msg'=>'Anda Belum Login, Silahkan Login Terlebih Dahulu.'));
			return;
		}
		if(empty($_POST)){
			show_404();
		}
		if($this->session->userdata('idgroup')>1){
			echo json_encode(array('st'=>0,'msg'=>'Anda Tidak Memiliki Kewenangan Untuk Mengakses Halaman Ini.'));
			return;
		}

		
	    $this->load->model('migrasi/migrasi','patching');
		$kode_pengadilan = $this->patching->getKodePN();

		$this->patching->exe_script('SET FOREIGN_KEY_CHECKS=0;');
		$this->patching->exe_script('SET autocommit = 0;');
		//--connect mssql server--
		$conn = conn_sqlserver();
		//--
		$this->nativesession->set('update_progress', 0);
		$this->nativesession->set('counter', 0);
		$this->load->helper('text');

        //cek koneksi siadpa
         $ms_cek = "SELECT * FROM MS_PERKARA";
		 $ms_cek_exe = exe_sqlserver($conn,$ms_cek);
		if($ms_cek_exe){
			$this->nativesession->set('update_progress', 0); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error koneksi sql server SIADPA yang mau dimigrasi <br> Silahkan cek file /application/config/database.php.'));
			return;
		}
 
      //--table perkara---
		$ms_perkara1 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara') BEGIN
              			DROP VIEW dbo.v_perkara
						END";
		$ms_perkara1_exe = exe_sqlserver($conn,$ms_perkara1);
		if($ms_perkara1_exe){
			$this->nativesession->set('update_progress', 1); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara1.'));
			return;
		}
		
		$ms_perkara2= "CREATE VIEW v_perkara AS
						SELECT
						ROW_NUMBER() OVER( ORDER BY tgl_daftar,no_perk )			AS perkara_id,
						NO_PERK		as no_perkara_siad,
						(case
							when SUBSTRING(NO_PERK,5,1)='G' then 15
							when SUBSTRING(NO_PERK,5,1)='P' then 16
						end)AS alur_perkara_id,
						convert(varchar(10),TGL_DAFTAR,120)				AS tanggal_pendaftaran,
						(case
							when mp.JNSPERK_KODE is not null then  mp.JNSPERK_KODE 			
						end)AS jenis_perkara_id,
						jp.JNSPERK_NAMA			AS jenis_perkara_nama,
						jp.JNSPERK_NAMA                                 AS jenis_perkara_text,
						ROW_NUMBER()OVER(Partition By substring(convert(varchar(10),TGL_DAFTAR,120),1,7) Order By tgl_daftar) as nomor_urut_register,
						ROW_NUMBER()OVER(Partition By year(TGL_DAFTAR) Order By tgl_daftar) as nomor_urut_perkara,						
						left(NO_PERK,4)+'/Pdt.'+Substring(NO_PERK,5,1)+'/'+'20'+right(NO_PERK,2)+'/".$kode_pengadilan."' As nomor_perkara,
						'".$kode_pengadilan."'+'20'+right(NO_PERK,2)+'_'+'Pdt.'+Substring(NO_PERK,5,1) AS nomor_indeks,
						(select a.nama from (select 
														distinct
															no_perk,fungsi,
															(SELECT STUFF(
																	 (SELECT '</br>' + nama 
																	  FROM DATA_BIODATA md 
																		WHERE md.no_perk = m.no_perk and md.fungsi=m.fungsi
																	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
															) as nama
														from DATA_BIODATA m where m.fungsi=1001 and m.tingkat=0) as a where a.NO_PERK=dr.no_perk) as pihak1_text,						
						(select a.nama from (select 
														distinct
															no_perk,fungsi,
															(SELECT STUFF(
																	 (SELECT '</br>' + nama 
																	  FROM DATA_BIODATA md 
																		WHERE md.no_perk = m.no_perk and md.fungsi=m.fungsi
																	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
															) as nama
														from DATA_BIODATA m where m.fungsi=5106 and m.tingkat=0) as a where a.NO_PERK=dr.no_perk) as pengacara_pihak1,						
						(select a.nama from (select 
														distinct
															no_perk,fungsi,
															(SELECT STUFF(
																	 (SELECT '</br>' + nama 
																	  FROM DATA_BIODATA md 
																		WHERE md.no_perk = m.no_perk and md.fungsi=m.fungsi
																	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
															) as nama
														from DATA_BIODATA m where m.fungsi=1002 and m.tingkat=0) as a where a.NO_PERK=dr.no_perk) as pihak2_text,						
						(select a.nama from (select 
														distinct
															no_perk,fungsi,
															(SELECT STUFF(
																	 (SELECT '</br>' + nama 
																	  FROM DATA_BIODATA md 
																		WHERE md.no_perk = m.no_perk and md.fungsi=m.fungsi
																	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
															) as nama
														from DATA_BIODATA m where m.fungsi=6106 and m.tingkat=0) as a where a.NO_PERK=dr.no_perk) as pengacara_pihak2,						
						(case 
						     when Substring(NO_PERK,5,1)='G' then 'Penggugat :<br />'+
							 						(select a.nama from (select 
														distinct
															no_perk,fungsi,
															(SELECT STUFF(
																	 (SELECT '</br>' + nama 
																	  FROM DATA_BIODATA md 
																		WHERE md.no_perk = m.no_perk and md.fungsi=m.fungsi
																	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
															) as nama
														from DATA_BIODATA m where m.fungsi=1001 and m.tingkat=0) as a where a.NO_PERK=dr.no_perk)
							 
							 +'<br /> Tergugat:<br />'+						(select a.nama from (select 
														distinct
															no_perk,fungsi,
															(SELECT STUFF(
																	 (SELECT '</br>' + nama 
																	  FROM DATA_BIODATA md 
																		WHERE md.no_perk = m.no_perk and md.fungsi=m.fungsi
																	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
															) as nama
														from DATA_BIODATA m where m.fungsi=1002 and m.tingkat=0) as a where a.NO_PERK=dr.no_perk)  
						     
							 
							 when Substring(NO_PERK,5,1)='P' then 'Pemohon:<br />'+						(select a.nama from (select 
														distinct
															no_perk,fungsi,
															(SELECT STUFF(
																	 (SELECT '</br>' + nama 
																	  FROM DATA_BIODATA md 
																		WHERE md.no_perk = m.no_perk and md.fungsi=m.fungsi
																	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
															) as nama
														from DATA_BIODATA m where m.fungsi=1001 and m.tingkat=0) as a where a.NO_PERK=dr.no_perk) 
						end) as para_pihak,						
						'T' as pihak_dipublikasikan,
						dr.petitum as petitum,
						(case
							when dr.prodeo=0 then 0
							when dr.prodeo=1 then 1
						end)				as prodeo,
						dr.TINGKAT as tingkat
						   
						from (select distinct no_perk,tingkat,tgl_daftar,prodeo,kode_perk,cast(petitum as varchar(1000)) as petitum from data_register where tingkat=0) as dr                                
						left join (select distinct kode_perk,JNSPERK_KODE FROM MS_PERKARA) as mp on dr.KODE_PERK=mp.KODE_PERK
						left join MS_JENIS_PERKARA as jp on mp.JNSPERK_KODE=jp.JNSPERK_KODE
						where cast(right(dr.no_perk,2) as INT) >=15 and dr.tingkat=0 and (mp.JNSPERK_KODE is not null and mp.JNSPERK_KODE > 0) ";
		
		if(exe_sqlserver($conn,$ms_perkara2)){
			$this->nativesession->set('update_progress', 2); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara2.'));
			return;
		}


		$ms_perkara3 = "SELECT * FROM v_perkara where nomor_perkara is not null";
		
		
		$ms_perkara3_exe = exe_sqlserver($conn,$ms_perkara3);

		$jml_data = odbc_num_rows($ms_perkara3_exe);
		if($jml_data>0){
		
		$data_perkara = array();
		$datas_perkara_proses = array();
		while($row = odbc_fetch_array($ms_perkara3_exe)){
								
			$data['perkara_id']=$row['perkara_id'];
			$data['alur_perkara_id']=$row['alur_perkara_id'];
			$data['tanggal_pendaftaran']=$row['tanggal_pendaftaran'];
			$data['jenis_perkara_id']=$row['jenis_perkara_id'];
			$data['jenis_perkara_nama']=$row['jenis_perkara_nama'];
			$data['jenis_perkara_text']=$row['jenis_perkara_text'];
			$data['nomor_urut_register']=$row['nomor_urut_register'];
			$data['nomor_urut_perkara']=$row['nomor_urut_perkara'];
			$data['nomor_perkara']=$row['nomor_perkara'];
			$data['nomor_indeks']=$row['nomor_indeks'];
			$data['pihak1_text']=ascii_to_entities($row['pihak1_text']);
			$data['pengacara_pihak1']=ascii_to_entities($row['pengacara_pihak1']);
			$data['pihak2_text']=ascii_to_entities($row['pihak2_text']);
			$data['pengacara_pihak2']=ascii_to_entities($row['pengacara_pihak2']);
			$data['para_pihak']=ascii_to_entities($row['para_pihak']);
			$data['pihak_dipublikasikan']=$row['pihak_dipublikasikan'];
			$petitum_reg=str_replace(array("\pard","\par"), array("</br>","</br>"), ascii_to_entities($row['petitum']));
			$petitum_br=preg_replace('(\'"{\\*?\\\\.+(;})|\\s?\\\\[A-Za-z0-9]+|\\s?{\\s?\\\\[A-Za-z0-9]+\\s?|\\s?}\\s?"\')','',$petitum_reg, -1, $count);
			$cari=array("Courier New;}","Arial Narrow;}","Arial Narrow;}","Times New Roman;}","Calibri;}","Calibri;}","Times New Roman;}};}","Normal;}","{\* Default Paragraph Font;}","Body Text;}","{\* Body Text Text;}","List Paragraph;}","No Spacing;}}{\*","-720\'02\'00.;}\'01;}}","-360\'02\'01.;}\'01;}}","-180\'02\'02.;}\'01;}}","-360\'02\'03.;}\'01;}}","-360\'02\'04.;}\'01;}}","-180\'02\'05.;}\'01;}}","-360\'02\'06.;}\'01;}}","-360\'02\'07.;}\'01;}}","-180\'02\'08.;}\'01;}}","\'02\'00.;}\'01;}}","-360\'02\'07.;}","Arial;}","};;}","Body Text Indent 3;}{\* Body Text Indent 3 Text;}","No Spacing;}}","Wingdings;}","Symbol;}};;}","header;}{\* header Text;}-240","Body Text Indent 2;}{\* Body Text Indent 2 Text;}","Body Text Indent;}{\* Body Text Indent Text;}","Body Text,Char;}{\* Body Text,Char Text;}","{\* page number;}","{\* Strong;}","Body Text,Char Char,Char Char1,Char Char Char,Body Text Char1,Char Char2,Body Text Char Char,Char Char1 Char;}{\* Body Text,Char Char,Char Char1,Char Char Char,Body Text Char1,Char Char2,Body Text Char Char,Char Char1 Char Text;}}","};}","Symbol;}","-360\'01-;};}}","-360\'01o;};}}","-360\'01 ?;};}}","-360\'01o;};}}","-360\'02\'06.;}\'01;}","Body Text 2;}","{\* Body Text 2 Text;}-3420 -360 }","};;;}"," {\* Body Text 2 Text;}}","-3420 }","header;}","{\* header Text;}}","{\* \'02\'00);}\'01;}}","{\* } }","}{\*","-705","      }","}","{\* Body Text 2 Text;",";;;;","{\* Body Text 2 Text;","\'02\'00);\'01;","{\*","\'02\'00.;\'0","-240","{\* header Text; ","header Text;","Body Text,","Char","Char;","Char","Text;","-360\'02\'02);\'01;","-360\'02\'08.;\'01;","-1080\'01\'01;\'01;","-360\'02\'06.;{","-1080","heading 1;","heading 1 Text;","{\* header Text;","{\* Body Text 2 Text;","Subtitle;","Subtitle Text;","Plain Text;","Plain Text Text;","footer;","footer Text;","Block Text;","-1080","-285","-360\'02\'02.;\'01;","heading 7;","heading 7 -360","-420","-360\'02\'05.;\'01;",", 1,  ,Body Text 1, 2,Body Text  , 1 ;  , 1,  ,Body Text 1, 2,Body Text  , 1","-2160","-720\'01\'00;\'01;","-690\'02\'01.;\'01;","-330",",, ;  ,,  -720  No Spacing;","-180{");
			$ganti=array('','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
			$petitum_clean=str_replace($cari, $ganti, $petitum_br);
			$data['petitum']=$petitum_clean;			
			$data['prodeo']=$row['prodeo'];

			$data['tahapan_terakhir_id']=10;
			$data['tahapan_terakhir_text']='Pendaftaran';
			$data['proses_terakhir_id']=10;
			$data['proses_terakhir_text']='Pendaftaran Perkara';
			$data['diinput_oleh']='System';
			$data['diinput_tanggal']=date("Y-m-d H:i:s", time());

			$data1['perkara_id']=$row['perkara_id'];
			$data1['tahapan_id']=10;
			$data1['tahapan_nama']='Pendaftaran';
			$data1['proses_id']=10;
			$data1['proses_nama']='Pendaftaran Perkara';
			$data1['tanggal']=$row['tanggal_pendaftaran'];
			$data1['diinput_oleh']='System';
			$data1['diinput_tanggal']=date("Y-m-d H:i:s", time());
			array_push($data_perkara, $data);
			array_push($datas_perkara_proses, $data1);
			//$my_perkara3 = $this->patching->replace_data('perkara',$data);
			//$my_perkara3_1 = $this->patching->replace_data('perkara_proses',$data1);
		}
		
		
		$this->db->insert_batch('perkara',$data_perkara);
		$this->db->insert_batch('perkara_proses',$datas_perkara_proses);

		$my_perkara3 = true;
		$my_perkara3_1 = true;
		if($my_perkara3 && $my_perkara3_1){
			$this->nativesession->set('update_progress', 3); 
			session_write_close();
			session_start();
			unset($data_perkara);
			unset($datas_perkara_proses);

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara3.'));
			return;
		}
		}

		$my_perkara4 =" UPDATE perkara SET jenis_perkara_id=
						CASE
							WHEN jenis_perkara_id=2 THEN 341
							WHEN jenis_perkara_id=3 THEN 342
							WHEN jenis_perkara_id=4 THEN 343
							WHEN jenis_perkara_id=5 THEN 344
							WHEN jenis_perkara_id=6 THEN 345
							WHEN jenis_perkara_id=7 THEN 346
							WHEN jenis_perkara_id=8 THEN 347
							WHEN jenis_perkara_id=9 THEN 348
							WHEN jenis_perkara_id=10 THEN 349
							WHEN jenis_perkara_id=11 THEN 350
							WHEN jenis_perkara_id=12 THEN 351
							WHEN jenis_perkara_id=13 THEN 352
							WHEN jenis_perkara_id=14 THEN 353
							WHEN jenis_perkara_id=15 THEN 354
							WHEN jenis_perkara_id=16 THEN 355
							WHEN jenis_perkara_id=17 THEN 356
							WHEN jenis_perkara_id=18 THEN 357
							WHEN jenis_perkara_id=19 THEN 358
							WHEN jenis_perkara_id=20 THEN 359
							WHEN jenis_perkara_id=21 THEN 360
							WHEN jenis_perkara_id=22 THEN 361
							WHEN jenis_perkara_id=23 THEN 362
							WHEN jenis_perkara_id=24 THEN 363
							WHEN jenis_perkara_id=25 THEN 364
							WHEN jenis_perkara_id=26 THEN 365
							WHEN jenis_perkara_id=27 THEN 366
							WHEN jenis_perkara_id=28 THEN 367
							WHEN jenis_perkara_id=29 THEN 368
							WHEN jenis_perkara_id=30 THEN 369
							WHEN jenis_perkara_id=31 THEN 370
							WHEN jenis_perkara_id=32 THEN 371
						END	
						WHERE jenis_perkara_id IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32)";

		if($this->patching->exe_script($my_perkara4)){
			$this->nativesession->set('update_progress', 4); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara4.'));
			return;
		}



		$sql = "DROP TABLE IF EXISTS pihak2_idlama_idbaru";
		$this->patching->exe_script($sql);
		$sql1 = "CREATE TABLE pihak2_idlama_idbaru (
						pihak2_id_lama bigint(20) unsigned NOT NULL,
						pihak2_id_baru bigint(20) unsigned NOT NULL
					) ENGINE=MyISAM;";
		
		if($this->patching->exe_script($sql1)){
			$this->nativesession->set('update_progress', 4); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query sql.'));
			return;
		}



		//--table pihak dan perkara_pihak1---
		$ms_perkara5 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_pihak') BEGIN
    					DROP VIEW dbo.v_pihak
						END";
		
		if(exe_sqlserver($conn,$ms_perkara5)){
			$this->nativesession->set('update_progress', 5); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara5.'));
			return;
		}
			$ms_perkara5a= "CREATE VIEW v_pihak as
						SELECT
						ROW_NUMBER() OVER( ORDER BY db.no_perk )		as id,
						db.NO_PERK										as no_perkara_siad,
						db.FUNGSI										as fungsi,
						1												as jenis_pihak_id,
						db.NAMA											as nama,
						(case
							when upper(CHARINDEX('binti',db.NAMA))>0  then 'P'
							ELSE 'L'
						end)											as jenis_kelamin,
						substring(db.ALAMAT,0,499)						as alamat,
						1												as agama_id,
						'Islam'											as agama_nama,
						db.PENDIDIKAN									as pendidikan,
						98												as warga_negara_id,
						'Indonesia'										as warga_negara,
						db.PEKERJAAN									as pekerjaan,
						'Kawin'											as status_kawin
						from DATA_BIODATA as db
						WHERE cast(right(db.no_perk,2) as INT) >=15 and db.tingkat=0 AND (db.FUNGSI=1001 or db.FUNGSI=1002 or db.FUNGSI=5106 or db.FUNGSI=6106) AND db.NAMA IS NOT NULL";
		
		if(exe_sqlserver($conn,$ms_perkara5a)){
			$this->nativesession->set('update_progress', 5); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara5a.'));
			return;
		}

		$ms_perkara5b = "SELECT * FROM v_pihak";
		$ms_perkara5b_exe = exe_sqlserver($conn,$ms_perkara5b);
		$jml_data = odbc_num_rows($ms_perkara5b_exe);
		if($jml_data>0){
		
		$data_pihak = array();
		while($row = odbc_fetch_array($ms_perkara5b_exe)){
			//$this->load->helper('text');
			$data5['id']=$row['id'];
			$data5['jenis_pihak_id']=$row['jenis_pihak_id'];
			$data5['nama']=substr(ascii_to_entities($row['nama']),0,499);
			//$data5['tempat_lahir']=$row['tempat_lahir'];
			$data5['jenis_kelamin']=$row['jenis_kelamin'];
			$data5['alamat']=substr(ascii_to_entities($row['alamat']),0,499);
			$data5['agama_id']=$row['agama_id'];
			$data5['agama_nama']=$row['agama_nama'];
			$data5['warga_negara_id']=$row['warga_negara_id'];
			$data5['warga_negara']=$row['warga_negara'];
			$data5['pekerjaan']=ascii_to_entities($row['pekerjaan']);
			$data5['status_kawin']=$row['status_kawin'];
			$data5['diinput_oleh']='System';
			$data5['diinput_tanggal']=date("Y-m-d H:i:s", time());
			
			array_push($data_pihak, $data5);
			//$my_perkara5b = $this->patching->replace_data('pihak',$data5);
		}
		
		$this->db->insert_batch('pihak',$data_pihak);
		
		$my_perkara5b = true;
		if($my_perkara5b){
			$this->nativesession->set('update_progress', 5); 
			session_write_close();
			session_start();
			unset($data_pihak);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara5b.'));
			return;
		}
		}

		//--perkarapihak1

		$list_perkara_id=$this->patching->get_list_perkara_id()->result();
		$cek_perkara_id=$list_perkara_id[0]->perkara_id;

		$ms_perkara6 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_pihak1') BEGIN
    					DROP VIEW dbo.v_perkara_pihak1
						END";
		
		if(exe_sqlserver($conn,$ms_perkara6)){
			$this->nativesession->set('update_progress', 6); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara6.'));
			return;
		}

		$ms_perkara6a= "CREATE VIEW v_perkara_pihak1 AS
						SELECT
						ROW_NUMBER() OVER( ORDER BY db.no_perk )		as id,
						db.NO_PERK										as no_perkara_siad,
						vp.perkara_id									as perkara_id,
						ROW_NUMBER()OVER(Partition By db.NO_PERK Order By DATE_MOD) as urutan,
						vphk.id									        as pihak_id,
						1												as jenis_pihak_id,
						db.NAMA											as nama,
						substring(db.ALAMAT,1,500)						as alamat
						from DATA_BIODATA as db
						left join v_perkara as vp on db.NO_PERK=vp.no_perkara_siad
						left join v_pihak as vphk on db.NO_PERK=vphk.no_perkara_siad and db.fungsi=vphk.fungsi
						WHERE cast(right(db.no_perk,2) as INT) >=15 and db.tingkat=0 and db.FUNGSI=1001 AND db.NAMA IS NOT NULL and vp.perkara_id is not null";
		
		if(exe_sqlserver($conn,$ms_perkara6a)){
			$this->nativesession->set('update_progress', 6); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara6a.'));
			return;
		}

		$ms_perkara6b = "SELECT * from v_perkara_pihak1";
		$ms_perkara6b_exe = exe_sqlserver($conn,$ms_perkara6b);
		$jml_data = odbc_num_rows($ms_perkara6b_exe);
		if($jml_data>0){

		$data_perkara_pihak1 = array();				
		while($row = odbc_fetch_array($ms_perkara6b_exe)){
			//$this->load->helper('text');
			$data6['id']=$row['id'];
			$data6['perkara_id']=$row['perkara_id'];
			$data6['urutan']=$row['urutan'];
			$data6['pihak_id']=$row['pihak_id'];
			$data6['jenis_pihak_id']=$row['jenis_pihak_id'];
			$data6['nama']=substr(ascii_to_entities($row['nama']),0,499);
			$data6['alamat']= substr(ascii_to_entities($row['alamat']),0,499);
			$data6['diinput_oleh']='System';
			$data6['diinput_tanggal']=date("Y-m-d H:i:s", time());

			array_push($data_perkara_pihak1, $data6);
			//$my_perkara6b = $this->patching->replace_data('perkara_pihak1',$data6);
		}
		
		$this->db->insert_batch('perkara_pihak1',$data_perkara_pihak1);

		$my_perkara6b = true;
		if($my_perkara6b){
			$this->nativesession->set('update_progress', 6); 
			session_write_close();
			session_start();
			unset($data_perkara_pihak1);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara6b.'));
			return;
		}
		}

		
		//--- tabel pihak dan perkara_pihak2
		$ms_perkara7 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_pihak2') BEGIN
    					DROP VIEW dbo.v_pihak2
						END";
		
		if(exe_sqlserver($conn,$ms_perkara7)){
			$this->nativesession->set('update_progress', 7); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara7.'));
			return;
		}




		$ms_perkara8 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_pihak2') BEGIN
    					DROP VIEW dbo.v_perkara_pihak2
						END";
		
		if(exe_sqlserver($conn,$ms_perkara8)){
			$this->nativesession->set('update_progress', 8); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara8.'));
			return;
		}

		$ms_perkara8a= "CREATE VIEW v_perkara_pihak2 AS
						SELECT
						ROW_NUMBER() OVER( ORDER BY db.no_perk )		as id,
						db.NO_PERK										as no_perkara_siad,
						vp.perkara_id									as perkara_id,
						ROW_NUMBER()OVER(Partition By db.NO_PERK Order By DATE_MOD) as urutan,
						vphk.id									        as pihak_id,
						1												as jenis_pihak_id,
						db.NAMA											as nama,
						substring(db.ALAMAT,0,499)						as alamat
						from DATA_BIODATA as db
						left join v_perkara as vp on db.NO_PERK=vp.no_perkara_siad
						left join v_pihak as vphk on db.NO_PERK=vphk.no_perkara_siad and db.fungsi=vphk.fungsi
						WHERE cast(right(db.no_perk,2) as INT) >=15 and  db.tingkat=0 and db.FUNGSI=1002 AND db.NAMA IS NOT NULL and vp.perkara_id is not null";
		
		if(exe_sqlserver($conn,$ms_perkara8a)){
			$this->nativesession->set('update_progress', 8); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara8a.'));
			return;
		}


		$ms_perkara8b = "SELECT * from v_perkara_pihak2";
		$ms_perkara8b_exe = exe_sqlserver($conn,$ms_perkara8b);
		$jml_data = odbc_num_rows($ms_perkara8b_exe);
		if($jml_data>0){				

		$data_perkara_pihak2=array();
		while($row = odbc_fetch_array($ms_perkara8b_exe)){			
				
			$data7['id']=$row['id'];
			$data7['perkara_id']=$row['perkara_id'];
			$data7['urutan']=$row['urutan'];
			$data7['pihak_id']=$row['pihak_id'];
			$data7['jenis_pihak_id']=$row['jenis_pihak_id'];
			$data7['nama']=substr(ascii_to_entities($row['nama']),0,499);
			$data7['alamat']=substr(ascii_to_entities($row['alamat']),0,499);
			$data7['diinput_oleh']='System';
			$data7['diinput_tanggal']=date("Y-m-d H:i:s", time());

			array_push($data_perkara_pihak2, $data7);
			//$my_perkara8b = $this->patching->replace_data('perkara_pihak2',$data7);			
		}		
		
		$this->db->insert_batch('perkara_pihak2',$data_perkara_pihak2);


		$my_perkara8b = true;		
		if($my_perkara8b){
			$this->nativesession->set('update_progress', 8); 
			session_write_close();
			session_start();
			unset($data_perkara_pihak2);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara8b.'));
			return;
		}
		}


		//--pihak - kuasa
		$ms_perkara9 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_pengacara1') BEGIN
    					DROP VIEW dbo.v_perkara_pengacara1
						END";
		
		if(exe_sqlserver($conn,$ms_perkara9)){
			$this->nativesession->set('update_progress', 9); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara9.'));
			return;
		}

		$ms_perkara9a= "CREATE VIEW v_perkara_pengacara1 AS						
						SELECT
						ROW_NUMBER() OVER( ORDER BY db.no_perk )		as id,
						NO_PERK											as no_perkara_siad,
						vp.perkara_id									as perkara_id,
						1												as pihak_ke,
						vphk1.pihak_id									as pihak_id,
						ROW_NUMBER()OVER(Partition By NO_PERK Order By DATE_MOD) as urutan,
						vphk.id									        as pengacara_id,
						1												as jenis_pihak_id,
						db.NAMA											as nama,
						substring(db.ALAMAT,0,499)						as alamat,
						'Y'												as aktif
						from DATA_BIODATA as db
						left join v_perkara as vp on db.NO_PERK=vp.no_perkara_siad
						left join v_pihak  as vphk on db.NO_PERK=vphk.no_perkara_siad and db.fungsi=vphk.fungsi
						left join v_perkara_pihak1 as vphk1 on db.NO_PERK=vphk1.no_perkara_siad
						WHERE cast(right(db.no_perk,2) as INT) >=15 and db.tingkat=0 and db.FUNGSI=5106 AND db.NAMA IS NOT NULL and vp.perkara_id is not null
						and vphk1.pihak_id is not null";
		
		if(exe_sqlserver($conn,$ms_perkara9a)){
			$this->nativesession->set('update_progress', 9); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara9a.'));
			return;
		}


		$ms_perkara9b = "SELECT * from v_perkara_pengacara1";
		$ms_perkara9b_exe = exe_sqlserver($conn,$ms_perkara9b);
		
		$jml_data = odbc_num_rows($ms_perkara9b_exe);
		if($jml_data>0){	

		$data_perkara_pengacara1=array();	
		$data_pihak_pengacara1=array();	
		while($row = odbc_fetch_array($ms_perkara9b_exe)){
		
			$data9a['perkara_id']=$row['perkara_id'];
			$data9a['pihak_ke']=$row['pihak_ke'];
			$data9a['pihak_id']=$row['pihak_id'];
			$data9a['urutan']=$row['urutan'];
			$data9a['pengacara_id']=$row['pengacara_id'];
			$data9a['jenis_pihak_id']=$row['jenis_pihak_id'];
			$data9a['nama']=substr(ascii_to_entities($row['nama']),0,499);
			$data9a['alamat']=substr(ascii_to_entities($row['alamat']),0,499);
			$data9a['diinput_oleh']='System';
			$data9a['diinput_tanggal']=date("Y-m-d H:i:s", time());					
						
			$data9b['pihak_id']=$row['pengacara_id'];			
			$data9b['alamat_kantor']=substr(ascii_to_entities($row['alamat']),0,499);

			array_push($data_perkara_pengacara1, $data9a);			
			array_push($data_pihak_pengacara1, $data9b);			
			//$data9b['diinput_oleh']='System';
			//$data9b['diinput_tanggal']=date("Y-m-d H:i:s", time());
		
			//$my_perkara9a = $this->patching->replace_data('perkara_pengacara',$data9a);
			//$my_perkara9b = $this->patching->replace_data('pihak_pengacara',$data9b);
		}	

        
		$this->db->insert_batch('perkara_pengacara',$data_perkara_pengacara1);
		$this->db->replace_batch('pihak_pengacara',$data_pihak_pengacara1);


		$my_perkara9a = true;	
		$my_perkara9b = true;	
		if($my_perkara9a && $my_perkara9b){
			$this->nativesession->set('update_progress', 9); 
			session_write_close();
			session_start();
			unset($data_perkara_pengacara1);	
			unset($data_pihak_pengacara1);	

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara9a,my_perkara9b.'));
			return;
		}
		}

		//--pihak - kuasa2
		$ms_perkara9c= "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_pengacara2') BEGIN
    					DROP VIEW dbo.v_perkara_pengacara2
						END";
		
		if(exe_sqlserver($conn,$ms_perkara9c)){
			$this->nativesession->set('update_progress', 10); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara9c.'));
			return;
		}

		$ms_perkara9d= "CREATE VIEW v_perkara_pengacara2 AS						
						SELECT
						ROW_NUMBER() OVER( ORDER BY db.no_perk )		as id,
						db.NO_PERK											as no_perkara_siad,
						vp.perkara_id									as perkara_id,
						1												as pihak_ke,
						vphk1.pihak_id									as pihak_id,
						ROW_NUMBER()OVER(Partition By db.NO_PERK Order By DATE_MOD) as urutan,
						vphk.id									        as pengacara_id,
						1												as jenis_pihak_id,
						db.NAMA											as nama,
						substring(db.ALAMAT,0,499)						as alamat,
						'Y'												as aktif
						from DATA_BIODATA as db
						left join v_perkara as vp on db.NO_PERK=vp.no_perkara_siad
						left join v_pihak as vphk on db.NO_PERK=vphk.no_perkara_siad and db.fungsi=vphk.fungsi
						left join v_perkara_pihak1 as vphk1 on db.NO_PERK=vphk1.no_perkara_siad 
						WHERE cast(right(db.no_perk,2) as INT) >=15 and db.tingkat=0 and db.FUNGSI=6106 AND db.NAMA IS NOT NULL and vp.perkara_id is not null
						and vphk1.pihak_id is not null";
		
		if(exe_sqlserver($conn,$ms_perkara9d)){
			$this->nativesession->set('update_progress', 10); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara9d.'));
			return;
		}


		$ms_perkara9e = "SELECT * from v_perkara_pengacara2";
		$ms_perkara9e_exe = exe_sqlserver($conn,$ms_perkara9e);
		
		$jml_data = odbc_num_rows($ms_perkara9e_exe);
		if($jml_data>0){	

		$data_perkara_pengacara2=array();	
		$data_pihak_pengacara2=array();			
		while($row = odbc_fetch_array($ms_perkara9e_exe)){
		
			$data9e['perkara_id']=$row['perkara_id'];
			$data9e['pihak_ke']=$row['pihak_ke'];
			$data9e['pihak_id']=$row['pihak_id'];
			$data9e['urutan']=$row['urutan'];
			$data9e['pengacara_id']=$row['pengacara_id'];
			$data9e['jenis_pihak_id']=$row['jenis_pihak_id'];
			$data9e['nama']=substr(ascii_to_entities($row['nama']),0,499);
			$data9e['alamat']=substr(ascii_to_entities($row['alamat']),0,499);
			$data9e['diinput_oleh']='System';
			$data9e['diinput_tanggal']=date("Y-m-d H:i:s", time());					
						
			$data9f['pihak_id']=$row['pengacara_id'];			
			$data9f['alamat_kantor']=substr(ascii_to_entities($row['alamat']),0,499);

			array_push($data_perkara_pengacara2, $data9e);			
			array_push($data_pihak_pengacara2, $data9f);			

			//$data9b['diinput_oleh']='System';
			//$data9b['diinput_tanggal']=date("Y-m-d H:i:s", time());		
			//$my_perkara9f = $this->patching->replace_data('perkara_pengacara',$data9e);
			//$my_perkara9g = $this->patching->replace_data('pihak_pengacara',$data9f);
		}		
		$this->db->insert_batch('perkara_pengacara',$data_perkara_pengacara2);
		$this->db->replace_batch('pihak_pengacara',$data_pihak_pengacara2);
		//$this->patching->exe_script('commit;');

		$my_perkara9f = true;	
		$my_perkara9g = true;			
		if($my_perkara9f && $my_perkara9g){
			$this->nativesession->set('update_progress', 10); 
			session_write_close();
			session_start();
			unset($data_perkara_pengacara2);	
			unset($data_pihak_pengacara2);			

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara9f,my_perkara9g.'));
			return;
		}
		}		

		
		//--perkara_penetapan
		$ms_perkara11 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_penetapan') BEGIN
    					DROP VIEW dbo.v_perkara_penetapan
						END";
		
		if(exe_sqlserver($conn,$ms_perkara11)){
			$this->nativesession->set('update_progress', 11); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara11.'));
			return;
		}

		$ms_perkara11a= "CREATE VIEW v_perkara_penetapan AS						
						select 
								vp.perkara_id			as perkara_id,
								a.NO_PERK				as no_perkara_siad,
								convert(varchar(10),dr.TGL_PMH,120)				as penetapan_majelis_hakim, 
								b.No_SK				as nomor_sk_majelis_hakim,
								c.km+'<br/>'+c.ag1+'<br/>'+c.ag2 as majelis_hakim_nama,
								('Hakim Ketua : '+c.km+'<br/>'+
								'Hakim Anggota 1 :'+c.ag1+'</br>'+
								'Hakim Anggota 2 :'+c.ag2) as majelis_hakim_text,
								convert(varchar(10),dr.TGL_PMH,120)				as penetapan_panitera_pengganti, 
								
								(case
									when a.tim=1 then pp1 
									when a.tim=2 then pp2
								end)											as panitera_pengganti_text,
								
								convert(varchar(10),dr.TGL_PHS,120) as penetapan_hari_sidang,
							    convert(varchar(10),sdp.tanggal,120)				as sidang_pertama,
								a.tingkat				as tingkat
								
							from data_sidang a
							join v_perkara as vp on a.NO_PERK=vp.no_perkara_siad
							join DATA_REGISTER as dr on a.NO_PERK=dr.NO_PERK
							join ms_hakim b on a.TGL_SK = b.TGL_SK
							join MS_DATAHAKIM c on b.KODE = c.KODE and a.RUANG = c.RUANG and a.HARI = c.HARI and a.NO_MAJELIS = c.TIM
							join (select no_perk,min(tanggal) as tanggal from data_sidang group by no_perk) as sdp on a.no_perk=sdp.no_perk
							join (select z.NO_PERK, max(z.ke) as Terakhir from data_sidang z group by z.NO_PERK, z.TINGKAT) d 
								on a.NO_PERK = d.NO_PERK and a.KE = d.Terakhir
							where cast(right(a.no_perk,2) as INT) >=15 and vp.perkara_id is not null and a.tingkat=0
					";
		
		if(exe_sqlserver($conn,$ms_perkara11a)){
			$this->nativesession->set('update_progress', 11); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara11a.'));
			return;
		}

		
		$ms_perkara11b = "SELECT * from v_perkara_penetapan";
		$ms_perkara11b_exe = exe_sqlserver($conn,$ms_perkara11b);
		
		$jml_data = odbc_num_rows($ms_perkara11b_exe);
		if($jml_data>0){
		
		$data_perkara_penetapan=array();
		$data_update_perkara1=array();
		$data_perkara_proses1=array();
		$data_update_perkara2=array();
		$data_perkara_proses2=array();
		$data_update_perkara3=array();
		$data_perkara_proses3=array();

		
		while($row = odbc_fetch_array($ms_perkara11b_exe)){		
			
			$data11['perkara_id']=$row['perkara_id'];
			$data11['penetapan_majelis_hakim']=$row['penetapan_majelis_hakim'];			
			$data11['nomor_sk_penetapan_majelis_hakim']=ascii_to_entities($row['nomor_sk_majelis_hakim']);
			//$data11['majelis_hakim_id']=$row['majelis_hakim_id'];
			$data11['majelis_hakim_nama']=ascii_to_entities($row['majelis_hakim_nama']);
			$data11['majelis_hakim_text']=ascii_to_entities($row['majelis_hakim_text']);

			$data11['penetapan_panitera_pengganti']=$row['penetapan_panitera_pengganti'];	
			$data11['nomor_sk_penetapan_panitera_pengganti']=ascii_to_entities($row['nomor_sk_majelis_hakim']);		
			//$data11['panitera_pengganti_id']=$row['panitera_pengganti_id'];
			$data11['panitera_pengganti_text']=ascii_to_entities($row['panitera_pengganti_text']);

			$data11['penetapan_hari_sidang']=$row['penetapan_hari_sidang'];
			$data11['sidang_pertama']=$row['sidang_pertama'];
			$data11['diinput_oleh']='System';
			$data11['diinput_tanggal']=date("Y-m-d H:i:s", time());

			//$this->patching->replace_data('perkara_penetapan',$data11);
			array_push($data_perkara_penetapan, $data11);
			
			if(!empty($row['penetapan_majelis_hakim'])){
			$data_update_1['tahapan_terakhir_id']=12;
			$data_update_1['tahapan_terakhir_text']='Penetapan';
			$data_update_1['proses_terakhir_id']=20;
			$data_update_1['proses_terakhir_text']='Penetapan Majelis Hakim/Hakim';
			$data_update_1['perkara_id']=$row['perkara_id'];

			array_push($data_update_perkara1, $data_update_1);

			//if($this->patching->exe_script($update_perkara)){
			
				$data11_a['perkara_id']=$row['perkara_id'];
				$data11_a['tahapan_id']=12;
				$data11_a['tahapan_nama']='Penetapan';
				$data11_a['proses_id']=20;
				$data11_a['proses_nama']='Penetapan Majelis Hakim/Hakim';
				$data11_a['tanggal']=$row['penetapan_majelis_hakim'];
				$data11_a['diinput_oleh']='System';
				$data11_a['diinput_tanggal']=date("Y-m-d H:i:s", time());
				//$my_perkara11b=$this->patching->replace_data('perkara_proses',$data11_a);
				array_push($data_perkara_proses1, $data11_a);
			//}
			}

			if(!empty($row['penetapan_panitera_pengganti'])){

			$data_update_2['tahapan_terakhir_id']=12;
			$data_update_2['tahapan_terakhir_text']='Penetapan';
			$data_update_2['proses_terakhir_id']=30;
			$data_update_2['proses_terakhir_text']='Penunjukan Panitera Pengganti';
			$data_update_2['perkara_id']=$row['perkara_id'];

			array_push($data_update_perkara2, $data_update_2);
			
			//if($this->patching->exe_script($update_perkara1)){
				$data11_b['perkara_id']=$row['perkara_id'];
				$data11_b['tahapan_id']=12;
				$data11_b['tahapan_nama']='Penetapan';
				$data11_b['proses_id']=30;
				$data11_b['proses_nama']='Penunjukan Panitera Pengganti';
				$data11_b['tanggal']=$row['penetapan_panitera_pengganti'];
				$data11_b['diinput_oleh']='System';
				$data11_b['diinput_tanggal']=date("Y-m-d H:i:s", time());
				
				//$this->patching->replace_data('perkara_proses',$data11_b);
				array_push($data_perkara_proses2, $data11_b);
			//}
			}

			if(!empty($row['penetapan_hari_sidang'])){
			$data_update_3['tahapan_terakhir_id']=12;
			$data_update_3['tahapan_terakhir_text']='Penetapan';
			$data_update_3['proses_terakhir_id']=80;
			$data_update_3['proses_terakhir_text']='Penetapan Hari Sidang Pertama';
			$data_update_3['perkara_id']=$row['perkara_id'];

			array_push($data_update_perkara3, $data_update_3);

			
			//if($this->patching->exe_script($update_perkara2)){
				$data11_c['perkara_id']=$row['perkara_id'];
				$data11_c['tahapan_id']=12;
				$data11_c['tahapan_nama']='Penetapan';
				$data11_c['proses_id']=80;
				$data11_c['proses_nama']='Penetapan Hari Sidang Pertama';
				$data11_c['tanggal']=$row['penetapan_hari_sidang'];
				$data11_c['diinput_oleh']='System';
				$data11_c['diinput_tanggal']=date("Y-m-d H:i:s", time());				
				
				//$my_perkara11d=$this->patching->replace_data('perkara_proses',$data11_c);
				array_push($data_perkara_proses3, $data11_c);
			//}
			}
			//$my_perkara11=TRUE;
		}		

		$this->db->replace_batch('perkara_penetapan',$data_perkara_penetapan);
		$this->db->update_batch('perkara', $data_update_perkara1, 'perkara_id');
		$this->db->replace_batch('perkara_proses',$data_perkara_proses1);
		$this->db->update_batch('perkara', $data_update_perkara2, 'perkara_id');
		$this->db->replace_batch('perkara_proses',$data_perkara_proses2);
		$this->db->update_batch('perkara', $data_update_perkara3, 'perkara_id');
		$this->db->replace_batch('perkara_proses',$data_perkara_proses3);


		$my_perkara11d = true;	

		if($my_perkara11d){
			$this->nativesession->set('update_progress', 11); 
			session_write_close();
			session_start();
			unset($data_perkara_penetapan);
			unset($data_update_perkara1);
			unset($data_perkara_proses1);
			unset($data_update_perkara2);
			unset($data_perkara_proses2);
			unset($data_update_perkara3);
			unset($data_perkara_proses3);

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query $my_perkara11d'));
			return;
		}
		}

		//temporary_hakim_Mysql
		$sql_temp_hakim = "DROP TABLE IF EXISTS hakim_pn_temp";
		$this->patching->exe_script($sql_temp_hakim);
		$sql1 = "CREATE TABLE hakim_pn_temp (
				  id INT NOT NULL AUTO_INCREMENT,
				  nama_gelar VARCHAR(250),
				  nama_clean VARCHAR(250),
				  KEY(id)
				  ) ENGINE=MyISAM;";
		
		if($this->patching->exe_script($sql1)){
			$this->nativesession->set('update_progress', 12); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query sql.'));
			return;
		}



		//--hakim_pn
		$ms_perkara12 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_hakim_pn') BEGIN
    					DROP VIEW dbo.v_hakim_pn
						END";
		
		if(exe_sqlserver($conn,$ms_perkara12)){
			$this->nativesession->set('update_progress', 13); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara12.'));
			return;
		}

		$ms_perkara12a= "CREATE VIEW v_hakim_pn AS						
						SELECT 
							ROW_NUMBER() OVER( ORDER BY hakim)		as id,
							1										as status_hakim_id,	
							hakim									as nama,
							hakim									as nama_gelar,
							'Y'										as aktif 
						from 
							(select km as hakim from ms_datahakim union
							select ag1 as hakim from ms_datahakim union
							select ag2 as hakim from ms_datahakim)
						as datahakim where hakim is not null
					";
		
		if(exe_sqlserver($conn,$ms_perkara12a)){
			$this->nativesession->set('update_progress', 14); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara12a.'));
			return;
		}


		//--
		$ms_perkara12bt = "SELECT * from v_hakim_pn";
		$ms_perkara12bt_exe = exe_sqlserver($conn,$ms_perkara12bt);
		$jml_data = odbc_num_rows($ms_perkara12bt_exe);
		if($jml_data>0){
		
		$data_hakimpn_t=array();
		while($row = odbc_fetch_array($ms_perkara12bt_exe)){		
			//$this->load->helper('text');
			$data12t['nama_gelar']=ascii_to_entities($row['nama_gelar']);
			$data12t['nama_clean']=$this->templatehelper->cleanNamaGelar(ascii_to_entities($row['nama_gelar']));
			array_push($data_hakimpn_t, $data12t);
			//$my_perkara12 = $this->patching->replace_data('hakim_pn',$data12);
		}		

		$this->db->replace_batch('hakim_pn_temp',$data_hakimpn_t);
		$my_perkara12=true;
		if($my_perkara12){
			$this->nativesession->set('update_progress', 15); 
			session_write_close();
			session_start();
			unset($data_hakimpn_t);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query $my_perkara12'));
			return;
		}
		}

		//temporary_hakim_Mysql
		$sql_hpn1 = "DROP VIEW IF EXISTS v_hakim_pn_temp";
		$sql_hpn2 = "CREATE VIEW v_hakim_pn_temp AS SELECT *,NULL AS id_baru FROM hakim_pn_temp GROUP BY nama_clean;";
		$sql_hpn3 = "REPLACE INTO hakim_pn
						SELECT
						    (@cnt := @cnt + 1) AS id,
						    1 AS status_hakim_id,
						    '' 	   	   AS kode,
						    '' 		   AS nip,
						    NULL 	   AS pangkat,
						    NULL 	   AS jabatan,  
						    hpt.nama_gelar AS nama,
						    hpt.nama_gelar AS nama_gelar,
						    'Y' 	   AS aktif,
						    NULL 	   AS mulai,
						    '' 		   AS keterangan,
						    NULL 	   AS foto,
						    '' 		   AS diedit_oleh,
						    NULL 	   AS diedit_tanggal,
						    'System' 	   AS diinput_oleh,
						    NOW() 	   AS diinput_tanggal,
						    '' 		   AS diperbaharui_oleh,
						    NULL 	   AS diperbaharui_tanggal
						FROM v_hakim_pn_temp AS hpt
						  CROSS JOIN (SELECT @cnt := 0) AS temp_id";
		$sql_hpn4 = "DROP TABLE IF EXISTS hakim_pn_temp2";
		$sql_hpn5 = "CREATE TABLE hakim_pn_temp2 AS SELECT * FROM v_hakim_pn_temp";
		$sql_hpn6 = "ALTER TABLE hakim_pn_temp2 CHANGE id_baru id_baru INT(11) NULL";
		$sql_hpn7 = "UPDATE hakim_pn_temp2 AS hpt2 SET id_baru=(SELECT id FROM hakim_pn WHERE nama_gelar=hpt2.nama_gelar)";

		$hpn1=$this->patching->exe_script($sql_hpn1);
		$hpn2=$this->patching->exe_script($sql_hpn2);
		$hpn3=$this->patching->exe_script($sql_hpn3);
		$hpn4=$this->patching->exe_script($sql_hpn4);
		$hpn5=$this->patching->exe_script($sql_hpn5);
		$hpn6=$this->patching->exe_script($sql_hpn6);
		$hpn7=$this->patching->exe_script($sql_hpn7);	
		
		if($hpn1 && $hpn2 && $hpn2 && $hpn3 && $hpn4 && $hpn5 && $hpn6 && $hpn7){
			$this->nativesession->set('update_progress', 16); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query sql.'));
			return;
		}


		//-- perkara_hakim_pn		
		$ms_perkara13 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_hakim_pn') BEGIN
    					DROP VIEW dbo.v_perkara_hakim_pn
						END";
		
		if(exe_sqlserver($conn,$ms_perkara13)){
			$this->nativesession->set('update_progress', 17); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara13.'));
			return;
		}

		$ms_perkara13a= "CREATE VIEW v_perkara_hakim_pn AS						
						 SELECT 	
								ROW_NUMBER() OVER( ORDER BY perkara_id)	   as id,
								A.perkara_id							   as perkara_id,  
								(case 
									when A.tingkat=0 then 10
									when A.tingkat=2 then 20
									when A.tingkat=3 then 30
									when A.tingkat=4 then 40
								end)										as tahapan_id,
								A.penetapan_majelis_hakim					as penetapan_majelis_hakim,
								A.nomor_sk_majelis_hakim			as nomor_sk_penetapan,
								ROW_NUMBER()OVER(Partition By perkara_id Order By perkara_id) as urutan,
								(case
									when ROW_NUMBER()OVER(Partition By perkara_id Order By perkara_id)=1 then 1
									else 2
								end) as jabatan_hakim_id,
								(case
									when ROW_NUMBER()OVER(Partition By perkara_id Order By perkara_id)=1 then 'Hakim Ketua'
									else 'Hakim Anggota'
								end) as jabatan_hakim_nama,
								NULL as hakim_id,
								Split.a.value('.', 'VARCHAR(200)')		   AS hakim_nama,
								'Y'						   as aktif		
						FROM  (SELECT			
									perkara_id,
									tingkat,
									penetapan_majelis_hakim,
									nomor_sk_majelis_hakim,			
									CAST ('<M>' + REPLACE(majelis_hakim_nama, '<br/>', '</M><M>') + '</M>' AS XML) AS hakim			
								FROM  v_perkara_penetapan) AS A CROSS APPLY hakim.nodes ('/M') AS Split(a)
						where A.tingkat is not null						
					";
		
		if(exe_sqlserver($conn,$ms_perkara13a)){
			$this->nativesession->set('update_progress', 18); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara13a.'));
			return;
		}


		$ms_perkara13b = "SELECT * from v_perkara_hakim_pn where hakim_nama is not null";
		$ms_perkara13b_exe = exe_sqlserver($conn,$ms_perkara13b);
		$jml_data = odbc_num_rows($ms_perkara13b_exe);
	   if($jml_data>0){

		$data_perkara_hakimpn=array();				
		while($row = odbc_fetch_array($ms_perkara13b_exe)){
			$nama_hakim_clean=$this->templatehelper->cleanNamaGelar($row['hakim_nama']);		
			$id_hakim_baru=$this->patching->get_id_hakim_baru($nama_hakim_clean)->result();
			$id_hakim=$id_hakim_baru[0]->id_baru;			

			$data13['perkara_id']=$row['perkara_id'];
			$data13['tahapan_id']=$row['tahapan_id'];
			$data13['tanggal_penetapan']=$row['penetapan_majelis_hakim'];
			$data13['nomor_sk_penetapan']=$row['nomor_sk_penetapan'];
			$data13['urutan']=$row['urutan'];
			$data13['jabatan_hakim_id']=$row['jabatan_hakim_id'];
			$data13['jabatan_hakim_nama']=$row['jabatan_hakim_nama'];
			$data13['hakim_id']=$id_hakim;
			$data13['hakim_nama']=$row['hakim_nama'];
			$data13['aktif']=$row['aktif'];
			$data13['diinput_oleh']='System';
			$data13['diinput_tanggal']=date("Y-m-d H:i:s", time());
			
			array_push($data_perkara_hakimpn, $data13);           
			//file_put_contents('arrayhakim5.txt', print_r($data_perkara_hakimpn,true)); 
 		    //$my_perkara13 = $this->patching->replace_data('perkara_hakim_pn',$data13);
		}		
		             
		$this->db->replace_batch('perkara_hakim_pn',$data_perkara_hakimpn);
		$my_perkara13=true;

		if($my_perkara13){
			$this->nativesession->set('update_progress', 19); 
			session_write_close();
			session_start();
			unset($data_perkara_hakimpn);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query $my_perkara13'));
			return;
		}
		
		}



		//temporary_panitera_Mysql
		$sql_temp_panitera = "DROP TABLE IF EXISTS panitera_pn_temp";
		$this->patching->exe_script($sql_temp_panitera);
		$sql1p = "CREATE TABLE panitera_pn_temp (
				  id INT NOT NULL AUTO_INCREMENT,
				  nama_gelar VARCHAR(250),
				  nama_clean VARCHAR(250),
				  KEY(id)
				  ) ENGINE=MyISAM;";
		
		if($this->patching->exe_script($sql1p)){
			$this->nativesession->set('update_progress', 20); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query sql1p.'));
			return;
		}



		//--panitera_pn
		$ms_perkara14 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_panitera_pn') BEGIN
    					DROP VIEW dbo.v_panitera_pn
						END";
		
		if(exe_sqlserver($conn,$ms_perkara14)){
			$this->nativesession->set('update_progress', 21); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara14.'));
			return;
		}

		$ms_perkara14a= "CREATE VIEW v_panitera_pn AS						
						select 
							ROW_NUMBER() OVER( ORDER BY panitera)		as id,
							panitera									as nama,
							panitera									as nama_gelar,
							'Y'											as aktif 
						from 
							(select pp1 as panitera from ms_datahakim union
							select pp2 as panitera from ms_datahakim)
						as datapanitera where panitera is not null
					";
		
		if(exe_sqlserver($conn,$ms_perkara14a)){
			$this->nativesession->set('update_progress', 22); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara14a.'));
			return;
		}


		$ms_perkara14b = "SELECT * from v_panitera_pn";
		$ms_perkara14b_exe = exe_sqlserver($conn,$ms_perkara14b);
		$jml_data = odbc_num_rows($ms_perkara14b_exe);
		if($jml_data>0){

		$data_paniterapn=array();
		while($row = odbc_fetch_array($ms_perkara14b_exe)){		

			$data14t['nama_gelar']=ascii_to_entities($row['nama_gelar']);
			$data14t['nama_clean']=$this->templatehelper->cleanNamaGelar(ascii_to_entities($row['nama_gelar']));

			array_push($data_paniterapn, $data14t);
			//$my_perkara14 = $this->patching->replace_data('panitera_pn',$data14);
		}		
		
		$this->db->replace_batch('panitera_pn_temp',$data_paniterapn);
		$my_perkara14=true;
				
		if($my_perkara14){
			$this->nativesession->set('update_progress', 23); 
			session_write_close();
			session_start();
			unset($data_paniterapn);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query $my_perkara14'));
			return;
		}
		}


		//temporary_hakim_Mysql
		$sql_ppn1 = "DROP VIEW IF EXISTS v_panitera_pn_temp";
		$sql_ppn2 = "CREATE VIEW v_panitera_pn_temp AS SELECT *,NULL AS id_baru FROM panitera_pn_temp GROUP BY nama_clean;";
		$sql_ppn3 = "REPLACE INTO panitera_pn
						SELECT
						    (@cnt := @cnt + 1) AS id,
						    '' 	   	   AS kode,
						    '' 		   AS nip,
						    NULL 	   AS pangkat,
						    NULL 	   AS jabatan,  
						    ppt.nama_gelar AS nama,
						    ppt.nama_gelar AS nama_gelar,
						    '' 		   AS keterangan,
						    'Y' 	   AS aktif,
						    NULL 	   AS foto,
						    '' 		   AS diedit_oleh,
						    NULL 	   AS diedit_tanggal,
						    'System' 	   AS diinput_oleh,
						    NOW() 	   AS diinput_tanggal,
						    '' 		   AS diperbaharui_oleh,
						    NULL 	   AS diperbaharui_tanggal
						FROM v_panitera_pn_temp AS ppt
						  CROSS JOIN (SELECT @cnt := 0) AS temp_id";
		$sql_ppn4 = "DROP TABLE IF EXISTS panitera_pn_temp2";
		$sql_ppn5 = "CREATE TABLE panitera_pn_temp2 AS SELECT * FROM v_panitera_pn_temp";
		$sql_ppn6 = "ALTER TABLE panitera_pn_temp2 CHANGE id_baru id_baru INT(11) NULL";
		$sql_ppn7 = "UPDATE panitera_pn_temp2 AS ppt2 SET id_baru=(SELECT id FROM panitera_pn WHERE nama_gelar=ppt2.nama_gelar)";

		$ppn1=$this->patching->exe_script($sql_ppn1);
		$ppn2=$this->patching->exe_script($sql_ppn2);
		$ppn3=$this->patching->exe_script($sql_ppn3);
		$ppn4=$this->patching->exe_script($sql_ppn4);
		$ppn5=$this->patching->exe_script($sql_ppn5);
		$ppn6=$this->patching->exe_script($sql_ppn6);
		$ppn7=$this->patching->exe_script($sql_ppn7);	
		
		if($ppn1 && $ppn2 && $ppn2 && $ppn3 && $ppn4 && $ppn5 && $ppn6 && $ppn7){
			$this->nativesession->set('update_progress', 24); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query sql.'));
			return;
		}


		//-- perkara_panitera_pn		
		$ms_perkara15 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_panitera_pn') BEGIN
    					DROP VIEW dbo.v_perkara_panitera_pn
						END";
		
		if(exe_sqlserver($conn,$ms_perkara15)){
			$this->nativesession->set('update_progress', 27); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara15.'));
			return;
		}

		$ms_perkara15a = " CREATE VIEW v_perkara_panitera_pn AS						
						   select
							ROW_NUMBER() OVER( ORDER BY A.perkara_id)	as id,
							A.perkara_id,
							(case 
								when A.tingkat=0 then 10
								when A.tingkat=2 then 20
								when A.tingkat=3 then 30
								when A.tingkat=4 then 40
							end)										as tahapan_id,
							A.penetapan_majelis_hakim					as tanggal_penetapan,
							A.nomor_sk_majelis_hakim			as nomor_sk_penetapan,
							1											as urutan,
							NULL										as panitera_id,
							A.panitera_pengganti_text					as panitera_nama,
							'Y'											as aktif
						from v_perkara_penetapan as A where A.tingkat is not null and A.tingkat<>1
												";
		
		if(exe_sqlserver($conn,$ms_perkara15a)){
			$this->nativesession->set('update_progress', 30); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara15a.'));
			return;
		}


		$ms_perkara15b = "SELECT * from v_perkara_panitera_pn where panitera_nama is not null";
		$ms_perkara15b_exe = exe_sqlserver($conn,$ms_perkara15b);
		$jml_data = odbc_num_rows($ms_perkara15b_exe);
		if($jml_data>0){
		
		$data_perkara_paniterapn=array();
		while($row = odbc_fetch_array($ms_perkara15b_exe)){		
			$nama_panitera_clean=$this->templatehelper->cleanNamaGelar($row['panitera_nama']);		
			$id_panitera_baru=$this->patching->get_id_panitera_baru($nama_panitera_clean)->result();
			$id_panitera=$id_panitera_baru[0]->id_baru;			
			
			$data15['id']=$row['id'];
			$data15['perkara_id']=$row['perkara_id'];
			$data15['tahapan_id']=$row['tahapan_id'];
			$data15['tanggal_penetapan']=$row['tanggal_penetapan'];
			$data15['nomor_sk_penetapan']=ascii_to_entities($row['nomor_sk_penetapan']);
			$data15['urutan']=$row['urutan'];
			$data15['panitera_id']=$id_panitera;
			$data15['panitera_nama']=ascii_to_entities($row['panitera_nama']);
			$data15['aktif']=$row['aktif'];
			$data15['diinput_oleh']='System';
			$data15['diinput_tanggal']=date("Y-m-d H:i:s", time());
			
			array_push($data_perkara_paniterapn, $data15);
			//$my_perkara15 = $this->patching->replace_data('perkara_panitera_pn',$data15);
		}		
		
		$this->db->replace_batch('perkara_panitera_pn',$data_perkara_paniterapn);
		$my_perkara15=true;
		if($my_perkara15){
			$this->nativesession->set('update_progress', 35); 
			session_write_close();
			session_start();
			unset($data_perkara_paniterapn);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query $my_perkara15'));
			return;
		}
		}


		//-- ruangan_sidang		
		$ms_perkara15rs = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_ruangan_sidang') BEGIN
    					DROP VIEW dbo.v_ruangan_sidang
						END";
		
		if(exe_sqlserver($conn,$ms_perkara15rs)){
			$this->nativesession->set('update_progress', 36); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara15rs.'));
			return;
		}

		$ms_perkara15rsa = " CREATE VIEW v_ruangan_sidang AS						
						   select 
								ROW_NUMBER() OVER( ORDER BY ruang)			as id,
								ruang,
								ruang as nama,
								'Y'	  as aktif
							from data_sidang group by ruang
												";
		
		if(exe_sqlserver($conn,$ms_perkara15rsa)){
			$this->nativesession->set('update_progress', 36); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara15rsa.'));
			return;
		}


		$ms_perkara15rsb = "SELECT * from v_ruangan_sidang";
		$ms_perkara15rsb_exe = exe_sqlserver($conn,$ms_perkara15rsb);
		$jml_data = odbc_num_rows($ms_perkara15rsb_exe);
		if($jml_data>0){

		$data_ruangan_sidang=array();		
		while($row = odbc_fetch_array($ms_perkara15rsb_exe)){		
			//$this->load->helper('text');
			$data15rs['id']=$row['id'];
			$data15rs['nama']=substr(ascii_to_entities($row['nama']),0,499);;
			$data15rs['kode']=$row['id'];
			$data15rs['aktif']=$row['aktif'];
			$data15rs['diinput_oleh']='System';
			$data15rs['diinput_tanggal']=date("Y-m-d H:i:s", time());
			
			array_push($data_ruangan_sidang,$data15rs);
			//$my_perkara15rs = $this->patching->replace_data('ruangan_sidang',$data15rs);
		}		
		
		$this->db->replace_batch('ruangan_sidang',$data_ruangan_sidang);
		$my_perkara15rs=true;

		if($my_perkara15rs){
			$this->nativesession->set('update_progress', 36); 
			session_write_close();
			session_start();
			unset($data_ruangan_sidang);

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query $my_perkara15rs'));
			return;
		}
		}

		//--perkara_jadwal_sidang
		$ms_perkara16 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_jadwal_sidang') BEGIN
    					DROP VIEW dbo.v_perkara_jadwal_sidang
						END";
		
		if(exe_sqlserver($conn,$ms_perkara16)){
			$this->nativesession->set('update_progress', 38); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara16.'));
			return;
		}

		$ms_perkara16a = "  CREATE VIEW v_perkara_jadwal_sidang AS		
							SELECT
								ROW_NUMBER() OVER( ORDER BY TANGGAL,NO_PERK )			as id,
								ds.tingkat												as tingkat,
								NO_PERK													as no_perkara_siad,
								vp.perkara_id											as perkara_id,
								ROW_NUMBER()OVER(Partition By vp.perkara_id Order By TANGGAL) as urutan,
								convert(varchar(10),TANGGAL,120)						as tanggal_sidang,
								'09:00:00'												as jam_sidang,
								RUANG													as ruangan_id,
								(case
									when ROW_NUMBER()OVER(Partition By vp.perkara_id Order By TANGGAL)=1 then 'Sidang Pertama'
								end)													as agenda,
								RUANG													as ruang,
								ALASAN_TUNDA											as alasan_ditunda

							from data_sidang as ds
							join v_perkara as vp on vp.no_perkara_siad=ds.NO_PERK and ds.tingkat=vp.tingkat
							where cast(right(ds.no_perk,2) as INT) >=15 and ds.TANGGAL IS NOT NULL

						";
		
		if(exe_sqlserver($conn,$ms_perkara16a)){
			$this->nativesession->set('update_progress', 40); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara16a.'));
			return;
		}


		$ms_perkara16b = "SELECT * from v_perkara_jadwal_sidang where tanggal_sidang is not null";
		$ms_perkara16b_exe = exe_sqlserver($conn,$ms_perkara16b);
		$jml_data = odbc_num_rows($ms_perkara16b_exe);
		if($jml_data>0){
		
		$data_perkara_jadwalsidang=array();
		$data_update_perkara_sdg=array();
		$data_perkara_proses_sdg=array();
		while($row = odbc_fetch_array($ms_perkara16b_exe)){		
			//$this->load->helper('text');
			$data16['id']=$row['id'];
			$data16['perkara_id']=$row['perkara_id'];			
			$data16['urutan']=$row['urutan'];
			$data16['tanggal_sidang']=$row['tanggal_sidang'];
			$data16['jam_sidang']='09:00:00'; 
			$data16['agenda']=$row['agenda'];
			$data16['ruangan_id']=$row['ruangan_id'];
			$data16['ruangan']=$row['ruang'];
			$data16['alasan_ditunda']=$row['alasan_ditunda'];
			$data16['sidang_ditempat']=1;

			$data16['diinput_oleh']='System';
			$data16['diinput_tanggal']=date("Y-m-d H:i:s", time());

			array_push($data_perkara_jadwalsidang, $data16);
			//$this->patching->replace_data('perkara_jadwal_sidang',$data16);

			if(!empty($row['tanggal_sidang'])){

			$data_update_sdg['tahapan_terakhir_id']=14;
			$data_update_sdg['tahapan_terakhir_text']='Persidangan';
			$data_update_sdg['proses_terakhir_id']=200;
			$data_update_sdg['proses_terakhir_text']='Persidangan';
			$data_update_sdg['perkara_id']=$row['perkara_id'];

			array_push($data_update_perkara_sdg, $data_update_sdg);
			
//			if($this->patching->exe_script($update_perkara_sdg)){
				$data16_s['perkara_id']=$row['perkara_id'];
				$data16_s['tahapan_id']=14;
				$data16_s['tahapan_nama']='Persidangan';
				$data16_s['proses_id']=200;
				$data16_s['proses_nama']='Persidangan';
				$data16_s['tanggal']=$row['tanggal_sidang'];
				$data16_s['diinput_oleh']='System';
				$data16_s['diinput_tanggal']=date("Y-m-d H:i:s", time());
				array_push($data_perkara_proses_sdg, $data16_s);

				//$my_perkara16=$this->patching->replace_data('perkara_proses',$data16_s);
//			}
			}
			//$my_perkara16=TRUE;
		}		
		
		$this->db->replace_batch('perkara_jadwal_sidang',$data_perkara_jadwalsidang);
		$this->db->update_batch('perkara', $data_update_perkara_sdg, 'perkara_id');
		$this->db->replace_batch('perkara_proses',$data_perkara_proses_sdg);

		$my_perkara16=TRUE;
		
		if($my_perkara16){			
			$this->nativesession->set('update_progress', 43); 
			session_write_close();
			session_start();
			unset($data_perkara_jadwalsidang);
			unset($data_update_perkara_sdg);
			unset($data_perkara_proses_sdg);

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query $my_perkara16'));
			return;
		}
		}


		//-- perkara_putusan		
		$ms_perkara18 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_putusan') BEGIN
    					DROP VIEW dbo.v_perkara_putusan
						END";
		
		if(exe_sqlserver($conn,$ms_perkara18)){
			$this->nativesession->set('update_progress', 60); 
			session_write_close();
			session_start(); 

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara18.'));
			return;
		}

		$ms_perkara18a = "  CREATE VIEW v_perkara_putusan AS													
							SELECT	
								vp.perkara_id											as perkara_id,
								dr.NO_PERK												as no_perkara_siad,
								convert(varchar(10),dr.TGL_PUTUSAN,120)					as tanggal_putusan,	
								(CASE 
									WHEN dr.JENIS_P=0	THEN 62
									WHEN dr.JENIS_P=1	THEN 63
									WHEN dr.JENIS_P=2	THEN 64
									WHEN dr.JENIS_P=3	THEN 65
									WHEN dr.JENIS_P=4	THEN 66
									WHEN dr.JENIS_P=5	THEN 67
								END)													as status_putusan_id,
								(CASE 
									WHEN dr.JENIS_P=0	THEN 'Dikabulkan'
									WHEN dr.JENIS_P=1	THEN 'Ditolak'
									WHEN dr.JENIS_P=2	THEN 'Tidak Dapat Diterima'
									WHEN dr.JENIS_P=3	THEN 'Digugurkan'
									WHEN dr.JENIS_P=4	THEN 'Dicoret dari Register'
									WHEN dr.JENIS_P=5	THEN 'Dicabut'
								END)													as status_putusan_nama,
								(CASE 
									WHEN dr.JENIS_P=0	THEN 'Dikabulkan'
									WHEN dr.JENIS_P=1	THEN 'Ditolak'
									WHEN dr.JENIS_P=2	THEN 'Tidak Dapat Diterima'
									WHEN dr.JENIS_P=3	THEN 'Digugurkan'
									WHEN dr.JENIS_P=4	THEN 'Dicoret dari Register'
									WHEN dr.JENIS_P=5	THEN 'Dicabut'
								END)													as status_putusan_text,
								(CASE 
									WHEN dr.JENIS_P=5	
										THEN convert(varchar(10),dr.TGL_PUTUSAN,120)
								END)													as tanggal_cabut,
								(CASE 
									WHEN dr.JENIS_P=3	
										THEN convert(varchar(10),dr.TGL_PUTUSAN,120)
								END)													as tanggal_gugur,
								replace(convert(varchar(8000),da.AMAR),char(10),'</br>')		as amar_putusan,
								convert(varchar(10),dr.TGL_MINUTASI,120)				as tanggal_minutasi,
								convert(varchar(10),dr.TGL_BHT,120)						as tanggal_bht,
								(case
									when (select dta.NO_PERK from data_amar as dta where dta.NO_PERK=dr.NO_PERK and dta.amar like '%verstek%' group by NO_PERK) IS NOT NULL THEN 'Y'
									else 'T'
								end)													as putusan_verstek

							from DATA_REGISTER as dr 
							left join v_perkara as vp on vp.no_perkara_siad=dr.NO_PERK and dr.TINGKAT=vp.tingkat
							left join DATA_AMAR as da on dr.NO_PERK=da.NO_PERK and dr.TINGKAT=vp.tingkat
							where cast(right(dr.no_perk,2) as INT) >=15 and dr.TGL_PUTUSAN is not null and vp.perkara_id is not null				
							";
		
		if(exe_sqlserver($conn,$ms_perkara18a)){
			$this->nativesession->set('update_progress', 63); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara18a.'));
			return;
		}


		$ms_perkara18b = "SELECT * from v_perkara_putusan where tanggal_putusan is not null";
		$ms_perkara18b_exe = exe_sqlserver($conn,$ms_perkara18b);

		$jml_data = odbc_num_rows($ms_perkara18b_exe);
		if($jml_data>0){

		$data_perkara_putusan=array();		
		$data_update_perkarapts=array();
		$data_perkara_prosespts=array();
		while($row = odbc_fetch_array($ms_perkara18b_exe)){		
			
			$data18['perkara_id']=$row['perkara_id'];
			$data18['tanggal_putusan']=$row['tanggal_putusan'];
			$data18['putusan_verstek']=$row['putusan_verstek'];
			$data18['status_putusan_id']=$row['status_putusan_id'];
			$data18['status_putusan_nama']=$row['status_putusan_nama'];
			$data18['status_putusan_text']=$row['status_putusan_id'];
			$amar=str_replace(array("\pard","\par"), array("</br>","</br>"), ascii_to_entities($row['amar_putusan']));;
			$amar_putusan=$amar;
			$amar_putusan_br=preg_replace('(\'"{\\*?\\\\.+(;})|\\s?\\\\[A-Za-z0-9]+|\\s?{\\s?\\\\[A-Za-z0-9]+\\s?|\\s?}\\s?"\')','',$amar_putusan, -1, $count);
			$cari=array("Courier New;}","Arial Narrow;}","Arial Narrow;}","Times New Roman;}","Calibri;}","Calibri;}","Times New Roman;}};}","Normal;}","{\* Default Paragraph Font;}","Body Text;}","{\* Body Text Text;}","List Paragraph;}","No Spacing;}}{\*","-720\'02\'00.;}\'01;}}","-360\'02\'01.;}\'01;}}","-180\'02\'02.;}\'01;}}","-360\'02\'03.;}\'01;}}","-360\'02\'04.;}\'01;}}","-180\'02\'05.;}\'01;}}","-360\'02\'06.;}\'01;}}","-360\'02\'07.;}\'01;}}","-180\'02\'08.;}\'01;}}","\'02\'00.;}\'01;}}","-360\'02\'07.;}","Arial;}","};;}","Body Text Indent 3;}{\* Body Text Indent 3 Text;}","No Spacing;}}","Wingdings;}","Symbol;}};;}","header;}{\* header Text;}-240","Body Text Indent 2;}{\* Body Text Indent 2 Text;}","Body Text Indent;}{\* Body Text Indent Text;}","Body Text,Char;}{\* Body Text,Char Text;}","{\* page number;}","{\* Strong;}","Body Text,Char Char,Char Char1,Char Char Char,Body Text Char1,Char Char2,Body Text Char Char,Char Char1 Char;}{\* Body Text,Char Char,Char Char1,Char Char Char,Body Text Char1,Char Char2,Body Text Char Char,Char Char1 Char Text;}}","};}","Symbol;}","-360\'01-;};}}","-360\'01o;};}}","-360\'01 ?;};}}","-360\'01o;};}}","-360\'02\'06.;}\'01;}","Body Text 2;}","{\* Body Text 2 Text;}-3420 -360 }","};;;}"," {\* Body Text 2 Text;}}","-3420 }","header;}","{\* header Text;}}","{\* \'02\'00);}\'01;}}","{\* } }","}{\*","-705","      }","}","{\* Body Text 2 Text;",";;;;","{\* Body Text 2 Text;","\'02\'00);\'01;","{\*","\'02\'00.;\'0","-240","{\* header Text; ","header Text;","Body Text,","Char","Char;","Char","Text;","-360\'02\'02);\'01;","-360\'02\'08.;\'01;","-1080\'01\'01;\'01;","-360\'02\'06.;{","-1080","heading 1;","heading 1 Text;","{\* header Text;","{\* Body Text 2 Text;","Subtitle;","Subtitle Text;","Plain Text;","Plain Text Text;","footer;","footer Text;","Block Text;","-1080","-285","-360\'02\'02.;\'01;","heading 7;","heading 7 -360","-420","-360\'02\'05.;\'01;",", 1,  ,Body Text 1, 2,Body Text  , 1 ;  , 1,  ,Body Text 1, 2,Body Text  , 1","-2160","-720\'01\'00;\'01;","-690\'02\'01.;\'01;","-330",",, ;  ,,  -720  No Spacing;","-180{");
			$ganti=array('','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
			$putusan_clean=str_replace($cari, $ganti, $amar_putusan_br);
			$data18['amar_putusan']=$putusan_clean;
			$data18['tanggal_minutasi']=$row['tanggal_minutasi'];
			$data18['tanggal_cabut']=$row['tanggal_cabut'];
			$data18['tanggal_gugur']=$row['tanggal_gugur'];
			$data18['tanggal_bht']=$row['tanggal_bht'];

			$data18['diinput_oleh']='System';
			$data18['diinput_tanggal']=date("Y-m-d H:i:s", time());

			array_push($data_perkara_putusan, $data18);
			//$this->patching->replace_data('perkara_putusan',$data18);

			if(!empty($row['tanggal_putusan'])){

			$data_update_pts['tahapan_terakhir_id']=15;
			$data_update_pts['tahapan_terakhir_text']='Putusan';
			$data_update_pts['proses_terakhir_id']=210;
			$data_update_pts['proses_terakhir_text']='Putusan';
			$data_update_pts['perkara_id']=$row['perkara_id'];

			array_push($data_update_perkarapts, $data_update_pts);
			
			//if($this->patching->exe_script($update_perkara_p)){
				$data11_p['perkara_id']=$row['perkara_id'];
				$data11_p['tahapan_id']=15;
				$data11_p['tahapan_nama']='Putusan';
				$data11_p['proses_id']=210;
				$data11_p['proses_nama']='Putusan';
				$data11_p['tanggal']=$row['tanggal_putusan'];
				$data11_p['diinput_oleh']='System';
				$data11_p['diinput_tanggal']=date("Y-m-d H:i:s", time());

				array_push($data_perkara_prosespts, $data11_p);
				//$my_perkara18=$this->patching->replace_data('perkara_proses',$data11_p);
			//}
			}
			
		}		
		
		$this->db->replace_batch('perkara_putusan',$data_perkara_putusan);
		$this->db->update_batch('perkara', $data_update_perkarapts, 'perkara_id');
		$this->db->replace_batch('perkara_proses',$data_perkara_prosespts);

		$my_perkara18=TRUE;
		
		if($my_perkara18){
			$this->nativesession->set('update_progress', 65); 
			session_write_close();
			session_start();
			unset($data_perkara_putusan);
			unset($data_update_perkarapts);
			unset($data_perkara_prosespts);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query $my_perkara18'));
			return;
		}
		}

		
		$sql = "DROP TABLE IF EXISTS idjenisbiaya_lama_baru";
		$this->patching->exe_script($sql);
		$sql1 = "CREATE TABLE idjenisbiaya_lama_baru (
						idjenisbiaya_lama bigint(20) unsigned NOT NULL,
						idjenisbiaya_baru bigint(20) unsigned NOT NULL
					) ENGINE=MyISAM;";
		
		if($this->patching->exe_script($sql1)){
			$this->nativesession->set('update_progress', 67); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query sql.'));
			return;
		}


		//idbiaya_baru_lama
		$ms_perkara27b = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_idjenisbiaya_lama_baru') BEGIN 
                		 DROP VIEW dbo.v_idjenisbiaya_lama_baru END";

        $ms_perkara27b_exe = exe_sqlserver($conn,$ms_perkara27b);
        if($ms_perkara27b_exe){
			$this->nativesession->set('update_progress', 68); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara27c.'));
			return;
		}
 
	 	$ms_perkara27c="CREATE VIEW v_idjenisbiaya_lama_baru AS
					  	select kode_siadpa,kode_sipp 
					  	from migrasi_var where kode_sipp is not null"; 

     if(exe_sqlserver($conn,$ms_perkara27c)){
			$this->nativesession->set('update_progress', 69); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara27b.migrasi variabel keuangan')); 
			return;
		}

	$ms_perkara27d = "SELECT * FROM v_idjenisbiaya_lama_baru";
	$ms_perkara27d_exe = exe_sqlserver($conn,$ms_perkara27d);
		
    $jml_data = odbc_num_rows($ms_perkara27d_exe);
		if($jml_data>0){

		$data_idbiaya_baru=array();			
		while($row = odbc_fetch_array($ms_perkara27d_exe)){

			$data27d['idjenisbiaya_lama']=$row['kode_siadpa']; 
			$data27d['idjenisbiaya_baru']=$row['kode_sipp']; 
	
			array_push($data_idbiaya_baru, $data27d);
			//$my_perkara30 = $this->patching->replace_data('perkara_biaya',$data30);
		}
		
		$this->db->replace_batch('idjenisbiaya_lama_baru',$data_idbiaya_baru);
		$my_perkara27d=true;
		if($my_perkara27d){
			$this->nativesession->set('update_progress', 70); 
			session_write_close();
			session_start();
			unset($data_idbiaya_baru);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara27d.'));
			return;
		}		
		}

		

/*
		$sql_jb = "REPLACE INTO idjenisbiaya_lama_baru 
				   VALUES 
				   (101000000,1),(102000000,224),(103000000,5),(104000000,50),
				   (105000000,25),(201020000,157),(202000000,61),(203000000,155),
				   (205000000,152),(212010000,12),(212020000,12),(212020100,12),
				   (212020200,12),(212020300,12),(212020400,12),(212020500,12),
				   (212030000,13),(212040000,13),(212050000,13),(212060000,13),
				   (212070000,13),(212080000,12),(212090000,12),(212100000,16),
				   (212110000,14),(212120000,12),(212130000,13),(212140000,13),
				   (212150000,13),(212160000,12),(212170000,12),(212180000,14),
				   (212190000,12),(213000000,317),(213010000,159),(213020000,160),
				   (213030000,27),(213040000,45),(214010000,29),(214020000,29),
				   (214030000,30),(214040000,31),(214040100,31),(214040200,31),
				   (214050000,32),(214060000,33),(214070100,34),(214070200,309),
				   (214080100,35),(214080200,312),(214090000,36),(214100000,37),
				   (214110100,36),(214110200,36),(214120100,39),(214120200,40),
				   (214130000,41),(214140000,42),(214150000,43),(214160000,43),
				   (214170000,42),(215000000,317),(215020000,317),(216010000,9),
				   (216020000,10),(216030000,11),(217000000,151),(219000000,45),
				   (220000000,153),(205010000,152),(212020600,12),(212190100,12),
				   (212190200,12),(212190300,12),(204000000,45),(214180000,29),
				   (212200000,12),(212210000,12),(107000000,1),(212020700,12),
				   (212220000,13),(212230000,13),(212240000,13),(212250000,13),
				   (212260000,13),(212270000,13),(212280000,13),(212290000,13),
				   (212300000,13),(212310000,13),(212320000,13),(212000000,12),(214000000,29)
				   ";
				
		if($this->patching->exe_script($sql_jb)){
			$this->nativesession->set('update_progress', 69); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query sql.'));
			return;
		}
		*/

		//perkara_akta_cerai

		$ms_perkara28 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_akta_cerai') BEGIN 
                		 DROP VIEW dbo.v_perkara_akta_cerai END";

        $ms_perkara28_exe = exe_sqlserver($conn,$ms_perkara28);
        if($ms_perkara28_exe){
			$this->nativesession->set('update_progress', 73); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara28.'));
			return;
		}
 
	 	$ms_perkara29="CREATE VIEW v_perkara_akta_cerai AS
					  SELECT 
							p.perkara_id		as perkara_id,
							dac.no_perk			as no_perkara_siad,
							substring(convert(varchar(10),tgl_ac,120),1,4) as tahun_akta_cerai,
							substring(dac.no_ac,1,len(dac.no_ac)-4) as nomor_urut_akta_cerai,
							dac.no_akta			as nomor_akta_cerai,
							convert(varchar(10),tgl_ac,120) as tgl_akta_cerai,
							no_seri_ac			as no_seri_akta_cerai,
							(case
								when gc=0 then 'Cerai Talak'
								when gc=1 then 'Cerai Gugat'
							end)				as jenis_cerai,
							(case
								WHEN faktor=0 THEN 14
								WHEN faktor=1 THEN 15
								WHEN faktor=2 THEN 16
								WHEN faktor=3 THEN 17
								WHEN faktor=4 THEN 10
								WHEN faktor=5 THEN 12
								WHEN faktor=6 THEN 18
								WHEN faktor=7 THEN 19
								WHEN faktor=9 THEN 20
								WHEN faktor=10 THEN 21
								WHEN faktor=11 THEN 22
								WHEN faktor=12 THEN 23
								WHEN faktor=13 THEN 24
								WHEN faktor=14 THEN 25
								WHEN faktor=15 THEN 26
							end)					as faktor_perceraian_id,
							QOBLABADA				as qobla_bada,
							KEADAANISTRI			as keadaan_istri,
							PERCERAIANKE			as perceraian_ke,
							tgl_ikrar 				as tgl_ikrar_talak
						
						from DATA_REG_AC as dac
						left join v_perkara as p on p.no_perkara_siad=dac.no_perk
						where cast(right(dac.no_perk,2) as int) >=15 and  p.perkara_id is not null and len (dac.no_ac) = 8
					"; 

     if(exe_sqlserver($conn,$ms_perkara29)){
			$this->nativesession->set('update_progress', 74); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara29.')); 
			return;
		}

	$ms_perkara29a = "SELECT * FROM v_perkara_akta_cerai";
	$ms_perkara29a_exe = exe_sqlserver($conn,$ms_perkara29a);
		
    $jml_data = odbc_num_rows($ms_perkara29a_exe);
		if($jml_data>0){

		$data_perkara_akta_cerai=array();			
		while($row = odbc_fetch_array($ms_perkara29a_exe)){

			$data29['perkara_id']=$row['perkara_id']; 
			$data29['tahun_akta_cerai']=$row['tahun_akta_cerai']; 
			$data29['nomor_urut_akta_cerai']=intval($row['nomor_urut_akta_cerai']); 
			$data29['nomor_akta_cerai']=ascii_to_entities($row['nomor_akta_cerai']); 
			$data29['tgl_akta_cerai']=$row['tgl_akta_cerai']; 
			$data29['no_seri_akta_cerai']=$row['no_seri_akta_cerai']; 
			$data29['jenis_cerai']=$row['jenis_cerai']; 
			$data29['faktor_perceraian_id']=$row['faktor_perceraian_id']; 
			$data29['qobla_bada']=$row['qobla_bada']; 
			$data29['keadaan_istri']=$row['keadaan_istri']; 
			$data29['perceraian_ke']=$row['perceraian_ke']; 

			$data29['diinput_oleh']='System';
			$data29['diinput_tanggal']=date("Y-m-d H:i:s", time());
			
			array_push($data_perkara_akta_cerai, $data29);
			//$my_perkara30 = $this->patching->replace_data('perkara_biaya',$data30);
		}
		
		$this->db->replace_batch('perkara_akta_cerai',$data_perkara_akta_cerai);
		$my_perkara29=true;
		if($my_perkara29){
			$this->nativesession->set('update_progress', 81); 
			session_write_close();
			session_start();
			unset($data_perkara_akta_cerai);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara29.'));
			return;
		}		
		}


		//perkara_ikrar_talak

		$ms_perkara28it = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_ikrar_talak') BEGIN 
                		 DROP VIEW dbo.v_perkara_ikrar_talak END";

        $ms_perkara28it_exe = exe_sqlserver($conn,$ms_perkara28it);
        if($ms_perkara28it_exe){
			$this->nativesession->set('update_progress', 82); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara28it.'));
			return;
		}
 
	 	$ms_perkara29it="CREATE VIEW v_perkara_ikrar_talak AS
					  select 
							vp.perkara_id 				as perkara_id,
							p.jenis_perkara_id 			as jenis_perkara_id,
							vp.tanggal_putusan 			as tanggal_putusan,
							vp.tanggal_bht 				as tanggal_bht,
							vpp.penetapan_majelis_hakim as penetapan_majelis_hakim,
							vpp.majelis_hakim_nama 		as majelis_hakim_nama,
							vpp.majelis_hakim_text 		as majelis_hakim_text,
							vpp.penetapan_panitera_pengganti as penetapan_panitera_pengganti,
							vpp.panitera_pengganti_text as panitera_pengganti_text,
							convert(varchar(10),vpa.tgl_ikrar_talak,120) as tanggal_sidang_pertama,
							convert(varchar(10),vpa.tgl_ikrar_talak,120) as tgl_ikrar_talak,
							(case
							    when vpa.tgl_ikrar_talak is not null then 1
							end)				as status_penetapan_ikrar_talak_id
							
						from v_perkara_putusan as vp
						left join v_perkara as p on p.perkara_id=vp.perkara_id
						left join v_perkara_penetapan as vpp on vpp.perkara_id=vp.perkara_id
						left join v_perkara_akta_cerai as vpa on vpa.perkara_id=vp.perkara_id
						where p.jenis_perkara_id=7;
					"; 

     if(exe_sqlserver($conn,$ms_perkara29it)){
			$this->nativesession->set('update_progress', 83); 
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara29it.')); 
			return;
		}

	$ms_perkara29ita = "SELECT * FROM v_perkara_ikrar_talak";
	$ms_perkara29ita_exe = exe_sqlserver($conn,$ms_perkara29ita);
		
    $jml_data = odbc_num_rows($ms_perkara29ita_exe);
		if($jml_data>0){

		$data_perkara_ikrar_talak=array();			
		while($row = odbc_fetch_array($ms_perkara29ita_exe)){

			$data29it['perkara_id']=$row['perkara_id']; 
			$data29it['penetapan_majelis_hakim']=$row['penetapan_majelis_hakim']; 
			$data29it['majelis_hakim_nama']=ascii_to_entities($row['majelis_hakim_nama']); 
			$data29it['majelis_hakim_text']=ascii_to_entities($row['majelis_hakim_text']); 
			$data29it['penetapan_panitera_pengganti']=$row['penetapan_panitera_pengganti']; 
			$data29it['panitera_pengganti_text']=ascii_to_entities($row['panitera_pengganti_text']); 
			$data29it['tanggal_sidang_pertama']=$row['tanggal_sidang_pertama']; 
			$data29it['tgl_ikrar_talak']=$row['tgl_ikrar_talak']; 
			$data29it['status_penetapan_ikrar_talak_id']=$row['status_penetapan_ikrar_talak_id']; 

			$data29it['diinput_oleh']='System';
			$data29it['diinput_tanggal']=date("Y-m-d H:i:s", time());
			
			array_push($data_perkara_ikrar_talak, $data29it);
			//$my_perkara30 = $this->patching->replace_data('perkara_biaya',$data30);
		}
		
	    $this->db->replace_batch('perkara_ikrar_talak',$data_perkara_ikrar_talak);
		$my_perkara29it=true;
		if($my_perkara29it){
			$this->nativesession->set('update_progress', 84); 
			session_write_close();
			session_start();
			unset($data_perkara_ikrar_talak);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara29it.'));
			return;
		}		
		}

	$list_id_biaya_lama=$this->patching->get_list_id_biaya_lama()->result();
	$cek_id_biaya_lama=$list_id_biaya_lama[0]->idjenisbiaya_lama;

		// perkara_biaya
		$ms_perkara30 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_biaya') BEGIN 
                		 DROP VIEW dbo.v_perkara_biaya END";

        $ms_perkara30_exe = exe_sqlserver($conn,$ms_perkara30);
        if($ms_perkara30_exe){
			$this->nativesession->set('update_progress', 85); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara30.'));
			return;
		}
 
	 	$ms_perkara30="CREATE VIEW v_perkara_biaya AS
					  SELECT
						ROW_NUMBER() OVER( ORDER BY tp.tgl_trans,tp.no_perk )	as id,
						tp.no_perk												as no_perkara_siad,
						vp.PRODEO												as id_pembiayaan,
						vp.perkara_id											as perkara_id,
						(case 
							when vp.tingkat=0 then 10
							when vp.tingkat=2 then 20
							when vp.tingkat=3 then 30
							when vp.tingkat=4 then 40
						end)													as tahapan_id,
						convert(int,tp.var_perk)								as jenis_biaya_id_lama,
						ROW_NUMBER()OVER(Partition By tp.no_perk Order By tgl_trans) as urutan,
						(case
							when vpp.debet=1 then 1
							when vpp.debet=0 then -1
						end)													as jenis_transaksi,
						convert(varchar(10),tp.tgl_trans,120)					as tanggal_transaksi,
						vpp.keterangan											as uraian,
						abs(tp.nil_trans)										as jumlah

					from v_perkara as vp
					left join trans_perk as tp on tp.NO_PERK=vp.no_perkara_siad
					left join var_perkiraan as vpp on tp.var_perk=vpp.kode
					where tp.tingkat=0 and tp.tgl_trans is not null and tp.nil_trans is not null
					"; 

     if(exe_sqlserver($conn,$ms_perkara30)){
			$this->nativesession->set('update_progress', 86); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara30.')); 
			return;
		}

	$ms_perkara30 = "SELECT * FROM v_perkara_biaya where jenis_biaya_id_lama in (".$cek_id_biaya_lama.")";
	$ms_perkara30_exe = exe_sqlserver($conn,$ms_perkara30);
		
    $jml_data = odbc_num_rows($ms_perkara30_exe);
		if($jml_data>0){

		$data_perkara_biaya=array();			
		while($row = odbc_fetch_array($ms_perkara30_exe)){
			$idjenisbiayabaru=$this->patching->get_id_jenisbiaya_baru($row['jenis_biaya_id_lama'])->result();
			$id_jenis_biaya_baru=$idjenisbiayabaru[0]->idjenisbiaya_baru;			

			$data30['id']=$row['id']; 
			$data30['id_pembiayaan']=$row['id_pembiayaan']; 
			$data30['perkara_id']=$row['perkara_id']; 
			$data30['tahapan_id']=$row['tahapan_id']; 
			$data30['jenis_biaya_id']=$id_jenis_biaya_baru;
			$data30['urutan']=$row['urutan']; 
			$data30['jenis_transaksi']=$row['jenis_transaksi']; 
			$data30['tanggal_transaksi']=$row['tanggal_transaksi']; 
			$data30['uraian']=$row['uraian']; 
			$data30['jumlah']=$row['jumlah']; 
			$data30['diinput_oleh']='System';
			$data30['diinput_tanggal']=date("Y-m-d H:i:s", time());
			
			array_push($data_perkara_biaya, $data30);
			//$my_perkara30 = $this->patching->replace_data('perkara_biaya',$data30);
		}
		//file_put_contents('arraybiayabantul.txt', print_r($data_perkara_biaya,true)); 
		$this->db->replace_batch('perkara_biaya',$data_perkara_biaya);
		$my_perkara30=true;
		if($my_perkara30){
			$this->nativesession->set('update_progress', 87); 
			session_write_close();
			session_start();
			unset($data_perkara_biaya);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara30.'));
			return;
		}		
		}

		// tambah
		$sql_update_kategori = "UPDATE perkara_biaya AS pb SET pb.kategori_id = (SELECT kategori_id FROM jenis_biaya WHERE id=pb.jenis_biaya_id)";
	
		if($this->patching->exe_script($sql_update_kategori)){
			$this->nativesession->set('update_progress', 100);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara30.'));
			return;
		}


		$del_sql_hpn1 = "DROP VIEW IF EXISTS v_hakim_pn_temp";
		$del_sql_hpn2 = "DROP TABLE IF EXISTS hakim_pn_temp";
		$del_sql_hpn3 = "DROP TABLE IF EXISTS hakim_pn_temp2";

		$del_sql_ppn1 = "DROP VIEW IF EXISTS v_panitera_pn_temp";
		$del_sql_ppn2 = "DROP TABLE IF EXISTS panitera_pn_temp";
		$del_sql_ppn3 = "DROP TABLE IF EXISTS panitera_pn_temp2";


		$del_sql_hpn4 = "DROP TABLE IF EXISTS idjenisbiaya_lama_baru";		
		$del_sql_hpn5 = "DROP TABLE IF EXISTS pihak2_idlama_idbaru";

		$this->patching->exe_script($del_sql_hpn1);
		$this->patching->exe_script($del_sql_hpn2);
		$this->patching->exe_script($del_sql_hpn3);

		$this->patching->exe_script($del_sql_ppn1);
		$this->patching->exe_script($del_sql_ppn2);
		$this->patching->exe_script($del_sql_ppn3);

		$this->patching->exe_script($del_sql_hpn4);
		$this->patching->exe_script($del_sql_hpn5);


		session_write_close();
		session_start();
		echo json_encode(array('st'=>1,'msg'=>'Migrasi Berhasil.'));
		return;

		$this->patching->exe_script('SET FOREIGN_KEY_CHECKS=1;');
		$this->patching->exe_script('SET autocommit = 1;');
		$this->patching->exe_script('commit;');	


	}


//$this->load->libraries('rtfelement');

function rtf2text($text) {


    // Create empty stack array.
    $document = "";
    $stack = array();
    $j = -1;
    // Read the data character-by- character…
    for ($i = 0, $len = strlen($text); $i < $len; $i++) {
        $c = $text[$i];

        // Depending on current character select the further actions.
        switch ($c) {
            // the most important key word backslash
            case "\\":
                // read next character
                $nc = $text[$i + 1];

                // If it is another backslash or nonbreaking space or hyphen,
                // then the character is plain text and add it to the output stream.
                if ($nc == '\\' && $this->rtf_isPlainText($stack[$j])) $document .= '\\';
                elseif ($nc == '~' && $this->rtf_isPlainText($stack[$j])) $document .= ' ';
                elseif ($nc == '_' && $this->rtf_isPlainText($stack[$j])) $document .= '-';
                // If it is an asterisk mark, add it to the stack.
                elseif ($nc == '*') $stack[$j]["*"] = true;
                // If it is a single quote, read next two characters that are the hexadecimal notation
                // of a character we should add to the output stream.
                elseif ($nc == "'") {
                    $hex = substr($text, $i + 2, 2);
                    if ($this->rtf_isPlainText($stack[$j]))
                        $document .= html_entity_decode("&#".hexdec($hex).";");
                    //Shift the pointer.
                    $i += 2;
                // Since, we’ve found the alphabetic character, the next characters are control word
                // and, possibly, some digit parameter.
                } elseif ($nc >= 'a' && $nc <= 'z' || $nc >= 'A' && $nc <= 'Z') {
                    $word = "";
                    $param = null;

                    // Start reading characters after the backslash.
                    for ($k = $i + 1, $m = 0; $k < strlen($text); $k++, $m++) {
                        $nc = $text[$k];
                        // If the current character is a letter and there were no digits before it,
                        // then we’re still reading the control word. If there were digits, we should stop
                        // since we reach the end of the control word.
                        if ($nc >= 'a' && $nc <= 'z' || $nc >= 'A' && $nc <= 'Z') {
                            if (empty($param))
                                $word .= $nc;
                            else
                                break;
                        // If it is a digit, store the parameter.
                        } elseif ($nc >= '0' && $nc <= '9')
                            $param .= $nc;
                        // Since minus sign may occur only before a digit parameter, check whether
                        // $param is empty. Otherwise, we reach the end of the control word.
                        elseif ($nc == '-') {
                            if (empty($param))
                                $param .= $nc;
                            else
                                break;
                        } else
                            break;
                    }
                    // Shift the pointer on the number of read characters.
                    $i += $m - 1;

                    // Start analyzing what we’ve read. We are interested mostly in control words.
                    $toText = "";
                    switch (strtolower($word)) {
                        // If the control word is "u", then its parameter is the decimal notation of the
                        // Unicode character that should be added to the output stream.
                        // We need to check whether the stack contains \ucN control word. If it does,
                        // we should remove the N characters from the output stream.
                        case "u":
                            $toText .= html_entity_decode("&#x".dechex($param).";");
                            $ucDelta = @$stack[$j]["uc"];
                            if ($ucDelta > 0)
                                $i += $ucDelta;
                        break;
                        // Select line feeds, spaces and tabs.
                        case "par": case "page": case "column": case "line": case "lbr":
                            $toText .= "\n"; 
                        break;
                        case "emspace": case "enspace": case "qmspace":
                            $toText .= " "; 
                        break;
                        case "tab": $toText .= "\t"; break;
                        // Add current date and time instead of corresponding labels.
                        case "chdate": $toText .= date("m.d.Y"); break;
                        case "chdpl": $toText .= date("l, j F Y"); break;
                        case "chdpa": $toText .= date("D, j M Y"); break;
                        case "chtime": $toText .= date("H:i:s"); break;
                        // Replace some reserved characters to their html analogs.
                        case "emdash": $toText .= html_entity_decode("&mdash;"); break;
                        case "endash": $toText .= html_entity_decode("&ndash;"); break;
                        case "bullet": $toText .= html_entity_decode("&#149;"); break;
                        case "lquote": $toText .= html_entity_decode("&lsquo;"); break;
                        case "rquote": $toText .= html_entity_decode("&rsquo;"); break;
                        case "ldblquote": $toText .= html_entity_decode("&laquo;"); break;
                        case "rdblquote": $toText .= html_entity_decode("&raquo;"); break;
                        // Add all other to the control words stack. If a control word
                        // does not include parameters, set &param to true.
                        default:
                            $stack[$j][strtolower($word)] = empty($param) ? true : $param;
                        break;
                    }
                    // Add data to the output stream if required.
                    if ($this->rtf_isPlainText($stack[$j]))
                        $document .= $toText;
                }

                $i++;
            break;
            // If we read the opening brace {, then new subgroup starts and we add
            // new array stack element and write the data from previous stack element to it.
            case "{":
                array_push($stack, $stack[$j++]);
            break;
            // If we read the closing brace }, then we reach the end of subgroup and should remove 
            // the last stack element.
            case "}":
                array_pop($stack);
                $j--;
            break;
            // Skip “trash”.
            case '\0': case '\r': case '\f': case '\n': break;
            // Add other data to the output stream if required.
            default:
                if ($this->rtf_isPlainText($stack[$j]))
                    $document .= $c;
            break;
        }
    }
    // Return result.
    return $document;
}
	
}




