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';
23 define('FORUMML_MESSAGE_ID', 1);
24 define('FORUMML_DATE', 2);
25 define('FORUMML_FROM', 3);
26 define('FORUMML_SUBJECT', 4);
27 define('FORUMML_CONTENT_TYPE', 12);
28 define('FORUMML_CC', 34);
29 class ForumML_MessageDao extends DataAccessObject {
31 function __construct($da) {
32 parent::__construct($da);
35 function searchHeaderValue($messageId, $headerId) {
36 $messageId = $this->da->quoteSmart($messageId);
37 $headerId = $this->da->quoteSmart($headerId);
38 $sql = 'SELECT mh.value'.
39 ' FROM plugin_forumml_message m'.
40 ' JOIN plugin_forumml_messageheader mh'.
41 ' ON (mh.id_message = m.id_message)'.
42 ' JOIN plugin_forumml_header h'.
43 ' ON (h.id_header = mh.id_header)'.
44 ' WHERE m.id_message = $1'.
45 ' AND h.id_header = $2';
46 return $this->retrieve($sql,array($messageId,$headerId));
49 function getMessageHeaders($id_message) {
50 $id_message = $this->da->quoteSmart($id_message);
51 $sql = 'SELECT value'.
52 ' FROM plugin_forumml_messageheader'.
53 ' WHERE id_message = $1'.
55 ' ORDER BY id_header';
56 return $this->retrieve($sql,array($id_message));
59 function getSpecificMessage($id_message,$list_id) {
60 $id_message = $this->da->quoteSmart($id_message);
61 $list_id = $this->da->quoteSmart($list_id);
62 $sql ='SELECT value, body'.
63 ' FROM plugin_forumml_message m, plugin_forumml_messageheader mh'.
64 ' WHERE m.id_message =$1 '.
65 ' AND mh.id_message = m.id_message'.
66 ' AND m.id_list = $2'.
67 ' AND mh.id_header = $3';
68 return $this->retrieve($sql,array($id_message,$list_id,FORUMML_SUBJECT));
71 function getHeaderValue($id, $ids) {
72 $id = $this->da->quoteSmart($id);
73 $ids = $this->da->quoteSmart($ids);
75 $ids = 'SELECT id_header FROM plugin_forumml_messageheader';
77 $sql = 'SELECT value , id_header FROM plugin_forumml_messageheader'.
78 ' WHERE id_message =$1 and id_header IN ($2,$3) ORDER BY id_header DESC';
79 return $this->retrieve($sql,array($id,$ids[0],$ids[1]));
82 function getAllThreadsFromList($list_id,$offset,$chunks) {
83 $list_id = $this->da->quoteSmart($list_id);
84 $offset = $this->da->quoteSmart($offset);
85 $chunks = $this->da->quoteSmart($chunks);
86 $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'.
87 ' FROM plugin_forumml_message m'.
88 ' LEFT JOIN plugin_forumml_messageheader mh_d ON (mh_d.id_message = m.id_message AND mh_d.id_header = $1)'.
89 ' LEFT JOIN plugin_forumml_messageheader mh_f ON (mh_f.id_message = m.id_message AND mh_f.id_header = $2) '.
90 ' LEFT JOIN plugin_forumml_messageheader mh_s ON (mh_s.id_message = m.id_message AND mh_s.id_header = $3) '.
91 ' WHERE m.id_parent = 0'.
93 ' ORDER BY m.last_thread_update DESC'.
96 return $this->retrieve($sql,array(FORUMML_DATE,FORUMML_FROM,FORUMML_SUBJECT,$list_id,$offset,$chunks));
99 function countAllThreadsFromList($list_id) {
100 $list_id = $this->da->quoteSmart($list_id);
101 $sql = 'SELECT COUNT(*) as nb'.
102 ' FROM plugin_forumml_message m'.
103 ' LEFT JOIN plugin_forumml_messageheader mh_d ON (mh_d.id_message = m.id_message AND mh_d.id_header = $1)'.
104 ' LEFT JOIN plugin_forumml_messageheader mh_f ON (mh_f.id_message = m.id_message AND mh_f.id_header = $2) '.
105 ' LEFT JOIN plugin_forumml_messageheader mh_s ON (mh_s.id_message = m.id_message AND mh_s.id_header = $3) '.
106 ' WHERE m.id_parent = 0'.
107 ' AND id_list = $4 GROUP BY m.last_thread_update'.
108 ' ORDER BY m.last_thread_update DESC';
109 return $this->retrieve($sql,array(FORUMML_DATE,FORUMML_FROM,FORUMML_SUBJECT,$list_id));
112 function countChildrenFromParents ($parents) {
113 $sql = 'SELECT id_message'.
114 ' FROM plugin_forumml_message m'.
115 ' WHERE m.id_parent IN ($1)';
116 return $this->retrieve($sql,array($parents));
119 function getChildrenFromDepthLevel($parents) {
120 $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'.
121 ' FROM plugin_forumml_message m'.
122 ' LEFT JOIN plugin_forumml_messageheader mh_d ON (mh_d.id_message = m.id_message AND mh_d.id_header = $1)'.
123 ' LEFT JOIN plugin_forumml_messageheader mh_f ON (mh_f.id_message = m.id_message AND mh_f.id_header = $2) '.
124 ' LEFT JOIN plugin_forumml_messageheader mh_s ON (mh_s.id_message = m.id_message AND mh_s.id_header = $3) '.
125 ' LEFT JOIN plugin_forumml_messageheader mh_ct ON (mh_ct.id_message = m.id_message AND mh_ct.id_header = $4) '.
126 ' LEFT JOIN plugin_forumml_messageheader mh_cc ON (mh_cc.id_message = m.id_message AND mh_cc.id_header = $5) '.
127 " LEFT JOIN plugin_forumml_attachment a ON (a.id_message = m.id_message AND a.content_id = '')".
128 ' WHERE m.id_parent IN ($6)';
130 return $this->retrieve($sql,array(FORUMML_DATE,FORUMML_FROM,FORUMML_SUBJECT,FORUMML_CONTENT_TYPE,FORUMML_CC,$parents));
133 function getFlattenedThread($topic) {
134 $topic = $this->da->quoteSmart($topic);
135 $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'.
136 ' FROM plugin_forumml_message m'.
137 ' LEFT JOIN plugin_forumml_messageheader mh_d ON (mh_d.id_message = m.id_message AND mh_d.id_header = $1)'.
138 ' LEFT JOIN plugin_forumml_messageheader mh_f ON (mh_f.id_message = m.id_message AND mh_f.id_header = $2)'.
139 ' LEFT JOIN plugin_forumml_messageheader mh_s ON (mh_s.id_message = m.id_message AND mh_s.id_header = $3)'.
140 ' LEFT JOIN plugin_forumml_messageheader mh_ct ON (mh_ct.id_message = m.id_message AND mh_ct.id_header = $4)'.
141 ' LEFT JOIN plugin_forumml_messageheader mh_cc ON (mh_cc.id_message = m.id_message AND mh_cc.id_header = $5)'.
142 " LEFT JOIN plugin_forumml_attachment a ON (a.id_message = m.id_message AND a.content_id = '')".
143 ' WHERE m.id_message =$6 ';
144 return $this->retrieve($sql,array(FORUMML_DATE,FORUMML_FROM,FORUMML_SUBJECT,FORUMML_CONTENT_TYPE,FORUMML_CC,$topic));
147 function updateCacheHTML($cache,$id) {
148 $cache = $this->da->quoteSmart($cache);
149 $id = $this->da->quoteSmart($id);
150 return $this->update('UPDATE plugin_forumml_message SET cached_html= $1 WHERE id_message= $2',array($cache,$id));
153 function getAttachment($id_message,$match) {
154 $id_message = $this->da->quoteSmart($id_message);
155 $match = $this->da->quoteSmart($match);
156 $sql = 'SELECT id_attachment FROM plugin_forumml_attachment WHERE id_message=$1 and content_id=<$2>';
157 return $this->retrieve($sql,array($id_message , $match));
159 function searchArchives($list_id,$pattern) {
160 $list_id = $this->da->quoteSmart($list_id);
161 $pattern = $this->da->quoteSmart($pattern);
162 $sql = 'SELECT mh.id_message, mh.value'.
163 ' FROM plugin_forumml_message m, plugin_forumml_messageheader mh'.
164 ' WHERE mh.id_header = $1'.
165 ' AND m.id_list = $2'.
166 ' AND m.id_parent = 0'.
167 ' AND m.id_message = mh.id_message'.
168 ' AND mh.value LIKE $3';
169 return $this->retrieve($sql,array(FORUMML_SUBJECT,$list_id,$pattern));
173 function hasArchives($list_id) {
174 $list_id = $this->da->quoteSmart($list_id);
175 $qry = 'SELECT NULL FROM plugin_forumml_message WHERE id_list = $1 LIMIT 1';
176 return $this->retrieve($qry,array($list_id));
179 function insertMessageHeader($id_message, $id_header,$value) {
180 $id_message = $this->da->quoteSmart($id_message);
181 $id_header = $this->da->quoteSmart($id_header);
182 $value = $this->da->quoteSmart($value);
183 $qry = 'INSERT INTO plugin_forumml_messageheader'.
184 ' (id_message, id_header, value)'.
185 ' VALUES ($1,$2,$3)';
186 return $this->update($qry,array($id_message , $id_header , $value));
188 function insertAttachment ($id_message, $filename,$filetype,$filesize,$filepath,$content_id) {
190 $id_message = $this->da->quoteSmart($id_message);
191 $filename = $this->da->quoteSmart($filename);
192 $filetype = $this->da->quoteSmart($filetype);
193 $filesize = $this->da->quoteSmart($filesize);
194 $filepath = $this->da->quoteSmart($filepath);
195 $content_id = $this->da->quoteSmart($content_id);
196 $qry = 'INSERT INTO plugin_forumml_attachment'.
197 ' (id_message, file_name, file_type, file_size, file_path, content_id)'.
198 ' VALUES ($1,$2,$3,$4,$5,$6)';
199 return $this->update($qry,array($id_message , $filename , $filetype , $filesize , $filepath , $content_id));
201 function searchHeader ($header) {
202 $header = $this->da->quoteSmart($header);
203 $qry = 'SELECT id_header'.
204 ' FROM plugin_forumml_header'.
206 return $this->retrieve($qry,array($header));
209 function insertHeader($header) {
211 $header = $this->da->quoteSmart($header);
212 $sql = 'INSERT INTO plugin_forumml_header'.
215 return db_insertid($this->update($sql,array($header)),'plugin_forumml_header','id_header');
218 function getParentMessageFromHeader ($id_header) {
219 $id_header = $this->da->quoteSmart($id_header);
220 $qry = 'SELECT id_message'.
221 ' FROM plugin_forumml_messageheader'.
222 ' WHERE id_header = 1'.
224 return $this->retrieve($qry,array($id_header));
228 function getParents($messageId) {
229 $messageId = $this->da->quoteSmart($messageId);
230 $sql = 'SELECT id_parent, last_thread_update FROM plugin_forumml_message WHERE id_message = $1';
231 return $this->retrieve($sql,array($messageId));
234 function updateParentDate($messageId,$date) {
235 $messageId = $this->da->quoteSmart($messageId);
236 $date = $this->da->quoteSmart($date);
237 $sql = 'UPDATE plugin_forumml_message'.
238 ' SET last_thread_update =$1 '.
239 ' WHERE id_message=$2';
240 $this->update($sql,array($date,$messageId));
243 function insertMessage ($id_list,$id_parent, $body, $messageDate, $ctype) {
244 $id_list = $this->da->quoteSmart($id_list);
245 $id_parent = $this->da->quoteSmart($id_parent);
246 $body = $this->da->quoteSmart($body);
247 $messageDate = $this->da->quoteSmart($messageDate);
248 $ctype = $this->da->quoteSmart($ctype);
250 $sql = 'INSERT INTO plugin_forumml_message'.
251 ' ( id_list, id_parent, body, last_thread_update, msg_type)'.
252 ' VALUES ($1, $2, $3, $4, $5)';
253 return db_insertid($this->update($sql,array($id_list , $id_parent , $body , $messageDate , $ctype)),'plugin_forumml_message' ,'id_message');