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

print_emp_detials();


function get_employeee_details($employment_type)
{
        $sql = "SELECT em.* 
                FROM ".TB_PREF."employee_master em 
                WHERE em.resigned=0 ";
          
          if($employment_type != "-1")
          {
               $sql .= " AND em.employee_type_id = ".db_escape($employment_type);
          }
   	return $result = db_query($sql, "Employee details could not be retrieved");
}


function get_employee_current_designation($employee)
{
        $sql = "SELECT IFNULL(edl.designation,'No Designation Entered') designation,edl.contract_end_date contract_end_date
            FROM ".TB_PREF."employment_details edl,
                 ".TB_PREF."employee_master em                
            WHERE em.employee_code = edl.employee_code AND 
                    em.employee_code =". db_escape($employee)."
                    ORDER BY edl.contract_start_date DESC LIMIT 1";
                
        $result=db_query($sql,"an employee could not be retreived");
        if (db_num_rows($result) == 0) return -1;
        return db_fetch($result);
}

function get_employee_type($employee)
{   
        $sql = "SELECT *
            FROM ".TB_PREF."employment_details edl,
                 ".TB_PREF."employee_master em,  
                 ".TB_PREF."list_values lv   
            WHERE em.employee_code = edl.employee_code AND 
                    em.employee_code =". db_escape($employee)." AND 
                    lv.id = edl.employment_type_id 
                    ORDER BY edl.contract_start_date DESC LIMIT 1";
                
        $result=db_query($sql,"an employee type could not be retreived");
        return db_fetch($result);
}

function get_employee_curent_departments_between_end_dates($employee)
{
        $sql = "SELECT  ed.*, 
                    IFNULL(d.value_name,'No Department Entered') department_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                     
                    WHERE ed.employee_code =". db_escape($employee)."
                    ORDER BY ed.start_date DESC LIMIT 1";
        
        $result=db_query($sql,"an employee could not be retreived");
        return db_fetch($result);
}

function get_employee_department_name_and_emp_type($id)
{
	$sql="SELECT d.value_name  FROM ".TB_PREF."list_values d 
                        WHERE 
                        d.id=".db_escape($id);

	$result = db_query($sql,"Selected list value could not be retrieved");
	return db_fetch($result);
}

function print_emp_detials()
{
    global $path_to_root, $pic_height;
        
        $department = $_POST['PARAM_0'];
        $employment_type = $_POST['PARAM_1'];
        $contract_end_date_from = $_POST['PARAM_2'];
        $months= $_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,50,220,350,500,600,700,800);
        $headers = array('Employee No', 'Name','Division','Designation','Employee Type','Contract Start Date','Contract End Date');
        $aligns = array('left','left','left','left','left','left','left');
        $rep = new FrontReport(_('Contract Renewal Report'), "Contract Renewal Report", 'A4', 9, $orientation);
        
        
        if($department == '-1'){
            $department_name="All";
        }else{
            $emp_departments=get_employee_department_name_and_emp_type($department);
            $department_name=$emp_departments['value_name'];
        }
        if($employment_type == '-1'){
            $employment_type_name="All";
        }else{
            $emp_types=get_employee_department_name_and_emp_type($employment_type);
            $employment_type_name=$emp_types['value_name'];
        }
        
         $params =   array( 	0 => $department_name,
                                1 => $employment_type_name,
                                2 => $contract_end_date_from,
                                3 => $months,
                                4 => $destination,
                    );
        
        
        if(IS_NUMERIC($months)){
            
            $rep->SetHeaderType('form_980');
            $rep->Font();
            $rep->Info($params, $cols, $headers, $aligns);
            $rep->NewPage();
                      
            $res = get_employeee_details($employment_type);
        
//            $duedate = date('Y-m-d', strtotime("+$months months", strtotime($contract_end_date_from)));
            
            $duedate = add_months($contract_end_date_from, $months);
            while ($employee_data = db_fetch($res))
            {
                $name = "";
                if($employee_data){
                    $name = $employee_data['initials'].
                            " ".$employee_data['last_name']." (".$employee_data["first_name"].") ";
                                   }
                $current_employee = $name;

                $department_data=get_employee_curent_departments_between_end_dates($employee_data['employee_code']); // link with the list values
                if ((isset($department_data['department_id']) && $department_data['department_id'] > 0 && $department_data['department_id'] == $department) 
                     || $department == "-1") {
                    $designation_data=get_employee_current_designation($employee_data['employee_code']);
                    if($designation_data !='-1'){
                        $from_date=date2sql($contract_end_date_from);
                        $to_date=date2sql($duedate);
                        $contract_end_date=$designation_data['contract_end_date'];
                        
//                        if ($contract_end_date >= $from_date) {
//                            echo 'greater than';
//                        }else{
//                            echo 'Less than';
//                        }
//                        
//                        if ($contract_end_date <= $to_date) {
//                            echo 'less than';
//                        }else{
//                            echo 'grateer than';
//                        }
                        
                        if($contract_end_date >= $from_date ){
                            
                            if($contract_end_date <= $to_date){
                                
                                    $employee_type_data=get_employee_type($employee_data['employee_code']);

                                    $rep->TextCol(0, 1, $employee_data['employee_code'] );
                                    $rep->TextCol(1, 2, $current_employee );

                                    $date_of_joining = $employee_data['date_of_joining'];
                                    $date_resigned = $employee_data['date_of_resignation'];
                                    $dep="No Department Entered";
                                    $desi="No Designation Entered";
                                    if(isset($department_data['department_name']) && $department_data['department_name'] !='')
                                    {
                                        $rep->TextCol(2, 3,$department_data['department_name'],-2);
                                    }
                                    else
                                    {
                                       $rep->TextCol(2, 3,$dep); 
                                    }
                                    if($designation_data['designation'] !='')
                                    {
                                        $rep->TextCol(3, 4,$designation_data['designation'],-2);
                                    }
                                    else
                                    {
                                       $rep->TextCol(3, 4,$desi);  
                                    }
                                    $rep->TextCol(4, 5, $employee_type_data['value_name']);

                                    $contract_start_date = $contract_end_date = '';
                                    $emp_contract_result = get_employement_record($employee_data['employee_code']);
                                    $emp_contract_data = db_fetch($emp_contract_result, 'get_employement_record');
                                        if (isset($emp_contract_data['employee_code']) && strlen($emp_contract_data['employee_code']) > 0) {
                                            $contract_start_date = sql2date($emp_contract_data['start_date']);
                                            $contract_end_date = sql2date($emp_contract_data['end_date']);
                                        }

                                    $rep->TextCol(5, 6, $contract_start_date);
                                    $rep->TextCol(6, 7, $contract_end_date);

                                    $rep->NewLine();
                            }
                        }
                    }
                }
                      
            }
        }
        $rep->End();  
}




