<?php

include '../../../config/koneksi.php';
session_start();
   mysqli_query($koneksi, "delete from renja ");
   mysqli_query($koneksi, "delete from sumber_dana");
   mysqli_query($koneksi, "delete from urusan");
   mysqli_query($koneksi, "delete from skpd");
   mysqli_query($koneksi, "delete from bidang_urusan");
   mysqli_query($koneksi, "delete from sub_skpd");
   mysqli_query($koneksi, "delete from program");
   mysqli_query($koneksi, "delete from kegiatan");
   mysqli_query($koneksi, "delete from sub_kegiatan");
   mysqli_query($koneksi, "delete from rekening");
   mysqli_query($koneksi, "delete from bidang_skpd");

		require('spreadsheet-reader-master/php-excel-reader/excel_reader2.php');
		require('spreadsheet-reader-master/SpreadsheetReader.php');

		//upload data excel kedalam folder uploads
		$target_dir = "uploads/".basename($_FILES['file']['name']);
		
		move_uploaded_file($_FILES['file']['tmp_name'],$target_dir);

		$Reader = new SpreadsheetReader($target_dir);

		foreach ($Reader as $Key => $Row)
		{
			// import data excel mulai baris ke-2 (karena ada header pada baris 1)
			if ($Key < 1) continue;
			$rowr1 = str_replace(',00', '', $Row[22]);	
			$rowr2 = str_replace('.', '', $rowr1);
			if($Row[0] == 'Total'){

			}
			else
			{


				    $no = $Row[0] ;
                $tahun =  $Row[1];

                $kode_urusan = $Row[2] ;
                $urusan = $Row[3];
                
                $kode_skpd = $Row[4] ;
                $skpd = $Row[5];
                
                $kode_sub_skpd = $Row[6] ;
                $sub_skpd = $Row[7];
                
                $kode_bidang_urusan = $Row[8] ;
                $bidang_urusan = $Row[9];

                
                $kode_program = $Row[10] ;
                $program = $Row[11];
             
                $kode_kegiatan = $Row[12];
                $kegiatan = $Row[13];

                $kode_sub_kegiatan = $Row[14] ;
                $sub_kegiatan = $Row[15];
               
                $kode_sumber_dana = $Row[16];
                $sumber_dana = $Row[17];

                $kode_rekening = $Row[18];
                $rekening = $Row[19];

              
                
                

                $rincian = str_replace(',', '.',  $Row[22]) ;
                //echo($rincian);

                $kode_bidang_skpd =$kode_urusan.".". $kode_bidang_urusan ."." . $kode_skpd .".".$kode_sub_skpd;
               

mysqli_query($koneksi, "replace INTO sumber_dana (sumber_dana,kode_sumber_dana) VALUES ('$sumber_dana','$kode_sumber_dana')");
mysqli_query($koneksi, "replace INTO urusan (kode_urusan,urusan) VALUES ('$kode_urusan','$urusan')");
mysqli_query($koneksi, "replace INTO skpd (skpd,kode_skpd) VALUES ('$skpd','$kode_skpd')");
mysqli_query($koneksi, "replace INTO bidang_urusan (bidang_urusan,kode_urusan,kode_bidang_urusan) VALUES ('$bidang_urusan','$kode_urusan','$kode_bidang_urusan')");
mysqli_query($koneksi, "replace INTO sub_skpd (sub_skpd,kode_skpd,kode_sub_skpd) VALUES ('$sub_skpd','$kode_skpd','$kode_sub_skpd')");
mysqli_query($koneksi, "replace INTO program (program,kode_bidang_urusan,kode_program) VALUES ('$program','$kode_bidang_urusan','$kode_program')");
mysqli_query($koneksi, "replace INTO kegiatan (kode_kegiatan,kode_program,kegiatan) VALUES ('$kode_kegiatan','$kode_program','$kegiatan')");
mysqli_query($koneksi, "replace INTO sub_kegiatan (kode_sub_kegiatan,kode_kegiatan,sub_kegiatan) VALUES ('$kode_sub_kegiatan','$kode_kegiatan','$sub_kegiatan')");
mysqli_query($koneksi, "replace INTO rekening (kode_rekening,rekening) VALUES ('$kode_rekening','$rekening')");
mysqli_query($koneksi, "replace INTO bidang_skpd (kode_bidang_skpd,kode_bidang_urusan,kode_skpd,kode_sub_skpd,kode_urusan) VALUES ('$kode_bidang_skpd','$kode_bidang_urusan','$kode_skpd','$kode_sub_skpd','$kode_urusan')");

			$query=mysqli_query($koneksi,"insert INTO renja 
(id_renja
,no
,tahun
,kode_urusan
,urusan
,kode_skpd
,skpd
,kode_sub_skpd
,sub_skpd
,kode_bidang_urusan
,bidang_urusan
,kode_program
,program
,kode_kegiatan
,kegiatan
,kode_sub_kegiatan
,sub_kegiatan
,kode_sumber_dana
,sumber_dana
,kode_rekening
,rekening
,rincian
) 
 VALUES (
 				'".$Row[0]."',
			    '".$Row[0]."', 
			    '".$Row[1]."',
			    '".$Row[2]."',
			    '".$Row[3]."',
			    '".$Row[4]."',
			    '".$Row[5]."',
			    '".$Row[6]."',
			    '".$Row[7]."',
			    '".$Row[8]."',
			    '".$Row[9]."',
			    '".$Row[10]."',
			    '".$Row[11]."',
			    '".$Row[12]."',
			    '".$Row[13]."',
			    '".$Row[14]."',
			    '".$Row[15]."',
			    '".$Row[16]."',
			    '".$Row[17]."',
			    '".$Row[18]."',
			    '".$Row[19]."',
			    '".$rowr2."'
			   
			)");
		}
		}
		if ($query) {
				echo "Import data berhasil";
				unlink($target_dir);
			}else{
				echo mysql_error();
			}
	
	?>