Tvheadend

#!/usr/bin/env python

# -*- coding: iso-8859-1 -*-

# General Imorts

import htsp

import json

from time import *

import time

import sqlite3

import sys

import os.path

import shutil

# Imports for mail transfer (considering UTF8 and German Umlaute)

import smtplib

from cStringIO import StringIO

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from email.header import Header

from email import Charset

from email.generator import Generator

# Unicode Workaround (Umlaute etc.)

import sys

reload(sys)

sys.setdefaultencoding("utf-8")

#########################

# Configuration

#########################

# TVheadend configuration

tvheadend_username = "XXX"

tvheadend_password = "XXX"

tvheadend_server = ("10.0.0.10",9982)

movielist_filename = "movie_liste.txt"

# Mail configuration

from_address = ['RaspVDR', '[email protected]']

recipient = ['My name', '[email protected]']

subject = "[RaspVDR] Neue Film-Treffer"

smtpserver = 'smtp.example.com'

smtp_username = "raspi"

smtp_password = "password"

# SQLite DB Filenames

db_filename = "epgEventsMatched.db"

db_backup_filename = "epgEventsMatched.backup.db"

db_swap_filename = "epgEventsMatched.swap.db"

########################

# TVheadend connection

########################

def connect_tvheadend_server(username, password):

    client = htsp.HTSPClient(tvheadend_server)

    print "[*] Say hello ..."

    resp = client.hello()

    print "[-] Server answers:"

    print "    Servername:    " + resp['servername']

    print "    Serverversion: " + resp['serverversion']

    print "    HTSP version:  " + str(resp['htspversion'])

    challenge = resp['challenge']

    print "[*] Authenticating ..."

    resp = client.authenticate(username, password)

    print "[*] Authentication succeeded"

    return client

########################

# Init SQLite DB (matched EPG results)

########################

def init_and_connect_DB(db_filename):

    print("[*] Using pysqlite version " + str(sqlite3.version))

    print("[*] Using SQLite version " + str(sqlite3.sqlite_version))

    try:

        connection = sqlite3.connect(db_filename)

        print("[*] Connected to SQLite database " + db_filename)

        with connection:

            cur = connection.cursor()

            cur.execute("DROP TABLE IF EXISTS Events")

            cur.execute("CREATE TABLE Events(eventid INT, title TEXT, description TEXT, start INT, stop INT, channelid INT)")

        connection.commit()

        print("[*] Created new SQLite database " + db_filename)

        return connection

    except sqlite3.Error:

        if connection:

            connection.rollback()

        print("[*] Error %s:" % sqlite3.Error.args[0])

        sys.exit(1)

########################

# Disconnect SQLite DB 

########################

def disconnectDB(connection):

    if connection:

        connection.close()

    print("[*] Disconnected from DB " + str(db_filename))

########################

# Search in EPG for matches

########################

def fill_DB_with_movies_from_file(movielist_filename, connection):

    # Iterate through the Most Wanted Movie List and search in EPG

    with open(movielist_filename) as f:

        for line in f:

           # Strip the newline

           line = line.rstrip('\n')

           # Query for EPG data

           print "[*] Searching for regular expression: " + line

           args = { "query":line,"full":1 }

           client.send("epgQuery", args)

           resp = client.recv()

           if resp:

              print "[*] Match(es) found!"

              # Iterate through all matches and parse it to the queue

              # {'eventId': 78377, 'contentType': 21, 'description': '...', 'title': 'NDR Comedy Contest', 'nextEventId': 78379, 'channelId': 13, 'stop': 1435964400, 'episodeId': 78378, 'start': 1435960800}

              for event in resp['events']:

                  eventid = event['eventId']

                  title = unicode(event['title'])

                  print (title)

                  channelid = event['channelId']

                  start = event['start']

                  stop = event['stop']

                  descr = None # Init

                  if 'description' in event.keys():

                      descr = unicode(event['description'])

                  else:

                      descr = ""

                  with connection:

                      cur = connection.cursor()

                      cur.execute("INSERT INTO Events (eventid,title,description,start,stop,channelid) values (?, ?, ?, ?, ?, ?)",(eventid,title,descr,start,stop,channelid))

                  connection.commit()

           # No regexp match

           else:

                print "[*] No match"

########################

# Generate HTML output for Mail

########################

def query_and_generate_HTML_from_DB(connection):

    # HTML result for the email

    html_result = ""

    html_result += "<table border =1>\n"

    html_result += "<th>Titel</th><th>Beschreibung<th>Sendezeiten und Kanal</th>\n"

    cur = connection.cursor()

    # Title and description as a unique identifier for one movie

    cur.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")

    rows_title_descr = cur.fetchall()

    for row_title_descr in rows_title_descr:

        title = row_title_descr[0]

        descr = row_title_descr[1]

        print("[---------------")

        print("[-] Title:       " + title)

        print("[-] Description: " + descr)

        # HTML: Beginning of the movie section

        html_result += "  <tr>\n"

        html_result += "    <td>" + title + "</td>\n"

        html_result += "    <td>" + descr + "</td>\n"

        # Fetch all attributes for one movie (multiple schedules, channels) 

        cur.execute("SELECT DISTINCT start,stop,channelid FROM Events WHERE title=? and description=?",(row_title_descr[0], row_title_descr[1]))

        rows_start_stop_channel = cur.fetchall()

        # HTML: Beginning of the schedules (considering multiple schedules for one movie)

        html_result += "    <td><ul>"

        for row_start_stop_channel in rows_start_stop_channel:

            schedule_start = str(row_start_stop_channel[0])

            schedule_stop  = str(row_start_stop_channel[1])

            schedule_start = time.strftime("%d.%m.%Y | %H:%M",time.localtime(row_start_stop_channel[0]))

            schedule_stop  = time.strftime(" - %H:%M",time.localtime(row_start_stop_channel[1]))

            channel  = str(row_start_stop_channel[2])

            print("[-] Schedule: " + schedule_start + " to " + schedule_stop + " on channel " + channel)

            # Only break line when there are multiple schedules and the current schedule is not the last

            if (len(rows_start_stop_channel) > 1) and (row_start_stop_channel != rows_start_stop_channel[len(rows_start_stop_channel)-1]):

                html_result += "<li>" + schedule_start + schedule_stop + " auf Kanal " + channel + "</li>\n"

            else:

                html_result += "<li>" + schedule_start + schedule_stop + " auf Kanal " + channel + "</li>"

                # HTML: Ending of the schedules section

                html_result += "</ul></td>\n"

        # HTML: Ending of the movie section

        html_result += "  </tr>\n"

    html_result += "</table>\n"

    return html_result

########################

# Check for already transferred movies (prevent sending it twice)

########################

def checkUpdates(con_db, db_backup_filename):

    # 1) Delete already mailed movies from the current search DB

    #    Check if backup DB already exists (history of already mailed movies)

    if os.path.isfile(db_backup_filename):

        try:

            con_db_backup = sqlite3.connect(db_backup_filename)

            print("[*] Connected to SQLite database " + db_backup_filename)

        except sqlite3.Error:

            if con_db_backup:

                con_db_backup.rollback()

            print("[*] Error %s:" % sqlite3.Error.args[0])

            sys.exit(1)

        cur_db = con_db.cursor()

        cur_db_backup = con_db_backup.cursor()

        # Title and description as a unique identifier for one movie

        cur_db.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")

        rows_db_title_descr = cur_db.fetchall()

        cur_db_backup.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")

        rows_db_backup_title_descr = cur_db_backup.fetchall()

        for row_db_title_descr in rows_db_title_descr:

            title_db = row_db_title_descr[0]

            descr_db = row_db_title_descr[1]

            # Check for each movie (identified as: title, description) if it exists in the backup DB (history)

            for row_db_backup_title_descr in rows_db_backup_title_descr:

                # If title and description are the same (=> duplicate)

                if ((title_db in row_db_backup_title_descr) and (descr_db in row_db_backup_title_descr)):

                    title_db_backup = row_db_backup_title_descr[0]

                    descr_db_backup = row_db_backup_title_descr[1]

                    # Delete already known (and sent) movies from current search (db) 

                    cur_db.execute("DELETE FROM Events WHERE title=? and description=?", (title_db, descr_db))

                    print ("[*] Record identified as duplicate and removed from result set: " + title_db + " -- " + descr_db)

        # 2) Add new search entries to the backup DB (history)

        cur_db.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")

        rows_db_title_descr = cur_db.fetchall()

        cur_db_backup.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")

        rows_db_backup_title_descr = cur_db_backup.fetchall()

        for row_db_title_descr in rows_db_title_descr:

            title_db = row_db_title_descr[0]

            descr_db = row_db_title_descr[1]

            # Check for each movie (identified as: title, description) if it exists in the backup DB (history)

            for row_db_backup_title_descr in rows_db_backup_title_descr:

                # If title and description are NOT the same (=> new movie) it is added to the backup DB (history)

                if (title_db not in row_db_backup_title_descr) or (descr_db not in row_db_backup_title_descr):

                    title_db_backup = row_db_backup_title_descr[0]

                    descr_db_backup = row_db_backup_title_descr[1]

                    cur_db_backup.execute("INSERT INTO Events (eventid,title,description,start,stop,channelid) values (?, ?, ?, ?, ?, ?)",("",title_db,descr_db,0,0,0))

                    con_db_backup.commit()

                    print ("[*] Record added to the backup db (history): " + title_db + " -- " + descr_db)

    else:

        print("[*] No backup file yet exists ... creating one")

        shutil.copyfile(db_filename, db_backup_filename)

    # Check (for entries) if new movies were added

    cur_db = con_db.cursor()

    cur_db.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")

    # No new movies found

    if (len(cur_db.fetchall()) < 1):

        return False

    # New movies found

    else:

        return True

########################

# Send Email

########################

def sendMail(htmlmsg, from_address, recipient, subject):

    html = ""

    html += """

        <html>

        <head>

        <style>

        table, td, th {

            border: 1px solid black;

        }

        table {

            border-collapse: collapse;

        }

        th {

            height: 50px;

        }

        table, td, th {

            border: 1px solid green;

        }

        td, th {

            padding: 0.5rem;

        }

        th {

            background-color: green;

            color: white;

        }

        </style>

        </head>

        <body>

        """

    html += htmlmsg

    htmlpart = MIMEText(html, 'html', 'UTF-8')

    msg = MIMEMultipart('alternative')

    msg['Subject'] = "%s" % Header(subject, 'utf-8')

    # Only descriptive part of recipient and sender shall be encoded, not the email address

    msg['From'] = "\"%s\" <%s>" % (Header(from_address[0], 'utf-8'), from_address[1])

    msg['To'] = "\"%s\" <%s>" % (Header(recipient[0], 'utf-8'), recipient[1])

    # Attach both parts

    htmlpart = MIMEText(html, 'html', 'UTF-8')

    msg.attach(htmlpart)

    # Create a generator and flatten message object to 'file’

    str_io = StringIO()

    g = Generator(str_io, False)

    g.flatten(msg)

    # str_io.getvalue() contains ready to sent message

    try:

        smtp = smtplib.SMTP(smtpserver)

        smtp.login(smtp_username,smtp_password)

        smtp.sendmail(from_address[1], recipient[1], str_io.getvalue())

        print "Successfully sent email"

    except SMTPException:

        print "Error: unable to send email"

# MAIN PROGRAM     

# Connect to TVheadend Server

client = connect_tvheadend_server(tvheadend_username, tvheadend_password)

# Connect and init new DB

con = init_and_connect_DB(db_filename)

# Search in EPG for movies and write to database (only in case of matches!)

fill_DB_with_movies_from_file(movielist_filename, con)

# Check if there were any matches or updates

# Open old state of DB and delete (old) already transferred matches

if (checkUpdates(con, db_backup_filename)):

    # Search for matching titles

    htmlmsg = query_and_generate_HTML_from_DB(con)

    # Send Mail

    sendMail(htmlmsg, from_address, recipient, subject)

else:

    print("[*] No new movies found ... not sending an email")

# Disconnect

disconnectDB(con)