DB_JOB/_스크립트/_dedup_all.py

# -*- coding: utf-8 -*-
"""부모-자식 URL 중복 제거 — 전 깊이 일반화 (매뉴얼 1-5 확장).

기존 phase1 dedup은 F→G 한 깊이만 처리. 서산시처럼 중분류(E)가 클릭 랜딩이고
그 URL이 첫 소분류(F)와 같으면(E→F 중복) 안 지워졌다. 본 도구는 깊이 무관:
  부모행의 가장 깊은 카테고리 컬럼이 lc 이고, 직후 자식행이
    · D~lc 까지 값이 모두 동일, · lc+1 컬럼이 채워짐, · K(URL)가 부모와 '정확히' 동일
  이면 부모행을 삭제(자식이 카테고리 텍스트를 병합으로 승계). URL이 다르면 보존.

평탄화→재병합(D/E/F)→순번/행높이/하이퍼링크 재설정은 _tab_expand 와 동일 로직.
모든 컬럼(B~AA, L~Q 등 Phase2~4 데이터 포함) 보존.

사용:
  python -X utf8 _dedup_all.py dry [기관...]     # 제거대상 집계만(읽기전용)
  python -X utf8 _dedup_all.py run [기관...]      # 백업(*_backup_dedup전.xlsx) 후 제거
"""
import os
import sys
import shutil
import warnings
from copy import copy

import openpyxl
from openpyxl.styles import Alignment, Font

warnings.filterwarnings('ignore')

MAXCOL = 27
CAT_COLS = [4, 5, 6, 7, 8, 9, 10]  # D E F G H I J
HEADER_MERGES = {'B1:R1', 'S1:W1', 'Y1:AA1'}

HERE = os.path.dirname(os.path.abspath(__file__))
MAP = os.path.join(os.path.dirname(HERE), '작업파일', '광역_사이트맵')

# 전 42개 시·군 (광역, idx, 기관)
SITES = (
    [('충청남도', i, n) for i, n in [
        (1, '계룡시'), (2, '공주시'), (3, '금산군'), (4, '논산시'), (5, '당진시'),
        (6, '보령시'), (7, '부여군'), (8, '서산시'), (9, '서천군'), (10, '아산시'),
        (11, '예산군'), (12, '천안시'), (13, '청양군'), (14, '태안군'), (15, '홍성군')]]
    + [('충청북도', i, n) for i, n in [
        (1, '괴산군'), (2, '단양군'), (3, '보은군'), (4, '영동군'), (5, '옥천군'),
        (6, '음성군'), (7, '제천시'), (8, '증평군'), (9, '진천군'), (10, '청주시'), (11, '충주시')]]
    + [('전북특별자치도', i, n) for i, n in [
        (1, '고창군'), (2, '군산시'), (3, '김제시'), (4, '남원시'), (5, '무주군'),
        (6, '부안군'), (7, '순창군'), (8, '완주군'), (9, '익산시'), (10, '임실군'),
        (11, '장수군'), (12, '전주시'), (13, '정읍시'), (14, '진안군')]]
    + [('제주특별자치도', i, n) for i, n in [(1, '서귀포시'), (2, '제주시')]]
)


def xpath(prov, idx, name):
    return os.path.join(MAP, prov, f'{idx}.{name}', f'{prov}_{name}.xlsx')


def load_flat(ws):
    for mr in list(ws.merged_cells.ranges):
        s = str(mr)
        if s in HEADER_MERGES:
            continue
        top = ws.cell(mr.min_row, mr.min_col).value
        ws.unmerge_cells(s)
        for rr in range(mr.min_row, mr.max_row + 1):
            for cc in range(mr.min_col, mr.max_col + 1):
                if ws.cell(rr, cc).value in (None, ''):
                    ws.cell(rr, cc).value = top
    rows = []
    for r in range(3, ws.max_row + 1):
        if all(ws.cell(r, c).value in (None, '') for c in range(2, MAXCOL + 1)):
            continue
        vals = {c: ws.cell(r, c).value for c in range(1, MAXCOL + 1)}
        styles = {}
        for c in range(1, MAXCOL + 1):
            sc = ws.cell(r, c)
            styles[c] = (copy(sc.font), copy(sc.fill), copy(sc.border),
                         copy(sc.alignment), sc.number_format, copy(sc.protection))
        hl = ws.cell(r, 11).hyperlink
        rows.append({'src': r, 'vals': vals, 'styles': styles,
                     'hyperlink': hl.target if hl else None})
    return rows


def leaf_depth(vals):
    deep = 4
    for c in CAT_COLS:
        if vals.get(c) not in (None, ''):
            deep = c
    return deep


def dedup(rows):
    """전 깊이 부모-자식 URL 중복 제거. 반환: (남은행, 제거목록)."""
    out, removed = [], []
    i = 0
    while i < len(rows):
        v = rows[i]['vals']
        if i + 1 < len(rows):
            nv = rows[i + 1]['vals']
            lc = leaf_depth(v)
            ku, kc = v.get(11), nv.get(11)
            if lc < 10 and isinstance(ku, str) and ku.startswith('http') and ku == kc:
                same_upper = all((v.get(c) or '') == (nv.get(c) or '')
                                 for c in CAT_COLS if c <= lc)
                child_next = nv.get(lc + 1) not in (None, '')
                if same_upper and child_next:
                    removed.append({'src': rows[i]['src'], 'lc': lc,
                                    'E': v.get(5), 'F_child': nv.get(lc + 1),
                                    'url': ku})
                    i += 1
                    continue
        out.append(rows[i])
        i += 1
    return out, removed


def write_back(ws, out_rows):
    """남은 행으로 데이터영역 재작성 + D/E/F 재병합 + 순번/행높이/하이퍼링크."""
    for r in range(3, ws.max_row + 1):
        for c in range(1, MAXCOL + 1):
            ws.cell(r, c).value = None
            ws.cell(r, c).hyperlink = None
    START = 3
    for idx, orow in enumerate(out_rows):
        r = START + idx
        sty = orow['styles']
        for c in range(1, MAXCOL + 1):
            cell = ws.cell(r, c)
            f, fl, bd, al, nf, pr = sty[c]
            cell.font = copy(f); cell.fill = copy(fl); cell.border = copy(bd)
            cell.alignment = copy(al); cell.number_format = nf; cell.protection = copy(pr)
        v = orow['vals']
        for c in range(1, MAXCOL + 1):
            ws.cell(r, c).value = v.get(c)
        ws.cell(r, 2).value = idx + 1
    END = START + len(out_rows) - 1
    center = Alignment(horizontal='center', vertical='center', wrap_text=True)
    left = Alignment(horizontal='left', vertical='center', wrap_text=False)

    def merge_runs(letter, col, group_cols=()):
        cur = ws.cell(START, col).value
        grp = tuple(ws.cell(START, g).value for g in group_cols)
        run = START
        runs = []
        for r in range(START + 1, END + 1):
            val = ws.cell(r, col).value
            g = tuple(ws.cell(r, gg).value for gg in group_cols)
            if val == cur and g == grp:
                continue
            if cur not in (None, '') and r - 1 > run:
                runs.append((run, r - 1))
            cur, grp, run = val, g, r
        if cur not in (None, '') and END > run:
            runs.append((run, END))
        for s, e in runs:
            ws.merge_cells(f'{letter}{s}:{letter}{e}')
            ws.cell(s, col).alignment = center

    merge_runs('F', 6, (4, 5))
    merge_runs('E', 5, (4,))
    merge_runs('D', 4)
    for r in range(START, END + 1):
        for c in (4, 5, 6, 7):
            if ws.cell(r, c).value is not None:
                ws.cell(r, c).alignment = center
    for r in range(1, END + 1):
        ws.row_dimensions[r].height = 15
    for r in range(START, END + 1):
        cell = ws.cell(r, 11)
        u = cell.value
        if isinstance(u, str) and u.startswith(('http://', 'https://')):
            cell.hyperlink = u
            old = cell.font
            cell.font = Font(name=old.name or '맑은 고딕', size=old.size or 11,
                             bold=old.bold, italic=old.italic,
                             color='0000FF', underline='single')
            cell.alignment = left


def main():
    mode = sys.argv[1] if len(sys.argv) > 1 else 'dry'
    only = set(a for a in sys.argv[2:] if not a.startswith('--'))
    sites = [s for s in SITES if not only or s[2] in only]
    grand = 0
    print(f'{"기관":<8}{"현재행":>6}{"제거":>5}{"→남음":>7}   유형(중분류 예시)')
    print('-' * 80)
    for prov, idx, name in sites:
        xp = xpath(prov, idx, name)
        if not os.path.exists(xp):
            print(f'{name:<8} 엑셀 없음')
            continue
        wb = openpyxl.load_workbook(xp)
        ws = wb.active
        rows = load_flat(ws)
        out, removed = dedup(rows)
        grand += len(removed)
        ex = ''
        if removed:
            sample = removed[0]
            ex = f"E={sample['E']} = {sample['F_child']}"
        print(f'{name:<8}{len(rows):>6}{len(removed):>5}{len(out):>7}   {ex}')
        if mode == 'run' and removed:
            bak = xp.replace('.xlsx', '_backup_dedup전.xlsx')
            shutil.copy(xp, bak)
            write_back(ws, out)
            try:
                wb.save(xp)
            except PermissionError:
                wb.save(xp.replace('.xlsx', '_LP.xlsx'))
                print(f'   !! 원본 잠김 → _LP.xlsx 저장')
    print('-' * 80)
    print(f'총 제거 대상: {grand}행  ({mode})')


if __name__ == '__main__':
    main()