<?php
/**********************************************************************
***********************************************************************/


define('EXPORT_MAX_INSERT', 50000);


function db_bank_file($bank_file_path, $payroll_run_id, $basic_salary_component, $incentive_salary_component, 
                                    $main_bank_tran_date, $other_bank_tran_Date, $account_info,
                                    $valid_salaries, $released_salaries, $line_reference, $field_separator)
{
	$filename = "hnb.txt";
    $filename = clean_file_name($filename);
    
    $main_bank_tran_date = date2sql($main_bank_tran_date);
    $main_bank_tran_date = date("Ymd", strtotime($main_bank_tran_date));
    
    $other_bank_tran_Date = date2sql($other_bank_tran_Date);
    $other_bank_tran_Date = date("Ymd", strtotime($other_bank_tran_Date));
    

	return salary_bank_data_export($bank_file_path, $filename, $payroll_run_id, $basic_salary_component, $incentive_salary_component, 
                                    $main_bank_tran_date, $other_bank_tran_Date, $account_info,
                                    $valid_salaries, $released_salaries, $line_reference, $field_separator);
}

// generates a dump of $db database
// $drop and $zip tell if to include the drop table statement or dry to pack
function salary_bank_data_export($bank_file_path, $filename, $payroll_run_id, $basic_salary_component, $incentive_salary_component, 
                                    $main_bank_tran_date, $other_bank_tran_Date, $account_info, 
                                    $valid_salaries, $released_salaries, $line_reference, $field_separator)
{
    $zip='no';
    $error = false;
    // set max string size before writing to file
    $max_size = 1048576 * 2; // 2 MB
    // changes max size if value can be retrieved
    if (ini_get("memory_limit")){
    	$max_size = 900000 * ini_get("memory_limit");
    }


    $final_full_total = 0;
    $account_no_total = 0;
    $tran_count = 0;
    
    $result = get_payroll_employees($payroll_run_id);
//    $result = get_banking_salary_data($payroll_run_id, $basic_salary_component);
    $text = '';
    $i = 0;
    while ($emp = mysql_fetch_array($result)) {
        
        //if($i==26)
        $salary_hold_details = get_salary_hold_details($payroll_run_id, $emp['employee_code']);
            if($salary_hold_details){
                // There is a salary hold record
                if($salary_hold_details['hold_salary'] == 1){
                    //This salary is held
                    //Do not send held salaries to the bank
                    continue;
                }
                else{
                    // Held salary is released
                    if($released_salaries == 1){
                        // Include released salaries
                        // Allow generating the detail record
                    }
                    else{
                        // Do not include released salaries
                        continue;
                    }
                }
            }
            else{
                if($valid_salaries == 1){
                    // Include valid salaries
                    // Allow generating the detail record
                }
                else{
                    // Do not include released salaries
                    continue;
                }
            }
            
            $employee = get_banking_salary_data($emp['employee_code']);
            $banking_basic_salary = $banking_incentive_salary = 0;
            
            if(!($employee['bank_basic_salary'] || $employee['bank_incentive_salary'])){
                // If employee does not want to bank any of the salaries do not proceed
                continue;
            }
            
            if(!$employee['include_in_bank_file']){
                // If banking total salary is not required to include in the bank upload file due to any reason 
                // (may be manually sending the instructions due to some reason) do not include ths salary in bank upload file.
                continue;
            }
            
            if($employee['bank_basic_salary']){
                if(strlen($basic_salary_component) > 2){
                    $banking_basic_salary = get_payroll_employee_salary_component_value($payroll_run_id, $employee['employee_code'], $basic_salary_component);
                }
                else{
                    if(!$employee['bank_incentive_salary']){
                        // Basic salary component not selected
                        // Employee does not want to bank incentive slary
                        // so the banking total slary is zero
                        continue;
                    }
                    $banking_basic_salary = 0;
                }
            }
            if($employee['bank_incentive_salary']){
                if(strlen($incentive_salary_component) > 2){
                    $banking_incentive_salary = get_payroll_employee_salary_component_value($payroll_run_id, $employee['employee_code'], $incentive_salary_component);
                }
                else{
                    if(!$employee['bank_basic_salary']){
                        // Incentive salary component not selected
                        // Employee does not want to bank basic slary
                        // so the banking total slary is zero
                        continue;
                    }
                    $banking_incentive_salary = 0;
                }
            }

            $banking_full_salary = $banking_basic_salary + $banking_incentive_salary;

            if(!($banking_full_salary > 0)){
                continue;
            }
            
            $i++;
            $final_bancking_salary = 0;
            $account_no = 0;

            $line = build_line_record($final_bancking_salary, $account_no, $i, $employee, $main_bank_tran_date, 
                                        $other_bank_tran_Date, $banking_full_salary, $line_reference, $field_separator);

            $final_full_total += $final_bancking_salary;
            $account_no_total += $account_no;
    
            $text .= $line."\r\n";  
//            $text .= $account_no_total."\r\n";  

            
        
        
//        if($i>2){
//            break;
//        }
    }
    
    $tran_count = $i;
            
    $header = build_header_record($tran_count, $final_full_total, $account_no_total, $main_bank_tran_date, $account_info, $field_separator);
    
    $text = $header."\r\n".$text;
    
    $end_line = set_string_length('000', 80, $field_separator);
    
    $text .= $end_line;

	if (save_to_file($bank_file_path, $filename, $zip, $text))
	{
		$text = "";
	}
	else
	{
		@unlink(BACKUP_PATH . $filename);
		return false;
	}
    return $filename;
}


function build_line_record(&$final_bancking_salary, &$account_no, $counter, $employee, $main_bank_tran_date, 
                            $other_bank_tran_Date, $banking_full_salary, $line_reference, $field_separator)
{
    $Tran_Ref = set_string_length($employee['epf_no'], 8, $field_separator);
    
    $Destination_Account_Name = $employee['bank_account_name'];
    $Destination_Account_Name = set_string_length($Destination_Account_Name, 20, $field_separator, ' ');
    
    $Destination_Bank = $employee['bank_code'];
    $Destination_Bank = set_string_length($Destination_Bank, 4, $field_separator);
    
    $Destination_Branch = $employee['branch_code'];
    $Destination_Branch = set_string_length($Destination_Branch,3, $field_separator);
    
    $Destination_Account  = $employee['bank_account_no'];
    $account_no = substr($Destination_Account,-12);
    $Destination_Account = set_string_length($Destination_Account, 12, $field_separator);
    
    $TRN_Code = "023";
    $TRN_Code = set_string_length($TRN_Code, 3, $field_separator);
    
    $Amount  = $banking_full_salary *100;
    $final_bancking_salary = $Amount;
    $Amount = set_string_length($Amount, 11, $field_separator);    
    
    if($employee['bank_code'] == '7083'){ //HNB Bank code
        $Value_Date  = $main_bank_tran_date;
    }
    else{
        $Value_Date  = $other_bank_tran_Date;
    }
    $Value_Date = set_string_length(substr($Value_Date,2,6), 6, $field_separator);
    
//    $Particulars = 'c'.$row['employee_code'].'e'.$row['epf_no'];
    $Particulars = set_string_length($line_reference, 13, $field_separator, ' ');
    
    $line_record_string = $Tran_Ref.
            $Destination_Account_Name.
            $Destination_Bank.
            $Destination_Branch.
            $Destination_Account.
            $TRN_Code.
            $Amount.
            $Value_Date.
            $Particulars;
    
    return $line_record_string; 
}


function build_header_record($tran_count, $final_full_total, $account_no_total, $main_bank_tran_date, $account_info, $field_separator)
{
    $Debit_Account_Name  = $account_info['account_name'];
    $Debit_Account_Name = set_string_length($Debit_Account_Name, 20, $field_separator, ' ');
    
    $Total_Debit_Amount   = $final_full_total;
    $Total_Debit_Amount = set_string_length($Total_Debit_Amount, 11, $field_separator);

    $Debit_Account_Number  = $account_info['account_number'];
    $Debit_Account_Number = set_string_length($Debit_Account_Number, 12, $field_separator);
    
    $Value_Date   = $main_bank_tran_date;
    $Value_Date = set_string_length($Value_Date, 6, $field_separator);
    
    $Hash_Total  = $account_no_total;
    $Hash_Total = set_string_length($Hash_Total, 14, $field_separator);
    
    $Total_Number_of_Transactions   = $tran_count;
    $Total_Number_of_Transactions = set_string_length($Total_Number_of_Transactions, 5, $field_separator);
    
    $Bank_Code  = $account_info['bank_code'];
    $Bank_Code = set_string_length($Bank_Code, 4, $field_separator);
    
    $Branch_Code  = $account_info['branch_code'];
    $Branch_Code = set_string_length($Branch_Code, 3, $field_separator);
    
    $TRN_Code   = $account_info['tran_code'];
    $TRN_Code = set_string_length($TRN_Code, 3, $field_separator);
    
    $header_record_string = 
                            $Debit_Account_Name.
                            $Total_Debit_Amount.
                            $Debit_Account_Number.
                            $Value_Date.
                            $Hash_Total.
                            $Total_Number_of_Transactions.
                            $Bank_Code.
                            $Branch_Code.
                            $TRN_Code;
    
    return $header_record_string;
}

function set_string_length($string, $length, $field_separator, $filling_char = '0')
{
    $string = html_entity_decode($string,ENT_QUOTES);
    
    $padding_string = str_repeat("$filling_char",$length);
    if($filling_char == ' '){
        $string = $string.$padding_string;
        $string = substr($string, 0, $length);
    }
    else{
        $string = $padding_string.$string;
        $string = substr($string, -$length);
    }
    
    if($field_separator){
        $string .= "|";
    }
    return $string;
    
}

function save_to_file($bank_file_path, $backupfile, $zip, $fileData)
{
	global $path_to_root;


        @unlink(BACKUP_PATH . $filename);
    
        if ($zp = @fopen($bank_file_path . $backupfile, "w"))
        {
			@fwrite($zp, $fileData);
			@fclose($zp);
			return true;
        }
        else
        {
        	return false;
        }
    
}

function get_banking_salary_data($employee_code)
{
    $sql = 'SELECT em.*, 
                    bn.value_code as bank_code,
                    br.branch_code 
            FROM '.TB_PREF.'employee_master em,
                    '.TB_PREF.'list_values bn,  
                    '.TB_PREF.'bank_branches br 
            WHERE em.bank_id = bn.id AND 
                    em.bank_branch_id = br.id AND 
                    em.employee_code = '.db_escape($employee_code);
    
    $result = db_query($sql, "Could not retrieve banking salary data");
    
    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);
}

?>