<?php

class Offering {

    private $conn;
    private $course_offering = "course_offering";
    private $offering_lesson = "offering_lesson";
    private $offering_content = "offering_content";
    private $result_criteria = "result_criteria";
    private $course_assignment = "course_assignment";
    private $rating = "rating";
    private $course = "course";
    private $course_category = "course_category";
    public $offering_id;
    public $course_id;
    public $offering_name;
    public $course_name;
    public $course_description;
    public $course_duration;
    public $daily_study_hours;
    public $start_date;
    public $end_date;
    public $passing_score;
    public $max_attempts;
    public $assmt_duration;
    public $score_flag;
    public $coaching_flag;
    public $status;
    public $created_ts;
    public $last_updated_ts;
    public $lesson_id;
    public $assessment_id;
    public $seq_no;
    public $contentdtl_id;
    public $off_val_date;
    //added
    public $time_diff;
    public $scorm_id;
    public $weightage;
    public $user_id = 0;
    public $group_id;
    public $attended_date;
    public $result;
    public $score;
    public $assignment_type;
    public $course_assigned_ts;
    public $rating_id;
    public $rating_value;
    public $rated_date;
    public $comments;
    public $cart_id;
    public $category_id;
    public $category_name;
    public $date;
    public $datetime;
    public $format;
    public $badge_id;
    public $template_id;
    public $address_line1;
    public $address_line2;
    public $city;
    public $state;
    public $country;
    public $pin_code;
    public $file;
    public $completed;
    public $total_time;
    public $learning_path_id = 0;

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

    function checkCourseAssigned() {
        try {
            $query = "SELECT ca.offering_id FROM course_assignment as ca WHERE ca.offering_id=:offering_id AND ca.status IS NULL and user_id=:user_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //////////////////NON ECOMMERCE QUERIES//////////////////////////////

    function getCourses_Home($from_record_num, $records_per_page) {
        $query = "SELECT  co.offering_id,co.course_id,co.course_name,co.course_description,cad.start_date,cad.end_date,co.leader_board_flag,ca.result,ca.content_completion,co.is_scorm,co.max_attempts,opr.media_path,co.offering_type,"
                . "if((SELECT count(oc.contentdtl_id) FROM offering_content as oc where oc.offering_id=co.offering_id), True, False) as content_count "
                . "FROM course_assignment as ca "
                . "JOIN course_offering co ON ca.offering_id=co.offering_id "
                . "JOIN course_assignment_date as cad ON ca.offering_id=cad.offering_id AND ca.user_id=cad.user_id "
                . "LEFT JOIN offering_preview opr ON co.offering_id=opr.offering_id AND opr.media_type='I' 
                    WHERE ca.user_id=:user_id AND ca.offering_id=co.offering_id 
                    AND co.status='Active' AND ca.status IS NULL  
                    AND co.end_date >=:date1 
                    AND co.start_date <=:date2 order by co.end_date LIMIT :start, :end";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
        $stmt->bindParam(":date1", $this->date, PDO::PARAM_STR);
        $stmt->bindParam(":date2", $this->date, PDO::PARAM_STR);
        $stmt->bindParam(":start", $from_record_num, PDO::PARAM_INT);
        $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt;
    }

    function getCourses_Home_count() {
        try {
            $query = "SELECT  COUNT(co.offering_id)"
                . "FROM course_assignment as ca "
                . "JOIN course_offering co ON ca.offering_id=co.offering_id "
                . "JOIN course_assignment_date as cad ON ca.offering_id=cad.offering_id AND ca.user_id=cad.user_id "
                . "LEFT JOIN offering_preview opr ON co.offering_id=opr.offering_id AND opr.media_type='I' 
                    WHERE ca.user_id=:user_id AND ca.offering_id=co.offering_id 
                    AND co.status='Active' AND ca.status IS NULL  
                    AND co.end_date >=:date1 
                    AND co.start_date <=:date2 order by co.end_date";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
        $stmt->bindParam(":date1", $this->date, PDO::PARAM_STR);
        $stmt->bindParam(":date2", $this->date, PDO::PARAM_STR);
        $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return count
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function check_course_criteria() {
        try {
            $query = "SELECT offering_id from result_criteria as rc where rc.offering_id=:offering_id";
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function check_course_criteria_ref() {
        try {
            $query = "SELECT co.offering_id,co.completion_points from course_offering as co where co.offering_id=:offering_id and co.end_date >=:date1 "
                    . "AND NOT EXISTS (SELECT offering_id from result_criteria as rc where rc.offering_id=co.offering_id)";
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            $stmt->bindParam(":date1", $this->date, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function offering_current_track_count($offering_id, $user_id) {
        try {
            $query = "SELECT COUNT( cc.contentdtl_id ) as track_count
             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_id
                    AND cc.contentdtl_id = ct.contentdtl_id AND ct.completed='Y')
                    GROUP BY cc.offering_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":user_id", $user_id, PDO::PARAM_STR);
            $stmt->bindParam(":offering_id1", $offering_id, PDO::PARAM_STR);
            $stmt->bindParam(":offering_id2", $offering_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return count
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function offering_total_content_count($offering_id) {
        try {
            $query = "SELECT count(oc.contentdtl_id) as total_count FROM offering_content as oc where oc.offering_id=:offering_id and status='Active' ";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $offering_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return count
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_course_name_home() {
        try {
            $query = "select distinct course_name from course where status!='Inactive'";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

//query changed 18-2-2022 as per the image changes
    //limit 9 removed from query for newer design on 01/06/2023
    function getoffers_achv() {
        try {
            $query = "SELECT DISTINCT co.offering_id,ca.result,ca.score,ca.user_id,co.course_name,ca.attended_date "
                    . "FROM course_assignment as ca,course_offering as co,certificate_assignment as ct "
                    . "WHERE ca.user_id=:user_id "
                    . "AND ca.offering_id=co.offering_id "
                    . "AND co.offering_id=ct.offering_id "
                    . "AND ct.cut_off<=ca.score "
                    . "AND co.status='Active' AND ca.result='complete' AND ca.status IS NULL "
                    . "ORDER BY ca.attended_date desc";
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getgrade_achv() {
        try {
            $query = "SELECT ct.badge_id,ct.grade_type,ct.grade,ct.cut_off 
                        FROM certificate_assignment as ct
                        where ct.offering_id=:offering_id and ct.cut_off<=:score ORDER BY ct.cut_off desc Limit 1";
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            $stmt->bindParam(":score", $this->score, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getgrade_file_achv() {
        try {
            $query = "SELECT gold_file,silver_file,bronze_file "
                    . "FROM certificate_badge as cb "
                    . "where badge_id=:badge_id";
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":badge_id", $this->badge_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getleaders() {
        try {
            $query = "SELECT ca.offering_id, ca.user_id, ca.score, ua.first_name, ua.last_name, ua.user_image,ua.login_id,ca.attended_date, 
            MAX( q.attempt_no ) AS attempt_no, co.score_flag 
            FROM course_assignment AS ca 
            INNER JOIN course_offering AS co ON co.offering_id = ca.offering_id 
            LEFT JOIN quiz_score AS q ON q.user_id = ca.user_id 
            AND q.offering_id = ca.offering_id 
            INNER JOIN user_account AS ua ON ua.user_id = ca.user_id AND ua.company_id=(SELECT company_id from user_account where user_id=:user_id)
            LEFT JOIN course_assignment ca2 ON ca2.offering_id = ca.offering_id 
            AND ca2.score > ca.score 
            WHERE ca.offering_id =:offering_id 
                AND ca.result =  'complete' AND ca.status IS NULL
                GROUP BY ca.user_id, ca.offering_id,ca.group_id 
                HAVING COUNT( DISTINCT ca2.score ) <3 
                ORDER BY ca.score DESC , attempt_no ASC , ca.attended_date ASC";
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getCompletionCount() {
        try {
            $query = "SELECT count(ca.offering_id) as overall_result
                          FROM course_assignment as ca
                          INNER JOIN user_account AS ua ON ua.user_id = ca.user_id AND ua.company_id=(SELECT company_id from user_account where user_id=:user_id) 
                          WHERE ca.offering_id=:offering_id AND ca.result='complete' AND ca.status IS NULL";
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getTotalCompletionCount() {
        try {
            $query = "SELECT count(ca.user_id) as total_user
                      FROM course_assignment as ca
                      INNER JOIN user_account AS ua ON ua.user_id = ca.user_id AND ua.company_id=(SELECT company_id from user_account where user_id=:user_id) 
                      WHERE ca.offering_id=:offering_id AND ca.status IS NULL";
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function activecourses($from_record_num, $records_per_page) {
        try {
            $query = "SELECT co.offering_id,ca.group_id,co.course_name,co.course_description,ca.completion,ca.content_completion,cad.start_date,cad.end_date,opr.media_path,co.offering_type,
            if((SELECT count(oc.contentdtl_id) FROM offering_content as oc where oc.offering_id=co.offering_id), True, False) as content_count, 
            (Case ca.group_id 
            When 0 THEN '' 
            Else (SELECT group_name From user_group 
            Where ca.group_id =  user_group.group_id ) END) As group_name 
            FROM course_assignment as ca 
            JOIN course_offering co ON ca.offering_id=co.offering_id
            JOIN course_assignment_date as cad ON ca.offering_id=cad.offering_id AND ca.user_id=cad.user_id 
            LEFT JOIN offering_preview opr ON co.offering_id=opr.offering_id AND opr.media_type='I'
            WHERE ca.user_id=:user_id AND co.status='Active' AND ca.offering_id=co.offering_id 
            and (ca.result is NULL or ca.result='awaited') AND ca.status IS NULL ORDER BY co.end_date,co.course_name asc
            LIMIT :start, :end";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":start", $from_record_num, PDO::PARAM_INT);
            $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function activecourses_count() {
        try {
            $query = "SELECT COUNT(co.offering_id)
            FROM course_assignment as ca 
            JOIN course_offering co ON ca.offering_id=co.offering_id
            JOIN course_assignment_date as cad ON ca.offering_id=cad.offering_id AND ca.user_id=cad.user_id 
            LEFT JOIN offering_preview opr ON co.offering_id=opr.offering_id AND opr.media_type='I'
            WHERE ca.user_id=:user_id AND co.status='Active' AND ca.offering_id=co.offering_id 
            and (ca.result is NULL or ca.result='awaited') AND ca.status IS NULL ORDER BY co.end_date,co.course_name asc";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return count
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function activecourses_count_forHome() {
        try {
            $query = "SELECT count(co.offering_id) 
            FROM course_assignment as ca 
            JOIN course_offering co ON ca.offering_id=co.offering_id
            JOIN course_assignment_date as cad ON ca.offering_id=cad.offering_id AND ca.user_id=cad.user_id 
            WHERE ca.user_id=:user_id AND ca.offering_id=co.offering_id AND co.status='Active' 
            AND (ca.result is NULL or ca.result='awaited') AND ca.status IS NULL";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function completecourses($from_record_num, $records_per_page) {
        try {
            $query = "select ca.score,ca.result,ca.attended_date,ca.group_id,co.leader_board_flag,co.course_name,co.course_description,cad.start_date,opr.media_path,
            cad.end_date,co.offering_id,co.coaching_flag,co.score_flag,co.final_assmt_id,
             (Case ca.group_id 
             When 0 THEN '' 
             Else (SELECT group_name 
             From user_group 
             Where ca.group_id =  user_group.group_id) 
             END) As group_name 
             FROM course_assignment as ca
             JOIN course_offering co ON ca.offering_id=co.offering_id
             JOIN course_assignment_date as cad ON ca.offering_id=cad.offering_id AND ca.user_id=cad.user_id  
             LEFT JOIN offering_preview opr ON co.offering_id=opr.offering_id AND opr.media_type='I' 
             WHERE ca.user_id=:user_id AND ca.offering_id=co.offering_id AND ca.result='complete' 
             AND co.status='Active' AND ca.status IS NULL 
             order by ca.attended_date desc LIMIT :start,:end";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":start", $from_record_num, PDO::PARAM_INT);
            $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function completedcourses_count() {
        try {
            $query = "select COUNT(co.offering_id)
             FROM course_assignment as ca
             JOIN course_offering co ON ca.offering_id=co.offering_id
             JOIN course_assignment_date as cad ON ca.offering_id=cad.offering_id AND ca.user_id=cad.user_id  
             LEFT JOIN offering_preview opr ON co.offering_id=opr.offering_id AND opr.media_type='I' 
             WHERE ca.user_id=:user_id AND ca.offering_id=co.offering_id AND ca.result='complete' 
             AND co.status='Active' AND ca.status IS NULL 
             order by ca.attended_date desc ";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return count
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function completedcourses_count_forHome() {
        try {
            $query = "SELECT count(co.offering_id)
             FROM course_assignment as ca,course_offering as co
             WHERE ca.user_id=:user_id 
             AND co.status='Active' AND ca.status IS NULL 
             AND ca.offering_id=co.offering_id AND ca.result='complete'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function incompletecourses($from_record_num, $records_per_page) {
        try {
            $query = "SELECT ca.score,ca.result,ca.attended_date,ca.group_id,co.course_name,co.course_description, 
           cad.start_date,cad.end_date,co.final_assmt_id,co.offering_id,co.coaching_flag,co.score_flag,opr.media_path,
           (Case ca.group_id When 0 THEN '' 
           Else (SELECT group_name 
           From user_group 
           Where ca.group_id =  user_group.group_id) END) As group_name 
           FROM course_assignment as ca 
           JOIN course_offering co ON ca.offering_id=co.offering_id
           JOIN course_assignment_date as cad ON ca.offering_id=cad.offering_id AND ca.user_id=cad.user_id  
           LEFT JOIN offering_preview opr ON co.offering_id=opr.offering_id AND opr.media_type='I' 
           WHERE ca.user_id=:user_id AND co.status='Active' AND ca.offering_id=co.offering_id 
           AND ca.result='incomplete' AND ca.status IS NULL ORDER BY co.end_date,co.course_name asc 
           LIMIT :start,:end";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":start", $from_record_num, PDO::PARAM_INT);
            $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function incompletecourses_count() {
        try {
            $query = "SELECT COUNT(co.offering_id)
            FROM course_assignment as ca 
            JOIN course_offering co ON ca.offering_id=co.offering_id
            JOIN course_assignment_date as cad ON ca.offering_id=cad.offering_id AND ca.user_id=cad.user_id  
            LEFT JOIN offering_preview opr ON co.offering_id=opr.offering_id AND opr.media_type='I' 
            WHERE ca.user_id=:user_id AND co.status='Active' AND ca.offering_id=co.offering_id 
            AND ca.result='incomplete' AND ca.status IS NULL ORDER BY co.end_date,co.course_name asc ";
             $stmt = $this->conn->prepare($query);
             $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
             $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
           
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function incompletecourses_count_forHome() {
        try {
            $query = "SELECT count(co.offering_id)
           FROM course_assignment as ca,course_offering as co 
           LEFT JOIN offering_preview opr ON co.offering_id=opr.offering_id AND opr.media_type='I' 
           WHERE ca.user_id=:user_id AND co.status='Active' AND ca.offering_id=co.offering_id 
           AND ca.result='incomplete' AND ca.status IS NULL";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Common
    function offering_display() {
        try {
            $query = "SELECT ca.score,ca.attended_date,ca.result,ca.completion_points,co.course_name,co.course_description,co.course_objective,co.course_prerequisites,co.course_duration,co.daily_study_hours,co.final_assmt_id,cc.category_name,co.offering_type, "
                    . "co.max_attempts,co.passing_score,co.score_flag,co.assmt_duration,co.online,assmt_startdate,co.assmt_enddate,opr.media_path,co.start_date,co.end_date,co.course_id,co.coaching_flag,"
                    . "cad.start_date as display_start_date,cad.end_date as display_end_date,"
                    . "(Case ca.group_id When 0 THEN '' Else (SELECT group_name From user_group Where ca.group_id = user_group.group_id) 
                    END) As group_name "
                    . "FROM course_assignment as ca "
                    . "JOIN course_offering co ON ca.offering_id=co.offering_id "
                    . "LEFT JOIN course c ON c.course_id=co.course_id "
                    . "LEFT JOIN course_category cc ON c.category_id=cc.category_id "
                    . "JOIN course_assignment_date as cad ON ca.offering_id=cad.offering_id AND ca.user_id=cad.user_id "
                    . "LEFT JOIN offering_preview opr ON co.offering_id=opr.offering_id AND opr.media_type='I' "
                    . "WHERE ca.offering_id=:offering_id AND ca.user_id=:user_id AND ca.status IS NULL";

            $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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getOfferVideo() {
        try {
            $query = "SELECT opr.media_path  
            FROM course_offering as co, offering_preview as opr ON co.offering_id=opr.offering_id AND opr.media_type='I' "
                    . "WHERE co.offering_id=:offering_id and co.offering_id=opr.offering_id AND opr.media_type='V'";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function offer_display_course_lesson() {
        try {
            $query = "SELECT ol.lesson_id,l.lesson_name,l.lesson_desc,ol.lesson_dependency,ol.seq_no,COUNT(oc.contentdtl_id) AS oc_count_ind,COUNT(ct.contentdtl_id) AS ct_count_ind  FROM lesson as l 
                  JOIN offering_lesson as ol ON ol.lesson_id=l.lesson_id AND ol.offering_id=:offering_id AND ol.status='Active'
                  LEFT JOIN offering_content as oc ON  oc.lesson_id=ol.lesson_id AND oc.offering_id=ol.offering_id AND oc.status='Active'
                  LEFT JOIN content_track as ct ON oc.offering_id=ct.offering_id AND oc.contentdtl_id=ct.contentdtl_id AND ct.user_id=:user_id AND ct.completed='Y' 
                  GROUP BY ol.lesson_id,ol.lesson_dependency,ol.seq_no ORDER BY ol.seq_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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function content_count_lesson() {
        try {
            $query = "SELECT COUNT(oc.contentdtl_id) AS total_content,COUNT(ct.contentdtl_id) AS viewed_content "
                    . "FROM offering_content as oc "
                    . "LEFT JOIN content_track as ct ON oc.offering_id=ct.offering_id AND oc.contentdtl_id=ct.contentdtl_id AND ct.user_id=:user_id AND ct.completed='Y' "
                    . "WHERE oc.offering_id=:offering_id AND oc.status='Active' and oc.lesson_id in(SELECT lesson_id FROM offering_lesson WHERE seq_no<:seq_no AND offering_id=:offering_id1 and status='Active')";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id1", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":seq_no", $this->seq_no, PDO::PARAM_INT);
            //$stmt->bindParam(":lesson_ids", $lesson_ids, PDO::PARAM_STR);
            $stmt->execute();
            //return $query;
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function lesson_level_progress() {
        try {
            $query = "SELECT SUM(ct.content_progress) AS content_progress "
                    . "FROM content_track as ct where ct.offering_id=:offering_id AND ct.user_id=:user_id  AND "
                    . "ct.contentdtl_id in(select oc.contentdtl_id from offering_content as oc where oc.lesson_id=:lesson_id and oc.offering_id=:offering_id1 AND oc.status='Active')";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id1", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":lesson_id", $this->lesson_id, PDO::PARAM_INT);
            $stmt->execute();
            //return $query;
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function content_calculation() {
        try {
            $query = "SELECT COUNT(oc.contentdtl_id) AS total_content,COUNT(ct.contentdtl_id) AS viewed_content "
                    . "FROM offering_content as oc "
                    . "LEFT JOIN content_track as ct ON oc.offering_id=ct.offering_id AND oc.contentdtl_id=ct.contentdtl_id AND ct.user_id=:user_id AND ct.completed='Y' "
                    . "WHERE oc.offering_id=:offering_id AND  oc.status='Active' AND oc.lesson_id in(SELECT lesson_id FROM offering_lesson WHERE seq_no<:seq_no AND offering_id=:offering_id1 AND status='Active')";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id1", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":seq_no", $this->seq_no, PDO::PARAM_INT);
            //$stmt->bindParam(":lesson_ids", $lesson_ids, PDO::PARAM_STR);
            $stmt->execute();
            //return $query;
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function lesson_content_dtls() {
        try {
            $query = "SELECT l.lesson_name, ol.lesson_id,cd.contentdtl_id,cd.content_title,ct.completed "
                    . "FROM content_details as cd JOIN offering_content as oc JOIN offering_lesson as ol JOIN lesson as l "
                    . "LEFT JOIN content_track as ct ON ct.offering_id=oc.offering_id AND ct.contentdtl_id=oc.contentdtl_id AND ct.user_id=:user_id "
                    . "WHERE cd.contentdtl_id=oc.contentdtl_id AND oc.offering_id=:offering_id AND ol.offering_id=oc.offering_id AND ol.lesson_id=l.lesson_id AND oc.lesson_id=ol.lesson_id AND ol.status='Active' AND oc.status='Active' AND  l.lesson_id in(SELECT lesson_id FROM offering_lesson WHERE seq_no<:seq_no AND offering_id=:offering_id1 AND status='Active')";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id1", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":seq_no", $this->seq_no, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            //$stmt->bindParam(":lesson_ids", $lesson_ids, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function all_lesson_completed_check() {
        try {
            $query = "SELECT COUNT(oc.contentdtl_id) AS oc_count_all,COUNT(ct.contentdtl_id) AS ct_count_all "
                    . "FROM offering_content as oc "
                    . "LEFT JOIN content_track as ct ON oc.offering_id=ct.offering_id AND oc.contentdtl_id=ct.contentdtl_id AND ct.user_id=:user_id AND ct.completed='Y' "
                    . "WHERE oc.offering_id=:offering_id AND oc.status='Active'";
            $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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function offer_display_course_content() {
        try {
            $query = "SELECT ct.content_type_name,ct.content_type_id,cd.content_title,cd.content_type_id,cd.content_desc,cd.contentdtl_id,cd.content_id,cd.content_path,cd.content_size,cd.scorm_id,cd.xapi_id,"
                    . "if((SELECT ct.offering_id from content_track as ct WHERE ct.offering_id=:offering_id AND ct.user_id=:user_id AND ct.contentdtl_id=cd.contentdtl_id AND ct.completed='Y')is null, False, True) as offering_count, "
                    . "(select ct1.time_diff from content_track as ct1 where ct1.offering_id=:offering_id1 AND  ct1.user_id=:user_id1 AND ct1.contentdtl_id=cd.contentdtl_id) as time_diff "
                    . "FROM content_details as cd,offering_content as oc,content_type as ct "
                    . "WHERE cd.contentdtl_id=oc.contentdtl_id AND ct.content_type_id=cd.content_type_id AND oc.offering_id=:offering_id2 and oc.lesson_id=:lesson_id AND oc.status='Active' order by oc.seq_no ASC";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id1", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id2", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":lesson_id", $this->lesson_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id1", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function offer_cyu_lesson() {
        try {
            $query = "SELECT a.assessment_name,ol.assessment_id "
                    . "FROM offering_lesson as ol,assessment as a  WHERE a.assessment_id=ol.assessment_id AND ol.lesson_id=:lesson_id AND ol.offering_id=:offering_id AND ol.status='Active'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":lesson_id", $this->lesson_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_assessment_name() {
        try {
            $query = "SELECT a.assessment_name,co.assessment_dependency FROM assessment as a,course_offering as co where co.final_assmt_id=a.assessment_id  AND co.offering_id=:offering_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");
        }
    }

    function check_result_criteria() {
        try {
            $query = "SELECT rc.offering_id FROM result_criteria as rc where rc.offering_id=:offering_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");
        }
    }

    public function offering_assessment_no_questions() {
        try {
            $query = "SELECT count(aq.question_id) as no_questions from assessment as a,course_offering as co,assessment_qp as aq WHERE co.final_assmt_id=a.assessment_id AND a.assessment_id=aq.assessment_id AND co.offering_id=:offering_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");
        }
    }

    public function offering_display_assessment_randomization() {
        try {
            $query = "select SUM(ac.question_count) as question_count from assessment_criteria as  ac where ac.offering_id=:offering_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");
        }
    }

//    public function offering_current_attempt() {
//        $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, PDO::PARAM_INT);
//        $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
//        $stmt->execute();
//        return $stmt;
//    }

    public function offering_current_attempt() {
        try {
            $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";
            $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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_assessment_score_card() {
        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->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_assessment_score_card_complete_incomplete() {
        try {
            $query = "SELECT qz.*,a.assessment_name FROM quiz_score as qz,assessment as a where qz.offering_id=:offering_id AND qz.user_id=:user_id AND qz.assessment_id=a.assessment_id order by qz.attempt_no desc 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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_assessment_score_card_complete_incomplete_all() {
        try {
            $query = "SELECT qz.*,a.assessment_name FROM quiz_score as qz,assessment as a where qz.offering_id=:offering_id AND qz.user_id=:user_id AND qz.assessment_id=a.assessment_id  ORDER BY offering_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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_user_score() {
        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=:assessment_id ORDER BY attempt_no desc 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(":assessment_id", $this->assessment_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_scorm_score_card() {
        try {
            $query = "SELECT c.scorm_id,c.content_title,s.mastery_score "
                    . "FROM content_details as c,scorm as s,offering_content as oc "
                    . "WHERE oc.offering_id=:offering_id and oc.contentdtl_id=c.contentdtl_id and c.scorm_id=s.scorm_id and oc.status='Active'";
            $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");
        }
    }

    public function get_scorm_score_card_considered() {
        try {
            $query = "SELECT rs.* FROM result_criteria as rs,content_details as cd,offering_content as oc where  "
                    . "oc.contentdtl_id=cd.contentdtl_id and oc.status='Active' and cd.scorm_id=rs.scorm_id and "
                    . "rs.offering_id=:offering_id and rs.scorm_id=:scorm_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":scorm_id", $this->scorm_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_scorm_score_card_considered_active_inactive() {
        try {
            $query = "SELECT scr.* FROM scorm_offer_criteria as scr,content_details as cd,offering_content as oc where  "
                    . "oc.contentdtl_id=cd.contentdtl_id and oc.status='Active' and cd.scorm_id=scr.scorm_id and "
                    . "scr.offering_id=:offering_id and scr.scorm_id=:scorm_id and user_id=:user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":scorm_id", $this->scorm_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_scorm_score_user() {
        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->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":scorm_id", $this->scorm_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_scorm_lesson_status_user() {
        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->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":scorm_id", $this->scorm_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function offering_count_instructor() {
        try {
            $query = "SELECT count(oi.instructor_id) as instructor_id "
                    . "FROM user_account as u,offering_instructor as oi "
                    . "WHERE u.user_id=oi.instructor_id AND oi.offering_id=:offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            // execute query
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return count
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function total_assessment_taken() {
        try {
            $query = "SELECT qz.*,a.assessment_name FROM quiz_score as qz,assessment as a "
                    . "WHERE qz.offering_id=:offering_id and qz.user_id=:user_id AND a.assessment_id=qz.assessment_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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function track_content_view() {
        try {
            $query = "INSERT INTO
                content_track
            SET
                offering_id = :offering_id,
                user_id = :user_id,
                contentdtl_id = :contentdtl_id,
                last_viewed_ts = :last_viewed_ts
                ON DUPLICATE KEY UPDATE last_viewed_ts=:date";
            // 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(":contentdtl_id", $this->contentdtl_id);
            $stmt->bindParam(":last_viewed_ts", $this->datetime);
            $stmt->bindParam(":date", $this->datetime);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function track_content_download() {
        try {
            $query = "INSERT INTO
                content_track
            SET
                offering_id = :offering_id,
                user_id = :user_id,
                contentdtl_id = :contentdtl_id,
                last_downloaded_ts = :last_downloaded_ts
                ON DUPLICATE KEY UPDATE last_downloaded_ts=:date";
            // 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(":contentdtl_id", $this->contentdtl_id);
            $stmt->bindParam(":last_downloaded_ts", $this->datetime);
            $stmt->bindParam(":date", $this->datetime);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function track_content_complete() {
        try {
            $query = "UPDATE content_track SET completed ='Y',content_progress=100,last_viewed_ts=:last_viewed_ts WHERE user_id=:user_id AND offering_id=:offering_id AND contentdtl_id=:contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":contentdtl_id", $this->contentdtl_id);
            $stmt->bindParam(":last_viewed_ts", $this->datetime);
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function track_content_incomplete() {
        try {
            $query = "UPDATE content_track SET completed=NULL,content_progress=0,last_viewed_ts=:last_viewed_ts WHERE user_id=:user_id AND offering_id=:offering_id AND contentdtl_id=:contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":contentdtl_id", $this->contentdtl_id);
            $stmt->bindParam(":last_viewed_ts", $this->datetime);
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function update_completion($completion) {
        try {
            $query = "UPDATE course_assignment SET completion =:completion,last_updated_ts=:last_updated_ts,last_updated_user=:last_updated_user WHERE user_id=:user_id AND offering_id=:offering_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->bindParam(":completion", $completion);
            $stmt->bindParam(":last_updated_user", $this->user_id);
            $stmt->bindParam(":last_updated_ts", $this->datetime);
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function cal_update_completion() {
        try {
            $query = "UPDATE course_assignment set last_updated_user=:last_updated_user,last_updated_ts=:last_updated_ts,"
                    . "completion=(SELECT FLOOR(((COUNT(cc.contentdtl_id ))*100)/(SELECT count(oc.contentdtl_id) "
                    . "FROM offering_content as oc where oc.offering_id=:offering_id 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_id 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) "
                    . "where offering_id=:offering_id3 and user_id=:user_id1 AND (status IS NULL or status='awaited')";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":offering_id1", $this->offering_id);
            $stmt->bindParam(":offering_id2", $this->offering_id);
            $stmt->bindParam(":offering_id3", $this->offering_id);
            $stmt->bindParam(":offering_id4", $this->offering_id);
            $stmt->bindParam(":offering_id5", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":user_id1", $this->user_id);
            $stmt->bindParam(":user_id2", $this->user_id);
            $stmt->bindParam(":last_updated_user", $this->user_id);
            $stmt->bindParam(":last_updated_ts", $this->datetime);
            if ($stmt->execute()) {
                $stmt_comp = $this->check_course_status();
                if ($stmt_comp->rowCount() > 0) {
                    if ($row_comp = $stmt_comp->fetch(PDO::FETCH_ASSOC)) {
                        extract($row_comp);
                        return $content_completion;
                    }
                }
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getContentPath() {
        try {
            $query = "SELECT co.course_id,co.company_id, co.course_name,co.online, c.category_id,oc.lesson_id,oc.seq_no,ct.content_type_name, con.contentdtl_id,con.content_id,co.end_date,
                con.content_path,con.content_size, con.content_title,con.status, l.lesson_name,l.lesson_no,ol.lesson_dependency,ol.seq_no,
                ct1.total_time,ct1.completed,ct1.prev_status,ct1.time_diff,ca.result,lca.attendee_url,con.content_duration,con.recording_url,con.start_time,
                if((SELECT ct.offering_id from content_track as ct WHERE ct.offering_id=:offer1 AND  ct.user_id=:user AND ct.contentdtl_id=:content1 AND ct.completed='Y')is null, False, True) as offering_count 
                FROM course_assignment as ca,course_offering as co,course as c ,offering_content  AS oc, content_type AS ct,content_details as con,lesson as l ,offering_lesson as ol 
                LEFT JOIN content_track as ct1 ON ct1.offering_id=:offer2 and ct1.user_id=:user1 and  ct1.contentdtl_id=:content2
                LEFT JOIN live_content_assignment as lca ON lca.offering_id=:offer3 and lca.user_id=:user3 and  lca.contentdtl_id=:content3
                WHERE co.offering_id=:offer AND ca.offering_id=co.offering_id AND ca.user_id=:user2 AND co.course_id=c.course_id AND oc.offering_id=co.offering_id AND oc.contentdtl_id=:content AND  con.content_type_id = ct.content_type_id
                AND con.contentdtl_id=oc.contentdtl_id AND l.lesson_id=oc.lesson_id AND ol.lesson_id=l.lesson_id AND ol.offering_id=co.offering_id AND ca.status IS NULL";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offer", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offer1", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offer2", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offer3", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":content", $this->contentdtl_id, PDO::PARAM_INT);
            $stmt->bindParam(":content1", $this->contentdtl_id, PDO::PARAM_INT);
            $stmt->bindParam(":content2", $this->contentdtl_id, PDO::PARAM_INT);
            $stmt->bindParam(":content3", $this->contentdtl_id, PDO::PARAM_INT);
            $stmt->bindParam(":user", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user1", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user2", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user3", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getCYUdependency() {
        try {
            $query = "SELECT lesson_dependency,seq_no FROM offering_lesson WHERE assessment_id=:assessment_id AND 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");
        }
    }

    function getWebContent() {
        try {
            $query = "select link_type,web_link from content_details where contentdtl_id=:contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":contentdtl_id", $this->contentdtl_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getHTML() {
        try {
            $query = "select start_page from content_details where contentdtl_id=:contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":contentdtl_id", $this->contentdtl_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getScorm() {
        try {
            $query = "select scorm_id from content_details where contentdtl_id=:contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":contentdtl_id", $this->contentdtl_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function checkScormStatus() {
        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->bindParam(":scorm_id", $this->scorm_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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function check_certificate_avail_course() {
        try {
            $query = "SELECT template_id FROM certificate_assignment where offering_id=:offering_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");
        }
    }

    function check_certificate_avail_ref_course() {
        try {
            $query = "SELECT offering_content.contentdtl_id "
                    . "FROM offering_content WHERE offering_content.offering_id=:offering_id1 AND "
                    . "NOT EXISTS (SELECT 1 FROM content_track "
                    . "WHERE content_track.offering_id=:offering_id2 "
                    . "AND content_track.user_id=:user_id "
                    . "AND offering_content.contentdtl_id =content_track.contentdtl_id)";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id1", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id2", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Certificate
    function get_course_cert() {
        try {
            $query = "SELECT ca.score,co.course_name "
                    . "FROM course_assignment as ca,course_offering as co "
                    . "where ca.offering_id=:offering_id AND ca.user_id=:user_id "
                    . "AND ca.offering_id=co.offering_id AND ca.status IS NULL";
            $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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_grade_cert() {
        try {
            $query = "SELECT ct.grade,ct.badge_id,ct.grade_type,ct.cut_off,ct.template_id "
                    . "FROM certificate_assignment as ct,course_offering as co "
                    . "where ct.offering_id=:offering_id AND co.offering_id=ct.offering_id ORDER BY ct.cut_off desc";
            $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");
        }
    }

    //Certficate end

    function get_user_result() {
        try {
            $query = "SELECT score,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, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getPassingScore() {
        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, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getScormCriteria() {
        try {
            $query = "select scorm_id,weightage from result_criteria WHERE offering_id=:offering_id and scorm_id!=0";
            $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");
        }
    }

    public function update_result() {
        try {
            $query = "UPDATE
                course_assignment
            SET
                attended_date = :attended_date,
                result = :result,
                completion_points =:completion_points, 
                score = :score,
                last_updated_ts = :last_updated_ts
                 WHERE 
                 offering_id=:offering_id
                 and user_id=:user_id 
                 AND status IS NULL";

            $stmt = $this->conn->prepare($query);

            $stmt->bindParam(":attended_date", $this->attended_date);
            $stmt->bindParam(":result", $this->result);
            $stmt->bindParam(":score", $this->score);
            $stmt->bindParam(":completion_points", $this->completion_points);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);

            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_Company_profile() {
        try {
            $query = "SELECT * FROM company_profile";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_details_cert() {
        try {
            $query = "Select ua.first_name,ua.last_name,co.course_name,ca.attended_date,ca.score "
                    . "from user_account as ua,course_offering as co,course_assignment as ca "
                    . "where ua.user_id=:user_id and ca.offering_id=:offering_id "
                    . "and ca.offering_id=co.offering_id and ca.user_id=ua.user_id AND ca.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");
        }
    }

    function get_cert_template() {
        try {
            $query = "SELECT ct.template_file,ct.template_name,cf.comp_name,cf.text1,cf.text2,cf.text3,cf.text4,cf.text5,cf.text6,"
                    . "cf.text1_color,cf.text2_color,cf.text3_color,cf.text4_color,cf.text5_color,cf.text6_color,cf.comp_name_color,cf.name_color,cf.course_color,cf.grade_color,"
                    . "cf.text1_size,cf.text2_size,cf.text3_size,cf.text4_size,cf.text5_size,cf.text6_size,cf.comp_name_size,cf.name_size,cf.course_size,cf.grade_size,cf.comp_name_path,cf.sign_path "
                    . "FROM certificate_template as ct,certificate_format as cf "
                    . "where ct.template_id=:template_id AND ct.format_id=cf.format_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":template_id", $this->template_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Common end

    public function get_license() {
        try {
            $query = "select * from license_key where id=1";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function time_track() {
        try {
            $query = "INSERT INTO
                content_track
            SET
                offering_id = :offering_id,
                user_id = :user_id,
                contentdtl_id = :contentdtl_id,
                total_time=:total_time,
                time_diff=:time_diff,
                last_viewed_ts = :last_viewed_ts
                 ON DUPLICATE KEY UPDATE total_time=:time,time_diff=:time_diff1,last_viewed_ts=:timestamp";
            $stmt = $this->conn->prepare($query);
            // 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(":contentdtl_id", $this->contentdtl_id);
            $stmt->bindParam(":total_time", $this->total_time);
            $stmt->bindParam(":last_viewed_ts", $this->datetime);
            $stmt->bindParam(":time", $this->total_time);
            $stmt->bindParam(":timestamp", $this->datetime);
            $stmt->bindParam(":time_diff", $this->time_diff);
            $stmt->bindParam(":time_diff1", $this->time_diff);

            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function check_course_status() {
        try {
            $query = "SELECT result,completion,content_completion,"
                    . "if((SELECT count(oc.contentdtl_id) FROM offering_content as oc where oc.offering_id=:offering_id1 AND oc.status='Active'), True, False) as total_contents "
                    . "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, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id1", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function mark_incomplete($from_record_num, $records_per_page) {
        try {
            // select all courses query
            $query = "SELECT  
           FROM course_assignment as ca,course_offering as o "
                    . "WHERE ca.user_id=:user_id1 
                    AND ca.offering_id=:offering_id
               AND ca.offering_id=o.offering_id 
               AND if (o.is_scorm='No',ca.result is null 
               AND (o.max_attempts=(Select max(attempt_no) as attempt_no from quiz_score where user_id=:user_id2 and quiz_score.offering_id=ca.offering_id)), "
                    . "ca.result='Fail' AND "
                    . "if(EXISTS (SELECT offering_id from result_criteria as rc where rc.offering_id=ca.offering_id and rc.scorm_id!=0), o.end_date <:date2 "
                    . "OR o.max_attempts=(Select max(attempt_no) as attempt_no from quiz_score where user_id=:user_id3 and quiz_score.offering_id=ca.offering_id), "
                    . "o.max_attemps attempt_no from quiz_score where user_id=:user_id4 and quiz_score.offering_id=ca.offering_id))) "
                    . " AND ca.status IS NULL ORDER BY o.end_date,o.course_name asc LIMIT :start,:end";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id1", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id2", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id3", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id4", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":date1", $this->date, PDO::PARAM_STR);
            $stmt->bindParam(":date2", $this->date, PDO::PARAM_STR);
            $stmt->bindParam(":start", $from_record_num, PDO::PARAM_INT);
            $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //Scorm content progress - content completion
    public function get_scorm_total_time() {
        try {
            $query = "SELECT total_time FROM scorm where scorm_id=:scorm_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":scorm_id", $this->scorm_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //ecommerce
    function checkVideo() {
        try {
            $query = "SELECT media_path from offering_preview "
                    . "WHERE offering_id=:offering_id and media_type='V'";
            $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");
        }
    }

    public function offering_display_instructor($from_record_num, $records_per_page) {
        try {
            $query = "SELECT u.first_name,u.last_name,u.user_image,u.mobile_no,u.email_id,u.biography,"
                    . "u.website_link,u.twitter_link,u.facebook_link,u.linkedin_link "
                    . "FROM user_account as u,offering_instructor as oi "
                    . "WHERE u.user_id=oi.instructor_id AND oi.offering_id=:offering_id LIMIT :start,:end";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":start", $from_record_num, PDO::PARAM_INT);
            $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function offering_display_all_instructor() {
        try {
            $query = "SELECT u.first_name,u.last_name,u.user_image,u.mobile_no,u.email_id,u.biography,u.website_link,u.facebook_link,u.linkedin_link,u.twitter_link "
                    . "FROM user_account as u,offering_instructor as oi "
                    . "WHERE u.user_id=oi.instructor_id AND oi.offering_id=:offering_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");
        }
    }

    public function rating_count() {
        try {
            $query = "SELECT count(*) as count,AVG(rating_value) as rating   from rating where offering_id=:offering_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");
        }
    }

    public function review_count() {
        try {
            $query = "SELECT count(*) as count  from rating where offering_id=:offering_id AND comments!='' AND status='Active' AND company_id=(select company_id from user_account where user_id=:user_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->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return count
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function review($from_record_num, $records_per_page) {
        try {
            $query = "SELECT u.first_name,u.last_name,u.user_image,r.rating_value,r.last_updated_ts,r.comments,r.rating_id,r.offering_id,r.user_id "
                    . "FROM rating as r,user_account as u WHERE u.user_id=r.user_id and r.offering_id=:offering_id AND comments!='' AND r.status='Active' AND r.company_id=(select company_id from user_account where user_id=:user_id) "
                    . "ORDER BY  r.rating_value desc LIMIT :start , :end";
            $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(":start", $from_record_num, PDO::PARAM_INT);
            $stmt->bindParam(":end", $records_per_page, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_your_rating_value() {
        try {
            $query = "SELECT  * FROM rating WHERE user_id=:user_id and offering_id=:offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_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");
        }
    }

    public function rating_insert($user_id, $offering_id, $rating_value, $comments, $status, $last_updated_user, $date_time) {
        try {
            $query = "SELECT offering_id FROM rating WHERE user_id=:user_id AND offering_id=:offering_id ";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $user_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id", $offering_id, PDO::PARAM_INT);
            $stmt->execute();
            if ($stmt->rowCount() > 0) {
                $query2 = "UPDATE rating SET rating_value =:rating_value,comments=:comment,status=:status,last_updated_user=:last_updated_user,last_updated_ts=:date_time WHERE user_id=:user_id AND offering_id=:offering_id";
                $stmt2 = $this->conn->prepare($query2);
                $stmt2->bindParam(':user_id', $user_id, PDO::PARAM_INT);
                $stmt2->bindParam(':offering_id', $offering_id, PDO::PARAM_INT);
                $stmt2->bindParam(':rating_value', $rating_value, PDO::PARAM_STR);
                $stmt2->bindParam(':comment', $comments, PDO::PARAM_STR);
                $stmt2->bindParam(':last_updated_user', $last_updated_user, PDO::PARAM_STR);
                $stmt2->bindParam(':status', $status, PDO::PARAM_STR);
                $stmt2->bindParam(':date_time', $date_time, PDO::PARAM_STR);
                $stmt2->execute();
                if ($stmt2->rowCount() > 0) {
                    return '1';
                } else {
                    return '2';
                }
            } else {
                $query1 = "INSERT into rating (user_id,company_id,department_id,offering_id,rating_value,comments,status,last_updated_user,created_ts,last_updated_ts)values( :user_id,(select company_id from user_account where user_id=:user_id1),(select department_id from user_account where user_id=:user_id2),:offering_id,:rating_value,:comment,:status,:last_updated_user,:date_time1,:date_time2)";
                $stmt1 = $this->conn->prepare($query1);
                $stmt1->bindParam(':user_id', $user_id, PDO::PARAM_INT);
                $stmt1->bindParam(':user_id1', $user_id, PDO::PARAM_INT);
                $stmt1->bindParam(':user_id2', $user_id, PDO::PARAM_INT);
                $stmt1->bindParam(':offering_id', $offering_id, PDO::PARAM_INT);
                $stmt1->bindParam(':rating_value', $rating_value, PDO::PARAM_STR);
                $stmt1->bindParam(':comment', $comments, PDO::PARAM_STR);
                $stmt1->bindParam(':status', $status, PDO::PARAM_STR);
                $stmt1->bindParam(':last_updated_user', $last_updated_user, PDO::PARAM_STR);
                $stmt1->bindParam(':date_time1', $date_time, PDO::PARAM_STR);
                $stmt1->bindParam(':date_time2', $date_time, PDO::PARAM_STR);
                $stmt1->execute();
                if ($stmt1->rowCount() > 0) {
                    return '1';
                } else {
                    return '2';
                }
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function check_spam_review($rating_id, $id1) {
        try {
            $query = "SELECT rating_id FROM review_abuse WHERE rating_id=:rating_id AND user_id=:user_id AND status='Open'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":rating_id", $rating_id, PDO::PARAM_STR);
            $stmt->bindParam(":user_id", $id1, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function review_spam_insert($user_id, $offering_id, $rating_value, $status, $last_updated_user, $date_time) {
        try {
            $query = "INSERT into review_abuse (rating_id,user_id,offering_id,status,last_updated_user,created_ts,last_updated_ts)values( :rating_value,:user_id,:offering_id,:status,:last_updated_user,:date_time1,:date_time2)";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $stmt->bindParam(':offering_id', $offering_id, PDO::PARAM_INT);
            $stmt->bindParam(':rating_value', $rating_value, PDO::PARAM_STR);
            $stmt->bindParam(':status', $status, PDO::PARAM_STR);
            $stmt->bindParam(':last_updated_user', $last_updated_user, PDO::PARAM_STR);
            $stmt->bindParam(':date_time1', $date_time, PDO::PARAM_STR);
            $stmt->bindParam(':date_time2', $date_time, PDO::PARAM_STR);
            $stmt->execute();
            if ($stmt->rowCount() > 0) {
                return '1';
            } else {
                return '2';
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function review_spam_delete($user_id, $offering_id, $rating_id) {
        try {
            $query = "DELETE from review_abuse WHERE rating_id=:rating_id AND user_id=:user_id AND offering_id=:offering_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $stmt->bindParam(':offering_id', $offering_id, PDO::PARAM_INT);
            $stmt->bindParam(':rating_id', $rating_id, PDO::PARAM_STR);
            $stmt->execute();
            if ($stmt->rowCount() > 0) {
                return '1';
            } else {
                return '2';
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function getLanguages() {
        try {
            $query = "SELECT lang_id,lang_name FROM pref_language";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function language_update($user_id, $language_id) {
        try {
            $query2 = "UPDATE user_account SET pref_lang_id=:language_id where user_id=:user_id";
            $stmt2 = $this->conn->prepare($query2);
            $stmt2->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $stmt2->bindParam(':language_id', $language_id, PDO::PARAM_INT);
            $stmt2->execute();
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function language_check($user_id) {
        try {
            $query = "select pref_lang_id from user_account where user_id=:user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function fetchCourseName() {
        try {
            $query = "SELECT course_name FROM course_offering  WHERE offering_id=:offering_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function track_content_video_complete() {
        try {
            $query = "UPDATE content_track SET prev_status='Y',total_time=:total_time,time_diff=NULL,last_viewed_ts=:last_viewed_ts WHERE user_id=:user_id AND offering_id=:offering_id AND contentdtl_id=:contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":contentdtl_id", $this->contentdtl_id);
            $stmt->bindParam(":total_time", $this->total_time);
            $stmt->bindParam(":last_viewed_ts", $this->datetime);
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function isCertificatepoints() {
        try {
            $query = "SELECT offering_id FROM certificate_assignment WHERE offering_id=:offering_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            if ($stmt->rowCount() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function completion_pt_certificate() {
        try {
            $query = "SELECT completion_points FROM certificate_assignment WHERE offering_id=:offering_id and cut_off<=:cut_off Order By cut_off desc LIMIT 1";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":cut_off", $this->score);
            // execute query
            $stmt->execute();
            // return values
            if ($stmt->rowCount() > 0) {
                $rows = $stmt->fetch(PDO::FETCH_NUM);
                return $rows[0];
            } else {
                $stmt_point = $this->completion_pt_certificate_null();
                if ($stmt_point->rowCount() > 0) {
                    $row_point = $stmt_point->fetch(PDO::FETCH_NUM);
                    return $row_point[0];
                }
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function completion_pt_certificate_null() {
        try {
            $query = "SELECT completion_points FROM certificate_assignment WHERE offering_id=:offering_id and cut_off is NULL";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function completion_pt_assignment() {
        try {
            $query = "SELECT co.completion_points from course_offering as co where co.offering_id=:offering_id ";
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id);
            // execute query
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function Sum_completionPoints() {
        try {
//            $query = "SELECT SUM(completion_points) as count from course_assignment where result='complete' and user_id=:user_id AND status IS NULL";
            $query = "SELECT SUM(ca.completion_points) as count from course_assignment AS ca JOIN course_offering AS co ON ca.offering_id = co.offering_id where ca.result='complete' and ca.user_id=:user_id AND ca.status IS NULL  AND co.status = 'Active'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function update_userpoint($points) {
        try {
            $query = "UPDATE user_account SET total_completion_points=:completion_points WHERE user_id=:user_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":completion_points", $points, PDO::PARAM_STR);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function points_course() {
        try {
            $query = "SELECT co.offering_name,co.course_name,ca.* from course_assignment as ca,course_offering as co where ca.result='complete' and ca.user_id=:user_id AND co.offering_id=ca.offering_id AND ca.completion_points IS NOT NULL AND ca.status IS NULL AND co.status = 'Active' order by ca.attended_date desc ";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_max_attempt() {
        try {
            $query = "Select max(attempt_no) as max from quiz_score as qz,assessment as a where qz.offering_id=:offering_id and qz.user_id=:user_id and qz.assessment_id=a.assessment_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_all_without_last($max) {
        try {
            $query = "SELECT qz.*,a.assessment_name FROM quiz_score as qz,assessment as a where qz.offering_id=:offering_id AND qz.user_id=:user_id AND qz.assessment_id=a.assessment_id  ORDER BY offering_id limit :max";
            $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(":max", $max);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_all_only_last($max) {
        try {
            $query = "SELECT qz.*,a.assessment_name FROM quiz_score as qz,assessment as a where qz.offering_id=:offering_id AND qz.user_id=:user_id AND qz.assessment_id=a.assessment_id  ORDER BY offering_id limit :max,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(":max", $max);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function getAssmtInstruction($oid) {
        try {
            $query = "SELECT special_instructions FROM course_offering  WHERE offering_id=:offering_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $oid);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function offering_Course_Report($off_val_date1) {
        try {
            $query = "SELECT ca.offering_id,co.offering_name,co.passing_score,co.course_name,ca.result,ca.content_completion,ca.attended_date,cc.category_name,"
                    . "ca.score,ca.completion_points,concat(SEC_TO_TIME( SUM( TIME_TO_SEC( ct.total_time ) ) ),'') AS total_time FROM course_assignment as ca "
                    . "LEFT JOIN course_offering as co on ca.offering_id=co.offering_id "
                    . "LEFT JOIN content_track as ct on ct.offering_id=ca.offering_id AND ct.user_id=ca.user_id "
                    . "LEFT JOIN course c ON c.course_id=co.course_id "
                    . "LEFT JOIN assessment a ON a.assessment_id=co.final_assmt_id AND c.course_id = 0 "
                    . "LEFT JOIN course_category cc ON (c.category_id=cc.category_id or a.category_id=cc.category_id) "
                    . "WHERE ca.user_id=:user_id "
                    . "$off_val_date1"
                    . "and co.status='Active' and ca.status is NULL GROUP BY ca.offering_id,co.offering_name,co.passing_score,co.course_name,ca.result,ca.content_completion,ca.attended_date,cc.category_name,
ca.score,ca.completion_points ORDER BY ca.attended_date DESC";
            $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 offering_Course_Reportbadge($off_val_date1) {
        try {
            $query = "SELECT cat.grade FROM certificate_assignment as cat,course_assignment as ca "
                    . "WHERE ca.offering_id=cat.offering_id AND ca.offering_id=:offering_id and ca.user_id=:user_id AND cat.badge_id>0 AND  cat.cut_off<=ca.score AND ca.result='complete' " . $off_val_date1 . " ORDER BY cat.cut_off DESC 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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function offering_Course_Reporttemplate($off_val_date1) {
        try {
            $query = "SELECT count(cat.template_id) as template_count FROM certificate_assignment as cat,course_assignment as ca "
                    . "WHERE ca.offering_id=cat.offering_id AND ca.offering_id=:offering_id and ca.user_id=:user_id AND cat.template_id>0 AND ca.result='complete' "
                    . "$off_val_date1";
            $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->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_Course_totalTime($off_val_date1) {
        try {
            $query = "SELECT concat(SEC_TO_TIME( SUM( TIME_TO_SEC( ct.total_time ) ) ),'') AS total_time FROM course_assignment as ca,course_offering as co,content_track as ct WHERE "
                    . "ca.offering_id=co.offering_id AND ct.offering_id=ca.offering_id AND ct.user_id=ca.user_id " . $off_val_date1 . " and "
                    . "ca.user_id=:user_id and co.status='Active' and ca.status is NULL";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
//             return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_ReportActivityPoints() {
        try {
            $query = "SELECT * FROM activity_points WHERE company_id=(select company_id 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");
        }
    }

    //FORUM
    public function get_totaltopic($forum_val_date, $discussion_forum) {
        try {
            $query = "Select (count(ft.topic_id)* " . $discussion_forum . ")  as sum from forum_topic as ft where ft.user_id= :user_id " . $forum_val_date;
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
//             return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_totalresponse($forum_val_date, $discussion_forum) {
        try {
            $query = "Select (count(ftr.response_id)* " . $discussion_forum . ")  as sum from forum_topic as ft,forum_topic_response as ftr WHERE ft.topic_id=ftr.topic_id and ftr.user_id=:user_id " . $forum_val_date . "";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->execute();
            $rows = $stmt->fetch(PDO::FETCH_NUM);
//             return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_offer_CompletionCount($offer_completionDate, $offer_completion, $score1, $score2, $score3, $score4, $offer_score1, $offer_score2, $offer_score3, $offer_score4) {
        try {
            $query = "SELECT sum(CASE WHEN ca.result='complete' THEN 1 ELSE 0 end)*" . $offer_completion . "  as offer_completion_points,"
                    . "sum(CASE WHEN ca.score >= " . $score1 . " THEN 1 ELSE 0 end)*" . $offer_score1 . " as offer_score1_points,"
                    . "sum(CASE WHEN ca.score >= " . $score2 . " AND ca.score<" . $score1 . " THEN 1 ELSE 0 end)*" . $offer_score2 . " as offer_score2_points,"
                    . "sum(CASE WHEN ca.score >= " . $score3 . " AND ca.score<" . $score2 . " THEN 1 ELSE 0 end)*" . $offer_score3 . " as offer_score3_points,"
                    . "sum(CASE WHEN ca.score >= " . $score4 . " AND ca.score<" . $score3 . " THEN 1 ELSE 0 end)*" . $offer_score4 . "  as offer_score4_points "
                    . "FROM course_assignment as ca,course_offering as co WHERE ca.user_id=:user_id AND "
                    . "ca.offering_id=co.offering_id " . $offer_completionDate . ";";
            $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_offer_CompletionPointsCount($offer_completionDate, $offer_completion) {
        try {
            $query = "SELECT co.offering_name,co.course_name,sum(CASE WHEN ca.result='complete' THEN 1 ELSE 0 end)*" . $offer_completion . "  as offer_completion_points 
                        FROM course_assignment as ca,course_offering as co WHERE ca.user_id=:user_id AND 
                        ca.offering_id=co.offering_id " . $offer_completionDate . " AND ca.result='complete' GROUP BY co.offering_name,co.course_name ";
            $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_offer_score($offer_completionDate, $offer_score1, $score_value) {
        try {
            $query = "SELECT co.course_name,co.offering_name,sum(CASE WHEN " . $score_value . " THEN 1 ELSE 0 end)*" . $offer_score1 . " as offer_score1_points "
                    . "FROM course_assignment as ca,course_offering as co WHERE ca.user_id=:user_id "
                    . "AND ca.offering_id=co.offering_id " . $offer_completionDate . " AND " . $score_value . " GROUP BY co.course_name,co.offering_name";
            $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");
        }
    }

    //offer view    
    /* function get_offer_view($offer_completionDate, $offer_view) {
      try {
      $query = "SELECT co.course_name,co.offering_name,TRUNCATE(sum((TIME_TO_SEC(total_time)/3600)*" . $offer_view . "),2) as total_time FROM content_track as ct,course_offering as co,course_assignment as ca WHERE ca.user_id=:user_id and ca.user_id=ct.user_id and ca.offering_id=co.offering_id AND ca.offering_id=ct.offering_id AND ct.user_id=ca.user_id AND ct.total_time is not null " . $offer_completionDate . " GROUP BY co.course_name,co.offering_name";
      $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_offer_view($offer_completionDate, $offer_view) {
        try {
            $query = "SELECT co.course_name,co.offering_name,ROUND(ROUND(TIME_TO_SEC(SUM(total_time))/60, 0)/60,1) * " . $offer_view . " as total_time FROM content_track as ct,course_offering as co,course_assignment as ca WHERE ca.user_id=:user_id and ca.user_id=ct.user_id and ca.offering_id=co.offering_id AND ca.offering_id=ct.offering_id AND ct.user_id=ca.user_id AND ct.total_time is not null " . $offer_completionDate . " GROUP BY co.course_name,co.offering_name";
            $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_offer_rating($offer_completionDate, $offer_ratings) {
        try {
            $query = "select co.course_name,co.offering_name,count(r.rating_value)*" . $offer_ratings . " as rating_value FROM rating as r,course_offering as co ,course_assignment as ca WHERE ca.user_id=:user_id and ca.offering_id=co.offering_id AND ca.offering_id=r.offering_id " . $offer_completionDate . " AND r.user_id=ca.user_id GROUP BY co.course_name,co.offering_name";
            $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");
        }
    }

    public function fetch_designation() {
        try {
            $query = "SELECT d.designation_id,d.designation_name FROM designation_master as d,user_account as ua WHERE d.designation_id=ua.designation_id AND ua.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");
        }
    }

    public function learning_names_display() {
        try {
            $query = "SELECT l.learning_path_name,l.learning_path_id FROM learning_path as l,learning_path_designation as ld WHERE l.learning_path_id=ld.learning_path_id AND ld.designation_id=:designation_id AND l.status='Active' ORDER BY l.learning_path_name";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":designation_id", $this->designation_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function learning_path_completed() {

        try {
            $query = "SELECT l.learning_path_id FROM learning_path_user_completed as lpuc,learning_path as l WHERE l.learning_path_id=lpuc.learning_path_id AND lpuc.learning_path_id=:learning_path_id AND lpuc.user_id=:user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":learning_path_id", $this->learning_path_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function learnings_course_display_complete() {
        try {
            $query = "SELECT c.course_name FROM course as c,learning_path_user as lpa WHERE lpa.ca_id=c.course_id AND lpa.assignment_type='c' AND lpa.learning_path_id=:learning_path_id AND lpa.user_id=:user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":learning_path_id", $this->learning_path_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function learnings_assessment_display_complete() {
        try {
            $query = "SELECT a.assessment_name FROM assessment as a,learning_path_user as lpa WHERE lpa.ca_id=a.assessment_id AND lpa.assignment_type='a' AND lpa.learning_path_id=:learning_path_id AND lpa.user_id=:user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":learning_path_id", $this->learning_path_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function learnings_course_display() {
        try {
            $query = "SELECT c.course_id,c.course_name,lpa.dependency FROM course as c,learning_path_assignment as lpa WHERE lpa.ca_id=c.course_id AND lpa.assignment_type='c' AND lpa.learning_path_id=:learning_path_id ";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":learning_path_id", $this->learning_path_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    
    public function learnings_course_display_offer() {
        try {
            $query = "WITH ranked_courses AS (
                        SELECT  
                            co.offering_id,c.course_id,
                            ROW_NUMBER() OVER (PARTITION BY c.course_id ORDER BY co.offering_id ASC) AS row_num
                        FROM 
                            course AS c
                        JOIN 
                            learning_path_assignment AS lpa ON lpa.ca_id = c.course_id
                        JOIN 
                            course_offering AS co ON co.course_id = c.course_id
                        LEFT JOIN 
                            course_assignment AS ca ON co.offering_id = ca.offering_id 
                        WHERE 
                            lpa.assignment_type = 'c' 
                            AND lpa.learning_path_id = :learning_path_id  
                            AND co.status = 'Active'
                            AND ca.user_id = :user_id
                    )
                    SELECT offering_id,course_id 
                    FROM ranked_courses
                    WHERE row_num = 1";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":learning_path_id", $this->learning_path_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function learnings_assessment_display() {
        try {
            $query = "SELECT a.assessment_id,a.assessment_name,lpa.dependency FROM assessment as a,learning_path_assignment as lpa WHERE lpa.ca_id=a.assessment_id AND lpa.assignment_type='a' AND lpa.learning_path_id=:learning_path_id ";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":learning_path_id", $this->learning_path_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function check_learnings_status_course() {
        try {
            $query = "SELECT SUM(CASE WHEN ca.result = 'incomplete' OR ca.result = 'awaited' OR ca.result is NULL  THEN 1 ELSE 0 end ) incomplete,"
                    . "SUM(CASE WHEN ca.result = 'complete' THEN 1 ELSE 0 end) AS complete "
                    . "FROM course_offering as co,course_assignment as ca WHERE co.offering_id=ca.offering_id AND co.course_id=:course_id AND ca.user_id=:user_id AND co.offering_type is NULL";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":course_id", $this->course_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function check_learnings_status_assessment() {
        try {
            $query = "SELECT  SUM(CASE WHEN ca.result = 'incomplete' OR ca.result = 'awaited' OR ca.result is NULL  THEN 1 ELSE 0 end ) incomplete,"
                    . "SUM(CASE WHEN ca.result = 'complete' THEN 1 ELSE 0 end) AS complete FROM course_offering as co,course_assignment as ca WHERE co.offering_id=ca.offering_id AND co.final_assmt_id=:assessment_id AND ca.user_id=:user_id  AND co.offering_type='A'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_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 learning_certficate() {
        try {
            $query = "SELECT template_id FROM learning_path_certificate WHERE learning_path_id=:learning_path_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":learning_path_id", $this->learning_path_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function learning_certficate_display() {
        try {
            $query = "SELECT l.learning_path_name,l.learning_path_id FROM learning_path as l,learning_path_designation as ld,learning_path_user_completed as lpu,learning_path_certificate as lpc WHERE l.learning_path_id=lpu.learning_path_id AND l.learning_path_id=lpc.learning_path_id AND l.learning_path_id=ld.learning_path_id AND lpu.user_id=:user_id GROUP BY l.learning_path_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_learning_cert() {
        try {
            $query = "SELECT lpc.template_id,c.logo_path,c.name,ua.first_name,ua.last_name,l.learning_path_name,lpu.completed_date "
                    . "FROM user_account as ua,learning_path as l,learning_path_user_completed as lpu,company as c,learning_path_certificate as lpc "
                    . "WHERE ua.user_id=lpu.user_id AND l.learning_path_id=lpu.learning_path_id AND ua.company_id=c.company_id AND lpc.learning_path_id=l.learning_path_id "
                    . "AND lpu.learning_path_id=:learning_path_id AND ua.user_id=:user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":learning_path_id", $this->learning_path_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_your_note() {
        try {
            $query = "SELECT course_notes,last_updated_ts FROM course_notes 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 notes_insert($user_id, $offering_id, $course_notes, $last_updated_user, $date_time) {
        try {
            $query = "SELECT offering_id FROM course_notes WHERE user_id=:user_id AND offering_id=:offering_id ";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $user_id, PDO::PARAM_INT);
            $stmt->bindParam(":offering_id", $offering_id, PDO::PARAM_INT);
            $stmt->execute();
            if ($stmt->rowCount() > 0) {
                $query2 = "UPDATE course_notes SET course_notes=:course_notes,last_updated_user=:last_updated_user,last_updated_ts=:date_time WHERE user_id=:user_id AND offering_id=:offering_id";
                $stmt2 = $this->conn->prepare($query2);
                $stmt2->bindParam(':user_id', $user_id, PDO::PARAM_INT);
                $stmt2->bindParam(':offering_id', $offering_id, PDO::PARAM_INT);
                $stmt2->bindParam(':course_notes', $course_notes, PDO::PARAM_STR);
                $stmt2->bindParam(':last_updated_user', $last_updated_user, PDO::PARAM_STR);
                $stmt2->bindParam(':date_time', $date_time, PDO::PARAM_STR);
                $stmt2->execute();
                if ($stmt2->rowCount() > 0) {
                    return '1';
                } else {
                    return '2';
                }
            } else {
                $query1 = "INSERT into course_notes (user_id,offering_id,course_notes,created_ts,last_updated_user,last_updated_ts)values( :user_id,:offering_id,:course_notes,:date_time1,:last_updated_user,:date_time2)";
                $stmt1 = $this->conn->prepare($query1);
                $stmt1->bindParam(':user_id', $user_id, PDO::PARAM_INT);
                $stmt1->bindParam(':offering_id', $offering_id, PDO::PARAM_INT);
                $stmt1->bindParam(':course_notes', $course_notes, PDO::PARAM_STR);
                $stmt1->bindParam(':last_updated_user', $last_updated_user, PDO::PARAM_STR);
                $stmt1->bindParam(':date_time1', $date_time, PDO::PARAM_STR);
                $stmt1->bindParam(':date_time2', $date_time, PDO::PARAM_STR);
                $stmt1->execute();
                if ($stmt1->rowCount() > 0) {
                    return '1';
                } else {
                    return '2';
                }
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /* added by renuga */

    public function UserDetails($user_id) {
        try {
//            select * from user_account as ua join company c on c.company_id=ua.company_id left join department d on d.department_id =ua.department_id where ua.user_id=9
            $query = $this->conn->prepare("SELECT dm.designation_name as designation,ua.*,c.name,d.dept_name FROM user_account as ua "
                    . "JOIN company c on c.company_id=ua.company_id "
                    . "LEFT JOIN department d on d.department_id =ua.department_id "
                    . "LEFT JOIN designation_master dm on dm.designation_id =ua.designation_id "
                    . " where ua.user_id=:user_id");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            $query->execute();
            return $query;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /* 14-06-2023 Learning Path */

    function checkLearningPathDependency() {
        try {
            $query = "SELECT"
                    . "FROM learning_path_designation as lpd,learning_path_assignment as lpa,user_account as ua,course_offering as co "
                    . "WHERE ua.user_id=:user_id AND ua.designation_id=lpd.designation_id AND lpd.learning_path_id=lpa.learning_path_id "
                    . "AND (lpa.ca_id=co.course_id OR (lpa.ca_id=co.final_assmt_id AND co.course_id=0)) AND co.offering_id=:offering_id "
                    . "AND if(lpa.dependency='Y' AND lpa.seq_no!=1, "
                    . "(SELECT result FROM course_assignment as ca,learning_path_assignment as lpa1,course_offering as co1 "
                    . "WHERE ca.user_id=:user_id AND ca.offering_id=:offering_id AND lpa1.seq_no))";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_STR);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_learning_paths() {
        try {
//            $query = "SELECT lpa.learning_path_id,lpa.dependency,lpa.seq_no "
//                    . "FROM learning_path_designation as lpd,learning_path_assignment as lpa,user_account as ua,course_offering as co "
//                    . "WHERE ua.user_id=:user_id AND ua.designation_id=lpd.designation_id AND lpd.learning_path_id=lpa.learning_path_id "
//                    . "AND (lpa.ca_id=co.course_id OR (lpa.ca_id=co.final_assmt_id AND co.course_id=0)) AND co.offering_id=:offering_id "
//                    . "AND lpa.dependency='Y'";
            $query = "SELECT lpa.learning_path_id,lpa.dependency,lpa.seq_no,l.status 
                  FROM learning_path_designation as lpd,learning_path_assignment as lpa,user_account as ua,course_offering as co,learning_path as l 
                   WHERE ua.user_id=:user_id AND ua.designation_id=lpd.designation_id AND lpd.learning_path_id=lpa.learning_path_id 
                    AND CASE WHEN lpa.assignment_type = 'c' THEN (lpa.ca_id=co.course_id) ELSE (lpa.ca_id=co.final_assmt_id AND co.course_id=0) END AND co.offering_id=:offering_id 
                    AND lpa.dependency='Y' AND l.learning_path_id=lpa.learning_path_id AND l.status='Active'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function check_prev_learning_paths($learning_path_id, $seq_no, $user_id) {
        try {
            $query = "SELECT count(*) as count,lpa.ca_id,result,co.course_name,co.status "
                    . "FROM learning_path_assignment as lpa,course_offering co,course_assignment as ca "
                    . "WHERE lpa.learning_path_id=" . $learning_path_id . " AND lpa.seq_no=" . $seq_no . " AND "
                    . "if(lpa.assignment_type='c',co.course_id=lpa.ca_id,co.final_assmt_id=lpa.ca_id) "
                    . "AND co.offering_id=ca.offering_id AND ca.user_id=" . $user_id . " GROUP BY lpa.ca_id,result,co.course_name,co.status";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function learning_paths_course_name($learning_path_id, $seq_no) {
        try {
            $query = "SELECT if(lpa.assignment_type='c',"
                    . "(SELECT course_name FROM course where course_id=lpa.ca_id),"
                    . "(SELECT assessment_name FROM assessment where assessment_id=lpa.ca_id)) as course_name "
                    . "FROM learning_path_assignment as lpa "
                    . "WHERE lpa.learning_path_id=" . $learning_path_id . " AND lpa.seq_no=" . $seq_no . "";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

//XAPI
    function getxApi() {
        try {
            $query = "select xapi_id from content_details where contentdtl_id=:contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":contentdtl_id", $this->contentdtl_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function checkxApiStatus() {
        try {
            $query = "select status from xapi_track where xapi_id=:xapi_id and offering_id=:offering_id and user_id=:user_id and contentdtl_id=:contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":xapi_id", $this->xapi_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(":contentdtl_id", $this->contentdtl_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getlrsdetails() {
        try {
            $query = "select * from configuration as c,course as co where c.company_id=co.company_id and co.course_id=:course_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":course_id", $this->course_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function getXApiCriteria() {
        try {
            $query = "select xapi_id,weightage from result_criteria WHERE offering_id=:offering_id and xapi_id!=0";
            $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");
        }
    }

    public function get_xapi_score_user() {
        try {
            $query = "SELECT actual_score,score_max FROM xapi_track where xapi_id=:xapi_id "
                    . "and offering_id=:offering_id and user_id=:user_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(":xapi_id", $this->xapi_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_xapi_lesson_status_user() {
        try {
            $query = "SELECT status,xapi_batch FROM xapi_track where xapi_id=:xapi_id "
                    . "and offering_id=:offering_id and user_id=:user_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(":xapi_id", $this->xapi_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_xapi_score_status_user() {
        try {
            $query = "SELECT status,score_min FROM xapi_track where xapi_id=:xapi_id "
                    . "and offering_id=:offering_id and user_id=:user_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(":xapi_id", $this->xapi_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_xapi_score_card() {
        try {
            $query = "SELECT c.xapi_id,c.content_title "
                    . "FROM content_details as c,offering_content as oc,xapi_content_master as xt "
                    . "WHERE oc.offering_id=:offering_id and oc.contentdtl_id=c.contentdtl_id and c.xapi_id=xt.xapi_id and oc.status='Active'";
            $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");
        }
    }

    public function get_xapi_score_card_considered() {
        try {
            $query = "SELECT rs.* FROM result_criteria as rs,content_details as cd,offering_content as oc where  "
                    . "oc.contentdtl_id=cd.contentdtl_id and oc.status='Active' and cd.xapi_id=rs.xapi_id and "
                    . "rs.offering_id=:offering_id and rs.xapi_id=:xapi_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":xapi_id", $this->xapi_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_xapi_score_card_considered_active_inactive() {
        try {
            $query = "SELECT scr.* FROM scorm_offer_criteria as scr,content_details as cd,offering_content as oc where  "
                    . "oc.contentdtl_id=cd.contentdtl_id and oc.status='Active' and cd.xapi_id=scr.xapi_id and "
                    . "scr.offering_id=:offering_id and scr.xapi_id=:xapi_id and user_id=:user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":xapi_id", $this->xapi_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //LTI
    function getLticriteria() {
        try {
            $query = "select lti_id,weightage from result_criteria WHERE offering_id=:offering_id and lti_id!=0";
            $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");
        }
    }

    public function get_lti_lesson_status_user() {
        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->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":lti_id", $this->lti_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_lti_score_card() {
        try {
            $query = "SELECT lt.lti_id,c.content_title "
                    . "FROM content_details as c,offering_content as oc,lti as lt "
                    . "WHERE oc.offering_id=:offering_id and oc.contentdtl_id=c.contentdtl_id and c.contentdtl_id=lt.contentdtl_id and oc.status='Active'";
            $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");
        }
    }

    public function get_lti_score_card_considered_active_inactive() {
        try {
            $query = "SELECT lug.* FROM lti_user_grades as lug,grade_items as g,content_details as cd,offering_content as oc,lti as l where  "
                    . " lug.grade_items_id=g.grade_items_id AND "
                    . "oc.contentdtl_id=cd.contentdtl_id and oc.status='Active' and cd.contentdtl_id=l.contentdtl_id and l.lti_id=g.lti_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->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":lti_id", $this->lti_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_lti_score_card_considered() {
        try {
            $query = "SELECT rs.* FROM result_criteria as rs,content_details as cd,offering_content as oc,lti as l where  "
                    . "oc.contentdtl_id=cd.contentdtl_id and oc.status='Active' and cd.contentdtl_id=l.contentdtl_id and l.lti_id=rs.lti_id and "
                    . "rs.offering_id=:offering_id and rs.lti_id=:lti_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":lti_id", $this->lti_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_lti_score_user() {
        try {
            $query = "SELECT lug.raw_grade,g.grade_pass,lug.raw_grade_max 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->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":lti_id", $this->lti_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function check_Gradeitem() {
        try {
            $query = "SELECT * FROM grade_items as g,lti as l where l.lti_id=g.lti_id "
                    . "and l.contentdtl_id=:contentdtl_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":contentdtl_id", $this->contentdtl_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function get_gradeitem() {
        try {
            $query = "SELECT * FROM grade_items as g,lti as l where l.lti_id=g.lti_id "
                    . "and l.lti_id=:lti_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":lti_id", $this->lti_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function check_content_track() {
        try {
            $query = "SELECT ct.* FROM content_track as ct,lti as l where l.contentdtl_id=ct.contentdtl_id "
                    . "and l.lti_id=:lti_id and ct.offering_id=:offering_id and ct.user_id=:user_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(":lti_id", $this->lti_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    
    //added on 08-12-2025 for AI feature
    function getContentPathByOffering()
    {
        try {
            $query = "SELECT 
            co.course_id,
            co.company_id,
            co.course_name,
            co.online,
            c.category_id,
            oc.lesson_id,
            oc.seq_no,
            ct.content_type_name,
            con.contentdtl_id,
            con.content_id,
            co.end_date,
            con.content_path,
            con.content_size,
            con.content_title,
            con.status,
            l.lesson_name,
            l.lesson_no,
            ol.lesson_dependency,
            ol.seq_no,
            ct1.total_time,
            ct1.completed,
            ct1.prev_status,
            ct1.time_diff,
            ca.result,
            lca.attendee_url,
            con.content_duration,
            con.recording_url,
            con.start_time,
            IF(
                (SELECT ct.offering_id 
                 FROM content_track AS ct 
                 WHERE ct.offering_id = :offer 
                   AND ct.user_id = :user 
                   AND ct.completed = 'Y'
                 LIMIT 1
                ) IS NULL, FALSE, TRUE
            ) AS offering_count 
        FROM 
            course_assignment AS ca
            JOIN course_offering AS co ON ca.offering_id = co.offering_id
            JOIN course AS c ON co.course_id = c.course_id
            JOIN offering_content AS oc ON oc.offering_id = co.offering_id
            JOIN content_details AS con ON con.contentdtl_id = oc.contentdtl_id
            JOIN content_type AS ct ON con.content_type_id = ct.content_type_id
            JOIN lesson AS l ON l.lesson_id = oc.lesson_id
            JOIN offering_lesson AS ol ON ol.lesson_id = l.lesson_id AND ol.offering_id = co.offering_id
            LEFT JOIN content_track AS ct1 ON ct1.offering_id = :offer1 AND ct1.user_id = :user1 AND ct1.contentdtl_id = con.contentdtl_id
            LEFT JOIN live_content_assignment AS lca ON lca.offering_id = :offer2 AND lca.user_id = :user2 AND lca.contentdtl_id = con.contentdtl_id
        WHERE 
            co.offering_id = :offer3
            AND ca.user_id = :user3
            AND ca.status IS NULL
        ";

            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":offer", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offer1", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offer2", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":offer3", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":user", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user1", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user2", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user3", $this->user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught exception in getContentPath(): " . $e->getMessage());
            return false;
        }
    }

}
