def create_new_adjustment(reason_id, item_sku, employee_id, qty_changes):
"""
qty_changes should be dict list in form of {location_id: int, old_qty: int, new_qty: int}
"""
try:
employee = session.query(Employee).get(employee_id)
item = session.query(Item).get(item_sku)
reason = session.query(AdjustmentReason).get(reason_id)
except:
abort(400)
new_adjustment = Adjustment()
new_adjustment.employee = employee
new_adjustment.item = item
new_adjustment.reason = reason
try:
session.add(new_adjustment)
except:
session.rollback()
abort(500)
for qty_change in qty_changes:
try:
location = session.query(Location).filter(Location.id == qty_change['location_id']).one()
except:
abort(400)
new_adj_location = AdjustmentLocation(new_qty = qty_change['new_qty'], old_qty = qty_change['old_qty'])
new_adj_location.adjustment = new_adjustment
new_adj_location.location = location
try:
session.add(new_adj_location)
except:
session.rollback()
abort(500)
return new_adjustment.id
def count_search_results(search_string: str) -> int:
count = 0
try:
item = session.query(Item).filter(Item.sku==int(search_string)).one()
return [item.sku]
except:
pass
search_terms = search_string.split()
for i in range(len(search_terms) - 1, -1, -1):
if len(search_terms[i]) < 3:
search_terms.pop(i)
sql = "SELECT sku FROM items WHERE "
for i in range(len(search_terms)):
term = search_terms[i]
search = "%{}%".format(term)
sql += "(part_no LIKE '{}' OR description ILIKE '{}') ".format(search, search)
if i < len(search_terms) - 1:
sql += "AND "
con = engine.connect()
rows = con.execute(text(sql))
fetched_results = rows.fetchall()
con.close()
matched_skus = []
for result in fetched_results:
matched_skus.append(result[0])
return matched_skus
def get_search_results(matched_skus, page: int = 0) -> [Item]:
results = []
page_start = page * page_limit
for sku in matched_skus[page_start: page_start + page_limit]:
item = session.query(Item).filter(Item.sku == sku).one()
results.append(item)
return results
def get_index_years(index_id):
years=[]
index_values=session.query(Index_value).filter(Index_value.index_id==index_id).all()
for index_value in index_values:
if index_value.year not in years:
years.append(index_value.year)
return years
def fill_indicator_value_table(ind_dict, index_name):
#{'Voice and Accountability': {'Afghanistan': {'year': '2017', 'value': -0.99}}
#'Rule of Law': {'Afghanistan': {'year': '2017', 'value': -1.57}}
index_id = get_index_id(index_name)
if not index_id:
fill_index_table(index_name, i_id=None)
index_id = get_index_id(index_name)
for indicator in ind_dict.keys():
indicator_id = get_indicator_id(indicator)
if not indicator_id:
fill_indicator_table(indicator, index_id)
indicator_id = get_indicator_id(indicator)
for info in ind_dict.values():
for country_code, value in info.items():
#print(country_code)
if not country_code or country_code == 'ANT':
continue
country_id = get_country_id_by_code(country_code)
record_exists = session.query(Indicator_value).filter(
and_(Indicator_value.country_id == country_id,
Indicator_value.index_id == indicator_id,
Indicator_value.year == value['year'])).count()
if not record_exists:
indicator_value = Indicator_value(year=value['year'],
value=value['value'],
index_id=indicator_id,
country_id=country_id)
session.add(indicator_value)
session.commit()
def fill_index_value_table(dict, index_name):
for country in dict:
c_id = session.query(Country).filter(
Country.name == country).first().id
i_id = session.query(Index).filter(Index.name == index_name).first().id
y = dict.get(country)[0]
record_exists = session.query(Index_value).filter(
and_(Index_value.country_id == c_id, Index_value.index_id == i_id,
Index_value.year == y)).count()
if not record_exists:
index_value = Index_value(country_id=c_id,
year=y,
value=dict.get(country)[1],
index_id=i_id)
session.add(index_value)
session.commit()
def get_adjustments_by_date(date1: date, date2: date = None, page: int = 0) -> [Adjustment]:
if date2 == None:
date2 = date1
return session.query(Adjustment).\
filter(and_(Adjustment.date>=date1, Adjustment.date<=date2)).\
order_by(Adjustment.datetime.desc()).\
limit(page_limit).offset(page_limit * page).all()
def delete_location_item(location_item_id):
try:
location_item = session.query(LocationItem).filter(LocationItem.id == location_item_id).one()
except:
abort(400)
if location_item.quantity != 0 or location_item.picklists != []:
return 409
session.delete(location_item)
return 200
def get_adjustment_dates(page: int = 0) -> [date]:
dates = []
for row in session.query(Adjustment.date).\
group_by(Adjustment.date).\
limit(page_limit).\
offset(page_limit * page).all():
dates.append(row[0])
dates.sort(reverse=True)
return dates
def fill_indicator_table(indicator_name, index_id):
#{'Voice and Accountability': {'Afghanistan': {'year': '2017', 'value': -0.99}}
#'Rule of Law': {'Afghanistan': {'year': '2017', 'value': -1.57}}
indicator_exists = session.query(Indicator).filter(
Indicator.name == indicator_name).count()
if not indicator_exists:
indicator = Indicator(index_id=index_id, name=indicator_name)
session.add(indicator)
session.commit()
def add_new_location(location_name, item_sku):
try:
item = session.query(Item).get(item_sku)
except:
abort(400)
try:
location = session.query(Location).filter(Location.name == location_name.upper()).one()
except:
location = Location(name = location_name.upper())
session.add(location)
for location_item in item.locations:
if location_item.location.id == location.id:
return location_item.id
location_item = LocationItem(quantity = 0)
session.add(location_item)
location_item.location = location
location_item.item = item
return location_item.id
def modify_item_details(item_sku, part_no, manufacturer, description, category_id):
try:
item = session.query(Item).filter(Item.sku == item_sku).one()
except:
abort(400)
item.part_no = part_no
item.description = description
item.manufacturer = manufacturer
item.category_id = category_id
return item
def create_new_picklist(picklist_title, employee_id):
picklist = Picklist(title = picklist_title, status = 'open')
try:
employee = session.query(Employee).filter(Employee.id == employee_id).one()
picklist.employee = employee
session.add(picklist)
except:
session.rollback()
abort(500)
return picklist
def get_index_values(index_id,year,countries_ids):
values_dict=dict()
index_values=session.query(Index_value).filter(and_(
Index_value.index_id==index_id,
Index_value.year==year,
Index_value.country_id.in_(countries_ids))).all()
for index_value in index_values:
country_name, country_id, country_code=get_country_info(index_value.country_id)
values_dict[country_name]=[index_value.value, country_code]
return values_dict
def get_index_countries_info(index_id):
index_countries_info=dict()
countries_ids_list=[]
for index_value in session.query(Index_value).filter(Index_value.index_id==index_id).all():
if index_value.country_id not in countries_ids_list:
countries_ids_list.append(index_value.country_id)
for id in countries_ids_list:
#return country.name, country.id, country.code
country_name, country_id, country_code=get_country_info(id)
index_countries_info[country_name]=[country_id,country_code]
return index_countries_info
def fill_country_table(countries_dict):
for country_id in countries_dict:
country_exists = session.query(Country).filter(
Country.id == country_id).count()
if not country_exists:
c_id = country_id
code = countries_dict.get(country_id)[0]
name = countries_dict.get(country_id)[1]
country = Country(id=c_id, code=code, name=name)
session.add(country)
session.commit()
def fill_index_table(index_name, i_id=None):
index_exists = session.query(Index).filter(
Index.name == index_name).count()
if not index_exists:
name = index_name
if i_id:
id = i_id
index = Index(id=id, name=name)
else:
index = Index(name=name)
session.add(index)
session.commit()
def create_new_item(part_no: str, description: str, manufacturer: str, category_id: int) -> Item:
try:
category = session.query(Category).filter(Category.id == category_id).one()
except:
traceback.print_exc()
abort(400)
new_item = Item(part_no=part_no.lower(), description=description, manufacturer=manufacturer.lower(), category_id=category_id)
try:
session.add(new_item)
return new_item
except:
traceback.print_exc()
abort(500)
def adjust_quantities_for_item(locations, employee_id: int, reason_id: int, item_sku: int) -> str:
# Locations argument should be a dictionary with format {'location_id': 'quantity'}
try:
item = get_item_by_sku(item_sku)
reason = session.query(AdjustmentReason).filter(AdjustmentReason.id==reason_id).one()
employee = session.query(Employee).filter(Employee.id == employee_id).one()
qty_changes = []
for location_item in item.locations:
old_qty = location_item.quantity
new_qty = locations[location_item.id]
qty_changes.append({
'location_id': location_item.location.id,
'old_qty': old_qty,
'new_qty': new_qty
})
location_item.quantity = new_qty
item.qty_checked_out = locations['checked-out']
create_new_adjustment(reason.id, item.sku, employee.id, qty_changes)
return 'Success'
except Exception as e:
traceback.print_exc()
session.rollback()
abort(500)
def get_best_worst_index_value(index_name,index_id, year):
index_values=session.query(Index_value).filter(and_(
Index_value.index_id==index_id,
Index_value.year==year)).all()
values=[index_value.value for index_value in index_values]
if index_name==fsi_index_name:
#best=min
return min(values), max(values)
if index_name==hdi_index_name:
#best=max
return max(values), min(values)
if index_name==gpi_index_name:
#best=max
return min(values), max(values)
def get_country_info_for_value(index_id, index_value, year):
index_values=session.query(Index_value).filter(and_(
Index_value.index_id==index_id,
Index_value.year==year,
Index_value.value==index_value)).all()
countries_ids=[index_value.country_id for index_value in index_values]
country_info=[]
for id in countries_ids:
info=dict()
name, c_id, code = get_country_info(id)
info[name]=[index_value, code]
if info not in country_info:
country_info.append(info)
return country_info
def count_adjustments_by_reason_id(reason_id: int) -> int:
return session.query(func.count(Adjustment.id)).filter(Adjustment.reason_id==reason_id).scalar()
def get_adjustments_by_reason_id(reason_id: int, page: int = 0) -> [Adjustment]:
return session.query(Adjustment).\
filter(Adjustment.reason_id==reason_id).\
order_by(Adjustment.datetime.desc()).\
limit(page_limit).offset(page_limit * page).all()
def count_adjustments_by_sku(sku: int) -> int:
return session.query(func.count(Adjustment.id)).filter(Adjustment.item_sku==sku).scalar()
def get_adjustments_by_sku(sku: int, page: int = 0) -> [Adjustment]:
return session.query(Adjustment).\
filter(Adjustment.item_sku==sku).\
order_by(Adjustment.datetime.desc()).\
limit(page_limit).offset(page_limit * page).all()
def count_adjustments_by_employee_id(employee_id: int) -> int:
return session.query(func.count(Adjustment.id)).filter(Adjustment.employee_id==employee_id).scalar()
def get_adjustment_by_id(adjustment_id: int) -> Adjustment:
return session.query(Adjustment).filter(Adjustment.id==adjustment_id).one()
def get_all_locations(page: int = 0) -> [Location]:
locations = session.query(Location).order_by(Location.name).limit(page_limit).offset(page_limit * page).all()
for i in range(len(locations)-1,-1,-1):
if locations[i].total_quantity == 0:
locations.pop(i)
return locations
def count_all_locations():
return session.query(func.count(Location.id)).scalar()
def get_all_categories() -> [Category]:
return session.query(Category).order_by(Category.name).all()
