PDO::ERRMODE_EXCEPTION]); $safe=preg_replace('/[^a-zA-Z0-9_]/','',$d['name']); $server->exec("CREATE DATABASE IF NOT EXISTS `{$safe}` CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci"); } catch (Throwable $e) { throw new RuntimeException('Veritabanı oluşturulamadı. config.php bilgilerini kontrol edin veya database/schema.sql dosyasını elle çalıştırın. Ayrıntı: '.$e->getMessage()); } $pdo=new PDO($base.';dbname='.$d['name'],$d['user'],$d['pass'],[ PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES=>false, ]); migrate($pdo); seed_personnel($pdo); seed_movements($pdo); return $pdo; } function migrate(PDO $pdo): void { $sql=file_get_contents(dirname(__DIR__).'/database/schema.sql'); foreach (array_filter(array_map('trim', preg_split('/;\s*(?:\r?\n|$)/',$sql))) as $q) $pdo->exec($q); // Older installations used email as the login field. Keep the column only // for compatibility, but make it optional because login now uses name. $pdo->exec('ALTER TABLE users MODIFY email VARCHAR(190) NULL'); $index=$pdo->query("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='users' AND index_name='uq_users_name'")->fetchColumn(); if((int)$index===0){ $duplicates=(int)$pdo->query('SELECT COUNT(*) FROM (SELECT name FROM users GROUP BY name HAVING COUNT(*)>1) x')->fetchColumn(); if($duplicates===0) $pdo->exec('ALTER TABLE users ADD UNIQUE KEY uq_users_name (name)'); } $columns=[ 'salary_type'=>"ENUM('monthly','hourly') NOT NULL DEFAULT 'monthly'", 'salary_amount'=>'DECIMAL(12,2) NULL', 'hourly_rate'=>'DECIMAL(12,2) NULL', 'iban'=>'VARCHAR(50) NULL', 'bank_name'=>'VARCHAR(150) NULL', 'bank_account_name'=>'VARCHAR(190) NULL', 'first_name'=>'VARCHAR(120) NULL', 'last_name'=>'VARCHAR(120) NULL', 'card_no'=>'VARCHAR(50) NULL', 'photo_data'=>'MEDIUMBLOB NULL', 'photo_base64'=>'MEDIUMTEXT NULL', 'photo_mime'=>'VARCHAR(50) NULL' ]; foreach($columns as $name=>$definition){ $s=$pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='employees' AND column_name=?");$s->execute([$name]); if((int)$s->fetchColumn()===0) $pdo->exec("ALTER TABLE employees ADD COLUMN `$name` $definition"); } $s=$pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='employee_documents' AND column_name='original_name'");$s->execute(); if((int)$s->fetchColumn()===0) $pdo->exec("ALTER TABLE employee_documents ADD COLUMN original_name VARCHAR(255) NULL AFTER file_path"); $s=$pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='employee_annual_leaves' AND column_name='leave_type'");$s->execute(); if((int)$s->fetchColumn()===0) $pdo->exec("ALTER TABLE employee_annual_leaves ADD COLUMN leave_type VARCHAR(30) NOT NULL DEFAULT 'annual' AFTER end_date"); $pdo->exec("CREATE TABLE IF NOT EXISTS employee_work_periods (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id INT UNSIGNED NOT NULL, start_date DATE NOT NULL, end_date DATE NULL, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_work_period_employee FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE, INDEX idx_work_period_employee(employee_id,start_date)) ENGINE=InnoDB"); // Excel'deki ID, personelin kalıcı personel numarasıdır. Mevcut kayıtları güvenli biçimde doldur. $pdo->exec("UPDATE employees SET employee_no=CAST(legacy_id AS CHAR) WHERE legacy_id IS NOT NULL AND (employee_no IS NULL OR employee_no='')"); // v5: yeni sade personel kartı için mevcut tam adı güvenli biçimde Ad alanına taşı. $pdo->exec("UPDATE employees SET first_name=full_name WHERE (first_name IS NULL OR first_name='') AND (last_name IS NULL OR last_name='')"); // Eski tek başlangıç/bitiş tarihlerini çalışma dönemleri tablosuna yalnızca bir kez aktar. $pdo->exec("CREATE TABLE IF NOT EXISTS app_migrations (migration_key VARCHAR(120) PRIMARY KEY, applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB"); $workMigration='v5_employee_work_periods'; $wm=$pdo->prepare('SELECT COUNT(*) FROM app_migrations WHERE migration_key=?');$wm->execute([$workMigration]); if((int)$wm->fetchColumn()===0){ $pdo->exec("INSERT INTO employee_work_periods(employee_id,start_date,end_date,sort_order) SELECT e.id,e.start_date,e.end_date,1 FROM employees e WHERE e.start_date IS NOT NULL AND NOT EXISTS (SELECT 1 FROM employee_work_periods p WHERE p.employee_id=e.id)"); $pdo->prepare('INSERT INTO app_migrations(migration_key) VALUES(?)')->execute([$workMigration]); } // Excel'deki Sigorta işareti (Check10) daha önce sisteme yanlış aktarılmış kayıtları // yalnızca bir kez düzeltir. Sonraki manuel değişikliklere dokunmaz. $pdo->exec("CREATE TABLE IF NOT EXISTS app_migrations (migration_key VARCHAR(120) PRIMARY KEY, applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB"); $migrationKey='v4_excel_sigorta_check10'; $m=$pdo->prepare('SELECT COUNT(*) FROM app_migrations WHERE migration_key=?');$m->execute([$migrationKey]); if((int)$m->fetchColumn()===0){ $rows=json_decode(file_get_contents(dirname(__DIR__).'/database/personnel_seed.json'),true) ?: []; $u=$pdo->prepare('UPDATE employees SET insurance=? WHERE legacy_id=?'); foreach($rows as $r){$u->execute([(int)($r['insurance']??0),(int)$r['legacy_id']]);} $pdo->prepare('INSERT INTO app_migrations(migration_key) VALUES(?)')->execute([$migrationKey]); } // Excel aktarımı: giriş / çıkış saatleri geçmişte ayrı tabloda tutulur. // Tarih bilgisi v7 ile ilk çalışma döneminin başlangıcı olarak kullanılır. $pdo->exec("CREATE TABLE IF NOT EXISTS employee_shift_history (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id INT UNSIGNED NOT NULL, effective_date DATE NOT NULL, shift_start TIME NULL, shift_end TIME NULL, source VARCHAR(100) NULL, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_shift_history_employee FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE, INDEX idx_shift_history_employee(employee_id,effective_date)) ENGINE=InnoDB"); $shiftMigration='v6_vardiya_xlsx_history'; $sm=$pdo->prepare('SELECT COUNT(*) FROM app_migrations WHERE migration_key=?');$sm->execute([$shiftMigration]); if((int)$sm->fetchColumn()===0){ $rows=json_decode(file_get_contents(dirname(__DIR__).'/database/shift_seed.json'),true) ?: []; $find=$pdo->prepare('SELECT id FROM employees WHERE employee_no=? LIMIT 1'); $ins=$pdo->prepare('INSERT INTO employee_shift_history(employee_id,effective_date,shift_start,shift_end,source,sort_order) VALUES(?,?,?,?,?,?)'); $orders=[]; foreach($rows as $r){ $find->execute([(string)$r['employee_no']]);$employeeId=(int)($find->fetchColumn()?:0);if($employeeId<1)continue; $orders[$employeeId]=($orders[$employeeId]??0)+1; $ins->execute([$employeeId,$r['effective_date'],$r['shift_start'],$r['shift_end'],'Vardiya.xlsx',$orders[$employeeId]]); } $pdo->prepare('INSERT INTO app_migrations(migration_key) VALUES(?)')->execute([$shiftMigration]); } // v7: Vardiya.xlsx içindeki Tarih, vardiya tarihi olarak gösterilmez. // Bu tarih, ilgili personelin ilk çalışma döneminin başlangıcıdır. // Mevcut kullanıcı dönemlerine dokunmamak için yalnızca hiç çalışma dönemi olmayan personele eklenir. $periodDateMigration='v7_excel_date_first_work_period'; $pm=$pdo->prepare('SELECT COUNT(*) FROM app_migrations WHERE migration_key=?');$pm->execute([$periodDateMigration]); if((int)$pm->fetchColumn()===0){ $rows=json_decode(file_get_contents(dirname(__DIR__).'/database/shift_seed.json'),true) ?: []; $find=$pdo->prepare('SELECT id FROM employees WHERE employee_no=? LIMIT 1'); $has=$pdo->prepare('SELECT COUNT(*) FROM employee_work_periods WHERE employee_id=?'); $add=$pdo->prepare('INSERT INTO employee_work_periods(employee_id,start_date,end_date,sort_order) VALUES(?,?,NULL,1)'); $earliest=[]; foreach($rows as $r){ $no=(string)($r['employee_no']??'');$date=(string)($r['effective_date']??''); if($no===''||$date==='')continue; if(!isset($earliest[$no])||$date<$earliest[$no])$earliest[$no]=$date; } foreach($earliest as $no=>$date){ $find->execute([$no]);$employeeId=(int)($find->fetchColumn()?:0);if($employeeId<1)continue; $has->execute([$employeeId]);if((int)$has->fetchColumn()===0)$add->execute([$employeeId,$date]); } $pdo->prepare('INSERT INTO app_migrations(migration_key) VALUES(?)')->execute([$periodDateMigration]); } } function seed_personnel(PDO $pdo): void { $count=(int)$pdo->query('SELECT COUNT(*) FROM employees')->fetchColumn(); if ($count>0) return; $rows=json_decode(file_get_contents(dirname(__DIR__).'/database/personnel_seed.json'),true) ?: []; $stmt=$pdo->prepare('INSERT INTO employees (legacy_id, employee_no, full_name, card_no, is_active, insurance, employment_status) VALUES (?,?,?,?,?,?,?)'); foreach ($rows as $r) $stmt->execute([$r['legacy_id'],(string)$r['legacy_id'],$r['full_name'],$r['card_no'] ?: null,$r['is_active'],$r['insurance'],$r['is_active']?'active':'inactive']); } function seed_movements(PDO $pdo): void { $migrationKey='v15_hareket_xlsx_movements'; $m=$pdo->prepare('SELECT COUNT(*) FROM app_migrations WHERE migration_key=?');$m->execute([$migrationKey]); if((int)$m->fetchColumn()>0) return; $file=dirname(__DIR__).'/database/movement_seed.json'; if(!is_file($file)) return; $rows=json_decode(file_get_contents($file),true) ?: []; $find=$pdo->prepare('SELECT id FROM employees WHERE employee_no=? LIMIT 1'); $ins=$pdo->prepare('INSERT IGNORE INTO employee_movements(employee_id,employee_name,employee_no,movement_date,movement_time,source,source_row) VALUES(?,?,?,?,?,?,?)'); try{ $pdo->beginTransaction(); foreach($rows as $r){ $employeeNo=(string)($r['employee_no']??''); $find->execute([$employeeNo]);$employeeId=$find->fetchColumn(); $ins->execute([$employeeId!==false?(int)$employeeId:null,(string)$r['employee_name'],$employeeNo,(string)$r['date'],(string)$r['time'],'Hareket.xlsx',(int)$r['source_row']]); } $pdo->prepare('INSERT INTO app_migrations(migration_key) VALUES(?)')->execute([$migrationKey]); $pdo->commit(); }catch(Throwable $e){ if($pdo->inTransaction())$pdo->rollBack(); throw $e; } } function logged_in(): bool { return !empty($_SESSION['user_id']); } function require_login(): void { if (!logged_in()) redirect('index.php?page=login'); } function current_user(PDO $pdo): ?array { if(!logged_in()) return null; $s=$pdo->prepare('SELECT id,name,role FROM users WHERE id=?');$s->execute([$_SESSION['user_id']]);return $s->fetch()?:null; }