/*
* This file is part of LibrePlan
*
* Copyright (C) 2009-2010 Fundación para o Fomento da Calidade Industrial e
* Desenvolvemento Tecnolóxico de Galicia
* Copyright (C) 2010-2012 Igalia, S.L.
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero 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 Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
package org.libreplan.business.resources.daos;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.libreplan.business.common.daos.IntegrationEntityDAO;
import org.libreplan.business.labels.entities.Label;
import org.libreplan.business.reports.dtos.HoursWorkedPerResourceDTO;
import org.libreplan.business.reports.dtos.HoursWorkedPerWorkerInAMonthDTO;
import org.libreplan.business.reports.dtos.LabelFilterType;
import org.libreplan.business.resources.entities.Criterion;
import org.libreplan.business.resources.entities.LimitingResourceQueue;
import org.libreplan.business.resources.entities.Machine;
import org.libreplan.business.resources.entities.Resource;
import org.libreplan.business.resources.entities.Worker;
import org.libreplan.business.scenarios.IScenarioManager;
import org.libreplan.business.workingday.EffortDuration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.config.BeanDefinition;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
/**
* Hibernate DAO for the <code>Resource</code> entity.
* @author Fernando Bellas Permuy <fbellas@udc.es>
* @author Diego Pino Garcia <dpino@udc.es>
*/
@Repository
@Scope(BeanDefinition.SCOPE_SINGLETON)
@Transactional
public class ResourceDAO extends IntegrationEntityDAO<Resource> implements
IResourceDAO {
@Autowired
private IScenarioManager scenarioManager;
@Override
public List<Worker> getWorkers() {
return list(Worker.class);
}
@Override
public List<Worker> getVirtualWorkers() {
List<Worker> list = getWorkers();
for (Iterator<Worker> iterator = list.iterator(); iterator.hasNext();) {
Worker worker = iterator.next();
if (worker.isReal()) {
iterator.remove();
}
}
return list;
}
@Override
public List<Worker> getRealWorkers() {
List<Worker> list = getWorkers();
for (Iterator<Worker> iterator = list.iterator(); iterator.hasNext();) {
Worker worker = iterator.next();
if (worker.isVirtual()) {
iterator.remove();
}
}
return list;
}
public List<Resource> getResources() {
return list(Resource.class);
}
@SuppressWarnings("unchecked")
@Override
public List<Resource> getAllLimitingResources() {
return getSession().createCriteria(Resource.class).add(
Restrictions.eq("limitingResource", true)).list();
}
@SuppressWarnings("unchecked")
@Override
public List<Resource> getAllNonLimitingResources() {
return getSession().createCriteria(Resource.class).add(
Restrictions.eq("limitingResource", false)).list();
}
@Override
public List<Machine> getMachines() {
return list(Machine.class);
}
@Override
public List<Resource> getRealResources() {
List<Resource> list = new ArrayList<Resource>();
list.addAll(getRealWorkers());
list.addAll(getMachines());
return list;
}
@Override
public void save(Resource resource) {
if (resource instanceof Worker || resource instanceof Machine) {
if (resource.isLimitingResource() && resource.getLimitingResourceQueue() == null) {
resource.setLimitingResourceQueue(LimitingResourceQueue.create());
}
}
super.save(resource);
}
@Override
@Transactional(readOnly = true)
public List<HoursWorkedPerResourceDTO> getWorkingHoursPerWorker(
List<Resource> resources, List<Label> labels,
LabelFilterType labelFilterType, List<Criterion> criterions,
Date startingDate,
Date endingDate) {
String strQuery = "SELECT new org.libreplan.business.reports.dtos.HoursWorkedPerResourceDTO(resource, wrl) "
+ "FROM Resource resource, WorkReportLine wrl "
+ "LEFT OUTER JOIN wrl.resource wrlresource "
+ "WHERE wrlresource.id = resource.id ";
// Set date range
if (startingDate != null && endingDate != null) {
strQuery += "AND wrl.date BETWEEN :startingDate AND :endingDate ";
}
if (startingDate != null && endingDate == null) {
strQuery += "AND wrl.date >= :startingDate ";
}
if (startingDate == null && endingDate != null) {
strQuery += "AND wrl.date <= :endingDate ";
}
// Set workers
if (resources != null && !resources.isEmpty()) {
strQuery += "AND resource IN (:resources) ";
}
// Set labels
if (labels != null && !labels.isEmpty()) {
switch (labelFilterType) {
case ORDER_ELEMENT:
strQuery += " AND ( EXISTS (FROM wrl.orderElement.labels as etq WHERE etq IN (:labels)) "
+ "OR EXISTS (FROM wrl.workReport.orderElement.labels as etqwr WHERE etqwr IN (:labels)) ) ";
break;
case WORK_REPORT:
strQuery += " AND ( EXISTS (FROM wrl.labels as etq WHERE etq IN (:labels)) "
+ "OR EXISTS (FROM wrl.workReport.labels as etqwr WHERE etqwr IN (:labels)) ) ";
break;
case BOTH:
strQuery += " AND ( EXISTS (FROM wrl.labels as etq WHERE etq IN (:labels)) "
+ "OR EXISTS (FROM wrl.workReport.labels as etqwr WHERE etqwr IN (:labels)) ) "
+ "AND ( EXISTS (FROM wrl.orderElement.labels as etq WHERE etq IN (:labels)) "
+ "OR EXISTS (FROM wrl.workReport.orderElement.labels as etqwr WHERE etqwr IN (:labels)) ) ";
break;
case ANY:
default:
strQuery += " AND ( ( EXISTS (FROM wrl.labels as etq WHERE etq IN (:labels)) "
+ "OR EXISTS (FROM wrl.workReport.labels as etqwr WHERE etqwr IN (:labels)) ) "
+ "OR ( EXISTS (FROM wrl.orderElement.labels as etq WHERE etq IN (:labels)) "
+ "OR EXISTS (FROM wrl.workReport.orderElement.labels as etqwr WHERE etqwr IN (:labels)) ) ) ";
break;
}
}
// Set Criterions
if (criterions != null && !criterions.isEmpty()) {
strQuery += " AND EXISTS (FROM resource.criterionSatisfactions as satisfaction "
+ " WHERE satisfaction.criterion IN (:criterions)) ";
}
// Order by
strQuery += "ORDER BY resource.id, wrl.date";
// Set parameters
Query query = getSession().createQuery(strQuery);
if (startingDate != null) {
query.setParameter("startingDate", startingDate);
}
if (endingDate != null) {
query.setParameter("endingDate", endingDate);
}
if (resources != null && !resources.isEmpty()) {
query.setParameterList("resources", resources);
}
if (labels != null && !labels.isEmpty()) {
query.setParameterList("labels", labels);
}
if (criterions != null && !criterions.isEmpty()) {
query.setParameterList("criterions",
Criterion.withAllDescendants(criterions));
}
// Get result
return query.list();
}
@Override
public List<HoursWorkedPerWorkerInAMonthDTO> getWorkingHoursPerWorker(
Integer year, Integer month) {
String strQuery = "SELECT wrlresource.id, SUM(wrl.effort) "
+ "FROM WorkReportLine wrl "
+ "LEFT OUTER JOIN wrl.resource wrlresource ";
if (year != null) {
strQuery += "WHERE YEAR(wrl.date) = :year ";
}
if (month != null) {
strQuery += "AND MONTH(wrl.date) = :month ";
}
strQuery += "GROUP BY wrlresource.id, MONTH(wrl.date) ";
Query query = getSession().createQuery(strQuery);
if (year != null) {
query.setParameter("year", year);
}
if (month != null) {
query.setParameter("month", month);
}
List<HoursWorkedPerWorkerInAMonthDTO> result = toDTO(query.list());
return result;
}
private List<HoursWorkedPerWorkerInAMonthDTO> toDTO(List<Object> rows) {
List<HoursWorkedPerWorkerInAMonthDTO> result = new ArrayList<HoursWorkedPerWorkerInAMonthDTO>();
for (Object row: rows) {
Object[] columns = (Object[]) row;
Resource resource = (Resource) findExistingEntity((Long) columns[0]);
EffortDuration effort = EffortDuration.seconds(((Long) columns[1])
.intValue());
HoursWorkedPerWorkerInAMonthDTO dto = new HoursWorkedPerWorkerInAMonthDTO(
resource, effort);
result.add(dto);
}
return result;
}
}