unit Unit1;

interface

uses
{$IFDEF LINUX}
 QForms,SysUtils,IniFiles,xmldom, XMLIntf, DBXpress, FMTBcd, SqlExpr, Provider, DB, DBClient,
  QDialogs, QMenus, QTypes, xercesxmldom, XMLDoc, QGrids, QDBGrids,
  QStdCtrls, QControls, QExtCtrls, QComCtrls, Classes,
  IdBaseComponent, IdComponent, IdTCPConnection, IdTCPClient, IdHTTP;
{$ENDIF}
{$IFDEF MSWINDOWS}
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBClient, ScktComp,  StdCtrls,
  ExtCtrls,DBGrids, Grids, xmldom,DateUtils,
  Menus, ComCtrls,IniFiles,XMLIntf, msxmldom, XMLDoc, DBXpress, SqlExpr,
  Provider, FMTBcd, IdBaseComponent,
  IdComponent, IdTCPConnection, IdTCPClient, IdHTTP;
{$ENDIF}

type
  TForm1 = class(TForm)
    XMLDocument1: TXMLDocument;
    MainMenu1: TMainMenu;
    Options1: TMenuItem;
    StatusBar1: TStatusBar;
    File1: TMenuItem;
    Refreshdatas1: TMenuItem;
    Panel2: TPanel;
    Splitter1: TSplitter;
    GroupBox1: TGroupBox;
    Memo1: TMemo;
    GroupBox2: TGroupBox;
    Memo2: TMemo;
    Splitter2: TSplitter;
    Help1: TMenuItem;
    N1: TMenuItem;
    GroupBox4: TGroupBox;
    DBGrid3: TDBGrid;
    Splitter5: TSplitter;
    DBGrid4: TDBGrid;
    DataSource3: TDataSource;
    DataSource4: TDataSource;
    OpenFirebirdFile1: TMenuItem;
    Firebirdsuser1: TMenuItem;
    OpenDialog2: TOpenDialog;
    toFirebirdFile1: TMenuItem;
    LoadfromInternet1: TMenuItem;
    OpenDialog3: TOpenDialog;
    SQLConnection1: TSQLConnection;
    ClientDataSet1: TClientDataSet;
    DataSetProvider1: TDataSetProvider;
    SQLQuery1: TSQLQuery;
    ClientDataSet2: TClientDataSet;
    DataSetProvider2: TDataSetProvider;
    SQLQuery2: TSQLQuery;
    SQLQuery3: TSQLQuery;
    SQLDataSet1: TSQLDataSet;
    IdHTTP1: TIdHTTP;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure WriteToIniFile;
    procedure OpenFirebirdFile;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Refreshdatas1Click(Sender: TObject);
    procedure Help1Click(Sender: TObject);
    procedure OpenFirebirdFile1Click(Sender: TObject);
    procedure FormResize(Sender: TObject);
    procedure toFirebirdFile1Click(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure Firebirdsuser1Click(Sender: TObject);
    procedure LoadfromInternet1Click(Sender: TObject);

  private
    { Private declarations }
     ExchangeFileName,
     FirebirdFileName : string;
     AccessToFirebird : Boolean;
  public
    { Public declarations }
     UserName,
     Password         : string;
  end;

var
  Form1: TForm1;

implementation

{$IFDEF MSWINDOWS}
{$R *.dfm}
{$ENDIF}
{$IFDEF LINUX}
{$R *.xfm}
{$ENDIF}

uses Unit3, Math, Unit2;

procedure TForm1.toFirebirdFile1Click(Sender: TObject);
var XMLfile:string;
    i,j,n,t:Integer;
    ExDate:string;
    NumCode:Integer;
    CharCode:string;
    Nominal:Integer;
    Name,Value:string;
    FileStrm: TFileStream;
begin
 Memo1.Clear;
 Memo2.Clear;
 if Length(Trim(Form1.FirebirdFileName))=0
 then
 begin
  ShowMessage('Please open Firebird file with exchange datas');
  Exit;
 end;

 NumCode:=0;
 Nominal:=-1;
 Value:='';

 Memo1.Clear;
 Memo2.Clear;
 Application.ProcessMessages;
 XMLDocument1.Active:=false;
 XMLfile:=ExtractFilePath(Application.ExeName)+'daily.xml';

 if LoadfromInternet1.Checked
 then
 begin
  N1.Enabled:=false;

  DeleteFile(XMLfile);
  FileStrm := TFileStream.Create(XMLfile, fmCreate);
  try
   try
    IdHTTP1.Get('http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml', FileStrm);
    if FileStrm.Size = 0
    then
    begin
     ShowMessage('Connection error');
     N1.Enabled:=true;
     Exit;
    end
   except
    on E:Exception do
    begin
     ShowMessage(E.Message);
     N1.Enabled:=true;
     Exit;
    end;
   end;
  finally;
     FileStrm.Free;
  end;
 end
 else
   if OpenDialog3.Execute
   then
   begin
    if FileExists(OpenDialog3.FileName)
    then
    begin
     if MessageDlg('ARE YOU SURE TO LOAD XML FILE?',
       mtConfirmation, [mbYes, mbNo], 0) = mrYes
     then XMLfile:=OpenDialog3.FileName
     else Exit;
    end
    else Exit;
   end
   else Exit;

  N1.Enabled:=false;
  try
   XMLDocument1.FileName:=XMLfile;
   XMLDocument1.Active:=true;
   Memo1.Lines:=XMLDocument1.XML;
   n:=0;
   while  n<=XMLDocument1.DocumentElement.ChildNodes.Count-1 do
   begin
    if Sametext('Cube',XMLDocument1.DocumentElement.ChildNodes[n].NodeName)then Break;
    Inc(n);
   end;
   if not(Sametext('Cube',XMLDocument1.DocumentElement.ChildNodes[n].NodeName))
   then
   begin
    ShowMessage('Bad file format!!!');
    N1.Enabled:=true;
    Exit;
   end;

   for i:=0 to XMLDocument1.DocumentElement.ChildNodes[n].ChildNodes.Count-1 do
   begin
    if Sametext('Cube',XMLDocument1.DocumentElement.ChildNodes[n].ChildNodes[i].NodeName)
    then
     if Sametext('time',XMLDocument1.DocumentElement.ChildNodes[n].ChildNodes[i].AttributeNodes.Nodes[0].NodeName)
     then
     begin
      DateSeparator:='-';
      ShortDateFormat:='yyyy/MM/dd';
      ExDate:=XMLDocument1.DocumentElement.ChildNodes[n].ChildNodes[i].AttributeNodes.Nodes[0].NodeValue;
      DateSeparator:='/';
      ShortDateFormat:='MM/dd/yyyy';
      Memo2.Lines.Add('European Central Bank');
      Memo2.Lines.Add('Date: '+ExDate);
      GroupBox2.Caption:=('Date: '+ExDate);
      Memo2.Lines.Add('1 EURO reference');

      for j:=0 to XMLDocument1.DocumentElement.ChildNodes[n].ChildNodes[i].ChildNodes.Count-1 do
       if Sametext('Cube',XMLDocument1.DocumentElement.ChildNodes[n].ChildNodes[i].ChildNodes[j].NodeName)
       then
       begin
        for t:=0 to XMLDocument1.DocumentElement.ChildNodes[n].ChildNodes[i].ChildNodes[j].AttributeNodes.Count-1 do
        begin
         if Sametext('currency',XMLDocument1.DocumentElement.ChildNodes[n].ChildNodes[i].ChildNodes[j].AttributeNodes[t].NodeName)
         then CharCode:=XMLDocument1.DocumentElement.ChildNodes[n].ChildNodes[i].ChildNodes[j].AttributeNodes[t].NodeValue
         else
         if Sametext('rate',XMLDocument1.DocumentElement.ChildNodes[n].ChildNodes[i].ChildNodes[j].AttributeNodes[t].NodeName)
         then Value:=XMLDocument1.DocumentElement.ChildNodes[n].ChildNodes[i].ChildNodes[j].AttributeNodes[t].NodeValue;
        end;

        CharCode:=CharCode+'        ';
        SQLQuery3.Active:=false;
        SQLQuery3.SQL.Text:='select "Currencies"."NumCode","Currencies"."CurrName",'+
          '"Currencies"."CharCode" from "Currencies"'+
          'WHERE "Currencies"."CharCode"='+''''+CharCode+'''';
        SQLQuery3.Active:=true;

        if not(SQLQuery3.EOF)
        then
        begin
         NumCode:=SQLQuery3.Fields[0].AsInteger;
         Name:=SQLQuery3.Fields[1].AsString;
        end
        else
        begin
         ShowMessage('Unknown Currency: '+CharCode);
         N1.Enabled:=true;
         Exit;
        end;

        Memo2.Lines.Add('------------------------------------');
        Memo2.Lines.Add('NumCode: '+IntToStr(NumCode));
        Memo2.Lines.Add('CharCode: '+CharCode);
        Memo2.Lines.Add('Name: '+Name);
        Memo2.Lines.Add('Nominal: '+IntToStr(Nominal));
        Memo2.Lines.Add('Value: '+Value);

        try
         SQLDataSet1.CommandText:='insert into "Valute"("RefNumCode","NumCode",'+
         '"ExDate","ExNominal","ExValue") '+
         'values( 978,'+ IntToStr(NumCode)+','''+ExDate+''','+IntToStr(Nominal)+','+ Value+')';
         SQLDataSet1.ExecSQL();
        except
         SQLDataSet1.Cancel;
        end;
       end;
     end;
   end;
  except
   on E:Exception do ShowMessage(E.Message )
  end;
 OpenFirebirdFile;
 Application.ProcessMessages;
 N1.Enabled:=true;
end;
//---------------------------------------------------------------------------
procedure TForm1.FormCreate(Sender: TObject);
var s,dir   : string;
    i       : Integer;
    AppIni  : TIniFile;
    str1,
    str2    : TStringList;
begin
{$IFDEF LINUX}
 PixelsPerInch:=81;
 try
  dir:=ExtractFilePath(Application.ExeName)+ChangeFileExt(ExtractFileName(Application.ExeName),'.ico');
  if FileExists(dir) then Form1.Icon.LoadFromFile(dir);
 except
 end;
{$ENDIF}

 OpenDialog2.Filter :='Firebird files (*.FDB)|*.FDB|';
 OpenDialog3.Filter :='XML files (*.xml)|*.xml|';
 ExchangeFileName:='';
 FirebirdFileName:='';
 DecimalSeparator:='.';
 DateSeparator:='/';
 TimeSeparator:=':';
 ThousandSeparator:=' ';
 ShortDateFormat:='MM/dd/yyyy';
 ShortTimeFormat:='hh:mm:ss AMPM';
 CurrencyString:='';
 CurrencyFormat:=0;
 NegCurrFormat:=2;
 CurrencyDecimals:=4;
 UserName:='sysdba';
 Password:='masterkey';

 AppIni:=nil;
 str1:=nil;
 str2:=nil;
 dir:=ExtractFilePath(Application.ExeName)+ChangeFileExt(ExtractFileName(Application.ExeName),'.ini');
 if not(FileExists(dir))
 then WriteToIniFile;
 try
  str1:=TStringList.Create;
  str2:=TStringList.Create;
  try
   AppIni := TIniFile.Create(dir);
   AppIni.ReadSection('FormPos',str1);
   AppIni.ReadSectionValues('FormPos',str2);
   if str2.Count=6
   then
    for i:=0 to 5 do
    begin
     s:=str2.Strings[i];
     Delete(s,1,Pos('=',s));
     str2.Strings[i]:=s;

     if SameText('Top',str1.Strings[i])
     then try Top:=StrToInt(str2.Strings[i]) except end;
     if SameText('Left',str1.Strings[i])
     then try Left:=StrToInt(str2.Strings[i]) except end;
     if SameText('Height',str1.Strings[i])
     then try Height:=StrToInt(str2.Strings[i]) except end;
     if SameText('Width',str1.Strings[i])
     then try Width:=StrToInt(str2.Strings[i]) except end;
     if SameText('Panel2Height',str1.Strings[i])
     then try Panel2.Height:=StrToInt(str2.Strings[i]) except end;
     if SameText('GroupBox2With',str1.Strings[i])
     then try GroupBox2.Width:=StrToInt(str2.Strings[i]) except end;
    end;
   AppIni.ReadSection('Paths',str1);
   AppIni.ReadSectionValues('Paths',str2);
   for i:=0 to str2.Count-1 do
   begin
    s:=str2.Strings[i];
    Delete(s,1,Pos('=',s));
    str2.Strings[i]:=s;
    if SameText('ExchangeFileName',str1.Strings[i])
    then try ExchangeFileName:=str2.Strings[i] except end;
    if SameText('FirebirdFileName',str1.Strings[i])
    then try FirebirdFileName:=str2.Strings[i] except end;
   end;

   AppIni.ReadSection('Firebird',str1);
   AppIni.ReadSectionValues('Firebird',str2);
   if str2.Count=2
   then
    for i:=0 to 1 do
    begin
     s:=str2.Strings[i];
     Delete(s,1,Pos('=',s));
     str2.Strings[i]:=s;

     if SameText('UserName',str1.Strings[i])
     then UserName:=str2.Strings[i];
     if SameText('Password',str1.Strings[i])
     then Password:=str2.Strings[i];
    end;
  finally
   AppIni.Free;
  end;
 finally
  str1.Free;
  str2.Free;
 end;

 OpenFirebirdFile;
end;
//---------------------------------------------------------------------------
procedure TForm1.FormDestroy(Sender: TObject);
begin
end;
//---------------------------------------------------------------------------
procedure TForm1.OpenFirebirdFile1Click(Sender: TObject);
begin
 if OpenDialog2.Execute
 then
 if FileExists(OpenDialog2.FileName)
 then
 if MessageDlg('ARE YOU SURE ?',
   mtConfirmation, [mbYes, mbNo], 0) = mrYes
 then
 begin
  Form1.FirebirdFileName:=OpenDialog2.FileName;
  OpenFirebirdFile;
 end;
end;
//---------------------------------------------------------------------------
procedure TForm1.WriteToIniFile;
var
 f    : TextFile;
 dir  : string;
begin
 dir:=ExtractFilePath(Application.ExeName)+ChangeFileExt(ExtractFileName(Application.ExeName),'.ini');
 try
  AssignFile(f,dir);
  Rewrite(f);
  Writeln(f,'[FormPos]');
  Writeln(f,'Top='+IntToStr(Top));
  Writeln(f,'Left='+IntToStr(Left));
  Writeln(f,'Height='+IntToStr(Height));
  Writeln(f,'Width='+IntToStr(Width));
  Writeln(f,'Panel2Height='+IntToStr(Panel2.Height));
  Writeln(f,'GroupBox2With='+IntToStr(GroupBox2.Width));
  Writeln(f,'[Paths]');
  Writeln(f,'ExchangeFileName='+ExchangeFileName);
  Writeln(f,'FirebirdFileName='+FirebirdFileName);
  Writeln(f,'[Firebird]');
  Writeln(f,'UserName='+UserName);
  Writeln(f,'Password='+Password);
 finally
  CloseFile(f);
 end;
end;
//---------------------------------------------------------------------------
procedure TForm1.OpenFirebirdFile;
begin
 try
  StatusBar1.SimpleText:='';

  SQLQuery1.DisableControls;
  SQLQuery2.DisableControls;
  ClientDataSet1.DisableControls;
  ClientDataSet2.DisableControls;

  SQLQuery1.Active:=false;
  SQLQuery2.Active:=false;
  ClientDataSet1.Active:=false;
  ClientDataSet2.Active:=false;

  SQLConnection1.Connected:=false;
  SQLConnection1.LoginPrompt:=false;

  SQLConnection1.ConnectionName:='IBConnection';
  SQLConnection1.DriverName:='Interbase';
  SQLConnection1.GetDriverFunc:='getSQLDriverINTERBASE';
{$IFDEF LINUX}
  SQLConnection1.LibraryName:='libsqlib.so';
{$ENDIF}
{$IFDEF MSWINDOWS}
  SQLConnection1.LibraryName:='dbexpint.dll';
{$ENDIF}
  SQLConnection1.Params.Clear;
  SQLConnection1.Params.Add('DriverName=Interbase');
  SQLConnection1.Params.Add('BlobSize=-1');
  SQLConnection1.Params.Add('CommitRetain=False');
  SQLConnection1.Params.Add('ErrorResourceFile=');
  SQLConnection1.Params.Add('LocaleCode=0000');
  SQLConnection1.Params.Add('RoleName=RoleName');
  SQLConnection1.Params.Add('ServerCharSet=');
  SQLConnection1.Params.Add('SQLDialect=3');
  SQLConnection1.Params.Add('Interbase TransIsolation=ReadCommited');
  SQLConnection1.Params.Add('WaitOnLocks=True');
  SQLConnection1.Params.Add('user_name='+Form1.UserName);
  SQLConnection1.Params.Add('password='+Form1.Password);

  if Length(Trim(Form1.FirebirdFileName))=0
  then
  begin
   SQLQuery1.EnableControls;
   SQLQuery2.EnableControls;
   ClientDataSet1.EnableControls;
   ClientDataSet2.EnableControls;
   Exit;
  end;

  SQLConnection1.Params.Add('Database='+Form1.FirebirdFileName);

  SQLQuery1.SQL.Text:=
   'SELECT "Currencies"."NumCode","Currencies"."CharCode","Currencies"."CurrName" '+
   'FROM "Currencies" '+
   'GROUP BY "Currencies"."CurrName","Currencies"."CharCode","Currencies"."NumCode"';

  SQLQuery2.SQL.Text:=
   'SELECT "Valute"."RefNumCode", "Valute"."NumCode", "Valute"."ExDate",'+
     '"Valute"."ExNominal", "Valute"."ExValue" '+
   'FROM "Valute" GROUP BY "Valute"."RefNumCode", "Valute"."NumCode", "Valute"."ExDate",'+
     '"Valute"."ExNominal", "Valute"."ExValue" ';

  SQLConnection1.Connected:=true;

  SQLQuery1.Active:=true;
  ClientDataSet1.Active:=true;
  SQLQuery2.Active:=true;
  ClientDataSet2.Active:=true;
  SQLQuery1.EnableControls;
  SQLQuery2.EnableControls;
  ClientDataSet1.EnableControls;
  ClientDataSet2.EnableControls;

  StatusBar1.SimpleText:=Form1.FirebirdFileName;
 except
  on E:Exception do
  begin
   ShowMessage('OpenFirebirdFile -> '+E.Message);
   Form1.ExchangeFileName:='';
   Exit;
  end;
 end;
end;
//---------------------------------------------------------------------------
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
 WriteToIniFile;
end;
//---------------------------------------------------------------------------
procedure TForm1.Refreshdatas1Click(Sender: TObject);
begin
 OpenFirebirdFile;
end;
//---------------------------------------------------------------------------
procedure TForm1.Help1Click(Sender: TObject);
begin
 Form2.Memo1.Clear;
 Form2.Memo1.Lines.Add('Owner Info = © CIOINA ALEXEI, e-mail:<cioina@usm.md, cioina@hotmail.com>');
 Form2.Memo1.Lines.Add('Version Info = EURO rates loader v1.0.6 for Linux and Windows');
 Form2.Memo1.Lines.Add('');
 Form2.Memo1.Lines.Add('URL1 = http://www.geocities.com/alexei_cioina/calculator.html');
 Form2.Memo1.Lines.Add('URL2 = http://groups.yahoo.com/group/applied_mathematics/');
 Form2.Memo1.Lines.Add('');
 Form2.Memo1.Lines.Add('Load XML file manually from http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml');
 Form2.Show
end;
//---------------------------------------------------------------------------
procedure TForm1.FormResize(Sender: TObject);
begin
 StatusBar1.Panels[0].Width:=Trunc(Form1.Width/2);
end;
//---------------------------------------------------------------------------
procedure TForm1.N1Click(Sender: TObject);
begin
 AccessToFirebird:=false;
end;
//---------------------------------------------------------------------------
procedure TForm1.Firebirdsuser1Click(Sender: TObject);
begin
 Form3.Edit1.Text:=UserName;
 Form3.Edit2.Text:=Password;
 Form3.ShowModal;
end;
//---------------------------------------------------------------------------
procedure TForm1.LoadfromInternet1Click(Sender: TObject);
begin
 if LoadfromInternet1.Checked
 then LoadfromInternet1.Checked:=false
 else LoadfromInternet1.Checked:=true;
end;
//---------------------------------------------------------------------------
end.
