/*
 * Copyright (c) 2010 by Intevation GmbH
 *
 * This program is free software under the LGPL (>=v2.1)
 * Read the file LGPL.txt coming with the software for details
 * or visit http://www.gnu.org/licenses/ if it does not exist.
 */

package de.intevation.gnv.geobackend.base.query;

import com.esri.sde.sdk.client.SDEPoint;
import com.esri.sde.sdk.client.SeColumnDefinition;
import com.esri.sde.sdk.client.SeConnection;
import com.esri.sde.sdk.client.SeException;
import com.esri.sde.sdk.client.SeFilter;
import com.esri.sde.sdk.client.SeLayer;
import com.esri.sde.sdk.client.SeQuery;
import com.esri.sde.sdk.client.SeQueryInfo;
import com.esri.sde.sdk.client.SeRow;
import com.esri.sde.sdk.client.SeShape;
import com.esri.sde.sdk.client.SeShapeFilter;
import com.esri.sde.sdk.client.SeSqlConstruct;

public class GroupBySample {


    public void executeQuery(SeConnection con, String[] pLayername,
                                   String pSpatialColumnName, String pWhere,
                                   SDEPoint[] g, String[] pReturnFields, String byClause) {

        try {
            // get the layer for querying

            SeShapeFilter[] filters  = null;
            if (g != null){
                SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName);
                SeShape shape = new SeShape();
                shape.setCoordRef(lLayer.getCoordRef());



                shape.generatePolygon(g.length, 1, null, g);
                SeShapeFilter filter = new SeShapeFilter(pLayername[0],
                        pSpatialColumnName, shape, SeFilter.METHOD_AI);
                filters = new SeShapeFilter[1];
                filters[0] = filter;
            }

            SeQuery spatialQuery = null;
            SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere);
            spatialQuery = new SeQuery(con);

            SeQueryInfo queryInfo = new SeQueryInfo();
            queryInfo.setColumns(pReturnFields);

            if (byClause != null){
                queryInfo.setByClause(byClause);
            }

            queryInfo.setConstruct(sqlCons);
            spatialQuery.prepareQueryInfo(queryInfo);

            /*
             * Set spatial constraints
             */
            if (filters != null){
                spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false,
                        filters);
            }
            spatialQuery.execute();

            SeRow row;
            int lCount;
            for (lCount = 0; (row =spatialQuery.fetch()) != null; lCount++) {
                // one time execution
                if (lCount == 0) {
                    // analyze cols of result set
                    SeColumnDefinition[] lCols = row.getColumns();

                }
                short lNumCols = row.getNumColumns();

                for (int i = 0; i < lNumCols; i++) {
                    System.out.println(row.getObject(i));
                }

            }
            spatialQuery.close();

        } catch (Exception e){
                e.printStackTrace();
        }

    }


    /**
     * @param args
     */
    public static void main(String[] args) {

        try {
            String[] layerNames = new String[] { "median.meshpoint", "median.mesh" };
            String spatialColumnName = "SHAPE";
            String where = "median.meshpoint.meshid = median.mesh.meshid";
            String[] returnFields = new String[] { "sourceid" };
            String byClause = "group by sourceid";
            SDEPoint[] g = new SDEPoint[5];
            g[0] = new SDEPoint(52, 8);
            g[1] = new SDEPoint(52, 9);
            g[2] = new SDEPoint(53, 9);
            g[3] = new SDEPoint(53, 8);
            g[4] = new SDEPoint(52, 8);

            String server = "";
            String port = "";
            String database = "";
            String username = "";
            String credentials = "";
            SeConnection con = new SeConnection(server, port,
                                                database, username,
                                                credentials);
            new GroupBySample().executeQuery(con, layerNames, spatialColumnName,
                                             where, g, returnFields, byClause);


        } catch (SeException e) {
           e.printStackTrace();
        }
    }
}