def unified_foreign_registry_import(self, request):
if request.method == "GET":
return render(
request,
"admin/core/company/unified_import.html",
{"form": ForeignImportForm()},
)
if request.method == "POST":
form = ForeignImportForm(request.POST, request.FILES)
if not form.is_valid():
return render(request,
"admin/core/company/unified_import.html",
{"form": form})
created_records = 0
updated_records = 0
r = DictReader(request.FILES["csv"])
importer = CompanyImporter(logger=MessagesLogger(request))
conn_importer = Company2CountryImporter(
logger=MessagesLogger(request))
for entry in r:
company, created = importer.get_or_create_from_unified_foreign_registry(
entry)
if not company:
continue
if created:
created_records += 1
else:
updated_records += 1
country_connection, _ = conn_importer.get_or_create(
company,
entry.get("country", "").strip(), "registered_in")
self.message_user(
request,
"Створено %s компаній, оновлено %s" %
(created_records, updated_records),
)
return redirect(reverse("admin:core_company_changelist"))
def edr_import(self, request):
if request.method == "GET":
return render(
request,
"admin/core/company/edr_import.html",
{"form": EDRImportForm(initial={"is_state_companies": True})},
)
if request.method == "POST":
form = EDRImportForm(request.POST, request.FILES)
if not form.is_valid():
return render(request, "admin/core/company/edr_import.html",
{"form": form})
created_records = 0
updated_records = 0
r = DictReader(request.FILES["csv"])
importer = CompanyImporter(logger=MessagesLogger(request))
for entry in r:
company, created = importer.get_or_create_from_edr_record(
entry)
if not company:
continue
if created:
created_records += 1
else:
updated_records += 1
company.state_company = company.state_company or form.cleaned_data.get(
"is_state_companies", False)
company.save()
self.message_user(
request,
"Створено %s компаній, оновлено %s" %
(created_records, updated_records),
)
return redirect(reverse("admin:core_company_changelist"))
def handle(self, *args, **options):
activate(settings.LANGUAGE_CODE)
self.importer = CompanyImporter(logger=PythonLogger("cli_commands"))
self.conn_importer = Person2CompanyImporter(
logger=PythonLogger("cli_commands"))
self.successful = 0
self.failed = 0
self.companies_created = 0
self.companies_updated = 0
self.connections_created = 0
self.connections_updated = 0
self.connect_domestic_companies(options["real_run"])
self.connect_foreign_companies(options["real_run"])
self.stdout.write("Creation failed: %s, creation successful: %s" %
(self.failed, self.successful))
self.stdout.write("Companies created: %s, companies updated: %s" %
(self.companies_created, self.companies_updated))
self.stdout.write("Connections created: %s, connections updated: %s" %
(self.connections_created, self.connections_updated))
def handle(self, *args, **options):
company_code_path = jmespath.compile(
"nacp_orig.step_7.*.emitent_ua_company_code")
save_it = options["real_run"]
activate(settings.LANGUAGE_CODE)
self.importer = CompanyImporter(logger=PythonLogger("cli_commands"))
self.conn_importer = Person2CompanyImporter(
logger=PythonLogger("cli_commands"))
successful = 0
failed = 0
total = 0
companies_created = 0
companies_updated = 0
connections_created = 0
connections_updated = 0
for rec in AdHocMatch.objects.filter(
status="a",
dataset_id="smida_10").prefetch_related("person").nocache():
total += 1
if "EDRPOU" not in rec.matched_json:
self.stderr.write(
"Approved company {} has no edrpou!, skipping".format(
rec.pk))
failed += 1
continue
if rec.person is None:
self.stderr.write(
"Cannot find a person rec {}, skipping".format(rec.pk))
failed += 1
continue
ans = EDRPOU.find_by_edrpou(rec.matched_json["EDRPOU"])
if len(ans) > 1:
self.stderr.write(
"Too many companies found by code {}, skipping".format(
rec.matched_json["EDRPOU"]))
failed += 1
continue
if not ans:
self.stderr.write(
"No company found by code {}, skipping".format(
rec.matched_json["EDRPOU"]))
failed += 1
continue
company, created = self.importer.get_or_create_from_edr_record(
ans[0].to_dict(), save_it)
if not company:
self.stderr.write(
"Cannot create a company by code {}, for the rec {}, skipping"
.format(rec.matched_json["EDRPOU"], rec.pk))
failed += 1
continue
if created:
companies_created += 1
self.stdout.write("Created company {}".format(company))
else:
companies_updated += 1
self.stdout.write("Updated company {}".format(company))
existing_connections = Person2Company.objects.filter(
from_person=rec.person,
to_company=company).exclude(relationship_type_uk="Акціонер")
if existing_connections:
for ex_conn in existing_connections:
self.stderr.write(
"Connection between {} and {} already exists but has type {}"
.format(ex_conn.from_person, ex_conn.to_company,
ex_conn.relationship_type))
conn, conn_created = self.conn_importer.get_or_create(
rec.person, company, "Акціонер",
rec.last_updated_from_dataset.date(),
"https://smida.gov.ua/db/emitent/{}".format(
rec.matched_json["EDRPOU"]),
"За інформацією Агентства з розвитку інфраструктури фондового ринку України (АРІФРУ)",
"According to the information Stock market infrastructure development agency of Ukraine (SMIDA)",
save_it)
if conn_created:
connections_created += 1
else:
connections_updated += 1
if "share" in rec.matched_json:
conn.share = float(rec.matched_json["share"].replace(
",", ".").strip())
if save_it:
conn.save()
decls = rec.person.get_declarations()
if decls:
decl = decls[0]
if decl.nacp_declaration:
declared_companies = company_code_path.search(
decl.source) or []
declared_companies = list(
filter(
None,
set(
map(lambda x: x.lstrip("0"),
declared_companies))))
if rec.matched_json["EDRPOU"].lstrip(
"0") not in declared_companies:
self.stderr.write(
"Cannot find company {} ({}) in declaration {} of {}"
.format(company, company.edrpou, decl.url,
rec.person))
else:
self.stderr.write(
"No declaration found for person {}".format(
rec.person))
self.stdout.write(
"{} records processed, failed: {}, successed: {}".format(
total, failed, successful))
self.stdout.write(
"Companies created: {}, companies updated: {}".format(
companies_created, companies_updated))
self.stdout.write(
"Connections created: {}, connections updated: {}".format(
connections_created, connections_updated))
class Command(BaseCommand):
EXCLUDE_PATTERNS = (
re.compile("РАЙОН", flags=re.I),
re.compile("ДЕРЖАВНОЇ АДМ[iіIІ]Н[iіIІ]СТРАЦ[iіIІ]Ї", flags=re.I),
re.compile("ЄДНАНЕ УПРАВЛ[iіIІ]ННЯ ПЕНС[iіIІ]ЙНОГО ФОНДУ УКРАЇНИ",
flags=re.I),
re.compile("М[iіIІ]СЬКИЙ"),
re.compile("У М\.", flags=re.I),
re.compile("У М[iіIІ]СТ[iіIІ]", flags=re.I),
re.compile("ДЕРЖАВНОГО КАЗНАЧЕЙСТВА", flags=re.I),
re.compile("М[iіIІ]СЬКОЇ РАДИ", flags=re.I),
re.compile("СЕЛИЩНОЇ РАДИ", flags=re.I),
re.compile("РАЙДЕРЖАДМ[iіIІ]Н[iіIІ]СТРАЦ[iіIІ]Ї", flags=re.I),
re.compile("м[iіIІ]ста", flags=re.I),
re.compile("РЕСПУБЛ[iіIІ]Ц[iіIІ] КРИМ", flags=re.I),
re.compile("РЕСПУБЛ[iіIІ]КИ КРИМ", flags=re.I),
re.compile("АРХ[iіIІ]В", flags=re.I),
re.compile("М[iіIІ]СТА", flags=re.I),
re.compile("В[iіIІ]ДД[iіIІ]Л", flags=re.I),
re.compile("ТЕРИТОР[iіIІ]АЛЬНОГО УПРАВЛ[iіIІ]ННЯ ЮСТИЦ[iіIІ]Ї",
flags=re.I),
re.compile("ДЕРЖАВНА НОТАР[iіIІ]АЛЬНА", flags=re.I),
re.compile("ПОЛ[iіIІ]Ц[iіIІ]Ї ОХОРОНИ", flags=re.I),
re.compile("ОКРУЖНИЙ СУД", flags=re.I),
re.compile("ОБЛДЕРЖАДМ[iіIІ]Н[iіIІ]СТРАЦ[iіIІ]Ї", flags=re.I),
)
def add_arguments(self, parser):
parser.add_argument(
"--set_flag",
choices=[
"public_office",
"political_party",
"state_enterprise",
"affiliated_with_pep",
"bank",
"service_provider",
],
help="Set flag for the company",
)
parser.add_argument("infile", help="File with data to import")
def __init__(self):
self.company_info = {}
def _iter_xml(self, fp):
mapping = {
"Організаційно-правова_форма": "form",
"Найменування": "name",
"Скорочена_назва": "short_name",
"Код_ЄДРПОУ": "edrpou",
"Стан": "status",
"ADDRESS": "location",
"CHARTER_CAPITAL": "charter_capital",
"Дата_державної_реэстрації": "founded",
"Керівник": "head",
}
try:
content = fp.read()
except:
content = ""
with tqdm() as pbar:
while True:
chunk = fp.read(1024 * 1024 * 10)
if not chunk:
break
content += chunk
pbar.update(len(chunk))
regex = "<DATA_RECORD>.*?</DATA_RECORD>"
for chunk in tqdm(re.finditer(regex, content, flags=re.S | re.U)):
company = {}
founders_list = []
try:
etree = ET.fromstring(
chunk.group(0).replace(
"Місцезнаходження", "ADDRESS").replace(
"Статутний_капітал,_грн.",
"CHARTER_CAPITAL").replace(
"Засновник/кінцевий_бенефіціар",
"FOUNDER").replace(
"Код_ЄДРПОУ_засн/бенефіц",
"FOUNDER_CODE").replace(
"Частка_в_стат._капіталі,_грн.",
"SHARE").encode("utf-8"))
except Exception as e:
print("Error parsing {}, exception was {}".format(
chunk.group(0), e))
edrpou = None
for el in etree.getchildren():
if el.tag not in mapping:
continue
field = mapping[el.tag]
if field == "edrpou":
if el.text and el.text.lstrip("0"):
company[field] = int(el.text)
edrpou = int(el.text)
else:
company[field] = 0
elif field == "founded" and el.text:
company[field] = dt_parse(el.text, yearfirst=True).date()
else:
if field:
company[field] = el.text
if edrpou is not None:
if edrpou not in self.company_info:
self.company_info[edrpou] = company
yield company
def handle(self, *args, **kwargs):
activate(settings.LANGUAGE_CODE)
logger = PythonLogger("cli_commands")
self.importer = CompanyImporter(logger=logger)
filter_statuses = [
"припинено",
"ліквідація",
"розпорядження майном",
"зареєстровано, свідоцтво про державну реєстрацію недійсне",
]
if kwargs["set_flag"] == "public_office":
filter_statuses.append("в стані припинення")
with open(kwargs["infile"], encoding="cp1251") as fp:
for company_rec in self._iter_xml(fp):
company, created = self.importer.get_or_create_from_edr_record(
company_rec, save=False)
if created:
if company_rec["status"].lower() in filter_statuses:
continue
if kwargs["set_flag"] == "public_office":
if any(
map(
lambda x: x.search(company_rec["name"])
or x.search(
company_rec.get("short_name", "")),
self.EXCLUDE_PATTERNS,
)):
logger.warning(
"Skipping company {} with edrpou {} and status {}"
.format(
company_rec["name"],
company_rec["edrpou"],
company_rec["status"],
))
continue
logger.warning(
"New company {} with edrpou {} added and status {} created"
.format(
company_rec["name"],
company_rec["edrpou"],
company_rec["status"],
))
edrpou = int(company_rec["edrpou"])
if NAISCompany.objects.filter(pk=edrpou).exclude(
status="p").count():
logger.warning(
"Task for company {} with edrpou {} was already processed"
.format(company_rec["name"], company_rec["edrpou"]))
continue
task, _ = NAISCompany.objects.update_or_create(
pk=edrpou,
defaults={
"status":
"p",
"created":
created,
"matched_json":
company_rec,
"company_name":
company_rec.get("short_name", company_rec["name"]),
"company_status":
company_rec["status"],
"company_type":
kwargs["set_flag"],
},
)
def handle(self, *args, **kwargs):
activate(settings.LANGUAGE_CODE)
logger = PythonLogger("cli_commands")
self.importer = CompanyImporter(logger=logger)
filter_statuses = [
"припинено",
"ліквідація",
"розпорядження майном",
"зареєстровано, свідоцтво про державну реєстрацію недійсне",
]
if kwargs["set_flag"] == "public_office":
filter_statuses.append("в стані припинення")
with open(kwargs["infile"], encoding="cp1251") as fp:
for company_rec in self._iter_xml(fp):
company, created = self.importer.get_or_create_from_edr_record(
company_rec, save=False)
if created:
if company_rec["status"].lower() in filter_statuses:
continue
if kwargs["set_flag"] == "public_office":
if any(
map(
lambda x: x.search(company_rec["name"])
or x.search(
company_rec.get("short_name", "")),
self.EXCLUDE_PATTERNS,
)):
logger.warning(
"Skipping company {} with edrpou {} and status {}"
.format(
company_rec["name"],
company_rec["edrpou"],
company_rec["status"],
))
continue
logger.warning(
"New company {} with edrpou {} added and status {} created"
.format(
company_rec["name"],
company_rec["edrpou"],
company_rec["status"],
))
edrpou = int(company_rec["edrpou"])
if NAISCompany.objects.filter(pk=edrpou).exclude(
status="p").count():
logger.warning(
"Task for company {} with edrpou {} was already processed"
.format(company_rec["name"], company_rec["edrpou"]))
continue
task, _ = NAISCompany.objects.update_or_create(
pk=edrpou,
defaults={
"status":
"p",
"created":
created,
"matched_json":
company_rec,
"company_name":
company_rec.get("short_name", company_rec["name"]),
"company_status":
company_rec["status"],
"company_type":
kwargs["set_flag"],
},
)
def handle(self, *args, **options):
self.stdout.write("Starting matching job ...")
activate(settings.LANGUAGE_CODE)
# region Companies
self.stdout.write("Starting import Companies.")
pep_heads = self.company_heads_mapping()
companies_dict = {}
created_companies_total = 0
updated_companies_total = 0
failed_companies_total = 0
company_importer = CompanyImporter(logger=PythonLogger("cli_commands"))
smida_candidates = SMIDACandidate.objects.filter(status="a")
for candidate in tqdm(smida_candidates.nocache().iterator(),
total=smida_candidates.count()):
edrpou = candidate.smida_edrpou
if companies_dict.get(edrpou):
continue
ans = EDRPOU.find_by_edrpou(candidate.smida_edrpou)
if len(ans) > 1:
self.stderr.write(
"Too many companies found by code {}, skipping".format(edrpou)
)
failed_companies_total += 1
continue
if not ans:
self.stderr.write(
"No company found by code {}, skipping".format(edrpou)
)
failed_companies_total += 1
continue
company, created = company_importer.get_or_create_from_edr_record(
ans[0].to_dict(),
options["real_run"])
if created and edrpou in pep_heads:
company.state_company = True
if options["real_run"]:
company.save()
if not company:
self.stderr.write(
"Cannot create a company by code {}, for the rec {}, skipping".format(
edrpou,
candidate.pk
)
)
failed_companies_total += 1
continue
if created:
created_companies_total += 1
tqdm.write("Created {} {}".format("state company" if company.state_company else "company", company))
else:
updated_companies_total += 1
tqdm.write("Updated company {}".format(company))
companies_dict[edrpou] = company
self.stdout.write("Finished import companies.")
# endregion
# region Persons and P2C
self.stdout.write("Starting import Persons and Person2Company relations.")
smida_candidates = SMIDACandidate.objects.filter(status="a",
smida_is_real_person=True)\
.order_by("dt_of_first_entry")
peps = self.all_peps_names()
self.persons_dict = {}
self.new_persons_pk = []
self.persons_stats = {"created_total": 0, "matched_resolved": 0, "matched_not_resolved": 0}
p2c_links_created = 0
p2c_links_updated = 0
self.smida_p2c = self.person_2_companies_relations()
for candidate in tqdm(smida_candidates.nocache().iterator(),
total=smida_candidates.count()):
person_name = candidate.smida_parsed_name.strip().lower()
# If can't tie person with company skip it to avoid duplicates
if not any(edrpou in companies_dict for edrpou in self.smida_p2c[person_name]):
tqdm.write("Skipped person: {} from processing as he not tied to any valid EDRPOU."
.format(person_name))
continue
is_pep = person_name in peps
person = self.persons_dict.get(person_name)
if not person:
person = self.create_person(person_name, is_pep, candidate.smida_yob,
options["real_run"])
# The same person might have been created from a record without smida_yob
else:
self.update_person_dob(person, candidate.smida_yob, real_run=options["real_run"])
if person:
company = companies_dict.get(candidate.smida_edrpou)
if not company:
continue
pb_key = "{} {}".format(candidate.smida_position_class, candidate.smida_position_body)
relationship_type = SMIDA_POSITIONS_MAPPING.get(pb_key)
if not relationship_type:
relationship_type = candidate.smida_position
tqdm.write("Relation missing from a mapping for SMIDACandidate ID: {}"
.format(candidate.id))
# Calc date finished
last_entry = candidate.dt_of_last_entry
date_finished = self.p2c_get_date_finished(candidate)
if not date_finished and last_entry and last_entry.date() < self.threshold_quarter_end():
date_finished = last_entry
# Calc date established
date_established = self.p2c_get_date_established(candidate)
if date_established and (not date_finished or date_established.date() < date_finished.date()):
# update previous position on this work
prev_position = Person2Company.objects \
.filter(from_person=person, to_company=company,
is_employee=True, date_established__lt=date_established) \
.exclude(relationship_type__icontains=relationship_type)\
.order_by("-date_established").first()
if prev_position:
prev_position.date_finished = date_established
prev_position.date_finished_details = 0
if options["real_run"]:
prev_position.save()
tqdm.write("Updated previous position for SMIDACandidate ID: {}"
.format(candidate.id))
else:
date_established = candidate.dt_of_first_entry
# Get or create p2c
try:
p2c = Person2Company.objects.get(from_person=person,
to_company=company,
relationship_type__icontains=relationship_type,
is_employee=True)
updated = False
if (not p2c.date_finished and date_finished) or\
((p2c.date_finished and date_finished) and
(p2c.date_finished_details > 0 or date_finished.date() > p2c.date_finished)):
tqdm.write("Updated date_finished for P2C relation with id: {} Old: {}, New: {}"
.format(p2c.id,
p2c.date_finished,
date_finished))
p2c.date_finished = date_finished
p2c.date_finished_details = 0
updated = True
if (not p2c.date_established and date_established and date_established.date() < p2c.date_finished) or\
((p2c.date_established and date_established) and
(p2c.date_established_details > 0 or date_established.date() < p2c.date_established)):
tqdm.write("Updated date_established for P2C relation with id: {} Old: {}, New: {}"
.format(p2c.id,
p2c.date_established,
date_established))
p2c.date_established = date_established
p2c.date_established_details = 0
updated = True
p2c_links_updated += int(updated)
if options["real_run"]:
p2c.save()
except Person2Company.DoesNotExist:
p2c = Person2Company(from_person=person,
to_company=company,
relationship_type=relationship_type,
is_employee=True,
date_established=date_established,
date_finished=date_finished)
p2c_links_created += 1
tqdm.write("Created P2C relation: id: {} ({}) <=> id: {} ({}) EST. {}, FIN. {}"
.format(person.id or "N/A",
person_name,
company.id or "N/A",
company.name_uk,
p2c.date_established or "N/A",
p2c.date_finished or "N/A"))
if options["real_run"]:
p2c.save()
self.stdout.write("Finished import Persons and Person2Company relations.")
# endregion
self.stdout.write("New persons having multiple companies related")
self.new_persons_having_multiple_company_relations()
# region Create P2P connections
smida_candidates = SMIDACandidate.objects.filter(status="a",
smida_is_real_person=True) \
.order_by("dt_of_last_entry")
p2p_links_total = 0
for candidate in tqdm(smida_candidates.nocache().iterator(),
total=smida_candidates.count()):
person_name = candidate.smida_parsed_name.strip().lower()
heads_of_company = pep_heads.get(candidate.smida_edrpou) or []
from_person = self.persons_dict.get(person_name)
for head in heads_of_company:
to_person = self.persons_dict.get(head)
if from_person == to_person:
continue
try:
p2p = Person2Person.objects.get(from_person=from_person,
to_person=to_person,
from_relationship_type="ділові зв'язки",
to_relationship_type="ділові зв'язки")
p2p.date_confirmed = candidate.dt_of_last_entry\
or p2p.date_confirmed\
or datetime.now()
if options["real_run"]:
p2p.save()
tqdm.write("Updated P2P relation: id: {} ({}) <=> id: {} ({})// DC: {}"
.format(from_person.id or "N/A",
from_person.full_name,
to_person.id or "N/A",
to_person.full_name,
p2p.date_confirmed))
except Person2Person.DoesNotExist:
p2p = Person2Person(from_person=from_person,
to_person=to_person,
from_relationship_type="ділові зв'язки",
to_relationship_type="ділові зв'язки",
date_confirmed=candidate.dt_of_last_entry or datetime.now())
tqdm.write("Created P2P relation: id: {} ({}) <=> id: {} ({})"
.format(from_person.id or "N/A",
from_person.full_name,
to_person.id or "N/A",
to_person.full_name))
p2p_links_total += 1
if options["real_run"]:
p2p.save()
self.stdout.write("Finished import Person2Person relations.")
# endregion
self.stdout.write(
"Updated existing companies: {}.\n"
"Created new companies: {}.\n"
"Failed create companies: {}.\n"
"Created new persons: {}.\n"
"Matched existing resolved: {}.\n"
"Matched existing not resolved: {}.\n"
"Created P2C links: {}.\n"
"Updated P2C links: {}.\n"
"Created P2P links: {}."
.format(updated_companies_total,
created_companies_total,
failed_companies_total,
self.persons_stats["created_total"],
self.persons_stats["matched_resolved"],
self.persons_stats["matched_not_resolved"],
p2c_links_created,
p2c_links_updated,
p2p_links_total)
)