def edit(id=None): setExits() #import pdb;pdb.set_trace() transaction = Transaction(g.db) if request.form: id = request.form.get('id', None) id = cleanRecordID(id) items = Item(g.db).select() current_item = None if id < 0: flash("Invalid Record ID") return redirect(g.listURL) if id >= 0 and not request.form: if id == 0: rec = transaction.new() rec.created = local_datetime_now() if 'last_trx' in session: transaction.update(rec, session['last_trx']) else: rec = transaction.get(id) if not rec: flash('Record not Found') return redirect(g.listURL) else: #Get the item if there is one if rec.item_id != 0: current_item = Item(g.db).get(rec.item_id) elif request.form: current_item = Item(g.db).get( cleanRecordID(request.form.get('item_id', "0"))) if id == 0: rec = transaction.new() else: rec = transaction.get(id) if not rec: flash('Record not found when trying to save') return redirect(g.listURL) transaction.update(rec, request.form) error_list = [] if save_record(rec, error_list): return redirect(g.listURL) else: for err in error_list: flash(err) return redirect(g.listURL) return render_template('trx_edit.html', rec=rec, current_item=current_item, items=items)
def edit(id=0): setExits() g.title = 'Edit Location Record' map_html = None map_data = None search_field_id = None location = Location(g.db) id = cleanRecordID(id) if request.form: id = cleanRecordID(request.form.get("id")) #import pdb;pdb.set_trace() if id < 0: return abort(404) if id > 0: rec = location.get(id) if not rec: flash("{} Record Not Found".format(location.display_name)) return redirect(g.listURL) else: rec = location.new() rec.location_name = "New Location" search_field_id = 'search-input' if request.form: location.update(rec, request.form) if valid_input(rec): location.save(rec) g.db.commit() return redirect(g.listURL) if rec.lat and rec.lng: map_data = { 'lat': rec.lat, 'lng': rec.lng, 'title': rec.location_name, 'UID': rec.id, 'draggable': True, 'latitudeFieldId': 'latitude', 'longitudeFieldId': 'longitude', } else: search_field_id = "search-input" map_html = simple_map(map_data, target_id='map', search_field_id=search_field_id) return render_template( 'location_edit.html', rec=rec, map_html=map_html, )
def stock_on_hand(id=None): """Return the stock count for the item.id else something else""" rec = Item(g.db).get(cleanRecordID(id)) if rec: soh = Item(g.db).stock_on_hand(cleanRecordID(id)) if soh > 0: if soh >= cleanRecordID(rec.min_stock): return soh else: return "{} Min ({})".format(soh, rec.min_stock) return "- out of stock -"
def edit_from_list(id=None, item_id=None): """Handle creation of transaction from the Item record form""" setExits() #import pdb;pdb.set_trace() item_id = cleanRecordID(item_id) item_rec = None rec = None warehouses = Warehouse(g.db).select() trx_types = get_site_config().get('trx_types', [ 'Add', 'Remove', ]) transaction = Transaction(g.db) trx_id = cleanRecordID(id) if trx_id > 0: rec = transaction.get(trx_id) if rec: item_id = rec.item_id else: rec = transaction.new() rec.created = local_datetime_now() if 'last_trx' in session: transaction.update(rec, session['last_trx']) # Handle Response? if request.form: #import pdb;pdb.set_trace() error_list = [] transaction.update(rec, request.form) if save_record(rec, error_list): return "success" # the success function looks for this... else: pass if item_id > 0: item_rec = Item(g.db).get(item_id) if not item_rec: flash("This is not a valid item id") return "failure: This is not a valid item id." else: rec.item_id = item_id return render_template('trx_edit_from_list.html', rec=rec, current_item=item_rec, warehouses=warehouses, trx_types=trx_types)
def category_name(self, id): id = cleanRecordID(id) rec = self.get(id) if rec: return rec.name else: return "Unknown"
def get_trx_list_for_item(item_id=None): """Render an html snippet of the transaciton list for the item""" item_id = cleanRecordID(item_id) trxs = None if item_id and item_id > 0: where = 'lower(trx.trx_type) not like "transfer%" and item_id = {}'.format( item_id) order_by = 'trx.created desc, trx.warehouse_id' sql = """SELECT trx.*, warehouse.name as warehouse_name, category.name as category_name FROM trx JOIN item on item.id = trx.item_id JOIN category on category.id = item.cat_id JOIN warehouse on warehouse.id = trx.warehouse_id WHERE {where} ORDER BY {order_by} """.format(where=where, order_by=order_by) trxs = Transaction(g.db).query(sql) return render_template('trx_embed_list.html', trxs=trxs, item_id=item_id)
def edit(rec_id=None): setExits() g.title = "Edit {} Record".format(g.title) starter = PRIMARY_TABLE(g.db) rec = None if rec_id == None: rec_id = request.form.get('id',request.args.get('id',-1)) rec_id = cleanRecordID(rec_id) #import pdb;pdb.set_trace if rec_id < 0: flash("That is not a valid ID") return redirect(g.listURL) if rec_id == 0: rec = starter.new() else: rec = starter.get(rec_id) if not rec: flash("Unable to locate that record") return redirect(g.listURL) if request.form: starter.update(rec,request.form) if validForm(rec): starter.save(rec) g.db.commit() return redirect(g.listURL) # display form return render_template('starter/starter_edit.html', rec=rec)
def get(self, name, user_name=None, **kwargs): """can get by pref name and user_name""" user_clause = 'and user_name is null' if user_name: user_clause = 'and user_name = "{}"'.format(user_name) if type(name) is str: where = ' lower(name) = lower("{}") {}'.format(name, user_clause) else: where = ' id = {}'.format(cleanRecordID(name)) result = self.select_one(where=where) if not result and ('default' in kwargs or 'description' in kwargs) and type(name) is str: # create a record with the default values rec = self.new() rec.name = name rec.value = kwargs['default'] rec.user_name = user_name rec.expires = kwargs.get('expires') rec.description = kwargs.get("description") self.save(rec) self.db.commit() result = rec return result
def add_role(self, user_id, role): """Add roles for the user. role param may be int or str of Role name""" user_id = cleanRecordID(user_id) role_id = -1 if type(role) == str: rec = Role(self.db).get(role) if rec: role_id = rec.id else: role_id = cleanRecordID(role) if user_id > 0 and role_id > 0: self.db.execute( 'insert into user_role (user_id,role_id) values (?,?)', ( user_id, role_id, ))
def category_name(id=None): """Return the name of the category or None""" from inventory.models import Category rec = Category(g.db).select_one(where='id = {}'.format(cleanRecordID(id))) if rec: return rec.name return None
def validate_form(rec): valid_form = True datestring = request.form.get('created', '').strip() if datestring == '': valid_form = False flash('Date may not be empty') else: createdDate = getDatetimeFromString(datestring) if createdDate is None: flash('Date is not in a known format ("mm/dd/yy")') valid_form = False elif createdDate > local_datetime_now(): flash("The date may not be in the future") valid_form = False if valid_form: rec.created = createdDate # Value must be a number try: rec.value = float(request.form.get('value', 0)) except ValueError as e: flash('Could not convert Value {} to a number'.format( request.form.get('value', ""))) valid_form = False # Must be attached to an item itemID = cleanRecordID(request.form.get('item_id', 0)) if not itemID or itemID < 0: flash("You must select an item to use with this transaction") valid_form = False #Try to coerse qty to a number rec.qty = request.form.get('qty', '').strip() if rec.qty == '': flash('Quantity is required') valid_form = False if not Warehouse(g.db).get(request.form.get('warehouse_id', -1)): flash("You must select a warehouse") valid_form = False try: rec.qty = float(rec.qty) if rec.qty == 0: flash('Quantity may not be 0') valid_form = False #truncate qty if int if rec.qty - int(rec.qty) == 0: rec.qty = int(rec.qty) except ValueError as e: flash('Could not convert Qty {} to a number'.format(rec.qty)) valid_form = False return valid_form
def get_roles(self, userID, **kwargs): """Return a list of the role namedlist objects for the user's roles""" order_by = kwargs.get('order_by', 'rank desc, name') sql = """select * from role where id in (select role_id from user_role where user_id = ?) order by {} """.format(order_by) return Role(self.db).rows_to_data_row( self.db.execute(sql, (cleanRecordID(userID), )).fetchall())
def _get_warehouse_where(self, **kwargs): """Return a snippet of sql to filter by warehouse_id""" warehouse_id = cleanRecordID(kwargs.get('warehouse_id')) warehouse_where = '' if warehouse_id > 0: warehouse_where = " and trx.warehouse_id = {} ".format( warehouse_id) return warehouse_where
def _validate_rec_id(self): if not self.rec_id: self.rec_id = request.form.get('id', request.args.get('id', 0)) self.rec_id = cleanRecordID(self.rec_id) if self.rec_id < 0: self.result_text = "That is not a valid ID" self.success = False raise ValueError(self.result_text)
def test_cleanRecordID(): """Tesst the cleanRecordID utility fuction""" assert utils.cleanRecordID(1234) == 1234 assert utils.cleanRecordID("1234") == 1234 assert utils.cleanRecordID("this is a test4455") == -1 assert utils.cleanRecordID("1234this is a test") == -1 assert utils.cleanRecordID(-4) == -4 assert utils.cleanRecordID('-4') == -1 assert utils.cleanRecordID(None) == -1
def refresh_trx_lists(item_id=0): #import pdb;pdb.set_trace() item_id = cleanRecordID(item_id) transactionList = get_trx_list_for_item(item_id) transferList = get_transfer_list_for_item(item_id) qoh_list = get_qoh_by_warehouse(item_id) on_hand = get_stock_on_hand(item_id) return render_template("trx_and_transfer_lists.html", transactionList=transactionList, transferList=transferList, qoh_list=qoh_list, on_hand=on_hand)
def get(self, id, **kwargs): """Return a single namedlist for the user with this id. If 'id' is a string, try to find the user by username or email using self.get_by_username_or_email If include_inactive=True is in kwargs the search will include inactive users else they are excluded from the result. """ if type(id) is str: return self.get_by_username_or_email(id, **kwargs) include_inactive = kwargs.get('include_inactive', False) where = 'id = {} {}'.format(cleanRecordID(id), self._active_only_clause(include_inactive)) return self.select_one(where=where)
def delete(self,id,**kwargs): """Delete a single row with this id. Return True or False""" #import pdb;pdb.set_trace() id = cleanRecordID(id) row = self.get(id,**kwargs) if row: self.db.execute('delete from {} where id = ?'.format(self.table_name),(id,)) if kwargs.get('commit',False): self.db.commit() return True return False
def get_transfer_list_for_item(item_id=None): """Render an html snippet of the transaciton list for the item""" item_id = cleanRecordID(item_id) recs = None where = "1" if item_id and item_id > 0: where = 'transfer.item_id = {}'.format(item_id) sql = get_transfer_select(where) #print(sql) recs = Transfer(g.db).query(sql) return render_template('transfer_embed_list.html', recs=recs, item_id=item_id)
def handle_delete(id=None): if id == None: id = request.form.get('id', request.args.get('id', -1)) id = cleanRecordID(id) if id <= 0: #flash("That is not a valid record ID") return False rec = Transaction(g.db).get(id) if not rec: #flash("Record not found") return False else: Transaction(g.db).delete(rec.id) g.db.commit() return True
def lifo_cost(self, id, start_date=None, end_date=None, **kwargs): """Return the LIFO cost for an item Optional date range may be porvided. if "warehouse_id" is in kwargs, limit search to that warehouse """ id = cleanRecordID(id) start_date, end_date = self.set_dates(start_date, end_date) sql = """ select COALESCE(value, 0) as value from trx where item_id = {} and qty > 0 and value > 0 and date(created) >= date("{}") and date(created) <= date("{}") order by created desc """.format(id, start_date, end_date) rec = self.db.execute(sql).fetchone() return self.handle_rec_value(rec, 'value')
def admin(id=None): """Administrator access for the User table records """ setExits() if id == None: flash("No User identifier supplied") return redirect(g.listURL) #import pdb;pdb.set_trace() id = cleanRecordID(id) if (id < 0): flash("That is an invalid id") return redirect(g.listURL) #session['user_edit_token'] = id return edit(id)
def edit(id=None): setExits() g.title = "Edit {} Record".format(g.title) warehouse = Warehouse(g.db) if request.form: id = request.form.get('id', None) id = cleanRecordID(id) if id >= 0 and not request.form: if id == 0: rec = warehouse.new() else: rec = warehouse.get(id) if rec: return render_template('warehouse_edit.html', rec=rec) else: flash('Record not Found') if request.form: if not validate_form(): return render_template('warehouse_edit.html', rec=request.form) if id == 0: rec = warehouse.new() else: rec = warehouse.get(id) if rec: warehouse.update(rec, request.form) warehouse.save(rec) try: g.db.commit() except Exception as e: g.db.rollback() flash( printException('Error attempting to save Warehouse record', str(e))) return redirect(g.listURL) else: flash('Record not Found') return redirect(g.listURL)
def delete(id=None): setExits() if id == None: id = request.form.get('id', request.args.get('id', -1)) id = cleanRecordID(id) if id <= 0: flash("That is not a valid record ID") return redirect(g.listURL) rec = Item(g.db).get(id) if not rec: flash("Record not found") else: Item(g.db).delete(rec.id) g.db.commit() flash("Record Deleted") return redirect(g.listURL)
def get_qoh_by_warehouse(id): """Return a list of namedlist with quantity on hand in each warehouse for the item id""" recs = [] id = cleanRecordID(id) if id > 0: sql = """select COALESCE(sum(trx.qty), 0) as qty, warehouse.name from warehouse join item on item.id = trx.item_id left join trx on trx.warehouse_id = warehouse.id where item.id = {id} group by warehouse_id,item.id order by lower(warehouse.name)""".format(id=id) recs = Item(g.db).query(sql) else: flash("Invalid item ID") return recs
def subtractions(self, id, start_date=None, end_date=None, **kwargs): """Return the quantity of of product removed from inventory Optional date range may be porvided. if "warehouse_id" is in kwargs, limit search to that warehouse """ # 12/13/19 - exclude Transfer transactions warehouse_where = self._get_warehouse_where(**kwargs) id = cleanRecordID(id) start_date, end_date = self.set_dates(start_date, end_date) sql = """ select COALESCE(sum(qty), 0) as qty from trx where item_id = {} and qty < 0 and date(created) >= date("{}") and date(created) <= date("{}") and trx_type not like 'Transfer%' {} """.format(id, start_date, end_date, warehouse_where) rec = self.db.execute(sql).fetchone() return self.handle_rec_value(rec, 'qty')
def delete(self, *args, **kwargs): record_id = None delete_by_admin = request.args.get('delete', None) if delete_by_admin: rec = self.table.select_one( where='access_token = "{}"'.format(delete_by_admin.strip())) if rec: record_id = rec.id if not record_id and len(self.path) > 1: record_id = self.path[1] self.success = self.table.delete(cleanRecordID(record_id)) if not self.success: self.result_text = 'Not able to delete that record.' else: self.db.commit() self.result_text = 'User Record Deleted' flash(self.result_text)
def stock_on_hand(self, id, end_date=None, **kwargs): """Return the quantity in inventory Optional date range may be porvided. if "warehouse_id" is in kwargs, limit search to that warehouse """ #import pdb;pdb.set_trace() warehouse_where = self._get_warehouse_where(**kwargs) id = cleanRecordID(id) if end_date is None: end_date = local_datetime_now() sql = """select COALESCE(sum(qty), 0) as qty from trx where item_id = {} and date(created) <= date("{}") {} """.format(id, end_date, warehouse_where) rec = self.db.execute(sql).fetchone() return self.handle_rec_value(rec, 'qty')
def additions(self, id, start_date=None, end_date=None, **kwargs): """Return the quantity of of product added to inventory Optional date range may be porvided. if "warehouse_id" is in kwargs, limit search to that warehouse """ # 12/13/19 - exclude Transfer transactions # 11/13/20 - don't worry about transfers, include all trx.qty > 0 warehouse_where = self._get_warehouse_where(**kwargs) id = cleanRecordID(id) #import pdb;pdb.set_trace() start_date, end_date = self.set_dates(start_date, end_date) sql = """select COALESCE(sum(qty), 0) as qty from trx where item_id = {} and qty > 0 and date(created) >= date("{}") and date(created) <= date("{}") and trx.qty > 0 {} """.format(id, start_date, end_date, warehouse_where) rec = self.db.execute(sql).fetchone() return self.handle_rec_value(rec, 'qty')
def validForm(rec): # Validate the form goodForm = True if request.form['name'].strip() == '': goodForm = False flash('Name may not be blank') else: # name must be unique (but not case sensitive) where = 'lower(name)="{}"'.format( request.form['name'].lower().strip(), ) if rec.id: where += ' and id <> {}'.format(rec.id) if PRIMARY_TABLE(g.db).select(where=where) != None: goodForm = False flash('Role names must be unique') # Rank must be in a reasonalble range temp_rank = cleanRecordID(request.form['rank']) if temp_rank < 0 or temp_rank > 1000: goodForm = False flash("The Rank must be between 0 and 1000") return goodForm