Ranking Online

[KaL]

Well...I'm developing a system the changes the color name/title of the top character of the server ( The PvP Top, Pk Top, Level Top, Recommend Top, Online Top, Clan Level ( Only for Leader ) Top, & Clan Reputation Top ( Only for Leader Too )...

 

It is working fine...but I need some help with the queries...look at my codes :

Obs.: The Config. options are already set...

 

if(Config.STAT_COLOR && !activeChar.isGM())
	{			
		Connection con = null;
		PcAppearance appearance = activeChar.getAppearance();
	try
	{
		con = L2DatabaseFactory.getInstance().getConnection();
		PreparedStatement level = con.prepareStatement("SELECT charId,level FROM characters WHERE accesslevel < 10 ORDER BY level DESC LIMIT 1");
		PreparedStatement pvp = con.prepareStatement("SELECT charId,pvpkills FROM characters WHERE accesslevel < 10 ORDER by pvpkills DESC LIMIT 1");
		PreparedStatement pk = con.prepareStatement("SELECT charId,pkkills FROM characters WHERE accesslevel < 10 ORDER BY pkkills DESC LIMIT 1");
		PreparedStatement rec = con.prepareStatement("SELECT charId,rec_have FROM characters WHERE accesslevel < 10 ORDER BY rec_have DESC LIMIT 1");
		PreparedStatement on = con.prepareStatement("SELECT charId,onlinetime FROM characters WHERE accesslevel < 10 ORDER BY onlinetime DESC LIMIT 1");
		PreparedStatement clevel = con.prepareStatement("SELECT clan_level,leader_id FROM clan_data ORDER BY clan_level DESC LIMIT 1");
		PreparedStatement crep = con.prepareStatement("SELECT leader_id,reputation_score FROM clan_data ORDER BY reputation_score DESC LIMIT 1");
		ResultSet rlevel = level.executeQuery();
		ResultSet rpvp = pvp.executeQuery();
		ResultSet rpk = pk.executeQuery();
		ResultSet rrec = rec.executeQuery();
		ResultSet ron = on.executeQuery();
		ResultSet rclevel = clevel.executeQuery();
		ResultSet rcrep = crep.executeQuery();
	while(rlevel.next())
	{
	if(Config.STAT_LEVEL && activeChar.getObjectId() == rlevel.getInt("charId") && activeChar.getLevel() == rlevel.getInt("level"))
	{
		if(Config.LEVEL_NAME_COLOR)
			appearance.setNameColor(Config.NAME_COLOR_LEVEL);
		if(Config.LEVEL_TITLE_COLOR)
			appearance.setTitleColor(Config.TITLE_COLOR_LEVEL);
		activeChar.sendMessage("Congratulations! You are the Top Level!");
	}
	}
		level.close();
		rlevel.close();
	while(rpvp.next())
	{
	if(Config.STAT_PVP && activeChar.getObjectId() == rpvp.getInt("charId") && activeChar.getPvpKills() == rpvp.getInt("pvpkills"))
	{
		if(Config.PVP_NAME_COLOR)
			appearance.setNameColor(Config.NAME_COLOR_PVP);
		if(Config.PVP_TITLE_COLOR)
			appearance.setTitleColor(Config.TITLE_COLOR_PVP);
		activeChar.sendMessage("Congratulations! You are the Top PvP!");
	}
	}
		pvp.close();
		rpvp.close();
	while(rpk.next())
	{
	if(Config.STAT_PK && activeChar.getObjectId() == rpk.getInt("charId") && activeChar.getPkKills() == rpk.getInt("pkkills"))
	{
		if(Config.PK_NAME_COLOR)
			appearance.setNameColor(Config.NAME_COLOR_PK);
		if(Config.PK_TITLE_COLOR)
			appearance.setTitleColor(Config.TITLE_COLOR_PK);
		activeChar.sendMessage("Congratulations! You are the Top Pk!");
	}
	}
		pk.close();
		rpk.close();
	while(rrec.next())
	{
	if(Config.STAT_REC && activeChar.getObjectId() == rrec.getInt("charId") && activeChar.getRecomHave() == rrec.getInt("rec_have"))
	{
		if(Config.REC_NAME_COLOR)
			appearance.setNameColor(Config.NAME_COLOR_REC);
		if(Config.REC_TITLE_COLOR)
			appearance.setTitleColor(Config.TITLE_COLOR_REC);
		activeChar.sendMessage("Congratulations! You are the Top Recommend!");
	}
	}
		rec.close();
		rrec.close();
	while(ron.next())
	{
	if(Config.STAT_ONLINE && activeChar.getObjectId() == ron.getInt("charId") && activeChar.getOnlineTime() == ron.getLong("onlinetime"))
	{
		if(Config.ONLINE_NAME_COLOR)
			appearance.setNameColor(Config.NAME_COLOR_ONLINE);
		if(Config.ONLINE_TITLE_COLOR)
			appearance.setTitleColor(Config.TITLE_COLOR_ONLINE);
		activeChar.sendMessage("Congratulations! You are the Top Online!");
	}
	}
		on.close();
		ron.close();
	while(rclevel.next())
	{
	if(Config.STAT_CLAN && activeChar.getClan() != null)
	{
	if(activeChar.getClan().getLeaderId() == rclevel.getInt("leader_id") && activeChar.getClan().getLevel() == rclevel.getInt("clan_level"))
	{
		if(Config.CLEVEL_NAME_COLOR)
			appearance.setNameColor(Config.NAME_COLOR_CLEVEL);
		if(Config.CLEVEL_TITLE_COLOR)
			appearance.setTitleColor(Config.TITLE_COLOR_CLEVEL);
		activeChar.sendMessage("Congratulations! Your clan is the Top Level!");
	}
	}
	}
		clevel.close();
		rclevel.close();
	while(rcrep.next())
	{
	if(Config.STAT_REP && activeChar.getClan() != null)
	{
	if(activeChar.getClan().getLeaderId() == rcrep.getInt("leader_id") && activeChar.getClan().getReputationScore() == rcrep.getInt("reputation_score"))
	{
		if(Config.CREP_NAME_COLOR)
			appearance.setNameColor(Config.NAME_COLOR_CREP);
		if(Config.CREP_TITLE_COLOR)
			appearance.setTitleColor(Config.TITLE_COLOR_CREP);
		activeChar.sendMessage("Congratulations! Your clan is the Top Reputation!");
	}
	}
	}
		crep.close();
		rcrep.close();

		con.close();
	}		
	catch (SQLException e)
	{
		_log.warn("Could not get Stat Ranking from Database!");
	}
	finally
	{
		try
		{
			con.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}	
	}

 

I want to make it doesn't select already selected chars, for exemple, if the Top PvP is the Top Recommend too, the system gets him as Top PvP, and goes to the next Top Recommend...at this way, it doesn't eliminate any ranking...but how can I make it select an array that comes after the code?

 

Look to this part :

 

PreparedStatement level = con.prepareStatement("SELECT charId,level FROM characters WHERE accesslevel < 10 ORDER BY level DESC LIMIT 1");

PreparedStatement pvp = con.prepareStatement("SELECT charId,pvpkills FROM characters WHERE accesslevel < 10 AND charId!="+rlevel.getInt("charId")+"ANDR charId!="+rpk.getInt("charId")+"AND charId!="+rrec.getInt("charId")+"AND charId!="+ron.getInt("charId")+"ORDER by pvpkills DESC LIMIT 1");

 

But it doesn't works because the queries that must be selected comes after...I could do it only in "Online Ranking" putting the Result set after each statement, but the other rankings will not be able to identify the charId of the query below...

 

I also tried using "else if" and putting all "whiles" together "while(rlevel.next() && rpvp.next()......)" but didn't set any color

 

If somebody didn't understand, please, tell me that I try to explain better...

[daredevil911]

how can i input this on my server ?!?!?! :? :?

[rodox]

This is not complete, it is java code - you must have source code to add it.

[LordOfDarkness]

i have source code but how to add?

[Matim]

i have source code but how to add?

 

Read few guides firstly, everything is explained.