# Daten

Abfrage:
Select TBL_CONTACT.COMPANYNAME As Firma, TBL_CONTACT.FULLNAME As Kontakt, TBL_CONTACT.CONTACTID As showACTContact_Kontakt, TBL_ADDRESS.POSTALCODE As PLZ, TBL_ADDRESS.CITY As Stadt, ACos(Sin(GruppenmitgliederGeo.BGrad * Pi() / 180.0) * Sin(KontaktGeo.BGrad * Pi() / 180.0) + Cos(GruppenmitgliederGeo.BGrad * Pi() / 180.0) * Cos(KontaktGeo.BGrad * Pi() / 180.0) * Cos((GruppenmitgliederGeo.LGrad - KontaktGeo.LGrad) * Pi() / 180.0)) / Pi() * 180.0 * 60.0 * 1.1515 * 1.609344 As [right_Luftlinie KM] From TBL_GROUP Inner Join TBL_GROUP_CONTACT On TBL_GROUP.GROUPID = TBL_GROUP_CONTACT.GROUPID Inner Join TBL_CONTACT On TBL_CONTACT.CONTACTID = TBL_GROUP_CONTACT.CONTACTID Inner Join (Select TBL_CONTACT.CONTACTID, Convert(decimal(10,6),Replace(TBL_CONTACT.USER3, ',', '.')) As LGrad, Convert(decimal(10,6),Replace(TBL_CONTACT.USER4, ',', '.')) As BGrad From TBL_CONTACT Inner Join TBL_GROUP_CONTACT On TBL_CONTACT.CONTACTID = TBL_GROUP_CONTACT.CONTACTID Inner Join TBL_GROUP On TBL_GROUP.GROUPID = TBL_GROUP_CONTACT.GROUPID Where TBL_GROUP.NAME = 'Entfernung Luftlinie') GruppenmitgliederGeo On GruppenmitgliederGeo.CONTACTID = TBL_CONTACT.CONTACTID Inner Join TBL_ADDRESS On TBL_ADDRESS.CONTACTID = TBL_GROUP_CONTACT.CONTACTID, (Select Convert(decimal(10,6),Replace(TBL_CONTACT.USER3, ',', '.')) As LGrad, Convert(decimal(10,6),Replace(TBL_CONTACT.USER4, ',', '.')) As BGrad, TBL_CONTACT.CONTACTID From TBL_CONTACT Where TBL_CONTACT.CONTACTID = '{!ACTKontakt, GUID}') KontaktGeo Where TBL_GROUP.NAME = 'Entfernung Luftlinie' Order By [right_Luftlinie KM]


Abfrage:
SHAPE {Select 1 As hide_A, '' + TBL_CONTACT_CONTACT.CONTACT1ROLE + '' As [Eigene Rolle], TBL_CONTACT1.FULLNAME As [Über Mitarbeiter], '' + TBL_CONTACT2.COMPANYNAME + '' As [Beziehung zu Firma], TBL_CONTACT2.COMPANYID As showactcompany_Firma, TBL_CONTACT2.FULLNAME As Ansprechpartner, TBL_CONTACT2.CONTACTID As showactcontact_Kontakt, TBL_CONTACT_CONTACT.CONTACTID1 As hide_ID_A, '' + TBL_CONTACT_CONTACT.CONTACT2ROLE + '' As [Fremde Rolle], TBL_CONTACT_CONTACT.CONTACTID2 As hide_ID_B From TBL_CONTACT Inner Join TBL_COMPANY On TBL_CONTACT.COMPANYID = TBL_COMPANY.COMPANYID Inner Join TBL_CONTACT TBL_CONTACT1 On TBL_COMPANY.COMPANYID = TBL_CONTACT1.COMPANYID Inner Join TBL_CONTACT_CONTACT On TBL_CONTACT1.CONTACTID = TBL_CONTACT_CONTACT.CONTACTID1 Inner Join TBL_CONTACT TBL_CONTACT2 On TBL_CONTACT_CONTACT.CONTACTID2 = TBL_CONTACT2.CONTACTID Where TBL_CONTACT.CONTACTID = '{!ACTKontakt, GUID}' Union Select 2 As hide_A, '' + TBL_CONTACT_CONTACT.CONTACT2ROLE + '' As [Eigene Rolle], TBL_CONTACT1.FULLNAME As [Über Mitarbeiter], '' + TBL_CONTACT2.COMPANYNAME + '' As [Beziehung zu Firma], TBL_CONTACT2.COMPANYID As showactcompany_Firma, TBL_CONTACT2.FULLNAME As Ansprechpartner, TBL_CONTACT2.CONTACTID As showactcontact_Kontakt, TBL_CONTACT_CONTACT.CONTACTID1 As hide_ID_A, '' + TBL_CONTACT_CONTACT.CONTACT1ROLE + '' As [Fremde Rolle], TBL_CONTACT_CONTACT.CONTACTID2 As hide_ID_B From TBL_CONTACT Inner Join TBL_COMPANY On TBL_CONTACT.COMPANYID = TBL_COMPANY.COMPANYID Inner Join TBL_CONTACT TBL_CONTACT1 On TBL_COMPANY.COMPANYID = TBL_CONTACT1.COMPANYID Inner Join TBL_CONTACT_CONTACT On TBL_CONTACT1.CONTACTID = TBL_CONTACT_CONTACT.CONTACTID2 Inner Join TBL_CONTACT TBL_CONTACT2 On TBL_CONTACT2.CONTACTID = TBL_CONTACT_CONTACT.CONTACTID1 Where TBL_CONTACT.CONTACTID = '{!ACTKontakt, GUID}'}  AS EinsBeziehung APPEND ({Select Cast(TBL_CONTACT_CONTACT.DETAILS As varchar(255)) As rtf2text_Details, TBL_CONTACT_CONTACT.CONTACTID1 As hide_ID_A, TBL_CONTACT_CONTACT.CONTACTID2 As hide_ID_B From TBL_CONTACT Inner Join TBL_COMPANY On TBL_CONTACT.COMPANYID = TBL_COMPANY.COMPANYID Inner Join TBL_CONTACT TBL_CONTACT1 On TBL_COMPANY.COMPANYID = TBL_CONTACT1.COMPANYID Inner Join TBL_CONTACT_CONTACT On TBL_CONTACT1.CONTACTID = TBL_CONTACT_CONTACT.CONTACTID1 Inner Join TBL_CONTACT TBL_CONTACT2 On TBL_CONTACT_CONTACT.CONTACTID2 = TBL_CONTACT2.CONTACTID Where TBL_CONTACT.CONTACTID = '{!ACTKontakt, GUID}' Union Select Cast(TBL_CONTACT_CONTACT.DETAILS As varchar(255)) As rtf2text_Details, TBL_CONTACT_CONTACT.CONTACTID1 As hide_ID_A, TBL_CONTACT_CONTACT.CONTACTID2 As hide_ID_B From TBL_CONTACT Inner Join TBL_COMPANY On TBL_CONTACT.COMPANYID = TBL_COMPANY.COMPANYID Inner Join TBL_CONTACT TBL_CONTACT1 On TBL_COMPANY.COMPANYID = TBL_CONTACT1.COMPANYID Inner Join TBL_CONTACT_CONTACT On TBL_CONTACT1.CONTACTID = TBL_CONTACT_CONTACT.CONTACTID2 Inner Join TBL_CONTACT TBL_CONTACT2 On TBL_CONTACT2.CONTACTID = TBL_CONTACT_CONTACT.CONTACTID1 Where TBL_CONTACT.CONTACTID = '{!ACTKontakt, GUID}'}  AS ZweiDetails RELATE 'hide_ID_A' TO 'hide_ID_A','hide_ID_B' TO 'hide_ID_B') AS ZweiDetails


Abfrage:
Select TBL_CONTACT.FULLNAME As Kontakt, TBL_CONTACT.COMPANYNAME As Firma, Sum(Dauer.[Gesamtdauer Minuten]) As [right_format#,##_Dauer Minuten], Sum(Dauer.[Gesamtdauer Stunden]) As [right_format#,##0*000_Dauer Stunden], Sum(Dauer.[Gesamtdauer Stunden] * Verrechnungssatz.Verrechnungssatz) As [right_format#,##0*00_RG-Betrag] From (Select Sum(TBL_HISTORY.DURATION / 60.0) As [Gesamtdauer Stunden], TBL_CONTACT_HISTORY.CONTACTID, Sum(TBL_HISTORY.DURATION) As [Gesamtdauer Minuten] From TBL_HISTORY Inner Join TBL_CONTACT_HISTORY On TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID Inner Join TBL_HISTORYTYPE On TBL_HISTORYTYPE.HISTORYTYPEID = TBL_HISTORY.HISTORYTYPEID Inner Join TBL_ACTIVITYTYPE On TBL_ACTIVITYTYPE.ACTIVITYTYPEID = TBL_HISTORYTYPE.ACTIVITYTYPEID Inner Join TBL_CONTACT On TBL_CONTACT.CONTACTID = TBL_CONTACT_HISTORY.CONTACTID Where TBL_ACTIVITYTYPE.NAME = '{!RegistryValue, Suchbegriff01}' And TBL_HISTORY.STARTTIME >= Convert(Datetime,'{!RegistryValue, WP_Start}',104) And TBL_HISTORY.STARTTIME <= Convert(Datetime,'{!RegistryValue, WP_Ende}',104) Group By TBL_CONTACT_HISTORY.CONTACTID, TBL_ACTIVITYTYPE.NAME, TBL_HISTORY.STARTTIME Having TBL_CONTACT_HISTORY.CONTACTID = '{!ACTKontakt, GUID}') Dauer Inner Join TBL_CONTACT On TBL_CONTACT.CONTACTID = Dauer.CONTACTID, (Select '{!RegistryValue, Zahl01}' As Verrechnungssatz) Verrechnungssatz Group By TBL_CONTACT.FULLNAME, TBL_CONTACT.COMPANYNAME Order By Kontakt


Abfrage:
SHAPE {Select TBL_HISTORY.STARTTIME As formatshortlocaldate_Datum, TBL_HISTORY.DURATION As [format#,##_Dauer Minuten], TBL_USER.USERLOGIN As [Durchgeführt von], TBL_HISTORYTYPE.NAME As Ergebnis, TBL_HISTORY.REGARDING As Betreff, TBL_HISTORY.HISTORYID As hide_HistoryID From TBL_ACTIVITYTYPE Inner Join TBL_HISTORYTYPE On TBL_ACTIVITYTYPE.ACTIVITYTYPEID = TBL_HISTORYTYPE.ACTIVITYTYPEID Inner Join TBL_HISTORY On TBL_HISTORYTYPE.HISTORYTYPEID = TBL_HISTORY.HISTORYTYPEID Inner Join TBL_CONTACT_HISTORY On TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID Inner Join TBL_USER On TBL_HISTORY.MANAGEUSERID = TBL_USER.USERID Inner Join (Select '{!RegistryValue, Suchbegriff01}' As Suchbegriff, Convert(Datetime,'{!RegistryValue, WP_Start}',104) As Startdatum, Convert(Datetime,'{!RegistryValue, WP_Ende}',104) As Enddatum) Parameter On TBL_ACTIVITYTYPE.NAME = Parameter.Suchbegriff Where TBL_HISTORY.STARTTIME >= Parameter.Startdatum And TBL_HISTORY.STARTTIME <= Parameter.Enddatum And TBL_CONTACT_HISTORY.CONTACTID = '{!ACTKontakt, GUID}' Order By formatshortlocaldate_Datum}  AS Eins_Servicefaelle APPEND ({Select TBL_HISTORY.HISTORYID As hide_HistoryID, TBL_HISTORY.DETAILS As rtf2text_Details From TBL_ACTIVITYTYPE Inner Join TBL_HISTORYTYPE On TBL_ACTIVITYTYPE.ACTIVITYTYPEID = TBL_HISTORYTYPE.ACTIVITYTYPEID Inner Join TBL_HISTORY On TBL_HISTORYTYPE.HISTORYTYPEID = TBL_HISTORY.HISTORYTYPEID Inner Join TBL_CONTACT_HISTORY On TBL_HISTORY.HISTORYID = TBL_CONTACT_HISTORY.HISTORYID Inner Join TBL_USER On TBL_HISTORY.MANAGEUSERID = TBL_USER.USERID Inner Join (Select '{!RegistryValue, Suchbegriff01}' As Suchbegriff, Convert(Datetime,'{!RegistryValue, WP_Start}',104) As Startdatum, Convert(Datetime,'{!RegistryValue, WP_Ende}',104) As Enddatum) Parameter On Parameter.Suchbegriff = TBL_ACTIVITYTYPE.NAME Where TBL_CONTACT_HISTORY.CONTACTID = '{!ACTKontakt, GUID}' And TBL_HISTORY.STARTTIME >= Parameter.Startdatum And TBL_HISTORY.STARTTIME <= Parameter.Enddatum}  AS Zwei_Details RELATE 'hide_HistoryID' TO 'hide_HistoryID') AS Zwei_Details


Abfrage:
Select '{!RegistryValue, WP_Start, Kein Startdatum angegeben}' As Startdatum, '{!RegistryValue, WP_Ende, Kein Enddatum angegeben}' As Enddatum, '{!RegistryValue, Suchbegriff01, Kein Typ angegeben}' As Typ, '{!RegistryValue, Zahl01, Keinen Zahlenwert angegeben}' As [right_format#,##0*00_Stundensatz]


# Rubriken

Titel:
Zeigt Luftlinie in Kilometern zu ausgewählten Kontakten an

Bemerkung:
Entfernung des aktuellen Kontakt ({!ActContact, Kontakt}) zu ausgewählten anderen Kontakten, die Mitglieder in einer Gruppe "Entfernung Luftlinie" sind.

Die Berechnung der Entfernung (Luftlinie) setzt das Vorhandensein der GeoDaten in den Feldern "USER3" (Längengrad) und "USER4 (Breitengrad) bei allen beteiligten Kontakten voraus.


Bemerkung:
Zeigt vorhandene Beziehungen zu anderen Firmen an. Hierfür ist eine eingetragene Kontaktbeziehung (Register: Beziehungen) nötig. Die Verbindung wird auch in Datensätzen von anderen Kontakten der gleichen Firmen angezeigt.


Bemerkung:
Zeigt zum aktuellen Kontakt '{!ACTKontakt, Kontakt}' die gefilterten Protokolleinträge eines vorzugebenden Typs an.

Die Festlegung erfolgt über das AutoData-Script "SetKIParameter".

Für die Vorgabe des gefilterten Datumsbereiches, sowie den Stunden_Verrechnungssatz verwenden Sie ebenfalls das AutoData-Script "SetKIParameter".


