Projekt: Hesla Jednoty bratrské/2016/hes16sql-quote.pl

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