def get_all_room_states_for_client(
client_id: Integer) -> list('RoomState'):
session = create_session(bind=get_engine())
result_room_states = session.query(RoomState).filter(
RoomState.client == client_id).all()
session.close()
return result_room_states
def delete_room_state(room_state_id: int):
session = create_session(bind=get_engine())
session.begin()
session.query(RoomState).filter(RoomState.id == room_state_id).delete(
synchronize_session=False)
session.commit()
session.close()
def create_user(user: '******') -> Integer:
session = create_session(bind=get_engine())
session.begin()
session.add(user)
session.commit()
session.close()
return user.id
def create_client(client: 'Client') -> Integer:
session = create_session(bind=get_engine())
session.begin()
session.add(client)
session.commit()
session.close()
return client.id
def get_all_states_for_room_after(
room_id: Integer, date_after: datetime.date) -> list('RoomState'):
session = create_session(bind=get_engine())
states = session.query(RoomState).filter(
RoomState.room == room_id,
RoomState.reserved_to < date_after).all()
session.close()
return states
def update_client(client: 'Client'):
session = create_session(bind=get_engine())
session.begin()
client_database = session.query(Client).filter(
Client.id == client.id).first()
client_database.name = client.name
client_database.surname = client.surname
session.commit()
session.close()
def create_sample_data():
if get_engine().dialect.has_table(get_engine(), 'user'):
return
conn = get_engine()
create_tables(conn)
#add some client
session.begin()
for client in [
Client(name=('example_name_%s' % x),
surname=('example_surname_%s' % x)) for x in range(1, 4)
]:
session.add(client)
session.commit()
#add some user
session.begin()
for user in [
User(login=('user_%s' % x),
password=('user_%s' % x),
client=x,
permission_level=x) for x in range(1, 4)
]:
session.add(user)
session.commit()
#add some rooms
session.begin()
for room in [Room(name=('room_%s' % x)) for x in range(1, 4)]:
session.add(room)
session.commit()
#add some reservations
session.begin()
for roomstate in [
RoomState(reserved_from=datetime.date(2016, 11, 15),
reserved_to=datetime.date(2016, 11, 17 + x),
room=x,
client=3) for x in range(1, 4)
]:
session.add(roomstate)
session.commit()
def update_room_state(room_state: 'RoomState'):
session = create_session(bind=get_engine())
session.begin()
room_state_in_database = session.query(RoomState).filter(
RoomState.id == room_state.id).first()
room_state_in_database.reserved_from = room_state.reserved_from
room_state_in_database.reserved_to = room_state.reserved_to
room_state_in_database.room = room_state.room
room_state_in_database.client = room_state.client
room_state_in_database.payment = room_state.payment
session.commit()
session.close()
def check_colliding_states_for_room(room_id: Integer,
date_start: datetime.date,
date_end: datetime.date):
session = create_session(bind=get_engine())
states = session.query(RoomState).filter(
RoomState.room == room_id).filter(
or_(
and_(RoomState.reserved_from > date_start,
RoomState.reserved_from < date_end),
and_(RoomState.reserved_to > date_start,
RoomState.reserved_to < date_end),
and_(RoomState.reserved_from > date_start,
RoomState.reserved_to < date_end),
and_(RoomState.reserved_from < date_start,
RoomState.reserved_to > date_end))).all()
session.close()
return states
def persist_room_state(room_state: 'RoomState'):
session = create_session(bind=get_engine())
session.begin()
session.add(room_state)
session.commit()
session.close()
def get_room_state_by_id(room_state_id: int) -> 'RoomState':
session = create_session(bind=get_engine())
result_room_state = session.query(RoomState).filter(
RoomState.id == room_state_id).first()
session.close()
return result_room_state
def get_all_states_for_room(room_id: Integer) -> list('RoomState'):
session = create_session(bind=get_engine())
states = session.query(RoomState).filter(
RoomState.room == room_id).all()
session.close()
return states
def get_user(login: str, password: str) -> 'User':
session = create_session(bind=get_engine())
user = session.query(User).filter(User.login == login,
User.password == password).first()
session.close()
return user
# -*- coding: utf-8 -*-
import datetime
from dao import Base, get_engine
from sqlalchemy.orm import create_session
from dao.client import Client
from dao.user import User
from dao.room import Room
from dao.roomstate import RoomState
session = create_session(bind=get_engine())
def create_tables(engine):
Base.metadata.create_all(engine)
def create_sample_data():
if get_engine().dialect.has_table(get_engine(), 'user'):
return
conn = get_engine()
create_tables(conn)
#add some client
session.begin()
for client in [
Client(name=('example_name_%s' % x),
surname=('example_surname_%s' % x)) for x in range(1, 4)
]:
session.add(client)
session.commit()
def get_all_clients() -> list('Client'):
session = create_session(bind=get_engine())
clients = session.query(Client).all()
session.close()
return clients
def get_room(room_id: Integer) -> 'Room':
session = create_session(bind=get_engine())
room = session.query(Room).filter(Room.id == room_id).first()
session.close()
return room
def get_all_rooms() -> list('Room'):
session = create_session(bind=get_engine())
rooms = session.query(Room).all()
session.close()
return rooms
def get_client(client_id: Integer) -> 'Client':
session = create_session(bind=get_engine())
client = session.query(Client).filter(Client.id == client_id).first()
session.close()
return client
