<?php

function get_departments_components_summary($payroll_run_id) {
    $sql = "SELECT pds.*, 
                    com.value_name process_name, 
                    dep.value_name department_name
            FROM ".TB_PREF."payroll_departments_summaries pds 
                JOIN ".TB_PREF."list_values dep 
                    ON dep.value_code = pds.department_code 
                    AND dep.list_code = 'DEPARTMENTS' 
                JOIN ".TB_PREF."list_values com  
                    ON com.value_code = pds.component  
            WHERE pds.payroll_run_id = ".db_escape($payroll_run_id)." 
                AND pds.department_code <> ''";
  
    return db_query($sql, "get_departments_components_summary");
}

function get_department_component_total_by_payroll_tran_date($department_id, $tran_date, $payroll_run_id, $component) 
{
    $sql = "SELECT SUM(pay.value) component_total 
            FROM ".TB_PREF."payroll_run_details pay 
                JOIN ".TB_PREF."employee_departments dep 
                    ON pay.employee_code = dep.employee_code 
            WHERE pay.payroll_run_id = ".db_escape($payroll_run_id)." 
                AND pay.component = ".db_escape($component)." 
                AND pay.employee_code <> '' 
                AND dep.department_id = ".db_escape($department_id)." 
                AND dep.start_date <= ".db_escape($tran_date)." 
                AND dep.end_date >= ".db_escape($tran_date);
  
    $result = db_query($sql, "get_department_component_total_by_payroll_tran_date");
    return db_fetch($result);
}

function get_all_departments_component_total_by_payroll_run_id($payroll_run_id, $component) {
    $sql = "SELECT SUM(value) all_departments_component_total 
            FROM ".TB_PREF."payroll_departments_summaries  
            WHERE payroll_run_id = ".db_escape($payroll_run_id)." 
                AND component = ".db_escape($component)." 
                AND department_code <> ''";
  
    $result = db_query($sql, "get_all_departments_component_total_by_payroll_run_id");
    return db_fetch($result);
}

function get_all_departments_component_total_by_component($payroll_run_id, $component) {
    $sql = "SELECT value 
            FROM ".TB_PREF."payroll_departments_summaries  
            WHERE payroll_run_id = ".db_escape($payroll_run_id)." 
                AND component = ".db_escape($component)." 
                AND department_code = ''";
  
    $result = db_query($sql, "get_all_departments_component_total_by_component");
    return db_fetch($result);
}

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

    db_query($sql, "delete_payroll_departments_summary"); 
}

function insert_payroll_departments_summaries($payroll_run_id, $department_code, $component, $value) {        
    $sql = "INSERT INTO ".TB_PREF."payroll_departments_summaries (
                        payroll_run_id, department_code,  
                        component, value) 
            VALUES (" .db_escape($payroll_run_id).", ".db_escape($department_code).", "
                      .db_escape($component). ", ".db_escape($value).")";

    db_query($sql, "insert_payroll_departments_summaries");
}

function select_departments_components_summary($payroll_run_id, $db_name='')
{
   if((strlen($db_name) > 1)){
        $db = $db_name.".";
    }
    else{
        $db = '';
    } 

    $sql = "SELECT pds.*, 
                    com.value_name process_name, 
                    dep.value_name department_name
            FROM ".$db.TB_PREF."payroll_departments_summaries pds 
                JOIN ".$db.TB_PREF."list_values dep 
                    ON dep.value_code = pds.department_code 
                    AND dep.list_code = 'DEPARTMENTS' 
                JOIN ".$db.TB_PREF."list_values com  
                    ON com.value_code = pds.component  
            WHERE pds.payroll_run_id = ".db_escape($payroll_run_id)." 
                AND pds.department_code <> ''";
  
    return db_query($sql, "select_departments_components_summary");
}