Simple Status Page
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

147 lines
4.6 KiB

  1. <?php
  2. /** Class to manage reports */
  3. class Reports {
  4. /** DB connection */
  5. private static $db;
  6. /** DB connection contructor */
  7. function __construct()
  8. {
  9. $dsn = 'sqlite:db/reports.sqlite3';
  10. try{
  11. self::$db=new PDO($dsn);
  12. # We can now log any exceptions on Fatal error.
  13. self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  14. # Disable emulation of prepared statements, use REAL prepared statements instead.
  15. self::$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  16. self::$db->exec('PRAGMA foreign_keys = ON;');
  17. }
  18. catch(PDOException $e){
  19. printf("Échec de la connexion : %s\n", $e->getMessage());
  20. $this->db = NULL;
  21. }
  22. }
  23. /** Get last 5 entries */
  24. function Get_public()
  25. {
  26. $sql="SELECT REPORTS.DATE, REPORTS.TITLE, REPORTS.MAINDESCRIPTION, REPORTS.STATE,
  27. GROUP_CONCAT(REPORT_DETAILS.DATE,'|') AS MSGDATE,
  28. GROUP_CONCAT(REPORT_DETAILS.DESCRIPTION,'|') AS MSGDESCRIPTION
  29. FROM REPORTS
  30. LEFT JOIN REPORT_DETAILS ON REPORT_ID = REPORTS.ID
  31. GROUP BY REPORTS.ID
  32. ORDER BY REPORTS.DATE DESC
  33. LIMIT 5";
  34. $data=self::$db->query($sql);
  35. return $data;
  36. }
  37. /** Get last 10 entries */
  38. function Get_rss()
  39. {
  40. $sql="SELECT REPORTS.ID, REPORTS.DATE, REPORTS.TITLE, REPORTS.MAINDESCRIPTION, REPORTS.STATE,
  41. GROUP_CONCAT(REPORT_DETAILS.ID,'|') AS MSGID,
  42. GROUP_CONCAT(REPORT_DETAILS.DATE,'|') AS MSGDATE,
  43. GROUP_CONCAT(REPORT_DETAILS.DESCRIPTION,'|') AS MSGDESCRIPTION
  44. FROM REPORTS
  45. LEFT JOIN REPORT_DETAILS ON REPORT_ID = REPORTS.ID
  46. GROUP BY REPORTS.ID
  47. ORDER BY REPORTS.DATE DESC
  48. LIMIT 10";
  49. $data=self::$db->query($sql);
  50. return $data;
  51. }
  52. /** Get last 5 entries */
  53. function Get_last_reports()
  54. {
  55. $sql="SELECT REPORTS.ID, REPORTS.DATE, REPORTS.TITLE, REPORTS.MAINDESCRIPTION, REPORTS.STATE,
  56. GROUP_CONCAT(REPORT_DETAILS.ID,'|') AS MSGID,
  57. GROUP_CONCAT(REPORT_DETAILS.DATE,'|') AS MSGDATE,
  58. GROUP_CONCAT(REPORT_DETAILS.DESCRIPTION,'|') AS MSGDESCRIPTION
  59. FROM REPORTS
  60. LEFT JOIN REPORT_DETAILS ON REPORT_ID = REPORTS.ID
  61. GROUP BY REPORTS.ID
  62. ORDER BY REPORTS.DATE DESC
  63. LIMIT 5";
  64. $data=self::$db->query($sql);
  65. return $data;
  66. }
  67. /** Get all DB entries */
  68. function Get_all_reports()
  69. {
  70. $sql="SELECT REPORTS.ID, REPORTS.DATE, REPORTS.TITLE, REPORTS.MAINDESCRIPTION, REPORTS.STATE,
  71. GROUP_CONCAT(REPORT_DETAILS.ID,'|') AS MSGID,
  72. GROUP_CONCAT(REPORT_DETAILS.DATE,'|') AS MSGDATE,
  73. GROUP_CONCAT(REPORT_DETAILS.DESCRIPTION,'|') AS MSGDESCRIPTION
  74. FROM REPORTS
  75. LEFT JOIN REPORT_DETAILS ON REPORT_ID = REPORTS.ID
  76. GROUP BY REPORTS.ID
  77. ORDER BY REPORTS.DATE DESC";
  78. $data=self::$db->query($sql);
  79. return $data;
  80. }
  81. /** Add report */
  82. function Add_report($data)
  83. {
  84. $sql = "INSERT INTO REPORTS(DATE,TITLE,MAINDESCRIPTION) values (?,?,?)";
  85. $stmt = self::$db->prepare($sql);
  86. return $stmt->execute(array($data['date'], $data['title'], $data['maindescription']));
  87. }
  88. /** Add report description */
  89. function Add_description_report($data)
  90. {
  91. $sql = "INSERT INTO REPORT_DETAILS(REPORT_ID,DATE,DESCRIPTION) values (?,?,?)";
  92. $stmt = self::$db->prepare($sql);
  93. return $stmt->execute(array($data['report_id'], $data['date'], $data['description']));
  94. }
  95. /** Get report by ID */
  96. function Get_report_by_id($id)
  97. {
  98. $sql="SELECT * FROM REPORTS WHERE ID=:id";
  99. $stmt=self::$db->prepare($sql);
  100. $stmt->bindParam(':id', $id, PDO::PARAM_INT);
  101. $stmt->execute();
  102. return $stmt->fetch(PDO::FETCH_OBJ);
  103. }
  104. /** Remove report by ID */
  105. function Delete_report_by_id($id)
  106. {
  107. $sql="DELETE FROM REPORTS WHERE REPORTS.ID=:id";
  108. $stmt=self::$db->prepare($sql);
  109. $stmt->bindParam(':id', $id, PDO::PARAM_INT);
  110. return $stmt->execute();
  111. }
  112. /** Remove report description by ID */
  113. function Delete_description_by_id($id)
  114. {
  115. $sql="DELETE FROM REPORT_DETAILS WHERE ID=:id";
  116. $stmt=self::$db->prepare($sql);
  117. $stmt->bindParam(':id', $id, PDO::PARAM_INT);
  118. return $stmt->execute();
  119. }
  120. /** Update report by ID */
  121. function Update($id, $date, $title, $maindescription, $state)
  122. {
  123. $sql = "UPDATE `REPORTS`
  124. SET `DATE` = :date,
  125. `TITLE` = :title,
  126. `MAINDESCRIPTION` = :maindescription,
  127. `STATE` = :state
  128. WHERE `REPORTS`.`ID` = :id";
  129. $stmt = self::$db->prepare($sql);
  130. $stmt->bindParam(':id', $id);
  131. $stmt->bindParam(':date', $date);
  132. $stmt->bindParam(':title', $title);
  133. $stmt->bindParam(':maindescription', $maindescription);
  134. $stmt->bindParam(':state', $state);
  135. return $stmt->execute();
  136. }
  137. }