es.sm2.openppm.core.dao.TimesheetDAO.java Source code

Java tutorial

Introduction

Here is the source code for es.sm2.openppm.core.dao.TimesheetDAO.java

Source

/*
 * Copyright (C) 2009-2015 SM2 SOFTWARE & SERVICES MANAGEMENT
 * 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 has been created in the hope that it will be useful.
 * It is distributed WITHOUT ANY WARRANTY of any Kind,
 * 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/.
 *
 * For more information, please contact SM2 Software & Services Management.
 * Mail: info@talaia-openppm.com
 * Web: http://www.talaia-openppm.com
 *
 * Module: core
 * File: TimesheetDAO.java
 * Create User: javier.hernandez
 * Create Date: 15/03/2015 12:52:46
 */

package es.sm2.openppm.core.dao;

import java.util.*;

import es.sm2.openppm.core.common.Settings;
import es.sm2.openppm.core.logic.setting.GeneralSetting;
import es.sm2.openppm.core.model.wrap.ImputationWrap;
import es.sm2.openppm.core.utils.SettingUtil;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import es.sm2.openppm.core.common.Constants;
import es.sm2.openppm.core.javabean.PropertyRelation;
import es.sm2.openppm.core.utils.FilterUtil;
import es.sm2.openppm.core.model.impl.Employee;
import es.sm2.openppm.core.model.impl.Operation;
import es.sm2.openppm.core.model.impl.Project;
import es.sm2.openppm.core.model.impl.Projectactivity;
import es.sm2.openppm.core.model.impl.Resourcepool;
import es.sm2.openppm.core.model.impl.Resourceprofiles;
import es.sm2.openppm.core.model.impl.Teammember;
import es.sm2.openppm.core.model.impl.Timesheet;
import es.sm2.openppm.core.model.impl.Wbsnode;
import es.sm2.openppm.core.model.wrap.ApprovalWrap;
import es.sm2.openppm.core.model.wrap.ResourceTimeWrap;
import es.sm2.openppm.core.model.wrap.TimesheetWrap;
import es.sm2.openppm.utils.DateUtil;
import es.sm2.openppm.utils.functions.ValidateUtil;
import es.sm2.openppm.utils.hibernate.dao.AbstractGenericHibernateDAO;

/**
 * DAO object for domain model class Timesheet
 * @see es.sm2.openppm.core.dao.Timesheet
 * @author Hibernate Generator by Javier Hernandez
 */
public class TimesheetDAO extends AbstractGenericHibernateDAO<Timesheet, Integer> {

    public TimesheetDAO(Session session) {
        super(session);
    }

    /**
     * Find time Sheet of the resource by criteria 
     * @param employee
     * @param projectactivity
     * @param initDate
     * @param endDate
     * @return
     */
    public Timesheet findByCriteria(Employee employee, Projectactivity projectactivity, Date initDate,
            Date endDate) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .add(Restrictions.eq(Timesheet.PROJECTACTIVITY, projectactivity))
                .add(Restrictions.eq(Timesheet.EMPLOYEE, employee))
                .add(Restrictions.eq(Timesheet.INITDATE, initDate))
                .add(Restrictions.eq(Timesheet.ENDDATE, endDate));

        return (Timesheet) crit.uniqueResult();
    }

    /**
     * Find time sheets of the resource
     * @param employee
     * @param initDate
     * @param endDate
     * @param joins
     * @param project
     * @param minStatus
     * @param maxStatus
     * @param filterUser
     * @param settings
      * @return
     */
    public List<Timesheet> findByCriteria(Employee employee, Date initDate, Date endDate, List<String> joins,
            Project project, String minStatus, String maxStatus, Employee filterUser, boolean includeClosed,
            HashMap<String, String> settings) {

        return findByCriteria(employee, initDate, endDate, joins, project, minStatus, maxStatus, filterUser,
                Constants.RESOURCE_ASSIGNED, includeClosed, settings);
    }

    /**
     * Find time sheets of the resource
     * 
     * @param employee
     * @param initDate
     * @param endDate
     * @param joins
     * @param project
     * @param minStatus
     * @param maxStatus
     * @param filterUser
     * @param statusResource
      * @param settings
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Timesheet> findByCriteria(Employee employee, Date initDate, Date endDate, List<String> joins,
            Project project, String minStatus, String maxStatus, Employee filterUser, String statusResource,
            boolean includeClosed, HashMap<String, String> settings) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                .add(Restrictions.eq(Timesheet.EMPLOYEE, employee))
                .add(Restrictions.eq(Timesheet.INITDATE, initDate))
                .add(Restrictions.eq(Timesheet.ENDDATE, endDate));

        if (minStatus != null && maxStatus != null) {

            Disjunction disjunction = Restrictions.disjunction();
            disjunction.add(Restrictions.eq(Timesheet.STATUS, minStatus));
            disjunction.add(Restrictions.eq(Timesheet.STATUS, maxStatus));

            if (Constants.TIMESTATUS_APP0.equals(minStatus) && (Constants.TIMESTATUS_APP2.equals(maxStatus)
                    || Constants.TIMESTATUS_APP3.equals(maxStatus))) {

                disjunction.add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP1));
            }

            if (Constants.TIMESTATUS_APP3.equals(maxStatus) && (Constants.TIMESTATUS_APP0.equals(minStatus)
                    || Constants.TIMESTATUS_APP1.equals(minStatus))) {

                disjunction.add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP2));
            }

            //TODO 21/04/2015 - jordi.ripoll- tarea 1095 -pendiente de confirmacion
            // Last level be approved hours app1 by setting
            //
            //            String lastLevelForApprove = SettingUtil.getString(settings, Settings.SETTING_LAST_LEVEL_FOR_APPROVE_SHEET,
            //                    Settings.DEFAULT_LAST_LEVEL_FOR_APPROVE_SHEET);
            //
            //            if ((filterUser != null && lastLevelForApprove.equals(String.valueOf(filterUser.getResourceprofiles().getIdProfile()))) &&
            //                    (settings != null && SettingUtil.getBoolean(settings, GeneralSetting.LAST_LEVEL_APPROVED_APP1))) {
            //
            //                disjunction.add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP1));
            //            }

            crit.add(disjunction);
        }

        Criteria critEmployee = crit.createCriteria(Timesheet.EMPLOYEE);
        critEmployee.createCriteria(Employee.CONTACT);

        Criteria critActivity = crit.createCriteria(Timesheet.PROJECTACTIVITY);

        Criteria critMembers = critActivity.createCriteria(Projectactivity.TEAMMEMBERS)
                .add(Restrictions.eq(Teammember.EMPLOYEE, employee));

        if (ValidateUtil.isNotNull(statusResource)) {
            critMembers.add(Restrictions.eq(Teammember.STATUS, statusResource));
        }

        Criteria critProject = critActivity.createCriteria(Projectactivity.PROJECT);

        if (!includeClosed) {
            critProject.add(Restrictions.and(Restrictions.ne(Project.STATUS, Constants.STATUS_CLOSED),
                    Restrictions.ne(Project.STATUS, Constants.STATUS_ARCHIVED)));
        }

        if (project != null) {
            critProject.add(Restrictions.eq(Project.IDPROJECT, project.getIdProject()));
        } else if (filterUser != null) {

            // Filter by User. Settings by company for last approval.
            Resourceprofiles profile = filterUser.getResourceprofiles();

            if (profile.getIdProfile() == Constants.ROLE_FM) {

                critProject.add(Restrictions.eq(Project.EMPLOYEEBYFUNCTIONALMANAGER, filterUser));
            } else if (profile.getIdProfile() == Constants.ROLE_PMO) {

                critProject.add(Restrictions.eq(Project.PERFORMINGORG, filterUser.getPerformingorg()));
            }
        }

        addJoins(crit, joins);

        return crit.list();
    }

    /**
     * Hours assigned
     * 
     * @param teammember
     * @param minStatus
     * @param maxStatus
     * @return
     */
    public boolean hoursInState(Teammember teammember, String minStatus, String maxStatus) {

        String q = "SELECT COUNT(ts) FROM Timesheet ts " + "JOIN ts.employee e " + "JOIN e.teammembers tem "
                + "WHERE " + "ts.projectactivity = :projectactivity "
                + "AND ((ts.initDate between :since and :until) " + "OR (ts.endDate between :since and :until) "
                + "OR (:since between ts.initDate and ts.endDate) "
                + "OR (:until between ts.initDate and ts.endDate)) " + "AND tem = :teammember "
                + "AND (ts.status = :minStatus OR ts.status = :maxStatus)";

        Query query = getSession().createQuery(q);

        query.setEntity("teammember", teammember);
        query.setEntity("projectactivity", teammember.getProjectactivity());
        query.setDate("since", DateUtil.getFirstWeekDay(teammember.getDateIn()));
        query.setDate("until", DateUtil.getFirstWeekDay(teammember.getDateOut()));
        query.setString("minStatus", minStatus);
        query.setString("maxStatus", maxStatus);

        Long count = (Long) query.uniqueResult();

        return (count != null && count > 0);
    }

    /**
     * Time Sheets in state by member
     * 
     * @param teammember
     * @param status
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Timesheet> hoursInState(Teammember teammember, String status) {

        String q = "SELECT ts FROM Timesheet ts " + "JOIN ts.employee e " + "JOIN e.teammembers tem " + "WHERE "
                + "ts.projectactivity = :projectactivity " + "AND ts.status = :status " + "AND tem = :teammember "
                + "AND ((ts.initDate between :since and :until) " + "OR (ts.endDate between :since and :until) "
                + "OR (:since between ts.initDate and ts.endDate) "
                + "OR (:until between ts.initDate and ts.endDate)) ";

        Query query = getSession().createQuery(q);

        query.setEntity("teammember", teammember);
        query.setEntity("projectactivity", teammember.getProjectactivity());
        query.setDate("since", DateUtil.getFirstWeekDay(teammember.getDateIn()));
        query.setDate("until", DateUtil.getFirstWeekDay(teammember.getDateOut()));
        query.setString("status", status);

        return query.list();
    }

    /**
     * 
     * @param employee
     * @param initDate
     * @param endDate
     * @param joins
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Timesheet> findWithActivityByResource(Employee employee, Date initDate, Date endDate,
            List<String> joins) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                .add(Restrictions.eq(Timesheet.EMPLOYEE, employee))
                .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3))
                .add(Restrictions.ge(Timesheet.INITDATE, initDate)).add(Restrictions.le(Timesheet.ENDDATE, endDate))
                .createAlias(Timesheet.PROJECTACTIVITY, "pActivity")
                .addOrder(Order.asc("pActivity." + Projectactivity.PROJECT))
                .addOrder(Order.asc(Timesheet.INITDATE));

        addJoins(crit, joins);

        return crit.list();
    }

    /**
     * 
     * @param employee
     * @param initDate
     * @param endDate
     * @param joins
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Timesheet> findWithOperationByResource(Employee employee, Date initDate, Date endDate,
            List<String> joins) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                .add(Restrictions.isNull(Timesheet.PROJECTACTIVITY))
                .add(Restrictions.eq(Timesheet.EMPLOYEE, employee))
                .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3))
                .add(Restrictions.ge(Timesheet.INITDATE, initDate)).add(Restrictions.le(Timesheet.ENDDATE, endDate))
                .addOrder(Order.asc(Timesheet.OPERATION)).addOrder(Order.asc(Timesheet.INITDATE));

        addJoins(crit, joins);

        return crit.list();
    }

    /**
     * Find unique time sheet operation
     * @param operation
     * @param consUser
     * @param initDate
     * @param endDate
     * @return
     */
    public Timesheet findByOperation(Operation operation, Employee employee, Date initDate, Date endDate) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .add(Restrictions.eq(Timesheet.EMPLOYEE, employee))
                .add(Restrictions.eq(Timesheet.OPERATION, operation))
                .add(Restrictions.eq(Timesheet.INITDATE, initDate))
                .add(Restrictions.eq(Timesheet.ENDDATE, endDate));

        return (Timesheet) crit.uniqueResult();
    }

    /**
     * Find Time Sheets operations
     * @param employee
     * @param initDate
     * @param endDate
     * @param joins
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Timesheet> findByOperation(Employee employee, Date initDate, Date endDate, List<String> joins,
            String minStatus, String maxStatus) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .add(Restrictions.eq(Timesheet.EMPLOYEE, employee)).add(Restrictions.isNotNull(Timesheet.OPERATION))
                .add(Restrictions.eq(Timesheet.INITDATE, initDate))
                .add(Restrictions.eq(Timesheet.ENDDATE, endDate));

        if (minStatus != null && maxStatus != null) {

            Disjunction disjunction = Restrictions.disjunction();
            disjunction.add(Restrictions.eq(Timesheet.STATUS, minStatus));
            disjunction.add(Restrictions.eq(Timesheet.STATUS, maxStatus));

            if (Constants.TIMESTATUS_APP0.equals(minStatus) && (Constants.TIMESTATUS_APP2.equals(maxStatus)
                    || Constants.TIMESTATUS_APP3.equals(maxStatus))) {

                disjunction.add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP1));
            }

            if (Constants.TIMESTATUS_APP3.equals(maxStatus) && (Constants.TIMESTATUS_APP0.equals(minStatus)
                    || Constants.TIMESTATUS_APP1.equals(minStatus))) {

                disjunction.add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP2));
            }

            crit.add(disjunction);
        }

        addJoins(crit, joins);

        return crit.list();
    }

    /**
     * Is in status
     * @param idEmployee
     * @param id
     * @param initDate
     * @param endDate
     * @param status
     * @param filterUser 
     * @return
     */
    public boolean isStatusResource(Integer idEmployee, Integer id, Date initDate, Date endDate, String status,
            Employee filterUser) {

        Criteria crit = getSession().createCriteria(getPersistentClass()).setProjection(Projections.rowCount())
                .add(Restrictions.eq(Timesheet.STATUS, status)).add(Restrictions.eq(Timesheet.INITDATE, initDate))
                .add(Restrictions.eq(Timesheet.ENDDATE, endDate))
                .add(Restrictions.eq(Timesheet.EMPLOYEE, new Employee(idEmployee)));

        if (Constants.TIMESTATUS_APP1.equals(status)) {

            if (id.equals(0)) {
                crit.add(Restrictions.isNull(Timesheet.PROJECTACTIVITY));
            } else {
                crit.createCriteria(Timesheet.PROJECTACTIVITY)
                        .add(Restrictions.eq(Projectactivity.PROJECT, new Project(id)));
            }
        } else if (Constants.TIMESTATUS_APP2.equals(status)) {
            Criteria critFilter = crit.createCriteria(Timesheet.PROJECTACTIVITY)
                    .createCriteria(Projectactivity.PROJECT);

            // Filter by User. Settings by company for last approval.

            Resourceprofiles profile = filterUser.getResourceprofiles();

            if (profile.getIdProfile() == Constants.ROLE_FM) {

                critFilter.add(Restrictions.eq(Project.EMPLOYEEBYFUNCTIONALMANAGER, filterUser));
            } else if (profile.getIdProfile() == Constants.ROLE_PM) {

                critFilter.add(Restrictions.eq(Project.EMPLOYEEBYPROJECTMANAGER, filterUser));
            } else {

                critFilter.add(Restrictions.eq(Project.PERFORMINGORG, filterUser.getPerformingorg()));
            }
        }

        Integer count = (Integer) crit.uniqueResult();

        return (count != null && count > 0);
    }

    /**
     * Is time status
     * @param idEmployee
     * @param initDate
     * @param endDate
     * @param status
     * @param filterUser
     * @param inOperation
     * @return
     */
    public boolean isTimeStatus(Integer idEmployee, Date initDate, Date endDate, String status, Employee filterUser,
            boolean inOperation) {

        boolean isStatus = false;

        Criteria crit = getSession().createCriteria(getPersistentClass()).setProjection(Projections.rowCount())
                .add(Restrictions.eq(Timesheet.STATUS, status)).add(Restrictions.eq(Timesheet.INITDATE, initDate))
                .add(Restrictions.eq(Timesheet.ENDDATE, endDate))
                .add(Restrictions.eq(Timesheet.EMPLOYEE, new Employee(idEmployee)));

        if (inOperation) {
            crit.add(Restrictions.isNotNull(Timesheet.OPERATION));
        } else {
            Resourceprofiles profile = filterUser.getResourceprofiles();

            Criterion restriction = null;

            if (profile.getIdProfile() == Constants.ROLE_FM) {
                restriction = Restrictions.eq(Project.EMPLOYEEBYFUNCTIONALMANAGER, filterUser);
            } else if (profile.getIdProfile() == Constants.ROLE_PM) {
                restriction = Restrictions.eq(Project.EMPLOYEEBYPROJECTMANAGER, filterUser);
            } else {
                restriction = Restrictions.eq(Project.PERFORMINGORG, filterUser.getPerformingorg());
            }

            Criteria critFilter = crit.createCriteria(Timesheet.PROJECTACTIVITY)
                    .createCriteria(Projectactivity.PROJECT);
            critFilter.add(restriction);
        }

        isStatus = (Integer) crit.uniqueResult() > 0;

        if (!isStatus && !inOperation) {
            isStatus = isTimeStatus(idEmployee, initDate, endDate, status, filterUser, true);
        }

        return isStatus;
    }

    /**
     * Get Hours resource
     * @param idEmployee
     * @param id
     * @param initDate
     * @param endDate
     * @param minStatus
     * @param maxStatus
     * @return
     */
    public double getHoursResource(Integer idEmployee, Integer id, Date initDate, Date endDate, String minStatus,
            Employee filterUser) {

        ProjectionList proList = Projections.projectionList();
        proList.add(Projections.sum(Timesheet.HOURSDAY1));
        proList.add(Projections.sum(Timesheet.HOURSDAY2));
        proList.add(Projections.sum(Timesheet.HOURSDAY3));
        proList.add(Projections.sum(Timesheet.HOURSDAY4));
        proList.add(Projections.sum(Timesheet.HOURSDAY5));
        proList.add(Projections.sum(Timesheet.HOURSDAY6));
        proList.add(Projections.sum(Timesheet.HOURSDAY7));

        Criteria crit = getSession().createCriteria(getPersistentClass()).setProjection(proList)
                .add(Restrictions.disjunction().add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP1))
                        .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP2))
                        .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3)))
                .add(Restrictions.eq(Timesheet.INITDATE, initDate)).add(Restrictions.eq(Timesheet.ENDDATE, endDate))
                .add(Restrictions.eq(Timesheet.EMPLOYEE, new Employee(idEmployee)));

        if (Constants.TIMESTATUS_APP1.equals(minStatus)) {
            crit.createCriteria(Timesheet.PROJECTACTIVITY)
                    .add(Restrictions.eq(Projectactivity.PROJECT, new Project(id)));
        } else if (Constants.TIMESTATUS_APP2.equals(minStatus)) {

            // Filter by User. Settings by company for last approval.

            Criteria critFilter = crit.createCriteria(Timesheet.PROJECTACTIVITY)
                    .createCriteria(Projectactivity.PROJECT);

            Resourceprofiles profile = filterUser.getResourceprofiles();

            if (profile.getIdProfile() == Constants.ROLE_FM) {

                critFilter.add(Restrictions.eq(Project.EMPLOYEEBYFUNCTIONALMANAGER, filterUser));
            } else if (profile.getIdProfile() == Constants.ROLE_PMO) {

                critFilter.add(Restrictions.eq(Project.PERFORMINGORG, filterUser.getPerformingorg()));
            }
        }
        Object[] hoursList = (Object[]) crit.uniqueResult();

        double hours = 0;

        if (hoursList != null) {
            hours += (hoursList[0] == null ? 0 : (Double) hoursList[0]);
            hours += (hoursList[1] == null ? 0 : (Double) hoursList[1]);
            hours += (hoursList[2] == null ? 0 : (Double) hoursList[2]);
            hours += (hoursList[3] == null ? 0 : (Double) hoursList[3]);
            hours += (hoursList[4] == null ? 0 : (Double) hoursList[4]);
            hours += (hoursList[5] == null ? 0 : (Double) hoursList[5]);
            hours += (hoursList[6] == null ? 0 : (Double) hoursList[6]);
        }

        return hours;
    }

    /**
     * Get Hours resource by operation
     * @param idEmployee
     * @param initDate
     * @param endDate
     * @return
     */
    public double getHoursResourceOpeartion(Integer idEmployee, Date initDate, Date endDate) {

        ProjectionList proList = Projections.projectionList();
        proList.add(Projections.sum(Timesheet.HOURSDAY1));
        proList.add(Projections.sum(Timesheet.HOURSDAY2));
        proList.add(Projections.sum(Timesheet.HOURSDAY3));
        proList.add(Projections.sum(Timesheet.HOURSDAY4));
        proList.add(Projections.sum(Timesheet.HOURSDAY5));
        proList.add(Projections.sum(Timesheet.HOURSDAY6));
        proList.add(Projections.sum(Timesheet.HOURSDAY7));

        Criteria crit = getSession().createCriteria(getPersistentClass()).setProjection(proList)
                .add(Restrictions.disjunction().add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP1))
                        .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP2))
                        .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3)))
                .add(Restrictions.eq(Timesheet.INITDATE, initDate)).add(Restrictions.eq(Timesheet.ENDDATE, endDate))
                .add(Restrictions.eq(Timesheet.EMPLOYEE, new Employee(idEmployee)))
                .add(Restrictions.isNotNull(Timesheet.OPERATION));

        Object[] hoursList = (Object[]) crit.uniqueResult();

        double hours = 0;

        if (hoursList != null) {
            hours += (hoursList[0] == null ? 0 : (Double) hoursList[0]);
            hours += (hoursList[1] == null ? 0 : (Double) hoursList[1]);
            hours += (hoursList[2] == null ? 0 : (Double) hoursList[2]);
            hours += (hoursList[3] == null ? 0 : (Double) hoursList[3]);
            hours += (hoursList[4] == null ? 0 : (Double) hoursList[4]);
            hours += (hoursList[5] == null ? 0 : (Double) hoursList[5]);
            hours += (hoursList[6] == null ? 0 : (Double) hoursList[6]);
        }

        return hours;
    }

    /**
     * Calculate Fte for inputed hours in project
     * 
     * @param project
     * @param member
     * @param firstWeekDay
     * @param lastWeekDay
     * @return
     */
    public double getHoursResource(Project project, Employee member, Date firstWeekDay, Date lastWeekDay) {

        ProjectionList proList = Projections.projectionList();
        proList.add(Projections.sum(Timesheet.HOURSDAY1));
        proList.add(Projections.sum(Timesheet.HOURSDAY2));
        proList.add(Projections.sum(Timesheet.HOURSDAY3));
        proList.add(Projections.sum(Timesheet.HOURSDAY4));
        proList.add(Projections.sum(Timesheet.HOURSDAY5));
        proList.add(Projections.sum(Timesheet.HOURSDAY6));
        proList.add(Projections.sum(Timesheet.HOURSDAY7));

        Criteria crit = getSession().createCriteria(getPersistentClass()).setProjection(proList)
                .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3))
                .add(Restrictions.eq(Timesheet.EMPLOYEE, member));

        if (firstWeekDay != null && lastWeekDay != null) {

            crit.add(Restrictions.eq(Timesheet.INITDATE, firstWeekDay))
                    .add(Restrictions.eq(Timesheet.ENDDATE, lastWeekDay));
        }

        // Aliases 
        //
        crit.createAlias(Timesheet.PROJECTACTIVITY, "pa", CriteriaSpecification.LEFT_JOIN);

        // Projects or operations
        crit.add(Restrictions.disjunction().add(Restrictions.isNotNull(Timesheet.OPERATION))
                .add(Restrictions.eq("pa." + Projectactivity.PROJECT, project)));

        Object[] hoursList = (Object[]) crit.uniqueResult();

        double hours = 0;

        if (hoursList != null) {
            hours += (hoursList[0] == null ? 0 : (Double) hoursList[0]);
            hours += (hoursList[1] == null ? 0 : (Double) hoursList[1]);
            hours += (hoursList[2] == null ? 0 : (Double) hoursList[2]);
            hours += (hoursList[3] == null ? 0 : (Double) hoursList[3]);
            hours += (hoursList[4] == null ? 0 : (Double) hoursList[4]);
            hours += (hoursList[5] == null ? 0 : (Double) hoursList[5]);
            hours += (hoursList[6] == null ? 0 : (Double) hoursList[6]);
        }

        return hours;
    }

    public double getHoursResourceInDates(Project project, Employee member, Date since, Date until,
            Integer idResourcePool, Operation operation, Projectactivity activity) {

        Criteria crit = getSession().createCriteria(getPersistentClass());

        Criteria employeeCrit = null;

        if (member != null || idResourcePool != null) {
            employeeCrit = crit.createCriteria(Timesheet.EMPLOYEE);
        }

        if (idResourcePool != null) {
            employeeCrit.add(Restrictions.eq(Employee.RESOURCEPOOL, new Resourcepool(idResourcePool)));
        }

        if (member != null) {
            employeeCrit.add(Restrictions.idEq(member.getIdEmployee()));
        }

        if (since != null && until != null) {

            crit.add(Restrictions.disjunction().add(Restrictions.between(Timesheet.INITDATE, since, until))
                    .add(Restrictions.between(Timesheet.ENDDATE, since, until))
                    .add(Restrictions.and(Restrictions.le(Timesheet.INITDATE, since),
                            Restrictions.ge(Timesheet.ENDDATE, until))));
        }

        if (project != null) {

            crit.add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3));

            Criteria activitiesCrit = crit.createCriteria(Timesheet.PROJECTACTIVITY)
                    .add(Restrictions.eq(Projectactivity.PROJECT, project));

            // Select only timesheet whose activity is control account
            Criteria wbsnodeCrit = activitiesCrit.createCriteria(Projectactivity.WBSNODE)
                    .add(Restrictions.eq(Wbsnode.ISCONTROLACCOUNT, true));
        } else if (operation != null) {

            crit.add(Restrictions.or(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP2),
                    Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3)))
                    .add(Restrictions.eq(Timesheet.OPERATION, operation));
        } else if (activity != null) {

            crit.add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3))
                    .add(Restrictions.eq(Timesheet.PROJECTACTIVITY, activity));
        }

        return calcHours(crit.list(), since, until);
    }

    /**
     * Get hours in status of project
     *
     * @param project
     * @param status
     * @return
     */
    public double getHoursResource(Project project, String status) {

        Criteria crit = getSession().createCriteria(getPersistentClass());

        crit.add(Restrictions.eq(Timesheet.STATUS, status == null ? Constants.TIMESTATUS_APP3 : status));

        Criteria activitiesCrit = crit.createCriteria(Timesheet.PROJECTACTIVITY)
                .add(Restrictions.eq(Projectactivity.PROJECT, project));

        // Select only timesheet whose activity is control account
        Criteria wbsnodeCrit = activitiesCrit.createCriteria(Projectactivity.WBSNODE)
                .add(Restrictions.eq(Wbsnode.ISCONTROLACCOUNT, true));

        return calcHours(crit.list(), null, null);
    }

    private double calcHours(List<Timesheet> timeSheets, Date since, Date until) {

        double hours = 0;

        for (Timesheet item : timeSheets) {

            Calendar tempCal = DateUtil.getCalendar();
            tempCal.setTime(item.getInitDate());

            if ((since == null || until == null) || DateUtil.between(since, until, tempCal.getTime())) {
                hours += (item.getHoursDay1() == null ? 0 : item.getHoursDay1());
            }
            tempCal.add(Calendar.DAY_OF_MONTH, 1);

            if ((since == null || until == null) || DateUtil.between(since, until, tempCal.getTime())) {
                hours += (item.getHoursDay2() == null ? 0 : item.getHoursDay2());
            }
            tempCal.add(Calendar.DAY_OF_MONTH, 1);

            if ((since == null || until == null) || DateUtil.between(since, until, tempCal.getTime())) {
                hours += (item.getHoursDay3() == null ? 0 : item.getHoursDay3());
            }
            tempCal.add(Calendar.DAY_OF_MONTH, 1);
            if ((since == null || until == null) || DateUtil.between(since, until, tempCal.getTime())) {
                hours += (item.getHoursDay4() == null ? 0 : item.getHoursDay4());
            }
            tempCal.add(Calendar.DAY_OF_MONTH, 1);
            if ((since == null || until == null) || DateUtil.between(since, until, tempCal.getTime())) {
                hours += (item.getHoursDay5() == null ? 0 : item.getHoursDay5());
            }
            tempCal.add(Calendar.DAY_OF_MONTH, 1);
            if ((since == null || until == null) || DateUtil.between(since, until, tempCal.getTime())) {
                hours += (item.getHoursDay6() == null ? 0 : item.getHoursDay6());
            }
            tempCal.add(Calendar.DAY_OF_MONTH, 1);
            if ((since == null || until == null) || DateUtil.between(since, until, tempCal.getTime())) {
                hours += (item.getHoursDay7() == null ? 0 : item.getHoursDay7());
            }
        }
        return hours;
    }

    @SuppressWarnings("unchecked")
    public double getHoursInProject(Project project, Date since, Date until) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3));

        crit.createCriteria(Timesheet.PROJECTACTIVITY).add(Restrictions.eq(Projectactivity.PROJECT, project));

        return calcHours(crit.list(), since, until);
    }

    @SuppressWarnings("unchecked")
    public double getHoursInActivity(Projectactivity activity, Date since, Date until) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3));

        crit.add(Restrictions.eq(Timesheet.PROJECTACTIVITY, activity));

        return calcHours(crit.list(), since, until);
    }

    /**
     * Calculate Fte for inputed hours in project
     * @param project
     * @param member
     * @param firstWeekDay
     * @param lastWeekDay
     * @return
     */
    public double getHoursResource(Project project, Teammember member, Date firstWeekDay, Date lastWeekDay) {

        ProjectionList proList = Projections.projectionList();
        proList.add(Projections.sum(Timesheet.HOURSDAY1));
        proList.add(Projections.sum(Timesheet.HOURSDAY2));
        proList.add(Projections.sum(Timesheet.HOURSDAY3));
        proList.add(Projections.sum(Timesheet.HOURSDAY4));
        proList.add(Projections.sum(Timesheet.HOURSDAY5));
        proList.add(Projections.sum(Timesheet.HOURSDAY6));
        proList.add(Projections.sum(Timesheet.HOURSDAY7));

        Criteria crit = getSession().createCriteria(getPersistentClass()).setProjection(proList)
                .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3));

        crit.createCriteria(Timesheet.EMPLOYEE).createCriteria(Employee.TEAMMEMBERS)
                .add(Restrictions.idEq(member.getIdTeamMember()));

        if (firstWeekDay != null && lastWeekDay != null) {

            crit.add(Restrictions.eq(Timesheet.INITDATE, firstWeekDay))
                    .add(Restrictions.eq(Timesheet.ENDDATE, lastWeekDay));
        }

        if (project == null) {
            crit.add(Restrictions.eq(Timesheet.PROJECTACTIVITY, member.getProjectactivity()));
        } else {

            crit.createCriteria(Timesheet.PROJECTACTIVITY).add(Restrictions.eq(Projectactivity.PROJECT, project));
        }

        Object[] hoursList = (Object[]) crit.uniqueResult();

        double hours = 0;

        if (hoursList != null) {
            hours += (hoursList[0] == null ? 0 : (Double) hoursList[0]);
            hours += (hoursList[1] == null ? 0 : (Double) hoursList[1]);
            hours += (hoursList[2] == null ? 0 : (Double) hoursList[2]);
            hours += (hoursList[3] == null ? 0 : (Double) hoursList[3]);
            hours += (hoursList[4] == null ? 0 : (Double) hoursList[4]);
            hours += (hoursList[5] == null ? 0 : (Double) hoursList[5]);
            hours += (hoursList[6] == null ? 0 : (Double) hoursList[6]);
        }

        return hours;
    }

    /**
     * Find time sheet
     * @param employee
     * @param initDate
     * @param endDate
     * @param activity
     * @param status
     * @return
     */
    public Timesheet findByResource(Employee employee, Date initDate, Date endDate, Projectactivity activity,
            String status) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .add(Restrictions.eq(Timesheet.PROJECTACTIVITY, activity))
                .add(Restrictions.eq(Timesheet.EMPLOYEE, employee))
                .add(Restrictions.eq(Timesheet.INITDATE, initDate)).add(Restrictions.eq(Timesheet.ENDDATE, endDate))
                .add(Restrictions.eq(Timesheet.STATUS, status));

        return (Timesheet) crit.uniqueResult();
    }

    /**
     * 
     * @param employee
     * @param initDate
     * @param endDate
     * @param joins
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Timesheet> findByProject(Employee employee, Project project, Date initDate, Date endDate,
            List<String> joins) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                .add(Restrictions.eq(Timesheet.EMPLOYEE, employee))
                .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3))
                .add(Restrictions.ge(Timesheet.INITDATE, initDate)).add(Restrictions.le(Timesheet.ENDDATE, endDate))
                .addOrder(Order.asc(Timesheet.INITDATE));

        crit.createCriteria(Timesheet.PROJECTACTIVITY).add(Restrictions.eq(Projectactivity.PROJECT, project))
                .addOrder(Order.asc(Projectactivity.PROJECT));

        if (joins != null) {
            addJoins(crit, joins);
        }

        return crit.list();
    }

    /**
     * Hours by resource in dates in status APP3
     * 
     * @param since
     * @param until
     * @param activities
     * @param employee
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Object[]> getHoursResourceInDates(Date since, Date until, List<Projectactivity> activities,
            Employee employee) {

        // Declare response
        List<Object[]> result = null;

        if (ValidateUtil.isNotNull(activities)) {

            Query query = getSession().createQuery("select project.idProject, activity.idActivity,  " + "SUM("
                    + "(case when (timesheet.hoursDay1 is not null and TO_DAYS(timesheet.initDate) >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) <= TO_DAYS(:until)) THEN timesheet.hoursDay1 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay2 is not null and TO_DAYS(timesheet.initDate) + 1 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 1 <= TO_DAYS(:until)) THEN timesheet.hoursDay2 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay3 is not null and TO_DAYS(timesheet.initDate) + 2 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 2 <= TO_DAYS(:until)) THEN timesheet.hoursDay3 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay4 is not null and TO_DAYS(timesheet.initDate) + 3 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 3 <= TO_DAYS(:until)) THEN timesheet.hoursDay4 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay5 is not null and TO_DAYS(timesheet.initDate) + 4 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 4 <= TO_DAYS(:until)) THEN timesheet.hoursDay5 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay6 is not null and TO_DAYS(timesheet.initDate) + 5 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 5 <= TO_DAYS(:until)) THEN timesheet.hoursDay6 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay7 is not null and TO_DAYS(timesheet.initDate) + 6 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 6 <= TO_DAYS(:until)) THEN timesheet.hoursDay7 ELSE 0 end )"
                    + ") " + "from Projectactivity activity " + "join activity.project as project "
                    + "join activity.wbsnode as wbsnode " + "left join activity.timesheets as timesheet "
                    + "left join timesheet.employee as employee " + "left join employee.contact as contact "
                    + "where activity in(:activities) " + "and employee.idEmployee = :idEmployee "
                    + "and timesheet.status = :app3 " + "group by project.idProject, activity.idActivity "
                    + "order by project.accountingCode asc, activity.idActivity asc");

            query.setParameterList("activities", activities);
            query.setInteger("idEmployee", employee.getIdEmployee());
            query.setDate("since", since);
            query.setDate("until", until);
            query.setString("app3", Constants.TIMESTATUS_APP3);

            result = query.list();
        } else {

            // Initialize list
            result = new ArrayList<Object[]>();
        }

        return result;
    }

    /**
     * Hours by resource in dates in status APP2 or APP3
     * 
     * @param since
     * @param until
     * @param operations
     * @param employee
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Object[]> getHoursResourceInDatesOperation(Date since, Date until, List<Operation> operations,
            Employee employee) {

        // Declare response
        List<Object[]> result = null;

        if (ValidateUtil.isNotNull(operations)) {

            Query query = getSession().createQuery("select operation.idOperation,  " + "SUM("
                    + "(case when (timesheet.hoursDay1 is not null and TO_DAYS(timesheet.initDate) >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) <= TO_DAYS(:until)) THEN timesheet.hoursDay1 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay2 is not null and TO_DAYS(timesheet.initDate) + 1 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 1 <= TO_DAYS(:until)) THEN timesheet.hoursDay2 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay3 is not null and TO_DAYS(timesheet.initDate) + 2 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 2 <= TO_DAYS(:until)) THEN timesheet.hoursDay3 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay4 is not null and TO_DAYS(timesheet.initDate) + 3 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 3 <= TO_DAYS(:until)) THEN timesheet.hoursDay4 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay5 is not null and TO_DAYS(timesheet.initDate) + 4 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 4 <= TO_DAYS(:until)) THEN timesheet.hoursDay5 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay6 is not null and TO_DAYS(timesheet.initDate) + 5 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 5 <= TO_DAYS(:until)) THEN timesheet.hoursDay6 ELSE 0 end ) + "
                    + "(case when (timesheet.hoursDay7 is not null and TO_DAYS(timesheet.initDate) + 6 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 6 <= TO_DAYS(:until)) THEN timesheet.hoursDay7 ELSE 0 end )"
                    + ") " + "from Timesheet timesheet " + "join timesheet.operation as operation "
                    + "join timesheet.employee as employee " + "join employee.contact as contact "
                    + "where operation in(:operations) " + "and employee.idEmployee = :idEmployee "
                    + "and (timesheet.status = :app2 or timesheet.status = :app3) "
                    + "group by operation.idOperation "
                    + "order by operation.operationName asc, operation.idOperation asc");

            query.setParameterList("operations", operations);
            query.setInteger("idEmployee", employee.getIdEmployee());
            query.setDate("since", since);
            query.setDate("until", until);
            query.setString("app2", Constants.TIMESTATUS_APP2);
            query.setString("app3", Constants.TIMESTATUS_APP3);

            result = query.list();
        } else {

            // Initialize list
            result = new ArrayList<Object[]>();
        }

        return result;
    }

    /**
     * The node has hours in APP1, APP2 or APP3
     * 
     * @param wbsNode
     * @return
     */
    public boolean isInputedApprovedHours(Wbsnode wbsNode) {

        Criteria crit = getSession().createCriteria(getPersistentClass()).setProjection(Projections.rowCount())
                .add(Restrictions.disjunction().add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP1))
                        .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP2))
                        .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP3)))
                .createCriteria(Timesheet.PROJECTACTIVITY).add(Restrictions.eq(Projectactivity.WBSNODE, wbsNode));

        return (Integer) crit.uniqueResult() > 0;
    }

    /**
     * The node has hours in APP0
     * 
     * @param wbsNode
     * @return
     */
    public boolean isInputedHours(Wbsnode wbsNode) {

        Criteria crit = getSession().createCriteria(getPersistentClass()).setProjection(Projections.rowCount())
                .add(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP0))
                .createCriteria(Timesheet.PROJECTACTIVITY).add(Restrictions.eq(Projectactivity.WBSNODE, wbsNode));

        return (Integer) crit.uniqueResult() > 0;
    }

    /**
     * Calc hours to the activiy for status
     * @param idActivity
     * @param timestatus
     * @return
     */
    public double calcHoursActivityForStatus(Integer idActivity, String timestatus) {

        Double hours = 0.0;
        String hoursDay = Timesheet.HOURSDAY1;

        hoursDay = hoursDay.substring(0, hoursDay.length() - 1);

        for (int i = 1; i < 8; i++) {

            Criteria crit = getSession().createCriteria(getPersistentClass())
                    .setProjection(Projections.sum(hoursDay + i)).add(Restrictions.eq(Timesheet.STATUS, timestatus))
                    .add(Restrictions.eq(Timesheet.PROJECTACTIVITY, new Projectactivity(idActivity)));

            hours += (Double) crit.uniqueResult() == null ? 0.0 : (Double) crit.uniqueResult();
        }

        return hours;
    }

    /**
     * Search timesheet by filters
     * @param list
     * @param properyOrder
     * @param typeOrder
     * @param joins 
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Timesheet> findByFilters(ArrayList<PropertyRelation> list, String propertyOrder, String typeOrder,
            List<String> joins) {

        Criteria crit = getSession().createCriteria(getPersistentClass());

        if (joins != null) {
            for (String join : joins) {
                crit.setFetchMode(join, FetchMode.JOIN);
            }
        }

        for (PropertyRelation propertyRelation : list) {
            crit.add(findRestriction(propertyRelation));
        }

        addOrder(crit, propertyOrder, typeOrder);

        return crit.list();
    }

    /**
     * Find restriction type
     * @param propertyRelation
     * @return
     */
    @SuppressWarnings("unchecked")
    private Criterion findRestriction(PropertyRelation propertyRelation) {

        Criterion criterion = null;

        if (propertyRelation.getRestriction() == Constants.EQUAL_RESTRICTION) {
            criterion = Restrictions.eq(propertyRelation.getProperty(), propertyRelation.getRelation());
        } else if (propertyRelation.getRestriction() == Constants.NOT_EQUAL_RESTRICTION) {
            criterion = Restrictions.ne(propertyRelation.getProperty(), propertyRelation.getRelation());
        } else if (propertyRelation.getRestriction() == Constants.GREATER_OR_EQUAL_RESTRICTION) {
            criterion = Restrictions.ge(propertyRelation.getProperty(), propertyRelation.getRelation());
        } else if (propertyRelation.getRestriction() == Constants.LESS_OR_EQUAL_RESTRICTION) {
            criterion = Restrictions.le(propertyRelation.getProperty(), propertyRelation.getRelation());
        } else if (propertyRelation.getRestriction() == Constants.ILIKE_RESTRICTION) {
            criterion = Restrictions.ilike(propertyRelation.getProperty(), propertyRelation.getRelation());
        } else if (propertyRelation.getRestriction() == Constants.DISJUNCTION) {
            ArrayList<PropertyRelation> listDisjunctions = (ArrayList<PropertyRelation>) propertyRelation
                    .getRelation();

            Disjunction d = Restrictions.disjunction();

            for (PropertyRelation propertyRelationDis : listDisjunctions) {
                d.add(findRestriction(propertyRelationDis));
            }

            criterion = d;
        }

        return criterion;
    }

    /**
     * Find time sheet affected for update date out
     * 
     * @param teammember
     * @param newDateOut
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Timesheet> timeSheetsForUpdateOut(Teammember teammember, Date newDateOut) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .add(Restrictions.eq(Timesheet.PROJECTACTIVITY, teammember.getProjectactivity()))
                .add(Restrictions.eq(Timesheet.EMPLOYEE, teammember.getEmployee()))
                .add(Restrictions.conjunction()
                        .add(Restrictions.le(Timesheet.INITDATE, DateUtil.getFirstWeekDay(teammember.getDateOut())))
                        .add(Restrictions.ge(Timesheet.ENDDATE, DateUtil.getLastWeekDay(newDateOut))));

        return crit.list();
    }

    /**
     * Find time sheet affected for update date in
     * 
     * @param teammember
     * @param newDateOut
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Timesheet> timeSheetsForUpdateIn(Teammember teammember, Date newDateIn) {

        Criteria crit = getSession().createCriteria(getPersistentClass())
                .add(Restrictions.eq(Timesheet.PROJECTACTIVITY, teammember.getProjectactivity()))
                .add(Restrictions.eq(Timesheet.EMPLOYEE, teammember.getEmployee()))
                .add(Restrictions.conjunction()
                        .add(Restrictions.ge(Timesheet.INITDATE, DateUtil.getFirstWeekDay(teammember.getDateIn())))
                        .add(Restrictions.le(Timesheet.ENDDATE, DateUtil.getLastWeekDay(newDateIn))));

        return crit.list();
    }

    /**
     * Get timesheets in app3 by resources and dates
     * 
     * @param resources
     * @param since
     * @param until
     * @param showOperations
     * @param idProjects
     * @param user
      * @param idPMs
      * @return
     */
    @SuppressWarnings("unchecked")
    public List<ResourceTimeWrap> capacityRunning(List<Employee> resources, Date since, Date until,
            Boolean showOperations, Integer[] idProjects, Employee user, Integer[] idPMs) {

        List<ResourceTimeWrap> operations = new ArrayList<ResourceTimeWrap>();

        String where = "";
        String groupBy = "group by ";
        String orderBy = "order by ";

        String q = "SELECT NEW es.sm2.openppm.core.model.wrap.ResourceTimeWrap(" + "e.idEmployee, " + "c.fullName, "
                + "ts.initDate, " + "ts.endDate, "
                + "SUM(coalesce(ts.hoursDay1, 0) + coalesce(ts.hoursDay2, 0) + coalesce(ts.hoursDay3, 0) + coalesce(ts.hoursDay4, 0) + "
                + "coalesce(ts.hoursDay5, 0) + coalesce(ts.hoursDay6, 0) + coalesce(ts.hoursDay7, 0))" + ") "
                + "FROM Timesheet ts " + "LEFT JOIN ts.projectactivity pa " + "LEFT JOIN pa.project p "
                + "JOIN ts.employee e " + "JOIN e.contact c " + "LEFT JOIN p.employeeByProjectManager pm ";

        // Names where
        //
        where += FilterUtil.addFilterAnd(where, "ts.status = :status ");

        where += FilterUtil.addFilterAnd(where, "ts.projectactivity is not null ");

        if (ValidateUtil.isNotNull(idProjects)) {
            where += FilterUtil.addFilterAnd(where, "p.idProject IN (:idProjects) ");
        }
        if (ValidateUtil.isNotNull(resources)) {
            where += FilterUtil.addFilterAnd(where, "ts.employee IN (:resources) ");
        }
        if (since != null) {
            where += FilterUtil.addFilterAnd(where, "ts.initDate >= :initDate ");
        }
        if (until != null) {
            where += FilterUtil.addFilterAnd(where, "ts.initDate <= :endDate ");
        }

        // PM Projects
        if (user.getResourceprofiles().getIdProfile() == Constants.ROLE_PM) {
            where += FilterUtil.addFilterAnd(where, "pm.idEmployee = :idPM ");
        }
        //TODO no se si el filtro esta bien o deberia ser asi, lo comento para dejarlo como estaba
        //        else if (ValidateUtil.isNotNull(idPMs)) {
        //            where += FilterUtil.addFilterAnd(where, "pm.idEmployee IN (:idPMs) ");
        //        }

        // Group by
        groupBy += "e.idEmployee, c.fullName, ts.initDate, ts.endDate ";

        // Order by
        orderBy += "c.fullName, ts.initDate";

        // Query
        Query query = getSession().createQuery(q + where + groupBy + orderBy);

        // Values where
        //
        query.setString("status", Constants.TIMESTATUS_APP3);

        if (ValidateUtil.isNotNull(resources)) {
            query.setParameterList("resources", resources);
        }

        if (since != null) {
            query.setDate("initDate", since);
        }
        if (until != null) {
            query.setDate("endDate", until);
        }

        if (ValidateUtil.isNotNull(idProjects)) {
            query.setParameterList("idProjects", idProjects);
        }

        // PM Projects
        if (user.getResourceprofiles().getIdProfile() == Constants.ROLE_PM) {
            query.setParameter("idPM", user.getIdEmployee());
        }
        //TODO si hay un recurso que tiene proyectos con dos PMs saldran los datos de los dos proyectos aunque se filtre por uno
        //        else if (ValidateUtil.isNotNull(idPMs)) {
        //            query.setParameterList("idPMs", idPMs);
        //        }

        List<ResourceTimeWrap> activities = query.list();

        // Show operations
        if (showOperations != null && showOperations) {

            // Select for operations
            //
            where = "";
            groupBy = "group by ";
            orderBy = "order by ";

            q = "SELECT NEW es.sm2.openppm.core.model.wrap.ResourceTimeWrap(" + "op.operationName, "
                    + "ts.initDate, " + "ts.endDate, " + "e.idEmployee, "
                    + "SUM(coalesce(ts.hoursDay1, 0) + coalesce(ts.hoursDay2, 0) + coalesce(ts.hoursDay3, 0) + coalesce(ts.hoursDay4, 0) + "
                    + "coalesce(ts.hoursDay5, 0) + coalesce(ts.hoursDay6, 0) + coalesce(ts.hoursDay7, 0))" + ") "
                    + "FROM Timesheet ts " + "JOIN ts.operation op " + "JOIN ts.employee e ";

            // Names where
            where += FilterUtil.addFilterAnd(where, "ts.status = :status ");

            if (ValidateUtil.isNotNull(resources)) {
                where += FilterUtil.addFilterAnd(where, "ts.employee IN (:resources) ");
            }
            if (since != null) {
                where += FilterUtil.addFilterAnd(where, "ts.initDate >= :initDate ");
            }
            if (until != null) {
                where += FilterUtil.addFilterAnd(where, "ts.initDate <= :endDate ");
            }

            // Group by
            groupBy += "ts.initDate, ts.endDate, op.operationName, e.idEmployee ";

            // Order by
            orderBy += "ts.initDate";

            // Query
            query = getSession().createQuery(q + where + groupBy + orderBy);

            // Values where
            query.setString("status", Constants.TIMESTATUS_APP3);

            if (ValidateUtil.isNotNull(resources)) {
                query.setParameterList("resources", resources);
            }
            if (since != null) {
                query.setDate("initDate", since);
            }
            if (until != null) {
                query.setDate("endDate", until);
            }

            operations = query.list();
        }

        // Concatenate selects
        //
        List<ResourceTimeWrap> activitiesAndOperations = new ArrayList<ResourceTimeWrap>();

        activitiesAndOperations.addAll(activities);
        activitiesAndOperations.addAll(operations);

        return activitiesAndOperations;
    }

    /**
     * Select capacity running resource by project
     * 
     * @param employee
     * @param since
     * @param until
     * @param user
      * @param settings
      * @return
     */
    @SuppressWarnings("unchecked")
    public List<ResourceTimeWrap> capacityRunningResourceByProject(Employee employee, Date since, Date until,
            Employee user, HashMap<String, String> settings) {

        // Select for activities
        //
        String where = "";
        String groupBy = "group by ";
        String orderBy = "order by ";

        String q = "SELECT NEW es.sm2.openppm.core.model.wrap.ResourceTimeWrap(" + "e.idEmployee, "
                + "p.projectName, " + "pmContact.fullName, " + "ts.initDate, " + "ts.endDate, "
                + "SUM(coalesce(ts.hoursDay1, 0) + coalesce(ts.hoursDay2, 0) + coalesce(ts.hoursDay3, 0) + coalesce(ts.hoursDay4, 0) + "
                + "coalesce(ts.hoursDay5, 0) + coalesce(ts.hoursDay6, 0) + coalesce(ts.hoursDay7, 0))" + ") "
                + "FROM Timesheet ts " + "JOIN ts.projectactivity pa " + "JOIN pa.project p "
                + "JOIN ts.employee e " + "LEFT JOIN p.employeeByProjectManager pm "
                + "LEFT JOIN pm.contact pmContact ";

        // Names where
        where += FilterUtil.addFilterAnd(where, "ts.status = :status ");

        if (employee != null) {
            where += FilterUtil.addFilterAnd(where, "ts.employee = :employee ");
        }
        if (since != null) {
            where += FilterUtil.addFilterAnd(where, "ts.initDate >= :initDate ");
        }
        if (until != null) {
            where += FilterUtil.addFilterAnd(where, "ts.initDate <= :endDate ");
        }

        // PM Projects
        if (!SettingUtil.getBoolean(settings, GeneralSetting.PM_VIEW_OTHER_PROJECTS)
                && user.getResourceprofiles().getIdProfile() == Constants.ROLE_PM) {

            where += FilterUtil.addFilterAnd(where, "pm.idEmployee = :idPM ");
        }

        // Group by
        groupBy += "e.idEmployee, p.projectName, pmContact.fullName, ts.initDate, ts.endDate ";

        // Order by
        orderBy += "p.projectName, ts.initDate";

        // Query
        Query query = getSession().createQuery(q + where + groupBy + orderBy);

        // Values where
        query.setString("status", Constants.TIMESTATUS_APP3);
        if (employee != null) {
            query.setParameter("employee", employee);
        }
        if (since != null) {
            query.setDate("initDate", since);
        }
        if (until != null) {
            query.setDate("endDate", until);
        }

        // PM Projects
        if (!SettingUtil.getBoolean(settings, GeneralSetting.PM_VIEW_OTHER_PROJECTS)
                && user.getResourceprofiles().getIdProfile() == Constants.ROLE_PM) {

            query.setParameter("idPM", user.getIdEmployee());
        }

        List<ResourceTimeWrap> activities = query.list();

        // Select for operations
        //
        where = "";
        groupBy = "group by ";
        orderBy = "order by ";

        q = "SELECT NEW es.sm2.openppm.core.model.wrap.ResourceTimeWrap(" + "op.operationName, " + "ts.initDate, "
                + "ts.endDate, "
                + "SUM(coalesce(ts.hoursDay1, 0) + coalesce(ts.hoursDay2, 0) + coalesce(ts.hoursDay3, 0) + coalesce(ts.hoursDay4, 0) + "
                + "coalesce(ts.hoursDay5, 0) + coalesce(ts.hoursDay6, 0) + coalesce(ts.hoursDay7, 0))" + ") "
                + "FROM Timesheet ts " + "JOIN ts.operation op ";

        // Names where
        where += FilterUtil.addFilterAnd(where, "ts.status = :status ");

        if (employee != null) {
            where += FilterUtil.addFilterAnd(where, "ts.employee = :employee ");
        }
        if (since != null) {
            where += FilterUtil.addFilterAnd(where, "ts.initDate >= :initDate ");
        }
        if (until != null) {
            where += FilterUtil.addFilterAnd(where, "ts.initDate <= :endDate ");
        }

        // Group by
        groupBy += "ts.initDate, ts.endDate, op.operationName ";

        // Order by
        orderBy += "ts.initDate";

        // Query
        query = getSession().createQuery(q + where + groupBy + orderBy);

        // Values where
        query.setString("status", Constants.TIMESTATUS_APP3);

        if (employee != null) {
            query.setParameter("employee", employee);
        }
        if (since != null) {
            query.setDate("initDate", since);
        }
        if (until != null) {
            query.setDate("endDate", until);
        }

        List<ResourceTimeWrap> operations = query.list();

        // Concatenate selects
        //
        List<ResourceTimeWrap> activitiesAndOperations = new ArrayList<ResourceTimeWrap>();

        activitiesAndOperations.addAll(activities);
        activitiesAndOperations.addAll(operations);

        return activitiesAndOperations;
    }

    /**
     * Select capacity running resource by job category
     * 
     * @param employee
     * @param since
     * @param until
     * @param user
      * @param settings
      * @return
     */
    @SuppressWarnings("unchecked")
    public List<ResourceTimeWrap> capacityRunningResourceByJobCategory(Employee employee, Date since, Date until,
            Employee user, HashMap<String, String> settings) {

        // Select for activities 
        //
        String where = "";
        String groupBy = "group by ";
        String orderBy = "order by ";

        String q = "SELECT NEW es.sm2.openppm.core.model.wrap.ResourceTimeWrap(" + "jc.name, " + "p.projectName, "
                + "ts.initDate, " + "ts.endDate, "
                + "SUM(coalesce(ts.hoursDay1, 0) + coalesce(ts.hoursDay2, 0) + coalesce(ts.hoursDay3, 0) + coalesce(ts.hoursDay4, 0) + "
                + "coalesce(ts.hoursDay5, 0) + coalesce(ts.hoursDay6, 0) + coalesce(ts.hoursDay7, 0))" + ") "
                + "FROM Timesheet ts " + "JOIN ts.projectactivity pa " + "JOIN pa.project p "
                + "LEFT JOIN p.employeeByProjectManager pm " + "JOIN ts.employee e " + "JOIN e.teammembers tm "
                + "JOIN tm.projectactivity patm " + "JOIN tm.jobcategory jc ";

        // Names where
        where += FilterUtil.addFilterAnd(where, "ts.status = :status ");

        where += FilterUtil.addFilterAnd(where, "pa.idActivity = patm.idActivity ");

        if (employee != null) {
            where += FilterUtil.addFilterAnd(where, "ts.employee = :employee ");
        }

        if (since != null) {

            where += FilterUtil.addFilterAnd(where, "ts.initDate >= :initDate ");

            where += FilterUtil.addFilterAnd(where, "tm.dateIn <= :endDate ");
        }

        if (until != null) {

            where += FilterUtil.addFilterAnd(where, "ts.initDate <= :endDate ");

            where += FilterUtil.addFilterAnd(where, "tm.dateOut >= :initDate ");
        }

        // PM Projects
        if (!SettingUtil.getBoolean(settings, GeneralSetting.PM_VIEW_OTHER_PROJECTS)
                && user.getResourceprofiles().getIdProfile() == Constants.ROLE_PM) {

            where += FilterUtil.addFilterAnd(where, "pm.idEmployee = :idPM ");
        }

        // Group by
        groupBy += "jc.name, p.projectName, ts.initDate, ts.endDate ";

        // Order by
        orderBy += "jc.name, ts.initDate";

        // Query
        Query query = getSession().createQuery(q + where + groupBy + orderBy);

        // Values where
        query.setString("status", Constants.TIMESTATUS_APP3);

        if (employee != null) {
            query.setParameter("employee", employee);
        }
        if (since != null) {
            query.setDate("initDate", since);
        }
        if (until != null) {
            query.setDate("endDate", until);
        }

        // PM Projects
        if (!SettingUtil.getBoolean(settings, GeneralSetting.PM_VIEW_OTHER_PROJECTS)
                && user.getResourceprofiles().getIdProfile() == Constants.ROLE_PM) {

            query.setParameter("idPM", user.getIdEmployee());
        }

        List<ResourceTimeWrap> activities = query.list();

        // Select for operations 
        //
        where = "";
        groupBy = "group by ";
        orderBy = "order by ";

        q = "SELECT NEW es.sm2.openppm.core.model.wrap.ResourceTimeWrap(" + "op.operationName, " + "ts.initDate, "
                + "ts.endDate, "
                + "SUM(coalesce(ts.hoursDay1, 0) + coalesce(ts.hoursDay2, 0) + coalesce(ts.hoursDay3, 0) + coalesce(ts.hoursDay4, 0) + "
                + "coalesce(ts.hoursDay5, 0) + coalesce(ts.hoursDay6, 0) + coalesce(ts.hoursDay7, 0))" + ") "
                + "FROM Timesheet ts " + "JOIN ts.operation op ";

        // Names where
        where += FilterUtil.addFilterAnd(where, "ts.status = :status ");

        if (employee != null) {
            where += FilterUtil.addFilterAnd(where, "ts.employee = :employee ");
        }
        if (since != null) {
            where += FilterUtil.addFilterAnd(where, "ts.initDate >= :initDate ");
        }
        if (until != null) {
            where += FilterUtil.addFilterAnd(where, "ts.initDate <= :endDate ");
        }

        // Group by
        groupBy += "ts.initDate, ts.endDate, op.operationName ";

        // Order by
        orderBy += "ts.initDate";

        // Query
        query = getSession().createQuery(q + where + groupBy + orderBy);

        // Values where
        query.setString("status", Constants.TIMESTATUS_APP3);

        if (employee != null) {
            query.setParameter("employee", employee);
        }
        if (since != null) {
            query.setDate("initDate", since);
        }
        if (until != null) {
            query.setDate("endDate", until);
        }

        List<ResourceTimeWrap> operations = query.list();

        // Concatenate selects 
        //
        List<ResourceTimeWrap> activitiesAndOperations = new ArrayList<ResourceTimeWrap>();

        activitiesAndOperations.addAll(activities);
        activitiesAndOperations.addAll(operations);

        return activitiesAndOperations;
    }

    /**
     * Check for hours pending approval - APP1 or APP2
     * 
     * @param project
     * @return
     */
    public boolean pendingApproval(Project project) {

        Criteria crit = getSession().createCriteria(getPersistentClass()).setProjection(Projections.rowCount())
                .add(Restrictions.or(Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP1),
                        Restrictions.eq(Timesheet.STATUS, Constants.TIMESTATUS_APP2)));

        // Project restriction
        crit.createCriteria(Timesheet.PROJECTACTIVITY).add(Restrictions.eq(Projectactivity.PROJECT, project));

        Integer count = (Integer) crit.uniqueResult();

        return (count != null && count > 0);
    }

    /**
     * Returns employee with all level (app) hours.
     * 
     * @param initDate
     * @param endDate
     * @param user
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<ApprovalWrap> findTimesheetsAllApp(Date initDate, Date endDate, Employee user) {

        String activity = "(SELECT coalesce(SUM(" + "coalesce(tse.hoursDay1, 0D) + "
                + "coalesce(tse.hoursDay2, 0D) + " + "coalesce(tse.hoursDay3, 0D) + "
                + "coalesce(tse.hoursDay4, 0D) + " + "coalesce(tse.hoursDay5, 0D) + "
                + "coalesce(tse.hoursDay6, 0D) + " + "coalesce(tse.hoursDay7, 0D)), 0D) " + "FROM Timesheet tse "
                + "JOIN tse.projectactivity paSub " + "JOIN paSub.project pSub " + "WHERE tse.employee = e "
                + "AND tse.projectactivity is not null " + "AND tse.initDate = :initDate "
                + "AND tse.endDate= :endDate "
                + "AND ((pSub.status != :statusClosed AND pSub.status != :statusArchived) "
                + "OR ((pSub.status = :statusClosed OR pSub.status = :statusArchived) AND tse.status != :app0))) ";

        String operation = "(SELECT coalesce(SUM(" + "coalesce(tseo.hoursDay1, 0D) + "
                + "coalesce(tseo.hoursDay2, 0D) + " + "coalesce(tseo.hoursDay3, 0D) + "
                + "coalesce(tseo.hoursDay4, 0D) + " + "coalesce(tseo.hoursDay5, 0D) + "
                + "coalesce(tseo.hoursDay6, 0D) + " + "coalesce(tseo.hoursDay7, 0D)), 0D) " + "FROM Timesheet tseo "
                + "WHERE tseo.employee = e " + "AND tseo.operation is not null " + "AND tseo.initDate = :initDate "
                + "AND tseo.endDate = :endDate) ";

        String suggestReject = "(SELECT count(tss.suggestReject) " + " FROM Timesheet tss "
                + " WHERE tss.initDate = :initDate " + " AND tss.suggestReject IS TRUE "
                + " AND tss.employee = e) ";

        String q = "SELECT NEW es.sm2.openppm.core.model.wrap.ApprovalWrap( " + "e.idEmployee, " + "c.fullName,"
                + "rp.name," + "coalesce(s.name,'')," + getHours(Constants.TIMESTATUS_APP0) + ", "
                + getHours(Constants.TIMESTATUS_APP1) + ", " + getHours(Constants.TIMESTATUS_APP2) + ", "
                + getHours(Constants.TIMESTATUS_APP3) + ", " + activity + ", " + operation + ", " + suggestReject
                + " ) " + "FROM Employee e " + "JOIN e.timesheets ts " + "JOIN e.contact c "
                + "JOIN e.resourcepool rp " + "LEFT JOIN e.seller s " + "WHERE ts.initDate = :initDate "
                + "AND ts.endDate = :endDate " + "AND e.performingorg = :performingOrg "
                + "GROUP BY e.idEmployee ,c.fullName ";

        Query query = getSession().createQuery(q);

        query.setString(Constants.TIMESTATUS_APP0, Constants.TIMESTATUS_APP0);
        query.setString(Constants.TIMESTATUS_APP1, Constants.TIMESTATUS_APP1);
        query.setString(Constants.TIMESTATUS_APP2, Constants.TIMESTATUS_APP2);
        query.setString(Constants.TIMESTATUS_APP3, Constants.TIMESTATUS_APP3);

        // Add filters.
        query.setDate("initDate", initDate);
        query.setDate("endDate", endDate);
        query.setEntity("performingOrg", user.getPerformingorg());
        query.setString("statusClosed", Constants.STATUS_CLOSED);
        query.setString("statusArchived", Constants.STATUS_ARCHIVED);

        return query.list();
    }

    /**
     * Returns the sum of all the hours for an employee by level (app0 ... etc)
     * 
     * @param appLevel
     * @return
     */
    private String getHours(String appLevel) {

        String query = "(SELECT coalesce(SUM(" + "coalesce(tseh1.hoursDay1, 0D) + "
                + "coalesce(tseh1.hoursDay2, 0D) + " + "coalesce(tseh1.hoursDay3, 0D) + "
                + "coalesce(tseh1.hoursDay4, 0D) + " + "coalesce(tseh1.hoursDay5, 0D) + "
                + "coalesce(tseh1.hoursDay6, 0D) + " + "coalesce(tseh1.hoursDay7, 0D)), 0D) "
                + "FROM Timesheet tseh1 " + "LEFT JOIN tseh1.projectactivity paSub1 "
                + "LEFT JOIN paSub1.project pSub1 " + "LEFT JOIN tseh1.operation ope1 "
                + "WHERE tseh1.employee = e " + "AND tseh1.status = :" + appLevel + " "
                + "AND tseh1.initDate = :initDate " + "AND tseh1.endDate = :endDate ";

        if (Constants.TIMESTATUS_APP0.equals(appLevel)) {

            query += "AND ((pSub1.status != :statusClosed AND pSub1.status != :statusArchived) OR ope1 is not null) ";
        }

        query += ")";

        return query;
    }

    /**
     * Find teemsheets for an employee
     * 
     * @param initWeek
     * @param endWeek
     * @param employee
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<TimesheetWrap> findTimesheetsForEmployee(Date initWeek, Date endWeek, Employee employee) {

        String q = "SELECT DISTINCT NEW es.sm2.openppm.core.model.wrap.TimesheetWrap( " + "ts, " + "p.status,"
                + "case when pa is null then 0 else pa.idActivity end, "
                + "case when pa is null then '' else pa.activityName end, "
                + "case when o is null then '' else o.operationName end, "
                + "case when p is null then 0 else p.idProject end, "
                + "case when p is null then '' else p.projectName end, "
                + "case when p is null then '' else p.chartLabel end, "
                + "case when p is null then '' else p.status end, "
                + "case when pm is null then 0 else pm.idEmployee end, "
                + "case when c is null then '' else c.fullName end) " + "FROM Timesheet ts "
                + "LEFT JOIN ts.employee e " + "LEFT JOIN ts.projectactivity pa " + "LEFT JOIN pa.project p "
                + "LEFT JOIN p.employeeByProjectManager pm " + "LEFT JOIN pm.contact c "
                + "LEFT JOIN ts.operation o " + "WHERE ts.employee = :employee " + "AND ts.initDate = :initWeek "
                + "AND ts.endDate = :endWeek "
                + "AND (((p.status = :statusClosed OR p.status = :statusArchived) AND ts.status = :app3) "
                + "OR (p.status != :statusClosed AND p.status != :statusArchived) "
                + "OR ts.operation is not null)";

        Query query = getSession().createQuery(q);

        query.setDate("initWeek", initWeek);
        query.setDate("endWeek", endWeek);
        query.setEntity("employee", employee);
        query.setString("statusClosed", Constants.STATUS_CLOSED);
        query.setString("statusArchived", Constants.STATUS_ARCHIVED);
        query.setString("app3", Constants.TIMESTATUS_APP3);

        return query.list();
    }

    /**
     * Returns employee with all level (app) hours.
     * 
     * @param initDate
     * @param endDate
     * @param user
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<ApprovalWrap> findTimesheetsAllAppByProject(Date initDate, Date endDate, Employee user) {

        String suggestReject = "(SELECT count(tss.suggestReject) " + "FROM Timesheet tss "
                + "LEFT JOIN tss.projectactivity pjas " + "LEFT JOIN pjas.project prs "
                + "WHERE tss.initDate = :initDate " + "AND tss.suggestReject IS TRUE " + "AND tss.employee = e "
                + "AND prs = p) ";

        String q = "SELECT NEW es.sm2.openppm.core.model.wrap.ApprovalWrap( " + "e.idEmployee, " + "c.fullName, "
                + "p.idProject, " + "p.projectName, " + getHoursByProject(Constants.TIMESTATUS_APP0) + ", "
                + getHoursByProject(Constants.TIMESTATUS_APP1) + ", " + getHoursByProject(Constants.TIMESTATUS_APP2)
                + ", " + getHoursByProject(Constants.TIMESTATUS_APP3) + ", " + suggestReject + " ) "
                + "FROM Employee e " + "JOIN e.timesheets ts " + "JOIN e.contact c " + "JOIN ts.projectactivity pa "
                + "JOIN pa.project p " + "WHERE ts.initDate = :initDate " + "AND ts.endDate = :endDate "
                + "AND p.employeeByProjectManager = :pm "
                + "GROUP BY e.idEmployee , c.fullName, p.idProject, p.projectName ";

        Query query = getSession().createQuery(q);

        query.setString(Constants.TIMESTATUS_APP0, Constants.TIMESTATUS_APP0);
        query.setString(Constants.TIMESTATUS_APP1, Constants.TIMESTATUS_APP1);
        query.setString(Constants.TIMESTATUS_APP2, Constants.TIMESTATUS_APP2);
        query.setString(Constants.TIMESTATUS_APP3, Constants.TIMESTATUS_APP3);

        // Add filters.
        query.setDate("initDate", initDate);
        query.setDate("endDate", endDate);
        query.setEntity("pm", user);
        query.setString("statusClosed", Constants.STATUS_CLOSED);
        query.setString("statusArchived", Constants.STATUS_ARCHIVED);

        return query.list();
    }

    /**
     * Returns the sum of all the hours for an employee by level (app0 ... ect) and project
     * 
     * @param appLevel
     * @return
     */
    private String getHoursByProject(String appLevel) {

        String query = "(SELECT coalesce(SUM(" + "coalesce(tseh.hoursDay1, 0D) + "
                + "coalesce(tseh.hoursDay2, 0D) + " + "coalesce(tseh.hoursDay3, 0D) + "
                + "coalesce(tseh.hoursDay4, 0D) + " + "coalesce(tseh.hoursDay5, 0D) + "
                + "coalesce(tseh.hoursDay6, 0D) + " + "coalesce(tseh.hoursDay7, 0D)), 0D) " + "FROM Timesheet tseh "
                + "LEFT JOIN tseh.projectactivity pjah " + "LEFT JOIN pjah.project prh "
                + "WHERE tseh.employee = e " + "AND prh = p " + "AND tseh.status = :" + appLevel + " "
                + "AND tseh.initDate = :initDate " + "AND tseh.endDate = :endDate ";

        if (Constants.TIMESTATUS_APP0.equals(appLevel)) {

            query += "AND prh.status != :statusClosed AND prh.status != :statusArchived ";
        }

        query += ")";

        return query;
    }

    /**
     * Find teemsheets for an employee by project
     * 
     * @param initWeek
     * @param endWeek
     * @param employee
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<TimesheetWrap> findTimesheetsForEmployeeByProject(Date initWeek, Date endWeek, Employee employee,
            Project project) {

        String q = "SELECT DISTINCT NEW es.sm2.openppm.core.model.wrap.TimesheetWrap( " + "ts, " + "p.status,"
                + "case when pa is null then 0 else pa.idActivity end, "
                + "case when pa is null then '' else pa.activityName end, "
                + "case when o is null then '' else o.operationName end, "
                + "case when p is null then 0 else p.idProject end, "
                + "case when p is null then '' else p.projectName end, "
                + "case when p is null then '' else p.chartLabel end, "
                + "case when p is null then '' else p.status end, "
                + "case when pm is null then 0 else pm.idEmployee end, "
                + "case when c is null then '' else c.fullName end) " + "FROM Timesheet ts "
                + "LEFT JOIN ts.employee e " + "LEFT JOIN ts.projectactivity pa " + "LEFT JOIN pa.project p "
                + "LEFT JOIN ts.operation o " + "LEFT JOIN p.employeeByProjectManager pm "
                + "LEFT JOIN pm.contact c " + "WHERE ts.employee = :employee " + "AND ts.initDate = :initWeek "
                + "AND ts.endDate = :endWeek " + "AND (p = :project OR ts.operation is not null ) "
                + "AND ((p.status = :statusClosed OR p.status = :statusArchived) " + "AND ts.status = :appLevel "
                + "OR (p.status != :statusClosed AND p.status != :statusArchived)"
                + "OR ts.operation is not null) ";

        Query query = getSession().createQuery(q);

        query.setDate("initWeek", initWeek);
        query.setDate("endWeek", endWeek);
        query.setEntity("employee", employee);
        query.setString("statusClosed", Constants.STATUS_CLOSED);
        query.setString("statusArchived", Constants.STATUS_ARCHIVED);
        query.setString("appLevel", Constants.TIMESTATUS_APP3);
        query.setEntity("project", project);

        return query.list();
    }

    /**
     * Find imputations hours APP3
     *
     * @param employee
     * @param initDate
     * @param endDate
     * @return
     */
    public List<ImputationWrap> findImputations(Employee employee, Date initDate, Date endDate) {

        List<ImputationWrap> imputations = new ArrayList<ImputationWrap>();

        // Query project activities
        //
        String qAct = "SELECT DISTINCT NEW es.sm2.openppm.core.model.wrap.ImputationWrap( " + "p.projectName, "
                + "p.chartLabel, " + "coalesce(SUM("
                + "(case when (ts.hoursDay1 is not null and TO_DAYS(ts.initDate) >= TO_DAYS(:since) and TO_DAYS(ts.initDate) <= TO_DAYS(:until)) THEN ts.hoursDay1 ELSE 0 end ) + "
                + "(case when (ts.hoursDay2 is not null and TO_DAYS(ts.initDate) + 1 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 1 <= TO_DAYS(:until)) THEN ts.hoursDay2 ELSE 0 end ) + "
                + "(case when (ts.hoursDay3 is not null and TO_DAYS(ts.initDate) + 2 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 2 <= TO_DAYS(:until)) THEN ts.hoursDay3 ELSE 0 end ) + "
                + "(case when (ts.hoursDay4 is not null and TO_DAYS(ts.initDate) + 3 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 3 <= TO_DAYS(:until)) THEN ts.hoursDay4 ELSE 0 end ) + "
                + "(case when (ts.hoursDay5 is not null and TO_DAYS(ts.initDate) + 4 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 4 <= TO_DAYS(:until)) THEN ts.hoursDay5 ELSE 0 end ) + "
                + "(case when (ts.hoursDay6 is not null and TO_DAYS(ts.initDate) + 5 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 5 <= TO_DAYS(:until)) THEN ts.hoursDay6 ELSE 0 end ) + "
                + "(case when (ts.hoursDay7 is not null and TO_DAYS(ts.initDate) + 6 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 6 <= TO_DAYS(:until)) THEN ts.hoursDay7 ELSE 0 end )"
                + "), 0D) " + ") " + "FROM Timesheet ts " + "JOIN ts.employee e " + "JOIN ts.projectactivity pa "
                + "JOIN pa.project p " + "WHERE e.idEmployee = :idEmployee " + "AND ts.initDate >= :initDate "
                + "AND ts.endDate <= :endDate " + "AND ts.status = :status "
                + "GROUP BY p.projectName, p.chartLabel " + "ORDER BY p.projectName";

        Query query = getSession().createQuery(qAct);

        query.setString("status", Constants.TIMESTATUS_APP3);
        query.setInteger("idEmployee", employee.getIdEmployee());
        query.setDate("initDate", DateUtil.getFirstWeekDay(initDate));
        query.setDate("endDate", DateUtil.getLastWeekDay(endDate));
        query.setDate("since", initDate);
        query.setDate("until", endDate);

        List<ImputationWrap> imputationsActivity = query.list();

        // Add elements
        if (ValidateUtil.isNotNull(imputationsActivity)) {

            for (ImputationWrap imputationActivity : imputationsActivity) {

                if (imputationActivity.getHoursAPP3() != null && imputationActivity.getHoursAPP3() != 0.0) {
                    imputations.add(imputationActivity);
                }
            }
        }

        // Query operations
        //
        String qOp = "SELECT DISTINCT NEW es.sm2.openppm.core.model.wrap.ImputationWrap( " + "op.operationName, "
                + "coalesce(SUM("
                + "(case when (ts.hoursDay1 is not null and TO_DAYS(ts.initDate) >= TO_DAYS(:since) and TO_DAYS(ts.initDate) <= TO_DAYS(:until)) THEN ts.hoursDay1 ELSE 0 end ) + "
                + "(case when (ts.hoursDay2 is not null and TO_DAYS(ts.initDate) + 1 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 1 <= TO_DAYS(:until)) THEN ts.hoursDay2 ELSE 0 end ) + "
                + "(case when (ts.hoursDay3 is not null and TO_DAYS(ts.initDate) + 2 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 2 <= TO_DAYS(:until)) THEN ts.hoursDay3 ELSE 0 end ) + "
                + "(case when (ts.hoursDay4 is not null and TO_DAYS(ts.initDate) + 3 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 3 <= TO_DAYS(:until)) THEN ts.hoursDay4 ELSE 0 end ) + "
                + "(case when (ts.hoursDay5 is not null and TO_DAYS(ts.initDate) + 4 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 4 <= TO_DAYS(:until)) THEN ts.hoursDay5 ELSE 0 end ) + "
                + "(case when (ts.hoursDay6 is not null and TO_DAYS(ts.initDate) + 5 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 5 <= TO_DAYS(:until)) THEN ts.hoursDay6 ELSE 0 end ) + "
                + "(case when (ts.hoursDay7 is not null and TO_DAYS(ts.initDate) + 6 >= TO_DAYS(:since) and TO_DAYS(ts.initDate) + 6 <= TO_DAYS(:until)) THEN ts.hoursDay7 ELSE 0 end )"
                + "), 0D) " + ") " + "FROM Timesheet ts " + "JOIN ts.employee e " + "JOIN ts.operation op "
                + "WHERE e.idEmployee = :idEmployee " + "AND ts.initDate >= :initDate "
                + "AND ts.endDate <= :endDate " + "AND ts.status = :status " + "GROUP BY op.operationName "
                + "ORDER BY op.operationName";

        query = getSession().createQuery(qOp);

        query.setString("status", Constants.TIMESTATUS_APP3);
        query.setInteger("idEmployee", employee.getIdEmployee());
        query.setDate("initDate", DateUtil.getFirstWeekDay(initDate));
        query.setDate("endDate", DateUtil.getLastWeekDay(endDate));
        query.setDate("since", initDate);
        query.setDate("until", endDate);

        List<ImputationWrap> imputationsOperation = query.list();

        // Add elements
        if (ValidateUtil.isNotNull(imputationsOperation)) {

            for (ImputationWrap imputationOperation : imputationsOperation) {

                if (imputationOperation.getHoursAPP3() != null && imputationOperation.getHoursAPP3() != 0.0) {
                    imputations.add(imputationOperation);
                }
            }
        }

        return imputations;
    }
}