def terminated(self): # (1, _("Помер")), # (2, _("Звільнився/склав повноваження")), # (3, _("Пов'язана особа або член сім'ї - ПЕП помер")), # (4, _("Пов'язана особа або член сім'ї - ПЕП припинив бути ПЕПом")), # (5, _("Зміни у законодавстві що визначає статус ПЕПа")), # (6, _("Зміни форми власності юр. особи посада в котрій давала статус ПЕПа")), if self.reason_of_termination in [1, 3]: return True if (self.reason_of_termination in [2, 4, 5, 6] and self.termination_date is not None): if (ceil_date(self.termination_date, self.termination_date_details) + datetime.timedelta(days=3 * 365) <= datetime.date.today()): return True return False
def handle(self, *args, **options): def _fetch_person(task, pk): try: return Person.objects.get(pk=pk) except Person.DoesNotExist: self.stderr.write( "\tperson with id {} doesn't exist, skipping".format(pk) ) return None def _delete_person(task, pk): person = _fetch_person(task, pk) if person: self.stdout.write( "\tdeleting person {} with id {}, imported={}".format( person.full_name, person.pk, person.imported) ) if options["real_run"]: person.delete() cursor = connection.cursor() for task in PersonDeduplication.objects.filter( applied=False).exclude(status="p"): self.stdout.write("Task #{}:".format(task.pk)) if task.status == "a": self.stdout.write("\tskipping") if task.status in ["d1", "dd"]: if task.status == "d1": self.stdout.write( "\tkeeping {}".format( task.person2_id) ) _delete_person(task, task.person1_id) if task.status in ["d2", "dd"]: if task.status == "d2": self.stdout.write( "\tkeeping {}".format( task.person1_id) ) _delete_person(task, task.person2_id) if task.status == "m": person1 = _fetch_person(task, max(task.person1_id, task.person2_id)) person2 = _fetch_person(task, min(task.person1_id, task.person2_id)) if person1 is None or person2 is None: continue # Round 1: fight: if len(person1.full_name) > len(person2.full_name): master = person1 donor = person2 self.stdout.write("\tpreferring {} over {}".format( person1.full_name, person2.full_name)) else: master = person2 donor = person1 self.stdout.write("\tpreferring {} over {}".format( person2.full_name, person1.full_name)) # Transfering data fields # Those to concatenate for field in FIELDS_TO_CONCATENATE: donor_val = getattr(donor, field) master_val = getattr(master, field) if donor_val and donor_val.strip(): setattr(master, field, ((master_val or "") + " " + donor_val).strip()) self.stdout.write("\tconcatenating content of {}".format( field)) # Those to overwrite for field in FIELDS_TO_UPDATE: donor_val = getattr(donor, field) master_val = getattr(master, field) if donor_val and not master_val: setattr(master, field, donor_val) self.stdout.write("\treplacing content of {}".format( field)) # Corner case: if field == "dob": if donor_val and master_val and (donor.dob_details < master.dob_details): master.dob = donor.dob master.dob_details = donor.dob_details self.stdout.write("\timproving content of {} (replacing {} with {})".format( field, master.dob, donor.dob)) if donor.reason_of_termination == None and master.reason_of_termination is not None and master.reason_of_termination != 1: master.reason_of_termination = None master.termination_date = None master.termination_date_details = 0 self.stdout.write("\tResurrecting person as a pep, because donor has no termination date") if donor.type_of_official != master.type_of_official: self.stdout.write("\tSwitching pep level to {}".format( donor.type_of_official)) master.type_of_official = donor.type_of_official elif donor.reason_of_termination is not None and master.reason_of_termination is not None: if ceil_date(donor.termination_date, donor.termination_date_details) > ceil_date(master.termination_date, master.termination_date_details): master.termination_date = donor.termination_date master.termination_date_details = donor.termination_date_details self.stdout.write("\tUpdating termination date from {} to {} for a person".format( ceil_date(donor.termination_date, donor.termination_date_details), ceil_date(master.termination_date, master.termination_date_details) )) elif donor.reason_of_termination is None and master.reason_of_termination is None: # Another corner case: if donor.type_of_official < master.type_of_official: self.stdout.write("\tUpgrading pep level to {}".format( donor.type_of_official)) master.type_of_official = donor.type_of_official if options["real_run"]: master.save() # Merging relations with companies for p2c in Person2Company.objects.filter( from_person_id=donor.pk): self.stdout.write("\tchanging link {}".format(p2c)) if options["real_run"]: p2c.from_person = master p2c.save() # Merging relations with countries for p2c in Person2Country.objects.filter( from_person_id=donor.pk): self.stdout.write("\tchanging link {}".format(p2c)) if options["real_run"]: p2c.from_person = master p2c.save() # Merging relations with other persons for p2p in Person2Person.objects.filter( from_person_id=donor.pk): self.stdout.write("\tchanging link {}".format(p2p)) if options["real_run"]: p2p.from_person_id = master.pk p2p.save() for p2p in Person2Person.objects.filter( to_person_id=donor.pk): self.stdout.write("\tchanging link {}".format(p2p)) if options["real_run"]: p2p.to_person_id = master.pk p2p.save() # Merging declarations for decl in Declaration.objects.filter( person=donor.pk): if Declaration.objects.filter( person=master.pk, declaration_id=decl.declaration_id).count() == 0: self.stdout.write( "\tswitching declaration {}".format(decl)) if options["real_run"]: decl.person = master decl.save() else: decl.delete() self.stdout.write( "\t not switching declaration {}, deleting it".format(decl)) # TODO: Move also DeclarationExtra self.stdout.write( "\tkeeping {} with id {}".format( master.pk, master.full_name) ) self.stdout.write( "\tdeleting {} with id {}, imported={}".format( donor.pk, donor.full_name, donor.imported) ) if options["real_run"]: AdHocMatch.objects.filter(person=donor).update(person=None) # Kill the donor! # Raw SQL because otherwise django will also kill the old # connections of donor person, which are stuck for some reason. cursor.execute( "DELETE from core_person WHERE id=%s", [donor.pk] ) if options["real_run"]: task.applied = True task.save()
unicode(edrpou).rjust(8, "0") ]).first() personnel = {} for p2c in Person2Company.objects.filter(to_company_id=company.pk): personnel[p2c.from_person_id] = p2c for p2p in Person2Person.objects.filter( from_person_id__in=personnel.keys(), to_person_id__in=personnel.keys()).select_related( "from_person", "to_person"): dates_1 = [ floor_date( personnel[p2p.from_person_id].date_established, personnel[p2p.from_person_id].date_established_details), ceil_date(personnel[p2p.from_person_id].date_finished, personnel[p2p.from_person_id].date_finished_details) ] dates_2 = [ floor_date( personnel[p2p.to_person_id].date_established, personnel[p2p.to_person_id].date_established_details), ceil_date(personnel[p2p.to_person_id].date_finished, personnel[p2p.to_person_id].date_finished_details) ] if dates_1[0] is None: dates_1[0] = date(1991, 1, 1) if dates_2[0] is None: dates_2[0] = date(1991, 1, 1)
def all_related_persons(self): related_persons = [ (i.relationship_type_uk, deepcopy(i.from_person), i) for i in self.from_persons.prefetch_related("from_person").defer( "from_person__reputation_assets", "from_person__reputation_crimes", "from_person__reputation_manhunt", "from_person__reputation_convictions", "from_person__wiki", "from_person__names", "from_person__hash", ).order_by("from_person__last_name_uk", "from_person__first_name_uk") ] res = { "managers": [], "founders": [], "sanctions": [], "bank_customers": [], "rest": [], "all": [] } for rtp, p, rel in related_persons: add_to_rest = True p.rtype = rtp p.connection = rel res["all"].append(p) if any(map(lambda x: x.search(rtp.lower()), self.HEADS_CLASSIFIERS)): if (rel.date_finished and ceil_date(rel.date_finished, rel.date_finished_details) <= date.today()): add_to_rest = True else: res["managers"].append(p) add_to_rest = False elif rtp.lower() in [ "засновник", "учасник", "власник", "бенефіціарний власник", "номінальний власник", "колишній засновник/учасник", ]: res["founders"].append(p) add_to_rest = False elif rtp.lower() in ["клієнт банку"]: res["bank_customers"].append(p) add_to_rest = False if p.reputation_sanctions: res["sanctions"].append(p) add_to_rest = False if add_to_rest: res["rest"].append(p) return res
def handle(self, *args, **options): # One day I'll offload that to Neo4J and nail it down with 3 simple queries # But for now, let's the rampage begin activate(settings.LANGUAGE_CODE) almost_fired_persons = [] fired_persons = Person.objects.filter( reason_of_termination__isnull=False).values_list("pk", flat=True) # First pass: find those PEPs who resigned or has no position in public bodies/SE peps_by_position = Person.objects.filter( type_of_official=1, reason_of_termination__isnull=True).nocache() probably_not_peps = {} for pep in peps_by_position.iterator(): has_open_positions = ( Person2Company.objects.prefetch_related("to_company").filter( from_person=pep, date_finished__isnull=True, to_company__state_company=True, ).exclude(relationship_type_uk__in=["Клієнт банку"]).exists()) # Person got a connection to state company and not yet resigned # moving forward if has_open_positions: continue last_workplace = pep.last_workplace pep_position = ("{} @ {}".format(last_workplace["company"], last_workplace["position"]) if last_workplace else "") has_connections_to_state = ( Person2Company.objects.prefetch_related("to_company").filter( from_person=pep, to_company__state_company=True).exclude( relationship_type_uk__in=["Клієнт банку"]).exists()) if not has_connections_to_state: # PEP by position has no connections to state at all TerminationNotice.objects.get_or_create( pep_name=pep.full_name, person=pep, action="review", defaults={ "pep_position": pep_position, "comments": "Взагалі нема зв'язків з юр. особами які роблять ПЕПом за посадою", }, ) continue last_date_on_job = ( Person2Company.objects.prefetch_related("to_company").filter( from_person=pep, to_company__state_company=True).order_by( "-date_finished").exclude( relationship_type_uk__in=["Клієнт банку"]).first()) # Not creating termination notice yet as we need to check also if resigned PEP # has no relatives who are still PEPs probably_not_peps[pep.pk] = pep, last_date_on_job # Second pass for pep, last_date_on_job in probably_not_peps.values(): last_workplace = pep.last_workplace pep_position = ("{} @ {}".format(last_workplace["company"], last_workplace["position"]) if last_workplace else "") # Let's check if our candidates has a friends in high places to_persons = list( Person2Person.objects.prefetch_related("to_person").filter( from_person=pep, # Національний публічний діяч, Іноземний публічний діяч, Діяч, що виконуює значні функції в міжнародній організації to_person__type_of_official__in=[1, 2, 3], ).values_list("to_person", flat=True)) from_persons = list( Person2Person.objects.prefetch_related("from_persons").filter( to_person=pep, # Національний публічний діяч, Іноземний публічний діяч, Діяч, що виконуює значні функції в міжнародній організації from_person__type_of_official__in=[1, 2, 3], ).values_list("from_person", flat=True)) # Here we have three possible options. # 1. PEP by position has no positions to make him PEP anymore and no connections to other PEPs # We need to change his profile and record the fact that he won't be a PEP after 3 years since resignation # 2. Same as #1 but has other PEPs by position in connections, which makes him a related person instead of PEP # We need to change his profile and make him a related person instead, after 3 years since his resignation # 3. He is connected to some ex-PEPs by position, who is also resigned. # Then we need to make him a related person and then remove him from PEPs after 3 years since last resignation # (his or his croonies) has passed just_let_him_die = False switch_him_to_related = False still_a_friend_of = None remove_him_from_related = False remove_him_from_related_since = { "dt": last_date_on_job.date_finished, "dt_details": last_date_on_job.date_finished_details, "dt_ceiled": ceil_date( last_date_on_job.date_finished, last_date_on_job.date_finished_details, ), } # TODO: ignore those who is resigned recently self.stdout.write( "Reviewing {} who got {} friends in high places".format( pep, len(set(to_persons + from_persons)))) if to_persons + from_persons: for friend in Person.objects.filter(pk__in=to_persons + from_persons): # Check if we've found a PEP's friend who is not resigned yet: if (friend.pk not in probably_not_peps and not friend.reason_of_termination): self.stdout.write( "\tWe've found a friend {} who still holds office". format(friend)) switch_him_to_related = True still_a_friend_of = friend remove_him_from_related = False # Let's get outta here break # When his friend resigned earlier too: if friend.reason_of_termination: self.stdout.write( "\tWe've found a friend {} who is already resigned" .format(friend)) friend_dt_ceiled = ceil_date( friend.termination_date, friend.termination_date_details) if (friend_dt_ceiled > remove_him_from_related_since["dt_ceiled"]): still_a_friend_of = friend remove_him_from_related = True remove_him_from_related_since[ "dt_ceiled"] = friend_dt_ceiled remove_him_from_related_since[ "dt"] = friend.termination_date remove_him_from_related_since[ "dt_details"] = friend.termination_date_details if friend.pk in probably_not_peps: self.stdout.write( "\tWe've found a friend {} who is also about to set to resigned" .format(friend)) _, friend_last_date_on_job = probably_not_peps[ friend.pk] friend_dt_ceiled = ceil_date( friend_last_date_on_job.date_finished, friend_last_date_on_job.date_finished_details, ) if (friend_dt_ceiled > remove_him_from_related_since["dt_ceiled"]): still_a_friend_of = friend remove_him_from_related = True remove_him_from_related_since[ "dt_ceiled"] = friend_dt_ceiled remove_him_from_related_since[ "dt"] = friend_last_date_on_job.date_finished remove_him_from_related_since[ "dt_details"] = friend_last_date_on_job.date_finished_details else: just_let_him_die = True if not switch_him_to_related and not remove_him_from_related: # Even if person had friends in high places but they all lost # their position before he resigned, we'll just mark him as one # to fire just_let_him_die = True if just_let_him_die: # Person is not a PEP by position anymore and doesn't have other PEPs connected with him almost_fired_persons.append(pep.pk) TerminationNotice.objects.get_or_create( pep_name=pep.full_name, person=pep, new_person_status=2, # Resigned action="fire", defaults={ "termination_date": last_date_on_job.date_finished, "termination_date_details": last_date_on_job.date_finished_details, "termination_date_ceiled": ceil_date( last_date_on_job.date_finished, last_date_on_job.date_finished_details, ), "pep_position": pep_position, "comments": '{} звільнився з посади "{}" у "{}"'.format( last_date_on_job.date_finished_human, last_date_on_job.relationship_type_uk, last_date_on_job.to_company.name, ), }, ) elif switch_him_to_related: # Person is not a PEP by position anymore but have related persons who are PEPs by position try: TerminationNotice.objects.get_or_create( pep_name=pep.full_name, person=pep, action="change_type", defaults={ "termination_date": last_date_on_job.date_finished, "termination_date_details": last_date_on_job.date_finished_details, "termination_date_ceiled": ceil_date( last_date_on_job.date_finished, last_date_on_job.date_finished_details, ), "pep_position": pep_position, "comments": '{} звільнився з посади "{}" у "{}" але залишився пов\'язаною особою до {}' .format( last_date_on_job.date_finished_human, last_date_on_job.relationship_type_uk, last_date_on_job.to_company.name, still_a_friend_of, ), }, ) except django.db.utils.IntegrityError: pass elif remove_him_from_related: # Person is not a PEP by position anymore and all his related persons who was PEPs by position # is also not a PEPs anymore almost_fired_persons.append(pep.pk) TerminationNotice.objects.get_or_create( pep_name=pep.full_name, person=pep, action="change_and_fire", new_person_status= 4, # "Пов'язана особа або член сім'ї - ПЕП припинив бути ПЕПом" defaults={ "termination_date": remove_him_from_related_since["dt"], "termination_date_details": remove_him_from_related_since["dt_details"], "termination_date_ceiled": remove_him_from_related_since["dt_ceiled"], "pep_position": pep_position, "comments": '{} звільнився з посади "{}" у "{}" але залишився пов\'язаною особою до {}, що теж звільнився {}' .format( last_date_on_job.date_finished_human, last_date_on_job.relationship_type_uk, last_date_on_job.to_company.name, still_a_friend_of, render_date( remove_him_from_related_since["dt"], remove_him_from_related_since["dt_details"], ), ), }, ) else: self.stderr.write("Unknown action for {}".format(pep)) # Here is the rough list of family members of real PEPs which we are gonna # narrow down by checking if their real PEPs are still acting family_members = Person.objects.filter( type_of_official__in=[5], reason_of_termination__isnull=True).nocache() true_family_members = [] for family_member in family_members.iterator(): # Find all family members of true acting PEPs to_persons_cnt = ( Person2Person.objects.prefetch_related("to_person").filter( from_person=family_member, to_person__type_of_official__in=[1, 2, 3]). exclude( to_person__pk__in=(fired_persons) # to_person__pk__in=(almost_fired_persons + fired_persons) ).count()) from_persons_cnt = ( Person2Person.objects.prefetch_related("from_persons").filter( to_person=family_member, from_person__type_of_official__in=[1, 2, 3]). exclude( from_person__pk__in=(fired_persons) # from_person__pk__in=(almost_fired_persons + fired_persons) ).count()) if to_persons_cnt + from_persons_cnt: true_family_members.append(family_member.pk) self.stdout.write("Found {} true family members".format( len(true_family_members))) # according to methodology any connection to a family member of a real pep makes # other connections of that person a PEP peps_by_connections = Person.objects.filter( type_of_official__in=[4, 5], reason_of_termination__isnull=True).nocache() for pep in peps_by_connections.iterator(): # Let's check if our candidates amongst family members has acting friends in high places to_pep_persons_cnt = ( Person2Person.objects.prefetch_related("to_person").filter( from_person=pep, to_person__type_of_official__in=[1, 2, 3] ).exclude( # to_person__pk__in=(almost_fired_persons + fired_persons) to_person__pk__in=(fired_persons)).count()) from_pep_persons_cnt = ( Person2Person.objects.prefetch_related("from_persons").filter( to_person=pep, from_person__type_of_official__in=[1, 2, 3] ).exclude( # from_person__pk__in=(almost_fired_persons + fired_persons) from_person__pk__in=(fired_persons)).count()) if to_pep_persons_cnt + from_pep_persons_cnt: # Still has some acting friends or relatives in high places, # skipping continue to_family_members_cnt = ( Person2Person.objects.prefetch_related("to_person").filter( from_person=pep, to_person__pk__in=true_family_members).count()) from_family_members_cnt = ( Person2Person.objects.prefetch_related("from_persons").filter( to_person=pep, from_person__pk__in=true_family_members).count()) if to_family_members_cnt + from_family_members_cnt: self.stdout.write( "\tNot deleting {} as it has {} connections to true family members" .format(pep, to_family_members_cnt + from_family_members_cnt)) continue # Determining the date of dismissal to_fired_persons = list( Person2Person.objects.prefetch_related("to_person").filter( from_person=pep, to_person__type_of_official__in=[1, 2, 3], to_person__pk__in=(fired_persons), ).values_list("to_person", flat=True)) from_fired_persons = list( Person2Person.objects.prefetch_related("from_persons").filter( to_person=pep, from_person__type_of_official__in=[1, 2, 3], from_person__pk__in=(fired_persons), ).values_list("from_person", flat=True)) last_survivor = None for fired_pep in Person.objects.filter(pk__in=to_fired_persons + from_fired_persons): if not fired_pep.terminated: continue if not last_survivor: last_survivor = fired_pep elif ceil_date(fired_pep.termination_date, fired_pep.termination_date_details) > ceil_date( last_survivor.termination_date, last_survivor.termination_date_details, ): last_survivor = fired_pep if last_survivor is None: self.stdout.write( "All the peps by position connected to {} resigned but they are still peps, so skipping" .format(pep)) continue last_workplace = pep.last_workplace pep_position = ("{} @ {}, ".format(last_workplace["company"], last_workplace["position"]) if last_workplace else "") pep_position += "Пов'язана особа до {}".format( last_survivor.full_name) TerminationNotice.objects.get_or_create( pep_name=pep.full_name, person=pep, action="fire_related", # "Пов'язана особа або член сім'ї - ПЕП помер" # vs # "Пов'язана особа або член сім'ї - ПЕП припинив бути ПЕПом" new_person_status=3 if last_survivor.reason_of_termination == 1 else 4, defaults={ "termination_date": last_survivor.termination_date, "termination_date_details": last_survivor.termination_date_details, "termination_date_ceiled": ceil_date( last_survivor.termination_date, last_survivor.termination_date_details, ), "pep_position": pep_position, "comments": "Припинив бути ПЕПом з {}, тому що остання пов'язана особа {} припинила бути ПЕПом з причини '{}'" .format( render_date( last_survivor.termination_date, last_survivor.termination_date_details, ), last_survivor.full_name, last_survivor.get_reason_of_termination_display(), ), }, ) if options["on_date"]: on_date = dt_parse(options["on_date"], yearfirst=True) TerminationNotice.objects.filter( termination_date_ceiled__gte=on_date - timedelta(days=365 * 3), status="p").delete()