Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 10 Next »

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

Alle Kontakte löschen

z.B. nach einem fehlgeschlagenen Kontaktimport

DELETE FROM instance_properties WHERE instance_id=1;
DELETE FROM tlstatus WHERE instance_id=1;
DELETE FROM instance_properties_attachments where addin_id=1;
DELETE FROM contact;

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

    select CONCAT("Remove-item \'", SUBSTRING(guid, 1, 2), "/", SUBSTRING(guid, 3, 2) , "/", REPLACE( SUBSTRING(guid, 5), "-", "/"), "/\' -Recurse -Force")
FROM instance_properties_attachments
WHERE addin_id=3

  2. Daten aus Datenbank löschen

    DELETE FROM email_messages;
DELETE FROM email_addresses;
DELETE FROM `email_message-email_account`;
DELETE FROM instance_properties WHERE instance_id=3;
DELETE FROM tlstatus WHERE instance_id=3;
DELETE FROM instance_properties_attachments where addin_id=3

Alle Termine aus antony löschen (über Navicat)

z.B. nach einem fehlgeschlagenen Terminimport

DELETE FROM appointment;
DELETE FROM appointment_reminders;
DELETE FROM appointment_resource;
DELETE FROM instance_properties WHERE instance_id=4;
DELETE FROM tlstatus WHERE instance_id=4;
DELETE FROM instance_properties_attachments where addin_id=4;

Nach E-Mail-Import aus Outlook die E-Mail-Datumfelder korrigieren

Die UserId des Admins ist in den Queries notwendig, um zwischen importierten E-Mails und regulär, abgeholten E-Mails zu unterscheiden

UPDATE
email_messages
    INNER JOIN instance_properties ON instance_properties.instance_id = 3 AND instance_properties.object_id = email_messages.id
    AND instance_properties.direction = 2
SET instance_properties.object_date = email_messages.delivery_date WHERE instance_properties.created_by_userid = 2 --das ist die ID des Admins
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
  • No labels