/*
create table CORELOGIC (
	[RECID] [int] IDENTITY(1,1) NOT NULL,
	RECORD_TYPE TINYINT NULL,
	LENDOR_CODE VARCHAR(20) NULL,
	ESCROW CHAR(1) NULL,
	PARCEL_NO VARCHAR(20) NULL,
	PAYMENT_AMT NUMERIC(12,2) NULL,
	TAX_YEAR SMALLINT NULL,
	BILL_NO BIGINT NULL
	)
*/

/*

--drop table CORELOGIC_IMPORT
create table CORELOGIC_IMPORT (
	RAWDATA VARCHAR(100) NULL
	)
*/
/*
TRUNCATE TABLE CORELOGIC_IMPORT 
BULK INSERT CORELOGIC_IMPORT
FROM '\\tigh\tco\GATCS\imports\DY2022\Lereta\Lereta-3.txt'
WITH
(
    FIRSTROW = 1,
    FIELDTERMINATOR = ',',  --CSV field delimiter -- '\t' = tab
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)
*/

/*
SELECT SUBSTRING(RAWDATA,2,6) AS NUMBER_RECS
	, SUBSTRING(RAWDATA,8,20) AS LENDOR_CODE
	, SUBSTRING(RAWDATA,28,18) AS CHECK_NO 
	, CONVERT(NUMERIC(12,2),SUBSTRING(RAWDATA,46,12))/100.0 AS CHECK_AMT
	FROM CORELOGIC_IMPORT WHERE LEFT(RAWDATA,1)=0
*/

truncate table corelogic
insert into CORELOGIC (RECORD_TYPE, LENDOR_CODE, ESCROW, PARCEL_NO, PAYMENT_AMT, TAX_YEAR, BILL_NO)
SELECT [recordtype]
      ,[lendorcode]
      ,[escrow]
      ,[parcel_no]
      ,[payment]
      ,[tax_year]
      ,[bill_no]
  FROM [tcsbulk].[dbo].corelogic20231120
  order by bill_no

/*
select 
	SUBSTRING(RAWDATA,1,1) as RECORD_TYPE 
	, SUBSTRING(RAWDATA,2,20) as LENDOR_CODE
	, SUBSTRING(RAWDATA,22,1) as ESCROW
	, SUBSTRING(RAWDATA,23,20) as PARCEL_NO
	, CONVERT(NUMERIC(12,2),SUBSTRING(RAWDATA,43,12))/100.0 as PAYMENT_AMT
	, REPLACE(SUBSTRING(RAWDATA,55,4),'R','') as TAX_YEAR
	, SUBSTRING(RAWDATA,59,12) as BILL_NO
FROM CORELOGIC_IMPORT
WHERE LEFT(RAWDATA,1)=1
*/

/* ********************************************************************
		CLEAN UP FROM MISSED PAYMENT 2/17

select b.BILL_NO, b.INTEREST, b.COMMENTS, b.BALANCE_DUE, b.TOTAL, b.BALANCE_DUE-b.INTEREST as CALC_BAL, b.TOTAL-b.INTEREST as CALC_TOT, cl.PAYMENT_AMT
-- update b set b.INTEREST=0, b.balance_due=b.balance_due-b.interest, b.total=b.total-b.interest, b.COMMENTS=rtrim(b.comments)+'; '+char(13)+char(10)+'Payment-Check was received 2/17, interest should not have been applied, interest rolled off 3/31'
from bill b 
inner join property p on p.PROPERTYKEY=b.PROPERTYKEY
inner join CORELOGIC cl on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ',''))

select p.PROPERTYKEY, p.COMMENTS
-- update p set p.COMMENTS=rtrim(p.comments)+'; '+char(13)+char(10)+'Payment-Check was received 2/17, interest should not have been applied, interest rolled off 3/31'
from property p 
inner join CORELOGIC cl on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ',''))

delete from interest_rp where recid in (
select i.recid
from INTEREST_RP i
inner join bill b on b.BILL_NO=i.BILL_NO and b.BILL_YEAR=i.BILL_YEAR
inner join property p on p.PROPERTYKEY=b.PROPERTYKEY
inner join CORELOGIC cl on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ',''))
)

*************************************************************************************/

/*		SINGLE TRANSACTIONS		*/

select 'EXEC dbo.spLOCAL_SINGLETRANSACTION_0 '''',' 
	+ CONVERT(CHAR(4),2023 )+', ' 
	+ CONVERT(VARCHAR(12),b.BILL_NO)+ ', ' 
	+ convert(varchar(12),payment_amt)  + ', '' '' , '''+CONVERT(VARCHAR(23),'20231127 09:00:00:000',21)+''', 999, ''Corelogic Tax Service'', '''', ''GATCS'', ''CHECK'', ''20231127'', 0,' 
	+ convert(varchar(12),case 
							when cl.PAYMENT_AMT > vb.TOTAL_BALANCE then 0
							when vb.TOTAL_BALANCE=b.total then b.TOTAL-cl.PAYMENT_AMT 
							when vb.TOTAL_BALANCE>cl.payment_amt then vb.TOTAL_BALANCE-cl.PAYMENT_AMT 
							else 0 
							end)+', '  -- balance_due
	+ convert(varchar(12),case 
							when vb.TOTAL_BALANCE = 0 then cl.PAYMENT_AMT-vb.TOTAL_BALANCE 
							when vb.TOTAL_BALANCE < payment_amt then abs(vb.TOTAL_BALANCE-payment_amt)
							when payment_amt > vb.TOTAL_BALANCE then payment_amt - vb.TOTAL_BALANCE
							else 0 
							end)+', '  -- overage
	+ convert(varchar(12),payment_amt) 
	+ ', ''' + ltrim(rtrim(B.BILL_TYPE)) + ''' '
	AS LONG_SP_EXPRESSION
	, CL.PARCEL_NO, B.TOTAL, B.BILL_NO, vb.TOTAL_BALANCE, payment_amt--, cl.PARCEL_NO, p.PARCEL_NO, rtrim(replace(cl.PARCEL_NO,' ','')) NEWPIN
	, (select sum(paid_amt) from payment where BILL_NO=b.BILL_NO) as paid_amt_ytd
FROM CORELOGIC CL
inner JOIN BILL B ON b.BILL_NO = cl.BILL_NO and b.BILL_YEAR=2023
inner join PROPERTY p on p.PROPERTYKEY=b.PROPERTYKEY
inner join vBILL_BALANCE vb on vb.BILL_NO=b.BILL_NO and vb.BILL_YEAR=b.BILL_YEAR AND VB.BILL_TYPE=B.BILL_TYPE
where cl.TAX_YEAR=2023
order by b.BILL_NO

/*
insert into GOVWIN_BATCH (PROPERTYKEY, I_U, BILL_NO, BILL_YEAR, TRANSMIT)
select b.PROPERTYKEY, 'U', b.BILL_NO, b.BILL_YEAR, 0 
FROM CORELOGIC CL
inner join PROPERTY p on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ',''))
inner JOIN BILL B ON b.PROPERTYKEY = P.PROPERTYKEY
inner join vBILL_BALANCE vb on vb.BILL_NO=b.BILL_NO and vb.BILL_YEAR=b.BILL_YEAR
*/


/***************************************************************************************************************************/

truncate table corelogic
insert into CORELOGIC (RECORD_TYPE, LENDOR_CODE, ESCROW, PARCEL_NO, PAYMENT_AMT, TAX_YEAR, BILL_NO)
SELECT [recordtype]
      ,[lendorcode]
      ,[escrow]
      ,[parcel_no]
      ,[payment]
      ,[tax_year]
      ,[bill_no]
  FROM [tcsbulk].[dbo].[corelogicT09354568]

select distinct 'EXEC dbo.spLOCAL_SINGLETRANSACTION_0 '''',' 
	+ CONVERT(CHAR(4),2022 )+', ' 
	+ CONVERT(VARCHAR(12),b.BILL_NO)+ ', ' 
	+ convert(varchar(12),payment_amt)  + ', '' '' , '''+CONVERT(VARCHAR(23),'20221027 20:00:00:000',21)+''', 991, ''CoreLogic'', '''', ''GATCS'', ''CHECK'', ''09354568'', 0,' 
	+ convert(varchar(12),case 
							when vb.TOTAL_BALANCE=b.total then b.TOTAL-cl.PAYMENT_AMT 
							when vb.TOTAL_BALANCE>cl.payment_amt then vb.TOTAL_BALANCE-cl.PAYMENT_AMT 
							else 0 
							end)+', '  -- balance_due
	+ convert(varchar(12),case 
							when vb.TOTAL_BALANCE = 0 then cl.PAYMENT_AMT-vb.TOTAL_BALANCE 
							when vb.TOTAL_BALANCE < payment_amt then abs(vb.TOTAL_BALANCE-payment_amt)
							when payment_amt > vb.TOTAL_BALANCE then payment_amt - vb.TOTAL_BALANCE
							else 0 
							end)+', '  -- overage
	+ convert(varchar(12),payment_amt) 
	+ ', ''' + ltrim(rtrim(B.BILL_TYPE)) + ''' '
	AS LONG_SP_EXPRESSION
	, CL.PARCEL_NO, B.TOTAL, B.BILL_NO, vb.TOTAL_BALANCE, payment_amt--, cl.PARCEL_NO, p.PARCEL_NO, rtrim(replace(cl.PARCEL_NO,' ','')) NEWPIN
	, (select sum(paid_amt) from payment where BILL_NO=b.BILL_NO) as paid_amt_ytd
FROM CORELOGIC CL
inner JOIN BILL B ON b.BILL_NO = cl.BILL_NO and b.BILL_YEAR=2022
inner join PROPERTY p on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ','')) and p.propertykey=b.propertykey
inner join vBILL_BALANCE vb on vb.BILL_NO=b.BILL_NO and vb.BILL_YEAR=b.BILL_YEAR AND VB.BILL_TYPE=B.BILL_TYPE
order by b.BILL_NO

/*
insert into GOVWIN_BATCH (PROPERTYKEY, I_U, BILL_NO, BILL_YEAR, TRANSMIT)
select distinct b.PROPERTYKEY, 'U', b.BILL_NO, b.BILL_YEAR, 0 
FROM CORELOGIC CL
inner join PROPERTY p on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ',''))
inner JOIN BILL B ON b.PROPERTYKEY = P.PROPERTYKEY
inner join vBILL_BALANCE vb on vb.BILL_NO=b.BILL_NO and vb.BILL_YEAR=b.BILL_YEAR
order by b.BILL_NO

*/

/***************************************************************************************************************************/

truncate table corelogic
insert into CORELOGIC (RECORD_TYPE, LENDOR_CODE, ESCROW, PARCEL_NO, PAYMENT_AMT, TAX_YEAR, BILL_NO)
SELECT [recordtype]
      ,[lendorcode]
      ,[escrow]
      ,[parcel_no]
      ,[payment]
      ,[tax_year]
      ,[bill_no]
  FROM [tcsbulk].[dbo].[corelogicT09492272]

select 'EXEC dbo.spLOCAL_SINGLETRANSACTION_0 '''',' 
	+ CONVERT(CHAR(4),2022 )+', ' 
	+ CONVERT(VARCHAR(12),b.BILL_NO)+ ', ' 
	+ convert(varchar(12),payment_amt)  + ', '' '' , '''+CONVERT(VARCHAR(23),'20221027 20:00:00:000',21)+''', 992, ''CoreLogic'', '''', ''GATCS'', ''CHECK'', ''09492272'', 0,' 
	+ convert(varchar(12),case 
							when vb.TOTAL_BALANCE=b.total then b.TOTAL-cl.PAYMENT_AMT 
							when vb.TOTAL_BALANCE>cl.payment_amt then vb.TOTAL_BALANCE-cl.PAYMENT_AMT 
							else 0 
							end)+', '  -- balance_due
	+ convert(varchar(12),case 
							when vb.TOTAL_BALANCE = 0 then cl.PAYMENT_AMT-vb.TOTAL_BALANCE 
							when vb.TOTAL_BALANCE < payment_amt then abs(vb.TOTAL_BALANCE-payment_amt)
							when payment_amt > vb.TOTAL_BALANCE then payment_amt - vb.TOTAL_BALANCE
							else 0 
							end)+', '  -- overage
	+ convert(varchar(12),payment_amt) 
	+ ', ''' + ltrim(rtrim(B.BILL_TYPE)) + ''' '
	AS LONG_SP_EXPRESSION
	, CL.PARCEL_NO, B.TOTAL, B.BILL_NO, vb.TOTAL_BALANCE, payment_amt--, cl.PARCEL_NO, p.PARCEL_NO, rtrim(replace(cl.PARCEL_NO,' ','')) NEWPIN
	, (select sum(paid_amt) from payment where BILL_NO=b.BILL_NO) as paid_amt_ytd
FROM CORELOGIC CL
inner JOIN BILL B ON b.BILL_NO = cl.BILL_NO and b.BILL_YEAR=2022
inner join PROPERTY p on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ',''))
inner join vBILL_BALANCE vb on vb.BILL_NO=b.BILL_NO and vb.BILL_YEAR=b.BILL_YEAR AND VB.BILL_TYPE=B.BILL_TYPE
order by b.BILL_NO

/*
insert into GOVWIN_BATCH (PROPERTYKEY, I_U, BILL_NO, BILL_YEAR, TRANSMIT)
select b.PROPERTYKEY, 'U', b.BILL_NO, b.BILL_YEAR, 0 
FROM CORELOGIC CL
inner join PROPERTY p on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ',''))
inner JOIN BILL B ON b.PROPERTYKEY = P.PROPERTYKEY
inner join vBILL_BALANCE vb on vb.BILL_NO=b.BILL_NO and vb.BILL_YEAR=b.BILL_YEAR
*/

/***************************************************************************************************************************/

truncate table corelogic
insert into CORELOGIC (RECORD_TYPE, LENDOR_CODE, ESCROW, PARCEL_NO, PAYMENT_AMT, TAX_YEAR, BILL_NO)
SELECT [recordtype]
      ,[lendorcode]
      ,[escrow]
      ,[parcel_no]
      ,[payment]
      ,[tax_year]
      ,[bill_no]
  FROM [tcsbulk].[dbo].[invh2022_pay]

select 'EXEC dbo.spLOCAL_SINGLETRANSACTION_0 '''',' 
	+ CONVERT(CHAR(4),2022 )+', ' 
	+ CONVERT(VARCHAR(12),b.BILL_NO)+ ', ' 
	+ convert(varchar(12),payment_amt)  + ', '' '' , '''+CONVERT(VARCHAR(23),'20221027 20:00:00:000',21)+''', 993, ''INVH'', '''', ''GATCS'', ''CHECK'', ''00000930'', 0,' 
	+ convert(varchar(12),case 
							when vb.TOTAL_BALANCE=b.total then b.TOTAL-cl.PAYMENT_AMT 
							when vb.TOTAL_BALANCE>cl.payment_amt then vb.TOTAL_BALANCE-cl.PAYMENT_AMT 
							else 0 
							end)+', '  -- balance_due
	+ convert(varchar(12),case 
							when vb.TOTAL_BALANCE = 0 then cl.PAYMENT_AMT-vb.TOTAL_BALANCE 
							when vb.TOTAL_BALANCE < payment_amt then abs(vb.TOTAL_BALANCE-payment_amt)
							when payment_amt > vb.TOTAL_BALANCE then payment_amt - vb.TOTAL_BALANCE
							else 0 
							end)+', '  -- overage
	+ convert(varchar(12),payment_amt) 
	+ ', ''' + ltrim(rtrim(B.BILL_TYPE)) + ''' '
	AS LONG_SP_EXPRESSION
	, CL.PARCEL_NO, B.TOTAL, B.BILL_NO, vb.TOTAL_BALANCE, payment_amt--, cl.PARCEL_NO, p.PARCEL_NO, rtrim(replace(cl.PARCEL_NO,' ','')) NEWPIN
	, (select sum(paid_amt) from payment where BILL_NO=b.BILL_NO) as paid_amt_ytd
FROM CORELOGIC CL
inner JOIN BILL B ON b.BILL_NO = cl.BILL_NO and b.BILL_YEAR=2022
inner join PROPERTY p on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ',''))
inner join vBILL_BALANCE vb on vb.BILL_NO=b.BILL_NO and vb.BILL_YEAR=b.BILL_YEAR AND VB.BILL_TYPE=B.BILL_TYPE
order by b.BILL_NO

/*
insert into GOVWIN_BATCH (PROPERTYKEY, I_U, BILL_NO, BILL_YEAR, TRANSMIT)
select distinct b.PROPERTYKEY, 'U', b.BILL_NO, b.BILL_YEAR, 0 
FROM CORELOGIC CL
inner join PROPERTY p on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ',''))
inner JOIN BILL B ON b.PROPERTYKEY = P.PROPERTYKEY
inner join vBILL_BALANCE vb on vb.BILL_NO=b.BILL_NO and vb.BILL_YEAR=b.BILL_YEAR
*/

/***************************************************************************************************************************/

truncate table corelogic
insert into CORELOGIC (RECORD_TYPE, LENDOR_CODE, ESCROW, PARCEL_NO, PAYMENT_AMT, TAX_YEAR, BILL_NO)
SELECT [recordtype]
      ,[lendorcode]
      ,[escrow]
      ,[parcel_no]
      ,[payment]
      ,[tax_year]
      ,[bill_no]
  FROM [tcsbulk].[dbo].[corelogic_pay4_with_dupbillno]
  where parcel_no='0027F00000007000'

select 'EXEC dbo.spLOCAL_SINGLETRANSACTION_0 '''',' 
	+ CONVERT(CHAR(4),2022 )+', ' 
	+ CONVERT(VARCHAR(12),b.BILL_NO)+ ', ' 
	+ convert(varchar(12),payment_amt)  + ', '' '' , '''+CONVERT(VARCHAR(23),'20221030 20:00:00:000',21)+''', 990, ''CoreLogic'', '''', ''GATCS'', ''CHECK'', ''40078732'', 0,' 
	+ convert(varchar(12),case 
							when vb.TOTAL_BALANCE=b.total then b.TOTAL-cl.PAYMENT_AMT 
							when vb.TOTAL_BALANCE>cl.payment_amt then vb.TOTAL_BALANCE-cl.PAYMENT_AMT 
							else 0 
							end)+', '  -- balance_due
	+ convert(varchar(12),case 
							when vb.TOTAL_BALANCE = 0 then cl.PAYMENT_AMT-vb.TOTAL_BALANCE 
							when vb.TOTAL_BALANCE < payment_amt then abs(vb.TOTAL_BALANCE-payment_amt)
							when payment_amt > vb.TOTAL_BALANCE then payment_amt - vb.TOTAL_BALANCE
							else 0 
							end)+', '  -- overage
	+ convert(varchar(12),payment_amt) 
	+ ', ''' + ltrim(rtrim(B.BILL_TYPE)) + ''' '
	AS LONG_SP_EXPRESSION
	, CL.PARCEL_NO, B.TOTAL, B.BILL_NO, vb.TOTAL_BALANCE, payment_amt--, cl.PARCEL_NO, p.PARCEL_NO, rtrim(replace(cl.PARCEL_NO,' ','')) NEWPIN
	, (select sum(paid_amt) from payment where BILL_NO=b.BILL_NO) as paid_amt_ytd
FROM CORELOGIC CL
inner JOIN BILL B ON b.BILL_NO = cl.BILL_NO and b.BILL_YEAR=2022
inner join PROPERTY p on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ',''))
inner join vBILL_BALANCE vb on vb.BILL_NO=b.BILL_NO and vb.BILL_YEAR=b.BILL_YEAR AND VB.BILL_TYPE=B.BILL_TYPE
order by b.BILL_NO

/*
insert into GOVWIN_BATCH (PROPERTYKEY, I_U, BILL_NO, BILL_YEAR, TRANSMIT)
select distinct b.PROPERTYKEY, 'U', b.BILL_NO, b.BILL_YEAR, 0 
FROM CORELOGIC CL
inner join PROPERTY p on rtrim(p.PARCEL_NO)=rtrim(replace(replace(cl.PARCEL_NO,'-',''),' ',''))
inner JOIN BILL B ON b.PROPERTYKEY = P.PROPERTYKEY
inner join vBILL_BALANCE vb on vb.BILL_NO=b.BILL_NO and vb.BILL_YEAR=b.BILL_YEAR
order by b.BILL_NO

*/