Example #1
0
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)
Example #2
0
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)
Example #3
0
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)
Example #4
0
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)
Example #5
0
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')
Example #6
0
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'))
Example #7
0
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()
Example #8
0
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)
Example #9
0
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')
Example #10
0
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
Example #11
0
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)
Example #12
0
    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)