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;
}
// @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;
}
/**
* 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 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
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
ResultSet rs = null;
try {
// SET UP Context environment, to look for Data Pooling Resource
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB");
Connection dbcon = ds.getConnection();
// ########### SEARCH INPUT PARAMETERS, EXECUTE search
// #####################################################
Vector<String> params = new Vector<String>();
params.add(request.getParameter("fname"));
params.add(request.getParameter("lname"));
params.add(request.getParameter("title"));
params.add(request.getParameter("year"));
params.add(request.getParameter("director"));
List<Movie> movies = new ArrayList<Movie>();
movies = SQLServices.getMovies(params.toArray(new String[params.size()]), dbcon);
// ########## SET DEFAULT SESSION() PARAMETERS ####################################
request.getSession().removeAttribute("movies");
request.getSession().removeAttribute("linkedListMovies");
request.getSession().removeAttribute("hasPaged");
request.getSession().setAttribute("hasPaged", "no");
request.getSession().setAttribute("movies", movies);
request.getSession().setAttribute("currentIndex", "0");
request.getSession().setAttribute("defaultN", "5");
// ########## IF MOVIES FROM SEARCH NON-EMPTY ###########################################
List<String> fields = Movie.fieldNames();
int count = 1;
if (!movies.isEmpty()) {
request.setAttribute("movies", movies);
for (String field : fields) {
request.setAttribute("f" + count++, field);
}
request.getRequestDispatcher("../movieList.jsp").forward(request, response);
} else {
out.println("<html><head><title>error</title></head>");
out.println("<body><h1>could not find any movies, try your search again.</h1>");
out.println("<p> we are terribly sorry, please go back. </p>");
out.println("<table border>");
out.println("</table>");
}
dbcon.close();
} catch (SQLException ex) {
while (ex != null) {
System.out.println("SQL Exception: " + ex.getMessage());
ex = ex.getNextException();
}
} catch (java.lang.Exception ex) {
out.println(
"<html>"
+ "<head><title>"
+ "moviedb: error"
+ "</title></head>\n<body>"
+ "<p>SQL error in doGet: "
+ ex.getMessage()
+ "</p></body></html>");
return;
}
out.close();
}
