def delete_client_handler(): try: id_ = int(input('Enter client id to delete:')) except (ValueError, TypeError): print('Invalid id') return session.query(Client).filter(Client.id == id_).delete() session.commit()
def delete_medicine_handler(): try: medicine_id = int(input('Enter medicine id to delete:')) except (ValueError, TypeError): print('Invalid id') return mn = session.query(Medicine).filter(Medicine.id == medicine_id).first() session.query(Recipe).filter(Recipe.medicine_name == mn.name).delete() session.query(Medicine).filter(Medicine.id == medicine_id).delete() session.commit()
def get_cooking_book_for_orders_in_process() -> t.Optional[CookingBook]: medicine_ids_query = (session.query( Order, Medicine.id).join(Medicine).filter( Order.status == Order.STATUSES.in_process)) medicine_ids = set(r[1] for r in medicine_ids_query.all()) cooking_books_query = (session.query(CookingBook).join(Medicine).filter( Medicine.id.in_(medicine_ids))) return cooking_books_query.all()
def get_medicine_with_minimal_components_amount( type_: t.Optional[MedicineType] = None) -> t.List[str]: min_query = (session.query(func.min(Component.amount)).join( Medicine.ingredients).join(Component).scalar_subquery()) query = (session.query(distinct(Medicine.name)).join( Medicine.ingredients).join(Component).filter( Component.amount == min_query)) if type_: query = query.filter(Medicine.type == type_) return [r[0] for r in query.all()]
def get_orders_for_most_popular_medicine(): most_popular_medicine_id = (session.query( Order.medicine_id, func.count(Order.medicine_id).label('order_amount')).group_by( Order.medicine_id).order_by( text('order_amount DESC')).limit(1).first()) if most_popular_medicine_id: query = (session.query(Order).filter( Order.medicine_id == most_popular_medicine_id[0])) return query.all() else: return None
def delete_recipe_handler(): try: id_ = int(input('Enter recipe id to delete:')) except (ValueError, TypeError): print('Invalid id') return r = session.query(Recipe).get(id_) if r.order_id: session.query(Order).filter(Order.id == r.order_id).delete() session.commit() session.query(Recipe).filter(Recipe.id == id_).delete() session.commit()
def create_medicine( name: str, storage_time: date, amount: float, price: float, type: MedicineType, cooking_method: CookingMethod, ingredients_ids: t.List[int] = [], ) -> Medicine: med = Medicine( name=name, storage_time=storage_time, amount=amount, price=price, type=type, ) ingredients = (session.query(Ingredient).filter( Ingredient.id.in_(ingredients_ids))) if ingredients: med.ingredients.extend(ingredients) session.add(med) session.commit() cb_service.create_cooking_book(med.id, cooking_method) return med
def set_ingredient_dose(id_: int, dose: int) -> None: ( session.query(Ingredient) .filter(Ingredient.id == id_) .update({'dose': dose}) ) session.commit()
def get_top_ten_most_used_medicines() -> t.List[str]: query = (session.query(Medicine.name, func.count(Order.medicine_id).label('total')).join( Order, Order.medicine_id == Medicine.id).group_by( Order.medicine_id).order_by( text('total DESC')).limit(10)) return [r[0] for r in query.all()]
def create_recipe_and_order_handler(): print("Creating recipe:") doctor = input('Enter doctor:') try: client_id = int(input('Enter client id:')) except (ValueError, TypeError): print('Wrong client id') return diagnosis = input('Enter diagnosis:') try: amount = int(input('Enter amount:')) except (ValueError, TypeError): print('Wrong amount') return consumption_type_str = input('Enter consumption type:') try: consumption_type = ConsumptionType[consumption_type_str] except KeyError: print('No such type') return medicine_name = input('Enter medicine name:') medicine = session.query(Medicine).filter(Medicine.name == medicine_name).first() if not medicine: print("No such medicine") return recipe = recipe_service.create_recipe( doctor=doctor, client_id=client_id, diagnosis=diagnosis, amount=amount, consumption_type=consumption_type, medicine_name=medicine_name, ) print(recipe) print("Creating order") try: medicine_id = int(input('Enter medicine id:')) except (ValueError, TypeError): print('Wrong medicine id') return try: rt = input('Enter ready time:') ready_time = ( datetime.datetime.strptime(rt, '%d-%m-%y').date() ) except ValueError: print('No such date') return order = order_service.create_order( medicine_id=medicine_id, recipe_id=recipe.id, ready_time=ready_time, ) session.commit() print(order)
def create_critical_norm( component_id: int, amount: float, ) -> CriticalNorm: component = session.query(Component).filter( Component.id == component_id).first() if not component: return None crit_norm = CriticalNorm(component_id=component_id, amount=amount) session.add(crit_norm) session.commit() return crit_norm
def get_client_by_ordered_medicine_type( type_: MedicineType, start_date: t.Optional[date] = None, end_date: t.Optional[date] = None, ) -> t.List[Client]: query = (session.query( Order, Medicine.type, Client).join(Medicine).join(Client).filter(Medicine.type == type_)) if start_date: query = query.filter(Order.date_created > start_date) if end_date: query = query.filter(Order.date_created <= end_date) return [r[2] for r in query.all()]
def create_supply_request( component_id: int, client_id: int, ) -> SupplyRequest: component = session.query(Component).filter( Component.id == component_id).first() if not component: return None sup_request = SupplyRequest( component_id=component_id, client_id=client_id, ) session.add(sup_request) session.commit() return sup_request
def get_component_used_amount( name: str, start_date: date, end_date: date, ) -> t.List[t.Tuple[str, int]]: query = (session.query( func.avg(Order.date_created), Component.name, func.sum(Ingredient.dose), ).join(Medicine).join(Medicine.ingredients).join( Ingredient.component).filter(Order.date_created > start_date).filter( Order.date_created <= end_date).filter( Component.name == name).group_by(Component.name)) return [(r[1], r[2]) for r in query.all()]
def get_component_price_for_medicine( medicine_id: int, ) -> t.Dict[str, t.List[t.Tuple[str, float]]]: query = (session.query(Medicine.name, Ingredient.component_id, Component.name, Component.price).join( Medicine.ingredients).join(Component).filter( Medicine.id == medicine_id)) return { medicine_name: [(r[2], r[3]) for r in records] for medicine_name, records in groupby( sorted( query, key=itemgetter(0), ), key=itemgetter(0), ) }
def create_recipe( doctor: str, client_id: int, diagnosis: str, amount: float, consumption_type: ConsumptionType, medicine_name: str, order_id: Optional[int] = None, ) -> Recipe: client = session.query(Client).filter(Client.id == client_id).first() if not client: return None recipe = Recipe( doctor=doctor, client_id=client_id, diagnosis=diagnosis, amount=amount, consumption_type=consumption_type, order_id=order_id, medicine_name=medicine_name, ) session.add(recipe) session.commit() return recipe
def set_critical_norm(component_id: int, amount: int) -> None: (session.query(CriticalNorm).filter( CriticalNorm.id == component_id).update({'amount': amount})) session.commit()
def get_clients_waiting_for_ingredients() -> t.List[Client]: query = (session.query(Client, Order.status).join( Client, Order.client_id == Client.id).filter( Order.status == Order.STATUSES.waiting_for_components)) return [i[0] for i in query.all()]
def get_full_medicine_info(medicine_id: int) -> t.Optional[Medicine]: return session.query(Medicine).filter(Medicine.id == medicine_id).first()
def get_by_name(name: str) -> t.Optional[Medicine]: return session.query(Medicine).filter(name == name).first()
def get_price_for_medicine(medicine_id: int) -> int: query = (session.query(Medicine.price).filter(Medicine.id == medicine_id)) return query.scalar()
def get_clients_with_not_taken_orders() -> t.List[Client]: query = (session.query(Recipe, Client).join(Client).join(Order).filter( Order.status == Order.STATUSES.ready).filter( Order.ready_time < date.today())) return [r[1] for r in query.all()]
def get_component_by_id(id_: int) -> Optional[Component]: component = session.query(Component).get(id_) return component
def get_cooking_book_for_medicine_type( type_: MedicineType) -> t.Optional[CookingBook]: query = (session.query(CookingBook).join(Medicine).filter( Medicine.type == type_)) return query.all()
def set_component_amount(component_id: int, amount: int) -> None: (session.query(Component).filter(Component.id == component_id).update( {'amount': amount})) session.commit()
def get_ingredient_by_id(id_: int) -> Optional[Ingredient]: component = session.query(Ingredient).get(id_) return component
def get_components_with_critical_norm(): query = (session.query(CriticalNorm.amount, Component).join(Component).filter( Component.amount <= CriticalNorm.amount)) return [r[1] for r in query.all()]
def get_medicine_in_waiting_for_components_status(): query = (session.query(Order.status, Medicine).join(Medicine).filter( Order.status == Order.STATUSES.waiting_for_components)) return [r[1] for r in query.all()]
def get_orders_amount_in_process_status(): query = (session.query(func.count( Order.id)).filter(Order.status == Order.STATUSES.in_process)) return query.scalar()
def get_cooking_book_for_medicine_name(name: str) -> t.Optional[CookingBook]: query = (session.query(CookingBook).join(Medicine).filter( Medicine.name == name)) return query.all()