<?php
/**********************************************************************
***********************************************************************/
function get_unapproved_salary_adjustments()
{  
	$sql1 = "SELECT CONCAT('A',sa.id) rec_id, sa.employee_code, 
                        'Addition' as type, sa.salary_addition amount,
                        sa.due_date, sa.created_on, sa.comments, 
                        em.initials, em.first_name, em.last_name,
                        sa.status_code, st.value_name status_name,
                        ty.value_name type_name 
                FROM ".TB_PREF."salary_additions sa,
                        ".TB_PREF."employee_master em, 
                        ".TB_PREF."list_values st,
                        ".TB_PREF."list_values ty  
                WHERE status_code IN ('N', 'U', 'O') AND 
                        sa.employee_code = em.employee_code AND 
                        sa.status_code = st.value_code AND st.list_code = 'RECORD_STATUS' AND 
                        sa.type_id = ty.id"; 
                        
        $sql2 = "SELECT CONCAT('D',sd.id) rec_id, sd.employee_code, 
                        'Deduction' as type, -sd.salary_deduction amount,
                        sd.due_date, sd.created_on, sd.comments, 
                        em.initials, em.first_name, em.last_name,
                        sd.status_code, st.value_name status_name,
                        ty.value_name type_name   
                FROM ".TB_PREF."salary_deductions sd,
                        ".TB_PREF."employee_master em, 
                        ".TB_PREF."list_values st,
                        ".TB_PREF."list_values ty   
                WHERE status_code IN ('N', 'U', 'O') AND 
                        sd.employee_code = em.employee_code AND 
                        sd.status_code = st.value_code AND st.list_code = 'RECORD_STATUS' AND 
                        sd.type_id = ty.id"; 
        
        $sql3 = "SELECT CONCAT('V',sadv.id) rec_id, sadv.employee_code, 
                        'Deduction' as type, -sadv.salary_advance amount,
                        sadv.due_date, sadv.created_on, sadv.comments, 
                        em.initials, em.first_name, em.last_name,
                        sadv.status_code, st.value_name status_name,
                        ty.value_name type_name   
                FROM ".TB_PREF."salary_advances sadv,
                        ".TB_PREF."employee_master em, 
                        ".TB_PREF."list_values st,
                        ".TB_PREF."list_values ty   
                WHERE status_code IN ('N', 'U', 'O') AND 
                        sadv.employee_code = em.employee_code AND 
                        sadv.status_code = st.value_code AND st.list_code = 'RECORD_STATUS' AND 
                        sadv.type_id = ty.id"; 
        
        $sql = $sql1." UNION ".$sql2." UNION ".$sql3." ORDER BY due_date, employee_code, rec_id ";
	
	$result = db_query($sql,"Could not retrieve unapproved salary adjustments");	
        
        return $result;
}


function get_unapproved_salary_additions()
{  
	$sql = "SELECT CONCAT('A',sa.id) rec_id, sa.employee_code, 
                        'Addition' as type, sa.salary_addition amount,
                        sa.due_date, sa.created_on, sa.comments, 
                        em.initials, em.first_name, em.last_name,
                        sa.status_code, st.value_name status_name,
                        ty.value_name type_name,
                        sa.id 
                FROM ".TB_PREF."salary_additions sa,
                        ".TB_PREF."employee_master em, 
                        ".TB_PREF."list_values st,
                        ".TB_PREF."list_values ty  
                WHERE status_code IN ('N', 'U', 'O') AND 
                        sa.employee_code = em.employee_code AND 
                        sa.status_code = st.value_code AND st.list_code = 'RECORD_STATUS' AND 
                        sa.type_id = ty.id"; 
        $sql .= " ORDER BY due_date, employee_code, rec_id ";
        
        
       	$result = db_query($sql,"Could not retrieve unapproved salary additions");	
        
        return $result;
}

function get_unapproved_salary_deductions()
{
     $sql = "SELECT CONCAT('D',sd.id) rec_id, sd.employee_code, 
                        'Deduction' as type, -sd.salary_deduction amount,
                        sd.due_date, sd.created_on, sd.comments, 
                        em.initials, em.first_name, em.last_name,
                        sd.status_code, st.value_name status_name,
                        ty.value_name type_name,
                        sd.id 
                FROM ".TB_PREF."salary_deductions sd,
                        ".TB_PREF."employee_master em, 
                        ".TB_PREF."list_values st,
                        ".TB_PREF."list_values ty   
                WHERE status_code IN ('N', 'U', 'O') AND 
                        sd.employee_code = em.employee_code AND 
                        sd.status_code = st.value_code AND st.list_code = 'RECORD_STATUS' AND 
                        sd.type_id = ty.id"; 
      $sql .= " ORDER BY due_date, employee_code, rec_id ";
        
        
      $result = db_query($sql,"Could not retrieve unapproved salary deductions");	
        
      return $result;
    
    
    
}

function get_unapproved_salary_advances()
{
    $sql = "SELECT CONCAT('V',sadv.id) rec_id, sadv.employee_code, 
                        'Deduction' as type, -sadv.salary_advance amount,
                        sadv.due_date, sadv.created_on, sadv.comments, 
                        em.initials, em.first_name, em.last_name,
                        sadv.status_code, st.value_name status_name,
                        ty.value_name type_name,
                        sadv.id 
                FROM ".TB_PREF."salary_advances sadv,
                        ".TB_PREF."employee_master em, 
                        ".TB_PREF."list_values st,
                        ".TB_PREF."list_values ty   
                WHERE status_code IN ('N', 'U', 'O') AND 
                        sadv.employee_code = em.employee_code AND 
                        sadv.status_code = st.value_code AND st.list_code = 'RECORD_STATUS' AND 
                        sadv.type_id = ty.id";
    
    $sql .= " ORDER BY due_date, employee_code, rec_id ";
        
        
      $result = db_query($sql,"Could not retrieve unapproved salary advances");	
        
      return $result;
}

function approve_salary_addition($row_id, $status_code, $approval_status_array = array()) {
    $approved_by = $_SESSION['wa_current_user']->user;
    $approved_on = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."salary_additions 
                SET status_code = ".db_escape($status_code).", 
                    approved_by = ".db_escape($approved_by).", 
                    approved_on = ".db_escape($approved_on)." 
                WHERE id = ".$row_id; 
         
        if (count($approval_status_array) > 0) {
            $sql .= " AND status_code IN (".implode(',', $approval_status_array).")";
        }
	
	db_query($sql, "Could not update the salary addition");	
        return db_num_affected_rows();		
}

function approve_salary_deduction($row_id, $status_code, $approval_status_array = array()) {
    $approved_by = $_SESSION['wa_current_user']->user;
    $approved_on = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."salary_deductions 
                SET status_code = ".db_escape($status_code).", 
                    approved_by = ".db_escape($approved_by).", 
                    approved_on = ".db_escape($approved_on)." 
                WHERE id = ".$row_id;
        
        if (count($approval_status_array) > 0) {
            $sql .= " AND status_code IN (".implode(',', $approval_status_array).")";
        }
	
	db_query($sql,"Could not update the salary deduction");	
        return db_num_affected_rows();		
}

function approve_salary_advance($row_id, $status_code, $approval_status_array = array()) {
    $approved_by = $_SESSION['wa_current_user']->user;
    $approved_on = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."salary_advances 
                SET status_code = ".db_escape($status_code).", 
                    approved_by = ".db_escape($approved_by).", 
                    approved_on = ".db_escape($approved_on)." 
                WHERE id = ".$row_id;
        
        if (count($approval_status_array) > 0) {
            $sql .= " AND status_code IN (".implode(',', $approval_status_array).")";
        }
	
	db_query($sql,"Could not update the salary advance");	
        return db_num_affected_rows();		
}

function get_approved_salary_additions($sql_only=false) {  
    $sql = "(SELECT CONCAT('A',sa.id) rec_id, sa.employee_code, 
                    'Addition' as type, sa.salary_addition amount,
                    sa.due_date, sa.created_on, sa.comments, 
                    em.initials, em.first_name, em.last_name,
                    sa.status_code, st.value_name status_name,
                    ty.value_name type_name,  
                    sa.id 
            FROM ".TB_PREF."salary_additions sa,
                    ".TB_PREF."employee_master em, 
                    ".TB_PREF."list_values st,
                    ".TB_PREF."list_values ty  
            WHERE status_code IN ('A', 'L') AND 
                    sa.employee_code = em.employee_code AND 
                    sa.status_code = st.value_code AND st.list_code = 'RECORD_STATUS' AND 
                    sa.type_id = ty.id 
            ORDER BY due_date, employee_code, rec_id )";
        
        if ($sql_only === false) {
            return db_query($sql, "get_approved_salary_additions");
        } else {
            return $sql;
        }
}

function get_approved_salary_deductions($sql_only=false) {
    $sql = "(SELECT CONCAT('D',sd.id) rec_id, sd.employee_code, 
                    'Deduction' as type, -sd.salary_deduction amount,
                    sd.due_date, sd.created_on, sd.comments, 
                    em.initials, em.first_name, em.last_name,
                    sd.status_code, st.value_name status_name,
                    ty.value_name type_name,
                    sd.id 
            FROM ".TB_PREF."salary_deductions sd,
                    ".TB_PREF."employee_master em, 
                    ".TB_PREF."list_values st,
                    ".TB_PREF."list_values ty   
            WHERE status_code IN ('A', 'L') AND 
                    sd.employee_code = em.employee_code AND 
                    sd.status_code = st.value_code AND st.list_code = 'RECORD_STATUS' AND 
                    sd.type_id = ty.id 
            ORDER BY due_date, employee_code, rec_id )";
        
        if ($sql_only === false) {
            return db_query($sql, "get_approved_salary_deductions");
        } else {
            return $sql;
        }	
}

function get_approved_salary_advances($sql_only=false) {
    $sql = "(SELECT CONCAT('V',sadv.id) rec_id, sadv.employee_code, 
                    'Advance' as type, -sadv.salary_advance amount,
                    sadv.due_date, sadv.created_on, sadv.comments, 
                    em.initials, em.first_name, em.last_name,
                    sadv.status_code, st.value_name status_name,
                    ty.value_name type_name,
                    sadv.id 
            FROM ".TB_PREF."salary_advances sadv,
                    ".TB_PREF."employee_master em, 
                    ".TB_PREF."list_values st,
                    ".TB_PREF."list_values ty   
            WHERE status_code IN ('A', 'L') AND 
                    sadv.employee_code = em.employee_code AND 
                    sadv.status_code = st.value_code AND st.list_code = 'RECORD_STATUS' AND 
                    sadv.type_id = ty.id 
            ORDER BY due_date, employee_code, rec_id )";
        
        if ($sql_only === false) {
            return db_query($sql, "get_approved_salary_advances");
        } else {
            return $sql;
        }    
}

function get_approved_salary_adjustments() {  
    $sql1 = get_approved_salary_additions(true);

    $sql2 = get_approved_salary_deductions(true);

    $sql3 = get_approved_salary_advances(true);
        
        $sql = $sql1." UNION ".$sql2." UNION ".$sql3." ORDER BY due_date, employee_code, rec_id ";
	
	return db_query($sql, "get_approved_salary_adjustments");	
}

?>