1: <?php
  2: /**
  3:  * This file contains the collection class for userforum plugin.
  4:  *
  5:  * @package Plugin
  6:  * @subpackage UserForum
  7:  * @version SVN Revision $Rev:$
  8:  *
  9:  * @author Claus Schunk
 10:  * @copyright four for business AG <www.4fb.de>
 11:  * @license http://www.contenido.org/license/LIZENZ.txt
 12:  * @link http://www.4fb.de
 13:  * @link http://www.contenido.org
 14:  */
 15: defined('CON_FRAMEWORK') || die('Illegal call: Missing framework initialization - request aborted.');
 16: 
 17: /**
 18:  * This class contains functions for dB manipulations and for the interaction
 19:  * between the frontend module
 20:  * content_user_forum and the backend plugin.
 21:  *
 22:  * @package Plugin
 23:  * @subpackage UserForum
 24:  */
 25: class ArticleForumCollection extends ItemCollection {
 26: 
 27:     protected $cfg = 0;
 28: 
 29:     protected $db = 0;
 30: 
 31:     protected $item = 0;
 32:     // contents array of translations from frontend module
 33:     protected $languageSync = 0;
 34: 
 35:     protected $idContentType = 0;
 36: 
 37:     public function __construct() {
 38:         // sync time
 39:         date_default_timezone_set('Europe/Berlin');
 40:         $this->db = cRegistry::getDb();
 41:         $this->cfg = cRegistry::getConfig();
 42: 
 43:         parent::__construct($this->cfg['tab']['user_forum'], 'id_user_forum');
 44:         $this->_setItemClass('ArticleForum');
 45:         $this->item = new ArticleForumItem();
 46:         $this->idContentType = $this->getIdUserForumContenType();
 47:     }
 48: 
 49:     public function getAllCommentedArticles() {
 50:         $sql = "SELECT DISTINCT t.title, t.idart, f.idcat FROM con_art_lang t," . $this->table . " f WHERE f.idart=t.idart AND t.idlang = f.idlang ORDER BY id_user_forum ASC ;";
 51:         $this->db->query($sql);
 52:         $data = array();
 53:         while ($this->db->next_record()) {
 54:             array_push($data, $this->db->toArray());
 55:         }
 56: 
 57:         return $data;
 58:     }
 59: 
 60:     /**
 61:      * deletes comment with all subcomments from this comment
 62:      *
 63:      * @param $keyPost
 64:      * @param $level
 65:      * @param $idart
 66:      * @param $idcat
 67:      * @param $lang
 68:      */
 69:     public function deleteHierarchie($keyPost, $level, $idart, $idcat, $lang) {
 70:         $comments = $this->_getCommentHierachrie($idcat, $idart, $lang);
 71: 
 72:         $arri = array();
 73: 
 74:         foreach ($comments as $key => $com) {
 75:             $com['key'] = $key;
 76:             $arri[] = $com;
 77:         }
 78:         $idEntry = 0;
 79:         $id_user_forum = array();
 80:         $lastLevel = 0;
 81:         for ($i = 0; $i < count($arri); $i++) {
 82:             // select Entry
 83:             if ($arri[$i]['key'] == $keyPost) {
 84:                 $idEntry = $arri[$i]['id_user_forum'];
 85:                 if ($arri[$i]['level'] < $arri[$i + 1]['level']) {
 86:                     // check for more subcomments
 87:                     for ($j = $i + 1; $j < $arri[$j]; $j++) {
 88:                         if ($arri[$i]['level'] < $arri[$j]['level']) {
 89:                             $id_user_forum[] = $arri[$j]['id_user_forum'];
 90:                         }
 91:                     }
 92:                 }
 93:             }
 94:         }
 95: 
 96:         if (empty($id_user_forum)) {
 97:             $this->deleteBy('id_user_forum', $idEntry);
 98:         } else {
 99:             $this->deleteBy('id_user_forum', $idEntry);
100:             foreach ($id_user_forum as $com) {
101:                 $this->deleteBy('id_user_forum', $com);
102:             }
103:         }
104:     }
105: 
106:     protected function _getCommentHierachrie($id_cat, $id_art, $id_lang) {
107:         $this->query();
108:         while (false != $field = $this->next()) {
109:             $arrUsers[$field->get('userid')]['email'] = $field->get('email');
110:             $arrUsers[$field->get('userid')]['realname'] = $field->get('realname');
111:         }
112:         $arrforum = array();
113:         $this->getTreeLevel($id_cat, $id_art, $id_lang, $arrUsers, $arrforum);
114:         $result = array();
115:         $this->normalizeArray($arrforum, $result);
116: 
117:         return $result;
118:     }
119: 
120:     public function normalizeArray($arrforum, &$result, $level = 0) {
121:         if (is_array($arrforum)) {
122:             foreach ($arrforum as $key => $value) {
123:                 $value['level'] = $level;
124:                 unset($value['children']);
125:                 $result[$key] = $value;
126:                 $this->normalizeArray($arrforum[$key]['children'], $result, $level + 1);
127:             }
128:         }
129:     }
130: 
131:     public function getTreeLevel($id_cat, $id_art, $id_lang, &$arrUsers, &$arrforum, $parent = 0, $frontend = false) {
132:         $db = cRegistry::getDb();
133: 
134:         if ($frontend) {
135:             // select only comments that are marked visible in frontendmode.
136:             $query = "SELECT * FROM con_pi_user_forum WHERE (idart = $id_art) AND (idcat = $id_cat)
137:             AND (idlang = $id_lang) AND (id_user_forum_parent = $parent) AND (online = 1) ORDER BY timestamp DESC";
138:         } else {
139:             // select all comments -> used in backendmode.
140:             $query = "SELECT * FROM con_pi_user_forum WHERE (idart = $id_art) AND (idcat = $id_cat)
141:         AND (idlang = $id_lang) AND (id_user_forum_parent = $parent) ORDER BY timestamp DESC";
142:         }
143:         $db->query($query);
144: 
145:         while ($db->next_record()) {
146:             $arrforum[$db->f('id_user_forum')]['userid'] = $db->f('userid');
147: 
148:             if (array_key_exists($db->f('userid'), $arrUsers)) {
149:                 $arrforum[$db->f('id_user_forum')]['email'] = $arrUsers[$db->f('userid')]['email'];
150:                 $arrforum[$db->f('id_user_forum')]['realname'] = $arrUsers[$db->f('userid')]['realname'];
151:             } else {
152:                 $arrforum[$db->f('id_user_forum')]['email'] = $db->f('email');
153:                 $arrforum[$db->f('id_user_forum')]['realname'] = $db->f('realname');
154:             }
155:             $arrforum[$db->f('id_user_forum')]['forum'] = str_replace(chr(13) . chr(10), '<br />', $db->f('forum'));
156:             $arrforum[$db->f('id_user_forum')]['forum_quote'] = str_replace(chr(13) . chr(10), '<br />', $db->f('forum_quote'));
157:             $arrforum[$db->f('id_user_forum')]['timestamp'] = $db->f('timestamp');
158:             $arrforum[$db->f('id_user_forum')]['like'] = $db->f('like');
159:             $arrforum[$db->f('id_user_forum')]['dislike'] = $db->f('dislike');
160: 
161:             $arrforum[$db->f('id_user_forum')]['editedat'] = $db->f('editedat');
162:             $arrforum[$db->f('id_user_forum')]['editedby'] = $db->f('editedby');
163: 
164:             // Added values to array for allocation
165:             $arrforum[$db->f('id_user_forum')]['idcat'] = $db->f('idcat');
166:             $arrforum[$db->f('id_user_forum')]['idart'] = $db->f('idart');
167:             $arrforum[$db->f('id_user_forum')]['id_user_forum'] = $db->f('id_user_forum');
168:             $arrforum[$db->f('id_user_forum')]['online'] = $db->f('online');
169:             $arrforum[$db->f('id_user_forum')]['editedat'] = $db->f('editedat');
170:             $arrforum[$db->f('id_user_forum')]['editedby'] = $db->f('editedby');
171: 
172:             $this->getTreeLevel($id_cat, $id_art, $id_lang, $arrUsers, $arrforum[$db->f('id_user_forum')]['children'], $db->f('id_user_forum'), $frontend);
173:         }
174:     }
175: 
176:     public function updateValues($id_user_forum, $name, $email, $like, $dislike, $forum, $online, $checked) {
177:         $uuid = cRegistry::getAuth()->isAuthenticated();
178: 
179:         $this->item->loadByPrimaryKey($id_user_forum);
180: 
181:         if ($this->item->getField('realname') == $name && $this->item->getField('email') == $email && $this->item->getField('forum') == $forum) {
182: 
183:             // load timestamp from db to check if the article was already
184:             // edited.
185:             if ($this->item->getField('editedat') === "0000-00-00 00:00:00") {
186:                 // case : never edited
187:                 $timeStamp = "0000-00-00 00:00:00";
188:             } else {
189:                 $timeStamp = $this->item->getField('editedat');
190:             }
191:         } else {
192:             // actual timestamp: Content was edited
193:             $timeStamp = date('Y-m-d H:i:s', time());
194:         }
195: 
196:         if (preg_match('/\D/', $like)) {
197:             $like = $this->item->getField('like');
198:         }
199: 
200:         if (preg_match('/\D/', $dislike)) {
201:             $dislike = $this->item->getField('dislike');
202:         }
203: 
204:         // check for negative inputs
205:         // does not work with php 5.2
206:         // (!preg_match('/\D/', $like))? : $like =
207:         // $this->item->getField('like');
208:         // (!preg_match('/\D/', $dislike))? : $dislike =
209:         // $this->item->getField('dislike');
210: 
211:         $fields = array(
212:             'realname' => cSecurity::escapeDB($name, $this->db),
213:             'editedby' => cSecurity::escapeDB($uuid, $this->db),
214:             'email' => cSecurity::escapeDB($email, $this->db),
215:             'forum' => $forum,
216:             'editedat' => cSecurity::escapeDB($timeStamp, $this->db),
217:             'like' => cSecurity::escapeDB($like, $this->db),
218:             'dislike' => cSecurity::escapeDB($dislike, $this->db),
219:             'online' => cSecurity::escapeDB($online, $this->db)
220:         );
221: 
222:         $whereClauses = array(
223:             'id_user_forum' => $id_user_forum
224:         );
225:         $statement = $this->db->buildUpdate($this->table, $fields, $whereClauses);
226:         $this->db->query($statement);
227:     }
228: 
229:     /**
230:      * toggles the given input with update in db.
231:      *
232:      * @param $onlineState
233:      * @param primary key $id_user_forum
234:      */
235:     public function toggleOnlineState($onlineState, $id_user_forum) {
236:         // toggle state
237:         // ($onlineState == 0)? $onlineState = 1 : $onlineState = 0;
238:         if ($onlineState == 0) {
239:             $onlineState = 1;
240:         } else {
241:             $onlineState = 0;
242:         }
243: 
244:         $fields = array(
245:             'online' => cSecurity::escapeDB($onlineState, $this->db)
246:         );
247:         $whereClauses = array(
248:             'id_user_forum' => cSecurity::escapeDB($id_user_forum, $this->db)
249:         );
250:         $statement = $this->db->buildUpdate($this->table, $fields, $whereClauses);
251:         $this->db->query($statement);
252:     }
253: 
254:     /**
255:      * email notification for registred moderator.
256:      * before calling this function it is necessary to receive the converted
257:      * language string from frontend module.
258:      */
259:     public function mailToModerator($realname, $email, $forum, $idart, $lang, $forum_quote = 0) {
260: 
261:         // get article name
262:         $ar = $this->getArticleTitle($idart, $lang);
263: 
264:         $mail = new cMailer();
265:         $mail->setCharset('UTF-8');
266: 
267:         // build message content
268:         $message = $this->languageSync['NEWENTRYTEXT'] . " " . $this->languageSync['ARTICLE'] . $ar[0]["title"] . "\n" . "\n";
269:         $message .= $this->languageSync['USER'] . ' : ' . $realname . "\n";
270:         $message .= $this->languageSync['EMAIL'] . ' : ' . $email . "\n" . "\n";
271:         $message .= $this->languageSync['COMMENT'] . ' : ' . "\n" . $forum . "\n";
272:         if ($forum_quote != 0) {
273:             $message .= UserForum::i18n('QUOTE') . ' : ' . $forum_quote . "\n";
274:         }
275: 
276:         // send mail only if modEmail is set -> minimize traffic.
277:         if ($this->getModEmail($idart) != NULL) {
278:             $mail->sendMail(getEffectiveSetting("userforum", "mailfrom"), $this->getModEmail($idart), $this->languageSync['NEWENTRY'], $message);
279:         }
280:     }
281: 
282:     public function getArticleTitle($idart, $idlang) {
283:         $sql = "SELECT DISTINCT t.title FROM con_art_lang t WHERE t.idart=$idart AND t.idlang=$idlang;";
284:         $this->db->query($sql);
285:         $data = array();
286:         while ($this->db->next_record()) {
287:             array_push($data, $this->db->toArray());
288:         }
289:         return $data;
290:     }
291: 
292:     /**
293:      *
294:      * @param $id_cat
295:      * @param $id_art
296:      * @param $id_lang
297:      * @return ArticleForumRightBottom
298:      *
299:      */
300:     public function getExistingforum($id_cat, $id_art, $id_lang) {
301:         $userColl = new cApiUserCollection();
302:         $userColl->query();
303: 
304:         while (($field = $userColl->next()) != false) {
305:             $arrUsers[$field->get('user_id')]['email'] = $field->get('email');
306:             $arrUsers[$field->get('user_id')]['realname'] = $field->get('realname');
307:         }
308:         return $arrUsers;
309:     }
310: 
311:     function selectNameAndNameByForumId($idquote) {
312:         $ar = array();
313:         $this->item->loadByPrimaryKey(cSecurity::escapeDB($idquote, $this->db));
314:         $ar[] = $this->item->get('realname');
315:         return $ar;
316:     }
317: 
318:     public function selectUser($userid) {
319:         return $this->item->loadByPrimaryKey(cSecurity::escapeDB($userid, $this->db));
320:     }
321: 
322:     /**
323:      * this function inkrements the actual value of likes from a comment and
324:      * persists it.
325:      *
326:      * @param $forum_user_id identifies a comment.
327:      */
328:     public function incrementLike($forum_user_id) {
329:         $db = cRegistry::getDb();
330:         $ar = array();
331:         // load actual value
332:         $this->item->loadByPrimaryKey(cSecurity::escapeDB($forum_user_id, $db));
333:         $ar = $this->item->toArray();
334:         $current = $ar['like'];
335:         // increment value
336:         $current += 1;
337: 
338:         $fields = array(
339:             'like' => $current
340:         );
341:         $whereClauses = array(
342:             'id_user_forum' => $forum_user_id
343:         );
344:         // persist inkremented value
345:         $statement = $this->db->buildUpdate($this->table, $fields, $whereClauses);
346:         $this->db->query($statement);
347:     }
348: 
349:     /**
350:      * this function inkrements the actual value of dislikes from a comment and
351:      * persists it.
352:      *
353:      * @param $forum_user_id identifies a comment.
354:      */
355:     public function incrementDislike($forum_user_id) {
356:         $db = cRegistry::getDb();
357:         $ar = array();
358:         // load actual value
359:         $this->item->loadByPrimaryKey(cSecurity::escapeDB($forum_user_id, $db));
360:         $ar = $this->item->toArray();
361:         $current = $ar['dislike'];
362:         // increment value
363:         $current += 1;
364: 
365:         $fields = array(
366:             'dislike' => $current
367:         );
368:         $whereClauses = array(
369:             'id_user_forum' => $forum_user_id
370:         );
371:         // persist inkremented value
372:         $statement = $this->db->buildUpdate($this->table, $fields, $whereClauses);
373:         $this->db->query($statement);
374:     }
375: 
376:     /**
377:      * persists a new comment created at the frontend module.
378:      *
379:      * @param $parent
380:      * @param $idart
381:      * @param $idcat
382:      * @param $lang
383:      * @param $userid
384:      * @param $email
385:      * @param $realname
386:      * @param $forum
387:      * @param $forum_quote
388:      */
389:     public function insertValues($parent, $idart, $idcat, $lang, $userid, $email, $realname, $forum, $forum_quote) {
390:         $db = cRegistry::getDb();
391: 
392:         // comments are marked as offline if the moderator mode is turned on.
393:         // ($modCheck = $this->getModeModeActive($idart))? $online = 0 : $online
394:         // = 1;
395: 
396:         if ($modCheck = $this->getModeModeActive($idart)) {
397:             $online = 0;
398:         } else {
399:             $online = 1;
400:         }
401:         // build array for sql statemant
402:         $fields = array(
403:             'id_user_forum' => NULL,
404:             'id_user_forum_parent' => cSecurity::escapeDB($parent, $db),
405:             'idart' => cSecurity::escapeDB($idart, $db),
406:             'idcat' => cSecurity::escapeDB($idcat, $db),
407:             'idlang' => cSecurity::escapeDB($lang, $db),
408:             'userid' => cSecurity::escapeDB($userid, $db),
409:             'email' => cSecurity::escapeDB($email, $db),
410:             'realname' => cSecurity::escapeDB($realname, $db),
411:             'forum' => ($forum),
412:             'forum_quote' => ($forum_quote),
413:             'like' => 0,
414:             'dislike' => 0,
415:             'editedat' => NULL,
416:             'editedby' => NULL,
417:             'timestamp' => date('Y-m-d H:i:s'),
418:             'online' => $online
419:         );
420: 
421:         $db->insert($this->table, $fields);
422: 
423:         // if moderator mode is turned on the moderator will receive an email
424:         // with the new comment and is able to
425:         // change the online state in the backend.
426:         if ($modCheck) {
427:             $this->mailToModerator($realname, $email, $forum, $idart, $lang, $forum_quote = 0);
428:         }
429:     }
430: 
431:     /**
432:      * this function deletes all comments related to the same articleId
433:      *
434:      * @param articleId $idart
435:      */
436:     public function deleteAllCommentsById($idart) {
437:         // var_dump($idart);
438:         $this->deleteBy('idart', cSecurity::escapeDB(($idart), $this->db));
439:     }
440: 
441:     public function getExistingforumFrontend($id_cat, $id_art, $id_lang, $frontend) {
442:         global $cfg;
443: 
444:         $db = cRegistry::getDb();
445: 
446:         $userColl = new cApiUserCollection();
447:         $userColl->query();
448: 
449:         while (($field = $userColl->next()) != false) {
450:             $arrUsers[$field->get('user_id')]['email'] = $field->get('email');
451:             $arrUsers[$field->get('user_id')]['realname'] = $field->get('realname');
452:         }
453: 
454:         $arrforum = array();
455:         $this->getTreeLevel($id_cat, $id_art, $id_lang, $arrUsers, $arrforum, 0, $frontend);
456: 
457:         $result = array();
458:         $this->normalizeArray($arrforum, $result);
459:         return $result;
460:     }
461: 
462:     /**
463:      * returns the emailadress from the moderator for this article
464:      *
465:      * @param articleid $idart
466:      * @return string
467:      */
468:     public function getModEmail($idart) {
469:         $data = $this->readXML();
470:         for ($i = 0; $i < count($data); $i++) {
471:             if ($data[$i]['idart'] == $idart) {
472:                 return $data[$i]["email"];
473:             }
474:         }
475:         return NULL;
476:     }
477: 
478:     /**
479:      * returns if moderator mode is actice for this article
480:      *
481:      * @param articleid $idart
482:      * @return bool
483:      */
484:     public function getModeModeActive($idart) {
485:         $data = $this->readXML();
486:         for ($i = 0; $i < count($data); $i++) {
487:             if ($data[$i]['idart'] == $idart) {
488:                 if ($data[$i]["modactive"] === 'false') {
489:                     return false;
490:                 }
491:             }
492:         }
493:         return true;
494:     }
495: 
496:     /**
497:      * returns if quotes for comments are allowed in this article
498:      *
499:      * @param articleid $idart
500:      * @return bool
501:      */
502:     public function getQuoteState($idart) {
503:         // get content from con_type
504:         $data = $this->readXML();
505:         for ($i = 0; $i < count($data); $i++) {
506:             if ($data[$i]['idart'] == $idart) {
507:                 if ($data[$i]["subcomments"] === 'false') {
508:                     return false;
509:                 }
510:             }
511:         }
512:         return true;
513:     }
514: 
515:     /**
516:      * This function loads and returns the xml content from the contentType
517:      * aditionally the return array implies the articleId because of an easier
518:      * mapping in the frontend.
519:      *
520:      * @return array
521:      */
522:     public function readXML() {
523:         // get variables from global context
524:         $catId = cRegistry::getCategoryId();
525:         $idclient = cRegistry::getClientId();
526:         $cfgClient = cRegistry::getClientConfig();
527:         $idtype = $this->idContentType;
528: 
529:         $sql = "SELECT t.value,f.idart FROM con_art_lang f , con_content t WHERE idtype=$idtype AND t.idartlang=f.idartlang;";
530:         try {
531:             $this->db->query($sql);
532:             $data = array();
533:             $ar = array();
534:             while ($this->db->next_record()) {
535:                 array_push($data, $this->db->toArray());
536:             }
537: 
538:             for ($i = 0; $i < count($data); $i++) {
539:                 $ar[$i] = cXmlBase::xmlStringToArray($data[$i]['value']);
540:                 // add articleId
541:                 $ar[$i]['idart'] = $data[$i]['idart'];
542:             }
543:         } catch (Exception $e) {
544:             // var_dump(e);
545:         }
546:         return $ar;
547:     }
548: 
549:     /**
550:      * this function is used to get translations from the language of the
551:      * frontend module for example to generate
552:      * the e-mail text with correct language settings.
553:      *
554:      * @param array $str
555:      */
556:     public function languageSync(array &$str) {
557:         $this->languageSync = $str;
558:     }
559: 
560:     public function getlanguageSync() {
561:         if ($this->languageSync != 0) {
562:             return $this->languageSync;
563:         } else {
564:             return array();
565:         }
566:     }
567: 
568:     public function getCommentContent($id_user_forum) {
569:         $ar = array();
570:         $item = $this->loadItem($id_user_forum);
571:         $ar['name'] = $item->get("realname");
572:         $ar['content'] = $item->get("forum");
573: 
574:         return $ar;
575:     }
576: 
577:     protected function getIdUserForumContenType() {
578:         $sql = "SELECT idtype from con_type WHERE type='CMS_USERFORUM';";
579:         $result = $this->db->query($sql);
580:         if ($this->db->next_record()) {
581:             return $this->db->f('idtype');
582:         } else {
583:             return false;
584:         }
585:     }
586: 
587: }
588: 
589: ?>