package hsql_bsp3;

import java.awt.*;
import java.awt.event.*;
import java.util.*;
import javax.swing.*;
import javax.swing.table.*;

import java.io.*;
import javax.swing.JFileChooser;
import javax.swing.filechooser.*;


public class FDBSConsole  extends JDialog implements ActionListener {
    public boolean retcode=false;    
    
    private JList listEntities = new JList();
    private JList listAttrib = new JList();
    private JTextArea editor = new JTextArea("SELECT * FROM mitarbeiter");
    private JTable table = null;
    private Vector<String> listTables = new Vector<String>();
    private Vector<TableColumns> listColumns = new Vector<TableColumns>();
    
    Dbs dbs = Dbs.getDbsInstance();            
    DataTable dt=null;
    
    private final JMenuItem mnOpenSQL = new JMenuItem("Laden einer Textdatei");
    private final JMenuItem mnSaveSQL = new JMenuItem("Speichern der SQL-Anweisung");
    private final JMenuItem mnClose = new JMenuItem("Schließen");
    
    private final JMenuItem mnRun = new JMenuItem("Starten");
    private final JMenuItem mnExport2Excel = new JMenuItem("Export nach Excel");
    
    private final JButton bnOpenSQL = new JButton("Laden sql");
    private final JButton bnSaveSQL = new JButton("Laden sql");
    private final JButton bnStartSQL = new JButton("Starten SQL");
    
    
    public FDBSConsole(Frame parent) {
       super(parent);

        setModal(true);
        setTitle("Datenbank-Console");
        setSize(800,520);
        retcode=false;
        setGUI();
        setMenues();
        setToolBars();
        setTables();
        setVisible(true);
    }
    
    private void setGUI() {
        table = new JTable( this.createTableModel() );
      setLayout( new BorderLayout() );
      JSplitPane splitLeft = new JSplitPane(JSplitPane.VERTICAL_SPLIT,false,     
        new JScrollPane(listEntities), new JScrollPane(listAttrib) );
      splitLeft.setDividerLocation(250);

      JSplitPane splitRight = new JSplitPane(JSplitPane.VERTICAL_SPLIT,false,     
        new JScrollPane(editor), new JScrollPane(table) );
      splitRight.setDividerLocation(150);
    
      JSplitPane splitMain = new JSplitPane(JSplitPane.HORIZONTAL_SPLIT,false,     
            splitLeft, splitRight );
    
      splitMain.setDividerLocation(150);
      this.getContentPane().add(splitMain, BorderLayout.CENTER);
      
      table.setFont(new Font("Arial", Font.BOLD, 14));
      
      listEntities.addMouseListener(new MouseAdapter() {
            public void mouseClicked(MouseEvent e) {
                listEntities_click(e);
            }
        });      
    }    // setGUI
    
    
    
    private TableModel createTableModel() {
        TableModel dataModel = new AbstractTableModel() {
            public int getColumnCount() {
                if (dt==null)
                    return 1;
                else 
                    return dt.headerColums.size();
            }
            public int getRowCount() {
                if (dt==null)
                    return 1;
                else 
                    return dt.tableRows.size();
            }

            public Object getValueAt(int row, int col) {
                if (dt==null)
                    return 1;
                else {
                    ArrayList tupels = dt.tableRows.get(row);
                    return tupels.get(col);
                }
            }

            public String getColumnName(int column) {
                if (dt==null)
                    return "Column";
                else 
                    return dt.headerColums.get(column);
            }
         };
        
        return dataModel;
    }  // createDataModel
    
    
    private void setMenues() {
        JMenu mainFile = new JMenu("Datei");
        
        mnOpenSQL.setAccelerator( KeyStroke.getKeyStroke(KeyEvent.VK_O, ActionEvent.CTRL_MASK));
        mnSaveSQL.setAccelerator( KeyStroke.getKeyStroke(KeyEvent.VK_S, ActionEvent.CTRL_MASK));
        mnRun.setAccelerator( KeyStroke.getKeyStroke(KeyEvent.VK_F5, 0));
        
        mainFile.add(mnOpenSQL);
        mainFile.add(mnSaveSQL);
        mainFile.addSeparator();
        mainFile.add(mnClose);
        
        JMenu mainRun = new JMenu("Starten");
        mainRun.add(mnRun);
        
        JMenu mainExport = new JMenu("Export");
        mainExport.add(mnExport2Excel);
        

        JMenuBar menuBar1 = new JMenuBar();
        menuBar1.add(mainFile);
        menuBar1.add(mainRun);
        menuBar1.add(mainExport);
        this.setJMenuBar(menuBar1);
        
        mnOpenSQL.addActionListener(this);
        mnSaveSQL.addActionListener(this);
        mnClose.addActionListener(this);
        mnRun.addActionListener(this);
        mnExport2Excel.addActionListener(this);
                
        for (int i = 0; i < menuBar1.getComponentCount(); i++) {
              JMenu main = (JMenu) menuBar1.getComponent(i);
              main.setFont(new Font("Arial", Font.BOLD, 14));
              for (int j = 0; j < main.getItemCount(); j++) {
                Component c = main.getItem(j);
                if (c instanceof JMenuItem) {
                  JMenuItem mn = (JMenuItem) c;
                  mn.setFont(new Font("Arial", Font.BOLD, 14));
                }
              }
        }        
    } // setMenues    
    
 private void setToolBars() {
    JToolBar toolBar = new JToolBar();

    toolBar.add(bnOpenSQL);
    toolBar.add(bnSaveSQL);
    toolBar.addSeparator( new Dimension(20,10) );
    toolBar.add(bnStartSQL);
    bnOpenSQL.addActionListener(this);    
    bnSaveSQL.addActionListener(this);    
    bnStartSQL.addActionListener(this);    
    
    this.getContentPane().add(toolBar, BorderLayout.NORTH);
  } // setToolBars
      
    
    private void setTables() {   
        //dbs.setDbs(databasefilename);        
        listTables = dbs.getTables();
        listEntities.setListData(listTables);
    } // setTables
    
    private void listEntities_click(MouseEvent e) {      
        int i = listEntities.getSelectedIndex();
        if (i>=0) {
            String item = listTables.get(i);
            switch (e.getClickCount() ) {
                case 1: setColumns(item);
                        break;
                case 2: break;
            }
        }
    } // listEntities_click
    
    private void setColumns(String table) {        
        listColumns = dbs.getColumns(table);
        listAttrib.setListData(listColumns);
    } // setColumns
    
    
    @Override
    public void actionPerformed(ActionEvent e) {
        if (e.getSource()==mnClose) {
            this.dispose();
        }     
        if ((e.getSource()==mnOpenSQL) || (e.getSource()==bnOpenSQL) ) {
            mnLoad_click();
        }     
        if ((e.getSource()==mnSaveSQL) || (e.getSource()==bnSaveSQL) ) {
            mnSave_click();
        }     
        if ( (e.getSource()==mnRun)|| (e.getSource()==bnStartSQL) ) {
            mnRun_click();
        } 
        if (e.getSource()==mnExport2Excel) {
            mnExport2Excel_click();
        } 
    }  // actionPerformed
        
    

    
    private void mnLoad_click() {
        JFileChooser jFileChooser1 = new JFileChooser();
          // setzen der Optionen
        jFileChooser1.setMultiSelectionEnabled(false);
        jFileChooser1.setCurrentDirectory(  new File(  (String) System.getProperties().get("user.dir")  )   );

           // Aufruf des Dialogfensters
         if (JFileChooser.APPROVE_OPTION == jFileChooser1.showOpenDialog(this)) {
             // holen des Dateinamens
           readTxtFile( jFileChooser1.getSelectedFile().getPath() );
         }        
    }
    
    private void readTxtFile(String filename) {
        FileInputStream fin;
        InputStreamReader iin;
        LineNumberReader din;
        StringBuilder sb= new StringBuilder();
        try {
          fin = new FileInputStream(filename);
          iin = new InputStreamReader(fin);
          din = new LineNumberReader(iin);  // oder BufferedReader br = new BufferedReader(ir);
          while  ( din.ready() ) {
            sb.append(din.readLine());
            sb.append("\n");
          }
          editor.setText( sb.toString() );
        }
         catch (FileNotFoundException e1) {
             Basis.ErrorMsg("Die Datei war nicht vorhanden!", "Fehler");
        }
        catch (IOException ee) {
               System.err.println("IOException: " + ee);
        }

    }
    private void mnSave_click() {
        JFileChooser jFileChooser1 = new JFileChooser();
          // setzen der Optionen
        jFileChooser1.setMultiSelectionEnabled(false);
        jFileChooser1.setCurrentDirectory(  new File(  (String) System.getProperties().get("user.dir")  )   );

           // Aufruf des Dialogfensters
         if (JFileChooser.APPROVE_OPTION == jFileChooser1.showSaveDialog(this)) {
             // holen des Dateinamens
           saveTxtFile( jFileChooser1.getSelectedFile().getPath() );
         }        
        
    }
    
    private void saveTxtFile(String filename) {
    try {
        FileOutputStream fout = new FileOutputStream(filename); 
        PrintStream p = new PrintStream(fout);  
        p.println(editor.getText());
        p.close();
       }
       catch (IOException ee) {
           System.err.println("IOException: " + ee);
       }        
    }  // saveTxtFile
    
    
    private void mnRun_click() {
        String sql = editor.getText().trim();
        if (sql.length()>0) {
            dt = dbs.selectDBS(sql);            
            table.setModel( createTableModel() );
        }                
    }
    
    
    private void mnExport2Excel_click() {
        JFileChooser jFileChooser1 = new JFileChooser();
          // setzen der Optionen
        jFileChooser1.setMultiSelectionEnabled(false);
        jFileChooser1.setCurrentDirectory(  new File(  (String) System.getProperties().get("user.dir")  )   );

           // Aufruf des Dialogfensters
         if (JFileChooser.APPROVE_OPTION == jFileChooser1.showSaveDialog(this)) {
             // holen des Dateinamens
           saveTable2Excel( jFileChooser1.getSelectedFile().getPath() );
         }                
    }
    
    private void saveTable2Excel( String filename ) {
        Export2BIFF excel = new Export2BIFF( filename );
        excel.openFile();
        for (int col=0; col<dt.headerColums.size(); col++) {
            excel.Write(col,0, dt.headerColums.get(col) );  // col row Value
        }
        for (int row=0; row<dt.tableRows.size(); row++) {
            ArrayList tupels = dt.tableRows.get(row);
            for (int col=0; col<tupels.size(); col++) {
                excel.Write(col,row+1, tupels.get(col) );  // col row Value
            }                    
        }
        excel.closeFile();
        StartExcel(filename);  
        
    }  // saveTable2Excel
    
    private void StartExcel(String filename) {
        String excel = "\"C:\\Program Files (x86)\\Microsoft Office\\Office14\\EXCEL.EXE\"";

        try {
          // funktioniert nur mit vollstaendigen Pfad
          Runtime.getRuntime().exec( excel+" "+filename);
        }
        catch (IOException e) {
           System.out.println("Error in Start Excel");
        }
    } // StartExcel
    
}