Solve my Leave Application Problem

[ramkumar]

Hi Guys,

i m doing leave management application...

Leave Conditions:i m split 12 months into 3/3/3/3 months i.e four quarter...

in each quarter have only 6 days....

i m having three tables 1)Employee_details
2)Leave Details
3)Status

i have a problem in admin side...

*Admin enter and he see the leave table contains employee_id,no.of.days,Reason,from_date,to_date,status...

*Admin do only two process whether he Accept or Reject the leave....

*if he reject then i change the employee status of employee to 2 and display rejected employees.

*if he accept then then find,in which quarter the employee apply leave and sanctioned....

problem:if i accept continuously for same employee then leave will sanctioned and the value in status table(6) will be reduced to zero and go beyond to minus value...?

how i check this type of conditions....?

This is my Accept.php code:[code]<?php
    
    include_once("app.ini.php");
    $smarty = getSmarty();
    $result['emp_id'] = $_REQUEST['eid'];
    $result['leave_id'] = $_REQUEST['id_l'];
    $result['start_date'] = $_REQUEST['leavefrom'];
    $today=date("Y-n-j");

    $sql_query="SELECT d_date_of_joining FROM `employee_details` WHERE id_eid = ".stripslashes($result['emp_id']);
    $res=mysql_query($sql_query);
    while($line = mysql_fetch_assoc($res)){
    
    $result['doj'] = $line['d_date_of_joining'];
    }
    function dateDiff($dformat, $endDate, $beginDate) {
        $date_parts1=explode($dformat, $beginDate);
        $date_parts2=explode($dformat, $endDate);
        $start_date=gregoriantojd($date_parts1[1], $date_parts1[2], $date_parts1[0]);
        $end_date=gregoriantojd($date_parts2[1], $date_parts2[2], $date_parts2[0]);
        return ($end_date - $start_date);
                
    }
    $different = dateDiff("-",$result['start_date'],$result['doj']);
    $result['exp'] = $different;
    $result['total_days'] = $result['exp'] % 365;
                                                                                                                
    /******************First Quarter****************************/
    
    if($result['total_days'] < 91)    {
        global $smarty;
        $smarty->assign('quarter',"First Quarter");
        $query1 = "SELECT i_sta_first_quarter from status where id_eid = ".$result['emp_id'];
        $result1 = mysql_query($query1);
        while ($line1 = mysql_fetch_assoc($result1)){
            $a1 =$line1;
        }
        $result['available_days'] = $a1['i_sta_first_quarter'];
        $sql1 = "SELECT i_leave_days FROM LeaveDetails WHERE id_leave =".$result['leave_id'];
        $result1 = mysql_query($sql1);
        while ($line1 = mysql_fetch_assoc($result1)){
             $a11 =$line1;
        }
       $result['needed'] = $a11['i_leave_days'];
       $result['remaining_days'] = $result['available_days'] - $result['needed'];
       $lde1 = "UPDATE LeaveDetails SET i_status = 1 where id_leave = ".$result['leave_id'];
       mysql_query($lde1);
       $qua1 ="UPDATE status SET i_sta_first_quarter =".$result['remaining_days']." WHERE id_eid =".$result['emp_id'];
       mysql_query($qua1);
       $first = "SELECT i_sta_first_quarter FROM status WHERE id_eid = ".$result['emp_id'];
       mysql_query($first);
       $first = "SELECT i_sta_frist_quarter FROM `status` WHERE id_eid = ".$result['emp_id'];
       mysql_query($first);
       /*if(i_sta_first_quarter == 0) {
           $smarty->assign('first','You are not allowed to take leave in First Quarter');
           $smarty->display('accept_display.tpl.html');
       }
       if($result['total_days'] > 365){
            $quarter1 = "UPDATE `status` SET i_sta_first_quarter = 6 WHERE id_eid = ".$result['emp_id'];
            mysql_query($quarter1);
       }*/
}        
//********SECOND QUARTER******************************************//


      else if($result['total_days'] > 91 && $result['total_days'] < 182){
        $smarty->assign('quarter',"Second Quarter");
        $qcheck2="SELECT i_sta_first_quarter from status where id_eid = ".$result['emp_id'];
        $qcheckresult2=mysql_query($qcheck2);
        while($qcheckresult2fetch=mysql_fetch_assoc($qcheckresult2))
        {
             $a3=$qcheckresult2fetch;
        }
         $a4 = $result['available_days'];
        if($a4 > 0)
        {
            $sql2update="UPDATE status SET i_sta_first_quarter=0,i_sta_second_quarter =i_sta_second_quarter + ".$a4." WHERE id_eid =".$result['emp_id'];
            mysql_query($sql2update);
        }
        $query2 = "SELECT i_sta_second_quarter from status where id_eid = ".$result['emp_id'];
        $result2 = mysql_query($query2);
        while ($line2 = mysql_fetch_assoc($result2)) {
                $a2 =$line2;
        }
        $result['available_days'] = $a2['i_sta_second_quarter'];
        $sql2 = "SELECT i_leave_days FROM LeaveDetails WHERE id_leave =".$result['leave_id'];
        $result2 = mysql_query($sql2);
        while ($line2 = mysql_fetch_assoc($result2))
        {
            $a12 =$line2;
        }
       $result['needed'] = $a12['i_leave_days'];
       $result['remaining_days'] = $result['available_days'] - $result['needed'];
       $lde2 = "UPDATE LeaveDetails SET i_status = 1 where id_leave = ".$result['leave_id'];
       mysql_query($lde2);
       $qua2 ="UPDATE status SET i_sta_second_quarter =".$result['remaining_days']." WHERE id_eid =".$result['emp_id'];
       mysql_query($qua2);
}

//*****************THIRD QUARTER***************************************//


       else if($result['total_days'] >= 182 && $result['total_days'] <=273)
       {

            $smarty->assign('quarter',"Third Quarter");
            $qcheck3="SELECT i_sta_first_quarter,i_sta_second_quarter from status where id_eid = ".$result['emp_id'];
            $qcheckresult3=mysql_query($qcheck3);
            while($qcheckresult3fetch=mysql_fetch_assoc($qcheckresult3))
            {
                $a3=$qcheckresult3fetch;
            }
            $a4 = $result['available_days'];
            $a5 = $result['available_days'];
            if($a4>0 || $a5>0)
            {
                echo "<br>";
                $a6 = $a4 + $a5;
            $sql3update="UPDATE status SET i_sta_first_quarter=0,i_sta_second_quarter=0,i_sta_third_quarter      =i_sta_third_quarter +".$a6." WHERE id_eid =".$result['emp_id'];
            mysql_query($sql3update);
        }
        $query3 = "SELECT i_sta_third_quarter from status where id_eid = ".$result['emp_id'];
        $result3 = mysql_query($query3);
        while ($line3 = mysql_fetch_assoc($result3))
        {
        
            $a3 =$line3;
        }
        $result['available_days'] = $a3['i_sta_third_quarter'];
        $sql3 = "SELECT i_leave_days FROM LeaveDetails WHERE id_leave =".$result['leave_id'];
        $result3 = mysql_query($sql3);
        while ($line3 = mysql_fetch_assoc($result3))
        {
        
            $a13 =$line3;
        }
        $result['needed'] = $a13['i_leave_days'];
        $result['remaining_days'] = $result['available_days'] - $result['needed'];
        $lde3 = "UPDATE LeaveDetails SET i_status = 1 where id_leave = ".$result['leave_id'];
        mysql_query($lde3);
        $qua3 ="UPDATE status SET i_sta_third_quarter =".$result['remaining_days']." WHERE id_eid =".$eid;
        mysql_query($qua3);
    }


  //****************Fourth Quarter******************************************//
        else
        {

            $smarty->assign('quarter',"Fourth Quarter");
            $qcheck4="SELECT i_sta_first_quarter,i_sta_second_quarter,i_sta_third_quarter from status where id_eid = ".$result['emp_id'];
            $qcheckresult4=mysql_query($qcheck4);
            while($qcheckresult4fetch=mysql_fetch_assoc($qcheckresult4))
            {
                $a3=$qcheckresult4fetch;
            }
            $a4 = $result['available_days'];
            $a5 = $result['available_days'];
            $a6 = $result['available_days'];
            if($a4 > 0 || $a5 > 0)
            {
    
                $a7 = $a4+$a5+$a6;
                $sql3update="UPDATE status SET i_sta_first_quarter=0,i_sta_second_quarter=0,i_sta_third_quarter=0, i_sta_fourth_quarter = i_sta_fourth_quarter +".$a7." WHERE id_eid =".$result['emp_id'];
                mysql_query($sql3update);
            }
            $query4 = "SELECT i_sta_fourth_quarter from status where id_eid = ".$result['emp_id'];
            $result4 = mysql_query($query4);
            while ($line4 = mysql_fetch_assoc($result4)) {
            $a4 =$line4;
            }
            $result['available_days'] = $a4['i_sta_fourth_quarter'];
            $sql4 = "SELECT i_leave_days FROM LeaveDetails WHERE id_leave =".$result['leave_id'];
            $result4 = mysql_query($sql4);
            while ($line4 = mysql_fetch_assoc($result4)) {
                $a14 =$line4;
            }
            $result['needed'] = $a14['i_leave_days'];
            $result['remaining_days'] = $result['available_days'] - $result['needed'];
            $lde4 = "UPDATE LeaveDetails SET i_status = 1 where id_leave = ".$result['leave_id'];
            mysql_query($lde4);
            $qua4 ="UPDATE status SET i_sta_fourth_quarter =".$result['remaining_days']." WHERE id_eid =".$result['emp_id'];
            
            mysql_query($qua4);
        }
/************************************List Leave Details*************************/    
            $sql = "SELECT * FROM `LeaveDetails`";
            $result['employee_list'] = getrows($sql,$err);
            $smarty->assign('sm',$result);
            $smarty->display("accept_display.tpl.html");

?>
[/code]


by
Raokyram