* debian-install.sh missed by pakaging

* report: RECORDS - status message with wrong id
* alist/clist/rlist: reorder sorting parameter, remove double sql query
* rlist: move time formating from template to perl code

4 files changed, 133 insertions, 71 deletions

diff --git a/lib/Tools.pm b/lib/Tools.pm
index de0c8d4..bcf5800 100644
--- a/lib/Tools.pm
+++ b/lib/Tools.pm
@@ -49,26 +49,29 @@ use constant WEEKDAYS => qw/Sun Mon Tue Wed Thu Fri Sat/;
 sub datum {
 # ------------------
     my $zeit = shift  || time;
-    my $typ  = shift  || 'voll';
+    my $typ  = lc(shift)  || 'voll';
 
-    if(lc($typ) eq 'voll') {
+    if($typ eq 'voll') {
         # full date depends locale e.g. 24.12.2007 12:00:00 or 12/24/2007 ...
         return strftime("%x %X", localtime($zeit)); 
-    } elsif(lc($typ) eq 'tag') {
+    } elsif($typ eq 'short') {
+        # short date depends locale e.g. 24.12.2007 12:00 or 12/24/2007 ...
+        return strftime("%x %H:%M", localtime($zeit)); 
+    } elsif($typ eq 'tag') {
         # day depends locale e.g. 24.12.2007 or 12/24/2007
         return strftime("%x", localtime($zeit));   
-    } elsif(lc($typ) eq 'weekday') {
+    } elsif($typ eq 'weekday') {
         # day depends locale e.g. Fryday ,24.12.2007
         return strftime("%A, %x", localtime($zeit));   
-    } elsif (lc($typ) eq 'int') {
+    } elsif ($typ eq 'int') {
         # 1901-01-01T00:00+00:00
         return strftime("%Y-%m-%dT%H:%M:%S%z", localtime($zeit));
-    } elsif (lc($typ) eq 'rss') {
+    } elsif ($typ eq 'rss') {
         # 23 Aug 1999 07:00:00 GMT
         my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime($zeit);
         return sprintf('%02d %s %04d %02d:%02d:%02d GMT',
             $mday, (MONTHS)[$mon], $year+1900, $hour, $min, $sec );
-    } elsif (lc($typ) eq 'header') {
+    } elsif ($typ eq 'header') {
         my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime($zeit);
         return sprintf("%s, %02d %s %04d %02d:%02d:%02d GMT",
             (WEEKDAYS)[$wday],$mday,(MONTHS)[$mon],$year + 1900,$hour,$min,$sec);
diff --git a/lib/XXV/MODULES/AUTOTIMER.pm b/lib/XXV/MODULES/AUTOTIMER.pm
index 709bb62..a74f487 100644
--- a/lib/XXV/MODULES/AUTOTIMER.pm
+++ b/lib/XXV/MODULES/AUTOTIMER.pm
@@ -1159,10 +1159,10 @@ sub list {
         'Activ' => gettext('Activ'),
         'Search' => gettext('Search'),
         'Channels' => gettext('Channels'),
-        'Start' => gettext('Start'),
-        'Stop' => gettext('Stop'),
+        'Start' => gettext('Start time'),
+        'Stop' => gettext('Stop time'),
         'Dir' => gettext('Directory'),
-        'Min' => gettext('Minimum length'),
+        'MinLength' => gettext('Minimum length'),
     );
 
     my $sql = qq|
@@ -1174,26 +1174,31 @@ sub list {
       Dir as \'$f{'Dir'}\',
       Start as \'$f{'Start'}\',
       Stop as \'$f{'Stop'}\',
-      MinLength as \'$f{'Min'}\'
+      MinLength as \'$f{'MinLength'}\'
     FROM
       AUTOTIMER
     $search
+    ORDER BY
     |;
 
-    my $fields = fields($obj->{dbh}, $sql);
-
-    my $sortby = gettext("Search");
-    $sortby = $params->{sortby}
-        if(exists $params->{sortby} && grep(/^$params->{sortby}$/i,@{$fields}));
-    $sql .= " order by $sortby";
-    if(exists $params->{desc} && $params->{desc} == 1) {
-        $sql .= " desc"; }
-    else {
-        $sql .= " asc"; }
+    my $sortby = "Search";
+    if(exists $params->{sortby}) {
+      while(my($k, $v) = each(%f)) {
+        if($params->{sortby} eq $k or $params->{sortby} eq $v) {
+          $sortby = $k;
+          last;
+        }
+      }
+    }
+    $sql .= $sortby;
+    $sql .= " desc"
+        if(exists $params->{desc} && $params->{desc} == 1);
 
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute(@{$term})
       or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
+
+    my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
     map {
         $_->[5] = fmttime($_->[5]);
diff --git a/lib/XXV/MODULES/CHANNELS.pm b/lib/XXV/MODULES/CHANNELS.pm
index c8771a1..39901a9 100644
--- a/lib/XXV/MODULES/CHANNELS.pm
+++ b/lib/XXV/MODULES/CHANNELS.pm
@@ -434,32 +434,70 @@ sub list {
     my $id      = shift || '';
     my $params = shift;
 
+    my %f = (
+        'Id' => gettext('Service'),
+        'Name' => gettext('Name'),
+        'Frequency' => gettext('Transponder frequency'),
+        'Parameters' => gettext('Parameters'),
+        'Source' => gettext('Signal source'),
+        'Srate' => gettext('Symbol rate'),
+        'VPID' => gettext('Video PID'),
+        'APID' => gettext('Audio PID'),
+        'TPID' => gettext('Teletext PID'),
+        'CA' => gettext('Conditional access'),
+        'SID' => gettext('SID'),
+        'NID' => gettext('NID'),
+        'RID' => gettext('RID'),
+        'GRP' => gettext('Channel group'),
+        'POS' => gettext('Position'),
+    );
+
     my $sql = qq|
 SELECT SQL_CACHE 
-    c.*, cg.Name as __GrpName
+    c.Id as \'$f{'Id'}\',
+    c.Name as \'$f{'Name'}\',
+    c.Frequency as \'$f{'Frequency'}\',
+    c.Parameters as \'$f{'Parameters'}\',
+    c.Source as \'$f{'Source'}\',
+    c.Srate as \'$f{'Srate'}\',
+    c.VPID as \'$f{'VPID'}\',
+    c.APID as \'$f{'APID'}\',
+    c.TPID as \'$f{'TPID'}\',
+    c.CA as \'$f{'CA'}\',
+    c.SID as \'$f{'SID'}\',
+    c.NID as \'$f{'NID'}\',
+    c.TID as \'$f{'TID'}\',
+    c.RID as \'$f{'RID'}\',
+    c.GRP as \'$f{'GRP'}\',
+    c.POS as \'$f{'POS'}\',
+    cg.Name as __GrpName
 from
     CHANNELS as c,
     CHANNELGROUPS as cg
-where
-    c.Name like ?
-    and
-    c.GRP = cg.Id
+WHERE
+    c.Name LIKE ?
+    AND c.GRP = cg.Id
+ORDER BY
 |;
 
-    my $fields = fields($obj->{dbh}, $sql);
-
     my $sortby = "POS";
-    $sortby = $params->{sortby}
-        if(exists $params->{sortby} && grep(/^$params->{sortby}$/i,@{$fields}));
-    $sql .= "order by $sortby";
+    if(exists $params->{sortby}) {
+      while(my($k, $v) = each(%f)) {
+        if($params->{sortby} eq $k or $params->{sortby} eq $v) {
+          $sortby = $k;
+          last;
+        }
+      }
+    }
+    $sql .= $sortby;
     $sql .= " desc"
         if(exists $params->{desc} && $params->{desc} == 1);
 
-
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute('%'.$id.'%')
         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);
     $console->table($erg,{sortable => 1 });
diff --git a/lib/XXV/MODULES/RECORDS.pm b/lib/XXV/MODULES/RECORDS.pm
index f207e3c..caaadc2 100644
--- a/lib/XXV/MODULES/RECORDS.pm
+++ b/lib/XXV/MODULES/RECORDS.pm
@@ -1498,6 +1498,7 @@ sub list {
 
     my $deep = 1;
     my $folder = scalar (my @a = split('/',$obj->{videodir})) + 1;
+    my $term;
 
     my $where = "e.eventid = r.eventid";
     if($text) {
@@ -1509,32 +1510,31 @@ sub list {
         $text =~ s/%/\\%/sg;
         $where .= qq|
 AND (
-      SUBSTRING_INDEX(CONCAT_WS('~',e.title,e.subtitle), '~', $deep) LIKE '$text'
+      SUBSTRING_INDEX(CONCAT_WS('~',e.title,e.subtitle), '~', $deep) LIKE ?
       OR
-      SUBSTRING_INDEX(CONCAT_WS('~',e.title,e.subtitle), '~', $deep) LIKE '$text~%'
+      SUBSTRING_INDEX(CONCAT_WS('~',e.title,e.subtitle), '~', $deep) LIKE ?
 )
 |;
-
+      push(@{$term},$text);
+      push(@{$term},$text . '~%');
     }
 
     my %f = (
-        'Id' => gettext('Service'),
+        'RecordMD5' => gettext('Index'),
         'Title' => gettext('Title'),
         'Subtitle' => gettext('Subtitle'),
-        'Duration' => gettext('Duration')
+        'Duration' => gettext('Duration'),
+        'starttime' => gettext('Start time')
     );
 
-    my $start = "e.starttime";
-    $start = "UNIX_TIMESTAMP(e.starttime)" if($console->typ eq "HTML");
-
     my $sql = qq|
 SELECT SQL_CACHE 
-    r.RecordMD5 as \'$f{'Id'}\',
+    r.RecordMD5 as \'$f{'RecordMD5'}\',
     r.eventid as __EventId,
     e.title as \'$f{'Title'}\',
     e.subtitle as \'$f{'Subtitle'}\',
     SUM(e.duration) as \'$f{'Duration'}\',
-    $start as __RecordStart,
+    UNIX_TIMESTAMP(e.starttime) as \'$f{'starttime'}\',
     SUM(State) as __New,
     r.Type as __Type,
     COUNT(*) as __Group,
@@ -1548,23 +1548,32 @@ WHERE
     $where 
 GROUP BY
     SUBSTRING_INDEX(r.Path, '/', IF(Length(e.subtitle)<=0, $folder + 1, $folder))
+ORDER BY __IsRecording asc, 
 |;
 
-    my $fields = fields($obj->{dbh}, $sql);
 
-    my $sortby = "__fulltitle";
-    $sortby = '__RecordStart'
-        if($text);
+    my $sortby = $text ? "starttime" : "__fulltitle";
+    if(exists $params->{sortby}) {
+      while(my($k, $v) = each(%f)) {
+        if($params->{sortby} eq $k or $params->{sortby} eq $v) {
+          $sortby = $k;
+          last;
+        }
+      }
+    }
+    $sql .= $sortby;
+    $sql .= " desc"
+        if(exists $params->{desc} && $params->{desc} == 1);
 
-    $sortby = $params->{sortby}
-        if(exists $params->{sortby} && grep(/^$params->{sortby}$/i,@{$fields}));
-    $sql .= "order by __IsRecording asc, $sortby";
-    if(exists $params->{desc} && $params->{desc} == 1) {
-        $sql .= " desc"; }
-    else {
-        $sql .= " asc"; }
+    my $sth = $obj->{dbh}->prepare($sql);
+    $sth->execute(@{$term})
+      or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
 
-    my $erg = $obj->{dbh}->selectall_arrayref($sql);
+    my $fields = $sth->{'NAME'};
+    my $erg = $sth->fetchall_arrayref();
+    map {
+        $_->[5] = datum($_->[5],'short');
+    } @$erg;
     unshift(@$erg, $fields);
 
     my $param = {
@@ -1593,23 +1602,21 @@ sub search {
     my $term = $query->{term};
 
     my %f = (
-        'Id' => gettext('Service'),
+        'RecordMD5' => gettext('Index'),
         'Title' => gettext('Title'),
         'Subtitle' => gettext('Subtitle'),
-        'Duration' => gettext('Duration')
+        'Duration' => gettext('Duration'),
+        'starttime' => gettext('Start time')
     );
 
-    my $start = "e.starttime";
-    $start = "UNIX_TIMESTAMP(e.starttime)" if($console->typ eq "HTML");
-
     my $sql = qq|
 SELECT SQL_CACHE 
-    r.RecordMD5 as \'$f{'Id'}\',
+    r.RecordMD5 as \'$f{'RecordMD5'}\',
     r.eventid as __EventId,
     e.title as \'$f{'Title'}\',
     e.subtitle as \'$f{'Subtitle'}\',
     e.duration as \'$f{'Duration'}\',
-    $start as __RecordStart ,
+    UNIX_TIMESTAMP(e.starttime) as \'$f{'starttime'}\',
     r.State as __New,
     r.Type as __Type,
     0 as __Group,
@@ -1622,23 +1629,32 @@ FROM
 WHERE
     e.eventid = r.eventid
 	AND ( $search )
+ORDER BY 
 |;
 
-    my $fields = fields($obj->{dbh}, $sql);
+    my $sortby = "starttime";
+    if(exists $params->{sortby}) {
+      while(my($k, $v) = each(%f)) {
+        if($params->{sortby} eq $k or $params->{sortby} eq $v) {
+          $sortby = $k;
+          last;
+        }
+      }
+    }
+    $sql .= $sortby;
+    $sql .= " desc"
+        if(exists $params->{desc} && $params->{desc} == 1);
 
-    my $sortby = "e.starttime";
-    $sortby = $params->{sortby}
-        if(exists $params->{sortby} && grep(/^$params->{sortby}$/i,@{$fields}));
-    $sql .= "order by $sortby";
-    if(exists $params->{desc} && $params->{desc} == 1) {
-        $sql .= " desc"; }
-    else {
-        $sql .= " asc"; }
 
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute(@{$term})
       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],'short');
+    } @$erg;
     unshift(@$erg, $fields);
 
     my $param = {
@@ -2116,7 +2132,7 @@ sub status {
 
     my $sql = qq|
 SELECT SQL_CACHE 
-    r.RecordId as __Id,
+    r.RecordMD5 as __Id,
     r.eventid as __EventId,
     e.title,
     e.subtitle,