133 lines
4.2 KiB
Python
133 lines
4.2 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
I列类目覆盖检查器 — 建表后跑，报警但不阻断。
|
|
检查I列是否覆盖了足够多的固定类目，不够就报警提示人工核查。
|
|
|
|
用法: python3 i-column-coverage-check.py <xlsx_file>
|
|
|
|
规则：
|
|
租赁合同行：21个固定类目，≥15个不报警，<15个报警提示核查
|
|
物业合同行：15个固定类目，≥10个不报警，<10个报警提示核查
|
|
能耗协议/其他：不检查
|
|
|
|
报警≠阻断：有些合同确实没这么多类目，报警只是提醒逐一确认"是真没有还是漏了"。
|
|
"""
|
|
import sys, re
|
|
import openpyxl
|
|
|
|
LEASE_CATEGORIES = [
|
|
'用途', '转租', '装修改造', '广告标识', '非竞争', '维修责任', '保险要求',
|
|
'物业服务联动', '配套设施', '出租方变更', '解除权机制', '违约金机制',
|
|
'不可抗力', '征收拆迁', '房屋抵押查封', '政策变化', '到期处理',
|
|
'恢复原状', '优先权', '管辖', '备案'
|
|
]
|
|
|
|
PROPERTY_CATEGORIES = [
|
|
'物业服务内容', '服务标准', '公共能耗费', '特约服务', '共用设施管理',
|
|
'装修管理', '安保措施', '消防安全', '保险要求', '联动终止',
|
|
'违约责任', '退出交接', '免责条款', '不可抗力', '管辖'
|
|
]
|
|
|
|
LEASE_THRESHOLD = 15
|
|
PROPERTY_THRESHOLD = 10
|
|
|
|
|
|
def check_row(row_num, cell_value, contract_type):
|
|
"""检查一行I列的类目覆盖情况"""
|
|
if not cell_value:
|
|
return None
|
|
|
|
text = str(cell_value)
|
|
|
|
if contract_type == 'lease':
|
|
categories = LEASE_CATEGORIES
|
|
threshold = LEASE_THRESHOLD
|
|
type_name = '租赁合同'
|
|
elif contract_type == 'property':
|
|
categories = PROPERTY_CATEGORIES
|
|
threshold = PROPERTY_THRESHOLD
|
|
type_name = '物业合同'
|
|
else:
|
|
return None
|
|
|
|
found = []
|
|
missing = []
|
|
for cat in categories:
|
|
# 检查类目是否出现在文本中（允许【】或[]包裹）
|
|
if re.search(rf'[·\-\[\【]{cat}[\]\】]?', text) or cat in text:
|
|
found.append(cat)
|
|
else:
|
|
missing.append(cat)
|
|
|
|
coverage = len(found)
|
|
total = len(categories)
|
|
|
|
result = {
|
|
'row': row_num,
|
|
'type': type_name,
|
|
'coverage': coverage,
|
|
'total': total,
|
|
'threshold': threshold,
|
|
'found': found,
|
|
'missing': missing,
|
|
'alert': coverage < threshold
|
|
}
|
|
return result
|
|
|
|
|
|
def main():
|
|
if len(sys.argv) < 2:
|
|
print("用法: python3 i-column-coverage-check.py <xlsx_file>")
|
|
sys.exit(1)
|
|
|
|
filepath = sys.argv[1]
|
|
wb = openpyxl.load_workbook(filepath)
|
|
ws = wb.active
|
|
|
|
results = []
|
|
for row in range(1, ws.max_row + 1):
|
|
# 判断合同类型（C列）
|
|
c_val = str(ws.cell(row, 3).value or '').strip()
|
|
i_val = ws.cell(row, 9).value
|
|
|
|
if not i_val or not c_val:
|
|
continue
|
|
|
|
if '租赁' in c_val and '物业' not in c_val:
|
|
contract_type = 'lease'
|
|
elif '物业' in c_val:
|
|
contract_type = 'property'
|
|
else:
|
|
continue # 能耗协议等不检查
|
|
|
|
result = check_row(row, i_val, contract_type)
|
|
if result:
|
|
results.append(result)
|
|
|
|
if not results:
|
|
print("⚠️ 未找到租赁/物业合同行，请确认文件结构。")
|
|
sys.exit(0)
|
|
|
|
all_pass = True
|
|
for r in results:
|
|
status = '✅' if not r['alert'] else '⚠️'
|
|
if r['alert']:
|
|
all_pass = False
|
|
print(f"{status} Row {r['row']} [{r['type']}]: {r['coverage']}/{r['total']} 类目"
|
|
f"（阈值{r['threshold']}）")
|
|
if r['alert']:
|
|
print(f" 缺失类目: {', '.join(r['missing'])}")
|
|
print(f" → 请逐一核查：是合同确实没有，还是提取时漏了？")
|
|
print()
|
|
|
|
if all_pass:
|
|
print(f"\n✅ I列类目覆盖检查通过（{len(results)}行全部达标）。")
|
|
else:
|
|
alert_count = sum(1 for r in results if r['alert'])
|
|
print(f"\n⚠️ {alert_count}行I列类目覆盖不足，请核查确认。")
|
|
print(" 说明：报警≠错误。有些合同确实没这么多类目，核查确认即可。")
|
|
|
|
|
|
if __name__ == '__main__':
|
|
main()
|