search

bcgov / von-bc-registries-agent

Apache License 2.0
3 stars 17 forks source link

Audit report between BC Reg and OrgBook databases (for active credentials) #143

Closed ianco closed 5 years ago

ianco commented 5 years ago

Ensure we have the correct set of (active) credentials posted to OrgBook

ianco commented 5 years ago

This query provides a summary of company type and state in the BC Reg database, but it runs for over 7 minutes:

select corp.corp_typ_cd, op_state.op_state_typ_cd, count(*) from bc_registries.corporation corp, bc_registries.corp_state state, bc_registries.corp_op_state op_state, bc_registries.corp_type ctype where state.corp_num = corp.corp_num and state.end_event_id is null and op_state.state_typ_cd = state.state_typ_cd and corp.corp_typ_cd = ctype.corp_typ_cd group by corp.corp_typ_cd, op_state.op_state_typ_cd;

ianco commented 5 years ago

Sample output:

corp_typ_cd op_state_typ_cd count A ACT 30798 A HIS 77960 B ACT 1 B HIS 597 BC ACT 440808 BC HIS 744267 C ACT 4258 C HIS 5872 CC ACT 59 CC HIS 22 CEM ACT 23 CEM HIS 5 CP ACT 614 CP HIS 1672 CS ACT 8 CS HIS 1 CUL ACT 435 CUL HIS 368 EPR ACT 1 EPR HIS 314 FI ACT 43 FI HIS 99 FOR HIS 293 GP ACT 121114 GP HIS 7700 LIB ACT 37 LIB HIS 72 LIC HIS 615 LL ACT 1310 LL HIS 248 LLC ACT 540 LLC HIS 523 LP ACT 8995 LP HIS 2592 MF ACT 46 MF HIS 15 PA ACT 188 PA HIS 215 PAR ACT 73 PFS ACT 1 PFS HIS 1 QA ACT 1 QA HIS 64 QB HIS 129 QC HIS 150 QD ACT 1 QD HIS 847 QE ACT 3 QE HIS 3135 REG HIS 113 RLY ACT 7 RLY HIS 3 S ACT 27656 S HIS 42213 SB ACT 2 SP ACT 595436 SP HIS 37329 T HIS 166 TMY ACT 10 TMY HIS 1 ULC ACT 2675 ULC HIS 987 XCP ACT 26 XCP HIS 22 XL ACT 71 XL HIS 15 XP ACT 2217 XP HIS 1108 XS ACT 989 XS HIS 747

ianco commented 5 years ago

Query to get the same data from corp_history_log (from event processor database):

select corp_typ_cd, corp_state, count(*) from ( SELECT record_id, corp_num, corp_state, corp_json->>'corp_typ_cd' as corp_typ_cd, entry_date, process_date FROM corp_history_log ch1 WHERE record_id = (SELECT MAX(record_id) FROM corp_history_log ch2 WHERE ch1.corp_num = ch2.corp_num and process_date is not null) ORDER BY corp_num, record_id ) as foo group by corp_typ_cd, corp_state;

ianco commented 5 years ago

Sample output:

corp_typ_cd corp_state count A ACT 25 A HIS 18 B HIS 5 BC ACT 37 BC HIS 41 C ACT 5 C HIS 5 CC ACT 5 CP ACT 7 CP HIS 3 CS ACT 5 CUL ACT 4 CUL HIS 1 EPR HIS 5 FI ACT 2 FI HIS 3 FOR HIS 5 GP ACT 6 LIC HIS 5 LL ACT 6 LL HIS 2 LLC ACT 5 LLC HIS 2 LP ACT 8 MF ACT 5 MF HIS 2 PA ACT 8 PA HIS 1 PAR ACT 5 QA HIS 5 QB HIS 5 QC HIS 5 QD HIS 5 QE HIS 5 REG HIS 5 S ACT 7 S HIS 1 SP ACT 205 SP HIS 13 ULC ACT 8 ULC HIS 1 XCP ACT 5 XL ACT 5 XP ACT 6 XP HIS 1 XS ACT 7 XS HIS 1

ianco commented 5 years ago

Same query from credential_log:

select corp_typ_cd, corp_state, count(*) from ( SELECT record_id, corp_num, credential_json->>'entity_status' as corp_state, credential_json->>'entity_type' as corp_typ_cd, entry_date, process_date FROM credential_log cl1 WHERE record_id = (SELECT MAX(record_id) FROM credential_log cl2 WHERE cl1.corp_num = cl2.corp_num and process_date is not null and credential_type_cd = 'REG') ORDER BY corp_num, record_id ) as foo group by corp_typ_cd, corp_state;

ianco commented 5 years ago

... and from the OrgBook search database:

select corp_typ_cd, corp_state, count(*) from ( select credential_id, MAX(CASE WHEN claim_name='entity_type' THEN claim_value END) corp_typ_cd, MAX(CASE WHEN claim_name='entity_status' THEN claim_value END) corp_state from ( select claim.credential_id as credential_id, claim.name as claim_name, claim.value as claim_value from credential_set, credential, claim where credential_set.credential_type_id in (select id from credential_type where description = 'Registration') and credential.id = credential_set.latest_credential_id and claim.credential_id = credential.id and claim.name in ('registration_id', 'entity_type', 'entity_status')) as foo group by credential_id ) as foo group by corp_typ_cd, corp_state;

ianco commented 5 years ago

Sample output:

corp_typ_cd corp_state count QC HIS 5 LP ACT 8 BC HIS 35 GP ACT 6 QE HIS 5 CUL HIS 1 PAR ACT 5 XCP ACT 5 FOR HIS 5 MF ACT 5 LLC HIS 2 A ACT 25 FI HIS 3 B HIS 5 XS ACT 7 CS ACT 5 QD HIS 5 LL ACT 7 EPR HIS 5 CUL ACT 4 PA HIS 1 FI ACT 2 REG HIS 5 QB HIS 5 S HIS 1 CP ACT 7 XL ACT 5 LL HIS 1 CC ACT 5 S ACT 7 PA ACT 8 LIC HIS 5 XS HIS 1 A HIS 17 C HIS 5 ULC HIS 1 QA HIS 5 XP HIS 1 ULC ACT 8 CP HIS 3 C ACT 5 MF HIS 2 BC ACT 27 XP ACT 6 LLC ACT 5 SP ACT 217 SP HIS 1