public function findUsers($kw, $limit = null) { $questionName = OW::getConfig()->getValue('base', 'display_name_question'); $questionDataTable = BOL_QuestionDataDao::getInstance()->getTableName(); $limitStr = $limit === null ? '' : 'LIMIT 0, ' . intval($limit); $query = 'SELECT DISTINCT qd.userId FROM ' . $questionDataTable . ' qd LEFT JOIN `' . BOL_UserSuspendDao::getInstance()->getTableName() . '` AS `us` ON ( `qd`.`userId` = `us`.`userId` ) WHERE `us`.`userId` IS NULL AND questionName=:name AND textValue LIKE :kw ' . $limitStr; return OW::getDbo()->queryForColumnList($query, array('kw' => '%' . $kw . '%', 'name' => $questionName)); }
function usearch_set_question_sql(BASE_CLASS_QueryBuilderEvent $event) { $params = $event->getParams(); if (empty($params['question']) || !$params['question'] instanceof BOL_Question || empty($params['value']) || !in_array($params['question']->name, array('sex', 'match_sex'))) { return; } $value = is_array($params['value']) ? array_sum($params['value']) : (int) $params['value']; $prefix = !empty($params['prefix']) ? $params['prefix'] : 'q' . rand(100, 10000); $questionName = $params['question']->name == 'sex' ? 'match_sex' : 'sex'; $innerJoin = " INNER JOIN `" . BOL_QuestionDataDao::getInstance()->getTableName() . "` `" . $prefix . "`\n ON ( `user`.`id` = `" . $prefix . "`.`userId` AND `" . $prefix . "`.`questionName` = '" . OW::getDbo()->escapeString($questionName) . "' AND `" . $prefix . "`.`intValue` & " . OW::getDbo()->escapeString($value) . " ) "; $event->addJoin($innerJoin); }
public function findUsers($kw, $userId, $limit = null) { if (!OW::getPluginManager()->isPluginActive('friends')) { return array(); } $friendsTable = FRIENDS_BOL_FriendshipDao::getInstance()->getTableName(); $questionName = OW::getConfig()->getValue('base', 'display_name_question'); $questionDataTable = BOL_QuestionDataDao::getInstance()->getTableName(); $limitStr = $limit === null ? '' : 'LIMIT 0, ' . intval($limit); $query = "SELECT `fr`.`userId` FROM `" . $friendsTable . "` AS `fr`\n INNER JOIN " . $questionDataTable . " qd ON fr.userId = qd.userId\n LEFT JOIN `" . BOL_UserSuspendDao::getInstance()->getTableName() . "` AS `us` ON ( `fr`.`friendId` = `us`.`userId` )\n WHERE `fr`.`status` = :status AND `us`.`userId` IS NULL AND `fr`.`friendId` = :userId\n AND qd.questionName=:name AND qd.textValue LIKE :kw\n UNION\n SELECT `fr`.`friendId` AS `userId` FROM `" . $friendsTable . "` AS `fr`\n INNER JOIN " . $questionDataTable . " qd ON fr.friendId = qd.userId\n LEFT JOIN `" . BOL_UserSuspendDao::getInstance()->getTableName() . "` AS `us` ON ( `fr`.`friendId` = `us`.`userId` )\n WHERE `fr`.`status` = :status AND `us`.`userId` IS NULL AND `fr`.`userId` = :userId\n AND qd.questionName=:name AND qd.textValue LIKE :kw\n {$limitStr}\n "; return OW::getDbo()->queryForColumnList($query, array('userId' => $userId, 'status' => FRIENDS_BOL_FriendshipDao::VAL_STATUS_ACTIVE, 'kw' => '%' . $kw . '%', 'name' => $questionName)); }
public function photoGetPhotoList(BASE_CLASS_QueryBuilderEvent $event) { $params = $event->getParams(); $aliases = $params['aliases']; if (empty($params['listType']) || !in_array($params['listType'], array('latest', 'featured', 'toprated', 'most_discussed', 'searchByDesc', 'searchByHashtag', 'searchByUsername'))) { return; } if (!OW::getUser()->isAuthenticated() || !(bool) OW::getConfig()->getValue('skadate', 'photo_filter_setting_matching') || OW::getUser()->isAuthorized('photo') || OW::getUser()->isAuthorized('base')) { return; } $userId = OW::getUser()->getId(); $matchValue = BOL_QuestionService::getInstance()->getQuestionData(array($userId), array('sex', 'match_sex')); if (empty($matchValue[$userId]['match_sex'])) { return; } $join = 'INNER JOIN `' . BOL_UserDao::getInstance()->getTableName() . '` AS `sk_u` ON(`' . $aliases['album'] . '`.`userId` = `sk_u`.`id`) INNER JOIN `' . BOL_QuestionDataDao::getInstance()->getTableName() . '` AS `sk_qd` ON (`sk_qd`.`userId` = `sk_u`.`id` AND `sk_qd`.`questionName` = :sk_sexQuestionName AND `sk_qd`.`intValue` & :sk_matchSexValue) INNER JOIN `' . BOL_QuestionDataDao::getInstance()->getTableName() . '` AS `sk_qd1` ON (`sk_qd1`.`userId` = `sk_u`.`id` AND `sk_qd1`.`questionName` = :sk_matchSexQuestionName AND `sk_qd1`.`intValue` & :sk_sexValue)'; $params = array('sk_sexQuestionName' => 'sex', 'sk_matchSexQuestionName' => 'match_sex', 'sk_sexValue' => $matchValue[$userId]['sex'], 'sk_matchSexValue' => $matchValue[$userId]['match_sex']); $event->addJoin($join); $event->addBatchQueryParams($params); }
private function getQueryParamsForUserIdList($userId, $userIdList, $sortOrder = 'newest') { $questionService = BOL_QuestionService::getInstance(); $userService = BOL_UserService::getInstance(); $matchFields = $this->findAll(); $prefix = 'qd'; $counter = 0; $innerJoin = ''; $leftJoin = ''; $where = ''; $compatibility = ' 0 +'; $requiredCompatibility = ' 1 *'; foreach ($matchFields as $field) { $question = $questionService->findQuestionByName($field->questionName); if (!$question) { continue; } $matchQuestion = $questionService->findQuestionByName($field->matchQuestionName); if (!$matchQuestion) { continue; } $checkData = $questionService->getQuestionData(array($userId), array($field->matchQuestionName)); if (empty($checkData[$userId][$field->matchQuestionName])) { if ($field->required) { return array(); } } $value1 = null; if (isset($checkData[$userId][$field->matchQuestionName])) { $value1 = $checkData[$userId][$field->matchQuestionName]; } if (!empty($value1)) { if ($field->required) { // calculate compatibility for required fields $questionString = $this->prepareQuestionWhere($question, $value1, $prefix . $counter); if (!empty($questionString)) { // there is no base match questions // if ( $question->base == 1 ) // { // $where .= ' OR ' . $questionString; // // if users don't match by required field than compatibility = 0 // $requiredCompatibility .= ' IF( ' . $questionString . ', 1, 0 ) *'; // } // else // { $innerJoin .= "\n LEFT JOIN `" . BOL_QuestionDataDao::getInstance()->getTableName() . "` `" . $prefix . $counter . "`\n ON ( `user`.`id` = `" . $prefix . $counter . "`.`userId` AND `" . $prefix . $counter . "`.`questionName` = '" . $this->dbo->escapeString($question->name) . "' AND " . $questionString . " ) "; $compatibility .= ' IF( `' . $prefix . $counter . '`.`questionName` IS NOT NULL, ' . $field->coefficient . ', 0 ) +'; // if users don't match by required field than compatibility = 0 $requiredCompatibility .= ' IF( `' . $prefix . $counter . '`.`id` IS NOT NULL, 1, 0 ) *'; // } $counter++; } $checkData2 = $questionService->getQuestionData(array($userId), array($field->questionName)); if (empty($checkData2[$userId][$field->questionName])) { continue; } $value2 = $checkData2[$userId][$field->questionName]; $questionString = $this->prepareQuestionWhere($matchQuestion, $value2, $prefix . $counter); if (!empty($questionString)) { // there is no base match questions // if ( $matchQuestion->base == 1 ) // { // $where .= ' OR ' . $questionString; // // if users don't match by required field than compatibility = 0 // $requiredCompatibility .= ' IF( ' . $questionString . ', 1, 0 ) *'; // } // else // { $innerJoin .= "\n LEFT JOIN `" . BOL_QuestionDataDao::getInstance()->getTableName() . "` `" . $prefix . $counter . "`\n ON ( `user`.`id` = `" . $prefix . $counter . "`.`userId` AND `" . $prefix . $counter . "`.`questionName` = '" . $this->dbo->escapeString($matchQuestion->name) . "' AND " . $questionString . " ) "; // if users don't match by required field than compatibility = 0 $requiredCompatibility .= ' IF( `' . $prefix . $counter . '`.`questionName` IS NOT NULL, 1, 0 ) *'; // } $counter++; } } else { // calculate compatibility for not required fields $questionString = $this->prepareQuestionWhere($question, $value1, $prefix . $counter); if (!empty($questionString)) { // there is no base match questions // if ( $question->base == 1 ) // { // $where .= ' OR ' . $questionString; // } // else // { $leftJoin .= "\n LEFT JOIN `" . BOL_QuestionDataDao::getInstance()->getTableName() . "` `" . $prefix . $counter . "` ON ( `user`.`id` = `" . $prefix . $counter . "`.`userId` AND `" . $prefix . $counter . "`.`questionName` = '" . $this->dbo->escapeString($question->name) . "' AND " . $questionString . " ) "; $compatibility .= ' IF( `' . $prefix . $counter . '`.`questionName` IS NOT NULL, ' . $field->coefficient . ', 0 ) +'; $counter++; // } } } } else { $compatibility .= ' ' . $field->coefficient . ' +'; } } /**/ if ($sortOrder == 'newest') { $order = ' `user`.`joinStamp` DESC, '; } else { if ($sortOrder == 'mail') { $order = ' `user`.`joinStamp` DESC, '; $matchmaking_lastmatch_userid = BOL_PreferenceService::getInstance()->getPreferenceValue('matchmaking_lastmatch_userid', $userId); $where = ' AND `user`.`id` > ' . $matchmaking_lastmatch_userid; } else { if ($sortOrder == 'compatible') { //$order = '(' . substr($compatibility, 0, -1) . ') DESC , '; $order = '`matches`.`compatibility` DESC , '; } } } if (!empty($userIdList)) { $listOfUserIds = $this->dbo->mergeInClause($userIdList); $where .= ' AND `user`.`id` IN ( ' . $listOfUserIds . ' ) '; } $result = array('userId' => $userId, 'join' => $innerJoin . $leftJoin, 'where' => $where, 'order' => $order, 'compatibility' => ' (' . substr($compatibility, 0, -1) . ') * (' . substr($requiredCompatibility, 0, -1) . ') '); if (!empty($userIdList)) { $result['userIdList'] = $userIdList; } return $result; }
public function findFriendIdListByDisplayName($userId, $search, $first, $count, $userIdList = null) { $ids = $this->findFriendIdList($userId, $first, $count, $userIdList); $queryParts = BOL_UserDao::getInstance()->getUserQueryFilter('u', 'id', array('method' => 'MAILBOX_BOL_AjaxService::findFriendIdListByDisplayName')); $questionName = OW::getConfig()->getValue('base', 'display_name_question'); $params = array('search' => $search . '%'); if ($questionName === 'username') { $order = ' ORDER BY `u`.`username`'; $queryParts['where'] .= ' AND `u`.`username` LIKE :search'; } else { $order = ' ORDER BY `qd`.`textValue`'; $params['questionName'] = $questionName; $queryParts['where'] .= ' AND qd.questionName=:questionName AND qd.textValue LIKE :search'; $queryParts['join'] .= ' INNER JOIN `' . BOL_QuestionDataDao::getInstance()->getTableName() . '` AS `qd` ON(`u`.`id` = `qd`.`userId`) '; } $query = 'SELECT DISTINCT `u`.`id` FROM `' . BOL_UserDao::getInstance()->getTableName() . '` AS `u` ' . $queryParts['join'] . ' WHERE`u`.`id` IN(' . $this->dbo->mergeInClause($ids) . ') AND ' . $queryParts['where'] . ' ' . $order . ' LIMIT :first, :count'; return $this->dbo->queryForColumnList($query, array_merge(array('first' => $first, 'count' => $count), $params)); }
/** * Constructor. * */ private function __construct() { $this->questionsBOL['base'] = array(); $this->questionsBOL['notBase'] = array(); $this->questionDao = BOL_QuestionDao::getInstance(); $this->valueDao = BOL_QuestionValueDao::getInstance(); $this->dataDao = BOL_QuestionDataDao::getInstance(); $this->sectionDao = BOL_QuestionSectionDao::getInstance(); $this->accountDao = BOL_QuestionAccountTypeDao::getInstance(); $this->accountToQuestionDao = BOL_QuestionToAccountTypeDao::getInstance(); $this->userService = BOL_UserService::getInstance(); $this->questionConfigDao = BOL_QuestionConfigDao::getInstance(); // all available presentations are hardcoded here $this->presentations = array(self::QUESTION_PRESENTATION_TEXT => self::QUESTION_VALUE_TYPE_TEXT, self::QUESTION_PRESENTATION_SELECT => self::QUESTION_VALUE_TYPE_SELECT, self::QUESTION_PRESENTATION_TEXTAREA => self::QUESTION_VALUE_TYPE_TEXT, self::QUESTION_PRESENTATION_CHECKBOX => self::QUESTION_VALUE_TYPE_BOOLEAN, self::QUESTION_PRESENTATION_RADIO => self::QUESTION_VALUE_TYPE_SELECT, self::QUESTION_PRESENTATION_MULTICHECKBOX => self::QUESTION_VALUE_TYPE_MULTISELECT, self::QUESTION_PRESENTATION_DATE => self::QUESTION_VALUE_TYPE_DATETIME, self::QUESTION_PRESENTATION_BIRTHDATE => self::QUESTION_VALUE_TYPE_DATETIME, self::QUESTION_PRESENTATION_AGE => self::QUESTION_VALUE_TYPE_DATETIME, self::QUESTION_PRESENTATION_RANGE => self::QUESTION_VALUE_TYPE_TEXT, self::QUESTION_PRESENTATION_URL => self::QUESTION_VALUE_TYPE_TEXT, self::QUESTION_PRESENTATION_PASSWORD => self::QUESTION_VALUE_TYPE_TEXT); }
public function findUserListByBirthday($date) { $query = "SELECT `u`.`id` FROM `" . $this->getTableName() . "` AS `u`\r\n INNER JOIN `" . BOL_QuestionDataDao::getInstance()->getTableName() . "` AS `qd` ON(`u`.`id` = `qd`.`userId`)\r\n WHERE `qd`.`questionName` = 'birthdate' AND DATE_FORMAT(`qd`.`dateValue`, '" . date('Y') . "-%m-%d') = :date"; return $this->dbo->queryForColumnList($query, array('date' => $date)); }
public function photoGetPhotoList(BASE_CLASS_QueryBuilderEvent $event) { if (!OW::getUser()->isAuthenticated()) { return; } $params = $event->getParams(); $aliases = $params['tables']; $currentId = OW::getUser()->getId(); $sex = BOL_QuestionService::getInstance()->getQuestionData(array($currentId), array('sex')); $join = ' INNER JOIN `' . BOL_QuestionDataDao::getInstance()->getTableName() . '` AS `bqdt` ON(`' . $aliases['content'] . '`.`userId` = `bqdt`.`userId` AND `bqdt`.`questionName` = \'sex\' AND (`bqdt`.`intValue` != ' . $sex[$currentId]['sex'] . ' OR `' . $aliases['content'] . '`.`userId` = ' . $currentId . ' )) '; $params = array('sex' => $sex[$currentId]['sex'], 'currentId' => $currentId); $event->addJoin($join); }
public function getUserTokenJoinString($userToken) { $userTokenJoin = ""; if (mb_strlen($userToken)) { $question = OW::getConfig()->getValue('base', 'display_name_question'); if ($question == 'username') { $userTokenJoin = " INNER JOIN `" . BOL_UserDao::getInstance()->getTableName() . "` AS `u`\n ON (`u`.`id` = `p`.`userId` AND `u`.`username` LIKE '" . $this->dbo->escapeString($userToken) . "%') "; } else { $userTokenJoin = " INNER JOIN `" . BOL_QuestionDataDao::getInstance()->getTableName() . "` AS `qd`\n ON (`qd`.`userId`=`p`.`userId` AND `qd`.`questionName`='realname'\n AND `qd`.`textValue` LIKE '" . $this->dbo->escapeString($userToken) . "%') "; } } return $userTokenJoin; }
private function photoFiltrSkadate($event) { $params = $event->getParams(); $aliases = $params['tables']; $currentId = OW::getUser()->getId(); $sex = BOL_QuestionService::getInstance()->getQuestionData(array($currentId), array('sex')); if ($this->onlyDifferentSexCanSeePhoto) { $join = ' INNER JOIN `' . BOL_QuestionDataDao::getInstance()->getTableName() . '` AS `bqdt` ON(`' . $aliases['content'] . '`.`userId` = `bqdt`.`userId` AND `bqdt`.`questionName` = \'sex\' AND (`bqdt`.`intValue` != ' . $sex[$currentId]['sex'] . ' OR `' . $aliases['content'] . '`.`userId` = ' . $currentId . ' )) '; $params = array('sex' => $sex[$currentId]['sex'], 'currentId' => $currentId); $event->addJoin($join); } if ($this->onlySameSexCanSeePhoto) { $join = ' INNER JOIN `' . BOL_QuestionDataDao::getInstance()->getTableName() . '` AS `bqdt` ON(`' . $aliases['content'] . '`.`userId` = `bqdt`.`userId` AND `bqdt`.`questionName` = \'sex\' AND (`bqdt`.`intValue` = ' . $sex[$currentId]['sex'] . ' OR `' . $aliases['content'] . '`.`userId` = ' . $currentId . ' )) '; $params = array('sex' => $sex[$currentId]['sex'], 'currentId' => $currentId); $event->addJoin($join); } }
public function findConversationByKeyword($kw, $limit = null, $from = 0) { $questionName = OW::getConfig()->getValue('base', 'display_name_question'); $questionDataTable = BOL_QuestionDataDao::getInstance()->getTableName(); $userId = OW::getUser()->getId(); $limitStr = $limit === null ? '' : 'LIMIT ' . intval($from) . ', ' . intval($limit); //TODO get by username if ($questionName == 'username') { $join = "INNER JOIN `" . BOL_UserDao::getInstance()->getTableName() . "` AS us ON ( `us`.`id` = IF (`conv`.`initiatorId`=:user, `conv`.`interlocutorId`, `conv`.`initiatorId`) )"; $where = " `us`.`username` LIKE :kw "; } else { $join = "INNER JOIN `" . $questionDataTable . "` AS qd ON ( `qd`.`userId` = IF (`conv`.`initiatorId`=:user, `conv`.`interlocutorId`, `conv`.`initiatorId`) )"; $where = " `qd`.`questionName`=:name AND `qd`.`textValue` LIKE :kw "; } $query = " SELECT `conv`.`id`,\n `conv`.`initiatorId`,\n `conv`.`interlocutorId`,\n `conv`.`subject`,\n `conv`.`read`,\n `conv`.`viewed`,\n `conv`.`lastMessageTimestamp`,\n\n `message`.`id` as lastMessageId,\n `message`.`text` as lastMessageText,\n `message`.`recipientRead` as lastMessageRecipientRead,\n `message`.`isSystem` as lastMessageIsSystem,\n `message`.`senderId` as lastMessageSenderId,\n `message`.`recipientId` as lastMessageRecipientId,\n `message`.`wasAuthorized` as lastMessageWasAuthorized\n\n FROM `" . $this->getTableName() . "` AS `conv`\n\n\t\t\t\t INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `message`\n\t\t\t\t \tON ( `conv`.`lastMessageId` = `message`.`id` )\n\n {$join}\n\n\t\t\t\t WHERE ( {$where} ) AND (( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `message`.`timeStamp`>`conv`.`initiatorDeletedTimestamp` ) )\n\t\t\t\t\t \tOR ( `conv`.`interlocutorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `message`.`timeStamp`>`conv`.`interlocutorDeletedTimestamp` ) ))\n\n UNION\n\n SELECT `conv`.`id`,\n `conv`.`initiatorId`,\n `conv`.`interlocutorId`,\n `conv`.`subject`,\n `conv`.`read`,\n `conv`.`viewed`,\n `conv`.`lastMessageTimestamp`,\n\n `message`.`id` as lastMessageId,\n `message`.`text` as lastMessageText,\n `message`.`recipientRead` as lastMessageRecipientRead,\n `message`.`isSystem` as lastMessageIsSystem,\n `message`.`senderId` as lastMessageSenderId,\n `message`.`recipientId` as lastMessageRecipientId,\n `message`.`wasAuthorized` as lastMessageWasAuthorized\n\n FROM `" . $this->getTableName() . "` AS `conv`\n\n\t\t\t\t INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `message`\n\t\t\t\t \tON ( `conv`.`lastMessageId` = `message`.`id` )\n\n\t\t\t\t WHERE conv.subject LIKE :kw AND ( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `message`.`timeStamp`>`conv`.`initiatorDeletedTimestamp` ) )\n\n\n UNION\n\n SELECT `conv`.`id`,\n `conv`.`initiatorId`,\n `conv`.`interlocutorId`,\n `conv`.`subject`,\n `conv`.`read`,\n `conv`.`viewed`,\n `conv`.`lastMessageTimestamp`,\n\n `message`.`id` as lastMessageId,\n `message`.`text` as lastMessageText,\n `message`.`recipientRead` as lastMessageRecipientRead,\n `message`.`isSystem` as lastMessageIsSystem,\n `message`.`senderId` as lastMessageSenderId,\n `message`.`recipientId` as lastMessageRecipientId,\n `message`.`wasAuthorized` as lastMessageWasAuthorized\n\n FROM `" . $this->getTableName() . "` AS `conv`\n\n\t\t\t\t INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `message`\n\t\t\t\t \tON ( `conv`.`lastMessageId` = `message`.`id` )\n\n\t\t\t\t WHERE conv.subject LIKE :kw AND ( `conv`.`interlocutorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `message`.`timeStamp`>`conv`.`interlocutorDeletedTimestamp` ))\n\n\n GROUP BY 1\n ORDER BY 7 DESC\n\n " . $limitStr; //print_r($query); // $query = " SELECT `conv`.`id`, // `conv`.`initiatorId`, // `conv`.`interlocutorId`, // `conv`.`subject`, // `conv`.`read`, // `conv`.`viewed`, // // `last_m`.`initiatorMessageId`, // `initiatorMessage`.`id` as initiatorLastMessageId, // `initiatorMessage`.`text` as initiatorText, // `initiatorMessage`.`recipientRead` as initiatorRecipientRead, // `initiatorMessage`.`isSystem` as initiatorMessageIsSystem, // `initiatorMessage`.`senderId` as `initiatorMessageSenderId`, // `initiatorMessage`.`recipientId` as `initiatorMessageRecipientId`, // `initiatorMessage`.`wasAuthorized` as `initiatorMessageWasAuthorized`, // `initiatorMessage`.`timeStamp` as `initiatorMessageTimestamp`, // // `last_m`.`interlocutorMessageId`, // `interlocutorMessage`.`id` as interlocutorLastMessageId, // `interlocutorMessage`.`text` as interlocutorText, // `interlocutorMessage`.`recipientRead` as interlocutorRecipientRead, // `interlocutorMessage`.`isSystem` as interlocutorMessageIsSystem, // `interlocutorMessage`.`senderId` as `interlocutorMessageSenderId`, // `interlocutorMessage`.`recipientId` as `interlocutorMessageRecipientId`, // `interlocutorMessage`.`wasAuthorized` as `interlocutorMessageWasAuthorized`, // `interlocutorMessage`.`timeStamp` as `interlocutorMessageTimestamp` // // FROM `" . $this->getTableName() . "` AS `conv` // // INNER JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m` // ON ( `last_m`.`conversationId` = `conv`.`id` ) // // LEFT JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `interlocutorMessage` // ON ( `conv`.`id` = `interlocutorMessage`.conversationId AND `last_m`.`interlocutorMessageId` = `interlocutorMessage`.`id` ) // // LEFT JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `initiatorMessage` // ON ( `conv`.`id` = `initiatorMessage`.conversationId AND `last_m`.`initiatorMessageId` = `initiatorMessage`.`id` ) // // {$join} // // WHERE ( {$where} ) AND (( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `initiatorMessage`.`timeStamp`>`conv`.`initiatorDeletedTimestamp` ) ) // OR ( `conv`.`interlocutorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `interlocutorMessage`.`timeStamp`>`conv`.`interlocutorDeletedTimestamp` ) )) AND `last_m`.`id` IS NOT NULL // // GROUP BY `conv`.`id` // // ORDER BY GREATEST( COALESCE(`initiatorMessage`.`timeStamp`, 0), COALESCE(`interlocutorMessage`.`timeStamp`, 0) ) DESC // // ".$limitStr; $conversationItemList = OW::getDbo()->queryForList($query, array('kw' => '%' . $kw . '%', 'user' => $userId, 'name' => $questionName)); foreach ($conversationItemList as $i => $conversation) { $conversationItemList[$i]['timeStamp'] = (int) $conversation['lastMessageTimestamp']; $conversationItemList[$i]['lastMessageSenderId'] = $conversation['lastMessageSenderId']; $conversationItemList[$i]['isSystem'] = $conversation['lastMessageIsSystem']; $conversationItemList[$i]['text'] = $conversation['lastMessageText']; $conversationItemList[$i]['lastMessageId'] = $conversation['lastMessageId']; $conversationItemList[$i]['recipientRead'] = $conversation['lastMessageRecipientRead']; $conversationItemList[$i]['lastMessageRecipientId'] = $conversation['lastMessageRecipientId']; $conversationItemList[$i]['lastMessageWasAuthorized'] = $conversation['lastMessageWasAuthorized']; } // foreach($conversationItemList as $i => $conversation) // { // if ((int)$conversation['initiatorMessageTimestamp'] > (int)$conversation['interlocutorMessageTimestamp']) // { // $conversationItemList[$i]['timeStamp'] = (int)$conversation['initiatorMessageTimestamp']; // $conversationItemList[$i]['lastMessageSenderId'] = $conversation['initiatorMessageSenderId']; // $conversationItemList[$i]['isSystem'] = $conversation['initiatorMessageIsSystem']; // $conversationItemList[$i]['text'] = $conversation['initiatorText']; // // $conversationItemList[$i]['lastMessageId'] = $conversation['initiatorLastMessageId']; // $conversationItemList[$i]['recipientRead'] = $conversation['initiatorRecipientRead']; // $conversationItemList[$i]['lastMessageRecipientId'] = $conversation['initiatorMessageRecipientId']; // $conversationItemList[$i]['lastMessageWasAuthorized'] = $conversation['initiatorMessageWasAuthorized']; // } // else // { // $conversationItemList[$i]['timeStamp'] = (int)$conversation['interlocutorMessageTimestamp']; // $conversationItemList[$i]['lastMessageSenderId'] = $conversation['interlocutorMessageSenderId']; // $conversationItemList[$i]['isSystem'] = $conversation['interlocutorMessageIsSystem']; // $conversationItemList[$i]['text'] = $conversation['interlocutorText']; // // $conversationItemList[$i]['lastMessageId'] = $conversation['interlocutorLastMessageId']; // $conversationItemList[$i]['recipientRead'] = $conversation['interlocutorRecipientRead']; // $conversationItemList[$i]['lastMessageRecipientId'] = $conversation['interlocutorMessageRecipientId']; // $conversationItemList[$i]['lastMessageWasAuthorized'] = $conversation['interlocutorMessageWasAuthorized']; // } // } return $conversationItemList; }
<?php /** * Copyright (c) 2013, Podyachev Evgeny <*****@*****.**> * All rights reserved. * ATTENTION: This commercial software is intended for use with Oxwall Free Community Software http://www.oxwall.org/ * and is licensed under Oxwall Store Commercial License. * Full text of this license can be found at http://www.oxwall.org/store/oscl */ /** * @author Podyachev Evgeny <*****@*****.**> * @package ow_plugins.google_maps_location * @since 1.0 */ BOL_QuestionService::getInstance()->deleteSection('location'); $question = BOL_QuestionService::getInstance()->findQuestionByName('googlemap_location'); if (!empty($question)) { BOL_QuestionService::getInstance()->deleteQuestion(array($question->id)); BOL_QuestionService::getInstance()->deleteQuestionToAccountTypeByQuestionName('googlemap_location'); } BOL_QuestionDataDao::getInstance()->deleteByQuestionNamesList(array('googlemap_location'));
private function getQueryParamsForUserIdList($userId, $userIdList, $sortOrder = 'newest') { $questionService = BOL_QuestionService::getInstance(); $userService = BOL_UserService::getInstance(); $matchFields = $this->findAll(); $prefix = 'qd'; $counter = 0; $innerJoin = ''; $leftJoin = ''; $where = ''; $compatibility = ''; foreach ($matchFields as $field) { $question = $questionService->findQuestionByName($field->questionName); $matchQuestion = $questionService->findQuestionByName($field->matchQuestionName); $checkData = $questionService->getQuestionData(array($userId), array($field->matchQuestionName)); if (empty($checkData[$userId][$field->matchQuestionName])) { if ($field->required) { return array(); } } $value1 = null; if (isset($checkData[$userId][$field->matchQuestionName])) { $value1 = $checkData[$userId][$field->matchQuestionName]; } if (!empty($value1)) { if ($field->required) { $questionString = $this->prepareQuestionWhere($question, $value1, $prefix . $counter); if (!empty($questionString)) { if ($question->base == 1) { $where .= ' OR ' . $questionString; } else { $innerJoin .= "\n LEFT JOIN `" . BOL_QuestionDataDao::getInstance()->getTableName() . "` `" . $prefix . $counter . "`\n ON ( `user`.`id` = `" . $prefix . $counter . "`.`userId` AND `" . $prefix . $counter . "`.`questionName` = '" . $this->dbo->escapeString($question->name) . "' AND " . $questionString . " ) "; //$compatibility .= ' IF( `' . $prefix . $counter . '`.`questionName` IS NOT NULL, ' . MATCHMAKING_BOL_Service::MAX_COEFFICIENT . ', 0 ) +'; $counter++; } } $checkData2 = $questionService->getQuestionData(array($userId), array($field->questionName)); if (empty($checkData2[$userId][$field->questionName])) { continue; } $value2 = $checkData2[$userId][$field->questionName]; $questionString = $this->prepareQuestionWhere($matchQuestion, $value2, $prefix . $counter); if (!empty($questionString)) { if ($matchQuestion->base == 1) { $where .= ' OR ' . $questionString; } else { $innerJoin .= "\n LEFT JOIN `" . BOL_QuestionDataDao::getInstance()->getTableName() . "` `" . $prefix . $counter . "`\n ON ( `user`.`id` = `" . $prefix . $counter . "`.`userId` AND `" . $prefix . $counter . "`.`questionName` = '" . $this->dbo->escapeString($matchQuestion->name) . "' AND " . $questionString . " ) "; $counter++; } } } else { $questionString = $this->prepareQuestionWhere($question, $value1, $prefix . $counter); if (!empty($questionString)) { if ($question->base == 1) { $where .= ' OR ' . $questionString; } else { $leftJoin .= "\n LEFT JOIN `" . BOL_QuestionDataDao::getInstance()->getTableName() . "` `" . $prefix . $counter . "` ON ( `user`.`id` = `" . $prefix . $counter . "`.`userId` AND `" . $prefix . $counter . "`.`questionName` = '" . $this->dbo->escapeString($question->name) . "' AND " . $questionString . " ) "; $compatibility .= ' IF( `' . $prefix . $counter . '`.`questionName` IS NOT NULL, ' . $field->coefficient . ', 0 ) +'; $counter++; } } } } else { $compatibility .= ' ' . $field->coefficient . ' +'; } } /**/ if ($sortOrder == 'newest') { $order = ' `user`.`joinStamp` DESC, '; } else { if ($sortOrder == 'compatible') { $order = '`matches`.`compatibility` DESC , '; $listOfUserIds = $this->dbo->mergeInClause($userIdList); $where .= ' AND `user`.`id` IN ( ' . $listOfUserIds . ' ) '; } } $result = array('userId' => $userId, 'userIdList' => $userIdList, 'join' => $innerJoin . $leftJoin, 'where' => $where, 'order' => $order, 'compatibility' => '(' . substr($compatibility, 0, -1) . ')'); // pve($result); return $result; }
/** * Query for provided question values. * * @param array $questionValues * @param int $first * @param int $count * @param boolean $isAdmin * @param boolean $type * * @return String */ public function findUserIdListByQuestionValuesQuery($questionValues, $isAdmin = false, $aditionalParams = array()) { $questionNameList = array_keys($questionValues); $questions = BOL_QuestionService::getInstance()->findQuestionByNameList($questionNameList); $prefix = 'qd'; $counter = 0; $innerJoin = ''; $where = ''; foreach ($questions as $question) { if (!empty($questionValues[$question->name]) && $question->name != 'password') { if ($question->base == 1) { $where .= ' AND `user`.`' . $this->dbo->escapeString($question->name) . '` LIKE \'' . $this->dbo->escapeString($questionValues[$question->name]) . '%\''; } else { $params = array('question' => $question, 'value' => $questionValues[$question->name], 'prefix' => $prefix . $counter); $event = new BASE_CLASS_QueryBuilderEvent("base.question.search_sql", $params); OW::getEventManager()->trigger($event); $data = $event->getData(); if (!empty($data['join']) || !empty($data['where'])) { $innerJoin .= $event->getJoin(); $where .= ' AND ' . $event->getWhere(); } else { $questionString = $this->getQuestionWhereString($question, $questionValues[$question->name], $prefix . $counter); if (!empty($questionString)) { $innerJoin .= " INNER JOIN `" . BOL_QuestionDataDao::getInstance()->getTableName() . "` `" . $prefix . $counter . "`\r\n ON ( `user`.`id` = `" . $prefix . $counter . "`.`userId` AND `" . $prefix . $counter . "`.`questionName` = '" . $this->dbo->escapeString($question->name) . "' AND " . $questionString . " ) "; } } $counter++; } } } if (!empty($aditionalParams['join'])) { $innerJoin .= $aditionalParams['join']; } if (!empty($aditionalParams['where'])) { $where = $aditionalParams['where']; } if (!empty($questionValues['accountType'])) { $where .= " AND `user`.`accountType` = '" . $this->dbo->escapeString($questionValues['accountType']) . "' "; } $queryParts = $this->getUserQueryFilter("user", "id", array("method" => "BOL_UserDao::findUserIdListByQuestionValues")); $order = '`user`.`activityStamp` DESC'; if (!empty($aditionalParams['order'])) { $order = $aditionalParams['order']; } $usersTableName = "`{$this->getTableName()}`"; if (!empty($aditionalParams["limit_users_count"]) && OW_SQL_LIMIT_USERS_COUNT > 0) { $orderFieldname = self::ACTIVITY_STAMP; $usersTableName = "( SELECT * FROM {$usersTableName} ORDER BY `{$orderFieldname}` DESC LIMIT " . OW_SQL_LIMIT_USERS_COUNT . " )"; } $query = "SELECT DISTINCT `user`.id, `user`.`activityStamp` FROM {$usersTableName} `user`\r\n {$innerJoin}\r\n {$queryParts["join"]}\r\n\r\n WHERE {$queryParts["where"]} {$where}\r\n ORDER BY {$order}"; if ($isAdmin === true) { $query = "SELECT DISTINCT `user`.id FROM {$usersTableName} `user`\r\n {$innerJoin}\r\n WHERE 1 {$where}\r\n ORDER BY {$order}"; } return $query; }
public function findUsers($kw, $limit = null) { $questionName = OW::getConfig()->getValue('base', 'display_name_question'); $questionDataTable = BOL_QuestionDataDao::getInstance()->getTableName(); $limitStr = $limit === null ? '' : 'LIMIT 0, ' . intval($limit); $queryParts = BOL_UserDao::getInstance()->getUserQueryFilter("qd", "userId", array("method" => "MAILBOX_BOL_AjaxService::findUsers")); $query = 'SELECT DISTINCT qd.userId FROM ' . $questionDataTable . ' qd ' . $queryParts['join'] . ' WHERE ' . $queryParts['where'] . ' AND questionName=:name AND textValue LIKE :kw ORDER BY `textValue` ' . $limitStr; return OW::getDbo()->queryForColumnList($query, array('kw' => $kw . '%', 'name' => $questionName)); }
public function findUserIdListByQuestionValuesCustom($questionValues, $first, $count, $isAdmin = false, $aditionalParams = array()) { // echo "first->" . $first; // echo "<br>"; // echo "first->" . $count; // exit; $questionNameList = array_keys($questionValues); $questions = BOL_QuestionService::getInstance()->findQuestionByNameList($questionNameList); $prefix = 'qd'; $counter = 0; $innerJoin = ''; $where = ''; foreach ($questions as $question) { if (!empty($questionValues[$question->name]) && $question->name != 'password') { if ($question->base == 1) { $where .= ' AND `user`.`' . $this->dbo->escapeString($question->name) . '` LIKE \'' . $this->dbo->escapeString($questionValues[$question->name]) . '%\''; } else { $params = array('question' => $question, 'value' => $questionValues[$question->name], 'prefix' => $prefix . $counter); $event = new BASE_CLASS_QueryBuilderEvent("base.question.search_sql", $params); OW::getEventManager()->trigger($event); $data = $event->getData(); if (!empty($data['join']) && $question->name != "sex" || !empty($data['where'])) { $innerJoin .= $event->getJoin(); $where .= ' AND ' . $event->getWhere(); } else { $questionString = $this->getQuestionWhereString($question, $questionValues[$question->name], $prefix . $counter); if (!empty($questionString)) { $innerJoin .= " INNER JOIN `" . BOL_QuestionDataDao::getInstance()->getTableName() . "` `" . $prefix . $counter . "`\n ON ( `user`.`id` = `" . $prefix . $counter . "`.`userId` AND `" . $prefix . $counter . "`.`questionName` = '" . $this->dbo->escapeString($question->name) . "' AND " . $questionString . " ) "; } } $counter++; } } } if (!empty($aditionalParams['join'])) { $innerJoin .= $aditionalParams['join']; } if (!empty($aditionalParams['where'])) { $where = $aditionalParams['where']; } if (!empty($questionValues['accountType'])) { $where .= " AND `user`.`accountType` = '" . $this->dbo->escapeString($questionValues['accountType']) . "' "; } $queryParts = $this->getUserQueryFilter("user", "id", array("method" => "BOL_UserDao::findUserIdListByQuestionValues")); $order = '`user`.`activityStamp` DESC'; if (!empty($aditionalParams['order'])) { $order = $aditionalParams['order']; } $query = "SELECT DISTINCT `user`.id, `user`.`activityStamp` FROM `" . $this->getTableName() . "` `user`\n " . $innerJoin . "\n {$queryParts["join"]}\n\n WHERE {$queryParts["where"]} " . $where . "\n ORDER BY " . $order . "\n LIMIT :first, :count "; if ($isAdmin === true) { $query = "SELECT DISTINCT `user`.id FROM `" . $this->getTableName() . "` `user`\n " . $innerJoin . "\n WHERE 1 " . $where . "\n ORDER BY '.{$order}.'\n LIMIT :first, :count "; } return $this->dbo->queryForColumnList($query, array_merge(array('first' => $first, 'count' => $count))); }
public function findUsers($kw, $limit = null) { $questionName = OW::getConfig()->getValue('base', 'display_name_question'); $questionDataTable = BOL_QuestionDataDao::getInstance()->getTableName(); $limitStr = $limit === null ? '' : 'LIMIT 0, ' . intval($limit); $queryParts = BOL_UserDao::getInstance()->getUserQueryFilter("u", "id", array("method" => "MAILBOX_BOL_AjaxService::findUsers")); $params = array('kw' => $kw . '%'); $order = ''; if ($kw !== null) { if ($questionName == "username") { $order = ' ORDER BY `u`.`username`'; $queryParts["where"] .= " AND `u`.`username` LIKE :kw"; } else { $order = ' ORDER BY `qd`.`textValue`'; $params['questionName'] = $questionName; $queryParts["where"] .= " AND qd.questionName=:questionName AND qd.textValue LIKE :kw"; $queryParts['join'] .= ' INNER JOIN `' . BOL_QuestionDataDao::getInstance()->getTableName() . '` AS `qd` ON(`u`.`id` = `qd`.`userId`) '; } } $query = 'SELECT DISTINCT u.id FROM `' . BOL_UserDao::getInstance()->getTableName() . '` u ' . $queryParts['join'] . ' WHERE ' . $queryParts['where'] . $order . ' ' . $limitStr; return OW::getDbo()->queryForColumnList($query, $params); }