#!/usr/bin/perl

# use strict;
# use warnings;

# use utf8;

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

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

# use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
# use CGI::Cookie;
use HTML::Template;
use ConfigFile;
use File::Path;                             ### !
use Encode;                                 ### !
use CGI::Simple;                            ### !
use JSON -convert_blessed_universally;      ### !
use POSIX qw(ceil);

use Database;
use DatabaseMMS;
use DateTimeTR;
use UserDepend;
use GetList;

my ($UsrId,$DepId,$UgrpId,$UsrName,$Appendix) ;
my $CONFIG_FILE = '../../config/project.conf';
my $Config = new ConfigFile($CONFIG_FILE);

my $DATABASE               = 'database';

my $CONFIG_TMPL_KEY        = 'template.path';
my $TMPL_DIR_MMS_PORTAL    = $Config->getValue('template.dir.mms.portal');
my $CGI_DIR_ADMIN          = $Config->getValue('cgi.dir.admin');

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

my $TMPL_FILE    		   = $TMPL_DIR_ADMIN . '/login.html';
my $TMPL_MESSAGE   		   = $TMPL_DIR_ADMIN . '/message.html';

my $EXTENSIONS = "\.gif\.jpg\.jpeg\.png\.";

my $TMPL_MMS_INDEX           = $TMPL_DIR_MMS_PORTAL . '/mms_portal_index.html';
my $TMPL_MMS_ERROR           = $TMPL_DIR_MMS_PORTAL . '/mms_portal_error.html';
my $TMPL_MMS_MAP_PRISONS     = $TMPL_DIR_MMS_PORTAL . '/mms_portal_kg_map_prisons.html';
my $TMPL_MMS_MAP_PRISONS_2   = $TMPL_DIR_MMS_PORTAL . '/mms_portal_kg_map_prisons_2.html';
#my $TMPL_MMS_MAP_PRISONS_3   = $TMPL_DIR_MMS_PORTAL . '/mms_portal_kg_map_prisons.osm.html';
my $TMPL_MMS_MAP_PRISONS_3   = $TMPL_DIR_MMS_PORTAL . '/mms_portal_kg_map_prisons.osm.start.html';
my $TMPL_MMS_PRESENTATION    = $TMPL_DIR_MMS_PORTAL . '/mms_portal_presentation.html';

my $TMPL_MMS_DEFAULT         = $TMPL_DIR_MMS_PORTAL . '/mms_presentation.responsive-1.html';

my %CONFIG = (  
    template_path => {
        default => 'portal_mms',
        tablet  => 'portal_mms/tablet'
    },
    template => {
        start => {
            default => 'mms_portal_presentation.html',
            #default => 'mms_portal_presentation.html',
        }
    },
    template_station => {
#         1 => {
#             tablet => 'mms_portal_index.tablet.html',
#         },
#         2 => {
#             tablet => 'mms_portal_index.tablet.html',
#         },
#         3 => {
#             tablet => 'mms_portal_index.tablet.html',
#         },
#         5 => {
#             tablet => 'mms_portal_index.tablet.html',
#         },
#         6 => {
#             tablet => 'mms_portal_index.tablet.html',
#         },
#         7 => {
#             tablet => 'mms_portal_index.tablet.html',
#         },                
#         13 => {
#             tablet => 'mms_portal_index.tablet.html',
#         },  
    },
    template_station_default_by_theme => {
        tablet => 'mms_portal_index.tablet.html',
    },
    video_path => '/mms/movies/movie',
    video_poster_path => '/mms/movies/movie-thumbs',
);

my %JSON_PARAMS = (
    PRESENTATION => {
        mms_presentation_id              => 1,
        mms_presentation_title           => 1,
        mms_presentation_public_title    => 1,
        mms_presentation_public_subtitle => 1,
        mms_presentation_link_title      => 1,
        mms_presentation_notes           => 1,
        mms_presentation_desc            => 1,
        mms_presentation_url_params      => 1,
        mms_presentation_template        => 1,
        mms_presentation_autoplay_videochain  => 1,
        mms_presentation_show_title_before_video => 1, 
        mms_presentation_no_intro_screen => 1,       
    },
    PROJECT => {
        mms_project_id           => 1,
        mms_project_title        => 1,
        mms_project_public_title => 1,        
        mms_project_link_title   => 1,
        mms_project_notes        => 1,
        mms_project_desc         => 1,
        mms_project_imprint      => 1,
        mms_project_color_bg     => 1,
        mms_project_color_font   => 1,
        mms_project_template     => 1,
    },
    STATION => {
        mms_station_id           => 1,
        mms_station_project_id   => 1,        
        mms_station_title        => 1,
        mms_station_link_title   => 1,
        mms_station_notes        => 1,
        mms_station_desc         => 1,
        mms_station_content_type => 1,
    },
    CS_PRISON => {
        cs_prison_id             => 1,
        cs_prison_name           => 1,
        cs_prison_name_synonym   => 1,
        cs_prison_desc           => 1,
        cs_prison_coord_lat      => 1,
        cs_prison_coord_long     => 1,
        cs_prison_city           => 1,
    },             
);

my $TEST = 0;
#### the main program ################################
my %Params = {};

# create config modul instance
my $Config = new ConfigFile($CONFIG_FILE);

my $DEBUG = 1;
my $FATAL = 1;
my $CGI = new CGI::Simple();

# create database instance
my $DBMMS = new DatabaseMMS($Config) or die;

my $date = new DateTimeTR();

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

# XML
if ( $Params{action} eq 'loadXMLPrisons') {
	loadXMLPrisons();
}
# JSON
elsif ($Params{'action'} eq 'loadJSONPresentationData') {
    loadJSONPresentationData();
}


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

# Programmweiche
my $Tmpl;

$Params{action} = $Params{action} || "startNewPresentation";
# Personen
# if ( $Params{action} eq 'showIndex' || $Params{mms_station_id} ) {
# 	$Tmpl = showIndex();
# }
if ( $Params{action} eq 'showIndex' ) {
	$Tmpl = showIndex();
}
elsif ( $Params{action} eq 'showMapPrisons') {
	$Tmpl = showMapPrisons();
}
elsif ( $Params{action} eq 'startPresentation') {
	$Tmpl = startPresentation();
}
elsif ( $Params{action} eq 'startNewPresentation') {
	$Tmpl = startNewPresentation();
}
elsif ( $Params{action} eq 'testItem') {
	$Tmpl = testItem();
}
else {
	$Tmpl = showIndex();
}


($sek, $min, $stu, $tag, $mon, $jahr) = localtime(time);
$jahr = $jahr+1900;

@Monate = ('Januar', 'Februar', 'März', 'April', 'Mai', 'Juni', 'Juli','August','September', 'Oktober', 'November', 'Dezember');
$Datum = $tag . '. ' . $Monate[$mon] . ' ' . $jahr;
$Tmpl->param('Datum'    		=> $Datum);

$Tmpl->param('standard_title'   => $STANDARD_TITLE);
$Tmpl->param('version'	        => 'Version ' . $VERSION);
$Tmpl->param('menu'				=> $User{'menu'});
$Tmpl->param('usrnew_name'		=> $User{'usrnew_name'});
$Tmpl->param('usrgrp_name'	    => $User{'usrgrp_name'});
$Tmpl->param('usrnew_type_name'	=> $User{'usrnew_type_name'});
$Tmpl->param('loggeduser'	    => $User{loggeduser});
print $Tmpl->output();
exit();


#########################################################################
# Neue Meta-Startseite der MM-Station aufrufen
#########################################################################
sub startNewPresentation
{
    my $LOG = 0;
    
    if ( ( !$Params{mms_presentation_id} && !$Params{device_id} && !$Params{mms_person_id} ) || !$Params{code} ) {
        return showErrorPage();
        exit; 
    }
    
    if ( $Params{device_id} ) {
        $DBMMS->insertMMSLog( { 'ip_address' => $ENV{REMOTE_ADDR}, 'device_id' => $Params{device_id}, mms_log_type => 1 } , $LOG);
        my %Vals;
        $Vals{special_select} = ' *, PASSWORD(mms_presentation_id) AS CODE FROM MMS_STATION_PHYSICAL LEFT JOIN MMS_PRESENTATION ON mms_presentation_id = mms_station_physical_presentation_id WHERE mms_station_physical_id = ' . $Params{device_id};
        if ( $DBMMS->specialSelect(\%Vals,$LOG, 1))  {
            foreach ($DBMMS->getData())  {
                my %Cols = %{$_};
                if ( $Cols{CODE} ) {
                    $Params{mms_presentation_id} = $Cols{mms_presentation_id};
                    $Params{code}                = $Cols{CODE};
                    last;    
                }
            }
        }  
    }
    else {
        $DBMMS->insertMMSLog( { 'ip_address' => $ENV{REMOTE_ADDR}, mms_log_type => 3 } , $LOG);    
    }

    if ( $Params{mms_person_id} ) {
        my $SELECT_STMT  = ' mms_presentation_id, PASSWORD(mms_presentation_id) AS CODE FROM MMS_PERSON_MMS_STATION_ASGT, MMS_PRESENTATION_MMS_STATION_ASGT, MMS_PRESENTATION ';
           $SELECT_STMT .= ' WHERE mpmsa_person_id = ' . $Params{mms_person_id} . ' AND PASSWORD("' . $Params{mms_person_id} . '") = "' . $Params{code} . '"';
           $SELECT_STMT .= ' AND MMS_PRESENTATION_MMS_STATION_ASGT.mpmsa_station_id = MMS_PERSON_MMS_STATION_ASGT.mpmsa_station_id ';
           $SELECT_STMT .= ' AND mms_presentation_id = MMS_PRESENTATION_MMS_STATION_ASGT.mpmsa_presentation_id ';
           $SELECT_STMT .= ' GROUP BY mpmsa_person_id ';

        if ( $DBMMS->specialSelect( { special_select => $SELECT_STMT } , $LOG, 1) )  {
            foreach ( $DBMMS->getData() )  {
                $Params{mms_presentation_id} = $_->{mms_presentation_id};
                $Params{code}                = $_->{CODE};
                last;
            }
        }
    }
    
    my %Pars;
    my %Cols;
    # Presentation 
    $Cols{presentation}    = _getPresentation( mode => 'hash', 'return-params' => 'config' , mms_presentation_id => $Params{mms_presentation_id}, code => $Params{code}, log => $LOG );
    
    # Projekte
    my @ProjectList;
    $Pars{special_select} = ' DISTINCT(mms_station_project_id), MMS_PROJECT.* FROM MMS_STATION, MMS_PRESENTATION_MMS_STATION_ASGT, MMS_PROJECT WHERE mms_station_id = mpmsa_station_id AND mpmsa_presentation_id = ' . $Params{mms_presentation_id} . ' AND mms_project_id = mms_station_project_id ORDER BY mms_project_sort';
    if ( $DBMMS->specialSelect(\%Pars, $LOG, 1) )  {
        foreach ( $DBMMS->getData() )   {
            my %Cols;

            for my $k ( keys %{$_} ) {
                $Cols{$k} = $_->{$k} if $JSON_PARAMS{PROJECT}->{$k};
            }

            if ( $LOG ) {for (sort keys %Cols){print "$_ : $Cols{$_}<br>";}}

            push(@ProjectList, \%Cols);
        }
    }

    $Cols{project}   = \@ProjectList;
    if ( $Params{mms_person_id} ) {
        $Cols{station}   = _getStations( 'with-single-person' => true, mms_person_id => $Params{mms_person_id}, 'order-by' => 'mms_station_project_id, mpmsa_sort', 'return-params' => 'config', select_mode => 'by-presentation-id', mms_presentation_id => $Params{mms_presentation_id}, log => $LOG );
    }
    else {
        $Cols{station}   = _getStations( 'with-person' => true, 'order-by' => 'mms_station_project_id, mpmsa_sort', 'return-params' => 'config', select_mode => 'by-presentation-id', mms_presentation_id => $Params{mms_presentation_id}, log => $LOG );
    }
    $Cols{glossary}  = _getGlossary( mode => 'hash', log => $LOG );

    my $TEMPLATE = _getPresentationTemplate( presentation => $Cols{presentation}, log => $LOG);
    #print  $TEMPLATE;
    my $Tmpl = new HTML::Template(filename => $TEMPLATE, die_on_bad_params => 0);

	my $obj = \%Cols;
    my $js = JSON->new->allow_blessed->convert_blessed->encode( $obj );
    
    #print $js; exit;
        
    $Tmpl->param('json_data' => $js); 
    
    #$Tmpl->param('project_title'        => $Cols{station}->{mms_project_title} );   
    $Tmpl->param('video_path'           => $CONFIG{video_path} );
    $Tmpl->param('video_poster_path'    => $CONFIG{video_poster_path} );
    
    return $Tmpl;         
    
    exit;    
    
    if ( $Params{mms_station_id} ) {
        $Cols{station}    = _getStations( mode => 'hash', mms_station_id => $Params{mms_station_id}, code => $Params{code}, log => $LOG );
        if ( $Cols{station}->{mms_station_id} ) {
            #$Cols{witnesses}  = _getWitnesses( mms_station_id => $Params{mms_station_id}, 'order-by' => 'mms_person_sort, mms_person_lastname', log => $LOG );
            $Cols{witnesses}  = _getWitnessesSorted( mms_station_id => $Params{mms_station_id}, 'order-by' => 'mms_person_sort, mms_person_lastname', log => $LOG );
            $Cols{glossary}   = _getGlossary( mode => 'hash', log => $LOG );
            $Cols{rubric}     = _getVideoRubrics(  mms_project_id => $Cols{station}->{mms_station_project_id}, mms_station_id => $Params{mms_station_id}, log => 0 );            
        }
        else {
            return showErrorPage();
            exit;        
        }
    }
    
    

    print "\n" if $LOG;
    
    #for (sort keys %{$Cols{station}}) {print "$_ : $Cols{station}->{$_}<br>";}   
    # create template instance
    my $TmplFile = $Config->getValue($CONFIG_TMPL_KEY);
    
    my $TEMPLATE_STATION = 0;
    my $TEMPLATE_PROJECT = 0;
    if ( $Cols{station}->{mms_station_template} ) {
        my $TMPL_STATION = $TMPL_DIR_MMS_PORTAL . '/' . $Cols{station}->{mms_station_template};
        my $TmplFileStation = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_STATION;        
        print "konfiguriertes Stationstemplate: $TmplFileStation\n" if $LOG;
        if ( -e $TmplFileStation ) {
            $TMPL_MMS_INDEX = $TMPL_STATION;
            $TEMPLATE_STATION++;
            print "konfiguriertes Stationstemplate vorhanden\n" if $LOG; 
        }                
    }    
    
    if ( $Cols{station}->{mms_project_template} && !$TEMPLATE_STATION ) {
        my $TMPL_PROJECT = $TMPL_DIR_MMS_PORTAL . '/' . $Cols{station}->{mms_project_template};
        my $TmplFileProject = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_PROJECT;        
        print "konfiguriertes Projekttemplate: $TmplFileProject\n" if $LOG;
        if ( -e $TmplFileProject ) {
            $TMPL_MMS_INDEX = $TMPL_PROJECT;
            $TEMPLATE_PROJECT++;            
            print "konfiguriertes Projekttemplate vorhanden\n" if $LOG;            
        }                
    }

    #my $LOG=1;
    # Test auf lokal definiertes Template
    if ( $Params{mms_station_id} && $Params{theme} ) {    
        #my $TEMPLATE = _getTemplate( station_id => $Params{mms_station_id}, log => $LOG);
        my $TEMPLATE = _getTemplate( station_id => $Params{mms_station_id}, log => $LOG);
        print "lokal konfiguriertes Stationstemplate vorhanden: $TEMPLATE<br>" if ($LOG&&$TEMPLATE); 
        $TMPL_MMS_INDEX = $TEMPLATE if $TEMPLATE;        
    }    

    print "zu verwendendes Template: $TMPL_MMS_INDEX\n" if $LOG;

    #print "Template!!!! $TMPL_MMS_INDEX";
    
    $TmplFile .= ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_MMS_INDEX;
    my $Tmpl = new HTML::Template(filename => $TmplFile, die_on_bad_params => 0);

    # Gruppen- und Gruppenkategorieliste für Auswahlmenü
#     my $WitnessList = _getWitnesses( log => $LOG );
#     $Tmpl->param('witnesslist' => $WitnessList);

	my $obj = \%Cols;
    my $js = JSON->new->allow_blessed->convert_blessed->encode( $obj );
    $Tmpl->param('json_data' => $js);
    
    #print $js; exit; 
    
    $Tmpl->param('project_title'        => $Cols{station}->{mms_project_title} );   
    $Tmpl->param('video_path'           => $CONFIG{video_path} );
    $Tmpl->param('video_poster_path'    => $CONFIG{video_poster_path} );
    
    return $Tmpl;
}

#########################################################################
# Testseite
#########################################################################
sub testItem
{
    my $LOG = 0;
    
    if ( ( !$Params{mms_presentation_id} && !$Params{mms_station_id} ) || !$Params{code} ) {
        return showErrorPage();
        exit; 
    }
    
    my $TmplFile = $Config->getValue($CONFIG_TMPL_KEY);
    $TmplFile .= ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_MMS_DEFAULT;
    my $Tmpl = new HTML::Template(filename => $TmplFile, die_on_bad_params => 0);   
    
    my %Pars;
    my %Cols;
    # Presentation 
    #$Cols{presentation}    = _getPresentation( mode => 'hash', 'return-params' => 'config' , mms_presentation_id => $Params{mms_presentation_id}, code => $Params{code}, log => $LOG );
    
    $Cols{presentation} = {
        mms_presentation_show_title_before_video    => 1,
        mms_presentation_link_title                 => '',
        mms_presentation_public_title               => 'Test',
        mms_presentation_no_intro_screen            => 1
    };
    
    # Projekte
    my @ProjectList;

    #$Cols{glossary}  = _getGlossary( mode => 'hash', log => $LOG );

    if ( $Params{mms_station_id} ) {        
        $Cols{station}   = _getStations( 'with-person' => true, 'order-by' => 'mms_station_title', '__return-params' => 'config', select_mode => 'with-project', mms_station_id => $Params{mms_station_id}, log => $LOG );

        $Pars{special_select} = ' MMS_PROJECT.* FROM MMS_PROJECT, MMS_STATION WHERE mms_project_id = mms_station_project_id AND mms_station_id = ' . $Params{mms_station_id};
        if ( $DBMMS->specialSelect(\%Pars, $LOG, 1) )  {
            foreach ( $DBMMS->getData() )   {
                my %Cols;
    
                for my $k ( keys %{$_} ) {
                    $Cols{$k} = $_->{$k} if $JSON_PARAMS{PROJECT}->{$k};
                }
                
                if ( $LOG ) {for (sort keys %Cols){print "$_ : $Cols{$_}<br>";}}
               
                push(@ProjectList, \%Cols);            
            }
        }     
        
        $Cols{project}   = \@ProjectList;
    }
   
    #print $Cols{station};
    
	my $obj = \%Cols;
    my $js = JSON->new->allow_blessed->convert_blessed->encode( $obj );
    $Tmpl->param('json_data' => $js);
    
    #print $js; exit; 
    
#     $Tmpl->param('project_title'        => $Cols{station}->{mms_project_title} );   
    $Tmpl->param('video_path'           => $CONFIG{video_path} );
    $Tmpl->param('video_poster_path'    => $CONFIG{video_poster_path} );
    
    return $Tmpl;         
}


#########################################################################
# Startseite der MM-Station aufrufen
#########################################################################
sub showIndex
{
    my $LOG = 0;
    
    if ( ( !$Params{mms_station_id} && !$Params{mms_person_id} ) && !$Params{code} ) {
        return showErrorPage();
        exit; 
    }
    
    my %Cols;
    
    if ( $Params{mms_station_id} ) {
        $Cols{station}    = _getStations( mode => 'hash', mms_station_id => $Params{mms_station_id}, code => $Params{code}, log => $LOG );
        if ( $Cols{station}->{mms_station_id} ) {
            #$Cols{witnesses}  = _getWitnesses( mms_station_id => $Params{mms_station_id}, 'order-by' => 'mms_person_sort, mms_person_lastname', log => $LOG );
            $Cols{witnesses}  = _getWitnessesSorted( mms_station_id => $Params{mms_station_id}, 'order-by' => 'mms_person_sort, mms_person_lastname', log => $LOG );
            $Cols{glossary}   = _getGlossary( mode => 'hash', log => $LOG );
            $Cols{rubric}     = _getVideoRubrics(  mms_project_id => $Cols{station}->{mms_station_project_id}, mms_station_id => $Params{mms_station_id}, log => 0 );            
        }
        else {
            return showErrorPage();
            exit;        
        }
    }

    elsif ( $Params{mms_person_id} ) {
        my %WIT    = %{_getWitnesses( mode => 'hash', mms_person_id => $Params{mms_person_id}, code => $Params{code}, log => $LOG )};
        if ( $WIT{mms_person_station_id} ) {
            $Cols{station}    = _getStations( mode => 'hash', mms_station_id => $WIT{mms_person_station_id}, log => $LOG ); 
            $Cols{witnesses}  = _getWitnesses( mms_person_id => $Params{mms_person_id}, code => $Params{code}, log => $LOG );
            $Cols{glossary}   = _getGlossary( mode => 'hash', log => $LOG );                    
        }
        else {
            return showErrorPage();
            exit;        
        }
    } 

    print "\n" if $LOG;
    
    #for (sort keys %{$Cols{station}}) {print "$_ : $Cols{station}->{$_}<br>";}   
    # create template instance
    my $TmplFile = $Config->getValue($CONFIG_TMPL_KEY);
    
    my $TEMPLATE_STATION = 0;
    my $TEMPLATE_PROJECT = 0;
    if ( $Cols{station}->{mms_station_template} ) {
        my $TMPL_STATION = $TMPL_DIR_MMS_PORTAL . '/' . $Cols{station}->{mms_station_template};
        my $TmplFileStation = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_STATION;        
        print "konfiguriertes Stationstemplate: $TmplFileStation\n" if $LOG;
        if ( -e $TmplFileStation ) {
            $TMPL_MMS_INDEX = $TMPL_STATION;
            $TEMPLATE_STATION++;
            print "konfiguriertes Stationstemplate vorhanden\n" if $LOG; 
        }                
    }    
    
    if ( $Cols{station}->{mms_project_template} && !$TEMPLATE_STATION ) {
        my $TMPL_PROJECT = $TMPL_DIR_MMS_PORTAL . '/' . $Cols{station}->{mms_project_template};
        my $TmplFileProject = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_PROJECT;        
        print "konfiguriertes Projekttemplate: $TmplFileProject\n" if $LOG;
        if ( -e $TmplFileProject ) {
            $TMPL_MMS_INDEX = $TMPL_PROJECT;
            $TEMPLATE_PROJECT++;            
            print "konfiguriertes Projekttemplate vorhanden\n" if $LOG;            
        }                
    }

    #my $LOG=1;
    # Test auf lokal definiertes Template
    if ( $Params{mms_station_id} && $Params{theme} ) {    
        #my $TEMPLATE = _getTemplate( station_id => $Params{mms_station_id}, log => $LOG);
        my $TEMPLATE = _getTemplate( station_id => $Params{mms_station_id}, log => $LOG);
        print "lokal konfiguriertes Stationstemplate vorhanden: $TEMPLATE<br>" if ($LOG&&$TEMPLATE); 
        $TMPL_MMS_INDEX = $TEMPLATE if $TEMPLATE;        
    }    

    print "zu verwendendes Template: $TMPL_MMS_INDEX\n" if $LOG;

    #print "Template!!!! $TMPL_MMS_INDEX";
    
    $TmplFile .= ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_MMS_INDEX;
    my $Tmpl = new HTML::Template(filename => $TmplFile, die_on_bad_params => 0);

    # Gruppen- und Gruppenkategorieliste für Auswahlmenü
#     my $WitnessList = _getWitnesses( log => $LOG );
#     $Tmpl->param('witnesslist' => $WitnessList);

	my $obj = \%Cols;
    my $js = JSON->new->allow_blessed->convert_blessed->encode( $obj );
    $Tmpl->param('json_data' => $js); 
    
    $Tmpl->param('project_title'        => $Cols{station}->{mms_project_title} );   
    $Tmpl->param('video_path'           => $CONFIG{video_path} );
    $Tmpl->param('video_poster_path'    => $CONFIG{video_poster_path} );
    
    return $Tmpl;
}

#########################################################################
# Haftorte
#########################################################################
sub showMapPrisons
{
    my $LOG = 0;
    
    my %DATA_MAP_PRISONS = (
        headline => 'Station 4: Haftorte',
        rubric   => [
            {
                name           => 'Haftorte in Dresden',
                img_thumb      => 'thumb_mueplatz.jpg',
                img_big        => 'big_mueplatz.jpg',
                img_big_legend => 'Haftanstalt George-Bähr-Straße 5 (1908)',
                img_map_thumb  => 'thumb_map_dresden.jpg',
                headline       => '',
                desc           => 'Innerhalb weniger Monate nach ihrem Einmarsch in Dresden 1945 schuf die sowjetische Besatzungsmacht ein Netz von Haftorten in der Stadt. So wurden bereits bestehende Haftanstalten, wie das Untersuchungsgefängnis am Münchner Platz oder die ehemalige Militär-Arrestanstalt auf der Königsbrücker Straße 125, als sowjetische Gefängnisse genutzt. Es wurden aber auch neue Arrestorte geschaffen, so beispielsweise in Kellerräumen von Villen auf der Bautzner Straße. Solche vor allem in beschlagnahmten Privatgrundstücken
eingerichteten Kellergefängnisse wurden umgangssprachlich „NKWD-Keller“
oder „GPU-Keller“ genannt; dabei stehen die Abkürzungen für vormalige Bezeichnungen der sowjetischen Sicherheitsorgane. Solche Gefängnisse waren ab 1946 dem sowjetischen Innenministerium (MWD) unterstellt. Zu diesem Netz gehörten aber auch deutsche Gefängnisse, wie das Dresdner Polizeipräsidium.
<br><br>
An diesen Haftorten waren nicht nur tatsächliche oder vermeintliche NS-Täter und höherrangige Nationalsozialisten inhaftiert, sondern auch Menschen, die dem Aufbau eines stalinistischen Systems in der SBZ und DDR kritisch gegenüberstanden oder vollkommen grundlos in Haft kamen.
<br><br>
Nach Wochen der Untersuchungshaft mit nächtlichen Verhören durch den sowjetischen Geheimdienst führte der Weg in sowjetische Speziallager auf dem Gebiet der SBZ und frühen DDR oder in den Gulag auf dem Territorium der Sowjetunion. Entlassungen aus der Untersuchungshaft des sowjetischen Geheimdienstes waren selten.
<br><br>
Ähnlich wie in Dresden gab es im gesamten Gebiet der SBZ und frühen DDR eine Vielzahl kleiner und mittelgroßer lokaler Haftorte und größerer Gefängnisse, die durch die sowjetische Besatzungsmacht genutzt wurden. Bis heute sind längst nicht alle Haftorte bekannt.
',
                zoom      => '13',
            },
            {
                name      => 'Speziallager',
                img_thumb => 'thumb_buchenwald.jpg',
                img_big   => 'big_buchenwald.jpg',
                img_big_legend => 'Speziallager Buchenwald mit Wachturm (1952)',                
                img_map_thumb => 'thumb_map_speziallager.jpg',                
                headline  => '',
                zoom      => '8',
                desc      => 'In der SBZ wurden insgesamt zehn Speziallager für die Internierung von NS-Funktionären und Kriegsverbrechern eingerichtet. Dies geschah 1945 durch den NKWD-Chef Lawrentij Berija, der mit dem Befehl Nr. 00315 die Grundlage für die Internierung von Funktionsträgern des NS-Regimes schuf. Die Verhaftungen gingen jedoch weit über das Ziel der Entnazifizierung hinaus, denn der Befehl war so pauschal formuliert, dass nahezu jeder hätte eingesperrt werden können. Und so internierte man die Menschen ohne richterliche Prüfung der persönlichen Schuld und ohne Unterscheidung der tatsächlichen Funktion im NS-System. So konnte die sowjetische Besatzungsmacht leicht „politisch unliebsame“ Personen „wegsperren“. Eine Internierung hatte fatale Folgen, denn obwohl die Speziallager weder Vernichtungs- noch Arbeitslager waren, starben etwa ein Drittel der Häftlinge während ihrer Haftzeit. 1946 wurde zusätzlich ein Teil der SMT-Verurteilten in die häufig schon stark überfüllten Speziallager eingewiesen. An der Verhaftungspraxis änderte auch die Direktive
Nr. 38 des Alliierten Kontrollrats nichts, wonach zwischen aktiven und nominellen Mitgliedern der NSDAP unterschieden werden sollte. So ist ca. ein Drittel aller Verhaftungen nicht auf eine Tätigkeit im NS-Regime zurückzuführen.
Erst 1948 wurde ein Großteil der Sowjetischen Speziallager aufgelöst, wodurch knapp 28.000 Menschen in die Freiheit gelangten. Nur in Buchenwald, Sachsenhausen und Bautzen blieben die Speziallager bestehen. Bei der Auflösung der drei letzten Speziallager im Jahre 1950 wurde ein Großteil der verbliebenen Häftlinge entlassen. Etwa 3.500 verurteilte die DDR-Justiz in den „Waldheimer Prozessen“ in Schnellverfahren zu weiteren Haftstrafen. Jene, die bereits von einem SMT verurteilt worden waren, wurden zur Verbüßung ihrer Strafe nun dem Ministerium des Innern der DDR übergeben. Dabei handelte es sich um mehr als 10.000 Menschen. Nach sowjetischen Zählungen ist von über 122.000 deutschen Gefangenen in den verschiedenen Speziallagern auszugehen.
',                                                                
            },
            {
                name      => 'Gulag',
                img_thumb => 'thumb_workuta.jpg',                
                img_big   => 'big_workuta.jpg',
                img_big_legend => 'Wachturm in der Schachtzone des Lagerkomplexes Workuta (1945)',                
                img_map_thumb => 'thumb_map_gulag.jpg',                
                headline  => '',
                zoom      => '4',
                desc      => 'Gulag war ursprünglich eine russische Abkürzung: ГУЛаг – Главное Управление Лагерей (GULag – Akronym für Hauptverwaltung der Besserungsarbeitslager). Heute wird es gleichzeitig als Synonym für ein umfassendes Repressionssystem in der ehemaligen Sowjetunion verwendet, das aus Dutzenden Zwangsarbeitslagern, Straflagern, Gefängnissen und Verbannungsorten mit Tausenden Unterlagern und Haftorten bestand. Der Gulag diente der Unterdrückung politischer Gegner, der Ausbeutung durch Zwangsarbeit sowie der Internierung von Kriegsgefangenen und stellte ein wesentliches Element der Stalinschen Herrschaft dar. Nach dem Zweiten Weltkrieg wurden Internierte und aus politischen Gründen durch Sowjetische Militärtribunale verurteilte Deutsche sowie deutsche Kriegsgefangene als Zwangsarbeiter in den Gulag deportiert. In den Lagern waren Menschen aus vielen Nationen inhaftiert, zeitweise insgesamt über 2,5 Millionen, wie im Jahr 1950.
In den Straflagern gab es eine fein abgestufte Rangordnung, in der Kriminelle im Gegensatz zu politischen Häftlingen oft wichtige Posten besetzen konnten. Die „Politischen“ mussten dagegen die schwersten Arbeiten verrichten. Die unmenschlichen Lebensbedingungen führten zum Tod Hunderttausender Häftlinge. Die Freilassung politischer Häftlinge begann nach Stalins Tod, wobei die erste Amnestie vom März 1953 politische Häftlinge noch ausschloss. Ein Großteil der deutschen SMT-Verurteilten konnte erst im Zuge der erfolgreichen Verhandlungen Konrad Adenauers mit der Sowjetunion heimkehren. Auf Einladung der sowjetischen Regierung war Adenauer im September 1955 zu Gesprächen nach Moskau gereist. Ausgehandelt wurden die Wiederaufnahme diplomatischer Beziehungen zwischen beiden Ländern und die Freilassung der rund 10.000 auf sowjetischem Boden verbliebenen deutschen Kriegsgefangenen. In diesen Verhandlungen wurde auch die Entlassung der politischen Häftlinge aus dem Gulag erreicht. Die letzten deutschen Häftlinge kamen 1956 aus der Sowjetunion zurück.
',                                                  
            },
        ]
    );
    
    my @List;
    my %Pars;

    # HAFTORTE DRESDEN
    $Pars{special_select} = ' * FROM CS_PRISON WHERE cs_prison_city = "Dresden" AND cs_prison_coord_lat IS NOT NULL AND cs_prison_coord_long IS NOT NULL';
    if ( $DBMMS->specialSelect(\%Pars, $LOG, 1) )  {
        foreach ( $DBMMS->getData() )   {
            my %Cols = %{$_};
            push @List,\%Cols;       
        }
    }     
    
    $DATA_MAP_PRISONS{rubric}->[0]->{prison} = \@List;
    
    $Pars{special_select} = '(max(cs_prison_coord_long) + min(cs_prison_coord_long)) AS MID_LONG,(max(cs_prison_coord_lat) + min(cs_prison_coord_lat)) AS MID_LAT,count(*) AS NUM FROM CS_PRISON WHERE cs_prison_city = "Dresden" AND cs_prison_coord_lat IS NOT NULL AND cs_prison_coord_long IS NOT NULL';
    if ( $DBMMS->specialSelect(\%Pars, $LOG, 1) )  {
        foreach ( $DBMMS->getData() )   {
            $DATA_MAP_PRISONS{rubric}->[0]->{longitude_middle} = $_->{MID_LONG} / 2;
            $DATA_MAP_PRISONS{rubric}->[0]->{latitude_middle}  = $_->{MID_LAT} / 2;
            $DATA_MAP_PRISONS{rubric}->[0]->{prison_num}       = $_->{NUM};
        }
    }
    
    # SPEZIALLAGER SBZ
    my @List;
    %Pars = {};
    $Pars{special_select} = ' * FROM CS_PRISON WHERE cs_prison_prison_type_id = 6 AND cs_prison_coord_lat IS NOT NULL AND cs_prison_coord_long IS NOT NULL';
    if ( $DBMMS->specialSelect(\%Pars, $LOG, 1) )  {
        foreach ( $DBMMS->getData() )   {
            my %Cols = %{$_};
            push @List,\%Cols;       
        }
    }     
    
    $DATA_MAP_PRISONS{rubric}->[1]->{prison} = \@List;
    
    my @LagerList = @List;
    
    $Pars{special_select} = '(max(cs_prison_coord_long) + min(cs_prison_coord_long)) AS MID_LONG,(max(cs_prison_coord_lat) + min(cs_prison_coord_lat)) AS MID_LAT,count(*) AS NUM FROM CS_PRISON WHERE cs_prison_prison_type_id = 6 AND cs_prison_coord_lat IS NOT NULL AND cs_prison_coord_long IS NOT NULL';
    if ( $DBMMS->specialSelect(\%Pars, $LOG, 1) )  {
        foreach ( $DBMMS->getData() )   {
            $DATA_MAP_PRISONS{rubric}->[1]->{longitude_middle} = $_->{MID_LONG} / 2;
            $DATA_MAP_PRISONS{rubric}->[1]->{latitude_middle}  = $_->{MID_LAT} / 2;
            $DATA_MAP_PRISONS{rubric}->[1]->{prison_num}       = $_->{NUM};
        }
    } 
    
    # GULAG SBZ
    my @List;
    %Pars = {};
    $Pars{special_select} = ' * FROM CS_PRISON WHERE cs_prison_prison_type_id = 1 AND cs_prison_coord_lat IS NOT NULL AND cs_prison_coord_long IS NOT NULL';
    if ( $DBMMS->specialSelect(\%Pars, $LOG, 1) )  {
        foreach ( $DBMMS->getData() )   {
            my %Cols = %{$_};
            push @List,\%Cols;       
        }
    }     
    
    $DATA_MAP_PRISONS{rubric}->[2]->{prison} = \@List;    
    
    $Pars{special_select} = '(max(cs_prison_coord_long) + min(cs_prison_coord_long)) AS MID_LONG,(max(cs_prison_coord_lat) + min(cs_prison_coord_lat)) AS MID_LAT,count(*) AS NUM FROM CS_PRISON WHERE cs_prison_prison_type_id = 1 AND cs_prison_coord_lat IS NOT NULL AND cs_prison_coord_long IS NOT NULL';
    if ( $DBMMS->specialSelect(\%Pars, $LOG, 1) )  {
        foreach ( $DBMMS->getData() )   {
            $DATA_MAP_PRISONS{rubric}->[2]->{longitude_middle} = $_->{MID_LONG} / 2;
            $DATA_MAP_PRISONS{rubric}->[2]->{latitude_middle}  = $_->{MID_LAT} / 2;
            $DATA_MAP_PRISONS{rubric}->[2]->{prison_num}       = $_->{NUM};
        }
    }            

   
    
    
    $DATA_MAP_PRISONS{glossary}   = _getGlossary( mode => 'hash', log => $LOG );    
    
    if ( $Params{templ} == 2 ) {
        $TMPL_MMS_MAP_PRISONS = $TMPL_MMS_MAP_PRISONS_2;
    }
    elsif ( $Params{templ} == 3 ) {
        $TMPL_MMS_MAP_PRISONS = $TMPL_MMS_MAP_PRISONS_3;
    }

    # create template instance
    my $TmplFile = $Config->getValue($CONFIG_TMPL_KEY);
    $TmplFile .= ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_MMS_MAP_PRISONS;
    my $Tmpl = new HTML::Template(filename => $TmplFile, die_on_bad_params => 0);
    
	my $obj = \%DATA_MAP_PRISONS;
    my $js = JSON->new->allow_blessed->convert_blessed->encode( $obj );
    $Tmpl->param('json_data' => $js); 
    
    $Tmpl->param(lagerlist => \@LagerList);
    $Tmpl->param('longmid' => $DATA_MAP_PRISONS{rubric}->[1]->{longitude_middle} );
    $Tmpl->param('latmid'  => $DATA_MAP_PRISONS{rubric}->[1]->{latitude_middle} );    
    
    return $Tmpl;
}

#########################################################################
# Startseite Präsentationsrechner
#########################################################################
sub startPresentation
{
    my $LOG = 0;

    # create template instance
    my $TEMPLATE = _getTemplate( site => 'start', log => $LOG);
    print "Template: $TEMPLATE<br>" if $LOG;

    my $TmplFile  = $Config->getValue($CONFIG_TMPL_KEY);
       $TmplFile .= ($TmplFile !~ /\/$/ ? '/' : '') . $TEMPLATE;
    my $Tmpl = new HTML::Template(filename => $TmplFile, die_on_bad_params => 0);
    
    my %Cols;
    
    $Cols{page_title} = 'Überblick Medienstationen und Zeitzeugen';
    
    ### Stationen auslesen #####################################################
    my @List;
    $Params{select_mode}           = 'with-project';    
    $Params{mms_station_activate}  = 1;
    $Params{'order-by'}            = 'mms_project_title, mms_station_title';
    my %Project;
    my $i=0;
    if ( $DBMMS->selectMMSStation(\%Params,$LOG, 1))  {
        foreach ($DBMMS->getData())  {
            my %Cols = %{$_};
            if ( $Project{$Cols{mms_project_id}} ) {
                delete($Cols{mms_project_id}); 
            }
            else {
                $Project{$Cols{mms_project_id}}++;
                if ( $i ) {
                    $Cols{closing}++;
                }
            }
            #$PROJECT_TITLE = $Cols{mms_project_title} if ( $Cols{mms_project_id} == 2 );
            $Cols{station_link} = '<a href="/cgi-bin/mms/mms_portal.pl?mms_station_id=' . $Cols{mms_station_id} . '&code=' . $Cols{CODE} . ( $Params{theme} ? '&theme=' . $Params{theme} : '' ) . '" title="Medienstation starten">' . $Cols{mms_station_title} . '</a>'; 

            $i++;
            push @List, \%Cols;
        }
    }
    
    #### hard coded
    # Kartenlink
    my %Vars;
    $Vars{mms_station_title} = 'Station Karten';
    $Vars{station_link}      = '<a href="/cgi-bin/mms/mms_portal.pl?action=showMapPrisons" title="Medienstation starten">' . $Vars{mms_station_title} . '</a>';    
    push @List, \%Vars;    
    
    $Cols{stations} = \@List; 
    
    ### Zeitzeugen auslesen #####################################################
    my @PersonList;
    $Params{select_mode}  = 'with-project-and-station';
    $Params{mms_person_activate}  = 1;
    #$Params{'order-by'}   = 'mms_person_lastname, mms_person_firstname';
    $Params{'order-by'}   = 'mms_person_firstname, mms_person_lastname';
    delete($Params{mms_station_activate});
    if ( $DBMMS->selectMMSPerson(\%Params,$LOG, 1))  {
        foreach ($DBMMS->getData())  {
            my %Cols = %{$_};
            
            $Cols{person_name} = $Cols{mms_person_firstname} . ' ' . $Cols{mms_person_lastname};
            $Cols{person_link} = '<a href="/cgi-bin/mms/mms_portal.pl?mms_person_id=' . $Cols{mms_person_id} . '&code=' . $Cols{CODE} . '" title="Zeitzeugen starten">' . $Cols{person_name} . '</a>';
            #print "$Cols{person_link}<br>";

            push @PersonList, \%Cols;
        }
    }    

    $Cols{persons} = \@PersonList;            
    
    for ( keys %Cols ) {
        $Tmpl->param( $_ => $Cols{$_} );
    }
    
    return $Tmpl;
}

#########################################################################
# Fehlerseite
#########################################################################
sub showErrorPage
{
    my $LOG = 0;

    # create template instance
    my $TmplFile = $Config->getValue($CONFIG_TMPL_KEY);
    $TmplFile .= ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_MMS_ERROR;
    my $Tmpl = new HTML::Template(filename => $TmplFile, die_on_bad_params => 0);
    
    return $Tmpl;
}



#########################################################################
# Zeitzeugendaten als JSON-Objekt zurückgeben
#########################################################################
sub getJSONWitnessData
{
    my $LOG = 0;
    
    print $CGI->header('application/json');    

    my @List;
    my %Pars;
    $Pars{usrnew_id} = $Params{usrnew_id};
    $Pars{simple}++;
    $Pars{usrnew_witness_module} = 1;
    
    my %Cols;
    if ($DB->selectUserNew(\%Pars, $LOG, 1))   {    
    	foreach ($DB->getData())  {
        	%Cols = %{$_};
            $Cols{usrnew_birth_date}     = $date->getOneDateTime($_->{usrnew_birth_date}) || undef;            
        }
    }

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

################################################################################
# Haftorte nach Rubriken auswählen und als XML ausgeben
################################################################################
sub loadXMLPrisons {

    print $CGI->header(-type=>'text/xml');

    print '<markers>' . "\n";
    
    my %Pars;
    $Params{rubric} ||= 0;
    
    if ( $Params{rubric} == 0 ) {
        # HAFTORTE DRESDEN
        $Pars{special_select} = ' * FROM CS_PRISON WHERE cs_prison_city = "Dresden" AND ( cs_prison_id = 233 OR cs_prison_prison_type_id = 4 OR cs_prison_prison_type_id = 11 ) AND cs_prison_coord_lat IS NOT NULL AND cs_prison_coord_long IS NOT NULL';    
    }    
    elsif ( $Params{rubric} == 1 ) {
        # SPEZIALLAGER SBZ    
        $Pars{special_select} = ' * FROM CS_PRISON WHERE cs_prison_prison_type_id = 6 AND cs_prison_coord_lat IS NOT NULL AND cs_prison_coord_long IS NOT NULL';    
    }
    elsif ( $Params{rubric} == 2 ) {
        # GULAG    
        $Pars{special_select} = ' * FROM CS_PRISON WHERE cs_prison_prison_type_id = 1 AND cs_prison_coord_lat IS NOT NULL AND cs_prison_coord_long IS NOT NULL';    
    }

    if ( $DBMMS->specialSelect(\%Pars, $LOG, 1) )  {
        foreach ( $DBMMS->getData() )   {
            my %Cols = %{$_};
            for ( keys %Cols ) {
                #$Cols{$_} = encode("utf8",decode("ISO-8859-1", $Cols{$_}));

                $Cols{$_} =~ s/\„/&bdquo;/g;
                $Cols{$_} =~ s/\“/&ldquo;/g;
                $Cols{$_} =~ s/\–/&ndash;/g;

                $Cols{$_} = encode("ISO-8859-1",decode("utf8", $Cols{$_}));            
                $Cols{$_} =~ s/\&/&amp;/g;
                $Cols{$_} =~ s/\"/&quot;/g;
     
            }
            
            my %Vals;
            $Vals{special_select} = ' * FROM CS_FILE_ASGT, CS_FILE WHERE csfa_fk_id = ' . $Cols{cs_prison_id} . ' AND cs_file_id = csfa_cs_file_id ORDER BY csfa_sort LIMIT 1';
            if ( $DBMMS->specialSelect(\%Vals, $LOG, 1) )  {
                foreach ( $DBMMS->getData() )   {
                    $Vals{img_id}    = $_->{cs_file_id};
                    $Vals{img_file}  = $_->{cs_file_filename};
                    $Vals{img_title} = $_->{cs_file_title};
                    
                    for ( keys %Vals ) {
                        $Vals{$_} =~ s/\„/&bdquo;/g;
                        $Vals{$_} =~ s/\“/&ldquo;/g;
                        $Vals{$_} =~ s/\–/&ndash;/g;
        
                        $Vals{$_} = encode("ISO-8859-1",decode("utf8", $Vals{$_}));            
                        $Vals{$_} =~ s/\&/&amp;/g;
                        $Vals{$_} =~ s/\"/&quot;/g;
                    }
                                        
                    
                }
            }                          
            
            my $id              = $Cols{cs_prison_id};
            my $prison_name     = $Cols{cs_prison_name};
            my $prison_name_syn = $Cols{cs_prison_name_synonym};
            my $address     = $Cols{cs_prison_street} . ', ' . $Cols{cs_prison_zip} . ' ' . $Cols{cs_prison_city} . ', Germany';
            my $street      = $Cols{cs_prison_street};
            my $city        = $Cols{cs_prison_zip} . ' ' . $Cols{cs_prison_city};
            my $img_id      = $Vals{img_id};            
            my $img_file    = $Vals{img_file};
            my $img_title   = $Vals{img_title};
            my $iwidth      = $Cols{cs_prison_img_width};
            my $iheight     = $Cols{cs_prison_img_height};
            my $longitude   = $Cols{cs_prison_coord_long};
            my $latitude    = $Cols{cs_prison_coord_lat};
            my $web         = $Cols{cs_prison_link} ? 'http://' . $Cols{cs_prison_link} : '';
            #my $type        = $Cols{cs_prison_type} ? 'main' : 'normal';
            my $type        = 'main';
            my $desc        = $Cols{cs_prison_desc};            
            
            print '<marker ';
            print 'name="' . $prison_name . '" ';
            print 'name_syn="' . $prison_name_syn . '" ';
            print 'address="' . $address . '" ';
            print 'street="' . $street . '" ';
            print 'city="' . $city . '" ';
            print 'lat="' . $latitude . '" ';
            print 'lng="' . $longitude . '" ';
            print 'type="' . $type . '" ';
            print 'web="' . $web . '" ';
            print 'img_id="' . $img_id . '" ';
            print 'img_file="' . $img_file . '" ';
            print 'img_title="' . $img_title . '" ';
            print 'iwidth="' . $iwidth . '" ';
            print 'iheight="' . $iheight . '" ';
            print 'desc="' . $desc . '" ';
            print ' />' . "\n";
        }
    }

    print '</markers>';

    exit;
}

#################################################################################
# Zeitzeugen auslesen
#################################################################################
sub _getWitnesses()
{

    my %arg = (
        log     => 0,
        @_
    );

    my $LOG          = $arg{log} || 0;

    print "Zeitzeugen auslesen... (Mode: $MODE)<br>" if $LOG;

    my %User;
    my @UserList;

    my %Pars;
    $Pars{mms_station_id} = $arg{mms_station_id};
    if ( $arg{mms_person_id} && $arg{code} ) {
        $Pars{mms_person_id}  = $arg{mms_person_id};
        $Pars{code}           = $arg{code}; 
    }
    $Pars{'order-by'} = $arg{'order-by'};
    if ( $DBMMS->selectMMSPerson(\%Pars, $LOG, 1))   {
        foreach ($DBMMS->getData())  {
            #my %Cols = %{$_};
            my %Cols;
            
            $Cols{pers_id}            = $_->{mms_person_id};
            $Cols{birth_year}         = ($date->getSplitDate($_->{mms_person_birth_date}))[0];
            $Cols{death_year}         = ($date->getSplitDate($_->{mms_person_death_date}))[0] if $_->{mms_person_death_date};
            $Cols{detention_reason}   = $_->{mms_person_custody_grounds}; 
            $Cols{detention_year}     = ($date->getSplitDate($_->{mms_person_detention}))[0] if $_->{mms_person_detention};
            $Cols{detention_sentence} = $_->{mms_person_sentence};
            $Cols{detention_release}  = ($date->getSplitDate($_->{mms_person_discharge}))[0] if $_->{mms_person_discharge};
            $Cols{name}               = $_->{mms_person_firstname} . ' ' . $_->{mms_person_lastname};
            $Cols{headline}           = $_->{mms_person_subtitle}; 
            $Cols{desc}               = $_->{mms_person_bio};
            $Cols{desc}               =~ s/([\n\f\r][\n\f\r])|[\n\f\r]/\<BR\>\<BR\>/sg; 
            $Cols{interview_date}     = $date->getOneDateTime($_->{mms_person_recording_date});
            $Cols{interview_location} = $_->{mms_person_recording_location};
            $Cols{interview_names}    = $_->{mms_person_recording_staff};
            $Cols{img_thumb}          = $_->{mms_person_filename_small} ? ( $_->{mms_person_id} . '/' . $_->{mms_person_filename_small} ) : '';
            $Cols{img_big}            = $_->{mms_person_filename_large} ? ( $_->{mms_person_id} . '/' . $_->{mms_person_filename_large} ) : '';
            if ( $LOG ) {for (sort keys %Cols){print "$_ : $Cols{$_}<br>";}}
            
            # Dokumente
            if ( !$arg{mode} ) {
                my @List;
                my %Vars;
                $Vars{'order-by'}    = 'mms_document_rubric_sort';
                $Vars{mms_person_id} = $_->{mms_person_id};
                if ( $DBMMS->selectMMSDocumentRubric(\%Vars,$LOG, 1))  {
                    foreach ($DBMMS->getData())  {
                        my %Cols;
                        $Cols{doc_rubric}             = $_->{mms_document_rubric_title};
                        
                        my @DocList;
                        my %Values;
                        $Values{mms_document_rubric_id} = $_->{mms_document_rubric_id};
                        $Values{'order-by'}   = 'mms_document_sort';            
                        if ( $DBMMS->selectMMSDocument(\%Values,$LOG, 1))  {
                            foreach ($DBMMS->getData())  {
                                my %Pars;
                                $Pars{doc_title}    = $_->{mms_document_title};
                                $Pars{doc_desc}     = $_->{mms_document_desc};
                                $Pars{doc_id}       = $_->{mms_document_id};
                                $Pars{doc_filename} = $_->{mms_document_filename};
                                $Pars{doc_file}     = $_->{mms_document_filename} ? '<img src="/mms/img/witnesses/docs/' . $_->{mms_document_id} . '/' . $_->{mms_document_filename} . '" />' : '&nbsp;';                    
                                push @DocList, \%Pars;                    
                            }
                        }
                        
                        $Cols{doclist} = \@DocList;            
            
                        push @List, \%Cols;
                    }
                }
                $Cols{persdoclist} = \@List;                
            }
            
            # Videos
            if ( !$arg{mode} ) { 
                my @List;
                my %Vars;                                       
                $Vars{select_mode} = 'with-rubric';
                $Vars{'order-by'}  = 'mms_video_rubric_sort, mms_video_sort';
                $Vars{mms_person_id} = $_->{mms_person_id};                
                my %Border;
                my $CUR_VIDEO_RUBRIC_ID = 0;
                my $LAST_VIDEO_ID       = 0;
                if ( $DBMMS->selectMMSVideo(\%Vars, $LOG,1) )  {
                    foreach ($DBMMS->getData())   {
                        my %Cols;
                        $Cols{video_id}           = $_->{mms_video_id};
                        $Cols{video_title}        = $_->{mms_video_title};
                        $Cols{video_file}         = $_->{mms_video_filename};
                        $Cols{video_rubric_id}    = $_->{mms_video_rubric_id};
                        $Cols{video_rubric_title} = $_->{mms_video_rubric_title};
                        
                        push @List, \%Cols;
                    }
                }
                $Cols{persvideolist} = \@List;                
            }            
            
            %User = %{$_};
                        
            push(@UserList, \%Cols);
        }
    }

    if ( $arg{mode} eq 'hash' ) {
        print "RETURN HASH<br>" if $LOG;
        return \%User;
    }
    else {
        return \@UserList;
    }
}

#################################################################################
# Zeitzeugen auslesen
#################################################################################
sub _getWitnessesSorted
{

    my %arg = (
        log     => 0,
        @_
    );

    my $LOG          = $arg{log} || 0;

    print "Zeitzeugen auslesen... (Mode: $MODE) [$arg{mms_person_id}]<br>" if $LOG;

    my %User;
    my @UserList;

    my %Pars;
    $Pars{mms_station_id} = $arg{mms_station_id};
    if ( $arg{mms_person_id} && $arg{code} ) {
        $Pars{mms_person_id}  = $arg{mms_person_id};
        $Pars{code}           = $arg{code}; 
    }
    $Pars{'order-by'} = $arg{'order-by'};
    #if ( $DBMMS->selectMMSPerson(\%Pars, $LOG, 1))   {
    if ( $DBMMS->selectMMSPersonMMSStation( { mms_station_id => $Pars{mms_station_id}, mms_person_id => $arg{mms_person_id}, 'select-type' => 'with-person', 'order-by' => 'mpmsa_sort' } , $LOG, 1) )  {
        foreach ($DBMMS->getData())  {
        
            next unless $_->{mms_person_activate};
            #my %Cols = %{$_};
            my %Cols;
            
            $Cols{pers_id}            = $_->{mms_person_id};
            $Cols{birth_year}         = ($date->getSplitDate($_->{mms_person_birth_date}))[0];
            $Cols{death_year}         = ($date->getSplitDate($_->{mms_person_death_date}))[0] if $_->{mms_person_death_date};
            $Cols{detention_reason}   = $_->{mms_person_custody_grounds}; 
            $Cols{detention_year}     = ($date->getSplitDate($_->{mms_person_detention}))[0] if $_->{mms_person_detention};
            $Cols{detention_sentence} = $_->{mms_person_sentence};
            $Cols{detention_release}  = ($date->getSplitDate($_->{mms_person_discharge}))[0] if $_->{mms_person_discharge};
            $Cols{name}               = $_->{mms_person_firstname} . ' ' . $_->{mms_person_lastname};
            $Cols{headline}           = $_->{mms_person_subtitle}; 
            $Cols{desc}               = $_->{mms_person_bio};
            $Cols{desc}               =~ s/([\n\f\r][\n\f\r])|[\n\f\r]/\<BR\>\<BR\>/sg; 
            $Cols{interview_date}     = $date->getOneDateTime($_->{mms_person_recording_date});
            $Cols{interview_location} = $_->{mms_person_recording_location};
            $Cols{interview_names}    = $_->{mms_person_recording_staff};
            $Cols{img_thumb}          = $_->{mms_person_filename_small} ? ( $_->{mms_person_id} . '/' . $_->{mms_person_filename_small} ) : '';
            $Cols{img_big}            = $_->{mms_person_filename_large} ? ( $_->{mms_person_id} . '/' . $_->{mms_person_filename_large} ) : '';
            if ( $LOG ) {for (sort keys %Cols){print "$_ : $Cols{$_}<br>";}}
            
            # Dokumente
            if ( !$arg{mode} ) {
                my @List;
                my %Vars;
                $Vars{'order-by'}    = 'mms_document_rubric_sort';
                $Vars{mms_person_id} = $_->{mms_person_id};
                if ( $DBMMS->selectMMSDocumentRubric(\%Vars,$LOG, 1))  {
                    foreach ($DBMMS->getData())  {
                        my %Cols;
                        $Cols{doc_rubric}             = $_->{mms_document_rubric_title};
                        $Cols{doc_rubric_id}          = $_->{mms_document_rubric_id};
                        
                        my @DocList;
                        my %Values;
                        $Values{mms_document_rubric_id} = $_->{mms_document_rubric_id};
                        $Values{'order-by'}   = 'mms_document_sort';            
                        if ( $DBMMS->selectMMSDocument(\%Values,$LOG, 1))  {
                            foreach ($DBMMS->getData())  {
                                my %Pars;
                                $Pars{doc_title}    = $_->{mms_document_title};
                                $Pars{doc_desc}     = $_->{mms_document_desc};
                                $Pars{doc_id}       = $_->{mms_document_id};
                                $Pars{doc_filename} = $_->{mms_document_filename};
                                $Pars{doc_file}     = $_->{mms_document_filename} ? '<img src="/mms/img/witnesses/docs/' . $_->{mms_document_id} . '/' . $_->{mms_document_filename} . '" />' : '&nbsp;';                    
                                push @DocList, \%Pars;                    
                            }
                        }
                        
                        $Cols{doclist} = \@DocList;            
            
                        push @List, \%Cols;
                    }
                }
                $Cols{persdoclist} = \@List;                
            }
            
            # Videos
            if ( !$arg{mode} ) { 
                my @List;
                my %Vars;                                       
                $Vars{select_mode} = 'with-rubric';
                $Vars{'order-by'}  = 'mms_video_rubric_sort, mms_video_sort';
                $Vars{mms_person_id} = $_->{mms_person_id};     
                #$Vars{mms_video_visible} = 1;           
                my %Border;
                my $CUR_VIDEO_RUBRIC_ID = 0;
                my $LAST_VIDEO_ID       = 0;
                if ( $DBMMS->selectMMSVideo(\%Vars, $LOG,1) )  {
                    foreach ($DBMMS->getData())   {
                        my %Cols;
                        $Cols{video_id}                   = $_->{mms_video_id};
                        $Cols{video_title}                = $_->{mms_video_title};
                        $Cols{video_file}                 = $_->{mms_video_filename};
                        $Cols{video_rubric_id}            = $_->{mms_video_rubric_id};
                        $Cols{video_rubric_title}         = $_->{mms_video_rubric_title};
                        $Cols{mms_video_rubric_parent_id} = $_->{mms_video_rubric_parent_id};
                        
                        # Elternrubrik auslesen
                        if ( $Cols{mms_video_rubric_parent_id} && $DBMMS->selectMMSVideoRubric( { mms_video_rubric_id => $Cols{mms_video_rubric_parent_id}, 'order-by' => 'mms_video_rubric_sort'}, $LOG, 1))   {
                            foreach ($DBMMS->getData())  {
                                 $Cols{parent_rubric_title} = $_->{mms_video_rubric_title};
                            }
                        }
                                                         
                        
                        push @List, \%Cols;
                    }
                }
                $Cols{persvideolist} = \@List;                
            }            
            
            %User = %{$_};
                        
            push(@UserList, \%Cols);
        }
    }

    if ( $arg{mode} eq 'hash' ) {
        print "RETURN HASH<br>" if $LOG;
        return \%User;
    }
    else {
        return \@UserList;
    }
}

#################################################################################
# Haftorte auslesen
#################################################################################
sub _getPrisonsSorted
{

    my %arg = (
        log     => 0,
        @_
    );

    my $LOG          = $arg{log} || 0;

    print "Haftorte auslesen... (Mode: $MODE)<br>" if $LOG;

    my %Prison;
    my @PrisonList;

    my %Pars;
    $Pars{mms_station_id} = $arg{mms_station_id};
    if ( $arg{mms_prison_id} && $arg{code} ) {
        $Pars{mms_prison_id}  = $arg{mms_prison_id};
        $Pars{code}           = $arg{code}; 
    }
    $Pars{'order-by'} = $arg{'order-by'};
    if ( $DBMMS->selectMMSPrisonMMSStation( { mms_station_id => $Pars{mms_station_id}, 'select-type' => 'with-person', 'order-by' => 'mprimsa_sort' } , $LOG, 1) )  {    
        foreach ($DBMMS->getData())  {
        
            next unless $_->{mms_prison_activate};
            #my %Cols = %{$_};
            my %Cols;
            
            $Cols{prison_id}          = $_->{mms_prison_id};
            $Cols{name}               = $_->{mms_prison_name};
            $Cols{year_from}          = ($date->getSplitDate($_->{mms_prison_date_from}))[0];
            $Cols{year_to}            = ($date->getSplitDate($_->{mms_prison_date_to}))[0] if $_->{mms_prison_date_to};
            $Cols{coord_lat}          = $_->{mms_prison_coord_lat}; 
            $Cols{coord_long}         = $_->{mms_prison_coord_long};

            $Cols{headline}           = $_->{mms_prison_subtitle}; 
            $Cols{desc}               = $_->{mms_prison_desc};
            $Cols{desc}               =~ s/([\n\f\r][\n\f\r])|[\n\f\r]/\<BR\>\<BR\>/sg; 
            
            $Cols{img_thumb}          = $_->{mms_prison_filename_small} ? ( $_->{mms_prison_id} . '/' . $_->{mms_prison_filename_small} ) : '';
            $Cols{img_big}            = $_->{mms_prison_filename_large} ? ( $_->{mms_prison_id} . '/' . $_->{mms_prison_filename_large} ) : '';
            if ( $LOG ) {for (sort keys %Cols){print "$_ : $Cols{$_}<br>";}}
            
#             # Dokumente
#             if ( !$arg{mode} ) {
#                 my @List;
#                 my %Vars;
#                 $Vars{'order-by'}    = 'mms_document_rubric_sort';
#                 $Vars{mms_person_id} = $_->{mms_person_id};
#                 if ( $DBMMS->selectMMSDocumentRubric(\%Vars,$LOG, 1))  {
#                     foreach ($DBMMS->getData())  {
#                         my %Cols;
#                         $Cols{doc_rubric}             = $_->{mms_document_rubric_title};
#                         $Cols{doc_rubric_id}          = $_->{mms_document_rubric_id};
#                         
#                         my @DocList;
#                         my %Values;
#                         $Values{mms_document_rubric_id} = $_->{mms_document_rubric_id};
#                         $Values{'order-by'}   = 'mms_document_sort';            
#                         if ( $DBMMS->selectMMSDocument(\%Values,$LOG, 1))  {
#                             foreach ($DBMMS->getData())  {
#                                 my %Pars;
#                                 $Pars{doc_title}    = $_->{mms_document_title};
#                                 $Pars{doc_desc}     = $_->{mms_document_desc};
#                                 $Pars{doc_id}       = $_->{mms_document_id};
#                                 $Pars{doc_filename} = $_->{mms_document_filename};
#                                 $Pars{doc_file}     = $_->{mms_document_filename} ? '<img src="/mms/img/witnesses/docs/' . $_->{mms_document_id} . '/' . $_->{mms_document_filename} . '" />' : '&nbsp;';                    
#                                 push @DocList, \%Pars;                    
#                             }
#                         }
#                         
#                         $Cols{doclist} = \@DocList;            
#             
#                         push @List, \%Cols;
#                     }
#                 }
#                 $Cols{persdoclist} = \@List;                
#             }
            
#             # Videos
#             if ( !$arg{mode} ) { 
#                 my @List;
#                 my %Vars;                                       
#                 $Vars{select_mode} = 'with-rubric';
#                 $Vars{'order-by'}  = 'mms_video_rubric_sort, mms_video_sort';
#                 $Vars{mms_person_id} = $_->{mms_person_id};     
#                 #$Vars{mms_video_visible} = 1;           
#                 my %Border;
#                 my $CUR_VIDEO_RUBRIC_ID = 0;
#                 my $LAST_VIDEO_ID       = 0;
#                 if ( $DBMMS->selectMMSVideo(\%Vars, $LOG,1) )  {
#                     foreach ($DBMMS->getData())   {
#                         my %Cols;
#                         $Cols{video_id}                   = $_->{mms_video_id};
#                         $Cols{video_title}                = $_->{mms_video_title};
#                         $Cols{video_file}                 = $_->{mms_video_filename};
#                         $Cols{video_rubric_id}            = $_->{mms_video_rubric_id};
#                         $Cols{video_rubric_title}         = $_->{mms_video_rubric_title};
#                         $Cols{mms_video_rubric_parent_id} = $_->{mms_video_rubric_parent_id};
#                         
#                         # Elternrubrik auslesen
#                         if ( $Cols{mms_video_rubric_parent_id} && $DBMMS->selectMMSVideoRubric( { mms_video_rubric_id => $Cols{mms_video_rubric_parent_id}, 'order-by' => 'mms_video_rubric_sort'}, $LOG, 1))   {
#                             foreach ($DBMMS->getData())  {
#                                  $Cols{parent_rubric_title} = $_->{mms_video_rubric_title};
#                             }
#                         }
#                                                          
#                         
#                         push @List, \%Cols;
#                     }
#                 }
#                 $Cols{persvideolist} = \@List;                
#             }            
            
            %User = %{$_};
                        
            push(@UserList, \%Cols);
        }
    }

    if ( $arg{mode} eq 'hash' ) {
        print "RETURN HASH<br>" if $LOG;
        return \%User;
    }
    else {
        return \@UserList;
    }
}

#################################################################################
# Haftort-Selektionen auslesen
#################################################################################
sub _getPrisonCollectionsSorted
{

    my %arg = (
        log     => 0,
        @_
    );

    my $LOG          = $arg{log} || 0;

    print "Haftort-Selektionen auslesen... (Mode: $MODE)<br>" if $LOG;

    my %PrisonCollection;
    my @PrisonCollectionList;

    my %Pars;
    $Pars{mms_station_id} = $arg{mms_station_id};
    if ( $arg{mms_prison_collection_id} && $arg{code} ) {
        $Pars{mms_prison_collection_id}  = $arg{mms_prison_collection_id};
        $Pars{code}                      = $arg{code}; 
    }
    $Pars{'order-by'} = $arg{'order-by'};
    if ( $DBMMS->selectMMSPrisonCollectionMMSStation( { mms_station_id => $Pars{mms_station_id}, 'select-type' => 'with-person', 'order-by' => 'mpricolmsa_sort' } , $LOG, 1) )  {    
        foreach ($DBMMS->getData())  {
        
            next unless $_->{mms_prison_collection_activate};
            #my %Cols = %{$_};
            my %Cols;
            
            $Cols{prison_collection_id}     = $_->{mms_prison_collection_id};
            $Cols{name}                     = $_->{mms_prison_collection_name};            
            $Cols{desc}                     = $_->{mms_prison_collection_desc};
            $Cols{desc}                     =~ s/([\n\f\r][\n\f\r])|[\n\f\r]/\<BR\>\<BR\>/sg;
            $Cols{label_min_zoom}           = $_->{mms_prison_collection_map_label_min_zoom}; 
            $Cols{vector_source}            = $_->{mms_prison_collection_layer_vector_source};
            $Cols{alt_tile_source}          = $_->{mms_prison_collection_layer_tile_source};
            $Cols{alt_tile_from_zoom}       = $_->{mms_prison_collection_layer_tile_from_zoom};
            $Cols{alt_tile_to_zoom}         = $_->{mms_prison_collection_layer_tile_to_zoom};
            $Cols{img_thumb}                = $_->{mms_prison_collection_filename_small} ? ( $_->{mms_prison_collection_id} . '/' . $_->{mms_prison_collection_filename_small} ) : '';
            $Cols{img_big}                  = $_->{mms_prison_collection_filename_large} ? ( $_->{mms_prison_collection_id} . '/' . $_->{mms_prison_collection_filename_large} ) : '';
            
            my $SELECT_STMT                 = $_->{mms_prison_collection_select_stmt};
            if ( $DBMMS->specialSelect( { special_select => $SELECT_STMT } , $LOG, 1) )  {
                my @List;
                my $NUM      = 0;
                my $SUM_LAT  = 0;
                my $SUM_LONG = 0;
                my $MAX_LAT  = 0;
                my $MIN_LAT  = 0;
                my $MAX_LONG = 0;
                my $MIN_LONG = 0;                
                foreach ( $DBMMS->getData() )   {
                    #my %Cols = %{$_};
                    $NUM++;
                    $SUM_LAT  += $_->{cs_prison_coord_lat};
                    $SUM_LONG += $_->{cs_prison_coord_long};
                    $MAX_LAT   = $_->{cs_prison_coord_lat} if ( !$MAX_LAT || $_->{cs_prison_coord_lat} > $MAX_LAT );
                    $MIN_LAT   = $_->{cs_prison_coord_lat} if ( !$MIN_LAT || $_->{cs_prison_coord_lat} < $MIN_LAT );
                    $MAX_LONG  = $_->{cs_prison_coord_long} if ( !$MAX_LONG || $_->{cs_prison_coord_long} > $MAX_LONG );
                    $MIN_LONG  = $_->{cs_prison_coord_long} if ( !$MIN_LONG || $_->{cs_prison_coord_long} < $MIN_LONG );                    
                    my %Cols;
                    for my $k ( keys %{$_} ) {
                        $Cols{$k} = $_->{$k} if $JSON_PARAMS{CS_PRISON}->{$k};
                    }                    
                    
                    my $SELECT_STMT_IMAGES = ' * FROM CS_FILE_ASGT, CS_FILE WHERE csfa_fk_id = ' . $_->{cs_prison_id} . ' AND csfa_type = 10 AND cs_file_id = csfa_cs_file_id ';
                    if ( $DBMMS->specialSelect( { special_select => $SELECT_STMT_IMAGES } , $LOG, 1) )  {
                        foreach ( $DBMMS->getData() )   {
                            $Cols{image_id}    = $_->{cs_file_id};
                            $Cols{image_name}  = $_->{cs_file_filename_prev};
                            $Cols{image_title} = $_->{cs_file_title};
                        }                    
                    }                                        
                    
                    push @List,\%Cols;       
                }
                #$Cols{mid_lat}     = $SUM_LAT / $NUM;
                #$Cols{mid_long}    = $SUM_LONG / $NUM;
                $Cols{mid_lat}     = ( $MAX_LAT + $MIN_LAT) / 2;
                $Cols{mid_long}    = ( $MAX_LONG + $MIN_LONG) / 2;
                $Cols{max_lat}     = $MAX_LAT;
                $Cols{min_lat}     = $MIN_LAT;
                $Cols{max_long}    = $MAX_LONG;
                $Cols{min_long}    = $MIN_LONG;
                $Cols{prison_data} = \@List;
            }               
            
            if ( $LOG ) {for (sort keys %Cols){print "$_ : $Cols{$_}<br>";}}
            
            %PrisonCollection = %{$_};
                        
            push(@PrisonCollectionList, \%Cols);
        }
    }

    if ( $arg{mode} eq 'hash' ) {
        print "RETURN HASH<br>" if $LOG;
        return \%PrisonCollection;
    }
    else {
        return \@PrisonCollectionList;
    }
}

#################################################################################
# Präsentationen auslesen
#################################################################################
sub _getPresentation
{

    my %arg = (
        log     => 0,
        @_
    );

    my $LOG          = $arg{log};

    print "MMS-Präsentation auslesen... (Mode: $MODE)<br>" if $LOG;

    my %Presentation;

    my %Pars;
    $Pars{mms_presentation_id} = $arg{mms_presentation_id};
    $Pars{code}                = $arg{code};    
    if ($DBMMS->selectMMSPresentation(\%Pars, $LOG, 1))   {
        foreach ($DBMMS->getData())  {
            if ( $arg{'return-params'} eq 'config' ) {
                for my $k ( keys %{$_} ) {
                    $Presentation{$k} = $_->{$k} if $JSON_PARAMS{PRESENTATION}->{$k};
                }
            }
            else {
                %Presentation = %{$_};
            }
            
            if ( $LOG ) {for (sort keys %Presentation){print "$_ : $Presentation{$_}<br>";}}
           
            push(@List, \%Cols);
        }
    }

    if ( $arg{mode} eq 'hash' ) {
        print "RETURN HASH<br>" if $LOG;
        return \%Presentation;
    }
    else {
        return \@List;
    }
}

#################################################################################
# Stationen auslesen
#################################################################################
sub _getStations
{

    my %arg = (
        log     => 0,
        @_
    );

    my $LOG          = $arg{log} || 0;

    print "MMS-Stationen auslesen... (Mode: $MODE)<br>" if $LOG;

    my %Station;
    my @List;

    my %Pars;
    $Pars{mms_station_id}      = $arg{mms_station_id};
    $Pars{mms_presentation_id} = $arg{mms_presentation_id};
    $Pars{code}                = $arg{code};    
    $Pars{select_mode}         = $arg{select_mode} || 'with-project';
    $Pars{'order-by'}          = $arg{'order-by'} || 'mms_project_title, mms_station_title';
    
    if ($DBMMS->selectMMSStation(\%Pars, $LOG, 1))   {
        foreach ($DBMMS->getData())  {
            %Station = %{$_};
            my %Cols;
            if ( $arg{'return-params'} eq 'config' ) {
                for my $k ( keys %{$_} ) {
                    $Cols{$k} = $_->{$k} if $JSON_PARAMS{STATION}->{$k};
                }
            }
            else {
                %Cols = %{$_};
            }            
            
            if ( $Station{mms_station_content_type} == 2 ) {
                    $Cols{prisons}  = _getPrisonsSorted( mms_station_id => $Station{mms_station_id}, 'order-by' => 'mms_person_sort, mms_person_lastname', log => $LOG );            
            }
            elsif ( $Station{mms_station_content_type} == 3 ) {
                    $Cols{prison_collections}  = _getPrisonCollectionsSorted( mms_station_id => $Station{mms_station_id}, 'order-by' => 'mms_person_sort', log => $LOG );            
            }
            else {
                if ( $arg{'with-person'} ) {
                    $Cols{witnesses}  = _getWitnessesSorted( mms_station_id => $Station{mms_station_id}, 'order-by' => 'mms_person_sort, mms_person_lastname', log => $LOG );            
                }
                elsif ( $arg{'with-single-person'} && $arg{mms_person_id} ) {
                    $Cols{witnesses}  = _getWitnessesSorted( mms_station_id => $Station{mms_station_id}, mms_person_id => $arg{mms_person_id}, 'order-by' => 'mms_person_sort, mms_person_lastname', log => $LOG );
                }
            }
            
            if ( $LOG ) {for (sort keys %Station){print "$_ : $Station{$_}<br>";}}
           
            push(@List, \%Cols);
        }
    }

    if ( $arg{mode} eq 'hash' ) {
        print "RETURN HASH<br>" if $LOG;
        return \%Station;
    }
    else {
        return \@List;
    }
}

#################################################################################
# Video-Rubriken auslesen
#################################################################################
sub _getVideoRubrics()
{

    my %arg = (
        log     => 0,
        @_
    );

    my $LOG          = $arg{log} || 0;

    print "MMS-Videorubriken auslesen... (Mode: $MODE)<br>" if $LOG;

    my %Rubric;
    my @List;

    my %Pars;
    $Pars{mms_project_id} = $arg{mms_project_id};
    $Pars{mms_station_id} = $arg{mms_station_id};
    $Pars{level}          = 1;
    $Pars{'order-by'}     = 'mms_video_rubric_sort' || $arg{'order-by'};
    if ($DBMMS->selectMMSVideoRubric(\%Pars, $LOG, 1))   {
        foreach ($DBMMS->getData())  {
            #my %Cols = %{$_};

            my %Cols;
            $Cols{rubric_id}    = $_->{mms_video_rubric_id};
            $Cols{rubric_title} = $_->{mms_video_rubric_title};
            
            if ( $LOG ) {for (sort keys %Cols){print "$_ : $Cols{$_}<br>";}}
            
            # Unterrubriken auslesen
            if ($DBMMS->selectMMSVideoRubric( { mms_video_rubric_parent_id => $Cols{rubric_id}, 'order-by' => 'mms_video_rubric_sort'}, $LOG, 1))   {
                my @ChildList;
                foreach ($DBMMS->getData())  {
                    my %Vals;
                    $Vals{rubric_id}    = $_->{mms_video_rubric_id};
                    $Vals{rubric_title} = $_->{mms_video_rubric_title};   
                    
                    if ( $DBMMS->selectMMSPersonMMSStation( { mms_station_id => $Pars{mms_station_id}, mms_video_rubric_id => $Vals{rubric_id}, 'select-type' => 'with-person-and-video', 'order-by' => 'mpmsa_sort' } , $LOG, 1) )  {    
                        my @PersonList;
                        foreach ($DBMMS->getData())  {
                            my %Values;
                            $Values{pers_id}          = $_->{mms_person_id};
                            $Values{pers_lastname}    = $_->{mms_person_lastname};
                            $Values{pers_firstname}   = $_->{mms_person_firstname};
                            $Values{video_id}         = $_->{mms_video_id};
                            $Values{video_file}       = $_->{mms_video_filename};
                            #print "$Values{pers_lastname}<br>";
                            push @PersonList, \%Values;                        
                        }
                        $Vals{personlist} = \@PersonList if @PersonList;                    
                    }                       
                                     
                    push @ChildList, \%Vals;
                }
                $Cols{childlist} = \@ChildList if @ChildList;
            }
                        
            # wenn keine Unterrubriken existieren => Videos und Personen auslesen
            if ( !$Cols{childlist} ) {
                if ( $DBMMS->selectMMSPersonMMSStation( { mms_station_id => $Pars{mms_station_id}, mms_video_rubric_id => $Cols{rubric_id}, 'select-type' => 'with-person-and-video', 'order-by' => 'mpmsa_sort' } , $LOG, 1) )  {    
                    my @PersonList;
                    foreach ($DBMMS->getData())  {
                        #print "$_->{mms_person_lastname} : $_->{mms_video_id}<br>";
                        my %Vals;
                        $Vals{pers_id}          = $_->{mms_person_id};
                        $Vals{pers_lastname}    = $_->{mms_person_lastname};
                        $Vals{pers_firstname}   = $_->{mms_person_firstname};
                        $Vals{video_id}         = $_->{mms_video_id};
                        $Vals{video_file}       = $_->{mms_video_filename};
                        push @PersonList, \%Vals;                        
                    }
                    $Cols{personlist} = \@PersonList if @PersonList;                    
                }                            
            }               
            

           
            push(@List, \%Cols);
        }
    }

    if ( $arg{mode} eq 'hash' ) {
        print "RETURN HASH<br>" if $LOG;
        return \%Cols;
    }
    else {
        return \@List;
    }
}

#################################################################################
# Glossar auslesen
#################################################################################
sub _getGlossary
{

    my %arg = (
        log     => 0,
        @_
    );

    my $LOG          = $arg{log};

    print "Glossar auslesen... (Mode: $MODE)<br>" if $LOG;

    my %Glossar;
    my @List;

    if ( $DBMMS->selectMMSGlossary( { project_id => 1 } , $LOG, 1))   {
        foreach ($DBMMS->getData())  {
            my %Cols;

            $Cols{term}       = $_->{mms_glossary_term};
            $Cols{type}       = $_->{mms_glossary_type};
            $Cols{definition} = $_->{mms_glossary_definition};

            if ( $LOG ) {for (sort keys %Cols){print "$_ : $Cols{$_}<br>";}}
                        
            push(@List, \%Cols);
            
            if ( $_->{mms_glossary_term_variants} ) {
                 my $variants = $_->{mms_glossary_term_variants};
                 $variants    =~ s/^\s*(.*\S)?\s*$/$1/;
                 my @variant_list = split /\s*;\s*/, $variants;
                 for my $term ( @variant_list ) {
                    my %Vals = %Cols;
                    $Cols{term} = $term;
                    push(@List, \%Vals);                   
                 }                 
            }
        }
    }

    if ( $MODE eq 'hash' ) {
        print "RETURN HASH<br>" if $LOG;
        return \%Glossar;
    }
    else {
        return \@List;
    }
}

#################################################################################
# Template ermitteln
#################################################################################
sub _getTemplate()
{

    my %arg = (
        log     => 0,
        @_
    );

    my $LOG          = $arg{log} || 0;

    my $TEMPLATE;
    my $SITE = $arg{site};
    
    print "site = $SITE<br>station_id = $arg{station_id}<br>" if $LOG;
    
    my $TmplFile = $Config->getValue($CONFIG_TMPL_KEY);    
    
    if ( $arg{station_id} && $Params{theme} ) {
        if ( $CONFIG{template_station}->{$arg{station_id}}->{$Params{theme}} ) {
            print "lokal definiertes Stationstemplate: $CONFIG{template_station}->{$arg{station_id}}->{$Params{theme}}<br>" if $LOG;
            $TEMPLATE  = $CONFIG{template_path}->{$Params{theme}} . '/'; 
            $TEMPLATE .= $CONFIG{template_station}->{$arg{station_id}}->{$Params{theme}};
            my $TmplFileWithPath = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TEMPLATE;
            print "Datei: $TmplFileWithPath<br>" if $LOG; 
            if ( -e $TmplFileWithPath ) {
                print "lokal konfiguriertes Template vorhanden<br>" if $LOG;
                return $TEMPLATE; 
            }             
            else {
                print "lokal konfiguriertes Template nicht vorhanden<br>";
                return undef;            
            }
        }
        elsif ( $CONFIG{template_station_default_by_theme}->{$Params{theme}} ) {
            print "lokal definiertes Default-Stationstemplate: $CONFIG{template_station_default_by_theme}->{$Params{theme}}<br>" if $LOG;
            $TEMPLATE  = $CONFIG{template_path}->{$Params{theme}} . '/'; 
            $TEMPLATE .= $CONFIG{template_station_default_by_theme}->{$Params{theme}};
            my $TmplFileWithPath = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TEMPLATE;
            print "Datei: $TmplFileWithPath<br>" if $LOG; 
            if ( -e $TmplFileWithPath ) {
                print "lokal konfiguriertes Default-Stations-Themen-Template vorhanden<br>" if $LOG;
                return $TEMPLATE; 
            }             
            else {
                print "lokal konfiguriertes Default-Stations-Themen-Template nicht vorhanden<br>";
                return undef;            
            }
        }        
        else {
            return undef;
        }
    }
    elsif ( $Params{mms_person_id} ) {
        my $SELECT_STMT  = ' mms_presentation_template FROM MMS_PERSON_MMS_STATION_ASGT, MMS_PRESENTATION_MMS_STATION_ASGT, MMS_PRESENTATION ';
           $SELECT_STMT .= ' WHERE mpmsa_person_id = ' . $Params{mms_person_id};
           $SELECT_STMT .= ' AND MMS_PRESENTATION_MMS_STATION_ASGT.mpmsa_station_id = MMS_PERSON_MMS_STATION_ASGT.mpmsa_station_id ';
           $SELECT_STMT .= ' AND mms_presentation_id = MMS_PRESENTATION_MMS_STATION_ASGT.mpmsa_presentation_id ';
           $SELECT_STMT .= ' GROUP BY mpmsa_person_id ';

        if ( $DBMMS->specialSelect( { special_select => $SELECT_STMT } , $LOG, 1) )  {
            foreach ( $DBMMS->getData() )   {
                my $TMPL_PERSON = $TMPL_DIR_MMS_PORTAL . '/' . $_->{mms_presentation_template};
                my $TmplFilePerson = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_PERSON;
                print "konfiguriertes Präsentationsstemplate: $TmplFilePresentation\n" if $LOG;
                if ( -e $TmplFilePerson ) {
                    print "in DB konfiguriertes Präsentationsstemplate vorhanden\n" if $LOG;
                    #return $TmplFilePerson;
                    return $TMPL_PERSON;
                }
                else {
                    print "in DB konfiguriertes Präsentationsstemplate nicht vorhanden<br>";
                    return undef;
                }
            }
        }
    }
    elsif ( $Params{theme} ) {
        $TEMPLATE  = $CONFIG{template_path}->{$Params{theme}} . '/'; 
        $TEMPLATE .= $CONFIG{templates}->{$SITE}->{$Params{theme}} || $CONFIG{template}->{$SITE}->{default};
    }
    else {
        $TEMPLATE = $CONFIG{template_path}->{default} . '/' . $CONFIG{template}->{$SITE}->{default};    
    }
    
    return $TEMPLATE;
}

#################################################################################
# Präsentations-Template ermitteln
#################################################################################
sub _getPresentationTemplate()
{

    my %arg = (
        log     => 0,
        @_
    );

    my $LOG          = $arg{log};

    my $TEMPLATE;
    my %PRESENTATION = %{$arg{presentation}};
    
    #print keys %PRESENTATION;
    print "sub _getPresentationTemplate: PRESENTATION = $PRESENTATION{mms_presentation_id}<br>Template?: $PRESENTATION{mms_presentation_template}<br>" if $LOG;
    
    my $TmplFile = $Config->getValue($CONFIG_TMPL_KEY);  
    
    if ( $PRESENTATION{mms_presentation_template} ) {
        print "In DB definiertes Präsentationsstemplate: $PRESENTATION{mms_presentation_template}<br>" if $LOG;
        my $TMPL_PRESENTATION = $TMPL_DIR_MMS_PORTAL . '/' . $PRESENTATION{mms_presentation_template};
        my $TmplFilePresentation = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TMPL_PRESENTATION;        
        print "konfiguriertes Präsentationsstemplate: $TmplFilePresentation\n" if $LOG;
        if ( -e $TmplFilePresentation ) {
            print "in DB konfiguriertes Präsentationsstemplate vorhanden\n" if $LOG;
            return $TmplFilePresentation;  
        }             
        else {
            print "in DB konfiguriertes Präsentationsstemplate nicht vorhanden<br>";
            return undef;            
        }    
    }  
    
    exit;
    
    if ( $arg{station_id} && $Params{theme} ) {
        if ( $CONFIG{template_station}->{$arg{station_id}}->{$Params{theme}} ) {
            print "lokal definiertes Stationstemplate: $CONFIG{template_station}->{$arg{station_id}}->{$Params{theme}}<br>" if $LOG;
            $TEMPLATE  = $CONFIG{template_path}->{$Params{theme}} . '/'; 
            $TEMPLATE .= $CONFIG{template_station}->{$arg{station_id}}->{$Params{theme}};
            my $TmplFileWithPath = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TEMPLATE;
            print "Datei: $TmplFileWithPath<br>" if $LOG; 
            if ( -e $TmplFileWithPath ) {
                print "lokal konfiguriertes Template vorhanden<br>" if $LOG;
                return $TEMPLATE; 
            }             
            else {
                print "lokal konfiguriertes Template nicht vorhanden<br>";
                return undef;            
            }
        }
        elsif ( $CONFIG{template_station_default_by_theme}->{$Params{theme}} ) {
            print "lokal definiertes Default-Stationstemplate: $CONFIG{template_station_default_by_theme}->{$Params{theme}}<br>" if $LOG;
            $TEMPLATE  = $CONFIG{template_path}->{$Params{theme}} . '/'; 
            $TEMPLATE .= $CONFIG{template_station_default_by_theme}->{$Params{theme}};
            my $TmplFileWithPath = $TmplFile . ($TmplFile !~ /\/$/ ? '/' : '') . $TEMPLATE;
            print "Datei: $TmplFileWithPath<br>" if $LOG; 
            if ( -e $TmplFileWithPath ) {
                print "lokal konfiguriertes Default-Stations-Themen-Template vorhanden<br>" if $LOG;
                return $TEMPLATE; 
            }             
            else {
                print "lokal konfiguriertes Default-Stations-Themen-Template nicht vorhanden<br>";
                return undef;            
            }
        }        
        else {
            return undef;
        }
    }
    elsif ( $Params{theme} ) {
        $TEMPLATE  = $CONFIG{template_path}->{$Params{theme}} . '/'; 
        $TEMPLATE .= $CONFIG{templates}->{$SITE}->{$Params{theme}} || $CONFIG{template}->{$SITE}->{default};
    }
    else {
        $TEMPLATE = $CONFIG{template_path}->{default} . '/' . $CONFIG{template}->{$SITE}->{default};    
    }
    
    return $TEMPLATE;
}

#########################################################################
# Präsentationen als JSON-Objekt ausgeben
#########################################################################
sub loadJSONPresentationData
{
    my $LOG = 0;
    print $CGI->header('application/json');

    if ( ( !$Params{mms_presentation_id} && !$Params{device_id} ) || !$Params{code} ) {
        exit; 
    }
    
    if ( $Params{device_id} ) {
        $DBMMS->insertMMSLog( { 'ip_address' => $ENV{REMOTE_ADDR}, 'device_id' => $Params{device_id}, mms_log_type => 2 } , $LOG);    
        my %Vals;
        $Vals{special_select} = ' *, PASSWORD(mms_presentation_id) AS CODE FROM MMS_STATION_PHYSICAL LEFT JOIN MMS_PRESENTATION ON mms_presentation_id = mms_station_physical_presentation_id WHERE mms_station_physical_id = ' . $Params{device_id};
        if ( $DBMMS->specialSelect(\%Vals,$LOG, 1))  {
            foreach ($DBMMS->getData())  {
                my %Cols = %{$_};
                if ( $Cols{CODE} ) {
                    $Params{mms_presentation_id} = $Cols{mms_presentation_id};
                    $Params{code}                = $Cols{CODE};
                    last;    
                }
            }
        }    
    }
    
    my %Pars;
    my %Cols;
    # Presentation 
    $Cols{presentation}    = _getPresentation( mode => 'hash', 'return-params' => 'config' , mms_presentation_id => $Params{mms_presentation_id}, code => $Params{code}, log => $LOG );
    
    # Projekte
    my @ProjectList;
    $Pars{special_select} = ' DISTINCT(mms_station_project_id), MMS_PROJECT.* FROM MMS_STATION, MMS_PRESENTATION_MMS_STATION_ASGT, MMS_PROJECT WHERE mms_station_id = mpmsa_station_id AND mpmsa_presentation_id = ' . $Params{mms_presentation_id} . ' AND mms_project_id = mms_station_project_id ORDER BY mms_project_sort';
    if ( $DBMMS->specialSelect(\%Pars, $LOG, 1) )  {
        foreach ( $DBMMS->getData() )   {
            my %Cols;

            for my $k ( keys %{$_} ) {
                $Cols{$k} = $_->{$k} if $JSON_PARAMS{PROJECT}->{$k};
            }
            
            if ( $LOG ) {for (sort keys %Cols){print "$_ : $Cols{$_}<br>";}}
           
            push(@ProjectList, \%Cols);            
        }
    }  
    
    $Cols{project}   = \@ProjectList;
    $Cols{station}   = _getStations( 'with-person' => true, 'order-by' => 'mms_station_project_id, mpmsa_sort', 'return-params' => 'config', select_mode => 'by-presentation-id', mms_presentation_id => $Params{mms_presentation_id}, log => $LOG );
    $Cols{glossary}  = _getGlossary( mode => 'hash', log => $LOG );
    
	my $obj = \%Cols;
    my $js = JSON->new->allow_blessed->convert_blessed->encode( $obj );
    print $js;
    exit;
}