File: /var/www/content-sp/cron/sync_local_ques.php
<html>
<head>
<!-- <meta http-equiv="refresh" content="90"> -->
</head>
</html>
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
include_once("../work-sp/config.php");
die();
$SP_MASTER_CATEGORY = 'csp_sp_master_category';
$SP_MEDIUM = 'csp_sp_medium';
$SP_MASTER_STRUCTURE = 'csp_sp_master_structure';
/* ========================= MARK QUESTION COMPLETE========================= */
function markQuestionComplete($json_id)
{
$completeApi = "https://postzygapophyseal-ronda-unputrefiable.ngrok-free.dev/api/question/complete";
$payload = json_encode(["question_id" => $json_id]);
$ch = curl_init($completeApi);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
curl_setopt($ch, CURLOPT_POSTFIELDS, $payload);
curl_exec($ch);
curl_close($ch);
}
/* ========================= FETCH API DATA========================= */
$apiUrl = "https://postzygapophyseal-ronda-unputrefiable.ngrok-free.dev/api/questions/pending?page=1&limit=100";
$response = file_get_contents($apiUrl);
$data = json_decode($response, true);
// echo "<pre>".print_r($data, true)."</pre>";
if (!$data || $data['total'] == 0) {
die("No data found");
}
// to remove if non-matching last ['meta']['subject'] and ['question_no']
$fetchCount = count($data['questions']);
if ($fetchCount > 1) {
$last = $data['questions'][$fetchCount - 1];
$secondLast = $data['questions'][$fetchCount - 2];
if ($last['meta']['subject'] === $secondLast['meta']['subject'] && $last['question_no'] === $secondLast['question_no']) {
// found
} else {
array_pop($data['questions']);
}
}
// echo "<pre>".print_r($data['questions'], true)."</pre>";
/* ========================= GROUP BY question_no========================= */
$groupedQuestions = [];
foreach ($data['questions'] as $q) {
$groupedQuestions[$q['meta']['subject']][$q['question_no']][] = $q;
}
// echo "<pre>".print_r($groupedQuestions, true)."</pre>";
// die();
/* ========================= PROCESS GROUPS========================= */
foreach ($groupedQuestions as $subjects => $subNames) {
$counter = 1;
foreach ($subNames as $question_no => $questions) {
$totalVariants = count($questions);
$primaryQ = $questions[0]; // Always treat first record as PRIMARY
try {
/* ================= PRIMARY DATA ================= */
$pId = $primaryQ['id'];
$editor_id = $primaryQ['editor_id'];
$instruction = $conn->real_escape_string($primaryQ['instruction']['text'] ?? '');
$question = $conn->real_escape_string($primaryQ['question']['text'] ?? '');
$solution = $conn->real_escape_string($primaryQ['solution']['text'] ?? '');
$difficulty = $conn->real_escape_string($primaryQ['meta']['difficulty'] ?? 'E');
$medium = $conn->real_escape_string($primaryQ['meta']['medium'] ?? 'English');
$level = $conn->real_escape_string($primaryQ['meta']['level'] ?? 'L1');
$tags = $conn->real_escape_string($primaryQ['meta']['tags'] ?? '');
$group_number = $conn->real_escape_string($primaryQ['meta']['group_number'][0] ?? 0);
$master = $conn->real_escape_string($primaryQ['meta']['master'] ?? '');
$subject = $conn->real_escape_string($primaryQ['meta']['subject'] ?? '');
$topic = $conn->real_escape_string($primaryQ['meta']['topic'] ?? '');
$subtopic = $conn->real_escape_string($primaryQ['meta']['subtopic'] ?? '');
$pyp_name = $conn->real_escape_string($primaryQ['pyp']['exam_name'] ?? '');
$pyp_shift = $conn->real_escape_string($primaryQ['pyp']['shift'] ?? '');
$pyp_year = $conn->real_escape_string($primaryQ['pyp']['year'] ?? '');
$correct_answer = '';
if (!empty($primaryQ['correct_answer'])) {
$correct_answer = implode(',', $primaryQ['correct_answer']);
} else {
foreach ($questions as $index => $other) {
if ($index == 0) continue;
if (!empty($other['correct_answer'])) {
$correct_answer = implode(',', $other['correct_answer']);
break;
}
}
}
$correct_answer = $conn->real_escape_string($correct_answer);
$options = $primaryQ['options'] ?? [];
$validOptions = array_filter($options, function ($opt) {
return !empty(trim($opt['text']));
});
$option_count = count($validOptions);
/* ---------- MASTER / SUBJECT / TOPIC ---------- */
$masterId = $subjectId = $topicId = $subtopicId = 0;
if ($master != '') {
$res = $conn->query("SELECT master_id FROM $SP_MASTER_CATEGORY WHERE master_name='$master'");
$row = $res->fetch_assoc();
$masterId = $row['master_id'] ?? 0;
}
if ($subject != '' && $masterId > 0) {
$res = $conn->query("SELECT sub_id FROM $SP_MASTER_STRUCTURE WHERE category_name='$subject' AND parent_id=0 AND master_id='$masterId'");
$row = $res->fetch_assoc();
$subjectId = $row['sub_id'] ?? 0;
if ($subjectId == 0) {
$editorId = $primaryQ['editor_id'];
$curDate = date('Y-m-d H:i:s');
$insertSub = "INSERT into $SP_MASTER_STRUCTURE set master_id='$masterId', parent_id='0', category_name='$subject', medium_id='1', display_order='1', status='1', source='SP', added_by='$editorId', added_date='$curDate'";
if ($conn->query($insertSub)) {
$subjectId = $conn->insert_id;
}
}
}
if ($topic != '' && $subjectId > 0) {
$res = $conn->query("SELECT sub_id FROM $SP_MASTER_STRUCTURE WHERE category_name='$topic' AND parent_id='$subjectId'");
$row = $res->fetch_assoc();
$topicId = $row['sub_id'] ?? 0;
}
if ($subtopic != '' && $topicId > 0) {
$res = $conn->query("SELECT sub_id FROM $SP_MASTER_STRUCTURE WHERE category_name='$subtopic' AND parent_id='$topicId'");
$row = $res->fetch_assoc();
$subtopicId = $row['sub_id'] ?? 0;
}
$resM = $conn->query("SELECT medium_id FROM $SP_MEDIUM WHERE medium_name='$medium'");
$rowM = $resM->fetch_assoc();
$mediumId = $rowM['medium_id'];
if (!empty($mediumId)) {
if ($masterId == 0 || $subjectId == 0 || $mediumId == 0) {
// throw new Exception("Mapping failed");
echo $counter . " ❌ mapping failed | QNo: $question_no with count $totalVariants | subject: $subjects | medium: $medium | <br>";
} else {
/* ---------- OPTIONS ---------- */
$options = [];
for ($i = 0; $i < $option_count; $i++) {
$options[$i] = $conn->real_escape_string(trim($primaryQ['options'][$i]['text']) ?? '');
}
/* ================= INSERT PRIMARY ================= */
$existId = 0;
$checkExist = $conn->query("SELECT old_question_id FROM csp_sp_question WHERE old_question_id='$pId' and master_id='$masterId' and subject_id= '$subjectId'");
$checkExistRow = $checkExist->fetch_assoc();
$existId = $checkExistRow['old_question_id'] ?? 0;
if (empty($existId)) { // == 0
$option2 = $options[2] ?? '';
$option3 = $options[3] ?? '';
$option4 = $options[4] ?? '';
$sql = "INSERT INTO csp_sp_question SET old_question_id='$pId', master_id='$masterId', subject_id='$subjectId', topic_id='$topicId', sub_topic_id= '$subtopicId', medium_id='$mediumId', question_group='$group_number', correct_answer='$correct_answer', level_name='$level', difficulty= '$difficulty', no_of_option='$option_count', question_statement='$question', question_instruction='$instruction', option_1='{$options[0]}', option_2 ='{$options[1]}', option_3='{$option2}', option_4='{$option3}', option_5='{$option4}', question_solution='$solution', exam_shift= '$pyp_shift', previous_exam_name='$pyp_name', previous_exam_year='$pyp_year', source_id='$question_no', question_tag='$tags', add_by='$editor_id', add_date=NOW(), status=1";
if ($conn->query($sql)) {
$primary_id = $conn->insert_id;
if ($totalVariants > 1) {
for ($i = 1; $i < $totalVariants; $i++) {
$secondaryQ = $questions[$i];
$sec_instruction = $conn->real_escape_string($secondaryQ['instruction']['text'] ?? '');
$sec_question = $conn->real_escape_string($secondaryQ['question']['text'] ?? '');
$sec_solution = $conn->real_escape_string($secondaryQ['solution']['text'] ?? '');
$sec_medium = $conn->real_escape_string($secondaryQ['meta']['medium'] ?? '');
$res = $conn->query("SELECT medium_id FROM $SP_MEDIUM WHERE medium_name='$sec_medium'");
$row = $res->fetch_assoc();
$secMediumId = $row['medium_id'] ?? 0;
if ($secMediumId == 0) {
// throw new Exception("Secondary medium mapping failed");
echo $counter . " ❌ Secondary mapping | QNo: $question_no with count $totalVariants | subject: $subjects | <br>";
} else {
$sec_option = [];
for ($j = 0; $j < 5; $j++) {
$sec_option[$j] = $conn->real_escape_string($secondaryQ['options'][$j]['text'] ?? '');
}
$options2 = $sec_option[2] ?? '';
$options3 = $sec_option[3] ?? '';
$options4 = $sec_option[4] ?? '';
$sql2 = "INSERT INTO csp_sp_question_medium_wise SET question_id='$primary_id', question_instruction='$sec_instruction', question_statement='$sec_question', option_1='{$sec_option[0]}', option_2='{$sec_option[1]}', option_3='{$options2}', option_4='{$options3}', option_5='{$options4}', question_solution='$sec_solution', medium_id='$secMediumId', add_by='{$secondaryQ['editor_id']}', add_date=NOW(), status=1";
if (!$conn->query($sql2)) {
if ($totalVariants == 2) {
$conn->query("DELETE FROM csp_sp_question WHERE question_id ='$primary_id'");
}
// throw new Exception("Secondary insert failed");
echo $counter . " ❌ Secondary insert | QNo: $question_no with count $totalVariants | subject: $subjects | <br>";
} else {
markQuestionComplete($pId);
markQuestionComplete($secondaryQ['id']);
echo $counter . " ✅ Multi medium | QNo: $question_no with count $totalVariants | subject: $subjects | pId: $pId | sId: " . $secondaryQ['id'] . "<br>";
}
}
}
} else {
echo $counter . " ✅ Single medium | QNo: $question_no with count $totalVariants | subject: $subjects | pId: $pId <br>";
markQuestionComplete($pId);
}
} else {
// throw new Exception("Primary insert failed");
echo $counter . " ❌ Primary insert | QNo: $question_no with count $totalVariants | subject: $subjects | <br>";
}
}
}
} else {
echo $counter . " ❌ medium fetch failed | QNo: $question_no with count $totalVariants | subject: $subjects | medium: $medium | <br>";
}
} catch (Exception $e) {
// $conn->rollback();
echo $counter . " ❌ Error | QNo: $question_no with count $totalVariants | subject: $subjects | " . $e->getMessage() . "<br>";
}
$counter++;
}
}