package ticketserver::DBManager;

use utf8;
use strict;
use warnings;

use ADBGUI::BasicVariables;
use ADBGUI::DBManager;
use ADBGUI::Tools qw(:DEFAULT Log time2sql localtime2sql normaliseLine sql2time sql2localtime);
use ticketserver::DBDesign qw(getPricesForCount $FILTERKAT $TICKETS $BOXES $PASS $VORLAGE $MAC $VERBUND $VORLAGENFLAG $MACSPEZIAL $URLFILTER $CUSTOMER $SURFTIMEFLAG $INSTALLATIONFLAG $VERBUNDFLAG $DATEOWNFLAG $BUCHUNGEN $ZAHLUNGSART);
use HTTP::Date;
use Time::Local;
use MIME::Base64;
use JSON qw(to_json from_json);
use Encode qw(from_to);
use ticketserver::Tools qw(getiTANforVerbundPlain);
use ticketserver::registration;
use Data::Dumper;
use Text::CSV;
use ADBGUI::Timetools qw(calcTimeInRanges getStartEndFromTimedefNoDB getRangesOnDay addDays);
use Digest::SHA qw(sha256);
use Math::BaseCalc;
use Math::BigInt;
use URI::Escape qw(uri_escape);
use CM::POETools qw(getSSL poe_sleep);

my $melden = "Bitte melden Sie sich <b>per WhatsApp<b> unter 0821/217009-97 ";

my $TICKETLEN = 10;
my $HASHMINLEN = 5;

our @ISA = ("ADBGUI::DBManager");

my @TICKETCOPY = ("beschreibung", "name", "room", "vorlage_id", "customer_id", "rabatt");

my $ticketcolor = {
   valid => "44FF44",      # Gr<C3><BC>n
   expired => "CCCCCC",    # Grau
   blocked => "FF8888",    # Rot
};

my $readerconfig = {
   nossl     => 1,
   redirects => 10,
   timeout   => 60, # (Sekunden) Verbindung schliessen nach letzem Aufbau;
};
my $AUTOMATFNKT = "Automat";
my $BUCHUNG_NGE = 3; # Next Generation Einzahlung

my $READER_BLOCK_TIME = 5 * 60; # (5 Min) Mindestzeit in sekunden zwischen 2 Benutzern.
my $MAXSTOPTIME       = 60 * 60; # Die minimale Wartezeit zwischen zwei Sitzungen des gleichen users
my $PERUSERMAXTIME    = 5 * 60; # Die maximale Zeit die ein user das Gerät für eine sitzung nutzen darf
my $MAX_READER_PING   = 5;

sub new {
   my $proto = shift;
   my $class = ref($proto) || $proto;
   my $self = shift;
   my $parent = $self ? ref($self) : "";
   @ISA = ($parent) if $parent;
   $self = $self ? $self : {};
   bless ($self, $class);

   $readerconfig->{ip}   = $self->{config}->{billreaderip};
   $readerconfig->{port} = $self->{config}->{billreaderport};
   $MAXSTOPTIME          = $self->{config}->{readermaxstoptime}
      if                   $self->{config}->{readermaxstoptime};
   $PERUSERMAXTIME       = $self->{config}->{readerperusermaxtime}
      if                   $self->{config}->{readerperusermaxtime};
   $READER_BLOCK_TIME    = $self->{config}->{readerblocktime}
      if                   $self->{config}->{readerblocktime};

   $self->{BOXDATA} = 9;
   $self->{registrations} = [{
      myconfig => {apname => "-"},
   }, {
      myconfig => {apname => "GUI"},
   }];
   my $id = 1;
   while (1) {
      if ($self->{config}->{"apip".$id}   &&
          $self->{config}->{"apwlanif".$id}) {
         push(@{$self->{registrations}}, ticketserver::registration->new($self->{config}, {
            apip     => $self->{config}->{"apip".$id},
            apwlanif => $self->{config}->{"apwlanif".$id},
            apsshkey => $self->{config}->{"apsshkey".$id},
            apname   => $self->{config}->{"apname".$id} || "Registration ".$id,
         }));
         $id++;
      } else {
         last;
      }
   }
   return $self;
}

sub getRightFlags {
   my $self = shift;
   my $curSession = shift;
   my $return = $self->SUPER::getRightFlags($curSession, @_);
   $return |= $SURFTIMEFLAG     if $curSession->{$USERSTABLENAME.$TSEP."surftime"};
   $return |= $INSTALLATIONFLAG if $curSession->{$USERSTABLENAME.$TSEP."installation"};
   $return |= $VERBUNDFLAG      if $curSession->{$USERSTABLENAME.$TSEP."verbund"};
   $return |= $DATEOWNFLAG      if $curSession->{$USERSTABLENAME.$TSEP."dateown"};
   $return |= $VORLAGENFLAG     if $curSession->{$USERSTABLENAME.$TSEP."vorlagen"};
   return $return;
}

sub checkRights {
   my $self = shift;
   my $session = shift;
   my $rights  = shift;
   my $table   = shift;

   # Wenn in unserer Anwendung das Adminrecht gesetzt ist, dann
   # darf der Benutzer auch Daten verändern
   if ($session->{$USERSTABLENAME.$TSEP.$ADMINFLAGCOLUMNNAME}) {
       $session->{$USERSTABLENAME.$TSEP.$MODIFYFLAGCOLUMNNAME}++ unless
	    $session->{$USERSTABLENAME.$TSEP.$MODIFYFLAGCOLUMNNAME};
   }
   # Die Tickets darf jeder gültige Benutzer ändern
   $rights = $NOTHING if (($rights == $MODIFY) && defined($table) && ($table eq $TICKETS));
   return $self->SUPER::checkRights($session, $rights, $table, @_);
}

sub getBoxTemplate {
   my $self       = shift;
   my $site       = shift;
   my $curSession = shift;
   my $boxid      = shift;
   my $db = $self->getDBBackend($BOXES);
   unless ($boxid =~ m,^[a-zA-Z0-9]+$,) {
      Log("No getBoxTemplate id!", $ERROR);
      return undef;
   }
   my $wherepre = []; # $self->Where_Pre($curSession, $BOXES);
   push(@$wherepre, $BOXES.$TSEP.$NAMECOLUMNNAME." = '".$boxid."'");
   if (my $ret = $db->getDataSet({
      session => $curSession,
      table   => $BOXES,
      wherePre  => $wherepre,
   })) {
      if (scalar(@{$ret->[0]}) == 1) {
         return $ret->[0]->[0]->{$BOXES.$TSEP.$site}
            if  $ret->[0]->[0]->{$BOXES.$TSEP.$site};
         if (my $verbund = $ret->[0]->[0]->{$BOXES.$TSEP.$VERBUND."_".$UNIQIDCOLUMNNAME}) {
            $db = $self->getDBBackend($VERBUND);
            if (my $retverbund = $db->getDataSet({
               session => $curSession,
               table   => $VERBUND,
               id      => $verbund,
            })) {
               return $retverbund->[0]->[0]->{$VERBUND.$TSEP.$site}
                  if ((scalar(@{$retverbund->[0]}) == 1) &&
                      $retverbund->[0]->[0]->{$VERBUND.$TSEP.$site});
            }
         }
         if (my $customer = $ret->[0]->[0]->{$BOXES.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}) {
            $db = $self->getDBBackend($CUSTOMER);
            if (my $retcustomer = $db->getDataSet({
               session => $curSession,
               table   => $CUSTOMER,
               id      => $customer,
            })) {
               return $retcustomer->[0]->[0]->{$CUSTOMER.$TSEP.$site}
                  if ((scalar(@{$retcustomer->[0]}) == 1) &&
                                $retcustomer->[0]->[0]->{$CUSTOMER.$TSEP.$site});
            }
         }
      } else {
         Log("BOXTEMPLATE Bad count: ".$ret->[1]." for boxid ".$BOXES.$TSEP.$NAMECOLUMNNAME." = '".$boxid."'", $WARNING);
      }
   } else {
      Log("BOXTEMPLATE SQL failed!", $WARNING);
   }
   return undef;
}

sub getiTANforVerbund {
   my $self = shift;
   my $curSession = shift;
   my $verbundid = shift;
   my $tannum = shift;
   my $forename = shift;
   my $name = shift;
   #$columns->{tannum} = $columns->{"enduser".$TSEP."tannum"};
   my $db = $self->getDBBackend($VERBUND);
   my $ret = undef;
   if ($verbundid && defined($ret = $db->getDataSet({
      table             => $VERBUND,
      session           => $curSession,
      $UNIQIDCOLUMNNAME => $verbundid,
   })) && (ref($ret) eq "ARRAY") && ($ret->[1] == 1) &&
          (ref($ret->[0]) eq "ARRAY") &&
          (ref($ret->[0]->[0]) eq "HASH") &&
       (exists($ret->[0]->[0]->{$VERBUND.$TSEP."tanid"})) &&
       defined($ret->[0]->[0]->{$VERBUND.$TSEP."tanid"})  &&
               $ret->[0]->[0]->{$VERBUND.$TSEP."tanid"}   &&
       (exists($ret->[0]->[0]->{$VERBUND.$TSEP."tananz"})) &&
       defined($ret->[0]->[0]->{$VERBUND.$TSEP."tananz"})  &&
               $ret->[0]->[0]->{$VERBUND.$TSEP."tananz"}) {
      return getiTANforVerbundPlain($tannum, $forename, $name, $ret->[0]->[0]->{$VERBUND.$TSEP."tanid"});
   }
   return undef;
}

sub getBoxId {
   my $self = shift;
   my $curSession = shift;
   my $boxid = shift;
   my $dbbox = $self->getDBBackend($BOXES);
   my $boxret = $dbbox->getDataSet({
      table     => $BOXES,
      session   => $curSession,
      wherePre  => $boxid ? [$BOXES.$TSEP.$NAMECOLUMNNAME." = '".normaliseLine($boxid)."'"] : undef,
   });
   my $newboxid = ($boxret && (ref(
                   $boxret) eq "ARRAY") &&
                  @$boxret) ?
                   $boxret->[0]->[0]->{$BOXES.$TSEP.$NAMECOLUMNNAME} : undef;
   Log("Not existing boxid ".$boxid, $WARNING)
      unless ($newboxid);
   return $newboxid;
}

sub getBoxCfgForID {
   my $self = shift;
   my $curSession = shift;
   my $boxid = shift;
   my $boxret = undef;
   my $dbbox = $self->getDBBackend($BOXES);
   unless (($boxret = $dbbox->getDataSet({
      table    => $BOXES,
      session  => $curSession,
      wherePre => $boxid ? [$BOXES.$TSEP.$NAMECOLUMNNAME." = '".normaliseLine($boxid)."'"] : undef,
   })) && (ref($boxret) eq "ARRAY") &&
          (ref($boxret->[0]) eq "ARRAY") &&
     (scalar(@{$boxret->[0]}) == 1)) {
      Log("getBoxCfgForID: No box found for ID=".($boxid||"-"), $WARNING);
      return undef;
   };
   return $boxret->[0]->[0];
}

sub surfrequestForBox {
   my $self = shift;
   my $curSession = shift;
   my $mac = shift;
   my $boxcfg = shift;
   my $nobook = shift;
   my $noticker = shift;
   return $self->surfrequest(
      $curSession,
      uc($mac),
      $boxcfg->{$BOXES.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME},
      $boxcfg->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME},
      $boxcfg->{$BOXES.$TSEP.$VERBUND."_".$UNIQIDCOLUMNNAME},
      $boxcfg->{$BOXES.$TSEP.$NAMECOLUMNNAME},
      $boxcfg->{$BOXES.$TSEP."noenduserverify"},
      $nobook,
      $noticker,
   );
}

sub surfrequestresult {
   my $self = shift;
   my $result = shift;
   my $customerID = shift;
   my $noticker = shift || 0;
   unless ($noticker) {
      # TODO:XXX:FIXME: Nur bei richtiger Customer ID?
      foreach my $curclient (grep {
         $_->{ticketticker} &&
        ($_->{ticketticker} eq $customerID)
      } @{$self->{TelnetServer}->getConnections()}) {
         $curclient->send(JSON->new->allow_nonref->encode({
            data   => [$result],
            result => "ticker",
         })."\n");
      };
   }
   return $result;
}

sub getTicketForMac {
   my $self = shift;
   my $curSession = shift;
   my $mac = shift;
   my $customerID = shift;
   my $dbmac    = $self->getDBBackend($MAC);
   my $ret = undef;
   return undef
      unless (((defined($ret = $dbmac->getDataSet({
         session   => $curSession,
         rows      => 1,
         table     => $MAC,
         sortby    => $MAC.$TSEP.$UNIQIDCOLUMNNAME."_",
         searchdef => {
            $MAC.$TSEP."address"                                                  => uc($mac),
            $TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP.$DELETEDCOLUMNNAME  => "0",
            $CUSTOMER."_".$UNIQIDCOLUMNNAME."_".$CUSTOMER.$TSEP.$UNIQIDCOLUMNNAME => $customerID,
         },
      })))) && (ref($ret) eq "ARRAY") &&
               (ref($ret->[0]) eq "ARRAY") &&
           scalar(@{$ret->[0]}));
   return $ret->[0]->[0];
}

sub surfrequest {
   my $self = shift;
   my $curSession = shift;
   my $mac = shift;
   my $customerID = shift;
   my $boxid = shift;
   my $verbundid = shift;
   my $boxname = shift;
   my $noenduserverify = shift;
   my $nobook = shift;
   my $noticker = shift;
   my $macticket = undef;
   my $deny = "";
   my $base = {
      mac             => uc($mac),
      noenduserverify => $noenduserverify,
   };

   my $modulelist = $self->getPaymentModuleList();
   my $deadline = undef;
   my $retticket = undef;
   my $macid = undef;
   my $curtime = time();
   my $ticketid = undef;
   my $columns = undef;
   if (@$modulelist) {
      ##############
      ### Anfordern
      ##############

      unless ($macticket = $self->getTicketForMac($curSession, $mac, $customerID)) {
         return $self->surfrequestresult({
            %$base,
            error  => "nomacfound",
            failed => 1,
         }, $customerID, $noticker);
      };

      ###############
      ### Überprüfen
      ###############

      # Keine Ticket ID
      $macid = $macticket->{$MAC.$TSEP.$UNIQIDCOLUMNNAME};
      $ticketid = $macticket->{$TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP.$UNIQIDCOLUMNNAME};
      unless (($base->{id} = $ticketid) && $macid) {
         Log("DBManager: onNewLineServer: SURFREQUEST: NO TICKET ID!", $WARNING);
         return $self->surfrequestresult({
            %$base,
            error  => "noticketid",
            failed => 1,
         }, $customerID, $noticker)
      }

      foreach my $curlink (
         [$CUSTOMER."_".$UNIQIDCOLUMNNAME."_".$CUSTOMER, $customerID, "USER", 1], # Ticket ist für anderen Kunden!
         [$BOXES.   "_".$UNIQIDCOLUMNNAME."_".$BOXES,    $boxid,      "BOX"    ], # Ticket gilt nicht auf Box
         [$VERBUND. "_".$UNIQIDCOLUMNNAME."_".$VERBUND,  $verbundid,  "CLUSTER"], # Ticket gilt nicht auf Cluster
      ) {
         if ($curlink->[3] ?
            !$macticket->{$curlink->[0].$TSEP.$UNIQIDCOLUMNNAME} ||
            ($macticket->{$curlink->[0].$TSEP.$UNIQIDCOLUMNNAME} ne $curlink->[1]) :
             $macticket->{$curlink->[0].$TSEP.$UNIQIDCOLUMNNAME} &&
            ($macticket->{$curlink->[0].$TSEP.$UNIQIDCOLUMNNAME} ne $curlink->[1])) {
             Log("DBManager: onNewLineServer: SURFREQUEST: WRONG ".$curlink->[2]." ID (".$macticket->{$curlink->[0].$TSEP.$UNIQIDCOLUMNNAME}." != ".$curlink->[1].")", $WARNING);
            return $self->surfrequestresult({
               %$base,
               error  => "bad".lc($curlink->[2]),
               failed => 1,
            }, $customerID, $noticker)
         }
      }

      ###########
      ### Ticket
      ###########

      # Das Ticket holen und somit auch remaining/daysremaining
      my $dbmac = $self->getDBBackend("mac");
      unless (((defined($retticket = $dbmac->getDataSet({
         session => $curSession,
         table   => $TICKETS,
         id      => $ticketid,
      })))) && (ref($retticket) eq "ARRAY") &&
               (ref($retticket->[0]) eq "ARRAY") &&
          (scalar(@{$retticket->[0]}) == 1) &&
                    $retticket->[0]->[0]->{$TICKETS.$TSEP.$UNIQIDCOLUMNNAME} &&
                   ($retticket->[0]->[0]->{$TICKETS.$TSEP.$UNIQIDCOLUMNNAME} eq $ticketid)) {
         Log("DBManager: onNewLineServer: SURFREQUEST: NO TICKET ID!", $WARNING);
         return $self->surfrequestresult({
            %$base,
            error  => "unfetchableticket",
            failed => 1,
         }, $customerID, $noticker)
      }

      # Deadline ausrechnen
      $columns = {};
      my $reftime = undef;
      $deadline = $retticket->[0]->[0]->{$TICKETS.$TSEP.$TIMESTAMP} ?
            str2time($retticket->[0]->[0]->{$TICKETS.$TSEP.$TIMESTAMP}) : undef;
      unless ($retticket->[0]->[0]->{$TICKETS.$TSEP."start"} && ($reftime =
     str2time($retticket->[0]->[0]->{$TICKETS.$TSEP."start"}))) {
         $reftime = str2time($macticket->{$MAC.$TSEP."start"})
            if ($retticket->[0]->[0]->{$TICKETS.$TSEP."used"});
         $reftime ||= $curtime;
         $columns->{$TICKETS.$TSEP."start"} = ADBGUI::Tools::MakeTime($reftime)
            if $reftime;
      }
      if ($reftime && $retticket->[0]->[0]->{$TICKETS.$TSEP."maxusedays"}) {
         $deadline = $reftime + int($retticket->[0]->[0]->{$TICKETS.$TSEP."maxusedays"} * 60 * 60 * 24);
         $columns->{$TICKETS.$TSEP.$TIMESTAMP} = ADBGUI::Tools::MakeTime($deadline);
      }
      $deadline -= $curtime
         if $deadline;
      # Resultierende Endzeit is falsch oder abgelaufen!
      if ($deadline &&
         ($deadline =~ /^\-?\d+$/)) {
         $deny .= "exhausted ".int($deadline / 60 / 60)."h"
            unless ($deadline > 0);
      } else {
         $deny .= "baddeadline";
      }
   } else {
      $deadline = 5*60;
   }
   # TODO:XXX:FIXME: geht noenduserverify noch vom Verbund/Custumer/Ticket?
   if ($noenduserverify || ($retticket &&
      ($retticket->[0]->[0]->{$CUSTOMER."_".$UNIQIDCOLUMNNAME."_".$CUSTOMER.$TSEP."noenduserverify"} ||
       $retticket->[0]->[0]->{$VERBUND. "_".$UNIQIDCOLUMNNAME."_".$VERBUND.$TSEP. "noenduserverify"} ||
       $retticket->[0]->[0]->{$TICKETS.$TSEP."noenduserverify"}))) {
      $base->{noenduserverify}++;
   } else {
      my $dbenduser = $self->getDBBackend("enduser");
      # Ueberpruefen ob der Endbenutzer freigeschaltet ist
      my $retenduser = $dbenduser->getDataSet({
         table     => "enduser",
         session   => $curSession,
         searchdef => {
            "enduser".$TSEP."address" => uc($mac),
         },
      });
      unless ((ref($retenduser) eq "ARRAY") &&
                  ($retenduser->[1] == 1)) {
         Log("Kein Eintrag fuer Enduser mit mac=".uc($mac), $WARNING);
         $deny .= "noenduser";
      }
      unless ($retenduser->[0]->[0]->{"enduser".$TSEP."verified"}) {
         Log("Enduser wuerde nicht bestaetigt!", $WARNING);
         $deny .= "noverified";
      }
   }

   ##############
   ### Reagieren
   ##############

   if ($deny) {
      $self->{registrations}->[0]->{active}->{uc($mac)} = [time()];
      return $self->surfrequestresult({
         %$base,
         error  => $deny,
         failed => 1,
      }, $customerID, $noticker)
   }
   if (@$modulelist) {
      my $dbmac = $self->getDBBackend("mac");
      my $retmacnewupdate = $dbmac->updateDataSet({
         table   => $MAC,
         session => $curSession,
         id      => $macid,
         user    => $boxname,
         columns => {
            # TODO:XXX:FIXME: Auch standort dazu machen?
            $MAC.$TSEP.$TIMESTAMP => ADBGUI::Tools::MakeTime($curtime),
         },
      }) unless $nobook;
      Log("DBManager: onNewLineServer: SURFREQUEST: OKAY: Ticket:".$ticketid." MAC:".uc($mac)." DEADLINE:".$deadline, $INFO);
      # Nutzungszeit
      if (my $nutzungszeit = $retticket->[0]->[0]->{$TICKETS.$TSEP."nutzungszeit"}) {
         my $now = time();
         my $yesterday = addDays($now,-1);
         my $profile = getRangesOnDay(
            getStartEndFromTimedefNoDB($nutzungszeit, $now, $yesterday, 0, 0),
            getStartEndFromTimedefNoDB($nutzungszeit, $now, $now,       0, 0), $now);
         $deadline = calcTimeInRanges($profile, time(), time()+1,    "FINDME") ?
                     calcTimeInRanges($profile, time(), time()+60*5, "FINDME") : 0;
         Log("Surfrequest outside of nutzungszeit ".$nutzungszeit." -> ".$deadline." MAC=".uc($mac), $WARNING);
         return $self->surfrequestresult({
            %$base,
            error  => "outofnutzungszeit",
            failed => 1,
         }, $customerID, $noticker) unless $deadline;
      }
      # Ticket Timestamp und Zeitraum updaten
      unless ($nobook) {
         my $dbticket = $self->getDBBackend($TICKETS);
         my $retupdateticket = $dbticket->updateDataSet({
            table   => $TICKETS,
            id      => $ticketid,
            session => $curSession,
            user    => $boxname,
            columns => {
               %$columns,
               # TODO:XXX:FIXME: Auch standort dazu machen?
               $TICKETS.$TSEP."used" => 1,
            },
         });
         if (!defined($retupdateticket)             ||
                     ($retupdateticket !~ m,^\d+$,) ||
             !defined($retmacnewupdate)             ||
                     ($retmacnewupdate !~ m,^\d+$,)) {
            Log("DBManager: onNewLineServer: SURFREQUEST: UPDATES FAILED ticketid=".$ticketid." retupdateticket=".($retupdateticket || "-")." retmacnewupdate=".($retmacnewupdate||"-")." nobook=".($nobook||"-"), $ERROR);
            return $self->surfrequestresult({
               %$base,
               error  => "failedupdates",
               failed => 1,
            }, $customerID, $noticker)
         }
      }
      # URLFilter
      if (my $filterid = $retticket->[0]->[0]->{$TICKETS.$TSEP.$URLFILTER."_".$UNIQIDCOLUMNNAME}) {
         my $wherepre = $self->Where_Pre($curSession, $FILTERKAT."_".$URLFILTER);
         push(@$wherepre, $FILTERKAT."_".$URLFILTER.$TSEP.$URLFILTER."_".$UNIQIDCOLUMNNAME." = ".$filterid);
         my $filterret = $dbmac->getDataSet({
            table     => $FILTERKAT."_".$URLFILTER,
            session   => $curSession,
            wherePre  => $wherepre,
         });
         $base->{filter}->{allow} = [map {
            $_->{$FILTERKAT."_".$UNIQIDCOLUMNNAME."_".$FILTERKAT.$TSEP."name"}
         } @{$filterret->[0]}]
            if ((ref($filterret)      eq "ARRAY") &&
                (ref($filterret->[0]) eq "ARRAY") &&
                    ($filterret->[1] > 0));
      }
   }
   return $self->surfrequestresult({
      %$base,
      deadline => $deadline,
      success  => 1,
   }, $customerID, $noticker)
}

sub getTicketHistory {
   my $self = shift;
   my $curSession = shift;
   my $mac = shift;
   my $return = {};
   my $dbmacgroup = $self->getDBBackend("mac");
   my $retmacgroup = undef;
   unless ($mac) {
      Log("getTicketHistory: no mac?!", $ERROR);
      return undef;
   }
   if (($retmacgroup = $dbmacgroup->getDataSet({
      table     => $MAC,
      session   => $curSession,
      searchdef => {
         $MAC.$TSEP."address" => uc($mac),
      },
   })) && (ref($retmacgroup) eq "ARRAY") &&
          (ref($retmacgroup->[0]) eq "ARRAY") &&
      scalar(@{$retmacgroup->[0]})) {
      my $lastticket = undef;
      foreach my $curmacline (sort {
         $a->{$MAC.$TSEP."tickets"."_".$UNIQIDCOLUMNNAME} <=>
         $b->{$MAC.$TSEP."tickets"."_".$UNIQIDCOLUMNNAME}
      } @{$retmacgroup->[0]}) {
         my $curticket = $curmacline->{$MAC.$TSEP."tickets"."_".$UNIQIDCOLUMNNAME};
         next
            if $lastticket &&
              ($lastticket eq $curticket);
         #print "Found for mac ".uc($mac)." the tickets ".$curticket."\n";
         $return->{$curticket}->{mac} = $curmacline;
         my $dbmac = $self->getDBBackend("mac");
         my $ret = undef;
         unless (($ret = $dbmac->getDataSet({
            table     => $TICKETS,
            session   => $curSession,
            nodeleted => 1,
            searchdef => {
               $TICKETS.$TSEP.$UNIQIDCOLUMNNAME => $curticket,
            },
         })) && (ref($ret) eq "ARRAY") &&
               (ref($ret->[0]) eq "ARRAY") &&
          (scalar(@{$ret->[0]})) == 1) {
            Log("Cannot fetch ticket ".$curticket." for ticket history!", $WARNING);
            next;
         }
         $return->{$curticket}->{$TICKETS} = $ret->[0]->[0];
         $return->{$curticket}->{macs} = $self->getTicketHistoryForTicket($curSession, $curticket);
         $lastticket = $curticket;
      }
   }
   return $return;
}

sub getTicketHistoryForTicket {
   my $self = shift;
   my $curSession = shift;
   my $curticket = shift;
   my $return = {};
   my $dbmacgroupmacs = $self->getDBBackend($MAC);
   my $retmacgroupmacs = undef;
   if (($retmacgroupmacs = $dbmacgroupmacs->getDataSet({
      table     => $MAC,
      session   => $curSession,
      searchdef => {
         $MAC.$TSEP."tickets"."_".$UNIQIDCOLUMNNAME => $curticket,
      },
   })) && (ref($retmacgroupmacs) eq "ARRAY") &&
          (ref($retmacgroupmacs->[0]) eq "ARRAY") &&
      scalar(@{$retmacgroupmacs->[0]})) {
       my $lastmacgroupmac = undef;
       foreach my $curmacgroupmac (sort {
          $a->{$MAC.$TSEP."address"} cmp
          $b->{$MAC.$TSEP."address"}
       } @{$retmacgroupmacs->[0]}) {
         if ($lastmacgroupmac &&
            ($lastmacgroupmac eq $curmacgroupmac->{$MAC.$TSEP."address"})) {
            Log("Skipping ".$curmacgroupmac->{$MAC.$TSEP."address"}." == ".$lastmacgroupmac, $INFO);
            next;
         }
         $return->{uc($curmacgroupmac->{$MAC.$TSEP."address"})} = $self->getDBBackend("enduser")->getDataSet({
            table     => "enduser",
            session   => $curSession,
            searchdef => {
               "enduser".$TSEP."address" => uc($curmacgroupmac->{$MAC.$TSEP."address"}),
            },
         })->[0];
         #print "Reenable ticket ".$curticket." with MAC ".uc($curmacgroupmac->{$MAC.$TSEP."address"})."\n";
         $lastmacgroupmac = $curmacgroupmac->{$MAC.$TSEP."address"};
      }
   }
   return $return;
}

sub doBase {
   my $calc36 = new Math::BaseCalc(digits=>[2,7,9,"C","D","F","H","K","M","N","P","R","S","T","U","V","W","X"]);
   return $calc36->to_base(Math::BigInt->from_bytes(shift));
}

sub calcKey {
   my $num = shift;
   my $secret = shift;
   return uc($num).doBase(sha256(uc($num).$secret));
}

sub getVerifiedNum {
   my $self = shift;
   return getVerifiedNumConfig($self->{config}, @_);
}

sub getVerifiedNumConfig {
   my $config = shift;
   my $pass = shift;
   unless ($config->{ticketsecret}) {
      Log("No ticketsecret!", $WARNING);
      return 0;
   }
   my $numpart = '';
   foreach my $size (1..($TICKETLEN-$HASHMINLEN)) {
      my $num = substr($pass, 0, $size);
      my $curkey = calcKey($num, $config->{ticketsecret});
      $curkey = substr($curkey, 0, $TICKETLEN);
      #print "Testing ".$num." -> ".$curkey." = ".uc($pass)."\n";
      my $calc36 = new Math::BaseCalc(digits=>[2,7,9,"C","D","F","H","K","M","N","P","R","S","T","U","V","W","X"]);
      my $calc10 = new Math::BaseCalc(digits=>[0..9]);
      return $calc10->to_base($calc36->from_base($num))
         if (uc($curkey) eq uc($pass));
   }
   return 0;
}

sub getTicketsForBoxCfg {
   my $self = shift;
   my $curSession = shift;
   my $boxcfg = shift;
   my $dbmac = $self->getDBBackend($MAC);
   my $macs = {};
   my $retticket = undef;
   if (((defined($retticket = $dbmac->getDataSet({
       session   => $curSession,
       table     => $MAC,
       searchdef => {
          $CUSTOMER."_".$UNIQIDCOLUMNNAME."_".$CUSTOMER.$TSEP.$UNIQIDCOLUMNNAME => $boxcfg->{$BOXES.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME},
       },
   })))) && (ref($retticket) eq "ARRAY") &&
            (ref($retticket->[0]) eq "ARRAY") &&
        scalar(@{$retticket->[0]})) {
      foreach my $curmac (@{$retticket->[0]}) {
         $macs->{uc($curmac->{$MAC.$TSEP."address"})}++;
      }
      foreach my $curmac (keys %$macs) {
         my $result = $self->surfrequestForBox($curSession, $curmac, $boxcfg, 1, 1);
         #print "JSON TICKETS MAC=".$curmac." RESULT=";
         if ($result->{success}) {
            #print "OK TICKET=".$result->{id}." DEADLINE=".$result->{deadline};
            $macs->{$curmac} = $result;
         } else {
            #print "FAIL";
            delete $macs->{$curmac};
         }
         #print "\n";
      }
   } else {
      Log("ticketserver::DBManager: onNewLineServer: JSON TICKETS: No lines given!", $WARNING);
   }
   return $macs;
}

sub doEnduser {
   my $self = shift;
   my $curSession = shift;
   my $context = shift;
   return
      if $context->{boxcfg}->{$BOXES.$TSEP."noenduserverify"} ||
         $context->{activeticket}->{"noenduserverify"};
   my $db = $self->getDBBackend("verifiedart");
   my $verifiedart = $db->getDataSet({
      table     => "verifiedart",
      session   => $curSession,
      wherePre  => $self->Where_Pre($curSession, "verifiedart"),
   });
   my $curtabledef = $self->getTableDefiniton("enduser");
   my $columns = {
      (map {
         ("enduser".$TSEP.$_->[0] => $_->[1])
      } map {
         [$_, $curtabledef->{columns}->{$_}->{defaultfunc} ? $curtabledef->{columns}->{$_}->{defaultfunc}({dbm => $self}) : ""]
      } keys %{$curtabledef->{columns}}),
      "enduser".$TSEP."address"  => uc($context->{mac}),
      "myiTAN"                   => "",
   };
   $columns->{"verifiedart"} =
      ((ref($verifiedart) eq "ARRAY") &&
      (ref($verifiedart->[0]) eq "ARRAY")) ?
      "<select style='font-size: 8px;' name='enduser".$TSEP."verifiedart"."_".$UNIQIDCOLUMNNAME."'>".join("\n", map {
         '<option style="font-size: 8px;" value="'.$_->{"verifiedart".$TSEP.$UNIQIDCOLUMNNAME}.'">'.
          $_->{"verifiedart".$TSEP."name"}.'</option>'
      } @{$verifiedart->[0]})."</select>" : "ERROR fetching verifiedart";
   $db = $self->getDBBackend("enduser");
   my $wherepre = $self->Where_Pre($curSession, "enduser");
   push(@$wherepre, "enduser".$TSEP."address = '".uc($context->{mac})."'");
   my $enduserret = $db->getDataSet({
      table     => "enduser",
      session   => $curSession,
      wherePre  => $wherepre,
   });
   if (!((ref($enduserret) eq "ARRAY") &&
      scalar(@$enduserret) &&
         (ref($enduserret->[0]) eq "ARRAY"))) {
      $context->{template} = $self->{gui}->replacer($self->getBoxTemplate("start", $curSession, $context->{boxid}), {
         'ticketstreet' => $self->{config}->{ticketstreet} || "Unbekannt",
         'ticketplz'    => $self->{config}->{ticketplz} || "",
         'ticketcity'   => $self->{config}->{ticketcity} || "",
         'id'           => $self->{config}->{id},
         'boxid'        => $context->{boxid},
         'agent'        => $context->{agent},
         'mac'          => $context->{mac},
         'altoption'    => "",
         'statusinfo'   => "",
         'selectoption' => "",
         'errortext'    => "Internal error",
         'myticket'     => "",
         'mypasswort'   => "",
         'info'         => $context->{info} || "",
         'qrcode'       => $self->{config}->{qrcodebase}.$context->{mac},
         'userid'       => $self->{config}->{id}."M.".$context->{mac},
      });
   } elsif ($enduserret->[1] == 1) {
      $context->{enduserid} = $enduserret->[0]->[0]->{"enduser".$TSEP.$UNIQIDCOLUMNNAME};
      unless ($enduserret->[0]->[0]->{"enduser".$TSEP."verified"}) {
         # Bestehender nutzer ohne ITAN
         $columns = {
            %$columns,
            %{$enduserret->[0]->[0]},
            myiTAN => "",
         };
         $columns->{"verifiedart"} =
            ((ref($verifiedart) eq "ARRAY") &&
             (ref($verifiedart->[0]) eq "ARRAY")) ?
            #join(";", keys %{$enduserret->[0]->[0]}).
            "<select name='enduser".$TSEP."verifiedart"."_".$UNIQIDCOLUMNNAME."'>".join("\n", map {
               '<option value="'.$_->{"verifiedart".$TSEP.$UNIQIDCOLUMNNAME}.'" '.(
                  ($enduserret->[0]->[0]->{"enduser".$TSEP."verifiedart"."_".$UNIQIDCOLUMNNAME} eq
                                                      $_->{"verifiedart".$TSEP.$UNIQIDCOLUMNNAME}) ? " selected": "").'>'.
                  #$enduserret->[0]->[0]->{"enduser".$TSEP."verifiedart"."_".$UNIQIDCOLUMNNAME}."<>".
                  #                                   $_->{"verifiedart".$TSEP.$UNIQIDCOLUMNNAME}." ".
                  $_->{"verifiedart".$TSEP."name"}.'</option>'
            } @{$verifiedart->[0]})."</select>" : "ERROR fetching verifiedart";
         foreach my $column (%$columns) {
            from_to($columns->{$column}, "iso-8859-1", "utf8");
         }
         #print "   XXX   ".$json->{param}->{submit}."\n";
         #print "\n\nZZZZ\n".join(";", map { $_."=".$json->{param}->{$_} } sort { $a cmp $b } keys %{$json->{param}});
         if ($context->{param}->{lala}) { # TODO:XXX:FIXME: Was ist lala?!
            Log("ERROR deleting ID ".$columns->{"enduser".$TSEP.$UNIQIDCOLUMNNAME}." for ".$context->{mac}, $ERROR)
               unless ($db->deleteDataSet({
                  table   => "enduser",
                  session => $curSession,
                  id      => $enduserret->[0]->[0]->{"enduser".$TSEP.$UNIQIDCOLUMNNAME},
               }));
            if (($columns->{"enduser".$TSEP."gebdatum"}) &&
                ($columns->{"enduser".$TSEP."gebdatum"} =~ m/^\s*(\d{4})\-(\d{1,2})\-(\d{1,2})/)) {
               $columns->{"enduser".$TSEP."gebdatum"} = $3.".".$2.".".$1;
            }
            $context->{template} = $self->{gui}->replacer($self->getBoxTemplate($context->{iphoneprivate} && $self->{config}->{noiphoneprivat} ? "login" : "register", $curSession, $context->{boxid}), {
               %$columns,
               'ticketstreet' => $self->{config}->{ticketstreet} || "Unbekannt",
               'ticketplz'    => $self->{config}->{ticketplz} || "",
               'ticketcity'   => $self->{config}->{ticketcity} || "",
               'info'         => $context->{info} || "",
               'boxid'        => $context->{boxid},
               'agent'        => $context->{agent},
               'id'           => $self->{config}->{id},
               'userid'       => $self->{config}->{id}."M.".$context->{mac},
            });
         } else {
            if ($context->{param}->{myiTAN} &&
                $columns->{"enduser".$TSEP."tannum"} &&
                $columns->{"enduser".$TSEP."forename"} &&
                $columns->{"enduser".$TSEP."name"} &&
                $columns->{"enduser".$TSEP.$UNIQIDCOLUMNNAME}) {
               $columns->{myiTAN} = $context->{param}->{myiTAN}; # ." != ".$salt.":".iTAN($salt);
               if (my $itan = $self->getiTANforVerbund($curSession, 1, $columns->{"enduser".$TSEP."tannum"}, (
                                                                       $columns->{"enduser".$TSEP."forename"},
                                                                       $columns->{"enduser".$TSEP."name"}))) {
                  if (($context->{param}->{myiTAN} eq $itan) ||
                      ($context->{param}->{myiTAN} eq $self->{config}->{itanmasterpass})) {
                     if (my $retupdateenduser = $db->updateDataSet({
                        table   => "enduser",
                        session => $curSession,
                        id      => $columns->{"enduser".$TSEP.$UNIQIDCOLUMNNAME},
                        columns => {
                           "enduser".$TSEP."verified" => 1,
                        },
                     }) =~ m,^\d+$,) {
                        Log("Error creating enduser: ".$retupdateenduser, $WARNING);
                        $context->{error} .= "Error creating enduser";
                     } else {
                        $columns->{myiTAN} = "ERROR1";
                     }
                  } else {
                     # TODO:XXX:FIXME: Mehrfache Falscheingabe muß sperren oder prellen oder so...
                     #$columns->{myiTAN} = "ERROR2"; # :".join(";", keys %{$connection->{boxsession}}).":".$connection->{boxsession}->{$BOXES.$TSEP.$VERBUND."_".$UNIQIDCOLUMNNAME}.":!";
                     #$columns->{myiTAN} .= " != ".$itan;
                  }
               } else {
                  $columns->{myiTAN} = "ERROR3"; # :".join(";", keys %{$connection->{boxsession}}).":".$connection->{boxsession}->{$BOXES.$TSEP.$VERBUND."_".$UNIQIDCOLUMNNAME}.":!";
               }
            }
            $context->{template} = $self->{gui}->replacer($self->getBoxTemplate("registerok", $curSession, $context->{boxid}), {
               %$columns,
               'ticketstreet' => $self->{config}->{ticketstreet} || "Unbekannt",
               'ticketplz'    => $self->{config}->{ticketplz} || "",
               'ticketcity'   => $self->{config}->{ticketcity} || "",
               'info'         => $context->{info} || "",
               'boxid'        => $context->{boxid},
               'agent'        => $context->{agent},
               'id'           => $self->{config}->{id},
               'userid'       => $self->{config}->{id}."M.".$context->{mac},
            });
         }
      }
   } elsif($enduserret->[1] == 0) {
      # Neuer Nenutzer
      my $insertdata = {
         # TODO:XXX:FIXME: Wir uebernehmen hier einfach alles was da im Query ankam?! Realy?! -> Filtern!!!
         %{$context->{param}},
         "enduser".$TSEP."address" => uc($context->{mac}),
         # TODO:XXX:FIXME: Hotfix! Eine TAN aus 30 ist hier fest konfiguriert!!!
         # TODO:XXX:FIXME: Durch oft genuges regstrieren/löschen kann man beliebige tannum erzeugen!
         "enduser".$TSEP."tannum" => int(rand(29)+1),
         "enduser".$TSEP."agent"  => $context->{agent},
      };
      $insertdata = {(map { ($_ => $insertdata->{$_}) } grep { $_ =~ m,^enduser, } keys %$insertdata)};
      $insertdata->{"enduser".$TSEP."created"} = ADBGUI::Tools::MakeTime(time());
      $insertdata->{"enduser".$TSEP."deleted"} = 0;
      if ($self->{config}->{noverify}) {
         $insertdata->{"enduser".$TSEP."verified"} = 1;
         $insertdata->{"enduser".$TSEP."verifiedart"."_".$UNIQIDCOLUMNNAME} ||= 8;
         $insertdata->{"enduser".$TSEP."stelle"} ||= "Unbekannt";
         $insertdata->{"enduser".$TSEP."auswnummer"} ||= "Unbekannt";
      }
      if ($insertdata->{"enduser".$TSEP."forename"} &&
          $insertdata->{"enduser".$TSEP."name"}) {
         foreach my $curcolumn (keys %$insertdata) {
            from_to($insertdata->{$curcolumn}, "utf8", "iso-8859-1");
            $insertdata->{$curcolumn} =~ s/[^a-zA-Z0-9\u00C0-\u00ff\-\,\.\ \:]//g;
         }
         if    (!$insertdata->{"enduser".$TSEP."gebdatum"} && $self->{config}->{noverify}) {
                 $insertdata->{"enduser".$TSEP."gebdatum"} = "0000-00-00 00:00:00";
         } elsif($insertdata->{"enduser".$TSEP."gebdatum"} =~ m/^\s*(\d{1,2})\.(\d{1,2})\.(\d{4})\s*$/) {
                 $insertdata->{"enduser".$TSEP."gebdatum"} = $3."-".$2."-".$1;
         } else {
            $insertdata->{"verifiedart"} =
               ((ref($verifiedart) eq "ARRAY") &&
                (ref($verifiedart->[0]) eq "ARRAY")) ?
               #join(";", keys %{$enduserret->[0]->[0]}).
               "<select name='enduser".$TSEP."verifiedart"."_".  $UNIQIDCOLUMNNAME."'>".join("\n", map {
                      '<option value="'.$_->{"verifiedart".$TSEP.$UNIQIDCOLUMNNAME}.'" '.(
              ($insertdata->{"enduser".$TSEP."verifiedart"."_".  $UNIQIDCOLUMNNAME} eq
                                        $_->{"verifiedart".$TSEP.$UNIQIDCOLUMNNAME}) ? " selected": "").'>'.
               #$insertdata->{"enduser".$TSEP."verifiedart"."_".$UNIQIDCOLUMNNAME}."<>".
               #                         $_->{"verifiedart".$TSEP.$UNIQIDCOLUMNNAME}." ".
                                        $_->{"verifiedart".$TSEP."name"}.'</option>'
            } @{$verifiedart->[0]})."</select>" : "ERROR fetching verifiedart";
            $context->{template} = $self->{gui}->replacer($self->getBoxTemplate($context->{iphoneprivate} && $self->{config}->{noiphoneprivat} ? "login" : "register", $curSession, $context->{boxid}), {%$insertdata,
               'ticketstreet' => $self->{config}->{ticketstreet} || "Unbekannt",
               'ticketplz'    => $self->{config}->{ticketplz}    || "",
               'ticketcity'   => $self->{config}->{ticketcity}   || "",
               'info'         => $context->{info} || "",
               'boxid'        => $context->{boxid},
               'agent'        => $context->{agent},
               'id'           => $self->{config}->{id},
               'userid'       => $self->{config}->{id}."M.".$context->{mac},
            });
         }
         unless ($context->{template}) {
            foreach my $column (%$insertdata) {
               from_to($insertdata->{$column}, "iso-8859-1", "utf8");
            }
            $enduserret = $db->insertDataSet({
               table   => "enduser",
               user    => $context->{boxcfg}->{$BOXES.$TSEP.$NAMECOLUMNNAME} || (
                  $context->{boxid} ? "BOXID=".
                  $context->{boxid} : "UNKNOWNBOX"),
               session => $curSession,
               columns => $insertdata,
            });
            my $enduserret = $db->getDataSet({
               table     => "enduser",
               session   => $curSession,
               wherePre  => $wherepre,
            });
            return
               if ((ref($enduserret) eq "ARRAY") &&
                   (ref($enduserret->[0]) eq "ARRAY") &&
                       ($enduserret->[1] == 1));
         }
      }
      $context->{info} ||= "M.".uc($context->{mac}),
      $context->{template} ||= $self->{gui}->replacer($self->getBoxTemplate($context->{iphoneprivate} && $self->{config}->{noiphoneprivat} ? "login" : "register", $curSession, $context->{boxid}), {
         %$columns,
         'ticketstreet' => $self->{config}->{ticketstreet} || "Unbekannt",
         'ticketplz'    => $self->{config}->{ticketplz}    || "",
         'ticketcity'   => $self->{config}->{ticketcity}   || "",
         'info'         => $context->{info} || "",
         'boxid'        => $context->{boxid},
         'agent'        => $context->{agent},
         'id'           => $self->{config}->{id},
         'userid'       => $self->{config}->{id}."M.".$context->{mac},
      });
   }
}

sub createBuchungAndTicket {
   # TODO:XXX:FIXME: Unterstuetzen dass man nen Ticket verlaengert! Also neu bucht wenn es noch gueltig ist, oder im neuen Ticket die Restzeit bucht.
   my $self = shift;
   my $curSession = shift;
   my $customerID = shift;
   my $mac = shift;
   my $brumsid = shift;
   my $zahlart = shift;
   my $payed = shift || 0;
   my $tickethistorylastticket = undef;
   my $lastticketid = undef;
   if (my $macticket = $self->getTicketForMac($curSession, $mac, $customerID)) {
      if (my $tickethistory = $self->getTicketHistory($curSession, $mac)) {
         $lastticketid = $macticket->{$TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP.$UNIQIDCOLUMNNAME};
         $tickethistorylastticket = $tickethistory->{$lastticketid};
      }
   }
   my $columnsbrums = {
      $TICKETS.$TSEP.$TIMESTAMP   => $self->{config}->{newmydate} || "2030-12-31 00:00:00",
      $TICKETS.$TSEP."deleted"    => 0, ($tickethistorylastticket ? (map { (
      $TICKETS.$TSEP.$_           => $tickethistorylastticket->{mac}->{$TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP.$_}) } (@TICKETCOPY)) : ()),
   };
   $columnsbrums->{$TICKETS.$TSEP."vorlage_id"}  ||= $self->{config}->{newvorlage_id}  || 1;
   $columnsbrums->{$TICKETS.$TSEP."customer_id"} ||= $self->{config}->{newcustomer_id} || 1;
   my $dbticket = $self->getDBBackend($TICKETS);
   my $insertticket = $dbticket->insertDataSet({
      table   => $TICKETS,
      user    => $mac,
      session => $curSession,
      columns => $columnsbrums,
   });
   if ($insertticket &&
      ($insertticket =~ m,^\d+$,)) {
      my $buchungsid = $self->getDBBackend($BUCHUNGEN)->insertDataSet({
         session => $curSession,
         table   => $BUCHUNGEN,
         columns => {
            $BUCHUNGEN.$TSEP.$TICKETS."_".$UNIQIDCOLUMNNAME     => $insertticket,
            $BUCHUNGEN.$TSEP."payment"                          => $payed,
            $BUCHUNGEN.$TSEP.$ZAHLUNGSART."_".$UNIQIDCOLUMNNAME => $zahlart,
            $BUCHUNGEN.$TSEP."brumsok"                          => $payed ? 1 : 0,
            $BUCHUNGEN.$TSEP."brums"                            => $brumsid,
            $BUCHUNGEN.$TSEP.$TIMESTAMP                         => localtime2sql(time()),
         },
      });
      return     $buchungsid
         unless ($buchungsid &&
                ($buchungsid =~ m,^\d+$,));
      my $macloop = [$tickethistorylastticket &&
                     $tickethistorylastticket->{macs} &&
             (keys %{$tickethistorylastticket->{macs}}) ?
              keys %{$tickethistorylastticket->{macs}} : $mac];
      my $dbmac = $self->getDBBackend($MAC);
      Log("Newticket ".$insertticket, $INFO);
      foreach my $curmac (@$macloop) {
         Log(" - MAC ".$curmac, $INFO);
         if (my $macticket = $self->getTicketForMac($curSession, $curmac, $customerID)) {
            my $macid = $macticket->{$MAC.$TSEP.$UNIQIDCOLUMNNAME};
            my $ticketid = $macticket->{$TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP.$UNIQIDCOLUMNNAME};
            Log("   - Lastticket ".$ticketid, $INFO);
            if ($lastticketid && ($lastticketid ne $ticketid)) {
               Log("Not copying mac ".$curmac." cause it is currently assigned to ".$ticketid." and not to lastticket ".$lastticketid, $WARNING);
               next;
            }
         }
         my $retmac = $dbmac->insertDataSet({
            table   => $MAC,
            user    => $mac,
            session => $curSession,
            columns => {
               $MAC.$TSEP."address"      => uc($curmac),
               $MAC.$TSEP.$TICKETS."_id" => $insertticket,
               $MAC.$TSEP."start"        => localtime2sql(time()),
               $MAC.$TSEP."deleted"      => 0,
            },
         });
         unless ($retmac &&
                ($retmac =~ m,^\d+$,)) {
            Log("Error assigning ticket=".$insertticket." mac=".$mac." curmac=".$curmac." error=".$retmac, $ERROR);
            return "Fehler beim Verknuepfen des Tickets mit ihrem Ger&auml;t! ".$melden." mit der Ticket-Nummer T.".$insertticket.",LT.".$lastticketid.($brumsid ? ",B.".$brumsid : "")."\n";
            #return $retmac;
         }
      }
      return $buchungsid;
   }
   Log("Error creating new ticket mac=".$mac." error=".$insertticket, $WARNING);
   return $insertticket;
}

my $READER_START = "start";
my $READER_NORMINAL = "norminal";
my $READER_INVALIDBILL = "invalidmoney";
my $READER_PAYMENT = "gotmoney";

sub doPing {
   my $self = shift;
   my $blocking = shift;
   Log($AUTOMATFNKT.": do ping",$DEBUG);
   return getSSL({
      %$readerconfig,
      data => HTTP::Request->new("GET" => "/?command=ping"),
      timeout => 2,
      callback => $blocking ? undef :
      sub {
         my $ret = shift;
         if(!$ret->{success} && $ret->{error}) {
            Log($AUTOMATFNKT.": ping to money reader unsuccessful: ".$ret->{error},$DEBUG);
            #$context->{error} .= "<br>Keine Verbindung zum Lesegerät<br>";
            $self->{moneyReader}->{lastPingFail}++;
            $self->doPing()
               if($self->{moneyReader}->{lastPingFail} < $MAX_READER_PING);
            # TODO:Lukas: evtl alle verbindungen kappen nach X Fehlschlägen?
         } else {
            Log($AUTOMATFNKT.": ping successful",$INFO);
            $self->{moneyReader}->{lastPingFail} = 0;
            $self->{moneyReader}->{lastAnswer} = time;
         }
      },
   },\{});
}

sub doMoneyMachine {
   my $self = shift;
   my $curSession = shift;
   my $context = shift;

   return
      unless $self->{config}->{billreaderip} &&
             $self->{config}->{billreaderport};

   my $moneybutton = '<form action="" method="post">'."\n".
      '<input type="hidden" name="job" value="boxtemplate">'."\n".
      '<input type="hidden" name="boxid" value="'.$context->{boxid}.'">'."\n".
      '<input name="ticketcopy" type="hidden" value='.($context->{activeticket}->{id}||"").'>'."\n".
      '<input type="hidden" name="module" value="moneymachine">'."\n".
      '<button class="btn btn-primary" name="submit"><img src="/login/money.png"><br><div style='."'font-size: 6vw;'".'><nobr>Click here to insert money</nobr><br><font size=1vw><br></font>Hier klicken um Geldschein einzuzahlen</div></button>'."\n".
      '</form>'."\n";

   if ($context->{module}) {
      return
         if $context->{module} ne "moneymachine";
   } else {
      $context->{selectoption} = $moneybutton.($context->{selectoption}||"");
      return;
   }
   $moneybutton .=
      '<script>'."\n".
      'var request;'."\n".
      'function doPoll() {'."\n".
      '   if ((typeof(request) != "undefined") && (typeof(request.abort) == "function")) {'."\n".
      '     request.isdying = true;'."\n".
      '     request.abort();'."\n".
      '   }'."\n".
      '   request = new XMLHttpRequest();'."\n".
      '   request.timeout = 6*1000; /* 3 seconds timeout *//* useless during sync configuration */'."\n".
      '   request.open("GET", "?job=boxtemplate&module=moneymachine&ajax=1&boxid='.$context->{boxid}.'&ticketcopy='.($context->{activeticket}->{id}||"").'");'."\n".
      '   request.onerror = function() {'."\n".
      '      let message = document.getElementById("message");'."\n".
      '      message.innerHTML= "<div style=\"font-size: 8vw;\">error</div>";'."\n".
      '   }'."\n".
      '   request.onload = function() {'."\n".
      '      if (this.isdying) return console.log("dying connection -> do nothing");'."\n".
      '      let message = document.getElementById("message");'."\n".
      '      try {'."\n".
      '         const resp = JSON.parse(this.responseText);'."\n".
      '         if (resp.message) {'."\n".
      '            message.innerHTML= resp.message;'."\n".
      '         } else {'."\n".
      '            message.innerHTML= "&nbsp;";'."\n".
      '         }'."\n".
      '         if (resp.poll)'."\n".
      '            setTimeout(function () { doPoll(); }, 2000);'."\n".
      '         if (resp.reload)'."\n".
      '            location.reload();'."\n".
      '      } catch (e) {'."\n".
      '         message.innerHTML= "<div style=\"font-size: 8vw;\">Vorgang abgebrochen!<br>Action aborted!</div>";'."\n".
      '      }'."\n".
      '   }'."\n".
      '   request.ontimeout = function() {'."\n".
      '      let message = document.getElementById("message");'."\n".
      '      //message.innerHTML = "<div style=\"font-size: 8vw;\">Aktualisieren...</div>";'."\n".
      '      location.reload();'."\n".
      '   };'."\n".
      '   request.send();'."\n".
      '}'."\n".
      'doPoll();'."\n".
      '</script>'."\n";
   my $message = "";
   $message .= "<b>".$context->{newticketdays}."</b> Tage für <b>".$context->{price}."</b> &euro;<br>".(($context->{anzdevices} && ($context->{anzdevices} > 1)) ? "<font size=+5>".$context->{anzdevices}." Ger&auml;te</font><br>" : "")."<br>";
   $self->{moneyReader} = {}
      if $context->{param}->{reset};
   unless(defined($self->{moneyReader}->{lastdate})) {
      # TODO:XXX:FIXME: Verbindern dass man NGE Buchungen macht mit Zahl bei Brums. Oder einfach komplett verhindern?
      my $buchungendbm = $self->getDBBackend($BUCHUNGEN);
      my $retbuchungen = undef;
      if (($retbuchungen = $buchungendbm->getDataSet({
         session => $curSession,
         table => $BUCHUNGEN,
         sortby => $BUCHUNGEN.$TSEP.$UNIQIDCOLUMNNAME."_",
         limit => 1,
         wherePre => [$BUCHUNGEN.$TSEP."brums".' REGEXP '."'".'^[0-9]+$'."'"],
         whereEx => {
            $BUCHUNGEN.$TSEP.$ZAHLUNGSART."_".$UNIQIDCOLUMNNAME => $BUCHUNG_NGE,
         },
      })) && defined($retbuchungen) &&
               (ref($retbuchungen) eq "ARRAY") &&
            scalar(@$retbuchungen) &&
               (ref($retbuchungen->[0]) eq "ARRAY") &&
           scalar(@{$retbuchungen->[0]})) {
         $self->{moneyReader}->{lastdate} = $retbuchungen->[0]->[0]->{$BUCHUNGEN.$TSEP."brums"};
         Log("lastdate in db is ".$self->{moneyReader}->{lastdate}, $INFO);
      } else {
         Log("No lastdate for NGE in DB found: ".Dumper($retbuchungen), $ERROR);
      }
      $self->{moneyReader}->{lastdate} = time()
         unless ($self->{moneyReader}->{lastdate} &&
                ($self->{moneyReader}->{lastdate} =~ m,^[0-9]+$,));
      Log($AUTOMATFNKT.": LOCALTIME=".$self->{moneyReader}->{lastdate}."=".time2sql($self->{moneyReader}->{lastdate}),$DEBUG);
      Log($AUTOMATFNKT.": TIME=".time()."=".time2sql(time()),$DEBUG);
   }
   my $curtime = time();
   unless (                  $self->{moneyReader}->{inuse} &&
      $self->{moneyReader}->{$self->{moneyReader}->{inuse}}->{lasttime}) {
      my $ret = $self->doPing(1);
      if(!$ret->{success} || $ret->{error}) {
         $message = "Money reader unavailable!";
         if ($context->{param}->{ajax}) {
            $context->{ajaxret}->{message} .= $message;
         } else {
            $context->{statusinfo} = '<div id="message">'.$message.'</div>'.$moneybutton;
         }
         return;
      }
      $self->{moneyReader}->{$self->{moneyReader}->{inuse}}->{lasttime} = $curtime
         if $self->{moneyReader}->{inuse};
   }
   my $freeforothers      = $self->{moneyReader}->{inuse} ? ($self->{moneyReader}->{$self->{moneyReader}->{inuse}}->{lasttime}  + $READER_BLOCK_TIME                     ) - $curtime : 0;
   my $freeforothersmax   = $self->{moneyReader}->{inuse} ? ($self->{moneyReader}->{$self->{moneyReader}->{inuse}}->{starttime} + $READER_BLOCK_TIME + $PERUSERMAXTIME   ) - $curtime : $MAXSTOPTIME + $PERUSERMAXTIME;
   my $freeforcurrent     = $self->{moneyReader}->{$context->{mac}}->{starttime} ? ($self->{moneyReader}->{$context->{mac}}->{starttime} + $PERUSERMAXTIME + $MAXSTOPTIME) - $curtime : 0;
   my $remaingcurrentuser = $self->{moneyReader}->{$context->{mac}}->{starttime} ? ($self->{moneyReader}->{$context->{mac}}->{starttime} + $PERUSERMAXTIME               ) - $curtime : 0;
   # $message .= '<font size=3>';
   # $message .= "FREEFOROTHERS TIME=".$freeforothers." MAX=".$freeforothersmax."<br>\n";
   # $message .= "FREEFORCURRENT TIME=".$freeforcurrent."<br>\n";
   # $message .= "REMAINING TIME=".$remaingcurrentuser."<br>\n";
   # $message .= '</font>';
   if ($freeforothers < 0) {
      delete $self->{moneyReader}->{$self->{moneyReader}->{inuse}}->{lasttime};
      delete $self->{moneyReader}->{inuse};
   }
   my $availablein = 0;
   if ($self->{moneyReader}->{inuse} &&
      ($self->{moneyReader}->{inuse} ne $context->{mac})) {
      Log("FreeForOthers ".$freeforothers." MAX=".$freeforothersmax, $INFO);
      $availablein = $freeforothersmax
         if !$availablein ||
            ($availablein < $freeforothersmax);
   # } else {
   #    # TODO:XXX:FIXME: Async machen und sowas hier immer durchlassen solange die MAC gleich ist...
   #    $self->{moneyReader}->{$context->{mac}}->{buchungscount} = $self->getLastBuchungID($curSession, $context->{boxcfg}->{$BOXES.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}, $context->{mac});
   #    #$context->{selectoption} = "getLastBuchungID ".$self->{moneyReader}->{$context->{mac}}->{buchungscount}."<br>\n".($context->{selectoption}||"");
   #    $self->{moneyReader}->{$context->{mac}}->{startbuchungscount} ||= $self->{moneyReader}->{$context->{mac}}->{buchungscount};
   #    if ($self->{moneyReader}->{$context->{mac}}->{buchungscount} &&
   #       ($self->{moneyReader}->{$context->{mac}}->{startbuchungscount} ne
   #        $self->{moneyReader}->{$context->{mac}}->{buchungscount})) {
   #       $self->{moneyReader}->{$context->{mac}}->{startbuchungscount} = $self->{moneyReader}->{$context->{mac}}->{buchungscount};
   #       $message .= "Ticket gebucht!<br>";
   #    }
   }
   if ($remaingcurrentuser <= 0) {
      if ($freeforcurrent < 0) {
         delete $self->{moneyReader}->{$context->{mac}};
      } else {
         Log("FreeForCurrent ".$freeforcurrent, $INFO);
         $availablein = $freeforcurrent
            if !$availablein ||
               ($availablein < $freeforcurrent);
      }
      $remaingcurrentuser = $PERUSERMAXTIME;
   }
   if ($availablein) {
      $message .= '<div style="font-size: 8vw;"><img src="/login/nomoney.png"><font color="red">DO NOT ENTER MONEY !</font><br><br></div><div style="font-size: 6vw;"><font color="red">You have to wait '.$availablein.' second'.($availablein == 1 ? "": "s").' until you are in line for payment and get green!<br><br>Noch '.$availablein.' Sekunde'.($availablein == 1 ? "": "n").' warten bis Sie an der Reihe sind und gr&uuml;n bekommen!<br><br></font></div>';
      $context->{ajaxret}->{poll}++
         if $context->{param}->{ajax};
   } else {
      unless (!$self->{moneyReader}->{$context->{mac}}->{started} &&
              $context->{param}->{ajax}) {
         $self->{moneyReader}->{$context->{mac}}->{lasttime} = $curtime;
         $self->{moneyReader}->{$context->{mac}}->{started}++;
         $self->{moneyReader}->{$context->{mac}}->{starttime} ||= $curtime;
         $self->{moneyReader}->{inuse} = $context->{mac};
         if ($self->{moneyReader}->{connectionBroken}) {
            $message .= "Es konnte keine Verbindung mit dem Automat aufgebaut werden. <br> Bitte wenden Sie sich an den Support wenn dies wiederhohlt auftritt.";
            Log($AUTOMATFNKT.": reader nicht erreichbar.",$INFO);
            delete $self->{moneyReader}->{connectionBroken};
         } else {
            my $data = {
               mac          => $context->{mac},
               ld           => $self->{moneyReader}->{lastdate},
               time         => time(),
               lastticket   => $context->{lastticket}||0,
               lastIsActive => $context->{activeticket}->{success} ? 1 : 0,
               command      => $READER_START,
               anzdevices   => $context->{anzdevices},
            };
            my $url = "/?".join("&", map { uri_escape($_)."=".uri_escape($data->{$_}) } keys %$data);
            Log($AUTOMATFNKT.": url: ".$url, $INFO);
            my $req = HTTP::Request->new("GET" => $url);
            my $result = {};
            my $sslConfig = {
               %$readerconfig,
               data => $req,
               callback => sub {
                  my $ret = shift;
                  if ($ret->{error}) {
                     if ($ret->{error} eq "Timeout") {
                        Log($AUTOMATFNKT." Timeout", $INFO);
                     } else {
                        Log($AUTOMATFNKT."Broken connecion to reader! ERROR=".$ret->{error}, $WARNING);
                        $self->{moneyReader}->{connectionBroken}++;
                     }
                     return undef;
                  } elsif(!$ret->{data}->content) {
                     Log($AUTOMATFNKT." No content, maybe already replaced connection MAC=".$context->{mac}, $WARNING);
                     return undef;
                  }
                  eval {
                     $ret = from_json($ret->{data}->content);
                  };
                  if ($@) {
                     if(!$ret->{data}) {
                        Log($AUTOMATFNKT.": empty json or worse answer from reader! ".$ret." ERROR= ".$@, $INFO);
                     } else {
                        Log($AUTOMATFNKT.": malformed json or worse answer from reader! ".$ret->{data}->content." ERROR= ".$@, $INFO);
                     }
                     $self->{moneyReader}->{connectionBroken}++;
                     return;
                  }
                  if ($ret->{state} eq $READER_NORMINAL) {
                     Log($AUTOMATFNKT.": state norminal: just before packetloop.",$DEBUG);
                     foreach my $packet (@{$ret->{queue}}) {
                        if ($self->{moneyReader}->{lastdate} > $packet->{time}) {
                           Log($AUTOMATFNKT.": Ignoring ".$packet->{time}." <= ".$self->{moneyReader}->{lastdate},$DEBUG);
                           next;
                        }
                        unless ($packet->{time}) {
                           Log($AUTOMATFNKT.": got packet without timestamp! (".time2sql(time).") ".Dumper($packet), $ERROR);
                           next;
                        }
                        my $mac = $packet->{query}->{mac};
                        unless ($mac) {
                           Log($AUTOMATFNKT.": got packet without mac! (".time2sql($packet->{time}).") ".Dumper($packet),$WARNING);
                           next;
                        }
                        if ($packet->{money} > 0) {
                           if ($self->{moneyReader}->{lastCreatedID} &&
                              ($self->{moneyReader}->{lastCreatedID} eq $packet->{time})) {
                              Log("BOOK: Habe doppelte Buchung fuer MAC ".$mac." fuer NGE mit Nummer ".$packet->{time}." verhindert!", $WARNING);
                           } else {
                              my $buchungsid = $self->createBuchungAndTicket($curSession, $context->{boxcfg}->{$BOXES.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}, $mac, $packet->{time}, $BUCHUNG_NGE, $packet->{money});
                              if($buchungsid =~ m,^[0-9]+$,) {
                                 Log("BOOK: Buchung ".$buchungsid."/".$packet->{time}." mit MAC=".$mac." erfolgreich gebucht!", $WARNING);
                                 # TODO:XXX:FIXME: Sollte man loggen irgendwo... Auch mit der Buchungsnummer...
                                 $self->{moneyReader}->{lastCreatedID} = $packet->{time};
                                 $self->getTicketContext($curSession, $context);
                                 $self->{moneyReader}->{lastdate} = $packet->{time}
                                    if($packet->{time} > $self->{moneyReader}->{lastdate});
                              } else {
                                 Log("BOOK: Booking NGE mit Nummer ".$packet->{time}." und MAC=".$mac." schlug fehl mit Fehler ".$buchungsid, $WARNING);
                                 # TODO:XXX:FIXME: Sollte man loggen irgendwo...
                              }
                           }
                        }
                        if ($packet->{query}->{mac} &&
                           ($packet->{query}->{mac} eq $context->{mac})) {
                           #$found++;
                           Log($AUTOMATFNKT.": MY MAC FOUND IN PAYMENT ".$packet->{query}->{mac},$DEBUG);
                        } else {
                           Log($AUTOMATFNKT.": MY MAC NOT FOUND IN PAYMENT ".$packet->{query}->{mac}."=!".$context->{mac},$DEBUG);
                        }
                     }
                  } elsif($ret->{state} eq $READER_INVALIDBILL) {
                     # TODO:XXX:FIXME: WARUM IST DAS NICHT WIE NORMALES INVALID BILL IM ARRAY!??!?!
                     my $error =  "Geldschein konnte nicht erkannt werden!";
                     Log($error, $INFO);
                     $message .= $error;
                  } else {
                     $message .= "Unbekannter Status ".$ret->{state};
                     $self->{moneyReader}->{connectionBroken}++;
                     print STDERR "XX".Dumper($ret);
                     # TODO:XXX:FIXME: NEED_MAINTANACE $READER_TILT
                     # TODO:XXX:FIXME: andere Fälle hier auch gescheid behandeln
                  }
               },
            };
            #$sslConfig->{timeout} = 0.1
            #   if $context->{param}->{ending};
            my $ret = getSSL($sslConfig, \$result);
            $message .= '<div style="font-size: 6vw;"><img src="/login/okmoney.png"><br><br><font color=green>Pay NOW within '.$remaingcurrentuser." seconds!<br><br>Jetzt innerhalb von ".$remaingcurrentuser." Sekunden bezahlen!<br><br></font></div>";
            $context->{ajaxret}->{poll}++
               if $context->{param}->{ajax};
            Log($AUTOMATFNKT.": doMoneyMachine - SSL - done", $INFO);
         }
      }
   }
   if ($context->{param}->{ajax}) {
      $context->{ajaxret}->{message} .= $message;
   } else {
      $context->{statusinfo} = '<div id="message">'.$message.'</div>'.$moneybutton;
   }
}

sub doBrums {
   my $self = shift;
   my $curSession = shift;
   my $context = shift;
   my $id = undef;
   my $curtime = time();
   my $num = undef;

   return
      if $self->{config}->{nobrums};

   if ($context->{module}) {
      return
         if $context->{module} ne "brums";
   } else {
      $context->{selectoption} = '
         <form action="" method="post">
         <input type="hidden" name="job" value="boxtemplate">
         <input type="hidden" name="boxid" value="'.$context->{boxid}.'">
         <input name="ticketcopy" type="hidden" value='.$context->{activeticket}->{id}.'>
         <input type="hidden" name="module" value="brums">
         <button class="btn btn-primary" name="submit"><img src="/login/brums.png" style="width:30%"><br><div style='."'font-size: 6vw;'".'>Click here to pay with envelope<br><font size=1vw><br></font>Hier klicken um mit Umschlag zu bezahlen</div></button>
         </form>'.$context->{selectoption};
      return;
   }
   if ($context->{bad}) {
      if ($context->{activeticket}->{success}) {
         if ($context->{tickethistory}->{$context->{activeticket}->{id}}->{mac}->{$TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP."start"}) {
            my $opentime = int((time()-sql2time($context->{tickethistory}->{$context->{activeticket}->{id}}->{mac}->{$TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP."start"})) / 60 / 60 / 24);
            #$context->{statusinfo} .= "<table><tr><td valign=top>Hinweis:</td><td>Die Zahlung f&uuml;r Ticket ".$context->{tickethistory}->{$context->{activeticket}->{id}}->{mac}->{$TICKETS.$TSEP.$UNIQIDCOLUMNNAME}." mit Umschlag ".join(",", keys %{$context->{bad}})." ist seit ".$opentime." Tagen offen.<br><br>Solang eine Zahlung offen ist, sind neue Buchung nicht m&ouml;glich.<br><br>Zahlungen werden erst bei der Safe-Leerung vermerkt, dies kann bis zu 14 Tage dauern.</td></tr></table><br>"
            $context->{statusinfo} .= "Umschlag <font color=red>".join(",", keys %{$context->{bad}})."</font> mit <font color=red>".$context->{price}." &euro;</font> einwerfen oder Sie werden gesperrt!<br><br>"
            #   if ($opentime > 7)
         }
      } else {
         $context->{error} .= "<center>Umschlag <b>".join(",", keys %{$context->{bad}})."</b> nicht angekommen!<br><br>".$melden."</center>";
      }
      return;
   }
   my $pass = $context->{param}->{"mypasswort"} || "";
   $pass =~ s,[^a-z0-9],,gi;
   if ($pass) {
      if ($num = $self->getVerifiedNum(uc($pass))) {
         unless (($context->{activeticket}->{id} eq $context->{lastticket}) &&
                        $context->{tickethistory}->{$context->{lastticket}}->{mac}->{$TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP.'brums'} &&
                       ($context->{tickethistory}->{$context->{lastticket}}->{mac}->{$TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP.'brums'} eq $num)) {
            #$context->{selectoption} .= $context->{tickethistory}->{$context->{lastticket}} ? "Ticket is ".$context->{lastticket}." ".scalar(keys %{$context->{tickethistory}->{$context->{lastticket}}->{macs}})." \n" : "Komplett neues Ticket.\n";
            # Nach benutzten BrUms suchen
            #$context->{statusinfo} .= "Searching for brums '".$num."'<br>\n";
            my $dbticket = $self->getDBBackend($TICKETS);
            my $retbuchungen = undef;
            if (!$context->{error} && ($retbuchungen = $dbticket->getDataSet({
               session   => $curSession,
               table     => $BUCHUNGEN,
               #nodeleted => 1,
               searchdef => {
                  $BUCHUNGEN.$TSEP."brums" => $num,
               },
            })) && (ref($retbuchungen) eq "ARRAY") &&
                   (ref($retbuchungen->[0]) eq "ARRAY") &&
               scalar(@{$retbuchungen->[0]})) {
               # TODO:XXX:FIXME: Weiteres Gerät ermöglichen wenn der Umschlag schon eingeworfen worden ist...
               Log("Bereits benutzter BrUms mac=".$context->{mac}." brums=".$num, $INFO);
               $context->{error} .= "Bereits benutzter BrUms!<br>Ticket Nummer ".
                  $retbuchungen->[0]->[0]->{$BUCHUNGEN.$TSEP.$TICKETS."_".$UNIQIDCOLUMNNAME}."/".
                  $retbuchungen->[0]->[0]->{$BUCHUNGEN.$TSEP."brums"}."\n";
            } elsif(!$context->{error}) {
               my $buchungsid = $self->createBuchungAndTicket($curSession, $context->{boxcfg}->{$BOXES.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}, $context->{mac}, $num, 2);
               if ($buchungsid =~ m,^[0-9]+$,) {
                  $context->{statusinfo} .= "Umschlag <font color=red>".$num."</font> innerhalb<br>von <font color=red>2 Tagen</font> mit <font color=red>".$context->{price}." &euro;</font> einwerfen!<br><br>";
                  # TODO:XXX:FIXME: Sollte man loggen irgendwo... Auch mit der Buchungsnummer...
                  $self->getTicketContext($curSession, $context);
               } else {
                  Log("Booking Brums mit Nummer ".$num." schlug fehl mit Fehler ".$buchungsid, $WARNING);
                  $context->{error} .= "Fehler beim Verbuchen des Tickets<br>";
                  # TODO:XXX:FIXME: Sollte man loggen irgendwo...
               }
            }
         }
      } else {
         # TODO:XXX:FIXME: Pass wieder irgendwie implementieren!
         $context->{error} .= "Falscher Code, oder falsches Ticket.";
         Log("Pass noch nicht wieder implementiert!", $ERROR);
      }
   }
   if (!$context->{error} &&
       !$context->{bad}) {
      $context->{selectoption} .= "<b>Bitte geben Sie Ihr Ticket ein.</b><br><br>";
      #my $uniqid = "barzahlung";
      # $context->{selectoption} .= "<a onClick='if (document.getElementById(".chr(34).$uniqid.chr(34).").style.display == ".chr(34).'block'.chr(34).") { ".
      #                                  "document.getElementById(".chr(34).$uniqid.chr(34).").style.display =".chr(34).'none'.chr(34)." } else {".
      #                                  "document.getElementById(".chr(34).$uniqid.chr(34).").style.display =".chr(34).'block'.chr(34)." }' ".
      #    "onMouseOver='"."document.getElementById(".chr(34).$uniqid.chr(34).").style.display =".chr(34).'block'.chr(34)."' ".
      #    "onMouseOut='"."document.getElementById(".chr(34).$uniqid.chr(34).").style.display =".chr(34).'none'.chr(34)."' ".
      #    "href='javascript: return false'>".
      $context->{selectoption} .= '<form action="" method="post">';
      $context->{selectoption} .= '<input type="hidden" name="job" value="boxtemplate">';
      $context->{selectoption} .= '<input type="hidden" name="module" value="brums">';
      $context->{selectoption} .= '<input type="hidden" name="boxid" value="'.$context->{boxid}.'">';
      $context->{selectoption} .= '<input name="ticketcopy" type="hidden" value='.$context->{activeticket}->{id}.'>'
         if $context->{activeticket}->{id};
      $context->{selectoption} .= '<input name="mypasswort" class="input" tabindex="0" size="18" placeholder="Ticket- oder Bruns-Code" type="text">';
      $context->{selectoption} .= '<input class="btn btn-primary" value="Absenden" type="submit">';
      $context->{selectoption} .= '</form>';
   } else {
      $context->{error} .= "<br><br>"
         if $context->{error};
      $context->{error} .= '<form action="" method="post"><input class="btn btn-primary" value="Aktualisieren" type="submit"></form>';
   }
}

sub doPayPal {
   my $self = shift;
   my $curSession = shift;
   my $context = shift;
   return
      if $self->{config}->{nopaypal};
   return
      if $context->{module};
   $context->{selectoption} .= '
      <button class="btn btn-primary"><br><div id="paypal-button"></div><br>Click on paypal logo to pay via paypal<br><br>Auf das paypal Logo klicken um per Paypal zu bezahlen</button><br><br>';
   $context->{selectoption} .= '<script src="https://www.paypalobjects.com/api/checkout.js"></script>
      <script>
         paypal.Button.render({
            env: "sandbox",
            payment: function(data, actions) {
               return actions.request.post("/cgi-bin/payment.pl", {
                  job: "prepare",
                  mac: "'.$context->{mac}.'"
               }).then(function(res) {
                  return res;
               });
            },
            onAuthorize: function(data, actions) {
               return actions.request.post("/cgi-bin/payment.pl", {
                  paymentID: data.paymentID,
                  payerID:   data.payerID,
                  job:       "execute",
                  mac:       "'.$context->{mac}.'"
               }).then(function(res) {
                  if ((res.substring(0, 4) == "http") ||
                     (res.substring(0, 1) == "/")) {
                     document.location.href = res;
                  } else {
                  document.getElementById("message").innerHTML = res;
                  }
               });
            }
      }, "#paypal-button");
      </script>

      <div id="message">
      </div>';
   #$context->{selectoption} .= '<br><table><tr><td valign=top><li></td><td valign=top><b>NEU:</b></li></td><td><b>Per Paypal bezahlen</b><br><br>Einfach auf folgenden Button drücken, und bargeldlos per EC, Konto oder Kreditkarte zahlen.<br><br><br></td></tr>';
}

sub doShowLastTicket {
   my $self = shift;
   my $curSession = shift;
   my $context = shift;
   return
      unless $context->{lastticket};
   my $curticket = $context->{tickethistory}->{$context->{lastticket}};
   my $end = 0;
   my $start = ($curticket->{"mac"}->{"tickets_id_tickets.start"} &&
               ($curticket->{"mac"}->{"tickets_id_tickets.start"} ne "0000-00-00 00:00:00") ?
                $curticket->{"mac"}->{"tickets_id_tickets.start"} :
                $curticket->{"mac"}->{"mac.start"});
   $end = sql2time($curticket->{"mac"}->{"tickets_id_tickets".$TSEP.$TIMESTAMP})
      if $curticket->{"mac"}->{"tickets_id_tickets".$TSEP.$TIMESTAMP} &&
        ($curticket->{"mac"}->{"tickets_id_tickets".$TSEP.$TIMESTAMP} ne "0000-00-00 00:00:00");
   # TODO:XXX:FIXME: $curticket->{$TICKETS} wird derzeit über getTicketHistory befüllt...... Gute Idee?
   $end = sql2time($start) + $curticket->{$TICKETS}->{$TICKETS.$TSEP."maxusedays"}*60*60*24
      if           $start && $curticket->{$TICKETS}->{$TICKETS.$TSEP."maxusedays"};
   #Log("Ticketstate: lastticket: ".Dumper($context->{tickethistory}->{$context->{lastticket}}),$DEBUG);
   #Log("Ticketstate: curticket: ".Dumper($curticket), $DEBUG);
   $context->{statusinfo} .= "<table id='tickettable' width=100% bgcolor=#".$ticketcolor->{$context->{activeticket}->{success} ? "valid" : "expired"}.">";
   $context->{statusinfo} .= "<tr><td colspan=2><b>".($context->{activeticket}->{success} ? "Aktuelles" : "Letztes")." Ticket:</b></td></tr>";
   #$context->{statusinfo} .= "<tr><td colspan=2><b> blocked reason:".$context->{activeticket}->{error}." </b></td></tr>"
   #   if($context->{activeticket}->{error}); #TODO:Lukas: shall not remain in final version - debug only
   $context->{statusinfo} .= "<tr><td>Nummer</td><td>".$curticket->{"mac"}->{"tickets_id_tickets.id"}."</td></tr>\n";
   $context->{statusinfo} .= "<tr><td>Typ</td><td>".(
      ($context->{anzdevices} < 2) ? "Standardticket" : "Familienticket")." (".$context->{price}." Euro)"."</td></tr>";
   $context->{statusinfo} .= "<tr><td valign=top>Gerät".($context->{anzdevices}>1?"e":"")."</td><td>";
   my $macs = [keys(%{$context->{tickethistory}->{$context->{lastticket}}->{macs}})];
   my $uniqid = $context->{lastticket};
   $context->{statusinfo} .= # "<a ".#"onClick='if (document.getElementById(".chr(34).$uniqid.chr(34).").style.display == ".chr(34).'block'.chr(34).") { ".
                  #                      #"document.getElementById(".chr(34).$uniqid.chr(34).").style.display =".chr(34).'none'.chr(34)." } else {".
                  #                      #"document.getElementById(".chr(34).$uniqid.chr(34).").style.display =".chr(34).'block'.chr(34)." }' ".
                  #       "onMouseOver='"."document.getElementById(".chr(34).$uniqid.chr(34).").style.display =".chr(34).'block'.chr(34)."' ".
                  #       "onMouseOut='". "document.getElementById(".chr(34).$uniqid.chr(34).").style.display =".chr(34).'none'.chr(34)."' ".
      #"href='javascript: return false'>".
      scalar(@$macs)." Ger&auml;te<br>"; # </a><br>\n";
   #$context->{statusinfo} .= '<span id="'.$uniqid.'" style="display: none;">';
   $context->{statusinfo} .= "<table border=0 bgcolor='#add8e6' width=100%><tr><td><div style='font-size: 2vw;'>".
      join("<br>\n", map { #"<tr><td>".
         my $curmac = $_;
         join(" ", map { "".
            ($_->{"enduser".$TSEP."verified"} ? "" : '<s>').$self->{config}->{id}.
            ($_->{"enduser".$TSEP.$UNIQIDCOLUMNNAME} ? "E.".
             $_->{"enduser".$TSEP.$UNIQIDCOLUMNNAME}." ".
             $_->{"enduser".$TSEP."name"}." ".
             $_->{"enduser".$TSEP."forename"}." ".
            ($_->{"enduser".$TSEP."hostname"} ? $_->{"enduser".$TSEP."hostname"}." " : "").
            ($_->{"enduser".$TSEP."vendor"}   ? $_->{"enduser".$TSEP."vendor"}." "   : "").
            ($_->{"enduser".$TSEP."agent"} ? "<br><font size=1>".
             $_->{"enduser".$TSEP."agent"}."</font>" : "") : "M.".$curmac).
            ($_->{"enduser".$TSEP."verified"} ? "" : '</s>')
         } grep {
            !$_->{"enduser".$TSEP.$DELETEDCOLUMNNAME}
         } @{$context->{tickethistory}->{$context->{lastticket}}->{macs}->{$curmac}})#."</td></tr>"
      } sort {
          !$context->{tickethistory}->{$context->{lastticket}}->{macs}->{$a}->[0]->{"enduser".$TSEP.$UNIQIDCOLUMNNAME} ? 1 :
          !$context->{tickethistory}->{$context->{lastticket}}->{macs}->{$b}->[0]->{"enduser".$TSEP.$UNIQIDCOLUMNNAME} ? -1 :
          ($context->{tickethistory}->{$context->{lastticket}}->{macs}->{$a}->[0]->{"enduser".$TSEP."name"} cmp
           $context->{tickethistory}->{$context->{lastticket}}->{macs}->{$b}->[0]->{"enduser".$TSEP."name"}) ||
          ($context->{tickethistory}->{$context->{lastticket}}->{macs}->{$a}->[0]->{"enduser".$TSEP."forename"} cmp
           $context->{tickethistory}->{$context->{lastticket}}->{macs}->{$b}->[0]->{"enduser".$TSEP."forename"})
      } keys %{$context->{tickethistory}->{$context->{lastticket}}->{macs}})."</td></tr></table>";
   #$context->{statusinfo} .= '</span>';
   $context->{statusinfo} .= '</div></td></tr>';
   # TODO:XXX:FIXME: $curticket->{$TICKETS} wird derzeit über getTicketHistory befüllt...... Gute Idee?
   $context->{statusinfo} .= "<tr><td>Dauer</td><td>".$curticket->{$TICKETS}->{$TICKETS.$TSEP."maxusedays"}." Tage</td></tr>\n"
      if $curticket->{$TICKETS}->{$TICKETS.$TSEP."maxusedays"};
   if ($start) { #} || $curticket->{"mac"}->{"tickets_id_tickets".$TSEP."used"}) {
         #if $start && $curticket->{$TICKETS}->{$TICKETS.$TSEP."maxusedays"};
         #sql2time($curticket->{"mac"}->{"tickets_id_tickets".$TSEP.$TIMESTAMP});
      $context->{statusinfo} .= "<tr><td>Von</td><td>".time2sql(sql2time($start))."</td></tr>\n";
      my $remainhours = (int(((($end || sql2time($curticket->{"mac"}->{"tickets_id_tickets.mydate"}))-sql2localtime(time2sql(time())))/(60*6))+1)/10);
      my $remainshow = ($remainhours > 24) ? "".(int($remainhours / 2.4)/10)." Tage" : $remainhours." Stunden";
      $remainshow =~ s/\./,/g;
      $context->{statusinfo} .= "<tr><td valign=top>Bis</td><td>".($end ? time2sql($end) : $curticket->{"mac"}->{"tickets_id_tickets.mydate"})."</b></td></tr>\n";
      $context->{statusinfo} .= "<tr><td>Verbleibend</td><td>".($remainhours < 48 ? "<b><font color=red>Noch " : "").$remainshow.($remainhours < 48 ? "</font>" : "")
         if ($remainhours > 0);
   }
   $context->{statusinfo} .= "</table><br>";
}

sub doSearchBadTickets {
   my $self = shift;
   my $curSession = shift;
   my $context = shift;
   my $dbmac = $self->getDBBackend($MAC);
   my $where = []; # $self->Where_Pre({ curSession => $curSession, table => $MAC });
   push(@$where, '(('.$TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP.'deleted = 0) or (isnull('.$TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP.'deleted)))');
   my $badticket = undef;
   my $macloop = [                       $context->{lastticket} &&
      exists($context->{tickethistory}->{$context->{lastticket}}) &&
             $context->{tickethistory}->{$context->{lastticket}}->{macs} &&
     (keys %{$context->{tickethistory}->{$context->{lastticket}}->{macs}}) ?
      keys %{$context->{tickethistory}->{$context->{lastticket}}->{macs}} : $context->{mac}];
   my $bad = {};
   foreach my $curmac (@$macloop) {
      if (($badticket = $dbmac->getDataSet({
         session   => $curSession,
         table     => $MAC,
         wherePre  => $where,
         searchdef => {
            $MAC.$TSEP."address" => uc($curmac),
         },
      })) && (ref($badticket) eq "ARRAY") &&
             (ref($badticket->[0]) eq "ARRAY") &&
         scalar(@{$badticket->[0]})) {
         foreach my $curticket (@{$badticket->[0]}) {
            my $ticketid = $curticket->{$TICKETS."_".$UNIQIDCOLUMNNAME."_".$TICKETS.$TSEP.$UNIQIDCOLUMNNAME};
            if ($ticketid) {
               my $where2 = [];
               push(@$where2,  '('.$BUCHUNGEN.$TSEP.'brums != "")');
               push(@$where2,  '('.$BUCHUNGEN.$TSEP.$TICKETS."_".$UNIQIDCOLUMNNAME." = ".$ticketid.")");
               push(@$where2, '(('.$BUCHUNGEN.$TSEP.'brumsok = 0) or (isnull('.$BUCHUNGEN.$TSEP.'brumsok)))');
               my $retbuchungen = undef;
               if (($retbuchungen = $dbmac->getDataSet({
                  session   => $curSession,
                  table     => $BUCHUNGEN,
                  wherePre  => $where2,
               })) && (ref($retbuchungen) eq "ARRAY") &&
                      (ref($retbuchungen->[0]) eq "ARRAY") &&
                  scalar(@{$retbuchungen->[0]})) {
                  foreach my $curbuchung (@{$retbuchungen->[0]}) {
                     my $buchungsid = $curbuchung->{$BUCHUNGEN.$TSEP.$UNIQIDCOLUMNNAME};
                     my $brumsid = $curbuchung->{$BUCHUNGEN.$TSEP."brums"};
                     Log("Unpayed ticket found mac=".$curmac." ticketid=".$ticketid." buchungid=".$buchungsid." BRUMS=".$brumsid, $INFO);
                     push(@{$bad->{$brumsid}}, [$ticketid, $buchungsid]);
                  }
               }
            } else {
               Log("Cannot check ticket cause no ticketid on ticket entry in mac cache! mac=".$curmac, $WARNING);
            }
         }
      }
   }
   $context->{bad} = $bad
      if scalar(keys(%$bad));
}

sub getTicketContext {
   my $self = shift;
   my $curSession = shift;
   my $context = shift;
   $context->{activeticket} = $self->surfrequestForBox($curSession, $context->{mac}, $context->{boxcfg}, 1);
   $context->{tickethistory} = $self->getTicketHistory($curSession, $context->{mac}) || {};
  ($context->{lastticket}) = $context->{ticketcopy} || $context->{activeticket}->{success} ? (
                                                       $context->{activeticket}->{$UNIQIDCOLUMNNAME}) : (sort {
      $b <=> $a
   } keys %{$context->{tickethistory}});
   $context->{anzdevices} = $context->{lastticket} ? scalar(keys %{$context->{tickethistory}->{$context->{lastticket}}->{macs}}) : 1;
   my $rabatt = !$context->{lastticket} ? 0 : ($context->{tickethistory}->{$context->{lastticket}}) ?
                                               $context->{tickethistory}->{$context->{lastticket}}->{mac}->{"tickets_id_tickets.rabatt"} : 0;
   $context->{price} = getPricesForCount($context->{anzdevices}, $rabatt);
}

sub doTemplate {
   my $self = shift;
   my $curSession = shift;
   my $context = shift;
   my $client = shift;
   if ($context->{ajaxret}) {
      $context->{template} = ref($context->{ajaxret}) ? to_json($context->{ajaxret}) : $context->{ajaxret};
   } elsif ($context->{error} ||
            $context->{done}) {
      $self->doShowLastTicket($curSession, $context);
      $context->{template} ||= $self->{gui}->replacer($self->getBoxTemplate("start", $curSession, $context->{boxid}), {
         'ticketstreet' => $self->{config}->{ticketstreet} || "Unbekannt",
         'ticketplz'    => $self->{config}->{ticketplz} || "",
         'ticketcity'   => $self->{config}->{ticketcity} || "",
         'boxid'        => $context->{boxid},
         'agent'        => $context->{agent},
         'mac'          => $context->{mac},
         'selectoption' => $context->{selectoption},
         'altoption'    => '',
         'statusinfo'   => $context->{statusinfo} || "",
         'errortext'    => $context->{error} ? "<font color=red>".$context->{error}."</font>" : "",
         'myticket'     => "",
         'info'         => $context->{info} || "",
         'id'           => $self->{config}->{id},
         'userid'       => $self->{config}->{id}.($context->{enduserid} ? "E.".$context->{enduserid} : "M.".$context->{mac}),
         'qrcode'       => $self->{config}->{qrcodebase}.$context->{mac},
      });
   }
   $client->send("BOXTEMPLATE BEGIN\n".$context->{template}."\nBOXTEMPLATE END\n")
      if $context->{template};
   return $context->{template} ? 1 : 0;
}

sub getPaymentModuleList {
   my $self = shift;
   my $modulelist = [];
   push(@$modulelist, "paypal")
      unless $self->{config}->{nopaypal};
   push(@$modulelist, "brums")
      unless $self->{config}->{nobrums};
   push(@$modulelist, "moneymachine")
      if $self->{config}->{billreaderip} &&
         $self->{config}->{billreaderport};
   return $modulelist;
}

sub LineHandler {
   my $self = shift;
   $_ = shift;
   my $client = shift;
   my $connection = $client->getTrackingHash();
   my $onConnect = shift;

   my $curSession = $self->getSession($connection->{sessionID}) || {};
   no warnings 'uninitialized';
   if (/^LOGDATA\s+(.*)$/) {
      my $logline = [split(/;/, $1, 13)];
      push(@$logline, ("", "")) if (scalar(@$logline) == 11);
      if (!($connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME})) {
         $client->send("LOGDATA FAILED: No access!\n");
      } elsif (scalar(@$logline) == 13) {
         my $id = shift(@$logline);
         #print "ID: ".$id ."\n";
         my $month = ((localtime(time()))[4]+1);
         $month = "0".$month unless(length($month)>1);
         my $filename = $connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}."_".$month.".".((localtime(time()))[5]+1900).".log";
         if ($self->{loghandles}->{$connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}}->{filename} ne $filename) {
            close($self->{loghandles}->{$connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}}->{fd}) if
                  $self->{loghandles}->{$connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}}->{fd};
            if (open($self->{loghandles}->{$connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}}->{fd}, ">>", $self->{config}->{logpath}."/".$filename)) {
               $self->{loghandles}->{$connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}}->{filename} = $filename;
            } else {
               Log("LOGDATA COULD NOT OPEN NEW LOGFILE ".$filename." !", $ERROR);
               return $client->send("LOGDATA FAILED\n");
            }
         }
         my $logline = {
            $BOXES."_".$UNIQIDCOLUMNNAME   => $connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME},
            $TICKETS."_".$UNIQIDCOLUMNNAME => $logline->[0],
            "connectionid"                 => $logline->[1],
            $TIMESTAMP                     => ADBGUI::Tools::MakeTime($logline->[2]),
            "logged"                       => ADBGUI::Tools::MakeTime(time()),
            "client_host"                  => $logline->[3],
            "client_port"                  => $logline->[4],
            "client_MAC"                   => $logline->[5],
            "remote_host"                  => $logline->[6],
            "remote_port"                  => $logline->[7],
            "bytesout"                     => $logline->[8],
            "bytesin"                      => $logline->[9],
            "url"                          => $logline->[10],
            "agent"                        => $logline->[11],
         };
         my $sendstr = join(", ", map { '"'.$logline->{$_}.'"' } keys %$logline)."\n";
         if (syswrite($self->{loghandles}->{$connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}}->{fd}, $sendstr) == length($sendstr)) {
            $client->send("OK ".$id."\n");
         } else {
            Log("LOGDATA: Failed to write to file: ".$!, $ERROR);
            return $client->send("LOGDATA FAILED\n");
         }
      } else {
         $client->send("LOGDATA FAILED: Wrong format\n");
         Log("LOGDATA Line with unknown column number of params: ".scalar(@$logline), $ERROR);
      }
      return 1;
   } elsif (/^BOXAUTH\s+(\S+)\s+(\S+)$/) {
      my $id = $1;
      my $pass = $2;
      my $ret;
      my $db = $self->getDBBackend($BOXES);
      unless (defined($ret = $db->getDataSet({
         table     => $BOXES,
         session   => $curSession,
         searchdef => {
            $BOXES.$TSEP.$NAMECOLUMNNAME => $id,
            $BOXES.$TSEP.$PASS           => $pass,
         },
      })) && (ref($ret) eq "ARRAY")) {
         Log("DBManager: onNewLineServer: BOXAUTH ".$BOXES." FAILED SQL Query failed: ".$ret, $WARNING);
         return $client->send("BOXAUTH ".$id." FAILED\n");
      }
      unless ((ref($ret) eq "ARRAY") &&
              ($ret->[1] == 1) &&
          (ref($ret->[0]) eq "ARRAY") &&
          (ref($ret->[0]->[0]) eq "HASH") &&
       (exists($ret->[0]->[0]->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME})) &&
              ($ret->[0]->[0]->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}) &&
       (exists($ret->[0]->[0]->{$BOXES.$TSEP.$NAMECOLUMNNAME})) &&
              ($ret->[0]->[0]->{$BOXES.$TSEP.$NAMECOLUMNNAME}) &&
              ($ret->[0]->[0]->{$BOXES.$TSEP.$NAMECOLUMNNAME} eq $id) &&
       (exists($ret->[0]->[0]->{$BOXES.$TSEP.$PASS})) &&
              ($ret->[0]->[0]->{$BOXES.$TSEP.$PASS}) &&
              ($ret->[0]->[0]->{$BOXES.$TSEP.$PASS} eq $pass)) {
         Log("DBManager: onNewLineServer: BOXAUTH ".$BOXES.": No matching entry.", $INFO);
         return $client->send("BOXAUTH ".$id." FAILED\n");
      }
      $connection->{boxsession} = $ret->[0]->[0];
      $client->send("BOXAUTH ".$ret->[0]->[0]->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}." OK\n");
   } elsif (/^JSON\sTICKETBOOKED\s+(.+)$/) {
      my $json = undef;
      eval {
         $json = from_json($1, { utf8  => 1 });
      };
      my $boxcfg = $self->getBoxCfgForID($curSession);
      my $context = {};
      $context->{activeticket} = $self->surfrequestForBox($curSession, $json->{mac}, $boxcfg, 1);
      $context->{tickethistory} = $self->getTicketHistory($curSession, $json->{mac}) || {};
      $context->{lastticket} = scalar(sort {
         $b <=> $a
      } keys %{$context->{tickethistory}});
      my $macloop = [$context->{tickethistory}->{$context->{lastticket}}->{macs} &&
             (keys %{$context->{tickethistory}->{$context->{lastticket}}->{macs}}) ?
              keys %{$context->{tickethistory}->{$context->{lastticket}}->{macs}} : $json->{mac}];
      my $error = '';
      my $price = getPricesForCount(scalar(@$macloop), $context->{lastticket} ? $context->{tickethistory}->{$context->{lastticket}}->{$TICKETS.$TSEP."rabatt"} : 0);
      my $buchungsid = $self->createBuchungAndTicket($curSession, $boxcfg->{$BOXES.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}, $context->{mac}, $json->{transactionid}, 1, $price);
      if ($buchungsid =~ m,^[0-9]+$,) {
         Log($price." EUR wurden angenommen und unter der ID ".$buchungsid." pre als PayPal verbucht.", $INFO);
         # TODO:XXX:FIXME: Sollte man loggen irgendwo... Auch mit der Buchungsnummer...
         $self->getTicketContext($curSession, $context);
      } else {
         Log("PayPal Buchung mit Nummer ".$json->{transactionid}." schlug fehl mit Fehler ".$buchungsid, $WARNING);
         $error .= "Fehler beim Verbuchen des Tickets<br>";
         # TODO:XXX:FIXME: Sollte man loggen irgendwo...
      }
      return $client->send(to_json($error ? {
         result => "failed",
         error => $error,
      } : {
         result => "ok",
      })."\n");
   } elsif (/^JSON\sPRICEFORMAC\s+(.+)$/) {
      my $json = undef;
      eval {
         $json = from_json($1, { utf8  => 1 });
      };
      unless(!$@ && $json && $json->{mac}) {
         Log("PRICEFORMAC with without mac URI=".$json->{query}, $WARNING);
         return $client->send(to_json({
            result => "failed",
            error => "INVALID",
         })."\n");
      }
      my $boxcfg = $self->getBoxCfgForID($curSession);
      my $activeticket = $self->surfrequestForBox($curSession, $json->{mac}, $boxcfg, 1, 1);
      my $tickethistory = $self->getTicketHistory($curSession, $json->{mac}) || {};
      return $client->send(to_json({
         error => "ALREADYACTIVE",
      })."\n") if ($activeticket->{success});
      unless ($activeticket->{"noenduserverify"}) {
         my $db = $self->getDBBackend("enduser");
         my $wherepre = $self->Where_Pre($curSession, "enduser");
         push(@$wherepre, "enduser".$TSEP."address = '".uc($json->{mac})."'");
         my $enduserret = $db->getDataSet({
            table     => "enduser",
            session   => $curSession,
            wherePre  => $wherepre,
         });
         return $client->send(to_json({
            result => "failed",
            error => "UNREGISTRED",
         })."\n") if (!$activeticket->{"noenduserverify"} &&
                 !((ref($enduserret) eq "ARRAY") &&
                scalar(@$enduserret) &&
                   (ref($enduserret->[0]) eq "ARRAY") &&
              (scalar(@{$enduserret->[0]}) == 1)));
      }
      my ($lastticket) = (sort {
         $b <=> $a
      } keys %$tickethistory);
      my $anzdevices = $lastticket ? scalar(keys %{$tickethistory->{$lastticket}->{macs}}) : 0;
      my $rabatt = ($tickethistory->{$lastticket}) ? $tickethistory->{$lastticket}->{mac}->{"tickets_id_tickets.rabatt"} : 0;
      my $price = getPricesForCount($anzdevices, $rabatt);
       $client->send(to_json({
          result => "ok",
          price  => $price,
       })."\n");
   } elsif (/^BOXTEMPLATE\s+(.+)$/) {
      my $context = {};
      return Log("BOXTEMPLATE without json", $ERROR)
         unless (my $json = from_json($1, { utf8  => 1 }));
      $context->{param} = $json->{param};
      $context->{agent} = $json->{agent};
      if (length($json->{full}) &&
         (length($json->{full}) > length($json->{path}))) {
         # TODO:XXX:FIXME: MAC absichern
         $context->{mac} = substr($json->{full}, length($json->{path})+1, length($json->{full})-length($json->{path}));
         $self->{registrations}->[0]->{active}->{uc($context->{mac})} = [time()];
         #  if $json->{live};
      } elsif(!($context->{mac} = $json->{mac})) {
         Log("BOXTEMPLATE with without mac ip=".($json->{ip}||"-")." URI=".$json->{query}, $WARNING);
         return $client->send("BOXTEMPLATE FAILED\n");
      }
      $context->{newticketdays} =
         $self->{config}->{newmaxusedays} ||
         $self->{config}->{overwritecopymaxusedays} ||
         $self->{config}->{copymaxusedays} || 28;
      $context->{ticketcopy} = $context->{param}->{"ticketcopy"};
      $context->{ticketcopy} =~ s,\D,,gi;
      $context->{boxid} = $self->getBoxId($curSession, $json->{boxid}) ||
                          $self->getBoxId($curSession, $self->{config}->{boxid}) ||
                          $self->getBoxId($curSession);
      $context->{boxcfg} = $self->getBoxCfgForID($curSession, $context->{boxid});
      unless ($context->{boxid} && $context->{boxcfg}) {
         Log("BOXTEMPLATE failed to determine boxid", $WARNING);
         return $client->send("BOXTEMPLATE FAILED\n");
      }
      my $macsecond = uc(substr($context->{mac}, 1, 1));
      $context->{iphoneprivate} = 0;
      Log("NO AGENT IN PRIVATE WLAN TEST", $WARNING)
         unless $json->{agent};
      $context->{iphoneprivate}++
         if ((!$context->{agent} ||
              ($context->{agent} =~ /iPhone\ OS/)) && (
           (uc($macsecond) eq "2") ||
           (uc($macsecond) eq "6") ||
           (uc($macsecond) eq "A") ||
           (uc($macsecond) eq "E")));
      if(($context->{param}->{site} eq "filter") ||
         ($context->{param}->{site} eq "notickserv")) {
         my $template = $self->getBoxTemplate($context->{param}->{site}, $curSession, $context->{boxid}, {
            boxid => $context->{boxid},
            agent => $context->{agent},
         });
         return $client->send($template ? "BOXTEMPLATE BEGIN\n".$template."\nBOXTEMPLATE END\n" : "BOXTEMPLATE FAILED\n");
      }
      $self->getTicketContext($curSession, $context);
      $self->doEnduser($curSession, $context);
      return
         if $self->doTemplate($curSession, $context, $client);
      $self->doSearchBadTickets($curSession, $context);  # Nach offenen Zahlungen suchen
      $context->{module} = $context->{param}->{module};
      unless ($context->{module}) {
         my $modulelist = $self->getPaymentModuleList();
         $context->{module} = $modulelist->[0]
            if (scalar(@$modulelist) == 1);
         $self->{done}++
            unless $context->{module};
      }
      return
         if $self->doTemplate($curSession, $context, $client);
      # TODO:XXX:FIXME: Wenn createBuchungAndTicket noch nicht abgelaufene Tickets unterstuetzt, kann das unless hier weg.
      if ($context->{activeticket}->{success}) {
         if ($context->{param}->{ajax}) {
            $context->{ajaxret}->{message} = "Reloading...";
            $context->{ajaxret}->{reload}++;
         }
      } else {
         $self->doMoneyMachine($curSession, $context);
         return
            if $self->doTemplate($curSession, $context, $client);
         $self->doBrums($curSession, $context);
         return
            if $self->doTemplate($curSession, $context, $client);
         return
            if $self->doTemplate($curSession, $context, $client);
         $self->doPayPal($curSession, $context);
         return
            if $self->doTemplate($curSession, $context, $client);
      }
       $context->{done}++;
      return
         if $self->doTemplate($curSession, $context, $client);
      $client->send("BOXTEMPLATE FAILED\n");
   } elsif (/^REGISTERBOX\s+(\S+)$/) {
      my $sshkey = $1;
      if ((!$connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}) || (!$connection->{boxsession}->{$BOXES.$TSEP.$NAMECOLUMNNAME})) {
         return $client->send("REGISTERBOX FAILED\n");
      }
      my $id = $connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME};
      my $db = $self->getDBBackend($BOXES);
      my $retupdatebox = $db->updateDataSet({
         table   => $BOXES,
         session => $curSession,
         id      => $id,
         user    => $connection->{boxsession}->{$BOXES.$TSEP.$NAMECOLUMNNAME},
         columns => {
            $BOXES.$TSEP."sshkey" => decode_base64($sshkey),
         },
      });
      Log("DBManager: onNewLineServer: REGISTERBOX: OKAY: Boxid:".$id." SSHKey:".$sshkey, $INFO);
      if (defined($retupdatebox)) {
         if ($self->{config}->{sshkeyspath} &&
             $self->{config}->{sshkeyssleepcmd} && (open(SSHKEYS, ">:encoding(iso-8859-7)",
             $self->{config}->{sshkeyspath}))) {
            if (defined(my $ret = $db->getDataSet({
               session => $curSession,
               table   => $BOXES,
            }))) {
               foreach my $row (@{$ret->[0]}) {
                  print SSHKEYS 'command="'.$self->{config}->{sshkeyssleepcmd}.'" '.join(" ", map {
                     $_ = $row->{$_};
                     s,[^a-zA-Z0-9\ \=\+\/\-],,g;
                     $_
                  } ($BOXES.$TSEP."sshkey", $BOXES.$TSEP.$NAMECOLUMNNAME))."\n"
                     if $row->{$BOXES.$TSEP."sshkey"};
               }
               close(SSHKEYS);
               return $client->send("REGISTERBOX ".$connection->{boxsession}->{$BOXES.$TSEP."sshtunnelport"}." OK\n");
            } else {
               Log("Could not update ssh keys: getDataSet failed!", $WARNING);
            }
         } else {
            Log("Could not update ssh keys(File:".$self->{config}->{sshkeyspath}."): ".$1, $WARNING);
         }
         close(SSHKEYS);
      } else {
         Log("DBManager: onNewLineServer: REGISTERBOX: UPDATE FAILED!", $ERROR);
      }
      $client->send("REGISTERBOX FAILED\n");
   } elsif (/^REGISTERADDMAC\s+([0-9a-fA-F]+)$/) {
      my $mac = $1;
      $self->{registrations}->[1]->{active}->{uc($mac)} = [time()];
      return $client->send("REGISTERADDMAC OK\n");
   } elsif (/^JSON\s+SEEN(DHCP)?\s+(.+?)(\s+(\S+))?$/) {
      my $mac = undef;
      my $data = $2;
      my $json = undef;
      if ($1) {
         $data .= $3;
         eval {
            $json = from_json($data, { utf8  => 1 });
         };
         if ($@ || !$json) {
            return $client->send(JSON->new->allow_nonref->encode({
               result => "failed",
               cause  => "Unparseable json",
            })."\n");
         }
         $mac = $json->{mac};
      } else {
         $mac = uc($data);
      }
      return $client->send(JSON->new->allow_nonref->encode({
         result => "failed",
         cause  => "No box session",
      })."\n") unless ($connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME} &&
                       $connection->{boxsession}->{$BOXES.$TSEP.$NAMECOLUMNNAME});
      my $result = $self->surfrequestForBox($curSession, $mac, $connection->{boxsession});
      my $columns = undef;
      $columns->{"enduser".$TSEP."vendor"} = $json->{vendor}
         if $json->{vendor};
      $columns->{"enduser".$TSEP."hostname"} = $json->{hostname}
         if $json->{hostname};
      $columns->{"enduser".$TSEP."lastseen"} = ADBGUI::Tools::MakeTime(time());
      if ($columns) {
         my $dbmac = $self->getDBBackend($MAC);
         my $macticket = undef;
         if (((defined($macticket = $dbmac->getDataSet({
            session   => $curSession,
            table     => "enduser",
            searchdef => {
               "enduser".$TSEP."address" => uc($mac),
            },
         })))) && (ref($macticket) eq "ARRAY") &&
                  (ref($macticket->[0]) eq "ARRAY") &&
              scalar(@{$macticket->[0]})) {
             if (my $macid = $macticket->[0]->[0]->{"enduser".$TSEP.$UNIQIDCOLUMNNAME}) {
                my $dbticket = $self->getDBBackend($TICKETS);
                my $retmacnewupdate = $dbmac->updateDataSet({
                   table   => "enduser",
                   session => $curSession,
                   id      => $macid,
                   user    => $connection->{boxsession}->{$BOXES.$TSEP.$NAMECOLUMNNAME},
                   columns => $columns,
                });
                Log("DBManager: onNewLineServer: SURFREQUEST: vendor/hostname update failed: ".$retmacnewupdate, $ERROR)
                   unless (defined($retmacnewupdate) &&
                                  ($retmacnewupdate =~ m,^\d+$,));
            } else {
               Log("DBManager: onNewLineServer: SURFREQUEST: vendor/hostname update failed: Error fetching MAC entry for ".uc($mac)."!", $WARNING);
            }
         } else {
            Log("DBManager: onNewLineServer: SURFREQUEST: vendor/hostname: No entry in DB for ".uc($mac), $INFO);
         }
      }
      return $client->{ticketticker} ? undef : $client->send(JSON->new->allow_nonref->encode({
         data   => $result,
         result => "seen",
      })."\n");
   } elsif (/^JSON\s+(NO)?TICKETTICKER$/) {
      my $disable = $1 ? 1 : 0;
      return $client->send(JSON->new->allow_nonref->encode({
         result => "failed",
      })."\n") unless ($connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME} &&
                       $connection->{boxsession}->{$BOXES.$TSEP.$NAMECOLUMNNAME});
      if ($disable) {
         delete $client->{ticketticker};
      } else {
         # TODO:XXX: FIXME: Nicht customer id sondern box id?
         $client->{ticketticker} = $connection->{boxsession}->{$BOXES.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME};
      }
      return $client->send(JSON->new->allow_nonref->encode({
         result => "ok",
      })."\n");
   } elsif (/^JSON\s+TICKETS$/) {
      my $return = undef;
      return $client->send(JSON->new->allow_nonref->encode({
         result => "failed",
      })."\n") unless ($connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME} &&
                       $connection->{boxsession}->{$BOXES.$TSEP.$NAMECOLUMNNAME});
      $client->send(JSON->new->allow_nonref->encode({
         data   => $self->getTicketsForBoxCfg($curSession, $connection->{boxsession}),
         result => "ok",
      })."\n");
   } elsif (/^JSON\s+REGISTRATIONS$/) {
      if (defined(my $err = $self->checkRights($curSession, $ADMIN))) {
         $client->send(JSON->new->allow_nonref->encode({
            result => "failed",
         })."\n");
         return Log("ticketserver::DBManager: onNewLineServer: JSON REGISTRATIONS: ACCESS DENIED: ".$err->[0], $err->[1]);
      }
      my $foundregs = [];
      foreach my $registration (@{$self->{registrations}}) {
         next
            unless (    $registration->{active} &&
                   (ref($registration->{active}) eq "HASH"));
         #print "DUMPER:".Dumper($registration)."\n";
         foreach my $curmac (sort {
            $registration->{active}->{$a}->[0] <=>
            $registration->{active}->{$b}->[0]
         } grep {
            ($registration->{active}->{$_}->[0] > (time()-(60*10))) ? 1 : 0
         } keys %{$registration->{active}}) {
            push(@$foundregs, [$curmac, $registration->{active}->{$curmac}->[0], $registration->{myconfig}->{apname}]);
         }
      }
      my $lastmac = undef;
      foreach my $macdef (sort {
         $a->[0] cmp
         $b->[0]
      } @$foundregs) {
         next
            if ($lastmac &&
               ($lastmac eq $macdef->[0]));
         $lastmac = $macdef->[0];
         my $pretickets = $self->getTicketHistory($curSession, $macdef->[0]);
         my $dbenduser = $self->getDBBackend("enduser");
         my $retenduser = $dbenduser->getDataSet({
            table     => "enduser",
            session   => $curSession,
            searchdef => {
               "enduser".$TSEP."address" => uc($macdef->[0]),
            },
         });
         my $userdata = undef;
         my $useable = 0;
         if ((ref($retenduser) eq "ARRAY") && (ref($retenduser->[0]) eq "ARRAY")) {
            $userdata = $retenduser->[0]->[0];
            $useable  = 1;
            if ($retenduser->[1] > 1) {
               $useable  = 0;
            }
         }

         my $dbmac = $self->getDBBackend("mac");
         my $retmac = $dbmac->getDataSet({
            table     => "mac",
            session   => $curSession,
            searchdef => {
               "mac".$TSEP."address" => uc($macdef->[0]),
            },
         });
         my $tickets = [];
         if ((ref($retmac) eq "ARRAY") && (ref($retmac->[0]) eq "ARRAY")) {
            if ($retmac->[1] >= 1) {
               $tickets = [map { $_->{"mac.tickets_id"} } @{$retmac->[0]}];
            }
         }

         my $vendor = "Unknown";
         if ($self->{config}->{ouicsvdb}) {
            if (my $csv = Text::CSV->new ( { binary => 1 } )) {
               if (open my $fh, "<:encoding(utf8)", $self->{config}->{ouicsvdb}) {
                  while ( my $row = $csv->getline( $fh ) ) {
                     if (uc($row->[1]) eq substr(uc($macdef->[0]), 0, 6)) {
                        $vendor = $row->[2];
                        last;
                     }
                  }
               } else {
                  Log("Cannot open oui database: ".$!, $ERROR);
               }
            } else {
               Log("Cannot initialize Text::CSV:".$!, $ERROR);
            }
         }
         push(@$macdef, $useable, $vendor, $userdata, $tickets, $pretickets);
      }
      $client->send(JSON->new->allow_nonref->encode({
         data   => $foundregs,
         result => "ok",
      })."\n");
   } elsif (/^JSON\s+GETUSERDATAFORTICKETS\s+(\S+)$/) {
      if (defined(my $err = $self->checkRights($curSession, $ADMIN))) {
         $client->send(JSON->new->allow_nonref->encode({
            result => "failed",
         })."\n");
         return Log("ticketserver::DBManager: JSON GETUSERDATAFORTICKETS: ACCESS DENIED: ".$err->[0], $err->[1]);
      }
      my $json = $1;
      my $return = {};
      eval {
         $json = from_json($1, { utf8  => 1 });
      };
      return $client->send(JSON->new->allow_nonref->encode({
         result => "failed",
      })."\n") if ($@);
      my $curSession = {};
      foreach my $ticketobj (@$json) {
         my $ticketid = $ticketobj->[0];
         my $brumsid = $ticketobj->[1];
         next
            unless $ticketid =~ m,^\d+$, &&
                   $brumsid  =~ m,^\d+$,;
         Log("Search userdata for ticket ".$ticketid, $DEBUG);
         my $where = $self->Where_Pre($curSession, $MAC);
         my $ret = undef;
         push(@$where,  "(".$MAC.$TSEP.$TICKETS."_".$UNIQIDCOLUMNNAME." = ".$ticketid.")");
         my $db = $self->getDBBackend($MAC);
         if (defined($ret = $db->getDataSet({
            table    => $MAC,
            session  => $curSession,
            wherePre => $where,
            orderby  => $MAC.$TSEP.$UNIQIDCOLUMNNAME,
         })) && (ref($ret) eq "ARRAY") && scalar(@{$ret->[0]})) {
            my $anz = scalar(@{$ret->[0]});
            Log("Found ".scalar(@{$ret->[0]})."/".$anz." MACs, using ".uc($ret->[0]->[0]->{$MAC.$TSEP."address"}), $DEBUG);
            my $where2 = $self->Where_Pre($curSession, "enduser");
            push(@$where2, "("."enduser".$TSEP."address = '".uc($ret->[0]->[0]->{$MAC.$TSEP."address"})."')");
            my $ret2 = undef;
            if (defined($ret2 = $db->getDataSet({
               table    => "enduser",
               session  => $curSession,
               wherePre => $where2,
               orderby  => "enduser".$TSEP.$UNIQIDCOLUMNNAME,
            })) && (ref($ret2) eq "ARRAY") && scalar(@{$ret2->[0]})) {
               Log("Found ".scalar(@{$ret->[0]})." Enduser", $DEBUG);
               $return->{$ticketid} = $ret2->[0]->[0];
               $return->{$ticketid}->{anz} = $anz;
            }
         }
      }
      $client->send(JSON->new->allow_nonref->encode({result => "ok", data => $return})."\n");
   } elsif (/^(JSON\s+)?SURFREQUEST\s+(\S+)\s+(\S+)\s+(\S+)(\s(\S+))?$/) {
      my $json = $1;
      my $pass = $3;
      my $ip = $4;
      my $mac = $6;
      my $oldformat = 0;
      unless ($mac) {
         $oldformat++;
         ($ip, $mac) = split(":", $ip);
      }
      Log("SURFREQUEST no longer supported!", $ERROR);
      return $client->send("SURFREQUEST ".to_json({
         mac    => $mac,
         ip     => $ip,
         failed => 1,
      })."\n") if $json;
      $client->send("SURFREQUEST "."0"          ." ".($oldformat ? $ip.":".$mac : $ip." ".$mac)." "."0".                " FAILED\n");
   } elsif (/^SETBOXSTATUS\s(\S+)$/) {
      my $action = $1;
      if (!($connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME})) {
         $client->send("BOXSTATUS FAILED: No access!\n");
      }
      if ($action eq "BEGIN") {
         return $self->protokolError($client) unless ($connection->{state} == $self->{NOSTATE});
         $connection->{state} = $self->{BOXDATA};
         $connection->{lines} = '';
      } elsif ($action eq "END") {
         return $self->protokolError($client) unless ($connection->{state} == $self->{BOXDATA});
         $connection->{state} = $self->{NOSTATE};
         Log("Setting ".$connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}." to ".length($connection->{lines})." Bytes", $DEBUG);
         $self->{boxdatastatus}->{$connection->{boxsession}->{$BOXES.$TSEP.$UNIQIDCOLUMNNAME}} = "Status von: ".$connection->{boxsession}->{$BOXES.$TSEP.$NAMECOLUMNNAME}." am ".localtime(time())."<br><br>\n".$connection->{lines};
         $connection->{lines} = '';
      }
   } elsif (/^GETBOXSTATUS\s(\S+)$/) {
      my $uniqid = $1;
      my $table = $BOXES;
      my $db = $self->getDBBackend($table);
      unless ($uniqid) {
         $client->send("GETBOXSTATUS ".$uniqid." FAILED ACCESS DENIED\n");
         return Log("DBManager: onNewLineServer: GETBOXSTATUS: NO ID!", $WARNING);
      }
      my $curSession = $self->getSession($connection->{sessionID});
      if (defined(my $err = $self->checkRights($curSession, $ACTIVESESSION, $table, $uniqid))) {
         $client->send("GETBOXSTATUS ".$uniqid." FAILED ACCESS DENIED\n");
         return Log("DBManager: onNewLineServer: GETBOXSTATUS: ACCESS DENIED: ".$err->[0], $err->[1]);
      }
      return $self->protokolError($client) unless ($connection->{state} == $self->{NOSTATE});
      my $ret;
      unless (defined($ret = $db->getDataSet({
         table    => $table,
         session  => $curSession,
         id       => $uniqid,
         wherePre => $self->Where_Pre($curSession, $table),
      })) && (ref($ret) eq "ARRAY")) {
         Log("DBManager: onNewLineServer: GETBOXSTATUS ".$uniqid." FAILED SQL Query failed.", $WARNING);
         return $client->send("GETBOXSTATUS ".$uniqid." FAILED\n")
      }
      unless ((ref($ret) eq "ARRAY") && ($ret->[1] == 1)) {
         #$client->send("GETBOXSTATUS ".$uniqid." FAILED ACCESS DENIED\n");
         $client->send("GETBOXSTATUS ".$table." BEGIN\n");
         $client->send("ACCESS DENIED\n");
         $client->send("GETBOXSTATUS ".$table." END\n");
         return Log("DBManager: onNewLineServer: GETBOXSTATUS: ACCESS DENIED");
      }
      $client->send("GETBOXSTATUS ".$table." BEGIN\n");
      $client->send($self->{boxdatastatus}->{$uniqid});
      $client->send("GETBOXSTATUS ".$table." END\n");
   } elsif ($connection->{state} == $self->{BOXDATA}) {
      if (length($connection->{lines}) < (1024*128)) {
         $connection->{lines} .= $_."\n";
      }
      #Log("DBManager: onNewLineServer: SET :".$_.":", $DEBUG);
   } else {
      $self->SUPER::LineHandler($_, $client, $onConnect, @_);
   }
   use warnings;
}

sub checkTableRights {
   my $self = shift;
   my $options = shift;
   my $moreparams = shift;
   unless ((!$moreparams) && $options->{table} && $options->{curSession} && $options->{cmd} && $options->{id}) {
      Log("DBManager: checkTableRights: Missing parameters: table:".$options->{table}.":curSession:".$options->{curSession}.": !", $ERROR);
      return "ACCESS DENIED";
   }
   my $ret;
   my $db = $self->getDBBackend($options->{table});
   # TODO:XXX:FIXME: Tabellenamen ueberpruefen! ->  !!!! DONE!!!!!
   unless (defined($ret = $db->getDataSet({
      table      => $options->{table},
      session    => $options->{curSession},
      id         => $options->{id},
      searchdef  => $options->{deleted} ? { $options->{table}.$TSEP.$DELETEDCOLUMNNAME => 1 } : undef,
   })) && (ref($ret) eq "ARRAY")) {
      Log("DBManager: checkTableRights: ".$options->{cmd}.": ".$options->{table}.": SQL Query failed.", $WARNING);
      return "ACCESS DENIED";
   }
   my $custprefix = ($options->{table} eq $CUSTOMER) ? $options->{table} : $CUSTOMER."_".$UNIQIDCOLUMNNAME."_".$CUSTOMER;
   my $userCheck =
      ($options->{userAssignmentIsOK} &&
      (ref($ret) eq "ARRAY") &&
      (ref($ret->[0]) eq "ARRAY") &&
      (ref($ret->[0]->[0]) eq "HASH") &&
   (exists($ret->[0]->[0]->{$custprefix.$TSEP.$USERSTABLENAME."_".$UNIQIDCOLUMNNAME})) &&
  (defined($ret->[0]->[0]->{$custprefix.$TSEP.$USERSTABLENAME."_".$UNIQIDCOLUMNNAME})) &&
           $ret->[0]->[0]->{$custprefix.$TSEP.$USERSTABLENAME."_".$UNIQIDCOLUMNNAME} &&
   (exists($options->{curSession}->{$USERSTABLENAME.$TSEP.$UNIQIDCOLUMNNAME})) &&
  (defined($options->{curSession}->{$USERSTABLENAME.$TSEP.$UNIQIDCOLUMNNAME})) &&
           $options->{curSession}->{$USERSTABLENAME.$TSEP.$UNIQIDCOLUMNNAME} &&
          ($options->{curSession}->{$USERSTABLENAME.$TSEP.$UNIQIDCOLUMNNAME} eq
           $ret->[0]->[0]->{$custprefix.$TSEP.$USERSTABLENAME."_".$UNIQIDCOLUMNNAME})) ? 1 : 0;
   my $customerID =
      (exists($options->{customerid}) &&
      defined($options->{customerid}) &&
              $options->{customerid}) ?
              $options->{customerid} :
     ((exists($options->{curSession}->{$USERSTABLENAME.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME})) &&
     (defined($options->{curSession}->{$USERSTABLENAME.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME})) &&
              $options->{curSession}->{$USERSTABLENAME.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}) ?
              $options->{curSession}->{$USERSTABLENAME.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME} : undef;
   my $customerCheck =
      (defined($customerID) &&
      (ref($ret) eq "ARRAY") &&
      (ref($ret->[0]) eq "ARRAY") &&
      (ref($ret->[0]->[0]) eq "HASH") &&
   (exists($ret->[0]->[0]->{$custprefix.$TSEP.$UNIQIDCOLUMNNAME})) &&
  (defined($ret->[0]->[0]->{$custprefix.$TSEP.$UNIQIDCOLUMNNAME})) &&
           $ret->[0]->[0]->{$custprefix.$TSEP.$UNIQIDCOLUMNNAME} &&
          ($ret->[0]->[0]->{$custprefix.$TSEP.$UNIQIDCOLUMNNAME} eq $customerID)) ? 1 : 0;
   unless ($customerCheck || $userCheck) {
      if ($options->{userAssignmentIsOK}) {
         Log("Darf nicht sein!!!", $ERROR)
            if ($options->{customerid});
      } else {
         if ($options->{customerid}) {
            print $options->{table}.":".$CUSTOMER."\n";
            my $curtabledef      = $self->getTableDefiniton($options->{table});
            my $curtablecustomer = $self->getTableDefiniton($CUSTOMER);
            return
               ($curtabledef->{label} || $options->{table})." mit CustomerID ".$customerID.
               ($ret->[1] ?
               $options->{table}.$TSEP.$curtabledef->{crossShowInSelect}."\n".
               join("<br>", map { $_."=".$ret->[0]->[0]->{$_} } sort { $a cmp $b } keys %{$ret->[0]->[0]}).
               ($curtabledef->{crossShowInSelect} ?
                  ' "'.$ret->[0]->[0]->{$custprefix.$TSEP.$curtabledef->{crossShowInSelect}}.'"' :
                  ' ID:'.$ret->[0]->[0]->{$custprefix.$TSEP.$UNIQIDCOLUMNNAME}).' kann nur mit '.
               ($curtablecustomer->{label} || $CUSTOMER).
               ($curtablecustomer->{crossShowInSelect} ?
                  ' "'.$ret->[0]->[0]->{$custprefix.$TSEP.$curtablecustomer->{crossShowInSelect}}.'"' :
                  ' ID:'.$ret->[0]->[0]->{$custprefix.$TSEP.$UNIQIDCOLUMNNAME}) : "-").
               " verwendet werden!"; #und nicht vom ".($curtablecustomer->{label} || $CUSTOMER)." ".$options->{customerid}."!";
         } else {
            Log("Darf auch nicht sein!!!", $ERROR);
         }
      }
      return "ACCESS DENIED";
   }
   return undef;
}

my $customertables = [$VORLAGE, $BOXES, $VERBUND, $MACSPEZIAL, $USERSTABLENAME, $TICKETS, $CUSTOMER, $URLFILTER];
sub BeforeNewUpdate {
   my $self = shift;
   my $table = shift;
   my $cmd = shift;
   my $columns = shift;
   my $curSession = shift;
   if (grep { lc($table) eq lc($_) } @$customertables) {
      my $basehash = {
         cmd => $cmd,
         curSession => $curSession
      };
      if (defined(my $err = $self->checkRights($curSession, $ADMIN, $table, undef))) {
         if ($table eq $CUSTOMER) {
            $columns->{$table.$TSEP.$USERSTABLENAME."_".$UNIQIDCOLUMNNAME} = $curSession->{$USERSTABLENAME.$TSEP.$UNIQIDCOLUMNNAME};
            unless ($columns->{$table.$TSEP.$USERSTABLENAME."_".$UNIQIDCOLUMNNAME}) {
               Log("DBManager: BeforeNewUpdate: No User ID!", $ERROR);
               return "ACCESS DENIED";
            }
         } else {
            if (uc($cmd) eq "NEW") {
               $columns->{$table.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME} ||= $curSession->{$USERSTABLENAME.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME};
               unless ($columns->{$table.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}) {
                  Log("DBManager: BeforeNewUpdate: No Customer ID!", $ERROR);
                  return "ACCESS DENIED";
               }
            }
         }
         $columns->{$table.$TSEP.$ADMINFLAGCOLUMNNAME} = 0 if ($table eq $USERSTABLENAME);
         if ((uc($cmd) eq "DEL") || (uc($cmd) eq "UNDEL")) {
            if (defined(my $err = $self->checkTableRights({cmd => $cmd,
               curSession => $curSession,
               table => $table,
               columns => $columns,
               id => $columns->{$table.$TSEP.$UNIQIDCOLUMNNAME},
               condtion => ($cmd eq "NEW"),
               undel => (uc($cmd) eq "UNDEL")
            }))) {
               return $err;
            }
         } else {
            if (defined(my $err =
            ((uc($cmd) eq "NEW") ? undef : $self->checkTableRights({ # Darf ich in der fraglichen Tabelle in der bestehenden Zeile für den derzeit ausgewählten Kunden agieren?
               %$basehash,
               table => $table,
               id => $columns->{$table.$TSEP.$UNIQIDCOLUMNNAME},
               undel => (uc($cmd) eq "UNDEL"),
               userAssignmentIsOK => 1})) ||
              ((exists($columns->{$table.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}) &&
               defined($columns->{$table.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}) &&
                       $columns->{$table.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}) ?
               $self->checkTableRights({ # Darf ich in der fraglichen Tabelle in der ausgewählten Zeile für den neuen Kunden agieren?
               %$basehash,
               table => $CUSTOMER,
               id => $columns->{$table.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME},
               userAssignmentIsOK => 1}) : undef))) {
               return $err;
            }
         }
      }
      foreach my $curtable (grep { (lc($table) ne lc($_)) && ($table ne $CUSTOMER) } @$customertables) {
         next unless (exists($columns->{$table.$TSEP.$curtable."_".$UNIQIDCOLUMNNAME}) &&
                     defined($columns->{$table.$TSEP.$curtable."_".$UNIQIDCOLUMNNAME}) &&
                             $columns->{$table.$TSEP.$curtable."_".$UNIQIDCOLUMNNAME});
         my $curtabledef = $self->getTableDefiniton($table);
         return "Den Wert für ".($curtabledef->{label} || $curtable)." kann nicht ohne Customer ID geändert werden!"
            unless (exists($columns->{$table.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}) &&
                   defined($columns->{$table.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}) &&
                           $columns->{$table.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME});
         #print STDERR "Testing ".$curtable."(".$columns->{$table.$TSEP.$curtable."_".$UNIQIDCOLUMNNAME}.") IS CUSTOMER ".$columns->{$table.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}."\n";
         if (defined(my $err = $self->checkTableRights({ # Ist der ausgewählte $curtable dem fragenlichen Kunden zugeordnet?
            %$basehash,
            table => $curtable,
            id =>         $columns->{$table.$TSEP.$curtable."_".$UNIQIDCOLUMNNAME},
            customerid => $columns->{$table.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}}))) {
            return $err;
         }
      }
   }
   return $self->SUPER::BeforeNewUpdate($table, $cmd, $columns, @_);
}

sub Where_Pre {
   my $self = shift;
   my $a = shift;
   my $b = shift;
   my $moreparams = shift;
   my $options = undef;
   if ($b) {
      $options->{curSession} = $a;
      $options->{table} = $b;
   } else {
      $options = $a;
   }
   my $ret = $self->SUPER::Where_Pre($options);
   return $ret
      unless (defined(my $err = $self->checkRights($options->{curSession}, $ADMIN)));
   if (grep { $options->{table} eq $_ } @$customertables) {
      if ($options->{curSession}->{$USERSTABLENAME.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}) {
         my $customerpre = ($options->{table} eq $CUSTOMER) ? "" : $CUSTOMER."_".$UNIQIDCOLUMNNAME."_";
         push(@$ret, "(".$customerpre.$CUSTOMER.$TSEP.$UNIQIDCOLUMNNAME."=".$options->{curSession}->{$USERSTABLENAME.$TSEP.$CUSTOMER."_".$UNIQIDCOLUMNNAME}." OR ".
                         $customerpre.$CUSTOMER.$TSEP.$USERSTABLENAME."_".$UNIQIDCOLUMNNAME."=".$options->{curSession}->{$USERSTABLENAME.$TSEP.$UNIQIDCOLUMNNAME}.")");
      } else {
         Log("DBManager: Where_Pre: No Customer ID on User ".$options->{curSession}->{$USERSTABLENAME.$TSEP.$USERNAMECOLUMNNAME}."!\n", $ERROR);
         push(@$ret, "(1 = 2)");
      }
   } elsif ($options->{table} eq $LOG) {
      # TODO/FIXME/XXX: Logging nach KundenID!
      push(@$ret, "(".$LOG.$TSEP.$USER."='".($options->{curSession}->{$USERSTABLENAME.$TSEP.$USERNAMECOLUMNNAME} || $options->{curSession}->{$BOXES.$TSEP.$NAMECOLUMNNAME})."')");
   }
   return $ret;
}

1;
