<?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");
include_once($path_to_root . "/payroll/includes/db/job_details_db.inc");
//----------------------------------------------------------------------------------------------------

print_epf_etf_details();

function get_employees_for_epf_etf_report($employee,$department, $to_date)
{
	 $sql = "SELECT 
                        em.*, 
                        IFNULL(dn.value_code,'No Department Entered') department_code, dn.value_name department_name 
                 FROM ".TB_PREF."employee_master em        
                    JOIN ".TB_PREF."payroll_run_details pr 
                        ON em.employee_code = pr.employee_code 
                            AND pr.component IN ('EMP_EPF8','EMP_EPF12','ETF 3%') 
                        LEFT JOIN ".TB_PREF."employee_departments ed ON 
                            em.employee_code = ed.employee_code AND 
                            ed.start_date <=".db_escape($to_date)." AND ed.end_date >=".db_escape($to_date)."   
                        LEFT JOIN ".TB_PREF."list_values dn ON
                            ed.department_id=dn.id                                                  
                 WHERE  1=1 AND em.resigned=0 ";
                
         if($employee != "")
         {
           $sql .= " AND em.employee_code = ".db_escape($employee);
         }
          if($department != "-1")
         {
           $sql .= " AND dn.id = ".db_escape($department);
         }
          
                       
       
        $sql .= " ORDER BY dn.value_code,em.employee_code ";
        
       
        
        return  $result= db_query($sql,"ETF & EPF details could not be retreived");
}



function get_employee_epf_salary_components($payroll_run_ids,$employee)
{
        $sql = "SELECT * 
                FROM ".TB_PREF."payroll_run_details 
                WHERE payroll_run_id IN(".$payroll_run_ids.") AND component IN ('SALARY_FOR_EPF','EMP_EPF8','EMP_EPF12','ETF 3%') 
                AND employee_code=".db_escape($employee);
            
        
   	$result = db_query($sql, "ETF salary components could not be retrieved");

        return $result;
}

function get_departments($department)  
 {
            $sql = "SELECT  
                ls.value_name department_name
                FROM 
                " .TB_PREF."list_values ls WHERE ls.id=".db_escape($department);
            
       return $result = db_query($sql, "Status could not be retrieved");
 }
 
function get_department_employees($date,$employee)
{
	 $sql = "SELECT  ed.*, 
                    IFNULL(d.value_code,'No Department Entered') department_code),
                    d.value_name department_name,
                    em.initials,
                    em.last_name,
                    em.first_name,
                    edl.designation
            FROM ".TB_PREF."employee_departments ed 
                    INNER JOIN ".TB_PREF."employee_master em ON 
                            ed.employee_code = em.employee_code 
                    INNER JOIN ".TB_PREF."list_values d ON 
                            ed.department_id = d.id 
                    LEFT JOIN ".TB_PREF."employment_details edl ON 
                            ed.employee_code = edl.employee_code AND 
                            edl.contract_start_date <= ".db_escape($date)." AND 
                            edl.contract_end_date >= ".db_escape($date)." 
                    WHERE ed.start_date <= ".db_escape($date)." AND 
                    ed.end_date >= ".db_escape($date);
         
         if($employee != "")
         {
             $sql .= " AND ed.employee_code =". db_escape($employee);
             
         }
             $sql .= " ORDER BY d.value_name, ed.employee_code";
                
        
        return  db_query($sql,"an employee 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 subtotal($rep,$salary_advance_sub_total)
{
        $rep->NewLine(); 
        $rep->Font('bold');
        $rep->TextCol(4,5, 'Sub Total');
        $rep->TextCol(5,6, number_format("$salary_advance_sub_total",2,".",","));
        $rep->Font(''); 
        $rep->NewLine();

        
    
}

function display_department_sub_heading($rep, $epf_etf)
{
    $rep->NewLine(); 
    $rep->Font('bold'); 
    $rep->TextCol(0, 1, $epf_etf['department_code']." - ".$epf_etf['department_name'] );
    $rep->Font(''); 
    $rep->NewLine(); 
}
function get_payroll_run_ids($from_date,$to_date)
{
    $sql ="SELECT id
           FROM ".TB_PREF."payroll_runs
           WHERE period_start_date >=". db_escape($from_date)." AND period_end_date <=". db_escape($to_date);
    
    $result = db_query($sql, "Payroll Run Id could not be retrieved");
    
    $ids = '';
    while($row = db_fetch($result)){
        $ids .= $row['id'].",";
    }
    
    $ids = substr($ids, 0, -1);
    
    return $ids;
}

function print_epf_etf_details()
{
    global $path_to_root, $pic_height;
        
        $employee = $_POST['PARAM_0'];
        $department = $_POST['PARAM_1'];
        $from_date = $_POST['PARAM_2'];
        $to_date = $_POST['PARAM_3'];
        $destination = $_POST['PARAM_4'];
        
	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, 245,400,500,600,690,770);
        $headers = array('Emp Code - Employee','Designation','8% EPF','12% EPF','EPF Total','3% ETF');
        $aligns = array('left',	'left','right','right','right','right');
        $rep = new FrontReport(_('ETF & EPF Details'), "ETFEPFDetails", '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"].") ";
             }
                $employee_name = $name;
            
        }
        else
        {
            $employee_name = "All";
            
        }
        
        if($department != "-1")
        {
                  
        $result= get_departments($department);
        $dep=db_fetch($result);
        $department_name=$dep['department_name'];
        
        }
        else
        {
           $department_name ="All";
        } 
        
        
                
        $params =   array( 	0 => $employee_name,
                                1 => $department_name,
                                2 => $from_date,
                                3 => $to_date  
                    );
        
        $rep->SetHeaderType('form_969');
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
        $rep->NewPage();
 
             $current_department = "";

             $from_date=date2sql($from_date);
             $to_date=date2sql($to_date);
             
             $payroll_run_ids = get_payroll_run_ids($from_date,$to_date);

            $empl_epf_8_total=0;
            $empl_epf12_total=0;
            $empl_etf3_total=0;
                        
            $result_epf_etf = get_employees_for_epf_etf_report($employee,$department,$to_date);
         
            while ($epf_etf = db_fetch($result_epf_etf))
            {
                    
                   
                if($current_department != $epf_etf['department_code'])
                {    
                    display_department_sub_heading($rep, $epf_etf);
                    $current_department = $epf_etf['department_code'];
                }


                $name = "";
                $name = "[".$epf_etf['employee_code']."] - ".$epf_etf['initials'].
                        " ".$epf_etf['last_name']." (".$epf_etf["first_name"].") ";
                                   
                $current_employee = $name;
                        

                $empl_epf_8=0;
                $empl_epf12=0;
                $empl_etf3=0;
                
                $emp_designation='';
                $epf_etf_values=get_employee_epf_salary_components($payroll_run_ids,$epf_etf['employee_code']);
                $emp_designation= get_employee_current_designation($epf_etf['employee_code'], $to_date);
                while($employee_salary = db_fetch($epf_etf_values))
                {
                    if($employee_salary['component']=='EMP_EPF8')
                    {
                        $empl_epf_8 +=doubleval($employee_salary['value']);

                    }
                    if($employee_salary['component']=='EMP_EPF12')
                    {
                        $empl_epf12 +=doubleval($employee_salary['value']);

                    }
                    if($employee_salary['component']=='ETF 3%')
                    {
                        $empl_etf3 +=doubleval($employee_salary['value']);

                    }
                } 


                $empl_epf_8_total +=$empl_epf_8;
                $empl_epf12_total +=$empl_epf12;
                $empl_etf3_total +=$empl_etf3; 

                $epf_total=$empl_epf_8_total + $empl_epf12_total;

                $rep->TextCol(0, 1, $current_employee );
                
                $emp_design='';
                if (isset($emp_designation['designation'])) {
                    if($emp_designation['designation'] == '')
                    {
                        $emp_design='No Designation Entered';
                    }
                    else
                    {
                        $emp_design=$emp_designation['designation'];
                    }
                }
                
                $rep->TextCol(1, 2, $emp_design);                                
                $rep->TextCol(2, 3, number_format(abs($empl_epf_8),2));    
                $rep->TextCol(3, 4, number_format($empl_epf12,2));
                $rep->TextCol(4, 5, number_format(abs($empl_epf_8) + $empl_epf12,2));
                $rep->TextCol(5, 6, number_format($empl_etf3,2));                 
                $rep->NewLine();  
                        
            }
            $rep->NewLine(); 
            $rep->fontSize += 1;
            $rep->Font('bold'); 
            $rep->TextCol(0, 1, "Total" );                               
            $rep->TextCol(2, 3, number_format(abs($empl_epf_8_total),2));    
            $rep->TextCol(3, 4, number_format($empl_epf12_total,2));
            $rep->TextCol(4, 5, number_format(abs($empl_epf_8_total)+ $empl_epf12_total,2));
            $rep->TextCol(5, 6, number_format($empl_etf3_total,2));       
            $rep->Font('');
            $rep->fontSize -= 1;
            $rep->NewLine();  
$rep->End();  
}




