<?php

/* =======================================================\
  |                        FrontHrm                        |
  |--------------------------------------------------------|
  |   Creator: Phương <trananhphuong83@gmail.com>          |
  |   Date :   09-Jul-2017                                 |
  |   Description: Frontaccounting Payroll & Hrm Module    |
  |   Free software under GNU GPL                          |
  |                                                        |
  \======================================================= */

function payroll_name_exist($name) {
    return check_empty_result("SELECT COUNT(*) FROM " . TB_PREF . "payroll WHERE name = " . db_escape($name));
}

function payroll_exist($month, $fromdate, $todate, $position) {
    return check_empty_result("SELECT COUNT(*) FROM " . TB_PREF . "payroll WHERE month = " . db_escape($month) . " AND fromdate = " . db_escape($fromdate) . " AND todate = " . db_escape($todate) . " AND position = " . db_escape($position));
}

function add_payroll($name, $date, $fromdate, $todate, $position, $month) {
    $sql = "INSERT INTO " . TB_PREF . "payroll (name, date, fromdate,todate,position,month,closed) VALUES (" . db_escape($name) . ", '" . date2sql($date) . "', '" . date2sql($fromdate) . "', '" . date2sql($todate) . "', " . db_escape($position) . ", " . db_escape($month) . ",'0')";

    db_query($sql, _('could not insert payroll data'));
    return db_insert_id();
}

function update_payroll($id, $name, $date, $fromdate, $todate, $position, $month) {
    $sql = "UPDATE " . TB_PREF . "payroll SET name = " . db_escape($name) . ",date='" . date2sql($date) . "',fromdate='" . date2sql($fromdate) . "',todate='" . date2sql($todate) . "',position=" . db_escape($position) . ",month=" . db_escape($month) . " WHERE id = " . db_escape($id);
    db_query($sql, _('could not update grade data'));
}

function get_payroll($id) {
    $sql = "SELECT * FROM " . TB_PREF . "payroll WHERE id = " . db_escape($id);

    return db_query($sql, _('Could not get grade data'));
}

function get_sql_for_search_payrolls($name, $fromdate, $todate, $position, $month, $closed = false) {
    $sql = "SELECT p.* "
            . "FROM " . TB_PREF . "payroll as p WHERE id > 0";

    if (!empty($name))
        $sql .= " AND name=".  db_escape($name);

    if (is_date($fromdate) && is_date($todate))
        $sql .= " AND date BETWEEN '".  date2sql($fromdate)."' AND '".  date2sql($todate)."' ";

    if (!empty($position))
        $sql .= " AND position=" . db_escape($position);
    
    if (!empty($month))
        $sql .= " AND month=" . db_escape($month);

    if ($overdue) {
        $today = date2sql(Today());

        $sql .= " AND due_date < '$today'";
    }

    $sql .= " AND date_ >= '" . date2sql($from) . "'
			AND date_ <= '" . date2sql($to) . "'";

    return $sql;
}
