import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Calendar;
import java.util.Formatter;

public class UpdateTsmDataDB
{
	static final String JDBC_DRIVER 		= "com.mysql.jdbc.Driver";
	static final String SQL_TSMDATA_DB_URL 	= "jdbc:mysql://p8810/tsmdata";
	static final String TSM_DB_USERID       = "TSM";
	static final String TSM_DB_PASSWORD		= "s8mm3r";
	
	public UpdateTsmDataDB() 
	{
		try {
			Class.forName( JDBC_DRIVER ).newInstance();
		} catch ( IllegalAccessException iae ) {
			iae.printStackTrace();
			System.exit(1);
		} catch ( InstantiationException ie ) {
			ie.printStackTrace();
			System.exit(1);
		} catch( ClassNotFoundException classNotFound ) {
			classNotFound.printStackTrace();
			System.exit(1);
		}
	}

	public void CollectOccupancy() {
		Connection oConnection = null;
		Statement  oStatement  = null;
		
		Formatter Date = new Formatter();
		Formatter Time = new Formatter();
		Calendar now = Calendar.getInstance();
		Date.format("%tF", now );
		Time.format("%tT", now );
		
		System.out.println("CollectTsmData starting.");
			
		try {
			oConnection = DriverManager.getConnection( SQL_TSMDATA_DB_URL, 
													   TSM_DB_USERID, 
											           TSM_DB_PASSWORD );
		    oStatement  = oConnection.createStatement();
		
			TsmServers tsmServers = new TsmServers( oConnection ); 
			String SelectStatement = "SELECT * FROM OCCUPANCY";

			System.out.println("TSM Occupancy Collection Beginning.");
			oConnection.setAutoCommit(false);						// Start transaction
			while( tsmServers.nextConnection()) {
				System.out.printf("Collecting from " + tsmServers.getTsmServerName());
				
				ResultSet resultSet = tsmServers.getTsmStatement().executeQuery( SelectStatement );
				ResultSetMetaData metaData = resultSet.getMetaData();
				
				int cols = metaData.getColumnCount();
				String FieldNames = "ServerName, Date, Time";
				for( int i = 1; i <= cols; i++ ) {
					FieldNames = FieldNames + ", " + metaData.getColumnName(i);
				}
				int RowsInserted = 0;
				while( resultSet.next() ) {
					String InsertStmt = "INSERT INTO occupancy ( " + FieldNames + " ) " + 
									"VALUES ('" + tsmServers.getTsmServerName() + "', " +
											"'" + Date + "', '" + Time + "'";
					for( int i = 1; i <= cols; i++ ) {
						InsertStmt = InsertStmt + ", '" + resultSet.getString(i) + "'";
					}
					InsertStmt = InsertStmt + ")";
					oStatement.executeUpdate( InsertStmt );
					RowsInserted++;
				}
				System.out.println("  Rows inserted=" + RowsInserted);
			}
			oConnection.commit();									// Commit the transaction
			oStatement.close();										// close the statement
			oConnection.close();									// close the connection
			
			System.out.println("TSM Occupancy Collection Complete.");
			
		} catch( SQLException sqlException ) {
			sqlException.printStackTrace();
			System.exit(1);
		} catch( ClassNotFoundException classNotFound ) {
			classNotFound.printStackTrace();
			System.exit(1);
		}
	}
	
	public void UpdateTrendTable() {
		
		Connection oConnection = null;
		CallableStatement CallProcStmt = null;

		System.out.println("Updating Trend Table.");
		try {
			oConnection = DriverManager.getConnection( SQL_TSMDATA_DB_URL, 
					   TSM_DB_USERID, 
			           TSM_DB_PASSWORD );
 			CallProcStmt = oConnection.prepareCall("{call UpdateTrendTable()}");
 		    CallProcStmt.execute();
			System.out.println("Call UpdateTrendTable() completed.");
		    CallProcStmt.close();
		
			oConnection.close();
		} catch( SQLException sqlException ) {
			sqlException.printStackTrace();
			System.exit(1);
		}
	}
	
	public void TrimOccupancyTable() {
		
		Connection oConnection = null;
		Statement oStatement   = null;

		System.out.println("Trimming old rows from occupancy table.");
		try {
			oConnection = DriverManager.getConnection( SQL_TSMDATA_DB_URL, 
					   TSM_DB_USERID, 
			           TSM_DB_PASSWORD );
			oStatement  = oConnection.createStatement();
		    int RowsDeleted = oStatement.executeUpdate( "DELETE FROM Occupancy where Date < SubDate(current_date,365)" );
		    System.out.println("Deleted " + RowsDeleted + " old rows from occupancy.");
		} catch( SQLException sqlException ) {
			sqlException.printStackTrace();
			System.exit(1);
		}
	}
}