<?php

/**
 * @preserve d22d5078ffadb754b5170383f19ddc8d
 *
 * d22d5078ffadb754b5170383f19ddc8d
 * htmlphp
 * lab6
 * v2
 * rasb14
 * 2017-10-21 16:29:48
 * v2.3.8 (2017-10-19)
 *
 * Generated 2017-10-21 18:29:48 by dbwebb lab-utility v2.3.8 (2017-10-19).
 * https://github.com/mosbth/lab
 */

// Set error reporting to verbose
error_reporting(-1);              // Report all type of errors
ini_set('display_errors', 1);     // Display all errors

// Load and create object with lab utilities
require __DIR__ . "/.Dbwebb.php";
$dbwebb = new Dbwebb();


/** ===================================================================
 * Lab 6 - Htmlphp
 *
 * In this lab you will be working with a SQLite database file: `myDB.sqlite`
 * and PDO.
 *
 * Do not forget to check the [PHP Manual on
 * PDO](http://php.net/manual/en/book.pdo.php)
 *
 */


/** -------------------------------------------------------------------
 * Section 1 . Working with SQLite and PDO
 *
 * The database has one table called `people`.
 *
 * The table 'people' has six columns:
 *
 * > `id`, `firstName`, `lastName`, `born`, `cityOfBirth`, `countryOfBirth`.
 *
 */


/**
 * Exercise 1.1 (1 points)
 *
 * Find the firstnames of the people born in England. Answer with an array
 * containing their names.
 *
 * Write your code below and put the answer into the variable ANSWER.
 */

$dsn = "sqlite:" . __DIR__ . "/myDB.sqlite";
$db = new PDO($dsn);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$query = "SELECT firstName FROM people WHERE countryOfBirth == ?";
$stmt = $db->prepare($query);
$stmt->execute(["England"]);
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

$arr = array();
foreach ($res as $value) {
    array_push($arr, $value["firstName"]);
}
$ANSWER = $arr;

// I will now test your answer - change false to true to get a hint.
echo $dbwebb->assertEqual("1.1", $ANSWER, false);

/**
 * Exercise 1.2 (1 points)
 *
 * Find the first name and last name of the person born 1962.
 * Answer with a string in the format: `"Firstname Lastname"`.
 *
 * Write your code below and put the answer into the variable ANSWER.
 */

$query = "SELECT firstName, lastName FROM people WHERE born == ?";
$stmt = $db->prepare($query);
$stmt->execute([1962]);
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

$name = $res[0]["firstName"] . " " . $res[0]["lastName"];

$ANSWER = $name;

// I will now test your answer - change false to true to get a hint.
echo $dbwebb->assertEqual("1.2", $ANSWER, false);

/**
 * Exercise 1.3 (1 points)
 *
 * Sum the years the persons with the lastnames `Hanks` and `Bloom` were born.
 * Answer with an integer.
 *
 * Write your code below and put the answer into the variable ANSWER.
 */

$query = "SELECT born FROM people WHERE lastName == ? OR lastName = ?";
$stmt = $db->prepare($query);
$stmt->execute(["Hanks", "Bloom"]);
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

$sum = 0;
foreach ($res as $value) {
    $sum += $value["born"];
}

$ANSWER = $sum;

// I will now test your answer - change false to true to get a hint.
echo $dbwebb->assertEqual("1.3", $ANSWER, false);

/**
 * Exercise 1.4 (1 points)
 *
 * Count the number of entries in the table `people`. Answer with an integer.
 *
 * Write your code below and put the answer into the variable ANSWER.
 */

$query = "SELECT * FROM people";
$stmt = $db->prepare($query);
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

$ANSWER = intval(count($res));

// I will now test your answer - change false to true to get a hint.
echo $dbwebb->assertEqual("1.4", $ANSWER, false);

/**
 * Exercise 1.5 (1 points)
 *
 * Find which country `(countryOfBirth)` the oldest person was born in. Answer
 * with a string.
 *
 * Write your code below and put the answer into the variable ANSWER.
 */

$query = "SELECT countryOfBirth FROM people ORDER BY born ASC LIMIT 1";
$stmt = $db->prepare($query);
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

$ANSWER = $res[0]["countryOfBirth"];

// I will now test your answer - change false to true to get a hint.
echo $dbwebb->assertEqual("1.5", $ANSWER, false);

/**
 * Exercise 1.6 (1 points)
 *
 * What is the average value of the column `born`? Answer with an integer.
 *
 * Write your code below and put the answer into the variable ANSWER.
 */

$query = "SELECT born FROM people";
$stmt = $db->prepare($query);
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

$sum = 0;
foreach ($res as $value) {
    $sum += $value["born"];
}
$ans = $sum / count($res);

$ANSWER = intval($ans);

// I will now test your answer - change false to true to get a hint.
echo $dbwebb->assertEqual("1.6", $ANSWER, false);

/**
 * Exercise 1.7 (1 points)
 *
 * Find the youngest person born in USA. Which city is he/she born in? Answer
 * with a string.
 *
 * Write your code below and put the answer into the variable ANSWER.
 */

$query = "SELECT cityOfBirth FROM people WHERE countryOfBirth == ? ORDER BY born DESC LIMIT 1";
$stmt = $db->prepare($query);
$stmt->execute(["USA"]);
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

$ANSWER = $res[0]["cityOfBirth"];

// I will now test your answer - change false to true to get a hint.
echo $dbwebb->assertEqual("1.7", $ANSWER, false);

/** -------------------------------------------------------------------
 * Section 2 . Extra assignments
 *
 * These questions are worth 3 points each. Solve them for extra points.
 *
 */


/**
 * Exercise 2.1 (3 points)
 *
 * Get the first name and lastname of all persons in the database. Order them
 * by their last name, alphabetically and ascending.
 *
 * Answer with an array of strings, like this:
 *
 * > `["lastName firstName", "lastName firstName"]`.
 *
 * Write your code below and put the answer into the variable ANSWER.
 */

$query = "SELECT firstName, lastName FROM people ORDER BY lastName ASC";
$stmt = $db->prepare($query);
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

$arr = array();
foreach ($res as $value) {
    array_push($arr, $value["lastName"] . " " . $value["firstName"]);
}

$ANSWER = $arr;

// I will now test your answer - change false to true to get a hint.
echo $dbwebb->assertEqual("2.1", $ANSWER, false);


// Wrap it up
exit($dbwebb->exitWithSummary());