from PIL import Image, ImageDraw, ImageFont from pathlib import Path OUT=Path('/home/hyein/jh-mh/장헌산업/exports') font=ImageFont.truetype('/usr/share/fonts/truetype/dejavu/DejaVuSans.ttf',17) font_b=ImageFont.truetype('/usr/share/fonts/truetype/dejavu/DejaVuSans-Bold.ttf',20) 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='#252b2e'; BORDER='#59636a'; TEXT='#edf2f4'; MUTED='#a9b2b8'; PK='#f5d76e'; FK='#87c8ff'; BLUE='#60d7f2'; GREEN='#80e27e'; PURPLE='#b99bff'; GRAY='#9ca7ad' def draw_erd(path, title, subtitle, entities, lines, legend): W,H=1500,1000 img=Image.new('RGB',(W,H),BG); d=ImageDraw.Draw(img) heights={k:52+len(v[3])*25+18 for k,v in entities.items()} def card(n): x,y,w,fs=entities[n]; h=heights[n] 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),n,font=font_b,fill=TEXT) yy=y+58 for f in fs: c=PK if f.startswith('PK') else FK if f.startswith('FK') else TEXT d.text((x+18,yy),f,font=font_s,fill=c); yy+=25 def a(n,s,off=0): x,y,w,_=entities[n]; h=heights[n] return {'r':(x+w,y+h//2+off),'l':(x,y+h//2+off),'t':(x+w//2+off,y),'b':(x+w//2+off,y+h)}[s] def arrow(p1,p2,label,color,via_x=None,via_y=None): x1,y1=p1; x2,y2=p2 pts=[p1] 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 += [p2] d.line(pts,fill=color,width=4,joint='curve') x,y=pts[-1] if x2>=x1: tri=[(x,y),(x-10,y-6),(x-10,y+6)] else: tri=[(x,y),(x+10,y-6),(x+10,y+6)] d.polygon(tri,fill=color) mid=pts[len(pts)//2] tw=d.textlength(label,font=font_s) d.rounded_rectangle((mid[0]-tw/2-8,mid[1]-14,mid[0]+tw/2+8,mid[1]+14),radius=5,fill=BG,outline='#4b5358') d.text((mid[0]-tw/2,mid[1]-9),label,font=font_s,fill=MUTED) d.text((60,35),title,font=font_t,fill=TEXT) d.text((60,73),subtitle,font=font_s,fill=MUTED) for x1,x2,col_title in [(45,445,'SOURCE'),(535,985,'MATCH / DETAIL'),(1090,1455,'OUTPUT')]: d.rounded_rectangle((x1,110,x2,890),radius=12,fill='#181b1c',outline='#2c3235') d.text((x1+20,130),col_title,font=font_b,fill='#7f8a90') for ln in lines: src,ss,dst,ds,label,color,via=ln kwargs={} if via and via[0]=='x': kwargs['via_x']=via[1] if via and via[0]=='y': kwargs['via_y']=via[1] arrow(a(src,ss),a(dst,ds),label,color,**kwargs) for n in entities: card(n) d.rounded_rectangle((60,920,1440,970),radius=8,fill='#1d2123',outline='#3a4247') d.text((80,936),legend,font=font_s,fill=MUTED) img.save(path) print(path) people_entities={ 'member':(80,170,320,['PK MemberNo','korName','teamName','rankName','groupCode','rankCode','isRetired']), 'dallyproject':(80,510,350,['PK id','FK MemberNo','WorkDate','EntryPCode','TotalHours','RegularHours','OvertimeHours']), 'site_worksheet_record':(570,250,390,['PK projectCode + workDate','PK memberNo + korName','FK memberNo','jobType','workText','personCount']), 'work_calendar_detail':(570,610,390,['PK id','source: sql / site','FK memberNo','workDate','projectCode','hours','personCount']), 'work_calendar_day':(1120,360,360,['PK memberNo + workDate','korName / teamName / rankName','sqlHours','siteCount','sqlProjectCodes','siteProjectCodes','hasSql / hasSite'])} people_lines=[ ('member','r','site_worksheet_record','l','employee match',BLUE,('x',500)), ('member','r','dallyproject','l','MemberNo',BLUE,('y',470)), ('dallyproject','r','work_calendar_detail','l','SQL rows',BLUE,('x',505)), ('site_worksheet_record','b','work_calendar_detail','t','site rows',PURPLE,('x',765)), ('work_calendar_detail','r','work_calendar_day','l','daily summary',GRAY,('x',1040)), ('member','r','work_calendar_day','l','person/day output',BLUE,('x',1030)), ] project_entities={ 'project_alias':(80,190,350,['PK projectCode','shortName','bridge/project name']), 'dallyproject':(80,520,350,['PK id','EntryPCode','WorkDate','MemberNo','TotalHours','OvertimeHours']), 'site_worksheet_worker_cache':(570,150,420,['PK projectCode + workDate','PK korName + jobType','workText','note','personCount','syncedAt']), 'site_worksheet_day_sync':(570,500,390,['PK projectCode + workDate','syncedAt']), 'site_worksheet_menu_sync':(570,700,390,['PK projectCode + workDate','PK selMenu','2 = normal worksheet','3 = tension/temp works','syncedAt']), 'work_calendar_detail':(1120,240,370,['PK id','source: sql / site','projectCode','projectName','workText','memberNo','hours','personCount']), 'work_calendar_day':(1120,650,370,['PK memberNo + workDate','siteProjectCodes','siteWorkTexts','sqlProjectCodes','siteCount','hasSql / hasSite'])} project_lines=[ ('project_alias','r','site_worksheet_worker_cache','l','projectCode',GREEN,('x',515)), ('project_alias','r','dallyproject','l','EntryPCode',GREEN,('y',450)), ('site_worksheet_day_sync','t','site_worksheet_worker_cache','b','date fetched',GRAY,('x',1040)), ('site_worksheet_menu_sync','t','site_worksheet_worker_cache','b','menu 2/3 fetched',GRAY,('x',1080)), ('site_worksheet_worker_cache','r','work_calendar_detail','l','ERP raw site rows',PURPLE,('x',1040)), ('dallyproject','r','work_calendar_detail','l','SQL project rows',BLUE,('x',1040)), ('project_alias','r','work_calendar_detail','l','projectName',GREEN,('x',1040)), ('work_calendar_detail','b','work_calendar_day','t','calendar output',GRAY,('x',1305)), ] draw_erd(OUT/'work_data_erd_people.png','People-Centered ERD','How one employee is connected to SQL work, ERP site work, and calendar output',people_entities,people_lines,'Blue = employee/SQL relation Purple = ERP matched rows Gray = calendar aggregation') draw_erd(OUT/'work_data_erd_project.png','Project-Centered ERD','How project codes, bridge names, ERP worksheet menus, and calendar details are connected',project_entities,project_lines,'Green = project code/name Purple = ERP worksheet data Blue = SQL project rows Gray = sync/calendar flow')