fetch dropboxes and listing allowed shows works now

2 files changed, 137 insertions, 68 deletions

diff --git a/rhimport b/rhimport
index 3a29733..ba40b9a 100755
--- a/rhimport
+++ b/rhimport
@@ -32,10 +32,6 @@ my $FILE = "";
 my $DROPBOX = "";
 my $LISTALLOWED = 0;
 
-binmode(STDIN, ":utf8");
-binmode(STDOUT, ":utf8");
-binmode(STDERR, ":utf8");
-
 GetOptions ("help!" => \$HELP,
             "file=s" => \$FILE,
             "dropbox=s" => \$DROPBOX,
@@ -48,8 +44,8 @@ usage: $0 --file <audio file> --dropbox <path to dropbox> --list-allowed
 
 options:
      -f | --file          the media file or playlist to import
-     -d | --dropbox       the path to the dropbox to use
-     -l | --list-allowed  list allowed dropboxes and exit
+     -i | --id            the show id to import to
+     -l | --list-allowed  list allowed shows and exit
 
 EOF
   exit 0;
@@ -57,17 +53,31 @@ EOF
 
 my $user = `/usr/bin/id -un`;
 $user =~ s/\n//;
+$user = 'heslinki';
 
 my ($dbh, $errorstring) = rhimport::opendb();
 if(!defined $dbh) {
   print "$errorstring\n";
   exit 1;
 }
-my @allowed_dbs = rhimport::get_dropboxes($dbh, $user);
+(my @allowed_dbs, $errorstring) = rhimport::get_dropboxes($dbh, $user);
+unless(defined $allowed_dbs[0]) {
+  print "$allowed_dbs[1]\n";
+  exit 1;
+}
 
 if($LISTALLOWED) {
+  print "| id    | Title\n";
+  print "+-------+-------------------------------------------------------------------\n";
   for my $href ( @allowed_dbs ) {
-    print "$href->{'NAME'} \t-> $href->{'PATH'}\n" unless $href->{'NAME'} =~ /^autoimport/;
+    if($href->{'TYPE'} eq 'show') {
+      my @show_carts = rhimport::get_show_carts($dbh, $href->{'SHOWLOG'}, $href->{'GROUPLOWCART'}, $href->{'GROUPHIGHCART'});
+      unless(defined $show_carts[0]) {
+        print "$show_carts[1]\n";
+        exit 1;
+      }
+      print "| $href->{'SHOWID'} | $href->{'SHOWTITLE'}, Carts: [" . join(', ', @show_carts) . "]\n";
+    }
   }
   rhimport::closedb($dbh);
   exit 0;
diff --git a/rhimport.pm b/rhimport.pm
index 3451bcd..3cdcf62 100644
--- a/rhimport.pm
+++ b/rhimport.pm
@@ -117,86 +117,145 @@ sub closedb
   $dbh->disconnect();
 }
 
-sub get_dropboxes
+sub get_token
 {
-  my ($dbh, $user, $group) = @_;
+  my ($dbh, $username) = @_;
 
-  my $sql = "";
-  if($group) {
-    $sql = qq{select USER_PERMS.GROUP_NAME,DROPBOXES.PATH,DROPBOXES.TO_CART,GROUPS.DESCRIPTION from USER_PERMS, DROPBOXES, GROUPS where USER_PERMS.USER_NAME='$user' and DROPBOXES.GROUP_NAME=USER_PERMS.GROUP_NAME and DROPBOXES.GROUP_NAME=GROUPS.NAME and DROPBOXES.GROUP_NAME='$group';};
-  } else {
-    $sql = qq{select USER_PERMS.GROUP_NAME,DROPBOXES.PATH,DROPBOXES.TO_CART,GROUPS.DESCRIPTION from USER_PERMS, DROPBOXES, GROUPS where USER_PERMS.USER_NAME='$user' and DROPBOXES.GROUP_NAME=USER_PERMS.GROUP_NAME and DROPBOXES.GROUP_NAME=GROUPS.NAME;};
+  my $sql = qq{select PASSWORD from USERS where LOGIN_NAME = ?;};
+  my $sth = $dbh->prepare($sql)
+    or return (undef, "Database Error: " . $dbh->errstr);
+
+  $sth->execute($username)
+    or return (undef, "Database Error: " . $sth->errstr);
+
+  my ($token) = $sth->fetchrow_array;
+  $sth->finish();
+
+  unless(defined $token) {
+    return (undef, "user '" . $username . "' not known by rivendell")
+  }
+  return ($token, 'OK', 'success');
+}
+
+
+sub get_showtitle_and_log
+{
+  my ($dbh, $showid) = @_;
+
+  my $sql = qq{select TITLE,MACROS from CART where NUMBER = ?;};
+  my $sth = $dbh->prepare($sql)
+    or return (undef, undef, "Database Error: " . $dbh->errstr);
+
+  $sth->execute($showid)
+    or return (undef, undef, "Database Error: " . $sth->errstr);
+
+  my ($title, $macros) = $sth->fetchrow_array;
+  $sth->finish();
+
+  unless(defined $title) {
+    return (undef, undef, "Show with ID=" . $showid .  " not found!")
+  }
+  unless(defined $macros) {
+    return (undef, undef, "Show with ID=" . $showid . " has no macro!");
   }
-  my $sth = $dbh->prepare($sql);
-  $sth->execute();
+
+  unless($macros =~ /^LL 1 ([^ ]+) 0\!$/) {
+    return (undef, undef, "Show with ID=" . $showid . " has invalid macro: '" . $macros . "'");
+  }
+  my $log = $1;
+
+  return ($title, $log);
+}
+
+sub get_dropboxes
+{
+  my ($dbh, $username) = @_;
+
+  my $sql = qq{select USER_PERMS.GROUP_NAME,DROPBOXES.TO_CART,DROPBOXES.NORMALIZATION_LEVEL,DROPBOXES.AUTOTRIM_LEVEL,DROPBOXES.SET_USER_DEFINED,GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART,GROUPS.DESCRIPTION from USER_PERMS, DROPBOXES, GROUPS where USER_PERMS.USER_NAME=? and DROPBOXES.GROUP_NAME=USER_PERMS.GROUP_NAME and DROPBOXES.GROUP_NAME=GROUPS.NAME and DROPBOXES.STATION_NAME=?;};
+
+  my $sth = $dbh->prepare($sql)
+    or return (undef, "Database Error: " . $dbh->errstr);
+
+  $sth->execute($username, 'import-dropbox') # TODO: hardcoded value
+    or return (undef, "Database Error: " . $sth->errstr);
+
   my @allowed_dbs;
-  while(my ($group, $path, $to_cart, $desc) = $sth->fetchrow_array()) {
-    $path =~ s/\/\*$//;
-    my $name = $path;
-    $name =~ s/^\/programm\///;
-    if($name =~ /^([0-9]{2}-[A-Za-z]+)\/([0-9]{2})([0-9]{2})-([01]{4})-([0-9]{3})-(.*)$/) {
-      $name = "$1 - $2:$3 - $6 ($4, $5)";
-    }
-    elsif($name =~ /^([0-9]{2}-[A-Za-z]+)\/programmvorschau_(.*)$/) {
-      $name = "Programmvorschau - $1 - $2";
-    }
-    elsif($name =~ /^([0-9]{2}-[A-Za-z]+)\/jingle$/ || $name =~ /^jingles\/(.*)$/) {
-      $name = "Jingles - $1";
-    }
-    elsif($name =~ /^pool\/pool(.*)$/) {
-      $name = "Pool $1 - $desc";
-    }
-    elsif($name =~ /^pool\/(.*)$/) {
-      $name = "Pool - $1";
-    }
-    elsif($name =~ /^sondersendungen\/(.*)$/) {
-      $name = "Sondersendungen - $1";
+  while(my ($group, $to_cart, $normlevel, $trimlevel, $params, $lowcart, $highcart, $groupdesc) = $sth->fetchrow_array()) {
+    my @p = split(';', $params);
+
+    next unless($p[0] eq "S");
+
+    my $entry = {};
+    $entry->{'GROUP'} = $group;
+    $entry->{'GROUPDESC'} = $groupdesc;
+    $entry->{'GROUPLOWCART'} = int $lowcart;
+    $entry->{'GROUPHIGHCART'} = int $highcart;
+    $entry->{'NORMLEVEL'} = int $normlevel;
+    $entry->{'TRIMLEVEL'} = int $trimlevel;
+    $entry->{'PARAM'} = $params;
+    $entry->{'TYPE'} = 'show';
+    $entry->{'SHOWID'} = $to_cart;
+
+    my ($title, $log, $status, $errorstring) = get_showtitle_and_log($dbh, $to_cart);
+    unless (defined $title && defined $log) {
+      return (undef, $status, $errorstring);
     }
-    elsif($name =~ /^autoimport\/(.*)$/) {
-      $name = "autoimport - $1";
-    }
-    my $perm = {};
-    $perm->{'GROUP'} = $group;
-    $perm->{'PATH'} = $path;
-    $perm->{'TO_CART'} = $to_cart;
-    $perm->{'NAME'} = $name;
-    push @allowed_dbs, $perm;
+    $entry->{'SHOWTITLE'} = $title;
+    $entry->{'SHOWLOG'} = $log;
+
+    $entry->{'SHOWRHYTHM'} = $p[1];
+    $entry->{'SHOWDOW'} = int $p[2];
+    $entry->{'SHOWDOW'} = 0 unless $entry->{'SHOWDOW'} < 7;
+    substr($p[3], 2, 0) = ':';
+    $entry->{'SHOWSTARTTIME'} = $p[3];
+    $entry->{'SHOWLEN'} = int $p[4];
+
+    push @allowed_dbs, $entry;
   }
   $sth->finish();
 
-  return sort { uc($a->{'NAME'}) cmp uc($b->{'NAME'}) } @allowed_dbs;
+  return @allowed_dbs;
 }
 
-sub get_cart_range
+sub get_show_carts
 {
-  my ($dbh, $group) = @_;
+  my ($dbh, $logname, $group_low_cart, $group_high_cart) = @_;
 
-  my $sql = qq{select DEFAULT_LOW_CART,DEFAULT_HIGH_CART from GROUPS where NAME='$group';};
-  my $sth = $dbh->prepare($sql);
-  $sth->execute();
-  my @carts;
-  my ($low_cart, $high_cart) = $sth->fetchrow_array();
+  my $sql = qq{select LOG_EXISTS from LOGS where NAME = ?;};
+  my $sth = $dbh->prepare($sql)
+    or return (undef, "Database Error: " . $dbh->errstr);
+
+  $sth->execute($logname)
+    or return (undef, "Database Error: " . $sth->errstr);
+
+  my $log_exists = $sth->fetchrow_array;
   $sth->finish();
 
-  return ($low_cart, $high_cart);
-}
+  if(!defined $log_exists && $log_exists eq 'Y') {
+    return (undef, "log $logname does not exist")
+  }
 
-sub get_used_carts
-{
-  my ($dbh, $group) = @_;
+  $logname=~s/ /_/g;
+  $logname = $dbh->quote_identifier($logname . '_LOG');
+  $sql = qq{select COUNT,CART_NUMBER from $logname order by COUNT;};
+
+  $sth = $dbh->prepare($sql)
+    or return (undef, "Database Error: " . $dbh->errstr);
+
+  $sth->execute()
+    or return (undef, "Database Error: " . $sth->errstr);
 
-  my ($low_cart, $high_cart) = get_cart_range($dbh, $group);
   my @carts;
+  while(my ($count, $cart) = $sth->fetchrow_array()) {
+    if($cart >= $group_low_cart && $cart <= $group_high_cart) {
+      push @carts, $cart;
+    }
+  }
+  $sth->finish();
+
   return @carts;
 }
 
-sub get_num_carts
-{
-  my ($dbh, $group) = @_;
-
-  my ($low_cart, $high_cart) = get_cart_range($dbh, $group);
-  return $high_cart - $low_cart + 1;
-}
 
 sub clear_carts
 {