122 lines
2.9 KiB
Plaintext
122 lines
2.9 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', '')
|
|
sub_reseller_id = ns.get('sub_reseller_id', '')
|
|
|
|
sc_dbname = get_module_dbname('supplychain')
|
|
acc_dbname = get_module_dbname('accounting')
|
|
|
|
# 我方逐笔: supplychain_accounting
|
|
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 sub_reseller_id:
|
|
sc_where += " AND sa.sub_distributor_id = ${sub_reseller_id}$"
|
|
sc_params['sub_reseller_id'] = sub_reseller_id
|
|
|
|
sc_sql = f"""
|
|
SELECT
|
|
sa.id,
|
|
sa.sale_date,
|
|
sa.productid,
|
|
p.name as product_name,
|
|
sa.sub_distributor_id,
|
|
sr.sub_reseller_name as sub_distributor_name,
|
|
sa.quantity,
|
|
sa.unit_price,
|
|
sa.dist_discount,
|
|
sa.dist_amount,
|
|
sa.supply_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 sub_resellers sr ON sa.sub_distributor_id = sr.id COLLATE utf8mb4_unicode_ci
|
|
{sc_where}
|
|
ORDER BY sa.sale_date DESC, sa.id
|
|
"""
|
|
|
|
# 我方账务逐笔: acc_detail关联bill
|
|
acc_where = "WHERE 1=1"
|
|
acc_params = {}
|
|
if date_start:
|
|
acc_where += " AND ad.acc_date >= ${date_start}$"
|
|
acc_params['date_start'] = date_start
|
|
if date_end:
|
|
acc_where += " AND ad.acc_date <= ${date_end}$"
|
|
acc_params['date_end'] = date_end
|
|
|
|
acc_sql = f"""
|
|
SELECT
|
|
ad.id,
|
|
ad.acc_date as sale_date,
|
|
ad.acclogid,
|
|
ad.acc_dir,
|
|
ad.amount,
|
|
ad.summary,
|
|
bd.subjectname,
|
|
bd.participantid,
|
|
bd.participanttype,
|
|
b.id as billid,
|
|
b.business_op,
|
|
b.resellerid
|
|
FROM acc_detail ad
|
|
LEFT JOIN bill_detail bd ON ad.acclogid = bd.id COLLATE utf8mb4_unicode_ci
|
|
LEFT JOIN bill b ON bd.billid = b.id COLLATE utf8mb4_unicode_ci
|
|
{acc_where}
|
|
ORDER BY ad.acc_date DESC, ad.id
|
|
"""
|
|
|
|
db = DBPools()
|
|
rows = []
|
|
|
|
async with db.sqlorContext(sc_dbname) as sc_sor:
|
|
sc_recs = await sc_sor.sqlExe(sc_sql, sc_params)
|
|
|
|
# 构建我方交易明细
|
|
our_records = {}
|
|
for r in (sc_recs or []):
|
|
rid = r.id
|
|
our_records[rid] = {
|
|
'id': rid,
|
|
'record_type': 'supplychain',
|
|
'sale_date': str(r.sale_date or ''),
|
|
'product_name': r.product_name or '',
|
|
'counterparty': r.sub_distributor_name or '',
|
|
'quantity': float(r.quantity or 0),
|
|
'unit_price': float(r.unit_price or 0),
|
|
'our_amount': float(r.dist_amount or 0),
|
|
'their_amount': 0,
|
|
'diff_amount': float(r.dist_amount or 0),
|
|
'match_status': '0',
|
|
'source_id': r.source_id or '',
|
|
'source_type': r.source_type or ''
|
|
}
|
|
rows.append(our_records[rid])
|
|
|
|
total = len(rows)
|
|
debug(f'get_reseller_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)
|