#!/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 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';

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_OFFER_DESCRIPTIONS    = $TMPL_DIR_SERVICE . '/visit_offer_descriptions.html';

# my $TMPL_EVENT_LIST            = $TMPL_DIR_SERVICE . '/event_list.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;


#### 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');

my $Tmpl;

$Params{'action'} = $Params{'action'} || "showOfferDescriptions";
if ($Params{'action'} eq 'showOfferDescriptions') {
    $Tmpl = showOfferDescriptions();
}
else {
    $Tmpl = showOfferDescriptions();
}

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


#################################################################################
# Beschreibungen von Angeboten
#################################################################################
sub showOfferDescriptions
{
    my $LOG = 0;

    $TmplFile .= ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_OFFER_DESCRIPTIONS;
    my $Tmpl = new HTML::Template(filename => $TmplFile, die_on_bad_params => 0);
    
    my %Pars;
    $Pars{visit_offer_type}        = $Params{type} || 1; 
    $Pars{visit_offer_desc_public} = 1;
    
    $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};    
#     $Pars{visit_offer_visible}    = $arg{visit_offer_visible};

    my @List;
    if ($DB->selectVisitOffer(\%Pars, $LOG, 1))  {
    	foreach ($DB->getData())  {
        	my %Cols = %{$_};
            
            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 ) {
                print "geänderte Preise vorhanden<br>" if $LOG;
                $Cols{visit_offer_price}               = $VISIT_OFFER_PRICE{visit_offer_price_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});
            }            
            
            if ( $Cols{visit_offer_attendance_min} && $Cols{visit_offer_attendance_max} ) {
                $Cols{member} = "$Cols{visit_offer_attendance_min}-$Cols{visit_offer_attendance_max} Personen";            
            }
            elsif ( $Cols{visit_offer_attendance_min} ) {
                $Cols{member} = "ab $Cols{visit_offer_attendance_min} Personen";            
            }
            elsif ( $Cols{visit_offer_attendance_max} ) {
                $Cols{member} = "bis $Cols{visit_offer_attendance_max} Personen";            
            } 

            if ( $Cols{visit_offer_price_by_agreement} ) {
                $Cols{visit_offer_price}           = 'Preis: nach Absprache';            
            }
            elsif ( $Cols{visit_offer_price} ) {
                $Cols{visit_offer_price_unit_name} = $Cols{visit_offer_price_unit} == 1 ? 'Preis pro Teilnehmer: ' : 'Preis pro Gruppe: ';
                $Cols{visit_offer_price}           =~ s/\./,/;
                $Cols{visit_offer_price}          .= ' €';            
            }
            elsif ( $Cols{visit_offer_price} == 0 ) {
                $Cols{visit_offer_price}           = 'Das Angebot ist kostenlos';            
            }
            
            if ( $Cols{visit_offer_price_min}) {
                $Cols{visit_offer_price_min}      =~ s/\./,/;            
            }
            
            if ( $Cols{visit_offer_price_lang_add}) {
                $Cols{visit_offer_price_lang_add}      =~ s/\./,/;            
            }
            
            # Dokumente auslesen
            my @FileList;
            if ( $DB->selectVisitOfferFile( { visit_offer_id => $Cols{visit_offer_id} },$LOG, 1) ) {
                foreach ($DB->getData())
                {
                    my %Cols;
        
                    $Cols{vo_id}             = $_->{visit_offer_file_id};
                    $Cols{vo_title}          = $_->{visit_offer_file_title} || $_->{visit_offer_file_filename};
        
                    $Cols{fs}                   = $_->{visit_offer_file_filesize};
                    if ($Cols{fs} > 1000000) {
                        $Cols{fs} = $Cols{fs} / 1048576;
                        $Cols{fs} = sprintf("%.3f", $Cols{fs});
                        $Cols{fs} .= ' MB';
                    }
                    elsif ($Cols{fs} > 1000) {
                        $Cols{fs} = $Cols{fs} / 1024;
                        $Cols{fs} = sprintf("%.0f", $Cols{fs});
                        $Cols{fs} .= ' KB';
                    }
                    else { $Cols{fs} = $Cols{fs} . ' B';}
        
                    $Cols{vo_filename_prep}  = "$_->{visit_offer_file_filename_prep} ($Cols{fs})";
                    $Cols{vo_filename_prep}  = "<a href='/Documents/Files/VISIT_OFFER_FILES/$Cols{vo_id}/$_->{visit_offer_file_filename}' title='Download: $Cols{vo_filename_prep}' target='_blank'>$Cols{vo_title} &raquo;</a>";
        
                    push @FileList, \%Cols;
                }
            }
            
            $Cols{visit_offer_files} = \@FileList;

            push @List, \%Cols;
        }
    }
    $Tmpl->param( 'list' => \@List );
    
    return $Tmpl;
    
    my %Pars;
    
    if ( !$Params{year} ) {
        $Pars{headline} = 'Aktuelle Veranstaltungen';
        $Params{modus}  = 'currently';
    }
    
    my @List;
    $Params{ev_visible} = 1;
    $Params{ev_public}  = 1;
    if ( $DB->selectEvent(\%Params, $LOG, 1))   {
        foreach ($DB->getData())  {
            $I++;
            my %Cols = %{$_};
            #$Cols{ev_desc}  =~ s/([\n\f\r][\n\f\r])|[\n\f\r]/\<BR\>/sg;

 		    #$date = new DateTimeTR();
 		    $Cols{ev_date}    = $date->getTwoDateTimes($Cols{ev_start_date}, $Cols{ev_start_time}, $Cols{ev_end_date}, $Cols{ev_end_time},1, 'Uhr');
            if ( !$Cols{ev_end_date} || ( $Cols{ev_end_date} ne $Cols{ev_start_date} ) ) {
               $Cols{ev_date} = $date->getDayOfWeek($Cols{ev_start_date}) . ', ' . $Cols{ev_date}; 
            }
            $Cols{etype_name} = $EVENT_TYPE{$Cols{ev_etype_id}};
            
            push @List, \%Cols;
        }
    }
    
    $Tmpl->param('eventlist' => \@List );    
    
    for ( keys %Pars ) {
        $Tmpl->param($_ => $Pars{$_});
    }
    
    return;
}


#################################################################################
# Veranstaltungsliste
#################################################################################
sub showEventList
{
    my $LOG = 0;

    $TmplFile .= ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_EVENT_LIST;
    $Tmpl = new HTML::Template(filename => $TmplFile, die_on_bad_params => 0);
    
    my %Pars;
    
    if ( !$Params{year} ) {
        $Pars{headline} = 'Aktuelle Veranstaltungen';
        $Params{modus}  = 'currently';
    }
    
    my @List;
    $Params{ev_visible} = 1;
    $Params{ev_public}  = 1;
    if ( $DB->selectEvent(\%Params, $LOG, 1))   {
        foreach ($DB->getData())  {
            $I++;
            my %Cols = %{$_};
            #$Cols{ev_desc}  =~ s/([\n\f\r][\n\f\r])|[\n\f\r]/\<BR\>/sg;

 		    #$date = new DateTimeTR();
 		    $Cols{ev_date}    = $date->getTwoDateTimes($Cols{ev_start_date}, $Cols{ev_start_time}, $Cols{ev_end_date}, $Cols{ev_end_time},1, 'Uhr');
            if ( !$Cols{ev_end_date} || ( $Cols{ev_end_date} ne $Cols{ev_start_date} ) ) {
               $Cols{ev_date} = $date->getDayOfWeek($Cols{ev_start_date}) . ', ' . $Cols{ev_date}; 
            }
            $Cols{etype_name} = $EVENT_TYPE{$Cols{ev_etype_id}};
            
            push @List, \%Cols;
        }
    }
    
    $Tmpl->param('eventlist' => \@List );    
    
    for ( keys %Pars ) {
        $Tmpl->param($_ => $Pars{$_});
    }
    
    return;
}

#################################################################################
# Formulardaten verarbeiten
#################################################################################
sub insertVisit()
{
    my $LOG = 0;
    
    $Params{visit_env} = Dumper(\%ENV);
    
    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;
    
    # 19.12.2014: erstmal nur Führungen
    $Params{visit_offer_id} = 1;
    
    # 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 ($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;
    }
    
    $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{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{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) {
        $Params{$_} =~ s/ß/\&szlig\;/g;
        $Params{$_} =~ s/ä/\&auml\;/g;
        $Params{$_} =~ s/ö/\&ouml\;/g;
        $Params{$_} =~ s/ü/\&uuml\;/g;
        $Params{$_} =~ s/Ä/\&Auml\;/g;
        $Params{$_} =~ s/Ö/\&Ouml\;/g;
        $Params{$_} =~ s/Ü/\&Uuml\;/g;
        
        $Params{$_} =~ s/é/\&eacute\;/g;
        $Params{$_} =~ s/€/\&euro\;/g;

        $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 $html = $TmplMail->output();
#     print $html;
#     exit;

    $msg = MIME::Lite->new(
#         From     => 'info@bautzner-strasse-dresden.de',
        From     => 'fuehrungen@bautzner-strasse-dresden.de',
        To       => $TO,
        Bcc      => $BCC,
        Subject  => 'Besucheranmeldung!',
        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];
        	$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))
    {
    	foreach ($DB->getData())
      	{
        	my %Cols;

        	$Cols{'vislang_id'}      	  = $_->[0];
        	$Cols{'vislang_name'}         = $_->[1];
        	
        	if ($MODUS eq 'value' && $VISLANG_ID == $_->[0])
        	{
                return $Cols{'vislang_name'};
            }
        	
        	if (!$VISLANG_ID) {
                $Cols{selected}          = 'selected' if ($_->[0] == 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;
    }    
}

#########################################################################
# 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 $LOG = 0;

    my @List;
    if ($DB->selectVisitOffer(\%Params, $LOG, 1))  {
    	foreach ($DB->getData())  {
        	my %Cols = %{$_};
            
            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  );
            }                
            
#             $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  );            

        	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 _getJSONVisitOfferModules
{
    my $LOG = 0;

    my @List;
    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  );
            
            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);
        }
    }

	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;                    
}

exit;