sub show {
  my( $cgi, $rh_template_variables ) = @_;

  my $coupon_code = $cgi->param("coupon_code");
  return unless $coupon_code;

  my %stmt_lookup = (
             codes          => 'SELECT codelist_id, active, last_change, start_date, end_date, (start_date IS NULL OR start_date < NOW()) AND (end_date IS NULL OR end_date > NOW()) FROM codes WHERE code = ?',
             benefits_codes => {
               
     codelist => 'SELECT type, percent, amount, currency, start_date, end_date, description_de, (start_date IS NULL OR start_date < NOW()) AND (end_date IS NULL OR end_date > NOW()) FROM benefits_codes WHERE codelist_id = ?',
               
     code     => 'SELECT type, percent, amount, currency, start_date, end_date, description_de, (start_date IS NULL OR start_date < NOW()) AND (end_date IS NULL OR end_date > NOW()) FROM benefits_codes WHERE coupon_code = ?'
               
        }
            );

  $rh_template_variables->{coupon_code} = $coupon_code;
  my $found_something = 0;    # Variable wird auf 0 gesetzt
  foreach my $shop_abbr ( @{$SHOP::Constants::supported{shop_abbr}{Server}} ) {
    my $db_id = $SHOP::Constants::shop{$shop_abbr}{db_id};
    my $dbh = DBI->connect( @{$SHOP::Constants::db{$db_id}{connect}{shop_write}} );

    my $rh_code = $rh_template_variables->{code_lookup}{$shop_abbr}{code} = {};
    @$rh_code{ qw( codelist_id active last_change start_date end_date is_valid ) } = $dbh->selectrow_array( $stmt_lookup{codes}, {}, $coupon_code );
    #### $hash{eins}, $hash{zwei}, $hash{drei}        @hash{'eins', 'zweiter key', 'drei'}   @hash{ qw(eins zweiter key drei), 'zweiter key', qw(...) }
    #      my( $codelist_id, $active, $last_change, $start_date, $end_date ) = $dbh->selectrow_array( $stmt_lookup{codes}, {}, $coupon_code );
    if ( defined $rh_code->{codelist_id} ) {
      $found_something = 1; # Wenn eingegebener Code gefunden dann Variable auf 1 gesetzt
      $rh_code->{last_change} =~ /^(\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)$/;
      $rh_template_variables->{code_lookup}{$shop_abbr}{code}{last_change} = sprintf( "%04d-%02d-%02d %02d:%02d:%02d", $1, $2, $3, $4, $5, $6 );

      $rh_code->{is_used} = ( ($rh_code->{active} == 1 or $rh_code->{codelist_id} == 0 ) ? 1 : 0 );

      if ( $rh_code->{codelist_id} > 0 ) {
    my $sth_lookup = $dbh->prepare( $stmt_lookup{benefits_codes}{codelist} );
    $sth_lookup->execute( $rh_code->{codelist_id} ); #or warn $dbh->errstr();
    while ( my $ra_benefits_codes = $sth_lookup->fetchrow_arrayref() ) {
      push @{$rh_template_variables->{code_lookup}{$shop_abbr}{actions}}, {
               
               
             type           => $ra_benefits_codes->[0] || '',
               
               
             percent        => $ra_benefits_codes->[1] || '',
               
               
             amount         => $ra_benefits_codes->[2] || '',
               
               
             currency       => $ra_benefits_codes->[3] || '',
               
               
             start_date     => $ra_benefits_codes->[4] || '',
               
               
             end_date       => $ra_benefits_codes->[5] || '',
               
               
             description_de => $ra_benefits_codes->[6] || '',
               
               
             is_used        => $ra_benefits_codes->[7] || '',
               
               
             codelist_id    => $rh_code->{codelist_id}
               
               
            };
    }
      }
    } else {
      delete $rh_template_variables->{code_lookup}{$shop_abbr};
    }

    ### lookup in benefits_codes über coupon_code
    my $sth_lookup = $dbh->prepare( $stmt_lookup{benefits_codes}{code} );
    $sth_lookup->execute( $coupon_code );
    while ( my $ra_benefits_codes = $sth_lookup->fetchrow_arrayref() ) {
      $found_something = 1;
      push @{$rh_template_variables->{code_lookup}{$shop_abbr}{actions}}, {
               
               
         type           => $ra_benefits_codes->[0] || '',
               
               
         percent        => $ra_benefits_codes->[1] || '',
               
               
         amount         => $ra_benefits_codes->[2] || '',
               
               
         currency       => $ra_benefits_codes->[3] || '',
               
               
         start_date     => $ra_benefits_codes->[4] || '',
               
               
         end_date       => $ra_benefits_codes->[5] || '',
               
               
         description_de => $ra_benefits_codes->[6] || '',
               
               
         is_used        => $ra_benefits_codes->[7] || '',
               
               
         codelist_id    => ''
               
               
        };
    }
    $dbh->disconnect();
  }
  #  wenn Variable = 0 oder undefiniert dann gib Fehlermeldung aus!
  unless( $found_something ) {
    $rh_template_variables->{error} = 'nothing_found';
  }
}