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()