/*
 * 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.config.utils.db;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.jxb.entity.Efield;
import com.queplix.core.jxb.entity.Entity;
import com.queplix.core.modules.config.utils.EntityViewConfigDAO;
import com.queplix.core.utils.dao.AbstractDAO;
import com.queplix.core.utils.sql.SqlWrapper;
import com.queplix.core.utils.sql.SqlWrapperFactory;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InvalidClassException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

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

public class EntityViewConfigDAOImpl
        extends AbstractDAO implements EntityViewConfigDAO {

    // --------------------------------------------------------------- Variables

    protected SqlWrapper sqlWrapper = SqlWrapperFactory.getSqlWrapper();

    // --------------------------------------------------------------- Overrided Methods

    /*
     * No javadoc
     * @see EntityViewConfigDAO#clearAllEntityVO
     */

    public int clearAllEntityVO() {
        Connection con = null;
        Statement stat = null;

        try {
            con = sqlWrapper.doConnection();
            stat = sqlWrapper.doStatement(con);
            return sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_entity"));

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

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

    /*
     * No javadoc
     * @see EntityViewConfigDAO#clearEntityVO
     */
    public int clearEntityVO(String entityName) {
        Connection con = null;
        PreparedStatement ps = null;

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

            sqlWrapper.getStringParser().setValue(ps, 1, entityName);
            return sqlWrapper.executeUpdate(ps);

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

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

    /*
     * No javadoc
     * @see EntityViewConfigDAO#storeEntityVO
     */
    public int storeEntityVO(Entity entity) {

        Connection con = null;
        PreparedStatement ps = null;

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

            sqlWrapper.getStringParser().setValue(ps, 1, entity.getName());
            sqlWrapper.getBinaryParser().setValue(ps, 2, toByteArray(entity));
            return sqlWrapper.executeUpdate(ps);

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

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

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

    /*
     * No javadoc
     * @see EntityViewConfigDAO#storeHistoryVO
     */
    public void storeHistoryVO(Entity entity) {

        final String HISTORY_TABLE = "QX_HISTORY";
        final String HISTORY_TBL_TABLE = "QX_HIS_TABLE";
        final String HISTORY_FLD_TABLE = "QX_HIS_FIELD";
        Connection con = null;
        PreparedStatement ps_sel = null;
        PreparedStatement ps_ins = null;
        Long hisTableId = null;
        Long hisFieldId = null;

        if(entity.getDbobject().equals(HISTORY_TABLE)) {
            return;
        }

        try {
            con = sqlWrapper.doConnection();
            ps_sel = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                    "select_his_table"));
            sqlWrapper.getStringParser().setObject(ps_sel, 1,
                    entity.getDbobject().toUpperCase());
            ResultSet rs = sqlWrapper.executeQuery(ps_sel);
            if(rs.next()) {
                hisTableId = sqlWrapper.getLongParser().getValue(rs, 1);
            } else {
                ps_ins = sqlWrapper.doPreparedStatement(con,
                        DBRealmManager.getSql("insert_his_table"));
                hisTableId = sqlWrapper.getNextKey(con, HISTORY_TBL_TABLE);
                sqlWrapper.getLongParser().setValue(ps_ins, 1, hisTableId);
                sqlWrapper.getStringParser().setValue(ps_ins, 2,
                        entity.getDbobject().toUpperCase());
                int res = sqlWrapper.executeUpdate(ps_ins);
            }
        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);

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

        String hisField = entity.getHistoryfield();

        try {
            con = sqlWrapper.doConnection();
            // cycle to select all Efield objects
            int efield_count = entity.getEfieldCount();
            for(int k = 0; k < efield_count; k++) {
                Efield efield = entity.getEfield(k);
                if(efield.getName().equals(hisField)) {
                    continue;
                }
                ps_sel = sqlWrapper.doPreparedStatement(con,
                        DBRealmManager.getSql("select_his_field"));
                sqlWrapper.getStringParser().setObject(ps_sel, 1,
                        efield.getName().toLowerCase());
                sqlWrapper.getLongParser().setObject(ps_sel, 2, hisTableId);
                ResultSet rs = sqlWrapper.executeQuery(ps_sel);
                if(!rs.next()) {
                    ps_ins = sqlWrapper.doPreparedStatement(con,
                            DBRealmManager.getSql("insert_his_field"));
                    hisFieldId = sqlWrapper.getNextKey(con, HISTORY_FLD_TABLE);
                    sqlWrapper.getLongParser().setValue(ps_ins, 1, hisFieldId);
                    sqlWrapper.getLongParser().setValue(ps_ins, 2, hisTableId);
                    sqlWrapper.getStringParser().setValue(ps_ins, 3,
                            efield.getName().toLowerCase());
                    int res = sqlWrapper.executeUpdate(ps_ins);
                }
            }
        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);

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

    /*
     * No javadoc
     * @see EntityViewConfigDAO#loadEntityVO
     */
    public Entity loadEntityVO(String entityName) {

        Connection con = null;
        PreparedStatement ps = null;

        Entity entity = null;

        try {
            con = sqlWrapper.doConnection();
            ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                    "select_entity"));
            sqlWrapper.getStringParser().setObject(ps, 1, entityName);
            ResultSet rs = sqlWrapper.executeQuery(ps);
            if(rs.next()) {
                byte[] data = sqlWrapper.getBinaryParser().getValue(rs, 1);
                entity = toEntity(data);
            }

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

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

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

        return entity;
    }

    /*
     * No javadoc
     * @see EntityViewConfigDAO#loadAllEntityVO
     */
    public Collection loadAllEntityVO() {

        Connection con = null;
        PreparedStatement ps = null;

        Collection entityList = new ArrayList();

        try {
            con = sqlWrapper.doConnection();
            ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                    "select_all_entity"));
            ResultSet rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                byte[] data = sqlWrapper.getBinaryParser().getValue(rs, 2);
                Entity entity = toEntity(data);
                entityList.add(entity);
            }

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

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

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

        return entityList;
    }

    // --------------------------------------------------------------- Private methods

    //
    // Convert the Entity to byte[].
    //

    private static byte[] toByteArray(Entity entity)
            throws IOException {

        if(entity == null) {
            return null;
        }

        ByteArrayOutputStream baos = null;
        ObjectOutputStream oos = null;
        try {
            baos = new ByteArrayOutputStream();
            oos = new ObjectOutputStream(baos);

            oos.writeObject(entity);
            return baos.toByteArray();

        } finally {
            try {
                if(oos != null) {
                    oos.close();
                }
            } catch (Exception ex) {
            }
            try {
                if(baos != null) {
                    baos.close();
                }
            } catch (Exception ex) {
            }
        }
    }

    //
    // Convert byte[] to an Entity.
    //
    private static Entity toEntity(byte[] data)
            throws IOException {

        if(data == null) {
            return null;
        }

        ByteArrayInputStream bais = null;
        ObjectInputStream ois = null;
        try {
            bais = new ByteArrayInputStream(data);
            ois = new ObjectInputStream(bais);
            return (Entity) ois.readObject();

        } catch (InvalidClassException ex) {
            // bad Entity in database - return <null>
            return null;

        } catch (ClassNotFoundException ex) {
            throw new GenericSystemException(ex);

        } finally {
            try {
                if(ois != null) {
                    ois.close();
                }
            } catch (Exception ex) {
            }
            try {
                if(bais != null) {
                    bais.close();
                }
            } catch (Exception ex) {
            }
        }
    }
}
