package Server;
// This file contains material supporting section 3.7 of the textbook:
// "Object Oriented Software Engineering" and is issued under the open-source
// license found at www.lloseng.com
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.io.Serializable;
import ocsf.server.*;
import java.io.*;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.awt.Frame;
import Gui.Login;
import Gui.ServerOnline;
//import com.sun.jmx.remote.internal.ClientCommunicatorAdmin;
import java.awt.Frame;
import javax.swing.JOptionPane;
import logic.Review;
import logic.obj;
/**
* This class overrides some of the methods in the abstract
* superclass in order to give more functionality to the server.
*
* @author Dr Timothy C. Lethbridge
* @author Dr Robert Laganière
* @author François Bélanger
* @author Paul Holden
* @version July 2000
*/
public class EchoServer extends AbstractServer
{
//Class variables *************************************************
public Connection con;
/**
* The default port to listen on.
*/
final public static int DEFAULT_PORT = 5555;
//Constructors ****************************************************
/**
* Constructs an instance of the echo server.
*
* @param port The port number to connect on.
*/
public EchoServer(int port)
{
super(port);
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception ex) {/* handle the error*/}
try{
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/i-book","root","Braude");
con=conn;
System.out.println("SQL connection succeed");
ResetOnline(); //Reset Online status
}catch (SQLException ex)
{/* handle any errors*/
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
}
//Instance methods ************************************************
/**
* This method handles any messages received from the client.
*
* @param msg The message received from the client.
* @param client The connection from which the message originated.
*/
public void handleMessageFromClient
(Object msg, ConnectionToClient client)
{
Statement stmt;
String field_code ;
PreparedStatement Pass;
ResultSet sqlResult;
ArrayList<String> arr= new ArrayList();
String[] retdata = new String[1000];
String[] retdata2 = new String[1000];
String[] retdata3 = new String[500];
String[] msg2 =(String[])msg;
System.out.println("Message received: " + msg2[0] + " from " + client);
PreparedStatement ps;
String rows="0";
try{
stmt = con.createStatement();
if (msg instanceof String[])
{
/* restoring Object }*/
obj msg3 = new obj();
msg3.setType(msg2[0]);
int i;
for(i=0;msg2[i]!=null;i++){
msg3.setParam(i, msg2[i+1]);
}
/* end of restoring Object */
// System.out.println(msg3.getParam(0));
if(msg3 instanceof obj){
if(msg3.getType().equals("login")){
int err1=1;
ResultSet rs = stmt.executeQuery( "SELECT * FROM User WHERE Username = \"" + msg3.getParam(0) + "\" ;");
retdata[0] = "login";
while(rs.next())
{
err1=0;
retdata[1] = (rs.getString(1)) ; //User name
retdata[2] = (rs.getString(2)); // Password
retdata[3] = (rs.getString(3)); // First name
retdata[4] = (rs.getString(4)); // Last name
retdata[5] = (rs.getString(5)); // Address
retdata[6] = (rs.getString(6)); // Online
retdata[7] = (rs.getString(7)); // Privilege
retdata[8] = (rs.getString(8)); // Birth date
// System.out.println("Message received: " + msg2[1] + " from " + client);
}
if(err1==1)
{
retdata[1]="not found";
//System.out.println("Message received: " + retdata[1] + " from " + client);
}
//System.out.println(retdata[1]);
// end else
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end if login
if(msg3.getType().equals("AddUser"))
{
ResultSet rs1 = stmt.executeQuery( "SELECT * FROM User WHERE Username = \"" + msg3.getParam(0) + "\" ;"); //Check if the user already exists
if (!rs1.next())
{
PreparedStatement tr = con.prepareStatement("insert into User values(?,?,?,?,?,?,?,?,?)"); //Add to the table User
tr.setString(1,msg2[1]); //Username
tr.setString(2,msg2[2]); //Password
tr.setString(3,msg2[4]); // First_name
tr.setString(4,msg2[5]); // Last_name
tr.setString(5,msg2[6]); // address
tr.setInt(6,1); // Online
tr.setInt(7,0); // privilege
tr.setString(8,msg2[7]); //birth date
tr.setInt(9,0); // used
tr.executeUpdate(); //Update table
retdata[1]="ok"; //
retdata[2]=msg2[1]; //Username
retdata[3]=msg2[2]; //Password
retdata[4]=msg2[4]; // First_name
retdata[5]=msg2[5]; // Last_name
retdata[6]=msg2[6]; // address
retdata[7]="1"; // Online
retdata[8]="0"; // privilege
retdata[9]=msg2[7]; //birthdate
} // end
retdata[0]="AddUser";
try {
client.sendToClient(retdata); //Sent details back to client
}
catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}// end catch
}// end if AddUser
if(msg2[0].equals("check"))
{
ResultSet rs2 = stmt.executeQuery( "SELECT * FROM User WHERE Username = \"" + msg3.getParam(0) + "\" ;");
retdata[0] = "check";
if(rs2.next()){
retdata[1] = (rs2.getString(1));
//System.out.println("Message received: " + retdata[1] + " from " + client);
}
else
{
retdata[1] = "empty";
//System.out.println("Message received: " + retdata[1] + " from " + client);
}
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end if check
if(msg3.getType().equals("Online"))
{
int rs3 = stmt.executeUpdate( "UPDATE User SET Online = 1 WHERE Username = \"" +msg3.getParam(0)+ "\";");
} // end if Online
if(msg3.getType().equals("Offline"))
{
int rs4 = stmt.executeUpdate( "UPDATE User SET Online = 0 WHERE Username = \"" +msg3.getParam(0)+ "\";");
} // end if Offline
if(msg3.getType().equals("new review")){ // get new review for management
ResultSet rs6 = stmt.executeQuery( "Select COUNT(*) from review_new; ");
if(rs6.next()){
retdata[5] = (rs6.getString(1));
//System.out.println("Message received: " + retdata[5] + " from " + client);
} // end if rs6
System.out.println("count= "+retdata[5]);
if(!retdata[5].equals("0")) {// if there are any reviews
ResultSet rs5 = stmt.executeQuery( "SELECT * FROM review_new WHERE id = \"" + msg3.getParam(0) + "\" ;");
retdata[0] = "new review";
// System.out.println("Message received: " + retdata[1] + " from " + client);
while(rs5.next())
{
retdata[1] = (rs5.getString(1)) ; //isbn
retdata[2] = (rs5.getString(2)); // username
retdata[3] = (rs5.getString(3)); // review
retdata[4] = (rs5.getString(4)); // date
retdata[7] = (rs5.getString(6)); // rating
} // end while rs.next()
ResultSet rs7 = stmt.executeQuery( "SELECT * FROM book WHERE isbn = \"" + retdata[1] + "\" ;");
if(rs7.next()){
retdata[6] = (rs7.getString(2));
//System.out.println("Message received: " + retdata[6] + " from " + client);
}
}// end if ("0")
else{//else ("0")
retdata[1]="0";
}// end else ("0")
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
} // end new review
/*
* Check how many reviews in server
*/
if(msg3.getType().equals("check review")){
retdata[0] = "check review";
ResultSet rs10 = stmt.executeQuery( "Select COUNT(*) from review_new; ");
if(rs10.next()){
retdata[1] = (rs10.getString(1)); // save amount of rows in review_new
} // end if rs10
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end check review
if(msg3.getType().equals("confirm review")){
retdata[0] = "confirm review";
System.out.println("before");
ResultSet rs10 = stmt.executeQuery( "Select COUNT(id) from review_new; ");
if(rs10.next()){
rows = (rs10.getString(1)); // save amount of rows in review_new
} // end if rs10
System.out.println("After: "+rows);
/*
*
* save review_new(id) in retdata
*
*/
ResultSet rs8 = stmt.executeQuery( "SELECT * FROM review_new WHERE id = \"" + msg3.getParam(0) + "\" ;");
while(rs8.next())
{
retdata[1] = (rs8.getString(1)) ; //isbn
retdata[2] = (rs8.getString(2)); // username
retdata[3] = (msg3.getParam(1)); // review from user
retdata[4] = (rs8.getString(4)); // date
retdata[11] = (rs8.getString(6)); // rating
retdata[5] = "success";
} // end while rs8.next()
System.out.println("data from review_new(id): "+retdata[1]+" "+ retdata[2]);
if(retdata[5]=="success"){ // only if successful
/*
*
* save data in review_new(rows) - last row and put it in retdata
*
*
*/
if(!rows.equals("1")){
// System.out.println("1");
ps=con.prepareStatement( "SELECT * FROM review_new WHERE id = ?;");
ps .setString(1,rows);
ResultSet rs11 = ps.executeQuery();
// ResultSet rs11 = stmt.executeQuery( "SELECT * FROM review_new WHERE id = \"" + rows + "\" ;");
while(rs11.next()) // store in retdata the last row from reveiw_new
{
// System.out.println("2");
retdata[6] = (rs11.getString(1)) ; //isbn
retdata[7] = (rs11.getString(2)); // username
retdata[8] = (rs11.getString(3)); // review
retdata[9] = (rs11.getString(4)); // date
retdata[10] = (rs11.getString(6)); // date
} // end while rs11.next()
// System.out.println("data from review_new(rows): "+retdata[6]+" "+ retdata[7]);
/* deleting the last row in table in table review_new
*
*/
ps=con.prepareStatement("DELETE FROM review_new WHERE id = ? ;");
ps .setString(1,rows);
ps.executeUpdate();
/*
* row(id) from table review_new = row(rows) from table review_new
*
* */
// System.out.println("4");
// int rs3 = stmt.executeUpdate( "UPDATE review_new SET isbn = \"" +retdata[6]+ "\",username = \"" +retdata[7]+ "\",review = \"" +retdata[8]+ "\" ,data = \"" +retdata[9]+ "\" WHERE id = \"" +msg3.getParam(0)+ "\";");
ps=con.prepareStatement("UPDATE review_new SET isbn = ?, username = ?, review = ?, date = ?, rating = ? WHERE id = ?;");
ps.setString(1,retdata[6]);
ps.setString(2,retdata[7]);
ps.setString(3,retdata[8]);
ps.setString(4,retdata[9]);
ps.setString(5,retdata[10]);
ps.setString(6,msg3.getParam(0));
ps.executeUpdate();
// System.out.println("5");
// System.out.println("new data in review_new(id): "+retdata[6]+" "+ retdata[7]);
}// end if rows...
else { // if we at the last review new
/* deleting the last row in table in table review_new
*
*/
// System.out.println("6");
ps=con.prepareStatement("DELETE FROM review_new WHERE id = ? ;");
ps .setString(1,msg3.getParam(0));
ps.executeUpdate();
}// end else
/*
* insert row(id) from table review_new into table review_old
*/
// System.out.println("7");
PreparedStatement tr3 = con.prepareStatement("insert into review_old values(?,?,?,?,?,?)"); //Add to the table review_old
tr3.setString(1,retdata[1]); //isbn
tr3.setString(2,retdata[2]); //username
tr3.setString(3,retdata[3]); // review
tr3.setString(4,retdata[4]); // date
tr3.setString(5,retdata[11]); // rating
tr3.setString(6,"0"); // use
tr3.executeUpdate(); //Update table
/** update rating accordering
*
*/
ps=con.prepareStatement( "SELECT avg(rating) FROM review_old WHERE isbn = ?;");
ps .setString(1,msg3.getParam(2)); //isbn
ResultSet rs11 = ps.executeQuery();
// ResultSet rs11 = stmt.executeQuery( "SELECT * FROM review_new WHERE id = \"" + rows + "\" ;");
while(rs11.next()) // store in retdata the last row from reveiw_new
{
retdata[20] = (rs11.getString(1)) ; //avg(rating)
} // end while rs11.next(
/** update rating in book table
*
*
*/
ps=con.prepareStatement("UPDATE book SET rating = ? WHERE isbn = ?;");
ps.setString(1,retdata[20]);
ps.setString(2,msg3.getParam(2));
ps.executeUpdate();
}// end if successful
// System.out.println("8");
}// end confirm review
if(msg3.getType().equals("check review1")){
System.out.println("Server: in check review1");
retdata[0] = "check review1";
int success=0;
/* Check if user has already review on that book*/
ps=con.prepareStatement( "SELECT * FROM review_new WHERE isbn = ? and username = ?;");
ps .setString(1,msg3.getParam(0));
ps .setString(2,msg3.getParam(1));
ResultSet rs11 = ps.executeQuery();
while(rs11.next()) // store in retdata the last row from reveiw_new
{
success=1;
} // end while rs11.next()
ps=con.prepareStatement( "SELECT * FROM review_old WHERE isbn = ? and username = ?;");
ps .setString(1,msg3.getParam(0));
ps .setString(2,msg3.getParam(1));
ResultSet rs12 = ps.executeQuery();
while(rs12.next()) // store in retdata the last row from reveiw_new
{
success=1;
} // end while rs11.next()
/* have review */
if(success==1){
retdata[1]="yes";
}// end success
/* no review */
else
retdata[1]="no";
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end check review1
if(msg3.getType().equals("delete review")){
retdata[0] = "delete review";
ResultSet rs13 = stmt.executeQuery( "Select COUNT(id) from review_new; ");
if(rs13.next()){
rows = (rs13.getString(1)); // save amount of rows in review_new
} // end if rs13
if(!rows.equals("0")){
ps=con.prepareStatement( "SELECT * FROM review_new WHERE id = ?;");
ps .setString(1,rows);
ResultSet rs11 = ps.executeQuery();
while(rs11.next()) // store in retdata the last row from reveiw_new
{
retdata[6] = (rs11.getString(1)) ; //isbn
retdata[7] = (rs11.getString(2)); // username
retdata[8] = (rs11.getString(3)); // review
retdata[9] = (rs11.getString(4)); // date
retdata[10]=(rs11.getString(6)); // rating
} // end while rs11.next()
// System.out.println("data from review_new(rows): "+retdata[6]+" "+ retdata[7]);
/* deleting the last row in table in table review_new
*
*/
ps=con.prepareStatement("DELETE FROM review_new WHERE id = ? ;");
ps .setString(1,rows);
ps.executeUpdate();
try {
Thread.sleep(100);
} // end try
catch (InterruptedException ex) {
Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
} // end catch
/*
* row(id) from table review_new = row(rows) from table review_new
*
* */
if(!rows.equals("1")){
// int rs3 = stmt.executeUpdate( "UPDATE review_new SET isbn = \"" +retdata[6]+ "\",username = \"" +retdata[7]+ "\",review = \"" +retdata[8]+ "\" ,data = \"" +retdata[9]+ "\" WHERE id = \"" +msg3.getParam(0)+ "\";");
ps=con.prepareStatement("UPDATE review_new SET isbn = ?, username = ?, review = ?, date = ?, rating = ? WHERE id = ?;");
ps.setString(1,retdata[6]);
ps.setString(2,retdata[7]);
ps.setString(3,retdata[8]);
ps.setString(4,retdata[9]);
ps.setString(5, retdata[10]);
ps.setString(6,msg3.getParam(0));
ps.executeUpdate();
} // if rows !"1"
// System.out.println("new data in review_new(id): "+retdata[6]+" "+ retdata[7]);
}// end if rows...
}// end delete review
if(msg3.getType().equals("search reviews")){ // searching reviews in review_old
String filter = msg3.getParam(1); // if "filter" was sent as filter
int index1=0; // index1
int index2=1; // index2
if(filter.equals("Title") || filter.equals("Filter") ){
index1=0;
/*
* Get isbn from book db by title
*/
ps=con.prepareStatement( "SELECT * FROM book WHERE title = ?;");
ps .setString(1,msg3.getParam(0));
ResultSet rs12 = ps.executeQuery();
while(rs12.next()) // store in retdata search results
{
retdata2[index1++] = (rs12.getString(1)) ; //isbn
// System.out.println(retdata2[0]);
} // end while rs12.next() // isbn from book db
/*
* Go through all the results from retdata2(isbn's) and get reviews from reivew_old
*/
for(index1--;index1>=0;index1--){
ps=con.prepareStatement( "SELECT * FROM review_old WHERE isbn = ?;");
ps .setString(1,retdata2[index1]);
ResultSet rs14 = ps.executeQuery();
while(rs14.next()) // store in retdata search results
{
if(rs14.getString(6).equals("0")){ // get only reviews that are not already used
retdata[index2++] = (rs14.getString(1)) ; //isbn
retdata[index2++] = (rs14.getString(2)); // username
System.out.println(rs14.getString(2));
String tmp = (rs14.getString(3)); // review
retdata[index2++] = (rs14.getString(4)); // date
retdata[index2++] = (rs14.getString(5)); // rating
ps=con.prepareStatement( "UPDATE review_old SET used = 1 WHERE review = ?;");
ps.setString(1,tmp);
ps.executeUpdate();
}// end if(rs14.getString(1).equals("0"))
// System.out.println(retdata[1]+retdata[2]+retdata[3]+retdata[4]+retdata[5]);
} // end while rs14.next() // reviews from review_old
}// end for
}// equals to Title
if(filter.equals("Author") || filter.equals("Filter") ){
index1=0;
/*
* Get isbn from book_author by author
*/
ps=con.prepareStatement( "SELECT * FROM book_author WHERE author = ?;");
ps .setString(1,msg3.getParam(0));
ResultSet rs12 = ps.executeQuery();
while(rs12.next()) // store in retdata search results
{
retdata2[index1++] = (rs12.getString(1)) ; //isbn
} // end while rs12.next() // isbn from book_author
/*
* Go through all the results from retdata2(isbn's) and get reviews from reivew_old
*/
for(index1--;index1>=0;index1--){
ps=con.prepareStatement( "SELECT * FROM review_old WHERE isbn like '%"+retdata2[index1]+"%';");
// ps .setString(1,retdata2[index1]);
ResultSet rs14 = ps.executeQuery();
while(rs14.next()) // store in retdata search results
{
if(rs14.getString(6).equals("0")){ // get only reviews that are not already used
retdata[index2++] = (rs14.getString(1)) ; //isbn
retdata[index2++] = (rs14.getString(2)); // username
String tmp = (rs14.getString(3)); // review
retdata[index2++] = (rs14.getString(4)); // date
retdata[index2++] = (rs14.getString(5)); // rating
ps=con.prepareStatement( "UPDATE review_old SET used = 1 WHERE review like '%"+tmp+"%';");
// ps.setString(1,tmp);
ps.executeUpdate();
}// end if(rs14.getString(1).equals("0"))
} // end while rs14.next() // reviews from review_old
}// end for
}// equals to Author
if(filter.equals("Keyword") || filter.equals("Filter") ){
index1=0;
/*
* Get isbn from keyword by keyword
*/
ps=con.prepareStatement( "SELECT * FROM keyword WHERE keyword like '%"+msg3.getParam(0)+"%';");
// ps .setString(1,msg3.getParam(0));
ResultSet rs12 = ps.executeQuery();
while(rs12.next()) // store in retdata search results
{
retdata2[index1++] = (rs12.getString(1)) ; //isbn
} // end while rs12.next() // isbn from keyword
/*
* Go through all the results from retdata2(isbn's) and get reviews from reivew_old
*/
for(index1--;index1>=0;index1--){
ps=con.prepareStatement( "SELECT * FROM review_old WHERE isbn = ?;");
ps .setString(1,retdata2[index1]);
ResultSet rs14 = ps.executeQuery();
while(rs14.next()) // store in retdata search results
{
if(rs14.getString(6).equals("0")){ // get only reviews that are not already used
retdata[index2++] = (rs14.getString(1)) ; //isbn
retdata[index2++] = (rs14.getString(2)); // username
String tmp = (rs14.getString(3)); // review
retdata[index2++] = (rs14.getString(4)); // date
retdata[index2++] = (rs14.getString(5)); // rating
ps=con.prepareStatement( "UPDATE review_old SET used = 1 WHERE review = ?;");
ps.setString(1,tmp);
ps.executeUpdate();
}// end if(rs14.getString(1).equals("0"))
} // end while rs14.next() // reviews from review_old
}// end for
}// equals to Keyword
retdata[0]= "search reviews";
retdata[index2]="end"; // marking end of data
/*
* set all use field to 0 ( because we are done with reading reviews form server )
*/
Statement stmt1;
try{
stmt1 = con.createStatement();
int rs = stmt1.executeUpdate( "UPDATE review_old SET used=0;");
}
catch (SQLException esx)
{/* handle any errors */
System.out.println("SQLException: " + esx.getMessage());
System.out.println("SQLState: " + esx.getSQLState());
System.out.println("VendorError: " + esx.getErrorCode());
}
/*
* Sending retdata back to chatclient
*/
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end search reviews
if(msg3.getType().equals("get hist")){
int suc=0;
int result=0;
int index2=1; // index2
String tmp = new String();
retdata[0]= "get hist";
System.out.println("Server:get hist entered");
ps=con.prepareStatement( "SELECT isbn From book WHERE title = ? ;");
ps.setString(1,msg3.getParam(0));
ResultSet rs13 = ps.executeQuery();
while(rs13.next()) // store in retdata book info
{
tmp = (rs13.getString(1)) ; //isbn
System.out.println("Server:get hist:isbn:"+tmp);
} // end while (rs12.next())
ps=con.prepareStatement( "SELECT MONTH(date) AS month, COUNT(*) AS monthly_sales_count FROM book_order WHERE isbn = ? AND YEAR(date) = ? GROUP BY month;");
ps.setString(1,tmp);
ps.setString(2,msg3.getParam(1));
ResultSet rs12 = ps.executeQuery();
while(rs12.next()) // store in retdata book info
{
retdata[index2++] = (rs12.getString(1)) ; //months
retdata[index2++] = (rs12.getString(2)) ; //counter for that month
suc=1;
result =1;
System.out.println("Server:Show book:"+retdata[1]);
} // end while (rs12.next())
if(suc==1){
retdata[index2]="null"; // marking end of data
}
else{
retdata[1]="zero";
retdata[2]="null"; // marking end of data
}
System.out.println("Server:Show hist:"+retdata[0]+retdata[1]+retdata[2]+retdata[3]);
/*
* Sending retdata back to chatclient
*/
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end get hist
if(msg3.getType().equals("update search")){
int update =0;
ps=con.prepareStatement( "SELECT * From book_search WHERE isbn = ? AND date = CURRENT_DATE ;");
ps.setString(1,msg3.getParam(0));
ResultSet rs13 = ps.executeQuery();
if(rs13.next()) // store in retdata book info
{
retdata[1] = (rs13.getString(1)) ; //isbn
retdata[2] = (rs13.getString(2)) ; //counter
retdata[3] = (rs13.getString(3)) ; //date
update =1; // need to update counter
retdata[20]=Integer.toString(Integer.parseInt(retdata[2])+1); // counter++
} // end if (rs12.next())
/** need to update counter
*
*/
if(update==1){
PreparedStatement ps11=con.prepareStatement( "UPDATE book_search SET counter = ? WHERE isbn = ? AND date = CURRENT_DATE;");
ps11.setString(1,retdata[20]);
ps11.setString(2,retdata[1]);
ps11.executeUpdate();
}// end
/**
* need to insert new result into book search table
*/
else{
PreparedStatement tr3 = con.prepareStatement("insert into book_search values(?,?,CURRENT_DATE)"); //Add to the table review_old
tr3.setString(1,msg3.getParam(0)); //isbn
tr3.setInt(2,1); //first counter
tr3.executeUpdate(); //Update table
}// end else
}// end update search
if(msg3.getType().equals("get hist2")){
int suc=0;
int result=0;
int index2=1; // index2
String tmp = new String();
retdata[0]= "get hist";
System.out.println("Server:get hist2 entered");
ps=con.prepareStatement( "SELECT isbn From book WHERE title = ? ;");
ps.setString(1,msg3.getParam(0));
ResultSet rs13 = ps.executeQuery();
while(rs13.next()) // store in retdata book info
{
tmp = (rs13.getString(1)) ; //isbn
System.out.println("Server:get hist:isbn:"+tmp);
} // end while (rs12.next())
ps=con.prepareStatement( "SELECT MONTH(date) AS month, counter*MONTH(date) AS monthly_sales_count FROM book_search WHERE isbn = ? AND YEAR(date) = ? GROUP BY date;");
ps.setString(1,tmp);
ps.setString(2,msg3.getParam(1));
ResultSet rs12 = ps.executeQuery();
while(rs12.next()) // store in retdata book info
{
retdata[index2++] = (rs12.getString(1)) ; //months
retdata[index2++] = (rs12.getString(2)) ; //counter for that month
suc=1;
result =1;
System.out.println("Server:Show book:"+retdata[1]);
} // end while (rs12.next())
if(suc==1){
retdata[index2]="null"; // marking end of data
}
else{
retdata[1]="zero";
retdata[2]="null"; // marking end of data
}
System.out.println("Server:Show hist:"+retdata[0]+retdata[1]+retdata[2]+retdata[3]);
/*
* Sending retdata back to chatclient
*/
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end get hist2
if(msg3.getType().equals("sent review")){
String ID = new String();
retdata[0] = "sent review";
ResultSet rs10 = stmt.executeQuery( "Select COUNT(*) from review_new; ");
if(rs10.next()){
ID = (rs10.getString(1)); // save amount of rows in review_new
} // end if rs10
ID = Integer.toString(Integer.parseInt(ID)+1); // ID++
retdata[1]=(String)ID;
System.out.println("sent review: review:"+msg3.getParam(2));
PreparedStatement tr3 = con.prepareStatement("INSERT INTO review_new VALUE (?,?,?, CURDATE(),?, ?);"); //Add to the table review_new
tr3.setString(1,msg3.getParam(0)); //isbn
tr3.setString(2,msg3.getParam(1)); //username
tr3.setString(3,msg3.getParam(2)); // review
tr3.setString(4,ID); // id
tr3.setString(5,msg3.getParam(3)); // rating
// tr3.setString(6,"0"); // used
tr3.executeUpdate(); //Update table
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end sent review
if(msg3.getType().equals("msg")){
String ID = new String();
retdata[0] = "msg";
ResultSet rs10 = stmt.executeQuery( "Select COUNT(*) from review_new; ");
if(rs10.next()){
ID = (rs10.getString(1)); // save amount of rows in review_new
} // end if rs10
retdata[1]=(String)ID;
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end msg
if(msg3.getType().equals("show book")){
int index2=1; // index2
retdata[0] = "show book";
ps=con.prepareStatement( "SELECT b.isbn,b.title,b.language,b.summery,b.contents,b.rating,ct1.entry_desc ,ct2.entry_desc ,ba.author ,b.price FROM book b, catalog_field cf, catalog_subject cs, book_author ba, code_table ct1, code_table ct2 WHERE b.isbn = ? and b.isbn = cf.isbn and ct1.table_code = 7 and ct2.table_code = 6 and cf.field = ct1.entry_code and cs.subject = ct2.entry_code and b.isbn = cs.isbn and b.isbn = ba.isbn");
ps .setString(1,msg3.getParam(0));
ResultSet rs12 = ps.executeQuery();
while(rs12.next()) // store in retdata book info
{
retdata[index2++] = (rs12.getString(1)) ; //isbn
retdata[index2++] = (rs12.getString(2)) ; //title
retdata[index2++] = (rs12.getString(3)) ; //language
retdata[index2++] = (rs12.getString(4)) ; //summary
retdata[index2++] = (rs12.getString(5)) ; //contents
retdata[index2++] = (rs12.getString(6)) ; //rating
retdata[index2++] = (rs12.getString(7)) ; //price
retdata[index2++] = (rs12.getString(8)) ; //field
retdata[index2++] = (rs12.getString(9)) ; //subject
retdata[index2++] = (rs12.getString(10)) ; //author
System.out.println("Server:Show book:"+retdata[1]);
} // end while (rs12.next())
retdata[index2]="null"; // marking end of data
/*
* Sending retdata back to chatclient
*/
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end show book
if(msg3.getType().equals("show")){
retdata[0] = "show review";
/*
* Get review from review_old by isbn & username
*/
System.out.println("In ShowReview");
ps=con.prepareStatement( "SELECT * FROM review_old WHERE isbn = ? AND username = ?;");
ps .setString(1,msg3.getParam(0));
ps .setString(2,msg3.getParam(1));
ResultSet rs12 = ps.executeQuery();
while(rs12.next()) // store in retdata review
{
retdata[1] = (rs12.getString(1)) ; //isbn
retdata[2] = (rs12.getString(2)) ; //username
retdata[3] = (rs12.getString(3)) ; //review
retdata[4] = (rs12.getString(4)) ; //date
retdata[5] = (rs12.getString(5)) ; //rating
// System.out.println("hi: "+retdata[0]+retdata[1]+retdata[2]);
} // end while (rs12.next())
/*
* get book title
*/
ps=con.prepareStatement( "SELECT * FROM book WHERE isbn = ?;");
ps .setString(1,retdata[1]);
ResultSet rs13 = ps.executeQuery();
while(rs13.next()) // store in retdata review title
{
retdata[6] = (rs13.getString(2)) ; //title
} // end while (rs13.next())
System.out.println(retdata[6]);
/*
* Sending retdata back to chatclient
*/
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
} // end show review
if(msg3.getType().equals("get users")){
// System.out.println("entered get users");
int index=1;
retdata[0]="get users";
ps=con.prepareStatement( "SELECT username FROM user WHERE Privilege=0;");
ResultSet rs13 = ps.executeQuery();
while(rs13.next()) // store in retdata review title
{
retdata[index++] = (rs13.getString(1)) ; //username
} // end while (rs13.next())
/*
* Sending retdata back to chatclient
*/
retdata[index]="null";
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end get users
if(msg3.getType().equals("get orders")){
int index=0;
int index1=1;
retdata[0]="order";
if(msg3.getParam(0).equals("All")){ // if all orders were asked
ps=con.prepareStatement( "SELECT * FROM book_order br,book b WHERE br.isbn = b.isbn ORDER BY(username);");
ResultSet rs13 = ps.executeQuery();
while(rs13.next()) // store in retdata boo order
{
retdata[index1++] = (rs13.getString(5)) ; //title
retdata[index1++] = (rs13.getString(1)); // isbn
retdata[index1++] = (rs13.getString(2)); //user
retdata[index1++] = (rs13.getString(3)); //date
} // end while (rs13.next())
}// end if
else{
ps=con.prepareStatement( "SELECT * FROM book_order br,book b WHERE br.username = ? and br.isbn = b.isbn ORDER BY(username);");
ps .setString(1,msg3.getParam(0)); //username
ResultSet rs13 = ps.executeQuery();
while(rs13.next()) // store in retdata boo order
{
retdata[index1++] = (rs13.getString(5)) ; //title
retdata[index1++] = (rs13.getString(1)); // isbn
retdata[index1++] = (rs13.getString(2)); //user
retdata[index1++] = (rs13.getString(3)); //date
} // end while (rs13.next())
}// end else
/*
* Sending retdata back to chatclient
*/
System.out.println("Server: get order"+retdata[0]+retdata[1]);
retdata[index1]="null";
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end get orders
if(msg3.getType().equals("get books")){
int index=1;
retdata[0]="get books";
ps=con.prepareStatement( "SELECT title FROM book ;");
ResultSet rs13 = ps.executeQuery();
while(rs13.next()) // store in retdata review title
{
retdata[index++] = (rs13.getString(1)) ; //title
} // end while (rs13.next())
/*
* Sending retdata back to chatclient
*/
retdata[index]="null";
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end get books
if(msg3.getType().equals("get books po")){
int index=1;
retdata[0]="get books po";
ps=con.prepareStatement( "SELECT b.title, br.isbn,count(br.isbn) AS counter FROM book_order br, book b WHERE br.isbn=b.isbn GROUP BY (br.isbn) ORDER BY (counter);");
ResultSet rs13 = ps.executeQuery();
while(rs13.next()) // store in retdata review title
{
retdata[index++] = (rs13.getString(1)) ; //title
retdata[index++] = (rs13.getString(2)) ; //isbn
retdata[index++] = (rs13.getString(3)) ; //count
} // end while (rs13.next())
/*
* Sending retdata back to chatclient
*/
retdata[index]="null";
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end get bookspo
/*
Vitali
*/
if(msg3.getType().equals("Search Book")){
boolean Title = Boolean.parseBoolean(msg3.getParam(1)),
Author = Boolean.parseBoolean(msg3.getParam(2)),
Field = Boolean.parseBoolean(msg3.getParam(3)),
Keyword = Boolean.parseBoolean(msg3.getParam(4)),
Subject = Boolean.parseBoolean(msg3.getParam(5)),
Language = Boolean.parseBoolean(msg3.getParam(6)),
All = Boolean.parseBoolean(msg3.getParam(7));
int index1=1; // index1
if( Title || All){
/*
* Get isbn by title
*/
String query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b,catalog_field cf WHERE cf.isbn = b.isbn AND b.title LIKE '%";
index1 = AddToResult(queryEXES(query,msg3.getParam(0)),retdata,index1);
}// equals to Title
if(Author|| All ){
/*
* Get isbn by author
*/
String query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b,book_author ba,catalog_field cf WHERE b.isbn = cf.isbn AND ba.isbn = b.isbn AND ba.author LIKE '%" ;
index1 = AddToResult(queryEXES(query,msg3.getParam(0)),retdata,index1);
}// equals to Author
if(Keyword|| All ){
/*
* Get isbn by keyword
*/
String query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b,keyword k,catalog_field cf WHERE b.isbn = cf.isbn AND k.isbn = b.isbn AND k.keyword LIKE '%";
index1 = AddToResult(queryEXES(query,msg3.getParam(0)),retdata,index1);
}// equals to Keyword
//** Acceptance test Eror The Query was not updated to the new database
//** FIXED
if(Field|| All ){
/*
* Get isbn by Field
*/
String query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b, catalog_field cf, code_table ct WHERE b.isbn = cf.isbn AND ct.table_code = 7 AND cf.field = ct.entry_code and ct.entry_desc LIKE '%";
index1 = AddToResult(queryEXES(query,msg3.getParam(0)),retdata,index1);
}// equals to Field
if(Subject|| All ){
/*
* Get isbn by Subject
*/
String query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b,catalog_subject cs, code_table ct WHERE b.isbn = cs.isbn AND ct.table_code = 6 AND cs.subject = ct.entry_code AND ct.entry_desc LIKE '%";
index1 = AddToResult(queryEXES(query,msg3.getParam(0)),retdata,index1);
}// equals to Subject
//** END FIX
if(Language|| All ){
/*
* Get isbn by Language
*/
String query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b,catalog_field cf WHERE b.isbn = cf.isbn AND b.language LIKE '%";
index1 = AddToResult(queryEXES(query,msg3.getParam(0)),retdata,index1);
}// equals to Language
retdata[0]= "search Book";
retdata[index1]="end1"; // marking end of data
/*
* set all use field to 0 ( because we are done with book search )
*/
Statement stmt1;
try{
stmt1 = con.createStatement();
int rs = stmt1.executeUpdate( "UPDATE book SET used=0;");
}
catch (SQLException esx)
{/* handle any errors */
System.out.println("SQLException: " + esx.getMessage());
System.out.println("SQLState: " + esx.getSQLState());
System.out.println("VendorError: " + esx.getErrorCode());
}
/*
* Sending retdata back to chatclient
*/
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end search book
if(msg3.getType().equals("showBookIn")){
int index1 = 0;
retdata[index1++] = "show BookAll";
String query = "SELECT title,language,Summery,contents,rating,viewed,purchased,price,pic FROM book WHERE isbn = ?;";
ResultSet rs = queryEXE(query,msg3.getParam(0));
rs.first();
retdata[index1++] = msg3.getParam(0) ; //isbn
retdata[index1++] = (rs.getString(1)); // title
retdata[index1++] = (rs.getString(2)); // language
retdata[index1++] = (rs.getString(3)); //Summery
retdata[index1++] = (rs.getString(4)); // contents
retdata[index1++] = (rs.getString(5)) ; //rating
retdata[index1++] = (rs.getString(6)); // viewed
retdata[index1++] = (rs.getString(7)); // purchased
retdata[index1++] = (rs.getString(8)); //price
try
{
File image = new File("D:\\java.gif");
FileOutputStream fos = new FileOutputStream(image);
byte[] buffer = new byte[256];
// Get the binary stream of our BLOB data
//
InputStream is = rs.getBinaryStream(9);
while (is.read(buffer) > 0) {
fos.write(buffer);
}
fos.close();
}
catch(IOException ex){
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
query = "SELECT author FROM book_author WHERE isbn = ?;";
AddToResultView(queryEXE(query,msg3.getParam(0)),retdata,index1);
retdata[index1++] = "endBookAutorTable"; //end From Book Table
query = "SELECT ct.entry_desc FROM book_field b, code_table ct WHERE ct.table_code = 1 AND ct.entry_code = b.field ANd b.isbn = ?";
AddToResultView(queryEXE(query,msg3.getParam(0)),retdata,index1);
retdata[index1++] = "endBookAutorTable"; //end From Book Table
retdata[index1++] = "endBookFieldTable"; //end From Book Table
// retdata[index1] = "endALL"; //end From Book Table
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
} //End Show BOOK
if(msg3.getType().equals("AdSearch Book")){
int index1 = 1;
retdata[0] = "AdSearch Book";
index1 = AddToResult(ASqueryEXE(msg3.getParam(0),msg3.getParam(1)),retdata,index1);
if (msg3.getParam(2).equals("or")&& !msg3.getParam(3).equals("None"))
index1 = AddToResult(ASqueryEXE(msg3.getParam(3),msg3.getParam(4)),retdata,index1);
if (msg3.getParam(5).equals("or")&&!msg3.getParam(6).equals("None"))
index1 = AddToResult(ASqueryEXE(msg3.getParam(6),msg3.getParam(7)),retdata,index1);
if (msg3.getParam(8).equals("or")&& !msg3.getParam(9).equals("None"))
index1 = AddToResult(ASqueryEXE(msg3.getParam(9),msg3.getParam(10)),retdata,index1);
if (msg3.getParam(11).equals("or")&& !msg3.getParam(12).equals("None"))
index1 = AddToResult(ASqueryEXE(msg3.getParam(12),msg3.getParam(13)),retdata,index1);
if (msg3.getParam(2).equals("and")&& !msg3.getParam(3).equals("None"))
index1 = AddToResult2(ASqueryEXE(msg3.getParam(3),msg3.getParam(4)),retdata,1);
if (msg3.getParam(5).equals("and")&& !msg3.getParam(6).equals("None"))
index1 = AddToResult2(ASqueryEXE(msg3.getParam(6),msg3.getParam(7)),retdata,1);
if (msg3.getParam(8).equals("and")&& !msg3.getParam(9).equals("None"))
index1 = AddToResult2(ASqueryEXE(msg3.getParam(9),msg3.getParam(10)),retdata,1);
if (msg3.getParam(11).equals("and")&& !msg3.getParam(12).equals("None"))
index1 = AddToResult2(ASqueryEXE(msg3.getParam(12),msg3.getParam(13)),retdata,1);
retdata[index1]="end1"; // marking end of data
/*
* set all use field to 0 ( because we are done with book search )
*/
ps=con.prepareStatement( "UPDATE book SET used = 0;");
ps.executeUpdate();
/*
* Sending retdata back to chatclient
*/
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
if(msg3.getType().equals("BuyBook ShowAccounts")){
int index1 = 1;
retdata[0] = "BuyBook ShowAccounts";
// Number Type Detail BookLeft
String query = "SELECT * FROM book_order WHERE username = ? AND isbn = ? ;";
ps=con.prepareStatement( query);
ps.setString(1, msg3.getParam(0));
ps.setString(2, msg3.getParam(1));
ResultSet rs = ps.executeQuery();
if (!rs.first()) // store in retdata search results
{
// Number Type Detail BookLeft
query = "SELECT account_type,details,book_left FROM account WHERE active = 1 AND Username = ?;";
rs = queryEXE(query,msg3.getParam(0));
if (rs.first()){ // store in retdata search results
retdata[index1++] = rs.getString(1);
retdata[index1++] = rs.getString(2);
retdata[index1++] = rs.getString(3);
}
retdata[index1] = "end1";
}else retdata[index1] = "end2";
System.out.println("server : BuyBook ShowAccounts "+retdata[1]);
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
if(msg3.getType().equals("MngShow Account"))
{
System.out.println("MngShow Account SERVER= " + msg3.getParam(0) + " " + msg3.getParam(1));
int index1 = 1;
String query ;
ResultSet rs ;
retdata3[0] = "MngShow Account";
if (msg3.getParam(0).equals("None") && msg3.getParam(1).equals("None") ){
query = "SELECT u.Username,u.First_name,u.Last_name,u.Privilege,u.Address FROM user u ";
ps=con.prepareStatement( query);
rs = ps.executeQuery();
while(rs.next()) // store in retdata search results
{
retdata3[index1++] = rs.getString(1);
retdata3[index1++] = rs.getString(2);
retdata3[index1++] = rs.getString(3);
retdata3[index1++] = rs.getString(4);
retdata3[index1++] = rs.getString(5);
}
}
else if(!msg3.getParam(0).equals("None"))
{
query = "SELECT u.Username,u.First_name,u.Last_name,u.Privilege,u.Address FROM user u WHERE u.First_name LIKE '";
query = query.concat(msg3.getParam(0));
query = query.concat("%' OR Last_name LIKE '");
query = query.concat(msg3.getParam(0));
query = query.concat("%';");
ps=con.prepareStatement(query);
rs = ps.executeQuery();
while(rs.next()) // store in retdata search results
{
retdata3[index1++] = rs.getString(1);
retdata3[index1++] = rs.getString(2);
retdata3[index1++] = rs.getString(3);
retdata3[index1++] = rs.getString(4);
retdata3[index1++] = rs.getString(5);
PreparedStatement ps11=con.prepareStatement( "UPDATE user SET used = 1 WHERE Username = ?;");
ps11.setString(1,rs.getString(1));
ps11.executeUpdate();
}
if(!msg3.getParam(1).equals("None"))
{
query = "SELECT u.Username,u.First_name,u.Last_name,u.Privilege,u.Address,u.used FROM user u WHERE u.Privilege = ?;";
ps=con.prepareStatement(query);
ps.setString(1,(String)msg3.getParam(1));
rs = ps.executeQuery();
index1=1;
while(rs.next()) // store in retdata search results
{
if (rs.getString(6).equals("1"))
{
retdata3[index1++] = rs.getString(1);
retdata3[index1++] = rs.getString(2);
retdata3[index1++] = rs.getString(3);
retdata3[index1++] = rs.getString(4);
retdata3[index1++] = rs.getString(5);
}
}
}
}
else
{
query = "SELECT u.Username,u.First_name,u.Last_name,u.Privilege,u.Address FROM user u WHERE u.Privilege = ?;";
ps=con.prepareStatement(query);
ps.setString(1,(String)msg3.getParam(1));
rs = ps.executeQuery();
while(rs.next()) // store in retdata search results
{
retdata3[index1++] = rs.getString(1);
retdata3[index1++] = rs.getString(2);
retdata3[index1++] = rs.getString(3);
retdata3[index1++] = rs.getString(4);
retdata3[index1++] = rs.getString(5);
}
}
retdata3[index1] = "end1";
Statement stmt1;
try{
stmt1 = con.createStatement();
int num = stmt1.executeUpdate( "UPDATE user SET used=0;");
}
catch (SQLException esx)
{/* handle any errors */
System.out.println("SQLException: " + esx.getMessage());
System.out.println("SQLState: " + esx.getSQLState());
System.out.println("VendorError: " + esx.getErrorCode());
}
try {
client.sendToClient(retdata3); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
if(msg3.getType().equals("Buy Book Server")){
PreparedStatement tr = con.prepareStatement("INSERT INTO book_order values(?,?,{fn Now()})"); //Add to the table review_old
tr.setString(1,(String)msg3.getParam(0)); //isbn
tr.setString(2,(String)msg3.getParam(1)); //username
tr.executeUpdate();
}
if(msg3.getType().equals("Update Privilege"))/////////////////////////////////////////fanny
{
ResultSet rs ;
String query ;
retdata3[0] = "Update Privilege";
query = "UPDATE user SET Privilege = ? WHERE Username = ?;" ;
ps=con.prepareStatement( query);
ps.setString(1,(String)msg3.getParam(1));//new privilege
ps.setString(2,(String)msg3.getParam(0));//user name
ps.executeUpdate();
}
if(msg3.getType().equals("Has Account"))
{
int index1 = 1;
ResultSet rs ;
String query ;
retdata[0] = "Has Account";
query = "SELECT a.active FROM account a,user u WHERE u.Username=? AND a.username = u.Username;" ;
ps=con.prepareStatement( query);
ps.setString(1,(String)msg3.getParam(0));//user name
rs = ps.executeQuery();
if (rs.first())
{ // store in retdata search results
retdata[index1++] = rs.getString(1);
}
retdata[index1] = "no account";
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
if(msg3.getType().equals("Delete Account"))/////////////////////////////////////////fanny
{
String query ;
query = ("DELETE FROM account WHERE username= ? ;");
ps=con.prepareStatement( query);
ps.setString(1,(String)msg3.getParam(0));//User name
ps.executeUpdate();
}
if(msg3.getType().equals("Frozen Account"))/////////////////////////////////////////fanny
{
String query ;
query = "UPDATE account SET active = 0 WHERE username= ?;" ;
ps=con.prepareStatement( query);
ps.setString(1,(String)msg3.getParam(0));//User name
ps.executeUpdate();
}
if(msg3.getType().equals("Active Account"))/////////////////////////////////////////fanny
{
String query ;
query = "UPDATE account SET active = 1 WHERE username= ?;" ;
ps=con.prepareStatement( query);
ps.setString(1,(String)msg3.getParam(0));//User name
ps.executeUpdate();
}
if(msg3.getType().equals("Search reviews isbn"))
{
int index1 = 1;
retdata[0] = "Search reviews isbn back";
ps=con.prepareStatement( "SELECT * FROM review_old WHERE isbn = ?;");
ps .setString(1,msg3.getParam(0));
ResultSet rs14 = ps.executeQuery();
while(rs14.next()) // store in retdata search results
{
System.out.println("***************************" + msg3.getParam(0));
retdata[index1++] = (rs14.getString(1)) ; //isbn
retdata[index1++] = (rs14.getString(2)); // username
retdata[index1++] = (rs14.getString(4)); // date
retdata[index1++] = (rs14.getString(5)); // rating
}
retdata[index1] = "end1";
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
if(msg3.getType().equals("Download FILE"))
{
System.out.println("*************************** picture Server");
ArrayList As = new ArrayList();
//(String)msg3.getParam(1);
//(String)msg3.getParam(0);
String name= null;
ResultSet r = stmt.executeQuery(("SELECT bf." + (String)msg3.getParam(1) + ",b.title FROM book_files bf,book b WHERE b.isbn = bf.isbn AND b.isbn = " + (String)msg3.getParam(0) +" ;"));
while(r.next())
{
Blob b=r.getBlob(1);
As.add("Download FILE");
byte[] mybytearray = new byte[(int) b.length()];
InputStream is = b.getBinaryStream();
BufferedInputStream bis = new BufferedInputStream(is);
name = (r.getString(2));
try {
bis.read(mybytearray,0,mybytearray.length);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
As.add(b.length());
As.add(mybytearray);
if (!msg3.getParam(1).equals("pic"))
{
As.add(name.concat(".").concat((String)msg3.getParam(1)));
As.add("doc");
}
else
{
As.add(((String)msg3.getParam(0)).concat(".").concat("jpg"));
As.add("pic");
}
}
try {
client.sendToClient(As); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/* end vitali
/**
********************** Manage Book Part ************************
*/
/**
* Update Book Info
*/
if(msg3.getType().equals("***save book***"))
{
retdata[0] = "***save book ***";
try
{
Pass=con.prepareStatement("UPDATE book SET title = ?, language = ?, summery = ?, contents = ?, price = ? WHERE isbn = ?;");
Pass.setString(1,msg3.getParam(1));
Pass.setString(2,msg3.getParam(2));
Pass.setString(3,msg3.getParam(3));
Pass.setString(4,msg3.getParam(4));
Pass.setString(5,msg3.getParam(5));
Pass.setString(6,msg3.getParam(0));
Pass.executeUpdate();
retdata[1] = "Updated";
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
} catch(SQLException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
System.out.println("SQL insert book exception code:" + ex.getErrorCode() + " MSG: " + ex.getMessage());
}
}
/**
* Delete Book
*/
if(msg3.getType().equals("***delete book***"))
{
retdata[0] = "***delete book***";
try
{
/**
* delete from:
* book from book, book field, catalog field, catalog subject, book author
*/
Pass = con.prepareStatement("DELETE FROM book_author where isbn = ?;");
Pass.setString(1,msg3.getParam(0));
Pass.executeUpdate();
Pass = con.prepareStatement("DELETE FROM catalog_subject where isbn = ?;");
Pass.setString(1,msg3.getParam(0));
Pass.executeUpdate();
Pass = con.prepareStatement("DELETE FROM catalog_field where isbn = ?;");
Pass.setString(1,msg3.getParam(0));
Pass.executeUpdate();
Pass = con.prepareStatement("DELETE FROM book_field where isbn = ?;");
Pass.setString(1,msg3.getParam(0));
Pass.executeUpdate();
Pass = con.prepareStatement("DELETE FROM book where isbn = ?;");
Pass.setString(1,msg3.getParam(0));
Pass.executeUpdate();
retdata[1] = "Deleted";
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}catch (SQLException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
System.out.println("SQL exception code:" + ex.getErrorCode() + " MSG: " + ex.getMessage());
}
}
/**
* remove field of a book
*/
if(msg3.getType().equals("***remove book field***"))
{
retdata[0] = "***remove book field***";
try
{
/**
* getting field code
*/
Pass= con.prepareStatement("SELECT t.entry_code FROM code_table t WHERE t.table_code = 1 AND t.entry_desc = ?");
Pass.setString(1, msg3.getParam(1));
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
field_code = sqlResult.getString(1);
Pass = con.prepareStatement("DELETE FROM book_field where isbn = ? AND field = ?;");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
Pass.executeUpdate();
try
{
Thread.sleep(100);
} catch (InterruptedException ex)
{
Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
}
Pass = con.prepareStatement("SELECT * FROM book_field t where t.isbn = ? AND t.field = ?");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
retdata[1] = "field Not Deleted";
}
else
{
retdata[1] = "Deleted";
}
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
else
{
retdata[1] = "No field code found";
}
}catch (SQLException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* add field to book
*/
if(msg3.getType().equals("***add book field***"))
{
retdata[0] = "***add book field***";
try
{
/**
* getting field code
*/
Pass= con.prepareStatement("SELECT t.entry_code FROM code_table t WHERE t.table_code = 1 AND t.entry_desc = ?");
Pass.setString(1, msg3.getParam(1));
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
field_code = sqlResult.getString(1);
/**
* Search if author already exist for book
*/
Pass = con.prepareStatement("SELECT * FROM book_field t where t.isbn = ? AND t.field = ?");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
retdata[1] = "Field Exists";
}
else
{
Pass = con.prepareStatement("INSERT INTO book_field(isbn,field) VALUES (?,?)");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
Pass.executeUpdate();
retdata[1] = msg3.getParam(1);
}
}//if found field code
else
{
retdata[1] = "No field code found";
}
} catch (SQLException ex)
{
System.out.println("SQL Error ERR_CODE: " + ex.getErrorCode() + " ERROR DESC: " + ex.getMessage());
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
//SEND TO CLIENT SQL EXCEPTION NOTIFAICATION
retdata[2] = "SQL Error:" + ex.getMessage();
}
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* remove author of a book
*/
if(msg3.getType().equals("***remove book author***"))
{
retdata[0] = "***remove book author***";
try
{
Pass = con.prepareStatement("DELETE FROM book_author where isbn = ? AND author = ?;");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,msg3.getParam(1));
Pass.executeUpdate();
try
{
Thread.sleep(100);
} catch (InterruptedException ex)
{
Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
}
Pass = con.prepareStatement("SELECT * FROM book_author t where t.isbn = ? AND t.author = ?");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,msg3.getParam(1));
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
retdata[1] = "Author Not Deleted";
}
else
{
retdata[1] = "Deleted";
}
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}catch (SQLException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* add author to book
* gets isbn and author name
* insert to book_author
*/
if(msg3.getType().equals("***add book author***"))
{
retdata[0] = "***add book author***";
try
{
/**
* Search if author already exist for book
*/
Pass = con.prepareStatement("SELECT * FROM book_author t where t.isbn = ? AND t.author = ?");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,msg3.getParam(1));
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
retdata[1] = "Author Exists";
}
else
{
Pass = con.prepareStatement("INSERT INTO book_author(isbn,author) VALUES (?,?)");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,msg3.getParam(1));
Pass.executeUpdate();
retdata[1] = msg3.getParam(1);
}
} catch (SQLException ex)
{
System.out.println("SQL Error ERR_CODE: " + ex.getErrorCode() + " ERROR DESC: " + ex.getMessage());
//SEND TO CLIENT SQL EXCEPTION NOTIFAICATION
retdata[2] = "SQL Error:" + ex.getMessage();
}
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* Create new book
*/
if(msg3.getType().equals("new book"))
{
retdata[0] = "new book";
Pass = con.prepareStatement("INSERT INTO book(price) VALUES (0);");
Pass.executeUpdate(); /** Creating new empty book with new ISBN and 0 price **/
Pass = con.prepareStatement("SELECT t.isbn FROM book t, (select max(isbn) maxisbn from book) t2 where t.isbn = t2.maxisbn");
sqlResult = Pass.executeQuery();
int err1 = 1;
while (sqlResult.next())
{
err1 = 0;
retdata[1] = (sqlResult.getString(1));
}
if (err1 == 1)
{
retdata[1] = "can't create book";
}
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* Get Book Field List from DB
*/
if(msg3.getType().equals("get book field list"))
{
Pass = con.prepareStatement("SELECT t.entry_desc as Genre FROM code_table t where t.table_code = 1");
sqlResult = Pass.executeQuery();
int index=0;
int err1 = 1;
retdata[index++] = "get book field list";
/**
* adding all fields to string array
*/
while (sqlResult.next())
{
err1=0;
retdata[index++] = (sqlResult.getString(1));
}
if (err1 == 1)
{
retdata[1] = "no book fields found";
}
/**
* send to client
*/
try
{
retdata[index] = "***end***";
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* Search Book from Manage Books in book table
*/
if(msg3.getType().equals("search manage book"))
{
String filter = msg3.getParam(1); // if "filter" was sent as filter
int str_index=1; // index for result string list
int inner_index=0; // index for inner looPass
String str = null;
/**
* ISBN Search
* Return book info according to it's ISBN
*/
if (filter.equals("ISBN") || filter.equals("Filter"))
{
Pass=con.prepareStatement( "SELECT * FROM book WHERE isbn = ?;");
Pass .setString(1,msg3.getParam(0));
ResultSet sqlResult2;
sqlResult = Pass.executeQuery();
/**
* we generates info string for the book
* the structure of the list:
* ***book info start***, BOOK INFO, ***book info end***,
* ***book author start***, book authors, ***book auther end***
* ***book field start***, book fields, ***book field end***
*/
while (sqlResult.next())
{
retdata[str_index++] = "***book info start***";
retdata[str_index++] = sqlResult.getString(1); //isbn
retdata[str_index++] = sqlResult.getString(2); //title
retdata[str_index++] = sqlResult.getString(3); //language
retdata[str_index++] = sqlResult.getString(4); //summery
retdata[str_index++] = sqlResult.getString(5); //contents
retdata[str_index++] = sqlResult.getString(6); //rating
retdata[str_index++] = sqlResult.getString(7); //viewed
retdata[str_index++] = sqlResult.getString(8); //purchased
retdata[str_index++] = "***book files start***";
retdata[str_index++] = sqlResult.getString(9); //pic
retdata[str_index++] = sqlResult.getString(10); //pdf
retdata[str_index++] = sqlResult.getString(11); //doc
retdata[str_index++] = sqlResult.getString(12); //fb2
retdata[str_index++] = sqlResult.getString(13); //price
retdata[str_index++] = "***book info end***";
/**
* retrieving book authors
*/
Pass=con.prepareStatement( "SELECT DISTINCT author FROM book_author WHERE isbn = ?;");
Pass .setString(1,msg3.getParam(0));
sqlResult2 = Pass.executeQuery();
retdata[str_index++] = "***book author start***";
while (sqlResult2.next())
{
retdata[str_index++] = sqlResult2.getString(1);
}
retdata[str_index++] = "***book author end***";
/**
* retrieving book fields
*/
Pass=con.prepareStatement( "SELECT ct.entry_desc FROM book_field t, code_table ct WHERE t.isbn = ? AND ct.table_code = 1 AND t.field = ct.entry_code;");
Pass .setString(1,msg3.getParam(0));
sqlResult2 = Pass.executeQuery();
retdata[str_index++] = "***book field start***";
while (sqlResult2.next())
{
retdata[str_index++] = sqlResult2.getString(1);
}
retdata[str_index++] = "***book field end***";
} //end while
} //end ISBN
/**
* Title Search
*/
if (filter.equals("Title") || filter.equals("Filter"))
{
Pass=con.prepareStatement( "SELECT DISTINCT * FROM book WHERE title like '%" + msg3.getParam(0) + "%';");
//Pass .setString(1,msg3.getParam(0));
ResultSet sqlResult2;
sqlResult = Pass.executeQuery();
/**
* for each book we find we generates info string
* the structure of the list:
* ***book info start***, BOOK INFO, ***book info end***,
* ***book author start***, book authors, ***book auther end***
* ***book field start***, book fields, ***book field end***
*/
while (sqlResult.next())
{
retdata[str_index++] = "***book info start***";
retdata[str_index++] = sqlResult.getString(1); //isbn
retdata[str_index++] = sqlResult.getString(2); //title
retdata[str_index++] = sqlResult.getString(3); //language
retdata[str_index++] = sqlResult.getString(4); //summery
retdata[str_index++] = sqlResult.getString(5); //contents
retdata[str_index++] = sqlResult.getString(6); //rating
retdata[str_index++] = sqlResult.getString(7); //viewed
retdata[str_index++] = sqlResult.getString(8); //purchased
retdata[str_index++] = "***book files start***";
retdata[str_index++] = sqlResult.getString(9); //pic
retdata[str_index++] = sqlResult.getString(10); //pdf
retdata[str_index++] = sqlResult.getString(11); //doc
retdata[str_index++] = sqlResult.getString(12); //fb2
retdata[str_index++] = sqlResult.getString(13); //price
retdata[str_index++] = "***book info end***";
/**
* retrieving book authors
*/
Pass=con.prepareStatement( "SELECT DISTINCT author FROM book_author WHERE isbn = ?;");
Pass .setString(1,sqlResult.getString(1));
sqlResult2 = Pass.executeQuery();
retdata[str_index++] = "***book author start***";
while (sqlResult2.next())
{
retdata[str_index++] = sqlResult2.getString(1);
}
retdata[str_index++] = "***book author end***";
/**
* retrieving book fields
*/
Pass=con.prepareStatement( "SELECT ct.entry_desc FROM book_field t, code_table ct WHERE t.isbn = ? AND ct.table_code = 1 AND t.field = ct.entry_code;");
Pass .setString(1,sqlResult.getString(1));
sqlResult2 = Pass.executeQuery();
retdata[str_index++] = "***book field start***";
while (sqlResult2.next())
{
retdata[str_index++] = sqlResult2.getString(1);
}
retdata[str_index++] = "***book field end***";
} //end while
} //end title
if(filter.equals("Author") || filter.equals("Filter") ){
inner_index=0;
/**
* Get isbn from book_author by author
**/
//ILAN CHECK HERE
Pass=con.prepareStatement( "SELECT distinct isbn FROM book_author WHERE author like '%" + msg3.getParam(0) + "%';");
//Pass=con.prepareStatement( "SELECT * FROM book_author WHERE author = ?;");
//Pass.setString(1,msg3.getParam(0));
sqlResult = Pass.executeQuery();
while(sqlResult.next()) // store in retdata2 isbn of all books relevant to the auther
{
retdata2[inner_index++] = (sqlResult.getString(1)) ; //isbn
} // end while sqlResult.next()
/**
* Go through all the results from retdata2(isbn's) and get book info from books
**/
for(;inner_index>=0;inner_index--)
{
Pass=con.prepareStatement( "SELECT DISTINCT * FROM book WHERE isbn = ?;");
Pass .setString(1,retdata2[inner_index]);
ResultSet sqlResult2 = Pass.executeQuery();
while(sqlResult2.next()) // store in retdata search results
{
retdata[str_index++] = "***book info start***";
retdata[str_index++] = sqlResult2.getString(1); //isbn
retdata[str_index++] = sqlResult2.getString(2); //title
retdata[str_index++] = sqlResult2.getString(3); //language
retdata[str_index++] = sqlResult2.getString(4); //summery
retdata[str_index++] = sqlResult2.getString(5); //contents
retdata[str_index++] = sqlResult2.getString(6); //rating
retdata[str_index++] = sqlResult2.getString(7); //viewed
retdata[str_index++] = sqlResult2.getString(8); //purchased
retdata[str_index++] = "***book files start***";
retdata[str_index++] = sqlResult2.getString(9); //pic
retdata[str_index++] = sqlResult2.getString(10); //pdf
retdata[str_index++] = sqlResult2.getString(11); //doc
retdata[str_index++] = sqlResult2.getString(12); //fb2
retdata[str_index++] = sqlResult2.getString(13); //price
retdata[str_index++] = "***book info end***";
/**
* retrieving book authors
*/
Pass=con.prepareStatement( "SELECT DISTINCT author FROM book_author WHERE isbn = ?;");
Pass .setString(1,retdata2[inner_index]);
sqlResult2 = Pass.executeQuery();
retdata[str_index++] = "***book author start***";
while (sqlResult2.next())
{
retdata[str_index++] = sqlResult2.getString(1);
}
retdata[str_index++] = "***book author end***";
/**
* retrieving book fields
*/
Pass=con.prepareStatement( "SELECT ct.entry_desc FROM book_field t, code_table ct WHERE t.isbn = ? AND ct.table_code = 1 AND t.field = ct.entry_code;");
Pass .setString(1,retdata2[inner_index]);
sqlResult2 = Pass.executeQuery();
retdata[str_index++] = "***book field start***";
while (sqlResult2.next())
{
retdata[str_index++] = sqlResult2.getString(1);
}
retdata[str_index++] = "***book field end***";
} // end while
}// end for
}// equals to Author
retdata[0] = "search manage book";
retdata[str_index] = "***END***";
/**
* Sending retdata back to chatclient
*/
try
{
client.sendToClient(retdata); //Send details back to client
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}//Search manage book end
/**
*********************** Manage Book End ********************
*/
/**
* *************** Manage Catalog ********************************
*/
/**
* retrieve all book catalog info
* getting isbn
* if book exist in catalog we return a list of all his fields and subjects
* we seperate fields and subjects in return list by special signals in list
*/
if(msg3.getType().equals("***get book catalog info***"))
{
int index = 1;
retdata[0] = "***get book catalog info***";
System.out.println("***get book catalog info started ***");
/**
* if book in catalog retrieve it's fields
*/
try
{
Pass=con.prepareStatement( "SELECT count(distinct t.isbn) FROM catalog_field t WHERE t.isbn = ?;");
Pass.setString(1, msg3.getParam(0));
sqlResult = Pass.executeQuery();
/**
* if book exists
*/
sqlResult.next();
if (sqlResult.getInt(1) > 0)
{
Pass=con.prepareStatement( "SELECT ct.entry_desc FROM catalog_field t, code_table ct WHERE t.isbn = ? AND ct.table_code = 7 AND t.field = ct.entry_code;");
Pass.setString(1,msg3.getParam(0));
ResultSet sqlResult2 = Pass.executeQuery();
retdata[index++] = "***catalog book field start***";
while (sqlResult2.next())
{
retdata[index++] = sqlResult2.getString(1);
}
retdata[index++] = "***catalog book field end***";
Pass=con.prepareStatement( "SELECT ct.entry_desc FROM catalog_subject t, code_table ct WHERE t.isbn = ? AND ct.table_code = 6 AND t.subject = ct.entry_code;");
Pass.setString(1,msg3.getParam(0));
sqlResult2 = Pass.executeQuery();
retdata[index++] = "***catalog book subject start***";
while (sqlResult2.next())
{
retdata[index++] = sqlResult2.getString(1);
}
retdata[index++] = "***catalog book subject end***";
}
else
{
retdata[1] = "***Book Not In Catalog***";
}
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}catch (SQLException ex)
{
System.out.println("SQL file get catalog info Error ERR_CODE: " + ex.getErrorCode() + " ERROR DESC: " + ex.getMessage());
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* retrieving entire catalog field list
* searching in code table
* catalog field code is 7
* we add the fields to a string array and send back to client
*/
if(msg3.getType().equals("***get catalog field list***"))
{
int index=0;
int err1 = 1;
retdata[index++] = "***get catalog field list***";
Pass = con.prepareStatement("SELECT t.entry_desc FROM code_table t where t.table_code = 7");
sqlResult = Pass.executeQuery();
/**
* adding all catalog fields to string array
*/
while (sqlResult.next())
{
err1=0;
retdata[index++] = (sqlResult.getString(1));
}
if (err1 == 1)
{
retdata[1] = "no catalog fields found";
}
/**
* send to client
*/
try
{
retdata[index] = "***end***";
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* add catalog field to book
* first we search for desired field code
* if we found the code
* insert the book and desired field to catalog_field
* else return error
*/
if(msg3.getType().equals("***add book catalog field***"))
{
retdata[0] = "***add book catalog field***";
try
{
/**
* getting catalog field code
*/
Pass= con.prepareStatement("SELECT t.entry_code FROM code_table t WHERE t.table_code = 7 AND t.entry_desc = ?");
Pass.setString(1, msg3.getParam(1));
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
field_code = sqlResult.getString(1);
/**
* Search if field already exist for book in catalog
*/
Pass = con.prepareStatement("SELECT * FROM catalog_field t where t.isbn = ? AND t.field = ?");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
retdata[1] = "Field Exists";
}
else
{
/**
* insert to table
*/
Pass = con.prepareStatement("INSERT INTO catalog_field(isbn,field) VALUES (?,?)");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
Pass.executeUpdate();
retdata[1] = msg3.getParam(1);
}
}//if found field code
else
{
retdata[1] = "No field code found";
}
} catch (SQLException ex)
{
System.out.println("SQL Error ERR_CODE: " + ex.getErrorCode() + " ERROR DESC: " + ex.getMessage());
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
//SEND TO CLIENT SQL EXCEPTION NOTIFAICATION
retdata[2] = "SQL Error:" + ex.getMessage();
}
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* removing book entirely from the catalog
* from each catalog table we delete the selected book
*/
if(msg3.getType().equals("***remove book from catalog***"))
{
retdata[0] = "***remove book from catalog***";
try
{
/**
* delete from:
* catalog field, catalog subject
*/
Pass = con.prepareStatement("DELETE FROM catalog_subject where isbn = ?;");
Pass.setString(1,msg3.getParam(0));
Pass.executeUpdate();
Pass = con.prepareStatement("DELETE FROM catalog_field where isbn = ?;");
Pass.setString(1,msg3.getParam(0));
Pass.executeUpdate();
retdata[1] = "Deleted";
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}catch (SQLException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
System.out.println("SQL exception code:" + ex.getErrorCode() + " MSG: " + ex.getMessage());
}
}
/**
* removing specific field from book catalog
* getting field, finding it's code.
* search for book in catalog field table
* if found delete it else return error
*/
if(msg3.getType().equals("***remove catalog book field***"))
{
retdata[0] = "***remove catalog book field***";
try
{
/**
* getting catalog field code
*/
Pass= con.prepareStatement("SELECT t.entry_code FROM code_table t WHERE t.table_code = 7 AND t.entry_desc = ?");
Pass.setString(1, msg3.getParam(1));
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
field_code = sqlResult.getString(1);
Pass = con.prepareStatement("DELETE FROM catalog_field where isbn = ? AND field = ?;");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
Pass.executeUpdate();
try
{
Thread.sleep(100);
} catch (InterruptedException ex)
{
Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
}
Pass = con.prepareStatement("SELECT * FROM catalog_field t where t.isbn = ? AND t.field = ?");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
retdata[1] = "field Not Deleted";
}
else
{
retdata[1] = "Deleted";
}
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
else
{
retdata[1] = "No field code found";
}
}catch (SQLException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* retrieving entire catalog subject list
* searching in code table
* catalog field code is 6
* we add the subject to a string array and send back to client
*/
if(msg3.getType().equals("***get catalog subject list***"))
{
int index=0;
int err1 = 1;
retdata[index++] = "***get catalog subject list***";
Pass = con.prepareStatement("SELECT t.entry_desc FROM code_table t where t.table_code = 6");
sqlResult = Pass.executeQuery();
/**
* adding all catalog fields to string array
*/
while (sqlResult.next())
{
err1=0;
retdata[index++] = (sqlResult.getString(1));
}
if (err1 == 1)
{
retdata[1] = "no catalog subjects found";
}
/**
* send to client
*/
try
{
retdata[index] = "***end***";
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* add catalog subject to book
* first we search for desired subject code
* if we found the code
* insert the book and desired subject to catalog_subject
* else return error
*/
if(msg3.getType().equals("***add catalog book subject***"))
{
retdata[0] = "***add catalog book subject***";
try
{
/**
* getting catalog subject code
*/
Pass= con.prepareStatement("SELECT t.entry_code FROM code_table t WHERE t.table_code = 6 AND t.entry_desc = ?");
Pass.setString(1, msg3.getParam(1));
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
field_code = sqlResult.getString(1);
/**
* Search if subject already exist for book in catalog
*/
Pass = con.prepareStatement("SELECT * FROM catalog_subject t where t.isbn = ? AND t.subject = ?");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
retdata[1] = "subject Exists";
}
else
{
Pass = con.prepareStatement("INSERT INTO catalog_subject(isbn,subject) VALUES (?,?)");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
Pass.executeUpdate();
retdata[1] = msg3.getParam(1);
}
}//if found field code
else
{
retdata[1] = "No subject code found";
}
} catch (SQLException ex)
{
System.out.println("SQL Error ERR_CODE: " + ex.getErrorCode() + " ERROR DESC: " + ex.getMessage());
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
//SEND TO CLIENT SQL EXCEPTION NOTIFAICATION
retdata[2] = "SQL Error:" + ex.getMessage();
}
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* removing specific subject from book catalog
* getting subject, finding it's code.
* search for book in catalog subject table
* if found delete it else return error
*/
if(msg3.getType().equals("***remove catalog book subject***"))
{
retdata[0] = "***remove catalog book subject***";
try
{
/**
* getting catalog subject code
*/
Pass= con.prepareStatement("SELECT t.entry_code FROM code_table t WHERE t.table_code = 6 AND t.entry_desc = ?");
Pass.setString(1, msg3.getParam(1));
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
field_code = sqlResult.getString(1);
Pass = con.prepareStatement("DELETE FROM catalog_subject where isbn = ? AND subject = ?;");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
Pass.executeUpdate();
try
{
Thread.sleep(100);
} catch (InterruptedException ex)
{
Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
}
Pass = con.prepareStatement("SELECT * FROM catalog_subject t where t.isbn = ? AND t.subject = ?");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,field_code);
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
retdata[1] = "subject Not Deleted";
}
else
{
retdata[1] = "Deleted";
}
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
else
{
retdata[1] = "No subject code found";
}
}catch (SQLException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* *************** Manage Catalog End********************************
*/
/**
* *************** Account Settinga ********************************
*/
/**
* checking user name if exists in user table
*
*/
if(msg2[0].equals("checkAcc"))
{
ResultSet rs2 = stmt.executeQuery( "SELECT * FROM User WHERE Username = \"" + msg3.getParam(0) + "\" ;");
retdata[0] = "checkAcc";
if(rs2.next()){
retdata[1] = (rs2.getString(1));
//System.out.println("Message received: " + retdata[1] + " from " + client);
}
else
{
retdata[1] = "empty";
//System.out.println("Message received: " + retdata[1] + " from " + client);
}
try {
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end if check
/**
* updating user name according to old user name
*/
if(msg3.getType().equals("***update user name***"))
{
retdata[0] = "***update user name***";
try
{
Pass=con.prepareStatement("UPDATE user SET username = ? WHERE username = ?;");
Pass.setString(1,msg3.getParam(1));
Pass.setString(2,msg3.getParam(0));
Pass.executeUpdate();
retdata[1] = "Updated";
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
} catch(SQLException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
System.out.println("SQL insert book exception code:" + ex.getErrorCode() + " MSG: " + ex.getMessage());
}
}
/**
* update user password according to user name
*/
if(msg3.getType().equals("***update user pass***"))
{
retdata[0] = "***update user pass***";
try
{
Pass=con.prepareStatement("UPDATE user SET password = ? WHERE username = ?;");
Pass.setString(1,msg3.getParam(1));
Pass.setString(2,msg3.getParam(0));
Pass.executeUpdate();
retdata[1] = "Updated";
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
} catch(SQLException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
System.out.println("SQL insert book exception code:" + ex.getErrorCode() + " MSG: " + ex.getMessage());
}
}
/**
* check if account exist to specific user
*/
if(msg2[0].equals("*** check Account State ***"))
{
retdata[0] = "*** check Account State ***";
ResultSet rs2 = stmt.executeQuery( "SELECT * FROM account WHERE Username = \"" + msg3.getParam(0) + "\" ;");
if(rs2.next())
{
retdata[1] = (rs2.getString(1));
System.out.println("Account Check: " + retdata[1]);
}
else
{
retdata[1] = "empty";
}
try
{
client.sendToClient(retdata); //Sent details back to client
} catch (IOException ex) {
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}// end if check
/**
* creating account for user
* first we check if account already exist if not we create
*/
if(msg3.getType().equals("***create account***"))
{
retdata[0] = "***create account***";
try
{
/** Checking if user already in accounts **/
Pass = con.prepareStatement("SELECT * FROM account t where Username = ?;");
Pass.setString(1,msg3.getParam(0));
sqlResult = Pass.executeQuery();
if (sqlResult.next())
{
retdata[1] = "User Have Account";
}
else
{
Pass = con.prepareStatement("INSERT INTO account(username,account_type,book_left,details,active) VALUES (?,0,0,?,0)");
Pass.setString(1,msg3.getParam(0));
Pass.setString(2,msg3.getParam(1));
Pass.executeUpdate();
retdata[1] = msg3.getParam(0);
}
} catch (SQLException ex)
{
System.out.println("SQL Error ERR_CODE: " + ex.getErrorCode() + " ERROR DESC: " + ex.getMessage());
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
//SEND TO CLIENT SQL EXCEPTION NOTIFAICATION
retdata[2] = "SQL Error:" + ex.getMessage();
}
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* *************** Account Settings ********************************
*/
}// end if instanseof obj
}//instance of Strin[]
if (msg instanceof ArrayList)
{
ArrayList msgfile = (ArrayList) msg;
/**
************************* Handle Uplaoding files to DB ************************************
*/
System.out.println("Message received: " + msgfile.get(0) + " " + msgfile.get(3) + " from " + client );
if (msgfile.get(0).equals("***Uplaod File***"))
{
retdata[0] = "***Uplaod File***";
int length;
String str;
InputStream is = new ByteArrayInputStream((byte[]) msgfile.get(4));
try
{
Pass=con.prepareStatement( "SELECT count(t.isbn) FROM book_files t WHERE t.isbn = ?;");
str = (String )msgfile.get(1);
Pass.setString(1, str);
sqlResult = Pass.executeQuery();
/**
* if book exists
*/
sqlResult.next();
if (sqlResult.getInt(1) == 0)
{
//if book has no files yet we add it to file table
Pass = con.prepareStatement("INSERT INTO book_files(isbn) VALUES (?)");
Pass.setString(1,str);
Pass.executeUpdate();
}
if (((String) msgfile.get(2)).equals("pdf"))
{
Pass = con.prepareStatement("UPDATE book_files SET pdf = ? WHERE isbn = ?;");
}
else
if (((String) msgfile.get(2)).equals("doc"))
{
Pass = con.prepareStatement("UPDATE book_files SET doc = ? WHERE isbn = ?;");
}
else
if (((String) msgfile.get(2)).equals("fb2"))
{
Pass = con.prepareStatement("UPDATE book_files SET fb2 = ? WHERE isbn = ?;");
}
else Pass = con.prepareStatement("UPDATE book_files SET pic = ? WHERE isbn = ?;");
length = Integer.parseInt(msgfile.get(5).toString());
Pass.setBinaryStream(1,is, length);
str = (String )msgfile.get(1);
Pass.setString(2, str);
Pass.executeUpdate();
retdata[1] = "file updated";
try
{
client.sendToClient(retdata);
} catch (IOException ex)
{
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}catch (SQLException ex)
{
System.out.println("SQL file insert Error ERR_CODE: " + ex.getErrorCode() + " ERROR DESC: " + ex.getMessage());
Logger.getLogger(EchoServer.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}// end try
catch (SQLException ex)
{/* handle any errors*/
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}// end catch
} //end handleMessageFromClient
/**
* This method overrides the one in the superclass. Called
* when the server starts listening for connections.
*/
protected void serverStarted()
{
System.out.println
("Server listening for connections on port " + getPort());
}
/**
* This method overrides the one in the superclass. Called
* when the server stops listening for connections.
*/
protected void serverStopped()
{
System.out.println
("Server has stopped listening for connections.");
}
//Class methods ***************************************************
/**
* This method is responsible for the creation of
* the server instance (there is no UI in this phase).
*
* @param args[0] The port number to listen on. Defaults to 5555
* if no argument is entered.
*/
/* On setup clear online status */
protected void ResetOnline() throws SQLException
{
Statement stmt;
try{
stmt = con.createStatement();
int rs = stmt.executeUpdate( "UPDATE User SET Online=0;");
}
catch (SQLException esx)
{/* handle any errors*/
System.out.println("SQLException: " + esx.getMessage());
System.out.println("SQLState: " + esx.getSQLState());
System.out.println("VendorError: " + esx.getErrorCode());
}
}// end class
/* Vitali 2*/
public int AddToResult(ResultSet rs12,String[] retdata,int index1)
{
try {
while(rs12.next()) // store in retdata search results
{
if(rs12.getString(6).equals("0")){ // get only books that are not already used
String tmp = (rs12.getString(1)); // isbn
retdata[index1++] = tmp ; //isbn
retdata[index1++] = (rs12.getString(2)); // title
retdata[index1++] = (rs12.getString(3)); // language
retdata[index1++] = (rs12.getString(4)); //rating
retdata[index1++] = (rs12.getString(5)); // price
PreparedStatement ps;
ps=con.prepareStatement( "UPDATE book SET used = 1 WHERE isbn = ?;");
ps.setString(1,tmp);
ps.executeUpdate();
}// end if used check
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}// end while
return index1;
}
public int AddToResult2(ResultSet rs12,String[] retdata,int index1)
{
try {
while(rs12.next()) // store in retdata search results
{
String tmp = (rs12.getString(1)); // isbn
if(rs12.getString(6).equals("1")){ // get only books that are not already used
retdata[index1++] = tmp ; //isbn
retdata[index1++] = (rs12.getString(2)); // title
retdata[index1++] = (rs12.getString(3)); // language
retdata[index1++] = (rs12.getString(4)); //rating
retdata[index1++] = (rs12.getString(5)); // price
//*********bug with 3 and in testing, after founded result that not
// been in the other search need to set USED to 2 to fix the bug
PreparedStatement ps;
ps=con.prepareStatement( "UPDATE book SET used = 2 WHERE isbn = ?;");
ps.setString(1,tmp);
ps.executeUpdate();
}
}
//*********bug with 3 and in testing, after founded result that not
// been in the other search need to set USED to 1 if 2
// and 0 where 1
PreparedStatement ps;
ps=con.prepareStatement( "UPDATE book SET used = 0 WHERE used = 1;");
ps.executeUpdate();
ps=con.prepareStatement( "UPDATE book SET used = 1 WHERE used = 2;");
ps.executeUpdate();
//end FIT FIX
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}// end while
return index1;
}
public int AddToResultView(ResultSet rs,String[] retdata,int index1) throws SQLException
{
while(rs.next()) // store in retdata search results
{
retdata[index1++] = (rs.getString(1)) ; //isbn
}
return index1;
}
public ResultSet ASqueryEXE(String choice,String param) throws SQLException
{
String query = null;
if( choice.equals("Title"))
{
query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b,catalog_field cf WHERE cf.isbn = b.isbn AND b.title LIKE '";
}// equals to Title
if(choice.equals("Author")){
/*
* Get isbn by author
*/
query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b,book_author ba,catalog_field cf WHERE b.isbn = cf.isbn AND ba.isbn = b.isbn AND ba.author LIKE '" ;
}// equals to Author
if(choice.equals("Keyword")){
/*
* Get isbn by keyword
*/
query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b,keyword k,catalog_field cf WHERE b.isbn = cf.isbn AND k.isbn = b.isbn AND k.keyword LIKE '";
}// equals to Keyword
if(choice.equals("Field")){
/*
* Get isbn by Field
*/
query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b, catalog_field cf, code_table ct WHERE b.isbn = cf.isbn AND ct.table_code = 7 AND cf.field = ct.entry_code and ct.entry_desc LIKE '";
}// equals to Field
if(choice.equals("Subject") ){
/*
* Get isbn by Subject
*/
query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b,catalog_subject cs, code_table ct WHERE b.isbn = cs.isbn AND ct.table_code = 6 AND cs.subject = ct.entry_code AND ct.entry_desc LIKE '";
}// equals to Subject
if(choice.equals("Language")){
/*
* Get isbn by Language
*/
query = "SELECT DISTINCT b.isbn,b.title,b.language,b.rating,b.price,b.used FROM book b,catalog_field cf WHERE b.isbn = cf.isbn AND b.language LIKE '";
}// equals to Language
PreparedStatement ps;
query = query.concat("%");
query = query.concat(param);
query = query.concat("%';");
ps=con.prepareStatement(query);
return ps.executeQuery();
}
public ResultSet queryEXES(String query,String param) throws SQLException
{
query = query.concat(param);
query = query.concat("%';");
PreparedStatement ps;
ps=con.prepareStatement(query);
return ps.executeQuery();
}
public ResultSet queryEXE(String query,String param) throws SQLException
{
PreparedStatement ps;
ps=con.prepareStatement(query);
ps.setString(1,param);
return ps.executeQuery();
}
/* end vitali 2*/
public static void main(String[] args)
{
int port = 0; //Port to listen on
ServerOnline server = new ServerOnline();
//this.setVisible(false);
server.setVisible(true);
try
{
port = Integer.parseInt(args[0]); //Get port from command line
}
catch(Throwable t)
{
port = DEFAULT_PORT; //Set port to 5555
}
EchoServer sv = new EchoServer(port);
try
{
sv.listen(); //Start listening for connections
}
catch (Exception ex)
{
System.out.println("ERROR - Could not listen for clients!");
}
}
}
//End of EchoServer class