/myproject/src/com/bp/pensionline/publishing/database/PublishingToolbarSQLHandler.java

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)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}		

		
		return result;
	}	
	
	/**
	 * Check if a child page has been deployed:
	 * - Has at least 1 parent
	 * - All parent are approved
	 * @return
	 */
	public static boolean isChildPageDeployed (int childPageId)
	{		
		String checkAllParentDeployed = "Select count(*) as numDP from bp_release_pages p inner join bp_release_childparent cp on (p.page_id = cp.parent_id) " +
				"where 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(checkAllParentDeployed);
			ResultSet rs = pstm.executeQuery();	
			
			if (rs.next())
			{
				numParentApproved = rs.getInt("numDP");
			}
			
			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)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}		

		
		return result;
	}		
	
	/**
	 * Check if there is deployable pages in the system
	 * @return
	 */
	public static boolean hasDeployablePages ()
	{
		String selectApprovedPageIdsSQL = "Select page_id " +
			"from bp_release_pages where status = 'Approved'";
		
		Connection con = null;
		DBConnector connector = DBConnector.getInstance();
		boolean result = false;
		try
		{
			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
			//con = connector.getDBConnFactory(Environment.SQL);
			con = connector.getDBConnFactory(Environment.PENSIONLINE);
			
			PreparedStatement pstm = con.prepareStatement(selectApprovedPageIdsSQL);
			ResultSet rs = pstm.executeQuery();
			
			while (rs.next())
			{
				int pageId = rs.getInt("page_id");
				if (isParentPageReadyToDeploy(pageId))
				{
					result = true;
					break;
				}
			}
			
			Statement stat = con.createStatement();
			ResultSet rst = stat.executeQuery("SELECT * FROM bp_release_child_pages where status='Approved'");
			if (rst.next()) {
				return true;
			}
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in hasDeployablePages: " + sqle.toString());
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}		
		
		return result;
	}
	
	/**
	 * Create a release package by inserting a new record to bp_releases table
	 * @param packageName
	 * @return Id of new package inserted
	 */
	public static int createReleasePackage(String packageName, String notes)
	{
		if (packageName == null || packageName.trim().equals(""))
		{
			return -1;
		}
		
		String insertReleaseSQL = "Insert into bp_releases (released, package_name, notes) values (?, ?, ?)";
		int releaseId = -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(insertReleaseSQL);
			long released = DateTimeUtil.formatToOrderingTime(System.currentTimeMillis());
			pstm.setLong(1, released);
			pstm.setString(2, packageName);
			pstm.setString(3, notes);
			pstm.executeUpdate();
			
			con.commit();
			con.setAutoCommit(true);
			
			String getReleasePageSQL = "Select release_id from bp_releases where package_name = '" + packageName + "'";
			
			PreparedStatement getIdPstm = con.prepareStatement(getReleasePageSQL);
			ResultSet rs = getIdPstm.executeQuery();
			if (rs.next())
			{
				releaseId = rs.getInt("release_id");
			}			
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in createReleasePackage: " + sqle.toString());
			try
			{
				con.rollback();
			}
			catch (Exception e)
			{
				LOG.error("Error in createReleasePackage rollback: " + e.toString());
			}
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}		
		
		return releaseId;		
	}
	
	/**
	 * Create a release note by inserting a new record to bp_notes table
	 * @param packageName
	 * @param releasePageDTO
	 */
	public static void createReleaseNote (int releaseId, ReleasePageDTO releasePageDTO, Vector<ReleasePageDTO> childPages)
	{
		LOG.info("createReleaseNote():BEGIN");
		if (releaseId < 0 || releasePageDTO == null)
		{
			return;
		}
		
		String insertReleaseNoteSQL = "Insert into bp_release_notes " +
				"(release_id, page_id, parent_id, page_uri, page_ext, mode, bug_id, is_embeded) " +
				"values (?, ?, ?, ?, ?, ?, ?, ?)";
		String updatePageSQL = "Update bp_release_pages set status = 'Deployed' where page_id = ?";	
		
		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);
			// insert note for parentPage
			PreparedStatement pstm = null;
			
			if (releasePageDTO.getMode() != null && !releasePageDTO.getMode().equals("Impacted") && 
					releasePageDTO.getSelfEdited() != null && releasePageDTO.getSelfEdited().equals("Y"))
			{
				pstm =con.prepareStatement(insertReleaseNoteSQL);			
				pstm.setInt(1, releaseId);
				pstm.setInt(2, releasePageDTO.getPageId());
				pstm.setInt(3, -1);
				pstm.setString(4, releasePageDTO.getPageURI());
				String pageURI = releasePageDTO.getPageURI();
				String pageExt = "Unknown";
				if (pageURI != null && pageURI.lastIndexOf('.') > -1)
				{
					pageExt = pageURI.substring(pageURI.lastIndexOf('.') + 1);
				}
				pstm.setString(5, pageExt);
				pstm.setString(6, releasePageDTO.getMode());
				pstm.setString(7, releasePageDTO.getBugId());
				pstm.setString(8, "N");
				
				pstm.executeUpdate();
			}
			
			int parentId = releasePageDTO.getPageId();
			
			// update page status to 'Deployed' for both self_edited is true or false
			pstm = con.prepareStatement(updatePageSQL);
			pstm.setInt(1, releasePageDTO.getPageId());
			
			pstm.executeUpdate();			
			
			// insert note for child pages. If parent page is not self_edited consider these children as
			// orphan
			if (childPages != null)
			{
				for (int i = 0; i < childPages.size(); i++)
				{
					ReleasePageDTO childPage = (ReleasePageDTO)childPages.elementAt(i);
					if (childPage != null)
					{
						pstm = con.prepareStatement(insertReleaseNoteSQL);
						pstm.setInt(1, releaseId);
						pstm.setInt(2, childPage.getPageId());
						pstm.setInt(3, parentId);
						pstm.setString(4, childPage.getPageURI());
						String childPageURI = childPage.getPageURI();
						String childPageExt = "Unknown";
						if (childPageURI != null && childPageURI.lastIndexOf('.') > -1)
						{
							childPageExt = childPageURI.substring(childPageURI.lastIndexOf('.') + 1);
						}
						pstm.setString(5, childPageExt);
						
						// inherit from parent
						pstm.setString(6, childPage.getMode());
						pstm.setString(7, releasePageDTO.getBugId());
						pstm.setString(8, "Y");
						
						pstm.executeUpdate();					
					}
				}
			}
			
			con.commit();
			
			// Update page status to deployed
			con.setAutoCommit(true);	
			LOG.info("createReleaseNote():END");
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in createReleaseNote: " + sqle.toString());
			try
			{
				con.rollback();
			}
			catch (Exception e)
			{
				LOG.error("Error in createReleaseNote rollback: " + e.toString());
			}
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}				
	}
	
//	/**
//	 * Delete all pages that are published
//	 * @param packageName
//	 * @param releasePageDTO
//	 */
//	public static void cleanUpDeployedPages ()
//	{
//		String deleteDeployedChildPageSQL = "Delete from bp_release_child_pages where status = 'Deployed'";
//		
//		String deleteDeployedPageActionSQL = "Delete from bp_release_actions where page_id in " +
//				"(select page_id from bp_release_pages where status = 'Deployed')";
//		String deleteDeployedPageSQL = "Delete from bp_release_pages where status = 'Deployed'";
//		String deleteDeployedChildPageSQL = "Delete from bp_release_child_pages where status = 'Deployed'";
//		
//		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(deleteDeployedPageActionSQL);						
//			pstm.executeUpdate();
//			
//			pstm = con.prepareStatement(deleteDeployedPageSQL);						
//			pstm.executeUpdate();
//			
//			pstm = con.prepareStatement(deleteDeployedChildPageSQL);						
//			pstm.executeUpdate();
//			
//			con.commit();
//			con.setAutoCommit(true);						
//		}
//		catch (SQLException sqle)
//		{
//			LOG.error("Error in cleanUpPublishedPages: " + sqle.toString());
//			try
//			{
//				con.rollback();
//			}
//			catch (Exception e)
//			{
//				LOG.error("Error in cleanUpPublishedPages rollback: " + e.toString());
//			}
//		}
//		finally
//		{
//			if (con != null)
//			{
//				try
//				{
//					connector.close(con);
//				}
//				catch (Exception e)
//				{
//					LOG.error("Error in closing MySQL connection: " + e.toString());
//				}
//			}
//		}				
//	}	
	

	/**
	 * Get all deployed pages for clean up
	 * @return
	 */
	public static Vector<Integer> getAllDeployedParentPageIds ()
	{
		String selectAllApprovedPagesSQL = "Select page_id from bp_release_pages where status = 'Deployed'";

		Vector<Integer> deployedPageIds = new Vector<Integer>();
		
		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();
			
			while (rs.next())
			{			
				deployedPageIds.add(Integer.valueOf(rs.getInt("page_id")));
			}
			
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in getAllDeployedParentPageIds: " + sqle.toString());
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}	
		
		return deployedPageIds;
	}
	
	/**
	 * Get all deployed pages for clean up
	 * @return
	 */
	public static ArrayList<String> getDeployedParentURIs ()
	{
		// only self_edited pages are exported
		String selectUndeleteDeployedPagesSQL = "Select page_uri from bp_release_pages " +
				"where status = 'Deployed' and mode != 'Impacted' and self_edited = 'Y'";		
		

		ArrayList<String> undeleteDeployedPages = new ArrayList<String>();
		
		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(selectUndeleteDeployedPagesSQL);
			ResultSet rs = pstm.executeQuery();
			
			while (rs.next())
			{
				undeleteDeployedPages.add(rs.getString("page_uri"));
			}			
			
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in getUndeltedDeployedPageURIs: " + sqle.toString());
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}	
		
		return undeleteDeployedPages;
	}	
	
	/**
	 * Log the action of user to the publishing page. This will stay in the database permernantly
	 * @param eventTime
	 * @param pageURI
	 * @param userID
	 * @param status
	 * @param notes
	 */
	public static void doAudit(long eventTime, String pageURI, String userID, String status, String notes)
	{
		if (pageURI == null && userID == null)
		{
			return;
		}
		String insertPageSQL = "Insert into bp_release_audit " +
			"(event_time, page_uri, user_id, status, notes) values " +
			"(?, ?, ?, ?, ?)";
		
		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.setLong(1, eventTime);
			pstm.setString(2, pageURI);
			pstm.setString(3, userID);
			pstm.setString(4, status);
			pstm.setString(5, notes);
			
			pstm.executeUpdate();
			
			con.commit();
			con.setAutoCommit(true);		
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in doAudit: " + sqle.toString());
			try
			{
				con.rollback();
			}
			catch (Exception e)
			{
				LOG.error("Error in doAudit rollback: " + e.toString());
			}
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}			
	}
	
	/**
	 * Get all html publishing pages
	 * @return
	 */
	public static Vector<ReleasePageDTO> getAllPublishingPages ()
	{
		String selectAllPublishingPagesSQL = "Select page_id, page_uri, mode, status, bug_id, notes, last_update, self_edited " +
			"from bp_release_pages order by status desc, mode, page_uri";

		Vector<ReleasePageDTO> publishingPages = 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(selectAllPublishingPagesSQL);
			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(rs.getString("self_edited"));
				
				publishingPages.add(pageDTO);
			}
			
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in getAllPublishingPages: " + sqle.toString());
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}	
		
		return publishingPages;		
	}
	
	/**
	 * Get all html publishing child pages
	 * @return
	 */
	public static Vector<ReleasePageDTO> getAllChildPages ()
	{
		String selectAllOrphanChildPagesSQL = "Select page_id, page_uri, mode, last_update from bp_release_child_pages";

		Vector<ReleasePageDTO> childPages = 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(selectAllOrphanChildPagesSQL);
			ResultSet rs = pstm.executeQuery();
			ReleasePageDTO pageDTO = null;
			
			while (rs.next())
			{
				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"));
				
				childPages.add(pageDTO);
			}
			
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in getAllChildPages: " + sqle.toString());
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}	
		
		return childPages;		
	}	
	
	/**
	 * Get all html deleting publishing pages
	 * @return
	 */
	public static Vector getAllDeletingPages ()
	{
		String selectAllApprovedPagesSQL = "Select page_id, page_uri, status, bug_id, notes, last_update, self_edited " +
			"from bp_release_pages where mode = 'Deleted' order by page_uri";

		Vector<ReleasePageDTO> deletingPages = 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("Deleted");
				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"));
				
				deletingPages.add(pageDTO);
			}
			
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in getAllDeletingPages: " + sqle.toString());
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}	
		
		return deletingPages;		
	}
	
	/**
	 * Get all releases happened in the system
	 * @return
	 */
	public static Vector getAllReleases()
	{
		String getAllReleasesSQL = "Select release_id, released, package_name, notes from bp_releases order by released desc";

		Vector<ReleasePackageDTO> releases = new Vector<ReleasePackageDTO>();
		
		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(getAllReleasesSQL);
			ResultSet rs = pstm.executeQuery();
			ReleasePackageDTO releaseDTO = null;
			
			while (rs.next())
			{
				releaseDTO = new ReleasePackageDTO();
				releaseDTO.setReleaseId(rs.getInt("release_id"));
				releaseDTO.setReleasedAt(rs.getLong("released"));
				releaseDTO.setPackageName(rs.getString("package_name"));
				releaseDTO.setNotes(rs.getString("notes"));
				
				releases.add(releaseDTO);
			}
			
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in getAllReleases: " + sqle.toString());
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}	
	
		return releases;		
	}
	
	/**
	 * Get all pages released in a released package
	 * @return
	 */
	public static Vector getBugIdsInRelease(int releaseId)
	{
		String getAllBugIdsSQL = "Select distinct(bug_id) from bp_release_notes where release_id = '" + releaseId +
				"' order by bug_id asc";

		Vector<String> bugIds = new Vector<String>();
		
		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(getAllBugIdsSQL);
			ResultSet rs = pstm.executeQuery();			
			
			while (rs.next())
			{
				bugIds.add(rs.getString("bug_id"));
			}
			
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in getBugIdsInRelease: " + sqle.toString());
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}	
	
		return bugIds;		
	}		
	
	/**
	 * Get all pages released in a released package
	 * @return
	 */
	public static Vector getReleaseDetailsByBugId (int releaseId, String bugId)
	{

		String getReleaseDetailsSQL = "Select page_id, parent_id, page_uri, page_ext, mode, is_embeded from bp_release_notes " +
				"where release_id = '" + releaseId + "' and bug_Id = '" + bugId + "' " +
				"and mode != 'Impacted' order by page_ext asc";

		Vector<ReleasePageDTO> releaseDetails = new Vector<ReleasePageDTO>();	
		//LOG.info("getReleaseDetailsSQL: " + getReleaseDetailsSQL);
		
		HashSet<String> releasedURIs = new HashSet<String>();
		
		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(getReleaseDetailsSQL);
			ResultSet rs = pstm.executeQuery();
			ReleasePageDTO pageDTO = null;
			
			while (rs.next())
			{
				String pageURI = rs.getString("page_uri");				
				if (!releasedURIs.contains(pageURI))
				{
					releasedURIs.add(pageURI);
					pageDTO = new ReleasePageDTO();
					pageDTO.setPageId(rs.getInt("page_id"));
					pageDTO.setParentId(rs.getInt("parent_id"));
					pageDTO.setPageURI(pageURI);
					pageDTO.setMode(rs.getString("mode"));
					pageDTO.setStatus("Deployed"); 
					pageDTO.setBugId(bugId);
					pageDTO.setNotes("Page has been deployed"); // no use
					pageDTO.setSelfEdited("Y");	// no use
					
					releaseDetails.add(pageDTO);
				}
			}
			
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in getReleaseDetailsByBugId: " + sqle.toString());
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}	
	
		return releaseDetails;		
	}	
	
	/**
	 * 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 validPageIds
	 */
	public static void removeOphanPage (int parentId, Vector validPageIds)
	{
		String cleanQuery = null;
		
		if (validPageIds == null || validPageIds.size() == 0)
		{
			cleanQuery = "Delete from bp_release_childparent where parent_id = '" + parentId + "'";
		}
		else
		{
			// combine URI set
			StringBuffer pageSetBuf = new StringBuffer();
			pageSetBuf.append("(");
			for (int i = 0; i < validPageIds.size(); i++)
			{
				if (i == validPageIds.size() -1)
				{
					pageSetBuf.append("'").append(validPageIds.elementAt(i)).append("'");
				}
				else
				{
					pageSetBuf.append("'").append(validPageIds.elementAt(i)).append("',");
				}
			}
			pageSetBuf.append(")");
			
			// clean child pages
			cleanQuery = "Delete from bp_release_childparent where parent_id = '" + parentId + "' " +
					"and child_id 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();
			
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in removeOphanPage: " + sqle.toString());
			try
			{
				con.rollback();
			}
			catch (Exception e)
			{
				LOG.error("Error in removeOphanPage rollback: " + e.toString());
			}
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}		
	}	
	
	public static Vector<Integer> getParentPagesOfChild (int childPageId)
	{
		String getParentPagesOfChildSQL = "Select parent_id from bp_release_childparent " +
				"where child_id = '" + childPageId + "'";

		Vector<Integer> parentPageIds = new Vector<Integer>();	
		//LOG.info("getReleaseDetailsSQL: " + getReleaseDetailsSQL);
		
		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(getParentPagesOfChildSQL);
			ResultSet rs = pstm.executeQuery();
			
			while (rs.next())
			{
				int parent_id = rs.getInt("parent_id");
				parentPageIds.add(new Integer(parent_id));
			}
			
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in getParentPagesOfChild: " + sqle.toString());
		}
		finally
		{
			if (con != null)
			{
				try
				{
					connector.close(con);
				}
				catch (Exception e)
				{
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}	
		
		return parentPageIds;		
	}
	
	/**
	 * Update all 'Paused' pages to 'Approved' pages
	 * @param con
	 * @param pageURI
	 * @return
	 */
	public static boolean unpauseAllApprovedPages ()
	{
		String updatePageStatusSQL = "Update bp_release_pages set status = 'Approved' " +
			"where status = 'Paused'";		
				
		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.executeUpdate();
			
			con.commit();
			con.setAutoCommit(true);
			
			updateOk = true;
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in unpauseAllApprovedPages: " + sqle.toString());
			try
			{
				con.rollback();
			}
			catch (Exception e)
			{
				LOG.error("Error in unpauseAllApprovedPages 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 child page's last_update
	 * @param con
	 * @param pageURI
	 * @return
	 */
	public static boolean updateChildPageLastUpdate (int pageId, long lastUpdate, String mode, String status)
	{
		String updateChildPageLastUpdateSQL = "Update bp_release_child_pages set last_update = ?, mode = ?, status = ?" +
			"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(updateChildPageLastUpdateSQL);
			pstm.setLong(1, lastUpdate);
			pstm.setString(2, mode);
			pstm.setString(3, status);
			pstm.executeUpdate();
			
			con.commit();
			con.setAutoCommit(true);
			
			updateOk = true;
		}
		catch (SQLException sqle)
		{
			LOG.error("Error in updateChildPageLastUpdate: " + sqle.toString());
			try
			{
				con.rollback();
			}
			catch (Exception e)
			{
				LOG.error("Error in updateChildPageLastUpdate 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;
	}	
	
	
	public static void debugPageDTO (ReleasePageDTO pageDTO)
	{
		if (pageDTO != null)
		{
			LOG.info("Page Id: " + pageDTO.getPageId());
			LOG.info("Page URI: " + pageDTO.getPageURI());
			LOG.info("Page Mode: " + pageDTO.getMode());
			LOG.info("Page Status: " + pageDTO.getStatus());
			LOG.info("Page BugId: " + pageDTO.getBugId());
			LOG.info("Page LastModified: " + pageDTO.getLastUpdate());
		}
	}
	
	//These methods are used to process changed documents which do not belong to any pages (orphan)
	/**
	 * Get all changed documents which do not have parent
	 * @return
	 */
	public static Vector<ReleasePageDTO> getAllChangedChildPages(String userId) {
		String selectSQL = "Select page_id, page_uri, mode, last_update, status, bug_id" +
			" from bp_release_child_pages where page_id not in (SELECT child_id FROM bp_release_childparent) " +
			" order by page_uri";
		
		String lastUserEditedSQL = "Select user_id from bp_release_actions where childpage_id=? " +
				                    "and status='Edited' and action_time=(Select max(action_time) as atMax from bp_release_actions where childpage_id=? " +
				                    "and status='Edited')";
		
		String lastUserCheckedSQL = "Select user_id from bp_release_actions where childpage_id=? " +
        							"and status='Checked' and action_time=(Select max(action_time) as atMax from bp_release_actions where childpage_id=? " +
        							"and status='Checked')";

		System.out.println(lastUserEditedSQL);
		System.out.println(lastUserCheckedSQL);
		Vector<ReleasePageDTO> changedChildPages = new Vector<ReleasePageDTO>();
		Connection con = null;
		DBConnector connector = DBConnector.getInstance();
		try {
			LOG.info("SQL: "+selectSQL);
			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
			//con = connector.getDBConnFactory(Environment.SQL);
			con = connector.getDBConnFactory(Environment.PENSIONLINE);
			
			PreparedStatement pstm = con.prepareStatement(selectSQL);
			ResultSet rs = pstm.executeQuery();
			ReleasePageDTO pageDTO = null;
			
			while (rs.next()) {
				int id = rs.getInt("page_id");
				pageDTO = new ReleasePageDTO();
				pageDTO.setPageId(id);
				pageDTO.setParentId(-1);
				pageDTO.setPageURI(rs.getString("page_uri"));
				pageDTO.setMode(rs.getString("mode"));
				pageDTO.setLastUpdate(rs.getLong("last_update"));
				pageDTO.setStatus(rs.getString("status"));
				pageDTO.setBugId(rs.getString("bug_id"));
				pageDTO.setOrphan(true);
				/**
				 * Check last user update
				 */
				PreparedStatement eps = con.prepareStatement(lastUserEditedSQL);
				eps.setInt(1, id);
				eps.setInt(2, id);
				ResultSet eRs = eps.executeQuery();
				if(eRs.next()) {
					System.out.println(eRs.getString("user_id")+"***"+userId);
					if (!userId.equals(eRs.getString("user_id"))) {
						pageDTO.setAbleSend4Review(true);
					}
				}
				PreparedStatement cps = con.prepareStatement(lastUserCheckedSQL);
				cps.setInt(1, id);
				cps.setInt(2, id);
				ResultSet cRs = cps.executeQuery();
				if(cRs.next()) {
					System.out.println(cRs.getString("user_id")+"***"+userId);
					if (!userId.equals(cRs.getString("user_id"))) {
						pageDTO.setAblePassChecking(true);
					}
				}
				eps.close();
				cps.close();
				changedChildPages.add(pageDTO);
			}
			
		}
		catch (SQLException sqle) {
			LOG.error("Error in getAllChangedChildPages: " + sqle.toString());
		}
		finally {
			if (con != null) {
				try {
					connector.close(con);
				} catch (Exception e) {
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}	
		
		LOG.info("Number of child pages which do not have parent: " + changedChildPages.size());
		System.out.println("Number of child pages which do not have parent: " + changedChildPages.size());
		return changedChildPages;		
	}
	
	public static String getOrphanDocumentMode(int pageId) {
		String selectSQL = "SELECT mode FROM bp_release_child_pages";
		String mode = "";
		//LOG.info("updatePageInfoSQL: " + updatePageInfoSQL);
		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(selectSQL);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				mode = rs.getString("mode");
			}
			pstm.close();
			
		} catch (SQLException sqle) {
			LOG.error("Error in getReleasePageByURI: " + sqle.toString());
		} finally {
			if (con != null) {
				try {
					connector.close(con);
				} catch (Exception e) {
					LOG.error("Error in closing MySQL connection: " + e.toString());
				}
			}
		}
		return mode;
	}
	/**
	 * Update publishing page based on page URI
	 * @param con
	 * @param pageId
	 * @param status
	 * @return
	 */
	public static boolean updateOrphanPageInfo (int pageId, String newStatus, String ptbBugId) {		
		String updatePageInfoSQL = "Update bp_release_child_pages set status = ?, 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, ptbBugId);
			//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;
	}
	
	/**
	 * Delete release page by pageId
	 * @return
	 */
	public static boolean removeOrphanReleasePage (int pageId) {
		String deleteReleasePageSQL = "Delete from bp_release_child_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 page by uri
	 * @return
	 */
	public static boolean removeOrphanReleasePages (ArrayList<String> uris) {
		String deleteReleasePageSQL = "Delete from bp_release_child_pages where page_uri in (' '";
		for (int i=0; i<uris.size(); i++) {
			deleteReleasePageSQL += ",'"+uris.get(i)+"'";
		}
		deleteReleasePageSQL += ")";
		LOG.info("SQL: "+deleteReleasePageSQL);
		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;
	}
	
	public static Vector<ReleasePageDTO> getAllApprovedOrphans() {
		Vector<ReleasePageDTO> uris = new Vector<ReleasePageDTO>();
		String selectSQL = "Select page_id,page_uri,mode,bug_id from bp_release_child_pages where status = 'Approved'";
		
		Connection con = null;
		DBConnector connector = DBConnector.getInstance();
		try {
			con = connector.getDBConnFactory(Environment.PENSIONLINE);
			con.setAutoCommit(false);
			
			PreparedStatement pstm = con.prepareStatement(selectSQL);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ReleasePageDTO obj = new ReleasePageDTO();
				obj.setBugId(rs.getString("bug_id"));
				obj.setMode(rs.getString("mode"));
				obj.setPageId(rs.getInt("page_id"));
				obj.setPageURI(rs.getString("page_uri"));
				uris.add(obj);
			}
			con.commit();
			con.setAutoCommit(true);

		} catch (SQLException sqle) {
			LOG.error("Error in getAllApprovedOrphans: " + sqle.toString());
			try {
				con.rollback();
			} catch (Exception e) {
				LOG.error("Error in getAllApprovedOrphans 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("There are "+uris.size()+" orphan documents ready to publish");
		return uris;
	}
}