88 lines
2.1 KiB
Plaintext
88 lines
2.1 KiB
Plaintext
|
|
ns = params_kw.copy()
|
|
ns['page'] = ns.get('page') or 1
|
|
ns['sort'] = ns.get('sort') or 'sale_date'
|
|
|
|
date_start = ns.get('date_start', '')
|
|
date_end = ns.get('date_end', '')
|
|
supplier_id = ns.get('supplier_id', '')
|
|
|
|
sc_dbname = get_module_dbname('supplychain')
|
|
acc_dbname = get_module_dbname('accounting')
|
|
|
|
# 我方逐笔: supplychain_accounting (供应商维度看supply_amount)
|
|
sc_where = "WHERE 1=1"
|
|
sc_params = {}
|
|
if date_start:
|
|
sc_where += " AND sa.sale_date >= ${date_start}$"
|
|
sc_params['date_start'] = date_start
|
|
if date_end:
|
|
sc_where += " AND sa.sale_date <= ${date_end}$"
|
|
sc_params['date_end'] = date_end
|
|
if supplier_id:
|
|
sc_where += " AND sa.supplier_id = ${supplier_id}$"
|
|
sc_params['supplier_id'] = supplier_id
|
|
|
|
sc_sql = f"""
|
|
SELECT
|
|
sa.id,
|
|
sa.sale_date,
|
|
sa.productid,
|
|
p.name as product_name,
|
|
sa.supplier_id,
|
|
sp.supplier_name,
|
|
sa.quantity,
|
|
sa.unit_price,
|
|
sa.supply_discount,
|
|
sa.supply_amount,
|
|
sa.dist_amount,
|
|
sa.source_type,
|
|
sa.source_id
|
|
FROM supplychain_accounting sa
|
|
LEFT JOIN product p ON sa.productid = p.id COLLATE utf8mb4_unicode_ci
|
|
LEFT JOIN suppliers sp ON sa.supplier_id = sp.id COLLATE utf8mb4_unicode_ci
|
|
{sc_where}
|
|
ORDER BY sa.sale_date DESC, sa.id
|
|
"""
|
|
|
|
db = DBPools()
|
|
rows = []
|
|
|
|
async with db.sqlorContext(sc_dbname) as sc_sor:
|
|
sc_recs = await sc_sor.sqlExe(sc_sql, sc_params)
|
|
|
|
for r in (sc_recs or []):
|
|
rid = r.id
|
|
rows.append({
|
|
'id': rid,
|
|
'record_type': 'supplychain',
|
|
'sale_date': str(r.sale_date or ''),
|
|
'product_name': r.product_name or '',
|
|
'counterparty': r.supplier_name or '',
|
|
'quantity': float(r.quantity or 0),
|
|
'unit_price': float(r.unit_price or 0),
|
|
'our_amount': float(r.supply_amount or 0),
|
|
'their_amount': 0,
|
|
'diff_amount': float(r.supply_amount or 0),
|
|
'match_status': '0',
|
|
'source_id': r.source_id or '',
|
|
'source_type': r.source_type or ''
|
|
})
|
|
|
|
total = len(rows)
|
|
debug(f'get_provider_detail_reconcile: {total} records')
|
|
|
|
return json.dumps({
|
|
'success': True,
|
|
'total': total,
|
|
'rows': rows,
|
|
'summary': {
|
|
'our_count': total,
|
|
'our_total': sum(r['our_amount'] for r in rows),
|
|
'matched_count': 0,
|
|
'unmatched_count': total,
|
|
'their_count': 0,
|
|
'their_total': 0
|
|
}
|
|
}, ensure_ascii=False, default=str)
|