<?php

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

/**
 * Description of Searcch
 *
 * @author Lenovo
 */
class Search {

    // database connection and table name
    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";
    // object properties
    public $offering_id;
    public $course_id;
    public $user_id;
    public $lesson_id;
    public $topic_id;
    public $date;
    public $datetime;

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

    public function activecourses() {
        try {
            $query = "SELECT co.course_name,co.course_description,co.start_date,co.end_date,co.offering_id "
                    . "FROM course_assignment as ca,course_offering as co "
                    . "WHERE ca.user_id=:user_id AND co.status='Active' AND ca.offering_id=co.offering_id "
                    . "AND ca.result is NULL AND ca.status IS NULL ORDER BY co.end_date,co.offering_name ASC";
            // 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 completedcourses() {
        try {
            // select all courses query
            $query = "SELECT co.course_name,co.course_description,co.start_date,co.end_date,co.offering_id "
                    . "FROM course_assignment as ca,course_offering as co "
                    . "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 co.end_date,co.offering_name ASC";
            // 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 incompletecourses() {
        try {
            $query = "SELECT co.course_name,co.course_description,co.start_date,co.end_date,co.offering_id "
                    . "FROM course_assignment as ca,course_offering as co "
                    . "WHERE ca.user_id=:user_id AND ca.offering_id=co.offering_id AND ca.result='incomplete' "
                    . "AND co.status='Active' AND ca.status IS NULL "
                    . "ORDER BY co.end_date,co.offering_name ASC";
            // 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 notices($construct) {
        try {
            // select all courses query
            $query = "SELECT nb.notice_board_id,nb.notice_subject, nb.notice_desc, nb.end_date,nb.start_date,
                  MIN(nbg.group_id) as group_id  
                  FROM notice_board_assignment as nbg 
                  JOIN notice_board as nb on nbg.notice_board_id=nb.notice_board_id 
                  Left join user_group_allocation as uga on nbg.group_id=uga.group_id  
                  WHERE nb.end_date>=:date AND nb.start_date<=:date1 
                  AND (nb.notice_subject regexp :construct OR nb.notice_desc regexp :construct1)
                  and (IF(nbg.group_id='0',nbg.user_id=:user_id,nbg.group_id=uga.group_id AND uga.user_id=:user_id1)
                  or (IF(nb.department_id !='',(nbg.group_id='0' and nbg.user_id='0' AND nb.department_id=(SELECT department_id from user_account where user_id=:user_id2) AND nb.company_id=(SELECT company_id from user_account where user_id=:user_id3)),(nbg.group_id='0' and nbg.user_id='0' AND nb.company_id=(SELECT company_id from user_account where user_id=:user_id4)))))
                  GROUP BY nb.notice_board_id  
                  ORDER BY nb.notice_board_id DESC";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id1", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id2", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id3", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id4", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":date", $this->date, PDO::PARAM_STR);
            $stmt->bindParam(":date1", $this->date, PDO::PARAM_STR);
            $stmt->bindParam(":construct", $construct, PDO::PARAM_STR);
            $stmt->bindParam(":construct1", $construct, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function referencematerials($construct) {
        try {
            // select all courses query
            $query = "SELECT rm.reference_material_id,MIN(rma.group_id) as group_id,rm.reference_title,rm.reference_desc,"
                    . "rm.reference_filename,rm.last_updated_ts "
                    . "FROM reference_material_assignment as rma "
                    . "JOIN reference_material as rm on rma.reference_material_id=rm.reference_material_id "
                    . "Left join user_group_allocation as uga on rma.group_id=uga.group_id "
                    . "WHERE rm.status='Active' "
                    . " AND (rm.reference_title regexp :construct OR rm.reference_desc regexp :construct1)"
                    . "AND (IF(rma.group_id='0',rma.user_id=:user_id,rma.group_id=uga.group_id AND uga.user_id=:user_id1) "
                    . " or (IF(rm.department_id !='',(rma.group_id='0' and rma.user_id='0' AND rm.department_id=(SELECT department_id from user_account where user_id=:user_id2) AND rm.company_id=(SELECT company_id from user_account where user_id=:user_id3)),(rma.group_id='0' and rma.user_id='0' AND rm.company_id=(SELECT company_id from user_account where user_id=:user_id4)))))"
                    . "GROUP BY rm.reference_material_id ORDER BY rm.reference_material_id DESC";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id1", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id2", $this->user_id, PDO::PARAM_INT);  
            $stmt->bindParam(":user_id3", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id4", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":construct", $construct, PDO::PARAM_STR);
            $stmt->bindParam(":construct1", $construct, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function course_search($construct) {
        try {
            // select all courses query
            $query = "SELECT co.course_name,co.course_description "
                    . "FROM course_offering as co "
                    . "WHERE co.offering_id =:offering_id "
                    . "AND (co.course_name regexp :construct OR co.course_description regexp :construct1) AND co.status='Active'";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":construct", $construct, PDO::PARAM_STR);
            $stmt->bindParam(":construct1", $construct, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function offering_lessons() {
        try {
            // select all courses query
            $query = "SELECT l.lesson_id,l.lesson_name,l.lesson_desc "
                    . "FROM offering_lesson as oa,lesson as l "
                    . "WHERE oa.offering_id=:offering_id AND oa.lesson_id=l.lesson_id AND l.status='Active'"
                    . "ORDER BY l.lesson_no asc";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function lesson_search($construct) {
        try {
            // select all courses query
            $query = "SELECT l.lesson_id,l.lesson_name,l.lesson_desc "
                    . "FROM lesson as l "
                    . "WHERE l.lesson_id =:lesson_id "
                    . "AND (l.lesson_name regexp :construct OR l.lesson_desc regexp :construct1) AND l.status='Active'";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":lesson_id", $this->lesson_id, PDO::PARAM_INT);
            $stmt->bindParam(":construct", $construct, PDO::PARAM_STR);
            $stmt->bindParam(":construct1", $construct, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function content_search($construct) {
        try {
            // select all courses query
            $query = "SELECT c.content_title,c.content_desc,c.contentdtl_id "
                    . "FROM offering_content as oc,content_details as c "
                    . "WHERE oc.offering_id =:offering_id "
                    . "AND oc.lesson_id =:lesson_id "
                    . "AND oc.contentdtl_id =c.contentdtl_id  "
                    . "AND (c.content_title regexp :construct OR c.content_desc regexp :construct1)  AND c.status='Active'";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":offering_id", $this->offering_id, PDO::PARAM_INT);
            $stmt->bindParam(":lesson_id", $this->lesson_id, PDO::PARAM_INT);
            $stmt->bindParam(":construct", $construct, PDO::PARAM_STR);
            $stmt->bindParam(":construct1", $construct, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function forum_topics() {
        try {
            // select all courses query
            $query = "SELECT ft.topic_name,ft.topic_id FROM forum_topic as ft
					JOIN user_account u on ft.user_id=u.user_id AND ft.company_id=(SELECT company_id from user_account where user_id=:user_id)
					WHERE ft.status='Active'";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function topic_search($construct) {
        try {
            // select all courses query
            $query = "SELECT topic_name,topic_desc,topic_id "
                    . "FROM forum_topic "
                    . "WHERE topic_id =:topic_id "
                    . "AND (topic_desc regexp :construct) AND status='Active'";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":topic_id", $this->topic_id, PDO::PARAM_INT);
            $stmt->bindParam(":construct", $construct, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function topic_response_search($construct) {
        try {
            // select all courses query
            $query = "SELECT response_desc,response_id "
                    . "FROM forum_topic_response "
                    . "WHERE topic_id =:topic_id "
                    . "AND (response_desc regexp :construct) AND status='Active'";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind limit clause variables
            $stmt->bindParam(":topic_id", $this->topic_id, PDO::PARAM_INT);
            $stmt->bindParam(":construct", $construct, PDO::PARAM_STR);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

}
