public String deleteFriendship(Profile friend1, Profile friend2) throws SQLException {
String r = "Friendship deleted!";
try {
// creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
stmt.execute(
"DELETE FROM Friends WHERE "
+ "(Friend1 IN (SELECT ProfileID FROM Profiles "
+ "WHERE Login='******') AND "
+ "Friend2 IN (SELECT ProfileID FROM Profiles "
+ "WHERE Login='******')) "
+ "OR (Friend2 IN (SELECT ProfileID FROM Profiles "
+ "WHERE Login='******') AND "
+ "Friend1 IN (SELECT ProfileID FROM Profiles "
+ "WHERE Login='******'))");
stmt.close();
// System.out.println("Requête executée");
} catch (SQLException e) {
// System.err.println(e.toString());
r = e.toString();
throw e;
}
return r;
}
public ArrayList<String> getContactList(Profile profile) {
ArrayList<String> contacts = new ArrayList<String>();
try { // creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
ResultSet result =
stmt.executeQuery(
"SELECT login FROM Profiles WHERE ProfileID IN "
+ "(SELECT DISTINCT Friend2 FROM Friends WHERE Friend1 IN "
+ "(SELECT ProfileID FROM Profiles "
+ "WHERE (Login='******' AND isAccepted=TRUE)) UNION "
+ "(SELECT DISTINCT Friend1 FROM Friends "
+ "WHERE Friend2 IN (SELECT ProfileID FROM Profiles "
+ "WHERE (Login='******' AND isAccepted=TRUE))))");
while (result.next()) { // if result of the query is not empty
contacts.add(result.getString(1));
}
stmt.close();
} catch (SQLException e) {
System.err.println(e.toString());
}
return contacts;
}
public String insertProfile(Profile profile) throws SQLException {
String r = "New profile inserted!";
if (this.getProfileID(profile) == -1) {
try {
// creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
// String login = (firstName, lastName, password)).createLogin();
if (profile.getEmail() != null) {
stmt.execute(
"INSERT INTO Profiles (FirstName, LastName, Login, Password, Email, IsAdmin) "
+ "VALUES ('"
+ profile.getFirstName()
+ "', '"
+ profile.getLastName()
+ "', "
+ "'"
+ profile.getLogin()
+ "', '"
+ profile.getPassword()
+ "', "
+ "'"
+ profile.getEmail()
+ "', "
+ profile.isAdmin()
+ ")");
} else {
stmt.execute(
"INSERT INTO Profiles (FirstName, LastName, Login, Password, IsAdmin) "
+ "VALUES ('"
+ profile.getFirstName()
+ "', '"
+ profile.getLastName()
+ "', "
+ "'"
+ profile.getLogin()
+ "', '"
+ profile.getPassword()
+ "', "
+ profile.isAdmin()
+ ") ");
}
stmt.close();
// System.out.println("Requête executée");
} catch (SQLException e) {
// System.err.println(e.toString());
r = e.toString();
throw e;
}
}
return r;
}
public void incrementNbConnections(Profile profile) {
try {
// creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
stmt.execute(
"UPDATE Profiles SET NbConnections=(SELECT NbConnections FROM Profiles "
+ "WHERE Login='******')+1 WHERE Login='******'"
+ "");
stmt.close();
// System.out.println("Requête executée");
} catch (SQLException e) {
}
}
public void setAdmin(Profile profile, boolean isAdmin) {
try {
// creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
stmt.execute(
"UPDATE Profiles SET isAdmin = " + isAdmin + " WHERE Login='******'");
stmt.close();
} catch (SQLException e) {
System.err.println(e.toString());
}
}
public String insertPendingFriendship(Profile friend1, Profile friend2) throws SQLException {
String r = "New friendship inserted!";
try {
// creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
stmt.execute(
"INSERT INTO Friends (Friend1,Friend2) VALUES "
+ "((select ProfileID from Profiles where Login='******') , (select ProfileID from Profiles where Login='******'))");
stmt.close();
// System.out.println("Requête executée");
} catch (SQLException e) {
// System.err.println(e.toString());
r = e.toString();
throw e;
}
return r;
}
public void setViewedConversation(Profile profile1, Profile profile2) {
int profile1ID = this.getProfileID(profile1);
int profile2ID = this.getProfileID(profile2);
if (profile1ID != -1 && profile2ID != -1) { // Check if profile1 and profile2 exist
try { // creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
stmt.execute(
"UPDATE Messages SET IsDelivered = true "
+ " WHERE MessageID IN "
+ "((SELECT Messages.MessageID "
+ "FROM Messages, Profiles "
+ "WHERE (Profiles.ProfileID = Messages.Sender "
+ "AND Profiles.Login ='******'"
+ "AND Messages.IsDelivered = false"
+ ") "
+ "AND (Receiver IN (SELECT ProfileID FROM Profiles "
+ "WHERE Login = '******'))) "
+ "UNION (SELECT Messages.MessageID "
+ "FROM Messages, Profiles "
+ "WHERE (Profiles.ProfileID = Messages.Sender "
+ "AND Profiles.Login ='******'"
+ "AND Messages.IsDelivered = false "
+ ") "
+ "AND (Receiver IN (SELECT ProfileID FROM Profiles "
+ "WHERE Login = '******'))) "
+ "ORDER BY MESSAGEID)");
stmt.close();
} catch (SQLException e) {
System.err.println(e.toString());
}
}
}
public String setEmail(Profile profile, String email) throws SQLException {
String r = "Email address modified!";
try {
// creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
stmt.execute(
"UPDATE Profiles SET Email='" + email + "' WHERE Login='******'");
stmt.close();
// System.out.println("Requête executée");
} catch (SQLException e) {
// System.err.println(e.toString());
r = e.toString();
throw e;
}
return r;
}
public String deleteProfile(Profile profile) throws SQLException {
String r = "Profile deleted!";
int profileID = this.getProfileID(profile);
try {
// creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
stmt.execute("DELETE FROM Friends WHERE Friend1=" + profileID + " OR Friend2=" + profileID);
stmt.execute("DELETE FROM Files WHERE Sender=" + profileID + " OR Receiver=" + profileID);
stmt.execute("DELETE FROM Messages WHERE Sender=" + profileID + " OR Receiver=" + profileID);
stmt.execute("DELETE FROM Profiles WHERE Login='******'");
stmt.close();
// System.out.println("Requête executée");
} catch (SQLException e) {
// System.err.println(e.toString());
r = e.toString();
throw e;
}
return r;
}
public String setStatus(Profile profile, Profile.Status status) throws SQLException {
String r = "Status modified!";
try {
// creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
stmt.execute(
"UPDATE Profiles SET Status="
+ status.getValue()
+ " WHERE Login='******'");
stmt.close();
// System.out.println("Requête executée");
} catch (SQLException e) {
// System.err.println(e.toString());
r = e.toString();
throw e;
}
return r;
}
public int getProfileID(Profile profile) {
int res = -1; // -1 indique que le profile n'existe pas
try {
stmt = conn.createStatement();
ResultSet result =
stmt.executeQuery(
"SELECT PROFILEID FROM Profiles WHERE " + "Login='******'");
if (result.next()) { // if result of the query is not empty
// System.out.println("This profile exist !");
// result.first(); //Take the first result because both profiles haven't the same login
res = Integer.parseInt(result.getString(1)); // The first row is PROFILEID
// System.out.println("ID of " + profile.getLogin() + " : " + res);
} else {
// System.out.println("WARNING : This profile doesn't exist ! -> return -1 value");
}
stmt.close();
} catch (SQLException e) {
System.err.println(e.toString());
}
return res;
}
public ArrayList<File> getNotDownloadedFiles(Profile profile) {
ArrayList<File> files = new ArrayList<File>();
try { // creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
ResultSet result =
stmt.executeQuery(
"SELECT Filename, Sender, Receiver FROM Files WHERE IsDelivered = false AND Receiver IN (SELECT profileID FROM Profiles WHERE Login='******')");
while (result.next()) { // if result of the query is not empty
files.add(
new File(
result.getString(1),
this.getProfile(result.getInt(2)),
this.getProfile(result.getInt(3))));
}
stmt.close();
} catch (SQLException e) {
System.err.println(e.toString());
}
return files;
}
public boolean isAdmin(Profile profile) {
boolean res = false;
try {
// creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
ResultSet result =
stmt.executeQuery(
"SELECT * FROM Profiles WHERE "
+ "Login='******' AND IsAdmin="
+ true);
if (result.next()) { // if result of the query is not empty
res = true;
}
stmt.close();
} catch (SQLException e) {
System.err.println(e.toString());
}
return res;
}
public ArrayList<Message> getUndeliveredConversation(Profile profile1, Profile profile2) {
ArrayList<Message> conversation = new ArrayList<Message>();
int profile1ID = this.getProfileID(profile1);
int profile2ID = this.getProfileID(profile2);
Profile sender = null;
Profile receiver = null;
if (profile1ID != -1 && profile2ID != -1) { // Check if profile1 and profile2 exist
try { // creates a SQL Statement object in order to execute the SQL insert command
stmt = conn.createStatement();
ResultSet result =
stmt.executeQuery(
"(SELECT Profiles.Firstname, Profiles.Lastname, Profiles.Login, "
+ "Profiles.password, "
+ "Messages.Receiver, Messages.Text, "
+ "Messages.DateMsg,Messages.TimeMsg, Messages.MessageID "
+ "FROM Messages, Profiles "
+ "WHERE (Profiles.ProfileID = Messages.Sender "
+ "AND Profiles.Login ='******'"
+ "AND Messages.IsDelivered = false) "
+ "AND (Receiver IN (SELECT ProfileID FROM Profiles "
+ "WHERE Login = '******'))) "
+ "UNION (SELECT Profiles.Firstname, "
+ "Profiles.Lastname, Profiles.Login, Profiles.password, "
+ "Messages.Receiver, Messages.Text, "
+ "Messages.DateMsg,Messages.TimeMsg, Messages.MessageID "
+ "FROM Messages, Profiles "
+ "WHERE (Profiles.ProfileID = Messages.Sender "
+ "AND Profiles.Login ='******'"
+ "AND Messages.IsDelivered = false "
+ ") "
+ "AND (Receiver IN (SELECT ProfileID FROM Profiles "
+ "WHERE Login = '******'))) "
+ "ORDER BY DATEMSG, TIMEMSG, MESSAGEID");
while (result.next()) { // if result of the query is not empty
if (result.getInt(5) == profile1ID) {
receiver = profile1;
sender = profile2;
} else if (result.getInt(5) == profile2ID) {
receiver = profile2;
sender = profile1;
} else {
return null;
}
conversation.add(
new Message(
sender,
receiver,
result.getString(6),
result.getDate(7),
result.getTime(8),
true));
}
stmt.close();
} catch (SQLException e) {
System.err.println(e.toString());
}
}
return conversation;
/*
*/
}
