import smtplib
import mimetypes
from email.mime.multipart import MIMEMultipart
from email import encoders
from email.message import Message
from email.mime.audio import MIMEAudio
from email.mime.base import MIMEBase
from email.mime.image import MIMEImage
from email.mime.text import MIMEText
import datetime


import xlwt

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

# Create connection with MySQL database
cur = create_engine('mysql+pymysql://root:user1234@127.0.0.1:3306/atlantip?charset=utf8&local_infile=1')
#cur = create_engine('mysql+pymysql://straqr:straqr2018@straqr.cicypd6ywu7v.ap-south-1.rds.amazonaws.com:3306/dj?charset=utf8&local_infile=1')
db = scoped_session(sessionmaker(autocommit=False,  autoflush=False, bind=cur ))





# create excel sheet for notification

def notification_rule():

    wb = xlwt.Workbook(encoding='utf-8')
    sel_q = "SELECT patent_title , territory , p_type , publication_no, registration_date, registration_no, next_annuity, annuity_no , owner_name , group_name , agent_name FROM atlip_patentportfolio where datediff( next_annuity , curdate())"

    sel = cur.execute(sel_q).fetchall()


    if len(sel)>0 :
        ws = wb.add_sheet('Patent', cell_overwrite_ok=True)
        row_num = 0
        font_style = xlwt.XFStyle()
        font_style.font.bold = True

        columns = ["Patent Title" , "Territory" , "Type" , "Publication Number" , "Registration Date" , "Registration Number" , "Next Annuity" , "Annuity Number", "Owner" , "Group" , "Agent"]


        for col_num in range(len(columns)):
            #print(columns[col_num])
            ws.write(row_num, col_num, columns[col_num], font_style)

        # Writing data in excel
        row_num = 0
        font_style = xlwt.XFStyle()

        for s in sel :
            row_num += 1

            for col_num in range(len(s)):
                #print(str(s[col_num]))
                ws.write(row_num, col_num, str(s[col_num]), font_style)

        wb.save('Email_notifications.xls')

    else :
        pass


    # return response
notification_rule()




"""emailfrom = "info@socialtraqr.com"
emailto = "rajat@socialtraqr.com"
fileToSend = "hi.xlsx"
username = "info@socialtraqr.com"
password = "xsw2#EDC"

msg = MIMEMultipart()
msg["From"] = "info@socialtraqr.com"
msg["To"] = "rajat@socialtraqr.com"
msg["Subject"] = "here we are sending you  an attachment"
msg.preamble = "here we are sending you  an attachment"

ctype, encoding = mimetypes.guess_type(fileToSend)
if ctype is None or encoding is not None:
    ctype = "application/octet-stream"

maintype, subtype = ctype.split("/", 1)

if maintype == "text":
    fp = open(fileToSend)
    # Note: we should handle calculating the charset
    attachment = MIMEText(fp.read(), _subtype=subtype)
    fp.close()
elif maintype == "image":
    fp = open(fileToSend, "rb")
    attachment = MIMEImage(fp.read(), _subtype=subtype)
    fp.close()
elif maintype == "audio":
    fp = open(fileToSend, "rb")
    attachment = MIMEAudio(fp.read(), _subtype=subtype)
    fp.close()
else:
    fp = open(fileToSend, "rb")
    attachment = MIMEBase(maintype, subtype)
    attachment.set_payload(fp.read())
    fp.close()
    encoders.encode_base64(attachment)
attachment.add_header("Content-Disposition", "attachment", filename=fileToSend)
msg.attach(attachment)

server = smtplib.SMTP("smtp.gmail.com:587")
server.starttls()
server.login(username,password)
server.sendmail(emailfrom, emailto, msg.as_string())
server.quit()
"""