// Calling our method to populate the jtable
private void updateJTable() {
try {
String sql = "Select totalcost, Amountpaid, Balance FROM payments ";
st = conn.prepareStatement(sql);
rs = st.executeQuery(sql);
jTable1.setModel(DbUtils.resultSetToTableModel(rs));
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
}
private void updateTableKhachHang() {
String sql = "select * from TableKhachHang";
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
jTableKhachHang.setModel(DbUtils.resultSetToTableModel(rs));
} catch (Exception e) {
JOptionPane.showConfirmDialog(null, e);
}
}
private void Update_table() {
try {
String sql = "SELECT * FROM trans where userid=? order by date asc ";
pst = conn.prepareStatement(sql);
pst.setString(1, branch.getText());
rs = pst.executeQuery();
jTable1.setModel(DbUtils.resultSetToTableModel(rs));
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
}
private void Update_table() {
try {
Connection conn = getConnect();
String sql = "SELECT * FROM LoggedInPeople";
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
PeopleLoggedIn.setModel(DbUtils.resultSetToTableModel(rs));
closeConnect();
} catch (Exception e) {
System.out.println(e);
}
}
public void getData() {
String sql =
"select item_category as 'ItemCate',item_code as 'ItCode',item_name as 'ItName',supplier_name as 'SuppName',brand_company as 'BrandName',opening_stock as 'OppStock',purchase_rate as 'PurRate',selling_rate as 'SellRate',warranty as 'Warr',re_order as 'ReOrder' from item ";
try {
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
Table.setModel(DbUtils.resultSetToTableModel(rs));
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
}
private void Update_Table() {
try {
String sql = "select * from Bank_001";
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
table.setModel(DbUtils.resultSetToTableModel(rs));
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private void jButton4ActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton4ActionPerformed
// TODO add your handling code here:
try {
String sql =
"select Package_ID,Destination,Hotel,Day,Price\n"
+ "from Package where Cost_Type='Standard'\n"
+ "and Package_Type='Summer special'";
statement = conn.createStatement();
rs = statement.executeQuery(sql);
this.jTable1.setModel(DbUtils.resultSetToTableModel(rs));
} catch (Exception e) {
e.printStackTrace();
}
} // GEN-LAST:event_jButton4ActionPerformed
private void viewalllllActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_viewalllllActionPerformed
try {
String sql3 =
"SELECT date,SUM(cost) AS TotalAmountReceived FROM trans WHERE branch=? GROUP BY date order by date asc";
pst = conn.prepareStatement(sql3);
pst.setString(1, branch.getText());
rs = pst.executeQuery();
jTable1.setModel(DbUtils.resultSetToTableModel(rs));
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
} // GEN-LAST:event_viewalllllActionPerformed
private void viewallamountActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_viewallamountActionPerformed
Object item = date1.getText();
Object item1 = date2.getText();
String sql =
"select date,SUM(cost) AS TotalAmountReceived from trans where branch=? and date between '"
+ item
+ "' and '"
+ item1
+ "'GROUP BY date order by date";
try {
pst = conn.prepareStatement(sql);
pst.setString(1, branch.getText());
rs = pst.executeQuery();
jTable1.setModel(DbUtils.resultSetToTableModel(rs));
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
} // GEN-LAST:event_viewallamountActionPerformed
public void viewDbSVS_Personal() {
truncateTempDbTable();
insertIntoTempDbTable();
connection c = new connection();
Connection con = c.conn();
try {
Statement stmt = con.createStatement();
ResultSet rs =
stmt.executeQuery(
"SELECT X.vk_Id,X.transDate,X.description, X.debit, X.credit, SUM(Y.bal) balance FROM( SELECT *,credit-debit bal FROM tbl_temp_account_VK) X JOIN( SELECT *,credit-debit bal FROM tbl_temp_account_VK) Y ON Y.id <= X.id WHERE MONTH(X.transDate)=MONTH(CURRENT_DATE) and YEAR(X.transDate)=YEAR(CURRENT_DATE) GROUP BY X.id");
jTable1.setModel(DbUtils.resultSetToTableModel(rs));
jTable1.getColumnModel().getColumn(0).setMinWidth(50);
jTable1.getColumnModel().getColumn(0).setMaxWidth(50);
jTable1.getColumnModel().getColumn(1).setMinWidth(100);
jTable1.getColumnModel().getColumn(1).setMaxWidth(100);
jTable1.getColumnModel().getColumn(3).setMinWidth(100);
jTable1.getColumnModel().getColumn(3).setMaxWidth(100);
jTable1.getColumnModel().getColumn(4).setMinWidth(100);
jTable1.getColumnModel().getColumn(4).setMaxWidth(100);
jTable1.getColumnModel().getColumn(5).setMinWidth(100);
jTable1.getColumnModel().getColumn(5).setMaxWidth(100);
jTable1.getColumnModel().getColumn(1).setCellRenderer(new DateCellRenderer());
jTable1.getColumnModel().getColumn(3).setCellRenderer(NumberRenderer.getIntegerRenderer());
jTable1.getColumnModel().getColumn(4).setCellRenderer(NumberRenderer.getIntegerRenderer());
jTable1.getColumnModel().getColumn(5).setCellRenderer(NumberRenderer.getIntegerRenderer());
jTable1.setAutoCreateRowSorter(true);
con.close();
} catch (Exception e) {
}
}
private void jButton6ActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton6ActionPerformed
// TODO add your handling code here:
if (jTextField2.getText().isEmpty() == false && jTextField4.getText().isEmpty() == false) {
try {
PreparedStatement statement =
conn.prepareStatement(
"insert into Booking(Name,E_mail,Phone,Package_ID) values (?, ?, ?, ?)");
statement.setString(1, jTextField1.getText());
statement.setString(2, jTextField2.getText());
statement.setString(3, jTextField3.getText());
statement.setString(4, jTextField4.getText());
statement.executeUpdate();
JOptionPane.showMessageDialog(null, "successfully inserted");
// this.jTable1.setModel(DbUtils.resultSetToTableModel(rs));
jTextField1.setText(null);
jTextField2.setText(null);
jTextField3.setText(null);
jTextField4.setText(null);
} catch (Exception e) {
e.printStackTrace();
}
} else {
if (jTextField2.getText().isEmpty())
JOptionPane.showMessageDialog(null, "please insert E-mail");
else JOptionPane.showMessageDialog(null, "please insert Package ID");
}
try {
String sql = "select Booking_No,name,Package_id from booking";
statement = conn.createStatement();
rs = statement.executeQuery(sql);
this.jTable1.setModel(DbUtils.resultSetToTableModel(rs));
} catch (Exception e) {
e.printStackTrace();
}
} // GEN-LAST:event_jButton6ActionPerformed
private void jButton5ActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton5ActionPerformed
// TODO add your handling code here:
try {
String sql = "DELETE FROM Booking\n" + "WHERE E_mail='" + jTextField5.getText() + "'";
PreparedStatement statement = conn.prepareStatement(sql);
statement.executeUpdate();
JOptionPane.showMessageDialog(null, "successfully deleted");
jTextField5.setText(null);
} catch (Exception e) {
e.printStackTrace();
}
try {
String sql = "select Booking_No,name,Package_id from booking";
statement = conn.createStatement();
rs = statement.executeQuery(sql);
this.jTable1.setModel(DbUtils.resultSetToTableModel(rs));
} catch (Exception e) {
e.printStackTrace();
}
} // GEN-LAST:event_jButton5ActionPerformed
public View_OR(UserType usr) throws SQLException {
initComponents();
vw = usr;
if (vw.t == 1) {
jLabel6.setVisible(false);
} else if (vw.t == 0) {
jLabel6.setVisible(true);
jButton2.setVisible(false);
jButton3.setVisible(false);
jTextArea2.setVisible(false);
jLabel5.setVisible(false);
jTextField2.setText(vw.id);
} else {
jLabel6.setVisible(true);
jLabel5.setVisible(false);
jButton2.setVisible(false);
jButton3.setVisible(false);
jTextArea2.setVisible(false);
}
q.pst = q.conn.prepareStatement("select * from meo");
q.rs = q.pst.executeQuery();
jTable1.setModel(DbUtils.resultSetToTableModel(q.rs));
}
public void Update_table() {
try {
// Statement st=conn.createStatement();
String sql =
"Select checkIn.id,rooms.roomid as 'room id',roomname as 'room name',roomtypedesc as 'room description',format(roomprice,2)as price,customername as 'customer name',DATE_FORMAT(checkInDate,'%b %d %Y') as 'Check In Date' from checkIn,rooms,roomtype where rooms.roomid=checkIn.roomid and rooms.roomtypeid=roomtype.roomtypeid and rooms.available='No' and checkIn.status='in'";
// rs=st.executeQuery(sql); //if using simple query not prepared statment
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
tbl_rooms.setModel(DbUtils.resultSetToTableModel(rs));
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
} finally {
try {
rs.close();
pst.close();
} catch (Exception e) {
}
}
}
public View_OR() throws SQLException {
initComponents();
q.pst = q.conn.prepareStatement("select * from meo");
q.rs = q.pst.executeQuery();
jTable1.setModel(DbUtils.resultSetToTableModel(q.rs));
}
private void buscarStocksMensualesProductosTerminadosButtonActionPerformed(
java.awt.event.ActionEvent
evt) { // GEN-FIRST:event_buscarStocksMensualesProductosTerminadosButtonActionPerformed
if (parametroComboBox.getSelectedIndex() >= 0) {
if (!valorTextField.getText().equals("")) {
switch (parametroComboBox.getSelectedIndex()) {
case 0:
try {
String sql =
"SELECT PT_Codificacion_CaracterizadoEn_PT AS \"Codificación\","
+ "SM_PT_Fecha AS \"Fecha\","
+ "SM_PT_Inicio AS \"Inicio\","
+ "SM_PT_Ingreso AS \"Ingreso\","
+ "SM_PT_Egreso AS \"Egreso\","
+ "SM_PT_CantidadCalculada AS \"Cant Calculada\","
+ "SM_PT_CantidadReal AS \"Cant Real\","
+ "SM_PT_Diferencia AS \"Diferencia\" "
+ "FROM StocksMensualesProductosTerminados WHERE PT_Codificacion_CaracterizadoEn_PT = ? ORDER BY SM_PT_Fecha";
pst = conn.prepareStatement(sql);
pst.setString(1, valorTextField.getText().toUpperCase());
rs = pst.executeQuery();
stocksMensualesProductosTerminadosTable.setModel(DbUtils.resultSetToTableModel(rs));
stocksMensualesProductosTerminadosTable.setEnabled(false);
} catch (Exception e) {
JOptionPane.showMessageDialog(
this,
e.getMessage(),
"Error al realizar la búsqueda de stocks mensuales de productos terminados",
JOptionPane.ERROR_MESSAGE);
}
break;
case 1:
try {
String sql =
"SELECT PT_Codificacion_CaracterizadoEn_PT AS \"Codificación\","
+ "SM_PT_Fecha AS \"Fecha\","
+ "SM_PT_Inicio AS \"Inicio\","
+ "SM_PT_Ingreso AS \"Ingreso\","
+ "SM_PT_Egreso AS \"Egreso\","
+ "SM_PT_CantidadCalculada AS \"Cant Calculada\","
+ "SM_PT_CantidadReal AS \"Cant Real\","
+ "SM_PT_Diferencia AS \"Diferencia\" "
+ "FROM StocksMensualesProductosTerminados WHERE SM_PT_Fecha = ? ORDER BY PT_Codificacion_CaracterizadoEn_PT";
pst = conn.prepareStatement(sql);
int anio = Integer.parseInt(valorTextField.getText().substring(0, 4));
int mes =
Integer.parseInt(valorTextField.getText().substring(5, 7))
- 1; // DEBO RESTAR UNO PORQUE EN JAVA LOS MESES EMPIEZAN EN 0 !!!!!!! o.0
int dia = Integer.parseInt(valorTextField.getText().substring(8, 10));
Calendar fechaIngresada = new GregorianCalendar(anio, mes, dia);
java.util.Date fecha = new java.util.Date(fechaIngresada.getTimeInMillis());
java.sql.Date fechaActual = new java.sql.Date(fecha.getTime());
pst.setDate(1, fechaActual);
rs = pst.executeQuery();
stocksMensualesProductosTerminadosTable.setModel(DbUtils.resultSetToTableModel(rs));
stocksMensualesProductosTerminadosTable.setEnabled(false);
} catch (Exception e) {
JOptionPane.showMessageDialog(
this,
e.getMessage(),
"Error al realizar la búsqueda de stocks mensuales de productos terminados",
JOptionPane.ERROR_MESSAGE);
}
break;
}
} else {
JOptionPane.showMessageDialog(
this,
"El valor del parámetro de búsqueda no puede ser vacio.",
"Error al buscar stocks mensuales de insumo",
JOptionPane.ERROR_MESSAGE);
}
} else {
JOptionPane.showMessageDialog(
this,
"El parámetro de búsqueda no puede ser vacio.",
"Error al buscar stocks mensuales de insumo",
JOptionPane.ERROR_MESSAGE);
}
} // GEN-LAST:event_buscarStocksMensualesProductosTerminadosButtonActionPerformed
private void jButton1ActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton1ActionPerformed
try {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
con = DriverManager.getConnection("jdbc:derby:millmanage;create=true", "app", "");
Object d = day.getSelectedItem();
Object m = month.getSelectedItem();
Object y = year.getSelectedItem();
String names = null;
String sql3 =
"select border_name from bazar where amount<>0 and bday='"
+ d.toString()
+ "' and bmonth='"
+ m.toString()
+ "' and byear='"
+ y.toString()
+ "' ";
pst = con.prepareStatement(sql3);
rs = pst.executeQuery();
if (rs.next()) {
names = rs.getString("border_name");
nameofbazari.setText(String.valueOf(names));
}
String sql =
"select items,amount from bazar where border_name='"
+ names
+ "' and amount<>0 and bday='"
+ d.toString()
+ "' and bmonth='"
+ m.toString()
+ "' and byear='"
+ y.toString()
+ "' ";
String sql2 =
"select sum(amount) from bazar where border_name='"
+ names
+ "' and amount<>0 and bday='"
+ d.toString()
+ "' and bmonth='"
+ m.toString()
+ "' and byear='"
+ y.toString()
+ "' ";
pst = con.prepareStatement(sql2);
rs = pst.executeQuery();
int amount;
if (rs.next()) {
amount = rs.getInt(1);
output.setText(String.valueOf(amount));
}
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
bazarlist.setModel(DbUtils.resultSetToTableModel(rs));
pst.close();
rs.close();
con.close();
} catch (Exception e) {
JOptionPane.showMessageDialog(this, e);
}
} // GEN-LAST:event_jButton1ActionPerformed