<?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_salary_advance();

function get_salary_advance_for_report($from_date, $employee, $salary_advance_type, $status_code)
{
	 $sql = "SELECT 
                        sa.employee_code,
                        em.initials initials,
                        em.last_name last_name,
                        em.first_name first_name,
                        sa.due_date,
                        sa.salary_advance,
                        satn.value_name type_name,
                        st.value_name status_code_name,
                        dn.value_name department_name,
                        IFNULL(dp.value_code,'No Department Entered') department_code,
                        dp.value_name department,
                        edl.designation 
                 FROM ".TB_PREF."salary_advances sa 
                        INNER JOIN ".TB_PREF."employee_master em ON 
                            sa.employee_code=em.employee_code
                        INNER JOIN ".TB_PREF."list_values st ON 
                            sa.status_code=st.value_code AND st.list_code = 'ATTENDANCE_STATUS' 
                        INNER JOIN ".TB_PREF."list_values satn ON 
                            sa.type_id =satn.id 
                        LEFT JOIN ".TB_PREF."employee_departments ed ON 
                            sa.employee_code = ed.employee_code AND 
                            ed.start_date <= '$from_date' AND ed.end_date >= '$from_date' 
                        LEFT JOIN ".TB_PREF."list_values dn ON
                            ed.department_id=dn.id 
                        LEFT JOIN ".TB_PREF."list_values dp ON
                            ed.department_id=dp.id     
                        LEFT JOIN ".TB_PREF."employment_details edl ON 
                            sa.employee_code = edl.employee_code AND 
                            edl.contract_start_date <= '$from_date' AND edl.contract_end_date >= '$from_date'                          
                WHERE  sa.created_on >= '$from_date' ";
                
         if($employee != "")
         {
           $sql .= " AND sa.employee_code = ".db_escape($employee);
         }
        if($salary_advance_type != "-1")
         {
          $sql .= " AND sa.type_id = ".db_escape($salary_advance_type);            
         }  
  
        if($status_code != "-1")
         {
          $sql .= " AND st.id= ".db_escape($status_code);            
         }                   
       
        $sql .= " ORDER BY dn.value_name, sa.employee_code ";
        
       
        
        return  $result= db_query($sql,"Salary advance information could not be retreived");
}

function get_status_data($status_code)  
 {
            $sql = "SELECT  
                ls.value_name status_code_name
                FROM 
                " .TB_PREF."list_values ls Where ls.id=".db_escape($status_code);
            
       return $result = db_query($sql, "Status could not be retrieved");
 }
 
function get_department_employees($date,$employee)
{
	 $sql = "SELECT  ed.*, 
                    IFNULL(d.value_code,'No Department Entered') department_code),
                    d.value_name department_name,
                    em.initials,
                    em.last_name,
                    em.first_name,
                    edl.designation
            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 
                    LEFT JOIN ".TB_PREF."employment_details edl ON 
                            ed.employee_code = edl.employee_code AND 
                            edl.contract_start_date <= ".db_escape($date)." AND 
                            edl.contract_end_date >= ".db_escape($date)." 
                    WHERE ed.start_date <= ".db_escape($date)." AND 
                    ed.end_date >= ".db_escape($date);
         
         if($employee != "")
         {
             $sql .= " AND ed.employee_code =". db_escape($employee);
             
         }
             $sql .= " ORDER BY d.value_name, ed.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 ";
          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 subtotal($rep,$salary_advance_sub_total)
{
        $rep->NewLine(); 
        $rep->Font('bold');
        $rep->TextCol(4,5, 'Sub Total');
        $rep->TextCol(5,6, number_format("$salary_advance_sub_total",2,".",","));
        $rep->Font(''); 
        $rep->NewLine();

        
    
}

function display_department_sub_heading($rep, $salary_advance)
{
    $rep->NewLine(); 
    $rep->Font('bold'); 
    $rep->TextCol(0, 1, $salary_advance['department_code']." - ".$salary_advance['department'] );
    $rep->Font(''); 
    $rep->NewLine(); 
}
function get_salary_advance_type($selected_id){
    	$sql = "SELECT sa.*, lv.value_name salary_run_name     
                FROM ".TB_PREF."salary_advances sa,
                        ".TB_PREF."list_values lv  
                WHERE sa.type_id = lv.id AND 
                        lv.id = ".db_escape($selected_id);
	
	return $result = db_query($sql,"Could not retrieve payroll run details");	
        
          
}

function print_salary_advance()
{
    global $path_to_root, $pic_height;
        
        $employee = $_POST['PARAM_0'];
        $salary_advance_type = $_POST['PARAM_1'];
        $from_date = date2sql($_POST['PARAM_2']);
        $status_code = $_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, 200, 385,505,560,620,700,760);
        $headers = array('Emp Code - Employee','Designation','Department','Date','Type','Advance Amount','Status');
        $aligns = array('left',	'left','left','left','left','right','right');
        $rep = new FrontReport(_('Salary Advance'), "SalaryAdvance", 'A4', 9, $orientation);
        
        if($employee != "")
        {
            
            $employee_detials = get_employeee_details($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"].") ";
             }
             $employeeName = $name;
            
        }
        else
        {
            $employeeName = "All";
            
        }
        
        if($salary_advance_type != "-1")
        {
                  
        $result= get_salary_advance_type($salary_advance_type);
        $StC=db_fetch($result);
        $salary_type_name=$StC['salary_run_name'];
        
        }
        else
        {
           $salary_type_name ="None";
        } 
        
          
       if($status_code != "-1")
        {
            
        $result= get_status_data($status_code);
        $StC=db_fetch($result);
        $st_code=$StC['status_code_name'];
        
        }
        else
        {
           $st_code ="All";
        } 
                
        $params =   array( 	0 => $employeeName,
                                1 => $salary_type_name,
                                2 => sql2date($from_date),
                                3 => $st_code
                    );
        $rep->SetHeaderType('form_961');
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
        $rep->NewPage();
                      
            $salary_advance_sub_total = 0;
            $salary_advance_grand_total = 0; 
            $current_department = "";
             
                
             $result_salary_advance = get_salary_advance_for_report($from_date, $employee, $salary_advance_type, $status_code);
         
             while ($salary_advance = db_fetch($result_salary_advance))
            {
                    
                   
                if($current_department != $salary_advance['department_code'])
                {
                    
                    if($current_department != '')  
                    {

                        subtotal($rep,$salary_advance_sub_total);
        //                    $rep->NewLine(); 
        //                    $rep->Font('bold');
        //                    $rep->TextCol(4,5, 'Sub Total');
        //                    $rep->TextCol(5,6, $salary_advance_sub_total);
        //                    $rep->Font(''); 
        //                    $rep->NewLine();
        //
                        $salary_advance_grand_total += $salary_advance_sub_total;
                        $salary_advance_sub_total = 0 ;
                    }
                    
                    display_department_sub_heading($rep, $salary_advance);
                    $current_department = $salary_advance['department_code'];
                }

                $salary_advance_sub_total += $salary_advance['salary_advance'];
                
                $name = "";
                    $name = "[".$salary_advance['employee_code']."] - ".$salary_advance['initials'].
                            " ".$salary_advance['last_name']." (".$salary_advance["first_name"].") ";
                                   
                $current_employee = $name;

                
                             
                        $rep->NewLine(); 
            //                        $rep->Font("italic");
                                                
                        $rep->TextCol(0, 1, $current_employee );
                        $rep->TextCol(1, 2, $salary_advance['designation'] != null ? substr($salary_advance['designation'] ,0,42) : '');
                        $rep->TextCol(2, 3, $salary_advance['department_name']);
                        $rep->TextCol(3, 4, sql2date($salary_advance['due_date']));                                           
                        $rep->TextCol(4, 5, $salary_advance['type_name']);
                        $rep->TextCol(5, 6, number_format($salary_advance['salary_advance'],2,".",","));
                        $rep->TextCol(6, 7, $salary_advance['status_code_name']);
                                               
                                         
                        $rep->NewLine();    
                        
            }
        $salary_advance_grand_total += $salary_advance_sub_total;
        subtotal($rep,$salary_advance_sub_total);
        
        
        $rep->NewLine(); 
        $rep->Font('bold'); 
        $rep->TextCol(4,5, 'Grand Total');
        $rep->TextCol(5,6, number_format($salary_advance_grand_total,2,".",","));
        
$rep->End();  
}




