/*	Restaurent Database Oracle Client Program
	James O' Connor 50703338
	08/12/02
	This is my Original work.
*/

import java.io.*;
import java.sql.*;
import oracle.jdbc.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*; 

public class ResDB implements ActionListener 
{
	JFrame mainFrame;
	JPanel topPanel;		
	JPanel middlePanel;	
	JPanel bottomPanel;
	String[] data;
	String[] labelStrings;
	JComboBox stockItems;
	JButton viewStock;
	JButton saveStock;
	JButton sqlButt;
	JButton exitButt;
	JComponent[] stockVars;
	JLabel[] labels;
	JScrollPane k;
	public static Connection conn = null;
	public static Connection conn2 = null;
	int numCols = 0;

	public ResDB(Connection conn)
	{
		labelStrings = new String[7];
		labels = new JLabel[7];
		stockVars = new JComponent[7];
		this.initData(conn);
		this.GUI();
		getStockItem();
		
	}
	
	public static void main(String [] args)
	{	
		connectDB(conn);
		conn = conn2;
		ResDB r = new ResDB(conn);
	}

	public void actionPerformed(ActionEvent e) 
	{
		System.out.println(stockItems.getSelectedItem());
		JComponent b = (JComponent) e.getSource();
		System.out.println(b.getName());
		if(b.getName().equals("viewStock"))
			getStockItem();
		
		else if(b.getName().equals("saveStock"))
			saveStockItem();

		else if(b.getName().equals("sqlButt"))
			userSQL();

		else if(b.getName().equals("exitButt"))
			cleanExit();

    }

	public static void connectDB(Connection conn)
	{
		
		String username = "50703338";
		String password = "61814449";
		String tmp = "";

		System.out.println("Connecting to oracle server");

		try
		{

		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		for(int i=0; i<password.length(); i++)
			tmp = tmp + (char)(((int)password.charAt(i)) -1);
		password = tmp;

		conn = DriverManager.getConnection("jdbc:oracle:thin:@kiwi.isg.computing.dcu.ie:1521:kiwi",username,password);
        System.out.println("connectDB: "+conn);
		conn2 = conn;
		}
		catch(Exception e)
		{
			System.out.println("Problem is: "+e);
			JOptionPane.showMessageDialog(null, "The Following error occured:\n" + e);
		}
	}

	public static void disconnectDB(Connection conn)
	{
		if(conn != null)
		{
			try
			{
				conn.close();
				System.out.println("disconnectDB");
			}
			catch(Exception e)
			{
				System.out.println("Could'nt close connection \n"+e);
				JOptionPane.showMessageDialog(null, "The Following error occured:\n" + e);
			}
		}
	}
	
	public static ResultSet execSQL(Connection conn, String query)
	{
		ResultSet rs = null;
		
		try
		{

			for(int i=0; i<query.length(); i++)
			{
				if(query.charAt(i) == '\n')
					query = query.substring(0, (i - 1)) + " " + query.substring(0, (i + 1));
			}
			query.trim();
			if(query.substring(query.length() - 1).equals(";"))
				query = query.substring(0, query.length() - 1);

			Statement stmt = conn.createStatement();
			if(query.substring(0, 6).equalsIgnoreCase("SELECT"))
				rs = stmt.executeQuery(query);
			else
				stmt.executeUpdate(query);
			
		}
		catch(Exception e)
		{
			System.out.println("Problem in execSQL: "+e);
			JOptionPane.showMessageDialog(null, "The Following error occured:\n" + e);
		}

		return rs;
	}

	public void GUI()
	{

		// Create the frame and container.
		mainFrame = new JFrame("Restaurent Database: Stock Inventory");
		topPanel = new JPanel();
		topPanel.setLayout(new GridLayout(1, 5));

		middlePanel = new JPanel();
		middlePanel.setLayout(new GridLayout(6, 2));

		bottomPanel = new JPanel();
		bottomPanel.setLayout(new GridLayout(2, 2));
		
		// Add the widgets.
		stockItems = new JComboBox(data);
		topPanel.add(stockItems);

		viewStock = new JButton("View");
		viewStock.setName("viewStock");
		viewStock.addActionListener(this);
		topPanel.add(viewStock);

		saveStock = new JButton("Save Changes");
		saveStock.setName("saveStock");		
		saveStock.addActionListener(this);
		topPanel.add(saveStock);

		sqlButt = new JButton("Execute SQL");
		sqlButt.setName("sqlButt");
		sqlButt.addActionListener(this);
		topPanel.add(sqlButt);
				
		exitButt = new JButton("Exit");
		exitButt.setName("exitButt");
		exitButt.addActionListener(this);
		topPanel.add(exitButt);

		
		// Add widgets to middle container.
		for(int i=0; i<6; i++)
		{
			labels[i] = new JLabel(labelStrings[i], SwingConstants.RIGHT);
			middlePanel.add(labels[i]);
			labels[i].setText("");

			stockVars[i] = new JTextField(30);
			middlePanel.add(stockVars[i]);
			((JTextField)stockVars[i]).setText("");

		}

		labels[6] = new JLabel(labelStrings[6], SwingConstants.RIGHT);
		bottomPanel.add(labels[6]);

		JScrollPane j = new JScrollPane();
		j.setViewportView(new JTextArea(6, 30));
		stockVars[6] = j;
		bottomPanel.add(stockVars[6]);
		((JTextArea)(((JScrollPane)(stockVars[6])).getViewport().getView())).setText("");

		bottomPanel.add(new JLabel("Interact with Database:", SwingConstants.RIGHT));
		k = new JScrollPane();
		k.setViewportView(new JTextArea(6, 30));
		bottomPanel.add(k);
		
			
		
		// Add the top panel to the frame.
		mainFrame.getContentPane().add(topPanel, BorderLayout.NORTH);

		// Add the middle panel to the frame.
		mainFrame.getContentPane().add(middlePanel, BorderLayout.CENTER);

		// Add the bottom panel to the frame.
		mainFrame.getContentPane().add(bottomPanel, BorderLayout.SOUTH);

		// Exit when the window is closed.
		mainFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

		// Show the content.
		mainFrame.pack();
		mainFrame.setVisible(true);


	}

	public void initData(Connection conn)
	{	ResultSet rs = null;
		try
		{
			String initSQL = "SELECT * FROM StockInventory;";
			rs = execSQL(conn, initSQL);
			String[] tmpData = new String[2048];

			int m=0;
			int countY=0;
			while(rs.next())
			{
				tmpData[m] = rs.getString(1);
				m++;
			}
			
			data = new String[m];
			for(int i=0; i<m; i++)
				data[i] = tmpData[i];

			rs.close();

		}

		catch(Exception e)
		{
			System.out.println("initData failed: " + e);
			JOptionPane.showMessageDialog(null, "initData failed: " + e);
			
		}

	}

	public void getStockItem()
	{
		ResultSet rs = null;
		try
		{
			String getStockSQL = "SELECT * FROM StockInventory WHERE StockInventory.itemName = '";
			getStockSQL =getStockSQL + stockItems.getSelectedItem() + "';";
			rs = execSQL(conn, getStockSQL);
			
			int countX = 0;
			rs.next();
			numCols = rs.getMetaData().getColumnCount();
			for(int i=0; i<rs.getMetaData().getColumnCount(); i++)
			{
				if(rs.getMetaData().getColumnName(i+1).equalsIgnoreCase("DESCRIPTION"))
				{
					((JTextArea)(((JScrollPane)(stockVars[6])).getViewport().getView())).setText(rs.getString(i+1));
					labels[6].setText(rs.getMetaData().getColumnName(i+1));
				}

				else if(rs.getMetaData().getColumnName(i+1).equalsIgnoreCase("EXPIRATIONDATE"))
				{
					Date d = rs.getDate(i+1);
					if(d != null)
					{						
						((JTextField)stockVars[countX]).setText(d.toString());
						labels[countX].setText(rs.getMetaData().getColumnName(i+1));
					}

					else
					{
						((JTextField)stockVars[countX]).setText("");
						labels[countX].setText(rs.getMetaData().getColumnName(i+1));
					}
					countX++;
				}
				else
				{
					((JTextField)stockVars[countX]).setText(rs.getString(i+1));
					labels[countX].setText(rs.getMetaData().getColumnName(i+1));
					countX++;
				}
			}
			rs.close();
		}

		catch(Exception e)
		{
			System.out.println("getStockItem failed!: " + e);
			JOptionPane.showMessageDialog(null, "getStockItem failed!: " + e);
		}

		
	}

	public void saveStockItem()
	{
		String deleteStockSQL = "DELETE FROM StockInventory WHERE itemName='";
		deleteStockSQL = deleteStockSQL + ((JTextField)stockVars[0]).getText() + "'";
		String setStockSQL = "INSERT INTO StockInventory (";

		for(int i=0; i<7; i++)
		{
			if(!labels[i].getText().equals(""))
				setStockSQL = setStockSQL + labels[i].getText() + ",";
		}

		setStockSQL = setStockSQL.substring(0, (setStockSQL.length() -1));
		setStockSQL = setStockSQL + ") VALUES(";

		for(int i=0; i<6; i++)
		{
			if(labels[i].getText().equalsIgnoreCase("EXPIRATIONDATE") && !((JTextField)stockVars[i]).getText().equals(""))
			{
				setStockSQL = setStockSQL + "CAST(TO_DATE('" + ((JTextField)stockVars[i]).getText() 
					+ "', 'YYYY-MM-DD HH:MI:SS') AS DATE), "; 
			}
			else if(!labels[i].getText().equals(""))
				setStockSQL = setStockSQL + " '" + ((JTextField)stockVars[i]).getText() + "', "; 
		}

		setStockSQL = setStockSQL + "'" 
			+ ((JTextArea)(((JScrollPane)(stockVars[6])).getViewport().getView())).getText() 
			+ "')"; 

		System.out.println(deleteStockSQL + "\n" + setStockSQL);
		try
		{	
			ResultSet rs =execSQL(conn, deleteStockSQL);
			if(rs != null)
				rs.close();

			rs = execSQL(conn, setStockSQL);
			if(rs != null)
				rs.close();

			initData(conn);
			if( ! inList( ((JTextField)stockVars[0]).getText()) ) 
				stockItems.addItem(((JTextField)stockVars[0]).getText());
			stockItems.setSelectedItem(((JTextField)stockVars[0]).getText());
			stockItems.repaint();
		}

		catch(Exception e)
		{
			System.out.println("saveStockItem failed!: " + e);
			JOptionPane.showMessageDialog(null, "saveStockItem failed!: " + e);
		}
		
	}

	public void userSQL()
	{
		try
		{
			String userSQL = ((JTextArea)(k.getViewport().getView())).getText().trim();  
			ResultSet rs = execSQL(conn, userSQL);
			String result = "QUERY" + "\n=======================\n" 
					+ userSQL + "\n=======================\n"
					+ "RESULTS" + "\n=======================\n" ;

			while(rs != null && rs.next())
			{
				int ccount = rs.getMetaData().getColumnCount();
				for( int i = 1; i <= ccount; i++ )
					result = result + rs.getString(i) + "\t";
				result = result + "\n";
			}

			((JTextArea)(k.getViewport().getView())).setText(result);
			if(rs != null)
				rs.close();

			
		}

		catch(Exception e)
		{
			System.out.println("userSQL failed!: " + e);
			JOptionPane.showMessageDialog(null, "The Following error occured:\n" + e);
			

		}
	}

	public void cleanExit()
	{
		disconnectDB(conn);
		System.exit(0);
	}

	public boolean inList(String s)
	{
		for(int i=0; i<stockItems.getItemCount(); i++)
		{
			if(stockItems.getItemAt(i).equals(s))
				return true;
		}
		return false;
	}
}