@Override
protected String doInBackground(String... strings) {
if (!spotCategory.equals("所有類型") && !spotCategory.equals("")) {
String sql =
"SELECT * FROM "
+ spotengname
+ " where city like '"
+ spotname
+ "' and category like '"
+ spotCategory
+ "'";
result = DBConnector.executeQuery(sql);
} else {
if (!spotCategory.equals("")) {
String sql = "SELECT * FROM " + spotengname + " where city like '" + spotname + "'";
result = DBConnector.executeQuery(sql);
}
}
if (spotname.equals(strText) && !spotCategory.equals("所有類型")) {
String sql = "SELECT * FROM " + spotengname + " where category like '" + spotCategory + "'";
result = DBConnector.executeQuery(sql);
} else {
if (spotname.equals(strText) && spotCategory.equals("所有類型")) {
String sql = "SELECT * FROM " + spotengname + " ";
result = DBConnector.executeQuery(sql);
}
}
return null;
}
public static Advancement findByName(String name) {
if (!connector.checkForDataBaseConnection()) {
return null;
}
Advancement advancement = null;
try {
Statement statement = connector.createStatement();
ResultSet rs =
statement.executeQuery("SELECT * FROM advancement WHERE name LIKE '" + name + "'");
if (rs.next()) {
advancement = new Advancement();
advancement.setId(rs.getInt(KeyConst.ADVANCEMENT_ID.getName()));
advancement.setName(rs.getString(KeyConst.ADVANCEMENT_NAME.getName()));
advancement.setImgPath(rs.getString(KeyConst.ADVANCEMENT_IMG_PATH.getName()));
}
} catch (Exception e) {
return null;
}
return advancement;
}
public static List<Advancement> findAllAdvancements() {
if (!connector.checkForDataBaseConnection()) {
return null;
}
List<Advancement> advancementList = new ArrayList<Advancement>();
try {
Statement statement = connector.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM advancement ORDER BY name");
while (rs.next()) {
Advancement advancement = new Advancement();
advancement.setId(rs.getInt(KeyConst.ADVANCEMENT_ID.getName()));
advancement.setName(rs.getString(KeyConst.ADVANCEMENT_NAME.getName()));
advancement.setImgPath(rs.getString(KeyConst.ADVANCEMENT_IMG_PATH.getName()));
advancementList.add(advancement);
}
} catch (Exception e) {
return null;
}
return advancementList;
}
private void queryServer(boolean showDialog) {
mListView.setAdapter(null);
if (showDialog) showDialog();
if (mConnector != null) mConnector.cancelQuery();
mConnector = new DBConnector(getActivity(), this);
mConnector.setOnDataReceivedListener(this);
mConnector.query(mUriData.clone());
}
private static boolean saveAdvancement(Advancement advancement) {
if (advancement == null) {
return true;
}
if (advancement.getId() < 0) {
advancement.setId(getNextId());
}
try {
Statement statement = connector.createStatement();
statement.executeUpdate(
"INSERT INTO advancement VALUES( "
+ advancement.getId()
+ ",'"
+ advancement.getName()
+ "', '"
+ advancement.getImgPath().replace("\\", "\\\\")
+ "')");
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
public GroupRecord[] getGroups() throws SQLException {
Connection connection = dbConnector.connect();
Statement getUsersStatement = connection.createStatement();
HashSet<GroupRecord> groups = new HashSet<>();
try (ResultSet resultSet =
getUsersStatement.executeQuery(
"SELECT `"
+ ID_FIELD_NAME
+ "`, `"
+ GROUP_NAME_FIELD_NAME
+ "` FROM `"
+ GROUPS_TABLE_NAME
+ "`")) {
while (resultSet.next())
groups.add(
new GroupRecord(
resultSet.getInt(ID_FIELD_NAME), resultSet.getString(GROUP_NAME_FIELD_NAME)));
}
getUsersStatement.close();
connection.close();
return groups.toArray(new GroupRecord[groups.size()]);
}
public Group getGroupById(int id) throws SQLException {
Connection connection = dbConnector.connect();
Statement statement = connection.createStatement();
Group group;
try (ResultSet resultSet =
statement.executeQuery(
"SELECT `"
+ ID_FIELD_NAME
+ "`, `"
+ GROUP_NAME_FIELD_NAME
+ "` FROM `"
+ GROUPS_TABLE_NAME
+ "` WHERE"
+ " `"
+ ID_FIELD_NAME
+ "` = "
+ id)) {
resultSet.next();
group =
new Group(resultSet.getInt(ID_FIELD_NAME), resultSet.getString(GROUP_NAME_FIELD_NAME));
} finally {
statement.close();
connection.close();
}
return group;
}
// Function to avoid replicating Javascript array printing code.
// the database connector needs a result array containing image sources at column index 0,
// captions at index 1 and an id for the photo's url at index 2.
public String printJSArrays(DBConnector dbc) {
int albumCnt = dbc.getRowCount();
String result =
"var imgSrcArray = new Array();\n"
+ "var imgCaptionArray = new Array();\n"
+ "var imgLinkArray = new Array();\n"
+ "var ALBUM_LENGTH = "
+ albumCnt
+ ";\n";
for (int i = 0; i < albumCnt; i++) {
result += "imgSrcArray[" + i + "] = 'images/" + dbc.getRecord(i, 0) + "';\n";
result += "imgCaptionArray[" + i + "] = '" + dbc.getRecord(i, 1) + "';\n";
result += "imgLinkArray[" + i + "] = '" + dbc.getRecord(i, 2) + "';\n";
}
return result;
}
public List<DatabaseEngine.AckRecord> DBReadAbilities() {
DBConnection D = null;
Vector<DatabaseEngine.AckRecord> rows = new Vector<DatabaseEngine.AckRecord>();
try {
D = DB.DBFetch();
ResultSet R = D.query("SELECT * FROM CMGAAC");
while (R.next())
rows.addElement(
new DatabaseEngine.AckRecord(
DBConnections.getRes(R, "CMGAID"),
DBConnections.getRes(R, "CMGAAT"),
DBConnections.getRes(R, "CMGACL")));
} catch (Exception sqle) {
Log.errOut("DataLoader", sqle);
} finally {
DB.DBDone(D);
}
// log comment
return rows;
}
public static List<String> getNameList() {
if (!connector.checkForDataBaseConnection()) {
return null;
}
List<String> advancementList = new ArrayList<String>();
try {
Statement statement = connector.createStatement();
ResultSet rs = statement.executeQuery("SELECT name FROM advancement ORDER BY name");
while (rs.next()) {
advancementList.add(rs.getString(KeyConst.ADVANCEMENT_NAME.getName()));
}
} catch (Exception e) {
return null;
}
return advancementList;
}
private static boolean deleteAdvancement(String advancementName) {
if (Util.isEmpty(advancementName)) {
return true;
}
try {
Statement statement = connector.createStatement();
statement.executeUpdate(
"DELETE FROM advancement WHERE name LIKE ('" + advancementName + "')");
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
public void DBCreateAbility(String classID, String typeClass, String data) {
DB.updateWithClobs(
"INSERT INTO CMGAAC ("
+ "CMGAID, "
+ "CMGAAT, "
+ "CMGACL "
+ ") values ("
+ "'"
+ classID
+ "',"
+ "?,"
+ "'"
+ typeClass
+ "'"
+ ")",
data + " ");
}
private static int getNextId() {
int id = 1;
try {
Statement statement = connector.createStatement();
ResultSet rs = statement.executeQuery("SELECT MAX(id) AS id FROM advancement");
if (rs.next()) {
id = rs.getInt(KeyConst.ADVANCEMENT_ID.getName()) + 1;
}
} catch (Exception e) {
id = -1;
}
return id;
}
public void addUserToGroup(int userId, int groupId) throws SQLException {
Connection connection = dbConnector.connect();
Statement statement = connection.createStatement();
statement.executeUpdate(
"INSERT IGNORE INTO `"
+ USER_GROUP_TABLE_NAME
+ "` SET `"
+ USER_ID_FIELD_NAME
+ "` = "
+ userId
+ ", `"
+ GROUP_ID_FIELD_NAME
+ "` = "
+ groupId);
statement.close();
connection.close();
}
public GroupRecord[] getUserGroups(int userId) throws SQLException {
Connection connection = dbConnector.connect();
Statement statement = connection.createStatement();
ArrayList<GroupRecord> groups = new ArrayList<>();
try (ResultSet resultSet =
statement.executeQuery(
"SELECT `"
+ GROUP_ID_FIELD_NAME
+ "`, `"
+ GROUP_NAME_FIELD_NAME
+ "` FROM "
+ "`"
+ GROUPS_TABLE_NAME
+ "` JOIN `"
+ USER_GROUP_TABLE_NAME
+ "` ON "
+ "`"
+ GROUPS_TABLE_NAME
+ "`.`"
+ ID_FIELD_NAME
+ "` = `"
+ USER_GROUP_TABLE_NAME
+ "`.`"
+ GROUP_ID_FIELD_NAME
+ "` WHERE "
+ "`"
+ USER_ID_FIELD_NAME
+ "` = "
+ userId)) {
while (resultSet.next()) {
groups.add(
new GroupRecord(
resultSet.getInt(GROUP_ID_FIELD_NAME), resultSet.getString(GROUP_NAME_FIELD_NAME)));
}
} finally {
statement.close();
connection.close();
}
return groups.toArray(new GroupRecord[groups.size()]);
}
public void removeUserFromGroup(int userId, int groupId) throws SQLException {
Connection connection = dbConnector.connect();
Statement statement = connection.createStatement();
statement.executeUpdate(
"DELETE FROM "
+ USER_GROUP_TABLE_NAME
+ " WHERE `"
+ USER_ID_FIELD_NAME
+ "` = "
+ userId
+ " AND "
+ "`"
+ GROUP_ID_FIELD_NAME
+ "` ="
+ " "
+ groupId);
statement.close();
connection.close();
}
private static boolean updateAdvancement(Advancement advancement) {
if (advancement == null) {
return true;
}
try {
Statement statement = connector.createStatement();
statement.executeUpdate(
"UPDATE advancement SET name = '"
+ advancement.getName()
+ "', imgPath = '"
+ advancement.getImgPath().replace("\\", "\\\\")
+ "'"
+ " WHERE id = "
+ advancement.getId());
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
public int guardaPedido(perfum.Pedido pedido) throws SQLException {
String non_query =
"insert into pedido values (null,current_timestamp,"
+ pedido.getId_cliente()
+ ","
+ perfum.Pedido.SIN_CONFIRMAR
+ ")";
// System.out.println("insert pedido nonquery: "+non_query);
db.getStatement().executeUpdate(non_query);
String query = "select max(id_pedido) id_pedido from pedido";
ResultSet rs = db.ejecuta_query(query);
if (rs.next()) {
int id_pedido_gen = rs.getInt("id_pedido");
int nro_linea_pedido = 1;
for (LineaDePedido li : pedido.getLineas()) {
non_query =
"insert into linea_pedido values("
+ nro_linea_pedido
+ ","
+ id_pedido_gen
+ ","
+ li.getProducto().getId_producto()
+ ","
+ li.getPresentacion().getId()
+ ","
+ li.getCantidad()
+ ","
+ DBConnector.adapta_float(li.getMonto())
+ ")";
db.getStatement().executeUpdate(non_query);
nro_linea_pedido++;
}
return id_pedido_gen;
}
return -1;
}
/** Create the frame. */
@SuppressWarnings({"unchecked", "rawtypes"})
public MembersManagement() {
connection = DBConnector.dbConnector();
setIconImage(
Toolkit.getDefaultToolkit()
.getImage(
MembersManagement.class.getResource(
"/Resources/Custo.Man.Christmas.Folder.Library.ico.png")));
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setBounds(100, 100, 1366, 768);
contentPane = new JPanel();
contentPane.setBackground(Color.DARK_GRAY);
contentPane.setBorder(new LineBorder(Color.BLUE, 1, true));
setContentPane(contentPane);
StartPosition.centerOnScreen(this);
String[] MemberType = new String[] {"Students", "Teachers"};
JButton button = new JButton("_");
button.setBounds(1234, 1, 44, 23);
button.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
MinFrame();
}
});
contentPane.setLayout(null);
button.setForeground(Color.WHITE);
button.setFocusable(false);
button.setBackground(Color.DARK_GRAY);
contentPane.add(button);
JButton button_1 = new JButton("");
button_1.setBounds(1277, 1, 44, 23);
button_1.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
MaxFrame();
}
});
button_1.setIcon(new ImageIcon(MembersManagement.class.getResource("/Resources/Maximize.png")));
button_1.setForeground(Color.WHITE);
button_1.setFocusable(false);
button_1.setBackground(Color.DARK_GRAY);
contentPane.add(button_1);
JButton button_2 = new JButton("X");
button_2.setBounds(1321, 1, 44, 23);
button_2.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
CloseFrame();
}
});
button_2.setForeground(Color.WHITE);
button_2.setFont(new Font("Ubuntu", Font.BOLD, 8));
button_2.setFocusable(false);
button_2.setBackground(Color.RED);
contentPane.add(button_2);
Border emptyBorder = BorderFactory.createEmptyBorder();
JLabel lblStudentsManagement = new JLabel("Members Management");
lblStudentsManagement.setBounds(403, 34, 543, 78);
lblStudentsManagement.setHorizontalAlignment(SwingConstants.CENTER);
lblStudentsManagement.setForeground(new Color(255, 255, 255));
lblStudentsManagement.setFont(new Font("Ubuntu", Font.BOLD, 36));
contentPane.add(lblStudentsManagement);
String[] searchCriteria = new String[] {"Name", "Registration Number"};
JPanel panel3 = new JPanel();
panel3.setBackground(Color.DARK_GRAY);
panel3.setBorder(new LineBorder(Color.BLUE));
panel3.setBounds(14, 187, 1340, 568);
contentPane.add(panel3);
panel3.setLayout(null);
panel3.setVisible(false);
JLabel lblSelectSearchCriteria = new JLabel("Select Search Criteria");
lblSelectSearchCriteria.setForeground(Color.WHITE);
lblSelectSearchCriteria.setFont(new Font("Ubuntu", Font.PLAIN, 13));
lblSelectSearchCriteria.setBounds(487, 39, 149, 21);
panel3.add(lblSelectSearchCriteria);
JScrollPane scrollPane_1 = new JScrollPane();
scrollPane_1.setBounds(10, 99, 1320, 402);
panel3.add(scrollPane_1);
table_1 = new JTable();
table_1.setCursor(Cursor.getPredefinedCursor(Cursor.HAND_CURSOR));
table_1.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
scrollPane_1.setViewportView(table_1);
JLabel lblCriteria = new JLabel("Name");
lblCriteria.setForeground(Color.WHITE);
lblCriteria.setFont(new Font("Ubuntu", Font.PLAIN, 13));
lblCriteria.setBounds(952, 39, 149, 21);
panel3.add(lblCriteria);
JComboBox cmbSearchCriteria = new JComboBox(searchCriteria);
cmbSearchCriteria.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
if (cmbSearchCriteria.getSelectedItem().toString() == "Name") {
lblCriteria.setText("Name");
txtSearch.setBounds(1001, 37, 160, 25);
} else if (cmbSearchCriteria.getSelectedItem().toString() == "Registration Number") {
lblCriteria.setText("Registration Number");
txtSearch.setBounds(1100, 37, 160, 25);
}
}
});
cmbSearchCriteria.setForeground(Color.WHITE);
cmbSearchCriteria.setFont(new Font("Ubuntu", Font.PLAIN, 13));
cmbSearchCriteria.setBorder(emptyBorder);
cmbSearchCriteria.setBackground(Color.DARK_GRAY);
cmbSearchCriteria.setBounds(646, 39, 160, 20);
panel3.add(cmbSearchCriteria);
JComboBox cmbType = new JComboBox(MemberType);
cmbType.setForeground(Color.WHITE);
cmbType.setFont(new Font("Ubuntu", Font.PLAIN, 13));
cmbType.setBackground(Color.DARK_GRAY);
cmbType.setBounds(246, 39, 160, 20);
panel3.add(cmbType);
JLabel lblSearchBy = new JLabel("Search By");
lblSearchBy.setForeground(Color.WHITE);
lblSearchBy.setFont(new Font("Ubuntu", Font.PLAIN, 13));
lblSearchBy.setBounds(885, 39, 75, 21);
panel3.add(lblSearchBy);
txtSearch = new JTextField();
txtSearch.addKeyListener(
new KeyAdapter() {
@Override
public void keyTyped(KeyEvent e) {
String type = cmbType.getSelectedItem().toString();
String sCriteria = cmbSearchCriteria.getSelectedItem().toString();
if (type == "Students") {
if (sCriteria == "Name") {
try {
LibMemberDM bDM = new LibMemberDM();
table_1.setModel(
DbUtils.resultSetToTableModel(bDM.searchByNameS(txtSearch.getText())));
} catch (Exception ex) {
JOptionPane.showMessageDialog(null, ex.getMessage());
}
} else if (sCriteria == "Registration Number") {
try {
LibMemberDM bDM = new LibMemberDM();
table_1.setModel(
DbUtils.resultSetToTableModel(bDM.searchByRegNoS(txtSearch.getText())));
} catch (Exception ex) {
JOptionPane.showMessageDialog(null, ex.getMessage());
}
}
} else if (type == "Teachers") {
if (sCriteria == "Name") {
try {
LibMemberDM bDM = new LibMemberDM();
table_1.setModel(
DbUtils.resultSetToTableModel(bDM.searchByNameT(txtSearch.getText())));
} catch (Exception ex) {
JOptionPane.showMessageDialog(null, ex.getMessage());
}
} else if (sCriteria == "Registration Number") {
try {
LibMemberDM bDM = new LibMemberDM();
table_1.setModel(
DbUtils.resultSetToTableModel(bDM.searchByRegNoT(txtSearch.getText())));
} catch (Exception ex) {
JOptionPane.showMessageDialog(null, ex.getMessage());
}
}
}
}
});
txtSearch.setForeground(Color.WHITE);
txtSearch.setFont(new Font("Ubuntu", Font.PLAIN, 13));
txtSearch.setBorder(emptyBorder);
txtSearch.setBackground(Color.GRAY);
txtSearch.setBounds(1001, 37, 200, 25);
panel3.add(txtSearch);
txtSearch.setColumns(10);
JButton btnUpdate = new JButton("Update");
btnUpdate.setFont(new Font("Ubuntu", Font.PLAIN, 12));
btnUpdate.setBackground(Color.DARK_GRAY);
btnUpdate.setForeground(Color.WHITE);
btnUpdate.setBounds(518, 523, 89, 23);
panel3.add(btnUpdate);
JButton btnRemove = new JButton("Remove");
btnRemove.setFont(new Font("Ubuntu", Font.PLAIN, 12));
btnRemove.setBackground(Color.DARK_GRAY);
btnRemove.setForeground(Color.WHITE);
btnRemove.setBounds(676, 523, 89, 23);
panel3.add(btnRemove);
JLabel lblSelectMemberType = new JLabel("Select Member Type");
lblSelectMemberType.setForeground(Color.WHITE);
lblSelectMemberType.setFont(new Font("Ubuntu", Font.PLAIN, 13));
lblSelectMemberType.setBounds(97, 39, 149, 21);
panel3.add(lblSelectMemberType);
JPanel panel1 = new JPanel();
panel1.setBorder(new LineBorder(Color.BLUE));
panel1.setBackground(Color.DARK_GRAY);
panel1.setBounds(14, 187, 1340, 568);
contentPane.add(panel1);
panel1.setVisible(true);
panel1.setLayout(null);
JComboBox cmbMemType = new JComboBox(MemberType);
cmbMemType.setForeground(Color.WHITE);
cmbMemType.setFont(new Font("Ubuntu", Font.PLAIN, 13));
cmbMemType.setBorder(emptyBorder);
cmbMemType.setBackground(Color.DARK_GRAY);
cmbMemType.setBounds(449, 39, 146, 25);
panel1.add(cmbMemType);
JButton btnView = new JButton("View Members");
btnView.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
String Type = cmbMemType.getSelectedItem().toString();
try {
if (Type == "Students") {
String Query =
"select t2.MemID, t1.FullName, t1.Address, t1.DOB, t1.NIC, t1.Sex, t1.ContactNo, t1.CurrentGrade, t1.CurrentClass from StudentsMF t1, LibMem t2 where t1.RegNo = t2.RegNoS";
PreparedStatement pst = connection.prepareStatement(Query);
ResultSet rs = pst.executeQuery();
table.setModel(DbUtils.resultSetToTableModel(rs));
rs.close();
pst.close();
} else if (Type == "Teachers") {
String Query =
"select t2.MemID, t1.Name, t1.Adress, t1.DOB, t1.NIC, t1.Sex, t1.ContactNo, t1.TGrade, t1.Type from TeachersMF t1, LibMem t2 where t1.RegNo = t2.RegNoT";
PreparedStatement pst = connection.prepareStatement(Query);
ResultSet rs = pst.executeQuery();
table.setModel(DbUtils.resultSetToTableModel(rs));
rs.close();
pst.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
});
btnView.setForeground(Color.WHITE);
btnView.setBackground(Color.DARK_GRAY);
btnView.setFont(new Font("Ubuntu", Font.PLAIN, 13));
btnView.setBounds(736, 36, 131, 30);
panel1.add(btnView);
JScrollPane scrollPane = new JScrollPane();
scrollPane.setBounds(10, 99, 1320, 458);
panel1.add(scrollPane);
table = new JTable();
table.setGridColor(Color.GRAY);
table.setFont(new Font("Ubuntu", Font.PLAIN, 11));
table.setCursor(Cursor.getPredefinedCursor(Cursor.HAND_CURSOR));
table.setBackground(Color.WHITE);
table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
scrollPane.setViewportView(table);
JPanel panel2 = new JPanel();
panel2.setBorder(new LineBorder(Color.BLUE));
panel2.setBackground(Color.DARK_GRAY);
panel2.setBounds(14, 187, 1340, 568);
contentPane.add(panel2);
panel2.setLayout(null);
JLabel lblISBN = new JLabel("Registration Number");
lblISBN.setForeground(Color.WHITE);
lblISBN.setFont(new Font("Ubuntu", Font.PLAIN, 13));
lblISBN.setBounds(232, 183, 142, 22);
panel2.add(lblISBN);
txtRegNo = new JTextField();
txtRegNo.setCursor(Cursor.getPredefinedCursor(Cursor.TEXT_CURSOR));
txtRegNo.setBackground(Color.GRAY);
txtRegNo.setForeground(Color.WHITE);
txtRegNo.setFont(new Font("Ubuntu", Font.PLAIN, 13));
txtRegNo.setBounds(413, 182, 208, 24);
txtRegNo.setBorder(emptyBorder);
panel2.add(txtRegNo);
txtRegNo.setColumns(10);
txtPassword = new JTextField();
txtPassword.setCursor(Cursor.getPredefinedCursor(Cursor.TEXT_CURSOR));
txtPassword.setForeground(Color.WHITE);
txtPassword.setFont(new Font("Ubuntu", Font.PLAIN, 13));
txtPassword.setBorder(emptyBorder);
txtPassword.setColumns(10);
txtPassword.setBackground(Color.GRAY);
txtPassword.setBounds(413, 214, 208, 24);
panel2.add(txtPassword);
JLabel lblName = new JLabel("Password");
lblName.setForeground(Color.WHITE);
lblName.setFont(new Font("Ubuntu", Font.PLAIN, 13));
lblName.setBounds(232, 215, 142, 22);
panel2.add(lblName);
JLabel lblNicNumber = new JLabel("Re-Enter Password");
lblNicNumber.setForeground(Color.WHITE);
lblNicNumber.setFont(new Font("Ubuntu", Font.PLAIN, 13));
lblNicNumber.setBounds(232, 248, 142, 22);
panel2.add(lblNicNumber);
txtRPassword = new JTextField();
txtRPassword.setCursor(Cursor.getPredefinedCursor(Cursor.TEXT_CURSOR));
txtRPassword.setForeground(Color.WHITE);
txtRPassword.setFont(new Font("Ubuntu", Font.PLAIN, 13));
txtRPassword.setBorder(emptyBorder);
txtRPassword.setColumns(10);
txtRPassword.setBackground(Color.GRAY);
txtRPassword.setBounds(413, 247, 208, 24);
panel2.add(txtRPassword);
JButton btnClear = new JButton("Clear");
btnClear.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
clearFeilds();
}
});
btnClear.setForeground(Color.WHITE);
btnClear.setFont(new Font("Ubuntu", Font.PLAIN, 13));
btnClear.setBackground(Color.DARK_GRAY);
btnClear.setBounds(474, 344, 110, 32);
panel2.add(btnClear);
JButton btnAddMember = new JButton("Add Member");
btnAddMember.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (!txtRPassword.getText().equals("")
|| !txtPassword.getText().equals("")
|| !txtRegNo.getText().equals("")) {
if (txtPassword.getText().equals(txtRPassword.getText())) {
try {
LibMemberDM mDM = new LibMemberDM();
LibMember member = new LibMember();
member.setMemID(txtRegNo.getText());
member.setPassword(txtPassword.getText());
// checking whether RegNo exist in the database {
String query1 = "select RegNo from TeachersMF where RegNo=?";
String query2 = "select RegNo from StudentsMF where RegNo=?";
PreparedStatement pst1 = connection.prepareStatement(query1);
pst1.setString(1, member.getMemID());
PreparedStatement pst2 = connection.prepareStatement(query2);
pst2.setString(1, member.getMemID());
ResultSet rs1 = pst1.executeQuery();
ResultSet rs2 = pst2.executeQuery();
int count1 = 0, count2 = 0;
while (rs1.next()) count1++;
while (rs2.next()) count2++;
if (count1 == 0 && count2 == 0)
JOptionPane.showMessageDialog(
null,
"Registration Number does not exist!",
"Error",
JOptionPane.WARNING_MESSAGE);
else if (count1 == 1 && count2 == 0) {
String query3 =
"select MemID from LibMem where MemID='" + member.getMemID() + "'";
PreparedStatement pst3 = connection.prepareStatement(query3);
ResultSet rs3 = pst3.executeQuery();
int count3 = 0;
while (rs3.next()) count3++;
if (count3 != 0) JOptionPane.showMessageDialog(null, "User Already Exist!");
else if (count3 == 0) {
if (mDM.insertMemberT(member)) {
JOptionPane.showMessageDialog(null, "Successful");
clearFeilds();
} else JOptionPane.showMessageDialog(null, "Failed");
rs3.close();
pst3.close();
}
} else if (count1 == 0 && count2 == 1) {
String query6 = "select * from LibMem where MemID='" + member.getMemID() + "'";
PreparedStatement pst5 = connection.prepareStatement(query6);
ResultSet rs4 = pst5.executeQuery();
int count3 = 0;
while (rs4.next()) count3++;
if (count3 != 0) JOptionPane.showMessageDialog(null, "User Already Exist!");
else if (count3 == 0) {
if (mDM.insertMemberS(member)) {
JOptionPane.showMessageDialog(null, "Successful");
clearFeilds();
} else JOptionPane.showMessageDialog(null, "Failed");
rs4.close();
pst5.close();
}
}
rs1.close();
rs2.close();
pst1.close();
pst2.close();
} catch (Exception x) {
JOptionPane.showMessageDialog(null, x);
System.out.println(x);
}
} else JOptionPane.showMessageDialog(null, "Passwords do not Match");
} else JOptionPane.showMessageDialog(null, "Fill in All the Details");
}
});
btnAddMember.setFont(new Font("Ubuntu", Font.PLAIN, 13));
btnAddMember.setBackground(Color.DARK_GRAY);
btnAddMember.setForeground(Color.WHITE);
btnAddMember.setBounds(265, 344, 125, 32);
panel2.add(btnAddMember);
JLabel label = new JLabel("");
label.setIcon(
new ImageIcon(MembersManagement.class.getResource("/Resources/sign-up-icon.png")));
label.setBounds(856, 140, 256, 226);
panel2.add(label);
panel2.setVisible(false);
// Creating Tab Buttons of Tabs Pane
JButton btnEditStudentDetails = new JButton("Edit Member Details");
JButton btnAddStudents = new JButton("Add Members");
JButton btnViewStudents = new JButton("View Members");
btnViewStudents.setFont(new Font("Ubuntu", Font.PLAIN, 13));
btnViewStudents.setForeground(Color.WHITE);
btnViewStudents.setBackground(Color.BLUE);
btnViewStudents.setBounds(14, 164, 124, 23);
contentPane.add(btnViewStudents);
btnAddStudents.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (btnAddStudents.hasFocus()) {
btnAddStudents.setBackground(Color.BLUE);
btnAddStudents.setForeground(Color.WHITE);
btnViewStudents.setBackground(Color.DARK_GRAY);
btnViewStudents.setForeground(Color.WHITE);
btnEditStudentDetails.setBackground(Color.DARK_GRAY);
btnEditStudentDetails.setForeground(Color.WHITE);
}
if (panel2.isVisible() == false) {
panel2.setVisible(true);
panel1.setVisible(false);
panel3.setVisible(false);
}
}
});
btnAddStudents.setForeground(Color.WHITE);
btnAddStudents.setBorder(BorderFactory.createLineBorder(Color.BLUE));
btnAddStudents.setFont(new Font("Ubuntu", Font.PLAIN, 13));
btnAddStudents.setBackground(Color.DARK_GRAY);
btnAddStudents.setBounds(137, 164, 144, 23);
contentPane.add(btnAddStudents);
btnEditStudentDetails.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (btnEditStudentDetails.hasFocus()) {
btnEditStudentDetails.setBackground(Color.BLUE);
btnEditStudentDetails.setForeground(Color.WHITE);
btnAddStudents.setBackground(Color.DARK_GRAY);
btnAddStudents.setForeground(Color.WHITE);
btnViewStudents.setBackground(Color.DARK_GRAY);
btnViewStudents.setForeground(Color.WHITE);
}
if (panel3.isVisible() == false) {
panel3.setVisible(true);
panel1.setVisible(false);
panel2.setVisible(false);
}
}
});
btnEditStudentDetails.setForeground(Color.WHITE);
btnEditStudentDetails.setBorder(BorderFactory.createLineBorder(Color.BLUE));
btnEditStudentDetails.setFont(new Font("Ubuntu", Font.PLAIN, 13));
btnEditStudentDetails.setBackground(Color.DARK_GRAY);
btnEditStudentDetails.setBounds(280, 164, 169, 23);
contentPane.add(btnEditStudentDetails);
btnViewStudents.setBorder(BorderFactory.createLineBorder(Color.BLUE));
btnViewStudents.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (btnViewStudents.hasFocus()) {
btnViewStudents.setBackground(Color.BLUE);
btnViewStudents.setForeground(Color.WHITE);
btnAddStudents.setBackground(Color.DARK_GRAY);
btnAddStudents.setForeground(Color.WHITE);
btnEditStudentDetails.setBackground(Color.DARK_GRAY);
btnEditStudentDetails.setForeground(Color.WHITE);
}
if (panel1.isVisible() == false) {
panel1.setVisible(true);
panel2.setVisible(false);
panel3.setVisible(false);
}
}
});
}
public static void main(String[] args) throws IOException {
System.out.println("Starting test");
DBConnector db = new DBConnector();
Connection conn = db.getDbConnection();
Statement stmt = null;
ResultSet rs = null;
try {
String filePath =
"/home/gaurabde/Documents/Cloud/workSpace/RDSPuchCode/ParserOuputData/productDetails_EBay";
BufferedReader input = new BufferedReader(new FileReader(filePath));
System.out.println("loading");
int i = 0, j = 1;
String stringInput = input.readLine();
stmt = conn.createStatement();
System.out.println("create statement");
while (stringInput != null) {
String[] prod = stringInput.split("::::");
// stmt.executeUpdate("INSERT INTO Ebay_Products
// (ID,Product_Name,Product_URL,Product_Price,Store_Name) VALUES
// (0,\""+prod[1]+"\",\""+prod[3]+"\","+prod[5]+",\""+prod[7]+"\");");
System.out.println(
"" + prod[1] + "\",\"" + prod[3] + "\"," + prod[5] + ",\"" + prod[7] + "\"");
if (i > 10) {
i = 0;
System.out.println();
} else {
i++;
}
j++;
stringInput = input.readLine();
}
// rs.next();
// String searchProductList= ((String)rs.getObject(1));
// System.out.println(searchProductList+ "Successful from Database!");
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
} finally {
// it is a good idea to release
// resources in a finally{} block
// in reverse-order of their creation
// if they are no-longer needed
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) {
} // ignore
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
} // ignore
stmt = null;
}
}
}
public void DBDeleteAbility(String classID) {
DB.update("DELETE FROM CMGAAC WHERE CMGAID='" + classID + "'");
}
public void uploadMeteoData(String[] parameters) throws GCRGException {
int lineNo = 0;
AtqasukDAO atq = new AtqasukDAO();
Connection conn = DBConnector.getDBConnection();
PreparedStatement preStmt = null;
ResultSet rs = null;
Calendar calendar = Calendar.getInstance();
calendar.set(Calendar.DAY_OF_YEAR, Integer.parseInt(parameters[2]));
calendar.set(Calendar.YEAR, Integer.parseInt(parameters[1]));
calendar.set(Calendar.HOUR_OF_DAY, Integer.parseInt(parameters[3]) / 100);
calendar.set(Calendar.MINUTE, Integer.parseInt(parameters[3]) % 100);
// String oldFormat = "MM/dd/yyyy HH:mm";
String newFormat = "yyyy/MM/dd HH:mm";
SimpleDateFormat sdf;
MeteorologicalDataVO dataVo;
try {
dataVo = new MeteorologicalDataVO();
// use comma as separator
sdf = new SimpleDateFormat(newFormat);
Date date = new Date(calendar.getTimeInMillis());
System.out.println("date:" + sdf.format(date));
dataVo.setTimeStamp(sdf.format(date));
if (!parameters[15].equals("") && !parameters[15].equals("NaN")) {
dataVo.setAir_T_AVG_L(Float.parseFloat(parameters[15]));
System.out.println("Air_T_AVG_L:" + parameters[15]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[17].equals("") && !parameters[17].equals("NaN")) {
dataVo.setG_1_AVG_L(Float.parseFloat(parameters[17]));
System.out.println("G_1_AVG_L:" + parameters[17]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[18].equals("") && !parameters[18].equals("NaN")) {
dataVo.setG_2_AVG_L(Float.parseFloat(parameters[18]));
System.out.println("G_2_AVG_L:" + parameters[18]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[19].equals("") && !parameters[19].equals("NaN")) {
dataVo.setG_3_AVG_L(Float.parseFloat(parameters[19]));
System.out.println("G_3_AVG_L:" + parameters[19]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[46].equals("") && !parameters[46].equals("NaN")) {
dataVo.setP2_SWC_15_AVG_L(Float.parseFloat(parameters[46]));
System.out.println("P2_SWC_15_AVG_L:" + parameters[46]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[47].equals("") && !parameters[47].equals("NaN")) {
dataVo.setP2_SWC_30_AVG_L(Float.parseFloat(parameters[47]));
System.out.println("P2_SWC_30_AVG_L:" + parameters[47]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[45].equals("") && !parameters[45].equals("NaN")) {
dataVo.setP2_SWC_5_AVG_L(Float.parseFloat(parameters[45]));
System.out.println("P2_SWC_5_AVG_L:" + parameters[45]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[52].equals("") && !parameters[52].equals("NaN")) {
dataVo.setP3_SolT15_AVG_L(Float.parseFloat(parameters[52]));
System.out.println("P3_SolT15_AVG_L:" + parameters[52]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[53].equals("") && !parameters[53].equals("NaN")) {
dataVo.setP3_SolT30_AVG_L(Float.parseFloat(parameters[53]));
System.out.println("P3_SolT30_AVG_L:" + parameters[53]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[51].equals("") && !parameters[51].equals("NaN")) {
dataVo.setP3_SolT5_AVG_L(Float.parseFloat(parameters[51]));
System.out.println("P3_SolT5_AVG_L:" + parameters[51]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[49].equals("") && !parameters[49].equals("NaN")) {
dataVo.setP3_SWC_15_AVG_L(Float.parseFloat(parameters[49]));
System.out.println("P3_SWC_15_AVG_L:" + parameters[49]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[50].equals("") && !parameters[50].equals("NaN")) {
dataVo.setP3_SWC_30_AVG_L(Float.parseFloat(parameters[50]));
System.out.println("P3_SWC_30_AVG_L:" + parameters[50]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[48].equals("") && !parameters[48].equals("NaN")) {
dataVo.setP3_SWC_5_AVG_L(Float.parseFloat(parameters[48]));
System.out.println("P3_SWC_5_AVG_L:" + parameters[48]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[52].equals("") && !parameters[52].equals("NaN")) {
dataVo.setP4_SolT15_AVG_L(Float.parseFloat(parameters[52]));
System.out.println("P4_SolT15_AVG_L:" + parameters[52]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[53].equals("") && !parameters[53].equals("NaN")) {
dataVo.setP4_SolT30_AVG_L(Float.parseFloat(parameters[53]));
System.out.println("P4_SolT30_AVG_L:" + parameters[53]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[54].equals("") && !parameters[54].equals("NaN")) {
dataVo.setP4_SolT5_AVG_L(Float.parseFloat(parameters[54]));
System.out.println("P4_SolT5_AVG_L:" + parameters[54]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[6].equals("") && !parameters[6].equals("NaN")) {
dataVo.setPAR_AVG_L(Float.parseFloat(parameters[6]));
System.out.println("PAR_AVG_L:" + parameters[6]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[21].equals("") && !parameters[21].equals("NaN")) {
dataVo.setPPT_TOT_L(Float.parseFloat(parameters[21]));
System.out.println("PPT_TOT_L:" + parameters[21]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[27].equals("") && !parameters[27].equals("NaN")) {
dataVo.setPress_mb_AVG_L(Float.parseFloat(parameters[27]));
System.out.println("Press_mb_AVG_L:" + parameters[27]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[16].equals("") && !parameters[16].equals("NaN")) {
dataVo.setRH_AVG_L(Float.parseFloat(parameters[16]));
System.out.println("RH_AVG_L:" + parameters[16]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[10].equals("") && !parameters[10].equals("NaN")) {
dataVo.setRnet_WC_AVG_L(Float.parseFloat(parameters[10]));
System.out.println("Rnet_WC_AVG_L:" + parameters[10]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[7].equals("") && !parameters[7].equals("NaN")) {
dataVo.setRsolar_AVG_L(Float.parseFloat(parameters[7]));
System.out.println("Rsolar_AVG_L:" + parameters[7]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[57].equals("") && !parameters[57].equals("NaN")) {
dataVo.setSnowDepth_L(Float.parseFloat(parameters[57]));
System.out.println("SnowDepth_L:" + parameters[57]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[29].equals("") && !parameters[29].equals("NaN")) {
dataVo.setSS_Dif_R_AVG_L(Float.parseFloat(parameters[29]));
System.out.println("SS_Dif_R_AVG_L:" + parameters[29]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[28].equals("") && !parameters[28].equals("NaN")) {
dataVo.setSS_Tl_R_AVG_L(Float.parseFloat(parameters[28]));
System.out.println("SS_Tl_R_AVG_L:" + parameters[28]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[41].equals("") && !parameters[41].equals("NaN")) {
dataVo.setSWC_1_AVG_L(Float.parseFloat(parameters[41]));
System.out.println("SWC_1_AVG_L:" + parameters[41]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[42].equals("") && !parameters[42].equals("NaN")) {
dataVo.setSWC_2_AVG_L(Float.parseFloat(parameters[42]));
System.out.println("SWC_2_AVG_L:" + parameters[42]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[43].equals("") && !parameters[43].equals("NaN")) {
dataVo.setSWC_3_AVG_L(Float.parseFloat(parameters[43]));
System.out.println("SWC_3_AVG_L:" + parameters[43]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[44].equals("") && !parameters[44].equals("NaN")) {
dataVo.setSWC_4_AVG_L(Float.parseFloat(parameters[44]));
System.out.println("SWC_4_AVG_L:" + parameters[44]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
if (!parameters[16].equals("")) {
dataVo.setSoil_1_AVG_L(Float.parseFloat(parameters[11]));
System.out.println("Soil_1_AVG_L:" + parameters[11]);
} else {
dataVo.setSoil_1_AVG_L(-9999);
}
if (!parameters[17].equals("")) {
dataVo.setSoil_2_AVG_L(Float.parseFloat(parameters[12]));
System.out.println("Soil_2_AVG_L:" + parameters[12]);
} else {
dataVo.setSoil_2_AVG_L(-9999);
}
if (!parameters[18].equals("")) {
dataVo.setSoil2_1_AVG_L(Float.parseFloat(parameters[13]));
System.out.println("Soil2_1_AVG_L:" + parameters[13]);
} else {
dataVo.setSoil2_1_AVG_L(-9999);
}
if (!parameters[19].equals("") && !parameters[19].equals("NaN")) {
dataVo.setSoil2_2_AVG_L(Float.parseFloat(parameters[14]));
System.out.println("Soil2_2_AVG_L:" + parameters[14]);
} else {
dataVo.setSoil2_2_AVG_L(-9999);
}
try {
atq.addMeteoData(dataVo, conn, preStmt, rs);
System.out.println("Uploaded");
} catch (GCRGException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
lineNo++;
} finally {
DBConnector.closeResources(preStmt, rs, conn);
}
System.out.println("Done with " + lineNo + "rows");
}
@Override
public void handle(MouseEvent click) {
@SuppressWarnings("rawtypes")
TablePosition pos = groupsTable.getSelectionModel().getSelectedCells().get(0);
if (click.getClickCount() == 2) {
groupsTable.getSelectionModel().setCellSelectionEnabled(true);
if (pos.getColumn() == 1) {
try {
TableColumn<GroupModel, String> firstNameCol = pos.getTableColumn();
firstNameCol.setCellFactory(TextFieldTableCell.<GroupModel>forTableColumn());
firstNameCol.setOnEditCommit(
(TableColumn.CellEditEvent<GroupModel, String> t) -> {
GroupModel model =
((GroupModel) t.getTableView().getItems().get(t.getTablePosition().getRow()));
if (t.getNewValue().isEmpty()) {
emptyCheckingError();
} else {
model.setGroupName(t.getNewValue());
DBConnector.getInstance().updateGroupModel(model);
dbUpdatable.onDBUpdated();
}
});
} catch (ClassCastException exc) {
System.out.println("ClassCastException ");
}
} else if (pos.getColumn() == 2 || pos.getColumn() == 3) {
TableColumn<GroupModel, Integer> firstNameCol = pos.getTableColumn();
firstNameCol.setCellFactory(TextFieldTableCell.forTableColumn(new EditStringConverter()));
try {
firstNameCol.setOnEditCommit(
new EventHandler<TableColumn.CellEditEvent<GroupModel, Integer>>() {
@Override
public void handle(TableColumn.CellEditEvent<GroupModel, Integer> tt) {
int row = tt.getTablePosition().getRow();
int column = tt.getTablePosition().getColumn();
GroupModel rowModel = ((GroupModel) tt.getTableView().getItems().get(row));
Integer rowNewValue = tt.getNewValue().intValue();
if (tt.getNewValue() == 0) {
emptyCheckingError();
} else if ((tt.getNewValue().getClass().isPrimitive())) {
// TODO: 1/4/2016
invalidDigitValue(tt.getNewValue().toString());
} else {
switch (column) {
case 2:
rowModel.setGroupYearGraduate(rowNewValue);
break;
case 3:
rowModel.setVillageElderId(rowNewValue);
break;
}
DBConnector.getInstance().updateGroupModel(rowModel);
dbUpdatable.onDBUpdated();
}
}
});
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
