SugarCRM: When an Account was Last Contacted

-- update the last_touched_datetime_c based on directly related calls
update calls,accounts,accounts_cstm
set accounts_cstm.last_touched_datetime_c = calls.date_end
where calls.parent_id = accounts.id and accounts.deleted = 0
    and accounts.id = accounts_cstm.id_c
and calls.status = 'Held' and calls.parent_type = 'Accounts' and calls.deleted=0
and (accounts_cstm.last_touched_datetime_c is null OR calls.date_end > accounts_cstm.last_touched_datetime_c);

-- update the last_touched_datetime_c based on directly related meetings
update meetings,accounts,accounts_cstm
set accounts_cstm.last_touched_datetime_c = meetings.date_end
where meetings.parent_id = accounts.id and accounts.deleted = 0
    and accounts.id = accounts_cstm.id_c
and meetings.status = 'Held' and meetings.parent_type = 'Accounts' and meetings.deleted=0
and (accounts_cstm.last_touched_datetime_c is null OR meetings.date_end > accounts_cstm.last_touched_datetime_c);

-- update the last_touched_datetime_c based on directly related notes
update notes,accounts,accounts_cstm
set accounts_cstm.last_touched_datetime_c = notes.date_entered
where notes.parent_id = accounts.id and accounts.deleted = 0
    and accounts.id = accounts_cstm.id_c
and notes.parent_type = 'Accounts' and notes.deleted=0
and (accounts_cstm.last_touched_datetime_c is null OR notes.date_entered > accounts_cstm.last_touched_datetime_c);

-- update the last_touched_datetime_c based on directly related emails
update emails,accounts,accounts_cstm
set accounts_cstm.last_touched_datetime_c = emails.date_sent
where emails.parent_id = accounts.id and accounts.deleted = 0
    and accounts.id = accounts_cstm.id_c
and emails.parent_type = 'Accounts' and emails.deleted=0
and (accounts_cstm.last_touched_datetime_c is null OR emails.date_sent > accounts_cstm.last_touched_datetime_c);

-- update the last_touched_datetime_c based on indirectly related calls
update calls,contacts,accounts_contacts,accounts,accounts_cstm
set accounts_cstm.last_touched_datetime_c = calls.date_end
where
calls.parent_id = contacts.id and contacts.deleted = 0 and calls.deleted=0
and accounts_contacts.contact_id = contacts.id and accounts_contacts.deleted =0
and accounts.id = accounts_contacts.account_id and accounts.deleted=0
and accounts.id = accounts_cstm.id_c
and calls.parent_type = 'Contacts' and calls.status = 'Held'
and (accounts_cstm.last_touched_datetime_c is null OR calls.date_end > accounts_cstm.last_touched_datetime_c);

-- update the last_touched_datetime_c based on indirectly related meetings
update meetings,contacts,accounts_contacts,accounts,accounts_cstm
set accounts_cstm.last_touched_datetime_c = meetings.date_end
where
meetings.parent_id = contacts.id and contacts.deleted = 0 and meetings.deleted=0
and accounts_contacts.contact_id = contacts.id and accounts_contacts.deleted =0
and accounts.id = accounts_contacts.account_id and accounts.deleted=0
and accounts.id = accounts_cstm.id_c
and meetings.parent_type = 'Contacts' and meetings.status = 'Held'
and (accounts_cstm.last_touched_datetime_c is null OR meetings.date_end > accounts_cstm.last_touched_datetime_c);

-- update the last_touched_datetime_c based on indirectly related notes
update notes,contacts,accounts_contacts,accounts,accounts_cstm
set accounts_cstm.last_touched_datetime_c = notes.date_entered
where
notes.parent_id = contacts.id and contacts.deleted = 0 and notes.deleted=0
and accounts_contacts.contact_id = contacts.id and accounts_contacts.deleted =0
and accounts.id = accounts_contacts.account_id and accounts.deleted=0
and accounts.id = accounts_cstm.id_c
and notes.parent_type = 'Contacts'
and (accounts_cstm.last_touched_datetime_c is null OR notes.date_entered > accounts_cstm.last_touched_datetime_c);

-- update the last_touched_datetime_c based on indirectly related emails
update emails,contacts,accounts_contacts,accounts,accounts_cstm
set accounts_cstm.last_touched_datetime_c = emails.date_sent
where
emails.parent_id = contacts.id and contacts.deleted = 0 and emails.deleted=0
and accounts_contacts.contact_id = contacts.id and accounts_contacts.deleted =0
and accounts.id = accounts_contacts.account_id and accounts.deleted=0
and accounts.id = accounts_cstm.id_c
and emails.parent_type = 'Contacts'
and (accounts_cstm.last_touched_datetime_c is null OR emails.date_sent > accounts_cstm.last_touched_datetime_c);