#! /usr/bin/perl -w
# hes15sql-quote.pl přehodí citát do vlastní tabulky
# v. 13-03; ukládá $source
#use 5.010;
#use strict;
#use warnings;
use Modern::Perl;
use DBI;
use DateTime;
use utf8;
binmode STDOUT, ':utf8';
my $year = 2016;
my $dbname = 'hes16'; # Mirek
my $hostname = 'localhost';
my $dsn = "DBI:mysql:database=$dbname; host=$hostname"; #
my $user = 'petr';
my $password = '********';
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 @mesic_gen = qw(ledna února března dubna května června července srpna září října listopadu prosince);
my $prevmonth = -1; # registruje změnu měsíce
my $dbh = DBI->connect($dsn, $user, $password, { RaiseError=>1,AutoCommit=>0, mysql_enable_utf8 => 1 })
|| die "Chyba připojení k databázi č. $DBI::err: $DBI::errstr\n"; # uplatní se jen při RaiseError=>0
$dbh->do("set names 'utf8'");
print "'''Hesla Jednoty bratrské $year'''\n\n";
# PREPARE QUERIES
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 $losung_h = $dbh->prepare("SELECT `which`,`transl`,`source`,`intro`,`text` FROM `losung` WHERE `sel`=1 AND `date`=? AND `lang`='cs' AND `which`=?");
my $losung_del = $dbh->prepare("DELETE FROM `losung` WHERE `sel`=1 AND `date`=? AND `lang`='cs' AND `which`=? AND `transl`='oth' LIMIT 1");
my $quote_h = $dbh->prepare("SELECT `which`,`transl`,`source`,`text` FROM `quote` 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'");
#my $quote_ins = $dbh->prepare("INSERT INTO `quote` (`sel`,`date`,`which`,`lang`,`transl`,`source`,`text`) VALUES (1,?,?,'cs','MM','',?)");
my $quote_ins = $dbh->prepare("INSERT INTO `quote` VALUES (1, ?, ?, 'cs', 'MM', ?, ?)"); # 7 columns
my $date;
sub day {
my ($which) = @_;
$day_h->execute($date->ymd, $which);
while (my ($which,$nr,$text,$meaning,$src) = $day_h->fetchrow_array) {
# print $date->ymd, " day: $which, $nr, $text, $meaning |$src|\n";
$meaning = $meaning ? " ($meaning)" : '';
$src = $src ? "[$src]" : '';
# my $ind;
# given($which) {
# when('sunday') {$ind = '* '}
# when('week') {$ind = ': '}
# when('holiday') {$ind = ':: '}
# when('holiday2') {$ind = ':: '}
# when('important') {$ind = ':: '}
# default {$ind = '***??'}
# };
print "$which *** $text *** $meaning$src\n";
}
}
sub losung {
my ($which) = @_;
$losung_h->execute($date->ymd, $which);
while (my ($which, $transl, $source, $intro, $text) = $losung_h->fetchrow_array) {
$intro = $intro ? "<$intro:> " : '';
# if(($transl eq 'oth') && !$source) {
if($transl eq 'oth') {
if($which eq 'sunday' || $which eq 'holiday' || $which eq 'MONTH' ) {
# $source = '-QUOTE';
print '>>>';
$quote_ins->execute($date->ymd, $which, $source, $text);
$losung_del->execute($date->ymd, $which);
}
else {$source = '???';}
}
$text =~ s/\r//g; # from DOS
$text =~ s/\n/ /g;
printf ("%-30s |%s|\n", "$which [$source /$transl]", "$intro$text");
}
}
sub quote {
my ($which) = @_;
$quote_h->execute($date->ymd, $which);
while (my ($which, $transl, $source, $text) = $quote_h->fetchrow_array) {
#if (length($source) > 30) {die $source};
#$source .= substr(' ', length($source));
$text =~ s/\r//g; # from DOS
$text =~ s/\n/ /g;
printf ("QUOTE %-7s %-13s |%s|\n", $which, "[$source/$transl]", $text);
}
}
print "====== Rok $year =====\n\n";
# THE MAIN LOOP THROUGH ALL DAYS IN THE WHOLE YEAR
for($date = DateTime->new(year=>$year, month=>1, day=>1); $date->year==$year; $date->add(days=>1)) {
losung('year');
### MĚSÍC ###
# if($date->month_0 != $prevmonth){ # new month
# $prevmonth = $date->month_0;
# print "\n\#\#\# ", $mesic[$date->month_0], " \#\#\#\n\n";
# }
### DATUM ###
#print "\# ", $date->ymd, "\n";
### Název dne ###
printf "==== %s %d. %s ====\n", $dow[$date->day_of_week_0], $date->day, $mesic_gen[$date->month-1];
### Měsíc ###
losung('MONTH');
quote('MONTH');
### Neděle ###
day('sunday');
losung('sunday');
quote('sunday');
### Významný týden ###
day('week');
### Svátek ###
day('holiday');
losung('holiday');
quote('holiday');
### Svátek2 ###
day('holiday2');
losung('holiday2');
### Významný den ###
day('important');
### Heslo dne ###
losung('OT');
losung('NT');
$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
$text =~ s|\n| / |g;
print ": $book $nr,$strophe: $text\n"; # NIC NENAJDE?!
}
$reading_h->execute($date->ymd);
while (my ($which, $source) = $reading_h->fetchrow_array) {
print "$which [$source]\n";
}
print "\n";
}
#$sth->finish;
$dbh->disconnect;
print "-----------------------------------------------------------\n";