alertdaoimpl.java

CRM源码This file describes some issues that should be implemented in future and how it should be imple

/*
 * Copyright 2006-2007 Queplix Corp.
 *
 * Licensed under the Queplix Public License, Version 1.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.queplix.com/solutions/commercial-open-source/queplix-public-license/
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package com.queplix.core.modules.alert.utils.db;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.modules.alert.AlertBlock;
import com.queplix.core.modules.alert.AlertData;
import com.queplix.core.modules.alert.utils.AlertDAO;
import com.queplix.core.modules.alert.utils.AlertVO;
import com.queplix.core.modules.eql.CompoundKey;
import com.queplix.core.utils.StringHelper;
import com.queplix.core.utils.dao.AbstractDAO;
import com.queplix.core.utils.sql.SqlWrapper;
import com.queplix.core.utils.sql.SqlWrapperFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Project-specific implementation of the AlertDAO
 * @author [ALB] Baranov Andrey
 * @version $Revision: 1.1.1.1 $ $Date: 2005/09/12 15:30:10 $
 */

public class AlertDAOImpl
    extends AbstractDAO implements AlertDAO {

    // ===================================================== Fields

    /** SQL wrapper implementation reference. */
    protected SqlWrapper sqlWrapper = SqlWrapperFactory.getSqlWrapper();

    // ===================================================== Overridden methods

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#deleteAlertVO
     */
    public int deleteAlertVO( AlertVO alert ) {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = sqlWrapper.doConnection();

            // Delete blocks.
            ps = sqlWrapper.doPreparedStatement( con, DBRealmManager.getSql( "delete_block_alerts" ) );
            ps.setLong( 1, alert.getAlertID() );
            sqlWrapper.executeUpdate( ps );
            ps.close();

            // Delete main record.
            ps = sqlWrapper.doPreparedStatement( con, DBRealmManager.getSql( "delete_alert" ) );
            ps.setLong( 1, alert.getAlertID() );

            return sqlWrapper.executeUpdate( ps );

        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );

        } finally {
            sqlWrapper.closeConnection( con, ps );
        }
    }

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#blockAlertVO
     */
    public int blockAlertVO( AlertVO alert, AlertBlock block ) {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = sqlWrapper.doConnection();
            long id = sqlWrapper.getNextKey( con, DBRealmManager.getSql( "alert_block_table" ) );
            ps = sqlWrapper.doPreparedStatement( con, DBRealmManager.getSql( "insert_block_alert" ) );
            ps.setLong( 1, id );
            ps.setLong( 2, alert.getAlertID() );
            ps.setLong( 3, block.getUserID() );
            ps.setInt( 4, block.getUserType() );

            return sqlWrapper.executeUpdate( ps );

        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );

        } finally {
            sqlWrapper.closeConnection( con, ps );
        }
    }

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#getNextAlertID
     */
    public long getNextAlertID() {
        Connection con = null;

        try {
            con = sqlWrapper.doConnection();
            return sqlWrapper.getNextKey( con, DBRealmManager.getSql( "alert_table" ) );

        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );

        } finally {
            sqlWrapper.closeConnection( con );
        }
    }

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#storeAlertVO
     */
    public int storeAlertVO( AlertVO alert ) {

        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = sqlWrapper.doConnection();
            ps = sqlWrapper.doPreparedStatement( con, DBRealmManager.getSql( "insert_alert" ) );

            // Store mandatory data.
            int i = 1;
            ps.setLong( i++, alert.getAlertID() );
            ps.setLong( i++, alert.getCreatorID() );
            ps.setInt( i++, alert.getCreatorType() );
            ps.setLong( i++, alert.getSenderID() );
            ps.setInt( i++, alert.getSenderType() );
            ps.setString( i++, alert.getMessage() );
            ps.setInt( i++, alert.getSeverity() );
            sqlWrapper.getTimestampParser().setValue( ps, i++, alert.getDateposted() );

            // Store optional data.
            sqlWrapper.getLongParser().setValue( ps, i++, alert.getRecipientID() );
            sqlWrapper.getIntParser().setValue( ps, i++, alert.getRecipientType() );
            sqlWrapper.getLongParser().setValue( ps, i++, alert.getWorkgroupID() );
            sqlWrapper.getIntParser().setValue( ps, i++, alert.getTier() );
            ps.setInt( i++, alert.isToAll() ? 1 : 0 );

            // Store add-on data.
            AlertData data = alert.getData();
            if( data != null ) {
                // .. put values
                CompoundKey recordKey = data.getRecordKey();
                int keys = recordKey.size();

                ps.setString( i++, data.getFocusID() );
                ps.setString( i++, data.getTabID() );
                ps.setString( i++, data.getFormID() );
                for( int j = 0; j < 4; j++ ) {
                    if( j < keys ) {
                        ps.setString( i++, ( String ) recordKey.getKey( j ) );
                    } else {
                        // .. put empty string
                        ps.setString( i++, "" );
                    }
                }

            } else {
                // .. put nulls
                while( i <= 20 ) {
                    ps.setNull( i++, Types.VARCHAR );
                }
            }

            return sqlWrapper.executeUpdate( ps );

        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );

        } finally {
            sqlWrapper.closeConnection( con, ps );
        }
    }

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#updateAlertVO
     */
    public int updateAlertVO( AlertVO alert ) {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = sqlWrapper.doConnection();
            ps = sqlWrapper.doPreparedStatement( con, DBRealmManager.getSql( "update_alert" ) );

            // Store mandatory data.
            int i = 1;
            ps.setString( i++, alert.getMessage() );
            ps.setInt( i++, alert.getSeverity() );
            ps.setLong( i++, alert.getAlertID() );

            return sqlWrapper.executeUpdate( ps );

        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );

        } finally {
            sqlWrapper.closeConnection( con, ps );
        }
    }

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#loadAlertVO
     */
    public AlertVO loadAlertVO( long alertID ) {

        if( getLogger().isDebugEnabled() ) {
            DEBUG( "Try to get data for " + alertID + " alert" );
        }

        AlertVO alert = null;
        Connection con = null;
        PreparedStatement ps = null;

        try {
            // Open connection.
            con = sqlWrapper.doConnection();

            // Execute main SQL query.
            String sql = DBRealmManager.getSql( "get_alert" );
            ps = sqlWrapper.doPreparedStatement( con, sql );
            ps.setLong( 1, alertID );

            // Read main data.
            ResultSet rs = sqlWrapper.executeQuery( ps );
            if( !rs.next() ) {
                return null;
            }
            alert = createAlert( rs );
            rs.close();
            ps.close();

            // Execute block SQL query.
            sql = DBRealmManager.getSql( "select_block_alerts" );
            ps = sqlWrapper.doPreparedStatement( con, sql );
            ps.setLong( 1, alertID );

            // Read blocks.
            rs = sqlWrapper.executeQuery( ps );
            while( rs.next() ) {
                AlertBlock block = createBlock( rs, 1 );
                alert.addBlock( block );
            }

        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );

        } finally {
            sqlWrapper.closeConnection( con, ps );
        }

        if( getLogger().isDebugEnabled() ) {
            DEBUG( "Got alert VO for " + alertID + " : " + alert );
        }

        return alert;
    }

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#loadAlertVO
     */
    public Collection loadAllAlertVO() {

        Connection con = null;
        PreparedStatement ps = null;

        Collection alertList = new ArrayList();
        Map blockMap = new HashMap();

        try {
            // Open connection.
            con = sqlWrapper.doConnection();

            // Execute block SQL query.
            ps = sqlWrapper.doPreparedStatement( con, DBRealmManager.getSql( "select_all_block_alerts" ) );

            // Read all blocks and put to the Map <code>blockMap</code>.
            ResultSet rs = sqlWrapper.executeQuery( ps );
            while( rs.next() ) {
                Long alertID = new Long( rs.getLong( 1 ) );
                AlertBlock block = createBlock( rs, 2 );

                List l = ( List ) blockMap.get( alertID );
                if( l == null ) {
                    l = new ArrayList();
                    blockMap.put( alertID, l );
                }
                l.add( block );
            }
            rs.close();
            ps.close();

            // Execute main SQL query.
            ps = sqlWrapper.doPreparedStatement( con, DBRealmManager.getSql( "select_all_alert" ) );

            // Read main data.
            rs = sqlWrapper.executeQuery( ps );
            while( rs.next() ) {
                AlertVO alert = createAlert( rs );
                alertList.add( alert );

                // Add blocks if any exist.
                Long alertID = new Long( alert.getAlertID() );
                List l = ( List ) blockMap.get( alertID );
                if( l != null ) {
                    for( int i = 0; i < l.size(); i++ ) {
                        alert.addBlock( ( AlertBlock ) l.get( i ) );
                    }
                }
            }

        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );

        } finally {
            sqlWrapper.closeConnection( con, ps );
        }

        return alertList;
    }

    // ===================================================== Protected methods

    //
    // Creates AlertVO from ResultSet
    //
    protected AlertVO createAlert( ResultSet rs )
        throws SQLException {

        // Read mandatory data.
        int i = 1;
        long alertID = rs.getLong( i++ );
        long creatorID = rs.getLong( i++ );
        int creatorType = rs.getInt( i++ );
        long senderID = rs.getLong( i++ );
        int senderType = rs.getInt( i++ );
        String message = rs.getString( i++ );
        int severity = rs.getInt( i++ );
        Date dateposted = sqlWrapper.getTimestampParser().getValue( rs, i++ );

        AlertVO alertVO = new AlertVO( alertID,
                                       creatorID,
                                       creatorType,
                                       senderID,
                                       senderType,
                                       message,
                                       severity,
                                       dateposted );

        // Read optional data.
        Long recipientID = sqlWrapper.getLongParser().getValue( rs, i++ );
        Integer recipientType = sqlWrapper.getIntParser().getValue( rs, i++ );
        if( recipientID != null ) {
            if( recipientType == null ) {
                throw new NullPointerException( "Recipeint Type for Alert #" + alertID + " is NULL" );
            }
            alertVO.setUserRecipient( recipientID.longValue(), recipientType.intValue() );
        }

        Long workgroupID = sqlWrapper.getLongParser().getValue( rs, i++ );
        Integer tier = sqlWrapper.getIntParser().getValue( rs, i++ );
        if( workgroupID != null ) {
            alertVO.setWorkgroupRecipient( workgroupID.longValue(), tier );
        }

        boolean toAll = ( rs.getInt( i++ ) == 1 );
        alertVO.setToAllRecipient( toAll );

        // Read add-on data.
        String focusID = rs.getString( i++ );
        if( focusID != null ) {
            String tabID = rs.getString( i++ );
            String formID = rs.getString( i++ );

            CompoundKey recordKey = new CompoundKey();
            recordKey.addKey( rs.getString( i++ ) );

            String recordId2 = rs.getString( i++ );
            if( !StringHelper.isEmpty( recordId2 ) ) {
                recordKey.addKey( recordId2 );
            }
            String recordId3 = rs.getString( i++ );
            if( !StringHelper.isEmpty( recordId3 ) ) {
                recordKey.addKey( recordId3 );
            }
            String recordId4 = rs.getString( i++ );
            if( !StringHelper.isEmpty( recordId4 ) ) {
                recordKey.addKey( recordId4 );
            }

            alertVO.setData( new AlertData( focusID, tabID, formID, recordKey ) );
        }

        return alertVO;
    }

    //
    // Creates AlertBlock from ResultSet
    //
    protected AlertBlock createBlock( ResultSet rs, int i )
        throws SQLException {

        long recipientID = rs.getLong( i++ );
        int recipientType = rs.getInt( i++ );

        return new AlertBlock( recipientID, recipientType );
    }
}