Next, build the payroll engine.

---

# SQL TABLE

```sql
CREATE TABLE payroll_details (

    id INT AUTO_INCREMENT PRIMARY KEY,

    payroll_month INT NOT NULL,
    payroll_year INT NOT NULL,

    employee_id INT NOT NULL,

    days_worked INT DEFAULT 0,
    hours_worked DECIMAL(10,2) DEFAULT 0,
    overtime_hours DECIMAL(10,2) DEFAULT 0,

    gross_salary DECIMAL(12,2) DEFAULT 0,
    tax_amount DECIMAL(12,2) DEFAULT 0,
    pension_amount DECIMAL(12,2) DEFAULT 0,
    net_salary DECIMAL(12,2) DEFAULT 0,

    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY payroll_unique (
        payroll_month,
        payroll_year,
        employee_id
    )
);
```

---

# payroll_script.php

This file calculates payroll from:

```text
employees
attendance
employee_salary
shifts
department_schedules
```

Create:

```php
<?php

error_reporting(E_ALL);
ini_set('display_errors',1);

require 'config.php';
session_start();

if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit;
}

$month = intval($_GET['month'] ?? date('m'));
$year  = intval($_GET['year'] ?? date('Y'));

$startDate = "$year-" . str_pad($month,2,'0',STR_PAD_LEFT) . "-01";
$endDate   = date(
    'Y-m-t',
    strtotime($startDate)
);

$payroll = [];

if(isset($_POST['generate'])){

    $employees = $conn->query("
        SELECT
        e.id,
        e.name,
        d.name AS department,

        s.salary_type,
        s.basic_salary,
        s.overtime_rate,
        s.tax_percent,
        s.pension_percent

        FROM employees e

        LEFT JOIN departments d
        ON d.id=e.department_id

        LEFT JOIN employee_salary s
        ON s.employee_id=e.id

        ORDER BY e.name
    ");

    while($emp = $employees->fetch_assoc()){

        $empId = $emp['id'];

        /*
        --------------------------------
        DAYS WORKED
        --------------------------------
        */

        $daysWorked = 0;

        $stmt = $conn->prepare("
            SELECT COUNT(*) total
            FROM attendance
            WHERE employee_id=?
            AND status='Present'
            AND attendance_date
            BETWEEN ? AND ?
        ");

        $stmt->bind_param(
            "iss",
            $empId,
            $startDate,
            $endDate
        );

        $stmt->execute();

        $stmt->bind_result($daysWorked);

        $stmt->fetch();

        $stmt->close();

        /*
        --------------------------------
        HOURS WORKED
        --------------------------------
        */

        $hoursWorked = 0;

        $hoursQuery = $conn->prepare("
            SELECT
            SUM(total_hours)
            FROM attendance
            WHERE employee_id=?
            AND attendance_date
            BETWEEN ? AND ?
        ");

        $hoursQuery->bind_param(
            "iss",
            $empId,
            $startDate,
            $endDate
        );

        $hoursQuery->execute();

        $hoursQuery->bind_result($hoursWorked);

        $hoursQuery->fetch();

        $hoursQuery->close();

        $hoursWorked = floatval($hoursWorked);

        /*
        --------------------------------
        SALARY
        --------------------------------
        */

        $gross = 0;

        switch($emp['salary_type']){

            case 'daily':

                $gross =
                $daysWorked *
                $emp['basic_salary'];

            break;

            case 'hourly':

                $gross =
                $hoursWorked *
                $emp['basic_salary'];

            break;

            default:

                $gross =
                $emp['basic_salary'];

        }

        $tax =
        ($gross * $emp['tax_percent'])
        / 100;

        $pension =
        ($gross * $emp['pension_percent'])
        / 100;

        $net =
        $gross
        - $tax
        - $pension;

        $save = $conn->prepare("
            INSERT INTO payroll_details
            (
                payroll_month,
                payroll_year,
                employee_id,
                days_worked,
                hours_worked,
                gross_salary,
                tax_amount,
                pension_amount,
                net_salary
            )
            VALUES (?,?,?,?,?,?,?,?,?)
            ON DUPLICATE KEY UPDATE

            days_worked=VALUES(days_worked),
            hours_worked=VALUES(hours_worked),
            gross_salary=VALUES(gross_salary),
            tax_amount=VALUES(tax_amount),
            pension_amount=VALUES(pension_amount),
            net_salary=VALUES(net_salary)
        ");

        $save->bind_param(
            "iiiiidddd",
            $month,
            $year,
            $empId,
            $daysWorked,
            $hoursWorked,
            $gross,
            $tax,
            $pension,
            $net
        );

        $save->execute();

        $save->close();
    }

    header(
        "Location: payroll.php?month=$month&year=$year&generated=1"
    );

    exit;
}

/*
--------------------------------
LOAD PAYROLL
--------------------------------
*/

$res = $conn->query("
SELECT

p.*,
e.name,
d.name department

FROM payroll_details p

LEFT JOIN employees e
ON e.id=p.employee_id

LEFT JOIN departments d
ON d.id=e.department_id

WHERE payroll_month=$month
AND payroll_year=$year

ORDER BY e.name
");

while($row = $res->fetch_assoc()){

    $payroll[] = $row;
}
```

---

# payroll.php

Header:

```php
<?php
require 'payroll_script.php';
?>
```

---

Month selector:

```php
<form method="GET">

<select name="month">

<?php for($m=1;$m<=12;$m++): ?>

<option
value="<?= $m ?>"
<?= $month==$m?'selected':'' ?>>

<?= date('F', mktime(0,0,0,$m,1)) ?>

</option>

<?php endfor; ?>

</select>

<input
type="number"
name="year"
value="<?= $year ?>">

<button>
Load
</button>

</form>
```

---

Generate button:

```php
<form method="POST">

<button
name="generate"
class="bg-green-600 text-white px-6 py-3 rounded">

Generate Payroll

</button>

</form>
```

---

Summary Cards:

```php
<?php

$totalGross = 0;
$totalNet = 0;
$totalTax = 0;

foreach($payroll as $row){

    $totalGross += $row['gross_salary'];
    $totalNet += $row['net_salary'];
    $totalTax += $row['tax_amount'];
}
?>

<div class="grid md:grid-cols-3 gap-4 mb-6">

<div class="bg-white p-6 rounded shadow">

Gross Payroll

<h2>
₦<?= number_format($totalGross,2) ?>
</h2>

</div>

<div class="bg-white p-6 rounded shadow">

Tax

<h2>
₦<?= number_format($totalTax,2) ?>
</h2>

</div>

<div class="bg-white p-6 rounded shadow">

Net Payroll

<h2>
₦<?= number_format($totalNet,2) ?>
</h2>

</div>

</div>
```

---

Payroll table:

```php
<table class="w-full">

<thead>

<tr>

<th>Employee</th>
<th>Department</th>
<th>Days</th>
<th>Hours</th>
<th>Gross</th>
<th>Tax</th>
<th>Pension</th>
<th>Net</th>

</tr>

</thead>

<tbody>

<?php foreach($payroll as $row): ?>

<tr>

<td><?= htmlspecialchars($row['name']) ?></td>

<td><?= htmlspecialchars($row['department']) ?></td>

<td><?= $row['days_worked'] ?></td>

<td><?= number_format($row['hours_worked'],2) ?></td>

<td>
₦<?= number_format($row['gross_salary'],2) ?>
</td>

<td>
₦<?= number_format($row['tax_amount'],2) ?>
</td>

<td>
₦<?= number_format($row['pension_amount'],2) ?>
</td>

<td class="font-bold text-green-700">
₦<?= number_format($row['net_salary'],2) ?>
</td>

</tr>

<?php endforeach; ?>

</tbody>

</table>
```

### After payroll.php

The next modules to add are:

1. Payslip PDF Generator
2. Payroll Approval Workflow
3. Advance Salary / Loan Management
4. Allowances & Bonuses
5. Leave Management
6. Tax & Pension Reports
7. HR Dashboard with:

   * Attendance %
   * Payroll Cost %
   * Department Labour Cost %
   * Overtime %
   * Absenteeism %

Those will make it a complete hotel HR + Payroll system integrated directly with your attendance and department schedule modules.
