Fortuna Entwickler Blog

Zu den täglich anfallenden Aufgaben gehört es, Einstellungen zu myInfoPoint Benutzern zu ändern bzw. anzulegen. Dies geschieht derzeit noch direkt auf der Datenbank und wird damit von IT erledigt. Der Auftrag hierzu kommt üblicherweise von Service, also Versicherungsbetrieb.

Um Wartezeiten für Versicherungsbetrieb zu minimieren und gleichzeitig die Arbeiten aus IT auszulagern ist für die anfallenden Aufgaben eine Weboberfläche zu erstellen, um die Aufgaben durch Versicherungsbetrieb ausführen zu lassen.

1. Einstellen / Ändern der Sicht eines Benutzers in myInfoPoint

1.1 Abfrage der Aktuellen Sicht

1. Sicht aufgrund des zugrunde liegenden Vertriebsweges (der Kunde hat über einen Vermittler einen Versicherungsvertrag abgeschlossen und der Vermittler ist einem Vertriebsweg zugeordnet. Die Vertriebswege sind alle mit Sichten in myInfoPoint hinterlegt)
2. Sicht aufgrund des Wunsches des Vermittlers für alle seine Kunden (zum Vermittler wurde auf dessen Veranlassung eine Sicht hinterlegt, die sich vom Vertriebsweg unterscheiden kann)
3. Sicht aufgrund des Wunsches des Vermittlers für einen bestimmten Kunden (zum Kunden wurde auf Veranlassung des zugeordneten Vermittlers eine Sicht hinterlegt, die sich von den Sichten der anderen Kunden des Vermittlers unterscheiden kann)

Die aktuelle Einstellung zur Sicht erhält man z.B. über folgende Abfrage auf WEBDATA:

SELECT v.V_NR, r.P_NR, ov.VKVO, v.VERTRIEBSWEG_KZ, 
	NVL(msz1.SICHT_KZ, 0) AS SICHT_VERTRIEBSWEG, NVL(msz2.SICHT_KZ, 0) AS SICHT_VKVO, NVL(msz3.SICHT_KZ, 0) AS SICHT_P_NR
	FROM V@BSTDB v
	INNER JOIN OBJ_VM@BSTDB ov	ON v.V_NR = ov.OBJ_NR	AND ov.OBJ_KZ = 1	AND ov.HIST_KZ = 2
	INNER JOIN ROLLE@BSTDB r		ON v.V_NR = r.V_NR		AND r.R_KZ = 1
	LEFT OUTER JOIN MLS_SICHT_ZUORDNUNG msz1 ON	v.VERTRIEBSWEG_KZ = msz1.VERTRIEBSWEG_KZ
	LEFT OUTER JOIN MLS_SICHT_ZUORDNUNG msz2 ON	ov.VKVO = msz2.VKVO
	LEFT OUTER JOIN MLS_SICHT_ZUORDNUNG msz3 ON	r.P_NR = msz3.P_NR
	WHERE v.HIST_KZ = 2
	ORDER BY v.V_NR DESC;

INSERT INTO MLS_SICHT_ZUORDNUNG (ID, SICHT_KZ, P_NR, KOMMENTAR) VALUES (DEFAULT, [SICHT], [Kundennummer], '[Kommentar]');

INSERT INTO MLS_SICHT_ZUORDNUNG (ID, SICHT_KZ, VKVO, KOMMENTAR) VALUES (DEFAULT, [SICHT], [VKVO], '[Kommentar]');

2. Ändern eines Benutzernamens in myInfoPoint

Benutzer können über myInfoPoint eine Änderung Ihres Benutzernamens (= ihrer Emailadresse) beantragen. Dies bedeutet derzeit, daß die Emailadresse in VWS geändert durch Versicherungsbetrieb geändert wird und für myInfoPoint selbst durch IT geändert wird.

2.1 Prüfen des Benutzernamens

Um zu prüfen, ob der Benutzer in myInfoPoint existiert, nutzt man auf WEBSERVICES eine der folgenden Abfragen:

SELECT * FROM ASPNET_USERS au WHERE au.USERNAME = '[alter Username]';
SELECT * FROM ASPNET_USER_EXTENSIONS aue INNER JOIN ASPNET_USERS au ON aue.USERID = au.USERID WHERE aue.PNR = [Kundennummer];

UPDATE ASPNET_USER_EXTENSIONS aue SET aue.MAIL = '[neuer Username]' WHERE aue.USERID = (SELECT au.USERID FROM ASPNET_USERS au WHERE au.USERNAME = '[alter Username]');
UPDATE ASPNET_USERS aue SET aue.USERNAME = '[neuer Username]' WHERE aue.USERID = (SELECT au.USERID FROM ASPNET_USERS au WHERE au.USERNAME = '[alter Username]');

3. Technische Umsetzung

Ich habe mich im Rahmen der Restschuldversicherung für die von Essen Bank mal mit RESTful Services beschäftigt, da ich eine ganze Reihe an Daten an den Service übergeben muss und auch viele Daten zurückbekomme, liegt der Fokus erst einmal auf POST.

Das Ganze wollte ich erst einmal einfach halten, um bei etwaigen Fehlern nicht in Unmengen von Code zu suchen. Also habe ich erst einmal mit einem ganz kleinen Service begonnen.

In Visual Studio habe ich ein neues Projekt erstellt: Installed -> Visual C# -> WCF -> WCF Service Application. Im neuen Projekt bekommt man dann ein Interface und einen Service vorgegeben, die man nach eigenem Gusto benennen kann. In meinem Fall war dies hinsichtlich des eigentlichen Ziels Fortuna.RSV.Services

Im Interface (Fortuna.RSV.Services.IRSVCalc.cs) habe ich dann erst mal einen ganz einfachen Service angelegt:

        [OperationContract]
        [WebInvoke(Method = "POST",
            ResponseFormat = WebMessageFormat.Json,
            RequestFormat = WebMessageFormat.Json,
            BodyStyle = WebMessageBodyStyle.Bare,
            UriTemplate = "Berechnen")]
        int Berechnen(int i);

Der zugehörige Code (Fortuna.RSV.Services.RSVCalc.svc.cs) im Service selbst ist dazu denkbar einfach:

        public int Berechnen(int i)
        {
            return i * 2;
        }

In der Web.config ist der Service zudem noch einzutragen:

  <system.serviceModel>
    <services>
      <service name="Fortuna.RSV.Services.RSVCalc" behaviorConfiguration="serviceBehavior">
        <endpoint address="" binding="webHttpBinding" contract="Fortuna.RSV.Services.IRSVCalc" behaviorConfiguration="web"></endpoint>
      </service>
    </services>
    <behaviors>
      <endpointBehaviors>
        <behavior name="web">
          <webHttp />
        </behavior>
      </endpointBehaviors>
      <serviceBehaviors>
        <behavior name="serviceBehavior">
          <serviceMetadata httpGetEnabled="true" />
          <serviceDebug includeExceptionDetailInFaults="true" />
        </behavior>

Damit ist der Service auch schon fertig und grundsätzlich einsatzbereit. Man kann ihn schon im Visual Studio testen.

Nun wollte ich natürlich auch mal sehen, wie man den Service konsumieren kann. Natürlich geht das, indem man einen Request an den Service schickt und die Response auswertet. Anstatt alles 'von Hand' zu machen, habe ich mich entschieden, RestSharp zu verwenden. Informationen dazu findet man auf RestSharp.org und man kann das Package über nuget in sein Projekt einbinden.

Um das nun zu testen habe ich ein neues Projekt (einfach eine C# ClassLibrary) hinzugefügt und über nuget RestSharp in den Referenzen ergänzt. Das Projekt habe ich Fortuna.RSV.ServiceClient genannt, aus class1.cs wurde RSVService.cs:

    public class RSVService
    {
        private string _ServiceBaseUri;
        public RSVService()
        {
        }
        public RSVService(string Uri)
        {
            _ServiceBaseUri = Uri + "RSVCalc.svc/";
        }
        public int Berechnen(int Eingabe)
        {
            int Ausgabe = 0;
            var client = new RestClient(_ServiceBaseUri);
            var request = new RestRequest("Berechnen", Method.POST);
            request.RequestFormat = DataFormat.Json;
            request.AddBody(Eingabe);
            var response = client.Execute(request);
            if (response.ResponseStatus == ResponseStatus.Completed)
                Ausgabe = int.Parse(response.Content);
            return Ausgabe;
        }
    }

Was passiert hier in der Methode Berechnen?

Es wird ein RestClient erstellt; dies ist ein Objekt, welches von RestSharp zur Verfügung gestellt wird. Als Parameter wird die Uri des Service angegeben. Selbige wird im Konstruktor meiner Klasse übergeben. Anschliessend wird der RestRequest definiert. Auch dies ist ein Objekt von RestSharp. Hier geben wir schon an, wie die Methode heisst, die aufgerufen wird, in unserem Fall 'Berechnen', was dem Wert entspricht, der oben als 'UriTemplate' im Contract steht. Als Methode habe ich auch hier (wie im Contract) POST definiert. Ebenso analog zum Contract verwende ich JSON als Datenformat.

Der Parameter (int i) wird dem RequestBody hinzugefügt; RestSharp kümmert sich um die Serialisierung in JSON, was in diesem Fall denkbar wenig ist, aber für die eigentliche Zielsetzung durchaus von Belang ist.

Anschliessend wird der Request mit Execute ausgeführt und das Ergebnis (die Response) in einer Variablen abgelegt. Das Ergebnis des Service erhält man bei dieser Art des Aufrufs in der RestResponse-Property 'Content'. Fertig.

Hier würde natürlich erst mal alles ganz einfach gehalten: Ein integer wird übergeben und ein integer wird zurückgeliefert. Im nächsten Schritt wollte ich dann ein einfaches Objekt mit zwei integer-Werten liefern und einen integer zurückbekommen. Im Service-Interface sieht das dann so aus:

        [OperationContract]
        [WebInvoke(Method = "POST",
            ResponseFormat = WebMessageFormat.Json,
            RequestFormat = WebMessageFormat.Json,
            BodyStyle = WebMessageBodyStyle.Bare,
            UriTemplate = "Berechnen3")]
        int Berechnen3(BerechnenClass data);

Die Berechnen Class ist denkbar übersichtlich:

    [DataContract(Namespace="http://fortuna.mylife-leben.de/RSV")]
    public class BerechnenClass
    {
        [DataMember]
        public int parm1 { get; set; }
        [DataMember]
        public int parm2 { get; set; }
    }

Und auch den Code des Service habe ich einfach gehalten:

        public int Berechnen3(BerechnenClass data)
        {
            return data.parm1 * data.parm2;
        }

In der Web.config ist jetzt nichts weiter einzutragen; der Service ist dort ja bereits vorhanden; es kommt nur eine neue Methode dazu.

Im Client benötige ich natürlich auch das Objekt und der Code ist in grossen Teilen mit dem ersten identisch:

        public int Berechnen3(int parm1, int parm2)
        {
            BerechnenClass c = new BerechnenClass() { parm1 = parm1, parm2 = parm2 };
            int Ausgabe = 0;
            var client = new RestClient(_ServiceBaseUri);
            var request = new RestRequest("Berechnen3", Method.POST);
            request.RequestFormat = DataFormat.Json;
            request.AddBody(c);
            var response = client.Execute(request);
            if (response.ResponseStatus == ResponseStatus.Completed)
                Ausgabe = int.Parse(response.Content);
            return Ausgabe;
        }

Auch hier wieder: Client erstellen, Request erstellen, dann aber eben das zu übergebende Objekt dem RequestBody hinzufügen, die Serialisierung nach JSON wird von RestSharp übernommen, dann der Aufruf des Services und die Verarbeitung der Response. Fertig.

Nachdem auch dies funktioniert wollte ich nun das Objekt übergeben, welches zur Berechnung und als Ergebnis bei der Restschuldversicherung bereits vorhanden ist. Ein Klasse mit deutlich über 30 Parametern, sowohl integer als auch double Werte, also schon etwas größer als das, was ich hier bisher verwendet habe. Die Klasse heisst 'RSVdaten'

Das Interface ist ähnlich wie gehabt:

        [OperationContract]
        [WebInvoke(Method = "POST",
            ResponseFormat = WebMessageFormat.Json,
            RequestFormat = WebMessageFormat.Json,
            BodyStyle = WebMessageBodyStyle.Bare,
            UriTemplate = "Berechnen2")]
        RSVdaten Berechnen2(RSVdaten data);

Der Code im Service ist sehr einfach, weil die eigentliche Berechnung in C++ abgebildet ist:

        public RSVdaten Berechnen2(RSVdaten data)
        {
            RSVInterfaceUnmanaged.ClassRSVBer myClass = new ClassRSVBer();
            myClass.ber(data);
            return data;
        }

Während dies alles nicht weiter aufregend ist, gab es jetzt für den Client eine gewisse Herausforderung. Bisher haben die Methoden int zurückgeliefert, hier jetzt aber ein komplexes Objekt. RestSharp bietet hier eine schöne Variante des 'Execute' an:

        public RSVdaten Berechnen2(RSVdaten data)
        {
            RSVdaten Ausgabe = new RSVdaten();

            var client = new RestClient(_ServiceBaseUri);
            var request = new RestRequest("Berechnen2", Method.POST);
            request.RequestFormat = DataFormat.Json;
            request.AddBody(data);

            var response = client.Execute<RSVdaten>(request);
            if (response.ResponseStatus == ResponseStatus.Completed)
                Ausgabe = response.Data;

            return Ausgabe;
        }

Beim Execute gibt man bereits an, von welchem Typ man die Response erwartet. Den Inhalt findet man dann nciht im Parameter Content, sondern in Data, welcher dann schon vom angegeben Typ ist. Mehr ist auch hier nicht zu tun.

Jeden Monat benötigt Finanzen eine Reihe von Auswertungen. Diese gehen als Excel-Tabellen an Almut bzw. deren Vertretung.

BCAS schick üblicherweise zwischen dem 21. und 23. jeden Monats die Daten an den Service. Die Daten sind dann auf WEBDATA abgelegt. Alle relevanten Tabellen beginnen mit 'BCAS'.

Bevor die Auswertungen erstellt werden, muss der Status bei jedem Vertrag geprüft und ggf. aktualisiert werden. Dies passiert durch den Aufruf einer Stored Procedure:

EXEC BCAS_IO.BCAS_STATECHANGE();

Anschliessend können die Reports abgerufen werden. Das Ergebnis wird jeweils in einer Excel-Datei gespeichert; der Dateiname steht im Kommentar über dem jeweiligen Statement (MM_YYYY austauschen gegen Monat / Jahr)

-- Exportdatei: BCAS_POLICYOVERVIEW_MM_YYYY
SELECT * FROM BCAS_POLICYOVERVIEW ORDER BY UEBERMITTELT_AM, POLICYNUMBER;

-- Exportdatei: BCAS_PAYMENTOVERVIEW_MM_YYYY
SELECT * FROM BCAS_PAYMENTOVERVIEW ORDER BY UEBERMITTELT_AM, POLICYNUMBER;

-- Exportdatei: BCAS_PAYMENTOVERVIEW_BY_MONTH_MM_YYYY
SELECT * FROM BCAS_PAYMENTOVERVIEW_BY_MONTH;

-- Exportdatei: BCAS_POLICYOVERVIEW_MONTHLY_MM_YYYY
SELECT * FROM BCAS_POLICYOVERVIEW_MONTHLY bpm;

-- Exportdatei: Monatsübersicht_YYYY_MM

--Monatsübersicht: + Commission SK
SELECT
			TO_CHAR(bp.UEBERMITTELT_AM, 'YYYY-MM') AS Monat,
			SUM(bp."value") AS  "Gebuchte Beiträge",
      0 AS "Rücklaufleistung",
      0 AS "Todesfallleistung",
      SUM(ROUND(bp.IC_BCAS / 12, 0)) AS "Provision BCAS",
      SUM(ROUND(bp.IC_SK / 12, 0)) AS "Provision SK",
      SUM(ROUND((bp.IC_SK) / 12, 0)) +ROUND(SUM(bp."Diff"), 0) AS "Provision SK2",
--	ROUND(SUM(bp."Diff"), 0) AS "Diff",
			ROUND(SUM(bp.RE / 12), 0) AS "RV-Beiträge",
      0 AS "RV-Leistungen",
      ROUND(SUM(bp.RE) / 12 * 2.5 / 100, 0) AS "RV-Provisionen",
			ROUND(SUM(ROUND(bp.C, 0) / 12), 0) AS "Costs of Insurance"
	FROM BCAS_PAYMENTOVERVIEW bp
  GROUP BY TO_CHAR(bp.UEBERMITTELT_AM, 'YYYY-MM')--, BP1.STATE
  ORDER BY TO_CHAR(bp.UEBERMITTELT_AM, 'YYYY-MM')--, BP1.STATE
	;

Als weiteren Report gibt es eine Statusübersicht. Diese wird monatlich fortgeschrieben. Man führt also das nachfolgende SQL-Statement aus, kopiert den Report des Vormonats und fügt die Daten des neuen Monats hinzu:

-- Juli 2014
WITH SEL_WERTE AS (SELECT TO_DATE('31.07.2014', 'DD.MM.YYYY') AS SEL_DATE FROM DUAL)
SELECT  BS.ID AS STATEID, BS."name" AS STATE, COUNT(SEL.ID) AS ANZAHL, NVL(SUM(SEL2.PAYMENT), 0) AS PAYMENT, NVL(SUM(SEL.SUMASSURED), 0) AS SUMASSURED
  FROM  BCAS_STATE BS
  LEFT OUTER JOIN
  (
    SELECT BP.ID, BCAS_IO.BCAS_IP_STATE_BY_DATE(BI.ID, (SELECT SEL_DATE FROM SEL_WERTE)) AS STATEID, BI.SUMASSURED
      FROM  BCAS_TRANSFER BT
      INNER JOIN BCAS_POLICY BP ON BT.ID = BP.TRANSFERID
      INNER JOIN BCAS_INSURANCEPRODUCT BI ON BP.ID = BI.POLICYID
      WHERE BT.CREATINGDATE <= (SELECT SEL_DATE FROM SEL_WERTE)
  ) SEL ON BS.ID = SEL.STATEID
  LEFT OUTER JOIN
  (
    SELECT BP.POLICYID, SUM(BP."value") AS PAYMENT
      FROM BCAS_PAYMENT BP
      WHERE BP.BOOKINGDATE <= (SELECT SEL_DATE FROM SEL_WERTE)
      GROUP BY BP.POLICYID
  ) SEL2 ON SEL.ID = SEL2.POLICYID
GROUP BY BS.ID, BS."name"
ORDER BY BS.ID;

Wichtig ist es natürlich, den Monat bzw. das Datum in Zeile 1 anzupassen. Es ist immer das Datem des letzten Tages des Monats einzutragen.

Auswertung Tschechien.sql (12,46 kb)

Für den Datenaustausch zwischen BCAS (Tschechien) und myLife wurde die DataExchangeServices erstellt. Die Services werden von BCAS aufgerufen, Daten übermittelt und ggf. Daten abgefragt.

Die Quellen zu den Services findet man im TFS unter $/Fortuna/Services/DataExchangeServices

Die Datenbanktabellen sind unter WEBDATA, sowohl in der Entwicklungsumgebung, der Testumgebung als auch der Produktivumgebung verfügbar. Innner halb des Projektes im TFS gibt es ein SQL-Script, in welches man Änderungen an den Tabellen einarbeiten kann, bei Ausführung werden dann bisherige Daten gespeichert, die geänderten Tabellen geschrieben und anschließend wieder ggf. mit des alten Daten gefüllt.

Aufrufbar sind die Services unter
http://test.mylife-leben.de/Services/DataExchangeServices/BCAS.asmx (Test)
https://fortuna.mylife-leben.de/services/dataexchangeservices/bcas.asmx (Produktiv)

Produktiv genutzt werden Stand 1.9. die Services für policies und payments. Hier weden von BCAS nun monatlich, immer um den 20. eines Monats, Daten geliefert.