<?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/payroll_run_db.inc");

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

print_OT();

function get_department_employees($departments, $date)
{
	 $sql = "SELECT  ed.*, 
                    d.value_code department_code,
                    d.value_name department_name,
                    em.initials,
                    em.last_name,
                    em.first_name,
                    edl.designation,
                    jc.value_name job_category_name 
            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)." 
                    LEFT JOIN ".TB_PREF."list_values jc ON 
                            edl.job_category_id = jc.id 
            WHERE ed.start_date <= ".db_escape($date)." AND 
                    ed.end_date >= ".db_escape($date);
     
    if($departments > 0){
            $sql .= " AND ed.department_id = ".$departments ;
    }
    
    $sql .= " AND em.date_of_resignation >=".db_escape($date)." ORDER BY d.value_name, jc.value_name, ed.employee_code";
                
        
        return  db_query($sql,"an employee could not be retreived");
}



function get_employeee_salary_details($payroll_run_id, $employee_code)
{
        $sql = "SELECT component, value   
                FROM ".TB_PREF."payroll_run_details  
                WHERE payroll_run_id = ".$payroll_run_id." AND 
                        employee_code = ".db_escape($employee_code)." AND 
                        component IN ('SALARY_FOR_EPF','ADDONS_TO_EPF_SALARY','SALARY_TOTAL_2',
                                        'ADDONS_TO_INCENTIVE', 'TAKEHOME_BASIC','TAKEHOME_INCENTIVE',
                                        'ETF 3%','EPF 12%')";
        
   	return db_query($sql, "Employee details could not be retrieved");

}

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

function display_department_sub_totals($rep,$dep_head_count,$dep_SALARY_FOR_EPF,$dep_ADDONS_TO_EPF_SALARY,$dep_SALARY_TOTAL_2,
                                                $dep_ADDONS_TO_INCENTIVE,$dep_ETF_3,$dep_EPF_12)
{
//    $rep->NewLine(); 
    $rep->Font('bold'); 
    $rep->TextCol(2, 3, 'Head Count: '.$dep_head_count);
    $rep->TextCol(3, 4, number_format($dep_SALARY_FOR_EPF,2));
    $rep->TextCol(4, 5, number_format($dep_ADDONS_TO_EPF_SALARY,2));
    $rep->TextCol(5, 6, number_format($dep_SALARY_TOTAL_2,2));
    $rep->TextCol(6, 7, number_format($dep_ADDONS_TO_INCENTIVE,2));
    $rep->TextCol(7, 8, number_format($dep_ETF_3,2));
    $rep->TextCol(8, 9, number_format($dep_EPF_12,2));
    $rep->Font(''); 
    $rep->NewLine(); 
}

function print_OT()
{
    global $path_to_root, $pic_height;
        
        $departments = $_POST['PARAM_0'];
        $payroll_run_id = $_POST['PARAM_1'];
        $destination  = $_POST['PARAM_2'];
        
        
        
	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, 200, 300,420,470,530,590,650,710,770);
        $headers = array('Emp Code - Employee','job Category','Designation', 'EPF Salary','EPF Salary+','Incentive','Incentive+','ETF 3%','EPF 12%');
        $aligns = array('left',	'left','left','right','right','right','right','right','right');
        $rep = new FrontReport(_('Head Count Report'), "head_count", 'A4', 9, $orientation);

        $params =   array( 	0 => '',
                                1 => '',
                                2 => '',
                                3 => ''
                    );
        $rep->SetHeaderType('form_960');
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
        $rep->NewPage();
        
        
        if($payroll_run_id> 0){
            $payroll_run = get_payroll_run_details($payroll_run_id);
            $date = $payroll_run['period_start_date'];
        }
        else{
            $date = '4000-01-01';
        }
        
        $dep_head_count = 0;
        $dep_SALARY_FOR_EPF = 0;
        $dep_ADDONS_TO_EPF_SALARY = 0;
        $dep_SALARY_TOTAL_2 = 0;
        $dep_ADDONS_TO_INCENTIVE = 0;
        $dep_ETF_3 = 0;
        $dep_EPF_12 = 0;
        
        $tot_head_count = 0;
        $tot_SALARY_FOR_EPF = 0;
        $tot_ADDONS_TO_EPF_SALARY = 0;
        $tot_SALARY_TOTAL_2 = 0;
        $tot_ADDONS_TO_INCENTIVE = 0;
        $tot_ETF_3 = 0;
        $tot_EPF_12 = $ADDONS_TO_EPF_SALARY = $SALARY_TOTAL_2 = $ADDONS_TO_INCENTIVE = $ETF_3 = $EPF_12 = 0;
                
        $current_department = "";
        $department_employees_res = get_department_employees($departments, $date);
        while ($department_employees = db_fetch($department_employees_res)){
            if($current_department != $department_employees['department_code']){
                if($current_department != ''){
                display_department_sub_totals($rep,$dep_head_count,$dep_SALARY_FOR_EPF,$dep_ADDONS_TO_EPF_SALARY,$dep_SALARY_TOTAL_2,
                                                $dep_ADDONS_TO_INCENTIVE,$dep_ETF_3,$dep_EPF_12);
                
                $dep_head_count = 0;
                $dep_SALARY_FOR_EPF = 0;
                $dep_ADDONS_TO_EPF_SALARY = 0;
                $dep_SALARY_TOTAL_2 = 0;
                $dep_ADDONS_TO_INCENTIVE = 0;
                $dep_ETF_3 = 0;
                $dep_EPF_12 = 0;                    
                }
                display_department_sub_heading($rep, $department_employees);
                $current_department = $department_employees['department_code'];
            }
            $salary_details_res = get_employeee_salary_details($payroll_run_id, $department_employees['employee_code']);
            while ($salary_details = db_fetch($salary_details_res)){
                switch ($salary_details['component']) {
                    case 'SALARY_FOR_EPF':
                        $SALARY_FOR_EPF = $salary_details['value'];
                        break;
                    case 'ADDONS_TO_EPF_SALARY':
                        $ADDONS_TO_EPF_SALARY = $salary_details['value'];
                        break;
                    case 'SALARY_TOTAL_2':
                        $SALARY_TOTAL_2 = $salary_details['value'];
                        break;
                    case 'ADDONS_TO_INCENTIVE':
                        $ADDONS_TO_INCENTIVE = $salary_details['value'];
                        break;
                    case 'TAKEHOME_BASIC':
                        $TAKEHOME_BASIC = $salary_details['value'];
                        break;
                    case 'TAKEHOME_INCENTIVE':
                        $TAKEHOME_INCENTIVE = $salary_details['value'];
                        break;
                    case 'ETF 3%':
                        $ETF_3 = $salary_details['value'];
                        break;
                    case 'EPF 12%':
                        $EPF_12 = $salary_details['value'];
                        break;
                    default:
                        break;
                }
            }
                $name = "";
                    $name = "[".$department_employees['employee_code']."] - ".$department_employees['initials'].
                            " ".$department_employees['last_name']." (".$department_employees["first_name"].") ";
                                   
                $current_employee = $name;

                //$rep->NewLine(); 
                $rep->TextCol(0, 1, $current_employee );
                $rep->TextCol(1, 2, $department_employees['job_category_name'], -5);
                $rep->TextCol(2, 3, $department_employees['designation']);
                $rep->TextCol(3, 4, $SALARY_FOR_EPF);
                $rep->TextCol(4, 5, $ADDONS_TO_EPF_SALARY);
                $rep->TextCol(5, 6, $SALARY_TOTAL_2);
                $rep->TextCol(6, 7, $ADDONS_TO_INCENTIVE); 
                $rep->TextCol(7, 8, $ETF_3);
                $rep->TextCol(8, 9, $EPF_12);
                $rep->NewLine();    
                
                $dep_head_count += 1;
                $dep_SALARY_FOR_EPF += $SALARY_FOR_EPF;
                $dep_ADDONS_TO_EPF_SALARY += $ADDONS_TO_EPF_SALARY;
                $dep_SALARY_TOTAL_2 += $SALARY_TOTAL_2;
                $dep_ADDONS_TO_INCENTIVE += $ADDONS_TO_INCENTIVE;
                $dep_ETF_3 += $ETF_3;
                $dep_EPF_12 += $EPF_12;
                
                $tot_head_count += 1;
                $tot_SALARY_FOR_EPF+= $SALARY_FOR_EPF;
                $tot_ADDONS_TO_EPF_SALARY += $ADDONS_TO_EPF_SALARY;
                $tot_SALARY_TOTAL_2 += $SALARY_TOTAL_2;
                $tot_ADDONS_TO_INCENTIVE += $ADDONS_TO_INCENTIVE;
                $tot_ETF_3 += $ETF_3;
                $tot_EPF_12 += $EPF_12;
        }

        display_department_sub_totals($rep,$dep_head_count,$dep_SALARY_FOR_EPF,$dep_ADDONS_TO_EPF_SALARY,$dep_SALARY_TOTAL_2,
                                                $dep_ADDONS_TO_INCENTIVE,$dep_ETF_3,$dep_EPF_12);
        if($departments =='-1')
        {
            $rep->NewLine(); 
            $rep->Font('bold'); 
            $rep->TextCol(0, 2, "Report Totals:");
            display_department_sub_totals($rep,$tot_head_count,$tot_SALARY_FOR_EPF,$tot_ADDONS_TO_EPF_SALARY,$tot_SALARY_TOTAL_2,
                                                    $tot_ADDONS_TO_INCENTIVE,$tot_ETF_3,$tot_EPF_12);
        
             $rep->Font(''); 
        }
        $rep->End();       
	}
        



