<?php
/**********************************************************************
***********************************************************************/



function get_sql_for_attendance_inquiry($from_date, $to_date, $employee_code, $ot_only, $status_code)
{
    global $order_number, $selected_stock_item;;

	$sql = "SELECT 
                        wtc.date,
                        CONCAT(a.employee_code,' - ', em.last_name) employee,
                        s.shift_name,
                        SUBSTRING(a.from_date_time1,12,5),
                        SUBSTRING(a.to_date_time1,12,5),
                        a.counted_duration1,
                        a.split_shift,
                        SUBSTRING(a.from_date_time2,12,5) in2,
                        SUBSTRING(a.to_date_time2,12,5) out2,
                        a.counted_duration2,
                        SUBSTRING(a.granted_ot_time,1,5) ot_time,
                        rt.value_name table_name,
                        sc.value_name status_name,
                        a.id,
                        a.employee_code 
		FROM ".TB_PREF."attendance a,
                        ".TB_PREF."employee_master em,
                        ".TB_PREF."shifts s,
                        ".TB_PREF."list_values rt,
                        ".TB_PREF."list_values sc,
                        ".TB_PREF."work_time_calendars wtc  
		WHERE a.employee_code = em.employee_code 
                        AND a.shift_id = s.id 
                        AND a.rate_table_id = rt.id 
                        AND (a.ot_status_code = sc.value_code AND sc.list_code = 'ATTENDANCE_STATUS' OR 
                                a.deficit_status_code = sc.value_code AND sc.list_code = 'ATTENDANCE_STATUS') 
                        AND SUBSTRING(a.from_date_time1,1,10) = wtc.date 
                        AND ".db_escape(date2sql($from_date))." <= a.from_date_time1  
                        AND a.from_date_time1 <= ".db_escape(date2sql($to_date));  
                            
	if ($employee_code != "")
		$sql .= "AND a.employee_code = ".db_escape($employee_code)." ";
        
	if ($ot_only == 1)
	{
		$sql .= "AND a.granted_ot_time > '00:00:00'";
	}
        
        if ($status_code != "-1")
	{
		$sql .= " AND a.status_code = ".  db_escape($status_code);
	}

	$sql .= " ORDER BY a.employee_code, a.from_date_time1";
        
	return $sql;
}

function get_sql_for_leave_inquiry($from_date, $to_date, $employee_code, $status_code)
{
    $sql = "SELECT  
                CONCAT(l.employee_code,' - ', em.last_name) employee,
                lt.value_name leave_type_name, 
                l.from_date,
                ftc.value_name from_time_name,
                l.to_date,
                ttc.value_name to_time_name,
                l.leave_days,
                l.is_revoke,
                l.comment,
                ls.value_name leave_status_name, 
                l.nopay_days,
                l.payroll_run_id 
                
            FROM 
                ".TB_PREF."leaves l,
                ".TB_PREF."leave_entitlements le,
                ".TB_PREF."list_values lt,
                ".TB_PREF."list_values ls,
                ".TB_PREF."list_values ftc,
                ".TB_PREF."list_values ttc,
                ".TB_PREF."employee_master em 
            WHERE 
		l.employee_code = em.employee_code AND 
                l.leave_entitlement_id = le.id AND 
                le.leave_type_id = lt.id AND 
                l.status_code = ls.value_code AND ls.list_code = 'ATTENDANCE_STATUS' AND 
                l.from_time_code = ftc.value_code AND ftc.list_code = 'WORKING_TIME' AND 
                l.to_time_code = ttc.value_code AND ttc.list_code = 'WORKING_TIME' AND 
                ".db_escape(date2sql($from_date))." <= l.to_date AND l.from_date <= ".db_escape(date2sql($to_date)); 
   
        if ($employee_code != "")
		$sql .= " AND l.employee_code = ".db_escape($employee_code)." ";
        
	if ($status_code != "-1")
	{
		$sql .= " AND l.status_code = ".  db_escape($status_code);
	}

	$sql .= " ORDER BY l.employee_code, l.from_date";
        

    return $sql;
}


function get_sql_for_loans_inquiry($employee_code)
{
    $sql = "SELECT  
                l.id,
                CONCAT(l.employee_code,' - ', em.last_name) employee,
                l.loan_description,
                l.loan_amount,
                l.installment_amount,
                l.start_date,
                vli.total_paid, vli.total_interest ,
                l.inactive,
                l.completed 
            FROM 
                ".TB_PREF."loans l 
                            INNER JOIN ".TB_PREF."employee_master em 
                            LEFT JOIN ".TB_PREF."vw_loan_installments vli ON   
                                        l.id = vli.loan_id 
            WHERE 
		l.employee_code = em.employee_code ";
   
        if ($employee_code != "")
		$sql .= " AND l.employee_code = ".db_escape($employee_code)." ";
        

	$sql .= " ORDER BY l.employee_code, l.start_date";
        

    return $sql;
}

function get_sql_for_employee_inquiry($employee_code, $first_name, $middle_name, $epf_no, $nic)
{
    $sql = "SELECT em.employee_code, em.middle_name, em.first_name, em.nic , em.date_of_joining, office_mobile_no, work_telephone_no, mobile_no 
            FROM 
                ".TB_PREF."employee_master em 
            WHERE 1"; 
   
        if ($employee_code != "")
        {
            $sql .= " AND em.employee_code LIKE ".db_escape("%".$employee_code."%")." ";
        }
 
        if ($first_name != "")
        {
            $sql .= " AND em.first_name LIKE ".db_escape("%".$first_name."%");
        }
        
        if ($middle_name != "")
        {
            $sql .= " AND em.middle_name LIKE ".db_escape("%".$middle_name."%")." ";
        }
        
        if ($epf_no != 0)
        {
            $sql .= " AND em.epf_no LIKE ".db_escape("%".$epf_no."%")." ";
        }
 
        if ($nic != "")
        {
            $sql .= " AND em.nic LIKE ".  db_escape("%".$nic."%");
        }


        $sql .= " ORDER BY em.employee_code";
        

    return $sql;
}


function get_payroll_employees($payroll_run_id, $from_employee = '', $to_employee = '')
{
	$sql = " SELECT DISTINCT prd.employee_code 
                 FROM ".TB_PREF."payroll_run_details prd, 
                        ".TB_PREF."payroll_runs pr   
                 WHERE prd.payroll_run_id = pr.id AND 
                        
                        prd.employee_code <> '' AND 
                        pr.id = ".db_escape($payroll_run_id)  ;
        
    if(strlen($from_employee) > 0){
        $sql .= " AND prd.employee_code >= ".db_escape($from_employee);
    }

    if(strlen($to_employee) > 0){
        $sql .= " AND prd.employee_code <= ".db_escape($to_employee);
    }
        
	$sql .= " ORDER BY prd.employee_code";
    
	return db_query($sql, "Could not retreive payroll employess");
    //pr.status = 'R' AND --- to where clause
}

function get_payroll_employee_salary_component_value($payroll_run_id, $employee_code, $component_code)
{
    $sql = "SELECT value 
            FROM ".TB_PREF."payroll_run_details 
            WHERE payroll_run_id = ".db_escape($payroll_run_id)." AND 
                     employee_code = ".  db_escape($employee_code)." AND 
                     component = ".db_escape($component_code); 
                         
   	$result = db_query($sql, "Salary component amount could not be retrieved");

    $v = db_fetch($result);
    if(isset($v['value']) && $v['value'] > 0){
        return $v['value'];
    }
    else{
        return 0;
    }
}

?>