* Remove double query to select fields from SQL Query

* search, movetimeredit: Show channels groups 
* now/next/schema: show weekday as group
* AUTOTIMER: Fuzzy compare between matching event and exists timers, recordings, chronicle. see mysql/soundex
* AUTOTIMER: Detect exists timer with changed starttime inside pretime and aftertime from timers, inseed fixed five minutes
* AUTOTIMER: status with translated field headers
* MOVETIMER/CHRONICLE: mtl/chrl/chrs - query name from channels with single SQL-Query

8 files changed, 119 insertions, 64 deletions

diff --git a/lib/XXV/MODULES/AUTOTIMER.pm b/lib/XXV/MODULES/AUTOTIMER.pm
index 3bc812a..eae1282 100644
--- a/lib/XXV/MODULES/AUTOTIMER.pm
+++ b/lib/XXV/MODULES/AUTOTIMER.pm
@@ -174,17 +174,26 @@ sub status {
     my $console = shift;
     my $lastReportTime = shift || 0;
 
+    my %f = (
+        'title' => gettext('Title'),
+        'day' => gettext('Day'),
+        'channel' => gettext('Channel'),
+        'start' => gettext('Start'),
+        'stop' => gettext('Stop'),
+        'priority' => gettext('Priority')
+    );
+
     my $sql = qq|
 SELECT SQL_CACHE 
     t.id as __id,
-    t.file,
+    t.file as \'$f{'title'}\',
     t.flags as __flags,
-    c.Name as Channel,
+    c.Name as \'$f{'channel'}\',
     c.Pos as __Pos,
-    DATE_FORMAT(t.day, '%e.%c.%Y') as Day,
-    t.start,
-    t.stop,
-    t.priority,
+    UNIX_TIMESTAMP(t.starttime) as \'$f{'day'}\',
+    t.start as \'$f{'start'}\',
+    t.stop as \'$f{'stop'}\',
+    t.priority as \'$f{'priority'}\',
     UNIX_TIMESTAMP(t.starttime) as __day,
     t.collision as __collision,
     t.eventid as __eventid,
@@ -199,17 +208,18 @@ WHERE
 ORDER BY
     t.starttime|;
 
-    my $fields = fields($obj->{dbh}, $sql);
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute($lastReportTime)
         or return error "Couldn't execute query: $sth->errstr.";
+    my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
     for(@$erg) {
+        $_->[5] = datum($_->[5],'weekday');
         $_->[6] = fmttime($_->[6]);
         $_->[7] = fmttime($_->[7]);
     }
-
     unshift(@$erg, $fields);
+
     return {
         message => sprintf(gettext('Autotimer has programmed %d new timer(s) since last report to %s'),
             (scalar @$erg - 1), scalar localtime($lastReportTime)),
@@ -493,7 +503,7 @@ sub _autotimerLookup {
             next if($obj->_timerexists($event));
 
             # Adjust timers set by the autotimer
-            my $timerID = $obj->_timerexistsfuzzy($event);
+            my $timerID = $obj->_timerexistsfuzzy($event,$a,$modT);
 
             if(scalar @done) {
 
@@ -1359,19 +1369,34 @@ sub _timerexistsfuzzy {
 # ------------------
     my $obj = shift  || return error('No object defined!');
     my $eventdata = shift  || return error('No data defined!');
+    my $a   = shift  || return error('No data defined!');
+    my $modT = shift || return error('No timer modul defined!');
+
+	  my $after = 0;
+	  my $prev = 0;
+    if(defined $a->{prevminutes}) {
+		  $prev = $a->{prevminutes} * 60;
+	  } else {
+		  $prev = $modT->{prevminutes} * 60;
+	  }
+	  if(defined $a->{afterminutes}) {
+		  $after = $a->{afterminutes} * 60;
+	  } else {
+		  $after = $modT->{afterminutes} * 60;
+	  }
 
     # Adjust timers set by the autotimer, if event changed +/- five minutes. 
     my $sql = "SELECT SQL_CACHE id from TIMERS where
                 channel = ?
-                and ? between (UNIX_TIMESTAMP(starttime) - 300) AND (UNIX_TIMESTAMP(starttime) + 300)
-                and ? between (UNIX_TIMESTAMP(stoptime)  - 300) AND (UNIX_TIMESTAMP(stoptime) + 300)
+                and ? between (UNIX_TIMESTAMP(starttime) - ?) AND (UNIX_TIMESTAMP(starttime) + ?)
+                and ? between (UNIX_TIMESTAMP(stoptime)  - ?) AND (UNIX_TIMESTAMP(stoptime) + ?)
                 and file = ?
                 and aux like ?";
 
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute($eventdata->{channel},
-                  $eventdata->{starttime},
-                  $eventdata->{stoptime},
+                  $eventdata->{starttime},$prev,$prev,
+                  $eventdata->{stoptime},$after,$after,
                   $eventdata->{file},
                   "%".$eventdata->{aux})
         or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
@@ -1391,7 +1416,7 @@ sub _recordexists {
     my $sql = "SELECT SQL_CACHE count(*) as cc
                 FROM RECORDS as r, OLDEPG as e
                 WHERE e.eventid = r.EventId
-                    AND CONCAT_WS('~',e.title,IF(e.subtitle<>'',e.subtitle,NULL)) = ?";
+                    AND CONCAT_WS('~',e.title,IF(e.subtitle<>'',e.subtitle,NULL)) SOUNDS LIKE ?";
 
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute($eventdata->{file})
@@ -1412,7 +1437,7 @@ sub _chronicleexists {
     return 0
       unless($modCH and $modCH->{active} eq 'y');
 
-    my $sql = "SELECT SQL_CACHE count(*) as cc from CHRONICLE where title = ?";
+    my $sql = "SELECT SQL_CACHE count(*) as cc from CHRONICLE where title SOUNDS LIKE ?";
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute($eventdata->{file})
         or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
@@ -1428,7 +1453,7 @@ sub _timerexiststitle {
     my $obj = shift  || return error('No object defined!');
     my $eventdata = shift  || return error('No data defined!');
 
-    my $sql = "SELECT SQL_CACHE count(*) as cc from TIMERS where file = ?";
+    my $sql = "SELECT SQL_CACHE count(*) as cc from TIMERS where file SOUNDS LIKE ?";
 
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute($eventdata->{file})
diff --git a/lib/XXV/MODULES/CHRONICLE.pm b/lib/XXV/MODULES/CHRONICLE.pm
index 005307d..0d61c2e 100644
--- a/lib/XXV/MODULES/CHRONICLE.pm
+++ b/lib/XXV/MODULES/CHRONICLE.pm
@@ -164,7 +164,6 @@ sub list {
     my %f = (
         'id' => gettext('Service'),
         'title' => gettext('Title'),
-        'subtitle' => gettext('Subtitle'),
         'channel' => gettext('Channel'),
         'day' => gettext('Day'),
         'start' => gettext('Start'),
@@ -173,17 +172,26 @@ sub list {
 
     my $sql = qq|
 SELECT SQL_CACHE 
-  CHRONICLE.id as \'$f{'id'}\',
-  CHRONICLE.title as \'$f{'title'}\',
-  CHRONICLE.channel_id as \'$f{'channel'}\',
-  DATE_FORMAT(CHRONICLE.starttime, '%d.%m.%Y') as \'$f{'day'}\',
-  DATE_FORMAT(CHRONICLE.starttime, '%H:%i') as \'$f{'start'}\',
-  DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(CHRONICLE.starttime) + CHRONICLE.duration), '%H:%i') as \'$f{'stop'}\'
-FROM CHRONICLE
-ORDER BY CHRONICLE.starttime
+  id as \'$f{'id'}\',
+  title as \'$f{'title'}\',
+  (SELECT Name
+      FROM CHANNELS as c
+      WHERE channel_id = c.Id
+      LIMIT 1) as \'$f{'channel'}\',
+  UNIX_TIMESTAMP(starttime) as \'$f{'day'}\',
+  DATE_FORMAT(starttime, '%H:%i') as \'$f{'start'}\',
+  DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(starttime) + duration), '%H:%i') as \'$f{'stop'}\'
+FROM CHRONICLE WHERE id > 0
+ORDER BY starttime
 |;
-    my $fields = fields($self->{dbh}, $sql);
-    my $erg = $self->{dbh}->selectall_arrayref($sql);
+    my $sth = $self->{dbh}->prepare($sql);
+    $sth->execute()
+        or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+    my $fields = $sth->{'NAME'};
+    my $erg = $sth->fetchall_arrayref();
+    map {
+        $_->[3] = datum($_->[3],'weekday');
+    } @$erg;
     unshift(@$erg, $fields);
     $console->table($erg);
 
@@ -203,7 +211,6 @@ sub search {
     my %f = (
         'id' => gettext('Service'),
         'title' => gettext('Title'),
-        'subtitle' => gettext('Subtitle'),
         'channel' => gettext('Channel'),
         'day' => gettext('Day'),
         'start' => gettext('Start'),
@@ -212,20 +219,26 @@ sub search {
 
     my $sql = qq|
 SELECT SQL_CACHE 
-  CHRONICLE.id as \'$f{'id'}\',
-  CHRONICLE.title as \'$f{'title'}\',
-  CHRONICLE.channel_id as \'$f{'channel'}\',
-  DATE_FORMAT(CHRONICLE.starttime, '%d.%m.%Y') as \'$f{'day'}\',
-  DATE_FORMAT(CHRONICLE.starttime, '%H:%i') as \'$f{'start'}\',
-  DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(CHRONICLE.starttime) + CHRONICLE.duration), '%H:%i') as \'$f{'stop'}\'
+  id as \'$f{'id'}\',
+  title as \'$f{'title'}\',
+  (SELECT Name
+      FROM CHANNELS as c
+      WHERE channel_id = c.Id
+      LIMIT 1) as \'$f{'channel'}\',
+  UNIX_TIMESTAMP(starttime) as \'$f{'day'}\',
+  DATE_FORMAT(starttime, '%H:%i') as \'$f{'start'}\',
+  DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(starttime) + duration), '%H:%i') as \'$f{'stop'}\'
 FROM CHRONICLE
 |;
-    $sql .= sprintf("WHERE %s ORDER BY CHRONICLE.starttime",$query->{query});
-    my $fields = fields($self->{dbh}, $sql);
+    $sql .= sprintf("WHERE %s ORDER BY starttime",$query->{query});
     my $sth = $self->{dbh}->prepare($sql);
     $sth->execute(@{$query->{term}})
         or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+    my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
+    map {
+        $_->[3] = datum($_->[3],'weekday');
+    } @$erg;
     unshift(@$erg, $fields);
     $console->table($erg);
 
diff --git a/lib/XXV/MODULES/EPG.pm b/lib/XXV/MODULES/EPG.pm
index 4cfc013..a827dae 100644
--- a/lib/XXV/MODULES/EPG.pm
+++ b/lib/XXV/MODULES/EPG.pm
@@ -676,10 +676,11 @@ sub search {
     order by
         starttime
         |;
-        my $fields = fields($obj->{dbh}, $sql);
+
         my $sth = $obj->{dbh}->prepare($sql);
         $sth->execute(@{$search->{term}})
           or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
+        my $fields = $sth->{'NAME'};
         $erg = $sth->fetchall_arrayref();
         map {
             $_->[7] = datum($_->[7],'weekday');
@@ -689,7 +690,7 @@ sub search {
     }
     my $modC = main::getModule('CHANNELS');
     $console->table($erg,  {
-                            channels => $modC->ChannelArray('Name'),
+                            channels => $modC->ChannelWithGroup('Name,Pos'),
     });
 }
 
@@ -757,10 +758,10 @@ where
 order by
     starttime
 |;
-    my $fields = fields($obj->{dbh}, $sql);
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute($cid)
         or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
+    my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
     map {
         $_->[5] = datum($_->[5],'weekday');
@@ -840,10 +841,10 @@ where
     e.channel_id = c.Id
     and eventid = ?
 |;
-    $fields = fields($obj->{dbh}, $sql);
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute($eventid)
         or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
+    $fields = $sth->{'NAME'};
     $erg = $sth->fetchall_arrayref();
 
     last
@@ -949,10 +950,11 @@ WHERE
     AND c.GRP = ?
 ORDER BY
     c.POS|;
-    my $fields = fields($obj->{dbh}, $sql);
+
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute($cgrp)
         or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
+    my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
     unshift(@$erg, $fields);
 
@@ -1036,10 +1038,10 @@ WHERE
 ORDER BY
     c.POS|;
 
-    my $fields = fields($obj->{dbh}, $sql);
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute($zeit, $cgrp)
         or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
+    my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
     unshift(@$erg, $fields);
 
@@ -1203,10 +1205,10 @@ ORDER BY
     c.POS,e.starttime
 |;
 
-    my $fields = fields($obj->{dbh}, $sql);
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute($zeitvon,$zeitbis,$zeitvon,$zeitbis,$zeitvon,$zeitbis,$cgrp)
         or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
+    my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
 
     my $data = {};
diff --git a/lib/XXV/MODULES/MOVETIMER.pm b/lib/XXV/MODULES/MOVETIMER.pm
index c9df382..e33c0b2 100644
--- a/lib/XXV/MODULES/MOVETIMER.pm
+++ b/lib/XXV/MODULES/MOVETIMER.pm
@@ -377,7 +377,7 @@ sub movetimeredit {
         'source' => {
             typ     => 'list',
             def     => $con ? $modC->ChannelToPos($rule->{source}) : $rule->{source},
-            choices => $con ? $modC->ChannelArray('Name') : $modC->ChannelIDArray('Name'),
+            choices => $con ? $modC->ChannelArray('Name') : $modC->ChannelWithGroup('Name,Id'),
             msg     => gettext('Which channel should used as source?'),
             req     => gettext("This is required!"),
             check   => sub{
@@ -399,7 +399,7 @@ sub movetimeredit {
         'destination' => {
             typ     => 'list',
             def     => $con ? $modC->ChannelToPos($rule->{destination}) : $rule->{destination},
-            choices => $con ? $modC->ChannelArray('Name') : $modC->ChannelIDArray('Name'),
+            choices => $con ? $modC->ChannelArray('Name') : $modC->ChannelWithGroup('Name,Id'),
             msg     => gettext('Which channel should used as destination?'),
             req     => gettext("This is required!"),
             check   => sub{
@@ -552,18 +552,27 @@ sub movetimerlist {
     my $sql = qq|
     select
       id as \'$f{'id'}\',
-      source as \'$f{'source'}\',
-      destination as \'$f{'destination'}\',
+      (SELECT Name
+          FROM CHANNELS as c
+          WHERE m.source = c.Id
+          LIMIT 1) as \'$f{'source'}\',
+      (SELECT Name
+          FROM CHANNELS as c
+          WHERE m.destination = c.Id
+          LIMIT 1) as \'$f{'destination'}\',
       move as \'$f{'move'}\',
       original as \'$f{'original'}\'
     from
-      MOVETIMER
+      MOVETIMER as m
     order by 
       id
     |;
 
-    my $fields = fields($self->{dbh}, $sql);
-    my $erg = $self->{dbh}->selectall_arrayref($sql);
+    my $sth = $self->{dbh}->prepare($sql);
+    $sth->execute()
+        or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+    my $fields = $sth->{'NAME'};
+    my $erg = $sth->fetchall_arrayref();
 
     my %m;
     my %d;
@@ -577,10 +586,7 @@ sub movetimerlist {
       $d{$drr->[0]} = $drr->[1];
     }
 
-    my $modC = main::getModule('CHANNELS');
     map { 
-      $_->[1] = $modC->ChannelToName($_->[1]);
-      $_->[2] = $modC->ChannelToName($_->[2]);
       $_->[3] = $m{$_->[3]};
       $_->[4] = $d{$_->[4]};
     } @$erg;
diff --git a/lib/XXV/MODULES/MUSIC.pm b/lib/XXV/MODULES/MUSIC.pm
index 00470fe..4163c3d 100644
--- a/lib/XXV/MODULES/MUSIC.pm
+++ b/lib/XXV/MODULES/MUSIC.pm
@@ -687,11 +687,10 @@ sub list {
         |;
     }
 
-    my $fields = fields($dbh, $sql);
-
     my $sth = $dbh->prepare($sql);
     $sth->execute(@{$term})
       or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+    my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
     unshift(@$erg, $fields);
 
diff --git a/lib/XXV/MODULES/RECORDS.pm b/lib/XXV/MODULES/RECORDS.pm
index 1515a12..f207e3c 100644
--- a/lib/XXV/MODULES/RECORDS.pm
+++ b/lib/XXV/MODULES/RECORDS.pm
@@ -1392,9 +1392,10 @@ where
 |;
 
     my $erg;
-#   my $fields = fields($obj->{dbh}, $sql);
+#   my $fields;
     my $sth = $obj->{dbh}->prepare($sql);
     if(!$sth->execute($recordid)
+#     || !($fields = $sth->{'NAME'})
       || !($erg = $sth->fetchrow_hashref())) {
         con_err($console,sprintf(gettext("Recording '%s' does not exist in the database!"),$recordid));
         return;
@@ -2130,10 +2131,11 @@ WHERE
 ORDER BY
     e.starttime asc
 |;
-    my $fields = fields($obj->{dbh}, $sql);
+
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute($lastReportTime)
         or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+    my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
     unshift(@$erg, $fields);
     return {
diff --git a/lib/XXV/MODULES/TIMERS.pm b/lib/XXV/MODULES/TIMERS.pm
index 119b636..5d25b14 100644
--- a/lib/XXV/MODULES/TIMERS.pm
+++ b/lib/XXV/MODULES/TIMERS.pm
@@ -482,6 +482,8 @@ sub saveTimer {
 
       event sprintf('Save timer "%s" with id: "%d"', $data->{file}, $pos || 0);
 
+      $obj->{changedTimer} = 1;
+
       return $erg;
   }
   return 0;
@@ -497,7 +499,7 @@ sub _saveTimer {
     $data->{flags} |= ($data->{vps} eq 'y' ? 4 : 0);
 
     $data->{file} =~ s/(\r|\n)//sg;
-    $data->{aux}  =~ s/(\r|\n)//sg;
+    $data->{aux}  =~ s/(\r|\n)//sg if(exists $data->{aux});
 
     my $file = $data->{file};
     $file =~ s/:/|/g;
@@ -838,6 +840,7 @@ sub editTimer {
                 ( $console->{USER} && $console->{USER}->{Name} ? sprintf(' from user: %s', $console->{USER}->{Name}) : "" )
                 );
                 $console->message($erg);
+
           } else {
             error sprintf('%s timer with title "%s" does\'nt saved : %s',
                 ($timerid ? 'Changed' : 'New'),
@@ -846,6 +849,8 @@ sub editTimer {
                 );
                 $console->err($erg);
           }
+          $obj->{changedTimer} = 1;
+
           if($obj->_readData($watcher,$console)) {
             $console->redirect({url => '?cmd=tlist', wait => 1})
               if(!$error && $console->typ eq 'HTML');
@@ -1142,12 +1147,13 @@ sub _readData {
     $obj->getTimersByAutotimer($aids);
 
     # Get new timers by User
-    if($oldTimers) {
+    if($oldTimers or exists $obj->{changedTimer}) {
         my $timers = $obj->getNewTimers($oldTimers);
         foreach my $timerdata (@$timers) {
             event sprintf('New timer "%s" with id: "%d"', $timerdata->{file}, $timerdata->{pos});
         }
-        $obj->updated() if(scalar @$timers);
+        $obj->updated() if(scalar @$timers or exists $obj->{changedTimer});
+        delete $obj->{changedTimer}  if(exists $obj->{changedTimer});
     }
 
     $obj->{REGISTER}++;
@@ -1258,11 +1264,10 @@ ORDER BY
     t.starttime
 |;
 
-    my $fields = fields($obj->{dbh}, $sql);
-
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute(@{$term})
       or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+    my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
     map {
         $_->[4] = datum($_->[4],'weekday');
diff --git a/lib/XXV/MODULES/USER.pm b/lib/XXV/MODULES/USER.pm
index 5314032..73b4eea 100644
--- a/lib/XXV/MODULES/USER.pm
+++ b/lib/XXV/MODULES/USER.pm
@@ -539,9 +539,12 @@ SELECT SQL_CACHE
 from 
   USER
     |;
-    my $fields = fields($obj->{dbh}, $sql);
 
-    my $erg = $obj->{dbh}->selectall_arrayref($sql);
+    my $sth = $obj->{dbh}->prepare($sql);
+    $sth->execute()
+        or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+    my $fields = $sth->{'NAME'};
+    my $erg = $sth->fetchall_arrayref();
     unshift(@$erg, $fields);
 
     $console->table($erg);