File indexing completed on 2024-10-13 13:29:13
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 }