e-leave management system
Posted: Mon Mar 22, 2010 10:29 pm
i create a system. the system is about e-leave. E-Leave Management System. so, every staff has their own profile. so, whenever a staff enter the company, there is the commencement date -� means the first date their enter the company. so, the problem is in service length -� how many days, month and year that they have been work here. it is not same as their needed. some got extra days, about, one, two or three days. so, can you look for the coding an tell me what is wrong?
//LEAVE SUMMARY**************************************************************************************************************************************************************
function dateDiff($dformat, $endDate, $beginDate)
{
$date_parts1=explode($dformat, $beginDate);
$date_parts2=explode($dformat, $endDate);
$start_date=gregoriantojd($date_parts1[1], $date_parts1[0], $date_parts1[2]);
$end_date=gregoriantojd($date_parts2[1], $date_parts2[0], $date_parts2[2]);
return $end_date - $start_date + 1;
}
$query200 = "SELECT * FROM leave_selection WHERE '$apply_year-01-01' BETWEEN dari AND hingga";
$result200 = mysql_query($query200) or die("couldn't execute query");
$row200 = mysql_fetch_array($result200);
$c_date = date('d-m-Y',strtotime($row1['commencement_date']));
$service_year = round(dateDiff("-", date("31-12-Y",time()), $c_date)/365,2);
$zarbi=explode('-',$c_date);
$Day = $zarbi[0];
$Month = $zarbi[1];
$Year = $zarbi[2];
$query201= "SELECT * FROM annual_leave WHERE ('$service_year' BETWEEN service_length AND service_length1) && ('$Day' BETWEEN joining_date AND joining_date1) ";
$result201 = mysql_query($query201) or die("2couldn't execute query");
$row201 = mysql_fetch_array($result201);
//to get leave entitlement(days) based on service period && day and month of commencement date
if ($Month == '01'){$annual_ed = $row201['january'];}
elseif ($Month == '02'){$annual_ed = $row201['february'];}
elseif ($Month == '03'){$annual_ed = $row201['march'];}
elseif ($Month == '04'){$annual_ed = $row201['april'];}
elseif ($Month == '05'){$annual_ed = $row201['may'];}
elseif ($Month == '06'){$annual_ed = $row201['jun'];}
elseif ($Month == '07'){$annual_ed = $row201['july'];}
elseif ($Month == '08'){$annual_ed = $row201['august'];}
elseif ($Month == '09'){$annual_ed = $row201['september'];}
elseif ($Month == '10'){$annual_ed = $row201['october'];}
elseif ($Month == '11'){$annual_ed = $row201['november'];}
elseif ($Month == '12'){$annual_ed = $row201['december'];}
$query202= "SELECT SUM(carryforward) FROM leave_application Where ((status='6') || ( (name_tm LIKE '%') &&(status='15' || status='16' || status='14' || status='12'))) && NRIC = '".$_SESSION[NRIC]."' && NRIC1 = '".$_SESSION[NRIC1]."' && NRIC2 = '".$_SESSION[NRIC2]."' && leave_code = '".$row2['lc']."' && dari LIKE '$apply_year%' ";
$result202= mysql_query($query202) or die("as execute query");
$row202 = mysql_fetch_array($result202);
$annual_balance = $annual_ed-$row120['SUM(total)']+$row202['SUM(carryforward)'];
$query203 = "SELECT * FROM prorated WHERE leave_entitlement = '$annual_ed'";
$result203 = mysql_query($query203) or die("4couldn't execute query");
$row203 = mysql_fetch_array($result203);
$apply_month = date('m');
//for QUARTERLY, to get currently available leave entitlement(days) based on quarter apply
if ($apply_month == '01'){$quarter_curr = $row300['quarter1']; $current_quarter = "JAN , FEB , MARCH";}
elseif ($apply_month == '02'){$quarter_curr = $row203['quarter1']; $current_quarter = "JAN , FEB , MARCH";}
elseif ($apply_month == '03'){$quarter_curr = $row203['quarter1']; $current_quarter = "JAN , FEB , MARCH";}
elseif ($apply_month == '04'){$quarter_curr = $row203['quarter2']; $current_quarter = "APR , MAY , JUNE";}
elseif ($apply_month == '05'){$quarter_curr = $row203['quarter2']; $current_quarter = "APR , MAY , JUNE";}
elseif ($apply_month == '06'){$quarter_curr = $row203['quarter2']; $current_quarter = "APR , MAY , JUNE";}
elseif ($apply_month == '07'){$quarter_curr = $row203['quarter3']; $current_quarter = "JUL , AUG , SEPT";}
elseif ($apply_month == '08'){$quarter_curr = $row203['quarter3']; $current_quarter = "JUL , AUG , SEPT";}
elseif ($apply_month == '09'){$quarter_curr = $row203['quarter3']; $current_quarter = "JUL , AUG , SEPT";}
elseif ($apply_month == '10'){$quarter_curr = $row203['quarter4']; $current_quarter = "OCT , NOV , DEC";}
elseif ($apply_month == '11'){$quarter_curr = $row203['quarter4']; $current_quarter = "OCT , NOV , DEC";}
elseif ($apply_month == '12'){$quarter_curr = $row203['quarter4']; $current_quarter = "OCT , NOV , DEC";}
$previous_year = $apply_year-1;
$query204 = "SELECT * FROM leave_selection WHERE '$previous_year-01-01' BETWEEN dari AND hingga";
$result204 = mysql_query($query204) or die("couldn't execute query");
$row204 = mysql_fetch_array($result204);
$previous_service_year = round(dateDiff("-", date("31-12-$previous_year",time()), $c_date)/365,2);
$query205= "SELECT * FROM annual_leave WHERE ('$previous_service_year' BETWEEN service_length AND service_length1) && ('$Day' BETWEEN joining_date AND joining_date1) ";
$result205 = mysql_query($query205) or die("2couldn't execute query");
$row205 = mysql_fetch_array($result205);
//to get leave entitlement(days) based on service period && day and month of commencement date
if ($Month == '01'){$previous_annual_ed = $row205['january'];}
elseif ($Month == '02'){$previous_annual_ed = $row205['february'];}
elseif ($Month == '03'){$previous_annual_ed = $row205['march'];}
elseif ($Month == '04'){$previous_annual_ed = $row205['april'];}
elseif ($Month == '05'){$previous_annual_ed = $row205['may'];}
elseif ($Month == '06'){$previous_annual_ed = $row205['jun'];}
elseif ($Month == '07'){$previous_annual_ed = $row205['july'];}
elseif ($Month == '08'){$previous_annual_ed = $row205['august'];}
elseif ($Month == '09'){$previous_annual_ed = $row205['september'];}
elseif ($Month == '10'){$previous_annual_ed = $row205['october'];}
elseif ($Month == '11'){$previous_annual_ed = $row205['november'];}
elseif ($Month == '12'){$previous_annual_ed = $row205['december'];}
$query206= "SELECT SUM(total) FROM leave_application Where ((status='6') || ( (name_tm LIKE '%') &&(status='15' || status='16' || status='14' || status='12'))) && NRIC = '".$_SESSION[NRIC]."' && NRIC1 = '".$_SESSION[NRIC1]."' && NRIC2 = '".$_SESSION[NRIC2]."' && leave_code = '".$row2['lc']."' && dari LIKE '$previous_year%' ";
$result206= mysql_query($query206) or die("as execute query");
$row206 = mysql_fetch_array($result206);
$query207= "SELECT SUM(carryforward) FROM leave_application Where ((status='6') || ( (name_tm LIKE '%') &&(status='15' || status='16' || status='14' || status='12'))) && NRIC = '".$_SESSION[NRIC]."' && NRIC1 = '".$_SESSION[NRIC1]."' && NRIC2 = '".$_SESSION[NRIC2]."' && leave_code = '".$row2['lc']."' && dari LIKE '$previous_year%' ";
$result207= mysql_query($query207) or die("as execute query");
$row207 = mysql_fetch_array($result207);
$carryforward_balance = $previous_annual_ed-$row206['SUM(total)']+$row207['SUM(carryforward)'];
$query208 = "SELECT * FROM carryforward_leave";
$result208 = mysql_query($query208) or die("couldn't execute query");
$row208 = mysql_fetch_array($result208);
$net_carryforward_balance = $carryforward_balance*$row208['percentage']/100;
$query209= "SELECT SUM(carryforward) FROM leave_application Where ((status='6') || ( (name_tm LIKE '%') &&(status='15' || status='16' || status='14' || status='12'))) && NRIC = '".$_SESSION[NRIC]."' && NRIC1 = '".$_SESSION[NRIC1]."' && NRIC2 = '".$_SESSION[NRIC2]."' && leave_code = '".$row2['lc']."' && dari LIKE '$apply_year%' ";
$result209= mysql_query($query209) or die("as execute query");
$row209 = mysql_fetch_array($result209);
$net_carryforward_left = $net_carryforward_balance-$row209['SUM(carryforward)'];
$cfpl = $_GET['cfpl'];
$rl = $_GET['rl'];
//convert decimal year to year and month
function year2monthsNdays($years)
{
$array = explode(".",$years);
$year = $array[0];
$month = ($array[1]>9) ? $array[1]/100 : $array[1]/10;
if ($month) {
$days = round($month*365,2);
$daysArray = explode(".",$days);
$months = round($daysArray[0]/30,2);
$monthArray = explode(".",$months);
$monthInt = $monthArray[0];
$daysInt = round($monthArray[1]*30/100,1);
}
$a = "$year-$monthInt-$daysInt";
return $a;
}
$service_year_convert = year2monthsNdays($service_year);
$convert =explode('-',$service_year_convert);
$sl_year = $convert[0]; if($sl_year == NULL){$s_year = 0;} else{$s_year = $sl_year;}
$sl_month = $convert[1]; if($sl_month == NULL){$s_month = 0;} else{$s_month = $sl_month;}
$sl_day = round($convert[2]); if($sl_day == NULL){$s_day = 0;} else{$s_day = $sl_day;}
}
?>
//LEAVE SUMMARY**************************************************************************************************************************************************************
function dateDiff($dformat, $endDate, $beginDate)
{
$date_parts1=explode($dformat, $beginDate);
$date_parts2=explode($dformat, $endDate);
$start_date=gregoriantojd($date_parts1[1], $date_parts1[0], $date_parts1[2]);
$end_date=gregoriantojd($date_parts2[1], $date_parts2[0], $date_parts2[2]);
return $end_date - $start_date + 1;
}
$query200 = "SELECT * FROM leave_selection WHERE '$apply_year-01-01' BETWEEN dari AND hingga";
$result200 = mysql_query($query200) or die("couldn't execute query");
$row200 = mysql_fetch_array($result200);
$c_date = date('d-m-Y',strtotime($row1['commencement_date']));
$service_year = round(dateDiff("-", date("31-12-Y",time()), $c_date)/365,2);
$zarbi=explode('-',$c_date);
$Day = $zarbi[0];
$Month = $zarbi[1];
$Year = $zarbi[2];
$query201= "SELECT * FROM annual_leave WHERE ('$service_year' BETWEEN service_length AND service_length1) && ('$Day' BETWEEN joining_date AND joining_date1) ";
$result201 = mysql_query($query201) or die("2couldn't execute query");
$row201 = mysql_fetch_array($result201);
//to get leave entitlement(days) based on service period && day and month of commencement date
if ($Month == '01'){$annual_ed = $row201['january'];}
elseif ($Month == '02'){$annual_ed = $row201['february'];}
elseif ($Month == '03'){$annual_ed = $row201['march'];}
elseif ($Month == '04'){$annual_ed = $row201['april'];}
elseif ($Month == '05'){$annual_ed = $row201['may'];}
elseif ($Month == '06'){$annual_ed = $row201['jun'];}
elseif ($Month == '07'){$annual_ed = $row201['july'];}
elseif ($Month == '08'){$annual_ed = $row201['august'];}
elseif ($Month == '09'){$annual_ed = $row201['september'];}
elseif ($Month == '10'){$annual_ed = $row201['october'];}
elseif ($Month == '11'){$annual_ed = $row201['november'];}
elseif ($Month == '12'){$annual_ed = $row201['december'];}
$query202= "SELECT SUM(carryforward) FROM leave_application Where ((status='6') || ( (name_tm LIKE '%') &&(status='15' || status='16' || status='14' || status='12'))) && NRIC = '".$_SESSION[NRIC]."' && NRIC1 = '".$_SESSION[NRIC1]."' && NRIC2 = '".$_SESSION[NRIC2]."' && leave_code = '".$row2['lc']."' && dari LIKE '$apply_year%' ";
$result202= mysql_query($query202) or die("as execute query");
$row202 = mysql_fetch_array($result202);
$annual_balance = $annual_ed-$row120['SUM(total)']+$row202['SUM(carryforward)'];
$query203 = "SELECT * FROM prorated WHERE leave_entitlement = '$annual_ed'";
$result203 = mysql_query($query203) or die("4couldn't execute query");
$row203 = mysql_fetch_array($result203);
$apply_month = date('m');
//for QUARTERLY, to get currently available leave entitlement(days) based on quarter apply
if ($apply_month == '01'){$quarter_curr = $row300['quarter1']; $current_quarter = "JAN , FEB , MARCH";}
elseif ($apply_month == '02'){$quarter_curr = $row203['quarter1']; $current_quarter = "JAN , FEB , MARCH";}
elseif ($apply_month == '03'){$quarter_curr = $row203['quarter1']; $current_quarter = "JAN , FEB , MARCH";}
elseif ($apply_month == '04'){$quarter_curr = $row203['quarter2']; $current_quarter = "APR , MAY , JUNE";}
elseif ($apply_month == '05'){$quarter_curr = $row203['quarter2']; $current_quarter = "APR , MAY , JUNE";}
elseif ($apply_month == '06'){$quarter_curr = $row203['quarter2']; $current_quarter = "APR , MAY , JUNE";}
elseif ($apply_month == '07'){$quarter_curr = $row203['quarter3']; $current_quarter = "JUL , AUG , SEPT";}
elseif ($apply_month == '08'){$quarter_curr = $row203['quarter3']; $current_quarter = "JUL , AUG , SEPT";}
elseif ($apply_month == '09'){$quarter_curr = $row203['quarter3']; $current_quarter = "JUL , AUG , SEPT";}
elseif ($apply_month == '10'){$quarter_curr = $row203['quarter4']; $current_quarter = "OCT , NOV , DEC";}
elseif ($apply_month == '11'){$quarter_curr = $row203['quarter4']; $current_quarter = "OCT , NOV , DEC";}
elseif ($apply_month == '12'){$quarter_curr = $row203['quarter4']; $current_quarter = "OCT , NOV , DEC";}
$previous_year = $apply_year-1;
$query204 = "SELECT * FROM leave_selection WHERE '$previous_year-01-01' BETWEEN dari AND hingga";
$result204 = mysql_query($query204) or die("couldn't execute query");
$row204 = mysql_fetch_array($result204);
$previous_service_year = round(dateDiff("-", date("31-12-$previous_year",time()), $c_date)/365,2);
$query205= "SELECT * FROM annual_leave WHERE ('$previous_service_year' BETWEEN service_length AND service_length1) && ('$Day' BETWEEN joining_date AND joining_date1) ";
$result205 = mysql_query($query205) or die("2couldn't execute query");
$row205 = mysql_fetch_array($result205);
//to get leave entitlement(days) based on service period && day and month of commencement date
if ($Month == '01'){$previous_annual_ed = $row205['january'];}
elseif ($Month == '02'){$previous_annual_ed = $row205['february'];}
elseif ($Month == '03'){$previous_annual_ed = $row205['march'];}
elseif ($Month == '04'){$previous_annual_ed = $row205['april'];}
elseif ($Month == '05'){$previous_annual_ed = $row205['may'];}
elseif ($Month == '06'){$previous_annual_ed = $row205['jun'];}
elseif ($Month == '07'){$previous_annual_ed = $row205['july'];}
elseif ($Month == '08'){$previous_annual_ed = $row205['august'];}
elseif ($Month == '09'){$previous_annual_ed = $row205['september'];}
elseif ($Month == '10'){$previous_annual_ed = $row205['october'];}
elseif ($Month == '11'){$previous_annual_ed = $row205['november'];}
elseif ($Month == '12'){$previous_annual_ed = $row205['december'];}
$query206= "SELECT SUM(total) FROM leave_application Where ((status='6') || ( (name_tm LIKE '%') &&(status='15' || status='16' || status='14' || status='12'))) && NRIC = '".$_SESSION[NRIC]."' && NRIC1 = '".$_SESSION[NRIC1]."' && NRIC2 = '".$_SESSION[NRIC2]."' && leave_code = '".$row2['lc']."' && dari LIKE '$previous_year%' ";
$result206= mysql_query($query206) or die("as execute query");
$row206 = mysql_fetch_array($result206);
$query207= "SELECT SUM(carryforward) FROM leave_application Where ((status='6') || ( (name_tm LIKE '%') &&(status='15' || status='16' || status='14' || status='12'))) && NRIC = '".$_SESSION[NRIC]."' && NRIC1 = '".$_SESSION[NRIC1]."' && NRIC2 = '".$_SESSION[NRIC2]."' && leave_code = '".$row2['lc']."' && dari LIKE '$previous_year%' ";
$result207= mysql_query($query207) or die("as execute query");
$row207 = mysql_fetch_array($result207);
$carryforward_balance = $previous_annual_ed-$row206['SUM(total)']+$row207['SUM(carryforward)'];
$query208 = "SELECT * FROM carryforward_leave";
$result208 = mysql_query($query208) or die("couldn't execute query");
$row208 = mysql_fetch_array($result208);
$net_carryforward_balance = $carryforward_balance*$row208['percentage']/100;
$query209= "SELECT SUM(carryforward) FROM leave_application Where ((status='6') || ( (name_tm LIKE '%') &&(status='15' || status='16' || status='14' || status='12'))) && NRIC = '".$_SESSION[NRIC]."' && NRIC1 = '".$_SESSION[NRIC1]."' && NRIC2 = '".$_SESSION[NRIC2]."' && leave_code = '".$row2['lc']."' && dari LIKE '$apply_year%' ";
$result209= mysql_query($query209) or die("as execute query");
$row209 = mysql_fetch_array($result209);
$net_carryforward_left = $net_carryforward_balance-$row209['SUM(carryforward)'];
$cfpl = $_GET['cfpl'];
$rl = $_GET['rl'];
//convert decimal year to year and month
function year2monthsNdays($years)
{
$array = explode(".",$years);
$year = $array[0];
$month = ($array[1]>9) ? $array[1]/100 : $array[1]/10;
if ($month) {
$days = round($month*365,2);
$daysArray = explode(".",$days);
$months = round($daysArray[0]/30,2);
$monthArray = explode(".",$months);
$monthInt = $monthArray[0];
$daysInt = round($monthArray[1]*30/100,1);
}
$a = "$year-$monthInt-$daysInt";
return $a;
}
$service_year_convert = year2monthsNdays($service_year);
$convert =explode('-',$service_year_convert);
$sl_year = $convert[0]; if($sl_year == NULL){$s_year = 0;} else{$s_year = $sl_year;}
$sl_month = $convert[1]; if($sl_month == NULL){$s_month = 0;} else{$s_month = $sl_month;}
$sl_day = round($convert[2]); if($sl_day == NULL){$s_day = 0;} else{$s_day = $sl_day;}
}
?>