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

function get_payroll_summary_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 
                    lv.list_code IN ('ATTENDANCE_ALLOWANCES',
                                    'WORKTIME_DEFICITS') AND 
                    payroll_id = ".db_escape($payroll_id)." AND 
                    pcs.active = 1  
            ORDER BY pcs.seq_no";
  

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


function select_period_leave_records($employee_code, $period_start_date, $period_end_date)
{
    $sql = "SELECT l.is_revoke, 
                    ld.date, ld.leave_days, ld.leave_time_code 
            FROM ".TB_PREF."leaves l, ".TB_PREF."leave_details ld  
            WHERE l.id = ld.leave_id AND 
                    l.employee_code = ".db_escape($employee_code)." AND  
                    ld.date >= ".db_escape($period_start_date)." AND 
                    ld.date <= ".db_escape($period_end_date);

    return db_query($sql, "Period leave records could not be retrieved");

}

function select_fullday_holiday_summary($period_start_date, $period_end_date)
{
    $sql = "SELECT count(id) as record_count    
            FROM ".TB_PREF."holidays  
            WHERE holiday_time_code = 'F' AND 
                    holiday_date >= ".db_escape($period_start_date)." AND 
                    holiday_date <= ".db_escape($period_end_date);

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


function select_attendance_records_summary($employee_code, $period_start_date, $period_end_date)
{
    $sql = "SELECT count(id) as record_count    
            FROM ".TB_PREF."attendance  
            WHERE employee_code = ".db_escape($employee_code)." AND  
                    entry_date >= ".db_escape($period_start_date)." AND 
                    entry_date <= ".db_escape($period_end_date);

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


function insert_payroll_employee_attendance_summary($payroll_run_id, $employee_code,
                        $component, $calculated_days, $comment = "")
{        
	$sql = "INSERT INTO ".TB_PREF."payroll_attendance_summary (
                    payroll_run_id,employee_code,  
                    component, 
                    calculated_days, granted_days,  
                    comment, status_code) 
                VALUES ("
                    .db_escape($payroll_run_id).", ".db_escape($employee_code).", "
                    .db_escape($component). ", "
                    .db_escape($calculated_days).", ".db_escape($calculated_days).", "
                    .db_escape($comment).", ".db_escape(ATD_NEW).")";
        
        db_query($sql,"Payroll employee attendance summary could not be inserted");


}


function select_all_approved_worktime_deficits($employee_code, $start_date, $end_date)
{
    $sql = "SELECT *  
            FROM ".TB_PREF."attendance   
            WHERE employee_code = ".db_escape($employee_code)." AND  
                    entry_date >= ".db_escape($start_date)." AND 
                    entry_date <= ".db_escape($end_date)." AND 
                    deducted_deficit_time > '00:00:00' AND 
                    shift_id > 0 AND 
                    deficit_status_code IN('A', 'L')"; 

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


function update_worktime_deficit_record_status($rec_id, $status_code, $payroll_run_id, $deficit_amount)
{

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

        $sql = "UPDATE ".TB_PREF."attendance 
                SET 
                    deficit_amount=".db_escape($deficit_amount).",
                    deficit_status_code=".db_escape($status_code).",
                    deficit_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,"Worktime deficit record could not be updated");
            
}

function insert_payroll_employee_worktime_deficit_summary($payroll_run_id, $employee_code,
                        $component, $calculated_time, $comment = "")
{        
	$sql = "INSERT INTO ".TB_PREF."payroll_worktime_deficit_summary (
                    payroll_run_id,employee_code,  
                    component, 
                    calculated_time, deducted_time, 
                    comment, status_code) 
                VALUES ("
                    .db_escape($payroll_run_id).", ".db_escape($employee_code).", "
                    .db_escape($component). ", "
                    .db_escape($calculated_time).", ".db_escape($calculated_time).", "
                    .db_escape($comment).", ".db_escape(ATD_NEW).")";
        
        db_query($sql,"Payroll employee worktime deficit summary could not be inserted");


}

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

    $result = db_query($sql, "Attendance records summary could not be deleted");
    
    return db_fetch($result);
}

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

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


?>