[mythtv-users] Email notifier of recorded shows

[Adam Lewandowski]

Here's a perl script (requires DBD::mysql, available through CPAN) that 
makes it look a little better. Run it through cron and cron will send 
you the output as an email on it's own.
I looked in to pulling the scheduled recordings also but that 
information is not stored in the database, you need to communicate with 
the backend server over a socket to get it. Maybe some other day.

Josh Burks wrote:

> > mysql -u mythtv -pyourpw -e "select title, subtitle, description,
> > starttime, CONCAT(hour(endtime-starttime), ':',
> > minute(endtime-starttime)) length, channum, callsign from recorded r,
> > channel c where r.chanid = c.chanid AND DATE_SUB(CURDATE(),INTERVAL 24
> > HOUR) <= starttime;" mythconverg
>
> add this to the end of that command:
>
> | mail -s "Recorded by MythTV" your_email at your_domain.com
>
> It's raw data straight out of the db, oh and sendmail has to be running.
>
> Almost all you need in a one-liner.
>
> Josh Burks
>
>
>
> Adam Lewandowski wrote:
>
>> This isn't exactly what you're looking for, but here's a quick mysql 
>> query command that will return all of the shows recorded in the last 
>> 24 hours. It could be run as a cron job for a pretty crude report.
>> Obviously, use your own mythtv database user id and password.
>>
>> mysql -u mythtv -pyourpw -e "select title, subtitle, description, 
>> starttime, CONCAT(hour(endtime-starttime), ':', 
>> minute(endtime-starttime)) length, channum, callsign from recorded r,
>> channel c where r.chanid = c.chanid AND DATE_SUB(CURDATE(),INTERVAL 
>> 24 HOUR) <= starttime;" mythconverg
>>
>>
>>
>> Tim Ahpee wrote:
>>
>>> Hi All,
>>>
>>> I've had a look through the mailing list archives and also thrown a few
>>> queries at Google but haven't yeilded anything.
>>>
>>> What I'm after is a script that will send me a mail once a day with 
>>> what shows
>>> have been recorded, what are to be recoreded etc. Does one exist? If 
>>> not I
>>> might be off to write one.
>>>
>>> Thanks in advance,
>>>
>>> Tim
>>>  
>>>
>>> ------------------------------------------------------------------------ 
>>>
>>>
>>> _______________________________________________
>>> mythtv-users mailing list
>>> mythtv-users at mythtv.org
>>> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>>>  
>>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> mythtv-users mailing list
>> mythtv-users at mythtv.org
>> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>
>------------------------------------------------------------------------
>
>_______________________________________________
>mythtv-users mailing list
>mythtv-users at mythtv.org
>http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>  
>

-------------- next part --------------
#!/usr/bin/perl

use DBI();

my $dsn = "DBI:mysql:database=mythconverg";
my $user = "mythtv";
my $pw = "mythtv";
my $hours = 24;

my $dbh = DBI->connect($dsn,$user,$pw) || die;
print "Programs recorded in the last $hours hours:\n\n";

# Retreive recently recorded shows
my $sth = $dbh->prepare("select title, subtitle, description, starttime, CONCAT(hour(endtime-starttime), ':', LPAD(minute(endtime-starttime),2,'0')) length, channum, callsign from recorded r, channel c where r.chanid = c.chanid AND DATE_SUB(CURDATE(),INTERVAL $hours HOUR) <= starttime order by starttime asc;");
$sth->execute() || die $sth->errstr;
my $rowCount = 0;
while(my @row = $sth->fetchrow_array) {
 my ($title,$subtitle,$description,$starttime,$length,$channum,$callsign) = @row;
 ++$rowCount;
 $length = "Unknown" if !$length;

 my $titleStr = "$title";
 print "-------------------------------------------------------------------\n";
 $titleStr .= " - $subtitle" if $subtitle;
 print "$titleStr\n";
 print $callsign . "[$channum], Length $length, Start $starttime\n";
 print "$description\n" if $description;
}
if($rowCount > 0) {
 print "-------------------------------------------------------------------\n\n";
 print "$rowCount recorded programs\n";
} else {
 print "No programs have been recorded.\n";
}

$dbh->disconnect();