<?php include("cn.php");?>
<?php $title = "Employees Report";?>
<!DOCTYPE html>
<html lang="en">
    <style>html, body {
     max-height: 50%;
}</style>
<?php include("head-tag.php");
$sql = mysqli_query($connect, "select count(*) as total from employee where Status = 'Active'");
$data = mysqli_fetch_assoc($sql);



$report_type = $_REQUEST["report"];
$designation = $_REQUEST["designation"];

if($_REQUEST["start_date"]!="" && $_REQUEST["end_date"]!=""){
    $start_date = date('Y-m-d',strtotime($_REQUEST["start_date"]));
$end_date = date('Y-m-d',strtotime($_REQUEST["end_date"]));
    $query = "and $report_type >= '$start_date' and $report_type <= '$end_date'";
}
if($designation != ""){
    $query = $query." and sanctionposts.Designation='$designation'";   
}
?>

<body>

<div class="inner-wrapper">
    
<?php include("pre-loader.php");?>
<?php include("header.php");?>

<div class="page-wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-xl-12 col-lg-12  col-md-12">
<?php include("tabs.php");?>

<div class="card shadow-sm ctm-border-radius">
<div class="card-body align-center">
<div class="tab-content" id="pills-tabContent">

<div class="tab-pane fade show active" id="employee-report" role="tabpanel" aria-labelledby="employee-report-tab">
    <div class="card-header">
                        <h4 class="card-title mb-0"> Active Employees Report
                        </h4> 
                    </div>
    <div class="card-body align-center">
        <form method = "Post" action = "">
            <div class="row filter-row">
<div class="col">
<div class="form-group mb-xl-0 mb-md-2 mb-sm-2">
<select class="form-control select" name="designation"/>
<option value="">Select Designation</option>
<?php $sql = mysqli_query($connect, "select count(employee.Id) as total, posts.* 
from posts 
inner join sanctionposts ON posts.Id = sanctionposts.Designation
left join employee ON employee.Designation = sanctionposts.Id
group by posts.Id");
while($data_m = mysqli_fetch_assoc($sql)){?>
	<option value="<?php echo $data_m["Id"]?>" <?php echo ($data_m["Id"] == $designation )? "selected" : "" ;?>><?php echo $data_m['Title'];?></option>
<?php }?>
</select>
</div>
</div>
<div class="col">
<div class="form-group mb-xl-0 mb-md-2 mb-sm-2">
<select class="form-control select" name="report" required/>
<option value="">Select Report Type </option>
<option value="DateOfBirth" <?php echo ($report_type == "DateOfBirth" )? "selected" : "" ;?>>Date Of Birth</option>
<option value="AppointmentDate" <?php echo ($report_type == "AppointmentDate" )? "selected" : "" ;?>>Date Of Appointment</option>
<option value="SuperannuationDate" <?php echo ($report_type == "SuperannuationDate" )? "selected" : "" ;?>>Date Of Superannuation</option>
<option value="PostingDate" <?php echo ($report_type == "PostingDate" )? "selected" : "" ;?>>Date of Posting</option>
</select>
</div>
</div>
<div class="col">
<div class="form-group mb-lg-0 mb-md-2 mb-sm-2">
<input type="text" name="start_date" value="<?php echo ($start_date != "" )? date("d-m-Y", strtotime($start_date)) : "" ;?>"
class="form-control datetimepicker" placeholder="From" required>
</div>
</div>
<div class="col">
<div class="form-group mb-lg-0 mb-md-0 mb-sm-0">
<input type="text" name="end_date" value="<?php echo ($end_date != "" )? date("d-m-Y", strtotime($end_date)) : "" ;?>"
class="form-control datetimepicker" placeholder="To" required>
</div>
</div>
<div class="col">
<button type="submit" class="btn btn-theme button-1 ctm-border-radius text-white btn-block"><span><i class="fa fa-filter"></i></span> Filter</button>    
</div>
</div>
        </form>

</div>                
    <div class="card-body">
        <?php if($start_date !="" && $end_date!="" && $report_type != ""){ ?>
        <div class="table-responsive table-togglable table-hover" >
            <table id="empreport" class="table table-striped table-bordered">
		<thead>
		<tr>
          <th class="text-center">DOB</th>
		  <th class="text-center">DOR</th>
		  <th class="text-center">DOA</th>
		  <th class="text-center">DOP</th>		    
		  <th class="text-center">Personnel No.</th>  
		  <th class="text-center">Name</th>
		  <th class="text-center">Designation</th>
          <th class="text-center">Place Of Posting</th>
          <th class="text-center">Section</th>
		  
		</tr>
		</thead>
		<tbody>
						<?php 
						$sql = mysqli_query($connect, "select employee.*, posts.Title, offices.DistrictName, offices.Type,Status 
							from employee 
							left join sanctionposts ON sanctionposts.Id = employee.Designation
							inner join posts ON sanctionposts.Designation = posts.Id
							inner join offices ON sanctionposts.District = offices.Id
							where Status = 'Active' $query order by $report_type asc");
						while($data = mysqli_fetch_assoc($sql)){?>
							<tr>
							<td class="text-center"><?php echo ($data["DateOfBirth"]!="")?date("d/m/Y",strtotime($data["DateOfBirth"])):"";?> </td>
                            <td class="text-center"> <?php echo ($data["SuperannuationDate"]!="")?date("d/m/Y",strtotime($data["SuperannuationDate"])):"";?></td>
                            <td class="text-center"> <?php echo ($data["AppointmentDate"]!="")?date("d/m/Y",strtotime($data["AppointmentDate"])):"";?></td>
                            <td class="text-center"> <?php echo ($data["PostingDate"]!="")?date("d/m/Y",strtotime($data["PostingDate"])):"";?></td>
                            
							<td class="text-center"><a class="btn btn-outline-success text-dark btn-sm"><?php echo $data["PersonnelNumber"]?></a></td>
   
                            <td>
                                <a href="details.php?id=<?php echo $data["Id"]?>" class="avatar"><img src="<?php echo $data["Photo"]?>" alt="profiles/user.png" class="img-fluid" /></a>
                                <h2><a href="details.php?id=<?php echo $data["Id"]?>"><?php echo  $data["FullName"]?></a></h2>
                            </td>
                            
                            <td class="text-center"><a class="btn btn-outline-danger btn-sm"> <?php echo $data["Title"]?> </a></td>
                            
                            <td class="text-center"><a class="btn btn-outline-warning btn-sm"><?php echo $data["DistrictName"]?> </a></td>
                            
                            <td class="text-center"> 
							<?php
								$list_sql = mysqli_query($connect,"select sections.*, employeesection.* from 
								employeesection
								INNER JOIN sections on sections.Id = employeesection.Section
								where employeesection.Employee = '".$data["Id"]."'");
								while($data_list = mysqli_fetch_assoc($list_sql)){
								    echo $data_list["SectionName"].($data_list["Type"]);
									echo ",";echo "  ";'","';
								}
							?>
							
							</td>
                            
                            
                            
                        </tr>
						<?php }?>
						
                    </tbody>
		</table>
        </div>
        <?}?>
    
    </div>
</div>


</div>

</div>
</div>
</div>
</div>
</div>
</div>

</div>


<div class="sidebar-overlay" id="sidebar_overlay"></div>

<?php include("scripts.php");?>
<script>
$('#empreport').DataTable( {
            dom: 'B<clear>frtip',
            "bFilter": true,
            "bSort": false,
            "bPaginate": true,
            "order": [ 0, 'desc' ],
            "lengthMenu": [ [10, 50, 100, -1], [10, 50, 100, "All"] ],
            columnDefs: [
                {
                    targets: 1,
                    className: 'noVis'
                },
                {
                targets: [<?php echo ($report_type!="DateOfBirth")?"0":"";?>,
                <?php echo ($report_type!="SuperannuationDate")?"1":"";?>,
                <?php echo ($report_type!="AppointmentDate")?"2":"";?>,
                <?php echo ($report_type!="PostingDate")?"3":"";?>],
                visible: false
                }
            ],
            buttons: [
                {
                    extend: 'copy',
                    title: 'Employees Report (<?php echo $report_type;?>)',
                    exportOptions: {
                        columns: ':visible'
                    }
                    
                },
                {
                    extend: 'excel',
                    title: 'Employees Report (<?php echo $report_type;?>)',
                    exportOptions: {
                        columns: ':visible'
                    }
                },
                {
                extend: 'print',
                orientation: 'landscape',
                title: '<h3 style="text-align:center">Employees Report (<?php echo $report_type;?>)</h3>',
                pageSize: 'LEGAL',
                exportOptions: {
                    columns: ':visible'
                }
                
            },
                {
                    extend: 'pdf',
                    title: 'Employees Report (<?php echo $report_type;?>)',
                    orientation: 'portrait',
                    pageSize: 'A4',
                    exportOptions: {
                        columns: ':visible'
                    }
                    
                },
                
                {
                extend: 'colvis',
                collectionLayout: 'fixed three-column',
                columns: ':not(.noVis)',
                postfixButtons: [ 'colvisRestore' ]
            }
            ],
            language: {
            buttons: {
                colvis: 'Select Fields'
                }
            },
           
        } );</script>

</body>
<script>
    $(document).ready(function() {
        
    });
</script>
</html>