diff options
| -rw-r--r-- | ChangeLog | 5 | ||||
| -rwxr-xr-x | lib/RHRD/rddb.pm | 107 | ||||
| -rwxr-xr-x | utils/rhrd-schedules | 25 |
3 files changed, 136 insertions, 1 deletions
@@ -1,3 +1,8 @@ +2016.06.23 -- Version 0.10.0 + +* libs now can export schedules including show titles and imported audio len +* rhrd-schedules has new command 'get' + 2016.06.16 -- Version 0.9.0 * rhrd-schedules has new command 'orphans' diff --git a/lib/RHRD/rddb.pm b/lib/RHRD/rddb.pm index 62146b5..99b4128 100755 --- a/lib/RHRD/rddb.pm +++ b/lib/RHRD/rddb.pm @@ -26,6 +26,7 @@ use strict; use Config::IniFiles; use DBI; use RHRD::utils; +use DateTime; use Date::Calc; ########################### constants ########################### @@ -1034,6 +1035,58 @@ sub create_or_update_schedule_log return (1, 'OK', 'success'); } +sub get_schedule_log +{ + my $ctx = shift; + my $year = shift; + my $month = shift; + my $day = shift; + + my $logname = sprintf("%04d_%02d_%02d", $year, $month, $day); + my ($log_exists, $status, $errorstring) = check_log_exists($ctx, $logname); + unless (defined $log_exists) { + return (undef, $status, $errorstring); + } + if(!$log_exists) { + return (undef, 'ERROR', "Log does not exist"); + } + + $logname = get_log_table_name_escaped($ctx, $logname); + my $sql = qq{select START_TIME,CART_NUMBER from $logname order by COUNT}; + + my $sth = $ctx->{'dbh'}->prepare($sql) + or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr); + + $sth->execute() + or return (undef, 'ERROR', "Database Error: " . $sth->errstr); + + my @show_dbs; + while(my ($start_time, $cart_number) = $sth->fetchrow_array()) { + my $entry = {}; + $entry->{'ID'} = $cart_number; + my ($title, $len, $status, $errorstring) = get_show_title_and_len($ctx, $cart_number); + unless (defined $title && defined $len) { + $sth->finish(); + return (undef, $status, $errorstring); + } + + $start_time = $start_time / 1000; + my $hour = int($start_time / 3600); + my $min = int(($start_time % 3600) / 60); + my $sec = int(($start_time % 60)); + my $start = DateTime->new(year => $year, month => $month, day => $day, hour => $hour, minute => $min, second => $sec, time_zone => 'Europe/Vienna'); + + $entry->{'TITLE'} = $title; + $entry->{'START'} = $start; + $entry->{'LEN'} = $len; + + push @show_dbs, $entry; + } + $sth->finish(); + + return @show_dbs; +} + ########################### SHOW handling ########################### sub get_shows_cart_range @@ -1390,6 +1443,60 @@ sub get_show_carts return @carts; } +sub get_show_title_and_len +{ + my ($ctx, $showid) = @_; + + my ($group_low_cart, $group_high_cart, $status, $errorstring) = get_show_group_cart_range($ctx, $showid); + unless (defined $group_low_cart && defined $group_high_cart) { + return (undef, undef, $status, $errorstring); + } + + (my $title, my $log, $status, $errorstring) = get_show_title_and_log($ctx, $showid); + unless (defined $title && defined $log) { + return (undef, undef, $status, $errorstring); + } + + (my $log_exists, $status, $errorstring) = check_log_exists($ctx, $log); + unless (defined $log_exists) { + return (undef, undef, $status, $errorstring); + } + unless($log_exists) { + return (undef, undef, 'ERROR', "Log with name '$log' does not exist") + } + + $log = get_log_table_name_escaped($ctx, $log); + my $sql = qq{select COUNT,CART_NUMBER from $log order by COUNT;}; + + my $sth = $ctx->{'dbh'}->prepare($sql) + or return (undef, undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr); + + $sth->execute() + or return (undef, undef, 'ERROR', "Database Error: " . $sth->errstr); + + my @carts; + while(my ($count, $cart) = $sth->fetchrow_array()) { + if($cart >= $group_low_cart && $cart <= $group_high_cart) { + push @carts, $cart; + } + } + $sth->finish(); + + $sql = qq{select SUM(AVERAGE_LENGTH) from CART where NUMBER in (} . join(',', @carts) . ")"; + + $sth = $ctx->{'dbh'}->prepare($sql) + or return (undef, undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr); + + $sth->execute() + or return (undef, undef, 'ERROR', "Database Error: " . $sth->errstr); + + my ($len) = $sth->fetchrow_array; + $sth->finish(); + $len = 0 unless (defined $len); + + return ($title, $len, 'OK', 'success'); +} + sub create_show_log { my ($ctx, $logname, $low_cart, $high_cart) = @_; diff --git a/utils/rhrd-schedules b/utils/rhrd-schedules index fbd8227..1bd3776 100755 --- a/utils/rhrd-schedules +++ b/utils/rhrd-schedules @@ -30,7 +30,7 @@ use DateTime::Format::Strptime; sub print_usage { - print STDERR "Usage: rhrd-schedules generate YYYY-MM-DD\n" . + print STDERR "Usage: rhrd-schedules (generate|get) YYYY-MM-DD\n" . " rhrd-schedules show (W1|W2|W3|W4|ALL) (MO|TU|WE|TH|FR|SA|SU|ALL)\n" . " rhrd-schedules orphans\n"; } @@ -90,6 +90,22 @@ sub generate return $errcnt; } +sub get +{ + my ($ctx, $year, $month, $day) = @_; + + my @shows = RHRD::rddb::get_schedule_log($ctx, $year, $month, $day); + if(!defined $shows[0] && defined $shows[1]) { + print STDERR "$shows[1]: $shows[2]\n"; + return 1; + } + for my $href (@shows) { + print $href->{'START'} . ": " . $href->{'ID'} . " | " . $href->{'TITLE'} . " (" . $href->{'LEN'} . " ms)\n"; + } + + return 0; +} + sub show__day { my ($ctx, $year, $month, $day) = @_; @@ -327,6 +343,13 @@ if(defined $ctx) { print_usage(); $ret = 1; } + } if($cmd eq "get") { + if($num_args == 2 && $ARGV[1] =~ m/^(\d{4})-(\d{2})-(\d{2})$/) { + $ret = get($ctx, $1, $2, $3); + } else { + print_usage(); + $ret = 1; + } } elsif($cmd eq "show") { if($num_args != 3) { print_usage(); |