<?php

function delete_monthly_summary_payrolls($month_id, $calculation_type)
{
    $sql = "DELETE FROM ".TB_PREF."monthly_summary_payrolls  
            WHERE month_id = ".db_escape($month_id)." 
                AND calculation_type = ".db_escape($calculation_type);

    db_query($sql,"delete_monthly_summary_payrolls");
}


function get_payroll_id_from_group_company($db_name, $payroll_code)
{
    $sql="SELECT id                
            FROM ".$db_name.".".TB_PREF."list_values                      
            WHERE list_code = 'PAYROLLS'  
                AND value_code = ".db_escape($payroll_code);

    $result = db_query($sql,"get_monthly_summary_projects_next_project");
    $row = db_fetch($result);
    return $row['id'];
}

function get_group_company_department_recruitments_count($db_name, $department, $from_date, $to_date)
{
    $sql = "SELECT COUNT(DISTINCT em.employee_code) recruitments  
            FROM  ".$db_name.".".TB_PREF."employee_master em 
                JOIN ".$db_name.".".TB_PREF."employee_departments ed 
                    ON em.employee_code = ed.employee_code                     
            WHERE em.date_of_joining >= ".db_escape($from_date)."  
                AND em.date_of_joining <= ".db_escape($to_date)."  
                AND department_id = ".$department['id'];
    
    $result = db_query($sql,"get_group_company_department_new_employee_count");
    
    return db_result_value($result, 'recruitments');
    
}


function get_group_company_department_resignations_count($db_name, $department, $from_date, $to_date)
{
    $sql = "SELECT COUNT(DISTINCT em.employee_code) resignations  
            FROM  ".$db_name.".".TB_PREF."employee_master em 
                JOIN ".$db_name.".".TB_PREF."employee_departments ed 
                    ON em.employee_code = ed.employee_code                     
            WHERE em.date_of_resignation >= ".db_escape($from_date)."  
                AND em.date_of_resignation <= ".db_escape($to_date)."  
                AND em.resigned = 1 
                AND department_id = ".$department['id'];
    
    $result = db_query($sql,"get_group_company_department_employee_resignation_count");
    
    return db_result_value($result, 'resignations');
    
}



function get_group_company_department_employees($db_name, $department, $date)
{
    $sql = "SELECT DISTINCT employee_code 
            FROM  ".$db_name.".".TB_PREF."employee_departments ed 
            WHERE ed.start_date <= ".db_escape($date)."  
                AND ed.end_date >= ".db_escape($date)."  
                AND department_id = ".$department['id'];
    
    return db_query($sql,"get_group_cpmpany_department_employees");
    
}

function get_group_company_employees_total_salary_cost($db_name, $payroll_run_id, $employee_codes_in, $components_in)
{
    $sql = "SELECT SUM(value) total_salary   
            FROM  ".$db_name.".".TB_PREF."payroll_run_details 
            WHERE payroll_run_id = ".db_escape($payroll_run_id)."   
                    AND employee_code IN (".$employee_codes_in.")  
                    AND component IN (".$components_in.")";  
    
    $result = db_query($sql,"get_group_company_employees_total_salary_cost");
    return db_result_value($result, 'total_salary');
    
}

function get_group_company_department_salary_paid_employee_count($db_name, $payroll_run_id, $employee_codes_in, $components_in)
{
    $sql = "SELECT COUNT(DISTINCT employee_code) employeee_count   
            FROM  ".$db_name.".".TB_PREF."payroll_run_details 
            WHERE payroll_run_id = ".db_escape($payroll_run_id)."   
                    AND employee_code IN (".$employee_codes_in.")  
                    AND component IN (".$components_in.")";  
    
    $result = db_query($sql,"get_group_company_department_salary_paid_employee_count");
    return db_result_value($result, 'employeee_count');
    
}


function get_group_company_payroll_run_record($db_name, $payrol_id, $mid_date)
{
    $sql = "SELECT *  
            FROM  ".$db_name.".".TB_PREF."payroll_runs 
            WHERE payroll_id = ".db_escape($payrol_id)."    
                    AND period_start_date < ".db_escape($mid_date)."  
                    AND period_end_date > ".db_escape($mid_date);
    
    $result = db_query($sql,"get_group_company_payroll_run_record");
    return db_fetch($result);
}

function add_to_monthly_summary_payroll($month_id, $calculation_type, $company_code, $department_code, $value_type, $value, $calculation_run_id)
{
    if($value == 0){
        return;
    }
	$sql = "INSERT INTO ".TB_PREF."monthly_summary_payrolls 
                    (month_id, company_code, department_code, 
                    value_type, calculation_type, 
                    value, calculation_run_id) 
		VALUES( ".db_escape($month_id).",".db_escape($company_code).",".db_escape($department_code).","
                    .db_escape($value_type).",".db_escape($calculation_type).","
                    .db_escape($value).",".db_escape($calculation_run_id).")";

	db_query($sql,"add_to_monthly_summary_payroll");
}