package org.jcon.df.request;

import org.jcon.util.GenLib;
import org.jcon.df.column.ColumnDef;
import org.jcon.df.column.MoneyColumn;
import org.jcon.df.Schema;
import org.jcon.df.work.Row;
import org.jcon.test.TestJDBC;
import java.sql.*;

/**
 * Creates and reads a JDBC ResultSet. Performs the crucial
 * impendence mismatch translation from a relational database
 * to objects. Test: java org.jcon.df.request.ResultSetReader
 *
 * @author Jack Harich
 */
public class ResultSetReader {

//---------- Private Fields ------------------------------
// Provided properties
private Schema      schema;
private String[]    columnIDs;
private ColumnDef[] columnDefs;
private Statement   statement;
private String      sql;
// Work
private ResultSet         resultSet;
private ResultSetMetaData metaData;
private int               columnCount;

//---------- Initialization ------------------------------
public static void main(String args[]) {
    new ResultSetReader().runUnitTest();
}
//---------- Properties ----------------------------------
// Must setSchema() before setColumnIDs()
public void setSchema(Schema schema) {
    this.schema = schema;
}
// Base 0, format "EntityName.ColumnName"
public void setColumnIDs(String[] columnIDs) {
    this.columnIDs = columnIDs;
    // Prepare columnDefs - faster than Hashtable use
    columnDefs = new ColumnDef[columnIDs.length];
    for (int i = 0; i < columnIDs.length; i++) {
        columnDefs[i] = schema.getColumnDef(columnIDs[i]);
    }
}
public void setStatement(Statement statement) {
    this.statement = statement;
}
public void setSQL(String sql) {
    this.sql = sql;
}
//---------- Public Methods ------------------------------
/**
 * Executes the query using properties statement and sql.
 * Throws an exception if failure.
 */
public void execute() throws SQLException {
    resultSet = statement.executeQuery(sql);
    metaData = resultSet.getMetaData();
    columnCount = metaData.getColumnCount();
}
/**
 * Returns the next row or null if there are no more.
 * An empty ResultSet will thus always return null.
 * Returns null if a database read exception occurs.
 * <p>
 * For this method to work properly, first set the
 * properties and call execute().
 */
public Row nextRow() {
    if (resultSet == null) return null;
    // Move to next, close if done
    boolean hasMore;
    try {
        // Move resultSet to next row or end
        hasMore = resultSet.next();
    } catch(SQLException ex) {
        GenLib.exception("ResultSetReader.nextRow()",
            "Failure with SQL: \n" + sql, ex);
        hasMore = false;
    }
    if (hasMore == false) {
        resultSet = null;
        metaData = null;
        return null;
    }
    // Populate and return row.
    // Note i is columnIndex, base 1
    Row row = new Row();
    for (int i = 1; i <= columnCount; i++) {
        setRowColumn(row, i);
    }
    return row;
}
public void runUnitTest() {

try {
    TestJDBC tester = new TestJDBC();
    // Prepare reader - set properties, execute
    ResultSetReader reader = new ResultSetReader();

    String[] columnIDs = new String[3];
    columnIDs[0] = "SysUser.MID";
    columnIDs[1] = "SysUser.FirstName";
    columnIDs[2] = "SysUser.LastLogon";
    reader.setColumnIDs(columnIDs);

    reader.setStatement(tester.getStatement());
    String sql = "SELECT UserSys.MID, UserSys.FirstName, "
        + "UserSys.LastName FROM UserSys";
    reader.setSQL(sql);
    reader.execute();
    // Use reader
    Row row = reader.nextRow();
    while (row != null) {
        print("Row = " + row);
        row = reader.nextRow();
    }
    // Done
    tester.close();

} catch (Exception ex) {
    ex.printStackTrace();
}
} // End method

//---------- Private Methods -----------------------------
// Note columnIndex is base 1 in JDBC
private void setRowColumn(Row row, int columnIndex) {
    // Set value, columnType
    String value = null;
    int    metaColumnType = 0;
    try {
        value = resultSet.getString(columnIndex);
        metaColumnType = metaData.getColumnType(columnIndex);

    } catch(SQLException ex) {
        GenLib.exception("ResultSetReader.nextRow()",
            "Failure with SQL: \n" + sql + "\nOn column " + columnIndex, ex);
        return;
    }
    // Format some (format Money when displaying)
    String columnID = columnIDs[columnIndex - 1];

    //print(".setRowColumn() - columnID = " + columnID + ", type = " + columnType + ", value = '" + value + "'");
    if (value != null) {
        // Format some values depending on columnType
        ColumnDef columnDef = columnDefs[columnIndex - 1];
        String columnType = columnDef.getType().intern();

        // Similar to but better than:
        /** if (metaColumnType == java.sql.Types.DATE ||
            metaColumnType == java.sql.Types.TIMESTAMP) { */

        if (columnType == "TimeStamp" || columnType == "Date") {
            value = convertRawDateTimeToString(
                columnDef, columnID, value);

        } else if (columnType == "MID") {
            value = convertMIDColumn(value);

        } else if (columnType == "Money") {
            value = convertMoneyColumn(value, columnDef);
        }
    }
    // Put value in row
    row.setValue(columnID, value);
}
// Remove unwanted decimal places
// Assumes 2 places or more are always returned
// *** From old RowsetFactory.formatMoneyColumn()
private String convertMoneyColumn(String value, ColumnDef columnDef) {
    MoneyColumn moneyColumn = (MoneyColumn)columnDef;
    if (moneyColumn.isDollarsOnly() ) {
        // Remove decimal and all to right
        return value.substring(0, value.indexOf(".") );
    } else {
        // Include decimal and 2 digits only for readability
        return value.substring(0, value.indexOf(".") + 3 );
    }
}
private String convertMIDColumn(String value) {
    // Remove decimal and all to right
    // This is actually not necessary, but prevents
    // ".0000" from obscuring the MID's value
    return value.substring(0, value.indexOf(".") );
}
// Raw format is "1996-01-22 00:00:00". Return m/d/yyyy.
private String convertRawDateTimeToString(
    ColumnDef columnDef, String columnID, String text) {

    // A null date is returned as the following:
    if (text.startsWith("1900-01-01")) return "";

    // Prepare m/d/yyyy datePortion, return if Date
    String logicalType = columnDef.getType();

    String month = text.substring(5, 7);
    if (month.startsWith("0")) month = month.substring(1);

    String day = text.substring(8, 10);
    if (day.startsWith("0")) day = day.substring(1);

    String year = text.substring(0, 4);

    String datePortion = month + "/" + day + "/" + year;
    if (logicalType.equals("Date")) return datePortion;

    // Prepare timePortion
    String timePortion = text.substring(11);

    if (logicalType.equals("TimeStamp")) {
        return datePortion + " " + timePortion;

    } else if (logicalType.equals("Time")) {
        return timePortion; // Future type, time only
    } else {
        GenLib.error("ResultSetReader.convertRawDateTimeToString()",
            "Unknown ColumnDef type '" + logicalType +
            "' for columnID " + columnID);
        return "";
    }
}
//--- Std
private static void print(String text) {
    System.out.println("ResultSetReader" + text);
}

} // End class
