<?php
/**********************************************************************

	Released under the terms of the GNU General Public License, GPL,
***********************************************************************/
$page_security = 'SA_ITEMSVALREP';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	Joe Hunt
// date_:	2005-05-19
// Title:	Stock Check Sheet
// ----------------------------------------------------------------
$path_to_root="..";

include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/payroll/includes/db/employees_db.inc");

//----------------------------------------------------------------------------------------------------

print_OT();

//function get_ot_employees($payroll_run_id)
//{
//    $sql = "SELECT DISTINCT a.employee_code,em.initials, em.last_name, em.first_name "
//            . "FROM "
//            .TB_PREF."attendance a, "
//            .TB_PREF."employee_master em ,"
//             .TB_PREF."payroll_runs p "
//            . "WHERE a.employee_code = em.employee_code ";
//      if($payroll_run_id != "-1")
//         {
//          $sql .= " AND a.calculated_payroll_run_id= ".db_escape($payroll_run_id);            
//         }      
//          $sql .= " AND a.calculated_payroll_run_id =p.id";
//    
//    $result = db_query($sql, "Employee details could not be retrieved");
//
//        return $result;
//    
//}

function get_ot_for_report($Employee,$Shifts,$Value_table,$StatusCode,$payroll_run_id)
{
	 $sql = "SELECT 
                a.*,
                
                s.shift_name,
                li.value_name status_code_name,
                ls.value_name ot_table_name
                From ".TB_PREF."attendance a ,"
                 .TB_PREF."shifts s ,"
                 .TB_PREF."list_values li ,"
                 .TB_PREF."list_values ls ,"
                 .TB_PREF."payroll_runs p "
                  ."where 1=1 ";

           $sql .= " AND a.employee_code = ".db_escape($Employee);
         if($payroll_run_id)
         {
          $sql .= " AND a.calculated_payroll_run_id= ".db_escape($payroll_run_id);            
         } 
        if($Shifts > 0)
         {
          $sql .= " AND a.shift_id= ".db_escape($Shifts);            
         }  
         if($Value_table > 0)
         {
          $sql .= " AND a.rate_table_id= ".db_escape($Value_table);            
         }  
        if($StatusCode != "-1")
         {
          $sql .= " AND li.id= ".db_escape($StatusCode);            
         }  
        
        $sql .= " AND  a.shift_id=s.id AND "
                . " a.ot_status_code =li.value_code AND "
                . " li.list_code = 'ATTENDANCE_STATUS' AND a.rate_table_id=ls.id AND "
                . " ls.list_code = 'VALUE_TABLES' AND a.calculated_payroll_run_id=p.id AND "
                . " NOT (a.calculated_ot_time = '00:00:00' AND a.granted_ot_time = '00:00:00' AND a.payroll_ot_time = '00:00:00') ";
        
        return  db_query($sql,"an employee could not be retreived");
}



function get_employeee_ot_details($Employee, $Shifts,$Value_table,$StatusCode,$payroll_run_id)
{
       $sql = "SELECT DISTINCT a.employee_code,em.initials, em.last_name, em.first_name "
            . "FROM "
            .TB_PREF."attendance a, "
            .TB_PREF."employee_master em,"
              .TB_PREF."shifts s ,"
                 .TB_PREF."list_values li ,"
                 .TB_PREF."list_values ls ,"
                 .TB_PREF."payroll_runs p "
            . "WHERE a.employee_code = em.employee_code";
          if($payroll_run_id)
         {
          $sql .= " AND a.calculated_payroll_run_id= ".db_escape($payroll_run_id);            
         }    
         if($Employee != "")
         {
           $sql .= " AND a.employee_code = ".db_escape($Employee);
         }
          if($Shifts > 0)
         {
          $sql .= " AND a.shift_id= ".db_escape($Shifts);            
         }  
         if($Value_table > 0)
         {
          $sql .= " AND a.rate_table_id= ".db_escape($Value_table);            
         }  
        if($StatusCode != "-1")
         {
          $sql .= " AND li.id= ".db_escape($StatusCode);            
         }  
        
        $sql .= " AND  a.shift_id=s.id AND "
                . " a.ot_status_code =li.value_code AND "
                . " li.list_code = 'ATTENDANCE_STATUS' AND a.rate_table_id=ls.id AND "
                . " ls.list_code = 'VALUE_TABLES' AND a.calculated_payroll_run_id=p.id AND "
                . " NOT (a.calculated_ot_time = '00:00:00' AND a.granted_ot_time = '00:00:00' AND a.payroll_ot_time = '00:00:00') ";
          
                       
   	return $result = db_query($sql, "Employee details could not be retrieved");

        //return db_fetch($result);
}

function get_payroll_run_information($selected_id){
    	$sql = "SELECT pr.*, lv.value_name payroll_name     
                FROM ".TB_PREF."payroll_runs pr,
                        ".TB_PREF."list_values lv  
                WHERE pr.payroll_id = lv.id AND 
                        pr.id = ".db_escape($selected_id);
	
	return $result = db_query($sql,"Could not retrieve payroll run details");	
        
       
    
}
  function calculatetime($time)
        {
            $h = floor($time /3600);
            $m = floor(($time - $h *3600) / 60);
            
            if(strlen($h) == 1){
                $h = '0'.$h;
            }
            if(strlen($m) == 1){
                $m = '0'.$m;
            }
            
            return $h.":".$m;        
            
        }
        
function get_employeee_details($employee)
{
        $sql = "SELECT em.* 
                FROM ".TB_PREF."employee_master em 
                WHERE em.employee_code = ".  db_escape($employee);
        
   	return $result = db_query($sql, "Employee details could not be retrieved");

         
}
        
function get_shift_data($shift)
{
     $sql = "SELECT  
                s.shift_name
                FROM 
                ".TB_PREF."attendance a ,"
                 .TB_PREF."shifts s Where a.shift_id=s.id AND a.shift_id=".db_escape($shift);
            
       return $result = db_query($sql, "Status could not be retrieved");
    
    
}
 function get_ot_table_data($ot_table)  
 {
            $sql = "SELECT  
               ls.value_name ot_table_name
                FROM 
                ".TB_PREF."attendance a ,"
                 .TB_PREF."list_values ls Where a.rate_table_id=ls.id AND a.rate_table_id=".db_escape($ot_table);
            
        return $result = db_query($sql, "Status could not be retrieved");  
 }
 function get_status_data($status_code)  
 {
            $sql = "SELECT  
                ls.value_name StatusCodeName
                FROM 
                ".TB_PREF."list_values ls Where ls.id=".db_escape($status_code);
            
       return $result = db_query($sql, "Status could not be retrieved");
 }
 
function print_OT()
{
    global $path_to_root, $pic_height;
        
        $payroll_run_id = $_POST['PARAM_0'];
        $Employee = $_POST['PARAM_1'];
        $Shifts = $_POST['PARAM_2'];
        $Value_table = $_POST['PARAM_3'];
        $StatusCode = $_POST['PARAM_4'];
        $destination = $_POST['PARAM_5'];
        
        
        
	if ($destination)
		include_once($path_to_root . "/reporting/includes/excel_report.inc");
	else
		include_once($path_to_root . "/reporting/includes/pdf_report.inc");
	
	$orientation = 'L';
       

        $cols = array(0, 175, 250,375,450,525,600,710,800);
        $headers = array('Emp Code - Employee','Date','Shift','OT Table','Cal. OT Hrs.','Granted OT Hrs.','Payroll OT Hrs','Status');
        $aligns = array('left',	'left','left','left','right','right','center','left');
        $rep = new FrontReport(_('Over Time'), "OverTime", 'A4', 9, $orientation);
       
        
        
        
        if($Employee != "")
        {
            
            $emp_data = get_employeee_details($Employee);
            $name = "";
            if($empdetails= db_fetch($emp_data))
            {
            $name = "[".$empdetails['employee_code']."] - ".$empdetails['initials'].
                      " ".$empdetails['last_name']." (".$empdetails["first_name"].") ";
                     }
            $employeeName = $name;
            
        }
        else
        {
            $employeeName = "All";
            
        }
        if($Shifts != "-1")
        {
         $result= get_shift_data($Shifts); 
         $StC=db_fetch($result);
         $shift_name=$StC['shift_name'];
        
        }
        else
        {
           $shift_name ="All";
        } 
        if($Value_table != "-1")
        {
            
        $result= get_ot_table_data($Value_table);
        $StC=db_fetch($result);
        $ot_table_name=$StC['ot_table_name'];
        
        }
        else
        {
           $ot_table_name ="All";
        }       
      
        if($StatusCode != "-1")
        {
            
        $result= get_status_data($StatusCode);
        $StC=db_fetch($result);
        $StCode=$StC['StatusCodeName'];
        
        }
        else
        {
           $StCode ="All";
        } 
        if($payroll_run_id != "-1")
        {
                  
        $result= get_payroll_run_information($payroll_run_id);
        $StC=db_fetch($result);
        $payrol_run_name=$StC['period_start_date']." - ".$StC['period_end_date']." - ".$StC['payroll_name'];
        
        }
        else
        {
           $payrol_run_name ="None";
        } 
                
        $params =   array( 	
                                0 => $payrol_run_name,
                                1 => $employeeName,
                                2 => $shift_name,
                                3 => $ot_table_name,
                                4 => $StCode
                    );
        $rep->SetHeaderType('form_959');
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
        $rep->NewPage();
        
        
        
        
        $secondsCalOtTotal =0;
        $secondsGranOtTotal =0;
        $secondspayOtTotal =0;
        
        $employeee_details = get_employeee_ot_details($Employee, $Shifts,$Value_table,$StatusCode,$payroll_run_id);
       
       while($ot_employee = db_fetch($employeee_details))
       {
           
            $name = "[".$ot_employee['employee_code']."] - ".$ot_employee['initials'].
                            " ".$ot_employee['last_name']." (".$ot_employee["first_name"].") ";
            
            $rep->font('bold');
            $rep->TextCol(0, 1, $name );
       
            $rep->font('');
            $rep->NewLine();
       
           
            $sum_cal_Ot =0;
            $sum_cal_Gran =0;
            $sum_cal_payot =0;
            $secondsCalOt=0;
            $secondsGranOt=0;
            $secondspayOt=0;
            
            
            
             
            $result_ot = get_ot_for_report($ot_employee['employee_code'],$Shifts,$Value_table,$StatusCode,$payroll_run_id);
            while ($employee_ot = db_fetch($result_ot))
            {            
                       $name = $ot_employee['initials']." ".$ot_employee['last_name'];      
                
                       $rep->TextCol(0, 1, $name );    
                      //  $rep->NewLine(); 
            //                        $rep->Font("italic");
                                                
                      //  $rep->TextCol(0, 1, $name );
                        $rep->TextCol(1, 2, sql2date($employee_ot['entry_date']));
                        $rep->TextCol(2, 3, $employee_ot['shift_name']);
                        $rep->TextCol(3, 4, $employee_ot['ot_table_name']);
                        $rep->TextCol(4, 5, substr($employee_ot['calculated_ot_time'],0,5));
                       // $secondsCalOt +=strtotime($employee_ot['calculated_ot_time']);
                      
                        $secondsCalOt = time_diff_seconds(sql2date( Today()), $employee_ot['calculated_ot_time'], sql2date(Today()), "00:00:00") ;
                        $secondsCalOt=abs($secondsCalOt);
                        $sum_cal_Ot += $secondsCalOt;
                        
                         $rep->TextCol(5, 6, substr($employee_ot['granted_ot_time'],0,5));
                         //$secondsGranOt +=strtotime($employee_ot['granted_ot_time']);
                         $secondsGranOt = time_diff_seconds(sql2date(Today()), $employee_ot['granted_ot_time'], sql2date(Today()), "00:00:00") ;
                         $secondsGranOt=abs($secondsGranOt); 
                         $sum_cal_Gran += $secondsGranOt;
                         
                        $rep->TextCol(6, 7, substr($employee_ot['payroll_ot_time'],0,5));
                       // $secondspayOt +=strtotime($employee_ot['payroll_ot_time']);
                        $secondspayOt = time_diff_seconds(sql2date(Today()), $employee_ot['payroll_ot_time'], sql2date(Today()), "00:00:00") ;
                        $secondspayOt =abs($secondspayOt);
                        $sum_cal_payot += $secondspayOt;
                        $rep->TextCol(7, 8, $employee_ot['status_code_name']);
                                                
                        $rep->NewLine();
                       
                         
                }
      
                $secondsCalOtTotal += $sum_cal_Ot;
                $secondsGranOtTotal += $sum_cal_Gran;
                $secondspayOtTotal += $sum_cal_payot;
                
                $sum_cal_Ot = calculatetime($sum_cal_Ot);
                $sum_cal_Gran = calculatetime($sum_cal_Gran);
                $sum_cal_payot = calculatetime($sum_cal_payot);
                
                
                      
                
                $rep->NewLine();
                       
                $rep->font('bold');
                $rep->TextCol(2, 4,_('Total'));
                $rep->TextCol(4, 5,$sum_cal_Ot);
                $rep->TextCol(5, 6,$sum_cal_Gran);
                $rep->TextCol(6, 7,$sum_cal_payot);
                $rep->font('');
               
                $rep->NewLine(); 
                 
      //  }
        
                
                
                $rep->NewLine();
               
                 
	}
        
                $secondsCalOtTotal = calculatetime($secondsCalOtTotal);
                $secondsGranOtTotal = calculatetime($secondsGranOtTotal);
                $secondspayOtTotal = calculatetime($secondspayOtTotal);
                
                $rep->fontSize += 1;       
                $rep->font('bold');
                $rep->TextCol(2, 4,_('Grand Total'));
                $rep->TextCol(4, 5,$secondsCalOtTotal);
                $rep->TextCol(5, 6,$secondsGranOtTotal);
                $rep->TextCol(6, 7,$secondspayOtTotal);
                $rep->font('');
                $rep->fontSize -= 1;
$rep->End();  


}