function getRequestedCategory($categoryID, &$strResponseData)
{
    assert(isset($categoryID));
    $arrCategoryItems = array();
    $strResponseMessage = "Unsuccessful";
    $dbConnection = getDBConnection($strResponseMessage);
    if (!$dbConnection->connect_errno) {
        $stmtQuery = "SELECT category_item_id, name, price FROM icaict515a_category_items";
        $stmtQuery .= " WHERE category_id=?";
        if ($stmt = $dbConnection->prepare($stmtQuery)) {
            $categoryID = scrubInput($categoryID, $dbConnection);
            $stmt->bind_param('s', $categoryID);
            if ($stmt->execute()) {
                $stmt->bind_result($db_category_item_id, $db_name, $db_price);
                while ($stmt->fetch()) {
                    $orderItem = new structCategoryItem();
                    $orderItem->categoryItemID = $db_category_item_id;
                    $orderItem->name = $db_name;
                    $orderItem->price = $db_price;
                    $arrCategoryItems[] = $orderItem;
                }
                $strResponseMessage = "Success";
            }
            $stmt->close();
            // Free resultset
        }
        $dbConnection->close();
    }
    $strResponseData = json_encode($arrCategoryItems);
    return $strResponseMessage;
}

/**
 * Retrieves data & tags for all the videos that have been fully processed.
 * @return format: JSON array, items: {id: , assigned_id: , title: , url: , preview_img: , vieo_url: , status: , tags: [{id: , content: , accepted: }]}
 */
function get_history()
{
    $conn = getDBConnection();
    $sqlResult = $conn->query("SELECT * FROM media WHERE status=\"" . STATUS_HISTORY . "\" ORDER BY id DESC");
    // build media ID string for later, set up media items array
    // array indizes = media id (so we don't have so many loops later :) )
    $mediaIDs = "(";
    $mediaItems = array();
    if ($sqlResult->num_rows > 0) {
        while ($row = $sqlResult->fetch_assoc()) {
            $mediaIDs .= $row["id"] . ",";
            $mediaItems[$row["id"]] = array("id" => $row["id"], "assigned_id" => $row["assigned_id"], "title" => utf8_encode($row["title"]), "url" => $row["url"], "preview_img" => $row["preview_image"], "video_url" => $row["video_url"], "tags" => array());
        }
    }
    if (mb_substr($mediaIDs, -1) == ",") {
        $mediaIDs = substr($mediaIDs, 0, strlen($mediaIDs) - 1);
    }
    $mediaIDs .= ")";
    // query using the media ID string, assign tags to the media items
    if (sizeof($mediaItems) > 0) {
        $tags = $conn->query("SELECT * FROM tags WHERE media_id IN {$mediaIDs}");
        if ($tags->num_rows > 0) {
            while ($row = $tags->fetch_assoc()) {
                // append "object" to the tags array in the media item
                $mediaItems[$row["media_id"]]["tags"][] = array("id" => $row["id"], "content" => utf8_encode($row["content"]), "accepted" => $row["accepted"]);
            }
        }
    }
    $conn->close();
    $arrayToPrint = array();
    foreach ($mediaItems as $key => $item) {
        $arrayToPrint[] = $item;
    }
    echo json_encode($arrayToPrint);
}

function actionQuery($query)
{
    $conn = getDBConnection();
    $stmt = $conn->prepare($query);
    $stmt->execute();
    return $conn->affected_rows;
}

function getData($lastID)
{
    $sql = "SELECT * FROM chat WHERE id > " . $lastID . " ORDER BY id ASC LIMIT 60";
    $conn = getDBConnection();
    $results = mysql_query($sql, $conn);
    if (!$results || empty($results)) {
        //echo 'There was an error creating the entry';
        end;
    }
    while ($row = mysql_fetch_array($results)) {
        //the result is converted from the db setup (see initDB.php)
        $name = $row[2];
        $text = $row[3];
        $id = $row[0];
        if ($name == '') {
            $name = 'no name';
        }
        if ($text == '') {
            $name = 'no message';
        }
        echo $id . " ---" . $name . " ---" . $text . " ---";
        // --- is being used to separete the fields in the output
    }
    echo "end";
}

function updateDeptDB($deptID, $deptName, $deptManagerID, $deptBudget)
{
    assert(isset($deptID));
    assert(isset($deptName));
    assert(isset($deptManagerID));
    assert(isset($deptBudget));
    global $strResponseMessage;
    global $strResponseData;
    $strResponseMessage = "Unsuccessful";
    $strResponseData = "Update failed. Please contact Administrator to update details";
    $dbConnection = getDBConnection($strResponseMessage);
    if (!$dbConnection->connect_errno) {
        $stmtQuery = "UPDATE icaict515a_departments SET name=?, manager_id=?, budget=?";
        $stmtQuery .= " WHERE department_id=?";
        if ($stmt = $dbConnection->prepare($stmtQuery)) {
            $deptID = scrubInput($deptID, $dbConnection);
            $deptName = scrubInput($deptName, $dbConnection);
            $deptManagerID = scrubInput($deptManagerID, $dbConnection);
            $deptBudget = scrubInput($deptBudget, $dbConnection);
            $stmt->bind_param("ssss", $deptName, $deptManagerID, $deptBudget, $deptID);
            if ($stmt->execute()) {
                $strResponseMessage = "Success";
                if ($dbConnection->affected_rows > 0) {
                    $strResponseData = "Update Successful";
                } else {
                    $strResponseData = "Nothing changed. Details are still the same.";
                }
            }
            $stmt->close();
        }
        $dbConnection->close();
    }
    return $strResponseMessage == "Success";
}

/**
 * !runscript subroutine!
 * <br>
 * Creates images from the frames of a video; one image is taken every .2 seconds.
 * @param $mediaID the video's ID
 * @param $queueID the ID of the item in the queue
 * @param $videoFilePath path to the video file (has only been tested on .mp4)
 * @param $segmentedVideoPath path for a folder that will contain some of the video's frames that are extracted in this method
 */
function segmentVideo($mediaID, $queueID, $videoFilePath, $segmentedVideoPath)
{
    $conn = getDBConnection();
    if (!file_exists($videoFilePath)) {
        $conn->query("UPDATE queue SET status=\"" . STATUS_DOWNLOAD_ERROR . "\" WHERE id={$queueID}");
        exit("Datei wurde nicht gefunden.");
    }
    segementVideo_setupFolder($segmentedVideoPath);
    $conn->query("UPDATE queue SET status=\"" . STATUS_SEGMENTING_VIDEO . "\" WHERE id={$queueID}");
    $ffProbeAndFfMpeg = segmentVideo_getFfProbeAndFfMpeg($queueID);
    if (!is_array($ffProbeAndFfMpeg)) {
        exit("Error creating ffmpeg and/or ffprobe.");
    }
    $ffprobe = $ffProbeAndFfMpeg[0];
    $ffmpeg = $ffProbeAndFfMpeg[1];
    $videoDuration = null;
    try {
        $videoDuration = $ffprobe->format($videoFilePath)->get('duration');
        // returns the duration property
    } catch (Exception $e) {
        $conn->query("UPDATE queue SET status=\"" . STATUS_SEGMENTING_ERROR . "\" WHERE id={$queueID}");
        $conn->close();
        error_log("Error initializing ffmpeg and/or ffprobe.");
        exit("Error creating ffmpeg and/or ffprobe.");
    }
    $video = $ffmpeg->open($videoFilePath);
    // 0.2: analyze 5 FPS
    for ($i = 0, $counter = 0; $i < $videoDuration; $i += 0.2, $counter++) {
        $video->frame(FFMpeg\Coordinate\TimeCode::fromSeconds($i))->save($segmentedVideoPath . "/frame_{$counter}.jpg");
    }
    $conn->query("UPDATE queue SET status=\"" . STATUS_FINISHED_SEGMENTING_VIDEO . "\" WHERE id={$queueID}");
    $conn->close();
    unlink($_GET["video_file_path"]);
}

 public static function loadAirportCodes()
 {
     // open db connection
     $db = getDBConnection();
     // create db query
     $tbl = _DBTableNames::$_airportCodes;
     $sqlstmt = "SELECT * FROM {$tbl}";
     $rs = null;
     // fetch the data
     if (!($rs = $db->query($sqlstmt))) {
         Logger::logMsg(__FILE__, __FUNCTION__, "Error executing query - " + $sqlstmt);
         // trigger_error(mysql_error(), E_USER_ERROR);
         return NULL;
     }
     $airportName = $code = null;
     for ($airportCodes = null; $row = mysqli_fetch_assoc($rs);) {
         // print_r($row);
         foreach ($row as $key => $val) {
             if (strcmp($key, "code") == 0) {
                 $code = $val;
             } else {
                 if (strcmp($key, "airport") == 0) {
                     $airportName = $val;
                 }
             }
         }
         //$rowEntry = "Code - $code; Name - $airportName";
         //Logger::logTxt($rowEntry);
         $airportCodes[$code] = $airportName;
     }
     return $airportCodes;
 }

function getData($lastID)
{
    require_once "conn.php";
    # getting connection data
    include "../include/settings.php";
    # getting table prefix
    include "../include/offset.php";
    $sql = "SELECT * FROM {$TABLE_PREFIX}chat WHERE (" . $CURUSER['uid'] . " = toid OR " . $CURUSER['uid'] . "= fromid AND private='yes')  ORDER BY id DESC";
    $conn = getDBConnection();
    # establishes the connection to the database
    $results = mysqli_query($conn, $sql);
    # getting the data array
    while ($row = mysqli_fetch_array($results)) {
        # getting the data array
        $id = $row[id];
        $uid = $row[uid];
        $time = $row[time];
        $name = $row[name];
        $text = $row[text];
        # if no name is present somehow, $name and $text are set to the strings under
        # we assume all must be ok, othervise no post will be made by javascript check
        # if ($name == '') { $name = 'Anonymous'; $text = 'No message'; }
        # we put together our chat using some css
        $chatout = "\n                 <li><span class='name'>" . date("d/m/Y H:i:s", $time - $offset) . " | <a href=index.php?page=userdetails&id=" . $uid . ">" . $name . "</a>:</span></li>\n                            <div class='lista' style='text-align:right;\n                                      margin-top:-13px;\n                                    margin-bottom:0px;\n                                   /* color: #006699;*/\n                          '>\n                          # {$id}</div>\n                 \n                 <!-- # chat output -->\n                 <div class='chatoutput'>" . format_shout($text) . "</div>\n                 ";
        echo $chatout;
        # echo as known handles arrays very fast...
    }
}

 public function count()
 {
     $mdb2 = getDBConnection();
     $this->fetchType = 'one';
     $this->setLimit($mdb2);
     $sql = "SELECT COUNT(*) FROM " . $this->table();
     return $this->doQuery($mdb2, $sql);
 }

 public function add($user, $body)
 {
     $sql = "INSERT INTO submissions (user, body) VALUES ({$user}, '{$body}')";
     $affected = getDBConnection()->exec($sql);
     if (PEAR::isError($affected)) {
         die($affected->getMessage());
     }
 }

function getBugCount()
{
    $conn = getDBConnection();
    $sql = " SELECT id\r\nFROM mantis_bug_table INNER JOIN mantis_bug_status\r\n WHERE DATEDIFF(CURDATE(),FROM_UNIXTIME(date_submitted)) >= " . $GLOBALS['targetDay'] . " \r\n AND mantis_bug_table.status = mantis_bug_status.status\r\n AND mantis_bug_table.status != 90 \r\n ";
    $result = mysqli_query($conn, $sql);
    $bugCount = mysqli_num_rows($result);
    return '(' . $bugCount . ') <span style="font-size:x-small">' . getFireDate() . '</span>';
}

function strategies_getStatistics($tournament)
{
    $tournament = intval($tournament);
    $link = getDBConnection();
    if (mysqli_select_db($link, getDBName())) {
        $query = "SELECT COUNT(*) as cnt, DAY(date) as dt FROM strategies " . ($tournament == -1 ? "" : " WHERE tournament=" . $tournament) . " GROUP BY DATE(date)";
        return mysqli_fetch_all(mysqli_query($link, $query));
    }
}

 public function __construct($host, $dbname, $username, $password)
 {
     $this->host = $host;
     $this->dbname = $dbname;
     $this->username = $username;
     $this->password = $password;
     $this->log = new Log();
     $this->db = getDBConnection();
 }

function getBookInfoArray()
{
    $conn = getDBConnection();
    $stmt = $conn->prepare("select * from books");
    $stmt->execute();
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $data = $stmt->fetchAll();
    $conn = NULL;
    return $data;
}

function init()
{
    $sql = "\n\tCREATE TABLE chat (\n  id mediumint(9) NOT NULL auto_increment,\n  time timestamp(14) NOT NULL,\n  name tinytext NOT NULL,\n  text text NOT NULL,\n  UNIQUE KEY id (id)\n) TYPE=MyISAM\n\t";
    $conn = getDBConnection();
    $results = mysql_query($sql, $conn);
    if (!$results || empty($results)) {
        echo 'There was an error creating the entry';
        end;
    }
}

function deleteEntries($id)
{
    $sql = "DELETE FROM chat WHERE id < " . $id;
    $conn = getDBConnection();
    $results = mysql_query($sql, $conn);
    if (!$results || empty($results)) {
        //echo 'There was an error deletig the entries';
        end;
    }
}

 /**
  * Get the PersonalityTraits for the given PetListing
  * 
  * @param type $petListingId ID of the PetListing
  * @return array of PersonalityTraits for the given PetListing
  */
 function getByPetListingId($petListingId)
 {
     $dbh = getDBConnection();
     $stmt = $dbh->prepare("select PT.* from {$this->model} as PT " . "inner join PetListing_PersonalityTrait on personalityTraitId = id " . "where petListingId = :petListingId");
     $stmt->bindParam(':petListingId', $petListingId);
     $stmt->execute();
     $stmt->setFetchMode(PDO::FETCH_CLASS, $this->model);
     $instances = $stmt->fetchAll();
     return $instances;
 }

function runMatching($contacts)
{
    // Read data from jva_contacts table and store it in a object
    $jva_data = array();
    $jvaQuery = "SELECT * FROM jva_contacts";
    $DBResource = getDBConnection();
    $jva_resultSet = $resultSet = execSQL($DBResource, $jvaQuery);
    if (mysqli_num_rows($jva_resultSet) > 0) {
        while ($jva_r = mysqli_fetch_row($jva_resultSet)) {
            $jva_row = new TempContact();
            $jva_row->jva_id = $jva_r[0];
            $jva_row->jva_first_name = $jva_r[1];
            $jva_row->jva_middle_name = $jva_r[2];
            $jva_row->jva_last_name = $jva_r[3];
            $jva_row->jva_salutation = $jva_r[4];
            $jva_row->jva_phone_no = $jva_r[5];
            $jva_row->jva_fax_no = $jva_r[6];
            $jva_row->jva_country = $jva_r[7];
            $jva_row->jva_zipcode = $jva_r[8];
            $jva_row->jva_email = $jva_r[9];
            array_push($jva_data, $jva_row);
        }
        closeDBConnection($DBResource);
        // To compare each record in jva_contacts with all records in the file
        foreach ($jva_data as $jva_row) {
            foreach ($contacts as $contacts_row) {
                $c_last = $contacts_row->con_last_name;
                $j_last = $jva_row->jva_last_name;
                $DBResource = getDBConnection();
                if ($jva_row->jva_last_name == $contacts_row->con_last_name && $jva_row->jva_country == $contacts_row->con_country && $jva_row->jva_email == $contacts_row->con_email) {
                    $nQuery = "INSERT INTO notifications(src_id,src_con_id,jva_id,match_case,pending_notification) values('" . $contacts_row->src_id . "','" . $contacts_row->src_con_id . "'," . $jva_row->jva_id . ",'Perfect',1)";
                    $resultSet = execSQL($DBResource, $nQuery);
                } else {
                    if ($jva_row->jva_country == $contacts_row->con_country && $jva_row->jva_email == $contacts_row->con_email) {
                        if (strpos($j_last, $c_last) !== FALSE || strpos($c_last, $j_last) !== FALSE) {
                            $nQuery = "INSERT INTO notifications(src_id,src_con_id,jva_id,match_case,pending_notification) values('" . $contacts_row->src_id . "','" . $contacts_row->src_con_id . "'," . $jva_row->jva_id . ",'Partial',1)";
                            $resultSet = execSQL($DBResource, $nQuery);
                        }
                    } else {
                        $nQuery = "INSERT INTO notifications(src_id,src_con_id,jva_id,match_case,pending_notification) values('" . $contacts_row->src_id . "','" . $contacts_row->src_con_id . "',0,'New',1)";
                        $resultSet = execSQL($DBResource, $nQuery);
                    }
                }
                closeDBConnection($DBResource);
            }
            // end of inner for each loop
        }
        // end of outer for each loop
    } else {
        foreach ($contacts as $contacts_row) {
            $nQuery = "INSERT INTO notifications(src_id,src_con_id,jva_id,match_case,pending_notification) values('" . $contacts_row->src_id . "','" . $contacts_row->src_con_id . "',0,'New',1)";
            execSQL($DBResource, $nQuery);
        }
    }
}

 public function __construct()
 {
     require_once __DIR__ . '/db_config.php';
     $this->host = HOST;
     $this->dbname = DB_NAME;
     $this->username = DB_USER;
     $this->password = DB_PASSWORD;
     //$this->log = new Log();
     $this->db = getDBConnection();
     return $this->db;
 }

/**
 * Updates the positions / order of items in the queue.
 * The parameter needs to be an array in a two-dimensional format:
 * [[VIDEO_ID,POSITION],[VIDEO_ID,POSITION],...] and so on and sent to the script in a JSON format.
 */
function change_queue_positions($positions)
{
    $conn = getDBConnection();
    for ($i = 0; $i < sizeof($positions); $i++) {
        $position = $positions[$i][1];
        $mediaId = $positions[$i][0];
        $conn->query("UPDATE queue SET position={$position} WHERE (media_id={$mediaId} AND status!=\"" . STATUS_BEING_PROCESSED . "\")");
    }
    echo json_encode(array("status" => "ok"));
    $conn->close();
}