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
