def UpdateClientAttributes(connector, table, conditionCategory, condition):
c = connector.cursor()
data = (table, conditionCategory, condition)
if conditionCategory in ('ClientName', 'Email'): # force ID lookup
select_query = """SELECT Client_ID FROM %s WHERE %s LIKE '%s'""" % (
data[0], data[1], "%" + str(data[2]) + "%")
c.execute(select_query, )
result = c.fetchall()
for i in result:
for j in i:
if j >= 1:
condition = j
conditionCategory = 'Client_ID'
data = (table, conditionCategory, condition)
select_query = """SELECT * FROM %s WHERE %s = %s""" % (data[0], data[1],
str(data[2]))
c.execute(select_query, )
printResultTable(c)
userChoice = getWholeNumberChoice(update_client_options)
while userChoice != 0:
update_value = ''
column = ''
if userChoice == 1: # name
update_value = input(clientInName)
column = 'ClientName'
elif userChoice == 2: # address
update_value = input(addressInMessage)
column = 'Address'
elif userChoice == 3: # city
update_value = input(cityInMessage)
column = 'City'
elif userChoice == 4: # state
update_value = input(stateInMessage)
if len(update_value) != 2 or re.search('[0-9]', update_value):
print('Error: State Values are accepted in format: \'CA\'')
else:
column = 'State'
elif userChoice == 5: # zip
update_value = input(zipInMessage)
if len(update_value) != 5 or re.search('[a-zA-Z]', update_value):
print("Error: zip code is 5 integers ex: 55555...\n")
else:
column = 'Zip'
elif userChoice == 6: # email
update_value = input(emailInMessage)
column = 'Email'
elif userChoice == 7: # phone
update_value = input(phoneInMessage)
column = 'Phone'
str_column = str(column)
if column != '':
query = """UPDATE %s SET %s = '%s' WHERE (%s = '%s')""" % (
table, str_column, update_value, conditionCategory, condition)
c.execute(query, )
connector.commit()
c.execute(select_query, )
printResultTable(c)
userChoice = getWholeNumberChoice(update_client_options)
return 0
def UpdateUser(connector):
userChoice = getWholeNumberChoice(update_user_search_options)
curr_table_name = 'Users'
while int(userChoice) != 0:
categoryCondition = ''
searched_value = ''
if userChoice == 1: # ID
searched_value = getWholeNumberChoice(userIDPrompt)
categoryCondition = 'User_ID'
elif userChoice == 2: # name
searched_value = input("enter user name...\n")
categoryCondition = 'Name'
elif userChoice == 3: # email
searched_value = input("enter user email...\n")
categoryCondition = 'Email'
if categoryCondition in 'User_ID' and RecordExistsOneCondition(
connector, curr_table_name, str(categoryCondition),
searched_value):
# connector, table, conditionCategory, condition
userChoice = UpdateUserAttributes(connector, curr_table_name,
categoryCondition,
searched_value)
elif categoryCondition in ('Name', 'Email') and RecordExistsLike(
connector, curr_table_name, str(categoryCondition),
searched_value):
userChoice = UpdateUserAttributes(connector, curr_table_name,
categoryCondition,
searched_value)
else:
userChoice = getWholeNumberChoice(update_user_search_options)
return
def GetJobRecord(connector):
userChoice = getWholeNumberChoice(update_job_search_options)
curr_table_name = 'Job'
while int(userChoice) != 0:
categoryCondition = ''
searched_value = ''
if userChoice == 1: # ID
searched_value = getWholeNumberChoice(jobIDPrompt)
categoryCondition = 'Job_ID'
elif userChoice == 2: # name
searched_value = input(clientIDprompt)
categoryCondition = 'Client_ID'
elif userChoice == 3: # Date
searched_value = getDate()
# searched_value = searched_value
categoryCondition = 'Last_active'
if categoryCondition in ('Job_ID',
'Client_ID') and RecordExistsOneCondition(
connector, curr_table_name,
str(categoryCondition), searched_value):
# connector, table, conditionCategory, condition
userChoice = UpdateJobAttributes(connector, curr_table_name,
categoryCondition, searched_value)
elif categoryCondition in 'Last_active' and RecordExistsDate(
connector, curr_table_name, str(categoryCondition),
searched_value):
userChoice = UpdateJobAttributes(connector, curr_table_name,
categoryCondition, searched_value)
else:
userChoice = getWholeNumberChoice(update_job_search_options)
return
def GetContactRecord(connector):
userChoice = getWholeNumberChoice(update_contact_search_options)
curr_table_name = 'Contacts'
while int(userChoice) != 0:
categoryCondition = ''
searched_value = ''
if userChoice == 1: # ID
searched_value = getWholeNumberChoice(contactIDprompt)
categoryCondition = 'Contact_ID'
elif userChoice == 2: # name
searched_value = input(contactInName)
categoryCondition = 'ContactName'
elif userChoice == 3: # email
searched_value = input(contactInEmail)
categoryCondition = 'Email'
if categoryCondition in 'Contact_ID' and RecordExistsOneCondition(
connector, curr_table_name, str(categoryCondition),
searched_value):
# connector, table, conditionCategory, condition
userChoice = UpdateContactAttributes(connector, curr_table_name,
categoryCondition,
searched_value)
elif categoryCondition in ('ContactName',
'Email') and RecordExistsLike(
connector, curr_table_name,
str(categoryCondition), searched_value):
userChoice = UpdateContactAttributes(connector, curr_table_name,
categoryCondition,
searched_value)
else:
userChoice = getWholeNumberChoice(update_contact_search_options)
return
def getDay(year, month):
last_day = int(calendar.monthrange(year, month)[1])
dayError = 'Please enter in range 1-%s', last_day
day = getWholeNumberChoice(dayMessage)
while day <= 0 or day > last_day:
print(dayError)
day = getWholeNumberChoice(dayMessage)
return day
def getStatusID():
statuses = [
'Lead', 'Client has made contact with Company regarding job',
'Accepted', 'Terms have been negotiated and contract signed',
'Declined', 'Negotiations did not succeed - job not accepted',
'Cancelled', 'Job has been permanently cancelled', 'Postponed',
'Job has been postponed due to some type of emergency', 'Finished',
'Job has been completed'
]
i = 0
phrase = ''
full_phrase = ''
count = 0
for status in statuses:
if i >= 2 and (i % 2) == 0:
if count == 0:
full_phrase = "Enter..\n"
count += 1
full_phrase += str(count) + ": " + phrase + "\n"
phrase = ''
phrase += status + ' '
if (i % 2) == 0:
phrase += '| '
i += 1
userChoice = 0
full_phrase = full_phrase[:-2]
full_phrase += '...\n'
while not 1 <= userChoice <= 5:
userChoice = getWholeNumberChoice(full_phrase)
return userChoice
def GetUserIDbyID(connector):
ID = getWholeNumberChoice(userIDPrompt)
table = 'Users'
conditionCategory = 'User_ID'
if RecordExistsOneCondition(connector, table, conditionCategory, ID):
return ID
else:
return 0
def GetContactIDbyID(connector):
ID = getWholeNumberChoice(contactIDprompt)
table = 'Contacts'
conditionCategory = 'Contact_ID'
if RecordExistsOneCondition(connector, table, conditionCategory, ID):
return ID
else:
return 0
def UpdateContactAttributes(connector, table, conditionCategory, condition):
c = connector.cursor()
data = (table, conditionCategory, condition)
if conditionCategory in ('ContactName', 'Email'): # force ID lookup
select_query = """SELECT Contact_ID FROM %s WHERE %s LIKE '%s'""" % (
data[0], data[1], "%" + str(data[2]) + "%")
c.execute(select_query, )
result = c.fetchall()
for i in result:
for j in i:
if j >= 1:
condition = j
conditionCategory = 'Contact_ID'
data = (table, conditionCategory, condition)
select_query = """SELECT * FROM %s WHERE %s = %s""" % (data[0], data[1],
str(data[2]))
c.execute(select_query, )
printResultTable(c)
userChoice = getWholeNumberChoice(update_contact_options)
while userChoice != 0:
update_value = ''
column = ''
if userChoice == 1: # name
update_value = input(contactInName)
column = 'ContactName'
elif userChoice == 2: # url
update_value = input(contactInURL)
column = 'URL'
elif userChoice == 3: # email
update_value = input(emailInMessage)
column = 'Email'
elif userChoice == 4: # phone
update_value = input(phoneInMessage)
column = 'Phone'
str_column = str(column)
if column != '':
query = """UPDATE %s SET %s = '%s' WHERE (%s = '%s')""" % (
table, str_column, update_value, conditionCategory, condition)
c.execute(query, )
connector.commit()
c.execute(select_query, )
printResultTable(c)
userChoice = getWholeNumberChoice(update_contact_options)
return 0
def addNewClientJobToDB(connection, clientID):
c = connection.cursor()
Estimate = getUserNumFloat(estimatePrompt)
Payout = getUserNumFloat(payoutPrompt)
Hours = getWholeNumberChoice(hoursPrompt)
date = datetime.now()
# "AddJob(IN ClientIDIn int, IN EstimateIn float, IN PayoutIn float, IN HoursIN float,IN DateIN datetime)"
# print(Mats)
# print(adds)
args = (clientID, Estimate, Payout, Hours, date)
# print('inserted %s', ''.join(str(args)))
c.callproc('AddJob', args)
return clientID
def addUser(
cursor
): #TODO: ORI - you need to check your inputs and validate user input data. I set num_roles up to make sure that \
# roles are accurate incase more roles are added. The method below shows the workflow
name = input(userInName)
num_roles = getRoles(cursor)
role = 0
while (role <= 0) or (role > num_roles):
role = getWholeNumberChoice(userRoleIDIn)
address = input(addressInMessage)
city = input(cityInMessage)
state = input(stateInMessage)
# doesn't check for non-numeric/alphabetical characters yet
while len(state) > 2 or len(state) < 2 or re.search('[0-9]', state):
state = input(stateInMessage)
# zip length error check
zipCode = input(zipInMessage)
while len(zipCode) > 5 or len(zipCode) < 5 or re.search(
'[a-zA-Z]', zipCode):
zipCode = input(zipInMessage)
phone = input(phoneInMessage)
email = input(emailInMessage)
args = [name, role, address, city, state, zipCode, phone, email]
cursor.callproc('CreateUser', args)
print('User created, returning to Menu...')
cursor.execute("select User_ID from Users ORDER BY User_ID DESC LIMIT 1"
) # get last user
lastUserIDList = cursor.fetchall() # returns a list with a tuple inside
lastUserID = 0
for value in lastUserIDList: # iterate the list
for num in value: # iterate the tuple
if num > 0:
lastUserID = num
break
elif num is None:
print('User does not exist')
else:
print('something happened')
return lastUserID
def addExistingClientJobToDB(connection):
c = connection.cursor()
clientID = getWholeNumberChoice(clientIDprompt)
while not RecordExistsOneCondition(connection, 'Client', 'Client_ID', clientID):
print(dne_error_enter)
clientID = getWholeNumberChoice(clientIDprompt)
Estimate = getUserNumFloat(estimatePrompt)
Payout = getUserNumFloat(payoutPrompt)
Hours = getWholeNumberChoice(hoursPrompt)
date = datetime.now()
# "AddJob(IN ClientIDIn int, IN EstimateIn float, IN PayoutIn float, IN HoursIN float,IN DateIN datetime)"
# print(Mats)
# print(adds)
args = (clientID, Estimate, Payout, Hours, date)
# TODO: LINK Subcontractors and SalesPeople
sub_id = getWholeNumberChoice('Enter Sub-Contractor Contact ID:\n')
while not RecordExistsOneCondition(connection, 'Contacts', 'Contact_ID', sub_id):
print("Invalid ID")
sub_id = getWholeNumberChoice('Enter Sub-Contractor Contact ID:\n')
sales_id = getWholeNumberChoice('Enter Sales ID:\n')
while not RecordExistsTwoCondition(connection, 'Users', 'User_ID', sales_id, 'Role_ID', 1):
print("Invalid ID")
sales_id = getWholeNumberChoice('Enter Sales ID:\n')
c.callproc('AddJob', args)
query = """Select Job_ID from Job Order By Job_ID DESC LIMIT 1"""
last_job_id = ExtractSingleSelectValue(c, query)
args_sub = (last_job_id, sub_id)
args_sales = (last_job_id, sales_id)
c.callproc('CreateJobSubDetails', args_sub)
c.callproc('CreateJobSalesDetails', args_sales)
CalculateNumJobsForClient(connection, clientID)
return clientID
def getMonth():
month = getWholeNumberChoice(monthMessage)
while month <= 0 or month > 12:
print(monthError)
month = getWholeNumberChoice(monthMessage)
return month
def getYear():
year = getWholeNumberChoice(yearMessage)
while year <= MINYEAR or year >= MAXYEAR:
print(yearError)
year = getWholeNumberChoice(monthMessage)
return year
# main
# TODO: (OPTIONAL FEATURES before class due date): More Verbose UI, Login Input Information, LOGGING SYSTEM
start_time = time.time()
connection = mysql.connector.connect(host='35.247.37.38',
database='ContractorManagementDB',
user='******',
password='******')
mycursor = connection.cursor()
userChoice = -1
while userChoice != 1 and userChoice != 2:
userChoice = getWholeNumberChoice('input mode:\n 1: fresh, 2: continual...\n ')
if userChoice == 1: # fresh DB must have at least one record
num_tuples = -1
while num_tuples <= 1:
num_tuples = getWholeNumberChoice('Enter the amount of clients and jobs: Ex: 9\n')
ResetDBToRandVals(connection, num_tuples)
# printAnyFullTable(mycursor, tableNamesAddOrder[4])
else:
while userChoice != 0:
userChoice = getWholeNumberChoice(main_output_message)
if userChoice == 1: # display options - works for now - need to add if deleted dont show
userChoice = getWholeNumberChoice((DisplayTableMessage(table_names_drop_order)))
if not userChoice > len(table_names_drop_order):
printAnyFullTable(mycursor, table_names_drop_order[userChoice - 1])
def UpdateJobAttributes(connector, table, conditionCategory, condition):
c = connector.cursor()
select_query = ''
data = (table, conditionCategory, condition)
if conditionCategory in 'Last_active': # force ID lookup
start_date = data[2].strftime('%Y-%m-%d %H:%M:%S')
end_date = (data[2] + timedelta(days=1)).strftime('%Y-%m-%d %H:%M:%S')
# SELECT * FROM Job WHERE (Last_active >= '2020-03-23 00:00:00') AND (Last_active <= '2020-03-24 00:00:00');
select_query = """SELECT Job_ID FROM %s WHERE (%s >= '%s') AND (%s <= '%s')""" % (
data[0], data[1], start_date, data[1], end_date)
c.execute(select_query, )
result = c.fetchall()
for i in result:
for j in i:
if j >= 1:
condition = j
conditionCategory = 'Job_ID'
if conditionCategory in ('Client_ID'): # force ID lookup
select_query = """SELECT Job_ID FROM %s WHERE %s = %s""" % (
data[0], data[1], data[2])
c.execute(select_query, )
result = c.fetchall()
for i in result:
for j in i:
if j >= 1:
condition = j
conditionCategory = 'Job_ID'
table_two = 'JobCost'
table_three = 'JobStatus'
conditionCategory_two = 'Job.Job_ID'
conditionCategory_three = 'JobCost.Job_ID'
conditionCategory_four = 'JobStatus.Job_ID'
displayItems = (conditionCategory_three, 'Last_active', 'Status_ID',
'Job.Estimate', 'Payout', 'Hours', 'MaterialsCost',
'Additions')
# select * from Job, JobCost WHERE Job.Job_ID = 42 AND JobCost.Job_ID = 42;
data = (table, table_two, table_three, conditionCategory_two, condition,
conditionCategory_three, conditionCategory_four)
select_query = """SELECT %s, %s,%s, %s,%s, %s, %s, %s
FROM %s, %s, %s
WHERE %s = %s AND %s = %s AND %s = %s AND DateCreated = Last_active
ORDER BY %s ASC LIMIT 1""" % (
displayItems[0], displayItems[1], displayItems[2], displayItems[3],
displayItems[4], displayItems[5], displayItems[6], displayItems[7],
data[0], data[1], data[2], data[3], data[4], data[5], data[4], data[6],
data[4], displayItems[1])
c.execute(select_query, )
printResultTable(c)
userChoice = getWholeNumberChoice(update_job_attributes)
while userChoice != 0:
is_cost_table = False
is_status_table = False
update_value = ''
column = ''
if userChoice == 1: # Estimate
update_value = float(input(estimatePrompt))
column = 'Estimate'
elif userChoice == 2: # Payout
update_value = float(input(payoutPrompt))
column = 'Payout'
elif userChoice == 3: # hours
update_value = getWholeNumberChoice(hoursPrompt)
column = 'Hours'
elif userChoice == 4: # Status
update_value = getStatusID()
table_two = 'JobStatus'
column = 'Status_ID'
is_status_table = True
elif userChoice == 5: # additional costs
update_value = float(input(additionsInPrompt))
table_two = 'JobCost'
column = 'Additions'
is_cost_table = True
elif userChoice == 6: # additional Mats
update_value = float(input(materialsInPrompt))
table_two = 'JobCost'
column = 'MaterialsCost'
is_cost_table = True
str_column = str(column)
if column != '':
if is_cost_table: # do jobcost
update_query = """UPDATE %s SET %s = '%s' WHERE (%s = %s)""" % (
table_two, str_column, update_value, conditionCategory,
condition)
c.execute(update_query, )
connector.commit()
is_cost_table = False
elif is_status_table: # change status
curr_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
args = [condition, update_value, curr_date]
c.callproc('UpdateJobStatus', args)
is_status_table = False
else:
# update estimate #update hours #update status
update_query = """UPDATE %s SET %s = '%s' WHERE (%s = %s)""" % (
table, str_column, update_value, conditionCategory,
condition)
c.execute(update_query, )
connector.commit()
c.execute(select_query, )
printResultTable(c)
userChoice = getWholeNumberChoice(update_job_attributes)
return 0