def generate_sheet(path, expanded=False):
# Generates a template to be filled and imported. Can accept either condensed or expanded
temp_wb = Workbook()
temp_sheet = temp_wb.active
# Setup the pages to have the right headers. Grab lists of the col names and merge them
db_export = DatabaseManager()
col_keys = db_export.db_getcolnames('winedata')
col_keys.extend(db_export.db_getcolnames('userinventory')[1:])
if expanded:
temp_sheet.title = 'Expanded Inventory'
temp_sheet.append(col_keys)
# Add a label to the filename so the parser knows which one we're looking at
path += 'template_expanded.xlsx'
else:
temp_sheet.title = 'Condensed Inventory'
# Replace the 'location' tag with 'qty' and add it to the condensed table and remove the dates
qty_index = col_keys.index('location')
col_keys[qty_index] = 'qty'
date_index = col_keys.index('date_in')
del col_keys[date_index:date_index+2]
temp_sheet.append(col_keys)
# Add a label to the filename so the parser knows which one we're looking at
path += 'template_condensed.xlsx'
# Save the file
temp_wb.save(path)
def update_userinv_row(update_input, rowid):
# Updates a userinventory row using the row id (so duplicates aren't
# affected). This will mostly be used for moving and checking out bottles
arg = 'UPDATE userinventory SET '
for term in update_input:
arg += term + ' = :' + term + ', '
arg = arg.rstrip(', ')
arg += ' WHERE rowid = ' + str(rowid)
inv_update = DatabaseManager()
inv_update.db_execute(arg, update_input)
def update_winedata_row(update_input):
# updates a specific row in a specified table
# this function takes a list of the following format:
# entry_input = [wine_id, upc, winery, region,
# name, varietal, wtype,
# vintage, msrp, value]
terms = cleanup_dbinput(update_input)
arg = 'UPDATE winedata SET '
for term in terms:
arg += term + ' = :' + term + ', '
arg = arg.rstrip(', ')
arg += ' WHERE wine_id = :wine_id'
db_update = DatabaseManager()
db_update.db_execute(arg, terms)
def drop_row(wine_id, rowid=None, table='userinventory'):
# drops a row from the database, if for example it is
# entered by mistake
# this function only takes a wine_id input, which forces
# the wine to be positively identified before it is
# deleted
# This also allows the table to be selected. By default,
# the table is the user inventory (which is less dangerous)
terms = [wine_id]
arg = 'DELETE FROM ' + table + ' WHERE wine_id = ?'
if rowid:
arg += ' AND rowid = ?'
terms.append(rowid)
terms = tuple(terms)
db_drop = DatabaseManager()
db_drop.db_execute(arg, terms)
def fetch_db(fetch_input, table='userinventory', in_cellar=True, sort_by=None):
# fetches a row from the database
# shoud be faster than searching since it doesn't have to guess
terms = cleanup_dbinput(fetch_input)
if table == 'both':
arg = 'SELECT * FROM winedata JOIN userinventory USING (wine_id) WHERE '
else:
arg = 'SELECT * FROM ' + table + ' WHERE '
for term in terms:
arg += '{0}={1} AND '.format(term, ':' + term)
arg = arg.rstrip(' AND ')
if in_cellar == True and table != 'winedata':
arg += ' AND date_out IS NULL'
if sort_by != None:
arg += ' ORDER BY ' + sort_by
fetch = DatabaseManager()
return fetch.db_fetch(arg, terms, 'all')
def get_rowid(entry_input, table='userinventory'):
# Returns the row id of the desired input
terms = cleanup_dbinput(entry_input)
arg = 'SELECT rowid FROM ' + table + ' WHERE '
for term in terms:
arg += '{0} = {1} AND '.format(term, ':' + term)
arg = arg.rstrip(' AND ')
getid = DatabaseManager()
return getid.db_fetch(arg, terms, 'one')[0]
####################################################################
############################ Test Code #############################
####################################################################
# userinv_dict = {"wine_id":input_list[0],
# "bottle_size":input_list[2],
# "location":input_list[3],
# "comments":input_list[4],
# "date_in":input_list[5],
# "date_out":input_list[6]}
# winedata_dict = {"wine_id":None,
# "upc":'081128011680',
# "winery":'Burnt Bridge Cellars',
# "region":'Walla Walla',
# "name":None,
# "varietal":'Cabernet Sauvignon',
# "wtype":'Table',
# "vintage":2012,
# "msrp":'$35',
# "value":'$35'}
# winedata_dict = {"winery":'ur'}
# enter_db(winedata_dict, 'winedata')
# drop_row(winedata_dict['wine_id'], 'winedata')
# print(search_db(winedata_dict, 'both'))
# find_bottle = input('Enter a bottle ID: ')
# print(lookup_db(find_bottle, 'winedata'))
def import_db(path):
# This function imports an excel file (from a specified format),
# checks for duplicates, and enters the non-duplicates into the
# database. (Note: duplicates refer to the winedata table, not
# the inventory. That will allow any)
# First, grab the col names
db_import = DatabaseManager()
wine_keys = db_import.db_getcolnames('winedata')
bottle_keys = db_import.db_getcolnames('userinventory')
# Create an instance of the bottle object
bottle = wine_bottle.Bottle({}, {})
import_wb = load_workbook(path)
import_ws = import_wb.active
# Grab all rows from the spreadsheet and make a dictionary with
# them. This will be used to add the entries to the db
import_rows = tuple(import_ws.rows)
input_dict = {}
for key in import_rows[0]:
input_dict[key.value] = None
for i in range(len(import_rows)-1):
for j, key in enumerate(input_dict):
input_dict[key] = import_rows[i+1][j].value
for wine_key in wine_keys:
bottle.wine_info[wine_key] = str(input_dict[wine_key]) if wine_key in input_dict and input_dict[wine_key] != None else None
for bottle_key in bottle_keys:
bottle.bottle_info[bottle_key] = str(input_dict[bottle_key]) if bottle_key in input_dict and input_dict[bottle_key] != None else None
if 'expanded' in path[-23:-5]:
bottle.add_new()
bottle.clear_bottle()
elif 'condensed' in path[-23:-5]:
for k in range(input_dict['qty']):
bottle.add_new()
bottle.clear_bottle()
def enter_db(entry_input, table='userinventory', ret_id=False):
# enters a wine into the database
# this function takes a list of the following format:
# entry_input = [wine_id, upc, winery, region,
# name, varietal, wtype,
# vintage, msrp, value]
# it is critical that the list is complete, even if some
# of the values are null
db_enter = DatabaseManager()
terms = cleanup_dbinput(entry_input)
#arg = 'INSERT INTO ' + table + ' (upc, winery, region, name, varietal, wtype, vintage, msrp, value) VALUES (?,?,?,?,?,?,?,?,?)'
arg = 'INSERT INTO ' + table + ' ('
values = '('
for term in terms:
arg += term + ', '
values += ':' + term + ', '
values = values.rstrip(', ') + ')'
arg = arg.rstrip(', ') + ') VALUES ' + values
return db_enter.db_execute(arg, terms, ret_id)
def lookup_db(lookup_number,
table='userinventory',
in_cellar=True,
sort_by=None):
# This is a function to quickly lookup based on either a upc or wine_id
# This is really similar to the fetch_db function (and may end up
# replacing it).
lookup = DatabaseManager()
placeholders = [lookup_number]
if table == 'both':
arg = 'SELECT * FROM winedata JOIN userinventory USING (wine_id) WHERE '
else:
arg = 'SELECT * FROM ' + table + ' WHERE '
arg += '(wine_id=?'
if table != 'userinventory':
arg += ' OR upc=?)'
placeholders.append(lookup_number)
else:
arg += ')'
if in_cellar == True and table != 'winedata':
arg += ' AND date_out IS NULL'
if sort_by != None:
arg += ' ORDER BY ' + sort_by
return lookup.db_fetch(arg, tuple(placeholders), 'all')
def search_db(search_input,
table='userinventory',
in_cellar=True,
sort_by=None):
# searches for a wine in the database that resembles the input
# first, assign the inputs to a list to be cleaned up
# this function takes a list of the following format:
# search_input = [wine_id, upc, winery, region,
# name, varietal, wtype,
# vintage, msrp, value]
# it is critical that the list is complete, even if some
# of the values are null
terms = cleanup_dbinput(search_input)
# craft the sql query string
if table == 'both':
arg = 'SELECT * FROM winedata JOIN userinventory USING (wine_id) WHERE '
else:
arg = 'SELECT * FROM ' + table + ' WHERE '
for term in terms:
arg += '{0} LIKE {1} AND '.format(term, ':' + term)
terms[term] = '%' + terms[term] + '%'
arg = arg.rstrip(' AND ')
if in_cellar == True and table != 'winedata':
arg += ' AND date_out IS NULL'
if sort_by != None:
arg += ' ORDER BY ' + sort_by
# finally, call the search function from the db_man object
db_search = DatabaseManager()
result = db_search.db_fetch(arg, terms, 'all')
if result:
return result
else:
return None
def export_db(path):
# Exports the database to an excel file at the specified
# file path
# First, create a workbook. Condensed uses Qty instead of location
# Expanded preserves location data
exp_wb = Workbook()
exp_condensed = exp_wb.active
exp_condensed.title = 'Condensed Inventory'
exp_full = exp_wb.create_sheet('Expanded Inventory')
# Setup the pages to have the right headers. Grab lists of the col names and merge them
db_export = DatabaseManager()
col_keys = db_export.db_getcolnames('winedata')
col_keys.extend(db_export.db_getcolnames('userinventory')[1:])
exp_full.append(col_keys)
# Replace the 'location' tag with 'qty' and add it to the condensed table and remove the dates
qty_index = col_keys.index('location')
col_keys[qty_index] = 'qty'
date_index = col_keys.index('date_in')
del col_keys[date_index:date_index+2]
exp_condensed.append(col_keys)
# now, grab everything from each table and store it in a list of lists
inv_rows = db_export.db_fetch('SELECT * FROM userinventory WHERE date_out IS NULL ORDER BY wine_id', rows='all')
# Add everything to the excel file sequentially
# First, initialize the variables that may not exist
# until later in the loop
old_id = None
qty = 0
old_row = []
# Iterate through each of the entries in the inventory For each
# entry, look it up in the winedata table and add create one
# long list which the relevant data. This is a little slower,
# but more memory efficient. Write that to the extended page.
for i in range(len(inv_rows)):
write_row = list(db_export.db_fetch('SELECT * FROM winedata WHERE wine_id=?', (inv_rows[i][0],)))
write_row.extend(inv_rows[i][1:])
exp_full.append(write_row)
# Remove the date in/out entries
del write_row[date_index:date_index+2]
# The condensed row will run one iteration behind in order
# to check for another of the same bottle. It is initialized
# as none to avoid a definition error in the first loop
if old_row != []:
# if the wine id is the same, increment the counter
if old_id == inv_rows[i][0]:
qty += 1
# if it's not, incriment the counter to account for the
# last bottle, write it, and reset qty counter
else:
qty += 1
old_row[qty_index] = qty
exp_condensed.append(old_row)
qty = 0
# set old row and old id so we can check it against the
# next index on the next cycle
old_row = write_row.copy()
old_id = inv_rows[i][0]
# Because the condensed table runs one cycle behind, we have to
# run the write command one more time to finish the last entry
qty += 1
old_row[qty_index] = qty
exp_condensed.append(old_row)
# Save the file
exp_wb.save(path)
def __init__(self):
super().__init__()
self.setupUi(Vinny)
# Connect the buttons to their respective functions
self.actionBottle.triggered.connect(self.delete_bottle)
self.actionWine.triggered.connect(self.delete_wine)
self.actionExport.triggered.connect(self.export_to_excel)
self.actionImport.triggered.connect(self.import_from_excel)
self.actionGenerate_Barcode.triggered.connect(self.generate_barcode)
self.actionBackup_Database.triggered.connect(self.backup_database)
self.actionPreferences.triggered.connect(self.edit_preferences)
self.InventorySearch.clicked.connect(self.quick_search)
self.InventoryCheckOut.clicked.connect(self.inv_check_out)
self.InventoryMoveBottle.clicked.connect(self.inv_move_bottle)
self.InventoryAddCopy.clicked.connect(self.inv_add_copy)
self.InventoryEditBottle.clicked.connect(self.inv_edit_bottle)
self.InventoryTable.cellClicked.connect(self.inv_get_bottle)
self.AddBottleSearch.clicked.connect(self.ab_deep_search)
self.AddBottleAdd.clicked.connect(self.ab_add_to_cellar)
self.AddBottleTable.doubleClicked.connect(self.ab_get_wine)
self.AddBottleClearFields.clicked.connect(self.ab_clear_fields)
self.AddBottleUpdate.clicked.connect(self.ab_update_wine)
self.AddBottleUPC.returnPressed.connect(self.ab_upc_fill)
self.AddBottleGenerateBarcode.clicked.connect(self.generate_barcode)
self.HistoryTable.cellClicked.connect(self.hist_get_bottle)
# Connect all fields in the wines tab to a function to detect modifications
self.ab_modified_flag = True
self.AddBottleUPC.textChanged.connect(self.ab_modified)
self.AddBottleWinery.textChanged.connect(self.ab_modified)
self.AddBottleAVA.textChanged.connect(self.ab_modified)
self.AddBottleBlendName.textChanged.connect(self.ab_modified)
self.AddBottleVarietal.textChanged.connect(self.ab_modified)
self.AddBottleType.currentIndexChanged.connect(self.ab_modified)
self.AddBottleVintage.textChanged.connect(self.ab_modified)
self.AddBottleMSRP.textChanged.connect(self.ab_modified)
self.AddBottleCurrentValue.textChanged.connect(self.ab_modified)
self.AddBottleComments.textChanged.connect(self.ab_modified)
self.AddBottleRating.textChanged.connect(self.ab_modified)
# Get the names of the collumns at the beginning so we don't have to do that a million times
# Start by verifying that the database actually exists
self.db_manager = DatabaseManager()
self.db_manager.verify_db()
self.wine_col_names = self.db_manager.db_getcolnames('winedata')
self.inv_col_names = self.db_manager.db_getcolnames('userinventory')
self.combined_col_names = self.wine_col_names.copy()
self.combined_col_names.extend(self.inv_col_names[1:])
self.location_index = self.combined_col_names.index('location')
# Initialize the table sizes so they don't have to be queried every single time
self.InventoryTable.setColumnCount(len(self.combined_col_names))
self.InventoryTable.setHorizontalHeaderLabels(
self.translate_col_names(self.combined_col_names))
self.AddBottleTable.setColumnCount(len(self.wine_col_names))
self.AddBottleTable.setHorizontalHeaderLabels(
self.translate_col_names(self.wine_col_names))
self.HistoryTable.setColumnCount(len(self.combined_col_names))
self.HistoryTable.setHorizontalHeaderLabels(
self.translate_col_names(self.combined_col_names))
# Create a new bottle object to be manipulated by the user
self.bottle = Bottle({}, {})
# Populate the inventory table so it's ready to go at the start
self.inv_table_pop(None, None)
# Disable the 'Generate Barcode' button until it's ready to be used
self.AddBottleGenerateBarcode.setEnabled(False)