49 lines
1.9 KiB
PHP
49 lines
1.9 KiB
PHP
<?php
|
|
require_once __DIR__ . '/../config.php';
|
|
|
|
try {
|
|
$pdo = db();
|
|
echo "Connected to database successfully.\n";
|
|
|
|
$sql = <<<SQL
|
|
CREATE TABLE IF NOT EXISTS `payroll_runs` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`company_id` INT NOT NULL,
|
|
`pay_period_month` INT NOT NULL,
|
|
`pay_period_year` INT NOT NULL,
|
|
`status` ENUM('draft', 'completed') NOT NULL DEFAULT 'draft',
|
|
`run_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY `company_period` (`company_id`, `pay_period_year`, `pay_period_month`),
|
|
FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `payslips` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`payroll_run_id` INT NOT NULL,
|
|
`employee_id` INT NOT NULL,
|
|
`gross_pay` DECIMAL(15, 2) NOT NULL,
|
|
`total_deductions` DECIMAL(15, 2) NOT NULL,
|
|
`net_pay` DECIMAL(15, 2) NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`payroll_run_id`) REFERENCES `payroll_runs`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `payslip_items` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`payslip_id` INT NOT NULL,
|
|
`type` ENUM('earning', 'deduction', 'tax') NOT NULL,
|
|
`description` VARCHAR(255) NOT NULL,
|
|
`amount` DECIMAL(15, 2) NOT NULL,
|
|
FOREIGN KEY (`payslip_id`) REFERENCES `payslips`(`id`) ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
SQL;
|
|
|
|
$pdo->exec($sql);
|
|
echo "Successfully created payroll tables: payroll_runs, payslips, payslip_items.\n";
|
|
|
|
} catch (PDOException $e) {
|
|
die("Database migration failed: " . $e->getMessage() . "\n");
|
|
}
|
|
|