scripts/parsedumps.pl

   1 #!/usr/bin/perl
   2 q{
   3 /***************************************************************************
   4  *   Copyright (C) 2006 by Michael Andreen <harvATruinDOTnu>               *
   5  *                                                                         *
   6  *   This program is free software; you can redistribute it and/or modify  *
   7  *   it under the terms of the GNU General Public License as published by  *
   8  *   the Free Software Foundation; either version 2 of the License, or     *
   9  *   (at your option) any later version.                                   *
  10  *                                                                         *
  11  *   This program is distributed in the hope that it will be useful,       *
  12  *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
  13  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
  14  *   GNU General Public License for more details.                          *
  15  *                                                                         *
  16  *   You should have received a copy of the GNU General Public License     *
  17  *   along with this program; if not, write to the                         *
  18  *   Free Software Foundation, Inc.,                                       *
  19  *   51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.         *
  20  ***************************************************************************/
  21 };
  22
  23 use strict;
  24 use warnings;
  25 use DBI;
  26 use DBD::Pg qw(:pg_types);
  27
  28 use LWP::Simple;
  29
  30 use lib qw{/var/www/ndawn/};
  31
  32 use ND::Include;
  33 use ND::DB;
  34
  35 our $dbh = ND::DB::DB();
  36
  37 $ND::DBH = $dbh;
  38
  39 my $tick = $ARGV[0];
  40 my $hour;
  41 $dbh->begin_work;
  42 my $dumps = $dbh->prepare("SELECT dump,modified FROM dumps WHERE tick = ? and type = ? ORDER BY modified LIMIT 1");
  43 $dumps->execute($tick,"alliance");
  44 my @alliances;
  45 if (@_ = $dumps->fetchrow){
  46         $_ = $_[0];
  47         $hour = (gmtime($_[1]))[2];
  48         my $allianceid = $dbh->prepare(qq{SELECT find_alliance_id(?)});
  49         while (m/\d+\t\"(.+)\"\t(\d+)\t(\d+)\t(\d+)/g){
  50                 $allianceid->execute($1);
  51                 my ($id) = $allianceid->fetchrow;
  52                 push @alliances,[$tick,$id,$3,$2,$4,0,0,0,0,0,0,0,0,0,0,0,0];
  53         }
  54 }
  55
  56
  57 for my $i (3,4){
  58         @alliances = sort {$b->[$i] <=> $a->[$i]} @alliances;
  59         my $rank = 0;
  60         for my $alliance (@alliances) {
  61                 $rank++;
  62                 $alliance->[$i+2] = $rank;
  63     }
  64 }
  65 my $insert = $dbh->prepare(q{INSERT INTO alliance_stats (tick,id,members,
  66         size,score,
  67         sizerank,scorerank,
  68         size_gain,score_gain,
  69         sizerank_gain,scorerank_gain,
  70         size_gain_day,score_gain_day,
  71         sizerank_gain_day,scorerank_gain_day,
  72         members_gain,members_gain_day
  73         ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}) or die $dbh->errstr;
  74
  75 my $findalliance = $dbh->prepare(q{SELECT tick,id,members,
  76         size, score,
  77         sizerank, scorerank,
  78         size_gain, score_gain,
  79         sizerank_gain, scorerank_gain,
  80         size_gain_day, score_gain_day,
  81         sizerank_gain_day, scorerank_gain_day,
  82         members_gain,members_gain_day
  83 FROM alliance_stats WHERE id = $1 AND tick < $2 ORDER BY tick DESC LIMIT 1}) or die $dbh->errstr;
  84
  85 for my $alliance (@alliances) {
  86
  87         $findalliance->execute($alliance->[1],$tick) or die $dbh->errstr;
  88         if (my @oldAlliance = $findalliance->fetchrow_array){
  89                 for my $i (1,2){
  90                         $alliance->[$i+6] = $alliance->[$i+2] - $oldAlliance[$i+2];
  91                         $alliance->[$i+8] = $alliance->[$i+4] - $oldAlliance[$i+4];
  92                         $alliance->[$i+10] = $alliance->[$i+6] + $oldAlliance[$i+10] if $hour;
  93                         $alliance->[$i+12] = $alliance->[$i+8] + $oldAlliance[$i+12] if $hour;
  94                 }
  95                 $alliance->[15] = $alliance->[2] - $oldAlliance[+2];
  96                 $alliance->[16] = $alliance->[15] + $oldAlliance[16] if $hour;
  97
  98         }
  99         $insert->execute(@{$alliance}) or die $dbh->errstr;
 100 }
 101
 102 my @planets = ();
 103 $dumps->execute($tick,"planet");
 104 if (@_ = $dumps->fetchrow){
 105         $_ = $_[0];
 106         $hour = (gmtime($_[1]))[2];
 107         my $planetid = $dbh->prepare(qq{SELECT findplanetid(?,?,?)});
 108         while (m/(\d+)\t(\d+)\t(\d+)\t\"(.*)\"\t\"(.*)\"\t(Ter|Cat|Zik|Xan|Etd)\t(\d+)\t(\d+)\t(\d+)\t(\d+)/g){
 109                 $planetid->execute($5,$4,$6);
 110                 my @id = $planetid->fetchrow;
 111                 push @planets,[$tick,$id[0],$1,$2,$3,$7,$8,$9,$10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
 112         }
 113 }
 114
 115 for my $i (8,7,5,6){
 116         @planets = sort {$b->[$i] <=> $a->[$i]} @planets;
 117         my $rank = 0;
 118         for my $planet (@planets) {
 119                 $rank++;
 120                 $planet->[$i+4] = $rank;
 121     }
 122 }
 123
 124 my $findplanets = $dbh->prepare(q{SELECT tick,id, x, y, z,
 125         size, score, value, xp,
 126         sizerank, scorerank, valuerank, xprank,
 127         size_gain, score_gain, value_gain, xp_gain,
 128         sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain,
 129         size_gain_day, score_gain_day, value_gain_day, xp_gain_day,
 130         sizerank_gain_day, scorerank_gain_day, valuerank_gain_day, xprank_gain_day
 131 FROM planet_stats WHERE tick = (SELECT MAX(tick) FROM planet_stats WHERE tick < $1)});
 132 $insert = $dbh->prepare(q{INSERT INTO planet_stats(tick,id, x, y, z,
 133         size, score, value,xp,
 134         sizerank,scorerank,valuerank,xprank,
 135         size_gain, score_gain, value_gain, xp_gain,
 136         sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain,
 137         size_gain_day, score_gain_day, value_gain_day, xp_gain_day,
 138         sizerank_gain_day, scorerank_gain_day, valuerank_gain_day, xprank_gain_day)
 139         VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)});
 140 $findplanets->execute($tick) or die $dbh->errstr;
 141 my %oldStats;
 142 while (my @planet = $findplanets->fetchrow){
 143         $oldStats{$planet[1]} = \@planet;
 144 }
 145 for my $planet (@planets) {
 146         #print "$planet->[1]\n";
 147         my $oldPlanet = $oldStats{$planet->[1]};
 148
 149         if ($oldPlanet){
 150                 for my $i (1,2,3,4){
 151                         $planet->[$i+12] = $planet->[$i+4] - $oldPlanet->[$i+4];
 152                         $planet->[$i+16] = $planet->[$i+8] - $oldPlanet->[$i+8];
 153                         $planet->[$i+20] = $planet->[$i+12] + $oldPlanet->[$i+20] if $hour;
 154                         $planet->[$i+24] = $planet->[$i+16] + $oldPlanet->[$i+24] if $hour;
 155                 }
 156                 if (($planet->[2] != $oldPlanet->[2]) or
 157                         ($planet->[3] != $oldPlanet->[3]) or
 158                         ($planet->[4] != $oldPlanet->[4])){
 159                         #print "Planet has moved from $oldPlanet[2]:$oldPlanet[3]:$oldPlanet[4] to $planet->[2]:$planet->[3]:$planet->[4]\n";
 160                         intel_log -3, $planet->[1],"Planet has moved from $oldPlanet->[2]:$oldPlanet->[3]:$oldPlanet->[4] to $planet->[2]:$planet->[3]:$planet->[4] tick $tick";
 161                 }
 162         }
 163         #print "@{$oldPlanet}\n";
 164         #print "@{$planet}\n";
 165         $insert->execute(@{$planet}) or die $dbh->errstr;
 166 }
 167
 168
 169 $dumps->execute($tick,"galaxy");
 170 my @galaxies;
 171 if (@_ = $dumps->fetchrow){
 172         $_ = $_[0];
 173         $hour = (gmtime($_[1]))[2];
 174         while (m/(\d+)\t(\d+)\t\"(?:.+)\"\t(\d+)\t(\d+)\t(\d+)\t(\d+)/g){
 175                 push @galaxies,[$tick,$1,$2,$3,$4,$5,$6,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
 176         }
 177 }
 178
 179 for my $i (6,5,3,4){
 180         @galaxies = sort {$b->[$i] <=> $a->[$i]} @galaxies;
 181         my $rank = 0;
 182         for my $galaxy (@galaxies) {
 183                 $rank++;
 184                 $galaxy->[$i+4] = $rank;
 185     }
 186 }
 187
 188 my $findgalaxy = $dbh->prepare(q{SELECT tick,x, y,
 189         size, score, value, xp,
 190         sizerank, scorerank, valuerank, xprank,
 191         size_gain, score_gain, value_gain, xp_gain,
 192         sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain,
 193         size_gain_day, score_gain_day, value_gain_day, xp_gain_day,
 194         sizerank_gain_day, scorerank_gain_day, valuerank_gain_day, xprank_gain_day,
 195         planets,planets_gain,planets_gain_day
 196 FROM galaxies WHERE x = $1 AND y = $2 AND tick < $3 ORDER BY tick DESC LIMIT 1});
 197 $insert = $dbh->prepare(q{INSERT INTO galaxies(tick, x, y,
 198         size, score, value,xp,
 199         sizerank,scorerank,valuerank,xprank,
 200         size_gain, score_gain, value_gain, xp_gain,
 201         sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain,
 202         size_gain_day, score_gain_day, value_gain_day, xp_gain_day,
 203         sizerank_gain_day, scorerank_gain_day, valuerank_gain_day, xprank_gain_day,
 204         planets,planets_gain,planets_gain_day
 205         ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)});
 206 my $countplanets = $dbh->prepare(q{SELECT count(*) from planet_stats where x = $1 and y = $2 and tick = $3});
 207 for my $galaxy (@galaxies) {
 208
 209         my ($planets) = $dbh->selectrow_array($countplanets,undef,$galaxy->[1],$galaxy->[2],$tick) or die $dbh->errstr;
 210         $galaxy->[27] = $planets;
 211         $findgalaxy->execute($galaxy->[1],$galaxy->[2],$tick) or die $dbh->errstr;
 212         if (my @oldGalaxy = $findgalaxy->fetchrow_array){
 213                 for my $i (1,2,3,4){
 214                         $galaxy->[$i+10] = $galaxy->[$i+2] - $oldGalaxy[$i+2];
 215                         $galaxy->[$i+14] = $galaxy->[$i+6] - $oldGalaxy[$i+6];
 216                         $galaxy->[$i+18] = $galaxy->[$i+10] + $oldGalaxy[$i+18] if $hour;
 217                         $galaxy->[$i+22] = $galaxy->[$i+14] + $oldGalaxy[$i+22] if $hour;
 218                 }
 219                 $galaxy->[28] = $galaxy->[27] - $oldGalaxy[27];
 220                 $galaxy->[29] = $galaxy->[28] + $oldGalaxy[29] if $hour;
 221
 222         }
 223         $insert->execute(@{$galaxy}) or die $dbh->errstr;
 224         #print "@{$galaxy}\n";
 225 }
 226
 227
 228 #$dbh->rollback;
 229 $dbh->commit;
 230
 231 $countplanets->finish;
 232 $findgalaxy->finish;
 233 $findalliance->finish;
 234 $dumps->finish;
 235 $dbh->disconnect;