#!/usr/bin/perl -w
#
# Copyright (c) 2004 Stefan Moeding <s.moeding@setuid.de>
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions
# are met:
# 1. Redistributions of source code must retain the above copyright
#    notice, this list of conditions and the following disclaimer.
# 2. Redistributions in binary form must reproduce the above copyright
#    notice, this list of conditions and the following disclaimer in the
#    documentation and/or other materials provided with the distribution.
#
# THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
# ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
# ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
# FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
# OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
# HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
# OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
# SUCH DAMAGE.
#
#       $Id: hsdb,v 1.13 2004/04/01 16:22:43 sm Exp $
#


use strict;
use utf8;
use Getopt::Long;


my $conffile = '/usr/local/etc/hsdb.ini';

##############################################################################
#
# Argumente auswerten:
# --file=...   Filename, der gelesen oder geschrieben werden soll (abhängig
#              vom Kommando)
# --cmd=...    auszuführendes Kommando
# --hive=...   Hive Name
# --opt=...    Kommandospezifische Option
# --arg=...    Argument für das Kommando (z.B. Suchmuster für Paketname)
# --index=...  Index des Ergebnisses
#
my ($file, $cmd, $arg, $hive, $opt, $index);

GetOptions("cmd=s"  => \$cmd,  "arg=s"  => \$arg,
           "hive=s" => \$hive, "file=s" => \$file,
           "index=i" => \$index,
	   "opt=s" => \$opt ) || die;

die "Option cmd is mandatory" if (!defined($cmd));
die "Option arg is mandatory" if (!defined($arg));

$file = '-' if (!defined($file));	# Use stdin
$index = 0 if (!defined($index));
$cmd  = lc($cmd);			# Lowercase the command string
$arg =~ tr/*?/%_/;			# Replace with SQL symbols

#
#
#
my $rc = 0;

if ($cmd eq "addr_get") {
  my $db = new HSAddress;
  $db->init($conffile);
  $rc = $db->addr_get($arg, $file);
}
elsif ($cmd eq "addr_get_by_index") {
  my $db = new HSAddress;
  $db->init($conffile);
  $rc = $db->addr_get_by_index($arg, $file, $index);
}
elsif ($cmd eq "addr_exists") {
  my $db = new HSAddress;
  $db->init($conffile);
  $rc = $db->addr_exists($arg, $file);
}
elsif ($cmd eq "hive_get") {
  my $db = new HSHive;
  $db->init($conffile);
  $rc = $db->hive_get($arg, $file);
}
elsif ($cmd eq "hives_by_name") {
  my $db = new HSHive;
  $db->init($conffile);
  $rc = $db->hives_by_name($arg);
}
elsif ($cmd eq "pac_get") {
  my $db = new HSPacket;
  $db->init($conffile);
  $rc = $db->pac_get($arg, $file);
}
elsif ($cmd eq "pacs_by_name") {
  my $db = new HSPacket;
  $db->init($conffile);
  $rc = $db->pacs_by_name($arg, $hive);
}
elsif ($cmd eq "pacs_by_member") {
  my $db = new HSPacket;
  $db->init($conffile);
  $rc = $db->pacs_by_member($arg);
}
elsif ($cmd eq "pacs_by_type") {
  my $db = new HSPacket;
  $db->init($conffile);
  $rc = $db->pacs_by_type($arg, $hive);
}
elsif ($cmd eq "pac_exists") {
  my $db = new HSPacket;
  $db->init($conffile);
  $rc = $db->pac_exists($arg);
}
elsif ($cmd eq "pac_list") {
  my $db = new HSPacket;
  $db->init($conffile);
  $rc = $db->pac_list($arg);
}
elsif ($cmd eq "doms_by_name") {
  my $db = new HSDomain;
  $db->init($conffile);
  $rc = $db->doms_by_name($arg, $hive);
}
elsif ($cmd eq "doms_by_owner") {
  my $db = new HSDomain;
  $db->init($conffile);
  $rc = $db->doms_by_owner($arg, $hive);
}
elsif ($cmd eq "dom_exists") {
  my $db = new HSDomain;
  $db->init($conffile);
  $rc = $db->dom_exists($arg, $hive);
}
elsif ($cmd eq "doms_list") {
  my $db = new HSDomain;
  $db->init($conffile);
  $rc = $db->doms_list($arg, $hive);
}
elsif ($cmd eq "dom_get") {
  my $db = new HSDomain;
  $db->init($conffile);
  $rc = $db->dom_get($arg, $hive);
}
else {
  die $cmd, ": unknown command\n";
}

exit $rc;


##############################################################################
#
# DataFile - Parser und Serializer zur Verarbeitung der Daten in Flat-Files.
#
# Beim Anlegen eines Objektes wird ein Filename und eine Filerliste übergeben.
# Die Methoden read und write übernehmen dann das Einlesen bzw. Schreiben
# der Daten.  Dazu bekommt write einen entsprechenden Hash übergeben,
# während read einen Hash zurückliefert.  Der Hash besteht aus Schlüssel-
# Werte-Paaren, die zu einem Eintrag der Form SCHLUESSEL='WERT' in der
# Datei gehören.  Es werden nur Zeilen mit solchen Schlüsseln verarbeitet,
# die in der Filterliste vorkommen.
#

package DataFile;

use Carp;

#
# Konstruktor
#
sub new {
  my $proto = shift;
  my $class = ref($proto) || $proto;
  my $self  = {};

  $self->{FILENAME}  = shift;
  @{$self->{FILTER}} = @_;

  bless($self, $class);
  return $self;
}

#
# Einlesen der Daten in einen Hash
#
sub read {
  my $self = shift;
  my %hash;

  # File öffnen (ggfs. wird stdin verwendet, falls der Filename '-' ist.
  if ($self->{FILENAME} ne '-') {
    open(FILE, $self->{FILENAME}) || croak $self->{FILENAME}, ": $!";
  }
  else {
    *FILE = \*STDIN;
  }

  while(<FILE>) {
    # Whitespace, Newline & Carriage Return am Zeilenende plätten
    s/[\s\012\015]+$//;

    # Quoting entfernen
    s/^([A-Z0-9_]+)='(.*)'$/$1=$2/;
    s/^([A-Z0-9_]+)="(.*)"$/$1=$2/;

    # Leere Zeilen oder Kommentare werden ignoriert
    next if ((/^$/) || (/^#/));

    # Passende Zeilen verarbeiten
    if (/^([A-Z0-9_]+)=([\wöäüáàéèíìóòßÖÄÜÁÀÍÌÓÒÉÈ !.,;:=&@\/\?\$\^\(\)\\\+\*-]*)$/) {
      delete($hash{$1});		# Alten Wert löschen
      $hash{$1} = $2 if ($2 ne '');	# Neuen Wert eintragen, falls gesetzt

      next;
    }

    # Der Rest sind Fehler
    warn $0, ": Parse error in input ($_)";
    exit 1;
  }

  if ($self->{FILENAME} ne '-') {
    close(FILE);
  }

  if (defined($self->{FILTER})) {
    foreach (keys %hash) {
      # Spaces am Anfang und Ende entfernen
      $hash{$_} =~ s/^\s+//;
      $hash{$_} =~ s/\s+$//;

      # Löschen der Einträge, die nicht in der Filterliste auftauchen
      delete($hash{$_}) if (!grep(/^$_$/, @{$self->{FILTER}}));
    }
  }

  return %hash;
}

#
# Ausgabe der Daten in ein File
#
sub write {
  my $self = shift;
  my %hash = @_;

  # File öffnen (ggfs. wird stdout verwendet, falls der Filename '-' ist.
  if ($self->{FILENAME} ne '-') {
    open(FILE, '>'.$self->{FILENAME}) || croak $self->{FILENAME}, ": $!";
  }
  else {
    *FILE = \*STDOUT;
  }

  # Nur Einträge aus der Filterliste ausgeben
  # Die Reihenfolge wird ebenfalls durch die Filterliste bestimmt.
  foreach (@{$self->{FILTER}}) {
    printf FILE "%s='%s'\n", $_, (defined($hash{$_}) ? $hash{$_} : '');
  }

  if ($self->{FILENAME} ne '-') {
    close(FILE);
  }
}


##############################################################################
#
# DBConnect - Verbindung zur Datenbank
#

package DBConnect;

use Config::IniFiles;
use Carp;
use DBI;

#
# Konstruktor
#
sub new {
  my $proto = shift;
  my $class = ref($proto) || $proto;
  my $self  = {};

  $self->{DBH} = undef;
  $self->{STH} = undef;
  $self->{TNX} = 0;	# Flag für offene Transaktion

  bless($self, $class);
  return $self;
}

#
# Destruktor
#
sub DESTROY {
  my $self = shift;

  # Statement freigeben
  if (defined($self->{STH})) {
    $self->{STH}->finish;
    $self->{STH} = undef;
  }

  # Zur Sicherheit Transaktion zurückrollen und abmelden
  if (defined($self->{DBH})) {
    $self->{DBH}->rollback if ($self->{TNX} != 0);
    $self->{DBH}->disconnect;
    $self->{DBH} = undef;
  }
}

#
# Initialisierung
# - Auslesen der Konfigurationsdaten
# - Anmelden an der Datenbank
#
sub init {
  my $self = shift;
  my $file = shift;

  my ($user, $pass, $cfg);
  my $dsn = 'DBI:Pg:';
  my $sec = 'hsdb';

  # Konfiguration zur Verbindung an die Datenbank einlesen.
  # Im Inifile wird die durch $sec bestimmte Section verwendet.
  $cfg = Config::IniFiles->new(-file => $file);
  croak $0, ": ", join("\n", @Config::IniFiles::errors) unless (defined($cfg));

  $user = $cfg->val($sec, 'username');
  $pass = $cfg->val($sec, 'password');

  $user = '' unless (defined($user));
  $pass = '' unless (defined($pass));

  # Connectstring zusammenbauen
  foreach ('dbname', 'host', 'port', 'options') {
    if ((defined($cfg->val($sec, $_))) && (length($cfg->val($sec, $_)) > 0)) {
      $dsn .= $_ . '=' . $cfg->val($sec, $_) . ';';
    }
  }

  # Verbindung zur Datenbank erstellen.  Abbruch bei Fehlern.
  $self->{DBH} = DBI->connect($dsn, $user, $pass,
                              { RaiseError => 0,
                                PrintError => 0,
                                AutoCommit => 0 });

  croak $0, ": ", $DBI::errstr unless (defined($self->{DBH}));

  # Datumsformat einstellen
  $self->{DBH}->do("SET DATESTYLE = GERMAN") || croak $0, ": ", $DBI::errstr;

  # Charset einstellen
  $self->{DBH}->do("SET CLIENT_ENCODING = UTF8") || croak $0, ": ", $DBI::errstr;

  return $self;
}

#
# Änderungen in der Datenbank festschreiben
#
sub commit {
  my $self = shift;
  $self->{DBH}->commit() || croak $0, ": ", $DBI::errstr;
  $self->{TNX} = 0;
}

#
# Änderungen in der Datenbank verwerfen
#
sub rollback {
  my $self = shift;
  $self->{DBH}->rollback() || croak $0, ": ", $DBI::errstr;
  $self->{TNX} = 0;
}

#
# Anzahl der betroffenen Zeilen zurückgeben
#
sub rows {
  my $self = shift;
  return $self->{STM}->rows;
}

#
# Ausführen eines SQL-Kommandos. Der erste Parameter ist das Statement
# (mit Platzhaltern). Die restlichen Parameter sind die Werte der
# Platzhalter.
#
sub exec {
  my $self = shift;
  my $stmt = shift;

  $self->{STH} = $self->{DBH}->prepare($stmt);
  croak $0, ": ", $DBI::errstr unless (defined($self->{STH}));

  $self->{STH}->execute(@_) || croak $0, ": ", $DBI::errstr;
  $self->{TNX} = 1;

  return $self;
}

#
# Auslesen einer Ergebniszeile. Es wird ein Hash zurückgegeben, dessen
# Keys die Spaltennamen (in Großschrift) des Ergebnisses darstellen.
# NULLs werden durch einen leeren Strings repräsentiert.
#
sub fetch {
  my $self = shift;
  my $ref  = $self->{STH}->fetchrow_hashref('NAME_uc');
  my %hash;

  if (defined($ref)) {
    for (@{$self->{STH}->{'NAME_uc'}}) {
      $hash{$_} = (defined($$ref{$_}) ? $$ref{$_} : '');
    }
  }

  return %hash;
}

#
# Ausführen eines SQL-Kommandos ohne Sicherung des Ergebnisses
# (z.B. DDL). Der erste Parameter ist das Statement (mit Platzhaltern).
# Die restlichen Parameter sind die Werte der Platzhalter.
#
sub do {
  my $self = shift;
  my $stmt = shift;
  my $sth  = $self->{DBH}->prepare($stmt);
  croak $0, ": ", $DBI::errstr unless (defined($sth));

  $sth->execute(@_) || croak $0, ": ", $DBI::errstr;
  $self->{TNX} = 1;

  return $self;
}

#
# Ausführen eines Statements, welches einen einzelnen Wert
# zurückliefert. Falls die Abfrage keine Zeile ergibt, dann wird der
# Wert undef zurückgegeben. Der erste Parameter ist wieder der Text
# der Abfrage und die übrigen Parameter werden als Werte der
# Platzhalter verwendet.
#
sub getvalue {
  my $self = shift;
  my $stmt = shift;
  my ($stm, @res, $val);

  $stm = $self->{DBH}->prepare($stmt);
  croak $0, ": ", $DBI::errstr unless (defined($stm));

  $stm->execute(@_) || croak $0, ": ", $DBI::errstr;

  @res = $stm->fetchrow_array;

  $val = ($stm->rows > 0) ? $res[0] : undef;
  $stm->finish();

  return $val;
}


##############################################################################
#
# HSAddress - Adressen der Kunden/Mitglieder
#

package HSAddress;

use base qw(DBConnect);

#
# Konstruktor
#
sub init {
  my $self = shift;

  $self->SUPER::init(@_);

  # Der Filter definiert die erlaubten Variablennamen
  # sowie deren Reihenfolge bei der Ausgabe.
  @{$self->{FILTER}} = ( 'SALUT', 'SALUTATION',
                         'TITLE', 'FIRST_NAME', 'LAST_NAME',
                         'FIRMA', 'CO', 'STREET', 'ZIPCODE', 'CITY', 'COUNTRY',
                         'PHONE_PRIVATE', 'PHONE_OFFICE', 'PHONE_MOBILE',
                         'FAX', 'EMAIL_EXTERNAL', 'UID_VAT',
                         'MEMBER_ID', 'MEMBER_CODE', 'MEMBER_ROLE',
                         'MEMBER_SINCE', 'MEMBER_UNTIL' );

  return $self;
}

#
# Adresse zu $arg aus DB auslesen und nach $file schreiben
#
sub addr_get {
  my $self = shift;
  my $arg  = shift;
  my $file = shift;
  my %hash;
  my $query;

  $query = q{
SELECT bpa.member_id,
       bpa.member_code,
       bpa.member_since,
       bpa.member_until,
       bpa.member_role,
       bpa.author_contract,
       bpa.nondisc_contract,
       bpa.uid_vat,
       con.salut,
       case
         when con.salut = 'Firma' then 'Hallo '||con.firma
         when con.salut = 'Frau'  then 'Hallo Frau '||con.last_name
         when con.salut = 'Herr'  then 'Hallo Herr '||con.last_name
         else                          'Hallo '||con.first_name
       end as salutation,
       con.first_name,
       con.last_name,
       con.title,
       con.firma,
       con.co,
       con.street,
       con.zipcode,
       con.city,
       con.country,
       con.phone_private,
       con.phone_office,
       con.phone_mobile,
       con.fax,
       con.email                           AS email_external
  FROM business_partner bpa,
       contact          con,
       contactrole_ref  role
 WHERE bpa.member_code = ?
   AND bpa.bp_id = con.bp_id
   AND role.contact_id = con.contact_id
   AND role = 'operation'
 LIMIT 1
};

  $self->exec($query, $arg);
  %hash = $self->fetch;

  if (%hash) {
    my $df = new DataFile($file, @{$self->{FILTER}});
    $df->write(%hash);
  }
  else {
    warn $arg, ": no such MEMBER_CODE\n";
    return 1;
  }

  return 0;
}

#
# Auf existierenden Datensatz für MEMBER_CODE testen. Rückgabe von 0
# falls gefunden und 1 sonst.
#
sub addr_exists {
  my $self = shift;
  my $arg  = shift;

  my $query = 'SELECT bp_id FROM business_partner WHERE member_code = ?';
  return((defined($self->getvalue($query, $arg))) ? 0 : 1);
}

#
# eine durch $index bestimmte Adresse zu $arg aus DB auslesen und nach $file schreiben
#
sub addr_get_by_index {
  my $self = shift;
  my $arg  = shift;
  my $file = shift;
  my $index = shift;
  my %hash;
  my $query;

  $query = q{
SELECT bpa.member_id,
       bpa.member_code,
       bpa.member_since,
       bpa.member_until,
       bpa.member_role,
       bpa.author_contract,
       bpa.nondisc_contract,
       bpa.uid_vat,
       con.salut,
       case
         when con.salut = 'Firma' then 'Hallo '||con.firma
         when con.salut = 'Frau'  then 'Hallo Frau '||con.last_name
         when con.salut = 'Herr'  then 'Hallo Herr '||con.last_name
         else                          'Hallo '||con.first_name
       end as salutation,
       con.first_name,
       con.last_name,
       con.title,
       con.firma,
       con.co,
       con.street,
       con.zipcode,
       con.city,
       con.country,
       con.phone_private,
       con.phone_office,
       con.phone_mobile,
       con.fax,
       con.email                           AS email_external
  FROM business_partner bpa,
       contact          con,
       contactrole_ref  role
 WHERE bpa.member_code = ?
   AND bpa.bp_id = con.bp_id
   AND role.contact_id = con.contact_id
   AND role = 'operation'
   ORDER BY con.email
};

  $self->exec($query, $arg);
  my $has_value = 0;
  my $count = 0;
  while((%hash = $self->fetch())) {
    if ($count == $index) {
      my $df = new DataFile($file, @{$self->{FILTER}});
      $df->write(%hash);
    }
    $count = $count + 1;
    $has_value = 1;
  }

  if ($index >= $count) {
      # es gibt keinen weiteren Eintrag
      return 2
  }

  if ($has_value == 0) {
    warn $arg, ": no such MEMBER_CODE\n";
    return 1;
  }

  return 0;
}

##############################################################################
#
# HSHive - Hives
#

package HSHive;

use base qw(DBConnect);

#
# Konstruktor
#
sub init {
  my $self = shift;

  $self->SUPER::init(@_);

  # Der Filter definiert die erlaubten Variablennamen
  # sowie deren Reihenfolge bei der Ausgabe.
  @{$self->{FILTER}} = ( 'HIVE_NAME', 'HIVE_IPNUMBER', 'HIVE_DESC' );

  return $self;
}

sub hive_get {
  my $self = shift;
  my $arg  = shift;
  my $file = shift;
  my %hash;
  my $query;

  $query = q{
SELECT hiv.hive_name,
       hiv.description AS hive_desc,
       ina.inet_addr   AS hive_ipnumber
  FROM hive           AS hiv
       JOIN inet_addr AS ina USING(inet_addr_id)
 WHERE hiv.hive_name = ?
};

  $self->exec($query, $arg);
  %hash = $self->fetch;

  if (%hash) {
    my $df = new DataFile($file, @{$self->{FILTER}});
    $df->write(%hash);
  }
  else {
    warn $arg, ": no such HIVE_NAME\n";
    return 1;
  }

  return 0;
}

sub hives_by_name {
  my $self = shift;
  my $arg  = shift;
  my $rc   = 1;
  my %hash;

  my $query = q{
SELECT hive_name
  FROM hive
 WHERE hive_name LIKE ?
ORDER BY hive_name
};

  $self->exec($query, $arg);

  while((%hash = $self->fetch())) {
    print $hash{'HIVE_NAME'}, "\n";
    $rc = 0;
  }

  return $rc;
}


##############################################################################
#
# HSPacket - Pakete
#

package HSPacket;

use base qw(DBConnect);

#
# Konstruktor
#
sub init {
  my $self = shift;

  $self->SUPER::init(@_);

  # Der Filter definiert die erlaubten Variablennamen
  # sowie deren Reihenfolge bei der Ausgabe.
  @{$self->{FILTER}} = ( 'PAC_NAME', 'PAC_MEMBER', 'PAC_TYPE',
                         'PAC_DATE', 'PAC_CANCELLED', 'PAC_ORDERNO',
                         'PAC_FREE', 'PAC_HOST',
                         'PAC_MULTI',
                         'PAC_QUOTA', 'PAC_STORAGE', 'PAC_TRAFFIC',
                         'PAC_CPU', 'PAC_RAM',
                         'PAC_IPNUMBER', 'PAC_IPNUMBER_EX');

  # Mapping der in BASECOMPONENT.BASECOMPONENT_CODE gespeichertern
  # Kürzel auf die dafür verwendeten Variablennamen.
  %{$self->{DB2VAR}} = ('MULTI'      => 'PAC_MULTI',
                        'TRAFFIC'    => 'PAC_TRAFFIC',
                        'QUOTA'      => 'PAC_QUOTA',
                        'STORAGE'    => 'PAC_STORAGE',
                        'CPU'        => 'PAC_CPU',
                        'RAM'        => 'PAC_RAM',
                        'FREE'       => 'PAC_FREE');

  return $self;
}

#
# Paketdaten zu $arg aus DB auslesen und nach $file schreiben
#
sub pac_get {
  my $self = shift;
  my $arg  = shift;
  my %hash;
  my %keep;
  my $query;

  $query = q{
SELECT pac.packet_id,
       pac.packet_name             AS pac_name,
       pac.created                 AS pac_date,
       pac.cancelled               AS pac_cancelled,
       bpa.member_code             AS pac_member,
       hiv.hive_name               AS pac_host,
       ip1.inet_addr               AS pac_ipnumber,
       ip2.inet_addr               AS pac_ipnumber_ex,
       bpc.basepacket_code         AS pac_type,
       pac.basepacket_id
  FROM packet                      AS pac
       INNER JOIN business_partner AS bpa USING(bp_id)
       INNER JOIN hive             AS hiv USING(hive_id)
       INNER JOIN packet_component AS pco USING(packet_id)
       INNER JOIN basepacket       AS bpc USING(basepacket_id)
       LEFT  JOIN inet_addr        AS ip1 ON (pac.cur_inet_addr_id = ip1.inet_addr_id)
       LEFT  JOIN inet_addr        AS ip2 ON (pac.old_inet_addr_id = ip2.inet_addr_id)
 WHERE pac.packet_name = ?
 LIMIT 1
};

  $self->exec($query, $arg);

  if ((%hash = $self->fetch())) {
    %keep = %hash;

    $query = q{
SELECT bco.basecomponent_code,
       COALESCE(pco.quantity, com.default_quantity) AS quantity
  FROM            
       packet AS pkt
       INNER JOIN packet_component AS pco USING(packet_id)
       RIGHT JOIN component        AS com USING(basepacket_id,basecomponent_id)
       INNER JOIN basepacket       AS bpc USING(basepacket_id)
       INNER JOIN basecomponent    AS bco USING(basecomponent_id)
 WHERE (   pkt.packet_id = ?
        OR pco.packet_id IS NULL)
   AND com.basepacket_id = ?
};

    $self->exec($query, $keep{'PACKET_ID'}, $keep{'BASEPACKET_ID'});

    while ((%hash = $self->fetch())) {
      # Bei der Auswertung wird für die jeweilige Komponente durch
      # die Mappingfunktion der zugehörige Variablenname bestimmt.
      # Als Wert wird die gelieferte Menge gespeichert. Diese Menge
      # wird in der angegebenen Abfrage ggfs. aus der Default-Menge
      # bestimmt, wenn für die Komponente keine explizite Menge
      # angegeben wurde.
      # So lassen sich durch das Hinzufügen einer neuen Komponente zu
      # einem Pakettyp sofort alle Pakete mit einer Defaultmenge
      # ausstatten, ohne daß die Daten explizit angefaßt werden müssen.
      # Erst bei einem erneuten Abspeichern durch die Funktion pac_put
      # bekommt das Paket dann auch einen expliziten Datensatz mit der
      # Menge der neuen Komponente.

      # Der Hash %keep wird zur Speicherung der gesamten über die
      # Schleifendurchläufe ermittelten Daten verwendet.
      if (exists $self->{DB2VAR}{$hash{'BASECOMPONENT_CODE'}}) {
        $keep{$self->{DB2VAR}{$hash{'BASECOMPONENT_CODE'}}} = $hash{'QUANTITY'};
      }
    }

    my $df = new DataFile($file, @{$self->{FILTER}});
    $df->write(%keep);
  }
  else {
    warn $arg, ": no such PAC_NAME\n";
    return 1;
  }

  return 0;
}

#
# Alle auf das Suchmuster $arg passenden PACKET_NAMEn ausgeben
#
sub pacs_by_name {
  my $self = shift;
  my $arg  = shift;
  my $hive = shift;
  my $rc   = 1;
  my %hash;
  my $query;

  if (defined($hive)) {
    $query = q{
SELECT pac.packet_name
  FROM packet    AS pac
       JOIN hive AS hiv USING(hive_id)
 WHERE pac.packet_name like ?
   AND hiv.hive_name = ?
 ORDER BY pac.packet_name
};

    $self->exec($query, $arg, $hive);
  }
  else {
    $query = q{
SELECT packet_name
  FROM packet
 WHERE packet_name like ?
 ORDER BY packet_name
};

    $self->exec($query, $arg);
  }

  while((%hash = $self->fetch())) {
    print $hash{'PACKET_NAME'}, "\n";
    $rc = 0;
  }

  return $rc;
}

#
# Alle PACKET_NAMEn ausgeben, deren ORDER_NUMBER auf das Suchmuster $arg paßt
#
sub pacs_by_orderno {
  my $self = shift;
  my $arg  = shift;
  my $rc   = 1;
  my %hash;
  my $query;

  $query = q{
SELECT packet_name
  FROM packet
 WHERE order_number like ?
 ORDER BY packet_name
};

  $self->exec($query, $arg);

  while((%hash = $self->fetch())) {
    print $hash{'PACKET_NAME'}, "\n";
    $rc = 0;
  }

  return $rc;
}

#
# Alle PACKET_NAMEn ausgeben, deren Eigentümer auf das Suchmuster $arg paßt
#
sub pacs_by_member {
  my $self = shift;
  my $arg  = shift;
  my $rc   = 1;
  my %hash;

  my $query = q{
SELECT pac.packet_name
  FROM business_partner AS bpa
       JOIN packet      AS pac USING(bp_id)
 WHERE bpa.member_code like ?
 ORDER BY pac.packet_name
};

  $self->exec($query, $arg);

  while((%hash = $self->fetch())) {
    print $hash{'PACKET_NAME'}, "\n";
    $rc = 0;
  }

  return $rc;
}

#
# Alle PACKET_NAMEn ausgeben, die in der Abrechnung berücksichtig ($arg = 0)
# oder nicht ($arg = 1) berücksichtigt werden sollen.
#
sub pacs_by_free {
  my $self = shift;
  my $arg  = shift;
  my $hive = shift;
  my $rc   = 1;
  my %hash;
  my $query;

  if (defined($hive)) {
    $query = q{
SELECT DISTINCT pac.packet_name
  FROM basecomponent               AS bco
       RIGHT JOIN packet_component AS pco USING(basecomponent_id)
       RIGHT JOIN packet           AS pac USING(packet_id)
       JOIN hive                   AS hiv USING(hive_id)
 WHERE bco.basecomponent_code = 'Free'
   AND COALESCE(pco.quantity, 0) = ?
   AND hiv.hive_name = ?
 ORDER by pac.packet_name
};

    $self->exec($query, $arg, $hive);
  }
  else {
    $query = q{
SELECT DISTINCT pac.packet_name
  FROM basecomponent               AS bco
       RIGHT JOIN packet_component AS pco USING(basecomponent_id)
       RIGHT JOIN packet           AS pac USING(packet_id)
 WHERE bco.basecomponent_code = 'Free'
   AND COALESCE(pco.quantity, 0) = ?
 ORDER by pac.packet_name
};

    $self->exec($query, $arg);
  }

  while((%hash = $self->fetch())) {
    print $hash{'PACKET_NAME'}, "\n";
    $rc = 0;
  }

  return $rc;
}

#
# Alle PACKET_NAMEn ausgeben, die auf den Typ $arg passen.
#
sub pacs_by_type {
  my $self = shift;
  my $arg  = shift;
  my $hive = shift;
  my $rc   = 1;
  my %hash;
  my $query;

  if (defined($hive)) {
    $query = q{
SELECT DISTINCT pac.packet_name
  FROM basepacket            AS bpa
       JOIN packet           AS pac USING(basepacket_id)
       JOIN hive             AS hiv USING(hive_id)
 WHERE bpa.basepacket_code like ?
   AND hiv.hive_name = ?
 ORDER by pac.packet_name
};

    $self->exec($query, $arg, $hive);
  }
  else {
    $query = q{
SELECT DISTINCT pac.packet_name
  FROM basepacket            AS bpa
       JOIN packet           AS pac USING(basepacket_id)
 WHERE bpa.basepacket_code like ?
 ORDER by pac.packet_name
};

    $self->exec($query, $arg);
  }

  while((%hash = $self->fetch())) {
    print $hash{'PACKET_NAME'}, "\n";
    $rc = 0;
  }

  return $rc;
}

#
# Auf existierenden Datensatz für PACKET_NAME testen. Rückgabe von 0
# falls gefunden und 1 sonst.
#
sub pac_exists {
  my $self = shift;
  my $arg  = shift;

  my $query = 'SELECT packet_id FROM packet WHERE packet_name = ?';
  return((defined($self->getvalue($query, $arg))) ? 0 : 1);
}

#
# Einfache Liste aller Pakete als CSV-Format ausgeben.
#
sub pac_list {
  my $self = shift;
  my $arg  = shift;
  my $rc   = 1;
  my %hash;

  my $query = q{
SELECT DISTINCT
       pac.packet_name,
       bpa.basepacket_code,
       hiv.hive_name
  FROM packet                AS pac
       JOIN packet_component AS pco USING (packet_id)
       JOIN basepacket       AS bpa USING (basepacket_id)
       JOIN hive             AS hiv USING (hive_id)
 WHERE pac.packet_name like ?
   AND (pac.cancelled IS NULL OR pac.cancelled > now())
 ORDER BY pac.packet_name
};

  $self->exec($query, $arg);

  while((%hash = $self->fetch())) {
    printf "%s:%s:%s\n", $hash{'PACKET_NAME'},
      $hash{'HIVE_NAME'}, $hash{'BASEPACKET_CODE'};
    $rc = 0;
  }

  return $rc;
}

##############################################################################
#
# HSDomain - Domains
#

package HSDomain;

use base qw(DBConnect);

#
# Konstruktor
#
sub init {
  my $self = shift;

  $self->SUPER::init(@_);

  # Der Filter definiert die erlaubten Variablennamen
  # sowie deren Reihenfolge bei der Ausgabe.
  @{$self->{FILTER}} = ( 
	'DOM_DOMAIN',
	'DOM_OWNER',
	'DOM_IPNUMBER',
	'DOM_IPNUMBER_EX');
  return $self;
}

#
# Domaindaten zu $arg aus DB auslesen und nach $file schreiben
#
sub dom_get {
  my $self = shift;
  my $arg  = shift;
  my %hash;
  my %keep;
  my $query;

  $query = q{
SELECT dom.domain_id,
       dom.domain_name          	AS dom_domain,
       usr.name				AS dom_owner,
       ipn_cur.inet_addr                AS dom_ipnumber,
       ipn_old.inet_addr                AS dom_ipnumber_ex
  FROM domain                           AS dom
       INNER JOIN unixuser              AS usr ON usr.unixuser_id = dom.domain_owner
       INNER JOIN packet                AS pac ON pac.packet_id = usr.packet_id
       INNER JOIN inet_addr             AS ipn_cur ON ipn_cur.inet_addr_id = pac.cur_inet_addr_id
       LEFT JOIN inet_addr              AS ipn_old ON ipn_old.inet_addr_id = pac.old_inet_addr_id
 WHERE dom.domain_name = ?
 LIMIT 1
};

  $self->exec($query, $arg);

  if ((%hash = $self->fetch())) {
    %keep = %hash;

    my $df = new DataFile($file, @{$self->{FILTER}});
    $df->write(%keep);
  }
  else {
    warn $arg, ": no such DOM_NAME\n";
    return 1;
  }

  return 0;
}

#
# Alle auf das Suchmuster $arg passenden DOMAIN_NAMEn ausgeben
#
sub doms_by_name {
  my $self = shift;
  my $arg  = shift;
  my $hive = shift;
  my $rc   = 1;
  my %hash;
  my $query;

  if (defined($hive)) {
    $query = q{
SELECT dom.domain_name
  FROM domain    AS dom
       INNER JOIN unixuser AS usr ON dom.domain_owner = usr.unixuser_id
       INNER JOIN packet AS pac USING(packet_id)
       INNER JOIN hive USING(hive_id)
 WHERE dom.domain_name like ?
   AND hive.hive_name = ?
 ORDER BY dom.domain_name
};

    $self->exec($query, $arg, $hive);
  }
  else {
    $query = q{
SELECT domain_name
  FROM domain
 WHERE domain_name like ?
 ORDER BY domain_name
};

    $self->exec($query, $arg);
  }

  while((%hash = $self->fetch())) {
    print $hash{'DOMAIN_NAME'}, "\n";
    $rc = 0;
  }

  return $rc;
}

#
# Alle PACKET_NAMEn ausgeben, deren Eigentümer auf das Suchmuster $arg paßt
#
sub doms_by_owner {
  my $self = shift;
  my $arg  = shift;
  my $rc   = 1;
  my %hash;

  $arg =~ tr/%_/*?/;			# this method uses POSIX regex!

  my $query = q{
SELECT dom.domain_name
  FROM domain AS dom
       JOIN unixuser AS usr ON dom.domain_owner=usr.unixuser_id
 WHERE usr.name ~ ?
 ORDER BY dom.domain_name
};

  $self->exec($query, $arg);

  while((%hash = $self->fetch())) {
    print $hash{'DOMAIN_NAME'}, "\n";
    $rc = 0;
  }

  return $rc;
}

#
# Auf existierenden Datensatz für DOMAIN_NAME testen. Rückgabe von 0
# falls gefunden und 1 sonst.
#
sub dom_exists {
  my $self = shift;
  my $arg  = shift;
  my $hive = shift;
  my $query;

  if (defined($hive)) {
    $query = qq{
    	SELECT dom.domain_id 
	  FROM domain AS dom
         INNER JOIN unixuser AS usr ON dom.domain_owner=usr.unixuser_id
         INNER JOIN packet AS pac USING(packet_id)
         INNER JOIN hive AS hiv USING(hive_id)
	 WHERE dom.domain_name = ?
           AND hiv.hive_name = ?
};
    return((defined($self->getvalue($query, $arg, $hive))) ? 0 : 1);
  }
  else {
    $query = 'SELECT domain_id FROM domain WHERE domain_name = ?';
    return((defined($self->getvalue($query, $arg))) ? 0 : 1);
  }
}

#
# Einfache Liste aller Domains als CSV-Format ausgeben.
#
sub doms_list {
  my $self = shift;
  my $arg  = shift;
  my $rc   = 1;
  my %hash;

  $arg =~ tr/%_/*?/;			# this method uses POSIX regex!

  my $query = q{
SELECT hive.hive_name, 
       dom.domain_name,
       pac.packet_name,
       usr.name
  FROM domain AS dom              
       INNER JOIN unixuser AS usr ON dom.domain_owner=usr.unixuser_id
       INNER JOIN packet AS pac USING(packet_id)
       INNER JOIN hive USING(hive_id)
 WHERE dom.domain_name ~ ?
 ORDER BY dom.domain_name
};

  $self->exec($query, $arg);

  while((%hash = $self->fetch())) {
    printf "%s:%s:%s:%s:%s\n", 
      $hash{'HIVE_NAME'},
      $hash{'DOMAIN_NAME'},
      $hash{'PACKET_NAME'},
      $hash{'NAME'},
    $rc = 0;
  }

  return $rc;
}
