from pathlib import Path
import csv,re,html,json
from openpyxl import Workbook,load_workbook
from openpyxl.styles import Font,PatternFill,Alignment
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.utils import get_column_letter
from openpyxl.comments import Comment
base=Path(__file__).resolve().parent
out=base/'output';out.mkdir(exist_ok=True)
wb=Workbook();wb.remove(wb.active)
def add(name,filename,widths):
 s=wb.create_sheet(name)
 with (base/filename).open(encoding='utf-8-sig') as f:
  for r in csv.reader(f):s.append(r)
 s.freeze_panes='A2';s.auto_filter.ref=s.dimensions
 for i,w in enumerate(widths,1):s.column_dimensions[get_column_letter(i)].width=w
 return s
s=wb.create_sheet('使用说明')
for r in [['项目','说明'],['用途','政府企业学校90天销售动作；2026-09-10—12-09，内部讨论版'],['第一步','先由Jack确定唯一负责人姓名、现有交付半径与投入人数'],['第二步','从候选复访选12家；所有账户当前都未联系/未确认，不计有效商机'],['有效商机','痛点、付费方、预算、关键人、采购窗口和客户下一动作均有证据'],['销量口径','新增正式合同订单数/金额；主项目、增购、续费、付费试点分列'],['工作目标','1主项目+2增购仅为建议，9/24按真实机会、成本、产能校准'],['阶段边界','原项目未关闭先完成原责任；既有合同权益内不重复收费'],['未知值','空白是待核，不是0；工作量建议不是已确认需求'],['公式','销量模型和报价成本仅在输入齐全后计算；不是自动预测'],['源文档','strategy-and-sales-plan.md；完整来源见source-scope.csv'],['未执行','没有联系客户、写CRM、立项、改生产或公开发布财务资料']]:s.append(r)
s.column_dimensions['A'].width=18;s.column_dimensions['B'].width=110
add('客户分层','segment-definition.csv',[13,23,27,28,45,48,30,20,25])
add('候选复访','candidate-accounts.csv',[9,12,29,40,17,17,17,62,15,17,38])
add('行动台账','action-plan.csv',[10,19,26,54,55,18])
add('采购阶段','journey-gates.csv',[15,70,45,45])
add('责任与关键人','buyer-unit.csv',[13,32,27,24,30,60])
add('经济性缺口','unit-economics-gaps.csv',[35,26,20,35])
s=wb.create_sheet('报价成本')
for r in [['项目','金额（元，同税口径）','说明'],['合同收入',None,'待按客户正式配置填写'],['硬件采购',None,'成本，不是对客报价'],['第三方接口与授权',None,'含基础软件/接口/测评中本方承担项'],['直接实施人工差旅',None,'实际人天×成本及差旅'],['渠道服务',None,'按合同及合规渠道安排'],['直接售后准备',None,'质保/支持估算依据需记录'],['直接成本合计','=IF(COUNT(B3:B7)=5,SUM(B3:B7),"待录入")','无成本填0必须有依据，未知留空'],['项目贡献额','=IF(AND(COUNT(B2:B7)=6,B2>0),B2-SUM(B3:B7),"待录入")','不等于会计毛利/利润'],['贡献率','=IF(AND(COUNT(B2:B7)=6,B2>0),(B2-SUM(B3:B7))/B2,"待录入")','固定研发管理分摊另列'],['付款前累计支出',None,'首笔客户款到账前已支出/必支出'],['同期间客户回款',None,'同一节点，不是合同总额'],['该节点现金暴露','=IF(COUNT(B11:B12)=2,MAX(0,B11-B12),"待录入")','还需逐后续里程碑重复评估']]:s.append(r)
s.column_dimensions['A'].width=28;s.column_dimensions['B'].width=28;s.column_dimensions['C'].width=65
s['B10'].number_format='0.0%'
s=wb.create_sheet('销量模型')
for r in [['输入/指标','数值','说明'],['经批准90天订单目标',None,'先填批准目标；建议3单不自动代入'],['可比报价到签约率',None,'0<赢率≤1；需样本与起止时间，不填默认赢率'],['所需合格报价数','=IF(AND(COUNT(B2:B3)=2,B2>0,B3>0,B3<=1),ROUNDUP(B2/B3,0),"待录入")','同口径报价项目分母；不是承诺'],['当前合格报价机会',None,'需采购路径、预算、成本与交付均确认'],['报价机会缺口','=IF(AND(ISNUMBER(B4),COUNT(B5)=1),MAX(0,B4-B5),"待录入")','待核机会不能进入当前合格数'],['90天可交付项目上限',None,'按实际团队余量，存量承诺已扣除'],['其中正式主项目目标',None,'主项目与增购分列，不把交付数等同合同数'],['交付容量检查','=IF(COUNT(B7:B8)=2,IF(B8<=B7,"容量表面满足，需逐单排期","超过容量，需要调整"),"待录入")','只是项目数检查，复杂度/人天仍需逐单核'],['预计客单价',None,'不同主项目/增购不要混用历史平均'],['签约金额',None,'按逐项目真实报价汇总，暂不自动以平均价外推']]:s.append(r)
s.column_dimensions['A'].width=30;s.column_dimensions['B'].width=38;s.column_dimensions['C'].width=76;s['B3'].number_format='0.0%'
add('证据账本','evidence-ledger.csv',[13,23,22,80,19,38,45])
for s in wb:
 s.freeze_panes='A2';s.sheet_view.showGridLines=False
 for row in s:
  for c in row:
   c.font=Font(name='微软雅黑',size=12,color='202A34');c.alignment=Alignment(vertical='top',wrap_text=True)
   if c.row==1:c.font=Font(name='微软雅黑',size=12,color='FFFFFF',bold=True);c.fill=PatternFill('solid',fgColor='173448')
   elif c.row%2==0:c.fill=PatternFill('solid',fgColor='F3F6F7')
   if c.data_type=='f':c.font=Font(name='微软雅黑',size=12,color='000000');c.comment=Comment('公式：输入齐全才计算；见本表C列边界说明。','Codex')
 for row in range(2,s.max_row+1):s.row_dimensions[row].height=60
 s.row_dimensions[1].height=32
 s.print_title_rows='1:1';s.sheet_properties.pageSetUpPr.fitToPage=True;s.page_setup.orientation='landscape';s.page_setup.paperSize=s.PAPERSIZE_A3;s.page_setup.fitToWidth=1;s.page_setup.fitToHeight=0
for sn,ranges in [('报价成本',['B2:B7','B11:B12']),('销量模型',['B2:B3','B5','B7:B8','B10:B11'])]:
 s=wb[sn]
 for rg in ranges:
  for row in s[rg] if ':' in rg else [(s[rg],)]:
   for c in row:c.font=Font(name='微软雅黑',size=12,color='0000FF');c.fill=PatternFill('solid',fgColor='FFF3CE')
validation=DataValidation(type='decimal',operator='between',formula1=0,formula2=1,allow_blank=True);validation.showErrorMessage=True;validation.error='填写0到1之间的真实同口径赢率；无数据请留空';wb['销量模型'].add_data_validation(validation);validation.add(wb['销量模型']['B3'])
file=out/'政府企业学校_90天销售行动台账.xlsx';wb.save(file)
print(file)