BillPaymentDetails-investigate.sql

select 
    br.billnumber, 
    br.billdate,
    br.billamount as grossAmount ,
    br.billstatus,
    brm.narration
from eg_billregister br
join eg_billregistermis brm
on brm.billid = br.id
where br.billnumber='ADM/EJV/0011/2019-20';

select sum(bd.creditamount) as netAmount
from 
    eg_billregister br
    join eg_billdetails bd on br.id = bd.billid
    join chartofaccounts coa on coa.id = bd.glcodeid
    join eg_appconfig_values cvalues on cvalues.value = cast(coa.purposeid as text)
    join eg_appconfig config on config.id = cvalues.key_id
where 
    br.billnumber='ADM/EJV/0011/2019-20'
    and config.key_name = 'worksBillPurposeIds' 
    and bd.creditamount > 0;

select vh.vouchernumber, vh.voucherdate, case vh.status when 5 then 'CREATED' when 4 then 'CANCELLED' when 0 then 'APPROVED' end as voucherStatus from voucherheader vh join eg_billregistermis egbmis on egbmis.voucherheaderid = vh.id join eg_billregister egbireg on egbireg.id = egbmis.billid where egbireg.billnumber='ADM/EJV/0011/2019-20';


SELECT
	vh.vouchernumber,
	vh.voucherdate,
	CASE vh.status
	WHEN 5 THEN
		'CREATED'
	WHEN 4 THEN
		'CANCELLED'
	WHEN 0 THEN
		'APPROVED'
	END AS voucherStatus,
	paymentamount,
	ih.instrumentnumber chequeNumber,
	ih.instrumentdate chequeDate,
	ih.instrumentamount chequeAmount
FROM
	miscbilldetail mbd
	JOIN voucherheader vh ON vh.id = mbd.payvhid
	LEFT JOIN paymentheader ph ON ph.voucherheaderid = vh.id
	LEFT JOIN egf_instrumentvoucher iv ON iv.voucherheaderid = vh.id
	LEFT JOIN egf_instrumentheader ih ON ih.id = iv.instrumentheaderid
	LEFT JOIN egf_instrumenttype it ON it.id = ih.instrumenttype
		AND it.type = 'cheque'
WHERE
	billnumber = 'ADM/EJV/0011/2019-20';

SELECT billnumber from eg_billregister where id = 4015;