import io
import base64
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment

userid = await get_user()
userorgid = await get_userorgid()

start_date = params_kw.get('start_date', '')
end_date = params_kw.get('end_date', '')

if not start_date or not end_date:
	return json.dumps({'status': 'error', 'data': {'message': '缺少日期参数'}}, ensure_ascii=False)

ns = {
	'orgid': userorgid,
	'start_date': start_date,
	'end_date': end_date
}

async with get_sor_context(request._run_ns, 'accounting') as sor:
	sql = """select d.acc_date, d.acc_timestamp, s.name as subject_name, d.acc_dir, 
d.summary, d.amount, d.balance
from acc_detail d
join account a on d.accountid = a.id COLLATE utf8mb4_unicode_ci
join subject s on a.subjectid = s.id COLLATE utf8mb4_unicode_ci
where a.orgid = ${orgid}$
	and d.acc_date >= ${start_date}$
	and d.acc_date <= ${end_date}$
order by d.acc_date desc"""
	recs = await sor.sqlExe(sql, ns)
	
	# 生成 xlsx
	wb = Workbook()
	ws = wb.active
	ws.title = '账单明细'
	
	# 表头
	headers = ['日期', '时间', '科目', '方向', '摘要', '金额', '余额']
	ws.append(headers)
	header_font = Font(bold=True)
	for cell in ws[1]:
		cell.font = header_font
		cell.alignment = Alignment(horizontal='center')
	
	# 数据
	for rec in recs:
		direction = '借' if rec.acc_dir == '1' else '贷'
		ws.append([
			str(rec.acc_date),
			str(rec.acc_timestamp),
			rec.subject_name,
			direction,
			rec.summary,
			float(rec.amount),
			float(rec.balance)
		])
	
	# 调整列宽
	ws.column_dimensions['A'].width = 12
	ws.column_dimensions['B'].width = 20
	ws.column_dimensions['C'].width = 15
	ws.column_dimensions['D'].width = 8
	ws.column_dimensions['E'].width = 40
	ws.column_dimensions['F'].width = 15
	ws.column_dimensions['G'].width = 15
	
	# 保存到内存
	output = io.BytesIO()
	wb.save(output)
	output.seek(0)
	
	# Base64 编码
	b64_data = base64.b64encode(output.read()).decode('utf-8')
	filename = f'账单明细_{start_date}_{end_date}.xlsx'
	
	return json.dumps({
		'status': 'ok',
		'data': {
			'filename': filename,
			'content': b64_data
		}
	}, ensure_ascii=False)