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 'sale_date'
|
|
|
|
date_start = ns.get('date_start', '')
|
|
date_end = ns.get('date_end', '')
|
|
supplier_id = ns.get('supplier_id', '')
|
|
settlement_status = ns.get('settlement_status', '')
|
|
|
|
sc_dbname = get_module_dbname('supplychain')
|
|
userorgid = await get_userorgid()
|
|
|
|
where = "WHERE sa.resellerid = ${userorgid}$"
|
|
sql_params = {'userorgid': userorgid}
|
|
|
|
if date_start:
|
|
where += " AND sa.sale_date >= ${date_start}$"
|
|
sql_params['date_start'] = date_start
|
|
if date_end:
|
|
where += " AND sa.sale_date <= ${date_end}$"
|
|
sql_params['date_end'] = date_end
|
|
if supplier_id:
|
|
where += " AND sa.supplier_id = ${supplier_id}$"
|
|
sql_params['supplier_id'] = supplier_id
|
|
|
|
sql = f"""
|
|
SELECT
|
|
sa.id,
|
|
sa.supplier_id,
|
|
sp.supplier_name as supplier_id_text,
|
|
sa.sale_date,
|
|
sa.sale_date as settlement_period,
|
|
p.name as product_name,
|
|
sa.productid,
|
|
sa.quantity,
|
|
sa.supply_amount as amount,
|
|
sa.dist_amount,
|
|
COALESCE(sl.settlement_status, '0') as settlement_status,
|
|
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
|
|
LEFT JOIN sales_ledger sl ON sl.productid = sa.productid COLLATE utf8mb4_unicode_ci
|
|
AND sl.supplier_id = sa.supplier_id COLLATE utf8mb4_unicode_ci
|
|
AND sl.sale_date = sa.sale_date
|
|
{where}
|
|
ORDER BY sa.sale_date DESC, sa.id
|
|
"""
|
|
|
|
if settlement_status:
|
|
sql = f"""
|
|
SELECT * FROM ({sql}) t WHERE t.settlement_status = ${settlement_status}$
|
|
"""
|
|
sql_params['settlement_status'] = settlement_status
|
|
|
|
db = DBPools()
|
|
async with db.sqlorContext(sc_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 '',
|
|
'settlement_period': str(r.sale_date or ''),
|
|
'product_name': r.product_name or '',
|
|
'amount': float(r.amount or 0),
|
|
'settlement_status': r.settlement_status or '0',
|
|
'settlement_date': '',
|
|
'payment_method': '',
|
|
'reference_no': r.source_id or '',
|
|
'remark': r.source_type or '',
|
|
'created_at': str(r.sale_date or '')
|
|
})
|
|
|
|
total = len(rows)
|
|
debug(f'get_provider_settlement: {total} records')
|
|
|
|
return json.dumps({
|
|
'success': True,
|
|
'total': total,
|
|
'rows': rows
|
|
}, ensure_ascii=False, default=str)
|