195 lines
8.4 KiB
Python
195 lines
8.4 KiB
Python
import requests
|
|
import time
|
|
import os
|
|
import datetime
|
|
import json
|
|
import urllib3
|
|
from openpyxl import Workbook, load_workbook
|
|
|
|
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
|
|
|
|
DETAIL_HEADERS = {
|
|
'Accept': 'application/json, text/plain, */*',
|
|
'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',
|
|
'Authorization': 'Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6ImEzck1VZ01Gdjl0UGNsTGE2eUYzekFrZnF1RSIsImtpZCI6ImEzck1VZ01Gdjl0UGNsTGE2eUYzekFrZnF1RSJ9.eyJpc3MiOiJodHRwczovL2F1dGgxLmhrczM2MC5jb20vY29yZSIsImF1ZCI6Imh0dHBzOi8vYXV0aDEuaGtzMzYwLmNvbS9jb3JlL3Jlc291cmNlcyIsImV4cCI6MTc4MDEyODg1MCwibmJmIjoxNzgwMTI1MjUwLCJjbGllbnRfaWQiOiJ6YS5jbGllbnQiLCJjbGllbnRfbG9jYXRpb24iOiJkYXRhY2VudGVyIiwic2NvcGUiOiJ3cml0ZSJ9.fiumtw9xCYWj-euP_LCQdrT9Wd4OsVopuHQpt3Qaae8En4lPA7aaqOfpXVF8gwxtoayfpjATtIaMomkUcnglYqZBCUTC50bc6IHYFgRrYl_7h4g9BCIHwGEswYbvFiQfAB5Q4gLFptzEJ1W2pjHnrNgmum5syQR3fsR5_25OayQ_KI6HWdtR3wReuInl0PQcDJs5jxdeId2ViDuYnl1x7TDFoIIwPov46H4KViUrBKFwr6iaTcNwrpl0thPBZjLJ8StTj50JwL1tRe71LbHkavD3MGsqs9_ulJaFZgyu2UYpl6cO0Let2zk9w-k2echh7P1ajQg7LfO2hEJ-c6RHXg',
|
|
'Content-Type': 'application/json;charset=utf-8',
|
|
'Connection': 'keep-alive',
|
|
'Origin': 'http://www.kuaixiuge.com',
|
|
'Proxy-Connection': 'keep-alive',
|
|
'Referer': 'http://www.kuaixiuge.com/',
|
|
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36 Edg/148.0.0.0',
|
|
}
|
|
|
|
MAX_DETAIL = 99999
|
|
|
|
OUTPUT_DIR = r'D:\Idea Project\F6+宜搭+其它(1)\张阳脚本\文件输出'
|
|
INPUT_FILE = os.path.join(OUTPUT_DIR, '快修哥历史维修记录_合并去重_无明细_20260530_140130.xlsx')
|
|
OUTPUT_FILE = os.path.join(OUTPUT_DIR, f'快修哥历史维修记录_含明细_{datetime.datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx')
|
|
PROGRESS_FILE = os.path.join(OUTPUT_DIR, os.path.basename(INPUT_FILE).replace('.xlsx', '_进度.json'))
|
|
|
|
SERVICE_COLUMNS = [
|
|
'serviceid', 'servicename', 'servicetype', 'servicenumber',
|
|
'serviceprice', 'servicehj', 'timefee', 'isinquiry', 'isinterimpurchase',
|
|
]
|
|
|
|
WXXM_COLUMNS = [
|
|
'xmdm', 'xmname', 'xmprice', 'hour',
|
|
]
|
|
|
|
PARTS_COLUMNS = [
|
|
'pcode', 'pname', 'price', 'count', 'amount', 'unit',
|
|
]
|
|
|
|
def fetch_detail(gch, cid='24574'):
|
|
params = {'cid': cid, 'gch': gch}
|
|
url = 'http://saas.hks360.com:84/WXinfoservice/GetAllServices'
|
|
|
|
try:
|
|
requests.options(url, params=params, headers=DETAIL_HEADERS, verify=False, timeout=10)
|
|
time.sleep(0.15)
|
|
resp = requests.get(url, params=params, headers=DETAIL_HEADERS, verify=False, timeout=15)
|
|
if resp.status_code == 200:
|
|
data = resp.json()
|
|
rows = (data.get('rows') or []) if isinstance(data, dict) else []
|
|
return rows, True
|
|
print(f" [X] HTTP {resp.status_code} gch={gch}")
|
|
return [], False
|
|
except Exception as e:
|
|
print(f" [X] 异常 gch={gch}, {e}")
|
|
return [], False
|
|
|
|
def parse_detail_rows(detail_rows):
|
|
result = []
|
|
for row in detail_rows:
|
|
service_info = {k: row.get(k) for k in SERVICE_COLUMNS}
|
|
|
|
wxxm_list = row.get('wxxmList', []) or []
|
|
parts_list = row.get('wxinfopartsList', []) or []
|
|
|
|
has_any = False
|
|
if wxxm_list:
|
|
has_any = True
|
|
for xm in wxxm_list:
|
|
merged = dict(service_info)
|
|
for k in WXXM_COLUMNS:
|
|
merged['xm_' + k] = xm.get(k)
|
|
result.append(merged)
|
|
if parts_list:
|
|
has_any = True
|
|
for part in parts_list:
|
|
merged = dict(service_info)
|
|
for k in PARTS_COLUMNS:
|
|
merged['part_' + k] = part.get(k)
|
|
result.append(merged)
|
|
if not has_any:
|
|
result.append(service_info)
|
|
|
|
return result
|
|
|
|
DETAIL_HEADER = (['明细_serviceid', '明细_服务名称', '明细_服务类型', '明细_服务数量',
|
|
'明细_服务单价', '明细_服务合计', '明细_工时费', '明细_是否询价', '明细_是否中间采购',
|
|
'明细_项目代码', '明细_项目名称', '明细_项目价格', '明细_项目工时',
|
|
'明细_配件编码', '明细_配件名称', '明细_配件单价', '明细_配件数量', '明细_配件金额', '明细_配件单位'])
|
|
|
|
DETAIL_XM_KEYS = ['serviceid', 'servicename', 'servicetype', 'servicenumber',
|
|
'serviceprice', 'servicehj', 'timefee', 'isinquiry', 'isinterimpurchase',
|
|
'xm_xmdm', 'xm_xmname', 'xm_xmprice', 'xm_hour']
|
|
DETAIL_PART_KEYS = ['part_pcode', 'part_pname', 'part_price', 'part_count', 'part_amount', 'part_unit']
|
|
DETAIL_OUTPUT_KEYS = DETAIL_XM_KEYS + DETAIL_PART_KEYS
|
|
|
|
def _save_xlsx(main_data, all_detail_rows, headers_row, output_path):
|
|
expanded_rows = []
|
|
for main_row, detail_items in zip(main_data, all_detail_rows):
|
|
if detail_items:
|
|
for d in detail_items:
|
|
row_vals = list(main_row) + [d.get(k, '') for k in DETAIL_OUTPUT_KEYS]
|
|
expanded_rows.append(row_vals)
|
|
else:
|
|
expanded_rows.append(list(main_row) + [''] * len(DETAIL_OUTPUT_KEYS))
|
|
|
|
out_wb = Workbook()
|
|
out_ws = out_wb.active
|
|
out_ws.title = '历史维修记录(含明细)'
|
|
out_ws.append(list(headers_row) + list(DETAIL_HEADER))
|
|
for row_vals in expanded_rows:
|
|
out_ws.append(row_vals)
|
|
for cell in out_ws['A']:
|
|
cell.number_format = '@'
|
|
out_ws.auto_filter.ref = out_ws.dimensions
|
|
out_wb.save(output_path)
|
|
print(f"数据已保存至 {output_path} ({len(expanded_rows)} 行)")
|
|
|
|
print(f"读取输入文件: {INPUT_FILE}")
|
|
wb = load_workbook(INPUT_FILE)
|
|
ws = wb.active
|
|
|
|
headers_row = [cell.value for cell in ws[1]]
|
|
main_data = []
|
|
gch_list = []
|
|
for row in ws.iter_rows(min_row=2, values_only=True):
|
|
row_list = list(row)
|
|
gch_str = str(int(row_list[0])) if isinstance(row_list[0], float) else str(row_list[0])
|
|
row_list[0] = gch_str
|
|
main_data.append(row_list)
|
|
gch_list.append(gch_str)
|
|
|
|
print(f"共 {len(gch_list)} 条工单待获取明细，本次取前 {MAX_DETAIL} 条")
|
|
|
|
all_detail_rows = [None] * len(gch_list)
|
|
fetch_count = min(len(gch_list), MAX_DETAIL)
|
|
start_idx = 0
|
|
|
|
if os.path.exists(PROGRESS_FILE):
|
|
with open(PROGRESS_FILE, 'r', encoding='utf-8') as f:
|
|
saved = json.load(f)
|
|
saved_data = saved.get('detail_data', [])
|
|
if len(saved_data) == len(gch_list):
|
|
for idx, item in enumerate(saved_data):
|
|
if idx < len(all_detail_rows) and item is not None:
|
|
all_detail_rows[idx] = item
|
|
start_idx = saved.get('next_idx', 0)
|
|
else:
|
|
print(f"[!] 进度文件与输入不匹配 (进度{len(saved_data)}条, 输入{len(gch_list)}条), 从头开始")
|
|
if start_idx >= fetch_count:
|
|
print(f"[!] 已完成全部 {fetch_count} 条, 无需重新请求")
|
|
else:
|
|
print(f"[断点续传] 从第 {start_idx + 1}/{fetch_count} 条继续 (已完成 {start_idx} 条)")
|
|
|
|
consecutive_fails = 0
|
|
SAVE_ON_FAILS = 10
|
|
SLEEP_SEC = 0.8
|
|
|
|
def save_progress():
|
|
os.makedirs(OUTPUT_DIR, exist_ok=True)
|
|
with open(PROGRESS_FILE, 'w', encoding='utf-8') as f:
|
|
json.dump({'next_idx': i + 1, 'detail_data': all_detail_rows}, f, ensure_ascii=False)
|
|
|
|
for i in range(start_idx, fetch_count):
|
|
gch = gch_list[i]
|
|
if (i + 1) % 10 == 0 or i == fetch_count - 1:
|
|
print(f"正在获取明细 {i + 1}/{fetch_count} ... (连续失败: {consecutive_fails})")
|
|
time.sleep(SLEEP_SEC)
|
|
raw, ok = fetch_detail(gch)
|
|
all_detail_rows[i] = parse_detail_rows(raw)
|
|
|
|
if not ok:
|
|
consecutive_fails += 1
|
|
if consecutive_fails >= SAVE_ON_FAILS:
|
|
print(f"\n[!!] 连续失败 {consecutive_fails} 次，保存已获取数据后终止 (下次从第 {i - consecutive_fails + 1} 条继续)")
|
|
_save_xlsx(main_data, all_detail_rows, headers_row, OUTPUT_FILE)
|
|
raise SystemExit(1)
|
|
else:
|
|
consecutive_fails = 0
|
|
save_progress()
|
|
|
|
print("\n全部完成! 正在最终保存...")
|
|
with_detail = sum(1 for d in all_detail_rows if d)
|
|
empty_detail = sum(1 for d in all_detail_rows if d is not None and len(d) == 0)
|
|
none_detail = sum(1 for d in all_detail_rows if d is None)
|
|
total_detail_rows = sum(len(d) for d in all_detail_rows if d)
|
|
print(f" 有明细={with_detail}条, 无明细(空)={empty_detail}条, 未请求={none_detail}条, 明细行={total_detail_rows}")
|
|
_save_xlsx(main_data, all_detail_rows, headers_row, OUTPUT_FILE)
|
|
if os.path.exists(PROGRESS_FILE):
|
|
os.remove(PROGRESS_FILE)
|
|
print("进度文件已清理")
|