...
| Code Block | ||
|---|---|---|
| ||
SELECT contact.id,contact.main_contact_id,contacttitle.`name` as title,contact.company,contact.firstname,contact.lastname,contact.street,contact.zipcode,contact.city,contactstate.`name` as state,contactcountry.`name` as country,contact.phone,contact.phone2,contact.fax,contact.email,contact.mobile,contact.homepage,contact.`comment`,contact.street_private,contact.zipcode_private,contact.city_private,privatestate.`name` as state_private,privatecountry.`name` as country_private,contact.phone_private,contact.phone2_private,contact.fax_private,contact.department,contact.birthday FROM contact LEFT JOIN type_state as contactstate ON contactstate.id = contact.state LEFT JOIN type_state as privatestate ON privatestate.id = contact.state_private LEFT JOIN type_title as contacttitle ON contacttitle.id = contact.title_id LEFT JOIN type_country as contactcountry ON contactcountry.id = contact.country_id LEFT JOIN type_country as privatecountry ON privatecountry.id = contact.country_id_private WHERE contact.deleted = 0 ORDER by main_contact_id ASC |
Kontakte aus antony exportieren (über Navicat) mit Mapping der Kontaktdaten auf Ansprechpartner
(Firma, Vorname, Name, Straße, PLZ, Ort)
| Code Block | ||
|---|---|---|
| ||
SELECT
contact.id,contact.main_contact_id,contacttitle.`name` as title,if(contact.company <> '', contact.company, main_contact.company) as company,if(contact.firstname <> '', contact.firstname, main_contact.firstname) as firstname,if(contact.lastname <> '', contact.lastname, main_contact.lastname) as lastname,if(contact.street <> '', contact.street, main_contact.street) as street,if(contact.zipcode <> '', contact.zipcode, main_contact.zipcode) as zipcode,if(contact.city <> '', contact.city, main_contact.city) as city,contactstate.`name` as state,contactcountry.`name` as country,contact.phone,contact.phone2,contact.fax,contact.email,contact.mobile,contact.homepage,contact.`comment`,contact.street_private,contact.zipcode_private,contact.city_private,privatestate.`name` as state_private,privatecountry.`name` as country_private,contact.phone_private,contact.phone2_private,contact.fax_private,contact.department,contact.birthday
FROM
contact
INNER JOIN instance_properties as ip ON ip.object_id = contact.id AND ip.instance_id = 1
LEFT JOIN type_state as contactstate ON contactstate.id = contact.state
LEFT JOIN type_state as privatestate ON privatestate.id = contact.state_private
LEFT JOIN type_title as contacttitle ON contacttitle.id = contact.title_id
LEFT JOIN type_country as contactcountry ON contactcountry.id = contact.country_id
LEFT JOIN type_country as privatecountry ON privatecountry.id = contact.country_id_private
LEFT join contact as main_contact ON contact.main_contact_id = main_contact.id
WHERE
contact.deleted = 0 AND ip.deleted = 0
ORDER by main_contact_id ASC |
Kontakte aus antony exportieren. Nur eine Kontaktgruppe, inkl. Stichworten
| Code Block | ||
|---|---|---|
| ||
SELECT
(SELECT GROUP_CONCAT(DISTINCT tags.text SEPARATOR ',') FROM tags WHERE (tags.id = SUBSTRING_INDEX(SUBSTRING_INDEX(ip.tags, '\|', 2), '\|', -1) or tags.id = SUBSTRING_INDEX(SUBSTRING_INDEX(ip.tags, '\|', 3), '\|', -1) or tags.id = SUBSTRING_INDEX(SUBSTRING_INDEX(ip.tags, '\|', 4), '\|', -1) or tags.id = SUBSTRING_INDEX(SUBSTRING_INDEX(ip.tags, '\|', 5), '\|', -1)) AND tags.text <> ''),
contact.id,contact.main_contact_id,contacttitle.`name` as title,contact.company,contact.firstname,contact.lastname,contact.street,contact.zipcode,contact.city,contactstate.`name` as state,contactcountry.`name` as country,contact.phone,contact.phone2,contact.fax,contact.email,contact.mobile,contact.homepage,contact.`comment`,contact.street_private,contact.zipcode_private,contact.city_private,privatestate.`name` as state_private,privatecountry.`name` as country_private,contact.phone_private,contact.phone2_private,contact.fax_private,contact.department,contact.birthday
FROM
`contact-group`, contact
LEFT JOIN type_state as contactstate ON contactstate.id = contact.state
LEFT JOIN type_state as privatestate ON privatestate.id = contact.state_private
LEFT JOIN type_title as contacttitle ON contacttitle.id = contact.title_id
LEFT JOIN type_country as contactcountry ON contactcountry.id = contact.country_id
LEFT JOIN type_country as privatecountry ON privatecountry.id = contact.country_id_private
LEFT JOIN instance_properties as ip ON ip.object_id = contact.id AND ip.instance_id = 1
WHERE
contact.deleted = 0 AND
`contact-group`.contact_id = contact.id AND `contact-group`.group_id = 3
AND ip.tags <> ''
ORDER by main_contact_id ASC |
Kontakte exportieren (z.B. für Cleverreach)
| Code Block | ||
|---|---|---|
| ||
SELECT
(SELECT GROUP_CONCAT(DISTINCT tags.text SEPARATOR ',') FROM tags WHERE (tags.id = SUBSTRING_INDEX(SUBSTRING_INDEX(ip.tags, '\|', 2), '\|', -1) or tags.id = SUBSTRING_INDEX(SUBSTRING_INDEX(ip.tags, '\|', 3), '\|', -1) or tags.id = SUBSTRING_INDEX(SUBSTRING_INDEX(ip.tags, '\|', 4), '\|', -1) or tags.id = SUBSTRING_INDEX(SUBSTRING_INDEX(ip.tags, '\|', 5), '\|', -1)) AND tags.text <> '') as interesse_an,
contact.id,contact.main_contact_id,contacttitle.`name` as title,contact.company,contact.firstname,contact.lastname,contact.street,contact.zipcode,contact.city,contactstate.`name` as state,contactcountry.`name` as country,contact.phone,contact.phone2,contact.fax,contact.email,contact.mobile,contact.homepage,contact.street_private,contact.zipcode_private,contact.city_private,privatestate.`name` as state_private,privatecountry.`name` as country_private,contact.phone_private,contact.phone2_private,contact.fax_private,contact.department,contact.birthday
FROM
`contact-group`, contact
LEFT JOIN type_state as contactstate ON contactstate.id = contact.state
LEFT JOIN type_state as privatestate ON privatestate.id = contact.state_private
LEFT JOIN type_title as contacttitle ON contacttitle.id = contact.title_id
LEFT JOIN type_country as contactcountry ON contactcountry.id = contact.country_id
LEFT JOIN type_country as privatecountry ON privatecountry.id = contact.country_id_private
LEFT JOIN instance_properties as ip ON ip.object_id = contact.id AND ip.instance_id = 1
WHERE
contact.deleted = 0 AND contact.email <> '' AND
`contact-group`.contact_id = contact.id AND `contact-group`.group_id = 4
AND ip.tags <> ''
ORDER by main_contact_id ASC |
Termine aus antony als CSV exportieren (über Navicat)
| Code Block | ||
|---|---|---|
| ||
select
allday as 'ganztaegiges ereigniss',
DATE_FORMAT(start, '%Y-%m-%d') as 'beginnt am',
DATE_FORMAT(start, '%T') as 'beginnt um',
DATE_FORMAT(end, '%Y-%m-%d') as 'endet am',
DATE_FORMAT(end, '%T') as 'endet um',
subject as 'betreff',
location as 'ort',
description as 'beschreibung',
cr.caption
FROM appointment
LEFT JOIN appointment_resource ar ON ar.appointment_id = appointment.id
LEFT JOIN calendar_resource cr ON cr.id = ar.resource_id
INNER JOIN instance_properties ip ON ip.object_id = appointment.id AND ip.instance_id = 4
WHERE
appointment.private = 0 AND appointment.recurrence_type = 0 AND ip.deleted = 0 |
Alle Kontakte löschen
z.B. nach einem fehlgeschlagenen Kontaktimport
| Code Block | ||
|---|---|---|
| ||
DELETE FROM instance_properties WHERE instance_id=1; DELETE FROM link WHERE instance_id1=1 OR instance_id2=1; DELETE FROM links WHERE instance_id1=1 OR instance_id2=1; DELETE FROM tlstatus WHERE instance_id=1; DELETE FROM instance_properties_attachments where addin_id=1; DELETE FROM contact; DELETE FROM `contact-group`; |
Alle Emails aus antony und dem Dateisystem löschen (über Navicat und Powershell)
...
Nach E-Mail-Import aus Outlook die E-Mail-Datumfelder korrigieren
| Note |
|---|
ACHTUNG: |
Die UserId des Admins ist in den Queries notwendig, um zwischen importierten E-Mails und regulär, abgeholten E-Mails zu unterscheiden
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
UPDATE
tlstatus
INNER JOIN instance_properties ON
instance_properties.instance_id = tlstatus.instance_id and
tlstatus.instance_id=3 AND
instance_properties.object_id = tlstatus.object_id AND
instance_properties.direction = 2 AND
tlstatus.is_resubmission=0 AND -- nicht wiedervorlegt
tlstatus.resubmission_date is null AND -- keine wiedervorlage eingerichtet
tlstatus.is_globalize=0 AND -- nicht geteilt
instance_properties.chatsession_id=0 -- nicht geteilt
SET tlstatus.resubmission_sort_date = instance_properties.object_date
WHERE tlstatus.resubmission_sort_date is NULL |