A simple script for this:
SQL:
Code: Select all
ALTER TABLE `characters` ADD COLUMN `last_pvpkills` SMALLINT UNSIGNED DEFAULT NULL;
Java:
Code: Select all
package custom; import com.l2jserver.L2DatabaseFactory;import com.l2jserver.gameserver.ThreadPoolManager;import com.l2jserver.gameserver.model.quest.Quest;import java.sql.Connection;import java.sql.PreparedStatement;import java.util.Calendar;import java.util.Date;import java.util.GregorianCalendar; public class PVPKillsSave extends Quest { private Calendar _date = Calendar.getInstance(); private Calendar _nextSaveDate = new GregorianCalendar(); private static final long PERIOD = 86400000L; // checks update each 1 day private static final int SAVE_WEEKDAY = Calendar.MONDAY; // pvpkills are updated on every monday private static final int SAVE_HOUR = 0; // at midnight public PVPKillsSave(int questId, String name, String descr){ super(questId, name, descr); _nextSaveDate.setTime(new Date()); _nextSaveDate.set(Calendar.DAY_OF_WEEK, SAVE_WEEKDAY); _nextSaveDate.set(Calendar.HOUR_OF_DAY, SAVE_HOUR); _nextSaveDate.set(Calendar.MINUTE, 0); _nextSaveDate.set(Calendar.SECOND, 0); _nextSaveDate.set(Calendar.MILLISECOND, 0); _nextSaveDate.add(Calendar.DAY_OF_MONTH, 7); checkSave(); } private void checkSave(){ long nextCheckSave = _nextSaveDate.getTimeInMillis() - _date.getTimeInMillis(); if(nextCheckSave >= PERIOD){ nextCheckSave = PERIOD; } if(_date.getTimeInMillis() >= _nextSaveDate.getTimeInMillis()){ updatePVPKills(); } ThreadPoolManager.getInstance().scheduleGeneral(new Runnable(){ @Override public void run(){ checkSave(); } }, nextCheckSave); } private void updatePVPKills(){ Connection con = null; try{ con = L2DatabaseFactory.getInstance().getConnection(); PreparedStatement statement = con.prepareStatement("UPDATE `characters`" + " SET `last_pvpkills` = `pvpkills`"); statement.executeUpdate(); }catch(Exception e){ e.printStackTrace(); } } @SuppressWarnings("ResultOfObjectAllocationIgnored") public static void main(String[] args){ new PVPKillsSave(-1, "PVPKillsSave", "custom"); }}
PHP:
Code: Select all
<?php$res_list_lotw = mysql_query("SELECT *" ." FROM characters" ." WHERE last_pvpkills > '0' AND pvpkills > '0'"); $week_pvpkills = array();$i = 0; while($r_list_lotw = mysql_fetch_array($res_list_lotw)){ $week_pvpkills[0][$i] = $r_list_lotw['char_name']; $week_pvpkills[1][$i] = $r_list_lotw['pvpkills'] - $r_list_lotw['last_pvpkills']; $i++;} @array_multisort($week_pvpkills[1], SORT_DESC, $week_pvpkills[0]); echo "The winner is ",$week_pvpkills[0][0]," with ",$week_pvpkills[1][0]," kills.";
OR
Code: Select all
<?php$res_list_lotw = mysql_query("SELECT *," ." (pvpkills - last_pvpkills) AS week_pvpkills" ." FROM characters" ." WHERE last_pvpkills > '0' AND pvpkills > '0'" ." ORDER BY week_pvpkills DESC LIMIT 1"); $r_list_lotw = mysql_fetch_array($res_list_lotw); echo "The winner is ",$r_list_lotw['char_name']," with ",$r_list_lotw['week_pvpkills']," kills.";
I recommend the first php script, since that does not load MySQL that much with operations at each row.
Of course, it will dynamically query pvpkill counts in a week, but if you want to show the winner of last week, then you should directly save the subtracted value to last_pvpkills column under updatePVPkills, like:
UPDATE `characters` SET `last_pvpkills` = `pvpkills` - `last_pvpkills`
YET NOT TESTED!