/**
 * Title:        JDBC Modlet for SoftPLC
 * Description:  A SoftPLC Modlet that implements a TLI called "JDBC_READ".
 *               It should work with most any JDBC type 4 driver but was
 *               tested specifically with "MM MYSQL 2.0.7".
 *
 * Copyright:    Copyright (c) 2001
 * Company:      SoftPLC Corporation
 * @author Dick Hollenbeck
 * @version 1.0
 */

import com.softplc.*;
import java.sql.*;
import java.lang.reflect.Method;


/** A private class that is used to hold a READ request */
class Query
{
    public int     day;
    public int     month;
    public long    hError;
    public long    hResults;

    public Query( int day, int month, long hError, long hResults )
    {
        this.day = day;
        this.month = month;
        this.hError = hError;
        this.hResults = hResults;
    }
}



/**
 *  The ladder instruction is called JDBC_READ and takes the 4 parameters:
 *  JDBC_READ <out:error_code> <out:fetched_vales> <in:day> <in.month>
 *  The database name is "test" with a table called "recipes" that have the
 *  INT columns: Day, Month, Timer1, Timer2, Timer3.
 *
 *  <p>Modify the configuration data as needed, then recompile and zip to *.jar.
 */
public class JDBC extends Thread
    implements Modlet
{
    private final static boolean debug = true;
//    private final static boolean debug = false;


    //-----<Error Word Codes>----------------------------------------
    /** The service thread queue is full */
    public final static int ERROR_QFULL = 1;

    //-----</Error Word Codes>---------------------------------------


    //  Modify the configuration data as needed.
    //-----<Configuration Data>--------------------------------------
    /** The database user name used to login to the database */
    private final static String USER_NAME = "dickh";

    /** The password for the USER_NAME */
    private final static String PASSWORD = "pass1111";

    /** The JDBC driver class name, depends on your JDBC driver */
    private final static String DRIVER_CLASSNAME = "org.gjt.mm.mysql.Driver";

    /** The JDBC URL of the database set, depends on your:
     *  database, driver, database server name and port number
     *  Since you probably will not have a DNS server on your network,
     *  most likely you need to use a numeric IP address of the server.
     */
    private final static String DATABASE_URL = "jdbc:mysql://localhost:3306/test";
//    private final static String DATABASE_URL = "jdbc:mysql://192.100.100.10:3306/test";

    /** The number of columns to fetch.  Only "int"s are supported now */
    private final static int FETCHED_COLUMN_COUNT = 3;

    /** The SQL query string to execute */
    private final static String QUERY_STRING =
        "SELECT Timer1, Timer2, Timer3 from recipes WHERE Day LIKE ? and Month LIKE ?";

    //-----</Configuration Data>-------------------------------------

    /** A place for static tli() methods to grab the Modlet instance. */
    private static JDBC   instance;

    /** A queue for pass requests from the ladder thread to the service
     *  thread which is created in init() within this Modlet.
     */
    private Queue outbox = new Queue(1);

    private ModletConfig    cfg;

    /** A JDBC statement that is built during Modlet init. */
    private PreparedStatement statement;

    /** A JDBC connection that is built during Modlet init. */
    private Connection connection;

    private int[] results = new int[FETCHED_COLUMN_COUNT];

    /** Indicates that the ladder app is waiting for a query to complete */
    private boolean appWaiting;

    /** Indicates that the service thread is busy with a Query */
    private boolean serviceBusy;


    //-----<Modlet Methods>-------------------------------------------

    public void init( ModletConfig cfg)
        throws ModletException
    {
        System.out.print("\nJDBC_TLM: initializing...");

        this.cfg = cfg;

        //  Save this instance for the static tli() method(s).
        instance = this;

        //  Register the TLI instruction(s)
        registerTLIs();

        try {
            openDatabase();
        } catch( ClassNotFoundException ex ) {
            System.out.println("\nJDBC_TLM: unable to find JDBC driver");
            throw new ModletException( ex.getMessage() );
        } catch( SQLException ex ) {
            System.out.println("\nJDBC_TLM: unable to open database");
            throw new ModletException( ex.getMessage() );
        }

        // Create a new Thread which will sleep on the outbox.
        start();

        System.out.println("OK");
    }

    public void destroy()
    {
        try{
            outbox.putElem(null);
        } catch( Exception ex ) {}

        if( connection != null )
        {
            try{
                connection.close();
            } catch( Exception ex ) {
                ex.printStackTrace();
            }
        }
        System.out.println("\nJDBC_TLM: terminated." );
    }

    public void setMode(int newMode)
        throws ModletException
    {
        //  not interested in mode.
    }

    public String getModletInfo()
    {
        return "JDBC Modlet sample";
    }

    public ModletConfig getModletConfig()
    {
        return cfg;
    }
    //-----</Modlet Methods>------------------------------------------

    public void run()
    {
        if( debug )
            System.out.println("Service Thread has started.");

        try {
            Query  query;

            while( (query = (Query) outbox.getElem() ) != null )
            {
                if(debug) System.out.println("Query");

                ResultSet rs;

                try {
                    rs = doQuery( query.day, query.month );
                } catch( SQLException ex ) {
                    if(debug) ex.printStackTrace();
                    setErrorWord( query.hError, ex );
                    serviceBusy = false;
                    continue;
                }

                //  Indicate success;
                SPLC.dt.putInt( query.hError, 0 );

                if( rs.next() )
                {
                    for( int i=0; i<FETCHED_COLUMN_COUNT; ++i )
                    {
                        results[i] = rs.getInt(i+1);
                        if(debug) System.out.println("results["+i+"]="+ results[i] );
                    }
                }

                SPLC.dt.write( query.hResults, FETCHED_COLUMN_COUNT, results );

                serviceBusy = false;
            }

        } catch( InterruptedException ex ) {
            //  Not expected, so terminate the thread
        } catch( SQLException ex ) {
//            System.out.println("bug in run()");
            ex.printStackTrace( System.out );
        }

        if( debug )
        {
            System.out.println();
            System.out.println("Service Thread ending.");
            System.out.flush();
        }
    }


    /**
     * Method registerTLIs
     * tells SoftPLC about the tli() method(s) so that the tli()
     * method(s) can be called from SoftPLC as a ladder instruction.
     * @throws ModletException during development only if the
     * java.lang.reflect.Method parameters are not correct.
     */
    private void registerTLIs()
        throws ModletException
    {
        //  Create an array of Ladder instr "parameter types".
        //  This array is from 0 - 9 elements long, depending on how
        //  many ladder parameters the Java method will take.
        //  This array will cause TOPDOC to enforce certain
        //  rules regarding allowed Ladder instruction
        //  parameters.  Java "interface TLI" defines all
        //  the "TLI." constants.
        PARMDEF[] ladder1Parms =
        {
            new PARMDEF(
                "Error:",                                   // name
                TLI.ARG_OUT | TLI.ARG_INT,                  // type
                1                                           // length in words
            ),

            new PARMDEF(
                "Results:",                                 // name
                TLI.ARG_OUT  | TLI.ARG_INT | TLI.ARG_FILE,  // type
                FETCHED_COLUMN_COUNT                        // length in words
            ),

            new PARMDEF(
                "Day:",                                     // name
                TLI.ARG_IN  | TLI.ARG_INT,                  // type
                1                                           // length in words
            ),

            new PARMDEF(
                "Month:",                                   // name
                TLI.ARG_IN  | TLI.ARG_INT,                  // type
                1                                           // length in words
            ),
        };

        //  Create an array of argument types that match those
        //  used by our ladder instruction method "tli1()".
        //  The method arguments must be in same order as our PARMDEF[]
        //  array ladder1Parms[]. The tli method must be a
        //  static XXX(boolean rungstate, int argc, ...)
        //  type method.
        Class method1Args[] = {
            Boolean.TYPE,           // rungstate
            Integer.TYPE,           // argc
            Long.TYPE,              // error: output params are handles => Long
            Long.TYPE,              // results: is a block(file), so we'll get a handle
            Integer.TYPE,           // day: is an integer
            Integer.TYPE,           // month: is an integer
        };

        // Construct a java.lang.reflect.Method instance for
        // the current class's "tli1" method, provided below.
        Method method1;
        try
        {
            method1 = getClass().getDeclaredMethod(
                "tli1",
                method1Args     // this must be just right
                );
        }
        catch( Exception e )
        {
            // This should only happen while developing, after that the
            // method1Args will be correct and we don't expect this.
            System.out.println( e );
            throw new ModletException(
                "user bug in PARMDEF[], java.lang.reflect.Method, or method name");
        }

        //  Declare the instruction to SoftPLC or TOPDOC/SoftWIRES
        SPLC.registerInstruction(
            "JDBC_READ",                // instruction name for TOPDOC
            "Read via JDBC",            // description for TOPDOC
            TLI.PERMISSIVE,             // type PERMISSIVE or OUTPUT
            0,                          // numoptionalargs
            ladder1Parms,               // array of PARMDEF
            method1                     // which method to call.
            );
    }


    /**
     * Method openDatabase
     * creates a JDBC Connection and gets ready for reading from the databse.
     *
     * @throws ClassNotFoundException if the JDBC driver cannot be found due
     * to a missing JAR or CLASSPATH problem.
     *
     * @throws SQLException if there is a problem opening the database.
     */
    private void openDatabase()
        throws SQLException, ClassNotFoundException
    {
        Class.forName( DRIVER_CLASSNAME );

        connection = DriverManager.getConnection(
                                    DATABASE_URL,
                                    USER_NAME,
                                    PASSWORD );

//        statement = connection.createStatement();
        statement = connection.prepareStatement(QUERY_STRING);
    }


    /**
     * Method doQuery
     * performs the fetching part of the database lookup.
     *
     * @param int The day of the month to find a recipe for.
     * @param int the month to find a recipe for.
     *
     * @return ResultSet
     *
     * @throws SQLException, see PreparedStatement.executeQuery()
     */
    private ResultSet doQuery( int day, int month )
        throws SQLException
    {
        statement.setInt(1, day);
        statement.setInt(2, month);
        return statement.executeQuery();
    }


    /**
     * Method tli1
     * is the ladder instruction that is named JDBC_READ by TOPDOC
     *
     * @param rungState A boolean that indictes whether the rung is enabled
     *  up to this point.
     * @param argc An int the gives the count of arguments.
     * @param hError Is a long that is the Datatable handle for
     *  the "Error:" word.
     * @param hResults Is a long that is the Datatable handle for the
     *  results block.
     * @param day Is an int containing the day of the month 1-31
     * @param month Is an int containing the month, 1-12.
     */
    private static boolean tli1( boolean rungState, int argc, long hError,
        long hResults, int day, int month )
    {
        JDBC        me = instance;
        ResultSet   rs;

        //  Edge triggered on false to true transition but under the
        //  condition that there is not already a pending transaction.
        //
        if( rungState && !me.appWaiting )
        {
            if( me.outbox.getCount() > 0 )
            {
                SPLC.dt.putInt(hError, ERROR_QFULL);
                return true;
            }

            Query q = new Query( day, month, hError, hResults );

            me.serviceBusy = true;

            try {
                me.outbox.putElem( q );
            } catch( InterruptedException ex ) {
                //  This should never happen.
                ex.printStackTrace();
            }

            me.appWaiting = true;
        }

        if( me.appWaiting && !me.serviceBusy )
        {
            if(debug) System.out.println("query completed");
            me.appWaiting = false;
        }



        return !me.appWaiting;
    }


    private void setErrorWord( long hError, SQLException ex )
    {
    }

    public static void main( String[] args )
    {
        JDBC    me = new JDBC();

        try {
            me.openDatabase();

            ResultSet rs = me.doQuery( 2, 12 );

            while( rs.next() )
            {
                int timer1 = rs.getInt(1);
                int timer2 = rs.getInt(2);
                int timer3 = rs.getInt(3);

                System.out.println("Timer1="+timer1
                    +" Timer2="+timer2
                    +" Timer3="+timer3 );
            }


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

//EOF