def create_offering(offering: Offering):
query = '''
insert into offering (
id,
created_at,
updated_at,
user_account_id,
category,
subcategory,
quantity_kg,
price_per_kg_cfa_cents
)
values (
:id,
:created_at,
:updated_at,
:user_account_id,
:category,
:subcategory,
:quantity_kg,
:price_per_kg_cfa_cents
)
returning *;
'''
engine.execute(sqla.text(query), asdict(offering))
def retrieve_offering_audios(
filters: OfferingAudioFilters) -> List[OfferingAudio]:
params: OfferingAudioFilters = {
'lang': filters['lang'],
'category': filters['category'],
'subcategory': filters['subcategory'],
'min_quantity': filters.get('min_quantity', 0.0),
'max_quantity': filters.get('max_quantity', 9999.0),
}
print(params)
query = '''
select *
from offering_audio
where offering_id in (
select id
from offering
where quantity_kg >= :min_quantity
and quantity_kg <= :max_quantity
and category = lower(:category)
and lower(subcategory) = lower(:subcategory)
) and lang = :lang;
'''
result = engine.execute(sqla.text(query), params).fetchall()
return [from_result(a, OfferingAudio) for a in result]
def create_user(data: User) -> User:
query = '''
insert into user_account (
id,
created_at,
updated_at,
name,
user_group,
email,
phone_number,
password
)
values (
:id,
:created_at,
:updated_at,
:name,
:user_group,
lower(:email),
:phone_number,
:password
)
returning *;
'''
result = engine.execute(sqla.text(query), asdict(data))\
.first()
return from_result(result, User) # type: ignore
def retrieve_offerings(filters: OfferingFilters) -> List[Offering]:
params: OfferingFilters = {
'category': filters['category'],
'subcategory': filters['subcategory'],
'min_quantity': filters.get('min_quantity', 0.0),
'max_quantity': filters.get('max_quantity', 9999.0),
}
query = '''
select *
from offering o1
where o1.created_at = (
select max(created_at)
from offering o2
where o2.category = o1.category
and o2.subcategory = o1.subcategory
and o2.user_account_id = o1.user_account_id
and o1.category = :category
and o1.subcategory = :subcategory
and o1.quantity_kg >= :min_quantity
and o1.quantity_kg <= :max_quantity
);
'''
result = engine.execute(sqla.text(query), params).fetchall()
return [from_result(a, Offering) for a in result]
def update_user(id: str, data: UserData) -> User:
def _parse_args() -> str:
out = ''
if 'name' in data:
out += 'name = :name,\n'
if 'user_group' in data:
out += 'user_group = :user_group,\n'
if 'email' in data:
out += 'email = :email,\n'
if 'phone_number' in data:
out += 'phone_number = :phone_number,\n'
if 'is_admin' in data:
out += 'is_admin = :is_admin,\n'
if 'password' in data:
out += 'password = :password,\n'
return out
query = f'''
update user_account
set {_parse_args()}
updated_at = :updated_at
where id = :id
returning *;
'''
params: Mapping[str, Any] = {**data, 'updated_at': get_cursor()}
result = engine.execute(sqla.text(query), params)\
.first()
return from_result(result, User) # type: ignore
def retrieve_offering_audios_by_id(ids: List[str]) -> List[OfferingAudio]:
query = '''
select *
from offering_audio
where offering_id = any(:ids);
'''
audios = engine.execute(sqla.text(query), {'ids': ids}).fetchall()
return [from_result(a, OfferingAudio) for a in audios]
def retrieve_users_by_id(ids: List[str]) -> List[User]:
query = '''
select *
from user_account
where id = any(:ids);
'''
results = engine.execute(sqla.text(query), {'ids': ids})\
.fetchall()
return [from_result(u, User) for u in results]
def retrieve_user_by_phone_number(phone_number: str) -> Optional[User]:
query = '''
select *
from user_account
where phone_number = :phone_number;
'''
result = engine.execute(sqla.text(query), {'phone_number': phone_number})\
.first()
return from_result(result, User) if result else None
def retrieve_user_by_email(email: str) -> Optional[User]:
query = '''
select *
from user_account
where email = :email;
'''
result = engine.execute(sqla.text(query), {'email': email})\
.first()
return from_result(result, User) if result else None
def retrieve_user_by_id(id: str) -> Optional[User]:
query = '''
select *
from user_account
where id = :id;
'''
result = engine.execute(sqla.text(query), {'id': id})\
.first()
return from_result(result, User) if result else None
def create_offering_audio(offering_audio: OfferingAudio):
query = '''
insert into offering_audio (
id,
created_at,
updated_at,
offering_id,
lang,
path
)
values (
:id,
:created_at,
:updated_at,
:offering_id,
:lang,
:path
)
returning *;
'''
engine.execute(sqla.text(query), asdict(offering_audio))
def retrieve_latest_offerings() -> List[Offering]:
query = '''
select *
from offering o1
where o1.created_at = (
select max(created_at)
from offering o2
where o2.category = o1.category
and o2.subcategory = o1.subcategory
and o2.user_account_id = o1.user_account_id
);
'''
offerings = engine.execute(sqla.text(query)).fetchall()
return [from_result(o, Offering) for o in offerings]
def delete_user(id: str):
query = '''
delete from user_account
where id = :id;
'''
engine.execute(sqla.text(query), {'id': id})