/*********************************************************************/
 /*(c) Copyright IBM Corp. 2004  All rights reserved.                 */
 /*                                                                   */
 /*This sample program is owned by International Business Machines    */
 /*Corporation or one of its subsidiaries ("IBM") and is copyrighted  */
 /*and licensed, not sold.                                            */
 /*                                                                   */
 /*You may copy, modify, and distribute this sample program in any    */
 /*form without payment to IBM,  for any purpose including developing,*/
 /*using, marketing or distributing programs that include or are      */
 /*derivative works of the sample program.                            */
 /*                                                                   */
 /*The sample program is provided to you on an "AS IS" basis, without */
 /*warranty of any kind.  IBM HEREBY  EXPRESSLY DISCLAIMS ALL         */
 /*WARRANTIES EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO*/
 /*THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTIC-*/
 /*ULAR PURPOSE. Some jurisdictions do not allow for the exclusion or */
 /*limitation of implied warranties, so the above limitations or      */
 /*exclusions may not apply to you.  IBM shall not be liable for any  */
 /*damages you suffer as a result of using, modifying or distributing */
 /*the sample program or its derivatives.                             */
 /*                                                                   */
 /*Each copy of any portion of this sample program or any derivative  */
 /*work,  must include a the above copyright notice and disclaimer of */
 /*warranty.                                                          */
 /*                                                                   */
 /*********************************************************************/

// Generates an Excel spreadsheet from a given table

package com.ibm.ExcelDB2;

import java.awt.*;
import java.awt.event.*;
import java.sql.SQLException;
import java.util.List;
import javax.swing.*;
import org.apache.poi.hssf.usermodel.*;

public class DBTableGenerator extends JFrame implements Runnable {
	Thread runner;
	JProgressBar current;
	JLabel generatingLabel;
	String spreadsheetName = "";
	SQLFacade sqlFacade;
	SpreadsheetTableModel spreadsheetModel;
	
	/**
	* Method centerDialog.
	* Centers the dialog box to appear in the middle of the screen
	*/
    private void centerDialog() {
        Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
        int x = ((int) screenSize.getWidth() - (int) (screenSize.getWidth() / 2)) - (int) (getWidth() / 2);
        int y = ((int) screenSize.getHeight() - (int) (screenSize.getHeight() / 2)) - (int) (getHeight() / 2);
        setLocation(x, y);
    }

	/**
	 * @see java.lang.Runnable#run()
	 */
	public void run() {
		String sqlRowInsertQuery = "";
		String sqlCreateQuery = ""; 
		try {		
			// Create the Table with the name and credentials provided
			// Calculate the number sql calls we will complete to track progress
			int goal = (spreadsheetModel.getRowCount() + 1) * 2;
			int progressToGoal = 0;
			// call constructor to create a progress bar
			DBTableGenerator frame = new DBTableGenerator(goal, spreadsheetName);			
			frame.addWindowListener(new WindowAdapter() {
				public void windowClosing(WindowEvent e) {
					// Allow close of progress window
				}
			});
			frame.pack();
			frame.centerDialog();
			frame.setVisible(true);
			// Create the new table by calling the standard Create SQL
			//  Ex CREATE TABLE ADMIN.EMAIL_ADDRESSES ( NAME CHARACTER (100) , EMAIL CHARACTER (100)   ) ;
			sqlCreateQuery = "CREATE TABLE " + sqlFacade.getUser() + "." + spreadsheetName + " ( ";
			String sqlColumnNames = " ( ";
			// Make sure to neglect the first column, which was used for placeholder row numbers
			for(int col = 0; col < (spreadsheetModel.getColumnCount()-1); col++) {
				if (col>0) {
					sqlCreateQuery += ", ";
					sqlColumnNames += ", ";
				}
				sqlCreateQuery += spreadsheetModel.getColumnNames().get(col);			
				sqlColumnNames += spreadsheetModel.getColumnNames().get(col);			
				switch(((Integer)(spreadsheetModel.getColumnTypes().get(col))).intValue()) {
					// Convert numeric types the equivalent floats (guaranteed to work for all number types)
					case HSSFCell.CELL_TYPE_NUMERIC: sqlCreateQuery += " FLOAT "; break;
					// No need to convert strings
					case HSSFCell.CELL_TYPE_STRING: sqlCreateQuery += " CHARACTER (100) "; break; 
					// Convert formulas to their string representations
					case HSSFCell.CELL_TYPE_FORMULA: sqlCreateQuery += " CHARACTER (100) "; break; 
					// No need to convert blank cells
					case HSSFCell.CELL_TYPE_BLANK: sqlCreateQuery += " CHARACTER (100) "; break;
					// We have covered all the cell types POI/HSSF produce above, but just in case we will provide error handling for any others by throwing an exception.
					default:
						System.out.println("Current SQL statement: " + sqlCreateQuery); 
						throw new SQLException();  				
				}
			}
			sqlCreateQuery += " ); ";
			sqlColumnNames += " ) ";
			// update the progress bar
			frame.current.setValue(1);
			// Run the sql query
			sqlFacade.executeUpdate(sqlCreateQuery);
			// update the progress bar
			frame.current.setValue(2);
			progressToGoal = 2;
			// Populate the new DB table row-by-row from the spreadsheetModel data, converting each column as appropriate
			for(int row = 0; row < spreadsheetModel.getRowCount(); row++) {
				sqlRowInsertQuery = "INSERT INTO " + sqlFacade.getUser() + "." + spreadsheetName + sqlColumnNames + " VALUES (";
				for(int col = 0; col < spreadsheetModel.getColumnCount()-1; col++) {
					if (col > 0) {
						sqlRowInsertQuery += ", ";
					}
					sqlRowInsertQuery += "'" + ((HSSFCell)(spreadsheetModel.getRow(row).get(col))).getStringCellValue() + "'";
				}
				sqlRowInsertQuery += " ); ";
				// update the progress bar
				progressToGoal++;
				frame.current.setValue(progressToGoal);
				// Run the sql query
				sqlFacade.executeUpdate(sqlRowInsertQuery);
				progressToGoal++;
				frame.current.setValue(progressToGoal);
			}
			// update the status label when finished
			frame.generatingLabel.setText(spreadsheetName + " Generated");
		} catch (Exception e) {
			System.out.println("Current Create SQL statement: " + sqlCreateQuery); 
			System.out.println("Current Insert SQL statement: " + sqlRowInsertQuery); 
			e.printStackTrace();	
		}
	}

	/**
	 * Method DBTableGenerator.
	 * constructor used to create progress bar
	 * @param goal
	 * @param spreadsheetName
	 */
	public DBTableGenerator(int goal, String spreadsheetName) {
		super("DB Table Generation Progress");
		JPanel top = new JPanel();		
		top.setPreferredSize(new Dimension(300,30));
		top.setLayout(new FlowLayout());
		current= new JProgressBar(0,goal);
		current.setValue(0);
		generatingLabel = new JLabel("Generating DB Table " + spreadsheetName,JLabel.CENTER);
		current.setStringPainted(true);
		top.add(current);		
		JPanel pane = new JPanel();
		pane.setLayout(new BorderLayout());
		pane.add("North", top);
		pane.add("South", generatingLabel);
		setContentPane(pane);
	}
	
	/**
	* Method DBTableGenerator.
	* constructor called by main program. 	A threaded model was used so one could see the progress of the DB table creation
	* @param spreadsheetName
	* @param spreadsheetModel
	* @param facade
	*/ 
	public DBTableGenerator(String spreadsheetName, SpreadsheetTableModel spreadsheetModel, SQLFacade facade) {
		this.spreadsheetName = spreadsheetName;
		this.spreadsheetModel = spreadsheetModel;
		this.sqlFacade = facade;
		try {
			Thread runner = new Thread(this);
			runner.start();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}	
}