/** 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;
  }
Exemple #13
0
  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;
  }