import ianywhere.ml.script.*;
import java.io.*;
import java.sql.*;

public class MobiLinkOrders {

  // class level DBConnectionContext
  DBConnectionContext _cc;
    
  public MobiLinkOrders( DBConnectionContext cc )
    throws IOException, FileNotFoundException {
    // declare a class-level DBConnectionContext
    _cc = cc;
  }


  //  method for the handle_UploadData synchronization event
  public void GetUpload( UploadData ut )
    throws SQLException, IOException { 
    //  get an UploadedTableData for OrderComments
    UploadedTableData orderCommentsTbl = ut.getUploadedTableByName("OrderComments");

    // get inserts uploaded by the MobiLink client
    ResultSet insertResultSet = orderCommentsTbl.getInserts();
    try {
      // connect to the excel worksheet through ODBC
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Connection con = DriverManager.getConnection( "jdbc:odbc:excel_datasource" );

      while( insertResultSet.next() ) {
        // get order comments
        int _commentID = insertResultSet.getInt("comment_id");
        int _orderID = insertResultSet.getInt("order_id");
        String _specialComments = insertResultSet.getString("order_comment");

        // execute an insert statement to add the order comment to the worksheet
        Statement st = con.createStatement();
        st.executeQuery( "insert into [order_sheet$]"
          + "(order_id, comment_id, order_comment) VALUES ("
          + Integer.toString(_orderID) + ", "
          + Integer.toString(_commentID) + ", '"
          + _specialComments + "')");      
        st.close();
      }
      con.close();
    } catch(Exception ex) {
      System.err.print("Exception: ");
      System.err.println(ex.getMessage());
    }
    insertResultSet.close();
  }

    
  public void SetDownload() throws SQLException, IOException {

    DownloadData download_d = _cc.getDownloadData();
 
    DownloadTableData download_td = download_d.getDownloadTableByName( "OrderComments" );
  
    // Prepared statement to compile upserts (inserts or updates).
    PreparedStatement download_upserts = download_td.getUpsertPreparedStatement();
    try {
      // connect to the excel worksheet through ODBC
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Connection con = DriverManager.getConnection( "jdbc:odbc:excel_datasource" );

      // retrieve all the rows in the worksheet
      Statement st = con.createStatement();
      ResultSet Excel_rs = st.executeQuery( "select * from [order_sheet$]" );

      while (Excel_rs.next()) {
        // retrieve the row data
        int Excel_comment_id = Excel_rs.getInt(1);
        int Excel_order_id = Excel_rs.getInt(2);
        String Excel_comment = Excel_rs.getString(3);

        // add the Excel data to the MobiLink download.
        download_upserts.setInt( 1, Excel_comment_id );
        download_upserts.setInt( 2, Excel_order_id );
        download_upserts.setString( 3, Excel_comment ); 
        download_upserts.executeUpdate();
      }
      // close the excel result set, statement, and connection.
      Excel_rs.close();
      st.close();
      con.close();
    } catch (Exception ex) {
      System.err.print("Exception: ");
      System.err.println(ex.getMessage());
    }

    download_upserts.close();
  }

}