<?php

function get_summary_payrolls_value_total($month_id, $company_codes_in, $department_codes_in, $salary_components_in)
{
    $sql = "SELECT SUM(value) total   
            FROM ".TB_PREF."monthly_summary_payrolls  
            WHERE month_id = ". db_escape($month_id)."  AND 
                    company_code IN (".$company_codes_in.") AND 
                    department_code IN (".$department_codes_in.") AND 
                    value_type IN (".$salary_components_in.")";
    
    $result = db_query($sql);
    return db_result_value($result, 'total', 0);
}

function get_summary_payrolls_departments($month_id, $company_codes_in, $salary_components_in)
{
    $sql = "SELECT DISTINCT department_code 
            FROM ".TB_PREF."monthly_summary_payrolls  
            WHERE month_id = ". db_escape($month_id)."  AND 
                    company_code IN (".$company_codes_in.") AND 
                    value_type IN (".$salary_components_in.")";
    
    return db_query($sql);
    
}

function get_summary_payrolls_month_range_departments($company_codes_in, $salary_components_in, $from_month_display_order, $to_month_display_order)
{
    $sql = "SELECT DISTINCT p.department_code 
            FROM ".TB_PREF."monthly_summary_payrolls p  
                JOIN ".TB_PREF."months m   
                    ON p.month_id = m.id 
            WHERE m.display_order >= ". db_escape($from_month_display_order)."  AND 
                    m.display_order <= ". db_escape($to_month_display_order)."  AND 
                    company_code IN (".$company_codes_in.") AND 
                    value_type IN (".$salary_components_in.")";
    
    return db_query($sql);
    
}

function get_summary_payrolls_month_range_company_values($company_codes_in, $value_types_in, $from_month_display_order, $to_month_display_order)
{
    $sql = "SELECT p.department_code, SUM(p.value) total    
            FROM ".TB_PREF."monthly_summary_payrolls p                 
                JOIN ".TB_PREF."months m   
                    ON p.month_id = m.id 
            WHERE m.display_order >= ". db_escape($from_month_display_order)."  AND 
                    m.display_order <= ". db_escape($to_month_display_order)."  AND 
                    p.company_code IN (".$company_codes_in.") AND 
                    p.value_type IN (".$value_types_in.") 
            GROUP BY p.department_code 
            ORDER BY total DESC ";
    
    return db_query($sql);
    
}

function get_summary_payrolls_month_range_department_total($company_codes_in, $department_codes_in, $value_types_in, $from_month_display_order, $to_month_display_order)
{
    $sql = "SELECT SUM(p.value) total    
            FROM ".TB_PREF."monthly_summary_payrolls p                 
                JOIN ".TB_PREF."months m   
                    ON p.month_id = m.id 
            WHERE m.display_order >= ". db_escape($from_month_display_order)."  AND 
                    m.display_order <= ". db_escape($to_month_display_order)."  AND 
                    p.company_code IN (".$company_codes_in.") AND 
                    p.department_code IN (".$department_codes_in.") AND 
                    p.value_type IN (".$value_types_in.") 
            ORDER BY total DESC ";
    
    $result = db_query($sql);
    return db_result_value($result, "total");
    
}


function get_summary_payrolls_month_range_company_total($company_codes_in, $value_types_in, $from_month_display_order, $to_month_display_order)
{
    $sql = "SELECT SUM(p.value) total    
            FROM ".TB_PREF."monthly_summary_payrolls p                 
                JOIN ".TB_PREF."months m   
                    ON p.month_id = m.id 
            WHERE m.display_order >= ". db_escape($from_month_display_order)."  AND 
                    m.display_order <= ". db_escape($to_month_display_order)."  AND 
                    p.company_code IN (".$company_codes_in.") AND 
                    p.value_type IN (".$value_types_in.") ";
    
    $result = db_query($sql);
    return db_result_value($result, "total");
    
}