AddressBook_Contacts.sql

WITH
CleanLabels AS (
    SELECT
        ROWID AS label_id,
        CASE
            WHEN value LIKE '_$!<%>!$_'
            THEN substr(value, instr(value, '<') + 1, instr(value, '>') - instr(value, '<') - 1)
            ELSE value
        END AS clean_value
    FROM ABMultiValueLabel
),
CustomFields AS (
    SELECT
        ABMultiValue.record_id,
        group_concat(
            COALESCE(CleanLabels.clean_value, ABMultiValueLabel.value) || ': ' ||
			CASE
                WHEN 
                    ABMultiValue.value GLOB '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
                    OR ABMultiValue.value GLOB '( [0-9][0-9][0-9] ) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
                    OR ABMultiValue.value GLOB '+[0-9] [0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
                    OR ABMultiValue.value GLOB '+[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
                    OR ABMultiValue.value GLOB '*[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]*'
                    OR ABMultiValue.value GLOB '( [0-9][0-9][0-9] )*'
                THEN 
                    ABMultiValue.value
                WHEN 
                    ABMultiValue.value NOT GLOB '*[-+() ]*'
                    AND CAST(ABMultiValue.value AS REAL) = CAST(ABMultiValue.value AS REAL)
                    AND CAST(ABMultiValue.value AS REAL) BETWEEN 100000000.0 AND 2147483647.0
                    AND (CAST(ABMultiValue.value AS REAL) = CAST(CAST(ABMultiValue.value AS REAL) AS INTEGER)
                    OR ABS(CAST(ABMultiValue.value AS REAL) - CAST(CAST(ABMultiValue.value AS REAL) AS INTEGER)) < 0.00001)
                THEN 
                    SUBSTR(DATETIME(CAST(ABMultiValue.value AS INTEGER) + 978307200, 'unixepoch'), 1, 10)
                ELSE 
                    ABMultiValue.value
			END, CHAR(10)) AS custom_field_list
    FROM ABMultiValue
    LEFT JOIN ABMultiValueLabel ON ABMultiValue.label = ABMultiValueLabel.ROWID
    LEFT JOIN CleanLabels ON ABMultiValue.label = CleanLabels.label_id
    WHERE 
        ABMultiValueLabel.ROWID IS NOT NULL
        AND ABMultiValue.value IS NOT NULL
        AND ABMultiValue.value != ''
    GROUP BY ABMultiValue.record_id
),
AddressEntries AS (
    SELECT
        ABMultiValueEntry.parent_id AS person_rowid,
        ABMultiValueEntryKey.value AS entry_key,
        REPLACE(ABMultiValueEntry.value, CHAR(10), ' ') AS entry_value
    FROM ABMultiValueEntry
    LEFT JOIN ABMultiValueEntryKey
        ON ABMultiValueEntry.key = ABMultiValueEntryKey.ROWID
),
OrderedAddresses AS (
    SELECT
        person_rowid,
        group_concat(entry_value, ', ') AS full_address
    FROM (
        SELECT
            AddressEntries.person_rowid,
            CASE
				WHEN AddressEntries.entry_key IS 'CountryCode'
				THEN upper(AddressEntries.entry_value)
				ELSE AddressEntries.entry_value
			END AS entry_value
        FROM AddressEntries
        ORDER BY 
            AddressEntries.person_rowid,
            CASE AddressEntries.entry_key
                WHEN 'Street' THEN 1
                WHEN 'City' THEN 2
                WHEN 'State' THEN 3
                WHEN 'ZIP' THEN 4
                WHEN 'Country' THEN 5
                ELSE 99
            END
    )
    GROUP BY person_rowid
),
AddressLabels AS (
    SELECT
        CleanLabels.clean_value AS address_label,
        ABMultiValue.record_id AS person_rowid,
        ABMultiValue.UID AS multivalue_uid
    FROM ABMultiValue
    LEFT JOIN CleanLabels
        ON ABMultiValue.label = CleanLabels.label_id
    WHERE ABMultiValue.property = 5 AND ABMultiValue.record_id = ABPerson.ROWID
),
data AS (
    SELECT
        ROWID,
        First,
        date(Birthday + 978307200, 'unixepoch') AS BirthdayDate,
        AlternateBirthday
    FROM ABPerson
    WHERE AlternateBirthday LIKE 'chinese%'
),
split AS (
    SELECT
        ROWID,
        First,
        BirthdayDate,
        AlternateBirthday,
        substr(AlternateBirthday || ',', instr(AlternateBirthday || ',', ',') + 1) AS part2
    FROM data
),
final_split AS (
    SELECT
        ROWID,
        First,
        BirthdayDate,
        AlternateBirthday,
        substr(part2, 0, instr(part2, ',')) AS is_leap_str,
        substr(part2, instr(part2, ',') + 1) AS rest
    FROM split
),
version_step AS (
    SELECT
        ROWID,
        First,
        BirthdayDate,
        is_leap_str,
        rest,
        CAST(substr(rest, 0, instr(rest, ',')) AS INTEGER) AS version
    FROM final_split
),
year_split AS (
    SELECT
        ROWID,
        First,
        BirthdayDate,
        CAST(is_leap_str AS INTEGER) AS is_leap,
        version,
        substr(rest, instr(rest, ',') + 1) AS rest2
    FROM version_step
),
month_day_split AS (
    SELECT
        ROWID,
        First,
        BirthdayDate,
        is_leap,
        version,
        CAST(substr(rest2, 0, instr(rest2, ',')) AS INTEGER) AS year_index,
        substr(rest2, instr(rest2, ',') + 1) AS rest3
    FROM year_split
),
final_calc AS (
    SELECT
        *,
        1863 + 60 * (version - 76) + year_index AS real_year,
        (1863 + 60 * (version - 76) + year_index - 1984 + 60) % 60 AS sb_index
    FROM month_day_split
),
MVE_Username AS (
    SELECT 
        ABMultiValueEntry.parent_id AS entry_parent_id,
        TRIM(ABMultiValueEntry.value) AS entry_username
    FROM ABMultiValueEntry 
    LEFT JOIN ABMultiValueEntryKey 
        ON ABMultiValueEntry.key = ABMultiValueEntryKey.ROWID 
    WHERE ABMultiValueEntryKey.value = 'username'),
MVE_Service AS (
    SELECT 
        ABMultiValueEntry.parent_id AS entry_parent_id,
        TRIM(ABMultiValueEntry.value) AS entry_service
    FROM ABMultiValueEntry 
    LEFT JOIN ABMultiValueEntryKey 
        ON ABMultiValueEntry.key = ABMultiValueEntryKey.ROWID 
    WHERE ABMultiValueEntryKey.value = 'service'),
MVE_ProfileEntries AS (
    SELECT 
        ABMultiValueEntry.parent_id AS entry_parent_id,
        ABMultiValueEntryKey.value AS entry_key,
        TRIM(ABMultiValueEntry.value) AS entry_value
    FROM ABMultiValueEntry
    LEFT JOIN ABMultiValueEntryKey
        ON ABMultiValueEntry.key = ABMultiValueEntryKey.ROWID),
MV_InstantMessage AS (
    SELECT 
        COALESCE(
            CleanLabels.clean_value,
            ABMultiValueLabel.value,
            'Instant Message'
        ) AS im_label,
        ABMultiValue.record_id AS person_rowid,
        ABMultiValue.UID AS multivalue_uid
    FROM ABMultiValue 
    LEFT JOIN ABMultiValueLabel
        ON ABMultiValue.label = ABMultiValueLabel.ROWID
    LEFT JOIN CleanLabels
        ON ABMultiValue.label = CleanLabels.label_id
    WHERE 
        ABMultiValue.property = 13),
MV_Profile AS (
    SELECT 
        COALESCE(ABMultiValueLabel.value, 'Profile') AS profile_label,
        ABMultiValue.record_id AS person_rowid,
        ABMultiValue.UID AS multivalue_uid
    FROM ABMultiValue
    LEFT JOIN ABMultiValueLabel
        ON ABMultiValue.label = ABMultiValueLabel.ROWID
    WHERE 
        ABMultiValue.property = 46),
UnifiedAccounts AS (
    SELECT 
        MV_InstantMessage.person_rowid AS person_rowid,
        TRIM(MVE_Service.entry_service) AS service,
        TRIM(MVE_Username.entry_username) AS username
    FROM MV_InstantMessage
    LEFT JOIN MVE_Service
        ON MV_InstantMessage.multivalue_uid = MVE_Service.entry_parent_id
    LEFT JOIN MVE_Username
        ON MV_InstantMessage.multivalue_uid = MVE_Username.entry_parent_id

    UNION ALL

    SELECT 
        MV_Profile.person_rowid AS person_rowid,
        TRIM(MAX(CASE WHEN MVE_ProfileEntries.entry_key = 'service' THEN MVE_ProfileEntries.entry_value END)) AS service,
        TRIM(MAX(CASE WHEN MVE_ProfileEntries.entry_key = 'username' THEN MVE_ProfileEntries.entry_value END)) AS username
    FROM MV_Profile
    LEFT JOIN MVE_ProfileEntries
        ON MV_Profile.multivalue_uid = MVE_ProfileEntries.entry_parent_id
    GROUP BY 
        MV_Profile.person_rowid,
        MV_Profile.multivalue_uid)
SELECT
ABPerson.Prefix AS "Prefix",
ABPerson.First AS "First Name",
ABPerson.Middle AS "Middle Name",
ABPerson.Last AS "Last Name",
ABPerson.Suffix AS "Suffix",
COALESCE(CustomFields.custom_field_list, '') AS "Phone / Email / Misc",
(
	SELECT group_concat(
		CASE 
			WHEN AddressLabels.address_label IS NOT NULL AND TRIM(AddressLabels.address_label) != '' 
				THEN AddressLabels.address_label || ': ' || OrderedAddresses.full_address
			ELSE OrderedAddresses.full_address
		END, CHAR(13)
	)
	FROM AddressLabels
	LEFT JOIN OrderedAddresses
		ON AddressLabels.multivalue_uid = OrderedAddresses.person_rowid
	GROUP BY AddressLabels.person_rowid
) AS "Address(es)",
CASE 
	WHEN TRIM(
		COALESCE(ABPerson.Organization, '') ||
		COALESCE(ABPerson.Department, '') ||
		COALESCE(ABPerson.JobTitle, '') ||
		COALESCE(ABPerson.OrganizationPhonetic, '') ||
		COALESCE(ABPerson.OrganizationPronunciation, '')
		)!= ''
	THEN 
		TRIM(
			(CASE 
				WHEN TRIM(ABPerson.Organization) != '' 
				THEN 'Organization: ' || TRIM(ABPerson.Organization) || 
					CASE 
						WHEN TRIM(ABPerson.Department) != '' OR TRIM(ABPerson.JobTitle) != '' 
						THEN CHAR(10) 
						ELSE '' 
					END
				ELSE '' END) ||
			(CASE 
				WHEN TRIM(ABPerson.Department) != '' 
				THEN 'Department: ' || TRIM(ABPerson.Department) || 
					CASE 
						WHEN TRIM(ABPerson.JobTitle) != '' 
						THEN CHAR(10) 
						ELSE '' 
					END
			 ELSE '' 
			 END) ||
			 (CASE 
				WHEN TRIM(ABPerson.JobTitle) != '' 
				THEN 'Title: ' || TRIM(ABPerson.JobTitle) || 
					CASE 
						WHEN TRIM(ABPerson.OrganizationPhonetic) != '' 
						THEN CHAR(10) 
						ELSE '' 
					END
			 ELSE '' 
			 END) ||
			 (CASE 
				WHEN TRIM(ABPerson.OrganizationPhonetic) != '' 
				THEN 'Organization Phonetic: ' || TRIM(ABPerson.OrganizationPhonetic) || 
					CASE 
						WHEN TRIM(ABPerson.OrganizationPronunciation) != '' 
						THEN CHAR(10) 
						ELSE '' 
					END
			 ELSE '' 
			 END) ||
			(CASE 
				WHEN TRIM(ABPerson.OrganizationPronunciation) != '' 
				THEN 'Organization Pronunciation: ' || TRIM(ABPerson.OrganizationPronunciation) 
				ELSE '' 
			END))
	ELSE NULL
END AS "Profession",
(SELECT group_concat(formatted_account, CHAR(10))
	FROM (
		SELECT
			CASE 
				WHEN service IS NOT NULL AND service != '' 
				 AND username IS NOT NULL AND username != ''
					THEN service || ': ' || username
				WHEN service IS NOT NULL AND service != ''
					THEN service
				WHEN username IS NOT NULL AND username != ''
					THEN username
				ELSE NULL
			END AS formatted_account,
			service
		FROM UnifiedAccounts
		WHERE UnifiedAccounts.person_rowid = ABPerson.ROWID
		  AND ((service IS NOT NULL AND TRIM(service) != '') OR (username IS NOT NULL AND TRIM(username) != ''))
		ORDER BY service COLLATE NOCASE ASC)) AS "Account(s)",
ABPerson.Note AS "Note",
NULLIF(
	TRIM(
		COALESCE(
			CASE 
				WHEN ABPerson.Birthday IS NOT NULL AND ABPerson.Birthday <> ''
				THEN 'Birthday: ' || SUBSTR(DATETIME(ABPerson.Birthday + 978307200, 'UNIXEPOCH'),1,10)
			END, '') ||
			CASE 
				WHEN ABPerson.AlternateBirthday IS NOT NULL AND ABPerson.AlternateBirthday <> ''
				THEN CHAR(10) || 'Alternate Birthday: ' || real_year ||
					CASE sb_index
						WHEN 0 THEN '甲子' WHEN 1 THEN '乙丑' WHEN 2 THEN '丙寅' WHEN 3 THEN '丁卯'
						WHEN 4 THEN '戊辰' WHEN 5 THEN '己巳' WHEN 6 THEN '庚午' WHEN 7 THEN '辛未'
						WHEN 8 THEN '壬申' WHEN 9 THEN '癸酉' WHEN 10 THEN '甲戌' WHEN 11 THEN '乙亥'
						WHEN 12 THEN '丙子' WHEN 13 THEN '丁丑' WHEN 14 THEN '戊寅' WHEN 15 THEN '己卯'
						WHEN 16 THEN '庚辰' WHEN 17 THEN '辛巳' WHEN 18 THEN '壬午' WHEN 19 THEN '癸未'
						WHEN 20 THEN '甲申' WHEN 21 THEN '乙酉' WHEN 22 THEN '丙戌' WHEN 23 THEN '丁亥'
						WHEN 24 THEN '戊子' WHEN 25 THEN '己丑' WHEN 26 THEN '庚寅' WHEN 27 THEN '辛卯'
						WHEN 28 THEN '壬辰' WHEN 29 THEN '癸巳' WHEN 30 THEN '甲午' WHEN 31 THEN '乙未'
						WHEN 32 THEN '丙申' WHEN 33 THEN '丁酉' WHEN 34 THEN '戊戌' WHEN 35 THEN '己亥'
						WHEN 36 THEN '庚子' WHEN 37 THEN '辛丑' WHEN 38 THEN '壬寅' WHEN 39 THEN '癸卯'
						WHEN 40 THEN '甲辰' WHEN 41 THEN '乙巳' WHEN 42 THEN '丙午' WHEN 43 THEN '丁未'
						WHEN 44 THEN '戊申' WHEN 45 THEN '己酉' WHEN 46 THEN '庚戌' WHEN 47 THEN '辛亥'
						WHEN 48 THEN '壬子' WHEN 49 THEN '癸丑' WHEN 50 THEN '甲寅' WHEN 51 THEN '乙卯'
						WHEN 52 THEN '丙辰' WHEN 53 THEN '丁巳' WHEN 54 THEN '戊午' WHEN 55 THEN '己未'
						WHEN 56 THEN '庚申' WHEN 57 THEN '辛酉' WHEN 58 THEN '壬戌' WHEN 59 THEN '癸亥'
					END || '年' ||
			CASE 
				WHEN is_leap = 1 
				THEN '闰' 
				ELSE '' 
			END ||
			CASE CAST(substr(rest3, 0, instr(rest3 || ',', ',')) AS INTEGER)
				WHEN 1 THEN '正' WHEN 2 THEN '二' WHEN 3 THEN '三' WHEN 4 THEN '四'
				WHEN 5 THEN '五' WHEN 6 THEN '六' WHEN 7 THEN '七' WHEN 8 THEN '八'
				WHEN 9 THEN '九' WHEN 10 THEN '十' WHEN 11 THEN '冬' WHEN 12 THEN '腊'
			END || '月' ||
			CASE CAST(substr(rest3, instr(rest3 || ',', ',') + 1) AS INTEGER)
				WHEN 1 THEN '初一' WHEN 2 THEN '初二' WHEN 3 THEN '初三' WHEN 4 THEN '初四'
				WHEN 5 THEN '初五' WHEN 6 THEN '初六' WHEN 7 THEN '初七' WHEN 8 THEN '初八'
				WHEN 9 THEN '初九' WHEN 10 THEN '初十' WHEN 11 THEN '十一' WHEN 12 THEN '十二'
				WHEN 13 THEN '十三' WHEN 14 THEN '十四' WHEN 15 THEN '十五' WHEN 16 THEN '十六'
				WHEN 17 THEN '十七' WHEN 18 THEN '十八' WHEN 19 THEN '十九' WHEN 20 THEN '二十'
				WHEN 21 THEN '廿一' WHEN 22 THEN '廿二' WHEN 23 THEN '廿三' WHEN 24 THEN '廿四'
				WHEN 25 THEN '廿五' WHEN 26 THEN '廿六' WHEN 27 THEN '廿七' WHEN 28 THEN '廿八'
				WHEN 29 THEN '廿九' WHEN 30 THEN '三十'
			END 
		ELSE ''
    END), '') AS "Birthday",
(SELECT 
	group_concat(ABGroup.Name, ', ')
FROM ABGroupMembers
LEFT JOIN ABGroup ON ABGroupMembers.group_id = ABGroup.ROWID
WHERE ABGroupMembers.member_id = ABPerson.ROWID
GROUP BY ABGroupMembers.member_id) AS "Group",
NULLIF(
	TRIM(
		COALESCE(CASE 
			WHEN ABPerson.FirstPhonetic IS NOT NULL AND ABPerson.FirstPhonetic <> ''
			THEN 'Phonetic First Name: ' || ABPerson.FirstPhonetic || CHAR(10)
		END, '') ||
		COALESCE(CASE 
			WHEN ABPerson.FirstPronunciation IS NOT NULL AND ABPerson.FirstPronunciation <> ''
			THEN 'First Name Pronunciation: ' || ABPerson.FirstPronunciation || CHAR(10)
		END, '') ||
		COALESCE(CASE 
			WHEN ABPerson.MiddlePhonetic IS NOT NULL AND ABPerson.MiddlePhonetic <> ''
			THEN 'Phonetic Middle Name: ' || ABPerson.MiddlePhonetic || CHAR(10)
		END, '') ||
		COALESCE(CASE 
			WHEN ABPerson.MiddlePronunciation IS NOT NULL AND ABPerson.MiddlePronunciation <> ''
			THEN 'Middle Name Pronunciation: ' || ABPerson.MiddlePronunciation || CHAR(10)
		END, '') ||
		COALESCE(CASE 
			WHEN ABPerson.LastPhonetic IS NOT NULL AND ABPerson.LastPhonetic <> ''
			THEN 'Phonetic Last Name: ' || ABPerson.LastPhonetic || CHAR(10)
		END, '') ||
		COALESCE(CASE 
			WHEN ABPerson.LastPronunciation IS NOT NULL AND ABPerson.LastPronunciation <> ''
			THEN 'Last Name Pronunciation: ' || ABPerson.LastPronunciation
		END, '')), '') AS "Phonetic / Pronunciation Name",
CASE 
	WHEN COALESCE(ABPerson.CreationDate, 0) != 0 OR COALESCE(ABPerson.ModificationDate, 0) != 0 OR 
		EXISTS (SELECT 1 
				FROM ABPersonChanges
				JOIN ClientSequence ON ABPersonChanges.sequence_number = ClientSequence.sequence_number 
				WHERE ABPersonChanges.record = ABPerson.ROWID AND COALESCE(ClientSequence.save_timestamp, 0) != 0)
	THEN 
		TRIM((CASE 
			WHEN COALESCE(ABPerson.CreationDate, 0) != 0 
			THEN 'Created: ' || DATETIME(ABPerson.CreationDate + 978307200, 'UNIXEPOCH') || CHAR(10)
			ELSE '' 
			END) ||
		(CASE 
			WHEN EXISTS (SELECT 1
						FROM ABPersonChanges
						JOIN ClientSequence ON ABPersonChanges.sequence_number = ClientSequence.sequence_number
						WHERE ABPersonChanges.record = ABPerson.ROWID AND COALESCE(ClientSequence.save_timestamp, 0) != 0)
			THEN 'Saved:' || CHAR(10) ||
				(SELECT GROUP_CONCAT('  ' || DATETIME(ClientSequence.save_timestamp + 978307200, 'UNIXEPOCH'), CHAR(10))
				FROM ABPersonChanges
				JOIN ClientSequence ON ABPersonChanges.sequence_number = ClientSequence.sequence_number
				WHERE ABPersonChanges.record = ABPerson.ROWID AND COALESCE(ClientSequence.save_timestamp, 0) != 0) || CHAR(10)
			ELSE ''
			END) ||
		(CASE 
			WHEN COALESCE(ABPerson.ModificationDate, 0) != 0 
			THEN 'Modified: ' || DATETIME(ABPerson.ModificationDate + 978307200, 'UNIXEPOCH')
			ELSE '' 
		END))
	ELSE NULL
END AS "Date Values"
FROM ABPerson
LEFT JOIN ABStore ON ABPerson.StoreID = ABStore.ROWID
LEFT JOIN CustomFields ON CustomFields.record_id = ABPerson.ROWID
LEFT JOIN final_calc ON final_calc.ROWID = ABPerson.ROWID