Task #7152: Agrobank Housekeeping - Agrobank Projects - Penril Support System

Task #7152

Agrobank Housekeeping

Added by Ngoh Chee Ping over 7 years ago. Updated over 6 years ago.

Status:

Closed - End of life cycle

Start date:

February 15, 2017

Priority:

Normal

Due date:

February 22, 2017

Assignee:

Norhaidah Md Dasuki

% Done:

100%

Category:

Spent time:

Target version:

Description

Create a batch program to archive the old data from all the transaction history, user activity, audit log, and sms table to another database.
The number of days data to keep need to set in AppConfig that can be parameterize.

You may follow the lhbank housekeeping project as reference.
https://hub.penril.net/hg/LH/lh-data-purging/

script2.sql (14.7 KB) Stephanie Sufrapto, March 03, 2017 15:22

housekeepingerrors.txt (22.5 KB) Ngoh Chee Ping, March 06, 2017 09:25

scriptHousekeeping.sql (48.7 KB) Stephanie Sufrapto, March 10, 2017 15:56

History

#1 Updated by Stephanie Sufrapto over 7 years ago

Status changed from New - Begin Life Cycle to Development / Work In Progress

#2 Updated by Yap Kah Yan over 7 years ago

Hi Stephanie,

Please add the print receipt tables also for RIB and BIB

Thank you.

#3 Updated by Stephanie Sufrapto over 7 years ago

% Done changed from 0 to 100

Done developing and testing in local.

Will waiting for instruction to deployed to SIT

#4 Updated by Stephanie Sufrapto over 7 years ago

Status changed from Development / Work In Progress to Code Review
Assignee changed from Stephanie Sufrapto to Ngoh Chee Ping

Hi Chee ping,

Please review the code.
https://hub.penril.net/hg/agrobank/agro-housekeeping/

Thank you

#5 Updated by Ngoh Chee Ping over 7 years ago

Status changed from Code Review to Development / Work In Progress
Assignee changed from Ngoh Chee Ping to Stephanie Sufrapto
% Done changed from 100 to 50

Stephanie

Please take note on the below table that need to archive as well:

BIB_APPROVER_STATUS
BIB_BILL_PAYMENT
BIB_BULK_PAYMENT_HISTORY
BIB_BULK_REJECTED_TRNX
BIB_INTER_TRANSFER_HISTORY
BIB_INTRA_TRANSFER_HISTORY
BIB_MULTIPLE_PAYMENT_HISTORY
BIB_NBPS_PAYMENT
BIB_PAYROLL_HISTORY
BIB_SWEEPING_ACCT_RECIPIENTS_HISTORY
BIB_SWEEPING_ACCT_SENDER_HISTORY
BIB_SWEEPING_ACCT_TRNX_HISTORY
BIB_TRNX_APPROVER_RULES
IB_INTER_TRANSFER
IB_INTRA_TRANSFER
IB_MOBILE_RELOAD
IB_NBPS_PAYMENT

There is error when run the program
Start to remove old BIB Transaction History
[22/02/2017] [15:02:14:742] [ERROR] [JDBCExceptionReporter]:101 - The DELETE statement conflicted with the REFERENCE constraint "FK110720E17916BDC8". The conflict occurred in database "agroib_db_test", table "dbo.BIB_TRNX_HISTORY", column 'INITIATED_BY'.
[22/02/2017] [15:02:14:742] [ERROR] [JDBCExceptionReporter]:101 - The DELETE statement conflicted with the REFERENCE constraint "FK110720E17916BDC8". The conflict occurred in database "agroib_db_test", table "dbo.BIB_TRNX_HISTORY", column 'INITIATED_BY'.
[22/02/2017] [15:02:14:744] [ERROR] [AbstractFlushingEventListener]:324 - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: could not delete: [net.penril.bib.core.BibUser#36]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2569)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2725)

Does not pickup data below for purging
Retrieve Admin audit Log
Admin Audit Log List Size : 0
Retrieve BIB User Activity
BIB User Activity Log List Size : 0
Retrieve IB User Activity
IB User Activity Log List Size : 0
Retrieve BIB User Print Receipt
BIB User Print Receipt Log List Size : 0
Retrieve IB User Print Receipt
User Print Receipt Log List Size : 0
Retrieve SMS
SMS Log List Size : 0

#6 Updated by Stephanie Sufrapto over 7 years ago

File script2.sql added
Assignee changed from Stephanie Sufrapto to Ngoh Chee Ping
% Done changed from 50 to 100

Done coding.

Attached the query for additional table

#7 Updated by Ngoh Chee Ping over 7 years ago

File housekeepingerrors.txt added
Assignee changed from Ngoh Chee Ping to Stephanie Sufrapto
% Done changed from 100 to 90

The db script contain extra "[BIB_INTER_TRANSFER_HISTORY_ARC]". Please remove this.

The program contain error while delete the records. I attached the error log here. Please check about it.

#8 Updated by Stephanie Sufrapto over 7 years ago

Status changed from Development / Work In Progress to Finished Development
Assignee changed from Stephanie Sufrapto to Ngoh Chee Ping
% Done changed from 90 to 100

Need you to clear the data in table "BIB_MULTIPLE_PAYMENT_HISTORY"
Have confirmed with kahyan this table is old table and not used anymore. Cant find the connection of this table to BIB_TRNX_HISTORY.
But if you remove the data inside the error will be gone.

#9 Updated by Stephanie Sufrapto over 7 years ago

File scriptHousekeeping.sql added

Attached the latest query for housekeeping.

Thanks

#10 Updated by Ngoh Chee Ping over 7 years ago

Assignee changed from Ngoh Chee Ping to Stephanie Sufrapto

Now the program and DB script is correct already. But need to create a runnable jar to run on server. After create and tested, pass the batch program to illah.