async def assign_post(courier_id: CourierID):
response_ids = []
default_response = JSONResponse(status_code=status.HTTP_400_BAD_REQUEST)
db = Session()
courier_from_db = db.query(CourierSchema).get(courier_id.courier_id)
if not courier_from_db:
raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST,
detail="Courier not found")
query = db.query(OrderSchema).filter(
and_(OrderSchema.region.in_(courier_from_db.regions)),
(OrderSchema.weight <= courier_from_db.courier_type.weight())).all()
if not query:
return default_response
for order in query:
if check_courier_time_for_order(courier_from_db.working_hours,
order.delivery_hours):
order.courier_id_assigned = courier_from_db.courier_id
response_ids.append(order.order_id)
if response_ids:
db.commit()
return JSONResponse(status_code=status.HTTP_200_OK,
content={
'orders': [{
'id': id_
} for id_ in response_ids],
'assign_time':
datetime.now().isoformat()[:-4] + 'Z'
})
else:
return default_response
async def post_orders(order_list: Orders):
db = Session()
order_ids = []
order_fail_ids = []
for order in order_list.data:
db_order = OrderSchema(order_id=order.order_id,
weight=order.weight,
region=order.region,
delivery_hours=order.delivery_hours)
db.add(db_order)
try:
db.commit()
except IntegrityError:
order_fail_ids.append(order.order_id)
else:
db.refresh(db_order)
order_ids.append(order.order_id)
if len(order_fail_ids) == 0:
return JSONResponse(
content={'couriers': [{
'id': c_id
} for c_id in order_ids]},
status_code=status.HTTP_201_CREATED)
else:
return JSONResponse(content={
"validation_error": {
'couriers': [{
'id': c_id
} for c_id in order_fail_ids]
}
},
status_code=status.HTTP_400_BAD_REQUEST)
async def post_couriers(courier_list: Couriers):
db = Session()
courier_ids = []
courier_fail_ids = []
for courier in courier_list.data:
db_courier = CourierSchema(courier_id=courier.courier_id,
courier_type=courier.courier_type,
regions=courier.regions,
working_hours=courier.working_hours)
db.add(db_courier)
try:
db.commit()
except IntegrityError:
courier_fail_ids.append(courier.courier_id)
else:
db.refresh(db_courier)
courier_ids.append(courier.courier_id)
if len(courier_fail_ids) == 0:
return JSONResponse(
content={'couriers': [{
'id': c_id
} for c_id in courier_ids]},
status_code=status.HTTP_201_CREATED)
else:
return JSONResponse(content={
"validation_error": {
'couriers': [{
'id': c_id
} for c_id in courier_fail_ids]
}
},
status_code=status.HTTP_400_BAD_REQUEST)
def session_scope(self):
session = Session()
try:
yield session
session.commit()
except:
session.rollback()
raise
finally:
session.close()
async def complete_post(order: OrderDone):
db = Session()
courier_from_db = db.query(CourierSchema).get(order.courier_id)
order_from_db = db.query(OrderSchema).get(order.order_id)
if not courier_from_db or not order_from_db or courier_from_db.courier_id != order.courier_id:
raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST,
detail="Wrong courier or order id")
# TODO count rating
db.delete(order_from_db)
db.commit()
return JSONResponse(status_code=status.HTTP_200_OK,
content={'order_id': order.order_id})
async def patch_courier(courier_id: int, data: PatchCourier):
db = Session()
query = db.query(CourierSchema).get(courier_id)
if not query:
raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
detail="Courier not found")
else:
if data.courier_type and data.regions and data.working_hours:
query.regions = data.regions
db.commit()
return JSONResponse(query.to_dict(),
status_code=status.HTTP_200_OK)
else:
raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST,
detail="Wrong fields")
def today_orders_import(ctx):
"""
read orders from url and write new today orders into database
"""
url = 'https://online.moysklad.ru/api/remap/1.1/entity/customerorder'
login = '******'
password = '******'
resp = get(url, auth=HTTPBasicAuth(login, password))
get_orders = resp.json()
session_orders = Session()
session_clients = Session()
if settings.debug:
today = date(2019, 2, 15)
else:
today = datetime.today().date()
for i in get_orders['rows']:
if datetime.strptime(i['moment'], "%Y-%m-%d %H:%M:%S").date() == today:
if not session_orders.query(DenysOrders).filter(
DenysOrders.id == i['id']).all():
client_data = get(i['agent']['meta']['href'],
auth=HTTPBasicAuth(login, password)).json()
if not session_clients.query(DenysClients).filter(
DenysClients.id == client_data['id']).all():
session_clients.add(
DenysClients(
id=client_data['id'],
name=client_data['name'],
))
session_orders.add(
DenysOrders(
id=i['id'],
name=i['name'],
# description =
moment=i['moment'],
sum=i['sum'],
counterparty_id=client_data['id'],
))
session_clients.commit()
session_orders.commit()
def excel_read(ctx):
"""
read from excel file and fill the database
"""
session = Session()
session.query(DenysOrders).delete()
session.query(DenysClients).delete()
loc = '../task/ПримерМСКонтрагентыSQLAzure2.xlsx'
wb = xlrd.open_workbook(loc)
orders_sheet = wb.sheet_by_index(0)
counterparty_sheet = wb.sheet_by_index(1)
clients_list = []
for i in range(1, counterparty_sheet.nrows):
values = counterparty_sheet.row_values(i)
clients_list.append(DenysClients(
id=values[0],
name=values[1],
))
session.add_all(clients_list)
session.commit()
orders_list = []
for i in range(1, orders_sheet.nrows):
values = orders_sheet.row_values(i)
orders_list.append(
DenysOrders(
id=values[0],
name=values[1],
description=values[2],
moment=values[3],
sum=values[4],
counterparty_id=values[5],
))
session.add_all(orders_list)
session.commit()
self.seen_date = kwargs.get('creation_date', datetime.now().date())
def __str__(self):
return f'Reaction[id={self.id}, seen_date={self.seen_date}, reaction={self.reaction}, user={self.user}, quote_id={self.quote}]'
if __name__ == '__main__':
Base.metadata.create_all(engine)
session = Session()
user = User('3649028588457703', 'DAILY')
session.add(user)
quote = Quote(
"If you want to achieve greatness stop asking for permission.",
"Anonymous")
session.add(quote)
session.commit()
user_quote = Reaction('3649028588457703', quote.id, 'LIKE')
session.add(user_quote)
session.commit()
session.close()
print("=== Now querying data ===")
session = Session()
users = session.query(User).all()
for user in users:
print(user)
quotes = session.query(Quote).all()
for quote in quotes:
print(quote)
reactions = session.query(Quote.text).join(Reaction) \
.filter(Reaction.reaction == 'LIKE')
for reaction in reactions:
class Context:
def __init__(self):
Base.metadata.create_all(engine)
self.session = Session()
def getAdById(self, adId):
return self.session.query(Ad).get(adId)
def getAdByTitle(self, title):
return self.session.query(Ad).filter_by(title=title).one_or_none()
def getAdsByTags(self, tags):
return self.session.query(Ad).filter(Ad.hiddenTags.any(Tag.value.in_(tags))).all()
def addAd(self, ad):
self._adTags(ad)
try:
self.session.commit()
self.session.add(ad)
self.session.commit()
return ad
except exc.SQLAlchemyError:
print("Context: cannot create ad: ", ad)
return None
def getAds(self, limit, offset=0):
return self.session.query(Ad).limit(limit).offset(offset).all()
def updateAd(self, adId, updated):
fromDb = self.getAdById(adId)
if fromDb is None:
return None
self._removeTags(fromDb)
self._adTags(updated)
try:
fromDb.update(updated)
self.session.commit()
return fromDb
except exc.SQLAlchemyError:
print("Context: cannot remove ad: ", fromDb)
return None
def _adTags(self, ad):
try:
for tag in ad.tags:
self.session.add(tag)
except exc.SQLAlchemyError:
print("Context: cannot add hidden tags: ", ad.tags)
return None
try:
for tag in ad.hiddenTags:
self.session.add(tag)
except exc.SQLAlchemyError:
print("Context: cannot add hidden tags: ", ad.hiddenTags)
def _removeTags(self, ad):
try:
for tag in ad.tags:
self.session.query(ad_tags_association).filter_by(tag_id=tag.id).delete()
self.session.delete(tag)
self.session.commit()
except exc.SQLAlchemyError:
print("Context: cannot delete tags: ", ad.tags)
return None
try:
for tag in ad.hiddenTags:
self.session.query(hidden_ad_tags_association).filter_by(tag_id=tag.id).delete()
self.session.delete(tag)
self.session.commit()
except exc.SQLAlchemyError:
print("Context: cannot delete hidden tags: ", ad.hiddenTags)
def removeAd(self, adId):
fromDb = self.getAdById(adId)
if fromDb is None:
return False
self._removeTags(fromDb)
try:
self.session.commit()
self.session.delete(fromDb)
self.session.commit()
return True
except exc.SQLAlchemyError:
print("Context: cannot delete ad: ", fromDb)
return False
def getTagById(self, tagId):
return self.session.query(Tag).get(tagId)
def getTagByValue(self, value):
return self.session.query(Tag).filter_by(value=value).first()
def addTag(self, tag):
self.session.add(tag)
self.session.commit()
def __del__(self):
try:
self.session.close()
except AttributeError as err:
print(err)
def main():
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
models.Group.set_max_players_no(5)
session = Session()
address_1 = models.Address("city", "postcode", "street", 2)
address2 = models.Address("city", "postcode", "street", 1)
person = models.Person(first_name="Jan", last_name="Kowalski")
owner_1 = models.EscapeRoomOwnerPerson(username="******",
password="******",
address=address2,
er_owner_person=person)
owner_2 = models.EscapeRoomOwnerCompany(username="******",
password="******",
name="Escapers Inc.",
establishment_date=date(
2020, 1, 1),
address=address_1)
owner_3 = models.EscapeRoomOwnerCompany(username="******",
password="******",
name="Crazytown",
establishment_date=date(
2020, 1, 1),
address=address_1)
escape_room1 = models.FixedPriceEscapeRoom(
name="W krainie zła",
opening_date=date(2020, 5, 1),
category=models.EscapeRoomCategory.THRILLER,
min_players_no=1,
max_players_no=5,
price=80,
owner=owner_1)
escape_room2 = models.VariablePriceEscapeRoom(
name="Dziki zachód",
opening_date=date(2020, 5, 1),
category=models.EscapeRoomCategory.HORROR,
min_players_no=1,
max_players_no=5,
max_price=15,
price=80,
owner=owner_3)
escape_room3 = models.WeekendPriceEscapeRoom(
name="Wszyscy ludzie prezydenta",
opening_date=date(2020, 5, 1),
category=models.EscapeRoomCategory.CRIME,
min_players_no=1,
max_players_no=5,
weekend_price=90,
price=80,
owner=owner_2)
escape_room4 = models.FixedPriceEscapeRoom(
name="Uprowadzeni przez wampira",
opening_date=date(2019, 1, 1),
closing_date=date(2020, 5, 3),
category=models.EscapeRoomCategory.THRILLER,
min_players_no=1,
max_players_no=5,
price=80,
owner=owner_2)
escape_room5 = models.FixedPriceEscapeRoom(
name="Rycerze",
opening_date=date(2020, 5, 1),
category=models.EscapeRoomCategory.HISTORY,
min_players_no=1,
max_players_no=5,
price=80,
owner=owner_2)
escape_room6 = models.FixedPriceEscapeRoom(
name="Subnautica",
opening_date=date(2020, 5, 1),
category=models.EscapeRoomCategory.ADVENTURE,
min_players_no=1,
max_players_no=5,
price=80,
owner=owner_1)
group1 = create_group()
player1 = models.Player(username="******",
password="******",
person=models.Person(first_name="Sandra",
last_name="Rawicz"))
player2 = models.Player(username="******",
password="******",
person=models.Person(first_name="Anna",
last_name="Barańska"))
player3 = models.Player(username="******",
password="******",
person=models.Person(first_name="Jan",
last_name="Nowacki"))
group2 = models.Group(name="Ja i mój chłopak", players=[player1])
group3 = models.Group(name="Znajomi z pracy", players=[player1, player2])
visit1 = models.Visit(group=group2,
escape_room=escape_room2,
visit_date=date(2020, 6, 19),
duration=61,
rating=3)
visit2 = models.Visit(group=group3,
escape_room=escape_room6,
visit_date=date(2020, 6, 16),
duration=50,
rating=5)
recommendation1 = models.Recommendation(player=player1,
escape_room=escape_room1,
expected_rating=4)
recommendation2 = models.Recommendation(player=player2,
escape_room=escape_room2,
expected_rating=5)
recommendation3 = models.Recommendation(player=player1,
escape_room=escape_room3,
expected_rating=3)
objects = [
escape_room1, escape_room2, escape_room3, escape_room4, escape_room5,
escape_room6, player3, group1, group2, group3, visit1, visit2,
recommendation1, recommendation2, recommendation3
]
session.add(player1)
try:
session.commit()
except Exception as ex:
print(ex)
for obj in objects:
session.add(obj)
try:
session.commit()
except Exception as ex:
print(ex)
print(person.player)
print(person.er_owner)
print(escape_room5.get_rating())