Example usage for org.hibernate Query setDate

List of usage examples for org.hibernate Query setDate

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. 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:de.fhdo.terminologie.ws.administration._export.ExportCSV.java

License:Apache License

public String exportCSV(ExportCodeSystemContentResponseType reponse) {
    String s = ""; // Status-Meldung
    //int count = countExported;

    CsvWriter csv;//from   w w  w . j  a  v  a2s. c o m
    ExportType exportType = new ExportType();
    paramList = new HashMap<Integer, String>();
    hb_session = HibernateUtil.getSessionFactory().openSession();
    //hb_session.getTransaction().begin();

    try {
        //TODO URL erstellen und setzen (Namenskonvention?) 
        //TODO ggf. auf bereits identischen Export-File prfen
        //String csv_output_url = "/var/lib/tomcat6/webapps/csv_test_output.csv";
        //csv = new CsvWriter(new FileWriter(csv_output_url), ';');

        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        //csv = new CsvWriter(bos, ';', Charset.forName("ISO-8859-1")); // TODO Charset prfen
        //csv.setTextQualifier('\'');
        csv = new CsvWriter(bos, PropertiesHelper.getInstance().getDelimiter(), Charset.forName("ISO-8859-1")); // TODO Charset prfen
        csv.setTextQualifier(PropertiesHelper.getInstance().getExportCsvQualifier());
        csv.setForceQualifier(PropertiesHelper.getInstance().isExportCsvForceQualifier());

        try {
            //Request-Parameter fr ReturnCodeSystemDetails erstellen
            if (logger.isInfoEnabled())
                logger.info("[ExportCSV] Erstelle Request-Parameter fr ReturnCodeSystemDetails");

            ReturnCodeSystemDetailsRequestType requestCodeSystemDetails = new ReturnCodeSystemDetailsRequestType();
            requestCodeSystemDetails.setCodeSystem(parameter.getCodeSystem());
            if (requestCodeSystemDetails.getCodeSystem() != null
                    && requestCodeSystemDetails.getCodeSystem().getCodeSystemVersions() != null)
                requestCodeSystemDetails.getCodeSystem().getCodeSystemVersions().add(
                        (CodeSystemVersion) parameter.getCodeSystem().getCodeSystemVersions().toArray()[0]);
            requestCodeSystemDetails.setLoginToken(parameter.getLoginToken());

            //CodeSystemDetails abrufen
            ReturnCodeSystemDetails rcsd = new ReturnCodeSystemDetails();
            ReturnCodeSystemDetailsResponseType responseCodeSystemDetails = rcsd
                    .ReturnCodeSystemDetails(requestCodeSystemDetails, "");
            if (logger.isInfoEnabled())
                logger.info("[ExportCSV] ReturnCodeSystemDetails abgerufen");

            /*if (parameter.getExportInfos().isUpdateCheck())
            {
              if (responseCodeSystemDetails.getReturnInfos().getStatus() == Status.OK
                      && responseCodeSystemDetails.getCodeSystem() != null)
              {
                if (!responseCodeSystemDetails.getCodeSystem().getCurrentVersionId().equals(((CodeSystemVersion) parameter.getCodeSystem().getCodeSystemVersions().toArray()[0]).getVersionId()))
                {
                  ((CodeSystemVersion) parameter.getCodeSystem().getCodeSystemVersions().toArray()[0]).setVersionId(responseCodeSystemDetails.getCodeSystem().getCurrentVersionId());
                    
                  requestCodeSystemDetails = new ReturnCodeSystemDetailsRequestType();
                  requestCodeSystemDetails.setCodeSystem(parameter.getCodeSystem());
                  requestCodeSystemDetails.getCodeSystem().getCodeSystemVersions().add((CodeSystemVersion) parameter.getCodeSystem().getCodeSystemVersions().toArray()[0]);
                  requestCodeSystemDetails.setLoginToken(parameter.getLoginToken());
                    
                  //CodeSystemDetails abrufen
                  rcsd = new ReturnCodeSystemDetails();
                  responseCodeSystemDetails = rcsd.ReturnCodeSystemDetails(requestCodeSystemDetails, "");
                  if (logger.isInfoEnabled())
                    logger.info("[ExportCSV] ReturnCodeSystemDetails abgerufen");
                }
              }
            }*/

            //Request-Parameter fr ListCodeSystemConcepts erstellen
            if (logger.isInfoEnabled())
                logger.info("[ExportCSV] Erstelle Request-Parameter fr ListCodeSystemConcepts");

            if (logger.isInfoEnabled())
                logger.info("[ExportCSV] ListCodeSystemConcepts abgerufen");

            String hql = "select distinct csv from CodeSystemVersion csv join csv.codeSystem cs"
                    + " where cs.id=:id and" + " csv.versionId=:versionId";

            org.hibernate.Query q = hb_session.createQuery(hql);
            q.setLong("id", parameter.getCodeSystem().getId());
            q.setLong("versionId",
                    parameter.getCodeSystem().getCodeSystemVersions().iterator().next().getVersionId());

            List<CodeSystemVersion> csvList = q.list();
            CodeSystemVersion csversion = null;
            if (csvList != null && csvList.size() == 1) {
                csversion = csvList.get(0);
            }

            String hqlM = "select distinct mp from MetadataParameter mp join mp.codeSystem cs"
                    + " where cs.id=:id";

            org.hibernate.Query qM = hb_session.createQuery(hqlM);
            qM.setLong("id", parameter.getCodeSystem().getId());
            List<MetadataParameter> mlist = qM.list();

            //=================================================
            // CSV-Header erstellen und Dauerattribute auslesen
            //=================================================
            csv.write("code");
            csv.write("codeSystem");
            csv.write("displayName");
            csv.write("parentCode");
            csv.write("concept_Beschreibung");
            csv.write("meaning");
            csv.write("hints");
            int count = 0;
            for (MetadataParameter mp : mlist) {
                String para = mp.getParamName();
                String b = para.substring(0, 1);
                b = b.toLowerCase();
                para = b + para.substring(1);

                csv.write(para); //lowerCase
                paramList.put(count, mp.getParamName());//Normal UpperCase
                count++;
                if (para.equals("level"))
                    levelExists = true;
            }

            if (!levelExists)
                csv.write("level");

            //ENDE Header erstellen
            csv.endRecord();

            if (logger.isInfoEnabled())
                logger.info("[ExportCSV] CSV-Header erstellt.");

            //CSV-Inhalt erstellen
            if (logger.isInfoEnabled())
                logger.info("[ExportCSV] Erstelle CSV-Inhalt mit Konzepten...");

            int countRoot = 0;

            String hqlC = "select distinct cse from CodeSystemEntity cse join cse.codeSystemVersionEntityMemberships csvem join csvem.codeSystemVersion csv join cse.codeSystemEntityVersions csev join csev.codeSystemConcepts csc"
                    + " where csv.versionId=:versionId";

            if (parameter.getExportParameter() != null
                    && parameter.getExportParameter().getDateFrom() != null) {
                // Datum fr Synchronisation hinzufgen
                hqlC += " and csev.statusVisibilityDate>:dateFrom";
            }

            org.hibernate.Query qC = hb_session.createQuery(hqlC);
            qC.setLong("versionId",
                    parameter.getCodeSystem().getCodeSystemVersions().iterator().next().getVersionId());

            if (parameter.getExportParameter() != null
                    && parameter.getExportParameter().getDateFrom() != null) {
                // Datum fr Synchronisation hinzufgen
                qC.setDate("dateFrom", parameter.getExportParameter().getDateFrom());
                logger.debug("Snych-Zeit: " + parameter.getExportParameter().getDateFrom().toString());
            } else
                logger.debug("keine Snych-Zeit angegeben");

            List<CodeSystemEntity> cselist = qC.list();
            for (CodeSystemEntity cse : cselist) {
                for (CodeSystemEntityVersion csev : cse.getCodeSystemEntityVersions()) {
                    // Nur aktuellste Version exportieren
                    if (cse.getCurrentVersionId().longValue() == csev.getVersionId().longValue()) {

                        CodeSystemConcept csc = csev.getCodeSystemConcepts().iterator().next();
                        if (csev.getStatusVisibility() == 1) {
                            if (logger.isDebugEnabled())
                                logger.debug("Schreibe Code: " + csc.getCode());
                            CodeSystemVersionEntityMembership member = cse
                                    .getCodeSystemVersionEntityMemberships().iterator().next();
                            writeEntry(csv, 0, csev, member, null, csversion);
                        }
                    }
                }
            }

            /*
            for (CodeSystemEntity cse : cselist)//responseListCodeSystemConcepts.getCodeSystemEntity())
            {
              CodeSystemVersionEntityMembership member = cse.getCodeSystemVersionEntityMemberships().iterator().next();
              boolean isAxis = false;
              boolean isMainClass = false;
              if (member.getIsAxis() != null)
                isAxis = member.getIsAxis().booleanValue();
              if (member.getIsMainClass() != null)
                isMainClass = member.getIsMainClass().booleanValue();
                    
              if (isAxis || isMainClass)
              {
                countRoot++;
                    
                for (CodeSystemEntityVersion csev : cse.getCodeSystemEntityVersions())
                {
                  // Nur aktuellste Version exportieren
                  if (cse.getCurrentVersionId().longValue() == csev.getVersionId().longValue())
                  {
                    writeEntry(csv, 0, csev, member, null, csversion);
                    
                    // TODO Beziehungen ausgeben
                    for (CodeSystemEntityVersionAssociation assChild : csev.getCodeSystemEntityVersionAssociationsForCodeSystemEntityVersionId1())
                    {
                      if (assChild.getCodeSystemEntityVersionByCodeSystemEntityVersionId2() != null)
                      {
            long childId = assChild.getCodeSystemEntityVersionByCodeSystemEntityVersionId2().getVersionId();
            exportChild(csv, 1, childId, cselist, csev, csversion);
                      }
                    }
                  }
                }
              }
            }
                    
            if (countRoot == 0)
            {
              // Flaches Vokabular, einfach alle Eintrge exportieren
              for (CodeSystemEntity cse : cselist)
              {
                for (CodeSystemEntityVersion csev : cse.getCodeSystemEntityVersions())
                {
                  // Nur aktuellste Version exportieren
                  if (cse.getCurrentVersionId().longValue() == csev.getVersionId().longValue())
                  {
                    writeEntry(csv, 0, csev, null, null, csversion);
                  }
                }
              }
            }
            */
            /*Iterator<CodeSystemEntity> it_CSE = responseListCodeSystemConcepts.getCodeSystemEntity().iterator();
                    
             while (it_CSE.hasNext())
             {
             Iterator<CodeSystemEntityVersion> it_CSEV = it_CSE.next().getCodeSystemEntityVersions().iterator();
                    
             while (it_CSEV.hasNext())
             {
             Iterator<CodeSystemConcept> it_CSC = it_CSEV.next().getCodeSystemConcepts().iterator();
                    
             while (it_CSC.hasNext())
             {
             temp_CSC = it_CSC.next();
                    
             //CSV-Zeile erstellen (CodeSystemConcept)
                    
             //CodeSystem-Name (immer)                
             csv.write(csvEntryCodeSystem);
             //CodeSystem-Beschreibung (Parameter codeSystemInfo == true)
             if (parameter.getExportParameter().getCodeSystemInfos())
             {
             csv.write(csvEntryCodeSystemDescription);
             }
             //CodeSystem-Version (immer)
             csv.write(csvEntryCodeSystemVersion);
             //CodeSystem-Version Beschreibung (Parameter codeSystemInfo == true)
             if (parameter.getExportParameter().getCodeSystemInfos())
             {
             csv.write(csvEntryCodeSystemVersionDescription);
             }
             //CodeSystem-OID (immer)
             csv.write(csvEntryCodeSystemOid);
             //CodeSystemVersion-Ablaufdatum (Parameter codeSystemInfo == true)
             if (parameter.getExportParameter().getCodeSystemInfos())
             {
             csv.write(csvEntryCodeSystemExpirationDate);
             }
                    
                    
             //Code (immer)
             csv.write(temp_CSC.getCode());
             //Term (immer)
             csv.write(temp_CSC.getTerm());
             //isPreferred (immer)
             if (temp_CSC.getIsPreferred())
             {
             csv.write("Bevorzugt");
             }
             else
             {
             csv.write("Nicht bevorzugt");
             }
                    
                    
             //bersetzungen (Parameter translations == true)
             if (parameter.getExportParameter().getTranslations())
             {
             //TODO Translations in CSV-Inhalt                 
             }
                    
             //bersetzungen (Parameter associationInfos == true)
             if (!parameter.getExportParameter().getAssociationInfos().isEmpty())
             {
             //TODO AssociationInfos CSV-Header                                
             }
                    
             count++;
             csv.endRecord();
                    
                    
             } //END CodeSystemConcept
             } //END CodeSystemEntityVersion
             } //END CodeSystemEntity
             */
            //ENDE CSV-Inhalt erstellen
            //Sort
            //Collections.sort(entryList, new AlphanumComparator());

            for (Entry e : entryList) {
                writeCsvEntry(csv, e.level, e.csev, e.csevParent, e.csv);
            }

            if (logger.isInfoEnabled())
                logger.info("[ExportCSV] CSV-Inhalt erstellt");

        } catch (Exception ex) {
            ex.printStackTrace();
            logger.error(ex.getMessage());
            s = "Fehler: " + ex.getLocalizedMessage();
        }

        //CSV-Datei schliessen
        csv.close();
        //if (logger.isInfoEnabled())
        //  logger.info("[ExportCSV] CSV-Datei geschrieben. Dateipfad: " + csv_output_url);

        if (countExported > 0) {
            //countExported = count;
            //CSV-Datei in Byte umwandeln

            /*File file = new File(csv_output_url);
             byte[] filecontent = new byte[(int) file.length()];
             FileInputStream fileInputStream = new FileInputStream(file);
             fileInputStream.read(filecontent);
             fileInputStream.close();
             if (logger.isInfoEnabled())
             logger.info("[ExportCSV] CSV-Datei in byte[] umgewandelt. (filecontent) ");*/
            //Filecontent setzen
            exportType.setFilecontent(bos.toByteArray());
            //Export-URL setzen
            //exportType.setUrl(csv_output_url);
            exportType.setUrl("");
            reponse.getReturnInfos()
                    .setMessage("Export abgeschlossen. " + countExported + " Konzepte exportiert.");
        } else {
            reponse.getReturnInfos().setMessage("Keine Konzepte exportiert.");
            if (logger.isInfoEnabled())
                logger.info("[ExportCSV] Kein Export erstellt...");
        }

        //Format-ID (CSV) setzen
        exportType.setFormatId(ExportCodeSystemContentRequestType.EXPORT_CSV_ID);

        //ExportInfos in Response schreiben
        reponse.setExportInfos(exportType);
    } catch (Exception ex) {
        s = "Fehler: " + ex.getLocalizedMessage();
        ex.printStackTrace();
    }

    //hb_session.getTransaction().commit();
    hb_session.close();
    return s;
}

From source file:edu.sjsu.cs157bproject3.App.java

public static final Query findProductWTimeInterval(Session session) throws IllegalArgumentException {

    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
    Date dateX = new Date();
    Date dateY = new Date();

    System.out.println("\nPlease enter the first date: ");
    String date0 = input.nextLine();
    try {//from  w ww  . ja  va 2s .c o  m
        dateX = dateFormat.parse(date0);
    } catch (ParseException ex) {
        Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
    }

    System.out.println("\nPlease enter the second date: ");
    String date1 = input.nextLine();
    try {
        dateY = dateFormat.parse(date1);
    } catch (ParseException ex) {
        Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
    }

    Query query = session
            .createQuery("SELECT s " + "FROM Sales s " + "WHERE s.date " + "BETWEEN :date0 AND :date1");
    query.setDate("date0", dateX);
    query.setDate("date1", dateY);

    return query;
}

From source file:edu.sjsu.cs157bproject3.App.java

public static Query soldLastMonth(Session session) {

    LocalDate lastMonth = LocalDate.now().minusMonths(1);
    LocalDate currentMonth = LocalDate.now();
    Date lastMonthAsDate = java.sql.Date.valueOf(lastMonth);
    Date currentMonthAsDate = java.sql.Date.valueOf(currentMonth);

    Query query = session
            .createQuery("SELECT s " + "FROM Sales s " + "WHERE Date_id " + "BETWEEN :from AND :to ");
    query.setDate("from", lastMonthAsDate);
    query.setDate("to", currentMonthAsDate);

    return query;
}

From source file:es.itecban.deployment.executionmanager.manager.PlanManagerImpl.java

License:Apache License

public DeploymentPlanType[] findDeploymentPlans(PlanFilter filter, String planName) {
    DeploymentPlanType[] result = null;//from w w w  . jav a  2  s .co  m
    Query query = null;

    final Session session = sessionFactory.openSession();
    try {
        if (filter == null)
            throw new Exception("A non-null plan filter must be specified.");
        //getting the target from the filter
        if (filter.getTarget() == null) {
            logger.fine("The target in the filter is null. Getting all the plans");
            return this.getAllDeploymentPlans();
        }
        String planTarget = filter.getTarget();
        String qSelect = qp.getDeploymentList(filter, planName);
        logger.info("Query to execute: " + qSelect);
        query = session.createQuery(qSelect);

        query.setString("planTarget", planTarget);
        if (filter.getCreationMin() != null)
            query.setDate("creationDateMin", filter.getCreationMin().toGregorianCalendar().getTime());
        if (filter.getCreationMax() != null)
            query.setDate("creationDateMax", filter.getCreationMax().toGregorianCalendar().getTime());
        if (filter.getLastModificationMin() != null)
            query.setDate("lastModificationDateMin",
                    filter.getLastModificationMin().toGregorianCalendar().getTime());
        if (filter.getLastModificationMax() != null)
            query.setDate("lastModificationDateMax",
                    filter.getLastModificationMax().toGregorianCalendar().getTime());
        if (filter.getScheduledLaunchMin() != null)
            query.setDate("scheduledLaunchDateMin",
                    filter.getScheduledLaunchMin().toGregorianCalendar().getTime());
        if (filter.getScheduledLaunchMax() != null)
            query.setDate("scheduledLaunchDateMax",
                    filter.getScheduledLaunchMax().toGregorianCalendar().getTime());

        List deploymentPlans = query.list();

        logger.fine("Executing query: " + qSelect);
        result = (DeploymentPlanType[]) deploymentPlans.toArray(new DeploymentPlanType[deploymentPlans.size()]);
        logger.fine("Number of plans matching criteria found: " + result.length);
    } catch (Exception e) {
        e.printStackTrace();
    }
    session.close();
    return result;
}

From source file:es.itecban.deployment.executionmanager.manager.report.manager.ReportManagerImpl.java

License:Apache License

@Override
public String[] findExecutionReportByParameters(String planName, XMLGregorianCalendar endTimeMin,
        XMLGregorianCalendar endTimeMax, String creationUserId, String launchUserId, String environment) {

    Query query = null;
    final Session session = sessionFactory.openSession();

    String qSelect = this.qp.findReportsByParameters(planName, endTimeMin, endTimeMax, creationUserId,
            launchUserId, environment);/*  ww w  .  j av  a2s.  c o  m*/
    query = session.createQuery(qSelect);
    // Set the values
    query.setString("environment", environment != null ? environment : "%");
    if (endTimeMin != null)
        query.setDate("endTimeMin", endTimeMin.toGregorianCalendar().getTime());
    if (endTimeMax != null)
        query.setDate("endTimeMax", endTimeMax.toGregorianCalendar().getTime());

    String[] executionReportArray = this.findExecutionReports(query);
    session.close();
    return executionReportArray;
}

From source file:es.logongas.fpempresa.dao.empresa.impl.OfertaDAOImplHibernate.java

License:Open Source License

@Override
public List<Oferta> getOfertasInscritoUsuarioTitulado(DataSession dataSession, Usuario usuario,
        Provincia provincia, Date fechaInicio, Date fechaFin) {
    Session session = (Session) dataSession.getDataBaseSessionImpl();

    StringBuilder sb = new StringBuilder();

    sb.append("SELECT candidato.oferta FROM  Candidato  as candidato WHERE candidato.usuario.idIdentity=?");

    if (provincia != null) {
        sb.append(" AND candidato.oferta.municipio.provincia.idProvincia=? ");
    }// w w w  .  ja  v  a2s  .  c  om
    if (fechaInicio != null) {
        sb.append(" AND candidato.oferta.fecha>=? ");
    }
    if (fechaFin != null) {
        sb.append(" AND candidato.oferta.fecha<=? ");
    }

    sb.append(" ORDER BY candidato.oferta.fecha DESC");

    Query query = session.createQuery(sb.toString());
    int paramNumber = 0;
    query.setInteger(paramNumber++, usuario.getIdIdentity());
    if (provincia != null) {
        query.setInteger(paramNumber++, provincia.getIdProvincia());
    }
    if (fechaInicio != null) {
        query.setDate(paramNumber++, fechaInicio);
    }
    if (fechaFin != null) {
        query.setDate(paramNumber++, fechaFin);
    }

    return (List<Oferta>) query.list();
}

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

License:Open Source License

/**
 * Calculate FTE of Team Members for these employee
 * @param employee// www .  j ava2 s . c om
 * @param dateOut 
 * @param dateIn 
 * @return
 */
public double calculateFTE(Employee employee, Date dateIn, Date dateOut) {

    double fte = 0;
    if (employee.getIdEmployee() != null) {
        Query query = getSession().createQuery("select sum(t.fte) " + "from Teammember as t "
                + "where ((t.dateIn between :datein and :dateout) "
                + "or (t.dateOut between :datein and :dateout) "
                + "or (:datein between t.dateIn and t.dateOut) "
                + "or (:dateout between t.dateIn and t.dateOut)) " + "and t.employee.idEmployee = :idEmployee");
        query.setInteger("idEmployee", employee.getIdEmployee());
        query.setDate("datein", dateIn);
        query.setDate("dateout", dateOut);
        if (query.uniqueResult() != null) {
            fte = (Long) query.uniqueResult();
        }
    }
    return fte;
}

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

License:Open Source License

/**
 * Find Approvals TimeSheet/*  w ww .  j ava  2s .  c o m*/
 * object[0] == idEmployee
 * object[1] == Name employee
 * object[2] == idProject
 * object[3] == Project Name 
 * @param pm
 * @param initDate
 * @param endDate
 * @param minStatus
 * @param maxStatus
 * @param statusResource
 * @param includeClosed
 * @return
 */
@SuppressWarnings("unchecked")
public List<Object[]> findApprovalsTimeByPM(Employee pm, Date initDate, Date endDate, String minStatus,
        String maxStatus, String statusResource, boolean includeClosed) {

    String orts2StatusApp2 = "";
    String ortsStatusApp2 = "";

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

        ortsStatusApp2 = "and (ts.status = :minStatus or ts.status = :maxStatus ";
        orts2StatusApp2 = "and (ts2.status = :minStatus or ts2.status = :maxStatus ";

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

            ortsStatusApp2 += "or ts.status = :app1 ";
            orts2StatusApp2 += "or ts2.status = :app1 ";
        }

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

            ortsStatusApp2 += "or ts.status = :app2 ";
            orts2StatusApp2 += "or ts2.status = :app2 ";
        }

        ortsStatusApp2 += ") ";
        orts2StatusApp2 += ") ";
    }

    Query query = getSession().createQuery(
            "select distinct e.idEmployee,c.fullName ,p.idProject, p.projectName " + "from Employee as e "
                    + "join e.timesheets as ts " + "join e.contact as c " + "join ts.projectactivity as pa "
                    + "join pa.teammembers tm " + "join pa.project as p " + "where " + "tm.employee = e and "
                    + (ValidateUtil.isNotNull(statusResource) ? "tm.status = :statusResource and " : "")
                    + "(p.employeeByProjectManager = :pm or " + "(ts.projectactivity is null "
                    + "and e.performingorg = :po " + "and e.idEmployee not in "
                    + "(select distinct e2.idEmployee " + "from Employee as e2 " + "join e2.timesheets as ts2 "
                    + "join ts2.projectactivity as pa2 " + "join pa2.project as p2 "
                    + "where p2.employeeByProjectManager = :pm " + "and ts2.initDate = :initDate "
                    + "and ts2.endDate = :endDate " + orts2StatusApp2 + ")" + ")" + "and e.idEmployee in "
                    + "(select distinct e3.idEmployee " + "from Employee as e3 " + "join e3.timesheets as ts3 "
                    + "join ts3.projectactivity as pa3 " + "join pa3.project as p3 "
                    + "where p3.employeeByProjectManager = :pm " + "and ts3.initDate = :initDate "
                    + "and ts3.endDate = :endDate " + "and ts3.status = :app0" + ")" + ") "
                    + "and ts.initDate = :initDate " + "and ts.endDate = :endDate " + ortsStatusApp2
                    + (!includeClosed ? "and p.status != :statusClosed and p.status != :statusArchived " : ""));

    query.setInteger("pm", pm.getIdEmployee());
    query.setDate("initDate", initDate);
    query.setDate("endDate", endDate);
    query.setEntity("po", pm.getPerformingorg());
    query.setString("app0", Constants.TIMESTATUS_APP0);

    if (!includeClosed) {
        query.setString("statusClosed", Constants.STATUS_CLOSED);
        query.setString("statusArchived", Constants.STATUS_ARCHIVED);
    }

    if (ValidateUtil.isNotNull(statusResource)) {
        query.setString("statusResource", statusResource);
    }

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

        query.setString("maxStatus", maxStatus);
        query.setString("minStatus", minStatus);

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

            query.setString("app1", Constants.TIMESTATUS_APP1);
        }

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

            query.setString("app2", Constants.TIMESTATUS_APP2);
        }
    }

    return query.list();
}

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

License:Open Source License

/**
 * Find Approvals Expense Sheet//from   ww w .j ava2s  .  c o m
 * object[0] == idEmployee
 * object[1] == Name employee
 * object[2] == idProject
 * object[3] == Project Name 
 * @param pm
 * @param initDate
 * @param endDate
 * @param maxStatus 
 * @return
 */
@SuppressWarnings("unchecked")
public List<Object[]> findApprovalsExpenseByPM(Employee pm, Date initDate, Date endDate, String maxStatus) {

    Query query = getSession()
            .createQuery("select distinct e.idEmployee,c.fullName ,p.idProject, p.projectName "
                    + "from Employee as e " + "join e.expensesheets as ex " + "join ex.project as p "
                    + "join e.contact as c " + "where p.employeeByProjectManager = :pm "
                    + "and (ex.expenseDate between :initDate and :endDate) "
                    + "and (ex.status = :app1 or ex.status = :maxStatus)");

    query.setInteger("pm", pm.getIdEmployee());
    query.setDate("initDate", initDate);
    query.setDate("endDate", endDate);
    query.setString("app1", Constants.EXPENSE_STATUS_APP1);
    query.setString("maxStatus", maxStatus);

    return query.list();
}

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

License:Open Source License

/**
* Filter employees an contacts having timesheets suggest to reject.
* 
* @param initDate//from   w  w  w.  jav a 2  s  .c  om
* @return
*/
@SuppressWarnings("unchecked")
public List<ApprovalWrap> findSuggestRejectInAprovals(Date initDate) {

    // Construct query.
    String q = "SELECT DISTINCT NEW es.sm2.openppm.core.model.wrap.ApprovalWrap( " + "e, "
            + "(SELECT count(ts.suggestReject) " + " FROM Timesheet ts "
            + " WHERE ts.initDate = :initDate AND ts.suggestReject is true and ts.employee = e))"
            + "FROM Timesheet t " + "JOIN t.employee e " + "WHERE t.initDate = :initDate ";

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

    // Add initDate  for filter.
    query.setDate("initDate", initDate);

    return query.list();
}