/** Delete an existing record from the Age database. */
public boolean deleteRecord(int PKOrganization, int PKUser) throws SQLException, Exception {
String OldName = "";
String command = "SELECT * FROM tblOrganization WHERE PKOrganization = " + PKOrganization;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(command);
if (rs.next()) {
OldName = rs.getString("OrganizationName");
}
} catch (Exception E) {
System.err.println("Organization.java - deleteRecord - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
String sql = "Delete from tblOrganization where PKOrganization = " + PKOrganization;
boolean bIsDeleted = false;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
int iSuccess = st.executeUpdate(sql);
if (iSuccess != 0) bIsDeleted = true;
} catch (Exception E) {
System.err.println("Organization.java - deleteRecord - " + E);
} finally {
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
sDetail = detail.getUserDetail(PKUser);
ev.addRecord("Delete", itemName, OldName, sDetail[2], sDetail[11], sDetail[10]);
return bIsDeleted;
}
/* Method Name : isConsulting
* Checks whether login organisation is a Consulting Company
* @param sOrgName
* @param orgCode
* @author Mark Oei
* @since v.1.3.12.63 (09 Mar 2010)
*/
public boolean isConsulting(String orgName) {
String sOrgName = "";
orgName = "\'" + orgName + "\'";
String querySql = "SELECT * FROM tblConsultingCompany WHERE CompanyName = " + orgName;
// Change to disable print statement. Used for debugging only
// Mark Oei 19 Mar 2010
// System.out.println("testing " + orgName);
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(querySql);
if (rs.next()) sOrgName = rs.getString("CompanyName");
} catch (Exception E) {
System.err.println("Organization.java - isConsulting - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
// Change to disable print statement. Used for debugging only
// Mark Oei 19 Mar 2010
// System.out.println("testing " + sOrgName);
if ((sOrgName == null) || (sOrgName == "")) return false;
else return true;
} // End of isConsulting
/** Get Organisation ID by User email */
public int getOrgIDbyEmail(String UserEmail) throws SQLException, Exception {
String query = "Select COUNT(*) as TotRecord from tblEmail";
int count = 0;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
count = rs.getInt(1);
}
} catch (Exception E) {
System.err.println("Organization.java - editRecord - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return count;
}
public String getOrganisationName(int iFKOrg) {
String sOrgName = "";
String querySql = "SELECT * FROM tblOrganization WHERE PKOrganization = " + iFKOrg;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(querySql);
if (rs.next()) sOrgName = rs.getString("OrganizationName");
} catch (Exception E) {
System.err.println("Organization.java - getOrganizationName - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return sOrgName;
}
/**
* Get Company ID by OrganisationID
*
* @param OrgID
* @return PKCompany
* @throws SQLException
* @throws Exception
*/
public int getCompanyID(int OrgID) throws SQLException, Exception {
String query = "Select FKCompanyID from tblOrganization WHERE PKOrganization = " + OrgID;
/*db.openDB();
Statement stmt = db.con.createStatement();
ResultSet rs = stmt.executeQuery(query);
if(rs.next())
return rs.getInt(1);*/
int iCompanyID = 0;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
iCompanyID = rs.getInt(1);
}
} catch (Exception E) {
System.err.println("Organization.java - getCompanyID - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return iCompanyID;
}
/**
* Get organisation's name sequence
*
* @param iOrgID
* @return
* @throws SQLException
* @throws Exception
* @author Maruli
*/
public int getNameSeq(int iOrgID) throws SQLException, Exception {
String query = "SELECT NameSequence FROM tblOrganization WHERE PKOrganization =" + iOrgID;
int iNameSeqe = 0;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
iNameSeqe = rs.getInt(1);
}
} catch (Exception E) {
System.err.println("Organization.java - getNameSeq - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return iNameSeqe;
}
/**
* Get organisation's nomination rater status
*
* @param iOrgID
* @return
* @throws SQLException
* @throws Exception
* @author Desmond
*/
public boolean getNomRater(int iOrgID) throws SQLException, Exception {
String query = "SELECT NominationModule FROM tblOrganization WHERE PKOrganization =" + iOrgID;
boolean iNomRater = true;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
iNomRater = rs.getBoolean(1);
}
} catch (Exception E) {
System.err.println("Organization.java - getNomRater - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return iNomRater;
} // End getNomRater()
private void ListValueChanged(
javax.swing.event.ListSelectionEvent evt) { // GEN-FIRST:event_ListValueChanged
// TODO add your handling code here:
// String part=partno.getText();
try {
String sql =
"SELECT TYPE,ITEM_NAME,QUANTITY,MRP FROM MOTORS WHERE ITEM_NAME='"
+ List.getSelectedValue()
+ "'";
Class.forName("com.mysql.jdbc.Driver");
Connection con =
(Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/bharatmotors", "root", "");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
partno.setText(rs.getString("TYPE"));
name.setText(rs.getString("ITEM_NAME"));
qty.setText(rs.getString("QUANTITY"));
rate.setText(rs.getString("MRP"));
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e.toString());
}
} // GEN-LAST:event_ListValueChanged
private void jButton3ActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton3ActionPerformed
try {
Connection con =
(Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/bharatmotors", "root", "");
Statement stmt = con.createStatement();
ResultSet resultSet = stmt.executeQuery("select Bill_No from bill;");
String productCode = null;
while (resultSet.next()) {
productCode = resultSet.getString("Bill_No");
}
int pc = Integer.parseInt(productCode);
System.out.println(pc);
no.setText((String.valueOf(++pc)));
// TODO add your handling code here:
String part = partno.getText();
String itemname = name.getText();
String qty1 = qty.getText();
String rate1 = rate.getText();
// String amo=amount.getText();
String noo = no.getText();
no1 = Integer.parseInt(noo);
cname = custname.getText();
bill1 = bill.getText();
addr1 = addr.getText();
String ta = tax.getText();
int a = Integer.parseInt(qty1);
int b = Integer.parseInt(rate1);
int c = a * b;
String str = Integer.toString(c);
amount.setText(str);
String str1 = amount.getText();
/* String sql1="select Quantity from lubricants";
String sql2="UPDATE LUBRICANTS SET QUANTITY=QUANTITY-qty1 WHERE PART_NO='"+partno+"'";
Statement stmt1=con.createStatement();
ResultSet rs1= stmt1.executeQuery(sql1);
String ch=rs1.getString("QUANTITY");
int q=Integer.parseInt(qty1);
int r=Integer.parseInt(ch);
if(q >r)
{
JOptionPane.showMessageDialog(this,"STOCK UNAVAILABLE");
}
else
{
stmt1.executeUpdate(sql2);
}*/
} catch (SQLException ex) {
Logger.getLogger(Billspare.class.getName()).log(Level.SEVERE, null, ex);
}
} // GEN-LAST:event_jButton3ActionPerformed
public votblOrganization getAllOrganizations(int iFKCompanyID, int iOrganisationID) {
Vector v = new Vector();
Connection con = null;
Statement st = null;
ResultSet rs = null;
votblOrganization vo = new votblOrganization();
String sql = "";
if (iOrganisationID != 0) {
sql = "SELECT * FROM tblConsultingCompany a, tblOrganization b";
sql =
sql
+ " WHERE a.CompanyID = b.FKCompanyID AND a.CompanyID = "
+ iFKCompanyID
+ " AND b.PKOrganization ="
+ iOrganisationID;
} else {
sql = "SELECT * FROM tblConsultingCompany WHERE CompanyID = " + iFKCompanyID;
}
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(sql);
if (rs.next()) {
// 27 May 2008 by Hemilda - cause iOrganisationID == 0 the query only retrieve
// tblConsultingCompany column
if (iOrganisationID != 0) {
vo.setEmailNom(rs.getString("EmailNom"));
vo.setEmailNomRemind(rs.getString("EmailNomRemind"));
vo.setEmailPart(rs.getString("EmailPart"));
vo.setEmailPartRemind(rs.getString("EmailPartRemind"));
vo.setExtraModule(rs.getInt("ExtraModule"));
vo.setFKCompanyID(rs.getInt("FKCompanyID"));
vo.setNameSequence(rs.getInt("NameSequence"));
vo.setOrganizationCode(rs.getString("OrganizationCode"));
vo.setOrganizationLogo(rs.getString("OrganizationLogo"));
vo.setOrganizationName(rs.getString("OrganizationName"));
vo.setPKOrganization(rs.getInt("PKOrganization"));
}
vo.setCompanyName(rs.getString("CompanyName"));
}
} catch (SQLException SE) {
System.err.println("Organization.java - getAllOrganizations - " + SE.getMessage());
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return vo;
}
public static void ProcessCustomerPayment(Connection conn) {
// This function updates is_paid so billing staff can keep track of the payments customers have
// made
int c_id = BooksAThousand.getIntFromShell("Enter customer ID: ");
try {
Statement statement =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs =
statement.executeQuery(
"select customer_id, isbn, quantity, price, customer_order_date, is_paid"
+ " from customer_order where customer_id="
+ c_id
+ " and is_paid='N'");
if (rs.next()) {
System.out.println("Outstanding payments:");
System.out.printf(
"Order %-10s %-5s %-5s %-10s %-10s\n", "ISBN", "Quant", "Price", "Total", "Date");
Date date;
int i = 0;
SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy hh:mm");
do {
i++;
date = rs.getDate(5);
System.out.printf(
"%-5s: %-10s %-5d %-5d %-10d %-10s \n",
i,
rs.getInt(2),
rs.getInt(3),
rs.getInt(4),
rs.getInt(3) * rs.getInt(4),
sdf.format(date));
} while (rs.next());
int order = BooksAThousand.getIntFromShell("Which order to mark as paid: ");
rs.absolute(i);
rs.updateString(6, "Y");
rs.updateRow();
System.out.println("Update successful.");
} else {
System.out.println("This user has no unpaid orders");
}
} catch (Throwable e) {
e.printStackTrace();
}
}
// @karen not completed
public Vector getRecord_Sorted(String sSQL, int type) {
Vector v = new Vector();
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
// int SortType = getSortType();
sSQL = sSQL + " ORDER BY ";
if (type == 1) {
if (SortType == 1) sSQL = sSQL + "CompanyName";
else if (SortType == 2) sSQL = sSQL + "CompanyDesc";
if (Toggle[SortType - 1] == 1) sSQL = sSQL + " DESC";
} else {
if (SortType_org == 1) sSQL = sSQL + "OrganizationName";
else if (SortType_org == 2) sSQL = sSQL + "OrganizationCode";
else if (SortType_org == 3) sSQL = sSQL + "NameSequence";
if (Toggle_org[SortType_org - 1] == 1) sSQL = sSQL + " DESC";
}
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(sSQL);
while (rs.next()) {
votblConsultingCompany vo = new votblConsultingCompany();
vo.setCompanyDesc(rs.getString("CompanyDesc"));
vo.setCompanyName(rs.getString("CompanyName"));
vo.setCompanyID(rs.getInt("CompanyID"));
v.add(vo);
}
} catch (SQLException SE) {
System.err.println("Organization.java - getRecord_Sorted - " + SE.getMessage());
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return v;
}
public String getId(Connection con) throws Exception {
String ret = null;
String sql = "select seq_id.nextval from dual";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
if (rs.next()) {
ret = sdf2.format(new java.util.Date()) + rs.getString(1);
} else {
throw new Exception("fail get id from oracle seq_id");
}
rs.close();
st.close();
return ret;
}
private void jButton4ActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton4ActionPerformed
// TODO add your handling code here:
String wash = chrg.getText();
cname = custname.getText();
addr1 = addr.getText();
total.setText(String.valueOf(tamount));
String a = lcharge.getText();
String b = total.getText();
String j = la.getText();
String c = con.getText();
int d = Integer.parseInt(b);
int e = Integer.parseInt(a);
int g = Integer.parseInt(wash);
int h = Integer.parseInt(c);
int i = Integer.parseInt(j);
int f = d + e + g + h + i;
toamnt.setText(String.valueOf(f));
String sql =
"insert into bill(Bill_No,Bill_Date,Cust_Name,Cust_Addr,P_Details,Amount) values('"
+ no1
+ "','"
+ bill1
+ "','"
+ cname
+ "','"
+ addr1
+ "','"
+ pd
+ "','"
+ toamnt
+ "')";
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con =
(Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/bharatmotors", "root", "");
Statement stmt = con.createStatement();
stmt.executeUpdate(sql);
} catch (Exception e2) {
JOptionPane.showMessageDialog(this, e2.getMessage());
}
} // GEN-LAST:event_jButton4ActionPerformed
public static void GenerateCustomerBill(Connection conn) {
// We intended to produce the bills over certain time periods but
// we will offer an option to check just the last 3 months.
try {
int c_id = BooksAThousand.getIntFromShell("Enter customer ID: ");
Statement statement = conn.createStatement();
ResultSet rs =
statement.executeQuery("select address, name from customer where customer_id = " + c_id);
rs.next();
String address = rs.getString(1);
String name = rs.getString(2);
rs =
statement.executeQuery(
"select customer_id, isbn, quantity, price, customer_order_date"
+ " from customer_order where customer_id="
+ c_id
+ " and is_paid='N'");
float total = 0;
if (rs.next()) {
Date date;
SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy hh:mm");
System.out.println("\n" + address);
System.out.println("\nDear " + name + ", \nBelow are your unpaid orders. Pay up.\n");
System.out.printf(
"%-10s %-20s %-5s %-8s %-11s\n", "ISBN", "Date", "Quant", "Price", "Total");
do {
total += rs.getInt(3) * rs.getFloat(4);
date = rs.getDate(5);
System.out.printf(
"%-10s %-20s %-5d $%-7.2f $%-10.2f \n",
rs.getInt(2),
sdf.format(date),
rs.getInt(3),
rs.getFloat(4),
rs.getInt(3) * rs.getFloat(4));
} while (rs.next());
System.out.printf("Total: $%.2f\n", total);
} else {
System.out.println("This user has no unpaid orders");
}
} catch (Throwable e) {
e.printStackTrace();
}
}
/**
* Get Email Template by SurveyID
*
* @param SurveyID int SurveyID
* @param int iOpt Template Option: 0=Nom Email, 1=Nom Reminder, 2=Participant, 3=Participant
* Reminder, 4=SELF
* @return EmailTemplate
*/
public String getEmailTemplate_SurvID(int SurveyID, int iOpt) throws SQLException, Exception {
String sTemplate = "EmailNom";
if (iOpt == 1) sTemplate = "EmailNomRemind";
else if (iOpt == 2) sTemplate = "EmailPart";
else if (iOpt == 3) sTemplate = "EmailPartRemind";
else if (iOpt == 4) sTemplate = "EmailIndividualReport";
else if (iOpt == 5) sTemplate = "EmailSelf";
String query = "SELECT " + sTemplate + " FROM tblOrganization INNER JOIN tblSurvey ON ";
query = query + "tblOrganization.PKOrganization = tblSurvey.FKOrganization ";
query = query + "WHERE (tblSurvey.SurveyID = " + SurveyID + ")";
String sOrgTemplate = "";
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
sOrgTemplate = rs.getString(1);
}
} catch (Exception E) {
System.err.println("Organization.java - getEmailTemplate_SurvID - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
// Edit by Roger 27 June 2008
// Check for null value
if (sOrgTemplate == null) {
sOrgTemplate = "";
}
return sOrgTemplate;
}
/**
* Get Organization
*
* @param iFKCompanyID
* @return
* @author James
*/
public votblOrganization getOrganization(int iOrgID) {
votblOrganization vo = new votblOrganization();
Connection con = null;
Statement st = null;
ResultSet rs = null;
String command = "SELECT * FROM tblOrganization WHERE PKOrganization= " + iOrgID;
try {
con = ConnectionBean.getConnection();
System.out.println("con:" + con);
st = con.createStatement();
rs = st.executeQuery(command);
if (rs.next()) {
vo.setEmailNom(rs.getString("EmailNom"));
vo.setEmailNomRemind(rs.getString("EmailNomRemind"));
vo.setEmailPart(rs.getString("EmailPart"));
vo.setEmailPartRemind(rs.getString("EmailPartRemind"));
vo.setExtraModule(rs.getInt("ExtraModule"));
vo.setFKCompanyID(rs.getInt("FKCompanyID"));
vo.setNameSequence(rs.getInt("NameSequence"));
vo.setOrganizationCode(rs.getString("OrganizationCode"));
vo.setOrganizationLogo(rs.getString("OrganizationLogo"));
vo.setOrganizationName(rs.getString("OrganizationName"));
vo.setPKOrganization(rs.getInt("PKOrganization"));
// Added by DeZ, 18/06/08, to add function to enable/disable Nominate Rater
vo.setNomRater(rs.getBoolean("NominationModule"));
}
} catch (SQLException SE) {
System.err.println("Organization.java - getOrganization - " + SE.getMessage());
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return vo;
}
public void FillList() {
try {
String sql1 = "select * from motors";
Class.forName("com.mysql.jdbc.Driver");
Connection con =
(Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/bharatmotors", "root", "");
Statement stmt = con.createStatement();
ResultSet rs1 = stmt.executeQuery(sql1);
DefaultListModel DLM = new DefaultListModel();
while (rs1.next()) {
DLM.addElement(rs1.getString(3));
// DLM.addElement(rs1.getString(2));
}
List.setModel(DLM);
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "e.getString()");
}
}
private void jButton1ActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton1ActionPerformed
// TODO add your handling code here:
String part = partno.getText();
String itemname = name.getText();
String qty1 = qty.getText();
String rate1 = rate.getText();
String no1 = no.getText();
String cname = custname.getText();
String bill1 = bill.getText();
String addr1 = addr.getText();
String ta = tax.getText();
String str1 = amount.getText();
DefaultTableModel model = (DefaultTableModel) Table.getModel();
model.addRow(
new Object[] {
(String.valueOf(++i)),
partno.getText(),
name.getText(),
tax.getText(),
qty.getText(),
rate.getText(),
amount.getText()
});
pd = pd + "," + "(" + part + "," + itemname + "," + qty1 + ")";
tamount = tamount + Integer.parseInt(str1);
try {
Connection con =
(Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306/bharatmotors", "root", "");
Statement stmt2 = con.createStatement();
stmt2.executeUpdate(
"UPDATE MOTORS SET QUANTITY=QUANTITY-'" + qty1 + "' WHERE ITEM_NAME='" + part + "'");
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e.toString());
}
} // GEN-LAST:event_jButton1ActionPerformed
/**
* Get Company ID by Organisation Code
*
* @param sOrgCode
* @return PKOrganisation
* @throws SQLException
* @throws Exception
*/
public int getPKOrg(String sOrgCode) throws SQLException, Exception {
String query =
"SELECT PKOrganization FROM tblOrganization WHERE OrganizationCode = '" + sOrgCode + "'";
/*db.openDB();
Statement stmt = db.con.createStatement();
ResultSet rs = stmt.executeQuery(query);
if(rs.next())
return rs.getInt(1);*/
Connection con = null;
Statement st = null;
ResultSet rs = null;
int iPKOrganization = 0;
try {
con = ConnectionBean.getConnection();
System.out.println(con + " connection null");
st = con.createStatement();
System.out.println(st + "st");
rs = st.executeQuery(query);
if (rs.next()) {
iPKOrganization = rs.getInt(1);
}
} catch (Exception E) {
System.err.println("The ERROR IS HERE ->Organization.java - getPKOrg - " + E);
E.printStackTrace();
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return iPKOrganization;
}
/** Check Organization exist. */
public int checkOrgExist(String OrganizationCode, String OrganizationName, int FKCompanyID)
throws SQLException, Exception {
int iPKOrganization = 0;
// Changed by Ha 09/06/08 two org are the same if they have the same name
String command =
"SELECT * FROM tblOrganization WHERE OrganizationName = '" + OrganizationName + "'";
/*ResultSet rs1 = db.getRecord(command);
if(rs1.next())
iPKOrganization = rs1.getInt("PKOrganization");
rs1.close();*/
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(command);
if (rs.next()) {
iPKOrganization = rs.getInt("PKOrganization");
}
} catch (Exception E) {
System.err.println("Organization.java - checkOrgExist - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return iPKOrganization;
}
// Changed by DeZ, 18/06/08, to add function to enable/disable Nominate Rater
public boolean editRecord(
int PKOrganization,
String OrganizationCode,
String OrganizationName,
int FKCompanyID,
int NameSequence,
int PKUser,
String nomRater)
throws SQLException, Exception {
String OldName = "";
String command = "SELECT * FROM tblOrganization WHERE PKOrganization = " + PKOrganization;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(command);
if (rs.next()) {
OldName = rs.getString("OrganizationName");
}
rs.close();
rs = null;
} catch (Exception E) {
System.err.println("Organization.java - editRecord - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
// Changed by DeZ, 18/06/08, to add function to enable/disable Nominate Rater
String sql =
"UPDATE tblOrganization SET OrganizationCode = '"
+ OrganizationCode
+ "', OrganizationName = '"
+ OrganizationName
+ "', FKCompanyID = "
+ FKCompanyID
+ ", NameSequence = "
+ NameSequence
+ ", NominationModule = '"
+ Boolean.parseBoolean(nomRater)
+ "'";
sql = sql + " WHERE PKOrganization = " + PKOrganization;
boolean bIsUpdated = false;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
int iSuccess = st.executeUpdate(sql);
if (iSuccess != 0) bIsUpdated = true;
} catch (Exception E) {
System.err.println("Organization.java - editRecord - " + E);
} finally {
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
sDetail = detail.getUserDetail(PKUser);
try {
ev.addRecord(
"Update",
itemName,
"(" + OldName + ") - (" + OrganizationName + ")",
sDetail[2],
sDetail[11],
sDetail[10]);
} catch (SQLException SE) {
}
return bIsUpdated;
}
/**
* Takes a vector full of property lists and generates a report.
*
* @param args Command line arguments. args[0] should be the config filename.
*/
public static void main(String[] args) {
// Load the database properties from properties file
Properties properties = new Properties();
// Load config file
String configFile = null;
if (args.length > 0) configFile = args[0];
try {
if (configFile == null) {
System.out.println("Database config file not set.");
return;
} else properties.load(new FileInputStream(configFile));
} catch (IOException e) {
System.out.println("Error opening config file.");
}
String url = properties.getProperty("databaseUrl");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
String dir = System.getProperty("user.dir"); // Current working directory
Connection con = null;
// Try to open file containing javac output
String output = "";
try {
BufferedReader outputReader = new BufferedReader(new FileReader(dir + "/CompileOut.txt"));
while (outputReader.ready()) output += outputReader.readLine() + '\n';
// Close file
outputReader.close();
} catch (FileNotFoundException e) {
System.out.println("Error opening compilation output file.");
return;
} catch (IOException e) {
System.out.println("I/O Exception Occured.");
return;
}
boolean hasDriver = false;
// Create class for the driver
try {
Class.forName("com.mysql.jdbc.Driver");
hasDriver = true;
} catch (Exception e) {
System.out.println("Failed to load MySQL JDBC driver class.");
}
// Create connection to database if the driver was found
if (hasDriver) {
try {
con = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
System.out.println("Couldn't get connection!");
}
}
// Check that a connection was made
if (con != null) {
long userEventId = -1;
// Store results from the report into the database
try {
BufferedReader rd =
new BufferedReader(
new FileReader(dir + "/userId.txt")); // Read userId.txt to get userId
String userId = rd.readLine(); // Store userId from text file
rd.close();
// Insert the report into the table and get the auto_increment id for it
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO userEvents (userId) VALUES ('" + userId + "')");
ResultSet result = stmt.getGeneratedKeys();
result.next();
userEventId = result.getLong(1);
// Close the statement
stmt.close();
// Prepare statement for adding the compilation error to the userEvent
PreparedStatement compErrorPrepStmt =
con.prepareStatement(
"INSERT INTO userEventCompilationErrors(userEventId, output) VALUES (?, ?)");
// Insert userEventId and docletId into the database
compErrorPrepStmt.setLong(1, userEventId);
compErrorPrepStmt.setString(2, output);
compErrorPrepStmt.executeUpdate();
// Close the prepare statements
compErrorPrepStmt.close();
} catch (Exception e) {
System.out.println("Exception Occurred");
System.out.println(e);
}
// Store the java files for the report
try {
// Prepare statement for storing files
PreparedStatement filePrepStmt =
con.prepareStatement(
"INSERT INTO files(userEventId, filename, contents) VALUES ("
+ userEventId
+ ", ?, ?)");
// Get the list of files from source.txt
BufferedReader rd =
new BufferedReader(
new FileReader(dir + "/source.txt")); // Read userId.txt to get userId
while (rd.ready()) {
String filename = rd.readLine(); // Store userId from text file
// Remove the "src/" from the beginning to get the real file name
String realname = filename.substring(4);
filePrepStmt.setString(1, realname);
// Read in the contents of the files
String contents = "";
File javaFile = new File(dir + "/" + filename);
int length = (int) javaFile.length();
// Add parameter for file contents to the prepared statement and execute it
filePrepStmt.setCharacterStream(2, new BufferedReader(new FileReader(javaFile)), length);
filePrepStmt.executeUpdate();
}
rd.close();
} catch (IOException e) {
System.err.println("I/O Exception Occured.");
} catch (SQLException e) {
System.err.println("SQL Exception Occured.");
}
}
}
// Changed by DeZ, 18/06/08, to add function to enable/disable Nominate Rater
public boolean addRecord(
String OrganizationCode,
String OrganizationName,
int FKCompanyID,
int NameSequence,
int PKUser,
String nomRater)
throws SQLException, Exception {
Connection con = null;
Statement st = null;
boolean bIsAdded = false;
// Changed by DeZ, 18/06/08, to add function to enable/disable Nominate Rater
String sql =
"INSERT INTO tblOrganization (OrganizationCode, OrganizationName, FKCompanyID, NameSequence, NominationModule)";
sql =
sql
+ " VALUES ('"
+ OrganizationCode
+ "', '"
+ OrganizationName
+ "', "
+ FKCompanyID
+ ", "
+ NameSequence
+ ", '"
+ Boolean.parseBoolean(nomRater)
+ "')";
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
int iSuccess = st.executeUpdate(sql);
System.out.println(iSuccess);
if (iSuccess != 0) bIsAdded = true;
} catch (Exception E) {
System.err.println("Organization.java - AddRecord - " + E);
} finally {
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
/*
* Codes commented to prevent addition of Admin account when a new
* organisation is added
* Mark Oei 09 Mar 2010
*/
// System.out.println("1. Add Organization");
//
// // add default under the organization.
// String defaultName = "NA";
// int FKOrganization = checkOrgExist(OrganizationCode, OrganizationName, FKCompanyID);
// System.out.println("testing " + FKOrganization);
// System.out.println("2. Check Organization Exist");
// if(FKOrganization != 0) {
// div.addRecord(defaultName, FKOrganization, PKUser);
// System.out.println("3. Add Division");
//
// dept.addRecord(defaultName, FKOrganization, PKUser);
// System.out.println("4. Add Department");
//
// G.addRecord(defaultName, FKOrganization, PKUser);
// System.out.println("5. Add Group");
//
// int FKDivision = div.checkDivExist(defaultName, FKOrganization);
// int FKDepartment = dept.checkDeptExist(defaultName, FKOrganization);
// int FKGroup = G.checkGroupExist(defaultName, FKOrganization);
//
// /*System.out.println("FKDivision = " + FKDivision);
// System.out.println("FKDepartment = " + FKDepartment);
// System.out.println("FKGroup = " + FKGroup);*/
//
// // Link newly created Department with Division
// dept.linkDepartment(FKDivision, FKDepartment);
// // Link newly created Group with Department
// G.linkGroup(FKDepartment, FKGroup);
//
// Date timeStamp = new java.util.Date();
// SimpleDateFormat dFormat = new SimpleDateFormat("ddMMyyHHmmss");
// String temp = dFormat.format(timeStamp);
// String loginName = OrganizationCode + "admin";
// String password = OrganizationCode + temp;
// int userType = 6;
//
// U.addRecord(FKDepartment, FKDivision, userType, "Admin", "Admin",
// loginName, "NA", "NA", FKGroup, password, 1, FKCompanyID, FKOrganization, "NA",
// PKUser);
//
// System.out.println("6. Add User");
// int userExist = U.checkUserExist(FKDepartment, FKDivision, userType, "Admin", "Admin",
// loginName, "NA", "NA", FKGroup, password, 1, FKCompanyID, FKOrganization);
//
// System.out.println("FKDivision = " + FKDivision + ", FKDepartment = " + FKDepartment + ",
// FKGroup = " + FKGroup + " and User Exist = " + userExist);
//
// if(userExist != 0) {
// try {
// U.insertRelation(userExist, userExist, 0);
// }catch(SQLException SE) {System.out.println(SE.getMessage());}
//
// String content = template.ForgotPass_temp(loginName, password);
// String email = "*****@*****.**";
// //Edited By Roger 13 June 2008
// Email.sendMail(server.getAdminEmail(), email, "New Admin Assignment for " +
// OrganizationName, content, FKOrganization);
// }
//
// System.out.println("8. Add User Relation");
// }
//
// sDetail = detail.getUserDetail(PKUser);
// ev.addRecord("Insert", itemName, OrganizationName, sDetail[2], sDetail[11], sDetail[10]);
return bIsAdded;
} // End Method for addRecord
public Vector getAllOrganizations(int iFKCompanyID) {
Vector v = new Vector();
Connection con = null;
Statement st = null;
ResultSet rs = null;
/*
* Change(s) : Changed the SQL statement to get the company name as well
* Reason(s) : Able to set the selected company name when company selection is changed in OrganizationList.jsp
* Updated By: Gwen Oh
* Updated On: 26 Sep 2011
*/
// String sSQL="SELECT * FROM tblOrganization WHERE FKCompanyID= "+ iFKCompanyID;
String sSQL =
"SELECT tblOrganization.*, tblConsultingCompany.CompanyName FROM "
+ "tblOrganization INNER JOIN tblConsultingCompany ON tblOrganization.FKCompanyID = tblConsultingCompany.CompanyID "
+ "WHERE tblOrganization.FKCompanyID="
+ iFKCompanyID;
try {
/*
* Re-edited by Eric Lu 15-May-08
*
* Added sort and toggle functionality for getting organizations
*/
sSQL = sSQL + " ORDER BY ";
if (SortType_org == 1) sSQL = sSQL + "OrganizationName";
else if (SortType_org == 2) sSQL = sSQL + "OrganizationCode";
else if (SortType_org == 3) sSQL = sSQL + "NameSequence";
if (Toggle_org[SortType_org - 1] == 1) sSQL = sSQL + " DESC";
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(sSQL);
while (rs.next()) {
votblOrganization vo = new votblOrganization();
vo.setEmailNom(rs.getString("EmailNom"));
vo.setEmailNomRemind(rs.getString("EmailNomRemind"));
vo.setEmailPart(rs.getString("EmailPart"));
vo.setEmailPartRemind(rs.getString("EmailPartRemind"));
vo.setExtraModule(rs.getInt("ExtraModule"));
vo.setFKCompanyID(rs.getInt("FKCompanyID"));
vo.setNameSequence(rs.getInt("NameSequence"));
vo.setOrganizationCode(rs.getString("OrganizationCode"));
vo.setOrganizationLogo(rs.getString("OrganizationLogo"));
vo.setOrganizationName(rs.getString("OrganizationName"));
vo.setPKOrganization(rs.getInt("PKOrganization"));
// Gwen Oh - 26/09/2011: Set the company name
vo.setCompanyName(rs.getString("CompanyName"));
v.add(vo);
}
} catch (SQLException SE) {
System.err.println("Organization.java - getAllOrganizations - " + SE.getMessage());
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return v;
}
/*
* Add a new record to the Organization table,
* creates an admin account when SA creates a new consulting company
* @param OrganizationCode (based on Company Name)
* @param OrganizationName (based on Company Description)
* @param FKCompanyID
* @param NameSequence
* @param PKUser
* @param nomRater
* @throws SQLException
* @throws Exception
* @author: Mark Oei
* @since v.1.3.12.63 09 Mar 2010
*/
public boolean addOrganisationByCons(
String OrganizationCode,
String OrganizationName,
int FKCompanyID,
int NameSequence,
int PKUser,
String nomRater)
throws SQLException, Exception {
Connection con = null;
Statement st = null;
boolean bIsAdded = false;
String sql =
"INSERT INTO tblOrganization (OrganizationCode, OrganizationName, FKCompanyID, NameSequence, NominationModule)";
sql =
sql
+ " VALUES ('"
+ OrganizationCode
+ "', '"
+ OrganizationName
+ "', "
+ FKCompanyID
+ ", "
+ NameSequence
+ ", '"
+ Boolean.parseBoolean(nomRater)
+ "')";
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
int iSuccess = st.executeUpdate(sql);
System.out.println(iSuccess);
if (iSuccess != 0) bIsAdded = true;
} catch (Exception E) {
System.err.println("Organization.java - AddRecord - " + E);
} finally {
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
System.out.println("1. Add Organization");
// add default under the organization.
String defaultName = "NA";
int FKOrganization = checkOrgExist(OrganizationCode, OrganizationName, FKCompanyID);
// Change to disable print statement. Used for debugging only
// Mark Oei 19 Mar 2010
// System.out.println("testing " + FKOrganization);
System.out.println("2. Check Organization Exist");
if (FKOrganization != 0) {
// Add Division
div.addRecord(defaultName, FKOrganization, PKUser);
System.out.println("3. Add Division");
// Add Department
dept.addRecord(defaultName, FKOrganization, PKUser);
System.out.println("4. Add Department");
// Add Group
G.addRecord(defaultName, FKOrganization, PKUser);
System.out.println("5. Add Group");
// Check whether exists
int FKDivision = div.checkDivExist(defaultName, FKOrganization);
int FKDepartment = dept.checkDeptExist(defaultName, FKOrganization);
int FKGroup = G.checkGroupExist(defaultName, FKOrganization);
// Create links
dept.linkDepartment(FKDivision, FKDepartment);
G.linkGroup(FKDepartment, FKGroup);
// Establish new admin account and password
Date timeStamp = new java.util.Date();
SimpleDateFormat dFormat = new SimpleDateFormat("ddMMyyHHmmss");
String temp = dFormat.format(timeStamp);
String loginName = OrganizationCode + "admin";
String password = OrganizationCode + temp;
int userType = 6;
// Insert record into database
U.addRecord(
FKDepartment,
FKDivision,
userType,
"Admin",
"Admin",
loginName,
"NA",
"NA",
FKGroup,
password,
1,
FKCompanyID,
FKOrganization,
"NA",
PKUser);
System.out.println("6. Add User");
int userExist =
U.checkUserExist(
FKDepartment,
FKDivision,
userType,
"Admin",
"Admin",
loginName,
"NA",
"NA",
FKGroup,
password,
1,
FKCompanyID,
FKOrganization);
System.out.println(
"FKDivision = "
+ FKDivision
+ ", FKDepartment = "
+ FKDepartment
+ ", FKGroup = "
+ FKGroup
+ " and User Exist = "
+ userExist);
if (userExist != 0) {
try {
U.insertRelation(userExist, userExist, 0);
} catch (SQLException SE) {
System.out.println(SE.getMessage());
}
// Send email notification
String content = template.ForgotPass_temp(loginName, password);
String email = "*****@*****.**";
// Edited By Roger 13 June 2008
Email.sendMail(
server.getAdminEmail(),
email,
"New Admin Assignment for " + OrganizationName,
content,
FKOrganization);
}
System.out.println("8. Add User Relation");
}
sDetail = detail.getUserDetail(PKUser);
ev.addRecord("Insert", itemName, OrganizationName, sDetail[2], sDetail[11], sDetail[10]);
return bIsAdded;
} // End Method for addOrganisationByCons
/** Set the logo parh. */
public boolean editLogo(int PKOrganization, String path, int PKUser)
throws SQLException, Exception {
String OldName = "";
String command = "SELECT * FROM tblOrganization WHERE PKOrganization = " + PKOrganization;
Connection con = null;
Statement st = null;
ResultSet rs = null;
/*ResultSet rs1 = db.getRecord(command);
if(rs1.next())
OldName = rs1.getString("OrganizationLogo");
rs1.close();
db.openDB();*/
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(command);
if (rs.next()) {
OldName = rs.getString("OrganizationLogo");
}
} catch (Exception E) {
System.err.println("Organization.java - editLogo - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
String sql =
"UPDATE tblOrganization SET OrganizationLogo = '"
+ path
+ "' WHERE PKOrganization = "
+ PKOrganization;
boolean bIsUpdated = false;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
int iSuccess = st.executeUpdate(sql);
if (iSuccess != 0) bIsUpdated = true;
} catch (Exception E) {
System.err.println("Organization.java - editLogo- " + E);
} finally {
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
sDetail = detail.getUserDetail(PKUser);
ev.addRecord(
"Update",
itemName,
"(" + OldName + ") - (" + path + ")",
sDetail[2],
sDetail[11],
sDetail[10]);
return bIsUpdated;
}
