"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)"
")");
//People start
QString result;
- result.append("{\"people\": [");
+ result.append("{\"people\": {");
QHash<QString, QString> tags;
- //Get friends
+ //Get all
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' "
}
}
- QStringList userIds;
+ QStringList friendIds;
QHashIterator<QString, QString> i(tags);
while (i.hasNext())
- userIds.append(i.next().key());
+ friendIds.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 "
+ result.append("\"friends\": [");
+
+ //Get friends
+ //(SELECT friend.uid2 FROM friend WHERE friend.uid1 = 613374451
+ //UNION
+ //SELECT friend.uid1 FROM friend WHERE friend.uid2 = 613374451)
+
+ QSqlQuery friendQuery(QString("SELECT user.id, user.name, user.image_url FROM user WHERE "
+ "user.id IN (") + friendIds.join(", ") + QString(") AND "
"user.latitude >= '%1' AND user.latitude < '%2' AND "
"user.longitude >= '%3' AND user.longitude < '%4'")
.arg(southWestCoordinates.latitude()).arg(northEastCoordinates.latitude())
.arg(southWestCoordinates.longitude()).arg(northEastCoordinates.longitude()));
- while (userQuery.next()) {
+ while (friendQuery.next()) {
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("\"tags\": [" + tags.value(friendQuery.value(0).toString()) + "]");
result.append("},");
}
- int lastComma = result.lastIndexOf(",");
- if (lastComma != -1)
+ int friendLastComma = result.lastIndexOf(",");
+ if (friendLastComma != -1)
+ result.remove(result.lastIndexOf(","), 1);
+
+ result.append("],");
+
+ QSqlQuery othersQuery(QString("SELECT user.id, user.name, user.image_url FROM user WHERE "
+ "user.id IN (") + friendIds.join(", ") + QString(") AND "
+ "user.latitude >= '%1' AND user.latitude < '%2' AND "
+ "user.longitude >= '%3' AND user.longitude < '%4'")
+ .arg(southWestCoordinates.latitude()).arg(northEastCoordinates.latitude())
+ .arg(southWestCoordinates.longitude()).arg(northEastCoordinates.longitude()));
+
+ while (othersQuery.next()) {
+ result.append("{");
+ result.append("\"uid\": \"" + othersQuery.value(0).toString() + "\",");
+ result.append("\"name\": \"" + othersQuery.value(1).toString() + "\",");
+ result.append("\"image_url\": \"" + othersQuery.value(2).toString() + "\",");
+ result.append("\"tags\": [" + tags.value(othersQuery.value(0).toString()) + "]");
+ result.append("},");
+ }
+
+ int friendLastComma = result.lastIndexOf(",");
+ if (friendLastComma != -1)
result.remove(result.lastIndexOf(","), 1);
+ result.append("]");
+
//People end
- 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\": [");
+ 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\": [");
+
+ while (receivedQuery.next()) {
+ 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("\"latitude\": \"" + receivedQuery.value(7).toString() + "\",");
+ result.append("\"longitude\": \"" + receivedQuery.value(8).toString() + "\"");
+ result.append("},");
+ }
+
+ 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));
- while (query.next()) {
+ result.append("\"sent\": [");
+
+ while (sentQuery.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("\"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("\"latitude\": \"" + sentQuery.value(7).toString() + "\",");
+ result.append("\"longitude\": \"" + sentQuery.value(8).toString() + "\"");
result.append("},");
}
if (lastComma != -1)
result.remove(result.lastIndexOf(","), 1);
- result.append("]}");
+ result.append("]");
+
+ result.append("}}");
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