<?php

function get_fi_group_company_payroll_runs($db_name, $payroll_type_id, $cutoff_date, $batch_size)
{
    $sql = "SELECT pr.* 
            FROM ".$db_name.".".TB_PREF."payroll_runs pr  
            WHERE tran_date <=".db_escape($cutoff_date)." 
                AND payroll_id = ".db_escape($payroll_type_id)." 
                AND fi_batch_no = 0 
            ORDER BY tran_date 
            LIMIT ".$batch_size;

    $result = db_query($sql, "get_fi_group_company_payrolls");
    return $result;
}

function get_fi_group_company_processed_payroll_runs_by_payroll_type($db_name, $payroll_type_code, $cutoff_date, $batch_size)
{
    $sql = "SELECT pr.*,
                    ptn.value_code payroll_type_code 
            FROM ".$db_name.".".TB_PREF."payroll_runs pr  
                JOIN ".$db_name.".".TB_PREF."list_values ptn 
                    ON pr.payroll_id = ptn.id 
            WHERE pr.tran_date <=".db_escape($cutoff_date)." 
                AND ptn.value_code = ".db_escape($payroll_type_code)." 
                AND pr.fi_batch_no = 0 
                AND pr.status = 'R' 
            ORDER BY tran_date 
            LIMIT ".$batch_size;

    $result = db_query($sql, "get_fi_group_company_payrolls");
    return $result;
}

function get_fi_payroll_summaries($db_name, $payroll_type_id, $payroll_run_id)
{
    $sql = "SELECT prd.id,
                    prd.component,
                    prd.value,
                    lv.value_name component_name,
                    pa.debit_code,
                    pa.credit_code 
            FROM ".$db_name.".".TB_PREF."payroll_run_details prd 
                JOIN ".$db_name.".".TB_PREF."list_values lv 
                    ON lv.value_code = prd.component 
                JOIN ".$db_name.".".TB_PREF."lists l 
                    ON lv.list_code = l.list_code 
                    AND l.list_type = 'PAYROLL'   
                LEFT JOIN ".$db_name.".".TB_PREF."payroll_accounts pa  
                    ON pa.payroll_id = ".db_escape($payroll_type_id)." 
                    AND pa.component_id = lv.id 
            WHERE prd.payroll_run_id = ".db_escape($payroll_run_id)." 
                AND prd.employee_code = ''";
                    

    return db_query($sql, "get_fi_payroll_summaries");

}

function get_fi_payroll_mismatching_summaries($db_name, $payroll_type_code, $payroll_run_id)
{
//    $sql = "SELECT prd.id,
//                    prd.component,
//                    prd.value,
//                    lv.value_name component_name,
//                    pa.debit_code,
//                    pa.credit_code 
//            FROM ".$db_name.".".TB_PREF."payroll_run_details prd 
//                JOIN ".$db_name.".".TB_PREF."list_values lv 
//                    ON lv.value_code = prd.component 
//                JOIN ".$db_name.".".TB_PREF."lists l 
//                    ON lv.list_code = l.list_code 
//                    AND l.list_type = 'PAYROLL'   
//                LEFT JOIN ".$db_name.".".TB_PREF."payroll_accounts pa  
//                    ON pa.payroll_id = ".db_escape($payroll_type_id)." 
//                    AND pa.component_id = lv.id 
//            WHERE prd.payroll_run_id = ".db_escape($payroll_run_id)." 
//                AND prd.employee_code = ''";
//                    
//
//    $result = db_query($sql, "get_fi_payroll_summaries");
    return 0;

}


function get_fi_payroll_department_summaries($db_name, $payroll_type_id, $payroll_run_id, $department_code)
{
    $sql = "SELECT prd.id,
                    prd.component,
                    prd.value,
                    lv.value_name component_name,
                    pa.debit_code,
                    pa.credit_code 
            FROM ".$db_name.".".TB_PREF."payroll_run_details prd 
                JOIN ".$db_name.".".TB_PREF."list_values lv 
                    ON lv.value_code = prd.component 
                JOIN ".$db_name.".".TB_PREF."lists l 
                    ON lv.list_code = l.list_code 
                    AND l.list_type = 'PAYROLL'   
                LEFT JOIN ".$db_name.".".TB_PREF."payroll_accounts pa  
                    ON pa.payroll_id = ".db_escape($payroll_type_id)." 
                    AND pa.component_id = lv.id 
            WHERE prd.payroll_run_id = ".db_escape($payroll_run_id)." 
                AND prd.employee_code = ''";
                    

    return db_query($sql, "get_fi_payroll_summaries");

}

function get_fi_payroll_department_summary_value($db_name, $payroll_run_id, $department_code, $component_code)
{
    $sql = "SELECT pds.* 
            FROM ".$db_name.".".TB_PREF."payroll_departments_summaries pds  
            WHERE pds.payroll_run_id = ".db_escape($payroll_run_id)." 
                    AND pds.department_code = ".db_escape($department_code)." 
                    AND pds.component = ".db_escape($component_code);                    

    $result = db_query($sql, "get_fi_payroll_department_summary_value");
    
    return db_fetch($result);

}

