<?php

function delete_monthly_summary_project_work($month_id, $company_code)
{
    $sql = "DELETE FROM ".TB_PREF."monthly_summary_project_work  
            WHERE month_id = ".db_escape($month_id)." 
                AND company_code = ".db_escape($company_code);

    db_query($sql,"delete_monthly_summary_payrolls");
}

function get_emplyee_default_previous_project($company_code, $employee_code, $from_date)
{
    $sql = "SELECT ep.*,
                    er.rate 
            FROM ".TB_PREF."employee_projects ep 
                JOIN ".TB_PREF."employee_rates er  
                    ON ep.rate_category_id = er.id 
            WHERE ep.company_code = ".  db_escape($company_code)." 
                AND ep.employee_code = ".  db_escape($employee_code)." 
                AND ep.start_date < ".  db_escape($from_date)." 
            ORDER BY ep.start_date LIMIT 1";
    
    $result = db_query($sql, "get_emplyee_default_previous_project");
    return db_fetch($result);
}

function get_emplyee_default_projects($company_code, $employee_code, $from_date)
{
    $sql = "SELECT ep.*,
                    er.rate 
            FROM ".TB_PREF."employee_projects ep 
                JOIN ".TB_PREF."employee_rates er  
                    ON ep.rate_category_id = er.id 
            WHERE ep.company_code = ".  db_escape($company_code)." 
                AND ep.employee_code = ".  db_escape($employee_code)." 
                AND ep.start_date >= ".  db_escape($from_date)." 
            ORDER BY ep.start_date";
    
    return  db_query($sql, "get_emplyee_default_projects");
}


function get_employee_project_works($company_code, $employee_code, $display_from_date, $display_to_date)
{
    $sql = "SELECT epw.*,
                    pm.description project_description 
            FROM ".TB_PREF."employee_project_works epw 
                JOIN ".TB_PREF."projects_master pm  
                    ON epw.project_id = pm.project_id 
            WHERE epw.company_code = ".db_escape($company_code)." 
                AND epw.employee_code = ".db_escape($employee_code)." 
                AND epw.to_date >= ".db_escape($display_from_date)." 
                AND epw.from_date <= ".db_escape($display_to_date);

    $sql .= " ORDER By epw.from_date" ;
                    
    return db_query($sql, "get_employee_project_works");
}


function get_employee_project_work_leave_details($db_name, $employee_code, $from_date, $to_date)
{
    $sql = "SELECT ld.* 
            FROM ".$db_name.".".TB_PREF."leave_details ld  
                JOIN ".$db_name.".".TB_PREF."leaves l   
                    ON ld.leave_id = l.id  
            WHERE l.employee_code = ".db_escape($employee_code)." 
                AND ld.date >= ".db_escape($from_date)." 
                AND ld.date <= ".db_escape($to_date)." 
                AND l.status_code IN ('Y', 'A', 'L', 'S', 'D')"; 
    $sql .= " ORDER By ld.date" ;
                    
    return db_query($sql, "get_employee_project_work_leave_details");
    
}


function add_to_monthly_summary_project_work($month_id, $company_code, $employee_code, $project_id, $days, 
                                            $rate_category_id, $rate, $comment, $calculation_run_id)
{
	$sql = "INSERT INTO ".TB_PREF."monthly_summary_project_work(
                        month_id, company_code, employee_code, 
                        project_id, days, 
                        rate_category_id, rate, 
                        comment, calculation_run_id) 
                VALUES (".db_escape($month_id).",".db_escape($company_code).",".db_escape($employee_code).",
                        ".db_escape($project_id).",".db_escape($days).",
                        ".db_escape($rate_category_id).",".db_escape($rate).",
                        ".db_escape($comment).",".db_escape($calculation_run_id).")";

	db_query($sql,"add_to_monthly_summary_project_work");
}


