Update to new database structure

[NDIRC.git] / Commands / Def.pm

diff --git a/Commands/Def.pm b/Commands/Def.pm
index 9b2addbd255489a8fc73b8485cb455fa6851c4a0..1731689e12689e673d06cec3e0d1543e9acbb80c 100644 (file)
--- a/Commands/Def.pm
+++ b/Commands/Def.pm
@@ -53,25 +53,24 @@ sub defcall
        my $callinfo = "";
        if ($callnr){
                my $st = $dbh->prepare(q{
-SELECT covered
+SELECT status
        ,c.landing_tick - (SELECT value::integer FROM misc WHERE id = 'TICK') AS eta
        ,concat(i.shiptype||'/') AS shiptype
 FROM calls c
-       JOIN incomings i ON i.call = c.id
+       JOIN incomings i USING (call)
        LEFT OUTER JOIN users dc ON dc.uid = c.dc
-       JOIN users u ON u.uid = c.member
-WHERE c.id = ?
-GROUP BY c.id,c.landing_tick,c.covered
+WHERE c.call = ?
+GROUP BY c.call,c.landing_tick,c.status
 ORDER BY c.landing_tick;
                });
                my $call = $dbh->selectrow_hashref($st,undef,$callnr);
-               unless (defined $call->{covered}){
+               unless (defined $call->{status}){
                        $c->reply("No call with id: $callnr");
                        return;
                }
                chop($call->{shiptype});
                $callinfo = "(Anti $call->{shiptype} ETA: $call->{eta})";
-               if($call->{covered}){
+               if($call->{status} eq 'Covered'){
                        $c->reply("Call <b>$callnr</b> $callinfo is covered.");
                        return;
                }
@@ -91,10 +90,10 @@ sub settype
        my $dbh = $c->model;
 
        my $query = q{
-SELECT i.id,call,shiptype, coords(x,y,z),c.landing_tick - tick() AS eta
+SELECT inc,call,shiptype, coords(x,y,z),c.landing_tick - tick() AS eta
 FROM incomings i
-       JOIN current_planet_stats p ON i.sender = p.id
-       JOIN calls c ON i.call = c.id
+       JOIN current_planet_stats p USING (pid)
+       JOIN calls c USING (call)
        };
        my $fleets;
        my $type;
@@ -104,20 +103,20 @@ FROM incomings i
                ($call,$type) = $msg =~ /^(\d+) (.*)$/ or die 'ARGS';
 
                $fleets = $dbh->prepare($query . q{
-WHERE i.call = ?
+WHERE call = ?
                });
                $fleets->execute($call);
        }else{
                my ($id,$x,$y,$z,$t) = $msg =~ /^(\d+) (\d+):(\d+):(\d+) (.*)$/;
                if (defined $id){
                        $fleets = $dbh->prepare($query . q{
-WHERE i.call = ? AND p.id = planetid(?,?,?,tick())
+WHERE call = ? AND pid = planetid(?,?,?,tick())
                        });
                        $fleets->execute($id,$x,$y,$z);
                }else{
                        ($id,$t) = $msg =~ /^(\d+) (.*)$/ or die 'ARGS';
                        $fleets = $dbh->prepare($query . q{
-WHERE i.id = ?
+WHERE inc = ?
                        });
                        $fleets->execute($id);
                }
@@ -127,7 +126,7 @@ WHERE i.id = ?
        $type = CGI::escapeHTML($type);
        $dbh->begin_work;
        my $deflog = '';
-       my $settype = $dbh->prepare(q{UPDATE incomings SET shiptype = ? WHERE id = ?});
+       my $settype = $dbh->prepare(q{UPDATE incomings SET shiptype = ? WHERE inc = ?});
        while (my $inc = $fleets->fetchrow_hashref){
                $call //= $inc->{call};
                if ($inc->{eta} < 0){
@@ -137,9 +136,9 @@ WHERE i.id = ?
                        $dbh->rollback;
                        return;
                }
-               $settype->execute($type,$inc->{id});
-               $deflog .= "Set fleet: [B]$inc->{id} [/B] to: [B]$type [/B]\n";
-               $c->reply("Set fleet $inc->{id} from $inc->{coords} on call $call to $type (previously $inc->{shiptype})");
+               $settype->execute($type,$inc->{inc});
+               $deflog .= "Set fleet: [B]$inc->{inc} [/B] to: [B]$type [/B]\n";
+               $c->reply("Set fleet $inc->{inc} from $inc->{coords} on call $call to $type (previously $inc->{shiptype})");
        }
        if ($fleets->rows == 0){
                $c->reply("No matching fleets");
@@ -160,26 +159,26 @@ sub calltake
        my ($id) = $msg =~ /^(\d+)$/ or die 'ARGS';
        my $dbh = $c->model;
 
-       my $extra = '';
+       my $status = 'Open';
 
        given ($self->{name}){
                when('callignore'){
-                       $extra = ',covered = FALSE, open = FALSE'
+                       $status = 'Ignored';
                }
                when('callcov'){
-                       $extra = ',covered = TRUE, open = FALSE'
+                       $status = 'Covered';
                }
        }
 
        $dbh->begin_work;
        my $rows = $dbh->do(q{
 UPDATE calls SET dc = (SELECT uid FROM users WHERE hostmask ILIKE $1)
-       }. $extra .q{
-WHERE id = $2
-               },undef,$c->host,$id);
+       ,status = $3
+WHERE call = $2
+               },undef,$c->host,$id,$status);
        if ($rows == 1){
-               $c->reply("Marked call $id with ".$self->name);
-               $c->def_log($id , "Used: [B]".$self->name."[/B]");
+               $c->reply("Setting status on call $id to $status");
+               $c->def_log($id , "Changed status: [B]$status [/B]");
                $dbh->commit;
        }else{
                $c->reply("$id is not a valid call");