<?php

function get_employee_attendances_details($date, $only_saved_res=false) {
    $sql = "SELECT emp.employee_code, 
                    a.from_date_time1,
                    a.to_date_time1,
                    a.id,
                    a.shift_id,
                    a.calculated_deficit_time, 
                    a.comment 
            FROM ".TB_PREF."employee_master emp 
                LEFT JOIN ".TB_PREF."attendance a 
                    ON a.employee_code = emp.employee_code 
                        AND a.entry_date = ".db_escape(date2sql($date))." 
            WHERE emp.date_of_joining <= ".db_escape(date2sql($date))." 
                AND (emp.resigned = 0 OR (emp.resigned = 1 AND emp.date_of_resignation >= ".db_escape(date2sql($date))."))";
    
    if ($only_saved_res === true) {
        $sql .= " AND a.id > 0";
    }
    
    $sql .= " ORDER BY emp.employee_code";

    return db_query($sql, "get_employee_attendances_details");
}

function add_employee_attendance($date, $employee_code, $in_time, $out_time) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."attendance (
                    entry_date, employee_code,  
                    from_date_time1, to_date_time1, 
                    created_by, created_on) 
                VALUES ("
                    .db_escape(date2sql($date)).", ".db_escape($employee_code).", "
                    .db_escape($in_time).", ".db_escape($out_time).", " 
                    .db_escape($created_by).", ".db_escape($created_on).")";

    return db_query($sql, "add_employee_attendance");
}

function update_employee_attendance($id, $date, $employee_code, $in_time, $out_time) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."attendance  
            SET from_date_time1 = ".db_escape($in_time).", 
                to_date_time1 = ".db_escape($out_time).", 
                updated_by = ".db_escape($updated_by).",
                updated_on = ".db_escape($updated_on)." 
            WHERE entry_date = ".db_escape(date2sql($date))." 
                AND employee_code = ".db_escape($employee_code)."
                AND id = ".db_escape($id);

    return db_query($sql, "update_employee_attendance");
}

function delete_employee_attendance($id, $date=null, $employee_code=null, $ot_status_array=array()) {
    $sql = "DELETE 
            FROM ".TB_PREF."attendance  
            WHERE id = ".db_escape($id);
    
    if (! ($date == null)) {
        $sql .= " AND entry_date = ".db_escape(date2sql($date));
    }
    if (! ($employee_code == null)) {
        $sql .= " AND employee_code = ".db_escape($employee_code);
    }
    if (count($ot_status_array) > 0) {
        $sql .= " AND ot_status_code IN (". implode(',', $ot_status_array).")";
    }

        db_query($sql, "delete_employee_attendance");
        return db_num_affected_rows();
}



function get_employee_attendances_count($employee_code, $from_date, $to_date) {
    $sql = "SELECT COUNT(id) presents 
            FROM ".TB_PREF."attendance    
            WHERE employee_code = ".db_escape($employee_code)." 
                AND entry_date >= ".db_escape(date2sql($from_date))." 
                AND entry_date <= ".db_escape(date2sql($to_date));

        $result = db_query($sql, "get_employee_attendances_header");
            return db_result_value($result, 'presents');
}



function add_employee_attendances_header($date, $total_employees, $presents, $attendance_status_code) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."employee_attendances_header (
                        date, total_employees,  
                        presents, attendance_status_code, 
                        created_by, created_on) 
                    VALUES ("
                        .db_escape($date).", ".db_escape($total_employees).", "
                        .db_escape($presents).", ".db_escape($attendance_status_code).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";

            return db_query($sql, "add_employee_attendances_header");
}

function update_employee_attendances_header($id, $total_employees, $presents, $attendance_status_code) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."employee_attendances_header   
                SET total_employees = ".db_escape($total_employees).", 
                    presents = ".db_escape($presents).", 
                    attendance_status_code = ".db_escape($attendance_status_code).", 
                    updated_by = ".db_escape($updated_by).",
                    updated_on = ".db_escape($updated_on)." 
                WHERE id = ".db_escape($id);

            return db_query($sql, "update_employee_attendances_header");
}

function get_employee_attendances_header($id=0, $from_date=null, $to_date=null) {
    $sql = "SELECT *  
            FROM ".TB_PREF."employee_attendances_header   
            WHERE 1 = 1";
    
    if ($id > 0) {
        $sql .= " AND id = ".db_escape($id);
    }
    if (! ($from_date === null)) {
        $sql .= " AND date >= ".db_escape($from_date)." AND date <= ".db_escape($to_date);
    }

    return db_query($sql, "get_employee_attendances_header");
}


function delete_employee_attendances_header($id, $date=null) {
    $sql = "DELETE 
            FROM ".TB_PREF."employee_attendances_header   
            WHERE attendance_status_code IN ('N', 'U')";
    
        if ($id > 0) {
            $sql .= " AND id = ".db_escape($id);
        }
        if (! ($date === null)) {
            $sql .= " AND date = ".db_escape($date);
        }

        db_query($sql, "delete_employee_attendances_header");
        return db_num_affected_rows();  
}

function get_employee_attendances_header_by_date($date) {
    $sql = "SELECT *  
            FROM ".TB_PREF."employee_attendances_header   
            WHERE date = ".db_escape($date);

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

function get_payroll_runs_records_by_filters($id=0, $payroll_id=0, $date=null, $status_array=array(), $fetch=false) {
    $sql = "SELECT *     
            FROM ".TB_PREF."payroll_runs   
            WHERE 1 = 1";
    
        if ($id > 0) {
            $sql .= " AND id = ".db_escape($id); 
        }
        
        if ($payroll_id > 0) {
            $sql .= " AND payroll_id = ".db_escape($payroll_id); 
        }
        
        if ($date !== null) {
            $sql .= " AND period_start_date <= ".db_escape($date)." 
                      AND period_end_date >= ".db_escape($date);
        }
        
        if (count($status_array) > 0) {
            $sql .= " AND status IN (".implode(',', $status_array).")";
        }
	
        $result = db_query($sql, "get_payroll_runs_records_by_filters");	
        
        if ($fetch === false) {
            return $result;
        } else {
            return db_fetch($result);
        }
}

function get_attendance_summary_records_by_filters($id=0, $date=null, $status_array=array(), $fetch=false, $from_date=null, $to_date=null) {
    $sql = "SELECT *     
            FROM ".TB_PREF."employee_attendances_header    
            WHERE 1 = 1";
    
        if ($id > 0) {
            $sql .= " AND id = ".db_escape($id); 
        }
        
        if ($date !== null) {
            $sql .= " AND date = ".db_escape($date);
        }
        
        if (count($status_array) > 0) {
            $sql .= " AND attendance_status_code IN (".implode(',', $status_array).")";
        }
        
        if ($from_date !== null && $to_date !== null) {
            $sql .= " AND date >= ".db_escape($from_date)." AND date <= ".db_escape($to_date);
        }
        
        $sql .= " ORDER BY date";
	
        $result = db_query($sql, "get_attendance_summary_records_by_filters");	
        
        if ($fetch === false) {
            return $result;
        } else {
            return db_fetch($result);
        }
}

function update_employee_attendances_header_approvel_status($id, $status, $pre_status_array) {
    $approved_by = $_SESSION['wa_current_user']->user;
    $approved_on = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."employee_attendances_header   
                SET attendance_status_code = ".db_escape($status).",  
                    approved_by = ".db_escape($approved_by).",
                    approved_on = ".db_escape($approved_on)."
                WHERE id = ".db_escape($id)." 
                    AND attendance_status_code IN (".implode(',', $pre_status_array).")";
        
        db_query($sql, 'update_short_leave_approvel_status');
        return db_num_affected_rows();
}

function get_payroll_worktime_deficit_summary_records_with_payroll_run($date=null, $payroll_nun_id=0, $status_array=array(), $not_in=false, $fetch=false) {
    $sql = "SELECT df.* 
            FROM ".TB_PREF."payroll_worktime_deficit_summary df 
                JOIN ".TB_PREF."payroll_runs pr 
                    ON df.payroll_run_id = pr.id 
            WHERE 1 = 1";
    
        if ($date !== null) {
            $sql .= " AND pr.period_start_date <= ".db_escape($date)." 
                    AND pr.period_end_date >= ".db_escape($date);
        }
    
        if (count($status_array) > 0) {
            if ($not_in === false) {
                $sql .= " AND df.status_code IN (".implode(',', $status_array).")";
            } else {
                $sql .= " AND df.status_code NOT IN (".implode(',', $status_array).")";
            }
        }
        
        if ($payroll_nun_id > 0) {
            $sql .= " AND pr.id = ".db_escape($payroll_nun_id);
        }
            
        $sql .= " ORDER BY df.id, df.employee_code";
    
        $result = db_query($sql, "get_payroll_worktime_deficit_summary_records_with_payroll_run");
        
        if ($fetch === false) {
            return $result;
        } else {
            return db_fetch($result);
        }
}

// can add more filters if want
function get_ot_records_by_filters($id=0, $from_date=null, $to_date=null, $employee_code=null, $status_array=array(), $not_in=false, $fetch=false) {
    $sql = "SELECT *  
            FROM ".TB_PREF."attendance  
            WHERE 1 = 1";
    
        if ($id > 0) {
            $sql .= " AND id = ".db_escape($id);
        }
        
        if ($from_date !== null && $to_date !== null ) {
            $sql .= " AND entry_date >= ".db_escape($from_date)." 
                    AND entry_date <= ".db_escape($to_date);
        }
        
        if ($employee_code !== null) {
            $sql .= " AND employee_code = ".db_escape($employee_code);
        }
        
        if (count($status_array) > 0) {
            if ($not_in === false) {
                $sql .= " AND ot_status_code IN (". implode(',', $status_array).")";
            } else {
                $sql .= " AND ot_status_code NOT IN (". implode(',', $status_array).")";
            }
        }
    
        $result = db_query($sql, "get_ot_records_by_filters");

            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}