from PIL import Image, ImageDraw, ImageFont from pathlib import Path OUT = Path('/home/hyein/jh-mh/장헌산업/exports') PNG = OUT / 'work_data_erd_columns.png' W, H = 1800, 1250 img = Image.new('RGB', (W, H), '#151718') d = ImageDraw.Draw(img) font = ImageFont.truetype('/usr/share/fonts/truetype/dejavu/DejaVuSans.ttf', 17) font_b = ImageFont.truetype('/usr/share/fonts/truetype/dejavu/DejaVuSans-Bold.ttf', 19) font_t = ImageFont.truetype('/usr/share/fonts/truetype/dejavu/DejaVuSans-Bold.ttf', 30) font_s = ImageFont.truetype('/usr/share/fonts/truetype/dejavu/DejaVuSans.ttf', 14) BG = '#151718' CARD = '#1f2426' HEADER = '#242a2d' BORDER = '#4c555a' LINE = '#8b969d' TEXT = '#e7ecef' MUTED = '#a6b0b6' PK = '#f6d365' FK = '#8bc7ff' BLUE = '#67d8ef' PURPLE = '#c4a7ff' GREEN = '#8be28b' entities = { # col 1 source 'member': (80, 170, 310, ['PK MemberNo', 'korName', 'teamName', 'rankName', 'groupCode', 'rankCode', 'isRetired']), 'dallyproject': (80, 500, 330, ['PK id', 'FK MemberNo', 'WorkDate', 'EntryPCode', 'EntryTime', 'LeaveTime', 'OverTime', 'TotalHours', 'RegularHours', 'OvertimeHours']), 'project_alias': (80, 900, 330, ['PK projectCode', 'shortName', 'bridge/project name']), # col 2 erp collection 'site_worksheet_worker_cache': (610, 140, 400, ['PK projectCode + workDate', 'PK korName + jobType', 'workText', 'note', 'personCount', 'syncedAt']), 'site_worksheet_record': (610, 500, 400, ['PK projectCode + workDate', 'PK memberNo + korName', 'FK memberNo', 'jobType', 'workText', 'note', 'personCount']), 'site_worksheet_day_sync': (610, 850, 400, ['PK projectCode + workDate', 'syncedAt']), 'site_worksheet_menu_sync': (610, 1035, 400, ['PK projectCode + workDate', 'PK selMenu', '2 = normal worksheet', '3 = tension/temp works', 'syncedAt']), # col 3 integrated calendar 'work_calendar_detail': (1240, 310, 390, ['PK id', 'source: sql / site', 'FK memberNo', 'workDate', 'projectCode', 'projectName', 'workText', 'jobType', 'hours', 'regularHours', 'overtimeHours', 'personCount']), 'work_calendar_day': (1240, 790, 390, ['PK memberNo + workDate', 'korName', 'teamName', 'rankName', 'sqlHours', 'sqlProjectCodes', 'siteCount', 'siteProjectCodes', 'siteWorkTexts', 'hasSql', 'hasSite']), } heights = {} for k,(x,y,w,fields) in entities.items(): heights[k] = 52 + len(fields)*25 + 18 def draw_card(name): x,y,w,fields = entities[name] h = heights[name] d.rounded_rectangle((x,y,x+w,y+h), radius=8, fill=CARD, outline=BORDER, width=2) d.rectangle((x,y,x+w,y+42), fill=HEADER) d.line((x,y+42,x+w,y+42), fill=BORDER, width=1) d.text((x+16,y+11), name, font=font_b, fill=TEXT) yy = y+58 for f in fields: color = TEXT if f.startswith('PK'): color = PK elif f.startswith('FK'): color = FK d.text((x+18,yy), f, font=font_s, fill=color) yy += 25 def anchor(name, side, offset=0): x,y,w,_ = entities[name] h = heights[name] if side == 'r': return (x+w, y+h//2+offset) if side == 'l': return (x, y+h//2+offset) if side == 't': return (x+w//2+offset, y) if side == 'b': return (x+w//2+offset, y+h) def orth(a, b, label='', color=LINE, via_x=None, via_y=None): x1,y1 = a; x2,y2 = b pts = [(x1,y1)] if via_x is not None: pts += [(via_x,y1), (via_x,y2)] elif via_y is not None: pts += [(x1,via_y), (x2,via_y)] else: mx = (x1+x2)//2 pts += [(mx,y1), (mx,y2)] pts += [(x2,y2)] d.line(pts, fill=color, width=3) for p in (pts[0], pts[-1]): d.ellipse((p[0]-5,p[1]-5,p[0]+5,p[1]+5), fill=color) if label: # place label around middle point mid = pts[len(pts)//2] tw = d.textlength(label, font=font_s) x,y = mid d.rounded_rectangle((x-tw/2-8,y-13,x+tw/2+8,y+13), radius=5, fill=BG, outline='#4a5054') d.text((x-tw/2,y-9), label, font=font_s, fill=MUTED) # background columns cols = [(50,120,470,1135,'SOURCE'), (570,120,1050,1135,'ERP CACHE / MATCH'), (1200,120,1680,1135,'CALENDAR INTEGRATION')] for x1,y1,x2,y2,title in cols: d.rounded_rectangle((x1,y1,x2,y2), radius=12, fill='#181b1c', outline='#2a2f31') d.text((x1+20,y1+18), title, font=font_b, fill='#6f7a80') # title d.text((60,40), 'Work Data ERD', font=font_t, fill=TEXT) d.text((60,78), 'Columns arranged by data flow: source -> ERP cache/match -> calendar integration', font=font_s, fill=MUTED) # lines first orth(anchor('member','r',-25), anchor('dallyproject','l',-60), 'MemberNo', via_x=500, color=BLUE) orth(anchor('member','r',10), anchor('site_worksheet_record','l',-40), 'MemberNo', via_x=535, color=BLUE) orth(anchor('project_alias','r',-30), anchor('dallyproject','r',20), 'EntryPCode', via_x=500, color=GREEN) # project alias to erp and calendar orth(anchor('project_alias','r',10), anchor('site_worksheet_worker_cache','l',-30), 'projectCode', via_x=540, color=GREEN) # cache and sync to worker cache orth(anchor('site_worksheet_day_sync','t'), anchor('site_worksheet_worker_cache','b',80), 'project+date fetched', via_x=1110, color=LINE) ord_start = anchor('site_worksheet_menu_sync','t') orth(ord_start, anchor('site_worksheet_worker_cache','b',150), 'menu 2/3 fetched', via_x=1160, color=LINE) # worker cache to record orth(anchor('site_worksheet_worker_cache','b'), anchor('site_worksheet_record','t'), 'match name/date/project', via_x=820, color=PURPLE) # sources to detail orth(anchor('dallyproject','r',-10), anchor('work_calendar_detail','l',-80), 'source=sql', via_x=1120, color=BLUE) orth(anchor('site_worksheet_record','r'), anchor('work_calendar_detail','l',20), 'source=site', via_x=1120, color=PURPLE) orth(anchor('project_alias','r',55), anchor('work_calendar_detail','l',70), 'projectName', via_x=1120, color=GREEN) # detail to day orth(anchor('work_calendar_detail','b'), anchor('work_calendar_day','t'), 'daily summary', via_x=1435, color=LINE) # member to day orth(anchor('member','r',55), anchor('work_calendar_day','l',-60), 'MemberNo + workDate', via_x=520, color=BLUE) for name in entities: draw_card(name) # legend d.rounded_rectangle((60,1160,1660,1225), radius=8, fill='#1c2022', outline='#343a3e') d.text((80,1178), 'Legend', font=font_b, fill=TEXT) d.text((180,1179), 'Blue = employee relation Green = project relation Purple = ERP site worksheet matching Gray = sync/calendar flow', font=font_s, fill=MUTED) img.save(PNG) print(PNG)