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


define('EXPORT_MAX_INSERT', 50000);


function db_epf_file($epf_file_path, $payroll_run, $info)
{
	 

	return salary_epf_data_export($epf_file_path, $payroll_run, $info);
}

// generates a dump of $db database
// $drop and $zip tell if to include the drop table statement or dry to pack
function salary_epf_data_export($epf_file_path, $payroll_run, $info)
{
    $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");
    }


    $company_total_contribution = 0;
    
    $result = get_payroll_employees($payroll_run['id']);
//    $result = get_banking_salary_data($payroll_run_id, $basic_salary_component);
    $line_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($info['released_salaries'] == 1){
                        // Include released salaries
                        // Allow generating the detail record
                    }
                    else{
                        // Do not include released salaries
                        continue;
                    }
                }
            }
            else{
                if($info['valid_salaries'] == 1){
                    // Include valid salaries
                    // Allow generating the detail record
                }
                else{
                    // Do not include released salaries
                    continue;
                }
            }
            
            $employee = get_epf_salary_data($emp['employee_code']);
      
            $epf_salary = get_payroll_employee_salary_component_value($payroll_run['id'], $employee['employee_code'], $info['epf_salary_component']);

            $i++;
            $employee_total_contribution = 0;

            $line = build_line_record($employee_total_contribution, $payroll_run, $info, $employee, $epf_salary);

            $company_total_contribution += $employee_total_contribution;
    
            if($line_text == ''){
                $line_text .= $line;
            }
            else{
                $line_text .= "\r\n".$line;  
            }
//            $text .= $account_no_total."\r\n";  
        
//        if($i>2){
//            break;
//        }
    }
//    return;
            
    $header_text = build_header_record($info, $i, $company_total_contribution);
    
    
//    return;
    
//    $end_line = set_string_length('000', 80, $field_separator);
//    
//    $text .= $end_line;

    $contribtion_file_name = "EVEMC.TXT";
    $contribtion_file_name = clean_file_name($contribtion_file_name); 
    $cf = save_to_file($epf_file_path, $contribtion_file_name, $zip, $line_text);
    if($cf !== true){
        return array("success" => false, "message" => "Could not generate contribution file.");
    }
    return array("success" => true, "message" => "EPF file has been generated.");
//    
//    $payment_file_name = "EVEMP.TXT";
//    $payment_file_name = clean_file_name($payment_file_name); 
//    $pf = save_to_file($epf_file_path, $payment_file_name, $zip, $header_text);
//    if($pf !== true){
//        return array("success" => false, "message" => "Could not generate contribution file.");
//    }
//    else{
//        return array("success" => true, "message" => "EPF files have been generated.");
//    }
}


function build_line_record(&$TotCont, $payroll_run, $info, $employee, $epf_salary)
{
    $field_separator = $info['field_separator'];
    
    $NICNumber  = $employee['nic'];
    $NICNumber = set_string_length($NICNumber, 20, $field_separator);
    
    $LastName   = $employee['last_name'];
    $LastName = set_string_length($LastName, 40, $field_separator);

    $Initials  = $employee['initials'];
    $Initials = set_string_length($Initials, 20, $field_separator);
    
    $MemNumber   = $employee['epf_no'];
    $MemNumber = set_string_length($MemNumber, 6, $field_separator, true, '0');
    
    $TotCont  = number_format($epf_salary/100*20,2,".","");
    $TotCont = set_string_length($TotCont, 10, $field_separator, true, '0');
    
    $EmpCont   = number_format($epf_salary/100*12,2,".","");
    $EmpCont = set_string_length($EmpCont, 10, $field_separator, true, '0');
    
    $MemCont  = number_format($epf_salary/100*8,2,".","");
    $MemCont = set_string_length($MemCont, 10, $field_separator, true, '0');
    
    $TotEarning  = number_format($epf_salary,2,".","");
    $TotEarning = set_string_length($TotEarning, 12, $field_separator, true, '0');
    
    $hpepf = has_previous_epf_salaries($payroll_run['id'], $employee['employee_code'], $info['epf_salary_component']);
    if($hpepf){
        $MemStatus   = "E";
    }
    else{
        $MemStatus   = "N";
    }
    $MemStatus = set_string_length($MemStatus, 1, $field_separator);
    
    $ZnCode  = $info['zone_code'];
    $ZnCode = set_string_length($ZnCode, 1, $field_separator, true, '0');
    
    $EmpNo  = $info['employer_number'];
    $EmpNo = set_string_length($EmpNo, 6, $field_separator, true, '0');
    
    $ContPeriod   = $info['year_month'];
    $ContPeriod = set_string_length($ContPeriod, 6, $field_separator);
    
    $SubmisionID  = $info['submission_number'];
    $SubmisionID = set_string_length($SubmisionID, 2, $field_separator, true, '0');
    
    $leave_days = get_leaves_from_to($employee['employee_code'], $payroll_run['period_start_date'], $payroll_run['period_end_date']);
    $DaysWork   = $info['no_of_working_days'] - $leave_days;
    if($DaysWork < 0){
        $DaysWork = 0;
    }
    $DaysWork  = number_format($DaysWork,2);
    $DaysWork = set_string_length($DaysWork, 5, $field_separator);
    
    $OcGrade = get_employee_ocg($employee['employee_code'], $payroll_run['period_start_date'], $payroll_run['period_end_date']);
    $OcGrade  = number_format($OcGrade,0);
    $OcGrade = set_string_length($OcGrade, 3, $field_separator, true, '0');
    
    $line_record_string = $NICNumber.
            $LastName.
            $Initials.
            $MemNumber.
            $TotCont.
            $EmpCont.
            $MemCont.
            $TotEarning.
            $MemStatus.
            $ZnCode.
            $EmpNo.            
            $ContPeriod.
            $SubmisionID.
            $DaysWork.
            $OcGrade;
    
    return $line_record_string; 
}


function build_header_record($info, $member_count, $company_total_contribution)
{
    $field_separator = $info['field_separator'];
    
    $ZnCode  = $info['zone_code'];
    $ZnCode = set_string_length($ZnCode, 1, $field_separator);
    
    $EmpNo  = $info['employer_number'];
    $EmpNo = set_string_length($EmpNo, 6, $field_separator, true, "0");

    $ContPeriod   = $info['year_month'];
    $ContPeriod = set_string_length($ContPeriod, 6, $field_separator, true, "0");
    
    $SubmisionID  = $info['submission_number'];
    $SubmisionID = set_string_length($SubmisionID, 2, $field_separator, true, "0");
    
    $TotalCont   = number_format($company_total_contribution,2,".","");
    $TotalCont = set_string_length($TotalCont, 14, $field_separator, true, "0");
    
    $MemCount  = $member_count;
    $MemCount = set_string_length($MemCount, 5, $field_separator, true, "0");
    
    $PayMode  = $info['payment_mode'];
    $PayMode = set_string_length($PayMode, 1, $field_separator, true, "0");
    
    $PayRef   = $info['payment_reference'];
    $PayRef = set_string_length($PayRef, 20, $field_separator, true, "0");
    
    $Paydate  = $info['payment_date'];
    $Paydate = set_string_length($Paydate, 10, $field_separator, true, "0");
    
    $DOCode   = $info['do_code'];
    $DOCode = set_string_length($DOCode, 2, $field_separator);
    
    $header_record_string = $ZnCode.
                            $EmpNo.
                            $ContPeriod.
                            $SubmisionID.
                            $TotalCont.
                            $MemCount.
                            $PayMode.
                            $PayRef.
                            $Paydate.
                            $DOCode;
    
    return $header_record_string;
}

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

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

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

function get_epf_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);
}

function has_previous_epf_salaries($payroll_run_id, $employee_code, $component_code)
{
    $sql = "SELECT value 
            FROM ".TB_PREF."payroll_run_details 
            WHERE payroll_run_id < ".db_escape($payroll_run_id)." AND 
                     employee_code = ".  db_escape($employee_code)." AND 
                     component = ".db_escape($component_code)." 
                    LIMIT 1"; 
                         
   	$result = db_query($sql, "Could not get has_previous_epf_salaries");

    $v = db_fetch($result);
    if(isset($v['value']) && $v['value'] >= 0){
        return true;
    }
    else{
        return false;
    }
}


function get_employee_ocg($employee,$from_date,$to_date)
{
    
        $sql = "SELECT IFNULL(ocg.amount1,'00') oc_grade 
            FROM ".TB_PREF."employment_details edl 
                    LEFT JOIN ".TB_PREF."list_values ocg ON 
                            edl.job_category_id = ocg.id 
            WHERE edl.contract_start_date <= ".db_escape($to_date)." AND 
                    edl.contract_end_date >= ".db_escape($from_date)." AND 
                    edl.employee_code =". db_escape($employee)."
                    ORDER BY edl.contract_end_date DESC LIMIT 1";
                
        
        $result=db_query($sql,"Could not get_employee_ocg");
        $ocg = db_fetch($result);
        
        if(isset($ocg['oc_grade']) && $ocg['oc_grade'] >= 0){
            return $ocg['oc_grade'];
        }
        else{
            return 0;
        }
    
}

?>