package system.util;

import market.servlets.*;
import system.util.*;
import java.net.*;
import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.lang.*;
import java.sql.*;
/**
 * This is test class
 * @author: Nikolai Rangelov
 */
public class test {
	public static Connection rwdbConn = null;

	public final static String db_Drivers = "org.gjt.mm.mysql.Driver";
	public final static String db_user = "root";
	public final static String db_password = "";
	public final static String db_host = "jdbc:mysql://www.server.esof:3306/";
 	public final static String db_rwServerDB = "rw";
/**
 * test constructor comment.
 */
public test() {
	super();
}
/**
 * Insert the method's description here.
 * @param args java.lang.String[]
 */
public static void main(String[] args) {
/*	long ret;
	try {
		System.out.println("1.RW is : " + rwdbConn.isClosed());
	} catch (Exception e) {
		System.out.println("1.Conn is closed." + e);
	};
*/	rwdbConnect();
/*	try {
		System.out.println("2.RW is : " + rwdbConn.isClosed());
	} catch (Exception e) {
		System.out.println("2.Conn is closed." + e);		
	};
	
	String Table = "rw_a";
	long rwKey = 0;
	String rwString = "~!@#$%^&*()_+\\df\"dfdfd";
	short langKey = 2;
	long rwCounts = 1;
	long Date = 123234344;
	try {
		System.out.println("3.RW is : " + rwdbConn.isClosed());
	} catch (Exception e) {
		System.out.println("3.Conn is closed." + e);		
	};
	rwKey = Search(rwString, langKey, Table);
	if (rwKey == Constants.No_Key)
		Register(55555, rwString, langKey, rwCounts, Table);
	else
		Update(rwKey, rwCounts, Date, Table);
	rwKey = PrepareSearch(rwString, langKey, Table);
	if (rwKey == Constants.No_Key)
		Register(55555, rwString, langKey, rwCounts, Table);
	else
		PrepareUpdate(rwKey, rwCounts, Date, Table);
*/

	String Table = "rw_z";
	long rwKey = 0;
	String rwString = "fg\\=-`[]:\"?><,./';[]"; 					//"~!@#$%^&*()_+\\df\"dfdf{d";
	short langKey = 2;
	long rwCounts = 1;
	long Date = 123234344;


Register(5, rwString, langKey, rwCounts, Table);
System.out.println("The res is : " + Search(rwString, langKey, Table));
rwdbDisConnect();

String Str = "dsfgsdfg\\sdfg\"";
System.out.println("The string is : " + Str + "\nThe result is : " + Constants.DBString(Str));

/*
System.out.print("Now is your turn : ");
String str = null;
BufferedReader in
   = new BufferedReader(new InputStreamReader(System.in));
try {
	
str = in.readLine();
}
catch(IOException e){}
System.out.print("The result    is : " + Constants.DBString(str));
*/

	
}
private static boolean PrepareRegister(long RrwKey, String RrwString, short RlangKey, long RrwCounts, String RrwTable) {
	String SQLStat = "INSERT INTO " + RrwTable + " (" + dbFields.db_rwKey + ", " + dbFields.db_langKey + ", " + dbFields.db_rwString + ", " + dbFields.db_rwRegDate + ", " + dbFields.db_rwLastCount + ", " + dbFields.db_rwSumAllCounts + ", " + dbFields.db_rwSumAllVolatility + ", " + dbFields.db_rwSumAllVulgarity + ", " + dbFields.db_volatilityUpdate + ", " + dbFields.db_vulgarityUpdate + " ) values (?,?,?,?,?,?,?,?,?,?)";
	try {
		PreparedStatement stmt = rwdbConn.prepareStatement(SQLStat);
		stmt.clearParameters();
		stmt.setLong(1, RrwKey);
		stmt.setShort(2, RlangKey);
		stmt.setString(3, RrwString);
		stmt.setDate(4, new java.sql.Date(System.currentTimeMillis()));
		stmt.setDate(5, new java.sql.Date(System.currentTimeMillis()));
		stmt.setLong(6, RrwCounts);
		stmt.setInt(7, 0);
		stmt.setInt(8, 0);
		stmt.setDate(9, new java.sql.Date(System.currentTimeMillis()));
		stmt.setDate(10, new java.sql.Date(System.currentTimeMillis()));
		int Q_res = stmt.executeUpdate();
		if (Q_res == 0) {
			System.out.println("Register : Cannot register rw with rwKey - " + RrwKey + ", rwString - " + RrwString + ", langKey - " + RlangKey);
			return false;
		}
		System.out.println("Register : RegisterRw - info inserted.");
		return true;
	} catch (SQLException e) {
		System.out.println("Register : error sql - " + SQLStat);
		return false;
	} catch (NumberFormatException e) {
		System.out.println("Register : error format - " + SQLStat);
		return false;
	}
}
private static long PrepareSearch(String prwString, short plangKey, String prwTable) {
	long res_rwKey = Constants.No_Key;
	String SQLStat = "SELECT " + dbFields.db_rwKey + " FROM " + prwTable + " WHERE " + dbFields.db_rwString + " LIKE ? AND " + dbFields.db_langKey + " = ?";
	try {
		PreparedStatement stmt = rwdbConn.prepareStatement(SQLStat);
		stmt.clearParameters();
		stmt.setString(1, prwString);
		stmt.setShort(2, plangKey);
		ResultSet RS = stmt.executeQuery();
		if (RS.next()) {
			res_rwKey = RS.getLong(1);
			System.out.println("PrepareSearch : ResolveString returns : " + res_rwKey);
			RS.close();
			return res_rwKey;
		} else {
			System.out.println("PrepareSearch : Cannot find such rwString - " + prwString + " with langKey - " + plangKey);
			RS.close();
			return Constants.No_Key;
		}
	} catch (SQLException E) {
		System.out.println("PrepareSearch : SQLException : " + E + "\n" + E.getMessage());
		return Constants.No_Key;
	}
}
public static void PrepareUpdate(long Key,long Count,long Date, String Table) {
	String SQLStat = "UPDATE " + Table + " SET " + dbFields.db_rwSumAllCounts + " = " + dbFields.db_rwSumAllCounts + " + ?, " + dbFields.db_rwLastCount + " = ? WHERE " + dbFields.db_rwKey + " = ?";
	try {
		PreparedStatement stmt = rwdbConn.prepareStatement(SQLStat);
		stmt.clearParameters();
		stmt.setLong(1, Count);
		stmt.setDate(2, new java.sql.Date(Date));	
		stmt.setLong(3, Key);	
		int Q_res = stmt.executeUpdate();
		if(Q_res == 0)
		{
			System.out.println("PrepateUpdate : None record updated. See Key = " + Key);		
		}
		if(Q_res != 1)
		{
			System.out.println("PrepateUpdate : More than one record updated. See Key = " + Key);
		}
	} catch (SQLException e) {
		System.out.println("PrepateUpdate : error sql - " + SQLStat);
	} catch (NumberFormatException e) {
		System.out.println("PrepareUpdate  : error format - " + SQLStat);
	}
	System.out.println("PrepareUpdate  : info updated.");
}
private static boolean Register(long RrwKey, String RrwString, short RlangKey, long RrwCounts, String RrwTable) {
	java.sql.Date N = new java.sql.Date(System.currentTimeMillis());
	char Quote = '\'';
	String SQLStat = "INSERT INTO " + RrwTable + " (" + dbFields.db_rwKey + ", " + dbFields.db_langKey + ", " + dbFields.db_rwString + ", " + dbFields.db_rwRegDate + ", " + dbFields.db_rwLastCount + ", " + dbFields.db_rwSumAllCounts + ", " + dbFields.db_rwSumAllVolatility + ", " + dbFields.db_rwSumAllVulgarity + ", " + dbFields.db_volatilityUpdate + ", " + dbFields.db_vulgarityUpdate + " ) values (" + RrwKey + "," + RlangKey + "," + Constants.DBString(RrwString) + "," + Quote + N + Quote + "," + Quote + N + Quote + "," + RrwCounts + "," + 0 + "," + 0 + "," + Quote + N + Quote + "," + Quote + N + Quote + ")";
	try {
		Statement Stmt = rwdbConn.createStatement();
		int RS = Stmt.executeUpdate(SQLStat);
		System.out.println("SQLStat : " + SQLStat);
		System.out.println("The result from registration of " + RrwString + " is : " + RS);
		Stmt.close();
		return true;
	} catch (SQLException e) {
		System.out.println("Register : error sql - " + SQLStat);
		return false;
	} catch (NumberFormatException e) {
		System.out.println("Register : error format - " + SQLStat);
		return false;
	}
}
public static boolean rwdbConnect() {
	// load mysql driver
	try {
		Class.forName(db_Drivers).newInstance();
		System.out.println("got a driver to rwServer.");
	} catch (Exception E) {
		System.err.println("Unable to load a driver to rwServer DB.");
		E.printStackTrace();
		return false;
	}
	try {
		rwdbConn = DriverManager.getConnection(db_host + db_rwServerDB, db_user, db_password);		
		System.out.println("Conection to rw DB Established.");
	} catch (SQLException E) {
		System.out.println("SQLException: " + E.getMessage());
		System.out.println("SQLState:     " + E.getSQLState());
		System.out.println("VendorError:  " + E.getErrorCode());
		return false;
	}
	return true;
}
public static void rwdbDisConnect() {
	try {
		 rwdbConn.close();
		} 
	catch(Exception e) {
		 System.out.println("cannot close the db");
	    }
	System.out.println("market_rw DB closed");
}
private static long Search(String prwString, short plangKey, String prwTable) {
	long res_rwKey = Constants.No_Key;
	String SQLStat = "SELECT " + dbFields.db_rwKey + " FROM " + prwTable + " WHERE " + dbFields.db_rwString + " = " + Constants.DBString(prwString) + " AND " + dbFields.db_langKey + " = " + String.valueOf(plangKey);
	try {
		Statement Stmt = rwdbConn.createStatement();
		ResultSet RS = Stmt.executeQuery(SQLStat);
		if (RS.next()) {
			res_rwKey = RS.getLong(1);
			System.out.println("Search : ResolveString for    : " + prwString + "\nreturns : " + res_rwKey);
			while(RS.next()) {
				System.out.println("Search : ERROR IN THE DB ONE MORE ROW : ResolveString for    : " + prwString + "again appeared returns : " + RS.getLong(1));
			}
			RS.close();
			Stmt.close();
			return res_rwKey;
		} else {
			RS.close();
			Stmt.close();
			System.out.println("Search : Cannot find rwString - " + prwString + " with langKey - " + plangKey);
			return Constants.No_Key;
		}
	} catch (SQLException E) {
		System.out.println("Search : SQLException : " + E + "\n" + E.getMessage());
		return Constants.No_Key;
	}
}
public static void Update(long Key, long Count, long Date, String Table) {
	String SQLStat = "UPDATE " + Table + " SET " + dbFields.db_rwSumAllCounts + " = " + dbFields.db_rwSumAllCounts + " + " + String.valueOf(Count) + " , " + dbFields.db_rwLastCount + " = '" + (new java.sql.Date(Date)) + "' WHERE " + dbFields.db_rwKey + " = " + String.valueOf(Key);
	try {
		PreparedStatement stmt = rwdbConn.prepareStatement(SQLStat);
		stmt.executeUpdate();
	} catch (SQLException e) {
		System.out.println("Update : error sql - " + SQLStat);
	} catch (NumberFormatException e) {
		System.out.println("Update  : error format - " + SQLStat);
	}
}
}