Editing Migrating Aiga records (section)

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