/**
* Connects to DB and inserts the user response.
* @param $response (array) data we'll be saving to the response table
* @return builds and returns a response message
*/
protected function increase_mc_option_responses($mc_option_id)
{
// connect to PDO
$pdo = new enp_quiz_Db();
// Get our Parameters ready
$params = array(':mc_option_id' => $mc_option_id);
// write our SQL statement
// write our SQL statement
$sql = "UPDATE " . $pdo->question_mc_option_table . "\n SET mc_option_responses = mc_option_responses + 1\n WHERE mc_option_id = :mc_option_id";
// update the question view the database
$stmt = $pdo->query($sql, $params);
// success!
if ($stmt !== false) {
// set-up our response array
$return = array('mc_option_id' => $mc_option_id, 'status' => 'success', 'action' => 'increase_mc_option_responses');
// merge the response arrays
self::$return = array_merge($return, self::$return);
// see what type of question we're working on and save that response
} else {
// handle errors
self::$return['error'][] = 'Increase MC Option Responses failed.';
}
// return response
return self::$return;
}
/**
* Selects all quiz ids from the DB
* @return array of all quiz ids
*/
public function get_all_quiz_ids()
{
$pdo = new enp_quiz_Db();
$sql = "SELECT quiz_id from " . $pdo->quiz_table;
$stmt = $pdo->query($sql);
$quiz_ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
return $quiz_ids;
}
public function select_ab_test_question_results($ab_test_id)
{
$pdo = new enp_quiz_Db();
// Do a select query to see if we get a returned row
$params = array(":ab_test_id" => $ab_test_id, ":mc_option_id" => $this->get_mc_option_id());
$sql = "SELECT COUNT(*) from " . $pdo->response_ab_test_table . " ab_response\n INNER JOIN " . $pdo->response_mc_table . " mc_response\n ON ab_response.response_quiz_id = mc_response.response_quiz_id\n WHERE ab_response.ab_test_id = :ab_test_id\n AND mc_response.mc_option_id = :mc_option_id\n AND mc_response.response_mc_is_deleted = 0";
$stmt = $pdo->query($sql, $params);
$results = $stmt->fetchColumn();
// return the found results
return $results;
}
public function select_ab_test_slider_results($ab_test_id)
{
$pdo = new enp_quiz_Db();
// Do a select query to see if we get a returned row
$params = array(":ab_test_id" => $ab_test_id, ":slider_id" => $this->get_slider_id());
$sql = "SELECT response_slider from " . $pdo->response_ab_test_table . " ab_response\n INNER JOIN " . $pdo->response_slider_table . " slider_response\n ON ab_response.response_quiz_id = slider_response.response_quiz_id\n WHERE ab_response.ab_test_id = :ab_test_id\n AND slider_response.slider_id = :slider_id\n AND slider_response.response_slider_is_deleted = 0";
$stmt = $pdo->query($sql, $params);
$results = $stmt->fetchAll(PDO::FETCH_COLUMN);
// return the found results
return $results;
}
public function select_ab_test_question_results($question_id, $ab_test_id)
{
$pdo = new enp_quiz_Db();
// Do a select query to see if we get a returned row
$params = array(":ab_test_id" => $ab_test_id, ":question_id" => $question_id);
$sql = "SELECT * from " . $pdo->response_ab_test_table . " ab_response\n INNER JOIN " . $pdo->response_question_table . " question_response\n ON ab_response.response_quiz_id = question_response.response_quiz_id\n WHERE ab_response.ab_test_id = :ab_test_id\n AND question_response.question_id = :question_id\n AND question_response.response_question_is_deleted = 0";
$stmt = $pdo->query($sql, $params);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
// return the found results
return $results;
}
/**
* For using PDO to select one ab_test row
*
* @param $ab_test_id = ab_test_id that you want to select
* @return row from database table if found, false if not found
**/
public function select_ab_test_by_id($ab_test_id)
{
$pdo = new enp_quiz_Db();
// Do a select query to see if we get a returned row
$params = array(":ab_test_id" => $ab_test_id);
$sql = "SELECT * from " . $pdo->ab_test_table . " WHERE\n ab_test_id = :ab_test_id\n AND ab_test_is_deleted = 0";
$stmt = $pdo->query($sql, $params);
$ab_test_row = $stmt->fetch();
// return the found quiz row
return $ab_test_row;
}
/**
* For using PDO to select one slider row
*
* @param $slider_id = slider_id that you want to select
* @return row from database table if found, false if not found
**/
public function select_slider_by_id($slider_id)
{
$pdo = new enp_quiz_Db();
// Do a select query to see if we get a returned row
$params = array(":slider_id" => $slider_id);
$sql = "SELECT * from " . $pdo->question_slider_table . " WHERE\n slider_id = :slider_id\n AND slider_is_deleted = 0";
$stmt = $pdo->query($sql, $params);
$slider_row = $stmt->fetch();
// return the found slider row
return $slider_row;
}
public function select_slider_question_results($slider_id)
{
$pdo = new enp_quiz_Db();
// Do a select query to see if we get a returned row
$params = array(":slider_id" => $slider_id);
$sql = "SELECT response_slider from " . $pdo->response_slider_table . "\n WHERE slider_id = :slider_id\n AND response_slider_is_deleted = 0\n ORDER BY response_slider";
$stmt = $pdo->query($sql, $params);
$results = $stmt->fetchAll(PDO::FETCH_COLUMN);
// return the found results
return $results;
}
/**
* Set the ab_tests for our User Object
* @param $user_id
* @return ab_test array of ids array(3,4,5) from the database
*/
protected function set_ab_tests()
{
$pdo = new enp_quiz_Db();
// Do a select query to see if we get a returned row
$params = array(":user_id" => $this->user_id);
$sql = "SELECT ab_test_id from " . $pdo->ab_test_table . " WHERE\n ab_test_owner = :user_id\n AND ab_test_is_deleted = 0";
$stmt = $pdo->query($sql, $params);
$ab_test_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$ab_tests = array();
foreach ($ab_test_rows as $row => $ab_test) {
$ab_tests[] = (int) $ab_test['ab_test_id'];
}
return $ab_tests;
}
protected function update_quiz_option($quiz_option, $quiz_option_id)
{
// connect to PDO
$pdo = new enp_quiz_Db();
// Get our Parameters ready
$params = array(':quiz_option_id' => $quiz_option_id, ':quiz_option_name' => $quiz_option, ':quiz_option_value' => parent::$quiz[$quiz_option]);
$sql = "UPDATE " . $pdo->quiz_option_table . "\n SET quiz_option_value = :quiz_option_value\n WHERE quiz_option_id = :quiz_option_id\n AND quiz_option_name = :quiz_option_name\n ";
$stmt = $pdo->query($sql, $params);
// success!
if ($stmt !== false) {
// set-up our response array
$quiz_option_response = array('quiz_option_id' => $quiz_option_id, 'status' => 'success', 'action' => 'update');
// pass the response array to our response object
parent::$response_obj->set_quiz_option_response($quiz_option_response, $quiz_option);
} else {
self::$response_obj->add_error('Quiz option ' . $quiz_option . ' could not be updated. Try again and if it continues to not work, send us an email with details of how you got to this error.');
}
}
/**
* Connects to DB and inserts the user response.
* @param $response (array) data we'll be saving to the response table
* @return builds and returns a response message
*/
protected function insert_response_slider($response)
{
// connect to PDO
$pdo = new enp_quiz_Db();
// Get our Parameters ready
$params = array(':response_quiz_id' => $response['response_quiz_id'], ':response_question_id' => $response['response_question_id'], ':slider_id' => $response['slider_id'], ':response_slider' => $response['question_response']);
// write our SQL statement
$sql = "INSERT INTO " . $pdo->response_slider_table . " (\n response_quiz_id,\n response_question_id,\n slider_id,\n response_slider\n )\n VALUES(\n :response_quiz_id,\n :response_question_id,\n :slider_id,\n :response_slider\n )";
// insert the slider into the database
$stmt = $pdo->query($sql, $params);
// success!
if ($stmt !== false) {
// add our response ID to the array we're working with
$response['response_slider_id'] = $pdo->lastInsertId();
// set-up our response array
$response_response = array('response_slider_id' => $response['response_slider_id'], 'status' => 'success', 'action' => 'insert');
// we added a slider successfully, let them know!
return $response;
} else {
// handle errors
return false;
}
}
/**
* Connects to DB and inserts the user response.
* @param $response (array) data we'll be saving to the response table
* @return builds and returns a response message
*/
private function insert_response_ab_test($ab_test_id, $response_quiz_id)
{
// connect to PDO
$pdo = new enp_quiz_Db();
// Get our Parameters ready
$params = array(':ab_test_id' => $ab_test_id, ':response_quiz_id' => $response_quiz_id);
$sql = "INSERT INTO " . $pdo->response_ab_test_table . " (\n response_quiz_id,\n ab_test_id\n )\n VALUES(\n :response_quiz_id,\n :ab_test_id\n )";
// update the question view the database
$stmt = $pdo->query($sql, $params);
// success!
if ($stmt !== false) {
// set-up our response array
$return = array('result_ab_test_id' => $pdo->lastInsertId(), 'status' => 'success', 'action' => 'insert_ab_test_result');
// merge the response arrays
self::$return = array_merge($return, self::$return);
// see what type of question we're working on and save that response
} else {
// handle errors
self::$return['error'][] = 'Insert ab_test_result_id failed.';
}
// return response
return self::$return;
}
protected function get_response_question_id($response)
{
// connect to PDO
$pdo = new enp_quiz_Db();
// Get our Parameters ready
$params = array(':response_quiz_id' => $response['response_quiz_id'], ':question_id' => $response['question_id']);
$sql = "SELECT response_question_id from " . $pdo->response_question_table . " WHERE\n response_quiz_id = :response_quiz_id\n AND question_id = :question_id";
$stmt = $pdo->query($sql, $params);
$result = $stmt->fetch();
return $result['response_question_id'];
}
protected function delete_slider_responses($slider_id)
{
// connect to PDO
$pdo = new enp_quiz_Db();
// Get our Parameters ready
$params = array(':slider_id' => $slider_id);
// write our SQL statement
$sql = "UPDATE " . $pdo->response_slider_table . "\n SET response_slider_is_deleted = 1\n WHERE slider_id = :slider_id";
// update the quiz responses the database
$stmt = $pdo->query($sql, $params);
if ($stmt !== false) {
// success!
return $stmt->rowCount();
} else {
// error :(
return false;
}
}
/**
* Connects to DB and updates the question.
* @param $question = formatted question array
* @param $quiz_id = which quiz this question goes with
* @return builds and returns a response message
*/
protected function update_question()
{
// connect to PDO
$pdo = new enp_quiz_Db();
// Get our Parameters ready
$params = array(':question_id' => self::$question['question_id'], ':question_title' => self::$question['question_title'], ':question_image' => self::$question['question_image'], ':question_image_alt' => self::$question['question_image_alt'], ':question_type' => self::$question['question_type'], ':question_explanation' => self::$question['question_explanation'], ':question_order' => self::$question['question_order'], ':question_is_deleted' => self::$question['question_is_deleted']);
// write our SQL statement
$sql = "UPDATE " . $pdo->question_table . "\n SET question_title = :question_title,\n question_image = :question_image,\n question_image_alt = :question_image_alt,\n question_type = :question_type,\n question_explanation = :question_explanation,\n question_order = :question_order,\n question_is_deleted = :question_is_deleted\n\n WHERE question_id = :question_id";
// update the question in the database
$stmt = $pdo->query($sql, $params);
// success!
if ($stmt !== false) {
// set-up our response array
$question_response = array('status' => 'success', 'action' => 'update');
$question_response = array_merge($this->build_question_response(), $question_response);
// pass the response array to our response object
parent::$response_obj->set_question_response($question_response, self::$question);
// see if we were deleting a question in here...
if (self::$question['question_is_deleted'] === 1) {
// we deleted a question successfully. Let's let them know!
parent::$response_obj->add_success('Question deleted.');
}
// pass the question on to save_mc_option or save_slider
$this->save_question_type_options();
} else {
parent::$response_obj->add_error('Question number ' . self::$question['question_order'] . ' could not be updated.');
}
}
/**
* Connects to DB and updates the question.
* @param $question = formatted question array
* @param $question_id = which quiz this question goes with
* @return builds and returns a response message
*/
protected function update_slider()
{
// connect to PDO
$pdo = new enp_quiz_Db();
// Get our Parameters ready
$params = array(':slider_id' => self::$slider['slider_id'], ':slider_range_low' => self::$slider['slider_range_low'], ':slider_range_high' => self::$slider['slider_range_high'], ':slider_correct_low' => self::$slider['slider_correct_low'], ':slider_correct_high' => self::$slider['slider_correct_high'], ':slider_increment' => self::$slider['slider_increment'], ':slider_prefix' => self::$slider['slider_prefix'], ':slider_suffix' => self::$slider['slider_suffix']);
// write our SQL statement
$sql = "UPDATE " . $pdo->question_slider_table . "\n SET slider_range_low = :slider_range_low,\n slider_range_high = :slider_range_high,\n slider_correct_low = :slider_correct_low,\n slider_correct_high = :slider_correct_high,\n slider_increment = :slider_increment,\n slider_prefix = :slider_prefix,\n slider_suffix = :slider_suffix\n\n WHERE slider_id = :slider_id";
// update the slider in the database
$stmt = $pdo->query($sql, $params);
// success!
if ($stmt !== false) {
// set-up our response array
$slider_response = array('slider_id' => self::$slider['slider_id'], 'status' => 'success', 'action' => 'update');
$slider_response = array_merge(self::$slider, $slider_response);
// pass the response array to our response object
parent::$response_obj->set_slider_response($slider_response, parent::$question);
} else {
// add an error that we couldn't update the slider
parent::$response_obj->add_error('Question #' . (parent::$question['question_order'] + 1) . ' could not update the Slider. Please try again and contact support if you continue to see this error message.');
}
}
/**
* Set the slider for our Question Object
* @param $quiz_id
* @return slider id from the database
*/
protected function set_slider()
{
$question_id = self::$question['question_id'];
$pdo = new enp_quiz_Db();
// Do a select query to see if we get a returned row
$params = array(":question_id" => $question_id);
$sql = "SELECT slider_id from " . $pdo->question_slider_table . " WHERE\n question_id = :question_id\n AND slider_is_deleted = 0";
$stmt = $pdo->query($sql, $params);
$slider_id = $stmt->fetch();
return $slider_id['slider_id'];
}
/**
* Delete an AB Test
* @param $ab_test (array) full info on data to insert a new AB Test
* @return response (array)
*/
private function delete_ab_test($ab_test)
{
// connect to PDO
$pdo = new enp_quiz_Db();
// Get our Parameters ready
$params = array(':ab_test_id' => $ab_test['ab_test_id'], ':quiz_id_a' => $ab_test['quiz_id_a'], ':quiz_id_b' => $ab_test['quiz_id_b'], ':ab_test_updated_by' => $ab_test['ab_test_updated_by'], ':ab_test_updated_at' => $ab_test['ab_test_updated_at'], ':ab_test_is_deleted' => $ab_test['ab_test_is_deleted']);
// write our SQL statement
$sql = "UPDATE " . $pdo->ab_test_table . "\n SET ab_test_updated_by = :ab_test_updated_by,\n ab_test_updated_at = :ab_test_updated_at,\n ab_test_is_deleted = :ab_test_is_deleted\n WHERE ab_test_id = :ab_test_id\n AND quiz_id_a = :quiz_id_a\n AND quiz_id_b = :quiz_id_b";
// insert the quiz into the database
$stmt = $pdo->query($sql, $params);
// start the response
$this->response['action'] = 'update';
$this->response['user_action']['action'] = 'delete';
$this->response['user_action']['element'] = 'ab_test';
$this->response['ab_test_id'] = $this->ab_test['ab_test_id'];
$this->response['quiz_id_a'] = $this->ab_test['quiz_id_a'];
$this->response['quiz_id_b'] = $this->ab_test['quiz_id_b'];
// success!
if ($stmt !== false) {
// build more response stuff
$this->response['status'] = 'success';
$this->add_success('AB Test Deleted.');
} else {
$this->response['status'] = 'error';
$this->add_error('AB Test could not be deleted. Try again and if it continues to not work, send us an email with details of how you got to this error.');
}
return $this->response;
}
/**
* Connects to DB and update the user response when quiz restarted
* @param $response (array) data we'll be saving to the response quiz table
* @return builds and returns a response message
*/
public function update_response_quiz_restarted($response)
{
// connect to PDO
$pdo = new enp_quiz_Db();
// Get our Parameters ready
$params = array(':response_quiz_id' => $response['response_quiz_id'], ':quiz_id' => $response['quiz_id'], ':quiz_restarted' => 1, ':response_quiz_updated_at' => $response['response_quiz_updated_at']);
// write our SQL statement
$sql = "UPDATE " . $pdo->response_quiz_table . "\n SET quiz_restarted = :quiz_restarted,\n response_quiz_updated_at = :response_quiz_updated_at\n WHERE response_quiz_id = :response_quiz_id\n AND quiz_id = :quiz_id";
// insert the mc_option into the database
$stmt = $pdo->query($sql, $params);
// success!
if ($stmt !== false) {
// set-up our response array
$return = array('response_quiz_id' => $response['response_quiz_id'], 'status' => 'success', 'action' => 'updated_quiz_started');
// merge the response arrays
$return = array_merge($response, $return);
} else {
// handle errors
$return['error'] = 'Save response failed.';
}
// return response
return $return;
}
/**
* Select all AB Tests that a quiz is a part of
* @param $quiz_id
* @return $ab_test_ids (array) of all AB Test IDs
*/
protected function get_all_quiz_ab_tests($quiz_id)
{
$pdo = new enp_quiz_Db();
// Do a select query to see if we get a returned row
$params = array(":quiz_id" => $quiz_id);
$sql = "SELECT ab_test_id from " . $pdo->ab_test_table . "\n WHERE (quiz_id_a = :quiz_id OR quiz_id_b = :quiz_id)\n AND ab_test_is_deleted = 0";
$stmt = $pdo->query($sql, $params);
$ab_test_row = $stmt->fetchAll(PDO::FETCH_COLUMN);
// return the found quiz row
return $ab_test_row;
}
/**
* Connects to DB and updates the question.
* @param $question = formatted question array
* @param $question_id = which quiz this question goes with
* @return builds and returns a response message
*/
protected function update_mc_option()
{
// connect to PDO
$pdo = new enp_quiz_Db();
// Get our Parameters ready
$params = array(':mc_option_id' => self::$mc_option['mc_option_id'], ':mc_option_content' => self::$mc_option['mc_option_content'], ':mc_option_correct' => self::$mc_option['mc_option_correct'], ':mc_option_order' => self::$mc_option['mc_option_order'], ':mc_option_is_deleted' => self::$mc_option['mc_option_is_deleted']);
// write our SQL statement
$sql = "UPDATE " . $pdo->question_mc_option_table . "\n SET mc_option_content = :mc_option_content,\n mc_option_correct = :mc_option_correct,\n mc_option_order = :mc_option_order,\n mc_option_is_deleted = :mc_option_is_deleted\n\n WHERE mc_option_id = :mc_option_id";
// update the mc_option in the database
$stmt = $pdo->query($sql, $params);
// success!
if ($stmt !== false) {
// set-up our response array
$mc_option_response = array('mc_option_id' => self::$mc_option['mc_option_id'], 'status' => 'success', 'action' => 'update');
// pass the response array to our response object
parent::$response_obj->set_mc_option_response($mc_option_response, parent::$question, self::$mc_option);
// SUCCESS MESSAGES
// see if we we're deleting a mc_option in here...
if (self::$mc_option['mc_option_is_deleted'] === 1) {
// we deleted a question successfully. Let's let them know!
parent::$response_obj->add_success('Multiple Choice Option deleted from Question #' . (parent::$question['question_order'] + 1) . '.');
}
// See if we're setting one as CORRECT
if (self::$mc_option['mc_option_correct'] === 1) {
// we set a question as correct!
parent::$response_obj->add_success('Multiple Choice Option set as Correct on Question #' . (parent::$question['question_order'] + 1) . '.');
}
} else {
// add an error that we couldn't update the mc_option
parent::$response_obj->add_error('Question #' . (parent::$question['question_order'] + 1) . ' could not update Multiple Choice Option #' . (self::$mc_option['mc_option_order'] + 1) . '. Please try again and contact support if you continue to see this error message.');
}
}
/**
* Get the individual score data on each take of this quiz
* @param $quiz = quiz object
* @return array of all the scores
*/
public function get_quiz_scores()
{
$pdo = new enp_quiz_Db();
// Do a select query to see if we get a returned row
$params = array(":quiz_id" => $this->get_quiz_id());
$sql = "SELECT quiz_score from " . $pdo->response_quiz_table . "\n WHERE quiz_completed = 1\n AND quiz_id = :quiz_id\n AND response_quiz_is_deleted = 0\n ORDER BY quiz_score ASC";
$stmt = $pdo->query($sql, $params);
$scores = $stmt->fetchAll(PDO::FETCH_ASSOC);
$quiz_scores = array();
foreach ($scores as $score) {
$quiz_scores[] = (int) round($score['quiz_score'] * 100);
}
// return the found quiz row
return $quiz_scores;
}