from pathlib import Path from datetime import datetime, date, timedelta from collections import Counter, defaultdict from difflib import SequenceMatcher import json, re, hashlib, math, unicodedata import xlsxwriter import openpyxl ROOT = Path(__file__).resolve().parent LOGS = json.loads((ROOT / 'source/logs.json').read_text()) START, END = date(2026, 8, 17), date(2026, 10, 8) DAYS = [START + timedelta(days=i) for i in range((END - START).days + 1)] PEOPLE = sorted({x['name'] for x in LOGS}) # Alias groups are grounded in the exported logs. Ambiguous customers remain separate. PROJECTS = [ ('滨州健康科技职业学院','滨州|滨职|滨州职校','学校','餐厅上线与就餐保障'), ('中央美术学院','中央美术学院|中央美院|央美|美院','学校','餐厅建设与一卡通对接'), ('江西206',r'江西.?206|(?）)]+', '[原文链接已隐藏]', text) text = re.sub(r'\b(?:\d{1,3}\.){3}\d{1,3}\b', '[地址已隐藏]', text) text = re.sub(r'(?i)(?=24 and len(on)>=24 and (n in on or on in n)) or (same_proj and len(n)>=20 and ratio>=0.9)): duplicate=other;break if duplicate: it['duplicate']='同份汇报复述：'+duplicate['cell'];it['count']=0 else:seen.append((it,n)) # Stable IDs and accounting base: one retained current-action segment = one item, not one hour. for i,it in enumerate(items,1):it['key']=f'W{i:05}' ACTIVE=[x for x in items if x['count']] by_log={x['id']:x for x in LOGS} by_day_person=defaultdict(list) for it in ACTIVE:by_day_person[(it['date'],it['name'])].append(it) logdp=Counter((x['date'],x['name']) for x in LOGS) day_types=Counter((x['date'],x['category']) for x in ACTIVE) person_types=Counter((x['name'],x['category']) for x in ACTIVE) PERSONNOTES={ '侯世伟':'现场交付、施工与设备安装；涉及滨州、央美、安庆和张家港，重点是现场条件与交付节点。', '张帅A':'设备安装调试、客户培训和售后；同时覆盖餐厅、食品安全、体测和训练设备。', '赵野':'多项目现场推进、网络/设备排查和客户沟通；四方达、三全、哈尔滨等项目经常并行。', '王金洋':'滨州就餐现场保障占据大量日志，也承担设备测试、更换、消费机升级及金斯瑞支持。', '柴玉龙':'服务器部署、项目上线、远程技术保障；较多项目之间切换，并协同交付人员处理现场条件。', '兰海军':'后端功能、数据及接口对接、安全整改；后期出现达梦数据库适配与迁移。', '赖清涛':'私有化部署、接口对接、代码审核与项目技术方案；跨多个餐厅项目进行技术协作。', '郭东伟':'PC/小程序页面、设备数据展示与功能开发；后期参与食安和前厅达梦适配。', '孙倩倩':'菜品识别、消费功能和学校膳食营养开发与测试；也处理食安部署及设备接口问题。', '尹旭亮':'设备端软件、称重/刷卡/支付兼容、硬件问题定位和厂家技术沟通。', '田亚辉':'初期小程序事项，后期主要是三全的主数据、钉钉/魔点回调、成本中心和订单对接。', '王玉':'AI跑步/训练助手的后端、知识库、训练计划和小程序联调；包含入职学习与服务配置。', '李潇':'客户需求确认、产品原型、标准产品资料、营养设计和多个项目的优先级协调。', '孙海琴':'产品原型、用户手册、宣传物料及项目需求跟进；同时有报销和知识资料沉淀。', '王朔A':'售前方案与投标报价、安全认证材料、学校膳食营养设计及产品资料建设。', } OUT=ROOT/'团队工作日志与通俗工作分类_20260817-20261008.xlsx' wb=xlsxwriter.Workbook(OUT,{'strings_to_formulas':False,'strings_to_urls':False}) wb.set_properties({'title':'团队工作日志与通俗工作分类','subject':'企业微信原始日志、日期人员视图与项目产品分类','author':'Codex','comments':'内容标签为分析判断，不能替代工时或绩效考核。'}) fmt=wb.add_format({'font_name':'微软雅黑','font_size':12,'valign':'top','text_wrap':True}) titlefmt=wb.add_format({'font_name':'微软雅黑','font_size':20,'bold':True,'font_color':'163B56'}) headfmt=wb.add_format({'font_name':'微软雅黑','font_size':12,'bold':True,'font_color':'FFFFFF','bg_color':'163B56','valign':'vcenter','text_wrap':True}) numfmt=wb.add_format({'font_name':'微软雅黑','font_size':12,'valign':'top','num_format':'0'}) pctfmt=wb.add_format({'font_name':'微软雅黑','font_size':12,'num_format':'0.0%'}) datefmt=wb.add_format({'font_name':'微软雅黑','font_size':12,'num_format':'yyyy-mm-dd','valign':'top'}) warnfmt=wb.add_format({'font_name':'微软雅黑','font_size':12,'bg_color':'FFF1CF','font_color':'835817','text_wrap':True,'valign':'top'}) greenfmt=wb.add_format({'font_name':'微软雅黑','font_size':12,'bg_color':'E8F3EE','font_color':'226D51','text_wrap':True,'valign':'top'}) linkfmt=wb.add_format({'font_name':'微软雅黑','font_size':12,'font_color':'1677C8','underline':1}) def sheet(name,headers,rows,widths=None): ws=wb.add_worksheet(name) ws.hide_gridlines(2);ws.freeze_panes(1,3 if len(headers)>3 else 1) ws.set_row(0,44,headfmt) ws.set_column(0,len(headers)-1,20,fmt) for col,width in (widths or {}).items():ws.set_column(col,col,width,fmt) ws.write_row(0,0,headers,headfmt) for i,row in enumerate(rows,1): for j,v in enumerate(row): if isinstance(v,(int,float)):ws.write_number(i,j,v,numfmt) elif isinstance(v,date):ws.write_datetime(i,j,datetime.combine(v,datetime.min.time()),datefmt) else:ws.write_string(i,j,redact(v),fmt) linecount=1 for j,v in enumerate(row): width=(widths or {}).get(j,20) wrapped=sum(max(1,math.ceil(sum(2 if unicodedata.east_asian_width(ch) in 'WF' else 1 for ch in line)/max(10,width-2))) for line in str(v).split('\n')) linecount=max(linecount,wrapped) ws.set_row(i,min(150,max(30,18*linecount+8))) if rows:ws.add_table(0,0,len(rows),len(headers)-1,{'columns':[{'header':h} for h in headers],'style':'Table Style Medium 2'}) ws.set_landscape();ws.fit_to_pages(1,0);ws.repeat_rows(0);ws.set_paper(9) return ws overview=wb.add_worksheet('先看这里') overview.hide_gridlines(2);overview.set_column('A:A',4);overview.set_column('B:B',32,fmt);overview.set_column('C:H',20,fmt) overview.merge_range('B2:H3','团队每天都在做什么',titlefmt) overview.merge_range('B5:H6','2026-08-17 至 2026-10-08｜当前账号收到的「工作过程线上化--工作日志」｜后台分段导出',fmt) summary=[('原始汇报数',len(LOGS)),('涉及人员',len(PEOPLE)),('有提交记录的日期',len({x['date'] for x in LOGS})),('日历范围天数',len(DAYS)),('保留用于统计的今日行动片段',len(ACTIVE)),('同人同日多份汇报组合',sum(n>1 for n in logdp.values())),('凌晨提交记录（工作日期待核对）',sum(x['time'][11:13] in ['00','01','02','03','04','05'] for x in LOGS))] for r,(label,n) in enumerate(summary,8):overview.write(r,1,label,fmt);overview.write_number(r,2,n,numfmt) overview.merge_range('B17:H19','阅读顺序：先看「每日人员矩阵」找到某天某人；再看「日期人员日览」了解当天做了什么；「事项标签明细」可按工作类型、项目、产品、状态筛选；「人员工作画像」查看每个人的主要工作；「类型说明与标签」用普通话解释分类。',fmt) overview.merge_range('B21:H23','统计口径：一个计入片段是日志中的一段可识别行动，绝不等于一个任务、一小时或绩效。一条片段只分配一个主类型，辅助标签可以有多个。事项写得详细的人会有更多片段，不能据此比较忙碌程度。',warnfmt) overview.merge_range('B25:H28','日期按导出文件的「汇报时间」归属。实际工作日期只有原文明确写出时才另列；凌晨补报不自动移到前一天。同人同日不同汇报均保留。同份日志中的高度相似复述不重复计数。没有记录仅表示本次导出未发现，不能认定没工作、缺报或旷工。',warnfmt) overview.merge_range('B30:H33','采集范围完整性：第一段316份、第二段147份，合计463份，编号互不重复，原始数据1218行均保留。关联结果表当前无权限，无法补核工作日归属。附件列标注「已删除」，本次没有附件内容。今天截至导出时点未发现提交记录。',warnfmt) overview.merge_range('B35:H37','标签是基于日志内容的语义归类与可复算规则，未使用工时估算，也未向外部分类服务发送原文。项目别名依同一客户名称归并；安徽/安庆等不能证实相同的项目分别保留。含账号口令、服务器地址、手机号及原文网址的字段在分析表中隐藏，未经独立核验的完成情况仅标「据原文」。',fmt) for i,n in enumerate(['每日人员矩阵','日期人员日览','人员工作画像','工作类型总览','项目汇总','产品汇总','事项标签明细','原始日志汇总','原始字段逐行','类型说明与标签','质量与采集核验'],40):overview.write_url(i,1,f"internal:'{n}'!A1",linkfmt,n) rawrows=[] for i,l in enumerate(sorted(LOGS,key=lambda x:(x['time'],x['name'])),1): l['localid']=f'L{i:04}' work='\n\n'.join(f"[{w['number']} {w['metric']}]\n{w['text']}" for w in l['work']) flags=[] if logdp[(l['date'],l['name'])]>1:flags.append('同人同日多份：均保留') if l['time'][11:13] in ['00','01','02','03','04','05']:flags.append('凌晨提交：工作日待确认') rawrows.append([l['localid'],l['date'],l['name'],l['time'],l['department'].split('/')[-1],work,l['other'],l['tomorrow'],l['learning'],'；'.join(flags),l['source'],l['source_row']]) sheet('原始日志汇总',['日志编号','提交日期','人员','汇报时间','部门','重点工作原文（敏感值隐藏）','其他重点工作原文（敏感值隐藏）','明日目标原文','学习与反思原文','日期/重复提示','来源文件','源首行'],rawrows,{5:90,6:90,7:65,8:55,10:47}) source_rows=[] for l in sorted(LOGS,key=lambda x:(x['time'],x['name'])): for idx,row in enumerate(l['all_rows']):source_rows.append([l['localid'],l['date'],l['name'],l['source'],l['source_row']+idx]+row[6:]) sheet('原始字段逐行',['日志编号','提交日期','人员','来源文件','源行号','重点序号','绩效指标原文','今日进度原文（敏感值隐藏）','其他重点工作原文','明日目标原文','学习反思原文','评论原文'],source_rows,{3:45,6:42,7:100,8:90,9:70,10:60,11:60}) ih=['事项编号','日志编号','提交日期','人员','部门','主工作类型','辅助工作类型','客户项目/工作归属','产品/能力','客户领域','阶段','工作对象','内容属性','原文片段（敏感值隐藏）','上下文标题','日志填报栏目','结果状态','交付物提示','项目判定依据','原文明示工作日期','计入今日行动(1/0)','重复说明','待核验提示','来源文件','源单元格','工作范围'] ir=[[x['key'],by_log[x['log']]['localid'],x['date'],x['name'],x['department'],x['category'],x['alltypes'],x['projects'],x['products'],x['sector'],x['stage'],x['objects'],x['mode'],x['text'],x['heading'],x['metric'],x['state'],x['result'],x['basis'],x['workdate'],x['count'],x['duplicate'],x['flags'],x['source'],x['cell'],x['scope']] for x in items] sheet('事项标签明细',ih,ir,{5:28,6:45,7:40,8:42,13:100,14:55,15:43,16:40,18:35,21:45,22:45,23:45}) daily=[] matrix=[] for d in DAYS: ds=d.isoformat();mr=[d,['周一','周二','周三','周四','周五','周六','周日'][d.weekday()]] for name in PEOPLE: its=by_day_person[(ds,name)];n=logdp[(ds,name)] cats=Counter(x['category'] for x in its) typ='；'.join(f'{c}({nn})' for c,nn in cats.most_common()) pj=sorted({p for x in its for p in x['projects'].split('；') if p!='待确认/未说明'}) pd=sorted({p for x in its for p in x['products'].split('；') if p!='待确认/未说明'}) ex='\n'.join(f"• {x['text']}" for x in sorted(its,key=lambda x:len(x['text']),reverse=True)[:5]) status='有日志但无具体行动片段' if n and not its else ('有日志' if n else '本次导出无记录（非缺勤判定）') daily.append([d,name,status,n,len(its),typ,'；'.join(pj) or '待确认/未说明','；'.join(pd) or '待确认/未说明',ex,'；'.join(x['localid'] for x in LOGS if x['name']==name and x['date']==ds)]) mr.append(f"{n}份｜"+' / '.join(c for c,_ in cats.most_common(3)) if n else '无导出记录') matrix.append(mr) ws=sheet('日期人员日览',['提交日期','人员','日志状态','汇报份数','今日行动片段数','当天工作类型（片段数）','项目','产品/能力','具体工作举例（最多5段）','原始日志编号'],daily,{2:39,5:65,6:60,7:60,8:110,9:35}) mx=sheet('每日人员矩阵',['提交日期','星期']+PEOPLE,matrix,{i:35 for i in range(2,17)}) mx.freeze_panes(1,2);mx.set_zoom(70) mx.conditional_format(1,2,len(matrix),len(PEOPLE)+1,{'type':'text','criteria':'containing','value':'无导出记录','format':warnfmt}) portraits=[] for name in PEOPLE: own=[x for x in ACTIVE if x['name']==name];ls=[x for x in LOGS if x['name']==name] ct=Counter(x['category'] for x in own) pjs=Counter(p for x in own for p in x['projects'].split('；') if p!='待确认/未说明') prs=Counter(p for x in own for p in x['products'].split('；') if p!='待确认/未说明') portraits.append([name,ls[0]['department'].split('/')[-1],len(ls),len({x['date'] for x in ls}),len(own),PERSONNOTES.get(name,''),'；'.join(f'{c}({n})' for c,n in ct.most_common(5)),'；'.join(p for p,_ in pjs.most_common(6)),'；'.join(p for p,_ in prs.most_common(5)),'日志自述画像；片段数不用于工时或绩效排名']) sheet('人员工作画像',['人员','部门','汇报份数','有提交日期数','具体行动片段数','通俗工作画像（分析）','主要工作类型及片段数','经常涉及的项目','经常涉及的产品/能力','使用边界'],portraits,{5:95,6:70,7:65,8:65,9:65}) endrow=len(items)+1 countformula=f"'事项标签明细'!$U$2:$U${endrow}" catformula=f"'事项标签明细'!$F$2:$F${endrow}" personformula=f"'事项标签明细'!$D$2:$D${endrow}" dateformula=f"'事项标签明细'!$C$2:$C${endrow}" type_rows=[] total=len(ACTIVE) typect=Counter(x['category'] for x in ACTIVE) for name,ex,pat in TYPES: its=[x for x in ACTIVE if x['category']==name] type_rows.append([name,ex,len(its),len(its)/total if total else 0,'；'.join(sorted({x['name'] for x in its})),'\n'.join(x['text'][:150] for x in its[:3])]) tw=sheet('工作类型总览',['主要工作类型','外行解释','今日行动片段数','片段占比（非工时）','涉及人员','真实日志举例'],type_rows,{0:29,1:80,4:75,5:100}) for i,row in enumerate(type_rows,1): tw.write_formula(i,2,f'=SUMIFS({countformula},{catformula},A{i+1})',numfmt,row[2]) tw.write_formula(i,3,f'=IFERROR(C{i+1}/SUM(C$2:C${len(type_rows)+1}),0)',pctfmt,row[3]) chart=wb.add_chart({'type':'bar'}) chart.add_series({'name':'今日行动片段','categories':['工作类型总览',1,0,len(TYPES),0],'values':['工作类型总览',1,2,len(TYPES),2],'fill':{'color':'258D9B'}}) chart.set_title({'name':'工作类型分布（按日志片段，非工时）'});chart.set_legend({'none':True});chart.set_size({'width':900,'height':580});tw.insert_chart('H2',chart) ptrows=[] for name in PEOPLE: ptrows.append([name]+[person_types[(name,t[0])] for t in TYPES]+[sum(person_types[(name,t[0])] for t in TYPES)]) ps=sheet('人员类型交叉表',['人员']+[t[0] for t in TYPES]+['片段合计'],ptrows,{i:25 for i in range(1,len(TYPES)+2)}) for ri,row in enumerate(ptrows,1): for ci,t in enumerate(TYPES,1): from xlsxwriter.utility import xl_col_to_name ps.write_formula(ri,ci,f'=SUMIFS({countformula},{personformula},$A{ri+1},{catformula},{xl_col_to_name(ci)}$1)',numfmt,row[ci]) ps.write_formula(ri,len(TYPES)+1,f'=SUM(B{ri+1}:{xl_col_to_name(len(TYPES))}{ri+1})',numfmt,row[-1]) ps.conditional_format(1,1,len(PEOPLE),len(TYPES),{'type':'3_color_scale','min_color':'FFFFFF','mid_color':'D9EEF0','max_color':'258D9B'}) dtrows=[] for d in DAYS: ds=d.isoformat();ct=[day_types[(ds,t[0])] for t in TYPES] dtrows.append([ds,sum(x['date']==ds for x in LOGS),len({x['name'] for x in LOGS if x['date']==ds})]+ct+[sum(ct)]) dsheet=sheet('每日类型交叉表',['提交日期','汇报份数','提交人数']+[t[0] for t in TYPES]+['片段合计'],dtrows,{i:25 for i in range(3,len(TYPES)+4)}) for ri,row in enumerate(dtrows,1): for ci,t in enumerate(TYPES,3):dsheet.write_formula(ri,ci,f'=SUMIFS({countformula},{dateformula},$A{ri+1},{catformula},{xl_col_to_name(ci)}$1)',numfmt,row[ci]) dsheet.write_formula(ri,len(TYPES)+3,f'=SUM(D{ri+1}:{xl_col_to_name(len(TYPES)+2)}{ri+1})',numfmt,row[-1]) dsheet.conditional_format(1,3,len(DAYS),len(TYPES)+2,{'type':'3_color_scale','min_color':'FFFFFF','mid_color':'D9EEF0','max_color':'258D9B'}) def aggregate(field): rows=[] vals=sorted({p for x in ACTIVE for p in x[field].split('；')}) for p in vals: own=[x for x in ACTIVE if p in x[field].split('；')] types=Counter(x['category'] for x in own) other='products' if field=='projects' else 'projects' rows.append([p,'；'.join(sorted({x['sector'] for x in own})),len(own),len({x['log'] for x in own}),len({x['name'] for x in own}),len({x['date'] for x in own}),'；'.join(sorted({x['name'] for x in own})),'；'.join(f'{t}({n})' for t,n in types.most_common(5)),'；'.join(sorted({z for x in own for z in x[other].split('；')})),'\n'.join(x['text'][:150] for x in own[:3]),'多标签可重复关联，不能把各行数量相加作为总工作量']) return rows sheet('项目汇总',['项目（统一别名）','客户领域','关联行动片段数','涉及汇报份数','涉及人数','涉及日期数','参与人员','常见工作类型','产品/能力','日志例子','统计说明'],aggregate('projects'),{0:42,6:70,7:75,8:75,9:100,10:70}) sheet('产品汇总',['产品/能力','关联客户领域','关联行动片段数','涉及汇报份数','涉及人数','涉及日期数','参与人员','常见工作类型','涉及项目','日志例子','统计说明'],aggregate('products'),{0:35,6:70,7:75,8:90,9:100,10:70}) dictionary=[['工作性质',n,note,'按实际行动词判定，主类型唯一，辅助类型可多选'] for n,note,_ in TYPES] dictionary += [['项目',n,f'别名识别规则：{pat}',f'客户领域：{sec}；常见主题：{topic}；只用原句或清楚的上下文，不按人员岗位推断'] for n,pat,sec,topic in PROJECTS] dictionary += [['产品/能力',n,'涵盖关键词：'+pat,'按具体能力识别，可以多选；未说明时留待确认'] for n,pat in PRODS] dictionary += [['内容属性',n,note,'只有今日行动中的具体且未重复片段计入统计'] for n,note in [('今日行动','原文描述的当日动作，不等于独立验收完成'),('未来计划','明日、下一步、预计完成或项目待办'),('风险/卡点','客户条件、故障和未解决的约束'),('复述/经验','产品差异性、经验总结等，避免与行动重复'),('休假/非工作事项','请假和休假记录保留，但不算具体工作')]] sheet('类型说明与标签',['标签维度','标签名称','普通话解释/识别依据','使用规则'],dictionary,{0:20,1:44,2:95,3:95}) quality=[] for p,expected in [('01-原始日志-20260817-20260916.xlsx',316),('02-原始日志-20260917-20261008.xlsx',147)]: actual=sum(x['source']==p for x in LOGS) quality.append(['采集对账',p,f'UI显示{expected}份；文件读取{actual}份','通过' if actual==expected else '不一致','来源文件汇报编号逐份计数']) quality += [ ['合并去重','汇报编号',f'{len(LOGS)}份 / {len({x["id"] for x in LOGS})}个唯一编号','通过','不同编号的同人同日记录均保留'], ['逐行保留','原始字段',f'{len(source_rows)}个源数据行','通过','2个文件870/350行，扣除各1行表头：1218'], ['范围','提交日期','2026-08-17至2026-10-07有数据；10-08截至导出时点无记录','已说明','53个日历日全部出现在日览和矩阵中'], ['日期归属','实际工作日期','导出没有单独的工作所属日期字段；凌晨提交与补报需核对','待核验','原文明示的日期另列，不自动把凌晨提交移到前一天'], ['权限','关联结果表','当前账号无权限；未点击申请权限','不影响现有日志分析','不能借关联表补核日期或缺报'], ['缺报','应报人员及日历','没有完整应报人员/休假/日报计划的系统证据','不作缺报率','无记录不等于没工作'], ['附件','原导出附件列','来源列为附件（已删除）','无附件内容','分析只覆盖导出中的文本'], ['隐私','敏感字段','分析工作簿隐藏账号口令、地址、手机号和原文网址','已隐藏','原始文件仅本地保存，不上传、发消息或纳入Git'], ['算法','分类方法','文本语义分组+本地规则+上下文继承+同份近似复述去重','可复算','未使用外部向量/分类服务；此报告非独立绩效审核'], ] for l in LOGS: n=logdp[(l['date'],l['name'])] if n>1:quality.append(['同日多份',l['name']+' '+l['date'],l['localid'],'已保留','不同编号，不自动删；实际日期需核对']) if l['time'][11:13] in ['00','01','02','03','04','05']:quality.append(['凌晨提交',l['name']+' '+l['date'],l['localid']+' '+l['time'],'工作日期待确认','统计仍按提交日期，原文如明示日期则另列']) sheet('质量与采集核验',['核验维度','对象','事实/数量','结果或限制','说明'],quality,{0:22,1:50,2:95,3:35,4:100}) for s in wb.worksheets():s.set_tab_color('258D9B' if s.name in ['先看这里','每日人员矩阵','人员工作画像'] else '163B56') wb.close() (ROOT/'source/items.json').write_text(json.dumps(items,ensure_ascii=False,indent=2)) facts={'reports':len(LOGS),'people':len(PEOPLE),'calendar_days':len(DAYS),'report_days':len({x['date'] for x in LOGS}),'raw_data_rows':len(source_rows),'segments':len(items),'counted_segments':len(ACTIVE),'duplicates_removed_from_counts':sum(bool(x['duplicate']) for x in items),'type_counts':dict(typect),'people_reports':dict(Counter(x['name'] for x in LOGS)),'unclassified_active':sum(x['category']=='待确认工作类型' for x in ACTIVE),'project_unknown_active':sum(x['projects']=='待确认/未说明' for x in ACTIVE),'product_unknown_active':sum(x['products']=='待确认/未说明' for x in ACTIVE),'workbook':str(OUT),'sheets':len(wb.worksheets())} (ROOT/'validation.json').write_text(json.dumps(facts,ensure_ascii=False,indent=2)) print(json.dumps(facts,ensure_ascii=False,indent=2))