File indexing completed on 2025-05-04 05:29:10

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_DbTable_ProjectRating extends Local_Model_Table
0024 {
0025 
0026     protected $_name = "project_rating";
0027 
0028     protected $_keyColumnsForRow = array('rating_id');
0029 
0030     protected $_key = 'rating_id';
0031 
0032 
0033     public static $options = array(1 => 'ugh', 2=>'really bad',3=>'bad',4=>'soso',5=>'average', 6=>'okay',7=>'good', 8=>'great', 9=>'excellent',10=>'the best');
0034 
0035     /**
0036      * @param int $project_id
0037      *
0038      * @return array
0039      */
0040     public function fetchRating($project_id)
0041     {
0042         $sql = "
0043                 SELECT
0044                    p.* ,
0045                    (SELECT `profile_image_url` FROM member m WHERE m.member_id = p.member_id)  AS profile_image_url,
0046                    (SELECT `username` FROM member m WHERE m.member_id = p.member_id)  AS username,
0047                    (SELECT `comment_text` FROM comments c WHERE c.comment_id = p.comment_id)  AS comment_text
0048                 FROM
0049                     project_rating p
0050                 WHERE
0051                     project_id = :project_id and rating_active = 1
0052                     ORDER BY created_at DESC
0053                 ;
0054                ";
0055         $result = $this->_db->query($sql, array('project_id' => $project_id))->fetchAll();
0056 
0057         return $result;
0058     }
0059 
0060     /**
0061      * @param int $project_id
0062      * @param int $member_id
0063      *
0064      * @return null
0065      */
0066     public function getProjectRateForUser($project_id, $member_id)
0067     {
0068         $sql = "
0069                 SELECT
0070                    p.* ,
0071                    (SELECT `comment_text` FROM comments c WHERE c.comment_id = p.comment_id)  AS comment_text
0072                 FROM
0073                     project_rating p
0074                 WHERE
0075                     project_id = :project_id
0076                     AND member_id = :member_id
0077                     AND rating_active = 1
0078                 ;
0079                ";
0080         $result = $this->_db->query($sql, array('project_id' => $project_id, 'member_id' => $member_id))->fetchAll();
0081         if (count($result) > 0) {
0082             return $result[0];
0083         } else {
0084             return null;
0085         }
0086     }
0087 
0088     /**
0089      * @param int $project_id
0090      *
0091      * @return mixed
0092      */
0093     public function fetchRatingCntActive($project_id)
0094     {
0095         $sql = "
0096                 SELECT
0097                    count(*)
0098                 FROM
0099                     project_rating p
0100                 WHERE
0101                     project_id = :project_id
0102                     AND rating_active = 1
0103                 ;
0104                ";
0105         $result = $this->_db->query($sql, array('project_id' => $project_id))->fetch();
0106 
0107         return $result;
0108     }
0109 
0110     public function getScore($project_id)
0111     {
0112           $sql = "select (sum(t.totalscore)+4*5)/(sum(t.count)+4)*100 as score
0113                     from
0114                     (
0115                         select project_id
0116                         ,user_like as likes
0117                         ,user_dislike as dislikes
0118                         ,1 as count
0119                         ,score as totalscore
0120                         from project_rating pr where pr.project_id=:project_id and pr.rating_active = 1
0121                     ) t
0122                 ";
0123 
0124         $result = $this->_db->query($sql, array('project_id' => $project_id))->fetchAll();
0125         if($result[0]['score'])
0126         {
0127             return $result[0]['score'];
0128          }else
0129          {
0130             return 500;
0131          }
0132     }
0133 
0134     // public function getScore($project_id)
0135     // {
0136     //       $sql = "select (sum(t.totalscore)+4*5)/(sum(t.count)+4)*100 as score
0137     //                 from
0138     //                 (
0139     //                     select project_id
0140     //                     ,user_like as likes
0141     //                     ,user_dislike as dislikes
0142     //                     ,1 as count
0143     //                     ,score as totalscore
0144     //                     from project_rating pr where pr.project_id=:project_id and pr.rating_active = 1
0145     //
0146     //                     union all
0147     //
0148     //                     select
0149     //                     project_id
0150     //                     ,user_like-6 as likes
0151     //                     ,user_dislike-6 as dislikes
0152     //                     ,user_like+user_dislike-12 as count
0153     //                     ,(user_like-6)*9+(user_dislike-6)*3 as totalscore
0154     //                     from project_rating pr
0155     //                     where pr.project_id=:project_id and pr.rating_active = 0 and user_dislike >=6 and user_like>=6
0156     //                 ) t
0157     //             ";
0158     //
0159     //     $result = $this->_db->query($sql, array('project_id' => $project_id))->fetchAll();
0160     //     if($result[0]['score'])
0161     //     {
0162     //         return $result[0]['score'];
0163     //      }else
0164     //      {
0165     //         return 500;
0166     //      }
0167     // }
0168 
0169     public function getScoreOld($project_id)
0170     {
0171         $sql = "
0172             SELECT laplace_score(sum(pr.user_like), sum(pr.user_dislike)) AS score
0173                 FROM project_rating AS pr
0174               WHERE pr.project_id = :project_id and (pr.rating_active = 1 or (rating_active=0 and user_like>1))
0175               ";
0176 
0177         $result = $this->_db->query($sql, array('project_id' => $project_id))->fetchAll();
0178         if($result[0]['score'])
0179         {
0180             return $result[0]['score'];
0181          }else
0182          {
0183             return 50;
0184          }
0185     }
0186 
0187     /**
0188      * @param int      $projectId
0189      * @param int      $member_id
0190      * @param int      $userRating
0191      * @param int|null $msg comment
0192      */
0193     public function rateForProject($projectId, $member_id, $userRating, $msg )
0194     {
0195         $msg = trim($msg);
0196         if(strlen($msg)<1) return;
0197         $userLikeIt = $userRating == 1 ? 1 : 0;
0198         $userDislikeIt = $userRating == 2 ? 1 : 0;
0199         $sql = 'select rating_id,comment_id from project_rating where project_id='.$projectId.'  and rating_active=1 and user_like='.$userLikeIt.' and user_dislike='.$userDislikeIt.' and member_id='.$member_id;
0200         $result = $this->getAdapter()->fetchRow($sql);
0201         $is_upvote=$userRating == 1 ? true : false;
0202         $is_exist = (($result!=null) && ($result['rating_id']!=null))?true:false;
0203         $modelComments = new Default_Model_ProjectComments();
0204 
0205         // Zend_Registry::get('logger')->info($msg);
0206         if($is_exist){
0207             // this do cancel old rating .  remove rating & deactive
0208             $rating_id = $result['rating_id'];
0209             $comment_id = $result['comment_id'];
0210             $this->update(array('rating_active' => 0), 'rating_id=' . $rating_id);
0211             $modelComments->deactiveComment($comment_id);
0212             /*if($is_upvote){
0213                $this->rateUpdateProject($projectId,1);
0214             }else{
0215                 $this->rateUpdateProject($projectId,2);
0216             }*/
0217         }else{
0218             // this do first rating or change from - to + or + to -
0219             // first comment
0220             $data = array();
0221             $data['comment_target_id'] =$projectId;
0222             $data['comment_member_id'] =$member_id;
0223             $data['comment_parent_id'] = 0;
0224             $data['comment_text'] = $msg;
0225             $tableReplies = new Default_Model_ProjectComments();
0226             $result = $tableReplies->save($data);
0227             $comment_id =  $result->comment_id;
0228 
0229             // get old rating
0230             $sql = 'select rating_id,comment_id,user_like from project_rating where project_id='.$projectId.'  and rating_active=1 and member_id='.$member_id;
0231             $result = $this->getAdapter()->fetchRow($sql);
0232             if($result!=null && $result['rating_id']!=null){
0233                  $this->update(array('rating_active' => 0), 'rating_id=' . $result['rating_id']);
0234                 $modelComments->deactiveComment($result['comment_id']);
0235             }
0236 
0237             if($userLikeIt==1)
0238             {
0239                 $score = 8;
0240             }else
0241             {
0242                 $score = 3;
0243             }
0244             $this->save(array(
0245                 'project_id'    => $projectId,
0246                 'member_id'     => $member_id,
0247                 'user_like'     => $userLikeIt,
0248                 'user_dislike'  => $userDislikeIt,
0249                 'score'         => $score,
0250                 'rating_active' => 1,
0251                 'comment_id'    => $comment_id
0252             ));
0253 
0254             // deal with project table ratings
0255             /*if(($result!=null) && ($result['rating_id']!=null)){
0256                 if($is_upvote){
0257                       $this->rateUpdateProject($projectId,5);
0258                 }else{
0259                      $this->rateUpdateProject($projectId,6);
0260                 }
0261             }else{
0262                 // first time rating
0263                 if($is_upvote){
0264                    $this->rateUpdateProject($projectId,3);
0265                 }else{
0266                     $this->rateUpdateProject($projectId,4);
0267                 }
0268             }*/
0269 
0270         }
0271 
0272 
0273     }
0274 
0275    
0276 
0277     /**
0278      * @param int      $projectId
0279      * @param int      $member_id
0280      * @param int      $userRating
0281      * @param int|null $msg comment
0282      */
0283     public function scoreForProject($projectId, $member_id, $score, $msg )
0284     {
0285         $msg = trim($msg);
0286         $score =(int)$score;
0287         if(strlen($msg)<1) return;
0288         if($score<6){
0289             $userLikeIt = 0;
0290             $userDislikeIt = 1;
0291         }else
0292         {
0293             $userLikeIt = 1;
0294             $userDislikeIt = 0;
0295         }
0296 
0297         $sql = 'select rating_id,comment_id from project_rating where project_id='.$projectId.'  and rating_active=1 and member_id='.$member_id;
0298         $result = $this->getAdapter()->fetchRow($sql);
0299 
0300         $is_upvote=$score<6 ? false : true;
0301 
0302         //$is_exist = (($result!=null) && ($result['rating_id']!=null))?true:false;
0303         $modelComments = new Default_Model_ProjectComments();
0304 
0305         if($score<=0){
0306             // this do cancel old rating .  remove rating & deactive
0307             $rating_id = $result['rating_id'];
0308             $comment_id = $result['comment_id'];
0309             $this->update(array('rating_active' => 0), 'rating_id=' . $rating_id);
0310             $modelComments->deactiveComment($comment_id);
0311             /*if($is_upvote){
0312                $this->rateUpdateProject($projectId,1);
0313             }else{
0314                 $this->rateUpdateProject($projectId,2);
0315             }*/
0316         }else{
0317             // this do first rating or change from - to + or + to -
0318             // first comment
0319             $data = array();
0320             $data['comment_target_id'] =$projectId;
0321             $data['comment_member_id'] =$member_id;
0322             $data['comment_parent_id'] = 0;
0323             $data['comment_text'] = $msg;
0324             $tableReplies = new Default_Model_ProjectComments();
0325             $result = $tableReplies->save($data);
0326             $comment_id =  $result->comment_id;
0327 
0328             // get old rating
0329             $sql = 'select rating_id,comment_id from project_rating where project_id='.$projectId.'  and rating_active=1 and member_id='.$member_id;
0330             $result = $this->getAdapter()->fetchRow($sql);
0331             if($result!=null && $result['rating_id']!=null){
0332                  $this->update(array('rating_active' => 0), 'rating_id=' . $result['rating_id']);
0333                 $modelComments->deactiveComment($result['comment_id']);
0334             }
0335 
0336 
0337             $this->save(array(
0338                 'project_id'    => $projectId,
0339                 'member_id'     => $member_id,
0340                 'user_like'     => $userLikeIt,
0341                 'user_dislike'  => $userDislikeIt,
0342                 'score'         => $score,
0343                 'rating_active' => 1,
0344                 'comment_id'    => $comment_id
0345             ));
0346 
0347             // deal with project table ratings
0348             /*if(($result!=null) && ($result['rating_id']!=null)){
0349                 if($is_upvote){
0350                       $this->rateUpdateProject($projectId,5);
0351                 }else{
0352                      $this->rateUpdateProject($projectId,6);
0353                 }
0354             }else{
0355                 // first time rating
0356                 if($is_upvote){
0357                    $this->rateUpdateProject($projectId,3);
0358                 }else{
0359                     $this->rateUpdateProject($projectId,4);
0360                 }
0361             }*/
0362 
0363         }
0364 
0365 
0366     }
0367 
0368       /**
0369      * @param int      $projectId
0370      * @param int      $member_id
0371      * @param int      $userRating
0372      * @param int|null $msg comment
0373      */
0374       /*
0375     public function rateForProject($projectId, $member_id, $userRating, $msg )
0376     {
0377         $msg = trim($msg);
0378         if(strlen($msg)<1) return;
0379         $userLikeIt = $userRating == 1 ? 1 : 0;
0380         $userDislikeIt = $userRating == 2 ? 1 : 0;
0381         $sql = 'select rating_id,comment_id from project_rating where project_id='.$projectId.'  and rating_active=1 and user_like='.$userLikeIt.' and user_dislike='.$userDislikeIt.' and member_id='.$member_id;
0382         $result = $this->getAdapter()->fetchRow($sql);
0383         $is_upvote=$userRating == 1 ? true : false;
0384         $is_exist = (($result!=null) && ($result['rating_id']!=null))?true:false;
0385         $modelComments = new Default_Model_ProjectComments();
0386 
0387         // Zend_Registry::get('logger')->info($msg);
0388         if($is_exist){
0389             // this do cancel old rating .  remove rating & deactive
0390             $rating_id = $result['rating_id'];
0391             $comment_id = $result['comment_id'];
0392             $this->update(array('rating_active' => 0), 'rating_id=' . $rating_id);
0393             $modelComments->deactiveComment($comment_id);
0394             if($is_upvote){
0395                $this->rateUpdateProject($projectId,1);
0396             }else{
0397                 $this->rateUpdateProject($projectId,2);
0398             }
0399         }else{
0400             // this do first rating or change from - to + or + to -
0401             // first comment
0402             $data = array();
0403             $data['comment_target_id'] =$projectId;
0404             $data['comment_member_id'] =$member_id;
0405             $data['comment_parent_id'] = 0;
0406             $data['comment_text'] = $msg;
0407             $tableReplies = new Default_Model_ProjectComments();
0408             $result = $tableReplies->save($data);
0409             $comment_id =  $result->comment_id;
0410 
0411             // get old rating
0412             $sql = 'select rating_id,comment_id,user_like from project_rating where project_id='.$projectId.'  and rating_active=1 and member_id='.$member_id;
0413             $result = $this->getAdapter()->fetchRow($sql);
0414             if($result!=null && $result['rating_id']!=null){
0415                  $this->update(array('rating_active' => 0), 'rating_id=' . $result['rating_id']);
0416                 $modelComments->deactiveComment($result['comment_id']);
0417             }
0418 
0419             $this->save(array(
0420                 'project_id'    => $projectId,
0421                 'member_id'     => $member_id,
0422                 'user_like'     => $userLikeIt,
0423                 'user_dislike'  => $userDislikeIt,
0424                 'rating_active' => 1,
0425                 'comment_id'    => $comment_id
0426             ));
0427 
0428             // deal with project table ratings
0429             if(($result!=null) && ($result['rating_id']!=null)){
0430                 if($is_upvote){
0431                       $this->rateUpdateProject($projectId,5);
0432                 }else{
0433                      $this->rateUpdateProject($projectId,6);
0434                 }
0435             }else{
0436                 // first time rating
0437                 if($is_upvote){
0438                    $this->rateUpdateProject($projectId,3);
0439                 }else{
0440                     $this->rateUpdateProject($projectId,4);
0441                 }
0442             }
0443 
0444         }
0445 
0446 
0447     }*/
0448 
0449     /*private function rateUpdateProject($projectId,$action)
0450     {
0451         // $action ==1 => $project->count_likes - 1
0452         // $action ==2 => $project->count_dislikes - 1
0453         // $action ==3 => $project->count_likes + 1
0454         // $action ==4 => $project->count_dislikes + 1
0455         // $action ==5 => $project->count_likes+1 and $project->count_dislikes - 1
0456         // $action ==6 => $project->count_likes-1 and $project->count_dislikes +1
0457          $projectTable = new Default_Model_Project();
0458          $project = $projectTable->fetchProductInfo($projectId);
0459          if($action==1)
0460          {
0461             $numLikes = (int)$project->count_likes - 1;
0462             $updatearray = array('count_likes' => $numLikes);
0463          }else if($action==2)
0464          {
0465             $numLikes = (int)$project->count_dislikes - 1;
0466             $updatearray = array('count_dislikes' => $numLikes);
0467          }else if($action==3)
0468          {
0469             $numLikes = (int)$project->count_likes +1;
0470             $updatearray = array('count_likes' => $numLikes);
0471          }else if($action==4)
0472          {
0473             $numLikes = (int)$project->count_dislikes +1;
0474             $updatearray = array('count_dislikes' => $numLikes);
0475          }else if($action==5)
0476          {
0477             $numdisLikes = (int)$project->count_dislikes -1;
0478             $numLikes = (int)$project->count_likes +1;
0479             $updatearray = array('count_dislikes' => $numdisLikes,'count_likes' => $numLikes);
0480          }else if($action==6)
0481          {
0482             $numdisLikes = (int)$project->count_dislikes +1;
0483             $numLikes = (int)$project->count_likes -1;
0484             $updatearray = array('count_dislikes' => $numdisLikes,'count_likes' => $numLikes);
0485          }
0486          $projectTable->update($updatearray, 'project_id = ' . $projectId);
0487     }
0488 */
0489 
0490     /**
0491      * @param int $memberId
0492      *
0493      * @return array returns array of affected rows. can be empty.
0494      */
0495     public function setDeletedByMemberId($memberId)
0496     {
0497         $sql = "
0498             UPDATE {$this->_name}
0499             SET rating_active = 0
0500             WHERE member_id = :member_id AND rating_active = 1
0501         ";
0502 
0503         $sqlAffectedRows =
0504             "SELECT rating_id, project_id, user_like, user_dislike FROM {$this->_name} WHERE member_id = :member_id AND rating_active = 1";
0505         $affectedRows = $this->_db->fetchAll($sqlAffectedRows, array('member_id' => $memberId));
0506 
0507         $result = $this->_db->query($sql, array('member_id' => $memberId))->execute();
0508         if ($result) {
0509             return $affectedRows;
0510         }
0511 
0512         return array();
0513     }
0514 
0515     public function getRatedForMember($member_id)
0516     {
0517         $sql = "
0518                      SELECT
0519                        r.user_like
0520                        ,r.user_dislike
0521                        ,r.rating_active
0522                        ,r.created_at rating_created_at
0523                        ,(select `comment_text` from comments c where c.comment_id = r.comment_id)  as comment_text
0524                        ,r.project_id
0525                        ,r.score
0526                         ,p.member_id as project_member_id
0527                         ,p.username as project_username
0528                         ,p.project_category_id
0529                         ,p.status
0530                         ,p.title
0531                         ,p.description
0532                         ,p.image_small
0533                         ,p.project_created_at
0534                         ,p.project_changed_at
0535                         ,p.laplace_score
0536                         ,p.cat_title
0537                         ,p.count_likes
0538                         ,p.count_dislikes
0539                     FROM
0540                         project_rating r
0541                     inner join stat_projects p on r.project_id = p.project_id and p.status = 100
0542                     WHERE
0543                         r.member_id = :member_id
0544                         and r.rating_active = 1
0545                     order by r.created_at desc
0546         ";
0547         $result = $this->_db->query($sql, array('member_id' => $member_id))->fetchAll();
0548         return $result;
0549     }
0550 
0551 }