contactdao.java

开源项目CRM之OpenCustomer

            if (boundType != null)
                hql.append(" AND lower(e.boundType) like :boundType ");
            if (contactTimestampStart != null)
                hql.append(" AND lower(e.contactTimestamp) >= :contactTimestampStart ");
            if (contactTimestampEnd != null)
                hql.append(" AND lower(e.contactTimestamp) <= :contactTimestampEnd ");
            if (name != null)
            {
                hql.append(" AND (lower(p.firstName) like :name ");
                hql.append(" OR lower(p.lastName) like :name ");
                hql.append(" OR lower(c.companyName) like :name ");
                hql.append(" ) ");
            }

            Query query = this.session.createQuery(hql.toString());
            if (personId != null)
                query.setInteger("personId", personId);
            if (subject != null)
                query.setString("subject", _subject);
            if (contactType != null)
                query.setString("contactType", contactType);
            if (boundType != null)
                query.setString("boundType", boundType);
            if (contactTimestampStart != null)
                query.setDate("contactTimestampStart", contactTimestampStart);
            if (contactTimestampEnd != null)
                query.setDate("contactTimestampEnd", contactTimestampEnd);
            if (name != null)
                query.setString("name", _name);

            count = (Integer) query.uniqueResult();

            if (log.isDebugEnabled())
                log.debug("count " + count + " contacts");
        }
        catch (HibernateException e)
        {
            log.error("Could not find contacts", e);
            throw e;
        }

        return count;
    }

    public List<ContactVO> getListForCompany(Integer companyId, String subject, String contactType, String boundType, Date contactTimestampStart, Date contactTimestampEnd, String name, Sort sort, Page page)
    {
        String _subject = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(subject));
        String _name = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(name));

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

        try
        {
            StringBuilder hql = new StringBuilder();

            hql.append(" select distinct e ");
            hql.append(" FROM ").append(getEntityClass().getName()).append(" e ");
            hql.append(" left join e.personContacts pc ");
            hql.append(" left join pc.person p ");
            hql.append(" left join e.company c ");
            hql.append(" WHERE e.deleted = 0 ");
            hql.append(" AND (c.id = :companyId ");
            hql.append(" OR e in ( ");
            hql.append(" select e1 ");
            hql.append(" from ").append(getEntityClass().getName()).append(" e1 ");
            hql.append(" inner join e1.personContacts pc1 ");
            hql.append(" inner join pc1.person p1 ");
            hql.append(" inner join p1.company c1 ");
            hql.append(" where c1.id = :companyId ");
            hql.append(" )) ");
            if (subject != null)
                hql.append(" AND lower(e.subject) like :subject ");
            if (contactType != null)
                hql.append(" AND lower(e.contactType) like :contactType ");
            if (boundType != null)
                hql.append(" AND lower(e.boundType) like :boundType ");
            if (contactTimestampStart != null)
                hql.append(" AND lower(e.contactTimestamp) >= :contactTimestampStart ");
            if (contactTimestampEnd != null)
                hql.append(" AND lower(e.contactTimestamp) <= :contactTimestampEnd ");
            if (name != null)
            {
                hql.append(" AND (lower(p.firstName) like :name ");
                hql.append(" OR lower(p.lastName) like :name ");
                hql.append(" OR lower(c.companyName) like :name ");
                hql.append(" ) ");
            }

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

            Query query = this.session.createQuery(hql.toString());
            if (companyId != null)
                query.setInteger("companyId", companyId);
            if (subject != null)
                query.setString("subject", _subject);
            if (contactType != null)
                query.setString("contactType", contactType);
            if (boundType != null)
                query.setString("boundType", boundType);
            if (contactTimestampStart != null)
                query.setDate("contactTimestampStart", contactTimestampStart);
            if (contactTimestampEnd != null)
                query.setDate("contactTimestampEnd", contactTimestampEnd);
            if (name != null)
                query.setString("name", _name);

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

            list = toTypeSafeList(query.list());

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

        return list;
    }

    public int countListForCompany(Integer companyId, String subject, String contactType, String boundType, Date contactTimestampStart, Date contactTimestampEnd, String name)
    {
        String _subject = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(subject));
        String _name = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(name));

        Integer count = 0;

        try
        {
            StringBuilder hql = new StringBuilder();

            hql.append(" select count(distinct e.id) ");
            hql.append(" FROM ").append(getEntityClass().getName()).append(" e ");
            hql.append(" left join e.personContacts pc ");
            hql.append(" left join pc.person p ");
            hql.append(" left join e.company c ");
            hql.append(" WHERE e.deleted = 0 ");
            hql.append(" AND (c.id = :companyId ");
            hql.append(" OR e in ( ");
            hql.append(" select e1 ");
            hql.append(" from ").append(getEntityClass().getName()).append(" e1 ");
            hql.append(" inner join e1.personContacts pc1 ");
            hql.append(" inner join pc1.person p1 ");
            hql.append(" inner join p1.company c1 ");
            hql.append(" where c1.id = :companyId ");
            hql.append(" )) ");
            if (subject != null)
                hql.append(" AND lower(e.subject) like :subject ");
            if (contactType != null)
                hql.append(" AND lower(e.contactType) like :contactType ");
            if (boundType != null)
                hql.append(" AND lower(e.boundType) like :boundType ");
            if (contactTimestampStart != null)
                hql.append(" AND lower(e.contactTimestamp) >= :contactTimestampStart ");
            if (contactTimestampEnd != null)
                hql.append(" AND lower(e.contactTimestamp) <= :contactTimestampEnd ");
            if (name != null)
            {
                hql.append(" AND (lower(p.firstName) like :name ");
                hql.append(" OR lower(p.lastName) like :name ");
                hql.append(" OR lower(c.companyName) like :name ");
                hql.append(" ) ");
            }

            Query query = this.session.createQuery(hql.toString());
            if (companyId != null)
                query.setInteger("companyId", companyId);
            if (subject != null)
                query.setString("subject", _subject);
            if (contactType != null)
                query.setString("contactType", contactType);
            if (boundType != null)
                query.setString("boundType", boundType);
            if (contactTimestampStart != null)
                query.setDate("contactTimestampStart", contactTimestampStart);
            if (contactTimestampEnd != null)
                query.setDate("contactTimestampEnd", contactTimestampEnd);
            if (name != null)
                query.setString("name", _name);

            count = (Integer) query.uniqueResult();

            if (log.isDebugEnabled())
                log.debug("count " + count + " contacts");
        }
        catch (HibernateException e)
        {
            log.error("Could not find contacts", e);
            throw e;
        }

        return count;
    }
}