PageRenderTime 10ms CodeModel.GetById 7ms app.highlight 96ms RepoModel.GetById 1ms app.codeStats 0ms

/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

Large files files are truncated, but you can click here to view the full 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				

Large files files are truncated, but you can click here to view the full file