Skip to content

Instantly share code, notes, and snippets.

/calendar_script.py
Created Sep 16, 2014

Embed
What would you like to do?
#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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.