/**
 * Title:           SdeQuery, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/SDEQuery.java,v 1.3 2008/01/30 12:38:34 blume Exp $
 * Source:          $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/SDEQuery.java,v $
 * created by:      Stefan Blume (blume)
 * erstellt am:     21.11.2007
 * Copyright:       con terra GmbH, 2005
 *
 * modified by:     $Author: blume $
 * modified on:     $Date: 2008/01/30 12:38:34 $
 * Version:         $Revision: 1.3 $
 * TAG:             $Name:  $
 * locked from:     $Locker:  $
 * CVS State:       $State: Exp $
 * Project:         $ProjectName$
 */
package de.intevation.gnv.geobackend.sde.datasources;

import org.apache.log4j.Logger;

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.SeCoordinateReference;
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.SeRow;
import com.esri.sde.sdk.client.SeShape;
import com.esri.sde.sdk.client.SeShapeFilter;
import com.esri.sde.sdk.client.SeSqlConstruct;

import de.intevation.gnv.geobackend.sde.datasources.exception.TechnicalException;
import de.intevation.gnv.geobackend.util.DateUtils;

/**
 * // todo: supply a general interface for Query-Objects (Query = read access to datasource implementation)
 *
 * @author blume
 * @version 1.0
 * @serial 1.0
 * @see
 * @since 21.11.2007 11:32:19
 */
public class SDEQuery {

    /**
     * Default Logging instance
     */
    private static final Logger sLogger = Logger.getLogger(SDEQuery.class);
    private static boolean sDebug = sLogger.isDebugEnabled();


    DatasourceConnection mConnection;

    public SDEQuery(DatasourceConnection pConnection) {
        if (sDebug) sLogger.debug("SDEQuery()");
        mConnection = pConnection;
    }

    /**
     * Execute a query against a sde datasource.
     * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
     *
     * @param pTables
     * @param pCols
     * @param pWhere  the where-clause to be executed.
     * @return a ResultSet
     * @see SeSqlConstruct
     * @see SeQuery
     */
    public ResultSet executeQuery(String pTables[], String pCols[], String pWhere) throws TechnicalException {
        sLogger.debug("executeQuery()");
        SeConnection con = null;
        try {
            con = getConnection();
            SeSqlConstruct lSeSqlConstruct = new SeSqlConstruct(pTables, pWhere);
            SeQuery lSeQuery = new SeQuery(con, pCols, lSeSqlConstruct);
            long lStart = System.currentTimeMillis();
            lSeQuery.prepareQuery();
            ResultSet lSet = handleResultSet(lSeQuery);
            long lEnd = System.currentTimeMillis();
            if (sDebug)
                sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString());
            return lSet;
        } catch (TechnicalException e) {
            sLogger.error(e.getMessage(), e);
            throw e;
        } catch (SeException e) {
            sLogger.error(e.getMessage(), e);
            throw new TechnicalException("Error during executeQuery", e);
        } finally {
            returnConnection(con);
        }

    }

    /**
     * Execute a query against a sde datasource.
     * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
     *
     * @param pSQLStatement the SQL-Statement to be executed.
     * @return a ResultSet
     * @see SeQuery
     */
    public ResultSet executeQuery(String pSQLStatement) throws TechnicalException {
        if (sDebug) sLogger.debug("executeQuery():\n" + pSQLStatement);
        SeConnection con = null;
        try {
            con = getConnection();

            SeQuery lSeQuery = new SeQuery(con);
            long lStart = System.currentTimeMillis();
            lSeQuery.prepareSql(pSQLStatement);
            lSeQuery.execute();

            ResultSet lSet = handleResultSet(lSeQuery);
            
            //TmpFile erstellen
            //Row auslesen
            //Row -> TmpFile
            //TmpFile close

            long lEnd = System.currentTimeMillis();
            if (sDebug)
                sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString());
            return lSet;
        } catch (TechnicalException e) {
            sLogger.error(e.getMessage(), e);
            throw e;
        } catch (SeException e) {
            sLogger.error(e.getMessage(), e);
            throw new TechnicalException("Error during executeQuery", e);
        } finally {
            returnConnection(con);
        }


    }
    
//    /**
//     * Execute a query against a sde datasource.
//     * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
//     *
//     * @param pSQLStatement the SQL-Statement to be executed.
//     * @return File
//     * @see SeQuery
//     */
//    public TempFile  executeQueryDiagram (String pSQLStatement) throws TechnicalException {
//        if (sDebug) sLogger.debug("executeQueryDiagram():\n" + pSQLStatement);
//        SeConnection con = null;
//        TempFile lImageFile = null;
//        try {
//            con = getConnection();
//
//            SeQuery lSeQuery = new SeQuery(con);
//            long lStart = System.currentTimeMillis();
//            lSeQuery.prepareSql(pSQLStatement);
//            lSeQuery.execute();
//
//            //ResultSet lSet = handleResultSet(lSeQuery);
//            lImageFile = handleRowToCSVFile(lSeQuery);
//            
//
//            long lEnd = System.currentTimeMillis();
//            if (sDebug)
//                sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString());
//            
//        } catch (TechnicalException e) {
//            sLogger.error(e.getMessage(), e);
//            throw e;
//        } catch (SeException e) {
//            sLogger.error(e.getMessage(), e);
//            throw new TechnicalException("Error during executeQuery", e);
//        } finally {
//            returnConnection(con);
//        }
//        return lImageFile;
//
//    }

    /**
     * Execute a spatial query against a sde datasource.
     * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
     * // todo: there is work needed to fully implement the wanted functionality.
     *
     * @param pLayername
     * @param pSpatialColumnName
     * @param pPoints
     * @param pReturnFields
     * @return a ResultSet
     * @see SeShapeFilter
     * @see SeQuery
     */
    public ResultSet executeQuery(String pLayername, String pSpatialColumnName, double[][] pPoints, String[] pReturnFields) throws TechnicalException {
        sLogger.debug("executeQuery()");
        SeConnection con = null;
        try {
            con = getConnection();
            // get the layer for querying
            SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
            SeCoordinateReference cref = lLayer.getCoordRef();


            SeShape shape = new SeShape();
            shape.setCoordRef(lLayer.getCoordRef());
            SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints);

            /*
            * int�numPts, int�numParts, int[]�partOffsets,SDEPoint[]�ptArray
            * */
            shape.generatePolygon(lPoints.length, 1, null, lPoints);
            SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI);
            SeShapeFilter[] filters = new SeShapeFilter[1];
            filters[0] = filter;

            SeQuery spatialQuery = null;
            SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername);
            spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
            spatialQuery.prepareQuery();
            /*
            *   Set spatial constraints
            */
            spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters);
            spatialQuery.execute();

            return handleResultSet(spatialQuery);

        } catch (TechnicalException e) {
            sLogger.error(e.getMessage(), e);
            throw e;
        } catch (SeException e) {
            sLogger.error(e.getMessage(), e);
            throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e);
        } finally {
            returnConnection(con);
        }
    }
    /**
     * Execute a spatial query against a sde datasource.
     * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
     * // todo: there is work needed to fully implement the wanted functionality.
     *
     * @param pLayername
     * @param pSpatialColumnName
     * @param pPoints
     * @param pReturnFields
     * @return a ResultSet
     * @see SeShapeFilter
     * @see SeQuery
     */
    public ResultSet executeQuery(String pLayername, String pSpatialColumnName, String pWhere, double[][] pPoints, String[] pReturnFields) throws TechnicalException {
        sLogger.debug("executeQuery()");
        SeConnection con = null;
        try {
            con = getConnection();
            // get the layer for querying
            SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
            SeCoordinateReference cref = lLayer.getCoordRef();


            SeShape shape = new SeShape();
            shape.setCoordRef(lLayer.getCoordRef());
            SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints);

            /*
            * int�numPts, int�numParts, int[]�partOffsets,SDEPoint[]�ptArray
            * */
            shape.generatePolygon(lPoints.length, 1, null, lPoints);
            SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI);
            SeShapeFilter[] filters = new SeShapeFilter[1];
            filters[0] = filter;

            SeQuery spatialQuery = null;
            SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername,pWhere);
            spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
            spatialQuery.prepareQuery();
            /*
            *   Set spatial constraints
            */
            spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters);
            spatialQuery.execute();

            return handleResultSet(spatialQuery);

        } catch (TechnicalException e) {
            sLogger.error(e.getMessage(), e);
            throw e;
        } catch (SeException e) {
            sLogger.error(e.getMessage(), e);
            throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e);
        } finally {
            returnConnection(con);
        }
    }
    private SeConnection getConnection() throws TechnicalException {
        SeConnection lConnection = ((SDEConnection) mConnection).getConnection();
        if (sDebug) try {
            sLogger.debug("get connection to server: " + lConnection.getServer() + ", ServerTime: " + DateUtils.getPatternedDateAmer(lConnection.getServerTime()) + ", Release: " + lConnection.getRelease().getDesc());
        } catch (SeException e) {
            sLogger.error(e.getMessage(), e);
        }
        return lConnection;
    }

    private void returnConnection(SeConnection pSeConnection) {
        ((SDEConnection) mConnection).returnConnection(pSeConnection);
    }


    private ResultSet handleResultSet(SeQuery pSeQuery) throws SeException {
        sLogger.debug("handleResultSet()");
        SDEResultSet lSet = new SDEResultSet();
        SeRow row;
        int lCount;
        for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) {
            // one time execution
            if (lCount == 0) {
                // analyze cols of result set
                SeColumnDefinition[] lCols = row.getColumns();
                for (SeColumnDefinition lCol : lCols) {
                    lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));// notice: esri-types have been copied into colDefinition class!
                }
            }
            short lNumCols = row.getNumColumns();
            Row lBackingRow = new Row(lNumCols);
            for (int i = 0; i < lNumCols; i++) {
                lBackingRow.addObject(row.getObject(i), i);
            }
            lSet.addRow(lBackingRow);
        }
        pSeQuery.close();
        return lSet;
    }
 
//    private TempFile handleRowToCSVFile(SeQuery pSeQuery) throws SeException {
//        sLogger.debug("handleRowToTempFile()");
//        TempFile lImageFile = TemporaryFileDirectory.getInstance().createFile(".csv");
//        sLogger.debug("handleRowToTempFile() erstellen " + lImageFile.getFile().getName());
//        CSVWriter writer = null;
//        ColDefinition[] lColDefinitions = null;
//        
//        SeRow row;
//        int lCount;
//        try {
//	        writer = new CSVWriter(new FileWriter(lImageFile.getFile()), ';');
//	        for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) {
//	            // one time execution
//	            if (lCount == 0) {
//	            	lColDefinitions = new ColDefinition[row.getNumColumns()];
//	                // analyze cols of result set
//	                SeColumnDefinition[] lCols = row.getColumns();
//	                int j = 0;
//	                for (SeColumnDefinition lCol : lCols) {
//	                	lColDefinitions[j] = new ColDefinition(lCol.getName(), lCol.getType());
//	                	j = j + 1;
//	                }
//	            }
//	            short lNumCols = row.getNumColumns();
//	            Row lBackingRow = new Row(lNumCols);
//	            for (int i = 0; i < lNumCols; i++) {
//	                lBackingRow.addObject(row.getObject(i), i);
//	            }
//	          
//	            writer.writeRow(lBackingRow,lColDefinitions, false);
//	        }
//        } catch (IOException e) {
//        	 sLogger.error(e.getMessage(), e);
//        } catch (TechnicalException e) {
//        	 sLogger.error(e.getMessage(), e);     
//        }
//        finally {
//        	pSeQuery.close();
//        	try{
//        	writer.close();
//        	}
//        	catch (Exception e){
//        		sLogger.error(e.getMessage(), e);
//        	}
//        }
//        
//        return lImageFile;
//    }

}