<?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 LTI
{
    private $conn;
    public $user_id;
    public $contentdtl_id;
    public $name;
    public $lti_types_id;
    public $tool_url;
    public $text;
    public $launch_container;
    public $grade_max;
    public $custom_id;
    public $datetime;
    //added
    public $lti_id;

    public $content_id;

    public $date_time;

    public $lesson_id;

    public $course_id;

    public $grade_pass;
    public $grade_max_lti;

    public $grade_min;

    public $grade_pass_actual;

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

    public function check_user($id)
    {
        try {
            $query = "SELECT user_id FROM user_account WHERE user_id = :user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $id, PDO::PARAM_INT);
            $stmt->execute();
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_lti_types($id)
    {
        try {
            $query = "SELECT * FROM lti_types WHERE lti_types_id = :lti_types_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":lti_types_id", $id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_lti_types_from_clientId($clientid)
    {
        try {
            $query = "SELECT * FROM lti_types WHERE client_id = :clientid";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":clientid", $clientid, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_lti_types_config($id)
    {
        try {
            $query = "SELECT name, value
            FROM lti_types_config
            WHERE lti_types_id = :lti_types_id1
            UNION ALL
            SELECT 'toolurl' AS name, tool_url AS value
            FROM lti_types
            WHERE lti_types_id = :lti_types_id2";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":lti_types_id1", $id, PDO::PARAM_INT);
            $stmt->bindParam(":lti_types_id2", $id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_lti($contentdtl_id)
    {
        try {
            $query = "SELECT * FROM lti WHERE contentdtl_id=$contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_grade_items($contentdtl_id, $lti_id)
    {
        try {
            $query = "SELECT * FROM grade_items WHERE contentdtl_id=$contentdtl_id AND lti_id=$lti_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_grade_item($grade_item_id)
    {
        try {
            $query = "SELECT * FROM grade_items WHERE grade_items_id=$grade_item_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_lti_by_grade_item($lti_id)
    {
        try {
            $query = "SELECT * FROM lti WHERE lti_id=$lti_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_grade_grades($grade_item_id, $offering_id, $user_id)
    {
        try {
            $query = "SELECT * FROM lti_user_grades WHERE grade_items_id=$grade_item_id and offering_id=$offering_id and user_id=$user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function insert_lti($lti)
    {
        try {
            $query = "INSERT INTO lti(contentdtl_id, name, lti_types_id, tool_url, text, launch_container, custom_id, grade_max, created_user, created_ts) VALUES (:contentdtl_id, :name, :lti_types_id, :tool_url, :text, :launch_container, :custom_id, :grade_max, :created_user, :created_ts)";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":contentdtl_id", $lti->contentdtl_id, PDO::PARAM_INT);
            $stmt->bindParam(":name", $lti->name, PDO::PARAM_STR);
            $stmt->bindParam(":lti_types_id", $lti->lti_types_id, PDO::PARAM_INT);
            $stmt->bindParam(":tool_url", $lti->tool_url, PDO::PARAM_STR);
            $stmt->bindParam(":text", $lti->text, PDO::PARAM_STR);
            $stmt->bindParam(":launch_container", $lti->launch_container, PDO::PARAM_STR);
            $stmt->bindParam(":custom_id", $lti->custom_id, PDO::PARAM_STR);
            $stmt->bindParam(":grade_max", $lti->grade_max_lti, PDO::PARAM_INT);
            $stmt->bindParam(":created_user", $lti->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":created_ts", $lti->date_time, PDO::PARAM_STR);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_last_value($v1, $v2)
    {
        try {
            $query = "select MAX($v1) from $v2";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function insert_gradeitems($lti)
    {
        try {
            $query = "INSERT INTO grade_items(contentdtl_id, name, lti_id, grade_type, grade_max, grade_min, grade_pass,grade_pass_actual, created_user, created_ts) VALUES (:contentdtl_id, :name, :lti_id, 1, :grade_max, :grade_min, :grade_pass,:grade_pass_actual, :created_user, :created_ts)";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":contentdtl_id", $lti->contentdtl_id, PDO::PARAM_INT);
            $stmt->bindParam(":name", $lti->name, PDO::PARAM_STR);
            $stmt->bindParam(":lti_id", $lti->lti_id, PDO::PARAM_INT);
            $stmt->bindParam(":grade_max", $lti->grade_max, PDO::PARAM_STR);
            $stmt->bindParam(":grade_min", $lti->grade_min, PDO::PARAM_STR);
            $stmt->bindParam(":grade_pass", $lti->grade_pass, PDO::PARAM_STR);
            $stmt->bindParam(":grade_pass_actual", $lti->grade_pass_actual, PDO::PARAM_STR);
            $stmt->bindParam(":created_user", $lti->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":created_ts", $lti->date_time, PDO::PARAM_STR);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }


    public function insert_user_grade($grade, $contentdtl_id, $grade_pass)
    {
        try {
            $query = "INSERT INTO lti_user_grades(offering_id, user_id, grade_items_id,raw_grade,raw_grade_actual,raw_grade_max,raw_grade_min,last_updated_ts) 
                VALUES (:offering_id,:user_id,:grade_items_id,:raw_grade,:raw_grade_actual,:raw_grade_max,:raw_grade_min,:last_updated_ts)";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $grade->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $grade->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":grade_items_id", $grade->grade_items_id, PDO::PARAM_INT);
            $stmt->bindParam(":raw_grade", $grade->raw_grade, PDO::PARAM_STR);
            $stmt->bindParam(":raw_grade_actual", $grade->raw_grade_actual, PDO::PARAM_STR);
            $stmt->bindParam(":raw_grade_max", $grade->raw_grade_max, PDO::PARAM_INT);
            $stmt->bindParam(":raw_grade_min", $grade->raw_grade_min, PDO::PARAM_STR);
            $stmt->bindParam(":last_updated_ts", $grade->timemodified, PDO::PARAM_STR);
            // execute query
            if ($stmt->execute()) {
                if ($this->update_content_track($grade, $contentdtl_id, $grade_pass)) {
                    return true;
                }
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function update_user_grade($grade, $contentdtl_id, $grade_pass)
    {
        try {
            $query = "UPDATE lti_user_grades set raw_grade=:raw_grade,raw_grade_actual=:raw_grade_actual,raw_grade_max=:raw_grade_max,raw_grade_min=:raw_grade_min,last_updated_ts=:last_updated_ts WHERE offering_id=:offering_id AND user_id=:user_id AND grade_items_id=:grade_items_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $grade->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $grade->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":grade_items_id", $grade->grade_items_id, PDO::PARAM_INT);
            $stmt->bindParam(":raw_grade", $grade->raw_grade, PDO::PARAM_STR);
            $stmt->bindParam(":raw_grade_actual", $grade->raw_grade_actual, PDO::PARAM_STR);
            $stmt->bindParam(":raw_grade_max", $grade->raw_grade_max, PDO::PARAM_INT);
            $stmt->bindParam(":raw_grade_min", $grade->raw_grade_min, PDO::PARAM_STR);
            $stmt->bindParam(":last_updated_ts", $grade->timemodified, PDO::PARAM_STR);
            // execute query
            if ($stmt->execute()) {
                if ($this->update_content_track($grade, $contentdtl_id, $grade_pass)) {
                    return true;
                }
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function update_content_track($grade, $contentdtl_id, $grade_pass)
    {
        try {
            $completed = null;
            $content_progress = 0;
            if ($grade->raw_grade >= $grade_pass) {
                $completed = "Y";
                $content_progress = "100";

                $query = "UPDATE content_track SET completed=:completed,content_progress=:content_progress WHERE offering_id=:offering_id AND user_id=:user_id AND contentdtl_id=:contentdtl_id";
                // prepare query statement
                $stmt = $this->conn->prepare($query);
                // bind limit clause variables
                $stmt->bindParam(":completed", $completed, PDO::PARAM_STR);
                $stmt->bindParam(":content_progress", $content_progress, PDO::PARAM_INT);
                $stmt->bindParam(":offering_id", $grade->offering_id, PDO::PARAM_INT);
                $stmt->bindParam(":user_id", $grade->user_id, PDO::PARAM_INT);
                $stmt->bindParam(":contentdtl_id", $contentdtl_id, PDO::PARAM_INT);
                // execute query
                if ($stmt->execute()) {
                    return true;
                }
            } else {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }


    public function get_content_details($contentdtl_id)
    {
        try {
            $query = "SELECT cc.course_id,con.contentdtl_id,con.content_title,con.content_desc FROM content_details as con,course_content as cc WHERE cc.content_id=con.content_id AND con.contentdtl_id=$contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function fetch_content_id()
    {
        try {
            $query = "SELECT max(content_id) AS content_id FROM content_details";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function insert_content_details($lti)
    {
        $content_desc = '';
        try {
            $query = "INSERT INTO content_details(content_id, content_type_id, scorm_id, xapi_id, content_title, content_desc, content_path, content_size, start_page, link_type, web_link, version_no, status, class_id, presenter_email, start_time, content_duration, attendee_limit, create_recording, recording_url, presenter_url, created_user, created_ts, last_updated_user, last_updated_ts) VALUES (:content_id, 10, 0, 0, :content_title, :content_desc, '', 0, '', '', '', 1, 'Active', null, null, null, null, null, null, null, null, :created_user, :created_ts, :last_updated_user, :last_updated_ts)";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":content_id", $lti->content_id, PDO::PARAM_INT);
            $stmt->bindParam(":content_title", $lti->name, PDO::PARAM_STR);
            $stmt->bindParam(":content_desc", $content_desc, PDO::PARAM_STR);
            $stmt->bindParam(":created_user", $lti->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":created_ts", $lti->date_time, PDO::PARAM_STR);
            $stmt->bindParam(":last_updated_user", $lti->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":last_updated_ts", $lti->date_time, PDO::PARAM_STR);
            // execute query
            if ($stmt->execute()) {
                //seq_no fetch
                $query1 = "SELECT max(seq_no)+1 as seq_no FROM course_content WHERE course_id=:course_id AND lesson_id=:lesson_id";
                // prepare query statement
                $stmt1 = $this->conn->prepare($query1);
                // bind limit clause variables
                $stmt1->bindParam(":course_id", $lti->course_id, PDO::PARAM_INT);
                $stmt1->bindParam(":lesson_id", $lti->lesson_id, PDO::PARAM_INT);
                if ($stmt1->execute()) {
                    $result = $stmt1->fetch(PDO::FETCH_OBJ);
                    if ($result->seq_no != null) {
                        $seq_no = $result->seq_no;
                    } else {
                        $seq_no = 1;
                    }
                    //course content insert
                    $query2 = "INSERT INTO course_content(course_id, lesson_id, content_id, seq_no, created_user, created_ts, last_updated_user, last_updated_ts) VALUES (:course_id, :lesson_id, :content_id, :seq_no,  :created_user, :created_ts, :last_updated_user, :last_updated_ts)";
                    // prepare query statement
                    $stmt2 = $this->conn->prepare($query2);
                    // bind limit clause variables
                    $stmt2->bindParam(":course_id", $lti->course_id, PDO::PARAM_INT);
                    $stmt2->bindParam(":lesson_id", $lti->lesson_id, PDO::PARAM_INT);
                    $stmt2->bindParam(":content_id", $lti->content_id, PDO::PARAM_INT);
                    $stmt2->bindParam(":seq_no", $seq_no, PDO::PARAM_INT);
                    $stmt2->bindParam(":created_user", $lti->user_id, PDO::PARAM_INT);
                    $stmt2->bindParam(":created_ts", $lti->date_time, PDO::PARAM_STR);
                    $stmt2->bindParam(":last_updated_user", $lti->user_id, PDO::PARAM_INT);
                    $stmt2->bindParam(":last_updated_ts", $lti->date_time, PDO::PARAM_STR);
                    $stmt2->execute();
                    return true;
                }
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    //Fetch User Details
    public function get_user_details($user_id)
    {
        try {
            $query = "SELECT user_id,first_name,last_name,mobile_no,email_id,login_id FROM user_account WHERE user_id=$user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Fetch User Company Details
    public function get_company_details_by_user($user_id)
    {
        try {
            $query = "SELECT c.company_id,c.company_code,c.name,c.private_key,c.kid FROM company as c,user_account as u WHERE u.company_id=c.company_id AND u.user_id=$user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_company_details($comp_id)
    {
        try {
            $query = "SELECT c.company_id,c.company_code,c.name,c.private_key,c.kid FROM company as c WHERE c.company_id=$comp_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Fetch Course Details
    public function get_course_details($course_id)
    {
        try {
            $query = "SELECT * FROM course WHERE course_id=$course_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Fetch User Details
    public function get_offering_details($offering_id)
    {
        try {
            $query = "SELECT * FROM course_offering WHERE offering_id=$offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Fetch User Role
    public function get_role_user($user_id)
    {
        try {
            $query = "SELECT user_id FROM user_roles WHERE user_id=$user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Fetch User Current Langugae
    public function get_language_user($user_id)
    {
        try {
            $query = "SELECT l.lang_cd_mobile FROM user_account as ua,pref_language as l WHERE ua.pref_lang_id=l.lang_id AND ua.user_id=$user_id AND l.lang_cd_mobile!=''";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_lti_types_admin($comp_id)
    {
        try {
            $query = "SELECT * FROM lti_types WHERE company_id=$comp_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_company_detail($company_id)
    {
        try {
            $query = "SELECT * FROM company  WHERE company_id=$company_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Score.php conditions
    public function check_offering_exists($offering_id)
    {
        try {
            $query = "SELECT offering_id FROM course_offering WHERE offering_id=$offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Token related
    public function token_record_exists($token)
    {
        try {
            $query = "SELECT lti_access_tokens_id,lti_types_id,scope,valid_until FROM lti_access_tokens WHERE token='$token'";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    //Insert new token
    public function insert_token($newtoken)
    {
        try {
            $query = "INSERT INTO lti_access_tokens(lti_types_id, scope, token, valid_until, last_access, created_time) 
                VALUES (:lti_types_id,:scope,:token,:valid_until,:last_access,:created_time)";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":lti_types_id", $newtoken->typeid, PDO::PARAM_INT);
            $stmt->bindParam(":scope", $newtoken->scope, PDO::PARAM_STR);
            $stmt->bindParam(":token", $newtoken->token, PDO::PARAM_INT);
            $stmt->bindParam(":valid_until", $newtoken->validuntil, PDO::PARAM_STR);
            $stmt->bindParam(":last_access", $newtoken->lastaccess, PDO::PARAM_INT);
            $stmt->bindParam(":created_time", $newtoken->timecreated, PDO::PARAM_STR);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function update_token_last_access($updatetoken)
    {
        $content_desc = '';
        try {
            $query = "UPDATE lti_access_tokens 
            SET last_access=:last_access
            WHERE lti_access_tokens_id=:lti_access_tokens_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":last_access", $updatetoken->last_access, PDO::PARAM_INT);
            $stmt->bindParam(":lti_access_tokens_id", $updatetoken->lti_access_tokens_id, PDO::PARAM_STR);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Result criteria checks
    public function get_lti_criteria($offering_id)
    {
        try {
            $query = "SELECT lti_id,weightage FROM result_criteria WHERE offering_id=$offering_id and lti_id!=0";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_user_lti_score($offering_id, $user_id, $lti_id)
    {
        try {
            $query = "SELECT lug.raw_grade,g.grade_pass FROM lti_user_grades as lug,grade_items as g 
            where lug.grade_items_id=g.grade_items_id AND lug.offering_id=$offering_id AND lug.user_id=$user_id AND g.lti_id=$lti_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_assessment_criteria($offering_id)
    {
        try {
            $query = "SELECT rc.*,a.assessment_name,a.assessment_id FROM result_criteria as rc,assessment as a 
            where rc.offering_id=$offering_id and a.assessment_id=rc.final_assmt_id and rc.final_assmt_id!=0";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_assessment_attempt($offering_id, $user_id, $f_id)
    {
        try {
            $query = "SELECT attempt_no,score,passing_score as pass_score FROM quiz_score 
            where offering_id=$offering_id and user_id=$user_id and assessment_id=$f_id ORDER BY attempt_no desc limit 1";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_offering_passing_score($offering_id)
    {
        try {
            $query = "SELECT passing_score from course_offering WHERE offering_id=$offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_user_offering_max_attempt($offering_id)
    {
        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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_scorm_criteria($offering_id)
    {
        try {
            $query = "SELECT scorm_id,weightage FROM result_criteria 
            WHERE offering_id=$offering_id and scorm_id!=0";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_user_scorm_status($offering_id, $user_id, $scorm_id)
    {
        try {
            $query = "SELECT value FROM scorm_track WHERE scorm_id=$scorm_id AND 
            offering_id=$offering_id AND user_id=$user_id AND model='cmi.core.lesson_status'";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_user_scorm_score($offering_id, $user_id, $scorm_id)
    {
        try {
            $query = "SELECT value FROM scorm_track WHERE scorm_id=$scorm_id AND 
            offering_id=$offering_id AND user_id=$user_id AND model='cmi.core.score.raw'";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_xapi_criteria($offering_id)
    {
        try {
            $query = "SELECT xapi_id,weightage FROM result_criteria 
            WHERE offering_id=$offering_id and xapi_id!=0";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_user_xapi_status($offering_id, $user_id, $xapi_id)
    {
        try {
            $query = "SELECT status FROM xapi_track WHERE xapi_id=$xapi_id AND 
            offering_id=$offering_id AND user_id=$user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_user_xapi_score($offering_id, $user_id, $xapi_id)
    {
        try {
            $query = "SELECT actual_score FROM xapi_track WHERE xapi_id=$xapi_id AND 
            offering_id=$offering_id AND user_id=$user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function update_user_offering_incomplete_or_null($course_assignment)
    {
        $content_desc = '';
        try {
            $query = "UPDATE course_assignment 
            SET 
            completion=(SELECT FLOOR(((COUNT(cc.contentdtl_id ))*100)/(SELECT count(oc.contentdtl_id) 
            FROM offering_content as oc where oc.offering_id=:offering_id3 AND oc.status='Active')) as cmp 
            FROM offering_content as cc WHERE cc.offering_id =:offering_id1 AND cc.status='Active' AND EXISTS 
            (SELECT 1 FROM content_track as ct WHERE ct.offering_id =:offering_id2 AND ct.user_id =:user_id1 AND 
            cc.contentdtl_id = ct.contentdtl_id AND ct.completed='Y')), 
            content_completion=COALESCE((SELECT ROUND((SUM(ct.content_progress))/(SELECT count(oc.contentdtl_id) 
            FROM offering_content as oc where oc.offering_id=:offering_id4 AND oc.status='Active'),0)
            FROM content_track as ct,offering_content as oc  WHERE ct.offering_id =:offering_id5 AND ct.user_id =:user_id2 AND oc.contentdtl_id=ct.contentdtl_id AND ct.offering_id=oc.offering_id),0),
            attended_date=:attended_date,
            result=:result,
            score=:score,
            last_updated_user=:last_updated_user,
            last_updated_ts=:last_updated_ts 
            WHERE offering_id=:offering_id AND user_id=:user_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":attended_date", $course_assignment->date, PDO::PARAM_STR);
            $stmt->bindParam(":result", $course_assignment->result, PDO::PARAM_STR);
            $stmt->bindParam(":score", $course_assignment->score, PDO::PARAM_STR);
            $stmt->bindParam(":last_updated_user", $course_assignment->last_updated_user, PDO::PARAM_INT);
            $stmt->bindParam(":last_updated_ts", $course_assignment->date, PDO::PARAM_STR);
            $stmt->bindParam(":offering_id", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id1", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id2", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id3", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id4", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id5", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $course_assignment->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id1", $course_assignment->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id2", $course_assignment->user_id, PDO::PARAM_INT);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function check_completion_point($offering_id)
    {
        try {
            $query = "SELECT Group_concat(completion_points) as points 
            FROM certificate_assignment WHERE offering_id=$offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_completion_point($offering_id, $final_score)
    {
        try {
            $query = "SELECT completion_points FROM certificate_assignment 
            WHERE offering_id=$offering_id AND cut_off<=$final_score
            ORDER BY cut_off DESC LIMIT 1";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_completion_point_cutoff_null($offering_id)
    {
        try {
            $query = "SELECT completion_points FROM certificate_assignment 
            WHERE offering_id=$offering_id and cut_off is NULL";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_completion_point_offering($offering_id)
    {
        try {
            $query = "SELECT co.completion_points from course_offering as co 
            where co.offering_id=$offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function update_user_offering_complete($course_assignment)
    {
        try {
            $query = "UPDATE course_assignment 
            SET 
            completion=(SELECT FLOOR(((COUNT(cc.contentdtl_id ))*100)/(SELECT count(oc.contentdtl_id) 
            FROM offering_content as oc where oc.offering_id=:offering_id3 AND oc.status='Active')) as cmp 
            FROM offering_content as cc WHERE cc.offering_id =:offering_id1 AND cc.status='Active' AND EXISTS 
            (SELECT 1 FROM content_track as ct WHERE ct.offering_id =:offering_id2 AND ct.user_id =:user_id1 AND 
            cc.contentdtl_id = ct.contentdtl_id AND ct.completed='Y')), 
            content_completion=COALESCE((SELECT ROUND((SUM(ct.content_progress))/(SELECT count(oc.contentdtl_id) 
            FROM offering_content as oc where oc.offering_id=:offering_id4 AND oc.status='Active'),0)
            FROM content_track as ct,offering_content as oc  WHERE ct.offering_id =:offering_id5 AND ct.user_id =:user_id2 AND oc.contentdtl_id=ct.contentdtl_id AND ct.offering_id=oc.offering_id),0),
            attended_date=:attended_date,
            result=:result,
            score=:score,
            completion_points =:completion_points,
            last_updated_user=:last_updated_user,
            last_updated_ts=:last_updated_ts 
            WHERE offering_id=:offering_id AND user_id=:user_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":attended_date", $course_assignment->date, PDO::PARAM_STR);
            $stmt->bindParam(":result", $course_assignment->result, PDO::PARAM_STR);
            $stmt->bindParam(":score", $course_assignment->score, PDO::PARAM_STR);
            $stmt->bindParam(":completion_points", $course_assignment->completion_points, PDO::PARAM_STR);
            $stmt->bindParam(":last_updated_user", $course_assignment->last_updated_user, PDO::PARAM_INT);
            $stmt->bindParam(":last_updated_ts", $course_assignment->date, PDO::PARAM_STR);
            $stmt->bindParam(":offering_id", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id1", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id2", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id3", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id4", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id5", $course_assignment->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $course_assignment->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id1", $course_assignment->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id2", $course_assignment->user_id, PDO::PARAM_INT);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_sum_of_completion_point($user_id)
    {
        try {
            $query = "SELECT SUM(completion_points) as count 
            FROM course_assignment WHERE result='complete' and user_id=$user_id AND status IS NULL";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function update_user_completion_point($user_id, $count, $date_time, $batch_user_id)
    {
        try {
            $query = "UPDATE user_account 
            SET total_completion_points=:count,last_updated_user=:batch_user_id,
            last_updated_ts=:date_time 
            WHERE user_id=:user_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":count", $count);
            $stmt->bindParam(":batch_user_id", $batch_user_id, PDO::PARAM_INT);
            $stmt->bindParam(":date_time", $date_time, PDO::PARAM_STR);
            $stmt->bindParam(":user_id", $user_id, PDO::PARAM_INT);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    public function get_lti_by_contentdtl_id($lti_id, $contentdtl_id)
    {
        try {
            $query = "SELECT * FROM lti WHERE lti_id=$lti_id AND contentdtl_id=$contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function check_lti_and_lti_types($lti_id, $contentdtl_id, $type_id)
    {
        try {
            $query = "SELECT lti.* FROM lti as lti INNER JOIN lti_types as typ ON lti.lti_types_id = typ.lti_types_id
                WHERE lti.lti_id = $lti_id
                AND lti.contentdtl_id = $contentdtl_id
                AND typ.lti_types_id = $type_id";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function check_offering_is_null($offering_id, $user_id)
    {
        try {
            $query = "SELECT result FROM course_assignment WHERE offering_id = $offering_id AND user_id = $user_id AND result is null";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
}