PageRenderTime 102ms CodeModel.GetById 18ms RepoModel.GetById 0ms 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

  1. package com.bp.pensionline.publishing.database;
  2. /*
  3. * This class handle all Database transaction for publishing actions
  4. */
  5. import java.sql.Connection;
  6. import java.sql.PreparedStatement;
  7. import java.sql.ResultSet;
  8. import java.sql.SQLException;
  9. import java.sql.Statement;
  10. import java.util.ArrayList;
  11. import java.util.HashSet;
  12. import java.util.Vector;
  13. import org.apache.commons.logging.Log;
  14. import org.opencms.main.CmsLog;
  15. import com.bp.pensionline.constants.Environment;
  16. import com.bp.pensionline.database.DBConnector;
  17. import com.bp.pensionline.publishing.dto.ReleasePackageDTO;
  18. import com.bp.pensionline.publishing.dto.ReleasePageDTO;
  19. import com.bp.pensionline.publishing.util.DateTimeUtil;
  20. public class PublishingToolbarSQLHandler
  21. {
  22. public static final Log LOG = CmsLog.getLog(PublishingToolbarSQLHandler.class);
  23. /**
  24. * Get page DTO object from URI
  25. * @param con
  26. * @param pageURI
  27. * @return
  28. */
  29. public static ReleasePageDTO getReleasePageByURI (String pageURI)
  30. {
  31. String getReleasePageSQL = "Select page_id, mode, status, bug_id, notes, last_update, self_edited from bp_release_pages " +
  32. "where page_uri = ?";
  33. ReleasePageDTO pageDTO = null;
  34. Connection con = null;
  35. DBConnector connector = DBConnector.getInstance();
  36. try
  37. {
  38. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  39. //con = connector.getDBConnFactory(Environment.SQL);
  40. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  41. PreparedStatement pstm = con.prepareStatement(getReleasePageSQL);
  42. pstm.setString(1, pageURI);
  43. ResultSet rs = pstm.executeQuery();
  44. if (rs.next())
  45. {
  46. pageDTO = new ReleasePageDTO();
  47. pageDTO.setPageId(rs.getInt("page_id"));
  48. pageDTO.setPageURI(pageURI);
  49. pageDTO.setMode(rs.getString("mode"));
  50. pageDTO.setStatus(rs.getString("status"));
  51. pageDTO.setBugId(rs.getString("bug_id"));
  52. pageDTO.setNotes(rs.getString("notes"));
  53. pageDTO.setLastUpdate(rs.getLong("last_update"));
  54. pageDTO.setSelfEdited(rs.getString("self_edited"));
  55. }
  56. pstm.close();
  57. }
  58. catch (SQLException sqle)
  59. {
  60. LOG.error("Error in getReleasePageByURI: " + sqle.toString()+"\n$SQL: "+getReleasePageSQL);
  61. }
  62. finally
  63. {
  64. if (con != null)
  65. {
  66. try
  67. {
  68. connector.close(con);
  69. }
  70. catch (Exception e)
  71. {
  72. LOG.error("Error in closing MySQL connection: " + e.toString());
  73. }
  74. }
  75. }
  76. return pageDTO;
  77. }
  78. /**
  79. * Get page DTO object from URI in bp_release_pages
  80. * @param con
  81. * @param pageURI
  82. * @return
  83. */
  84. public static ReleasePageDTO getReleaseChildPageByURI (String pageURI)
  85. {
  86. String getReleasePageSQL = "Select page_id, mode, last_update from bp_release_child_pages " +
  87. "where page_uri = '" + pageURI + "'";
  88. ReleasePageDTO pageDTO = null;
  89. Connection con = null;
  90. DBConnector connector = DBConnector.getInstance();
  91. try
  92. {
  93. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  94. //con = connector.getDBConnFactory(Environment.SQL);
  95. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  96. PreparedStatement pstm = con.prepareStatement(getReleasePageSQL);
  97. ResultSet rs = pstm.executeQuery();
  98. if (rs.next())
  99. {
  100. pageDTO = new ReleasePageDTO();
  101. pageDTO.setPageId(rs.getInt("page_id"));
  102. pageDTO.setPageURI(pageURI);
  103. pageDTO.setMode(rs.getString("mode"));
  104. pageDTO.setLastUpdate(rs.getLong("last_update"));
  105. }
  106. }
  107. catch (SQLException sqle)
  108. {
  109. LOG.error("Error in getReleaseChildPageByURI: " + sqle.toString());
  110. }
  111. finally
  112. {
  113. if (con != null)
  114. {
  115. try
  116. {
  117. connector.close(con);
  118. }
  119. catch (Exception e)
  120. {
  121. LOG.error("Error in closing MySQL connection: " + e.toString());
  122. }
  123. }
  124. }
  125. return pageDTO;
  126. }
  127. /**
  128. * Get all child page in bp_release_page_child of a parent
  129. * @param con
  130. * @param pageURI
  131. * @return
  132. */
  133. public static Vector<ReleasePageDTO> getChildPagesByParent (int parentId)
  134. {
  135. String getReleasePageSQL = "Select page_id, page_uri, mode, last_update " +
  136. "from bp_release_child_pages c, bp_release_childparent cp " +
  137. "where c.page_id = cp.child_id and cp.parent_id = '" + parentId + "'";
  138. Vector<ReleasePageDTO> childPageDTOs = new Vector<ReleasePageDTO>();
  139. Connection con = null;
  140. DBConnector connector = DBConnector.getInstance();
  141. try
  142. {
  143. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  144. //con = connector.getDBConnFactory(Environment.SQL);
  145. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  146. PreparedStatement pstm = con.prepareStatement(getReleasePageSQL);
  147. ResultSet rs = pstm.executeQuery();
  148. while (rs.next())
  149. {
  150. ReleasePageDTO pageDTO = new ReleasePageDTO();
  151. pageDTO = new ReleasePageDTO();
  152. pageDTO.setPageId(rs.getInt("page_id"));
  153. pageDTO.setPageURI(rs.getString("page_uri"));
  154. pageDTO.setMode(rs.getString("mode"));
  155. pageDTO.setLastUpdate(rs.getLong("last_update"));
  156. childPageDTOs.add(pageDTO);
  157. }
  158. }
  159. catch (SQLException sqle)
  160. {
  161. LOG.error("Error in getReleaseChildPageByParent: " + sqle.toString());
  162. }
  163. finally
  164. {
  165. if (con != null)
  166. {
  167. try
  168. {
  169. connector.close(con);
  170. }
  171. catch (Exception e)
  172. {
  173. LOG.error("Error in closing MySQL connection: " + e.toString());
  174. }
  175. }
  176. }
  177. return childPageDTOs;
  178. }
  179. /**
  180. * Get page status from Id
  181. * @param con
  182. * @param pageURI
  183. * @return
  184. */
  185. public static String getReleasePageStatus (String pageURI)
  186. {
  187. String getPageStatusSQL = "Select status from bp_release_pages where page_uri = '" +
  188. pageURI + "'";
  189. String status = null;
  190. Connection con = null;
  191. DBConnector connector = DBConnector.getInstance();
  192. try
  193. {
  194. //con = connector.getDBConnFactory(Environment.SQL);
  195. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  196. PreparedStatement pstm = con.prepareStatement(getPageStatusSQL);
  197. ResultSet rs = pstm.executeQuery();
  198. if (rs.next())
  199. {
  200. status = rs.getString("status");
  201. }
  202. }
  203. catch (SQLException sqle)
  204. {
  205. LOG.error("Error in getReleasePageStatus " + sqle.toString());
  206. }
  207. finally
  208. {
  209. if (con != null)
  210. {
  211. try
  212. {
  213. connector.close(con);
  214. }
  215. catch (Exception e)
  216. {
  217. LOG.error("Error in closing MySQL connection: " + e.toString());
  218. }
  219. }
  220. }
  221. return status;
  222. }
  223. /**
  224. * Get page last update of page
  225. * @param con
  226. * @param pageURI
  227. * @return
  228. */
  229. public static long getReleasePageLastUpdate (int pageId)
  230. {
  231. String getPageStatusSQL = "Select last_update from bp_release_pages where page_id = '" +
  232. pageId + "'";
  233. long lastUpdate = -1;
  234. Connection con = null;
  235. DBConnector connector = DBConnector.getInstance();
  236. try
  237. {
  238. //con = connector.getDBConnFactory(Environment.SQL);
  239. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  240. PreparedStatement pstm = con.prepareStatement(getPageStatusSQL);
  241. ResultSet rs = pstm.executeQuery();
  242. if (rs.next())
  243. {
  244. lastUpdate = rs.getLong("last_update");
  245. }
  246. }
  247. catch (SQLException sqle)
  248. {
  249. LOG.error("Error in getReleasePageLastUpdate: " + sqle.toString());
  250. }
  251. finally
  252. {
  253. if (con != null)
  254. {
  255. try
  256. {
  257. connector.close(con);
  258. }
  259. catch (Exception e)
  260. {
  261. LOG.error("Error in closing MySQL connection: " + e.toString());
  262. }
  263. }
  264. }
  265. return lastUpdate;
  266. }
  267. /**
  268. * Update page's status for HTML page
  269. * @param con
  270. * @param pageURI
  271. * @return
  272. */
  273. public static boolean updateReleasePageStatus (int pageId, String status)
  274. {
  275. String updatePageStatusSQL = "Update bp_release_pages set status = ? " +
  276. "where page_id = '" + pageId + "' ";
  277. String updateImpactPageStatusSQL = null;
  278. if (status != null && status.equals("Editing"))
  279. {
  280. updatePageStatusSQL = "Update bp_release_pages set status = ? " +
  281. "where page_id = '" + pageId + "' and mode != 'Impacted'";
  282. updateImpactPageStatusSQL = "Update bp_release_pages set status = 'Edited' " +
  283. "where page_id = '" + pageId + "' and mode = 'Impacted'";
  284. }
  285. boolean updateOk = false;
  286. Connection con = null;
  287. DBConnector connector = DBConnector.getInstance();
  288. try
  289. {
  290. //con = connector.getDBConnFactory(Environment.SQL);
  291. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  292. con.setAutoCommit(false);
  293. PreparedStatement pstm = con.prepareStatement(updatePageStatusSQL);
  294. pstm.setString(1, status);
  295. pstm.executeUpdate();
  296. if (updateImpactPageStatusSQL != null)
  297. {
  298. pstm = con.prepareStatement(updateImpactPageStatusSQL);
  299. pstm.executeUpdate();
  300. }
  301. con.commit();
  302. con.setAutoCommit(true);
  303. updateOk = true;
  304. }
  305. catch (SQLException sqle)
  306. {
  307. LOG.error("Error in updateReleasePageStatus: " + sqle.toString());
  308. try
  309. {
  310. con.rollback();
  311. }
  312. catch (Exception e)
  313. {
  314. LOG.error("Error in updateReleasePageStatus rollback: " + e.toString());
  315. }
  316. }
  317. finally
  318. {
  319. if (con != null)
  320. {
  321. try
  322. {
  323. connector.close(con);
  324. }
  325. catch (Exception e)
  326. {
  327. LOG.error("Error in closing MySQL connection: " + e.toString());
  328. }
  329. }
  330. }
  331. return updateOk;
  332. }
  333. /**
  334. * Update page's status for HTML page
  335. * @param con
  336. * @param pageURI
  337. * @return
  338. */
  339. public static boolean updateReleasePageToUneditedState (int pageId, boolean isEdited, long lastUpdate)
  340. {
  341. String updatePageSelfEditedSQL = "Update bp_release_pages set self_edited = ?, last_update =? " +
  342. "where page_id = '" + pageId + "' ";
  343. String selfEdited = isEdited ? "Y" : "N";
  344. boolean updateOk = false;
  345. Connection con = null;
  346. DBConnector connector = DBConnector.getInstance();
  347. try
  348. {
  349. //con = connector.getDBConnFactory(Environment.SQL);
  350. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  351. con.setAutoCommit(false);
  352. PreparedStatement pstm = con.prepareStatement(updatePageSelfEditedSQL);
  353. pstm.setString(1, selfEdited);
  354. pstm.setLong(2, lastUpdate);
  355. pstm.executeUpdate();
  356. con.commit();
  357. con.setAutoCommit(true);
  358. updateOk = true;
  359. }
  360. catch (SQLException sqle)
  361. {
  362. LOG.error("Error in updateReleasePageSelfEdited: " + sqle.toString());
  363. try
  364. {
  365. con.rollback();
  366. }
  367. catch (Exception e)
  368. {
  369. LOG.error("Error in updateReleasePageSelfEdited rollback: " + e.toString());
  370. }
  371. }
  372. finally
  373. {
  374. if (con != null)
  375. {
  376. try
  377. {
  378. connector.close(con);
  379. }
  380. catch (Exception e)
  381. {
  382. LOG.error("Error in closing MySQL connection: " + e.toString());
  383. }
  384. }
  385. }
  386. return updateOk;
  387. }
  388. /**
  389. * Update page's last_update and status for HTML page
  390. * @param con
  391. * @param pageURI
  392. * @return
  393. */
  394. public static boolean updateReleasePageStatus (int pageId, String status, long lastUpdate)
  395. {
  396. String updatePageLastUpdateSQL = "Update bp_release_pages set mode = 'Edited', status = ?, last_update = ?, self_edited = 'Y' " +
  397. "where page_id = '" + pageId + "' ";
  398. boolean updateOk = false;
  399. Connection con = null;
  400. DBConnector connector = DBConnector.getInstance();
  401. try
  402. {
  403. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  404. con.setAutoCommit(false);
  405. PreparedStatement pstm = con.prepareStatement(updatePageLastUpdateSQL);
  406. pstm.setString(1, status);
  407. pstm.setLong(2, lastUpdate);
  408. pstm.executeUpdate();
  409. con.commit();
  410. con.setAutoCommit(true);
  411. updateOk = true;
  412. }
  413. catch (SQLException sqle)
  414. {
  415. LOG.error("Error in updateReleasePageStatus: " + sqle.toString());
  416. try
  417. {
  418. con.rollback();
  419. }
  420. catch (Exception e)
  421. {
  422. LOG.error("Error in updateReleasePageStatus rollback: " + e.toString());
  423. }
  424. }
  425. finally
  426. {
  427. if (con != null)
  428. {
  429. try
  430. {
  431. connector.close(con);
  432. }
  433. catch (Exception e)
  434. {
  435. LOG.error("Error in closing MySQL connection: " + e.toString());
  436. }
  437. }
  438. }
  439. return updateOk;
  440. }
  441. /**
  442. * Update status for all child page
  443. * @param con
  444. * @param pageURI
  445. * @return
  446. */
  447. public static boolean updateAllReleaseChildPageInfo (int parentId, String status, String notes, String bugId)
  448. {
  449. String updateAllReleaseChildInfoSQL = "Update bp_release_child_pages set status = ?, bug_id = ?, notes = ?" +
  450. "where parent_id = '" + parentId + "' ";
  451. boolean updateOk = false;
  452. Connection con = null;
  453. DBConnector connector = DBConnector.getInstance();
  454. try
  455. {
  456. //con = connector.getDBConnFactory(Environment.SQL);
  457. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  458. con.setAutoCommit(false);
  459. PreparedStatement pstm = con.prepareStatement(updateAllReleaseChildInfoSQL);
  460. pstm.setString(1, status);
  461. pstm.setString(2, bugId);
  462. pstm.setString(3, notes);
  463. pstm.executeUpdate();
  464. con.commit();
  465. con.setAutoCommit(true);
  466. updateOk = true;
  467. }
  468. catch (SQLException sqle)
  469. {
  470. LOG.error("Error in updateAllReleaseChildPageInfo: " + sqle.toString());
  471. try
  472. {
  473. con.rollback();
  474. }
  475. catch (Exception e)
  476. {
  477. LOG.error("Error in updateAllReleaseChildPageInfo rollback: " + e.toString());
  478. }
  479. }
  480. finally
  481. {
  482. if (con != null)
  483. {
  484. try
  485. {
  486. connector.close(con);
  487. }
  488. catch (Exception e)
  489. {
  490. LOG.error("Error in closing MySQL connection: " + e.toString());
  491. }
  492. }
  493. }
  494. return updateOk;
  495. }
  496. /**
  497. * Update page's last_update and status for child page
  498. * @param con
  499. * @param pageURI
  500. * @return
  501. */
  502. public static boolean updateReleaseChildPageMode (int pageId, String mode, long lastUpdate)
  503. {
  504. String updatePageLastUpdateSQL = "Update bp_release_child_pages set mode = ?, last_update = ? " +
  505. "where page_id = '" + pageId + "' ";
  506. boolean updateOk = false;
  507. Connection con = null;
  508. DBConnector connector = DBConnector.getInstance();
  509. try
  510. {
  511. //con = connector.getDBConnFactory(Environment.SQL);
  512. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  513. con.setAutoCommit(false);
  514. PreparedStatement pstm = con.prepareStatement(updatePageLastUpdateSQL);
  515. pstm.setString(1, mode);
  516. pstm.setLong(2, lastUpdate);
  517. pstm.executeUpdate();
  518. con.commit();
  519. con.setAutoCommit(true);
  520. updateOk = true;
  521. }
  522. catch (SQLException sqle)
  523. {
  524. LOG.error("Error in updateReleaseChildPageStatus: " + sqle.toString());
  525. try
  526. {
  527. con.rollback();
  528. }
  529. catch (Exception e)
  530. {
  531. LOG.error("Error in updateReleaseChildPageStatus rollback: " + e.toString());
  532. }
  533. }
  534. finally
  535. {
  536. if (con != null)
  537. {
  538. try
  539. {
  540. connector.close(con);
  541. }
  542. catch (Exception e)
  543. {
  544. LOG.error("Error in closing MySQL connection: " + e.toString());
  545. }
  546. }
  547. }
  548. return updateOk;
  549. }
  550. // /**
  551. // * Update status for all child page
  552. // * @param con
  553. // * @param pageURI
  554. // * @return
  555. // */
  556. // public static boolean updateAllReleaseChildPageStatus (int parentId, String status)
  557. // {
  558. // String updatePageLastUpdateSQL = "Update bp_release_child_pages set status = ? " +
  559. // "where parent_id = '" + parentId + "' ";
  560. //
  561. // boolean updateOk = false;
  562. // Connection con = null;
  563. // DBConnector connector = DBConnector.getInstance();
  564. // try
  565. // {
  566. // //con = connector.getDBConnFactory(Environment.SQL);
  567. // con = connector.getDBConnFactory(Environment.PENSIONLINE);
  568. //
  569. // con.setAutoCommit(false);
  570. // PreparedStatement pstm = con.prepareStatement(updatePageLastUpdateSQL);
  571. // pstm.setString(1, status);
  572. // pstm.executeUpdate();
  573. //
  574. // con.commit();
  575. // con.setAutoCommit(true);
  576. //
  577. // updateOk = true;
  578. // }
  579. // catch (SQLException sqle)
  580. // {
  581. // LOG.error("Error in updateAllReleaseChildPageStatus: " + sqle.toString());
  582. // try
  583. // {
  584. // con.rollback();
  585. // }
  586. // catch (Exception e)
  587. // {
  588. // LOG.error("Error in updateAllReleaseChildPageStatus rollback: " + e.toString());
  589. // }
  590. // }
  591. // finally
  592. // {
  593. // if (con != null)
  594. // {
  595. // try
  596. // {
  597. // connector.close(con);
  598. // }
  599. // catch (Exception e)
  600. // {
  601. // LOG.error("Error in closing MySQL connection: " + e.toString());
  602. // }
  603. // }
  604. // }
  605. //
  606. // return updateOk;
  607. // }
  608. /**
  609. * Set child page parentId if not existed
  610. * @param con
  611. * @param pageURI
  612. * @return: true if a record is updated, false otherwise
  613. */
  614. public static boolean addChildToPage (int parentId, int childId)
  615. {
  616. String checkChildParentExistedSQL = "Select count(*) as num from bp_release_childparent " +
  617. "where parent_id = '" + parentId + "' and child_id = '" + childId + "'";
  618. String addChildToPageSQL = "Insert into bp_release_childparent values (?, ?)";
  619. boolean updateOk = false;
  620. Connection con = null;
  621. DBConnector connector = DBConnector.getInstance();
  622. try
  623. {
  624. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  625. PreparedStatement pstmSelect = con.prepareStatement(checkChildParentExistedSQL);
  626. ResultSet rsSelect = pstmSelect.executeQuery();
  627. if (rsSelect.next())
  628. {
  629. int numRecord = rsSelect.getInt("num");
  630. if (numRecord == 0)
  631. {
  632. con.setAutoCommit(false);
  633. PreparedStatement pstm = con.prepareStatement(addChildToPageSQL);
  634. pstm.setInt(1, parentId);
  635. pstm.setInt(2, childId);
  636. pstm.executeUpdate();
  637. con.commit();
  638. con.setAutoCommit(true);
  639. updateOk = true;
  640. }
  641. }
  642. }
  643. catch (SQLException sqle)
  644. {
  645. LOG.error("Error in addChildToPage: " + sqle.toString());
  646. try
  647. {
  648. con.rollback();
  649. }
  650. catch (Exception e)
  651. {
  652. LOG.error("Error in addChildToPage rollback: " + e.toString());
  653. }
  654. }
  655. finally
  656. {
  657. if (con != null)
  658. {
  659. try
  660. {
  661. connector.close(con);
  662. }
  663. catch (Exception e)
  664. {
  665. LOG.error("Error in closing MySQL connection: " + e.toString());
  666. }
  667. }
  668. }
  669. LOG.info("addChildToPage for: " + parentId + " with " + childId+": "+updateOk);
  670. System.out.println("addChildToPage for: " + parentId + " with " + childId+": "+updateOk);
  671. return updateOk;
  672. }
  673. /**
  674. * Update publishing page based on page URI
  675. * @param con
  676. * @param pageId
  677. * @param status
  678. * @return
  679. */
  680. public static boolean updateReleasePageInfo (int pageId, String newStatus, String notes, String bugId)
  681. {
  682. String updatePageInfoSQL = "Update bp_release_pages set status = ?, notes = ?, bug_id = ? " +
  683. "where page_id = '" + pageId + "'";
  684. //LOG.info("updatePageInfoSQL: " + updatePageInfoSQL);
  685. Connection con = null;
  686. DBConnector connector = DBConnector.getInstance();
  687. boolean updateOk = false;
  688. try
  689. {
  690. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  691. con.setAutoCommit(false);
  692. PreparedStatement pstm = con.prepareStatement(updatePageInfoSQL);
  693. //long lastUpdate = DateTimeUtil.formatToOrderingTime(System.currentTimeMillis());
  694. pstm.setString(1, newStatus);
  695. pstm.setString(2, notes);
  696. pstm.setString(3, bugId);
  697. //pstm.setLong(4, lastUpdate);
  698. pstm.executeUpdate();
  699. con.commit();
  700. con.setAutoCommit(true);
  701. updateOk = true;
  702. }
  703. catch (SQLException sqle)
  704. {
  705. LOG.error("Error in updatePageInfo: " + sqle.toString());
  706. try
  707. {
  708. con.rollback();
  709. }
  710. catch (Exception e)
  711. {
  712. LOG.error("Error in updatePageInfo rollback: " + e.toString());
  713. }
  714. }
  715. finally
  716. {
  717. if (con != null)
  718. {
  719. try
  720. {
  721. connector.close(con);
  722. }
  723. catch (Exception e)
  724. {
  725. LOG.error("Error in closing MySQL connection: " + e.toString());
  726. }
  727. }
  728. }
  729. return updateOk;
  730. }
  731. /**
  732. * Insert a new release page and returns the page_id of new record inserted
  733. * @param con
  734. * @param pageDTO
  735. */
  736. public static int insertReleaseChildPage (ReleasePageDTO pageDTO)
  737. {
  738. String insertPageSQL = "Insert into bp_release_child_pages " +
  739. "(page_uri, mode, last_update) values (?, ?, ?)";
  740. int pageId = -1;
  741. Connection con = null;
  742. DBConnector connector = DBConnector.getInstance();
  743. try
  744. {
  745. //con = connector.getDBConnFactory(Environment.SQL);
  746. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  747. con.setAutoCommit(false);
  748. PreparedStatement pstm = con.prepareStatement(insertPageSQL);
  749. pstm.setString(1, pageDTO.getPageURI());
  750. pstm.setString(2, pageDTO.getMode());
  751. pstm.setLong(3, pageDTO.getLastUpdate());
  752. pstm.executeUpdate();
  753. con.commit();
  754. con.setAutoCommit(true);
  755. String getReleasePageSQL = "Select page_id from bp_release_child_pages where page_uri = '" + pageDTO.getPageURI() + "'";
  756. PreparedStatement getIdPstm = con.prepareStatement(getReleasePageSQL);
  757. ResultSet rs = getIdPstm.executeQuery();
  758. if (rs.next())
  759. {
  760. pageId = rs.getInt("page_id");
  761. }
  762. }
  763. catch (SQLException sqle)
  764. {
  765. LOG.error("Error in insertReleaseChildPage: " + sqle.toString());
  766. try
  767. {
  768. con.rollback();
  769. }
  770. catch (Exception e)
  771. {
  772. LOG.error("Error in insertReleaseChildPage rollback: " + e.toString());
  773. }
  774. }
  775. finally
  776. {
  777. if (con != null)
  778. {
  779. try
  780. {
  781. connector.close(con);
  782. }
  783. catch (Exception e)
  784. {
  785. LOG.error("Error in closing MySQL connection: " + e.toString());
  786. }
  787. }
  788. }
  789. return pageId;
  790. }
  791. /**
  792. * Insert a new release page and returns the page_id of new record inserted
  793. * @param con
  794. * @param pageDTO
  795. */
  796. public static int insertReleasePage (ReleasePageDTO pageDTO)
  797. {
  798. String insertPageSQL = "Insert into bp_release_pages " +
  799. "(page_uri, mode, status, bug_id, notes, last_update, self_edited) values (?, ?, ?, ?, ?, ?, ?)";
  800. int pageId = -1;
  801. Connection con = null;
  802. DBConnector connector = DBConnector.getInstance();
  803. try
  804. {
  805. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  806. //con = connector.getDBConnFactory(Environment.SQL);
  807. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  808. con.setAutoCommit(false);
  809. PreparedStatement pstm = con.prepareStatement(insertPageSQL);
  810. pstm.setString(1, pageDTO.getPageURI());
  811. pstm.setString(2, pageDTO.getMode());
  812. pstm.setString(3, pageDTO.getStatus());
  813. pstm.setString(4, pageDTO.getBugId());
  814. pstm.setString(5, pageDTO.getNotes());
  815. pstm.setLong(6, pageDTO.getLastUpdate());
  816. pstm.setString(7, pageDTO.getSelfEdited());
  817. pstm.executeUpdate();
  818. con.commit();
  819. con.setAutoCommit(true);
  820. String getReleasePageSQL = "Select page_id from bp_release_pages where page_uri = '" + pageDTO.getPageURI() + "'";
  821. PreparedStatement getIdPstm = con.prepareStatement(getReleasePageSQL);
  822. ResultSet rs = getIdPstm.executeQuery();
  823. if (rs.next())
  824. {
  825. pageId = rs.getInt("page_id");
  826. }
  827. }
  828. catch (SQLException sqle)
  829. {
  830. LOG.error("Error in insertReleasePage: " + sqle.toString());
  831. try
  832. {
  833. con.rollback();
  834. }
  835. catch (Exception e)
  836. {
  837. LOG.error("Error in insertReleasePage rollback: " + e.toString());
  838. }
  839. }
  840. finally
  841. {
  842. if (con != null)
  843. {
  844. try
  845. {
  846. connector.close(con);
  847. }
  848. catch (Exception e)
  849. {
  850. LOG.error("Error in closing MySQL connection: " + e.toString());
  851. }
  852. }
  853. }
  854. return pageId;
  855. }
  856. /**
  857. * Synchronize editing pages between CMS tables and publishing tables
  858. */
  859. public static void updatePageSystem (Vector cmsPageURIs)
  860. {
  861. Connection con = null;
  862. DBConnector connector = DBConnector.getInstance();
  863. try
  864. {
  865. // delete old records
  866. // Implement later
  867. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  868. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  869. for (int i = 0; i < cmsPageURIs.size(); i++)
  870. {
  871. // check if there is a file in the current release existed in bp_release_pages
  872. String pageURI = (String)cmsPageURIs.elementAt(i);
  873. insertPageToSystem(pageURI);
  874. }
  875. }
  876. catch (SQLException sqle)
  877. {
  878. LOG.error("Error in creating MySQL connection: " + sqle.toString());
  879. }
  880. finally
  881. {
  882. if (con != null)
  883. {
  884. try
  885. {
  886. connector.close(con);
  887. }
  888. catch (Exception e)
  889. {
  890. LOG.error("Error in closing MySQL connection: " + e.toString());
  891. }
  892. }
  893. }
  894. }
  895. /**
  896. * Insert a new release page
  897. * @param con
  898. * @param pageDTO
  899. */
  900. public static void insertPageToSystem (String pageURI)
  901. {
  902. String updatePageStatusSQL = "Insert into bp_page_system " +
  903. "(page_uri) values" +
  904. "('"
  905. + pageURI +
  906. "')";
  907. Connection con = null;
  908. DBConnector connector = DBConnector.getInstance();
  909. try
  910. {
  911. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  912. //con = connector.getDBConnFactory(Environment.SQL);
  913. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  914. con.setAutoCommit(false);
  915. PreparedStatement pstm = con.prepareStatement(updatePageStatusSQL);
  916. pstm.executeUpdate();
  917. con.commit();
  918. con.setAutoCommit(true);
  919. }
  920. catch (SQLException sqle)
  921. {
  922. LOG.error("Error in updatePageStatus: " + sqle.toString());
  923. try
  924. {
  925. con.rollback();
  926. }
  927. catch (Exception e)
  928. {
  929. LOG.error("Error in updatePageStatus rollback: " + e.toString());
  930. }
  931. }
  932. finally
  933. {
  934. if (con != null)
  935. {
  936. try
  937. {
  938. connector.close(con);
  939. }
  940. catch (Exception e)
  941. {
  942. LOG.error("Error in closing MySQL connection: " + e.toString());
  943. }
  944. }
  945. }
  946. }
  947. /**
  948. * This method delete the records in bp_release_pages table that is no longer valid for the publishing.
  949. * Eg, when a new page is created than deleted or a page marked as delete but then is unmarked by Author
  950. * @param pageURIs
  951. */
  952. public static void cleanReleasePages (Vector pageURIs)
  953. {
  954. if (pageURIs == null || pageURIs.size() == 0)
  955. {
  956. return;
  957. }
  958. // combine URI set
  959. StringBuffer pageSetBuf = new StringBuffer();
  960. pageSetBuf.append("(");
  961. for (int i = 0; i < pageURIs.size(); i++)
  962. {
  963. if (i == pageURIs.size() -1)
  964. {
  965. pageSetBuf.append("'").append(pageURIs.elementAt(i)).append("'");
  966. }
  967. else
  968. {
  969. pageSetBuf.append("'").append(pageURIs.elementAt(i)).append("',");
  970. }
  971. }
  972. pageSetBuf.append(")");
  973. String cleanQuery = "Delete from bp_release_pages where page_uri not in " + pageSetBuf.toString();
  974. Connection con = null;
  975. DBConnector connector = DBConnector.getInstance();
  976. try
  977. {
  978. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  979. //con = connector.getDBConnFactory(Environment.SQL);
  980. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  981. con.setAutoCommit(false);
  982. PreparedStatement pstm = con.prepareStatement(cleanQuery);
  983. pstm.executeUpdate();
  984. con.commit();
  985. con.setAutoCommit(true);
  986. }
  987. catch (SQLException sqle)
  988. {
  989. LOG.error("Error in cleanReleasePages: " + sqle.toString());
  990. try
  991. {
  992. con.rollback();
  993. }
  994. catch (Exception e)
  995. {
  996. LOG.error("Error in cleanReleasePages rollback: " + e.toString());
  997. }
  998. }
  999. finally
  1000. {
  1001. if (con != null)
  1002. {
  1003. try
  1004. {
  1005. connector.close(con);
  1006. }
  1007. catch (Exception e)
  1008. {
  1009. LOG.error("Error in closing MySQL connection: " + e.toString());
  1010. }
  1011. }
  1012. }
  1013. }
  1014. /**
  1015. * This method delete the records in bp_release_pages table that is no longer valid for the publishing.
  1016. * Eg, when a new page is created than deleted or a page marked as delete but then is unmarked by Author
  1017. * Dont use this method.
  1018. * @param pageIds
  1019. */
  1020. public static void cleanReleaseChildPages (Vector<ReleasePageDTO> pageDTOs, String prefix)
  1021. {
  1022. if (pageDTOs == null)
  1023. {
  1024. return;
  1025. }
  1026. // combine URI set
  1027. StringBuffer pageSetBuf = new StringBuffer();
  1028. pageSetBuf.append("(''");
  1029. for (int i = 0; i < pageDTOs.size(); i++)
  1030. {
  1031. ReleasePageDTO childPage = pageDTOs.elementAt(i);
  1032. pageSetBuf.append(",'").append(childPage.getPageId()).append("'");
  1033. }
  1034. pageSetBuf.append(")");
  1035. // clean child pages
  1036. String cleanChildQuery = "Delete from bp_release_child_pages where page_id not in " + pageSetBuf.toString() + " and page_uri like '" + prefix + "%'";
  1037. String cleanChildParentQuery = "Delete from bp_release_childparent where child_id in " +
  1038. "(select page_id from bp_release_child_pages where page_id not in "
  1039. + pageSetBuf.toString() + " and page_uri like '" + prefix + "%')";
  1040. LOG.info("Clean child pages: " + cleanChildQuery);
  1041. LOG.info("Clean child_parent links: " + cleanChildParentQuery);
  1042. Connection con = null;
  1043. DBConnector connector = DBConnector.getInstance();
  1044. try
  1045. {
  1046. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1047. //con = connector.getDBConnFactory(Environment.SQL);
  1048. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1049. con.setAutoCommit(false);
  1050. PreparedStatement pstm = con.prepareStatement(cleanChildParentQuery);
  1051. pstm.executeUpdate();
  1052. con.commit();
  1053. pstm = con.prepareStatement(cleanChildQuery);
  1054. pstm.executeUpdate();
  1055. con.commit();
  1056. con.setAutoCommit(true);
  1057. }
  1058. catch (SQLException sqle)
  1059. {
  1060. LOG.error("Error in cleanReleaseChildPages: " + sqle.toString());
  1061. try
  1062. {
  1063. con.rollback();
  1064. }
  1065. catch (Exception e)
  1066. {
  1067. LOG.error("Error in cleanReleaseChildPages rollback: " + e.toString());
  1068. }
  1069. }
  1070. finally
  1071. {
  1072. if (con != null)
  1073. {
  1074. try
  1075. {
  1076. connector.close(con);
  1077. }
  1078. catch (Exception e)
  1079. {
  1080. LOG.error("Error in closing MySQL connection: " + e.toString());
  1081. }
  1082. }
  1083. }
  1084. }
  1085. /**
  1086. * Record action made with page in publishing
  1087. * @param pageId
  1088. * @param userId
  1089. * @param actionType
  1090. * @param notes
  1091. * @param time Formated long value: YYYYmmddHHmmss
  1092. * @return
  1093. */
  1094. public static boolean insertReleaseAction (int pageId, String userId, String status,
  1095. String notes, long updateTime)
  1096. {
  1097. LOG.info("insertReleaseAction():BEGIN");
  1098. LOG.info(updateTime);
  1099. String insertReleaseActionSQL = "Insert into bp_release_actions " +
  1100. "(page_id, user_id, status, notes, action_time) values (?, ?, ?, ?, ?)";
  1101. boolean result = false;
  1102. Connection con = null;
  1103. DBConnector connector = DBConnector.getInstance();
  1104. try
  1105. {
  1106. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1107. //con = connector.getDBConnFactory(Environment.SQL);
  1108. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1109. con.setAutoCommit(false);
  1110. PreparedStatement pstm = con.prepareStatement(insertReleaseActionSQL);
  1111. pstm.setInt(1, pageId);
  1112. pstm.setString(2, userId);
  1113. pstm.setString(3, status);
  1114. pstm.setString(4, notes);
  1115. pstm.setLong(5, updateTime);
  1116. pstm.executeUpdate();
  1117. con.commit();
  1118. con.setAutoCommit(true);
  1119. result = true;
  1120. }
  1121. catch (SQLException sqle)
  1122. {
  1123. LOG.error("Error in insertReleaseAction: " + sqle.toString());
  1124. try
  1125. {
  1126. con.rollback();
  1127. }
  1128. catch (Exception e)
  1129. {
  1130. LOG.error("Error in insertReleaseAction rollback: " + e.toString());
  1131. }
  1132. }
  1133. finally
  1134. {
  1135. if (con != null)
  1136. {
  1137. try
  1138. {
  1139. connector.close(con);
  1140. }
  1141. catch (Exception e)
  1142. {
  1143. LOG.error("Error in closing MySQL connection: " + e.toString());
  1144. }
  1145. }
  1146. }
  1147. LOG.info("insertReleaseAction():END");
  1148. return result;
  1149. }
  1150. public static boolean insertOrphanReleaseAction (int pageId, String userId, String status,
  1151. String notes, long updateTime)
  1152. {
  1153. LOG.info("insertOrphanReleaseAction():BEGIN");
  1154. LOG.info(updateTime);
  1155. String insertReleaseActionSQL = "Insert into bp_release_actions " +
  1156. "(page_id, childpage_id, user_id, status, notes, action_time) values (-1, ?, ?, ?, ?, ?)";
  1157. boolean result = false;
  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(insertReleaseActionSQL);
  1167. pstm.setInt(1, pageId);
  1168. pstm.setString(2, userId);
  1169. pstm.setString(3, status);
  1170. pstm.setString(4, notes);
  1171. pstm.setLong(5, updateTime);
  1172. pstm.executeUpdate();
  1173. con.commit();
  1174. con.setAutoCommit(true);
  1175. result = true;
  1176. }
  1177. catch (SQLException sqle)
  1178. {
  1179. LOG.error("Error in insertOrphanReleaseAction: " + sqle.toString());
  1180. try
  1181. {
  1182. con.rollback();
  1183. }
  1184. catch (Exception e)
  1185. {
  1186. LOG.error("Error in insertOrphanReleaseAction rollback: " + e.toString());
  1187. }
  1188. }
  1189. finally
  1190. {
  1191. if (con != null)
  1192. {
  1193. try
  1194. {
  1195. connector.close(con);
  1196. }
  1197. catch (Exception e)
  1198. {
  1199. LOG.error("Error in closing MySQL connection: " + e.toString());
  1200. }
  1201. }
  1202. }
  1203. LOG.info("insertOrphanReleaseAction():END");
  1204. return result;
  1205. }
  1206. /**
  1207. * Build action histories of this page
  1208. * @param pageId
  1209. * @return
  1210. */
  1211. public static String buildActionHistories (int pageId)
  1212. {
  1213. String selectReleaseActionSQL = "Select notes from bp_release_actions where page_id = '"
  1214. + pageId + "' order by action_time desc";
  1215. StringBuffer actionHisBuf = new StringBuffer();
  1216. Connection con = null;
  1217. DBConnector connector = DBConnector.getInstance();
  1218. try
  1219. {
  1220. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1221. //con = connector.getDBConnFactory(Environment.SQL);
  1222. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1223. PreparedStatement pstm = con.prepareStatement(selectReleaseActionSQL);
  1224. ResultSet rs = pstm.executeQuery();
  1225. while (rs.next())
  1226. {
  1227. String notes = rs.getString("notes");
  1228. if (notes != null)
  1229. {
  1230. actionHisBuf.append(notes).append("\n\n");
  1231. }
  1232. }
  1233. if (actionHisBuf.length() >= 2)
  1234. {
  1235. actionHisBuf.delete(actionHisBuf.length() - 2, actionHisBuf.length());
  1236. }
  1237. }
  1238. catch (SQLException sqle)
  1239. {
  1240. LOG.error("Error in buildActionHistories: " + sqle.toString());
  1241. }
  1242. finally
  1243. {
  1244. if (con != null)
  1245. {
  1246. try
  1247. {
  1248. connector.close(con);
  1249. }
  1250. catch (Exception e)
  1251. {
  1252. LOG.error("Error in closing MySQL connection: " + e.toString());
  1253. }
  1254. }
  1255. }
  1256. return actionHisBuf.toString();
  1257. }
  1258. /**
  1259. * Build action histories for orphan documents
  1260. * @param pageId
  1261. * @return
  1262. */
  1263. public static String buildOrphanActionHistories (int pageId)
  1264. {
  1265. String selectReleaseActionSQL = "Select notes from bp_release_actions where childpage_id = '"
  1266. + pageId + "' order by action_time desc";
  1267. StringBuffer actionHisBuf = new StringBuffer();
  1268. Connection con = null;
  1269. DBConnector connector = DBConnector.getInstance();
  1270. try
  1271. {
  1272. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1273. //con = connector.getDBConnFactory(Environment.SQL);
  1274. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1275. PreparedStatement pstm = con.prepareStatement(selectReleaseActionSQL);
  1276. ResultSet rs = pstm.executeQuery();
  1277. while (rs.next())
  1278. {
  1279. String notes = rs.getString("notes");
  1280. if (notes != null)
  1281. {
  1282. actionHisBuf.append(notes).append("\n\n");
  1283. }
  1284. }
  1285. if (actionHisBuf.length() >= 2)
  1286. {
  1287. actionHisBuf.delete(actionHisBuf.length() - 2, actionHisBuf.length());
  1288. }
  1289. }
  1290. catch (SQLException sqle)
  1291. {
  1292. LOG.error("Error in buildActionHistories: " + sqle.toString());
  1293. }
  1294. finally
  1295. {
  1296. if (con != null)
  1297. {
  1298. try
  1299. {
  1300. connector.close(con);
  1301. }
  1302. catch (Exception e)
  1303. {
  1304. LOG.error("Error in closing MySQL connection: " + e.toString());
  1305. }
  1306. }
  1307. }
  1308. return actionHisBuf.toString();
  1309. }
  1310. /**
  1311. * Get the last publishing action userId right before the page change to status
  1312. * @param pageId
  1313. * @param status: Status that the page is changed by user
  1314. * @return
  1315. */
  1316. public static String getLastPublishingActionUserId (int pageId, String status)
  1317. {
  1318. String getMaxUpdateActionSQL = "Select max(action_time) as atMax from bp_release_actions where page_id = '"
  1319. + pageId + "' and status = '" + status + "'";
  1320. String getLastUserActionSQL = "Select user_id from bp_release_actions where page_id = '"
  1321. + pageId + "' and status = '" + status + "' and action_time = ?";
  1322. Connection con = null;
  1323. DBConnector connector = DBConnector.getInstance();
  1324. try
  1325. {
  1326. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1327. //con = connector.getDBConnFactory(Environment.SQL);
  1328. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1329. PreparedStatement pstmGetMax = con.prepareStatement(getMaxUpdateActionSQL);
  1330. ResultSet rsGetMax = pstmGetMax.executeQuery();
  1331. if (rsGetMax.next())
  1332. {
  1333. long maxActionTime = rsGetMax.getLong("atMax");
  1334. // Get user_id from max action time
  1335. PreparedStatement pstmLastUser = con.prepareStatement(getLastUserActionSQL);
  1336. pstmLastUser.setLong(1, maxActionTime);
  1337. ResultSet rsLastUser = pstmLastUser.executeQuery();
  1338. if (rsLastUser.next())
  1339. {
  1340. return rsLastUser.getString("user_id");
  1341. }
  1342. }
  1343. }
  1344. catch (SQLException sqle)
  1345. {
  1346. LOG.error("Error in getLastPublishingActionUserId: " + sqle.toString());
  1347. }
  1348. finally
  1349. {
  1350. if (con != null)
  1351. {
  1352. try
  1353. {
  1354. connector.close(con);
  1355. }
  1356. catch (Exception e)
  1357. {
  1358. LOG.error("Error in closing MySQL connection: " + e.toString());
  1359. }
  1360. }
  1361. }
  1362. return null;
  1363. }
  1364. /**
  1365. * Delete release page by pageId
  1366. * @return
  1367. */
  1368. public static boolean removeReleasePage (int pageId)
  1369. {
  1370. String deleteReleasePageSQL = "Delete from bp_release_pages where page_id = '" + pageId + "'";
  1371. boolean result = false;
  1372. Connection con = null;
  1373. DBConnector connector = DBConnector.getInstance();
  1374. try
  1375. {
  1376. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1377. //con = connector.getDBConnFactory(Environment.SQL);
  1378. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1379. con.setAutoCommit(false);
  1380. PreparedStatement pstm = con.prepareStatement(deleteReleasePageSQL);
  1381. pstm.executeUpdate();
  1382. con.commit();
  1383. con.setAutoCommit(true);
  1384. result = true;
  1385. }
  1386. catch (SQLException sqle)
  1387. {
  1388. LOG.error("Error in deleteReleasePage: " + sqle.toString());
  1389. try
  1390. {
  1391. con.rollback();
  1392. }
  1393. catch (Exception e)
  1394. {
  1395. LOG.error("Error in deleteReleasePage rollback: " + e.toString());
  1396. }
  1397. }
  1398. finally
  1399. {
  1400. if (con != null)
  1401. {
  1402. try
  1403. {
  1404. connector.close(con);
  1405. }
  1406. catch (Exception e)
  1407. {
  1408. LOG.error("Error in closing MySQL connection: " + e.toString());
  1409. }
  1410. }
  1411. }
  1412. return result;
  1413. }
  1414. /**
  1415. * Delete release child page by pageId
  1416. * @return
  1417. */
  1418. public static boolean removeReleaseChildPage (int childPageId)
  1419. {
  1420. String deleteReleaseChildPageSQL = "Delete from bp_release_child_pages where page_id = '" + childPageId + "'";
  1421. boolean result = false;
  1422. Connection con = null;
  1423. DBConnector connector = DBConnector.getInstance();
  1424. try
  1425. {
  1426. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1427. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1428. con.setAutoCommit(false);
  1429. PreparedStatement pstm = con.prepareStatement(deleteReleaseChildPageSQL);
  1430. pstm.executeUpdate();
  1431. con.commit();
  1432. con.setAutoCommit(true);
  1433. result = true;
  1434. }
  1435. catch (SQLException sqle)
  1436. {
  1437. LOG.error("Error in removeReleaseChildPage: " + sqle.toString());
  1438. try
  1439. {
  1440. con.rollback();
  1441. }
  1442. catch (Exception e)
  1443. {
  1444. LOG.error("Error in removeReleaseChildPage rollback: " + e.toString());
  1445. }
  1446. }
  1447. finally
  1448. {
  1449. if (con != null)
  1450. {
  1451. try
  1452. {
  1453. connector.close(con);
  1454. }
  1455. catch (Exception e)
  1456. {
  1457. LOG.error("Error in closing MySQL connection: " + e.toString());
  1458. }
  1459. }
  1460. }
  1461. return result;
  1462. }
  1463. /**
  1464. * Delete release actions by pageId
  1465. * @return
  1466. */
  1467. public static boolean removeReleaseActions (int pageId)
  1468. {
  1469. String deleteReleasePageSQL = "Delete from bp_release_actions where page_id = '" + pageId + "'";
  1470. boolean result = false;
  1471. Connection con = null;
  1472. DBConnector connector = DBConnector.getInstance();
  1473. try
  1474. {
  1475. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1476. //con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1477. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1478. con.setAutoCommit(false);
  1479. PreparedStatement pstm = con.prepareStatement(deleteReleasePageSQL);
  1480. pstm.executeUpdate();
  1481. con.commit();
  1482. con.setAutoCommit(true);
  1483. result = true;
  1484. }
  1485. catch (SQLException sqle)
  1486. {
  1487. LOG.error("Error in deleteReleaseActions: " + sqle.toString());
  1488. try
  1489. {
  1490. con.rollback();
  1491. }
  1492. catch (Exception e)
  1493. {
  1494. LOG.error("Error in deleteReleaseActions rollback: " + e.toString());
  1495. }
  1496. }
  1497. finally
  1498. {
  1499. if (con != null)
  1500. {
  1501. try
  1502. {
  1503. connector.close(con);
  1504. }
  1505. catch (Exception e)
  1506. {
  1507. LOG.error("Error in closing MySQL connection: " + e.toString());
  1508. }
  1509. }
  1510. }
  1511. return result;
  1512. }
  1513. /**
  1514. * Delete release child pages by parentId
  1515. * @return
  1516. */
  1517. public static boolean removeReleaseChildPagesByParent (int parentId)
  1518. {
  1519. String selectChildIdFromParentSQL = "Select child_id from bp_release_childparent " +
  1520. "where parent_id = '" + parentId + "'";
  1521. String deleteReleaseChildPageSQL = "Delete from bp_release_child_pages where page_id in ";
  1522. String deleteChildParentSQL = "Delete from bp_release_childparent where child_id in ";
  1523. boolean result = false;
  1524. Connection con = null;
  1525. DBConnector connector = DBConnector.getInstance();
  1526. try
  1527. {
  1528. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1529. //con = connector.getDBConnFactory(Environment.SQL);
  1530. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1531. // get child_id set from parent_id
  1532. PreparedStatement pstm = con.prepareStatement(selectChildIdFromParentSQL);
  1533. ResultSet rs = pstm.executeQuery();
  1534. StringBuffer inCondStrBuf = new StringBuffer("(''");
  1535. while (rs.next())
  1536. {
  1537. int child_id = rs.getInt("child_id");
  1538. inCondStrBuf.append(",'").append(child_id).append("'");
  1539. }
  1540. inCondStrBuf.append(")");
  1541. con.setAutoCommit(false);
  1542. LOG.info("deleteReleaseChildPageSQL: " + deleteReleaseChildPageSQL + inCondStrBuf.toString());
  1543. pstm = con.prepareStatement(deleteReleaseChildPageSQL + inCondStrBuf.toString());
  1544. pstm.executeUpdate();
  1545. // delete child-parent relationship
  1546. pstm = con.prepareStatement(deleteChildParentSQL + inCondStrBuf.toString());
  1547. pstm.executeUpdate();
  1548. con.commit();
  1549. con.setAutoCommit(true);
  1550. result = true;
  1551. }
  1552. catch (SQLException sqle)
  1553. {
  1554. LOG.error("Error in removeReleaseChildPagesByParent: " + sqle.toString());
  1555. try
  1556. {
  1557. con.rollback();
  1558. }
  1559. catch (Exception e)
  1560. {
  1561. LOG.error("Error in removeReleaseChildPagesByParent rollback: " + e.toString());
  1562. }
  1563. }
  1564. finally
  1565. {
  1566. if (con != null)
  1567. {
  1568. try
  1569. {
  1570. connector.close(con);
  1571. }
  1572. catch (Exception e)
  1573. {
  1574. LOG.error("Error in closing MySQL connection: " + e.toString());
  1575. }
  1576. }
  1577. }
  1578. return result;
  1579. }
  1580. /**
  1581. * Delete release child pages by parentId
  1582. * @return
  1583. */
  1584. public static boolean removeAllInvalidParentPages ()
  1585. {
  1586. String selectAllParentIdSQL = "Select parent_id from bp_release_childparent";
  1587. String deleteInvalidParentPageSQL = "Delete from bp_release_pages where mode = 'Impacted' and " +
  1588. "self_edited = 'N' and page_id not in ";
  1589. boolean result = false;
  1590. Connection con = null;
  1591. DBConnector connector = DBConnector.getInstance();
  1592. try
  1593. {
  1594. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1595. //con = connector.getDBConnFactory(Environment.SQL);
  1596. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1597. // get child_id set from parent_id
  1598. PreparedStatement pstm = con.prepareStatement(selectAllParentIdSQL);
  1599. ResultSet rs = pstm.executeQuery();
  1600. StringBuffer inCondStrBuf = new StringBuffer("(''");
  1601. while (rs.next())
  1602. {
  1603. int parent_id = rs.getInt("parent_id");
  1604. inCondStrBuf.append(",'").append(parent_id).append("'");
  1605. }
  1606. inCondStrBuf.append(")");
  1607. con.setAutoCommit(false);
  1608. LOG.info("deleteInvalidParentPageSQL: " + deleteInvalidParentPageSQL + inCondStrBuf.toString());
  1609. pstm = con.prepareStatement(deleteInvalidParentPageSQL + inCondStrBuf.toString());
  1610. pstm.executeUpdate();
  1611. con.commit();
  1612. con.setAutoCommit(true);
  1613. result = true;
  1614. }
  1615. catch (SQLException sqle)
  1616. {
  1617. LOG.error("Error in removeAllInvalidParentPages: " + sqle.toString());
  1618. try
  1619. {
  1620. con.rollback();
  1621. }
  1622. catch (Exception e)
  1623. {
  1624. LOG.error("Error in removeAllInvalidParentPages rollback: " + e.toString());
  1625. }
  1626. }
  1627. finally
  1628. {
  1629. if (con != null)
  1630. {
  1631. try
  1632. {
  1633. connector.close(con);
  1634. }
  1635. catch (Exception e)
  1636. {
  1637. LOG.error("Error in closing MySQL connection: " + e.toString());
  1638. }
  1639. }
  1640. }
  1641. return result;
  1642. }
  1643. /**
  1644. * Get all approved pages for deploy
  1645. * @return
  1646. */
  1647. public static Vector<ReleasePageDTO> getAllApprovedParentPages ()
  1648. {
  1649. String selectAllApprovedPagesSQL = "Select page_id, page_uri, mode, status, bug_id, notes, last_update " +
  1650. "from bp_release_pages where status = 'Approved'";
  1651. Vector<ReleasePageDTO> approvedPages = new Vector<ReleasePageDTO>();
  1652. Connection con = null;
  1653. DBConnector connector = DBConnector.getInstance();
  1654. try
  1655. {
  1656. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1657. //con = connector.getDBConnFactory(Environment.SQL);
  1658. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1659. PreparedStatement pstm = con.prepareStatement(selectAllApprovedPagesSQL);
  1660. ResultSet rs = pstm.executeQuery();
  1661. ReleasePageDTO pageDTO = null;
  1662. while (rs.next())
  1663. {
  1664. pageDTO = new ReleasePageDTO();
  1665. pageDTO.setPageId(rs.getInt("page_id"));
  1666. pageDTO.setParentId(-1);
  1667. pageDTO.setPageURI(rs.getString("page_uri"));
  1668. pageDTO.setMode(rs.getString("mode"));
  1669. pageDTO.setStatus(rs.getString("status"));
  1670. pageDTO.setBugId(rs.getString("bug_id"));
  1671. pageDTO.setNotes(rs.getString("notes"));
  1672. pageDTO.setLastUpdate(rs.getLong("last_update"));
  1673. pageDTO.setSelfEdited("Y");
  1674. approvedPages.add(pageDTO);
  1675. }
  1676. }
  1677. catch (SQLException sqle)
  1678. {
  1679. LOG.error("Error in getAllApprovedParentPages: " + sqle.toString());
  1680. }
  1681. finally
  1682. {
  1683. if (con != null)
  1684. {
  1685. try
  1686. {
  1687. connector.close(con);
  1688. }
  1689. catch (Exception e)
  1690. {
  1691. LOG.error("Error in closing MySQL connection: " + e.toString());
  1692. }
  1693. }
  1694. }
  1695. return approvedPages;
  1696. }
  1697. /**
  1698. * Check if a parent page is Ok to deploy:
  1699. * - It has no children that whose's parent are not approved
  1700. * - It has status of 'Approved'
  1701. * @return
  1702. */
  1703. public static boolean isParentPageReadyToDeploy (int parentId)
  1704. {
  1705. String checkAllChildren = "select count(*) as numC from bp_release_childparent cp where cp.parent_id = '" + parentId + "'";
  1706. String checkParentStatus = "select status from bp_release_pages where page_id = '" + parentId + "'";
  1707. String selectChildIdFromParentSQL = "Select child_id from bp_release_childparent " +
  1708. "where parent_id = '" + parentId + "'";
  1709. String checkAllParentApproved = "Select count(*) as num from bp_release_pages p inner join bp_release_childparent cp on (p.page_id = cp.parent_id) " +
  1710. "where p.status != 'Approved' and p.status != 'Deployed' and cp.child_id in (" +
  1711. selectChildIdFromParentSQL +")";
  1712. boolean result = false;
  1713. Connection con = null;
  1714. DBConnector connector = DBConnector.getInstance();
  1715. try
  1716. {
  1717. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1718. //con = connector.getDBConnFactory(Environment.SQL);
  1719. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1720. // get child_id set from parent_id
  1721. PreparedStatement pstm = con.prepareStatement(checkAllChildren);
  1722. ResultSet rs = pstm.executeQuery();
  1723. if (rs.next())
  1724. {
  1725. int numC = rs.getInt("numC");
  1726. rs.close();
  1727. pstm.close();
  1728. if (numC > 0)
  1729. {
  1730. // Check other parent of their children
  1731. pstm = con.prepareStatement(selectChildIdFromParentSQL);
  1732. rs = pstm.executeQuery();
  1733. //LOG.info("checkAllParentApproved: " + checkAllParentApproved);
  1734. pstm = con.prepareStatement(checkAllParentApproved);
  1735. rs = pstm.executeQuery();
  1736. if (rs.next())
  1737. {
  1738. int num = rs.getInt("num");
  1739. if (num == 0)
  1740. {
  1741. result = true;
  1742. }
  1743. }
  1744. }
  1745. else
  1746. {
  1747. pstm = con.prepareStatement(checkParentStatus);
  1748. rs = pstm.executeQuery();
  1749. if (rs.next())
  1750. {
  1751. String status = rs.getString("status");
  1752. if (status != null && (status.equals("Approved") || status.equals("Deployed")))
  1753. {
  1754. result = true;
  1755. }
  1756. }
  1757. }
  1758. }
  1759. }
  1760. catch (SQLException sqle)
  1761. {
  1762. LOG.error("Error in removeReleaseChildPagesByParent: " + sqle.toString());
  1763. }
  1764. finally
  1765. {
  1766. if (con != null)
  1767. {
  1768. try
  1769. {
  1770. connector.close(con);
  1771. }
  1772. catch (Exception e)
  1773. {
  1774. LOG.error("Error in closing MySQL connection: " + e.toString());
  1775. }
  1776. }
  1777. }
  1778. return result;
  1779. }
  1780. /**
  1781. * Check if a child page is Ok to deploy:
  1782. * - Has at least 1 parent
  1783. * - All parent are approved
  1784. * @return
  1785. */
  1786. public static boolean isChildPageReadyToDeploy (int childPageId)
  1787. {
  1788. String checkAllParentApproved = "Select count(*) as numAP from bp_release_pages p inner join bp_release_childparent cp on (p.page_id = cp.parent_id) " +
  1789. "where (p.status = 'Approved' or p.status = 'Deployed') and cp.child_id = '" + childPageId + "'";
  1790. String checkAllParent = "select count(*) as numP from bp_release_childparent cp where cp.child_id = '" + childPageId + "'";
  1791. boolean result = false;
  1792. Connection con = null;
  1793. DBConnector connector = DBConnector.getInstance();
  1794. try
  1795. {
  1796. int numParentApproved = 0;
  1797. int numAllParent = 0;
  1798. //int caseWorkNo = CaseWorkSQLHandler.getMaxCaseNo() + 1;
  1799. //con = connector.getDBConnFactory(Environment.SQL);
  1800. con = connector.getDBConnFactory(Environment.PENSIONLINE);
  1801. // get child_id set from parent_id
  1802. PreparedStatement pstm = con.prepareStatement(checkAllParentApproved);
  1803. ResultSet rs = pstm.executeQuery();
  1804. if (rs.next())
  1805. {
  1806. numParentApproved = rs.getInt("numAP");
  1807. }
  1808. pstm = con.prepareStatement(checkAllParent);
  1809. rs = pstm.executeQuery();
  1810. if (rs.next())
  1811. {
  1812. numAllParent = rs.getInt("numP");
  1813. }
  1814. if (numParentApproved > 0 && numParentApproved == numAllParent)
  1815. {
  1816. result = true;
  1817. }
  1818. }
  1819. catch (SQLException sqle)
  1820. {
  1821. LOG.error("Error in removeReleaseChildPagesByParent: " + sqle.toString());
  1822. }
  1823. finally
  1824. {
  1825. if (con != null)
  1826. {
  1827. try
  1828. {
  1829. connector.close(con);
  1830. }
  1831. catch (Exception e)

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