Application: gvSIG desktop

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2020 gvSIG Association.

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.fmap.dal.store.jdbc2.spi.operations;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.expressionevaluator.Code;

import org.gvsig.expressionevaluator.Expression;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.expressionevaluator.SymbolTable;

import org.gvsig.fmap.dal.SQLBuilder.SelectBuilder;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.expressionevaluator.FeatureAttributeEmulatorExpression;

import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureAttributeEmulator;

import org.gvsig.fmap.dal.feature.FeatureQuery;

import org.gvsig.fmap.dal.feature.FeatureQueryOrder;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException;

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_FEATURE_TYPE;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_JDBCHELPER;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_QUERY;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_SYMBOLTABLE;

import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE;

import org.gvsig.tools.dynobject.DynField;

import org.gvsig.tools.evaluator.Evaluator;

public class CountOperation extends AbstractConnectionOperation {

    private final TableReference table;

    private final String baseFilter;

    private final FeatureQuery query;

    private final FeatureType featureType;

    public CountOperation(

            JDBCHelper helper

        ) {

        this(helper, null, null, null, null);

    }

    public CountOperation(

            JDBCHelper helper,

            FeatureType featureType,

            TableReference table,

            String baseFilter,

            FeatureQuery query

        ) {

        super(helper);

        this.featureType = featureType;

        this.table = table;

        this.baseFilter = baseFilter;

        this.query = query;

    }

    @Override

    public final Object perform(Connection conn) throws DataException {

        return this.count(conn);

    }

    public String getSQL() {

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        ExpressionBuilder expbuilder = sqlbuilder.expression();

        SelectBuilder select = sqlbuilder.select();

        ArrayList<ExpressionBuilder.Value> valuesToRemoveFeatureType = new ArrayList<>();

        if (this.query != null && this.query.hasGroupByColumns()) {

            JDBCSQLBuilderBase subsqlbuilder = this.createSQLBuilder();

            SelectBuilder subselect = subsqlbuilder.select();

            subselect.column().value(subsqlbuilder.count().all());

            for (FeatureAttributeDescriptor attr : this.featureType) {

                if (attr.isComputed()) {

                    if (attr.getRelationType() == DynField.RELATION_TYPE_NONE) {

                        FeatureAttributeEmulator attrEmulator = attr.getFeatureAttributeEmulator();

                        if (attrEmulator instanceof FeatureAttributeEmulatorExpression) {

                            FeatureAttributeEmulatorExpression x = (FeatureAttributeEmulatorExpression) attrEmulator;

                            Expression exp = x.getExpression();

                            if (query != null && query.hasGroupByColumns()) {

                                String aggregate = query.getAggregate(this.table.getTable(), attr.getName());

                                if (this.query.isAGroupByColumn(attr.getName())) {

                                    if (!subselect.has_column(attr.getName())) {

                                        subselect.column().value(exp.getCode().toValue()).as(attr.getName());

                                    }

                                } else if (aggregate == null) {

                                    subselect.column().value(expbuilder.constant(null)).as(attr.getName());

                                } else {

                                    String fn = this.query.getAggregateFunctions().get(attr.getName());

                                    ExpressionBuilder.Function aggregateExp = expbuilder.function(fn, exp.getCode().toValue());

                                    if (!subselect.has_column(attr.getName())) {

                                        subselect.column().value(aggregateExp).as(attr.getName());

                                    }

                                }

                            } else {

                                if (exp != null && !exp.isEmpty() && this.helper.supportExpression(this.featureType, exp.getPhrase())) {

                                    Code code = exp.getCode();

                                    subselect.column()

                                            .value(code.toValue(expbuilder))

                                            .as(attr.getName());

                                }

                            }

                        }

                    }

                }

            }

            if (this.query.hasGroupByColumns()) {

                if (this.query.hasOrder()) {

                    FeatureQueryOrder order = query.getOrder();

                    if (order != null) {

                        for (FeatureQueryOrder.FeatureQueryOrderMember member : order.members()) {

                            if (member.hasEvaluator()) {

                                String sqlorder = member.getEvaluator().getSQL();

                                if (!StringUtils.isEmpty(sqlorder)) {

                                    subselect.order_by()

                                            .value(expbuilder.toValue(sqlorder))

                                            .ascending(member.getAscending());

                                }

                            } else {

                                subselect.order_by()

                                        .column(member.getAttributeName())

                                        .ascending(member.getAscending());

                            }

                        }

                    }

                }

                subselect.from().table()

                        .database(this.table.getDatabase())

                        .schema(this.table.getSchema())

                        .name(this.table.getTable());

                subselect.from().subquery(this.table.getSubquery());

                Evaluator filter = query == null ? null : query.getFilter();

                if (filter != null) {

                    String sqlfilter = filter.getSQL();

                    if (!StringUtils.isEmpty(sqlfilter)) {

                        if (this.helper.supportFilter(this.featureType, filter)) {

                            subselect.where().set(expbuilder.toValue(sqlfilter));

                        }

                    }

                }

                if (!StringUtils.isEmpty(baseFilter)) {

                    subselect.where().and(expbuilder.toValue(baseFilter));

                }

                List<String> groupbyColumns = query == null ? null : query.getGroupByColumns();

                if (groupbyColumns != null && !groupbyColumns.isEmpty()) {

                    for (String columnName : groupbyColumns) {

                        if (this.featureType.getAttributeDescriptor(columnName) != null) {

                            subselect.group_by(expbuilder.column(columnName));

                        } else if(this.featureType.getExtraColumns().get(columnName)!=null) {

                            EditableFeatureAttributeDescriptor attr = this.featureType.getExtraColumns().get(columnName);

                            subselect.group_by(expbuilder.column(columnName));

                            Expression exp = ((FeatureAttributeEmulatorExpression) attr.getFeatureAttributeEmulator()).getExpression();

                            subselect.column().value(exp.getCode().toValue()).as(columnName);

                        } else {

                            try {

                                try {

                                    Code groupByColumnCode = ExpressionUtils.compile(columnName);

    //                                if (groupByColumnCode.code() == Code.CALLABLE) {

    //                                    Code.Callable callable = (Code.Callable) groupByColumnCode;

    //                                    if (callable.name().equalsIgnoreCase(FUNCTION_LET)) {

    //                                        Code exp = callable.parameters().get(1);

    //                                        Code name = callable.parameters().get(0);

    //                                        subselect.column().value(exp.toValue())

    //                                                .as((String) ((Code.Constant) name).value());

    //                                        // nombre que se pone en la parte del groupby debe de ser el nombre de la var del set

    //                                        groupByColumnCode = exp; 

    //                                    }

    //                                }

                                    subselect.group_by(groupByColumnCode.toValue());

                                } catch (Exception ex) {

                                    throw new RuntimeException("Not able to create column by expression in groupby query", ex);

                                }

                            } catch (Exception ex) {

                                throw new RuntimeException("Not able to create column by expression in groupby query", ex);

                            }

                        }

                    }

                }

                this.helper.processSpecialFunctions(subsqlbuilder, featureType, null);

                subsqlbuilder.setProperties(

                        ExpressionBuilder.Variable.class,

                        PROP_FEATURE_TYPE, this.featureType,

                        PROP_TABLE, table,

                        PROP_SYMBOLTABLE, this.query == null ? null : this.query.getSymbolTable(),

                        PROP_JDBCHELPER, this.helper,

                        PROP_QUERY, this.query

                );

                String subsql = subselect.toString();

                select.from().table()

                        .database(this.table.getDatabase())

                        .schema(this.table.getSchema())

                        .name(this.table.getTable());

                select.from().subquery(subsql);

            }

        } else {

            select.column().value(sqlbuilder.count().all());

            select.from().table()

                    .database(this.table.getDatabase())

                    .schema(this.table.getSchema())

                    .name(this.table.getTable());

            select.from().subquery(this.table.getSubquery());

            if (!StringUtils.isEmpty(baseFilter)) {

                sqlbuilder.select().where().set(expbuilder.custom(baseFilter));

            }

            if (this.query != null) {

                if (this.query.getFilter() != null && !StringUtils.isBlank(this.query.getFilter().getSQL())) {

                    // El and() hace un set() si no hay un filtro previo

                    select.where().and(expbuilder.toValue(this.query.getFilter().getSQL()));

                }

            }

        }

        this.helper.processSpecialFunctions(sqlbuilder, featureType, null);

        select.remove_all_columns();

        select.column().value(sqlbuilder.count().all());

//        sqlbuilder.setProperties(

//                ExpressionBuilder.Variable.class,

//                PROP_TABLE, table

//        );

        sqlbuilder.setProperties(

                null,

                PROP_FEATURE_TYPE, this.featureType,

                PROP_TABLE, table,

                PROP_SYMBOLTABLE, this.query==null? null:this.query.getSymbolTable(),

                PROP_JDBCHELPER, this.helper,

                PROP_QUERY, this.query

        );

        String sql = select.toString();

        return sql;

    }

    public long count(Connection conn) throws DataException {

        String sql = this.getSQL();

        Statement st = null;

        ResultSet rs = null;

        try {

            st = conn.createStatement();

            rs = JDBCUtils.executeQuery(st, sql);

            if (!rs.next()) {

                return 0;

            }

            return rs.getLong(1);

        } catch (SQLException ex) {

            throw new JDBCSQLException(ex);

        } finally {

            JDBCUtils.closeQuietly(st);

            JDBCUtils.closeQuietly(rs);

        }

    }

}