# carry forward

A

#### Anonymous

##### Guest
i don't know how to carry forward the result?

for example, for the first 2 years, he does not has bonus leave, on the third year onwards, he has 1 day increase.

by default, he has 18 days leave, on the third year, he can carry forward the maximum 10 days(not 18 days) from the previous year, now, he has 28 days leave plus 1 day bonus leave = 29 days.

same with the fourth year, he can carry forward maximum 10 days, so, he has 39 days leave plus 1 day bonus leave = 40 days.

if he apply 2 days leave in the first 2 years, the balance is 16 days. on the third year, he only can carry forward 10 days only, not 16 days.

if he apply 12 days leave in the first 2 years, the balance is 6 days. on the third year, he only can carry forward 6 days only, not 10 days.

this is the problem that i don't know how to fix, can you teach me?

can someone give suggestion how to use this in mysql database to carry forward from the previous year & add to next year? thanx.

This is complex stuff you are asking here. The actual will be not all that difficult but there is a lot of logical thinking involved. You are probably capable of coding this yourself, what YOU need to do is sit down, plan out the input requirements and output requirements and think of the logic behind what you need it to do.

Logic.

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

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();
?>

icq online yahhhhhhhhhh!!![/img][/list][/code]

irvine said:
icq online yahhhhhhhhhh!!![/img][/list][/code]

What the ...?

am i need to create a field in table?
to store the balance in 1990, 1991, 1992....2003, 2004..??

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? thank you

i think whot to you need create new table and storing inside next field:
Code:
``````UsedId
PrevYear
CurrentYear
TotalYear
TotalBalance``````
i think what you understand what i mean :wink:

can you explain it more, i'm not really understand :wink:
thanks

superman said:
can you explain it more, i'm not really understand :wink:
thanks
i'm try:
as i understand you cannot collect and process bonus system, correct? if yes than....
you have a main table, it's tabe of youre users(workers) in this table you store all info about users(workers) and you try calculate bonus via PHP.
I'm propose create new table where you storing all balace, but you storing for prevouse year for self current year if not finishing need to calculate, total years practics, and total balance for all work practics.....
understand?
By the way i recommend to you before somthing create think about it! What exactly you want from programm. Algorytm....

i have another table called 'LeaveApplication':

ApplicationNumber, StaffID, LeaveType, JobTitle, FromDate, ToDate, Duration, Reason, TodayDate, TodayTime, YearStarted, BonusLeave, Balance

if a staff apply leave, he will select the date from & date to, then calculate the duration, bonus leave & the balance. all the details store in this table

superman said:
i have another table called 'LeaveApplication':

ApplicationNumber, StaffID, LeaveType, JobTitle, FromDate, ToDate, Duration, Reason, TodayDate, TodayTime, YearStarted, BonusLeave, Balance

if a staff apply leave, he will select the date from & date to, then calculate the duration, bonus leave & the balance. all the details store in this table
i'm propose to you count bonus and live days bonus for current year total years!
i think what it's easy count all mathematics.

this is what i'm think, any problems with this code? can this happen?

Code:
``````\$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;
}

}
}``````

are you know how to run the script once when reach that time?
then how to update all rows in the table?

superman said:
are you know how to run the script once when reach that time?
put all info in session or cookie....
superman said:
then how to update all rows in the table?
put in array and than cycle....

run the script one year one time..
can use session? how, any tutorial that teach this function?

WiZARD said:
superman said:
then how to update all rows in the table?
put in array and than cycle....

I haven't really been following this thread (the whole thing has confused me, actually), but it seems to me that a lot of this could be done with MySQL queries instead of in PHP. There's seldom any reason to individually process every single row in a MySQL table with PHP. MySQL has lots of built-in functions, and I encourage you to try to find such a solution, as it'll save you tons of processing.

i got this error message:
Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in c:\phpweb/approve_staff.php on line 316

Code:
``````\$table = 'Staff';
\$baseleave = 18; //leaves days in 1 year
\$cy = date("Y"); //current year
\$query = "SELECT StaffID, Name, Entitlement, YearStarted FROM \$table";
\$result = mysql_query(\$query);
while(\$row = mysql_fetch_array(\$result))
{
\$years = \$cy - \$row['YearStarted']; // years in service at company
\$id = \$row['StaffID']; // employee id type thing
\$Balance = \$row['Entitlement']; // number of days available for leave
\$name = \$row['Name'];
if (\$Balance >= 10) { \$Balance = 10+\$baseleave; } //if 10 or more days left, carry them forward + baseleave, regardless of years
if (\$years == 3) // if over three years, add the bonus day
{
\$Balance += 1;
}
\$update = "UPDATE \$table SET Entitlement=\$Balance where StaffID=\$id";
\$r_update = sql_query(\$update);
if (mysql_rows_affected(\$r_update) == 1) { echo "Echo \$name (\$id) updated from \$row['Entitlement'] to \$Balance for this year<br>\n"; } //only 1 row was updated as should be
else { echo "Error, either more than one, or no rows were updated!<br>\n"; }
}``````

It would seriously help if you'd tell us which line is line 316.