#!/usr/bin/perl -w

BEGIN {
	push(@INC, "/home/th/projects/sp");
}

use strict;

use DBI;
use CGI;
use HTML::Template::Expr;
use HTML::Template;
use HTML::Clean;
use Cache::FileCache;
use Data::Dumper;
use Number::Format qw(:subs :vars);
use Crypt::Blowfish();
use MIME::Lite;
use Sys::UniqueID;
use Date::Format;
use URI::Escape;
use Image::Size;
use Date::Format;

use soplex::saferpay::MessageFactory;

use Crapoud::Apache::Session;
use Crapoud::Util::EnterData qw(:subs);

######################### CONFIG ##############################################

my $db_source = 'dbi:mysql:host=localhost;database=sp';
my $db_user = "admin";
my $db_pass = "o73ax6";
my @class = qw(home faq cart contact series);
my $sales_mail_from = '"meyposters" <sales@meyposters.com>';
my $mail_from = '"meyposters" <info@meyposters.com>';
my $mailman = 'meyposters-news-request@mailman.crapoud.com';
my $mail_order = 'm.mey@meyposters.com, t.hartwig@crapoud.com';
##$mail_order = 't.hartwig@crapoud.com';
my $default_language = "";
if($ENV{HTTP_HOST} =~ /.de$/i) {
	$default_language = "DE";
} else {
	$default_language = "ENG";
}
my $path_saferpay = "/opt/saferpay/out-sp";
my $path_base = "/home/th/projects/sp/public_html";

###############################################################################


#### get the right section of the request
my %cache_imgsize = ();
my @section = ("home");

## map { warn("$_: ", $ENV{$_}); } sort(keys(%ENV));

my $path_info = $ENV{REQUEST_URI};
$path_info =~ s/(\?|\#).*$//i;

@section = split(/\/+/, $2) if( ( $ENV{REQUEST_URI} ) =~ /^(.*)\/?index\d?\/([^\?]*)/);
$section[0] = ( defined $section[0] ) ? $section[0] : $class[0];

#### initializing the core script objects
my $query = new CGI();
my $session = Crapoud::Apache::Session->new();
my $formater= new Number::Format();
my $request = ();

###--- ADVERT HANDLING --------------------------------------------------------
if($section[0] =~ /^(adv)$/i || $path_info =~ /^(\/adv)\/([^\/]+)(\/.*)*$/i ) {
	print $query->redirect("http://$ENV{HTTP_HOST}" . ((defined $2 && defined $3) ? $3 : "" ));
	my $dbh = DBI->connect($db_source,$db_user,$db_pass);
	my $affiliate = ((defined $2) ? $2 : $section[1] );
	my $haendler =  ReadData("Haendler", $dbh, "select id from Haendler where Affiliate='" . $affiliate . "'");
	$affiliate = ( (defined $haendler->[0]) ? $haendler->[0]->{"id"} : $affiliate );
	my %hash = ();
	$hash{id_Haendler_Affiliate} = $affiliate;
	$hash{HTTP_REFERER} = ( ( defined $ENV{HTTP_REFERER} ) ? $ENV{HTTP_REFERER} : "" );
	$hash{Session} = $session->getid();
	EnterData("Affiliatehit", \%hash, $dbh);
	## mailout('t.hartwig@crapoud.com', \%hash, "adv");
	$session->set("ADVERT" => $affiliate ); # TODO: turn off
	if( ! defined $session->get("ADVERT")) {
		$session->set("ADVERT" => $affiliate );
	}
	finish($session);
	exit(0);
} elsif ( ! defined $session->get("ADVERT") ) {
	$session->set("ADVERT" => "NULL");
}



###--- SECURITY SETTING IF SITES ARE SWITCHED WITH THE SESSION ID -------------
$session->set("REMOTE_ADDR" => $ENV{REMOTE_ADDR});
$session->set("HTTP_USER_AGENT" => $ENV{HTTP_USER_AGENT});

###--- LANGUAGE SETTING -------------------------------------------------------
my $lang = ( defined $query->param('lang') ) ? uc($query->param('lang')) : $default_language;
if($lang) {
	$session->set("LANGUAGE" => $lang);
} else {
	$lang = ( defined $session->get('LANGUAGE') ) ? $session->get('LANGUAGE') : $default_language;
}
if($lang eq "DE") {
	$formater = new Number::Format(
		-decimal_point => ',',
		-thousands_sep => '.'
	);
} else {
	$formater = new Number::Format(
		-decimal_point => '.',
		-thousands_sep => ','
	);
}
my $formater_english = new Number::Format(
	-decimal_point => '.',
	-thousands_sep => ','
);


###--- DATABASE INIT ----------------------------------------------------------
my $posters = ();
my $dbh = DBI->connect($db_source,$db_user,$db_pass);
my $stmt = 'SELECT P.*, CONCAT(F.FirstName, " ", F.LastName) as Photograph, S.Series FROM Poster P LEFT JOIN Photograph F ON P.id_Photograph_LastName=F.id LEFT JOIN Series S ON S.id=P.id_Series_Series WHERE Public=1 ORDER BY Dummy, ArticleNumber';
my $sth = $dbh->prepare($stmt);
$sth->execute;
$posters = $sth->fetchall_hashref('id');

my $countries = ();
$sth = $dbh->prepare("select * from Land order by Priority, Land");
$sth->execute();
$countries = $sth->fetchall_hashref("Land");
$session->set("Form_Address_Country" => "GERMANY") unless defined $session->get("Form_Address_Country");
$session->set("Form_Address_CountryD" => "GERMANY") unless defined $session->get("Form_Address_CountryD");

my $payments = ();
$payments =  ReadData("Zahlungsart", $dbh, "select * from Zahlungsart order by Nummer");
$session->set("PAYMENTS", $payments);

my $popups = ();
$popups = ReadData("Popup", $dbh, "select * from Popup order by Number ASC");
$session->set("POPUPS", $popups);

my $serieses = ();
$stmt = 'SELECT * from Series where Active';
$sth = $dbh->prepare($stmt);
$sth->execute;
$serieses = $sth->fetchall_hashref('id');


###--- PAYMENT HANDLING WITH SAFERPAY OR OTHERS -------------------------------
### get results from saferpay and decode the cipher created for the back url of saferpay
if($section[0] eq "success") {
	if(defined $session->get("KEY")) {
		my $noerror = 1;
		my $cipher = new Crypt::Blowfish $session->get("KEY");
		my $MO = undef;		
		if(! defined $query->param("lpay")) {
			### tests the saferpay return message			
			my $MF = soplex::saferpay::MessageFactory->new();
			$MF->Open($path_saferpay);
			my($data, $signature) = ($query->param("DATA"), $query->param("SIGNATURE"));
			### real test 1. saferpay and 2. session crypting
			if(eval{ $MO = $MF->VerifyPayConfirm($data, $signature); }) {
				$noerror = 1;
			} else {
				$noerror = 0;
			}			
		} else {
			if($query->param("lpay") ne $session->get("Form_Address_Zahlungsart")) {
				$noerror = 0;
			}
		}

		if(defined $section[1] && substr($session->get("KEY"),0,8) eq $cipher->decrypt(pack("H16", lc($section[1]))) ) {
			$noerror = 1;
		} else {
			$noerror = 0;
		}
		##warn($section[1]);

		if($noerror) {
			### enter the date into the database
			my $dbh = DBI->connect($db_source,$db_user,$db_pass);

			my %hash_address = ();
			foreach my $key ($session->keys()) {
				next if $key !~ /^Form_Address/;
				( my $newkey = $key ) =~ s/Form_Address_//;
				$hash_address{$newkey} = $session->get($key);
			}
			### enter the address
			my %hash_cart = ();
			my $time = time;
			$hash_cart{Number} = $session->get("ORDERID"); 
			$hash_address{Language} = $lang;
			$hash_address{Session} = $session->getid();
			$hash_cart{id_Haendler_Affiliate} = $session->get("ADVERT") if defined $session->get("ADVERT");
			$hash_cart{Session} = $session->getid();
			$hash_cart{id_Address_LastName} = EnterData("Address", \%hash_address, $dbh);
			$hash_cart{id_Zahlungsart_Zahlungsart} = $session->get("Form_Address_id_Zahlungsart");
			foreach(@{$session->get("PAYMENTS")}) {
				if($hash_cart{id_Zahlungsart_Zahlungsart} eq $_->{id}) {
					$session->set("Form_Address_HowToPay" => $_->{Zahlungsart});
					last;
				}
			}
			### enter the cart
			if(defined $MO) {
				$hash_cart{Currency} = $MO->GetAttribute("CURRENCY");
				$hash_cart{Amount} = $MO->GetAttribute("AMOUNT");
			} else {
				$hash_cart{Currency} = $session->get("CURRENCY");
				$hash_cart{Amount} = $session->get("PRICE_END") * 100;
			}			
			$hash_cart{CostShipIncluded} = $session->get("PRICE_SHIP") * 100;
			$hash_cart{CostExtraIncluded} = $session->get("PRICE_EXTRA") * 100;
			$hash_cart{Payment} = "1" if defined $MO;
			my $cartid = EnterData("Cart", \%hash_cart, $dbh);
			### enter the posters
			my @CART_LOOP = ();
			my $counter = 1;
			foreach my $key (keys(%{$session->get("CART")})) {
				next if $key =~ /COUNT/;
				my %hash_poster = ();
				$hash_poster{CART_COUNTER} = $counter++;
				$hash_poster{id_Cart_id} = $cartid;
				$hash_poster{id_Poster_ArticleNumber} = $key;
				$hash_poster{Count} = $session->get("CART")->{$key};
				$hash_poster{Currency} = $session->get("CURRENCY");
				my %hash = ();
				if(defined $posters->{$key}) {
					foreach(keys(%{$posters->{$key}})) {
						$hash{$_} = $posters->{$key}->{$_};
					}
				}
				$hash_poster{ArticleNumber} = $hash{ArticleNumber};
				if( defined $session->get("PRICE") && defined $session->get("PRICE")->{$key} ) {
				    $hash_poster{Amount} = $session->get("PRICE")->{$key};
                } else {
				    $hash_poster{Amount} = $hash_poster{Count} * $hash{"Preis" . $hash_poster{Currency}};
                }
				$hash_poster{WithSignature} = ( defined $session->get("SIGNATURE") && grep {/^$key$/} keys(%{$session->get("SIGNATURE")})) ? "1" : "";
				$hash_poster{WithFootprint} = ( defined $session->get("FOOTPRINT") && grep {/^$key$/} keys(%{$session->get("FOOTPRINT")})) ? "1" : ""; 
				## $hash_poster{Count} * $hash{"Preis" . $hash_poster{Currency}};
				EnterData("CartItem", \%hash_poster, $dbh);
				push(@CART_LOOP, \%hash_poster);
			}
			### $session->expire();
			### mail out the order to office
			### mail out to customer
			my $mail = HTML::Template::Expr->new( filename => "templates/mailout.txt",
			global_vars => 1,
			path => [ '/home/th/projects/sp/public_html' ],
			die_on_bad_params => 0,
			loop_context_vars => 1);
			my $mailx = HTML::Template::Expr->new( filename => ( $lang ne "ENG") ? "templates/$lang/thanks.txt" : "templates/thanks.txt",
			global_vars => 1,
			path => [ '/home/th/projects/sp/public_html' ],
			die_on_bad_params => 0,
			loop_context_vars => 1);
			##warn(":::::::", Dumper(@CART_LOOP));
			$mail->param("CART_LOOP" => \@CART_LOOP, "HTTP_HOST" => $ENV{HTTP_HOST});
			$mail->param("AMOUNT" => $hash_cart{Amount});
			$mailx->param("CART_LOOP" => \@CART_LOOP, "HTTP_HOST" => $ENV{HTTP_HOST});
			$mailx->param("BANK" =>  ($session->get("Form_Address_HowToPay") =~ /(berweisung|money transfer)/i) ? 1 : 0);
			$mailx->param("AMOUNT_DISPLAY" => $formater->format_number($hash_cart{Amount}/100, 2, 2));
			$mailx->param("ORDER" => $hash_cart{Number});
			foreach my $key ($session->keys()) {
				next if $key !~ /^Form_Address/;
				$mail->param($key => $session->get($key));
				$mailx->param($key => $session->get($key));
			}
			my $msg = MIME::Lite->new(
				From     =>$sales_mail_from, 
				To       =>$mail_order,
				Subject  =>'[ meyposters ] ORDER ' . $hash_cart{Number},
				Data     => $mail->output()
			);
			$msg->send;

			my $msgx = MIME::Lite->new(
				From     =>$sales_mail_from, 
				To       =>$hash_address{Email},
				Subject  =>'[ meyposters ] ' . $hash_cart{Number},
				Data     => $mailx->output()
			);
			$msgx->send;

			print $query->redirect("http://$ENV{HTTP_HOST}/index/thanks-redirect?input=SESSION_CLEAR");
			finish($session);
		}		
	}	
	print $query->redirect("http://$ENV{HTTP_HOST}/index/error");
	finish($session);
}


#### series 
my $series = ( defined $section[2] && $section[0] eq "series" ) ? uc($section[2]) : undef;
if(defined $series) {
	$session->set("SERIES_SELECTED" => $series);
}

### --  loading the TEMPLATE  of the selected section or a view template ------
my $file = ( -e "$path_base/templates/$lang/$section[0].html" ) ? "templates/$lang/$section[0].html" : "templates/$section[0].html";
## loading different if view parameter is given
$file = ($section[0] ne "review" && defined $query->param("view") && $query->param("view") =~ /\.html$/) ? $query->param("view") : $file;
## loading different if section eq text
## $file = ($section[0] eq "text" && defined $section[1]) ? "templates/$lang/$section[1].html" : $file;
my $tmpl = "";
eval {
	$tmpl = HTML::Template::Expr->new( filename => $file,
	path => [ $path_base ],
	global_vars => 1,
	strict => 0,
	die_on_bad_params => 0,
	loop_context_vars => 1);
};
if($@) {
	warn($@);
	print $query->header(-status=>404);
	exit(0);
}

#### initializing the input parameter and handle the inputs
my $input = ( defined $query->param('input') ) ? $query->param('input') : "";
if($input =~ /SESSION_CLEAR/) {
	$session->set("COUNT" => undef);
	$session->set("CART" => undef);
	$session->set("PRICE" => undef);
	$session->set("PAYMENTS" => undef);
	$session->set("SIGNATURE" => undef);
	$session->set("FOOTPRINT" => undef);
	$session->set("POPUPS" => undef);
################################# handle cart #################################
} elsif ($input eq "add") {

	my $key = $query->param('id');
	if(! defined $session->get("CART")) {
		my %hash;
		$hash{$key} = $query->param('count');
		$session->set("CART" => \%hash);
		$session->set("SIGNATURE" => \%hash);
		$session->set("FOOTPRINT" => \%hash);
		$session->set("BACKREF" => \%hash);
		$session->set("PRICE" => \%hash);
		$session->get("CART")->{COUNT} = $query->param('count');
	} else {
		$session->get("CART")->{$key} += $query->param('count');
		$session->get("CART")->{COUNT} += $query->param('count');
	}

	$session->get("BACKREF")->{$key} = $query->param('backref') if defined $session->get("BACKREF");

	if(defined  $query->param('WithSignature') && defined $key) {
		if(defined $session->get("SIGNATURE")) {
			$session->get("SIGNATURE")->{$key} = 1;
		} else {
			$session->set("SIGNATURE" => { $key => 1 } );
		}
	}
	if(defined  $query->param('WithFootprint') && defined $key) {
		if(defined $session->get("FOOTPRINT")) {
			$session->get("FOOTPRINT")->{$key} = 1;
		} else {
			$session->set("FOOTPRINT" => { $key => 1 } );
		}
	}
################################# handle address ##############################
} elsif ($input =~ /refresh|aktualisieren/i) {
	my %params = $query->Vars;
	$session->set("CART" => undef);
	$session->set("CART" => { 'COUNT' => 0 });
	foreach my $key (keys(%params)) {
		if($key =~ /^COUNT_(.*)$/) {
			next if $params{$key} < 1;
			$session->get("CART")->{$1} = $params{$key};
			$session->get("CART")->{COUNT} += $params{$key};
		}
		if($key =~ /^PRICE_(.*)$/) {
			next if $params{$key} < 1;
			my %hash = ();
			$session->set("PRICE" => \%hash) if ! defined $session->get("PRICE");
			$session->get("PRICE")->{$1} = $params{$key};
		}
	}
} elsif ($input eq "address") {
	my %params = $query->Vars;
	$session->set("ADDRESS_OK" => undef);

	foreach my $key ($session->keys()) {
		next if $key !~ /^Form_Address/;
		$key =~ s/_Must$//;
		$session->set($key => undef);
	}

	if(exists $params{action} && $params{action} =~ /(Clear|Löschen)/i) {
		print $query->redirect("http://$ENV{HTTP_HOST}/index/address");
		finish($session);
	}

    if($params{Form_Address_LastName_Must} =~ /^\!.*\!$/ && $params{Form_Address_LastName_Must} =~ s/^\!(.*)\!$/$1/) {
        $session->set("ADMIN_OK" => 1);
    }

	my $error = 0;
	foreach my $key (%params) {
		my $keycopy = $key;
		$keycopy =~ s/_Must$//;
		if($key =~ /_Must$/ && ! $params{$key}) {
			$tmpl->param("ERROR" => "ERROR");
			$error = 1;
		}
		if($keycopy eq "Form_Address_Zahlungsart") {
			foreach my $h (@{$session->get("PAYMENTS")}) {
				if($h->{Zahlungsart} eq $params{$key}) {
					$session->set("Form_Address_id_Zahlungsart" => $h->{id});
					if(defined $h->{CostsEUR} && $h->{CostsEUR} > 0) {
						$session->set("PRICE_EUR_PAYMENT" => $h->{CostsEUR});
					} else {
						$session->set("PRICE_EUR_PAYMENT" => undef);
					}
					if(defined $h->{CostsUSD} && $h->{CostsUSD} > 0) {
						$session->set("PRICE_USD_PAYMENT" => $h->{CostsUSD});
					} else {
						$session->set("PRICE_USD_PAYMENT" => undef);
					}
					last;
				}
			}
		}
		if($keycopy eq "Form_Address_Country") {
			my $d = ( defined $params{"Form_Address_CountryD"} && $params{"Form_Address_AddressDelivery"} ) ? $params{"Form_Address_CountryD"} : $params{$key};
			$session->set("PRICE_FEDEX_SHIP" => $countries->{$d}->{ShippingFedex});
			$session->set("PRICE_POST_SHIP" => $countries->{$d}->{ShippingPost});
		}
		$session->set($keycopy => $params{$key});
	}

	if(! $error) {
		$session->set("ADDRESS_OK" => 1);
	}

	if(exists $params{action} && $params{action} =~ /(Back|Zurück)/) {
		print $query->redirect("http://$ENV{HTTP_HOST}/index/cart");
		finish($session);
	}

	if(exists $params{action} && $params{action} =~ /(Next|Weiter) /i) {
		if($error) {
			$tmpl->param("ERROR" => "ERROR");
			$input = "";
		} else {
			print $query->redirect("http://$ENV{HTTP_HOST}/index/review");
			finish($session);
		}
	}
} elsif ($input eq "review") {
	my %params = $query->Vars;
	$session->set("REVIEW_OK" => undef);

	foreach my $key ($session->keys()) {
		next if $key !~ /^Form_Review/;
		$key =~ s/_Must$//;
		$session->set($key => undef);
	}

	my $error = 0;
	foreach my $key (%params) {
		my $keycopy = $key;
		$keycopy =~ s/_Must$//;
		if($key =~ /_Must$/ && ! $params{$key}) {
			$tmpl->param("ERROR" => "ERROR");
			$error = 1;
		}
		$session->set($keycopy => $params{$key});
	}

	if(! $error) {
		$session->set("REVIEW_OK" => 1);
	}


	if(exists $params{action} && $params{action} =~ /(Back|Zurück)/i) {
		print $query->redirect("http://$ENV{HTTP_HOST}/index/address");
		finish($session);
	}

	if(exists $params{action} && $params{action} =~ /(Next|Weiter)/i) {
		if($error || ! defined $query->param("Form_Review_AGB_Accepted_Must")) {
			$tmpl->param("ERROR" => "ERROR");
			$input = "";
		} else {
			print $query->redirect("http://$ENV{HTTP_HOST}/index/payment?view=" . &uri_escape($query->param("view")));
			finish($session);
		}
	}
} elsif ($input =~ /^newsletter_(\w+)$/) {
	if(defined $query->param("email")) {
		my $email = $query->param("email");
		my $msgx = MIME::Lite->new(
			From     => $query->param("email"), 
			To       =>$mailman,
			Subject  =>'[ meyposters ]',
			Data     => "$1 " .  $query->param("password") . "address=$email"
		);
		$msgx->send;

		print $query->redirect("http://$ENV{HTTP_HOST}/index/text/newsletter-thanks");
		finish($session);
	} else {}
} elsif ($input eq "affiliate_data") {
	my %params = $query->Vars;
	$session->set("AFFILIATE_OK" => undef);

	foreach my $key ($session->keys()) {
		next if $key !~ /^Form_Haendler/;
		$key =~ s/_Must$//;
		$session->set($key => undef);
	}

	if(exists $params{action} && $params{action} =~ /(Clear|Löschen)/i) {
		print $query->redirect("http://$ENV{HTTP_HOST}/index/affiliate_data");
		finish($session);
	}

	my $error = "";

	if( ! $params{"Form_Haendler_CheckAgreement_Must"}) {
		$error = 1;
		$tmpl->param("ERROR_Form_Haendler_CheckAgreement_Must" => 1);
	}

	foreach my $key (%params) {
		my $keycopy = $key;
		$keycopy =~ s/_Must$//;
		if($key =~ /_Must$/ && ! $params{$key}) {
			$error = 1;
			$tmpl->param("ERROR_FORM" => 1);
		}
		$session->set($keycopy => $params{$key});
	}
	if(! $error) {
		$session->set("AFFILIATE_OK" => 1);
	}

	if(exists $params{action} && $params{action} =~ /(Next|Weiter) /i) {
		if($error) {
			$tmpl->param("ERROR" => "ERROR");
			$input = "";
		} else {
			print $query->redirect("http://$ENV{HTTP_HOST}/index/affiliate_code");
			my $mailx = HTML::Template->new( filename => "templates/affiliate_mailcode.txt",
			global_vars => 1,
			path => [ '/home/th/projects/sp/public_html' ],
			die_on_bad_params => 0,
			loop_context_vars => 1);
			my $uuid = uniqueid;
			$mailx->param("CODE" => $uuid);
			$session->set("UUID_CODE" => $uuid);
			foreach my $key ($session->keys()) {
				next if $key !~ /^Form_Haendler/;
				$mailx->param($key => $session->get($key));
			}
			my $msg = MIME::Lite->new(
				From     => $sales_mail_from,
				To       => $session->get("Form_Haendler_Email"),
				Subject  =>'[ meyposters ] Affiliate Verification Code',
				Data     => $mailx->output()
			);
			$msg->send;
			finish($session);
		}
	}
} elsif ($input eq "affiliate_code") {
	my %params = $query->Vars;
	$session->set("CODE_OK" => undef);

	my $error = 0;
	foreach my $key (%params) {
		my $keycopy = $key;
		$keycopy =~ s/_Must$//;
		if($key =~ /_Must$/ && ! $params{$key}) {
			$tmpl->param("ERROR_" . $key => 1);
			$error = 1;
		}
		$session->set($keycopy => $params{$key});
	}

	my $haendler =  ReadData("Haendler", $dbh);
	if( $params{"Form_Haendler_Login_Must"} =~ /\s/ ||
	length($params{"Form_Haendler_Login_Must"}) < 2 ||
	( grep { lc($_->{"Login"}) eq lc($params{"Form_Haendler_Login_Must"}) } @{$haendler} ) ) {
		$error = 1;
		$tmpl->param("ERROR_Dupe" => 1);
	}

	if($params{"Form_Haendler_Affiliate_Must"} !~ /^[\w\d]{2,}$/ ||
	( grep { lc($_->{"Affiliate"}) eq lc($params{"Form_Haendler_Affiliate_Must"}) } @{$haendler} ) ||
	$params{"Form_Haendler_Affiliate_Must"} =~ /^(google|overtur|notregist)/i
) {
	$error = 1;
	$tmpl->param("ERROR_Form_Haendler_Affiliate_Must" => 1);
}
if($params{"Form_Haendler_Password1_Must"} ne $params{"Form_Haendler_Password2_Must"}) {
	$error = 1;
	$tmpl->param("ERROR_Form_Haendler_Password1_Must" => 1);
} else {
	$session->set("Form_Haendler_Password" => $params{"Form_Haendler_Password1_Must"});
}
if(lc($params{"Form_Haendler_Code_Must"}) ne lc($session->get("UUID_CODE"))) {
	$error = 1;
	$tmpl->param("ERROR_Form_Haendler_Code_Must" => 1);
}

if(! $error) {
	$session->set("CODE_OK" => 1);
}

if(exists $params{action} && $params{action} =~ /(Back|Zurück)/i) {
	print $query->redirect("http://$ENV{HTTP_HOST}/index/affiliate_data");
	finish($session);
}

if(exists $params{action} && $params{action} =~ /(Next|Weiter)/i) {
	if($error) {
		$tmpl->param("ERROR" => "ERROR");
		$input = "";
	} else {
		print $query->redirect("http://$ENV{HTTP_HOST}/index/affiliate_code?input=Haendler_dbadd&thanks=affiliate");
		my $mailx = HTML::Template->new( filename => "templates/affiliate_register.txt",
		global_vars => 1,
		path => [ '/home/th/projects/sp/public_html' ],
		die_on_bad_params => 0,
		loop_context_vars => 1);
		foreach my $key ($session->keys()) {
			next if $key !~ /^Form_Haendler/;
			$mailx->param($key => $session->get($key));
		}
		my $msg = MIME::Lite->new(
			From     => $sales_mail_from,
			To       => $session->get("Form_Haendler_Email"),
			Subject  =>'[ meyposters ] Affiliate Registration Results',
			Data     => $mailx->output()
		);
		$msg->send;
		finish($session);
	}
}
} elsif ($input =~ /^(\w+)_dbadd$/) {
	my %hash = ();
	my $tb = $1;
	my $one = -1;
	$hash{REMOTE_ADDR} = $ENV{REMOTE_ADDR};
	$hash{_unique} = int(time/1000) . "-" . $ENV{REMOTE_ADDR} . "-" . ( (defined $query->param("id")) ? $query->param("id") : "" );
	foreach my $key ($query->param) {
		next if $key !~ /^Form_(.*)$/;
		$one++;
		( my $newkey = $key ) =~ s/Form_//;
		$hash{$newkey} = $query->param($key);
		if($newkey eq "Choice") {
			$hash{$newkey} =~ s/^\s+//;
			$hash{$newkey} =~ s/\s+$//;
			$hash{$newkey} =~ s/JA/YES/;
			$hash{$newkey} =~ s/NEIN/NO/;
		}
	}
	foreach my $key ($session->keys()) {
		next if $key !~ /^Form_${tb}_(.*)$/;
		$one++;
		( my $newkey = $key ) =~ s/Form_${tb}_//;
		$hash{$newkey} = $session->get($key);
		if($newkey eq "Choice") {
			$hash{$newkey} =~ s/^\s+//;
			$hash{$newkey} =~ s/\s+$//;
			$hash{$newkey} =~ s/JA/YES/;
			$hash{$newkey} =~ s/NEIN/NO/;
		}
	}
	if($one && ( ! defined $query->param("NOMAIL") ) ) {
		my $dbh = DBI->connect($db_source,$db_user,$db_pass);
		EnterData($tb, \%hash, $dbh);
		mailout('t.hartwig@crapoud.com', \%hash, $tb) if ! $DBI::errstr;
		if( ! defined($query->param("noredir"))) {
			print $query->redirect("http://$ENV{HTTP_HOST}/" . ( ( $query->param("_self") ) ? $ENV{SCRIPT_NAME} : "index/text/" . ( ( $query->param("thanks") ) ? $query->param("thanks") : lc($tb) ) . "-thanks") );
			finish($session);
		}
	}
}

if($input) {
	##print $query->redirect("http://$ENV{HTTP_HOST}$ENV{SCRIPT_NAME}");
	##finish($session);
}

###
my $POSTER_OFFSET = 1;
if($section[0] =~ /series/ && defined $section[1] && $section[1] =~ /^(\d+)$/) {
	$POSTER_OFFSET = $1;
}

if(! defined $session->get("CART")) {
	$session->set( "CART" => { "COUNT" => 0 });
}

my $posters_selected = ();
if ($section[0] =~ /series/) {
	$session->set("POSTER_COUNT" => 0);
	## for displaying page selections
	my $pages = ();

	my @poster = ();

	foreach my $data (values(%{$posters})) { 
		next if defined $session->get("SERIES_SELECTED") && $session->get("SERIES_SELECTED") ne "_complete_" && $session->get("SERIES_SELECTED") ne $data->{"id_Series_Series"};
		$session->set("POSTER_COUNT" => ($session->get("POSTER_COUNT") + 1));
		$data->{PreisEURview} = $formater->format_number($data->{PreisEUR}, 2, 2) if $data->{PreisEUR} =~ /^[\d\.]+$/;
		$data->{PreisUSDview} = $formater->format_number($data->{PreisUSD}, 2, 2) if $data->{PreisUSD} =~ /^[\d\.]+$/;
		$data->{"backref"} = ( $session->get("POSTER_COUNT") % 2 == 0 ) ? $session->get("POSTER_COUNT") - 1 : $session->get("POSTER_COUNT");
		$posters_selected->{$data->{id}} = $data;
		push(@poster, $session->get("POSTER_COUNT"));
		if($#poster > 0) {
			push @$pages, { "active" => $poster[0] == $POSTER_OFFSET || $poster[1] == $POSTER_OFFSET, "PAGE_TEXT" => $poster[0] . "-" . $poster[1], "PAGE_NUMBER" => $poster[0] };
			@poster = ();
		}
	}

	if($#poster == 0) {
		push @$pages, { "active" => $poster[0] == $POSTER_OFFSET, "PAGE_TEXT" => $poster[0], "PAGE_NUMBER" => $poster[0] };
	}

	for(my $i = $POSTER_OFFSET - 1; $i < $POSTER_OFFSET + 1; $i++) {
		my $counter = $i - $POSTER_OFFSET + 2;
		my @sorted = sort { ($a->{ArticleNumber} eq "" || $b->{ArticleNumber} eq "" ) ? ($b->{ArticleNumber} eq "") ? -1 : 1 :$a->{ArticleNumber} cmp $b->{ArticleNumber} } values(%{$posters_selected}); 
		if(defined $sorted[$i]) {
			$sorted[$i]->{"count"} = ( exists $session->get("CART")->{$sorted[$i]->{id}} ) ? $session->get("CART")->{$sorted[$i]->{id}} : 0;
			$sorted[$i]->{"sseries"} = $sorted[$i]->{"Series"};
			## get the image size for the popup
			my $p1 = "$path_base/content/Poster/" . $sorted[$i]->{"File_ImageNormal"};
			my $p2 = "$path_base/content/Poster/" . $sorted[$i]->{"File_ImageThumb"};
			($sorted[$i]->{"Width"}, $sorted[$i]->{"Height"}) = imgsize($p1);
			($sorted[$i]->{"Width_Thumb"}, $sorted[$i]->{"Height_Thumb"}) = imgsize($p2);
			$tmpl->param( "POSTER_" . $counter => 1);
			foreach my $key (keys(%{$sorted[$i]})) {
				$tmpl->param( "${key}_${counter}" => ${$sorted[$i]}{$key});
			}
		}
	}
	if($session->get("POSTER_COUNT") - 1 > $POSTER_OFFSET) {
		$tmpl->param("POSTER_NEXT" => $POSTER_OFFSET + 2);
	}
	if($POSTER_OFFSET > 1) {
		$tmpl->param("POSTER_PREV" => $POSTER_OFFSET - 2);
	}
	$tmpl->param("PAGES" => $pages );
} 

if ($section[0] =~ /error/) {
	my %hash = ();
	$hash{SESSION} = $session->getid();
	$hash{URL} = "http://$ENV{HTTP_HOST}/index/address?JSESSIONID=" . $session->getid();
	map { $hash{$_} = $ENV{$_} } keys(%ENV);
	mailout('t.hartwig@crapoud.com', \%hash, "error") if ! $DBI::errstr;
}

my $output = "";

my @menu = map { { topic => $_, title => ucfirst $_ , active => $_ eq $section[0] ? 1 : 0 } } @class;

my @detail = ();

my $PRICE_USD_SUM = 0;
my $PRICE_EUR_SUM = 0;
my $PRICE_USD_END = 0;
my $PRICE_EUR_END = 0;
my $PRICE_SHIP = $session->get("PRICE_" . uc($session->get("Form_Address_ShippingType")) . "_SHIP") || 5.60;
my $PRICE_EUR_PAYMENT = $session->get("PRICE_EUR_PAYMENT") || 0;
my $PRICE_USD_PAYMENT = $session->get("PRICE_USD_PAYMENT") || 0;
my $PRICE_EUR_MWST = 0;

if($section[0] =~ /(cart|review|payment)/i) {
	my $counter = 1;
	my @CART_LOOP = ();
	foreach my $key (keys(%{$session->get("CART")})) {
		next if $key =~ /COUNT/;
		my %hash;
		$hash{CART_COUNTER} = $counter++;
		if(exists $posters->{$key}) {
			foreach(keys(%{$posters->{$key}})) {
				$hash{$_} = $posters->{$key}->{$_};
			}
		}
		$hash{Anzahl} = $session->get("CART")->{$key};
		$hash{backref} = $session->get("BACKREF")->{$key} if defined $session->get("BACKREF");
		if(defined($session->get("PRICE")) && defined($session->get("PRICE")->{$key})) {
		    $PRICE_USD_SUM += $session->get("PRICE")->{$key};
		    $PRICE_EUR_SUM += $session->get("PRICE")->{$key};
            $hash{Preis_USD_Total} = $formater->format_number($session->get("PRICE")->{$key}, 2, 2);
            $hash{Preis_EUR_Total} = $formater->format_number($session->get("PRICE")->{$key}, 2, 2);
        } else {
		    $PRICE_USD_SUM += $hash{Anzahl} * $hash{PreisUSD};
		    $PRICE_EUR_SUM += $hash{Anzahl} * $hash{PreisEUR};
            $hash{Preis_USD_Total} = $formater->format_number($hash{Anzahl} * $hash{PreisUSD}, 2, 2);
            $hash{Preis_EUR_Total} = $formater->format_number($hash{Anzahl} * $hash{PreisEUR}, 2, 2);
        }
		push(@CART_LOOP, \%hash);
	}
	$PRICE_USD_END = $PRICE_SHIP + $PRICE_USD_SUM + $PRICE_USD_PAYMENT;
	$PRICE_EUR_END = $PRICE_SHIP + $PRICE_EUR_SUM + $PRICE_EUR_PAYMENT;
	$PRICE_EUR_MWST = $PRICE_EUR_SUM / 1.16 * 0.16;

	$session->set("CURRENCY" => ($lang eq "ENG") ? "USD" : "EUR");
	$session->set("PRICE_END" => ($lang eq "ENG") ? $PRICE_USD_END : $PRICE_EUR_END);
	$session->set("PRICE_SHIP" => $PRICE_SHIP);
	$session->set("PRICE_EXTRA" => $session->get("PRICE_EUR_PAYMENT") || $session->get("PRICE_USD_PAYMENT") || 0);
	$tmpl->param("CART_LOOP" => \@CART_LOOP);
	$tmpl->param("PRICE_USD_SUM" => ( $PRICE_USD_SUM ) ? $formater->format_number($PRICE_USD_SUM, 2, 2) : "0,00");
	$tmpl->param("PRICE_EUR_SUM" => ( $PRICE_EUR_SUM ) ? $formater->format_number($PRICE_EUR_SUM, 2, 2) : "0,00");
	$tmpl->param("PRICE_SHIP" => $formater->format_number($PRICE_SHIP, 2, 2));
	$tmpl->param("PRICE_EUR_MWST" => $formater->format_number($PRICE_EUR_MWST, 2, 2));
	$tmpl->param("PRICE_EUR_END" => ( $PRICE_EUR_SUM ) ? $formater->format_number($PRICE_EUR_END, 2, 2) : "0,00");
	$tmpl->param("PRICE_USD_END" => ( $PRICE_USD_SUM ) ? $formater->format_number($PRICE_USD_END, 2, 2) : "0,00");
	$tmpl->param("PRICE_EUR_PAYMENT" => ( $PRICE_EUR_PAYMENT ) ? $formater->format_number($session->get("PRICE_EUR_PAYMENT"), 2, 2) : "");
	$tmpl->param("PRICE_USD_PAYMENT" => ( $PRICE_USD_PAYMENT ) ? $formater->format_number($session->get("PRICE_USD_PAYMENT"), 2, 2) : "");
	if(defined $query->param("shipping")) {
		$tmpl->param("SHIPPING" => 1);
	}
	if(defined $session->get("Form_Address_Zahlungsart")) {
		my @method = grep { $_->{Zahlungsart} eq $session->get("Form_Address_Zahlungsart") && $_->{Saferpay} } @{$payments};
		if($#method > -1) {
			$tmpl->param("PAYMENTPAGE" => "saferpay");
		} else {
			my $pay = $session->get("Form_Address_Zahlungsart");
			$pay =~ s/^(\S+).*$/$1/;
			$pay =~ s/\W//ig;
			$tmpl->param("PAYMENTPAGE" => $pay );
		}
	}
	$tmpl->param("CARTVIEW" => 1) if $section[0] =~ /(cart)/;
}

if ($section[0] =~ /popup/) {
	my $page = $section[2] || 1;
	my $pages = [];
	my @poster = ();
	my $next = 0;
	my $previous = 0;
	foreach my $data (@{$session->get("POPUPS")}) { 
		next if $data->{Section} ne $section[1];
		if($next) {
			$tmpl->param("NEXT"    => $data->{Number});
			last;
		}
		if($data->{Number} eq $page) {
			foreach(keys(%{$data})) {
				$tmpl->param(	$_ 			=> $data->{$_},
				PREVIOUS 	=> $previous
			);
			$next = 1;
		}
	}
	$previous = $data->{Number};
}
} 

if ($section[0] =~ /^(address|land|affiliate_data|affiliate_code)$/) {
	my $adverts =  ReadData("zAdvert", $dbh);
	my @countries = sort {( ! ( $a->{Priority} cmp $b->{Priority} ) ) ? $a->{Land} cmp $b->{Land} : $b->{Priority} cmp $a->{Priority} } values(%{$countries});
	my @p = grep { $_->{Language} =~ /^$lang/i } @{$session->get("PAYMENTS")};
	$tmpl->param("PAYMENTS" => \@p);
	foreach my $key ($session->keys()) {
		next if $key !~ /^Form_(Address|Haendler)/;
		$tmpl->param($key => $session->get($key));
	}
	if($section[0] =~ /^address/) {
		@countries = map { $_->{"is_selected"} = "1" if $session->get("Form_Address_Land") eq $_->{"Land"}; $_; } @countries if $session->get("Form_Address_Land") && $session->get("Form_Address_Land") ne "";
	} else {
		@countries = map { $_->{"is_selected"} = "1" if $session->get("Form_Haendler_Land") eq $_->{"Land"}; $_; } @countries if $session->get("Form_Haendler_Land") && $session->get("Form_Haendler_Land") ne "";
	}
	@{$adverts} = map { $_->{"is_selected"} = "1" if $session->get("Form_Haendler_Advert") eq $_->{"zAdvert"}; $_; } @{$adverts} if $session->get("Form_Haendler_Advert") && $session->get("Form_Haendler_Advert") ne "";
	$tmpl->param("ADVERTS" => $adverts);
	$tmpl->param("COUNTRIES" => \@countries,
	"COUNTRIESD" => \@countries);

}

if($section[0] =~ /^(review|cart)$/i && defined $session->get("ADDRESS_OK")) {
	foreach my $key ($session->keys()) {
		next if $key !~ /^Form_Address/;
		$tmpl->param($key => $session->get($key));
	}
} 

if($section[0] =~ /^(review)$/i && defined $session->get("ADDRESS_OK")) {
	$tmpl->param("ADDRESS_OK" => 1);
}

if($section[0] =~ /^(cart|review)$/i && defined $session->get("ADMIN_OK")) {
	$tmpl->param("ADMIN_OK" => 1);
}

if($section[0] eq "manage-view") {
	my $dbh = DBI->connect($db_source,$db_user,$db_pass);
	my %params = $query->Vars;

	my $redirect = 0;
	my $carts = ReadData("Cart", $dbh, "select Cart.*, Discount/100 as Discount, Amount/100 as Amount_Real, Cart.id as cid, NOW() as Now, Haendler.Name as Affiliate, Address.*, Zahlungsart.Zahlungsart as Zahlungsart, DATE_FORMAT(Cart.Date, '%Y-%m-%d') as Datum from Cart Left join Address on id_Address_LastName=Address.id left join Haendler on Cart.id_Haendler_Affiliate=Haendler.id left join Zahlungsart on id_Zahlungsart_Zahlungsart=Zahlungsart.id where Cart.id='" . $params{p} . "' order by Date");
	my $counter = 1;
	my $lang = "ENG";
	my $pposter = 0;
	my $discount = 0;
	for(my $i = 0; $i <= $#{$carts}; $i++) {
		my $poster = ReadData("CartItem", $dbh, "select * from CartItem left join Poster on  id_Poster_ArticleNumber=Poster.id where id_Cart_id='" . $carts->[$i]->{cid} . "'");
		$carts->[$i]->{COUNTER} = 0;
		for(my $j = 0; $j <= $#{$poster}; $j++) {
			$carts->[$i]->{COUNTER} += $poster->[$j]->{Count};
			$poster->[$j]->{COUNTER} = $j + 1;
			$poster->[$j]->{PreisUSD} = $poster->[$j]->{Amount} / $poster->[$j]->{Count};
			$poster->[$j]->{PreisEUR} = $poster->[$j]->{Amount} / $poster->[$j]->{Count};
			$poster->[$j]->{Preis_USD_Total} = $formater->format_number($poster->[$j]->{Count} * $poster->[$j]->{PreisUSD}, 2, 2);
			$poster->[$j]->{Preis_EUR_Total} = $formater->format_number($poster->[$j]->{Count} * $poster->[$j]->{PreisEUR}, 2, 2);
			$poster->[$j]->{PreisEURview} = $formater->format_number($poster->[$j]->{PreisEUR}, 2, 2) if $poster->[$j]->{PreisEUR} =~ /^[\d\.]+$/;
			$poster->[$j]->{PreisUSDview} = $formater->format_number($poster->[$j]->{PreisUSD}, 2, 2) if $poster->[$j]->{PreisUSD} =~ /^[\d\.]+$/;
			$PRICE_USD_SUM += $poster->[$j]->{Count} * $poster->[$j]->{PreisUSD};
			$PRICE_EUR_SUM += $poster->[$j]->{Count} * $poster->[$j]->{PreisEUR};
#			$PRICE_USD_SUM += $poster->[$j]->{Amount};
#			$PRICE_EUR_SUM += $poster->[$j]->{Amount};
		}
		if(defined $poster) {
			$carts->[$i]->{POSTER_LOOP} = $poster;
			$lang = $carts->[$i]->{Language};
		}
		$carts->[$i]->{Zahlungsart} =~ s/^([^\(]+)\s+.*$/$1/igm;
		$carts->[$i]->{DiscountView} = $formater->format_number($carts->[$i]->{Discount}, 2, 2);
		$tmpl->param("Datum" => $carts->[$i]->{Datum});

		my $country = $carts->[$i]->{Country};
		if( $carts->[$i]->{AddressDelivery} ) {
			$country = $carts->[$i]->{Country};
		}
##		my $land = ReadData("Land", $dbh, "select * from Land where Land='" . $country . "'");

		$PRICE_USD_PAYMENT = $carts->[$i]->{CostExtraIncluded} / 100;
		$PRICE_EUR_PAYMENT = $carts->[$i]->{CostExtraIncluded} / 100;
		$PRICE_SHIP = $carts->[$i]->{CostShipIncluded} / 100;
		$discount = $carts->[$i]->{Discount};
		$carts->[$i]->{Discount} = 0 if $carts->[$i]->{Discount} < 1;
		if($carts->[$i]->{COUNTER} > 1) {
			$pposter = 1;
		}

		if($carts->[$i]->{Zahlungsart} =~ /^(Rechnung|Invoice)/) {
			$carts->[$i]->{BANKING} = "1";
		}
	}
	$tmpl = HTML::Template::Expr->new( filename => ( $lang ne "ENG") ? "templates/$lang/manage-view.html" : "templates/manage-view.html",
	global_vars => 1,
	path => [ '/home/th/projects/sp/public_html' ],
	die_on_bad_params => 0,
	loop_context_vars => 1);
	$tmpl->param("CART_LOOP" => $carts);

	$PRICE_USD_END = $PRICE_SHIP + $PRICE_USD_SUM + $PRICE_USD_PAYMENT - $discount;
	$PRICE_EUR_END = $PRICE_SHIP + $PRICE_EUR_SUM + $PRICE_EUR_PAYMENT - $discount;
	$PRICE_EUR_MWST = $PRICE_EUR_SUM / 1.16 * 0.16;

	my @tt = localtime(time);
#	$tmpl->param("Datum" => join("-", $tt[5]+1900, $tt[4]+1, $tt[3]));

	$tmpl->param("PPOSTER" => $pposter);
	$tmpl->param("PRICE_USD_SUM" => ( $PRICE_USD_SUM ) ? $formater->format_number($PRICE_USD_SUM, 2, 2) : "0,00");
	$tmpl->param("PRICE_EUR_SUM" => ( $PRICE_EUR_SUM ) ? $formater->format_number($PRICE_EUR_SUM, 2, 2) : "0,00");
	$tmpl->param("PRICE_SHIP" => $formater->format_number($PRICE_SHIP, 2, 2));
	$tmpl->param("PRICE_EUR_MWST" => $formater->format_number($PRICE_EUR_MWST, 2, 2));
	$tmpl->param("PRICE_EUR_END" => ( $PRICE_EUR_SUM ) ? $formater->format_number($PRICE_EUR_END, 2, 2) : "0,00");
	$tmpl->param("PRICE_USD_END" => ( $PRICE_USD_SUM ) ? $formater->format_number($PRICE_USD_END, 2, 2) : "0,00");
	$tmpl->param("PRICE_EUR_PAYMENT" => ( $PRICE_EUR_PAYMENT ) ? $formater->format_number($PRICE_EUR_PAYMENT, 2, 2) : "");
	$tmpl->param("PRICE_USD_PAYMENT" => ( $PRICE_USD_PAYMENT ) ? $formater->format_number($PRICE_USD_PAYMENT, 2, 2) : "");
	if(defined $query->param("shipping")) {
		$tmpl->param("SHIPPING" => 1);
	}
	if(defined $session->get("Form_Address_Zahlungsart")) {
		my @method = grep { $_->{Zahlungsart} eq $session->get("Form_Address_Zahlungsart") && $_->{Saferpay} } @{$payments};
		if($#method > -1) {
			$tmpl->param("PAYMENTPAGE" => "saferpay");
		} else {
			my $pay = $session->get("Form_Address_Zahlungsart");
			$pay =~ s/^(\S+).*$/$1/;
			$pay =~ s/\W//ig;
			$tmpl->param("PAYMENTPAGE" => $pay );
		}
	}
} 

if($section[0] eq "manage") {
	my $dbh = DBI->connect($db_source,$db_user,$db_pass);
	my %params = $query->Vars;
	if(exists  $params{"action"}) {
		$session->set("ACTION" => $params{"action"});
	} else {
		$params{"action"} =  $session->get("ACTION");
	}
	if(exists $params{"Form_Discount_" . $params{cid}}) {
		$params{"Form_Discount_" . $params{cid}} =~ s/,/./g;
		$dbh->do("update Cart set Discount='" . $params{"Form_Discount_" . $params{cid}} * 100 . "' where id='" . $params{cid} . "'");
	}
	if(exists $params{"Form_Abgeschlossen_" . $params{cid}}) {
		$dbh->do("update Cart set Abgeschlossen='1' where id='" . $params{cid} . "'");
	} elsif (exists $params{cid}) {
		$dbh->do("update Cart set Abgeschlossen='0' where id='" . $params{cid} . "'");
	}
	if($params{"Action"} =~ /Ausdruck/) {
		print $query->redirect("http://$ENV{HTTP_HOST}/index/manage-view?p=" . $params{cid});
		finish($session);
	}

	### add link email to database
	if(defined $params{Email} || defined $params{bulkmail}) {
		my @email = ();
		if(defined $params{Email}) {
			my %linkmail = ();
			$linkmail{Email} = $params{Email};
			$linkmail{Email} =~ s/^mailto://i;
			$linkmail{Url} = $params{Url};
			## my $data = `cat templates/$params{Language}/addlink.txt`;
			if( EnterData("Linkmail", \%linkmail, $dbh) !~ /^-/ ) {
				push(@email, $linkmail{Email});
			}
		}
		if (defined $params{bulkmail}) {
			@email = map { $_->{Email} } @{ReadData("Linkmail", $dbh)};
		}
		foreach my $email (@email) {
			my $data = `cat $path_base/templates/addlink.txt`;
			my $msg = MIME::Lite->new(
				From     => 'info@meyposters.com',
				To       => $email,
				Subject  => '[ meyposters ] Affiliate Program',
				Type     => 'multipart/mixed'
			);
			$msg->attach(Type 		=> 'text/plain',
			Data     => $data);
			$msg->attach(Type 		=> 'image/jpg',
			Path  		=> "$path_base/banner/high_heels_posters_200x149.jpg",
			Disposition => 'attachment');
			## warn($msg->as_string());
			$msg->send;
		}
	}

	my $redirect = 0;
	my $carts = ReadData("Cart", $dbh, "select *, Amount/100 as Amount_Real, Cart.id, NOW() as Now, Haendler.Name as Affiliate, Discount/100 as Discount from Cart Left join Address on id_Address_LastName=Address.id left join Haendler on Cart.id_Haendler_Affiliate=Haendler.id where ISNULL(Abgefertigt) OR Abgefertigt='' order by Date");
	my $deliveries = ReadData("Lieferfirma", $dbh);

	my $survey = ReadData("Survey", $dbh);
	my %loop = ();

#	my $ahits = ReadData("Affiliatehit", $dbh, "select count(*) as hits, h.Affiliate as affiliate, a.id_Haendler_Affiliate as backfall from Affiliatehit a left join Haendler h on a.id_Haendler_Affiliate=h.id group by a.id_Haendler_Affiliate");
#	$tmpl->param("Affiliates" => $ahits);
	my @head = qw(Color Motiv Nackt Fetisch Was Welche);
	foreach my $chart (@{$survey}) {
		for(@head) {
			if($chart->{$_}) {
				$loop{$_}{$chart->{$_}}++;
			}
		}
	}

	my @s = ();
	foreach my $key (keys(%loop)) {
		my @a = ();
		my %b = ();
		map { $loop{$key}{MAX} = $loop{$key}{$_} if ! defined $loop{$key}{MAX} || $loop{$key}{MAX} < $loop{$key}{$_}; } keys(%{$loop{$key}});
		foreach(keys(%{$loop{$key}})) {
			next if /^MAX$/;
			my %l = ();
			$l{Section} = $_;
			$l{Count} = $loop{$key}{$_};
			$l{Width} = ($loop{$key}{MAX} > 0) ? int($l{Count} * 100 / $loop{$key}{MAX}) : 0;
			push(@a, \%l);
		}
		@a = sort { $b->{Count} <=> $a->{Count} } @a;
		$b{Headline} = $key;
		$b{LOOP} = \@a;
		push(@s, \%b);
	}
	$tmpl->param("SURVEY_LOOP" => \@s);

	foreach my $key (keys(%params)) {
		if($key =~ /^Form_Send_(.*)$/) {
			my $tk = $1;
			if($params{$key} =~ /^(Email|Abfertigen)$/) {
				$dbh->do("update Cart set Abgefertigt=NOW(), id_Lieferfirma_Lieferfirma='" . $params{"id_Lieferfirma_Lieferfirma_$tk"} . "' where id='" . $tk . "'");
			} elsif ($params{$key} =~ /^(Loeschen)$/) {
				$dbh->do("delete from CartItem where id_Cart_id='" . $tk . "'");
				$dbh->do("delete from Cart where id='" . $tk . "'");
			}
			$redirect++;
		}
	}	

	for(my $i = 0; $i <= $#{$carts}; $i++) {
		if(exists $params{"Form_Send_" . $carts->[$i]->{id}} && $params{"Form_Send_" . $carts->[$i]->{id}} eq "Email") {
			my $mailx = HTML::Template::Expr->new( filename => ( $carts->[$i]->{Language} ne "ENG") ? "templates/$lang/delivery.txt" : "templates/delivery.txt",
			global_vars => 1,
			path => [ '/home/th/projects/sp/public_html' ],
			die_on_bad_params => 0,
			loop_context_vars => 1);
			foreach my $key (%{$carts->[$i]}) {
				$mailx->param($key => $carts->[$i]->{$key});
			}
			$mailx->param("COMPANY" => join("", map { $_->{Lieferfirma} } grep { $_->{id} eq $params{"id_Lieferfirma_Lieferfirma_" . $carts->[$i]->{id}} } @{$deliveries}) );
			my $msg = MIME::Lite->new(
				From     => $sales_mail_from, 
				To       => $carts->[$i]->{Email},
				Subject  => '[ meyposters ] ' . $carts->[$i]->{Number},
				Data     => $mailx->output()
			);
			$msg->send;
		}
	}

	my $counter = 1;
	$carts = ReadData("Cart", $dbh, "select *, Amount/100 as Amount_Real, Discount/100 as Discount, Cart.id as cid, Cart.id as id from Cart Left join Address on id_Address_LastName=Address.id " . (($params{action} eq " Alle ") ? "where 1=1" : "where (ISNULL(Abgefertigt) OR Abgefertigt='') ") . " order by Date");

	for(my $i = 0; $i <= $#{$carts}; $i++) {
		my $poster = ReadData("CartItem", $dbh, "select * from CartItem left join Poster on  id_Poster_ArticleNumber=Poster.id where id_Cart_id='" . $carts->[$i]->{id} . "'");
		$carts->[$i]->{COUNTER} = $counter++;
		$carts->[$i]->{Lieferfirma_Loop} = $deliveries if defined $deliveries;
		if(defined $poster) {
			$carts->[$i]->{CART_LOOP} = $poster;
		}
	}
	$tmpl->param("ORDER" => $carts);
} 


#### saferpay payment window handling
if($section[0] =~ /payment/i && defined $session->get("REVIEW_OK")) {
	$tmpl->param("ADDRESS_OK" => 1);

	## create the security cipher
	my $key = "";
	while(length($key) < 56) {
		$key .= int(rand(9));
	}
	$session->set("KEY" => $key);
	my $cipher = new Crypt::Blowfish $key;

	my $code = uc(unpack("H16", $cipher->encrypt(substr($key,0,8)))); 

	$PRICE_USD_END = $PRICE_USD_END * 100;
	$PRICE_EUR_END = $PRICE_EUR_END * 100;

	$session->set("ORDERID" => uc(unpack("H*", pack("L", 100000000 + int(rand(100000000))))));

	my $selfURL = "http://" . $ENV{"HTTP_HOST"};

	my @method = grep { $_->{Zahlungsart} eq $session->get("Form_Address_Zahlungsart") && $_->{Saferpay} && uc($_->{Language}) eq $lang} @{$session->get("PAYMENTS")};
	if( $#method > -1 ) {
		my $MF = soplex::saferpay::MessageFactory->new();
		$MF->Open($path_saferpay);
		my $orderId = int(rand(1000));

		my $MO = $MF->CreatePayInit(DELIVERY => "no",
		PROVIDERSET => $method[0]->{Saferpay},
		CCNAME => "yes",
		AMOUNT => ($lang eq "ENG") ? $PRICE_USD_END : $PRICE_EUR_END,
		CURRENCY => ( $method[0]->{Currency} ) ? $method[0]->{Currency} : ($lang eq "ENG") ? "USD" : "EUR",
		DESCRIPTION => "Posters",
		SUCCESSLINK => $selfURL . "/index/success/" . $code,
		ALLOWCOLLECT => "no",
		BACKLINK => $selfURL . "/index/error",
		FAILLINK => $selfURL . "/index/error",
		## ACCOUNTID => "99867-94913159",
		## ACCOUNTID => "90642-17734970",
		ACCOUNTID => "90642-17743856",
		DURATION => time2str("%Y%m%d%H%M%S", time+600),
		LANGID => ($lang eq "DE") ? "de" : "en",
		ALLOWCOLLECT => "no",
		ORDERID => $session->get("ORDERID"));
		$tmpl->param(	"REVIEW_OK" => 1,
		"PAYMENT_URL" => $MO->GetURL(),
		"PAYMENT_DIRECT_URL" => "/index/success/" . $code . "?lpay=" . $session->get("Form_Address_Zahlungsart"));

	} elsif ($session->get("Form_Address_Zahlungsart") =~ /(Paypal)/i) {
		$PRICE_USD_END = $formater_english->format_number($PRICE_USD_END / 100, 2, 2);
		# my $url = 'https://www.paypal.com/cgi-bin/webscr?cmd=_xclick&amount=%24' . $PRICE_USD_END . '&business=sales%40meyposters.com&item_name=Posters+' . $session->get("ORDERID") . '&item_number=' + $session->get("ORDERID") + '&return=' . uri_escape($selfURL . '/index/success/' . $code . '/?lpay=' . $session->get("Form_Address_Zahlungsart")) . '&cancel_return=' . uri_escape($selfURL . '/index/error');
		my $url = 'https://www.paypal.com/cgi-bin/webscr?cmd=_xclick&amount=%24' . $PRICE_USD_END . '&business=sales%40meyposters.com&item_name=Posters&item_number=' . $session->get("ORDERID") . '&return=' . uri_escape($selfURL . '/index/success/' . $code . '/?lpay=' . $session->get("Form_Address_Zahlungsart")) . '&cancel_return=' . uri_escape($selfURL . '/index/error');
		warn($url);
		$tmpl->param(	"AMOUNT" => $PRICE_USD_END,
		"PAYMENT_URL" => $url,
		"REVIEW_OK" => 1
	);
	##warn($code);	
} else {
	$tmpl->param(	"REVIEW_OK" => 1,
	"PAYMENT_DIRECT_URL" => "/index/success/" . $code . "?lpay=" . $session->get("Form_Address_Zahlungsart"));
}	
}


my $PT = {
	"agb" => { "DE" => "Allgemeine Geschäftsbedingungen", "ENG" => "General Terms and Conditions of Trade" },
	"datenschutz" => { "DE" => "Datenschutz" },
	"feedback" => { "DE" => "Feedback", "ENG" => "Feedback" },
	"faq" => { "DE" => "FAQ", "ENG" => "FAQ" },
	"contact" => { "DE" => "Impressum" },
	"cart" => { "DE" => "Warenkorb", "ENG" => "Cart" }, 
	"home" => { "DE" => "Willkommen", "ENG" => "Welcome" },
	"specials" => { "DE" => "Specials", "ENG" => "Specials" },
	"background" => { "DE" => "Background Stories", "ENG" => "Background Stories" },
	"wer" => { "DE" => "Wer ist Mey", "ENG" => "Who is Mey" },
	"address" => { "DE" => "Adresseingabe" },
	"informationen" => { "DE" => "Informationen", "ENG" => "Information" },
	"vote" => { "DE" => "Poster or Not", "ENG" => "Poster or Not" },
	"affiliate_data" => { "DE" => "Affiliate", "ENG" => "Affiliate" },
	"affiliate_code" => { "DE" => "Affiliate", "ENG" => "Affiliate" },
};

if(defined $session->get("SERIES_SELECTED")) {
	$PT->{series} = { "DE" => $serieses->{$session->get("SERIES_SELECTED")}->{HeadlineDE}, "ENG" => $serieses->{$session->get("SERIES_SELECTED")}->{HeadlineENG} };
}

my $PAGE_TITLE = ( exists $PT->{$section[0]} && exists $PT->{$section[0]}->{$lang} ) ? $PT->{$section[0]}->{$lang} : "";

if ($section[0] eq "vote") {
	foreach my $data (grep { $_->{Survey} eq "1" } values(%{$posters})) { 
		my $id = $data->{"id"};
		my $r = $dbh->selectall_arrayref("select Choice, count(*) from Vote where id_Poster_ArticleNumber='" . $data->{"id"} . "' group by Choice");
		my $max = 0;
		foreach my $s (@{$r}) {
			$max += $s->[1];
		}
		foreach my $s (@{$r}) {
			$data->{$s->[0]} = int($s->[1]/$max * 100);
		}
		push(@detail, $data);
	}
}

if ($section[0] eq "vote-voice") {
	my @values = grep { $_->{Survey} eq "1" } values(%{$posters});
	my $id = ( (defined $query->param("nid")) ? $query->param("nid") : $query->param("id") );
	for (my $i = 0; $i <= $#values; $i++) {
		my %data = %{$values[$i]};
		next if $data{"ArticleNumber"} !~ /^PoN/ || $data{"id"} ne $id;
		####  warn("HERE: ", $id);
		$data{nid} = ( ($i < $#values) ? ${$values[$i+1]}{id} : ${$values[0]}{id} );
		push(@detail, \%data);
	}
}

#### loading database content
if(grep { /^$section[0]$/ } ("home", "faq", "specials", "background", "wer", "contact", "informationen", "link", "feedback") )  {
	my $dbh = DBI->connect($db_source,$db_user,$db_pass);
	my $stmt = "SELECT * FROM " . ucfirst $section[0];
	my $sth = $dbh->prepare($stmt);
	$sth->execute;
	while ( my $data = $sth->fetchrow_hashref ) {
		if(exists ${$data}{Language} && ${$data}{Language}) {
			next if $lang !~ /${$data}{Language}/i;
		}
		foreach my $key (keys(%{$data})) {
			if($key =~ /(EUR|USD)$/) {
				$data->{$key} = $formater->format_number($data->{$key}, 2, 2);
			}
			$data->{$key} =~ s/\r?\n/<BR>/gm if grep { /^$section[0]$/ } qw(feedback);
		}
		push @detail, $data;
	}
	@detail = sort { $a->{Nummer} <=> $b->{Nummer} } @detail;
}

#### loading database content
if( (grep { /^$section[0]$/ } qw(text text-popup)) && defined $section[1] && $section[1] ne "")  {
	my $dbh = DBI->connect($db_source,$db_user,$db_pass);
	my $stmt = "SELECT * FROM Text where Section='$section[1]'";
	my $sth = $dbh->prepare($stmt);
	$sth->execute;
	while ( my $data = $sth->fetchrow_hashref ) {
		foreach my $key (grep {/(EUR|USD)$/} keys(%{$data})) {
			$data->{$key} = $formater->format_number($data->{$key}, 2, 2);
		}
		push @detail, $data;
		## warn($data->{Content}, "        -----------------------      ");
		$PAGE_TITLE = ${$data}{"Headline" . $lang};
	}
}

##$session->set( "COUNT" => $session->get("COUNT") + 1 );

my $SELF = $path_info;
$SELF =~ s/^\///;

my @serieses = sort { $a->{Number} <=> $b->{Number} } values(%{$serieses});

$tmpl->param( menu => \@menu, detail => \@detail,
"SESSION" => $session->getid(),
"TIME" => time,
"CUSTOM_PAGE_TITLE" => $PAGE_TITLE,
"COUNT" => $session->get("CART")->{COUNT},
"SERIES_SELECTED" => $session->get("SERIES_SELECTED"),
"SERIES" => \@serieses,
"POSTER_COUNT" => $session->get("POSTER_COUNT"),
"FONT_STANDARD_NORMAL" => '<FONT FACE="Arial, Helvetica">',
"FONT_STANDARD_SMALL" => '<FONT FACE="Arial, Helvetica" SIZE="-2">',
"FONT_SIZE_SMALL" => "-1",
"FONT_FACE_STANDARD" => "Arial, Helvetica",
"SELF" => $SELF,
"TRADEMARK" => "<sup><font size='-2'>TM</font></sup>"
		);


###my $h = HTML::Clean->new(\$tmpl->output);
###	$h->compat; $h->strip;
###$output = ${$h->data};
###warn(":::::::", Dumper($tmpl));
$output = $tmpl->output;

print $query->header();
print $output;
finish($session);

sub finish {
	my $session=shift;
	$session and $session->finish;
	exit(0);
}

sub mailout {
	my($address, $hash, $subject, $from) = @_;
	my $msg = MIME::Lite->new(
		From     => (defined $from && $from) ? $from : 'noreply@meyposters.com', 
		To       => $address,
		Subject  => '[ meyposters - infomail ] ' . $subject,
		Data     => join("\n\n", map { "$_: " . $hash->{$_} } keys(%{$hash})),
	);
	$msg->send;
}