<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <title>JKRoyalty | Upload Report Youtube</title>
  <!-- Tell the browser to be responsive to screen width -->
  <meta content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no" name="viewport">
  <!-- Bootstrap 3.3.6 -->
  <link rel="stylesheet" href="../../bootstrap/css/bootstrap.min.css">
  <!-- Font Awesome -->
  <link rel="stylesheet" href="../../cloudflare/ajax/libs/font-awesome-4.7.0/css/font-awesome.min.css">
  <!-- Ionicons -->
  <link rel="stylesheet" href="../../cloudflare/ajax/libs/ionicons-2.0.1/css/ionicons.min.css">
  <!-- DataTables -->
  <link rel="stylesheet" href="../../plugins/datatables/dataTables.bootstrap.css">
  <!-- Select2 -->
  <link rel="stylesheet" href="../../plugins/select2/select2.min.css">
  <!-- Theme style -->
  <link rel="stylesheet" href="../../dist/css/AdminLTE.min.css">
  <!-- AdminLTE Skins. Choose a skin from the css/skins
       folder instead of downloading all of them to reduce the load. -->
  <link rel="stylesheet" href="../../dist/css/skins/_all-skins.min.css">
  
  <!--<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>-->
  <script src="../../plugins/jQuery/jquery-2.2.3.min.js"></script>
</head>
<body>
<div class="form-group">
	<div align="center" style=" margin-top:300px; "><b>Proses Input Data ....</b></div>
	<div align="center" class="progress" style=" margin-left:300px; height:25px; width:50%">
    	<div class="progress-bar progress-bar-striped progress-bar-animated" id="progress-bar" style="height:25px; width:100%; padding:0px">
        	<span id="progess-info"></span>
        </div>
    </div>
</div>

<?php
session_start();
ini_set('max_execution_time', 0);
include "../../conf/conn.php";
error_reporting(E_ALL ^ E_NOTICE); 
require_once '../../conf/excel_reader2.php';

//jika tombol import ditekan 
if(isset($_POST['submit']))
{
	
	$submitternumber 	= $_POST['submitternumber'];
	$sendertype 		= $_POST['sendertype'];
	$senderid 			= $_POST['senderid'];
	$sendername 		= $_POST['sendername'];
	date_default_timezone_set('Asia/Jakarta');
	$date				= date('Ymd');
	$time				= date('His');
	
 
	//----------------------HDR---------------------------------
      $hdr_record_type		    			= 'HDR';
	  $hdr_sender_type         				= $sendertype;
      $hdr_sender_id		 				= $senderid;
      $hdr_sender_name  					= $sendername;
	  $hdr_edi_standar_version_number 		= '01.10';
	  $hdr_creation_date 					= $date;
	  $hdr_creation_time 					= $time;
	  $hdr_transmission_date				= $date;
	  $hdr_character_set					= '';
	  $hdr_status_download					= '';
	  

	  //setelah data dibaca, masukkan ke tabel hdr sql
      $query_hdr = "INSERT ttk_hdr (record_type,
	  sender_type,
	  sender_id,
	  sender_name,
	  edi_standar_version_number, 
	  creation_date,
	  creation_time,
	  transmission_date,
	  character_set,
	  status_download)values('$hdr_record_type',
	  '$hdr_sender_type',
	  '$hdr_sender_id',
	  '".str_replace("'"," ",$hdr_sender_name)."',
	  '$hdr_edi_standar_version_number', 
	  '$hdr_creation_date',
	  '$hdr_creation_time',
	  '$hdr_transmission_date',
	  '$hdr_character_set',
	  '$hdr_status_download')";
      mysql_query($query_hdr);
	//----------------------HDR---------------------------------
	
	
	$target = basename($_FILES['filemetadata']['name']) ;
    move_uploaded_file($_FILES['filemetadata']['tmp_name'], $target);
    $data = new Spreadsheet_Excel_Reader($_FILES['filemetadata']['name'],false);

	//-----------------------Tampung Sementara-------------------
	$jumlahbaris_tmp = $data->rowcount(0);
	for($i=2; $i<=$jumlahbaris_tmp; $i++)
	{
		
		$barisXls = $jumlahbaris_tmp-1;
        $k = $i-1;
        $percent = intval($k/$barisXls * 100)."%";
    	// mengupdate progress bar
        echo '<script language="javascript">
        document.getElementById("progress-bar").style.width="'.$percent.'";
        document.getElementById("progess-info").innerHTML="'.($i-1).' record ('.$percent.')";
        </script>';
		
		
		//membaca data (kolom ke-1 sd terakhir)
		//Work
		$tmp_group_id 							= $data->val($i, 1, 0);
		$tmp_work_title 						= $data->val($i, 2, 0);
		$tmp_submitter_work 					= $data->val($i, 3, 0);
		$tmp_iswc 								= $data->val($i, 4, 0);
		$tmp_copy_right_date 					= $data->val($i, 5, 0);
		$tmp_musical_work_distribution_category = $data->val($i, 6, 0);
		$tmp_duration 							= $data->val($i, 7, 0);
		$tmp_version_type 						= $data->val($i, 8, 0);

		//Publisher
		$tmp_publisher_ipi_name_number 			= $data->val($i, 9, 0);
		$tmp_publisher_name 					= $data->val($i, 10, 0);
		$tmp_pub_pr_ownership_share 			= $data->val($i, 11, 0);
		$tmp_pub_mr_ownership_share 			= $data->val($i, 12, 0);
		$tmp_pub_sr_ownership_share 			= $data->val($i, 13, 0);
		$tmp_pub_pr_collection_share 			= $data->val($i, 14, 0);
		$tmp_pub_mr_collection_share 			= $data->val($i, 15, 0);
		$tmp_pub_sr_collection_share 			= $data->val($i, 16, 0);

		//Writer
		$tmp_writer_last_name 					= $data->val($i, 17, 0);
		$tmp_writer_first_name 					= $data->val($i, 18, 0);
		$tmp_writer_designation_code 			= $data->val($i, 19, 0);
		$tmp_writer_ipi_name_number 			= $data->val($i, 20, 0);
		$tmp_wrt_pr_ownership_share 			= $data->val($i, 21, 0);
		$tmp_wrt_mr_ownership_share 			= $data->val($i, 22, 0);
		$tmp_wrt_sr_ownership_share 			= $data->val($i, 23, 0);
		$tmp_wrt_pr_collection_share 			= $data->val($i, 24, 0);
		$tmp_wrt_mr_collection_share 			= $data->val($i, 25, 0);
		$tmp_wrt_sr_collection_share 			= $data->val($i, 26, 0);

		//Others
		$tmp_pr_affiliation_society 			= $data->val($i, 27, 0);
		$tmp_mr_society							= $data->val($i, 28, 0);
		$tmp_sr_society 						= $data->val($i, 29, 0);
		$tmp_ipi_base_number 					= $data->val($i, 30, 0);
		$tmp_tis_numeric_code 					= $data->val($i, 31, 0);


		//setelah data dibaca, masukkan ke tabel grh sql
		$query_grh = "INSERT INTO ttk_tmp (group_id,
		work_title, 						
		submitter_work, 					
		iswc, 								
		copy_right_date, 					
		musical_work_distribution_category, 
		duration, 							
		version_type, 
		publisher_ipi_name_number, 			
		publisher_name, 					
		pub_pr_ownership_share, 			
		pub_mr_ownership_share, 			
		pub_sr_ownership_share, 			
		pub_pr_collection_share, 			
		pub_mr_collection_share, 			
		pub_sr_collection_share, 
		writer_last_name, 					
		writer_first_name, 					
		writer_designation_code, 			
		writer_ipi_name_number, 			
		wrt_pr_ownership_share, 			
		wrt_mr_ownership_share, 			
		wrt_sr_ownership_share, 			
		wrt_pr_collection_share, 			
		wrt_mr_collection_share,
		wrt_sr_collection_share, 
		pr_affiliation_society, 			
		mr_affiliation_society,							
		sr_affiliation_society, 						
		ipi_base_number, 					
		tis_numeric_code)values('$tmp_group_id',
		'".str_replace("'","",$tmp_work_title)."', 						
		'$tmp_submitter_work', 					
		'$tmp_iswc', 								
		'$tmp_copy_right_date', 					
		'$tmp_musical_work_distribution_category',
		'$tmp_duration', 							
		'$tmp_version_type', 
		'$tmp_publisher_ipi_name_number', 			
		'$tmp_publisher_name', 					
		'$tmp_pub_pr_ownership_share', 			
		'$tmp_pub_mr_ownership_share', 			
		'$tmp_pub_sr_ownership_share', 			
		'$tmp_pub_pr_collection_share', 			
		'$tmp_pub_mr_collection_share', 			
		'$tmp_pub_sr_collection_share', 
		'".str_replace("'","",$tmp_writer_last_name)."', 					
		'".str_replace("'","",$tmp_writer_first_name)."', 					
		'$tmp_writer_designation_code', 			
		'$tmp_writer_ipi_name_number', 			
		'$tmp_wrt_pr_ownership_share', 			
		'$tmp_wrt_mr_ownership_share', 			
		'$tmp_wrt_sr_ownership_share', 			
		'$tmp_wrt_pr_collection_share', 			
		'$tmp_wrt_mr_collection_share', 			
		'$tmp_wrt_sr_collection_share', 
		'$tmp_pr_affiliation_society', 			
		'$tmp_mr_society',							
		'$tmp_sr_society', 						
		'$tmp_ipi_base_number', 					
		'$tmp_tis_numeric_code')";
		mysql_query($query_grh);


		flush();
    	// gunakan fungsi usleep 0,25 detik (25000 milisecond) untuk membuat jeda
    	usleep(25000);

	}
	//-----------------------Tampung Sementara-------------------


	
	//----------------------------- GRH -------------------------
	$no = 1;
	$query_grh_group = mysql_query("SELECT * FROM ttk_tmp GROUP BY group_id ORDER BY group_id");
	$jumlahbaris = mysql_num_rows($query_grh_group);
    while($data_grh_group = mysql_fetch_array($query_grh_group))
    {
		
		//PROGRESS BAR
		$barisXls = $jumlahbaris-1;
		$k = $no-1;
		$percent = @(intval($k/$barisXls * 100))."%";
		// mengupdate progress bar

		echo '<script language="javascript">
		document.getElementById("progress-bar").style.width="'.$percent.'";
		document.getElementById("progess-info").innerHTML="'.($no-1).' record ('.$percent.')";
		</script>';
		//AKHIR PROGRESS BAR


		$query=mysql_query("SELECT max(batch_request) as max_kode FROM ttk_grh ORDER BY batch_request DESC LIMIT 1");
		$data = mysql_fetch_array($query);
		$batchrequest = $data['max_kode'];
		//$no = substr($batchrequest, -10, 10);
		$nomor = $batchrequest;
		//$no += 1;
		
		if($nomor == '')
		{
			$noterurut = '0';	
		}
		elseif($nomor != '')
		{
			$nomor = (int) $batchrequest;
			$noterurut = $nomor + 1;
		}

		//$newCodeSong = $char . sprintf("%09s", $noterurut);
		$newBatchRequest = sprintf("%010s", $noterurut);
        

	  	
	  	$query_grh_spy = "INSERT INTO ttk_grh SET record_type = 'GRH',
      	transaction_type 	= 'NWR',
      	group_id 			= '$data_grh_group[group_id]',
      	version_number 		= '02.10',
      	batch_request 		= '$newBatchRequest',
	  	sub_distro_type 	= ''";
      	mysql_query($query_grh_spy);

	$no++;	
    }
	//----------------------------- GRH -------------------------

	
	
	$query_grh = mysql_query("SELECT * FROM ttk_grh ORDER BY group_id");
    while($data_grh = mysql_fetch_array($query_grh))
    {
		//----------------------------- NWR -------------------------
		$no = 1;
		$qnwr = mysql_query("SELECT * FROM ttk_tmp WHERE group_id = '$data_grh[group_id]' GROUP BY submitter_work ORDER BY submitter_work");
		$jumlahbaris = mysql_num_rows($qnwr);
    	while($data_nwr = mysql_fetch_array($qnwr))
    	{
	  		
			//PROGRESS BAR
			$barisXls = $jumlahbaris-1;
			$k = $no-1;
			$percent = @(intval($k/$barisXls * 100))."%";
			// mengupdate progress bar

			echo '<script language="javascript">
			document.getElementById("progress-bar").style.width="'.$percent.'";
			document.getElementById("progess-info").innerHTML="'.($no-1).' record ('.$percent.')";
			</script>';
			//AKHIR PROGRESS BAR
			
			
			//$query=mysql_query("SELECT max(transaction_sequence) as max_transactionsequence FROM ttk_nwr WHERE submitter_work_number = '$data_nwr[submitter_work]' 
			//					ORDER BY transaction_sequence DESC LIMIT 1");

			$query=mysql_query("SELECT max(transaction_sequence) as max_transactionsequence FROM ttk_nwr WHERE group_id = '$data_nwr[group_id]' 
								ORDER BY transaction_sequence DESC LIMIT 1");					
			$data = mysql_fetch_array($query);
			$transactionsequence = $data['max_transactionsequence'];
			//$no = substr($transactionsequence, -8, 8);
			$nomor = $transactionsequence;
			//$no += 1;
		
			if($nomor == '')
			{
				$noterurut = '0';
			}
			elseif($nomor != '')
			{
				$nomor = (int) $transactionsequence;
				$noterurut = $nomor + 1;
			}

			$newTransactionSequence = sprintf("%08s", $noterurut);

				
			//setelah data dibaca, masukkan ke tabel nwr sql
	  		$query_nwr = "INSERT INTO ttk_nwr SET group_id = '$data_nwr[group_id]', 
	  		record_type 							= 'NWR',
      		transaction_sequence 					= '$newTransactionSequence',
      		record_sequence 						= '00000000',
      		work_title 								= '$data_nwr[work_title]',
      		language_code 							= 'IN', 
	  		submitter_work_number 					= '$data_nwr[submitter_work]',
	  		iswc 									= '$data_nwr[iswc]',
	  		copy_right_date 						= '$data_nwr[copy_right_date]',
	  		copyright_number 						= '',
	  		musical_work_distribution 				= '$data_nwr[musical_work_distribution_category]',
	  		duration 								= '$data_nwr[duration]',
	  		recorded_indicator 						= 'Y',
	  		text_music_relationship					= '',
	  		composite_type 							= '',
	  		version_type 							= '$data_nwr[version_type]',
	  		excerpt_type 							= '',
	  		music_arrangement 						= '',
	  		lyric_adaptation 						= '',
	  		contact_name 							= '',
	  		contact_id 								= '',
	  		cwr_work_type 							= '',
	  		grand_right_indicator 					= 'N',
	  		composite_component_count 				= '000',
	  		date_of_publication_of_printed_edition 	= '00000000',
	  		exceptional_clause 						= '',
	  		opus_number 							= '',
	  		catalogue_number 						= '',
	  		priority_flag 							= 'Y'";
      		mysql_query($query_nwr);
	  		
    	
					
			//----------------------------- SPU -------------------------
			$qspu = mysql_query("SELECT * FROM ttk_tmp WHERE group_id = '$data_nwr[group_id]' AND submitter_work = '$data_nwr[submitter_work]' 
								GROUP BY publisher_ipi_name_number ORDER BY publisher_ipi_name_number");
    		while($data_spu = mysql_fetch_array($qspu))
    		{
				
				//pub_pr_ownership_share
				$q_spu_pub_pr_ownership = "SELECT group_id, submitter_work, publisher_ipi_name_number, SUM(pub_pr_ownership_share) 
										AS jumlah_pub_pr_ownership_share FROM ttk_tmp WHERE group_id = '$data_spu[group_id]' 
										AND submitter_work = '$data_spu[submitter_work]' 
										AND publisher_ipi_name_number = '$data_spu[publisher_ipi_name_number]'";
				$hasil_spu_pub_pr_ownership = @mysql_query($q_spu_pub_pr_ownership) or die (mysql_error());
				$dtjum_spu_pub_pr_ownership = mysql_fetch_array($hasil_spu_pub_pr_ownership);
				$jumkarakter_pr =strlen($dtjum_spu_pub_pr_ownership['jumlah_pub_pr_ownership_share']);
				if($jumkarakter_pr == '4')
				{
					$dtjum_spu_pub_pr_ownership = '0'.$dtjum_spu_pub_pr_ownership['jumlah_pub_pr_ownership_share'];	
				}
				elseif($jumkarakter_pr == '3')
				{
					$dtjum_spu_pub_pr_ownership = '00'.$dtjum_spu_pub_pr_ownership['jumlah_pub_pr_ownership_share'];	
				}
				elseif($jumkarakter_pr == '1')
				{
					$dtjum_spu_pub_pr_ownership = '0000'.$dtjum_spu_pub_pr_ownership['jumlah_pub_pr_ownership_share'];	
				}
				else
				{
					$dtjum_spu_pub_pr_ownership = $dtjum_spu_pub_pr_ownership['jumlah_pub_pr_ownership_share'];	
				}


				//pub_mr_ownership_share
				$q_spu_pub_mr_ownership = "SELECT group_id, submitter_work, publisher_ipi_name_number, SUM(pub_mr_ownership_share) 
										AS jumlah_pub_mr_ownership_share FROM ttk_tmp WHERE group_id = '$data_spu[group_id]' 
										AND submitter_work = '$data_spu[submitter_work]' 
										AND publisher_ipi_name_number = '$data_spu[publisher_ipi_name_number]'";
				$hasil_spu_pub_mr_ownership = @mysql_query($q_spu_pub_mr_ownership) or die (mysql_error());
				$dtjum_spu_pub_mr_ownership = mysql_fetch_array($hasil_spu_pub_mr_ownership);
				$jumkarakter_mr =strlen($dtjum_spu_pub_mr_ownership['jumlah_pub_mr_ownership_share']);
				if($jumkarakter_mr == '4')
				{
					$dtjum_spu_pub_mr_ownership = '0'.$dtjum_spu_pub_mr_ownership['jumlah_pub_mr_ownership_share'];	
				}
				elseif($jumkarakter_mr == '3')
				{
					$dtjum_spu_pub_mr_ownership = '00'.$dtjum_spu_pub_mr_ownership['jumlah_pub_mr_ownership_share'];	
				}
				elseif($jumkarakter_mr == '1')
				{
					$dtjum_spu_pub_mr_ownership = '0000'.$dtjum_spu_pub_mr_ownership['jumlah_pub_mr_ownership_share'];	
				}
				else
				{
					$dtjum_spu_pub_mr_ownership = $dtjum_spu_pub_mr_ownership['jumlah_pub_mr_ownership_share'];	
				}


				//pub_sr_ownership_share
				$q_spu_pub_sr_ownership = "SELECT group_id, submitter_work, publisher_ipi_name_number, pub_sr_ownership_share 
										FROM ttk_tmp WHERE group_id = '$data_spu[group_id]' 
										AND submitter_work = '$data_spu[submitter_work]' 
										AND publisher_ipi_name_number = '$data_spu[publisher_ipi_name_number]'";
				$hasil_spu_pub_sr_ownership = mysql_query($q_spu_pub_sr_ownership) or die (mysql_error());
				$dtjum_spu_pub_sr_ownership = mysql_fetch_array($hasil_spu_pub_sr_ownership);
				$jumkarakter_sr =strlen($dtjum_spu_pub_sr_ownership['pub_sr_ownership_share']);
				if($jumkarakter_sr == '4')
				{
					$dtjum_spu_pub_sr_ownership = '0'.$dtjum_spu_pub_sr_ownership['pub_sr_ownership_share'];	
				}
				elseif($jumkarakter_sr == '3')
				{
					$dtjum_spu_pub_sr_ownership = '00'.$dtjum_spu_pub_sr_ownership['pub_sr_ownership_share'];	
				}
				elseif($jumkarakter_sr == '1')
				{
					$dtjum_spu_pub_sr_ownership = '0000'.$dtjum_spu_pub_sr_ownership['pub_sr_ownership_share'];	
				}
				else
				{
					$dtjum_spu_pub_sr_ownership = $dtjum_spu_pub_sr_ownership['pub_sr_ownership_share'];	
				}


				$spu_interested_party_number = substr($data_spu['publisher_ipi_name_number'], 0, 9);

				
				$querynwr = mysql_query("SELECT * FROM ttk_nwr WHERE group_id = '$data_nwr[group_id]' AND submitter_work_number = '$data_spu[submitter_work]' 
										ORDER BY transaction_sequence");
    			while($datanwr = mysql_fetch_array($querynwr))
    			{
					//membuat seq_spu
					$query_seqspu=mysql_query("SELECT max(seq_spu) as max_seqspu FROM ttk_spu WHERE  group_id = '$datanwr[group_id]' 
										AND transaction_sequence = '$datanwr[transaction_sequence]' 
										ORDER BY seq_spu DESC LIMIT 1");
					$data_seqspu = mysql_fetch_array($query_seqspu);
					$seqspu = $data_seqspu['max_seqspu'];
					//$no = substr($seqspu, -8, 8);
					$nomor = $seqspu;
					//$no += 1;
		
					if($nomor == '')
					{
						$noterurut_seqspu = '1';	
					}
					elseif($nomor != '')
					{
						$noterurut_seqspu = $nomor + 1;
					}

					
					$newTransactionSequence_spu = $noterurut_seqspu;
					$publisher_sequence_number = sprintf("%02s", $noterurut_seqspu);

					
					//setelah data dibaca, masukkan ke tabel spu sql
	  				$query_spu = "INSERT INTO ttk_spu SET group_id = '$data_spu[group_id]', 
	  				record_type 						= 'SPU',
      				transaction_sequence 				= '$newTransactionSequence',
      				seq_spu 							= '$newTransactionSequence_spu',
      				publisher_sequence_number 			= '$publisher_sequence_number',
      				interested_party_number 			= '$spu_interested_party_number', 
	  				publisher_name 						= '$data_spu[publisher_name]',
	 				publisher_unknown_indicator  		= '',
	  				publisher_type 						= 'E',
	  				tax_id_number 						= '000000000',
	  				publisher_ipi_name_number 			= '$data_spu[publisher_ipi_name_number]',
	  				submitter_agreement_number 			= '',
	  				pr_affiliation_society 				= '$data_spu[pr_affiliation_society]',
	  				pr_ownership_share 					= '$dtjum_spu_pub_pr_ownership',
	  				mr_affiliation_society 				= '$data_spu[mr_affiliation_society]',
	  				mr_ownership_share 					= '$dtjum_spu_pub_mr_ownership',
	  				sr_affiliation_society 				= '$data_spu[sr_affiliation_society]',
	  				sr_ownership_share 					= '$dtjum_spu_pub_sr_ownership',
	  				special_agreements_indicator 		= '',
	  				first_recording_refusal_indicator 	= '',
	  				filler 								= '',
	  				publisher_ipi_base_number 			= '$data_spu[ipi_base_number]',
	  				isac 								= '',
	  				saan 								= '',
	  				agreement_type 						= '',
	  				usa_license_indicator 				= ''";
      				mysql_query($query_spu);
				}	

	  				
    		}
			//----------------------------- SPU -------------------------

		$no++;	
		}	
	
		
		
    	
		//----------------------------- SPT -------------------------
		$no = 1;
		$queryspt = mysql_query("SELECT * FROM ttk_spu WHERE group_id = '$data_grh[group_id]' GROUP BY transaction_sequence 
								ORDER BY transaction_sequence");
		$jumlahbaris = mysql_num_rows($queryspt);						
    	while($data_spt = mysql_fetch_array($queryspt))
    	{

			//PROGRESS BAR
			$barisXls = $jumlahbaris-1;
			$k = $no-1;
			$percent = @(intval($k/$barisXls * 100))."%";
			// mengupdate progress bar
	
			echo '<script language="javascript">
			document.getElementById("progress-bar").style.width="'.$percent.'";
			document.getElementById("progess-info").innerHTML="'.($no-1).' record ('.$percent.')";
			</script>';
			//AKHIR PROGRESS BAR

				
			//pub_pr_ownership_share
			$q_spt_pr_ownership = "SELECT group_id, publisher_ipi_name_number, SUM(pr_ownership_share) 
									AS jumlah_pr_ownership_share FROM ttk_spu WHERE group_id = '$data_spt[group_id]' 
									AND transaction_sequence = '$data_spt[transaction_sequence]'";
			$hasil_spt_pr_ownership = @mysql_query($q_spt_pr_ownership) or die (mysql_error());
			$dtjum_spt_pr_ownership = mysql_fetch_array($hasil_spt_pr_ownership);
			$jumkarakter_pr =strlen($dtjum_spt_pr_ownership['jumlah_pr_ownership_share']);
			if($jumkarakter_pr == '4')
			{
				$dtjum_spt_pub_pr_ownership = '0'.$dtjum_spt_pr_ownership['jumlah_pr_ownership_share'];	
			}
			elseif($jumkarakter_pr == '3')
			{
				$dtjum_spt_pub_pr_ownership = '00'.$dtjum_spt_pr_ownership['jumlah_pr_ownership_share'];	
			}
			elseif($jumkarakter_pr == '1')
			{
				$dtjum_spt_pub_pr_ownership = '0000'.$dtjum_spt_pr_ownership['jumlah_pr_ownership_share'];	
			}
			else
			{
				$dtjum_spt_pub_pr_ownership = $dtjum_spt_pr_ownership['jumlah_pr_ownership_share'];	
			}


			//pub_mr_ownership_share
			$q_spt_mr_ownership = "SELECT group_id, publisher_ipi_name_number, SUM(mr_ownership_share) 
									AS jumlah_mr_ownership_share FROM ttk_spu WHERE group_id = '$data_spt[group_id]' 
									AND transaction_sequence = '$data_spt[transaction_sequence]'";
			$hasil_spt_mr_ownership = @mysql_query($q_spt_mr_ownership) or die (mysql_error());
			$dtjum_spt_mr_ownership = mysql_fetch_array($hasil_spt_mr_ownership);
			$jumkarakter_mr =strlen($dtjum_spt_mr_ownership['jumlah_mr_ownership_share']);
			if($jumkarakter_mr == '4')
			{
				$dtjum_spt_pub_mr_ownership = '0'.$dtjum_spt_mr_ownership['jumlah_mr_ownership_share'];	
			}
			elseif($jumkarakter_mr == '3')
			{
				$dtjum_spt_pub_mr_ownership = '00'.$dtjum_spt_mr_ownership['jumlah_mr_ownership_share'];	
			}
			elseif($jumkarakter_mr == '1')
			{
				$dtjum_spt_pub_mr_ownership = '0000'.$dtjum_spt_mr_ownership['jumlah_mr_ownership_share'];	
			}
			else
			{
				$dtjum_spt_pub_mr_ownership = $dtjum_spt_mr_ownership['jumlah_mr_ownership_share'];	
			}


			//pub_sr_ownership_share
			$q_spt_sr_ownership = "SELECT group_id, publisher_ipi_name_number, SUM(sr_ownership_share) 
									AS jumlah_sr_ownership_share FROM ttk_spu WHERE group_id = '$data_spt[group_id]' 
									AND transaction_sequence = '$data_spt[transaction_sequence]'";
			$hasil_spt_sr_ownership = @mysql_query($q_spt_sr_ownership) or die (mysql_error());
			$dtjum_spt_sr_ownership = mysql_fetch_array($hasil_spt_sr_ownership);
			$jumkarakter_sr =strlen($dtjum_spt_sr_ownership['jumlah_sr_ownership_share']);
			if($jumkarakter_sr == '4')
			{
				$dtjum_spt_pub_sr_ownership = '0'.$dtjum_spt_sr_ownership['jumlah_sr_ownership_share'];	
			}
			elseif($jumkarakter_sr == '3')
			{
				$dtjum_spt_pub_sr_ownership = '00'.$dtjum_spt_sr_ownership['jumlah_sr_ownership_share'];	
			}
			elseif($jumkarakter_sr == '1')
			{
				$dtjum_spt_pub_sr_ownership = '0000'.$dtjum_spt_sr_ownership['jumlah_sr_ownership_share'];	
			}
			else
			{
				$dtjum_spt_pub_sr_ownership = $dtjum_spt_sr_ownership['jumlah_sr_ownership_share'];	
			}	
					

			$spt_interested_party_number = substr($data_spt['publisher_ipi_name_number'], 0, 9);
				
			$qtmp = mysql_query("SELECT * FROM ttk_tmp GROUP BY group_id ORDER BY group_id");
    		$datatmp = mysql_fetch_array($qtmp);


			//$querynwr = mysql_query("SELECT * FROM ttk_nwr WHERE group_id = '$datatmp[group_id]' ORDER BY transaction_sequence");
    		//while($datanwr = mysql_fetch_array($querynwr))
    		//{
				//membuat seq_spt
				$query_seqspt=mysql_query("SELECT max(seq_spt) as max_seqspt FROM ttk_spt WHERE group_id = '$data_spt[group_id]' 
										AND transaction_sequence = '$data_spt[transaction_sequence]' ORDER BY seq_spt DESC LIMIT 1");
				$data_seqspt = mysql_fetch_array($query_seqspt);
				$seqspt = $data_seqspt['max_seqspt'];
				//$no = substr($seqspt, -8, 8);
				$nomor = $seqspt;
				//$no += 1;
		
				if($nomor == '')
				{
						$noterurut_seqspt = '1';	
				}
				elseif($nomor != '')
				{
						$noterurut_seqspt = $nomor + 1;
				}

				//$newTransactionSequence = sprintf("%08s", $noterurut_seqspt);
				$newTransactionSequence_spt = $noterurut_seqspt;
					

	  			//setelah data dibaca, masukkan ke tabel spt sql
	  			$query_spt = "INSERT INTO ttk_spt SET group_id = '$data_spt[group_id]', 
	  			record_type 					= 'SPT',
      			transaction_sequence 			= '$data_spt[transaction_sequence]',
      			seq_spt 						= '$newTransactionSequence_spt',
      			interested_party_number 		= '$spt_interested_party_number', 
	  			constant 						= '',
	  			pr_collection_share 			= '$dtjum_spt_pub_pr_ownership',
	  			mr_collection_share 			= '$dtjum_spt_pub_mr_ownership',
	  			sr_collection_share 			= '$dtjum_spt_pub_sr_ownership',
	  			inclusion_exclusion_indicator 	= 'I',
	  			tis_numeric_code 				= '$datatmp[tis_numeric_code]',
	  			shares_change 					= 'Y',
	  			sequence_number 				= '001'";
      			mysql_query($query_spt);
			//}

		$no++;		
    	}
		//----------------------------- SPT -------------------------

		
			
		$no = 1;
		$qtmp2 = mysql_query("SELECT * FROM ttk_tmp WHERE group_id = '$data_grh[group_id]' GROUP BY submitter_work ORDER BY submitter_work");
		$jumlahbaris = mysql_num_rows($qtmp2);
		while($datatmp2 = mysql_fetch_array($qtmp2))
		{	
			
			//PROGRESS BAR
			$barisXls = $jumlahbaris-1;
			$k = $no-1;
			$percent = @(intval($k/$barisXls * 100))."%";
			// mengupdate progress bar

			echo '<script language="javascript">
			document.getElementById("progress-bar").style.width="'.$percent.'";
			document.getElementById("progess-info").innerHTML="'.($no-1).' record ('.$percent.')";
			</script>';
			//AKHIR PROGRESS BAR


			//--------------------------------- SWR --------------------------
			$queryswr = mysql_query("SELECT * FROM ttk_tmp WHERE group_id = '$datatmp2[group_id]' AND submitter_work = '$datatmp2[submitter_work]' 
									GROUP BY writer_ipi_name_number ORDER BY writer_ipi_name_number");
    		while($data_swr = mysql_fetch_array($queryswr))
    		{

				$swr_interested_party_number = substr($data_swr['writer_ipi_name_number'], 0, 9);
				

				$querynwr = mysql_query("SELECT * FROM ttk_nwr WHERE group_id = '$data_swr[group_id]' AND submitter_work_number = '$data_swr[submitter_work]' 
										ORDER BY transaction_sequence");
    			while($datanwr = mysql_fetch_array($querynwr))
    			{

					//membuat seq_swr
					$query_seqswr=mysql_query("SELECT max(seq_swr) as max_seqswr FROM ttk_swr WHERE group_id = '$datanwr[group_id]' 
											AND transaction_sequence = '$datanwr[transaction_sequence]' ORDER BY seq_swr DESC LIMIT 1");
					$data_seqswr = mysql_fetch_array($query_seqswr);
					$seqswr = $data_seqswr['max_seqswr'];
					//$no = substr($seqswr, -8, 8);
					$nomor = $seqswr;
					//$no += 1;
	
					if($nomor == '')
					{
						$noterurut_seqswr = '1';	
					}
					elseif($nomor != '')
					{
						$noterurut_seqswr = $nomor + 1;
					}

					//$newTransactionSequence = sprintf("%08s", $noterurut_seqswr);
					$newTransactionSequence_swr = $noterurut_seqswr;
				
	  				
	  				$query_swr = "INSERT INTO ttk_swr SET group_id = '$data_swr[group_id]', 
	  				record_type 						= 'SWR',
      				transaction_sequence 				= '$datanwr[transaction_sequence]',
      				seq_swr 							= '$newTransactionSequence_swr',
	  				interested_party_number 			= '$swr_interested_party_number',
	  				writer_last_name 					= '".str_replace("'"," ",$data_swr['writer_last_name'])."',
	  				writer_first_name 					= '".str_replace("'"," ",$data_swr['writer_first_name'])."',
	  				writer_unknown_indicator 			= '',
	  				writer_designation_code 			= '$data_swr[writer_designation_code]',
	  				tax_id_number 						= '000000000',
	  				writer_ipi_name_number 				= '$data_swr[writer_ipi_name_number]',
	  				pr_affiliation_society 				= '$data_swr[pr_affiliation_society]',
	  				pr_ownership_share 					= '$data_swr[wrt_pr_ownership_share]',
	  				mr_affiliation_society 				= '$data_swr[mr_affiliation_society]',
	  				mr_ownership_share 					= '$data_swr[wrt_mr_ownership_share]',
	  				sr_affiliation_society 				= '$data_swr[sr_affiliation_society]',
	  				sr_ownership_share 					= '$data_swr[wrt_sr_ownership_share]',
	  				reversionary_indicator 				= '',
	  				first_recording_refusal_indicator 	= 'N',
	  				work_for_hire_indicator 			= '',
	  				filler 								= '',
	  				writer_ipi_base_number 				= '',
	  				personal_number 					= '',
	  				usa_licence_indicator 				= ''";
      				mysql_query($query_swr);
				}
    		}
			//--------------------------------- SWR ---------------------------------------
			
		

			
			//--------------------------------- SWT ---------------------------------------
			$queryswt = mysql_query("SELECT * FROM ttk_tmp WHERE group_id = '$datatmp2[group_id]' AND submitter_work = '$datatmp2[submitter_work]' 
									GROUP BY writer_ipi_name_number ORDER BY writer_ipi_name_number");
    		while($data_swt = mysql_fetch_array($queryswt))
    		{	

				$swt_interested_party_number = substr($data_swt['writer_ipi_name_number'], 0, 9);
				
				$querynwr = mysql_query("SELECT * FROM ttk_nwr WHERE group_id = '$data_swt[group_id]' AND submitter_work_number = '$data_swt[submitter_work]' 
										ORDER BY transaction_sequence");
    			while($datanwr = mysql_fetch_array($querynwr))
    			{

					//membuat seq_swt
					$query_seqswt=mysql_query("SELECT max(seq_swt) as max_seqswt FROM ttk_swt WHERE group_id = '$datanwr[group_id]' 
											AND transaction_sequence = '$datanwr[transaction_sequence]' ORDER BY seq_swt DESC LIMIT 1");
					$data_seqswt = mysql_fetch_array($query_seqswt);
					$seqswt = $data_seqswt['max_seqswt'];
					//$no = substr($seqswt, -8, 8);
					$nomor = $seqswt;
					//$no += 1;
	
					if($nomor == '')
					{
						$noterurut_seqswt = '1';	
					}
					elseif($nomor != '')
					{
						$noterurut_seqswt = $nomor + 1;
					}

					//$newTransactionSequence = sprintf("%08s", $noterurut_seqswt);
					$newTransactionSequence_swt = $noterurut_seqswt;
				
	  				
	  				$query_swt = "INSERT INTO ttk_swt SET group_id = '$data_swt[group_id]', 
	  				record_type 					= 'SWT',
      				transaction_sequence 			= '$datanwr[transaction_sequence]',
      				seq_swt 						= '$newTransactionSequence_swt',
	  				interested_party_number 		= '$swt_interested_party_number',
	  				pr_collection_share 			= '$data_swt[wrt_pr_collection_share]',
	  				mr_collection_share 			= '$data_swt[wrt_mr_collection_share]',
	  				sr_collection_share 			= '$data_swt[wrt_sr_collection_share]',
	  				inclusion_exclusion_indicator 	= 'I',
	  				tis_numeric_code 				= '$data_swt[tis_numeric_code]',
	  				shares_change 					= 'N',
	  				sequence_number 				= '001'";
      				mysql_query($query_swt);
				}
    		}
			//--------------------------------- SWT -----------------------------------------
			
	
			
			//--------------------------------- PWR -----------------------------------------
			$querypwr = mysql_query("SELECT * FROM ttk_tmp WHERE group_id = '$datatmp2[group_id]' AND submitter_work = '$datatmp2[submitter_work]' 
									GROUP BY writer_ipi_name_number ORDER BY writer_ipi_name_number");
    		while($data_pwr = mysql_fetch_array($querypwr))
    		{

				$pwr_publisher_interested_party_number = substr($data_pwr['publisher_ipi_name_number'], 0, 9);
				$pwr_writer_interested_party_number = substr($data_pwr['writer_ipi_name_number'], 0, 9);
				
				$querynwr = mysql_query("SELECT * FROM ttk_nwr WHERE group_id = '$data_pwr[group_id]' AND submitter_work_number = '$data_pwr[submitter_work]' 
										ORDER BY transaction_sequence");
    			while($datanwr = mysql_fetch_array($querynwr))
    			{

					//membuat seq_pwr
					$query_seqpwr=mysql_query("SELECT max(seq_pwr) as max_seqpwr FROM ttk_pwr WHERE group_id = '$datanwr[group_id]' 
											AND transaction_sequence = '$datanwr[transaction_sequence]' ORDER BY seq_pwr DESC LIMIT 1");
					$data_seqpwr = mysql_fetch_array($query_seqpwr);
					$seqpwr = $data_seqpwr['max_seqpwr'];
					//$no = substr($seqpwr, -8, 8);
					$nomor = $seqpwr;
					//$no += 1;
	
					if($nomor == '')
					{
						$noterurut_seqpwr = '1';	
					}
					elseif($nomor != '')
					{
						$noterurut_seqpwr = $nomor + 1;
					}

					//$newTransactionSequence = sprintf("%08s", $noterurut_seqpwr);
					$newTransactionSequence_pwr = $noterurut_seqpwr;
				

	  				$query_pwr = "INSERT INTO ttk_pwr SET group_id = '$data_pwr[group_id]', 
	  				record_type 						= 'PWR',
      				transaction_sequence 				= '$datanwr[transaction_sequence]',
      				seq_pwr 							= '$newTransactionSequence_pwr',
	  				publisher_interested_party_number 	= '$pwr_publisher_interested_party_number',
	  				publisher_name 						= '".str_replace("'"," ",$data_pwr['publisher_name'])."',
	  				submitter_agreement_number 			= '',
	  				saan 								= '',
	  				writer_interested_party_number 		= '$pwr_writer_interested_party_number'";
      				mysql_query($query_pwr);
				}
    		}
			//--------------------------------- PWR -----------------------------------------

		$no++;		
		}
	



		/*
		//--------------------------------- OWR -----------------------------------------
		$query_owr = mysql_query("SELECT * FROM ttk_tmp WHERE group_id = '$data_nwr[group_id]' AND submitter_work = '$data_nwr[submitter_work]' 
								ORDER BY submitter_work");
    	while($data_owr = mysql_fetch_array($query_owr))
    	{

			$owr_writer_interested_party_number = substr($data_pwr['writer_ipi_name_number'], 9);
				
			//membuat seq_owr
			$query_seqowr=mysql_query("SELECT max(seq_owr) as max_seqowr FROM ttk_owr ORDER BY seq_owr DESC LIMIT 1");
			$data_seqowr = mysql_fetch_array($query_seqowr);
			$seqowr = $data_seqowr['max_seqowr'];
			//$no = substr($seqowr, -8, 8);
			$nomor = (int) $seqowr;
			//$no += 1;
	
			if($nomor = '')
			{
				$noterurut_seqowr = '1';	
			}
			elseif($nomor != '')
			{
				$noterurut_seqowr = $nomor + 1;
			}

			//$newTransactionSequence = sprintf("%08s", $noterurut_seqowr);
			$newTransactionSequence_owr = $noterurut_seqowr;
				

	  			$query_owr = "INSERT INTO ttk_owr (group_id, 
	  			record_type,
      			transaction_sequence,
      			seq_owr,
	  			interested_party_number,
	  			writer_last_name,
	  			writer_first_name, 
	  			writer_unknown_indicator,
	  			writer_designation_code,
	  			tax_id_number,
	  			writer_ipi_name_number,
	  			pr_affiliation_society,
	  			pr_ownership_share,
	  			mr_affiliation_society,
	  			mr_ownership_share,
	  			sr_affiliation_society,
	  			sr_ownership_share,
	  			reversionary_indicator,
	  			first_recording_refusal_indicator,
	  			work_for_hire_indicator,
	  			filler,
	  			writer_ipi_base_number,
	  			personal_number,
	  			usa_licence_indicator)values('$data_owr[group_id]', 
	  			'OWR',
      			'$newTransactionSequence',
      			'$newTransactionSequence_owr',
	  			'$owr_writer_interested_party_number',
	  			'".str_replace("'"," ",$data_owr['writer_last_name'])."',
	  			'".str_replace("'"," ",$data_owr['writer_first_name'])."', 
	  			'$owr_writer_unknown_indicator',
	  			'$data_owr[writer_designation_code]',
	  			'$owr_tax_id_number',
	  			'$data_owr[writer_ipi_name_number]',
	  			'$data_owr[pr_affiliation_society]',
	  			'$owr_pr_share',
	  			'$data_owr[mr_affiliation_society]',
	  			'$owr_mr_share',
	  			'$data_owr[sr_affiliation_society]',
	  			'$owr_sr_share',
	  			'$owr_reversionary_indicator',
	  			'$owr_first_recording_refusal_indicator',
	  			'$owr_work_for_hire_indicator',
	  			'$owr_filler',
	  			'$owr_writer_ipi_base_number',
	  			'$owr_personal_number',
	  			'$owr_usa_licence_indicator')";
      			mysql_query($query_owr);
	  			
    	}
		//----------------------------------- OWR ----------------------------------------------
		*/


	} //Akhir GRH


	
	//hapus file xls yang udah dibaca
  	unlink($_FILES['filemetadata']['name']);
	
	echo '<META HTTP-EQUIV="Refresh" Content="0; URL=aksi_upload_metadata_cwr_tiktok_2.php?page=two">';
	exit;

}
else
{
	/*
	echo '<script>alert("Proses import gagal !!!");
	window.location.href="../../index.php?page=tiktok_cwr"</script>';
	*/

	//sebelum redirect, daftarkan session pesan
    $_SESSION['gagal'] = 'Proses import gagal !!!';
	echo '<META HTTP-EQUIV="Refresh" Content="0; URL=../../index.php?page=tiktok_cwr">';
	exit;

}
 
?>

<!-- jQuery 2.2.3 -->
<script src="../../plugins/jQuery/jquery-2.2.3.min.js"></script>
<!-- Bootstrap 3.3.6 -->
<script src="../../bootstrap/js/bootstrap.min.js"></script>
<!-- Select2 -->
<script src="../../plugins/select2/select2.full.min.js"></script>
<!-- DataTables -->
<script src="../../plugins/datatables/jquery.dataTables.min.js"></script>
<script src="../../plugins/datatables/dataTables.bootstrap.min.js"></script>
<!-- SlimScroll -->
<script src="../../plugins/slimScroll/jquery.slimscroll.min.js"></script>
<!-- FastClick -->
<script src="../../plugins/fastclick/fastclick.js"></script>
<!-- AdminLTE App -->
<script src="../../dist/js/app.min.js"></script>
<!-- AdminLTE for demo purposes -->
<script src="../../dist/js/demo.js"></script>

</body>
</html>