<!DOCTYPE html>

<html>
<?php
error_reporting(2);
/*koneksi*/
$conn = mysqli_connect('localhost', 'kiosbarc_antrian_online' ,'kiosbarc_antrian_online2019', 'kiosbarc_antrian_online');
/*akhir-koneksi*/ 
#ambil data wilayah--
$query = "SELECT distinct(kode_wilayah)as kode_wilayah FROM kode_cabang order by kode_wilayah";
$sql = mysqli_query($conn, $query);
$arrkodewilayah = array();
while ($row = mysqli_fetch_assoc($sql)) {
	$arrkodewilayah [ $row['kode_wilayah'] ] = $row['kode_wilayah'];
}

?>
<head>
	<title>Report</title>	
	
	<script type="text/javascript" src="js/jquery.js"></script>
	<!--datepicker-->
	<script type="text/javascript" src="js/jquery-ui.js"></script>
	<script type="text/javascript" src="js/jquery.ui.datepicker-id.js"></script>
	<link rel="stylesheet" type="text/css" href="css/jquery-ui.css">
	<link rel="stylesheet" type="text/css" href="css/jquery-ui.theme.css">
	<!--jenislayanan dan jenistransaksi-->
	<script src="js/multiple-select.js"></script>
		<link rel="stylesheet" href="css/multiple-select.css"/>
		<!--tabel-->
		<script type="text/javascript" language="javascript" src="js/ZeroClipboard.js"></script>
		<script type="text/javascript" language="javascript" src="DataTables/Buttons-1.5.1/js/buttons.flash.min.js"></script>
		<script type="text/javascript" language="javascript" src="DataTables/Buttons-1.5.1/js/buttons.html5.min.js"></script>
		<script type="text/javascript" language="javascript" src="DataTables/Buttons-1.5.1/js/buttons.print.min.js"></script>
		<script type="text/javascript" language="javascript" src="DataTables/Buttons-1.5.1/js/dataTables.buttons.min.js"></script>
		<script type="text/javascript" language="javascript" src="DataTables/DataTables-1.10.16/js/jquery.dataTables.min.js"></script>
		<script type="text/javascript" language="javascript" src="DataTables/JSZip-2.5.0/jszip.min.js"></script>
		<script type="text/javascript" language="javascript" src="DataTables/pdfmake-0.1.32/pdfmake.min.js"></script>
		<script type="text/javascript" language="javascript" src="DataTables/pdfmake-0.1.32/vfs_fonts.min.js"></script>
		<script type="text/javascript" language="javascript" src="DataTables/datatables.min.js"></script>
		<link rel="stylesheet" type="text/css" href="DataTables/Buttons-1.5.1/css/buttons.dataTables.min.css">
		<link rel="stylesheet" type="text/css" href="DataTables/DataTables-1.10.16/css/jquery.dataTables.css">
		<link rel="stylesheet" type="text/css" href="DataTables/Buttons-1.5.1/css/datatables.min.css">
		<script type="text/javascript">
	$(document).ready(function() {
    $('#data').DataTable( {
        dom: 'Bfrtip',
        buttons: [
		
            {
                extend: 'pdfHtml5',
                orientation: 'landscape',
                pageSize: 'LEGAL'
            },{
				extend: 'excelHtml5'
			},
			{
				extend: 'copyHtml5'
			},
			{
				extend: 'csvHtml5'
			},
			{
				extend: 'print'
			}
        ],
		pageLength: 30,
		fnRowCallback: function (nRow, aData, iDisplayIndex, iDisplayIndexFull) {
if (aData[11] == "EXCEED") {
$('td:eq(11)',nRow).css('color', 'red');
}
if (aData[13] == "EXCEED") {
	$('td:eq(13)',nRow).css('color', 'red');
}
}
    } );
} );
</script>
<script type="text/javascript">
$(document).ready(function() {
    var table = $('#data').DataTable();
     
    $('#data tbody')
        .on( 'mouseenter', 'td', function () {
            var colIdx = table.cell(this).index().column;
 
            $( table.cells().nodes() ).removeClass( 'highlight' );
            $( table.column( colIdx ).nodes() ).addClass( 'highlight' );
        } );
} );
</script>

<style type="text/css">
td.highlight {
    background-color: whitesmoke !important;
}
th { font-size: 12px; }
td { font-size: 12px; }
</style>
	<!--datepicker-->
	<script type="text/javascript">
	$(document).ready(function(){
		$('.tanggal-awal').datepicker({dateFormat:'yy-mm-dd',placeholder: "Pilih Transaksi",});
		$('.tanggal-akhir').datepicker({dateFormat:'yy-mm-dd'});	
	});
	
 $(document).ready(function(){
				var load = "<option value=''> Pilih Cabang </option>";
				$('#namacabang').html(load);
				$('#iduser').multipleSelect({
					placeholder: "Pilih User",
					filter:true
				});
				$('#kode_wilayah').change(function(){
					var kode_wilayah = $('#kode_wilayah').val();
					$.ajax({
						url : "ajax_cabang.php",
						data : "kode_wilayah=" + kode_wilayah,
						success : function(data){
							$("#namacabang").html(data);
						}
					});
				});
				$('#namacabang').change(function(){
					var idcabang = $('#namacabang').val();
					$.ajax({
						url : "ajax_user_transaksi.php",
						data : "idcabang=" + idcabang,
						success : function(data){
							$('#iduser').html(data);
						}
					});
				});
			});
	</script>
	<!--jenistransaksi-->
	<script>
			$(document).ready(function(){
				$('#jenistransaksi').multipleSelect({
					placeholder: "Pilih Transaksi",
					filter:true
				});
			});
		</script>
	</head>
	</head>
	</head>
<body>
<div class="container">
<h2 class="text-center">Queuing Report Dengan Transaksi</h2>
	<div class="box-body table-responsive">
	<!--datepicker-->	
	<form name="formdatepicker" action="" method="post">
	<input type="text" placeholder="Start Date" name="datepickerawal" class="tanggal-awal" style="width:125px">
	<input type="text" placeholder="End Date" name="datepickerakhir" class="tanggal-akhir" style="width:125px">
	<br>
	<br>
	<!--wilayah-->
		<select id="kode_wilayah" name="kode_wilayah"  style="width:250px">
		<option selected="true" disabled="disabled">Kode Wilayah</option>
		<option value="all">Semua Wilayah(ALL)</option>
			<?php
			foreach($arrkodewilayah as $kode_wilayah=>$kode_wilayah) {
				echo "<option value='$kode_wilayah'>$kode_wilayah</option>";
			}
			?>
		</select>
		<br/>
		<br/>
	<!--cabang-->
	
	<select id="namacabang" name="namacabang"  style="width:250px">
			<!--<option value="all">Nama Cabang (ALL)</option>-->
		</select>
		<br/>
		<br/>
		</select>
			<select id="iduser" name="iduser[]" multiple="multiple" style="width:250px">
				
			</select>
			<br /><br />
	<!--jenistransaksi-->
	<select id="jenistransaksi" name="jenistransaksi[]" multiple="multiple" style="width:250px">
			<option value="A">Teller-->Setor</option>
		<option value="B">Teller-->Tarik</option>
		<option value="C">Teller-->Transfer Debet</option>
		<option value="D">Teller-->Transaksi Lain</option>
		<option value="E">CS-->Open Acc</option>
		<option value="F">CS-->Layanan Lain</option>
		<option value="G">LS-->Pengajuan</option>
		<option value="H">LS-->Layanan Lain</option>
		</select>
		<br>
		<br>
		<!--maxtunggu-->
	<select id="mintunggu" name="mintunggu" style="width:250px">
	<option selected="true" disabled="disabled">Min Waktu Tunggu (Minute)</option>
	<?php
	for($i=0;$i<=120;$i++){
		echo "<option value='$i'>$i</option>";
		$i+=4;
	}
	?>
	</select>
	<select id="maxtunggu" name="maxtunggu" style="width:250px">
	<option selected="true" disabled="disabled">Max Waktu Tunggu (Minute)</option>
	<?php
	for($i=0;$i<=120;$i++){
		echo "<option value='$i'>$i</option>";
		$i+=4;
	}
	?>
	</select>
	<br>
	<br>
	<!--maxtransaksi-->
	<select id="mintransaksi" name="mintransaksi" style="width:250px">
	<option selected="true" disabled="disabled">Min Waktu Transaksi (Minute)</option>
	<?php
	for($i=0;$i<=120;$i++){
		echo "<option value='$i'>$i</option>";
		$i+=4;
	}
	?>
	</select>
	<select id="maxtransaksi" name="maxtransaksi" style="width:250px">
	<option selected="true" disabled="disabled">Max Waktu Transaksi (Minute)</option>
	<?php
	for($i=0;$i<=120;$i++){
		echo "<option value='$i'>$i</option>";
		$i+=4;
	}
	?>
	</select>
	<br>
	<br>
	<input type="submit" name="Pilih" value="Submit"/>
	<br>
	<br>
	</form>
	
	<?php
		if(isset($_POST['Pilih'])) {
			if((empty($_POST['datepickerawal']))||(empty($_POST['datepickerakhir']))||(empty($_POST['kode_wilayah']))||(empty($_POST['namacabang']))||(empty($_POST['iduser']))||(!isset($_POST['mintunggu']))||(!isset($_POST['mintunggu']))||(!isset($_POST['mintransaksi']))||(!isset($_POST['maxtransaksi']))){
			die("Maaf, tidak boleh ada inputan yang kosong");
			//}elseif($_POST['kode_wilayah']=='all'){
			//	$query="select b.nama_cabang,a.nomorantrian,a.iduser,a.nocounter,a.waktudatang,a.waktupanggil,a.waktustart,a.waktufinish,a.jenistrx,TIMEDIFF(a.waktupanggil,a.waktudatang)AS lamatunggu,TIMEDIFF(a.waktufinish,a.waktustart)AS lamatransaksi from transaksi as a,kode_cabang as b where a.idcabang=b.id and date(a.waktudatang)>='"
			//.$_POST['datepickerawal'] ."' and date(a.waktudatang)<='" .$_POST['datepickerakhir'] ."'"
			//."and (a.nomorantrian like'" .implode("%' or a.nomorantrian like'", $_POST['jenistransaksi']) ."%')";
			//echo $query;
			//$sql = mysqli_query($conn, $query);
			}elseif
				($_POST['mintunggu']>=$_POST['maxtunggu']||$_POST['mintransaksi']>=$_POST['maxtransaksi']){
				die("Maaf, min waktu tunggu/transaksi tidak boleh sama atau lebih besar dari max waktu tunggu/transaksi");
			}else{
			
			//echo $_POST['datepickerawal'] .$_POST['datepickerakhir'];
			//echo implode(", ", $_POST['jenislayanan']);
			//echo implode(", ", $_POST['jenistransaksi']);
			if(substr($_POST['namacabang'],0,3)=='aal'){
				$query="select b.nama_cabang,a.nomorantrian,a.iduser,a.nocounter,a.waktudatang,a.waktupanggil,a.waktustart,a.waktufinish,a.jenistrx,TIMEDIFF(a.waktupanggil,a.waktudatang)AS lamatunggu,TIMEDIFF(a.waktufinish,a.waktustart)AS lamatransaksi from transaksi as a,kode_cabang as b where a.idcabang=b.id and date(a.waktudatang)>='"
			.$_POST['datepickerawal'] ."' and date(waktudatang)<='" .$_POST['datepickerakhir'] ."'"
			."and (a.nomorantrian like'" .implode("%' or a.nomorantrian like'", $_POST['jenistransaksi']) ."%')"
			//."and a.idcabang in(select id FROM kode_cabang WHERE kode_wilayah = substring('" .$_POST['namacabang']."',4))"
			."and a.iduser in('" .implode("','", $_POST['iduser']) ."')"
			." and TIMESTAMPDIFF(second,a.waktudatang,a.waktupanggil)>=" .($_POST['mintunggu']*60).""
			." and TIMESTAMPDIFF(second,a.waktudatang,a.waktupanggil)<=" .($_POST['maxtunggu']*60).""
			." and TIMESTAMPDIFF(second,a.waktustart,a.waktufinish)>=" .($_POST['mintransaksi']*60).""
			." and TIMESTAMPDIFF(second,a.waktustart,a.waktufinish)<=" .($_POST['maxtransaksi']*60)."";
			//echo $query;
			$sql = mysqli_query($conn, $query);
			}elseif(substr($_POST['namacabang'],0,3)=='all'){
				$query="select b.nama_cabang,a.nomorantrian,a.iduser,a.nocounter,a.waktudatang,a.waktupanggil,a.waktustart,a.waktufinish,a.jenistrx,TIMEDIFF(a.waktupanggil,a.waktudatang)AS lamatunggu,TIMEDIFF(a.waktufinish,a.waktustart)AS lamatransaksi from transaksi as a,kode_cabang as b where a.idcabang=b.id and date(a.waktudatang)>='"
			.$_POST['datepickerawal'] ."' and date(waktudatang)<='" .$_POST['datepickerakhir'] ."'"
			."and (a.nomorantrian like'" .implode("%' or a.nomorantrian like'", $_POST['jenistransaksi']) ."%')"
			."and a.idcabang in(select id FROM kode_cabang WHERE kode_wilayah = substring('" .$_POST['namacabang']."',4))"
			."and a.iduser in('" .implode("','", $_POST['iduser']) ."')"
			." and TIMESTAMPDIFF(second,a.waktudatang,a.waktupanggil)>=" .($_POST['mintunggu']*60).""
			." and TIMESTAMPDIFF(second,a.waktudatang,a.waktupanggil)<=" .($_POST['maxtunggu']*60).""
			." and TIMESTAMPDIFF(second,a.waktustart,a.waktufinish)>=" .($_POST['mintransaksi']*60).""
			." and TIMESTAMPDIFF(second,a.waktustart,a.waktufinish)<=" .($_POST['maxtransaksi']*60)."";
			//echo $query;
			$sql = mysqli_query($conn, $query);
			}else{
			$query="select b.nama_cabang,a.nomorantrian,a.iduser,a.nocounter,a.waktudatang,a.waktupanggil,a.waktustart,a.waktufinish,a.jenistrx,TIMEDIFF(a.waktupanggil,a.waktudatang)AS lamatunggu,TIMEDIFF(a.waktufinish,a.waktustart)AS lamatransaksi from transaksi as a,kode_cabang as b where a.idcabang=b.id and date(a.waktudatang)>='"
			.$_POST['datepickerawal'] ."' and date(waktudatang)<='" .$_POST['datepickerakhir'] ."'"
			."and (a.nomorantrian like'" .implode("%' or a.nomorantrian like'", $_POST['jenistransaksi']) ."%')"
			."and a.idcabang ='" .$_POST['namacabang']."'"
			."and a.iduser in('" .implode("','", $_POST['iduser']) ."')"
			." and TIMESTAMPDIFF(second,a.waktudatang,a.waktupanggil)>=" .($_POST['mintunggu']*60).""
			." and TIMESTAMPDIFF(second,a.waktudatang,a.waktupanggil)<=" .($_POST['maxtunggu']*60).""
			." and TIMESTAMPDIFF(second,a.waktustart,a.waktufinish)>=" .($_POST['mintransaksi']*60).""
			." and TIMESTAMPDIFF(second,a.waktustart,a.waktufinish)<=" .($_POST['maxtransaksi']*60)."";
			//echo $query;
			$sql = mysqli_query($conn, $query);
			}
			
		}
		}
		
	?>
	
	
	<table id="data" class="table table-bordered table-striped row-border hover order-column display">
	<thead>
	<tr>
	<th>NO</th>
	<th>CABANG</th>
   <th>NO ANTRIAN</th>
   <th>USER</th>
   <th>NO COUNTER</th>
   <th>DATANG</th>
   <th>PANGGIL</th>
   <th>START</th>
   <th>FINISH</th>
   <th>JENIS TRANSAKSI</th>
   <th>LAMA TUNGGU</th>
   <th>LAMA TRANSAKSI</th>
   
	</tr>
	</thead>
	<tbody>
	<?php
	
	$no_urut=0;
	while ($row = mysqli_fetch_assoc($sql))
	{
	$no_urut++;
   echo "<tr>";
   echo "<td>".$no_urut."</td>";
   echo "<td>".$row['nama_cabang']."</td>";
   echo "<td>".$row['nomorantrian']."</td>";
   echo "<td>".$row['iduser']."</td>";
   echo "<td>".$row['nocounter']."</td>";
   echo "<td>".$row['waktudatang']."</td>";
   echo "<td>".$row['waktupanggil']."</td>";
   echo "<td>".$row['waktustart']."</td>"; 
	echo "<td>".$row['waktufinish']."</td>";
   echo "<td>".$row['jenistrx']."</td>";
   echo "<td>".$row['lamatunggu']."</td>";
   echo "<td>".$row['lamatransaksi']."</td>";
   echo "</tr>";
	}
	?>
	<tbody>
	</table> 
 </div><!-- /.box-body -->
        </div>
</body>
</html>