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);