<?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 . "/inventory/includes/inventory_db.inc");
include_once($path_to_root . "/includes/db/manufacturing_db.inc");
include_once($path_to_root . "/includes/db/module_settings_db.inc");
include_once($path_to_root . "/payroll/includes/db/payroll_run_db.inc");
include_once($path_to_root . "/admin/db/list_values_db.inc");
//----------------------------------------------------------------------------------------------------

print_bank_instruction_letters();

function get_salary_payment_bank_accounts()
{
	$sql = " SELECT DISTINCT salary_payment_account_id  
                 FROM ".TB_PREF."employee_master  
                 WHERE inactive = 0 AND salary_payment_account_id > 0 
                 ORDER BY employee_code";
        
	return db_query($sql, "Could not retreive salary payment bank accounts");
}


function get_salary_payment_bank_account_details($id)
{
        $sql = "SELECT * 
                FROM ".TB_PREF."bank_accounts  
                WHERE id = ".  db_escape($id);
        
   	$result = db_query($sql, "Salary payment bank account details could not be retrieved");

        return db_fetch($result);
}

function get_salary_account_employees($payroll_run_id, $group_salary_id)
{
//	$sql = " SELECT em.*, lv.value_name bank_name, bb.branch_name   
//                    FROM ".TB_PREF."employee_master em, 
//                         ".TB_PREF."list_values lv,
//                         ".TB_PREF."list_values dn,
//                         ".TB_PREF."bank_branches bb, 
//                         ".TB_PREF."employee_departments ed  
//        
//                    WHERE 
//                            em.bank_id = lv.id AND   
//                            ed.employee_code = em.employee_code AND 
//                            ed.department_id = dn.id AND 
//                            ed.start_date <= ".db_escape($payroll_end_date)." AND ed.end_date >= ".db_escape($payroll_end_date)."   
//	
//                    ORDER BY dn.value_code, ed.employee_code";
    
   $sql = " SELECT em.*, 
                    bn.value_code bank_code, bn.value_name bank_name,
                    bb.branch_name 
            FROM ".TB_PREF."vw_payroll_employees vpe  
                    INNER JOIN ".TB_PREF."employee_master em ON
                            vpe.employee_code = em.employee_code 
                    INNER JOIN ".TB_PREF."list_values bn 
                                ON em.bank_id = bn.id 
                    INNER JOIN ".TB_PREF."bank_branches bb 
                                ON em.bank_branch_id = bb.id 
            WHERE vpe.payroll_run_id = ".db_escape($payroll_run_id);
                
        if($group_salary_id > 0){
            $sql .= " AND em.salary_group_id = ".db_escape($group_salary_id);
        }
//        if($is_insentive_salaries ==0){
//            $sql .= " AND em.bank_basic_salary = ".db_escape($is_insentive_salaries);
//        }
//         if($is_insentive_salaries ==1){
//            $sql .= " AND em.bank_incentive_salary = ".db_escape($is_insentive_salaries);
//        }
        
        $sql .= " ORDER BY  em.employee_code,bn.value_name, bb.branch_name ";
        

        
	return db_query($sql, "Could not retreive salary account employess");
}

function get_employee_salary_component_amount($payroll_run_id, $employee, $component)
{
        $sql = "SELECT * 
                FROM ".TB_PREF."payroll_run_details 
                WHERE payroll_run_id = ".db_escape($payroll_run_id)." AND 
                         employee_code = ".  db_escape($employee)." AND 
                         component = ".db_escape($component); 
                         
   	$result = db_query($sql, "Salary component amount could not be retrieved");

        return db_fetch($result);
}

function get_salary_hold_details($payroll_run_id, $employee)
{
    $sql = "SELECT hs.* 
                FROM ".TB_PREF."hold_salary hs
                WHERE hs.employee_code = ".db_escape($employee)." AND
                        hs.payroll_run_id = ".db_escape($payroll_run_id);
        
   	$result = db_query($sql, "hold salary  details could not be retrieved");

        return db_fetch($result);
}

function write_page_footer($rep)
{
    $rep->Line($rep->row-2,1);

        $rep->NewLine(3);
        $rep->Text($rep->leftMargin + 350,"Thanking You.");
        $rep->NewLine();
        $rep->Text($rep->leftMargin + 350,"Truely,");
        $rep->NewLine(3);
        $rep->Text($rep->leftMargin + 330,"----------------------------------------");
        $rep->NewLine();
        $rep->Text($rep->leftMargin + 350,"Authorized signatory");
        $rep->NewLine();
}
//----------------------------------------------------------------------------------------------------

function print_bank_instruction_letters()
{
    global $path_to_root, $pic_height;
        
        $payroll_run_id = $_POST['PARAM_0'];
        $salary_payment_account_id = $_POST['PARAM_1'];
        $group_salary_id = $_POST['PARAM_2'];
        $salary_total_id = $_POST['PARAM_3'];
        $salary_total_id_insentive = $_POST['PARAM_4'];
        $valid_salaries = $_POST['PARAM_5'];
        $released_salaries = $_POST['PARAM_6'];
        $hold_salaries = $_POST['PARAM_7'];
//        $is_insentive_salaries = $_POST['PARAM_6'];
        
        $department_id = -1;
        
	include_once($path_to_root . "/reporting/includes/pdf_report.inc");

	$orientation = 'P';
        //$component = get_module_parameter_value("payroll", "banking_salary_total");

        $component_basic = $component_insentive = '';
        $salary_total_basic = get_list_value($salary_total_id);
            if (isset($salary_total_basic['id']) && $salary_total_basic['id'] > 0) {
                $component_basic = $salary_total_basic['value_code'];
            }
        
        $salary_total_insentive = get_list_value($salary_total_id_insentive);
            if (isset($salary_total_insentive['id']) && $salary_total_insentive['id'] > 0) {
                $component_insentive = $salary_total_insentive['value_code'];
            }
        
        $cols = array(0,20,55, 220, 400, 475, 525);
        $headers = array('#','Code', 'Account Name','Bank / Branch','Account Number','Amount');
        $aligns = array('center','center', 'left', 'left', 'left', 'right', 'left');

   	$rep = new FrontReport(_('Salary Payment Instructions'), "SalaryPaymentInstructions", 'A4', 9, $orientation);

//        $payroll_run_details = get_payroll_run_details($payroll_run_id);
//            if (isset($payroll_run_details['id']) && $payroll_run_details['id'] > 0) {
//                $payroll_end_date = $payroll_run_details['period_end_date'];
//            }
       
        $salary_payment_bank_account = get_salary_payment_bank_account_details($salary_payment_account_id);
        $sp_bank_account_name = "";
        $sp_bank_account_no = "";
        $sp_bank_name = "";
        $sp_bank_address = "";
        if($salary_payment_bank_account){
            $sp_bank_account_name = $salary_payment_bank_account['bank_account_name'];
            $sp_bank_account_no = $salary_payment_bank_account['bank_account_number'];
            $sp_bank_name = $salary_payment_bank_account['bank_name'];
            $sp_bank_address = $salary_payment_bank_account['bank_address'];
        }

        $params =   array('sp_bank_account_name' => $sp_bank_account_name,
                            'sp_bank_account_no' => $sp_bank_account_no,
                            'sp_bank_name' => $sp_bank_name,
                            'sp_bank_address' => $sp_bank_address);

        //$rep->formData['doctype'] = "form_950";
        $rep->SetHeaderType('form_951');
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
        $rep->NewPage();  

        $seq_no = 1;
        $res = get_salary_account_employees($payroll_run_id,$group_salary_id);
        $final_salary= 0;
        $final_salary_basic = 0;
        
        $final_salary_inse = 0;
        while ($employee = db_fetch($res))
        {
//            write_to_log_file("rep_951", $employee['employee_code']);
            
            if($employee['bank_basic_salary'] == 0 && $employee['bank_incentive_salary'] == 0){
                continue;
            }
                
            $salary_hold_details = get_salary_hold_details($payroll_run_id, $employee['employee_code']);
            if($salary_hold_details){
                if($salary_hold_details['hold_salary'] == 1){
                    if($hold_salaries == 1){

                    }
                    else{
                        continue;
                    }
                }
                else{
                    if($released_salaries == 1){

                    }
                    else{
                        continue;
                    }
                }
            }
            else{
                if($valid_salaries == 1){

                }
                else{
                    continue;
                }
            }
                    
                    
            if($employee['bank_basic_salary'] == 1 && $employee['bank_incentive_salary'] == 0 )
            {
                $salary = get_employee_salary_component_amount($payroll_run_id, $employee['employee_code'], $component_basic);
                $final_salary = $salary['value'];
            }
            if($employee['bank_incentive_salary'] == 1 && $employee['bank_basic_salary'] == 0 )
            {
                $salary= get_employee_salary_component_amount($payroll_run_id, $employee['employee_code'], $component_insentive);
                $final_salary = $salary['value'];
            }
            if($employee['bank_incentive_salary'] == 1 && $employee['bank_basic_salary'] == 1 )
            {
                $salary_basic = get_employee_salary_component_amount($payroll_run_id, $employee['employee_code'], $component_basic);
                $final_salary_basic = $salary_basic['value'];
                
                $salary_insentive = get_employee_salary_component_amount($payroll_run_id, $employee['employee_code'], $component_insentive);
                $final_salary_inse = $salary_insentive['value'];
                
                $final_salary = $final_salary_basic + $final_salary_inse;
            }
                
                    
              

                $rep->NewLine();
                $rep->TextCol(0, 1, $seq_no);
                $rep->TextCol(1, 2, $employee['employee_code']);
                $rep->TextCol(2, 3, $employee['bank_account_name']);
                $bank_name = "";
                if(strlen($employee['bank_name'])>0){
                    $bank_name = $employee['bank_name']." - ".$employee['branch_name'];
                }

                $rep->TextCol(3, 4, $bank_name);
                $rep->TextCol(4, 5, $employee['bank_account_no']);
                $rep->TextCol(5, 6, number_format($final_salary,2));
                $rep->Line($rep->row - 2);

                $seq_no++;

        }
        

        if($rep->row - $rep->bottomMargin < $rep->lineHeight * 10){
            $rep->NewPage();
        }
            
        write_page_footer($rep);
        


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

?>
