/*
 * 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.integrator.security.AccessLevel;
import com.queplix.core.integrator.security.PermissionObjectType;
import com.queplix.core.modules.config.jxb.Button;
import com.queplix.core.modules.config.jxb.Col;
import com.queplix.core.modules.config.jxb.ExternalField;
import com.queplix.core.modules.config.jxb.ExternalForm;
import com.queplix.core.modules.config.jxb.ExternalSet;
import com.queplix.core.modules.config.jxb.Filter;
import com.queplix.core.modules.config.jxb.Filters;
import com.queplix.core.modules.config.jxb.Focus;
import com.queplix.core.modules.config.jxb.Form;
import com.queplix.core.modules.config.jxb.Header;
import com.queplix.core.modules.config.jxb.HiddenControl;
import com.queplix.core.modules.config.jxb.Htmlelement;
import com.queplix.core.modules.config.jxb.Htmlelements;
import com.queplix.core.modules.config.jxb.InternalField;
import com.queplix.core.modules.config.jxb.Link;
import com.queplix.core.modules.config.jxb.LinkedDataset;
import com.queplix.core.modules.config.jxb.Row;
import com.queplix.core.modules.config.jxb.SubFocus;
import com.queplix.core.modules.config.jxb.Tab;
import com.queplix.core.modules.config.jxb.types.FormLabelsOrientationType;
import com.queplix.core.modules.config.jxb.types.PermissionsType;
import com.queplix.core.modules.config.utils.EntityHelper;
import com.queplix.core.modules.config.utils.ViewObject;
import com.queplix.core.modules.config.utils.ViewObjectsDAO;
import com.queplix.core.modules.config.utils.ViewObjectsStructure;
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.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

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

public class ViewObjectsDAOImpl extends AbstractDAO implements ViewObjectsDAO {
    private static final int FOCUS_TYPE = Integer.parseInt(
            DBRealmManager.getProperty("focus_type_id"));
    private static final int SUBFOCUS_TYPE = Integer.parseInt(
            DBRealmManager.getProperty("subfocus_type_id"));
    private static final int TAB_TYPE = Integer.parseInt(
            DBRealmManager.getProperty("tab_type_id"));
    private static final int FORM_TYPE = Integer.parseInt(
            DBRealmManager.getProperty("form_type_id"));
    private static final int FIELD_TYPE = Integer.parseInt(
            DBRealmManager.getProperty("field_type_id"));

    private static final String VIEW_OBJECTS_TABLE_NAME = "QX_VIEW_OBJECTS";

    protected SqlWrapper sqlWrapper = SqlWrapperFactory.getSqlWrapper();

    /**
     * @see com.queplix.core.modules.config.utils.ViewObjectsDAO#deleteObjects(com.queplix.core.modules.config.utils.ViewObjectsStructure)
     */
    public void deleteObjects(ViewObjectsStructure toBeDeleted) {
        Collection<ViewObject> objects = toBeDeleted.getFullObjects().values();
        int idsSize = objects.size();

        StringBuffer sqlObjectsSet = new StringBuffer("(");
        for(Iterator<ViewObject> iterator = objects.iterator();
            iterator.hasNext();) {
            ViewObject viewObject = iterator.next();
            sqlObjectsSet.append(viewObject.getId());
            if(iterator.hasNext()) {
                sqlObjectsSet.append(",");
            }
        }
        sqlObjectsSet.append(")");

        String s = sqlObjectsSet.toString();
//        INFO("Objects to be deleted :" + s);
        Connection con = null;
        Statement stat = null;
        try {
            con = sqlWrapper.doConnection();
            stat = sqlWrapper.doStatement(con);
            if(idsSize > 0) {
                sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                        "delall_form_settings_in_set") + s);
                sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                        "delall_permission_in_set") + s);
                sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                        "updateall_role_in_set") + s);
                sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                        "delall_view_object_in_set") + s);
            }

            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_linked_datasource"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_external_field"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_external_form"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_external_set"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_header"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql("delall_row"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql("delall_col"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_button"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_html_element"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_hiddencontrol"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_link"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_helplink"));
            sqlWrapper.executeUpdate(stat, DBRealmManager.getSql(
                    "delall_filter"));
        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);
        } finally {
            sqlWrapper.closeConnection(con, stat);
        }
    }

    public void updateExternalTabValues(List<Tab> tabs) {
        Connection con = sqlWrapper.doConnection();
        PreparedStatement deleteStatement = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("delete_helplink"));
        PreparedStatement insertStatement = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("insert_helplink"));
        try {
            for(Tab tab : tabs) {
                sqlWrapper.getStringParser().setValue(deleteStatement, 1,
                        tab.getName());
                sqlWrapper.executeUpdate(deleteStatement);
                if(tab.getHelplink() != null) {
                    sqlWrapper.getStringParser().setValue(insertStatement, 1,
                            tab.getName());
                    sqlWrapper.getStringParser().setValue(insertStatement, 2,
                            tab.getHelplink());
                    sqlWrapper.executeUpdate(insertStatement);
                }
            }
            deleteStatement.close();
            insertStatement.close();
        }
        catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);
        }
        finally {
            sqlWrapper.closeConnection(con);
        }
    }

    public void updateObjects(ViewObjectsStructure toUpdate) {
        Connection con = null;

        Map<String, ViewObject> focuses = toUpdate.getFocuses();
        //update focuses
        try {
            con = sqlWrapper.doConnection();
            for(ViewObject viewObject : focuses.values()) {
                if(viewObject.getStatus() == ViewObject.ObjectStatus.EXISTING) {
                    updateViewObject(viewObject, con);
                } else if(viewObject.getStatus() == ViewObject.ObjectStatus
                        .TO_BE_INSERTED) {
                    insertFocus(viewObject, con);
                }
            }
            updateSubFocuses(toUpdate, con);
        } catch (SQLException e) {
            throw new GenericSystemException("SQL exception: " + e.getMessage(),
                    e);
        } finally {
            sqlWrapper.closeConnection(con);
        }
    }

    private void updateSubFocuses(ViewObjectsStructure toUpdate, Connection con)
            throws SQLException {
        Map<String, ViewObject> subFocuses = toUpdate.getSubFocuses();
        for(ViewObject viewObject : subFocuses.values()) {
            if(viewObject.getStatus() == ViewObject.ObjectStatus.EXISTING) {
                updateViewObject(viewObject, con);
            } else if(viewObject.getStatus() == ViewObject.ObjectStatus
                    .TO_BE_INSERTED) {
                insertSubFocus(viewObject, con);
            }
        }
        updateTabs(toUpdate, con);
    }

    private void updateTabs(ViewObjectsStructure toUpdate, Connection con)
            throws SQLException {
        Map<String, ViewObject> tabs = toUpdate.getTabs();
        for(ViewObject viewObject : tabs.values()) {
            if(viewObject.getStatus() == ViewObject.ObjectStatus.EXISTING) {
                updateViewObject(viewObject, con);
            } else if(viewObject.getStatus() == ViewObject.ObjectStatus
                    .TO_BE_INSERTED) {
                insertTab(viewObject, con);
            }
        }
        updateForms(toUpdate, con);
    }

    private void updateForms(ViewObjectsStructure toUpdate, Connection con)
            throws SQLException {
        Map<String, ViewObject> forms = toUpdate.getForms();
        for(ViewObject viewObject : forms.values()) {
            if(viewObject.getStatus() == ViewObject.ObjectStatus.EXISTING) {
                updateViewObject(viewObject, con);
            } else if(viewObject.getStatus() == ViewObject.ObjectStatus
                    .TO_BE_INSERTED) {
                insertForm(viewObject, con);
            }
//            updateFields
        }
    }

    private void insertFocus(ViewObject viewObject, Connection con)
            throws SQLException {
        insertViewObject("insert_focus", viewObject, con);
    }

    private void insertSubFocus(ViewObject viewObject, Connection con)
            throws SQLException {
        insertViewObject("insert_subfocus", viewObject, con);
    }

    private void insertTab(ViewObject viewObject, Connection con)
            throws SQLException {
        insertViewObject("insert_tab", viewObject, con);
    }

    private void insertForm(ViewObject viewObject, Connection con)
            throws SQLException {
        insertViewObject("insert_form", viewObject, con);
    }

    private void insertViewObject(String sqlStatementName,
                                  ViewObject viewObject, Connection con)
            throws SQLException {
        PreparedStatement insertStatement = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql(sqlStatementName));
        try {
            long id = sqlWrapper.getNextKey(con, VIEW_OBJECTS_TABLE_NAME);
            sqlWrapper.getLongParser().setValue(insertStatement, 1, id);
            sqlWrapper.getStringParser().setValue(insertStatement, 2,
                    viewObject.getName());
            Long parentId = viewObject.getParent();
            if(parentId == null) {
                ViewObject obj = viewObject.getParentObject();
                if(obj != null) {
                    parentId = obj.getId();
                }
            }
            sqlWrapper.getLongParser().setValue(insertStatement, 3, parentId);
            sqlWrapper.getIntParser().setValue(insertStatement, 4,
                    viewObject.getOrderInGroup());
            sqlWrapper.getStringParser().setValue(insertStatement, 5,
                    viewObject.getIcon());
            sqlWrapper.getIntParser().setValue(insertStatement, 6,
                    viewObject.getInFrameLinks() ? 1:0);
            sqlWrapper.getIntParser().setValue(insertStatement, 7,
                    viewObject.getGrid() ? 1:0);
            sqlWrapper.executeUpdate(insertStatement);
            viewObject.setId(id);
        } finally {
            insertStatement.close();
        }
    }

    private void updateViewObject(ViewObject viewObject, Connection con)
            throws SQLException {
        PreparedStatement updateObject = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("update_object"));
        try {
            sqlWrapper.getIntParser().setValue(updateObject, 1,
                    viewObject.getOrderInGroup());
            sqlWrapper.getStringParser().setValue(updateObject, 2,
                    viewObject.getIcon());
            sqlWrapper.getIntParser().setValue(updateObject, 3,
                    viewObject.getInFrameLinks() ? 1:0);
            sqlWrapper.getIntParser().setValue(updateObject, 4,
                    viewObject.getGrid() ? 1:0);
            sqlWrapper.getLongParser().setValue(updateObject, 5,
                    viewObject.getId());
            sqlWrapper.executeUpdate(updateObject);
        } finally {
            updateObject.close();
        }
    }

    /**
     * @see com.queplix.core.modules.config.utils.ViewObjectsDAO#loadViewObjects()
     */
    public ViewObjectsStructure loadViewObjects() {
        ViewObjectsStructure ret = new ViewObjectsStructure();
        Connection con = sqlWrapper.doConnection();
        Statement stat = sqlWrapper.doStatement(con);
        try {
            ResultSet set = sqlWrapper.executeQuery(stat, DBRealmManager.getSql(
                    "select_view_objects"));
            while(set.next()) {
                Long id = sqlWrapper.getLongParser().getValue(set, 1);
                String name = sqlWrapper.getStringParser().getValue(set, 2);
                Long parentId = sqlWrapper.getLongParser().getValue(set, 3);
                int type = sqlWrapper.getIntParser().getValue(set, 4);
                String icon = sqlWrapper.getStringParser().getValue(set, 5);
                int order = sqlWrapper.getIntParser().getValue(set, 6);
                boolean inFrameLinks = sqlWrapper.getIntParser().getValue(set,
                        7) != 0;
                boolean grid = sqlWrapper.getIntParser().getValue(set, 8) != 0;
                ret.add(new ViewObject(id, name, parentId, order, getPermission(
                        type),
                        ViewObject.ObjectStatus.EXISTING, null, icon,
                        inFrameLinks, grid));
            }
        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);
        } finally {
            sqlWrapper.closeConnection(con, stat);
        }
        return ret;
    }

    public void updateExternalFormValues(
            Map<ViewObject, Form> updatingStructure) {
        Connection con = sqlWrapper.doConnection();
        Set<ViewObject> objects = updatingStructure.keySet();
        try {
            for(ViewObject viewObject : objects) {
                Form form = updatingStructure.get(viewObject);
                if(viewObject.getStatus() == ViewObject.ObjectStatus.EXISTING) {
                    updateFormSettings(form, viewObject, con);
                } else if(viewObject.getStatus() == ViewObject.ObjectStatus
                        .TO_BE_INSERTED) {
                    insertFormSettings(viewObject, form, con);
                }

                insertExternalSets(form, con);
                insertExternalForms(form, con);
                insertExternalFields(form, con);
                insertLinkedDatasets(form, con);
                //don't insert internal fields.

                saveLinks(con, form);
                saveButtons(con, form);
                saveHtmlElements(con, form);
                saveLayout(con, form);
                saveFormFilters(con, form);
            }
        } catch (SQLException e) {
            throw new GenericSystemException("SQL exception: " + e.getMessage(),
                    e);
        } finally {
            sqlWrapper.closeConnection(con);
        }
    }

    private void insertFormSettings(ViewObject viewObject, Form form,
                                    Connection con) throws SQLException {
        PreparedStatement insertFormSettings = sqlWrapper.doPreparedStatement(
                con,
                DBRealmManager.getSql("insert_form_settings"));
        try {
            sqlWrapper.getLongParser().setValue(insertFormSettings, 1,
                    viewObject.getId());
            sqlWrapper.getStringParser().setValue(insertFormSettings, 2,
                    form.getEntity());
            sqlWrapper.getIntParser().setValue(insertFormSettings, 3,
                    form.getGrid() ? 1:0);
            sqlWrapper.getIntParser().setValue(insertFormSettings, 4,
                    form.getDefaultactions() ? 1:0);
            sqlWrapper.getIntParser().setValue(insertFormSettings, 5,
                    form.getLabelsOrientation().getType());
            sqlWrapper.getIntParser().setValue(insertFormSettings, 6,
                    form.getMyqueweb() ? 1:0);
            sqlWrapper.getIntParser().setValue(insertFormSettings, 7,
                    form.getAutosearch() ? 1:0);
            sqlWrapper.getIntParser().setValue(insertFormSettings, 8,
                    form.getVisible() ? 1 : 0);
            sqlWrapper.executeUpdate(insertFormSettings);
        } finally {
            insertFormSettings.close();
        }
    }

    private void updateFormSettings(Form form, ViewObject viewObject,
                                    Connection con) throws SQLException {
        PreparedStatement deleteFormSettings = sqlWrapper.doPreparedStatement(
                con,
                DBRealmManager.getSql("delete_form_settings"));
        try {
            sqlWrapper.getLongParser().setValue(deleteFormSettings, 1,
                    viewObject.getId());
            sqlWrapper.executeUpdate(deleteFormSettings);
        } finally {
            deleteFormSettings.close();
        }
        insertFormSettings(viewObject, form, con);
    }

    private void insertLinkedDatasets(Form form, Connection con)
            throws SQLException {
        // .. insert linked datasets for current form in cycle.
        PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("insert_linked_datasource"));
        try {
            for(int k = 0; k < form.getLinkedDatasetCount(); k++) {
                LinkedDataset linkedds = form.getLinkedDataset(k);

                sqlWrapper.getStringParser().setValue(ps, 1, form.getName());
                sqlWrapper.getStringParser().setValue(ps, 2,
                        linkedds.getForm());
                sqlWrapper.getStringParser().setValue(ps, 3,
                        linkedds.getName());
                sqlWrapper.executeUpdate(ps);
            }
        } finally {
            ps.close();
        }
    }

    private void insertExternalFields(Form form, Connection con)
            throws SQLException {
        // .. insert external fields for current form in cycle.
        PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("insert_external_field"));
        try {
            for(int k = 0; k < form.getExternalFieldCount(); k++) {
                ExternalField extfield = form.getExternalField(k);

                sqlWrapper.getStringParser().setValue(ps, 1, form.getName());
                sqlWrapper.getStringParser().setValue(ps, 2,
                        extfield.getForm());
                sqlWrapper.getStringParser().setValue(ps, 3,
                        extfield.getName());
                sqlWrapper.getStringParser().setValue(ps, 4,
                        extfield.getSourceField());
                sqlWrapper.executeUpdate(ps);
            }
        } finally {
            ps.close();
        }
    }

    private void insertExternalForms(Form form, Connection con)
            throws SQLException {
        // .. insert external forms for current form in cycle.
        PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("insert_external_form"));
        try {
            for(int k = 0; k < form.getExternalFormCount(); k++) {
                ExternalForm extform = form.getExternalForm(k);

                sqlWrapper.getStringParser().setValue(ps, 1, form.getName());
                sqlWrapper.getStringParser().setValue(ps, 2, extform.getName());
                sqlWrapper.executeUpdate(ps);
            }
        } finally {
            ps.close();
        }
    }

    private void insertExternalSets(Form form, Connection con)
            throws SQLException {
        // .. insert external sets for current form in cycle.
        PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("insert_external_set"));
        try {
            for(int k = 0; k < form.getExternalSetCount(); k++) {
                ExternalSet extset = form.getExternalSet(k);

                sqlWrapper.getStringParser().setValue(ps, 1, form.getName());
                sqlWrapper.getStringParser().setValue(ps, 2, extset.getName());
                sqlWrapper.executeUpdate(ps);
            }
        } finally {
            ps.close();
        }
    }

    private PermissionObjectType getPermission(int type) {
        PermissionObjectType ret = PermissionObjectType.FOCUS;
        if(type == SUBFOCUS_TYPE) {
            ret = PermissionObjectType.SUB_FOCUS;
        } else if(type == TAB_TYPE) {
            ret = PermissionObjectType.TAB;
        } else if(type == FORM_TYPE) {
            ret = PermissionObjectType.FORM;
        } else if(type == FIELD_TYPE) {
            ret = PermissionObjectType.FIELD;
        }
        return ret;
    }

    private void saveButtons(Connection con, Form form) throws SQLException {
        Button[] buttons = EntityHelper.FormHelper.getFormButtons(form);
        if(buttons.length != 0) {
            PreparedStatement psButton = sqlWrapper.doPreparedStatement(con,
                    DBRealmManager.getSql("insert_button"));
            try {
                for(Button button : buttons) {
                    sqlWrapper.getStringParser().setValue(psButton, 1,
                            form.getName());
                    sqlWrapper.getStringParser().setValue(psButton, 2,
                            button.getName());
                    sqlWrapper.getIntParser().setValue(psButton, 3,
                            button.getOrder());
                    if(button.getPermission() != null) {
                        sqlWrapper.getIntParser().setValue(psButton, 4,
                                button.getPermission().getType());
                    } else {
                        sqlWrapper.getIntParser().setValue(psButton, 4,
                                AccessLevel.READ.level);
                    }
                    sqlWrapper.executeUpdate(psButton);
                }
            } finally {
                psButton.close();
            }
        }
    }

    private void saveHtmlElements(Connection con, Form form)
            throws SQLException {

        Htmlelements htmlElements = form.getHtmlelements();
        if(htmlElements == null) {
            return;
        }
        
        Htmlelement[] elements = htmlElements.getHtmlelement();
        if(elements.length != 0) {
            PreparedStatement psButton = sqlWrapper.doPreparedStatement(con,
                    DBRealmManager.getSql("insert_html_element"));
            try {
                for(Htmlelement element : elements) {
                    sqlWrapper.getStringParser().setValue(psButton, 1,
                            form.getName());
                    sqlWrapper.getStringParser().setValue(psButton, 2,
                            element.getName());
                    sqlWrapper.getIntParser().setValue(psButton, 3,
                            element.getOrder());
                    sqlWrapper.executeUpdate(psButton);
                }
            } finally {
                psButton.close();
            }
        }
    }

    private void saveFormFilters(Connection con, Form form)
            throws SQLException {

        Filters filters = form.getFilters();
        if(filters == null) {
            return;
        }
        
        Filter[] elements = filters.getFilter();
        if(elements.length != 0) {
            PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                    DBRealmManager.getSql("insert_filter"));
            try {
                for(Filter element : elements) {
                    sqlWrapper.getStringParser().setValue(ps, 1,
                            form.getName());
                    sqlWrapper.getStringParser().setValue(ps, 2,
                            element.getFieldId());
                    sqlWrapper.getStringParser().setValue(ps, 3,
                            element.getByFormId());
                    sqlWrapper.getStringParser().setValue(ps, 4,
                            element.getByFieldId());
                    sqlWrapper.executeUpdate(ps);
                }
            } finally {
                ps.close();
            }
        }
    }

    private void saveLayout(Connection con, Form form) throws SQLException {
        Header[] headers = EntityHelper.FormHelper.getLayoutHeaders(form);
        if(headers.length != 0) {
            //save headers
            PreparedStatement psHeader = sqlWrapper.doPreparedStatement(con,
                    DBRealmManager.getSql("insert_header"));
            try {
                for(int i = 0; i < headers.length; i++) {
                    Header header = headers[i];
                    sqlWrapper.getStringParser().setValue(psHeader, 1,
                            form.getName());
                    sqlWrapper.getIntParser().setValue(psHeader, 2, i);
                    sqlWrapper.getIntParser().setValue(psHeader, 3,
                            header.getClientwidth());
                    sqlWrapper.executeUpdate(psHeader);
                }
            } finally {
                psHeader.close();
            }
        }
        Row[] rows = EntityHelper.FormHelper.getLayoutRows(form);
        if(rows.length != 0) {
            //save rows
            PreparedStatement psRow = sqlWrapper.doPreparedStatement(con,
                    DBRealmManager.getSql("insert_row"));
            PreparedStatement psCol = sqlWrapper.doPreparedStatement(con,
                    DBRealmManager.getSql("insert_col"));
            try {
                for(int rowOrder = 0; rowOrder < rows.length; rowOrder++) {
                    Row row = rows[rowOrder];
                    sqlWrapper.getStringParser().setValue(psRow, 1,
                            form.getName());
                    sqlWrapper.getIntParser().setValue(psRow, 2, rowOrder);
                    sqlWrapper.executeUpdate(psRow);
                    Col[] cols = row.getCol();
                    for(int colOrder = 0; colOrder < cols.length; colOrder++) {
                        Col col = cols[colOrder];
                        sqlWrapper.getStringParser().setValue(psCol, 1,
                                form.getName());
                        sqlWrapper.getIntParser().setValue(psCol, 2, rowOrder);
                        sqlWrapper.getIntParser().setValue(psCol, 3, colOrder);
                        sqlWrapper.getStringParser().setValue(psCol, 4,
                                col.getFieldid());
                        sqlWrapper.getIntParser().setValue(psCol, 5,
                                col.getRowspan());
                        sqlWrapper.getIntParser().setValue(psCol, 6,
                                col.getColspan());
                        sqlWrapper.executeUpdate(psCol);
                    }
                }
            } finally {
                psRow.close();
            }
        }
        HiddenControl[] hiddenControls = EntityHelper.FormHelper
                .getHiddenControls(form);
        if(hiddenControls.length != 0) {
            PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                    DBRealmManager.getSql("insert_hiddencontrol"));
            try {
                for(HiddenControl hiddenControl : hiddenControls) {
                    sqlWrapper.getStringParser().setValue(ps, 1,
                            form.getName());
                    sqlWrapper.getStringParser().setValue(ps, 2,
                            hiddenControl.getFieldid());
                    sqlWrapper.executeUpdate(ps);
                }
            } finally {
                ps.close();
            }
        }
    }


    /*
    * No javadoc
    * @see FocusConfigDAO#loadFocusVO
    */
    public Focus loadFocusVO(String focusName) {

        Connection con = null;
        PreparedStatement ps = null;

        Focus focus = new Focus();

        try {
            con = sqlWrapper.doConnection();

            // Load focus.
            ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                    "select_focus"));
            ps.setString(1, focusName);
            ResultSet rs = sqlWrapper.executeQuery(ps);
            if(rs.next()) {
                focus.setName(focusName);
                focus.setOrder(sqlWrapper.getIntParser().getValue(rs, 1));
                focus.setIcon(sqlWrapper.getStringParser().getValue(rs, 2));
                ps.close();

                // Fill out focus.
                fillFocusVO(focus, con);
            }

        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);
        } finally {
            sqlWrapper.closeConnection(con, ps);
        }

        return focus;
    }

    /*
     * No javadoc
     * @see FocusConfigDAO#loadAllFocusVO
     */
    public Collection<Focus> loadAllFocusVO() {

        Connection con = null;
        PreparedStatement ps = null;

        Collection<Focus> focusList = new ArrayList<Focus>();

        try {
            con = sqlWrapper.doConnection();

            // Load focuses.
            ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                    "select_all_focuses"));
            ResultSet rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                Focus focus = new Focus();
                focus.setName(sqlWrapper.getStringParser().getValue(rs, 1));
                focus.setOrder(sqlWrapper.getIntParser().getValue(rs, 2));
                focus.setIcon(sqlWrapper.getStringParser().getValue(rs, 3));
                focusList.add(focus);
            }
            ps.close();

            // Fill out focuses.
            for(Focus focus : focusList) {
                fillFocusVO(focus, con);
            }

        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);
        } finally {
            sqlWrapper.closeConnection(con, ps);
        }

        return focusList;
    }

    private void fillFocusVO(Focus focus, Connection con)
            throws SQLException {

        loadSubFocuses(con, focus);

        for(int i = 0; i < focus.getSubFocusCount(); i++) {
            SubFocus subFocus = focus.getSubFocus(i);
            loadTabs(con, subFocus);

            for(int j = 0; j < subFocus.getTabCount(); j++) {
                Tab tab = subFocus.getTab(j);
                loadForms(con, tab);

                for(int k = 0; k < tab.getFormCount(); k++) {
                    Form form = tab.getForm(k);

                    loadExternalSets(con, form);
                    loadExternalForms(con, form);
                    loadExternalFields(con, form);
                    loadInternalFields(con, form);
                    loadLinkedDatasets(con, form);
                }
            }
        }
    }

    private void loadSubFocuses(Connection con, Focus focus)
            throws SQLException {
        String focusName = focus.getName();

        PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("select_all_subfocus"));
        ps.setString(1, focusName);
        ResultSet rs = sqlWrapper.executeQuery(ps);
        while(rs.next()) {
            SubFocus subFocus = new SubFocus();
            subFocus.setFocus(focusName);
            subFocus.setName(sqlWrapper.getStringParser().getValue(rs, 1));
            subFocus.setOrder(sqlWrapper.getIntParser().getValue(rs, 2));
            subFocus.setIcon(sqlWrapper.getStringParser().getValue(rs, 3));

            focus.addSubFocus(subFocus);
            focus.putObject(subFocus.getName(), subFocus);
        }
        rs.close();
        ps.close();
    }

    private void loadTabs(Connection con, SubFocus subFocus)
            throws SQLException {
        PreparedStatement ps;
        PreparedStatement selectHelplink = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("select_helplink"));
        ResultSet helplink;
        ResultSet rs;
        String subFocusName = subFocus.getName();
        ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                "select_all_tabs"));
        ps.setString(1, subFocusName);
        rs = sqlWrapper.executeQuery(ps);
        while(rs.next()) {
            Tab tab = new Tab();
            tab.setSubFocus(subFocusName);
            tab.setName(sqlWrapper.getStringParser().getValue(rs, 1));
            tab.setOrder(sqlWrapper.getIntParser().getValue(rs, 2));
            tab.setInframelinks(
                    sqlWrapper.getIntParser().getValue(rs, 3).intValue() == 0
                            ? Boolean.FALSE:Boolean.TRUE);
            tab.setGrid(
                    sqlWrapper.getIntParser().getValue(rs, 4).intValue() == 0
                            ? Boolean.FALSE:Boolean.TRUE);
            sqlWrapper.getStringParser().setValue(selectHelplink, 1,
                    tab.getName());
            helplink = sqlWrapper.executeQuery(selectHelplink);
            if(helplink.next()) {
                tab.setHelplink(sqlWrapper.getStringParser().getValue(helplink,
                        1));
            } else {
                tab.setHelplink("");
            }
            subFocus.addTab(tab);
            subFocus.putObject(tab.getName(), tab);
        }
        rs.close();
        ps.close();
    }

    private void loadForms(Connection con, Tab tab) throws SQLException {
        PreparedStatement ps;
        ResultSet rs;
        ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                "select_all_forms"));
        ps.setString(1, tab.getName());
        rs = sqlWrapper.executeQuery(ps);
        while(rs.next()) {
            Form form = new Form();
            form.setTab(tab.getName());
            form.setName(sqlWrapper.getStringParser().getValue(rs, 1));
            form.setEntity(sqlWrapper.getStringParser().getValue(rs, 2));
            form.setOrder(sqlWrapper.getIntParser().getValue(rs, 3));
            Integer grid = sqlWrapper.getIntParser().getValue(rs, 4);
            if(grid != null) {
                form.setGrid(grid == 1 ? Boolean.TRUE:Boolean.FALSE);
            }
            Integer defaultactions = sqlWrapper.getIntParser().getValue(rs, 5);
            if(defaultactions != null) {
                form.setDefaultactions(
                        defaultactions == 1 ? Boolean.TRUE:Boolean.FALSE);
            }
            Integer labelsLayout = sqlWrapper.getIntParser().getValue(rs, 6);
            if(labelsLayout != null && labelsLayout == 1) {
                form.setLabelsOrientation(FormLabelsOrientationType.VERTICAL);
            } else {
                form.setLabelsOrientation(FormLabelsOrientationType.HORIZONTAL);
            }
            Integer myqueweb = sqlWrapper.getIntParser().getValue(rs, 7);
            if(myqueweb != null) {
                form.setMyqueweb(myqueweb == 1 ? Boolean.TRUE:Boolean.FALSE);
            }
            Integer autoSearch = sqlWrapper.getIntParser().getValue(rs, 8);
            if(autoSearch != null) {
                form.setAutosearch(
                        autoSearch == 1 ? Boolean.TRUE:Boolean.FALSE);
            }
            Integer isVisible = sqlWrapper.getIntParser().getValue(rs, 9);
            if (isVisible != null) {
                form.setVisible(isVisible != 0 ? Boolean.TRUE : Boolean.FALSE);
            }
            else {
                form.setVisible(true);
            }
            loadButtons(con, form);
            loadHtmlElements(con, form);
            loadHeaders(con, form);
            loadRows(con, form);
            loadHiddenControls(con, form);
            loadLinks(con, form);
            loadFormFilters(con, form);

            tab.addForm(form);
            tab.putObject(form.getName(), form);
        }
        rs.close();
        ps.close();
    }

    private void loadButtons(Connection con, Form form) throws SQLException {
        PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("select_button"));
        try {
            sqlWrapper.getStringParser().setValue(ps, 1, form.getName());
            ResultSet rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                Button button = new Button();
                button.setName(sqlWrapper.getStringParser().getValue(rs, 1));
                button.setPermission(loadPermission(
                        sqlWrapper.getIntParser().getValue(rs, 2)));
                EntityHelper.FormHelper.addFormButton(form, button);
            }
        }
        finally {
            ps.close();
        }
    }

    private void loadHtmlElements(Connection con, Form form)
            throws SQLException {
        PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("select_html_element"));
        try {
            sqlWrapper.getStringParser().setValue(ps, 1, form.getName());
            ResultSet rs = sqlWrapper.executeQuery(ps);
            List<Htmlelement> elements = new ArrayList<Htmlelement>();
            while(rs.next()) {
                Htmlelement htmlElement = new Htmlelement();
                htmlElement.setName(sqlWrapper.getStringParser().getValue(rs,
                        1));
                elements.add(htmlElement);
            }
            if(!elements.isEmpty()) {
                Htmlelements htmlElements = form.getHtmlelements();
                if(htmlElements == null) {
                    htmlElements = new Htmlelements();
                }

                for(Htmlelement element : elements) {
                    htmlElements.addHtmlelement(element);
                }

                form.setHtmlelements(htmlElements);
            }
        }
        finally {
            ps.close();
        }
    }

    private void loadFormFilters(Connection con, Form form)
            throws SQLException {

        PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("select_filter"));
        try {
            sqlWrapper.getStringParser().setValue(ps, 1, form.getName());
            ResultSet rs = sqlWrapper.executeQuery(ps);
            List<Filter> elements = new ArrayList<Filter>();
            while(rs.next()) {
                Filter element = new Filter();
                element.setFieldId(
                        sqlWrapper.getStringParser().getValue(rs, 1));
                element.setByFormId(
                        sqlWrapper.getStringParser().getValue(rs, 2));
                element.setByFieldId(
                        sqlWrapper.getStringParser().getValue(rs, 3));
                elements.add(element);
            }
            if(!elements.isEmpty()) {
                Filters filters = form.getFilters();
                if(filters == null) {
                    filters = new Filters();
                }
        
                for(Filter element : elements) {
                    filters.addFilter(element);
                }
        
                form.setFilters(filters);
            }
        }
        finally {
            ps.close();
        }
    }

    private void loadHeaders(Connection con, Form form) throws SQLException {
        PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("select_header"));
        try {
            sqlWrapper.getStringParser().setValue(ps, 1, form.getName());
            ResultSet rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                Header header = new Header();
                header.setClientwidth(sqlWrapper.getIntParser().getValue(rs,
                        1));
                EntityHelper.FormHelper.addHeaderIntoLayout(form, header);
            }
        }
        finally {
            ps.close();
        }
    }

    private void loadRows(Connection con, Form form) throws SQLException {
        PreparedStatement rowSt = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("select_row"));
        PreparedStatement colSt = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("select_col"));
        try {
            sqlWrapper.getStringParser().setValue(rowSt, 1, form.getName());
            ResultSet rows = sqlWrapper.executeQuery(rowSt);
            while(rows.next()) {
                Row row = new Row();
                row.setOrder(sqlWrapper.getIntParser().getValue(rows, 1));
                sqlWrapper.getStringParser().setValue(colSt, 1, form.getName());
                sqlWrapper.getIntParser().setValue(colSt, 2, row.getOrder());
                ResultSet cols = sqlWrapper.executeQuery(colSt);
                while(cols.next()) {
                    Col col = new Col();
                    String fieldId = sqlWrapper.getStringParser().getValue(cols,
                            1);
                    col.setFieldid(fieldId != null ? fieldId:"");
                    col.setRowspan(sqlWrapper.getIntParser().getValue(cols, 2));
                    col.setColspan(sqlWrapper.getIntParser().getValue(cols, 3));
                    row.addCol(col);
                }
                EntityHelper.FormHelper.addLayoutRow(form, row);
            }
        }
        finally {
            colSt.close();
            rowSt.close();
        }
    }

    private void loadExternalSets(Connection con, Form form)
            throws SQLException {
        PreparedStatement ps;
        ResultSet rs;
        ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                "select_external_set"));
        ps.setString(1, form.getName());
        rs = sqlWrapper.executeQuery(ps);
        while(rs.next()) {
            ExternalSet extSet = new ExternalSet();
            extSet.setName(sqlWrapper.getStringParser().getValue(rs, 1));
            form.addExternalSet(extSet);
        }
        rs.close();
        ps.close();
    }

    private void loadExternalForms(Connection con, Form form)
            throws SQLException {
        PreparedStatement ps;
        ResultSet rs;
        ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                "select_external_form"));
        ps.setString(1, form.getName());
        rs = sqlWrapper.executeQuery(ps);
        while(rs.next()) {
            ExternalForm extForm = new ExternalForm();
            extForm.setName(sqlWrapper.getStringParser().getValue(rs, 1));
            form.addExternalForm(extForm);
        }
        rs.close();
        ps.close();
    }

    private void loadExternalFields(Connection con, Form form)
            throws SQLException {
        PreparedStatement ps;
        ResultSet rs;
        ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                "select_external_field"));
        ps.setString(1, form.getName());
        rs = sqlWrapper.executeQuery(ps);
        while(rs.next()) {
            ExternalField extField = new ExternalField();
            extField.setForm(sqlWrapper.getStringParser().getValue(rs, 1));
            extField.setName(sqlWrapper.getStringParser().getValue(rs, 2));
            extField.setSourceField(sqlWrapper.getStringParser().getValue(rs,
                    3));
            form.addExternalField(extField);
        }
        rs.close();
        ps.close();
    }

    private void loadInternalFields(Connection con, Form form)
            throws SQLException {
        PreparedStatement ps;
        ResultSet rs;
        ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                "select_internal_field"));
        ps.setString(1, form.getName());
        rs = sqlWrapper.executeQuery(ps);
        while(rs.next()) {
            InternalField intField = new InternalField();
            intField.setForm(sqlWrapper.getStringParser().getValue(rs, 1));
            intField.setName(sqlWrapper.getStringParser().getValue(rs, 2));
            form.addInternalField(intField);
        }
        rs.close();
        ps.close();
    }

    private void loadLinkedDatasets(Connection con, Form form)
            throws SQLException {
        PreparedStatement ps;
        ResultSet rs;
        ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                "select_linked_datasource"));
        ps.setString(1, form.getName());
        rs = sqlWrapper.executeQuery(ps);
        while(rs.next()) {
            LinkedDataset linkedDataset = new LinkedDataset();
            linkedDataset.setForm(sqlWrapper.getStringParser().getValue(rs, 1));
            linkedDataset.setName(sqlWrapper.getStringParser().getValue(rs, 2));
            form.addLinkedDataset(linkedDataset);
        }
        rs.close();
        ps.close();
    }

    private void loadHiddenControls(Connection con, Form form)
            throws SQLException {
        PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("select_hiddencontrol"));
        ResultSet rs;
        try {
            sqlWrapper.getStringParser().setValue(ps, 1, form.getName());
            rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                HiddenControl hiddenControl = new HiddenControl();
                hiddenControl.setFieldid(sqlWrapper.getStringParser().getValue(
                        rs, 1));
                EntityHelper.FormHelper.addHiddenControl(form, hiddenControl);
            }
        }
        finally {
            ps.close();
        }
    }

    private void saveLinks(Connection con, Form form) throws SQLException {
        Link[] links = EntityHelper.FormHelper.getFormLinks(form);
        if(links.length != 0) {
            PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                    DBRealmManager.getSql("insert_link"));
            try {
                for(Link link : links) {
                    sqlWrapper.getStringParser().setValue(ps, 1,
                            form.getName());
                    sqlWrapper.getStringParser().setValue(ps, 2,
                            link.getField());
                    sqlWrapper.getStringParser().setValue(ps, 3,
                            link.getForm());
                    sqlWrapper.executeUpdate(ps);
                }
            }
            finally {
                ps.close();
            }
        }
    }

    private void loadLinks(Connection con, Form form) throws SQLException {
        PreparedStatement ps = sqlWrapper.doPreparedStatement(con,
                DBRealmManager.getSql("select_link"));
        try {
            sqlWrapper.getStringParser().setValue(ps, 1, form.getName());
            ResultSet rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                Link link = new Link();
                link.setField(sqlWrapper.getStringParser().getValue(rs, 1));
                link.setForm(sqlWrapper.getStringParser().getValue(rs, 2));
                EntityHelper.FormHelper.addFormLink(form, link);
            }
        }
        finally {
            ps.close();
        }
    }

    private PermissionsType loadPermission(int permissionType) {
        switch(permissionType) {
            case PermissionsType.READ_TYPE:
                return PermissionsType.READ;
            case PermissionsType.WRITE_TYPE:
                return PermissionsType.WRITE;
            case PermissionsType.OWNER_TYPE:
                return PermissionsType.OWNER;
            case PermissionsType.FULLCONTROL_TYPE:
                return PermissionsType.FULLCONTROL;
        }
        return PermissionsType.READ;
    }
}
