Skip to content
Snippets Groups Projects
Select Git revision
  • 5f1e0190bb351a3d6021f2881234ed5922c0d7e4
  • master default
  • renovate/junit-jupiter-engine.version
  • renovate/selenium.version
  • renovate/testcontainer.version
  • demo
  • v1_8_1
  • v2.18.1
  • v2.18.0
  • v2.17.2
  • v2.17.1
  • v2.17.0
  • v2.16.1
  • v2.16.0
  • v2.15.1
  • v2.15.0
  • v2.14.0
  • v2.13.0
  • v2.12.0
  • v2.11.0
  • v2.10.0
  • v2.9.2
  • v2.9.1
  • v2.9.0
  • v2.8.0
  • testPipeline2
  • v2.7.0
27 results

DatabaseHandler.java

Blame
    • Robert Goldmann's avatar
      5f1e0190
      Fixed #79 · 5f1e0190
      Robert Goldmann authored
      rest is not calculated if database doesn't contain at least one normal
      and one repeating payment
      5f1e0190
      History
      Fixed #79
      Robert Goldmann authored
      rest is not calculated if database doesn't contain at least one normal
      and one repeating payment
    DatabaseHandler.java 16.87 KiB
    package de.deadlocker8.budgetmasterserver.main;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    
    import org.joda.time.DateTime;
    import org.joda.time.format.DateTimeFormat;
    import org.joda.time.format.DateTimeFormatter;
    
    import de.deadlocker8.budgetmaster.logic.Category;
    import de.deadlocker8.budgetmaster.logic.LatestRepeatingPayment;
    import de.deadlocker8.budgetmaster.logic.NormalPayment;
    import de.deadlocker8.budgetmaster.logic.Payment;
    import de.deadlocker8.budgetmaster.logic.RepeatingPayment;
    import de.deadlocker8.budgetmaster.logic.RepeatingPaymentEntry;
    import javafx.scene.paint.Color;
    import logger.Logger;
    import tools.ConvertTo;
    
    public class DatabaseHandler
    {
    	private Connection connection;
    	private final DateTimeFormatter formatter = DateTimeFormat.forPattern("yyyy-MM-dd");
    
    	public DatabaseHandler(Settings settings) throws IllegalStateException
    	{
    		try
    		{
    			this.connection = DriverManager.getConnection(settings.getDatabaseUrl() + settings.getDatabaseName() + "?useLegacyDatetimeCode=false&serverTimezone=Europe/Berlin", settings.getDatabaseUsername(), settings.getDatabasePassword());
    			new DatabaseCreator(connection, settings);
    			Logger.info("Successfully initialized database (" + settings.getDatabaseUrl() + settings.getDatabaseName() + ")");
    		}
    		catch(Exception e)
    		{
    			Logger.error(e);
    			throw new IllegalStateException("Cannot connect the database!", e);
    		}
    	}
    
    	/*
    	 * GET
    	 */
    	public DateTime getFirstNormalPaymentDate()
    	{
    		Statement stmt = null;
    		String query = "SELECT MIN(Date) as \"min\" FROM payment";
    		DateTime dateTime = null;
    		try
    		{
    			stmt = connection.createStatement();
    			ResultSet rs = stmt.executeQuery(query);
    
    			while(rs.next())
    			{
    				String min = rs.getString("min");
    				if(min == null)
    				{
    					dateTime = null;
    				}
    				else
    				{
    					dateTime = formatter.parseDateTime(rs.getString("min"));
    				}
    			}
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    
    		return dateTime;
    	}
    	
    	public DateTime getFirstRepeatingPaymentDate()
    	{
    		Statement stmt = null;
    		String query = "SELECT MIN(Date) as \"min\" FROM repeating_payment";
    		DateTime dateTime = null;
    		try
    		{
    			stmt = connection.createStatement();
    			ResultSet rs = stmt.executeQuery(query);
    
    			while(rs.next())
    			{
    				String min = rs.getString("min");
    				if(min == null)
    				{
    					dateTime = null;
    				}
    				else
    				{
    					dateTime = formatter.parseDateTime(rs.getString("min"));
    				}
    			}
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    
    		return dateTime;
    	}
    
    	public int getRestForAllPreviousMonths(int year, int month)
    	{		
    		DateTimeFormatter formatter = DateTimeFormat.forPattern("MM.yyyy");
    		String dateString = String.valueOf(month) + "." + year;
    		DateTime currentDate = formatter.parseDateTime(dateString);		
    		
    		DateTime firstNormalPaymentDate = getFirstNormalPaymentDate();
    		if(firstNormalPaymentDate == null)
    		{
    			firstNormalPaymentDate = currentDate;
    		}
    		DateTime firstRepeatingPaymentDate = getFirstRepeatingPaymentDate();
    		if(firstRepeatingPaymentDate == null)
    		{
    			firstRepeatingPaymentDate = currentDate;
    		}
    		
    		DateTime firstDate = firstNormalPaymentDate;
    		if(firstRepeatingPaymentDate.isBefore(firstNormalPaymentDate))
    		{
    			firstDate = firstRepeatingPaymentDate;
    		}	
    	
    		if(firstDate.isAfter(currentDate))
    		{
    			return 0;
    		}
    
    		int startYear = firstDate.getYear();
    		int startMonth = firstDate.getMonthOfYear();
    		int totalRest = 0;
    		
    		while(startYear < year || startMonth < month)
    		{
    			totalRest += getRest(startYear, startMonth);			
    
    			startMonth++;
    			if(startMonth > 12)
    			{
    				startMonth = 1;
    				startYear++;
    			}
    		}
    		return totalRest;
    	}
    
    	public int getRest(int year, int month)
    	{
    		ArrayList<Payment> payments = new ArrayList<>();
    		payments.addAll(getPayments(year, month));
    		payments.addAll(getRepeatingPayments(year, month));
    		
    		int rest = 0;
    		for(Payment currentPayment : payments)
    		{
    			rest += currentPayment.getAmount();
    		}
    		
    		return rest;
    	}
    
    	public ArrayList<Category> getCategories()
    	{
    		Statement stmt = null;
    		String query = "SELECT * FROM category ORDER BY category.ID";
    		ArrayList<Category> results = new ArrayList<>();
    		try
    		{
    			stmt = connection.createStatement();
    			ResultSet rs = stmt.executeQuery(query);
    			while(rs.next())
    			{
    				int id = rs.getInt("ID");
    				String name = rs.getString("Name");
    				String color = rs.getString("Color");
    
    				results.add(new Category(id, name, Color.web(color)));
    			}
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    
    		return results;
    	}
    	
    	public Category getCategory(int ID)
    	{
    		Statement stmt = null;
    		String query = "SELECT * FROM category WHERE category.ID = " + ID;	
    		Category result = null;
    		try
    		{
    			stmt = connection.createStatement();
    			ResultSet rs = stmt.executeQuery(query);			
    			while(rs.next())
    			{
    				int id = rs.getInt("ID");
    				String name = rs.getString("Name");
    				String color = rs.getString("Color");
    
    				result = new Category(id, name, Color.web(color));
    			}
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    
    		return result;
    	}
    	
    	public NormalPayment getPayment(int ID)
    	{
    		Statement stmt = null;
    		String query = "SELECT * FROM payment WHERE payment.ID= " + ID;
    		try
    		{
    			stmt = connection.createStatement();
    			ResultSet rs = stmt.executeQuery(query);
    
    			while(rs.next())
    			{
    				int resultID = rs.getInt("ID");
    				String name = rs.getString("Name");
    				int amount = rs.getInt("amount");
    				String date = rs.getString("Date");
    				int categoryID = rs.getInt("CategoryID");
    				String description = rs.getString("Description");
    
    				return new NormalPayment(resultID, amount, date, categoryID, name, description);
    			}
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    
    		return null;
    	}
    
    	public ArrayList<NormalPayment> getPayments(int year, int month)
    	{
    		Statement stmt = null;
    		String query = "SELECT * FROM payment WHERE YEAR(Date) = " + year + " AND  MONTH(Date) = " + month;
    
    		ArrayList<NormalPayment> results = new ArrayList<>();
    		try
    		{
    			stmt = connection.createStatement();
    			ResultSet rs = stmt.executeQuery(query);
    
    			while(rs.next())
    			{
    				int resultID = rs.getInt("ID");				
    				String name = rs.getString("Name");
    				int amount = rs.getInt("amount");
    				String date = rs.getString("Date");				
    				int categoryID = rs.getInt("CategoryID");
    				String description = rs.getString("Description");
    			
    				results.add(new NormalPayment(resultID, amount, date, categoryID, name, description));
    			}
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    
    		return results;
    	}
    
    	public ArrayList<RepeatingPaymentEntry> getRepeatingPayments(int year, int month)
    	{
    		Statement stmt = null;
    		String query = "SELECT repeating_entry.ID, repeating_entry.RepeatingPaymentID, repeating_entry.Date, repeating_payment.Name, repeating_payment.CategoryID, repeating_payment.Amount, repeating_payment.RepeatInterval, repeating_payment.RepeatEndDate, repeating_payment.RepeatMonthDay, repeating_payment.Description FROM repeating_entry, repeating_payment WHERE repeating_entry.RepeatingPaymentID = repeating_payment.ID AND YEAR(repeating_entry.Date) = " + year + " AND MONTH(repeating_entry.Date) = " + month;
    
    		ArrayList<RepeatingPaymentEntry> results = new ArrayList<>();
    		try
    		{
    			stmt = connection.createStatement();
    			ResultSet rs = stmt.executeQuery(query);
    
    			while(rs.next())
    			{
    				int resultID = rs.getInt("ID");
    				int repeatingPaymentID = rs.getInt("repeatingPaymentID");				
    				String name = rs.getString("Name");
    				String description = rs.getString("Description");
    				int amount = rs.getInt("amount");
    				String date = rs.getString("Date");				
    				int categoryID = rs.getInt("CategoryID");
    				int repeatInterval = rs.getInt("RepeatInterval");
    				String repeatEndDate = rs.getString("RepeatEndDate");
    				int repeatMonthDay = rs.getInt("RepeatMonthDay");		
    			
    				results.add(new RepeatingPaymentEntry(resultID, repeatingPaymentID, date, amount, categoryID, name, description,repeatInterval, repeatEndDate, repeatMonthDay));
    			}
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    
    		return results;
    	}
    	
    	public ArrayList<RepeatingPayment> getAllRepeatingPayments()
    	{		
    		Statement stmt = null;
    		String query = "SELECT * FROM repeating_payment;";
    
    		ArrayList<RepeatingPayment> results = new ArrayList<>();
    		try
    		{
    			stmt = connection.createStatement();
    			ResultSet rs = stmt.executeQuery(query);
    
    			while(rs.next())
    			{
    				int resultID = rs.getInt("ID");				
    				String name = rs.getString("Name");
    				int amount = rs.getInt("amount");
    				String date = rs.getString("Date");		
    				String description = rs.getString("Description");
    				int categoryID = rs.getInt("CategoryID");
    				int repeatInterval = rs.getInt("RepeatInterval");
    				String repeatEndDate = rs.getString("RepeatEndDate");
    				int repeatMonthDay = rs.getInt("RepeatMonthDay");			
    
    				results.add(new RepeatingPayment(resultID, amount, date, categoryID, name, description, repeatInterval, repeatEndDate, repeatMonthDay));
    			}
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    
    		return results;
    	}
    	
    	public ArrayList<LatestRepeatingPayment> getLatestRepeatingPaymentEntries()
    	{
    		Statement stmt = null;
    		String query = "SELECT ID, RepeatingPaymentID, MAX(Date) as 'LastDate' FROM repeating_entry GROUP BY RepeatingPaymentID";
    
    		ArrayList<LatestRepeatingPayment> results = new ArrayList<>();
    		try
    		{
    			stmt = connection.createStatement();
    			ResultSet rs = stmt.executeQuery(query);
    
    			while(rs.next())
    			{
    				int resultID = rs.getInt("ID");
    				int repeatingPaymentID = rs.getInt("repeatingPaymentID");				
    				String date = rs.getString("LastDate");
    			
    				results.add(new LatestRepeatingPayment(resultID, repeatingPaymentID, date));
    			}
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    
    		return results;
    	}
    	
    	public RepeatingPayment getRepeatingPayment(int ID)
    	{
    		Statement stmt = null;
    		String query = "SELECT * FROM repeating_payment WHERE ID = " + ID;	
    		RepeatingPayment result = null;
    		try
    		{
    			stmt = connection.createStatement();
    			ResultSet rs = stmt.executeQuery(query);			
    			while(rs.next())
    			{
    				int id = rs.getInt("ID");				
    				int amount = rs.getInt("amount");
    				String date = rs.getString("Date");
    				int categoryID = rs.getInt("CategoryID");
    				String name = rs.getString("Name");
    				String description = rs.getString("Description");
    				int repeatInterval = rs.getInt("repeatInterval");
    				String repeatEndDate = rs.getString("repeatEndDate");
    				int repeatMonthDay = rs.getInt("repeatMonthDay");
    
    				result = new RepeatingPayment(id, amount, date, categoryID, name, description, repeatInterval, repeatEndDate, repeatMonthDay);
    			}
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    
    		return result;
    	}
    
    	/*
    	 * DELETE
    	 */
    	public void deleteCategory(int ID)
    	{
    		Statement stmt = null;
    		String query = "DELETE FROM category WHERE category.ID = " + ID;
    		try
    		{
    			stmt = connection.createStatement();
    			stmt.execute(query);
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    	}
    	
    	public void deletePayment(int ID)
    	{
    		Statement stmt = null;
    		String query = "DELETE FROM payment WHERE payment.ID = " + ID;
    		try
    		{
    			stmt = connection.createStatement();
    			stmt.execute(query);
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    	}
    	
    	public void deleteRepeatingPayment(int ID)
    	{
    		Statement stmt = null;
    		String query = "DELETE FROM repeating_payment WHERE repeating_payment.ID = " + ID;
    		try
    		{
    			stmt = connection.createStatement();
    			stmt.execute(query);
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    	}
    
    	/*
    	 * ADD
    	 */
    	public void addCategory(String name, Color color)
    	{
    		Statement stmt = null;
    		String query = "INSERT INTO category (Name, Color) VALUES('" + name + "' , '" + ConvertTo.toRGBHexWithoutOpacity(color) + "');";
    		try
    		{
    			stmt = connection.createStatement();
    			stmt.execute(query);
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    	}
    
    	public void addNormalPayment(int amount, String date, int categoryID, String name, String description)
    	{
    		Statement stmt = null;
    		String query = "INSERT INTO payment (Amount, Date, CategoryID, Name, Description) VALUES('" + amount + "' , '" + date + "' , '" + categoryID + "' , '" + name + "' , '" + description + "');";
    		try
    		{
    			stmt = connection.createStatement();
    			stmt.execute(query);
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    	}
    	
    	public void addRepeatingPayment(int amount, String date, int categoryID, String name, String description, int repeatInterval, String repeatEndDate, int repeatMonthDay)
    	{
    		Statement stmt = null;
    		String query;		
    		//A is placeholder for empty repeatEndDate
    		if(repeatEndDate.equals("A") || repeatEndDate == null)
    		{			
    			query = "INSERT INTO repeating_payment (Amount, Date, CategoryID, Name, RepeatInterval, RepeatEndDate, RepeatMonthDay, Description) VALUES('" + amount + "' , '" + date + "' , '" + categoryID + "' , '" + name + "' , '" + repeatInterval + "' , NULL , '" + repeatMonthDay + "' , '" + description + "');";
    		}
    		else
    		{
    			query = "INSERT INTO repeating_payment (Amount, Date, CategoryID, Name, RepeatInterval, RepeatEndDate, RepeatMonthDay, Description) VALUES('" + amount + "' , '" + date + "' , '" + categoryID + "' , '" + name + "' , '" + repeatInterval + "' , '" + repeatEndDate + "' , '" + repeatMonthDay + "' , '" + description + "');";
    		}
    		
    		try
    		{
    			stmt = connection.createStatement();
    			stmt.execute(query);
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    	}
    	
    	public void addRepeatingPaymentEntry(int repeatingPaymentID, String date)
    	{
    		Statement stmt = null;
    		String query;		
    		query = "INSERT INTO repeating_entry (RepeatingPaymentID, Date) VALUES('" +  repeatingPaymentID + "' , '" + date + "');";
    		try
    		{
    			stmt = connection.createStatement();
    			stmt.execute(query);
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    	}
    
    	/*
    	 * UPDATE
    	 */
    	public void updateCategory(int ID, String name, Color color)
    	{
    		Statement stmt = null;
    		String query = "UPDATE category SET name='" + name + "' , color='" + ConvertTo.toRGBHexWithoutOpacity(color) + "' WHERE ID = " + ID + ";";
    		try
    		{
    			stmt = connection.createStatement();
    			stmt.execute(query);
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    	}	
    	
    	public void updateNormalPayment(int ID, int amount, String date, int categoryID, String name, String description)
    	{
    		Statement stmt = null;
    		String query = "UPDATE payment SET amount = '" + amount + "', date='" + date + "', categoryID='" + categoryID + "', name='" + name + "', description='" + description + "' WHERE ID = " + ID + ";";
    		try
    		{
    			stmt = connection.createStatement();
    			stmt.execute(query);
    		}
    		catch(SQLException e)
    		{
    			Logger.error(e);
    		}
    		finally
    		{
    			if(stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch(SQLException e)
    				{
    				}
    			}
    		}
    	}
    }