<?php

$servername = "localhost";
$username = "devknestlms_dbuser";
$password = "Narahari1!";
$dbname = "devknestlms_multi";

date_default_timezone_set('Asia/Kolkata');
$datetime = date("Y-m-d H:i:s");
$date = date("Y-m-d");

$batch_user_id = 1;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
// Turn autocommit off
$conn->autocommit(FALSE);

try {

    $result = mysqli_query($conn, "SELECT DISTINCT qr.offering_id,qr.assessment_id,qr.user_id,
    qr.attempt_no,qr.question_id,aqp.question_marks,qt.question_type 
    from quiz_response as qr,question_bank as qb,assessment_qp as aqp,question_type as qt 
    WHERE qr.question_id=qb.question_id and aqp.question_id=qb.question_id and qb.question_type_id=qt.question_type_id");
    if (mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_array($result)) {

            if ($row['question_type'] == "MC" || $row['question_type'] == "MR") {
                $flag = true;
                $result4 = mysqli_query($conn, "SELECT qo.option_id,qr.user_response
                    FROM question_options as qo,quiz_response as qr 
                    WHERE qr.offering_id=" . $row['offering_id'] . " AND qr.user_id=" . $row['user_id'] . " AND 
                    qr.question_id=" . $row['question_id'] . " AND qr.attempt_no=" . $row['attempt_no'] . " AND 
                    qo.question_id=qr.question_id AND qo.option_id=qr.option_id");
                if (mysqli_num_rows($result4) > 0) {
                    while ($row4 = mysqli_fetch_array($result4)) {
                        if ($row4['user_response'] == "Y") {
                            $result5 = mysqli_query($conn, "SELECT * FROM question_options 
                            WHERE question_id=" . $row['question_id'] . " AND option_id=" . $row4['option_id'] . " AND 
                            answer_option='Y'");
                            if (mysqli_num_rows($result5) > 0) {
                                if ($row5 = mysqli_fetch_array($result5)) {
                                }
                            } else {
                                $flag = false;
                            }
                        } else {
                            $result5 = mysqli_query($conn, "SELECT * FROM question_options 
                            WHERE question_id=" . $row['question_id'] . " AND option_id=" . $row4['option_id'] . " AND 
                            answer_option='Y'");
                            if (mysqli_num_rows($result5) > 0) {
                                if ($row5 = mysqli_fetch_array($result5)) {
                                    $flag = false;
                                }
                            }
                        }
                    }
                }
                if ($flag) {
                    mysqli_query($conn, "INSERT INTO quiz_response_marks(user_id,offering_id,assessment_id,attempt_no,
                    question_id,marks,created_user,created_ts,last_updated_user,last_updated_ts) 
                    values(" . $row['user_id'] . "," . $row['offering_id'] . "," . $row['assessment_id'] . ",
                    " . $row['attempt_no'] . "," . $row['question_id'] . "," . $row['question_marks'] . ",
                    " . $batch_user_id . ",'" . $datetime . "'," . $batch_user_id . ",'" . $datetime . "')");
                } else {
                    mysqli_query($conn, "INSERT INTO quiz_response_marks(user_id,offering_id,assessment_id,attempt_no,
                    question_id,marks,created_user,created_ts,last_updated_user,last_updated_ts) 
                    values(" . $row['user_id'] . "," . $row['offering_id'] . "," . $row['assessment_id'] . ",
                    " . $row['attempt_no'] . "," . $row['question_id'] . ",0,
                    " . $batch_user_id . ",'" . $datetime . "'," . $batch_user_id . ",'" . $datetime . "')");
                }
            } else if ($row['question_type'] == "MF") {
                $flag = true;
                $result6 = mysqli_query($conn, "SELECT option_id,answer_option FROM question_options 
                    WHERE question_id=" . $row['question_id'] . " AND answer_option!='' order by option_id");
                if (mysqli_num_rows($result6) > 0) {
                    while ($row6 = mysqli_fetch_array($result6)) {
                        $result7 = mysqli_query($conn, "SELECT qr.user_response FROM quiz_response as qr 
                            WHERE qr.offering_id=" . $row['offering_id'] . " AND qr.user_id=" . $row['user_id'] . " AND 
                            qr.question_id=" . $row['question_id'] . " AND qr.attempt_no=" . $row['attempt_no'] . " AND
                            qr.option_id=" . $row6['option_id'] . " AND qr.user_response='" . $row6['answer_option'] . "'");
                        if (mysqli_num_rows($result7) > 0) {
                            if ($row7 = mysqli_fetch_array($result7)) {
                               
                            }
                        } else {
                            $result9 = mysqli_query($conn, "SELECT qr.user_response FROM quiz_response as qr 
                            WHERE qr.offering_id=" . $row['offering_id'] . " AND qr.user_id=" . $row['user_id'] . " AND 
                            qr.question_id=" . $row['question_id'] . " AND qr.attempt_no=" . $row['attempt_no'] . " AND
                            qr.option_id=" . $row6['option_id'] . "");
                            if (mysqli_num_rows($result9) > 0) {
                                if ($row9 = mysqli_fetch_array($result9)) {
                                    $result8 = mysqli_query($conn, "SELECT option_desc FROM question_options 
                                    WHERE question_id=" . $row['question_id'] . " AND option_id='" . $row9['user_response'] . "'");
                                    if (mysqli_num_rows($result8) > 0) {
                                        if ($row8 = mysqli_fetch_array($result8)) {
                                            $flag = false;
                                        }
                                    } else {
                                        $flag = false;
                                    }
                                }
                            }
                        }
                    }
                }
                if ($flag) {
                    mysqli_query($conn, "INSERT INTO quiz_response_marks(user_id,offering_id,assessment_id,attempt_no,
                    question_id,marks,created_user,created_ts,last_updated_user,last_updated_ts) 
                    values(" . $row['user_id'] . "," . $row['offering_id'] . "," . $row['assessment_id'] . ",
                    " . $row['attempt_no'] . "," . $row['question_id'] . "," . $row['question_marks'] . ",
                    " . $batch_user_id . ",'" . $datetime . "'," . $batch_user_id . ",'" . $datetime . "')");
                } else {
                    mysqli_query($conn, "INSERT INTO quiz_response_marks(user_id,offering_id,assessment_id,attempt_no,
                    question_id,marks,created_user,created_ts,last_updated_user,last_updated_ts) 
                    values(" . $row['user_id'] . "," . $row['offering_id'] . "," . $row['assessment_id'] . ",
                    " . $row['attempt_no'] . "," . $row['question_id'] . ",0,
                    " . $batch_user_id . ",'" . $datetime . "'," . $batch_user_id . ",'" . $datetime . "')");
                }
            } else if ($row['question_type'] == "FIB-E") {
                $flag = true;
                $result3 = mysqli_query($conn, "SELECT if(UPPER(qo.option_desc)=UPPER(qr.fib_answer),'Y','N') as result
                    FROM question_options as qo,quiz_response as qr 
                    WHERE qr.offering_id=" . $row['offering_id'] . " AND qr.user_id=" . $row['user_id'] . " AND 
                    qr.question_id=" . $row['question_id'] . " AND qr.attempt_no=" . $row['attempt_no'] . " AND
                    qo.question_id=qr.question_id AND qo.option_id=qr.option_id");
                if (mysqli_num_rows($result3) > 0) {
                    while ($row3 = mysqli_fetch_array($result3)) {
                        if ($row3['result'] != 'Y') {
                            $flag = false;
                        }
                    }
                }
                if ($flag) {
                    mysqli_query($conn, "INSERT INTO quiz_response_marks(user_id,offering_id,assessment_id,attempt_no,
                    question_id,marks,created_user,created_ts,last_updated_user,last_updated_ts) 
                    values(" . $row['user_id'] . "," . $row['offering_id'] . "," . $row['assessment_id'] . ",
                    " . $row['attempt_no'] . "," . $row['question_id'] . "," . $row['question_marks'] . ",
                    " . $batch_user_id . ",'" . $datetime . "'," . $batch_user_id . ",'" . $datetime . "')");
                } else {
                    mysqli_query($conn, "INSERT INTO quiz_response_marks(user_id,offering_id,assessment_id,attempt_no,
                    question_id,marks,created_user,created_ts,last_updated_user,last_updated_ts) 
                    values(" . $row['user_id'] . "," . $row['offering_id'] . "," . $row['assessment_id'] . ",
                    " . $row['attempt_no'] . "," . $row['question_id'] . ",0,
                    " . $batch_user_id . ",'" . $datetime . "'," . $batch_user_id . ",'" . $datetime . "')");
                }
            } else if ($row['question_type'] == "FIB-A") {
                $flag = true;
                $result2 = mysqli_query($conn, "SELECT if(SOUNDEX(UPPER(qo.option_desc))=SOUNDEX(UPPER(qr.fib_answer)),'Y','N') as result
                    FROM question_options as qo,quiz_response as qr 
                    WHERE qr.offering_id=" . $row['offering_id'] . " AND qr.user_id=" . $row['user_id'] . " AND 
                    qr.question_id=" . $row['question_id'] . " AND qr.attempt_no=" . $row['attempt_no'] . " AND
                    qo.question_id=qr.question_id AND qo.option_id=qr.option_id");
                if (mysqli_num_rows($result2) > 0) {
                    while ($row2 = mysqli_fetch_array($result2)) {
                        if ($row2['result'] != 'Y') {
                            $flag = false;
                        }
                    }
                }
                if ($flag) {
                    mysqli_query($conn, "INSERT INTO quiz_response_marks(user_id,offering_id,assessment_id,attempt_no,
                    question_id,marks,created_user,created_ts,last_updated_user,last_updated_ts) 
                    values(" . $row['user_id'] . "," . $row['offering_id'] . "," . $row['assessment_id'] . ",
                    " . $row['attempt_no'] . "," . $row['question_id'] . "," . $row['question_marks'] . ",
                    " . $batch_user_id . ",'" . $datetime . "'," . $batch_user_id . ",'" . $datetime . "')");
                } else {
                    mysqli_query($conn, "INSERT INTO quiz_response_marks(user_id,offering_id,assessment_id,attempt_no,
                    question_id,marks,created_user,created_ts,last_updated_user,last_updated_ts) 
                    values(" . $row['user_id'] . "," . $row['offering_id'] . "," . $row['assessment_id'] . ",
                    " . $row['attempt_no'] . "," . $row['question_id'] . ",0,
                    " . $batch_user_id . ",'" . $datetime . "'," . $batch_user_id . ",'" . $datetime . "')");
                }
            }
        }
    }
    $conn->commit();
} catch (Exception $e) {
    $conn->rollback();
    echo $e->getMessage();
}

$conn->close();
