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

function insert_nopay_deduction($leave_entitlement_id, $nopay_days, $nopay_amount, $payroll_run_id)
{

        $created_on = date('Y-m-d H:i:s');
        
        
	$sql = "INSERT INTO ".TB_PREF."nopay_deductions (
                    leave_entitlement_id, nopay_days,  
                    nopy_amount,payroll_run_id,
                    created_on) 
                VALUES ("
                    .db_escape($leave_entitlement_id).", ".db_escape($nopay_days).", "
                    .db_escape($nopay_amount). ", ".db_escape($payroll_run_id). ", "
                    .db_escape($created_on).")";
        
        db_query($sql,"Nopay deduction could not be added");
        
        return db_insert_id();


}


function update_last_calculated_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 calculated emplyee could not be updated");
           
}



function update_processed_salary_addition($id, $payroll_run_id)
{
        
        $sql = "UPDATE ".TB_PREF."salary_additions   
                SET status_code = 'Y' 
                WHERE id = ".db_escape($id)." AND 
                    payroll_run_id = ".db_escape($payroll_run_id)." AND 
                    status_code = 'L' ";

        db_query($sql,"Processed salary addition could not be updated.");
        
        return db_num_affected_rows();
}


function update_processed_salary_advance($id, $payroll_run_id)
{
        
        $sql = "UPDATE ".TB_PREF."salary_advances  
                SET status_code = 'Y' 
                WHERE id = ".db_escape($id)." AND 
                    payroll_run_id = ".db_escape($payroll_run_id)." AND 
                    status_code = 'L' ";

        db_query($sql,"Processed salary advance could not be updated.");
        
        return db_num_affected_rows();
}


function update_processed_salary_deduction($id, $payroll_run_id)
{
        
        $sql = "UPDATE ".TB_PREF."salary_deductions  
                SET status_code = 'Y' 
                WHERE id = ".db_escape($id)." AND 
                    payroll_run_id = ".db_escape($payroll_run_id)." AND 
                    status_code = 'L' ";

        db_query($sql,"Processed salary deduction could not be updated.");
        
        return db_num_affected_rows();
}



function update_paid_ot_amounts($id, $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 
                    status_code = 'Y',   
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)."  
                WHERE id = ".db_escape($id)." AND 
                    get_payments_payroll_run_id = ".db_escape($payroll_run_id)." AND 
                    ot_status_code = 'S'";

        db_query($sql,"Paid OT amount could not be updated.");
        
        return db_num_affected_rows();
            
}


function update_processed_ot_amounts($id, $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 = 'D',   
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)."  
                WHERE id = ".db_escape($id)." AND 
                    calculated_payroll_run_id = ".db_escape($payroll_run_id)." AND 
                    ot_status_code = 'L'";

        db_query($sql,"Processed OT amount could not be updated.");
        
        return db_num_affected_rows();
            
}

function update_processed_nopay_deduction($id, $nopay_days, $payroll_run_id)
{
        
        $sql = "UPDATE ".TB_PREF."leaves 
                SET status_code = 'Y' 
                WHERE id = ".db_escape($id)." AND 
                    payroll_run_id = ".db_escape($payroll_run_id)." AND 
                    status_code = 'L' ";

        db_query($sql,"Processed nopay deduction could not be updated.");
        
        return db_num_affected_rows();
}


function update_processed_attendance_summary_value($id)
{

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

        $sql = "UPDATE ".TB_PREF."payroll_attendance_summary 
                SET 
                    status_code = 'Y',   
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)."  
                WHERE id = ".db_escape($id)." AND 
                    status_code = 'L'";

        db_query($sql,"Processed attendance summary value could not be updated.");
        
        return db_num_affected_rows();
            
}

function update_processed_worktime_deficit_summary_deduction($id)
{

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

        $sql = "UPDATE ".TB_PREF."payroll_worktime_deficit_summary 
                SET 
                    status_code = 'Y',   
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)."  
                WHERE id = ".db_escape($id)." AND 
                    status_code = 'L'";

        db_query($sql,"Processed worktime deficit deduction could not be updated.");
        
        return db_num_affected_rows();
            
}

function update_processed_leave_entitlement($leave_entitlement_id, $nopay_days, $payroll_run_id)
{

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

        $sql = "UPDATE ".TB_PREF."leave_entitlements  
                SET leave_payroll_deducted = leave_payroll_deducted + ".$nopay_days.", 
                    payroll_run_id = ".db_escape($payroll_run_id).", 
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)."
                WHERE id = ".db_escape($leave_entitlement_id);

        db_query($sql,"Leave entitlement summary could not be updated.");

        return db_num_affected_rows();        
            
}

function update_processed_employee_allowances_over_usage($id)
{

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

        $sql = "UPDATE ".TB_PREF."employee_allowances_usage 
                SET status_code = 'Y',
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)."  
                WHERE id = ".db_escape($id);

        db_query($sql,"Processed employee allowances usage could not be updated.");
        
        return db_num_affected_rows();
            
}


function update_processed_performance_incentive_value($id)
{

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

        $sql = "UPDATE ".TB_PREF."performance_incentives_details 
                SET status_code = 'Y',
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)."  
                WHERE id = ".db_escape($id);

        db_query($sql,"Processed performance inentive value could not be updated.");
        
        return db_num_affected_rows();
            
}


function update_processed_mobile_allowances_over_usage($id)
{

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

        $sql = "UPDATE ".TB_PREF."mobile_allowances_details 
                SET status_code = 'Y',
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)."  
                WHERE id = ".db_escape($id);

        db_query($sql,"Processed mobile allowances usage could not be updated.");
        
        return db_num_affected_rows();
            
}

function update_processed_loan_installment($id)
{

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

        $sql = "UPDATE ".TB_PREF."loan_installments 
                SET status_code = 'Y' 
                WHERE id = ".db_escape($id);

        db_query($sql,"Processed load installment could not be updated.");
        
        return db_num_affected_rows();
            
}

function update_processed_loan($id, $completed)
{
        $sql = "UPDATE ".TB_PREF."loans 
                SET completed = ".db_escape($completed)." 
                WHERE id = ".db_escape($id);

        db_query($sql,"Processed loan could not be updated.");
            
}

function select_next_calculated_emloyee($last_employee_code, $payroll_run_id)
{
    $sql = "SELECT employee_code  
            FROM 
                ".TB_PREF."payroll_run_details 
            WHERE employee_code > ".db_escape($last_employee_code)." AND 
                    payroll_run_id = ".db_escape($payroll_run_id)." 
            ORDER BY  employee_code LIMIT 1";

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


function select_calculated_salary_additions($type_id, $employee_code, $payroll_run_id, $status)
{
    $sql = "SELECT s.id, 
                    s.salary_addition,
                    s.due_date    
            FROM ".TB_PREF."salary_additions s   
            WHERE s.payroll_run_id = ".db_escape($payroll_run_id)." AND  
                    s.employee_code = ".db_escape($employee_code)." AND
                    s.status_code = ".db_escape($status)." AND
                    s.type_id = ".db_escape($type_id);  
     
    $result = db_query($sql, "Calculated salary additions could not be retrieved");
    
    return $result;
}


function select_calculated_salary_advances($type_id, $employee_code, $payroll_run_id, $status)
{
    $sql = "SELECT s.id, 
                    s.salary_advance,
                    s.due_date 
            FROM ".TB_PREF."salary_advances s   
            WHERE s.payroll_run_id = ".db_escape($payroll_run_id)." AND  
                    s.employee_code = ".db_escape($employee_code)." AND
                    s.status_code = ".db_escape($status)." AND
                    s.type_id = ".db_escape($type_id);  
     
    $result = db_query($sql, "Calculated salary advances could not be retrieved");
    
    return $result;
}


function select_calculated_salary_deductions($type_id, $employee_code, $payroll_run_id, $status)
{
    $sql = "SELECT s.id, 
                    s.salary_deduction,
                    s.due_date 
            FROM ".TB_PREF."salary_deductions s   
            WHERE s.payroll_run_id = ".db_escape($payroll_run_id)." AND  
                    s.employee_code = ".db_escape($employee_code)." AND
                    s.status_code = ".db_escape($status)." AND
                    s.type_id = ".db_escape($type_id);  
     
    $result = db_query($sql, "Calculated salary deductions could not be retrieved");
    
    return $result;
}


function select_calculated_nopay_deductions($employee_code, $payroll_run_id, $status)
{
    $sql = "SELECT l.id, 
                    l.leave_entitlement_id, 
                    l.from_date,
                    l.to_date,
                    l.nopay_days, 
                    l.nopay_amount 
            FROM ".TB_PREF."leaves l  
            WHERE l.payroll_run_id = ".db_escape($payroll_run_id)." AND  
                    l.employee_code = ".db_escape($employee_code)." AND
                    l.status_code = ".db_escape($status); 
     
    $result = db_query($sql, "Calculated nopay deductions could not be retrieved");
    
    return $result;
}

function select_calcualted_attendance_summary_values($employee_code, $payroll_run_id, $component, $status_code)
{
    $sql = "SELECT * 
            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 = ".db_escape($status_code); 
     
    $result = db_query($sql, "Calculated attendance summay deductions could not be retrieved");
    
    return $result;
}


function select_calculated_worktime_deficit_summary_deductions($employee_code, $payroll_run_id, $component, $status_code)
{
    $sql = "SELECT * 
            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($component)." AND 
                    status_code = ".db_escape($status_code); 
     
    $result = db_query($sql, "Calculated worktime deficit summay deductions could not be retrieved");
    
    return $result;
}

function select_paid_ot_amounts($employee_code, $payroll_run_id, $status_code)
{
    $sql = "SELECT id, from_date_time1, granted_ot_time, ot_amount 
            FROM ".TB_PREF."attendance  
            WHERE get_payments_payroll_run_id = ".db_escape($payroll_run_id)." AND  
                    employee_code = ".db_escape($employee_code)." AND
                    ot_status_code = ".db_escape($status_code); 
     
    $result = db_query($sql, "Paid ot amounts could not be retrieved");
    
    return $result;
}


function select_calculated_employee_allowances_over_usages($employee_code, $payroll_run_id, $component, $status)
{
    $sql = "SELECT *   
            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 = ".db_escape($status); 
     
    $result = db_query($sql, "Calculated employee allowances usages could not be retrieved");
    
    return $result;
}

function select_calculated_performances_incentive_value($employee_code, $payroll_run_id, $component, $status)
{
    $sql = "SELECT *   
            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 = ".db_escape($status); 
     
    $result = db_query($sql, "Calculated performance incentive values could not be retrieved");
    
    return $result;
}

function select_calculated_mobile_allowances_over_usages($employee_code, $payroll_run_id, $component, $status)
{
    $sql = "SELECT *   
            FROM ".TB_PREF."mobile_allowances_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 = ".db_escape($status); 
     
    $result = db_query($sql, "Calculated mobile allowances usages could not be retrieved");
    
    return $result;
}


function select_calculated_loan_installments($employee_code, $payroll_run_id, $status)
{
    $sql = "SELECT li.*   
            FROM ".TB_PREF."loans l,  "
                    .TB_PREF."loan_installments li    
            WHERE l.id = li.loan_id AND 
                    li.payroll_run_id = ".db_escape($payroll_run_id)." AND  
                    l.employee_code = ".db_escape($employee_code)." AND
                    li.status_code = ".db_escape($status); 
     
    $result = db_query($sql, "Calculated loan installments could not be retrieved");
    
    return $result;
}


function select_calculated_ot_amounts($employee_code, $payroll_run_id, $status_codes)
{
    $sql = "SELECT id, from_date_time1, granted_ot_time, ot_amount 
            FROM ".TB_PREF."attendance  
            WHERE calculated_payroll_run_id = ".db_escape($payroll_run_id)." AND  
                    employee_code = ".db_escape($employee_code)." AND
                    ot_status_code IN (".$status_codes.")"; 
     
    $result = db_query($sql, "Calculated ot amounts could not be retrieved");
    
    return $result;
}


function select_employee_name($employee_code)
{
    $sql = "SELECT initials, first_name, middle_name, last_name 
            FROM 
                ".TB_PREF."employee_master  
            WHERE employee_code = ".db_escape($employee_code);
  

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


function select_calculated_payroll_run_records($payroll_run_id, $employee_code)
{
    $sql = "SELECT prd.*, 
                    lv.list_code process_type,
                    lv.id component_id,
                    lv.value_name process_name  
            FROM ".TB_PREF."payroll_run_details prd,
                    ".TB_PREF."list_values lv  
            WHERE lv.list_code IN ('COMPANY_ADDITION_SALARY_COMP', 
                                    'COMPANY_DEDUCTION_SALARY_COMP', 
                                    'GROUP_ADDITION_SALARY_COMP', 
                                    'GROUP_DEDUCTION_SALARY_COMP',
                                    'EMPLOYEE_ADDITION_SALARY_COMP',
                                    'EMPLOYEE_DEDUCTION_SALARY_COMP',
                                    'EMPLOYEE_ALLOWANCES_OVER_USAGE',
                                    'ATTENDANCE_ALLOWANCES',
                                    'WORKTIME_DEFICITS',
                                    'PERFORMANCE_RATE_INCENTIVES',
                                    'PERFORMANCE_VALUE_INCENTIVES',
                                    'MOBILE_ALLOWANCES_OVER_USAGE',
                                    'SALARY_ADDITIONS',
                                    'SALARY_ADVANCES',
                                    'SALARY_DEDUCTIONS',
                                    'SALARY_GROUP_TOTALS',
                                    'SALARY_TOTALS',
                                    'COMMON_PAYROLL_PROCESSES') AND 
                    lv.value_code = prd.component AND                                     
                    prd.payroll_run_id = ".db_escape($payroll_run_id)." AND 
                    prd.employee_code = ".db_escape($employee_code);
    
    return db_query($sql, "Payroll run records could not be retrieved");
}

function select_payroll_employee_result($payroll_run_id)
{
    $sql = "SELECT prd.*, 
                    lv.list_code process_type,
                    lv.id process_id,
                    lv.value_name process_name  
            FROM ".TB_PREF."payroll_run_details prd,
                    ".TB_PREF."list_values lv  
            WHERE lv.list_code IN ('COMPANY_ADDITION_SALARY_COMP', 
                                    'COMPANY_DEDUCTION_SALARY_COMP', 
                                    'GROUP_ADDITION_SALARY_COMP', 
                                    'GROUP_DEDUCTION_SALARY_COMP',
                                    'EMPLOYEE_ADDITION_SALARY_COMP',
                                    'EMPLOYEE_DEDUCTION_SALARY_COMP',
                                    'EMPLOYEE_ALLOWANCES_OVER_USAGE',
                                    'ATTENDANCE_ALLOWANCES',
                                    'WORKTIME_DEFICITS',
                                    'PERFORMANCE_RATE_INCENTIVES',
                                    'PERFORMANCE_VALUE_INCENTIVES',
                                    'MOBILE_ALLOWANCES_OVER_USAGE',
                                    'SALARY_ADDITIONS',
                                    'SALARY_ADVANCES',
                                    'SALARY_DEDUCTIONS',
                                    'SALARY_GROUP_TOTALS',
                                    'SALARY_TOTALS',
                                    'COMMON_PAYROLL_PROCESSES',
                                    'SALARY_ROUNDING_OFF') AND 
                    lv.value_code = prd.component AND                                     
                    prd.employee_code <> '' AND 
                    prd.payroll_run_id = ".db_escape($payroll_run_id);
                    

    return db_query($sql, "Payroll run records could not be retrieved");
}


function select_payroll_run_summary($payroll_run_id)
{
    $sql = "SELECT prd.*, 
                    lv.list_code process_type,
                    lv.id process_id,
                    lv.value_name process_name  
            FROM ".TB_PREF."payroll_run_details prd,
                    ".TB_PREF."list_values lv 
            WHERE prd.component = lv.value_code AND 
                    lv.list_code IN ('COMPANY_ADDITION_SALARY_COMP', 
                                    'COMPANY_DEDUCTION_SALARY_COMP', 
                                    'GROUP_ADDITION_SALARY_COMP', 
                                    'GROUP_DEDUCTION_SALARY_COMP',
                                    'EMPLOYEE_ADDITION_SALARY_COMP',
                                    'EMPLOYEE_DEDUCTION_SALARY_COMP',
                                    'EMPLOYEE_ALLOWANCES_OVER_USAGE',
                                    'ATTENDANCE_ALLOWANCES',
                                    'WORKTIME_DEFICITS',
                                    'PERFORMANCE_RATE_INCENTIVES',
                                    'PERFORMANCE_VALUE_INCENTIVES',
                                    'MOBILE_ALLOWANCES_OVER_USAGE',
                                    'SALARY_ADDITIONS',
                                    'SALARY_ADVANCES',
                                    'SALARY_DEDUCTIONS',
                                    'SALARY_GROUP_TOTALS',
                                    'SALARY_TOTALS',
                                    'COMMON_PAYROLL_PROCESSES') AND   
                    prd.employee_code = '' AND 
                    prd.payroll_run_id = ".db_escape($payroll_run_id);
                    

    return db_query($sql, "Payroll run records could not be retrieved");
}


function select_payroll_run_summary_accounts($payroll_run_id)
{
    $sql = "SELECT prd.id prd_id, prd.payroll_run_id, prd.component, 
                    prd.value, 
                    lv.id process_id,
                    lv.value_name process_name,
                    pa.debit_code,
                    pa.credit_code 
            FROM ".TB_PREF."payroll_run_details prd,
                    ".TB_PREF."list_values lv,
                    ".TB_PREF."payroll_accounts pa,
                    ".TB_PREF."payroll_runs pr  
            WHERE prd.employee_code = '' AND 
                    prd.component = lv.value_code AND 
                    lv.list_code IN ('COMPANY_ADDITION_SALARY_COMP', 
                                    'COMPANY_DEDUCTION_SALARY_COMP', 
                                    'GROUP_ADDITION_SALARY_COMP', 
                                    'GROUP_DEDUCTION_SALARY_COMP',
                                    'EMPLOYEE_ADDITION_SALARY_COMP',
                                    'EMPLOYEE_DEDUCTION_SALARY_COMP',
                                    'EMPLOYEE_ALLOWANCES_OVER_USAGE',
                                    'ATTENDANCE_ALLOWANCES',
                                    'WORKTIME_DEFICITS',
                                    'PERFORMANCE_RATE_INCENTIVES',
                                    'PERFORMANCE_VALUE_INCENTIVES',
                                    'MOBILE_ALLOWANCES_OVER_USAGE',
                                    'SALARY_ADDITIONS',
                                    'SALARY_ADVANCES',
                                    'SALARY_DEDUCTIONS',
                                    'SALARY_GROUP_TOTALS',
                                    'SALARY_TOTALS',
                                    'COMMON_PAYROLL_PROCESSES') AND 
                    lv.id = pa.component_id AND                                     
                    prd.payroll_run_id = pr.id AND 
                    pr.payroll_id = pa.payroll_id AND 
                    prd.payroll_run_id = ".db_escape($payroll_run_id);
                    

    return db_query($sql, "Payroll run records could not be retrieved");
}

function select_payroll_run_status($payroll_run_id)
{
    $sql = "SELECT status  
            FROM ".TB_PREF."payroll_runs   
            WHERE id = ".db_escape($payroll_run_id);
  

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

function upadte_payroll_component_summary_record($prd_id, $journal_id)
{
        $sql = "UPDATE ".TB_PREF."payroll_run_details  
                SET comment = ".  db_escape($journal_id)."  
                WHERE id = ".db_escape($prd_id);

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

        return db_num_affected_rows();   
}
?>