def list_events(iid=None, rid=None, type=None, date=None):
db = get_db_instance()
data = []
try:
with db.cursor() as cursor:
# Read all records
sql = "select * from {} where GCLOUD_ID like '%'"
if iid:
sql += ' and ID_INSTITUTION = {}'.format(iid)
if rid:
sql += ' and ID_REGION = {}'.format(rid)
if type:
sql += ' and TYPE = "{}"'.format(type)
if date:
sql += ' and DATE >= "{}"'.format(date)
cursor.execute(sql.format(table_name))
result = cursor.fetchall()
if result:
for row in result:
event = parse_event_obj(row)
data.append(event)
return data
else:
return None
finally:
db.close()
def get_institution_by_email(email):
db = get_db_instance()
institution = None
try:
with db.cursor() as cursor:
# Read a single record
sql = "select * from {} where EMAIL = '{}'"
cursor.execute(sql.format(table_name, email))
result = cursor.fetchone()
if result:
institution = Institution(id=result[0],
address=result[1],
name=result[2],
email=result[3],
passwd=result[4],
types=result[5],
shelter=result[6],
status=result[7],
created_at=result[8],
cpf_cnpj=[9],
admin=result[10])
return institution
else:
return None
finally:
db.close()
def create_institution(address, name, email, passwd, types, shelter, status,
cpf_cnpj):
db = get_db_instance()
institution = None
try:
with db.cursor() as cursor:
# insert record
sql = "INSERT INTO {} (ADDRESS, NAME, EMAIL, PASSWD, TYPES, SHELTER, STATUS, CPF_CNPJ) VALUES " \
"('{}', '{}', '{}','{}','{}',{},{},'{}')"
cursor.execute(
sql.format(table_name, address, name, email, passwd, types,
shelter, status, cpf_cnpj))
created_id = db.insert_id()
cursor.execute('commit')
institution = Institution(
id=created_id,
address=address,
name=name,
email=email,
passwd=passwd,
types=types,
shelter=shelter,
status=status,
cpf_cnpj=cpf_cnpj,
created_at=datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
admin=False)
return institution
finally:
db.close()
def delete_info(info_id):
db = get_db_instance()
try:
with db.cursor() as cursor:
# Remove a single record
sql = "delete from {} where ID_INFO = {}"
cursor.execute(sql.format(table_name, info_id))
cursor.execute('commit')
return True
finally:
db.close()
def get_event(event_id):
db = get_db_instance()
event = None
try:
with db.cursor() as cursor:
# Read a single record
sql = "select * from {} where ID_EVENT = {}"
cursor.execute(sql.format(table_name, event_id, type))
result = cursor.fetchone()
if result:
event = parse_event_obj(result)
return event
finally:
db.close()
def check_free(date, calendar, type):
db = get_db_instance()
event = None
try:
with db.cursor() as cursor:
# Read a single record
sql = "select * from {} where ID_REGION = {} and DATE = '{}' and TYPE = '{}'"
cursor.execute(sql.format(table_name, calendar.id, date, type))
result = cursor.fetchone()
if not result:
return True
else:
return False
finally:
db.close()
def get_calendar_by_region_and_type(region_id, type):
db = get_db_instance()
calendar = None
try:
with db.cursor() as cursor:
# Read a single record
sql = "select * from {} where ID_REGION = {} and TYPE like '{}'"
cursor.execute(sql.format(table_name, region_id, type))
result = cursor.fetchone()
calendar = Calendar(id=result[0],
region_id=result[3],
gcloud_id=result[2],
type=result[1])
return calendar
finally:
db.close()
def get_calendar(calendar_id):
db = get_db_instance()
data = []
try:
with db.cursor() as cursor:
# Read a single record
sql = "select * from {} where ID_CALENDAR = {}"
cursor.execute(sql.format(table_name, calendar_id))
result = cursor.fetchone()
if result:
return Calendar(id=result[0],
type=result[1],
gcloud_id=result[2],
region_id=result[3])
finally:
db.close()
def create_calendar(region_id, gcloud_id, type):
db = get_db_instance()
calendar = None
try:
with db.cursor() as cursor:
# insert record
sql = "INSERT INTO {} (ID_REGION, GCLOUD_ID, TYPE) VALUES ({}, '{}', '{}')"
cursor.execute(sql.format(table_name, region_id, gcloud_id, type))
created_id = db.insert_id()
cursor.execute('commit')
calendar = Calendar(id=created_id,
region_id=region_id,
gcloud_id=gcloud_id,
type=type)
return calendar
finally:
db.close()
def get_calendars_by_region(region_id):
db = get_db_instance()
data = []
try:
with db.cursor() as cursor:
# Read a single record
sql = "select * from {} where ID_REGION = {}"
cursor.execute(sql.format(table_name, region_id))
result = cursor.fetchall()
for row in result:
data.append({
'calendar_id': row[0],
'type': row[1],
'gcloud_id': row[2]
})
return data
finally:
db.close()
def create_region(region_name, address, population):
db = get_db_instance()
region = None
try:
with db.cursor() as cursor:
# insert record
sql = "INSERT INTO {} (NAME, ADDRESS, POPULATION) VALUES ('{}', '{}', {})"
cursor.execute(
sql.format(table_name, region_name, address, population))
created_id = db.insert_id()
cursor.execute('commit')
region = Region(id=created_id,
address=address,
name=region_name,
population=population)
return region
finally:
db.close()
def get_region(id):
db = get_db_instance()
region = None
try:
with db.cursor() as cursor:
# Read a single record
sql = "select * from {} where ID_REGION = {}"
cursor.execute(sql.format(table_name, id))
result = cursor.fetchone()
if result:
region = Region(id=result[0],
address=result[1],
name=result[2],
population=result[3])
return region
else:
return None
finally:
db.close()
def get_info(info_id):
db = get_db_instance()
info = None
try:
with db.cursor() as cursor:
# Read a single record
sql = "select * from {} where ID_INFO = {}"
cursor.execute(sql.format(table_name, info_id))
result = cursor.fetchone()
if result:
info = Info(id=result[0],
name=result[1],
phone=result[2],
link=result[3],
image=result[4],
description=result[5])
return info
else:
return None
finally:
db.close()
def create_information(name, phone, link, description, image):
db = get_db_instance()
info = None
try:
with db.cursor() as cursor:
# insert record
sql = "INSERT INTO {} (name, phone, link, description, image) VALUES " \
"('{}', '{}', '{}','{}', '{}')"
cursor.execute(
sql.format(table_name, name, phone, link, description, image))
created_id = db.insert_id()
cursor.execute('commit')
info = Info(id=created_id,
name=name,
phone=phone,
link=link,
description=description,
image=image)
return info
finally:
db.close()
def get_institutions():
db = get_db_instance()
data = []
try:
with db.cursor() as cursor:
# Read all records
sql = "select * from {} order by STATUS,CREATED_AT"
cursor.execute(sql.format(table_name))
result = cursor.fetchall()
if result:
for row in result:
data.append({
'id':
row[0],
'address':
row[1],
'name':
row[2],
'email':
row[3],
'password':
row[4],
'types':
row[5].split(','),
'shelter':
row[6],
'status':
status_enum[str(row[7])],
'created_at':
row[8].strftime('%Y-%m-%d %H:%M:%S'),
'cpf_cnpj':
row[9],
'admin':
row[10]
})
return data
else:
return None
finally:
db.close()
def update_institution(institution, name, address, email, passwd, types,
shelter, status, cpf_cnpj):
from utils.password_utils import convert_md5
db = get_db_instance()
try:
with db.cursor() as cursor:
# update a single record
sql = "update {} set ".format(table_name)
if name:
sql += "NAME = '{}', ".format(name)
institution.name = name
if address:
sql += "ADDRESS = '{}', ".format(address)
institution.address = address
if email:
sql += "EMAIL = '{}', ".format(email)
institution.email = email
if passwd:
sql += "PASSWD = '{}', ".format(convert_md5(passwd))
institution.passwd = convert_md5(passwd)
if types:
sql += "TYPES = '{}', ".format(types)
institution.types = types
if shelter:
sql += "SHELTER = {}, ".format(shelter)
institution.shelter = shelter
if status:
sql += "STATUS = {}, ".format(status_enum_reverse[status])
institution.status = status_enum_reverse[status]
if cpf_cnpj:
sql += "CPF_CNPJ = '{}', ".format(cpf_cnpj)
institution.cpf_cnpj = cpf_cnpj
sql = sql[:-2] + " "
sql += "where ID_INSTITUTION = {}".format(institution.id)
cursor.execute(sql)
cursor.execute('commit')
return institution
finally:
db.close()
def get_regions():
db = get_db_instance()
data = []
try:
with db.cursor() as cursor:
# Read all records
sql = "select * from {}"
cursor.execute(sql.format(table_name))
result = cursor.fetchall()
if result:
for row in result:
data.append({
'id_region': row[0],
'name': row[2],
'address': row[1],
'population': row[3]
})
return data
else:
return None
finally:
db.close()
def get_readiness_request():
"""Test all used services and return readiness."""
result = {
"API": "OK"
}
try:
db = get_db_instance()
if db:
result['database'] = 'OK'
db.close()
except:
result['database'] = 'ERROR'
#TODO: google api ready
code = 200
for key in result.keys():
if result[key] is not 'OK':
code = 503
return jsonify(result), code
def get_infos():
db = get_db_instance()
data = []
try:
with db.cursor() as cursor:
# Read all records
sql = "select * from {}"
cursor.execute(sql.format(table_name))
result = cursor.fetchall()
if result:
for row in result:
data.append({
'id': row[0],
'name': row[1],
'phone': row[2],
'link': row[3],
'image': row[4],
'description': row[5],
})
return data
else:
return {}
finally:
db.close()
def create_event(date, period, type, gcloud_id, calendar, institution, region):
db = get_db_instance()
event = None
try:
with db.cursor() as cursor:
# insert record
sql = "INSERT INTO {} (DATE, PERIOD, GCLOUD_ID, TYPE, ID_CALENDAR, ID_INSTITUTION, ID_REGION)" \
" VALUES ('{}', '{}', '{}', '{}', {}, {}, {})"
cursor.execute(
sql.format(table_name, date, period, gcloud_id, type,
calendar.id, institution.id, region.id))
created_id = db.insert_id()
cursor.execute('commit')
event = Event(id=created_id,
date=date,
period=period,
type=type,
gcloud_id=gcloud_id,
calendar=calendar,
institution=institution,
region=region)
return event
finally:
db.close()