package serwer;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
/*
* Klasa implementujaca operacje obslugujace polecenia przychodzacych do serwera
* Operacje jako argumenty wejsciowe otrzymuje wartosci dostarczone w poleceniu
* Operacje wykonuja zapytania do bazy danych
* nastepnie zwraca odpowiedz jako true/false lub pobrane z bazy rekordy
*
* @author Marcin Celejewski
* @version v1.0
* @see Serwer.java
* @see FILReply.java
* @see FILReplyElement.java
* @param c polaczenie z baza danych
* @param url url bazy danych
* @param user uzytkownik laczacy sie z baza danych
* @param password haslo sluzace do laczenia sie z baza danych
* @param logfile sciezka do zapisania loga jednego polaczenia
*
*/
public class Operations {
// private final String url = "jdbc:postgresql://192.168.1.10/cogdziekiedy";
private final String url
= "jdbc:postgresql://127.0.0.1/postgres"; private final String user
= "serwer"; private final String password
= "bananapi";
/*
* Konstruktor tworzacy nazwe kolejnego loga
* @param ip ip uzytkownika ktory wyslal polecenie
* @return nothing
*
*/
public Operations
(String ip
) {
System.
out.
println("D:\\logiserwera\\" + dateFormat.
format(date
) + "_" + ip
+ ".log"); logfile = "D:\\logiserwera\\" + dateFormat.format(date) + "_" + ip + ".log";
}
/*
* Metoda zapisujaca logi do pliku
* @param s string ktory chcemy zapisac do logow
* @return nothing
* @exception IOException On input error.
* @see IOException
*
*/
try {
fw.write("\n" + s + "\n");
fw.close();
e.printStackTrace();
}
}
/*
* Metoda sluzaca do polaczenia sie z baza danych
* @param nothing
* @return ifconnected
* @exception Exception
* @see Exception
*
*/
public boolean connect() {
try {
Class.forName("org.postgresql.Driver");
e.printStackTrace();
log("Cannot connect to databsase. " + e.toString());
return false;
}
log("Opened database successfully");
return true;
}
/*
* Metoda obsluzaca logowanie sie do systemu
* @param login login uzytkownika
* @param password haslo uzytkownika
* @return iflogged
* @exception SQLException e
* @see SQLException e
*
*/
boolean correct = false;
try {
PreparedStatement ps
= c.
prepareStatement("SELECT * FROM Moderators WHERE login = ? AND password = ?;"); ps.setString(1, login);
ps.
setInt(2,
Integer.
parseInt(password
)); if (rs.next()) {
correct = true;
}
rs.close();
ps.close();
c.close();
log("Loged in succussfully");
log("Cannot execute this login statment. " + e.toString());
}
return correct;
}
/*
* Metoda pobierajaca okreslone lokacje z bazy danych
* @param latitude szerokosc geograficzna uzytkownika
* @param longitude dlugosc geograficzna uzytkownika
* @param radius promien w jakim nalezy szukac lokacji
* @param tags tablica tagow z lokacjami, ktore nalezy wyszukac
* @return tosend
* @exception SQLException e
* @see SQLException e
* @see FILReply.java
*/
public FILReply findPlace
(double latitude,
double longitude,
double radius,
String[] tags
) { List<Long> tags_ID = new ArrayList<Long>();
List<FILReplyElement> result = new ArrayList<FILReplyElement>();
FILReply tosend = new FILReply();
if (tags.length > 0) {
tags_ID = findTags(tags);
}
double deltaLat = radius * 0.00001451;
double deltaLong = radius * 0.00000878;
String query
= "SELECT Location_ID, Location_Name, Latitude_cord, Longitude_cord, Tags, Description " + "FROM Location " + "WHERE (Latitude_cord BETWEEN ? AND ?) AND (Longitude_cord BETWEEN ? AND ?)";
if (tags_ID.size() > 0) {
query
= query.
concat(" AND (Tags LIKE '%|" + Long.
toString(tags_ID.
get(0)) + "|%'"); if (tags_ID.size() > 1) {
for (int i = 1; i < tags_ID.size(); i++) {
query
= query.
concat(" OR Tags LIKE '%|" + Long.
toString(tags_ID.
get(i
)) + "|%'"); }
}
query = query.concat(") GROUP BY Location_ID;");
} else {
query = query.concat(" GROUP BY Location_ID;");
}
try {
ps = c.prepareStatement(query);
ps.setDouble(1, latitude - deltaLat);
ps.setDouble(2, latitude + deltaLat);
ps.setDouble(3, longitude - deltaLong);
ps.setDouble(4, longitude + deltaLong);
rs = ps.executeQuery();
log("findPlace " + e.toString());
}
try {
while (rs.next()) {
FILReplyElement t = new FILReplyElement();
t.name = rs.getString("Location_Name");
t.latitude = rs.getDouble("Latitude_cord");
t.longitude = rs.getDouble("Longitude_cord");
t.description = rs.getString("description");
result.add(t);
}
log("findPlace " + e.toString());
}
FILReplyElement[] tab = new FILReplyElement[result.size()];
for (int i = 0; i < result.size(); i++) {
tab[i] = result.get(i);
}
tosend.arr = tab;
try {
rs.close();
ps.close();
c.close();
log("findPlace " + e.toString());
}
log("findedPlace successfully");
return tosend;
}
/*
* Metoda pobierajaca okreslone wydarzenia z bazy danych
* @param latitude szerokosc geograficzna uzytkownika
* @param longitude dlugosc geograficzna uzytkownika
* @param radius promien w jakim nalezy szukac wydarzen
* @param tags tablica tagow z wydrzeniami, ktore nalezy wyszukac
* @return result
* @exception SQLException e
* @see SQLException e
* @see FILReplyElement.java
*/
public FILReplyElement
[] findEvent
(double latitude,
double longitude,
double radius,
String[] tags
) {
List<Long> tags_ID = new ArrayList<Long>();
List<FILReplyElement> result = new ArrayList<FILReplyElement>();
if (tags.length > 0) {
tags_ID = findTags(tags);
}
double deltaLat = radius * 0.00001451;
double deltaLong = radius * 0.00000878;
try {
String query
= "SELECT Event_ID, Event_Name, Latitude_cord, Longitude_cord, Date, Time, Tags, Description " + "FROM Events "
+ "WHERE Time >= ? AND Date == ? AND (Latitude_cord BETWEEN ? AND ?) AND (Longitude_cord BETWEEN ? AND ?)";
if (tags_ID.size() > 0) {
query
= query.
concat(" AND (Tags LIKE '%|" + Long.
toString(tags_ID.
get(0)) + "|%'"); if (tags_ID.size() > 1) {
for (int i = 1; i < tags_ID.size(); i++) {
query
= query.
concat(" OR Tags LIKE '%|" + Long.
toString(tags_ID.
get(i
)) + "|%'"); }
}
query = query.concat(") GROUP BY Event_ID;");
} else {
query = query.concat(" GROUP BY Event_ID;");
}
ps.setString(1, time.format(cal.getTime()));
ps.setString(2, date.format(cal.getTime()));
ps.
setString(3,
Double.
toString(latitude
- deltaLat
)); ps.
setString(4,
Double.
toString(latitude
+ deltaLat
)); ps.
setString(5,
Double.
toString(longitude
- deltaLong
)); ps.
setString(6,
Double.
toString(longitude
+ deltaLong
));
while (rs.next()) {
FILReplyElement t = new FILReplyElement();
t.name = rs.getString("Event_Name");
t.latitude = rs.getDouble("Latitude_cord");
t.longitude = rs.getDouble("Longitude_cord");
t.date = rs.getString("Date");
t.time = rs.getString("Time");
t.description = rs.getString("Description");
result.add(t);
}
rs.close();
ps.close();
c.close();
log("Cannot execute this find event statment " + e.toString());
}
log("findedEvent successfully");
return result.toArray(new FILReplyElement[result.size()]);
}
/*
* Metoda uzupelniajaca baze danych o przeslana lokacje
* @param name nazwa lokacji
* @param latitude szerokosc geograficzna lokacji
* @param longitude dlugosc geograficzna lokacji
* @param tags tablica tagow opisujaca lokacje
* @param owner wlasciciel lokacji
* @param description opis lokacji
* @return correct
* @exception SQLException e
* @see SQLException e
*/
System.
out.
println("addplane\n"); boolean correct = false;
try {
for (int i = 0; i < tags.length; i++) {
stringtags
+= Long.
toString(checkTag
(tags
[i
])) + "|"; }
"INSERT INTO Location (Location_Name, Latitude_cord, Longitude_cord, Tags, Owner, Description)"
+ " VALUES (?,?,?,?,?,?);");
ps.setString(1, name);
ps.
setFloat(2,
Float.
parseFloat(latitude
)); ps.
setFloat(3,
Float.
parseFloat(longitude
)); ps.setString(4, stringtags);
ps.setString(5, owner);
ps.setString(6, description);
ps.executeUpdate();
correct = true;
ps.close();
c.close();
log("Cannot execute this addPlace event statment " + e.toString());
}
log("addedPlace succussfully");
return correct;
}
/*
* Metoda uzupelniajaca baze danych o przeslane wydarzenie
* @param name nazwa lokacji
* @param latitude szerokosc geograficzna lokacji
* @param longitude dlugosc geograficzna lokacji
* @param tags tablica tagow opisujaca lokacje
* @param owner wlasciciel lokacji
* @param description opis lokacji
* @param date data wydarzenia
* @param time godzina wydarzenia
* @return correct
* @exception SQLException e
* @see SQLException e
*/
boolean correct = false;
try {
for (int i = 0; i < tags.length; i++) {
stringtags
+= Long.
toString(checkTag
(tags
[i
])) + "|"; }
"INSERT INTO Events (Event_Name, Latitude_cord, Longitude_cord, Date, Time, Tags, Owner, Description)"
+ " VALUES (?,?,?,?,?,?,?,?);");
ps.setString(1, name);
ps.setDouble(2, latitude);
ps.setDouble(3, longitude);
ps.setString(4, date);
ps.setString(5, time);
ps.setString(6, stringtags);
ps.setString(7, owner);
ps.setString(8, description);
ps.executeUpdate();
ps.close();
c.close();
correct = true;
log("Cannot add event " + e.toString());
}
log("addedEvent succussfully");
return correct;
}
/*
* Metoda wyszukujaca id tagow
* @param tags tablica tagow
* @return tags_ID
* @exception SQLException e
* @see SQLException e
*/
protected List
<Long
> findTags
(String[] tags
) { List<Long> tags_ID = new ArrayList<Long>();
try {
String query
= "SELECT * FROM Tags WHERE "; for (int i = 0; i < tags.length - 1; i++) {
query = query.concat(" Tag LIKE '" + tags[i] + "' OR");
}
query = query.concat(" Tag LIKE '" + tags[tags.length - 1] + "' GROUP BY ID;");
while (rs.next()) {
long a = rs.getLong("ID");
tags_ID.add(a);
}
rs.close();
ps.close();
log("Cannot find tags " + e.toString());
}
log("findedTags successfully");
return tags_ID;
}
/*
* Metoda wyszukujaca wszystkie lokacje danego wlasciciela
* @param login login wlasciciela
* @return result
* @exception SQLException e
* @see SQLException e
* @see FILReplyElement.java
*/
public FILReplyElement
[] findOwnLocation
(String login
) { // potrzebne zabezpieczenie if login = owner dopiero wykonuj.
List<FILReplyElement> result = new ArrayList<FILReplyElement>();
try {
String query
= "SELECT * FROM Location WHERE Owner = ? GROUP BY Location_ID";
ps.setString(1, login);
while (rs.next()) {
FILReplyElement tosend = new FILReplyElement();
tosend.location_id = rs.getInt("Location_ID");
tosend.name = rs.getString("Location_Name");
tosend.latitude = rs.getDouble("Latitude_cord");
tosend.longitude = rs.getDouble("Longitude_cord");
tosend.tags = rs.getString("Tags");
tosend.owner = login;
tosend.description = rs.getString("Description");
result.add(tosend);
}
rs.close();
ps.close();
c.close();
log("Cannot find own locations " + e.toString());
}
log("findedOwnLocations successfully");
return result.toArray(new FILReplyElement[result.size()]);
}
/*
* Metoda wyszukujaca czy dany tag nie znajduje sie juz w bazie
* @param tag opis taga
* @return id
* @exception SQLException e
* @see SQLException e
*/
public long checkTag
(String tag
) { String query
= "SELECT * FROM Tags WHERE Tag=?"; long id;
try {
ps.setString(1, tag);
if (rs.next()) {
id = rs.getLong("ID");
log("chceckedTag successfully");
ps.close();
rs.close();
return id;
} else {
ps.close();
rs.close();
log("didnt find tag");
query = "INSERT into Tags (Tag) values(?);";
ps2.setString(1, tag);
ps2.executeUpdate();
ps2.close();
query = "SELECT * FROM Tags WHERE Tag = ?;";
ps2 = c.prepareStatement(query);
ps2.setString(1, tag);
rs = ps2.executeQuery();
if (rs.next()) {
id = rs.getLong("ID");
log("chceckedTag successfully");
ps2.close();
rs.close();
return id;
} else {
ps2.close();
rs.close();
log("straszny blad check tags");
return 0;
}
}
log("Cannot check Tag " + e.toString());
}
return 0;
}
}
