<?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 . "/inventory/includes/inventory_db.inc");
include_once($path_to_root . "/includes/db/manufacturing_db.inc");
include_once($path_to_root . "/payroll/includes/db/salary_components_db.inc");
include_once($path_to_root . "/payroll/includes/db/employees_db.inc");
include_once($path_to_root . "/admin/db/list_values_db.inc");
include_once($path_to_root . "/payroll/includes/db/job_details_db.inc");


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

print_payslips();

function get_employee_for_report($from_employee, $to_employee,$date)
{
	$sql = "SELECT * FROM ".TB_PREF."employee_master WHERE 1 ";
        
        if(strlen($from_employee) > 0){
            $sql .= " AND employee_code >= ".db_escape($from_employee);
        }
        
        if(strlen($to_employee) > 0){
            $sql .= " AND employee_code <= ".db_escape($to_employee);
        }
        
	$sql .= " AND date_of_resignation >=".db_escape(date2sql($date))." ORDER BY employee_code";
	return db_query($sql,"an employee could not be retreived");

}

function get_salary_components_details($employee_code, $salary_component_group, $date, $type)
{
    $salary_group_id = 520;
        $sql =" SELECT 
                        scgn.value_name salary_component_group_name ,
                        scn.value_name salary_component_name ,
                        stn.value_name salary_total_name,
                        tn.value_name table_name,
                        dn.value_name calculation_type_name ,
                        scg.print_seq,
                        sc.*
                FROM ".TB_PREF."salary_components  sc
                        LEFT JOIN ".TB_PREF."salary_component_grouping scg ON
                                    sc.salary_component_id = scg.component_id 
                        LEFT JOIN ".TB_PREF."list_values scn ON 
                                    scg.component_id = scn.id 
                        LEFT JOIN ".TB_PREF."list_values scgn ON 
                                    scg.component_group_id = scgn.id 
                        LEFT JOIN ".TB_PREF."list_values stn ON 
                                    sc.salary_total_code = stn.value_code AND stn.list_code = 'SALARY_TOTALS' 
                        LEFT JOIN ".TB_PREF."list_values tn ON 
                                    sc.table_id = tn.id
                        LEFT JOIN ".TB_PREF."list_values dn ON                 
                                    sc.calculation_type = dn.value_code AND dn.list_code = 'SALARY_CAL_TYPES'

            WHERE 
                        (
                        (sc.employee_code <> ''    
                        AND sc.employee_code =".db_escape($employee_code)."
                        AND sc.type IN(".SCT_ATTENDANCE_ALLOWANCES.","
                                        .SCT_EMPLOYEE_ADDITIONS.","
                                        .SCT_EMPLOYEE_ALLOWANCES_OVER_USAGE.","
                                        .SCT_EMPLOYEE_DEDUCTIONS.","
                                        .SCT_MOBILE_OVER_USAGE.","
                                        .SCT_PERFORMANCE_RATE_INCENTIVES.","
                                        .SCT_PERFORMANCE_VALUE_INCENTIVES.","
                                        .SCT_WORKTIME_DEFICITS.")) 
                        OR
                        (sc.employee_code <> '' 
                        AND sc.employee_code = ".db_escape($salary_group_id)."     
                        AND sc.type IN(".SCT_GROUP_ADDITIONS.","
                                        .SCT_GROUP_DEDUCTIONS."))
                        OR
                        (sc.employee_code = ''     
                        AND sc.type IN(".SCT_COMPANY_ADDITIONS.","
                                        .SCT_COMPANY_DEDUCTIONS."))
                        )";
        if($type == "ACTIVE"){
                        $sql .= " AND sc.effective_date <=".db_escape(date2sql($date))." AND sc.end_date >=".db_escape(date2sql($date));
        }
        elseif($type == "FUTURE"){
                        $sql .= " AND sc.effective_date >".db_escape(date2sql($date));
        }
        elseif($type == "EXPIRED"){
                        $sql .= " AND sc.end_date <".db_escape(date2sql($date));
        }
        else{ // Unknown type
            $sql .= " AND sc.effective_date >".db_escape(INFINITE_END_DATE);
        }
        $sql .= " AND scgn.value_code =".db_escape($salary_component_group)." 
                        ORDER BY sc.deduction, scg.print_seq, effective_date DESC";    
            
	return db_query($sql,"Salary components could not be retreived");
}

function get_salary_components_details_future($employee_code, $salary_components_types = null, $date = null)
{
       $sql =" SELECT 
                    scgn.value_name salary_component_group_name ,
                    scn.value_name salary_component_name ,
                    stn.value_name salary_total_name,
                    tn.value_name table_name,
                    dn.value_name calculation_type_name ,
                    sc.*
                FROM ".TB_PREF."salary_components  sc
                LEFT JOIN ".TB_PREF."salary_component_grouping scg ON
                            sc.salary_component_id = scg.component_id 
                LEFT JOIN ".TB_PREF."list_values scn ON 
                            scg.component_id = scn.id 
                LEFT JOIN ".TB_PREF."list_values scgn ON 
                            scg.component_group_id = scgn.id 
                LEFT JOIN ".TB_PREF."list_values stn ON 
                            sc.salary_total_code = stn.value_code AND stn.list_code = 'SALARY_TOTALS' 
                LEFT JOIN ".TB_PREF."list_values tn ON 
                            sc.table_id = tn.id
                LEFT JOIN ".TB_PREF."list_values dn ON                 
                            sc.calculation_type = dn.value_code AND dn.list_code = 'SALARY_CAL_TYPES'

            WHERE sc.employee_code <> '' ";
       
            if (is_date($date)) {
                $sql .= " AND sc.effective_date >". db_escape(date2sql($date));
            }
        
            if($salary_components_types)
            {
                $sql .=" AND scgn.value_code =".db_escape($salary_components_types);
            }
            
            if($employee_code != "")
            {
                $sql .=" AND sc.employee_code =".db_escape($employee_code);
            }
            
            $sql .= " ORDER BY scg.print_seq, sc.effective_date DESC";    
    
        	
                
	return db_query($sql,"Salary components for future could not be retreived");
}

function get_salary_components_details_old($employee_code, $salary_components_types = null, $date = null)
{
    $sql =" SELECT 
                    scgn.value_name salary_component_group_name ,
                    scn.value_name salary_component_name ,
                    stn.value_name salary_total_name,
                    tn.value_name table_name,
                    dn.value_name calculation_type_name ,
                    sc.*
                FROM ".TB_PREF."salary_components  sc
                LEFT JOIN ".TB_PREF."salary_component_grouping scg ON
                            sc.salary_component_id = scg.component_id 
                LEFT JOIN ".TB_PREF."list_values scn ON 
                            scg.component_id = scn.id 
                LEFT JOIN ".TB_PREF."list_values scgn ON 
                            scg.component_group_id = scgn.id 
                LEFT JOIN ".TB_PREF."list_values stn ON 
                            sc.salary_total_code = stn.value_code AND stn.list_code = 'SALARY_TOTALS' 
                LEFT JOIN ".TB_PREF."list_values tn ON 
                            sc.table_id = tn.id
                LEFT JOIN ".TB_PREF."list_values dn ON                 
                            sc.calculation_type = dn.value_code AND dn.list_code = 'SALARY_CAL_TYPES'

            WHERE sc.employee_code <> '' ";
    
            if (is_date($date)) {
                $sql .= " AND sc.end_date <". db_escape(date2sql($date));
            }  
        
            if($salary_components_types)
            {
                $sql .=" AND scgn.value_code =".db_escape($salary_components_types);
            }
            
            if($employee_code != "")
            {
                $sql .=" AND sc.employee_code =".db_escape($employee_code);
            }
            
            $sql .= " ORDER BY scg.print_seq, sc.effective_date DESC"; 
    
   
	return db_query($sql,"Salary components for future could not be retreived");
}

function get_payroll_employees($payroll_run_id, $employee)
{
	$sql = " SELECT DISTINCT prd.employee_code 
                 FROM ".TB_PREF."payroll_run_details prd, 
                        ".TB_PREF."payroll_runs pr   
                 WHERE prd.payroll_run_id = pr.id AND 
                        pr.status = 'R' AND 
                        prd.employee_code <> '' AND 
                        pr.id = ".db_escape($payroll_run_id)  ;
        
        if(strlen($employee) > 0){
            $sql .= " AND prd.employee_code = ".db_escape($employee);
        }
	$sql .= " ORDER BY prd.employee_code";
	return db_query($sql, "Could not retreive payroll employess");
}





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

        return db_fetch($result);
}


function get_employeee_names($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 print_salary_component($payslip_result, $rep)
{
    $value_total = 0;
    while ($payslip_group = db_fetch($payslip_result))
    {
        if($payslip_group["deduction"] == 1){
            $deduction = "Yes";
        }
        else {
            $deduction = "No";
        }

        if($payslip_group["inactive"] == 1){
            $inactive = "Yes";
        }
        else {
            $inactive = "No";
        }

        if($payslip_group["end_date"] == INFINITE_END_DATE){
            $end_date = "";
        }
        else{
            $end_date = sql2date($payslip_group["end_date"]);
        }                    


        //$rep->fontSize -= 2;
        $rep->TextCol(0, 1, $payslip_group['salary_component_name']);
        $rep->TextCol(1, 2, $deduction);
        $rep->TextCol(2, 3, $payslip_group['calculation_type_name']);
        $rep->TextCol(3, 4, number_format($payslip_group['amount'],2));
        $rep->TextCol(4, 5, $payslip_group['salary_total_name']);
        $rep->TextCol(5, 6, $payslip_group['table_name']);
        $rep->TextCol(6, 7, sql2date($payslip_group["effective_date"]));
        $rep->TextCol(7, 8, $end_date);
        $rep->TextCol(8, 9, $inactive);
        $rep->NewLine();
//                        $rep->NewLine();
        $rep->Font();
        //$rep->fontSize += 2;  

        if($payslip_group['calculation_type'] == "V" && $payslip_group["inactive"] == 0){
            if($payslip_group["deduction"] == 0){
                $value_total += $payslip_group['amount'];
            }
            else{
                $value_total -= $payslip_group['amount'];
            }
        }
    }   
    
    return $value_total;
                
                
}
//----------------------------------------------------------------------------------------------------
function print_all_salary_componants($employeee_details,$date,$rep, $future, $expired)
{
        $name = "";
        $epf_no = "";
        if($employeee_details){
            $name = "[".$employeee_details['employee_code']."] - ".$employeee_details['initials'].
                    " ".$employeee_details['last_name']." (".$employeee_details["first_name"].") ";
            $epf_no = $employeee_details['epf_no'];
        }
        
        $designation_name = '';
        $designation = get_employee_current_designation($employeee_details['employee_code'],date2sql($date));
            if (isset($designation['designation'])) {
                $designation_name = $designation['designation'];
            }
        
        $rep->Font('bold');
        $rep->TextCol(0, 6, "Name"." : ".$name.", EPF No"." : ".$epf_no);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Designation"." : ".$designation_name);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Active Salary Componants");
        $rep->Font();
        $rep->NewLine();
        
        print_salary_component_type($employeee_details,$date,$rep, 'ACTIVE');
        

        if($future){
            $rep->NewLine();
            $rep->Font('bold');        
            $rep->NewLine();
            $rep->TextCol(0, 6, "Future Salary Componants");                       
            $rep->NewLine(); 
            $rep->Font('');

            print_salary_component_type($employeee_details,$date,$rep, 'FUTURE');
        }

        if($expired){
            $rep->NewLine();
            $rep->Font('b');
            $rep->NewLine();
            $rep->TextCol(0, 6, "Expired Salary Componants");   
            $rep->Font('');
            $rep->NewLine(); 

            print_salary_component_type($employeee_details,$date,$rep, 'EXPIRED');
        }

        
}

function print_salary_component_group($employeee_details,$date,$rep, $type, $group_code)
{
    
    $payslip_result = get_salary_components_details($employeee_details['employee_code'], $group_code, $date, $type);
    
    $count = db_num_rows($payslip_result);
    if($count == 0){
        return;
    }
    $rep->fontSize -= 2;
    $salary_component_group = get_listvalue_by_code('SALARY_COMPONENT_GROUPS', $group_code);
    $rep->Font('b');
    $rep->TextCol(0, 6, $salary_component_group['value_name']." Componants Group");        
    $rep->Font('');
    $rep->NewLine();
    if(db_num_rows($payslip_result) == 0){
        
        $rep->Font('I');
        $rep->TextCol(1, 6, "No ".$salary_component_group['value_name']." components found");
        $rep->Font('');
        $rep->NewLine();

    }
    else{
        $group_total = print_salary_component($payslip_result, $rep); 

        $rep->fontSize += 1;
        $rep->Font('b');
        $rep->TextCol(1, 3, "Value Total");
        $rep->TextCol(3, 4, number_format($group_total,2));
        $rep->Font('');
        $rep->fontSize -= 1;
        
         
    }
    $rep->fontSize += 2;
    $rep->NewLine();
    
    return $count;
}

function print_salary_component_type($employeee_details,$date,$rep, $type)
{
    $printed_rows_count = 0;
    $result = get_all_list_values("SALARY_COMPONENT_GROUPS");
    while ($row = db_fetch($result)) {
        $printed_rows_count += print_salary_component_group($employeee_details,$date,$rep, $type, $row['value_code']);
    }
    
    if($printed_rows_count == 0){
        $rep->TextCol(1, 3, "No components found");
    }
}


function print_payslips()
{
    global $path_to_root, $pic_height;
        

    $date = $_POST['PARAM_0'];
    $from_employee = $_POST['PARAM_1'];
    $to_employee = $_POST['PARAM_2'];
    $print_future = $_POST['PARAM_3'];
    $print_expired = $_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 = 'P';

        $cols = array(0, 100, 150, 190, 250, 330, 420, 460,500,530);
        $headers = array('Component','Deduction.','Cal.Type','Amt/Per.'
            ,'Ref.Val','Table','Eff. Date','End Date','On Hold');
        $aligns = array('left',	'center', 'center', 'right', 'right', 'center', 'left', 'left', 'right');
       
   	$rep = new FrontReport(_('Salary Components'), "SalaryComponents", 'A4', 9, $orientation);
       
        
       if($from_employee != "")
        {
            
            $employee_detials = get_employeee_names($from_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"].") ";
             }
             $from_employeename = $name;
            
        }
        else
        {
            $from_employeename = "All";
            
        }
        
        if($to_employee != "")
        {
            
            $employee_detials = get_employeee_names($to_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"].") ";
             }
             $to_employeename = $name;
            
        }
        else
        {
            $to_employeename = "All";
            
        }

        $params =   array( 	0 => $date,
                                1 => $from_employeename,
                                2 => $to_employeename);
        $rep->SetHeaderType('form_954');      
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
        $rep->NewPage();
//        $rep->row - 4;
        
        $res = get_employee_for_report($from_employee, $to_employee,$date); 
        
        $first_employee = 1;
        while ($employee_info = db_fetch($res))
	{
            if(!$first_employee){
                $rep->NewPage(); 
            }
            print_all_salary_componants($employee_info, $date, $rep, $print_future, $print_expired);
            $first_employee = 0;
            
    
	}
     
       

	$rep->Line($rep->row - 4);
	$rep->NewLine();
    $rep->End();
}

?>
