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

$course_search = '';
$division = '';
$department1 = '';
$surname = '';
$course_selected = array();
$dependentcourse_selected = array();
$completion_status = '';
$certification_status = '';
$summaryview = 0;
$certified_course_counter = 0;
$notcertified_course_counter = 0;
$certified_overview = 0;
$certified_notoverview = 0;
$sendemail = '';
if(isset($_POST) && ($_POST)){
	$course_search = $_POST['course_search'];
	$division = $_POST['division'];
	if(isset($_POST['department1'])){
		$department1 = $_POST['department1'];
	}
	$surname = $_POST['surname'];
	$completion_status = $_POST['completion'];
	$certification_status = $_POST['certification'];
	
	if(isset($_POST['courses'])){
		$course_selected = $_POST['courses'];
	}
	if(isset($_POST['summaryview'])){
		$summaryview = 1;
	}
	if(isset($_POST['sendemail'])){
		$sendemail = $_REQUEST['sendemail'];
	} 
}
function check_course($cid){
	global $re_accrediation_course;
	foreach($re_accrediation_course AS $cidkey=> $cidval){
		if($cid == $cidval){
			return $cidkey;
		}
	}
}

if(isset($pre_selected_courses)){
	if(empty($course_selected)){
		$course_selected = $pre_selected_courses;
	}
}

if(isset($re_accrediation_course)){
	foreach($course_selected AS $coursesidsearch){
		$retuncid = check_course($coursesidsearch);				
		if($retuncid){
			if(!in_array($retuncid,$course_selected)){
				$dependentcourse_selected[] = $retuncid;
			}
		}
	}
}

$department = '';
if(isset($_SESSION['department'])){
	$department = $_SESSION['department'];
}
$division1 = '';
if(isset($_SESSION['division'])){
	$division1 = $_SESSION['division'];
}
$moodle_admin = '';
if(isset($_SESSION['moodle_admin'])){
	$moodle_admin = $_SESSION['moodle_admin'];
}


$where_usersearch = '';
if($division){
	$where_usersearch .= " AND u.institution LIKE '%".$division."%'";
}
if($department1){
	$where_usersearch .= " AND (( u.department LIKE '%".$department1."%' ) OR (u.phone1 LIKE '%".$department1."%'))";
}
if($surname){
	$where_usersearch .= " AND u.lastname LIKE '%".$surname."%'";
}
if($hide_suspended){
	$where_usersearch .= " AND u.suspended = '0'";
	$where_usersearch .= " AND ue.status = '0'";
}
$where_usersearch_accredited = '';
if(isset($accredited_display_course)){
	
	if($hide_suspended){
		$where_usersearch_accredited .= " AND u.suspended = '0'";
		$where_usersearch_accredited .= " AND ue.status = '0'";
	}
	if($department){
		$where_usersearch_accredited .= " AND ((u.department = '$department' ) OR (u.phone1 LIKE '%".$department."%'))";
	}
}

if($completion_status){
	if($completion_status == '1'){
		/*$where_usersearch .= " AND cc.timecompleted IS NOT NULL";*/
		$where_usersearch .= ' AND if( gi.gradepass = "0.00000", "100", gi.gradepass) <= gg.finalgrade  ';
	}
	if($completion_status == '2'){
		/*$where_usersearch .= " AND cc.timecompleted IS NULL";*/
		$where_usersearch .= ' AND ((if( gi.gradepass = "0.00000", "100", gi.gradepass) > gg.finalgrade) OR (gg.finalgrade IS NULL))';
	}
}
$order = "asc";
if(!empty($_GET["sort"])) {
	$order = $_GET["sort"];
}
				$postTitleNextOrder = "asc";

				//var_dump($_REQUEST);exit;
				if($order == "asc") {
					$postTitleNextOrder = "desc";
				}
				$order_by = '';
				if($postTitleNextOrder){
					$order_by = "ORDER BY fullname $postTitleNextOrder";
				}
				
				$where_course = '';
				if($course_search){
					$where_course = " AND fullname LIKE '%$course_search%'";
				}
				if($showcourseids){
					$where_course .= " AND id IN (".$showcourseids.")";
				}
				if(!$showhiddencourses){
            		$where_course .= " AND visible = '1' ";
            	}
				$course_query = "SELECT * FROM ".$database_prefix."course WHERE id != '1' $where_course $order_by";
				$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){
					$course_id_array = array();
				while($row_course = mysqli_fetch_array($result_course,MYSQLI_ASSOC)) {
					$course_id_array[] = $row_course['id'];
				}
			
				if($course_selected){
					$course_ids = implode(',',$course_selected);
				}else{
					$course_ids = implode(',',$course_id_array);					
				}
				$coursecid_ids = null;
				if($dependentcourse_selected){
					$coursecid_ids = implode(',',$dependentcourse_selected);
				}
				$dependent_searchstring = '';
				if($coursecid_ids){
					$dependent_searchstring = ' OR e.courseid IN ('.$coursecid_ids.')';
				}
				$where_department = '';
				if($department){
					//department user
					$where_department = " AND ((u.department = '$department') OR (u.phone1 LIKE '%".$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'";
				}
				if($division1){
					//department user
					$where_department .= " AND u.institution = '$division1'";
					//$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.email, u.lastname,u.username,u.institution,u.department,u.phone1,u.idnumber,c.id as courseid,c.fullname,gg.timemodified,gi.gradepass, gg.finalgrade, uid.data as reportfield 
				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) $dependent_searchstring) )
				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)  
				LEFT JOIN ".$database_prefix."user_info_data AS uid ON (uid.userid = u.id AND uid.fieldid = (SELECT id from ".$database_prefix."user_info_field WHERE shortname = 'reportfield'))		
				WHERE u.confirmed = '1' AND u.deleted = '0' AND u.id != '1'  $where_usersearch ORDER BY gg.timemodified DESC ";
				
				$user_accredited_query = "SELECT u.id,u.id As userid, u.firstname, u.email, u.lastname,u.username,u.institution,u.department,u.phone1,u.idnumber,c.id as courseid,c.fullname,gg.timemodified,gi.gradepass, gg.finalgrade, uid.data as reportfield 
				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 ($accredited_display_course)) )
				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)  
				LEFT JOIN ".$database_prefix."user_info_data AS uid ON (uid.userid = u.id AND uid.fieldid = (SELECT id from ".$database_prefix."user_info_field WHERE shortname = 'reportfield'))		
				WHERE u.confirmed = '1' AND u.deleted = '0' AND u.id != '1' $where_usersearch_accredited ORDER BY gg.timemodified DESC ";
				
				}else {
				$user_query = "SELECT u.id,u.id As userid, u.firstname, u.email, u.lastname,u.username,u.institution,u.department,u.phone1,u.idnumber,c.id as courseid,c.fullname,gg.timemodified,gi.gradepass, gg.finalgrade, uid.data as reportfield
				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) $dependent_searchstring) )
				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)  
				LEFT JOIN ".$database_prefix."user_info_data AS uid ON (uid.userid = u.id AND uid.fieldid = (SELECT id from ".$database_prefix."user_info_field WHERE shortname = 'reportfield'))		
				WHERE u.confirmed = '1' AND u.deleted = '0' AND u.id != '1' $where_department $where_usersearch ORDER BY gg.timemodified DESC";
				
				$user_accredited_query = "SELECT u.id,u.id As userid, u.firstname, u.email, u.lastname,u.username,u.institution,u.department,u.phone1,u.idnumber,c.id as courseid,c.fullname,gg.timemodified,gi.gradepass, gg.finalgrade, uid.data as reportfield
				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 ($accredited_display_course)) )
				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)  
				LEFT JOIN ".$database_prefix."user_info_data AS uid ON (uid.userid = u.id AND uid.fieldid = (SELECT id from ".$database_prefix."user_info_field WHERE shortname = 'reportfield'))		
				WHERE u.confirmed = '1' AND u.deleted = '0' AND u.id != '1' $where_usersearch_accredited ORDER BY gg.timemodified DESC";
				
				}
				
				$result_user = mysqli_query($con,$user_query) or die(mysqli_error($con));
				$no_user_rows = mysqli_num_rows($result_user);
				
				$final_result = array();
				
				if($result_user){
					while($row = mysqli_fetch_array($result_user,MYSQLI_ASSOC)) {
						$final_result[$row['id']][$row['courseid']] = $row;
					}
				}
							
				if($final_result){
					$final_result_array = array();
					$i = 0;
					foreach($final_result as $value){
							if(isset($re_accrediation_course))
							{
								foreach($re_accrediation_course as $recourse=>$val){
									if(array_key_exists($val, $value)){
										if(isset($value[$recourse])){
											$finalgrade = $value[$recourse]['finalgrade'];
											$gradepass = $value[$recourse]['gradepass'];
											if($finalgrade > 0){
												if($gradepass == "0.00000"){
													$gradepass = '100';
												}
												if($finalgrade >= $gradepass){
													$timecompleted_next = $value[$recourse]['timemodified'];
													if($timecompleted_next){														
														$value[$val]['showuntil'] = 'Valid until '.date('Y', strtotime('+ 1 Year',$timecompleted_next));
													    /*Temp fix start*/
													    if($val == '8'){
													        $value[$val]['showuntil'] = 'Completed Certified';
													    }
													    /*Temp Fix End*/
													}
													unset($timecompleted_next);
													
													/*main record need to remove from the loop which is depend on accredition*/
													unset($value[$recourse]);
												}
											}
										}
									}
								}
							}
							foreach($value as $key=>$valrow){
								/*get calculation for certified*/
								if(isset($certified_courses)){
									$timecompleted = $valrow['timemodified'];
									$finalgrade = $valrow['finalgrade'];
									$gradepass = $valrow['gradepass'];			
									$courseid = $valrow['courseid'];
									$showuntil1 = $valrow['showuntil'];
									if(in_array($courseid,$certified_courses)){
										if($gradepass == "0.00000"){
											$gradepass = '100';
										}
										if($finalgrade >= $gradepass){
											if($timecompleted){
												$certified_course_counter++;
											}else{
											}
										}else{
										    $notcertified_course_counter++;														
										}
									}
									unset($timecompleted);
									unset($finalgrade);
									unset($gradepass);
									unset($courseid);
									unset($showuntil1);
								}											
								/*get calculation for certified */
								if($certification_status > 0){												
									if(isset($certified_courses)){
										$timecompleted = $valrow['timemodified'];
										$finalgrade = $valrow['finalgrade'];
										$gradepass = $valrow['gradepass'];			
										$courseid = $valrow['courseid'];
										if(in_array($courseid,$certified_courses)){
											if($gradepass == "0.00000"){
												$gradepass = '100';
											}
											if($finalgrade >= $gradepass){
												if($timecompleted){
													if($certification_status == '1'){
														$final_result_array[$i] = $valrow;
													}
												}
											}else{
												if($certification_status == '2'){
													$final_result_array[$i] = $valrow;
												}															
											}
										}
									}
									unset($timecompleted);
									unset($finalgrade);
									unset($gradepass);
									unset($courseid);
								}else{												
									$final_result_array[$i] = $valrow;
								}
								$i++;
							}
					}
				}
				if($dependentcourse_selected){
					$certified_overview = 0;
					$certified_notoverview = 0;
					$final_result_array_temp = array();
					foreach($final_result_array As $final_resultdata){						
						$final_result_array_temp[$final_resultdata['userid']][$final_resultdata['courseid']] = $final_resultdata;
					}
					$final_result_array1 = array();
					if($final_result_array_temp){
						foreach($final_result_array_temp As $key=>$val){
							foreach($dependentcourse_selected as $ddata){
								
								if(isset($val[$ddata])){
									unset($val[$ddata]);
								}
								
							}
							$final_result_array1[] = $val;
						}
					}
					$final_result_array = array();
					if($final_result_array1){
						foreach($final_result_array1 as $data_final){
							if($data_final){
								foreach($data_final AS $data_final_row){
									$final_result_array[] = $data_final_row;
								}
							}
							
						}
					}
				
					if($final_result_array){
						$certified_course_counter = 0;
						$notcertified_course_counter = 0;
						foreach($final_result_array As $key=>$valrow){
							if(isset($certified_courses)){
									$timecompleted = $valrow['timemodified'];
									$finalgrade = $valrow['finalgrade'];
									$gradepass = $valrow['gradepass'];			
									$courseid = $valrow['courseid'];
									$showuntil1 = $valrow['showuntil'];
									if(in_array($courseid,$certified_courses)){
										if($gradepass == "0.00000"){
											$gradepass = '100';
										}
										if($finalgrade >= $gradepass){
											if($timecompleted){
												$certified_course_counter++;
											}
										}else{
											$notcertified_course_counter++;														
										}
									}
									unset($timecompleted);
									unset($finalgrade);
									unset($gradepass);
									unset($courseid);
									unset($showuntil1);
							}
						}	
					}
				}
				
				/* code for summary view*/
				if($summaryview == '1'){
					if($final_result_array){
						$summaryviewdata = array();
						foreach($final_result_array as $summaryval){
							$summaryviewdata[$summaryval['userid']][] = $summaryval;
						}
						if($summaryviewdata){
							$summaryfinal = array();
							foreach($summaryviewdata AS $summarytemp){
								$summaryfinal[] = array_reduce($summarytemp, function ($a, $b) {
													return @$a['timemodified'] > $b['timemodified'] ? $a : $b ;
												});
							}
						}
						$final_result_array = $summaryfinal;
					}
				}
				/* code for summary view*/
				
				/*Code for calculating accreditation*/
				
				$result_user_accredited = mysqli_query($con,$user_accredited_query) or die(mysqli_error($con));
				$no_user_rows_accredited = mysqli_num_rows($result_user_accredited);
				
				$final_result_accredited = array();
				
				if($result_user_accredited){
					while($row = mysqli_fetch_array($result_user_accredited,MYSQLI_ASSOC)) {
						$final_result_accredited[$row['id']][$row['courseid']] = $row;
					}
				}
				
				if($final_result_accredited){
					$final_result_array_accredited = array();
					$i = 0;
					foreach($final_result_accredited as $value){
							if(isset($re_accrediation_course))
							{
								foreach($re_accrediation_course as $recourse=>$val){
									if(array_key_exists($val, $value)){
										if(isset($value[$recourse])){
											$finalgrade = $value[$recourse]['finalgrade'];
											$gradepass = $value[$recourse]['gradepass'];
											if($finalgrade > 0){
												if($gradepass == "0.00000"){
													$gradepass = '100';
												}
												if($finalgrade >= $gradepass){
													$timecompleted_next = $value[$recourse]['timemodified'];
													if($timecompleted_next){														
														$value[$val]['showuntil'] = 'Completed '.date('Y', strtotime('+ 1 Year',$timecompleted_next));
													    /*Temp fix start*/
													    if($val == '8'){
													        $value[$val]['showuntil'] = 'Certified until Dec 2020';
													    }
													    /*Temp Fix End*/
													}
													unset($timecompleted_next);
													
													/*main record need to remove from the loop which is depend on accredition*/
													unset($value[$recourse]);
												}
											}
										}
									}
								}
							}
							foreach($value as $key=>$valrow){
								/*get calculation for certified*/
								if(isset($certified_courses)){
									$timecompleted = $valrow['timemodified'];
									$finalgrade = $valrow['finalgrade'];
									$gradepass = $valrow['gradepass'];			
									$courseid = $valrow['courseid'];
									$showuntil1 = $valrow['showuntil'];
									if(in_array($courseid,$certified_courses)){
										if($gradepass == "0.00000"){
											$gradepass = '100';
										}
										if($finalgrade >= $gradepass){
											if($timecompleted){
												$certified_overview++;
											}else{
												$certified_notoverview++;
											}
										}else{
										    if($showuntil1){
												$certified_overview++;
											}else{
												$certified_notoverview++;
											}
										}
									}
									unset($timecompleted);
									unset($finalgrade);
									unset($gradepass);
									unset($courseid);
									unset($showuntil1);
								}		
								$final_result_array_accredited[$i] = $valrow;
								$i++;
							}
					}
				}
				
				/*code for calculating accreditation*/
				if($sendemail == '1'){
					$i = 0;
					foreach($final_result_array as $key=>$row){		 
						$firstname = $row['firstname'];
						$lastname = $row['lastname'];
						$coursefullname = $row['fullname'];
						$user_email = $row['email'];
								
						
						$to = $firstname.' '.$lastname.'<'.$user_email.'>';
						//$to = $firstname.' '.$lastname.'<harshilshah1310@gmail.com>';
						$subject = 'Comlearn Incomplete Course Notification';
						$from = 'Comlearn<noreply@comlearn.org.au>';
						 
						// To send HTML mail, the Content-type header must be set
						$headers  = 'MIME-Version: 1.0' . "\r\n";
						$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
						 
						// Create email headers
						$headers .= 'From: '.$from."\r\n".
							'Reply-To: '.$from."\r\n" .
							'X-Mailer: PHP/' . phpversion();
						 
						// Compose a simple HTML email message
						$message = '<html><body>';
						$message .= '<p>Hello '.$firstname.',<br/><br/>';
						$message .= 'Your Course '.$coursefullname.' is incomplete.<br/><br/>';
						$message .= 'Regards, <br/>The Comlearn Team</p><br/><br/>';
						$message .= '</body></html>';
						 
						// Sending email
						mail($to, $subject, $message, $headers);
							
						
						
					}
				}
			}
?><!DOCTYPE html>
<html>
<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">

	<title>Comlearn Reporting</title>
	<link rel="shortcut icon" href="images/favicon.ico">
	<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/Comlearn Logo_Navy500.png" class="img-responsive">
		</div>
		<div class="col-sm-10">
			<h3>Comlearn 2022 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="index.php" method="post">
	<div class="searchform">
	<?php 
	if($department OR $moodle_admin OR $division1){  ?>
	<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 if($department != ''){ ?>
		      <label>(Department)</label> : <?php echo $department;?>
			  <br>
				<?php 
				$total_course_counter = $certified_course_counter + $notcertified_course_counter;
				$certified_course = round(($certified_course_counter*100)/$total_course_counter,2);
				$notcertified_course = round(($notcertified_course_counter*100)/$total_course_counter,2);
				
				$total_overviewcourse_counter = $certified_overview + $certified_notoverview;
				$certified_overiew_course = round(($certified_overview*100)/$total_overviewcourse_counter,2);
				$notcertified_overiew_course = round(($certified_notoverview*100)/$total_overviewcourse_counter,2);
				
				if($certified_overiew_course >= 50){
					echo '<lable style="background-color:green;color: white;padding:5px 20px;">'.$certified_overiew_course.'% Certified</label>';
				}else{
					echo '<lable style="background-color:red;color: white;padding:5px 20px;">'.$certified_overiew_course.'% Certified</label>';
				}
				?>
		<?php }else if($division1 != ''){ ?>
		      <label>Company </label> : <?php echo $division1;?>
		<?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-3 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>
		<?php if(($department == 'Super Admin') OR ($department == 'Moodle Site Administrator') OR ($moodle_admin == 'true') OR ($division1 != '')) {  ?>
			<div class="col-lg-3 col-md-4 col-sm-4 col-12">
				<label>Department (D)</label> 
				<input class="division" type="input" name="department1" value="<?php echo $department1; ?>" id="department1">
			</div>
		<?php } ?>
		<?php if($division1 == '') {  ?>
		<div class="col-lg-3 col-md-4 col-sm-4 col-12">
			<label>Region (I)</label> 
			<input class="division" type="input" name="division" value="<?php echo $division; ?>" id="division">
		</div>
		<?php } ?>
		
	</div>
	<div class="row top">
		<div class="col-lg-3 col-md-4 col-sm-4 col-12 search">
			
		</div>
		
		
		<div class="col-lg-3 col-md-4 col-sm-4 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" <?php if($completion_status == '0'){ echo 'Selected'; } ?> >Both</option>
				<option value="1" <?php if($completion_status == '1'){ echo 'Selected'; } ?> >Complete</option>
    			<option value="2" <?php if($completion_status == '2'){ echo 'Selected'; } ?> >Incomplete</option>    			
    		</select>
    	</div>
		<?php if(isset($certified_courses)){ ?>
		<div class="col-lg-3 col-md-4 col-sm-4 col-12 compdropdown">
    		<label style="margin-bottom: 0px;">Certification</label>&nbsp;
    		<select class="form-control" name="certification" id="certification">
    			<option value="0" <?php if($certification_status == '0'){ echo 'Selected'; } ?> >Both</option>
				<option value="1" <?php if($certification_status == '1'){ echo 'Selected'; } ?> >Certified</option>
    			<option value="2" <?php if($certification_status == '2'){ echo 'Selected'; } ?> >Not certified</option>    			
    		</select>
    	</div>
		<?php } ?>
	</div>
	<div class="row top text-center">
		<div class="col-12">
			<label >Summary View</label>&nbsp;
				<input type="checkbox" name="summaryview" value="1" style="width:auto;" <?php echo ($summaryview == '1') ? 'checked="checked"' : '';?> >
			</div>
		</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='index.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%'";
	}
	if($showcourseids){
		$where_course .= " AND id IN (".$showcourseids.")";
	}
	if(!$showhiddencourses){
		$where_course .= " AND visible = '1' ";
	}
	$course_query = "SELECT * FROM ".$database_prefix."course WHERE 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="index.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>
	
	<hr style="border-color: #2d4ec8; margin-top: 2px; margin-bottom: 2px;">
	
	<div class="bottom-table" style="display:none;">
		<div class="row">
			<?php
				
				$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{ 
			    $customfield_query = "SELECT * FROM ".$database_prefix."user_info_field WHERE shortname = 'reportfield'";
				$result_customfield = mysqli_query($con,$customfield_query) or die(mysqli_error($con));
				$row_customfield = mysqli_fetch_array($result_customfield,MYSQLI_ASSOC);
				
				
				?>
					<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>  
								<?php 
								if(isset($row_customfield)){
								    echo '<th>'.$row_customfield['name'].'</th>';
								}
								?>
								<th>Department (D)</th>  
								<th>Region (I)</th>  
								<th>Course</th>  
								<th>Final Score</th>
								<th>Completion</th>  
								<th>Date completed</th>
								<?php 
								if(isset($certified_courses)){
									echo '<th>Certified</th>';
								}
								?>
							</tr>
						</thead>
						<tbody>
							<?php 
							
							foreach($final_result_array as $key=>$row){	
								
								$employee_id = $row['idnumber'];
								$username = $row['username'];
								$firstname = $row['firstname'];
								$lastname = $row['lastname'];
								
								$department1 = $row['department'];
								$phone1 = $row['phone1'];
								$institution = $row['institution'];
								$coursefullname = $row['fullname'];
								$timecompleted = $row['timemodified'];
								$showuntil = '';
								if(isset($row['showuntil'])){
									$showuntil = $row['showuntil'];
								}
								$finalgrade = $row['finalgrade'];
								$gradepass = $row['gradepass'];
								$courseid = $row['courseid'];
								echo '<tr>';
								echo '<td>'.$employee_id.'</td>';
								echo '<td>'.$username.'</td>';
								echo '<td>'.$firstname.'</td>';
								echo '<td>'.$lastname.'</td>';
							    if(isset($row_customfield)){
                                    $reportfield = $row['reportfield'];
                                    echo '<td>'.$reportfield.'</td>';
							    }
								if($department1){
									echo '<td>'.$department1.'</td>';
								}else{
									echo '<td>'.$phone1.'</td>';
								}
								echo '<td>'.$institution.'</td>';
								echo '<td>'.$coursefullname.'</td>';
								if($finalgrade > 0){
									echo '<td>'.round($finalgrade,2).'%</td>';
									if($gradepass == "0.00000"){
									    $gradepass = '100';
									}
									
									if($finalgrade >= $gradepass){
									    echo '<td>Complete</td>';
									    if($timecompleted){
        									echo '<td>'.date('d/m/y', $timecompleted).'</td>';
        									/*RW 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></td>';
        								}
									}else{
									    echo '<td>Incomplete</td>';
									    echo '<td></td>';
									}
								}else{
									echo '<td>-</td>';
									echo '<td>Incomplete</td>';
									echo '<td></td>';
								}

								if(isset($certified_courses)){
									if(in_array($courseid,$certified_courses)){
										if($gradepass == "0.00000"){
											$gradepass = '100';
										}
										
										if($finalgrade >= $gradepass){
											
											if($timecompleted){
												echo '<td class="greenbg">Completed '.date('Y', strtotime('+ 0 Year',$timecompleted)).'</td>';
												
											}else{
												echo '<td></td>';
											}
										}else{
											if($showuntil){
												if($showuntil == 'Certified until Dec 2020'){
													echo '<td class="redbg">'.$showuntil.'</td>';
												}else{
													echo '<td class="greenbg">'.$showuntil.'</td>';
												}
											}else{
												echo '<td class="redbg">Incomplete</td>';
											}											
										}
									}else{
										echo '<td></td>';
									}
								}
								echo '</tr>';
							}
							?>
						</tbody>
					</table>
					</div>
				<?php }
				?>
		</div>
	</div>
	<?php 
	if(isset($completion_status)){
		if($completion_status == '2'){ ?>
			<button type="submit" name="sendemail" class="btn btn-primary" value="1" style="display: block; margin: 0 auto; margin-bottom: 20px" onclick="return ask_confirm();">Send email to these incomplete users</button>
		<?php }
	} ?>
	</form>
	<footer>
		<p>Developed by Wallace Web Design and Development</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,
				"pageLength": <?php echo $perpage_record; ?>,
				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,10,11]
                        },
						
						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;
							}
						}
					}
					
				],
				
		    });
			
			
		});	
		
		function ask_confirm(){
			if (confirm('Do you really want to send email to these not completed users?')) {
				
			} else {
				return false;
			}
			
		}
	</script>
</html>