PageRenderTime 149ms CodeModel.GetById 3ms app.highlight 127ms RepoModel.GetById 1ms app.codeStats 1ms

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

https://bitbucket.org/tuannguyen/mytest
Java | 3542 lines | 2649 code | 387 blank | 506 comment | 225 complexity | f69505f78083af3cec80e6a20f2c90cd MD5 | raw file
   1package com.bp.pensionline.publishing.database;
   2
   3/*
   4 * This class handle all Database transaction for publishing actions
   5 */
   6
   7import java.sql.Connection;
   8import java.sql.PreparedStatement;
   9import java.sql.ResultSet;
  10import java.sql.SQLException;
  11import java.sql.Statement;
  12import java.util.ArrayList;
  13import java.util.HashSet;
  14import java.util.Vector;
  15
  16import org.apache.commons.logging.Log;
  17import org.opencms.main.CmsLog;
  18
  19import com.bp.pensionline.constants.Environment;
  20import com.bp.pensionline.database.DBConnector;
  21import com.bp.pensionline.publishing.dto.ReleasePackageDTO;
  22import com.bp.pensionline.publishing.dto.ReleasePageDTO;
  23import com.bp.pensionline.publishing.util.DateTimeUtil;
  24
  25public class PublishingToolbarSQLHandler
  26{
  27	public static final Log LOG = CmsLog.getLog(PublishingToolbarSQLHandler.class);
  28	
  29	
  30	/**
  31	 * Get page DTO object from URI
  32	 * @param con
  33	 * @param pageURI
  34	 * @return
  35	 */
  36	public static ReleasePageDTO getReleasePageByURI (String pageURI)
  37	{
  38		String getReleasePageSQL = "Select page_id, mode, status, bug_id, notes, last_update, self_edited from bp_release_pages " +
  39				"where page_uri = ?";
  40		
  41		ReleasePageDTO pageDTO = null;
  42		
  43		Connection con = null;
  44		DBConnector connector = DBConnector.getInstance();
  45		try
  46		{
  47			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
  48			//con = connector.getDBConnFactory(Environment.SQL);
  49			con = connector.getDBConnFactory(Environment.PENSIONLINE);
  50			
  51			PreparedStatement pstm = con.prepareStatement(getReleasePageSQL);
  52			pstm.setString(1, pageURI);
  53			ResultSet rs = pstm.executeQuery();
  54			if (rs.next())
  55			{
  56				pageDTO = new ReleasePageDTO();
  57				pageDTO.setPageId(rs.getInt("page_id"));
  58				pageDTO.setPageURI(pageURI);
  59				pageDTO.setMode(rs.getString("mode"));
  60				pageDTO.setStatus(rs.getString("status"));
  61				pageDTO.setBugId(rs.getString("bug_id"));
  62				pageDTO.setNotes(rs.getString("notes"));
  63				pageDTO.setLastUpdate(rs.getLong("last_update"));
  64				pageDTO.setSelfEdited(rs.getString("self_edited"));
  65			}
  66			
  67			pstm.close();
  68		}
  69		catch (SQLException sqle)
  70		{
  71			LOG.error("Error in getReleasePageByURI: " + sqle.toString()+"\n$SQL: "+getReleasePageSQL);
  72		}
  73		finally
  74		{
  75			if (con != null)
  76			{
  77				try
  78				{
  79					connector.close(con);
  80				}
  81				catch (Exception e)
  82				{
  83					LOG.error("Error in closing MySQL connection: " + e.toString());
  84				}
  85			}
  86		}
  87		
  88		return pageDTO;
  89	}	
  90	
  91	/**
  92	 * Get page DTO object from URI in bp_release_pages
  93	 * @param con
  94	 * @param pageURI
  95	 * @return
  96	 */
  97	public static ReleasePageDTO getReleaseChildPageByURI (String pageURI)
  98	{
  99		String getReleasePageSQL = "Select page_id, mode, last_update from bp_release_child_pages " +
 100				"where page_uri = '" + pageURI + "'";
 101		
 102		ReleasePageDTO pageDTO = null;
 103		Connection con = null;
 104		DBConnector connector = DBConnector.getInstance();
 105		try
 106		{
 107			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
 108			//con = connector.getDBConnFactory(Environment.SQL);
 109			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 110			
 111			PreparedStatement pstm = con.prepareStatement(getReleasePageSQL);
 112			ResultSet rs = pstm.executeQuery();
 113			if (rs.next())
 114			{
 115				pageDTO = new ReleasePageDTO();
 116				pageDTO.setPageId(rs.getInt("page_id"));
 117				pageDTO.setPageURI(pageURI);
 118				pageDTO.setMode(rs.getString("mode"));
 119				pageDTO.setLastUpdate(rs.getLong("last_update"));
 120			}
 121		}
 122		catch (SQLException sqle)
 123		{
 124			LOG.error("Error in getReleaseChildPageByURI: " + sqle.toString());
 125		}
 126		finally
 127		{
 128			if (con != null)
 129			{
 130				try
 131				{
 132					connector.close(con);
 133				}
 134				catch (Exception e)
 135				{
 136					LOG.error("Error in closing MySQL connection: " + e.toString());
 137				}
 138			}
 139		}		
 140		
 141		return pageDTO;
 142	}		
 143	
 144	/**
 145	 * Get all child page in bp_release_page_child of a parent
 146	 * @param con
 147	 * @param pageURI
 148	 * @return
 149	 */
 150	public static Vector<ReleasePageDTO> getChildPagesByParent (int parentId)
 151	{
 152		String getReleasePageSQL = "Select page_id, page_uri, mode, last_update " +
 153				"from bp_release_child_pages c, bp_release_childparent cp " +
 154				"where c.page_id = cp.child_id and cp.parent_id = '" + parentId + "'";
 155		
 156		
 157		Vector<ReleasePageDTO> childPageDTOs = new Vector<ReleasePageDTO>();
 158		Connection con = null;
 159		DBConnector connector = DBConnector.getInstance();
 160		try
 161		{
 162			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
 163			//con = connector.getDBConnFactory(Environment.SQL);
 164			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 165			
 166			
 167			PreparedStatement pstm = con.prepareStatement(getReleasePageSQL);
 168			ResultSet rs = pstm.executeQuery();
 169			while (rs.next())
 170			{
 171				ReleasePageDTO pageDTO = new ReleasePageDTO();
 172				pageDTO = new ReleasePageDTO();
 173				pageDTO.setPageId(rs.getInt("page_id"));
 174				pageDTO.setPageURI(rs.getString("page_uri"));
 175				pageDTO.setMode(rs.getString("mode"));
 176				pageDTO.setLastUpdate(rs.getLong("last_update"));
 177				
 178				childPageDTOs.add(pageDTO);
 179			}
 180		}
 181		catch (SQLException sqle)
 182		{
 183			LOG.error("Error in getReleaseChildPageByParent: " + sqle.toString());
 184		}
 185		finally
 186		{
 187			if (con != null)
 188			{
 189				try
 190				{
 191					connector.close(con);
 192				}
 193				catch (Exception e)
 194				{
 195					LOG.error("Error in closing MySQL connection: " + e.toString());
 196				}
 197			}
 198		}		
 199		
 200		return childPageDTOs;
 201	}			
 202	
 203	/**
 204	 * Get page status from Id
 205	 * @param con
 206	 * @param pageURI
 207	 * @return
 208	 */
 209	public static String getReleasePageStatus (String pageURI)
 210	{
 211		String getPageStatusSQL = "Select status from bp_release_pages where page_uri = '" +
 212			pageURI + "'";
 213		
 214		String status = null;
 215		Connection con = null;
 216		DBConnector connector = DBConnector.getInstance();
 217		try
 218		{		
 219			//con = connector.getDBConnFactory(Environment.SQL);
 220			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 221			
 222			PreparedStatement pstm = con.prepareStatement(getPageStatusSQL);
 223			ResultSet rs = pstm.executeQuery();
 224			if (rs.next())
 225			{
 226				status = rs.getString("status");
 227			}
 228		}
 229		catch (SQLException sqle)
 230		{
 231			LOG.error("Error in getReleasePageStatus " + sqle.toString());
 232		}
 233		finally
 234		{
 235			if (con != null)
 236			{
 237				try
 238				{
 239					connector.close(con);
 240				}
 241				catch (Exception e)
 242				{
 243					LOG.error("Error in closing MySQL connection: " + e.toString());
 244				}
 245			}
 246		}		
 247		return status;
 248	}
 249	
 250	/**
 251	 * Get page last update of page
 252	 * @param con
 253	 * @param pageURI
 254	 * @return
 255	 */
 256	public static long getReleasePageLastUpdate (int pageId)
 257	{
 258		String getPageStatusSQL = "Select last_update from bp_release_pages where page_id = '" +
 259			pageId + "'";
 260		
 261		long lastUpdate = -1;
 262		Connection con = null;
 263		DBConnector connector = DBConnector.getInstance();
 264		try
 265		{		
 266			//con = connector.getDBConnFactory(Environment.SQL);
 267			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 268			
 269			PreparedStatement pstm = con.prepareStatement(getPageStatusSQL);
 270			ResultSet rs = pstm.executeQuery();
 271			if (rs.next())
 272			{
 273				lastUpdate = rs.getLong("last_update");
 274			}
 275		}
 276		catch (SQLException sqle)
 277		{
 278			LOG.error("Error in getReleasePageLastUpdate: " + sqle.toString());
 279		}
 280		finally
 281		{
 282			if (con != null)
 283			{
 284				try
 285				{
 286					connector.close(con);
 287				}
 288				catch (Exception e)
 289				{
 290					LOG.error("Error in closing MySQL connection: " + e.toString());
 291				}
 292			}
 293		}		
 294		return lastUpdate;
 295	}	
 296	
 297	/**
 298	 * Update page's status for HTML page
 299	 * @param con
 300	 * @param pageURI
 301	 * @return
 302	 */
 303	public static boolean updateReleasePageStatus (int pageId, String status)
 304	{
 305		String updatePageStatusSQL = "Update bp_release_pages set status = ? " +
 306			"where page_id = '" + pageId + "' ";
 307		
 308		String updateImpactPageStatusSQL = null;
 309		
 310		if (status != null && status.equals("Editing"))
 311		{
 312			updatePageStatusSQL = "Update bp_release_pages set status = ? " +
 313				"where page_id = '" + pageId + "' and mode != 'Impacted'";
 314			
 315			updateImpactPageStatusSQL = "Update bp_release_pages set status = 'Edited' " +
 316				"where page_id = '" + pageId + "' and mode = 'Impacted'";
 317		}
 318		
 319		boolean updateOk = false;
 320		Connection con = null;
 321		DBConnector connector = DBConnector.getInstance();
 322		try
 323		{		
 324			//con = connector.getDBConnFactory(Environment.SQL);
 325			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 326
 327			con.setAutoCommit(false);
 328			PreparedStatement pstm = con.prepareStatement(updatePageStatusSQL);
 329			pstm.setString(1, status);
 330			pstm.executeUpdate();
 331			
 332			if (updateImpactPageStatusSQL != null)
 333			{
 334				pstm = con.prepareStatement(updateImpactPageStatusSQL);				
 335				pstm.executeUpdate();
 336			}
 337			
 338			con.commit();
 339			con.setAutoCommit(true);
 340			
 341			updateOk = true;
 342		}
 343		catch (SQLException sqle)
 344		{
 345			LOG.error("Error in updateReleasePageStatus: " + sqle.toString());
 346			try
 347			{
 348				con.rollback();
 349			}
 350			catch (Exception e)
 351			{
 352				LOG.error("Error in updateReleasePageStatus rollback: " + e.toString());
 353			}
 354		}
 355		finally
 356		{
 357			if (con != null)
 358			{
 359				try
 360				{
 361					connector.close(con);
 362				}
 363				catch (Exception e)
 364				{
 365					LOG.error("Error in closing MySQL connection: " + e.toString());
 366				}
 367			}
 368		}	
 369		
 370		return updateOk;
 371	}
 372	
 373	/**
 374	 * Update page's status for HTML page
 375	 * @param con
 376	 * @param pageURI
 377	 * @return
 378	 */
 379	public static boolean updateReleasePageToUneditedState (int pageId, boolean isEdited, long lastUpdate)
 380	{
 381		String updatePageSelfEditedSQL = "Update bp_release_pages set self_edited = ?, last_update =? " +
 382			"where page_id = '" + pageId + "' ";
 383		String selfEdited = isEdited ? "Y" : "N";
 384		
 385		boolean updateOk = false;
 386		Connection con = null;
 387		DBConnector connector = DBConnector.getInstance();
 388		try
 389		{		
 390			//con = connector.getDBConnFactory(Environment.SQL);
 391			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 392
 393			con.setAutoCommit(false);
 394			PreparedStatement pstm = con.prepareStatement(updatePageSelfEditedSQL);			
 395			pstm.setString(1, selfEdited);
 396			pstm.setLong(2, lastUpdate);
 397			pstm.executeUpdate();
 398			
 399			con.commit();
 400			con.setAutoCommit(true);
 401			
 402			updateOk = true;
 403		}
 404		catch (SQLException sqle)
 405		{
 406			LOG.error("Error in updateReleasePageSelfEdited: " + sqle.toString());
 407			try
 408			{
 409				con.rollback();
 410			}
 411			catch (Exception e)
 412			{
 413				LOG.error("Error in updateReleasePageSelfEdited rollback: " + e.toString());
 414			}
 415		}
 416		finally
 417		{
 418			if (con != null)
 419			{
 420				try
 421				{
 422					connector.close(con);
 423				}
 424				catch (Exception e)
 425				{
 426					LOG.error("Error in closing MySQL connection: " + e.toString());
 427				}
 428			}
 429		}	
 430		
 431		return updateOk;
 432	}	
 433	
 434	
 435	/**
 436	 * Update page's last_update and status for HTML page
 437	 * @param con
 438	 * @param pageURI
 439	 * @return
 440	 */
 441	public static boolean updateReleasePageStatus (int pageId, String status, long lastUpdate)
 442	{
 443		String updatePageLastUpdateSQL = "Update bp_release_pages set mode = 'Edited', status = ?, last_update = ?, self_edited = 'Y' " +
 444			"where page_id = '" + pageId + "' ";
 445		
 446		boolean updateOk = false;
 447		Connection con = null;
 448		DBConnector connector = DBConnector.getInstance();
 449		try
 450		{		
 451			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 452
 453			con.setAutoCommit(false);
 454			PreparedStatement pstm = con.prepareStatement(updatePageLastUpdateSQL);
 455			pstm.setString(1, status);
 456			pstm.setLong(2, lastUpdate);
 457			pstm.executeUpdate();
 458			
 459			con.commit();
 460			con.setAutoCommit(true);
 461			
 462			updateOk = true;
 463		}
 464		catch (SQLException sqle)
 465		{
 466			LOG.error("Error in updateReleasePageStatus: " + sqle.toString());
 467			try
 468			{
 469				con.rollback();
 470			}
 471			catch (Exception e)
 472			{
 473				LOG.error("Error in updateReleasePageStatus rollback: " + e.toString());
 474			}
 475		}
 476		finally
 477		{
 478			if (con != null)
 479			{
 480				try
 481				{
 482					connector.close(con);
 483				}
 484				catch (Exception e)
 485				{
 486					LOG.error("Error in closing MySQL connection: " + e.toString());
 487				}
 488			}
 489		}	
 490		
 491		return updateOk;
 492	}
 493	
 494	/**
 495	 * Update status for all child page
 496	 * @param con
 497	 * @param pageURI
 498	 * @return
 499	 */
 500	public static boolean updateAllReleaseChildPageInfo (int parentId, String status, String notes, String bugId)
 501	{
 502		String updateAllReleaseChildInfoSQL = "Update bp_release_child_pages set status = ?, bug_id = ?, notes = ?" +
 503			"where parent_id = '" + parentId + "' ";
 504		
 505		boolean updateOk = false;
 506		Connection con = null;
 507		DBConnector connector = DBConnector.getInstance();
 508		try
 509		{		
 510			//con = connector.getDBConnFactory(Environment.SQL);
 511			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 512
 513			con.setAutoCommit(false);
 514			PreparedStatement pstm = con.prepareStatement(updateAllReleaseChildInfoSQL);
 515			pstm.setString(1, status);
 516			pstm.setString(2, bugId);
 517			pstm.setString(3, notes);
 518			pstm.executeUpdate();
 519			
 520			con.commit();
 521			con.setAutoCommit(true);
 522			
 523			updateOk = true;
 524		}
 525		catch (SQLException sqle)
 526		{
 527			LOG.error("Error in updateAllReleaseChildPageInfo: " + sqle.toString());
 528			try
 529			{
 530				con.rollback();
 531			}
 532			catch (Exception e)
 533			{
 534				LOG.error("Error in updateAllReleaseChildPageInfo rollback: " + e.toString());
 535			}			
 536		}
 537		finally
 538		{
 539			if (con != null)
 540			{
 541				try
 542				{
 543					connector.close(con);
 544				}
 545				catch (Exception e)
 546				{
 547					LOG.error("Error in closing MySQL connection: " + e.toString());
 548				}
 549			}
 550		}	
 551		
 552		return updateOk;
 553	}	
 554	
 555	/**
 556	 * Update page's last_update and status for child page
 557	 * @param con
 558	 * @param pageURI
 559	 * @return
 560	 */
 561	public static boolean updateReleaseChildPageMode (int pageId, String mode, long lastUpdate)
 562	{
 563		String updatePageLastUpdateSQL = "Update bp_release_child_pages set mode = ?, last_update = ? " +
 564			"where page_id = '" + pageId + "' ";
 565		
 566		boolean updateOk = false;
 567		Connection con = null;
 568		DBConnector connector = DBConnector.getInstance();
 569		try
 570		{		
 571			//con = connector.getDBConnFactory(Environment.SQL);
 572			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 573
 574			con.setAutoCommit(false);
 575			PreparedStatement pstm = con.prepareStatement(updatePageLastUpdateSQL);
 576			pstm.setString(1, mode);
 577			pstm.setLong(2, lastUpdate);
 578			pstm.executeUpdate();
 579			
 580			con.commit();
 581			con.setAutoCommit(true);
 582			
 583			updateOk = true;
 584		}
 585		catch (SQLException sqle)
 586		{
 587			LOG.error("Error in updateReleaseChildPageStatus: " + sqle.toString());
 588			try
 589			{
 590				con.rollback();
 591			}
 592			catch (Exception e)
 593			{
 594				LOG.error("Error in updateReleaseChildPageStatus rollback: " + e.toString());
 595			}			
 596		}
 597		finally
 598		{
 599			if (con != null)
 600			{
 601				try
 602				{
 603					connector.close(con);
 604				}
 605				catch (Exception e)
 606				{
 607					LOG.error("Error in closing MySQL connection: " + e.toString());
 608				}
 609			}
 610		}	
 611		
 612		return updateOk;
 613	}	
 614	
 615//	/**
 616//	 * Update status for all child page
 617//	 * @param con
 618//	 * @param pageURI
 619//	 * @return
 620//	 */
 621//	public static boolean updateAllReleaseChildPageStatus (int parentId, String status)
 622//	{
 623//		String updatePageLastUpdateSQL = "Update bp_release_child_pages set status = ? " +
 624//			"where parent_id = '" + parentId + "' ";
 625//		
 626//		boolean updateOk = false;
 627//		Connection con = null;
 628//		DBConnector connector = DBConnector.getInstance();
 629//		try
 630//		{		
 631//			//con = connector.getDBConnFactory(Environment.SQL);
 632//			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 633//
 634//			con.setAutoCommit(false);
 635//			PreparedStatement pstm = con.prepareStatement(updatePageLastUpdateSQL);
 636//			pstm.setString(1, status);
 637//			pstm.executeUpdate();
 638//			
 639//			con.commit();
 640//			con.setAutoCommit(true);
 641//			
 642//			updateOk = true;
 643//		}
 644//		catch (SQLException sqle)
 645//		{
 646//			LOG.error("Error in updateAllReleaseChildPageStatus: " + sqle.toString());
 647//			try
 648//			{
 649//				con.rollback();
 650//			}
 651//			catch (Exception e)
 652//			{
 653//				LOG.error("Error in updateAllReleaseChildPageStatus rollback: " + e.toString());
 654//			}			
 655//		}
 656//		finally
 657//		{
 658//			if (con != null)
 659//			{
 660//				try
 661//				{
 662//					connector.close(con);
 663//				}
 664//				catch (Exception e)
 665//				{
 666//					LOG.error("Error in closing MySQL connection: " + e.toString());
 667//				}
 668//			}
 669//		}	
 670//		
 671//		return updateOk;
 672//	}	
 673	
 674	/**
 675	 * Set child page parentId if not existed
 676	 * @param con
 677	 * @param pageURI
 678	 * @return: true if a record is updated, false otherwise
 679	 */
 680	public static boolean addChildToPage (int parentId, int childId)
 681	{
 682		
 683		String checkChildParentExistedSQL = "Select count(*) as num from bp_release_childparent " +
 684			"where parent_id = '" + parentId + "' and child_id = '" + childId + "'";
 685		
 686		String addChildToPageSQL = "Insert into bp_release_childparent values (?, ?)";
 687		
 688		boolean updateOk = false;
 689		Connection con = null;
 690		DBConnector connector = DBConnector.getInstance();
 691		try
 692		{
 693			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 694
 695			PreparedStatement pstmSelect = con.prepareStatement(checkChildParentExistedSQL);
 696			ResultSet rsSelect = pstmSelect.executeQuery();
 697			if (rsSelect.next())
 698			{
 699				int numRecord = rsSelect.getInt("num");
 700				if (numRecord == 0)
 701				{
 702					con.setAutoCommit(false);
 703					PreparedStatement pstm = con.prepareStatement(addChildToPageSQL);
 704					pstm.setInt(1, parentId);
 705					pstm.setInt(2, childId);
 706
 707					pstm.executeUpdate();			
 708					con.commit();
 709					con.setAutoCommit(true);
 710					
 711					updateOk = true;
 712				}
 713			}
 714		}
 715		catch (SQLException sqle)
 716		{
 717			LOG.error("Error in addChildToPage: " + sqle.toString());
 718			try
 719			{
 720				con.rollback();
 721			}
 722			catch (Exception e)
 723			{
 724				LOG.error("Error in addChildToPage rollback: " + e.toString());
 725			}
 726		}
 727		finally
 728		{
 729			if (con != null)
 730			{
 731				try
 732				{
 733					connector.close(con);
 734				}
 735				catch (Exception e)
 736				{
 737					LOG.error("Error in closing MySQL connection: " + e.toString());
 738				}
 739			}
 740		}	
 741		LOG.info("addChildToPage for: " + parentId + " with " + childId+": "+updateOk);
 742		System.out.println("addChildToPage for: " + parentId + " with " + childId+": "+updateOk);
 743		return updateOk;
 744	}		
 745	
 746	/**
 747	 * Update publishing page based on page URI
 748	 * @param con
 749	 * @param pageId
 750	 * @param status
 751	 * @return
 752	 */
 753	public static boolean updateReleasePageInfo (int pageId, String newStatus, String notes, String bugId)
 754	{		
 755		String updatePageInfoSQL = "Update bp_release_pages set status = ?, notes = ?, bug_id = ? " +
 756				"where page_id = '" + pageId + "'";
 757		
 758		//LOG.info("updatePageInfoSQL: " + updatePageInfoSQL);
 759		Connection con = null;
 760		DBConnector connector = DBConnector.getInstance();
 761		boolean updateOk = false;
 762		
 763		try
 764		{
 765			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 766			
 767			con.setAutoCommit(false);
 768			PreparedStatement pstm = con.prepareStatement(updatePageInfoSQL);
 769			
 770			//long lastUpdate = DateTimeUtil.formatToOrderingTime(System.currentTimeMillis());			
 771			pstm.setString(1, newStatus);
 772			pstm.setString(2, notes);
 773			pstm.setString(3, bugId);
 774			//pstm.setLong(4, lastUpdate);
 775
 776			pstm.executeUpdate();
 777			
 778			con.commit();
 779			con.setAutoCommit(true);
 780			
 781			updateOk = true;
 782		}
 783		catch (SQLException sqle)
 784		{
 785			LOG.error("Error in updatePageInfo: " + sqle.toString());
 786			try
 787			{
 788				con.rollback();
 789			}
 790			catch (Exception e)
 791			{
 792				LOG.error("Error in updatePageInfo rollback: " + e.toString());
 793			}
 794		}
 795		finally
 796		{
 797			if (con != null)
 798			{
 799				try
 800				{
 801					connector.close(con);
 802				}
 803				catch (Exception e)
 804				{
 805					LOG.error("Error in closing MySQL connection: " + e.toString());
 806				}
 807			}
 808		}		
 809		return updateOk;
 810	}	
 811	
 812	/**
 813	 * Insert a new release page and returns the page_id of new record inserted
 814	 * @param con
 815	 * @param pageDTO
 816	 */
 817	public static int insertReleaseChildPage (ReleasePageDTO pageDTO)
 818	{
 819		String insertPageSQL = "Insert into bp_release_child_pages " +
 820				"(page_uri, mode, last_update) values (?, ?, ?)";
 821		int pageId = -1;
 822		
 823		Connection con = null;
 824		DBConnector connector = DBConnector.getInstance();
 825		try
 826		{			
 827			//con = connector.getDBConnFactory(Environment.SQL);
 828			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 829			
 830			con.setAutoCommit(false);
 831			PreparedStatement pstm = con.prepareStatement(insertPageSQL);
 832			pstm.setString(1, pageDTO.getPageURI());
 833			pstm.setString(2, pageDTO.getMode());
 834			pstm.setLong(3, pageDTO.getLastUpdate());
 835			pstm.executeUpdate();
 836			
 837			con.commit();
 838			con.setAutoCommit(true);
 839			
 840			String getReleasePageSQL = "Select page_id from bp_release_child_pages where page_uri = '" + pageDTO.getPageURI() + "'";
 841			
 842			PreparedStatement getIdPstm = con.prepareStatement(getReleasePageSQL);
 843			ResultSet rs = getIdPstm.executeQuery();
 844			if (rs.next())
 845			{
 846				pageId = rs.getInt("page_id");
 847			}			
 848		}
 849		catch (SQLException sqle)
 850		{
 851			LOG.error("Error in insertReleaseChildPage: " + sqle.toString());
 852			try
 853			{
 854				con.rollback();
 855			}
 856			catch (Exception e)
 857			{
 858				LOG.error("Error in insertReleaseChildPage rollback: " + e.toString());
 859			}
 860		}
 861		finally
 862		{
 863			if (con != null)
 864			{
 865				try
 866				{
 867					connector.close(con);
 868				}
 869				catch (Exception e)
 870				{
 871					LOG.error("Error in closing MySQL connection: " + e.toString());
 872				}
 873			}
 874		}		
 875		
 876		return pageId;
 877	}	
 878	
 879	/**
 880	 * Insert a new release page and returns the page_id of new record inserted
 881	 * @param con
 882	 * @param pageDTO
 883	 */
 884	public static int insertReleasePage (ReleasePageDTO pageDTO)
 885	{
 886		String insertPageSQL = "Insert into bp_release_pages " +
 887				"(page_uri, mode, status, bug_id, notes, last_update, self_edited) values (?, ?, ?, ?, ?, ?, ?)";
 888		int pageId = -1;
 889		
 890		Connection con = null;
 891		DBConnector connector = DBConnector.getInstance();
 892		try
 893		{
 894			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
 895			//con = connector.getDBConnFactory(Environment.SQL);
 896			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 897			
 898			con.setAutoCommit(false);
 899			PreparedStatement pstm = con.prepareStatement(insertPageSQL);
 900			pstm.setString(1, pageDTO.getPageURI());
 901			pstm.setString(2, pageDTO.getMode());
 902			pstm.setString(3, pageDTO.getStatus());
 903			pstm.setString(4, pageDTO.getBugId());
 904			pstm.setString(5, pageDTO.getNotes());
 905			pstm.setLong(6, pageDTO.getLastUpdate());
 906			pstm.setString(7, pageDTO.getSelfEdited());
 907			
 908			pstm.executeUpdate();
 909			
 910			con.commit();
 911			con.setAutoCommit(true);
 912			
 913			String getReleasePageSQL = "Select page_id from bp_release_pages where page_uri = '" + pageDTO.getPageURI() + "'";
 914			
 915			PreparedStatement getIdPstm = con.prepareStatement(getReleasePageSQL);
 916			ResultSet rs = getIdPstm.executeQuery();
 917			if (rs.next())
 918			{
 919				pageId = rs.getInt("page_id");
 920			}			
 921		}
 922		catch (SQLException sqle)
 923		{
 924			LOG.error("Error in insertReleasePage: " + sqle.toString());
 925			try
 926			{
 927				con.rollback();
 928			}
 929			catch (Exception e)
 930			{
 931				LOG.error("Error in insertReleasePage rollback: " + e.toString());
 932			}
 933		}
 934		finally
 935		{
 936			if (con != null)
 937			{
 938				try
 939				{
 940					connector.close(con);
 941				}
 942				catch (Exception e)
 943				{
 944					LOG.error("Error in closing MySQL connection: " + e.toString());
 945				}
 946			}
 947		}		
 948		
 949		return pageId;
 950	}	
 951	
 952	/**
 953	 * Synchronize editing pages between CMS tables and publishing tables
 954	 */
 955	public static void updatePageSystem (Vector cmsPageURIs)
 956	{
 957		Connection con = null;
 958		DBConnector connector = DBConnector.getInstance();
 959		
 960		try
 961		{
 962			// delete old records
 963			// Implement later 
 964			
 965			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
 966			con = connector.getDBConnFactory(Environment.PENSIONLINE);
 967			
 968			for (int i = 0; i < cmsPageURIs.size(); i++)
 969			{
 970				// check if there is a file in the current release existed in bp_release_pages
 971				String pageURI = (String)cmsPageURIs.elementAt(i);
 972				insertPageToSystem(pageURI);
 973			}			
 974		}
 975		catch (SQLException sqle)
 976		{
 977			LOG.error("Error in creating MySQL connection: " + sqle.toString());
 978		}	
 979		finally
 980		{
 981			if (con != null)
 982			{
 983				try
 984				{
 985					connector.close(con);
 986				}
 987				catch (Exception e)
 988				{
 989					LOG.error("Error in closing MySQL connection: " + e.toString());
 990				}
 991			}
 992		}
 993
 994	}	
 995	
 996	/**
 997	 * Insert a new release page
 998	 * @param con
 999	 * @param pageDTO
1000	 */
1001	public static void insertPageToSystem (String pageURI)
1002	{
1003		String updatePageStatusSQL = "Insert into bp_page_system " +
1004				"(page_uri) values" +
1005				"('" 
1006				+ pageURI + 
1007				"')";
1008		
1009		Connection con = null;
1010		DBConnector connector = DBConnector.getInstance();
1011		
1012		try
1013		{
1014
1015			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1016			//con = connector.getDBConnFactory(Environment.SQL);
1017			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1018			
1019			con.setAutoCommit(false);
1020			PreparedStatement pstm = con.prepareStatement(updatePageStatusSQL);
1021			pstm.executeUpdate();
1022			
1023			con.commit();
1024			con.setAutoCommit(true);
1025		}
1026		catch (SQLException sqle)
1027		{
1028			LOG.error("Error in updatePageStatus: " + sqle.toString());
1029			try
1030			{
1031				con.rollback();
1032			}
1033			catch (Exception e)
1034			{
1035				LOG.error("Error in updatePageStatus rollback: " + e.toString());
1036			}
1037		}
1038		finally
1039		{
1040			if (con != null)
1041			{
1042				try
1043				{
1044					connector.close(con);
1045				}
1046				catch (Exception e)
1047				{
1048					LOG.error("Error in closing MySQL connection: " + e.toString());
1049				}
1050			}
1051		}		
1052	}
1053	
1054	/**
1055	 * This method delete the records in bp_release_pages table that is no longer valid for the publishing.
1056	 * Eg, when a new page is created than deleted or a page marked as delete but then is unmarked by Author
1057	 * @param pageURIs
1058	 */
1059	public static void cleanReleasePages (Vector pageURIs)
1060	{
1061		if (pageURIs == null || pageURIs.size() == 0)
1062		{
1063			return;
1064		}
1065		
1066		// combine URI set
1067		StringBuffer pageSetBuf = new StringBuffer();
1068		pageSetBuf.append("(");
1069		for (int i = 0; i < pageURIs.size(); i++)
1070		{
1071			if (i == pageURIs.size() -1)
1072			{
1073				pageSetBuf.append("'").append(pageURIs.elementAt(i)).append("'");
1074			}
1075			else
1076			{
1077				pageSetBuf.append("'").append(pageURIs.elementAt(i)).append("',");
1078			}
1079		}
1080		pageSetBuf.append(")");
1081		
1082		String cleanQuery = "Delete from bp_release_pages where page_uri not in " + pageSetBuf.toString();
1083
1084		Connection con = null;
1085		DBConnector connector = DBConnector.getInstance();
1086		try
1087		{
1088			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1089			//con = connector.getDBConnFactory(Environment.SQL);
1090			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1091			
1092			con.setAutoCommit(false);
1093			PreparedStatement pstm = con.prepareStatement(cleanQuery);
1094			pstm.executeUpdate();
1095			
1096			con.commit();
1097			con.setAutoCommit(true);
1098		}
1099		catch (SQLException sqle)
1100		{
1101			LOG.error("Error in cleanReleasePages: " + sqle.toString());
1102			try
1103			{
1104				con.rollback();
1105			}
1106			catch (Exception e)
1107			{
1108				LOG.error("Error in cleanReleasePages rollback: " + e.toString());
1109			}
1110		}
1111		finally
1112		{
1113			if (con != null)
1114			{
1115				try
1116				{
1117					connector.close(con);
1118				}
1119				catch (Exception e)
1120				{
1121					LOG.error("Error in closing MySQL connection: " + e.toString());
1122				}
1123			}
1124		}		
1125	}
1126	
1127	/**
1128	 * This method delete the records in bp_release_pages table that is no longer valid for the publishing.
1129	 * Eg, when a new page is created than deleted or a page marked as delete but then is unmarked by Author
1130	 * Dont use this method.
1131	 * @param pageIds
1132	 */
1133	public static void cleanReleaseChildPages (Vector<ReleasePageDTO> pageDTOs, String prefix)
1134	{
1135		if (pageDTOs == null)
1136		{
1137			return;
1138		}
1139		
1140		// combine URI set
1141		StringBuffer pageSetBuf = new StringBuffer();
1142		pageSetBuf.append("(''");
1143		for (int i = 0; i < pageDTOs.size(); i++)
1144		{
1145			ReleasePageDTO childPage = pageDTOs.elementAt(i);
1146			pageSetBuf.append(",'").append(childPage.getPageId()).append("'");
1147		}
1148		pageSetBuf.append(")");
1149		
1150		// clean child pages
1151		String cleanChildQuery = "Delete from bp_release_child_pages where page_id not in " + pageSetBuf.toString() + " and page_uri like '" + prefix + "%'";
1152		String cleanChildParentQuery = "Delete from bp_release_childparent where child_id in " +
1153				"(select page_id from bp_release_child_pages where page_id not in " 
1154				+ pageSetBuf.toString() + " and page_uri like '" + prefix + "%')";		
1155		LOG.info("Clean child pages: " + cleanChildQuery);
1156		LOG.info("Clean child_parent links: " + cleanChildParentQuery);
1157		
1158		Connection con = null;
1159		DBConnector connector = DBConnector.getInstance();
1160		try
1161		{
1162			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1163			//con = connector.getDBConnFactory(Environment.SQL);
1164			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1165			con.setAutoCommit(false);
1166			PreparedStatement pstm = con.prepareStatement(cleanChildParentQuery);
1167			pstm.executeUpdate();			
1168			con.commit();						
1169			
1170			pstm = con.prepareStatement(cleanChildQuery);
1171			pstm.executeUpdate();			
1172			con.commit();	
1173			con.setAutoCommit(true);
1174		}
1175		catch (SQLException sqle)
1176		{
1177			LOG.error("Error in cleanReleaseChildPages: " + sqle.toString());
1178			try
1179			{
1180				con.rollback();
1181			}
1182			catch (Exception e)
1183			{
1184				LOG.error("Error in cleanReleaseChildPages rollback: " + e.toString());
1185			}
1186		}
1187		finally
1188		{
1189			if (con != null)
1190			{
1191				try
1192				{
1193					connector.close(con);
1194				}
1195				catch (Exception e)
1196				{
1197					LOG.error("Error in closing MySQL connection: " + e.toString());
1198				}
1199			}
1200		}		
1201	}	
1202	
1203	/**
1204	 * Record action made with page in publishing
1205	 * @param pageId
1206	 * @param userId
1207	 * @param actionType
1208	 * @param notes
1209	 * @param time Formated long value: YYYYmmddHHmmss
1210	 * @return
1211	 */
1212	public static boolean insertReleaseAction (int pageId, String userId, String status,
1213			String notes, long updateTime)
1214	{
1215		LOG.info("insertReleaseAction():BEGIN");
1216		LOG.info(updateTime);
1217		String insertReleaseActionSQL = "Insert into bp_release_actions " +
1218			"(page_id, user_id, status, notes, action_time) values (?, ?, ?, ?, ?)";
1219
1220		boolean result = false;
1221		
1222		Connection con = null;
1223		DBConnector connector = DBConnector.getInstance();
1224		try
1225		{
1226			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1227			//con = connector.getDBConnFactory(Environment.SQL);
1228			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1229			
1230			con.setAutoCommit(false);
1231			PreparedStatement pstm = con.prepareStatement(insertReleaseActionSQL);
1232			pstm.setInt(1, pageId);
1233			pstm.setString(2, userId);
1234			pstm.setString(3, status);
1235			pstm.setString(4, notes);
1236			pstm.setLong(5, updateTime);
1237			pstm.executeUpdate();
1238			
1239			con.commit();
1240			con.setAutoCommit(true);
1241			result = true;
1242		}
1243		catch (SQLException sqle)
1244		{
1245			LOG.error("Error in insertReleaseAction: " + sqle.toString());
1246			try
1247			{
1248				con.rollback();
1249			}
1250			catch (Exception e)
1251			{
1252				LOG.error("Error in insertReleaseAction rollback: " + e.toString());
1253			}
1254		}
1255		finally
1256		{
1257			if (con != null)
1258			{
1259				try
1260				{
1261					connector.close(con);
1262				}
1263				catch (Exception e)
1264				{
1265					LOG.error("Error in closing MySQL connection: " + e.toString());
1266				}
1267			}
1268		}		
1269		LOG.info("insertReleaseAction():END");
1270		return result;
1271	}
1272	
1273	public static boolean insertOrphanReleaseAction (int pageId, String userId, String status,
1274			String notes, long updateTime)
1275	{
1276		LOG.info("insertOrphanReleaseAction():BEGIN");
1277		LOG.info(updateTime);
1278		String insertReleaseActionSQL = "Insert into bp_release_actions " +
1279			"(page_id, childpage_id, user_id, status, notes, action_time) values (-1, ?, ?, ?, ?, ?)";
1280
1281		boolean result = false;
1282		
1283		Connection con = null;
1284		DBConnector connector = DBConnector.getInstance();
1285		try
1286		{
1287			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1288			//con = connector.getDBConnFactory(Environment.SQL);
1289			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1290			
1291			con.setAutoCommit(false);
1292			PreparedStatement pstm = con.prepareStatement(insertReleaseActionSQL);
1293			pstm.setInt(1, pageId);
1294			pstm.setString(2, userId);
1295			pstm.setString(3, status);
1296			pstm.setString(4, notes);
1297			pstm.setLong(5, updateTime);
1298			pstm.executeUpdate();
1299			
1300			con.commit();
1301			con.setAutoCommit(true);
1302			result = true;
1303		}
1304		catch (SQLException sqle)
1305		{
1306			LOG.error("Error in insertOrphanReleaseAction: " + sqle.toString());
1307			try
1308			{
1309				con.rollback();
1310			}
1311			catch (Exception e)
1312			{
1313				LOG.error("Error in insertOrphanReleaseAction rollback: " + e.toString());
1314			}
1315		}
1316		finally
1317		{
1318			if (con != null)
1319			{
1320				try
1321				{
1322					connector.close(con);
1323				}
1324				catch (Exception e)
1325				{
1326					LOG.error("Error in closing MySQL connection: " + e.toString());
1327				}
1328			}
1329		}		
1330		LOG.info("insertOrphanReleaseAction():END");
1331		return result;
1332	}
1333	
1334	/**
1335	 * Build action histories of this page
1336	 * @param pageId
1337	 * @return
1338	 */
1339	public static String buildActionHistories (int pageId)
1340	{
1341		String selectReleaseActionSQL = "Select notes from bp_release_actions where page_id = '" 
1342			+ pageId + "' order by action_time desc";
1343
1344		StringBuffer actionHisBuf = new StringBuffer();
1345		
1346		Connection con = null;
1347		DBConnector connector = DBConnector.getInstance();
1348		try
1349		{
1350			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1351			//con = connector.getDBConnFactory(Environment.SQL);
1352			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1353			
1354			PreparedStatement pstm = con.prepareStatement(selectReleaseActionSQL);
1355			ResultSet rs = pstm.executeQuery();
1356			
1357			while (rs.next())
1358			{
1359				String notes = rs.getString("notes");
1360				if (notes != null)
1361				{
1362					actionHisBuf.append(notes).append("\n\n");
1363				}
1364			}
1365			
1366			if (actionHisBuf.length() >= 2)
1367			{
1368				actionHisBuf.delete(actionHisBuf.length() - 2, actionHisBuf.length());
1369			}			
1370		}
1371		catch (SQLException sqle)
1372		{
1373			LOG.error("Error in buildActionHistories: " + sqle.toString());
1374		}
1375		finally
1376		{
1377			if (con != null)
1378			{
1379				try
1380				{
1381					connector.close(con);
1382				}
1383				catch (Exception e)
1384				{
1385					LOG.error("Error in closing MySQL connection: " + e.toString());
1386				}
1387			}
1388		}		
1389		
1390		return actionHisBuf.toString();		
1391	}
1392	
1393	/**
1394	 * Build action histories for orphan documents
1395	 * @param pageId
1396	 * @return
1397	 */
1398	public static String buildOrphanActionHistories (int pageId)
1399	{
1400		String selectReleaseActionSQL = "Select notes from bp_release_actions where childpage_id = '" 
1401			+ pageId + "' order by action_time desc";
1402
1403		StringBuffer actionHisBuf = new StringBuffer();
1404		
1405		Connection con = null;
1406		DBConnector connector = DBConnector.getInstance();
1407		try
1408		{
1409			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1410			//con = connector.getDBConnFactory(Environment.SQL);
1411			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1412			
1413			PreparedStatement pstm = con.prepareStatement(selectReleaseActionSQL);
1414			ResultSet rs = pstm.executeQuery();
1415			
1416			while (rs.next())
1417			{
1418				String notes = rs.getString("notes");
1419				if (notes != null)
1420				{
1421					actionHisBuf.append(notes).append("\n\n");
1422				}
1423			}
1424			
1425			if (actionHisBuf.length() >= 2)
1426			{
1427				actionHisBuf.delete(actionHisBuf.length() - 2, actionHisBuf.length());
1428			}			
1429		}
1430		catch (SQLException sqle)
1431		{
1432			LOG.error("Error in buildActionHistories: " + sqle.toString());
1433		}
1434		finally
1435		{
1436			if (con != null)
1437			{
1438				try
1439				{
1440					connector.close(con);
1441				}
1442				catch (Exception e)
1443				{
1444					LOG.error("Error in closing MySQL connection: " + e.toString());
1445				}
1446			}
1447		}		
1448		
1449		return actionHisBuf.toString();		
1450	}
1451	/**
1452	 * Get the last publishing action userId right before the page change to status
1453	 * @param pageId
1454	 * @param status: Status that the page is changed by user
1455	 * @return
1456	 */
1457	public static String getLastPublishingActionUserId (int pageId, String status)
1458	{
1459		String getMaxUpdateActionSQL = "Select max(action_time) as atMax from bp_release_actions where page_id = '" 
1460			+ pageId + "' and status = '" + status + "'";
1461		String getLastUserActionSQL = "Select user_id from bp_release_actions where page_id = '" 
1462			+ pageId + "' and status = '" + status + "' and action_time = ?";		
1463		
1464		Connection con = null;
1465		DBConnector connector = DBConnector.getInstance();
1466		try
1467		{
1468			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1469			//con = connector.getDBConnFactory(Environment.SQL);
1470			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1471			
1472			PreparedStatement pstmGetMax = con.prepareStatement(getMaxUpdateActionSQL);
1473			ResultSet rsGetMax = pstmGetMax.executeQuery();
1474			
1475			if (rsGetMax.next())
1476			{
1477				long maxActionTime = rsGetMax.getLong("atMax");
1478				
1479				// Get user_id from max action time
1480				PreparedStatement pstmLastUser = con.prepareStatement(getLastUserActionSQL);
1481				pstmLastUser.setLong(1, maxActionTime);
1482				
1483				ResultSet rsLastUser = pstmLastUser.executeQuery();	
1484				if (rsLastUser.next())
1485				{
1486					return rsLastUser.getString("user_id");
1487				}
1488			}			
1489		}
1490		catch (SQLException sqle)
1491		{
1492			LOG.error("Error in getLastPublishingActionUserId: " + sqle.toString());
1493		}
1494		finally
1495		{
1496			if (con != null)
1497			{
1498				try
1499				{
1500					connector.close(con);
1501				}
1502				catch (Exception e)
1503				{
1504					LOG.error("Error in closing MySQL connection: " + e.toString());
1505				}
1506			}
1507		}		
1508		
1509		return null;		
1510	}	
1511	
1512	/**
1513	 * Delete release page by pageId
1514	 * @return
1515	 */
1516	public static boolean removeReleasePage (int pageId)
1517	{
1518		String deleteReleasePageSQL = "Delete from bp_release_pages where page_id = '" + pageId + "'";
1519
1520		boolean result = false;
1521		
1522		Connection con = null;
1523		DBConnector connector = DBConnector.getInstance();
1524		try
1525		{
1526			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1527			//con = connector.getDBConnFactory(Environment.SQL);
1528			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1529			
1530			con.setAutoCommit(false);
1531			PreparedStatement pstm = con.prepareStatement(deleteReleasePageSQL);
1532			pstm.executeUpdate();
1533			
1534			con.commit();
1535			con.setAutoCommit(true);
1536			result = true;
1537		}
1538		catch (SQLException sqle)
1539		{
1540			LOG.error("Error in deleteReleasePage: " + sqle.toString());
1541			try
1542			{
1543				con.rollback();
1544			}
1545			catch (Exception e)
1546			{
1547				LOG.error("Error in deleteReleasePage rollback: " + e.toString());
1548			}
1549		}
1550		finally
1551		{
1552			if (con != null)
1553			{
1554				try
1555				{
1556					connector.close(con);
1557				}
1558				catch (Exception e)
1559				{
1560					LOG.error("Error in closing MySQL connection: " + e.toString());
1561				}
1562			}
1563		}		
1564		
1565		return result;
1566	}	
1567	
1568	/**
1569	 * Delete release child page by pageId
1570	 * @return
1571	 */
1572	public static boolean removeReleaseChildPage (int childPageId)
1573	{
1574		String deleteReleaseChildPageSQL = "Delete from bp_release_child_pages where page_id = '" + childPageId + "'";
1575
1576		boolean result = false;
1577		
1578		Connection con = null;
1579		DBConnector connector = DBConnector.getInstance();
1580		try
1581		{
1582			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1583			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1584			
1585			con.setAutoCommit(false);
1586			PreparedStatement pstm = con.prepareStatement(deleteReleaseChildPageSQL);
1587			pstm.executeUpdate();
1588			
1589			con.commit();
1590			con.setAutoCommit(true);
1591			result = true;
1592		}
1593		catch (SQLException sqle)
1594		{
1595			LOG.error("Error in removeReleaseChildPage: " + sqle.toString());
1596			try
1597			{
1598				con.rollback();
1599			}
1600			catch (Exception e)
1601			{
1602				LOG.error("Error in removeReleaseChildPage rollback: " + e.toString());
1603			}
1604		}
1605		finally
1606		{
1607			if (con != null)
1608			{
1609				try
1610				{
1611					connector.close(con);
1612				}
1613				catch (Exception e)
1614				{
1615					LOG.error("Error in closing MySQL connection: " + e.toString());
1616				}
1617			}
1618		}		
1619		
1620		return result;
1621	}	
1622	
1623	/**
1624	 * Delete release actions by pageId
1625	 * @return
1626	 */
1627	public static boolean removeReleaseActions (int pageId)
1628	{
1629		String deleteReleasePageSQL = "Delete from bp_release_actions where page_id = '" + pageId + "'";
1630
1631		boolean result = false;
1632		
1633		Connection con = null;
1634		DBConnector connector = DBConnector.getInstance();
1635		try
1636		{
1637			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1638			//con = connector.getDBConnFactory(Environment.PENSIONLINE);
1639			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1640			
1641			con.setAutoCommit(false);
1642			PreparedStatement pstm = con.prepareStatement(deleteReleasePageSQL);
1643			pstm.executeUpdate();
1644			
1645			con.commit();
1646			con.setAutoCommit(true);
1647			result = true;
1648		}
1649		catch (SQLException sqle)
1650		{
1651			LOG.error("Error in deleteReleaseActions: " + sqle.toString());
1652			try
1653			{
1654				con.rollback();
1655			}
1656			catch (Exception e)
1657			{
1658				LOG.error("Error in deleteReleaseActions rollback: " + e.toString());
1659			}
1660		}
1661		finally
1662		{
1663			if (con != null)
1664			{
1665				try
1666				{
1667					connector.close(con);
1668				}
1669				catch (Exception e)
1670				{
1671					LOG.error("Error in closing MySQL connection: " + e.toString());
1672				}
1673			}
1674		}		
1675		
1676		return result;
1677	}	
1678	
1679	/**
1680	 * Delete release child pages by parentId
1681	 * @return
1682	 */
1683	public static boolean removeReleaseChildPagesByParent (int parentId)
1684	{
1685		String selectChildIdFromParentSQL = "Select child_id from bp_release_childparent " +
1686				"where parent_id = '" + parentId + "'";
1687		String deleteReleaseChildPageSQL = "Delete from bp_release_child_pages where page_id in ";
1688		
1689		String deleteChildParentSQL = "Delete from bp_release_childparent where child_id in ";
1690
1691		boolean result = false;
1692		
1693		Connection con = null;
1694		DBConnector connector = DBConnector.getInstance();
1695		try
1696		{
1697			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1698			//con = connector.getDBConnFactory(Environment.SQL);
1699			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1700			
1701			// get child_id set from parent_id
1702			PreparedStatement pstm = con.prepareStatement(selectChildIdFromParentSQL);
1703			ResultSet rs = pstm.executeQuery();	
1704			StringBuffer inCondStrBuf = new StringBuffer("(''");
1705			while (rs.next())
1706			{
1707				int child_id = rs.getInt("child_id");
1708				inCondStrBuf.append(",'").append(child_id).append("'");
1709			}
1710			inCondStrBuf.append(")");
1711			
1712			con.setAutoCommit(false);
1713			LOG.info("deleteReleaseChildPageSQL: " + deleteReleaseChildPageSQL + inCondStrBuf.toString());
1714			
1715			pstm = con.prepareStatement(deleteReleaseChildPageSQL + inCondStrBuf.toString());
1716			pstm.executeUpdate();
1717			
1718			// delete child-parent relationship
1719			pstm = con.prepareStatement(deleteChildParentSQL + inCondStrBuf.toString());
1720			pstm.executeUpdate();
1721			
1722			con.commit();
1723			con.setAutoCommit(true);
1724			result = true;
1725		}
1726		catch (SQLException sqle)
1727		{
1728			LOG.error("Error in removeReleaseChildPagesByParent: " + sqle.toString());
1729			try
1730			{
1731				con.rollback();
1732			}
1733			catch (Exception e)
1734			{
1735				LOG.error("Error in removeReleaseChildPagesByParent rollback: " + e.toString());
1736			}
1737		}
1738		finally
1739		{
1740			if (con != null)
1741			{
1742				try
1743				{
1744					connector.close(con);
1745				}
1746				catch (Exception e)
1747				{
1748					LOG.error("Error in closing MySQL connection: " + e.toString());
1749				}
1750			}
1751		}		
1752		
1753		return result;
1754	}
1755	
1756	/**
1757	 * Delete release child pages by parentId
1758	 * @return
1759	 */
1760	public static boolean removeAllInvalidParentPages ()
1761	{
1762		String selectAllParentIdSQL = "Select parent_id from bp_release_childparent";
1763		
1764		String deleteInvalidParentPageSQL = "Delete from bp_release_pages where mode = 'Impacted' and " +
1765				"self_edited = 'N' and page_id not in ";
1766		
1767
1768		boolean result = false;
1769		
1770		Connection con = null;
1771		DBConnector connector = DBConnector.getInstance();
1772		try
1773		{
1774			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1775			//con = connector.getDBConnFactory(Environment.SQL);
1776			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1777			
1778			// get child_id set from parent_id
1779			PreparedStatement pstm = con.prepareStatement(selectAllParentIdSQL);
1780			ResultSet rs = pstm.executeQuery();	
1781			StringBuffer inCondStrBuf = new StringBuffer("(''");
1782			while (rs.next())
1783			{
1784				int parent_id = rs.getInt("parent_id");
1785				inCondStrBuf.append(",'").append(parent_id).append("'");
1786			}
1787			inCondStrBuf.append(")");
1788			
1789			con.setAutoCommit(false);
1790			LOG.info("deleteInvalidParentPageSQL: " + deleteInvalidParentPageSQL + inCondStrBuf.toString());
1791			
1792			pstm = con.prepareStatement(deleteInvalidParentPageSQL + inCondStrBuf.toString());
1793			pstm.executeUpdate();			
1794			
1795			con.commit();
1796			con.setAutoCommit(true);
1797			result = true;
1798		}
1799		catch (SQLException sqle)
1800		{
1801			LOG.error("Error in removeAllInvalidParentPages: " + sqle.toString());
1802			try
1803			{
1804				con.rollback();
1805			}
1806			catch (Exception e)
1807			{
1808				LOG.error("Error in removeAllInvalidParentPages rollback: " + e.toString());
1809			}
1810		}
1811		finally
1812		{
1813			if (con != null)
1814			{
1815				try
1816				{
1817					connector.close(con);
1818				}
1819				catch (Exception e)
1820				{
1821					LOG.error("Error in closing MySQL connection: " + e.toString());
1822				}
1823			}
1824		}		
1825		
1826		return result;
1827	}	
1828
1829	
1830	/**
1831	 * Get all approved pages for deploy
1832	 * @return
1833	 */
1834	public static Vector<ReleasePageDTO> getAllApprovedParentPages ()
1835	{
1836		String selectAllApprovedPagesSQL = "Select page_id, page_uri, mode, status, bug_id, notes, last_update " +
1837				"from bp_release_pages where status = 'Approved'";
1838
1839		Vector<ReleasePageDTO> approvedPages = new Vector<ReleasePageDTO>();
1840		
1841		Connection con = null;
1842		DBConnector connector = DBConnector.getInstance();
1843		try
1844		{
1845			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1846			//con = connector.getDBConnFactory(Environment.SQL);
1847			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1848			
1849			PreparedStatement pstm = con.prepareStatement(selectAllApprovedPagesSQL);
1850			ResultSet rs = pstm.executeQuery();
1851			ReleasePageDTO pageDTO = null;
1852			
1853			while (rs.next())
1854			{
1855				pageDTO = new ReleasePageDTO();
1856				pageDTO.setPageId(rs.getInt("page_id"));
1857				pageDTO.setParentId(-1);
1858				pageDTO.setPageURI(rs.getString("page_uri"));
1859				pageDTO.setMode(rs.getString("mode"));
1860				pageDTO.setStatus(rs.getString("status"));
1861				pageDTO.setBugId(rs.getString("bug_id"));
1862				pageDTO.setNotes(rs.getString("notes"));
1863				pageDTO.setLastUpdate(rs.getLong("last_update"));
1864				pageDTO.setSelfEdited("Y");
1865				
1866				approvedPages.add(pageDTO);
1867			}
1868			
1869		}
1870		catch (SQLException sqle)
1871		{
1872			LOG.error("Error in getAllApprovedParentPages: " + sqle.toString());
1873		}
1874		finally
1875		{
1876			if (con != null)
1877			{
1878				try
1879				{
1880					connector.close(con);
1881				}
1882				catch (Exception e)
1883				{
1884					LOG.error("Error in closing MySQL connection: " + e.toString());
1885				}
1886			}
1887		}	
1888		
1889		return approvedPages;
1890	}	
1891	
1892	/**
1893	 * Check if a parent page is Ok to deploy:
1894	 * - It has no children that whose's parent are not approved
1895	 * - It has status of 'Approved'
1896	 * @return
1897	 */
1898	public static boolean isParentPageReadyToDeploy (int parentId)
1899	{
1900		String checkAllChildren = "select count(*) as numC from bp_release_childparent cp where cp.parent_id = '" + parentId + "'";
1901		
1902		String checkParentStatus = "select status from bp_release_pages where page_id = '" + parentId + "'";
1903		
1904		String selectChildIdFromParentSQL = "Select child_id from bp_release_childparent " +
1905			"where parent_id = '" + parentId + "'";
1906		
1907		String checkAllParentApproved = "Select count(*) as num from bp_release_pages p inner join bp_release_childparent cp on (p.page_id = cp.parent_id) " +
1908				"where p.status != 'Approved' and p.status != 'Deployed' and cp.child_id in (" +
1909				selectChildIdFromParentSQL +")";
1910		
1911		boolean result = false;
1912		
1913		Connection con = null;
1914		DBConnector connector = DBConnector.getInstance();
1915		try
1916		{
1917			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
1918			//con = connector.getDBConnFactory(Environment.SQL);
1919			con = connector.getDBConnFactory(Environment.PENSIONLINE);
1920			
1921			// get child_id set from parent_id
1922			PreparedStatement pstm = con.prepareStatement(checkAllChildren);
1923			ResultSet rs = pstm.executeQuery();			
1924			if (rs.next())
1925			{
1926				int numC = rs.getInt("numC");
1927				rs.close();
1928				pstm.close();
1929				
1930				if (numC > 0)
1931				{
1932					// Check other parent of their children
1933					pstm = con.prepareStatement(selectChildIdFromParentSQL);
1934					rs = pstm.executeQuery();	
1935					
1936					//LOG.info("checkAllParentApproved: " + checkAllParentApproved);
1937					
1938					pstm = con.prepareStatement(checkAllParentApproved);
1939					rs = pstm.executeQuery();
1940					if (rs.next())
1941					{
1942						int num = rs.getInt("num");
1943						if (num == 0)
1944						{
1945							result = true;
1946						}
1947					}					
1948				}
1949				else
1950				{
1951					pstm = con.prepareStatement(checkParentStatus);
1952					rs = pstm.executeQuery();
1953					
1954					if (rs.next())
1955					{
1956						String status = rs.getString("status");
1957						if (status != null && (status.equals("Approved") || status.equals("Deployed")))
1958						{
1959							result = true;
1960						}
1961					}					
1962				}
1963				
1964			}			
1965		}
1966		catch (SQLException sqle)
1967		{
1968			LOG.error("Error in removeReleaseChildPagesByParent: " + sqle.toString());
1969		}
1970		finally
1971		{
1972			if (con != null)
1973			{
1974				try
1975				{
1976					connector.close(con);
1977				}
1978				catch (Exception e)
1979				{
1980					LOG.error("Error in closing MySQL connection: " + e.toString());
1981				}
1982			}
1983		}		
1984
1985		
1986		return result;
1987	}
1988	
1989	/**
1990	 * Check if a child page is Ok to deploy:
1991	 * - Has at least 1 parent
1992	 * - All parent are approved
1993	 * @return
1994	 */
1995	public static boolean isChildPageReadyToDeploy (int childPageId)
1996	{		
1997		String checkAllParentApproved = "Select count(*) as numAP from bp_release_pages p inner join bp_release_childparent cp on (p.page_id = cp.parent_id) " +
1998				"where (p.status = 'Approved' or p.status = 'Deployed') and cp.child_id = '" + childPageId + "'";
1999		
2000		String checkAllParent = "select count(*) as numP from bp_release_childparent cp where cp.child_id = '" + childPageId + "'";
2001		
2002		boolean result = false;
2003		
2004		Connection con = null;
2005		DBConnector connector = DBConnector.getInstance();
2006		try
2007		{
2008			int numParentApproved = 0;
2009			int numAllParent = 0;
2010			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2011			//con = connector.getDBConnFactory(Environment.SQL);
2012			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2013			
2014			// get child_id set from parent_id
2015			PreparedStatement pstm = con.prepareStatement(checkAllParentApproved);
2016			ResultSet rs = pstm.executeQuery();	
2017			
2018			if (rs.next())
2019			{
2020				numParentApproved = rs.getInt("numAP");
2021			}
2022			
2023			pstm = con.prepareStatement(checkAllParent);
2024			rs = pstm.executeQuery();
2025			if (rs.next())
2026			{
2027				numAllParent = rs.getInt("numP");
2028			}
2029			
2030			if (numParentApproved > 0 && numParentApproved == numAllParent)
2031			{
2032				result = true;
2033			}
2034		}
2035		catch (SQLException sqle)
2036		{
2037			LOG.error("Error in removeReleaseChildPagesByParent: " + sqle.toString());
2038		}
2039		finally
2040		{
2041			if (con != null)
2042			{
2043				try
2044				{
2045					connector.close(con);
2046				}
2047				catch (Exception e)
2048				{
2049					LOG.error("Error in closing MySQL connection: " + e.toString());
2050				}
2051			}
2052		}		
2053
2054		
2055		return result;
2056	}	
2057	
2058	/**
2059	 * Check if a child page has been deployed:
2060	 * - Has at least 1 parent
2061	 * - All parent are approved
2062	 * @return
2063	 */
2064	public static boolean isChildPageDeployed (int childPageId)
2065	{		
2066		String checkAllParentDeployed = "Select count(*) as numDP from bp_release_pages p inner join bp_release_childparent cp on (p.page_id = cp.parent_id) " +
2067				"where p.status = 'Deployed' and cp.child_id = '" + childPageId + "'";
2068		
2069		String checkAllParent = "select count(*) as numP from bp_release_childparent cp where cp.child_id = '" + childPageId + "'";
2070		
2071		boolean result = false;
2072		
2073		Connection con = null;
2074		DBConnector connector = DBConnector.getInstance();
2075		try
2076		{
2077			int numParentApproved = 0;
2078			int numAllParent = 0;
2079			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2080			//con = connector.getDBConnFactory(Environment.SQL);
2081			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2082			
2083			// get child_id set from parent_id
2084			PreparedStatement pstm = con.prepareStatement(checkAllParentDeployed);
2085			ResultSet rs = pstm.executeQuery();	
2086			
2087			if (rs.next())
2088			{
2089				numParentApproved = rs.getInt("numDP");
2090			}
2091			
2092			pstm = con.prepareStatement(checkAllParent);
2093			rs = pstm.executeQuery();
2094			if (rs.next())
2095			{
2096				numAllParent = rs.getInt("numP");
2097			}
2098			
2099			if (numParentApproved > 0 && numParentApproved == numAllParent)
2100			{
2101				result = true;
2102			}
2103		}
2104		catch (SQLException sqle)
2105		{
2106			LOG.error("Error in removeReleaseChildPagesByParent: " + sqle.toString());
2107		}
2108		finally
2109		{
2110			if (con != null)
2111			{
2112				try
2113				{
2114					connector.close(con);
2115				}
2116				catch (Exception e)
2117				{
2118					LOG.error("Error in closing MySQL connection: " + e.toString());
2119				}
2120			}
2121		}		
2122
2123		
2124		return result;
2125	}		
2126	
2127	/**
2128	 * Check if there is deployable pages in the system
2129	 * @return
2130	 */
2131	public static boolean hasDeployablePages ()
2132	{
2133		String selectApprovedPageIdsSQL = "Select page_id " +
2134			"from bp_release_pages where status = 'Approved'";
2135		
2136		Connection con = null;
2137		DBConnector connector = DBConnector.getInstance();
2138		boolean result = false;
2139		try
2140		{
2141			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2142			//con = connector.getDBConnFactory(Environment.SQL);
2143			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2144			
2145			PreparedStatement pstm = con.prepareStatement(selectApprovedPageIdsSQL);
2146			ResultSet rs = pstm.executeQuery();
2147			
2148			while (rs.next())
2149			{
2150				int pageId = rs.getInt("page_id");
2151				if (isParentPageReadyToDeploy(pageId))
2152				{
2153					result = true;
2154					break;
2155				}
2156			}
2157			
2158			Statement stat = con.createStatement();
2159			ResultSet rst = stat.executeQuery("SELECT * FROM bp_release_child_pages where status='Approved'");
2160			if (rst.next()) {
2161				return true;
2162			}
2163		}
2164		catch (SQLException sqle)
2165		{
2166			LOG.error("Error in hasDeployablePages: " + sqle.toString());
2167		}
2168		finally
2169		{
2170			if (con != null)
2171			{
2172				try
2173				{
2174					connector.close(con);
2175				}
2176				catch (Exception e)
2177				{
2178					LOG.error("Error in closing MySQL connection: " + e.toString());
2179				}
2180			}
2181		}		
2182		
2183		return result;
2184	}
2185	
2186	/**
2187	 * Create a release package by inserting a new record to bp_releases table
2188	 * @param packageName
2189	 * @return Id of new package inserted
2190	 */
2191	public static int createReleasePackage(String packageName, String notes)
2192	{
2193		if (packageName == null || packageName.trim().equals(""))
2194		{
2195			return -1;
2196		}
2197		
2198		String insertReleaseSQL = "Insert into bp_releases (released, package_name, notes) values (?, ?, ?)";
2199		int releaseId = -1;
2200		
2201		Connection con = null;
2202		DBConnector connector = DBConnector.getInstance();
2203		try
2204		{
2205			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2206			//con = connector.getDBConnFactory(Environment.SQL);
2207			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2208			
2209			con.setAutoCommit(false);
2210			PreparedStatement pstm = con.prepareStatement(insertReleaseSQL);
2211			long released = DateTimeUtil.formatToOrderingTime(System.currentTimeMillis());
2212			pstm.setLong(1, released);
2213			pstm.setString(2, packageName);
2214			pstm.setString(3, notes);
2215			pstm.executeUpdate();
2216			
2217			con.commit();
2218			con.setAutoCommit(true);
2219			
2220			String getReleasePageSQL = "Select release_id from bp_releases where package_name = '" + packageName + "'";
2221			
2222			PreparedStatement getIdPstm = con.prepareStatement(getReleasePageSQL);
2223			ResultSet rs = getIdPstm.executeQuery();
2224			if (rs.next())
2225			{
2226				releaseId = rs.getInt("release_id");
2227			}			
2228		}
2229		catch (SQLException sqle)
2230		{
2231			LOG.error("Error in createReleasePackage: " + sqle.toString());
2232			try
2233			{
2234				con.rollback();
2235			}
2236			catch (Exception e)
2237			{
2238				LOG.error("Error in createReleasePackage rollback: " + e.toString());
2239			}
2240		}
2241		finally
2242		{
2243			if (con != null)
2244			{
2245				try
2246				{
2247					connector.close(con);
2248				}
2249				catch (Exception e)
2250				{
2251					LOG.error("Error in closing MySQL connection: " + e.toString());
2252				}
2253			}
2254		}		
2255		
2256		return releaseId;		
2257	}
2258	
2259	/**
2260	 * Create a release note by inserting a new record to bp_notes table
2261	 * @param packageName
2262	 * @param releasePageDTO
2263	 */
2264	public static void createReleaseNote (int releaseId, ReleasePageDTO releasePageDTO, Vector<ReleasePageDTO> childPages)
2265	{
2266		LOG.info("createReleaseNote():BEGIN");
2267		if (releaseId < 0 || releasePageDTO == null)
2268		{
2269			return;
2270		}
2271		
2272		String insertReleaseNoteSQL = "Insert into bp_release_notes " +
2273				"(release_id, page_id, parent_id, page_uri, page_ext, mode, bug_id, is_embeded) " +
2274				"values (?, ?, ?, ?, ?, ?, ?, ?)";
2275		String updatePageSQL = "Update bp_release_pages set status = 'Deployed' where page_id = ?";	
2276		
2277		Connection con = null;
2278		DBConnector connector = DBConnector.getInstance();
2279		try
2280		{
2281			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2282			//con = connector.getDBConnFactory(Environment.SQL);
2283			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2284			
2285			con.setAutoCommit(false);
2286			// insert note for parentPage
2287			PreparedStatement pstm = null;
2288			
2289			if (releasePageDTO.getMode() != null && !releasePageDTO.getMode().equals("Impacted") && 
2290					releasePageDTO.getSelfEdited() != null && releasePageDTO.getSelfEdited().equals("Y"))
2291			{
2292				pstm =con.prepareStatement(insertReleaseNoteSQL);			
2293				pstm.setInt(1, releaseId);
2294				pstm.setInt(2, releasePageDTO.getPageId());
2295				pstm.setInt(3, -1);
2296				pstm.setString(4, releasePageDTO.getPageURI());
2297				String pageURI = releasePageDTO.getPageURI();
2298				String pageExt = "Unknown";
2299				if (pageURI != null && pageURI.lastIndexOf('.') > -1)
2300				{
2301					pageExt = pageURI.substring(pageURI.lastIndexOf('.') + 1);
2302				}
2303				pstm.setString(5, pageExt);
2304				pstm.setString(6, releasePageDTO.getMode());
2305				pstm.setString(7, releasePageDTO.getBugId());
2306				pstm.setString(8, "N");
2307				
2308				pstm.executeUpdate();
2309			}
2310			
2311			int parentId = releasePageDTO.getPageId();
2312			
2313			// update page status to 'Deployed' for both self_edited is true or false
2314			pstm = con.prepareStatement(updatePageSQL);
2315			pstm.setInt(1, releasePageDTO.getPageId());
2316			
2317			pstm.executeUpdate();			
2318			
2319			// insert note for child pages. If parent page is not self_edited consider these children as
2320			// orphan
2321			if (childPages != null)
2322			{
2323				for (int i = 0; i < childPages.size(); i++)
2324				{
2325					ReleasePageDTO childPage = (ReleasePageDTO)childPages.elementAt(i);
2326					if (childPage != null)
2327					{
2328						pstm = con.prepareStatement(insertReleaseNoteSQL);
2329						pstm.setInt(1, releaseId);
2330						pstm.setInt(2, childPage.getPageId());
2331						pstm.setInt(3, parentId);
2332						pstm.setString(4, childPage.getPageURI());
2333						String childPageURI = childPage.getPageURI();
2334						String childPageExt = "Unknown";
2335						if (childPageURI != null && childPageURI.lastIndexOf('.') > -1)
2336						{
2337							childPageExt = childPageURI.substring(childPageURI.lastIndexOf('.') + 1);
2338						}
2339						pstm.setString(5, childPageExt);
2340						
2341						// inherit from parent
2342						pstm.setString(6, childPage.getMode());
2343						pstm.setString(7, releasePageDTO.getBugId());
2344						pstm.setString(8, "Y");
2345						
2346						pstm.executeUpdate();					
2347					}
2348				}
2349			}
2350			
2351			con.commit();
2352			
2353			// Update page status to deployed
2354			con.setAutoCommit(true);	
2355			LOG.info("createReleaseNote():END");
2356		}
2357		catch (SQLException sqle)
2358		{
2359			LOG.error("Error in createReleaseNote: " + sqle.toString());
2360			try
2361			{
2362				con.rollback();
2363			}
2364			catch (Exception e)
2365			{
2366				LOG.error("Error in createReleaseNote rollback: " + e.toString());
2367			}
2368		}
2369		finally
2370		{
2371			if (con != null)
2372			{
2373				try
2374				{
2375					connector.close(con);
2376				}
2377				catch (Exception e)
2378				{
2379					LOG.error("Error in closing MySQL connection: " + e.toString());
2380				}
2381			}
2382		}				
2383	}
2384	
2385//	/**
2386//	 * Delete all pages that are published
2387//	 * @param packageName
2388//	 * @param releasePageDTO
2389//	 */
2390//	public static void cleanUpDeployedPages ()
2391//	{
2392//		String deleteDeployedChildPageSQL = "Delete from bp_release_child_pages where status = 'Deployed'";
2393//		
2394//		String deleteDeployedPageActionSQL = "Delete from bp_release_actions where page_id in " +
2395//				"(select page_id from bp_release_pages where status = 'Deployed')";
2396//		String deleteDeployedPageSQL = "Delete from bp_release_pages where status = 'Deployed'";
2397//		String deleteDeployedChildPageSQL = "Delete from bp_release_child_pages where status = 'Deployed'";
2398//		
2399//		Connection con = null;
2400//		DBConnector connector = DBConnector.getInstance();
2401//		try
2402//		{
2403//			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2404//			//con = connector.getDBConnFactory(Environment.SQL);
2405//			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2406//			
2407//			con.setAutoCommit(false);
2408//			
2409//			PreparedStatement pstm = con.prepareStatement(deleteDeployedPageActionSQL);						
2410//			pstm.executeUpdate();
2411//			
2412//			pstm = con.prepareStatement(deleteDeployedPageSQL);						
2413//			pstm.executeUpdate();
2414//			
2415//			pstm = con.prepareStatement(deleteDeployedChildPageSQL);						
2416//			pstm.executeUpdate();
2417//			
2418//			con.commit();
2419//			con.setAutoCommit(true);						
2420//		}
2421//		catch (SQLException sqle)
2422//		{
2423//			LOG.error("Error in cleanUpPublishedPages: " + sqle.toString());
2424//			try
2425//			{
2426//				con.rollback();
2427//			}
2428//			catch (Exception e)
2429//			{
2430//				LOG.error("Error in cleanUpPublishedPages rollback: " + e.toString());
2431//			}
2432//		}
2433//		finally
2434//		{
2435//			if (con != null)
2436//			{
2437//				try
2438//				{
2439//					connector.close(con);
2440//				}
2441//				catch (Exception e)
2442//				{
2443//					LOG.error("Error in closing MySQL connection: " + e.toString());
2444//				}
2445//			}
2446//		}				
2447//	}	
2448	
2449
2450	/**
2451	 * Get all deployed pages for clean up
2452	 * @return
2453	 */
2454	public static Vector<Integer> getAllDeployedParentPageIds ()
2455	{
2456		String selectAllApprovedPagesSQL = "Select page_id from bp_release_pages where status = 'Deployed'";
2457
2458		Vector<Integer> deployedPageIds = new Vector<Integer>();
2459		
2460		Connection con = null;
2461		DBConnector connector = DBConnector.getInstance();
2462		try
2463		{
2464			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2465			//con = connector.getDBConnFactory(Environment.SQL);
2466			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2467			
2468			PreparedStatement pstm = con.prepareStatement(selectAllApprovedPagesSQL);
2469			ResultSet rs = pstm.executeQuery();
2470			
2471			while (rs.next())
2472			{			
2473				deployedPageIds.add(Integer.valueOf(rs.getInt("page_id")));
2474			}
2475			
2476		}
2477		catch (SQLException sqle)
2478		{
2479			LOG.error("Error in getAllDeployedParentPageIds: " + sqle.toString());
2480		}
2481		finally
2482		{
2483			if (con != null)
2484			{
2485				try
2486				{
2487					connector.close(con);
2488				}
2489				catch (Exception e)
2490				{
2491					LOG.error("Error in closing MySQL connection: " + e.toString());
2492				}
2493			}
2494		}	
2495		
2496		return deployedPageIds;
2497	}
2498	
2499	/**
2500	 * Get all deployed pages for clean up
2501	 * @return
2502	 */
2503	public static ArrayList<String> getDeployedParentURIs ()
2504	{
2505		// only self_edited pages are exported
2506		String selectUndeleteDeployedPagesSQL = "Select page_uri from bp_release_pages " +
2507				"where status = 'Deployed' and mode != 'Impacted' and self_edited = 'Y'";		
2508		
2509
2510		ArrayList<String> undeleteDeployedPages = new ArrayList<String>();
2511		
2512		Connection con = null;
2513		DBConnector connector = DBConnector.getInstance();
2514		try
2515		{
2516			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2517			//con = connector.getDBConnFactory(Environment.SQL);
2518			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2519			
2520			PreparedStatement pstm = con.prepareStatement(selectUndeleteDeployedPagesSQL);
2521			ResultSet rs = pstm.executeQuery();
2522			
2523			while (rs.next())
2524			{
2525				undeleteDeployedPages.add(rs.getString("page_uri"));
2526			}			
2527			
2528		}
2529		catch (SQLException sqle)
2530		{
2531			LOG.error("Error in getUndeltedDeployedPageURIs: " + sqle.toString());
2532		}
2533		finally
2534		{
2535			if (con != null)
2536			{
2537				try
2538				{
2539					connector.close(con);
2540				}
2541				catch (Exception e)
2542				{
2543					LOG.error("Error in closing MySQL connection: " + e.toString());
2544				}
2545			}
2546		}	
2547		
2548		return undeleteDeployedPages;
2549	}	
2550	
2551	/**
2552	 * Log the action of user to the publishing page. This will stay in the database permernantly
2553	 * @param eventTime
2554	 * @param pageURI
2555	 * @param userID
2556	 * @param status
2557	 * @param notes
2558	 */
2559	public static void doAudit(long eventTime, String pageURI, String userID, String status, String notes)
2560	{
2561		if (pageURI == null && userID == null)
2562		{
2563			return;
2564		}
2565		String insertPageSQL = "Insert into bp_release_audit " +
2566			"(event_time, page_uri, user_id, status, notes) values " +
2567			"(?, ?, ?, ?, ?)";
2568		
2569		Connection con = null;
2570		DBConnector connector = DBConnector.getInstance();
2571		try
2572		{
2573			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2574			//con = connector.getDBConnFactory(Environment.SQL);
2575			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2576			
2577			con.setAutoCommit(false);
2578			PreparedStatement pstm = con.prepareStatement(insertPageSQL);
2579			pstm.setLong(1, eventTime);
2580			pstm.setString(2, pageURI);
2581			pstm.setString(3, userID);
2582			pstm.setString(4, status);
2583			pstm.setString(5, notes);
2584			
2585			pstm.executeUpdate();
2586			
2587			con.commit();
2588			con.setAutoCommit(true);		
2589		}
2590		catch (SQLException sqle)
2591		{
2592			LOG.error("Error in doAudit: " + sqle.toString());
2593			try
2594			{
2595				con.rollback();
2596			}
2597			catch (Exception e)
2598			{
2599				LOG.error("Error in doAudit rollback: " + e.toString());
2600			}
2601		}
2602		finally
2603		{
2604			if (con != null)
2605			{
2606				try
2607				{
2608					connector.close(con);
2609				}
2610				catch (Exception e)
2611				{
2612					LOG.error("Error in closing MySQL connection: " + e.toString());
2613				}
2614			}
2615		}			
2616	}
2617	
2618	/**
2619	 * Get all html publishing pages
2620	 * @return
2621	 */
2622	public static Vector<ReleasePageDTO> getAllPublishingPages ()
2623	{
2624		String selectAllPublishingPagesSQL = "Select page_id, page_uri, mode, status, bug_id, notes, last_update, self_edited " +
2625			"from bp_release_pages order by status desc, mode, page_uri";
2626
2627		Vector<ReleasePageDTO> publishingPages = new Vector<ReleasePageDTO>();
2628		
2629		Connection con = null;
2630		DBConnector connector = DBConnector.getInstance();
2631		try
2632		{
2633			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2634			//con = connector.getDBConnFactory(Environment.SQL);
2635			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2636			
2637			PreparedStatement pstm = con.prepareStatement(selectAllPublishingPagesSQL);
2638			ResultSet rs = pstm.executeQuery();
2639			ReleasePageDTO pageDTO = null;
2640			
2641			while (rs.next())
2642			{
2643				pageDTO = new ReleasePageDTO();
2644				pageDTO.setPageId(rs.getInt("page_id"));
2645				pageDTO.setParentId(-1);
2646				pageDTO.setPageURI(rs.getString("page_uri"));
2647				pageDTO.setMode(rs.getString("mode"));
2648				pageDTO.setStatus(rs.getString("status"));
2649				pageDTO.setBugId(rs.getString("bug_id"));
2650				pageDTO.setNotes(rs.getString("notes"));
2651				pageDTO.setLastUpdate(rs.getLong("last_update"));
2652				pageDTO.setSelfEdited(rs.getString("self_edited"));
2653				
2654				publishingPages.add(pageDTO);
2655			}
2656			
2657		}
2658		catch (SQLException sqle)
2659		{
2660			LOG.error("Error in getAllPublishingPages: " + sqle.toString());
2661		}
2662		finally
2663		{
2664			if (con != null)
2665			{
2666				try
2667				{
2668					connector.close(con);
2669				}
2670				catch (Exception e)
2671				{
2672					LOG.error("Error in closing MySQL connection: " + e.toString());
2673				}
2674			}
2675		}	
2676		
2677		return publishingPages;		
2678	}
2679	
2680	/**
2681	 * Get all html publishing child pages
2682	 * @return
2683	 */
2684	public static Vector<ReleasePageDTO> getAllChildPages ()
2685	{
2686		String selectAllOrphanChildPagesSQL = "Select page_id, page_uri, mode, last_update from bp_release_child_pages";
2687
2688		Vector<ReleasePageDTO> childPages = new Vector<ReleasePageDTO>();
2689		
2690		Connection con = null;
2691		DBConnector connector = DBConnector.getInstance();
2692		try
2693		{
2694			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2695			//con = connector.getDBConnFactory(Environment.SQL);
2696			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2697			
2698			PreparedStatement pstm = con.prepareStatement(selectAllOrphanChildPagesSQL);
2699			ResultSet rs = pstm.executeQuery();
2700			ReleasePageDTO pageDTO = null;
2701			
2702			while (rs.next())
2703			{
2704				pageDTO = new ReleasePageDTO();
2705				pageDTO.setPageId(rs.getInt("page_id"));
2706				pageDTO.setPageURI(rs.getString("page_uri"));
2707				pageDTO.setMode(rs.getString("mode"));
2708				pageDTO.setLastUpdate(rs.getLong("last_update"));
2709				
2710				childPages.add(pageDTO);
2711			}
2712			
2713		}
2714		catch (SQLException sqle)
2715		{
2716			LOG.error("Error in getAllChildPages: " + sqle.toString());
2717		}
2718		finally
2719		{
2720			if (con != null)
2721			{
2722				try
2723				{
2724					connector.close(con);
2725				}
2726				catch (Exception e)
2727				{
2728					LOG.error("Error in closing MySQL connection: " + e.toString());
2729				}
2730			}
2731		}	
2732		
2733		return childPages;		
2734	}	
2735	
2736	/**
2737	 * Get all html deleting publishing pages
2738	 * @return
2739	 */
2740	public static Vector getAllDeletingPages ()
2741	{
2742		String selectAllApprovedPagesSQL = "Select page_id, page_uri, status, bug_id, notes, last_update, self_edited " +
2743			"from bp_release_pages where mode = 'Deleted' order by page_uri";
2744
2745		Vector<ReleasePageDTO> deletingPages = new Vector<ReleasePageDTO>();
2746		
2747		Connection con = null;
2748		DBConnector connector = DBConnector.getInstance();
2749		try
2750		{
2751			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2752			//con = connector.getDBConnFactory(Environment.SQL);
2753			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2754			
2755			PreparedStatement pstm = con.prepareStatement(selectAllApprovedPagesSQL);
2756			ResultSet rs = pstm.executeQuery();
2757			ReleasePageDTO pageDTO = null;
2758			
2759			while (rs.next())
2760			{
2761				pageDTO = new ReleasePageDTO();
2762				pageDTO.setPageId(rs.getInt("page_id"));
2763				pageDTO.setParentId(-1);
2764				pageDTO.setPageURI(rs.getString("page_uri"));
2765				pageDTO.setMode("Deleted");
2766				pageDTO.setStatus(rs.getString("status"));
2767				pageDTO.setBugId(rs.getString("bug_id"));
2768				pageDTO.setNotes(rs.getString("notes"));
2769				pageDTO.setLastUpdate(rs.getLong("last_update"));
2770				pageDTO.setSelfEdited(rs.getString("self_edited"));
2771				
2772				deletingPages.add(pageDTO);
2773			}
2774			
2775		}
2776		catch (SQLException sqle)
2777		{
2778			LOG.error("Error in getAllDeletingPages: " + sqle.toString());
2779		}
2780		finally
2781		{
2782			if (con != null)
2783			{
2784				try
2785				{
2786					connector.close(con);
2787				}
2788				catch (Exception e)
2789				{
2790					LOG.error("Error in closing MySQL connection: " + e.toString());
2791				}
2792			}
2793		}	
2794		
2795		return deletingPages;		
2796	}
2797	
2798	/**
2799	 * Get all releases happened in the system
2800	 * @return
2801	 */
2802	public static Vector getAllReleases()
2803	{
2804		String getAllReleasesSQL = "Select release_id, released, package_name, notes from bp_releases order by released desc";
2805
2806		Vector<ReleasePackageDTO> releases = new Vector<ReleasePackageDTO>();
2807		
2808		Connection con = null;
2809		DBConnector connector = DBConnector.getInstance();
2810		try
2811		{
2812			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2813			//con = connector.getDBConnFactory(Environment.SQL);
2814			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2815			
2816			PreparedStatement pstm = con.prepareStatement(getAllReleasesSQL);
2817			ResultSet rs = pstm.executeQuery();
2818			ReleasePackageDTO releaseDTO = null;
2819			
2820			while (rs.next())
2821			{
2822				releaseDTO = new ReleasePackageDTO();
2823				releaseDTO.setReleaseId(rs.getInt("release_id"));
2824				releaseDTO.setReleasedAt(rs.getLong("released"));
2825				releaseDTO.setPackageName(rs.getString("package_name"));
2826				releaseDTO.setNotes(rs.getString("notes"));
2827				
2828				releases.add(releaseDTO);
2829			}
2830			
2831		}
2832		catch (SQLException sqle)
2833		{
2834			LOG.error("Error in getAllReleases: " + sqle.toString());
2835		}
2836		finally
2837		{
2838			if (con != null)
2839			{
2840				try
2841				{
2842					connector.close(con);
2843				}
2844				catch (Exception e)
2845				{
2846					LOG.error("Error in closing MySQL connection: " + e.toString());
2847				}
2848			}
2849		}	
2850	
2851		return releases;		
2852	}
2853	
2854	/**
2855	 * Get all pages released in a released package
2856	 * @return
2857	 */
2858	public static Vector getBugIdsInRelease(int releaseId)
2859	{
2860		String getAllBugIdsSQL = "Select distinct(bug_id) from bp_release_notes where release_id = '" + releaseId +
2861				"' order by bug_id asc";
2862
2863		Vector<String> bugIds = new Vector<String>();
2864		
2865		Connection con = null;
2866		DBConnector connector = DBConnector.getInstance();
2867		try
2868		{
2869			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2870			//con = connector.getDBConnFactory(Environment.SQL);
2871			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2872			
2873			PreparedStatement pstm = con.prepareStatement(getAllBugIdsSQL);
2874			ResultSet rs = pstm.executeQuery();			
2875			
2876			while (rs.next())
2877			{
2878				bugIds.add(rs.getString("bug_id"));
2879			}
2880			
2881		}
2882		catch (SQLException sqle)
2883		{
2884			LOG.error("Error in getBugIdsInRelease: " + sqle.toString());
2885		}
2886		finally
2887		{
2888			if (con != null)
2889			{
2890				try
2891				{
2892					connector.close(con);
2893				}
2894				catch (Exception e)
2895				{
2896					LOG.error("Error in closing MySQL connection: " + e.toString());
2897				}
2898			}
2899		}	
2900	
2901		return bugIds;		
2902	}		
2903	
2904	/**
2905	 * Get all pages released in a released package
2906	 * @return
2907	 */
2908	public static Vector getReleaseDetailsByBugId (int releaseId, String bugId)
2909	{
2910
2911		String getReleaseDetailsSQL = "Select page_id, parent_id, page_uri, page_ext, mode, is_embeded from bp_release_notes " +
2912				"where release_id = '" + releaseId + "' and bug_Id = '" + bugId + "' " +
2913				"and mode != 'Impacted' order by page_ext asc";
2914
2915		Vector<ReleasePageDTO> releaseDetails = new Vector<ReleasePageDTO>();	
2916		//LOG.info("getReleaseDetailsSQL: " + getReleaseDetailsSQL);
2917		
2918		HashSet<String> releasedURIs = new HashSet<String>();
2919		
2920		Connection con = null;
2921		DBConnector connector = DBConnector.getInstance();
2922		try
2923		{
2924			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
2925			//con = connector.getDBConnFactory(Environment.SQL);
2926			con = connector.getDBConnFactory(Environment.PENSIONLINE);
2927			
2928			PreparedStatement pstm = con.prepareStatement(getReleaseDetailsSQL);
2929			ResultSet rs = pstm.executeQuery();
2930			ReleasePageDTO pageDTO = null;
2931			
2932			while (rs.next())
2933			{
2934				String pageURI = rs.getString("page_uri");				
2935				if (!releasedURIs.contains(pageURI))
2936				{
2937					releasedURIs.add(pageURI);
2938					pageDTO = new ReleasePageDTO();
2939					pageDTO.setPageId(rs.getInt("page_id"));
2940					pageDTO.setParentId(rs.getInt("parent_id"));
2941					pageDTO.setPageURI(pageURI);
2942					pageDTO.setMode(rs.getString("mode"));
2943					pageDTO.setStatus("Deployed"); 
2944					pageDTO.setBugId(bugId);
2945					pageDTO.setNotes("Page has been deployed"); // no use
2946					pageDTO.setSelfEdited("Y");	// no use
2947					
2948					releaseDetails.add(pageDTO);
2949				}
2950			}
2951			
2952		}
2953		catch (SQLException sqle)
2954		{
2955			LOG.error("Error in getReleaseDetailsByBugId: " + sqle.toString());
2956		}
2957		finally
2958		{
2959			if (con != null)
2960			{
2961				try
2962				{
2963					connector.close(con);
2964				}
2965				catch (Exception e)
2966				{
2967					LOG.error("Error in closing MySQL connection: " + e.toString());
2968				}
2969			}
2970		}	
2971	
2972		return releaseDetails;		
2973	}	
2974	
2975	/**
2976	 * This method delete the records in bp_release_pages table that is no longer valid for the publishing.
2977	 * Eg, when a new page is created than deleted or a page marked as delete but then is unmarked by Author
2978	 * @param validPageIds
2979	 */
2980	public static void removeOphanPage (int parentId, Vector validPageIds)
2981	{
2982		String cleanQuery = null;
2983		
2984		if (validPageIds == null || validPageIds.size() == 0)
2985		{
2986			cleanQuery = "Delete from bp_release_childparent where parent_id = '" + parentId + "'";
2987		}
2988		else
2989		{
2990			// combine URI set
2991			StringBuffer pageSetBuf = new StringBuffer();
2992			pageSetBuf.append("(");
2993			for (int i = 0; i < validPageIds.size(); i++)
2994			{
2995				if (i == validPageIds.size() -1)
2996				{
2997					pageSetBuf.append("'").append(validPageIds.elementAt(i)).append("'");
2998				}
2999				else
3000				{
3001					pageSetBuf.append("'").append(validPageIds.elementAt(i)).append("',");
3002				}
3003			}
3004			pageSetBuf.append(")");
3005			
3006			// clean child pages
3007			cleanQuery = "Delete from bp_release_childparent where parent_id = '" + parentId + "' " +
3008					"and child_id not in " + pageSetBuf.toString();
3009		}
3010
3011		
3012		Connection con = null;
3013		DBConnector connector = DBConnector.getInstance();
3014		try
3015		{
3016			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
3017			//con = connector.getDBConnFactory(Environment.SQL);
3018			con = connector.getDBConnFactory(Environment.PENSIONLINE);
3019			
3020			con.setAutoCommit(false);
3021			PreparedStatement pstm = con.prepareStatement(cleanQuery);
3022			pstm.executeUpdate();			
3023			con.commit();
3024			
3025		}
3026		catch (SQLException sqle)
3027		{
3028			LOG.error("Error in removeOphanPage: " + sqle.toString());
3029			try
3030			{
3031				con.rollback();
3032			}
3033			catch (Exception e)
3034			{
3035				LOG.error("Error in removeOphanPage rollback: " + e.toString());
3036			}
3037		}
3038		finally
3039		{
3040			if (con != null)
3041			{
3042				try
3043				{
3044					connector.close(con);
3045				}
3046				catch (Exception e)
3047				{
3048					LOG.error("Error in closing MySQL connection: " + e.toString());
3049				}
3050			}
3051		}		
3052	}	
3053	
3054	public static Vector<Integer> getParentPagesOfChild (int childPageId)
3055	{
3056		String getParentPagesOfChildSQL = "Select parent_id from bp_release_childparent " +
3057				"where child_id = '" + childPageId + "'";
3058
3059		Vector<Integer> parentPageIds = new Vector<Integer>();	
3060		//LOG.info("getReleaseDetailsSQL: " + getReleaseDetailsSQL);
3061		
3062		Connection con = null;
3063		DBConnector connector = DBConnector.getInstance();
3064		try
3065		{
3066			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
3067			//con = connector.getDBConnFactory(Environment.SQL);
3068			con = connector.getDBConnFactory(Environment.PENSIONLINE);
3069			
3070			PreparedStatement pstm = con.prepareStatement(getParentPagesOfChildSQL);
3071			ResultSet rs = pstm.executeQuery();
3072			
3073			while (rs.next())
3074			{
3075				int parent_id = rs.getInt("parent_id");
3076				parentPageIds.add(new Integer(parent_id));
3077			}
3078			
3079		}
3080		catch (SQLException sqle)
3081		{
3082			LOG.error("Error in getParentPagesOfChild: " + sqle.toString());
3083		}
3084		finally
3085		{
3086			if (con != null)
3087			{
3088				try
3089				{
3090					connector.close(con);
3091				}
3092				catch (Exception e)
3093				{
3094					LOG.error("Error in closing MySQL connection: " + e.toString());
3095				}
3096			}
3097		}	
3098		
3099		return parentPageIds;		
3100	}
3101	
3102	/**
3103	 * Update all 'Paused' pages to 'Approved' pages
3104	 * @param con
3105	 * @param pageURI
3106	 * @return
3107	 */
3108	public static boolean unpauseAllApprovedPages ()
3109	{
3110		String updatePageStatusSQL = "Update bp_release_pages set status = 'Approved' " +
3111			"where status = 'Paused'";		
3112				
3113		boolean updateOk = false;
3114		Connection con = null;
3115		DBConnector connector = DBConnector.getInstance();
3116		try
3117		{		
3118			//con = connector.getDBConnFactory(Environment.SQL);
3119			con = connector.getDBConnFactory(Environment.PENSIONLINE);
3120
3121			con.setAutoCommit(false);
3122			PreparedStatement pstm = con.prepareStatement(updatePageStatusSQL);
3123			pstm.executeUpdate();
3124			
3125			con.commit();
3126			con.setAutoCommit(true);
3127			
3128			updateOk = true;
3129		}
3130		catch (SQLException sqle)
3131		{
3132			LOG.error("Error in unpauseAllApprovedPages: " + sqle.toString());
3133			try
3134			{
3135				con.rollback();
3136			}
3137			catch (Exception e)
3138			{
3139				LOG.error("Error in unpauseAllApprovedPages rollback: " + e.toString());
3140			}
3141		}
3142		finally
3143		{
3144			if (con != null)
3145			{
3146				try
3147				{
3148					connector.close(con);
3149				}
3150				catch (Exception e)
3151				{
3152					LOG.error("Error in closing MySQL connection: " + e.toString());
3153				}
3154			}
3155		}	
3156		
3157		return updateOk;
3158	}
3159	
3160	/**
3161	 * Update child page's last_update
3162	 * @param con
3163	 * @param pageURI
3164	 * @return
3165	 */
3166	public static boolean updateChildPageLastUpdate (int pageId, long lastUpdate, String mode, String status)
3167	{
3168		String updateChildPageLastUpdateSQL = "Update bp_release_child_pages set last_update = ?, mode = ?, status = ?" +
3169			"where page_id = '" + pageId + "' ";
3170		
3171		boolean updateOk = false;
3172		Connection con = null;
3173		DBConnector connector = DBConnector.getInstance();
3174		try
3175		{		
3176			con = connector.getDBConnFactory(Environment.PENSIONLINE);
3177
3178			con.setAutoCommit(false);
3179			PreparedStatement pstm = con.prepareStatement(updateChildPageLastUpdateSQL);
3180			pstm.setLong(1, lastUpdate);
3181			pstm.setString(2, mode);
3182			pstm.setString(3, status);
3183			pstm.executeUpdate();
3184			
3185			con.commit();
3186			con.setAutoCommit(true);
3187			
3188			updateOk = true;
3189		}
3190		catch (SQLException sqle)
3191		{
3192			LOG.error("Error in updateChildPageLastUpdate: " + sqle.toString());
3193			try
3194			{
3195				con.rollback();
3196			}
3197			catch (Exception e)
3198			{
3199				LOG.error("Error in updateChildPageLastUpdate rollback: " + e.toString());
3200			}
3201		}
3202		finally
3203		{
3204			if (con != null)
3205			{
3206				try
3207				{
3208					connector.close(con);
3209				}
3210				catch (Exception e)
3211				{
3212					LOG.error("Error in closing MySQL connection: " + e.toString());
3213				}
3214			}
3215		}	
3216		
3217		return updateOk;
3218	}	
3219	
3220	
3221	public static void debugPageDTO (ReleasePageDTO pageDTO)
3222	{
3223		if (pageDTO != null)
3224		{
3225			LOG.info("Page Id: " + pageDTO.getPageId());
3226			LOG.info("Page URI: " + pageDTO.getPageURI());
3227			LOG.info("Page Mode: " + pageDTO.getMode());
3228			LOG.info("Page Status: " + pageDTO.getStatus());
3229			LOG.info("Page BugId: " + pageDTO.getBugId());
3230			LOG.info("Page LastModified: " + pageDTO.getLastUpdate());
3231		}
3232	}
3233	
3234	//These methods are used to process changed documents which do not belong to any pages (orphan)
3235	/**
3236	 * Get all changed documents which do not have parent
3237	 * @return
3238	 */
3239	public static Vector<ReleasePageDTO> getAllChangedChildPages(String userId) {
3240		String selectSQL = "Select page_id, page_uri, mode, last_update, status, bug_id" +
3241			" from bp_release_child_pages where page_id not in (SELECT child_id FROM bp_release_childparent) " +
3242			" order by page_uri";
3243		
3244		String lastUserEditedSQL = "Select user_id from bp_release_actions where childpage_id=? " +
3245				                    "and status='Edited' and action_time=(Select max(action_time) as atMax from bp_release_actions where childpage_id=? " +
3246				                    "and status='Edited')";
3247		
3248		String lastUserCheckedSQL = "Select user_id from bp_release_actions where childpage_id=? " +
3249        							"and status='Checked' and action_time=(Select max(action_time) as atMax from bp_release_actions where childpage_id=? " +
3250        							"and status='Checked')";
3251
3252		System.out.println(lastUserEditedSQL);
3253		System.out.println(lastUserCheckedSQL);
3254		Vector<ReleasePageDTO> changedChildPages = new Vector<ReleasePageDTO>();
3255		Connection con = null;
3256		DBConnector connector = DBConnector.getInstance();
3257		try {
3258			LOG.info("SQL: "+selectSQL);
3259			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
3260			//con = connector.getDBConnFactory(Environment.SQL);
3261			con = connector.getDBConnFactory(Environment.PENSIONLINE);
3262			
3263			PreparedStatement pstm = con.prepareStatement(selectSQL);
3264			ResultSet rs = pstm.executeQuery();
3265			ReleasePageDTO pageDTO = null;
3266			
3267			while (rs.next()) {
3268				int id = rs.getInt("page_id");
3269				pageDTO = new ReleasePageDTO();
3270				pageDTO.setPageId(id);
3271				pageDTO.setParentId(-1);
3272				pageDTO.setPageURI(rs.getString("page_uri"));
3273				pageDTO.setMode(rs.getString("mode"));
3274				pageDTO.setLastUpdate(rs.getLong("last_update"));
3275				pageDTO.setStatus(rs.getString("status"));
3276				pageDTO.setBugId(rs.getString("bug_id"));
3277				pageDTO.setOrphan(true);
3278				/**
3279				 * Check last user update
3280				 */
3281				PreparedStatement eps = con.prepareStatement(lastUserEditedSQL);
3282				eps.setInt(1, id);
3283				eps.setInt(2, id);
3284				ResultSet eRs = eps.executeQuery();
3285				if(eRs.next()) {
3286					System.out.println(eRs.getString("user_id")+"***"+userId);
3287					if (!userId.equals(eRs.getString("user_id"))) {
3288						pageDTO.setAbleSend4Review(true);
3289					}
3290				}
3291				PreparedStatement cps = con.prepareStatement(lastUserCheckedSQL);
3292				cps.setInt(1, id);
3293				cps.setInt(2, id);
3294				ResultSet cRs = cps.executeQuery();
3295				if(cRs.next()) {
3296					System.out.println(cRs.getString("user_id")+"***"+userId);
3297					if (!userId.equals(cRs.getString("user_id"))) {
3298						pageDTO.setAblePassChecking(true);
3299					}
3300				}
3301				eps.close();
3302				cps.close();
3303				changedChildPages.add(pageDTO);
3304			}
3305			
3306		}
3307		catch (SQLException sqle) {
3308			LOG.error("Error in getAllChangedChildPages: " + sqle.toString());
3309		}
3310		finally {
3311			if (con != null) {
3312				try {
3313					connector.close(con);
3314				} catch (Exception e) {
3315					LOG.error("Error in closing MySQL connection: " + e.toString());
3316				}
3317			}
3318		}	
3319		
3320		LOG.info("Number of child pages which do not have parent: " + changedChildPages.size());
3321		System.out.println("Number of child pages which do not have parent: " + changedChildPages.size());
3322		return changedChildPages;		
3323	}
3324	
3325	public static String getOrphanDocumentMode(int pageId) {
3326		String selectSQL = "SELECT mode FROM bp_release_child_pages";
3327		String mode = "";
3328		//LOG.info("updatePageInfoSQL: " + updatePageInfoSQL);
3329		Connection con = null;
3330		DBConnector connector = DBConnector.getInstance();
3331		try {
3332			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
3333			//con = connector.getDBConnFactory(Environment.SQL);
3334			con = connector.getDBConnFactory(Environment.PENSIONLINE);
3335			
3336			PreparedStatement pstm = con.prepareStatement(selectSQL);
3337			ResultSet rs = pstm.executeQuery();
3338			if (rs.next()) {
3339				mode = rs.getString("mode");
3340			}
3341			pstm.close();
3342			
3343		} catch (SQLException sqle) {
3344			LOG.error("Error in getReleasePageByURI: " + sqle.toString());
3345		} finally {
3346			if (con != null) {
3347				try {
3348					connector.close(con);
3349				} catch (Exception e) {
3350					LOG.error("Error in closing MySQL connection: " + e.toString());
3351				}
3352			}
3353		}
3354		return mode;
3355	}
3356	/**
3357	 * Update publishing page based on page URI
3358	 * @param con
3359	 * @param pageId
3360	 * @param status
3361	 * @return
3362	 */
3363	public static boolean updateOrphanPageInfo (int pageId, String newStatus, String ptbBugId) {		
3364		String updatePageInfoSQL = "Update bp_release_child_pages set status = ?, bug_id = ? " +
3365								   "where page_id = '" + pageId + "'";
3366		
3367		//LOG.info("updatePageInfoSQL: " + updatePageInfoSQL);
3368		Connection con = null;
3369		DBConnector connector = DBConnector.getInstance();
3370		boolean updateOk = false;
3371		
3372		try {
3373			con = connector.getDBConnFactory(Environment.PENSIONLINE);
3374			
3375			con.setAutoCommit(false);
3376			PreparedStatement pstm = con.prepareStatement(updatePageInfoSQL);
3377			
3378			//long lastUpdate = DateTimeUtil.formatToOrderingTime(System.currentTimeMillis());			
3379			pstm.setString(1, newStatus);
3380			pstm.setString(2, ptbBugId);
3381			//pstm.setLong(4, lastUpdate);
3382
3383			pstm.executeUpdate();
3384			
3385			con.commit();
3386			con.setAutoCommit(true);
3387			
3388			updateOk = true;
3389		} catch (SQLException sqle) {
3390			LOG.error("Error in updatePageInfo: " + sqle.toString());
3391			try {
3392				con.rollback();
3393			} catch (Exception e) {
3394				LOG.error("Error in updatePageInfo rollback: " + e.toString());
3395			}
3396		} finally {
3397			if (con != null) {
3398				try {
3399					connector.close(con);
3400				} catch (Exception e) {
3401					LOG.error("Error in closing MySQL connection: " + e.toString());
3402				}
3403			}
3404		}		
3405		return updateOk;
3406	}
3407	
3408	/**
3409	 * Delete release page by pageId
3410	 * @return
3411	 */
3412	public static boolean removeOrphanReleasePage (int pageId) {
3413		String deleteReleasePageSQL = "Delete from bp_release_child_pages where page_id = '" + pageId + "'";
3414
3415		boolean result = false;
3416		
3417		Connection con = null;
3418		DBConnector connector = DBConnector.getInstance();
3419		try {
3420			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
3421			//con = connector.getDBConnFactory(Environment.SQL);
3422			con = connector.getDBConnFactory(Environment.PENSIONLINE);
3423			
3424			con.setAutoCommit(false);
3425			PreparedStatement pstm = con.prepareStatement(deleteReleasePageSQL);
3426			pstm.executeUpdate();
3427			
3428			con.commit();
3429			con.setAutoCommit(true);
3430			result = true;
3431		} catch (SQLException sqle) {
3432			LOG.error("Error in deleteReleasePage: " + sqle.toString());
3433			try {
3434				con.rollback();
3435			} catch (Exception e) {
3436				LOG.error("Error in deleteReleasePage rollback: " + e.toString());
3437			}
3438		} finally {
3439			if (con != null) {
3440				try {
3441					connector.close(con);
3442				}
3443				catch (Exception e) {
3444					LOG.error("Error in closing MySQL connection: " + e.toString());
3445				}
3446			}
3447		}		
3448		
3449		return result;
3450	}
3451	
3452	/**
3453	 * Delete release page by uri
3454	 * @return
3455	 */
3456	public static boolean removeOrphanReleasePages (ArrayList<String> uris) {
3457		String deleteReleasePageSQL = "Delete from bp_release_child_pages where page_uri in (' '";
3458		for (int i=0; i<uris.size(); i++) {
3459			deleteReleasePageSQL += ",'"+uris.get(i)+"'";
3460		}
3461		deleteReleasePageSQL += ")";
3462		LOG.info("SQL: "+deleteReleasePageSQL);
3463		boolean result = false;
3464		
3465		Connection con = null;
3466		DBConnector connector = DBConnector.getInstance();
3467		try {
3468			//int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;			
3469			//con = connector.getDBConnFactory(Environment.SQL);
3470			con = connector.getDBConnFactory(Environment.PENSIONLINE);
3471			
3472			con.setAutoCommit(false);
3473			PreparedStatement pstm = con.prepareStatement(deleteReleasePageSQL);
3474			pstm.executeUpdate();
3475			
3476			con.commit();
3477			con.setAutoCommit(true);
3478			result = true;
3479		} catch (SQLException sqle) {
3480			LOG.error("Error in deleteReleasePage: " + sqle.toString());
3481			try {
3482				con.rollback();
3483			} catch (Exception e) {
3484				LOG.error("Error in deleteReleasePage rollback: " + e.toString());
3485			}
3486		} finally {
3487			if (con != null) {
3488				try {
3489					connector.close(con);
3490				}
3491				catch (Exception e) {
3492					LOG.error("Error in closing MySQL connection: " + e.toString());
3493				}
3494			}
3495		}		
3496		return result;
3497	}
3498	
3499	public static Vector<ReleasePageDTO> getAllApprovedOrphans() {
3500		Vector<ReleasePageDTO> uris = new Vector<ReleasePageDTO>();
3501		String selectSQL = "Select page_id,page_uri,mode,bug_id from bp_release_child_pages where status = 'Approved'";
3502		
3503		Connection con = null;
3504		DBConnector connector = DBConnector.getInstance();
3505		try {
3506			con = connector.getDBConnFactory(Environment.PENSIONLINE);
3507			con.setAutoCommit(false);
3508			
3509			PreparedStatement pstm = con.prepareStatement(selectSQL);
3510			ResultSet rs = pstm.executeQuery();
3511			while (rs.next()) {
3512				ReleasePageDTO obj = new ReleasePageDTO();
3513				obj.setBugId(rs.getString("bug_id"));
3514				obj.setMode(rs.getString("mode"));
3515				obj.setPageId(rs.getInt("page_id"));
3516				obj.setPageURI(rs.getString("page_uri"));
3517				uris.add(obj);
3518			}
3519			con.commit();
3520			con.setAutoCommit(true);
3521
3522		} catch (SQLException sqle) {
3523			LOG.error("Error in getAllApprovedOrphans: " + sqle.toString());
3524			try {
3525				con.rollback();
3526			} catch (Exception e) {
3527				LOG.error("Error in getAllApprovedOrphans rollback: " + e.toString());
3528			}
3529		} finally {
3530			if (con != null) {
3531				try {
3532					connector.close(con);
3533				}
3534				catch (Exception e) {
3535					LOG.error("Error in closing MySQL connection: " + e.toString());
3536				}
3537			}
3538		}
3539		LOG.info("There are "+uris.size()+" orphan documents ready to publish");
3540		return uris;
3541	}
3542}