import csv
import urllib.request, json
import pymysql.cursors
from getpass import getuser

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

from datetime import date

import codecs

import schedule
import time

cnx = pymysql.connect(user="bookzi_dk",
                      password="xxxxxxxxxxxx",
                      host='xxxxxxxxxxxx',
                      database='xxxxxxxxxx')

cursor = cnx.cursor()


def Aopgang_updatedata():
    try:
        #Removes all data from table Dopgang
        truncateAopgang = ('Truncate Table Aopgang;')
        cursor.execute(truncateAopgang)

        #Adds new information
        with open("A-opgang.csv") as csvfile:
            reader = csv.DictReader(csvfile, delimiter = ';')
            for row in reader:
                week = row['week']
                common = row['common']
                kitchen = row['kitchen']

                query = ('INSERT INTO Aopgang (week, common, kitchen) VALUES (%s, %s, %s);')
                cursor.execute(query, (week, common, kitchen))

        cnx.commit()
        cnx.close()

        print("A opgang data opdateret!")

    except:
        print('Error running Aopgang_updatedata')
def Dopgang_updatedata():

    try:
        #Removes all data from table Dopgang
        truncateDopgang = ('Truncate Table Dopgang;')
        cursor.execute(truncateDopgang)



        #Adds new information
        with open("D-opgang.csv") as csvfile:
            reader = csv.DictReader(csvfile, delimiter = ';')
            for row in reader:
                week = row['week']
                common = row['common']
                kitchen = row['kitchen']

                query = ('INSERT INTO Dopgang (week, common, kitchen) VALUES (%s, %s, %s);')
                cursor.execute(query, (week, common, kitchen))

        cnx.commit()
        cnx.close()

        print("D opgang data opdateret!")


    except:
        print('Error running Dopgang_updatedata')


def Bopgang_updatedata():

    try:
        #Removes all data from table Dopgang
        truncateBopgang = ('Truncate Table Bopgang;')
        cursor.execute(truncateBopgang)



        #Adds new information
        with open("B-opgang.csv") as csvfile:
            reader = csv.DictReader(csvfile, delimiter = ';')
            for row in reader:
                week = row['week']
                common = row['common']


                query = ('INSERT INTO Bopgang (week, common) VALUES (%s, %s);')
                cursor.execute(query, (week, common))

        cnx.commit()
        cnx.close()

        print("B opgang data opdateret!")

    except:
        print('Error running Dopgang_updatedata')



def sendmail(textmsg,subject,emails):


    try:

        msg = MIMEMultipart()
        msg['from']= "noreply@xxxxxxx"
        msg['To']= emails
        msg['Subject']= subject

        body= textmsg
        msg.attach(MIMEText(body, 'html'))
        print(msg)

        server = smtplib.SMTP('asmtp.unoeuro.com', 587)

        server.starttls()
        server.login(msg['From'], "xxxxxxxxxxxxx")

        server.sendmail(msg['From'], msg['To'], msg.as_string())
        server.quit()

        print('Mail was send sucessfully')

    except:
        print('Error running sendmail')
def getcurrentweeknumber():

    return date.today().isocalendar()[1]
def weekcleaning_Aopgang(weeknumber):
    try:
        query = ('SELECT * FROM Aopgang where week=%s;')
        cursor.execute(query,(int(weeknumber)))
        result = cursor.fetchall()
        final = []

        for i in result:
            common=i[1]
            kitchen=i[2]

            final.append(common)
            final.append(kitchen)

        return final

    except:
        print('Erro getting info from function weekcleaning_Aopgang with weeknumber'+str(weeknumber))
def weekcleaning_Dopgang(weeknumber):
    try:
        query = ('SELECT * FROM Dopgang where week=%s;')
        cursor.execute(query,(int(weeknumber)))
        result = cursor.fetchall()
        final = []

        for i in result:
            common=i[1]
            kitchen=i[2]

            final.append(common)
            final.append(kitchen)

        return final

    except:
        print('Erro getting info from function weekcleaning_Dopgang with weeknumber'+str(weeknumber))
def getuserinfo(dormtype,roomnumber):


    try:
        query = ('SELECT * fROM userinfo WHERE dormtype=%s AND roomnumber=%s;')

        cursor.execute(query,(dormtype,roomnumber))
        result = cursor.fetchall()

        userinfo=[]

        for i in result:
            info=[]
            firstname=i[1]
            email=i[2]
            dormtype=i[3]
            roomnumber=i[4]
            language=i[5]
            deletekey=i[7]
            info.append(firstname)
            info.append(email)
            info.append(dormtype)
            info.append(roomnumber)
            info.append(language)
            info.append(deletekey)

            userinfo.append(info)

        if userinfo==[]:
            return ""

        else:
            return userinfo

    except:
        print("Error getting info from function getuserinfo with dormtype: "+str(dormtype)+"roomnumber: "+str(roomnumber))
def ENemailtemplate():
    try:
        f=codecs.open("emailen.html", 'r',encoding="utf-8")

        emailstring=f.read()
        return emailstring

    except:
        print("Error finding emailtemplate EN (ENemailtemplate")

def DAemailtemplate():

    try:
        f=codecs.open("emailda.html", 'r' ,encoding="utf-8")
        emailstring=f.read()
        return emailstring
    except:
        print("Error finding emailtempalte DA (DAemailtamplate")


aOpgang_common=(getuserinfo("a",weekcleaning_Aopgang(getcurrentweeknumber())[0]))
aOpgang_kitchen=(getuserinfo("a",weekcleaning_Aopgang(getcurrentweeknumber())[1]))

#Get cleaning userinfo for Dopgang
dOpgang_common=(getuserinfo("d",weekcleaning_Dopgang(getcurrentweeknumber())[0]))
dOpgang_kitchen=(getuserinfo("d",weekcleaning_Dopgang(getcurrentweeknumber())[1]))




def sendreminder(userinfocleaningduty,area):


    if len(userinfocleaningduty)>0:

        for i in userinfocleaningduty:

            #If language is danish
            if i[4]=="da":
                areas=["Fællesområde","Køkkenet"]

                print(i)

                emailtemp=DAemailtemplate()
                emailtemp=emailtemp.replace("(name)",i[0])
                emailtemp=emailtemp.replace("(dormtype)",i[2]+"-opgang")
                emailtemp=emailtemp.replace("(roomnumber)",str(i[3]))
                emailtemp=emailtemp.replace("(weeknumber)",str(getcurrentweeknumber()))
                emailtemp=emailtemp.replace("(area)", areas[area])

                emailtemp=emailtemp.replace("(code)",i[5])
                emailtemp=emailtemp.replace("(email)",i[1])
                sendmail(emailtemp, "Rengøringsreminder", i[1])

            #if language is english
            if i[4]=="en":

                areas=["Commonarea","Kitchen"]
                emailtemp=ENemailtemplate()
                emailtemp=emailtemp.replace("(name)",i[0])
                emailtemp=emailtemp.replace("(dormtype)",i[2])
                emailtemp=emailtemp.replace("(roomnumber)",str(i[3]))
                emailtemp=emailtemp.replace("(weeknumber)",str(getcurrentweeknumber()))
                emailtemp=emailtemp.replace("(area)",areas[area])

                emailtemp=emailtemp.replace("(code)",i[5])
                emailtemp=emailtemp.replace("(email)",i[1])

                sendmail(emailtemp, "Cleaningreminder", i[1])



def start():
#Common A-opgang
    sendreminder(aOpgang_common,0)
    #Kitchen A-opgang
    sendreminder(aOpgang_kitchen,1)
    #Kitchen D-opgang
    sendreminder(dOpgang_common,0)

    sendreminder(dOpgang_kitchen,1)


# schedule.every().monday.at("10:00").do(start)
#
#
# while True:
#     schedule.run_pending()
#     time.sleep(1)

print(getuserinfo("a", "6"))

start()