<?php

function get_summary_projects_departmets_total($month_id, $department_codes_in, $value_types_in)
{
    $sql = "SELECT SUM(s.value) total,
                    lv.value_code 
            FROM ".TB_PREF."monthly_summary_projects s
                JOIN ".TB_PREF."list_values lv 
                    ON s.department_id = lv.id 
            WHERE s.month_id = ". db_escape($month_id)."  
                AND lv.value_code IN (".$department_codes_in.") 
                AND s.value_type IN (".$value_types_in.")";
    
    $result = db_query($sql);
    return db_result_value($result, 'total', 0);
}

function get_summary_projects_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_projects_total($month_id, $project_ids_in, $value_types_in)
{
    $sql = "SELECT SUM(s.value) total 
            FROM ".TB_PREF."monthly_summary_projects s 
            WHERE s.month_id = ". db_escape($month_id)."  
                AND s.project_id IN (".$project_ids_in.") 
                AND s.value_type IN (".$value_types_in.")";
    
    $result = db_query($sql);
    return db_result_value($result, 'total', 0);
}


function get_summary_projects_month_range_project_value($from_month_display_order, $to_month_display_order, $project_ids_in, $value_types_in)
{
    $sql = "SELECT SUM(s.value) total 
            FROM ".TB_PREF."monthly_summary_projects s 
                JOIN ".TB_PREF."months m 
                    ON s.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 s.project_id IN (".$project_ids_in.") 
                AND s.value_type IN (".$value_types_in.") ";
    
    $result = db_query($sql);
    return db_result_value($result, 'total', 0);
}

function get_summary_projects_month_range_department_value($from_month_display_order, $to_month_display_order, $department_ids_in, $value_types_in)
{
    $sql = "SELECT SUM(s.value) total 
            FROM ".TB_PREF."monthly_summary_projects s 
                JOIN ".TB_PREF."months m 
                    ON s.month_id = m.id                 
                JOIN ".TB_PREF."projects_master pm 
                    ON s.project_id = pm.project_id                  
            WHERE m.display_order >= ". db_escape($from_month_display_order)."  
                AND m.display_order <= ". db_escape($to_month_display_order)."  
                AND pm.department_id IN (".$department_ids_in.") 
                AND s.value_type IN (".$value_types_in.") ";
    
    $result = db_query($sql);
    return db_result_value($result, 'total', 0);
}

function get_summary_project_types_month_range_department_value($from_month_display_order, $to_month_display_order, $department_ids_in, $value_types_in, $project_types_in)
{
    $sql = "SELECT SUM(s.value) total 
            FROM ".TB_PREF."monthly_summary_projects s 
                JOIN ".TB_PREF."months m 
                    ON s.month_id = m.id                 
                JOIN ".TB_PREF."projects_master pm 
                    ON s.project_id = pm.project_id                  
            WHERE m.display_order >= ". db_escape($from_month_display_order)."  
                AND m.display_order <= ". db_escape($to_month_display_order)."  
                AND pm.department_id IN (".$department_ids_in.") 
                AND s.value_type IN (".$value_types_in.")  
                AND pm.type_id IN  (".$project_types_in.") "; 
    
    $result = db_query($sql);
    return db_result_value($result, 'total', 0);
}


function get_summary_project_types_month_range_project_value($from_month_display_order, $to_month_display_order, $project_ids_in, $value_types_in, $project_types_in)
{
    $sql = "SELECT SUM(s.value) total 
            FROM ".TB_PREF."monthly_summary_projects s 
                JOIN ".TB_PREF."months m 
                    ON s.month_id = m.id                 
                JOIN ".TB_PREF."projects_master pm 
                    ON s.project_id = pm.project_id                  
            WHERE m.display_order >= ". db_escape($from_month_display_order)."  
                AND m.display_order <= ". db_escape($to_month_display_order)."  
                AND pm.project_id IN (".$project_ids_in.") 
                AND s.value_type IN (".$value_types_in.")  
                AND pm.type_id IN  (".$project_types_in.") "; 
    
    $result = db_query($sql);
    return db_result_value($result, 'total', 0);
}


function get_summary_projects_month_range_company_value($from_month_display_order, $to_month_display_order, $value_types_in){
    
    $sql = "SELECT SUM(s.value) total 
            FROM ".TB_PREF."monthly_summary_projects s 
                JOIN ".TB_PREF."months m 
                    ON s.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 s.value_type IN (".$value_types_in.") ";
    
    $result = db_query($sql);
    return db_result_value($result, 'total', 0);
}


function get_summary_project_types_month_range_company_value($from_month_display_order, $to_month_display_order, $value_types_in, $project_types_in){
    
    $sql = "SELECT SUM(s.value) total 
            FROM ".TB_PREF."monthly_summary_projects s 
                JOIN ".TB_PREF."months m 
                    ON s.month_id = m.id                                
                JOIN ".TB_PREF."projects_master pm 
                    ON s.project_id = pm.project_id                                
            WHERE m.display_order >= ". db_escape($from_month_display_order)."  
                AND m.display_order <= ". db_escape($to_month_display_order)."  
                AND s.value_type IN (".$value_types_in.") 
                AND pm.type_id IN  (".$project_types_in.") "; 
    
    $result = db_query($sql);
    return db_result_value($result, 'total', 0);
}









