Update to new database structure

[NDIRC.git] / Commands / Members.pm

diff --git a/Commands/Members.pm b/Commands/Members.pm
index 34ef7d605028efa790c8935fdf336a976019b3dc..1a759b7495348a9cb08cee7003f5988e821fb974 100644 (file)
--- a/Commands/Members.pm
+++ b/Commands/Members.pm
@@ -35,11 +35,10 @@ sub def
 SELECT (c.landing_tick - tick()) AS eta, concat(i.shiptype||'/') AS shiptype
        , dc.username
 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 open AND (c.landing_tick - tick()) >= 7
-GROUP BY c.id,c.landing_tick,dc.username
+WHERE status = 'Open' AND (c.landing_tick - tick()) >= 7
+GROUP BY call,c.landing_tick,dc.username
 ORDER BY c.landing_tick;
                });
        $f->execute();
@@ -107,10 +106,13 @@ sub sms
        my ($self,$c,$msg) = @_;
        my ($nick) = $msg =~ /(\S+)/ or die 'ARGS';
        my $f = $c->model->prepare(q{
-SELECT username,COALESCE(sms,'nothing added') FROM users WHERE username ILIKE ?
+SELECT username,COALESCE(sms,'nothing added'), call_if_needed, timezone, sms_note
+       ,to_char(NOW() AT TIME ZONE timezone,'HH24:MI') AS time
+FROM users WHERE username ILIKE ?
                });
-       if (my ($username,$sms) = $c->model->selectrow_array($f,undef,$nick)){
-               $c->reply("<b>$username</b> has sms <b>$sms</b>");
+       if (my ($username,$sms, $call, $timezone, $note, $time) = $c->model->selectrow_array($f,undef,$nick)){
+               $call = $call ?  'Wake up if needed' : 'Do not wake up';
+               $c->reply("<b>$username</b> has sms <b>$sms</b>, $call, <b>$time</b> ($timezone), $note ");
        }else{
                $c->reply("No hit, maybe spelling mistake, or add % as wildcard");
        }