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

function get_special_allowance_for_report($employee,$payroll_run,$salary_componant)
{
	 $sql = "SELECT 
                        prd.employee_code,
                        prd.component,
                        co.value_name,
                        prd.value,
                        em.initials initials,
                        em.last_name last_name,
                        em.first_name first_name,
                        IFNULL(dp.value_code,'No Department Entered') department_code,
                        dn.value_name department,
                        edl.designation 
                  FROM ".TB_PREF."payroll_run_details prd
                        INNER JOIN ".TB_PREF."employee_master em ON 
                             prd.employee_code=em.employee_code 
                        INNER JOIN ".TB_PREF."payroll_runs pr ON
                                pr.id=prd.payroll_run_id 
                        INNER JOIN ".TB_PREF."list_values co ON 
                                prd.component= co.value_code AND co.list_code IN(".PAYROLL_LISTS.") 
                        LEFT JOIN ".TB_PREF."employee_departments ed ON 
                            prd.employee_code = ed.employee_code  AND 
                            ed.start_date <= pr.period_start_date AND 
                            ed.end_date >= pr.period_end_date 
                        LEFT JOIN ".TB_PREF."list_values dn ON
                            ed.department_id=dn.id  
                        LEFT JOIN ".TB_PREF."list_values dp ON
                            ed.department_id=dp.id   
                        LEFT JOIN ".TB_PREF."list_values ls ON
                            prd.component=ls.value_code
                        LEFT JOIN ".TB_PREF."employment_details edl ON 
                            prd.employee_code = edl.employee_code  AND 
                            edl.contract_start_date <= pr.period_start_date AND 
                            edl.contract_end_date >= pr.period_end_date 
                         WHERE  1=1 ";
                
         if($employee != "")
         {
            $sql .= " AND prd.employee_code = ".db_escape($employee);
         }
        if($payroll_run)
         {
            $sql .= " AND prd.payroll_run_id = ".db_escape($payroll_run);            
         } 
         if($salary_componant !="")
         {
             
            $sql .= " AND ls.id= ".db_escape($salary_componant);
   
         }
            $sql .= " ORDER BY department,prd.employee_code ";
         
         
        return  $result= db_query($sql,"Salary advance information could not be retreived");
}

 


function get_employeee_details($employee)
{
        $sql = "SELECT em.* 
                FROM ".TB_PREF."employee_master em ";
          if($employee != "")
          {
               $sql .= " WHERE em.employee_code = ".db_escape($employee);
          }
   	return $result = db_query($sql, "Employee details could not be retrieved");

       // return db_fetch($result);
}

function get_payroll_run_information($payroll_run){
    
    	$sql = "SELECT * 
                FROM ".TB_PREF."payroll_runs  
                WHERE id = ". db_escape($payroll_run);
        
   	return $result = db_query($sql, "Payroll run details could not be retrieved");
    
    
}
function get_salary_component_information($salary_componant){
    
    	$sql = "SELECT * 
                FROM ".TB_PREF."list_values ls 
                WHERE ls.id = ". db_escape($salary_componant);
        
   	return $result = db_query($sql, "Salary components could not be retrieved");
    
    
}


function subtotal($rep,$special_allowance_sub_total)
{
        $rep->NewLine(); 
        $rep->Font('bold');
        $rep->TextCol(2,3, 'Sub Total');
        $rep->TextCol(3,4, number_format("$special_allowance_sub_total",2,".",","));
        $rep->Font(''); 
        $rep->NewLine();  
}

function display_department_sub_heading($rep, $salary_advance)
{
    $rep->NewLine(); 
    $rep->Font('bold'); 
    $rep->TextCol(0, 1, $salary_advance['department_code']." - ".$salary_advance['department'] );
    $rep->Font(''); 
    $rep->NewLine(); 
}

function print_special_allowance()
{
    global $path_to_root, $pic_height;
        
        $employee = $_POST['PARAM_0'];
        $payroll_run= $_POST['PARAM_1'];
        $salary_componant= $_POST['PARAM_2'];
        $destination = $_POST['PARAM_3'];
        
	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, 275, 500,600,770);
        $headers = array('Emp Code - Employee','Designation','Salary Componant','Amount');
        $aligns = array('left',	'left','left','right');
        $rep = new FrontReport(_('Special Allowance'), "SpecialAllowance", 'A4', 9, $orientation);
        
        if($employee != "")
        {
            
            $employee_detials = get_employeee_details($employee);
            $name = "";
            if($employee_data= db_fetch($employee_detials))
             {
            $name = "[".$employee_data['employee_code']."] - ".$employee_data['initials'].
                            " ".$employee_data['last_name']." (".$employee_data["first_name"].") ";
             }
             $employeeName = $name;
            
        }
        else
        {
            $employeeName = "All";
            
        }
        
        if($payroll_run != "-1")
        {
                  
            $result= get_payroll_run_information($payroll_run);
            $StC=db_fetch($result);
            $payrol_run_name=$StC['period_start_date']." - ".$StC['period_end_date']." - ".$StC['description'];
        
        }
        else
        {
           $payrol_run_name ="None";
        } 
        
        if($salary_componant != "")
        {
                  
            $result= get_salary_component_information($salary_componant);
            $StC=db_fetch($result);
            $salary_component_name=$StC['value_name'];
        
        }
        else
        {
           $salary_component_name ="All";
        }  
                              
        $params =   array( 	0 => $employeeName,
                                1 => $payrol_run_name,
                                2 => $salary_component_name
                                
                    );
        
        $rep->SetHeaderType('form_962');
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
        $rep->NewPage();
                      
        $special_allowance_sub_total = 0;
        $special_allowance_grand_total = 0;
        $current_department ="";
                
             $result_special_allowance = get_special_allowance_for_report($employee,$payroll_run,$salary_componant);
         
             while ($special_allowance = db_fetch($result_special_allowance))
            {
                    
              if($current_department != $special_allowance['department_code'])
                {
                    
                if($current_department != '')  
                {
                        
                subtotal($rep,$special_allowance_sub_total);
                $special_allowance_grand_total += $special_allowance_sub_total;
                $special_allowance_sub_total = 0 ;
                
                }
                display_department_sub_heading($rep, $special_allowance);
                $current_department = $special_allowance['department_code'];
                }
                
                $special_allowance_sub_total += $special_allowance['value'];   
                $name = "";
                    $name = "[".$special_allowance['employee_code']."] - ".$special_allowance['initials'].
                            " ".$special_allowance['last_name']." (".$special_allowance["first_name"].") ";
                                   
                        $current_employee = $name;

                
                             
                        $rep->NewLine(); 
            //                        $rep->Font("italic");
                                                
                        $rep->TextCol(0, 1, $current_employee );
                        $rep->TextCol(1, 2, $special_allowance['designation'] != null ?substr($special_allowance['designation'],0,42) : '');
                        $rep->TextCol(2, 3, $special_allowance['value_name']);
                        $rep->TextCol(3, 4, number_format($special_allowance['value'],2,".",","));
                                                                 
                                       
                        $rep->NewLine();    
                      //  $special_allowance_grand_total += $special_allowance_sub_total;
            }
        $special_allowance_grand_total += $special_allowance_sub_total;   
        subtotal($rep,$special_allowance_sub_total);
        $rep->NewLine(); 
        $rep->fontSize += 1; 
        $rep->Font('bold'); 
        $rep->TextCol(2,3, 'Grand Total');
        $rep->TextCol(3,4, number_format($special_allowance_grand_total,2,".",",")); 
        $rep->fontSize += 1; 
       

$rep->End();  
}




