#! /usr/bin/perl -w

# hes17sql-pre.pl	převede data z MySQL databáze do prf
# v. 17-01;   table `siles` => `quote`

use DBI;
use DateTime;
use strict;

my $year	= 2017;
#my $date	=  DateTime->new( year => $year, month => 1, day  => 1 );

my $dbname	= 'hes17';
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 $siles_h = $dbh->prepare("SELECT `which`,`source`,`text` FROM `quote` WHERE `sel`=1 AND `date`=? AND `lang`='cs' AND `which`=?");
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 verse {			# zpracuje verš u Losung
    my ($intro_ref, $text_ref) = @_;
    if($$intro_ref) {$$intro_ref = "\t<<$$intro_ref:>>\n";} else {$$intro_ref = '';}
    $$text_ref =~ s/\r//g;		#from DOS
    $$text_ref = "\t" . $$text_ref;	#TAB
    $$text_ref =~ s/\n/\n\t/g;		#TAB
}


### Heslo roku ###  
  print "\# Hesla jednoty bratrské pro rok $year \#\n";
  $losung_h->execute("$year-01-01", 'YEAR');
  while (my ($which, $transl, $source, $intro, $text) = $losung_h->fetchrow_array) {
    if($intro) {$intro = "<<$intro>>";} else {$intro = '';}
    $text =~ s/\r//g;		#from DOS
    print "\ny> \t$intro$text\n-> $source\n";
  }

# 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";

### Heslo měsíce ###

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

  $siles_h->execute($date->ymd, 'MONTH');
  while (my ($which, $source, $text) = $siles_h->fetchrow_array) {
    $text =~ s/\r//g;		#from DOS
    print "{{$source\n$text\n}}\n";
  }

### Významný týden ###

  $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){$meaning="\no> $meaning";}
    if($src){$src="\n-> $src";}
    print "\n%> $text $meaning $src\n";
  }

### Významný den ###

  $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){$meaning="\no> $meaning";}
    if($src){$src="\n-> $src";}
    print "\n%> $text $meaning $src\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";
  }

  $siles_h->execute($date->ymd, 'sunday');
  while (my ($which, $source, $text) = $siles_h->fetchrow_array) {
    $text =~ s/\r//g;		#from DOS
    print "{{$source\n$text\n}}\n";
  }

### Svátek ###

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

  $siles_h->execute($date->ymd, 'holiday');
  while (my ($which, $source, $text) = $siles_h->fetchrow_array) {
    $text =~ s/\r//g;		#from DOS
    print "{{$source\n$text\n}}\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;

$dbh->disconnect;
__END__


$sth->execute();
while (my @row = $sth->fetchrow_array) { $svatek{$row[0]} = $row[1]; }
#foreach my $day(sort keys %svatek) { print "$day $svatek{$day}\n";}
#print "----------------\ncelkem $DBI::rows svátků\n----------------\n";
$sth->finish;


$sth = $dbh->prepare("SELECT * FROM hesla2008 ORDER BY datum");
$sth->execute();
my @months = qw(Nic Leden Únor Březen Duben Květen Červen Červenec Srpen Září Říjen Listopad Prosinec);
my @days = qw(Neděle Pondělí Úterý Středa Čtvrtek Pátek Sobota);
my $month = 7;	# začínáme červencem
my $dow = 2;	# 01.07.2008 je zrovna úterý

sub ifpr {	# if exists then print
  my ($tag, $field) = @_;
  if ($field) { print $tag, ' ', $field, "\n";}
}

sub correct {
  ($_) = @_;
  s/\r//g;
  s/\n/ /g;
  s/\.(\S)/. $1/g;
  s/:(\S)/: $1/g;
  s/\. "/."/g;
  s/: "/:"/g;
  return $_;
  }  
  
while (my @row = $sth->fetchrow_array) {
  #  print "@row\n";
  # datum, month, holiday, sunday, oldtestament, newtestament, song, readings
  # v=verse, s=source
    my ($datum, $mon_v, $mon_s, $hol_v, $hol_s, $sun_v, $sun_s, $ot_v, $ot_s,
        $nt_v, $nt_s, $song_v, $song_s, $read1, $read2, $read3) = @row; 

  $mon_v = &correct($mon_v);
  $hol_v = &correct($hol_v);
  $sun_v = &correct($sun_v);
  $ot_v = &correct($ot_v);
  $nt_v = &correct($nt_v);

  if($mon_v){ print "\n\# $months[$month++]\n";}
  ifpr('m>', $mon_v);
  ifpr('->', $mon_s);
  
  $datum =~ /(\d{4})-(\d{2})-(\d{2})/;
  my ($dd, $mm, $yyyy) = ($3, $2, $1);
  print "\n\# $dd.$mm.$yyyy ($dow)\n";

  if(exists $nedele{$datum}) { print "\n", '@>> ', "$nedele{$datum}\n";}
  ifpr('0>', $sun_v);
  ifpr('->', $sun_s);

  if(exists $svatek{$datum}) { print "\n", '$>> ', "$svatek{$datum}\n";}
  ifpr('0>', $hol_v);
  ifpr('->', $hol_s);

  printf ("\n%s %d. %s\n",  $dow ? 'x>' : '+>', $dd, $days[$dow]);
  ifpr('1>', $ot_v);
  ifpr('->', $ot_s);
  ifpr('2>', $nt_v);
  ifpr('->', $nt_s);

  if($song_s) { print "{ $song_s\n$song_v\n}\n";}
  
  ifpr('1->', $read1 );
  ifpr('2->', $read2);
  ifpr('3->', $read3);

  $dow = ++$dow % 7; 
}
# print "----------------\ncelkem $DBI::rows dní\n----------------\n";
$sth->finish;
$dbh->disconnect;