Editing Migrating Aiga records (section)

===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.