# munkireport integration to alerts users of 7 day uptime.
import re
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import sqlite3
conn = sqlite3.connect('db.sqlite')
import smtplib
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText

## use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
#
# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
sheet = client.open("copiousUsers").sheet1
humanname = sheet.col_values(2)
humanname = filter(None, humanname)
serialnumber = sheet.col_values(3)
serialnumber = filter(None, serialnumber)
emailaddress = sheet.col_values(5)
emailaddress = filter(None, emailaddress)
hostname = sheet.col_values(4)
hostname = filter(None, hostname)

# Now find hosts on the shitlist
c = conn.cursor()
#c.execute('SELECT hostname FROM machine INNER JOIN reportdata ON machine.id=reportdata.id WHERE reportdata.uptime>604800;')
c.execute('SELECT serial_number FROM reportdata WHERE uptime>604800;')
shitlist = c.fetchall()
conn.close()
# clean up the list and make ascii unicode
newlist = []
for tup in shitlist:
newlist = newlist + [item.encode('ascii','backslashreplace') for item in tup]
# get rid of anything after the first (.)
newlist = [i.split('.', 1)[0] for i in newlist]

# loop the list and search for matches against the googlesheet, get the details of each user and make a list of lists.
userlist = []
for line in serialnumber:
if line in newlist:
indexNumber = (serialnumber.index(line))
user=(humanname[indexNumber])
email=(emailaddress[indexNumber])
host=(hostname[indexNumber])
x = [user, email, host]
userlist.append(x)

for line in userlist:
## user is line[0] and email is line[1] and the host is line[2]
msg = MIMEMultipart('alternative')
msg['From'] = 'yourEmail'
msg['To'] = line[1]
msg['Subject'] = 'Reboot Reminder, Over 7 Days Uptime Detected'

text = """\
Hello %s,

We have noticed that your computer, %s, has been up for 7 days without a reboot. Please reboot your computer when you can. Having a computer left on for too long without a reboot can lead to memory leaks and overall performance issues.

Best Regards,
Automatic Watchdog
""" % (line[0],line[2])
html = """\

Hello %s,

We have noticed that your computer, %s, has been up for 7 days without a reboot. Please reboot your computer when you can. Having a computer left on for too long without a reboot can lead to memory leaks and overal performance issues.


""" % (line[0],line[2])

part1 = MIMEText(text, 'plain')
part2 = MIMEText(html, 'html')
msg.attach(part1)
msg.attach(part2)

server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login("emailaddress", "password")
text = msg.as_string()