async def execute(self, id, text):
# Check if identifier exists
check = await self.conn.fetchval(
"""
SELECT
schema_cli
FROM
bdms.codelist
WHERE
id_cli = $1
""", id)
if check != 'borehole_identifier':
raise NotFound()
await self.conn.fetchval(
"""
UPDATE bdms.codelist
SET text_cli_en = $1
WHERE
id_cli = $2
""", text, id)
return None
async def execute(self,
feb_id,
username,
password,
recipients,
server,
port,
tls,
starttls,
sender=None):
try:
# Getting the feedback from the db
get = GetFeedback(self.conn)
feedback = await get.execute(feb_id)
if feedback is None:
raise NotFound()
feedback = feedback['data']
# Preparing the email message
message = f"""TAG: {feedback['tag']}
DATE: {feedback['created']}
SENDER: {feedback['user']}
MESSAGE:
{feedback['message']}
"""
# Send the email
send = SendMail()
await send.execute(username,
password,
recipients,
f"[SWISSFORAGE][{feedback['tag']}] Feedback",
message,
server,
port=port,
tls=tls,
starttls=starttls)
await self.conn.execute(
"""
UPDATE
bdms.feedbacks
SET
frw_feb = TRUE
WHERE
id_feb = $1
""", feb_id)
return None
except Exception:
raise Exception("Error while forwarding feedback")
async def execute(self, id, cid, value):
# Check if identifier exists
check = await self.conn.fetchval(
"""
SELECT
schema_cli
FROM
bdms.codelist
WHERE
id_cli = $1
""",
cid
)
if check != 'borehole_identifier':
raise NotFound()
await self.conn.fetchval(
"""
INSERT INTO bdms.borehole_codelist(
id_bho_fk,
id_cli_fk,
code_cli,
value_bco
)
VALUES (
$1,
$2,
'borehole_identifier',
$3
)
""",
id,
cid,
value
)
return {
"data": {
"id": cid,
"value": value
}
}
async def execute(self, id, text):
# Check if identifier exists
check = await self.conn.fetchval(
"""
SELECT
schema_cli
FROM
bdms.codelist
WHERE
id_cli = $1
""", id)
if check != 'borehole_identifier':
raise NotFound()
# validate text dict
languages = list(text.keys())
allowed_languages = ['en', 'de', 'fr', 'it']
for lang in allowed_languages:
if lang not in languages:
raise MissingParameter(lang)
languages.remove(lang)
if len(languages) > 0:
raise WrongParameter(", ".join(languages))
await self.conn.fetchval(
"""
UPDATE bdms.codelist
SET
text_cli_en = $1,
text_cli_de = $2,
text_cli_it = $3,
text_cli_fr = $4
WHERE
id_cli = $5
""", text['en'], text['de'], text['it'], text['fr'], id)
return None
async def execute(self, id, cid):
# Check if identifier exists
check = await self.conn.fetchval(
"""
SELECT EXISTS(
SELECT 1
FROM
bdms.borehole_codelist
WHERE
id_bho_fk = $1
AND
id_cli_fk = $2
AND
code_cli = 'borehole_identifier'
) AS exists
""",
id,
cid
)
if not check:
raise NotFound()
await self.conn.fetchval(
"""
DELETE FROM
bdms.borehole_codelist
WHERE
id_bho_fk = $1
AND
id_cli_fk = $2
AND
code_cli = 'borehole_identifier'
""",
id,
cid
)
return None
async def execute(self, id):
# Check if identifier already exists
check = await self.conn.fetchval(
"""
SELECT
schema_cli
FROM
bdms.codelist
WHERE
id_cli = $1
""", id)
if check != 'borehole_identifier':
raise NotFound()
await self.conn.execute(
"""
DELETE FROM bdms.codelist
WHERE id_cli = $1
""", id)
return None
async def execute(self, id):
# Check if identifier already exists
check = await self.conn.fetchval("""
SELECT
schema_cli
FROM
bdms.codelist
WHERE
id_cli = $1
""", id)
if check != 'borehole_identifier':
raise NotFound()
# Check if identifier assigned to boreholes
check = await self.conn.fetchval("""
SELECT EXISTS (
SELECT
id_bho_fk
FROM
bdms.borehole_codelist
WHERE
id_cli_fk = $1
LIMIT 1
) as has_bh
""", id)
if check is True:
raise DeleteReferenced()
await self.conn.execute("""
DELETE FROM bdms.codelist
WHERE id_cli = $1
""", id)
return None
async def post(self, *args, **kwargs):
try:
if self.user is None:
raise AuthenticationException()
self.authorize()
body = self.request.body.decode('utf-8')
if body is None or body == '':
raise ActionEmpty()
payload = json.loads(body)
action = payload['action']
response = await self.execute(payload)
if action in ['EXPORT', 'EXPORT_BY_ID']:
now = datetime.datetime.now()
self.set_header("Content-Type", "application/zip")
self.set_header(
"Content-Disposition",
"inline; filename=bdms-export-%s.zip" %
now.strftime("%Y%m%d%H%M%S"))
output = BytesIO()
output_stream = zipfile.ZipFile(
output, mode="w", compression=zipfile.ZIP_DEFLATED)
if response:
output_stream.writestr('swissforages.gpkg',
response.getvalue())
# Check file repository
if options.file_repo == 's3':
session = boto3.Session(
aws_access_key_id=options.aws_access_key_id,
aws_secret_access_key=options.
aws_secret_access_key,
)
s3 = session.resource('s3')
async with self.pool.acquire() as conn:
widx = 1
where = []
arguments = []
if 'workgroup' in payload and payload[
'workgroup'] != 'all':
widx = 1
andId = []
for id in payload['workgroup']:
andId.append(f"""
id_wgp_fk = ${widx}
""")
arguments.append(id)
widx += 1
where.append(f"""(
{' OR '.join(andId)}
)""")
# where.append(f"""
# id_wgp_fk = ${widx}
# """)
# widx += 1
# arguments.append(payload['workgroup'])
if 'ids' in payload:
widx = 1
andId = []
for id in payload['ids']:
andId.append(f"""
borehole.id_bho = ${widx}
""")
arguments.append(id)
widx += 1
where.append(f"""(
{' OR '.join(andId)}
)""")
stringWhere = ''
if len(where) > 0:
stringWhere = f"""
WHERE
{' AND '.join(where)}
"""
rows = await conn.fetch(
f"""
SELECT
name_fil,
type_fil,
conf_fil
FROM
bdms.files
INNER JOIN
bdms.borehole_files
ON
id_fil_fk = id_fil
INNER JOIN
bdms.borehole
ON
id_bho = id_bho_fk
{stringWhere}
""", *arguments)
not_found = []
for file_info in rows:
if file_info is None:
raise NotFound()
conf = json.loads(file_info[2])
attachment = BytesIO()
try:
s3.Bucket(options.aws_bucket).download_fileobj(
conf['key'], attachment)
output_stream.writestr(f'files/{conf["key"]}',
attachment.getvalue())
except ClientError as ce:
# print(traceback.print_exc())
if ce.response['Error']['Code'] == '404':
not_found.append(conf['key'])
continue
raise Exception(
"Error while downloading from AWS")
if len(not_found) > 0:
print(
f"\033[91m{len(not_found)} files not found 🤔\033[0m"
)
else:
raise Exception("Unknown error")
output_stream.close()
self.write(output.getvalue())
else:
self.set_header("Content-Type",
"application/json; charset=utf-8")
if response is None:
response = {}
self.write({**{"success": True}, **response})
except BmsException as bex:
print(traceback.print_exc())
self.write({
"success": False,
"message": str(bex),
"error": bex.code,
"data": bex.data
})
except Exception as ex:
print(traceback.print_exc())
self.write({"success": False, "message": str(ex)})
self.finish()
async def get(self, *args, **kwargs):
try:
if self.user is None:
raise AuthenticationException()
self.authorize()
self.set_header(
"Expires",
datetime.datetime.utcnow() + datetime.timedelta(seconds=1))
self.set_header("Cache-Control", "max-age=" + str(1))
s3 = None
# Check file repository
if options.file_repo == 's3':
session = boto3.Session(
aws_access_key_id=options.aws_access_key_id,
aws_secret_access_key=options.aws_secret_access_key,
)
s3 = session.resource('s3')
async with self.pool.acquire() as conn:
file_id = int(self.get_argument('id', 0))
if file_id == 0:
raise NotFound()
# Getting attachment info
file_info = await conn.fetchrow(
"""
SELECT
name_fil,
type_fil,
conf_fil
FROM
bdms.files
WHERE
id_fil = $1
""", file_id)
if file_info is None:
raise NotFound()
conf = json.loads(file_info[2])
self.set_header("Content-Type", file_info[1])
self.set_header("Content-Disposition",
f"inline; {file_info[0]}")
attachment = BytesIO()
if options.file_repo == 's3':
try:
s3.Bucket(options.aws_bucket).download_fileobj(
conf['key'], attachment)
except ClientError:
print(traceback.print_exc())
raise Exception("Error while downloading from AWS")
self.write(attachment.getvalue())
except BmsException as bex:
print(traceback.print_exc())
self.write({
"success": False,
"message": str(bex),
"error": bex.code
})
except Exception as ex:
print(traceback.print_exc())
self.write({"success": False, "message": str(ex)})
self.finish()