package efrei.ngo.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import efrei.ngo.entity.Floor;
import efrei.ngo.entity.ItemDetail;
import efrei.ngo.entity.ItemSimple;
import efrei.ngo.entity.MsgPiece;
import efrei.ngo.entity.ResourcePiece;
import efrei.ngo.entity.User;
public class DatabaseAccess {
static String driver = "com.mysql.jdbc.Driver";
// URL indicate which database DBSansFrontiere
static String url = "jdbc:mysql://127.0.0.1:3306/DBSansFrontiere";
// MySQL UserName
static String user = "root";
// Java---MySQL Password
static String password = null;
// For the connection
static Connection conn;
// control bit for the static DB
static boolean valid = false;
/*
* function used to connect the database
*/
static public void connectToDB() {
try {
// Load in driver
Class.forName(driver);
// Connecting DB
conn = DriverManager.getConnection(url, user, password);
if (!conn.isClosed()) {
//System.out.println("Succeeded connecting to the Database!");
valid=true;
} else {
//System.out.println("Failed connecting to the Database!");
valid=false;
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
/*
* The function used to end the connection between the database
*/
static public boolean endConnectionToDB(){
try{
if(valid){
//conn.close();
valid=false;
//System.out.println("Disconnect to the DB");
return true;
}else{
//System.out.println("Fail to disconnect to the DB");
return false;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
public static boolean dbLoginViaUsername(String username, String psw,
User requestOne) {
try{
Statement statement = conn.createStatement();
String sql = "SELECT *FROM PersonalInformation"
+" WHERE usename='"
+username+"'AND psw='"
+psw+"'";
ResultSet rs=statement.executeQuery(sql);
if(rs.next()){
requestOne.setU_id(rs.getInt("id_pi"));
requestOne.setUsername(rs.getString("usename"));
requestOne.setEmail(rs.getString("email"));
requestOne.setAuth(rs.getString("auth").charAt(0));
statement.close();
return true;
}else{
statement.close();
return false;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
public static boolean dbLoginViaEmail(String email, String psw,
User requestOne) {
try{
Statement statement = conn.createStatement();
String sql = "SELECT *FROM PersonalInformation"
+" WHERE email='"
+email+"'AND psw='"
+psw+"'";
ResultSet rs=statement.executeQuery(sql);
if(rs.next()){
requestOne.setUsername(rs.getString("usename"));
requestOne.setEmail(rs.getString("email"));
requestOne.setAuth(rs.getString("auth").charAt(0));
statement.close();
return true;
}else{
statement.close();
return false;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
public static boolean dbCheckUsernameUni(String username) {
try{
Statement statement = conn.createStatement();
String sql = "SELECT *FROM PersonalInformation"
+" WHERE usename='"
+username+"'";
if(statement.executeQuery(sql).next()){
statement.close();
return false;
}else{
statement.close();
return true;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
public static boolean dbCheckEmailUni(String email) {
try{
Statement statement = conn.createStatement();
String sql = "SELECT *FROM PersonalInformation"
+" WHERE email='"
+email+"'";
if(statement.executeQuery(sql).next()){
statement.close();
return false;
}else{
statement.close();
return true;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
/*
* Store personal information into DB
*/
public static boolean dbStorePIIntoDB(String email, String username,
String psw, String sex, String career, String bday,
boolean isInternal) {
try{
Statement statement = conn.createStatement();
String sql = "INSERT INTO PersonalInformation"
+"(email,usename,psw,sex,career,bday,isInternal)"
+" VALUES ('"
+email+"','"
+username+"','"
+psw+"','"
+sex+"','"
+career+"',"
+bday+","
+ isInternal +")";
if(statement.executeUpdate(sql)==1){
//conn.commit();
statement.close();
return true;
}else{
//conn.commit();
statement.close();
return false;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
/*
* get the list of the whole post, SimpleItem
*/
public static ArrayList<ItemSimple> dbGetPostList() {
ArrayList<ItemSimple> itemList = new ArrayList<ItemSimple>();
try{
Statement statement = conn.createStatement();
String sql = "SELECT postID,titleName,description,postDate,useName FROM forumitemlist,PersonalInformation WHERE forumitemlist.owner=PersonalInformation.id_pi";
ResultSet rs=statement.executeQuery(sql);
while(rs.next()){
itemList.add(new ItemSimple(rs.getString("postID"), rs.getString("titleName"), rs.getString("description"),
efrei.ngo.util.GeneralUnil.changeDateToGregorianCalendar(rs.getDate("postDate")), rs.getString("useName")));
}
statement.close();
}catch(Exception ex){
ex.printStackTrace();
}
return itemList;
}
/*
* store a single post into the Database
*/
public static boolean dbStorePostInDB(String titleName,String description,String context,int owner){
try{
Statement statement = conn.createStatement();
String sql = "INSERT INTO forumitemlist"
+"(titleName,description,postDate,owner,content)"
+" VALUES ('"
+titleName+"','"
+description+"','"
+new java.sql.Date(new Date().getTime())+"',"
+owner+",'"
+context
+"')";
if(statement.executeUpdate(sql)==1){
//conn.commit();
statement.close();
return true;
}else{
//conn.commit();
statement.close();
return false;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
/*
* return a single detail of the post; content included.
*/
public static ItemDetail dbGetPostD(String postID) {
ItemDetail result=null;
try{
Statement statement = conn.createStatement();
String sql = "SELECT postID,titleName,description,postDate,useName,content FROM forumitemlist,PersonalInformation WHERE forumitemlist.owner=PersonalInformation.id_pi AND postID="+postID;
ResultSet rs=statement.executeQuery(sql);
if(rs.next()){
result = new ItemDetail(rs.getString("postID"), rs.getString("titleName"), rs.getString("description"),
efrei.ngo.util.GeneralUnil.changeDateToGregorianCalendar(rs.getDate("postDate")), rs.getString("useName"),rs.getString("content"));
}
statement.close();
}catch(Exception ex){
ex.printStackTrace();
}
return result;
}
/*
* get a list of floor according to a give id of the post
*/
public static ArrayList<Floor> dbGetFloorList(String postID) {
ArrayList<Floor> floorList = new ArrayList<Floor>();
try{
Statement statement = conn.createStatement();
String sql = "SELECT * FROM PostFloorList WHERE postID="+postID;
ResultSet rs=statement.executeQuery(sql);
Floor temp4add;
while(rs.next()){
temp4add=new Floor(rs.getString("postID"), rs.getString("floorID"), rs.getString("context"),
efrei.ngo.util.GeneralUnil.changeDateToGregorianCalendar(rs.getDate("postDate")), new User(rs.getInt("owner")),new User(rs.getInt("toSomebody")));
floorList.add(temp4add);
}
statement.close();
}catch(Exception ex){
ex.printStackTrace();
}
return floorList;
}
/*
* Store a floor into DB
*/
public static boolean dbStoreFoorIntoDB(int postID,String context,GregorianCalendar postDate,int owner,int toSomebody){
try{
Statement statement=conn.createStatement();
String sql = "INSERT INTO PostFloorList"
+"(postID,context,postDate,owner,toSomebody)"
+" VALUES ("
+postID+",'"
+context+"','"
+new java.sql.Date(new Date().getTime())+"',"
+owner+","
+toSomebody
+")";
if(statement.executeUpdate(sql)==1){
//conn.commit();
statement.close();
return true;
}else{
//conn.commit();
statement.close();
return false;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
/*
* to get a corresponding name to a id
*/
public static String dbGetNameViaID(int id_pi){
try{
Statement statement=conn.createStatement();
String sql ="SELECT usename FROM PersonalInformation WHERE id_pi="+id_pi;
ResultSet rs=statement.executeQuery(sql);
if(rs.next()){
return rs.getString("usename");
}else{
return null;
}
}catch(Exception ex){
ex.printStackTrace();
return null;
}
}
/*
* to get the id from the name
*/
public static int dbGetIDViaName(String name){
try{
Statement statement=conn.createStatement();
String sql ="SELECT id_pi FROM PersonalInformation WHERE usename='"+name+"'";
ResultSet rs=statement.executeQuery(sql);
if(rs.next()){
return rs.getInt("id_pi");
}else{
return -1;
}
}catch(Exception ex){
ex.printStackTrace();
return -1;
}
}
public static ArrayList<MsgPiece> dbCheckNewIM(){
ArrayList<MsgPiece> list=new ArrayList<MsgPiece>();
try{
Statement statement=conn.createStatement();
String sql ="SELECT * FROM im WHERE isRead='f'";
ResultSet rs=statement.executeQuery(sql);
MsgPiece onePiece;
while(rs.next()){
onePiece=new MsgPiece(rs.getInt("id_im"), rs.getInt("pFrom"), rs.getInt("pTo"), rs.getString("content"), efrei.ngo.util.GeneralUnil.changeDateToGregorianCalendar(rs.getTime("sTime")));
list.add(onePiece);
dbUpdateIsReadMark(onePiece.getId_im());
}
}catch(Exception ex){
ex.printStackTrace();
return null;
}
return list;
}
/*
* Mark the record isRead
*/
private static boolean dbUpdateIsReadMark(int id_im){
try{
Statement statement=conn.createStatement();
String sql ="UPDATE im SET isRead='t' WHERE id_im="+id_im;
if(statement.executeUpdate(sql)==1){
return true;
}else{
return false;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
/*
* Store a piece of im into DB
*/
public static boolean dbStoreIMIntoDB(MsgPiece onePiece) {
try{
Statement statement = conn.createStatement();
String sql = "INSERT INTO im"
+"(pFrom,pTo,content,sTime,isRead)"
+" VALUES (" +onePiece.getpFrom()+
"," +onePiece.getpTo()+
",'"+onePiece.getContent()+
"','" +new java.sql.Date(new Date().getTime())+
"','f'" +
")";
if(statement.executeUpdate(sql)==1){
//conn.commit();
statement.close();
return true;
}else{
//conn.commit();
statement.close();
return false;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
public static ArrayList<String> dbGetCountryListViaContinent(String continent){
ArrayList<String> arrCountry=new ArrayList<String>();
try{
Statement statement=conn.createStatement();
String sql ="SELECT DISTINCT country FROM rsLocation WHERE continent='"+continent+"'";
ResultSet rs=statement.executeQuery(sql);
while(rs.next()){
arrCountry.add(rs.getString("country"));
}
statement.close();
}catch(Exception ex){
ex.printStackTrace();
return null;
}
return arrCountry;
}
/*
* Insert record into DB
*/
public static boolean dbInsertResourceRecord(int id_rs,int id_pi,double quantity){
try{
Statement statement2 = conn.createStatement();
String sql2 = "INSERT INTO rsRecord"
+"(date,id_rs,id_pi,quantity)"
+" VALUES ('"+new java.sql.Date(new Date().getTime())+"',"+id_rs+","+id_pi+","+quantity+")";
if(statement2.executeUpdate(sql2)==1){
//conn.commit();
statement2.close();
return true;
}else{
//conn.commit();
statement2.close();
return false;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
/*
* Update the resource
*/
public static boolean dbUpdateResource(int id_rs,double quantity, int id_pi){
try{
Statement statement=conn.createStatement();
String sql ="UPDATE rslist SET quantity=quantity+"+quantity+" WHERE id_rs="+id_rs;
if(statement.executeUpdate(sql)==1){
statement.close();
return DatabaseAccess.dbInsertResourceRecord(id_rs, id_pi, quantity);
}else{
statement.close();
return false;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
public static boolean dbInsertResource(String diagName,String diagType,String diaglocation,String diagCity,double diagQuan,String diagUnit,String diagDate, int id_pi){
try{
int id_type=DatabaseAccess.dbGetResourceType(diagType, diagName);
int id_location=DatabaseAccess.dbGetResourceLocation(diaglocation, diagCity);
Statement statement=conn.createStatement();
String sql = "INSERT INTO rsList"
+"(id_type,id_location,quantity,unit,lu_date)"
+" VALUES ("+id_type+","+id_location+","+diagQuan+",'"+diagUnit+"','"+new java.sql.Date(new Date(diagDate).getTime())+"')";
if(statement.executeUpdate(sql)==1){
//conn.commit();
statement.close();
int id_rs=DatabaseAccess.dbGetInsertResourceID(id_type, id_location, diagQuan, diagUnit, new java.sql.Date(new Date(diagDate).getTime()));
return DatabaseAccess.dbInsertResourceRecord(id_rs, id_pi, diagQuan);
}else{
//conn.commit();
statement.close();
return false;
}
}catch(Exception ex){
ex.printStackTrace();
return false;
}
}
private static int dbGetInsertResourceID(int id_type,int id_location,double quantity,String unit,java.sql.Date lu_date){
try{
Statement statement=conn.createStatement();
String sql = "SELECT id_rs FROM rslist WHERE id_type="+id_type+" AND id_location="+id_location+" AND quantity="+quantity+" AND unit='"+unit+"' AND lu_date='"+lu_date+"'";
ResultSet rs=statement.executeQuery(sql);
if(rs.next()){
int temp=rs.getInt("id_rs");
statement.close();
return temp;
}else{
statement.close();
return -1;
}
}catch(Exception ex){
ex.printStackTrace();
return -1;
}
}
/*
* get the id of the location and city group OR insert a new one
*/
public static int dbGetResourceLocation(String diaglocation, String diagCity){
try{
Statement statement=conn.createStatement();
String sql = "SELECT id_location FROM rsLocation WHERE country='"+diaglocation+"' AND city='"+diagCity+"'";
ResultSet rs=statement.executeQuery(sql);
if(rs.next()){
int temp=rs.getInt("id_location");
statement.close();
return temp;
}else{
sql = "SELECT continent FROM rsLocation WHERE country='"+diaglocation+"'";
rs=statement.executeQuery(sql);
String continent=null;
if(rs.next()){
continent=rs.getString("continent");
}
sql = "INSERT INTO rsLocation (continent, country, city) VALUES ('"+continent+"','"+diaglocation+"','"+diagCity+"')";
if(statement.executeUpdate(sql)==1){
statement.close();
return dbGetResourceLocation(diaglocation,diagCity);
}else{
statement.close();
return -1;
}
}
}catch(Exception ex){
ex.printStackTrace();
return -1;
}
}
/*
* Get the id of the type name couple OR insert a new one
*/
public static int dbGetResourceType(String diagType, String diagName){
try{
Statement statement=conn.createStatement();
String sql = "SELECT id_type FROM rstypelist WHERE type='"+diagType+"' AND name='"+diagName+"'";
ResultSet rs=statement.executeQuery(sql);
if(rs.next()){
int temp=rs.getInt("id_type");
statement.close();
return temp;
}else{
sql = "INSERT INTO rstypelist (type, name) VALUES ('"+diagType+"','"+diagName+"')";
if(statement.executeUpdate(sql)==1){
statement.close();
return dbGetResourceType(diagType, diagName);
}else{
statement.close();
return -1;
}
}
}catch(Exception ex){
ex.printStackTrace();
return -1;
}
}
public static ArrayList<ResourcePiece> dbGetRSList(String sql){
ArrayList<ResourcePiece> list=new ArrayList<ResourcePiece>();
try{
Statement statement=conn.createStatement();
ResultSet rs=statement.executeQuery(sql);
ResourcePiece oneRS;
while(rs.next()){
oneRS=new ResourcePiece(rs.getInt("id_rs"), rs.getString("name"), rs.getString("type"), rs.getString("country"),
rs.getString("city"), rs.getDouble("quantity"), rs.getString("unit"), rs.getDate("lu_date"));
list.add(oneRS);
}
statement.close();
}catch(Exception ex){
ex.printStackTrace();
}
return list;
}
public static ArrayList<String> dbGetTypeList(){
ArrayList<String> arrType=new ArrayList<String>();
try{
Statement statement=conn.createStatement();
String sql ="SELECT DISTINCT type FROM rstypelist";
ResultSet rs=statement.executeQuery(sql);
while(rs.next()){
arrType.add(rs.getString("type"));
}
statement.close();
}catch(Exception ex){
ex.printStackTrace();
return null;
}
return arrType;
}
public static ArrayList<String> dbGetContinentList(){
ArrayList<String> arrType=new ArrayList<String>();
try{
Statement statement=conn.createStatement();
String sql ="SELECT DISTINCT continent FROM rslocation";
ResultSet rs=statement.executeQuery(sql);
while(rs.next()){
arrType.add(rs.getString("continent"));
}
statement.close();
}catch(Exception ex){
ex.printStackTrace();
return null;
}
return arrType;
}
public static ArrayList<User> dbGetFriendList(){
ArrayList<User> arrUser=new ArrayList<User>();
try{
Statement statement=conn.createStatement();
String sql ="SELECT usename,id_pi FROM PersonalInformation";
ResultSet rs=statement.executeQuery(sql);
while(rs.next()){
arrUser.add(new User(rs.getInt("id_pi"),rs.getString("usename")));
}
statement.close();
}catch(Exception ex){
ex.printStackTrace();
return null;
}
return arrUser;
}
}