/*
* This file is part of anycook. The new internet cookbook
* Copyright (C) 2014 Jan Graßegger
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see [http://www.gnu.org/licenses/].
*/
package de.anycook.db.mysql;
import de.anycook.recipe.Recipe;
import de.anycook.user.User;
import org.apache.commons.math3.random.RandomDataGenerator;
import java.sql.*;
import java.util.*;
import java.util.Date;
public class DBGetRecipe extends DBRecipe {
public DBGetRecipe() throws SQLException {
super();
}
public Recipe get(String name) throws SQLException, RecipeNotFoundException {
return get(name, -1);
}
/**
* Gibt alle Daten eines Gerichts zurueck. Sucht das Gericht per Gerichtenamen und schreibt den Namen, die Wertung,
* die Beschreibung, die Dauer, den Schwierigkeitsgrad, die Kalorien, den Bildlink und
* ob es vegetarisch ist in eine {@link java.util.Map} und gibt diese zurueck.
*
* @param name {@link String} mit dem Gerichtenamen
* @return {@link java.util.Map} mit den Daten des Gerichts.
*/
public Recipe get(String name, int loginId) throws RecipeNotFoundException, SQLException {
PreparedStatement preparedStatement =
connection.prepareStatement("SELECT versions.id AS id, gerichte.name AS name, beschreibung, " +
"IFNULL(versions.imagename, CONCAT('category/', kategorien.image)) AS image, " +
"gerichte.eingefuegt AS created," +
"min, std, skill, kalorien, personen, kategorien_name, active_id, users_id, nickname, users.image, " +
"viewed, last_change, " +
"(SELECT COUNT(users_id) FROM schmeckt WHERE schmeckt.gerichte_name = ? " +
"AND schmeckt.users_id = ?) AS tastes FROM gerichte " +
"INNER JOIN versions ON IF(active_id > 0, gerichte.name = gerichte_name " +
"AND active_id = versions.id, gerichte.name = gerichte_name) " +
"INNER JOIN users ON users_id = users.id " +
"INNER JOIN kategorien ON kategorien_name = kategorien.name " +
"WHERE gerichte.name = ?;");
preparedStatement.setString(1, name);
preparedStatement.setInt(2, loginId);
preparedStatement.setString(3, name);
ResultSet data = preparedStatement.executeQuery();
if (!data.next()) throw new RecipeNotFoundException(name);
return getRecipe(data);
}
public List<Recipe> getAllRecipes(int loginId) throws SQLException {
PreparedStatement preparedStatement =
connection.prepareStatement("SELECT versions.id AS id, gerichte.name AS name, beschreibung, " +
"IFNULL(versions.imagename, " +
"CONCAT('category/', kategorien.image)) AS image, gerichte.eingefuegt AS created, " +
"min, std, skill, kalorien, personen, kategorien_name, " +
"active_id, users_id, users.image, nickname, viewed, last_change, " +
"(SELECT COUNT(users_id) FROM schmeckt " +
"WHERE schmeckt.gerichte_name = gerichte.name AND schmeckt.users_id = ?) AS tastes " +
"FROM gerichte " +
"INNER JOIN versions ON IF(active_id > 0, gerichte.name = gerichte_name " +
"AND active_id = versions.id, gerichte.name = gerichte_name) " +
"INNER JOIN users ON users_id = users.id " +
"INNER JOIN kategorien ON kategorien_name = kategorien.name " +
"GROUP BY name;");
preparedStatement.setInt(1, loginId);
ResultSet data = preparedStatement.executeQuery();
return getRecipes(data);
}
public List<Recipe> getAllRecipes(int loginId, Date lastModified) throws SQLException {
PreparedStatement preparedStatement =
connection.prepareStatement("SELECT versions.id AS id, gerichte.name AS name, beschreibung, " +
"IFNULL(versions.imagename, " +
"CONCAT('category/', kategorien.image)) AS image, gerichte.eingefuegt AS created, " +
"min, std, skill, kalorien, personen, kategorien_name, " +
"active_id, users_id, users.image, nickname, viewed, last_change, " +
"(SELECT COUNT(users_id) FROM schmeckt " +
"WHERE schmeckt.gerichte_name = gerichte.name AND schmeckt.users_id = ?) AS tastes " +
"FROM gerichte " +
"INNER JOIN versions ON IF(active_id > 0, gerichte.name = gerichte_name " +
"AND active_id = versions.id, gerichte.name = gerichte_name) " +
"INNER JOIN users ON users_id = users.id " +
"INNER JOIN kategorien ON kategorien_name = kategorien.name " +
"WHERE last_change > ? " +
"GROUP BY name;");
preparedStatement.setInt(1, loginId);
preparedStatement.setTimestamp(2, new Timestamp(lastModified.getTime()));
ResultSet data = preparedStatement.executeQuery();
return getRecipes(data);
}
public List<Recipe> getVersions(String recipeName, int loginId) throws SQLException {
PreparedStatement preparedStatement =
connection.prepareStatement("SELECT versions.id AS id, beschreibung, " +
"IFNULL(versions.imagename, CONCAT('category/', kategorien.image)) AS image, " +
"gerichte.eingefuegt AS created, min, std, skill, kalorien, gerichte.name, personen, kategorien_name, " +
"active_id, users_id, nickname, users.image, viewed, versions.eingefuegt AS last_change, " +
"(SELECT COUNT(users_id) FROM schmeckt WHERE schmeckt.gerichte_name = ? " +
"AND schmeckt.users_id = ?) AS tastes " +
"FROM gerichte " +
"INNER JOIN versions ON gerichte.name = gerichte_name " +
"INNER JOIN users ON users_id = users.id " +
"INNER JOIN kategorien ON kategorien_name = kategorien.name " +
"WHERE gerichte.name = ?;");
preparedStatement.setString(1, recipeName);
preparedStatement.setInt(2, loginId);
preparedStatement.setString(3, recipeName);
try(ResultSet data = preparedStatement.executeQuery()){
return getRecipes(data);
}
}
public Recipe getVersionData(String recipeName, int versionId, int loginId) throws RecipeNotFoundException, SQLException {
PreparedStatement preparedStatement =
connection.prepareStatement("SELECT versions.id AS id, beschreibung, " +
"IFNULL(versions.imagename, CONCAT('category/', kategorien.image)) AS image, " +
"gerichte.eingefuegt AS created, versions.eingefuegt AS last_change, min, std, skill, kalorien, " +
"gerichte.name, personen, kategorien_name, active_id, users_id, nickname, users.image, viewed, " +
"(SELECT IF(COUNT(users_id) = 1, TRUE, FALSE) FROM schmeckt " +
"WHERE schmeckt.gerichte_name = gerichte.name AND schmeckt.users_id = ?) AS tastes " +
"FROM gerichte " +
"INNER JOIN versions ON gerichte.name = gerichte_name " +
"INNER JOIN users ON users_id = users.id " +
"INNER JOIN kategorien ON kategorien_name = kategorien.name " +
"WHERE gerichte.name = ? AND versions.id = ?;");
preparedStatement.setInt(1, loginId);
preparedStatement.setString(2, recipeName);
preparedStatement.setInt(3, versionId);
ResultSet data = preparedStatement.executeQuery();
if (!data.next()) throw new RecipeNotFoundException(recipeName);
return getRecipe(data);
}
public List<String> getAllRecipeNames() throws SQLException {
List<String> result = new LinkedList<>();
PreparedStatement pStatement = connection.prepareStatement("SELECT name FROM gerichte ORDER BY name");
ResultSet data = pStatement.executeQuery();
while (data.next()) {
result.add(data.getString("name"));
}
return result;
}
public List<String> getAllActiveRecipeNamesWithImage() throws SQLException {
List<String> result = new ArrayList<>();
PreparedStatement pStatement = connection.prepareStatement("SELECT name FROM gerichte " +
"INNER JOIN versions ON name = gerichte_name AND active_id = id " +
"WHERE imagename IS NOT NULL AND NOT (imagename = \"\") " +
"AND name NOT IN (SELECT tagesrezepte.gerichte_name FROM tagesrezepte WHERE DATE > DATE_SUB(curdate(),INTERVAL 1 MONTH)) " +
"GROUP BY name");
ResultSet data = pStatement.executeQuery();
while (data.next()) {
result.add(data.getString("name"));
}
return result;
}
/**
* Gibt einen zufaelligen Gerichtenamen zurueck. Waehlt aus dem Gesamtbestand an Gerichten mithilfe von {@link java.util.Random} eines aus.
*
* @return {@link String} mit dem zufaelligen Gerichtenamen
*/
public String randomRecipe() throws SQLException {
RandomDataGenerator generator = new RandomDataGenerator();
List<String> recipes = getAllActiveRecipeNamesWithImage();
if (recipes.size() == 0) return null;
return recipes.get(generator.nextInt(1, recipes.size()));
}
/**
* Gibt alle Tags eines Gericht als {@link java.util.List} zurueck.
*
* @param recipeName Name des Gerichts
* @return {@link java.util.List} mit Tags als String
*/
public List<String> getTags(String recipeName) throws SQLException {
List<String> tags = new LinkedList<>();
PreparedStatement pStatement = connection.prepareStatement("SELECT tags_name FROM gerichte_has_tags WHERE gerichte_name = ? AND active = 1");
pStatement.setString(1, recipeName);
ResultSet data = pStatement.executeQuery();
while (data.next())
tags.add(data.getString(1));
return tags;
}
public List<String> getAllActiveRecipeNames() throws SQLException {
List<String> result = new LinkedList<>();
PreparedStatement pStatement = connection.prepareStatement("SELECT name FROM gerichte WHERE active_id > -1 ORDER BY name");
ResultSet data = pStatement.executeQuery();
while (data.next()) {
result.add(data.getString("name"));
}
return result;
}
public int getActiveIdfromRecipe(String recipe) throws SQLException, RecipeNotFoundException {
PreparedStatement pStatement = connection.prepareStatement("SELECT active_id FROM gerichte WHERE name = ?");
pStatement.setString(1, recipe);
ResultSet data = pStatement.executeQuery();
if (data.next())
return data.getInt("active_id");
throw new RecipeNotFoundException(recipe);
}
public int getRecipeNumber() throws SQLException {
PreparedStatement pStatement = connection.prepareStatement("SELECT COUNT(name) AS recipenumber FROM gerichte ORDER BY name");
ResultSet data = pStatement.executeQuery();
if (data.next())
return data.getInt("recipenumber");
return 0;
}
public String getRecipeOfTheDay() throws SQLException, RecipeNotFoundException {
PreparedStatement pStatement = connection.prepareStatement("SELECT gerichte_name FROM tagesrezepte WHERE date = CURDATE()");
try (ResultSet data = pStatement.executeQuery()) {
if (data.next())
return data.getString("gerichte_name");
throw new RecipeNotFoundException("no recipes found");
}
}
public String createNewRecipeOfTheDay() throws RecipeNotFoundException, SQLException {
String recipeOfTheDay = randomRecipe();
if (recipeOfTheDay == null) throw new RecipeNotFoundException("no random recipe found");
PreparedStatement pStatement = connection.prepareStatement("INSERT INTO tagesrezepte (date, gerichte_name) VALUES (CURDATE(),?)");
pStatement.setString(1, recipeOfTheDay);
pStatement.executeUpdate();
return recipeOfTheDay;
}
public Set<Integer> getUsersFromGericht(String recipeName) throws SQLException {
Set<Integer> users = new HashSet<>();
PreparedStatement pStatement = connection.prepareStatement("SELECT users_id FROM versions WHERE gerichte_name = ? GROUP BY users_id");
pStatement.setString(1, recipeName);
ResultSet data = pStatement.executeQuery();
while (data.next())
users.add(data.getInt("users_id"));
return users;
}
/**
* Ueberprueft, ob eine Zutat noch Rezepte besitzt
*
* @param ingredient
* @return true, wenn zutat noch rezepte besitzt, sonst false
*/
public boolean checkZutatforGerichte(String ingredient) throws SQLException {
PreparedStatement pStatement = connection.prepareStatement("SELECT count(versions_gerichte_name) AS counter FROM versions_has_zutaten WHERE zutaten_name = ? GROUP BY versions_gerichte_name");
pStatement.setString(1, ingredient);
ResultSet data = pStatement.executeQuery();
if (data.next())
return data.getInt("counter") != 0;
return false;
}
/**
* Ueberprueft, ob eine Zutat noch Children besitzt
*
* @param ingredientName
* @return true, wenn zutat noch children besitzt, sonst false
*/
public boolean checkZutatforChildrens(String ingredientName) throws SQLException {
PreparedStatement pStatement = connection.prepareStatement("SELECT * FROM zutaten WHERE parent_zutaten_name = ? GROUP BY name");
pStatement.setString(1, ingredientName);
ResultSet data = pStatement.executeQuery();
if (data.next())
return true;
return false;
}
public Set<String> getRecipesFromUser(int userId) throws SQLException {
Set<String> recipes = new HashSet<>();
PreparedStatement pStatement = connection.prepareStatement("SELECT gerichte_name FROM versions WHERE users_id = ? GROUP BY gerichte_name");
pStatement.setInt(1, userId);
ResultSet data = pStatement.executeQuery();
while (data.next()) {
recipes.add(data.getString("gerichte_name"));
}
return recipes;
}
public int getAuthor(String recipeName, int versionId) throws RecipeNotFoundException, SQLException {
PreparedStatement pStatement = connection.prepareStatement("SELECT users_id FROM versions WHERE gerichte_name = ? AND id = ?");
pStatement.setString(1, recipeName);
pStatement.setInt(2, versionId);
ResultSet data = pStatement.executeQuery();
if (data.next())
return data.getInt("users_id");
throw new RecipeNotFoundException(recipeName, versionId);
}
public List<User> getAuthors(String recipeName) throws SQLException {
List<User> authors = new LinkedList<>();
PreparedStatement pStatement = connection.prepareStatement("SELECT nickname, users_id, users.image FROM versions INNER JOIN users ON users_id = users.id WHERE gerichte_name = ? GROUP BY nickname ORDER BY versions.id DESC ");
pStatement.setString(1, recipeName);
ResultSet data = pStatement.executeQuery();
while (data.next()) {
String username = data.getString("nickname");
int userId = data.getInt("users_id");
String userImage = data.getString("users.image");
authors.add(new User(userId, username, userImage));
}
return authors;
}
public boolean checkUserForGerichte(int userId) throws SQLException {
return getRecipesFromUser(userId).size() > 0;
}
public List<String> getAllTags() throws SQLException {
List<String> tags = new LinkedList<>();
PreparedStatement pStatement = connection.prepareStatement("SELECT name FROM tags");
ResultSet data = pStatement.executeQuery();
while (data.next())
tags.add(data.getString("name"));
return tags;
}
public String getImageName(String recipe) throws SQLException {
CallableStatement call = connection.prepareCall("{call recipe_image(?)}");
call.setString(1, recipe);
ResultSet data = call.executeQuery();
if (data.next()) {
String imageName = data.getString("imagename");
String categoryImage = "category/" + data.getString("image");
return imageName == null || imageName.equals("")
? categoryImage : imageName;
}
return "nopicture.png";
}
public int getTotalIngredients() throws SQLException {
PreparedStatement pStatement =
connection.prepareStatement("SELECT count(*) AS counter FROM zutaten");
ResultSet data = pStatement.executeQuery();
if (data.next()) {
return data.getInt("counter");
}
return -1;
}
public int getTotalRecipes() throws SQLException {
PreparedStatement pStatement =
connection.prepareStatement("SELECT count(*) AS counter FROM gerichte");
ResultSet data = pStatement.executeQuery();
if (data.next()) {
return data.getInt("counter");
}
return 0;
}
public List<Recipe> getTastingRecipes(int userId, int loginId) throws SQLException {
PreparedStatement preparedStatement =
connection.prepareStatement("SELECT versions.id AS id, beschreibung, " +
"IFNULL(versions.imagename, CONCAT('category/', kategorien.image)) AS image, " +
"gerichte.eingefuegt AS created, min, std, skill, kalorien, gerichte.name AS name, personen, " +
"kategorien_name, active_id, users.id AS users_id, nickname, users.image, viewed, last_change, " +
"(SELECT IF(COUNT(users_id) = 1, TRUE, FALSE) FROM schmeckt " +
"WHERE schmeckt.gerichte_name = gerichte.name " +
"AND schmeckt.users_id = ?) AS tastes " +
"FROM gerichte " +
"INNER JOIN versions ON gerichte.name = gerichte_name AND active_id = versions.id " +
"INNER JOIN users ON versions.users_id = users.id " +
"INNER JOIN kategorien ON kategorien_name = kategorien.name " +
"INNER JOIN schmeckt ON gerichte.name = schmeckt.gerichte_name " +
"WHERE schmeckt.users_id = ? GROUP BY name ORDER BY schmeckt.eingefuegt DESC;");
preparedStatement.setInt(1, loginId);
preparedStatement.setInt(2, userId);
ResultSet data = preparedStatement.executeQuery();
return getRecipes(data);
}
public List<String> getRecipeNamesForUserId(int userId) throws SQLException {
List<String> result = new LinkedList<>();
PreparedStatement pStatement = connection.prepareStatement("SELECT gerichte_name FROM versions " +
"LEFT JOIN gerichte ON gerichte_name = name " +
"WHERE versions.users_id = ? AND active_id > -1 ORDER BY versions.eingefuegt DESC");
pStatement.setInt(1, userId);
ResultSet data = pStatement.executeQuery();
while (data.next()) {
String recipeName = data.getString("gerichte_name");
result.add(recipeName);
}
return result;
}
public List<Recipe> getRecipesForUserId(int userId, int loginId) throws SQLException {
PreparedStatement statement =
connection.prepareStatement("SELECT versions.id AS id, beschreibung, " +
"IFNULL(versions.imagename, CONCAT('category/', kategorien.image)) AS image, " +
"gerichte.eingefuegt AS created, min, std, skill, kalorien, gerichte.name AS name, personen, " +
"kategorien_name, active_id, users_id, nickname, users.image, COUNT(users_id) AS counter, viewed, " +
"last_change, " +
"(SELECT COUNT(users_id) FROM schmeckt WHERE schmeckt.gerichte_name = gerichte.name " +
"AND schmeckt.users_id = ?) AS tastes " +
"FROM gerichte " +
"INNER JOIN versions ON gerichte.name = gerichte_name " +
"INNER JOIN users ON users_id = users.id " +
"INNER JOIN kategorien ON kategorien_name = kategorien.name " +
"WHERE versions.users_id = ? AND active_id > -1 " +
"GROUP BY gerichte.name ORDER BY versions.eingefuegt DESC;");
statement.setInt(1, loginId);
statement.setInt(2, userId);
ResultSet data = statement.executeQuery();
return getRecipes(data);
}
public Date getLastModified() throws SQLException {
PreparedStatement preparedStatement =
connection.prepareStatement("SELECT last_change FROM gerichte ORDER BY last_change DESC LIMIT 1");
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
return resultSet.getTimestamp("last_change");
}
return new Date();
}
}