#!/usr/bin/perl

#use strict;

### initializations #################################################

# the constructor does some initializations
sub BEGIN
{
    push(@INC, '../../modules');
    push(@INC, '/kunden/287520_01099/perl/lib/perl5/site_perl/5.8.8');
}

# import necessary packages
use CGI::Carp qw(fatalsToBrowser);
use CGI qw(:standard);
use CGI::Cookie;
use HTML::Template;
use HTML::Entities;
use Database;
use ConfigFile;
use MIME::Lite;
use Encode;
use JSON -convert_blessed_universally;    # ???
use Data::Dumper;

use DateTimeTR;
use EdeTools;

my $CONFIG_FILE = '../../config/project.conf';
my $Config = new ConfigFile($CONFIG_FILE);

require '../../config/config.pl';
require '../../config/constants.pl';

my $DATABASE          = 'database';
my $CONFIG_TMPL_KEY   = 'template.path';
my $TMPL_DIR_ADMIN    = $Config->getValue('template.dir.admin');
my $TMPL_DIR_PDF      = $Config->getValue('template.dir.pdf');
my $TMPL_DIR_SERVICE  = $Config->getValue('template.dir.service');
my $TMPL_DIR_MAIL     = $Config->getValue('template.dir.mail');
my $CGI_DIR_ADMIN     = $Config->getValue('cgi.dir.admin');
my $CGI_DIR_SERVICE   = $Config->getValue('cgi.dir.service');

my $TMPL_FORM                  = $TMPL_DIR_SERVICE . '/besucheranmeldung.html';
my $TMPL_FILE_RESPONSE         = 'service/schuelerprojekt_anmeldung_response.html';
my $TMPL_FILE_VISITOR_RESPONSE = $TMPL_DIR_SERVICE . '/message.html';

my $TMPL_MAIL_FOUNDATION       = $TMPL_DIR_MAIL . '/mail_foundation_visit.html';
my $ERRMSG;

my @SALUT		           = ('Herr','Frau');

#my $TO  = 'info@bautzner-strasse-dresden.de';
#my $TO  = 'fuehrungen@bautzner-strasse-dresden.de';
# ab 13.10.2021 keine Anmeldemails mehr ab Besucherdienst
my $TO = 'tr@totocom.de';
my $BCC = 'tr@totocom.de';

my %LOCALHOST = (
    'stasi-ede'       => 1,
    'stasi2.sokrates' => 1,
);

my $FORCE_TEST = 0;
#my $TEST       = $ENV{SERVER_NAME} eq 'www.bautzner-strasse-dresden.de' ? 0 : 1;
my $TEST       = $LOCALHOST{$ENV{SERVER_NAME}} ? 1 : 0;
$TEST        ||= $FORCE_TEST;

#my $TEST = 1;

if ( $TEST ) {
    $TO  = 'tr@totocom.de';
    $BCC = 'tr@totocom.de';
}
#### the main program ###############################
# create CGI handle
my $CGI = new CGI();

# create database instance
my $DB = new Database($Config) or die;

my $date     = new DateTimeTR;
my $edetools = new EdeTools();

#print $CGI->header();
# print $CGI->header(-charset=>'utf-8');

my %Params = {};

foreach ($CGI->param())
{
  $Params{$_} = $CGI->param($_);
  $Params{$_} =~ s/^\s*(.*\S)?\s*$/$1/;
  delete($Params{$_}) unless (length($Params{$_}));
}

$Params{'list'} = 1 unless $Params{'ref_id'};

my $Config = new ConfigFile($CONFIG_FILE);
my $TmplFile = $Config->getValue($CONFIG_TMPL_KEY);

# non html header
if ($Params{'action'} eq 'getJSONStates') {
    getJSONStates();
}

# print $CGI->header();
print $CGI->header(-charset=>'utf-8');

$Params{'aktion'} = $Params{'aktion'} || "showForm";
if ($Params{'aktion'} eq 'showForm') {
    showForm();
}
elsif ($Params{'aktion'} eq 'insertVisit') {
    insertVisit();
}
elsif ($Params{'aktion'} eq 'duplicateVisit') {
    duplicateVisit();
}
else {
    showForm();
}

#################################################################################
# Anmeldeformular öffnen
#################################################################################
sub showForm
{
    my $LOG = 0;

    $TmplFile .= ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_FORM;
    $Tmpl = new HTML::Template(filename => $TmplFile, die_on_bad_params => 0);
    
    my $CountryList = _getCountries();
    $Tmpl->param('countrylist' => $CountryList);
    
    my $StateList = _getStates();
    $Tmpl->param('statelist' => $StateList);
    
    my $InstitutionTypeList = _getInstitutionTypes();
    $Tmpl->param('institutiontypelist' => $InstitutionTypeList);
    
    my $SchoolTypeList = _getSchoolTypes();
    $Tmpl->param('schooltypelist' => $SchoolTypeList);
    
    my $VisitLangList = _getVisitLanguages();
    $Tmpl->param('visitlanglist' => $VisitLangList);
    
    #$Params{visit_offer_id} ||= 1;
    
    $Tmpl->param('visitofferlist' => _getVisitOfferList( visit_offer_visible => 1, visit_offer_public => 1, visit_offer_id => $Params{visit_offer_id}, log => $LOG ) );      
    
    my $json_visit_offers = _getJSONVisitOffers();
    $Tmpl->param('json_visit_offers' => $json_visit_offers);
    
    my $json_visit_offer_modules = _getVisitOfferModules( params => { visit_offer_module_public => 1 }, log => $LOG );
    $Tmpl->param('json_visit_offer_modules' => $json_visit_offer_modules); 
    
     
    
    my @SalutList;
    my $i=0;
    for (@SALUT) {
        my %Cols;
        $Cols{sex}   = $i;
        $Cols{salut} = $_;
        $i++;
        push @SalutList, \%Cols;
    }
    $Tmpl->param('salutlist' => \@SalutList);
    
    my $back = $_[0];
    my $err  = $_[1];

    if ($back) {
        for (keys %Params) {
            $Tmpl->param($_ => $Params{$_});

#             $ERRMSG = 'Bitte trage Deinen Referenzen ein!'       unless $Params{references};
#             $ERRMSG = 'Bitte trage Dein Unterrichtsfach ein!'   unless $Params{subject};

            $ERRMSG = 'Bitte trage Deine Klasse/Klassenstufe ein!'  unless $Params{Klasse};
            $ERRMSG = 'Bitte trage Deine Schule ein!'               unless $Params{Schule};
            $ERRMSG = 'Bitte trage Deine Telefonnummer ein!'        unless $Params{Telefon};
            $ERRMSG = 'Bitte trage Deinen Ort ein!'                 unless $Params{Ort};
            $ERRMSG = 'Bitte trage Deine Postleitzahl ein!'         unless $Params{PLZ};
            $ERRMSG = 'Bitte trage Deine Straße ein!'               unless $Params{Strasse};
            $ERRMSG = 'Bitte trage Deinen Geburtstag ein!'          unless $Params{Geburtstag};

            $ERRMSG = 'Bitte den Nachnamen angeben!'                unless $Params{visit_contact_lastname};
            $ERRMSG = 'Bitte den Vornamen angeben!'                 unless $Params{visit_contact_firstname};
        }
        $ERRMSG ||= $err;
        $Tmpl->param('errmsg' => $ERRMSG);
    }
    
    if ( $Params{visit_desc} ) {
        $Tmpl->param('visit_desc' => $Params{visit_desc});
    }
    
    return;
}

#################################################################################
# Formulardaten verarbeiten
#################################################################################
sub insertVisit()
{
    my $LOG  = 0;
    my $TEST = 0;
    my $NO_DB_ACTION   = 0;
    my $NO_MAIL_ACTION = 0; 
    
    $Params{visit_env} = Dumper(\%ENV);
    
    #if ( 1 ) {print "ENDE";exit;}
    
    if (   !$Params{visit_contact_firstname}
        || !$Params{visit_contact_lastname}  
       ) {
            showForm('back');
            return;
         }
    
    $TmplFileMail     = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_MAIL_FOUNDATION;
    $TmplMail         = new HTML::Template(filename => $TmplFileMail, die_on_bad_params => 0);
    $TmplFileResponse = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_FILE_VISITOR_RESPONSE;
    $TmplResponse     = new HTML::Template(filename => $TmplFileResponse, die_on_bad_params => 0);

    $Params{visit_desc_html}  =  $Params{visit_desc};
    $Params{visit_desc}       =~ s/([\n])//g;
    #$Params{visit_desc_html}  =~ s/([\n\f\r][\n\f\r])|[\n\f\r]/\<BR\>/sg;
    $Params{visit_date1_date} = $date->getOneNormDate($Params{date1});
    $Params{visit_date1_time} = $date->getConcatTime($Params{date1hour},$Params{date1min});
    $Params{visit_date1_datetime} = $Params{visit_date1_date} . ' ' . $Params{visit_date1_time};
    $Params{visit_date2_date} = $date->getOneNormDate($Params{date2});
    $Params{visit_date2_time} = $date->getConcatTime($Params{date2hour},$Params{date2min});
    $Params{visit_date2_datetime} = $Params{visit_date2_date} . ' ' . $Params{visit_date2_time} if $Params{visit_date2_date};
    
    $Params{date1}            = $date->getDayOfWeek($Params{visit_date1_date}) . ', ' . $Params{date1};
    $Params{date2}            = $date->getDayOfWeek($Params{visit_date2_date}) . ', ' . $Params{date2} if $Params{date2};
    
#     $Params{visit_amount}                =~ s/,/./;
#     $Params{visit_amount_gratis}       ||= 0;
        
    # User anonym
    $Params{usrnew_id} = 1;
    
    $Params{visit_prop_private} = $Params{prop_private} || 0;
    
    $Params{visit_bill_use_alternate_address}       ||= 0;
    
    # 19.12.2014: erstmal nur Führungen
    #$Params{visit_offer_id} = 1;
    
    # ab 27.09.2017 müssen die echten Angebote / Projekte eingetragen werden
    my $VISIT_OFFER = $edetools->getVisitOffer( select_params => { visit_offer_id => $Params{visit_offer_id} }, db => $DB, log => $LOG );
    my $VISIT_OFFER_NAME = $Params{visit_offer_name} = $VISIT_OFFER->[0]->{visit_offer_name};

    %VISIT_OFFER_MODULE_HASH = %{_getVisitOfferModules( 'return-type' => 'HASH' )};
    
    # Visit offer modules - zubuchbare Module
    my @VISIT_OFFER_MODULE = $CGI->param('visit_offer_module_id');
    $Params{visit_offer_module_ids} = '';
    my @ListModules;
    for ( @VISIT_OFFER_MODULE ) {
        $Params{visit_offer_module_ids} .= ( length($Params{visit_offer_module_ids}) ? ',' : '' ) . $_;
        my %Mods;
        $Mods{visit_offer_module_name} = $VISIT_OFFER_MODULE_HASH{$_};
        $Mods{visit_offer_module_name} = $edetools->encodeHTML( text => $Mods{visit_offer_module_name} );
        push @ListModules, \%Mods;
    }
    
    $TmplMail->param('listmodule' => \@ListModules );      
    
    # Prop1
    my @PROP1 = $CGI->param('prop1');
    $Params{visit_prop1} = 0;
    for (@PROP1) {
        $Params{visit_prop1} += $_;
        $TmplMail->param('prop1_' . $_ => 'ja');
        $TmplResponse->param('prop1_' . $_ => 'ja');
    }

    
    # Besucheranmeldung einfügen
    if ( !$NO_DB_ACTION ) {
        if ( $DB->insertVisit(\%Params, $LOG) ) {
            $Params{visit_id} = ($DB->getSpecialData())[0]->[0];
            $ERROR += 0;
            
            # Aktion aufzeichnen
            $DB->insertLogAction(1,1000,$Params{visit_id},0,5,1,1,'') if $ENABLE_LOGING_ACTION;
        }
        else  {
            $ERROR += 2**$ERR_POT;
            print "Es ist ein Fehler beim Speichern aufgetreten!";
            exit;
        }
    }

    $edetools->sendMail(
        lettmpl_id  => 33,
        division    => 5,
        rubric      => 'visit',
        type        => 'customer',
        visit_id    => $Params{visit_id},
        db          => $DB,
        variables   => \@TEMPLATE_VARIABLES,        
        evaluate    => 1,
        sendmail    => 1,
        to          => ( $Params{visit_contact2_mail} || $Params{visit_institution_mail} ),
        log         => $LOG,
    );

    
    $Params{salut}                    = $Params{sex} ? 'Sehr geehrte Frau' : 'Sehr geehrter Herr';
    $Params{salut2}                   = $Params{sex2} ? 'Sehr geehrte Frau' : 'Sehr geehrter Herr' if $Params{visit_contact2_lastname};
    $Params{salut3}                   = $Params{visit_bill_sex} ? 'Sehr geehrte Frau' : 'Sehr geehrter Herr' if $Params{visit_bill_lastname};
    $Params{institution_type_name}    = _getInstitutionTypes($Params{institution_type_id},'value');
    $Params{visit_institution_name} ||= '----';
    $Params{school_type_name}         = _getSchoolTypes($Params{school_type_id},'value') if $Params{school_type_id};
    $Params{school_type_name}       ||= '----';
    $Params{country_name}             = _getCountries($Params{country_id},'value') if $Params{country_id};
    $Params{state_name}               = _getStates($Params{state_id},$Params{country_id},'value') if $Params{state_id};
    $Params{state_name}             ||= '----';
    $Params{bill_country_name}        = _getCountries($Params{visit_bill_country_id},'value') if $Params{visit_bill_country_id};
    $Params{vislang_name}             = _getVisitLanguages($Params{vislang_id},'value') if $Params{vislang_id};
    
    $Params{href_edit}                = '<a href="http://' . $ENV{SERVER_NAME} . '/cgi-bin/admin_2/visit.pl?action=editVisit&visit_id=' . $Params{visit_id} . '">Besucheranmeldung bearbeiten &raquo;</a>';
    $Params{href_print}               = '<a href="http://' . $ENV{SERVER_NAME} . '/cgi-bin/admin_2/visit.pl?action=printViewVisit&visit_id=' . $Params{visit_id} . '">Druckvorschau der Besucheranmeldung &raquo;</a>';

    for (keys %Params) {
        if ( $_ ne 'href_edit' && $_ ne 'href_print' ) {        
#             $Params{$_} = encode("ISO-8859-1",decode("utf8",$Params{$_}));
#             $Params{$_} = encode_entities( $Params{$_} );
            $Params{$_} = $edetools->encodeHTML( text => $Params{$_} );               
        }
             
        if ( $_ eq 'visit_desc_html' ) {
            $Params{visit_desc_html}  =~ s/([\n\f\r][\n\f\r])|[\n\f\r]/\<BR\>/sg;    
        }
        $TmplMail->param($_ => $Params{$_});
        $TmplResponse->param($_ => $Params{$_});
    }
    
    $TmplMail->param('headline' => 'Besucheranmeldung');
    #$TmplMail->param('message'  => 'Soeben ist eine neue Besucheranmeldung eingegangen.');
    $TmplMail->param('admin'    => 1);    
    $TmplResponse->param('headline' => 'Vielen Dank');
    my $msg = 'Ihre Besucheranmeldung wurde soeben an uns versandt.<br>Die Anmeldung muss noch von uns bestätigt werden.';
    $TmplResponse->param('message'  => $msg);        
    
    #my $SUBJECT = 'Besucheranmeldung: ' . $edetools->rewriteUmlauts( text => $VISIT_OFFER_NAME );
    my $SUBJECT = 'Besucheranmeldung: ' . $VISIT_OFFER_NAME;
       $SUBJECT = $edetools->encodeMailHeader( text => $SUBJECT );

    my $html = $TmplMail->output();
    
    if ( $NO_MAIL_ACTION ) {
        #print "TO: $TO; BCC: $BCC<br>";
        print $html;
        exit;
    }

    $msg = MIME::Lite->new(
#         From     => 'info@bautzner-strasse-dresden.de',
        From     => 'fuehrungen@bautzner-strasse-dresden.de',
        To       => $TO,
        Bcc      => $BCC,
        Subject  => $SUBJECT,
        Type     => 'multipart/mixed'
    );

    $msg->attach(
        Type     => 'text/html',
        Data     => $html
    );

#     $msg->send('smtp','smtp.cms-musikschule.de',
#        AuthUser=>'cms-musikschule.de', AuthPass=>'pah6Iboo');

    $msg->send('smtp','smtp.bautzner-strasse-dresden.de',
       AuthUser=>'info@bautzner-strasse-dresden.de', AuthPass=>'uhA1997');
       
       


    $TmplResponse->param('url_redirect' => '/neu/Besucheranmeldung_Antwort.html');


    # Nutzerrückmeldung zum Ausdrucken
    print $TmplResponse->output();   
    exit; 
    return;
}

#########             H I L F S R O U T I N E N            #############

########################################################################
# Staaten auslesen
########################################################################
sub _getCountries
{
    my $COUNTRY_ID = $_[0] || undef;
    my $MODUS      = $_[1] || undef;
    my %Country = ();
    my @CountryLst = ();
    my %Params;
    $Params{country_vis} = 1;
    if ($DB->selectCountry(\%Params))
    {
    	foreach ($DB->getData())
      	{
        	my %Cols;

        	$Cols{'country_id'}      	  = $_->[0];
            
            $_->[1] = encode('utf8',$_->[1]);
            
        	$Cols{'country_name'}         = $_->[1];
        	
        	if ($MODUS eq 'value' && $COUNTRY_ID == $_->[0])
        	{
                return $Cols{'country_name'};
            }

            if ($COUNTRY_ID) {
        	   $Cols{'selected'}             = 'selected' if ($_->[0] == $COUNTRY_ID);
        	}
            else {
        	   $Cols{'selected'}             = 'selected' if ($_->[0] == 5);
        	}
        	
        	if (!$MODUS) {
        	   $Cols{res_state}                 = _getStates(undef,$Cols{'country_id'},'test');
#                 print "res: $Cols{res_state}<br>";
            }

        	$Country{$_->[0]}			  = $_->[1];

        	push(@CountryLst, \%Cols);
      }
    }
    return \@CountryLst;
}

########################################################################
# Bundesländer auslesen
########################################################################
sub _getStates
{
    my $LOG = 0;
    my $STATE_ID = $_[0] || undef;
    my %State = ();
    my @StateLst = ();
    my %Params;
    $Params{state_country_id} = $_[1] || 5;
    my $MODUS                 = $_[2] || undef;
    my $RESULT = 0;
    
    if ($DB->selectState(\%Params, $LOG))
    {
    	foreach ($DB->getData())
      	{
        	my %Cols;

        	$Cols{'state_id'}      	  = $_->[0];
        	$Cols{'state_name'}       = $_->[1];
        	
        	if ($MODUS eq 'test') {
                return 1;
            }

        	if ($MODUS eq 'value' && $STATE_ID == $_->[0])
        	{
                return $Cols{'state_name'};
            }
        	
#             if ($COUNTRY_ID) {
#         	   $Cols{'selected'}             = 'selected' if ($_->[0] == $COUNTRY_ID);
#         	}
#             else {
#         	   $Cols{'selected'}             = 'selected' if ($_->[0] == 5);
#         	}

#         	$Country{$_->[0]}			  = $_->[1];

        	push(@StateLst, \%Cols);
      }
    }
	if ($MODUS eq 'test') {
        return $RESULT;
    }
    else {
        return \@StateLst;
    }
}

########################################################################
# Institutionsart auslesen
########################################################################
sub _getInstitutionTypes
{
    my $LOG = 0;
    my $INSTITUTION_TYPE_ID = $_[0] || undef;
    my $MODUS               = $_[1] || undef;
    my @InstitutionTypeLst  = ();
    my %INSTITUTION_TYPE;
    my %Params;
    if ($DB->selectInstitutionType(\%Params, $LOG))
    {
    	foreach ($DB->getData())
      	{
        	my %Cols;

        	$Cols{'institution_type_id'}      	  = $_->[0];
        	$Cols{'institution_type_name'}        = $_->[1];
        	
        	print "$MODUS und $INSTITUTION_TYPE_ID<br>" if $LOG;
        	
        	if ($MODUS eq 'value' && $INSTITUTION_TYPE_ID == $_->[0])
        	{
                return $Cols{'institution_type_name'};
            }

        	push(@InstitutionTypeLst, \%Cols);
        	
        	$INSTITUTION_TYPE{$_->[0]}            = $_->[1];
      }
    }
    
    if ($MODUS eq 'hash') {
        return \%INSTITUTION_TYPE;
    }
    elsif ($MODUS eq 'value') {
        return 'Privatgruppe';
    }
    else {
        return \@InstitutionTypeLst;
    }
}

########################################################################
# Schulart auslesen
########################################################################
sub _getSchoolTypes
{
    my $LOG = 0;
    my $SCHOOL_TYPE_ID = $_[0] || undef;
    my $MODUS          = $_[1] || undef;
    my @SchoolTypeLst  = ();
    my %Params;
    if ($DB->selectSchoolType(\%Params, $LOG))
    {
    	foreach ($DB->getData())
      	{
        	my %Cols;

        	$Cols{'school_type_id'}      	   = $_->[0];
        	$Cols{'school_type_name'}          = $_->[1];
            $Cols{'school_type_class_degree'}  = $_->[3];
        	
        	if ($MODUS eq 'value' && $SCHOOL_TYPE_ID == $_->[0])
        	{
                return $Cols{'school_type_name'};
            }

        	push(@SchoolTypeLst, \%Cols);
      }
    }
    return \@SchoolTypeLst;
}

########################################################################
# Sprache auslesen
########################################################################
sub _getVisitLanguages
{
    my $LOG = 0;
    my $VISLANG_ID = $_[0] || undef;
    my $MODUS      = $_[1] || undef;
    my @VisitLangLst = ();
    my %Params;
    if ( $DB->selectVisitLanguage(\%Params, $LOG, 1) ) {
    	foreach ($DB->getData())  {
        	my %Cols = %{$_};
            
            next unless $Cols{vislang_visible};

#         	$Cols{'vislang_id'}      	  = $_->[0];
#         	$Cols{'vislang_name'}         = $_->[1];
        	
        	if ( $MODUS eq 'value' && $VISLANG_ID == $Cols{vislang_id} )  {
                return $Cols{vislang_name};
            }
        	
        	if ( !$VISLANG_ID ) {
                $Cols{selected}          = 'selected' if ( $Cols{vislang_id} == 1 );
            }

        	push(@VisitLangLst, \%Cols);
      }
    }
    return \@VisitLangLst;
}

########################################################################
# Angebote / Projekte auslesen
########################################################################
sub _getVisitOffers
{
    my $LOG = 0;
    my $VISIT_OFFER_ID = $_[0] || undef;
    my $MODUS          = $_[1] || undef;
    my @List = ();
    my %VISIT_OFFER;
    
    if ($DB->selectVisitOffer(\%Params, $LOG, 1))  {
    	foreach ($DB->getData())  {
        	my %Cols = %{$_};

        	$Cols{selected}             = 'selected' if ( $VISIT_OFFER_ID == $_->{visit_offer_id} );
      	            
            $VISIT_OFFER{$Cols{visit_offer_id}} = $Cols{visit_offer_name};

        	push(@List, \%Cols);
        }
    }
    
    if ($MODUS eq 'hash') {
        return \%VISIT_OFFER;
    }
    else {
        return \@List;
    }    
}

########################################################################
# Angebote auslesen
########################################################################
sub _getVisitOfferList
{
    my %arg = (
        log     => 0,
        @_
    );

    my $LOG          = $arg{log} || 0;
    
    print "<h2>sub _getVisitOfferList</h2>" if $LOG;
    
    my @List = ();
    my $Pars;
    $Pars{'order-by'}             = $arg{'order-by'} || 'visit_offer_name';
    $Pars{modus}                  = 'select-visit-offer-and-asgt';
    $Pars{visit_offer_module_id}  = $arg{visit_offer_module_id};    
    if ( exists($arg{visit_offer_visible}) ) {
        $Pars{visit_offer_visible}    = $arg{visit_offer_visible};
    }
    if ( exists($arg{visit_offer_public}) ) {
        $Pars{visit_offer_public}    = $arg{visit_offer_public};
    }
    if ($DB->selectVisitOffer(\%Pars, $LOG, 1))  {
    	foreach ($DB->getData())  {
        	my %Cols = %{$_};
            $Cols{visit_offer_price_de}           = _trimNumber(( $Cols{visit_offer_price} || 0 ));
            $Cols{visit_offer_price_min_de}       = _trimNumber($Cols{visit_offer_price_min})      if ( $Cols{visit_offer_price_min} > 0 );
            $Cols{visit_offer_price_lang_add_de}  = _trimNumber($Cols{visit_offer_price_lang_add}) if ( $Cols{visit_offer_price_lang_add} > 0  );

            $Cols{'tr-class'}            = $Cols{visit_offer_visible} ? 'offer-visible' : 'offer-not-visible';
            
            $Cols{attributes}            = '';
            $Cols{attributes}           .= ' data-h1="' . $Cols{visit_offer_name} . '"';
            $Cols{attributes}           .= ' data-notes="' . $Cols{visit_offer_notes} . '"';
            $Cols{attributes}           .= ' data-notes-intern="' . $Cols{visit_offer_notes_intern} . '"';
            $Cols{attributes}           .= ' data-desc="' . $Cols{visit_offer_desc} . '"';
            $Cols{attributes}           .= ' data-duration="' . $Cols{visit_offer_duration} . '"';
            $Cols{attributes}           .= ' title="Test"';
            
            $Cols{label}                 = '<label for="visit_offer_id_' . $Cols{visit_offer_id} . '" ' . $Cols{attributes} . '>' . $Cols{visit_offer_name} . '</label>';
            my $checked                  = $Pars{visit_offer_module_id} == $Cols{vovoma_visit_offer_module_id} ? 'checked="checked"' : '';
            $Cols{cb_visoffer}           = '<input type="checkbox" name="visit_offer_id" id="visit_offer_id_' . $Cols{visit_offer_id} . '" value="' . $Cols{visit_offer_id} . '" title="Angebot: ' . $Cols{visit_offer_name} . '" ' . $checked . ' />';
            
            $Cols{costs}  = ( $Cols{visit_offer_price_unit} == 1 ? 'Teilnehmerpreis: ' : 'Gruppenpreis: ' ) . $Cols{visit_offer_price_de} . ' EUR';
            $Cols{costs} .= $Cols{visit_offer_price_min_de} ? '<br>Mindestpreis: ' . $Cols{visit_offer_price_min_de} . ' EUR' : '';
            $Cols{costs} .= $Cols{visit_offer_price_lang_add_de} ? '<br>Fremdsprachenzuschlag: ' . $Cols{visit_offer_price_lang_add_de} . ' EUR' : '';
            
        	$Cols{selected}              = 'selected' if ( $arg{visit_offer_id} == $Cols{visit_offer_id} ); 
                        
        	push(@List, \%Cols);
        }
    }

    return \@List;
}

#########################################################################
# Bundesländer, Kantone etc. auslesen
#########################################################################
sub getJSONStates
{
    my $LOG = 0;
    print $CGI->header('application/json');

    my @List;
    if ($DB->selectState(\%Params,$LOG))
    {
        foreach ($DB->getData())
        {
            my %Cols;
            $Cols{state_id}     = $_->[0];
            $Cols{state_name}   = $_->[1];
            $Cols{state_name}   = encode("ISO-8859-1",decode("utf8",$Cols{state_name}));

            push @List, \%Cols;
        }
    }

	my $obj = \@List;
    my $js = JSON->new->allow_blessed->convert_blessed->encode( $obj );
    print $js;
    exit;
}

#########################################################################
# Angebote / Projekte als JSON-Objekt zurückgeben
#########################################################################
sub _getJSONVisitOffers
{
    my %arg = (
        log     => 0,
        @_
    );
    
    my $LOG = $arg{log} || 0;

    my @List;
    my $Pars;
    if ( exists($arg{visit_offer_visible}) ) {
        $Pars{visit_offer_visible}    = $arg{visit_offer_visible};
    }
    if ( exists($arg{visit_offer_public}) ) {
        $Pars{visit_offer_public}    = $arg{visit_offer_public};
    }
    if ($DB->selectVisitOffer(\%Pars, $LOG, 1))  {
    	foreach ($DB->getData())  {
        	my %Cols = %{$_};
#             $Cols{visit_offer_price_de} = _trimNumber($Cols{visit_offer_price});
#             $Cols{visit_offer_price_min_de}       = _trimNumber($Cols{visit_offer_price_min})      if ( $Cols{visit_offer_price_min} > 0 );
#             $Cols{visit_offer_price_lang_add_de}  = _trimNumber($Cols{visit_offer_price_lang_add}) if ( $Cols{visit_offer_price_lang_add} > 0  );
            
            my %VISIT_OFFER_PRICE = %{$edetools->getVisitOfferPrice( visit_offer_id => $Cols{visit_offer_id}, 'select-mode' => 'only-active', 'return-type' => 'HASH', db => $DB, log => $LOG )};
            #print %VISIT_OFFER_PRICE,"<br><br>";            
            if ( %VISIT_OFFER_PRICE ) {
                $Cols{visit_offer_price_de}            = _trimNumber($VISIT_OFFER_PRICE{visit_offer_price_price});
                $Cols{visit_offer_price_min_de}        = _trimNumber($VISIT_OFFER_PRICE{visit_offer_price_price_min})      if ( $VISIT_OFFER_PRICE{visit_offer_price_price_min} > 0 );
                $Cols{visit_offer_price_lang_add_de}   = _trimNumber($VISIT_OFFER_PRICE{visit_offer_price_price_lang_add}) if ( $VISIT_OFFER_PRICE{visit_offer_price_price_lang_add} > 0  );            
                $Cols{visit_offer_price_partner_de}    = _trimNumber($VISIT_OFFER_PRICE{visit_offer_price_price_partner})  if ( $VISIT_OFFER_PRICE{visit_offer_price_price_partner} > 0  );
                $Cols{visit_offer_price_by_agreement}  = $VISIT_OFFER_PRICE{visit_offer_price_by_agreement};
                $Cols{visit_offer_price_unit}          = $VISIT_OFFER_PRICE{visit_offer_price_price_unit};
                $Cols{visit_offer_price_partner_name}  = $VISIT_OFFER_PRICE{visit_offer_price_price_partner_name};
                $Cols{visit_offer_price_partner_unit}  = $VISIT_OFFER_PRICE{visit_offer_price_price_partner_unit};
                $Cols{visit_offer_price_valid_from}    = $VISIT_OFFER_PRICE{visit_offer_price_valid_from};
                $Cols{visit_offer_price_valid_from_de} = $date->getOneDateTime($Cols{visit_offer_price_valid_from});
            }
            else {
                $Cols{visit_offer_price_de}            = _trimNumber($Cols{visit_offer_price});
                $Cols{visit_offer_price_min_de}        = _trimNumber($Cols{visit_offer_price_min})      if ( $Cols{visit_offer_price_min} > 0 );
                $Cols{visit_offer_price_lang_add_de}   = _trimNumber($Cols{visit_offer_price_lang_add}) if ( $Cols{visit_offer_price_lang_add} > 0  );            
                $Cols{visit_offer_price_partner_de}    = _trimNumber($Cols{visit_offer_price_partner})  if ( $Cols{visit_offer_price_partner} > 0  );
            }            
                        
        	push(@List, \%Cols);
        }
    }

	my $obj = \@List;
    my $js = JSON->new->allow_blessed->convert_blessed->encode( $obj );
    return $js;
    exit;
}

#########################################################################
# Angebot- und Projektmodule als JSON-Objekt zurückgeben
#########################################################################
sub _getVisitOfferModules
{
    my %arg = (
        log     => 0,
        @_
    );
    
    my $LOG = $arg{log} || 0;

    my @List;
    my %Params = %{$arg{params}};
    my %VISIT_OFFER_MODULE;
    if ($DB->selectVisitOfferModule(\%Params, $LOG, 1))  {
    	foreach ($DB->getData())  {
        	my %Cols = %{$_};
            $Cols{visit_offer_module_price_de}           = _trimNumber($Cols{visit_offer_module_price});
            $Cols{visit_offer_module_price_min_de}       = _trimNumber($Cols{visit_offer_module_price_min})      if ( $Cols{visit_offer_module_price_min} > 0 );
            $Cols{visit_offer_module_price_lang_add_de}  = _trimNumber($Cols{visit_offer_module_price_lang_add}) if ( $Cols{visit_offer_module_price_lang_add} > 0  );
            
            $VISIT_OFFER_MODULE{$Cols{visit_offer_module_id}} = $Cols{visit_offer_module_name};
            
            my %Vals;
            $Vals{modus}                 = 'select-visit-offer-module-asgt';
            $Vals{visit_offer_module_id} = $Cols{visit_offer_module_id};
            if ($DB->selectVisitOfferModule(\%Vals, $LOG, 1))  {
            	foreach ($DB->getData())  {
                    $Cols{visit_offer_id} .= ( length($Cols{visit_offer_id}) ? ',' : '' ) . $_->{vovoma_visit_offer_id}; 
                }
            }            
                        
        	push(@List, \%Cols);
        }
    }
    
    if ( $arg{'return-type'} eq 'LIST' ) {
        return \@List;
    }
    elsif ( $arg{'return-type'} eq 'HASH' ) {
        return \%VISIT_OFFER_MODULE;
    }    
    else {
    	my $obj = \@List;
        my $js = JSON->new->allow_blessed->convert_blessed->encode( $obj );
        return $js;
    }    

# 	my $obj = \@List;
#     my $js = JSON->new->allow_blessed->convert_blessed->encode( $obj );
#     return $js;
#     exit;
}

#################################################################################
# UmschreibUmlaute
#################################################################################
sub _trimNumber
{
    my $NUMBER = $_[0];
    $NUMBER   = sprintf "%.2f",$NUMBER;
    $NUMBER   =~ s/\./,/;
    return $NUMBER;                    
}

# print out
print $Tmpl->output();
exit;