calendar_script.py

#module pip is required to install some modules
#module cx_freeze nees to be installed to create an exe file
from tkinter import *
from tkinter import ttk
import tkinter.messagebox
import time
import requests #needs to be installed
import pymysql #needs to be installed
import csv
import win32com.client #needs to be installed
import datetime

import ttkcalendar
import calendar



#################################
# Create Button Click Calendar

def callbackCal():
    root2=Toplevel(f2)
    ttkcal = ttkcalendar.Calendar(root2,firstweekday=calendar.SUNDAY)
    ttkcal.pack(expand=1, fill='both')
    root2.update()
    root2.minsize(root2.winfo_reqwidth(), root2.winfo_reqheight())


#calBut=ttk.Button(f2, width=4, text="Cal", command=callbackCal).grid(column=3,row=1, sticky=W)

#########################
#timing function
#this function calculates the time between status changes
#status 'a' is a dummie status used only when the program is used
#for the first time

times=[1]
statuses=['a']
def callback(*args):
    upload=[]
    end = time.time()
    b= (end - start)
    times.append(b)
    c=times[-1]-times[-2]
    statuses.append(status.get())
    d=statuses[-2]
    upload.append(login_entry.get())
    upload.append(d)
    upload.append(c)
    upload.append(time.strftime("%Y-%m-%d %H:%M:%S"))
    result = datetime.datetime.now() - datetime.timedelta(seconds=int(round(c,0)))
    result=str(result)
    result=result[0:19]
    upload.append(result)
    print(result)


    if d != "Offline" and d != "a" and upload[2]<43200:
        conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
        cur = conn.cursor()
        cur.execute("INSERT INTO hours (reviewer,task,num_seconds,day,start_time) VALUES('"+str(upload[0])+"','"+str(upload[1])+"','"+str(upload[2])+"','"+str(upload[3])+"','"+str(upload[4])+"')")
        cur.close()
        conn.close()
        print(upload)

    if upload[2]>=43200 and d != "Offline":

        body="It seems you have been on the same state for over 12 hours. The status was "+str(upload[1])+" and you've been on it since "+str(upload[4])+"and email is being sent to you with this information for you to make the necessary corrections"

        tkinter.messagebox.showerror("such long time...", body)

        Format = { 'UNSPECIFIED' : 0, 'PLAIN' : 1, 'HTML' : 2, 'RTF'  : 3}
        profile = "Outlook"
        #session = win32com.client.Dispatch("Mapi.Session")
        outlook = win32com.client.Dispatch("Outlook.Application")
        #session.Logon(profile)
        mainMsg = outlook.CreateItem(0)
        mainMsg.To = str(login_entry.get())+"@amazon.com"
        mainMsg.Subject = "Over 12 hours on the same status"
        mainMsg.BodyFormat = Format['RTF']
        mainMsg.HTMLBody = body
        mainMsg.Send()  #this line actually sends the email



    writer=csv.writer(open("output.csv",'a',newline=''),delimiter=',')
    writer.writerow(upload)

start = time.time()

#########################
#timing function to close
#this one has to be
#the same as callback function
#with the line "root.destroy()"
#at the end

def in_close(*args):
    callback()
    root.destroy()

#########################
#validate function
#triggered by the validate button

def validate(*args):
    url='https://kdpow.amazon.com/jobs/unassigned'
    print(login_entry.get())
    response = requests.get(url, verify=False, auth=(login_entry.get(),password_entry.get()))
    if response.status_code == 200:

        #personalized messages for when
        #people log in, mostly for trolling purposes
        if login_entry.get() == "rodrigof":
            tkinter.messagebox.showerror("The creator...","The creator has logged in, please prepare the hamsters deployment mode!!")
        if login_entry.get() == "guzmansg":
            tkinter.messagebox.showerror("Hi Sonia!!","Sonia has just logged in...")
        if login_entry.get() == "felipeso":
            tkinter.messagebox.showerror("Hi nigglet!!","Da nigglet has just logged in...")
        if login_entry.get() == "melanyc":
            tkinter.messagebox.showerror("Hi Mela!!","mela! mela! mela! mela! mela! mela! mela! mela! mela! mela! mela! mela! mela! mela! mela! mela! mela! mela! (picture me poking your arm) :)")
        if login_entry.get() == "murillop":
            tkinter.messagebox.showerror("Hi Pame!!!!!!","Pame has just logged in... :)")
        if login_entry.get() == "rcavall":
            tkinter.messagebox.showerror("Hi Ricardo!!!!!!","Ricardo logged in.... LOL")

        #if new managers or more people need to have permission to the team reports
        #add them to this list
        managers=['kellywa','esquivel','ctriunfo','mett','emilyt','soihara','rodrigof','alvaros','adamca','wardwill','aarowa', 'ligiag','meganmul',
                  'mluna', 'dbarrios']

        if login_entry.get() in managers:
            #creates button for manager reports
            manager_report=ttk.Button(f2, width=20, text="Team Productivity", command=manager_prod).grid(column=7, row=3)
            team_status=ttk.Button(f2, width=20, text="Team Status Report", command=team_status_email).grid(column=7, row=2)

        ttk.Button(f1, state=DISABLED, text="Validate", command=validate).grid(column=2, row=3)
        get_rows=ttk.Button(f2, width=20, state=NORMAL, text="Get Rows", command=func2).grid(column=3, row=2)
        password_entry.config(state=DISABLED)
        login_entry.config(state=DISABLED)
        status.trace('w',callback)
        pipe_var.trace('w',create_checks)

    elif response.status_code == 401:
        tkinter.messagebox.showerror("the errorest...", "There seems to be an issue with your login or password. Please try again.")

    else:
        tkinter.messagebox.showerror("the errorest...", "An error has ocurred. Please try again.")

#########################
#skills and add buckets functions
#triggered by the update dropdown button
#they determine which buckets have to be added to the dropdown

#this one is called by the one below and adds the skills to the skills list
#and then the dropdown
def add_buckets(*args):
    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()
    cur.execute("select * from sauron_buckets")
    buckets=[]

    for row in cur:
        buckets.append(row)
    bucket_count=0
    for bucket in buckets:
        if str(workflows[counter][0])==str(buckets[bucket_count][2]):
            skills.append(buckets[bucket_count][1])
            globals().update(locals())
        bucket_count=bucket_count+1

    cur.close()
    conn.close()

#this function uses the one above to create a checkbox for every different workflow
def func11(*args):
    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()
    cur.execute("select distinct workflow from sauron_buckets")

    workflows=[]

    for row in cur:
        workflows.append(row)

    skills=[]
    global counter
    counter=0
    for workf in workflows:

        globals().update(locals())
        exec("if "+str(workflows[counter][0])+"workf.get()==1: add_buckets()")
        counter=counter+1

    drop=ttk.OptionMenu(f1, status, "Offline", *skills).grid(column=5, row=1,sticky=W) #status change dropdown
    drop_row_new=ttk.OptionMenu(f2, new_row, "New Row", *skills).grid(column=1, row=5,sticky=E) #add row dropdown
    add_row=ttk.Button(f2, state=NORMAL, width=20, text="Add new row", command=addrow).grid(column=4, row=5) #add new row button
    globals().update(locals())
    cur.close()
    conn.close()

#########################
#create checkboxes
#triggered by selecting a different radio button

def create_checks(*args):

    #this iteration deletes all the previous
    #query results in the grid (if any)
    for label in f1.grid_slaves():
        if int(label.grid_info()["row"]) > 6:
            label.grid_forget()

    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()
    cur.execute("select distinct workflow, pipeline from sauron_buckets")
    workflows=[]

    for row in cur:
        workflows.append(row)

    #this iteration creates checkboxes
    #depending on which radio button is selected

    count=0
    for workf in workflows:
        if pipe_var.get()==1 and workflows[count][1]=="KDP":
            exec("""ttk.Checkbutton(f1, text='"""+str(workflows[count][0])+"""', variable="""+str(workflows[count][0])+"""workf).grid(column=1, row="""+str(count+7)+""", sticky=W)""")

        if pipe_var.get()==2 and workflows[count][1]=="CSP":
            exec("""ttk.Checkbutton(f1, text='"""+str(workflows[count][0])+"""', variable="""+str(workflows[count][0])+"""workf).grid(column=1, row="""+str(count+7)+""", sticky=W)""")

        if pipe_var.get()==3 and workflows[count][1]=="KW":
            exec("""ttk.Checkbutton(f1, text='"""+str(workflows[count][0])+"""', variable="""+str(workflows[count][0])+"""workf).grid(column=1, row="""+str(count+7)+""", sticky=W)""")
        if pipe_var.get()==4 and workflows[count][1]=="SEED":
            exec("""ttk.Checkbutton(f1, text='"""+str(workflows[count][0])+"""', variable="""+str(workflows[count][0])+"""workf).grid(column=1, row="""+str(count+7)+""", sticky=W)""")
        count=count+1

    cur.close()
    conn.close()

#########################
#time query function
#will run a query to display all the statuses within a given time range
#triggered by the get rows button
#the purpose of giving names to all the variables is because
#in the function called test, the names will be used to make corrections
#to existing rows

corrections=[]
variables=[]
var_label=[]

def func2(*args):


    #this iteration deletes all the previous
    #query results in the grid (if any)
    for label in f2.grid_slaves():
        if int(label.grid_info()["row"]) > 6:
            label.grid_forget()

    #this query will get all the statuses
    #for that time range
    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()
    cur.execute("select id, reviewer, task, round(num_seconds/3600,6),start_time,day \
from hours \
where day between "+"'"+str(start_date.get())+"'"+" and "+"'"+str(end_date.get())+"'"+" \
and reviewer = '"+str(login_entry.get())+"'")

    #This will validate the date and show an error if
    #if date is different
    try:
      valid_start_date = time.strptime(start_date.get(), '%Y-%m-%d')
      valid_end_date = time.strptime(end_date.get(), '%Y-%m-%d')

    except ValueError:
      tkinter.messagebox.showerror("ERROR","Invalid Date Format")
      return


    results=[]

    for row in cur:
        results.append(row)

    #gets pretty tricky from this point on...
    count=0
    varnum=0
    cornum=0
    for esc in results:
        count2=0

        for field in results[count]:

            #here, a text label is created for every field that needs to be populated
            #in the grid, varnum is giving the name as var32 (for example)
            #the variables' names are also being added to variable list
            exec("variables.append('var"+str(varnum)+"')")
            exec("var%d = %s" % (varnum, str(ttk.Label(f2, width=18, relief="solid", text=results[count][count2]).grid(column=count2+1, row=count+7, sticky=E))))
            var_label.append(results[count][count2])

            if count2+1 == 5:
                #for every row in the grid a correction text box
                #will be created, again the name of the correction
                #will be given by row number it's on

                exec("corrections.append('correction"+str(cornum)+"_entry')")
                exec("correction"+str(cornum)+"= StringVar()")
                exec("global correction"+str(cornum)+"_entry")
                exec("correction"+str(cornum)+"_entry = ttk.Entry(f2, width=15, textvariable=correction"+str(cornum)+")")
                exec("correction"+str(cornum)+"_entry.grid(column=count2+3, row=count+7, sticky=(W, E))")

                cornum=cornum+1

            varnum=varnum+1
            count2=count2+1
        count=count+1
    globals().update(locals())

    cur.close()
    conn.close()

#########################
#update row function
#triggered by the make corrections button
#uses the names created in func2 to update rows


def test(*args):

    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()

    lines_to_update=[]
    amount_to_update=[]
    replace_with=[]

    count=0

    for cor in corrections:
        exec("if "+str(corrections[count])+".get()!='': \
amount_to_update.append("+str(var_label[(count*6)+3])+")")

        exec("if "+str(corrections[count])+".get()!='': \
lines_to_update.append("+str(var_label[(count*6)])+")")

        exec("if "+str(corrections[count])+".get()!='': \
replace_with.append("+str(corrections[count])+".get())")

        count=count+1

    count=0

    #this query is iterated through all the
    #lines that are going to updated
    for x in lines_to_update:
        cur.execute("UPDATE hours SET num_seconds = "+str(replace_with[count])+"*3600 WHERE id = '"+str(lines_to_update[count])+"'")
        count=count+1

    #this function is called so that
    #the grid reflects the changes
    func2()

    cur.close()
    conn.close()



#########################
#add new row func
#triggered by the add new row button

def addrow(*args):

    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()
    cur.execute("INSERT INTO hours (task,reviewer,num_seconds,start_time) VALUES('"+str(new_row.get())+"','"+str(login_entry.get())+"',round("+str(time_to_add.get())+"*3600,5),'"+str(date_to_add_entry.get())+"')")

    #this function is called so that
    #the grid reflects the changes
    func2()

    cur.close()
    conn.close()

#########################
#send email with body created by a function

email_subject = "a"

def email_tamplate(*args):
    Format = { 'UNSPECIFIED' : 0, 'PLAIN' : 1, 'HTML' : 2, 'RTF'  : 3}
    profile = "Outlook"
    #session = win32com.client.Dispatch("Mapi.Session")
    outlook = win32com.client.Dispatch("Outlook.Application")
    #session.Logon(profile)
    mainMsg = outlook.CreateItem(0)
    mainMsg.To = str(login_entry.get())+"@amazon.com"
    mainMsg.Subject = email_subject
    mainMsg.BodyFormat = Format['RTF']
    mainMsg.HTMLBody = "Sauron Status Report"


    mainMsg.Attachments.Add(attachment1)
    mainMsg.Attachments.Add(attachment2)


    mainMsg.Send()  #this line actually sends the email

#########################
#send email
#triggered by send status report button
#runs query, formats results with HTML
#and sends it as an email

def status_email(*args):

    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()
    cur.execute("select reviewer, task, round(num_seconds/3600,6) as 'hours',start_time,day \
from hours \
where day between "+"'"+str(start_date.get())+"'"+" and "+"'"+str(end_date.get())+"'"+" \
and reviewer = '"+str(login_entry.get())+"' \
union \
(select '','Total', sum(hours),'','' \
from( \
select reviewer, task, round(num_seconds/3600,6) as 'hours',day from hours \
where day between '"+str(start_date.get())+"' and '"+str(end_date.get())+"' \
and reviewer = '"+str(login_entry.get())+"' \
)a)")


    #This will validate the date and show an error if
    #if date is different
    try:
      valid_start_date = time.strptime(start_date.get(), '%Y-%m-%d')
      valid_end_date = time.strptime(end_date.get(), '%Y-%m-%d')

    except ValueError:
      tkinter.messagebox.showerror("ERROR","Invalid Date Format")
      return

    cur.close()
    conn.close()

    eod = []
    body = ['<html><body><table border="1" style="width:300px"><tr><td>Reviewer</td><td>Skill</td><td>Amount of hours</td><td>Start time</td><td>End time</td></tr>']
    header = [['Reviewer', 'Skill', 'Amount of hours', 'Start time', 'End time']]

    for row in cur:
        eod.append(row)

# Crea el archivo CSV
    import csv
    with open('C:\\Python33\\status_email.csv', 'w', encoding=('UTF-8'), newline='') as fp:
        a = csv.writer(fp, delimiter=',')
        a.writerows(header)
        a.writerows(eod)

    attachment1 = 'C:\\Python33\\status_email.csv'
    email_subject = "Sauron personal status request between "+str(start_date.get())+" and "+str(end_date.get())

    count=0
    count2=0
    for item in eod:
        body[0]=body[0]+"<tr><td>"+str(eod[count2][count])+"</td><td>"+str(eod[count2][count+1])+"</td><td>"+str(eod[count2][count+2])+"</td><td>"+str(eod[count2][count+3])+"</td><td>"+str(eod[count2][count+4])+"</td></tr>"
        count2=count2+1

    body[0]=body[0]+"</table></body></html>"
    body=body[0]
    globals().update(locals())





#EMPIEZA EL TEST DE EL SEGUNDO ATTACHMENT

    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()
    cur.execute("select reviewer, task, round(num_seconds/3600,6) as 'hours',start_time,day \
from hours \
where day between "+"'"+str(start_date.get())+"'"+" and "+"'"+str(end_date.get())+"'"+" \
and reviewer = '"+str(login_entry.get())+"' \
group by task")


    #This will validate the date and show an error if
    #if date is different
    try:
      valid_start_date = time.strptime(start_date.get(), '%Y-%m-%d')
      valid_end_date = time.strptime(end_date.get(), '%Y-%m-%d')

    except ValueError:
      tkinter.messagebox.showerror("ERROR","Invalid Date Format")
      return

    cur.close()
    conn.close()

    eod = []
    body = ['<html><body><table border="1" style="width:300px"><tr><td>Reviewer</td><td>Skill</td><td>Amount of hours</td><td>Start time</td><td>End time</td></tr>']
    header = [['Reviewer', 'Skill', 'Amount of hours', 'Start time', 'End time']]

    for row in cur:
        eod.append(row)

# Crea el archivo CSV
    import csv
    with open('C:\\Python33\\status_email_by_task.csv', 'w', encoding=('UTF-8'), newline='') as fp:
        a = csv.writer(fp, delimiter=',')
        a.writerows(header)
        a.writerows(eod)


    count=0
    count2=0
    for item in eod:
        body[0]=body[0]+"<tr><td>"+str(eod[count2][count])+"</td><td>"+str(eod[count2][count+1])+"</td><td>"+str(eod[count2][count+2])+"</td><td>"+str(eod[count2][count+3])+"</td><td>"+str(eod[count2][count+4])+"</td></tr>"
        count2=count2+1

    attachment2 = 'C:\\Python33\\status_email_by_task.csv'
    #email_subject = "Sauron personal status request between "+str(start_date.get())+" and "+str(end_date.get())

    body[0]=body[0]+"</table></body></html>"
    body=body[0]
    globals().update(locals())


    email_tamplate()


#########################
#send email
#triggered by send report as email button
#runs query, formats results with HTML
#and sends it as an email

def team_status_email(*args):

    #gets logins from table
    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()
    cur.execute("select * from sauron_reviewers")

    general_prod=[]

    for row in cur:
        general_prod.append(row)


    body = ['<html><body><table border="1" style="width:300px"><tr><td>Reviewer</td><td>Skill</td><td>Amount of hours</td><td>Start time</td><td>End time</td></tr>']
    header = [['Reviewer', 'Skill', 'Amount of hours', 'Start time', 'End time']]

    master_list = []


    count3=0
    for reviewer in general_prod:

        conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
        cur = conn.cursor()
        cur.execute("select reviewer, task, round(num_seconds/3600,6),start_time,day \
from hours \
where day between "+"'"+str(start_date.get())+"'"+" and "+"'"+str(end_date.get())+"'"+" \
and reviewer = '"+str(general_prod[count3][1])+"'")


        #This will validate the date and show an error if
        #if date is different
        try:
          valid_start_date = time.strptime(start_date.get(), '%Y-%m-%d')
          valid_end_date = time.strptime(end_date.get(), '%Y-%m-%d')

        except ValueError:
          tkinter.messagebox.showerror("ERROR","Invalid Date Format")
          return


        eod = []

        for row in cur:
            eod.append(row)


        count=0
        for item in eod:
            master_list.append(eod[count])
            count = count+1


        count=0
        count2=0
        for item in eod:
            body[0]=body[0]+"<tr><td>"+str(eod[count2][count])+"</td><td>"+str(eod[count2][count+1])+"</td><td>"+str(eod[count2][count+2])+"</td><td>"+str(eod[count2][count+3])+"</td><td>"+str(eod[count2][count+4])+"</td></tr>"
            count2=count2+1
        count3=count3+1


    # Crea el archivo CSV
    import csv
    with open('C:\\Python33\\status_email.csv', 'w', encoding=('UTF-8'), newline='') as fp:
        a = csv.writer(fp, delimiter=',')
        a.writerows(header)
        a.writerows(master_list)

    attachment1 = 'C:\\Python33\\status_email.csv'
    email_subject = "Sauron Team Status Report between "+str(start_date.get())+" and "+str(end_date.get())


    body[0]=body[0]+"</table></body></html>"
    body=body[0]

    globals().update(locals())
    cur.close()
    conn.close()


#EMPIEZA EL TEST DE EL SEGUNDO ATTACHMENT

    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()
    cur.execute("select * from sauron_reviewers")

    general_prod=[]

    for row in cur:
        general_prod.append(row)


    body = ['<html><body><table border="1" style="width:300px"><tr><td>Reviewer</td><td>Skill</td><td>Amount of hours</td><td>Start time</td><td>End time</td></tr>']
    header = [['Reviewer', 'Skill', 'Amount of hours', 'Start time']]

    master_list = []


    count3=0
    for reviewer in general_prod:

        conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
        cur = conn.cursor()
        cur.execute("select reviewer, task, sum(num_seconds/3600) as total_hours,day from timetrackdb.hours \
where day between "+"'"+str(start_date.get())+"'"+" and "+"'"+str(end_date.get())+"'"+" \
and reviewer = '"+str(general_prod[count3][1])+"' \
group by task")


        #This will validate the date and show an error if
        #if date is different
        try:
          valid_start_date = time.strptime(start_date.get(), '%Y-%m-%d')
          valid_end_date = time.strptime(end_date.get(), '%Y-%m-%d')

        except ValueError:
          tkinter.messagebox.showerror("ERROR","Invalid Date Format")
          return


        eod = []

        for row in cur:
            eod.append(row)


        count=0
        for item in eod:
            master_list.append(eod[count])
            count = count+1


        count=0
        count2=0
        for item in eod:
            body[0]=body[0]+"<tr><td>"+str(eod[count2][count])+"</td><td>"+str(eod[count2][count+1])+"</td><td>"+str(eod[count2][count+2])+"</td><td>"+str(eod[count2][count+3])+"</td><td>"
            count2=count2+1
        count3=count3+1


    # Crea el archivo CSV
    import csv
    with open('C:\\Python33\\status_email_by_task.csv', 'w', encoding=('UTF-8'), newline='') as fp:
        a = csv.writer(fp, delimiter=',')
        a.writerows(header)
        a.writerows(master_list)

    attachment2 = 'C:\\Python33\\status_email_by_task.csv'
    #email_subject = "Sauron Team Status Report between "+str(start_date.get())+" and "+str(end_date.get())


    body[0]=body[0]+"</table></body></html>"
    body=body[0]
    globals().update(locals())

    cur.close()
    conn.close()



    email_tamplate()


#########################
#L1/AR productivity
#triggered by send productivity report email
#sends email with productivity for l1 and ar



def prod_email(*args):

    #runs query to get l1 titles and puts the number
    #in a list
    conn = pymysql.connect(host='contentreviewprod01-ro.ctpwyptleymd.us-east-1.rds.amazonaws.com', port=8443, user='crm-ro', passwd='ofS5i7MDP56e', db='ContentReviewProd01', autocommit = True)
    cur = conn.cursor()
    cur.execute("select count(*) from decisions \
where reviewer = '"+str(login_entry.get())+"' \
and reason <> 'OC Review Pass' \
and reason <> 'Content_Appropriateness' \
and created_at between '"+str(start_date.get())+"' and '"+str(end_date.get())+"'")

    l1_titles=[]

    for row in cur:
        l1_titles.append(row)

    l1_titles=l1_titles[0][0]

    #runs query to get ar titles and puts the number
    #in a list
    cur.execute("select count(*) from decisions \
where reviewer = '"+str(login_entry.get())+"' \
and (reason = 'OC Review Pass' \
or reason = 'Content_Appropriateness') \
and created_at between '"+str(start_date.get())+"' and '"+str(end_date.get())+"'")

    ar_titles=[]

    for row in cur:
        ar_titles.append(row)

    ar_titles=ar_titles[0][0]

    #runs query to get amount of hours for every skill
    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()

    cur.execute("select (sum(num_seconds)/3600) as total_hours, task, reviewer \
from hours \
where reviewer = '"+str(login_entry.get())+"' \
and day between '"+str(start_date.get())+"' and '"+str(end_date.get())+"' \
group by task")

    hours=[]

    for row in cur:
        hours.append(row)

    #it starts searching for the amount of
    #hours that say l1 or AR, since the query
    #results are in a list, it searches every
    #item of the list, if not there, it returns
    # no hours for that sklill
    try:
        find_ar="a"
        count=0
        while find_ar != "CA Review":
            if hours[count][1]=="CA Review":
                find_ar="CA Review"
                ar_hours=hours[count][0]
            else:
                count=count+1
    except:
        ar_hours="No AR Hours"
        find_ar="CA Review"

    try:
        find_l1="a"
        count=0
        while find_l1 != "L1 Review":
            if hours[count][1]=="L1 Review":
                find_l1="L1 Review"
                l1_hours=hours[count][0]
            else:
                count=count+1
    except:
        l1_hours="No L1 Hours"
        find_l1="L1 Review"

    try:
        l1_prod=l1_titles/l1_hours
    except:
        l1_prod= "No L1 hours"

    try:
        ar_prod=ar_titles/ar_hours
    except:
        ar_prod= "No AR hours"

    #gives HTML format to the results
    body = '<html><body><table border="1" style="width:300px"><tr><td>Reviewer</td><td>Titles</td><td>Skill</td><td>TPH</td></tr>'
    body=body+"<tr><td>"+str(login_entry.get())+"</td><td>"+str(l1_titles)+"</td><td>"+str(find_l1)+"</td><td>"+str(l1_prod)+"</td></tr>"
    body=body+"<tr><td>"+str(login_entry.get())+"</td><td>"+str(ar_titles)+"</td><td>"+str(find_ar)+"</td><td>"+str(ar_prod)+"</td></tr>"

    globals().update(locals())
    email_tamplate()

    cur.close()
    conn.close()

    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)

#########################
#manager report
#triggered by team report button
#does exactly the same thing that
#prod_email, but inside an iteration
#in where the user's login is replaced by
#all the logins in the reviewers table
#please see comments on prod_email

def manager_prod(*args):

    #gets logins from table
    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
    cur = conn.cursor()
    cur.execute("select * from sauron_reviewers")

    general_prod=[]

    for row in cur:
        general_prod.append(row)

    body = '<html><body><table border="1" style="width:300px"><tr><td>Reviewer</td><td>Titles</td><td>Skill</td><td>TPH</td></tr>'
    count2=0

    for reviewer in general_prod:

        conn = pymysql.connect(host='contentreviewprod01-ro.ctpwyptleymd.us-east-1.rds.amazonaws.com', port=8443, user='crm-ro', passwd='ofS5i7MDP56e', db='ContentReviewProd01', autocommit = True)
        cur = conn.cursor()
        cur.execute("select count(*) from decisions \
where reviewer = '"+general_prod[count2][1]+"' \
and reason <> 'OC Review Pass' \
and reason <> 'Content_Appropriateness' \
and created_at between '"+str(start_date.get())+"' and '"+str(end_date.get())+"'")

        l1_titles=[]

        for row in cur:
            l1_titles.append(row)

        l1_titles=l1_titles[0][0]

        cur.execute("select count(*) from decisions \
where reviewer = '"+general_prod[count2][1]+"' \
and (reason = 'OC Review Pass' \
or reason = 'Content_Appropriateness') \
and created_at between '"+str(start_date.get())+"' and '"+str(end_date.get())+"'")

        ar_titles=[]

        for row in cur:
            ar_titles.append(row)

        ar_titles=ar_titles[0][0]

        conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
        cur = conn.cursor()

        cur.execute("select (sum(num_seconds)/3600) as total_hours, task, reviewer \
from hours \
where reviewer = '"+general_prod[count2][1]+"' \
and day between '"+str(start_date.get())+"' and '"+str(end_date.get())+"' \
group by task")

        hours=[]

        for row in cur:
            hours.append(row)


        try:
            find_ar="a"
            count=0
            while find_ar != "CA Review":
                if hours[count][1]=="CA Review":
                    find_ar="CA Review"
                    ar_hours=hours[count][0]
                else:
                    count=count+1
        except:
            ar_hours="No AR Hours"
            find_ar="CA Review"

        try:
            find_l1="a"
            count=0
            while find_l1 != "L1 Review":
                if hours[count][1]=="L1 Review":
                    find_l1="L1 Review"
                    l1_hours=hours[count][0]
                else:
                    count=count+1
        except:
            l1_hours="No L1 Hours"
            find_l1="L1 Review"

        try:
            l1_prod=l1_titles/l1_hours
        except:
            l1_prod= "No L1 hours"

        try:
            ar_prod=ar_titles/ar_hours
        except:
            ar_prod= "No AR hours"

        body=body+"<tr><td>"+general_prod[count2][1]+"</td><td>"+str(l1_titles)+"</td><td>"+str(find_l1)+"</td><td>"+str(l1_prod)+"</td></tr>"
        body=body+"<tr><td>"+general_prod[count2][1]+"</td><td>"+str(ar_titles)+"</td><td>"+str(find_ar)+"</td><td>"+str(ar_prod)+"</td></tr>"

        count2=count2+1

    globals().update(locals())
    email_tamplate()

    cur.close()
    conn.close()

    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)

#########################
#nominations

def nominations(*args):


    conn = pymysql.connect(host='crapdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='dc_nom', passwd='nomNOMn0m', db='crapdb_read', autocommit = True)
    cur = conn.cursor()

    a=(manual_entry_var.get()).replace(" ","")

    if input_var.get()=="DIID":
        cur.execute("INSERT INTO crapdb_read.dc_nominations (crap_type,originator,date,digital_item_id,source) \
VALUES ('"+str(crap_type_var.get())+"','"+str(login_entry.get())+"','"+str(time.strftime("%Y-%m-%d %H:%M:%S"))+"','"+str(a)+"','"+str(crap_source_var.get())+"')")

    if input_var.get()=="ASIN":
        cur.execute("INSERT INTO crapdb_read.dc_nominations (crap_type,originator,date,asin,source) \
VALUES ('"+str(crap_type_var.get())+"','"+str(login_entry.get())+"','"+str(time.strftime("%Y-%m-%d %H:%M:%S"))+"','"+str(a)+"','"+str(crap_source_var.get())+"')")

    cur.close()
    conn.close()

    conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)

#########################
#########################
#########################
#########################
#main frame
root = Tk()
root.title("Sauron v3")
mainframe = ttk.Frame(root)
mainframe = ttk.Frame(root, padding="3 3 12 12")

mainframe.columnconfigure(0, weight=1)
mainframe.rowconfigure(0, weight=1)

mainframe.grid(column=0, row=0, sticky=(N, W, E, S))
mainframe.columnconfigure(0, weight=1)
mainframe.rowconfigure(0, weight=1)

#calls in_close function when closing the program
root.protocol('WM_DELETE_WINDOW', in_close)

#########################
#tabs

nb = ttk.Notebook(root)
#nb.pack(fill='both', expand='yes')
nb.grid(column=0, row=0, sticky=(W, E))

f1 = ttk.Frame()
f2 = ttk.Frame()
f3 = ttk.Frame()

nb.add(f1, text='Tracker')
nb.add(f2, text='Corrections')
nb.add(f3, text='CRAP Nominations')



#########################
#login box and text

login = StringVar()
login_entry = ttk.Entry(f1, width=15, textvariable=login)
login_entry.grid(column=2, row=1, sticky=(W, E))
ttk.Label(f1, text="Login: ").grid(column=1, row=1, sticky=E)
login_entry.focus()

#########################
#password box and text

password = StringVar()
password_entry = ttk.Entry(f1, show="*", width=15, textvariable=password) #show is for showing * instead of the pass
password_entry.grid(column=2, row=2, sticky=(W, E))
ttk.Label(f1, text="Password: ").grid(column=1, row=2, sticky=E)

#########################
#dropdown and skills

status=StringVar()

#########################
#login button

ttk.Button(f1, text="Validate", command=validate).grid(column=2, row=3)
root.bind('<Return>', validate)


#########################
#radio buttons
ttk.Label(f1, text="").grid(column=1, row=4, sticky=W)

pipe_var=IntVar()

pipeline1=ttk.Radiobutton(f1, value=1, text="KDP", variable=pipe_var).grid(column=1, row=5, sticky=W)
pipeline2=ttk.Radiobutton(f1, value=2, text="CSP", variable=pipe_var).grid(column=2, row=5, sticky=W)
pipeline3=ttk.Radiobutton(f1, value=3, text="KW", variable=pipe_var).grid(column=3, row=5, sticky=W)
pipeline4=ttk.Radiobutton(f1, value=4, text="SEED", variable=pipe_var).grid(column=4, row=5, sticky=E)

ttk.Label(f1, text="Skills: ", width=15).grid(column=1, row=6, sticky=W)

#########################
#update dropdown

ttk.Button(f1, text="Update dropdown!!", command=func11).grid(column=4, row=3)

#########################
#this part creates a variable for every skill
#in the entire table even if it won't be used
#to avoid name calling errors when creating
#the checkboxes
conn = pymysql.connect(host='kdpcrmdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='timetracker_rw', passwd='B!gBr0therI$W@tchingU', db='timetrackdb', autocommit = True)
cur = conn.cursor()
cur.execute("select distinct workflow from sauron_buckets")

workflows=[]

for row in cur:
    workflows.append(row)

count=0
for workf in workflows:
    exec(str(workflows[count][0])+"workf=IntVar()")
    count=count+1

#########################
#########################
#########################
#########################
#corrections table - time range fields

start_date = StringVar()
start_date = ttk.Entry(f2, width=15, textvariable=start_date)
start_date.grid(column=2, row=1, sticky=E)

ttk.Label(f2, text="Start date:", width=10).grid(column=1, row=1, sticky=E)

#ttk.Label(f2, text="Use: 'yyyy-mm-dd'").grid(column=3, row=1, sticky=W)

end_date = StringVar()
end_date = ttk.Entry(f2, width=15, textvariable=end_date)
end_date.grid(column=2, row=2, sticky=E)

ttk.Label(f2, text="End date:", width=10).grid(column=1, row=2, sticky=E)

ttk.Label(f2, text="").grid(column=1, row=3, sticky=E)

#########################
#corrections table - time range fields

ttk.Label(f2, text="").grid(column=1, row=5, sticky=E)

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

# Calendar Buttons

b=ttk.Button(f2, width=4, text="Cal", command=callbackCal).grid(column=3,row=1, sticky=W)

#calBut=ttk.Button(f2, width=4, text="Cal", command=callbackCal).grid(column=3,row=1, sticky=W)
#calBut=ttk.Button(f2, width=4, text="Cal", command=func2).grid(column=3,row=2, sticky=W)

#corrections table - time range query button

get_rows=ttk.Button(f2, width=20, state=DISABLED, text="Get rows", command=func2).grid(column=3, row=3)

#########################
#corrections table - corrections and add row

new_row=StringVar()

ttk.Label(f2, text="Amount of time").grid(column=2, row=4, sticky=W)

time_to_add = StringVar()
time_to_add_entry = ttk.Entry(f2, width=15, textvariable=time_to_add)
time_to_add_entry.grid(column=2, row=5, sticky=E)

ttk.Label(f2, text="Date to add").grid(column=3, row=4, sticky=W)

date_to_add = StringVar()
date_to_add_entry = ttk.Entry(f2, width=15, textvariable=date_to_add)
date_to_add_entry.grid(column=3, row=5, sticky=W)

add_row=ttk.Button(f2, state=DISABLED, width=20, text="Add new row", command=addrow).grid(column=4, row=5)

get_rows=ttk.Button(f2, width=20, text="Make Corrections!!", command=test).grid(column=7, row=6)

#########################
#corrections table - email report button

email_report=ttk.Button(f2, width=20, text="Send Status Report", command=status_email).grid(column=7, row=5)

#########################
#corrections table - email report button

email_prod=ttk.Button(f2, width=20, text="Send Productivity Report", command=prod_email).grid(column=7, row=4)


#########################
#########################
#########################
#crap tab

manual_entry_var = StringVar()
manual_entry = ttk.Entry(f3, width=15, textvariable=manual_entry_var)
manual_entry.grid(column=1, row=2, sticky=E)

inputs=["DIID","ASIN"]
input_var=StringVar()
input_number=ttk.OptionMenu(f3, input_var, "DIID or ASIN", *inputs).grid(column=1, row=3,sticky=W)

sources=['KDP','customer escalation']
crap_source_var=StringVar()
crap_source=ttk.OptionMenu(f3, crap_source_var, "Source", *sources).grid(column=2, row=2,sticky=W)

types=[]

conn = pymysql.connect(host='crapdb.chwk8ha45wbf.us-west-2.rds.amazonaws.com', port=3306, user='dc_nom', passwd='nomNOMn0m', db='crapdb_read', autocommit = True)
cur = conn.cursor()
cur.execute("select * from lookup_reasons where workflow_name='crap'")

for row in cur:
    types.append(row)

types2=[]
count=0
for item in types:
    types2.append(types[count][2])
    count=count+1

crap_type_var=StringVar()
crap_type=ttk.OptionMenu(f3, crap_type_var, "Type", *types2).grid(column=3, row=2,sticky=W)

submit_nomination=ttk.Button(f3, width=20, text="submit", command=nominations).grid(column=4, row=2)

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

root.mainloop()