package projecto;

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import com.borland.dbswing.*;
import com.borland.dx.sql.dataset.*;
import com.borland.dx.dataset.*;
import java.sql.*;
import javax.swing.JOptionPane.*;
/**
 * <p>Title: SQL Server + JBuilder Foundation</p>
 * <p>Description: BD Exemplo + Java</p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: DI-UBI</p>
 * @author Bases de dados
 * @version 1.0
 */

public class frameMain extends JFrame {
  JPanel contentPane;
  JMenuBar jMenuBar1 = new JMenuBar();
  JMenu jMenuFile = new JMenu();
  JMenuItem jMenuFileExit = new JMenuItem();
  JMenu jMenuHelp = new JMenu();
  JMenuItem jMenuHelpAbout = new JMenuItem();
  JToolBar jToolBar = new JToolBar();
  JButton jButton1 = new JButton();
  JButton jButton2 = new JButton();
  JButton jButton3 = new JButton();
  ImageIcon image1;
  ImageIcon image2;
  ImageIcon image3;
  JLabel statusBar = new JLabel();
  BorderLayout borderLayout1 = new BorderLayout();
  JMenu MenuDepartamento = new JMenu();
  JMenuItem miDepInserir = new JMenuItem();
  JMenuItem miDepAlterar = new JMenuItem();
  JMenuItem miDepEliminar = new JMenuItem();
  Database dbProjecto = new Database();
  QueryDataSet qdsDepSelect = new QueryDataSet();
  ParameterRow prDepValues = new ParameterRow();

  Column column1 = new Column();
  Column column2 = new Column();
  Column column3 = new Column();
  QueryDataSet qdsDepNext = new QueryDataSet();
  JMenuItem miDepRefrescar = new JMenuItem();
  TableScrollPane tableScrollPane1 = new TableScrollPane();
  JdbTable dbtDepartamentos = new JdbTable();

  private static final String DRIVER_NAME    = "sun.jdbc.odbc.JdbcOdbcDriver";
  private static final String DATABASE_NAME  = "jdbc:odbc:Projecto";

  private static final String SQL_SEL_DEPARTAMENTOS =
      "SELECT * FROM DEPARTAMENTO";

  private static final String SQL_SEL_NEXT_DEPNUM =
      "Select Max(DepNum) NextDep from Departamento";

  private static final String SQL_UPD_DEP =
       "Update Departamento\nSet Nome =?,\n    Local =?\nWhere DepNum = ?";

   private static final String SQL_INS_DEP =
       "Insert Into Departamento Values (:DepNum,:Nome,:Local)";

  //Construct the frame
  public frameMain() {
    enableEvents(AWTEvent.WINDOW_EVENT_MASK);
    try {
      jbInit();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

  //Component initialization
  private void jbInit() throws Exception {
    image1 = new ImageIcon(projecto.frameMain.class.getResource("openFile.png"));
    image2 = new ImageIcon(projecto.frameMain.class.getResource("closeFile.png"));
    image3 = new ImageIcon(projecto.frameMain.class.getResource("help.png"));
    contentPane = (JPanel)this.getContentPane();
    contentPane.setLayout(borderLayout1);
    this.setSize(new Dimension(461, 300));
    this.setTitle("SQL Server && JBuilder");
    statusBar.setText(" ");
    jMenuFile.setText("File");
    jMenuFileExit.setText("Exit");
    jMenuFileExit.addActionListener(new frameMain_jMenuFileExit_ActionAdapter(this));
    jMenuHelp.setText("Help");
    jMenuHelpAbout.setText("About");
    jMenuHelpAbout.addActionListener(new frameMain_jMenuHelpAbout_ActionAdapter(this));
    jButton1.setIcon(image1);
    jButton1.addActionListener(new frameMain_jButton1_actionAdapter(this));
    jButton1.setToolTipText("Open File");
    jButton2.setIcon(image2);
    jButton2.setToolTipText("Close File");
    jButton3.setIcon(image3);
    jButton3.setToolTipText("Help");

    MenuDepartamento.setText("Departamento");
    miDepInserir.setText("Inserir");
    miDepInserir.addActionListener(new frameMain_miDepInserir_actionAdapter(this));
    miDepAlterar.setText("Alterar");
    miDepAlterar.addActionListener(new frameMain_miDepAlterar_actionAdapter(this));
    miDepEliminar.setText("Eliminar");
    miDepEliminar.addActionListener(new frameMain_miDepEliminar_actionAdapter(this));
    dbProjecto.setConnection(
      new com.borland.dx.sql.dataset.ConnectionDescriptor(DATABASE_NAME,
        "sa", null, true,DRIVER_NAME));

    if (!dbProjecto.isOpen())
      dbProjecto.openConnection();

    qdsDepSelect.setReadOnly(true);
    qdsDepSelect.setEditable(false);
    qdsDepSelect.setResolvable(false);
    qdsDepSelect.setEnableInsert(false);
    qdsDepSelect.setEnableUpdate(false);
    qdsDepSelect.setEnableDelete(false);
    qdsDepSelect.setQuery(new com.borland.dx.sql.dataset.QueryDescriptor(
        dbProjecto, SQL_SEL_DEPARTAMENTOS, null, true, Load.ALL));

    qdsDepSelect.executeQuery();

    qdsDepNext.setQuery(new com.borland.dx.sql.dataset.QueryDescriptor(
        dbProjecto, SQL_SEL_NEXT_DEPNUM, null, true, Load.ALL));
    miDepRefrescar.setToolTipText("");
    miDepRefrescar.setText("Refrescar");
    miDepRefrescar.addActionListener(new frameMain_miDepRefrescar_actionAdapter(this));
    dbtDepartamentos.setDataSet(qdsDepSelect);
    jToolBar.add(jButton1);
    jToolBar.add(jButton2);
    jToolBar.add(jButton3);
    contentPane.add(jToolBar, BorderLayout.NORTH);
    jMenuFile.add(jMenuFileExit);
    jMenuHelp.add(jMenuHelpAbout);
    jMenuBar1.add(jMenuFile);
    jMenuBar1.add(jMenuHelp);
    jMenuBar1.add(MenuDepartamento);
    this.setJMenuBar(jMenuBar1);
    contentPane.add(statusBar, BorderLayout.SOUTH);
    contentPane.add(tableScrollPane1, BorderLayout.CENTER);
    tableScrollPane1.getViewport().add(dbtDepartamentos, null);
    MenuDepartamento.add(miDepInserir);
    MenuDepartamento.add(miDepAlterar);
    MenuDepartamento.add(miDepEliminar);
    MenuDepartamento.add(miDepRefrescar);

    column3.setColumnName("Local");
    column3.setDataType(com.borland.dx.dataset.Variant.STRING);
    column3.setDefault("");
    column3.setServerColumnName("Local");
    column3.setSqlType(0);
    column2.setColumnName("Nome");
    column2.setDataType(com.borland.dx.dataset.Variant.STRING);
    column2.setDefault("");
    column2.setServerColumnName("Nome");
    column2.setSqlType(0);
    column1.setColumnName("DepNum");
    column1.setDataType(com.borland.dx.dataset.Variant.INT);
    column1.setDefault("0");
    column1.setServerColumnName("DepNum");
    column1.setSqlType(0);
    prDepValues.setColumns(new Column[] {column1, column2, column3});
  }

//.............................................................................
// GetNextDepNum
// input:  none
// output: int
// purpose: get the next department number.
//.............................................................................
  private int GetNextDepNum() {
    // try to execute the query
    try {
      qdsDepNext.executeQuery();
    }
    catch (Exception ex) {
      return -1;
    }

    // gather the results
    DataRow resultRow = new DataRow(qdsDepNext);
    qdsDepNext.getDataRow(resultRow);

    // return the next dep number
    if (resultRow.isNull("NextDep"))
      return 1;
    else
      return 1 + resultRow.getInt("NextDep");
  }

//.............................................................................
// DepRefrescar
// input:  none
// output: int
// purpose: refresh 'departamento' data.
//.............................................................................
  private void DepRefrescar() {
    dbtDepartamentos.setDataSet(null);
    qdsDepSelect.close();
    qdsDepSelect.executeQuery();
    dbtDepartamentos.setDataSet(qdsDepSelect);
  }

//.............................................................................
// DepInserir
// input:
//     [in] DepNum - number of departamento
//     [in] Nome - name of departamento
//     [in] Local - location of departamento
// output: none
// purpose: insert a new raw in departamento table.
//.............................................................................
  private void DepInserir(int DepNum, String Nome, String Local) {
    // set the parameter raw values
    prDepValues.setInt("DepNum", DepNum);
    prDepValues.setString("Nome", Nome);
    prDepValues.setString("Local", Local);

    // Use a query provider ro execute a SQL Statement which don't
    // return a result set!
    QueryProvider qpDepInserir = new QueryProvider();
    qpDepInserir.executeStatement(dbProjecto,SQL_INS_DEP, prDepValues);

    qpDepInserir.closeStatement();
  }

//.............................................................................
// DepAlterar
// input:
//     [in] DepNum - number of departamento
//     [in] Nome - name of departamento
//     [in] Local - location of departamento
// output: none
// purpose: update a raw in departamento table, identified by DepNum.
//.............................................................................
  private void DepAlterar(int DepNum, String Nome, String Local) {
    // set the parameter raw values
/*
    prDepValues.setInt("DepNum", DepNum);
    prDepValues.setString("Nome", Nome);
    prDepValues.setString("Local", Local);

    QueryProvider qpDepAlterar = new QueryProvider();
    qpDepAlterar.executeStatement(dbProjecto,
                                  "Update Departamento\nSet Nome =:Nome,\n    Local =:Local\nWhere DepNum "
                                  + "= :DepNum", prDepValues);
    qpDepAlterar.closeStatement();
*/


   Connection _conn = dbProjecto.getJdbcConnection();
   try {

       // inserir despesa
       PreparedStatement sd  = _conn.prepareStatement(SQL_UPD_DEP);
       sd.setString(1, Nome);
       sd.setString(2, Local);
       sd.setInt(3, DepNum);
       sd.executeUpdate();
       sd.close();

     } catch (SQLException e) {
          System.err.println("ERRO: Inserir despesa - " + e.getMessage());
      }
  }

//.............................................................................
// DepEliminar
// input:
//     [in] DepNum - number of departamento
// output: none
// purpose: delete a raw from departamento table, identified by DepNum.
//.............................................................................
  private void DepEliminar(int DepNum) {
    // Use database executeStatement method to execute a SQL Statement
    // which don't resturn a result set and don't have parameters!
    dbProjecto.executeStatement("Delete from Departamento  Where DepNum ="
                                + Integer.toString(DepNum));
  }

  //File | Exit action performed
  public void jMenuFileExit_actionPerformed(ActionEvent e) {
    dbProjecto.closeConnection(); //--- close connection
    System.exit(0); // and exit!
  }

  //Help | About action performed
  public void jMenuHelpAbout_actionPerformed(ActionEvent e) {
    frameMain_AboutBox dlg = new frameMain_AboutBox(this);
    Dimension dlgSize = dlg.getPreferredSize();
    Dimension frmSize = getSize();
    Point loc = getLocation();
    dlg.setLocation( (frmSize.width - dlgSize.width) / 2 + loc.x,
                    (frmSize.height - dlgSize.height) / 2 + loc.y);
    dlg.setModal(true);
    dlg.pack();
    dlg.show();

  }

  //Overridden so we can exit when window is closed
  protected void processWindowEvent(WindowEvent e) {
    super.processWindowEvent(e);
    if (e.getID() == WindowEvent.WINDOW_CLOSING) {
      jMenuFileExit_actionPerformed(null);
    }
  }

  void jButton1_actionPerformed(ActionEvent e) {

  }

  void miDepInserir_actionPerformed(ActionEvent e) {
    dlgDepInserir dlg = new dlgDepInserir(this, "Inserir departamento", true);

    Dimension dlgSize = dlg.getPreferredSize();
    Dimension frmSize = getSize();
    Point loc = getLocation();
    dlg.setLocation( (frmSize.width - dlgSize.width) / 2 + loc.x,
                    (frmSize.height - dlgSize.height) / 2 + loc.y);
    dlg.setModal(true);
    dlg.pack();

    StringBuffer Nome = new StringBuffer("");
    StringBuffer Local = new StringBuffer("");
    int nextDep;

    while (true) {
      nextDep = GetNextDepNum(); // get the next depnum

      if (dlg.showDepInsAlt(nextDep, Nome, Local) == 0) {
        try {
          DepInserir(nextDep, Nome.toString(), Local.toString());
          DepRefrescar(); // refresh data
          break;
        }
        catch (Exception ex) {
          if (JOptionPane.showConfirmDialog(this,
                                            ex.getMessage() +
                                            "\n\nTENTAR NOVAMENTE?",
                                            "Atenção!!!",
                                            JOptionPane.YES_NO_OPTION) !=
              JOptionPane.OK_OPTION)
            break;
        }
      }
      else
        break;
    }
  }

  void miDepAlterar_actionPerformed(ActionEvent e) {
    dlgDepInserir dlg = new dlgDepInserir(this, "Alterar departamento", true);

    Dimension dlgSize = dlg.getPreferredSize();
    Dimension frmSize = getSize();
    Point loc = getLocation();
    dlg.setLocation( (frmSize.width - dlgSize.width) / 2 + loc.x,
                    (frmSize.height - dlgSize.height) / 2 + loc.y);
    dlg.setModal(true);
    dlg.pack();

    DataRow resultRow = new DataRow(qdsDepSelect);
    qdsDepSelect.getDataRow(resultRow);

    if (!resultRow.isAssignedNull("DepNum")) {
      int nextDep = resultRow.getInt("DepNum");
      StringBuffer Nome = new StringBuffer(resultRow.getString("Nome"));
      StringBuffer Local = new StringBuffer(resultRow.getString("Local"));

      if (dlg.showDepInsAlt(nextDep, Nome, Local) == 0) {
        try {
          DepAlterar(nextDep, Nome.toString(), Local.toString());
          DepRefrescar();
        }
        catch (Exception ex) {
          JOptionPane.showMessageDialog(this, ex.getMessage(),
                                        "ERRO: ao actualizar o departamento.",
                                        JOptionPane.ERROR_MESSAGE);
        }

      }
    }
  }

  void miDepEliminar_actionPerformed(ActionEvent e) {
    DataRow resultRow = new DataRow(qdsDepSelect);
    qdsDepSelect.getDataRow(resultRow);

    if (!resultRow.isNull("DepNum")) {
      int DepNum = resultRow.getInt("DepNum");
      String Nome = resultRow.getString("Nome");
      String Local = resultRow.getString("Local");

      if (JOptionPane.showConfirmDialog(this,
                                        Integer.toString(DepNum) + " " + Nome +
                                        "\n\nEliminar?",
                                        "Eliminar departamento...",
                                        JOptionPane.YES_NO_OPTION) ==
          JOptionPane.OK_OPTION) {
        try {
          DepEliminar(DepNum);
          DepRefrescar();
        }
        catch (Exception ex) {
          JOptionPane.showMessageDialog(this, ex.getMessage(),
                                        "ERRO: ao eliminar do departamento.",
                                        JOptionPane.ERROR_MESSAGE);
        }

      }
    }
  }

  void miDepRefrescar_actionPerformed(ActionEvent e) {
    DepRefrescar();
  }
}

class frameMain_jMenuFileExit_ActionAdapter implements ActionListener {
  frameMain adaptee;

  frameMain_jMenuFileExit_ActionAdapter(frameMain adaptee) {
    this.adaptee = adaptee;
  }
  public void actionPerformed(ActionEvent e) {
    adaptee.jMenuFileExit_actionPerformed(e);
  }
}

class frameMain_jMenuHelpAbout_ActionAdapter implements ActionListener {
  frameMain adaptee;

  frameMain_jMenuHelpAbout_ActionAdapter(frameMain adaptee) {
    this.adaptee = adaptee;
  }
  public void actionPerformed(ActionEvent e) {
    adaptee.jMenuHelpAbout_actionPerformed(e);
  }
}

class frameMain_jButton1_actionAdapter implements java.awt.event.ActionListener {
  frameMain adaptee;

  frameMain_jButton1_actionAdapter(frameMain adaptee) {
    this.adaptee = adaptee;
  }
  public void actionPerformed(ActionEvent e) {
    adaptee.jButton1_actionPerformed(e);
  }
}

class frameMain_miDepInserir_actionAdapter implements java.awt.event.ActionListener {
  frameMain adaptee;

  frameMain_miDepInserir_actionAdapter(frameMain adaptee) {
    this.adaptee = adaptee;
  }
  public void actionPerformed(ActionEvent e) {
    adaptee.miDepInserir_actionPerformed(e);
  }
}

class frameMain_miDepAlterar_actionAdapter implements java.awt.event.ActionListener {
  frameMain adaptee;

  frameMain_miDepAlterar_actionAdapter(frameMain adaptee) {
    this.adaptee = adaptee;
  }
  public void actionPerformed(ActionEvent e) {
    adaptee.miDepAlterar_actionPerformed(e);
  }
}

class frameMain_miDepEliminar_actionAdapter implements java.awt.event.ActionListener {
  frameMain adaptee;

  frameMain_miDepEliminar_actionAdapter(frameMain adaptee) {
    this.adaptee = adaptee;
  }
  public void actionPerformed(ActionEvent e) {
    adaptee.miDepEliminar_actionPerformed(e);
  }
}

class frameMain_miDepRefrescar_actionAdapter implements java.awt.event.ActionListener {
  frameMain adaptee;

  frameMain_miDepRefrescar_actionAdapter(frameMain adaptee) {
    this.adaptee = adaptee;
  }
  public void actionPerformed(ActionEvent e) {
    adaptee.miDepRefrescar_actionPerformed(e);
  }
}

