/**
* This file is part of Pau's Asset Manager Project.
*
* Pau's Asset Manager Project 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.
*
* Pau's Asset Manager Project 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 Pau's Asset Manager Project. If not, see <http://www.gnu.org/licenses/>.
*/
package org.pau.assetmanager.business;
import java.io.Serializable;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.pau.assetmanager.entities.Annotation;
import org.pau.assetmanager.entities.Annotation.AnnotationType;
import org.pau.assetmanager.entities.Book;
import org.pau.assetmanager.entities.MovementExpensesAnnotation;
import org.pau.assetmanager.entities.MovementIncomeAnnotation;
import org.pau.assetmanager.entities.PropertyBook;
import org.pau.assetmanager.entities.PropertyExpensesAnnotation;
import org.pau.assetmanager.entities.PropertyIncomeAnnotation;
import org.pau.assetmanager.entities.StockExpensesAnnotation;
import org.pau.assetmanager.entities.StockIncomeAnnotation;
import org.pau.assetmanager.entities.StocksBook;
import org.pau.assetmanager.viewmodel.type.ClientDomainType;
import org.pau.assetmanager.viewmodel.type.PropertyType;
import org.pau.assetmanager.viewmodel.utils.AnnotationsFilter;
import org.pau.assetmanager.viewmodel.utils.BookSelection;
import org.pau.assetmanager.viewmodel.utils.SortingCriteria;
import com.google.common.base.Optional;
import com.google.common.base.Predicate;
import com.google.common.collect.Collections2;
import com.google.common.collect.Lists;
/**
* This class contains database methods related to Annotations {@link org.pau.assetmanager.entities.Annotation}
*
* @author Pau Carré Cardona
*
*/
public class AnnotationsBusiness {
private static Logger logger = LogManager
.getLogger(AnnotationsBusiness.class);
/**
* Deletes the detached Annotation
* @param annotation annotation to find in the database and then delete
*/
public static void deleteAnnotation(Annotation annotation) {
DaoFunction.<Annotation> deleteDetachedFunction().apply(annotation);
}
/**
* Creates a new Annotation in the database
* @param annotation annotation to insert in the database
*/
public static <A extends Annotation> A createAnnotation(A annotation) {
return DaoFunction.<A> mergeFunction().apply(annotation);
}
/**
* Selects the Annotations necessary for the yearly tax report for a specific year and a specific book selection
*
* For the yearly report we only have into account the Annotations related to Properties and Stocks, which are the only ones
* that can be included in the tax subsystem. Furthermore, we ensure that the expense annotations have a deductible percentage
* and finally that the income annotations are done (they are not in pending)
*
* @param year year of the report which can be null skipping the year filtering
* @param bookSelection book selection information
* @param sortingCriteria is the date sorting criteria for the result list
*
* @return List of annotations for a given year that are relevant for the yearly tax payment report
*/
public static List<Annotation> getYearlyReportAnnotationsFromDatabase(
BookSelection bookSelection, Integer year, SortingCriteria sortingCriteria) {
Collection<Annotation> annotaitons = new LinkedList<>();
if(!bookSelection.isAllBooks() && bookSelection.getSelectedBook() instanceof PropertyBook){
/*
* if a single book is selected then we need all
* the annotataions for all the books related to the
* property of the selected book
*/
PropertyBook propertyBook = (PropertyBook) bookSelection.getSelectedBook();
Collection<PropertyBook> booksForTheProperty = BooksBusiness.getBooksFromProperty(propertyBook.getProperty());
for(PropertyBook currentPropertyBook : booksForTheProperty){
annotaitons.addAll(getAnnotationsWithFilter(
Optional.<AnnotationType> absent(), BookSelection.fromBook(currentPropertyBook),
Optional.of(year), ClientDomainType.CURRENT_CLIENT,
SortingCriteria.DESCENDING,
AnnotationsFilter.emptyAnnotationsFilter()));
}
}else{
// if all the books are selected or it is not a property book, we pick all the annotations
// from the selection
annotaitons.addAll(getAnnotationsWithFilter(
Optional.<AnnotationType> absent(), bookSelection,
Optional.of(year), ClientDomainType.CURRENT_CLIENT,
SortingCriteria.DESCENDING,
AnnotationsFilter.emptyAnnotationsFilter()));
}
Collection<Annotation> yearlyAnnotations = Collections2.filter(
annotaitons, new Predicate<Annotation>() {
@Override
public boolean apply(Annotation annotation) {
if (annotation instanceof PropertyExpensesAnnotation) {
PropertyExpensesAnnotation propertyExpensesAnnotation = (PropertyExpensesAnnotation) annotation;
// deductible > 0
return propertyExpensesAnnotation
.getDeductiblePercentage() > 0.0;
}
if (annotation instanceof PropertyIncomeAnnotation) {
PropertyIncomeAnnotation propertyIncomeAnnotation = (PropertyIncomeAnnotation) annotation;
// done
return propertyIncomeAnnotation.getDone();
}
if(annotation.getBook() instanceof StocksBook){
return true;
}
return false;
}
});
return Lists.newLinkedList(yearlyAnnotations);
}
/**
* Returns the list of annotations in the database that can be used for the quarterly tax report.
* The annotations for the quarterly report are all based on Properties and fit the following rules:
* The property can only be a PREMISE or a TENEMENT.
* In case of TENEMENT, it is necessary that the expense is related to a payment from a company ('forCompany' field)
*
* Furthermore, for the incomes, we only have into account the ones done (not pending) and for the expenses we ensure
* that the deducible percentage is above 0%.
*
* @param bookSelection book selection information
* @param optionalYear year of the annotations which can be absent skipping the year filtering
* @return the annotations related to the quarterly report sorted in a descending date order
*/
public static List<Annotation> getQuarterlyHaciendaAnnotationsFromDatabase(
BookSelection bookSelection, Optional<Integer> optionalYear) {
Collection<Annotation> annotaitons = new LinkedList<>();
if(!bookSelection.isAllBooks() && bookSelection.getSelectedBook() instanceof PropertyBook){
/*
* if a single book is selected then we need all
* the annotataions for all the books related to the
* property of the selected book
*/
PropertyBook propertyBook = (PropertyBook) bookSelection.getSelectedBook();
Collection<PropertyBook> booksForTheProperty = BooksBusiness.getBooksFromProperty(propertyBook.getProperty());
for(PropertyBook currentPropertyBook : booksForTheProperty){
annotaitons.addAll(getAnnotationsWithFilter(
Optional.<AnnotationType> absent(), BookSelection.fromBook(currentPropertyBook),
optionalYear, ClientDomainType.CURRENT_CLIENT,
SortingCriteria.DESCENDING,
AnnotationsFilter.emptyAnnotationsFilter()));
}
}else{
// if all the books are selected or it is not a property book, we pick all the annotations
// from the selection
annotaitons.addAll(getAnnotationsWithFilter(
Optional.<AnnotationType> absent(), bookSelection,
optionalYear, ClientDomainType.CURRENT_CLIENT,
SortingCriteria.DESCENDING,
AnnotationsFilter.emptyAnnotationsFilter()));
}
Collection<Annotation> quarterlyAnnotations = Collections2.filter(
annotaitons, new Predicate<Annotation>() {
@Override
public boolean apply(Annotation annotation) {
if (annotation instanceof PropertyExpensesAnnotation) {
PropertyExpensesAnnotation propertyExpensesAnnotation = (PropertyExpensesAnnotation) annotation;
// deductible > 0 and use_quaterly
return propertyExpensesAnnotation
.getDeductiblePercentage() > 0.0
&& propertyExpensesAnnotation
.getUseQuarterly();
}
if (annotation instanceof PropertyIncomeAnnotation) {
PropertyIncomeAnnotation propertyIncomeAnnotation = (PropertyIncomeAnnotation) annotation;
// done and [ ( premise ) or ( tenment and for_company) ]
return propertyIncomeAnnotation.getDone()
&& (propertyIncomeAnnotation
.getPropertyBook().getProperty()
.getType()
.equals(PropertyType.PREMISE) ||
(
propertyIncomeAnnotation
.getPropertyBook().getProperty()
.getType()
.equals(PropertyType.TENEMENT)
&&
propertyIncomeAnnotation.getForCompany()
)
);
}
return false;
}
});
return Lists.newLinkedList(quarterlyAnnotations);
}
/**
* Returns the Movements to other Books (MovementExpensesAnnnotations) for a given book selection
*
* @param bookSelection book selection information
* @param currentYear the optional year of the annotations which can be absent skipping the year filtering
* @param annotationsFilter the filter that will be used to discard unwanted annotations (usually, selected in the user interface)
* @param sortingCriteria is the date sorting criteria for the resulting list of annotations
* @return the list of annotations of the given book filtered by year and additionally by the filter
*/
public static List<Annotation> getMovementExpensesAnnotations(
BookSelection bookSelection, Optional<Integer> currentYear,
SortingCriteria sortingCriteria, AnnotationsFilter annotationsFilter) {
Collection<Annotation> annotations = getAnnotationsWithFilter(
Optional.<AnnotationType> absent(), bookSelection, currentYear,
ClientDomainType.CURRENT_CLIENT, sortingCriteria,
annotationsFilter);
Collection<Annotation> nonMovementAnnotations = Collections2.filter(
annotations, new Predicate<Annotation>() {
@Override
public boolean apply(Annotation annotation) {
return annotation != null
&& annotation instanceof MovementExpensesAnnotation;
}
});
return Lists.newLinkedList(nonMovementAnnotations);
}
/**
* Returns a Map of the form 'Concept Name' -> 'Total Expenses' given a book selection, a year and a client selection domain
*
* @param bookSelection book selection to have into account
* @param year year of the annotations involved in the concepts
* @param clientType client selection domain (all clients, a single client)
*
* @return the Map of the form 'Concept Name' -> 'Total Expenses' fitting the constraints form the arguments
*/
public static Map<String, Double> getExpensesDataForBook(
BookSelection bookSelection, Integer year,
ClientDomainType clientType) {
List<Object[]> annotations = new LinkedList<Object[]>();
Map<String, Serializable> parameters = new HashMap<String, Serializable>();
StringBuffer queryString = new StringBuffer(" select a.concept, sum(amount) / 100 from Annotation a inner join Book book on book.id = a.book inner join Client client on book.client = client.id inner join ExpensesAnnotation expensesAnnotation on expensesAnnotation.id = a.id where 1 ");
if (bookSelection.isAllBooks()) {
if (clientType.equals(ClientDomainType.CURRENT_CLIENT)) {
queryString.append(" and client = :client ");
parameters.put("client", bookSelection.getSelectedClient());
}
} else {
queryString.append(" and a.book = :book ");
parameters.put("book", bookSelection.getSelectedBook());
}
if (year != null) {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(
"dd/MM/yyyy");
try {
Date begin = simpleDateFormat.parse("01/01/" + year);
Date end = simpleDateFormat.parse("01/01/" + (year + 1));
queryString.append(" and a.date >= :date_year_from and a.date < :date_year_to ");
parameters.put("date_year_from", begin);
parameters.put("date_year_to", end);
} catch (ParseException e) {
logger.error("Error parsing date", e);
}
}
queryString.append(" group by concept");
annotations = DaoFunction.<Object[]> nativeQueryListFunction(
queryString.toString()).apply(parameters);
Map<String, Double> expensesDataForBook = new HashMap<String, Double>();
for (Object[] annotation : annotations) {
expensesDataForBook.put((String) annotation[0],
((BigDecimal) annotation[1]).doubleValue());
}
return expensesDataForBook;
}
/**
* Retrieves all the stock annotations (IncomeStockAnnotation and ExpensesStockAnnotation) which the database consistency
* guarantees to be linked to a StocksBook. The lit of stocks is filtered by an optional year, a clientType (described below) and
* a book selection
*
* @param year optional year to filter the stock annotations
* @param bookSelection book selection information
* @param clientDomainTypes the client domain type can be either 'CURRENT_CLIENT' (the client selected) or 'ALL_CLIENTS' which will have into account
* all the clients in the database.
* @param sortingCriteria is the date sorting criteria for the resulting list of annotations
*
* @return returns the list of annotations in the database filtered by the parameters
*/
public static List<Annotation> getAllStocksAnnotationsFromDatabase(
BookSelection bookSelection, Optional<Integer> year,
ClientDomainType clientDomainTypes, SortingCriteria sortingCriteria) {
Collection<Annotation> annotations = AnnotationsBusiness
.getAnnotationsWithFilter(Optional.<AnnotationType> absent(),
bookSelection, year, clientDomainTypes, sortingCriteria,
AnnotationsFilter.emptyAnnotationsFilter());
Collection<Annotation> stocksAnnotations = Collections2.filter(annotations,
new Predicate<Annotation>() {
@Override
public boolean apply(Annotation annotation) {
return annotation instanceof StockIncomeAnnotation ||
annotation instanceof StockExpensesAnnotation;
}
});
return Lists.newLinkedList(stocksAnnotations);
}
public static List<Annotation> getAllStocksAnnotationsFromDatabaseUntilYear(
BookSelection bookSelection, Integer lastYear,
ClientDomainType clientDomainType, SortingCriteria sortingCriteria) {
AnnotationsFilter annotationsFilter = AnnotationsFilter.emptyAnnotationsFilter();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd-MM-yyyy");
Date dateTo;
try {
dateTo = simpleDateFormat.parse("31-12-"+lastYear);
annotationsFilter.setDateTo(dateTo);
} catch (ParseException e) {
//TODO: log error
e.printStackTrace();
}
Collection<Annotation> annotations = AnnotationsBusiness
.getAnnotationsWithFilter(Optional.<AnnotationType> absent(),
bookSelection, Optional.<Integer>absent(), clientDomainType, sortingCriteria,
annotationsFilter);
Collection<Annotation> stocksAnnotations = Collections2.filter(annotations,
new Predicate<Annotation>() {
@Override
public boolean apply(Annotation annotation) {
return annotation.getBook() instanceof StocksBook;
}
});
return Lists.newLinkedList(stocksAnnotations);
}
/**
* Returns all the annotations for a given book selection without time constraints
*
* @param selectedBook book selection to have into account
* @param sortingCriteria is the date sorting criteria for the resulting list of annotations
*
* @return all the annotations from the book selection without time constraints
*/
public static List<Annotation> getAllTimeAnnotationsForSelectedBook(
Book selectedBook, SortingCriteria sortingCriteria) {
List<Annotation> annotations = new LinkedList<Annotation>();
if (selectedBook != null && selectedBook.getClient() != null) {
return getAnnotationsWithFilter(Optional.<AnnotationType> absent(),
BookSelection.fromBook(selectedBook),
Optional.<Integer> absent(),
ClientDomainType.CURRENT_CLIENT, sortingCriteria,
AnnotationsFilter.emptyAnnotationsFilter());
}
return annotations;
}
/**
* Returns the annotations that fit a the filter 'annotationsFilter' an annotation type, a year and a book selection
*
* @param typo optional annotation type (expenses or income)
* @param bookSelection book selection applicable
* @param currentYear optional year for the annotations
* @param annotationsFilter filter to apply to the search
* @param sortingCriteria is the date sorting criteria for the resulting list of annotations
*
* @return the list of the annotations that fit the constraints of the parameters
*/
public static List<Annotation> getNonMovementAnnotations(
Optional<AnnotationType> type, BookSelection bookSelection,
Optional<Integer> currentYear, SortingCriteria sortingCriteria,
AnnotationsFilter annotationsFilter) {
Collection<Annotation> annotations = getAnnotationsWithFilter(type,
bookSelection, currentYear, ClientDomainType.CURRENT_CLIENT,
sortingCriteria, annotationsFilter);
Collection<Annotation> nonMovementAnnotations = Collections2.filter(
annotations, new Predicate<Annotation>() {
@Override
public boolean apply(Annotation annotation) {
return annotation != null
&& !(annotation instanceof MovementIncomeAnnotation || annotation instanceof MovementExpensesAnnotation);
}
});
return Lists.newLinkedList(nonMovementAnnotations);
}
/**
* Returns the annotations that fit a the filter 'annotationsFilter' an annotation type, a year and a book selection
*
* @param type optional annotation type (expenses or income)
* @param bookSelection book selection applicable
* @param optionalCurrentYear optional year for the annotations
* @param annotationsFilter filter to apply to the search
* @param sortingCriteria is the date sorting criteria for the resulting list of annotations
*
* @return the list of the annotations that fit the constraints of the parameters
*/
public static List<Annotation> getAnnotationsWithFilter(
Optional<AnnotationType> optinalType, BookSelection bookSelection,
Optional<Integer> optionalCurrentYear,
ClientDomainType clientDomainTypes,
SortingCriteria sortingCriteria, AnnotationsFilter annotationsFilter) {
Map<String, Serializable> parameters = new HashMap<String, Serializable>();
StringBuffer queryString = new StringBuffer("select annotation from ");
if (optinalType.isPresent()) {
AnnotationType type = optinalType.get();
if (type.equals(AnnotationType.EXPENSES)) {
queryString.append(" ExpensesAnnotation ");
} else if (type.equals(AnnotationType.INCOME)) {
queryString.append(" IncomeAnnotation ");
}
} else {
queryString.append(" Annotation ");
}
queryString.append(" annotation inner join fetch annotation.book book where 1=1 ");
if (clientDomainTypes.equals(ClientDomainType.CURRENT_CLIENT)) {
if (!bookSelection.isAllBooks()) {
queryString.append(" and annotation.book=:selectedBook ");
parameters.put("selectedBook", bookSelection.getSelectedBook());
} else {
queryString.append(" and annotation.book.client=:client ");
parameters.put("client", bookSelection.getSelectedClient());
}
}
if (optionalCurrentYear.isPresent()) {
Integer currentYear = optionalCurrentYear.get();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(
"dd/MM/yyyy");
try {
Date begin = simpleDateFormat.parse("01/01/" + currentYear);
Date end = simpleDateFormat.parse("01/01/" + (currentYear + 1));
queryString.append(" and annotation.date >= :date_year_from and annotation.date < :date_year_to ");
parameters.put("date_year_from", begin);
parameters.put("date_year_to", end);
} catch (ParseException e) {
logger.error("Error parsing date", e);
}
}
queryString.append(getAnnotationsFilterQueryConstraints(parameters,
optinalType, annotationsFilter));
if (sortingCriteria.equals(SortingCriteria.ASCENDING)) {
queryString.append(" order by annotation.date asc");
} else if (sortingCriteria.equals(SortingCriteria.DESCENDING)) {
queryString.append(" order by annotation.date desc");
}
List<Annotation> annotations = DaoFunction
.<Annotation> queryListFunction(queryString.toString()).apply(parameters);
return annotations;
}
/**
* This class computes both a constraint part of a JPA query related to the filtering contained in 'annotationsFilter'
* and furthermore it fills the argument 'parameters' with the values of the 'annotationsFilter'.
*
* @param parameters map of parameters to be used in the final query
* @param optinalType optional annotation type for the annotations to be filtered
* @param annotationsFilter the filter to be applied
*
* @return the constrained part of the query necessary to retrieve the annotations that fit he filter
*/
public static String getAnnotationsFilterQueryConstraints(
Map<String, Serializable> parameters,
Optional<AnnotationType> optinalType,
AnnotationsFilter annotationsFilter) {
StringBuffer queryString = new StringBuffer("");
if (annotationsFilter != null) {
if (annotationsFilter.getCommunity() != null) {
queryString.append(" and annotation.community=:community");
parameters.put("community", annotationsFilter.getCommunity());
}
if (annotationsFilter.getDeductiblePercentageFrom() != null) {
queryString.append(" and annotation.deductiblePercentage >= :deductiblePercentage_from");
parameters.put("deductiblePercentage_from",
Math.round(annotationsFilter
.getDeductiblePercentageFrom() * 100));
}
if (annotationsFilter.getDeductiblePercentageTo() != null) {
queryString.append(" and annotation.deductiblePercentage <= :deductiblePercentage_to");
parameters.put("deductiblePercentage_to",
Math.round(annotationsFilter
.getDeductiblePercentageTo() * 100));
}
if (annotationsFilter.getDone() != null) {
queryString.append(" and annotation.done=:done");
parameters.put("done", annotationsFilter.getDone());
}
if (annotationsFilter.getConcept() != null
&& !annotationsFilter.getConcept().equals(
AnnotationsFilter.ALL_CONCEPTS)) {
queryString.append(" and annotation.concept=:concept");
parameters.put("concept", annotationsFilter.getConcept());
}
if (annotationsFilter.getDateFrom() != null) {
queryString.append(" and annotation.date >= :date_from");
parameters.put("date_from", annotationsFilter.getDateFrom());
}
if (annotationsFilter.getDateTo() != null) {
queryString.append(" and annotation.date <= :date_to");
parameters.put("date_to", annotationsFilter.getDateTo());
}
if (annotationsFilter.getCostFrom() != null) {
queryString.append(" and annotation.amount >= :cost_from");
parameters.put("cost_from",
Math.round(annotationsFilter.getCostFrom() * 100));
}
if (annotationsFilter.getCostTo() != null) {
queryString.append(" and annotation.amount <= :cost_to");
parameters.put("cost_to",
Math.round(annotationsFilter.getCostTo() * 100));
}
if (annotationsFilter.getVatFrom() != null) {
queryString.append(" and annotation.vat >= :vat_from");
parameters.put("vat_from",
Math.round(annotationsFilter.getVatFrom() * 100));
}
if (annotationsFilter.getVatTo() != null) {
queryString.append(" and annotation.vat <= :vat_to");
parameters.put("vat_to",
Math.round(annotationsFilter.getVatTo() * 100));
}
if (annotationsFilter.getRetentionFrom() != null) {
queryString.append(" and annotation.retention >= :retention_from");
parameters.put("retention_from",
Math.round(annotationsFilter.getRetentionFrom() * 100));
}
if (annotationsFilter.getRetentionTo() != null) {
queryString.append(" and annotation.retention <= :retention_to");
parameters.put("retention_to",
Math.round(annotationsFilter.getRetentionTo() * 100));
}
if (annotationsFilter.getUseYearly() != null) {
queryString.append(" and annotation.useYearly = :use_quarterly");
parameters.put("use_quarterly",
annotationsFilter.getUseYearly());
}
if (optinalType.isPresent()) {
AnnotationType type = optinalType.get();
if (type.equals(AnnotationType.INCOME)
&& (annotationsFilter.getVatTo() != null
|| annotationsFilter.getVatFrom() != null
|| annotationsFilter.getRetentionTo() != null || annotationsFilter
.getRetentionFrom() != null)) {
queryString.append(" and ( type(book) = PropertyBook and ( book.property.type='PREMISE' or ( book.property.type='TENEMENT' and annotation.forCompany = true ) ) ) ");
}
if (type.equals(AnnotationType.EXPENSES)
&& annotationsFilter.getUseQuarterly() != null) {
queryString.append(" and annotation.useQuarterly = :use_quarterly");
parameters.put("use_quarterly",
annotationsFilter.getUseQuarterly());
}
if (type.equals(AnnotationType.INCOME)
&& annotationsFilter.getForCompany() != null) {
queryString.append(" and annotation.forCompany = :for_company");
parameters.put("for_company",
annotationsFilter.getForCompany());
}
}
}
return queryString.toString();
}
}