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 }