Richest Players Rankings

Solved :)

/thread.

Just change reborns to mesos

Quote:

---

Originally Posted by Dichotome

Just change reborns to mesos

---

That would be mesos rankings. I mean for high rates. Like say my currency item is gold sacks.
I want it to get the total number of gold sacks of every player and then sort it highest to lowest displaying only the top 10.

So basically I need a query that gets the characters name, and the id number, and then uses that to check inventory items for the currency item eg. chicken, gold sack, w.e. and then get the quantity and sort it in ascending order.

Accumulate the amount (ex. 50 of 500 Maple Leaves = 25000 Maple Leaves) from the item table and sort it that way.

Or you could do a less recommended way of making it so that every time a Maple Leaf, it adds a +1 or -1 in a column in the characters table.

Or, make a script run every few minutes checking how many Maple Leaves the user has and update it on the characters table.

Quote:

---

Originally Posted by maplefreak26

Accumulate the amount (ex. 50 of 500 Maple Leaves = 25000 Maple Leaves) from the item table and sort it that way.

Or you could do a less recommended way of making it so that every time a Maple Leaf, it adds a +1 or -1 in a column in the characters table.

Or, make a script run every few minutes checking how many Maple Leaves the user has and update it on the characters table.

---

Is there an sql query I could use that would be more simpler than doing that? Like checking the inventory items column in mysql?

Why are you doing the database work in a script...?

Just select the itemid from inventoryitems, and order them by character. After that, sum them all together if the player has multiple slots of the item.

Quote:

---

Originally Posted by xArmani

Why are you doing the database work in a script...?

Just select the itemid from inventoryitems, and order them by character. After that, sum them all together if the player has multiple slots of the item.

---

That's what I was trying to do.
And I prefer doing it in a script than a command.
So what would the query to that be?

bump ..

I'm sure somone could do that better, but that's some quick solution I made:

Add this to NPCConversationManager

PHP Code:

---

    private LinkedList rankIDs = new LinkedList();
    private LinkedList rankQuantity = new LinkedList();
    private LinkedList rankNames = new LinkedList();    
    
    public void setItemRanks(int itemid) {
        rankIDs.clear();
        rankQuantity.clear();
        rankNames.clear();
        
        try {
            Connection con = DatabaseConnection.getConnection();
            PreparedStatement ps = con.prepareStatement("SELECT characterid, quantity FROM inventoryitems WHERE itemid = ? ORDER BY quantity desc LIMIT 10");
            ps.setInt(1, itemid);
            
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                rankIDs.add(rs.getInt("characterid"));
                rankQuantity.add(rs.getInt("quantity"));
                System.out.println("Adding: " + rs.getInt("characterid") + ", " + rs.getInt("quantity"));
            }
            
            rs.close();
            ps.close();
            
            String statement = "SELECT id, name FROM characters WHERE ";
            for (int x = 0; x < rankIDs.size(); x++) {
                statement += "id = " + rankIDs.get(x) + " OR ";
                System.out.println("String statement: " + statement);
            }
            statement = statement.substring(0, statement.length() - 4);
            System.out.println("Final string statement: " + statement);
            
            ps = con.prepareStatement(statement);
            rs = ps.executeQuery();
            while (rs.next()) {
                rankNames.add(rs.getInt("id"));
                rankNames.add(rs.getString("name"));
                System.out.println("Adding: " + rs.getInt("id") + ", " + rs.getString("name"));
            }
            
            System.out.println("List sizes: IDs " + rankIDs.size() + ", Quantities " + rankQuantity.size() + ", Names+IDs " + rankNames.size());
                        
            rs.close();
            ps.close();
                
        } catch (SQLException e) {
            System.out.println("Error creaing rankd stuffz" + e);
        }
    }
    
    public String showItemRanking() {
        String msg = "#eTranquilityStory Richest Player Rankings\r\n\r\n#n";
        int rank = 1; 
        
        for (int x = 0; x < rankIDs.size(); x++) {
            int id = Integer.parseInt("" + rankIDs.get(x));
            int quantity = Integer.parseInt("" + rankQuantity.get(x));
            int indexName = Integer.parseInt("" + rankNames.indexOf(id)) + 1;
            String name = "" + rankNames.get(indexName);
            msg += (rank + ") " + name + " (Amount: " + quantity + ")\r\n");
            rank++;
        }
            
        return msg;
    } 


---

(Remove the prints. Adding them just to check on what's happening in every step in case something's not working.)

NPC Script:

PHP Code:

---

var status = -1;

function action(mode, type, selection) {
    cm.setItemRanks(4000313); //took golden leaves for testing. 
    cm.sendOk(cm.showItemRanking());
    cm.dispose();
} 


---

It's somewhat similar to the reborn rankings, except for where to get the information from. What I basically did is getting the amount of a certain item from 'inventoryitems' and the characterids it belongs to. With the characterid got the names.

Only problem is, that it won't update the rankings everytime someone's amount increases, unless you save the amount everytime (which I don't think is recommended).
It updates individual amount as soon as it gets saved.

A better way of doing it might be by loading everyone's amount upon server start and edit the list.

EDIT: I didn't check for multiple slots of the same item, but just sum them up as xArmani said.

Quote:

---

Originally Posted by LikeABaws

I'm sure somone could do that better, but that's some quick solution I made:

Add this to NPCConversationManager

PHP Code:

---

    private LinkedList rankIDs = new LinkedList();
    private LinkedList rankQuantity = new LinkedList();
    private LinkedList rankNames = new LinkedList();    
    
    public void setItemRanks(int itemid) {
        rankIDs.clear();
        rankQuantity.clear();
        rankNames.clear();
        
        try {
            Connection con = DatabaseConnection.getConnection();
            PreparedStatement ps = con.prepareStatement("SELECT characterid, quantity FROM inventoryitems WHERE itemid = ? ORDER BY quantity desc LIMIT 10");
            ps.setInt(1, itemid);
            
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                rankIDs.add(rs.getInt("characterid"));
                rankQuantity.add(rs.getInt("quantity"));
                System.out.println("Adding: " + rs.getInt("characterid") + ", " + rs.getInt("quantity"));
            }
            
            rs.close();
            ps.close();
            
            String statement = "SELECT id, name FROM characters WHERE ";
            for (int x = 0; x < rankIDs.size(); x++) {
                statement += "id = " + rankIDs.get(x) + " OR ";
                System.out.println("String statement: " + statement);
            }
            statement = statement.substring(0, statement.length() - 4);
            System.out.println("Final string statement: " + statement);
            
            ps = con.prepareStatement(statement);
            rs = ps.executeQuery();
            while (rs.next()) {
                rankNames.add(rs.getInt("id"));
                rankNames.add(rs.getString("name"));
                System.out.println("Adding: " + rs.getInt("id") + ", " + rs.getString("name"));
            }
            
            System.out.println("List sizes: IDs " + rankIDs.size() + ", Quantities " + rankQuantity.size() + ", Names+IDs " + rankNames.size());
                        
            rs.close();
            ps.close();
                
        } catch (SQLException e) {
            System.out.println("Error creaing rankd stuffz" + e);
        }
    }
    
    public String showItemRanking() {
        String msg = "#eTranquilityStory Richest Player Rankings\r\n\r\n#n";
        int rank = 1; 
        
        for (int x = 0; x < rankIDs.size(); x++) {
            int id = Integer.parseInt("" + rankIDs.get(x));
            int quantity = Integer.parseInt("" + rankQuantity.get(x));
            int indexName = Integer.parseInt("" + rankNames.indexOf(id)) + 1;
            String name = "" + rankNames.get(indexName);
            msg += (rank + ") " + name + " (Amount: " + quantity + ")\r\n");
            rank++;
        }
            
        return msg;
    } 


---

(Remove the prints. Adding them just to check on what's happening in every step in case something's not working.)

NPC Script:

PHP Code:

---

var status = -1;

function action(mode, type, selection) {
    cm.setItemRanks(4000313); //took golden leaves for testing. 
    cm.sendOk(cm.showItemRanking());
    cm.dispose();
} 


---

It's somewhat similar to the reborn rankings, except for where to get the information from. What I basically did is getting the amount of a certain item from 'inventoryitems' and the characterids it belongs to. With the characterid got the names.

Only problem is, that it won't update the rankings everytime someone's amount increases, unless you save the amount everytime (which I don't think is recommended).
It updates individual amount as soon as it gets saved.

A better way of doing it might be by loading everyone's amount upon server start and edit the list.

EDIT: I didn't check for multiple slots of the same item, but just sum them up as xArmani said.

---

You're the best! Awesome, ah I see what you mean, so in what way would I sum them up? Because if it's looking for quantity of the item item, wouldn't get all the results in the table already?
Thanks.

You could always use a sql statement to do the summation..

PHP Code:

---

SELECT characterid, sum(quantity) FROM inventoryitems WHERE itemid = ? GROUP BY itemid ORDER BY sum(quantity) desc LIMIT 10 


---

Quote:

---

Originally Posted by NiteSec

You could always use a sql statement to do the summation..

PHP Code:

---

SELECT characterid, sum(quantity) FROM inventoryitems WHERE itemid = ? GROUP BY itemid ORDER BY sum(quantity) desc LIMIT 10 


---

---

EDIT: GROUP BY itemid would sum up any amount regadless of what ID it belong to.

PHP Code:

---

SELECT characterid, sum(quantity) FROM inventoryitems WHERE itemid = ? GROUP BY characterid ORDER BY sum(quantity) desc LIMIT 10 


---

Quote:

---

Originally Posted by LikeABaws

EDIT: GROUP BY itemid would sum up any amount regadless of what ID it belong to.

PHP Code:

---

SELECT characterid, sum(quantity) FROM inventoryitems WHERE itemid = ? GROUP BY characterid ORDER BY sum(quantity) desc LIMIT 10 


---

---

^ Yeah, this should be the right one.

Quote:

---

Originally Posted by NiteSec

^ Yeah, this should be the right one.

---

I think its trying to get the quantity column from the characters table? o.o

PHP Code:

---

Error creaing rankd stuffzjava.sql.SQLException: Column 'quantity' not found.
Apr 05, 2013 12:05:25 AM net.sf.odinms.scripting.npc.NPCScriptManager action
SEVERE: Error executing NPC script.
java.lang.reflect.UndeclaredThrowableException
        at com.sun.proxy.$Proxy3.action(Unknown Source)
        at net.sf.odinms.scripting.npc.NPCScriptManager.action(NPCScriptManager.
java:54)
        at net.sf.odinms.scripting.npc.NPCScriptManager.action(NPCScriptManager.
java:47)
        at net.sf.odinms.net.channel.handler.NPCMoreTalkHandler.handlePacket(NPC
MoreTalkHandler.java:31)
        at net.sf.odinms.net.MapleServerHandler.messageReceived(MapleServerHandl
er.java:135)
        at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messa
geReceived(AbstractIoFilterChain.java:570)
        at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageR
eceived(AbstractIoFilterChain.java:299)
        at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(Abst
ractIoFilterChain.java:53)
        at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.mess
ageReceived(AbstractIoFilterChain.java:648)
        at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flus
h(SimpleProtocolDecoderOutput.java:58)
        at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(Prot
ocolCodecFilter.java:180)
        at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageR
eceived(AbstractIoFilterChain.java:299)
        at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(Abst
ractIoFilterChain.java:53)
        at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.mess
ageReceived(AbstractIoFilterChain.java:648)
        at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorF
ilter.java:220)
        at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.
run(ExecutorFilter.java:264)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnabl
e.java:51)
        at java.lang.Thread.run(Unknown Source)
Caused by: java.security.PrivilegedActionException: javax.script.ScriptException
: sun.org.mozilla.javascript.internal.WrappedException: Wrapped java.lang.IndexO
utOfBoundsException: Index: 0, Size: 0 (<Unknown source>#12) in <Unknown source>
 at line number 12
        at java.security.AccessController.doPrivileged(Native Method)
        at com.sun.script.util.InterfaceImplementor$InterfaceImplementorInvocati
onHandler.invoke(Unknown Source)
        ... 20 more
Caused by: javax.script.ScriptException: sun.org.mozilla.javascript.internal.Wra
ppedException: Wrapped java.lang.IndexOutOfBoundsException: Index: 0, Size: 0 (<
Unknown source>#12) in <Unknown source> at line number 12
        at com.sun.script.javascript.RhinoScriptEngine.invoke(Unknown Source)
        at com.sun.script.javascript.RhinoScriptEngine.invokeFunction(Unknown So
urce)
        at com.sun.script.util.InterfaceImplementor$InterfaceImplementorInvocati
onHandler$1.run(Unknown Source)
        ... 22 more
Caused by: sun.org.mozilla.javascript.internal.WrappedException: Wrapped java.la
ng.IndexOutOfBoundsException: Index: 0, Size: 0 (<Unknown source>#12)
        at sun.org.mozilla.javascript.internal.Context.throwAsScriptRuntimeEx(Un
known Source)
        at sun.org.mozilla.javascript.internal.MemberBox.invoke(Unknown Source)
        at sun.org.mozilla.javascript.internal.NativeJavaMethod.call(Unknown Sou
rce)
        at sun.org.mozilla.javascript.internal.Interpreter.interpretLoop(Unknown
 Source)
        at sun.org.mozilla.javascript.internal.Interpreter.interpret(Unknown Sou
rce)
        at sun.org.mozilla.javascript.internal.InterpretedFunction.call(Unknown
Source)
        at sun.org.mozilla.javascript.internal.ContextFactory.doTopCall(Unknown
Source)
        at com.sun.script.javascript.RhinoScriptEngine$1.superDoTopCall(Unknown
Source)
        at com.sun.script.javascript.RhinoScriptEngine$1.doTopCall(Unknown Sourc
e)
        at sun.org.mozilla.javascript.internal.ScriptRuntime.doTopCall(Unknown S
ource)
        at sun.org.mozilla.javascript.internal.InterpretedFunction.call(Unknown
Source)
        ... 25 more
Caused by: java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
        at java.util.LinkedList.checkElementIndex(Unknown Source)
        at java.util.LinkedList.get(Unknown Source)
        at net.sf.odinms.scripting.npc.NPCConversationManager.showItemRanking(NP
CConversationManager.java:1384)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        ... 35 more 


---

Hmm, how would I implement the query into the script I posted above, if that would be easier?

Edit, whoops with the function need to change quantity to sum(quantity) derp.

Edited the thread ~