Printable View
I'm working on a new pwAdmin feature to count the pvp kills and death using the world2.log file
would be nice if someone who has a decent amount of pvp kills on his server can share this file (mine has only 1 pvp kill xD)
if you don't want to share this file (for security resons) it would be nice if you can post the lines containing the following pattern:
also the category number seems interesting, i've got 258 for a kill in the main world...Code:... 用户48被33杀死，类别(258) ...
Quote:
Originally Posted by ronny1982
So Im assuming that is userid, number of deaths, odd category id?Code:48 killed by 33 users, category (258) ...
victim-id, killer-id, kind-of-kill
the jsp with kill/dead count and mysql import is done, but i want to do some verifiction of my parser on a larger log file, which contains more pvp kills than mine...
'formatlog' file for more detail on PK/kill and so on... rbb138 already made a script for that on the forum. :mellow:
I do this:
Shell file to get the Logs.
Php Script will Update a different table named pkrankCode:cd /server/logs/
grep -e ':type=2:' world2.formatlog >> kills.formatlog
chmod 777 kills.formatlog
sed -i 's/ /,/g' kills.formatlog
sed -i 's/=/,/g' kills.formatlog
sed -i 's/:/,/g' kills.formatlog
sed -i 's/,,,/,/g' kills.formatlog
sed -i 's/,,/,/g' kills.formatlog
mysqlimport -u root --local --force --columns=date,hour,minute,@x,@x,@x,@x,@x,@x,@x,corpseid,@x,@x,@x,killerid --fields-terminated-by=, dbo kills.formatlog
rm kills.formatlog
Next script will call java servletPHP Code:<body bgcolor="#161616">
<font color=white>
<?
$username="root";
$password="";
$database="dbo";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT killerid, corpseid, hour, date, COUNT(killerid) AS ckills FROM kills GROUP BY killerid, corpseid, hour, date HAVING ( COUNT(killerid) > 0 )";
$result=mysql_query($query);
$num=mysql_num_rows($result);
$i=0;
while ($i < $num) {
$PlayerName=mysql_result($result,$i,"killerid");
$One = 1;
$SearchifexistQuery = Mysql_Query("SELECT * FROM pkrank WHERE userid = '$PlayerName'");
$SearchifexistNum = Mysql_Num_Rows($SearchifexistQuery);
IF ($SearchifexistNum == 1 OR $SearchifexistNum > 1) {
$SearchifexistFetch = Mysql_Fetch_Array($SearchifexistQuery);
$CharacterKills = $SearchifexistFetch['kills'];
$NewCharacterKills = $CharacterKills + $One;
Mysql_Query("UPDATE pkrank SET kills = '$NewCharacterKills' WHERE userid = '$PlayerName'");
}
ELSE {
Mysql_Query("INSERT INTO pkrank (userid, name, class, level, kills) VALUES ('$PlayerName', 'Needs Updating', 'Needs Updating', '1', '$One')");
}
$i++;
}
echo "Successfully Updated Tables!<br>";
Mysql_Query("DELETE FROM kills");
Mysql_Query("UPDATE administration SET status = NOW() WHERE type = 'PKUPDATE'");
?>
This is the java servlet, this servlet updates the userids, level and class of the top 50 characters.PHP Code:<?
$DBHost = "127.0.0.1"; // localhost or your IP
$DBUser = "root"; // Database user
$DBPassword = ""; // Database password
$DBName = "dbo"; // Database name
// Connect to server and select databse.
$Link = MySQL_Connect($DBHost, $DBUser, $DBPassword) or die ("Can't connect to MySQL");
MySQL_Select_Db($DBName, $Link) or die ("Database ".$DBName." do not exists.");
$handle = fopen("http://localhost:8180/iweb/pkrank.jsp", "r");
while($line = fgets($handle))
{
echo($line);
}
fclose($handle);
?>
Here are the create SQL statements for the 2 Tables I useCode:<%@page contentType="text/html; charset=GBK"%>
<%@page import="java.lang.*"%>
<%@page import="java.util.*"%>
<%@page import="java.text.*"%>
<%@page import="org.apache.commons.lang.StringEscapeUtils"%>
<%@page import="protocol.*"%>
<%@page import="com.goldhuman.auth.*"%>
<%@page import="com.goldhuman.util.*"%>
<%@page import="org.apache.commons.logging.Log"%>
<%@page import="org.apache.commons.logging.LogFactory"%>
<%@page import="java.sql.*"%>
<%
String db_host = "localhost";
String db_port = "3306";
String db_user = "root";
String db_password = "";
String db_database = "dbo";
%>
<%
//
// Coded by [B]aSH from Vendetta Gaming Network.
//
int count = 0;
String cls = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection connection = DriverManager.getConnection("jdbc:mysql://" + db_host + ":" + db_port + "/" + db_database, db_user, db_password);
Statement statement = connection.createStatement();
ResultSet rst = statement.executeQuery("SELECT * FROM pkrank ORDER BY kills DESC LIMIT 50");
RoleBean role = null;
String tempplayername = null;
int index = 0;
while (rst.next())
{
//Prepare Statement
PreparedStatement UpdateInfo = connection.prepareStatement("UPDATE pkrank SET name=?, class=?, level=? WHERE userid=?");
int roleid = rst.getInt("userid");
role = GameDB.get( roleid );
session.setAttribute( "gamedb_rolebean", role );
if (null == role){
}
else {
tempplayername = null;
tempplayername = StringEscapeUtils.escapeHtml(role.base.name.getString());
index = 0;
index = tempplayername.indexOf("'");
StringBuffer playername = new StringBuffer(tempplayername);
if(index > 0){
playername.replace(index, index + 1, "?");
}
}
switch(role.base.cls)
{
case 0: cls = "Warrior"; break;
case 1: cls = "Mage"; break;
case 2: cls = "Monk"; break;
case 3: cls = "WereFox"; break;
case 4: cls = "WereBeast"; break;
case 5: cls = "Genie"; break;
case 6: cls = "Archer"; break;
case 7: cls = "Priest"; break;
default: cls = "Unknown";
}
UpdateInfo.setString(1, tempplayername);
UpdateInfo.setString(2, cls);
UpdateInfo.setInt(3, role.status.level);
UpdateInfo.setInt(4, roleid);
UpdateInfo.executeUpdate();
count++;
}
}
catch (Exception e)
{
out.println("<font color=red>Error Occured But System Added <b>" + count + "</b> Characters.");
}
%>
<%
if (count > 0) {
out.println("<font color=green><br>Complete Updated: <b>" + count + "</b> Characters.<br><br>");
}
else
{
out.println("<br><font color=red>No Characters available to add!");
}
%>
Hope this helps anyone and Ronny of course.Code:CREATE TABLE `dbo`.`kills` (
`date` varchar(255) NOT NULL,
`hour` varchar(255) NOT NULL,
`minute` varchar(255) NOT NULL,
`corpseid` varchar(255) NOT NULL,
`killerid` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `dbo`.`pkrank` (
`userid` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`class` varchar(45) NOT NULL,
`level` varchar(45) NOT NULL,
`kills` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
aw nice, this file is smaller than world2.log and utf8 that will increase the parsing speed...Quote:
Originally Posted by Romulan
this is the part which i want to verify, can it be confirmed that only type=2** are player kills?Quote:
Originally Posted by nofxpunkerbrian
Yeah that was this post :thumbup:Quote:
Originally Posted by vixio_dv
here is an update for pwAdmin v1.7 to v1.7a
Features:
- added: pvp kill/dead count to mysql roles table (from file world2.formatlog)
- added: index.php to jetty's welcome files
Yep it does verify mate.Quote:
Originally Posted by ronny1982
type = 256 means the person got red nameQuote:
Originally Posted by ronny1982
and type = 2432894639284 (or similar) means death by guard (or possibly monster).
ensuring type = 2 means peple cant farm newbs, as i explained in my thread on ranking scripts ages ago.
also bash, credit where due pls...
"this is the script i use" = "this is the shell script robb gave me"
As a pvp server these ranks are very important for community (which is declining) so im not estatic to give them out but i always give help to those willing to work for it.
You can either use [b]ash's jsp method to directy import lines into mysql or run a wget on the webpage and import that into database in 1 go via scheduled shell script.
it's already done, i'm using an own file parser made in jsp to count kills/deaths for each role and store them to mysql...
Quote:
Originally Posted by ronny1982
It seems Chaos13 (or another) said: data "pvp kills" are recorded in the xml character
The server database itself is unaware of that XML, that is purely created from all the game database values via jsp in pwAdmin and or iweb if you are still using that ancient thing...Quote:
Originally Posted by gibat92
when I of this command
mysqlimport-u root - local - force -
columns = date, hour, minute, @ x, @ x, @ x, @ x, @ x, @ x, @ x, corpseid, @ x, @ x, @ x, killerid - fields-terminated-by =,
dbo kills.formatlog
error of more or less well
root @ localhost password: No
What can it be?
Add after "-u root" put -p and then imput ur pass...
Posted via Mobile Device
This cool!
grep-e ': type = 2:' world2.formatlog
Finds no result what to do?
The PHP script bash did is wrong no offense bro.
i took the liberty to edit it and make it better and more accurate here is the result:
.php
.jspPHP Code:<body bgcolor="#161616">
<font color=white>
<?
$DBHost = "local-host"; // ip
$DBUser = "my-username"; //user
$DBPassword = "my-pass"; //pass
$DBName = "dbo"; //database
$Link = MySQL_Connect($DBHost, $DBUser, $DBPassword) or die ("Can't connect to MySQL");
MySQL_Select_Db($DBName, $Link) or die ("Database ".$DBName." do not exists.");
$query="SELECT killerid, COUNT(killerid) AS ckills FROM kills GROUP BY killerid HAVING ( COUNT(killerid) > 0 )";
$result=mysql_query($query);
$querydeaths="SELECT corpseid, COUNT(corpseid) AS cdeaths FROM kills GROUP BY corpseid HAVING ( COUNT(corpseid) > 0 )";
$resultdeaths=mysql_query($querydeaths);
$num=mysql_num_rows($result);
$i=0;
while ($i < $num) {
// get killer-id
$PlayerName=mysql_result($result,$i,"killerid");
//get kills result
$ckills=mysql_result($result,$i,"ckills");
//get deaths result
$cdeaths=mysql_result($resultdeaths,$i,"cdeaths");
$SearchifexistQuery = Mysql_Query("SELECT kills,deaths FROM pkrank WHERE userid = '$PlayerName'");
$SearchifexistNum = Mysql_Num_Rows($SearchifexistQuery);
IF ($SearchifexistNum == 1 OR $SearchifexistNum > 1) {
$SearchifexistFetch = Mysql_Fetch_Array($SearchifexistQuery);
$CharacterKills = $SearchifexistFetch['kills'];
$Characterdeaths = $SearchifexistFetch['deaths'];
$NewCharacterKills = $CharacterKills + $ckills;
$NewCharacterdeaths = $Characterdeaths + $cdeaths;
Mysql_Query("UPDATE pkrank SET kills = '$NewCharacterKills' WHERE userid = '$PlayerName'");
Mysql_Query("UPDATE pkrank SET deaths = '$NewCharacterdeaths' WHERE userid = '$PlayerName'");
}
ELSE {
Mysql_Query("INSERT INTO pkrank (userid, name, clasa, level, kills, reputation, deaths, gender) VALUES ('$PlayerName', 'Upcoming...', 'Upcoming...', '1', '1' , '1', '1', 'Upcoming...')");
}
$i++;
}
echo "Successfully Updated Tables!<br>";
//delete table
Mysql_Query("DELETE FROM kills");
//close connection
mysql_close($Link);
?>
tables;PHP Code:<%@page import="java.sql.*"%>
<%@page import="protocol.*"%>
<%@page import="java.io.*"%>
<%@page import="java.text.*"%>
<%@page import="org.apache.commons.logging.LogFactory"%>
<%@page import="java.util.Iterator"%>
<%@page import="com.goldhuman.Common.Octets"%>
<%@page import="com.goldhuman.IO.Protocol.Rpc.Data.DataVector"%>
<%@page import="org.apache.commons.lang.StringEscapeUtils"%>
<%@ page language="java" import="java.sql.*" errorPage="" %>
<%
Connection connection = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection("jdbc:mysql://localhost/dbo","root", "password");
ResultSet rst = null;
RoleBean role = null;
String tempplayername = null;
int count = 0;
int level = 0;
int rep = 0;
String cls = null;
String gender = null;
PreparedStatement UpdateInfo = null;
try {
Statement statement = connection.createStatement();
rst = statement.executeQuery("SELECT userid FROM pkrank ORDER BY kills DESC");
int index = 0;
while (rst.next())
{
//Prepare Statement
UpdateInfo = connection.prepareStatement("UPDATE pkrank SET name=?, clasa=?, level=?, reputation=?, gender=? WHERE userid=?");
int roleid = rst.getInt("userid");
role = GameDB.get( roleid );
if (null == role)
{
}
else
{
try{
tempplayername = null;
tempplayername = StringEscapeUtils.escapeHtml(role.base.name.getString());
index = 0;
index = tempplayername.indexOf("'");
StringBuffer playername = new StringBuffer(tempplayername);
if(index > 0)
{
playername.replace(index, index + 1, "?");
}
switch(role.base.cls)
{
case 0: cls = "Warrior"; break;
case 1: cls = "Mage"; break;
case 2: cls = "Monk"; break;
case 3: cls = "WereFox"; break;
case 4: cls = "WereBeast"; break;
case 5: cls = "Genie"; break;
case 6: cls = "Archer"; break;
case 7: cls = "Priest"; break;
default: cls = "Unknown";
}
level = role.status.level;
rep = role.status.reputation;
gender = (role.base.gender == 0) ? "Male" : "Female";
}
catch (Exception e)
{
continue;
}
}
UpdateInfo.setString(1, tempplayername);
UpdateInfo.setString(2, cls);
UpdateInfo.setInt(3, level);
UpdateInfo.setInt(4, rep);
UpdateInfo.setString(5, gender);
UpdateInfo.setInt(6, roleid);
UpdateInfo.executeUpdate();
count++;
}
}
catch (Exception e)
{
out.println("<font color=red>Error Occured But System Added <b>" + count + "</b> Characters.<br>");
out.println(e);
}
if (count > 0) {
out.println("<font color=green><br>Complete Updated: <b>" + count + "</b> Characters.<br><br>");
}
else
{
out.println("<br><font color=red>No Characters available to add!");
}
try{
if(connection!=null){
connection.close();
}
if(rst!=null){
rst.close();
}
if(UpdateInfo!=null){
UpdateInfo.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}
%>
CREATE TABLE IF NOT EXISTS `kills` (
`date` varchar(255) NOT NULL,
`hour` varchar(255) NOT NULL,
`minute` varchar(255) NOT NULL,
`corpseid` int(22) NOT NULL,
`killerid` int(22) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `pkrank` (
`userid` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`clasa` varchar(45) NOT NULL,
`level` int(11) NOT NULL,
`kills` int(22) unsigned NOT NULL,
`reputation` bigint(11) NOT NULL,
`deaths` int(10) unsigned NOT NULL,
`gender` varchar(255) NOT NULL,
UNIQUE KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
enjoy:ott1:
Funny, people that DDOS my server are now using my work on theirs.
The php scripts might be changed but the basic principle and idea is still the same idea i had one year ago lol.
If you really go into it, you can create things like this:
http://img411.imageshack.us/img411/9...trolpanelc.png
i believed is bash script and i did give the credits is not a Release is just a script modified that's all :thumbdown:Quote:
Originally Posted by rbb138
Of course you gave credit. Then you turn around and brag to your players that you stole some things from other servers. You DDOS them and you take their stuff. To be perfectly blunt you could give all the credit in the world but people still aren't going to like you. Among the professionals you are a joke and an insult. You're a thief, you're a criminal, you're a liar. Do us all a favor and contribute some of YOUR OWN WORK for once. Or is that too much for you?
Theres nothing in that script thats not on the forum already.
Hintity hinity:
Try release stuff thats isn't already around.
Even just an idea is a "release", or thats what i tell myself when i give up my ideas on here...
Here's some ideas:
- Autopath
- New instances
- ORIGINAL items
Feel free to use your imagination.