update b set fees_other = convert(int,p.FLAT_CHG3)/100.0, total=convert(int,p.FLAT_CHG3)/100.0, balance_due=convert(int,p.FLAT_CHG3)/100.0, b.due_date=convert(date,p.due_date) --convert(date,RIGHT(p.DUE_DATE,4)+LEFT(p.DUE_DATE,2)+SUBSTRING(p.DUE_DATE,3,2) )
from dy2022.dbo.property a
inner join dy2022.dbo.bill b on b.PROPERTYKEY=a.PROPERTYKEY
inner join MCDUFFIE_GSI.dbo.PARCEL p on concat(p.map,p.parcel,p.SUBPARC)=a.PARCEL_NO and p.block<>'xxxxx' and p.BILL_YEAR=2022
inner join MCDUFFIE_GSI.dbo.CASH c on c.BILL_NO=p.BILL_NO and c.BILL_YEAR=p.BILL_YEAR and RECTYPE=1 and APPCODE=1
where exempt=1 --and PARCEL_no='0T310067000'

declare @paymentkey as int = (select max(paymentkey) from dy2022.dbo.payment)

insert into dy2022.dbo.payment (PAYMENTKEY, BILLKEY, TOTAL, PAYMENT_TYPE, PAID_BY, BALANCE_DUE, PAYMENTDATE, COMPNAME, LOGINID, REGISTER, FEES_OTHER, BILL_YEAR, BILL_NO, REVERSAL, PAID_AMT, BILL_TYPE, REFUND, UNPAID)
select @paymentkey + ROW_NUMBER() over( order by billkey ) as paymentkey 
	, b.BILLKEY
	, c.TAXAMT_7 *-1 as TOTAL
	, case when c.RECTYPE=5 then 'COLLECTION' when c.RECTYPE=6 then 'POSTING ERROR' end as paymenttype
	, c.PROPNAME as PAID_BY
	, 0 as BALANCE_DUE
	, TRY_CONVERT(DATETIME,RIGHT(c.POSTING_DATE,4)+LEFT(c.POSTING_DATE,2)+SUBSTRING(c.POSTING_DATE,3,2)
		+ ' ' + LEFT(c.TRANS_TIME,2)+':'+SUBSTRING(c.TRANS_TIME,3,2)+':'+SUBSTRING(c.TRANS_TIME,5,2))
		AS PAYMENT_DATE
	, HOST_NAME()
	, c.COMPNAME 
	, c.REGISTER
	, c.TAXAMT_7 *-1 as fees_other
	, b.BILL_YEAR, b.BILL_NO
	, 0 reversal
	, c.TAXAMT_7 *-1 as paid_amt
	, b.BILL_TYPE
	, 0 refund, 0 unpaid
from dy2022.dbo.property a
inner join dy2022.dbo.bill b on b.PROPERTYKEY=a.PROPERTYKEY
inner join MCDUFFIE_GSI.dbo.PARCEL p on concat(p.map,p.parcel,p.SUBPARC)=a.PARCEL_NO and p.block<>'xxxxx' and p.BILL_YEAR=2022
inner join MCDUFFIE_GSI.dbo.vCASH c on c.BILL_NO=p.BILL_NO and c.BILL_YEAR=p.BILL_YEAR and RECTYPE in (5,6) and APPCODE=1
where exempt=1 --and PARCEL_no='0T310067000'

update bu set bu.balance_due = vb.total_balance 
from dy2022.dbo.property a
inner join dy2022.dbo.bill bu on bu.PROPERTYKEY=a.PROPERTYKEY and a.EXEMPT=1 and bu.FEES_OTHER>0
inner join dy2022.dbo.vBILL_BALANCE vb on vb.BILL_NO=bu.BILL_NO and vb.BILL_YEAR=bu.BILL_YEAR 

insert into dy2022.dbo.TAXFEE_ITEMS (REALKEY, COMP_NO, DESCRIP, TAXTYPE, FEE_AMT)
select distinct a.realkey, 'FFEE', 'Fire Fee', 11, convert(int,p.FLAT_CHG3)/100.0
from dy2022.dbo.property a
inner join dy2022.dbo.bill b on b.PROPERTYKEY=a.PROPERTYKEY
inner join MCDUFFIE_GSI.dbo.PARCEL p on concat(p.map,p.parcel,p.SUBPARC)=a.PARCEL_NO and p.block<>'xxxxx' and p.BILL_YEAR=2022
inner join MCDUFFIE_GSI.dbo.CASH c on c.BILL_NO=p.BILL_NO and c.BILL_YEAR=p.BILL_YEAR and RECTYPE in (1) and APPCODE=1
where exempt=1 and convert(int,p.FLAT_CHG3)/100.0>0 --and PARCEL_no='0T310067000'

insert into dy2022.dbo.TAXDETAIL (REALKEY, TT_DESC, TAXTYPE, ESTTAX)
select distinct a.realkey, 'Fire Fee', 11, convert(int,p.FLAT_CHG3)/100.0
from dy2022.dbo.property a
inner join dy2022.dbo.bill b on b.PROPERTYKEY=a.PROPERTYKEY
inner join MCDUFFIE_GSI.dbo.PARCEL p on concat(p.map,p.parcel,p.SUBPARC)=a.PARCEL_NO and p.block<>'xxxxx' and p.BILL_YEAR=2022
inner join MCDUFFIE_GSI.dbo.CASH c on c.BILL_NO=p.BILL_NO and c.BILL_YEAR=p.BILL_YEAR and RECTYPE in (1) and APPCODE=1
where exempt=1 and convert(int,p.FLAT_CHG3)/100.0>0 --and PARCEL_no='0T310067000'