* SHARE: rewrite - holding data now into database

2 files changed, 128 insertions, 58 deletions

diff --git a/lib/XXV/MODULES/EPG.pm b/lib/XXV/MODULES/EPG.pm
index a827dae..2206370 100644
--- a/lib/XXV/MODULES/EPG.pm
+++ b/lib/XXV/MODULES/EPG.pm
@@ -665,7 +665,9 @@ sub search {
             NOW() between t.starttime and t.stoptime AND (t.flags & 1) 
             FROM TIMERS as t
             WHERE t.eventid = e.eventid
-            LIMIT 1) as __running
+            LIMIT 1) as __running,
+        e.video as __video,
+        e.audio as __audio
     from
         EPG as e,
         CHANNELS as c
diff --git a/lib/XXV/MODULES/SHARE.pm b/lib/XXV/MODULES/SHARE.pm
index ee2a098..f69afd4 100644
--- a/lib/XXV/MODULES/SHARE.pm
+++ b/lib/XXV/MODULES/SHARE.pm
@@ -18,9 +18,10 @@ sub AUTOLOAD {
 
     # Den Hash per Hand nachpflegen
     # bis zum nächsten Refresh ...
-    if($cmd eq 'setEventLevel' and exists $obj->{EventLevels} and ref $obj->{EventLevels} eq 'HASH') {
-        $obj->{EventLevels}->{$_[0]}->{Level} = $_[1];
+    if($cmd eq 'setEventLevel') {
+        $obj->StoreEventLevel($_[0],$_[1]);
         $_[2] += $obj->{TimeOffset} if(exists $obj->{TimeOffset});
+return;
     }
 
     if($obj->{SOAP} && $obj->{active} eq 'y') {
@@ -129,6 +130,26 @@ sub _init {
     $obj->{SessionId} = $obj->generateUniqueId
         unless($obj->{SessionId});
 
+    my $version = 27; # Must be increment if rows of table changed
+    # this tables hasen't handmade user data,
+    # therefore old table could dropped if updated rows
+
+    # remove old table, if updated version
+    if(!tableUpdated($obj->{dbh},'SHARE',$version,1)) {
+      return 0;
+    }
+
+    $obj->{dbh}->do(qq|
+        CREATE TABLE IF NOT EXISTS SHARE (
+            eventid int unsigned default '0',
+            level float,
+            quantity int unsigned default '0',
+            rank float,
+            addtime datetime NOT NULL default '0000-00-00 00:00:00',
+            PRIMARY KEY (eventid)
+          ) COMMENT = '$version'
+      |);
+
     main::after(sub{
 
         $obj->{SOAP} = $obj->ConnectToService($obj->{SessionId},$obj->{rating});
@@ -171,33 +192,23 @@ sub getSoapData {
 # ------------------
     my $obj = shift  || return error('No object defined!');
     return unless($obj->{SOAP} and $obj->{active} eq 'y');
-    lg 'Start interval to get popularity levels!';
-    my $levels = $obj->getEventLevels();
-    my $eventlevels;
-    foreach my $event (@$levels) {
-      my $id = $event->{e}; # eventid
-      $eventlevels->{$id} = {
-        'Eventid' => $id,
-        'Level' => $event->{l} #level
-      }
-    }
-    $obj->{EventLevels} = $eventlevels;
-#dumper($eventlevels);
 
     lg 'Start interval to get popularity top ten events!';
     my $topevents = $obj->getTopTen(1000);
-    my $topten;
-    foreach my $top (@$topevents) {
-      push(@$topten, [
-        $top->{e}, # eventid
-        $top->{l}, # level
-        $top->{c}, # count
-        $top->{r}  # rank
-        ]
+    my $time = time;
+    foreach my $t (@$topevents) {
+      my $sth = $obj->{dbh}->prepare('REPLACE INTO SHARE(eventid, level, quantity, rank, addtime) VALUES (?,?,?,?,FROM_UNIXTIME(?))');
+      $sth->execute(
+        $t->{e}, # eventid
+        $t->{l}, # level
+        $t->{c}, # count
+        $t->{r}, # rank
+        $time
       );
     }
-#dumper($topten);
-    $obj->{TopTen} = $topten;
+
+    my $dsth = $obj->{dbh}->prepare('DELETE FROM SHARE WHERE addtime != FROM_UNIXTIME(?)');
+    $dsth->execute($time);
 }
 
 
@@ -261,10 +272,32 @@ sub getEventLevel {
     my $obj = shift  || return error('No object defined!');
     my $eid = shift  || return;
 
-    return unless($obj->{EventLevels});
+    my $sql = qq|
+SELECT SQL_CACHE 
+    level from SHARE
+where
+    eventid = ?
+|;
+
+    my $sth = $obj->{dbh}->prepare($sql);
+    $sth->execute($eid)
+        or return error(sprintf("Event '%s' does not exist in the database!",$eid));
+    my $erg = $sth->fetchrow_hashref();
+    return $erg ? $erg->{level} : 0;
+}
 
-    return $obj->{EventLevels}->{$eid}->{Level}
-        if(exists $obj->{EventLevels}->{$eid});
+# ------------------
+sub StoreEventLevel {
+# ------------------
+    my $obj = shift  || return error('No object defined!');
+    my $eid = shift  || return;
+    my $level = shift  || return;
+
+    my $sth = $obj->{dbh}->prepare('REPLACE INTO SHARE(eventid, level, quantity, rank, addtime) VALUES (?,?,1,1,NOW())');
+    $sth->execute(
+      $eid, # eventid
+      $level # level
+    );
 }
 
 # ------------------
@@ -275,37 +308,72 @@ sub TopTen {
     my $console = shift || return error('No console defined!');
     my $anzahl = shift || 10;
 
-    $obj->getSoapData()
-      unless($obj->{TopTen});
-
-    my $data = $obj->{TopTen};
-
-    my $epg = main::getModule('EPG');
-    my $tim = main::getModule('TIMERS');
-    my $can = main::getModule('CHANNELS');
-
-    my @fields = ('eventid','title','subtitle','description','channel_id','starttime','video','audio');
-    my @query = @fields;
-
-       @query = ('eventid','title','subtitle','description','channel_id','UNIX_TIMESTAMP(starttime) as starttime','video','audio')
-          if($console->typ eq 'HTML');
-
-
-    my $out = [];
-    foreach my $entry (@$data) {
-            my $edata = $epg->getId( $entry->[0], join(", ", @query) );
-            next unless(keys %$edata);
-            push(@$out, [ @fields, 'Rank', '__Level', '__Count' ])
-                unless(scalar @$out);
-            my @val = map { $edata->{$_} } @fields;
-            push(@$out, [ @val, $entry->[3], $entry->[1],$entry->[2] ]);
-            last if(scalar @$out > $anzahl);
-        }
+    my %f = (
+        'id' => gettext('Service'),
+        'title' => gettext('Title'),
+        'channel' => gettext('Channel'),
+        'start' => gettext('Start'),
+        'stop' => gettext('Stop'),
+        'day' => gettext('Day'),
+        'rank' => gettext('Rank')
+    );
+
+    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,
+        ROUND(s.rank,2) as \'$f{'rank'}\',
+        s.level as __level,
+        s.quantity as __quantity
+    from
+        EPG as e,
+        CHANNELS as c,
+        SHARE as s
+    where
+        e.eventid = s.eventid
+        AND e.channel_id = c.Id
+        AND ((UNIX_TIMESTAMP(e.starttime) + e.duration) > UNIX_TIMESTAMP())
+    order by
+        rank desc
+    LIMIT ?
+        |;
+
+    my $sth = $obj->{dbh}->prepare($sql);
+    $sth->execute($anzahl)
+        or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
+    my $fields = $sth->{'NAME'};
+    my $erg = $sth->fetchall_arrayref();
+    map {
+        $_->[7] = datum($_->[7],'weekday');
+    } @$erg;
+    unshift(@$erg, $fields);
 
-    return $console->table($out, {
-        channels => $can->ChannelHash('Id'),
-        timers => $tim->getEvents()
-       });
+    return $console->table($erg);
 }
 
 # ------------------