EVE Development Network • View topic - MOD - Top Value Kills w/ lost isk (updated 7/22)

doctorstupid · **Joined:** Wed Feb 18, 2009 06:51 **Posts:** 29

First, see here: http://spacejerk.net

In short, when a kill is viewed in kill_detail, I've added a block of code that writes the kill ID, date, and total loss values to a new database table. The net effect here is every time a kill is posted, it's added to the database, and every time it's viewed later its value is updated to keep up with current market prices. I then added a new box to the main page that queries results less than a specified number of days old, and pulls out the top few (configurable), and displays the ship image, value lost, pilot name, etc.

First you need to create a new DB table using PHPmyAdmin, named iskloss with three fields named LossID, LossDate, and TotalLoss. The types in the same order should be varchar(30), varchar(30), bigint(30), default setting should work for the options, and create primary index on LossID.

kill_detail.php (in mods/fitting/ if applicable) find this line:
$html = $smarty->fetch('../mods/fitting/kill_detail.tpl');
and directly above it add:

Code:

//BEGIN TOP VALUE KILLS
$LossID = $kill->getID();
$LossDate = $kill->getTimeStamp();
$TotalLoss = ($TotalValue + $ship->getPrice());
   $qry = new DBQuery;
   $qry->execute("select * from kb3_invtypes where typeID = $ship->externalid_");
      while($row = $qry->getRow())
      {
      $LostGroup = $row['marketGroupID'];
      }
      //Excludes structures, comment out if statement and the loop { }thingies to include them
if ($LostGroup < 478 || $LostGroup > 490)
{
//Change corp/victim ID to exclude friendlies, $kill->getVictimAllianceID() for alliance
if ($kill->getVictimCorpID() != 1)
{
   $qry = new DBQuery;
   $qry->execute("insert into iskloss values('$LossID','$LossDate','$TotalLoss') on duplicate key update TotalLoss=$TotalLoss");
}
}
//END TOP VALUE KILLS

FOR A VERTICAL DISPLAY BOX, UNDER TOP KILLER/SCORER BOXES
Go to home.php (check your mod folders to find the right one), find $page->generate(); at the end before the closing ?> and add this ABOVE it:

Code:

// TOP VALUE KILLS PROTOTYPE, POORLY WRITTEN BY DOCTORSTUPID2, OPTIMIZED BY CATARI

// Box name
$topbox = new box("Top Value Kills");

// Number of top value kills to display
$topValueCount = 5;

$qry = new DBQuery();
// Change interval value for history length, e.g. 7 DAY for weekly, 30 DAY for monthly, etc
$qry->execute("SELECT LossDate, TotalLoss, LossID FROM iskloss WHERE LossDate>=NOW()-INTERVAL 30 DAY ORDER BY TotalLoss DESC LIMIT $topValueCount");

for($boxgen = 1;$boxgen <= $topValueCount; $boxgen++){
   $topValue = $qry->getRow();
   $query = new DBQuery();
   $query->execute("SELECT k.kll_victim_id, k.kll_ship_id, p.plt_name, s.shp_name, i.typeID
         FROM kb3_kills k
         JOIN kb3_pilots p ON p.plt_id = k.kll_victim_id
         JOIN kb3_ships s ON s.shp_id = k.kll_ship_id
         JOIN kb3_invtypes i ON i.typeName = s.shp_name
         WHERE kll_id=".$topValue['LossID']);
   $row = $query->getRow();
   $topbox->addOption('caption','<div align="center"><p><font size="2">' . $row['plt_name'] . '</font><br />' . $row['shp_name'] . '<br /><a href="?a=kill_detail&kll_id=' . $topValue['LossID'] . '"><img src="img/ships/64_64/' . $row['typeID'] . '.png" border=0></a><br />' . number_format($topValue['TotalLoss'], 2, '.', ',') . ' ISK</p></div>');

   if ($boxgen != $topValueCount)
   {
      // Seperator displayed between kills
      $topbox->addOption('caption','<div align="center"><p><img src="img/kills.gif"></p></div>');
   }
}
// Days to display at bottom of box, comment out to hide
$topbox->addOption('caption','<div align="center">(Past 30 Days)</div>');
$page->addContext($topbox->generate());

// END TOP VALUE KILLS

FOR A HORIZONTAL BOX BELOW THE KILL SUMMARY TABLE
Go to home.php (check your mod folders to find the right one), find // bad hax0ring, we really need mod callback stuff and add this ABOVE it:

Code:

//TOP VALUE MOD, CREDIT TO GREZ AND ANDYM
$topValueCount = 5;
$qry = new DBQuery();
$qry->execute("SELECT LossDate, TotalLoss, LossID FROM iskloss WHERE LossDate>=NOW()-INTERVAL 30 DAY ORDER BY TotalLoss DESC LIMIT 5");
$html .='<div class="kb-date-header">Top Value Kills, 30 Days</div><BR>';
$html .='<table width="100%" class="kb-table"><tr>';
for($boxgen = 1;$boxgen <= $topValueCount; $boxgen++){
   $topValue = $qry->getRow();
      $query = new DBQuery();
$query->execute("SELECT k.kll_victim_id, k.kll_ship_id, p.plt_name, s.shp_name, i.typeID
         FROM kb3_kills k
         JOIN kb3_pilots p ON p.plt_id = k.kll_victim_id
         JOIN kb3_ships s ON s.shp_id = k.kll_ship_id
         JOIN kb3_invtypes i ON i.typeName = s.shp_name
         WHERE kll_id=".$topValue['LossID']);
      $row = $query->getRow();
         $html .='<td align="center" class="kb-table-row-odd"><table width="100%"><tr><td align="center" width="20%"><b><font size="1">'.$row['plt_name'].'</font></b></td></tr><tr><td align="center"><a href="?a=kill_detail&kll_id=' . $topValue['LossID'] . '"><img src="img/ships/64_64/' . $row['typeID'] . '.png" border=0></a></td></tr><tr><td align="center"><b><font size="1">'.$row['shp_name'].'</font></b></td></tr><tr><td align="center"><b><font size="1">'.number_format($topValue['TotalLoss'], 2,'.', ',').' ISK</font></b></td></tr></table></td>';
   }
$html .='</tr></table>';
//END TOP VALUE MOD

All of the configuration is done in home.php, on the lines below.

change the number to how many kills you want displayed:
$topValueCount = 5;
Change history length by modifying the INTERVAL value:
$qry->execute("SELECT * FROM iskloss WHERE LossDate>=NOW()-INTERVAL 30 DAY ORDER BY TotalLoss DESC LIMIT $boxgen");
Also change this to reflect the history:
$topbox->addOption("caption","<P><CENTER>(Past 30 Days)</CENTER></P>");

And you're done, it will give an SQL error at the top of the home page until there are enough rows in the table to populate the list. From here just skim over your killboard, viewing any kills that you know to be high value (CS, BS, capital, etc), and the table will begin to populate itself. You only need to go as far back as you have it configured for, i.e. you don't need to view kills from 2008 to add them to the table, they (probably) won't be used.

Please feel free to make changes to the code here, I'm very new to PHP so please pick it apart and post suggestions/corrections/improvements. I could also use some pointers on formatting the box on the home page, I more or less just duplicated the menu/nav box

mastergamer · **Posted:** Tue Jun 09, 2009 12:07

Why did you mix $qry->execute() and mysql_query()?

You should always use $qry->execute(). Instead if this code:

Code:

$result = mysql_query("SELECT * FROM iskloss WHERE LossDate>=NOW()-INTERVAL 30 DAY ORDER BY TotalLoss DESC LIMIT 1");
   while($row = mysql_fetch_array($result))
     {
     //do stuff with the data
     }

you should do this:

Code:

$qry->execute("SELECT * FROM iskloss WHERE LossDate>=NOW()-INTERVAL 30 DAY ORDER BY TotalLoss DESC LIMIT 1");
   while($row = $qry->getRow())
     {
     //do stuff with the data
     }

You just have to make sure that you use the data from a particular result set before you execute another query, since the new result set will overwrite the old.

pdidoc · **Posted:** Tue Jun 09, 2009 13:53

Well I for one will be keeping an eye on this as this would be perfect for pretty much every EVE DEV KB out there GG mate on your work so far :-)

doctorstupid · **Joined:** Wed Feb 18, 2009 06:51 **Posts:** 29

mastergamer wrote:

Why did you mix $qry->execute() and mysql_query()?

You should always use $qry->execute(). Instead if this code:

Code:

$result = mysql_query("SELECT * FROM iskloss WHERE LossDate>=NOW()-INTERVAL 30 DAY ORDER BY TotalLoss DESC LIMIT 1");
   while($row = mysql_fetch_array($result))
     {
     //do stuff with the data
     }

you should do this:

Code:

$qry->execute("SELECT * FROM iskloss WHERE LossDate>=NOW()-INTERVAL 30 DAY ORDER BY TotalLoss DESC LIMIT 1");
   while($row = $qry->getRow())
     {
     //do stuff with the data
     }

You just have to make sure that you use the data from a particular result set before you execute another query, since the new result set will overwrite the old.

Duly noted, as I said I don't know PHP, my ability pretty much starts and stops at fucking with existing code and sometimes getting a working result :lol:

I'll play with it some more this evening and see if that cleans things up.

pdidoc · **Posted:** Wed Jun 10, 2009 14:34

doctorstupid wrote:

Duly noted, as I said I don't know PHP, my ability pretty much starts and stops at fucking with existing code and sometimes getting a working result :lol:

LOL i'm exactly the same... it's how i got the All time top killer and scorer onto the home of my killboard: http://www.killboard.0eathsquad.com

^^

doctorstupid · **Joined:** Wed Feb 18, 2009 06:51 **Posts:** 29

pdidoc wrote:

doctorstupid wrote:

Duly noted, as I said I don't know PHP, my ability pretty much starts and stops at fucking with existing code and sometimes getting a working result :lol:

LOL i'm exactly the same... it's how i got the All time top killer and scorer onto the home of my killboard: http://www.killboard.0eathsquad.com

^^

That's nice actually, one of the few things I always like in griefwatch over EDK. Another one being sorting a pilot's kills by weapon/ship, but that's another story. You should post that, I'd use it (though the right side of my KB is getting pretty full

)

pdidoc · **Posted:** Thu Jun 11, 2009 11:10

As requested bro:

viewtopic.php?f=505&t=14895

doctorstupid · **Joined:** Wed Feb 18, 2009 06:51 **Posts:** 29

Very cool.

So can anyone help with the kill_detail.php db query that's currently FUBAR'd? That's the only thing I can't find a solution to, other than some minor formatting in the award box, but I may try moving it around under the kill table to save some space. But really it's the INSERT query that's stopping progress.

doctorstupid · **Joined:** Wed Feb 18, 2009 06:51 **Posts:** 29

Can anyone please explain why adding the code below to the end of kill_detail.php (before closing ?>)doesn't do anything? Where do I need to place this to have it actually function?

Code:

$LossID = $kill->getID();
$LossDate = $kill->getTimeStamp();
$TotalLoss = ($TotalValue + $ship->getPrice());

if ($kill->getVictimCorpID() != 1) {

   $qry = "insert into iskloss values('$LossID','$LossDate','$TotalLoss') on duplicate key update TotalLoss=$TotalLoss";
   $qry->execute();
   
}

It appears that adding even a simple echo "test"; anywhere in the page doesn't actually do anything.

edit:
I'm playing around with it some more, and it seems that as long as the modified file is NOT named kill_detail.php (e.g. kill_detail2.php) it works as it should, but any file NOT named exactly that loses the fitting panel.

Help please?

doctorstupid · **Joined:** Wed Feb 18, 2009 06:51 **Posts:** 29

OK, I'm just a moron.

I forgot that the fitting panel was a mod, and as such, the file I should have been editing was located in the mods folder.

I'll clean up my code and put comments around it, and post it up in a few.

EDIT: First post updated

MOD - Top Value Kills w/ lost isk (updated 7/22)

Who is online