/**
* Returns an instance of class (singleton pattern implementation).
*
* @return BOL_QuestionDataDao
*/
public static function getInstance()
{
if (self::$classInstance === null) {
self::$classInstance = new self();
}
return self::$classInstance;
}
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 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));
}
public function deleteQuestionDataByUserId($userId)
{
$this->dataDao->deleteByUserId($userId);
}
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 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 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);
}
/**
* 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;
}
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;
}
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 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);
}
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;
}
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);
}
