Editing Imms (section)

==Technical information==

Data store is the <code>kpt</code> database, under <code>imms</code> schema. A single table exists for each vaccine file; <code>extract date</code> is used to separate and identify the latest data in each table.

The <code>imms.fn_imms_merge()</code> function executes daily at 1300 UTC via cron and is used to:
* Insert a list of unique identifiers across all new data into a reporting table (<code>imms.imms_all</code>)
* Update each row from new data to flag vaccine status separately for each vaccine, as well as update demographic/contact information.

Rationale for above is that there is often overlap of patient groups between vaccine data sets (especially childhood imms, but less frequently also the Covid data) and we need to see any combination of vaccines a person requires.

This function has a check on it to make sure it only executes when there is new data in underlying tables:

<syntaxhighlight lang="sql" line>
IF (select max(extract_date) from imms.imms_all) = (select max(extract_date) from imms.two_mmr) THEN
		RETURN 'No update needed EXITING'; --stop execution here if test is TRUE
	ELSE
		BEGIN --begin/end block to hold all the merge logic
			RAISE NOTICE 'Doing a merge'; --just for info
</syntaxhighlight>

===ELT Process===

Overview of current process shown below:

<mermaid>
%%{init: {'theme': 'default', 'flowchart': {'useMaxWidth': false,'htmlLabels': false}, 'logLevel': 'debug', 'securityLevel': 'loose', 'themeVariables': {'fontSize': '10px', 'nodeSpacing': 20, 'rankSpacing': 20}} }%%
flowchart TB
    
    subgraph EB0["📅 EventBridge"]
        direction TB
        EB1["cron(30 18 ? * MON,TUE *)"]:::mono-.->
        EB2[Sync_Imms_Files_Weekly]:::mono
    end
    subgraph L0["⚙️ Lambda"]
        direction TB
        L1[syncImmsFileSftp]:::mono
        subgraph S30["📁 s3://kpa-imms"]
            direction TB
            S31[Check for new files]-.->
            S32[Convert to parquet]-.->
            S33[Move to folder path <code> parquet/ </code>]
        end
        subgraph TW0["🔒 SFTP Endpoint"]
            direction TB
            N1[Weekly data produced]-.->
            N2[Files added to filepath]
        end
        L21[copyImmsData]:::mono
    end
    subgraph DB0["🗄️ Database"]
            DB1[Append new data to target in schema<code> imms </code>]
            DB2["cron(0 13 * * 2)"]:::mono-.->
            DB3["rpt.fn_imms_merge()"]:::mono-.->
            DB4[Patient level view of all overdue imms<code> imms.imms_all </code>]
    end
    
    L1<-->TW0
    L1<-->S30
    TW0<-->S30
    EB2-->L0
    S33-.PUT Trigger.->L21
    L21-.->DB1
    
    classDef mono font-family:Courier New,fill:#393939,color:#ffffff;

</mermaid>


===SWPICS===
We were previously providing a subset of this data for people domiciled in South Waikato to SWPICS.

Relevant data for them can be produced via <code>imms.vw_swpics_imms</code> as needed.