def query_packets_with_signed():
"""
Query the database and return a list of currently open packets and the number of signatures they currently have
:return: a list of results: intro members with open packets, their name, username, and number of signatures received
"""
try:
return db.engine.execute("""
SELECT packets.username AS username, packets.name AS name, packets.onfloor AS onfloor,
coalesce(packets.sigs_recvd, 0) AS received
FROM ( ( SELECT freshman.rit_username
AS username, freshman.name AS name, freshman.onfloor
AS onfloor, packet.id AS id, packet.start AS start, packet.end AS end
FROM freshman INNER JOIN packet ON freshman.rit_username = packet.freshman_username) AS a
LEFT JOIN ( SELECT totals.id AS id, coalesce(sum(totals.signed), 0) AS sigs_recvd
FROM ( SELECT packet.id AS id, coalesce(count(signature_fresh.signed), 0) AS signed
FROM packet FULL OUTER JOIN signature_fresh ON signature_fresh.packet_id = packet.id
WHERE signature_fresh.signed = TRUE AND packet.start < now() AND now() < packet.end
GROUP BY packet.id
UNION SELECT packet.id AS id, coalesce(count(signature_upper.signed), 0) AS signed FROM packet
FULL OUTER JOIN signature_upper ON signature_upper.packet_id = packet.id
WHERE signature_upper.signed = TRUE AND packet.start < now() AND now() < packet.end
GROUP BY packet.id ) totals GROUP BY totals.id ) AS b ON a.id = b.id ) AS packets
WHERE packets.start < now() AND now() < packets.end;
""")
except exc.SQLAlchemyError:
raise exc.SQLAlchemyError("Error: Failed to get open packets with signatures received from database")
def test_add_tasting_fail(admin_client):
with patch('juleol.db.db.session') as SessionMock:
SessionMock.commit.side_effect = exc.SQLAlchemyError()
ret = admin_client.post('/admin/', data={'year': 2000, 'beers': 2})
assert ret.status_code == 200
assert b'Error creating tasting' in ret.data
assert SessionMock.mock_calls[4][0] == 'rollback'
def test_delete_beer_heat(admin_client):
with patch('juleol.db.Beers') as BeersMock: # noqa: F841
db.Beers.query.filter.return_value.first.return_value = None
ret = admin_client.delete('/admin/beer/1/heat')
assert ret.status_code == 404
assert json.loads(ret.data)['error'] == 'Invalid beer id'
with patch('juleol.db.db.session') as SessionMock:
test_beer = db.Beers()
test_beer.id = 1
test_beer.number = 1
test_beer.name = 'test beer 1'
test_beer.heat = 1
db.Beers.query.filter.return_value.first.return_value = test_beer
ret = admin_client.delete('/admin/beer/1/heat')
assert ret.status_code == 200
assert json.loads(ret.data)['message'] == 'Beer heat deleted'
assert SessionMock.mock_calls[0][0] == 'add'
assert SessionMock.mock_calls[0][1] == (test_beer, )
assert SessionMock.mock_calls[1][0] == 'commit'
assert test_beer.heat is None
SessionMock.commit.side_effect = exc.SQLAlchemyError()
SessionMock.reset_mock()
ret = admin_client.delete('/admin/beer/1/heat')
assert ret.status_code == 500
assert json.loads(ret.data)['error'] == 'Error deleting beer heat'
assert SessionMock.mock_calls[2][0] == 'rollback'
def createValidEngine(uri_list):
"""
Create DB engine if connection is valid
Attempts each uri in the list until a valid connection is made
:param uri_list: list of connection uri's
:return: DB engine object
:raise: SQLAlchemyError if all connections fail
"""
errors = []
for conn_uri in uri_list:
try:
db_engine = create_engine(conn_uri, echo=True, pool_recycle=10, isolation_level="READ UNCOMMITTED",
connect_args={"connect_timeout": 5})
# test connection
_ = db_engine.connect()
# conn good return it
return db_engine
except Exception as ex:
errors.append(ex)
# we failed to return good connection raise exceptions
if settings.DEBUG:
for ex in errors:
debugException(ex, log_ex=False, print_ex=True, showstack=False)
try:
raise sql_exceptions.SQLAlchemyError(errors)
except:
raise Exception(errors)
def test_update_tasting(admin_client):
with patch('juleol.db.Tastings') as TastingsMock:
with patch('juleol.db.db.session') as SessionMock:
test_tasting = db.Tastings()
test_tasting.year = 2000
test_tasting.locked = False
TastingsMock.query.filter.return_value.first.return_value = None
ret = admin_client.put('/admin/tasting/2001',
data={"locked": "true"})
assert ret.status_code == 404
assert b'Invalid year' in ret.data
TastingsMock.query.filter.return_value.first.return_value = test_tasting
ret = admin_client.put('/admin/tasting/2000',
data={"invalid": "123"})
assert ret.status_code == 400
assert b'Invalid argument' in ret.data
ret = admin_client.put('/admin/tasting/2000',
data={"locked": "true"})
assert ret.status_code == 200
assert test_tasting.locked == True
assert SessionMock.mock_calls[0][0] == 'add'
assert SessionMock.mock_calls[0][1] == (test_tasting, )
assert SessionMock.mock_calls[1][0] == 'commit'
SessionMock.reset_mock()
SessionMock.commit.side_effect = exc.SQLAlchemyError()
ret = admin_client.put('/admin/tasting/2000',
data={"locked": "true"})
assert ret.status_code == 500
assert b'Error updating tasting' in ret.data
assert SessionMock.mock_calls[2][0] == 'rollback'
def get(self, record_id):
try:
record = self.model.query.filter_by(id=record_id).first()
if not record:
raise exc.SQLAlchemyError('Not Found')
return record
except exc.SQLAlchemyError:
self.session.rollback()
raise
def test_update_participant(admin_client):
with patch('juleol.db.Tastings') as TastingsMock:
test_tasting = db.Tastings()
test_tasting.year = 2000
TastingsMock.query.filter.return_value.first.return_value = test_tasting
TastingsMock.query.filter.return_value.first.return_value = None
ret = admin_client.post('/admin/2000/participant/1')
assert ret.status_code == 302
ret = admin_client.get('/admin/')
assert b'Invalid year' in ret.data
TastingsMock.query.filter.return_value.first.return_value = test_tasting
with patch('juleol.db.Participants') as ParticipantsMock:
ParticipantsMock.query.filter.return_value.filter.return_value.first.return_value = None
ret = admin_client.post('/admin/2000/participant/1')
assert ret.status_code == 302
assert ret.headers['Location'] == 'http://localhost/admin/2000'
ret = admin_client.get('/admin/2000')
assert b'Invalid participant' in ret.data
test_participant = db.Participants()
test_participant.tasting = test_tasting
ParticipantsMock.query.filter.return_value.filter.return_value.first.return_value = test_participant
ret = admin_client.post('/admin/2000/participant/1')
assert ret.status_code == 302
assert ret.headers['Location'] == 'http://localhost/admin/2000'
ret = admin_client.get('/admin/2000')
assert b'Invalid form data' in ret.data
ret = admin_client.post('/admin/2000/participant/1',
data={'email': 'email-invalid'})
assert ret.status_code == 302
assert ret.headers['Location'] == 'http://localhost/admin/2000'
ret = admin_client.get('/admin/2000')
assert b'Invalid form data' in ret.data
with patch('juleol.db.db.session') as SessionMock:
ret = admin_client.post(
'/admin/2000/participant/1',
data={'email': '*****@*****.**'})
assert ret.status_code == 302
assert ret.headers['Location'] == 'http://localhost/admin/2000'
assert test_participant.email == '*****@*****.**'
assert SessionMock.mock_calls[0][0] == 'add'
assert SessionMock.mock_calls[0][1] == (test_participant, )
assert SessionMock.mock_calls[1][0] == 'commit'
SessionMock.reset_mock()
SessionMock.commit.side_effect = exc.SQLAlchemyError()
ret = admin_client.post('/admin/2000/participant/1',
data={'email': '*****@*****.**'})
assert ret.status_code == 302
assert ret.headers['Location'] == 'http://localhost/admin/2000'
ret = admin_client.get('/admin/2000')
assert b'Error updating email' in ret.data
assert SessionMock.mock_calls[2][0] == 'rollback'
def set_response(id, retval, retry):
try:
engine = create_engine(SQLALCHEMY_DATABASE_URI)
engine.execute("UPDATE rbz_api SET Response = %s WHERE Id = %s",
(retval, str(id)))
except exc.SQLAlchemyError(e):
print("No entry in Database with ID: " + str(id))
print(e)
if retry:
set_response(id, retval, False)
def _check_EPD_con(con: Engine) -> None:
"""Check EPD connection object works.
Use simple example query which should be achievable with any instance of
the EPD.
"""
try:
pd.read_sql_query('select * from entity limit10;', con=con)
except exc.SQLAlchemyError as e:
logging.error('Could not connect to the EPD with the supplied engine.')
raise exc.SQLAlchemyError(e)
logging.info('Test query successfully run against EPD.')
def test_ibm_db_sa_raise_if_duplicate_entry_error_no_match(self):
# Tests that the session._raise_if_duplicate_entry_error method
# does not raise a DBDuplicateEntry exception when it's not a matching
# integrity error.
statement = ('ALTER TABLE instance_types ADD CONSTRAINT '
'uniq_name_x_deleted UNIQUE (name, deleted)')
params = None
orig = sqla_exc.SQLAlchemyError(
'SQL0542N The column named "NAME" cannot be a column of a '
'primary key or unique key constraint because it can contain null '
'values.')
integrity_error = sqla_exc.IntegrityError(statement, params, orig)
session._raise_if_duplicate_entry_error(integrity_error, 'ibm_db_sa')
def get_misc_signatures():
packet_misc_sigs = {}
try:
result = db.engine.execute("""
SELECT packet.freshman_username AS username, count(signature_misc.member) AS signatures FROM packet
RIGHT OUTER JOIN signature_misc ON packet.id = signature_misc.packet_id
GROUP BY packet.freshman_username;
""")
for packet in result:
packet_misc_sigs[packet.username] = packet.signatures
except exc.SQLAlchemyError:
raise exc.SQLAlchemyError(
"Error: Unable to query miscellaneous signatures from database")
return packet_misc_sigs
def query_signed_upperclassman(member):
"""
Query the database and return the list of packets signed by the given upperclassman
:param member: the user making the query
:return: list of results matching the query
"""
s = text("SELECT DISTINCT packet.freshman_username AS username, signature_upper.signed AS signed FROM packet "
"INNER JOIN signature_upper ON packet.id = signature_upper.packet_id "
"WHERE signature_upper.member = :member;")
try:
return db.engine.execute(s, member=member)
except exc.SQLAlchemyError:
raise exc.SQLAlchemyError("Error: Failed to get upperclassman's signatures from database")
def query_signed_alumni(member):
"""
Query the database and return the list of packets signed by the given alumni/off-floor
:param member: the user making the query
:return: list of results matching the query
"""
s = text("SELECT DISTINCT packet.freshman_username AS username, signature_misc.member AS signed FROM packet "
"LEFT OUTER JOIN signature_misc ON packet.id = signature_misc.packet_id "
"WHERE signature_misc.member = :member OR signature_misc.member ISNULL;")
try:
return db.engine.execute(s, member=member)
except exc.SQLAlchemyError:
raise exc.SQLAlchemyError("Error: Failed to get alumni's signatures from database")
def _check_unicode_returns(self, connection):
return True
# XXX Lately this hangs the database:
cursor = self._get_raw_cursor(connection)
item = cursor.columns('SYSTABLES').fetchone()
try:
item[1].encode('ascii')
except UnicodeEncodeError, e:
# In Linux, if the connection has been opened with
# unicode_results=True and an unpatched pyodbc (see
# progress_sa distribution) then the Progress ODBC driver
# returns UTF-8 interpreted by pyodbc as UCS-2.
# Things go downhill from there unless we abort.
raise exc.SQLAlchemyError(
"Non-ASCII in SYSTABLES. "
"Are you using an unpatched pyodbc?", )
def test_get_update_note(admin_client):
with patch('juleol.db.Notes') as NotesMock: # noqa: F841
db.Notes.query.filter.return_value.first.return_value = None
ret = admin_client.get('/admin/note/1')
assert ret.status_code == 404
assert json.loads(ret.data)['error'] == 'Invalid note id'
test_note = db.Notes()
test_note.id = 1
test_note.note = 'test'
db.Notes.query.filter.return_value.first.return_value = test_note
ret = admin_client.get('/admin/note/1')
assert ret.status_code == 200
data = json.loads(ret.data)
assert data['id'] == 1
assert data['note'] == 'test'
with patch('juleol.db.db.session') as SessionMock:
ret = admin_client.put('/admin/note/1')
assert ret.status_code == 400
assert json.loads(ret.data)['error'] == 'Invalid arguments'
ret = admin_client.put('/admin/note/1', data={'note': 'new test'})
assert ret.status_code == 200
assert json.loads(ret.data)['message'] == 'Note updated'
assert test_note.note == 'new test'
assert SessionMock.mock_calls[0][0] == 'add'
assert SessionMock.mock_calls[0][1] == (test_note, )
assert SessionMock.mock_calls[1][0] == 'commit'
SessionMock.reset_mock()
ret = admin_client.delete('/admin/note/1')
assert ret.status_code == 200
assert json.loads(ret.data)['message'] == 'Note deleted'
assert SessionMock.mock_calls[0][0] == 'delete'
assert SessionMock.mock_calls[0][1] == (test_note, )
assert SessionMock.mock_calls[1][0] == 'commit'
SessionMock.reset_mock()
SessionMock.commit.side_effect = exc.SQLAlchemyError()
ret = admin_client.put('/admin/note/1', data={'note': 'new test'})
assert ret.status_code == 500
assert json.loads(ret.data)['error'] == 'Error updating note'
ret = admin_client.delete('/admin/note/1')
assert ret.status_code == 500
assert json.loads(ret.data)['error'] == 'Error deleting note'
assert SessionMock.mock_calls[2][0] == 'rollback'
def test_ibm_db_sa_raise_if_duplicate_entry_error_duplicate(self):
# Tests that the session._raise_if_duplicate_entry_error method
# translates the duplicate entry integrity error for the DB2 engine.
statement = ('INSERT INTO key_pairs (created_at, updated_at, '
'deleted_at, deleted, name, user_id, fingerprint) VALUES '
'(?, ?, ?, ?, ?, ?, ?)')
params = ['20130918001123627099', None, None, 0, 'keypair-23474772',
'974a7c9ffde6419f9811fcf94a917f47',
'7d:2c:58:7f:97:66:14:3f:27:c7:09:3c:26:95:66:4d']
orig = sqla_exc.SQLAlchemyError(
'SQL0803N One or more values in the INSERT statement, UPDATE '
'statement, or foreign key update caused by a DELETE statement are'
' not valid because the primary key, unique constraint or unique '
'index identified by "2" constrains table "NOVA.KEY_PAIRS" from '
'having duplicate values for the index key.')
integrity_error = sqla_exc.IntegrityError(statement, params, orig)
self.assertRaises(db_exc.DBDuplicateEntry,
session._raise_if_duplicate_entry_error,
integrity_error, 'ibm_db_sa')
def test_create_note(admin_client):
with patch('juleol.db.Tastings') as TastingsMock:
test_tasting = db.Tastings()
test_tasting.year = 2000
TastingsMock.query.filter.return_value.first.return_value = test_tasting
TastingsMock.query.filter.return_value.first.return_value = None
ret = admin_client.post('/admin/2000/note')
assert ret.status_code == 302
ret = admin_client.get('/admin/')
assert b'Invalid year' in ret.data
TastingsMock.query.filter.return_value.first.return_value = test_tasting
with patch('juleol.db.Notes') as NotesMock:
ret = admin_client.post('/admin/2000/note')
assert ret.status_code == 302
assert ret.headers['Location'] == 'http://localhost/admin/2000'
ret = admin_client.get('/admin/2000')
assert b'Invalid form data' in ret.data
with patch('juleol.db.db.session') as SessionMock:
ret = admin_client.post('/admin/2000/note',
data={'note': 'test'})
assert ret.status_code == 302
assert ret.headers['Location'] == 'http://localhost/admin/2000'
assert NotesMock.mock_calls[0][2]['note'] == 'test'
assert NotesMock.mock_calls[0][2]['tasting'] == test_tasting
assert SessionMock.mock_calls[0][0] == 'add'
assert SessionMock.mock_calls[0][1] == (db.Notes(
note='test', tasting=test_tasting), )
assert SessionMock.mock_calls[1][0] == 'commit'
SessionMock.reset_mock()
SessionMock.commit.side_effect = exc.SQLAlchemyError()
ret = admin_client.post('/admin/2000/note',
data={'note': 'test'})
assert ret.status_code == 302
assert ret.headers['Location'] == 'http://localhost/admin/2000'
ret = admin_client.get('/admin/2000')
assert b'Error creating note' in ret.data
assert SessionMock.mock_calls[2][0] == 'rollback'
def createValidEngine(uri_list):
"""
Create DB engine if connection is valid
Attempts each uri in the list until a valid connection is made
This method uses a singleton pattern and returns db_engine if created
:param uri_list: list of connection uri's
:return: DB engine object
:raise: SQLAlchemyError if all connections fail
"""
if 'db_engine' in globals():
return globals()['db_engine']
errors = []
for conn_uri in uri_list:
try:
db_engine = create_engine(conn_uri,
echo=True,
pool_recycle=300,
pool_size=10,
isolation_level="READ UNCOMMITTED",
connect_args={"connect_timeout": 5})
# test connection
_ = db_engine.connect()
# conn good return it
return db_engine
except Exception as ex:
errors.append(ex)
# we failed to return good connection raise exceptions
if settings.DEBUG:
for ex in errors:
debugException(ex)
try:
raise sql_exceptions.SQLAlchemyError(errors)
except:
raise Exception(errors)
def raises_sql_exc_no_args(msg):
raise exc.SQLAlchemyError(msg)
def raises_sql_exc(msg):
raise exc.SQLAlchemyError(msg)
def test_put_rate_beer(client_authorized):
ret = client_authorized.post("/login", data={'year': '2000'})
ret = client_authorized.get('/login')
with patch('juleol.db.Beers') as MockBeers: # noqa: F841
with patch('juleol.db.ScoreTaste') as MockScoreTaste: # noqa: F841
with patch('juleol.db.ScoreAftertaste'
) as MockScoreAfterTaste: # noqa: F841
with patch(
'juleol.db.ScoreLook') as MockScoreLook: # noqa: F841
with patch('juleol.db.ScoreSmell'
) as MockScoreSmell: # noqa: F841
with patch('juleol.db.ScoreXmas'
) as MockScoreXmas: # noqa: F841
test_beer = db.Beers()
test_beer.id = 1
test_beer.name = 'test'
db.Beers.query.filter.return_value.filter.return_value.first.return_value = test_beer
test_score = MagicMock()
test_score.score = 0
db.ScoreTaste.query.filter.return_value.filter.return_value.first.return_value = test_score
db.ScoreAftertaste.query.filter.return_value.filter.return_value.first.return_value = test_score
db.ScoreLook.query.filter.return_value.filter.return_value.first.return_value = test_score
db.ScoreSmell.query.filter.return_value.filter.return_value.first.return_value = test_score
db.ScoreXmas.query.filter.return_value.filter.return_value.first.return_value = test_score
test_beer.tasting.locked = True
ret = client_authorized.put('/rate/2000/1',
data={'look': 10})
assert ret.status_code == 403
assert b'Tasting is locked' in ret.data
test_beer.tasting.locked = False
ret = client_authorized.put('/rate/2000/1',
data={'look': 'bogus'})
assert ret.status_code == 400
assert b'Not a valid integer value' in ret.data
ret = client_authorized.put('/rate/2000/1',
data={'smell': 10})
assert ret.status_code == 400
assert b'Number must be between' in ret.data
with patch('juleol.db.db.session') as SessionMock:
ret = client_authorized.put('/rate/2000/1',
data={'taste': 1})
assert test_score.score == 1
ret = client_authorized.put(
'/rate/2000/1', data={'aftertaste': 3})
assert test_score.score == 3
ret = client_authorized.put('/rate/2000/1',
data={'smell': 2})
assert test_score.score == 2
ret = client_authorized.put('/rate/2000/1',
data={'look': 1})
assert test_score.score == 1
ret = client_authorized.put('/rate/2000/1',
data={'xmas': 2})
assert test_score.score == 2
SessionMock.commit.side_effect = exc.SQLAlchemyError(
)
ret = client_authorized.put('/rate/2000/1',
data={'xmas': 2})
assert ret.status_code == 500
assert b'Error updating scores' in ret.data
def search_table(self):
region_id = self.request.args.get("region_id", -1)
if region_id:
region_id = int(region_id)
if region_id != -1:
self.q = self.q.filter(Patient.region_id == region_id)
self.search_form.region_id.default = region_id
self.search_params.append(
(_("Регион"),
Region.query.filter_by(id=region_id).first().name))
filt = dict()
job_category_id = self.request.args.get("job_category_id", "-1")
if job_category_id != "-1":
job_category_disp_name = _("Неизвестно")
if job_category_id == "None":
job_category_id = None
else:
job_category_id = int(job_category_id)
job_category_disp_name = JobCategory.query.filter_by(
id=job_category_id).first().name
self.search_params.append(
(_("Категория Работы"), job_category_disp_name))
self.q = self.q.filter(Patient.job_category_id == job_category_id)
self.search_form.job_category_id.default = job_category_id
is_found = self.request.args.get("is_found", "-1")
if is_found != "-1":
filt["is_found"] = is_found == "1"
self.search_form.is_found.default = is_found
self.search_params.append(
(_("Найден"), _("Да") if is_found == "1" else _("Нет")))
is_currently_infected = self.request.args.get("is_currently_infected",
"-1")
if is_currently_infected != "-1":
filt["is_infected"] = is_currently_infected == "1"
self.search_form.is_currently_infected.default = is_currently_infected
self.search_params.append(
(_("Инфицирован"),
_("Да") if filt["is_infected"] else _("Нет")))
def name_search(param, param_str, q, param_disp_name):
if param_str in request.args:
req_str = request.args[param_str]
if req_str:
q = q.filter(func.lower(param).contains(req_str.lower()))
param = getattr(self.search_form, param_str, None)
if param:
setattr(param, 'default', req_str)
self.search_params.append((param_disp_name, req_str))
return q
self.q = name_search(Patient.first_name, "first_name", self.q,
_("Имя"))
self.q = name_search(Patient.second_name, "second_name", self.q,
_("Фамилия"))
self.q = name_search(Patient.patronymic_name, "patronymic_name",
self.q, _("Отчество"))
iin = request.args.get("iin", "")
if iin:
self.q = self.q.filter(Patient.iin.contains(iin))
self.search_form.iin.default = iin
self.search_params.append((_("ИИН"), iin))
pass_num = request.args.get("pass_num", "")
if pass_num:
self.q = self.q.filter(Patient.pass_num.contains(pass_num))
self.search_form.pass_num.default = pass_num
self.search_params.append((_("Номер Паспорта"), pass_num))
travel_type = request.args.get("travel_type", c.all_travel_types[0])
if travel_type and travel_type != c.all_travel_types[0]:
try:
travel_type_query = TravelType.query.filter_by(
value=travel_type).first()
travel_type_id = travel_type_query.id
except (exc.SQLAlchemyError, AttributeError):
raise exc.SQLAlchemyError(
"Travel Type Error - {}".format(travel_type))
if travel_type_id:
filt["travel_type_id"] = travel_type_id
self.search_form.travel_type.default = travel_type
self.search_params.append(
(_("Тип Въезда"), travel_type_query.name))
# Created_date range
date_range_start = request.args.get("date_range_start", None)
if date_range_start:
date_range_start = parse_date(date_range_start)
self.q = self.q.filter(Patient.created_date >= date_range_start)
self.search_form.date_range_start.default = date_range_start
self.search_params.append((_("Дата Создания (Начало)"),
date_range_start.strftime('%Y-%m-%d')))
date_range_end = request.args.get("date_range_end", None)
if date_range_end:
date_range_end = parse_date(date_range_end)
self.q = self.q.filter(Patient.created_date <= date_range_end)
self.search_form.date_range_end.default = date_range_end
self.search_params.append((_("Дата Создания (Конец)"),
date_range_end.strftime('%Y-%m-%d')))
self.q = self.q.filter_by(**filt)
# State search
patient_state = self.request.args.get("patient_state", "-1")
if patient_state != "-1":
# State Filter
patient_state_val = State.query.filter_by(
value=patient_state).first()
patient_state_id = patient_state_val.id
patient_state_disp_name = patient_state_val.name
self.q = self.q.join(PatientState,
PatientState.patient_id == Patient.id)
self.q = self.q.filter(PatientState.state_id == patient_state_id)
self.search_form.patient_state.default = patient_state
# State Date
# State Date Start
state_date_range_start = request.args.get("state_date_range_start",
None)
if state_date_range_start:
state_date_range_start = parse_date(state_date_range_start)
self.q = self.q.filter(
PatientState.detection_date >= state_date_range_start)
self.search_form.state_date_range_start.default = state_date_range_start
patient_state_disp_name = "{} {}".format(
patient_state_disp_name,
state_date_range_start.strftime('%Y-%m-%d'))
# State Date End
state_date_range_end = request.args.get("state_date_range_end",
None)
if state_date_range_end:
state_date_range_end = parse_date(state_date_range_end)
self.q = self.q.filter(
PatientState.detection_date <= state_date_range_end)
self.search_form.state_date_range_end.default = state_date_range_end
patient_state_disp_name = "{}:{}".format(
patient_state_disp_name,
state_date_range_end.strftime('%Y-%m-%d'))
self.search_params.append(
(_("Статус Пациента"), patient_state_disp_name))
# State Count
# State Count Min
state_count_min = request.args.get("state_count_min", None)
if state_count_min:
self.q = self.q.having(
func.count(PatientState.id) >= state_count_min)
self.search_form.state_count_min.default = state_count_min
self.search_params.append(
(_("Минимум Статусов"), state_count_min))
# State Count Max
state_count_max = request.args.get("state_count_max", None)
if state_count_max:
self.q = self.q.having(
func.count(PatientState.id) <= state_count_max)
self.search_form.state_count_max.default = state_count_max
self.search_params.append(
(_("Максимум Статусов"), state_count_max))
# State Infec
if patient_state_val.value == c.state_infec[0]:
state_infec_type = request.args.get("state_infec_type", "None")
if state_infec_type != "None":
param = "state_infec_type"
self.q = self.q.filter(
text(
"CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'"
.format(param, state_infec_type)))
self.search_form.state_infec_type.default = state_infec_type
self.search_params.append(
(_("Инфицирован - Тип"),
dict(c.state_infec_types)[state_infec_type]))
state_infec_illness_symptoms = request.args.get(
"state_infec_illness_symptoms", "None")
if state_infec_illness_symptoms != "None":
param = "state_infec_illness_symptoms"
self.q = self.q.filter(
text(
"CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'"
.format(param, state_infec_illness_symptoms)))
self.search_form.state_infec_illness_symptoms.default = state_infec_illness_symptoms
self.search_params.append(
(_("Инфицирован - Симптомы"), dict(
c.illness_symptoms)[state_infec_illness_symptoms]))
state_infec_illness_severity = request.args.get(
"state_infec_illness_severity", "None")
if state_infec_illness_severity != "None":
param = "state_infec_illness_severity"
self.q = self.q.filter(
text(
"CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'"
.format(param, state_infec_illness_severity)))
self.search_form.state_infec_illness_severity.default = state_infec_illness_severity
self.search_params.append((
_("Инфицирован - Тяжесть Болезни"),
dict(
c.illness_severity)[state_infec_illness_severity]))
# State Dead
if patient_state_val.value == c.state_dead[0]:
state_dead_reason = request.args.get("state_dead_reason",
"None")
if state_dead_reason != "None":
param = "state_dead_reason"
self.q = self.q.filter(
text(
"CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'"
.format(param, state_dead_reason)))
self.search_form.state_dead_reason.default = state_dead_reason
self.search_params.append(
(_("Умер - Причина Смерти"),
dict(c.death_reasons)[state_dead_reason]))
self.q = self.q.group_by(Patient.id)
# Is contacted
contacted = self.request.args.get("contacted", "-1")
if contacted != "-1":
contacted_disp_name = None
if contacted == "contacted":
self.q = self.q.join(
ContactedPersons,
ContactedPersons.contacted_patient_id == Patient.id)
self.q = self.q.group_by(Patient.id)
contacted_disp_name = _("Контактный")
elif contacted == "with_contacts":
self.q = self.q.join(
ContactedPersons,
ContactedPersons.infected_patient_id == Patient.id)
self.q = self.q.group_by(Patient.id)
contacted_disp_name = _("С Контактами")
elif contacted == "contacted_close":
self.q = self.q.join(
ContactedPersons,
ContactedPersons.contacted_patient_id == Patient.id)
self.q = self.q.filter(
ContactedPersons.is_potential_contact == False)
self.q = self.q.group_by(Patient.id)
contacted_disp_name = _("Контактный (БК)")
elif contacted == "contacted_potential":
self.q = self.q.join(
ContactedPersons,
ContactedPersons.contacted_patient_id == Patient.id)
self.q = self.q.filter(
ContactedPersons.is_potential_contact == True)
self.q = self.q.group_by(Patient.id)
contacted_disp_name = _("Контактный (ПК)")
self.search_form.contacted.default = contacted
self.search_params.append((_("Контакты"), contacted_disp_name))
is_iin_fail = request.args.get("is_iin_fail", None)
if is_iin_fail:
if is_iin_fail == "is_iin_empty":
self.q = self.q.filter_by(iin='')
self.search_form.is_iin_fail.default = "is_iin_empty"
elif is_iin_fail == "is_iin_invalid":
self.q = self.q.filter(Patient.iin != '')
self.q = self.q.filter(func.length(Patient.iin) != 12)
self.search_form.is_iin_fail.default = "is_iin_invalid"
elif is_iin_fail == "is_iin_valid":
self.q = self.q.filter(func.length(Patient.iin) == 12)
self.search_form.is_iin_fail.default = "is_iin_valid"
address = self.request.args.get("address", None)
if address:
self.q = self.q.join(Address,
Patient.home_address_id == Address.id)
self.q = self.q.join(Country, Country.id == Address.country_id)
self.q = self.q.group_by(Patient.id)
self.q = self.q.filter(
func.lower(
func.concat(Country.name, ' ', Address.city, ' ',
Address.street, ' ', Address.house, ' ',
Address.flat)).contains(address.lower()))
self.search_form.address.default = address
self.search_params.append((_("Адрес"), address))
current_country = Country.query.filter_by(
code=c.current_country).first()
home_address_location_type_id = self.request.args.get(
"home_address_location_type_id", "-1")
if home_address_location_type_id != "-1":
location_type = AddressLocationType.query.filter_by(
id=home_address_location_type_id).first()
self.q = self.q.join(Address,
Patient.home_address_id == Address.id)
self.q = self.q.group_by(Patient.id)
self.q = self.q.filter(
Address.location_type_id == home_address_location_type_id)
self.search_form.home_address_location_type_id.default = home_address_location_type_id
self.search_params.append((_("Село/Город"), location_type))
if travel_type and travel_type != c.all_travel_types[0]:
# FlightTravel
if travel_type_query.value == c.flight_type[0]:
self.q = self.q.join(FlightTravel)
flight_code_id = request.args.get("flight_code_id", None)
if flight_code_id != None:
self.q = self.q.filter(
FlightTravel.flight_code_id == flight_code_id)
self.search_params.append((_("Номер Рейса"),
FlightCode.query.filter_by(
id=flight_code_id).first()))
travel_in_out = request.args.get("travel_departure_outer",
"all_travel")
if travel_in_out != "all_travel":
self.q = self.q.join(
FlightCode,
FlightTravel.flight_code_id == FlightCode.id)
if travel_in_out == "outer_travel":
self.q = self.q.filter(
FlightCode.from_country != current_country)
elif travel_in_out == "domestic_travel":
self.q = self.q.filter(
FlightCode.from_country == current_country)
self.search_form.travel_departure_outer.default = travel_in_out
# TrainTravel
elif travel_type_query.value == c.train_type[0]:
self.q = self.q.join(TrainTravel)
train_id = request.args.get("train_id", None)
if train_id != None:
self.q = self.q.filter(TrainTravel.train_id == train_id)
travel_in_out = request.args.get("travel_departure_outer",
"all_travel")
if travel_in_out != "all_travel":
self.q = self.q.join(Train,
TrainTravel.train_id == Train.id)
if travel_in_out == "outer_travel":
self.q = self.q.filter(
Train.from_country != current_country)
elif travel_in_out == "domestic_travel":
self.q = self.q.filter(
Train.from_country == current_country)
self.search_form.travel_departure_outer.default = travel_in_out
self.search_params.append(
(_("Поезд"),
Train.query.filter_by(id=train_id).first()))
# Blockpost
elif travel_type_query.value == c.blockpost_type[0]:
self.q = self.q.join(BlockpostTravel)
arrival_date = request.args.get("arrival_date", None)
if arrival_date:
self.q = self.q.filter(
BlockpostTravel.date == arrival_date)
self.search_form.arrival_date.default = parse_date(
arrival_date)
blockpost_region_id = request.args.get("blockpost_region_id",
"-1")
if blockpost_region_id != "-1":
self.q = self.q.filter(
BlockpostTravel.region_id == blockpost_region_id)
self.search_form.blockpost_region_id.default = blockpost_region_id
self.search_params.append(
(_("Блокпост"),
Region.query.filter_by(
id=blockpost_region_id).first().name))
# Auto
elif (travel_type_query.value,
travel_type_query.name) in c.various_travel_types:
self.q = self.q.join(VariousTravel)
arrival_date = request.args.get("arrival_date", None)
if arrival_date:
self.q = self.q.filter(VariousTravel.date == arrival_date)
self.search_form.arrival_date.default = parse_date(
arrival_date)
border_id = request.args.get("auto_border_id", "-1")
if border_id != "-1":
self.search_form.auto_border_id.default = border_id
border_list = [
("auto_border_id", self.search_form.auto_border_id),
("foot_border_id", self.search_form.foot_border_id),
("sea_border_id", self.search_form.sea_border_id)
]
for border_type in border_list:
if border_type[0] in request.args:
if request.args[border_type[0]] != "-1":
border_id = request.args[border_type[0]]
border_type[1].default = border_id
self.q = self.q.filter(
VariousTravel.border_control_id == border_id)
self.search_params.append(
(_("Граница"),
BorderControl.query.filter_by(
id=border_id).first().name))
break
self.search_form.process()
def test_create_participant(admin_client):
with patch('juleol.db.Tastings') as TastingsMock:
TastingsMock.query.filter.return_value.first.return_value = None
ret = admin_client.post('/admin/2000/participant')
assert ret.status_code == 302
assert ret.headers['Location'] == 'http://localhost/admin/'
ret = admin_client.get('/admin/')
assert b'Invalid year' in ret.data
test_tasting = db.Tastings()
test_tasting.year = 2000
test_beer = db.Beers()
test_beer.name = 'test beer'
test_beer.number = 1
test_tasting.beers = [test_beer]
test_tasting.participants = []
TastingsMock.query.filter.return_value.first.return_value = test_tasting
ret = admin_client.post('/admin/2000/participant',
data={'name': 'test'})
assert ret.status_code == 302
assert ret.headers['Location'] == 'http://localhost/admin/2000'
ret = admin_client.get('/admin/2000')
assert b'Invalid form data' in ret.data
ret = admin_client.post('/admin/2000/participant',
data={
'name': 'test',
'email': 'email-invalid'
})
assert ret.status_code == 302
assert ret.headers['Location'] == 'http://localhost/admin/2000'
ret = admin_client.get('/admin/2000')
assert b'Invalid form data' in ret.data
with patch('juleol.db.Beers') as BeersMock: # noqa: F841
with patch('juleol.db.Participants') as ParticpantsMock:
with patch('juleol.db.ScoreTaste') as MockScoreTaste:
with patch('juleol.db.ScoreAftertaste'
) as MockScoreAftertaste:
with patch('juleol.db.ScoreLook') as MockScoreLook:
with patch(
'juleol.db.ScoreSmell') as MockScoreSmell:
with patch('juleol.db.ScoreXmas'
) as MockScoreXmas:
with patch('juleol.db.db.session'
) as SessionMock:
ret = admin_client.post(
'/admin/2000/participant',
data={
'name': 'test',
'email': '*****@*****.**'
})
assert ret.status_code == 302
assert ret.headers[
'Location'] == 'http://localhost/admin/2000'
assert ParticpantsMock.mock_calls[0][
2]['tasting'] == test_tasting
assert ParticpantsMock.mock_calls[0][
2]['name'] == 'test'
assert ParticpantsMock.mock_calls[0][
2]['email'] == '*****@*****.**'
assert MockScoreLook.mock_calls[0][2][
'tasting'] == test_tasting
assert MockScoreLook.mock_calls[0][2][
'beer'] == test_beer
assert MockScoreSmell.mock_calls[0][2][
'tasting'] == test_tasting
assert MockScoreSmell.mock_calls[0][2][
'beer'] == test_beer
assert MockScoreTaste.mock_calls[0][2][
'tasting'] == test_tasting
assert MockScoreTaste.mock_calls[0][2][
'beer'] == test_beer
assert MockScoreAftertaste.mock_calls[
0][2]['tasting'] == test_tasting
assert MockScoreAftertaste.mock_calls[
0][2]['beer'] == test_beer
assert MockScoreXmas.mock_calls[0][2][
'beer'] == test_beer
assert MockScoreXmas.mock_calls[0][2][
'tasting'] == test_tasting
assert SessionMock.mock_calls[0][
0] == 'add'
assert SessionMock.mock_calls[0][
1] == (db.Participants(
name='test',
email='*****@*****.**',
tasting=test_tasting), )
assert SessionMock.mock_calls[1][
0] == 'add'
assert SessionMock.mock_calls[1][
1] == (db.ScoreLook(
tasting=test_tasting,
beer=test_beer,
participant=db.Participants(
name='test',
email='*****@*****.**',
tasting=test_tasting)), )
assert SessionMock.mock_calls[2][
0] == 'add'
assert SessionMock.mock_calls[2][
1] == (db.ScoreSmell(
tasting=test_tasting,
beer=test_beer,
participant=db.Participants(
name='test',
email='*****@*****.**',
tasting=test_tasting)), )
assert SessionMock.mock_calls[3][
0] == 'add'
assert SessionMock.mock_calls[3][
1] == (db.ScoreTaste(
tasting=test_tasting,
beer=test_beer,
participant=db.Participants(
name='test',
email='*****@*****.**',
tasting=test_tasting)), )
assert SessionMock.mock_calls[4][
0] == 'add'
assert SessionMock.mock_calls[4][
1] == (db.ScoreAftertaste(
tasting=test_tasting,
beer=test_beer,
participant=db.Participants(
name='test',
email='*****@*****.**',
tasting=test_tasting)), )
assert SessionMock.mock_calls[5][
0] == 'add'
assert SessionMock.mock_calls[5][
1] == (db.ScoreXmas(
tasting=test_tasting,
beer=test_beer,
participant=db.Participants(
name='test',
email='*****@*****.**',
tasting=test_tasting)), )
assert SessionMock.mock_calls[6][
0] == 'commit'
SessionMock.reset_mock()
SessionMock.commit.side_effect = exc.SQLAlchemyError(
)
ret = admin_client.post(
'/admin/2000/participant',
data={
'name': 'test',
'email': '*****@*****.**'
})
assert ret.status_code == 302
assert ret.headers[
'Location'] == 'http://localhost/admin/2000'
ret = admin_client.get('/admin/2000')
assert b'Error creating participant' in ret.data
assert SessionMock.mock_calls[7][
0] == 'rollback'
def test_update_beer(admin_client):
with patch('juleol.db.Beers') as BeersMock: # noqa: F841
db.Beers.query.filter.return_value.first.return_value = None
ret = admin_client.put('/admin/beer/1')
assert ret.status_code == 404
assert json.loads(ret.data)['error'] == 'Invalid beer id'
test_beer = db.Beers()
test_beer.id = 1
test_beer.number = 1
test_beer.name = 'test beer 1'
db.Beers.query.filter.return_value.first.return_value = test_beer
ret = admin_client.put('/admin/beer/1')
assert ret.status_code == 400
assert json.loads(ret.data)['error'] == 'Invalid arguments'
with patch('juleol.db.db.session') as SessionMock:
ret = admin_client.put('/admin/beer/1', data={'name': 'new name'})
assert ret.status_code == 200
assert json.loads(ret.data)['message'] == 'Beer name updated'
assert test_beer.name == 'new name'
assert SessionMock.mock_calls[0][0] == 'add'
assert SessionMock.mock_calls[0][1] == (test_beer, )
assert SessionMock.mock_calls[1][0] == 'commit'
SessionMock.reset_mock()
SessionMock.commit.side_effect = exc.SQLAlchemyError()
ret = admin_client.put('/admin/beer/1', data={'name': 'new name'})
assert ret.status_code == 500
assert json.loads(ret.data)['error'] == 'Error updating beer name'
assert SessionMock.mock_calls[0][0] == 'add'
assert SessionMock.mock_calls[0][1] == (test_beer, )
assert SessionMock.mock_calls[1][0] == 'commit'
assert SessionMock.mock_calls[2][0] == 'rollback'
with patch('juleol.db.Heats') as HeatsMock: # noqa: F841
test_heat = db.Heats()
test_heat.id = 1
test_heat.name = 'test'
db.Heats.query.filter.return_value.filter.return_value.first.return_value = None
ret = admin_client.put('/admin/beer/1', data={'heat': 1})
assert ret.status_code == 404
assert json.loads(ret.data)['error'] == 'Invalid heat'
db.Heats.query.filter.return_value.filter.return_value.first.return_value = test_heat
SessionMock.reset_mock()
SessionMock.commit.side_effect = None
ret = admin_client.put('/admin/beer/1', data={'heat': 1})
assert ret.status_code == 200
assert json.loads(ret.data)['message'] == 'Beer heat updated'
assert test_beer.name == 'new name'
assert SessionMock.mock_calls[0][0] == 'add'
assert SessionMock.mock_calls[0][1] == (test_beer, )
assert SessionMock.mock_calls[1][0] == 'commit'
assert test_beer.heat == test_heat
SessionMock.commit.side_effect = exc.SQLAlchemyError()
SessionMock.reset_mock()
ret = admin_client.put('/admin/beer/1', data={'heat': 1})
assert ret.status_code == 500
assert json.loads(
ret.data)['error'] == 'Error updating beer heat'
assert SessionMock.mock_calls[2][0] == 'rollback'
