Datenbank und SQL Snippets
- Dennis Scheidner
- mlinkwitz
Owned by Dennis Scheidner
- 1 Kontakte aus antony exportieren (über Navicat)
- 2 Kontakte aus antony exportieren (über Navicat) mit Mapping der Kontaktdaten auf Ansprechpartner
- 3 Kontakte aus antony exportieren. Nur eine Kontaktgruppe, inkl. Stichworten
- 4 Kontakte exportieren (z.B. für Cleverreach)
- 5 Termine aus antony als CSV exportieren (über Navicat)
- 6 Alle Kontakte löschen
- 7 Alle Emails aus antony und dem Dateisystem löschen (über Navicat und Powershell)
- 8 Alle Termine aus antony löschen (über Navicat)
- 9 Nach E-Mail-Import aus Outlook die E-Mail-Datumfelder korrigieren
Kontakte aus antony exportieren (über Navicat)
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 ASCKontakte aus antony exportieren (über Navicat) mit Mapping der Kontaktdaten auf Ansprechpartner
(Firma, Vorname, Name, Straße, PLZ, Ort)
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 ASCKontakte aus antony exportieren. Nur eine Kontaktgruppe, inkl. Stichworten
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 ASCKontakte exportieren (z.B. für Cleverreach)
Termine aus antony als CSV exportieren (über Navicat)
Alle Kontakte löschen
z.B. nach einem fehlgeschlagenen Kontaktimport
Alle Emails aus antony und dem Dateisystem löschen (über Navicat und Powershell)
Pfade im Dateisystem aus Datenbank holen und das Ergebniss in einem administrativen Powershell-Fenster ausführen
Daten aus Datenbank löschen
Alle Termine aus antony löschen (über Navicat)
z.B. nach einem fehlgeschlagenen Terminimport
Nach E-Mail-Import aus Outlook die E-Mail-Datumfelder korrigieren
ACHTUNG:
Dieses Vorgehen in der Datenbank nach einem Import ist nur bei den “alten” Importern nötig.
Die neuen (ab antony Version > *.22.150) wird das bereits beim Import korrekt eingefügt.
Die UserId des Admins ist in den Queries notwendig, um zwischen importierten E-Mails und regulär, abgeholten E-Mails zu unterscheiden