/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package gui;

import data.IInstrument;
import data.INoteAmount;
import data.ITonality;
import database.DatabaseInterface;
import java.awt.Color;
import java.awt.Component;
import java.awt.Rectangle;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Tom
 */
public class MatrixDialogAverage extends javax.swing.JDialog {
   private String user = ""; //username
   private String pw = ""; //password
   private String url = ""; //DB-Url
   
   /**
    * Creates new form MatrixDialog
    * @param parent Parent des Dialogs
    * @param modal andere Fenster anklickbar
    * @param Pitches die Pitches
    * @param _title der Titel
    */
   public MatrixDialogAverage(java.awt.Frame parent, boolean modal, int[] Pitches, LinkedHashMap<String, Integer> instruments, LinkedHashMap<String, Integer> keys, String _title, String user, String pw, String url) {
      super(parent, modal);
      initComponents();
      
      this.user = user;
      this.pw = pw;
      this.url = url;
      
      tNotes.setDefaultRenderer(String.class, new CustomCellRenderer());
      tInstr1.setDefaultRenderer(Integer.class, new CustomNumberCellRenderer());
      tInstr2.setDefaultRenderer(Integer.class, new CustomNumberCellRenderer());
      tKey1.setDefaultRenderer(Integer.class, new CustomNumberCellRenderer());
      tKey2.setDefaultRenderer(Integer.class, new CustomNumberCellRenderer());
      
      Rectangle parentBounds = parent.getBounds();
      this.setBounds((int)(parentBounds.getWidth()/2-getBounds().getWidth()/2) + (int)parentBounds.getX(), 
                     (int)(parentBounds.getHeight()/2-getBounds().getHeight()/2) + (int)parentBounds.getY(), 
                     (int)getBounds().getWidth(), (int)getBounds().getHeight());
      
      this.setTitle(_title);
      fillPitches(Pitches);
      fillInstruments(instruments);
      fillKeys(keys);
   }
   
   /**
    * CellRenderer, der die Zellen der Pitchtabelle je nach Inhalt anders einfaerbt
    */
   class CustomCellRenderer extends DefaultTableCellRenderer{
      @Override
       public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected,boolean hasFocus, int row, int column){
               Component cell = super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, column);
               int x = 0;
               if (value != null) {
                  if (!value.toString().equals(""))
                  {
                     try {
                        x = Integer.valueOf(value.toString().substring(0, value.toString().length()-1));
                     }catch (NumberFormatException nfe) {
                        ;
                     }
                  }
                  if (x >= 20) { //bei mehr als 20% Anteil anders einfaerben
                     cell.setBackground(Color.MAGENTA);
                  }
                  else if (x >= 10) { //bei mehr als 10% Anteil anders einfaerben
                     cell.setBackground(Color.green);
                  }
                  else if (x >= 5) { //bei mehr als 5% Anteil anders einfaerben
                     cell.setBackground(Color.yellow);
                  }
                  else if (!value.toString().equalsIgnoreCase("")
                          && !value.toString().equalsIgnoreCase("0%")) {
                     cell.setBackground(Color.LIGHT_GRAY);
                  }
                  else
                  {
                      cell.setBackground(Color.white);
                  }
               }
               return cell;
       }
   }
   
   /**
    * CellRenderer, der die Zellen der Instrumenten- und Tonartentabelle anders einfaerbt
    */
   class CustomNumberCellRenderer extends DefaultTableCellRenderer{
      @Override
       public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected,boolean hasFocus, int row, int column){
               Component cell = super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, column);
               if (value == 0)
               {
                  cell.setBackground(Color.WHITE);
               } else {
                  cell.setBackground(Color.LIGHT_GRAY);
               }
               return cell;

       }
   }
   
   /**
    * fuellt die Tabelle mit den Prozentwerten der Pitches
    * @param Pitches ein Array mit den Pitches
    */
   private void fillPitches(int[] Pitches) {
      DefaultTableModel tableModel = (DefaultTableModel)tNotes.getModel();
      for (int i=0; i<11; i++) {
         tableModel.addRow(new Object[]{0,"","","","","","","","","","","",""});
      }
      //Oktaven
      for (int i=-1; i<=9; i++) {
         tableModel.setValueAt(i, i+1, 0);
      }
      //Pitches
      int iRow = 0, iColumn = 1;
      for (int i=0; i<Pitches.length; i++) {
         tableModel.setValueAt(Pitches[i] +"%", iRow, iColumn);
         iColumn++;
         if (iColumn > 12) {
            iColumn = 1;
            iRow++;
         }
      }
   }
   
   /**
    * befuellt die Tabellen mit den Vorkommnissen der Instrumentengruppen
    * @param instruments Liste der Instrumentengruppen
    */
   private void fillInstruments(LinkedHashMap<String, Integer> instruments) {
      DefaultTableModel tableModel = (DefaultTableModel)tInstr1.getModel();
      DefaultTableModel tableModel2 = (DefaultTableModel)tInstr2.getModel();
      Iterator it = instruments.entrySet().iterator();
      int i = 0;
      while (it.hasNext()) {
         Map.Entry pair = (Map.Entry)it.next();
         if (i <= 7) {
            tableModel.addRow(new Object[]{(String)pair.getKey(), (int)pair.getValue()});
         } else {
            tableModel2.addRow(new Object[]{(String)pair.getKey(), (int)pair.getValue()});
         }
         i++;
      }
   }
   
   /**
    * befuellt die Tabellen mit den Vorkommnissen der Tonarten
    * @param keys Liste der Tonarten
    */
   private void fillKeys(LinkedHashMap<String, Integer> keys) {
      DefaultTableModel tableModel = (DefaultTableModel)tKey1.getModel();
      DefaultTableModel tableModel2 = (DefaultTableModel)tKey2.getModel();
      Iterator it = keys.entrySet().iterator();
      int i = 0;
      while (it.hasNext()) {
         Map.Entry pair = (Map.Entry)it.next();
         if (i <= 11) {
            tableModel.addRow(new Object[]{(String)pair.getKey(), (int)pair.getValue()});
         } else {
            tableModel2.addRow(new Object[]{(String)pair.getKey(), (int)pair.getValue()});
         }
         i++;
      }
   }

   /**
    * 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.
    */
   @SuppressWarnings("unchecked")
   // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
   private void initComponents() {

      jScrollPane1 = new javax.swing.JScrollPane();
      tNotes = new javax.swing.JTable();
      jScrollPane2 = new javax.swing.JScrollPane();
      tInstr1 = new javax.swing.JTable();
      jScrollPane3 = new javax.swing.JScrollPane();
      tInstr2 = new javax.swing.JTable();
      jScrollPane4 = new javax.swing.JScrollPane();
      tKey1 = new javax.swing.JTable();
      jScrollPane5 = new javax.swing.JScrollPane();
      tKey2 = new javax.swing.JTable();
      jToolBar1 = new javax.swing.JToolBar();
      btnSave = new javax.swing.JButton();

      setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
      setResizable(false);

      tNotes.setModel(new javax.swing.table.DefaultTableModel(
         new Object [][] {
            {null, null, null, null, null, null, null, null, null, null, null, null, null},
            {null, null, null, null, null, null, null, null, null, null, null, null, null},
            {null, null, null, null, null, null, null, null, null, null, null, null, null},
            {null, null, null, null, null, null, null, null, null, null, null, null, null},
            {null, null, null, null, null, null, null, null, null, null, null, null, null},
            {null, null, null, null, null, null, null, null, null, null, null, null, null},
            {null, null, null, null, null, null, null, null, null, null, null, null, null},
            {null, null, null, null, null, null, null, null, null, null, null, null, null},
            {null, null, null, null, null, null, null, null, null, null, null, null, null},
            {null, null, null, null, null, null, null, null, null, null, null, null, null},
            {null, null, null, null, null, null, null, null, null, null, null, null, null}
         },
         new String [] {
            "Octave", "C", "C#", "D", "D#", "E", "F", "F#", "G", "G#", "A", "A#", "B"
         }
      ) {
         Class[] types = new Class [] {
            java.lang.Integer.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.String.class, java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.String.class
         };
         boolean[] canEdit = new boolean [] {
            false, false, false, false, false, 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];
         }
      });
      tNotes.setRowSelectionAllowed(false);
      jScrollPane1.setViewportView(tNotes);

      tInstr1.setModel(new javax.swing.table.DefaultTableModel(
         new Object [][] {

         },
         new String [] {
            "Instrument Group", "Count"
         }
      ) {
         Class[] types = new Class [] {
            java.lang.String.class, java.lang.Integer.class
         };
         boolean[] canEdit = new boolean [] {
            false, false
         };

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

         public boolean isCellEditable(int rowIndex, int columnIndex) {
            return canEdit [columnIndex];
         }
      });
      jScrollPane2.setViewportView(tInstr1);

      tInstr2.setModel(new javax.swing.table.DefaultTableModel(
         new Object [][] {

         },
         new String [] {
            "Instrument Group", "Count"
         }
      ) {
         Class[] types = new Class [] {
            java.lang.String.class, java.lang.Integer.class
         };
         boolean[] canEdit = new boolean [] {
            false, false
         };

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

         public boolean isCellEditable(int rowIndex, int columnIndex) {
            return canEdit [columnIndex];
         }
      });
      jScrollPane3.setViewportView(tInstr2);

      tKey1.setModel(new javax.swing.table.DefaultTableModel(
         new Object [][] {

         },
         new String [] {
            "Key", "Count"
         }
      ) {
         Class[] types = new Class [] {
            java.lang.String.class, java.lang.Integer.class
         };
         boolean[] canEdit = new boolean [] {
            false, false
         };

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

         public boolean isCellEditable(int rowIndex, int columnIndex) {
            return canEdit [columnIndex];
         }
      });
      jScrollPane4.setViewportView(tKey1);

      tKey2.setModel(new javax.swing.table.DefaultTableModel(
         new Object [][] {

         },
         new String [] {
            "Key", "Count"
         }
      ) {
         Class[] types = new Class [] {
            java.lang.String.class, java.lang.Integer.class
         };
         boolean[] canEdit = new boolean [] {
            false, false
         };

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

         public boolean isCellEditable(int rowIndex, int columnIndex) {
            return canEdit [columnIndex];
         }
      });
      jScrollPane5.setViewportView(tKey2);

      jToolBar1.setRollover(true);

      btnSave.setIcon(new javax.swing.ImageIcon(getClass().getResource("/icons/save.png"))); // NOI18N
      btnSave.setToolTipText("Save");
      btnSave.setFocusable(false);
      btnSave.setHorizontalTextPosition(javax.swing.SwingConstants.CENTER);
      btnSave.setVerticalTextPosition(javax.swing.SwingConstants.BOTTOM);
      btnSave.addActionListener(new java.awt.event.ActionListener() {
         public void actionPerformed(java.awt.event.ActionEvent evt) {
            btnSaveActionPerformed(evt);
         }
      });
      jToolBar1.add(btnSave);

      javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
      getContentPane().setLayout(layout);
      layout.setHorizontalGroup(
         layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
         .addComponent(jToolBar1, javax.swing.GroupLayout.DEFAULT_SIZE, 1024, Short.MAX_VALUE)
         .addGroup(layout.createSequentialGroup()
            .addContainerGap()
            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
               .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                  .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                     .addComponent(jScrollPane4)
                     .addComponent(jScrollPane2, javax.swing.GroupLayout.DEFAULT_SIZE, 499, Short.MAX_VALUE))
                  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                  .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                     .addGroup(layout.createSequentialGroup()
                        .addComponent(jScrollPane3, javax.swing.GroupLayout.DEFAULT_SIZE, 498, Short.MAX_VALUE)
                        .addGap(1, 1, 1))
                     .addComponent(jScrollPane5, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, 499, Short.MAX_VALUE)))
               .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 1004, Short.MAX_VALUE))
            .addContainerGap())
      );
      layout.setVerticalGroup(
         layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
         .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
            .addComponent(jToolBar1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
            .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 206, javax.swing.GroupLayout.PREFERRED_SIZE)
            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
               .addComponent(jScrollPane3, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
               .addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 179, javax.swing.GroupLayout.PREFERRED_SIZE))
            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
               .addComponent(jScrollPane4, javax.swing.GroupLayout.DEFAULT_SIZE, 228, Short.MAX_VALUE)
               .addComponent(jScrollPane5, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)))
      );

      pack();
   }// </editor-fold>//GEN-END:initComponents

   /**
    * store all the Data (Notes, Instruments, Keys) in the DB
    * @param evt 
    */
   private void btnSaveActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnSaveActionPerformed
      int cat;
      
      if (user.equalsIgnoreCase("") || pw.equalsIgnoreCase("") || url.equalsIgnoreCase(""))
      {
         JOptionPane.showMessageDialog(this, "Please make sure that you have a connection to the database.\nPlease log in.", "Error", JOptionPane.ERROR_MESSAGE);
      }
      else
      {
         DatabaseInterface dbi = new DatabaseInterface(user, pw, url);
         cat = dbi.insertOrGetCategory(this.getTitle(), user);
         dbi.closeConnection();

         if (cat == -1)
         {
            JOptionPane.showMessageDialog(this, "There was an error inserting/getting the category.", "Error", JOptionPane.ERROR_MESSAGE);
         }
         else
         {
            //get all Notes
            ArrayList<INoteAmount> na = new ArrayList<>();
            DefaultTableModel tableModel = (DefaultTableModel) tNotes.getModel();
            for (int i=0; i<tableModel.getRowCount(); i++) {
               for (int j=1; j<tableModel.getColumnCount(); j++) {
                  if (tableModel.getValueAt(i, j) != null) {
                     String s = tableModel.getValueAt(i, j).toString();
                     //nur Noten speichern, welche verwendet werden
                     if (!s.equalsIgnoreCase("") && !s.equalsIgnoreCase("0%")) {
                        INoteAmount ina = new INoteAmount();
                        ina.setName(tableModel.getColumnName(j));
                        ina.setOctave((int)tableModel.getValueAt(i, 0));
                        ina.setAmount(Integer.valueOf(s.substring(0, s.length()-1)));
                        ina.setPitch(i*12 + j-1);
                        ina.setCategory(cat);
                        na.add(ina);
                     }
                  }
               }
            }

            //get all instrument groups
            ArrayList<IInstrument> ii = new ArrayList<>();
            tableModel = (DefaultTableModel) tInstr1.getModel();
            for (int i=0; i<tableModel.getRowCount(); i++) {
               String s = tableModel.getValueAt(i, 1).toString();
               //nur Instrumentengruppen speichern, welche verwendet werden
               if (!s.equalsIgnoreCase("0")) {
                  IInstrument ins = new IInstrument();
                  ins.setCategory(cat);
                  ins.setInstrument(tableModel.getValueAt(i, 0).toString());
                  ins.setCount(Integer.valueOf(tableModel.getValueAt(i, 1).toString()));
                  ii.add(ins);
               }
            }
            tableModel = (DefaultTableModel) tInstr2.getModel();
            for (int i=0; i<tableModel.getRowCount(); i++) {
               String s = tableModel.getValueAt(i, 1).toString();
               String s2 = tableModel.getValueAt(i, 0).toString();
               //nur Instrumentengruppen speichern, welche verwendet werden und Percussions ignorieren
               if (!s.equalsIgnoreCase("0") && !s2.equalsIgnoreCase("Percussions")) {
                  IInstrument ins = new IInstrument();
                  ins.setCategory(cat);
                  ins.setInstrument(tableModel.getValueAt(i, 0).toString());
                  ins.setCount(Integer.valueOf(tableModel.getValueAt(i, 1).toString()));
                  ii.add(ins);
               }
            }

            //get all Keys (Tonalities)
            ArrayList<ITonality> it = new ArrayList<>();
            tableModel = (DefaultTableModel) tKey1.getModel();
            for (int i=0; i<tableModel.getRowCount(); i++) {
               String s = tableModel.getValueAt(i, 1).toString();
               String s2 = tableModel.getValueAt(i, 0).toString();
               //nur Tonarten speichern, welche verwendet werden
               if (!s.equalsIgnoreCase("0")) {
                  ITonality t = new ITonality();
                  t.setCategory(cat);
                  t.setKey(s2.substring(s2.indexOf(" ")+1));
                  t.setMajor(true);
                  t.setCount(Integer.valueOf(s));
                  it.add(t);
               }
            }
            tableModel = (DefaultTableModel) tKey2.getModel();
            for (int i=0; i<tableModel.getRowCount(); i++) {
               String s = tableModel.getValueAt(i, 1).toString();
               String s2 = tableModel.getValueAt(i, 0).toString();
               //nur Tonarten speichern, welche verwendet werden
               if (!s.equalsIgnoreCase("0")) {
                  ITonality t = new ITonality();
                  t.setCategory(cat);
                  t.setKey(s2.substring(s2.indexOf(" ")+1));
                  t.setMajor(false);
                  t.setCount(Integer.valueOf(s));
                  it.add(t);
               }
            }

            //store everything in Database
            dbi = new DatabaseInterface(user, pw, url);
            dbi.deleteNoteAmount(cat);
            for (INoteAmount n : na) {
               int k = dbi.insertNoteAmount(n);
               if (k == -1) {
                  JOptionPane.showMessageDialog(this, "There was an error inserting/updating the notes.", "Error", JOptionPane.ERROR_MESSAGE);
               }
            }
            dbi.deleteInstrument(cat);
            for (IInstrument i : ii) {
               int k = dbi.insertInstrument(i);
               if (k == -1) {
                  JOptionPane.showMessageDialog(this, "There was an error inserting/updating the instruments.", "Error", JOptionPane.ERROR_MESSAGE);
               }
            }
            dbi.deleteTonality(cat);
            for (ITonality t : it) {
               int k = dbi.insertTonality(t);
               if (k == -1) {
                  JOptionPane.showMessageDialog(this, "There was an error inserting/updating the tonalities.", "Error", JOptionPane.ERROR_MESSAGE);
               }
            }
            dbi.closeConnection();
            JOptionPane.showMessageDialog(this, "Category " +this.getTitle() +" saved in database.", "Information", JOptionPane.INFORMATION_MESSAGE);
         }
      }
   }//GEN-LAST:event_btnSaveActionPerformed

   /**
    * store all the Data (Notes, Instruments, Keys) in the DB
    */
   public void SaveInDatabase() {                                        
      int cat;
      
      if (user.equalsIgnoreCase("") || pw.equalsIgnoreCase("") || url.equalsIgnoreCase(""))
      {
         JOptionPane.showMessageDialog(this, "Please make sure that you have a connection to the database.\nPlease log in.", "Error", JOptionPane.ERROR_MESSAGE);
      }
      else
      {
         DatabaseInterface dbi = new DatabaseInterface(user, pw, url);
         cat = dbi.insertOrGetCategory(this.getTitle(), user);
         dbi.closeConnection();

         if (cat == -1)
         {
            JOptionPane.showMessageDialog(this, "There was an error inserting/getting the category.", "Error", JOptionPane.ERROR_MESSAGE);
         }
         else
         {
            //get all Notes
            ArrayList<INoteAmount> na = new ArrayList<>();
            DefaultTableModel tableModel = (DefaultTableModel) tNotes.getModel();
            for (int i=0; i<tableModel.getRowCount(); i++) {
               for (int j=1; j<tableModel.getColumnCount(); j++) {
                  if (tableModel.getValueAt(i, j) != null) {
                     String s = tableModel.getValueAt(i, j).toString();
                     //nur Noten speichern, welche verwendet werden
                     if (!s.equalsIgnoreCase("") && !s.equalsIgnoreCase("0%")) {
                        INoteAmount ina = new INoteAmount();
                        ina.setName(tableModel.getColumnName(j));
                        ina.setOctave((int)tableModel.getValueAt(i, 0));
                        ina.setAmount(Integer.valueOf(s.substring(0, s.length()-1)));
                        ina.setPitch(i*12 + j-1);
                        ina.setCategory(cat);
                        na.add(ina);
                     }
                  }
               }
            }

            //get all instrument groups
            ArrayList<IInstrument> ii = new ArrayList<>();
            tableModel = (DefaultTableModel) tInstr1.getModel();
            for (int i=0; i<tableModel.getRowCount(); i++) {
               String s = tableModel.getValueAt(i, 1).toString();
               //nur Instrumentengruppen speichern, welche verwendet werden
               if (!s.equalsIgnoreCase("0")) {
                  IInstrument ins = new IInstrument();
                  ins.setCategory(cat);
                  ins.setInstrument(tableModel.getValueAt(i, 0).toString());
                  ins.setCount(Integer.valueOf(tableModel.getValueAt(i, 1).toString()));
                  ii.add(ins);
               }
            }
            tableModel = (DefaultTableModel) tInstr2.getModel();
            for (int i=0; i<tableModel.getRowCount(); i++) {
               String s = tableModel.getValueAt(i, 1).toString();
               String s2 = tableModel.getValueAt(i, 0).toString();
               //nur Instrumentengruppen speichern, welche verwendet werden und Percussions ignorieren
               if (!s.equalsIgnoreCase("0") && !s2.equalsIgnoreCase("Percussions")) {
                  IInstrument ins = new IInstrument();
                  ins.setCategory(cat);
                  ins.setInstrument(tableModel.getValueAt(i, 0).toString());
                  ins.setCount(Integer.valueOf(tableModel.getValueAt(i, 1).toString()));
                  ii.add(ins);
               }
            }

            //get all Keys (Tonalities)
            ArrayList<ITonality> it = new ArrayList<>();
            tableModel = (DefaultTableModel) tKey1.getModel();
            for (int i=0; i<tableModel.getRowCount(); i++) {
               String s = tableModel.getValueAt(i, 1).toString();
               String s2 = tableModel.getValueAt(i, 0).toString();
               //nur Tonarten speichern, welche verwendet werden
               if (!s.equalsIgnoreCase("0")) {
                  ITonality t = new ITonality();
                  t.setCategory(cat);
                  t.setKey(s2.substring(s2.indexOf(" ")+1));
                  t.setMajor(true);
                  t.setCount(Integer.valueOf(s));
                  it.add(t);
               }
            }
            tableModel = (DefaultTableModel) tKey2.getModel();
            for (int i=0; i<tableModel.getRowCount(); i++) {
               String s = tableModel.getValueAt(i, 1).toString();
               String s2 = tableModel.getValueAt(i, 0).toString();
               //nur Tonarten speichern, welche verwendet werden
               if (!s.equalsIgnoreCase("0")) {
                  ITonality t = new ITonality();
                  t.setCategory(cat);
                  t.setKey(s2.substring(s2.indexOf(" ")+1));
                  t.setMajor(false);
                  t.setCount(Integer.valueOf(s));
                  it.add(t);
               }
            }

            //store everything in Database
            dbi = new DatabaseInterface(user, pw, url);
            dbi.deleteNoteAmount(cat);
            for (INoteAmount n : na) {
               int k = dbi.insertNoteAmount(n);
               if (k == -1) {
                  JOptionPane.showMessageDialog(this, "There was an error inserting/updating the notes.", "Error", JOptionPane.ERROR_MESSAGE);
               }
            }
            dbi.deleteInstrument(cat);
            for (IInstrument i : ii) {
               int k = dbi.insertInstrument(i);
               if (k == -1) {
                  JOptionPane.showMessageDialog(this, "There was an error inserting/updating the instruments.", "Error", JOptionPane.ERROR_MESSAGE);
               }
            }
            dbi.deleteTonality(cat);
            for (ITonality t : it) {
               int k = dbi.insertTonality(t);
               if (k == -1) {
                  JOptionPane.showMessageDialog(this, "There was an error inserting/updating the tonalities.", "Error", JOptionPane.ERROR_MESSAGE);
               }
            }
            dbi.closeConnection();
         }
      }
   }   
   
   public void closeDialog(){
       dispose();
   }
   
   /**
    * @param args the command line arguments
    */
   public static void main(String args[]) {
      /* Set the Nimbus look and feel */
      //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
       * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
       */
      try {
         for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
            if ("Nimbus".equals(info.getName())) {
               javax.swing.UIManager.setLookAndFeel(info.getClassName());
               break;
            }
         }
      } catch (ClassNotFoundException ex) {
         java.util.logging.Logger.getLogger(MatrixDialogAverage.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
      } catch (InstantiationException ex) {
         java.util.logging.Logger.getLogger(MatrixDialogAverage.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
      } catch (IllegalAccessException ex) {
         java.util.logging.Logger.getLogger(MatrixDialogAverage.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
      } catch (javax.swing.UnsupportedLookAndFeelException ex) {
         java.util.logging.Logger.getLogger(MatrixDialogAverage.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
      }
      //</editor-fold>

      /* Create and display the dialog */
      java.awt.EventQueue.invokeLater(new Runnable() {
         public void run() {
            MatrixDialogAverage dialog = new MatrixDialogAverage(new javax.swing.JFrame(), true, null, null, null, null, null, null, null);
            dialog.addWindowListener(new java.awt.event.WindowAdapter() {
               @Override
               public void windowClosing(java.awt.event.WindowEvent e) {
                  System.exit(0);
               }
            });
            dialog.setVisible(true);
         }
      });
   }
   // Variables declaration - do not modify//GEN-BEGIN:variables
   private javax.swing.JButton btnSave;
   private javax.swing.JScrollPane jScrollPane1;
   private javax.swing.JScrollPane jScrollPane2;
   private javax.swing.JScrollPane jScrollPane3;
   private javax.swing.JScrollPane jScrollPane4;
   private javax.swing.JScrollPane jScrollPane5;
   private javax.swing.JToolBar jToolBar1;
   private javax.swing.JTable tInstr1;
   private javax.swing.JTable tInstr2;
   private javax.swing.JTable tKey1;
   private javax.swing.JTable tKey2;
   private javax.swing.JTable tNotes;
   // End of variables declaration//GEN-END:variables
}
