personlistdao.java

开源项目CRM之OpenCustomer

/*******************************************************************************
 * ***** BEGIN LICENSE BLOCK Version: MPL 1.1
 * 
 * The contents of this file are subject to the Mozilla Public License Version
 * 1.1 (the "License"); you may not use this file except in compliance with the
 * License. You may obtain a copy of the License at http://www.mozilla.org/MPL/
 * 
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 * 
 * The Original Code is the OpenCustomer CRM.
 * 
 * The Initial Developer of the Original Code is Thomas Bader (Bader & Jene
 * Software-Ingenieurb黵o). Portions created by the Initial Developer are
 * Copyright (C) 2005 the Initial Developer. All Rights Reserved.
 * 
 * Contributor(s): Thomas Bader <[e-mail]>
 * 
 * ***** END LICENSE BLOCK *****
 */

package org.opencustomer.application.db.dao.crm.custom;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.opencustomer.application.db.vo.crm.PersonVO;
import org.opencustomer.application.db.vo.crm.custom.PersonListVO;
import org.opencustomer.db.DatabaseUtility;
import org.opencustomer.db.dao.AbstractDAO;
import org.opencustomer.web.util.Page;
import org.opencustomer.web.util.Sort;

public final class PersonListDAO extends AbstractDAO
{
    private static final Logger log = Logger.getLogger(PersonListDAO.class);

    public final static int SORT_FIRSTNAME = 1;

    public final static int SORT_LASTNAME = 2;

    public final static int SORT_EMAIL = 3;

    public final static int SORT_PHONE = 4;

    public final static int SORT_FAX = 5;

    public final static int SORT_MOBILE = 6;

    public final static int SORT_COMPANY_NAME = 7;

    /**
     * @throws DatabaseException
     */
    public PersonListDAO() throws HibernateException
    {
        super();
    }

    public List<PersonListVO> getList(String firstName, String lastName, String phone, String fax, String mobile, String email, String companyName, Sort sort, Page page)
    {
        return getList(firstName, lastName, phone, fax, mobile, email, companyName, null, sort, page);
    }

    public int countList(String firstName, String lastName, String phone, String fax, String mobile, String email, String companyName)
    {
        return countList(firstName, lastName, phone, fax, mobile, email, companyName, null);
    }

    public List<PersonListVO> getList(String firstName, String lastName, String phone, String fax, String mobile, String email, String companyName, List<PersonVO> excludedPersons, Sort sort, Page page)
    {
        String _firstName = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(firstName));
        String _lastName = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(lastName));
        String _companyName = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(companyName));
        String _phone = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(phone));
        String _fax = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(fax));
        String _mobile = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(mobile));
        String _email = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(email));

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

        try
        {
            StringBuilder hql = new StringBuilder();
            hql.append(" SELECT e.id, e.firstName, e.lastName, e.phone, e.fax, e.mobile, e.email, c.companyName, u.id ");
            hql.append(" FROM ").append(PersonVO.class.getName()).append(" e ");
            hql.append(" left join e.company c ");
            hql.append(" left join e.user u ");
            hql.append(" WHERE e.deleted = 0 ");
            if (firstName != null)
                hql.append(" AND lower(e.firstName) like :firstName ");
            if (lastName != null)
                hql.append(" AND lower(e.lastName) like :lastName ");
            if (phone != null)
                hql.append(" AND lower(e.phone) like :phone ");
            if (fax != null)
                hql.append(" AND lower(e.fax) like :fax ");
            if (mobile != null)
                hql.append(" AND lower(e.mobile) like :mobile ");
            if (email != null)
                hql.append(" AND lower(e.email) like :email ");
            if (companyName != null)
                hql.append(" AND lower(c.companyName) like :companyName ");
            if (excludedPersons != null)
            {
                StringBuilder personIds = new StringBuilder();
                Iterator<PersonVO> it = excludedPersons.iterator();
                while (it.hasNext())
                {
                    if (personIds.length() > 0)
                        personIds.append(", ");
                    personIds.append(it.next().getId());
                }

                hql.append(" AND LOWER(person.person_id) not in ( ").append(personIds).append(")");
            }
            if (sort != null)
                hql.append(" order by " + getSortString(sort));

            Query query = this.session.createQuery(hql.toString());
            if (firstName != null)
                query.setString("firstName", _firstName);
            if (lastName != null)
                query.setString("lastName", _lastName);
            if (phone != null)
                query.setString("phone", _phone);
            if (fax != null)
                query.setString("fax", _fax);
            if (mobile != null)
                query.setString("mobile", _mobile);
            if (email != null)
                query.setString("email", _email);
            if (companyName != null)
                query.setString("companyName", _companyName);

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

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

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

        return list;
    }

    public int countList(String firstName, String lastName, String phone, String fax, String mobile, String email, String companyName, List<PersonVO> excludedPersons)
    {
        String _firstName = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(firstName));
        String _lastName = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(lastName));
        String _companyName = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(companyName));
        String _phone = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(phone));
        String _fax = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(fax));
        String _mobile = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(mobile));
        String _email = DatabaseUtility.toLower(DatabaseUtility.adjustWildcards(email));

        Integer count = 0;

        try
        {
            StringBuilder hql = new StringBuilder();
            hql.append(" SELECT count(e.id) ");
            hql.append(" FROM ").append(PersonVO.class.getName()).append(" e ");
            hql.append(" left join e.company c ");
            hql.append(" left join e.user u ");
            hql.append(" WHERE e.deleted = 0 ");
            if (firstName != null)
                hql.append(" AND lower(e.firstName) like :firstName ");
            if (lastName != null)
                hql.append(" AND lower(e.lastName) like :lastName ");
            if (phone != null)
                hql.append(" AND lower(e.phone) like :phone ");
            if (fax != null)
                hql.append(" AND lower(e.fax) like :fax ");
            if (mobile != null)
                hql.append(" AND lower(e.mobile) like :mobile ");
            if (email != null)
                hql.append(" AND lower(e.email) like :email ");
            if (companyName != null)
                hql.append(" AND lower(c.companyName) like :companyName ");
            if (excludedPersons != null)
            {
                StringBuilder personIds = new StringBuilder();
                Iterator<PersonVO> it = excludedPersons.iterator();
                while (it.hasNext())
                {
                    if (personIds.length() > 0)
                        personIds.append(", ");
                    personIds.append(it.next().getId());
                }

                hql.append(" AND LOWER(person.person_id) not in ( ").append(personIds).append(")");
            }

            Query query = this.session.createQuery(hql.toString());
            if (firstName != null)
                query.setString("firstName", _firstName);
            if (lastName != null)
                query.setString("lastName", _lastName);
            if (phone != null)
                query.setString("phone", _phone);
            if (fax != null)
                query.setString("fax", _fax);
            if (mobile != null)
                query.setString("mobile", _mobile);
            if (email != null)
                query.setString("email", _email);
            if (companyName != null)
                query.setString("companyName", _companyName);

            count = (Integer) query.uniqueResult();

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

        return count;
    }

    private void addEntity(List<PersonListVO> list, Object[] columns)
    {
        PersonListVO vo = new PersonListVO();

        vo.setPersonId((Integer) columns[0]);
        vo.setFirstName((String) columns[1]);
        vo.setLastName((String) columns[2]);
        vo.setPhone((String) columns[3]);
        vo.setFax((String) columns[4]);
        vo.setMobile((String) columns[5]);
        vo.setEmail((String) columns[6]);

        vo.setCompanyName((String) columns[7]);

        if (columns[8] != null)
            vo.setUser(true);
        else
            vo.setUser(false);

        list.add(vo);
    }

    protected final String getSortString(Sort sort)
    {
        String orderField = "";

        switch (sort.getField())
        {
            case SORT_COMPANY_NAME:
                orderField = "c.companyName";
                break;
            case SORT_FIRSTNAME:
                orderField = "e.firstName";
                break;
            case SORT_LASTNAME:
            default:
                orderField = "e.lastName";
                break;
            case SORT_PHONE:
                orderField = "e.phone";
                break;
            case SORT_FAX:
                orderField = "e.fax";
                break;
            case SORT_MOBILE:
                orderField = "e.mobile";
                break;
            case SORT_EMAIL:
                orderField = "e.email";
                break;
        }

        if (!sort.isAscending())
            return orderField + " desc";
        else
            return orderField + " asc";
    }
}