Update Rates.pyw

#!/usr/bin/env python

#imports
import datetime
import pandas as pd
import pandas.io.sql
import pymssql
import _mssql
import tkinter as tk

#used to update copyright info with current year
now = datetime.datetime.now()
year = str(now.year)

#connect to the database
conn = pymssql.connect(
    host=r"host",
    user=r"user",
    password="password",
    database="database"
)
cursor = conn.cursor()

#build log in popup for security`
def security():
	global root
	root = tk.Tk()
	background = 'azure2'
	font = "ISOCPEUR 10"
	global en2
	en2 = tk.Entry(root)
	en2.grid(row=1, column=1, sticky="NSEW", padx=2, pady=2)
	en2.focus_set()
	global en3
	en3 = tk.Entry(root)
	en3.grid(row=2, column=1, sticky="NSEW", padx=2, pady=2)
	en3.config(show="*")
	tk.Label(root, text="User", background=background, font = font).grid(row=1)
	tk.Label(root, text="Password", background=background, font = font).grid(row=2)
	tk.Button(root, text='Log in', command=logIn).grid(row=5, column=1)
	root.bind('<Return>', logIn)
	root.bind('<Escape>', close)
	root.title("Log In")
	root.configure(background=background)
	root.mainloop()

#pseudo login to prevent unwanted users from entering and changing rates
def logIn(event=None):
	usernames = []
	passwords = []
	if en2.get() in usernames and en3.get() in passwords:
		main()
		root.destroy()
	else:
		global top
		top = tk.Toplevel(root)
		top.title('Error')
		msg = tk.Message(top, text="Incorrect Username or Password", width=750, font="ISOCPEUR 10")
		msg.grid(row=0, column=1)
		return

#build GUI and assign functions to menu and keys
def main():
	global master
	master = tk.Tk()
	background = 'azure2'
	font = "ISOCPEUR 12 underline"

	#Labels 
	tk.Label(master, text="Quote Number", background=background, font = font).grid(row=0)
	tk.Label(master, text="Purchased Material %", background=background, font = font).grid(row=10)
	tk.Label(master, text="Fabricated Material %", background=background, font = font).grid(row=11)
	tk.Label(master, text="Labor Cost %", background=background, font = font).grid(row=12)
	tk.Label(master, text="Burden Cost %", background=background, font = font).grid(row=13)
	tk.Label(master, text="Service Cost %", background=background, font = font).grid(row=14)

	#Entry boxes
	global e
	e = tk.Entry(master)
	e.grid(row=0, column=1, sticky="NSEW", padx=10, pady=10)
	global e2
	e2 = tk.Entry(master)
	e2.grid(row=10, column=1, sticky="NSEW", padx=2, pady=2)
	global e3
	e3 = tk.Entry(master)
	e3.grid(row=11, column=1, sticky="NSEW", padx=2, pady=2)
	global e4
	e4 = tk.Entry(master)
	e4.grid(row=12, column=1, sticky="NSEW", padx=2, pady=2)
	global e5
	e5 = tk.Entry(master)
	e5.grid(row=13, column=1, sticky="NSEW", padx=2, pady=2)
	global e6
	e6 = tk.Entry(master)
	e6.grid(row=14, column=1, sticky="NSEW", padx=2, pady=2)
	e.focus_set()

	#menubar, keybindings, and configurations
	menubar = tk.Menu(master)
	menubar.add_command(label="Update", command=combinedUpdate, font="ISOCPEUR 10")
	menubar.add_command(label="Recalculate", command=combinedRecalc, font="ISOCPEUR 10")
	menubar.add_command(label="Reset", command=resetFromMenu, font="ISOCPEUR 10")
	menubar.add_command(label="Help", command=helpDoc, font="ISOCPEUR 10")
	menubar.add_command(label="Restart", command=restart, font="ISOCPEUR 10")
	menubar.add_command(label="Quit", command=master.quit, font="ISOCPEUR 10")
	master.bind('<Escape>', close)
	master.bind('<F2>', resetFromMenu)
	master.bind('<Control-r>', combinedRecalc)
	master.bind('<Control-u>', combinedUpdate)			
	master.bind('<Control-h>', helpDoc)
	master.bind('<Control-R>', combinedRecalc)
	master.bind('<Control-U>', combinedUpdate)			
	master.bind('<Control-H>', helpDoc)
	master.bind('<F5>', restart)
	master.config(menu=menubar)
	master.title("Update Quote Rates")
	master.minsize(width=350, height=100)
	master.configure(background=background)

#used to pop up a status then run a function
def combinedUpdate(event=None):
	updateStatus()
	master.after(3000, updateRates)

#popup showing status of update
def updateStatus():
	global top
	top = tk.Toplevel(master)
	top.title('Running')
	msg = tk.Message(top, text="Updating...                                     ", width=750, font="ISOCPEUR 10")
	msg.grid(row=0, column=1)
	master.after(4000, top.destroy)

#function to update rates
def updateRates():
	#make sure the quote number exists in the database
	verifySQL = "SELECT ID FROM QUOTE WHERE ID IN (SELECT ID FROM QUOTE WHERE ID = '" + e.get() + "')"
	v = pandas.io.sql.read_sql(verifySQL, conn)
	D = pd.DataFrame(v)
	if D.empty:
		global top
		top = tk.Toplevel(master)
		top.title('Error')
		msg = tk.Message(top, text="Please Check Your Quote Number", width=750, font="ISOCPEUR 10")
		msg.grid(row=0, column=1)
		return
	#prevent 2 from being calculated as 20 by forcing all percentages to be two digits long
	elif len(e2.get()) != 2 or len(e3.get()) != 2 or len(e4.get()) != 2 or len(e5.get()) != 2 or len(e6.get()) != 2:
			top = tk.Toplevel(master)
			top.title('Error')
			msg = tk.Message(top, text="All Percentages Must Be 2 Digits Long", width=750, font="ISOCPEUR 10")
			msg.grid(row=0, column=1)
			return
	try:
		top = tk.Toplevel(master)
		top.title('Updating')	
		msg = tk.Message(top, text="Rates Successfully Updated         ", width=750, font="ISOCPEUR 10")
		msg.grid(row=0, column=1)
		#read in all lines from quote and store them in a dataframe
		lineSQL = "SELECT LINE_NO FROM CR_QUOTE_LIN_PRICE WHERE QUOTE_ID = '" + e.get() + "'"
		q = pandas.io.sql.read_sql(lineSQL, conn)
		lines = pd.DataFrame(q)
		#loop through the lines and update percentage markups based on user input
		for l in lines:
			try:
				num = str(l)
				updatePercent = """UPDATE CR_QUOTE_LIN_PRICE SET PUR_MATL_PERCENT = """ + e2.get() + """, 
				FAB_MATL_PERCENT = """ + e3.get() + """, LABOR_PERCENT = """ + e4.get() + """, 
				BURDEN_PERCENT = """ + e5.get() + """, SERVICE_PERCENT = """ + e6.get() + """WHERE QUOTE_ID = '""" + e.get() + """' 
				AND LINE_NO = """ + num
				try:
					#commit data to database
					cursor.execute(updatePercent)
					conn.commit()
				except:
					pass
			except:
				pass
	except:
		pass

#used to pop up a status then run a function
def combinedRecalc(event=None):
	recalcStatus()
	master.after(3000, Recalc)

#popup showing status of recalculation
def recalcStatus():
    global top
    top = tk.Toplevel(master)
    top.title('Running')
    msg = tk.Message(top, text="Recalculating...                                ", width=750, font="ISOCPEUR 10")
    msg.grid(row=0, column=1)
    master.after(4000, top.destroy)

#recalculate based on rates entered
def Recalc():
	#make sure the quote number exists in the database 
	verifySQL = "SELECT ID FROM QUOTE WHERE ID IN (SELECT ID FROM QUOTE WHERE ID = '" + e.get() + "')"
	v = pandas.io.sql.read_sql(verifySQL, conn)
	D = pd.DataFrame(v)
	if D.empty:
		global top
		top = tk.Toplevel(master)
		top.title('Error')
		msg = tk.Message(top, text="Please Check Your Quote Number", width=750, font="ISOCPEUR 10")
		msg.grid(row=0, column=1)
		return
	#prevent 2 from being calculated as 20 by forcing all percentages to be two digits long
	if len(e2.get()) != 2 or len(e3.get()) != 2 or len(e4.get()) != 2 or len(e5.get()) != 2 or len(e6.get()) != 2:
		top = tk.Toplevel(master)
		top.title('Error')
		msg = tk.Message(top, text="All Percentages Must Be 2 Digits Long", width=750, font="ISOCPEUR 10")
		msg.grid(row=0, column=1)
		return
	#get line number and markups for quote
	try:
		top = tk.Toplevel(master)
		top.title('Recalculating')
		msg = tk.Message(top, text="Successfully Recalculated         ", width=750, font="ISOCPEUR 10")
		msg.grid(row=0, column=1)
		lineSQL = "SELECT LINE_NO FROM CR_QUOTE_LIN_PRICE WHERE QUOTE_ID = '" + e.get() + "'"
		matSQL = "SELECT PUR_MATL_PERCENT FROM CR_QUOTE_LIN_PRICE WHERE QUOTE_ID = '" + e.get() + "'"
		fabSQL = "SELECT FAB_MATL_PERCENT FROM CR_QUOTE_LIN_PRICE WHERE QUOTE_ID = '" + e.get() + "'"
		labSQL = "SELECT LABOR_PERCENT FROM CR_QUOTE_LIN_PRICE WHERE QUOTE_ID = '" + e.get() + "'"
		burSQL = "SELECT BURDEN_PERCENT FROM CR_QUOTE_LIN_PRICE WHERE QUOTE_ID = '" + e.get() + "'"
		serSQL = "SELECT SERVICE_PERCENT FROM CR_QUOTE_LIN_PRICE WHERE QUOTE_ID = '" + e.get() + "'"
		#read the queries into a dataframe
		matQ = pandas.io.sql.read_sql(matSQL, conn).head(1).values
		fabQ = pandas.io.sql.read_sql(fabSQL, conn).head(1).values
		labQ = pandas.io.sql.read_sql(labSQL, conn).head(1).values
		burQ = pandas.io.sql.read_sql(burSQL, conn).head(1).values
		serQ = pandas.io.sql.read_sql(serSQL, conn).head(1).values
		#remove ugly formatting
		matQ = str(matQ).strip('[.]')
		fabQ = str(fabQ).strip('[.]')
		labQ = str(labQ).strip('[.]')
		burQ = str(burQ).strip('[.]')
		serQ = str(serQ).strip('[.]')
		#put line data into dataframe
		q = pandas.io.sql.read_sql(lineSQL, conn)
		lines = pd.DataFrame(q).values
		#loop through lines and mark up all rates based on inputs
		for l in lines:
			try:
				num = str(l)[1:-1]
				matMarkUpQuery = "SELECT 1." + e2.get() + "*EST_MATERIAL_COST/DESIRED_QTY FROM WORK_ORDER WHERE BASE_ID = '" + e.get() + "' AND LOT_ID = '" + num + "'"
				EPMC = str(pandas.io.sql.read_sql(matMarkUpQuery, conn).values).strip('[.]')
				updateMaterial = float(EPMC)
				roundMaterial = round(updateMaterial)

				#couldn't find a use for this since we only use purchased materials in our quotes

				# fabMarkUpQuery = "SELECT SUM(EST_MATERIAL_COST)/DESIRED_QTY FROM WORK_ORDER WHERE BASE_ID LIKE '%" + e.get() + "%' AND LOT_ID = '" + num + "' GROUP BY LOT_ID"
				# EFMC = str(pandas.io.sql.read_sql(fabMarkUpQuery, conn).head(1).values).strip('[.]')
				# updateFabricated = float('1.' + e.get()) * float(EPMC)
				# roundFabricated = round(updateFabricated)

				labMarkUpQuery = "SELECT 1." + e4.get() + "*EST_LABOR_COST/DESIRED_QTY FROM WORK_ORDER WHERE BASE_ID = '" + e.get() + "' AND LOT_ID = '" + num + "'"
				ELC = str(pandas.io.sql.read_sql(labMarkUpQuery, conn).head(1).values).strip('[.]')
				updateLabor = float(ELC)
				roundLabor = round(updateLabor)

				burMarkUpQuery = "SELECT 1." + e5.get() + "*EST_BURDEN_COST/DESIRED_QTY FROM WORK_ORDER WHERE BASE_ID = '" + e.get() + "' AND LOT_ID = '" + num + "'"
				EBC = str(pandas.io.sql.read_sql(burMarkUpQuery, conn).head(1).values).strip('[.]')
				updateBurden = float(EBC)
				roundBurden = round(updateBurden)

				serMarkUpQuery = "SELECT 1." + e6.get() + "*EST_SERVICE_COST/DESIRED_QTY FROM WORK_ORDER WHERE BASE_ID = '" + e.get() + "' AND LOT_ID = '" + num + "'"
				ESC = str(pandas.io.sql.read_sql(serMarkUpQuery, conn).head(1).values).strip('[.]')
				updateService = float(ESC)
				roundService = round(updateService)

				#calculate markups by adding all costs together, then rounding for neatness
				finalCalc = updateMaterial + updateLabor + updateBurden + updateService
				roundFinalCalc = round(finalCalc)
				strFinalCalc = str(finalCalc)
				strRoundFinalCalc = str(roundFinalCalc)

				#update in quote by replacing costs with those calculated above
				updateCosts = """UPDATE CR_QUOTE_LIN_PRICE SET CALC_UNIT_PRICE = """ + strFinalCalc + """, UNIT_PRICE = """ + strRoundFinalCalc + """ WHERE QUOTE_ID = '""" + e.get() + """' 
		 							AND LINE_NO = """ + num
				print(num, strRoundFinalCalc)
				cursor.execute(updateCosts)
				conn.commit()
			except:
				pass
	except:
		pass

#resets screen 
def resetFromMenu(event=None):
   e.delete(0, 'end')
   e2.delete(0, 'end')
   e3.delete(0, 'end')
   e4.delete(0, 'end')
   e5.delete(0, 'end')
   e6.delete(0, 'end')
   e.focus_set()

#help box popup
def helpDoc(event=None):
	global top
	top = tk.Toplevel(master)
	top.title('Help')
	msg = tk.Message(
					top, 
					text="This program updates the rates and recalculates all lines for a quote in Visual.\
					\n\nTo use:\n\nEnter the exact quote number you wish to update.\nEnter percentages as two digit numbers in each box, in the same order you would in the quote in Visual.\
					\n\nYou should always update before recalculating.\
					\nAfter you enter the correct quote number and the rates you wish to use, click the update button on the menu bar.\
					\nIf the quote number or any rates are incorrect, you will get an error message.\
					\n\nAfter you have successfully updated the rates, do not clear the screen.\nClick the recalculate button.\
					\nThis program will recalculate based on the rates you have entered here, not based on the rates in Visual,\
					so it is important that the rates used to update are the same as the rates used to recalculate.\
					\n\nOnce you have updated and recalculated, you are all done. There is no need to save anything.\
					\nGo into Visual and refresh the quote.\nThe rates and prices will be updated and rounded to the nearest whole dollar.\
					\n\nShortcut keys:\n\nCtrl+R: Recalculate\nCtrl+U: Update\nF2: Reset\nF5: Restart\nEsc: Close Message\nCtrl+H: Help\nAlt+Q: Quit\
					\n\nBuilt by Michael Connell using the python programming language \nSouce code and more documentation can be found at https://github.com/MiConnell/Update-Rates\n©" + year,
					width=750
							)
	msg.pack()

#brings user back to log in
def restart(event=None):
	master.destroy()
	security()

#closes popups
def close(event=None):
    top.destroy()

#starts program
if __name__ == "__main__":
	security()