pool creation allmost done

3 files changed, 226 insertions, 20 deletions

diff --git a/lib/RHRD/rddb.pm b/lib/RHRD/rddb.pm
index 81ca9be..b576ad4 100755
--- a/lib/RHRD/rddb.pm
+++ b/lib/RHRD/rddb.pm
@@ -436,6 +436,133 @@ sub drop_logevent_table
   return (1, 'OK', 'success');
 }
 
+sub get_clock_table_name
+{
+  my ($clockname) = @_;
+  $clockname=~s/ /_/g;
+  return $clockname . '_CLK';
+}
+
+sub get_clock_table_name_escaped
+{
+  my ($ctx, $clockname) = @_;
+  return $ctx->{'dbh'}->quote_identifier(get_clock_table_name($clockname));
+}
+
+sub create_clock_table
+{
+  my ($ctx, $clockname) = @_;
+
+  $clockname = get_clock_table_name_escaped($ctx, $clockname);
+  my $sql = qq{
+    create table if not exists $clockname
+    (ID int unsigned auto_increment not null primary key,
+    EVENT_NAME char(64) not null,
+    START_TIME int not null,
+    LENGTH int not null,
+    INDEX EVENT_NAME_IDX (EVENT_NAME));
+  };
+
+  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);
+
+  $sth->finish();
+
+  return (1, 'OK', 'success');
+}
+
+sub fill_clock_table
+{
+  my $ctx = shift;
+  my $clockname = shift;
+  my @events = @_;
+
+  $clockname = get_clock_table_name_escaped($ctx, $clockname);
+  my $sql = qq{insert into $clockname (ID, EVENT_NAME, START_TIME, LENGTH) values (?, ?, ?, ?, ?);};
+
+  my $sth = $ctx->{'dbh'}->prepare($sql)
+    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
+
+  my $cnt = 0;
+  for my $event (@events) {
+    $sth->execute($cnt + 1, $event->{'NAME'}, $event->{'START_TIME'}, $event->{'LENGTH'})
+      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
+    $sth->finish();
+
+    $cnt++;
+  }
+
+  return ($cnt+1, 'OK', 'success');
+}
+
+sub drop_clock_table
+{
+  my ($ctx, $clockname) = @_;
+
+  $clockname = get_clock_table_name_escaped($ctx, $clockname);
+  my $sql = qq{drop table $clockname;};
+
+  $ctx->{'dbh'}->do($sql)
+    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
+
+  return (1, 'OK', 'success');
+}
+
+sub get_clockrules_table_name
+{
+  my ($clockname) = @_;
+  $clockname=~s/ /_/g;
+  return $clockname . '_RULES';
+}
+
+sub get_clockrules_table_name_escaped
+{
+  my ($ctx, $clockname) = @_;
+  return $ctx->{'dbh'}->quote_identifier(get_clockrules_table_name($clockname));
+}
+
+sub create_clockrules_table
+{
+  my ($ctx, $clockname) = @_;
+
+  $clockname = get_clockrules_table_name_escaped($ctx, $clockname);
+  my $sql = qq{
+    create table if not exists $clockname
+    (CODE varchar(10) not null primary key,
+    MAX_ROW int unsigned,
+    MIN_WAIT int unsigned,
+    NOT_AFTER varchar(10),
+    OR_AFTER varchar(10),
+    OR_AFTER_II varchar(10));
+  };
+
+  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);
+
+  $sth->finish();
+
+  return (1, 'OK', 'success');
+}
+
+sub drop_clockrules_table
+{
+  my ($ctx, $clockname) = @_;
+
+  $clockname = get_clockrules_table_name_escaped($ctx, $clockname);
+  my $sql = qq{drop table $clockname;};
+
+  $ctx->{'dbh'}->do($sql)
+    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
+
+  return (1, 'OK', 'success');
+}
+
 ###########################  TOKEN handling  ###########################
 
 sub get_token
@@ -1882,6 +2009,7 @@ sub get_musicpool_group
   unless(defined($groupname)) {
     return (undef, 'ERROR', "Pool not found");
   }
+
   return ($groupname, 'OK', 'success');
 }
 
@@ -1945,9 +2073,9 @@ sub get_musicpool_info
 
 sub create_musicpool_group
 {
-  my ($ctx, $groupname) = @_;
+  my ($ctx, $groupname, $title) = @_;
 
-  my ($cnt, $status, $errorstring) = add_group($ctx, $groupname);
+  my ($cnt, $status, $errorstring) = add_group($ctx, $groupname, $title);
   unless(defined $cnt) {
     return (undef, $status, $errorstring);
   }
@@ -1999,7 +2127,7 @@ sub create_musicpool_event
   my $sth = $ctx->{'dbh'}->prepare($sql)
     or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
 
-  $sth->execute($shortname, 'SEGUE, Scheduler', 3, 1, 1, '#' . $color, $groupname, 25)    # TODO: make title seperartion configurable !?!?
+  $sth->execute($shortname, 'SEGUE, Scheduler', 3, 1, 1, $color, $groupname, 25)    # TODO: make title seperartion configurable !?!?
     or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
   $sth->finish();
 
@@ -2024,6 +2152,59 @@ sub create_musicpool_event
   return (1, 'OK', 'success');
 }
 
+sub create_musicpool_clock
+{
+  my ($ctx, $shortname, $color) = @_;
+
+  my $sql = qq{insert into CLOCKS (NAME, SHORT_NAME, ARTISTSEP, COLOR, REMARKS) values (?, ?, ?, ?, '');};
+  my $sth = $ctx->{'dbh'}->prepare($sql)
+    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
+
+  $sth->execute($shortname, $shortname, 15, $color) # TODO: make artist seperation configurable !?!?
+    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
+  $sth->finish();
+
+  $sql = qq{insert into CLOCK_PERMS (CLOCK_NAME, SERVICE_NAME) values (?, ?);};
+  $sth = $ctx->{'dbh'}->prepare($sql)
+    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
+
+  $sth->execute($shortname, $ctx->{'config'}{'shows'}{'service'})
+    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
+  $sth->finish();
+
+  my ($cnt, $status, $errorstring) = create_clock_table($ctx, $shortname);
+  unless(defined $cnt) {
+    return (undef, $status, $errorstring);
+  }
+
+  ## TODO: fill clock table!!!!
+
+  ($cnt, $status, $errorstring) = create_clockrules_table($ctx, $shortname);
+  unless(defined $cnt) {
+    return (undef, $status, $errorstring);
+  }
+
+  return (1, 'OK', 'success');
+}
+
+sub create_musicpool_dropbox
+{
+  my ($ctx, $groupname, $shortname) = @_;
+  my $param = 'M;';
+
+  my $sql = qq{insert into DROPBOXES (STATION_NAME, GROUP_NAME, NORMALIZATION_LEVEL, AUTOTRIM_LEVEL, PATH, FIX_BROKEN_FORMATS, SET_USER_DEFINED) values (?, ?, ?, ?, ?, 'Y', ?)};
+
+  my $sth = $ctx->{'dbh'}->prepare($sql)
+    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
+
+  my $cnt = $sth->execute($ctx->{'config'}{'dropboxes'}{'dropbox-pseudo-station'}, $groupname, $ctx->{'config'}{'dropboxes'}{'norm-level'}, $ctx->{'config'}{'dropboxes'}{'trim-level'}, $shortname, $param)
+    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
+
+  $sth->finish();
+
+  return ($cnt, 'OK', 'success');
+}
+
 sub remove_musicpool
 {
   my ($ctx, $shortname) = @_;
@@ -2033,7 +2214,20 @@ sub remove_musicpool
     return (undef, $status, $errorstring);
   }
 
+  # TODO: remove grid/clock entries!
   my @actions = ({
+      # Delete Clock Permissions
+      sql => qq{delete from CLOCK_PERMS where CLOCK_NAME = ?;},
+      param => $shortname,
+      name => 'clock permissions',
+      cnt => 0
+    }, {
+      # Delete Event
+      sql => qq{delete from CLOCKS where NAME = ?;},
+      param => $shortname,
+      name => 'clocks',
+      cnt => 0
+    }, {
       # Delete Event Permissions
       sql => qq{delete from EVENT_PERMS where EVENT_NAME = ?;},
       param => $shortname,
@@ -2069,6 +2263,18 @@ sub remove_musicpool
   }
   push @actions, { name => 'log-event tables', cnt => ($cnt_pre + $cnt_post) };
 
+  (my $cnt_clk, $status, $errorstring) = drop_clock_table($ctx, $shortname);
+  unless (defined $cnt_pre) {
+    return (undef, $status, $errorstring);
+  }
+
+  (my $cnt_rules, $status, $errorstring) = drop_clockrules_table($ctx, $shortname);
+  unless (defined $cnt_post) {
+    return (undef, $status, $errorstring);
+  }
+  push @actions, { name => 'clock tables', cnt => ($cnt_clk + $cnt_rules) };
+
+
   my @group_results = remove_group($ctx, $groupname);
   if(!defined $group_results[0] && defined $group_results[2]) {
     return (undef, $group_results[1], $group_results[2]);
diff --git a/lib/RHRD/utils.pm b/lib/RHRD/utils.pm
index bcdac56..cdb810c 100755
--- a/lib/RHRD/utils.pm
+++ b/lib/RHRD/utils.pm
@@ -72,21 +72,21 @@ use constant {
   CMDLINE_DOW_HINT => "must be one of MO, TU, WE, TH, FR, SA, SU",
 
   # this is a subset of the colors from: stackoverflow.com/questions/2328339
-  POOL_COLORS => ["FFFFFF", "FFFF00", "1CE6FF", "FF34FF", "FF4A46", "008941", "006FA6",
-                  "FFDBE5", "7A4900", "0000A6", "63FFAC", "B79762", "004D43", "8FB0FF",
-                  "5A0007", "809693", "FEFFE6", "4FC601", "3B5DFF", "4A3B53", "FF2F80",
-                  "61615A", "BA0900", "6B7900", "00C2A0", "FFAA92", "FF90C9", "B903AA",
-                  "7B4F4B", "A1C299", "0AA6D8", "00846F", "997D87", "D16100", "6A3A4C",
-                  "FFB500", "C2FFED", "A079BF", "CC0744", "C0B9B2", "C2FF99", "012C58",
-                  "00489C", "6F0062", "0CBD66", "EEC3FF", "B77B68", "7A87A1", "788D66",
-                  "885578", "FAD09F", "FF8A9A", "D157A0", "BEC459", "0086ED", "886F4C",
-                  "549E79", "FFF69F", "72418F", "BC23FF", "99ADC0", "3A2465", "922329",
-                  "5B4534", "404E55", "0089A3", "CB7E98", "A4E804", "324E72", "A30059",
-                  "B4A8BD", "452C2C", "636375", "A3C8C9", "9B9700", "D0AC94", "FF6832",
-                  "575329", "00FECF", "B05B6F", "8CD0FF", "1E6E00", "66E1D3", "CFCDAC",
-                  "A77500", "6367A9", "A05837", "772600", "D790FF", "5B4E51", "8ADBB4",
-                  "83AB58", "D1F7CE", "C8D0F6", "A3A489", "806C66", "BF5650", "66796D",
-                  "DA007C", "FF1A59"]
+  POOL_COLORS => ["#FFFFFF", "#FFFF00", "#1CE6FF", "#FF34FF", "#FF4A46", "#008941", "#006FA6",
+                  "#FFDBE5", "#7A4900", "#0000A6", "#63FFAC", "#B79762", "#004D43", "#8FB0FF",
+                  "#5A0007", "#809693", "#FEFFE6", "#4FC601", "#3B5DFF", "#4A3B53", "#FF2F80",
+                  "#61615A", "#BA0900", "#6B7900", "#00C2A0", "#FFAA92", "#FF90C9", "#B903AA",
+                  "#7B4F4B", "#A1C299", "#0AA6D8", "#00846F", "#997D87", "#D16100", "#6A3A4C",
+                  "#FFB500", "#C2FFED", "#A079BF", "#CC0744", "#C0B9B2", "#C2FF99", "#012C58",
+                  "#00489C", "#6F0062", "#0CBD66", "#EEC3FF", "#B77B68", "#7A87A1", "#788D66",
+                  "#885578", "#FAD09F", "#FF8A9A", "#D157A0", "#BEC459", "#0086ED", "#886F4C",
+                  "#549E79", "#FFF69F", "#72418F", "#BC23FF", "#99ADC0", "#3A2465", "#922329",
+                  "#5B4534", "#404E55", "#0089A3", "#CB7E98", "#A4E804", "#324E72", "#A30059",
+                  "#B4A8BD", "#452C2C", "#636375", "#A3C8C9", "#9B9700", "#D0AC94", "#FF6832",
+                  "#575329", "#00FECF", "#B05B6F", "#8CD0FF", "#1E6E00", "#66E1D3", "#CFCDAC",
+                  "#A77500", "#6367A9", "#A05837", "#772600", "#D790FF", "#5B4E51", "#8ADBB4",
+                  "#83AB58", "#D1F7CE", "#C8D0F6", "#A3A489", "#806C66", "#BF5650", "#66796D",
+                  "#DA007C", "#FF1A59"]
 };
 
 sub get_musicpool_color
diff --git a/utils/rhrd-pool b/utils/rhrd-pool
index 866afe6..1534af6 100755
--- a/utils/rhrd-pool
+++ b/utils/rhrd-pool
@@ -97,7 +97,7 @@ sub add
     return 1
   }
 
-  (my $num, $status, $errorstring) = RHRD::rddb::create_musicpool_group($ctx, $groupname);
+  (my $num, $status, $errorstring) = RHRD::rddb::create_musicpool_group($ctx, $groupname, $title);
   unless(defined $num) {
     print STDERR $status . ": " . $errorstring . "\n";
     return 1;
@@ -118,7 +118,7 @@ sub add
     print STDERR $status . ": " . $errorstring . "\n";
     return 1;
   }
-  print " * created clock (color: " . $color . ")\n";
+  print " * created clock\n";
 
   ($result, $status, $errorstring) = RHRD::rddb::create_musicpool_dropbox($ctx, $groupname, $shortname);
   unless(defined $result) {