-- all
SELECT c_order.em_obpos_app_cashup_id, c_invoice.created, fin_payment_schedule.fin_payment_schedule_id, c_invoice.documentno, c_order.documentno,fin_payment_schedule.amount as expectedamount,fin_payment_schedule.paidamt as receivedamount,c_invoice.ispaid as paymentcomplete,c_invoice.grandtotal,c_invoice.totalpaid,c_invoice.outstandingamt,c_invoice.finalsettlement
FROM c_invoice
INNER JOIN c_invoiceline ON c_invoice.c_invoice_id = c_invoiceline.c_invoice_id
LEFT JOIN fin_payment_schedule ON c_invoice.c_invoice_id = fin_payment_schedule.c_invoice_id
INNER JOIN c_orderline ON c_invoiceline.c_orderline_id = c_orderline.c_orderline_id
INNER JOIN c_order ON c_orderline.c_order_id = c_order.c_order_id
WHERE c_order.em_obpos_app_cashup_id IS NOT null
ORDER BY c_invoice.documentno DESC

-- show all with amount > 0 and ispaid = 'N'
SELECT c_order.em_obpos_app_cashup_id, c_invoice.created, fin_payment_schedule.fin_payment_schedule_id, c_invoice.documentno, c_order.documentno,fin_payment_schedule.amount,fin_payment_schedule.paidamt,c_invoice.ispaid,c_invoice.grandtotal,c_invoice.totalpaid,c_invoice.outstandingamt,c_invoice.finalsettlement
FROM c_invoice
INNER JOIN c_invoiceline ON c_invoice.c_invoice_id = c_invoiceline.c_invoice_id
LEFT JOIN fin_payment_schedule ON c_invoice.c_invoice_id = fin_payment_schedule.c_invoice_id
INNER JOIN c_orderline ON c_invoiceline.c_orderline_id = c_orderline.c_orderline_id
INNER JOIN c_order ON c_orderline.c_order_id = c_order.c_order_id
WHERE c_order.em_obpos_app_cashup_id IS NOT null AND fin_payment_schedule.fin_payment_schedule_id IS NOT NULL AND c_invoice.ispaid = 'N'
ORDER BY c_invoice.documentno DESC

-- fix with amount > 0 (fin_payment_schedule)
UPDATE fin_payment_schedule AS a
SET paidamt = amount
WHERE a.c_invoice_id IN (
SELECT c_invoice.c_invoice_id
FROM c_invoice
INNER JOIN c_invoiceline ON c_invoice.c_invoice_id = c_invoiceline.c_invoice_id
LEFT JOIN fin_payment_schedule ON c_invoice.c_invoice_id = fin_payment_schedule.c_invoice_id
INNER JOIN c_orderline ON c_invoiceline.c_orderline_id = c_orderline.c_orderline_id
INNER JOIN c_order ON c_orderline.c_order_id = c_order.c_order_id
WHERE c_order.em_obpos_app_cashup_id IS NOT null AND fin_payment_schedule.fin_payment_schedule_id IS NOT NULL AND c_invoice.ispaid = 'N'
)

-- fix with amount > 0 (c_invoice)
UPDATE c_invoice AS a
SET 	totalpaid = grandtotal,
	outstandingamt = 0,
	finalsettlement = created,
	ispaid = 'Y'
WHERE a.c_invoice_id IN (
SELECT c_invoice.c_invoice_id
FROM c_invoice
INNER JOIN c_invoiceline ON c_invoice.c_invoice_id = c_invoiceline.c_invoice_id
LEFT JOIN fin_payment_schedule ON c_invoice.c_invoice_id = fin_payment_schedule.c_invoice_id
INNER JOIN c_orderline ON c_invoiceline.c_orderline_id = c_orderline.c_orderline_id
INNER JOIN c_order ON c_orderline.c_order_id = c_order.c_order_id
WHERE c_order.em_obpos_app_cashup_id IS NOT null AND fin_payment_schedule.fin_payment_schedule_id IS NOT NULL AND c_invoice.ispaid = 'N'
)


-- show all with totalpaid - outstandingamount != 0 AND ispaid = 'N'
SELECT c_order.em_obpos_app_cashup_id, c_invoice.created, fin_payment_schedule.fin_payment_schedule_id, c_invoice.documentno, c_order.documentno,fin_payment_schedule.amount as expectedamount,fin_payment_schedule.paidamt as receivedamount,c_invoice.ispaid as paymentcomplete,c_invoice.grandtotal,c_invoice.totalpaid,c_invoice.outstandingamt,c_invoice.finalsettlement
FROM c_invoice
INNER JOIN c_invoiceline ON c_invoice.c_invoice_id = c_invoiceline.c_invoice_id
LEFT JOIN fin_payment_schedule ON c_invoice.c_invoice_id = fin_payment_schedule.c_invoice_id
INNER JOIN c_orderline ON c_invoiceline.c_orderline_id = c_orderline.c_orderline_id
INNER JOIN c_order ON c_orderline.c_order_id = c_order.c_order_id
WHERE c_order.em_obpos_app_cashup_id IS NOT null AND fin_payment_schedule.fin_payment_schedule_id IS NULL AND (c_invoice.totalpaid + c_invoice.outstandingamt = 0) AND c_invoice.ispaid = 'N'
ORDER BY c_invoice.documentno DESC


-- fix with totalpaid - outstandingamount != 0 AND ispaid = 'N' (c_invoice)
UPDATE c_invoice AS a
SET 	totalpaid = 0,
	outstandingamt = 0,
	finalsettlement = created,
	ispaid = 'Y'
WHERE a.c_invoice_id IN (
SELECT c_invoice.c_invoice_id
FROM c_invoice
INNER JOIN c_invoiceline ON c_invoice.c_invoice_id = c_invoiceline.c_invoice_id
LEFT JOIN fin_payment_schedule ON c_invoice.c_invoice_id = fin_payment_schedule.c_invoice_id
INNER JOIN c_orderline ON c_invoiceline.c_orderline_id = c_orderline.c_orderline_id
INNER JOIN c_order ON c_orderline.c_order_id = c_order.c_order_id
WHERE c_order.em_obpos_app_cashup_id IS NOT null AND fin_payment_schedule.fin_payment_schedule_id IS NULL AND (c_invoice.totalpaid + c_invoice.outstandingamt = 0) AND c_invoice.ispaid = 'N'
)