NRO Decommission: Create file to send to NUANS from NAMEX (replaces current process in NAMESP)

ozamani9gh commented 1 month ago

There are two batch jobs that read the NAMESP database daily and send a file to NUANS. They are very similar, one for CORPs and one for FIRMs. These will have to be re-created to read the Postgres NAMEX database. They select approved names that are not consumed and not expired.

There is an existing batch job, created by Stephen Chen, that selects Sole Props and General Partnerships from LEAR and copies them to pimento where they are merged with other NUANS data and FTPed to NUANS.

Description Environment Timing Format Type Intermediary Step Final Recpients

NUANS OpenShift 2am tue to sat sftp SP/GP sftp to bcrsmgr@pimento/export/home/bcrsmgr/nuans_from_openshift.txt and ftped as a separate file NUANS FTP site

Select rpad('BC9'|| substr(r.NR_NUM,4,6) || -- missing the last digit '0'|| to_char(er.EVENT_TIMESTAMP, 'YYYYMMDD')|| substr(r.NR_NUM,1,2)|| substr(rt.CLASS_TYPE_CD,1,1)|| substr(nst.short_desc,1,3)|| to_char(en.EVENT_TIMESTAMP, 'YYYYMMDD')|| rpad(nvl(a.city,' '),40)|| 'BC'|| ni.NAME,528) From Request R, Request_state RS, Request_instance RI, Request_party RP, Request_type RT, Name N, Name_state NS, Name_instance NI, Name_state_type NST, Event ER, Event EN, address@GLOBAL_READONLY.BCGOV A Where r.REQUEST_ID = rs.REQUEST_ID and rs.END_EVENT_ID is null and rs.STATE_TYPE_CD = 'COMPLETED' and rs.REQUEST_ID = ri.REQUEST_ID and ri.end_event_id is null and ri.REQUEST_TYPE_CD = rt.REQUEST_TYPE_CD and rs.REQUEST_ID = n.REQUEST_ID and n.NAME_ID = ns.NAME_ID and ns.END_EVENT_ID is null and ns.NAME_STATE_TYPE_CD in ('A','C') –- approved or conditionally approved and ns.NAME_STATE_TYPE_CD = nst.NAME_STATE_TYPE_CD and ns.NAME_ID = ni.NAME_ID and ni.end_event_id is null and ni.corp_num is null -- not consumed and rs.START_EVENT_ID = er.EVENT_ID and ns.START_EVENT_ID = en.EVENT_ID and r.REQUEST_ID = RI.REQUEST_ID and trunc(ri.EXPIRATION_DATE)>trunc(sysdate) –- not expired and rt.class_type_cd = 'FIRM' and rp.request_id = r.request_id and rp.address_id=a.addr_id and rp.end_event_id is null and n.NAME_ID = ns.NAME_ID and rp.REQUEST_ID = n.REQUEST_ID and rp.EMAIL_ADDRESS NOT LIKE '%eborah.%ollier@gov.bc.ca' and rp.EMAIL_ADDRESS <> 'catherine.carlson@gov.bc.ca' and rp.party_id = (select max(party_id) from request_party where request_id=r.request_id and end_event_id is null)

Select rpad('BC9'|| substr(r.NR_NUM,4,6) || -- missing the last digit '0'|| to_char(er.EVENT_TIMESTAMP, 'YYYYMMDD')|| substr(r.NR_NUM,1,2)|| substr(rt.CLASS_TYPE_CD,1,1)|| substr(nst.short_desc,1,3)|| to_char(en.EVENT_TIMESTAMP, 'YYYYMMDD')|| rpad(nvl(a.city,' '),40)|| 'BC'|| ni.NAME,528) From Request R, Request_state RS, Request_instance RI, Request_party RP, Request_type RT, Name N, Name_state NS, Name_instance NI, Name_state_type NST, Event ER, Event EN, address@GLOBAL_READONLY.BCGOV A Where r.REQUEST_ID = rs.REQUEST_ID and rs.END_EVENT_ID is null and rs.STATE_TYPE_CD = 'COMPLETED' and rs.REQUEST_ID = ri.REQUEST_ID and ri.end_event_id is null and ri.REQUEST_TYPE_CD = rt.REQUEST_TYPE_CD and rs.REQUEST_ID = n.REQUEST_ID and n.NAME_ID = ns.NAME_ID and ns.END_EVENT_ID is null and ns.NAME_STATE_TYPE_CD in ('A','C') –- approved or conditionally approved and ns.NAME_STATE_TYPE_CD = nst.NAME_STATE_TYPE_CD and ns.NAME_ID = ni.NAME_ID and ni.end_event_id is null and ni.corp_num is null -- not consumed and rs.START_EVENT_ID = er.EVENT_ID and ns.START_EVENT_ID = en.EVENT_ID and r.REQUEST_ID = RI.REQUEST_ID and trunc(ri.EXPIRATION_DATE)>trunc(sysdate) –- not expired and rt.class_type_cd = 'CORP' and rp.request_id = r.request_id and rp.address_id=a.addr_id and rp.end_event_id is null and n.NAME_ID = ns.NAME_ID and rp.REQUEST_ID = n.REQUEST_ID and rp.EMAIL_ADDRESS NOT LIKE '%eborah.%ollier@gov.bc.ca' and rp.EMAIL_ADDRESS <> 'catherine.carlson@gov.bc.ca' and rp.party_id = (select max(party_id) from request_party where request_id=r.request_id and end_event_id is null)

David Roberts Senior Database Administrator – BC Registries and Online Services Service BC Ministry of Citizens’ Services T: 778-698-1403 | Web: http://www.servicebc.gov.bc.ca “Access to government services made easy”

ozamani9gh commented 1 month ago

from @droberts27 :

This is the query we need to implement. two of them, one with CR and one with FR:

Hi Omid – Here is the SQL for the Corporations batch job. For the Firms batch job change entity_type from CR to FR.

select rpad ( 'BC9' ||substr(r.NR_NUM,4,6) -- missing the last digit ||'0' ||to_char(r.last_update at time zone 'America/Vancouver','yyyymmdd') -- request date ||'NR' ||substr(r.entity_type_cd,1,1) -- C are Corporations F are Firms ||initcap(substr(n.state,1,3)) -- name state ||to_char(e.event_dt at time zone 'America/Vancouver','yyyymmdd') -- name date ||rpad(coalesce(a.city,' '),40) ||'BC' ||n.NAME ,528 ) from requests r ,names n ,applicants a ,events e where r.id =n.nr_id and n.nr_id=a.nr_id and n.nr_id=e.nr_id and e.event_dt=(select max(e2.event_dt) from events e2 where e2.nr_id=n.nr_id) and r.entity_type_cd='CR' -- CR are Corporations FR are Firms and r.state_cd in ('APPROVED','CONDITIONAL','DRAFT','COMPLETED','INPROGRESS') and n.state in ('APPROVED','CONDITION')

eve-git commented 3 weeks ago

After comparing the two result sets in Oracle and PostgreSQL, the following mismatches were found:

Expiration date mismatch for 'REAPPLY': Only PostgreSQL has been updated, while Oracle has not. For example, NR 027269%.
Expiration date discrepancy: The expiration_date in Oracle is later than in PostgreSQL. For example, NR 0518473.
Expired NR discrepancy: The NR has expired in PostgreSQL but not in the Oracle dataset. For example, NR 0408455.

eve-git commented 3 weeks ago

I would like to make the query like the following since if the name state is 'APPROVED'/'CONDITION' the nr state is 'APPROVED'/'CONDITIONAL'. Also, according to request_type table in oracle, there are mappings for request type to class type 'CORP'/'FIRM'. I've include the mapping in the code.

select DISTINCT ON (r.nr_num)
        r.nr_num,
        to_char(r.last_update at time zone 'America/Vancouver', 'YYYYMMDD') last_update,
        r.request_type_cd,
        n.state,
        to_char(e.event_dt at time zone 'America/Vancouver', 'YYYYMMDD') event_dt,
        a.city,
        n.name
from requests r, events e, names n, applicants a
where r.id = e.nr_id
and r.id = n.nr_id
and a.nr_id = r.id
and r.state_cd in ('APPROVED', 'CONDITIONAL')
and r.expiration_date::date > CURRENT_DATE
and n.corp_num is null
and n.state in ('APPROVED', 'CONDITION')
and e.action = 'patch' and e.state_cd in ('APPROVED', 'CONDITIONAL')
ORDER BY 
    r.nr_num, e.event_dt DESC

eve-git commented 3 weeks ago

@droberts27 I got questions:

what is the file name that recognized by NUANS?
is it ok to combine Firm and Corp in one file?

droberts27 commented 3 weeks ago

We need 2 separate files

droberts27 commented 3 weeks ago

Timing Filename Type 1am tue to sat bccn_mr_yyyymmdd_nnnn_reserved corp NR 1am tue to sat bcbn_mr_yyyymmdd_nnnn_reserved firm NR

yyyymmdd is the current date nnnn is the row count

droberts27 commented 3 weeks ago

Siddharth and Stephen Chen (back on Monday) know how to copy from openshift to pimento.bcgov. The file will go in the home directory for bcrsmgr /export/home/bcrsmgr. Let me know and I will write the code to read it from there and sftp it to NUANS.

stevenc987 commented 2 weeks ago

@ozamani9gh @eve-git In order to make sftp work, a pair of private key and public key needs to be generated so these keys will be used for communication between server (openshift) and and Pimento (the server the file will be sent to). The keys will be in the config file.

eve-git commented 2 weeks ago

@ozamani9gh Testing is currently blocked due to a Pimento SFTP connection failure. Should we place the ticket on hold, or can someone be contacted to resolve the connection issue?

droberts27 commented 2 weeks ago

Connectivity from Openshift to pimento already exists. We sftp data from LEAR to pimento for ICBC and NUANS:

eve-git commented 2 weeks ago

I tested it locally, and it passed without errors. However, it appears to have transferred historical data as well (all files in the folder). @stevenc987 Should we remove the files generated on the day after the file transfer?

...
...
2024-11-14 15:59:05,314 - paramiko.transport.sftp - DEBUG in sftp:sftp.py:169 - _log: [chan 0] stat(b'bccn_mr_20241113_4668_reserved.gz')
2024-11-14 15:59:05,345 - root - INFO in ftp_processor:ftp_processor.py:33 - process_ftp: SFTP to NUANS completed for file: /home/evedeng/projects/namex/jobs/sftp-nuans-report/data/bccn_mr_20241113_4668_reserved.gz
2024-11-14 15:59:05,345 - paramiko.transport.sftp - DEBUG in sftp:sftp.py:169 - _log: [chan 0] open(b'bccn_mr_20241114_4606_reserved.gz', 'wb')
2024-11-14 15:59:05,373 - paramiko.transport.sftp - DEBUG in sftp:sftp.py:169 - _log: [chan 0] open(b'bccn_mr_20241114_4606_reserved.gz', 'wb') -> 00000000
2024-11-14 15:59:05,374 - paramiko.transport.sftp - DEBUG in sftp:sftp.py:169 - _log: [chan 0] close(00000000)
2024-11-14 15:59:05,446 - paramiko.transport.sftp - DEBUG in sftp:sftp.py:169 - _log: [chan 0] stat(b'bccn_mr_20241114_4606_reserved.gz')
2024-11-14 15:59:05,475 - root - INFO in ftp_processor:ftp_processor.py:33 - process_ftp: SFTP to NUANS completed for file: /home/evedeng/projects/namex/jobs/sftp-nuans-report/data/bccn_mr_20241114_4606_reserved.gz
2024-11-14 15:59:05,475 - paramiko.transport.sftp - INFO in sftp:sftp.py:169 - _log: [chan 0] sftp session closed.
2024-11-14 15:59:05,475 - paramiko.transport - DEBUG in channel:channel.py:1210 - _log: [chan 0] EOF sent (0)
2024-11-14 15:59:05,476 - root - INFO in sftpnuans:sftpnuans.py:101 - <module>: job - jupyter notebook report completed in: 0:00:10.254448

David approved that the data collected is good.

I don’t have a personal Pimento account to verify the transferred files on the server. Could someone assist with this?

Otherwise, I think it’s ready for deployment to PROD.

stevenc987 commented 2 weeks ago

@eve-git @ozamani9gh You don't have to remove the files generated on the day after the file transfer because these files are generated in different jobs. I am ok to deploy it to Prod without deploying it on Devl and Test as long as you can make it work on prod.

eve-git commented 1 week ago

comments from David R..pdf tested by David R..pdf

eve-git commented 1 week ago

Please find the attached files containing the testing details from David R. Could you let me know if the tests are sufficient? Additionally, please let me know if I can assist in any way.

bcgov / entity

NRO Decommission: Create file to send to NUANS from NAMEX (replaces current process in NAMESP) #24077