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

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

print_emp_detials();

function get_employees_for_report($current_emp,$resigned_emp,$gender,$marital_status)
{
	$sql =  "SELECT em.*,
                        lv.value_name em_type ,
                        bn.value_name bank_name,
                        bb.branch_name
                 FROM ".TB_PREF."employee_master em 
                 LEFT JOIN ".TB_PREF."list_values lv ON
                     em.employee_type_id=lv.id
                 LEFT JOIN ".TB_PREF."list_values gn ON
                     gn.value_code=em.gender AND gn.list_code='GENDER'
                 LEFT JOIN ".TB_PREF."list_values bn ON 
                      bn.id=em.bank_id
                 LEFT JOIN ".TB_PREF."bank_branches bb ON 
                      bb.id=em.bank_branch_id
                 LEFT JOIN ".TB_PREF."list_values mt ON 
                      mt.value_code=em.marital_status AND mt.list_code='MARITAL_STATUS'";
                
                if($resigned_emp && $current_emp )
                {
                  if($gender !='-1')
                  {
                      $sql .= " AND gn.id=". db_escape($gender);
                  }
                  if($marital_status !='-1')
                  {
                      $sql .= " AND mt.id=". db_escape($marital_status);
                  }
                }
                elseif($resigned_emp)
                {
                   $sql .= " AND em.resigned=1 ";
                   if($gender !='-1')
                  {
                      $sql .= " AND gn.id=". db_escape($gender);
                  }
                  if($marital_status !='-1')
                  {
                      $sql .= " AND mt.id=". db_escape($marital_status);
                  }
                
                    
                }
                elseif($current_emp)
                {
                  $sql .= " AND em.date_of_joining <=".db_escape(date('Y-m-d'))." AND em.date_of_resignation >= ".db_escape(date('Y-m-d'));
                   if($gender !='-1')
                  {
                      $sql .= " AND gn.id=". db_escape($gender);
                  }
                  if($marital_status !='-1')
                  {
                      $sql .= " AND mt.id=". db_escape($marital_status);
                  }  
                }
                else
                {
                    $sql .= " AND em.resigned=1111111 ";
                }
                
                $sql .=" order by em.resigned,em.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 
                WHERE em.employee_code = ".  db_escape($employee);
        
   	$result = db_query($sql, "Employee details could not be retrieved");

        return db_fetch($result);
}

function get_employee_curent_departments($employee,$to_date)
{
        $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.start_date <= ".db_escape($to_date)." AND 
                          ed.end_date >= ".db_escape($to_date)." AND
                          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_current_designation($employee,$to_date)
{
    
        $sql = "SELECT IFNULL(edl.designation,'No Designation Entered') designation
            FROM ".TB_PREF."employment_details edl,
                 ".TB_PREF."employee_master em                
            WHERE em.employee_code = edl.employee_code AND 
                    edl.contract_start_date <= ".db_escape($to_date)." AND 
                    edl.contract_end_date >= ".db_escape($to_date)." 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");
        return db_fetch($result);
    
}

function cal_age($date_of_birth)
{
    $today=date2sql(Today());
    $years =date_diff2(sql2date($today), sql2date($date_of_birth), 'y');
    $effective_year = date('Y-m-d', strtotime("+$years years", strtotime($date_of_birth)));
    $months =date_diff2(sql2date($today), sql2date($effective_year), 'm');
    if(0>$months)
    {
        $years=$years-1; 
        $effective_year = date('Y-m-d', strtotime("+$years months", strtotime($date_of_birth)));
        $months = date_diff2(sql2date($today), sql2date($effective_year), 'm');
        $effective_date = date('Y-m-d', strtotime("+$months months", strtotime($effective_year)));
    }
    else{

        $effective_date = date('Y-m-d', strtotime("+$months months", strtotime($effective_year)));
    }
     $days = date_diff2(sql2date($today), sql2date($effective_date), 'd');

    if(0>$days)
    {

        $months=$months-1; 
        $effective_date = date('Y-m-d', strtotime("+$months months", strtotime($effective_year)));
        $days = abs(date_diff2(sql2date($effective_date),sql2date($today), 'd'));

    }
    return $years.' y '. $months.' m '.$days .' d';
}

function get_employee_job_category($employee,$to_date)
{
    
        $sql = "SELECT IFNULL(lv.value_name,'No Job Category Entered') job_category
            FROM ".TB_PREF."employment_details edl,
                 ".TB_PREF."employee_master em,  
                 ".TB_PREF."list_values lv   
            WHERE em.employee_code = edl.employee_code AND 
                    edl.contract_start_date <= ".db_escape($to_date)." AND 
                    edl.contract_end_date >= ".db_escape($to_date)." AND 
                    em.employee_code =". db_escape($employee)." AND lv.id = edl.job_category_id 
                    ORDER BY edl.contract_start_date DESC LIMIT 1";
                
        
        $result=db_query($sql,"an job category could not be retreived");
        return db_fetch($result);
    
}

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

        $current_emp=$_POST['PARAM_0'];
        $resigned_emp=$_POST['PARAM_1'];
        $gender = $_POST['PARAM_2'];
        $marital_status = $_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 = 'L';
       

        $cols = array(0,100,350,550,750,900,1050,1150,1250,1350,1450,1550,1650,1750,1850,2150,2350,2550,2700,2800,2900,3000);
        $headers = array('Emp No', 'Employee Name','Department','Designation','Employee Type','Job Category','Date of Join','Date Resigned','EPF No.','NIC','Date of Birth','Age','Gender','Marital Status','Address','Office Telephone','Office Mobile','Bank Acct Name','Bank','Branch','Bank Account No.');
        $aligns = array('left','left','left','left','left','left','left','left','left','left','left','left','left','left','left','left','left','left','left','left','left');
        $rep = new FrontReport(_('Employee Detials'), "Employee Detials", 'A3', 9, $orientation);

        if($current_emp !='0')
        {
            $current_employes='Yes';
        }
        else{
            $current_employes='No';
        }
        if($resigned_emp !='0')
        {
            $resigned_employes='Yes';
        }
       else{
            $resigned_employes='No';
        }
        if($resigned_emp !='0' && $current_emp !='0')
        {
            $resigned_employes='Yes';
            $current_employes='Yes';
        }
        if($gender =='944')
        {
            $gender_type='Female';
        }
        if($gender =='943')
        {
             $gender_type='Male';
        }
        if($gender =='-1')
        {
             $gender_type='All';
        }
        if($marital_status =='945')
        {
            $marital_status_type='Married';
        }
        if($marital_status =='946')
        {
             $marital_status_type='Single';
        }
        if($marital_status =='-1')
        {
             $marital_status_type='All';
        }
        
        $params =   array( 	0 => $current_employes,
                                1 => $resigned_employes,
                                2 =>$gender_type,
                                3 =>$marital_status_type
                                
                    );
        
        
        $rep->SetHeaderType('form_964');
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
        $rep->NewPage();
      
//        $rep->row - 4;
        $res = get_employees_for_report($current_emp,$resigned_emp,$gender,$marital_status);
    
       	while ($employee_data = db_fetch($res))
	{
//            $employeee_details = get_employeee_details($employee_data['employee_code']);
                $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($employee_data['employee_code'],date('Y-m-d')); // link with the list values
                $designation_data=get_employee_current_designation($employee_data['employee_code'],date('Y-m-d'));
                $job_cat_data=get_employee_job_category($employee_data['employee_code'],date('Y-m-d'));
                                
//                        $rep->NewLine(); 
                        $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 Desigation Entered";
                        if(isset($department_data['department_name']) && $department_data['department_name'] !='')
                        {
                            $rep->TextCol(2, 3,$department_data['department_name']);
                        }
                        else
                        {
                           $rep->TextCol(2, 3,$dep); 
                        }
                        if(isset($designation_data['designation']) && $designation_data['designation'] !='')
                        {
                        $rep->TextCol(3, 4,$designation_data['designation']);
                        }
                        else
                        {
                           $rep->TextCol(3, 4,$desi);  
                        }
                        $rep->TextCol(4, 5, $employee_data['em_type']);
                        $rep->TextCol(5, 6, isset($job_cat_data['job_category']) ? $job_cat_data['job_category'] : '');
                        $rep->TextCol(6,7, sql2date($date_of_joining));
                        
                        if($employee_data['date_of_resignation']=='3000-01-01')
                        {
                        $rep->TextCol(7, 8,'Not Resigned');
                        }
                        else
                        {
                        $rep->TextCol(7, 8,sql2date($date_resigned));   
                        }
                       // $rep->TextCol(6, 7,$employee_data['work_email_id']); 
                        $rep->TextCol(8, 9,$employee_data['epf_no']); 
                        $rep->TextCol(9, 10,$employee_data['nic']); 
                        $rep->TextCol(10, 11,sql2date($employee_data['date_of_birth']));
                        
                        $employee_age=cal_age($employee_data['date_of_birth']);
                                
                        $rep->TextCol(11, 12,$employee_age);
                        $gender='';
                        if($employee_data['gender']=='M')
                        {
                            $gender='Male';
                        }
                        if($employee_data['gender']=='F')
                        {
                             $gender='Female';
                        }
                        
                        $rep->TextCol(12, 13,$gender); 
                        
                        $marital_status='';
                        if($employee_data['marital_status']=='M')
                        {
                            $marital_status='Married';
                        }
                        if($employee_data['marital_status']=='S')
                        {
                             $marital_status='Single';
                        }
                        
                        $rep->TextCol(13, 14,$marital_status); 
                        $rep->TextCol(14, 15,$employee_data['address']);
                        $rep->TextCol(15, 16,$employee_data['work_telephone_no']);
                        $rep->TextCol(16, 17,$employee_data['office_mobile_no']); 
                        
                        $rep->TextCol(17, 18,$employee_data['bank_account_name']); 
                        $rep->TextCol(18, 19,$employee_data['bank_name']);
                        $rep->TextCol(19, 20,$employee_data['branch_name']); 
                        $rep->TextCol(20, 21,$employee_data['bank_account_no']);
                       
                      
                        $rep->NewLine();
                        
//                        $rep->NewLine();
                         
                }
                 $rep->End();       
	}
        



