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

function insert_payroll_log_message($payroll_run_id,$is_error, $reference_id, $message)
{        
	$message_time = date('Y-m-d H:i:s');
        
        $sql = "INSERT INTO ".TB_PREF."payroll_log (
                    payroll_run_id,is_error,  
                    reference_id, message, message_time) 
                VALUES ("
                    .db_escape($payroll_run_id).", ".db_escape($is_error).", "
                    .db_escape($reference_id). ", ".db_escape($message).", ".db_escape($message_time).")";
        
        db_query($sql,"Payroll log message could not be inserted");


}


function insert_payroll_run_detail($payroll_run_id, $employee_code,
                        $component, $value, $comment = "")
{        
	$sql = "INSERT INTO ".TB_PREF."payroll_run_details (
                    payroll_run_id,employee_code,  
                    component, value, comment) 
                VALUES ("
                    .db_escape($payroll_run_id).", ".db_escape($employee_code).", "
                    .db_escape($component). ", ".db_escape($value).", ".db_escape($comment).")";
        
        db_query($sql,"Payroll run detail could not be inserted");


}

function delete_payroll_run_details($payroll_run_id)
{
        $sql = "DELETE FROM ".TB_PREF."payroll_run_details  
                WHERE payroll_run_id=".db_escape($payroll_run_id);

        db_query($sql,"Payroll run details could not be deleted");
           
}


function delete_payroll_component_summary($payroll_run_id)
{
        $sql = "DELETE FROM ".TB_PREF."payroll_run_details  
                WHERE payroll_run_id=".db_escape($payroll_run_id)." AND 
                        employee_code = ''"; 

        db_query($sql,"Payroll run details could not be deleted");
           
}

function delete_incomplete_payroll_run_details($payroll_run_id, $last_completed_emplyee)
{
        $sql = "DELETE FROM ".TB_PREF."payroll_run_details  
                WHERE payroll_run_id=".db_escape($payroll_run_id)." AND 
                        employee_code > ".db_escape($last_completed_emplyee)." 
                ORDER BY employee_code";

        db_query($sql,"Payroll run details could not be deleted");
           
}

function update_payroll_run_status($payroll_run_id, $status_code, 
                                    $update_start_time = false, $update_end_time = false, 
                                    $update_run_by = false)
{
        $run_by = $_SESSION['wa_current_user']->user;
        $time = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."payroll_runs 
                SET 
                    status=".db_escape($status_code);
        
        if($update_start_time)
            $sql .= ",start_time=".db_escape($time);
        
        if($update_end_time)
            $sql .= ",end_time=".db_escape($time);
        
        if($update_run_by)
            $sql .= ",run_by=".db_escape($run_by);
                
        $sql .= " WHERE id=".db_escape($payroll_run_id);

        db_query($sql,"Payroll run status could not be updated");
           
}



function update_last_completed_employee_code($last_completed_employee_code, $payroll_run_id)
{
        $time = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."payroll_runs 
                SET 
                    last_completed_employee_code=".db_escape($last_completed_employee_code).",
                    end_time=".db_escape($time)." 
                WHERE id=".db_escape($payroll_run_id);

        db_query($sql,"Last completed employee could not be updated");
           
}

function update_calculated_performance_incentive_record_status($rec_id, $status_code, $payroll_run_id)
{
        $sql = "UPDATE ".TB_PREF."performance_incentives_details   
                SET 
                    status_code=".db_escape($status_code).",
                    payroll_run_id=".db_escape($payroll_run_id)." 
                WHERE id=".db_escape($rec_id);

        db_query($sql,"Calculated performance incentive record could not be updated");  
}

function update_calculated_attendance_allowance_summary_record_status($rec_id, $status_code, $payroll_run_id)
{
        $sql = "UPDATE ".TB_PREF."payroll_attendance_summary  
                SET 
                    status_code=".db_escape($status_code).",
                    payroll_run_id=".db_escape($payroll_run_id)." 
                WHERE id=".db_escape($rec_id);

        db_query($sql,"Payroll attendance summary record could not be updated");  
}

function update_calculated_worktime_deficit_summary_record_status($rec_id, $status_code, $payroll_run_id)
{
        $sql = "UPDATE ".TB_PREF."payroll_worktime_deficit_summary  
                SET 
                    status_code=".db_escape($status_code).",
                    payroll_run_id=".db_escape($payroll_run_id)." 
                WHERE id=".db_escape($rec_id);

        db_query($sql,"Worktime deficit summary record could not be updated");  
}

function update_approved_employee_allawance_usage_record_status($rec_id, $status_code, $payroll_run_id)
{
        $sql = "UPDATE ".TB_PREF."employee_allowances_usage   
                SET 
                    status_code=".db_escape($status_code).",
                    payroll_run_id=".db_escape($payroll_run_id)."
                WHERE id=".db_escape($rec_id);

        db_query($sql,"Employee allowances record could not be updated");  
}

function update_mobile_allowance_over_usage_record_status($rec_id, $status_code, $payroll_run_id)
{
        $sql = "UPDATE ".TB_PREF."mobile_allowances_details  
                SET 
                    status_code=".db_escape($status_code).",
                    payroll_run_id=".db_escape($payroll_run_id)."
                WHERE id=".db_escape($rec_id);

        db_query($sql,"Salary deduction record could not be updated");  
}

function update_salary_addition_record_status($rec_id, $employee_code, $status_code, $payroll_run_id)
{
//        $updated_by = $_SESSION['wa_current_user']->user;
//        $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."salary_additions  
                SET 
                    status_code=".db_escape($status_code).",
                    payroll_run_id=".db_escape($payroll_run_id)."
                WHERE id=".db_escape($rec_id);

        db_query($sql,"Salary addition record could not be updated");  
}


function update_salary_advance_record_status($rec_id, $employee_code, $status_code, $payroll_run_id)
{
//        $updated_by = $_SESSION['wa_current_user']->user;
//        $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."salary_advances  
                SET 
                    status_code=".db_escape($status_code).",
                    payroll_run_id=".db_escape($payroll_run_id)."
                WHERE id=".db_escape($rec_id);

        db_query($sql,"Salary advance record could not be updated");  
}


function update_salary_deduction_record_status($rec_id, $employee_code, $status_code, $payroll_run_id)
{
//        $updated_by = $_SESSION['wa_current_user']->user;
//        $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."salary_deductions  
                SET 
                    status_code=".db_escape($status_code).",
                    payroll_run_id=".db_escape($payroll_run_id)."
                WHERE id=".db_escape($rec_id);

        db_query($sql,"Salary deduction record could not be updated");  
}


function update_ot_payment_record_status($rec_id, $status_code, $payroll_run_id)
{

        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."attendance 
                SET 
                    ot_status_code=".db_escape($status_code).",
                    get_payments_payroll_run_id=".db_escape($payroll_run_id).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                WHERE id=".db_escape($rec_id);

        db_query($sql,"OT record could not be updated");
            
}



function update_nopay_leave_record_status($rec_id, $employee_code, $status_code, $payroll_run_id, $this_deduction)
{
//        $updated_by = $_SESSION['wa_current_user']->user;
//        $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."leaves 
                SET 
                    status_code=".db_escape($status_code).",
                    payroll_run_id=".db_escape($payroll_run_id).",
                    nopay_amount=".db_escape($this_deduction)." 
                WHERE id=".db_escape($rec_id);

        db_query($sql,"OT record could not be updated");  
}

function update_loan_record_status($rec_id, $employee_code, $status_code, $payroll_run_id, $this_installment)
{
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."loans 
                SET 
                    status=".db_escape($status_code).",
                    payroll_run_id=".db_escape($payroll_run_id).",
                    this_installment=".db_escape($this_installment).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                WHERE id=".db_escape($rec_id);

        db_query($sql,"OT record could not be updated");  
}


function update_ot_record_status($rec_id, $status_code, $payroll_run_id, $ot_amount, $payroll_ot_time)
{

        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."attendance 
                SET 
                    payroll_ot_time = ".db_escape($payroll_ot_time).",
                    ot_amount=".db_escape($ot_amount).",
                    ot_status_code=".db_escape($status_code).",
                    calculated_payroll_run_id=".db_escape($payroll_run_id).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                WHERE id=".db_escape($rec_id);

        db_query($sql,"OT record could not be updated");
            
}

function select_payroll_settings($payroll_id)
{
    $sql = "SELECT ps.*, 
                    rorc.value_code   rounding_off_reference_code, 
                    bfc.value_code   brought_forward_value_code, 
                    rosc.value_code   rounded_off_salary_code, 
                    cfc.value_code   carried_forward_value_code   
            FROM ".TB_PREF."payroll_settings ps 
                        LEFT JOIN ".TB_PREF."list_values rorc ON 
                            ps.rounding_off_reference_id = rorc.id 
                        LEFT JOIN ".TB_PREF."list_values bfc ON 
                            ps.brought_forward_value_id = bfc.id 
                        LEFT JOIN ".TB_PREF."list_values rosc  ON 
                            ps.rounded_off_salary_id = rosc.id 
                        LEFT JOIN ".TB_PREF."list_values cfc ON 
                            ps.carried_forward_value_id = cfc.id
            WHERE ps.payroll_id = ".db_escape($payroll_id); 

    $result = db_query($sql, "Payroll settings could not be retrieved");
    
    return db_fetch($result);
}

function select_approved_employee_allawance_usage_records($payroll_run_id, $employee_code, $component)
{
    $sql = "SELECT id, spent_amount    
            FROM ".TB_PREF."employee_allowances_usage    
            WHERE payroll_run_id = ".db_escape($payroll_run_id)." AND  
                    employee_code = ".db_escape($employee_code)." AND  
                    component = ".db_escape($component)." AND  
                    status_code IN('A', 'L')"; 

    $result = db_query($sql, "Approved employee allawances usage records could not be retrieved");
    
    return db_fetch($result);
}

function select_mobile_allowance_over_usage_records($component, $employee_code, $payroll_run_id)
{
    $sql = "SELECT * FROM ".TB_PREF."mobile_allowances_details 
            WHERE employee_code = ".db_escape($employee_code)." AND 
                    component = ".db_escape($component)." AND 
                    (status_code = ".db_escape(ATD_APPROVED)." OR status_code = ".db_escape('L').") AND 
                    payroll_run_id = ".db_escape($payroll_run_id);
    
    return db_query($sql, "Mobile allowance usgae records could not be retrieved");
}

function select_mobile_allowance_over_usage_record($rec_id)
{
    $sql = "SELECT * FROM ".TB_PREF."mobile_allowances_details 
            WHERE id = ".db_escape($rec_id);
    
    $result = db_query($sql, "Mobile allowance usgae record could not be retrieved");
    
    return db_fetch($result);
}


function select_approved_performance_incentive_value($payroll_run_id, $employee_code, $component)
{
    $sql = "SELECT id, amount_achieved    
            FROM ".TB_PREF."performance_incentives_details    
            WHERE payroll_run_id = ".db_escape($payroll_run_id)." AND  
                    employee_code = ".db_escape($employee_code)." AND  
                    component = ".db_escape($component)." AND  
                    status_code IN('A', 'L')"; 

    $result = db_query($sql, "Approved performance incentive value could not be retrieved");
    
    return db_fetch($result);
}


function select_approved_attendance_records_summary($payroll_run_id, $employee_code, $component)
{
    $sql = "SELECT id, granted_days   
            FROM ".TB_PREF."payroll_attendance_summary    
            WHERE payroll_run_id = ".db_escape($payroll_run_id)." AND  
                    employee_code = ".db_escape($employee_code)." AND  
                    component = ".db_escape($component)." AND  
                    status_code IN('A', 'L')"; 

    $result = db_query($sql, "Approved worktime deficit records could not be retrieved");
    
    return db_fetch($result);
}

function select_approved_worktime_deficit_summary($payroll_run_id, $employee_code, $process_code)
{
    $sql = "SELECT id, deducted_time   
            FROM ".TB_PREF."payroll_worktime_deficit_summary   
            WHERE payroll_run_id = ".db_escape($payroll_run_id)." AND  
                    employee_code = ".db_escape($employee_code)." AND  
                    component = ".db_escape($process_code)." AND  
                    status_code IN('A', 'L')"; 

    $result = db_query($sql, "Approved worktime deficit summary could not be retrieved");
    
    return db_fetch($result);
}

function select_salary_additions($process_code, $employee_code, $period_end_date)
{
    $sql = "SELECT sa.*  
            FROM ".TB_PREF."salary_additions sa, 
                    ".TB_PREF."list_values lv   
            WHERE employee_code = ".db_escape($employee_code)." AND  
                    due_date <= ".db_escape($period_end_date)." AND 
                    status_code IN('A', 'L') AND 
                    sa.type_id = lv.id AND lv.value_code = ".db_escape($process_code)." AND lv.list_code = 'SALARY_ADDITIONS'"; 

    $result = db_query($sql, "Salary additions could not be retrieved");
    
    return $result;
}


function select_salary_addition_record($id)
{
    $sql = "SELECT *  
            FROM ".TB_PREF."salary_additions   
            WHERE id = ".db_escape($id);  

    $result = db_query($sql, "Salary addition could not be retrieved");
    
    return db_fetch($result);
}

function select_salary_advances($process_code, $employee_code, $period_end_date)
{
    $sql = "SELECT sa.*  
            FROM ".TB_PREF."salary_advances sa,
                    ".TB_PREF."list_values lv  
            WHERE employee_code = ".db_escape($employee_code)." AND  
                    due_date <= ".db_escape($period_end_date)." AND 
                    status_code IN('D', 'L') AND 
                    sa.type_id = lv.id AND lv.value_code = ".db_escape($process_code)." AND lv.list_code = 'SALARY_ADVANCES'"; 

    $result = db_query($sql, "Salary advances could not be retrieved");
    
    return $result;
}


function select_salary_deductions($process_code, $employee_code, $period_end_date)
{
    $sql = "SELECT sd.*  
            FROM ".TB_PREF."salary_deductions sd,
                    ".TB_PREF."list_values lv  
            WHERE employee_code = ".db_escape($employee_code)." AND  
                    due_date <= ".db_escape($period_end_date)." AND 
                    status_code IN('A', 'L') AND 
                    sd.type_id = lv.id AND lv.value_code = ".db_escape($process_code)." AND lv.list_code = 'SALARY_DEDUCTIONS'"; 

    $result = db_query($sql, "Salary deductions could not be retrieved");
    
    return $result;
}

function select_salary_advance_record($id)
{
    $sql = "SELECT *  
            FROM ".TB_PREF."salary_advances   
            WHERE id = ".db_escape($id);  

    $result = db_query($sql, "Salary advancecould not be retrieved");
    
    return db_fetch($result);
}


function select_salary_deduction_record($id)
{
    $sql = "SELECT *  
            FROM ".TB_PREF."salary_deductions   
            WHERE id = ".db_escape($id);  

    $result = db_query($sql, "Salary_deduction could not be retrieved");
    
    return db_fetch($result);
}



function select_all_ot_payments($employee_code)
{
    $sql = "SELECT *  
            FROM ".TB_PREF."attendance   
            WHERE employee_code = ".db_escape($employee_code)." AND  
                    granted_ot_time > '00:00:00' AND 
                    ot_status_code IN('D')"; 

    $result = db_query($sql, "Paid OT records could not be retrieved");
    
    return $result;
}


function select_ot_payment_record($id)
{
    $sql = "SELECT id, ot_amount  
            FROM ".TB_PREF."attendance    
            WHERE id = ".db_escape($id);  

    $result = db_query($sql, "OT payment record could not be retrieved");
    
    return db_fetch($result);
}


function select_all_approved_ot($employee_code, $payroll_run)
{
    $sql = "SELECT *  
            FROM ".TB_PREF."attendance   
            WHERE employee_code = ".db_escape($employee_code)." AND  
                    entry_date <= ".db_escape($payroll_run['period_end_date'])." AND 
                    granted_ot_time > '00:00:00' AND 
                    shift_id > 0 AND 
                    ot_status_code IN('A', 'L') 
            ORDER BY entry_date ";

    $result = db_query($sql, "Approved OT records could not be retrieved");
    
    return $result;
}


function select_ot_record($id)
{
    $sql = "SELECT a.* 
            FROM ".TB_PREF."attendance a 
            WHERE a.id = ".db_escape($id);  

    $result = db_query($sql, "OT details could not be retrieved");
    
    return db_fetch($result);
}


function select_nopay_leaves($employee_code, $period_end_date)
{
    $sql = "SELECT *  
            FROM ".TB_PREF."leaves   
            WHERE employee_code = ".db_escape($employee_code)." AND  
                    to_date <= ".db_escape($period_end_date)." AND 
                    nopay_days <> 0.0 AND 
                    status_code IN('A', 'L')"; 

    $result = db_query($sql, "Nopay leaves could not be retrieved");
    
    return $result;
}


function select_nopay_leave_record($id)
{
    $sql = "SELECT *  
            FROM ".TB_PREF."leaves   
            WHERE id = ".db_escape($id);  

    $result = db_query($sql, "Nopay leave details could not be retrieved");
    
    return db_fetch($result);
}





function select_worktime_deficit_record($id)
{
    $sql = "SELECT a.* 
            FROM ".TB_PREF."attendance a 
            WHERE a.id = ".db_escape($id);  

    $result = db_query($sql, "Worktime deficit details could not be retrieved");
    
    return db_fetch($result);
}

function select_employee_loans($employee_code)
{
        $sql = "SELECT l.*   
            FROM ".TB_PREF."loans l 
            WHERE l.employee_code = ".db_escape($employee_code);

    $result = db_query($sql, "Employee loans could not be retrieved");
    
    return $result; 
}

function select_active_loans($employee_code, $payroll_run)
{    
        $sql = "SELECT l.*, vli.total_paid, vli.total_interest  
            FROM ".TB_PREF."loans l 
                    LEFT JOIN ".TB_PREF."vw_loan_installments vli ON  
                                l.id = vli.loan_id AND l.loan_amount > vli.total_paid 
            WHERE 
                    l.employee_code = ".db_escape($employee_code)." AND 
                    l.start_date <= ".db_escape($payroll_run['period_end_date'])." AND  
                    l.inactive = 0 AND 
                    l.completed = 0 "; 

    $result = db_query($sql, "Loans could not be retrieved");
    
    return $result;
}


function select_loan($loan_id)
{    
    $sql = "SELECT l.loan_amount, l.start_date, l.installment_amount, 
                    vli.total_paid, vli.total_interest  
            FROM ".TB_PREF."loans l 
                LEFT JOIN ".TB_PREF."vw_loan_installments vli ON   
                            l.id = vli.loan_id 
            WHERE l.id = ".db_escape($loan_id);  

    $sql .= " ORDER By start_date" ;
    
    $result = db_query($sql, "Loan details could not be retrieved");
    
    return db_fetch($result);
}


//function get_loan_installments($loan_id)
//{
//    $sql = "SELECT * 
//            FROM ".TB_PREF."loan_installments                     
//            WHERE status_code <> 'L' AND 
//                    loan_id = ".db_escape($loan_id);
//        
//	//$sql .= " ORDER BY id";
//        
//    $result =  db_query($sql, "Loan installments could not be retrieved");
//    
//    return $result;
//
//}

function delete_previously_calculated_loan_installments($payroll_run_id, $loan_id)
{
    $sql = "DELETE FROM ".TB_PREF."loan_installments                     
            WHERE status_code = 'L' AND 
                    loan_id = ".db_escape($loan_id)." AND 
                    payroll_run_id = ".db_escape($payroll_run_id);
        
	//$sql .= " ORDER BY id";
        
    $result =  db_query($sql, "Previously calculated loan installments could not be deleted");
    
    return $result;
}


function select_calculated_payroll_value($process_code, $employee_code, $payroll_run_id)
{
    $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($process_code);
  

    $result = db_query($sql, "Payroll calculated value could not be retrieved");
    
    return db_fetch($result);
}

function select_value_table_all_records($table_id)
{
    $sql = "SELECT vt.*,
                    IF(vt.table_id > 0, (SELECT lv.value_code FROM ".TB_PREF."list_values lv WHERE lv.id = vt.salary_total_id), '') process_code
            FROM ".TB_PREF."value_tables vt   
            WHERE vt.table_id = ".db_escape($table_id)." 
            ORDER BY vt.upto";

    $result = db_query($sql, "Value table records could not be retrieved");
    
    return $result;
}


function select_value_table_record($table_id, $reference_value)
{
    $sql = "SELECT vt.*,
                    IF(vt.table_id > 0, (SELECT lv.value_code FROM ".TB_PREF."list_values lv WHERE lv.id = vt.salary_total_id), '') process_code
            FROM ".TB_PREF."value_tables vt   
            WHERE vt.upto >= ".db_escape($reference_value)." AND 
                    vt.table_id = ".db_escape($table_id)." 
            ORDER BY vt.upto LIMIT 1";

    $result = db_query($sql, "Value table record could not be retrieved");
    
    return db_fetch($result);
}


function select_salary_component($record_type, $process_code, $employee_or_group, $payroll_run)
{
    $sql = "SELECT sc.*, lv.value_code as process_code 
            FROM 
                ".TB_PREF."salary_components sc,  
                ".TB_PREF."list_values lv   
            WHERE sc.salary_component_id = lv.id AND 
                    sc.inactive = 0  AND 
                    sc.type <= ".db_escape($record_type)." AND 
                    sc.effective_date <= ".db_escape($payroll_run['period_end_date'])." AND 
                    sc.end_date >= ".db_escape($payroll_run['period_start_date']);
    if($record_type == SCT_COMPANY_ADDITIONS || $record_type == SCT_COMPANY_DEDUCTIONS){
        $sql .= " AND sc.employee_code = '' AND 
                        sc.payroll_id = ".db_escape($payroll_run['payroll_id']); 
    }
    elseif(
            $record_type == SCT_GROUP_ADDITIONS || $record_type == SCT_GROUP_DEDUCTIONS || 
            $record_type == SCT_EMPLOYEE_ADDITIONS || $record_type == SCT_EMPLOYEE_DEDUCTIONS ||  $record_type == SCT_EMPLOYEE_ALLOWANCES_OVER_USAGE || 
            $record_type == SCT_ATTENDANCE_ALLOWANCES || $record_type == SCT_WORKTIME_DEFICITS || 
            $record_type == SCT_PERFORMANCE_RATE_INCENTIVES || $record_type == SCT_PERFORMANCE_VALUE_INCENTIVES || 
            $record_type == SCT_MOBILE_OVER_USAGE || 
            $record_type == SCT_COMPANY_ADDITIONS || $record_type == SCT_COMPANY_DEDUCTIONS){
        $sql .= " AND sc.employee_code = ".db_escape($employee_or_group)." AND 
                        sc.payroll_id = 0"; 
    }
    else{
        $sql .= " AND sc.payroll_id = ".db_escape($payroll_run['payroll_id']); 
    }
    
    $sql .= " AND lv.value_code = ".db_escape($process_code);  

    $sql .= " ORDER BY sc.effective_date LIMIT 1";
    
    $result = db_query($sql, "Employee salary component could not be retrieved");
    
    return db_fetch($result);
}


function select_payroll_employee_total_nopay_days($employee_code, $payroll_start_date, $payroll_end_date){
    
    $sql = "SELECT SUM(nopay_days) total_nopay_days 
            FROM  ".TB_PREF."leaves 
            WHERE employee_code = ".db_escape($employee_code)." AND 
                    from_date >= ".db_escape($payroll_start_date)." AND 
                    from_date <= ".db_escape($payroll_end_date); 
    
    $result = db_query($sql, "Payroll employee total nopay days  could not be retrieved");
    
    return db_fetch($result);
    
}



function get_payroll_calculation_seq($payroll_id)
{
    $sql = "SELECT pcs.process_code, 
                    lv.list_code as process_type 
            FROM 
                ".TB_PREF."payroll_calculation_seq pcs,  
                ".TB_PREF."list_values lv   
            WHERE pcs.list_value_id = lv.id AND 
                    payroll_id = ".db_escape($payroll_id)." AND 
                    pcs.active = 1  
            ORDER BY pcs.seq_no";
  

    $result = db_query($sql, "Payroll calculation sequence could not be retrieved");
    
    return $result;
}

function select_next_payroll_employee($last_employee_code, $payroll_run)
{
    $sql = "SELECT em.employee_code, salary_group_id, date_of_joining, resigned, date_of_resignation   
            FROM 
                ".TB_PREF."employee_master em 
            WHERE em.inactive = 0 AND 
                    em.inactive = 0 AND
                    em.employee_code > ".db_escape($last_employee_code)." AND 
                    em.date_of_joining <= ".db_escape($payroll_run['period_end_date'])." AND  
                            (em.resigned = 0 OR 
                            em.resigned = 1 AND em.date_of_resignation >= ".db_escape($payroll_run['period_start_date']).") 
            ORDER BY  em.employee_code LIMIT 1";

    $result = db_query($sql, "Next payroll employee could not be retrieved");
    
    return db_fetch($result);
}

function select_last_completed_emloyee($id)
{
    $sql = "SELECT last_completed_employee_code  
            FROM 
                ".TB_PREF."payroll_runs   
            WHERE id = ".$id;
  

    $result = db_query($sql, "Last completed employee could not be retrieved");
    
    return db_fetch($result);
}

function select_component_total($payroll_run_id, $component)
{
    $sql = "SELECT SUM(value) component_total 
            FROM ".TB_PREF."payroll_run_details   
            WHERE payroll_run_id = ".db_escape($payroll_run_id)." AND 
                    component = ".db_escape($component)." AND 
                    employee_code <> ''";
  

    $result = db_query($sql, "Payroll component total could not be retrieved");
    
    return db_fetch($result);
}


function select_nopay_refercnce_details($payroll_id, $employment_type_id)
{
	$sql="SELECT * 
            FROM ".TB_PREF."payroll_nopay_references 
            WHERE payroll_id = ".db_escape($payroll_id)." AND 
                    employment_type_id = ".$employment_type_id;

	$result = db_query($sql,"Payroll settings could not be retrieved");

	return db_fetch($result);
}


function get_applicable_employment_type($employee_code, $payroll_period_start_date, $payroll_period_end_date)
{
    	$sql = "SELECT employment_type_id  
                FROM ".TB_PREF."employment_details 
                WHERE employee_code = ".db_escape($employee_code)." AND 
                        contract_start_date <= ".db_escape($payroll_period_end_date)." AND 
                        contract_end_date >= ".db_escape($payroll_period_start_date)." 
                ORDER BY contract_start_date 
                LIMIT 1 ";
	
        $result = db_query($sql,"Could not retrieve employment type");	
        
        return db_fetch($result);
    
}

function delete_payroll_loan_installments($loan_id, $payroll_run_id){
    
    $sql = "DELETE FROM ".TB_PREF."loan_installments 
            WHERE loan_id = ".db_escape($loan_id)." AND 
                    payroll_run_id = ".db_escape($payroll_run_id);
    
    db_query($sql, "Payroll loan installment could not be deleted");
    
}

?>