public void setQuery(String q) {
cache = new Vector();
try {
ResultSet rs = statement.executeQuery(q);
ResultSetMetaData meta = rs.getMetaData();
colCount = meta.getColumnCount();
headers = new String[colCount];
for (int h = 1; h <= colCount; h++) {
headers[h - 1] = meta.getColumnName(h);
}
while (rs.next()) {
String[] record = new String[colCount];
for (int i = 0; i < colCount; i++) {
record[i] = rs.getString(i + 1);
}
cache.addElement(record);
} // while sonu
fireTableChanged(null);
} // try sonu
catch (Exception e) {
cache = new Vector();
e.printStackTrace();
}
} // setQuery sonu
private void fullAssociated() {
boolean associated = true;
String SQL =
"Select * "
+ "from XX_VMR_REFERENCEMATRIX "
+ "where M_product IS NULL AND XX_VMR_PO_LINEREFPROV_ID="
+ LineRefProv.get_ID();
try {
PreparedStatement pstmt = DB.prepareStatement(SQL, null);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
associated = false;
}
rs.close();
pstmt.close();
} catch (Exception a) {
log.log(Level.SEVERE, SQL, a);
}
if (associated == true) {
String SQL10 =
"UPDATE XX_VMR_PO_LineRefProv "
+ " SET XX_ReferenceIsAssociated='Y'"
+ " WHERE XX_VMR_PO_LineRefProv_ID="
+ LineRefProv.getXX_VMR_PO_LineRefProv_ID();
DB.executeUpdate(null, SQL10);
// LineRefProv.setXX_ReferenceIsAssociated(true);
// LineRefProv.save();
int dialog = Env.getCtx().getContextAsInt("#Dialog_Associate_Aux");
if (dialog == 1) {
ADialog.info(m_WindowNo, m_frame, "MustRefresh");
Env.getCtx().remove("#Dialog_Associate_Aux");
}
} else {
String SQL10 =
"UPDATE XX_VMR_PO_LineRefProv "
+ " SET XX_ReferenceIsAssociated='N'"
+ " WHERE XX_VMR_PO_LineRefProv_ID="
+ LineRefProv.getXX_VMR_PO_LineRefProv_ID();
DB.executeUpdate(null, SQL10);
// LineRefProv.setXX_ReferenceIsAssociated(false);
// LineRefProv.save();
}
}
public void setJenis() {
dataJenis = new ArrayList<Jenis>();
try {
String qry = "SELECT * from jenis";
ResultSet rs = stm.executeQuery(qry);
while (rs.next()) {
Jenis j = new Jenis();
j.setIdJenis(rs.getInt("id_jenis"));
j.setNamaJenis(rs.getString("nama_jenis"));
dataJenis.add(j);
}
} catch (SQLException SQLerr) {
SQLerr.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* verifica si hay productos asociados
*
* @param table table
*/
private boolean verify() {
String sql =
"SELECT XX_VMR_ReferenceMatrix_ID "
+ "FROM XX_VMR_ReferenceMatrix "
+ "WHERE XX_VMR_PO_LINEREFPROV_ID="
+ (Integer) LineRefProv.getXX_VMR_PO_LineRefProv_ID();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
rs = pstmt.executeQuery();
while (rs.next()) {
associatedReference_ID = rs.getInt("XX_VMR_ReferenceMatrix_ID");
rs.close();
pstmt.close();
return true;
}
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
} finally {
try {
rs.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
} // tableLoad
private boolean isInMatrix() {
String SQL =
"Select * "
+ "from XX_VMR_REFERENCEMATRIX "
+ "where XX_VMR_PO_LINEREFPROV_ID="
+ LineRefProv.get_ID();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(SQL, null);
rs = pstmt.executeQuery();
while (rs.next()) {
return true;
}
} catch (Exception a) {
log.log(Level.SEVERE, SQL, a);
} finally {
try {
rs.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
public void tableChanged(TableModelEvent tme) {
int baris = tme.getFirstRow();
int kolom = tme.getColumn();
TableModel model = (TableModel) tme.getSource();
int id = (Integer) model.getValueAt(baris, 0);
String query = "";
switch (kolom) {
case 1:
String nama = (String) model.getValueAt(baris, kolom);
query = "UPDATE produk SET nama_produk='" + nama + "' WHERE id_produk=" + id;
prosesEdit(query);
break;
case 2:
String jenis = (String) model.getValueAt(baris, kolom);
try {
query = "select * from jenis where nama_jenis='" + jenis + "'";
ResultSet rs = stm.executeQuery(query);
if (rs.next()) {
int idJenis = rs.getInt("id_jenis");
query = "UPDATE produk SET id_jenis=" + idJenis + " WHERE id_produk=" + id;
prosesEdit(query);
} else {
setDataTabel();
JOptionPane.showMessageDialog(null, "gagal,jenis tidak ada");
}
} catch (SQLException SQLerr) {
SQLerr.printStackTrace();
}
break;
case 3:
int stok = (Integer) model.getValueAt(baris, kolom);
query = "UPDATE `stok_produk` SET stok=" + stok + " WHERE id_produk=" + id;
prosesEdit(query);
break;
case 4:
int harga = (Integer) model.getValueAt(baris, kolom);
query = "UPDATE produk SET harga=" + harga + " WHERE id_produk=" + id;
prosesEdit(query);
break;
case 5:
String suplier = (String) model.getValueAt(baris, kolom);
try {
query = "SELECT * FROM suplier WHERE nama_suplier='" + suplier + "'";
ResultSet rs = stm.executeQuery(query);
if (rs.next()) {
int idSuplier = rs.getInt("id_suplier");
query = "UPDATE produk SET id_suplier=" + idSuplier + " WHERE id_produk=" + id;
prosesEdit(query);
} else {
setDataTabel();
JOptionPane.showMessageDialog(null, "gagal,suplier belum terdaftar");
}
} catch (SQLException SQLerr) {
SQLerr.printStackTrace();
}
break;
default:
break;
}
}
// ambil data dari database untuk tabel
public void setDataTabel() {
// combobox jenis
JComboBox cbJenis = new JComboBox();
cbJenis.setModel(new DefaultComboBoxModel(dataJenis.toArray()));
dataProduk = new ArrayList<Produk>();
try {
String qry =
"SELECT * FROM produk,suplier,jenis,stok_produk WHERE produk.id_jenis = jenis.id_jenis AND produk.id_suplier = suplier.id_suplier AND produk.id_produk=stok_produk.id_produk";
ResultSet rs = stm.executeQuery(qry);
while (rs.next()) {
Produk p = new Produk();
p.setIdProduk(rs.getInt("id_produk"));
p.setNamaProduk(rs.getString("nama_produk"));
p.setJenis(rs.getString("nama_jenis"));
p.setHarga(rs.getInt("harga"));
p.setStok(rs.getInt("stok"));
p.setNamaSuplier(rs.getString("nama_suplier"));
p.setComboJenis(cbJenis);
dataProduk.add(p);
}
} catch (Exception err) {
err.printStackTrace();
}
model = new TableModelProduk(dataProduk);
tabel.setModel(model);
tabel.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
class EditProdukListener implements TableModelListener {
public void tableChanged(TableModelEvent tme) {
int baris = tme.getFirstRow();
int kolom = tme.getColumn();
TableModel model = (TableModel) tme.getSource();
int id = (Integer) model.getValueAt(baris, 0);
String query = "";
switch (kolom) {
case 1:
String nama = (String) model.getValueAt(baris, kolom);
query = "UPDATE produk SET nama_produk='" + nama + "' WHERE id_produk=" + id;
prosesEdit(query);
break;
case 2:
String jenis = (String) model.getValueAt(baris, kolom);
try {
query = "select * from jenis where nama_jenis='" + jenis + "'";
ResultSet rs = stm.executeQuery(query);
if (rs.next()) {
int idJenis = rs.getInt("id_jenis");
query = "UPDATE produk SET id_jenis=" + idJenis + " WHERE id_produk=" + id;
prosesEdit(query);
} else {
setDataTabel();
JOptionPane.showMessageDialog(null, "gagal,jenis tidak ada");
}
} catch (SQLException SQLerr) {
SQLerr.printStackTrace();
}
break;
case 3:
int stok = (Integer) model.getValueAt(baris, kolom);
query = "UPDATE `stok_produk` SET stok=" + stok + " WHERE id_produk=" + id;
prosesEdit(query);
break;
case 4:
int harga = (Integer) model.getValueAt(baris, kolom);
query = "UPDATE produk SET harga=" + harga + " WHERE id_produk=" + id;
prosesEdit(query);
break;
case 5:
String suplier = (String) model.getValueAt(baris, kolom);
try {
query = "SELECT * FROM suplier WHERE nama_suplier='" + suplier + "'";
ResultSet rs = stm.executeQuery(query);
if (rs.next()) {
int idSuplier = rs.getInt("id_suplier");
query = "UPDATE produk SET id_suplier=" + idSuplier + " WHERE id_produk=" + id;
prosesEdit(query);
} else {
setDataTabel();
JOptionPane.showMessageDialog(null, "gagal,suplier belum terdaftar");
}
} catch (SQLException SQLerr) {
SQLerr.printStackTrace();
}
break;
default:
break;
}
}
private void prosesEdit(String query) {
try {
int hasil = stm.executeUpdate(query);
if (hasil == 1) {
setDataTabel();
JOptionPane.showMessageDialog(null, "edit berhasil");
} else {
JOptionPane.showMessageDialog(null, "gagal");
}
} catch (SQLException SQLerr) {
SQLerr.printStackTrace();
}
}
}
model.addTableModelListener(new EditProdukListener());
}
public void bookRoom() {
try {
int code = 0;
Connection con =
DriverManager.getConnection(
"jdbc:mysql://67.20.111.85:3306/jeehtove_caliking?relaxAutoCommit=false",
"jeehtove_ck",
"Z_^PBBZT+kcy");
Statement stmt_insert = con.createStatement();
Statement stmt_tableRange = con.createStatement();
Statement stmt_checkrooms = con.createStatement();
Statement stmt_checkrooms_2 = con.createStatement();
String insert = null;
int flag = 0;
String room_type;
if (standardRoom.isSelected()) {
room_type = "Standard";
code = 1;
} else if (familyRoom.isSelected()) {
room_type = "Family";
code = 2;
} else {
room_type = "Suite";
code = 3;
}
// Converting string from checkin and checkout dates so that they can access the correct table
String currentDate_checkin_getText = checkindateField.getText();
String currentDate_checkin_rest = currentDate_checkin_getText.substring(5);
String currentDate_checkin_replace = currentDate_checkin_rest.replace("-", "_");
String currentDate_checkin_year = currentDate_checkin.substring(0, 4);
String currentDate_checkin_final =
currentDate_checkin_year + "_" + currentDate_checkin_replace;
String currentDate_checkout_getText = checkoutdateField.getText();
String currentDate_checkout_rest = currentDate_checkout_getText.substring(5);
String currentDate_checkout_replace = currentDate_checkout_rest.replace("-", "_");
String currentDate_checkout_year = currentDate_checkout.substring(0, 4);
String currentDate_checkout_final =
currentDate_checkout_year + "_" + currentDate_checkout_replace;
// MySQL Statement to select tables from schema that are in between checkin and checkout range
String tableRange =
"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='jeehtove_caliking' AND TABLE_NAME BETWEEN 'rooms_"
+ currentDate_checkin_final
+ "' AND 'rooms_"
+ currentDate_checkout_final
+ "';";
ResultSet tb_Range = stmt_tableRange.executeQuery(tableRange);
System.out.println(tableRange);
List tb_range_list = new ArrayList();
while (tb_Range.next()) {
String tb_range_date = tb_Range.getString("table_name");
tb_range_list.add(tb_range_date);
// String current_table = tb_Range.getString("table_name");
// System.out.println(tb_range_list.add(tb_Range.getString("table_name")));
}
stmt_tableRange.close();
for (int x = 0; x < tb_range_list.size(); x++) {
String query_checkrooms =
"SELECT * FROM "
+ tb_range_list.get(x)
+ " WHERE type = '"
+ code
+ "' AND booked = '0' LIMIT 1;";
// System.out.println(query_checkrooms);
ResultSet checkrooms = stmt_checkrooms.executeQuery(query_checkrooms);
while (checkrooms.next()) {
String getroomnumber = checkrooms.getString("room");
insert =
"INSERT INTO `reservations` VALUES ('"
+ getroomnumber
+ "', '"
+ checkindateField.getText()
+ "', '"
+ checkoutdateField.getText()
+ "', '"
+ room_type
+ "', '"
+ nameField.getText()
+ "');";
String bookroom =
"UPDATE `"
+ tb_range_list.get(x)
+ "` SET booked = '1' WHERE room = '"
+ getroomnumber
+ "';";
// System.out.println(insert);
int cr = stmt_checkrooms_2.executeUpdate(bookroom);
flag = 1;
System.out.println("Room Booked!");
}
}
if (flag == 1) {
int rs = stmt_insert.executeUpdate(insert);
} else {
System.out.println("All " + room_type + " rooms are booked for the time requested.");
}
/*String query_checkrooms = "SELECT * FROM rooms_" + currentDate_checkin_final + " WHERE type = '" + code + "' AND booked = '0';";
System.out.println(query_checkrooms);
Statement stmt_checkrooms = con.createStatement();
ResultSet checkrooms = stmt_checkrooms.executeQuery(query_checkrooms);*/
/*if (checkrooms.next()){
String getroomnumber = checkrooms.getString("room");
String insert = "INSERT INTO `reservations` VALUES ('" + getroomnumber + "', '" + checkindateField.getText() + "', '" + checkoutdateField.getText() + "', '" + room_type + "', '" + nameField.getText() + "');";
String bookroom = "UPDATE `rooms_" + currentDate_checkin_final + "` SET booked = '1' WHERE room = '" + getroomnumber + "';";
//System.out.println(insert);
int rs=stmt_insert.executeUpdate(insert);
int cr=stmt_checkrooms.executeUpdate(bookroom);
con.commit();
System.out.println("Room Booked!");
}*/
// else System.out.println("All " + room_type + " rooms are booked for the time requested.");
System.out.println("Complete.");
} catch (Exception e) {
System.out.println(e);
}
}
public void setContent(String cat) {
cat = cat.trim();
selectAllCB.setVisible(false);
selectAllCB.setSelected(false);
deleteBut.setVisible(false);
restoreBut.setVisible(false);
refreshBut.setVisible(true);
Object columns[] = null;
int count = 0;
switch (cat) {
case "Inbox":
columns = new Object[] {"", "From", "Date", "Subject", "Content"};
count = Database.getCount("Inbox");
workingSet = db.getData("SELECT * FROM messages WHERE tag='inbox' ORDER BY msg_id desc");
;
break;
case "SentMail":
columns = new Object[] {"", "To", "Date", "Subject", "Content"};
count = Database.getCount("Sentmail");
workingSet = db.getData("SELECT * FROM messages WHERE tag='sentmail' ORDER BY msg_id desc");
break;
case "Draft":
columns = new Object[] {"", "To", "Date", "Subject", "Content"};
count = Database.getCount("Draft");
workingSet = db.getData("SELECT * FROM messages WHERE tag='draft' ORDER BY msg_id desc");
break;
case "Outbox":
columns = new Object[] {"", "To", "Date", "Subject", "Content"};
count = Database.getCount("Outbox");
workingSet = db.getData("SELECT * FROM messages WHERE tag='outbox' ORDER BY msg_id desc");
break;
case "Trash":
// restoreBut.setVisible(true);
columns = new Object[] {"", "To/From", "Date", "Subject", "Content"};
count = Database.getCount("Trash");
workingSet =
db.getData(
"SELECT * FROM messages,trash WHERE messages.tag='trash' and messages.msg_id=trash.msg_id ORDER BY deleted_at desc");
break;
default:
System.out.println("in default case");
}
if (count > 0) {
selectAllCB.setVisible(true);
rows = new Object[count][];
msgID = new int[count];
try {
workingSet.beforeFirst();
for (int i = 0; i < count && workingSet.next(); i++) {
msgID[i] = workingSet.getInt(1);
rows[i] =
new Object[] {
false,
workingSet.getString(2),
workingSet.getDate(3),
workingSet.getString(4),
workingSet.getString(5)
};
}
} catch (SQLException sqlExc) {
JOptionPane.showMessageDialog(null, sqlExc, "EXCEPTION", JOptionPane.ERROR_MESSAGE);
sqlExc.printStackTrace();
}
tableModel = new MyDefaultTableModel(rows, columns);
table = new JTable(tableModel);
table.getSelectionModel().addListSelectionListener(this);
table.addMouseListener(this);
table.getTableHeader().setOpaque(true);
table.getTableHeader().setReorderingAllowed(false);
// table.getTableHeader().setBackground(Color.blue);
table.getTableHeader().setForeground(Color.blue);
// table.setRowSelectionAllowed(false);
// table.setColumnSelectionAllowed(false);
table.setFont(new Font(Font.SANS_SERIF, Font.PLAIN, 14));
table.setRowHeight(20);
table.setFillsViewportHeight(true);
TableColumn column = null;
for (int i = 0; i < 5; i++) {
column = table.getColumnModel().getColumn(i);
if (i == 0) {
column.setPreferredWidth(6);
} else if (i == 3) {
column.setPreferredWidth(250);
} else if (i == 4) {
column.setPreferredWidth(450);
} else {
column.setPreferredWidth(40);
}
}
table.setAutoResizeMode(JTable.AUTO_RESIZE_SUBSEQUENT_COLUMNS);
remove(contentPan);
contentPan = new JScrollPane(table);
contentPan.setBackground(Color.orange);
contentPan.setOpaque(true);
contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
add(contentPan, "Center");
Home.home.homeFrame.setVisible(true);
} else {
JPanel centPan = new JPanel(new GridBagLayout());
centPan.setBackground(new Color(52, 86, 70));
JLabel label = new JLabel("No Messages In This Category");
label.setFont(new Font(Font.SANS_SERIF, Font.BOLD, 22));
label.setForeground(Color.orange);
centPan.add(label);
remove(contentPan);
contentPan = new JScrollPane(centPan);
contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
add(contentPan, "Center");
contentPan.repaint();
}
}
/**
* _more_
*
* @return _more_
* @throws Exception _more_
*/
private boolean initConnection() throws Exception {
if (getConnection() == null) {
return false;
}
// jdbc:postgresql://eol-rt-data.guest.ucar.edu/real-time
// evaluate("CREATE RULE update AS ON UPDATE TO global_attributes DO NOTIFY current;");
Statement stmt;
ResultSet results;
SqlUtil.Iterator iter;
EolDbTrackInfo trackInfo = new EolDbTrackInfo(this, "TRACK");
Hashtable cats = new Hashtable();
try {
stmt = select("*", TABLE_CATEGORIES);
iter = SqlUtil.getIterator(stmt);
while ((results = iter.getNext()) != null) {
cats.put(results.getString(COL_VARIABLE), results.getString(COL_CATEGORY));
}
} catch (Exception exc) {
// exc.printStackTrace();
}
missingMap = new Hashtable();
stmt = select("*", TABLE_GLOBALS);
globals = new Hashtable();
boolean gotCoords = false;
description = "<b>Globals</b><br>";
iter = SqlUtil.getIterator(stmt);
while ((results = iter.getNext()) != null) {
String globalName = results.getString(1).trim();
String globalValue = results.getString(2).trim();
globals.put(globalName, globalValue);
description =
description
+ "<tr valign=\"top\"><td>"
+ globalName
+ "</td><td>"
+ globalValue
+ "</td></tr>";
// System.err.println(globalName +"=" + globalValue);
if (globalName.equals(GLOBAL_STARTTIME)) {
startTime = new DateTime(DateUtil.parse(globalValue));
} else if (globalName.equals(GLOBAL_ENDTIME)) {
endTime = new DateTime(DateUtil.parse(globalValue));
} else if (globalName.equals(GLOBAL_COORDINATES)) {
List toks = StringUtil.split(globalValue, " ", true, true);
if (toks.size() != 4) {
throw new BadDataException("Incorrect coordinates value in database:" + globalValue);
}
gotCoords = true;
System.err.println("coords:" + toks);
trackInfo.setCoordinateVars(
(String) toks.get(0), (String) toks.get(1), (String) toks.get(2), (String) toks.get(3));
trackInfo.setCoordinateVars("GGLON", "GGLAT", "GGALT", "datetime");
}
}
description = description + "</table>";
if (!gotCoords) {
throw new BadDataException("No coordinates found in database");
}
this.name = (String) globals.get(GLOBAL_PROJECTNAME);
String flight = (String) globals.get(GLOBAL_FLIGHTNUMBER);
if ((this.name != null) && (flight != null) && (flight.length() != 0)) {
this.name += " - " + flight;
}
stmt = select("*", TABLE_VARIABLE_LIST);
iter = SqlUtil.getIterator(stmt);
while ((results = iter.getNext()) != null) {
String name = results.getString(COL_NAME).trim();
String desc = results.getString(COL_LONG_NAME).trim();
Unit unit = DataUtil.parseUnit(results.getString(COL_UNITS).trim());
String cat = (String) cats.get(name);
double missing = results.getDouble(COL_MISSING_VALUE);
VarInfo variable = new VarInfo(name, desc, cat, unit, missing);
trackInfo.addVariable(variable);
}
addTrackInfo(trackInfo);
return true;
}
public void actionPerformed(ActionEvent e) {
String a, b, c, f, g, h, j, k, l, m, n, o, p, tol = "";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:db2");
stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
} catch (Exception error) {
System.out.println(error);
}
if (e.getSource() == student) {
name = "Student";
cl.show(pnc, "cstu");
try {
rs = stm.executeQuery("Select * from Student");
rs.next();
t1.setText(rs.getString(1));
t2.setText(rs.getString(2));
t3.setText(rs.getString(3));
t4.setText(rs.getString(4));
t5.setText(rs.getString(5));
t6.setText(rs.getString(6));
} catch (Exception error) {
System.out.println(error);
}
}
if (e.getSource() == teacher) {
name = "Teacher";
cl.show(pnc, "ctea");
try {
rs = stm.executeQuery("Select * from Teacher");
rs.next();
t11.setText(rs.getString(1));
t12.setText(rs.getString(2));
t13.setText(rs.getString(3));
t14.setText(rs.getString(4));
} catch (Exception error) {
System.out.println(error);
}
}
if (e.getSource() == course) {
name = "Course";
cl.show(pnc, "ccou");
try {
rs = stm.executeQuery("Select * from Course");
rs.next();
t7.setText(rs.getString(1));
t8.setText(rs.getString(2));
t9.setText(rs.getString(3));
t0.setText(rs.getString(4));
} catch (Exception error) {
System.out.println(error);
}
}
if (e.getSource() == result) {
name = "Result";
cl.show(pnc, "cres");
try {
rs = stm.executeQuery("Select * from Result");
rs.next();
t15.setText(rs.getString(1));
t16.setText(rs.getString(2));
t17.setText(rs.getString(3));
t18.setText(rs.getString(4));
t19.setText(rs.getString(5));
t20.setText(rs.getString(6));
t21.setText(rs.getString(7));
t22.setText(rs.getString(8));
t23.setText(rs.getString(9));
t24.setText(rs.getString(10));
t25.setText(rs.getString(11));
t26.setText(rs.getString(12));
t27.setText(rs.getString(13));
} catch (Exception error) {
System.out.println(error);
}
}
if (e.getSource() == b6) {
Vector cols = new Vector();
Vector rows = new Vector();
if (z == 1) {
String sql = "Select * from " + co;
try {
rs = stm.executeQuery(sql);
meta = rs.getMetaData();
for (int i = 1; i <= meta.getColumnCount(); i++) cols.addElement(meta.getColumnName(i));
while (rs.next()) {
Vector currow = new Vector();
for (int i = 1; i <= meta.getColumnCount(); i++) currow.addElement(rs.getString(i));
rows.addElement(currow);
}
} catch (Exception ex) {
System.out.print(ex);
}
tb = new JTable(rows, cols);
js = new JScrollPane(tb);
pne2.remove(js);
pne2.add(blk, "bb");
cl2.show(pne2, "bb");
pne2.add(js, "jjs");
cl2.show(pne2, "jjs");
pne2.remove(blk);
z = 0;
}
}
if (e.getSource() == b1) // **** ADD BUTTON ****//
{
if (name == "Student") {
a = t1.getText();
b = t2.getText();
c = t3.getText();
f = t4.getText();
g = t5.getText();
h = t6.getText();
tol =
"Insert into Student values ('"
+ a
+ "','"
+ b
+ "','"
+ c
+ "','"
+ f
+ "','"
+ g
+ "','"
+ h
+ "')";
}
if (name == "Teacher") {
a = t11.getText();
b = t12.getText();
c = t13.getText();
f = t14.getText();
tol = "Insert into Teacher values ('" + a + "','" + b + "','" + c + "','" + f + "')";
}
if (name == "Course") {
a = t7.getText();
b = t8.getText();
c = t9.getText();
f = t0.getText();
tol = "Insert into Course values ('" + a + "','" + b + "','" + c + "','" + f + "')";
}
if (name == "Result") {
a = t15.getText();
b = t16.getText();
c = t17.getText();
f = t18.getText();
g = t19.getText();
h = t20.getText();
j = t21.getText();
k = t22.getText();
l = t23.getText();
m = t24.getText();
n = t25.getText();
o = t26.getText();
p = t27.getText();
tol =
"Insert into Result values ('"
+ a
+ "','"
+ b
+ "','"
+ c
+ "','"
+ f
+ "','"
+ g
+ "','"
+ h
+ "','"
+ j
+ "','"
+ k
+ "','"
+ l
+ "','"
+ m
+ "','"
+ n
+ "','"
+ o
+ "','"
+ p
+ "')";
}
try {
stm.executeUpdate(tol);
} catch (Exception error) {
System.out.println(error);
}
}
if (e.getSource() == b2) // **** DELETE BUTTON ****//
{
if (name == "Student") {
b = t2.getText();
tol = "Delete from Student where Id = '" + b + "'";
}
if (name == "Teacher") {
b = t12.getText();
tol = "Delete from Teacher where Id = '" + b + "'";
}
if (name == "Course") {
b = t8.getText();
tol = "Delete from Course where Id = '" + b + "'";
}
if (name == "Result") {
b = t16.getText();
tol = "Delete from Result where Code = '" + b + "'";
}
try {
stm.executeUpdate(tol);
} catch (Exception error) {
System.out.println(error);
}
}
if (e.getSource() == b3) // **** UPDATE BUTTON ****//
{
if (name == "Student") {
a = t1.getText();
b = t2.getText();
c = t3.getText();
f = t4.getText();
g = t5.getText();
h = t6.getText();
tol =
"Update Student set Name = '"
+ a
+ "', ID = '"
+ b
+ "', Dept = '"
+ c
+ "', CGPA = '"
+ f
+ "', Address = '"
+ g
+ "', Cell = '"
+ h
+ "' where ID = '"
+ b
+ "'";
}
if (name == "Teacher") {
a = t11.getText();
b = t12.getText();
c = t13.getText();
f = t14.getText();
tol =
"Update Teacher set Name = '"
+ a
+ "', ID = '"
+ b
+ "', Dept = '"
+ c
+ "', Course = '"
+ f
+ "' where ID = '"
+ b
+ "'";
}
if (name == "Course") {
a = t7.getText();
b = t8.getText();
c = t9.getText();
f = t0.getText();
tol =
"Update Course set Name = '"
+ a
+ "', Code = '"
+ b
+ "', Credit = '"
+ c
+ "', Prerecusite = '"
+ f
+ "' where Code = '"
+ b
+ "'";
}
if (name == "Result") {
a = t15.getText();
b = t16.getText();
c = t17.getText();
f = t18.getText();
g = t19.getText();
h = t20.getText();
j = t21.getText();
k = t22.getText();
l = t23.getText();
m = t24.getText();
n = t25.getText();
o = t26.getText();
p = t27.getText();
tol =
"Update Result set Course = '"
+ a
+ "', Code = '"
+ b
+ "', Credit = '"
+ c
+ "', A = '"
+ f
+ "', B+ = '"
+ g
+ "', B = '"
+ h
+ "', C+ = '"
+ j
+ "', C = '"
+ k
+ "', D+ = '"
+ l
+ "', D = '"
+ m
+ "', F = '"
+ n
+ "', I = '"
+ o
+ "', W = '"
+ p
+ "' where Code = '"
+ b
+ "'";
// JOptionPane.showMessageDialog(null,tol,null,JOptionPane.PLAIN_MESSAGE);
// tol = "Update Result set Course = '"+a+"', Code = '"+b+"', Credit = '"+c+"' where Code =
// '"+b+"'";
}
try {
stm.executeUpdate(tol);
} catch (Exception error) {
System.out.println(error);
}
}
if (e.getSource() == b4) // **** NEXT BUTTON ****//
{
if (name == "Student") {
try {
if (rs.next()) {
t1.setText(rs.getString(1));
t2.setText(rs.getString(2));
t3.setText(rs.getString(3));
t4.setText(rs.getString(4));
t5.setText(rs.getString(5));
t6.setText(rs.getString(6));
}
} catch (Exception error) {
System.out.println(error);
}
}
if (name == "Teacher") {
try {
if (rs.next()) {
t11.setText(rs.getString(1));
t12.setText(rs.getString(2));
t13.setText(rs.getString(3));
t14.setText(rs.getString(4));
}
} catch (Exception error) {
System.out.println(error);
}
}
if (name == "Course") {
try {
if (rs.next()) {
t7.setText(rs.getString(1));
t8.setText(rs.getString(2));
t9.setText(rs.getString(3));
t0.setText(rs.getString(4));
}
} catch (Exception error) {
System.out.println(error);
}
}
if (name == "Result") {
try {
if (rs.next()) {
t15.setText(rs.getString(1));
t16.setText(rs.getString(2));
t17.setText(rs.getString(3));
t18.setText(rs.getString(4));
t19.setText(rs.getString(5));
t20.setText(rs.getString(6));
t21.setText(rs.getString(7));
t22.setText(rs.getString(8));
t23.setText(rs.getString(9));
t24.setText(rs.getString(10));
t25.setText(rs.getString(11));
t26.setText(rs.getString(12));
t27.setText(rs.getString(13));
}
} catch (Exception error) {
System.out.println(error);
}
}
}
if (e.getSource() == b5) // **** PREVIOUS BUTTON ****//
{
if (name == "Student") {
try {
if (rs.previous()) {
t1.setText(rs.getString(1));
t2.setText(rs.getString(2));
t3.setText(rs.getString(3));
t4.setText(rs.getString(4));
t5.setText(rs.getString(5));
t6.setText(rs.getString(6));
}
} catch (Exception error) {
System.out.println(error);
}
}
if (name == "Teacher") {
try {
if (rs.previous()) {
t11.setText(rs.getString(1));
t12.setText(rs.getString(2));
t13.setText(rs.getString(3));
t14.setText(rs.getString(4));
}
} catch (Exception error) {
System.out.println(error);
}
}
if (name == "Course") {
try {
if (rs.previous()) {
t7.setText(rs.getString(1));
t8.setText(rs.getString(2));
t9.setText(rs.getString(3));
t0.setText(rs.getString(4));
}
} catch (Exception error) {
System.out.println(error);
}
}
if (name == "Result") {
try {
if (rs.previous()) {
t15.setText(rs.getString(1));
t16.setText(rs.getString(2));
t17.setText(rs.getString(3));
t18.setText(rs.getString(4));
t19.setText(rs.getString(5));
t20.setText(rs.getString(6));
t21.setText(rs.getString(7));
t22.setText(rs.getString(8));
t23.setText(rs.getString(9));
t24.setText(rs.getString(10));
t25.setText(rs.getString(11));
t26.setText(rs.getString(12));
t27.setText(rs.getString(13));
}
} catch (Exception error) {
System.out.println(error);
}
}
}
}
public Design() throws Exception {
super.setBackground(Color.BLACK);
this.setTitle("");
con = getContentPane();
con.setLayout(null);
dim = tk.getDefaultToolkit().getScreenSize();
this.setTitle("Customer Peer Login");
l1 = new JLabel(new ImageIcon("plain.jpg"));
l1.setBounds(0, 0, 400, 400);
con.add(l1);
l1.setBorder(BorderFactory.createEtchedBorder(5, Color.black, Color.black));
title = new JLabel("CUSTOMER PEER LOGIN ");
title.setFont(new Font("Bookman Old Style", Font.ROMAN_BASELINE, 20));
title.setForeground(Color.red);
title.setBounds(80, 30, 300, 30);
l1.add(title);
l4 = new JLabel("CMACHINE NAME");
l4.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l4.setForeground(Color.BLUE);
l4.setBounds(70, 100, 160, 20);
// l4.setBorder(BorderFactory.createEtchedBorder(5,Color.green,Color.green));
l1.add(l4);
jtf2 = new JTextField();
jtf2.setBounds(250, 100, 100, 20);
jtf2.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(jtf2);
l2 = new JLabel("CUSER LOGIN");
l2.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l2.setForeground(Color.blue);
l2.setBounds(70, 150, 120, 20);
l1.add(l2);
jtf1 = new JTextField();
jtf1.setBounds(250, 150, 100, 20);
jtf1.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(jtf1);
l3 = new JLabel("CPASSWORD");
l3.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l3.setForeground(Color.blue);
l3.setBounds(70, 200, 120, 20);
l1.add(l3);
jptf1 = new JPasswordField();
jptf1.setBounds(250, 200, 100, 20);
jptf1.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(jptf1);
JLabel l4 = new JLabel("DAgent");
l4.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l4.setForeground(Color.blue);
l4.setBounds(70, 250, 120, 20);
l1.add(l4);
box = new JComboBox();
box.setBounds(250, 250, 100, 20);
box.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(box);
b2 = new JButton("Register");
b2.setBounds(50, 300, 100, 20);
l1.add(b2);
b2.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));
b3 = new JButton("Login");
b3.setBounds(150, 300, 100, 20);
b3.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));
l1.add(b3);
b1 = new JButton("Cancel");
b1.setBounds(250, 300, 100, 20);
b1.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));
l1.add(b1);
b1.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent we) {
dispose();
}
});
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:agent");
} catch (Exception exp) {
}
try {
Statement satem = conn.createStatement();
ResultSet rsatem = satem.executeQuery("select * from Dagent");
while (rsatem.next()) {
String namem = rsatem.getString("uname");
box.addItem(namem);
}
} catch (Exception expo1) {
}
b2.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent we) {
String username = jtf1.getText().trim();
String password = jptf1.getText().trim();
String mechine = jtf2.getText().trim();
String dname = box.getSelectedItem().toString();
int porte = 0;
try {
Statement sate = conn.createStatement();
ResultSet rsate =
sate.executeQuery("select * from Dagent where uname='" + dname + "'");
if (rsate.next()) {
servermachine = rsate.getString("umechine");
porte = rsate.getInt("ulistport");
System.out.println(servermachine);
}
System.out.println(servermachine);
} catch (Exception exp) {
exp.printStackTrace();
}
try {
packet p = new packet();
p.setaction("Creg");
p.setCuser(username);
p.setCpass(password);
p.setCmname(mechine);
p.setCDpeer(dname);
Socket soc = new Socket(servermachine, porte);
ObjectOutputStream out = new ObjectOutputStream(soc.getOutputStream());
out.writeObject(p);
ObjectInputStream in = new ObjectInputStream(soc.getInputStream());
packet rpac = (packet) in.readObject();
if (rpac.getaction().equals("ok")) {
JOptionPane.showMessageDialog(null, "Sucessfully Registered");
jtf2.setText("");
jtf1.setText("");
jptf1.setText("");
} else {
JOptionPane.showMessageDialog(null, "Already Registered");
jtf2.setText("");
jtf1.setText("");
jptf1.setText("");
}
} catch (Exception exp) {
}
}
});
b3.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent we) {
String username = jtf1.getText().trim();
String password = jptf1.getText().trim();
String mechine = jtf2.getText().trim();
String Dname = box.getSelectedItem().toString();
int porte = 0;
try {
Statement sate = conn.createStatement();
ResultSet rsate =
sate.executeQuery("select * from Dagent where uname='" + Dname + "'");
if (rsate.next()) {
servermachine = rsate.getString("umechine");
porte = rsate.getInt("ulistport");
System.out.println(servermachine);
}
System.out.println(servermachine);
} catch (Exception exp) {
}
try {
packet p1 = new packet();
p1.setaction("clogin");
p1.setCuser(username);
p1.setCpass(password);
p1.setCmname(mechine);
p1.setCDpeer(Dname);
Socket soc1 = new Socket(servermachine, porte);
ObjectOutputStream out1 = new ObjectOutputStream(soc1.getOutputStream());
out1.writeObject(p1);
ObjectInputStream in1 = new ObjectInputStream(soc1.getInputStream());
packet rpac1 = (packet) in1.readObject();
if (rpac1.getaction().equals("ok")) {
int port1 = 0;
try {
int portm = rpac1.getCport();
System.out.println("XXXXXXX" + portm);
// JOptionPane.showMessageDialog(null,"Sucessfully Started");
new Listen(portm);
new process(username, portm);
dispose();
} catch (Exception exp) {
}
} else {
JOptionPane.showMessageDialog(
null, "Enter valid username and password", "Server reply", 2);
jtf1.setText("");
jtf2.setText("");
jptf1.setText("");
}
} catch (Exception exp) {
}
}
});
setSize(400, 400);
show();
setLocation(150, 100);
setResizable(false);
}
/** Process Button Pressed - Process Matching */
private void cmd_newProduct() {
// Selecciono el departamento
int depart = 0;
String SQL =
"Select XX_VMR_DEPARTMENT_ID "
+ "from XX_VMR_VENDORPRODREF "
+ "where XX_VMR_VENDORPRODREF_ID="
+ LineRefProv.getXX_VMR_VendorProdRef_ID();
try {
PreparedStatement pstmt = DB.prepareStatement(SQL, null);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
depart = rs.getInt("XX_VMR_DEPARTMENT_ID");
}
rs.close();
pstmt.close();
} catch (Exception a) {
log.log(Level.SEVERE, SQL, a);
}
MVMRVendorProdRef vendorProdRef =
new MVMRVendorProdRef(Env.getCtx(), LineRefProv.getXX_VMR_VendorProdRef_ID(), null);
if (vendorProdRef.getXX_VMR_ProductClass_ID() > 0
&& vendorProdRef.getXX_VMR_TypeLabel_ID() > 0) {
MOrder order = new MOrder(Env.getCtx(), LineRefProv.getC_Order_ID(), null);
// Selecciono el departamento
X_XX_VMR_Department dept =
new X_XX_VMR_Department(Env.getCtx(), vendorProdRef.getXX_VMR_Department_ID(), null);
int category = dept.getXX_VMR_Category_ID();
// Selecciono la línea
X_XX_VMR_Line line = new X_XX_VMR_Line(Env.getCtx(), vendorProdRef.getXX_VMR_Line_ID(), null);
int typeInventory = line.getXX_VMR_TypeInventory_ID();
MProduct newProduct =
new MProduct(
Env.getCtx(),
vendorProdRef.getXX_VMR_Department_ID(),
vendorProdRef.getXX_VMR_Line_ID(),
vendorProdRef.getXX_VMR_Section_ID(),
vendorProdRef.get_ID(),
vendorProdRef.getC_TaxCategory_ID(),
vendorProdRef.getXX_VME_ConceptValue_ID(),
typeInventory,
null);
// Se buscará si por la referencia para producto ya existe para asignarle el Tipo de
// Exhibición
String sql =
"select * from M_Product where XX_VMR_DEPARTMENT_ID = "
+ vendorProdRef.getXX_VMR_Department_ID()
+ " and "
+ "XX_VMR_LINE_ID = "
+ vendorProdRef.getXX_VMR_Line_ID()
+ " and XX_VMR_SECTION_ID = "
+ vendorProdRef.getXX_VMR_Section_ID()
+ " and "
+ "XX_VMR_VendorProdRef_id = "
+ vendorProdRef.getXX_VMR_VendorProdRef_ID()
+ " order by M_Product_ID desc";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
ResultSet rs = null;
try {
rs = pstmt.executeQuery();
if (rs.next())
newProduct.setXX_VMR_TypeExhibition_ID(rs.getInt("XX_VMR_TypeExhibition_ID"));
} catch (SQLException e) {
e.printStackTrace();
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
if (vendorProdRef.getXX_VMR_Section_ID() > 0) {
X_XX_VMR_Section section =
new X_XX_VMR_Section(Env.getCtx(), vendorProdRef.getXX_VMR_Section_ID(), null);
newProduct.setName(section.getName());
} else {
newProduct.setName(vendorProdRef.getName());
}
newProduct.setXX_VMR_Category_ID(category);
newProduct.setXX_VMR_LongCharacteristic_ID(vendorProdRef.getXX_VMR_LongCharacteristic_ID());
newProduct.setXX_VMR_Brand_ID(vendorProdRef.getXX_VMR_Brand_ID());
newProduct.setXX_VMR_UnitConversion_ID(vendorProdRef.getXX_VMR_UnitConversion_ID());
newProduct.setXX_PiecesBySale_ID(vendorProdRef.getXX_PiecesBySale_ID());
newProduct.setXX_VMR_UnitPurchase_ID(vendorProdRef.getXX_VMR_UnitPurchase_ID());
newProduct.setXX_SaleUnit_ID(vendorProdRef.getXX_SaleUnit_ID());
newProduct.setC_Country_ID(order.getC_Country_ID());
newProduct.setIsActive(true);
newProduct.setC_BPartner_ID(vendorProdRef.getC_BPartner_ID());
newProduct.setXX_VMR_TypeLabel_ID(vendorProdRef.getXX_VMR_TypeLabel_ID());
newProduct.setXX_VMR_ProductClass_ID(vendorProdRef.getXX_VMR_ProductClass_ID());
newProduct.setM_AttributeSet_ID(Env.getCtx().getContextAsInt("#XX_L_P_ATTRIBUTESETST_ID"));
newProduct.setProductType(X_Ref_M_Product_ProductType.ITEM.getValue());
newProduct.save();
} else {
// Creo variables de sesion para atraparlas en la ventana producto
Env.getCtx().setContext("#Depart_Aux", depart);
Env.getCtx().setContext("#Section_Aux", LineRefProv.getXX_VMR_Section_ID());
Env.getCtx().setContext("#Line_Aux", LineRefProv.getXX_VMR_Line_ID());
Env.getCtx().setContext("#VendorRef_Aux", LineRefProv.getXX_VMR_VendorProdRef_ID());
Env.getCtx().setContext("#FromProcess_Aux", "Y");
AWindow window_product = new AWindow();
Query query = Query.getNoRecordQuery("M_Product", true);
window_product.initWindow(140, query);
AEnv.showCenterScreen(window_product);
// Obtenemos el GridController para setear la variable m_changed=true
JRootPane jRootPane = ((JRootPane) window_product.getComponent(0));
JLayeredPane jLayeredPane = (JLayeredPane) jRootPane.getComponent(1);
JPanel jPanel = (JPanel) jLayeredPane.getComponent(0);
APanel aPanel = (APanel) jPanel.getComponent(0);
VTabbedPane vTabbedPane = (VTabbedPane) aPanel.getComponent(0);
GridController gridController = (GridController) vTabbedPane.getComponent(0);
GridTable mTable = gridController.getMTab().getTableModel();
mTable.setChanged(true);
MProduct.loadLineRefProv(LineRefProv, Env.getCtx());
// Borro las variables de sesion creadas
Env.getCtx().remove("#Depart_Aux");
Env.getCtx().remove("#FromProcess_Aux");
Env.getCtx().remove("#Line_Aux");
Env.getCtx().remove("#Section_Aux");
Env.getCtx().remove("#VendorRef_Aux");
}
} // cmd_newProduct
/** Dispose */
public void dispose() {
if (m_frame != null) m_frame.dispose();
m_frame = null;
MOrder order = new MOrder(Env.getCtx(), LineRefProv.getC_Order_ID(), null);
String oS = order.getXX_OrderStatus();
String compS = order.getDocStatus();
if (!oS.equals("AN") && !compS.equals("CO")) {
if (isInMatrix()) {
fullAssociated();
} else {
String SQL10 =
"UPDATE XX_VMR_PO_LineRefProv "
+ " SET XX_ReferenceIsAssociated='N'"
+ " WHERE XX_VMR_PO_LineRefProv_ID="
+ LineRefProv.getXX_VMR_PO_LineRefProv_ID();
DB.executeUpdate(null, SQL10);
// LineRefProv.setXX_ReferenceIsAssociated(false);
// LineRefProv.save();
}
}
// pelle
String SQL =
("SELECT A.XX_REFERENCEISASSOCIATED AS ASOCIATE "
+ "FROM XX_VMR_PO_LINEREFPROV A , C_ORDER B "
+ "WHERE A.C_ORDER_ID = B.C_ORDER_ID "
+ "AND A.C_ORDER_ID = '"
+ LineRefProv.getC_Order_ID()
+ "' ");
try {
PreparedStatement pstmt = DB.prepareStatement(SQL, null);
ResultSet rs = pstmt.executeQuery();
BigDecimal cifTotal = new BigDecimal(0);
Boolean check = true;
Integer maritimo = 0;
Integer aereo = 0;
Integer terrestre = 0;
BigDecimal montoBs = new BigDecimal(0);
BigDecimal rate = new BigDecimal(0);
BigDecimal Seguro = new BigDecimal(0);
BigDecimal perfleteInt = new BigDecimal(0);
BigDecimal fleteInternac = new BigDecimal(0);
Integer productid = 0;
Integer poline = 0;
BigDecimal costoitem = new BigDecimal(0);
BigDecimal porcentitem = new BigDecimal(0);
BigDecimal cifuni = new BigDecimal(0);
BigDecimal percentarancel = new BigDecimal(0);
BigDecimal arancelitem = new BigDecimal(0);
BigDecimal rateseniat = new BigDecimal(0);
BigDecimal montoseniat = new BigDecimal(0);
BigDecimal ratetesoreria = new BigDecimal(0);
BigDecimal montotesoreria = new BigDecimal(0);
BigDecimal tasaaduana = new BigDecimal(0);
BigDecimal part1 = new BigDecimal(0);
BigDecimal part2 = new BigDecimal(0);
BigDecimal iva = new BigDecimal(0);
BigDecimal ivaactual = new BigDecimal(0);
BigDecimal part = new BigDecimal(0);
BigDecimal montesorerianac = new BigDecimal(0);
while (rs.next()) {
if (rs.getString("ASOCIATE").trim().equalsIgnoreCase("N")) {
check = false;
}
}
rs.close();
pstmt.close();
if (check == true) {
String SQL2 =
("SELECT (A.TOTALLINES * B.MULTIPLYRATE) + A.XX_INTNACESTMEDAMOUNT + A.XX_ESTEEMEDINSURANCEAMOUNT AS CIFTOTAL "
+ "FROM C_ORDER A, C_CONVERSION_RATE B "
+ "WHERE B.C_CONVERSION_RATE_ID = A.XX_CONVERSIONRATE_ID "
+ "AND A.C_ORDER_ID = '"
+ LineRefProv.getC_Order_ID()
+ "' "
+ "AND A.AD_Client_ID IN(0,"
+ Env.getCtx().getAD_Client_ID()
+ ") "
+ "AND B.AD_Client_ID IN(0,"
+ Env.getCtx().getAD_Client_ID()
+ ")");
PreparedStatement pstmt2 = DB.prepareStatement(SQL2, null);
ResultSet rs2 = pstmt2.executeQuery();
if (rs2.next()) {
// Calculo del CIF Total
cifTotal = rs2.getBigDecimal("CIFTOTAL");
}
rs2.close();
pstmt2.close();
// Busco la via de despacho de la O/C
String SQL3 =
("SELECT XX_L_DISPATCHROUTE AS AEREO, XX_L_DISPATCHROUTEMAR AS MARITIMO, XX_L_DISPATCHROUTETER AS TERRESTRE "
+ "FROM XX_VSI_KEYNAMEINFO ");
PreparedStatement pstmt3 = DB.prepareStatement(SQL3, null);
ResultSet rs3 = pstmt3.executeQuery();
if (rs3.next()) {
maritimo = rs3.getInt("MARITIMO");
terrestre = rs3.getInt("TERRESTRE");
aereo = rs3.getInt("AEREO");
}
rs3.close();
pstmt3.close();
String SQL4 =
("SELECT (A.TOTALLINES * B.MULTIPLYRATE) AS MONTO "
+ "FROM C_ORDER A, C_CONVERSION_RATE B "
+ "WHERE B.C_CONVERSION_RATE_ID = A.XX_CONVERSIONRATE_ID "
+ "AND A.C_ORDER_ID = '"
+ LineRefProv.getC_Order_ID()
+ "' ");
PreparedStatement pstmt4 = DB.prepareStatement(SQL4, null);
ResultSet rs4 = pstmt4.executeQuery();
if (rs4.next()) {
// Monto de la O/C en Bs
montoBs = rs4.getBigDecimal("MONTO");
}
rs4.close();
pstmt4.close();
if (order.getXX_VLO_DispatchRoute_ID() == maritimo) {
String SQL5 =
("SELECT XX_RATE AS RATE "
+ "FROM XX_VLO_DispatchRoute "
+ "WHERE XX_VLO_DispatchRoute_ID = '"
+ maritimo
+ "' ");
PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
ResultSet rs5 = pstmt5.executeQuery();
if (rs5.next()) {
rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100));
// (new BigDecimal(100), 2, RoundingMode.HALF_UP);
// Calculo del Seguro con Via Maritimo
Seguro = montoBs.multiply(rate);
}
rs5.close();
pstmt5.close();
} else if (order.getXX_VLO_DispatchRoute_ID() == aereo) {
String SQL5 =
("SELECT XX_RATE AS RATE "
+ "FROM XX_VLO_DispatchRoute "
+ "WHERE XX_VLO_DispatchRoute_ID = '"
+ aereo
+ "' ");
PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
ResultSet rs5 = pstmt5.executeQuery();
if (rs5.next()) {
rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);
// Calculo del Seguro con Via aereo
Seguro = montoBs.multiply(rate);
}
rs5.close();
pstmt5.close();
} else if (order.getXX_VLO_DispatchRoute_ID() == terrestre) {
String SQL5 =
("SELECT XX_RATE AS RATE "
+ "FROM XX_VLO_DispatchRoute "
+ "WHERE XX_VLO_DispatchRoute_ID = '"
+ terrestre
+ "' ");
PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
ResultSet rs5 = pstmt5.executeQuery();
if (rs5.next()) {
rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);
// Calculo del Seguro con Via terrestre
Seguro = montoBs.multiply(rate);
}
rs5.close();
pstmt5.close();
}
// Busco el porcentaje del flete internacional segun el proveedor, puerto y pais de la O/C
String SQL6 =
("SELECT DISTINCT A.XX_INTERFREESTIMATEPERT AS PERTFLEINTER "
+ "FROM XX_VLO_COSTSPERCENT A, C_ORDER B "
+ "WHERE B.XX_VLO_ARRIVALPORT_ID = '"
+ order.getXX_VLO_ArrivalPort_ID()
+ "' "
+ "AND B.C_BPARTNER_ID = '"
+ order.getC_BPartner_ID()
+ "' "
+ "AND B.C_COUNTRY_ID = '"
+ order.getC_Country_ID()
+ "' "
+ "AND B.XX_VLO_ARRIVALPORT_ID = A.XX_VLO_ARRIVALPORT_ID "
+ "AND B.XX_VLO_ARRIVALPORT_ID = A.XX_VLO_ARRIVALPORT_ID "
+ "AND B.C_BPARTNER_ID = A.C_BPARTNER_ID "
+ "AND B.C_COUNTRY_ID = A.C_COUNTRY_ID ");
PreparedStatement pstmt6 = DB.prepareStatement(SQL6, null);
ResultSet rs6 = pstmt6.executeQuery();
if (rs6.next()) {
// Porcentaje del flete internacional
perfleteInt =
rs6.getBigDecimal("PERTFLEINTER")
.divide(new BigDecimal(100), 4, RoundingMode.HALF_UP);
// Flete Internacional
fleteInternac = montoBs.multiply(perfleteInt);
}
rs6.close();
pstmt6.close();
// Busco los productos que tienen asociados las Ref de la O/C
String SQL7 =
("SELECT C.M_PRODUCT AS PRODUCT, A.XX_VMR_PO_LINEREFPROV_ID AS POLINE "
+ "FROM XX_VMR_PO_LINEREFPROV A, C_ORDER B, XX_VMR_REFERENCEMATRIX C "
+ "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
+ "AND A.C_ORDER_ID = B.C_ORDER_ID "
+ "AND A.C_ORDER_ID = '"
+ LineRefProv.getC_Order_ID()
+ "' ");
PreparedStatement pstmt7 = DB.prepareStatement(SQL7, null);
ResultSet rs7 = pstmt7.executeQuery();
while (rs7.next()) {
// Calculo del Porcentaje de cada item = costo de cada item Bs / Monto O/C Bs
productid = rs7.getInt("PRODUCT");
poline = rs7.getInt("POLINE");
// Costo de cada item Bs
/*String SQL8 = ("SELECT (A.XX_UNITPURCHASEPRICE / B.XX_UNITCONVERSION) AS COSTO " +
"FROM XX_VMR_PO_LINEREFPROV A, XX_VMR_UNITCONVERSION B, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D " +
"WHERE A.XX_VMR_UNITCONVERSION_ID = B.XX_VMR_UNITCONVERSION_ID " +
"AND A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID " +
"AND C.M_PRODUCT = D.M_PRODUCT_ID " +
"AND C.M_PRODUCT = '"+productid+"' " +
"AND A.XX_VMR_PO_LINEREFPROV_ID = '"+poline+"' ");*/
String SQL8 =
("SELECT (A.LINENETAMT * E.MULTIPLYRATE) AS COSTO "
+ "FROM XX_VMR_PO_LINEREFPROV A, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D, C_CONVERSION_RATE E, C_ORDER F "
+ "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
+ "AND C.M_PRODUCT = D.M_PRODUCT_ID "
+ "AND E.C_CONVERSION_RATE_ID = F.XX_CONVERSIONRATE_ID "
+ "AND A.C_ORDER_ID = F.C_ORDER_ID "
+ "AND C.M_PRODUCT = '"
+ productid
+ "' "
+ "AND A.XX_VMR_PO_LINEREFPROV_ID = '"
+ poline
+ "' ");
PreparedStatement pstmt8 = DB.prepareStatement(SQL8, null);
ResultSet rs8 = pstmt8.executeQuery();
if (rs8.next()) {
costoitem = rs8.getBigDecimal("COSTO");
// Porcentaje de cada item = costo de cada item Bs / Monto O/C Bs
porcentitem = (costoitem.divide(montoBs, 8, RoundingMode.HALF_UP));
// CIF Unitario = Porcentaje de cada Item * CIF total
cifuni = porcentitem.multiply(cifTotal);
// Busco Porcentaje Arancelario
String SQL9 =
("SELECT (D.XX_PERCENTAGETARIFF/100) AS PERARANCEL "
+ "FROM XX_VMR_PO_LINEREFPROV A, C_ORDER B, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D "
+ "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
+ "AND A.C_ORDER_ID = B.C_ORDER_ID "
+ "AND C.M_PRODUCT = D.M_PRODUCT_ID "
+ "AND D.M_PRODUCT_ID = '"
+ productid
+ "' "
+ "AND A.C_ORDER_ID = '"
+ LineRefProv.getC_Order_ID()
+ "' ");
PreparedStatement pstmt9 = DB.prepareStatement(SQL9, null);
ResultSet rs9 = pstmt9.executeQuery();
if (rs9.next()) {
// Porcentaje Arancelario
percentarancel = rs9.getBigDecimal("PERARANCEL");
// Arancel de cada item = CIF unitario * Porcentaje Arancelario
arancelitem = cifuni.multiply(percentarancel).add(arancelitem);
}
rs9.close();
pstmt9.close();
}
rs8.close();
pstmt8.close();
// cif total(Creo que no hace falta) t arancel item se usan abajo
} // end While rs7
rs7.close();
pstmt7.close();
String SQL9 =
("SELECT XX_RATE/100 AS RATESENIAT FROM XX_VLO_IMPORTRATE WHERE NAME = 'Tasa de Servicio de aduanas SENIAT' "
+ "And AD_Client_ID IN(0,"
+ Env.getCtx().getAD_Client_ID()
+ ")");
PreparedStatement pstmt9 = DB.prepareStatement(SQL9, null);
ResultSet rs9 = pstmt9.executeQuery();
if (rs9.next()) {
rateseniat = rs9.getBigDecimal("RATESENIAT");
montoseniat = arancelitem.multiply(rateseniat);
String SQL10 =
("SELECT XX_RATE/100 AS RATETESORERIA FROM XX_VLO_IMPORTRATE WHERE NAME = 'Tasa de Servicio Aduana Tesorería' "
+ "And AD_Client_ID IN(0,"
+ Env.getCtx().getAD_Client_ID()
+ ")");
PreparedStatement pstmt10 = DB.prepareStatement(SQL10, null);
ResultSet rs10 = pstmt10.executeQuery();
if (rs10.next()) {
ratetesoreria = rs10.getBigDecimal("RATETESORERIA");
montotesoreria = arancelitem.multiply(ratetesoreria);
// Monto Tasa aduanera =monto tasa seniat + monto tasa tesoreria
tasaaduana = montoseniat.add(montotesoreria);
}
rs10.close();
pstmt10.close();
}
rs9.close();
pstmt9.close();
// Calculo del IVA = (CIF total + Impuesto de importación + tasa aduanera) *%iva actual
String SQL11 =
("SELECT MAX (VALIDFROM) AS FECHA, A.RATE/100 AS IVACT "
+ "FROM C_TAX A, C_TAXCATEGORY B "
+ "WHERE A.C_TAXCATEGORY_ID = B.C_TAXCATEGORY_ID "
+ "AND B.DESCRIPTION = 'Impuesto al Valor Agregado' "
+ "AND A.C_TAXCATEGORY_ID = B.C_TAXCATEGORY_ID "
+ "GROUP BY A.RATE ");
PreparedStatement pstmt11 = DB.prepareStatement(SQL11, null);
ResultSet rs11 = pstmt11.executeQuery();
if (rs11.next()) {
part1 = cifTotal.add(arancelitem);
part2 = part1.add(tasaaduana);
iva = part2.multiply(rs11.getBigDecimal("IVACT"));
}
rs11.close();
pstmt11.close();
// Monto tesorería Nacional = IVA + impuesto de importación + monto tasa tesoreria
part = iva.add(arancelitem);
montesorerianac = part.add(montotesoreria);
// redondeo los BigDecimal
montesorerianac = montesorerianac.setScale(2, BigDecimal.ROUND_UP);
montoseniat = montoseniat.setScale(2, BigDecimal.ROUND_UP);
String sql =
"UPDATE C_Order po"
+ " SET XX_NatTreasuryEstAmount="
+ montesorerianac
+ " , XX_SENIATESTEEMEDAMUNT="
+ montoseniat
+ " WHERE po.C_Order_ID="
+ LineRefProv.getC_Order_ID();
DB.executeUpdate(null, sql);
} // end if Check
else {
// alguna referencia no tiene producto asociado
String sql =
"UPDATE C_Order po"
+ " SET XX_NatTreasuryEstAmount="
+ 0
+ " , XX_SENIATESTEEMEDAMUNT="
+ 0
+ " WHERE po.C_Order_ID="
+ LineRefProv.getC_Order_ID();
DB.executeUpdate(null, sql);
}
} // end try
catch (Exception e) {
}
} // dispose
