maciejpi/nordabiz
1
0
Fork 0
Code Issues Pull Requests Actions 1 Packages Projects Releases Wiki Activity
master
nordabiz/scripts/import_excel_members_2026_01_13.py
Maciej Pienczyn 9eae623d3e feat: Add source tracking to events + import scripts 
- Add source and source_note fields to NordaEvent model
- Create import_calendar_2026.py for NORDA calendar events
- Create import_excel_members_2026_01_13.py for new members
- Add .private/ to .gitignore (confidential materials)

Imported 26 events from Kalendarz Izby NORDA 2026 (Artur Wiertel)
Imported 31 new member companies from Excel

Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
2026-01-13 10:22:24 +01:00

638 lines
20 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

#!/usr/bin/env python3
"""
Import nowych członków NORDA z pliku Excel
Źródło: Aktualna lista kontaktow wraz z data przystapienia.xlsx
Data importu: 2026-01-13
Przesłane przez: Artur Wiertel
Ten skrypt:
1. Dodaje nowe źródło danych "Excel - Lista członków NORDA (2026-01-13)"
2. Importuje nowe firmy z oznaczeniem źródła
3. Aktualizuje istniejące firmy (dla starych 80 firm oznacza źródło)
"""
import os
import sys
import re
from datetime import datetime
# Dodaj ścieżkę do głównego katalogu aplikacji
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
# Konfiguracja bazy danych - używaj zmiennej środowiskowej
DATABASE_URL = os.getenv('DATABASE_URL', 'postgresql://nordabiz_app:CHANGE_ME@127.0.0.1:5432/nordabiz')
# Źródło danych
DATA_SOURCE_NAME = "Excel - Lista członków NORDA"
DATA_SOURCE_DESC = "Import z pliku Excel od Artura Wiertela (2026-01-13). Plik: Aktualna lista kontaktow wraz z data przystapienia.xlsx"
DATA_SOURCE_TYPE = "excel"
IMPORT_DATE = "2026-01-13"
# Nowe firmy do dodania (z Excela)
NEW_COMPANIES = [
{
"name": "Dom Dziecka Pro-Sport",
"legal_name": "Dom Dziecka",
"contact_person": "Marek Mroske",
"phone": "604178533",
"email": "marekm@prosport.wejher.pl",
"address_full": "ul. Sobieskiego 215, 84-200 Wejherowo",
"address_city": "Wejherowo",
"address_postal": "84-200",
"joined": "1997-10-20",
"category": "Other",
},
{
"name": "Event Investycje",
"legal_name": "Event Investycje Sp. z o.o.",
"contact_person": "Krzysztof Bombera",
"phone": "790272510",
"email": "krzysztof@bombera.pl",
"address_full": "ul. Kręckiego 3/5, 80-318 Gdańsk",
"address_city": "Gdańsk",
"address_postal": "80-318",
"joined": "2016-11-06",
"category": "Services",
},
{
"name": "Kancelaria Notarialna Henryk Mizak",
"legal_name": "Kancelaria Notarialna",
"contact_person": "Henryk Mizak",
"phone": None,
"email": "notariat306@wp.pl",
"address_full": "ul. Sobieskiego 306, 84-200 Wejherowo",
"address_city": "Wejherowo",
"address_postal": "84-200",
"joined": None,
"category": "Services",
},
{
"name": "Orlex Design",
"legal_name": "Orlex Design",
"contact_person": "Michał Gębarowski",
"phone": "601318227",
"email": "biuro@orlexbeton.pl",
"address_full": "ul. Mickiewicza 7, 84-241 Gościcino",
"address_city": "Gościcino",
"address_postal": "84-241",
"joined": "2018-06-26",
"category": "Construction",
},
{
"name": "PTHU Cezary Mazur",
"legal_name": "PTHU Cezary Mazur",
"contact_person": "Cezary Mazur",
"phone": "601652556",
"email": "pthumazur@gmail.com",
"address_full": "ul. Grunwaldzka 12, 84-230 Rumia",
"address_city": "Rumia",
"address_postal": "84-230",
"joined": "2023-04-05",
"category": "Trade",
},
{
"name": "Podróże i My",
"legal_name": "Podróże i My",
"contact_person": "Witold Gulcz",
"phone": "602697969",
"email": "w.gulcz@itakarumia.pl",
"address_full": "ul. Wierzbięcice 44A/21B, 61-583 Poznań",
"address_city": "Poznań",
"address_postal": "61-583",
"joined": "2018-11-06",
"category": "Services",
},
{
"name": "Kancelaria Radcy Prawnego Joanna Ostrowska",
"legal_name": "Radca Prawny Joanna Ostrowska",
"contact_person": "Joanna Ostrowska",
"phone": None,
"email": None,
"address_full": None,
"address_city": "Wejherowo",
"address_postal": None,
"joined": None,
"category": "Services",
},
{
"name": "PZU TFI",
"legal_name": "PZU SA Towarzystwo Funduszy Inwestycyjnych",
"contact_person": "Łukasz Antoniak",
"phone": "887875775",
"email": "lantoniak@pzu.pl",
"address_full": "Rondo I. Daszyński 4, 00-843 Warszawa",
"address_city": "Warszawa",
"address_postal": "00-843",
"joined": "2019-02-05",
"category": "Services",
},
{
"name": "Rozsądni Bracia",
"legal_name": "Rozsądni Bracia Sp. z o.o.",
"contact_person": "Robert Kiereś, Piotr Kiereś, Michał Kiereś",
"phone": "737856865",
"email": "kontakt@rozsandnibracia.pl",
"address_full": "ul. Wałowa 30/8, 84-200 Wejherowo",
"address_city": "Wejherowo",
"address_postal": "84-200",
"joined": "2024-05-08",
"category": "Services",
},
{
"name": "TERMO",
"legal_name": "TERMO Sp. z o.o.",
"contact_person": "Bartłomiej Komkowski",
"phone": "503177099",
"email": "biuro@termocenter.pl",
"address_full": "ul. Tulipanowa 14, 84-252 Góra",
"address_city": "Góra",
"address_postal": "84-252",
"joined": "2025-04-01",
"category": "Construction",
},
{
"name": "Thai Union Poland",
"legal_name": "Thai Union",
"contact_person": "Piotr Sadowski",
"phone": None,
"email": None,
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": None,
"category": "Production",
},
{
"name": "Usługi Ogólnobudowlane Sławomir Grula",
"legal_name": "Usługi Ogólnobudowlane",
"contact_person": "Sławomir Grula",
"phone": "502919634",
"email": "slawek1055@onet.pl",
"address_full": "ul. Wiejska 6, 84-218 Łęczyce",
"address_city": "Łęczyce",
"address_postal": "84-218",
"joined": None,
"category": "Construction",
},
{
"name": "Wodmel",
"legal_name": "Wodmel Sp. J.",
"contact_person": "Tomasz Potrykus, Jan Derra",
"phone": None,
"email": "wodmel@op.pl",
"address_full": "ul. Przemysłowa 31A, 84-200 Wejherowo",
"address_city": "Wejherowo",
"address_postal": "84-200",
"joined": "1997-10-14",
"category": "Construction",
},
{
"name": "IT Space",
"legal_name": "IT Space B & Łaga Bartosz",
"contact_person": "Bartosz Łaga",
"phone": None,
"email": "vwmania6@gmail.com",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-08-01",
"category": "IT",
},
{
"name": "Aertom",
"legal_name": "Aertom",
"contact_person": "Tomasz Dzienisz",
"phone": None,
"email": "dzienisztomasz@gmail.com",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-08-01",
"category": "Other",
},
{
"name": "Wikęd",
"legal_name": "Wikęd",
"contact_person": "Grzegorz Wiśniewski",
"phone": None,
"email": "gwiked@wp.pl",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-08-01",
"category": "Production",
},
{
"name": "Unimot",
"legal_name": "Unimot",
"contact_person": "Łukasz Pawłowski, Tomasz Barejka",
"phone": "509109999",
"email": "Tomasz.barejka@unimot-eig.pl",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-11-01",
"category": "Trade",
},
{
"name": "Omega Energy",
"legal_name": "Omega",
"contact_person": "Piotr Karbowski",
"phone": "601679494",
"email": "p.karbowski@omega-energy.eu",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-11-01",
"category": "Production",
},
{
"name": "Nowatel",
"legal_name": "Nowatel",
"contact_person": "Łukasz Dominik, Stanisław Czech",
"phone": "607623100",
"email": "biuro@nowatel.com",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-11-01",
"category": "IT",
},
{
"name": "Informatyk1",
"legal_name": "Informatyk1",
"contact_person": "Mateusz Kurpet",
"phone": "602354351",
"email": "mateusz@informatyk1.pl",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-11-01",
"category": "IT",
},
{
"name": "Piotrex",
"legal_name": "Piotrex",
"contact_person": "Piotr Wieczorek",
"phone": "518842122",
"email": "biuro@piotrex.info",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-12-01",
"category": "Other",
},
{
"name": "Kancelaria Ostrowski i Wspólnicy",
"legal_name": "Kancelaria Ostrowski i wspólnicy",
"contact_person": "Paweł Cioban",
"phone": "727591189",
"email": "p.cioban@ostrowski-legal.net",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-12-01",
"category": "Services",
},
{
"name": "Family Art",
"legal_name": "Family Art",
"contact_person": "Artur Czaja",
"phone": "531972354",
"email": "artczaj@gmail.com",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-12-01",
"category": "Other",
},
{
"name": "Renk Hurtownie",
"legal_name": "Renk – Pomorskie Hurtownie Centrum Rolno-Spożywcze",
"contact_person": "Marcin Bulczak",
"phone": "504228997",
"email": "sekretariat@renk.pl",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-12-01",
"category": "Trade",
},
{
"name": "Elzit",
"legal_name": "Elzit",
"contact_person": "Bartosz Lang",
"phone": "603089984",
"email": "b.lang@elzit.pl",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-11-01",
"category": "IT",
},
{
"name": "Your Welcome",
"legal_name": "Your Welcome",
"contact_person": "Agnieszka Kulinkowska",
"phone": "519345844",
"email": "agnieszka@yourewelcome.pl",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-11-01",
"category": "Services",
},
{
"name": "3W",
"legal_name": "3W",
"contact_person": "Maciej Kasyna",
"phone": "664019585",
"email": "maciej.kasyna@3wdb.pl",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-11-01",
"category": "IT",
},
{
"name": "Wakat",
"legal_name": "Wakat",
"contact_person": "Dariusz Krasiński",
"phone": "600447635",
"email": "darek.krasinski@wp.pl",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-11-01",
"category": "Other",
},
{
"name": "BIS Maszyny",
"legal_name": "BIS Maszyny",
"contact_person": "Jarosław Rohraff",
"phone": "602228400",
"email": "jarek@bis-bau.pl",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-11-01",
"category": "Trade",
},
{
"name": "Elgreen EM",
"legal_name": "Elgreen EM P.S.A.",
"contact_person": "Krzysztof Zanaboni",
"phone": "514254267",
"email": "kz@elgreen.pl",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2025-10-01",
"category": "Production",
},
{
"name": "Iwona Spaleniak Coaching",
"legal_name": "Iwona Spaleniak Coach 4 you",
"contact_person": "Iwona Spaleniak",
"phone": "608336529",
"email": "kontakt@iwonaspaleniak.pl",
"address_full": None,
"address_city": None,
"address_postal": None,
"joined": "2026-01-01",
"category": "Services",
},
]
# Kategorie (mapowanie nazwa -> id)
CATEGORY_MAP = {
"IT": 1,
"Construction": 2,
"Services": 3,
"Production": 4,
"Trade": 5,
"Other": 6,
}
def slugify(text):
"""Generuje slug z nazwy firmy"""
text = text.lower()
# Zamień polskie znaki
replacements = {
'ą': 'a', 'ć': 'c', 'ę': 'e', 'ł': 'l', 'ń': 'n',
'ó': 'o', 'ś': 's', 'ź': 'z', 'ż': 'z'
}
for pl, en in replacements.items():
text = text.replace(pl, en)
# Usuń znaki specjalne, zostaw tylko litery, cyfry i spacje
text = re.sub(r'[^a-z0-9\s-]', '', text)
# Zamień spacje na myślniki
text = re.sub(r'\s+', '-', text.strip())
# Usuń podwójne myślniki
text = re.sub(r'-+', '-', text)
return text
def clean_phone(phone):
"""Czyści numer telefonu"""
if not phone:
return None
# Usuń wszystko oprócz cyfr i spacji
phone = re.sub(r'[^\d\s]', '', str(phone))
phone = phone.strip()
return phone if phone else None
def clean_email(email):
"""Czyści email - bierze pierwszy jeśli jest kilka"""
if not email:
return None
email = str(email).strip()
# Jeśli jest kilka emaili, weź pierwszy
if ';' in email:
email = email.split(';')[0].strip()
if ',' in email:
email = email.split(',')[0].strip()
# Wyciągnij email z formatu "Nazwa <email>"
match = re.search(r'<([^>]+)>', email)
if match:
email = match.group(1)
# Sprawdź czy wygląda jak email
if '@' not in email:
return None
return email.lower().strip()
def main():
print("=" * 60)
print("IMPORT CZŁONKÓW NORDA Z EXCELA")
print(f"Data: {IMPORT_DATE}")
print(f"Źródło: {DATA_SOURCE_NAME}")
print("=" * 60)
# Połącz z bazą
engine = create_engine(DATABASE_URL)
Session = sessionmaker(bind=engine)
session = Session()
try:
# 1. Dodaj nowe źródło danych jeśli nie istnieje
print("\n[1/4] Dodawanie źródła danych...")
result = session.execute(text(
"SELECT id FROM data_sources WHERE name = :name"
), {"name": DATA_SOURCE_NAME})
existing = result.fetchone()
if existing:
source_id = existing[0]
print(f" Źródło już istnieje (ID: {source_id})")
else:
session.execute(text("""
INSERT INTO data_sources (name, type, description, reliability_score, is_active)
VALUES (:name, :type, :desc, 4, true)
"""), {
"name": DATA_SOURCE_NAME,
"type": DATA_SOURCE_TYPE,
"desc": DATA_SOURCE_DESC
})
result = session.execute(text(
"SELECT id FROM data_sources WHERE name = :name"
), {"name": DATA_SOURCE_NAME})
source_id = result.fetchone()[0]
print(f" Dodano źródło (ID: {source_id})")
# 2. Oznacz istniejące firmy źródłem
print("\n[2/4] Oznaczanie istniejących firm źródłem importu...")
# Najpierw sprawdź ile firm nie ma oznaczonego źródła
result = session.execute(text(
"SELECT COUNT(*) FROM companies WHERE data_source IS NULL OR data_source = ''"
))
count_no_source = result.fetchone()[0]
if count_no_source > 0:
session.execute(text("""
UPDATE companies
SET data_source = 'norda-biznes.info (pierwotny import)'
WHERE data_source IS NULL OR data_source = ''
"""))
print(f" Oznaczono {count_no_source} firm jako 'norda-biznes.info (pierwotny import)'")
else:
print(" Wszystkie firmy mają już oznaczone źródło")
# 3. Importuj nowe firmy
print(f"\n[3/4] Importowanie {len(NEW_COMPANIES)} nowych firm...")
imported = 0
skipped = 0
for company in NEW_COMPANIES:
# Sprawdź czy firma już istnieje
slug = slugify(company["name"])
result = session.execute(text(
"SELECT id FROM companies WHERE slug = :slug"
), {"slug": slug})
existing = result.fetchone()
if existing:
print(f" ⏭ {company['name']} - już istnieje (slug: {slug})")
skipped += 1
continue
# Pobierz ID kategorii
category_id = CATEGORY_MAP.get(company.get("category"), 6) # 6 = Other
# Przygotuj dane
email = clean_email(company.get("email"))
phone = clean_phone(company.get("phone"))
# Wstaw firmę
session.execute(text("""
INSERT INTO companies (
name, legal_name, slug, category_id,
email, phone,
address_full, address_city, address_postal,
data_source, data_quality, status,
created_at
) VALUES (
:name, :legal_name, :slug, :category_id,
:email, :phone,
:address_full, :address_city, :address_postal,
:data_source, 'basic', 'active',
:created_at
)
"""), {
"name": company["name"],
"legal_name": company.get("legal_name"),
"slug": slug,
"category_id": category_id,
"email": email,
"phone": phone,
"address_full": company.get("address_full"),
"address_city": company.get("address_city"),
"address_postal": company.get("address_postal"),
"data_source": f"excel_norda_{IMPORT_DATE}",
"created_at": datetime.now(),
})
# Pobierz ID nowej firmy
result = session.execute(text(
"SELECT id FROM companies WHERE slug = :slug"
), {"slug": slug})
company_id = result.fetchone()[0]
# Dodaj wpis do company_data_sources
session.execute(text("""
INSERT INTO company_data_sources (company_id, source_id, collected_at, fields_collected)
VALUES (:company_id, :source_id, :collected_at, :fields)
"""), {
"company_id": company_id,
"source_id": source_id,
"collected_at": datetime.now(),
"fields": ["name", "email", "phone", "address"],
})
# Dodaj osobę kontaktową i datę przystąpienia do Nordy
updates = []
if company.get("contact_person"):
updates.append(f"Osoba kontaktowa: {company['contact_person']}")
if company.get("joined"):
updates.append(f"Członek NORDA od: {company['joined']}")
if updates:
session.execute(text("""
UPDATE companies
SET description_short = :desc,
founding_history = :history
WHERE id = :company_id
"""), {
"company_id": company_id,
"desc": updates[0] if len(updates) == 1 else None,
"history": f"Członek NORDA od: {company.get('joined', 'brak danych')}. " + (f"Kontakt: {company.get('contact_person')}" if company.get('contact_person') else ""),
})
print(f" ✓ {company['name']} (ID: {company_id})")
imported += 1
# 4. Podsumowanie
print(f"\n[4/4] Podsumowanie:")
print(f" Zaimportowano: {imported} firm")
print(f" Pominięto (już istnieją): {skipped} firm")
# Zatwierdź transakcję
session.commit()
print("\n✅ Import zakończony pomyślnie!")
except Exception as e:
session.rollback()
print(f"\n❌ Błąd: {e}")
raise
finally:
session.close()
if __name__ == "__main__":
main()
Reference in New Issue View Git Blame Copy Permalink