* AJAX: Reduce transmitted parameter

* Optimize SQL Query with ROW Limit
* Request[#13339] Show channels if search query empty

6 files changed, 243 insertions, 193 deletions

diff --git a/lib/XXV/MODULES/AUTOTIMER.pm b/lib/XXV/MODULES/AUTOTIMER.pm
index e734529..6e22611 100644
--- a/lib/XXV/MODULES/AUTOTIMER.pm
+++ b/lib/XXV/MODULES/AUTOTIMER.pm
@@ -1195,24 +1195,33 @@ sub list {
         if(exists $params->{desc} && $params->{desc} == 1);
 
     my $rows;
-    if($console->{cgi} && $console->{cgi}->param('limit')) {
+    my $sth;
+    my $limit = CORE::int($console->{cgi} ? $console->{cgi}->param('limit') : 0);
+    if($limit > 0) {
+      # Query total count of rows
       my $rsth = $obj->{dbh}->prepare($sql);
-        $rsth->execute(@{$term})
+         $rsth->execute(@{$term})
           or return error sprintf("Couldn't execute query: %s.",$rsth->errstr);
       $rows = $rsth->rows;
-
-      if($console->{cgi}->param('start')) {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
-        $sql .= "," . CORE::int($console->{cgi}->param('limit'));
+      if($rows <= $limit) {
+        $sth = $rsth;
       } else {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('limit'));
+        # Add limit query
+        if($console->{cgi}->param('start')) {
+          $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
+          $sql .= "," . $limit;
+        } else {
+          $sql .= " LIMIT " . $limit;
+        }
       }
     }
 
-    my $sth = $obj->{dbh}->prepare($sql);
-    $sth->execute(@{$term})
-      or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
-    $rows = $sth->rows unless($rows);
+    unless($sth) {
+      $sth = $obj->{dbh}->prepare($sql);
+      $sth->execute(@{$term})
+        or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+      $rows = $sth->rows unless($rows);
+    }
 
     my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
@@ -1225,17 +1234,15 @@ sub list {
       unshift(@$erg, $fields);
     }
 
-    my $channels = main::getModule('CHANNELS')->ChannelHash('Id');
-    my $timers = main::getModule('TIMERS')->getTimersByAutotimer();
-
-    $console->table($erg,
-        {
-            sortable => 1,
-            channels => $channels,
-            timers => $timers,
-            rows => $rows
-        }
-    );
+    my $info = {
+      rows => $rows
+    };
+    if($console->typ eq 'HTML') {
+      $info->{sortable} = '1';
+      $info->{channels} = main::getModule('CHANNELS')->ChannelHash('Id');
+      $info->{timers} = main::getModule('TIMERS')->getTimersByAutotimer();
+    }
+    $console->table($erg, $info );
 }
 
 
diff --git a/lib/XXV/MODULES/CONFIG.pm b/lib/XXV/MODULES/CONFIG.pm
index 99e9c66..e2fb1fd 100644
--- a/lib/XXV/MODULES/CONFIG.pm
+++ b/lib/XXV/MODULES/CONFIG.pm
@@ -338,15 +338,15 @@ sub usage {
             ) if(! $modCfg->{Commands}->{$cmdName}->{hidden} and ($u->{active} ne 'y') || $u->allowCommand($modCfg, $cmdName, $user, "1"));
         }
     }
-
-    $console->table(
-        $ret,
-        {
-            periods  => $mods->{'XXV::MODULES::EPG'}->{periods},
-            CHANNELS => $mods->{'XXV::MODULES::CHANNELS'}->ChannelArray('Name'),
-            CONFIGS  => [ sort @realModName ],
-        },
-    );
+    my $info = {
+      rows => scalar @$ret
+    };
+    if($console->typ eq 'HTML') {
+      $info->{periods} = $mods->{'XXV::MODULES::EPG'}->{periods};
+      $info->{CHANNELS} =  $mods->{'XXV::MODULES::CHANNELS'}->ChannelArray('Name');
+      $info->{CONFIGS} = [ sort @realModName ];
+    }
+    $console->table( $ret, $info );
 }
 
 1;
diff --git a/lib/XXV/MODULES/EPG.pm b/lib/XXV/MODULES/EPG.pm
index 56c81d8..edbbf81 100644
--- a/lib/XXV/MODULES/EPG.pm
+++ b/lib/XXV/MODULES/EPG.pm
@@ -602,122 +602,129 @@ sub search {
     }
 
     my $erg = [];
+    my $rows = 0;
 
-    unless($search) {
-      $console->err(gettext("There none text to search defined!"));
-      return 0;
-    }
-
-    # Channelsearch
-    if($params->{channel}) {
-        $search->{query} .= ' AND c.POS = ?';
-        push(@{$search->{term}},$params->{channel});
-    }
-
-    # Videoformat search
-    if($params->{Videoformat} && $params->{Videoformat} eq 'widescreen') {
-        $search->{query} .= ' AND e.video like "%%16:9%%"';
-    }
+    if($search) {
 
-    # Audioformat search
-    # XXX: Leider kann man an den Audioeintrag nicht richtig erkennnen
-    # hab erst zu spät erkannt das diese Info aus dem tvm2vdr kommen ;(
-#    if($params->{Audioformat} eq 'dts') {
-#        $search->{query} .= ' AND e.audio like "%%Digital%%"';
-#    }
-
-    # MinLength search
-    if($params->{MinLength}) {
-        $search->{query} .= ' AND e.duration >= ?';
-        push(@{$search->{term}},($params->{MinLength}*60));
-    }
+      # Channelsearch
+      if($params->{channel}) {
+          $search->{query} .= ' AND c.POS = ?';
+          push(@{$search->{term}},$params->{channel});
+      }
 
-    my %f = (
-        'id' => gettext('Service'),
-        'title' => gettext('Title'),
-        'channel' => gettext('Channel'),
-        'start' => gettext('Start'),
-        'stop' => gettext('Stop'),
-        'day' => gettext('Day')
-    );
+      # Videoformat search
+      if($params->{Videoformat} && $params->{Videoformat} eq 'widescreen') {
+          $search->{query} .= ' AND e.video like "%%16:9%%"';
+      }
 
-    my $sql = qq|
-    SELECT SQL_CACHE 
-        e.eventid as \'$f{'id'}\',
-        e.title as \'$f{'title'}\',
-        e.subtitle as __Subtitle,
-        c.Name as \'$f{'channel'}\',
-        c.POS as __Pos,
-        DATE_FORMAT(e.starttime, '%H:%i') as \'$f{'start'}\',
-        DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(e.starttime) + e.duration), '%H:%i') as \'$f{'stop'}\',
-        UNIX_TIMESTAMP(e.starttime) as \'$f{'day'}\',
-        e.description as __description,
-        IF(e.vpstime!=0,DATE_FORMAT(e.vpstime, '%H:%i'),'') as __PDC,
-        ( SELECT 
-            t.id
-            FROM TIMERS as t
-            WHERE t.eventid = e.eventid
-            LIMIT 1) as __timerid,
-        ( SELECT 
-            (t.flags & 1) 
-            FROM TIMERS as t
-            WHERE t.eventid = e.eventid
-            LIMIT 1) as __timeractiv,
-        ( SELECT 
-            NOW() between t.starttime and t.stoptime AND (t.flags & 1) 
-            FROM TIMERS as t
-            WHERE t.eventid = e.eventid
-            LIMIT 1) as __running,
-        e.video as __video,
-        e.audio as __audio
-    from
-        EPG as e,
-        CHANNELS as c
-    where
-        e.channel_id = c.Id
-        AND ( $search->{query} )
-        AND ((UNIX_TIMESTAMP(e.starttime) + e.duration) > UNIX_TIMESTAMP())
-    order by
-        starttime
-        |;
+      # Audioformat search
+      # XXX: Leider kann man an den Audioeintrag nicht richtig erkennnen
+      # hab erst zu spät erkannt das diese Info aus dem tvm2vdr kommen ;(
+  #    if($params->{Audioformat} eq 'dts') {
+  #        $search->{query} .= ' AND e.audio like "%%Digital%%"';
+  #    }
+
+      # MinLength search
+      if($params->{MinLength}) {
+          $search->{query} .= ' AND e.duration >= ?';
+          push(@{$search->{term}},($params->{MinLength}*60));
+      }
 
-    my $rows;
-    if($console->{cgi} && $console->{cgi}->param('limit')) {
-      # Query total count of rows
-      my $rsth = $obj->{dbh}->prepare($sql);
-        $rsth->execute(@{$search->{term}})
-          or return error sprintf("Couldn't execute query: %s.",$rsth->errstr);
-      $rows = $rsth->rows;
+      my %f = (
+          'id' => gettext('Service'),
+          'title' => gettext('Title'),
+          'channel' => gettext('Channel'),
+          'start' => gettext('Start'),
+          'stop' => gettext('Stop'),
+          'day' => gettext('Day')
+      );
+
+      my $sql = qq|
+      SELECT SQL_CACHE 
+          e.eventid as \'$f{'id'}\',
+          e.title as \'$f{'title'}\',
+          e.subtitle as __Subtitle,
+          c.Name as \'$f{'channel'}\',
+          c.POS as __Pos,
+          DATE_FORMAT(e.starttime, '%H:%i') as \'$f{'start'}\',
+          DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(e.starttime) + e.duration), '%H:%i') as \'$f{'stop'}\',
+          UNIX_TIMESTAMP(e.starttime) as \'$f{'day'}\',
+          e.description as __description,
+          IF(e.vpstime!=0,DATE_FORMAT(e.vpstime, '%H:%i'),'') as __PDC,
+          ( SELECT 
+              t.id
+              FROM TIMERS as t
+              WHERE t.eventid = e.eventid
+              LIMIT 1) as __timerid,
+          ( SELECT 
+              (t.flags & 1) 
+              FROM TIMERS as t
+              WHERE t.eventid = e.eventid
+              LIMIT 1) as __timeractiv,
+          ( SELECT 
+              NOW() between t.starttime and t.stoptime AND (t.flags & 1) 
+              FROM TIMERS as t
+              WHERE t.eventid = e.eventid
+              LIMIT 1) as __running,
+          e.video as __video,
+          e.audio as __audio
+      from
+          EPG as e,
+          CHANNELS as c
+      where
+          e.channel_id = c.Id
+          AND ( $search->{query} )
+          AND ((UNIX_TIMESTAMP(e.starttime) + e.duration) > UNIX_TIMESTAMP())
+      order by
+          starttime
+          |;
+
+      my $sth;
+      my $limit = CORE::int($console->{cgi} ? $console->{cgi}->param('limit') : 0);
+      if($limit > 0) {
+        # Query total count of rows
+        my $rsth = $obj->{dbh}->prepare($sql);
+           $rsth->execute(@{$search->{term}})
+            or return error sprintf("Couldn't execute query: %s.",$rsth->errstr);
+        $rows = $rsth->rows;
+        if($rows <= $limit) {
+          $sth = $rsth;
+        } else {
+          # Add limit query
+          if($console->{cgi}->param('start')) {
+            $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
+            $sql .= "," . $limit;
+          } else {
+            $sql .= " LIMIT " . $limit;
+          }
+        }
+      }
 
-      # Add limit query
-      if($console->{cgi}->param('start')) {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
-        $sql .= "," . CORE::int($console->{cgi}->param('limit'));
-      } else {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('limit'));
+      unless($sth) {
+        $sth = $obj->{dbh}->prepare($sql);
+        $sth->execute(@{$search->{term}})
+          or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+        $rows = $sth->rows unless($rows);
       }
-    }
 
-    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();
+      my $fields = $sth->{'NAME'};
+      $erg = $sth->fetchall_arrayref();
 
-    unless($console->typ eq 'AJAX') {
-      map {
-          $_->[7] = datum($_->[7],'weekday');
-      } @$erg;
+      unless($console->typ eq 'AJAX') {
+        map {
+            $_->[7] = datum($_->[7],'weekday');
+        } @$erg;
 
-      unshift(@$erg, $fields);
+        unshift(@$erg, $fields);
+      }
     }
-
-    my $modC = main::getModule('CHANNELS');
-    $console->table($erg,  {
-                            channels => $modC->ChannelWithGroup('Name,Pos'),
-                            rows => $rows
-                           }
-    );
+    my $info = {
+      rows => $rows
+    };
+    if($console->typ eq 'HTML') {
+      $info->{channels} = main::getModule('CHANNELS')->ChannelWithGroup('Name,POS');
+    }
+    $console->table($erg, $info );
 }
 
 # ------------------
@@ -786,25 +793,34 @@ order by
 |;
 
     my $rows;
-    if($console->{cgi} && $console->{cgi}->param('limit')) {
+    my $sth;
+    my $limit = CORE::int($console->{cgi} ? $console->{cgi}->param('limit') : 0);
+    if($limit > 0) {
       # Query total count of rows
       my $rsth = $obj->{dbh}->prepare($sql);
-        $rsth->execute($cid)
+         $rsth->execute($cid)
           or return error sprintf("Couldn't execute query: %s.",$rsth->errstr);
       $rows = $rsth->rows;
-
-      # Add limit query
-      if($console->{cgi}->param('start')) {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
-        $sql .= "," . CORE::int($console->{cgi}->param('limit'));
+      if($rows <= $limit) {
+        $sth = $rsth;
       } else {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('limit'));
+        # Add limit query
+        if($console->{cgi}->param('start')) {
+          $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
+          $sql .= "," . $limit;
+        } else {
+          $sql .= " LIMIT " . $limit;
+        }
       }
     }
 
-    my $sth = $obj->{dbh}->prepare($sql);
-    $sth->execute($cid)
-        or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
+    unless($sth) {
+      $sth = $obj->{dbh}->prepare($sql);
+      $sth->execute($cid)
+        or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+      $rows = $sth->rows unless($rows);
+    }
+
     my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
 
@@ -816,12 +832,14 @@ order by
       unshift(@$erg, $fields);
     }
 
-    $console->table($erg, {
-                            channels => $mod->ChannelWithGroup('Name,POS'),
-                            current => $mod->ChannelToPos($cid),
-                            rows => $rows
-                          }
-                    );
+    my $info = {
+      rows => $rows
+    };
+    if($console->typ eq 'HTML') {
+      $info->{channels} = $mod->ChannelWithGroup('Name,POS');
+      $info->{current} = $mod->ChannelToPos($cid);
+    }
+    $console->table($erg, $info );
 }
 
 # ------------------
diff --git a/lib/XXV/MODULES/HTTPD.pm b/lib/XXV/MODULES/HTTPD.pm
index e6af6e1..0667413 100644
--- a/lib/XXV/MODULES/HTTPD.pm
+++ b/lib/XXV/MODULES/HTTPD.pm
@@ -84,7 +84,7 @@ sub module {
                 default     => 'default',
                 type        => 'list',
                 required    => gettext('This is required!'),
-                choices     => sub{ return $self->findskins },
+                choices     => sub{ return $self->findskins(); },
             },
             StartPage => {
                 description => gettext('Startup screen'),
@@ -531,14 +531,14 @@ sub findskins
 # ------------------
 {
     my $self = shift || return error('No object defined!');
-    my $found;
+    my @skins;
     find({ wanted => sub{
               if(-d $File::Find::name
                     and ( -e $File::Find::name.'/index.tmpl'
                       or  -e $File::Find::name.'/index.html')
               ) {
                     my $l = basename($File::Find::name);
-                    push(@{$found},[$l,$l]);
+                    push(@skins,[$l,$l]);
                 }
            },
            follow => 1,
@@ -546,9 +546,10 @@ sub findskins
         },
         $self->{paths}->{HTMLDIR}
     );
-    error "Couldn't find useful HTML Skin at : $self->{paths}->{HTMLDIR}"
-        if(scalar $found == 0);
-    return sort { lc($a->[0]) cmp lc($b->[0]) } @{$found};
+    error "Couldn't find useable HTML Skin at : $self->{paths}->{HTMLDIR}"
+        unless(scalar @skins);
+    @skins = sort { lc($a->[0]) cmp lc($b->[0]) } @skins;
+    return \@skins;
 }
 
 # ------ unzip ------------
diff --git a/lib/XXV/MODULES/RECORDS.pm b/lib/XXV/MODULES/RECORDS.pm
index 569bedf..152025c 100644
--- a/lib/XXV/MODULES/RECORDS.pm
+++ b/lib/XXV/MODULES/RECORDS.pm
@@ -1610,26 +1610,35 @@ ORDER BY __IsRecording asc,
     $sql .= " desc"
         if(exists $params->{desc} && $params->{desc} == 1);
 
+
     my $rows;
-    if($console->{cgi} && $console->{cgi}->param('limit')) {
+    my $sth;
+    my $limit = CORE::int($console->{cgi} ? $console->{cgi}->param('limit') : 0);
+    if($limit > 0) {
       # Query total count of rows
       my $rsth = $obj->{dbh}->prepare($sql);
-        $rsth->execute(@{$term})
+         $rsth->execute(@{$term})
           or return error sprintf("Couldn't execute query: %s.",$rsth->errstr);
       $rows = $rsth->rows;
-
-      # Add limit query
-      if($console->{cgi}->param('start')) {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
-        $sql .= "," . CORE::int($console->{cgi}->param('limit'));
+      if($rows <= $limit) {
+        $sth = $rsth;
       } else {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('limit'));
+        # Add limit query
+        if($console->{cgi}->param('start')) {
+          $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
+          $sql .= "," . $limit;
+        } else {
+          $sql .= " LIMIT " . $limit;
+        }
       }
     }
 
-    my $sth = $obj->{dbh}->prepare($sql);
-    $sth->execute(@{$term})
-      or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
+    unless($sth) {
+      $sth = $obj->{dbh}->prepare($sql);
+      $sth->execute(@{$term})
+        or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+      $rows = $sth->rows unless($rows);
+    }
 
     my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
@@ -1712,25 +1721,33 @@ ORDER BY
         if(exists $params->{desc} && $params->{desc} == 1);
 
     my $rows;
-    if($console->{cgi} && $console->{cgi}->param('limit')) {
+    my $sth;
+    my $limit = CORE::int($console->{cgi} ? $console->{cgi}->param('limit') : 0);
+    if($limit > 0) {
       # Query total count of rows
       my $rsth = $obj->{dbh}->prepare($sql);
-        $rsth->execute(@{$term})
+         $rsth->execute(@{$term})
           or return error sprintf("Couldn't execute query: %s.",$rsth->errstr);
       $rows = $rsth->rows;
-
-      # Add limit query
-      if($console->{cgi}->param('start')) {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
-        $sql .= "," . CORE::int($console->{cgi}->param('limit'));
+      if($rows <= $limit) {
+        $sth = $rsth;
       } else {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('limit'));
+        # Add limit query
+        if($console->{cgi}->param('start')) {
+          $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
+          $sql .= "," . $limit;
+        } else {
+          $sql .= " LIMIT " . $limit;
+        }
       }
     }
 
-    my $sth = $obj->{dbh}->prepare($sql);
-    $sth->execute(@{$term})
-      or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
+    unless($sth) {
+      $sth = $obj->{dbh}->prepare($sql);
+      $sth->execute(@{$term})
+        or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+      $rows = $sth->rows unless($rows);
+    }
 
     my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();
diff --git a/lib/XXV/MODULES/TIMERS.pm b/lib/XXV/MODULES/TIMERS.pm
index a39c597..7d8b224 100644
--- a/lib/XXV/MODULES/TIMERS.pm
+++ b/lib/XXV/MODULES/TIMERS.pm
@@ -1268,26 +1268,33 @@ ORDER BY
 |;
 
     my $rows;
-    if($console->{cgi} && $console->{cgi}->param('limit')) {
+    my $sth;
+    my $limit = CORE::int($console->{cgi} ? $console->{cgi}->param('limit') : 0);
+    if($limit > 0) {
       # Query total count of rows
       my $rsth = $obj->{dbh}->prepare($sql);
-        $rsth->execute(@{$term})
+         $rsth->execute(@{$term})
           or return error sprintf("Couldn't execute query: %s.",$rsth->errstr);
       $rows = $rsth->rows;
-
-      # Add limit query
-      if($console->{cgi}->param('start')) {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
-        $sql .= "," . CORE::int($console->{cgi}->param('limit'));
+      if($rows <= $limit) {
+        $sth = $rsth;
       } else {
-        $sql .= " LIMIT " . CORE::int($console->{cgi}->param('limit'));
+        # Add limit query
+        if($console->{cgi}->param('start')) {
+          $sql .= " LIMIT " . CORE::int($console->{cgi}->param('start'));
+          $sql .= "," . $limit;
+        } else {
+          $sql .= " LIMIT " . $limit;
+        }
       }
     }
 
-    my $sth = $obj->{dbh}->prepare($sql);
-    $sth->execute(@{$term})
-      or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
-    $rows = $sth->rows unless($rows);
+    unless($sth) {
+      $sth = $obj->{dbh}->prepare($sql);
+      $sth->execute(@{$term})
+        or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+      $rows = $sth->rows unless($rows);
+    }
 
     my $fields = $sth->{'NAME'};
     my $erg = $sth->fetchall_arrayref();