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)