File indexing completed on 2024-10-06 13:36:20
0001 <?php 0002 0003 /** 0004 * ocs-apiserver 0005 * 0006 * Copyright 2016 by pling GmbH. 0007 * 0008 * This file is part of ocs-apiserver. 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 Application_Model_ProjectComments 0024 { 0025 0026 /** @var string */ 0027 protected $_dataTableName; 0028 /** @var Application_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 = 'Application_Model_DbTable_Comments') 0050 { 0051 $this->_dataTableName = $_dataTableName; 0052 $this->_dataTable = new Application_Model_DbTable_Comments(); 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 getCommentTreeForProject($project_id) 0096 { 0097 0098 $sql = " 0099 SELECT comment_id, comment_target_id, comment_parent_id, comment_text, comment_created_at, comment_active, comment_type, member_id, username, profile_image_url 0100 ,(SELECT (DATE_ADD(max(active_time), INTERVAL 1 YEAR) > now()) from support where support.status_id = 2 AND support.member_id = comments.comment_member_id) AS issupporter 0101 FROM comments 0102 STRAIGHT_JOIN member ON comments.comment_member_id = member.member_id 0103 WHERE comment_active = :status_active AND comment_type = :type_id AND comment_target_id = :project_id AND comment_parent_id = 0 0104 ORDER BY comment_created_at DESC 0105 "; 0106 $rowset = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0107 'status_active' => 1, 0108 'type_id' => Application_Model_DbTable_Comments::COMMENT_TYPE_PRODUCT, 0109 'project_id' => $project_id 0110 )) 0111 ; 0112 $sql = " 0113 SELECT comment_id, comment_target_id, comment_parent_id, comment_text, comment_created_at, comment_active, comment_type, member_id, username, profile_image_url 0114 ,(SELECT (DATE_ADD(max(active_time), INTERVAL 1 YEAR) > now()) from support where support.status_id = 2 AND support.member_id = comments.comment_member_id) AS issupporter 0115 FROM comments 0116 STRAIGHT_JOIN member ON comments.comment_member_id = member.member_id 0117 WHERE comment_active = :status_active AND comment_type = :type_id AND comment_target_id = :project_id AND comment_parent_id <> 0 0118 ORDER BY comment_created_at, comment_id 0119 "; 0120 $rowset2 = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0121 'status_active' => 1, 0122 'type_id' => Application_Model_DbTable_Comments::COMMENT_TYPE_PRODUCT, 0123 'project_id' => $project_id 0124 )) 0125 ; 0126 $rowset = array_merge($rowset, $rowset2); 0127 0128 /* create array with comment_id as key */ 0129 foreach ($rowset as $item) { 0130 $this->data[$item['comment_id']] = $item; 0131 } 0132 /* create an array with all parent_id's and their immediate children */ 0133 foreach ($rowset as $item) { 0134 $this->index[$item['comment_parent_id']][] = $item['comment_id']; 0135 } 0136 /* create the final sorted array */ 0137 $list = array(); 0138 $this->sort_child_nodes(0, 1, $list); 0139 0140 return new Zend_Paginator(new Zend_Paginator_Adapter_Array($list)); 0141 } 0142 0143 /** 0144 * @param int $parent_id 0145 * @param int $level 0146 * @param null $result 0147 * 0148 * @return array|null 0149 */ 0150 function sort_child_nodes($parent_id, $level, &$result = null) 0151 { 0152 // array(array('comment' => $rootElement, 'level' => 1)); 0153 $parent_id = $parent_id === null ? "NULL" : $parent_id; 0154 if (isset($this->index[$parent_id])) { 0155 foreach ($this->index[$parent_id] as $id) { 0156 $result[] = array('comment' => $this->data[$id], 'level' => $level); 0157 $this->sort_child_nodes($id, $level + 1, $result); 0158 } 0159 } 0160 } 0161 0162 /** 0163 * @param int $project_id 0164 * 0165 * @return Zend_Paginator 0166 */ 0167 public function getAllCommentsForProject($project_id) 0168 { 0169 $rootElements = $this->getRootCommentsForProjectNew($project_id); 0170 $returnValue = array(); 0171 foreach ($rootElements as $rootElement) { 0172 $resultElement = array(array('comment' => $rootElement, 'level' => 1)); 0173 $childs = $this->getAllChildComments($resultElement); 0174 if (0 == count($childs)) { 0175 $returnValue = array_merge($returnValue, $resultElement); 0176 } else { 0177 $returnValue = array_merge($returnValue, $childs); 0178 } 0179 } 0180 0181 return new Zend_Paginator(new Zend_Paginator_Adapter_Array($returnValue)); 0182 } 0183 0184 /** 0185 * @param int $_projectId 0186 * 0187 * @return array 0188 */ 0189 public function getRootCommentsForProject($_projectId) 0190 { 0191 $sql = ' 0192 SELECT * 0193 FROM comments 0194 STRAIGHT_JOIN member ON comments.comment_member_id = member.member_id 0195 JOIN project_rating ON project_rating.comment_id = comments.comment_id 0196 WHERE comment_target_id = :project_id 0197 AND comment_parent_id = 0 0198 AND comment_type = :type_id 0199 AND comment_active = :status 0200 ORDER BY comments.comment_created_at DESC, comment_parent_id 0201 '; 0202 0203 $rowset = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0204 'project_id' => $_projectId, 0205 'status' => Application_Model_DbTable_Comments::COMMENT_ACTIVE, 0206 'type_id' => Application_Model_DbTable_Comments::COMMENT_TYPE_PRODUCT 0207 )) 0208 ; 0209 if (0 == count($rowset)) { 0210 return array(); 0211 } 0212 0213 return $rowset; 0214 } 0215 0216 0217 /** 0218 * @param int $_projectId 0219 * 0220 * @return array 0221 */ 0222 public function getRootCommentsForProjectNew($_projectId) 0223 { 0224 $sql = " 0225 SELECT 0226 case when r.rating_id IS NOT NULL then 0227 case 0228 when r.score = 1 then 'ugh' 0229 when r.score = 2 then 'really bad' 0230 when r.score = 3 then 'bad' 0231 when r.score = 4 then 'soso' 0232 when r.score = 5 then 'average' 0233 when r.score = 6 then 'okay' 0234 when r.score = 7 then 'good' 0235 when r.score = 8 then 'great' 0236 when r.score = 9 then 'excellent' 0237 when r.score = 10 then 'the best' 0238 end 0239 ELSE '' END AS comment_subject 0240 ,case 0241 when r.rating_id IS NOT NULL then 0242 REPLACE( 0243 REPLACE(REPLACE(REPLACE(c.comment_text,CONCAT(r.score,' '),''),'+',''),'-','') 0244 ,case 0245 when r.score = 1 then 'ugh' 0246 when r.score = 2 then 'really bad' 0247 when r.score = 3 then 'bad' 0248 when r.score = 4 then 'soso' 0249 when r.score = 5 then 'average' 0250 when r.score = 6 then 'okay' 0251 when r.score = 7 then 'good' 0252 when r.score = 8 then 'great' 0253 when r.score = 9 then 'excellent' 0254 when r.score = 10 then 'the best' 0255 END 0256 ,'' 0257 ) 0258 ELSE c.comment_text 0259 END AS comment_text_trim 0260 ,case when r.score IS NOT NULL then (r.score*10) ELSE 0 END AS comment_score 0261 ,c.comment_id, member.username,c.comment_created_at 0262 FROM comments c 0263 STRAIGHT_JOIN member ON c.comment_member_id = member.member_id 0264 LEFT JOIN project_rating r ON r.comment_id = c.comment_id AND r.rating_active = 1 0265 WHERE comment_target_id = :project_id 0266 AND comment_parent_id = 0 0267 AND comment_type = :type_id 0268 AND comment_active = :status 0269 ORDER BY comment_created_at DESC, comment_parent_id 0270 "; 0271 0272 $rowset = $this->_dataTable->getAdapter()->fetchAll($sql, array( 0273 'project_id' => $_projectId, 0274 'status' => Application_Model_DbTable_Comments::COMMENT_ACTIVE, 0275 'type_id' => Application_Model_DbTable_Comments::COMMENT_TYPE_PRODUCT 0276 )) 0277 ; 0278 if (0 == count($rowset)) { 0279 return array(); 0280 } 0281 0282 return $rowset; 0283 } 0284 0285 /** 0286 * @param array $element 0287 * 0288 * @return array 0289 */ 0290 private function getAllChildComments($element) 0291 { 0292 $returnValue = array(); 0293 $level = $element[0]['level'] + 1; 0294 $childs = $this->getChildCommentsForIdNew($element[0]['comment']['comment_id']); 0295 if (0 == count($childs)) { 0296 return null; 0297 } 0298 foreach ($childs as $child) { 0299 $resultElement = array(array('comment' => $child, 'level' => $level)); 0300 $subChilds = $this->getAllChildComments($resultElement); 0301 if (0 == count($subChilds)) { 0302 $returnValue = array_merge($returnValue, $resultElement); 0303 } else { 0304 $returnValue = array_merge($returnValue, $subChilds); 0305 } 0306 } 0307 0308 return array_merge($element, $returnValue); 0309 } 0310 0311 /** 0312 * @param int $parent_id 0313 * 0314 * @return array 0315 */ 0316 public function getChildCommentsForId($parent_id) 0317 { 0318 $sql = "SELECT * 0319 FROM comments 0320 STRAIGHT_JOIN member ON comments.comment_member_id = member.member_id 0321 WHERE comment_parent_id = :parent_id 0322 AND comment_active = :status 0323 ORDER BY comments.comment_created_at, comments.comment_id 0324 "; 0325 $rowset = $this->_dataTable->getAdapter()->fetchAll($sql, 0326 array('parent_id' => $parent_id, 'status' => Application_Model_DbTable_Comments::COMMENT_ACTIVE)) 0327 ; 0328 if (0 == count($rowset)) { 0329 return array(); 0330 } 0331 0332 return $rowset; 0333 } 0334 0335 /** 0336 * @param int $parent_id 0337 * 0338 * @return array 0339 */ 0340 public function getChildCommentsForIdNew($parent_id) 0341 { 0342 $sql = "SELECT '' AS comment_subject,comments.comment_text AS comment_text_trim, 0 as comment_score, comments.*, member.* 0343 FROM comments 0344 STRAIGHT_JOIN member ON comments.comment_member_id = member.member_id 0345 WHERE comment_parent_id = :parent_id 0346 AND comment_active = :status 0347 ORDER BY comments.comment_created_at, comments.comment_id 0348 "; 0349 $rowset = $this->_dataTable->getAdapter()->fetchAll($sql, 0350 array('parent_id' => $parent_id, 'status' => Application_Model_DbTable_Comments::COMMENT_ACTIVE)) 0351 ; 0352 if (0 == count($rowset)) { 0353 return array(); 0354 } 0355 0356 return $rowset; 0357 } 0358 0359 /** 0360 * @param array $data 0361 * 0362 * @return Zend_Db_Table_Row_Abstract 0363 * @throws Exception 0364 */ 0365 public function save($data) 0366 { 0367 $writer = new Application_Model_DbTable_Comments(array('db' => 'db2')); 0368 return $writer->save($data); 0369 } 0370 0371 public function setAllCommentsForUserDeleted($member_id) 0372 { 0373 $sql = ' 0374 UPDATE comments 0375 SET comment_active = 0 0376 WHERE comment_member_id = :member_id'; 0377 $this->_dataTable->getAdapter()->query($sql, array('member_id' => $member_id))->execute(); 0378 } 0379 0380 public function setAllCommentsForUserActivated($member_id) 0381 { 0382 $sql = ' 0383 UPDATE comments 0384 SET comment_active = 1 0385 WHERE comment_member_id = :member_id'; 0386 $this->_dataTable->getAdapter()->query($sql, array('member_id' => $member_id))->execute(); 0387 } 0388 0389 public function setAllCommentsForProjectDeleted($project_id) 0390 { 0391 $sql = ' 0392 UPDATE comments 0393 SET comment_active = 0 0394 WHERE comment_target_id = :projectId'; 0395 $this->_dataTable->getAdapter()->query($sql, array('projectId' => $project_id))->execute(); 0396 } 0397 0398 public function setAllCommentsForProjectActivated($project_id) 0399 { 0400 $sql = ' 0401 UPDATE comments 0402 SET comment_active = 1 0403 WHERE comment_target_id = :projectId'; 0404 $this->_dataTable->getAdapter()->query($sql, array('projectId' => $project_id))->execute(); 0405 } 0406 0407 public function getCommentsHierarchic($project_id) 0408 { 0409 $rootElements = $this->getRootCommentsForProjectNew($project_id); 0410 $returnValue = array(); 0411 foreach ($rootElements as $parentComment) { 0412 $childs = $this->getChildCommentsHierarchic($parentComment); 0413 if (0 == count($childs)) { 0414 $parentComment['childcount'] = 0; 0415 } else { 0416 $parentComment['childcount'] = count($childs); 0417 $parentComment['children'] = $childs; 0418 } 0419 $returnValue[] = $parentComment; 0420 } 0421 0422 return new Zend_Paginator(new Zend_Paginator_Adapter_Array($returnValue)); 0423 } 0424 0425 protected function getChildCommentsHierarchic($parentComment) 0426 { 0427 $childs = $this->getChildCommentsForIdNew($parentComment['comment_id']); 0428 if (0 == count($childs)) { 0429 return array(); 0430 } 0431 $returnValue = array(); 0432 foreach ($childs as $child) { 0433 $subChilds = $this->getChildCommentsHierarchic($child); 0434 if (0 == count($subChilds)) { 0435 $child['childcount'] = 0; 0436 } else { 0437 $child['childcount'] = count($subChilds); 0438 $child['children'] = $subChilds; 0439 } 0440 $returnValue[] = $child; 0441 } 0442 0443 return $returnValue; 0444 } 0445 0446 0447 public function deactiveComment($comment_id){ 0448 $sql = ' 0449 UPDATE comments 0450 SET comment_active = 0 0451 WHERE comment_id = :comment_id'; 0452 $writer = new Application_Model_DbTable_Comments(array('db' => 'db2')); 0453 $writer->getAdapter()->query($sql, array('comment_id' => $comment_id))->execute(); 0454 } 0455 }