File indexing completed on 2025-03-09 05:22:43
0001 <?php 0002 0003 /** 0004 * ocs-webserver 0005 * 0006 * Copyright 2016 by pling GmbH. 0007 * 0008 * This file is part of ocs-webserver. 0009 * 0010 * This program is free software: you can redistribute it and/or modify 0011 * it under the terms of the GNU Affero General Public License as 0012 * published by the Free Software Foundation, either version 3 of the 0013 * License, or (at your option) any later version. 0014 * 0015 * This program is distributed in the hope that it will be useful, 0016 * but WITHOUT ANY WARRANTY; without even the implied warranty of 0017 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 0018 * GNU Affero General Public License for more details. 0019 * 0020 * You should have received a copy of the GNU Affero General Public License 0021 * along with this program. If not, see <http://www.gnu.org/licenses/>. 0022 **/ 0023 class Default_Model_ProjectComments 0024 { 0025 0026 /** @var string */ 0027 protected $_dataTableName; 0028 /** @var Default_Model_DbTable_Comments */ 0029 protected $_dataTable; 0030 /** @var array */ 0031 protected $data; 0032 /** @var array */ 0033 protected $index; 0034 0035 /** 0036 * PHP 5 allows developers to declare constructor methods for classes. 0037 * Classes which have a constructor method call this method on each newly-created object, 0038 * so it is suitable for any initialization that the object may need before it is used. 0039 * 0040 * Note: Parent constructors are not called implicitly if the child class defines a constructor. 0041 * In order to run a parent constructor, a call to parent::__construct() within the child constructor is required. 0042 * 0043 * param [ mixed $args [, $... ]] 0044 * 0045 * @param string $_dataTableName 0046 * 0047 * @link http://php.net/manual/en/language.oop5.decon.php 0048 */ 0049 public function __construct($_dataTableName = 'Default_Model_DbTable_Comments') 0050 { 0051 $this->_dataTableName = $_dataTableName; 0052 $this->_dataTable = new $this->_dataTableName; 0053 } 0054 0055 public function getCommentWithMember($comment_id) 0056 { 0057 $sql = " SELECT * 0058 FROM comments 0059 STRAIGHT_JOIN member ON comments.comment_member_id = member.member_id 0060 WHERE comment_id = :comment_id 0061 ORDER BY comments.comment_created_at DESC, comment_parent_id 0062 "; 0063 0064 $rowSet = $this->_dataTable->getAdapter()->fetchAll($sql, array('comment_id' => $comment_id)); 0065 if (0 == count($rowSet)) { 0066 return array(); 0067 } 0068 0069 return $rowSet[0]; 0070 } 0071 0072 public function getCommentFromSource($type = 0, $source_id, $source_pk) 0073 { 0074 $sql = " 0075 SELECT * 0076 FROM comments 0077 WHERE comment_type = :type AND source_id = :source_id AND source_pk = :source_pk 0078 "; 0079 0080 $rowset = $this->_dataTable->getAdapter()->fetchRow($sql, 0081 array('type' => $type, 'source_id' => $source_id, 'source_pk' => $source_pk)) 0082 ; 0083 if (!$rowset) { 0084 return false; 0085 } 0086 0087 return $rowset; 0088 } 0089 0090 /** 0091 * @param $project_id 0092 * 0093 * @return Zend_Paginator 0094 */ 0095 public function getCommentTreeForProjectList($project_id,$type=null) 0096 { 0097 $sql =" 0098 SELECT comments.comment_id 0099 , comment_target_id, comment_parent_id, comment_text, comment_created_at, comment_active, comment_type, 0100 member.member_id, username, profile_image_url 0101 ,( select count(distinct c.name) sections from 0102 section_support s, support t , section c 0103 where s.support_id = t.id and s.section_id = c.section_id 0104 and t.member_id = comments.comment_member_id and t.status_id>=2 0105 and s.is_active = 1 0106 ) as issupporter 0107 ,(select score from project_rating where project_rating.comment_id = comments.comment_id ) as rating 0108 ,(select score from project_rating r where r.project_id =:project_id and rating_active = 1 and r.member_id = comments.comment_member_id) as rating_member 0109 ,member.roleid 0110 ,tr.vote as tag_vote 0111 ,tag.tag_fullname 0112 FROM comments 0113 inner join member ON comments.comment_member_id = member.member_id 0114 left join tag_rating tr on tr.comment_id = comments.comment_id and tr.is_deleted = 0 0115 left join tag on tag.tag_id = tr.tag_id 0116 WHERE comment_active = :status_active AND comment_type =:type_id AND comment_target_id = :project_id AND comment_parent_id = 0 0117 ORDER BY comment_created_at DESC 0118 "; 0119 0120 0121 if($type==null) $type=Default_Model_DbTable_Comments::COMMENT_TYPE_PRODUCT; 0122 $rowset = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0123 'status_active' => 1, 0124 'type_id' => $type, 0125 'project_id' => $project_id 0126 )) 0127 ; 0128 $sql = " 0129 SELECT comments.comment_id, comment_target_id, comment_parent_id, comment_text, comment_created_at, comment_active 0130 , comment_type, member.member_id, username, profile_image_url 0131 ,( select count(distinct c.name) sections from 0132 section_support s, support t , section c 0133 where s.support_id = t.id and s.section_id = c.section_id 0134 and t.member_id = comments.comment_member_id and t.status_id>=2 0135 and s.is_active = 1 0136 ) as issupporter 0137 ,(select score from project_rating where project_rating.comment_id = comments.comment_id ) as rating 0138 ,(select score from project_rating r where r.project_id =:project_id and rating_active = 1 and r.member_id = comments.comment_member_id) as rating_member 0139 ,member.roleid 0140 ,tr.vote as tag_vote 0141 ,tag.tag_fullname 0142 FROM comments 0143 inner join member ON comments.comment_member_id = member.member_id 0144 left join tag_rating tr on tr.comment_id = comments.comment_id and tr.is_deleted = 0 0145 left join tag on tag.tag_id = tr.tag_id 0146 WHERE comment_active = :status_active AND comment_type = :type_id AND comment_target_id = :project_id AND comment_parent_id <> 0 0147 ORDER BY comment_created_at DESC 0148 "; 0149 $rowset2 = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0150 'status_active' => 1, 0151 'type_id' => $type, 0152 'project_id' => $project_id 0153 )) 0154 ; 0155 0156 0157 0158 $rowset = array_merge($rowset, $rowset2); 0159 0160 /* create array with comment_id as key */ 0161 foreach ($rowset as $item) { 0162 $this->data[$item['comment_id']] = $item; 0163 } 0164 /* create an array with all parent_id's and their immediate children */ 0165 foreach ($rowset as $item) { 0166 $this->index[$item['comment_parent_id']][] = $item['comment_id']; 0167 } 0168 /* create the final sorted array */ 0169 $list = array(); 0170 $this->sort_child_nodes(0, 1, $list); 0171 0172 return $list; 0173 } 0174 0175 0176 // /** 0177 // * @param $project_id 0178 // * 0179 // * @return Zend_Paginator 0180 // */ 0181 // public function getCommentTreeForProjectList($project_id,$type=null) 0182 // { 0183 0184 // $sql = " 0185 // SELECT comment_id, comment_target_id, comment_parent_id, comment_text, comment_created_at, comment_active, comment_type, member_id, username, profile_image_url 0186 // ,( select count(distinct c.name) sections from 0187 // section_support s, support t , section c 0188 // where s.support_id = t.id and s.section_id = c.section_id 0189 // and t.member_id = comments.comment_member_id and t.status_id=2 0190 // and s.is_active = 1 0191 // ) as issupporter 0192 // ,(select score from project_rating where project_rating.comment_id = comments.comment_id ) as rating 0193 // ,(select score from project_rating r where r.project_id =:project_id and rating_active = 1 and r.member_id = comments.comment_member_id) as rating_member 0194 // ,member.roleid 0195 // FROM comments 0196 // STRAIGHT_JOIN member ON comments.comment_member_id = member.member_id 0197 // WHERE comment_active = :status_active AND comment_type = :type_id AND comment_target_id = :project_id AND comment_parent_id = 0 0198 // ORDER BY comment_created_at DESC 0199 // "; 0200 // if($type==null) $type=Default_Model_DbTable_Comments::COMMENT_TYPE_PRODUCT; 0201 // $rowset = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0202 // 'status_active' => 1, 0203 // 'type_id' => $type, 0204 // 'project_id' => $project_id 0205 // )) 0206 // ; 0207 // $sql = " 0208 // SELECT comment_id, comment_target_id, comment_parent_id, comment_text, comment_created_at, comment_active, comment_type, member_id, username, profile_image_url 0209 // ,( select count(distinct c.name) sections from 0210 // section_support s, support t , section c 0211 // where s.support_id = t.id and s.section_id = c.section_id 0212 // and t.member_id = comments.comment_member_id and t.status_id=2 0213 // and s.is_active = 1 0214 // ) as issupporter 0215 // ,(select score from project_rating where project_rating.comment_id = comments.comment_id ) as rating 0216 // ,(select score from project_rating r where r.project_id =:project_id and rating_active = 1 and r.member_id = comments.comment_member_id) as rating_member 0217 // ,member.roleid 0218 // FROM comments 0219 // STRAIGHT_JOIN member ON comments.comment_member_id = member.member_id 0220 // WHERE comment_active = :status_active AND comment_type = :type_id AND comment_target_id = :project_id AND comment_parent_id <> 0 0221 // ORDER BY comment_created_at DESC 0222 // "; 0223 // $rowset2 = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0224 // 'status_active' => 1, 0225 // 'type_id' => $type, 0226 // 'project_id' => $project_id 0227 // )) 0228 // ; 0229 0230 0231 0232 // $rowset = array_merge($rowset, $rowset2); 0233 0234 // /* create array with comment_id as key */ 0235 // foreach ($rowset as $item) { 0236 // $this->data[$item['comment_id']] = $item; 0237 // } 0238 // /* create an array with all parent_id's and their immediate children */ 0239 // foreach ($rowset as $item) { 0240 // $this->index[$item['comment_parent_id']][] = $item['comment_id']; 0241 // } 0242 // /* create the final sorted array */ 0243 // $list = array(); 0244 // $this->sort_child_nodes(0, 1, $list); 0245 0246 // return $list; 0247 // } 0248 0249 /** 0250 * @param $project_id 0251 * 0252 * @return Zend_Paginator 0253 */ 0254 public function getCommentTreeForProject($project_id,$type=0) 0255 { 0256 0257 $list = $this->getCommentTreeForProjectList($project_id,$type); 0258 0259 return new Zend_Paginator(new Zend_Paginator_Adapter_Array($list)); 0260 } 0261 0262 /** 0263 * @param int $parent_id 0264 * @param int $level 0265 * @param null $result 0266 * 0267 * @return array|null 0268 */ 0269 function sort_child_nodes($parent_id, $level, &$result = null) 0270 { 0271 // array(array('comment' => $rootElement, 'level' => 1)); 0272 $parent_id = $parent_id === null ? "NULL" : $parent_id; 0273 if (isset($this->index[$parent_id])) { 0274 foreach ($this->index[$parent_id] as $id) { 0275 $result[] = array('comment' => $this->data[$id], 'level' => $level); 0276 $this->sort_child_nodes($id, $level + 1, $result); 0277 } 0278 } 0279 } 0280 0281 /** 0282 * @param int $project_id 0283 * 0284 * @return Zend_Paginator 0285 */ 0286 public function getAllCommentsForProject($project_id) 0287 { 0288 $rootElements = $this->getRootCommentsForProject($project_id); 0289 $returnValue = array(); 0290 foreach ($rootElements as $rootElement) { 0291 $resultElement = array(array('comment' => $rootElement, 'level' => 1)); 0292 $childs = $this->getAllChildComments($resultElement); 0293 if (0 == count($childs)) { 0294 $returnValue = array_merge($returnValue, $resultElement); 0295 } else { 0296 $returnValue = array_merge($returnValue, $childs); 0297 } 0298 } 0299 0300 return new Zend_Paginator(new Zend_Paginator_Adapter_Array($returnValue)); 0301 } 0302 0303 /** 0304 * @param int $_projectId 0305 * 0306 * @return array 0307 */ 0308 public function getRootCommentsForProject($_projectId) 0309 { 0310 $sql = ' 0311 SELECT * 0312 FROM comments 0313 STRAIGHT_JOIN member ON comments.comment_member_id = member.member_id 0314 WHERE comment_target_id = :project_id 0315 AND comment_parent_id = 0 0316 AND comment_type = :type_id 0317 AND comment_active = :status 0318 ORDER BY comments.comment_created_at DESC, comment_parent_id 0319 '; 0320 0321 $rowset = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0322 'project_id' => $_projectId, 0323 'status' => Default_Model_DbTable_Comments::COMMENT_ACTIVE, 0324 'type_id' => Default_Model_DbTable_Comments::COMMENT_TYPE_PRODUCT 0325 )) 0326 ; 0327 if (0 == count($rowset)) { 0328 return array(); 0329 } 0330 0331 return $rowset; 0332 } 0333 0334 /** 0335 * @param array $element 0336 * 0337 * @return array 0338 */ 0339 private function getAllChildComments($element) 0340 { 0341 $returnValue = array(); 0342 $level = $element[0]['level'] + 1; 0343 $childs = $this->getChildCommentsForId($element[0]['comment']['comment_id']); 0344 if (0 == count($childs)) { 0345 return null; 0346 } 0347 foreach ($childs as $child) { 0348 $resultElement = array(array('comment' => $child, 'level' => $level)); 0349 $subChilds = $this->getAllChildComments($resultElement); 0350 if (0 == count($subChilds)) { 0351 $returnValue = array_merge($returnValue, $resultElement); 0352 } else { 0353 $returnValue = array_merge($returnValue, $subChilds); 0354 } 0355 } 0356 0357 return array_merge($element, $returnValue); 0358 } 0359 0360 /** 0361 * @param int $parent_id 0362 * 0363 * @return array 0364 */ 0365 public function getChildCommentsForId($parent_id) 0366 { 0367 $sql = "SELECT * 0368 FROM comments 0369 STRAIGHT_JOIN member ON comments.comment_member_id = member.member_id 0370 WHERE comment_parent_id = :parent_id 0371 AND comment_active = :status 0372 ORDER BY comments.comment_created_at, comments.comment_id 0373 "; 0374 $rowset = $this->_dataTable->getAdapter()->fetchAll($sql, 0375 array('parent_id' => $parent_id, 'status' => Default_Model_DbTable_Comments::COMMENT_ACTIVE)) 0376 ; 0377 if (0 == count($rowset)) { 0378 return array(); 0379 } 0380 0381 return $rowset; 0382 } 0383 0384 /** 0385 * @param array $data 0386 * 0387 * @return Zend_Db_Table_Row_Abstract 0388 * @throws Exception 0389 */ 0390 public function save($data) 0391 { 0392 return $this->_dataTable->save($data); 0393 } 0394 0395 public function deactiveComment($comment_id){ 0396 $sql = ' 0397 UPDATE comments 0398 SET comment_active = 0 0399 WHERE comment_id = :comment_id'; 0400 $this->_dataTable->getAdapter()->query($sql, array('comment_id' => $comment_id))->execute(); 0401 } 0402 0403 public function setAllCommentsForUserDeleted($member_id) 0404 { 0405 0406 $sql = "SELECT comment_id FROM comments WHERE comment_member_id = :member_id AND comment_active = 1"; 0407 $commentsForDelete = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0408 'member_id' => $member_id 0409 )); 0410 foreach ($commentsForDelete as $item) { 0411 $this->setDeleted($member_id, $item['comment_id']); 0412 } 0413 0414 /* 0415 $sql = ' 0416 UPDATE comments 0417 SET comment_active = 0 0418 WHERE comment_member_id = :member_id'; 0419 $this->_dataTable->getAdapter()->query($sql, array('member_id' => $member_id))->execute(); 0420 */ 0421 } 0422 0423 public function setAllCommentsForUserActivated($member_id) 0424 { 0425 $sql = "SELECT comment_id FROM comments c 0426 JOIN member_deactivation_log l ON l.object_type_id = 4 AND l.object_id = c.comment_id AND l.deactivation_id = c.comment_member_id AND l.is_deleted = 0 0427 WHERE c.comment_member_id = :member_id AND c.comment_active = 0"; 0428 $commentsForDelete = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0429 'member_id' => $member_id 0430 )); 0431 foreach ($commentsForDelete as $item) { 0432 $this->setActive($member_id, $item['comment_id']); 0433 } 0434 /* 0435 $sql = ' 0436 UPDATE comments 0437 SET comment_active = 1 0438 WHERE comment_member_id = :member_id'; 0439 $this->_dataTable->getAdapter()->query($sql, array('member_id' => $member_id))->execute(); 0440 * 0441 */ 0442 } 0443 0444 /** 0445 * @param int $member_id 0446 * @param int $project_id 0447 * 0448 * @throws Zend_Db_Statement_Exception 0449 * @throws Zend_Exception 0450 */ 0451 public function setAllCommentsForProjectDeleted($member_id, $project_id) 0452 { 0453 $sql = 0454 "SELECT `comment_id` FROM `comments` WHERE `comment_target_id` = :project_id AND `comment_type` = 0 AND `comment_active` = 1"; 0455 $commentsForDelete = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0456 'project_id' => $project_id 0457 )) 0458 ; 0459 foreach ($commentsForDelete as $item) { 0460 $this->setDeleted($member_id, $item['comment_id']); 0461 } 0462 } 0463 0464 /** 0465 * @param int $member_id 0466 * @param int $comment_id 0467 * 0468 * @throws Zend_Db_Statement_Exception 0469 * @throws Zend_Exception 0470 */ 0471 public function setDeleted($member_id, $comment_id) 0472 { 0473 $sql = ' 0474 UPDATE `comments` 0475 SET `comment_active` = 0 0476 WHERE `comment_id` = :comment_id'; 0477 $this->_dataTable->getAdapter()->query($sql, array('comment_id' => $comment_id))->execute(); 0478 0479 $memberLog = new Default_Model_MemberDeactivationLog(); 0480 $memberLog->logCommentAsDeleted($member_id, $comment_id); 0481 } 0482 0483 public function setActive($member_id, $comment_id) 0484 { 0485 $sql = ' 0486 UPDATE comments 0487 SET comment_active = 1, comment_deleted_at = null 0488 WHERE comment_id = :comment_id'; 0489 $this->_dataTable->getAdapter()->query($sql, array('comment_id' => $comment_id))->execute(); 0490 0491 $memberLog = new Default_Model_MemberDeactivationLog(); 0492 $memberLog->removeLogCommentAsDeleted($member_id, $comment_id); 0493 } 0494 0495 /** 0496 * @param int $member_id 0497 * @param int $project_id 0498 */ 0499 public function setAllCommentsForProjectActivated($member_id, $project_id) 0500 { 0501 $sql = "SELECT `comment_id` FROM `comments` `c` 0502 JOIN `member_deactivation_log` `l` ON `l`.`object_type_id` = 4 AND `l`.`object_id` = `c`.`comment_id` AND `l`.`is_deleted` = 0 0503 WHERE `c`.`comment_target_id` = :project_id AND `l`.`deactivation_id` = :member_id AND `comment_active` = 0"; 0504 $commentsForDelete = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0505 'project_id' => $project_id, 0506 'member_id' => $member_id 0507 )) 0508 ; 0509 foreach ($commentsForDelete as $item) { 0510 $this->setActive($member_id, $item['comment_id']); 0511 } 0512 } 0513 0514 public function getCommentsHierarchic($project_id) 0515 { 0516 $rootElements = $this->getRootCommentsForProject($project_id); 0517 $returnValue = array(); 0518 foreach ($rootElements as $parentComment) { 0519 $childs = $this->getChildCommentsHierarchic($parentComment); 0520 if (0 == count($childs)) { 0521 $parentComment['childcount'] = 0; 0522 } else { 0523 $parentComment['childcount'] = count($childs); 0524 $parentComment['children'] = $childs; 0525 } 0526 $returnValue[] = $parentComment; 0527 } 0528 0529 return new Zend_Paginator(new Zend_Paginator_Adapter_Array($returnValue)); 0530 } 0531 0532 protected function getChildCommentsHierarchic($parentComment) 0533 { 0534 $childs = $this->getChildCommentsForId($parentComment['comment_id']); 0535 if (0 == count($childs)) { 0536 return array(); 0537 } 0538 $returnValue = array(); 0539 foreach ($childs as $child) { 0540 $subChilds = $this->getChildCommentsHierarchic($child); 0541 if (0 == count($subChilds)) { 0542 $child['childcount'] = 0; 0543 } else { 0544 $child['childcount'] = count($subChilds); 0545 $child['children'] = $subChilds; 0546 } 0547 $returnValue[] = $child; 0548 } 0549 0550 return $returnValue; 0551 } 0552 0553 /** 0554 * @return array 0555 */ 0556 public function fetchCommentsWithType($comment_type,$sorting='comment_created_at desc', $pageSize=10, $offset=0) 0557 { 0558 $sql="SELECT 0559 comment_id 0560 ,comment_text 0561 , member.member_id 0562 ,member.profile_image_url 0563 ,comment_created_at 0564 ,member.username 0565 ,p.title 0566 ,p.project_id 0567 ,p.image_small 0568 ,p.cat_title 0569 ,p.username as product_username 0570 FROM comments 0571 JOIN stat_projects p ON comments.comment_target_id = p.project_id 0572 join member ON comments.comment_member_id = member.member_id 0573 WHERE comments.comment_active = 1 0574 and comments.comment_type=:comment_type 0575 0576 "; 0577 $sql .= ' order by ' . $sorting; 0578 $sql .= ' limit ' . $pageSize; 0579 $sql .= ' offset ' . $offset; 0580 0581 $result = $this->_dataTable->getAdapter()->fetchAll($sql, array('comment_type' => $comment_type)); 0582 return $result; 0583 0584 } 0585 0586 public function fetchCommentsWithTypeCount($comment_type) 0587 { 0588 $sql="SELECT 0589 count(1) as cnt 0590 FROM comments 0591 JOIN project ON comments.comment_target_id = project.project_id 0592 WHERE comments.comment_active = 1 0593 AND project.status = 100 0594 and comments.comment_type=:comment_type 0595 "; 0596 $result = $this->_dataTable->getAdapter()->fetchRow($sql, array('comment_type' => $comment_type)); 0597 return $result['cnt']; 0598 } 0599 0600 public function fetchCommentsWithTypeProjectCount($comment_type,$project_id) 0601 { 0602 $sql="SELECT 0603 count(1) as cnt 0604 FROM comments 0605 JOIN project ON comments.comment_target_id = project.project_id 0606 WHERE comments.comment_active = 1 0607 AND project.status = 100 0608 And project.project_id = :project_id 0609 and comments.comment_type=:comment_type 0610 "; 0611 $result = $this->_dataTable->getAdapter()->fetchRow($sql, array('comment_type' => $comment_type,'project_id'=>$project_id)); 0612 return $result['cnt']; 0613 } 0614 }