<?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");

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

print_payslips();

function get_employee_for_report($from_employee, $to_employee)
{
	$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 .= " ORDER BY employee_code";
	return db_query($sql,"an employee could not be retreived");

}

function get_salary_components_details($employee_code, $salary_component_group = null, $date)
{
        $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 <> ''    
                AND sc.effective_date <=".db_escape(date2sql($date))." AND sc.end_date >=".db_escape(date2sql($date));
        
            if($salary_component_group)
            {
                $sql .=" AND scgn.value_code =".db_escape($salary_component_group);
            }
            
            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 could not be retreived");
}

function get_salary_components_details_future($employee_code, $salary_components_types = null,$date)
{
       $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 <> ''    
                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)
{
    $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 <> ''    
                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,$name,$rep)
{
    $previous_employee = "";
    while ($payslip_group = db_fetch($payslip_result))
                {
                    $current_employee = $name;
                    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"]);
                    }
                    
                    if($current_employee == $previous_employee ){
                        
                    }
                    else{
                        if($previous_employee == ""){
                            
                        }
                        else{
                            $rep->NewLine();
                        }
//                        

                        $rep->NewLine(); 
                    }
                                               
                        $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, $payslip_group['amount']);
                        $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;
                    $previous_employee = $current_employee;    
                }   
}
//----------------------------------------------------------------------------------------------------
function print_all_salary_componants($employeee_details,$date,$rep)
{       $current_employee = "";
        $previous_employee = "";

        $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'];
        }
        $current_employee = $name;
        
        $rep->Font('bold');
        $rep->TextCol(0, 6, "Name"." : ".$name.", EPF No"." : ".$epf_no);

        $rep->NewLine();
        $rep->TextCol(0, 6, "Active Salary Componants");
        $rep->Font();
        $rep->NewLine();
        
        print_active_salary_components($employeee_details,$date,$rep, $name);
        

        $rep->NewLine();
        $rep->Font('bold');        
        $rep->NewLine();
        $rep->TextCol(0, 6, "Future Salary Componants");                       
        $rep->NewLine(); 
        $rep->Font('');
        
        print_future_salary_components($employeee_details,$date,$rep, $name);
        
        $rep->Font('b');
        $rep->NewLine(2);
        $rep->TextCol(0, 6, "Expired Salary Componants");   
        $rep->Font('');
        
        print_expired_salary_components($employeee_details,$date,$rep, $name);
        
}

function print_salary_component_group($employeee_details,$date,$rep, $name, $group_code)
{
    $payslip_result = get_salary_components_details($employeee_details['employee_code'], $group_code, $date);
        $salary_component_group = get_listvalue_by_code('SALARY_COMPONENT_GROUPS', $group_code);
        $rep->TextCol(0, 6, $salary_component_group['value_name']." Componants Group");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Basic Salary components found");
            $rep->Font('');
        }
        else{
             print_salary_component($payslip_result, $name, $rep);         
        }
}

function print_active_salary_components($employeee_details,$date,$rep, $name)
{
        
       
        $payslip_result = get_salary_components_details($employeee_details['employee_code'], 'BASIC_SALARY',$date);
        $salary_component_group = get_listvalue_by_code('SALARY_COMPONENT_GROUPS', 'BASIC_SALARY');
        $rep->TextCol(0, 6, $salary_component_group['value_name']." Componants");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Basic Salary components found");
            $rep->Font('');
        }
        else{
             print_salary_component($payslip_result, $name, $rep);         
        }
        
        print_salary_component_group($employeee_details,$date,$rep, $name, 'BASIC_SALARY');
        
        $rep->NewLine();        
        $payslip_result_insentives = get_salary_components_details($employeee_details['employee_code'],  'INCENTIVES',$date);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Incentive Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_insentives)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Incentive Salary components found");
            $rep->Font('');
        }
        else
        {
                 print_salary_component($payslip_result_insentives,$name,$rep);             
	             
        }
        
        $rep->NewLine();        
        $payslip_result_ot = get_salary_components_details($employeee_details['employee_code'],  'OVERTIME',$date);
        $rep->NewLine(2);
        $rep->TextCol(0, 6, "Overtime Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_ot)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No OT salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_ot,$name,$rep);    
	             
        }
        
        $rep->NewLine();        
        $payslip_result_allownances = get_salary_components_details($employeee_details['employee_code'],  'ALLOWANCES',$date);
        $rep->NewLine(2);
        $rep->TextCol(0, 6, "Allowances Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_allownances)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Allowances salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_allownances,$name,$rep);    
	             
	}
        
        $rep->NewLine();        
        $payslip_result_staturory_deductions = get_salary_components_details($employeee_details['employee_code'],  'STATUTORY_DEDUCTIONS',$date);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Staturory Deductions Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_staturory_deductions)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Staturory Deductions salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_staturory_deductions,$name,$rep);    
	             
	}
}

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



        
function print_future_salary_components($employeee_details,$date,$rep, $name)
{
        $payslip_result_future_basic = get_salary_components_details_future($employeee_details['employee_code'],  'BASIC_SALARY',$date);
        $rep->TextCol(0, 6, "Basic Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_future_basic)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Basic salary componants found");
            $rep->Font('');
        }
        else
        {
            print_salary_component($payslip_result_future_basic,$name,$rep);            
	             
	}
        
       
        $payslip_result_future_group = get_salary_components_details_future($employeee_details['employee_code'],  'SALARY_COMPONENT_GROUPS',$date);
        $rep->NewLine(2);
        $rep->TextCol(0, 6, "Group Salary Components ");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_future_group)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No group salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_future_group,$name,$rep);            
	             
	}
        
        $rep->NewLine(); 
        $rep->Font('');
        $payslip_result_future_insen = get_salary_components_details_future($employeee_details['employee_code'],  'INCENTIVES',$date);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Salary Incentive Groups");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_future_insen)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Incentive salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_future_insen,$name,$rep);            
	             
	}
        
        $rep->NewLine(); 
        $rep->Font('');
        $payslip_result_future_ot = get_salary_components_details_future($employeee_details['employee_code'],  'OVERTIME',$date);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Overtime Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_future_ot)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No OT salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_future_ot,$name,$rep);            
	             
	}
        
        $rep->NewLine();        
        $payslip_result_allownances_future = get_salary_components_details_future($employeee_details['employee_code'],  'ALLOWANCES',$date);
        $rep->NewLine(2);
        $rep->TextCol(0, 6, "Allowances Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_allownances_future)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Allowances salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_allownances_future,$name,$rep);    
	             
	}
        
        $rep->NewLine();        
        $payslip_result_staturory_deductions_future = get_salary_components_details_future($employeee_details['employee_code'],  'STATUTORY_DEDUCTIONS',$date);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Staturory Deductions Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_staturory_deductions_future)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Staturory Deductions salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_staturory_deductions_future,$name,$rep);    
	             
	}
}

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

      
function print_expired_salary_components($employeee_details,$date,$rep, $name)
{        
        $payslip_result_old_basic = get_salary_components_details_old($employeee_details['employee_code'],  'BASIC_SALARY',$date);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Basic Salary Components");        
       $rep->NewLine();
        if(db_num_affected_rows($payslip_result_old_basic)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Basic salary componants found");
            $rep->Font('');
        }
        else
        {
            print_salary_component($payslip_result_old_basic,$name,$rep);            
	             
	}
        
        $payslip_result_old_group = get_salary_components_details_old($employeee_details['employee_code'],  'SALARY_COMPONENT_GROUPS',$date);
        $rep->NewLine(2);
        $rep->TextCol(0, 6, "Group Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_old_group)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No group salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_old_group,$name,$rep);            
	             
	}
        
        $rep->NewLine(); 
        $rep->Font('');
        $payslip_result_old_inse = get_salary_components_details_old($employeee_details['employee_code'],  'INCENTIVES',$date);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Incentive Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_old_inse)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Incentive salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_old_inse,$name,$rep);            
	             
	}
        
        $rep->NewLine(); 
        $rep->Font('');
        $payslip_result_old_ot = get_salary_components_details_old($employeee_details['employee_code'],  'OVERTIME',$date);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Overtime Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_old_ot)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No OT salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_old_ot,$name,$rep);            
	             
	}
        
         $rep->NewLine();        
        $payslip_result_allownances_old = get_salary_components_details_old($employeee_details['employee_code'],  'ALLOWANCES',$date);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Allowances Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_allownances_old)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Allowances salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_allownances_old,$name,$rep);    
	             
	}
        
        $rep->NewLine();        
        $payslip_result_staturory_deductions_old = get_salary_components_details_old($employeee_details['employee_code'],  'STATUTORY_DEDUCTIONS',$date);
        $rep->NewLine();
        $rep->TextCol(0, 6, "Staturory Deductions Salary Components");        
        $rep->NewLine();
        if(db_num_affected_rows($payslip_result_staturory_deductions_old)==null){
            $rep->Font('I');
            $rep->TextCol(0, 6, "No Staturory Deductions salary componants found");
            $rep->Font('');
        }
        else
        {
                print_salary_component($payslip_result_staturory_deductions_old,$name,$rep);    
	             
	}
        
        
}



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

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

        $cols = array(0, 150, 190, 230, 280, 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); 
        
        while ($employee_info = db_fetch($res))
	{
            
            
            print_all_salary_componants($employee_info,$date,$rep);
            $rep->NewPage();

           
	}
     
       

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

?>
