if (m_database.isOpen()) {
QSqlQuery tagQuery;
- ret = tagQuery.exec(QString("SELECT id FROM tag WHERE name = '%1'")
+ ret = tagQuery.exec(QString("SELECT id, count FROM tag WHERE name = '%1'")
.arg(tag));
//Tag already exists
if (ret && tagQuery.next()) {
+ qWarning() << tagQuery.value(1).toInt();
+ int count = tagQuery.value(1).toInt() + 1;
+ qWarning() << count;
+ QSqlQuery tagUpdateQuery;
+ tagUpdateQuery.exec(QString("UPDATE tag SET count = '%1' WHERE id = '%2'")
+ .arg(count).arg(tagQuery.value(0).toString()));
tagId = tagQuery.value(0).toULongLong();
+ qWarning() << tagUpdateQuery.lastQuery();
}
else {
QSqlQuery tagInsertQuery;
"id INTEGER PRIMARY KEY,"
"senderid INTEGER,"
"receiverid INTEGER,"
- "read INTEGER,"
"timestamp VARCHAR(20),"
"text TEXT,"
+ "latitude REAL,"
+ "longitude REAL,"
"FOREIGN KEY(senderid) REFERENCES user(id),"
"FOREIGN KEY(receiverid) REFERENCES user(id)"
")");
{
qDebug() << __PRETTY_FUNCTION__;
+ //People start
+ QString result;
+ result.append("{\"people\": {");
+
QHash<QString, QString> tags;
+ //Get tags
QSqlQuery tagQuery(QString("SELECT DISTINCT usertag.userid, tag.name FROM usertag, tag WHERE "
- "usertag.tagid = tag.id AND usertag.userid != '%1' AND "
- "tag.name LIKE '%2'").arg(userId).arg(tag));
+ "usertag.tagid = tag.id AND usertag.userid != '%1' AND "
+ "tag.name LIKE '\%%2\%'").arg(userId).arg(tag));
while (tagQuery.next()) {
QString userId = tagQuery.value(0).toString();
while (i.hasNext())
userIds.append(i.next().key());
- QSqlQuery userQuery(QString("SELECT user.id, user.name, user.image_url FROM user WHERE "
- "user.id IN (") + userIds.join(", ") + QString(")"));
- QString result;
- result.append("{\"people\": [");
+ //Get friends
+ result.append("\"friends\": [");
+
+ QSqlQuery friendQuery(QString("SELECT user.id, user.name, user.image_url, user.latitude, "
+ "user.longitude FROM user WHERE "
+ "user.id IN (") + userIds.join(", ") + QString(") AND "
+ "user.id IN "
+ "(SELECT friend.uid2 FROM friend WHERE friend.uid1 = '%1' "
+ "UNION "
+ "SELECT friend.uid1 FROM friend WHERE friend.uid2 = '%1')")
+ .arg(userId));
- while (userQuery.next()) {
+ bool friendRemoveLast = false;
+
+ while (friendQuery.next()) {
+ friendRemoveLast = true;
result.append("{");
- result.append("\"uid\": \"" + userQuery.value(0).toString() + "\",");
- result.append("\"name\": \"" + userQuery.value(1).toString() + "\",");
- result.append("\"image_url\": \"" + userQuery.value(2).toString() + "\",");
- result.append("\"tags\": [" + tags.value(userQuery.value(0).toString()) + "]");
+ result.append("\"uid\": \"" + friendQuery.value(0).toString() + "\",");
+ result.append("\"name\": \"" + friendQuery.value(1).toString() + "\",");
+ result.append("\"image_url\": \"" + friendQuery.value(2).toString() + "\",");
+ result.append("\"latitude\": \"" + friendQuery.value(3).toString() + "\",");
+ result.append("\"longitude\": \"" + friendQuery.value(4).toString() + "\",");
+ result.append("\"tags\": [" + tags.value(friendQuery.value(0).toString()) + "]");
result.append("},");
}
- int lastComma = result.lastIndexOf(",");
- if (lastComma != -1)
- result.remove(result.lastIndexOf(","), 1);
+ if (friendRemoveLast) {
+ int friendLastComma = result.lastIndexOf(",");
+ if (friendLastComma != -1)
+ result.remove(result.lastIndexOf(","), 1);
+ }
- result.append("]}");
+ result.append("],");
+
+ //Get others
+ result.append("\"others\": [");
+
+ QSqlQuery otherQuery(QString("SELECT user.id, user.name, user.image_url FROM user WHERE "
+ "user.id IN (") + userIds.join(", ") + QString(") AND "
+ "user.id NOT IN "
+ "(SELECT friend.uid2 FROM friend WHERE friend.uid1 = '%1' "
+ "UNION "
+ "SELECT friend.uid1 FROM friend WHERE friend.uid2 = '%1')")
+ .arg(userId));
+
+ bool otherRemoveLast = false;
+
+ while (otherQuery.next()) {
+ otherRemoveLast = true;
+ result.append("{");
+ result.append("\"uid\": \"" + otherQuery.value(0).toString() + "\",");
+ result.append("\"name\": \"" + otherQuery.value(1).toString() + "\",");
+ result.append("\"image_url\": \"" + otherQuery.value(2).toString() + "\",");;
+ result.append("\"tags\": [" + tags.value(otherQuery.value(0).toString()) + "]");
+ result.append("},");
+ }
+
+ if (otherRemoveLast) {
+ int otherLastComma = result.lastIndexOf(",");
+ if (otherLastComma != -1)
+ result.remove(result.lastIndexOf(","), 1);
+ }
+
+ result.append("]");
+
+ result.append("}}");
return result.toUtf8();
}
//People start
QString result;
- result.append("{\"people\": [");
+ result.append("{\"people\": {");
QHash<QString, QString> tags;
- //Get friends
+ //Get tags
QSqlQuery tagQuery(QString("SELECT usertag.userid, tag.name FROM usertag, tag WHERE "
"usertag.tagid IN (SELECT usertag.tagid FROM usertag WHERE "
"usertag.userid = '%1') AND usertag.userid != '%2' "
while (i.hasNext())
userIds.append(i.next().key());
- QSqlQuery userQuery(QString("SELECT user.id, user.name, user.image_url FROM user WHERE "
- "user.id IN (") + userIds.join(", ") + QString(") AND "
- "user.latitude >= '%1' AND user.latitude < '%2' AND "
- "user.longitude >= '%3' AND user.longitude < '%4'")
+ //Get friends
+ result.append("\"friends\": [");
+
+ QSqlQuery friendQuery(QString("SELECT user.id, user.name, user.image_url, user.latitude, "
+ "user.longitude FROM user WHERE "
+ "user.id IN (") + userIds.join(", ") + QString(") AND "
+ "user.latitude >= '%1' AND user.latitude < '%2' AND "
+ "user.longitude >= '%3' AND user.longitude < '%4' AND user.id IN "
+ "(SELECT friend.uid2 FROM friend WHERE friend.uid1 = '%5' "
+ "UNION "
+ "SELECT friend.uid1 FROM friend WHERE friend.uid2 = '%5')")
.arg(southWestCoordinates.latitude()).arg(northEastCoordinates.latitude())
- .arg(southWestCoordinates.longitude()).arg(northEastCoordinates.longitude()));
+ .arg(southWestCoordinates.longitude()).arg(northEastCoordinates.longitude())
+ .arg(userId));
+
+ bool friendRemoveLast = false;
- while (userQuery.next()) {
+ while (friendQuery.next()) {
+ friendRemoveLast = true;
result.append("{");
- result.append("\"uid\": \"" + userQuery.value(0).toString() + "\",");
- result.append("\"name\": \"" + userQuery.value(1).toString() + "\",");
- result.append("\"image_url\": \"" + userQuery.value(2).toString() + "\",");
- result.append("\"tags\": [" + tags.value(userQuery.value(0).toString()) + "]");
+ result.append("\"uid\": \"" + friendQuery.value(0).toString() + "\",");
+ result.append("\"name\": \"" + friendQuery.value(1).toString() + "\",");
+ result.append("\"image_url\": \"" + friendQuery.value(2).toString() + "\",");
+ result.append("\"latitude\": \"" + friendQuery.value(3).toString() + "\",");
+ result.append("\"longitude\": \"" + friendQuery.value(4).toString() + "\",");
+ result.append("\"tags\": [" + tags.value(friendQuery.value(0).toString()) + "]");
result.append("},");
}
- int lastComma = result.lastIndexOf(",");
- if (lastComma != -1)
- result.remove(result.lastIndexOf(","), 1);
+ if (friendRemoveLast) {
+ int friendLastComma = result.lastIndexOf(",");
+ if (friendLastComma != -1)
+ result.remove(result.lastIndexOf(","), 1);
+ }
- //People end
- result.append("]}");
+ result.append("],");
+
+ //Get others
+ result.append("\"others\": [");
+
+ QSqlQuery otherQuery(QString("SELECT user.id, user.name, user.image_url FROM user WHERE "
+ "user.id IN (") + userIds.join(", ") + QString(") AND "
+ "user.latitude >= '%1' AND user.latitude < '%2' AND "
+ "user.longitude >= '%3' AND user.longitude < '%4' AND user.id "
+ "NOT IN "
+ "(SELECT friend.uid2 FROM friend WHERE friend.uid1 = '%5' "
+ "UNION "
+ "SELECT friend.uid1 FROM friend WHERE friend.uid2 = '%5')")
+ .arg(southWestCoordinates.latitude()).arg(northEastCoordinates.latitude())
+ .arg(southWestCoordinates.longitude()).arg(northEastCoordinates.longitude())
+ .arg(userId));
+
+ bool otherRemoveLast = false;
+
+ while (otherQuery.next()) {
+ otherRemoveLast = true;
+ result.append("{");
+ result.append("\"uid\": \"" + otherQuery.value(0).toString() + "\",");
+ result.append("\"name\": \"" + otherQuery.value(1).toString() + "\",");
+ result.append("\"image_url\": \"" + otherQuery.value(2).toString() + "\",");;
+ result.append("\"tags\": [" + tags.value(otherQuery.value(0).toString()) + "]");
+ result.append("},");
+ }
+
+ if (otherRemoveLast) {
+ int otherLastComma = result.lastIndexOf(",");
+ if (otherLastComma != -1)
+ result.remove(result.lastIndexOf(","), 1);
+ }
+
+ result.append("]");
+
+ result.append("}}");
return result.toUtf8();
}
{
qDebug() << __PRETTY_FUNCTION__;
- QSqlQuery query(QString("SELECT notification.id, notification.senderid, user.name, "
- "user.image_url, notification.timestamp, notification.text "
- "FROM notification, user WHERE notification.receiverid = '%1' "
- "AND notification.senderid = user.id").arg(userId));
+ QString result;
+ result.append("{\"messages\": {");
+
+ //Received
+ QSqlQuery receivedQuery(QString("SELECT notification.id, notification.senderid, "
+ "notification.receiverid, user.name, user.image_url, "
+ "notification.timestamp, notification.text, "
+ "notification.latitude, notification.longitude "
+ "FROM notification, user WHERE notification.receiverid = '%1' AND "
+ "notification.senderid = user.id "
+ "ORDER BY notification.timestamp DESC")
+ .arg(userId));
+
+ result.append("\"received\": [");
+
+ bool receivedRemoveLast = false;
+
+ while (receivedQuery.next()) {
+ receivedRemoveLast = true;
+ result.append("{");
+ result.append("\"id\": \"" + receivedQuery.value(0).toString() + "\",");
+ result.append("\"sender_id\": \"" + receivedQuery.value(1).toString() + "\",");
+ result.append("\"receiver_id\": \"" + receivedQuery.value(2).toString() + "\",");
+ result.append("\"sender_name\": \"" + receivedQuery.value(3).toString() + "\",");
+ result.append("\"image_url\": \"" + receivedQuery.value(4).toString() + "\",");
+ result.append("\"timestamp\": \"" + receivedQuery.value(5).toString() + "\",");
+ result.append("\"text\": \"" + receivedQuery.value(6).toString() + "\",");
+ result.append("\"address\": \"" + QString("Address 13, Oulu") + "\",");
+ result.append("\"latitude\": \"" + receivedQuery.value(7).toString() + "\",");
+ result.append("\"longitude\": \"" + receivedQuery.value(8).toString() + "\"");
+ result.append("},");
+ }
+
+ if (receivedRemoveLast) {
+ int receivedLastComma = result.lastIndexOf(",");
+ if (receivedLastComma != -1)
+ result.remove(result.lastIndexOf(","), 1);
+ }
+
+ result.append("],");
+
+ //Sent
+ QSqlQuery sentQuery(QString("SELECT notification.id, notification.senderid, "
+ "notification.receiverid, user.name, user.image_url, "
+ "notification.timestamp, notification.text, "
+ "notification.latitude, notification.longitude "
+ "FROM notification, user WHERE notification.senderid = '%1' AND "
+ "notification.receiverid = user.id "
+ "ORDER BY notification.timestamp DESC")
+ .arg(userId));
+
+ result.append("\"sent\": [");
+
+ bool sentRemoveLast = false;
+
+ while (sentQuery.next()) {
+ sentRemoveLast = true;
+ result.append("{");
+ result.append("\"id\": \"" + sentQuery.value(0).toString() + "\",");
+ result.append("\"sender_id\": \"" + sentQuery.value(1).toString() + "\",");
+ result.append("\"receiver_id\": \"" + sentQuery.value(2).toString() + "\",");
+ result.append("\"sender_name\": \"" + sentQuery.value(3).toString() + "\",");
+ result.append("\"image_url\": \"" + sentQuery.value(4).toString() + "\",");
+ result.append("\"timestamp\": \"" + sentQuery.value(5).toString() + "\",");
+ result.append("\"text\": \"" + sentQuery.value(6).toString() + "\",");
+ result.append("\"address\": \"" + QString("Address 13, Oulu") + "\",");
+ result.append("\"latitude\": \"" + sentQuery.value(7).toString() + "\",");
+ result.append("\"longitude\": \"" + sentQuery.value(8).toString() + "\"");
+ result.append("},");
+ }
+
+ if (sentRemoveLast) {
+ int lastComma = result.lastIndexOf(",");
+ if (lastComma != -1)
+ result.remove(result.lastIndexOf(","), 1);
+ }
+
+ result.append("]");
+
+ result.append("}}");
+
+ return result.toUtf8();
+}
+
+QByteArray Database::getPopularTags()
+{
+ qDebug() << __PRETTY_FUNCTION__;
+
+ QSqlQuery query(QString("SELECT tag.id, tag.name FROM tag ORDER BY count DESC LIMIT 10"));
QString result;
- result.append("{\"messages\": [");
+ result.append("{\"popular_tags\": [");
while (query.next()) {
result.append("{");
result.append("\"id\": \"" + query.value(0).toString() + "\",");
- result.append("\"sender_id\": \"" + query.value(1).toString() + "\",");
- result.append("\"sender_name\": \"" + query.value(2).toString() + "\",");
- result.append("\"image_url\": \"" + query.value(3).toString() + "\",");
- result.append("\"timestamp\": \"" + query.value(4).toString() + "\",");
- result.append("\"text\": \"" + query.value(5).toString() + "\"");
+ result.append("\"name\": \"" + query.value(1).toString() + "\"");
result.append("},");
}
result.remove(result.lastIndexOf(","), 1);
result.append("]}");
-
+ qWarning() << result;
return result.toUtf8();
}
QSqlQuery removeMessageQuery;
return removeMessageQuery.exec(QString("DELETE FROM notification WHERE "
- "notification.receiverid = '%1' AND "
+ "(notification.receiverid = '%1' OR "
+ "notification.senderid = '%1') AND "
"notification.id = '%2'").arg(userId).arg(id));
}
QSqlQuery sendMessageQuery;
- return sendMessageQuery.exec(QString("INSERT INTO notification VALUES("
- "NULL, '%1', '%2', strftime('%s','now'), '%3', '%4', '%5')")
+ bool returnValue;
+
+ if (coordinates.isValid()) {
+ returnValue = sendMessageQuery.exec(QString("INSERT INTO notification VALUES("
+ "NULL, '%1', '%2', strftime('%s','now'), '%3', '%4', '%5')")
.arg(senderId).arg(receiverId).arg(message).arg(coordinates.latitude()).
arg(coordinates.longitude()));
+ } else {
+ returnValue = sendMessageQuery.exec(QString("INSERT INTO notification VALUES("
+ "NULL, '%1', '%2', strftime('%s','now'), '%3', NULL, NULL)")
+ .arg(senderId).arg(receiverId).arg(message));
+ }
+
+ return returnValue;
}
projects / situare / blobdiff