File "spreadsheet_import.php"

Full path: /home2/santhos2/public_html/bharath-coe.firstmatrix.in.net/api/spreadsheet_import.php
File size: 7.77 KiB (7952 bytes)
MIME-type: text/x-php
Charset: utf-8

Download   Open   Back

<?php
require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class QPSpreadsheetImport {
    private $pdo;

    public function __construct($pdo) {
        $this->pdo = $pdo;
    }

    /**
     * Import questions from Excel file
     */
    public function importQuestions($filePath, $assignment_id, $staff_id) {
        try {
            // Load the spreadsheet
            $spreadsheet = IOFactory::load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();

            $questions = [];
            $errors = [];
            $rowNumber = 2; // Start from row 2 (skip header)

            // Determine if this is Part A or Part B based on headers
            $headerRow = $worksheet->rangeToArray('A1:G1', null, true, false)[0];
            $isPartA = !in_array('Option', $headerRow);

            if ($isPartA) {
                // Part A import (columns: Q.No., Question Text, CO, BTL, Unit No., Line No.)
                while (true) {
                    $rowData = $worksheet->rangeToArray('A' . $rowNumber . ':F' . $rowNumber, null, true, false)[0];

                    // Check if row is empty
                    if (empty($rowData[0]) && empty($rowData[1])) {
                        break;
                    }

                    $question = [
                        'question_no' => trim($rowData[0] ?? ''),
                        'question_text' => trim($rowData[1] ?? ''),
                        'co_codes' => trim($rowData[2] ?? ''),
                        'cognitive_level' => trim($rowData[3] ?? ''),
                        'unit_no' => trim($rowData[4] ?? ''),
                        'line_no' => trim($rowData[5] ?? '')
                    ];

                    // Validate required fields
                    if (empty($question['question_no'])) {
                        $errors[] = "Row $rowNumber: Question number is required";
                    }
                    if (empty($question['question_text'])) {
                        $errors[] = "Row $rowNumber: Question text is required";
                    }

                    $questions[] = $question;
                    $rowNumber++;
                }
            } else {
                // Part B import (columns: Q.No., Option, Question Text, CO, BTL, Unit No., Line No.)
                $currentQuestion = null;
                $currentQuestionNo = null;

                while (true) {
                    $rowData = $worksheet->rangeToArray('A' . $rowNumber . ':G' . $rowNumber, null, true, false)[0];

                    // Check if row is empty
                    if (empty($rowData[0]) && empty($rowData[1]) && empty($rowData[2])) {
                        break;
                    }

                    $questionNo = trim($rowData[0] ?? '');
                    $option = trim($rowData[1] ?? '');
                    $questionText = trim($rowData[2] ?? '');

                    // If this is a new question number, create a new question entry
                    if ($questionNo !== $currentQuestionNo && !empty($questionNo)) {
                        if ($currentQuestion !== null) {
                            $questions[] = $currentQuestion;
                        }

                        $currentQuestion = [
                            'question_no' => $questionNo,
                            'options' => []
                        ];
                        $currentQuestionNo = $questionNo;
                    }

                    // Add option to current question
                    if ($currentQuestion !== null && !empty($option) && in_array($option, ['a', 'b', 'A', 'B'])) {
                        $option = strtolower($option);
                        $currentQuestion['options'][] = [
                            'option' => $option,
                            'question_text' => $questionText,
                            'co_codes' => trim($rowData[3] ?? ''),
                            'cognitive_level' => trim($rowData[4] ?? ''),
                            'unit_no' => trim($rowData[5] ?? ''),
                            'line_no' => trim($rowData[6] ?? '')
                        ];
                    }

                    $rowNumber++;
                }

                // Add the last question
                if ($currentQuestion !== null) {
                    $questions[] = $currentQuestion;
                }
            }

            // If there are errors, return them
            if (!empty($errors)) {
                return ['status' => 'error', 'message' => 'Validation errors: ' . implode('; ', $errors)];
            }

            // Save questions to database
            $savedCount = $this->saveImportedQuestions($questions, $assignment_id, $staff_id, $isPartA);

            return [
                'status' => 'success',
                'message' => "Successfully imported $savedCount questions.",
                'data' => ['questions_count' => $savedCount]
            ];

        } catch (Exception $e) {
            return ['status' => 'error', 'message' => 'Import failed: ' . $e->getMessage()];
        }
    }

    private function saveImportedQuestions($questions, $assignment_id, $staff_id, $isPartA) {
        // Get existing submission or create new one
        $stmt = $this->pdo->prepare("SELECT * FROM qp_setter_submissions WHERE assignment_id = ? AND staff_id = ? ORDER BY id DESC LIMIT 1");
        $stmt->execute([$assignment_id, $staff_id]);
        $submission = $stmt->fetch(PDO::FETCH_ASSOC);

        $existingData = [];
        if ($submission && $submission['template_json']) {
            $existingData = json_decode($submission['template_json'], true) ?: [];
        }

        // Merge imported questions with existing data
        if ($isPartA) {
            $existingData['part_a'] = $questions;
        } else {
            $existingData['part_b'] = $questions;
        }

        $templateJson = json_encode($existingData);

        // Update or insert submission
        if ($submission) {
            $stmt = $this->pdo->prepare("UPDATE qp_setter_submissions SET template_json = ?, updated_at = NOW() WHERE id = ?");
            $stmt->execute([$templateJson, $submission['id']]);
        } else {
            $stmt = $this->pdo->prepare("INSERT INTO qp_setter_submissions (assignment_id, staff_id, template_json, status, created_at) VALUES (?, ?, ?, 'Draft', NOW())");
            $stmt->execute([$assignment_id, $staff_id, $templateJson]);
        }

        return count($questions);
    }
}

// Handle API requests
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $action = $_POST['action'] ?? '';
    $staff_id = $_SESSION['qps_staff_id'] ?? 0;
    $assignment_id = $_SESSION['qps_assignment_id'] ?? 0;

    $importer = new QPSpreadsheetImport($pdo);

    switch ($action) {
        case 'import_questions':
            if (!isset($_FILES['excel_file']) || $_FILES['excel_file']['error'] !== UPLOAD_ERR_OK) {
                header('Content-Type: application/json');
                echo json_encode(['status' => 'error', 'message' => 'No file uploaded or upload error']);
                exit;
            }

            $tempFile = $_FILES['excel_file']['tmp_name'];
            $result = $importer->importQuestions($tempFile, $assignment_id, $staff_id);

            header('Content-Type: application/json');
            echo json_encode($result);
            break;

        default:
            header('Content-Type: application/json');
            echo json_encode(['status' => 'error', 'message' => 'Invalid action']);
            break;
    }
}
?>