3 * Copyright (c) STMicroelectronics, 2004-2009. All rights reserved
5 * This file is a part of Codendi.
7 * Codendi is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation; either version 2 of the License, or
10 * (at your option) any later version.
12 * Codendi is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
17 * You should have received a copy of the GNU General Public License
18 * along with Codendi. If not, see <http://www.gnu.org/licenses/>.
21 require_once 'common/dao/include/DataAccessObject.class.php';
24 define('FORUMML_MESSAGE_ID', 1);
25 define('FORUMML_DATE', 2);
26 define('FORUMML_FROM', 3);
27 define('FORUMML_SUBJECT', 4);
28 define('FORUMML_CONTENT_TYPE', 12);
29 define('FORUMML_CC', 34);
31 class ForumML_MessageDao extends DataAccessObject {
33 function __construct($da) {
34 parent::__construct($da);
37 function searchHeaderValue($messageId, $headerId) {
38 $messageId = $this->da->quoteSmart($messageId);
39 $headerId = $this->da->quoteSmart($headerId);
40 $sql = 'SELECT mh.value'.
41 ' FROM plugin_forumml_message m'.
42 ' JOIN plugin_forumml_messageheader mh'.
43 ' ON (mh.id_message = m.id_message)'.
44 ' JOIN plugin_forumml_header h'.
45 ' ON (h.id_header = mh.id_header)'.
46 ' WHERE m.id_message = $1'.
47 ' AND h.id_header = $2';
48 return $this->retrieve($sql,array($messageId,$headerId));
51 function getMessageHeaders($id_message) {
52 $id_message = $this->da->quoteSmart($id_message);
53 $sql = 'SELECT value'.
54 ' FROM plugin_forumml_messageheader'.
55 ' WHERE id_message = $1'.
57 ' ORDER BY id_header';
58 return $this->retrieve($sql,array($id_message));
61 function getSpecificMessage($id_message,$list_id) {
62 $id_message = $this->da->quoteSmart($id_message);
63 $list_id = $this->da->quoteSmart($list_id);
64 $sql ='SELECT value, body'.
65 ' FROM plugin_forumml_message m, plugin_forumml_messageheader mh'.
66 ' WHERE m.id_message =$1 '.
67 ' AND mh.id_message = m.id_message'.
68 ' AND m.id_list = $2'.
69 ' AND mh.id_header = $3';
70 return $this->retrieve($sql,array($id_message,$list_id,FORUMML_SUBJECT));
73 function getHeaderValue($id, $ids) {
74 $id = $this->da->quoteSmart($id);
75 $ids = $this->da->quoteSmart($ids);
77 $ids = 'SELECT id_header FROM plugin_forumml_messageheader';
79 $sql = 'SELECT value , id_header FROM plugin_forumml_messageheader'.
80 ' WHERE id_message =$1 and id_header IN ($2,$3) ORDER BY id_header DESC';
81 return $this->retrieve($sql,array($id,$ids[0],$ids[1]));
84 function getAllThreadsFromList($list_id,$offset,$chunks) {
85 $list_id = $this->da->quoteSmart($list_id);
86 $offset = $this->da->quoteSmart($offset);
87 $chunks = $this->da->quoteSmart($chunks);
88 $sql = 'SELECT m.id_message, m.last_thread_update as lastup, mh_d.value as date, mh_f.value as sender, mh_s.value as subject'.
89 ' FROM plugin_forumml_message m'.
90 ' LEFT JOIN plugin_forumml_messageheader mh_d ON (mh_d.id_message = m.id_message AND mh_d.id_header = $1)'.
91 ' LEFT JOIN plugin_forumml_messageheader mh_f ON (mh_f.id_message = m.id_message AND mh_f.id_header = $2) '.
92 ' LEFT JOIN plugin_forumml_messageheader mh_s ON (mh_s.id_message = m.id_message AND mh_s.id_header = $3) '.
93 ' WHERE m.id_parent = 0'.
95 ' ORDER BY m.last_thread_update DESC'.
98 return $this->retrieve($sql,array(FORUMML_DATE,FORUMML_FROM,FORUMML_SUBJECT,$list_id,$offset,$chunks));
101 function countAllThreadsFromList($list_id) {
102 $list_id = $this->da->quoteSmart($list_id);
103 $sql = 'SELECT COUNT(*) as nb'.
104 ' FROM plugin_forumml_message m'.
105 ' LEFT JOIN plugin_forumml_messageheader mh_d ON (mh_d.id_message = m.id_message AND mh_d.id_header = $1)'.
106 ' LEFT JOIN plugin_forumml_messageheader mh_f ON (mh_f.id_message = m.id_message AND mh_f.id_header = $2) '.
107 ' LEFT JOIN plugin_forumml_messageheader mh_s ON (mh_s.id_message = m.id_message AND mh_s.id_header = $3) '.
108 ' WHERE m.id_parent = 0'.
109 ' AND id_list = $4 GROUP BY m.last_thread_update'.
110 ' ORDER BY m.last_thread_update DESC';
111 return $this->retrieve($sql,array(FORUMML_DATE,FORUMML_FROM,FORUMML_SUBJECT,$list_id));
114 function countChildrenFromParents ($parents) {
115 $sql = 'SELECT id_message'.
116 ' FROM plugin_forumml_message m'.
117 ' WHERE m.id_parent IN ($1)';
118 return $this->retrieve($sql,array($parents));
121 function getChildrenFromDepthLevel($parents) {
122 $sql = 'SELECT m.*, mh_d.value as date, mh_f.value as sender, mh_s.value as subject, mh_ct.value as content_type, mh_cc.value as cc, a.id_attachment, a.file_name, a.file_type, a.file_size, a.file_path, a.content_id'.
123 ' FROM plugin_forumml_message m'.
124 ' LEFT JOIN plugin_forumml_messageheader mh_d ON (mh_d.id_message = m.id_message AND mh_d.id_header = $1)'.
125 ' LEFT JOIN plugin_forumml_messageheader mh_f ON (mh_f.id_message = m.id_message AND mh_f.id_header = $2) '.
126 ' LEFT JOIN plugin_forumml_messageheader mh_s ON (mh_s.id_message = m.id_message AND mh_s.id_header = $3) '.
127 ' LEFT JOIN plugin_forumml_messageheader mh_ct ON (mh_ct.id_message = m.id_message AND mh_ct.id_header = $4) '.
128 ' LEFT JOIN plugin_forumml_messageheader mh_cc ON (mh_cc.id_message = m.id_message AND mh_cc.id_header = $5) '.
129 " LEFT JOIN plugin_forumml_attachment a ON (a.id_message = m.id_message AND a.content_id = '')".
130 ' WHERE m.id_parent IN ($6)';
132 return $this->retrieve($sql,array(FORUMML_DATE,FORUMML_FROM,FORUMML_SUBJECT,FORUMML_CONTENT_TYPE,FORUMML_CC,$parents));
135 function getFlattenedThread($topic) {
136 $topic = $this->da->quoteSmart($topic);
137 $sql = 'SELECT m.*, mh_d.value as date, mh_f.value as sender, mh_s.value as subject, mh_ct.value as content_type, mh_cc.value as cc, a.id_attachment, a.file_name, a.file_type, a.file_size, a.file_path, a.content_id'.
138 ' FROM plugin_forumml_message m'.
139 ' LEFT JOIN plugin_forumml_messageheader mh_d ON (mh_d.id_message = m.id_message AND mh_d.id_header = $1)'.
140 ' LEFT JOIN plugin_forumml_messageheader mh_f ON (mh_f.id_message = m.id_message AND mh_f.id_header = $2)'.
141 ' LEFT JOIN plugin_forumml_messageheader mh_s ON (mh_s.id_message = m.id_message AND mh_s.id_header = $3)'.
142 ' LEFT JOIN plugin_forumml_messageheader mh_ct ON (mh_ct.id_message = m.id_message AND mh_ct.id_header = $4)'.
143 ' LEFT JOIN plugin_forumml_messageheader mh_cc ON (mh_cc.id_message = m.id_message AND mh_cc.id_header = $5)'.
144 " LEFT JOIN plugin_forumml_attachment a ON (a.id_message = m.id_message AND a.content_id = '')".
145 ' WHERE m.id_message =$6 ';
146 return $this->retrieve($sql,array(FORUMML_DATE,FORUMML_FROM,FORUMML_SUBJECT,FORUMML_CONTENT_TYPE,FORUMML_CC,$topic));
149 function updateCacheHTML($cache,$id) {
150 $cache = $this->da->quoteSmart($cache);
151 $id = $this->da->quoteSmart($id);
152 return $this->update('UPDATE plugin_forumml_message SET cached_html= $1 WHERE id_message= $2',array($cache,$id));
155 function getAttachment($id_message,$match) {
156 $id_message = $this->da->quoteSmart($id_message);
157 $match = $this->da->quoteSmart($match);
158 $sql = 'SELECT id_attachment FROM plugin_forumml_attachment WHERE id_message=$1 and content_id=<$2>';
159 return $this->retrieve($sql,array($id_message , $match));
161 function searchArchives($list_id,$pattern) {
162 $list_id = $this->da->quoteSmart($list_id);
163 $pattern = $this->da->quoteSmart($pattern);
164 $sql = 'SELECT mh.id_message, mh.value'.
165 ' FROM plugin_forumml_message m, plugin_forumml_messageheader mh'.
166 ' WHERE mh.id_header = $1'.
167 ' AND m.id_list = $2'.
168 ' AND m.id_parent = 0'.
169 ' AND m.id_message = mh.id_message'.
170 ' AND mh.value LIKE $3';
171 return $this->retrieve($sql,array(FORUMML_SUBJECT,$list_id,$pattern));
175 function hasArchives($list_id) {
176 $list_id = $this->da->quoteSmart($list_id);
177 $qry = 'SELECT NULL FROM plugin_forumml_message WHERE id_list = $1 LIMIT 1';
178 return $this->retrieve($qry,array($list_id));
181 function insertMessageHeader($id_message, $id_header,$value) {
182 $id_message = $this->da->quoteSmart($id_message);
183 $id_header = $this->da->quoteSmart($id_header);
184 $value = $this->da->quoteSmart($value);
185 $qry = 'INSERT INTO plugin_forumml_messageheader'.
186 ' (id_message, id_header, value)'.
187 ' VALUES ($1,$2,$3)';
188 return $this->update($qry,array($id_message , $id_header , $value));
190 function insertAttachment ($id_message, $filename,$filetype,$filesize,$filepath,$content_id) {
192 $id_message = $this->da->quoteSmart($id_message);
193 $filename = $this->da->quoteSmart($filename);
194 $filetype = $this->da->quoteSmart($filetype);
195 $filesize = $this->da->quoteSmart($filesize);
196 $filepath = $this->da->quoteSmart($filepath);
197 $content_id = $this->da->quoteSmart($content_id);
198 $qry = 'INSERT INTO plugin_forumml_attachment'.
199 ' (id_message, file_name, file_type, file_size, file_path, content_id)'.
200 ' VALUES ($1,$2,$3,$4,$5,$6)';
201 return $this->update($qry,array($id_message , $filename , $filetype , $filesize , $filepath , $content_id));
203 function searchHeader ($header) {
204 $header = $this->da->quoteSmart($header);
205 $qry = 'SELECT id_header'.
206 ' FROM plugin_forumml_header'.
208 return $this->retrieve($qry,array($header));
211 function insertHeader($header) {
213 $header = $this->da->quoteSmart($header);
214 $sql = 'INSERT INTO plugin_forumml_header'.
217 return db_insertid($this->update($sql,array($header)),'plugin_forumml_header','id_header');
220 function getParentMessageFromHeader ($id_header) {
221 $id_header = $this->da->quoteSmart($id_header);
222 $qry = 'SELECT id_message'.
223 ' FROM plugin_forumml_messageheader'.
224 ' WHERE id_header = 1'.
226 return $this->retrieve($qry,array($id_header));
230 function getParents($messageId) {
231 $messageId = $this->da->quoteSmart($messageId);
232 $sql = 'SELECT id_parent, last_thread_update FROM plugin_forumml_message WHERE id_message = $1';
233 return $this->retrieve($sql,array($messageId));
236 function updateParentDate($messageId,$date) {
237 $messageId = $this->da->quoteSmart($messageId);
238 $date = $this->da->quoteSmart($date);
239 $sql = 'UPDATE plugin_forumml_message'.
240 ' SET last_thread_update =$1 '.
241 ' WHERE id_message=$2';
242 $this->update($sql,array($date,$messageId));
245 function insertMessage ($id_list,$id_parent, $body, $messageDate, $ctype) {
246 $id_list = $this->da->quoteSmart($id_list);
247 $id_parent = $this->da->quoteSmart($id_parent);
248 $body = $this->da->quoteSmart($body);
249 $messageDate = $this->da->quoteSmart($messageDate);
250 $ctype = $this->da->quoteSmart($ctype);
252 $sql = 'INSERT INTO plugin_forumml_message'.
253 ' ( id_list, id_parent, body, last_thread_update, msg_type)'.
254 ' VALUES ($1, $2, $3, $4, $5)';
255 return db_insertid($this->update($sql,array($id_list , $id_parent , $body , $messageDate , $ctype)),'plugin_forumml_message' ,'id_message');