function insert_transaction($date, $recipient, $category, $amount, $description)
{
$link = open_database_connection();
$date = strtotime($date);
$date = mysql_real_escape_string(date('Y-m-d', $date));
$recipientName = mysql_real_escape_string(trim($recipient));
$categoryName = $category;
$categoryName = mysql_real_escape_string(trim($category));
$description = mysql_real_escape_string(trim($description));
$amount = mysql_real_escape_string(trim($amount));
$checkRecipientQuery = "select id from Recipients where upper(name) like upper('{$recipientName}');";
$result = mysql_query($checkRecipientQuery, $link);
if (mysql_num_rows($result) == 0) {
$createRecipientQuery = "insert into Recipients (name) values ('{$recipientName}');";
mysql_query($createRecipientQuery, $link);
$result = mysql_query($checkRecipientQuery, $link);
}
$row = mysql_fetch_assoc($result);
$recipientID = $row['id'];
$checkCategoryQuery = "select id from Categories where upper(name) like upper('{$categoryName}');";
$result = mysql_query($checkCategoryQuery, $link);
if (mysql_num_rows($result) == 0) {
$createCategoryQuery = "insert into Categories (name) values ('{$categoryName}');";
mysql_query($createCategoryQuery, $link);
$result = mysql_query($checkCategoryQuery, $link);
}
$row = mysql_fetch_assoc($result);
$categoryID = $row['id'];
$createTransactionQuery = "insert into Transactions (date, amount, description, recipientID, categoryID) " . "values ('{$date}', '{$amount}', '{$description}', '{$recipientID}', '{$categoryID}');";
mysql_query($createTransactionQuery, $link);
close_database_connection($link);
}
function remove_post($id)
{
$link = open_database_connection();
$sql = "DELETE FROM Post WHERE id = {$id}";
$result = mysql_query($sql, $link);
close_database_connection($link);
return $post;
}
function delete_post($id)
{
$sql = "DELETE FROM post WHERE id='{$id}'";
$link = open_database_connection();
mysql_query($sql, $link);
close_database_connection($link);
return;
}
function delete_post($id)
{
$link = open_database_connection();
$sql = "DELETE FROM `post` WHERE id='{$id}' ";
mysql_query($sql, $link) or die("Запрос не выполненн" . mysql_error());
close_database_connection($link);
return;
}
function get_post($id)
{
$link = open_database_connection();
$sql = "SELECT * FROM post WHERE id='{$id}'";
$result = mysql_query($sql, $link);
$post = mysql_fetch_assoc($result);
close_database_connection($link);
return $post;
}
function get_thing_by_key($thingKey)
{
$dbconn = open_database_connection();
$thingKey = intval($thingKey);
$result = $dbconn->query("SELECT * FROM things WHERE thingKey = " . $thingKey);
$row = $result->fetch_assoc();
close_database_connection($dbconn);
return $row;
}
function get_post_by_id($id)
{
$link = open_database_connection();
$id = mysql_real_escape_string($id);
$query = 'SELECT date, title, body FROM post WHERE id = ' . $id;
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
close_database_connection($link);
return $row;
}
/**
* @param $id
*
* @return array|null
*/
function get_post_by_id($id)
{
$link = open_database_connection();
$id = intval($id);
$query = 'SELECT id, title FROM post WHERE id = ' . $id;
$result = mysqli_query($link, $query);
$row = mysqli_fetch_assoc($result);
close_database_connection($link);
return $row;
}
function edit_data($id)
{
$autor = $_POST['autor'];
$date = date("Y-m-d H:i:s");
$title = $_POST['title'];
$edit_content = $_POST['content'];
$link = open_database_connection();
$sql = "UPDATE `post` SET `autor` = '{$autor}', `date` = '{$date}', `title` = '{$title}', `content` = '{$edit_content}' WHERE `id` = '{$id}';";
$add = mysql_query($sql, $link);
close_database_connection($link);
}
function update_row()
{
if (!($request = getRequest())) {
return false;
}
$sql = "UPDATE `pages` SET `date`='" . $request['date'] . "', \n\t\t\t\t\t\t`title`='" . $request['title'] . "', \n\t\t\t\t\t\t`author`='" . $request['author'] . "', \n\t\t\t\t\t\t`content`='" . $request['content'] . "' \n\t\t\t\t\t\t WHERE id=" . $request['id'];
$link = open_database_connection();
$result = mysql_query($sql, $link) or die("error! " . $sql . " == " . mysql_error());
close_database_connection($link);
return $result;
}
function get_all_rows()
{
$link = open_database_connection();
$result = mysql_query('SELECT * FROM pages', $link);
$rows = array();
while ($row = mysql_fetch_assoc($result)) {
$rows[] = $row;
}
close_database_connection($link);
return $rows;
}
function get_all_posts()
{
$link = open_database_connection();
$result = mysql_query('SELECT id, title FROM post', $link);
$posts = array();
while ($row = mysql_fetch_assoc($result)) {
$posts[] = $row;
}
close_database_connection($link);
return $posts;
}
function edit_data($id)
{
$autor = $_POST['autor'];
$content = $_POST['content'];
$title = $_POST['title'];
$link = open_database_connection();
$sql = "UPDATE `post` SET `autor` = '{$autor}', `title` = '{$title}', `content` = '{$content}' WHERE `id` = '{$id}';";
$edit = mysql_query($sql, $link);
close_database_connection($link);
header('location:admin');
}
function add_post()
{
$author = $_POST['add_author'];
$time = $_POST['add_time'];
$title = $_POST['add_title'];
$content = $_POST['add_content'];
$link = open_database_connection();
$sql = "INSERT INTO `post` (`id`, `author`, `time`, `title`, `content`) \n\t\t\tVALUES (NULL, '{$author}', '{$time}', '{$title}', '{$content}');";
$result = mysql_query($sql, $link);
close_database_connection($link);
}
function getAllFromById($tableName, $id)
{
$link = open_database_connection();
$result = mysql_query('SELECT * FROM $tableName WHERE id = $id', $link);
$posts = array();
while ($row = mysql_fetch_assoc($result)) {
$posts[] = $row;
}
close_database_connection($link);
return $posts;
}
function get_row($id)
{
$link = open_database_connection();
$sql = "SELECT * FROM `idpages` WHERE `id`={$id}";
$result = mysql_query($sql, $link);
$rows = array();
while ($row = mysql_fetch_array($result)) {
$rows[] = $row;
}
close_database_connection($link);
return $rows;
}
function add_row()
{
if (empty($_REQUEST['add_title']) && !empty($_REQUEST['add_content']) && !empty($_REQUEST['add_autor'])) {
return;
//'koik otsast peale'
}
$title = $_REQUEST['add_title'];
$autor = $_REQUEST['add_title'];
$content = $_REQUEST['add_title'];
$date = new date();
$link = $_REQUEST['add_title'];
$link = open_database_connection();
$sql = "INSERT INTO pages ('date',title,content,autor) \n\t\tVALUES('{$date}','{$title}','{$content}','{$autor}')";
close_database_connection($link);
}
/**
* return an array containing all menu categories after connecting to the database
*
* @return array
*/
public function getAllMenuCategories()
{
$connection = open_database_connection();
$query = "SELECT * FROM `categories`";
$results = mysqli_query($connection, $query);
$this->categories = array();
if (mysqli_num_rows($results) > 0) {
while ($rows = $results->fetch_assoc()) {
$this->categories[$rows['CategoryID']] = new MenuCategory($rows['CategoryID']);
$this->categories[$rows['CategoryID']]->setMenuCategoryTitle($rows['CategoryName']);
$this->categories[$rows['CategoryID']]->setMenuCategoryImage($rows['CategoryImage']);
$this->categories[$rows['CategoryID']]->setMenuCategorySummary($rows['CategorySummary']);
$this->addMenuCategory($this->categories[$rows['CategoryID']]);
}
}
close_database_connection($connection);
return $this->categories;
}
function insertNewRecord($playerName, $Score)
{
$connection = open_database_connection();
$query = "INSERT INTO highscores(Name, Score) VALUES ('{$playerName}', {$Score})";
print '<br>';
print $query;
mysqli_query($connection, $query);
close_database_connection($connection);
}
/**
* test for sorting products from the search
* NOTE: By changing the the products "Cappuccino", "Lemon Tea", or
* "Mineral Water" in the database or adding any products with
* similar names this test will no longer work unless $expectedResult
* is changed based on AdminSearch::sortProductBySearch's new output
*/
public function testSortProductBySearch()
{
// Arrange
$adminSearch = new AdminSearch();
$connection = open_database_connection();
$expectedResult = '<h3>Search results for Cappuccino</h3><form id="productResult13" action= "/adminEditProduct" method="get">
<p><span class="search" onclick="document.getElementById(\'productResult13\').submit();">Cappuccino</span> - This creamy, delicious, rich flavoured coffee specialty is made from 1/3 Espresso, 1/3 steamed milk and 1/3 foamed milk and garnished with your choice of Cinnamon or powder chocolate. We use high altitude(above 3000 ft) grown Arabica coffee beans.</p><p>Calories: 115</p><p>Price: 3.45</p><input type="hidden" name="product_ID" value="13" /><input type="hidden" name="product_name" value="Cappuccino" /><input type="hidden" name="product_image_URL" value="cappuccino.png" /><input type="hidden" name="product_description" value="This creamy, delicious, rich flavoured coffee specialty is made from 1/3 Espresso, 1/3 steamed milk and 1/3 foamed milk and garnished with your choice of Cinnamon or powder chocolate. We use high altitude(above 3000 ft) grown Arabica coffee beans." /><input type="hidden" name="product_calories" value="115" /><input type="hidden" name="product_allergy_info" value="no allergens are present on this drink product but consideration for those with low toleration to milk products should be given." /><input type="hidden" name="product_price" value="3.45" /><input type="hidden" name="category_ID" value="3" /><input type="hidden" name="category_name" value="Drinks" /><input type="hidden" name="category_summary" value="Quench your thirst with our selection of freshly made drinks or warm yourself up with a selection of hot drinks that we offer!" /><input type="hidden" name="productImageURL1" value="" /><input type="hidden" name="productImageURL2" value="cappuccino.png" /></form><h3>Search results for Water</h3><form id="productResult10" action= "/adminEditProduct" method="get">
<p><span class="search" onclick="document.getElementById(\'productResult10\').submit();">Lemon Tea</span> - KBK lemon tea is inspired from the Indian cuisine and it is a very refreshing drink which could be served at any time of the day indulging yourself or when meeting with your next door neighbor. It is made of aromatic Indian black tea, lemon, and spring water. Best served with a slice of lemon and some ice!</p><p>Calories: 40</p><p>Price: 2.95</p><input type="hidden" name="product_ID" value="10" /><input type="hidden" name="product_name" value="Lemon Tea" /><input type="hidden" name="product_image_URL" value="lemon_tea.png" /><input type="hidden" name="product_description" value="KBK lemon tea is inspired from the Indian cuisine and it is a very refreshing drink which could be served at any time of the day indulging yourself or when meeting with your next door neighbor. It is made of aromatic Indian black tea, lemon, and spring water. Best served with a slice of lemon and some ice!" /><input type="hidden" name="product_calories" value="40" /><input type="hidden" name="product_allergy_info" value="no allergens are present on this product." /><input type="hidden" name="product_price" value="2.95" /><input type="hidden" name="category_ID" value="3" /><input type="hidden" name="category_name" value="Drinks" /><input type="hidden" name="category_summary" value="Quench your thirst with our selection of freshly made drinks or warm yourself up with a selection of hot drinks that we offer!" /><input type="hidden" name="productImageURL1" value="" /><input type="hidden" name="productImageURL2" value="lemon_tea.png" /></form><form id="productResult14" action= "/adminEditProduct" method="get">
<p><span class="search" onclick="document.getElementById(\'productResult14\').submit();">Mineral Water</span> - This mineral water is bottled fresh at its source in the French Alps. You are what you drink, so hydration is very important, so do not leave out this vital supplement in any meal.</p><p>Calories: 10</p><p>Price: 1.49</p><input type="hidden" name="product_ID" value="14" /><input type="hidden" name="product_name" value="Mineral Water" /><input type="hidden" name="product_image_URL" value="mineral_water.png" /><input type="hidden" name="product_description" value="This mineral water is bottled fresh at its source in the French Alps. You are what you drink, so hydration is very important, so do not leave out this vital supplement in any meal." /><input type="hidden" name="product_calories" value="10" /><input type="hidden" name="product_allergy_info" value="None" /><input type="hidden" name="product_price" value="1.49" /><input type="hidden" name="category_ID" value="3" /><input type="hidden" name="category_name" value="Drinks" /><input type="hidden" name="category_summary" value="Quench your thirst with our selection of freshly made drinks or warm yourself up with a selection of hot drinks that we offer!" /><input type="hidden" name="productImageURL1" value="" /><input type="hidden" name="productImageURL2" value="mineral_water.png" /></form>';
// Assert
$result = $adminSearch->sortProductBySearch($connection, "ASC", "ProductName", "Cappuccino Water");
close_database_connection($connection);
// Act
$this->assertEquals($result, $expectedResult);
}
/**
* Function that validates if username and password are valid and accepted
*
* @param $username - string
* @param $password - string
* @return bool
*/
function isValidUsernamePassword($username, $password)
{
$connection = open_database_connection();
// run query
$query = "SELECT * FROM users WHERE username='******' AND password='******'";
if ($result = mysqli_query($connection, $query)) {
$numRows = $result->num_rows;
}
//closing database connection
close_database_connection($connection);
//return if we found a username password or not:
if ($numRows > 0) {
return true;
} else {
return false;
}
}
/**
* Action for displaying all the information about a product to the user
*
* Action for route: /product
*
* @param Request $request
* @param Application $app
* @return mixed
*/
public function productAction(Request $request, Application $app)
{
//check if username is stored in session
$username = getAuthenticatedUsername($app);
$params = $request->query->all();
$productName = $_GET['product_name'];
$error = "";
$connection = open_database_connection();
if (!$connection) {
$_SESSION['errorCategory'] = 'Database';
$_SESSION['errorMessage'] = 'DB connection failed: ' . mysqli_connect_error();
header('Location: /error');
}
$query = "SELECT * FROM `products` WHERE ProductName = '" . $productName . "'";
$resultSet = mysqli_query($connection, $query);
if (mysqli_num_rows($resultSet) > 0) {
$rows = mysqli_fetch_assoc($resultSet);
$productName = $rows['ProductName'];
$productImageURL = $rows['ProductImageURL'];
$productDescription = $rows['ProductDescription'];
$productCalories = $rows['ProductCalories'];
$productAllergyInfo = $rows['ProductAllergyInfo'];
$productPrice = sprintf("%01.2f", $rows['ProductPrice']);
} else {
$error = "An internal server error occurred. Please try again later.";
}
close_database_connection($connection);
// build args array
// -------------
$argsArray = array('username' => $username, 'title' => $productName, 'productName' => $productName, 'productImageURL' => $productImageURL, 'productDescription' => $productDescription, 'productCalories' => $productCalories, 'productAllergyInfo' => $productAllergyInfo, 'productPrice' => $productPrice, 'errorMessage' => $error);
// render template
// --------------
$templateName = 'product';
return $app['twig']->render($templateName . '.html.twig', $argsArray);
}
$row = mysql_fetch_assoc($result);
$recipientID = $row['id'];
$checkCategoryQuery = "select id from Categories where upper(name) like upper('{$categoryName}');";
$result = mysql_query($checkCategoryQuery, $link);
if (mysql_num_rows($result) == 0) {
$createCategoryQuery = "insert into Categories (name) values ('{$categoryName}');";
mysql_query($createCategoryQuery, $link);
$result = mysql_query($checkCategoryQuery, $link);
}
$row = mysql_fetch_assoc($result);
$categoryID = $row['id'];
$createTransactionQuery = "insert into Transactions (date, amount, description, recipientID, categoryID) " . "values ('{$date}', '{$amount}', '{$description}', '{$recipientID}', '{$categoryID}');";
mysql_query($createTransactionQuery, $link);
echo "SQL Errors: " . mysql_error($link);
// TODO: error checking, validation, etc.
close_database_connection($link);
?>
Transaction added!
<?php
}
?>
<script>
$(function() {
$("#date").datepicker();
});
</script>
<form name="newTransactionForm" method="post" action="">
Date <input id="date" name="date" type="text" /><br />
Amount <input name="amount" type="text" /><br />
Recipient <input name="recipient" type="text" /><br />
Category <input name="category" type="text" /><br />
function special_queries_l()
{
$conn = open_database_connection();
$query = "SELECT r.name, r.reputation, ra.date_submitted, re.name as resname FROM rater r, rating ra\n JOIN restaurant re ON re.restaurantid = ra.restaurantid\n WHERE r.userid=ra.userid AND ra.food='5' OR ra.mood='5'\n GROUP BY r.name, r.reputation, ra.date_submitted, resname";
$result = pg_query($conn, $query);
$answer_l = array();
while ($row = pg_fetch_assoc($result)) {
$answer_l[] = $row;
}
close_database_connection($conn);
return $answer_l;
}
function edit_post($id)
{
$avtor = $_REQUEST['edit_avtor'];
$data = date("Y-m-d H:i:s");
$content = $_REQUEST['edit_content'];
$title = $_REQUEST['edit_title'];
$link = open_database_connection();
/**
*klu4 raboty s BD
*/
$sql = "UPDATE FROM post (avtor, date, title, content) \n\t\tVALUES ('{$avtor}','{$data}','{$title}','{$content}') WHERE id='{$id}'";
/**
*vse zapisi v bd
*/
$result = mysql_query($sql, $link);
/**
*pomeshajem vyborku v bd
*/
$post = mysql_fetch_assoc($result);
close_database_connection($link);
//zakon4ili rab s bd
return $post;
//sdali klu4
}
function get_position_id_query($username)
{
$link = open_database_connection();
$position_id_query = sprintf("SELECT positionID FROM staff WHERE username = '******' AND start_date < NOW() AND end_date > NOW()", $username);
$position_id_result = mysql_query($position_id_query) or die(mysql_error($link));
close_database_connection($link);
return $position_id_result;
}
/**
* Action for deleting a product in the database.
*
* Action for route: /adminDeleteProduct
*
* @param Request $request
* @param Application $app
* @return \Symfony\Component\HttpFoundation\RedirectResponse
*/
public function deleteProductAction(Request $request, Application $app)
{
// test if 'username' stored in session ...
$username = getAuthenticatedUserName($app);
// check we are authenticated --------
$isAuthenticated = null != $username;
if (!$isAuthenticated) {
// not authenticated, so redirect to LOGIN page
return $app->redirect('/login');
}
$connection = open_database_connection();
$productID = $_GET['product_ID'];
$categoryID = $_GET['category_ID'];
$categoryName = $_GET['category_name'];
$categorySummary = $_GET['category_summary'];
$changeProductObject = new ChangeProduct();
$resultMessage = $changeProductObject->deleteProduct($connection, $productID);
close_database_connection($connection);
$categoryName = str_replace(" ", "+", $categoryName);
$categorySummary = str_replace(" ", "+", $categorySummary);
if ($resultMessage == "Success!") {
return $app->redirect("/adminChangeProductSelectProduct?categoryID=" . $categoryID . "&categoryName=" . $categoryName . "&categorySummary=" . $categorySummary);
} else {
return $app->redirect("/adminChangeProductSelectProduct?categoryID=" . $categoryID . "&categoryName=" . $categoryName . "&categorySummary=" . $categorySummary . "&resultMessage=" . $resultMessage);
}
}
function get_messages()
{
$link = open_database_connection();
$sql = 'SELECT id, name, subject, email, message FROM messages';
$query = $link->prepare($sql);
$query->execute();
$messages = array();
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
$messages[] = $row;
}
close_database_connection($link);
return $messages;
}
function update_post($id)
{
if (empty($_REQUEST['update_autor']) || empty($_REQUEST['update_date']) || empty($_REQUEST['update_title']) || empty($_REQUEST['update_content'])) {
echo "Пропущена запись!";
return false;
}
$id = $_REQUEST['update_id'];
$update_autor = $_REQUEST['update_autor'];
$update_date = date("Y-m-d H:i:s");
$update_title = $_REQUEST['update_title'];
$update_content = $_REQUEST['update_content'];
$sql = "UPDATE post SET `date`='{$update_date}', autor='{$update_autor}',title='{$update_title}',\n\tcontent='{$update_content}' WHERE id='{$id}'";
echo "sql=" . $sql;
/*$sql="UPDATE post SET(`date`,`autor`,`title`,`content`)
VALUES('$add_date','$add_autor','$add_title','$add_content') WHERE id='$id'";*/
$link = open_database_connection();
mysql_query($sql, $link) or die("Запрос не выполнен" . mysql_error());
close_database_connection($link);
echo "Обновлено!";
return true;
}
/**
* @return array
*/
public function getImageUrlArray()
{
// make a new connection to the database
$connection = open_database_connection();
// make a new query for the CategoryID and ProductImageURL from all rows from `products`
$query = "SELECT CategoryID, ProductImageURL FROM `products` ORDER BY CategoryID ASC";
// get the results from the query
$results = mysqli_query($connection, $query);
// declare imageData to be an empty array
// this variable will hold all the data in php array format before we json_encode() it
$imageData = array();
// declaring and defining a variable to represent which category(the CategoryID)
// we're processing in the loop below
$categoryToProcess = 0;
// declaring and defining a variable to represent which index in its respective
// category array in imageData we're processing
$categoryIndexToProcess = 0;
// check to make sure there are results from the mysql query we made earlier
if (mysqli_num_rows($results) > 0) {
// as long as we can retrieve another row from the database, add the row's ProductImageURL to imageData
while ($rows = $results->fetch_assoc()) {
// set the category we're processing to be the CategoryID of the row we retrieved
$categoryToProcess = $rows['CategoryID'];
if (array_key_exists($categoryToProcess, $imageData)) {
// If an array key exists in the object for the category we're processing,
// then there is an array dedicated to storing data for that category, so
// insert the image url in the category's array, in the next index
$imageData[$categoryToProcess][$categoryIndexToProcess] = "/images/" . $rows['ProductImageURL'];
} else {
// If there isn't an array key for the category we're processing,
// then there isn't an array for this category, so create a new array,
$imageData[$categoryToProcess] = array();
// reset the index of the item we're processing to 1,
$categoryIndexToProcess = 1;
// and insert the image's url into that index
$imageData[$categoryToProcess][$categoryIndexToProcess] = "/images/" . $rows['ProductImageURL'];
}
// update the amount of items in the array of the category we're processing
// to be the last index we processed
$imageData[$categoryToProcess][0] = $categoryIndexToProcess;
// update the amount of categories in the object to be the category we're processing
// for the same reason as above
$imageData[0] = $rows['CategoryID'];
// process the next index
$categoryIndexToProcess++;
}
}
// close the database connection
close_database_connection($connection);
// return the imageData
return $imageData;
}