<?php

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

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

    // database connection and table name
    private $conn;
    private $survey = "survey";

    // object properties
    public $survey_id;
    public $survey_publish_id;
    public $survey_title;
    public $survey_description;
    public $start_date;
    public $end_date;
    public $user_id;
    // constructor
    public function __construct() {
        $this->conn = DBConnection();
    }
    
    public function getSurvey($from_record_num, $records_per_page) {
        try {
            $query = "select sp.survey_publish_id,s.survey_title,s.survey_description,sp.start_date,sp.end_date 
            from survey_assignment as sa 
            join survey_publish sp on sa.survey_id=sp.survey_id and sp.status='active' and sp.start_date<=:start_date and sp.end_date>=:end_date
            join survey s on sa.survey_id=s.survey_id and s.status='active' 
            WHERE sa.user_id=:user_id and sa.status is null and
            sa.survey_id not in(select survey_id from survey_result where user_id=:user_id1) 
            order by sp.created_ts desc LIMIT :start, :end";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind value
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id1", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":start_date", $this->date);
            $stmt->bindParam(":end_date", $this->date);
            $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");
        }
    }

    public function getSurvey_count() {
        try {
            $query = "select COUNT( sp.survey_publish_id)
            from survey_assignment as sa 
            join survey_publish sp on sa.survey_id=sp.survey_id and sp.status='active' and sp.start_date<=:start_date and sp.end_date>=:end_date
            join survey s on sa.survey_id=s.survey_id and s.status='active' 
            WHERE sa.user_id=:user_id and sa.status is null and
            sa.survey_id not in(select survey_id from survey_result where user_id=:user_id1)";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind value
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id1", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":start_date", $this->date);
            $stmt->bindParam(":end_date", $this->date);
            // 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 isSurvey() {
        try {
            $query = $this->conn->prepare("select * 
            from survey_assignment as sa 
            join survey_publish sp on sa.survey_id=sp.survey_id and sp.status='active' and sp.start_date<=:start_date and sp.end_date>=:end_date
            join survey s on sa.survey_id=s.survey_id and s.status='active' 
            WHERE sa.user_id=:user_id and sa.status is null and
            sa.survey_id not in(select survey_id from survey_result where user_id=:user_id1)");
            $query->bindParam(":start_date", $this->date);
            $query->bindParam(":end_date", $this->date);
            $query->bindParam(":user_id",$this->user_id);
            $query->bindParam(":user_id1",$this->user_id);
            $query->execute();
            if ($query->rowCount() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    
    
    public function getSurveyLat() {
        try {
            $query = "select sp.survey_publish_id,s.survey_title,s.survey_description,sp.start_date,sp.end_date 
            from survey_assignment as sa 
            join survey_publish sp on sa.survey_id=sp.survey_id and sp.status='active' and sp.start_date<=:start_date and sp.end_date>=:end_date
            join survey s on sa.survey_id=s.survey_id and s.status='active' 
            WHERE sa.user_id=:user_id and sa.status is null and
            sa.survey_id not in(select survey_id from survey_result where user_id=:user_id1) 
            order by sp.created_ts desc LIMIT 1";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind value
            $stmt->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":user_id1", $this->user_id, PDO::PARAM_INT);
            $stmt->bindParam(":start_date", $this->date);
            $stmt->bindParam(":end_date", $this->date);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function survey() {
        try {
                $query = "select * from survey_publish as sp,survey as s where sp.survey_publish_id=:sur_id and  sp.survey_id=s.survey_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind value
            $stmt->bindParam(":sur_id", $this->sur_id);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

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

    public function getSurvey_question() {
        try {
                $query = "SELECT * FROM survey_question where survey_id=:survey_id order by question_order Asc";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind value
            $stmt->bindParam(":survey_id", $this->survey_id);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_surveyoption_count() {
        try {
            $query = "Select count(survey_question_option_id) as count_option_id "
                    . "from survey_question_options "
                    . "where survey_question_id=:survey_question_id ";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":survey_question_id", $this->survey_question_id, PDO::PARAM_INT);
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    function get_surveyoptions() {
        try {
            $query = "Select option_desc,survey_question_option_id "
                    . "from survey_question_options "
                    . "where survey_question_id=:survey_question_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":survey_question_id", $this->survey_question_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function getSurvey_group() {
        try {
                $query = "SELECT * FROM survey_question_group where survey_id=:survey_id ";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind value
            $stmt->bindParam(":survey_id", $this->survey_id);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //group
    public function get_no_questions_group() {
        // query to count existing cart item
        try {
            $query = "Select count(survey_question_id) as no_questions "
                    . "from survey_question "
                    . "where survey_id=:survey_id and survey_question_group_id=:survey_question_group_id";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // sanitize
           
            // bind category id variable
            $stmt->bindParam(":survey_id", $this->survey_id);
            $stmt->bindParam(":survey_question_group_id", $this->survey_question_group_id);
            // execute query
            $stmt->execute();
            // get row value
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function getSurvey_question_group() {
        try {
                $query = "SELECT * FROM survey_question where survey_id=:survey_id and survey_question_group_id=:survey_question_group_id ";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind value
            $stmt->bindParam(":survey_id", $this->survey_id);
            $stmt->bindParam(":survey_question_group_id", $this->survey_question_group_id);
            // execute query
            $stmt->execute();
            // return values
            return $stmt;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    
    function insert_survey_response() {
        try {
            // query to insert cart item record
            $query = "INSERT INTO
               survey_result
            SET
                survey_id = :survey_id,
                user_id = :user_id,
                survey_question_id=:question_id,
                survey_question_option_id=:user_response,
                text_others=:comment,
                last_updated_ts=:last_updated_ts";
            $stmt = $this->conn->prepare($query);
            // bind values
            $stmt->bindParam(":survey_id", $this->survey_id);
            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":question_id", $this->question_id);
            $stmt->bindParam(":comment", $this->comment);
            $stmt->bindParam(":user_response", $this->user_response);
            $stmt->bindParam(":last_updated_ts", $this->last_updated_ts);
            // execute query
            if ($stmt->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    
    public function getSurvey_question_typeid() {
        try {
                $query = "SELECT question_type_id FROM survey_question WHERE survey_question_id=:question_id ";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind value
            $stmt->bindParam(":question_id", $this->question_id);
            // execute query
            $stmt->execute();
            // return values
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
    
    public function get_surveyoptions_count() {
        try {
                $query = "SELECT count(*) FROM survey_question_options WHERE survey_question_id=:survey_question_id ";
            // prepare query statement
            $stmt = $this->conn->prepare($query);
            // bind value
           $stmt->bindParam(":survey_question_id", $this->survey_question_id, PDO::PARAM_INT);
            // execute query
            $stmt->execute();
            // return values
            $rows = $stmt->fetch(PDO::FETCH_NUM);
            // return
            return $rows[0];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }
}

