File: /var/www/content-sp/work-sp/nqb_add_question_upload_by_excel.php
<?php
include_once('header.php');
header('Content-Type: text/html; charset=utf-8');
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
$fileName = str_replace('.php', '', basename($_SERVER['REQUEST_URI'], '?' . $_SERVER['QUERY_STRING']));
function getMediumCode($medium)
{
$m = strtolower(trim($medium));
// English mappings → 1
$english = ["english", "eng", "en", "e", "1"];
if (in_array($m, $english)) {
return 1;
}
// Hindi mappings → 2
$hindi = ["hindi", "hin", "hi", "h", "2"];
if (in_array($m, $hindi)) {
return 2;
}
return 0; // unknown
}
//check edit permissions
if (!in_array($fileName, $pageArray)) {
/***define tables */
$SP_MEDIUM = 'csp_sp_medium';
$SP_QUESTION = 'csp_sp_question';
$SP_QUESTION_MEDIUM_WISE = 'csp_sp_question_medium_wise';
$TT_PREVIOUS_EXAM_TABLE = 'csp_sp_previous_exam';
$SP_MASTER_LEVEL = 'csp_sp_master_level';
$SP_QUESTION_DIFFICULTY = 'csp_sp_question_difficulty';
$SP_MASTER_STRUCTURE = 'csp_sp_master_structure';
$SP_QUES_HISTORY = 'csp_sp_question_history';
$curDate = date('Y-m-d H:i:s');
/**request param */
$subject_id = $_REQUEST['subject_id'];
$topic_id = $_REQUEST['topic_id'];
$sub_topic_id = $_REQUEST['sub_topic_id'];
$master_id = $_REQUEST['master_id'];
$mType = $_REQUEST['mType'];
$msg = '';
/***get master and topic name */
if ($subject_id == '0') {
$subquery = "ms.sub_id='$sub_topic_id' and sm.sub_id='$topic_id'";
} else {
$subquery = "ms.sub_id='$sub_topic_id' and sm.sub_id='$topic_id' and sms.sub_id='$subject_id'";
}
$masterDetails = $conn->query("SELECT ms.category_name as subtopic,sm.category_name as topic,sms.category_name as subject FROM $SP_MASTER_STRUCTURE as ms left join $SP_MASTER_STRUCTURE as sm on sm.sub_id=ms.parent_id left join $SP_MASTER_STRUCTURE as sms on sms.sub_id=sm.parent_id where $subquery");
$md = $masterDetails->fetch_assoc();
$subject = $md['subject'];
$topic = $md['topic'];
$sub_topic = $md['subtopic'];
$quesOfjson = '["sarkari-pariksha"]';
if ($topic_id != '' && $sub_topic_id != '') {
if (isset($_REQUEST['btn_upload'])) {
if (!isset($_FILES["csvfile"])) {
// die("No file selected!");
echo "No file selected!";
}
$file = $_FILES["csvfile"]["tmp_name"];
// Read CSV
$rows = array();
if (($handle = fopen($file, "r")) !== FALSE) {
// FIX BOM issue
$bom = fread($handle, 3);
if ($bom !== "\xEF\xBB\xBF") {
rewind($handle); // Not BOM, go back
}
$header = fgetcsv($handle); // Skip header row
while (($data = fgetcsv($handle)) !== FALSE) {
// Mapping CSV columns
$question_no = trim($data[0]);
$medium = trim($data[1]); // English / Hindi
$instruction = trim($data[2]);
$question = trim($data[3]);
$optA = trim($data[4]);
$optB = trim($data[5]);
$optC = trim($data[6]);
$optD = trim($data[7]);
$optE = trim($data[8]);
$correct = trim($data[9]);
$explanation = trim($data[10]);
// Save rows with question number as key
$rows[$question_no][] = [
"medium" => $medium,
"instruction" => $instruction,
"question" => $question,
"A" => $optA,
"B" => $optB,
"C" => $optC,
"D" => $optD,
"E" => $optE,
"correct" => $correct,
"explanation" => $explanation
];
}
fclose($handle);
}
foreach ($rows as $qno => $questions) {
if (count($questions) == 2) {
// BILINGUAL QUESTION (English + Hindi)
$q1 = $questions[0];
$q2 = $questions[1];
$medium = $q1["medium"];
$medium_id = getMediumCode($medium);
$instructionQ = mysqli_real_escape_string($conn, $q1["instruction"]);
$questionQ = mysqli_real_escape_string($conn, $q1["question"]);
$option1 = mysqli_real_escape_string($conn, $q1["A"]);
$option2 = mysqli_real_escape_string($conn, $q1["B"]);
$option3 = mysqli_real_escape_string($conn, $q1["C"]);
$option4 = mysqli_real_escape_string($conn, $q1["D"]);
$option5 = mysqli_real_escape_string($conn, $q1["E"]);
$correctAnswer = $q1["correct"];
$question_solutionQ = mysqli_real_escape_string($conn, $q1["explanation"]);
$medium_2 = $q2["medium"];
$medium_id_2 = getMediumCode($medium_2);
$instructionQ_2 = mysqli_real_escape_string($conn, $q2["instruction"]);
$questionQ_2 = mysqli_real_escape_string($conn, $q2["question"]);
$option1_2 = mysqli_real_escape_string($conn, $q2["A"]);
$option2_2 = mysqli_real_escape_string($conn, $q2["B"]);
$option3_2 = mysqli_real_escape_string($conn, $q2["C"]);
$option4_2 = mysqli_real_escape_string($conn, $q2["D"]);
$option5_2 = mysqli_real_escape_string($conn, $q2["E"]);
$question_solutionQ_2 = mysqli_real_escape_string($conn, $q2["explanation"]);
if ($subject_id == '0' || $subject_id == '') {
$subjectid0 = $topic_id;
$topicid0 = $sub_topic_id;
$sub_topic_id0 = '0';
} else {
$subjectid0 = $subject_id;
$topicid0 = $topic_id;
$sub_topic_id0 = $sub_topic_id;
}
$sys_id = sprintf('%03d', $master_id) . '-' . sprintf('%04d', $subjectid0) . '-' . sprintf('%05d', $topicid0) . '-' . sprintf('%06d', $sub_topic_id0) . '-' . time() . '-' . rand('1000', '9999');
$optionCount = count(array_filter([$option1, $option2, $option3, $option4, $option5]));
$checkExist = $conn->query("Select count(question_id) as ifexist from $SP_QUESTION where question_statement='$questionQ' and option_1='$option1' and option_2='$option2' and option_3='$option3' and option_4='$option4' and option_5='$option5'");
$existResult = $checkExist->fetch_assoc();
$existRows = $existResult['ifexist'];
if ($existRows == '0') {
if ($master_id != '0' && $master_id != '' && $subjectid0 != '0' && $subjectid0 != '' && $topicid0 != '0' && $topicid0 != '') {
$insertQuery = "INSERT into $SP_QUESTION set master_id='$master_id', subject_id='$subjectid0', topic_id='$topicid0',sub_topic_id='$sub_topic_id0', sys_id='$sys_id', medium_id='$medium_id', no_of_option='$optionCount', question_instruction='$instructionQ', question_statement='$questionQ', option_1='$option1', option_2='$option2', option_3='$option3', option_4='$option4', option_5='$option5', correct_answer='$correctAnswer', question_solution='$question_solutionQ', add_by='$editor_id', add_date='$curDate', status='2', question_of='$quesOfjson', level_name='L1', difficulty='E'";
if ($conn->query($insertQuery)) {
$ques = $conn->insert_id;
$insertHistory = $conn->query("INSERT into $SP_QUES_HISTORY set medium_id='$medium_id', question_id='$ques',action='2',action_by='$editor_id', action_date='$curDate'");
$msg .= "<br>Question Added successfully: #" . $ques;
$checkExist_2 = $conn->query("SELECT count(question_id) as ifexist from $SP_QUESTION_MEDIUM_WISE where question_statement='$questionQ_2' and option_1='$option1_2' and option_2='$option2_2' and option_3='$option3_2' and option_4='$option4_2' and option_5='$option5_2' and medium_id='$medium_id_2' and question_id='$ques'");
$existResult_2 = $checkExist_2->fetch_assoc();
$existRows_2 = $existResult_2['ifexist'];
if ($existRows_2 == '0') {
$insertQuery_2 = "INSERT into $SP_QUESTION_MEDIUM_WISE set question_id='$ques', medium_id='$medium_id_2',question_instruction='$instructionQ_2', question_statement='$questionQ_2', option_1='$option1_2', option_2='$option2_2', option_3='$option3_2', option_4='$option4_2', option_5='$option5_2',question_solution='$question_solutionQ_2', add_by='$editor_id', add_date='$curDate', status='2'";
if ($conn->query($insertQuery_2)) {
$ref_id = $conn->insert_id;
//insert for history
$insertHistory = $conn->query("INSERT into $SP_QUES_HISTORY set medium_id='$medium_id_2', question_id='$ques', ref_id='$ref_id', action='2', action_by='$editor_id', action_date='$curDate'");
$msg .= "<br>Translated Question Added successfully: #" . $ques;
} else {
$msg = "Question not Added" . $conn->error;
}
} else {
$msg .= "<br>This question is already exist: #" . $qno;
}
} else {
$msg = "Question not Added" . $conn->error;
}
} else {
$msg = "Something went wrong, detect zero value";
}
} else {
$msg .= "<br>This question is already exist: #" . $qno;
}
// echo "case1:<br>";
// print_r($q1);
} else {
// SINGLE MEDIUM QUESTION
$q = $questions[0];
$medium = $q["medium"];
$medium_id = getMediumCode($medium);
$instructionQ = mysqli_real_escape_string($conn, $q["instruction"]);
$questionQ = mysqli_real_escape_string($conn, $q["question"]);
$option1 = mysqli_real_escape_string($conn, $q["A"]);
$option2 = mysqli_real_escape_string($conn, $q["B"]);
$option3 = mysqli_real_escape_string($conn, $q["C"]);
$option4 = mysqli_real_escape_string($conn, $q["D"]);
$option5 = mysqli_real_escape_string($conn, $q["E"]);
$correctAnswer = $q["correct"];
$question_solutionQ = mysqli_real_escape_string($conn, $q["explanation"]);
// echo "case2:<br>";
// print_r($q);
if ($subject_id == '0' || $subject_id == '') {
$subjectid0 = $topic_id;
$topicid0 = $sub_topic_id;
$sub_topic_id0 = '0';
} else {
$subjectid0 = $subject_id;
$topicid0 = $topic_id;
$sub_topic_id0 = $sub_topic_id;
}
$sys_id = sprintf('%03d', $master_id) . '-' . sprintf('%04d', $subjectid0) . '-' . sprintf('%05d', $topicid0) . '-' . sprintf('%06d', $sub_topic_id0) . '-' . time() . '-' . rand('1000', '9999');
$optionCount = count(array_filter([$option1, $option2, $option3, $option4, $option5]));
$checkExist = $conn->query("Select count(question_id) as ifexist from $SP_QUESTION where question_statement='$questionQ' and option_1='$option1' and option_2='$option2' and option_3='$option3' and option_4='$option4' and option_5='$option5'");
$existResult = $checkExist->fetch_assoc();
$existRows = $existResult['ifexist'];
if ($existRows == '0') {
if ($master_id != '0' && $master_id != '' && $subjectid0 != '0' && $subjectid0 != '' && $topicid0 != '0' && $topicid0 != '') {
$insertQuery = "INSERT into $SP_QUESTION set master_id='$master_id', subject_id='$subjectid0', topic_id='$topicid0',sub_topic_id='$sub_topic_id0', sys_id='$sys_id', medium_id='$medium_id', no_of_option='$optionCount', question_instruction='$instructionQ', question_statement='$questionQ', option_1='$option1', option_2='$option2', option_3='$option3', option_4='$option4', option_5='$option5', correct_answer='$correctAnswer', question_solution='$question_solutionQ', add_by='$editor_id', add_date='$curDate', status='2', question_of='$quesOfjson', level_name='L1', difficulty='E'";
if ($conn->query($insertQuery)) {
$ques = $conn->insert_id;
$insertHistory = $conn->query("INSERT into $SP_QUES_HISTORY set medium_id='$medium_id', question_id='$ques',action='2',action_by='$editor_id', action_date='$curDate'");
$msg .= "<br>Question Added successfully: #" . $ques;
} else {
$msg = "Question not Added" . $conn->error;
}
} else {
$msg = "Something went wrong, detect zero value";
}
} else {
$msg .= "<br>This question is already exist: #" . $qno;
}
}
}
}
}
?>
<meta http-equiv="Content-Language" content="hi">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<script type="text/javascript">
</script>
<style>
</style>
<div class="page-inner">
<div class="page-title">
<h3>New Question Bank</h3>
<div class="page-breadcrumb">
<ol class="breadcrumb">
<li><a href="dashboard">Home</a></li>
<li class="active">Add Questions (Upload CSV)</li>
</ol>
</div>
</div>
<div id="main-wrapper">
<div class="row">
<div class="col-md-12">
<div class="panel panel-info">
<div class="panel-heading clearfix">
<h4 class="panel-title">Add Questions (Upload CSV): <?php echo $subject . ' >> ' . $topic . ' >> ' . $sub_topic; ?>
</h4>
</div>
<label class="error" style="display: block;float: none;"><?= $msg ?? '' ?></label>
<div class="col-md-6">
<div class="panel panel-default">
<div class="panel-body">
<form id="frmaddQues" method="post" action="" enctype="multipart/form-data">
<div class="form-group" style="float:left; width:100%;">
<label for="input-Default" class="col-sm-3 control-label">Upload CSV File: </label>
<div class="col-sm-9">
<input type="file" name="csvfile" accept=".csv" required>
</div>
</div>
<div class="form-group" style="float:left; width:100%;">
<label for="input-Default" class="col-sm-3 control-label"> </label>
<div class="col-sm-9">
<button type="submit" class="btn btn-primary" name="btn_upload" value="Upload">Upload</button>
</div>
</div>
</form>
</div>
</div>
</div>
<div class="col-md-6">
<div class="panel panel-info">
<div class="panel-body" style="font-size:13px; line-height:1.6;">
<h4 style="margin-top:0;">Steps to Prepare CSV</h4>
<ol>
<li>Prepare your questions in Excel or Google Sheets.</li>
<li>Ensure columns are arranged exactly in required sequence.</li>
<li>After completing the data, click <strong>Save As → CSV UTF-8 (Comma delimited)</strong>.</li>
<li>CSV UTF-8 is required to support Hindi language in csv.</li>
<li>Make sure there are no blank rows at the end.</li>
<li>Upload the CSV using the form on the left.</li>
<li>All the questions will be uploaded in the selected topic/subtopic only.</li>
<li>If any question from csv is not uploaded, you can note down the serial number.</li>
</ol>
<h4>CSV Column Order</h4>
<p>The CSV must follow this column sequence:</p>
<pre style="background:#f7f7f7; line-height: 8px;">
<ul>
<li style="line-height:14px;">Question No: <font style="color:red;">(keep the same serial number for bilingual question, <br> and keep the english question above)</font></li>
<li>Medium: <font style="color:red;">(English/Hindi), currently support 2 languages only</font></li>
<li>Instruction: <font style="color:red;">(optional)</font></li>
<li>Question: </li>
<li>Option 1: </li>
<li>Option 2: <font style="color:red;">(optional)</font></li>
<li>Option 3: <font style="color:red;">(optional)</font></li>
<li>Option 4: <font style="color:red;">(optional)</font></li>
<li>Option 5: <font style="color:red;">(optional)</font></li>
<li>Correct Answer: <font style="color:red;">(1,2,3,4,5), only integer accepted</font></li>
<li>Explanation: <font style="color:red;">(Solution, optional)</font></li>
</ul>
</pre>
<h4>Important Rules</h4>
<ul>
<li>Maximum questions allowed in one CSV: <strong>1000</strong>.</li>
<li>Medium should be <strong>English</strong> or <strong>Hindi</strong>.</li>
<li>Correct Answer should be an integer only.</li>
<li>Images are not supported inside CSV.</li>
</ul>
<a href="question_upload_format.xlsx" download class="btn btn-success btn-sm">
Download Sample Excel File
</a>
</div>
</div>
</div>
</div>
<!--panel info-->
</div>
<div class="page-footer">
<p class="no-s">Graposs Connect ©</p>
</div>
</div>
</div>
</div>
<script type="text/javascript">
jQuery.noConflict();
</script>
<?php
} else {
echo "<script>alert('You dont have access to add question');</script>";
echo "<script type='text/javascript'>location.href='/work-sp';</script>";
}
include_once('footer.php');
?>