File indexing completed on 2024-03-24 05:57:22

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 
0024 //use YoHang88\LetterAvatar\LetterAvatar;
0025 
0026 class Application_Model_Member extends Application_Model_DbTable_Member
0027 {
0028     const CASE_INSENSITIVE = 1;
0029 
0030     public static function cleanAuthMemberForJson(array $authMember)
0031     {
0032         if (empty($authMember)) {
0033             return $authMember;
0034         }
0035 
0036         $unwantedKeys = array(
0037             'mail'              => 0,
0038             'firstname'         => 0,
0039             'lastname'          => 0,
0040             'street'            => 0,
0041             'zip'               => 0,
0042             'phone'             => 0,
0043             'paypal_mail'       => 0,
0044             'gravatar_email'    => 0,
0045             'source_pk'         => 0,
0046             'source_id'         => 0,
0047             'password_old'      => 0,
0048             'password_type_old' => 0,
0049             'username_old'      => 0,
0050             'mail_old' => 0
0051         );
0052 
0053         $authMember = array_diff_key($authMember, $unwantedKeys);
0054 
0055         return $authMember;
0056     }
0057 
0058     /**
0059      * @param int    $count
0060      * @param string $orderBy
0061      * @param string $dir
0062      *
0063      * @return Zend_Db_Table_Rowset
0064      * @throws Zend_Exception
0065      */
0066     public function fetchNewActiveMembers($count = 100, $orderBy = 'created_at', $dir = 'DESC')
0067     {
0068         if (empty($count)) {
0069             return $this->generateRowSet($this->createRow());
0070         }
0071 
0072         $allowedDirection = array('desc' => true, 'asc' => true);
0073         if (false == isset($allowedDirection[strtolower($dir)])) {
0074             $dir = null;
0075         }
0076 
0077         /** @var Zend_Cache_Core $cache */
0078         $cache = Zend_Registry::get('cache');
0079         $cacheName = __FUNCTION__ . md5($count . $orderBy . $dir);
0080         $members = $cache->load($cacheName);
0081 
0082         if ($members) {
0083             return $members;
0084         } else {
0085 
0086             $sql = '
0087               SELECT count(*) AS `total_count`
0088               FROM `member`
0089               WHERE `is_active` = :activeVal
0090                  AND `type` = :typeVal
0091                AND `profile_image_url` <> :defaultImgUrl
0092                AND `profile_image_url` LIKE :likeImgUrl
0093           ';
0094 
0095             $resultCnt = $this->_db->fetchRow($sql, array(
0096                 'activeVal'     => Application_Model_Member::MEMBER_ACTIVE,
0097                 'typeVal'       => Application_Model_Member::MEMBER_TYPE_PERSON,
0098                 'defaultImgUrl' => 'hive/user-pics/nopic.png',
0099                 'likeImgUrl'    => 'hive/user-bigpics/0/%'
0100             ));
0101 
0102             $totalcnt = $resultCnt['total_count'];
0103 
0104             if ($totalcnt > $count) {
0105                 $offset = ' offset ' . rand(0, $totalcnt - $count);
0106             } else {
0107                 $offset = '';
0108             }
0109 
0110             $sql = '
0111                 SELECT *
0112                 FROM `member`
0113                 WHERE `is_active` = :activeVal
0114                    AND `type` = :typeVal
0115                  AND `profile_image_url` <> :defaultImgUrl
0116                  AND `profile_image_url` LIKE :likeImgUrl
0117             ';
0118             //$sql .= ' ORDER BY ' . $this->_db->quoteIdentifier($orderBy) . ' ' . $dir;
0119 
0120             $sql .= ' LIMIT ' . $this->_db->quote($count, Zend_Db::INT_TYPE);
0121             $sql .= $offset;
0122 
0123             $resultMembers = $this->getAdapter()->query($sql, array(
0124                 'activeVal'     => Application_Model_Member::MEMBER_ACTIVE,
0125                 'typeVal'       => Application_Model_Member::MEMBER_TYPE_PERSON,
0126                 'defaultImgUrl' => 'hive/user-pics/nopic.png',
0127                 'likeImgUrl'    => 'hive/user-bigpics/0/%'
0128             ))->fetchAll();
0129 
0130             $resultSet = $this->generateRowSet($resultMembers);
0131 
0132             $cache->save($resultSet, $cacheName, array(), 14400);
0133 
0134             return $resultSet;
0135         }
0136     }
0137 
0138     /**
0139      * @param $data
0140      *
0141      * @return Zend_Db_Table_Rowset
0142      */
0143     protected function generateRowSet($data)
0144     {
0145         $classRowSet = $this->getRowsetClass();
0146 
0147         $returnRowSet = new $classRowSet(array(
0148             'table'    => $this,
0149             'rowClass' => $this->getRowClass(),
0150             'stored'   => true,
0151             'data'     => $data
0152         ));
0153 
0154         return $returnRowSet;
0155     }
0156 
0157     /**
0158      * @return array
0159      * @deprecated
0160      */
0161     public function getMembersForSelectList()
0162     {
0163         $selectArr =
0164             $this->_db->fetchAll("SELECT `member_id`,`username`,`firstname`, `lastname` FROM {$this->_name} WHERE is_active=1 AND is_deleted=0 ORDER BY username");
0165 
0166         $arrayModified = array();
0167 
0168         $arrayModified[0] = "Benutzer wählen";
0169         foreach ($selectArr as $item) {
0170             $tmpStr = ($item['firstname']) ? $item['firstname'] : "";
0171             $tmpStr .= ($item['lastname']) ? ", " . $item['lastname'] : "";
0172             $tmpStr = ($tmpStr != "") ? " (" . $tmpStr . ")" : "";
0173 
0174             $arrayModified[$item['member_id']] = stripslashes($item['username'] . $tmpStr);
0175         }
0176 
0177         return $arrayModified;
0178     }
0179 
0180     /**
0181      * @param int $member_id
0182      *
0183      * @param     $verification_value
0184      *
0185      * @return boolean returns true if successful
0186      * @throws Zend_Db_Statement_Exception
0187      */
0188     public function activateMemberFromVerification($member_id, $verification_value)
0189     {
0190         $sql = "
0191             UPDATE `member`
0192               STRAIGHT_JOIN `member_email` ON `member`.`member_id` = `member_email`.`email_member_id` AND `member_email`.`email_checked` IS NULL AND `member`.`is_deleted` = 0 AND `member_email`.`email_deleted` = 0
0193             SET `member`.`mail_checked` = 1, `member`.`is_active` = 1, `member`.`changed_at` = NOW(), `member_email`.`email_checked` = NOW()
0194             WHERE `member`.`member_id` = :memberId AND `member_email`.`email_verification_value` = :verificationValue;
0195         ";
0196         $stmnt = $this->_db->query($sql, array('memberId' => $member_id, 'verificationValue' => $verification_value));
0197 
0198         return $stmnt->rowCount() > 0 ? true : false;
0199     }
0200 
0201     //User ist mind. 1 jahr alt, user ist supporter, user hat minimum 20 kommentare
0202 
0203     /**
0204      * @param int $member_id
0205      *
0206      * @throws Zend_Exception
0207      */
0208     public function setDeleted($member_id)
0209     {
0210         $updateValues = array(
0211             'is_active'  => 0,
0212             'is_deleted' => 1,
0213             'deleted_at' => new Zend_Db_Expr('Now()'),
0214         );
0215         $this->update($updateValues, $this->_db->quoteInto('member_id=?', $member_id, 'INTEGER'));
0216 
0217         $memberLog = new Application_Model_MemberDeactivationLog();
0218         $memberLog->logMemberAsDeleted($member_id);
0219 
0220 
0221         $this->setMemberProjectsDeleted($member_id);
0222         $this->setMemberCommentsDeleted($member_id);
0223         $this->setMemberRatingsDeleted($member_id);
0224         $this->setMemberReportingsDeleted($member_id);
0225         $this->setMemberEmailsDeleted($member_id);
0226         //$this->setMemberPlingsDeleted($member_id);
0227         //$this->removeMemberProjectsFromSearch($member_id);
0228         $this->setDeletedInMaterializedView($member_id);
0229         $this->setDeletedInSubSystems($member_id);
0230     }
0231 
0232     private function setMemberProjectsDeleted($member_id)
0233     {
0234         $modelProject = new Application_Model_Project();
0235         $modelProject->setAllProjectsForMemberDeleted($member_id);
0236     }
0237 
0238     private function setMemberCommentsDeleted($member_id)
0239     {
0240         $modelComments = new Application_Model_ProjectComments();
0241         $modelComments->setAllCommentsForUserDeleted($member_id);
0242     }
0243 
0244     private function setMemberRatingsDeleted($member_id)
0245     {
0246         $modelRatings = new Application_Model_DbTable_ProjectRating();
0247         $affectedRows = $modelRatings->setDeletedByMemberId($member_id);
0248         if (false === empty($affectedRows)) {
0249             $modelProject = new Application_Model_DbTable_Project();
0250             $modelProject->deleteLikes($affectedRows);
0251         }
0252     }
0253 
0254     private function setMemberReportingsDeleted($member_id)
0255     {
0256         $modelReportsProject = new Application_Model_DbTable_ReportProducts();
0257         $modelReportsProject->setDeleteByMember($member_id);
0258         $modelReportsComments = new Application_Model_DbTable_ReportComments();
0259         $modelReportsComments->setDeleteByMember($member_id);
0260     }
0261 
0262     private function setMemberEmailsDeleted($member_id)
0263     {
0264         $modelEmail = new Application_Model_DbTable_MemberEmail();
0265         $modelEmail->setDeletedByMember($member_id);
0266     }
0267 
0268     private function setDeletedInMaterializedView($member_id)
0269     {
0270         $sql = "UPDATE `stat_projects` SET `status` = :new_status WHERE `member_id` = :member_id";
0271 
0272         $this->_db->query($sql,
0273             array('new_status' => Application_Model_DbTable_Project::PROJECT_DELETED, 'member_id' => $member_id))
0274                   ->execute();
0275     }
0276 
0277     private function setDeletedInSubSystems($member_id)
0278     {
0279 
0280     }
0281 
0282     public function validDeleteMemberFromSpam($member_id)
0283     {
0284         $sql = 'SELECT 
0285               `m`.`created_at`
0286               , (`m`.`created_at`+ INTERVAL 12 MONTH < NOW()) `is_old`
0287               ,(SELECT count(1) FROM `comments` `c` WHERE `c`.`comment_member_id` = `m`.`member_id` AND `comment_active` = 1) `comments`
0288               ,(SELECT (DATE_ADD(max(`active_time`), INTERVAL 1 YEAR) > now()) FROM `support` `s`  WHERE `s`.`status_id` = 2  AND `s`.`member_id` =`m`.`member_id`) `is_supporter`
0289               FROM `member` `m` WHERE `member_id` = :member_id';
0290         $result = $this->_db->fetchRow($sql, array(
0291             'member_id' => $member_id,
0292         ));
0293         if ($result['is_supporter'] && $result['is_supporter'] == 1) {
0294             return false;
0295         }
0296         if ($result['is_old'] == 1 || $result['comments'] > 20) {
0297             return false;
0298         }
0299 
0300         return true;
0301     }
0302 
0303     /**
0304      * @param int $member_id
0305      *
0306      * @throws Zend_Db_Exception
0307      */
0308     public function setActivated($member_id)
0309     {
0310         $updateValues = array(
0311             'is_active'  => 1,
0312             'is_deleted' => 0,
0313             'changed_at' => new Zend_Db_Expr('Now()'),
0314             'deleted_at' => null
0315         );
0316 
0317         $this->update($updateValues, $this->_db->quoteInto('member_id=?', $member_id, 'INTEGER'));
0318 
0319         $memberLog = new Application_Model_MemberDeactivationLog();
0320         $memberLog->removeLogMemberAsDeleted($member_id);
0321 
0322         $this->setMemberProjectsActivated($member_id);
0323         $this->setMemberCommentsActivated($member_id);
0324         $this->setMemberEmailsActivated($member_id);
0325 
0326         $this->setActivatedInSubsystems($member_id);
0327 
0328         //$this->setMemberPlingsActivated($member_id);
0329     }
0330 
0331     private function setMemberProjectsActivated($member_id)
0332     {
0333         $modelProject = new Application_Model_Project();
0334         $modelProject->setAllProjectsForMemberActivated($member_id);
0335     }
0336 
0337     private function setMemberCommentsActivated($member_id)
0338     {
0339         $modelComment = new Application_Model_ProjectComments();
0340         $modelComment->setAllCommentsForUserActivated($member_id);
0341     }
0342 
0343     private function setMemberEmailsActivated($member_id)
0344     {
0345         $modelEmail = new Application_Model_DbTable_MemberEmail();
0346         $modelEmail->setActivatedByMember($member_id);
0347     }
0348 
0349     private function setActivatedInSubsystems($member_id)
0350     {
0351 
0352     }
0353 
0354     /**
0355      * @param int  $member_id
0356      *
0357      * @param bool $onlyNotDeleted
0358      *
0359      * @return Zend_Db_Table_Row
0360      * @throws Zend_Db_Statement_Exception
0361      */
0362     public function fetchMemberData($member_id, $onlyNotDeleted = true)
0363     {
0364         if (null === $member_id) {
0365             return null;
0366         }
0367 
0368         $sql = '
0369                 SELECT `m`.*, `member_email`.`email_address` AS `mail`, IF(ISNULL(`member_email`.`email_checked`),0,1) AS `mail_checked`, `member_email`.`email_address`, `mei`.`external_id`, `mei`.`gitlab_user_id`
0370                 FROM `member` AS `m`
0371                 JOIN `member_email` ON `m`.`member_id` = `member_email`.`email_member_id` AND `member_email`.`email_primary` = 1
0372                 LEFT JOIN `member_external_id` AS `mei` ON `mei`.`member_id` = `m`.`member_id`
0373                 WHERE
0374                     (`m`.`member_id` = :memberId)
0375         ';
0376 
0377         if ($onlyNotDeleted) {
0378             $sql .= " AND (m.is_deleted = " . self::MEMBER_NOT_DELETED . ")";
0379         }
0380 
0381         $result = $this->getAdapter()->query($sql, array('memberId' => $member_id))->fetch();
0382 
0383         $classRow = $this->getRowClass();
0384 
0385         return new $classRow(array('table' => $this, 'stored' => true, 'data' => $result));
0386     }
0387 
0388     /**
0389      * @param int  $member_id
0390      *
0391      * @param bool $onlyNotDeleted
0392      *
0393      * @return Zend_Db_Table_Row
0394      * @throws Zend_Db_Statement_Exception
0395      */
0396     public function fetchMemberDataByExternalId($external_id, $onlyNotDeleted = true)
0397     {
0398         if (null === $external_id) {
0399             return null;
0400         }
0401 
0402         $sql = '
0403                 SELECT `m`.*, `member_email`.`email_address` AS `mail`, IF(ISNULL(`member_email`.`email_checked`),0,1) AS `mail_checked`, `member_email`.`email_address`, `mei`.`external_id`, `mei`.`gitlab_user_id`
0404                 FROM `member` AS `m`
0405                 JOIN `member_email` ON `m`.`member_id` = `member_email`.`email_member_id` AND `member_email`.`email_primary` = 1
0406                 JOIN `member_external_id` AS `mei` ON `mei`.`member_id` = `m`.`member_id`
0407                 WHERE
0408                     (`mei`.`external_id` = :externalId)
0409         ';
0410 
0411         if ($onlyNotDeleted) {
0412             $sql .= " AND (m.is_deleted = " . self::MEMBER_NOT_DELETED . ")";
0413         }
0414 
0415         $result = $this->getAdapter()->query($sql, array('externalId' => $external_id))->fetch();
0416 
0417         $classRow = $this->getRowClass();
0418 
0419         return new $classRow(array('table' => $this, 'stored' => true, 'data' => $result));
0420     }
0421 
0422     /**
0423      * @param      $member_id
0424      * @param bool $onlyActive
0425      *
0426      * @return null|Zend_Db_Table_Row_Abstract
0427      * @throws Zend_Db_Statement_Exception
0428      */
0429     public function fetchMember($member_id, $onlyActive = true)
0430     {
0431         if (empty($member_id)) {
0432             return null;
0433         }
0434 
0435         $sql = "
0436                 SELECT `m`.*, `member_email`.`email_address` AS `mail`, IF(ISNULL(`member_email`.`email_checked`),0,1) AS `mail_checked`, `member_email`.`email_address`, `mei`.`external_id`
0437                 FROM `member` AS `m`
0438                 JOIN `member_email` ON `m`.`member_id` = `member_email`.`email_member_id` AND `member_email`.`email_primary` = 1
0439                 LEFT JOIN `member_external_id` AS `mei` ON `mei`.`member_id` = `m`.`member_id`
0440                 WHERE `m`.`member_id` = :memberId";
0441 
0442         if ($onlyActive) {
0443             $sql .= " AND `m`.`is_deleted` = " . self::MEMBER_NOT_DELETED . " AND `m`.`is_active` = " . self::MEMBER_ACTIVE;
0444         }
0445 
0446         $stmnt = $this->_db->query($sql, array('memberId' => $member_id));
0447 
0448         if ($stmnt->rowCount() == 0) {
0449             return null;
0450         }
0451 
0452         return $this->generateRowClass($stmnt->fetch());
0453     }
0454 
0455     /**
0456      * @param string $user_name
0457      *
0458      * @return Zend_Db_Table_Row
0459      */
0460     public function fetchMemberFromHiveUserName($user_name)
0461     {
0462         $sql = "
0463                 SELECT *
0464                 FROM `member`
0465             WHERE `source_id` = :sourceId
0466                   AND `username` = :userName
0467                 ";
0468 
0469         return $this->_db->fetchRow($sql,
0470             array('sourceId' => Application_Model_Member::SOURCE_HIVE, 'userName' => $user_name));
0471     }
0472 
0473     /**
0474      * @param string $user_name
0475      *
0476      * @return Zend_Db_Table_Row
0477      */
0478     public function fetchMemberFromHiveUserId($user_id)
0479     {
0480         $sql = "
0481                 SELECT *
0482                 FROM `member`
0483           WHERE `source_id` = :sourceId
0484                 AND `source_pk` = :userId
0485                 ";
0486 
0487         return $this->_db->fetchRow($sql,
0488             array('sourceId' => Application_Model_Member::SOURCE_HIVE, 'userId' => $user_id));
0489     }
0490 
0491     /**
0492      * @param int $member_id
0493      * @param int $limit
0494      *
0495      * @return Zend_Db_Table_Rowset
0496      */
0497     public function fetchFollowedMembers($member_id, $limit = null)
0498     {
0499         $sql = "
0500                 SELECT `member_follower`.`member_id`,
0501                        `member_follower`.`follower_id`,
0502                        `member`.*
0503                 FROM `member_follower`
0504                 LEFT JOIN `member` ON `member_follower`.`member_id` = `member`.`member_id`
0505             WHERE `member_follower`.`follower_id` = :followerId
0506                   AND `member`.`is_active` = :activeVal
0507                 GROUP BY `member_follower`.`member_id`
0508                 ORDER BY max(`member_follower`.`member_follower_id`) DESC
0509                 ";
0510 
0511         if (null != $limit) {
0512             $sql .= $this->_db->quoteInto(" limit ?", $limit, 'INTEGER');
0513         }
0514 
0515         $result = $this->_db->fetchAll($sql, array('followerId' => $member_id, 'activeVal' => self::MEMBER_ACTIVE));
0516 
0517         return $this->generateRowSet($result);
0518     }
0519 
0520     /**
0521      * @param int  $member_id
0522      * @param null $limit
0523      *
0524      * @return Zend_Db_Table_Rowset_Abstract
0525      */
0526     public function fetchFollowedProjects($member_id, $limit = null)
0527     {
0528         $sql = "
0529                 SELECT `project_follower`.`project_id`,
0530                         `project`.`title`,
0531                         `project`.`image_small`                                              
0532                 FROM `project_follower`
0533                   JOIN `project` ON `project_follower`.`project_id` = `project`.`project_id`                 
0534                   WHERE `project_follower`.`member_id` = :member_id
0535                   AND `project`.`status` = :project_status
0536                   AND `project`.`type_id` = 1               
0537                 ORDER BY `project_follower`.`project_follower_id` DESC
0538                 ";
0539 
0540         if (null != $limit) {
0541             $sql .= $this->_db->quoteInto(" limit ?", $limit, 'INTEGER');
0542         }
0543 
0544         $result =
0545             $this->_db->fetchAll($sql,
0546                 array('member_id' => $member_id, 'project_status' => Application_Model_Project::PROJECT_ACTIVE));
0547 
0548         return $this->generateRowSet($result);
0549     }
0550 
0551     public function fetchPlingedProjects($member_id, $limit = null)
0552     {
0553         $sql = "
0554                 SELECT project_category.title AS catTitle,
0555                        project.*,
0556                  member.*,
0557                plings.*
0558                 FROM plings
0559                 LEFT JOIN project ON plings.project_id = project.project_id
0560                 LEFT JOIN project_category ON project.project_category_id = project_category.project_category_id
0561             LEFT JOIN member ON project.member_id = member.member_id
0562             WHERE plings.member_id = :member_id
0563             AND plings.status_id = 2
0564                   AND project.status = :project_status
0565                   AND project.type_id = 1
0566                 ORDER BY plings.create_time DESC
0567                 ";
0568 
0569         if (null != $limit) {
0570             $sql .= $this->_db->quoteInto(" limit ?", $limit, 'INTEGER');
0571         }
0572 
0573         $result =
0574             $this->_db->fetchAll($sql,
0575                 array('member_id' => $member_id, 'project_status' => Application_Model_Project::PROJECT_ACTIVE));
0576 
0577         return $this->generateRowSet($result);
0578     }
0579 
0580     public function fetchProjectsSupported($member_id, $limit = null)
0581     {
0582         $sql = "
0583                 SELECT `project_category`.`title` AS `catTitle`,
0584                        `project`.`project_id`,
0585                        `project`.`title`,
0586                        `project`.`image_small`,
0587                        `plings`.`member_id`,
0588                        `plings`.`amount`,
0589                        `plings`.`create_time`,
0590                        `member`.`profile_image_url`,
0591                        `member`.`username`
0592 
0593                 FROM `plings`
0594                 JOIN `project` ON `plings`.`project_id` = `project`.`project_id`
0595                 JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id`
0596                 JOIN `member` ON `plings`.`member_id` = `member`.`member_id`
0597                 WHERE `project`.`member_id` = :member_id
0598                   AND `plings`.`status_id` = 2
0599                   AND `project`.`status` = :project_status
0600                   AND `project`.`type_id` = 1
0601                 ORDER BY `plings`.`create_time` DESC
0602                 ";
0603 
0604         if (null != $limit) {
0605             $sql .= $this->_db->quoteInto(" limit ?", $limit, 'INTEGER');
0606         }
0607 
0608         $result =
0609             $this->_db->fetchAll($sql,
0610                 array('member_id' => $member_id, 'project_status' => Application_Model_Project::PROJECT_ACTIVE));
0611 
0612         return $this->generateRowSet($result);
0613     }
0614 
0615     /**
0616      * @param $userData
0617      *
0618      * @return array
0619      * @throws Exception
0620      */
0621     public function createNewUser($userData)
0622     {
0623         $uuidMember = Local_Tools_UUID::generateUUID();
0624 
0625         if (false == isset($userData['password'])) {
0626             throw new Exception(__METHOD__ . ' - user password is not set.');
0627         }
0628         $userData['password'] = Local_Auth_Adapter_Ocs::getEncryptedPassword($userData['password'],
0629             Application_Model_DbTable_Member::SOURCE_LOCAL);
0630         if (false == isset($userData['roleId'])) {
0631             $userData['roleId'] = self::ROLE_ID_DEFAULT;
0632         }
0633         if ((false == isset($userData['avatar'])) OR (false == isset($userData['profile_image_url']))) {
0634             $imageFilename = $this->generateIdentIcon($userData, $uuidMember);
0635             $userData['avatar'] = $imageFilename;
0636             $userData['profile_image_url'] = IMAGES_MEDIA_SERVER . '/cache/200x200-2/img/' . $imageFilename;
0637         }
0638         if (false == isset($userData['uuid'])) {
0639             $userData['uuid'] = $uuidMember;
0640         }
0641         if (false == isset($userData['mail_checked'])) {
0642             $userData['mail_checked'] = 0;
0643         }
0644 
0645         //email is allways lower case
0646         $userData['mail'] = strtolower(trim($userData['mail']));
0647 
0648         $newUser = $this->storeNewUser($userData)->toArray();
0649 
0650         $memberMail = $this->createPrimaryMailAddress($newUser);
0651         $externalId = $this->createExternalId($newUser['member_id']);
0652 
0653         $newUser['verificationVal'] = $memberMail->email_verification_value;
0654         $newUser['externalId'] = $externalId;
0655 
0656         return $newUser;
0657     }
0658 
0659     /**
0660      * @param $userData
0661      * @param $uuidMember
0662      *
0663      * @return string
0664      * @throws Exception
0665      */
0666     protected function generateIdentIcon($userData, $uuidMember)
0667     {
0668         /*require_once 'vendor/autoload.php';
0669         // $name = substr($userData['username'],0,1).' '.substr($userData['username'],1);
0670         $name = $userData['username'] . '  ';
0671         $avatar = new LetterAvatar($name, 'square', 400);
0672         $tmpImagePath = IMAGES_UPLOAD_PATH . 'tmp/' . $uuidMember . '.png';
0673         $avatar->saveAs($tmpImagePath, LetterAvatar::MIME_TYPE_PNG);
0674         $imageService = new Application_Model_DbTable_Image();
0675         $imageFilename = $imageService->saveImageOnMediaServer($tmpImagePath);
0676 
0677         return $imageFilename;*/
0678         return null;
0679     }
0680 
0681     /**
0682      * @param array $userData
0683      *
0684      * @return Zend_Db_Table_Row_Abstract
0685      */
0686     public function storeNewUser($userData)
0687     {
0688         $newUserData = $this->createRow($userData);
0689         $newUserData->save();
0690 
0691         //create a user specified main project in project table
0692         $projectId = $this->createPersonalProject($newUserData->toArray());
0693 
0694         //and save the id in member table
0695         $newUserData->main_project_id = $projectId;
0696         $newUserData->save();
0697 
0698         return $newUserData;
0699     }
0700 
0701     /**
0702      * @param array $userData
0703      *
0704      * @return mixed $projectId
0705      */
0706     protected function createPersonalProject($userData)
0707     {
0708         $tableProject = new Application_Model_Project();
0709         /** @var Application_Model_DbRow_Project $newPersonalProject */
0710         $newPersonalProject = $tableProject->createRow($userData);
0711         $newPersonalProject->uuid = Local_Tools_UUID::generateUUID();
0712         $newPersonalProject->project_category_id = $newPersonalProject::CATEGORY_DEFAULT_PROJECT;
0713         $newPersonalProject->status = $newPersonalProject::STATUS_PROJECT_ACTIVE;
0714         $newPersonalProject->image_big = $newPersonalProject::DEFAULT_AVATAR_IMAGE;
0715         $newPersonalProject->image_small = $newPersonalProject::DEFAULT_AVATAR_IMAGE;
0716         $newPersonalProject->creator_id = $userData['member_id'];
0717         $newPersonalProject->title = $newPersonalProject::PERSONAL_PROJECT_TITLE;
0718         $projectId = $newPersonalProject->save();
0719 
0720         return $projectId;
0721     }
0722 
0723     /**
0724      * @param array $newUser
0725      * @return Zend_Db_Table_Row_Abstract
0726      * @throws Zend_Db_Statement_Exception
0727      * @throws Zend_Db_Table_Exception
0728      * @throws Zend_Exception
0729      */
0730     private function createPrimaryMailAddress($newUser)
0731     {
0732         $modelEmail = new Application_Model_MemberEmail();
0733         $userMail = $modelEmail->saveEmailAsPrimary($newUser['member_id'], $newUser['mail'], $newUser['mail_checked']);
0734 
0735         return $userMail;
0736     }
0737 
0738     /**
0739      * @param int $member_id
0740      *
0741      * @return string
0742      */
0743     private function createExternalId($member_id)
0744     {
0745         $modelExternalId = new Application_Model_DbTable_MemberExternalId();
0746         $externalId = $modelExternalId->createExternalId($member_id);
0747 
0748         return $externalId;
0749     }
0750 
0751     public function fetchTotalMembersCount()
0752     {
0753         $sql = "
0754                 SELECT
0755                     count(1) AS `total_member_count`
0756                 FROM
0757                     `member`
0758                ";
0759 
0760         $result = $this->_db->fetchRow($sql);
0761 
0762         return $result['total_member_count'];
0763     }
0764 
0765     public function fetchTotalMembersInStoreCount()
0766     {
0767         $sql = "
0768                 SELECT
0769                     count(1) AS `total_member_count`
0770                 FROM
0771                     `member`
0772                ";
0773 
0774         $result = $this->_db->fetchRow($sql);
0775 
0776         return $result['total_member_count'];
0777     }
0778 
0779     /**
0780      * @param string $email
0781      *
0782      * @return null|Zend_Db_Table_Row_Abstract
0783      * @deprecated
0784      */
0785     public function fetchCheckedActiveLocalMemberByEmail($email)
0786     {
0787         $sel = $this->select()->where('mail=?', $email)->where('is_deleted = ?',
0788             Application_Model_DbTable_Member::MEMBER_NOT_DELETED)
0789                     ->where('is_active = ?', Application_Model_DbTable_Member::MEMBER_ACTIVE)
0790                     ->where('mail_checked = ?', Application_Model_DbTable_Member::MEMBER_MAIL_CHECKED)
0791                     ->where('login_method = ?', Application_Model_DbTable_Member::MEMBER_LOGIN_LOCAL);
0792 
0793         return $this->fetchRow($sel);
0794     }
0795 
0796     public function fetchEarnings($member_id, $limit = null)
0797     {
0798         $sql = "
0799                 SELECT `project_category`.`title` AS `catTitle`,
0800                        `project`.*,
0801                        `member`.*,
0802                        `plings`.*
0803                 FROM `plings`
0804                  JOIN `project` ON `plings`.`project_id` = `project`.`project_id`
0805                  JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id`
0806                  JOIN `member` ON `project`.`member_id` = `member`.`member_id`
0807                 WHERE `plings`.`status_id` = 2
0808                   AND `project`.`status` = :status
0809                   AND `project`.`type_id` = 1
0810                   AND `project`.`member_id` = :memberId
0811                 ORDER BY `plings`.`create_time` DESC
0812                 ";
0813 
0814         if (null != $limit) {
0815             $sql .= $this->_db->quoteInto(" limit ?", $limit, 'INTEGER');
0816         }
0817 
0818         $result = $this->_db->fetchAll($sql,
0819             array('memberId' => $member_id, 'status' => Application_Model_Project::PROJECT_ACTIVE));
0820 
0821         return $this->generateRowSet($result);
0822     }
0823 
0824     /**
0825      * Finds an active user by given username or email ($identity)
0826      * Returns an empty rowset when no user found.
0827      *
0828      * @param string $identity could be the username or users mail address
0829      * @param bool   $withLoginLocal
0830      *
0831      * @return Zend_Db_Table_Row_Abstract
0832      */
0833     public function findActiveMemberByIdentity($identity, $withLoginLocal = false)
0834     {
0835         $sqlName = "SELECT * FROM `member` WHERE `is_active` = :active AND `is_deleted` = :deleted AND `username` = :identity";
0836         $sqlMail = "SELECT * FROM `member` WHERE `is_active` = :active AND `is_deleted` = :deleted AND `mail` = :identity";
0837         if ($withLoginLocal) {
0838             $sqlName .= " AND login_method = '" . self::MEMBER_LOGIN_LOCAL . "'";
0839             $sqlMail .= " AND login_method = '" . self::MEMBER_LOGIN_LOCAL . "'";
0840         }
0841 
0842         // test identity as username
0843         $resultName = $this->getAdapter()->fetchRow($sqlName,
0844             array('active' => self::MEMBER_ACTIVE, 'deleted' => self::MEMBER_NOT_DELETED, 'identity' => $identity));
0845         if ((false !== $resultName) AND (count($resultName) > 0)) {
0846             return $this->generateRowClass($resultName);
0847         }
0848 
0849         // test identity as mail
0850         $resultMail = $this->getAdapter()->fetchRow($sqlMail,
0851             array('active' => self::MEMBER_ACTIVE, 'deleted' => self::MEMBER_NOT_DELETED, 'identity' => $identity));
0852         if ((false !== $resultMail) AND (count($resultMail) > 0)) {
0853             return $this->generateRowClass($resultMail);
0854         }
0855 
0856         return $this->createRow();
0857     }
0858 
0859     /**
0860      * @param string $username
0861      * @return mixed
0862      */
0863     public function findActiveMemberByName($username)
0864     {
0865         $sql = '
0866           select m.member_id,m.username,profile_image_url 
0867           from member m 
0868           where m.is_active=1 and m.is_deleted = 0 and m.username like "' . $username . '%"
0869           limit 10
0870       ';
0871         $result = $this->getAdapter()->fetchAll($sql);
0872 
0873         return $result;
0874     }
0875 
0876     /**
0877      * @param string $hash
0878      * @param bool   $only_active
0879      *
0880      * @return array | false
0881      */
0882     public function findMemberForMailHash($hash, $only_active = true)
0883     {
0884         $sql = "
0885             SELECT `m`.* 
0886             FROM `member_email` AS `me`
0887             JOIN `member` AS `m` ON `m`.`member_id` = `me`.`email_member_id`
0888             WHERE `me`.`email_hash` = :email_hash
0889         ";
0890 
0891         if ($only_active) {
0892             $sql .= " `m`.`is_active` = 1 AND `m`.`is_deleted` = 0";
0893         }
0894 
0895         $member = $this->getAdapter()->fetchRow($sql, array('email_hash' => $hash));
0896 
0897         if (empty($member)) {
0898             return false;
0899         }
0900 
0901         return $member;
0902     }
0903 
0904     /**
0905      * @param Zend_Db_Table_Row_Abstract $memberData
0906      *
0907      * @return bool
0908      */
0909     public function isHiveUser($memberData)
0910     {
0911         if (empty($memberData)) {
0912             return false;
0913         }
0914         //20180801 ronald: If a hive user change his password, he gets the ocs password type and we do
0915         //have to check against the old hive password style
0916         //if ($memberData->source_id == self::SOURCE_HIVE) {
0917         //    return true;
0918         //}
0919         if ($memberData->password_type == self::PASSWORD_TYPE_HIVE) {
0920             return true;
0921         }
0922 
0923         return false;
0924     }
0925 
0926     public function fetchActiveHiveUserByUsername($username)
0927     {
0928         $sql = 'SELECT * FROM member WHERE username = :username AND is_active = 1 AND member.source_id = 1 AND member.is_deleted = 0';
0929 
0930         $result = $this->getAdapter()->query($sql, array('username' => $username))->fetch();
0931 
0932         return $result;
0933     }
0934 
0935     /**
0936      * @param $username
0937      * @return int|null
0938      * @throws Zend_Db_Statement_Exception
0939      */
0940     public function fetchActiveUserByUsername($username)
0941     {
0942         $sql = 'SELECT DISTINCT `member`.`member_id`
0943                 FROM `member`
0944                 WHERE LOWER(`username`) = :username
0945                 AND `is_active` = 1 
0946                 AND `member`.`is_deleted` = 0';
0947 
0948         $result = $this->getAdapter()->query($sql, array('username' => strtolower($username)))->fetchAll();
0949 
0950         if ($result && count($result) > 0) {
0951             $member_id = (int)$result[0]['member_id'];
0952 
0953             return $member_id;
0954         }
0955 
0956         return null;
0957     }
0958 
0959     public function fetchCommentsCount($member_id)
0960     {
0961         $sql = "
0962                   SELECT
0963                       count(1) AS `count`
0964                   FROM
0965                       `comments` 
0966                   WHERE `comment_target_id` <> 0 AND `comment_member_id` = :member_id AND `comment_active` = :comment_status
0967                  ";
0968         $result = $this->_db->fetchRow($sql, array(
0969             'member_id'      => $member_id,
0970             'comment_status' => Application_Model_DbTable_Comments::COMMENT_ACTIVE
0971         ));
0972 
0973         return $result['count'];
0974     }
0975 
0976     /**
0977      * @param      $member_id
0978      * @param null $limit
0979      *
0980      * @return Zend_Paginator
0981      * @throws Zend_Paginator_Exception
0982      */
0983     public function fetchComments($member_id, $limit = null)
0984     {
0985         $result = $this->fetchCommentsList($member_id, $limit);
0986         if (count($result) > 0) {
0987             return new Zend_Paginator(new Zend_Paginator_Adapter_Array($result));
0988         } else {
0989             return new Zend_Paginator(new Zend_Paginator_Adapter_Array(array()));
0990         }
0991     }
0992 
0993     /**
0994      * @param      $member_id
0995      * @param null $limit
0996      *
0997      * @return Zend_Paginator
0998      * @throws Zend_Paginator_Exception
0999      */
1000     public function fetchCommentsList($member_id, $limit = null)
1001     {
1002         $sql = '
1003             SELECT
1004                 `comment_id`
1005                 ,`comment_text`
1006                 ,`member`.`member_id`
1007                 ,`member`.`profile_image_url`
1008                 ,`comment_created_at`
1009                 ,`stat_projects`.`username`
1010                 ,`stat_projects`.`project_member_id`
1011                 ,`comment_target_id`
1012                 ,`stat_projects`.`title`
1013                 ,`stat_projects`.`project_id`      
1014                 ,`stat_projects`.`laplace_score`
1015                 ,`stat_projects`.`count_likes`
1016                 ,`stat_projects`.`count_dislikes`
1017                 ,`stat_projects`.`image_small` 
1018                 ,`stat_projects`.`version`
1019                 ,`stat_projects`.`cat_title`
1020                 ,`stat_projects`.`count_comments`
1021                 ,`stat_projects`.`changed_at`
1022                 ,`stat_projects`.`created_at`        
1023             FROM `comments`
1024             INNER JOIN  `member` ON `comments`.`comment_member_id` = `member`.`member_id`
1025             INNER JOIN `stat_projects` ON `comments`.`comment_target_id` = `stat_projects`.`project_id` AND `comments`.`comment_type` = 0
1026             WHERE `comments`.`comment_active` = :comment_status
1027             AND `stat_projects`.`status` = :project_status
1028             AND `comments`.`comment_member_id` = :member_id
1029             ORDER BY `comments`.`comment_created_at` DESC
1030         ';
1031 
1032         if (isset($limit)) {
1033             $sql .= ' limit ' . (int)$limit;
1034         }
1035         $result = $this->_db->fetchAll($sql, array(
1036             'member_id'      => $member_id,
1037             'project_status' => Application_Model_DbTable_Project::PROJECT_ACTIVE,
1038             'comment_status' => Application_Model_DbTable_Comments::COMMENT_ACTIVE
1039         ));
1040 
1041         return $result;
1042     }
1043 
1044     public function fetchCntSupporters($member_id)
1045     {
1046         $sql = '
1047                 SELECT DISTINCT `plings`.`member_id` FROM `plings`
1048                  JOIN `project` ON `plings`.`project_id` = `project`.`project_id`                
1049                  JOIN `member` ON `project`.`member_id` = `member`.`member_id`
1050                 WHERE `plings`.`status_id` = 2
1051                   AND `project`.`status` = :project_status
1052                   AND `project`.`type_id` = 1
1053                   AND `project`.`member_id` = :member_id
1054             ';
1055         $result =
1056             $this->_db->fetchAll($sql,
1057                 array('member_id' => $member_id, 'project_status' => Application_Model_Project::PROJECT_ACTIVE));
1058 
1059         return count($result);
1060     }
1061 
1062     public function fetchSupporterDonationInfo($member_id)
1063     {
1064         /*$sql = 'SELECT max(active_time) AS active_time_max
1065                             ,min(active_time)  AS active_time_min
1066                             ,(DATE_ADD(max(active_time), INTERVAL 1 YEAR) > now()) AS issupporter
1067                             ,count(1)  AS cnt from support  where status_id = 2 AND type_id = 0 AND member_id = :member_id ';*/
1068         $sql = "
1069                 select 
1070                 member_id,
1071                 max(active_time_max) as active_time_max,
1072                 min(active_time_min) as active_time_min,
1073                 max(is_valid) as issupporter,
1074                 count(1) AS cnt
1075                 from v_support
1076                 where member_id = :member_id
1077         ";
1078         $result = $this->getAdapter()->fetchRow($sql, array('member_id' => $member_id));
1079 
1080         return $result;
1081     }
1082 
1083     public function fetchSupporterSubscriptionInfo($member_id)
1084     {
1085         $sql = 'SELECT create_time,amount,period,period_frequency from support  where status_id = 2 AND type_id = 1 
1086                 AND member_id = :member_id
1087                 ORDER BY create_time desc
1088                 LIMIT 1';
1089         $result = $this->getAdapter()->fetchRow($sql, array('member_id' => $member_id));
1090 
1091         return $result;
1092     }
1093 
1094     public function fetchSupporterSectionInfo($member_id)
1095     {
1096         $sql = "select GROUP_CONCAT(distinct c.name) sections from 
1097                 section_support s, support t , section c
1098                 where s.support_id = t.id and s.section_id = c.section_id
1099                 and  t.member_id = :member_id and t.status_id=2
1100                 and s.is_active = 1
1101                 order by c.order";
1102         $result = $this->getAdapter()->fetchRow($sql, array('member_id' => $member_id));
1103 
1104         return $result;
1105     }
1106 
1107     public function fetchLastActiveTime($member_id)
1108     {
1109         $sql_page_views =
1110             "SELECT `created_at` AS `lastactive` FROM `stat_page_views` WHERE `member_id` = :member_id ORDER BY `created_at` DESC LIMIT 1";
1111         $sql_activities = "SELECT `time` AS `lastactive` FROM `activity_log` WHERE `member_id` = :member_id ORDER BY `time` DESC LIMIT 1";
1112 
1113         $result_page_views = $this->getAdapter()->fetchRow($sql_page_views, array('member_id' => $member_id));
1114         $result_activities = $this->getAdapter()->fetchRow($sql_activities, array('member_id' => $member_id));
1115 
1116         if (count($result_page_views) > 0 AND count($result_activities) > 0) {
1117             return $result_page_views['lastactive'] > $result_activities['lastactive'] ? $result_page_views['lastactive']
1118                 : $result_activities['lastactive'];
1119         }
1120         if (count($result_page_views) > count($result_activities)) {
1121             return $result_page_views['lastactive'];
1122         }
1123         if (count($result_activities) > count($result_page_views)) {
1124             return $result_activities['lastactive'];
1125         }
1126 
1127         return null;
1128     }
1129 
1130     /**
1131      * @param int $member_id
1132      *
1133      * @return array
1134      */
1135     public function fetchContributedProjectsByCat($member_id)
1136     {
1137         $projects = $this->fetchSupportedProjects($member_id);
1138         $catArray = array();
1139         if (count($projects) == 0) {
1140             return $catArray;
1141         }
1142 
1143         foreach ($projects as $pro) {
1144             $catArray[$pro->catTitle] = array();
1145         }
1146 
1147         $helperProductUrl = new Default_View_Helper_BuildProductUrl();
1148         foreach ($projects as $pro) {
1149             $projArr = array();
1150             $projArr['id'] = $pro->project_id;
1151             $projArr['name'] = $pro->title;
1152             $projArr['image'] = $pro->image_small;
1153             $projArr['url'] = $helperProductUrl->buildProductUrl($pro->project_id, '', null, true);
1154             $projArr['sumAmount'] = $pro->sumAmount;
1155             array_push($catArray[$pro->catTitle], $projArr);
1156         }
1157 
1158         return $catArray;
1159     }
1160 
1161     /**
1162      * @param int  $member_id
1163      * @param null $limit
1164      *
1165      * @return Zend_Db_Table_Rowset_Abstract
1166      */
1167     public function fetchSupportedProjects($member_id, $limit = null)
1168     {
1169         $sql = "
1170                 SELECT `plings`.`project_id`,                       
1171                        `project`.`title`,
1172                        `project`.`image_small`,                       
1173                        `project_category`.`title` AS `catTitle`,                       
1174                        (SELECT SUM(`amount`) FROM `plings` WHERE `plings`.`project_id`=`project`.`project_id` AND `plings`.`status_id`=2) AS `sumAmount`
1175                 FROM `plings`
1176                  JOIN `project` ON `plings`.`project_id` = `project`.`project_id`
1177                  JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id`                 
1178                 WHERE `plings`.`status_id` IN (2,3,4)
1179                   AND `plings`.`member_id` = :member_id
1180                   AND `project`.`status` = :project_status
1181                   AND `project`.`type_id` = 1
1182                 GROUP BY `plings`.`project_id`
1183                 ORDER BY `sumAmount` DESC
1184                 ";
1185 
1186         if (null != $limit) {
1187             $sql .= $this->_db->quoteInto(" limit ?", $limit, 'INTEGER');
1188         }
1189 
1190         $result =
1191             $this->_db->fetchAll($sql,
1192                 array('member_id' => $member_id, 'project_status' => Application_Model_Project::PROJECT_ACTIVE));
1193 
1194         return $this->generateRowSet($result);
1195     }
1196 
1197     /**
1198      * @param string $value
1199      * @param int    $test_case_sensitive
1200      * @param array  $omitMember
1201      * @param bool   $onlyActive
1202      *
1203      * @return array return an array of rows
1204      */
1205     public function findUsername(
1206         $value,
1207         $test_case_sensitive = self::CASE_INSENSITIVE,
1208         $omitMember = array(),
1209         $onlyActive = false
1210     ) {
1211         $sql = "
1212             SELECT *
1213             FROM `member`
1214         ";
1215         if ($test_case_sensitive == self::CASE_INSENSITIVE) {
1216             $sql .= "WHERE LCASE(member.username) = LCASE(:username)";
1217         } else {
1218             $sql .= "WHERE member.username = :username";
1219         }
1220 
1221         if (count($omitMember) > 0) {
1222             $sql .= " AND member.member_id NOT IN (" . implode(',', $omitMember) . ")";
1223         }
1224 
1225         if ($onlyActive) {
1226             $sql .= " AND member.is_active = 1 and member.is_deleted = 0";
1227         }
1228 
1229         return $this->_db->fetchAll($sql, array('username' => $value));
1230     }
1231 
1232     /**
1233      * @param string $login
1234      *
1235      * @return int
1236      */
1237     public function generateUniqueUsername($login)
1238     {
1239         $sql = "SELECT COUNT(*) AS `counter` FROM `member` WHERE `username` REGEXP CONCAT(:user_name,'[_0-9]*$')";
1240         $result = $this->_db->fetchRow($sql, array('user_name' => $login));
1241 
1242         return $login . '_' . $result['counter'];
1243     }
1244 
1245     /**
1246      * @param int    $member_id
1247      * @param string $email
1248      *
1249      * @return bool
1250      * @throws Zend_Db_Statement_Exception
1251      */
1252     public function setActive($member_id, $email)
1253     {
1254         $sql = "
1255             UPDATE `member`
1256               STRAIGHT_JOIN `member_email` ON `member`.`member_id` = `member_email`.`email_member_id` AND `member_email`.`email_checked` IS NULL AND `member`.`is_deleted` = 0 AND `member_email`.`email_deleted` = 0
1257             SET `member`.`mail_checked` = 1, `member`.`is_active` = 1, `member`.`changed_at` = NOW(), `member_email`.`email_checked` = NOW()
1258             WHERE `member`.`member_id` = :memberId AND `member_email`.`email_address` = :mailAddress;
1259         ";
1260         $stmnt = $this->_db->query($sql, array('memberId' => $member_id, 'mailAddress' => $email));
1261 
1262         return $stmnt->rowCount() > 0 ? true : false;
1263     }
1264 
1265     /**
1266      * @param string $identity
1267      *
1268      * @return Zend_Db_Table_Row_Abstract
1269      */
1270     public function findActiveMemberByMail($identity)
1271     {
1272         $sqlMail = "
1273                     SELECT `m`.*, `me`.`email_address` AS `mail`, IF(ISNULL(`me`.`email_checked`),0,1) AS `mail_checked`
1274                     FROM `member` AS `m`
1275                     JOIN `member_email` AS `me` ON `me`.`email_member_id` = `m`.`member_id` AND `me`.`email_primary` = 1
1276                     WHERE `is_active` = :active AND `is_deleted` = :deleted AND `me`.`email_address` = :identity
1277         ";
1278 
1279         // test identity as mail
1280         $resultMail = $this->getAdapter()->fetchRow($sqlMail,
1281             array('active' => self::MEMBER_ACTIVE, 'deleted' => self::MEMBER_NOT_DELETED, 'identity' => $identity));
1282         if ((false !== $resultMail) AND (count($resultMail) > 0)) {
1283             return $this->generateRowClass($resultMail);
1284         }
1285 
1286         return $this->createRow();
1287     }
1288 
1289     public function getMembersAvatarOldAutogenerated($orderby = 'member_id desc', $limit = null, $offset = null)
1290     {
1291         $sql = "
1292                      SELECT * FROM `tmp_member_avatar_unknow` 
1293              ";
1294 
1295 
1296         if (isset($orderby)) {
1297             $sql = $sql . '  order by ' . $orderby;
1298         }
1299 
1300         if (isset($limit)) {
1301             $sql .= ' limit ' . (int)$limit;
1302         }
1303 
1304         if (isset($offset)) {
1305             $sql .= ' offset ' . (int)$offset;
1306         }
1307 
1308         $resultSet = $this->_db->fetchAll($sql);
1309 
1310         return $resultSet;
1311     }
1312 
1313     public function getMembersAvatarOldAutogeneratedTotalCount()
1314     {
1315         $sql = " 
1316                       select count(1) as cnt
1317                       from tmp_member_avatar_unknow 
1318         ";
1319         $result = $this->getAdapter()->query($sql, array())->fetchAll();
1320 
1321         return $result[0]['cnt'];
1322     }
1323 
1324     public function updateAvatarTypeId($member_id, $type_id)
1325     {
1326         $sql = "
1327                       update member set avatar_type_id = :type_id where member_id = :member_id
1328                    ";
1329         $this->getAdapter()->query($sql, array('type_id' => $type_id, 'member_id' => $member_id));
1330     }
1331 
1332     /**
1333      * @param $userData
1334      * @param $uuidMember
1335      *
1336      * @return string
1337      * @throws Exception
1338      */
1339     protected function generateIdentIcon_old($userData, $uuidMember)
1340     {
1341         $identIcon = new Local_Tools_Identicon();
1342         $tmpImagePath = IMAGES_UPLOAD_PATH . 'tmp/' . $uuidMember . '.png';
1343         imagepng($identIcon->renderIdentIcon(sha1($userData['mail']), 1100), $tmpImagePath);
1344 
1345         $imageService = new Application_Model_DbTable_Image();
1346         $imageFilename = $imageService->saveImageOnMediaServer($tmpImagePath);
1347 
1348         return $imageFilename;
1349     }
1350 
1351     /**
1352      * @param int $member_id
1353      *
1354      * @throws Exception
1355      * @deprecated since we're using solr server for searching
1356      */
1357     private function removeMemberProjectsFromSearch($member_id)
1358     {
1359         $modelProject = new Application_Model_Project();
1360         $memberProjects = $modelProject->fetchAllProjectsForMember($member_id);
1361         $modelSearch = new Application_Model_Search_Lucene();
1362         foreach ($memberProjects as $memberProject) {
1363             $product = array();
1364             $product['project_id'] = $memberProject->project_id;
1365             $product['project_category_id'] = $memberProject->project_category_id;
1366             $modelSearch->deleteDocument($product);
1367         }
1368     }
1369 }