/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package usda.weru.weps.reports.query;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.apache.log4j.Logger;

/**
 * The Output OpCrop result set handles the special logic required for splitting 
 * the operations and crops that occur within the same period.  These are deleinated
 * by the ~ symbol by the science model.  But must be presented as separate lines
 * in the reports.
 * 
 * This result set queries the output result set and creates a sub resultset.
 * @author joelevin
 */
public class OutputOpCropResultSet extends WepsResultSet {

    private static final Logger LOGGER = Logger.getLogger(OutputOpCropResultSet.class);
    
	/**
	 *
	 */
	public static final String NAME = "output_opcrop";    
    
    /**
     * Index for the run.
     */ 
    public static final String COLUMN_RUNID = "runid";
    /**
     * Line number in the file.  0 is the header.  1 is the first line of data.  Line 0 is not included in the resultset.
     */            
    public static final String COLUMN_LINE = "line";

	/**
	 *
	 */
	public static final String COLUMN_OPERATION = "operation";

	/**
	 *
	 */
	public static final String COLUMN_CROP = "crop";
    
    
    private static final String SQL = "SELECT \"runid\", \"line\", \"operation\", \"crop\" FROM weps('output') WHERE \"key\"='P' AND (\"operation\" IS NOT NULL OR \"crop\" IS NOT NULL) ORDER BY \"startdate\"";
        
    private final WepsConnection c_con;
    private boolean c_filled;

	/**
	 *
	 * @param con
	 * @throws SQLException
	 */
	public OutputOpCropResultSet(WepsConnection con) throws SQLException {
        c_con = con;
        //added to query to tie back to runs when multiple are int the connection
        addColumn(COLUMN_RUNID, Types.INTEGER, 10, 0);
                        
        addColumn(COLUMN_LINE, Types.INTEGER, 10, 0);
        addColumn(COLUMN_OPERATION, Types.VARCHAR, 255, 0); 
        addColumn(COLUMN_CROP, Types.VARCHAR, 255, 0);                                       
    }

	/**
	 *
	 * @return
	 */
	@Override
    public String getName() {
        return NAME;
    }

	/**
	 *
	 * @throws SQLException
	 */
	@Override
    public synchronized void fill() throws SQLException {
        if (c_filled) {
            return;            
        }
        PreparedStatement statement = null;
        try{
            //Query the output resultset so we don't have to read all the same files into memory again
            statement = c_con.prepareStatement(SQL);
            ResultSet rs = statement.executeQuery();

            //loop over all the rows
            while(rs.next()){
                int runId = rs.getInt(COLUMN_RUNID);
                int line = rs.getInt(COLUMN_LINE);
                String operation = rs.getString(COLUMN_OPERATION);
                String crop = rs.getString(COLUMN_CROP);

                String[] operations = null;
                if (operation != null){
                    operations = operation.split("\\~", -1);
                }
                else{
                    operations = new String[] {null};
                }

                String[] crops = null;
                if (crop != null){
                    crops = crop.split("\\~", -1);
                }
                else{
                    crops = new String[] {null};
                }

                for (int i = 0; i < operations.length; i++){
                    String opName = operations[i];
                    opName = opName != null && opName.trim().length() > 0 ? opName.trim() : null;

                    String cropName = crops[i];
                    cropName = cropName != null && cropName.trim().length() > 0 ? cropName.trim() : null;

                    Object[] row = createNewRow(true);
                    setRowValue(row, COLUMN_RUNID, runId);
                    setRowValue(row, COLUMN_LINE, line);
                    setRowValue(row, COLUMN_OPERATION, opName);
                    setRowValue(row, COLUMN_CROP, cropName);
                }

                c_filled = true;
            }
        }
        finally{
            if(statement != null){
                try{
                    statement.close();
                }
                catch(Exception e){
                    LOGGER.warn("Unable to close sql statement.", e);
                }
            }
        }
    }
        
    
    
}