<?php 
	
include("auth.php");
include("config.php");

$course_search = '';
$division = '';
$surname = '';
$course_selected = array();
//var_dump($_POST);
if(isset($_POST) && ($_POST)){
	$course_search = $_POST['course_search'];
	$division = $_POST['division'];
	$surname = $_POST['surname'];
	if(isset($_POST['courses'])){
		$course_selected = $_POST['courses'];
	}
	
}
$department = '';
if(isset($_SESSION['department'])){
	$department = $_SESSION['department'];
}
$moodle_admin = '';
if(isset($_SESSION['moodle_admin'])){
	$moodle_admin = $_SESSION['moodle_admin'];
}


$where_usersearch = '';
if($division){
	$where_usersearch .= " AND u.institution LIKE '%".$division."%'";
}
if($surname){
	$where_usersearch .= " AND u.lastname LIKE '%".$surname."%'";
}
$order = "asc";
if(!empty($_GET["sort"])) {
	$order = $_GET["sort"];
}
?><!DOCTYPE html>
<html>
<head>

	<title>Report - wwdad</title>
	<link rel="shortcut icon" href="images/favicon.png">
	<link rel="stylesheet" type="text/css" href="css/bootstrap.min.css">
	<link rel="stylesheet" type="text/css" href="css/style.css">
	<link rel="stylesheet" type="text/css" href="css/responsive.css">
	<link rel="stylesheet" type="text/css" href="css/datatables.min.css">
	<link rel="stylesheet" type="text/css" href="css/font-awesome.min.css"> 
</head>

<body>
<div class="container-fluid">
	<div class="row header">
		<div class="col-sm-1 logo">
			<img src="images/wwdadlogo500.png" class="img-responsive">
		</div>
		<div class="col-sm-10">
			<h3>moodle management reporting tool</h3>
		</div>
		<div class="col-sm-1">
			<a href="logout.php"><button type="button" class="btn btn-danger">Logout</button></a>
		</div>
	</div>
	<!--<hr style="border-color: #2d4ec8; margin-top: 2px; margin-bottom: 2px;">-->
	<form action="demo.php" method="post">
	<div class="searchform">
	<?php 
	if($department OR $moodle_admin){  ?>
	<div class="row top">
		<div class="col-md-12 dept">
		<?php if(($department == 'Super Admin') OR ($department == 'Moodle Site Administrator') OR ($moodle_admin == 'true')) {  ?>
			  <label>Site Administrator </label>
		<?php }else { ?>
		      <label>Department </label> : <?php echo $department;?>
		<?php } ?>
		</div>
	</div>
	<?php } ?>
	<div class="row top">
		<div class="col-lg-3 col-md-4 col-sm-4 col-12 search">
			<label>Search By:</label> 		
		</div>
		<div class="col-lg-2 col-md-4 col-sm-4  col-12">
			<label>Course </label> 
			<input class="course" type="input" name="course_search" id="searchcoursename" placeholder="course name" value="<?php echo $course_search; ?>">
		</div>
		<div class="col-lg-2 col-md-4 col-sm-4 col-12">
			<label>Division </label> 
			<input class="division" type="input" name="division" value="<?php echo $division; ?>" id="division">
		</div>
		<div class="col-lg-3 col-md-6 col-sm-6 col-12">
			<label>Surname </label> 
			<input class="surname" type="input" name="surname" value="<?php echo $surname; ?>" id="surname">
		</div>
		<div class="col-lg-3 col-md-4 col-sm-4 col-12 compdropdown">
    		<label style="margin-bottom: 0px;">Completion</label>&nbsp;
    		<select class="form-control" name="completion" id="completion">
    			<option value="0">Both</option>
				<option value="1">Complete</option>
    			<option value="2">Incomplete</option>    			
    		</select>
    	</div>
	</div>
	<div class="row top text-center">
		<div class="col-sm-12 top-buttns">
			<div class="bttn">
				<button type="submit" class="btn btn-primary" value="submit" id="submitsearch">Submit Search</button>
			</div>
			<div class="bttn">
				<button type="button" onclick="window.location='demo.php'" class="btn btn-primary">Clear Search</button>
			</div>
			
		</div>
		
	</div>
	<p class="search_text">Please select your Course below and then click the "Submit Search" button above to view results</p>
	</div>
	<!--<hr style="border-color: #2d4ec8; margin-top: 2px; margin-bottom: 2px;">-->
<?php	$postTitleNextOrder = "asc";

	if($order == "asc") {
		$postTitleNextOrder = "desc";
	}
	$order_by = '';
	if($postTitleNextOrder){
		$order_by = "ORDER BY fullname $postTitleNextOrder";
	}
	?>
	<center>
	<?php
	$where_course = '';
	if($course_search){
		$where_course = " AND fullname LIKE '%$course_search%'";
	}
	$course_query = "SELECT * FROM ".$database_prefix."course WHERE visible = '1' AND id != '1' $where_course $order_by";
//	print_r($course_query);
	$result_course = mysqli_query($con,$course_query) or die(mysqli_error($con));
	$no_course_rows = mysqli_num_rows($result_course);
	if($no_course_rows >0){ ?>
		<div class="row" style="margin-bottom: 30px; margin-top: 30px">
		
		<div class="col-sm-8 offset-sm-2">
		<div id ="csv_wrapper">
			<table id="csv" class="table table-striped table-bordered" style="width:100%">
				<colgroup>
					<col class="col-xs-1">
					<col class="col-xs-11">	
								
				</colgroup>
		        <thead style="background-color: #ccc;">
		            <tr>
		                <th style=" padding: 10px; width: auto;"></th>
		                <th style=" padding: 10px" ><a href="demo.php?sort=<?php echo $postTitleNextOrder; ?>">Course Name</a></th>  

		            </tr>
		        </thead>
		        <tbody id="coursesearch">
		            
		<?php 
			$course_id_array = array();
			while($row_course = mysqli_fetch_array($result_course,MYSQLI_ASSOC)) {
				$course_id_array[] = $row_course['id'];
			?>
			<tr>
		                <td class="check">
							<?php 
								if(in_array($row_course['id'],$course_selected)){
									echo '<input type="checkbox" name="courses[]" checked class="form-check-input" value="'.$row_course['id'].'"';
								}else{
									echo '<input type="checkbox" name="courses[]" class="form-check-input" value="'.$row_course['id'].'"';
								}
							?>
							
						</td>
						<td>
							<?php echo $row_course['fullname']; ?>
						</td>
		            </tr>
		<?php } ?>
		</tbody>
		    </table>
        	</div>      
		</div>
		</div>
		
		<?php
	}else{
		echo '<div class="col-sm-12"><h3 style="text-align:center;">No Courses available</h3></div>';
	}
	?>
	
	</center>
	<form>
	<hr style="border-color: #2d4ec8; margin-top: 2px; margin-bottom: 2px;">
	
	<div class="bottom-table" style="display:none;">
		<div class="row">
			<?php
				if($course_selected){
					$course_ids = implode(',',$course_selected);
				}else{
					$course_ids = implode(',',$course_id_array);					
				}
				$where_department = '';
				if($department){
					//department user
					$where_department = " AND u.department = '$department'";
					//$user_query = "SELECT * FROM ".$database_prefix."user WHERE confirmed = '1' AND deleted = '0' AND `department` = '$department' $where_usersearch";
					//$user_query = "SELECT * FROM ".$database_prefix."user WHERE confirmed = '1' AND deleted = '0' AND `department` = '11'";
				}/*else{
					//admin user
					$user_query = "SELECT * FROM ".$database_prefix."user WHERE confirmed = '1' AND deleted = '0' AND id != '1' $where_usersearch";
					//var_dump($_POST);exit;
					
				}*/
				if(($department == 'Super Admin') OR ($department == 'Moodle Site Administrator')) { 
				$user_query = "SELECT u.id,u.id As userid, u.firstname, u.lastname,u.username,u.institution,u.department,u.idnumber,c.fullname,cc.timecompleted, gg.finalgrade 
				FROM ".$database_prefix."user_enrolments AS ue 
				JOIN ".$database_prefix."user  AS u ON (u.id = ue.userid) 
				JOIN ".$database_prefix."enrol As e ON (e.id = ue.enrolid AND e.courseid IN ($course_ids)) 
				LEFT JOIN ".$database_prefix."course AS c ON (c.id = e.courseid) 
				LEFT JOIN ".$database_prefix."course_completions AS cc ON (cc.course = e.courseid AND cc.userid = u.id) 
				LEFT JOIN ".$database_prefix."grade_items AS gi ON (gi.courseid = e.courseid AND gi.itemtype = 'course') 
				LEFT JOIN ".$database_prefix."grade_grades AS gg ON (gg.itemid = gi.id AND gg.userid = u.id)  
				WHERE u.confirmed = '1' AND u.deleted = '0' AND u.id != '1'  $where_usersearch ORDER BY cc.timecompleted DESC ";
				}else {
				$user_query = "SELECT u.id,u.id As userid, u.firstname, u.lastname,u.username,u.institution,u.department,u.idnumber,c.fullname,cc.timecompleted, gg.finalgrade 
				FROM ".$database_prefix."user_enrolments AS ue 
				JOIN ".$database_prefix."user  AS u ON (u.id = ue.userid) 
				JOIN ".$database_prefix."enrol As e ON (e.id = ue.enrolid AND e.courseid IN ($course_ids)) 
				LEFT JOIN ".$database_prefix."course AS c ON (c.id = e.courseid) 
				LEFT JOIN ".$database_prefix."course_completions AS cc ON (cc.course = e.courseid AND cc.userid = u.id) 
				LEFT JOIN ".$database_prefix."grade_items AS gi ON (gi.courseid = e.courseid AND gi.itemtype = 'course') 
				LEFT JOIN ".$database_prefix."grade_grades AS gg ON (gg.itemid = gi.id AND gg.userid = u.id)  
				WHERE u.confirmed = '1' AND u.deleted = '0' AND u.id != '1' $where_department $where_usersearch ORDER BY cc.timecompleted DESC";
				
				}
			//	print_r($user_query);
				$result_user = mysqli_query($con,$user_query) or die(mysqli_error($con));
				$no_user_rows = mysqli_num_rows($result_user);
				if($no_user_rows == 0){
					echo '<div class="col-sm-12"><h3 style="text-align:center;">No Users available</h3></div>';
				}else{ ?>
					<div style="clear:both;"></div>
					<div class="col-sm-12 table-responsive">
						
						<table id="bottom-table" class="table table-striped table-bordered table-bottm1" style="width:100%">
						
						<thead>
							<tr>
								<th>Employee ID</th>
								<th>User Name</th>  
								<th>First Name</th>  
								<th>Surname</th>  
								<th>Department</th>  
								<th>Division</th>  
								<th>Course</th>  
								<th>Final Score</th>  
								<th>Completion</th>  
								<th>Date completed</th>  

							</tr>
						</thead>
						<tbody>
							<?php 
							
							while($row = mysqli_fetch_array($result_user,MYSQLI_ASSOC)) {
								
								$employee_id = $row['idnumber'];
								$username = $row['username'];
								$firstname = $row['firstname'];
								$lastname = $row['lastname'];
								$department1 = $row['department'];
								$institution = $row['institution'];
								$coursefullname = $row['fullname'];
								$timecompleted = $row['timecompleted'];
								$finalgrade = $row['finalgrade'];
								
								echo '<tr>';
								echo '<td>'.$employee_id.'</td>';
								echo '<td>'.$username.'</td>';
								echo '<td>'.$firstname.'</td>';
								echo '<td>'.$lastname.'</td>';
								echo '<td>'.$department1.'</td>';
								echo '<td>'.$institution.'</td>';
								echo '<td>'.$coursefullname.'</td>';
								if($finalgrade > 0){
									echo '<td>'.round($finalgrade,2).'%</td>';
								}else{
									echo '<td>-</td>';
								}
								if($timecompleted){
									echo '<td>Complete</td>';
									echo '<td>'.date('d/m/y', $timecompleted).'</td>';
									/*echo '<td>'.date('M d, Y', $timecompleted).'</td>';*/
									/*echo '<td>'.date('d/m/Y H:i:s', $timecompleted).'</td>';				*/					
								}else{
									echo '<td>Incomplete</td>';
									/*echo '<td>-</td>';*/
									echo '<td></td>';
								}
								echo '</tr>';
							}
							?>
						</tbody>
					</table>
					</div>
				<?php }
				?>
		</div>
	</div>
	<button type="submit" class="btn btn-primary" value="submit" style="display: block; margin: 0 auto; margin-bottom: 20px">Send
				email to these incomplete users</button>
	<footer>
		<p>Developed by Wallace Web Design and Development, any queries please email <a href="mailto:richard@wwdad.com.au">richard@wwdad.com.au</a> </p>
	</footer>
</div>
</body>
	<script type="text/javascript" src="js/bootstrap.min.js"></script>	
	<script type="text/javascript" src="js/jquery.min.js"></script>
	<script type="text/javascript" src="js/datatables.min.js"></script>
	<script type="text/javascript" src="js/formValidation.js"></script>
	<script type="text/javascript" src="js/bootstrap-validation.js"></script>
	<script type="text/javascript" src="js/custom-validation.js"></script>
	<script type="text/javascript" src="js/jquery.datatables.min.js"></script>

	<script type="text/javascript" src="js/datatable/dataTables.buttons.min.js"></script>
	<script type="text/javascript" src="js/datatable/buttons.flash.min.js"></script>
	<script type="text/javascript" src="js/datatable/jszip.min.js"></script>
	<script type="text/javascript" src="js/datatable/pdfmake.min.js"></script>
	<script type="text/javascript" src="js/datatable/vfs_fonts.js"></script>
	<script type="text/javascript" src="js/datatable/buttons.html5.min.js"></script>
	<script type="text/javascript" src="js/datatable/buttons.print.min.js "></script>
	<script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
	<script type="text/javascript" src="//cdn.datatables.net/plug-ins/1.10.19/sorting/datetime-moment.js"></script>
	<script> 
		var searchcoursename = $("#searchcoursename").val();
		var division = $("#division").val();
		var surname = $("#surname").val();
	    var courses = $( "input:checked" ).length;
	    
			if((searchcoursename == '') && (division  == '') && (surname  == '') && (courses == 0)) {
				$(".bottom-table").css("display", "none");
			}else{
				$(".bottom-table").css("display", "block");
			}
				

		   $("#submitsearch").click(function() {
			   var searchcoursename = $("#searchcoursename").val();
			   var division = $("#division").val();
			   var surname = $("#surname").val();
			   var courses = $( "input:checked" ).length;
				console.log(courses);
		   if((searchcoursename == '') && (division  == '') && (surname  == '') && (courses == 0)) {
		       $(".bottom-table").css("display", "none");
		   }else{
		       $(".bottom-table").css("display", "block");
	       }

		   });
		$(document).ready(function() {

		   /* $('#csv').DataTable({
		    	"paging":   false,
        		"ordering": true,
        		"info":     false,
        		"searching":false,
				"aaSorting": [],
				"aoColumnDefs": [
					{'bSortable': false, 'aTargets': [0]} 
				  ],
        	//	scrollY: '30vh',
        		//scrollCollapse: true,   
		    }); */
			
			$("#searchcoursename").keyup(function(){
				var course_search = $("#searchcoursename").val();
				var request = $.ajax({
				  url: "getcourses.php",
				  method: "POST",
				  data: { coursesearch : course_search },				 
				});
				 
				request.done(function( resp ) {
				  $( "#coursesearch" ).html( resp );
				});
			});
		});	
		$(document).ready(function() {
		    
		    /*$.fn.dataTable.moment( 'MMM D, YYYY' );*/
		    $.fn.dataTable.moment( 'D/M/YY' );
		   /* $('.table-bottm1').DataTable();*/
			$('.table-bottm1').DataTable({
				dom: 'Bfrtip',
		    	"paging":   true,
        		"ordering": true,
        		"info":     false,
        		"searching":false,
				buttons: [
					{ extend: 'csv', text: 'Download as CSV',download: 'open',
						filename: function(){
							var d = new Date();
							var n = d.getTime();
							var y = d.getFullYear();
							var m = d.getMonth() + 1;
							var d1 = d.getDate();
							var h = d.getHours();
							var m1 = d.getMinutes();
							var s = d.getSeconds();
							return 'Report-wwdad-' + y+'-'+m+'-'+d1+'T'+h+'-'+m1+'-'+s;
						},
						title:function(){
							var d = new Date();
								var n = d.getTime();
								var y = d.getFullYear();
								var m = d.getMonth() + 1;
								var d1 = d.getDate();
								var h = d.getHours();
								var m1 = d.getMinutes();
								var s = d.getSeconds();
							var depar = '<?php echo $department;?>';
							if(depar){
								return 'Report for : '+depar+' '+ y+'-'+m+'-'+d1+'T'+h+'-'+m1+'-'+s;
							}else{
								return 'Report  '+ y+'-'+m+'-'+d1+'T'+h+'-'+m1+'-'+s;
							}
						},
					},
					{ extend: 'excel', text: 'Download as Excel',download: 'open',
						title:function(){
							var d = new Date();
								var n = d.getTime();
								var y = d.getFullYear();
								var m = d.getMonth() + 1;
								var d1 = d.getDate();
								var h = d.getHours();
								var m1 = d.getMinutes();
								var s = d.getSeconds();
							var depar = '<?php echo $department;?>';
							if(depar){
								return 'Report for : '+depar+' '+ y+'-'+m+'-'+d1+'T'+h+'-'+m1+'-'+s;
							}else{
								return 'Report  '+ y+'-'+m+'-'+d1+'T'+h+'-'+m1+'-'+s;
							}
						},
						filename: function(){
							var d = new Date();
							var n = d.getTime();
							var y = d.getFullYear();
							var m = d.getMonth() + 1;
							var d1 = d.getDate();
							var h = d.getHours();
							var m1 = d.getMinutes();
							var s = d.getSeconds();
							return 'Report-wwdad-' + y+'-'+m+'-'+d1+'T'+h+'-'+m1+'-'+s;
						} 
					},
					{
						extend : 'pdfHtml5',
						orientation : 'landscape',
						pageSize : 'LEGAL',
						text : 'Download as PDF',
						titleAttr : 'PDF',
						exportOptions: {
                            columns: [ 1, 2, 3, 4, 5, 6, 7, 8, 9 ]
                        },
						
						title:function(){
							var d = new Date();
								var n = d.getTime();
								var y = d.getFullYear();
								var m = d.getMonth() + 1;
								var d1 = d.getDate();
								var h = d.getHours();
								var m1 = d.getMinutes();
								var s = d.getSeconds();
							var depar = '<?php echo $department;?>';
							if(depar){
								return 'Report for : '+depar+' '+ y+'-'+m+'-'+d1+'T'+h+'-'+m1+'-'+s;
							}else{
								return 'Report  '+ y+'-'+m+'-'+d1+'T'+h+'-'+m1+'-'+s;
							}
						}
					}
					
				],
				
		    });
		});	

	</script>
</html>