exit-interview-php-debug.php

<?php
/**
 * Exit Interview Form Processor - DEBUG VERSION
 * This version includes detailed debugging output
 */

// Start a session
session_start();

// ENABLE ERROR REPORTING FOR DEBUGGING
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Create a debug log array to track execution steps
$debug_log = [];
$debug_log[] = "Script started at " . date('Y-m-d H:i:s');

// Database connection settings
$db_host = 'localhost';
$db_name = 'vdart_hr';
$db_user = 'root';  // Change if your MySQL username is different
$db_pass = '';      // Add your MySQL password if any

// Function to connect to the database with debugging
function connectDB() {
    global $db_host, $db_name, $db_user, $db_pass, $debug_log;
    
    $debug_log[] = "Attempting database connection to $db_name on $db_host";
    
    try {
        $conn = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $debug_log[] = "Database connection successful";
        return $conn;
    } catch(PDOException $e) {
        $debug_log[] = "Database connection failed: " . $e->getMessage();
        return false;
    }
}

// Test database connection immediately
$test_conn = connectDB();
if ($test_conn) {
    $debug_log[] = "Initial database connection test: SUCCESS";
    
    // Check if tables exist
    try {
        $stmt = $test_conn->prepare("SHOW TABLES LIKE 'exit_interviews'");
        $stmt->execute();
        if ($stmt->rowCount() > 0) {
            $debug_log[] = "Table 'exit_interviews' exists";
        } else {
            $debug_log[] = "ERROR: Table 'exit_interviews' does not exist!";
        }
        
        $stmt = $test_conn->prepare("SHOW TABLES LIKE 'exit_interview_reasons'");
        $stmt->execute();
        if ($stmt->rowCount() > 0) {
            $debug_log[] = "Table 'exit_interview_reasons' exists";
        } else {
            $debug_log[] = "ERROR: Table 'exit_interview_reasons' does not exist!";
        }
    } catch(PDOException $e) {
        $debug_log[] = "Error checking tables: " . $e->getMessage();
    }
} else {
    $debug_log[] = "Initial database connection test: FAILED";
}

// Function to sanitize input data
function sanitizeInput($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data, ENT_QUOTES, 'UTF-8');
    return $data;
}

// Process form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $debug_log[] = "POST request received";
    $debug_log[] = "Raw POST data: " . print_r($_POST, true);
    
    // Collect and sanitize all form data
    $formData = [];
    
    // Basic information
    $formData['employeeName'] = sanitizeInput($_POST['employeeName'] ?? '');
    $formData['employeeId'] = sanitizeInput($_POST['employeeId'] ?? '');
    $formData['designation'] = sanitizeInput($_POST['designation'] ?? '');
    $formData['team'] = sanitizeInput($_POST['team'] ?? '');
    $formData['calDal'] = sanitizeInput($_POST['calDal'] ?? '');
    $formData['buHead'] = sanitizeInput($_POST['buHead'] ?? '');
    $formData['doj'] = sanitizeInput($_POST['doj'] ?? '');
    $formData['dor'] = sanitizeInput($_POST['dor'] ?? '');
    
    // Resignation details
    $formData['resignDecision'] = sanitizeInput($_POST['resignDecision'] ?? '');
    if ($formData['resignDecision'] === 'Other' && isset($_POST['resignOther'])) {
        $formData['resignDecision'] = 'Other: ' . sanitizeInput($_POST['resignOther']);
    }
    
    // Reasons for leaving
    if (isset($_POST['reasonsForLeaving']) && is_array($_POST['reasonsForLeaving'])) {
        $formData['reasonsForLeaving'] = array_map('sanitizeInput', $_POST['reasonsForLeaving']);
    } else {
        $formData['reasonsForLeaving'] = [];
    }
    
    // Yes/No questions with descriptions
    $formData['exploredOptions'] = sanitizeInput($_POST['exploredOptions'] ?? '');
    $formData['exploredOptionsDesc'] = sanitizeInput($_POST['exploredOptionsDesc'] ?? '');
    $formData['specificEvent'] = sanitizeInput($_POST['specificEvent'] ?? '');
    $formData['specificEventDesc'] = sanitizeInput($_POST['specificEventDesc'] ?? '');
    $formData['recommendCompany'] = sanitizeInput($_POST['recommendCompany'] ?? '');
    $formData['recommendCompanyDesc'] = sanitizeInput($_POST['recommendCompanyDesc'] ?? '');
    $formData['openToReapply'] = sanitizeInput($_POST['openToReapply'] ?? '');
    $formData['openToReapplyDesc'] = sanitizeInput($_POST['openToReapplyDesc'] ?? '');
    
    // Text questions
    $formData['encourageToStay'] = sanitizeInput($_POST['encourageToStay'] ?? '');
    $formData['factorsNewRole'] = sanitizeInput($_POST['factorsNewRole'] ?? '');
    $formData['areasToImprove'] = sanitizeInput($_POST['areasToImprove'] ?? '');
    $formData['membersToThank'] = sanitizeInput($_POST['membersToThank'] ?? '');
    
    // Ratings
    $formData['ratingPay'] = sanitizeInput($_POST['ratingPay'] ?? '');
    $formData['ratingTraining'] = sanitizeInput($_POST['ratingTraining'] ?? '');
    $formData['ratingSupervisor'] = sanitizeInput($_POST['ratingSupervisor'] ?? '');
    $formData['ratingRelationship'] = sanitizeInput($_POST['ratingRelationship'] ?? '');
    $formData['ratingBenefits'] = sanitizeInput($_POST['ratingBenefits'] ?? '');
    $formData['ratingPromotion'] = sanitizeInput($_POST['ratingPromotion'] ?? '');
    $formData['ratingPerformance'] = sanitizeInput($_POST['ratingPerformance'] ?? '');
    $formData['ratingDiscipline'] = sanitizeInput($_POST['ratingDiscipline'] ?? '');
    
    // HRBP Note
    $formData['hrbpNote'] = sanitizeInput($_POST['hrbpNote'] ?? '');
    
    $debug_log[] = "Processed form data: " . print_r($formData, true);
    
    // Now attempt to save directly to the database
    try {
        $debug_log[] = "Attempting database save...";
        $conn = connectDB();
        
        if (!$conn) {
            $debug_log[] = "ERROR: Failed to establish database connection for save operation";
            throw new Exception("Database connection failed");
        }
        
        // Begin transaction
        $conn->beginTransaction();
        $debug_log[] = "Transaction started";
        
        // Insert basic information
        $sql = "INSERT INTO exit_interviews (
                    employee_name, employee_id, designation, team, 
                    cal_dal, bu_head, doj, dor, resign_decision,
                    explored_options, explored_options_desc,
                    specific_event, specific_event_desc,
                    recommend_company, recommend_company_desc,
                    open_to_reapply, open_to_reapply_desc,
                    encourage_to_stay, factors_new_role,
                    areas_to_improve, members_to_thank,
                    rating_pay, rating_training, rating_supervisor,
                    rating_relationship, rating_benefits, rating_promotion,
                    rating_performance, rating_discipline,
                    hrbp_note, submission_date
                ) VALUES (
                    :employee_name, :employee_id, :designation, :team,
                    :cal_dal, :bu_head, :doj, :dor, :resign_decision,
                    :explored_options, :explored_options_desc,
                    :specific_event, :specific_event_desc,
                    :recommend_company, :recommend_company_desc,
                    :open_to_reapply, :open_to_reapply_desc,
                    :encourage_to_stay, :factors_new_role,
                    :areas_to_improve, :members_to_thank,
                    :rating_pay, :rating_training, :rating_supervisor,
                    :rating_relationship, :rating_benefits, :rating_promotion,
                    :rating_performance, :rating_discipline,
                    :hrbp_note, NOW()
                )";
        
        $debug_log[] = "Preparing SQL statement: " . $sql;
        
        $stmt = $conn->prepare($sql);
        $debug_log[] = "SQL statement prepared";
        
        // Bind parameters
        $stmt->bindParam(':employee_name', $formData['employeeName']);
        $stmt->bindParam(':employee_id', $formData['employeeId']);
        $stmt->bindParam(':designation', $formData['designation']);
        $stmt->bindParam(':team', $formData['team']);
        $stmt->bindParam(':cal_dal', $formData['calDal']);
        $stmt->bindParam(':bu_head', $formData['buHead']);
        $stmt->bindParam(':doj', $formData['doj']);
        $stmt->bindParam(':dor', $formData['dor']);
        $stmt->bindParam(':resign_decision', $formData['resignDecision']);
        $stmt->bindParam(':explored_options', $formData['exploredOptions']);
        $stmt->bindParam(':explored_options_desc', $formData['exploredOptionsDesc']);
        $stmt->bindParam(':specific_event', $formData['specificEvent']);
        $stmt->bindParam(':specific_event_desc', $formData['specificEventDesc']);
        $stmt->bindParam(':recommend_company', $formData['recommendCompany']);
        $stmt->bindParam(':recommend_company_desc', $formData['recommendCompanyDesc']);
        $stmt->bindParam(':open_to_reapply', $formData['openToReapply']);
        $stmt->bindParam(':open_to_reapply_desc', $formData['openToReapplyDesc']);
        $stmt->bindParam(':encourage_to_stay', $formData['encourageToStay']);
        $stmt->bindParam(':factors_new_role', $formData['factorsNewRole']);
        $stmt->bindParam(':areas_to_improve', $formData['areasToImprove']);
        $stmt->bindParam(':members_to_thank', $formData['membersToThank']);
        $stmt->bindParam(':rating_pay', $formData['ratingPay']);
        $stmt->bindParam(':rating_training', $formData['ratingTraining']);
        $stmt->bindParam(':rating_supervisor', $formData['ratingSupervisor']);
        $stmt->bindParam(':rating_relationship', $formData['ratingRelationship']);
        $stmt->bindParam(':rating_benefits', $formData['ratingBenefits']);
        $stmt->bindParam(':rating_promotion', $formData['ratingPromotion']);
        $stmt->bindParam(':rating_performance', $formData['ratingPerformance']);
        $stmt->bindParam(':rating_discipline', $formData['ratingDiscipline']);
        $stmt->bindParam(':hrbp_note', $formData['hrbpNote']);
        
        $debug_log[] = "Parameters bound";
        
        // Execute the statement
        $stmt->execute();
        $debug_log[] = "Statement executed successfully";
        
        // Get the interview ID
        $interviewId = $conn->lastInsertId();
        $debug_log[] = "Got last insert ID: " . $interviewId;
        
        // Insert reasons for leaving
        if (isset($formData['reasonsForLeaving']) && is_array($formData['reasonsForLeaving']) && !empty($formData['reasonsForLeaving'])) {
            $reasonsSql = "INSERT INTO exit_interview_reasons (interview_id, reason) VALUES (:interview_id, :reason)";
            $reasonsStmt = $conn->prepare($reasonsSql);
            $debug_log[] = "Prepared reasons statement";
            
            foreach ($formData['reasonsForLeaving'] as $reason) {
                $reasonsStmt->bindParam(':interview_id', $interviewId);
                $reasonsStmt->bindParam(':reason', $reason);
                $reasonsStmt->execute();
                $debug_log[] = "Inserted reason: " . $reason;
            }
        } else {
            $debug_log[] = "No reasons for leaving to insert";
        }
        
        // Commit the transaction
        $conn->commit();
        $debug_log[] = "Transaction committed successfully";
        
        // Success!
        $debug_log[] = "Data saved successfully! Interview ID: " . $interviewId;
        
        // Set success message for thank you page
        $_SESSION['success_message'] = "Exit interview submitted successfully!";
        $_SESSION['debug_log'] = $debug_log;
        
        // Redirect to thank you page
        header('Location: thank_you.php');
        exit;
        
    } catch (Exception $e) {
        // Log the error
        $debug_log[] = "ERROR OCCURRED: " . $e->getMessage();
        if (isset($conn)) {
            $conn->rollBack();
            $debug_log[] = "Transaction rolled back due to error";
        }
        
        // Display detailed error page instead of redirecting
        $_SESSION['error_message'] = $e->getMessage();
        $_SESSION['debug_log'] = $debug_log;
        
        // Don't redirect - display the error right here
    }
} else {
    $debug_log[] = "Not a POST request";
}

// Display the debug information
echo "<!DOCTYPE html>
<html lang='en'>
<head>
    <meta charset='UTF-8'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0'>
    <title>Exit Interview Debug</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; line-height: 1.6; }
        h1 { color: #d9534f; }
        .debug-container { background-color: #f9f9f9; border: 1px solid #ddd; padding: 20px; border-radius: 5px; }
        .debug-log { background-color: #333; color: #fff; padding: 15px; overflow: auto; white-space: pre-wrap; font-family: monospace; }
        .error { color: #d9534f; font-weight: bold; }
        .success { color: #5cb85c; font-weight: bold; }
        .db-test-container { margin-top: 30px; padding: 15px; background-color: #eee; border-radius: 5px; }
        table { border-collapse: collapse; width: 100%; margin-top: 20px; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
    </style>
</head>
<body>
    <h1>Exit Interview Form Processor - Debug Mode</h1>
    
    <div class='debug-container'>
        <h2>Debug Log</h2>
        <div class='debug-log'>";

foreach ($debug_log as $log) {
    if (strpos($log, 'ERROR') !== false) {
        echo "<span class='error'>{$log}</span>\n";
    } else if (strpos($log, 'SUCCESS') !== false || strpos($log, 'successful') !== false) {
        echo "<span class='success'>{$log}</span>\n";
    } else {
        echo $log . "\n";
    }
}

echo "</div>
    </div>
    
    <div class='db-test-container'>
        <h2>Database Test</h2>";

// Perform a direct database test
try {
    $conn = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "<p class='success'>Database connection successful!</p>";
    
    // Check tables
    $tables = array('exit_interviews', 'exit_interview_reasons');
    
    echo "<h3>Table Status:</h3>";
    echo "<table><tr><th>Table Name</th><th>Status</th></tr>";
    
    foreach ($tables as $table) {
        $stmt = $conn->prepare("SHOW TABLES LIKE :table");
        $stmt->bindParam(':table', $table);
        $stmt->execute();
        
        if ($stmt->rowCount() > 0) {
            echo "<tr><td>{$table}</td><td class='success'>Exists</td></tr>";
        } else {
            echo "<tr><td>{$table}</td><td class='error'>Missing</td></tr>";
        }
    }
    
    echo "</table>";
    
    // Check for any existing records
    $stmt = $conn->prepare("SELECT COUNT(*) AS count FROM exit_interviews");
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    
    echo "<p>Number of existing exit interview records: <strong>{$result['count']}</strong></p>";
    
} catch(PDOException $e) {
    echo "<p class='error'>Database connection failed: " . $e->getMessage() . "</p>";
    
    echo "<h3>Troubleshooting Steps:</h3>
    <ol>
        <li>Make sure your MySQL server is running</li>
        <li>Check that the database name 'vdart_hr' exists</li>
        <li>Verify your username and password are correct</li>
        <li>Run the SQL script to create the required tables</li>
    </ol>";
}

echo "</div>

    <div style='margin-top: 30px;'>
        <h2>Form Submission</h2>";

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    echo "<p>A form submission was processed. See the debug log above for details.</p>";
    
    if (isset($_SESSION['error_message'])) {
        echo "<p class='error'>Error: {$_SESSION['error_message']}</p>";
    }
    
    echo "<p><a href='exit-interview1.php'>Return to the form</a></p>";
} else {
    echo "<p>No form submission detected. This page should be accessed via the form submission.</p>";
    echo "<p><a href='exit-interview1.php'>Go to the form</a></p>";
}

echo "</div>
</body>
</html>";
?>