apa_database.py

# AutoPositionerApp for Kyle Woodsworth
# Name: Aaron Bell
# Collaborators: None
# Time Spent: 6:00

print("Hi, I'm apa_database.py!!")

import sqlite3

def open_connection():

	global conn 
	global c
	conn = sqlite3.connect('mydb.db')
	c= conn.cursor()

def close_connection():

	c.close()
	conn.close()
	
def create_table(table1, table2=None, table3=None):
	
	open_connection()
	
	print('create_table() was called')

	c.execute('CREATE TABLE IF NOT EXISTS ' + table1 + '(machineID TEXT, modelID TEXT, machine_status INTEGER)')
	
	if table2 is not None:
		
		c.execute('CREATE TABLE IF NOT EXISTS ' + table2 + '(modelID TEXT, positionNum INTEGER)')
		
		if table3 is not None:
		
			c.execute('CREATE TABLE IF NOT EXISTS ' + table3 + '(teammate TEXT, modelID	TEXT, positionNum INTEGER)')
		
	print('create_table() finished')	

	close_connection()
	
def alter_table(table_name=None, column_name=None, column_type=None, default_value=None):

	c.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct} DEFAULT '{df}'"\
				.format(tn=table_name, cn=column_name, ct=column_type, df=default_value))
	
	''' Example use: 
	
	alter_table('modelID_positionnum', 'teammates', 'TEXT', 'needs_positioning') '''	
	
def insert_data(tb=None, col1=None, data1=None, col2=None, data2=None, col3=None, 	data3=None, col4=None, 	data4=None, col5=None, 	data5=None):
	
	print('insert_data() was called')
	
	if tb == None: pass
	
	elif col1 is None: pass
	
	elif col1 is not None or data1 is not None:
	
		if col2 is not None or data2 is not None:
		
			if col3 is not None or data3 is not None:
			
				if col4 is not None or data4 is not None:
					
					if col5 is not None or data5 is not None:
					
						c.execute('INSERT INTO ' + tb + '(' + col1 + ', ' + col2 + ', ' + col3 + ', ' + col4 + ', ' + col5 + ') VALUES(?, ?, ?, ?, ?)', (data1, data2, data3, data4, data5))
					
					else:
					
						c.execute('INSERT INTO ' + tb + '(' + col1 + ', ' + col2 + ', ' + col3 + ', ' + col4 + ') VALUES(?, ?, ?, ?)', (data1, data2, data3, data4))
					
				else:
			
					c.execute('INSERT INTO ' + tb + '(' + col1 + ', ' + col2 + ', ' + col3 + ') VALUES(?, ?, ?)', (data1, data2, data3))
				
			else: 
			
				c.execute('INSERT INTO ' + tb + '(' + col1 + ', ' + col2 + ') VALUES(?, ?)', (data1, data2))
				
		else:
		
			c.execute('INSERT INTO ' + tb + '(' + col1 + ') VALUES(?)', (data1,))
	
	conn.commit()
	
	print('insert_data() finished')
	
	'''Example use:
	
	insert_data('machineID_modelID_status', 'machineID', 'test', 'modelID', 'test')'''

def get_data(table_name=None, specific_data=False, column_name=None, model=None,
				order_by_column=None):

	open_connection()
	
	if specific_data == 'column':
	
		c.execute('SELECT {cn} FROM {tn} ORDER BY {cn}'.\
			format(tn=table_name, cn=column_name))
			
	elif model is not None: 
		
		c.execute('SELECT * FROM {tn} WHERE {cn}="{mo}" ORDER BY {ob} '.\
			format(tn=table_name, cn=column_name, mo=model, ob=order_by_column))
	
	elif order_by_column is not None:
		
		c.execute('SELECT * FROM {tn} ORDER BY {ob} DESC'.\
			format(tn=table_name, ob=order_by_column))
	
	else:
	
		c.execute('SELECT * FROM {tn}'.format(tn=table_name))
		
	return c.fetchall()

	close_connection()
	
def prepopulate_teammate_table():

	open_connection()
	
	c.execute('SELECT * FROM modelID_positionnum')
	current_positions = c.fetchall()
	
	tm_name = input("Please enter the teammates's full name: ")
	
	if tm_name:
	
		for row in current_positions:
		
			insert_data('teammate_modelID_positionnum', 'teammate', tm_name, 'modelID', row[0], 'positionNum', row[1], 'available', 'Yes', 'restricted', 'No')	
			print(row[0], row[1])
			
		if input("Would you like to add another person?"):
			
			prepopulate_teammate_table()
	
	close_connection()

def position_people():
		
	# Get a list of all the available people
	
	open_connection()
	
	current_model = "CSEG"
	current_position = 1
	
	c.execute('SELECT teammate FROM teammate_modelID_positionnum WHERE modelID=? AND positionNum=?', (current_model, str(current_position)))
		
	available_people = c.fetchall
	
	print(available_people)
	
	# # Compare the training of each person with the list 
	# c.execute('SELECT * FROM teammate_modelID_positionnum WHERE modelID=current_position)
	
	# if current_person in c.fetchall:
		# current_position_list += current_person
		
	close_connection()
	
def list_positions():

	open_connection()
	
	c.execute("SELECT * FROM machineID_modelID_status WHERE machine_status='Up'")
	
	# Finally getting comfortable
	
	# Generate a list based on models of machines that are up. 
	
	up_machines = c.fetchall()
	global positions
	positions = []
	
	for machine in up_machines:
		
		c.execute("SELECT * FROM modelID_positionnum WHERE modelID=?", (machine[1],))
		
		# Where I learned to use a '?..., (tuple,)' format instead of Python vars: http://bit.ly/2l6XCY0
		# Where I learned to make the last part a tuple, not just a single item: http://bit.ly/2l7KFwp
		
		positions.append(c.fetchall())
	
	close_connection()
	
	#print("The positions are: " + str(positions))
	
	# Positions done!
	
def update_database():
	
	open_connection()
	
	c.execute('UPDATE teammate_modelID_positionnum SET available="No" WHERE teammate="Louis"')
	conn.commit()
	
	# Where I learned this command: http://bit.ly/2k6oqXw
	
	close_connection()
	
def get_number_of_people_trained_on_each_position():

	open_connection()
	
	c.execute('SELECT * FROM teammate_modelID_positionnum')
	
	global available_people
	available_people = list(set([x[0] for x in c.fetchall() if x[3] != 'No']))
	global number_trained_in_position
	number_trained_in_position = []
	
	
	# Where I learned to use list comprehensions properly (i.e. list only things of a certain value, that the first part is what's usually below it.): http://bit.ly/2l368uq
	# Where I learned to create sets from list comprehensions: http://bit.ly/2l3cmdE
	
	# print('The people currently available are: ' + str(available_people))
	# print(positions)
	
	for each in positions:
		
		# print('The current positions handled are: ' + str(each))
		
		for each in each:
		
			# print('The single current position handled is: ' + str(each))
			
			c.execute('SELECT * FROM teammate_modelID_positionnum WHERE modelID=? AND positionNum=? AND available="Yes"', (each[0], each[1]) )
			number_trained_in_position.append((each, len(c.fetchall())))
			
			
			# for each in c.fetchall():
				
				# print(each)

	from operator import itemgetter
	number_trained_in_position = sorted(number_trained_in_position, key=itemgetter(1))
	print(number_trained_in_position)
	
	# Where I learned to sort lists: http://bit.ly/2l3hBKz
	
	return number_trained_in_position		
			
	close_connection()
	
def assign_people_to_positions():

	''' Every time I* do a print, do a \n\n + the thing + \n\n + a few lines of what I was trying to do, followed by another \n\n'''

	import random
	
	# Untaken pool

	taken = []
	current_position_pool = []
	position_chart = []
	
	
	positions_to_post_on_screen = [] # Should be a list of tuples, (name, position)
	positions_of_all_current_models = [each[0] for each in number_trained_in_position]
	print('\n\nThe positions are: ' + str(positions_of_all_current_models) + '\n\n^ ^ : Trying to see what the list of positions I*m working with are like\n\n')
	
	for each in positions_of_all_current_models:
	
		# Untaken people trained in current position pool
		
		c.execute('SELECT teammate FROM teammate_modelID_positionnum WHERE modelID=? AND positionNum=?', (each[0], each[1]))
		
		
		
		current_position = each
		
		#print('\n\nThe pre-for current_position_pool is: ' + str(current_position_pool) + '\n\n^ ^ : Trying to see the effect the upcoming for loop has on my current_position_pool\n\n')
		#print('\n\nThe position_chart is: ' + str(position_chart) + '\n\n^ ^ : Trying to see how the position_chart updates with each consecutive loop\n\n')
		
		''' Create a non-tupled list of people for the pool'''
		
		current_position_pool = []
		
		for each in c.fetchall():
			
			#print(each[0] + ' is trained on ' + str(current_position[0]) + str(current_position[1]))
			current_position_pool.append(each[0])
		
		current_position_pool = [x for x in current_position_pool if x not in taken]
		
		#print('\n\nCPP: ' + str(current_position_pool) + '\n\n^ ^ : Trying to see how the current_position_pool looks after the for-loop detuplified it.\n\n')
		
		#print('\n\nTaken: ' + str(taken) + '\n\n^ ^ : Trying to see how the taken list updates over time, and with what type of data.')
		
		#print('\n\nLC: ' + str([x for x in current_position_pool if x not in taken]) + '\n\n^ ^ : See the effect of the list comprehension I* was trying to work with\n\n')
		
		# Randomly select one person from the untaken pool
		
		random_select_from_untaken_current_position_pool = random.choice(current_position_pool)
		
		#print('\n\nMy random belongs to...: ' + random_select_from_untaken_current_position_pool + '!\n\n^ ^ : See if my random choice worked well\n\n')	
		
		# Where I learned to pick a random item from a list: http://bit.ly/2l7pgUQ
		
		# Place this person on the position chart
		
		# print('\n\nCurrent Position + Random Select: ' + str(current_position) + ' + ' +  str(random_select_from_untaken_current_position_pool) + '\n\n^ ^ : Trying to see what values are coming in for each.\n\n')
		
		my_sexy_tuple = (current_position, random_select_from_untaken_current_position_pool)
		# print(str(my_sexy_tuple))
		position_chart.append(my_sexy_tuple)
		taken.append(my_sexy_tuple[1])
		untaken = current_position_pool
		
	#print('\n\nThe position chart so far: ' + str(position_chart) + '\n\n^ ^ : Trying to see I* want to return to my main code.\n\n')
	
	# Delete Untaken people trained in current position pool
	
	'''Done with a list compo'''
	
	# Remove the person positioned from the untaken pool 
	
	'''Done with a list compo'''
	
	# Repeat until there are no more positions left
	
	'''Done with the original for loop'''
	
	# Keep "the untaken" (haha, good movie title) in its own list
	
	
	# Choose randomly from every untaken person available and trained in it
	# Remove this person from the untaken pool
		
	return position_chart
		
	# How I learned to return from functions properly: http://bit.ly/1yn6OfQ

	
	
create_table('machineID_modelID_status', 'modelID_positionnum', 'teammate_modelID_positionnum')

list_positions()
get_number_of_people_trained_on_each_position()
assign_people_to_positions()

# Python's guide that will probably help me learn fetchone and executemany: http://bit.ly/2kvtKXY
# Best SQLite3 guide to-date: http://bit.ly/2l2jsv4
# On listing tables: http://bit.ly/2k50OVC

autopositioner.py

# AutoPositionerApp for Kyle Woodsworth
# Name: Aaron Bell
# Collaborators: None
# Time Spent: 35:00

import apa_database
import inspect
from kivy.app import App
from kivy.uix.boxlayout import BoxLayout
from kivy.properties import ObjectProperty, StringProperty, Property
from kivy.uix.screenmanager import ScreenManager, Screen#, FadeTransition
from kivy.uix.dropdown import DropDown
from kivy.uix.button import Button


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

# Screens

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

class ScreenManagement(ScreenManager):

	pass
	
	
	
# # # # # # # # # # # # # # # # # # # # 

# Positioner Screen

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

class PositionerScreen(Screen):

	pass
	
class PositionerLayout(BoxLayout):

	def __init__(self, **kwargs): # Don't know **kwargs
	
		super(PositionerLayout, self).__init__(**kwargs)
		
		# What I WANT
		# for active_machine in all_active_machines:
			# check if the machine's up 
			# give the user the option to disable it or activate it, which will affect how the program positions people--or whether they position people at that machine
			# check the model_in_the_machine
			# give the user the option to change which model is used in the machine, which will affect how the program positions people--or whether they position people at that machine--because it changes the training and restriction requirements for that machine
			# check the positions required for the model_in_the_machine, for proper display of buttons--and accurate positioning.
			# people correctly positioned--according to their training and restrictions and availability--at each machine, in a randomized way.
		# for each_machines in machine_list:
			# # separated, is because deactivated machines still need to be displayed
			# self.add_widget(MmpModule())
			# current_module_machine_name = machine_list[num_machine]
			# current_module_model_name = current model, with a dropdown_list_of_models bound to it
			# a of positions to its left-- updated with people to fill the position (list_view_thing = ready_to_be_updated)
					
		machine_list = libs.apa_database.get_data('machineID_modelID_status', order_by_column='machineID') 
		model_list = set([x[0] for x in libs.apa_database.get_data('modelID_positionnum', order_by_column='modelID')]) # set does not contain duplicate values, and I don't think it orders it... don't know how to test that, yet... 
		#print('model_list is: ' + str(model_list))
		
		global position_chart # so that this variable is available for other functions (Because I don't know how to return data from this and give it to other classes, methods in other classes, because I don't know where Kivy stores implicit instances of these classes.)
		
		position_chart = libs.apa_database.assign_people_to_positions()
		
		# Not sure if it generates based on the machine list, or models currently used (i.e. not sure if it'll handle duplicates.)
		
		dropdown = []
		mmpm = []
		
		for each_machine in range(0, len(machine_list)):
			
			dropdown.append(DropDown()) # Because if I use a single DropDown instance, it'll either change every model_button, or only change one. 
			
			print('Dropdown ID: ' + str(id(dropdown[each_machine])))
			
			for model in model_list:

				btn = Button(text='{}'.format(model), size_hint_y=None, height=44)
				
				btn.bind(                                                                                                                                                                        )

				dropdown[each_machine].add_widget(btn)
		
			mmpm.append(MmpModule())
			mmpm[each_machine].ids.machine_button.text = 'Wow: {}'.format(machine_list[each_machine][0])
			mmpm[each_machine].ids.model_button.text = 'Wow: {}'.format(machine_list[each_machine][1])
			mmpm[each_machine].ids.model_button.bind(on_release=dropdown[each_machine].open)	
			
			
			# # # # # # # # # # # # # # # # # # # # 

			# WHY

			# # # # # # # # # # # # # # # # # # # # 
			
			dropdown[each_machine].bind(on_select=lambda instance, x: setattr(mmpm[each_machine].ids.model_button, 'text', x)) # lambda - get through Unit 1
			
			# Needs to pick each item apart and analyze it for its model name. 
			# Should move onto the item immediately if the item doesn't have the model
			
			for each in position_chart:
				
				if each[0][0] == machine_list[each_machine][1]:
				
					if each[0][1] == 3:
					
						mmpm[each_machine].ids.position3.text = 'Wow: {}'.format(each[1])
						
					elif each[0][1] == 2:
					
						mmpm[each_machine].ids.position2.text = 'Wow: {}'.format(each[1])
						
					else:
					
						mmpm[each_machine].ids.position1.text = 'Wow: {}'.format(each[1])
			
			#print('\n\nThe LC result: ' + str([x for x in position_chart if (machine_list[i][1]	 in position_chart]) + '\n\n^ ^ : Trying to get the values with only the current model.\n\n')
			
			#print('\n\nID Keys: ' + str(self.ids.keys()) + '\n\n^ ^ : Trying to get the values of ids.\n\n')
			
			mymodel = mmpm[each_machine].ids.machine_button.text
			self.add_widget(mmpm[each_machine])
		
		# need to get a person in each position
		
		print(machine_list)

	def position_people(self):
		
		# Get a list of all the available people
		
		# c.execute('SELECT teammate FROM teammate_modelID_positionnum WHERE modelID=current_model AND positionNum=current_position')
			
		# available_people = c.fetchall
		
		# print(available_people)
		
		# # Compare the training of each person with the list 
		# c.execute('SELECT * FROM teammate_modelID_positionnum WHERE modelID=current_position)
		# if current_person in c.fetchall:
			# current_position_list += current_person
			
		pass
	
	def prioritize_position_by_training(self):
		
		pass
		# For each position, get the # of rows where people are trained in this position
		
class MmpModule(BoxLayout):

	machine_name = StringProperty()
	
	def __init__(self, **kwargs):
	
		super(MmpModule, self).__init__(**kwargs)
		self.machine_name = 'I-34'
		self.model_name = 'CSEG'

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

# Add Teammates Screen

# # # # # # # # # # # # # # # # # # # # 
		
class AddTeammatesScreen(Screen):

	pass

class IsSheTrained(BoxLayout):

	pass
	
class AddTeammatesLayout(BoxLayout):
	
	def __init__(self, **kwargs):

		global teammate_name
		teammate_name = ''
		super(AddTeammatesLayout, self).__init__(**kwargs)
		
		global lsm		
		lsm = [] # Label-Switch Module
		print(str(position_chart))
		for i in range(0, len(position_chart)):
			
			lsm.append(IsSheTrained())
			lsm[i].ids.position_label.text = 'Wow: {}'.format(position_chart[i][0][0])
			self.add_widget(lsm[i])
			
			
			# Needs to pick each item apart and analyze it for its model name. 
			# Should move onto the item immediately if the item doesn't have the model
			
	def record_that_shit(self):
	
		teammate_name = self.ids.teammate_name.text
		print('\n\nThe TextInput result: ' + teammate_name + '\n\n^ ^ : Trying to get the result of whether the TI*s text is registering\n\n')
		
		for each in range(0, len(lsm)):
			
			if lsm[each].ids.position_training_switch.active:
				
				#print('\n\nfor each positionNum: ' + str(position_chart[each][0][1]) + '\n\n^ ^ : Trying to see what position_chart[each][1][0] is bringing me.\n\n')
				
				#Add an entry to teammate_modelID_positionnum
				
				libs.apa_database.insert_data(tb='teammate_modelID_positionnum', col1='teammate', \
				data1=teammate_name, col2='modelID', data2=position_chart[each][0][0], \
				col3='positionNum', data3=position_chart[each][0][1], col4='available', \
				data4='Yes', col5='restricted', data5='No')
				
				# Do db shit

		App.get_running_app().root.current = 'Victory'


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

# Added Teammates Successfully Screen

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


class AddedTeammatesSuccessfullyScreen(Screen):

	pass
	
class AddedTeammatesSuccessfullyLayout(BoxLayout):

	def __init__(self, **kwargs):
	
		print('Teammate text = ' + teammate_name)

		#print('\n\ntriggertool result: ' + triggertool + '\n\n^ ^ : Trying to get triggertool to go off\n\n')
		
		super(AddedTeammatesSuccessfullyLayout, self).__init__(**kwargs)
		
		self.victory_text = "You've added {} to your team!".format(teammate_name)
		
	#### #### #### #### #### #### #### #### #### #### #### #### #### #### #### ####
	#### #### #### #### #### #### #### #### #### #### #### #### #### #### #### ####	
	
	# How do I get this to fire after AddTeammatesLayout().record_that_shit() runs?

	def trigger_text(self):
		self.victory_text = "You've added {} to your team!".format(teammate_name)
		
	#### #### #### #### #### #### #### #### #### #### #### #### #### #### #### ####
	#### #### #### #### #### #### #### #### #### #### #### #### #### #### #### ####
	
	
# # # # # # # # # # # # # # # # # # # # 

# Add Model Screen

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

# If a machine is tapped, have the option to select what model is there

# Have an entirely new screen for managing models 

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

# Added Machine Successfully Screen

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

# Add Machine Screen

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



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

# Added Machine Successfully Screen

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


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

# Add Machine Screen

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



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

# Added Machine Successfully Screen

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

# "Boilerplate" (?)

# # # # # # # # # # # # # # # # # # # # 
	
class AutopositionerApp(App):
	def build(self):
		ScreenManagement()
		
if __name__ == '__main__':
	AutopositionerApp().run()

gistfile1.txt

# AutoPositionerApp for Kyle Woodsworth
# Name: Aaron Bell
# Collaborators: None
# Time Spent: 3:00

# File name: autopositioner.py
# #: import ListAdapter kivy.adapters.listadapter.ListAdapter
# #: import ListItemButton kivy.uix.listview.ListItemButton


ScreenManagement:
	#transition: FadeTransition()
	PositionerScreen:
	AddTeammatesScreen:
	AddedTeammatesSuccessfullyScreen:
	
# # # # # # # # # # # # # # # # # # # # 

# Positioner Screen

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

<PositionerScreen>:
	
	name: 'PositionerScreen'
	
	PositionerLayout

<PositionerLayout>:

	orientation: 'vertical'
	
	Button: 
		
		on_release: app.root.current = 'AddTeammatesScreen'
		text: 'Add Teammates!'
		font_size: '30dp'
	
	Image:
	
		source: 'data/cat.jpg'
	
<MmpModule>:

	size_hint: 1,1
	orientation: 'vertical'
	id: mmpm1
	
	BoxLayout: # This should probably be in Root
	
		id: bl_super
	
		height: '90dp'
		size_hint_y: None
		padding: 10, 10
		
		BoxLayout:
			
			orientation: 'vertical'
			Button: 
			
				id: position1
				
			Button:
			
				id: position2
			
			Button:
			
				id: position3
			
		BoxLayout:
		
			id: bl2
		
			orientation: 'vertical'
			
			Button:
			
				id: machine_button
				
			Button:
			
				id: model_button
				
			
			
	
# # # # # # # # # # # # # # # # # # # # 

# Add Teammates Screen

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

		
<AddTeammatesScreen>:

	name: 'AddTeammatesScreen'
	
	AddTeammatesLayout

<AddTeammatesLayout>:
	
	orientation: 'vertical'
	
	padding: 20

	TextInput:
		
		id: teammate_name
		
		
	Button: 
		
		on_release: app.root.current = 'PositionerScreen'
		text: 'Position Em!'
		font_size: '30dp'
		
	Button: 
		
		on_release: root.record_that_shit()
		text: 'Record That Shit!'
		font_size: '30dp'
	
	Image:
	
		source: 'data/cat.jpg'
	
<IsSheTrained>:	
	
	Label:
	
		id: position_label
		text: '[Position Name]'
		
	Switch: 
	
		id: position_training_switch
		
# # # # # # # # # # # # # # # # # # # # 

# Added Teammates Successfully Screen

# # # # # # # # # # # # # # # # # # # # 		
		
<AddedTeammatesSuccessfullyScreen>:
	
	name: 'Victory'

	AddedTeammatesSuccessfullyLayout

<AddedTeammatesSuccessfullyLayout>:
	
	id: added_teammate

	Label:
		id: display_label
		text: root.victory_text
		
	Button:
		on_release: app.root.current = 'PositionerScreen'
		text: 'Position Em!'
		font_size: '30dp'