/myproject/src/com/bp/pensionline/publishing/database/PublishingToolbarSQLHandler.java
Java | 3542 lines | 2649 code | 387 blank | 506 comment | 225 complexity | f69505f78083af3cec80e6a20f2c90cd MD5 | raw file
Large files files are truncated, but you can click here to view the full file
- package com.bp.pensionline.publishing.database;
- /*
- * This class handle all Database transaction for publishing actions
- */
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.HashSet;
- import java.util.Vector;
- import org.apache.commons.logging.Log;
- import org.opencms.main.CmsLog;
- import com.bp.pensionline.constants.Environment;
- import com.bp.pensionline.database.DBConnector;
- import com.bp.pensionline.publishing.dto.ReleasePackageDTO;
- import com.bp.pensionline.publishing.dto.ReleasePageDTO;
- import com.bp.pensionline.publishing.util.DateTimeUtil;
- public class PublishingToolbarSQLHandler
- {
- public static final Log LOG = CmsLog.getLog(PublishingToolbarSQLHandler.class);
-
-
- /**
- * Get page DTO object from URI
- * @param con
- * @param pageURI
- * @return
- */
- public static ReleasePageDTO getReleasePageByURI (String pageURI)
- {
- String getReleasePageSQL = "Select page_id, mode, status, bug_id, notes, last_update, self_edited from bp_release_pages " +
- "where page_uri = ?";
-
- ReleasePageDTO pageDTO = null;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- PreparedStatement pstm = con.prepareStatement(getReleasePageSQL);
- pstm.setString(1, pageURI);
- ResultSet rs = pstm.executeQuery();
- if (rs.next())
- {
- pageDTO = new ReleasePageDTO();
- pageDTO.setPageId(rs.getInt("page_id"));
- pageDTO.setPageURI(pageURI);
- pageDTO.setMode(rs.getString("mode"));
- pageDTO.setStatus(rs.getString("status"));
- pageDTO.setBugId(rs.getString("bug_id"));
- pageDTO.setNotes(rs.getString("notes"));
- pageDTO.setLastUpdate(rs.getLong("last_update"));
- pageDTO.setSelfEdited(rs.getString("self_edited"));
- }
-
- pstm.close();
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in getReleasePageByURI: " + sqle.toString()+"\n$SQL: "+getReleasePageSQL);
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return pageDTO;
- }
-
- /**
- * Get page DTO object from URI in bp_release_pages
- * @param con
- * @param pageURI
- * @return
- */
- public static ReleasePageDTO getReleaseChildPageByURI (String pageURI)
- {
- String getReleasePageSQL = "Select page_id, mode, last_update from bp_release_child_pages " +
- "where page_uri = '" + pageURI + "'";
-
- ReleasePageDTO pageDTO = null;
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- PreparedStatement pstm = con.prepareStatement(getReleasePageSQL);
- ResultSet rs = pstm.executeQuery();
- if (rs.next())
- {
- pageDTO = new ReleasePageDTO();
- pageDTO.setPageId(rs.getInt("page_id"));
- pageDTO.setPageURI(pageURI);
- pageDTO.setMode(rs.getString("mode"));
- pageDTO.setLastUpdate(rs.getLong("last_update"));
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in getReleaseChildPageByURI: " + sqle.toString());
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return pageDTO;
- }
-
- /**
- * Get all child page in bp_release_page_child of a parent
- * @param con
- * @param pageURI
- * @return
- */
- public static Vector<ReleasePageDTO> getChildPagesByParent (int parentId)
- {
- String getReleasePageSQL = "Select page_id, page_uri, mode, last_update " +
- "from bp_release_child_pages c, bp_release_childparent cp " +
- "where c.page_id = cp.child_id and cp.parent_id = '" + parentId + "'";
-
-
- Vector<ReleasePageDTO> childPageDTOs = new Vector<ReleasePageDTO>();
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
-
- PreparedStatement pstm = con.prepareStatement(getReleasePageSQL);
- ResultSet rs = pstm.executeQuery();
- while (rs.next())
- {
- ReleasePageDTO pageDTO = new ReleasePageDTO();
- pageDTO = new ReleasePageDTO();
- pageDTO.setPageId(rs.getInt("page_id"));
- pageDTO.setPageURI(rs.getString("page_uri"));
- pageDTO.setMode(rs.getString("mode"));
- pageDTO.setLastUpdate(rs.getLong("last_update"));
-
- childPageDTOs.add(pageDTO);
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in getReleaseChildPageByParent: " + sqle.toString());
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return childPageDTOs;
- }
-
- /**
- * Get page status from Id
- * @param con
- * @param pageURI
- * @return
- */
- public static String getReleasePageStatus (String pageURI)
- {
- String getPageStatusSQL = "Select status from bp_release_pages where page_uri = '" +
- pageURI + "'";
-
- String status = null;
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- PreparedStatement pstm = con.prepareStatement(getPageStatusSQL);
- ResultSet rs = pstm.executeQuery();
- if (rs.next())
- {
- status = rs.getString("status");
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in getReleasePageStatus " + sqle.toString());
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
- return status;
- }
-
- /**
- * Get page last update of page
- * @param con
- * @param pageURI
- * @return
- */
- public static long getReleasePageLastUpdate (int pageId)
- {
- String getPageStatusSQL = "Select last_update from bp_release_pages where page_id = '" +
- pageId + "'";
-
- long lastUpdate = -1;
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- PreparedStatement pstm = con.prepareStatement(getPageStatusSQL);
- ResultSet rs = pstm.executeQuery();
- if (rs.next())
- {
- lastUpdate = rs.getLong("last_update");
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in getReleasePageLastUpdate: " + sqle.toString());
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
- return lastUpdate;
- }
-
- /**
- * Update page's status for HTML page
- * @param con
- * @param pageURI
- * @return
- */
- public static boolean updateReleasePageStatus (int pageId, String status)
- {
- String updatePageStatusSQL = "Update bp_release_pages set status = ? " +
- "where page_id = '" + pageId + "' ";
-
- String updateImpactPageStatusSQL = null;
-
- if (status != null && status.equals("Editing"))
- {
- updatePageStatusSQL = "Update bp_release_pages set status = ? " +
- "where page_id = '" + pageId + "' and mode != 'Impacted'";
-
- updateImpactPageStatusSQL = "Update bp_release_pages set status = 'Edited' " +
- "where page_id = '" + pageId + "' and mode = 'Impacted'";
- }
-
- boolean updateOk = false;
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(updatePageStatusSQL);
- pstm.setString(1, status);
- pstm.executeUpdate();
-
- if (updateImpactPageStatusSQL != null)
- {
- pstm = con.prepareStatement(updateImpactPageStatusSQL);
- pstm.executeUpdate();
- }
-
- con.commit();
- con.setAutoCommit(true);
-
- updateOk = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in updateReleasePageStatus: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in updateReleasePageStatus rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return updateOk;
- }
-
- /**
- * Update page's status for HTML page
- * @param con
- * @param pageURI
- * @return
- */
- public static boolean updateReleasePageToUneditedState (int pageId, boolean isEdited, long lastUpdate)
- {
- String updatePageSelfEditedSQL = "Update bp_release_pages set self_edited = ?, last_update =? " +
- "where page_id = '" + pageId + "' ";
- String selfEdited = isEdited ? "Y" : "N";
-
- boolean updateOk = false;
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(updatePageSelfEditedSQL);
- pstm.setString(1, selfEdited);
- pstm.setLong(2, lastUpdate);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
-
- updateOk = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in updateReleasePageSelfEdited: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in updateReleasePageSelfEdited rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return updateOk;
- }
-
-
- /**
- * Update page's last_update and status for HTML page
- * @param con
- * @param pageURI
- * @return
- */
- public static boolean updateReleasePageStatus (int pageId, String status, long lastUpdate)
- {
- String updatePageLastUpdateSQL = "Update bp_release_pages set mode = 'Edited', status = ?, last_update = ?, self_edited = 'Y' " +
- "where page_id = '" + pageId + "' ";
-
- boolean updateOk = false;
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(updatePageLastUpdateSQL);
- pstm.setString(1, status);
- pstm.setLong(2, lastUpdate);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
-
- updateOk = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in updateReleasePageStatus: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in updateReleasePageStatus rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return updateOk;
- }
-
- /**
- * Update status for all child page
- * @param con
- * @param pageURI
- * @return
- */
- public static boolean updateAllReleaseChildPageInfo (int parentId, String status, String notes, String bugId)
- {
- String updateAllReleaseChildInfoSQL = "Update bp_release_child_pages set status = ?, bug_id = ?, notes = ?" +
- "where parent_id = '" + parentId + "' ";
-
- boolean updateOk = false;
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(updateAllReleaseChildInfoSQL);
- pstm.setString(1, status);
- pstm.setString(2, bugId);
- pstm.setString(3, notes);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
-
- updateOk = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in updateAllReleaseChildPageInfo: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in updateAllReleaseChildPageInfo rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return updateOk;
- }
-
- /**
- * Update page's last_update and status for child page
- * @param con
- * @param pageURI
- * @return
- */
- public static boolean updateReleaseChildPageMode (int pageId, String mode, long lastUpdate)
- {
- String updatePageLastUpdateSQL = "Update bp_release_child_pages set mode = ?, last_update = ? " +
- "where page_id = '" + pageId + "' ";
-
- boolean updateOk = false;
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(updatePageLastUpdateSQL);
- pstm.setString(1, mode);
- pstm.setLong(2, lastUpdate);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
-
- updateOk = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in updateReleaseChildPageStatus: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in updateReleaseChildPageStatus rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return updateOk;
- }
-
- // /**
- // * Update status for all child page
- // * @param con
- // * @param pageURI
- // * @return
- // */
- // public static boolean updateAllReleaseChildPageStatus (int parentId, String status)
- // {
- // String updatePageLastUpdateSQL = "Update bp_release_child_pages set status = ? " +
- // "where parent_id = '" + parentId + "' ";
- //
- // boolean updateOk = false;
- // Connection con = null;
- // DBConnector connector = DBConnector.getInstance();
- // try
- // {
- // //con = connector.getDBConnFactory(Environment.SQL);
- // con = connector.getDBConnFactory(Environment.PENSIONLINE);
- //
- // con.setAutoCommit(false);
- // PreparedStatement pstm = con.prepareStatement(updatePageLastUpdateSQL);
- // pstm.setString(1, status);
- // pstm.executeUpdate();
- //
- // con.commit();
- // con.setAutoCommit(true);
- //
- // updateOk = true;
- // }
- // catch (SQLException sqle)
- // {
- // LOG.error("Error in updateAllReleaseChildPageStatus: " + sqle.toString());
- // try
- // {
- // con.rollback();
- // }
- // catch (Exception e)
- // {
- // LOG.error("Error in updateAllReleaseChildPageStatus rollback: " + e.toString());
- // }
- // }
- // finally
- // {
- // if (con != null)
- // {
- // try
- // {
- // connector.close(con);
- // }
- // catch (Exception e)
- // {
- // LOG.error("Error in closing MySQL connection: " + e.toString());
- // }
- // }
- // }
- //
- // return updateOk;
- // }
-
- /**
- * Set child page parentId if not existed
- * @param con
- * @param pageURI
- * @return: true if a record is updated, false otherwise
- */
- public static boolean addChildToPage (int parentId, int childId)
- {
-
- String checkChildParentExistedSQL = "Select count(*) as num from bp_release_childparent " +
- "where parent_id = '" + parentId + "' and child_id = '" + childId + "'";
-
- String addChildToPageSQL = "Insert into bp_release_childparent values (?, ?)";
-
- boolean updateOk = false;
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
- PreparedStatement pstmSelect = con.prepareStatement(checkChildParentExistedSQL);
- ResultSet rsSelect = pstmSelect.executeQuery();
- if (rsSelect.next())
- {
- int numRecord = rsSelect.getInt("num");
- if (numRecord == 0)
- {
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(addChildToPageSQL);
- pstm.setInt(1, parentId);
- pstm.setInt(2, childId);
- pstm.executeUpdate();
- con.commit();
- con.setAutoCommit(true);
-
- updateOk = true;
- }
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in addChildToPage: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in addChildToPage rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
- LOG.info("addChildToPage for: " + parentId + " with " + childId+": "+updateOk);
- System.out.println("addChildToPage for: " + parentId + " with " + childId+": "+updateOk);
- return updateOk;
- }
-
- /**
- * Update publishing page based on page URI
- * @param con
- * @param pageId
- * @param status
- * @return
- */
- public static boolean updateReleasePageInfo (int pageId, String newStatus, String notes, String bugId)
- {
- String updatePageInfoSQL = "Update bp_release_pages set status = ?, notes = ?, bug_id = ? " +
- "where page_id = '" + pageId + "'";
-
- //LOG.info("updatePageInfoSQL: " + updatePageInfoSQL);
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- boolean updateOk = false;
-
- try
- {
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(updatePageInfoSQL);
-
- //long lastUpdate = DateTimeUtil.formatToOrderingTime(System.currentTimeMillis());
- pstm.setString(1, newStatus);
- pstm.setString(2, notes);
- pstm.setString(3, bugId);
- //pstm.setLong(4, lastUpdate);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
-
- updateOk = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in updatePageInfo: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in updatePageInfo rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
- return updateOk;
- }
-
- /**
- * Insert a new release page and returns the page_id of new record inserted
- * @param con
- * @param pageDTO
- */
- public static int insertReleaseChildPage (ReleasePageDTO pageDTO)
- {
- String insertPageSQL = "Insert into bp_release_child_pages " +
- "(page_uri, mode, last_update) values (?, ?, ?)";
- int pageId = -1;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(insertPageSQL);
- pstm.setString(1, pageDTO.getPageURI());
- pstm.setString(2, pageDTO.getMode());
- pstm.setLong(3, pageDTO.getLastUpdate());
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
-
- String getReleasePageSQL = "Select page_id from bp_release_child_pages where page_uri = '" + pageDTO.getPageURI() + "'";
-
- PreparedStatement getIdPstm = con.prepareStatement(getReleasePageSQL);
- ResultSet rs = getIdPstm.executeQuery();
- if (rs.next())
- {
- pageId = rs.getInt("page_id");
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in insertReleaseChildPage: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in insertReleaseChildPage rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return pageId;
- }
-
- /**
- * Insert a new release page and returns the page_id of new record inserted
- * @param con
- * @param pageDTO
- */
- public static int insertReleasePage (ReleasePageDTO pageDTO)
- {
- String insertPageSQL = "Insert into bp_release_pages " +
- "(page_uri, mode, status, bug_id, notes, last_update, self_edited) values (?, ?, ?, ?, ?, ?, ?)";
- int pageId = -1;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(insertPageSQL);
- pstm.setString(1, pageDTO.getPageURI());
- pstm.setString(2, pageDTO.getMode());
- pstm.setString(3, pageDTO.getStatus());
- pstm.setString(4, pageDTO.getBugId());
- pstm.setString(5, pageDTO.getNotes());
- pstm.setLong(6, pageDTO.getLastUpdate());
- pstm.setString(7, pageDTO.getSelfEdited());
-
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
-
- String getReleasePageSQL = "Select page_id from bp_release_pages where page_uri = '" + pageDTO.getPageURI() + "'";
-
- PreparedStatement getIdPstm = con.prepareStatement(getReleasePageSQL);
- ResultSet rs = getIdPstm.executeQuery();
- if (rs.next())
- {
- pageId = rs.getInt("page_id");
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in insertReleasePage: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in insertReleasePage rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return pageId;
- }
-
- /**
- * Synchronize editing pages between CMS tables and publishing tables
- */
- public static void updatePageSystem (Vector cmsPageURIs)
- {
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
-
- try
- {
- // delete old records
- // Implement later
-
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- for (int i = 0; i < cmsPageURIs.size(); i++)
- {
- // check if there is a file in the current release existed in bp_release_pages
- String pageURI = (String)cmsPageURIs.elementAt(i);
- insertPageToSystem(pageURI);
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in creating MySQL connection: " + sqle.toString());
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
- }
-
- /**
- * Insert a new release page
- * @param con
- * @param pageDTO
- */
- public static void insertPageToSystem (String pageURI)
- {
- String updatePageStatusSQL = "Insert into bp_page_system " +
- "(page_uri) values" +
- "('"
- + pageURI +
- "')";
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
-
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(updatePageStatusSQL);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in updatePageStatus: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in updatePageStatus rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
- }
-
- /**
- * This method delete the records in bp_release_pages table that is no longer valid for the publishing.
- * Eg, when a new page is created than deleted or a page marked as delete but then is unmarked by Author
- * @param pageURIs
- */
- public static void cleanReleasePages (Vector pageURIs)
- {
- if (pageURIs == null || pageURIs.size() == 0)
- {
- return;
- }
-
- // combine URI set
- StringBuffer pageSetBuf = new StringBuffer();
- pageSetBuf.append("(");
- for (int i = 0; i < pageURIs.size(); i++)
- {
- if (i == pageURIs.size() -1)
- {
- pageSetBuf.append("'").append(pageURIs.elementAt(i)).append("'");
- }
- else
- {
- pageSetBuf.append("'").append(pageURIs.elementAt(i)).append("',");
- }
- }
- pageSetBuf.append(")");
-
- String cleanQuery = "Delete from bp_release_pages where page_uri not in " + pageSetBuf.toString();
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(cleanQuery);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in cleanReleasePages: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in cleanReleasePages rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
- }
-
- /**
- * This method delete the records in bp_release_pages table that is no longer valid for the publishing.
- * Eg, when a new page is created than deleted or a page marked as delete but then is unmarked by Author
- * Dont use this method.
- * @param pageIds
- */
- public static void cleanReleaseChildPages (Vector<ReleasePageDTO> pageDTOs, String prefix)
- {
- if (pageDTOs == null)
- {
- return;
- }
-
- // combine URI set
- StringBuffer pageSetBuf = new StringBuffer();
- pageSetBuf.append("(''");
- for (int i = 0; i < pageDTOs.size(); i++)
- {
- ReleasePageDTO childPage = pageDTOs.elementAt(i);
- pageSetBuf.append(",'").append(childPage.getPageId()).append("'");
- }
- pageSetBuf.append(")");
-
- // clean child pages
- String cleanChildQuery = "Delete from bp_release_child_pages where page_id not in " + pageSetBuf.toString() + " and page_uri like '" + prefix + "%'";
- String cleanChildParentQuery = "Delete from bp_release_childparent where child_id in " +
- "(select page_id from bp_release_child_pages where page_id not in "
- + pageSetBuf.toString() + " and page_uri like '" + prefix + "%')";
- LOG.info("Clean child pages: " + cleanChildQuery);
- LOG.info("Clean child_parent links: " + cleanChildParentQuery);
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(cleanChildParentQuery);
- pstm.executeUpdate();
- con.commit();
-
- pstm = con.prepareStatement(cleanChildQuery);
- pstm.executeUpdate();
- con.commit();
- con.setAutoCommit(true);
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in cleanReleaseChildPages: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in cleanReleaseChildPages rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
- }
-
- /**
- * Record action made with page in publishing
- * @param pageId
- * @param userId
- * @param actionType
- * @param notes
- * @param time Formated long value: YYYYmmddHHmmss
- * @return
- */
- public static boolean insertReleaseAction (int pageId, String userId, String status,
- String notes, long updateTime)
- {
- LOG.info("insertReleaseAction():BEGIN");
- LOG.info(updateTime);
- String insertReleaseActionSQL = "Insert into bp_release_actions " +
- "(page_id, user_id, status, notes, action_time) values (?, ?, ?, ?, ?)";
- boolean result = false;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(insertReleaseActionSQL);
- pstm.setInt(1, pageId);
- pstm.setString(2, userId);
- pstm.setString(3, status);
- pstm.setString(4, notes);
- pstm.setLong(5, updateTime);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
- result = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in insertReleaseAction: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in insertReleaseAction rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
- LOG.info("insertReleaseAction():END");
- return result;
- }
-
- public static boolean insertOrphanReleaseAction (int pageId, String userId, String status,
- String notes, long updateTime)
- {
- LOG.info("insertOrphanReleaseAction():BEGIN");
- LOG.info(updateTime);
- String insertReleaseActionSQL = "Insert into bp_release_actions " +
- "(page_id, childpage_id, user_id, status, notes, action_time) values (-1, ?, ?, ?, ?, ?)";
- boolean result = false;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(insertReleaseActionSQL);
- pstm.setInt(1, pageId);
- pstm.setString(2, userId);
- pstm.setString(3, status);
- pstm.setString(4, notes);
- pstm.setLong(5, updateTime);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
- result = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in insertOrphanReleaseAction: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in insertOrphanReleaseAction rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
- LOG.info("insertOrphanReleaseAction():END");
- return result;
- }
-
- /**
- * Build action histories of this page
- * @param pageId
- * @return
- */
- public static String buildActionHistories (int pageId)
- {
- String selectReleaseActionSQL = "Select notes from bp_release_actions where page_id = '"
- + pageId + "' order by action_time desc";
- StringBuffer actionHisBuf = new StringBuffer();
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- PreparedStatement pstm = con.prepareStatement(selectReleaseActionSQL);
- ResultSet rs = pstm.executeQuery();
-
- while (rs.next())
- {
- String notes = rs.getString("notes");
- if (notes != null)
- {
- actionHisBuf.append(notes).append("\n\n");
- }
- }
-
- if (actionHisBuf.length() >= 2)
- {
- actionHisBuf.delete(actionHisBuf.length() - 2, actionHisBuf.length());
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in buildActionHistories: " + sqle.toString());
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return actionHisBuf.toString();
- }
-
- /**
- * Build action histories for orphan documents
- * @param pageId
- * @return
- */
- public static String buildOrphanActionHistories (int pageId)
- {
- String selectReleaseActionSQL = "Select notes from bp_release_actions where childpage_id = '"
- + pageId + "' order by action_time desc";
- StringBuffer actionHisBuf = new StringBuffer();
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- PreparedStatement pstm = con.prepareStatement(selectReleaseActionSQL);
- ResultSet rs = pstm.executeQuery();
-
- while (rs.next())
- {
- String notes = rs.getString("notes");
- if (notes != null)
- {
- actionHisBuf.append(notes).append("\n\n");
- }
- }
-
- if (actionHisBuf.length() >= 2)
- {
- actionHisBuf.delete(actionHisBuf.length() - 2, actionHisBuf.length());
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in buildActionHistories: " + sqle.toString());
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return actionHisBuf.toString();
- }
- /**
- * Get the last publishing action userId right before the page change to status
- * @param pageId
- * @param status: Status that the page is changed by user
- * @return
- */
- public static String getLastPublishingActionUserId (int pageId, String status)
- {
- String getMaxUpdateActionSQL = "Select max(action_time) as atMax from bp_release_actions where page_id = '"
- + pageId + "' and status = '" + status + "'";
- String getLastUserActionSQL = "Select user_id from bp_release_actions where page_id = '"
- + pageId + "' and status = '" + status + "' and action_time = ?";
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- PreparedStatement pstmGetMax = con.prepareStatement(getMaxUpdateActionSQL);
- ResultSet rsGetMax = pstmGetMax.executeQuery();
-
- if (rsGetMax.next())
- {
- long maxActionTime = rsGetMax.getLong("atMax");
-
- // Get user_id from max action time
- PreparedStatement pstmLastUser = con.prepareStatement(getLastUserActionSQL);
- pstmLastUser.setLong(1, maxActionTime);
-
- ResultSet rsLastUser = pstmLastUser.executeQuery();
- if (rsLastUser.next())
- {
- return rsLastUser.getString("user_id");
- }
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in getLastPublishingActionUserId: " + sqle.toString());
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return null;
- }
-
- /**
- * Delete release page by pageId
- * @return
- */
- public static boolean removeReleasePage (int pageId)
- {
- String deleteReleasePageSQL = "Delete from bp_release_pages where page_id = '" + pageId + "'";
- boolean result = false;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(deleteReleasePageSQL);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
- result = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in deleteReleasePage: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in deleteReleasePage rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return result;
- }
-
- /**
- * Delete release child page by pageId
- * @return
- */
- public static boolean removeReleaseChildPage (int childPageId)
- {
- String deleteReleaseChildPageSQL = "Delete from bp_release_child_pages where page_id = '" + childPageId + "'";
- boolean result = false;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(deleteReleaseChildPageSQL);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
- result = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in removeReleaseChildPage: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in removeReleaseChildPage rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return result;
- }
-
- /**
- * Delete release actions by pageId
- * @return
- */
- public static boolean removeReleaseActions (int pageId)
- {
- String deleteReleasePageSQL = "Delete from bp_release_actions where page_id = '" + pageId + "'";
- boolean result = false;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.PENSIONLINE);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- con.setAutoCommit(false);
- PreparedStatement pstm = con.prepareStatement(deleteReleasePageSQL);
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
- result = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in deleteReleaseActions: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in deleteReleaseActions rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return result;
- }
-
- /**
- * Delete release child pages by parentId
- * @return
- */
- public static boolean removeReleaseChildPagesByParent (int parentId)
- {
- String selectChildIdFromParentSQL = "Select child_id from bp_release_childparent " +
- "where parent_id = '" + parentId + "'";
- String deleteReleaseChildPageSQL = "Delete from bp_release_child_pages where page_id in ";
-
- String deleteChildParentSQL = "Delete from bp_release_childparent where child_id in ";
- boolean result = false;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- // get child_id set from parent_id
- PreparedStatement pstm = con.prepareStatement(selectChildIdFromParentSQL);
- ResultSet rs = pstm.executeQuery();
- StringBuffer inCondStrBuf = new StringBuffer("(''");
- while (rs.next())
- {
- int child_id = rs.getInt("child_id");
- inCondStrBuf.append(",'").append(child_id).append("'");
- }
- inCondStrBuf.append(")");
-
- con.setAutoCommit(false);
- LOG.info("deleteReleaseChildPageSQL: " + deleteReleaseChildPageSQL + inCondStrBuf.toString());
-
- pstm = con.prepareStatement(deleteReleaseChildPageSQL + inCondStrBuf.toString());
- pstm.executeUpdate();
-
- // delete child-parent relationship
- pstm = con.prepareStatement(deleteChildParentSQL + inCondStrBuf.toString());
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
- result = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in removeReleaseChildPagesByParent: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in removeReleaseChildPagesByParent rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return result;
- }
-
- /**
- * Delete release child pages by parentId
- * @return
- */
- public static boolean removeAllInvalidParentPages ()
- {
- String selectAllParentIdSQL = "Select parent_id from bp_release_childparent";
-
- String deleteInvalidParentPageSQL = "Delete from bp_release_pages where mode = 'Impacted' and " +
- "self_edited = 'N' and page_id not in ";
-
- boolean result = false;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- // get child_id set from parent_id
- PreparedStatement pstm = con.prepareStatement(selectAllParentIdSQL);
- ResultSet rs = pstm.executeQuery();
- StringBuffer inCondStrBuf = new StringBuffer("(''");
- while (rs.next())
- {
- int parent_id = rs.getInt("parent_id");
- inCondStrBuf.append(",'").append(parent_id).append("'");
- }
- inCondStrBuf.append(")");
-
- con.setAutoCommit(false);
- LOG.info("deleteInvalidParentPageSQL: " + deleteInvalidParentPageSQL + inCondStrBuf.toString());
-
- pstm = con.prepareStatement(deleteInvalidParentPageSQL + inCondStrBuf.toString());
- pstm.executeUpdate();
-
- con.commit();
- con.setAutoCommit(true);
- result = true;
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in removeAllInvalidParentPages: " + sqle.toString());
- try
- {
- con.rollback();
- }
- catch (Exception e)
- {
- LOG.error("Error in removeAllInvalidParentPages rollback: " + e.toString());
- }
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return result;
- }
-
- /**
- * Get all approved pages for deploy
- * @return
- */
- public static Vector<ReleasePageDTO> getAllApprovedParentPages ()
- {
- String selectAllApprovedPagesSQL = "Select page_id, page_uri, mode, status, bug_id, notes, last_update " +
- "from bp_release_pages where status = 'Approved'";
- Vector<ReleasePageDTO> approvedPages = new Vector<ReleasePageDTO>();
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- PreparedStatement pstm = con.prepareStatement(selectAllApprovedPagesSQL);
- ResultSet rs = pstm.executeQuery();
- ReleasePageDTO pageDTO = null;
-
- while (rs.next())
- {
- pageDTO = new ReleasePageDTO();
- pageDTO.setPageId(rs.getInt("page_id"));
- pageDTO.setParentId(-1);
- pageDTO.setPageURI(rs.getString("page_uri"));
- pageDTO.setMode(rs.getString("mode"));
- pageDTO.setStatus(rs.getString("status"));
- pageDTO.setBugId(rs.getString("bug_id"));
- pageDTO.setNotes(rs.getString("notes"));
- pageDTO.setLastUpdate(rs.getLong("last_update"));
- pageDTO.setSelfEdited("Y");
-
- approvedPages.add(pageDTO);
- }
-
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in getAllApprovedParentPages: " + sqle.toString());
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return approvedPages;
- }
-
- /**
- * Check if a parent page is Ok to deploy:
- * - It has no children that whose's parent are not approved
- * - It has status of 'Approved'
- * @return
- */
- public static boolean isParentPageReadyToDeploy (int parentId)
- {
- String checkAllChildren = "select count(*) as numC from bp_release_childparent cp where cp.parent_id = '" + parentId + "'";
-
- String checkParentStatus = "select status from bp_release_pages where page_id = '" + parentId + "'";
-
- String selectChildIdFromParentSQL = "Select child_id from bp_release_childparent " +
- "where parent_id = '" + parentId + "'";
-
- String checkAllParentApproved = "Select count(*) as num from bp_release_pages p inner join bp_release_childparent cp on (p.page_id = cp.parent_id) " +
- "where p.status != 'Approved' and p.status != 'Deployed' and cp.child_id in (" +
- selectChildIdFromParentSQL +")";
-
- boolean result = false;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- // get child_id set from parent_id
- PreparedStatement pstm = con.prepareStatement(checkAllChildren);
- ResultSet rs = pstm.executeQuery();
- if (rs.next())
- {
- int numC = rs.getInt("numC");
- rs.close();
- pstm.close();
-
- if (numC > 0)
- {
- // Check other parent of their children
- pstm = con.prepareStatement(selectChildIdFromParentSQL);
- rs = pstm.executeQuery();
-
- //LOG.info("checkAllParentApproved: " + checkAllParentApproved);
-
- pstm = con.prepareStatement(checkAllParentApproved);
- rs = pstm.executeQuery();
- if (rs.next())
- {
- int num = rs.getInt("num");
- if (num == 0)
- {
- result = true;
- }
- }
- }
- else
- {
- pstm = con.prepareStatement(checkParentStatus);
- rs = pstm.executeQuery();
-
- if (rs.next())
- {
- String status = rs.getString("status");
- if (status != null && (status.equals("Approved") || status.equals("Deployed")))
- {
- result = true;
- }
- }
- }
-
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in removeReleaseChildPagesByParent: " + sqle.toString());
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- {
- LOG.error("Error in closing MySQL connection: " + e.toString());
- }
- }
- }
-
- return result;
- }
-
- /**
- * Check if a child page is Ok to deploy:
- * - Has at least 1 parent
- * - All parent are approved
- * @return
- */
- public static boolean isChildPageReadyToDeploy (int childPageId)
- {
- String checkAllParentApproved = "Select count(*) as numAP from bp_release_pages p inner join bp_release_childparent cp on (p.page_id = cp.parent_id) " +
- "where (p.status = 'Approved' or p.status = 'Deployed') and cp.child_id = '" + childPageId + "'";
-
- String checkAllParent = "select count(*) as numP from bp_release_childparent cp where cp.child_id = '" + childPageId + "'";
-
- boolean result = false;
-
- Connection con = null;
- DBConnector connector = DBConnector.getInstance();
- try
- {
- int numParentApproved = 0;
- int numAllParent = 0;
- //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
- //con = connector.getDBConnFactory(Environment.SQL);
- con = connector.getDBConnFactory(Environment.PENSIONLINE);
-
- // get child_id set from parent_id
- PreparedStatement pstm = con.prepareStatement(checkAllParentApproved);
- ResultSet rs = pstm.executeQuery();
-
- if (rs.next())
- {
- numParentApproved = rs.getInt("numAP");
- }
-
- pstm = con.prepareStatement(checkAllParent);
- rs = pstm.executeQuery();
- if (rs.next())
- {
- numAllParent = rs.getInt("numP");
- }
-
- if (numParentApproved > 0 && numParentApproved == numAllParent)
- {
- result = true;
- }
- }
- catch (SQLException sqle)
- {
- LOG.error("Error in removeReleaseChildPagesByParent: " + sqle.toString());
- }
- finally
- {
- if (con != null)
- {
- try
- {
- connector.close(con);
- }
- catch (Exception e)
- …
Large files files are truncated, but you can click here to view the full file