I can calculate the leave days for current year only, I don't know how to carry forward to the next year. How to bring forward the balance to the next year?
If the balance is 14, only 10 days will be bring to the next year.
If the balance is 3, then the 3 days will bring to the next year. (maximum 10 days)
Hope you all can help me. Thank you.
<? $Connect = mysql_connect("localhost","root","");
mysql_select_db("help");
$result=mysql_query("select Entitlement from Staff where StaffID = '$username'") ;
$number_of_array = mysql_num_rows($result);
while ($number_of_array = mysql_fetch_array($result)){
"$number_of_array[Entitlement]";
}
mysql_close(); ?>
<? $Entitlement ?>
------------------------------------------------------
<? $Connect = mysql_connect("localhost","root","");
mysql_select_db("help");
$result=mysql_query("select YearStarted from Staff where StaffID = '$username'") ;
$number_of_array = mysql_num_rows($result);
while ($number_of_array = mysql_fetch_array($result)){
"$number_of_array[YearStarted]";
}
mysql_close(); ?>
<? $YearStarted ?>
-------------------------------------------------------
<?php
$CurrentYear = date("Y");
//current year would be 2003, Y in the date function is the Year in 4 digits
//select YearStarted from database where employee is john
//make YearStarted into an row, and make the variable name YearStarted
$yearsworked = $CurrentYear - $YearStarted;
if ($yearsworked >= 1)
{
$BonusLeave = 0;
}
if ($yearsworked >= 2)
{
$BonusLeave = 0;
}
if ($yearsworked >= 3)
{
$BonusLeave = 1;
}
if ($yearsworked >= 4)
{
$BonusLeave = 2;
}
if ($yearsworked >= 5)
{
$BonusLeave = 3;
}
if ($yearsworked >= 6)
{
$BonusLeave = 4;
}
.
.
.
if ($yearsworked >= 23)
{
$BonusLeave = 21;
}
if ($yearsworked >= 24)
{
$BonusLeave = 22;
}
if ($yearsworked >= 25)
{
$BonusLeave = 23;
}
echo $BonusLeave;
?>
-----------------------------------------------------
<? echo $FromDate ?>
-----------------------------------------------------
<? echo $ToDate ?>
-----------------------------------------------------
<?php
function NumHolidays($Start, $End)
{
$dbConn = MySQL_Connect("localhost", "root", "") or
Die("Error connecting to Server");
MySQL_Select_DB("help") or
Die("Error Connecting to Database");
$sSQL = "SELECT * FROM Holidays";
$recHolidays = MySQL_Query($sSQL) or
Die("Invalid Query");
$NumHolidays = 0;
while ($Holiday = MySQL_Fetch_Array($recHolidays))
{
$dtDate = StrToTime($Holiday["Date"]);
$dtStart = StrToTime($Start);
$dtEnd = StrToTime($End);
if (Date("m", $dtStart) < Date("m", $dtDate))
{
if (Date("m", $dtDate) <= Date("m", $dtEnd))
{
if (Date("d", $dtDate) <= Date("d", $dtEnd))
{
$NumHolidays++;
}
}
}
else if (Date("m", $dtStart) == Date("m", $dtDate))
{
if (Date("d", $dtStart) <= Date("d", $dtDate))
{
if (Date("m", $dtDate) <= Date("m", $dtEnd))
{
if (Date("d", $dtDate) <= Date("d", $dtEnd))
{
$NumHolidays++;
}
}
}
}
}
return $NumHolidays;
}
function count_workdays($date1,$date2){
$firstdate = strtotime($date1);
$lastdate = strtotime($date2);
$firstday = date(w,$firstdate);
$lastday = date(w,$lastdate);
$totaldays = intval(($lastdate-$firstdate)/86400)+1;
//check for one week only
if ($totaldays<=7 && $firstday <= $lastday){
$workdays = $lastday - $firstday+1;
//check for weekend
if ($firstday==0){
$workdays = $workdays-1;
}
if ($lastday==6){
$workdays = $workdays-1;
}
}else { //more than one week
//workdays of first week
if ($firstday==0){
//so we don't count weekend
$firstweek = 5;
}else {
$firstweek = 6-$firstday;
}
$totalfw = 7-$firstday;
//workdays of last week
if ($lastday==6){
//so we don't count sat, sun=0 so it won't be counted anyway
$lastweek = 5;
}else {
$lastweek = $lastday;
}
$totallw = $lastday+1;
//check for any mid-weeks
if (($totalfw+$totallw)>=$totaldays){
$midweeks = 0;
} else { //count midweeks
$midweeks = (($totaldays-$totalfw-$totallw)/7)*5;
}
//total num of workdays
$workdays = $firstweek+$midweeks+$lastweek;
}
return ($workdays);
} //end funtion count_workdays()
$date1 = "$FromDate";
$date2 = "$ToDate";
$leave_to_date = 0;
$Duration = count_workdays($date1, $date2) - NumHolidays($date1, $date2);
$Balance = $Entitlement - $leave_to_date - $Duration + $BonusLeave;
echo $Duration ; ?>
-----------------------------------------------------
<?php echo $Balance; ?>
-----------------------------------------------------
<? $Connect = mysql_connect("localhost","root","");
mysql_select_db("help");
$TodayDate = strftime("%Y-%m-%d");
$TodayTime = strftime("%H:%M:%S");
$Duration = count_workdays($FromDate,$ToDate);
$result=mysql_query("insert into LeaveApplication (ApplicationNumber, StaffID, LeaveType, JobTitle, FromDate, ToDate, Duration, Reason, TodayDate, TodayTime, YearStarted, BonusLeave, Balance, Subjects1, DateAffected1, DateReplaced1, Subjects2, DateAffected2, DateReplaced2, Subjects3, DateAffected3, DateReplaced3, Subjects4, DateAffected4, DateReplaced4, Subjects5, DateAffected5, DateReplaced5, Subjects6, DateAffected6, DateReplaced6) values ('$ApplicationNumber', '$username', '$LeaveType', '$JobTitle', '$FromDate', '$ToDate', '$Duration', '$Reason', '$TodayDate', '$TodayTime', '$YearStarted', '$BonusLeave', '$Balance', '$Subjects1', '$DateAffected1', '$DateReplaced1', '$Subjects2', '$DateAffected2', '$DateReplaced2', '$Subjects3', '$DateAffected3', '$DateReplaced3', '$Subjects4', '$DateAffected4', '$DateReplaced4', '$Subjects5', '$DateAffected5', '$DateReplaced5', '$Subjects6', '$DateAffected6', '$DateReplaced6')");
mysql_close();
?>
