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()