path: root/lib/RHRD/rddb.pm

# rddb.pm
#
#  rhrdlibs
#
#  Copyright (C) 2015 Christian Pointner <equinox@helsinki.at>
#
#  This file is part of rhrdlibs.
#
#  rhrdlibs is free software: you can redistribute it and/or modify
#  it under the terms of the GNU Affero General Public License as published by
#  the Free Software Foundation, either version 3 of the License, or
#  any later version.
#
#  rhrdlibs is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU Affero General Public License for more details.
#
#  You should have received a copy of the GNU Affero General Public License
#  along with rhrdlibs. If not, see <http://www.gnu.org/licenses/>.
#

package RHRD::rddb;

use strict;
use Config::IniFiles;
use DBI;

sub opendb
{
  my $RD_CONF = "/etc/rd.conf"; # TODO: hardcoded value
  my $cfg = Config::IniFiles->new(-file => $RD_CONF)
    or return (undef , 'ERROR', "Config File Error: " . join("\n", @Config::IniFiles::errors));

  my $dbhost = $cfg->val('mySQL', 'Hostname');
  my $dbname = $cfg->val('mySQL', 'Database');
  my $dbuser = $cfg->val('mySQL', 'Loginname');
  my $dbpasswd = $cfg->val('mySQL', 'Password');

  my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost","$dbuser","$dbpasswd")
    or return (undef, 'ERROR', "Database Error: " . $DBI::errstr);

  $dbh->do(qq{SET CHARACTER SET utf8;})
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  return ($dbh, 'OK', 'success');
}

sub closedb
{
  my $dbh = shift;
  $dbh->disconnect();
}

sub get_token
{
  my ($dbh, $username) = @_;

  my $sql = qq{select PASSWORD from USERS where LOGIN_NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($token) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $token) {
    return (undef, 'ERROR', "user '" . $username . "' not known by rivendell")
  }
  return ($token, 'OK', 'success');
}

sub set_token
{
  my ($dbh, $username, $token) = @_;

  my $sql = qq{update USERS set PASSWORD = ? where LOGIN_NAME = ?;};
  my $rows = $dbh->do($sql, undef, $token, $username)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  unless($rows == 1) {
    return (undef, 'ERROR', "user '" . $username . "' not known by rivendell")
  }
  return ($token, 'OK', 'success');
}

sub check_token
{
  my ($dbh, $username, $token) = @_;

  my $sql = qq{select PASSWORD from USERS where LOGIN_NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($token_result) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $token_result) {
    return (undef, 'ERROR', "user '" . $username . "' not known by rivendell")
  }

  if($token_result eq $token) {
    return (1, 'OK', 'success');
  }
  return (0, 'ERROR', "wrong password");
}

sub get_users
{
  my ($dbh) = @_;

  my $sql = qq{select LOGIN_NAME from USERS order by LOGIN_NAME;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute()
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my @users;
  while(my ($user) = $sth->fetchrow_array()) {
    push @users, $user;
  }
  $sth->finish();

  return @users;
}

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, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($showid)
    or return (undef, undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($title, $macros) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $title) {
    return (undef, undef, 'ERROR', "Show with ID=" . $showid .  " not found!")
  }
  unless(defined $macros) {
    return (undef, undef, 'ERROR', "Show with ID=" . $showid . " has no macro!");
  }

  unless($macros =~ /^LL 1 ([^ ]+) 0\!$/) {
    return (undef, undef, 'ERROR', "Show with ID=" . $showid . " has invalid macro: '" . $macros . "'");
  }
  my $log = $1;

  return ($title, $log, 'OK', 'success');
}

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, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($username, 'import-dropbox') # TODO: hardcoded value
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my @allowed_dbs;
  while(my ($group, $to_cart, $normlevel, $trimlevel, $params, $lowcart, $highcart, $groupdesc) = $sth->fetchrow_array()) {
    my @p = split(';', $params);

    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;
    if($p[0] eq "S") {
      $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);
      }
      $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];
    } elsif($p[0] eq "J") {
      $entry->{'TYPE'} = 'jingle';
      $entry->{'JINGLETITLE'} = $groupdesc;
    } elsif($p[0] eq "M") {
      $entry->{'TYPE'} = 'musicpool';
      $entry->{'MUSICPOOLTITLE'} = $groupdesc;
    }

    push @allowed_dbs, $entry;
  }
  $sth->finish();

  return @allowed_dbs;
}

sub get_show_carts
{
  my ($dbh, $logname, $group_low_cart, $group_high_cart) = @_;

  my $sql = qq{select LOG_EXISTS from LOGS where NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($logname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my $log_exists = $sth->fetchrow_array;
  $sth->finish();

  if(!defined $log_exists || $log_exists ne 'Y') {
    return (undef, 'ERROR', "Log with name '$logname' does not exist")
  }

  $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, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute()
    or return (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();

  return @carts;
}

return 1;