<?php
session_start();
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 spy_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++)
	{
		//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 spy_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_society,							
		sr_society, 						
		ipi_base_number, 					
		tis_numeric_code)values('$tmp_group_id',
		'$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', 
		'$tmp_writer_last_name', 					
		'$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);

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


	
	//----------------------------- GRH -------------------------
	$query_grh_group = mysql_query("SELECT * FROM spy_tmp GROUP BY group_id ORDER BY group_id");
    while($data_grh_group = mysql_fetch_array($query_grh_group))
    {
	  	/*membuat batch request*/
		//$char = 'SC';
		//$query=mysql_query("SELECT max(code_song) as max_kode FROM masterkatalog WHERE code_song LIKE '$char%' ORDER BY code_song DESC LIMIT 1");
		$query=mysql_query("SELECT max(batch_request) as max_kode FROM spy_grh ORDER BY batch_request DESC LIMIT 1");
		$data = mysql_fetch_array($query);
		$batchrequest = $data['max_kode'];
		$no = substr($batchrequest, -10, 10);
		$nomor = (int) $no;
		//$no += 1;
		
		if($nomor = '')
		{
			$noterurut = '0000000000';	
		}
		elseif($nomor != '')
		{
			$noterurut = $nomor + 1;
		}

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

	  	/*setelah data dibaca, masukkan ke tabel grh sql*/
	  	$query_grh_spy = "INSERT INTO spy_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);
    }
	//----------------------------- GRH -------------------------

	
	
	$query_grh = mysql_query("SELECT * FROM spy_grh ORDER BY group_id ASC");
    while($data_grh = mysql_fetch_array($query_grh))
    {
		//----------------------------- NWR -------------------------
		$query_nwr = mysql_query("SELECT * FROM spy_tmp WHERE group_id = '$data_grh[group_id]' GROUP BY submitter_work ORDER BY submitter_work");
    	while($data_nwr = mysql_fetch_array($query_nwr))
    	{
	  		/*membuat transaction_sequence*/
			$query=mysql_query("SELECT max(transaction_sequence) as max_kode FROM spy_nwr ORDER BY transaction_sequence DESC LIMIT 1");
			$data = mysql_fetch_array($query);
			$transactionsequence = $data['max_kode'];
			//$no = substr($transactionsequence, -8, 8);
			$nomor = (int) $transactionsequence;
			//$no += 1;
		
			if($nomor = '')
			{
				$noterurut = '00000000';	
			}
			elseif($nomor != '')
			{
				$noterurut = $nomor + 1;
			}

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

				
			//setelah data dibaca, masukkan ke tabel nwr sql
	  		$query_nwr = "INSERT INTO spy_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 -------------------------
				$query_spu = mysql_query("SELECT * FROM spy_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($query_spu))
    			{
					$q_spu_pub_pr_ownership = mysql_query("SELECT group_id, submitter_work, publisher_ipi_name_number, SUM(pub_pr_ownership_share) 
														AS jumlah_pub_pr_ownership_share FROM spy_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);


					$q_spu_pub_mr_ownership = mysql_query("SELECT group_id, submitter_work, publisher_ipi_name_number, SUM(pub_mr_ownership_share) 
														AS jumlah_pub_mr_ownership_share FROM spy_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);


					$q_spu_pub_sr_ownership = mysql_query("SELECT group_id, submitter_work, publisher_ipi_name_number, SUM(pub_sr_ownership_share) 
														AS jumlah_pub_sr_ownership_share FROM spy_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);

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

					/*membuat seq_spu*/
					$query_seqspu=mysql_query("SELECT max(seq_spu) as max_seqspu FROM spy_spu 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 = (int) $seqspu;
					//$no += 1;
		
					if($nomor = '')
					{
						$noterurut_seqspu = '1';	
					}
					elseif($nomor != '')
					{
						$noterurut_seqspu = $nomor + 1;
					}

					//$newTransactionSequence = sprintf("%08s", $noterurut_seqspu);
					$newTransactionSequence_spu = $noterurut_seqspu;
					/*membuat seq_spu*/

					
					//setelah data dibaca, masukkan ke tabel spu sql
	  				$query_spu = "INSERT INTO spy_spu SET group_id = '$data_spu[group_id]', 
	  				record_type 						= 'SPU',
      				transaction_sequence 				= '$newTransactionSequence',
      				seq_spu 							= '$newTransactionSequence_spu',
      				publisher_sequence_number 			= '01',
      				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_share 							= '$dtjum_spu_pub_pr_ownership[jumlah_pub_pr_ownership_share]',
	  				mr_affiliation_society 				= '$data_spu[mr_affiliation_society]',
	  				mr_share 							= '$dtjum_spu_pub_mr_ownership[jumlah_pub_mr_ownership_share]',
	  				sr_affiliation_society 				= '$data_spu[sr_affiliation_society]',
	  				sr_share 							= '$dtjum_spu_pub_sr_ownership[jumlah_pub_sr_ownership_share]',
	  				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 -------------------------
	
	
	
				//----------------------------- SPT -------------------------
				$query_spu = mysql_query("SELECT * FROM spy_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($query_spu))
    			{
					$q_spu_pub_pr_ownership = mysql_query("SELECT group_id, submitter_work, publisher_ipi_name_number, SUM(pub_pr_ownership_share) 
														AS jumlah_pub_pr_ownership_share FROM spy_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);


					$q_spu_pub_mr_ownership = mysql_query("SELECT group_id, submitter_work, publisher_ipi_name_number, SUM(pub_mr_ownership_share) 
														AS jumlah_pub_mr_ownership_share FROM spy_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);


					$q_spu_pub_sr_ownership = mysql_query("SELECT group_id, submitter_work, publisher_ipi_name_number, SUM(pub_sr_ownership_share) 
														AS jumlah_pub_sr_ownership_share FROM spy_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);
      
	  
	  				if($spt_record_type == 'SPT')
	  				{
	  					//setelah data dibaca, masukkan ke tabel spt sql
	  					$query_spt = "INSERT INTO spy_spt (group_id, 
	  					record_type,
      					transaction_sequence,
      					seq_spt,
      					interested_party_number, 
	  					constant,
	  					pr_share,
	  					mr_share,
	  					sr_share,
	  					inclusion_exclusion_indicator,
	  					tis_numeric_code,
	  					shares_change,
	  					sequence_number)values('$spt_group_id', 
	  					'$spt_record_type',
      					'$spt_transaction_sequence',
      					'$spt_seq_spt',
      					'$spt_interested_party_number',
      					'$spt_constant', 
	  					'$spt_pr_share',
	  					'$spt_mr_share',
	  					'$spt_sr_share',
	  					'$spt_inclusion_exclusion_indicator',
	  					'$spt_tis_numeric_code',
	  					'$spt_shares_change',
	  					'$spt_sequence_number')";
      					mysql_query($query_spt);
	  				}
    			}
				//----------------------------- SPT -------------------------
	
	
	
			//-------- import SWR dari sheet 5 ----------
			// baca jumlah baris dalam sheet 5
			$jmlbaris_swr = $data->rowcount(5);
	
			//import data excel mulai baris ke-2 (karena tabel xls ada header pada baris 1)
    		for ($i=2; $i<=$jmlbaris_swr; $i++)
    		{
	  			//membaca data (kolom ke-1 sd terakhir)
	  			$swr_group_id		         				= $data->val($i, 1, 5);
	  			$swr_record_type		         			= $data->val($i, 2, 5);
      			$swr_transaction_sequence         		= $data->val($i, 3, 5);
      			$swr_seq_swr 								= $data->val($i, 4, 5);
      			$swr_interested_party_number  			= $data->val($i, 5, 5);
	  			$swr_writer_last_name 					= $data->val($i, 6, 5);
	  			$swr_writer_first_name 					= $data->val($i, 7, 5);
	  			$swr_writer_unknown_indicator 			= $data->val($i, 8, 5);
	  			$swr_writer_designation_code				= $data->val($i, 9, 5);
	  			$swr_tax_id_number						= $data->val($i, 10, 5);
	  			$swr_writer_ipi_name_number				= $data->val($i, 11, 5);
	  			$swr_pr_affiliation_society				= $data->val($i, 12, 5);
	  			$swr_pr_share								= $data->val($i, 13, 5);
	  			$swr_mr_affiliation_society				= $data->val($i, 14, 5);
	  			$swr_mr_share								= $data->val($i, 15, 5);
	  			$swr_sr_affiliation_society				= $data->val($i, 16, 5);
	  			$swr_sr_share								= $data->val($i, 17, 5);
	  			$swr_reversionary_indicator				= $data->val($i, 18, 5);
	  			$swr_first_recording_refusal_indicator	= $data->val($i, 19, 5);
	  			$swr_work_for_hire_indicator				= $data->val($i, 20, 5);
	  			$swr_filler								= $data->val($i, 21, 5);
	  			$swr_writer_ipi_base_number				= $data->val($i, 22, 5);
	  			$swr_personal_number						= $data->val($i, 23, 5);
	  			$swr_usa_licence_indicator				= $data->val($i, 24, 5);
	  
	  
	  			if($swr_record_type == 'SWR')
	  			{
	  				//setelah data dibaca, masukkan ke tabel swr sql
	  				$query_swr = "INSERT INTO spy_swr (group_id, 
	  				record_type,
      				transaction_sequence,
      				seq_swr,
	  				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_share,
	  				mr_affiliation_society,
	  				mr_share,
	  				sr_affiliation_society,
	  				sr_share,
	  				reversionary_indicator,
	  				first_recording_refusal_indicator,
	  				work_for_hire_indicator,
	  				filler,
	  				writer_ipi_base_number,
	  				personal_number,
	  				usa_licence_indicator)values('$swr_group_id', 
	  				'$swr_record_type',
      				'$swr_transaction_sequence',
      				'$swr_seq_swr',
	  				'$swr_interested_party_number',
	  				'".str_replace("'"," ",$swr_writer_last_name)."',
	  				'".str_replace("'"," ",$swr_writer_first_name)."',
	  				'$swr_writer_unknown_indicator',
	  				'$swr_writer_designation_code',
	  				'$swr_tax_id_number',
	  				'$swr_writer_ipi_name_number',
	  				'$swr_pr_affiliation_society',
	  				'$swr_pr_share',
	  				'$swr_mr_affiliation_society',
	  				'$swr_mr_share',
      				'$swr_sr_affiliation_society',
      				'$swr_sr_share', 
	  				'$swr_reversionary_indicator',
	  				'$swr_first_recording_refusal_indicator',
	  				'$swr_work_for_hire_indicator',
	  				'$swr_filler',
	  				'$swr_writer_ipi_base_number',
	  				'$swr_personal_number',
	  				'$swr_usa_licence_indicator')";
      				mysql_query($query_swr);
	  			}
    		}
			//-------- akhir import SWR dari sheet 5 ----------
	
	
	
			//-------- import SWT dari sheet 6 ----------
			// baca jumlah baris dalam sheet 6
			$jmlbaris_swt = $data->rowcount(6);
	
			//import data excel mulai baris ke-2 (karena tabel xls ada header pada baris 1)
    		for ($i=2; $i<=$jmlbaris_swt; $i++)
    		{
	  			//membaca data (kolom ke-1 sd terakhir)
	  			$swt_group_id		         			= $data->val($i, 1, 6);
      			$swt_record_type		         		= $data->val($i, 2, 6);
	  			$swt_transaction_sequence         	= $data->val($i, 3, 6);
      			$swt_seq_swt 							= $data->val($i, 4, 6);
      			$swt_interested_party_number  		= $data->val($i, 5, 6);
	  			$swt_pr_share 						= $data->val($i, 6, 6);
	  			$swt_mr_share 						= $data->val($i, 7, 6);
	  			$swt_sr_share 						= $data->val($i, 8, 6);
	  			$swt_inclusion_exclusion_indicator	= $data->val($i, 9, 6);
	  			$swt_tis_numeric_code					= $data->val($i, 10, 6);
	  			$swt_shares_change					= $data->val($i, 11, 6);
	  			$swt_sequence_number					= $data->val($i, 12, 6);	

	  
	  			if($swt_record_type == 'SWT')
	  			{
	  				//setelah data dibaca, masukkan ke tabel swt sql
	  				$query_swt = "INSERT INTO spy_swt (group_id, 
	  				record_type,
      				transaction_sequence,
      				seq_swt,
	  				interested_party_number,
	  				pr_share,
	  				mr_share,
	  				sr_share,
	  				inclusion_exclusion_indicator,
	  				tis_numeric_code,
	  				shares_change,
	  				sequence_number)values('$swt_group_id', 
	  				'$swt_record_type',
      				'$swt_transaction_sequence',
      				'$swt_seq_swt',
	  				'$swt_interested_party_number',
	  				'$swt_pr_share',
	  				'$swt_mr_share',
	  				'$swt_sr_share',
	  				'$swt_inclusion_exclusion_indicator',
	  				'$swt_tis_numeric_code',
	  				'$swt_shares_change',
	  				'$swt_sequence_number')";
      				mysql_query($query_swt);
	  			}
    		}
			//-------- akihir import SWT dari sheet 6 ----------
	
	
	
	
			//-------- import PWR dari sheet 7 ----------
			// baca jumlah baris dalam sheet 7
			$jmlbaris_pwr = $data->rowcount(7);
	
			//import data excel mulai baris ke-2 (karena tabel xls ada header pada baris 1)
    		for ($i=2; $i<=$jmlbaris_pwr; $i++)
    		{
	  			//membaca data (kolom ke-1 sd terakhir)
	  			$pwr_group_id		         				= $data->val($i, 1, 7);
      			$pwr_record_type		         			= $data->val($i, 2, 7);
	  			$pwr_transaction_sequence         		= $data->val($i, 3, 7);
      			$pwr_seq_pwr 								= $data->val($i, 4, 7);
      			$pwr_publisher_interested_party_number	= $data->val($i, 5, 7);
	  			$pwr_publisher_name 						= $data->val($i, 6, 7);
	  			$pwr_submitter_agreement_number 			= $data->val($i, 7, 7);
	  			$pwr_saan 								= $data->val($i, 8, 7);
	  			$pwr_writer_interested_party_number		= $data->val($i, 9, 7);

	  
	  			if($pwr_record_type == 'PWR')
	  			{
	  				//setelah data dibaca, masukkan ke tabel pwr sql
	  				$query_pwr = "INSERT INTO spy_pwr (group_id, 
	  				record_type,
      				transaction_sequence,
      				seq_pwr,
	  				publisher_interested_party_number,
	  				publisher_name,
	  				submitter_agreement_number,
	  				saan,
	  				writer_interested_party_number)values('$pwr_group_id', 
	  				'$pwr_record_type',
      				'$pwr_transaction_sequence',
      				'$pwr_seq_pwr',
	  				'$pwr_publisher_interested_party_number',
	  				'".str_replace("'"," ",$pwr_publisher_name)."',
	  				'$pwr_submitter_agreement_number',
	  				'$pwr_saan',
	  				'$pwr_writer_interested_party_number')";
      				mysql_query($query_pwr);
	  			}
    		}
			//-------- akhir import PWR dari sheet 7 ----------
	
	
	
	
			//-------- import OWR dari sheet 8 ----------
			// baca jumlah baris dalam sheet 8
			$jmlbaris_owr = $data->rowcount(8);
	
			//import data excel mulai baris ke-2 (karena tabel xls ada header pada baris 1)
    		for ($i=2; $i<=$jmlbaris_owr; $i++)
    		{
	  			//membaca data (kolom ke-1 sd terakhir)
	  			$owr_group_id         					= $data->val($i, 1, 8);
	  			$owr_record_type         					= $data->val($i, 2, 8);
      			$owr_transaction_sequence         		= $data->val($i, 3, 8);
      			$owr_seq_owr 								= $data->val($i, 4, 8);
      			$owr_interested_party_number  			= $data->val($i, 5, 8);
	  			$owr_writer_last_name 					= $data->val($i, 6, 8);
	  			$owr_writer_first_name 					= $data->val($i, 7, 8);
	  			$owr_writer_unknown_indicator				= $data->val($i, 8, 8);
	  			$owr_writer_designation_code 				= $data->val($i, 9, 8);
	  			$owr_tax_id_number						= $data->val($i, 10, 8);
	  			$owr_writer_ipi_name_number				= $data->val($i, 11, 8);
	  			$owr_pr_affiliation_society				= $data->val($i, 12, 8);
	  			$owr_pr_share								= $data->val($i, 13, 8);
	  			$owr_mr_affiliation_society				= $data->val($i, 14, 8);
	  			$owr_mr_share								= $data->val($i, 15, 8);
	  			$owr_sr_affiliation_society				= $data->val($i, 16, 8);
	  			$owr_sr_share								= $data->val($i, 17, 8);
	  			$owr_reversionary_indicator				= $data->val($i, 18, 8);
	  			$owr_first_recording_refusal_indicator	= $data->val($i, 19, 8);
	  			$owr_work_for_hire_indicator				= $data->val($i, 20, 8);
	  			$owr_filler								= $data->val($i, 21, 8);
	  			$owr_writer_ipi_base_number				= $data->val($i, 22, 8);
	  			$owr_personal_number						= $data->val($i, 23, 8);
	  			$owr_usa_licence_indicator				= $data->val($i, 24, 8);

	    
	  			//setelah data dibaca, masukkan ke tabel owr sql
	  			if($owr_record_type == 'OWR')
	  			{
	  				$query_owr = "INSERT INTO spy_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_share,
	  				mr_affiliation_society,
	  				mr_share,
	  				sr_affiliation_society,
	  				sr_share,
	  				reversionary_indicator,
	  				first_recording_refusal_indicator,
	  				work_for_hire_indicator,
	  				filler,
	  				writer_ipi_base_number,
	  				personal_number,
	  				usa_licence_indicator)values('$owr_group_id', 
	  				'$owr_record_type',
      				'$owr_transaction_sequence',
      				'$owr_seq_owr',
	  				'$owr_interested_party_number',
	  				'".str_replace("'"," ",$owr_writer_last_name)."',
	  				'".str_replace("'"," ",$owr_writer_first_name)."', 
	  				'$owr_writer_unknown_indicator',
	  				'$owr_writer_designation_code',
	  				'$owr_tax_id_number',
	  				'$owr_writer_ipi_name_number',
	  				'$owr_pr_affiliation_society',
	  				'$owr_pr_share',
	  				'$owr_mr_affiliation_society',
	  				'$owr_mr_share',
	  				'$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);
	  			}
    		}
			//-------- akhir import OWR dari sheet 8 ----------


			//-------- import SEQUENCE dari sheet 9 ----------
			// baca jumlah baris dalam sheet 9
			$jmlbaris_sequence = $data->rowcount(9);
 	
			//import data excel mulai baris ke-2 (karena tabel xls ada header pada baris 1)
    		for ($i=2; $i<=$jmlbaris_sequence; $i++)
    		{
	  			//membaca data (kolom ke-1 sd terakhir)
	  			$sequence_group_id         		= $data->val($i, 1, 9); 
	  			$sequence_record_type         	= $data->val($i, 2, 9);
      			$sequence_transaction_sequence    = $data->val($i, 3, 9);
      			$sequence_record_sequence 		= $data->val($i, 4, 9);
	  			$sequence_seq_record_type    		= $data->val($i, 5, 9);

      
	  			//setelah data dibaca, masukkan ke tabel sequence sql
	  			$query_sequence = "INSERT INTO spy_sequence (group_id, 
	  			record_type,
      			transaction_sequence, 
	  			record_sequence, 
	  			seq_record_type)values('$sequence_group_id', 
	  			'$sequence_record_type',
      			'$sequence_transaction_sequence', 
	  			'$sequence_record_sequence', 
	  			'$sequence_seq_record_type')";
      			mysql_query($query_sequence);
    		}
			//-------- akhir import SEQUENCE dari sheet 11 ----------


		}
		//----------------------------- AKHIR NWR -------------------------
	
	

		//-------- import GRT dari sheet 10 ----------
		// baca jumlah baris dalam sheet 10
		$jmlbaris_grt = $data->rowcount(10);
 	
		//import data excel mulai baris ke-2 (karena tabel xls ada header pada baris 1)
    	for ($i=2; $i<=$jmlbaris_grt; $i++)
    	{
	  		//membaca data (kolom ke-1 sd terakhir)
	  		$grt_record_type         	= $data->val($i, 1, 10);
      		$grt_group_id         	= $data->val($i, 2, 10);
      		$grt_transaction_count 	= $data->val($i, 3, 10);
      		$grt_record_count  		= $data->val($i, 4, 10);
	  		$grt_currency_indicator 	= $data->val($i, 5, 10);
	  		$grt_total_monetary_value = $data->val($i, 6, 10);

	    
	  		//setelah data dibaca, masukkan ke tabel grt sql
	  		$query_grt = "INSERT INTO spy_grt (record_type,
      		group_id,
      		transaction_count,
	  		record_count,
	  		currency_indicator,
	  		total_monetary_value)values('$grt_record_type',
      		'$grt_group_id',
      		'$grt_transaction_count',
	  		'$grt_record_count',
	  		'$grt_currency_indicator',
	  		'$grt_total_monetary_value')";
      		mysql_query($query_grt);
    	}
		//-------- akhir import GRT dari sheet 10 ----------
	
	
	
		//-------- import TRL dari sheet 11 ----------
		// baca jumlah baris dalam sheet 11
		$jmlbaris_trl = $data->rowcount(11);
 	
		//import data excel mulai baris ke-2 (karena tabel xls ada header pada baris 1)
    	for ($i=2; $i<=$jmlbaris_trl; $i++)
    	{
	  		//membaca data (kolom ke-1 sd terakhir)
	  		$trl_record_type         	= $data->val($i, 1, 11);
      		$trl_group_id         	= $data->val($i, 2, 11);
      		$trl_transaction_count 	= $data->val($i, 3, 11);
      		$trl_record_count  		= $data->val($i, 4, 11);

      
	  		//setelah data dibaca, masukkan ke tabel trl sql
	  		$query_trl = "INSERT INTO spy_trl (record_type,
      		group_count,
      		transaction_count,
	  		record_count)values('$trl_record_type',
      		'$trl_group_id',
      		'$trl_transaction_count',
	  		'$trl_record_count')";
      		mysql_query($query_trl);
    	}
		//-------- akhir import TRL dari sheet 11 ----------
	
	}//Akhir GRH
	

	
	//hapus file xls yang udah dibaca
  	unlink($_FILES['filemetadata']['name']);
	
	echo '<script>alert("Proses import selesai !!!");
	window.location.href="../../index.php?page=spotify_1_cwr"</script>';

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

}
 
?>