from PIL import Image, ImageDraw, ImageFont from pathlib import Path OUT = Path('/home/hyein/jh-mh/장헌산업/exports') PNG = OUT / 'work_data_erd.png' SVG = OUT / 'work_data_erd.svg' W, H = 1900, 1280 img = Image.new('RGB', (W, H), '#f6f8fb') d = ImageDraw.Draw(img) font = ImageFont.truetype('/usr/share/fonts/truetype/dejavu/DejaVuSans.ttf', 18) font_b = ImageFont.truetype('/usr/share/fonts/truetype/dejavu/DejaVuSans-Bold.ttf', 20) font_title = ImageFont.truetype('/usr/share/fonts/truetype/dejavu/DejaVuSans-Bold.ttf', 34) font_small = ImageFont.truetype('/usr/share/fonts/truetype/dejavu/DejaVuSans.ttf', 15) entities = { 'member': (70, 110, 330, 330, ['PK MemberNo', 'korName', 'teamName', 'rankName', 'groupCode', 'rankCode', 'isRetired']), 'dallyproject': (470, 80, 780, 360, ['PK id', 'FK MemberNo', 'WorkDate', 'EntryPCode', 'EntryTime / LeaveTime', 'TotalHours', 'RegularHours', 'OvertimeHours']), 'project_alias': (1040, 110, 1330, 280, ['PK projectCode', 'shortName (bridge/project name)']), 'site_worksheet_worker_cache': (1360, 360, 1810, 610, ['PK projectCode + workDate + korName', 'jobType', 'workText', 'note', 'personCount', 'syncedAt']), 'site_worksheet_record': (900, 430, 1260, 690, ['PK projectCode + workDate + memberNo + korName', 'FK memberNo', 'jobType', 'workText', 'personCount']), 'site_worksheet_day_sync': (1370, 720, 1740, 870, ['PK projectCode + workDate', 'syncedAt']), 'site_worksheet_menu_sync': (1370, 930, 1740, 1090, ['PK projectCode + workDate + selMenu', 'selMenu 2 = normal', 'selMenu 3 = tension/temp works', 'syncedAt']), 'work_calendar_detail': (520, 800, 900, 1110, ['PK id', 'source: sql / site', 'FK memberNo', 'workDate', 'projectCode', 'projectName', 'workText', 'hours / regular / overtime', 'personCount']), 'work_calendar_day': (120, 770, 430, 1080, ['PK memberNo + workDate', 'korName / teamName / rankName', 'sqlHours', 'sqlProjectCodes', 'siteCount', 'siteProjectCodes', 'siteWorkTexts', 'hasSql / hasSite']), } colors = { 'member': ('#e0f2fe', '#0369a1'), 'dallyproject': ('#ecfdf5', '#047857'), 'project_alias': ('#fff7ed', '#c2410c'), 'site_worksheet_worker_cache': ('#fef3c7', '#b45309'), 'site_worksheet_record': ('#ede9fe', '#6d28d9'), 'site_worksheet_day_sync': ('#f1f5f9', '#475569'), 'site_worksheet_menu_sync': ('#f1f5f9', '#475569'), 'work_calendar_detail': ('#fee2e2', '#b91c1c'), 'work_calendar_day': ('#dbeafe', '#1d4ed8'), } def box(name, rect, fields): x1,y1,x2,y2 = rect fill, stroke = colors[name] d.rounded_rectangle(rect, radius=14, fill=fill, outline=stroke, width=3) d.rectangle((x1, y1, x2, y1+42), fill=stroke) d.text((x1+14, y1+10), name, fill='white', font=font_b) y = y1 + 55 for f in fields: d.text((x1+16, y), f, fill='#0f172a', font=font_small if len(f) > 34 else font) y += 26 def center(name, side): x1,y1,x2,y2 = entities[name][:4] if side == 'right': return (x2, (y1+y2)//2) if side == 'left': return (x1, (y1+y2)//2) if side == 'top': return ((x1+x2)//2, y1) if side == 'bottom': return ((x1+x2)//2, y2) def line(a, aside, b, bside, label, color='#334155'): p1 = center(a, aside); p2 = center(b, bside) d.line((p1, p2), fill=color, width=3) # endpoint dots for p in (p1, p2): d.ellipse((p[0]-5,p[1]-5,p[0]+5,p[1]+5), fill=color) mx, my = (p1[0]+p2[0])//2, (p1[1]+p2[1])//2 tw = d.textlength(label, font=font_small) d.rounded_rectangle((mx-tw/2-8, my-13, mx+tw/2+8, my+13), radius=7, fill='#ffffff', outline='#cbd5e1') d.text((mx-tw/2, my-9), label, fill=color, font=font_small) # title d.text((70, 30), 'Work Data ERD: SQL + Site Worksheet Integration', fill='#0f172a', font=font_title) d.text((72, 72), 'SQLite DB: /home/hyein/jh-mh/장헌산업/matching.db', fill='#475569', font=font_small) for name, data in entities.items(): box(name, data[:4], data[4]) # relationships line('member','right','dallyproject','left','MemberNo') line('member','right','site_worksheet_record','left','MemberNo') line('member','bottom','work_calendar_day','top','MemberNo + workDate') line('dallyproject','bottom','work_calendar_detail','top','source=sql') line('site_worksheet_record','bottom','work_calendar_detail','right','source=site') line('work_calendar_detail','left','work_calendar_day','right','daily summary') line('project_alias','bottom','site_worksheet_record','top','projectCode') line('project_alias','right','site_worksheet_worker_cache','top','projectCode') line('site_worksheet_worker_cache','left','site_worksheet_record','right','match by name/date/project') line('site_worksheet_day_sync','top','site_worksheet_worker_cache','bottom','project+date fetched') line('site_worksheet_menu_sync','top','site_worksheet_worker_cache','bottom','menu 2/3 fetched') line('project_alias','left','dallyproject','right','EntryPCode') line('project_alias','bottom','work_calendar_detail','right','projectName') # legend lx, ly = 70, 1140 d.rounded_rectangle((lx, ly, 760, ly+95), radius=12, fill='#ffffff', outline='#cbd5e1', width=2) d.text((lx+18, ly+14), 'Flow', font=font_b, fill='#0f172a') d.text((lx+18, ly+43), 'dallyproject = SQL work records / site_worksheet_worker_cache = ERP site worksheet raw rows', font=font_small, fill='#334155') d.text((lx+18, ly+68), 'site_worksheet_record = matched employee rows / work_calendar_* = calendar-ready integrated data', font=font_small, fill='#334155') img.save(PNG) # simple SVG wrapper embeds the PNG path as text fallback is not needed; create standalone SVG rectangles too minimal svg = f''' Work Data ERD: SQL + Site Worksheet Integration PNG version generated at {PNG} Open the PNG file for the full ERD diagram. ''' SVG.write_text(svg, encoding='utf-8') print(PNG) print(SVG)