<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of assessment
 *
 * @author Lenovo
 */
class Assessment {

    // database connection and table name
    private $conn;
    private $course_offering = "course_offering";
    private $assessment = "assessment";
    private $assessment_qp = "assessment_qp";
    private $quiz_score = "quiz_score";
    private $assessment_criteria = "assessment_criteria";
    private $complexity_type = "complexity_type";
    private $question_bank = "question_bank";
    private $question_options = "question_options";
    private $quiz_response = "quiz_response";
    private $quiz_response_time = "quiz_response_time";
    private $result_criteria = "result_criteria";
    private $quiz_response_marks = "quiz_response_marks";
    private $ai_batch = "ai_batch";
    // object properties
    public $offering_id;
    public $start_date;
    public $end_date;
    public $passing_score;
    public $max_attempts;
    public $assmt_duration;
    // object properties
    public $assessment_id;
    public $user_id;
    public $total_score;
    public $score;
    public $attempt_no;
    public $last_updated_ts;
    public $complexity_type_id;
    public $question_id;
    public $answer_option;
    public $option_id;
    public $time;
    public $weightage;
    //
    public $user_response;
    public $fib_answer;
    public $subjective_answer;
    private $cyu_quiz_response = "cyu_quiz_response";
    private $cyu_quiz_response_marks = "cyu_quiz_response_marks";
    private $cyu_quiz_score = "cyu_quiz_score";
    public $lesson_id = "lesson_id";
    
    public $test_url="";
    public $test_invitation_id;

    // constructor
    public function __construct() {
        $this->conn = DBConnection();
    }

    /*     * *************************Assessment******************************** */

    public function get_assessment_detail() {
        // query to select products
        try {
            $query = "Select a.subjective_assessment,co.assmt_duration,co.passing_score,co.max_attempts,a.assessment_name "
                    . "from $this->course_offering as co, $this->assessment as a,$this->assessment_qp as aq "
                    . "where a.assessment_id =:assessment_id "
                    . "and a.assessment_id=aq.assessment_id and co.offering_id=:offering_id "
                    . "and co.final_assmt_id=a.assessment_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":assessment_id", $this->assessment_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_attempt_no() {
        try {
            // query to count existing cart item
            $query = "Select max(qz.attempt_no) as attempt_no "
                    . "from quiz_score  as qz,course_offering as co "
                    . "where qz.offering_id=:offering_id and qz.user_id=:user_id and qz.assessment_id=co.final_assmt_id and qz.offering_id=co.offering_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind category id variable
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            // execute query
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    
     public function get_testId($no) {
        try {
            // query to count existing cart item
            $query = "Select test_invitation_id "
                    . "from quiz_score  as qz,course_offering as co "
                    . "where qz.attempt_no=:no and qz.offering_id=:offering_id and qz.user_id=:user_id and qz.assessment_id=co.final_assmt_id and qz.offering_id=co.offering_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind category id variable
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":no", $no);
            // execute query
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_no_questions() {
        // query to count existing cart item
        try {
            $query = "Select count(aq.question_id) as no_questions "
                    . "from $this->assessment as a,$this->assessment_qp as aq "
                    . "where a.assessment_id =:assessment_id and a.assessment_id=aq.assessment_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // sanitize
            $this->assessment_id = htmlspecialchars(strip_tags($this->assessment_id));
            // bind category id variable
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            // execute query
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //details fech result criteria
    function assessment_details_fetch() {
        try {
            $query = "SELECT rs.weightage,co.passing_score FROM course_offering as co LEFT JOIN result_criteria as rs on rs.final_assmt_id = co.final_assmt_id AND co.offering_id=rs.offering_id WHERE co.final_assmt_id=:assessment_id AND co.offering_id=:offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":assessment_id", $this->assessment_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //intial insert
    function quiz_score_initial_insert() {
        try {
            $query = "INSERT INTO
                " . $this->quiz_score . "
            SET
                passing_score = :passing_score,
                weightage = :weightage,
                total_score = :total_score,
                score = :score,
                offering_id = :offering_id,
                user_id=:user_id,
                assessment_id=:assessment_id,
                attempt_no=:attempt_no,
                test_url=:test_url,
                test_invitation_id=:test_invitation_id,
                last_updated_user=:last_updated_user,
                last_updated_ts=:last_updated_ts";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":total_score", $this->total_score);
            $stmt->bindParam(":score", $this->score);
            $stmt->bindParam(":passing_score", $this->passing_score);
            $stmt->bindParam(":weightage", $this->weightage);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":test_url", $this->test_url);
            $stmt->bindParam(":test_invitation_id", $this->test_invitation_id);
            $stmt->bindParam(":last_updated_user", $this->user_id);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //randomize exits
    function check_assessment_criteria() {
        try {
            $query = "select ac.question_count,c.complexity_type_id "
                    . "from $this->assessment_criteria as  ac,$this->complexity_type as c "
                    . "where ac.offering_id=:offering_id "
                    . "and ac.question_count!=0 "
                    . "and ac.complexity_type_id=c.complexity_type_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //random questions
    function get_questions_rand() {
        try {
            $query = "select qb.question_id "
                    . "from $this->assessment_qp as aqp,$this->question_bank as qb "
                    . "where aqp.assessment_id=:assessment_id "
                    . "and aqp.question_id=qb.question_id "
                    . "and qb.complexity_type_id=:complexity_type_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":complexity_type_id", $this->complexity_type_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //questions
    function get_question() {
        try {
            $query = "Select aq.question_id,aq.sequence_id,qb.question_type_id,question_text,question_image "
                    . "from $this->assessment_qp as aq, $this->question_bank as qb "
                    . "where aq.assessment_id=:assessment_id "
                    . "and aq.question_id=:question_id "
                    . "and aq.question_id=qb.question_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //get all question if randomization not exits
    function get_all_questions() {
        try {
            $query = "Select aq.question_id,aq.sequence_id,qb.question_type_id,question_text,question_image "
                    . "from $this->assessment_qp as aq, $this->question_bank as qb "
                    . "where aq.assessment_id=:assessment_id "
                    . "and aq.question_id=qb.question_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_option_count() {
        try {
            $query = "Select count(option_id) as count_option_id "
                    . "from $this->question_options "
                    . "where question_id=:question_id "
                    . "and answer_option!=''";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_options() {
        try {
            $query = "Select option_desc,option_image,option_id "
                    . "from $this->question_options "
                    . "where question_id=:question_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_MF_options() {
        try {
            $query = "select option_desc,option_id "
                    . "from $this->question_options "
                    . "WHERE question_id=:question_id"
                    . " and answer_option=''";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /**  Assessment Calculation * */
    function get_options_calc() {
        try {
            $query = "Select qo.option_id,qt.question_type "
                    . "from question_options as qo,question_bank as qb,question_type as qt "
                    . "where qo.question_id=:question_id "
                    . "and qb.question_id=qo.question_id "
                    . "and qb.question_type_id=qt.question_type_id and (qt.question_type !='S-AI' and qt.question_type !='S-AD')";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function insert_times() {
        try {
            $query = "INSERT INTO
                " . $this->quiz_response_time . "
            SET
                offering_id = :offering_id,
                user_id = :user_id,
                assessment_id = :assessment_id,
                attempt_no = :attempt_no,
                sequence_id=:sequence_id,
                question_id=:question_id,
                time=:time,
                last_updated_user=:last_updated_user,
                last_updated_ts=:last_updated_ts";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":sequence_id", $this->sequence_id);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":time", $this->time);
            $stmt->bindParam(":last_updated_user", $this->user_id);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function insert_quiz_response() {
        try {
            $query = "INSERT INTO
                " . $this->quiz_response . "
            SET
                offering_id = :offering_id,
                user_id = :user_id,
                 assessment_id = :assessment_id,
                attempt_no = :attempt_no,
                sequence_id=:sequence_id,
                question_id=:question_id,
                option_id=:option_id,
                user_response=:user_response,
                last_updated_user=:last_updated_user,
                last_updated_ts=:last_updated_ts";
            // prepare query statement
//         marks=(select question_marks from assessment_qp where assessment_id=:assessment_id1 and question_id=:question_id1),
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":sequence_id", $this->sequence_id);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":option_id", $this->option_id);
            $stmt->bindParam(":user_response", $this->user_response);
//            $stmt->bindParam(":assessment_id1", $this->assessment_id);
//            $stmt->bindParam(":question_id1", $this->question_id);
            $stmt->bindParam(":last_updated_user", $this->user_id);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_marks_question() {
        try {
            $query = "select qb.question_type_id,aqp.question_marks "
                    . "from question_bank as qb,assessment_qp as aqp "
                    . "WHERE qb.question_id=:question_id "
                    . "and aqp.question_id=:question_id1 "
                    . "and aqp.assessment_id=:assessment_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":question_id1", $this->question_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_option_id() {
        try {
            $query = "select option_id from question_options WHERE question_id=:question_id and answer_option='Y'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_answer_option_MF() {
        try {
            $query = "select option_desc,option_image,answer_option,feedback from question_options WHERE question_id=:question_id and answer_option!=''";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_passing_score() {
        try {
            $query = "select passing_score from course_offering WHERE offering_id=:offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function update_quiz_score($total_marks, $scr, $date_time, $attm) {
        try {
            $query = $this->conn->prepare("UPDATE " . $this->quiz_score . " SET total_score=:total_score, score=:score, last_updated_user=:last_updated_user,last_updated_ts=:date "
                    . "WHERE offering_id= :offering_id and user_id=:user_id and attempt_no=:attempt_no and assessment_id=:assessment_id");
            $query->bindParam(':total_score', $total_marks);
            $query->bindParam(':score', $scr);
            $query->bindParam(':last_updated_user', $this->user_id);
            $query->bindParam(':date', $date_time, PDO::PARAM_STR);
            $query->bindParam(':offering_id', $this->offering_id, PDO::PARAM_STR);
            $query->bindParam(':user_id', $this->user_id, PDO::PARAM_STR);
            $query->bindParam(':attempt_no', $attm, PDO::PARAM_STR);
            $query->bindParam(':assessment_id', $this->assessment_id, PDO::PARAM_STR);
            if ($query->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //FIB-E question type calculation
    function get_fib_e_options_calc() {
        try {
            $query = "Select if(count(qo.option_id)=(select count(option_id) from question_options where question_id=:question_id1),1,0)as result "
                    . "from question_options as qo,quiz_response as qr "
                    . "where qo.question_id=:question_id2 AND qo.question_id=qr.question_id "
                    . "AND qo.option_id=qr.option_id AND qr.attempt_no=:attempt_no AND qr.offering_id=:offering_id AND qr.assessment_id=:assessment_id AND qr.user_id=:user_id "
                    . "and UPPER(qo.option_desc)=UPPER(qr.fib_answer)";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id1", $this->question_id);
            $stmt->bindParam(":question_id2", $this->question_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //FIB-A question type calculation
    function get_fib_a_options_calc() {
        try {
            $query = "Select if(count(qo.option_id)=(select count(option_id) from question_options where question_id=:question_id1),1,0)as result "
                    . "from question_options as qo,quiz_response as qr "
                    . "where qo.question_id=:question_id2 AND qo.question_id=qr.question_id "
                    . "AND qo.option_id=qr.option_id AND qr.attempt_no=:attempt_no AND qr.offering_id=:offering_id AND qr.assessment_id=:assessment_id AND qr.user_id=:user_id "
                    . "and SOUNDEX(UPPER(qo.option_desc))=SOUNDEX(UPPER(qr.fib_answer))";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id1", $this->question_id);
            $stmt->bindParam(":question_id2", $this->question_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_sub_options_calc() {
        try {
            $query = "select subjective_answer "
                    . "from quiz_response "
                    . "where question_id=:question_id "
                    . "AND attempt_no=:attempt_no AND offering_id=:offering_id AND assessment_id=:assessment_id AND user_id=:user_id ";

            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //FIB question insert
    function insert_fib_quiz_response() {
        try {
            // query to insert cart item record
            $query = "INSERT INTO
                " . $this->quiz_response . "
            SET
                offering_id = :offering_id,
                user_id = :user_id,
                assessment_id = :assessment_id,
                attempt_no = :attempt_no,
                sequence_id=:sequence_id,
                question_id=:question_id,
                option_id=:option_id,
                fib_answer=:fib_answer,
                user_response=:user_response,
                last_updated_user=:last_updated_user,
                last_updated_ts=:last_updated_ts";
            // marks=(select question_marks from assessment_qp where assessment_id=:assessment_id1 and question_id=:question_id1),  
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":sequence_id", $this->sequence_id);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":option_id", $this->option_id);
            $stmt->bindParam(":fib_answer", $this->fib_answer);
            $stmt->bindParam(":user_response", $this->user_response);
//            $stmt->bindParam(":assessment_id1", $this->assessment_id);
//            $stmt->bindParam(":question_id1", $this->question_id);
            $stmt->bindParam(":last_updated_user", $this->user_id);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Subjective question insert
    function insert_sub_quiz_response() {
        try {
            // query to insert cart item record
            $query = "INSERT INTO
                " . $this->quiz_response . "
            SET
                offering_id = :offering_id,
                user_id = :user_id,
                assessment_id = :assessment_id,
                attempt_no = :attempt_no,
                sequence_id=:sequence_id,
                question_id=:question_id,
                option_id=:option_id,
                subjective_answer=:subjective_answer,
                user_response=:user_response,
                last_updated_user=:last_updated_user,
                last_updated_ts=:last_updated_ts";
            // prepare query statement

            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":sequence_id", $this->sequence_id);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":option_id", $this->option_id);
            $stmt->bindParam(":subjective_answer", $this->subjective_answer);
            $stmt->bindParam(":user_response", $this->user_response);
            $stmt->bindParam(":last_updated_user", $this->user_id);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function insert_ai_batch() {
        try {
            // query to insert cart item record
            $query = "INSERT INTO
                " . $this->ai_batch . "
            SET
                offering_id = :offering_id,
                user_id = :user_id,
                assessment_id = :assessment_id,
                attempt_no = :attempt_no,
                question_id=:question_id,
                status='pending',
                created_user=:created_user,
                created_ts=:created_ts,
                last_updated_user=:last_updated_user,
                last_updated_ts=:last_updated_ts";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":created_user", $this->user_id);
            $stmt->bindParam(":created_ts", $this->last_updated_ts);
            $stmt->bindParam(":last_updated_user", $this->user_id);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function insert_quiz_marks($marks) {
        try {
            // query to insert cart item record
            $query = "INSERT INTO
                " . $this->quiz_response_marks . "
            SET
                offering_id = :offering_id,
                user_id = :user_id,
                assessment_id = :assessment_id,
                attempt_no = :attempt_no,
                question_id=:question_id,
                marks=$marks,
                created_user=:created_user,
                created_ts=:created_ts,
                last_updated_user=:last_updated_user,
                last_updated_ts=:last_updated_ts";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":created_user", $this->user_id);
            $stmt->bindParam(":created_ts", $this->last_updated_ts);
            $stmt->bindParam(":last_updated_user", $this->user_id);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function update_course_result() {
        try {
            $query = "UPDATE course_assignment SET result ='awaited',last_updated_ts=:last_updated_ts WHERE user_id=:user_id AND offering_id=:offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function insert_screen_count($oid, $id, $a_id, $attm, $screen, $date_time) {
        try {
            $query = "INSERT INTO
                assmnt_alttab_count
            SET
                offering_id = :offering_id ,
                user_id = :user_id,
                assessment_id = :assessment_id,
                attempt_no = :attempt_no,
                count = :screen,
                last_updated_user = :last_updated_user,
                last_updated_ts = :created_ts";

            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":offering_id", $oid);
            $stmt->bindParam(":user_id", $id);
            $stmt->bindParam(":assessment_id", $a_id);
            $stmt->bindParam(":attempt_no", $attm);
            $stmt->bindParam(":screen", $screen);
            $stmt->bindParam(":last_updated_user", $id);
            $stmt->bindParam(":created_ts", $date_time);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_sub_ques_type() {
        try {
            $query = "Select qt.question_type from question_bank as qb,question_type as qt where qb.question_id=:question_id and qb.question_type_id=qt.question_type_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*     * *************************Quiz check******************************** */

    public function get_result() {
        try {
            $query = "select result from course_assignment WHERE offering_id=:offering_id and user_id=:user_id and status IS NULL";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_user_max_attempt() {
        try {
            $query = "Select max(attempt_no) as attempt_no from quiz_score where offering_id=:offering_id and user_id=:user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_offer_max_attempt() {
        try {
            $query = "Select max_attempts from course_offering where offering_id=:offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*     * *************************Quiz error & Quiz result******************************** */

    function get_quiz_time() {
        try {
            $query = "select sequence_id,time from quiz_response_time WHERE offering_id=:offering_id and attempt_no=:attempt_no and user_id=:user_id and assessment_id=:assessment_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_score() {
        try {
            $query = "select score from quiz_score WHERE offering_id=:offering_id and attempt_no=:attempt_no and user_id=:user_id and assessment_id=:assessment_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":attempt_no", $this->attempt_no);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_offer_detail() {
        try {
            $query = "select co.is_scorm,co.passing_score,co.max_attempts,co.score_flag,co.course_id "
                    . "from course_offering as co WHERE co.offering_id=:offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_all_option_desc() {
        try {
            $query = "select option_desc,option_image from question_options "
                    . "WHERE question_id=:question_id and answer_option='Y'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_correct_option() {
        try {
            $query = "select option_desc from question_options WHERE question_id=:question_id and option_id=:answer_option";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":answer_option", $this->answer_option);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*     * *************************Coaching Report******************************** */

    function get_ques_report() {
        try {
            $query = "select distinct qb.question_id,qb.question_text,qb.question_image,aqp.question_marks,qt.question_type,qr.sequence_id,qrm.marks 
            from quiz_response as qr,question_bank as qb,assessment_qp as aqp,question_type as qt,quiz_response_marks as qrm 
            WHERE qr.offering_id=:offering_id and qr.user_id=:user_id and qr.attempt_no=:attempt_no 
                and qr.question_id=qb.question_id and qr.assessment_id=:assessment_id and aqp.question_id=qb.question_id and qrm.question_id=qr.question_id
                and qrm.user_id=qr.user_id and qrm.offering_id=qr.offering_id
                and qb.question_type_id=qt.question_type_id and qrm.attempt_no=qr.attempt_no
                order by qr.sequence_id asc";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":attempt_no", $this->attempt_no, PDO::PARAM_INT);
            $stmt->bindParam(":assessment_id", $this->assessment_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function getQuestions_count() {
        try {
            $query = "select COUNT(distinct qb.question_id)
            from quiz_response as qr,question_bank as qb,assessment_qp as aqp,question_type as qt,quiz_response_marks as qrm 
            WHERE qr.offering_id=:offering_id and qr.user_id=:user_id and qr.attempt_no=:attempt_no 
                and qr.question_id=qb.question_id and qr.assessment_id=:assessment_id and aqp.question_id=qb.question_id and qrm.question_id=qr.question_id
                and qrm.user_id=qr.user_id and qrm.offering_id=qr.offering_id
                and qb.question_type_id=qt.question_type_id and qrm.attempt_no=qr.attempt_no";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":attempt_no", $this->attempt_no, PDO::PARAM_INT);
            $stmt->bindParam(":assessment_id", $this->assessment_id, PDO::PARAM_INT);
            // execute query
            $stmt->execute();
            // get row value
            //$rows = $stmt->fetch(PDO::FETCH_NUM);
            // return count
            //return $rows[0];
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function cr_user_details() {
        try {
            $query = "SELECT user_id,first_name,last_name,login_id,email_id,mobile_no from user_account where user_id=:user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_report_details() {
        try {
            $query = "select co.offering_type,co.course_name,a.assessment_name,co.passing_score from assessment as a,course_offering as co "
                    . "LEFT JOIN course c ON c.course_id=co.course_id where co.offering_id=:offering_id and a.assessment_id =:assessment_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":assessment_id", $this->assessment_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function marks_result_cr() {
        try {
            $query = "select qs.last_updated_ts,qs.total_score,sum(qrm.marks) as score,qs.score as score1 from quiz_score as qs,quiz_response_marks as qrm where qs.offering_id=:offering_id and qs.assessment_id =:assessment_id AND qs.attempt_no =:attempt_no and qs.user_id=:user_id and qrm.offering_id=qs.offering_id and qrm.user_id=qs.user_id and qrm.assessment_id=qs.assessment_id and qrm.attempt_no=qs.attempt_no group by qs.last_updated_ts,qs.total_score,qs.score";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":attempt_no", $this->attempt_no, PDO::PARAM_INT);
            $stmt->bindParam(":assessment_id", $this->assessment_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_ans_report() {
        try {
            $query = "SELECT distinct qo.option_id,qo.option_desc,qo.option_image,qr.user_response "
                    . "from quiz_response as qr,question_options as qo "
                    . "where qr.question_id=:question_id and qr.offering_id=:offering_id and qr.user_id=:user_id "
                    . "and qr.attempt_no=:attempt_no and qr.question_id=qo.question_id "
                    . "and qo.option_id=qr.option_id order by qo.option_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":attempt_no", $this->attempt_no, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_ans_opt_report() {
        try {
            $query = "select * from question_options where question_id=:question_id and option_id=:option_id and answer_option='Y'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->bindParam(":option_id", $this->option_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /** Used only for MF * */
    function get_opt_report() {
        try {
            $query = "select option_desc,option_id,option_image,answer_option from question_options WHERE question_id=:question_id and answer_option!='' order by option_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_opt_ans_report() {
        try {
            $query = "select qr.user_response from quiz_response as qr "
                    . "where qr.offering_id=:offering_id "
                    . "and qr.user_id=:user_id and qr.attempt_no=:attempt_no and qr.question_id=:question_id "
                    . "and qr.option_id=:option_id and qr.user_response=:answer_option";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":attempt_no", $this->attempt_no, PDO::PARAM_INT);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->bindParam(":option_id", $this->option_id, PDO::PARAM_INT);
            $stmt->bindParam(":answer_option", $this->answer_option, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function check_answer() {
        try {
            $query = "select option_desc from question_options WHERE question_id=:question_id and option_id=:user_response";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_response", $this->user_response, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_opt_an_opt() {
        try {
            $query = "select qr.user_response "
                    . "from quiz_response as qr where qr.offering_id=:offering_id and qr.user_id=:user_id and qr.attempt_no=:attempt_no "
                    . "and qr.question_id=:question_id and qr.option_id=:option_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":attempt_no", $this->attempt_no, PDO::PARAM_INT);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->bindParam(":option_id", $this->option_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_opt_an_opt1() {
        try {
            $query = "select option_desc from question_options WHERE question_id=:question_id and option_id=:user_response";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_response", $this->user_response, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_check_ans_report_MF() {
        try {
            $query = "select option_desc,answer_option from question_options WHERE question_id=:question_id and option_id=:option_id and answer_option!=''";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->bindParam(":option_id", $this->option_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_check_ans_report_MF1() {
        try {
            $query = "select option_desc from question_options WHERE question_id=:question_id and option_id=:answer_option";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->bindParam(":answer_option", $this->answer_option, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*     * MF END * */

    function get_fib_e_opt_ans_report() {
        try {
            $query = "Select qr.fib_answer,qo.option_desc,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=:offering_id AND qr.user_id=:user_id AND qr.question_id=:question_id AND qr.attempt_no=:attempt_no
                AND qo.question_id=qr.question_id 
                AND qo.option_id=qr.option_id order by qo.option_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":attempt_no", $this->attempt_no, PDO::PARAM_INT);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_fib_a_opt_ans_report() {
        try {
            $query = "Select qr.fib_answer,qo.option_desc,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=:offering_id AND qr.user_id=:user_id AND qr.question_id=:question_id AND qr.attempt_no=:attempt_no
                AND qo.question_id=qr.question_id 
                AND qo.option_id=qr.option_id order by qo.option_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":attempt_no", $this->attempt_no, PDO::PARAM_INT);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_sub_opt_ans_report() {
        try {
            $query = "Select qr.subjective_answer,qo.option_desc from quiz_response as qr left join question_options as qo on qo.question_id=qr.question_id
                where qr.offering_id=:offering_id AND qr.user_id=:user_id AND qr.question_id=:question_id AND qr.attempt_no=:attempt_no
                Limit 1";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":attempt_no", $this->attempt_no, PDO::PARAM_INT);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*     * *************************CYU******************************** */

    function get_feedback($options_var) {
        try {
            $query = "SELECT feedback FROM `question_options` WHERE `question_id`=:question_id and " . $options_var . "";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //FIB question type CYU
    function get_fib_options_cyu($answer) {
        try {
            $query = "Select qo.option_id "
                    . "from question_bank as qb, question_options as qo "
                    . "where qb.question_id=:question_id AND qb.question_id=qo.question_id AND qo.option_id=:option_id "
                    . "AND IF(qb.question_type_id=4, qo.option_desc=:answer1, SOUNDEX(UPPER(qo.option_desc))=SOUNDEX(UPPER(:answer2)))";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":option_id", $this->option_id);
            $stmt->bindParam(":answer1", $answer);
            $stmt->bindParam(":answer2", $answer);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_fib_options_cyu_result() {
        try {
            $query = "select option_desc from question_options where question_id=:question_id and option_id=:option_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":option_id", $this->option_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*     * *************************report******************************** */

    function get_assessmentno() {
        try {
            $query = "select final_assmt_id "
                    . "from course_offering "
                    . "where offering_id=:offering_id ";

            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //cyu
    function getLessonId() {
        try {
            $query = "SELECT lesson_id FROM offering_lesson where offering_id=:offering_id and assessment_id=:assessment_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":assessment_id", $this->assessment_id, PDO::PARAM_INT);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function insert_cyu_quiz_response() {
        try {
            $query = "INSERT IGNORE INTO
                " . $this->cyu_quiz_response . "
            SET
                offering_id = :offering_id,
                user_id = :user_id,
                assessment_id = :assessment_id,
                sequence_id=:sequence_id,
                question_id=:question_id,
                option_id=:option_id,
                lesson_id=:lesson_id,
                user_response=:user_response,
                last_updated_ts=:last_updated_ts";
            // prepare query statement
//         marks=(select question_marks from assessment_qp where assessment_id=:assessment_id1 and question_id=:question_id1),
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":sequence_id", $this->sequence_id);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":option_id", $this->option_id);
            $stmt->bindParam(":user_response", $this->user_response);
            $stmt->bindParam(":lesson_id", $this->lesson_id);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function insert_cyu_fib_quiz_response() {
        try {
            // query to insert cart item record
            $query = "INSERT IGNORE INTO
                " . $this->cyu_quiz_response . "
            SET
                offering_id = :offering_id,
                user_id = :user_id,
                assessment_id = :assessment_id,
                sequence_id=:sequence_id,
                question_id=:question_id,
                option_id=:option_id,
                lesson_id=:lesson_id,
                fib_answer=:fib_answer,
                user_response=:user_response,
                last_updated_ts=:last_updated_ts";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":sequence_id", $this->sequence_id);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":option_id", $this->option_id);
            $stmt->bindParam(":fib_answer", $this->fib_answer);
            $stmt->bindParam(":user_response", $this->user_response);
            $stmt->bindParam(":lesson_id", $this->lesson_id);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function insert_cyu_quiz_marks($marks) {
        try {
            $query = "INSERT IGNORE INTO
            " . $this->cyu_quiz_response_marks . "
        SET
            offering_id = :offering_id,
            user_id = :user_id,
            assessment_id = :assessment_id,
            question_id=:question_id,
            lesson_id= :lesson_id,
            marks=:marks,
            last_updated_ts=:last_updated_ts";

            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":assessment_id", $this->assessment_id);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":lesson_id", $this->lesson_id);
            $stmt->bindParam(":marks", $marks); // Bind marks
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);

            if ($stmt->execute()) {
                $affected_rows = $stmt->rowCount();
                if ($affected_rows > 0) {
                    // Insert was successful
                    $query1 = "SELECT score FROM cyu_quiz_score WHERE user_id=:user_id AND offering_id=:offering_id AND lesson_id=:lesson_id AND assessment_id=:assessment_id";
                    $stmt1 = $this->conn->prepare($query1);
                    $stmt1->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
                    $stmt1->bindParam(":assessment_id", $this->assessment_id, PDO::PARAM_INT);
                    $stmt1->bindParam(":user_id", $this->user_id);
                    $stmt1->bindParam(":lesson_id", $this->lesson_id);
                    $stmt1->execute();
                    if ($stmt1->rowCount() > 0) {
                        $rows = $stmt1->fetch(PDO::FETCH_NUM);
                        $score = $rows[0];
                        $total_score = $marks + $score;
                        $query2 = "UPDATE " . $this->cyu_quiz_score . " SET score=:score, last_updated_ts=:last_updated_ts WHERE offering_id= :offering_id AND user_id=:user_id AND lesson_id=:lesson_id AND assessment_id=:assessment_id";
                        $stmt2 = $this->conn->prepare($query2);
                        $stmt2->bindParam(':score', $total_score);
                        $stmt2->bindParam(':offering_id', $this->offering_id, PDO::PARAM_INT);
                        $stmt2->bindParam(':user_id', $this->user_id, PDO::PARAM_INT);
                        $stmt2->bindParam(':assessment_id', $this->assessment_id, PDO::PARAM_INT);
                        $stmt2->bindParam(':lesson_id', $this->lesson_id, PDO::PARAM_INT);
                        $stmt2->bindParam(":last_updated_ts", $this->last_updated_ts);
                        if ($stmt2->execute()) {
                            return true;
                        }
                        return false;
                    } else {
                        $query2 = "INSERT INTO " . $this->cyu_quiz_score . " SET score = :marks, offering_id = :offering_id, user_id=:user_id, assessment_id=:assessment_id, lesson_id=:lesson_id, last_updated_ts=:last_updated_ts";
                        $stmt2 = $this->conn->prepare($query2);
                        $stmt2->bindParam(":marks", $marks); // Bind marks
                        $stmt2->bindParam(":offering_id", $this->offering_id);
                        $stmt2->bindParam(":user_id", $this->user_id);
                        $stmt2->bindParam(":assessment_id", $this->assessment_id);
                        $stmt2->bindParam(':lesson_id', $this->lesson_id);
                        $stmt2->bindParam(":last_updated_ts", $this->last_updated_ts);
                        if ($stmt2->execute()) {
                            return true;
                        }
                        return false;
                    }
                } else {
                    // Insert was ignored
                    return false;
                }
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
            // Handle exception
            return false;
        }
    }

}
