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

class Migrasi_siadtun 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 SIADTUN';
		$data['main_body'] = 'migrasi/migrasi_tun';
		
		$data['data_asal'] = 'MSSQL - SIADTUN';
		$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 
		set_time_limit(0);

		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');
		$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);

		//--Buat Dimasukkin ke Table perkara---
		$ms_perkara1_vp1 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_pengacara_pihak1') BEGIN
    					DROP VIEW dbo.v_pengacara_pihak1
						END";

		$ms_perkara1_vp1_exe = exe_sqlserver($conn,$ms_perkara1_vp1);
		if($ms_perkara1_vp1_exe){
			$this->nativesession->set('update_progress', 1);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara1_vp1.'));
			return;
		}
		
		$ms_perkara2_vp1= " CREATE VIEW v_pengacara_pihak1 as 
							SELECT 
								pe.DREG_ID as DREG_ID,pe.DATA_ID as DATA_ID,pe.DBIO_NAMA as DBIO_NAMA,
								case
									when k.KEY_ID is not null then k.DKUASA_NAMA 
									when l.DREG_ID is not null then l.DKUASA_NAMA 
									else ''
								end as DKUASA_NAMA
							FROM (
										select 
											distinct
												DREG_ID,DATA_ID,
													(SELECT STUFF(
																 (SELECT '</br>' + DBIO_NAMA 
																  FROM DATA_PE md 
																  WHERE md.DATA_ID = m.DATA_ID
																  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
																) as DBIO_NAMA
													from DATA_PE m
								) AS pe

							left join (
								select 
									distinct
										KEY_ID,
											(SELECT STUFF(
														 (SELECT '</br>' + DKUASA_NAMA 
														  FROM DATA_KUASA_PE md 
														  WHERE md.KEY_ID = m.KEY_ID
														  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
														) as DKUASA_NAMA
											from DATA_KUASA_PE m	
								) as k on pe.DATA_ID=k.KEY_ID

							left join (
								select 
									distinct
										DREG_ID,
											(SELECT STUFF(
														 (SELECT '</br>' + DKUASA_NAMA 
														  FROM DATA_KUASA_PE md 
														  WHERE md.DREG_ID = m.DREG_ID
														  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
														) as DKUASA_NAMA
											from DATA_KUASA_PE m	
								) as l on pe.DREG_ID=l.DREG_ID";
							
		if(exe_sqlserver($conn,$ms_perkara2_vp1)){
			$this->nativesession->set('update_progress', 2);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara2_vp1.'));
			return;
		}

		//--vpengacara_pihak2
		$ms_perkara1_vp2 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_pengacara_pihak2') BEGIN
    					DROP VIEW dbo.v_pengacara_pihak2
						END";

		$ms_perkara1_vp2_exe = exe_sqlserver($conn,$ms_perkara1_vp2);
		if($ms_perkara1_vp2_exe){
			$this->nativesession->set('update_progress', 3);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara1_vp2.'));
			return;
		}
		
		$ms_perkara2_vp2= " CREATE VIEW v_pengacara_pihak2 as 
							SELECT 
								pe.DREG_ID as DREG_ID,pe.DATA_ID as DATA_ID,pe.DBIO_NAMA as DBIO_NAMA,
								case
									when k.KEY_ID is not null then k.DKUASA_NAMA 
									when l.DREG_ID is not null then l.DKUASA_NAMA 
									else ''
								end as DKUASA_NAMA
							FROM (
										select 
											distinct
												DREG_ID,DATA_ID,
													(SELECT STUFF(
																 (SELECT '</br>' + DBIO_NAMA 
																  FROM DATA_PE md 
																  WHERE md.DATA_ID = m.DATA_ID
																  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
																) as DBIO_NAMA
													from DATA_PE m
								) AS pe

							left join (
								select 
									distinct
										KEY_ID,
											(SELECT STUFF(
														 (SELECT '</br>' + DKUASA_NAMA 
														  FROM DATA_KUASA_PE md 
														  WHERE md.KEY_ID = m.KEY_ID
														  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
														) as DKUASA_NAMA
											from DATA_KUASA_PE m	
								) as k on pe.DATA_ID=k.KEY_ID

							left join (
								select 
									distinct
										DREG_ID,
											(SELECT STUFF(
														 (SELECT '</br>' + DKUASA_NAMA 
														  FROM DATA_KUASA_PE md 
														  WHERE md.DREG_ID = m.DREG_ID
														  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
														) as DKUASA_NAMA
											from DATA_KUASA_PE m	
								) as l on pe.DREG_ID=l.DREG_ID";
							
		if(exe_sqlserver($conn,$ms_perkara2_vp2)){
			$this->nativesession->set('update_progress', 4);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara2_vp2.'));
			return;
		}

		$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', 5);
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara1.'));
			return;
		}
		
		$ms_perkara2 = " CREATE VIEW v_perkara AS
						SELECT
							dr.DATA_ID as perkara_id,  
						9 as alur_perkara_id,  
						CONVERT(CHAR(10), dr.DREG_TGL_DAFTAR, 120)	as tanggal_pendaftaran, 
						dr.JNSPERK_ID										as jenis_perkara_id,
							jp.JNSPERK_NAMA										as jenis_perkara_nama,
							jp.JNSPERK_NAMA										as jenis_perkara_text, 
						(RTRIM(dr.NO_PERK)+'/G/'+cast(DATEPART(YEAR,dr.DREG_TGL_DAFTAR) as char(4))+'/'+
						cast((select MSET_VALUE from MS_SETTING where MSET_KEY='KODENOPERK' AND MSET_TYPE=1 AND MSET_TYPE IS NOT NULL) 
						COLLATE DATABASE_DEFAULT as varchar(50))) as nomor_perkara,

						cast((select MSET_VALUE from MS_SETTING where MSET_KEY='KODENOPERK' AND MSET_TYPE=1 AND MSET_TYPE IS NOT NULL) COLLATE DATABASE_DEFAULT as varchar(50))+
						+'_'+cast(DATEPART(YEAR,dr.DREG_TGL_DAFTAR) as char(4))
						+'_G_'+
						(case 
							when len(RTRIM(dr.NO_PERK))=1 then +'00'+RTRIM(dr.NO_PERK)
							when len(RTRIM(dr.NO_PERK))=2 then +'0'+RTRIM(dr.NO_PERK)
							when len(RTRIM(dr.NO_PERK))=3 then +RTRIM(dr.NO_PERK)
						end) as nomor_indeks,

					CONVERT(CHAR(10), dr.DREG_TGL_GUGATAN, 120)	as tanggal_surat, 
					'' as nomor_surat,
					(select a.DBIO_NAMA from (select 
													distinct
														DREG_ID,
														(SELECT STUFF(
																 (SELECT '</br>' + DBIO_NAMA 
																  FROM DATA_PE md 
																	WHERE md.DREG_ID = m.DREG_ID
																  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
														) as DBIO_NAMA
													from DATA_PE m) as a where a.DREG_ID=dr.DATA_ID) as pihak1_text,
					pp1.DKUASA_NAMA			as	pengacara_pihak1,
					(select a.DBIO_NAMA from (select 
													distinct
														DREG_ID,
														(SELECT STUFF(
																 (SELECT '</br>' + DBIO_NAMA 
																  FROM DATA_TER md 
																	WHERE md.DREG_ID = m.DREG_ID
																  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
														) as DBIO_NAMA
													from DATA_TER m) as a where a.DREG_ID=dr.DATA_ID) as pihak2_text,
					pp2.DKUASA_NAMA			as pengacara_pihak2,
					'Penggugat:</br>'+(select a.DBIO_NAMA from (select 
													distinct
														DREG_ID,
														(SELECT STUFF(
																 (SELECT '</br>' + DBIO_NAMA 
																  FROM DATA_PE md 
																	WHERE md.DREG_ID = m.DREG_ID
																  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
														) as DBIO_NAMA
													from DATA_PE m) as a where a.DREG_ID=dr.DATA_ID)+
					'</br>Tergugat:</br>'+(select a.DBIO_NAMA from (select 
													distinct
														DREG_ID,
														(SELECT STUFF(
																 (SELECT '</br>' + DBIO_NAMA 
																  FROM DATA_TER md 
																	WHERE md.DREG_ID = m.DREG_ID
																  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') 
														) as DBIO_NAMA
													from DATA_TER m) as a where a.DREG_ID=dr.DATA_ID) as para_pihak,
					'Y' as pihak_dipublikasikan,
					DREG_POSITA			as posita,
					DREG_PETITUM		AS petitum
					FROM DATA_REGISTER as dr
					LEFT JOIN MS_JENIS_PERKARA as jp on dr.JNSPERK_ID=jp.JNSPERK_ID
					LEFT JOIN (
								select 
												distinct
													DREG_ID,
														(SELECT STUFF(
																	 (SELECT '' + DKUASA_NAMA 
																	  FROM v_pengacara_pihak1 md 
																	  WHERE md.DREG_ID = m.DREG_ID
																	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, '') 
																	) as DKUASA_NAMA
														from v_pengacara_pihak1 m) as pp1 on dr.DATA_ID=pp1.DREG_ID

					LEFT JOIN (
								select 
												distinct
													DREG_ID,
														(SELECT STUFF(
																	 (SELECT '' + DKUASA_NAMA 
																	  FROM v_pengacara_pihak2 md 
																	  WHERE md.DREG_ID = m.DREG_ID
																	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, '') 
																	) as DKUASA_NAMA
														from v_pengacara_pihak2 m) as pp2 on dr.DATA_ID=pp2.DREG_ID";
							
		if(exe_sqlserver($conn,$ms_perkara2)){
			$this->nativesession->set('update_progress', 6);
		}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){

		while($row = odbc_fetch_array($ms_perkara3_exe)){
			//$this->load->helper('text');

			$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_perkara']=$row['nomor_perkara'];
			$data['nomor_indeks']=$row['nomor_indeks'];
			$data['tanggal_surat']=$row['tanggal_surat'];
			$data['nomor_surat']=$row['nomor_surat']; 
			$data['pihak1_text']=$row['pihak1_text'];
			$data['pengacara_pihak1']=$row['pengacara_pihak1'];
			$data['pihak2_text']=$row['pihak2_text'];
			$data['pengacara_pihak2']=$row['pengacara_pihak2'];
			$data['para_pihak']=$row['para_pihak'];
			$data['pihak_dipublikasikan']=$row['pihak_dipublikasikan'];
			$posita=ascii_to_entities($row['posita']); 
			$petitum=ascii_to_entities($row['petitum']);

			$pet_pos='<strong>Posita :</strong></br>'.$posita.'</br>'.'<strong>Petitum :</strong></br>'.$petitum;
			$pet_pos_nobr=str_replace(array("\pard","\par"), array("</br>","</br>"), $pet_pos);
			$pet_pos_new=preg_replace('(\'"{\\*?\\\\.+(;})|\\s?\\\\[A-Za-z0-9]+|\\s?{\\s?\\\\[A-Za-z0-9]+\\s?|\\s?}\\s?"\')','',$pet_pos_nobr, -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('','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
			$pet_pos_clean=str_replace($cari, $ganti, $pet_pos_new);
			$data['petitum']=$pet_pos_clean;

			$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());

			$my_perkara3 = $this->patching->replace_data('perkara',$data);
			$my_perkara3_1 = $this->patching->replace_data('perkara_proses',$data1);
		}
		if($my_perkara3 && $my_perkara3_1){ ;
			$this->nativesession->set('update_progress', 7); 
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara3.'));
			return;
		}
	}


//-- Buat masukkin ke table pihak ---
$my_perkara4 =" UPDATE perkara SET jenis_perkara_id=
						CASE
							WHEN jenis_perkara_id=1 THEN 318
							WHEN jenis_perkara_id=2 THEN 319
							WHEN jenis_perkara_id=3 THEN 320
							WHEN jenis_perkara_id=4 THEN 321
							WHEN jenis_perkara_id=5 THEN 322
							WHEN jenis_perkara_id=6 THEN 323
							WHEN jenis_perkara_id=7 THEN 324
							WHEN jenis_perkara_id=8 THEN 325
							WHEN jenis_perkara_id=9 THEN 326
							WHEN jenis_perkara_id=10 THEN 327
							WHEN jenis_perkara_id=11 THEN 328
							WHEN jenis_perkara_id=12 THEN 329
							WHEN jenis_perkara_id=13 THEN 335
							WHEN jenis_perkara_id=14 THEN 330
						END	
						WHERE jenis_perkara_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14)";

		if($this->patching->exe_script($my_perkara4)){
			$this->nativesession->set('update_progress', 8);
			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', 9);
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query sql.'));
			return;
		}

		//--tambah end
		//--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', 10);
			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  
							DATA_ID					as id, 
							(CASE WHEN JENIS_ID = 1 THEN 1 
								WHEN JENIS_ID = 2 THEN 3 
								WHEN JENIS_ID = 3 THEN 2 
								WHEN JENIS_ID = 4 THEN 2 
								WHEN JENIS_ID = 5 THEN 2 
								END) as jenis_pihak_id,
							DBIO_NAMA				as nama, 
							DBIO_ALAMAT				as alamat, 
							DBIO_PEKERJAAN			as pekerjaan, 
                            (CASE WHEN DBIO_KEWARGANEGARAAN = 'Indonesia' then 98 END) as warga_negara_id,   
	    					DBIO_KEWARGANEGARAAN    as warga_negara, 
							'T'						as difabel
						FROM DATA_PE";
		
		if(exe_sqlserver($conn,$ms_perkara5a)){
			$this->nativesession->set('update_progress', 11);
			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){
		
		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']=$row['nama'];
			$data5['alamat']=ascii_to_entities($row['alamat']);
			$data5['pekerjaan']=$row['pekerjaan'];
			$data5['warga_negara_id']=$row['warga_negara_id']; 
			$data5['warga_negara']=$row['warga_negara'];
			$data5['diinput_oleh']='System';
			$data5['diinput_tanggal']=date("Y-m-d H:i:s", time());

			$my_perkara5b = $this->patching->replace_data('pihak',$data5);
		}
		if($my_perkara5b){
			$this->nativesession->set('update_progress', 12);
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara5b.'));
			return;
		}
	}

		//-- Buat masukkin ke table perkara_pihak1

		$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', 13);
			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 DATA_ID ) as id,
					    DREG_ID as perkara_id,  
						ROW_NUMBER()OVER(Partition By DREG_ID Order By DATA_ID) as urutan,  
						DATA_ID as pihak_id,  
						(CASE WHEN JENIS_ID = 1 THEN 1 
						WHEN JENIS_ID = 2 THEN 3 
						WHEN JENIS_ID = 3 THEN 2 
						WHEN JENIS_ID = 4 THEN 2 
						WHEN JENIS_ID = 5 THEN 2 
						END) as jenis_pihak_id, 
						DBIO_NAMA as nama, 
						DBIO_ALAMAT as alamat  
						FROM DATA_PE as pe";
		
		if(exe_sqlserver($conn,$ms_perkara6a)){
			$this->nativesession->set('update_progress', 14);
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara6a.'));
			return;
		}


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

		$ms_perkara6b = "SELECT * from v_perkara_pihak1 where perkara_id in (".$cek_perkara_id.")";
		$ms_perkara6b_exe = exe_sqlserver($conn,$ms_perkara6b);

		$jml_data = odbc_num_rows($ms_perkara6b_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkara6b_exe)){
			
			$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']=$row['nama'];
			$data6['alamat']=ascii_to_entities($row['alamat']);
			$data6['diinput_oleh']='System';
			$data6['diinput_tanggal']=date("Y-m-d H:i:s", time());

			$my_perkara6b = $this->patching->replace_data('perkara_pihak1',$data6);
		}
		if($my_perkara6b){
			$this->nativesession->set('update_progress', 15);
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkara6b.'));
			return;
		}
	}

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

		$ms_perkara6_vp1a= " CREATE VIEW v_perkara_pengacara_vp1 AS						
							SELECT 
							ROW_NUMBER() OVER (ORDER BY dkp.DATA_ID)	AS id,
							(CASE 
								WHEN dkp.DREG_ID IS NOT NULL THEN dkp.DREG_ID			
								WHEN dkp.DREG_ID iS NULL THEN dp.DREG_ID
							END)											as perkara_id,
							ROW_NUMBER()OVER(Partition By dp.DREG_ID Order By dp.DATA_ID) as urutan,
							dkp.KEY_ID										as pihak_id,
							
							dkp.DKUASA_NAMA									as nama,
							1												as pihak_ke,
							(CASE	WHEN dp.JENIS_ID = 1 THEN 1 
									WHEN dp.JENIS_ID = 2 THEN 3 
									WHEN dp.JENIS_ID = 3 THEN 2 
									WHEN dp.JENIS_ID = 4 THEN 2 
									WHEN dp.JENIS_ID = 5 THEN 2 
							END) 											as jenis_pihak_id,
							dkp.DKUASA_TEMPAT_LAHIR							as tempat_lahir,
							CONVERT(CHAR(10), dkp.DKUASA_TGL_LAHIR, 120)	as tanggal_lahir,
							dkp.DATA_ID										as pengacara_id,
							(case
								when dkp.DKUASA_KELAMIN='True' then 'L'
								when dkp.DKUASA_KELAMIN='False' then 'P'
							end) 											as jenis_kelamin,

							dkp.DKUASA_ALAMAT								as alamat,
							dkp.DKUASA_PEKERJAAN 							as pekerjaan
								
						from DATA_KUASA_PE as dkp
						left join DATA_PE as dp on dp.DATA_ID=dkp.KEY_ID
						WHERE dp.DREG_ID IN (SELECT DATA_ID from DATA_REGISTER)
						";
		
		if(exe_sqlserver($conn,$ms_perkara6_vp1a)){
			$this->nativesession->set('update_progress', 17);
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara6_vp1a.'));
			return;
		}


		$ms_perkara6_vp1b = "SELECT * from v_perkara_pengacara_vp1 where perkara_id in (".$cek_perkara_id.")";
		$ms_perkara6_vp1b_exe = exe_sqlserver($conn,$ms_perkara6_vp1b);
		
		$jml_data = odbc_num_rows($ms_perkara6_vp1b_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkara6_vp1b_exe)){
			//$this->load->helper('text');
			$maxid=$this->patching->get_maxid_pihak()->result();
			$maxid_pihak=$maxid[0]->maxid_pihak;
		
			$id=$maxid_pihak;
			$data6_vp1['id']=$maxid_pihak;
			$data6_vp1['jenis_pihak_id']=$row['jenis_pihak_id'];
			$data6_vp1['nama']=$row['nama'];
			$data6_vp1['tempat_lahir']=$row['tempat_lahir'];
			$data6_vp1['jenis_kelamin']=$row['jenis_kelamin'];
			$data6_vp1['alamat']=ascii_to_entities($row['alamat']);
			$data6_vp1['pekerjaan']=ascii_to_entities($row['pekerjaan']);
			$data6_vp1['diinput_oleh']='System';
			$data6_vp1['diinput_tanggal']=date("Y-m-d H:i:s", time());

			$data6_vp1a['id']=$row['id'];
			$data6_vp1a['perkara_id']=$row['perkara_id'];
			$data6_vp1a['pihak_ke']=$row['pihak_ke'];
			$data6_vp1a['pihak_id']=$row['pihak_id'];
			$data6_vp1a['urutan']=$row['urutan'];
			$data6_vp1a['pengacara_id']=$maxid_pihak;
			$data6_vp1a['jenis_pihak_id']=$row['jenis_pihak_id'];
			$data6_vp1a['nama']=$row['nama'];
			$data6_vp1a['alamat']=ascii_to_entities($row['alamat']);
			$data6_vp1a['diinput_oleh']='System';
			$data6_vp1a['diinput_tanggal']=date("Y-m-d H:i:s", time());

						
			$data6_vp1b['pihak_id']=$maxid_pihak;
			$data6_vp1b['alamat_kantor']=ascii_to_entities($row['alamat']);

			$my_perkara6_vp1 = $this->patching->replace_data('pihak',$data6_vp1);
			$my_perkara6_vp1a = $this->patching->replace_data('perkara_pengacara',$data6_vp1a);
//			$my_perkara6_vp1b = $this->patching->replace_data('pihak_pengacara',$data6_vp1b);
		}		
		
		if($my_perkara6_vp1 && $my_perkara6_vp1a /*&& $my_perkara6_vp1b*/){
			$this->nativesession->set('update_progress', 18);
			session_write_close();
			session_start();
		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query $my_perkara6_vp1,my_perkara6_vp1a,my_perkara6_vp1b.'));
			return;
		}
		}


//-- Buat masukkin ke table pihak dan perkara_pihak 2

		$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', 19);
			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', 20);
			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 DATA_ID ) as id,
							DATA_ID 			as id_pihak_lama,
							DREG_ID				as perkara_id, 
							ROW_NUMBER()OVER(Partition By DREG_ID Order By DATA_ID) as urutan,
							
							(CASE WHEN JENIS_ID = 1 THEN 1 
							WHEN JENIS_ID = 2 THEN 3 
							WHEN JENIS_ID = 3 THEN 2 
							WHEN JENIS_ID = 4 THEN 2 
							WHEN JENIS_ID = 5 THEN 2 
							END) as jenis_pihak_id, 
							DBIO_NAMA as nama, 
							DBIO_ALAMAT as alamat,   
							DBIO_PEKERJAAN as pekerjaan,  
                            'T' as difabel
							FROM DATA_TER";
		
		if(exe_sqlserver($conn,$ms_perkara8a)){
			$this->nativesession->set('update_progress', 21);
			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 where jenis_pihak_id is not null and perkara_id in (".$cek_perkara_id.")";
		$ms_perkara8b_exe = exe_sqlserver($conn,$ms_perkara8b);
		
		$jml_data = odbc_num_rows($ms_perkara8b_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkara8b_exe)){
			//$this->load->helper('text');
			$maxid=$this->patching->get_maxid_pihak()->result();
			$maxid_pihak=$maxid[0]->maxid_pihak;
				
	
			$id=$maxid_pihak;
			$data7['id']=$maxid_pihak;
			$data7['jenis_pihak_id']=$row['jenis_pihak_id'];
			$data7['nama']=ascii_to_entities($row['nama']);
			$data7['alamat']=ascii_to_entities($row['alamat']);
			$data7['pekerjaan']=ascii_to_entities($row['pekerjaan']);

			$data8['id']=$row['id'];
			$data8['perkara_id']=$row['perkara_id'];
			$data8['urutan']=$row['urutan'];
			$data8['pihak_id']=$maxid_pihak;
			$data8['jenis_pihak_id']=$row['jenis_pihak_id'];
			$data8['nama']=ascii_to_entities($row['nama']);
			$data8['alamat']=ascii_to_entities($row['alamat']);
			$data8['diinput_oleh']='System';
			$data8['diinput_tanggal']=date("Y-m-d H:i:s", time());

			$data8c['pihak2_id_lama']=$row['id_pihak_lama'];
			$data8c['pihak2_id_baru']=$maxid_pihak;

		$my_perkara7b = $this->patching->replace_data('pihak',$data7);
		$my_perkara7ba = $this->patching->replace_data('perkara_pihak2',$data8);
		$my_perkara8c = $this->patching->replace_data('pihak2_idlama_idbaru',$data8c);
			//
		}		
		
		if($my_perkara7b && $my_perkara7ba && $my_perkara8c){
			$this->nativesession->set('update_progress', 22); 
			session_write_close();
			session_start();

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


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

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

		$ms_perkara7_vp2= " CREATE VIEW v_perkara_pengacara_vp2 AS						
							SELECT
								ROW_NUMBER() OVER (ORDER BY dkt.DATA_ID)	AS id,
								dt.DREG_ID			AS perkara_id,
								2					as pihak_ke,
								dkt.KEY_ID			as idpihak2lama,
								ROW_NUMBER()OVER(Partition By dt.DREG_ID Order By dt.DATA_ID) as urutan,
								dkt.DATA_ID			as pengacara_id,
								(CASE	WHEN dt.JENIS_ID = 1 THEN 1 
										WHEN dt.JENIS_ID = 2 THEN 3 
										WHEN dt.JENIS_ID = 3 THEN 2 
										WHEN dt.JENIS_ID = 4 THEN 2 
										WHEN dt.JENIS_ID = 5 THEN 2 
								END)				as jenis_pihak_id,
								dkt.DKUASA_NAMA		as nama,
								dkt.DKUASA_ALAMAT	as alamat,
								dkt.DKUASA_TEMPAT_LAHIR as tempat_lahir,
								dkt.DKUASA_TGL_LAHIR as tanggal_lahir,
								dkt.DKUASA_PEKERJAAN as pekerjaan,
								(case
									when dkt.DKUASA_KELAMIN='False' then 'L'
									when dkt.DKUASA_KELAMIN='True' then 'P'
								end) as jenis_kelamin,
								'Y'					as aktif			
							from DATA_KUASA_TER as dkt
							left join DATA_TER as dt on dt.DATA_ID=dkt.KEY_ID	
							WHERE dt.DREG_ID IN (SELECT DATA_ID from DATA_REGISTER)						
							";
		
		if(exe_sqlserver($conn,$ms_perkara7_vp2)){
			$this->nativesession->set('update_progress', 22); 
			session_write_close();
			session_start();

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

		$ms_perkara7_vp2b = "SELECT * from v_perkara_pengacara_vp2 where perkara_id in (".$cek_perkara_id.")";
		$ms_perkara7_vp2b_exe = exe_sqlserver($conn,$ms_perkara7_vp2b);
		
		$jml_data = odbc_num_rows($ms_perkara7_vp2b_exe);
		if($jml_data>0){
		
			$maxid=$this->patching->get_maxid_pihak()->result();
			$maxid_pihak=$maxid[0]->maxid_pihak;

			$maxidpengacara=$this->patching->get_maxid_pengacarapihak2()->result();
			$maxid_pengacara=$maxidpengacara[0]->maxid_pengacara;

		while($row = odbc_fetch_array($ms_perkara7_vp2b_exe)){
			$maxid_pihak++; 
			$maxid_pengacara++;  
			//$this->load->helper('text');
			$idpihakbaru=$this->patching->get_id_pihakbaru($row['idpihak2lama']);
			if($idpihakbaru->num_rows>0){
				$idpihakbaru = $idpihakbaru->result();
				$pihak_id_baru=$idpihakbaru[0]->pihak2_id_baru;	
				$data7_vp2a['id']=$maxid_pengacara;
				$data7_vp2a['perkara_id']=$row['perkara_id'];
				$data7_vp2a['pihak_ke']=$row['pihak_ke'];
				$data7_vp2a['pihak_id']=$pihak_id_baru;
				$data7_vp2a['urutan']=$row['urutan'];
				$data7_vp2a['pengacara_id']=$maxid_pihak;
				$data7_vp2a['jenis_pihak_id']=$row['jenis_pihak_id'];
				$data7_vp2a['nama']=ascii_to_entities($row['nama']);
				$data7_vp2a['alamat']=ascii_to_entities($row['alamat']);
				$data7_vp2a['diinput_oleh']='System';
				$data7_vp2a['diinput_tanggal']=date("Y-m-d H:i:s", time());		
            }
			$id=$maxid_pihak;
			$data7_vp2['id']=$maxid_pihak;
			$data7_vp2['jenis_pihak_id']=$row['jenis_pihak_id'];
			$data7_vp2['nama']=ascii_to_entities($row['nama']);
			$data7_vp2['tempat_lahir']=$row['tempat_lahir'];
			$data7_vp2['jenis_kelamin']=$row['jenis_kelamin'];
			$data7_vp2['alamat']=ascii_to_entities($row['alamat']);
			$data7_vp2['pekerjaan']=ascii_to_entities($row['pekerjaan']);
			$data7_vp2['diinput_oleh']='System';
			$data7_vp2['diinput_tanggal']=date("Y-m-d H:i:s", time());		

			/*
			*/			
			//$data7_vp2b['pihak_id']=$maxid_pihak;
			//$data7_vp2b['alamat_kantor']=ascii_to_entities($row['alamat']);

			$my_perkara7_vp2 = $this->patching->replace_data('pihak',$data7_vp2);
			$my_perkara7_vp2a = $this->patching->replace_data('perkara_pengacara',$data7_vp2a);
//			$my_perkara7_vp2b = $this->patching->replace_data('pihak_pengacara',$data7_vp2b);
		}		
		
		if($my_perkara7_vp2 && $my_perkara7_vp2a /**&& $my_perkara7_vp2b**/){
			$this->nativesession->set('update_progress', 22); 
			session_write_close();
			session_start();

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

		//--tambah end

//-- Buat Bikin Table Saksi --

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

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

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

$ms_perkarasaksi1_exe = exe_sqlserver($conn,$ms_perkarasaksi1);
		if($ms_perkarasaksi1_exe){
			$this->nativesession->set('update_progress', 22);
			session_write_close();
			session_start();

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

$ms_perkarasaksi2= "CREATE VIEW v_perkara_pihak5 AS
				    SELECT 
					ROW_NUMBER() OVER( ORDER BY DATA_ID ) as id, 
					1 as jenis_pihak_id, 
					DREG_ID as perkara_id,  
					ROW_NUMBER()OVER(Partition By DREG_ID Order By DATA_ID) as urutan, 
					DATA_ID as pihak_id,  
					DSAK_NAMA as nama,  
					DSAK_ALAMAT as alamat 
					FROM DATA_SAKSI WHERE DSAK_NAMA != ''AND DSAK_ALAMAT != ''"; 

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

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

    $ms_perkarasaksi3 = "SELECT * FROM v_perkara_pihak5 where perkara_id in (".$cek_perkara_id.")";
	$ms_perkarasaksi3_exe = exe_sqlserver($conn,$ms_perkarasaksi3);

	$jml_data = odbc_num_rows($ms_perkarasaksi3_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkarasaksi3_exe)){
			//$this->load->helper('text');
			$maxid=$this->patching->get_maxid_pihak()->result(); 
			$maxid_pihak=$maxid[0]->maxid_pihak;

			$id=$maxid_pihak;
			$data21['id']=$maxid_pihak;
			$data21['jenis_pihak_id']=$row['jenis_pihak_id'];
			$data21['nama']=$row['nama'];
			$data21['alamat']=ascii_to_entities($row['alamat']);
			$data21['diinput_oleh']='System';
			$data21['diinput_tanggal']=date("Y-m-d H:i:s", time());			

            $data21a['id']=$row['id'];
			$data21a['perkara_id']=$row['perkara_id']; 
			$data21a['urutan']=$row['urutan']; 
			$data21a['pihak_id']=$maxid_pihak;
			$data21a['nama']=$row['nama']; 
			$data21a['alamat']=ascii_to_entities($row['alamat']); 			
			$data21a['diinput_oleh']='System';
			$data21a['diinput_tanggal']=date("Y-m-d H:i:s", time());


$my_perkarasaksi3 = $this->patching->replace_data('pihak',$data21);
$my_perkarasaksi3a = $this->patching->replace_data('perkara_pihak5',$data21a);

		}
		if($my_perkarasaksi3 && $my_perkarasaksi3a){
			$this->nativesession->set('update_progress', 22); 
			session_write_close();
			session_start();

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

//--- Buat masukkin ke table perkara_penetapan 

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

$ms_perkara9_exe = exe_sqlserver($conn,$ms_perkara9); 

if($ms_perkara9_exe){
			$this->nativesession->set('update_progress', 23);
			session_write_close();
			session_start();

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

$ms_perkara9b = "CREATE VIEW v_perkara_penetapan AS
				SELECT 
				dm.DREG_ID				as perkara_id, 
				(SELECT convert(varchar(10), dm.DMAJ_TGL_PMH, 120)) as penetapan_majelis_hakim, 
				(RTRIM(dr.NO_PERK)+'/PEN-MH/'+cast(DATEPART(YEAR,dm.DMAJ_TGL_PMH) as char(4))+
				CAST((select MSET_VALUE from MS_SETTING where MSET_KEY='KODENOPERK' AND MSET_TYPE=1 AND MSET_TYPE IS NOT NULL) 
				COLLATE DATABASE_DEFAULT as varchar(50))) as nomor_sk_penetapan_majelis_hakim,
				CAST(dm.DMAJ_KETUA_ID as varchar(4)) + ',' + CAST(dm.DMAJ_ANGGOTA1_ID as varchar (4)) + ',' + CAST(dm.DMAJ_ANGGOTA2_ID as varchar (4)) as majelis_hakim_id, 
				(SELECT mh.MHKM_NAMA where dm.DMAJ_KETUA_ID = mh.MHKM_ID)+'</br>'+
				(SELECT mh1.MHKM_NAMA where dm.DMAJ_ANGGOTA1_ID = mh1.MHKM_ID )+'</br>'+
				(SELECT mh2.MHKM_NAMA where dm.DMAJ_ANGGOTA2_ID = mh2.MHKM_ID) as majelis_hakim_nama, 
				(CASE 
			when dm.DMAJ_ANGGOTA1_ID is null and dm.DMAJ_ANGGOTA2_ID is null then
					'Hakim Tunggal : '+(SELECT mh.MHKM_NAMA where dm.DMAJ_KETUA_ID = mh.MHKM_ID)
			else
					'Hakim Ketua: '+(SELECT mh.MHKM_NAMA where dm.DMAJ_KETUA_ID = mh.MHKM_ID)+'</br>'+
					'Hakim Anggota 1: '+(SELECT mh1.MHKM_NAMA where dm.DMAJ_ANGGOTA1_ID = mh1.MHKM_ID )+'</br>'+
					'Hakim Anggota 2: '+(select mh2.MHKM_NAMA where dm.DMAJ_ANGGOTA2_ID = mh2.MHKM_ID)
				end) as majelis_hakim_text,
				(SELECT convert(varchar(10), dm.DMAJ_TGL_PMH, 120)) as penetapan_panitera_pengganti,
				(RTRIM(dr.NO_PERK)+'/G/'+cast(DATEPART(YEAR,dm.DMAJ_TGL_PMH) as char(4))+
				CAST((select MSET_VALUE from MS_SETTING where MSET_KEY='KODENOPERK' AND MSET_TYPE=1 AND MSET_TYPE IS NOT NULL) 
				COLLATE DATABASE_DEFAULT as varchar(50))) as nomor_sk_penetapan_panitera_pengganti,dm.DMAJ_PP_ID		AS panitera_pengganti_id,
				'Panitera Pengganti :'+(SELECT mpp.MPP_NAMA where dm.DMAJ_PP_ID=mpp.MPP_ID) as panitera_pengganti_text,
				(SELECT convert(varchar(10), dm.DMAJ_TGL_PMH, 120)) as penetapan_jurusita,
				(RTRIM(dr.NO_PERK)+'/G/'+cast(DATEPART(YEAR,dm.DMAJ_TGL_PMH) as char(4))+
				CAST((select MSET_VALUE from MS_SETTING where MSET_KEY='KODENOPERK' AND MSET_TYPE=1 AND MSET_TYPE IS NOT NULL) 
				COLLATE DATABASE_DEFAULT as varchar(50))) as nomor_sk_penetapan_jurusita,
				dm.MJS_ID		as jurusita_id,
				'Jurusita :'+(SELECT mj.MJS_NAMA where dm.MJS_ID=mj.MJS_ID) as jurusita_text,
				(CONVERT(varchar(10), dr.DREG_TGL_PHS,120))		as penetapan_hari_sidang,
				(CONVERT(varchar(10), (select MIN(DSID_TANGGAL) FROM DATA_SIDANG AS ds where ds.DREG_ID=dm.DREG_ID),120)) as sidang_pertama 
				
				FROM DATA_MAJELIS as dm  
				LEFT JOIN MS_HAKIM as mh on dm.DMAJ_KETUA_ID = mh.MHKM_ID 
				LEFT JOIN MS_HAKIM as mh1 on dm.DMAJ_ANGGOTA1_ID = mh1.MHKM_ID 
				LEFT JOIN MS_HAKIM as mh2 on dm.DMAJ_ANGGOTA2_ID = mh2.MHKM_ID 
				LEFT JOIN MS_PP as mpp on mpp.MPP_ID=dm.DMAJ_PP_ID 
				LEFT JOIN MS_JS as mj on mj.MJS_ID=dm.MJS_ID 
				LEFT JOIN DATA_REGISTER as dr on dr.DATA_ID=dm.DREG_ID 
				WHERE dm.DMAJ_TGL_PMH is not null";

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

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

$ms_perkara9c = "SELECT * FROM v_perkara_penetapan where penetapan_majelis_hakim is not null and perkara_id in (".$cek_perkara_id.")";
$ms_perkara9c_exe = exe_sqlserver($conn,$ms_perkara9c);

$jml_data = odbc_num_rows($ms_perkara9c_exe);
		if($jml_data>0){
		
while($row = odbc_fetch_array($ms_perkara9c_exe)){
			//$this->load->helper('text');

			$data9['perkara_id']=$row['perkara_id'];
			$data9['penetapan_majelis_hakim']=$row['penetapan_majelis_hakim'];
			$data9['jenis_acara']=2; 
			$data9['majelis_hakim_id']=$row['majelis_hakim_id']; 
			$data9['majelis_hakim_nama']=$row['majelis_hakim_nama']; 
			$data9['majelis_hakim_text']=$row['majelis_hakim_text']; 

			$data9['penetapan_panitera_pengganti']=$row['penetapan_panitera_pengganti']; 
			$data9['panitera_pengganti_id']=$row['panitera_pengganti_id']; 
			$data9['panitera_pengganti_text']=$row['panitera_pengganti_text']; 

			$data9['penetapan_jurusita']=$row['penetapan_jurusita']; 
			$data9['jurusita_id']=$row['jurusita_id']; 
			$data9['jurusita_text']=$row['jurusita_text']; 

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


			$my_perkara9c=$this->patching->replace_data('perkara_penetapan',$data9);

			if(!empty($row['penetapan_majelis_hakim'])){
			$update_perkara ="UPDATE perkara SET 
							  tahapan_terakhir_id=12,
							  tahapan_terakhir_text='Penetapan',
							  proses_terakhir_id=20,
							  proses_terakhir_text='Penetapan Majelis Hakim/Hakim'
							  WHERE perkara_id=".$row['perkara_id']."";
			
			if($this->patching->exe_script($update_perkara)){
				$data9_a['perkara_id']=$row['perkara_id'];
				$data9_a['tahapan_id']=12;
				$data9_a['tahapan_nama']='Penetapan';
				$data9_a['proses_id']=20;
				$data9_a['proses_nama']='Penetapan Majelis Hakim/Hakim';
				$data9_a['tanggal']=$row['penetapan_majelis_hakim'];
				$data9_a['diinput_oleh']='System';
				$data9_a['diinput_tanggal']=date("Y-m-d H:i:s", time());
				$this->patching->replace_data('perkara_proses',$data9_a);
			}
			}

			if(!empty($row['penetapan_panitera_pengganti'])){
			$update_perkara1 ="UPDATE perkara SET 
							  tahapan_terakhir_id=12,
							  tahapan_terakhir_text='Penetapan',
							  proses_terakhir_id=30,
							  proses_terakhir_text='Penunjukan Panitera Pengganti'
							  WHERE perkara_id=".$row['perkara_id']."";
			
			if($this->patching->exe_script($update_perkara1)){
				$data9_b['perkara_id']=$row['perkara_id'];
				$data9_b['tahapan_id']=12;
				$data9_b['tahapan_nama']='Penetapan';
				$data9_b['proses_id']=30;
				$data9_b['proses_nama']='Penunjukan Panitera Pengganti';
				$data9_b['tanggal']=$row['penetapan_panitera_pengganti'];
				$data9_b['diinput_oleh']='System';
				$data9_b['diinput_tanggal']=date("Y-m-d H:i:s", time());
				$this->patching->replace_data('perkara_proses',$data9_b);
			}
			}

			if(!empty($row['penetapan_jurusita'])){
			$update_perkara1j ="UPDATE perkara SET 
							  tahapan_terakhir_id=12,
							  tahapan_terakhir_text='Penetapan',
							  proses_terakhir_id=40,
							  proses_terakhir_text='Penunjukan Jurusita'
							  WHERE perkara_id=".$row['perkara_id']."";
			
			if($this->patching->exe_script($update_perkara1j)){
				$data9_bj['perkara_id']=$row['perkara_id'];
				$data9_bj['tahapan_id']=12;
				$data9_bj['tahapan_nama']='Penetapan';
				$data9_bj['proses_id']=40;
				$data9_bj['proses_nama']='Penunjukan Jurusita';
				$data9_bj['tanggal']=$row['penetapan_jurusita'];
				$data9_bj['diinput_oleh']='System';
				$data9_bj['diinput_tanggal']=date("Y-m-d H:i:s", time());
				$this->patching->replace_data('perkara_proses',$data9_bj);
			}
			}

			if(!empty($row['penetapan_hari_sidang'])){
			$update_perkara2 ="UPDATE perkara SET 
							  tahapan_terakhir_id=12,
							  tahapan_terakhir_text='Penetapan',
							  proses_terakhir_id=80,
							  proses_terakhir_text='Penetapan Hari Sidang Pertama'
							  WHERE perkara_id=".$row['perkara_id']."";
			
			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());				
				$this->patching->replace_data('perkara_proses',$data11_c);
			}
			}
			//$my_perkara9c=TRUE;			

}
		if($my_perkara9c){
			$this->nativesession->set('update_progress', 25);
			session_write_close();
			session_start();

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

//-- Buat Masukkin ke Table hakim_pn --

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

$ms_perkara10_exe = exe_sqlserver($conn,$ms_perkara10); 

if($ms_perkara10_exe){
			$this->nativesession->set('update_progress', 26);
			session_write_close();
			session_start();

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

$ms_perkara11 = "CREATE VIEW v_hakim_pn AS
               SELECT 
			   MHKM_ID as id, 
			   1 						as status_hakim_id, 
			   (SELECT REPLACE(MHKM_NIP, ' ', '')) as nip,
			   MHKM_NAMA as nama,				 
			   'Y'					as aktif, 
			   (convert(varchar(10), MHKM_TGL_AKTIF, 120)) as mulai
			   from MS_HAKIM
			   WHERE MHKM_ID IS NOT NULL";

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

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

		$ms_perkara12 = "SELECT * FROM v_hakim_pn where id is not null";
		$ms_perkara12_exe = exe_sqlserver($conn,$ms_perkara12);

       $jml_data = odbc_num_rows($ms_perkara12_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkara12_exe)){
			//$this->load->helper('text');
			
			$data10['id']=$row['id'];
			$data10['status_hakim_id']=$row['status_hakim_id'];
			$data10['nip']=$row['nip'];
			$data10['nama']=$row['nama'];
			$data10['nama_gelar']=$row['nama'];
			$data10['aktif']=$row['aktif'];
			$data10['mulai']=$row['mulai'];
			$data10['diinput_oleh']='System';
			$data10['diinput_tanggal']=date("Y-m-d H:i:s", time());

		
			$my_perkara12 = $this->patching->replace_data('hakim_pn',$data10);
		}
		if($my_perkara12){
			$this->nativesession->set('update_progress', 28);
			session_write_close();
			session_start();

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

//-- Buat Masukkin ke Table 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";

		$ms_perkara13_exe = exe_sqlserver($conn,$ms_perkara13);
		if($ms_perkara13_exe){
			$this->nativesession->set('update_progress', 29);
			session_write_close();
			session_start();

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

		$ms_perkara14 = "CREATE VIEW v_perkara_hakim_pn AS 
                        SELECT 
                        ROW_NUMBER() OVER (ORDER BY dm.DATA_ID) AS id,
						dm.DREG_ID				AS perkara_id,
						10						AS tahapan_id,
						(SELECT convert(varchar(10), dm.DMAJ_TGL_PMH, 120)) as tanggal_penetapan,
						(RTRIM(dr.NO_PERK)+'/PEN-MH/'+cast(DATEPART(YEAR,dm.DMAJ_TGL_PMH) as char(4))+
						cast((select MSET_VALUE from MS_SETTING where MSET_KEY='KODENOPERK' AND MSET_TYPE=1 AND MSET_TYPE IS NOT NULL) 
						COLLATE DATABASE_DEFAULT as varchar(50))) as nomor_sk_penetapan,

					    (ROW_NUMBER()OVER(Partition By dm.DREG_ID Order By dm.DATA_ID)) as urutan, 
						JABATAN_HAKIM_ID	as jabatan_hakim_id,
						JABATAN_HAKIM_NAMA	as jabatan_hakim_nama,
						HAKIM_ID			as hakim_id,
						(select replace((select MHKM_NIP from MS_HAKIM where MHKM_ID=HAKIM_ID), ' ', '')) AS hakim_nip, 

						(select MHKM_NAMA from MS_HAKIM where MHKM_ID=HAKIM_ID) AS hakim_nama,
						'Y'					as aktif
						from DATA_MAJELIS as dm
						CROSS APPLY
						(
  						select '1','1',
						case 
						when dm.DMAJ_ANGGOTA1_ID is null and dm.DMAJ_ANGGOTA2_ID is null then 
						'Hakim Tunggal'
						else
						'Hakim Ketua'
						end
						, dm.DMAJ_KETUA_ID union all
  						select '2','2','Hakim Anggota', dm.DMAJ_ANGGOTA1_ID union all
  						select '3','2','Hakim Anggota', dm.DMAJ_ANGGOTA2_ID 
						) c (URUTAN, JABATAN_HAKIM_ID,JABATAN_HAKIM_NAMA,HAKIM_ID)
						LEFT JOIN DATA_REGISTER as dr on dr.DATA_ID=dm.DREG_ID
						WHERE HAKIM_ID IS NOT NULL";        

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

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

		$ms_perkara15 = "SELECT * FROM v_perkara_hakim_pn where hakim_id <> 0 and hakim_id in (select id from v_hakim_pn) and perkara_id in (".$cek_perkara_id.")";
		$ms_perkara15_exe = exe_sqlserver($conn,$ms_perkara15);

		$jml_data = odbc_num_rows($ms_perkara15_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkara15_exe)){
			//$this->load->helper('text');
			
			$data11['id']=$row['id'];
			$data11['perkara_id']=$row['perkara_id']; 
			$data11['tahapan_id']=$row['tahapan_id']; 
			$data11['tanggal_penetapan']=$row['tanggal_penetapan']; 
			$data11['nomor_sk_penetapan']=$row['nomor_sk_penetapan']; 
			$data11['urutan']=$row['urutan']; 
			$data11['jabatan_hakim_id']=$row['jabatan_hakim_id']; 
			$data11['jabatan_hakim_nama']=$row['jabatan_hakim_nama']; 
			$data11['hakim_id']=$row['hakim_id']; 
			$data11['hakim_nip']=$row['hakim_nip']; 
			$data11['hakim_nama']=$row['hakim_nama']; 
			$data11['aktif']=$row['aktif'];
			$data11['diinput_oleh']='System';
			$data11['diinput_tanggal']=date("Y-m-d H:i:s", time());

		
		$my_perkara15 = $this->patching->replace_data('perkara_hakim_pn',$data11);
		}
		if($my_perkara15){
			$this->nativesession->set('update_progress', 31); 
			session_write_close();
			session_start();

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

//--Buat Masukkin Ke Table panitera_pn 

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

		$ms_perkara16_exe = exe_sqlserver($conn,$ms_perkara16);

		if($ms_perkara16_exe){
			$this->nativesession->set('update_progress', 32); 
			session_write_close();
			session_start();

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

		$ms_perkara17 = "CREATE VIEW v_panitera_pn AS
						SELECT 
						MPP_ID as id, 
						MPP_KODE as kode, 
						(SELECT LEFT ((SELECT REPLACE(MPP_NIP, ' ', '')), 20)) as nip, 
						MPP_NAMA as nama, 
						'Y' as aktif  
						FROM MS_PP";

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

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

		$ms_perkara18 = "SELECT * FROM v_panitera_pn where id is not null";
		$ms_perkara18_exe = exe_sqlserver($conn,$ms_perkara18);
	
        $jml_data = odbc_num_rows($ms_perkara18_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkara18_exe)){
			//$this->load->helper('text');
			
			$data12['id']=$row['id'];
			$data12['kode']=$row['kode']; 
			$data12['nip']=$row['nip']; 
			$data12['nama']=$row['nama']; 
			$data12['nama_gelar']=$row['nama']; 
			$data12['aktif']=$row['aktif'];
			$data12['diinput_oleh']='System';
			$data12['diinput_tanggal']=date("Y-m-d H:i:s", time());
		
			$my_perkara18 = $this->patching->replace_data('panitera_pn',$data12);
		}
        if($my_perkara18){
			$this->nativesession->set('update_progress', 34); 
			session_write_close();
			session_start();

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

//--- Buat Masukkin ke Table perkara_panitera_pn 

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

        $ms_perkara19_exe = exe_sqlserver($conn,$ms_perkara19);

        if($ms_perkara19_exe){
			$this->nativesession->set('update_progress', 35); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara19.'));
			return;
		}
 
	 $ms_perkara20 = "CREATE VIEW v_perkara_panitera_pn AS
					  SELECT 
                	  ROW_NUMBER() OVER (ORDER BY dm.DATA_ID) AS id,
					  dm.DREG_ID				AS perkara_id,
					  10						AS tahapan_id,
					  (SELECT convert(varchar(10), dm.DMAJ_TGL_PMH, 120)) as tanggal_penetapan,
					  (RTRIM(dr.NO_PERK)+'/G/'+cast(DATEPART(YEAR,dm.DMAJ_TGL_PMH) as char(4))+
		              cast((select MSET_VALUE from MS_SETTING where MSET_KEY='KODENOPERK' AND MSET_TYPE=1 AND MSET_TYPE IS NOT NULL) 
		              COLLATE DATABASE_DEFAULT as varchar(50))) as nomor_sk_penetapan,
                      ROW_NUMBER()OVER(Partition By dm.DREG_ID Order By dm.DATA_ID) as urutan,
					  dm.DMAJ_PP_ID		as panitera_id,
					  (select MPP_NIP from MS_PP where MPP_ID=dm.DMAJ_PP_ID) AS panitera_nip,
					  (select MPP_NAMA from MS_PP where MPP_ID=dm.DMAJ_PP_ID) AS panitera_nama,
					  'Y'					as aktif
					  from DATA_MAJELIS as dm
					  LEFT JOIN DATA_REGISTER as dr on dr.DATA_ID=dm.DREG_ID
					  WHERE dm.DMAJ_PP_ID IS NOT NULL"; 

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

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

	$ms_perkara21 = "SELECT * FROM v_perkara_panitera_pn where panitera_id <> 0 and panitera_id in (select id from v_panitera_pn) and perkara_id in (".$cek_perkara_id.")";
	$ms_perkara21_exe = exe_sqlserver($conn,$ms_perkara21);

	$jml_data = odbc_num_rows($ms_perkara21_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkara21_exe)){
			//$this->load->helper('text');
			
			$data13['id']=$row['id']; 
			$data13['perkara_id']=$row['perkara_id']; 
			$data13['tahapan_id']=$row['tahapan_id']; 
			$data13['tanggal_penetapan']=$row['tanggal_penetapan']; 
			$data13['nomor_sk_penetapan']=$row['nomor_sk_penetapan']; 
			$data13['urutan']=$row['urutan']; 
			$data13['panitera_id']=$row['panitera_id']; 
			$data13['panitera_nip']=$row['panitera_nip']; 
			$data13['panitera_nama']=$row['panitera_nama']; 
			$data13['aktif']=$row['aktif'];
			$data13['diinput_oleh']='System';
			$data13['diinput_tanggal']=date("Y-m-d H:i:s", time());

			$my_perkara21 = $this->patching->replace_data('perkara_panitera_pn',$data13);
		}

		if($my_perkara21){
			$this->nativesession->set('update_progress', 37); 
			session_write_close();
			session_start();

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

//-- Buat Masukkin Table Ke perkara_dismissal 

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

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

		$ms_perkara23= "CREATE VIEW v_perkara_dismissal AS
						SELECT 
						DATA_ID as id,  
						DREG_ID as perkara_id, 
						CONVERT(VARCHAR(10), DDIS_TGL_PENETAPAN, 120) as tanggal_penetapan,  
						DDIS_AMAR_PENETAPAN as amar,  
						(CASE WHEN DDIS_TGL_PENETAPAN_LOLOS IS NOT NULL THEN 1 ELSE 2 END) as hasil, 
						CONVERT(VARCHAR(10), DDIS_TGL_PANGGILAN, 120) as pemberitahuan_penggugat 
						FROM DATA_DISMISSAL"; 

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

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara23.'));
			return;
		}
		
		$ms_perkara24 = "SELECT * FROM v_perkara_dismissal where perkara_id in (".$cek_perkara_id.")";
		$ms_perkara24_exe = exe_sqlserver($conn,$ms_perkara24);

		$jml_data = odbc_num_rows($ms_perkara24_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkara24_exe)){
			//$this->load->helper('text');
			
			//$data14['id']=$row['id']; 
			$data14['perkara_id']=$row['perkara_id']; 
			$data14['tanggal_penetapan']=$row['tanggal_penetapan']; 
			$amar_dis=$row['amar']; 

			$amar_dis_nobr=str_replace(array("\pard","\par"), array("</br>","</br>"), $amar_dis);
			$amar_dis_new=preg_replace('(\'"{\\*?\\\\.+(;})|\\s?\\\\[A-Za-z0-9]+|\\s?{\\s?\\\\[A-Za-z0-9]+\\s?|\\s?}\\s?"\')','',$amar_dis_nobr, -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('','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
			$amar_dis_clean=str_replace($cari, $ganti, $amar_dis_new);
			$data14['amar']=$amar_dis_clean; 

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

			$my_perkara24 = $this->patching->replace_data('perkara_dismissal',$data14);
		}
		if($my_perkara24){
			$this->nativesession->set('update_progress', 40); 
			session_write_close();
			session_start();

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

	$update_perkara_dismissal ="UPDATE perkara_dismissal SET hasil=1 
							    WHERE perkara_id in (select perkara_id from perkara_penetapan where penetapan_hari_sidang is not null)";			
	$update_dismissal1= $this->patching->exe_script($update_perkara_dismissal);

	if($update_dismissal1){
		$this->nativesession->set('update_progress', 40); 
			session_write_close();
			session_start();

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

	$update_perkara_dismissal_h ="REPLACE INTO perkara_dismissal (perkara_id,hasil,diinput_oleh,diinput_tanggal) SELECT perkara_id,1 AS hasil,'system' AS diinput_oleh,CURRENT_TIMESTAMP() AS diinput_tanggal FROM perkara_penetapan WHERE perkara_id NOT IN (SELECT perkara_id FROM perkara_dismissal);";			
	$update_dismissal1_h= $this->patching->exe_script($update_perkara_dismissal_h);

	if($update_dismissal1_h){
		$this->nativesession->set('update_progress', 40); 
			session_write_close();
			session_start();

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


//--- Buat Masukkin ke Table perkara_verzet 

$ms_perkara25 = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_verzet') BEGIN
    					DROP VIEW dbo.v_perkara_verzet
						END";
$ms_perkara25_exe = exe_sqlserver($conn,$ms_perkara25);
		if($ms_perkara25_exe){
			$this->nativesession->set('update_progress', 41); 
			session_write_close();
			session_start();

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

		$ms_perkara26= "CREATE VIEW v_perkara_verzet AS
						SELECT 
							dps.DREG_ID					as perkara_id,
							(SELECT convert(varchar(10), dps.DPER_TGL_TERIMA, 120)) as tanggal_pendaftaran_verzet,
							(SELECT convert(varchar(10), dps.DPER_TGL_SURAT, 120)) as tanggal_surat,
							(SELECT convert(varchar(10), dmp.DMAJ_TGL_PMH, 120)) as penetapan_majelis_hakim,
							 CAST(dmp.DMAJ_KETUA_ID as varchar(4)) + ',' + 
							 CAST(dmp.DMAJ_ANGGOTA1_ID as varchar (4)) + ',' + 
							 CAST(dmp.DMAJ_ANGGOTA2_ID as varchar (4)) as majelis_hakim_id,
							(SELECT mh.MHKM_NAMA where dmp.DMAJ_KETUA_ID = mh.MHKM_ID)+'</br>'+
							(SELECT mh1.MHKM_NAMA where dmp.DMAJ_ANGGOTA1_ID = mh1.MHKM_ID )+'</br>'+
							(select mh2.MHKM_NAMA where dmp.DMAJ_ANGGOTA2_ID = mh2.MHKM_ID) as majelis_hakim_nama,
							(case 
									when dmp.DMAJ_ANGGOTA1_ID is null and dmp.DMAJ_ANGGOTA2_ID is null then
											'Hakim Tunggal : '+(SELECT mh.MHKM_NAMA where dmp.DMAJ_KETUA_ID = mh.MHKM_ID)
									else
											'Hakim Ketua: '+(SELECT mh.MHKM_NAMA where dmp.DMAJ_KETUA_ID = mh.MHKM_ID)+'</br>'+
											'Hakim Anggota 1: '+(SELECT mh1.MHKM_NAMA where dmp.DMAJ_ANGGOTA1_ID = mh1.MHKM_ID )+'</br>'+
											'Hakim Anggota 2: '+(select mh2.MHKM_NAMA where dmp.DMAJ_ANGGOTA2_ID = mh2.MHKM_ID)
							end) as majelis_hakim_text,
							(SELECT convert(varchar(10), dmp.DMAJ_TGL_PMH, 120)) as penetapan_panitera_pengganti,
							dmp.DMAJ_PP_ID		AS panitera_pengganti_id,
							'Panitera Pengganti :'+(SELECT mpp.MPP_NAMA where dmp.DMAJ_PP_ID=mpp.MPP_ID) as panitera_pengganti_text,
							(SELECT convert(varchar(10), dmp.DMAJ_TGL_PMH, 120)) as penetapan_jurusita,
							dmp.MJS_ID		as jurusita_id,
							'Jurusita Penganti :'+(SELECT mj.MJS_NAMA where dmp.MJS_ID=mj.MJS_ID) as jurusita_text,
							ds.DREG_ID		as sidang_id,
							(convert(varchar(10), DPR_PEN_TGL_PHS,120))		as tanggal_penetapan_sidang_verzet,
							(convert(varchar(10), (select MIN(dsp.DSID_TANGGAL) FROM DATA_SIDANG_PLW AS dsp where dsp.DREG_ID=dps.DREG_ID),120))		as tanggal_sidang_pertama_verzet,
							(convert(varchar(10), dpp.DREG_TGL_PUTUSAN,120))		as putusan_verzet,
							dpp.MJNSPUT_ID			as status_putusan_verzet_id,
							mjp.JNSPUT_NAMA			as status_putusan_verzet_text,
							'T'						as putusan_verstek,
							dpp.DREG_AMAR_PUTUSAN	as amar_putusan_verzet
							
						FROM DATA_PERLAWANAN as dps
						LEFT JOIN DATA_MAJELIS_PLW as dmp on dmp.DREG_ID=dps.DREG_ID
						LEFT JOIN MS_HAKIM as mh on dmp.DMAJ_KETUA_ID = mh.MHKM_ID
						LEFT JOIN MS_HAKIM as mh1 on dmp.DMAJ_ANGGOTA1_ID = mh1.MHKM_ID
						LEFT JOIN MS_HAKIM as mh2 on dmp.DMAJ_ANGGOTA2_ID = mh2.MHKM_ID
						LEFT JOIN MS_PP as mpp on mpp.MPP_ID=dmp.DMAJ_PP_ID
						LEFT JOIN MS_JS as mj on mj.MJS_ID=dmp.MJS_ID
						LEFT JOIN DATA_PUTUSAN_PLW as dpp on dpp.DREG_ID=dps.DREG_ID
						LEFT JOIN MS_JENIS_PUTUSAN as mjp on mjp.JNSPUT_KODE=dpp.MJNSPUT_ID 
						LEFT JOIN DATA_SIDANG as ds on ds.DREG_ID = dps.DREG_ID";
  
        if(exe_sqlserver($conn,$ms_perkara26)){
			$this->nativesession->set('update_progress', 42); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkara26.'));
			return;
		}
		
		$ms_perkara27 = "SELECT * FROM v_perkara_verzet where perkara_id in (".$cek_perkara_id.")";
		$ms_perkara27_exe = exe_sqlserver($conn,$ms_perkara27);

		$jml_data = odbc_num_rows($ms_perkara27_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkara27_exe)){
			//$this->load->helper('text');
			
			$data15['perkara_id']=$row['perkara_id']; 
			$data15['tanggal_pendaftaran_verzet']=$row['tanggal_pendaftaran_verzet']; 
			$data15['tanggal_surat']=$row['tanggal_surat']; 
			$data15['penetapan_majelis_hakim']=$row['penetapan_majelis_hakim']; 
			$data15['majelis_hakim_id']=$row['majelis_hakim_id']; 
			$data15['majelis_hakim_nama']=$row['majelis_hakim_nama']; 
			$data15['majelis_hakim_text']=$row['majelis_hakim_text']; 
			$data15['penetapan_panitera_pengganti']=$row['penetapan_panitera_pengganti']; 
			$data15['panitera_pengganti_id']=$row['panitera_pengganti_id']; 
			$data15['panitera_pengganti_text']=$row['panitera_pengganti_text']; 
			$data15['penetapan_jurusita']=$row['penetapan_jurusita']; 
			$data15['jurusita_id']=$row['jurusita_id']; 
			$data15['jurusita_text']=$row['jurusita_text']; 
			$data15['sidang_id']=$row['sidang_id']; 
			$data15['tanggal_penetapan_sidang_verzet']=$row['tanggal_penetapan_sidang_verzet']; 
			$data15['tanggal_sidang_pertama_verzet']=$row['tanggal_sidang_pertama_verzet']; 
			$data15['putusan_verzet']=$row['putusan_verzet']; 
			$data15['status_putusan_verzet_id']=$row['status_putusan_verzet_id']; 
			$data15['status_putusan_verzet_text']=$row['status_putusan_verzet_text']; 
			$data15['putusan_verstek']=$row['putusan_verstek']; 

			$amar_verzet=ascii_to_entities($row['amar_putusan_verzet']);
			$amar_verzet_nobr=str_replace(array("\pard","\par"), array("</br>","</br>"), $amar_verzet);
			$amar_verzet_new=preg_replace('(\'"{\\*?\\\\.+(;})|\\s?\\\\[A-Za-z0-9]+|\\s?{\\s?\\\\[A-Za-z0-9]+\\s?|\\s?}\\s?"\')','',$amar_verzet_nobr, -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('','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
			$amar_verzet_clean=str_replace($cari, $ganti, $amar_verzet_new);
			$data15['amar_putusan_verzet']=$amar_verzet_clean;


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

			$my_perkara27 = $this->patching->replace_data('perkara_verzet',$data15);
		}
		if($my_perkara27){
			$this->nativesession->set('update_progress', 43); 
			session_write_close();
			session_start();

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

	$update_perkara_dismissal2 ="UPDATE perkara_dismissal SET hasil=2 
							    WHERE tanggal_penetapan is not null and perkara_id in (select perkara_id from perkara_verzet where status_putusan_verzet_id=34)";			
	$update_dismissal2=$this->patching->exe_script($update_perkara_dismissal2);

	if($update_dismissal2){
		$this->nativesession->set('update_progress', 43); 
			session_write_close();
			session_start();

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

//--- Buat Masukkin ke Table jurusita 

		$ms_perkarajs = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_jurusita') BEGIN
    					DROP VIEW dbo.v_jurusita
						END";
		
		$ms_perkarajs_exe = exe_sqlserver($conn,$ms_perkarajs);
		if($ms_perkarajs_exe){
			$this->nativesession->set('update_progress', 44); 
			session_write_close();
			session_start();

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

		$ms_perkarajs2= "CREATE VIEW v_jurusita AS
						SELECT 
						MJS_ID as id, 
						MJS_NIP as nip, 
						MJS_NAMA as nama,  
						'Y' as aktif
						FROM MS_JS"; 

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

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

		$ms_perkarajs3 = "SELECT * FROM v_jurusita where id is not null";
		$ms_perkarajs3_exe = exe_sqlserver($conn,$ms_perkarajs3);
	
$jml_data = odbc_num_rows($ms_perkarajs3_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkarajs3_exe)){
			//$this->load->helper('text');
			
			$data16['id']=$row['id']; 
			$data16['nip']=$row['nip']; 
			$data16['nama']=$row['nama']; 
			$data16['aktif']=$row['aktif']; 
			$data16['diinput_oleh']='System';
			$data16['diinput_tanggal']=date("Y-m-d H:i:s", time());

		$my_perkarajs3 = $this->patching->replace_data('jurusita',$data16);
		}
		if($my_perkarajs3){
			$this->nativesession->set('update_progress', 46); 
			session_write_close();
			session_start();

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

//--- Buat Masukkin ke Table perkara_jurusita 

		$ms_perkara1js = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v_perkara_jurusita') BEGIN
    					DROP VIEW dbo.v_perkara_jurusita
						END";
       	$ms_perkara1js_exe = exe_sqlserver($conn,$ms_perkara1js);
		if($ms_perkara1js_exe){
			$this->nativesession->set('update_progress', 47); 
			session_write_close();
			session_start();

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

        $ms_perkara2js= "CREATE VIEW v_perkara_jurusita AS
		                SELECT 
                        ROW_NUMBER() OVER( ORDER BY dm.DATA_ID) as id,    
                        dm.DREG_ID as perkara_id, 
                        10 as tahapan_id, 
                        (SELECT convert(varchar(10), dm.DMAJ_TGL_PMH, 120)) as tanggal_penetapan,  
                        (cast((select MSET_VALUE from MS_SETTING where MSET_KEY='KODENOPERK' AND MSET_TYPE=1 AND MSET_TYPE IS NOT NULL) 
	                    COLLATE DATABASE_DEFAULT as varchar(50))) as nomor_sk_penetapan, 
                        1 as urutan, 
                        (SELECT js.MJS_ID FROM MS_JS where js.MJS_ID = dm.MJS_ID) as jurusita_id,  
                        (SELECT js.MJS_NIP FROM MS_JS where  js.MJS_ID = dm.MJS_ID) as jurusita_nip, 
                        (SELECT js.MJS_NAMA FROM MS_JS where  js.MJS_ID = dm.MJS_ID) as jurusita_nama, 
                        'Y' as aktif 

                        FROM MS_JS as js 
                        LEFT JOIN DATA_MAJELIS as dm on js.MJS_ID = dm.MJS_ID 
                        LEFT JOIN DATA_REGISTER as dr on dm.DREG_ID=dr.DATA_ID";

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

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

		$ms_perkara3js = "SELECT * FROM v_perkara_jurusita where perkara_id is not null";
		$ms_perkara3js_exe = exe_sqlserver($conn,$ms_perkara3js);

		$jml_data = odbc_num_rows($ms_perkara3js_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkara3js_exe)){
			//$this->load->helper('text');
			
			$data17['id']=$row['id'];
			$data17['perkara_id']=$row['perkara_id']; 
			$data17['tahapan_id']=$row['tahapan_id']; 
			$data17['tanggal_penetapan']=$row['tanggal_penetapan']; 
			$data17['nomor_sk_penetapan']=$row['nomor_sk_penetapan']; 
			$data17['urutan']=$row['urutan']; 
			$data17['jurusita_id']=$row['jurusita_id']; 
			$data17['jurusita_nip']=$row['jurusita_nip']; 
			$data17['jurusita_nama']=$row['jurusita_nama']; 
			$data17['aktif']=$row['aktif'];
			$data17['diinput_oleh']='System';
			$data17['diinput_tanggal']=date("Y-m-d H:i:s", time());

		$my_perkara3js = $this->patching->replace_data('perkara_jurusita',$data17);
		}
		if($my_perkara3js){
			$this->nativesession->set('update_progress', 49); 
			session_write_close();
			session_start();

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

   // Buat Masukkin ke Table perkara_jadwal_sidang 

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

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

		$ms_perkarajdwl2= "CREATE VIEW v_perkara_jadwal_sidang AS
						SELECT 
						ROW_NUMBER() OVER( ORDER BY ds.DATA_ID) as id, 
						ds.DREG_ID as perkara_id, 
						CASE WHEN (dp.DPER_NO is NULL) THEN 'T' ELSE 'Y' END as verzet, 
						'T' as keberatan, 
						ROW_NUMBER()OVER(PARTITION BY ds.DREG_ID ORDER BY convert(varchar(10), ds.DSID_TANGGAL, 120)) as urutan, 
						(SELECT convert(varchar(10), ds.DSID_TANGGAL, 120)) as tanggal_sidang,  
						(SELECT REPLACE (LEFT(ds.DSID_JAM, 5), ';',':')) as jam_sidang,    
						ds.DSID_AGENDA as agenda, 
						ds.DSID_RUANG as ruangan,  
						'T' as sidang_keliling, 
						1 as sidang_ditempat
						FROM DATA_SIDANG as ds   
						LEFT JOIN DATA_PERLAWANAN as dp on ds.DREG_ID = dp.DREG_ID"; 

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

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query ms_perkarajdwl2.'));
			return;
		}
		
		$ms_perkarajdwl3 = "SELECT * FROM v_perkara_jadwal_sidang where perkara_id in (".$cek_perkara_id.") and tanggal_sidang is not null";
		$ms_perkarajdwl3_exe = exe_sqlserver($conn,$ms_perkarajdwl3);

		$jml_data = odbc_num_rows($ms_perkarajdwl3_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkarajdwl3_exe)){
			//$this->load->helper('text');
			
			$data18['id']=$row['id']; 
            $data18['perkara_id']=$row['perkara_id']; 
            $data18['verzet']=$row['verzet'];
            $data18['keberatan']=$row['keberatan']; 
            $data18['urutan']=$row['urutan']; 
            $data18['id']=$row['id'];
            $data18['tanggal_sidang']=$row['tanggal_sidang']; 
            //$data18['jam_sidang']=$row['jam_sidang']; 
            $data18['agenda']=$row['agenda']; 
            $data18['ruangan']=$row['ruangan']; 
            $data18['sidang_keliling']=$row['sidang_keliling']; 
            $data18['sidang_ditempat']=$row['sidang_ditempat']; 
			$data18['diinput_oleh']='System';
			$data18['diinput_tanggal']=date("Y-m-d H:i:s", time());
         
			$my_perkarajdwl3 = $this->patching->replace_data('perkara_jadwal_sidang',$data18);
		}
		if($my_perkarajdwl3){
			$this->nativesession->set('update_progress', 52); 
			session_write_close();
			session_start();

		}else{
			echo json_encode(array('st'=>0,'msg'=>'Error query my_perkarajdwl3.'));
			return;
		}
}
//-- Buat Masukkin ke Table perkara_putusan

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

    $ms_perkaraput1_exe = exe_sqlserver($conn,$ms_perkaraput1);
		if($ms_perkaraput1_exe){
			$this->nativesession->set('update_progress', 53); 
			session_write_close();
			session_start();

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

	$ms_perkaraput2= "CREATE VIEW v_perkara_putusan AS
					 SELECT 
	                 dp.DREG_ID					as perkara_id,
	                 (SELECT convert(varchar(10), dp.DREG_TGL_PUTUSAN, 120)) as tanggal_putusan,
                     (case 
	                     when dp.MJNSPUT_ID = 1 THEN 34
	                     when dp.MJNSPUT_ID = 2 THEN 35 
	                     when dp.MJNSPUT_ID = 3 THEN 38 
	                     when dp.MJNSPUT_ID = 4 THEN 36 
	                     when dp.MJNSPUT_ID = 5 THEN 37
                     end) 						as status_putusan_id, 
	                 mjp.JNSPUT_NAMA			as status_putusan_nama,
	                 mjp.JNSPUT_NAMA			as status_putusan_text,
	                 (case
		             when dp.MJNSPUT_ID = 5 THEN (SELECT convert(varchar(10), dp.DREG_TGL_PUTUSAN, 120))
	                 end) as tanggal_cabut,
	                 (case
		             when dp.MJNSPUT_ID = 3 THEN (SELECT convert(varchar(10), dp.DREG_TGL_PUTUSAN, 120))
					 end) as tanggal_gugur,
					 dp.DREG_AMAR_PUTUSAN	as amar_putusan,
					 (SELECT convert(varchar(10), dr.DREG_TGL_MINUTASI, 120)) as tanggal_minutasi
					 FROM DATA_PUTUSAN as dp
					 LEFT JOIN MS_JENIS_PUTUSAN as mjp on mjp.JNSPUT_KODE=dp.MJNSPUT_ID
					 LEFT JOIN DATA_REGISTER as dr on dr.DATA_ID=dp.DREG_ID
					 where dp.DREG_TGL_PUTUSAN is not null";	
				   
    if(exe_sqlserver($conn,$ms_perkaraput2)){
			$this->nativesession->set('update_progress', 54); 
			session_write_close();
			session_start();

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

	$ms_perkaraput3 = "SELECT * FROM v_perkara_putusan where perkara_id in (".$cek_perkara_id.") and tanggal_putusan is not null";
		$ms_perkaraput3_exe = exe_sqlserver($conn,$ms_perkaraput3);

		$jml_data = odbc_num_rows($ms_perkaraput3_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkaraput3_exe)){
			//$this->load->helper('text');
			
			$data19['perkara_id']=$row['perkara_id'];
			$data19['tanggal_putusan']=$row['tanggal_putusan']; 
			$data19['status_putusan_id']=$row['status_putusan_id']; 
			$data19['status_putusan_nama']=$row['status_putusan_nama']; 
			$data19['status_putusan_text']=$row['status_putusan_text']; 
			$data19['tanggal_cabut']=$row['tanggal_cabut']; 
			$data19['tanggal_gugur']=$row['tanggal_gugur']; 
			$amar_p=ascii_to_entities($row['amar_putusan']); 
			
			$amar_p_nobr=str_replace(array("\pard","\par"), array("</br>","</br>"), $amar_p);
			$amar_p_new=preg_replace('(\'"{\\*?\\\\.+(;})|\\s?\\\\[A-Za-z0-9]+|\\s?{\\s?\\\\[A-Za-z0-9]+\\s?|\\s?}\\s?"\')','',$amar_p_nobr, -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('','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
			$amar_p_clean=str_replace($cari, $ganti, $amar_p_new);
			$data19['amar_putusan']=$amar_p_clean; 

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

			$my_perkaraput3=$this->patching->replace_data('perkara_putusan',$data19);
			if(!empty($row['tanggal_putusan'])){
			$update_perkara_p ="UPDATE perkara SET 
							  	  tahapan_terakhir_id=15,
							 	  tahapan_terakhir_text='Putusan',
							 	  proses_terakhir_id=210,
							 	  proses_terakhir_text='Putusan'
							 	  WHERE perkara_id=".$row['perkara_id']."";
			
			if($this->patching->exe_script($update_perkara_p)){
				$data19_p['perkara_id']=$row['perkara_id'];
				$data19_p['tahapan_id']=15;
				$data19_p['tahapan_nama']='Putusan';
				$data19_p['proses_id']=210;
				$data19_p['proses_nama']='Putusan';
				$data19_p['tanggal']=$row['tanggal_putusan'];
				$data19_p['diinput_oleh']='System';
				$data19_p['diinput_tanggal']=date("Y-m-d H:i:s", time());

				$this->patching->replace_data('perkara_proses',$data19_p);
			}
			}
			//$my_perkaraput3=TRUE;


		}
		if($my_perkaraput3){
			$this->nativesession->set('update_progress', 55); 
			session_write_close();
			session_start();

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

//-- Buat Bikin Table putusan_sela --

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

	$ms_perkarasela1_exe = exe_sqlserver($conn,$ms_perkarasela1);
		if($ms_perkarasela1_exe){
			$this->nativesession->set('update_progress', 56); 
			session_write_close();
			session_start();

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

   $ms_perkarasela2= "CREATE VIEW v_perkara_putusan_sela AS
				     SELECT 
					ROW_NUMBER() OVER (ORDER BY dps.DATA_ID) AS putusansela_id,
					dps.DREG_ID					as perkara_id,
					(SELECT convert(varchar(10), dps.DREG_TGL_PUTUSAN, 120)) as tanggal_putusan_sela,
					dps.DREG_AMAR_PUTUSAN	as amar_sela
					FROM DATA_PUTUSAN_SELA as dps"; 

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

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

    $ms_perkarasela3 = "SELECT * FROM v_perkara_putusan_sela where perkara_id in (".$cek_perkara_id.")";
	$ms_perkarasela3_exe = exe_sqlserver($conn,$ms_perkarasela3);

	$jml_data = odbc_num_rows($ms_perkarasela3_exe);
		if($jml_data>0){
		
		while($row = odbc_fetch_array($ms_perkarasela3_exe)){
			//$this->load->helper('text');
			
			$data20['putusansela_id']=$row['putusansela_id']; 
			$data20['perkara_id']=$row['perkara_id']; 
			$data20['tanggal_putusan_sela']=$row['tanggal_putusan_sela']; 

			$amar_c=$row['amar_sela']; 
			$amar_c_nobr=str_replace(array("\pard","\par"), array("</br>","</br>"), $amar_c);
			$amar_c_new=preg_replace('(\'"{\\*?\\\\.+(;})|\\s?\\\\[A-Za-z0-9]+|\\s?{\\s?\\\\[A-Za-z0-9]+\\s?|\\s?}\\s?"\')','',$amar_p_nobr, -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('','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
			$amar_c_clean=str_replace($cari, $ganti, $amar_c_new);
			$data20['amar_sela']=$amar_c_clean; 

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

			$my_perkarasela3 = $this->patching->replace_data('perkara_putusan_sela',$data20);
		}
		if($my_perkarasela3){
			$this->nativesession->set('update_progress', 58); 
			session_write_close();
			session_start();

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

    		//--tambah jenis biaya mas puji
		$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', 62); 
			session_write_close();
			session_start();

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

		$sql_jb = "REPLACE INTO idjenisbiaya_lama_baru 
				   VALUES 
				   (1,224),(2,162),(3,1),(4,224),(5,162),(6,162),(94,150),
				   (8,12),(9,14),(10,16),(11,29),(12,28),(13,364),(14,30),
				   (15,365),(16,366),(17,367),(18,45),(19,31),(20,143),(21,263),
				   (22,264),(23,266),(24,9),(25,269),(26,36),(27,39),(28,37),(29,38),
				   (30,309),(31,368),(32,275),(33,41),(34,145),(35,42),(36,369),(37,43),
				   (38,162),(39,370),(40,371),(41,372),(42,373),(43,374),(44,375),(45,376),
				   (46,377),(47,378),(49,379),(50,252),(51,380),(52,1),(53,2),(54,4),(55,6),
				   (56,157),(57,343),(58,261),(59,271),(60,277),(61,262),(62,272),(63,278),
				   (64,275),(65,146),(66,336),(67,337),(68,381),(69,34),(70,335),(71,382),
				   (72,177),(73,383),(74,6),(75,10),(76,11),(77,384),(78,385),(79,386),(80,387),
				   (81,388),(82,389),(83,390),(84,391),(85,392),(86,151),(87,166),(88,181),
				   (89,196),(90,211),(95,155),(96,152),(97,154),(98,393),(99,45),(100,14),
				   (101,394),(102,395),(103,151),(104,151),(105,166),(106,181),(107,196),
				   (108,211),(109,153),(110,16),(111,393)
				   ";
				
		if($this->patching->exe_script($sql_jb)){
			$this->nativesession->set('update_progress', 63); 
			session_write_close();
			session_start();

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

		//--tambah end

		// perkara_biaya
	    // mas puji

		$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', 64); 
			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 dt.DATA_ID) AS id,
							1							as id_pembiayaan,
							dr.DATA_ID					AS perkara_id,
							10							AS tahapan_id,
							dt.PERKIRAAN_ID				AS jenis_biaya_id_lama,
							ROW_NUMBER()OVER(Partition By dr.DATA_ID Order By dr.DATA_ID) as urutan,
							(case
								when dt.PERKIRAAN_ID = 1 or dt.PERKIRAAN_ID = 3 OR dt.PERKIRAAN_ID = 4 then 
								1 
								else
								-1 
							end) as jenis_transaksi,
							(SELECT convert(varchar(10), dt.TANGGAL, 120)) as tanggal_transaksi,
							kp.NAMA				AS uraian,
							dt.NILAI			as jumlah
							
					FROM KEU_TRANS AS dt
					LEFT JOIN DATA_REGISTER as dr on dr.KEY_ID=dt.KEY_ID
					LEFT JOIN KEU_PERKIRAAN as kp on dt.PERKIRAAN_ID=kp.DATA_ID
					where dr.DATA_ID IS NOT NULL AND dt.PERKIRAAN_ID IS NOT NULL 
					AND dt.PERKIRAAN_ID IS NOT NULL AND dt.PERKIRAAN_ID <> 0
					AND dt.TANGGAL IS NOT NULL 
					AND dr.DATA_ID IN (SELECT DATA_ID FROM DATA_REGISTER)
					AND dt.PERKIRAAN_ID IN (SELECT DATA_ID FROM KEU_PERKIRAAN)
					"; 

     if(exe_sqlserver($conn,$ms_perkara30)){
			$this->nativesession->set('update_progress', 65); 
			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 perkara_id in (".$cek_perkara_id.")";
	$ms_perkara30_exe = exe_sqlserver($conn,$ms_perkara30);
		
    $jml_data = odbc_num_rows($ms_perkara30_exe);
		if($jml_data>0){

		while($row = odbc_fetch_array($ms_perkara30_exe)){
			//$this->load->helper('text');
			
			$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());
			
			$my_perkara30 = $this->patching->replace_data('perkara_biaya',$data30);
		}

		if($my_perkara30){
			$this->nativesession->set('update_progress', 66); 
			session_write_close();
			session_start();

		}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;
		}		
}

		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;');	

	
	}
	
}