def decrypt_tip(user_key, tip_prv_key, tip): tip_key = GCE.asymmetric_decrypt(user_key, tip_prv_key) for questionnaire in tip['questionnaires']: questionnaire['answers'] = json.loads(GCE.asymmetric_decrypt(tip_key, base64.b64decode(questionnaire['answers'].encode())).decode()) for k in ['whistleblower_identity']: if k in tip['data'] and tip['data'][k]: tip['data'][k] = json.loads(GCE.asymmetric_decrypt(tip_key, base64.b64decode(tip['data'][k].encode())).decode()) # Fix for issue: https://github.com/globaleaks/GlobaLeaks/issues/2612 # The bug is due to the fact that the data was initially saved as an array of one entry if k == 'whistleblower_identity' and isinstance(tip['data'][k], list): tip['data'][k] = tip['data'][k][0] for x in tip['comments'] + tip['messages']: x['content'] = GCE.asymmetric_decrypt(tip_key, base64.b64decode(x['content'].encode())).decode() for x in tip['wbfiles'] + tip['rfiles']: for k in ['name', 'description', 'type', 'size']: if k in x: x[k] = GCE.asymmetric_decrypt(tip_key, base64.b64decode(x[k].encode())).decode() if k == 'size': x[k] = int(x[k]) return tip
def db_admin_update_user(session, tid, user_session, user_id, request, language): """ Transaction for updating an existing user :param session: An ORM session :param tid: A tenant ID :param user_session: The current user session :param user_id: The ID of the user to update :param request: The request data :param language: The language of the request :return: The serialized descriptor of the updated object """ fill_localized_keys(request, models.User.localized_keys, language) user = db_get_user(session, tid, user_id) if user.username != request['username']: check = session.query(models.User).filter( models.User.username == request['username'], models.User.tid == tid).one_or_none() if check is not None: raise errors.InputValidationError('Username already in use') user.update(request) password = request['password'] if password and (not user.crypto_pub_key or user_session.ek): if user.crypto_pub_key and user_session.ek: enc_key = GCE.derive_key(password.encode(), user.salt) crypto_escrow_prv_key = GCE.asymmetric_decrypt( user_session.cc, Base64Encoder.decode(user_session.ek)) if tid == 1: user_cc = GCE.asymmetric_decrypt( crypto_escrow_prv_key, Base64Encoder.decode(user.crypto_escrow_bkp1_key)) else: user_cc = GCE.asymmetric_decrypt( crypto_escrow_prv_key, Base64Encoder.decode(user.crypto_escrow_bkp2_key)) user.crypto_prv_key = Base64Encoder.encode( GCE.symmetric_encrypt(enc_key, user_cc)) if user.hash_alg != 'ARGON2': user.hash_alg = 'ARGON2' user.salt = GCE.generate_salt() user.password = GCE.hash_password(password, user.salt) user.password_change_date = datetime_now() user.password_change_needed = True # The various options related in manage PGP keys are used here. parse_pgp_options(user, request) return user_serialize_user(session, user, language)
def generate_password_reset_token(session, tid, user_session, user_id): user = session.query(User).filter(User.tid == tid, User.id == user_id).one_or_none() if user is None: return db_generate_password_reset_token(session, user) if user_session.ek and user.crypto_pub_key: crypto_escrow_prv_key = GCE.asymmetric_decrypt(user_session.cc, Base64Encoder.decode(user_session.ek)) user_cc = GCE.asymmetric_decrypt(crypto_escrow_prv_key, Base64Encoder.decode(user.crypto_escrow_bkp1_key)) enc_key = GCE.derive_key(user.reset_password_token.encode(), user.salt) key = Base64Encoder.encode(GCE.symmetric_encrypt(enc_key, user_cc)) State.TempKeys[user_id] = TempKey(key)
def decrypt_tip(user_key, tip_prv_key, tip): tip_key = GCE.asymmetric_decrypt(user_key, tip_prv_key) for questionnaire in tip['questionnaires']: questionnaire['answers'] = json.loads(GCE.asymmetric_decrypt(tip_key, base64.b64decode(questionnaire['answers'].encode())).decode()) for k in ['whistleblower_identity']: if k in tip['data'] and tip['data'][k]['encrypted'] and tip['data'][k]['value']: tip['data'][k]['value'] = json.loads(GCE.asymmetric_decrypt(tip_key, base64.b64decode(tip['data'][k]['value'].encode())).decode()) for x in tip['comments'] + tip['messages']: x['content'] = GCE.asymmetric_decrypt(tip_key, base64.b64decode(x['content'].encode())).decode() return tip
def decrypt_tip(user_key, tip_prv_key, tip): tip_key = GCE.asymmetric_decrypt(user_key, tip_prv_key) for questionnaire in tip['questionnaires']: questionnaire['answers'] = json.loads(GCE.asymmetric_decrypt(tip_key, base64.b64decode(questionnaire['answers'].encode())).decode()) for k in ['whistleblower_identity']: if k in tip['data'] and tip['data'][k]['encrypted'] and tip['data'][k]['value']: tip['data'][k]['value'] = json.loads(GCE.asymmetric_decrypt(tip_key, base64.b64decode(tip['data'][k]['value'].encode())).decode()) for x in tip['comments'] + tip['messages']: x['content'] = GCE.asymmetric_decrypt(tip_key, base64.b64decode(x['content'].encode())).decode() return tip
def test_recovery_key(self): prv_key, _ = GCE.generate_keypair() bck_key, rec_key = GCE.generate_recovery_key(prv_key) plain_rec_key = GCE.asymmetric_decrypt(prv_key, Base64Encoder.decode(rec_key)) x = GCE.symmetric_decrypt(plain_rec_key, Base64Encoder.decode(bck_key)) self.assertEqual(x, prv_key)
def login(session, tid, username, password, authcode, client_using_tor, client_ip): """ login returns a session """ user = None users = session.query(User).filter(User.username == username, User.state != 'disabled', User.tid == tid).distinct() for u in users: if GCE.check_password(u.hash_alg, password, u.salt, u.password): user = u break # Fix for issue: https://github.com/globaleaks/GlobaLeaks/issues/2563 if State.tenant_cache[1].creation_date < 1551740400: u_password = '******'' + u.password + '\'' if GCE.check_password(u.hash_alg, password, u.salt, u_password): user = u break if user is None: log.debug("Login: Invalid credentials") Settings.failed_login_attempts += 1 raise errors.InvalidAuthentication connection_check(client_ip, tid, user.role, client_using_tor) crypto_prv_key = '' if State.tenant_cache[tid].encryption: if user.crypto_prv_key: user_key = GCE.derive_key(password.encode('utf-8'), user.salt) crypto_prv_key = GCE.symmetric_decrypt(user_key, user.crypto_prv_key) else: # Force the password change on which the user key will be created user.password_change_needed = True if user.two_factor_enable: if authcode != '': if user.crypto_pub_key: two_factor_secret = GCE.asymmetric_decrypt( crypto_prv_key, user.two_factor_secret).decode('utf-8') else: two_factor_secret = user.two_factor_secret.decode('utf-8') # RFC 6238: step size 30 sec; valid_window = 1; total size of the window: 1.30 sec if not pyotp.TOTP(two_factor_secret).verify(authcode, valid_window=1): raise errors.InvalidTwoFactorAuthCode else: raise errors.TwoFactorAuthCodeRequired user.last_login = datetime_now() return Sessions.new(tid, user.id, user.tid, user.role, user.password_change_needed, user.two_factor_enable, crypto_prv_key)
def get_recovery_key(session, user_tid, user_id, user_cc): user = db_get_user(session, user_tid, user_id) if not user.crypto_rec_key: return '' return Base32Encoder().encode(GCE.asymmetric_decrypt(user_cc, user.crypto_rec_key)).replace(b'=', b'')
def db_admin_update_user(session, tid, user_session, user_id, request, language): """ Transaction for updating an existing user :param session: An ORM session :param tid: A tenant ID :param user_session: The current user session :param user_id: The ID of the user to update :param request: The request data :param language: The language of the request :return: The serialized descriptor of the updated object """ fill_localized_keys(request, models.User.localized_keys, language) user = db_get_user(session, tid, user_id) user.update(request) password = request['password'] if password and (not user.crypto_pub_key or user_session.ek): if user.crypto_pub_key and user_session.ek: enc_key = GCE.derive_key(password.encode(), user.salt) crypto_escrow_prv_key = GCE.asymmetric_decrypt(user_session.cc, Base64Encoder.decode(user_session.ek)) if user_session.user_tid == 1: user_cc = GCE.asymmetric_decrypt(crypto_escrow_prv_key, Base64Encoder.decode(user.crypto_escrow_bkp1_key)) else: user_cc = GCE.asymmetric_decrypt(crypto_escrow_prv_key, Base64Encoder.decode(user.crypto_escrow_bkp2_key)) user.crypto_prv_key = Base64Encoder.encode(GCE.symmetric_encrypt(enc_key, user_cc)) if user.hash_alg != 'ARGON2': user.hash_alg = 'ARGON2' user.salt = GCE.generate_salt() user.password = GCE.hash_password(password, user.salt) user.password_change_date = datetime_now() State.log(tid=tid, type='change_password', user_id=user_session.user_id, object_id=user_id) # The various options related in manage PGP keys are used here. parse_pgp_options(user, request) return user_serialize_user(session, user, language)
def get(self, rtip_id): tip_export = yield get_tip_export(self.request.tid, self.current_user.user_id, rtip_id, self.request.language) if tip_export['crypto_tip_prv_key']: tip_export['tip'] = yield deferToThread( decrypt_tip, self.current_user.cc, tip_export['crypto_tip_prv_key'], tip_export['tip']) for file_dict in tip_export['tip']['rfiles'] + tip_export['tip'][ 'wbfiles']: if file_dict.get('status', '') == 'encrypted' or file_dict.get('forged'): continue tip_prv_key = GCE.asymmetric_decrypt( self.current_user.cc, tip_export['crypto_tip_prv_key']) file_dict['fo'] = GCE.streaming_encryption_open( 'DECRYPT', tip_prv_key, file_dict['path']) del file_dict['path'] for file_dict in tip_export['tip']['rfiles']: file_dict['name'] = 'files/' + file_dict['name'] if file_dict.get('status', '') == 'encrypted': file_dict['name'] += '.pgp' for file_dict in tip_export['tip']['wbfiles']: file_dict[ 'name'] = 'files_attached_from_recipients/' + file_dict['name'] tip_export['comments'] = tip_export['tip']['comments'] tip_export['messages'] = tip_export['tip']['messages'] files = tip_export['tip']['rfiles'] + tip_export['tip']['wbfiles'] del tip_export['tip']['rfiles'], tip_export['tip']['wbfiles'] export_template = Templating().format_template( tip_export['notification']['export_template'], tip_export).encode() export_template = msdos_encode(export_template.decode()).encode() files.append({ 'fo': BytesIO(export_template), 'name': 'data.txt', 'forged': True }) self.request.setHeader(b'X-Download-Options', b'noopen') self.request.setHeader(b'Content-Type', b'application/octet-stream') self.request.setHeader(b'Content-Disposition', b'attachment; filename="submission.zip"') self.zip_stream = iter(ZipStream(files)) yield ZipStreamProducer(self, self.zip_stream).start()
def get(self, rfile_id): rfile, tip_prv_key = yield self.download_rfile(self.request.tid, self.current_user.user_id, rfile_id) filelocation = os.path.join(Settings.attachments_path, rfile['filename']) directory_traversal_check(Settings.attachments_path, filelocation) if tip_prv_key: tip_prv_key = GCE.asymmetric_decrypt(self.current_user.cc, tip_prv_key) fo = GCE.streaming_encryption_open('DECRYPT', tip_prv_key, filelocation) yield self.write_file_as_download_fo(rfile['name'], fo) else: yield self.write_file_as_download(rfile['name'], filelocation)
def enable_2fa_step2(session, user_tid, user_id, user_cc, token): user = db_get_user(session, user_tid, user_id) if user.crypto_pub_key: two_factor_secret = GCE.asymmetric_decrypt(user_cc, user.two_factor_secret).decode('utf-8') else: two_factor_secret = user.two_factor_secret # RFC 6238: step size 30 sec; valid_window = 1; total size of the window: 1.30 sec if pyotp.TOTP(two_factor_secret).verify(token): user.two_factor_enable = True else: raise errors.InvalidTwoFactorAuthCode
def get(self, rfile_id): rfile, tip_prv_key = yield self.download_rfile(self.request.tid, self.current_user.user_id, rfile_id) filelocation = os.path.join(Settings.attachments_path, rfile['filename']) directory_traversal_check(Settings.attachments_path, filelocation) if tip_prv_key: tip_prv_key = GCE.asymmetric_decrypt(self.current_user.cc, tip_prv_key) fo = GCE.streaming_encryption_open('DECRYPT', tip_prv_key, filelocation) yield self.write_file_as_download_fo(rfile['name'], fo) else: yield self.write_file_as_download(rfile['name'], filelocation)
def get_recovery_key(session, tid, user_id, user_cc): """ Transaction to get a user recovery key :param session: An ORM session :param tid: The tenant ID :param user_id: The user ID :param user_cc: The user key :return: The recovery key encoded base32 """ user = db_get_user(session, tid, user_id) if not user.crypto_rec_key: return '' return Base32Encoder.encode(GCE.asymmetric_decrypt(user_cc, Base64Encoder.decode(user.crypto_rec_key.encode()))).replace(b'=', b'')
def toggle_escrow(session, tid, user_session, user_id): """ Transaction to toggle key escrow access for user an user given its id :param session: An ORM session :param tid: A tenant ID :param user_session: The current user session :param user_id: The user for which togling the key escrow access """ if user_session.user_id == user_id or not user_session.ek: return user = db_get_user(session, tid, user_id) if not user.crypto_pub_key: return if not user.crypto_escrow_prv_key: crypto_escrow_prv_key = GCE.asymmetric_decrypt(user_session.cc, Base64Encoder.decode(user_session.ek)) user.crypto_escrow_prv_key = Base64Encoder.encode(GCE.asymmetric_encrypt(user.crypto_pub_key, crypto_escrow_prv_key)) else: user.crypto_escrow_prv_key = ''
def get(self, rtip_id): tip_export = yield get_tip_export(self.request.tid, self.current_user.user_id, rtip_id, self.request.language) if tip_export['crypto_tip_prv_key']: for file_dict in tip_export['files']: if file_dict['forged']: continue tip_prv_key = GCE.asymmetric_decrypt(self.current_user.cc, tip_export['crypto_tip_prv_key']) file_dict['fo'] = GCE.streaming_encryption_open('DECRYPT', tip_prv_key, file_dict['path']) del file_dict['path'] self.request.setHeader(b'X-Download-Options', b'noopen') self.request.setHeader(b'Content-Type', b'application/octet-stream') self.request.setHeader(b'Content-Disposition', b'attachment; filename="submission.zip"') self.zip_stream = iter(ZipStream(tip_export['files'])) yield ZipStreamProducer(self, self.zip_stream).start()
def test_crypto_generate_encrypt_decrypt_message(self): prv_key, pub_key = GCE.generate_keypair() enc = GCE.asymmetric_encrypt(pub_key, message) dec = GCE.asymmetric_decrypt(prv_key, enc) self.assertEqual(dec, message)
def test_crypto_generate_encrypt_decrypt_message(self): prv_key, pub_key = GCE.generate_keypair() enc = GCE.asymmetric_encrypt(pub_key, message) dec = GCE.asymmetric_decrypt(prv_key, enc) self.assertEqual(dec, message)
def get_receivertips(session, tid, receiver_id, user_key, language): """ Return list of submissions received by the specified receiver :param session: An ORM session :param tid: The tenant ID :param receiver_id: The receiver ID :param user_key: The user key to be used for decrypting data :param language: The language to be used during data serialization :return: A list of submissions descriptors """ rtip_summary_list = [] rtip_ids = [] itip_ids = [] messages_by_rtip = {} comments_by_itip = {} files_by_itip = {} # Fetch rtip, internaltip and associated questionnaire schema for rtip, itip, answers, aqs in session.query(models.ReceiverTip, models.InternalTip, models.InternalTipAnswers, models.ArchivedSchema) \ .filter(models.ReceiverTip.receiver_id == receiver_id, models.InternalTip.id == models.ReceiverTip.internaltip_id, models.InternalTipAnswers.internaltip_id == models.InternalTip.id, models.ArchivedSchema.hash == models.InternalTipAnswers.questionnaire_hash, models.InternalTip.tid == tid) \ .order_by(models.InternalTip.progressive.desc(), models.InternalTipAnswers.creation_date.asc()): if rtip.id in rtip_ids: continue rtip_ids.append(rtip.id) itip_ids.append(itip.id) answers = answers.answers if itip.crypto_tip_pub_key: tip_key = GCE.asymmetric_decrypt( user_key, base64.b64decode(rtip.crypto_tip_prv_key)) answers = json.loads( GCE.asymmetric_decrypt(tip_key, base64.b64decode( answers.encode())).decode()) data = { 'id': rtip.id, 'itip_id': itip.id, 'creation_date': itip.creation_date, 'last_access': rtip.last_access, 'wb_last_access': itip.wb_last_access, 'update_date': itip.update_date, 'expiration_date': itip.expiration_date, 'progressive': itip.progressive, 'updated': rtip.access_counter == 0 or rtip.last_access < itip.update_date, 'context_id': itip.context_id, 'access_counter': rtip.access_counter, 'https': itip.https, 'questionnaire': db_serialize_archived_questionnaire_schema(aqs.schema, language), 'answers': answers, 'score': itip.total_score, 'status': itip.status, 'substatus': itip.substatus } if State.tenant_cache[tid].enable_private_annotations: data['important'] = rtip.important data['label'] = rtip.label else: data['important'] = itip.important data['label'] = itip.label rtip_summary_list.append(data) # Fetch messages count for rtip_id, count in session.query(models.ReceiverTip.id, func.count(distinct(models.Message.id))) \ .filter(models.Message.receivertip_id == models.ReceiverTip.id, models.ReceiverTip.id.in_(rtip_ids)) \ .group_by(models.ReceiverTip.id): messages_by_rtip[rtip_id] = count # Fetch comments count for itip_id, count in session.query(models.InternalTip.id, func.count(distinct(models.Comment.id))) \ .filter(models.Comment.internaltip_id == models.InternalTip.id, models.InternalTip.id.in_(itip_ids)) \ .group_by(models.InternalTip.id): comments_by_itip[itip_id] = count # Fetch attachment count for itip_id, count in session.query(models.InternalTip.id, func.count(distinct(models.InternalFile.id))) \ .filter(models.InternalFile.internaltip_id == models.InternalTip.id, models.InternalTip.id.in_(itip_ids)) \ .group_by(models.InternalTip.id): files_by_itip[itip_id] = count for elem in rtip_summary_list: elem['file_count'] = files_by_itip.get(elem['itip_id'], 0) elem['comment_count'] = comments_by_itip.get(elem['itip_id'], 0) elem['message_count'] = messages_by_rtip.get(elem['id'], 0) return rtip_summary_list
# BEGIN MOCKS NECESSARY FOR DETERMINISTIC ENCRYPTION VALID_PASSWORD1 = 'ACollectionOfDiplomaticHistorySince_1966_ToThe_Pr esentDay#' VALID_PASSWORD2 = VALID_PASSWORD1 VALID_SALT1 = GCE.generate_salt() VALID_SALT2 = GCE.generate_salt() VALID_HASH1 = GCE.hash_password(VALID_PASSWORD1, VALID_SALT1) VALID_HASH2 = GCE.hash_password(VALID_PASSWORD2, VALID_SALT2) VALID_BASE64_IMG = 'iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVQYV2NgYAAAAAMAAWgmWQ0AAAAASUVORK5CYII=' INVALID_PASSWORD = '******' KEY = GCE.generate_key() USER_KEY = GCE.derive_key(VALID_PASSWORD1, VALID_SALT1) USER_PRV_KEY, USER_PUB_KEY = GCE.generate_keypair() USER_PRV_KEY_ENC = Base64Encoder.encode(GCE.symmetric_encrypt(USER_KEY, USER_PRV_KEY)) USER_BKP_KEY, USER_REC_KEY = GCE.generate_recovery_key(USER_PRV_KEY) USER_REC_KEY_PLAIN = GCE.asymmetric_decrypt(USER_PRV_KEY, Base64Encoder.decode(USER_REC_KEY)) USER_REC_KEY_PLAIN = Base32Encoder.encode(USER_REC_KEY_PLAIN).replace(b'=', b'').decode('utf-8') GCE_orig_generate_key = GCE.generate_key GCE_orig_generate_keypair = GCE.generate_keypair def GCE_mock_generate_key(): return KEY def GCE_mock_generate_keypair(): return USER_PRV_KEY, USER_PUB_KEY setattr(GCE, 'generate_key', GCE_mock_generate_key) setattr(GCE, 'generate_keypair', GCE_mock_generate_keypair)
# BEGIN MOCKS NECESSARY FOR DETERMINISTIC ENCRYPTION VALID_PASSWORD1 = u'ACollectionOfDiplomaticHistorySince_1966_ToThe_Pr esentDay#' VALID_PASSWORD2 = VALID_PASSWORD1 VALID_SALT1 = GCE.generate_salt() VALID_SALT2 = GCE.generate_salt() VALID_HASH1 = GCE.hash_password(VALID_PASSWORD1, VALID_SALT1) VALID_HASH2 = GCE.hash_password(VALID_PASSWORD2, VALID_SALT2) VALID_BASE64_IMG = 'iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVQYV2NgYAAAAAMAAWgmWQ0AAAAASUVORK5CYII=' INVALID_PASSWORD = u'antani' KEY = GCE.generate_key() USER_KEY = GCE.derive_key(VALID_PASSWORD1, VALID_SALT1) USER_PRV_KEY, USER_PUB_KEY = GCE.generate_keypair() USER_PRV_KEY_ENC = GCE.symmetric_encrypt(USER_KEY, USER_PRV_KEY) USER_BKP_KEY, USER_REC_KEY = GCE.generate_recovery_key(USER_PRV_KEY) USER_REC_KEY_PLAIN = GCE.asymmetric_decrypt(USER_PRV_KEY, USER_REC_KEY) USER_REC_KEY_PLAIN = Base32Encoder().encode(USER_REC_KEY_PLAIN).replace(b'=', b'').decode() GCE_orig_generate_key = GCE.generate_key GCE_orig_generate_keypair = GCE.generate_keypair def GCE_mock_generate_key(): return KEY def GCE_mock_generate_keypair(): return USER_PRV_KEY, USER_PUB_KEY setattr(GCE, 'generate_key', GCE_mock_generate_key) setattr(GCE, 'generate_keypair', GCE_mock_generate_keypair)
def get_receivertips(session, tid, receiver_id, user_key, language): """ Return list of submissions received by the specified receiver :param session: An ORM session :param tid: The tenant ID :param receiver_id: The receiver ID :param user_key: The user key to be used for decrypting data :param language: The language to be used during data serialization :return: A list of submissions descriptors """ rtip_summary_list = [] rtip_ids = [] itip_ids = [] messages_by_rtip = {} comments_by_itip = {} internalfiles_by_itip = {} # Fetch rtip, internaltip and associated questionnaire schema result = session.query(models.ReceiverTip, models.InternalTip, models.ArchivedSchema) \ .filter(models.ReceiverTip.receiver_id == receiver_id, models.InternalTip.id == models.ReceiverTip.internaltip_id, models.InternalTipAnswers.internaltip_id == models.InternalTip.id, models.ArchivedSchema.hash == models.InternalTipAnswers.questionnaire_hash, models.InternalTip.tid == tid) for rtip, itip, aqs in result: rtip_ids.append(rtip.id) itip_ids.append(itip.id) preview = itip.preview if itip.crypto_tip_pub_key: tip_key = GCE.asymmetric_decrypt( user_key, base64.b64decode(rtip.crypto_tip_prv_key)) preview = json.loads( GCE.asymmetric_decrypt(tip_key, base64.b64decode( itip.preview.encode())).decode()) rtip_summary_list.append({ 'id': rtip.id, 'creation_date': datetime_to_ISO8601(itip.creation_date), 'last_access': datetime_to_ISO8601(rtip.last_access), 'wb_last_access': datetime_to_ISO8601(itip.wb_last_access), 'update_date': datetime_to_ISO8601(itip.update_date), 'expiration_date': datetime_to_ISO8601(itip.expiration_date), 'progressive': itip.progressive, 'new': rtip.access_counter == 0 or rtip.last_access < itip.update_date, 'context_id': itip.context_id, 'access_counter': rtip.access_counter, 'https': itip.https, 'preview_schema': db_serialize_archived_preview_schema(aqs.preview, language), 'preview': preview, 'score': itip.total_score, 'label': rtip.label, 'status': itip.status, 'substatus': itip.substatus }) # Fetch messages count result = session.query(models.ReceiverTip.id, func.count(distinct(models.Message.id))) \ .filter(models.Message.receivertip_id == models.ReceiverTip.id, models.ReceiverTip.id.in_(rtip_ids)) \ .group_by(models.ReceiverTip.id) for rtip_id, count in result: messages_by_rtip[rtip_id] = count # Fetch comments count result = session.query(models.InternalTip.id, func.count(distinct(models.Comment.id))) \ .filter(models.Comment.internaltip_id == models.InternalTip.id, models.InternalTip.id.in_(itip_ids)) \ .group_by(models.InternalTip.id) for itip_id, count in result: comments_by_itip[itip_id] = count # Fetch attachment count result = session.query(models.InternalTip.id, func.count(distinct(models.InternalFile.id))) \ .filter(models.InternalFile.internaltip_id == models.InternalTip.id, models.InternalTip.id.in_(itip_ids)) \ .group_by(models.InternalTip.id) for itip_id, count in result: internalfiles_by_itip[itip_id] = count for elem in rtip_summary_list: elem['file_count'] = internalfiles_by_itip.get(itip.id, 0) elem['comment_count'] = comments_by_itip.get(itip.id, 0) elem['message_count'] = messages_by_rtip.get(rtip.id, 0) return rtip_summary_list