#! /usr/bin/perl -w

# hes16sql-js.pl	převede data z MySQL databáze do polí JavaScriptu
my $version = '0.02';	#místo polí hashe

use DBI;
use DateTime;
use strict;

my $yy		= 16;		# rok
my $year	= 2000+$yy;
my $object	= 'losung';	# name of the js object
#my $date	=  DateTime->new( year => $year, month => 1, day  => 1 );

my $dbname	= "hes$yy";
my $hostname	= 'localhost';
#my $dsn		= "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dsn		= "DBI:mysql:database=$dbname; host=$hostname"; #
my $user	= 'petr';
my $password	= 'blabla';

my @dow   = qw(Pondělí Úterý Středa Čtvrtek Pátek Sobota Neděle);
my @mesic = qw(LEDEN ÚNOR BŘEZEN DUBEN KVĚTEN ČERVEN ČERVENEC SRPEN ZÁŘÍ ŘÍJEN LISTOPAD PROSINEC);
my $prevmonth = -1;	# registruje změnu měsíce

my $dbh = DBI->connect($dsn, $user, $password, { RaiseError=>1,AutoCommit=>0 })
  || die "Chyba připojení k databázi č. $DBI::err: $DBI::errstr\n"; # uplatní se jen při RaiseError=>0

$dbh->do("set names 'utf8'");

# PREPARE QUERIES

#my $day_h	= $dbh->prepare("SELECT `which`,`nr`,`text`,`meaning`,`src` FROM `day` WHERE `sel`=1 AND `date`=? AND `lang`='cs' ORDER BY `which` DESC")
#  || die "Chyba příkazu pro hledání dnů\n";
my $day_h	= $dbh->prepare("SELECT `which`,`nr`,`text`,`meaning`,`src` FROM `day` WHERE `sel`=1 AND `date`=? AND `lang`='cs' AND `which`=?")
  || die "Chyba příkazu pro hledání dnů\n";
#my $dayname_h	= $dbh->prepare("SELECT `text` FROM `dayname` WHERE `date`=? AND `lang`='cs'");
#my $losung_h = $dbh->prepare("SELECT `which`,`transl`,`source`,`intro`,`text` FROM `losung` WHERE `sel`=1 AND `date`=? AND `lang`='cs' AND NOT `which`='WP' ORDER BY `which`");
my $losung_h = $dbh->prepare("SELECT `which`,`transl`,`source`,`intro`,`text` FROM `losung` WHERE `sel`=1 AND `date`=? AND `lang`='cs' AND `which`=?");
my $drittetext_h = $dbh->prepare("SELECT `text` FROM `drittetext` WHERE `sel`=1 AND `date`=? AND `lang`='cs'");
my $song_h = $dbh->prepare("SELECT `which`,`book`,`nr`,`strophe`,`strophe2`,`text` FROM `song` WHERE `sel`=1 AND `date`=? AND `lang`='cs'");
my $reading_h = $dbh->prepare("SELECT `which`,`source` FROM `reading` WHERE `sel`=1 AND `date`=? AND `lang`='cs'");
#my $reading_h = $dbh->prepare("SELECT `which`,`source` FROM `reading` WHERE `sel`=1 AND `date`=? AND `lang`='cs' AND NOT `which`='PF' AND NOT `which`='WP'");
my $comment_h	= $dbh->prepare("SELECT `histdatetxt`,`text` FROM `comment` WHERE `sel`=1 AND `date`=? AND `lang`='cs' ORDER BY `histdatetxt`")
  || die "Chyba příkazu pro hledání komentářů\n";

sub verbose {
    my ($text) = @_;
    $text = "/* $text */\n";
    print STDERR $text;
    print "\n$text";
}

#sub verse {			# zpracuje verš u Losung
#    my ($intro_ref, $text_ref) = @_;
#    if($$intro_ref) {$$intro_ref = "<$$intro_ref:> ";} else {$$intro_ref = '';}
#    $$text_ref =~ s/\r//g;		#from DOS
#    #$$text_ref = "\t" . $$text_ref;	#TAB
#    #$$text_ref =~ s/\n/\n\t/g;		#TAB
#    $$text_ref =~ s/\n/ /g;		#odstraní zalomení
#    $$text_ref =~ s/'/\\'/g;		#apostrofy
#}

sub js_hash {		# deklaruje hash v javascriptu
    my ($var) = @_;
    print "$object.$var = {};\n";
}

sub js_out {		# výstup: proměnná, intro, verš, odkaz
    my ($var, $date, $intro, $text, $src) = @_;
    if (! $intro) {$intro = '';}
    $intro =~ s/\r//g;		#from DOS
    $intro =~ s/\n/ /g;		#odstraní zalomení
    $intro =~ s/'/\\'/g;	#escapuje apostrofy
    $text =~ s/\r//g;		#from DOS
    $text =~ s/\n/ /g;		#odstraní zalomení
    $text =~ s/'/\\'/g;		#escapuje apostrofy
    printf ("%-20s = \'%s|%s|%s\';\n", "$object.$var\[\"$date\"\]", $intro, $text, $src);
}

#print <<"_END_";
#<meta http-equiv="Content-Script-Type" content="text/javascript; charset=utf-8" />
#
#_END_

verbose "http://hesla.dulos.cz/js/losung-cs${year}v${version}.js";
verbose "Hesla jednoty bratrské pro rok $year; funkce javascriptu verse $version; kódování utf-8";
verbose "Watchwords of the Moravian Church for the year $year – Czech language; javascript functions version $version; charset utf-8";

print <<"_END_";

$object = {};

$object.lang = 'cs';

$object.about = '<a href=\"http://hesla.dulos.cz\">Hesla Jednoty bratrské</a> na rok $year, javascript <a href="http://hesla.dulos.cz/js/losung-cs${year}v${version}.js">verse $version</a>';

$object.date = new Date();

$object.setISOdate = function(dateStr) {
    this.date.setTime(Date.parse(dateStr))
}

$object.getISOdate = function() {
    dateStr = $object.date.toISOString();
    return dateStr.substr(0,10);
}

$object.dateStr = function(date) {
    dow = ['Neděle','Pondělí','Úterý','Středa','Čtvrtek','Pátek','Sobota'];
    month = ['ledna','února','března','dubna','května','června','července','srpna','září','října','listopadu','prosince'];
    return dow[$object.date.getDay()] + ' ' + $object.date.getDate()
    + '. ' + month[$object.date.getMonth()] + ' ' + $object.date.getFullYear();
}

$object.isSunday = function(date) {
    return (! this.date.getDay())
}

_END_

## Heslo roku ##
verbose "Watchword for the year";
js_hash('YEAR');
$losung_h->execute("$year-01-01", 'YEAR');
while (my ($which, $transl, $source, $intro, $text) = $losung_h->fetchrow_array) {
    js_out ('YEAR', $year, $intro, $text, $source);
}

## Hesla měsíců ##
verbose "Watchwords of the months";
js_hash ('MONTH');
for(my $date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
    $losung_h->execute($date->ymd, 'MONTH');
    while (my ($which, $transl, $source, $intro, $text) = $losung_h->fetchrow_array) {
	js_out ('MONTH', substr($date->ymd, 0, 7), $intro, $text, $source);
    }
}

## Významné týdny ##
verbose "Important weeks";
js_hash ('IMP_WEEK');
for(my $date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
    $day_h->execute($date->ymd, 'week');
    while (my ($which,$nr,$text,$meaning,$src) = $day_h->fetchrow_array) {
#    print $date->ymd, " day: $which, $nr, $text, $meaning |$src|\n";
	if($meaning){$text .= " ($meaning)";}
	js_out ('IMP_WEEK', $date->ymd, '', $text, $src);
    }
}

## Významné dny ##
verbose "Important days";
js_hash ('IMP_DAY');
for(my $date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
    $day_h->execute($date->ymd, 'important');
    while (my ($which,$nr,$text,$meaning,$src) = $day_h->fetchrow_array) {
	#    print $date->ymd, " day: $which, $nr, $text, $meaning |$src|\n";
	if($meaning){$text .= " ($meaning)";}
	js_out ('IMP_DAY', $date->ymd, '', $text, $src);
    }
}

## Neděle ##
verbose "Sundays of the church year";
js_hash ('SUNDAY');
for(my $date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
    $day_h->execute($date->ymd, 'sunday');
    while (my ($which,$nr,$text,$meaning,$src) = $day_h->fetchrow_array) {
	#    print $date->ymd, " day: $which, $nr, $text, $meaning |$src|\n";
	if($meaning){$text .= " ($meaning)";}
	js_out ('SUNDAY', $date->ymd, '', $text, $src);
    }
}

## Hesla nedělí ##
verbose "Watchwords for the sundays, i.e. for the weeks";
js_hash ('WEEK');
for(my $date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
    $losung_h->execute($date->ymd, 'sunday');
    while (my ($which, $transl, $src, $intro, $text) = $losung_h->fetchrow_array) {
	js_out ('WEEK', $date->ymd, $intro, $text, $src);
  }
}

## Svátky ##
verbose "Holidays";
js_hash ('HOLIDAY');
for(my $date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
    $day_h->execute($date->ymd, 'holiday');
    while (my ($which,$nr,$text,$meaning,$src) = $day_h->fetchrow_array) {
	#    print $date->ymd, " day: $which, $nr, $text, $meaning |$src|\n";
	if($meaning){$text .= " ($meaning)";}
	js_out ('HOLIDAY', $date->ymd, '', $text, $src);
    }
}

## Hesla svátků ##
verbose "Watchwords for Holidays";
js_hash ('HOLIDAY_LOS');
for(my $date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
    $losung_h->execute($date->ymd, 'holiday');
    while (my ($which, $transl, $src, $intro, $text) = $losung_h->fetchrow_array) {
	js_out ('HOLIDAY_LOS', $date->ymd, $intro, $text, $src);
    }
}

## Hesla SZ ##
verbose "Watchwords – Old Testament";
js_hash ('OT');
for(my $date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
    $losung_h->execute($date->ymd, 'OT');
    while (my ($which, $transl, $src, $intro, $text) = $losung_h->fetchrow_array) {
	js_out ('OT', $date->ymd, $intro, $text, $src);
    }
}

## Hesla NZ ##
verbose "Watchwords – New Testament";
js_hash ('NT');
for(my $date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
    $losung_h->execute($date->ymd, 'NT');
    while (my ($which, $transl, $src, $intro, $text) = $losung_h->fetchrow_array) {
	js_out ('NT', $date->ymd, $intro, $text, $src);
    }
}

print <<"_END_";

verse = function(text) {	// Formatting the verse
    if(! text) {return '';}
    // document.write(text.indexOf('|')+', '+text.lastIndexOf('|') );
    intro     = text.substring(0, text.indexOf('|'));
    contents  = text.substring(text.indexOf('|')+1, text.lastIndexOf('|'));
    source    = text.substring(text.lastIndexOf('|')+1, text.length);
    output = '';
    if (intro) {output += '<font size="-1"><i>' + intro + ':</i></font> ';}
    output += contents;
    output += ' <font size="-1"><i>' + source + '</i></font>';
    // return '*'+intro+'*'+verse+'*'+source+'*';
    return output;
}

$object.year  = function() { return verse(this.YEAR[this.getISOdate().substr(0,4)]);}
$object.month = function() { return verse(this.MONTH[this.getISOdate().substr(0,7)]);}
$object.ot    = function() { return verse(this.OT[this.getISOdate()]);}
$object.nt    = function() { return verse(this.NT[this.getISOdate()]);}
$object.sunday  = function() { return verse(this.SUNDAY[this.getISOdate()]);}
$object.week    = function() { return verse(this.WEEK[this.getISOdate()]);}
$object.holiday = function() { return verse(this.HOLIDAY[this.getISOdate()]);}
$object.holiday_los = function() { return verse(this.HOLIDAY_LOS[this.getISOdate()]);}

_END_

$dbh->disconnect;
__END__


print "\n/* Songs */\n";
for(my $date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
  $song_h->execute($date->ymd);
  while (my ($which, $book, $nr, $strophe, $strophe2, $text) = $song_h->fetchrow_array) {
    if($strophe2){$strophe .= '.'.$strophe2;}
    $text =~ s/\r//g;		#from DOS
    $text =~ s/\n\n/\n-\n/g;	#prázdný řádek nahradit rozdělovníkem – oddělení slok
	jsout ('song', $date->month, $date->day, $intro.$text, $src);
    print "{ $book $nr,$strophe\n$text\n}\n";	# NIC NENAJDE?!
  }




# THE MAIN LOOP THROUGH ALL DAYS IN THE WHOLE YEAR

for(my $date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
    if($date->month_0 != $prevmonth){	# new month
	$prevmonth = $date->month_0;
	print "\n\#\#\# ", $mesic[$date->month_0], " \#\#\#\n";
#	print "\n\#\#\# ", $mesic[$date->month_0], "\#\#\#\n";
    }
    print "\n\# ", $date->ymd, "\n";

### Neděle ###
  $day_h->execute($date->ymd, 'sunday');
  while (my ($which,$nr,$text,$meaning,$src) = $day_h->fetchrow_array) {
#    print $date->ymd, " day: $which, $nr, $text, $meaning |$src|\n";
    if($meaning){$meaning="\no> $meaning";}
    if($src){$src="\n-> $src";}
    print "\n@>> $text$meaning$src\n";
  }

  $losung_h->execute($date->ymd, 'sunday');
  while (my ($which, $transl, $source, $intro, $text) = $losung_h->fetchrow_array) {
    verse(\$intro, \$text);
    print "0> $intro$text\n-> $source\n";
  }

  $day_h->execute($date->ymd, 'holiday');
  while (my ($which,$nr,$text,$meaning,$src) = $day_h->fetchrow_array) {
#    print $date->ymd, " day: $which, $nr, $text, $meaning |$src|\n";
    if($meaning){$meaning="\no> $meaning";}
    if($src){$src="\n-> $src";}
    print "\n\$>> $text $meaning $src\n";
  }

  $losung_h->execute($date->ymd, 'holiday');
  while (my ($which, $transl, $source, $intro, $text) = $losung_h->fetchrow_array) {
    verse(\$intro, \$text);
    print "0> $intro$text\n-> $source\n";
  }

#  $day_h->execute($date->ymd);
#  while (my ($which,$nr,$text,$meaning,$src) = $day_h->fetchrow_array) {
##    print $date->ymd, " day: $which, $nr, $text, $meaning |$src|\n";
#    print "\n", $date->ymd, ": $text $meaning $src\n";
#  }

### Název dne ###
#  $dayname_h->execute($date->ymd);		# UŽ SE NELOVÍ Z DATABÁZE
#  while (my ($dayname_text) = $dayname_h->fetchrow_array) {
#    print $date->ymd, " dayname: $dayname_text\n";	# CHYBA V DATABÁZI U NEDĚLÍ - CHYBÍ PRVNÍ CIFRA DATUMU!

    my $dayname_text = sprintf "%d. %s", $date->day, $dow[$date->day_of_week_0];

    my $tag = $dayname_text=~ /Neděle/ ? "+>" : "x>"; 
    print "\n$tag $dayname_text\n";
#  }

#  $losung_h->execute($date->ymd);
#  while (my ($which, $transl, $source, $intro, $text) = $losung_h->fetchrow_array) {
#    if($intro) {$intro = "<<$intro>>";} else {$intro = '';}
#    print $date->ymd, " losung: $which, $transl, $source |$intro|$text|\n";
#  }

  $losung_h->execute($date->ymd, 'OT');
  while (my ($which, $transl, $source, $intro, $text) = $losung_h->fetchrow_array) {
    verse(\$intro, \$text);
    print "1> $intro$text\n-> $source\n";
  }

  $losung_h->execute($date->ymd, 'NT');
  while (my ($which, $transl, $source, $intro, $text) = $losung_h->fetchrow_array) {
    verse(\$intro, \$text);
    print "2> $intro$text\n-> $source\n";
  }

#  $drittetext_h->execute($date->ymd);
#  while (my ($author, $dritte_text) = $drittetext_h->fetchrow_array) {
#    print $date->ymd, " drittetext: $author |$dritte_text|\n";
#  }

  $song_h->execute($date->ymd);
  while (my ($which, $book, $nr, $strophe, $strophe2, $text) = $song_h->fetchrow_array) {
    if($strophe2){$strophe .= '.'.$strophe2;}
    $text =~ s/\r//g;		#from DOS
    $text =~ s/\n\n/\n-\n/g;	#prázdný řádek nahradit rozdělovníkem – oddělení slok
    print "{ $book $nr,$strophe\n$text\n}\n";	# NIC NENAJDE?!
  }

  $reading_h->execute($date->ymd);
  while (my ($which, $source) = $reading_h->fetchrow_array) {
    print "$which> $source\n";
  }

  $comment_h->execute($date->ymd);
  while (my ($histdatetxt, $coment_text) = $comment_h->fetchrow_array) {
    print "_> $histdatetxt $coment_text\n";
  }

  print "\n";
}
# print "----------------\ncelkem $DBI::rows komentářů\n----------------\n";
$comment_h->finish;