Editing Migrating Aiga records (section)

==Approach==
===Service Mapping===
For each service, Kotahi make available a template specifying the activities and fields available to migrate into.

As noted above, we generally want to only migrate into a <code>narrative case note</code>, unless there is a really good reason to do otherwise. The relevant sheet in this template will specify each field and the datatype it must conform to. This should be used to plan the approach in SQL to extract the necessary data.

Sheets that are also relevant will, at minimum, be:
* '''Notes'''. Metadata and actual narrative content around contacts recorded in Aiga.
* '''Users'''. Basic metadata about users who have entered historic notes or uploaded documents. Current staff members will have already been switched over to Kotahi, so this is mostly useful for creating placeholder accounts for staff who have left so they can be identified as authors in Kotahi.
* '''Attachments'''. Specifies filename relating to each migrated client, as well as the Aiga Client ID and a place for an alternative 'Display name' and any comments.<ref>Documents in Aiga are often accompanied by a comment, so this is a useful feature particularly since the file names themselves are generally complete nonsense.</ref>

===SQL definition===
Example SQL definitions for the TTA service are shown below. Comments are included for clarification.

====Notes====
<syntaxhighlight lang="sql" line>
select
	null as ID, --kotahi client id
	n.ClientID as ClientId, --internal AIGA client id
	NoteDate as ActivityStart,
	n.DateCreated as EnteredDate,
	n.DateModified as UpdatedDate,
	null as ClientPresent, --not accessible as a field from AIGA
	0 as IsConfidential, --not accessible as a field from AIGA
	null as DurationAsMinutes, --not accessible as a field from AIGA
	--s.StaffMemberName as CompletedById,
	n.UserID as CompletedById,
	null as Notes,
	--32 as ActivityId,
	ContactTypeID as ActivityId, --internal AIGA contact type id
	case
		when n.ContactTypeID in (2,26,38) then 'Phone Call'
		when n.ContactTypeID in (3,4,19,27,37,40,41,42,68,77,97) then 'Face to Face'
		when n.ContactTypeID in (31) then 'Email'
		else NULL
		end as ContactMethod, --mapping of internal AIGA contact types to ones we have set up and available in Kotahi
	case
		when n.ContactTypeID in (5,68,82,99) then 'Administration'
		when n.ContactTypeID in (13) then 'Advocacy'
		when n.ContactTypeID in (15) then 'Assessment'
		when n.ContactTypeID in (18) then 'Did Not Attend'
		when n.ContactTypeID in (21) then 'Internally Referred'
		when n.ContactTypeID in (24,29) then 'Externally Referral'
		when n.ContactTypeID in (36,72,86,89) then 'Case review'
		when n.ContactTypeID in (40) then 'Workshop'
		when n.ContactTypeID in (41) then 'Family conference'
		when n.ContactTypeID in (70) then 'Cold call visits'
		else NULL
		end as ContactType, --mapping of internal AIGA contact types to ones we have set up and available in Kotahi
	null as FileUpload, --not accessible as a field from AIGA
	Comments as Notes_,
	null as TupaiaAssessmentNotifyUser, --not accessible as a field from AIGA
	null as ContactDuration, --not accessible as a field from AIGA
	case
		when n.ContactTypeID in (4) then 'Home / Whanau Visit'
		when n.ContactTypeID in (42) then 'Social Agency'
		else null 
		end as ContactLocation --mapping of internal AIGA contact types to ones we have set up and available in Kotahi

from vClientNotesDocuments n
join
vEpisodeContracts ec
on n.EpisodeContractID = ec.EpisodeContractID
join
vClients c
on n.ClientID = c.ClientID
join
vStaffMember s
on n.UserID = s.StaffMemberID

where 
ec.ContractID in (
68 --TTA contract
)
and
ec.EpisodeContractEndDate is null  --active TTA clients only
and
n.DocumentPath is null --not an attachment
</syntaxhighlight>

Please note and pay attention here to the loose mapping that is done in the SQL to conform Aiga contact types to Kotahi activity fields.

====Users====
<syntaxhighlight lang="sql" line>
select
	distinct 
	s.UserID,
	s.EmailAddress as Email,
	s.Firstname,
	s.Surname as LastName,
	case
		when s.enabled = 1 then 'TRUE' --yes these are strings, SQL server doesn't have an actual boolean type 🤣
		else 'FALSE'
	end as Active
from vClientNotesDocuments n
join
vEpisodeContracts ec
on n.EpisodeContractID = ec.EpisodeContractID
join
vClients c
on n.ClientID = c.ClientID
join
vSec_Users s
on n.UserID = s.UserID

where 
ec.ContractID in (
68 --TTA contract
)
and
ec.EpisodeContractEndDate is null  --active TTA clients only
and
n.DocumentPath is null --not an attachment
</syntaxhighlight>

Note that table <code>vSec_Users</code> should be used rather than the <code>vStaffMember</code> view because the view does not offer what we need here.

====Attachments====
<syntaxhighlight lang="sql" line>
select
	right(DocumentPath,len(DocumentPath)-12) as FileName,
	null as DisplayName,
	DocumentTypeID as ActivityId, --internal AIGA document type id
	n.ClientID as ClientId, --internal AIGA client id
	Comments as Notes

from vClientNotesDocuments n
join
vEpisodeContracts ec
on n.EpisodeContractID = ec.EpisodeContractID
join
vClients c
on n.ClientID = c.ClientID
join
vStaffMember s
on n.UserID = s.StaffMemberID

where 
ec.ContractID in (
68 --TTA contract
)
and
ec.EpisodeContractEndDate is null  --active TTA clients only
and
n.DocumentPath is not null --is an attachment
</syntaxhighlight>

Note that the list of attachments this SQL provides should be consistent with the actual attachments corpus provided to Kotahi - ie, file names should match so they can be allocated to the right client and service.

===Export===
Result sets from above SQL should be saved as CSV and pushed to a subfolder in the Kotahi Teams environment.<ref>One subfolder per team/service.</ref>

Since all of this work has to be done on the actual Aiga VM, this is not necessarily as easy as it sounds. Firstly, SQL runs should ideally be done outside of business hours so as not to compromise Aiga app performance. Second, just using SSMS and doing something basic like exporting to CSV is usually excruciating. As a final gripe, we did draft some logic to parse out a user-friendly display name for each file but it turns out that SQL server doesn't even respect regex so that's just... wow.

===Attachments===
The counterpart to pulling the attachments metadata, is to actually pull and zip the documents themselves. These need to be saved up in the same subfolder as the CSV sets, so that they can be moved to Kotahi Azure storage and linked to our client records.

This step has a few parts:

====Create a CSV of file names==== 
A file containing the required file names.

This can be obtained by running SQL (from {{Section link|Migrating Aiga records|Attachments|nopage=1}}). Obviously, only the filename part is required. However please also note that the CSV file used for this part MUST include a header row.<ref>Using the provided example code, the header should be <code>FilePath</code>. PowerShell will give you an error if it doesn't have one, but of course the provided error would leave you with absolutely no idea that this is in fact the problem.

====Powershell file====
Containing logic to find files and copy them to target location. Example code:

<syntaxhighlight lang="powershell" line>
# Define the source and destination directories
$sourceDirectory = "[source dir containing documents]"
$destinationDirectory = "[target subfolder]"

# Import the CSV file containing the file paths
$filePaths = Import-Csv -Path "[full path to CSV file]"

# Loop through each file path and copy the file
foreach ($file in $filePaths) {
    $sourceFile = Join-Path -Path $sourceDirectory -ChildPath $file.FilePath
    $destinationFile = Join-Path -Path $destinationDirectory -ChildPath (Split-Path -Path $file.FilePath -Leaf)

    # Copy the file
    Copy-Item -Path $sourceFile -Destination $destinationFile -Force
}
</syntaxhighlight>

====Batch file====
Batch file is required to execute a powershell script, for some god-forsaken reason. Example code:

<syntaxhighlight lang="powershell" line>
@echo off
PowerShell -ExecutionPolicy Bypass -File "[Full path to PowerShell file]"
pause
</syntaxhighlight>

The <code>-ExecutionPolicy Bypass</code> flag apparently allows the script to run without being blocked by the PowerShell execution policy, whatever that means. If only Windows had a [https://linuxhandbook.com/linux-file-permissions/ grown-up permissions system], eh.