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

print_leave_inquiry();


function get_all_current_employees($emp_code ,$department_id,$to_date)
{
        $sql = "SELECT ed.department_id,lv.value_name department_name,em.*,max(ed.start_date)
		FROM ".TB_PREF."employee_departments ed 
		LEFT JOIN ".TB_PREF."list_values lv ON lv.id=ed.department_id 
		LEFT JOIN ".TB_PREF."employee_master em ON em.employee_code=ed.employee_code WHERE em.date_of_joining <= ".db_escape($to_date);
        
        if($emp_code != 0)
        {
             $sql .= " AND em.employee_code =".db_escape($emp_code);
        }
        
        if($department_id != '-1')
        {
             $sql .= " AND ed.department_id =".db_escape($department_id);
        }
        
        $sql .= " AND em.resigned =0 GROUP BY ed.employee_code ORDER BY ed.employee_code ";
        
   	return $result = db_query($sql, "Employee details could not be retrieved");
}

function get_emp_leave_details($employee_code,$from_date,$to_date)
{
        $sql= "SELECT le.id,le.employee_code,le.leave_type_id,le.from_date,le.to_date,"
                . "le.leave_entitlement,le.leave_payroll_deducted, lv.value_name leave_type_name,lv.value_code "
                . "FROM ".TB_PREF."leave_entitlements le, ".TB_PREF."list_values lv "
                . "WHERE le.leave_type_id = lv.id "
                . "AND le.closed = 0 "
                . "AND le.employee_code = ".db_escape($employee_code)
                . "AND le.from_date < ".db_escape($to_date)." AND le.to_date > ".db_escape($from_date)
                . "ORDER By lv.value_name, le.from_date";
        
   	return $result = db_query($sql, "Employee leave details could not be retrieved");
}

function get_leave_taken($employee_code,$from_date,$to_date,$leave_entitlement_id)
{
    $sql="SELECT l.leave_entitlement_id,sum(l.leave_days) sum_leave_days,SUM(l.nopay_days) sum_nopay_days "
            . " FROM ".TB_PREF."leaves l, ".TB_PREF."leave_entitlements le "
            . " WHERE l.leave_entitlement_id = le.id "
            . " AND l.leave_entitlement_id = ".db_escape($leave_entitlement_id)
            . " AND le.employee_code = ".db_escape($employee_code)
            . " AND l.from_date < ".db_escape($to_date)." AND l.to_date > ".db_escape($from_date)  
            . " ORDER BY `l`.`id` ASC";
    
    return $result = db_query($sql, "Employee leave takens could not be retrieved");
}

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 print_leave_inquiry()
{
    global $path_to_root, $pic_height;
        
        $from = $_POST['PARAM_0'];
	$to = $_POST['PARAM_1'];
        $employee_code = $_POST['PARAM_2'];
//        $department = $_POST['PARAM_3'];
//        $summary_mode = $_POST['PARAM_4'];
        $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 = 'L';
       

        $cols = array(0,100,150,370,500,600,700,800,900,1000,1100);
        $headers = array('Department','Emp No','Name','Leave Type','','Entitlement (Total)','Applied (Total)','Balance (Total)','Nopay (Total)');
        $aligns = array('left','left','left','left','left','left','left','left','left');
        $rep = new FrontReport(_('Leave Summary Report'), "LeaveSummaryReport", 'A4', 9, $orientation);
        
        if($employee_code != "")
        {
            
            $empData = get_employeee_details($employee_code);
            $name = "";
            if($empData){
                    $name = "[".$empData['employee_code']."] - ".$empData['initials'].
                            " ".$empData['last_name']." (".$empData["first_name"].") ";
                                   }
                $employee_name = $name;
            
        }
        else
        {
            $employee_name = "All";
            
        }
        
//        if($department != "-1"){
//            $department_name= get_department_name($department);
//        }else{
            $department_name ="All";
//        }
        
        if($summary_mode = 1)
            $summary_mode_name="Summary Mode";
        else
            $summary_mode_name="Detail Mode";
        
   
        $params =   array( 	0 => $from,
                                1 => $to,
                                2 => $employee_name,
                                3 => $department_name,
                                4 => $summary_mode_name
                    );
        $rep->SetHeaderType('form_979');
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
        $rep->NewPage();
//        $colsqty = count($rep->cols);
        
        $annual_leave_leave_entitlement_sum = $casual_leave_leave_entitlement_sum = $annual_leave_applied_total = $casual_leave_applied_total = 0;
        
         $result_employees =  get_all_current_employees($employee_code,$department=-1, date2sql($to));
        while ($employee_details= db_fetch($result_employees))
	{
                $rep->TextCol(0, 1, $employee_details['department_name'] );
                
                $name = "";
                $name = "[".$employee_details['employee_code']."] - ".$employee_details['initials'].
                            " ".$employee_details['last_name']." (".$employee_details["first_name"].") ";
                
                $rep->TextCol(1, 2, $employee_details['employee_code']);
                $rep->TextCol(2, 3, $name);
                
                $emp_leaves=get_emp_leave_details($employee_details['employee_code'],date2sql($from), date2sql($to));
                                
                $leave_type_name=null;
                $leave_entitlement_id=0;
                $sum_of_leave_entitlement_of_a_leave_type=0;
                $sum_of_leave_entitlements=0;
                $sum_of_applied_total=0;
                $sum_of_no_pay_days=0;
//                $sum_of_balance=0;
                $applied_total=0;
                $nopay_total=0;
                
                if(!$summary_mode){
                $annual_leave='';
                $annual_leave_leave_entitlement_sum=0;
                $annual_leave_applied_total=0;
                $annual_leave_nopay_total=0;

                $casual_leave='';
                $casual_leave_leave_entitlement_sum=0;
                $casual_leave_applied_total=0;
                $casual_leave_nopay_total=0;

                $special_approved_leave='';
                $special_approved_leave_leave_entitlement_sum=0;
                $special_approved_leave_applied_total=0;
                $special_approved_leave_nopay_total=0;

                $uninformed_leave='';
                $uninformed_leave_leave_entitlement_sum=0;
                $uninformed_leave_applied_total=0;
                $uninformed_leave_nopay_total=0;
                }


                while ($emp_leaves_details= db_fetch($emp_leaves))
                {
                    $leave_entitlement_id=$emp_leaves_details['id'];
                    $emp_leaves_taken=get_leave_taken($employee_details['employee_code'],date2sql($from), date2sql($to),$leave_entitlement_id);
                    
                    while ($emp_leaves_taken_details= db_fetch($emp_leaves_taken))
                    {
                        $applied_total += $emp_leaves_taken_details['sum_leave_days'];
                        $nopay_total += $emp_leaves_taken_details['sum_nopay_days'];
                        $sum_of_applied_total += $emp_leaves_taken_details['sum_leave_days'];
                        $sum_of_no_pay_days += $emp_leaves_taken_details['sum_nopay_days'];
                    }   
                    $sum_of_leave_entitlement_of_a_leave_type += $emp_leaves_details['leave_entitlement'];
                    $sum_of_leave_entitlements += $emp_leaves_details['leave_entitlement'];
                    
                    if(!$summary_mode){                    
                        switch ($emp_leaves_details['value_code']) {
                          case "Annual":
                            $annual_leave='Annual Leave';
                            $annual_leave_leave_entitlement_sum += $sum_of_leave_entitlement_of_a_leave_type;
                            $annual_leave_applied_total += $applied_total;
                            $annual_leave_nopay_total += $nopay_total;
                            break;
                          case "Casual":
                            $casual_leave='Casual Leave';
                            $casual_leave_leave_entitlement_sum += $sum_of_leave_entitlement_of_a_leave_type;
                            $casual_leave_applied_total += $applied_total;
                            $casual_leave_nopay_total += $nopay_total;
                            break;
                          case "SPECIAL_APPROVED_LEA":
                            $special_approved_leave='Special Approved Leave';
                            $special_approved_leave_leave_entitlement_sum += $sum_of_leave_entitlement_of_a_leave_type;
                            $special_approved_leave_applied_total += $applied_total;
                            $special_approved_leave_nopay_total += $nopay_total;
                            break;
                          case "Uninformed":
                            $uninformed_leave='Uninformed Leave';
                            $uninformed_leave_leave_entitlement_sum += $sum_of_leave_entitlement_of_a_leave_type;
                            $uninformed_leave_applied_total += $applied_total;
                            $uninformed_leave_nopay_total += $nopay_total;
                            break;
                        }
                    }
                    
                    $sum_of_leave_entitlement_of_a_leave_type=0;
                    $applied_total=0;
                    $nopay_total=0;                    
                }
                
                if(!$summary_mode){
                        //Anual
                
                        $rep->TextCol(0, 1, '' );
                        $rep->TextCol(1, 2, '' );
                        $rep->TextCol(2, 3, '' );
                        $rep->TextCol(3, 4, $annual_leave );
                        $rep->TextCol(4, 5, '' );
                        $rep->TextCol(5, 6, $annual_leave_leave_entitlement_sum );
                        $rep->TextCol(6, 7, $annual_leave_applied_total );
                        $rep->TextCol(7, 8, $annual_leave_leave_entitlement_sum-$annual_leave_applied_total);
                        $rep->TextCol(8, 9, $annual_leave_nopay_total );
                        $rep->NewLine();
                        
                        //Casual
                        
                        $rep->TextCol(0, 1, '' );
                        $rep->TextCol(1, 2, '' );
                        $rep->TextCol(2, 3, '' );
                        $rep->TextCol(3, 4, $casual_leave );
                        $rep->TextCol(4, 5, '' );
                        $rep->TextCol(5, 6, $casual_leave_leave_entitlement_sum );
                        $rep->TextCol(6, 7, $casual_leave_applied_total );
                        $rep->TextCol(7, 8, $casual_leave_leave_entitlement_sum-$casual_leave_applied_total);
                        $rep->TextCol(8, 9, $casual_leave_nopay_total );
                        $rep->NewLine();
                        
                        //Special
                        
                        $rep->TextCol(0, 1, '' );
                        $rep->TextCol(1, 2, '' );
                        $rep->TextCol(2, 3, '' );
                        $rep->TextCol(3, 4, $special_approved_leave );
                        $rep->TextCol(4, 5, '' );
                        $rep->TextCol(5, 6, $special_approved_leave_leave_entitlement_sum );
                        $rep->TextCol(6, 7, $special_approved_leave_applied_total );
                        $rep->TextCol(7, 8, $special_approved_leave_leave_entitlement_sum-$special_approved_leave_applied_total);
                        $rep->TextCol(8, 9, $special_approved_leave_nopay_total );
                        $rep->NewLine();
                        
                        //uninformed
                        
                        $rep->TextCol(0, 1, '' );
                        $rep->TextCol(1, 2, '' );
                        $rep->TextCol(2, 3, '' );
                        $rep->TextCol(3, 4, $uninformed_leave );
                        $rep->TextCol(4, 5, '' );
                        $rep->TextCol(5, 6, $uninformed_leave_leave_entitlement_sum );
                        $rep->TextCol(6, 7, $uninformed_leave_applied_total );
                        $rep->TextCol(7, 8, $uninformed_leave_leave_entitlement_sum-$uninformed_leave_applied_total);
                        $rep->TextCol(8, 9, $uninformed_leave_nopay_total );
                        $rep->NewLine();
                }
                
                
                        //Total
                        if(!$summary_mode){
                            $rep->NewLine();
                        }
                        
                        $rep->TextCol(2, 3, $name);
                        $rep->TextCol(3, 4, 'Total' );
                        $rep->TextCol(5, 6, $sum_of_leave_entitlements );
                                
                        $rep->TextCol(6, 7, $sum_of_applied_total );
                        $rep->TextCol(7, 8, ($annual_leave_leave_entitlement_sum+$casual_leave_leave_entitlement_sum)-($annual_leave_applied_total+$casual_leave_applied_total));
                        $rep->TextCol(8, 9, $sum_of_no_pay_days );
                        $rep->NewLine(2);
                       
                }
            
       
                 $rep->End();       
	}
        



