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:org.kimios.kernel.dms.hibernate.HDocumentFactory.java

License:Open Source License

public List<Document> getExpiredDocuments(String sourceWorkspace, Date expirationDate) {
    String sql = "from Document fetch all properties " + "where updateDate < :expirationDate "
            + "and path like :sourceWorkspace";
    Query query = getSession().createQuery(sql);
    query.setDate("expirationDate", expirationDate);
    query.setString("sourceWorkspace", "/" + sourceWorkspace + "/%");
    return query.list();
}

From source file:org.mifos.framework.components.batchjobs.helpers.ApplyPenaltyToLoanAccountsHelper.java

License:Open Source License

private List<LoanBO> getLoanAccounts() {
    Query select = StaticHibernateUtil.getSessionTL()
            .getNamedQuery(NamedQueryConstants.GET_ALL_LOAN_ACCOUNTS_WITH_PENALTIES);
    select.setDate("currentDate", currentDate);

    return castList(LoanBO.class, select.list());
}

From source file:org.mifos.framework.components.batchjobs.helpers.ProductStatusHelper.java

License:Open Source License

@Override
public void execute(long timeInMillis) throws BatchJobException {
    Session session;/* w  ww . j  a  v  a 2  s .  c  o  m*/
    String hqlUpdate;
    Query query;
    try {
        session = StaticHibernateUtil.getSessionTL();
        StaticHibernateUtil.startTransaction();

        hqlUpdate = "update PrdOfferingBO p set p.prdStatus=:activeLoanStatus "
                + "where p.prdType.productTypeID=:loan and p.startDate=:currentDate";
        query = session.createQuery(hqlUpdate);
        query.setShort("activeLoanStatus", PrdStatus.LOAN_ACTIVE.getValue());
        query.setShort("loan", ProductType.LOAN.getValue());
        query.setDate("currentDate", new Date(timeInMillis));
        query.executeUpdate();

        hqlUpdate = "update PrdOfferingBO p set p.prdStatus=:inActiveLoanStatus "
                + "where p.prdType.productTypeID=:loan and p.endDate=:currentDate";
        query = session.createQuery(hqlUpdate);
        query.setShort("inActiveLoanStatus", PrdStatus.LOAN_INACTIVE.getValue());
        query.setShort("loan", ProductType.LOAN.getValue());
        query.setDate("currentDate", new Date(timeInMillis));
        query.executeUpdate();

        hqlUpdate = "update PrdOfferingBO p set p.prdStatus=:activeSavingStatus "
                + "where p.prdType.productTypeID=:saving and p.startDate=:currentDate";
        query = session.createQuery(hqlUpdate);
        query.setShort("activeSavingStatus", PrdStatus.SAVINGS_ACTIVE.getValue());
        query.setShort("saving", ProductType.SAVINGS.getValue());
        query.setDate("currentDate", new Date(timeInMillis));
        query.executeUpdate();

        hqlUpdate = "update PrdOfferingBO p set p.prdStatus=:inActiveSavingStatus "
                + "where p.prdType.productTypeID=:saving and p.endDate=:currentDate";
        query = session.createQuery(hqlUpdate);
        query.setShort("inActiveSavingStatus", PrdStatus.SAVINGS_INACTIVE.getValue());
        query.setShort("saving", ProductType.SAVINGS.getValue());
        query.setDate("currentDate", new Date(timeInMillis));
        query.executeUpdate();

        StaticHibernateUtil.commitTransaction();
    } catch (Exception e) {
        StaticHibernateUtil.rollbackTransaction();
        throw new BatchJobException(e);
    }
}

From source file:org.olat.course.statistic.export.SQLLogExporter.java

License:Apache License

/**
 * @TODO: charSet is currently ignored!!!!!
 * @see org.olat.course.statistic.export.ICourseLogExporter#exportCourseLog(java.io.File, java.lang.String, java.lang.Long, java.util.Date, java.util.Date, boolean)
 *///from  w ww . j a  v  a  2s  . co  m
@Override
public void exportCourseLog(final File outFile, final String charSet, final Long resourceableId,
        final Date begin, Date end, final boolean resourceAdminAction, final boolean anonymize) {
    log_.info("exportCourseLog: BEGIN outFile=" + outFile + ", charSet=" + charSet + ", resourceableId="
            + resourceableId + ", begin=" + begin + ", end=" + end + ", resourceAdminAction="
            + resourceAdminAction + ", anonymize=" + anonymize);
    try {
        if (!outFile.exists()) {
            if (!outFile.getParentFile().exists() && !outFile.getParentFile().mkdirs()) {
                throw new IllegalArgumentException(
                        "Cannot create parent of OutFile " + outFile.getAbsolutePath());
            }
            if (!outFile.createNewFile()) {
                throw new IllegalArgumentException("Cannot create outFile " + outFile.getAbsolutePath());
            }
        }
    } catch (final IOException e) {
        e.printStackTrace();
        throw new IllegalArgumentException("Cannot create outFile " + outFile.getAbsolutePath());
    }
    if (!outFile.delete()) {
        throw new IllegalStateException("Could not delete temporary outfile " + outFile.getAbsolutePath());
    }

    // try to make sure the database can write into this directory
    if (!outFile.getParentFile().setWritable(true, false)) {
        log_.warn("exportCourseLog: COULD NOT SET DIR TO WRITEABLE: " + outFile.getParent());
    }

    String query = String.valueOf(anonymize ? anonymizedUserSql_ : nonAnonymizedUserSql_);
    if (begin != null) {
        query = query.concat(" AND (v.creationDate >= :createdAfter)");
    }
    if (end != null) {
        query = query.concat(" AND (v.creationDate <= :createdBefore)");
    }

    final Session session = sessionFactory_.openSession();
    final long startTime = System.currentTimeMillis();
    try {
        session.beginTransaction();
        final Query dbQuery = session.createSQLQuery(query);

        dbQuery.setBoolean("resAdminAction", resourceAdminAction);
        dbQuery.setString("resId", Long.toString(resourceableId));
        if (begin != null) {
            dbQuery.setDate("createdAfter", begin);
        }
        if (end != null) {
            final Calendar cal = Calendar.getInstance();
            cal.setTime(end);
            cal.add(Calendar.DAY_OF_MONTH, 1);
            end = cal.getTime();
            dbQuery.setDate("createdBefore", end);
        }

        dbQuery.setString("outFile", outFile.getAbsolutePath());

        dbQuery.scroll();
    } catch (final RuntimeException e) {
        e.printStackTrace(System.out);
    } catch (final Error er) {
        er.printStackTrace(System.out);
    } finally {
        if (session != null) {
            session.close();
        }
        final long diff = System.currentTimeMillis() - startTime;
        log_.info("exportCourseLog: END DURATION=" + diff + ", outFile=" + outFile + ", charSet=" + charSet
                + ", resourceableId=" + resourceableId + ", begin=" + begin + ", end=" + end
                + ", resourceAdminAction=" + resourceAdminAction + ", anonymize=" + anonymize);
    }
}

From source file:org.openbravo.costing.PriceDifferenceByDateProcess.java

License:Open Source License

@Override
protected JSONObject doExecute(Map<String, Object> parameters, String content) {
    JSONObject jsonRequest = null;//w  ww  . java 2s  . c  om
    String message = new String();
    OBContext.setAdminMode(true);
    try {
        jsonRequest = new JSONObject(content);
        JSONObject params = jsonRequest.getJSONObject("_params");
        log.debug("{}", jsonRequest);
        JSONArray productIds = params.getJSONArray("M_Product_ID");
        String mvdate = params.getString("movementdate");
        String orgId = params.getString("ad_org_id");
        productIds.toString();
        Date movementdate = JsonUtils.createDateFormat().parse(mvdate);
        doChecks(orgId, movementdate);

        String strUpdate = "UPDATE MaterialMgmtMaterialTransaction trx" + " SET checkpricedifference = 'Y'"
                + " WHERE exists (" + " SELECT 1" + " FROM  ProcurementReceiptInvoiceMatch mpo"
                + " WHERE trx.isCostCalculated = 'Y' and mpo.goodsShipmentLine.id = trx.goodsShipmentLine.id  "
                + " AND trx.movementDate >= :date and trx.organization.id in (:orgIds))";

        if (productIds.length() > 0) {
            strUpdate = strUpdate.concat(" AND product.id IN :productIds ");
        }

        Set<String> products = new HashSet<String>();
        for (int i = 0; i < productIds.length(); i++) {
            products.add(productIds.getString(i));
        }
        Query update = OBDal.getInstance().getSession().createQuery(strUpdate);

        if (productIds.length() > 0) {
            update.setParameterList("productIds", products);
        }
        update.setParameterList("orgIds", new OrganizationStructureProvider().getChildTree(orgId, true));
        update.setDate("date", movementdate);

        update.executeUpdate();

        JSONObject msg = new JSONObject();
        msg = PriceDifferenceProcess.processPriceDifference(null, null);
        jsonRequest.put("message", msg);
    } catch (Exception e) {
        log.error("Error Process Price Correction", e);

        try {
            jsonRequest = new JSONObject();
            jsonRequest.put("retryExecution", true);
            if (message.isEmpty()) {
                Throwable ex = DbUtility.getUnderlyingSQLException(e);
                message = OBMessageUtils.translateError(ex.getMessage()).getMessage();
            }
            JSONObject errorMessage = new JSONObject();
            errorMessage.put("severity", "error");
            errorMessage.put("text", message);
            jsonRequest.put("message", errorMessage);
            return jsonRequest;
        } catch (Exception ignore) {
        }
    } finally {
        OBContext.restorePreviousMode();
    }
    return jsonRequest;
}

From source file:org.openbravo.financial.ResetAccounting.java

License:Open Source License

@SuppressWarnings("unchecked")
public static HashMap<String, Integer> delete(String adClientId, String adOrgId, String adTableId,
        String recordId, String strdatefrom, String strdateto) throws OBException {
    if (recordId == null) {
        recordId = "";
    }/*from  w  w  w  .j  a v  a 2s. c o  m*/
    long totalProcess = System.currentTimeMillis();
    long start = 0l;
    long end = 0l;
    long totalselect = 0l;
    int deleted = 0;
    int updated = 0;
    HashMap<String, Integer> results = new HashMap<String, Integer>();
    results.put("deleted", 0);
    results.put("updated", 0);
    results.put("totaldeleted", 0);
    results.put("totalupdated", 0);
    String client = adClientId;
    List<String> tables = getTables(adTableId);
    try {
        Set<String> orgIds = new OrganizationStructureProvider().getChildTree(adOrgId, true);
        for (String table : tables) {
            List<String> docbasetypes = getDocbasetypes(client, table, recordId);
            String myQuery = "select distinct e.recordID from FinancialMgmtAccountingFact e where e.organization.id in (:orgIds) and e.client.id = :clientId and e.table.id = :tableId";
            if (recordId != null && !"".equals(recordId)) {
                myQuery = myQuery + " and e.recordID = :recordId ";
            }
            for (String dbt : docbasetypes) {
                List<Date[]> periods = new ArrayList<Date[]>();
                // organizationPeriod: hashmap with organizations allow period control and their open
                // periods
                Map<String, List<Date[]>> organizationPeriod = new HashMap<String, List<Date[]>>();
                // organizationPeriodControl: hashmap with organizations and their organization allow
                // period control associated
                Map<String, String> organizationPeriodControl = new HashMap<String, String>();
                String calendarId = getCalendarId(adOrgId);
                Iterator<String> iterator = orgIds.iterator();
                while (iterator.hasNext()) {
                    String organization = iterator.next();
                    String myQuery1 = "select p.id from Organization p where ad_org_getperiodcontrolallow(:organization)=p.id";
                    Query query1 = OBDal.getInstance().getSession().createQuery(myQuery1);
                    query1.setString("organization", organization);
                    query1.setMaxResults(1);
                    if (query1.uniqueResult() != null) {
                        String orgperiodcontrol = query1.uniqueResult().toString();
                        organizationPeriodControl.put(organization, orgperiodcontrol);
                        if (!organizationPeriod.keySet().contains(orgperiodcontrol)) {
                            periods = getPeriodsDates(getOpenPeriods(client, dbt, orgIds, calendarId, table,
                                    recordId, strdatefrom, strdateto, orgperiodcontrol));
                            organizationPeriod.put(orgperiodcontrol, periods);
                        }
                    }
                }
                int docUpdated = 0;
                int docDeleted = 0;
                for (String organization : orgIds) {
                    String orgAllow = organizationPeriodControl.get(organization);
                    periods = organizationPeriod.get(orgAllow);
                    for (Date[] p : periods) {
                        StringBuffer consDate = new StringBuffer();
                        consDate.append(" and e.documentCategory = :dbt");
                        consDate.append(" and e.organization.id = :organization");
                        consDate.append(" and e.accountingDate >= :dateFrom and e.accountingDate <= :dateTo");
                        String exceptionsSql = myQuery + consDate.toString();
                        consDate.append(
                                " and not exists (select a from FinancialMgmtAccountingFact a where a.recordID = e.recordID and a.table.id = e.table.id and (a.accountingDate < :dateFrom or a.accountingDate > :dateTo))");
                        final Query query = OBDal.getInstance().getSession()
                                .createQuery(myQuery + consDate.toString());
                        if (recordId != null && !"".equals(recordId)) {
                            query.setString("recordId", recordId);
                        }
                        query.setParameterList("orgIds", orgIds);
                        query.setString("clientId", client);
                        query.setString("dbt", dbt);
                        query.setString("tableId", table);
                        query.setDate("dateFrom", p[0]);
                        query.setDate("dateTo", p[1]);
                        query.setString("organization", organization);
                        if (recordId != null && !"".equals(recordId)) {
                            query.setMaxResults(1);
                        } else {
                            query.setFetchSize(FETCH_SIZE);
                        }
                        start = System.currentTimeMillis();
                        List<String> transactions = query.list();
                        end = System.currentTimeMillis();
                        totalselect = totalselect + end - start;
                        while (transactions.size() > 0) {
                            HashMap<String, Integer> partial = delete(transactions, table, client);
                            deleted = deleted + partial.get("deleted");
                            updated = updated + partial.get("updated");
                            docUpdated = docUpdated + partial.get("updated");
                            docDeleted = docDeleted + partial.get("deleted");
                            start = System.currentTimeMillis();
                            transactions = query.list();
                            end = System.currentTimeMillis();
                            totalselect = totalselect + end - start;
                        }
                        // Documents with postings in different periods are treated separately to validate
                        // all
                        // dates are within an open period
                        HashMap<String, Integer> partial = treatExceptions(exceptionsSql, recordId, table,
                                orgIds, client, p[0], p[1], calendarId, strdatefrom, strdateto, dbt, orgAllow,
                                organization);
                        deleted = deleted + partial.get("deleted");
                        updated = updated + partial.get("updated");
                        docUpdated = docUpdated + partial.get("updated");
                        docDeleted = docDeleted + partial.get("deleted");
                    }
                }
                log4j.debug("docBaseType: " + dbt);
                log4j.debug("updated: " + docUpdated);
                log4j.debug("deleted: " + docDeleted);
            }
        }

    } catch (OBException e) {

        throw e;
    } catch (Exception e) {
        throw new OBException("Delete failed", e);
    }
    results.put("deleted", deleted);
    results.put("updated", updated);
    log4j.debug("total totalProcess (milies): " + (System.currentTimeMillis() - totalProcess));
    if (recordId != null && !"".equals(recordId) && deleted == 0 && updated == 0) {
        if (recordId != null && !"".equals(recordId) && adTableId != null && !"".equals(adTableId)) {
            // If record exists but there is no entry in fact table then unpost record
            try {
                OBContext.setAdminMode(false);
                Table table = OBDal.getInstance().get(Table.class, adTableId);
                OBCriteria<AccountingFact> obc = OBDal.getInstance().createCriteria(AccountingFact.class);
                obc.setFilterOnReadableClients(false);
                obc.setFilterOnReadableOrganization(false);
                obc.setFilterOnActive(false);
                obc.add(Restrictions.eq(AccountingFact.PROPERTY_RECORDID, recordId));
                obc.add(Restrictions.eq(AccountingFact.PROPERTY_TABLE, table));
                if (obc.list().size() == 0) {
                    String tableName = table.getDBTableName();
                    String tableIdName = table.getDBTableName() + "_Id";
                    String strUpdate = "update " + tableName
                            + " set posted='N', processing='N' where (posted<>'N' or posted is null or processing='N') and "
                            + tableIdName + " = :recordID ";
                    final Query update = OBDal.getInstance().getSession().createSQLQuery(strUpdate);
                    update.setParameter("recordID", recordId);
                    updated = update.executeUpdate();
                    return results;
                }
            } finally {
                OBContext.restorePreviousMode();
            }
        }
        throw new OBException("@PeriodClosedForUnPosting@");
    }
    return results;
}

From source file:org.openbravo.financial.ResetAccounting.java

License:Open Source License

public static HashMap<String, Integer> restore(String clientId, String adOrgId, String tableId, String datefrom,
        String dateto) throws OBException {
    HashMap<String, Integer> results = new HashMap<String, Integer>();
    results.put("deleted", 0);
    results.put("updated", 0);
    String tableName = "";
    String tableDate = "";
    OBContext.setAdminMode(false);/*  w  w  w  .  j  a v  a 2  s  .c  om*/
    try {
        Table table = OBDal.getInstance().get(Table.class, tableId);
        tableName = table.getDBTableName();
        tableDate = ModelProvider.getInstance().getEntityByTableName(table.getDBTableName())
                .getPropertyByColumnName(table.getAcctdateColumn().getDBColumnName()).getColumnName();

        String strUpdate = "update " + tableName
                + " set posted='N', processing='N' where posted not in ('Y') and processed = 'Y' and AD_Org_ID in (:orgIds)  ";
        if (!("".equals(datefrom))) {
            strUpdate = strUpdate + " and " + tableDate + " >= :dateFrom ";
        }
        if (!("".equals(dateto))) {
            strUpdate = strUpdate + " and " + tableDate + " <= :dateTo ";
        }

        Query update = OBDal.getInstance().getSession().createSQLQuery(strUpdate);
        update.setParameterList("orgIds", new OrganizationStructureProvider().getNaturalTree(adOrgId));
        try {
            if (!("".equals(datefrom))) {
                update.setDate("dateFrom", OBDateUtils.getDate(datefrom));
            }
            if (!("".equals(dateto))) {
                update.setDate("dateTo", OBDateUtils.getDate(dateto));
            }
        } catch (ParseException e) {
            log4j.error("Restore - Error parsisng dates", e);
        }

        int updated = update.executeUpdate();
        results.put("updated", updated);
        OBDal.getInstance().getConnection().commit();
        OBDal.getInstance().getSession().clear();
        return results;
    } catch (Exception e) {
        OBDal.getInstance().rollbackAndClose();
        throw new OBException("Error Reseting Accounting", e);
    } finally {
        OBContext.restorePreviousMode();
    }
}

From source file:org.openbravo.financial.ResetAccounting.java

License:Open Source License

@SuppressWarnings("unchecked")
private static List<Period> getOpenPeriods(String clientId, String docBaseType, Set<String> orgIds,
        String calendarId, String tableId, String recordId, String datefrom, String dateto,
        String orgPeriodControl) {
    if (!"".equals(recordId)) {
        List<Period> periods = new ArrayList<Period>();
        periods.add(getDocumentPeriod(clientId, tableId, recordId, docBaseType, orgPeriodControl, orgIds));
        return periods;

    }// w w  w .  j a  v  a 2  s .com
    String myQuery = "select distinct p from FinancialMgmtPeriodControl e left join e.period p left join p.year y left join y.calendar c where c.id = :calendarId and e.client.id = :clientId and e.documentCategory = :docbasetype and e.periodStatus = 'O' and e.organization.id = :orgPeriodControl";

    if (!("".equals(datefrom)) && !("".equals(dateto))) {
        myQuery = myQuery + " and p.startingDate <= :dateTo";
        myQuery = myQuery + " and p.endingDate >= :dateFrom";
    } else if (!("".equals(datefrom)) && ("".equals(dateto))) {
        myQuery = myQuery + " and p.endingDate >= :dateFrom";
    } else if (("".equals(datefrom)) && !("".equals(dateto))) {
        myQuery = myQuery + " and p.startingDate <= :dateTo";
    }
    Query query = OBDal.getInstance().getSession().createQuery(myQuery);
    // TODO: Review orgIds
    // query.setParameterList("orgIds", orgIds);
    query.setString("calendarId", calendarId);
    query.setString("clientId", clientId);
    query.setString("docbasetype", docBaseType);
    query.setString("orgPeriodControl", orgPeriodControl);

    try {
        if (!("".equals(datefrom))) {
            query.setDate("dateFrom", OBDateUtils.getDate(datefrom));
        }
        if (!("".equals(dateto))) {
            query.setDate("dateTo", OBDateUtils.getDate(dateto));
        }
    } catch (ParseException e) {
        log4j.error("GetOpenPeriods - error parsing dates", e);
    }
    return query.list();
}

From source file:org.openbravo.financial.ResetAccounting.java

License:Open Source License

private static HashMap<String, Integer> treatExceptions(String myQuery, String recordId, String table,
        Set<String> orgIds, String client, Date dateFrom, Date dateTo, String calendarId, String datefrom,
        String dateto, String dbt, String orgPeriodControl, String targetOrganization) {
    HashMap<String, Integer> results = new HashMap<String, Integer>();
    results.put("deleted", 0);
    results.put("updated", 0);
    final Query query = OBDal.getInstance().getSession().createQuery(myQuery);
    if (recordId != null && !"".equals(recordId)) {
        query.setString("recordId", recordId);
    }//from  w  w  w.  jav  a2s  . c om
    query.setParameterList("orgIds", orgIds);
    query.setString("clientId", client);
    query.setString("dbt", dbt);
    query.setString("tableId", table);
    query.setDate("dateFrom", dateFrom);
    query.setDate("dateTo", dateTo);
    query.setString("organization", targetOrganization);
    if (recordId != null && !"".equals(recordId)) {
        query.setMaxResults(1);
    }
    @SuppressWarnings("unchecked")
    List<String> transactions = query.list();
    for (String transaction : transactions) {
        OBCriteria<AccountingFact> factCrit = OBDal.getInstance().createCriteria(AccountingFact.class);
        factCrit.add(Restrictions.eq(AccountingFact.PROPERTY_RECORDID, transaction));
        factCrit.add(
                Restrictions.eq(AccountingFact.PROPERTY_TABLE, OBDal.getInstance().get(Table.class, table)));
        factCrit.add(
                Restrictions.eq(AccountingFact.PROPERTY_CLIENT, OBDal.getInstance().get(Client.class, client)));
        List<AccountingFact> facts = factCrit.list();
        Set<Date> exceptionDates = new HashSet<Date>();
        for (AccountingFact fact : facts) {
            if (dateFrom.compareTo(fact.getAccountingDate()) != 0
                    || dateTo.compareTo(fact.getAccountingDate()) != 0) {
                exceptionDates.add(fact.getAccountingDate());
            }
        }
        if (checkDates(exceptionDates, client, orgIds, facts.get(0).getDocumentCategory(), calendarId, datefrom,
                dateto, orgPeriodControl)) {
            List<String> toDelete = new ArrayList<String>();
            toDelete.add(transaction);
            results = delete(toDelete, table, client);
        } else {
            if (recordId != null && !"".equals(recordId)) {
                throw new OBException("@PeriodClosedForUnPosting@");
            }
        }
    }
    return results;
}

From source file:org.opencustomer.db.dao.crm.custom.CompanyListDAO.java

License:Mozilla Public License

public List<CompanyListVO> getList(String companyName, Integer companyStateId, Integer companyTypeId,
        Integer sectorId, Integer categoryId, Integer ratingId, Date lastContactDateStart,
        Date lastContactDateEnd, Sort sort, Page page, UserVO user) {
    String _companyName = toLower(adjustWildcards(companyName));

    List<CompanyListVO> list = new ArrayList<CompanyListVO>();

    try {//www  . j av  a  2 s . c o  m
        StringBuilder hql = new StringBuilder();

        hql.append(" select c.id, c.companyName, ct.name, cs.name, s.name, ");
        hql.append(" (case when max(co.contactTimestamp) is null then max(co2.contactTimestamp) else ");
        hql.append("    case when max(co2.contactTimestamp) is null then max(co.contactTimestamp) else ");
        hql.append(
                "       case when max(co.contactTimestamp) > max(co2.contactTimestamp) then max(co.contactTimestamp) else max(co2.contactTimestamp) end ");
        hql.append("    end ");
        hql.append(" end), ct.nameKey, cs.nameKey, s.nameKey, ca.name, ca.nameKey, r.name, r.nameKey");
        hql.append(" from ").append(CompanyVO.class.getName()).append(" c ");
        hql.append(" left join c.companyType ct ");
        hql.append(" left join c.companyState cs ");
        hql.append(" left join c.sector s ");
        hql.append(" left join c.category ca ");
        hql.append(" left join c.rating r ");
        hql.append(" left join c.contacts co ");
        hql.append(" left join c.personContacts pc ");
        hql.append(" left join pc.contact co2 ");
        hql.append(" where 1=1 ");

        if (companyName != null)
            hql.append(" AND LOWER(c.companyName) like :companyName");
        if (companyStateId != null)
            hql.append(" AND c.companyState = :companyState");
        if (companyTypeId != null)
            hql.append(" AND c.companyType = :companyType");
        if (sectorId != null)
            hql.append(" AND c.sector = :sector");
        if (ratingId != null)
            hql.append(" AND c.rating = :rating");
        if (categoryId != null)
            hql.append(" AND c.category = :category");
        if (user != null) {
            hql.append(" AND (c.accessGlobal != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" OR (c.accessGroup != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" AND exists (from ").append(UserVO.class.getName())
                    .append(" as u where u.id = :userId and u.profile.usergroups.id = c.ownerGroup)) ");
            hql.append(" OR (c.accessUser != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" AND c.ownerUser = :userId)) ");
        }

        hql.append(" group by c.id, c.companyName, ct.name, cs.name, s.name");
        if (lastContactDateStart != null || lastContactDateEnd != null) {
            hql.append(" having ");
            if (lastContactDateStart != null) {
                hql.append(" (case when max(co.contactTimestamp) is null then max(co2.contactTimestamp) else ");
                hql.append(
                        "    case when max(co2.contactTimestamp) is null then max(co.contactTimestamp) else ");
                hql.append(
                        "       case when max(co.contactTimestamp) > max(co2.contactTimestamp) then max(co.contactTimestamp) else max(co2.contactTimestamp) end ");
                hql.append("    end ");
                hql.append(" end ");
                hql.append(" >= :lastContactDateStart)");
            }
            if (lastContactDateEnd != null) {
                if (lastContactDateStart != null)
                    hql.append(" and ");
                hql.append(" (case when max(co.contactTimestamp) is null then max(co2.contactTimestamp) else ");
                hql.append(
                        "    case when max(co2.contactTimestamp) is null then max(co.contactTimestamp) else ");
                hql.append(
                        "       case when max(co.contactTimestamp) > max(co2.contactTimestamp) then max(co.contactTimestamp) else max(co2.contactTimestamp) end ");
                hql.append("    end ");
                hql.append(" end ");
                hql.append(" <= :lastContactDateEnd)");
            }
        }

        if (sort != null)
            hql.append(" order by " + getSortString(sort));

        // companyStateId, Integer companyTypeId, Integer sectorTypeId, Date
        // lastContactDateStart, Date lastContactDateEnd
        Query query = HibernateContext.getSession().createQuery(hql.toString());
        if (companyName != null)
            query.setString("companyName", _companyName);
        if (companyStateId != null)
            query.setInteger("companyState", companyStateId);
        if (companyTypeId != null)
            query.setInteger("companyType", companyTypeId);
        if (sectorId != null)
            query.setInteger("sector", sectorId);
        if (ratingId != null)
            query.setInteger("rating", ratingId);
        if (categoryId != null)
            query.setInteger("category", categoryId);
        if (lastContactDateStart != null)
            query.setDate("lastContactDateStart", lastContactDateStart);
        if (lastContactDateEnd != null)
            query.setDate("lastContactDateEnd", lastContactDateEnd);
        if (user != null)
            query.setInteger("userId", user.getId());

        if (page != null) {
            query.setFirstResult(getFirstResult(page));
            query.setMaxResults(getMaxResults(page));
        }

        Iterator it = query.list().iterator();
        while (it.hasNext())
            addEntity(list, (Object[]) it.next());

        if (log.isDebugEnabled())
            log.debug("found " + list.size() + " companies");
    } catch (HibernateException e) {
        log.error("Could not find companies", e);
        throw e;
    }

    return list;
}