87 lines
2.3 KiB
Plaintext
87 lines
2.3 KiB
Plaintext
|
|
ns = params_kw.copy()
|
|
ns['page'] = ns.get('page') or 1
|
|
ns['sort'] = ns.get('sort') or 'acc_date'
|
|
|
|
date_start = ns.get('date_start', '')
|
|
date_end = ns.get('date_end', '')
|
|
supplier_id = ns.get('supplier_id', '')
|
|
recharge_status = ns.get('recharge_status', '')
|
|
|
|
acc_dbname = get_module_dbname('accounting')
|
|
sc_dbname = get_module_dbname('supplychain')
|
|
userorgid = await get_userorgid()
|
|
|
|
# 查询供应商充值记录: accounting_log中business_op='供应商充值'的记录
|
|
where = "WHERE al.business_op = '供应商充值'"
|
|
sql_params = {}
|
|
|
|
if date_start:
|
|
where += " AND al.acc_date >= ${date_start}$"
|
|
sql_params['date_start'] = date_start
|
|
if date_end:
|
|
where += " AND al.acc_date <= ${date_end}$"
|
|
sql_params['date_end'] = date_end
|
|
|
|
sql = f"""
|
|
SELECT
|
|
al.id,
|
|
al.accountid,
|
|
al.acc_date as recharge_date,
|
|
al.amount,
|
|
al.acc_dir,
|
|
al.summary,
|
|
al.business_op,
|
|
al.billid,
|
|
b.customerid as supplier_id,
|
|
sp.supplier_name as supplier_id_text,
|
|
CASE
|
|
WHEN al.billid IS NOT NULL AND al.billid != '' THEN '2'
|
|
WHEN al.amount > 0 THEN '1'
|
|
ELSE '0'
|
|
END as recharge_status,
|
|
al.created_at
|
|
FROM accounting_log al
|
|
LEFT JOIN bill b ON al.billid = b.id COLLATE utf8mb4_unicode_ci
|
|
LEFT JOIN (
|
|
SELECT id, supplier_name FROM suppliers
|
|
) sp ON b.customerid = sp.id COLLATE utf8mb4_unicode_ci
|
|
{where}
|
|
ORDER BY al.acc_date DESC, al.id
|
|
"""
|
|
|
|
if supplier_id:
|
|
sql = f"SELECT * FROM ({sql}) t WHERE t.supplier_id = ${supplier_id}$"
|
|
sql_params['supplier_id'] = supplier_id
|
|
if recharge_status:
|
|
sql = f"SELECT * FROM ({sql}) t WHERE t.recharge_status = ${recharge_status}$"
|
|
sql_params['recharge_status'] = recharge_status
|
|
|
|
db = DBPools()
|
|
async with db.sqlorContext(acc_dbname) as sor:
|
|
recs = await sor.sqlExe(sql, sql_params)
|
|
|
|
rows = []
|
|
for r in (recs or []):
|
|
rows.append({
|
|
'id': r.id,
|
|
'supplier_id': r.supplier_id or '',
|
|
'supplier_id_text': r.supplier_id_text or '',
|
|
'recharge_date': str(r.recharge_date or ''),
|
|
'amount': float(r.amount or 0),
|
|
'payment_method': r.summary or '',
|
|
'transaction_no': r.billid or '',
|
|
'recharge_status': r.recharge_status or '0',
|
|
'remark': r.summary or '',
|
|
'created_at': str(r.created_at or '')
|
|
})
|
|
|
|
total = len(rows)
|
|
debug(f'get_provider_recharge: {total} records')
|
|
|
|
return json.dumps({
|
|
'success': True,
|
|
'total': total,
|
|
'rows': rows
|
|
}, ensure_ascii=False, default=str)
|