<?php if(isset($_SESSION['iduser'])){?>
<div class="block-header">
     <h2>
          <ol class="breadcrumb breadcrumb-bg-teal">
                   <li><a href="index.php?home"><i class="material-icons">home</i> Home</a></li>
                   <li><a href="index.php?penarikan"><i class="material-icons">vertical_align_top</i> Penarikan</a></li>
                   <li class="active">Penarikan Add - Upload</li>
          </ol>
     </h2>
</div>			
<?php
if(isset($_GET['del']))
{
	$del=noninjection($koneksi,$_GET['del']);
	$qry_del=mysqli_query($koneksi,"DELETE FROM `tb_rekening_tmp` WHERE (id='".$del."') and (iduser='".$_SESSION['iduser']."')");
						if($qry_del){echo ' <div class="alert bg-green alert-dismissible" role="alert">
                        <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                        <strong>Well done!</strong> Hapus Data Sukses.
                         </div>';
							
							}else {
							echo '<div class="alert bg-pink alert-dismissible" role="alert">
                                <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                               <strong>Warning!</strong> Hapus Data Gagal.
                            </div>';
							}
}

if (isset($_POST["submit_finish"])) 
{
	$pin=noninjection($koneksi,$_POST['pin_finish']);
	if(isset($_POST['setujui']))
	{
	$setujui=noninjection($koneksi,$_POST['setujui']);
	$task="34567fghjkl;FGHJK09865fghjs12345@#$@#$!@#";
			
	$pin=md5($pin);
	$pin_encript=md5($pin.$task);
		
	$qry_pin=mysqli_query($koneksi,"SELECT * FROM `tb_user` WHERE (email='".$_SESSION['email']."') AND (password='".$_SESSION['password']."') AND (pin='".$pin_encript."')");
	$N_pin=mysqli_num_rows($qry_pin);
	if($N_pin<>0)
	{
	
					$qry_fix=mysqli_query($koneksi,"SELECT * FROM `tb_rekening_tmp` WHERE (iduser='".$_SESSION['iduser']."')  AND (in_out='out') AND  (idanggota IN (SELECT idanggota FROM `tb_anggota`))");
					while($row_fix=mysqli_fetch_array($qry_fix)){
					
									$qry_rek=mysqli_query($koneksi,"SELECT * FROM `tb_rekening`  WHERE idanggota='".$row_fix['idanggota']."' order by id desc limit 1");
									$row_rek=mysqli_fetch_array($qry_rek);
									if(mysqli_num_rows($qry_rek)<>0){
									$saldo=$row_rek['saldo']-$row_fix['nominal'];
									} else {$saldo=$nominal;}
						if($saldo > 0)
					{
					$res=mysqli_query($koneksi,"INSERT INTO `tb_rekening` (`idanggota`, `keterangan`, `in_out`, `nominal`, `saldo`, `reff`, `reff2`,`iduser`, `tgl`) VALUES ('".$row_fix['idanggota']."', '".$row_fix['keterangan']."', 'out', '".$row_fix['nominal']."', '".$saldo."', '-', '-','".$_SESSION['iduser']."', current_timestamp());");
					mysqli_query($koneksi,"DELETE FROM `tb_rekening_tmp` WHERE (iduser='".$_SESSION['iduser']."') AND  (id ='".$row_fix['id']."')");
					}}
					if(isset($res)){
					if($res){echo '<div class="alert bg-green alert-dismissible" role="alert">
                                <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                                <strong>Well done!</strong> Simpan Data Sukses.
             				 </div>';
							 }
					}
}else {
		echo '<div class="alert bg-pink alert-dismissible" role="alert">
             <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
            <strong>Warning!</strong> SIMPAN GAGAL, PIN Salah.
             </div>';
		}
	}else {
		echo '<div class="alert bg-pink alert-dismissible" role="alert">
             <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
            <strong>Warning!</strong> SIMPAN GAGAL, Ceklist persetujua.
             </div>';
		}
}

$uploadedStatus = 0;
if ( isset($_POST["submit"]) ) {
	$pin=noninjection($koneksi,$_POST['pin']);
	$task="34567fghjkl;FGHJK09865fghjs12345@#$@#$!@#";
			
	$pin=md5($pin);
	$pin_encript=md5($pin.$task);
		
	$qry_pin=mysqli_query($koneksi,"SELECT * FROM `tb_user` WHERE (email='".$_SESSION['email']."') AND (password='".$_SESSION['password']."') AND (pin='".$pin_encript."')");
	$N_pin=mysqli_num_rows($qry_pin);
	if($N_pin<>0)
	{

if ( isset($_FILES["file"])) {
//if there was an error uploading the file
	if ($_FILES["file"]["error"] > 0) 
	{$img='';}
	else {
		$fileType = $_FILES['file']['type'];
		$target_dir = "uploads/";
		$pembeda=rand(1111111,9999999);
		$pembeda1=rand(11111,99999);
		$pembeda2=rand(1111,9999);
		$allowed = array('application/vnd-ms-excel','application/vnd.ms-excel');
		$img='';
		if (in_array($fileType, $allowed)) 
		{
			$img=$pembeda.$pembeda1.$pembeda2.basename($_FILES["file"]["name"]);
			$target_file = $target_dir.$pembeda.$pembeda1.$pembeda2.basename($_FILES["file"]["name"]);
			move_uploaded_file($_FILES["file"]["tmp_name"],  $target_file);
		}
	}
}

set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
include 'PHPExcel/IOFactory.php';
 
// This is the file path to be uploaded.
$inputFileName = $target_file;
try {
    $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}
 
$allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
$arrayCount = count($allDataInSheet);  // Here get total count of row in that Excel sheet

for($i=1;$i<=$arrayCount;$i++){
			$idmember=noninjection($koneksi,$allDataInSheet[$i]["A"]);
			$nama=noninjection($koneksi,$allDataInSheet[$i]["B"]);
			$nominal=noninjection($koneksi,$allDataInSheet[$i]["C"]);
			$keterangan=noninjection($koneksi,$allDataInSheet[$i]["D"]);
			

if(($idmember<>'') and ($nominal<>'')  )
			{
									
					
					$row_user=mysqli_fetch_array($qry_pin);
					$tgl1=date('Y-m-d').' 00:00:00';
					$tgl2=date('Y-m-d').' 23:59:59';
					$qry_cari=mysqli_query($koneksi,"SELECT * FROM `tb_rekening_tmp` WHERE (idanggota='".$idmember."')  AND (in_out='out') and (nominal='".$nominal."') and (keterangan='".$keterangan."') and (tgl BETWEEN '$tgl1' AND '$tgl2')");
					$N=mysqli_num_rows($qry_cari);
					if($N==0)
					{
									$qry_rek=mysqli_query($koneksi,"SELECT * FROM `tb_rekening_tmp`  WHERE idanggota='".$idmember."' order by id desc limit 1");
									$row_rek=mysqli_fetch_array($qry_rek);
									if(mysqli_num_rows($qry_rek)<>0){
									$saldo=$row_rek['saldo']-$nominal;
									} else {$saldo=$nominal;}
					
					$res=mysqli_query($koneksi,"INSERT INTO `tb_rekening_tmp` (`idanggota`, `keterangan`, `in_out`, `nominal`, `saldo`, `reff`, `reff2`,`iduser`, `tgl`) VALUES ('".$idmember."', '".$keterangan."', 'out', '".$nominal."', '".$saldo."', '-', '-','".$_SESSION['iduser']."', current_timestamp());");
					}
			
}}
	if(isset($res)){
if($res){echo '<div class="alert bg-green alert-dismissible" role="alert">
                                <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                                <strong>Well done!</strong> Import Data Sukses.
              </div>';
							}else {
							echo '<div class="alert bg-pink alert-dismissible" role="alert">
                                <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                               <strong>Warning!</strong> Import Data Gagal.
                            </div>';
							}
		}//end res
}else {
				echo '<div class="alert bg-pink alert-dismissible" role="alert">
                                <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                               <strong>Warning!</strong> HAPUS GAGAL, PIN Salah.
                            </div>';
				}
}
?>
		 <div class="row clearfix">
                <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
                    <div class="card">
                        <div class="header">
                            <h2>PENARIKAN UPLOAD</h2>
                           
                        </div>
                        <div class="body">
						<form action="index.php?penarikan&mn=penarikan_upload" method="post" enctype="multipart/form-data" name="form1" id="form1">
						  <div class="form-group form-float">
                                    <div class="form-line">
                                        <input name="file" type="file" class="form-control" />
                                    </div>
                                    <div class="help-info">Upload File excel format file *.xls</b></div>
                                </div>
								
													  <div class="form-group form-float">
                                    <div class="form-line">
                                        <input type="password" class="form-control" name="pin" title="Masukkan PIN" style="width:200px"  required>
                                    </div>
                                    <div class="help-info">Input PIN untuk keamanan transaksi</b></div>
                                </div>
									
								
							 <button class="btn btn-primary waves-effect" name="submit" type="submit" data-toggle="tooltip" data-placement="right" title="Simpan Data">SUBMIT</button>	
						</form>
						 <div align="left"><br>
                    <b>Perhatian !</b>
					
                    <ul>
                      <li>File yang diupload harus berformat atau ekstensi .xls</li>
                      <li>Format xls-nya silahkan download, klik [ <a href="files/format-penarikan.xls">disini</a> ]</li>
                      <li>Hapus baris 1 (judul field) jika akan diupload !</li>
                      <li>Import bersifat menimpa, jika ada nomor induk sama, maka akan ditimpa dengan yang baru diimport.</li>
                      <li>Ukuran file .xls maksimal 1 MB</li>
                    </ul>
                  </div>
						 </div>
					</div>
				</div>
		</div>
		
		<?php 		
		$query_cari="SELECT * FROM `tb_rekening_tmp` where (in_out='out') AND (iduser='".$_SESSION['iduser']."') order by id desc";	
		$qry_cari=mysqli_query($koneksi,$query_cari);
		if(mysqli_num_rows($qry_cari)<>0){					
		?>
		 <div class="row clearfix">
                <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
                    <div class="card">
                        <div class="header">
                            <h2>DATA HASIL UPLOAD</h2>
                           
                        </div>
                        <div class="body">
		 <div class="table-responsive">
                                <table class="table table-bordered table-striped table-hover dataTable js-exportable">
                                    <thead>
                                        <tr>
                                          <th>IDMember</th>
                                            <th>Nama</th>
											<th>Kategori</th>
                                            <th>Nominal</th>
                                            <th>Saldo</th>
											<th>User / Tanggal</th>
											  <th></th>
                                        </tr>
                                    </thead>
                                    <tfoot>
                                        <tr>
                                           <th>IDMember</th>
                                            <th>Nama</th>
											<th>Kategori</th>
                                            <th>Nominal</th>
                                            <th>Saldo</th>
											<th>User / Tanggal</th>
											  <th></th>
                                        </tr>
                                    </tfoot>
                                    <tbody>
									<?php 
									while($row=mysqli_fetch_array($qry_cari)){
									?>
                                        <tr>
										 <td><?php echo $row['idanggota'];?></td>
										  <td ><b><?php 
									$qry_rek=mysqli_query($koneksi,"SELECT * FROM `tb_anggota`  WHERE idanggota='".$row['idanggota']."' limit 1");
									$row_rek=mysqli_fetch_array($qry_rek);
									
									echo $row_rek['nama'];
									?> </b></td>
										 <td><?php echo $row_rek['kategori'];?></td>
                                            <td align="right"><?php echo number_format($row['nominal'],0,",",".");?> IDR</td>
                                            <td align="right"><?php echo number_format($row['saldo'],0,",",".");?> IDR</td>
                                           <td>
										   <?php 
									$qry_user=mysqli_query($koneksi,"SELECT * FROM `tb_user`  WHERE id='".$row['iduser']."' limit 1");
									$qry_user=mysqli_fetch_array($qry_user);
									
									echo $qry_user['nama'];
									?>
										   <br />
										   <?php echo $row['tgl'];?></td>
											<td>
								<div align="right">
									

									
									<a href="index.php?penarikan&mn=penarikan_upload&del=<?php echo $row['id'];?>" onclick="return confirm('Yakin ingin Hapus data ini?');">
								  	<button type="button" class="btn bg-red btn-circle waves-effect waves-circle waves-float" data-toggle="tooltip" data-placement="left" title="Hapus Data">
                                    	<i class="material-icons">delete_sweep</i>
                                	</button>

									</a>
									
								</div>
											</td>
                                        </tr>
                                      <?php }?>
                                    </tbody>
                                </table>
<form action="index.php?penarikan&mn=penarikan_upload" method="post" name="form1" id="form1">
						<div class="block-header">
     					<h2>
          				<ol class="breadcrumb breadcrumb-bg-teal">
                   		<li>
				   			<input type="checkbox" id="remember_me" name="setujui" class="filled-in" required>
                            <label for="remember_me"><span style="font-size:14px">Saya mensetujui data ini, dan menyatakan data tersebut sudah benar.</span></label>
				   		</li>
                   
          				</ol>
     					</h2>
						</div>
								
							
						 
								
								<div class="form-group form-float">
                                <div class="form-line">
                                        <input type="password" class="form-control" name="pin_finish" style="width:200px" title="Masukkan PIN" required>
                                </div>
                                <div class="help-info">Input PIN untuk keamanan transaksi</b></div>
                                </div>	
								
							 <button class="btn btn-primary waves-effect" name="submit_finish" type="submit" data-toggle="tooltip" data-placement="right" title="Simpan Data">SETUJUI</button>	
						</form>	
						
					</div>
				</div>
		</div>			 
								
								
                            </div>
							<?php }?>
								<?php } ?>