carry forward
Posted: Mon Aug 04, 2003 7:25 am
by superman
i don't know how to carry forward the result?
for example, for the first 2 years, a new staff does not has bonus leave, on the third year onwards, he has 1 day increase.
by default, he has 18 days leave, the maximum days he can bring to next year are 10 days. If he already applied 12 days, than he can carry forward 6 days to the next year.
i explain more by giving this example.
Start working at 2000
(default) 18
(apply leave) -15
______
3 days
(bring forward to 2001) 3 days
-------------------------------------------------
2001
(default) 18
(apply leave) -13
______
5 days
(carried forward from 2000) 3 days
______
8 days
(bring forward to 2002) 8 days
------------------------------------------------------
2002
(default) 18
(apply leave) -11
______
7 days
(carried forward from 2001) 8 days
______
15 days
(bring forward to 2003) maximum 10 days
-------------------------------------------------------
2003
(default) 18
(bonus leave after 1 year) +1
(apply leave) -14
______
5 days
(carried forward from 2002) 10 days
______
15 days
(bring forward to 2004) maximum 10 days
Posted: Mon Aug 04, 2003 8:01 pm
by Jade
why don't you make a table for all those fields. For example, you could have yearstarted, yearnow, leave, bounusleave. Then as the person uses his leave apply that to the database. Then on the next year add 1 day to leave, and calculate how much bouns leave they have at the next end of the year, add more days to leave, etc.
Jade
Posted: Tue Aug 05, 2003 12:33 am
by superman
i've created a table for that, but i don't know how to carry forward from the previous year to the next year
Posted: Wed Aug 06, 2003 9:37 am
by superman
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();
?>
Posted: Thu Aug 07, 2003 11:53 pm
by superman
i just know how to calculate for the current year only, for example, 2003, if come to 2004, i don't know how to carry forward from 2003 to 2004.
can someone help me?
Posted: Tue Aug 12, 2003 2:58 am
by superman
Is this code correct? any problems? can it be run?
Code: Select all
$Table = 'Staff';
$MonthDay = strftime("%m-%d");
$Month = '01-01';
$CurrentYear = date("Y");
$query = "Select StaffID, Entitlement, YearStarted from $Table where StaffID='$username'";
$result = mysql_query($query);
if ($MonthDay == $Month) {
while($row = mysql_fetch_array($result))
{
$TotalYears = $CurrentYear - $row['YearStarted'];
$Years = $row['Entitlement'];
if ($TotalsYear <= 2) {
if ($Years >= 10) {
$CarryForward = 10;
$Years = 18 + $CarryForward;
}
else {
$Years = 18 + $CarryForward;
}
}
else if ($Years >= 10) {
$carryForward = 10;
$Years = 19 + $CarryForward;
}
else {
$Years = 19 + $CarryForward;
}
}
}