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