/*
 * 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.eqlext.actions;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.jxb.entity.Dataset;
import com.queplix.core.jxb.entity.Efield;
import com.queplix.core.jxb.entity.Entity;
import com.queplix.core.modules.config.jxb.CustomField;
import com.queplix.core.modules.config.jxb.Form;
import com.queplix.core.modules.config.jxb.LinkedDataset;
import com.queplix.core.modules.config.utils.EntityHelper;
import com.queplix.core.modules.eql.EQLDRes;
import com.queplix.core.modules.eql.EQLDateObject;
import com.queplix.core.modules.eql.EQLObject;
import com.queplix.core.modules.eql.EQLReqField;
import com.queplix.core.modules.eql.EQLRes;
import com.queplix.core.modules.eql.EQLResCell;
import com.queplix.core.modules.eql.EQLResRecord;
import com.queplix.core.modules.eql.error.EQLException;
import com.queplix.core.modules.eql.error.UserQueryParseException;
import com.queplix.core.modules.eql.parser.EQLIntPreparedStatement;
import com.queplix.core.modules.eqlext.actions.filters.EntityGRFilterFactory;
import com.queplix.core.modules.eqlext.jxb.gr.ReqEntity;
import com.queplix.core.modules.eqlext.jxb.gr.ReqFilter;
import com.queplix.core.modules.eqlext.jxb.gr.ReqFilters;
import com.queplix.core.modules.eqlext.jxb.gr.ReqFiltersTypeItem;
import com.queplix.core.modules.eqlext.jxb.gr.Reqs;
import com.queplix.core.modules.eqlext.jxb.gr.ResDataset;
import com.queplix.core.modules.eqlext.jxb.gr.ResField;
import com.queplix.core.modules.eqlext.jxb.gr.ResHeader;
import com.queplix.core.modules.eqlext.jxb.gr.ResHeaderDataset;
import com.queplix.core.modules.eqlext.jxb.gr.ResHeaderField;
import com.queplix.core.modules.eqlext.jxb.gr.ResLinkedDataset;
import com.queplix.core.modules.eqlext.jxb.gr.ResRecord;
import com.queplix.core.modules.eqlext.jxb.gr.types.DataSType;
import com.queplix.core.modules.eqlext.jxb.gr.types.SqlSType;
import com.queplix.core.modules.eqlext.utils.ExtDateParser;
import com.queplix.core.utils.StringHelper;
import com.queplix.core.utils.xml.XMLBinding;
import com.queplix.core.utils.xml.XMLFactory;
import com.queplix.core.utils.xml.XMLHelper;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Writer;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>Abstract Get Records Action</p>
 *
 * @author [ALB] Baranov Andrey
 * @version $Revision: 1.3 $ $Date: 2006/06/02 13:42:14 $
 */

public abstract class AbstractGRAction
        extends AbstractAction implements GRAction {

    // ----------------------------------------------------- constants

    // Ignore words detector
    protected static final GRIgnoreWordsDetector iwDetector
            = new GRIgnoreWordsDetector();

    // Mapper a user operation to EQL
    private static final String[] DEFAULT_EQL_OP_MAPPINGS = {
            "=",
            "=",
            "!=",
            ">",
            "<",
            ">=",
            "<="
    };

    // ----------------------------------------------------- variables

    // IN (request) parameters:
    protected ReqFilters reqFilters;
    protected String eqlFilters;
    protected int page;
    protected int pageSize;
    protected boolean doCount;
    protected boolean ignoreSendOnRequest;

    protected Writer writer;

    // OUT (response) parameters:
    private ResRecord[] resRecords;
    private EQLRes eqlRes;
    private int count = UNDEFINED_COUNT;
    private int rows = 0;
    private boolean hasMore = false;

    // SPECIAL parameters:
    // eql query
    protected String eqlQuery;

    // eql prepared statement
    protected EQLIntPreparedStatement eqlPS = new EQLIntPreparedStatement();

    // prepared satetement parameter counter
    private int psCount = 1;

    protected boolean ignoreLower = false;

    // ----------------------------------------------------- public static method

    /**
     * Call process method
     *
     * @param ctx    action context
     * @param reqs   Reqs object
     * @param writer Writer object
     * @return GRAction object
     * @throws EQLException
     */
    public static final GRAction process(ActionContext ctx, Reqs reqs,
                                         Writer writer)
            throws EQLException {

        // Init GR action.
        AbstractGRAction action;

        ReqEntity reqEntity = reqs.getReq().getReqEntity();
        if(reqEntity == null) {
            // .. get fields
            action = new FieldsGRAction();

        } else if(reqEntity.getNew().booleanValue()) {
            // .. get new entity
            action = new NewGRAction();

        } else {
            // .. get entity filter
            action = EntityGRFilterFactory.getEntityGRFilter(reqs);
            if(action == null) {
                // .. select entity
                action = new EntityGRAction();
            }
        }

        // Set context.
        action.setActionContext(ctx);

        // Set writer.
        if(writer != null) {
            action.setWriter(writer);
        }

        // Init action.
        action.init(reqs);

        // Call process.
        action.process();

        // Ok.
        return action;
    }

    // ----------------------------------------------------- setters

    // Set writer for records writing.

    public final void setWriter(Writer writer) {
        this.writer = writer;
    }

    // ----------------------------------------------------- getters

    // Get count records in database

    public final int getCount() {
        return count;
    }

    // Get count rows retrived from database
    public final int getRows() {
        return rows;
    }

    // Has more records in database flag
    public final boolean hasMore() {
        return hasMore;
    }

    // EQL response getter
    public final EQLRes getEQLRes() {
        return eqlRes;
    }

    // ResRecord array getter
    public final ResRecord[] getResRecords() {
        if(writer != null) {
            throw new IllegalStateException("Output stream is set");
        }
        return resRecords;
    }

    // Writer getter
    public final Writer getWriter() {
        if(writer == null) {
            throw new IllegalStateException("Output stream is not set");
        }
        return writer;
    }

    // ----------------------------------------------------- process method

    /*
     * No javadoc
     * @see Action#process
     */

    public final void process()
            throws EQLException {
// [MVT] do not uncomment line below - otherwise charts data will be broken
//        if( pageSize != UNDEFINED_PAGESIZE ) {
        // build EQL query
        buildEQL();
        DEBUG("Build EQL query - ok");

        // execute EQL query
        eqlRes = callEQLManager();
        DEBUG("Call EQL manager - ok");

        // get count
        doCount();
        DEBUG("Do count - ok");
//        }

        // initialize response OUT attributes

        { // 1. rows - rows in package
            rows = (eqlRes == null) ? 0:eqlRes.size();
        }

        { // 2. hasMore - is there any available records...
            Object o = null;
            if(eqlRes != null) {
                o = eqlRes.getMetaData().getParam(HAS_MORE_PARAM);
                if(o != null) {
                    hasMore = ((Boolean) o).booleanValue();
                }
            }
        }

        { // 3. count - count records in database...
            if(count == UNDEFINED_COUNT && rows > 0) {

                // Calculate count by the hands:
                //  count = page * pageSize + rows + ((hasMore) ? 1 : 0)

                if(page != UNDEFINED_PAGE && pageSize != UNDEFINED_PAGESIZE) {
                    count = page * pageSize + rows + (hasMore ? 1:0);
                } else {
                    count = rows;
                }
            }
        }

        DEBUG("Start read records...");

        // get Res records
        if(writer != null) {
            // write records
            /*if( rows == 0 ) {
                resRecords = createFakeResRecord( getFields() );
                XMLHelper.writeObject( resRecords[0], writer, true );
            } else {
                createResRecords( eqlRes, writer );
            }*///todo remove if need empty record in empty response
            if(rows == 0) {
                resRecords = new ResRecord[0];
                XMLHelper.writeObject(resRecords[0], writer, true);
            } else {
                createResRecords(eqlRes, writer);
            }
        } else {
            // collect records
            /*if( rows == 0 ) {
                resRecords = createFakeResRecord( getFields() );
            } else {
                resRecords = createResRecords( eqlRes );
            }*///todo remove if need empty record in empty response
            if(rows == 0) {
                resRecords = new ResRecord[0];
            } else {
                resRecords = createResRecords(eqlRes);
            }
        }

        if(getLogger().isDebugEnabled()) {
            DEBUG("   # page=" + page);
            DEBUG("   # pageSize=" + pageSize);
            DEBUG("   # count (in database)=" + count);
            DEBUG("   # rows (package size)=" + rows);
            DEBUG("   # has more=" + hasMore);
        }
    }

    // ----------------------------------------------------- protected methods

    //
    // Initialization.
    //

    protected void init(Reqs reqs) {
        page = reqs.getPage().intValue();
        pageSize = reqs.getPagesize().intValue();
        doCount = reqs.getDocount().booleanValue();
        ignoreSendOnRequest = reqs.getIgnoreSendOnRequest().booleanValue();
        reqFilters = reqs.getReqFilters();
        eqlFilters = reqs.getEqlFilters();
    }

    // ----------------------------------------------------- abstract methods

    /**
     * Call EQL manager
     *
     * @return EQL response
     * @throws EQLException
     */
    protected abstract EQLRes callEQLManager()
            throws EQLException;

    /**
     * Call EQL manager for counting
     *
     * @return int
     * @throws EQLException
     */
    protected abstract int callEQLManagerForCount()
            throws EQLException;

    /**
     * Create ResRecord objects using EQLRes
     *
     * @param eqlRes EQLRes object
     * @return array of ResRecord objects or null
     * @throws EQLException
     */
    protected abstract ResRecord[] createResRecords(EQLRes eqlRes)
            throws EQLException;

    /**
     * Write ResRecord objects in <code>writer</code>
     *
     * @param eqlRes EQLRes object
     * @param writer given writer
     * @throws EQLException
     */
    protected abstract void createResRecords(EQLRes eqlRes, Writer writer)
            throws EQLException;

    // ----------------------------------------------------- protected methods

    /**
     * Do counting.
     * Initialize "count" attribute.
     *
     * @throws EQLException
     */
    protected void doCount()
            throws EQLException {

        if(doCount) {

            if(page == UNDEFINED_PAGE) {
                // we got all records. don't count

            } else if(pageSize > eqlRes.size()) {
                // we got less than expected. don't count

            } else {
                count = callEQLManagerForCount();
            }
        }
    }

    /**
     * Build EQL query
     *
     * @throws UserQueryParseException
     */
    protected void buildEQL()
            throws UserQueryParseException {

        StringBuffer mainEql = new StringBuffer("SELECT ");

        // build EQL meta information
        buildEQLRequestMetaInf(mainEql);

        // build EQL select clause
        buildEQLSelectClause(mainEql);

        boolean hasFilters = (eqlFilters != null) ||
                (reqFilters != null &&
                        reqFilters.getReqFiltersTypeItemCount() > 0);

        if(hasFilters) {
            // build EQL where clause
            StringBuffer whereClause = new StringBuffer(" WHERE ");
            buildEQLWhereClause(whereClause);
            mainEql.append(whereClause.toString());
        }

        // build EQL order clause
        buildEQLOrderClause(mainEql);

        // set class variable
        eqlQuery = mainEql.toString();
    }

    /**
     * Build EQL select clause
     *
     * @param eql EQL string
     */
    protected void buildEQLSelectClause(StringBuffer eql) {
        // do nothing
    }

    /**
     * Build EQL order clause
     *
     * @param eql EQL string
     */
    protected void buildEQLOrderClause(StringBuffer eql) {
        // do nothing
    }

    /**
     * Build EQL request meta information
     *
     * @param eql EQL string
     */
    protected void buildEQLRequestMetaInf(StringBuffer eql) {
        eql.append("/* ");

        // set PAGE attribute
        if(page != UNDEFINED_PAGE) {
            eql.append(PAGE_PARAM).append("=").append(page).append(",");
        }

        // set PAGESIZE attribute
        if(pageSize != UNDEFINED_PAGESIZE) {
            eql.append(PAGE_SIZE_PARAM).append("=").append(pageSize).append(
                    ",");
        }

        eql.append(" */ ");
    }

    /**
     * Build EQL where clause
     *
     * @param eql EQL string
     * @throws UserQueryParseException
     */
    protected void buildEQLWhereClause(StringBuffer eql)
            throws UserQueryParseException {

        if(eqlFilters != null) {
            eql.append(eqlFilters);
        } else {
            buildEQLWhereClause(eql, reqFilters);
        }
    }

    /**
     * Build EQL where clause
     *
     * @param eql        EQL string
     * @param reqFilters ReqFilters object
     * @throws UserQueryParseException
     */
    protected void buildEQLWhereClause(StringBuffer eql, ReqFilters reqFilters)
            throws UserQueryParseException {

        String boolType = reqFilters.getType().toString();
        int count = reqFilters.getReqFiltersTypeItemCount();
        for(int i = 0; i < count; i++) {

            if(i > 0) {
                eql.append(" ").append(boolType).append(" ");
            }

            ReqFiltersTypeItem typeItem = reqFilters.getReqFiltersTypeItem(i);
            ReqFilters nextReqFilters = typeItem.getReqFilters();

            if(nextReqFilters != null) {
                eql.append("(");
                buildEQLWhereClause(eql, nextReqFilters);
                eql.append(")");

            } else {
                ReqFilter reqFilter = typeItem.getReqFilter();
                String _boolType = reqFilter.getConditiontype().toString();
                boolean useTextField = reqFilter.getUsetextfield()
                        .booleanValue();
                String entityName = reqFilter.getEntity();
                String fieldName = reqFilter.getName();
                Entity entity = ctx.getEntityViewConfig(entityName);
                Efield field = EntityHelper.getEfield(fieldName, entity);
                String searchFunction = field.getEqlextSrchfunc();

                int _count = reqFilter.getReqFilterValueCount();
                if(_count > 0) {
                    eql.append("(");
                    for(int j = 0; j < _count; j++) {
                        if(j > 0) {
                            eql.append(" ").append(_boolType).append(" ");
                        }

                        String userQuery = reqFilter.getReqFilterValue(j);
                        String eqlQuery;
                        if(searchFunction != null) {
                            eqlQuery = parseUserQueryInsideSearchFunction(
                                    searchFunction, entity, field, useTextField,
                                    userQuery);
                        } else {
                            eqlQuery = parseUserQuery(entity, field,
                                    useTextField, userQuery);
                        }
                        eql.append(eqlQuery);
                    }
                    eql.append(")");

                } else {
                    String eqlQuery = parseUserQuery(entity, field,
                            useTextField, StringHelper.EMPTY_VALUE);
                    eql.append(eqlQuery);
                }
            }
        }
    }

    /**
     * Parse user input query and put it inside EQL serach function
     * <code>searchFunc</code>.
     *
     * @param searchFunc   EQL search function name
     * @param entity       Entity object
     * @param field        Efield object
     * @param useTextField treat field as textfield or not
     * @param query        user query
     * @return part of EQL query
     * @throws UserQueryParseException
     */
    protected String parseUserQueryInsideSearchFunction(
            String searchFunc,
            Entity entity,
            Efield field,
            boolean useTextField,
            String query)
            throws UserQueryParseException {

        return searchFunc +
                "(" +
                field.getId() + ", " +
                StringHelper.java2sql(query) +
                ")";
    }

    /**
     * Parse user input query
     *
     * @param entity       Entity object
     * @param field        Efield object
     * @param useTextField treat field as textfield or not
     * @param query        user query
     * @return part of EQL query
     * @throws UserQueryParseException
     */
    protected String parseUserQuery(Entity entity,
                                    Efield field,
                                    boolean useTextField,
                                    String query)
            throws UserQueryParseException {

        StringBuffer ret = new StringBuffer();
        StringBuffer cond = new StringBuffer();

        query = iwDetector.replace(query);
        int size = (query == null) ? 0:query.length();

        boolean evenEscapes = true;
        for(int i = 0; i < size; i++) {
            char c = query.charAt(i);

            if(c == AND_CHARACTER && evenEscapes) {
                parseUserCondition(cond.toString(), entity, field, useTextField,
                        ret);
                ret.append(" AND ");
                cond.delete(0, cond.length());

            } else if(c == OR_CHARACTER && evenEscapes) {
                parseUserCondition(cond.toString(), entity, field, useTextField,
                        ret);
                ret.append(" OR ");
                cond.delete(0, cond.length());

            } else if(c == ESCAPE_CHARACTER) {
                cond.append(c);
                evenEscapes = !evenEscapes;

            } else {
                cond.append(c);
                evenEscapes = true;
            }
        }
        parseUserCondition(cond.toString(), entity, field, useTextField, ret);

        return ret.toString();
    }

    /**
     * Parse user query condition
     *
     * @param cond         user query conition
     * @param entity       given entity
     * @param field        given field
     * @param useTextField treat field as textfield
     * @param ret          result string buffer
     * @throws UserQueryParseException
     */
    protected void parseUserCondition(String cond,
                                      Entity entity,
                                      Efield field,
                                      boolean useTextField,
                                      StringBuffer ret)
            throws UserQueryParseException {

        if(cond == null) {
            throw new NullPointerException("Condition is NULL");
        }

        String leftMember;
        if(useTextField) {
            // add text field
            leftMember = "#" + field.getId();
        } else {
            // add field
            leftMember = field.getId();
        }

        // try to detect condition operation
        int cond_op;
        String value;
        String s = cond.trim();
        if(s.indexOf(QUERY_GTEQ_OP) == 0) {
            cond_op = GTEQ_OP;
            value = s.substring(2);

        } else if(s.indexOf(QUERY_LTEQ_OP) == 0) {
            cond_op = LTEQ_OP;
            value = s.substring(2);

        } else if(cond.indexOf(QUERY_GT_OP) == 0) {
            cond_op = GT_OP;
            value = cond.substring(1);

        } else if(s.indexOf(QUERY_LT_OP) == 0) {
            cond_op = LT_OP;
            value = s.substring(1);

        } else if(s.indexOf(QUERY_NE_OP) == 0) {
            cond_op = NE_OP;
            value = s.substring(1);

        } else if(s.indexOf(QUERY_EQ_OP) == 0) {
            cond_op = EQ_OP;
            value = s.substring(1);

        } else {
            cond_op = NONE_OP;
            value = cond;
        }

        // trim value if operation present
        if(cond_op != NONE_OP && value != null) {
            value = value.trim();
        }

        if(useTextField) {
            // Use text field
            String lrefEntityName = field.getListref().getEntity();
            String lrefFieldName = field.getListref().getEfield();
            entity = ctx.getEntityViewConfig(lrefEntityName);
            field = EntityHelper.getEfield(lrefFieldName, entity);
        }

        ret.append(" ");

        if(value == null || value.equalsIgnoreCase(StringHelper.NULL_VALUE)) {

            //
            // Null condition
            //

            ret.append(leftMember);
            if(cond_op == NONE_OP || cond_op == EQ_OP) {
                ret.append(" IS NULL");
            } else if(cond_op == NE_OP) {
                ret.append(" IS NOT NULL");
            } else {
                throwUserQueryParseException(entity, field, "NULL");
            }

        } else {

            //
            // Not null condition
            //

            int datatype = field.getDatatype().getType();
            switch(datatype) {
                case DataSType.INT_TYPE:
                case DataSType.LONG_TYPE:
                case DataSType.FLOAT_TYPE:
                case DataSType.TIME_TYPE:
                    parseUserSimpleCondition(leftMember, value, cond_op, entity,
                            field, ret);
                    break;

                case DataSType.STRING_TYPE:

                    // string type
                    parseUserStringCondition(leftMember, value, cond_op, entity,
                            field, ret);
                    break;

                case DataSType.TIMESTAMP_TYPE:
                case DataSType.DATE_TYPE:

                    // date type
                    parseUserDateCondition(leftMember, value, cond_op, entity,
                            field, ret);
                    break;

                case DataSType.MEMO_TYPE:

                    // memo type
                    parseUserMemoCondition(leftMember, cond, entity, field,
                            ret);
                    break;

                default:

                    // default behaivor
                    throw new GenericSystemException(
                            "Unsupported datatype '" + datatype + "'");
            }
        }
        ret.append(" ");
    }

    /**
     * Parse user query condition
     *
     * @param leftMember  left member of query
     * @param rightMember right member of query
     * @param cond_op     number of condition operation
     * @param entity      given entity
     * @param field       given field
     * @param ret         result string buffer
     * @throws UserQueryParseException
     */
    protected void parseUserSimpleCondition(String leftMember,
                                            String rightMember,
                                            int cond_op,
                                            Entity entity,
                                            Efield field,
                                            StringBuffer ret)
            throws UserQueryParseException {

        eqlPS.setObject(psCount++, convertString2EQLObject(entity, field,
                rightMember));

        ret.append(leftMember);
        ret.append(DEFAULT_EQL_OP_MAPPINGS[cond_op]);
        ret.append(PS_CHARACTER);
    }

    /**
     * Parse string user query condition
     *
     * @param leftMember  left member of query
     * @param rightMember right member of query
     * @param cond_op     number of condition operation
     * @param entity      given entity
     * @param field       given field
     * @param ret         result string buffer
     * @throws UserQueryParseException
     */
    protected void parseUserStringCondition(String leftMember,
                                            String rightMember,
                                            int cond_op,
                                            Entity entity,
                                            Efield field,
                                            StringBuffer ret)
            throws UserQueryParseException {

        if(cond_op != NONE_OP && cond_op != NE_OP) {
            //
            // Use default operation
            //
            parseUserSimpleCondition(leftMember, rightMember, cond_op, entity,
                    field, ret);
            return;
        }

        //
        // Parse string
        //
        GRStringParser grsParser = new GRStringParser(entity, field);
        rightMember = grsParser.toEQL(rightMember);

        if(grsParser.exactSearch) {
            //
            // No any advanced search options found - use default operation
            //
            parseUserSimpleCondition(leftMember, rightMember, cond_op, entity,
                    field, ret);
            return;
        }

        // call user transformer and add in EQL interpreter prepared statement
        eqlPS.setObject(psCount++, convertString2EQLObject(entity, field,
                rightMember));
        if(!grsParser.hasDog && !ignoreLower) {
            ret.append("LOWER(").append(leftMember).append(")");
            if(cond_op == NE_OP) {
                ret.append(" NOT LIKE ");
            } else {
                ret.append(" LIKE ");
            }
            ret.append("LOWER(").append(PS_CHARACTER).append(")");
        } else {
            ret.append(leftMember);
            if(cond_op == NE_OP) {
                ret.append(" NOT LIKE ");
            } else {
                ret.append(" LIKE ");
            }
            ret.append(PS_CHARACTER);
        }
    }

    /**
     * Parse string user date condition
     *
     * @param leftMember  left member of query
     * @param rightMember right member of query
     * @param cond_op     number of condition operation
     * @param entity      given entity
     * @param field       given field
     * @param ret         result string buffer
     * @throws UserQueryParseException
     */
    protected void parseUserDateCondition(String leftMember,
                                          String rightMember,
                                          int cond_op,
                                          Entity entity,
                                          Efield field,
                                          StringBuffer ret)
            throws UserQueryParseException {

        if(cond_op != NONE_OP) {
            //
            // Use default operation
            //
            parseUserSimpleCondition(leftMember, rightMember, cond_op, entity,
                    field, ret);
            return;
        }

        String firstRightMember = rightMember;
        String secondRightMember = rightMember;

        int pos = rightMember.indexOf(BETWEEN_OP);
        if(pos > 0) {
            //
            // found BETWEEN operation
            //
            firstRightMember = rightMember.substring(0, pos);
            secondRightMember = rightMember.substring(
                    pos + BETWEEN_OP.length());
        }

        // detect is second date has time part or not for comparing duration
        // (only for timestamp fields)
        boolean useDuration = false;
        if(field.getSqltype().getType() == SqlSType.TIMESTAMP_TYPE) {
            useDuration = !ExtDateParser.hasTimePart(secondRightMember);
        }

        if(getLogger().isInfoEnabled()) {
            INFO("[parse date] 1: '" + firstRightMember +
                    "' 2: '" + secondRightMember +
                    "' useDuration=" + useDuration);
        }

        // get first EQL date object
        EQLDateObject dateObject1 = (EQLDateObject)
                convertString2EQLObject(entity, field, firstRightMember);

        // get second EQL date object
        EQLDateObject dateObject2;
        if(firstRightMember.equals(secondRightMember)) {
            dateObject2 = dateObject1;
        } else {
            dateObject2 = (EQLDateObject) convertString2EQLObject(entity, field,
                    secondRightMember);
        }

        boolean exchangeDates = dateObject1.getValue().compareTo(
                dateObject2.getValue()) > 0;
        if(exchangeDates) {
            // exchange dates so they are in ascending order
            EQLDateObject dateTemp = dateObject1;
            dateObject1 = dateObject2;
            dateObject2 = dateTemp;
        }

        if(useDuration) {
            // get second EQL date object (plus one day)
            if(exchangeDates) {
                dateObject2 = (EQLDateObject)
                        convertString2EQLObject(entity, field,
                                firstRightMember + "+1d");
            } else {
                dateObject2 = (EQLDateObject)
                        convertString2EQLObject(entity, field,
                                secondRightMember + "+1d");
            }
        }

        if(getLogger().isInfoEnabled()) {
            INFO("[parse date] 1: '" + dateObject1.toString() +
                    "' 2: '" + dateObject2.toString());
        }

        if(useDuration) {
            eqlPS.setObject(psCount++, dateObject1);
            eqlPS.setObject(psCount++, dateObject2);

            ret.append(leftMember);
            ret.append(" >= ");
            ret.append(PS_CHARACTER);
            ret.append(" AND ");
            ret.append(leftMember);
            ret.append(" < ");
            ret.append(PS_CHARACTER);

        } else if(!firstRightMember.equals(secondRightMember)) {
            eqlPS.setObject(psCount++, dateObject1);
            eqlPS.setObject(psCount++, dateObject2);

            ret.append(leftMember);
            ret.append(" >= ");
            ret.append(PS_CHARACTER);
            ret.append(" AND ");
            ret.append(leftMember);
            ret.append(" <= ");
            ret.append(PS_CHARACTER);

        } else {
            eqlPS.setObject(psCount++, dateObject1);

            ret.append(leftMember);
            ret.append(" = ");
            ret.append(PS_CHARACTER);
        }
    }

    /**
     * Parse memo user query condition
     *
     * @param leftMember left member of query
     * @param rightCond  right condition (with operation) of query
     * @param entity     given entity
     * @param field      given field
     * @param ret        result string buffer
     * @throws UserQueryParseException
     */
    protected void parseUserMemoCondition(String leftMember,
                                          String rightCond,
                                          Entity entity,
                                          Efield field,
                                          StringBuffer ret)
            throws UserQueryParseException {

        //
        // Parse string
        //
        GRStringParser grsParser = new GRStringParser(entity, field);
        rightCond = grsParser.toEQL(rightCond);

        // call user transformer and add in EQL interpreter prepared statement
        eqlPS.setObject(psCount++, convertString2EQLObject(entity, field,
                rightCond));

        ret.append(leftMember);
        ret.append(" LIKE ");
        ret.append(PS_CHARACTER);
    }

    /**
     * Create new ResRecord object
     *
     * @param eqlRes EQLRes object
     * @param pos    position in EQL response
     * @return array of ResRecord objects or null
     * @throws EQLException
     */
    protected ResRecord createResRecord(EQLRes eqlRes, int pos)
            throws EQLException {

        // create new record
        ResRecord resRecord = createResRecordStub(pos);
        EQLResRecord eqlResRecord = eqlRes.getRecord(pos);

        // set values
        int cellCount = eqlResRecord.size();
        for(int i = 0; i < cellCount; i++) {
            setFieldValue(resRecord, eqlResRecord.getResCell(i),
                    ignoreSendOnRequest);
        }

        // set datasets
        int datasetSize = eqlResRecord.getDResSize();
        for(int i = 0; i < datasetSize; i++) {
            EQLDRes dRes = eqlResRecord.getDRes(i);
            Dataset dataset = dRes.getReqDataset().getDataset();

            ResRecord[] resDatasetRecords = createResRecords(dRes);
            if(resDatasetRecords != null) {
                // dataset has records - create ResDataset
                ResDataset resDataset = createResDatasetStub(resRecord,
                        dataset);
                resDataset.setResRecord(resDatasetRecords);

                // dataset - loaded
                resDataset.setLoaded(Boolean.TRUE);
            }
        }

        return resRecord;
    }

    /**
     * Create ResRecord stub
     *
     * @param pos position
     * @return stub for ResRecord
     */
    protected ResRecord createResRecordStub(int pos) {
        ResRecord record = new ResRecord();
        record.setId(new Integer(pos));
        return record;
    }

    /**
     * Create ResField stub and add it in ResRecord
     *
     * @param record ResRecord object
     * @param efield Efield object
     * @return stub for ResField
     */
    protected ResField createResFieldStub(ResRecord record, Efield efield) {
        ResField rfield = new ResField();
        rfield.setEntity(efield.getEntityName());
        rfield.setName(efield.getName());
        record.addResField(rfield);
        record.putObject(efield.getName(), rfield);
        return rfield;
    }

    /**
     * Create ResDataset stub and add it in ResRecord
     *
     * @param record  ResRecord object
     * @param dataset Dataset object
     * @return stub for ResDataset
     */
    protected ResDataset createResDatasetStub(ResRecord record,
                                              Dataset dataset) {

        String entityName1 = dataset.getEntityName();
        String entityName2 = dataset.getEntity();

        ResDataset rdataset = new ResDataset();
        rdataset.setEntity(entityName2);
        rdataset.setName(dataset.getName());
        record.addResDataset(rdataset);
        record.putObject(dataset.getName(), rdataset);

        // set external/internal (it will be deprecated)
        String[] attrs = getExternalAndInternalDatasetAttr(dataset);
        rdataset.setExternal(attrs[EXTERNAL_ATTR]);
        rdataset.setInternal(attrs[INTERNALL_ATTR]);

        return rdataset;
    }

    /**
     * Create new fake ResRecord
     *
     * @param fields array of Efield objects
     * @return one ResRecord
     */
    protected ResRecord[] createFakeResRecord(Efield[] fields) {
        // return one fake record
        ResRecord[] resRecords = new ResRecord[1];
        resRecords[0] = new ResRecord();
        resRecords[0].setId(new Integer(0));

        // add fake fields
        if(fields != null) {
            for(int i = 0; i < fields.length; i++) {
                createResFieldStub(resRecords[0], fields[i]);
            }
        }

        return resRecords;
    }

    /**
     * Get 'external' and 'internal' dataset attributes
     *
     * @param dataset Dataset object
     * @return array [0] - external, [1] - internal
     */
    protected String[] getExternalAndInternalDatasetAttr(Dataset dataset) {

        Entity datasetEntity = ctx.getEntityViewConfig(dataset.getEntity());
        Entity topEntity = ctx.getEntityViewConfig(dataset.getEntityName());

        // set <code>external</code> and <code>internal</code> attributes
        /** @todo support compound keys */
        Efield fieldExt = EntityHelper.getEfieldBySrc(
                dataset.getDataschema().getTable(0).getKey(0),
                topEntity);

        Efield fieldInt = EntityHelper.getEfieldBySrc(
                dataset.getDataschema().getTable(1).getKey(0),
                datasetEntity);

        return new String[]{
                fieldExt.getName(),
                fieldInt.getName()
        };
    }

    /**
     * Set field value as formatted string
     *
     * @param resRecord           ResRecord object
     * @param resCell             EQLResCell object
     * @param ignoreSendOnRequest consider Efield send on request attribute or not
     */
    protected void setFieldValue(ResRecord resRecord,
                                 EQLResCell resCell,
                                 boolean ignoreSendOnRequest) {
        // get EQL object
        EQLObject eqlObj = resCell.getEQLObject();

        EQLReqField reqField = resCell.getReqField();
        Efield field = reqField.getField();
        Entity entity = reqField.getReqEntity().getEntity();
        boolean sendOnReq = field.getSendonrequest().booleanValue();

        // create new record field
        ResField resField = createResFieldStub(resRecord, field);

        // set has-content flag
        if(resCell.isNull()) {
            resField.setHasContent(Boolean.FALSE);
        }

        if(ignoreSendOnRequest || sendOnReq) {
            // set value
            String value = convertEQLObject2String(entity, field, eqlObj);
            if(value != null) {
                resField.setResFieldValue(value);
            }

            // get field list value and set it
            setListFieldValue(resCell, resField);

            // field - loaded
            resField.setLoaded(Boolean.TRUE);

        } else {
            // field - not loaded
            resField.setLoaded(Boolean.FALSE);
        }
    }

    /**
     * Set result list field value as formatted string
     *
     * @param resCell  EQLResCell object
     * @param resField ResField object
     */
    protected void setListFieldValue(EQLResCell resCell, ResField resField) {

        EQLResCell listResCell = resCell.getListField();
        if(listResCell != null && !listResCell.isNull()) {
            EQLObject listEqlObj = listResCell.getEQLObject();
            EQLReqField listReqField = listResCell.getReqField();
            Efield listField = listReqField.getField();
            Entity listEntity = listReqField.getReqEntity().getEntity();

            String listValue = convertEQLObject2String(listEntity, listField,
                    listEqlObj);
            if(listValue != null) {
                resField.setResFieldText(listValue);
            }
        }
    }

    /**
     * Construct response header by the Entity and Form
     *
     * @param entity Entity object
     * @param form   Form object
     * @return ResHeader object
     */
    protected ResHeader getHeader(Entity entity, Form form) {

        ResHeader resHeader = getHeader(entity);

        // set 'has-external-sets' attr
        resHeader.setHasExternalSets((form.getExternalSetCount() == 0) ?
                Boolean.FALSE:Boolean.TRUE);

        // set 'linked-dataset' elements
        LinkedDataset[] linkedDatasets = form.getLinkedDataset();
        if(linkedDatasets != null) {
            XMLBinding xmlBind = XMLFactory.getXMLBinding();
            for(int i = 0; i < linkedDatasets.length; i++) {
                ResLinkedDataset resLinkedDataset = new ResLinkedDataset();
                xmlBind.copyAttributes(linkedDatasets[i],
                        resLinkedDataset,
                        LinkedDataset.class,
                        ResLinkedDataset.class);

                resHeader.addResLinkedDataset(resLinkedDataset);
            }
        }

        return resHeader;
    }

    /**
     * Construct response header by the Entity
     *
     * @param entity Entity object
     * @return ResHeader object
     */
    protected ResHeader getHeader(Entity entity) {

        ResHeader resHeader = getHeader(entity.getEfield(),
                entity.getDataset());

        // set 'listfield' attr
        String listfield = entity.getListfield();
        if(listfield != null) {
            resHeader.setListfield(listfield);
        }

        return resHeader;
    }

    /**
     * Construct response header
     *
     * @param efields  array of fields
     * @param datasets array of datasets
     * @return ResHeader object
     */
    protected ResHeader getHeader(Efield[] efields, Dataset[] datasets) {

        ResHeader resHeader = new ResHeader();
        XMLBinding xmlBind = XMLFactory.getXMLBinding();

        // select all Efield objects
        int efieldCount = (efields == null) ? 0:efields.length;
        for(int i = 0; i < efieldCount; i++) {
            ResHeaderField resHField = new ResHeaderField();
            Efield field = efields[i];
            String fieldName = field.getName();
            String entityName = field.getEntityName();

            // copy common attributes
            xmlBind.copyAttributes(field, resHField, Efield.class,
                    ResHeaderField.class);

            // set 'entity'
            resHField.setEntity(entityName);

            // set 'caption'
            CustomField cfield = ctx.getCustomField(entityName, fieldName);
            if(cfield != null) {
                resHField.setCaption(cfield.getCaption());
            }

            // set 'hasdefvalue'
            if(field.getEqlDefsrc() != null) {
                resHField.setHasdefvalue(Boolean.TRUE);
            } else {
                resHField.setHasdefvalue(Boolean.FALSE);
            }

            // set 'haslistref'
            if(field.getListref() != null) {
                resHField.setHaslistref(Boolean.TRUE);
            } else {
                resHField.setHaslistref(Boolean.FALSE);
            }

            resHeader.addResHeaderField(resHField);
        }

        // select all Dataset objects
        int dataset_count = (datasets == null) ? 0:datasets.length;
        for(int i = 0; i < dataset_count; i++) {
            ResHeaderDataset resHDataset = new ResHeaderDataset();
            Dataset dataset = datasets[i];
            String datasetName = dataset.getName();

            // set attributes
            String _entityName = dataset.getEntity();
            Entity _entity = ctx.getEntityViewConfig(_entityName);

            resHDataset.setName(dataset.getName());
            resHDataset.setEntity(_entityName);
            resHDataset.setSendonrequest(new Boolean(
                    ignoreSendOnRequest | dataset.getSendonrequest()
                            .booleanValue()));
            resHDataset.setRequired(dataset.getRequired());
            resHDataset.setResHeader(getHeader(_entity));
            if(dataset.getLinkedEntity() != null) {
                resHDataset.setLinkedEntity(dataset.getLinkedEntity());
            }

            // set 'caption'
            CustomField cfield = ctx.getCustomField(dataset.getEntityName(),
                    datasetName);
            if(cfield != null) {
                resHDataset.setCaption(cfield.getCaption());
            }

            // set external/internal
            String[] attrs = getExternalAndInternalDatasetAttr(dataset);
            resHDataset.setExternal(attrs[EXTERNAL_ATTR]);
            resHDataset.setInternal(attrs[INTERNALL_ATTR]);

            resHeader.addResHeaderDataset(resHDataset);
        }

        return (resHeader.getResHeaderFieldCount() == 0 &&
                resHeader.getResHeaderDatasetCount() == 0) ? null:resHeader;
    }

    // ----------------------------------------------------- inner class

    /**
     * <p>String parser helper class</p>
     *
     * @author [ALB] Baranov Andrey
     */

    static class GRStringParser {

        private Entity entity;
        private Efield field;

        public boolean hasTick = false;
        public boolean hasDog = false;
        public boolean hasDollar = false;
        public boolean exactSearch = true;

        //
        // Constructor
        //
        public GRStringParser(Entity entity, Efield field) {
            this.entity = entity;
            this.field = field;
        }

        //
        // Parse string user query condition with NONE operation
        // Return valid EQL string or throw exception UserQueryParseException
        //
        public final String toEQL(String rightMember)
                throws UserQueryParseException {

            // 1. find TICK and DOG
            int pos = 0;
            int size = rightMember.length();
            boolean foundEscape = false;
            for(int i = 0; i < size && pos < 2; i++) {
                char c = rightMember.charAt(i);
                if(c == TICK_OP && !hasTick) {
                    pos++;
                    if(foundEscape) {
                        break;
                    }
                    hasTick = true;

                } else if(c == DOG_OP && !hasDog) {
                    pos++;
                    if(foundEscape) {
                        break;
                    }
                    hasDog = true;

                } else if(c == ESCAPE_CHARACTER && !foundEscape) {
                    foundEscape = true;

                } else {
                    break;
                }
            }
            if(pos > 0) {
                rightMember = rightMember.substring(pos);
            }

            // 2. find ASTERISC, QUESTION and DOLLAR
            //    replace user escapes on EQL escapes
            StringBuffer sb = new StringBuffer();
            size = rightMember.length();
            boolean evenEscapes = true;
            for(int i = 0; i < size; i++) {
                char c = rightMember.charAt(i);

                if(c == EQL_ASTERISC_OP || c == EQL_QUESTION_OP) {
                    // '%' | '_'
                    sb.append(EQL_ESCAPE_CHARACTER).append(c);

                } else if(c == ASTERISC_OP && evenEscapes) {
                    // '*'
                    sb.append(EQL_ASTERISC_OP);
                    exactSearch = false;

                } else if(c == QUESTION_OP && evenEscapes) {
                    // '?'
                    sb.append(EQL_QUESTION_OP);
                    exactSearch = false;

                } else if(i == (size - 1) && c == DOLLAR_OP && evenEscapes) {
                    // '$'
                    hasDollar = true;

                } else if(c == ESCAPE_CHARACTER) {
                    // '\'
                    evenEscapes = !evenEscapes;
                    if(evenEscapes) {
                        // add ESCAPE symbol
                        sb.append(EQL_ESCAPE_CHARACTER);
                        sb.append(EQL_ESCAPE_CHARACTER);
                    }
                    continue;

                } else {
                    // other...
                    sb.append(c);
                }

                // drop escapes
                evenEscapes = true;
            }
            rightMember = sb.toString();

            // 3. build EQL string

            // check 'left-anchored' attribute
            // if present - don't add ASTERISC before value
            if(!hasTick && !field.getLeftAnchored().booleanValue()) {
                rightMember = EQL_ASTERISC_OP + rightMember;
                exactSearch = false;
            }

            // check 'right-anchored' attribute
            // if present - don't add ASTERISC after value
            if(!hasDollar && !field.getRightAnchored().booleanValue()) {
                rightMember += EQL_ASTERISC_OP;
                exactSearch = false;
            }

            // return new right member string in EQL format
            return rightMember;
        }

    } // --  end of inner class

    // ----------------------------------------------------- inner class

    /**
     * <p>String parser helper class</p>
     *
     * @author [ALB] Baranov Andrey
     */

    static class GRIgnoreWordsDetector {

        // constants
        private static final String IGNORE_WORDS_FILE
                = "com/queplix/config/sys/ignorewords.txt";

        // variables
        private List ignoreWords;

        private char[] checkedSymbols = new char[]{
                AND_CHARACTER,
                OR_CHARACTER,
                ASTERISC_OP,
                DOLLAR_OP
        };

        // Initialization
        public GRIgnoreWordsDetector() {
            InputStream is = getClass().getClassLoader().getResourceAsStream(
                    IGNORE_WORDS_FILE);
            if(is == null) {
                System.out.println("Ignore words vocabulary not found");
                return;
            }

            ignoreWords = new ArrayList();
            BufferedReader reader = null;
            try {
                reader = new BufferedReader(new InputStreamReader(is));
                String s;
                while((s = reader.readLine()) != null) {
                    if(!StringHelper.isEmpty(s)) {
                        String iw = s.toLowerCase();
                        ignoreWords.add(iw);
                    }
                }
                if(ignoreWords.size() == 0) {
                    ignoreWords = null;
                }
                System.out.println("Ignore words list loaded: " + ignoreWords);

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

            } finally {
                try {
                    if(reader != null) {
                        reader.close();
                    }
                } catch (IOException ex) {
                }
            }
        }

        // replace symbols in ignore words
        public final String replace(String s) {

            if(StringHelper.isEmpty(s)) {
                return s;
            }

            int size = (ignoreWords == null) ? 0:ignoreWords.size();
            for(int i = 0; i < size; i++) {
                String ignoreWord = (String) ignoreWords.get(i);
                int length = ignoreWord.length();

                int pos = 0;
                while((pos = s.toLowerCase().indexOf(ignoreWord, pos)) >= 0) {
                    s = s.substring(0, pos) +
                            buildIWSubstitutor(s.substring(pos, pos + length)) +
                            s.substring(pos + length);
                    pos += length;
                }
            }

            return s;
        }

        // build substitutor for given ignore word
        private String buildIWSubstitutor(String ignoreWord) {

            if(StringHelper.isEmpty(ignoreWord)) {
                return ignoreWord;
            }

            StringBuffer sb = new StringBuffer();
            int length = ignoreWord.length();
            for(int i = 0; i < length; i++) {
                char c = ignoreWord.charAt(i);
                for(int j = 0; j < checkedSymbols.length; j++) {
                    char symbol = checkedSymbols[j];
                    if(c == symbol) {
                        sb.append(ESCAPE_CHARACTER);
                        break;
                    }
                }
                sb.append(c);
            }

            return sb.toString();
        }

    } // --  end of inner class
}
