225 lines
11 KiB
Python
225 lines
11 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""Перенос контента: филиалы, FAQ, отзывы, документы, партнёры, посты, вакансии, страницы."""
|
||
import sqlread, io, os, re, html, zipfile
|
||
|
||
P = 'db.sql'
|
||
ZIP = r'C:\Users\naym4\Downloads\public_html.zip'
|
||
MEDIA_DIR = r'D:\dana\media'
|
||
OUT = io.open('seed_content.sql', 'w', encoding='utf-8')
|
||
MIME = {'.jpg': 'image/jpeg', '.jpeg': 'image/jpeg', '.png': 'image/png',
|
||
'.gif': 'image/gif', '.svg': 'image/svg+xml', '.webp': 'image/webp', '.pdf': 'application/pdf'}
|
||
|
||
def esc(s):
|
||
if s is None:
|
||
return "NULL"
|
||
# В PostgreSQL со standard_conforming_strings обратный слэш — обычный символ,
|
||
# удваивать его нельзя: имена приезжали в базу с лишним слэшем.
|
||
return "'" + str(s).replace("'", "''") + "'"
|
||
|
||
def clean(s):
|
||
# Парсер дампа отдаёт SQL NULL строкой 'NULL'. Без этой проверки clean()
|
||
# возвращал непустую строку "NULL", из-за чего конструкции вида
|
||
# `clean(DETAIL_TEXT) or clean(PREVIEW_TEXT)` не доходили до запасного
|
||
# поля — так у отзывов потерялся текст (он лежит в PREVIEW_TEXT).
|
||
if not s or s == 'NULL':
|
||
return ''
|
||
s = re.sub(r'(?is)<[^>]+>', '', s)
|
||
return html.unescape(s).strip()
|
||
|
||
files = {r['ID']: r for r in sqlread.rows(P, 'b_file')}
|
||
zf = zipfile.ZipFile(ZIP); names = set(zf.namelist()); extracted = {}
|
||
|
||
def ensure(fid):
|
||
if not fid or fid in ('', 'NULL'):
|
||
return None
|
||
if fid in extracted:
|
||
return extracted[fid]
|
||
f = files.get(fid)
|
||
if not f:
|
||
return None
|
||
sub = (f.get('SUBDIR') or '').strip(); nm = (f.get('FILE_NAME') or '').strip()
|
||
arc = 'public_html/upload/%s/%s' % (sub, nm)
|
||
if not sub or not nm or arc not in names:
|
||
return None
|
||
dd = os.path.join(MEDIA_DIR, sub.replace('/', os.sep)); os.makedirs(dd, exist_ok=True)
|
||
with zf.open(arc) as s, open(os.path.join(dd, nm), 'wb') as d:
|
||
d.write(s.read())
|
||
web = '/media/%s/%s' % (sub, nm); extracted[fid] = web
|
||
return web
|
||
|
||
def media_sql(fid, web, alt=''):
|
||
f = files[fid]; ext = os.path.splitext(web)[1].lower()
|
||
OUT.write("INSERT INTO media (path,mime,size,width,height,alt) VALUES (%s,%s,%s,%s,%s,%s) "
|
||
"ON CONFLICT (path) DO NOTHING;\n" % (
|
||
esc(web), esc(MIME.get(ext, 'application/octet-stream')),
|
||
f.get('FILE_SIZE') or '0', f.get('WIDTH') or '0', f.get('HEIGHT') or '0', esc(alt)))
|
||
|
||
# элементы и их свойства (нужные инфоблоки)
|
||
NEED = {'23', '17', '13', '25', '14', '28', '27', '26', '15', '19', '21', '18'}
|
||
elems = [r for r in sqlread.rows(P, 'b_iblock_element') if r['IBLOCK_ID'] in NEED]
|
||
props = {}
|
||
for r in sqlread.rows(P, 'b_iblock_element_property'):
|
||
props.setdefault((r.get('IBLOCK_ELEMENT_ID'), r.get('IBLOCK_PROPERTY_ID')), []).append(r.get('VALUE'))
|
||
|
||
def p1(eid, pid):
|
||
v = props.get((eid, pid))
|
||
return v[0] if v else None
|
||
|
||
def pN(eid, pid):
|
||
return [x for x in props.get((eid, pid), []) if x not in (None, '', 'NULL')]
|
||
|
||
def blocks_of(e):
|
||
d = e.get('DETAIL_TEXT') or ''
|
||
if not d.strip():
|
||
return "'[]'::jsonb"
|
||
return "jsonb_build_array(jsonb_build_object('type','html','html'," + esc(d) + "))"
|
||
|
||
OUT.write('BEGIN;\n\n')
|
||
|
||
# ---------- ФИЛИАЛЫ (iblock 19, элемент с объединённым адресом) ----------
|
||
OUT.write('-- Филиалы\n')
|
||
e = next((x for x in elems if x['IBLOCK_ID'] == '19' and x.get('ACTIVE') == 'Y'), None)
|
||
if e:
|
||
addr = clean(p1(e['ID'], '140'))
|
||
phone = clean(p1(e['ID'], '142'))
|
||
sched_raw = p1(e['ID'], '144') or ''
|
||
m = re.search(r'"TEXT";s:\d+:"(.*?)";', sched_raw)
|
||
hours = clean(m.group(1)) if m else 'ПН – ВС: 08:00 – 20:00'
|
||
coord = (p1(e['ID'], '146') or '').split(',')
|
||
lat = coord[0].strip() if len(coord) == 2 else None
|
||
lon = coord[1].strip() if len(coord) == 2 else None
|
||
parts = [a.strip() for a in re.split(r'[;]', addr) if a.strip()] or [addr]
|
||
for i, a in enumerate(parts):
|
||
slug = 'lyahova-%s' % (re.search(r'(\d+)', a).group(1) if re.search(r'(\d+)', a) else i + 1)
|
||
la = lat if i == 0 else 'NULL'
|
||
lo = lon if i == 0 else 'NULL'
|
||
OUT.write("INSERT INTO branches (slug,name,address,phones,work_hours,lat,lon,sort) "
|
||
"VALUES (%s,%s,%s,jsonb_build_array(%s),jsonb_build_array(%s),%s,%s,%s) "
|
||
"ON CONFLICT (slug) DO NOTHING;\n" % (
|
||
esc(slug), esc('г. Махачкала, ' + a), esc('г. Махачкала, ' + a),
|
||
esc(phone), esc(hours), la or 'NULL', lo or 'NULL', i + 1))
|
||
OUT.write('\n')
|
||
|
||
# ---------- FAQ (iblock 15) ----------
|
||
OUT.write('-- FAQ\n')
|
||
for e in elems:
|
||
if e['IBLOCK_ID'] != '15' or e.get('ACTIVE') != 'Y':
|
||
continue
|
||
q = clean(e.get('NAME')); a = clean(e.get('PREVIEW_TEXT')) or clean(e.get('DETAIL_TEXT'))
|
||
if not q:
|
||
continue
|
||
OUT.write("INSERT INTO faq (question,answer,sort,published,legacy_id) VALUES (%s,%s,%s,true,%s);\n" % (
|
||
esc(q), esc(a), e.get('SORT') or '100', e['ID']))
|
||
OUT.write('\n')
|
||
|
||
# ---------- ОТЗЫВЫ (iblock 23) ----------
|
||
OUT.write('-- Отзывы (ACTIVE=Y -> published, иначе pending)\n')
|
||
RATING = {'143': 1, '144': 2, '145': 3, '146': 4, '147': 5}
|
||
for e in elems:
|
||
if e['IBLOCK_ID'] != '23':
|
||
continue
|
||
name = clean(e.get('NAME')) or 'Аноним'
|
||
text = clean(e.get('DETAIL_TEXT')) or clean(e.get('PREVIEW_TEXT'))
|
||
if not text:
|
||
continue
|
||
rating = RATING.get(p1(e['ID'], '172'))
|
||
status = 'published' if e.get('ACTIVE') == 'Y' else 'pending'
|
||
photo = ensure(e.get('PREVIEW_PICTURE') or e.get('DETAIL_PICTURE'))
|
||
photo_sql = 'NULL'
|
||
if photo:
|
||
media_sql(e.get('PREVIEW_PICTURE') or e.get('DETAIL_PICTURE'), photo, name)
|
||
photo_sql = "(SELECT id FROM media WHERE path=%s)" % esc(photo)
|
||
doc = pN(e['ID'], '173') or pN(e['ID'], '170')
|
||
doc_sql = "(SELECT id FROM doctors WHERE legacy_id=%s)" % doc[0] if doc else 'NULL'
|
||
OUT.write("INSERT INTO reviews (author_name,author_photo_id,rating,text,doctor_id,source,status,legacy_id) "
|
||
"VALUES (%s,%s,%s,%s,%s,'manual',%s,%s);\n" % (
|
||
esc(name), photo_sql, rating if rating else 'NULL', esc(text), doc_sql, esc(status), e['ID']))
|
||
OUT.write('\n')
|
||
|
||
# ---------- ДОКУМЕНТЫ: лицензии(17)+надзор(13) ----------
|
||
OUT.write('-- Документы (лицензии, надзорные органы)\n')
|
||
for e in elems:
|
||
if e['IBLOCK_ID'] not in ('17', '13'):
|
||
continue
|
||
kind = 'license' if e['IBLOCK_ID'] == '17' else 'supervisor'
|
||
title = clean(e.get('NAME'))
|
||
body = clean(e.get('DETAIL_TEXT')) or clean(e.get('PREVIEW_TEXT'))
|
||
scans = pN(e['ID'], '136') if e['IBLOCK_ID'] == '17' else pN(e['ID'], '117')
|
||
prev = ensure(e.get('DETAIL_PICTURE') or e.get('PREVIEW_PICTURE') or (scans[0] if scans else None))
|
||
prev_sql = 'NULL'
|
||
if prev:
|
||
fid = e.get('DETAIL_PICTURE') or e.get('PREVIEW_PICTURE') or scans[0]
|
||
media_sql(fid, prev, title)
|
||
prev_sql = "(SELECT id FROM media WHERE path=%s)" % esc(prev)
|
||
OUT.write("INSERT INTO documents (kind,title,body,preview_id,sort,published,legacy_id) "
|
||
"VALUES (%s,%s,%s,%s,%s,true,%s);\n" % (
|
||
esc(kind), esc(title), esc(body), prev_sql, e.get('SORT') or '100', e['ID']))
|
||
OUT.write('\n')
|
||
|
||
# ---------- ПАРТНЁРЫ (iblock 25) ----------
|
||
OUT.write('-- Партнёры\n')
|
||
for e in elems:
|
||
if e['IBLOCK_ID'] != '25' or e.get('ACTIVE') != 'Y':
|
||
continue
|
||
logo = ensure(e.get('PREVIEW_PICTURE') or e.get('DETAIL_PICTURE') or (pN(e['ID'], '726')[:1] or [None])[0])
|
||
logo_sql = 'NULL'
|
||
if logo:
|
||
fid = e.get('PREVIEW_PICTURE') or e.get('DETAIL_PICTURE') or pN(e['ID'], '726')[0]
|
||
media_sql(fid, logo, clean(e.get('NAME')))
|
||
logo_sql = "(SELECT id FROM media WHERE path=%s)" % esc(logo)
|
||
OUT.write("INSERT INTO partners (name,url,logo_id,sort,published,legacy_id) VALUES (%s,%s,%s,%s,true,%s);\n" % (
|
||
esc(clean(e.get('NAME'))), esc(clean(p1(e['ID'], '210'))), logo_sql, e.get('SORT') or '100', e['ID']))
|
||
OUT.write('\n')
|
||
|
||
# ---------- ПОСТЫ: новости(27)+статьи(26)+акции(28) ----------
|
||
OUT.write('-- Новости, статьи, акции\n')
|
||
for e in elems:
|
||
if e['IBLOCK_ID'] == '27':
|
||
kind = 'news'
|
||
elif e['IBLOCK_ID'] == '26':
|
||
kind = 'article'
|
||
elif e['IBLOCK_ID'] == '28':
|
||
kind = 'promo'
|
||
else:
|
||
continue
|
||
if e.get('ACTIVE') != 'Y':
|
||
continue
|
||
slug = e.get('CODE') or (kind + '-' + e['ID'])
|
||
img = ensure(e.get('PREVIEW_PICTURE') or e.get('DETAIL_PICTURE'))
|
||
img_sql = 'NULL'
|
||
if img:
|
||
media_sql(e.get('PREVIEW_PICTURE') or e.get('DETAIL_PICTURE'), img, clean(e.get('NAME')))
|
||
img_sql = "(SELECT id FROM media WHERE path=%s)" % esc(img)
|
||
af = e.get('ACTIVE_FROM')
|
||
starts = esc(af[:10]) if kind == 'promo' and af and af != 'NULL' else 'NULL'
|
||
OUT.write("INSERT INTO posts (kind,slug,title,lead,blocks,image_id,starts_at,published,legacy_id) "
|
||
"VALUES (%s,%s,%s,%s,%s,%s,%s,true,%s) ON CONFLICT (kind,slug) WHERE deleted_at IS NULL DO NOTHING;\n" % (
|
||
esc(kind), esc(slug), esc(clean(e.get('NAME'))), esc(clean(e.get('PREVIEW_TEXT'))),
|
||
blocks_of(e), img_sql, starts, e['ID']))
|
||
OUT.write('\n')
|
||
|
||
# ---------- ВАКАНСИИ (iblock 14) ----------
|
||
OUT.write('-- Вакансии\n')
|
||
for e in elems:
|
||
if e['IBLOCK_ID'] != '14' or e.get('ACTIVE') != 'Y':
|
||
continue
|
||
slug = e.get('CODE') or ('vac-' + e['ID'])
|
||
OUT.write("INSERT INTO vacancies (slug,title,description,salary,sort,published,legacy_id) "
|
||
"VALUES (%s,%s,%s,%s,%s,true,%s) ON CONFLICT (slug) DO NOTHING;\n" % (
|
||
esc(slug), esc(clean(e.get('NAME'))), esc(clean(e.get('DETAIL_TEXT')) or clean(e.get('PREVIEW_TEXT'))),
|
||
esc(clean(p1(e['ID'], '122'))), e.get('SORT') or '100', e['ID']))
|
||
OUT.write('\n')
|
||
|
||
# ---------- СТРАНИЦЫ: о клинике(21)+история(18) ----------
|
||
OUT.write('-- Страницы (о клинике, история)\n')
|
||
page_map = {'21': ('about', 'О клинике'), '18': ('history', 'История')}
|
||
for e in elems:
|
||
if e['IBLOCK_ID'] not in page_map:
|
||
continue
|
||
slug, title = page_map[e['IBLOCK_ID']]
|
||
OUT.write("INSERT INTO pages (slug,title,blocks) VALUES (%s,%s,%s) ON CONFLICT (slug) DO NOTHING;\n" % (
|
||
esc(slug), esc(title), blocks_of(e)))
|
||
OUT.write('\nCOMMIT;\n')
|
||
OUT.close()
|
||
print('extracted media:', len(extracted))
|