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