Leave quota error.

[irvinehooi]

Hi,

Recently I found out that the OrangeHRM system allow my company staff to apply leave even their leave quota is not enough or ZERO.

Now the remaining leave for that person who reached zero become a negative number.

I thought the system will automatically stop the user to apply leave or give an error message when their leave quota reached zero.

Now my HR Manager is not very happy with this happen. Because everything will soon going to mesh up if this continue to happen. Guys please, can somebody help me to solve this problem. I really need your help.

I'm using stable version 2.4.1

Thank you very much.

[nilanga]

Hi

Thank you.There is an issue.It will be solve as soon as possible.

Regards
Nilanga

[siddeek]

Hi,

This is the current behavior in OrangeHRM.
We didn't restrict this because there are situation that employees should be able to apply leave even the quota is exceeded.
One example is there is a situation that an employee got accident and he hospitalized for 2 months.
In this case he should be able to apply a leave then supervisor or admin can do the approving.

"Now the remaining leave for that person who reached zero become a negative number"

With this management can get an idea that the quota is over for this employee.

[ricardo]

Hi,

I understand why it's done. But if an employee is in hospital then it the holidays type wouldn't be holidays IT WOULD BE sick leave.

Could you please supply some guide lines in how to limit leave submissions when there isn't a sufficient allowance??

Kind regards
Ric

[lostsoul82]

I would like to thank orangehrm for creating such a wonderful software.
It has saved me plenty of time to create a leave system from scratch.
I have found support and help from forumers' answers and now I have decided to contribute on my own.
I have managed to fix the bug but I'm not well versed in php or javascript so if there are any comments about my code feel free to let me know.

Insert this code in \OrangeHRM\2.5.0.2\htdocs\orangehrm-2.5.0.2\templates\leave\leaveApply.php line 131

OR

after this if statement:

if (strToDate(date1, format) > strToDate(date2, format)) {
err = true;
msg += " - <?php echo $lang_Leave_Common_InvalidDateRange; ?>\n"
}

CODE TO INSERT:

//Added by IT Admin Ryan : These code is to calculate the number of days applied by employee minus PH,Sat,Sun
//Add these codes in \OrangeHRM\2.5.0.2\htdocs\orangehrm-2.5.0.2\templates\leave\leaveApply.php line 131
//with the number of leave that he is currently having, if lesser will prompt error message.
//variables to store PH, no_of_days_alloted and leave_taken
var holidays = new Array();
var no_of_days_allotted;
var leave_taken;

//Check this condition only when Annual Leave is selected, in my case is LTY001
if (document.frmLeaveApp.sltLeaveType.value == "LTY001") {

<?php
//get employee_id, mutltiply by 1 to remove leading zeros
$employee_id = $_SESSION['empID'] * 1;

if ($employee_id != NULL) { //Added by me cos if i didn't add this when admin try assign leave there would be errors
$username="root"; //username root
$password= NULL; //using default password of database which is NULL
$database="hr_mysql";

mysql_connect("127.0.0.1",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM hs_hr_holidays";
mysql_query($query); //Extract SQL query from database
$result=mysql_query($query); //put contents of query into an array $result
$num=mysql_num_rows($result); //$num counts the number of rows in the query

// for loop to extract PH date to array $holidays[] and copy variable from PHP to javascript
$i=0;

while ($i < $num) {
$holidays[]=mysql_result($result,$i,"date"); ?>
var j = <?= $i ?>;
holidays[j] = "<?= $holidays[$i] ?>";
<?php $i++;
}
?>

//workdays is number of working days after minus off sat/sun and PH
var workdays = calcBusinessDays(date1,date2,holidays);


<?php

//LTY001 is my annual leave
$query="SELECT * FROM hs_hr_employee_leave_quota where employee_id = $employee_id and leave_type_id = 'LTY001'";
mysql_query($query);
$result=mysql_query($query);
mysql_close();

//We do not have to use the loop while $i < $num as it contains only one row
//this 2 variables is used to calculate remaining leave balance of employee (no_of_days_alloted - leave_taken)
$no_of_days_allotted=mysql_result($result,0,"no_of_days_allotted");
$leave_taken=mysql_result($result,0,"leave_taken");
?>

//convert php coding to javascript
no_of_days_allotted = "<?= $no_of_days_allotted ?>";
leave_taken = "<?= $leave_taken ?>";

//reject the leave application and pop up error message if leave balance is not enough
if ((no_of_days_allotted - leave_taken) < workdays) {
err = true;
msg += " - <?php echo $lang_Leave_Error_LeaveExceeded; ?>\n" //Define our own error message
}

<?php } ?> //End If Loop for PHP coding
}

//This function calculates business dates by subtracting sat and sun and PH
function calcBusinessDays(dDate1, dDate2, dHolidays) { // input given as Date objects

//This formula helps to substract sat and sun with To and From Dates
var iWeeks, iDateDiff, iAdjust = 0;
var FormattedDate1 = new Date(dDate1.replace(/-/g,"/"));
var FormattedDate2 = new Date(dDate2.replace(/-/g,"/"));

if (FormattedDate2 < FormattedDate1) return -1; // error code if dates transposed

var iWeekday1 = FormattedDate1.getDay(); // day of week
var iWeekday2 = FormattedDate2.getDay();

iWeekday1 = (iWeekday1 == 0) ? 7 : iWeekday1; // change Sunday from 0 to 7
iWeekday2 = (iWeekday2 == 0) ? 7 : iWeekday2;

if ((iWeekday1 > 5) && (iWeekday2 > 5)) iAdjust = 1; // adjustment if both days on weekend

iWeekday1 = (iWeekday1 > 5) ? 5 : iWeekday1; // only count weekdays
iWeekday2 = (iWeekday2 > 5) ? 5 : iWeekday2;

// calculate difference in weeks (1000mS * 60sec * 60min * 24hrs * 7 days = 604800000)
iWeeks = Math.floor((FormattedDate2.getTime() - FormattedDate1.getTime()) / 604800000)

if (iWeekday1 <= iWeekday2) {
iDateDiff = (iWeeks * 5) + (iWeekday2 - iWeekday1)
}

else {
iDateDiff = ((iWeeks + 1) * 5) - (iWeekday1 - iWeekday2)
}

iDateDiff -= iAdjust // take into account both days on weekend

//this formula below helps to calculate public holidays from the To and From Dates using strToDate
//If PH falls on Saturday or Sunday it is not subtracted, I assume that we add in the PH manually in the leave system as weekday
var Formatholidays;
var Dformat = YAHOO.OrangeHRM.calendar.format;
var Strholidays;
var k=0;
for (k=0; k < dHolidays.length ;k++)
{
Strholidays = strToDate(dHolidays[k], Dformat);
Formatholidays = new Date(dHolidays[k].replace(/-/g,"/"));

if (strToDate(dDate1,Dformat) <= Strholidays && Strholidays <= strToDate(dDate2,Dformat)
&& Formatholidays.getDay() != 6 && Formatholidays.getDay() != 0) {
iDateDiff--;
}

}

return (iDateDiff + 1); // add 1 because dates are inclusive

}

FOLLOWING THAT
Insert this code in \OrangeHRM\2.5.0.2\htdocs\orangehrm-2.5.0.2\language\default\language_default_full.php line 1834(neater) or enter it in any new line

CODE TO INSERT:

$lang_Leave_Error_LeaveExceeded = "You have exceeded your leave quota";[/b]

Links that help me to solve the bug:
http://www.freewebmasterhelp.com/tutorials/phpmysql/4/
http://snipplr.com/view/4086/calculate- ... two-dates/
http://stackoverflow.com/questions/3361 ... ys-in-php/

That's all.
Any problems can feedback.
I will try my best to help.
Cheers!

Regards,
Ryan
IT Administrator

[ricardo]

Hello Ryan,

Many thanks for the script you've provided and I'm very gratefull for the time you've spent doing it.
There only one thing that needs to be ammended otherwise the script won't work

$query="SELECT * FROM hs_hr_employee_leave_quota where employee_id = $employee_id and leave_type_id = 'LTY001' --> this lists multiple rows IF Orange has been used for more than one year.

$query="SELECT * FROM hs_hr_employee_leave_quota where employee_id = $employee_id and leave_type_id = 'LTY001' and year=2010"; (for this holiday year)
But the problem is... when the page first loads it populates a variable.. this means that if we are on the 31/12/2010 we can't book holidays for the 3/01/2011 because the year if different. Does this makes sense? That's why my approach would be to do this : the controller.

Thanks
Ricardo

PS : Waiting for everyone opinions on this

[lostsoul82]

To andremta,
Actually you just need to modify 2 files.
leaveapply.php (insert on line 131) and language_default_full.php (insert on line 1834)

Path for these 2 files:
\OrangeHRM\2.5.0.2\htdocs\orangehrm-2.5.0.2\language\default\language_default_full.php
\OrangeHRM\2.5.0.2\htdocs\orangehrm-2.5.0.2\templates\leave\leaveApply.php

I wouldn't advise you to copy and paste my code and overwrite the files in both files, so I would not provide my modified file.
As different companies have different leave policies, my annual leave is defined as LTY001,
yours might be different, it would not work.

I would advise you to modify the coding with dreamweaver etc or any kind of web programming software.
Take some time to go through my code, and I believe it would integrate with your company need.
I have took time to write out explaination for almost each code, so that others can benefit from it.


To ricardo,
I'm glad my code has helped you. If you managed to solve the problem for leave applied next year,
i would be glad if you can share with us your code, as I'm not able to test right now as another problem crops up.
I'm doing UAT testing with my colleagues and found some minor bugs with regards to approval of leave etc..

For example, once supervisor approved the leave etc, the drop down box shall be disabled, if the supervisor can keep changing
the leave status, it would be chaos. It seems like a simple thing to programmers, but since I'm not good in php or javascript, I might
need some time to figure it out.

If I managed to solve it, I will post the solution.
If anyone knows how to do it, please share. Thanks a lot.
Cheers =)

[lostsoul82]

I played around with the approval, rejected, cancelled etc and found out that it's not a bug and do not intend to change any more coding.
However, I fixed my original code with some bug fixes.

I have added Ricardo's code in but I still not able to fix the bug if you apply for next year's leave.
$query="SELECT * FROM hs_hr_employee_leave_quota where employee_id = $employee_id and leave_type_id = 'LTY001' and year = $currentyear";
this line of code will allow the database to search for employee's current leave for the current year.
Since i can't transform javascript to php coding, it would seem hard for me to solve the bug.
Which means if the user select leave from 28/12/2010 - 01/03/2011 they would minus the leave balance from the employee in the current year
and the application would work.
But if user tries to apply leave from 01/01/2011 when the current year is 2010 there would be problems.
It's not a major problem for me, I'm still able to bear with it.
Hoping to hear more opinions and solutions.
Hope that helps. =)


CODE FIXES:
- Does not allow user to select Sat, Sun or PH
- PH means that I can define any weekdays or weekends as public holiday.
Example if Labour Day falls on 1st of May and is a Saturday, I will add Monday(3rd of May) as a public holiday (it's my company policy, you can define your own)
When user tries to apply on leave on 3rd May, it will prompt user that it is a public holiday and user cannot select that.
This is to solve the bug if user select Sat or Sun when he applies for leave, the leave calculation would be inaccurate.

Just Insert this code in \OrangeHRM\2.5.0.2\htdocs\orangehrm-2.5.0.2\templates\leave\leaveApply.php line 131

CODE TO INSERT:
//Added by IT Admin Ryan : These code is to calculate the number of days applied by employee minus PH,Sat,Sun
//Add these codes in \OrangeHRM\2.5.0.2\htdocs\orangehrm-2.5.0.2\templates\leave\leaveApply.php line 131
//with the number of leave that he is currently having, if lesser will prompt error message.
//variables to store PH, no_of_days_alloted and leave_taken
var holidays = new Array();
var no_of_days_allotted;
var leave_taken;

<?php
//get employee_id, mutltiply by 1 to remove leading zeros
$employee_id = $_SESSION['empID'] * 1;

if ($employee_id != NULL) { //Added by me cos if i didn't add this when admin try assign leave there would be errors
$username="root"; //username root
$password= NULL; //using default password of database which is NULL
$database="hr_mysql";

mysql_connect("127.0.0.1",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM hs_hr_holidays";
mysql_query($query); //Extract SQL query from database
$result=mysql_query($query); //put contents of query into an array $result
$num=mysql_num_rows($result); //$num counts the number of rows in the query

// for loop to extract PH date to array $holidays[] and copy variable from PHP to javascript
$i=0;

while ($i < $num) {
$holidays[]=mysql_result($result,$i,"date"); ?>
var j = <?= $i ?>;
holidays[j] = "<?= $holidays[$i] ?>";
<?php $i++;
}
?>

//workdays is number of working days after minus off sat/sun and PH
var workdays = calcBusinessDays(date1,date2,holidays);

//Show error message if user selected Sat / Sun / PH
if (workdays == -2)
{
err = true;
msg += " - <?php echo $lang_Leave_Error_RestDay; ?>\n" //Define our own error message
}

//Check this condition only when Annual Leave is selected
if (document.frmLeaveApp.sltLeaveType.value == "LTY001") {

<?php
//calculates the current year
$currentyear = date('Y');
//LTY001 is my annual leave
$query="SELECT * FROM hs_hr_employee_leave_quota where employee_id = $employee_id and leave_type_id = 'LTY001' and year = $currentyear";
mysql_query($query);
$result=mysql_query($query);
mysql_close();

//We do not have to use the loop while $i < $num as it contains only one row
//this 2 variables is used to calculate remaining leave balance of employee (no_of_days_alloted - leave_taken)
$no_of_days_allotted=mysql_result($result,0,"no_of_days_allotted");
$leave_taken=mysql_result($result,0,"leave_taken");
?>

//convert php coding to javascript
no_of_days_allotted = "<?= $no_of_days_allotted ?>";
leave_taken = "<?= $leave_taken ?>";

//reject the leave application and pop up error message if leave balance is not enough
if ((no_of_days_allotted - leave_taken) < workdays) {
err = true;
msg += " - <?php echo $lang_Leave_Error_LeaveExceeded; ?>\n" //Define our own error message
}
} //close Annual Leave Condition Loop

<?php } ?> //End If Loop for PHP coding if ($employee_id != NULL)

//This function calculates business dates by subtracting sat and sun and PH
function calcBusinessDays(dDate1, dDate2, dHolidays) { // input given as Date objects

//This formula helps to substract sat and sun with To and From Dates
var iWeeks, iDateDiff, iAdjust = 0;
var FormattedDate1 = new Date(dDate1.replace(/-/g,"/"));
var FormattedDate2 = new Date(dDate2.replace(/-/g,"/"));

if (FormattedDate2 < FormattedDate1) return -1; // error code if dates transposed

var iWeekday1 = FormattedDate1.getDay(); // day of week
var iWeekday2 = FormattedDate2.getDay();

if (iWeekday1 == 6 || iWeekday1 == 0 || iWeekday2 == 6 || iWeekday2 == 0) return -2; //if date falls on Sat or Sun return error

iWeekday1 = (iWeekday1 == 0) ? 7 : iWeekday1; // change Sunday from 0 to 7
iWeekday2 = (iWeekday2 == 0) ? 7 : iWeekday2;

if ((iWeekday1 > 5) && (iWeekday2 > 5)) iAdjust = 1; // adjustment if both days on weekend

iWeekday1 = (iWeekday1 > 5) ? 5 : iWeekday1; // only count weekdays
iWeekday2 = (iWeekday2 > 5) ? 5 : iWeekday2;

// calculate difference in weeks (1000mS * 60sec * 60min * 24hrs * 7 days = 604800000)
iWeeks = Math.floor((FormattedDate2.getTime() - FormattedDate1.getTime()) / 604800000)

if (iWeekday1 <= iWeekday2) {
iDateDiff = (iWeeks * 5) + (iWeekday2 - iWeekday1)
}

else {
iDateDiff = ((iWeeks + 1) * 5) - (iWeekday1 - iWeekday2)
}

iDateDiff -= iAdjust // take into account both days on weekend

//this formula below helps to calculate public holidays from the To and From Dates using strToDate
//If PH falls on Saturday or Sunday it is not subtracted, I assume that we add in the PH manually in the leave system as weekday
var Formatholidays;
var Dformat = YAHOO.OrangeHRM.calendar.format;
var Strholidays;
var k=0;
for (k=0; k < dHolidays.length ;k++)
{
Strholidays = strToDate(dHolidays[k], Dformat);
Formatholidays = new Date(dHolidays[k].replace(/-/g,"/"));

if (strToDate(dDate1,Dformat) == Strholidays || strToDate(dDate2,Dformat) == Strholidays) return -2;//return error if PH selected

if (strToDate(dDate1,Dformat) <= Strholidays && Strholidays <= strToDate(dDate2,Dformat)
&& Formatholidays.getDay() != 6 && Formatholidays.getDay() != 0) iDateDiff--;
}

return (iDateDiff + 1); // add 1 because dates are inclusive

}


Insert this code in \OrangeHRM\2.5.0.2\htdocs\orangehrm-2.5.0.2\language\default\language_default_full.php line 1834

CODE TO INSERT:
$lang_Leave_Error_LeaveExceeded = "You have exceeded your leave quota";
$lang_Leave_Error_RestDay = "You have selected Sat / Sun or PH ";

[ricardo]

Hi all,

This is my contribution:

if ( isset($_POST['txtLeaveFromDate']) ) {
$yy = substr($_POST['txtLeaveFromDate'],0,4);
} else {
$yy = date('Y');
}

$query="SELECT * FROM hs_hr_employee_leave_quota where employee_id = $employee_id and leave_type_id = 'LTY001' and year=$yy";

It gets the submitted date from the text box.

Hope this helps
Ric

[lostsoul82]

Hi Ricardo,
Thanks for your help. You really saved much of my time to debug this again. :0
Anyway to elaborate further after adding in Ricardo's code to replace my coding:


Just replace :
//calculates the current year
$currentyear = date('Y');
//LTY001 is my annual leave
$query="SELECT * FROM hs_hr_employee_leave_quota where employee_id = $employee_id and leave_type_id = 'LTY001' and year = $currentyear";

With Ricardo's code:
//This function retrieves the year selected from the "From Date" selected by the user
//so that we can use the year to compare the annual leave entitled to him / her in the database
//if it's the current year or the following year
if ( isset($_POST['txtLeaveFromDate']) ) {
$yy = substr($_POST['txtLeaveFromDate'],0,4);
}
else {
$yy = date('Y');
}

//LTY001 is my annual leave
$query="SELECT * FROM hs_hr_employee_leave_quota where employee_id = $employee_id and leave_type_id = 'LTY001' and year = $yy";


In order to apply leave for following year:
You can either assign leave for all or that particular user whom is applying the leave for the following year.
or else you will have an error message display something like "you have zero leave".

HOW TO ASSIGN LEAVE FOR USER FOR THE FOLLOWING YEAR:
1) login to orangeHRM as admin
Go to Leave => Leave Summary => Employee Leave Summary
Select year 2011 and click view. You shall see "Copy leave quota from last year" beside the button box reset
Note: if you have manually key in an employee's leave amount for year 2011, the word will not appear.
You need to ensure all is zero including all other leave that you have defined.
Over here you are also able to assign a particular user leave for the following year.

For me I would prefer to copy leave quota from last year around 25th + onwards of Dec then I would do manually editing from there.
This allows user to apply leave for the following year and with lowest form of discranpancies, but it's still up to personal perferences.

Hope it helps.
Cheers ^^

[tracyk859]

Thanks for the post. Hi guys, Im a newbie. Nice to join this forum.

__________________
watch movies online free

[orangelibra]

Hi folks. I installed ver 2.6 recently and need some help with the leave quota code listed here. I inserted the two codes according to the instructions and I still cannot get it to work. The first code \OrangeHRM\2.5.0.2\htdocs\orangehrm-2.5.0.2\templates\leave\leaveApply.php, I inserted after line 246 which is right after:
244 msg += " - <?php echo $lang_Leave_Common_InvalidDateRange; ?>\n"
245 }
246 I inserted it here

The instructions indicate that I need to insert it at line 131, but before line 131 I have something different
126 jQuery("#txtLeaveToDate").focus(function() {
127 var todate = trim(jQuery("#txtLeaveToDate").val());
128 if(todate != '' && todate != "YYYY-mm-DD"){
129 if(trim(jQuery("#txtLeaveToDate").val()) == trim(jQuery("#txtLeaveFromDate").val())){
130 jQuery("#trTime1").show();
131 } else {

I inserted the second code: \OrangeHRM\2.5.0.2\htdocs\orangehrm-2.5.0.2\language\default\language_default_full.php line 1834

Do I need to modify anything from the code that Ryan provided or simply copy & paste? Also I have 5 leave types (LTY001, LTY002, etc) defined. Would this code work for any # of leave types or does it need to be modified?
I appreciate all your efforts! Thanks!

[kkrishieee143]

Im using OrangeHrm 2.7. I cannot see any folder "templates" in the root folder of the orange hrm.

where is applyLeave.php file?