1
|
#!/usr/bin/env python
|
2
|
# -*- coding: iso-8859-1 -*-
|
3
|
|
4
|
# General Imorts
|
5
|
import htsp
|
6
|
import json
|
7
|
from time import *
|
8
|
import time
|
9
|
import sqlite3
|
10
|
import sys
|
11
|
import os.path
|
12
|
import shutil
|
13
|
|
14
|
# Imports for mail transfer (considering UTF8 and German Umlaute)
|
15
|
import smtplib
|
16
|
from cStringIO import StringIO
|
17
|
from email.mime.multipart import MIMEMultipart
|
18
|
from email.mime.text import MIMEText
|
19
|
from email.header import Header
|
20
|
from email import Charset
|
21
|
from email.generator import Generator
|
22
|
|
23
|
# Unicode Workaround (Umlaute etc.)
|
24
|
import sys
|
25
|
reload(sys)
|
26
|
sys.setdefaultencoding("utf-8")
|
27
|
|
28
|
#########################
|
29
|
# Configuration
|
30
|
#########################
|
31
|
|
32
|
# TVheadend configuration
|
33
|
tvheadend_username = "XXX"
|
34
|
tvheadend_password = "XXX"
|
35
|
tvheadend_server = ("10.0.0.10",9982)
|
36
|
movielist_filename = "movie_liste.txt"
|
37
|
|
38
|
# Mail configuration
|
39
|
from_address = ['RaspVDR', '[email protected]']
|
40
|
recipient = ['My name', '[email protected]']
|
41
|
subject = "[RaspVDR] Neue Film-Treffer"
|
42
|
smtpserver = 'smtp.example.com'
|
43
|
smtp_username = "raspi"
|
44
|
smtp_password = "password"
|
45
|
|
46
|
# SQLite DB Filenames
|
47
|
db_filename = "epgEventsMatched.db"
|
48
|
db_backup_filename = "epgEventsMatched.backup.db"
|
49
|
db_swap_filename = "epgEventsMatched.swap.db"
|
50
|
|
51
|
########################
|
52
|
# TVheadend connection
|
53
|
########################
|
54
|
def connect_tvheadend_server(username, password):
|
55
|
client = htsp.HTSPClient(tvheadend_server)
|
56
|
|
57
|
print "[*] Say hello ..."
|
58
|
resp = client.hello()
|
59
|
print "[-] Server answers:"
|
60
|
print " Servername: " + resp['servername']
|
61
|
print " Serverversion: " + resp['serverversion']
|
62
|
print " HTSP version: " + str(resp['htspversion'])
|
63
|
challenge = resp['challenge']
|
64
|
|
65
|
print "[*] Authenticating ..."
|
66
|
resp = client.authenticate(username, password)
|
67
|
print "[*] Authentication succeeded"
|
68
|
return client
|
69
|
|
70
|
########################
|
71
|
# Init SQLite DB (matched EPG results)
|
72
|
########################
|
73
|
def init_and_connect_DB(db_filename):
|
74
|
print("[*] Using pysqlite version " + str(sqlite3.version))
|
75
|
print("[*] Using SQLite version " + str(sqlite3.sqlite_version))
|
76
|
|
77
|
try:
|
78
|
connection = sqlite3.connect(db_filename)
|
79
|
print("[*] Connected to SQLite database " + db_filename)
|
80
|
with connection:
|
81
|
cur = connection.cursor()
|
82
|
cur.execute("DROP TABLE IF EXISTS Events")
|
83
|
cur.execute("CREATE TABLE Events(eventid INT, title TEXT, description TEXT, start INT, stop INT, channelid INT)")
|
84
|
connection.commit()
|
85
|
print("[*] Created new SQLite database " + db_filename)
|
86
|
return connection
|
87
|
except sqlite3.Error:
|
88
|
if connection:
|
89
|
connection.rollback()
|
90
|
print("[*] Error %s:" % sqlite3.Error.args[0])
|
91
|
sys.exit(1)
|
92
|
|
93
|
########################
|
94
|
# Disconnect SQLite DB
|
95
|
########################
|
96
|
def disconnectDB(connection):
|
97
|
if connection:
|
98
|
connection.close()
|
99
|
print("[*] Disconnected from DB " + str(db_filename))
|
100
|
|
101
|
|
102
|
########################
|
103
|
# Search in EPG for matches
|
104
|
########################
|
105
|
def fill_DB_with_movies_from_file(movielist_filename, connection):
|
106
|
# Iterate through the Most Wanted Movie List and search in EPG
|
107
|
with open(movielist_filename) as f:
|
108
|
for line in f:
|
109
|
# Strip the newline
|
110
|
line = line.rstrip('\n')
|
111
|
# Query for EPG data
|
112
|
print "[*] Searching for regular expression: " + line
|
113
|
args = { "query":line,"full":1 }
|
114
|
client.send("epgQuery", args)
|
115
|
resp = client.recv()
|
116
|
if resp:
|
117
|
print "[*] Match(es) found!"
|
118
|
# Iterate through all matches and parse it to the queue
|
119
|
# {'eventId': 78377, 'contentType': 21, 'description': '...', 'title': 'NDR Comedy Contest', 'nextEventId': 78379, 'channelId': 13, 'stop': 1435964400, 'episodeId': 78378, 'start': 1435960800}
|
120
|
for event in resp['events']:
|
121
|
eventid = event['eventId']
|
122
|
title = unicode(event['title'])
|
123
|
print (title)
|
124
|
channelid = event['channelId']
|
125
|
start = event['start']
|
126
|
stop = event['stop']
|
127
|
descr = None # Init
|
128
|
if 'description' in event.keys():
|
129
|
descr = unicode(event['description'])
|
130
|
else:
|
131
|
descr = ""
|
132
|
with connection:
|
133
|
cur = connection.cursor()
|
134
|
cur.execute("INSERT INTO Events (eventid,title,description,start,stop,channelid) values (?, ?, ?, ?, ?, ?)",(eventid,title,descr,start,stop,channelid))
|
135
|
connection.commit()
|
136
|
|
137
|
# No regexp match
|
138
|
else:
|
139
|
print "[*] No match"
|
140
|
|
141
|
|
142
|
########################
|
143
|
# Generate HTML output for Mail
|
144
|
########################
|
145
|
def query_and_generate_HTML_from_DB(connection):
|
146
|
# HTML result for the email
|
147
|
html_result = ""
|
148
|
html_result += "<table border =1>\n"
|
149
|
html_result += "<th>Titel</th><th>Beschreibung<th>Sendezeiten und Kanal</th>\n"
|
150
|
|
151
|
cur = connection.cursor()
|
152
|
# Title and description as a unique identifier for one movie
|
153
|
cur.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")
|
154
|
rows_title_descr = cur.fetchall()
|
155
|
|
156
|
for row_title_descr in rows_title_descr:
|
157
|
title = row_title_descr[0]
|
158
|
descr = row_title_descr[1]
|
159
|
|
160
|
print("[---------------")
|
161
|
print("[-] Title: " + title)
|
162
|
print("[-] Description: " + descr)
|
163
|
|
164
|
# HTML: Beginning of the movie section
|
165
|
html_result += " <tr>\n"
|
166
|
html_result += " <td>" + title + "</td>\n"
|
167
|
html_result += " <td>" + descr + "</td>\n"
|
168
|
|
169
|
# Fetch all attributes for one movie (multiple schedules, channels)
|
170
|
cur.execute("SELECT DISTINCT start,stop,channelid FROM Events WHERE title=? and description=?",(row_title_descr[0], row_title_descr[1]))
|
171
|
rows_start_stop_channel = cur.fetchall()
|
172
|
|
173
|
# HTML: Beginning of the schedules (considering multiple schedules for one movie)
|
174
|
html_result += " <td><ul>"
|
175
|
for row_start_stop_channel in rows_start_stop_channel:
|
176
|
schedule_start = str(row_start_stop_channel[0])
|
177
|
schedule_stop = str(row_start_stop_channel[1])
|
178
|
|
179
|
schedule_start = time.strftime("%d.%m.%Y | %H:%M",time.localtime(row_start_stop_channel[0]))
|
180
|
schedule_stop = time.strftime(" - %H:%M",time.localtime(row_start_stop_channel[1]))
|
181
|
|
182
|
channel = str(row_start_stop_channel[2])
|
183
|
|
184
|
print("[-] Schedule: " + schedule_start + " to " + schedule_stop + " on channel " + channel)
|
185
|
|
186
|
# Only break line when there are multiple schedules and the current schedule is not the last
|
187
|
if (len(rows_start_stop_channel) > 1) and (row_start_stop_channel != rows_start_stop_channel[len(rows_start_stop_channel)-1]):
|
188
|
html_result += "<li>" + schedule_start + schedule_stop + " auf Kanal " + channel + "</li>\n"
|
189
|
else:
|
190
|
html_result += "<li>" + schedule_start + schedule_stop + " auf Kanal " + channel + "</li>"
|
191
|
# HTML: Ending of the schedules section
|
192
|
html_result += "</ul></td>\n"
|
193
|
|
194
|
# HTML: Ending of the movie section
|
195
|
html_result += " </tr>\n"
|
196
|
|
197
|
html_result += "</table>\n"
|
198
|
return html_result
|
199
|
|
200
|
########################
|
201
|
# Check for already transferred movies (prevent sending it twice)
|
202
|
########################
|
203
|
def checkUpdates(con_db, db_backup_filename):
|
204
|
|
205
|
# 1) Delete already mailed movies from the current search DB
|
206
|
# Check if backup DB already exists (history of already mailed movies)
|
207
|
if os.path.isfile(db_backup_filename):
|
208
|
try:
|
209
|
con_db_backup = sqlite3.connect(db_backup_filename)
|
210
|
print("[*] Connected to SQLite database " + db_backup_filename)
|
211
|
|
212
|
except sqlite3.Error:
|
213
|
if con_db_backup:
|
214
|
con_db_backup.rollback()
|
215
|
print("[*] Error %s:" % sqlite3.Error.args[0])
|
216
|
sys.exit(1)
|
217
|
|
218
|
cur_db = con_db.cursor()
|
219
|
cur_db_backup = con_db_backup.cursor()
|
220
|
|
221
|
# Title and description as a unique identifier for one movie
|
222
|
cur_db.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")
|
223
|
rows_db_title_descr = cur_db.fetchall()
|
224
|
cur_db_backup.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")
|
225
|
rows_db_backup_title_descr = cur_db_backup.fetchall()
|
226
|
|
227
|
for row_db_title_descr in rows_db_title_descr:
|
228
|
title_db = row_db_title_descr[0]
|
229
|
descr_db = row_db_title_descr[1]
|
230
|
|
231
|
# Check for each movie (identified as: title, description) if it exists in the backup DB (history)
|
232
|
for row_db_backup_title_descr in rows_db_backup_title_descr:
|
233
|
# If title and description are the same (=> duplicate)
|
234
|
if ((title_db in row_db_backup_title_descr) and (descr_db in row_db_backup_title_descr)):
|
235
|
title_db_backup = row_db_backup_title_descr[0]
|
236
|
descr_db_backup = row_db_backup_title_descr[1]
|
237
|
# Delete already known (and sent) movies from current search (db)
|
238
|
cur_db.execute("DELETE FROM Events WHERE title=? and description=?", (title_db, descr_db))
|
239
|
print ("[*] Record identified as duplicate and removed from result set: " + title_db + " -- " + descr_db)
|
240
|
|
241
|
# 2) Add new search entries to the backup DB (history)
|
242
|
cur_db.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")
|
243
|
rows_db_title_descr = cur_db.fetchall()
|
244
|
cur_db_backup.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")
|
245
|
rows_db_backup_title_descr = cur_db_backup.fetchall()
|
246
|
|
247
|
for row_db_title_descr in rows_db_title_descr:
|
248
|
title_db = row_db_title_descr[0]
|
249
|
descr_db = row_db_title_descr[1]
|
250
|
|
251
|
# Check for each movie (identified as: title, description) if it exists in the backup DB (history)
|
252
|
for row_db_backup_title_descr in rows_db_backup_title_descr:
|
253
|
# If title and description are NOT the same (=> new movie) it is added to the backup DB (history)
|
254
|
if (title_db not in row_db_backup_title_descr) or (descr_db not in row_db_backup_title_descr):
|
255
|
title_db_backup = row_db_backup_title_descr[0]
|
256
|
descr_db_backup = row_db_backup_title_descr[1]
|
257
|
cur_db_backup.execute("INSERT INTO Events (eventid,title,description,start,stop,channelid) values (?, ?, ?, ?, ?, ?)",("",title_db,descr_db,0,0,0))
|
258
|
con_db_backup.commit()
|
259
|
print ("[*] Record added to the backup db (history): " + title_db + " -- " + descr_db)
|
260
|
|
261
|
else:
|
262
|
print("[*] No backup file yet exists ... creating one")
|
263
|
shutil.copyfile(db_filename, db_backup_filename)
|
264
|
|
265
|
# Check (for entries) if new movies were added
|
266
|
cur_db = con_db.cursor()
|
267
|
cur_db.execute("SELECT DISTINCT title,description FROM Events GROUP BY title,description")
|
268
|
# No new movies found
|
269
|
if (len(cur_db.fetchall()) < 1):
|
270
|
return False
|
271
|
# New movies found
|
272
|
else:
|
273
|
return True
|
274
|
|
275
|
|
276
|
########################
|
277
|
# Send Email
|
278
|
########################
|
279
|
def sendMail(htmlmsg, from_address, recipient, subject):
|
280
|
|
281
|
html = ""
|
282
|
html += """
|
283
|
<html>
|
284
|
<head>
|
285
|
<style>
|
286
|
table, td, th {
|
287
|
border: 1px solid black;
|
288
|
}
|
289
|
|
290
|
table {
|
291
|
border-collapse: collapse;
|
292
|
}
|
293
|
|
294
|
th {
|
295
|
height: 50px;
|
296
|
}
|
297
|
|
298
|
table, td, th {
|
299
|
border: 1px solid green;
|
300
|
}
|
301
|
|
302
|
td, th {
|
303
|
padding: 0.5rem;
|
304
|
}
|
305
|
|
306
|
th {
|
307
|
background-color: green;
|
308
|
color: white;
|
309
|
}
|
310
|
</style>
|
311
|
</head>
|
312
|
<body>
|
313
|
"""
|
314
|
|
315
|
html += htmlmsg
|
316
|
htmlpart = MIMEText(html, 'html', 'UTF-8')
|
317
|
|
318
|
msg = MIMEMultipart('alternative')
|
319
|
msg['Subject'] = "%s" % Header(subject, 'utf-8')
|
320
|
# Only descriptive part of recipient and sender shall be encoded, not the email address
|
321
|
msg['From'] = "\"%s\" <%s>" % (Header(from_address[0], 'utf-8'), from_address[1])
|
322
|
msg['To'] = "\"%s\" <%s>" % (Header(recipient[0], 'utf-8'), recipient[1])
|
323
|
|
324
|
# Attach both parts
|
325
|
htmlpart = MIMEText(html, 'html', 'UTF-8')
|
326
|
msg.attach(htmlpart)
|
327
|
|
328
|
# Create a generator and flatten message object to 'file’
|
329
|
str_io = StringIO()
|
330
|
g = Generator(str_io, False)
|
331
|
g.flatten(msg)
|
332
|
# str_io.getvalue() contains ready to sent message
|
333
|
|
334
|
try:
|
335
|
smtp = smtplib.SMTP(smtpserver)
|
336
|
smtp.login(smtp_username,smtp_password)
|
337
|
smtp.sendmail(from_address[1], recipient[1], str_io.getvalue())
|
338
|
print "Successfully sent email"
|
339
|
except SMTPException:
|
340
|
print "Error: unable to send email"
|
341
|
|
342
|
|
343
|
|
344
|
# MAIN PROGRAM
|
345
|
|
346
|
# Connect to TVheadend Server
|
347
|
client = connect_tvheadend_server(tvheadend_username, tvheadend_password)
|
348
|
|
349
|
# Connect and init new DB
|
350
|
con = init_and_connect_DB(db_filename)
|
351
|
|
352
|
# Search in EPG for movies and write to database (only in case of matches!)
|
353
|
fill_DB_with_movies_from_file(movielist_filename, con)
|
354
|
|
355
|
# Check if there were any matches or updates
|
356
|
# Open old state of DB and delete (old) already transferred matches
|
357
|
if (checkUpdates(con, db_backup_filename)):
|
358
|
|
359
|
# Search for matching titles
|
360
|
htmlmsg = query_and_generate_HTML_from_DB(con)
|
361
|
|
362
|
# Send Mail
|
363
|
sendMail(htmlmsg, from_address, recipient, subject)
|
364
|
else:
|
365
|
print("[*] No new movies found ... not sending an email")
|
366
|
|
367
|
# Disconnect
|
368
|
disconnectDB(con)
|
369
|
|