<?php
/**
 * Enterprise HRM ERP System - Excel Import Utility
 * Import data from Payroll.xlsx file
 * 
 * Note: Requires PhpSpreadsheet library
 * Install via: composer require phpoffice/phpspreadsheet
 * Or download from: https://github.com/PHPOffice/PhpSpreadsheet
 */

require_once __DIR__ . '/../../includes/init.php';
requireLogin();

// Check if user has admin access
if (!hasAdminAccess()) {
    die('Access denied. Admin access required.');
}

$pageTitle = 'Import Excel Data';
$currentModule = 'payroll';

$message = '';
$error = '';

// Check if PhpSpreadsheet is available
$phpspreadsheet_available = class_exists('\PhpOffice\PhpSpreadsheet\Spreadsheet');

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel_file'])) {
    if (!$phpspreadsheet_available) {
        $error = 'PhpSpreadsheet library is not installed. Please install it using: composer require phpoffice/phpspreadsheet';
    } else {
        try {
            $file = $_FILES['excel_file'];
            
            if ($file['error'] !== UPLOAD_ERR_OK) {
                throw new Exception('File upload error: ' . $file['error']);
            }
            
            $allowedTypes = ['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel'];
            if (!in_array($file['type'], $allowedTypes)) {
                throw new Exception('Invalid file type. Please upload an Excel file (.xlsx or .xls)');
            }
            
            $uploadPath = __DIR__ . '/../../uploads/';
            if (!is_dir($uploadPath)) {
                mkdir($uploadPath, 0777, true);
            }
            
            $fileName = 'payroll_import_' . time() . '_' . basename($file['name']);
            $filePath = $uploadPath . $fileName;
            
            if (!move_uploaded_file($file['tmp_name'], $filePath)) {
                throw new Exception('Failed to move uploaded file');
            }
            
            // Load Excel file
            require_once __DIR__ . '/../../vendor/autoload.php'; // Adjust path if needed
            
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filePath);
            $siteId = getCurrentSiteId();
            $db = getDB();
            
            $importedCount = 0;
            
            // Import from each sheet
            $sheets = [
                'Employee Master Sheet' => 'importEmployeeMaster',
                'Attendance' => 'importAttendance',
                'Salary Register' => 'importSalaryRegister',
                'New Joinee Statutory' => 'importNewJoineeStatutory',
                'Over Time' => 'importOvertime',
                'Transfer' => 'importTransfer',
                'Resigned & Abscondee' => 'importResignedAbscondee',
            ];
            
            foreach ($sheets as $sheetName => $importFunction) {
                if ($spreadsheet->sheetNameExists($sheetName)) {
                    $worksheet = $spreadsheet->getSheetByName($sheetName);
                    if (function_exists($importFunction)) {
                        $count = $importFunction($worksheet, $db, $siteId);
                        $importedCount += $count;
                    }
                }
            }
            
            $message = "Successfully imported $importedCount records from Excel file.";
            
            // Clean up uploaded file
            unlink($filePath);
            
        } catch (Exception $e) {
            $error = 'Import error: ' . $e->getMessage();
            error_log("Excel import error: " . $e->getMessage());
        }
    }
}

/**
 * Import Employee Master Sheet data
 */
function importEmployeeMaster($worksheet, $db, $siteId) {
    $count = 0;
    $highestRow = $worksheet->getHighestRow();
    
    // Skip header row (row 1)
    for ($row = 2; $row <= $highestRow; $row++) {
        $siNo = $worksheet->getCell('A' . $row)->getValue();
        $empId = $worksheet->getCell('B' . $row)->getValue();
        
        if (empty($empId)) continue; // Skip empty rows
        
        $contactNumber = $worksheet->getCell('C' . $row)->getValue();
        $fathersName = $worksheet->getCell('D' . $row)->getValue();
        $dob = $worksheet->getCell('E' . $row)->getFormattedValue();
        $doj = $worksheet->getCell('F' . $row)->getFormattedValue();
        $dol = $worksheet->getCell('G' . $row)->getFormattedValue();
        $reasonForLeaving = $worksheet->getCell('H' . $row)->getValue();
        $pfNumber = $worksheet->getCell('I' . $row)->getValue();
        $uanNumber = $worksheet->getCell('J' . $row)->getValue();
        $insuranceNumber = $worksheet->getCell('K' . $row)->getValue();
        
        // Convert dates
        $dateOfBirth = !empty($dob) ? \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($worksheet->getCell('E' . $row)->getValue())->format('Y-m-d') : null;
        $dateOfJoining = !empty($doj) ? \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($worksheet->getCell('F' . $row)->getValue())->format('Y-m-d') : null;
        $dateOfLeaving = !empty($dol) ? \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($worksheet->getCell('G' . $row)->getValue())->format('Y-m-d') : null;
        
        try {
            $stmt = $db->prepare("
                INSERT INTO employee_master_sheet (
                    site_id, si_no, emp_id, contact_number, fathers_name,
                    date_of_birth, date_of_joining, date_of_leaving, reason_for_leaving,
                    pf_number, uan_number, insurance_number
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE
                    contact_number = VALUES(contact_number),
                    fathers_name = VALUES(fathers_name),
                    date_of_birth = VALUES(date_of_birth),
                    date_of_joining = VALUES(date_of_joining),
                    date_of_leaving = VALUES(date_of_leaving),
                    reason_for_leaving = VALUES(reason_for_leaving),
                    pf_number = VALUES(pf_number),
                    uan_number = VALUES(uan_number),
                    insurance_number = VALUES(insurance_number)
            ");
            
            $stmt->execute([
                $siteId, $siNo, $empId, $contactNumber, $fathersName,
                $dateOfBirth, $dateOfJoining, $dateOfLeaving, $reasonForLeaving,
                $pfNumber, $uanNumber, $insuranceNumber
            ]);
            $count++;
        } catch (Exception $e) {
            error_log("Error importing employee master row $row: " . $e->getMessage());
        }
    }
    
    return $count;
}

include __DIR__ . '/../../includes/header.php';
?>

<div class="card">
    <div class="card-header">
        <h2 class="card-title">Import Payroll Data from Excel</h2>
    </div>
    
    <div class="card-body">
        <?php if ($message): ?>
        <div class="alert alert-success"><?php echo htmlspecialchars($message); ?></div>
        <?php endif; ?>
        
        <?php if ($error): ?>
        <div class="alert alert-danger"><?php echo htmlspecialchars($error); ?></div>
        <?php endif; ?>
        
        <?php if (!$phpspreadsheet_available): ?>
        <div class="alert alert-warning">
            <strong>PhpSpreadsheet Library Not Found</strong><br>
            To import Excel files, please install PhpSpreadsheet:<br>
            <code>composer require phpoffice/phpspreadsheet</code><br>
            Or download from <a href="https://github.com/PHPOffice/PhpSpreadsheet" target="_blank">GitHub</a>
        </div>
        <?php endif; ?>
        
        <form method="POST" enctype="multipart/form-data">
            <div class="form-group">
                <label for="excel_file">Select Payroll.xlsx File:</label>
                <input type="file" name="excel_file" id="excel_file" accept=".xlsx,.xls" required class="form-control">
                <small class="form-text text-muted">Upload the Payroll.xlsx file to import all data</small>
            </div>
            
            <button type="submit" class="btn btn-primary" <?php echo !$phpspreadsheet_available ? 'disabled' : ''; ?>>Import Data</button>
        </form>
        
        <div style="margin-top: 30px;">
            <h4>Import Instructions:</h4>
            <ul>
                <li>Ensure the Excel file has the following sheets: Employee Master Sheet, Attendance, Salary Register, etc.</li>
                <li>Column headers should match the expected format</li>
                <li>Dates should be in Excel date format</li>
                <li>Empty rows will be skipped</li>
                <li>Existing records with matching Emp ID will be updated</li>
            </ul>
        </div>
    </div>
</div>

<?php include __DIR__ . '/../../includes/footer.php'; ?>

