Anti-MQ2 Reward NPC

[poru]

Explanation
I wrote the following to curb the growing usage of warp and speed on Casual Dreams (http://casualdreams.org/).

We have a free Port NPC that sends players to cities and a few other noob zones.

Now, when players use warp/speed excessively, the Port NPC refuses to send them anywhere for 24 hours max, usually less.

It seems like the bulk of us wish MQ2 wasn't in use on our servers but few of us have the time to police it.

Warp/speed abuse has declined so dramatically (~80%) on the server that I wanted to share it ASAP.

Cheers,
poru

Custom Views .sql (add to you database)
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`127.0.0.1` VIEW `hackers_account` AS SELECT *, COUNT(*) as hack_count FROM hackers WHERE `date` >= now() - INTERVAL 1 DAY and hacked not like "/MQZone%" GROUP BY `account` ORDER BY hack_count DESC;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`127.0.0.1` VIEW `hackers_ip` AS SELECT account_ip.ip, account.name as `account`, hackers.name as `character`, hackers.hacked, hackers.zone, COUNT(*) as hack_count FROM hackers LEFT JOIN (account,account_ip) ON (hackers.account = account.name AND account.id = account_ip.accid) WHERE hackers.`date` >= now() - INTERVAL 1 DAY AND account_ip.lastused >= now() - INTERVAL 1 DAY AND hackers.hacked not like "/MQZone%" GROUP BY account_ip.ip ORDER BY hack_count DESC;


Rewards_Bot.pl (rename to Your_NPC.pl)
sub EVENT_SAY{
my $npcName = $npc->GetCleanName();
if ($text =~/Hail/i)
{
my $hackCheck = 1;
my $accountName = 0;
my $accountID = 0;
my $foundIP = 0;
my $hackCount = 0;
my $hackCountIP = 0;
my $hackCountAccount = 0;
my $hackMessage = "";

if ($hackCheck == 1) {
my $connect = plugin::LoadMysql();
$accountID = $client->AccountID();
$accountName = $client->AccountName();

my $queryIP = "SELECT ip FROM account_ip WHERE accid = $accountID ORDER BY lastused DESC LIMIT 1";
my $queryIP_handle = $connect->prepare($queryIP);
$queryIP_handle->execute();

while (@foundIProw = $queryIP_handle->fetchrow_array()){
$foundIP = $foundIProw[0];
}

my $queryHacks = "SELECT hack_count FROM hackers_ip WHERE ip = '$foundIP'";
my $queryHacks_handle = $connect->prepare($queryHacks);
$queryHacks_handle->execute();

while (@foundHacksRow = $queryHacks_handle->fetchrow_array()){
$hackCountIP = $foundHacksRow[0];
}

my $queryHacks = "SELECT hack_count FROM hackers_account WHERE account = '$accountName'";
my $queryHacks_handle = $connect->prepare($queryHacks);
$queryHacks_handle->execute();

while (@foundHacksRow = $queryHacks_handle->fetchrow_array()){
$hackCountAccount = $foundHacksRow[0];
}

if ($hackCountIP > $hackCountAccount) {
$hackCount = $hackCountIP;
}
else {
$hackCount = $hackCountAccount;
}

if ($hackCount >= 3) {
$hackMessage = "Sorry, $name, your Hack Count is $hackCount for the last 24 hours. I can't reward you until your Hack Count is less than 3. Please check back in an hour or two.";
}
elsif ($hackCount > 0) {
$hackMessage = " By the way, your Hack Count is $hackCount for the last 24 hours, $name. I will be temporarily unable to reward you if your Hack Count becomes greater than 2.";
}
else {
$hackMessage = "";
}
}

if ($hackCount <= 2) {
$client->Message(315, "$npcName tells you, 'I like your style, $name, have a carrot!$hackMessage'");
quest::summonitem(52381); # Reward player with "Carrot"
}
else {
$client->Message(315, "$npcName tells you, '$hackMessage'");
}
}
}

[The_Beast]

Nice. This would be great, but I am skeptical of the "hacker" table in the DB. I have never used MQ on my home server, but when testing, that hacker table
was showing MQWarp entries for my character, even sometimes when I used a cross-zone gnome porter (like in Iceclad).