Example usage for org.hibernate Query setDate

Introduction

In this page you can find the example usage for org.hibernate Query setDate.

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setDate(String name, Date val) 

Source Link

Document

Bind the val (time is truncated) of a given Date object to a named query parameter.

Usage

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Hours assigned//from ww w  . ja  v a  2  s.  co  m
 * 
 * @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);
}

---

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Time Sheets in state by member/*  w w w  .  j av  a 2  s .c  om*/
 * 
 * @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();
}

---

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Hours by resource in dates in status APP3
 * //ww w.java  2  s .  c  o  m
 * @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;
}

---

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Hours by resource in dates in status APP2 or APP3
 * /*from   w  w  w. jav a  2 s . com*/
 * @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;
}

---

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Get timesheets in app3 by resources and dates
 * // w  w  w .java 2  s. com
 * @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;
}

---

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Select capacity running resource by project
 * /*from  w  ww  .jav  a  2s  . c  om*/
 * @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;
}

---

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Select capacity running resource by job category
 * /*www .  j av a2s. c o  m*/
 * @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;
}

---

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Returns employee with all level (app) hours.
 * /*from w  w  w  . j a  va  2 s.  c  o m*/
 * @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();
}

---

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Find teemsheets for an employee//  w ww. j ava  2 s  . c  o m
 * 
 * @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();
}

---

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Returns employee with all level (app) hours.
 * //w  w  w. j  a v a  2s.c  o  m
 * @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();
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• »