/*
 * TableTest.java
 *
 * Created on July 5, 2002, 2:45 PM
 */

package subjectData;

/**
 *
 * @author  Joset A. Etzel
 */
import subjectData.*; import java.sql.*; import java.awt.event.*; import javax.swing.*;  import javax.swing.table.*;

public class TableTest extends javax.swing.JInternalFrame {
    Connection conData; Statement stmSQL;   //variables for sql database access
  /** Creates new form TableTest */
    public TableTest() {
			initComponents();
      try { 
				Class.forName("org.gjt.mm.mysql.Driver"); 
				conData = SubjectData.getDatabaseConnection();
      } catch (Exception e) { e.printStackTrace(); }
      FillLists();  //call sub to put the choices in the list boxes
      ExcelAdapter myAd = new ExcelAdapter(tblResults);  //call code to let the table copy and paste into Excel
    }

    private void FillLists() {
      //this sub fills the list boxes with the appropriate choices from the database 
      String strList[]; int intSize = 0;
     
      try {   //fill up lstExperiment
        stmSQL = conData.createStatement();  //stmSQL is global - have to get it ready
        ResultSet rssSubjects = stmSQL.executeQuery("SELECT boxChoice FROM comboBoxes WHERE boxID = 'ExperimentType' ORDER BY boxChoice"); 
        while(rssSubjects.next()) {  intSize = intSize + 1; }  //count the number of rows in the resultset
        rssSubjects.beforeFirst();  //move to the start of the resultset again
        strList = new String[intSize];  //get the array ready to hold the data
        intSize = 0;
        //get the data out of the database
        while(rssSubjects.next()) { strList[intSize] = rssSubjects.getString("boxChoice");  //store the list item
          intSize = intSize + 1;  }  //update count for next time
        lstExperiment.setListData(strList); //done with the result set, so add the data to the list
      } catch(java.sql.SQLException e) { e.printStackTrace(); }     
      
      try {   //fill up lstType (subject type - normal, lesion, etc)
        stmSQL = conData.createStatement();  //stmSQL is global - have to get it ready
        ResultSet rssSubjects = stmSQL.executeQuery("SELECT boxChoice FROM comboBoxes WHERE boxID = 'SubjectType' ORDER BY boxChoice"); 
        while(rssSubjects.next()) {  intSize = intSize + 1; }  //count the number of rows in the resultset
        rssSubjects.beforeFirst();  //move to the start of the resultset again
        strList = new String[intSize];  //get the array ready to hold the data
        intSize = 0;
        //get the data out of the database
        while(rssSubjects.next()) { strList[intSize] = rssSubjects.getString("boxChoice");  //store the list item
          intSize = intSize + 1;  }  //update count for next time
        lstType.setListData(strList); //done with the result set, so add the data to the list
      } catch(java.sql.SQLException e) { e.printStackTrace(); }     
   }
    
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
  private void initComponents() {//GEN-BEGIN:initComponents
    java.awt.GridBagConstraints gridBagConstraints;

    jScrollPane1 = new javax.swing.JScrollPane();
    tblResults = new javax.swing.JTable();
    cmdExecute = new javax.swing.JButton();
    cmdClose = new javax.swing.JButton();
    lblSubject = new javax.swing.JLabel();
    lblType = new javax.swing.JLabel();
    lblExperiment = new javax.swing.JLabel();
    cmdReset = new javax.swing.JButton();
    jScrollPane2 = new javax.swing.JScrollPane();
    lstType = new javax.swing.JList();
    jScrollPane3 = new javax.swing.JScrollPane();
    lstExperiment = new javax.swing.JList();

    getContentPane().setLayout(new java.awt.GridBagLayout());

    setIconifiable(true);
    setMaximizable(true);
    setResizable(true);
    jScrollPane1.setMinimumSize(new java.awt.Dimension(600, 200));
    jScrollPane1.setPreferredSize(new java.awt.Dimension(600, 200));
    tblResults.setModel(new javax.swing.table.DefaultTableModel(
      new Object [][] {

      },
      new String [] {
        "#", "subID", "name or PPG", "subject type", "experiment", "session date", "resp", "HRV"
      }
    ) {
      Class[] types = new Class [] {
        java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.Boolean.class, java.lang.Boolean.class
      };
      boolean[] canEdit = new boolean [] {
        false, false, false, false, false, false, false, false
      };

      public Class getColumnClass(int columnIndex) {
        return types [columnIndex];
      }

      public boolean isCellEditable(int rowIndex, int columnIndex) {
        return canEdit [columnIndex];
      }
    });
    tblResults.setMinimumSize(new java.awt.Dimension(300, 64));
    jScrollPane1.setViewportView(tblResults);

    gridBagConstraints = new java.awt.GridBagConstraints();
    gridBagConstraints.gridx = 0;
    gridBagConstraints.gridy = 3;
    gridBagConstraints.gridwidth = 4;
    getContentPane().add(jScrollPane1, gridBagConstraints);

    cmdExecute.setText("Search");
    cmdExecute.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        cmdExecuteActionPerformed(evt);
      }
    });

    gridBagConstraints = new java.awt.GridBagConstraints();
    gridBagConstraints.gridx = 3;
    gridBagConstraints.gridy = 2;
    gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
    getContentPane().add(cmdExecute, gridBagConstraints);

    cmdClose.setText("Close");
    cmdClose.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        cmdCloseActionPerformed(evt);
      }
    });

    gridBagConstraints = new java.awt.GridBagConstraints();
    gridBagConstraints.gridx = 3;
    gridBagConstraints.gridy = 4;
    gridBagConstraints.insets = new java.awt.Insets(4, 0, 0, 0);
    gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
    getContentPane().add(cmdClose, gridBagConstraints);

    lblSubject.setText("Show subjects with these characteristics:");
    gridBagConstraints = new java.awt.GridBagConstraints();
    gridBagConstraints.gridx = 0;
    gridBagConstraints.gridy = 0;
    gridBagConstraints.gridwidth = 4;
    gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
    gridBagConstraints.insets = new java.awt.Insets(2, 0, 6, 0);
    getContentPane().add(lblSubject, gridBagConstraints);

    lblType.setText("Subject Type");
    gridBagConstraints = new java.awt.GridBagConstraints();
    gridBagConstraints.gridx = 0;
    gridBagConstraints.gridy = 1;
    gridBagConstraints.insets = new java.awt.Insets(0, 0, 2, 0);
    getContentPane().add(lblType, gridBagConstraints);

    lblExperiment.setText("Experiment");
    gridBagConstraints = new java.awt.GridBagConstraints();
    gridBagConstraints.gridx = 1;
    gridBagConstraints.gridy = 1;
    gridBagConstraints.insets = new java.awt.Insets(0, 0, 2, 0);
    getContentPane().add(lblExperiment, gridBagConstraints);

    cmdReset.setText("Reset");
    cmdReset.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        cmdResetActionPerformed(evt);
      }
    });

    gridBagConstraints = new java.awt.GridBagConstraints();
    gridBagConstraints.gridx = 2;
    gridBagConstraints.gridy = 2;
    gridBagConstraints.insets = new java.awt.Insets(0, 20, 0, 0);
    getContentPane().add(cmdReset, gridBagConstraints);

    jScrollPane2.setPreferredSize(new java.awt.Dimension(220, 100));
    lstType.setMinimumSize(new java.awt.Dimension(100, 50));
    lstType.setPreferredSize(new java.awt.Dimension(200, 80));
    jScrollPane2.setViewportView(lstType);

    gridBagConstraints = new java.awt.GridBagConstraints();
    gridBagConstraints.gridx = 0;
    gridBagConstraints.gridy = 2;
    gridBagConstraints.insets = new java.awt.Insets(0, 0, 5, 0);
    getContentPane().add(jScrollPane2, gridBagConstraints);

    jScrollPane3.setPreferredSize(new java.awt.Dimension(220, 100));
    lstExperiment.setMinimumSize(new java.awt.Dimension(100, 50));
    lstExperiment.setPreferredSize(new java.awt.Dimension(200, 80));
    jScrollPane3.setViewportView(lstExperiment);

    gridBagConstraints = new java.awt.GridBagConstraints();
    gridBagConstraints.gridx = 1;
    gridBagConstraints.gridy = 2;
    gridBagConstraints.insets = new java.awt.Insets(0, 10, 5, 0);
    getContentPane().add(jScrollPane3, gridBagConstraints);

    pack();
  }//GEN-END:initComponents

   private void cmdResetActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cmdResetActionPerformed
     //de-selects the choices in lstType & lstExperiment
     lstType.clearSelection();  
     lstExperiment.clearSelection();
   }//GEN-LAST:event_cmdResetActionPerformed

    private void cmdExecuteActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cmdExecuteActionPerformed
      //do the search based on list box selection and show the results in the table
			int intC = 0; int intExpRowCount = 0; Object[] strExpItems; String strSQL = "";
			Object objVec[] = {"", "", "", "", new Boolean(false), new Boolean(false)}; 
			Boolean BolTemp; int intSubID = 0; int intSesID = 0; String strSubType = "";
      
      DefaultTableModel tblModel = (DefaultTableModel)tblResults.getModel();  //get the table model for the table so can alter it
      if (tblModel.getRowCount() > 0) { tblModel.setRowCount(0); }  //get rid of previous rows, in case
      
      strExpItems = lstExperiment.getSelectedValues();  //load picked choices into object vector
      intExpRowCount = strExpItems.length;  //number of selected things in lstExperiment     
			if (intExpRowCount > 0) {  //need to do a selection from session based on expType
				strSQL = "SELECT subID, sessionID, expDate, expType FROM sessionData WHERE expType = '";
				intC = 0; 
				while (intC < intExpRowCount) { strSQL = strSQL + (String)strExpItems[intC] + "'"; 
					intC++;  //increment counter so can do test below
					if (intC < intExpRowCount) { strSQL = strSQL + " OR expType = '"; }
				}
			}
			else { strSQL = "SELECT subID, sessionID, expDate, expType FROM sessionData"; } //pick all expTypes
			
			try{
				stmSQL = conData.createStatement();  //retrieve subID, sessionID, expDate for chosen expType(s)
				ResultSet rssSubjects = stmSQL.executeQuery(strSQL);  
				rssSubjects.beforeFirst();
				intC = -1;  //set so counter comes out ok
				while (rssSubjects.next() == true) {  
					intSubID = rssSubjects.getInt("subID");
					intSesID = rssSubjects.getInt("sessionID");
					strSubType = GetSubjectType(intSubID);
							
					if( SubjectTypeMatches(strSubType) == true ) {  //correct type so show in table
						intC++; tblModel.addRow(objVec);  //add blank row to table
						tblModel.setValueAt("" + (intC + 1), intC, 0);  //counter in column 0
						tblModel.setValueAt("" + intSubID, intC, 1);  //column 1 is subject ID
						tblModel.setValueAt(GetNamePPG(intSubID), intC, 2);  //column 2 is subject name or PPG#					
						tblModel.setValueAt(strSubType, intC, 3);  //column 3 is subject type
						tblModel.setValueAt(rssSubjects.getString("expType"), intC, 4);  //column 4 is experiment type
						tblModel.setValueAt("" + rssSubjects.getDate("expDate"), intC, 5);  //column 5 is experiment date
						BolTemp = new Boolean(HasData(intSubID, intSesID, "respMeasures"));
						tblModel.setValueAt(BolTemp, intC, 6);  //column 6 is true/false for respiration data present
						BolTemp = new Boolean(HasData(intSubID, intSesID, "heartMeasures"));
						tblModel.setValueAt(BolTemp, intC, 7);  //column 7 is true/false for heart data present
					}
				}
				if( intC == -1 ) {  //no records in recordset, show user something
					tblModel.addRow(objVec);
					tblModel.setValueAt("no subjects", 0, 0);  //show subject counter
				}
			} catch(java.sql.SQLException e) { e.printStackTrace(); }
    }//GEN-LAST:event_cmdExecuteActionPerformed

		private boolean HasData(int intSubjectID, int intSessionID, String strTable) {
			//returns true if a listing for the subject and session in the table name passed, otherwise false
			String strTemp = ""; boolean bolDataPresent = true;
			
			strTemp = "SELECT * FROM " + strTable + " WHERE subID = \"" + intSubjectID + "\" AND sessionID = \"" + intSessionID + "\"";
			try { 
				ResultSet rssSubjects = stmSQL.executeQuery(strTemp);  //check for existing data in that table of the database
				if ( rssSubjects.next() == false ) { bolDataPresent = false; }  //no data in table, so can return false
				else { bolDataPresent =  true; }  //data in table, so return true
			}catch(java.sql.SQLException e) { e.printStackTrace(); } 
			
			return bolDataPresent;
		}
		
		private String GetNamePPG(int intSubjectID) {
			//returns the name or PPG# of the subject whose subjectID was passed
			int intC = 0; String strName = "";
			
			try{
        stmSQL = conData.createStatement(); 
				ResultSet rssSubjects = stmSQL.executeQuery("SELECT firstName, middleInitial, lastName, PPG FROM subject WHERE subID='" + intSubjectID + "'");
        intC = -1;  //set so counter comes out ok
        while(rssSubjects.next()) {  
          intC++; 
          strName = rssSubjects.getString("PPG");
          if( strName.equals("") == true ) { strName = rssSubjects.getString("lastName") + ", " + rssSubjects.getString("firstName") + " " + rssSubjects.getString("middleInitial") + "."; }
				}
			} catch(java.sql.SQLException e) { e.printStackTrace(); }
			
			return strName;
		}
		
		private String GetSubjectType(int intSubjectID) {
			//returns the subjectType of the subject whose subjectID was passed
			int intC = 0; String strType = "";
			
			try{
        stmSQL = conData.createStatement(); 
				ResultSet rssSubjects = stmSQL.executeQuery("SELECT subjectType FROM subject WHERE subID='" + intSubjectID + "'");
        intC = -1;  //set so counter comes out ok
        while(rssSubjects.next()) { intC++; strType = rssSubjects.getString("subjectType");	}
			} catch(java.sql.SQLException e) { e.printStackTrace(); }
			
			return strType;
		}
		
		private boolean SubjectTypeMatches(String strSubType) {
			//returns true if strSubType matches one of the picked subject types in lstType, false otherwise
			//if no subject types picked in lstType then returns true
			Object[] objTypeItems; int intTypeRowCount = 0; int intC = 0; boolean bolMatches = false;

			objTypeItems = lstType.getSelectedValues();  //load picked choices into object vector
      intTypeRowCount = objTypeItems.length;  //number of selected things in lstType
      
			if( intTypeRowCount == 0 ) { bolMatches = true; }  //nothing picked so all types ok
			else { 
				while( intC < intTypeRowCount ) { 
					if( objTypeItems[intC].equals(strSubType) == true ) { bolMatches = true; }
					intC++;
				}
			}
			return bolMatches;  //default false, so returns false unless changed in ifs
		}
		
    private void cmdCloseActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cmdCloseActionPerformed
        try {  this.setClosed(true); }
        catch( java.beans.PropertyVetoException e ) { e.printStackTrace(); }
    }//GEN-LAST:event_cmdCloseActionPerformed


  // Variables declaration - do not modify//GEN-BEGIN:variables
  private javax.swing.JButton cmdClose;
  private javax.swing.JButton cmdExecute;
  private javax.swing.JButton cmdReset;
  private javax.swing.JScrollPane jScrollPane1;
  private javax.swing.JScrollPane jScrollPane2;
  private javax.swing.JScrollPane jScrollPane3;
  private javax.swing.JLabel lblExperiment;
  private javax.swing.JLabel lblSubject;
  private javax.swing.JLabel lblType;
  private javax.swing.JList lstExperiment;
  private javax.swing.JList lstType;
  private javax.swing.JTable tblResults;
  // End of variables declaration//GEN-END:variables

}