Stuff

#!/usr/bin/perl

use IO::Handle;
use DBI;
use strict;

my $DO_DELETE = 0;

main();

sub main {
        my $dbh;
        my $sth;

        STDOUT->autoflush(1);

        $dbh = DBI->connect("dbi:mysql:database=mythconverg:host=localhost", 'root', '')
          or die "Cannot connect to database: $!\n\n";

        print "Getting card information: ";

        $sth = $dbh->prepare("SELECT sourceid,cardtype,cardinput.cardid FROM capturecard,cardinput ".
                             "WHERE cardinput.cardid = capturecard.cardid");
        $sth->execute();

        my %cards;

        while (my $row = $sth->fetchrow_hashref()) {
                $cards{$row->{'sourceid'}}->{'cardid'} = $row->{'cardid'};
                $cards{$row->{'sourceid'}}->{'cardtype'} = $row->{'cardtype'};
        }

        $sth->finish();

        print "done.\n";

        print "Getting recordings information: ";

        $sth = $dbh->prepare("SELECT recorded.chanid,starttime,title,subtitle,sourceid, ".
                             "(unix_timestamp(endtime) - unix_timestamp(starttime))/60/60 as length ".
                             "FROM recorded LEFT JOIN channel ON recorded.chanid = channel.chanid");
        $sth->execute();

        my %recorded;

        while (my $row = $sth->fetchrow_hashref()) {
                $recorded{$row->{'chanid'}}->{$row->{'starttime'}}->{'title'} = $row->{'title'}.": ".$row->{'subtitle'};
                $recorded{$row->{'chanid'}}->{$row->{'starttime'}}->{'length'} = $row->{'length'};
                $recorded{$row->{'chanid'}}->{$row->{'starttime'}}->{'cardid'} = $cards{$row->{'sourceid'}}->{'cardid'};
                $recorded{$row->{'chanid'}}->{$row->{'starttime'}}->{'cardtype'} = $cards{$row->{'sourceid'}}->{'cardtype'};
        }

        $sth->finish();

        print "done.\n";
        print "Getting seek table: ";

        $sth = $dbh->prepare("SELECT chanid,starttime from recordedseek");
        $sth->execute();

        print "done.\n";

        my $rownum;
        my $repnum;

        while (my $row = $sth->fetchrow_hashref()) {
                my $chanid = $row->{'chanid'};
                my $starttime = $row->{'starttime'};

                if (!($rownum % 10000)) {
                        $repnum++;
                        print "Processed ".(10000*$repnum)." rows\n";
                }

                if (!defined($recorded{$chanid}->{$starttime})) {
                        my $sql = "DELETE from recordedseek WHERE chanid = $chanid AND starttime = '$starttime'";

                        print "--> $sql\n";

                        $dbh->do($sql) if ($DO_DELETE);
                } else {
                        $recorded{$chanid}->{$starttime}->{'entries'}++;
                }

                $rownum++;
        }

        my %sorted;

        $sth->finish();

        foreach my $chanid (keys %recorded) {
                foreach my $starttime (keys %{$recorded{$chanid}}) {
                        my $entries = $recorded{$chanid}->{$starttime}->{'entries'};
                        $sorted{$entries} = $recorded{$chanid}->{$starttime};
                }
        }

        foreach my $entries (sort numerically keys %sorted) {
                my $title = $sorted{$entries}->{'title'};
                my $length = $sorted{$entries}->{'length'};
                my $cardid = $sorted{$entries}->{'cardid'};
                my $cardtype = $sorted{$entries}->{'cardtype'};

                my $average = $entries / $length;

                print "$cardtype ($cardid) -> $title ->\t$entries ($average/hour average)\n";
        }

        print "Done!\n";
}

sub numerically {
        $a <=> $b;
}