<?PHP
    include './conn.php';
	session_start();
	if($_SESSION['sid'] == session_id() && $_SESSION['user'] == "Staff")
	{	
		$staff_id = $_SESSION['staff_id'];
		
		
		$sql1 = "SELECT * FROM lms.staff WHERE staff_id = '".$staff_id."'";

        $sql2 = "SELECT * FROM lms.leave_statistics WHERE staff_id = '" . $staff_id . "' AND leave_type != 'annual leave'";

        $sql3 = "SELECT * FROM lms.leave_statistics WHERE staff_id = '" . $staff_id . "' AND leave_type = 'annual leave'";

		
		$result1 = mysqli_query($conn,$sql1);
		$result2 = mysqli_query($conn,$sql2);
        $result3 = mysqli_query($conn,$sql3);

		
		while($row1 = mysqli_fetch_array($result1))
		{
			$first_name = $row1['first_name'];
			$middle_name = $row1['middle_name'];
			$last_name = $row1['last_name'];
			$reg_year = $row1['reg_year'];
		}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Staff Profile</title>
<style type="text/css">
body {
	margin-left: 0px;
	margin-top: px;
	margin-right: 0px;
	margin-bottom: 0px;
}

</style>
<link href="../style.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="container">
  <div id="header">
    <div id="title">Lorenzo Group Leave Management</div>
    <div id="quick_links">
      <ul>
        <li><a class="home" href="index.php">Home</a></li>
        <li>|</li>
       
        <li><a class="logout" href="../logout.php">Logout</a></li>
        <li>|</li>
		<li><a class="greeting" href="#">Hi <?php echo $first_name." ".$last_name ?></a></li>
      </ul>
    </div>
  </div>
  <div id="side_bar">
  	<ul>
    	<li class="menu_head">Menu</li>
        <li><a href="apply_leave.php">Apply Leave</a></li>
        <li><a href="view_leave_history.php">View Leave History</a></li>
        <li><a href="view_leave_status.php">View Leave Status</a></li>
         <li><a href="view_profile.php">View Profile</a></li>
    </ul>
  </div>
  <div id="content_panel">
    <div id="heading">Staff Profile<hr size="2" color="#FFFFFF" ice:repeating=""/></div>
    <div id="form">
    <form method="post" action="approve_reject_db.php">
    <fieldset>
    <legend>Personal Information</legend>
    <label for="staff_id"><span>Staff ID </span>
    	<input type="text" name="staff_id" id="staff_id" readonly="true" value="<?php echo $staff_id ?>" style="background-color:#F6F6F6; color:#069" />
    </label>
    <label for="staff_name"><span>Staff Name </span>
    	<input type="text" readonly="true" value="<?php echo $first_name." ".$middle_name." ".$last_name ?>" style="background-color:#F6F6F6; color:#069" />
    </label>
    <label for="staff_name"><span>Joining Year </span>
    	<input type="text" readonly="true" value="<?php echo $reg_year ?>" style="background-color:#F6F6F6; color:#069" />
    </label>
    </fieldset>
    <br />
    <fieldset>
    <legend>Leave Statistics</legend>





    <div id="table">
    	<span><table border="1" bgcolor="#006699" >
				<tr>
                	<th width="120px">Leave Types</th>
					<th width="150px">Total Allowed Leaves P.A</th>
                    <th width="150px">Leaves Allowed P.M</th>
                    <th width="120px">Leaves Taken</th>
                    <th width="100px">Remaining Leaves</th>
                    <th width="200px">Maximum Allowed Leaves This Month</th>

				</tr>
			</table></span>

			
            <?php
while ($row2 = mysqli_fetch_array($result3)) {
    $leave_type2 = $row2['leave_type'];
    $maximum_leaves1 = $row2['maximum_leaves']; //=default=x
    $maximum_leaves2 =$maximum_leaves1 / 12;
    $leaves_taken2 = $row2['leaves_taken']; //=y
    $year2 = substr($row2['year'], 5, 2);
    $reg_year2 = substr($reg_year, 5, 2);

    // Initialize $maximum_leaves_this_year with a default value
    $maximum_leaves_this_year2 = $maximum_leaves2;
	if ($reg_year2 >= $year2) {
        $maximum_leaves_this_year2 = $maximum_leaves2; // for year 1
        $remaining_leaves2 = $maximum_leaves_this_year2 - $leaves_taken2; //=z=x-y


        // Update the database with the new value using prepared statement
        $update_query2 = "UPDATE leave_statistics SET maximum_leaves_this_year = ?, year = NOW() WHERE staff_id = ? AND leave_type = ?";
       $stmt = mysqli_prepare($conn, $update_query2);

        if ($stmt) {
            mysqli_stmt_bind_param($stmt, "iss", $maximum_leaves_this_year2,  $staff_id, $leave_type2);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_close($stmt);
        } else {
            // Handle the error, e.g., by printing an error message or logging
            echo "Error in prepared statement: " . mysqli_error($conn);
        }
    }

        // Assuming you have retrieved the dates from the database
        $year2 = ($row2['year']);
        $reg_year2 = ($reg_year);

// Extract year and month from each date
$year1 = (int)substr($year2, 0, 4);
$month1 = (int)substr($year2, 5, 2);
$year2 = (int)substr($reg_year2, 0, 4);
$month2 = (int)substr($reg_year2, 5, 2);

// Calculate the difference in months
$diff_in_years = $year1 - $year2;
$diff_in_months = $diff_in_years * 12 + ($month1 - $month2);




        $y = $diff_in_months * 1.75;

        $remaining_leaves2 = $y - $leaves_taken2; //=z=x-y

        $maximum_leaves_this_year2 = $maximum_leaves2 + $remaining_leaves2; // for following years= x+y



        // Update the database with the new value using prepared statement
        $update_query2 = "UPDATE leave_statistics SET maximum_leaves_this_year = ? WHERE staff_id = ? AND leave_type = ?";//
        $stmt = mysqli_prepare($conn, $update_query2);

        if ($stmt) {
            mysqli_stmt_bind_param($stmt, "iss", $maximum_leaves_this_year2, $staff_id, $leave_type2);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_close($stmt);
        } else {
            // Handle the error, e.g., by printing an error message or logging
            echo "Error in prepared statement: " . mysqli_error($conn);
        }
    

    echo
     "<table border=\"1\">
            <tr>
                <td width=\"120px\">" . $leave_type2 . "</td>
                <td width=\"150px\">" . $maximum_leaves1 . "</td>
                <td width=\"150px\">" . $maximum_leaves2 . "</td>
                <td width=\"120px\">" . $leaves_taken2 . "</td>
                <td width=\"100px\">" . $remaining_leaves2 . "</td>
                <td width=\"200px\">" . $maximum_leaves_this_year2 . "</td>

            </tr>
        </table>";
        
}
?>     

    </div>


    
<div id="table">        
    <span><table border="1" bgcolor="#006699" >
				<tr>
                	<th width="120px">Leave Types</th>
					<th width="200px">Default Allowed Leaves</th>
                    <th width="120px">Leaves Taken</th>
                    <th width="200px">Remaining Leaves</th>
                    <th width="200px">Maximum Allowed Leaves This Year</th>

				</tr>
			</table></span>
            <?php
while ($row2 = mysqli_fetch_array($result2)) {
    $leave_type = $row2['leave_type'];
    $maximum_leaves = $row2['maximum_leaves']; //=default=x
    $leaves_taken = $row2['leaves_taken']; //=y
    $remaining_leaves = $maximum_leaves - $leaves_taken; //=z=x-y
    $year = substr($row2['year'], 0, 4);
    $reg_year = substr($reg_year, 0, 4);

    // Initialize $maximum_leaves_this_year with a default value
    $maximum_leaves_this_year = $maximum_leaves;
	if ($reg_year == $year) {
        $maximum_leaves_this_year = $maximum_leaves; // for year 1

        // Update the database with the new value using prepared statement
        $update_query = "UPDATE leave_statistics SET maximum_leaves_this_year = ?, year = NOW() WHERE staff_id = ? AND leave_type = ?";
        $stmt = mysqli_prepare($conn, $update_query);

        if ($stmt) {
            mysqli_stmt_bind_param($stmt, "iss", $maximum_leaves_this_year,  $staff_id, $leave_type);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_close($stmt);
        } else {
            // Handle the error, e.g., by printing an error message or logging
            echo "Error in prepared statement: " . mysqli_error($conn);
        }
    }

    if ($reg_year < $year) {
        $maximum_leaves_this_year = $maximum_leaves + $remaining_leaves; // for following years= x+y

        // Update the database with the new value using prepared statement
        $update_query = "UPDATE leave_statistics SET maximum_leaves_this_year = ? WHERE staff_id = ? AND leave_type = ?";
        $stmt = mysqli_prepare($conn, $update_query);

        if ($stmt) {
            mysqli_stmt_bind_param($stmt, "iss", $maximum_leaves_this_year, $staff_id, $leave_type);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_close($stmt);
        } else {
            // Handle the error, e.g., by printing an error message or logging
            echo "Error in prepared statement: " . mysqli_error($conn);
        }
    }

    echo
     "<table border=\"1\">
            <tr>
                <td width=\"120px\">" . $leave_type . "</td>
                <td width=\"200px\">" . $maximum_leaves . "</td>
                <td width=\"120px\">" . $leaves_taken . "</td>
                <td width=\"200px\">" . $remaining_leaves . "</td>
                <td width=\"200px\">" . $maximum_leaves_this_year . "</td>

            </tr>
        </table>";
        
}
?>
            
    </div>


    
    </fieldset>
    
    </form>  
    </div>
  </div>

  <div id="footer">
  	<p><br />&copy; Lorenzo Group <br><br>All Rights Reserved.</p>
  </div>
</div>
</body>
</html>
<?php
	}
	else
	{
		header("Location: ../index.html");
	}
	mysqli_close($conn);
?>
