UDF Skripte

Währungskurse der ECB einlesen

Dieses Script liesst Währungskurse der ECB ein.

program wechselkurs;
type
  rWaehr = record
  waehrid:integer;
  ISOWaCode:string;
  WaCode:string;
  einheit:integer;
  end;
var
   WebConnector:TSQLConnector;
   M:TMemoryStream;
   rueckgabe:string;
   cGet:string;
   ADoc: TNativeXml;
   ANode,ANodeCubes: TXMLNode;
   cECBDate:string;
   cCurrency,cRate:string;
   i,nAID:integer; 
   aWaehr: array of rWaehr;
   nAdded,nUpdated:integer;
   
   function GetAID( ISOWaCode:string):integer;
   var i:integer;
   begin
     result := -1;
     for i := 0 to Length(aWaehr)-1 do
     begin
       if aWaehr[i].ISOWaCode = ISOWaCode then
       begin
         result := i; 
       end;
     end;
   
   end;
begin

  oQuery.SQL.Text := 'SELECT * FROM waehr';
  oQuery.open;
  while not oQuery.eof do
  begin
    i := Length(aWaehr);
    Setlength(aWaehr, i+1);
    aWaehr[i].waehrid   := oQuery.FieldByName('waehrid').AsInteger;
    aWaehr[i].ISOWaCode := oQuery.FieldByName('ISOWaCode').AsString;
    aWaehr[i].WaCode    := oQuery.FieldByName('WaCode').AsString;
    aWaehr[i].Einheit   := oQuery.FieldByName('Einheit').AsInteger;

    oQuery.Next;
  end;

  WebConnector := TSQLConnector.Create(nil);
  M := TMemoryStream.Create;

  ADoc := TNativeXml.Create;
  ADoc.Utf8Encoded := True;
  
  try
  
    with WebConnector do
    begin
      // Falls mit .htaccess user/pw
      auth     := False;
      authname := '';
      authpw   := '';

      ResetParams;

      cGet := 'http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml';
      
      if Get(cGet,M) then
      begin
        
        ADoc.LoadFromStream(M);
        ADoc.XmlFormat := xfReadable;
        writeln(ADoc.WriteToString);
        
        if assigned(ADoc.Root) then with ADoc.Root do
        begin
          ANode := NodeByName('Cube');
          if ANode <> nil then
          begin
            ANodeCubes := ANode.NodeByName('Cube');
            if ANodeCubes <> nil then
            begin
              cECBDate := ANodeCubes.AttributeByName['time']; 
              writeln( 'Datum: '+cECBDate ); 
              
              oQuery1.RequestLive := True;
              nAdded := 0;
              nUpdated := 0;
              
              if cECBDate <> '' then
              begin
                for i := 0 to ANodeCubes.NodeCount - 1 do 
                begin
                  cCurrency := ANodeCubes.Nodes[i].AttributeByName['currency'];
                  cRate := ANodeCubes.Nodes[i].AttributeByName['rate'];
                  
                  writeln(cCurrency+' :: '+cRate);
                  
                  nAID := GetAID(cCurrency);
                  if nAID > -1 then
                  begin
                  
                    oQuery1.SQL.Text := 'UPDATE waehr SET kurs='+cRate+
                     ' WHERE waehrid='+inttostr(aWaehr[nAID].waehrid);
                    oQuery1.ExecSQL;
                  
                    oQuery1.SQL.Text := 'SELECT * FROM waehrk'+
                                        ' WHERE waehrid='+inttostr( aWaehr[nAID].waehrid )+
                                        ' AND datum='+ datestring(date);
                    oQuery1.Open;
                    if oQuery1.RecordCount = 0 then
                    begin
                      oQuery1.SQL.Text := 'INSERT INTO waehrk '+
                                       '(waehrid,wacode,datum,einheit,kurs)VALUES('+
                                       inttostr(aWaehr[nAID].waehrid)+
                                       ','+quotedstring(aWaehr[nAID].wacode)+
                                       ','+datestring(date)+
                                       ','+inttostr(aWaehr[nAID].Einheit)+
                                       ','+cRate+
                                       ')';
                      oQuery1.ExecSQL;
                      nAdded := nAdded + 1;
                    end
                    else
                    begin
                      oQuery1.SQL.Text := 'UPDATE waehrk SET kurs='+cRate+
                                          ' WHERE waehrid='+inttostr(aWaehr[nAID].waehrid)+
                                          ' AND datum='+datestring(date);

                      oQuery1.ExecSQL;
                      nUpdated := nUpdated+1;
                    end;
                  end;
                end;
              end;
            end;
          end;
        end;

        ShowMeldung('Neue Tageskurse: '+inttostr(nAdded)+#13+#10+
                     'Tageskurs Updates: '+inttostr(nUpdated));
        
      end
      else 
      begin
        // nur bei Fehler
        rueckgabe := StreamToString(M);
        writeln(rueckgabe);
        ShowMeldung('Abruf nicht erfolgreich: '+rueckgabe);
      end;
    
    end;
    
  finally
    SetLength(aWaehr,0);
    oQuery.Close;
    oQuery1.Close;
    WebConnector.Close;
    M.Free;
    ADoc.Free;
  end;

end.