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

function get_loan_details_for_report($employee,$loan_completed)
{
	 $sql = "SELECT 
                        l.*,
                        l.loan_amount,
                        l.installment_amount,
                        l.start_date,
                        l.completed,
                        vli.total_paid total_paid,
                        em.initials initials,
                        em.last_name last_name,
                        em.first_name first_name,
                        IFNULL(dp.value_code,'No Department Entered') department_code,
                        dn.value_name department,
                        edl.designation 
                  FROM ".TB_PREF."loans l
                        INNER JOIN ".TB_PREF."employee_master em ON 
                            l.employee_code=em.employee_code 
                        LEFT JOIN ".TB_PREF."employee_departments ed ON 
                            l.employee_code = ed.employee_code  AND 
                            ed.start_date <= l.start_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 
                            l.employee_code = edl.employee_code  AND 
                            edl.contract_start_date <= l.start_date  
                       LEFT JOIN ".TB_PREF."vw_loan_installments vli ON   
                            l.id = vli.loan_id 
                         WHERE l.start_date <= ". db_escape(date("Y-m-d"));
                
         if($employee != "")
         {
            $sql .= " AND l.employee_code = ".db_escape($employee);
         }
        if($loan_completed)
         {
            $sql .= " AND l.completed = ".db_escape($loan_completed);            
         } 
       
            $sql .= " ORDER BY department,l.employee_code ";
         
         
        return  $result= db_query($sql,"Salary advance information 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 get_payroll_run_information($payroll_run){
    
    	$sql = "SELECT * 
                FROM ".TB_PREF."payroll_runs  
                WHERE id = ". db_escape($payroll_run);
        
   	return $result = db_query($sql, "Payroll run details could not be retrieved");
    
    
}



function subtotal($rep,$loan_sub_total,$total_paid_sub)
{
        $rep->NewLine(); 
        $rep->Font('bold');
        $rep->TextCol(2,3, 'Sub Total');
        $rep->TextCol(4,5, number_format("$loan_sub_total",2,".",","));
        $rep->TextCol(6,7, number_format("$total_paid_sub",2,".",","));
        $rep->Font(''); 
        $rep->NewLine();  
}

function display_department_sub_heading($rep, $loan_details)
{
    $rep->NewLine(); 
    $rep->Font('bold'); 
    $rep->TextCol(0, 1, $loan_details['department_code']." - ".$loan_details['department'] );
    $rep->Font(''); 
    $rep->NewLine(); 
}

function print_loan()
{
    global $path_to_root, $pic_height;
        
        $employee = $_POST['PARAM_0'];
        $loan_completed= $_POST['PARAM_1'];
        $destination = $_POST['PARAM_2'];
        
	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, 340,425,480,550,670,770);
        $headers = array('Emp Code - Employee','Designation','Loan Start Date','On Hold','Loan Amount','Installment Amount','Total Amount Paid');
        $aligns = array('left',	'left','left','left','right','right','right');
        $rep = new FrontReport(_('Loan'), "Loan", '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($loan_completed != 0)
        {
                  
             $loan_status ="Yes";
        
        }
        else
        {
           $loan_status ="No";
        } 
        
              
        $params =   array( 	0 => $employeeName,
                                1 => $loan_status
                                
                    );
        
        $rep->SetHeaderType('form_963');
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
        $rep->NewPage();
                      
        $loan_sub_total = 0;
        $loan_grand_total = 0;
        $total_paid_sub=0;
        $total_paid_grand=0;
        $current_department ="";
                
             $result_loan_details = get_loan_details_for_report($employee,$loan_completed);
         
             while ($loan_details = db_fetch($result_loan_details))
            {
                    
              if($current_department != $loan_details['department_code'])
                {
                    
                if($current_department !='')  
                {
                        
                subtotal($rep,$loan_sub_total,$total_paid_sub);
                $loan_grand_total += $loan_sub_total;
                $total_paid_grand += $total_paid_sub;
                
                $loan_sub_total = 0 ;
                $total_paid_sub=0;
                }
                
                display_department_sub_heading($rep, $loan_details);
                $current_department = $loan_details['department_code'];
                }
                
                $loan_sub_total += $loan_details['loan_amount'];   
                $total_paid_sub += $loan_details['total_paid'];
                $name = "";
                    $name = "[".$loan_details['employee_code']."] - ".$loan_details['initials'].
                            " ".$loan_details['last_name']." (".$loan_details["first_name"].") ";
                                   
                        $current_employee = $name;

                
                             
                        $rep->NewLine(); 
            //                        $rep->Font("italic");
                                                
                        $rep->TextCol(0, 1, $current_employee );
                        $rep->TextCol(1, 2, $loan_details['designation'] != null ? substr($loan_details['designation'],0,42) : '');
                        $rep->TextCol(2, 3, sql2date($loan_details['start_date']));
                        $hold="";
                        $hold=$loan_details['inactive'];
                        if($hold != 0)
                        {
                           $onhold="Yes";
                        }
                        else
                        {
                            $onhold="No";
                            
                        }
                        
                        $rep->TextCol(3, 4, $onhold);
                        $rep->TextCol(4, 5, number_format($loan_details['loan_amount'],2,".",","));
                        $rep->TextCol(5, 6, number_format($loan_details['installment_amount'],2,".",","));
                        $rep->TextCol(6, 7, $loan_details['total_paid'] != null ? number_format($loan_details['total_paid'],2,".",",") : '');                                         
                                       
                        $rep->NewLine();    
                      //  $special_allowance_grand_total += $special_allowance_sub_total;
            }
        $loan_grand_total += $loan_sub_total;  
        $total_paid_grand += $total_paid_sub;
        
        subtotal($rep,$loan_sub_total,$total_paid_sub);
        $rep->NewLine(); 
        $rep->fontSize += 1; 
        $rep->Font('bold'); 
        $rep->TextCol(2,3, 'Grand Total');
        $rep->TextCol(4,5, number_format($loan_grand_total,2,".",",")); 
        $rep->TextCol(6,7, number_format($total_paid_grand,2,".",","));
        $rep->fontSize += 1; 
       

$rep->End();  
}




