From e4624ce9f82e49fff037088999c45e92f37208ec Mon Sep 17 00:00:00 2001
From: Christian Pointner <equinox@helsinki.at>
Date: Thu, 23 Jun 2016 16:49:05 +0200
Subject: added feature to export schedules list


diff --git a/ChangeLog b/ChangeLog
index d272201..8e955da 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -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();
-- 
cgit v0.10.2