Datenbank und SQL Snippets

Owned by Dennis Scheidner
Last updated: Feb 22, 2024
3 min read

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 ASC

Kontakte 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 ASC

Kontakte 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 ASC

Kontakte 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)

  1. Pfade im Dateisystem aus Datenbank holen und das Ergebniss in einem administrativen Powershell-Fenster ausführen

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