unit DataBaseConnect;
interface
uses
Windows, SysUtils, Variants, Classes,controls,
Dialogs, ComCtrls, IBODataset, IB_Components, IB_Session,IB_Dialogs,UKunde,UBuecher
,Usendungsverfolgung,Autoren,URechnung;
type TDataconnect = Class(TObject)
private
FDataBase : TIBODatabase;
function IsConnected() : Boolean;
public
function getKunden():TKUndeList;
function getBuecher():TBuchList;
function getSendung():TSendungList;
function getSendungByKunde(AKunde:TKunde): TSendungList;
function getAutor(): TAutorList;
procedure SaveKunde(AKunde : TKunde);
procedure SaveSendung(ASendung: TSendung);
procedure SaveAutor(AAutor: TAutor);
procedure SaveBuch(Abuch: TBuch);
procedure DeleteKunde (Akunde: TKunde);
procedure DeleteBuecher (ABuch: TBuch);
procedure DeleteSendung (Asendung:TSendung);
procedure DeleteAutor (AAutor:TAutor);
procedure LegeKundeAn (Akunde: TKunde);
procedure LegeBuchAn (ABuch:TBuch);
procedure LegeAutorAn (AAutor:TAutor);
procedure LegeSendungAn (ASendung:TSendung);
procedure ORDERbyID (AKunde:TKunde);
procedure getPreis (Asendung:Tsendung);
constructor create(User,Passw,Server,Path: string);
property Connected : Boolean read IsConnected;
end;
implementation
uses DateUtils, Grids, Forms,
DB;
{ TDataconnect }
constructor TDataconnect.create(User,Passw,Server,Path: string);
begin
FDataBase := TIBODatabase.Create(nil);
FDataBase.Username := User;
FDataBase.Password := Passw;
FDataBase.Server := Server;
FDataBase.Path := path;
FDataBase.Protocol := cpTCP_IP;
try
FDataBase.Connect()
except
on e:
Exception do
begin
ShowMessage('Verbindung fehlgeschlagen. Überprüfen Sie ihre Login Daten.');
end;
end;
end;
function TDataconnect.getKunden():TKundeList;
var
Kunde : TKunde;
KundeQuery : TIBOQuery;
KundeTransa : TIBOTransaction;
begin
Result:= TKundeList.create();
KundeQuery:= TIBOQuery.Create(nil);
KundeQuery.SQL.Text:= 'SELECT * FROM KUNDEN ';
KundeQuery.IB_Connection:= FDataBase;
KundeTransa:= TIBOTRansaction.Create(nil);
KundeTransa.IB_Connection:= FDataBase;
KundeQuery.IB_Transaction:= KundeTransa;
KundeTransa.StartTransaction();
try
KundeQuery.Open();
while not KundeQuery.Eof do
begin
kunde:= TKunde.Create(KundeQuery.Fieldbyname('Kunden_ID').asInteger,
KundeQuery.FieldbyName('Vorname').asString,
KundeQuery.FieldbyName('nachname').AsString);
Result.addkunde(Kunde);
KundeQuery.Next();
end;
KundeTransa.Commit();
except
on e:
Exception do
begin
ShowMessage('FEHLER IN GETKUNDEN');
KundeTransa.Rollback();
end;
end;
end;
function TDataconnect.getBuecher():TBuchList;
var
Buch : TBuch;
BuchQuery : TIBOQuery;
BuchTransa : TIBOTransaction;
begin
Result:= TBuchList.create();
BuchQuery:= TIBOQuery.Create(nil);
BuchQuery.SQL.Text:= 'SELECT * FROM BUECHER ';
BuchQuery.IB_Connection:= FDataBase;
BuchTransa:= TIBOTRansaction.Create(nil);
BuchTransa.IB_Connection:= FDataBase;
BuchQuery.IB_Transaction:= BuchTransa;
BuchTransa.StartTransaction();
try
BuchQuery.Open();
while not BuchQuery.Eof do
begin
Buch:= TBuch.Create (BuchQuery.Fieldbyname('Buch_ID').asInteger,
BuchQuery.FieldbyName('Preise').asInteger,
BuchQuery.FieldbyName('Name').asString,);
Result.addBuch(Buch);
BuchQuery.Next();
end;
BuchTransa.Commit();
except
on e:
Exception do
begin
ShowMessage('FEHLER IN GETBUECHER');
BuchTransa.Rollback();
end;
end;
end;
function TDataconnect.getSendung():TSendungList;
var
Sendung : TSendung;
SendungQuery : TIBOQuery;
SendungTransa : TIBOTransaction;
begin
Result:= TSendungList.create();
SendungQuery:= TIBOQuery.Create(nil);
SendungQuery.SQL.Text:= 'SELECT * from Sendungsverfolgung';
SendungQuery.IB_Connection:= FDataBase;
SendungTransa:= TIBOTRansaction.Create(nil);
SendungTransa.IB_Connection:= FDataBase;
SendungQuery.IB_Transaction:= SendungTransa;
SendungTransa.StartTransaction();
try
SendungQuery.Open();
while not SendungQuery.Eof do
begin
Sendung:= TSendung.Create(SendungQuery.Fieldbyname('Sendungsverfolgung_ID').asInteger,
SendungQuery.FieldbyName('Status').asString,
SendungQuery.FieldbyName('Rechnung').AsString);
Result.addSendung(Sendung);
SendungQuery.Next();
end;
SendungTransa.Commit();
except
on e:
Exception do
begin
ShowMessage('FEHLER IN GET SENDUNG');
SendungTransa.Rollback();
end;
end;
end;
function TDataconnect.getAutor():TAutorList;
var
Autor : TAutor;
AutorQuery : TIBOQuery;
AutorTransa : TIBOTransaction;
begin
Result:= TAutorList.create();
AutorQuery:= TIBOQuery.Create(nil);
AutorQuery.SQL.Text:= 'SELECT * FROM Autoren ';
AutorQuery.IB_Connection:= FDataBase;
AutorTransa:= TIBOTRansaction.Create(nil);
AutorTransa.IB_Connection:= FDataBase;
AutorQuery.IB_Transaction:= AutorTransa;
AutorTransa.StartTransaction();
try
AutorQuery.Open();
while not AutorQuery.Eof do
begin
Autor:= TAutor.Create(AutorQuery.Fieldbyname('Autor_ID').asInteger,
AutorQuery.FieldbyName('Vorname').asString,
AutorQuery.FieldbyName('nachname').AsString);
Result.addautor(Autor);
AutorQuery.Next();
end;
AutorTransa.Commit();
except
on e:
Exception do
begin
ShowMessage('fehler in get Autor');
AutorTransa.Rollback();
end;
end;
end;
function TDataconnect.IsConnected: Boolean;
begin
Result:= self.FDataBase.Connected;
end;
procedure TDataconnect.SaveKunde(AKunde: TKunde);
var
SaveKuQuery : TIBOQuery;
SaveKuTransa : TIBOTransaction;
begin
SaveKuQuery:= TIBOQuery.Create(nil);
SaveKuQuery.IB_Connection:= FDataBase;
SaveKuTransa:= TIBOTRansaction.Create(nil);
SaveKuTransa.IB_Connection:= FDataBase;
SaveKuQuery.SQL.Text := 'UPDATE Kunden k SET K.Vorname = '+QuotedStr(AKunde.Vorname)+','+
'K.Nachname ='+QuotedStr(AKunde.Nachname)+' WHERE k.Kunden_ID = '''+(inttoStr(AKunde.ID))+ ''';';
SaveKuQuery.ExecSQL();
SaveKuTransa.Commit;
SaveKuQuery.Free;
SaveKuTransa.Free;
end;
procedure TDataconnect.deleteKunde(AKunde: TKunde);
var
delKuQuery: TIBOQuery;
delKuTransa: TIBOTransaction;
begin
delKuQuery:= TIBOQuery.Create(nil);
try
delKuQuery.IB_Connection:=FDataBase;
delKuTransa:= TIBOTransaction.Create(nil);
try
delKuTransa.IB_Connection:= FDataBase;
delKuQuery.IB_Connection:=delKuQuery.IB_Connection;
try
delKuQuery.SQL.Text :=' DELETE FROM Sendungsverfolgung S WHERE s.Kunden_ID = :id';
delKuQuery.ParamByName('id').Value := AKunde.ID;
delKuQuery.ExecSQL();
delKuQuery.SQL.Text :=' DELETE FROM RECHNUNGEN R WHERE r.Kunden_ID = :id';
delKuQuery.ParamByName('id').value := AKunde.ID;
delKuQuery.ExecSQL();
delKuQuery.SQL.Text :=' DELETE FROM Kunden K WHERE k.Kunden_ID = :id';
delKuQuery.ParamByName('id').Value := AKunde.ID;
delKuQuery.ExecSQL();
delKuTransa.Commit;
except
on E:
Exception do
begin
ShowMessage('FEHLER IN deleteKunde');
delKuTransa.Rollback;
end;
end;
finally
delKuTransa.Free;
end;
finally
delKuQuery.Free;
end;
end;
procedure TDataconnect.SaveAutor(AAutor: TAutor);
var
SaveAuQuery : TIBOQuery;
SaveAuTransa : TIBOTransaction;
begin
SaveAuQuery:= TIBOQuery.Create(nil);
SaveAuQuery.IB_Connection:= FDataBase;
SaveAuTransa:= TIBOTRansaction.Create(nil);
SaveAuTransa.IB_Connection:= FDataBase;
SaveAuQuery.SQL.Text := 'UPDATE Autoren A SET A.Vorname = '+QuotedStr(AAutor.Vorname)+','+
'A.Nachname ='+QuotedStr(AAutor.Nachname)+' WHERE A.Autor_ID = '''+(inttoStr(AAutor.ID))+ ''';';
SaveAuQuery.ExecSQL();
SaveAuTransa.Commit;
SaveAuQuery.Free;
SaveAuTransa.Free;
end;
procedure TDataconnect.SaveBuch(Abuch: TBuch);
var
SaveBuQuery : TIBOQuery;
SaveBuTransa : TIBOTransaction;
begin
SaveBuQuery:= TIBOQuery.Create(nil);
SaveBuQuery.IB_Connection:= FDataBase;
SaveBuTransa:= TIBOTRansaction.Create(nil);
SaveBuTransa.IB_Connection:= FDataBase;
try
SaveBuQuery.SQL.Text := 'UPDATE Buecher B SET B.Preise = '+inttoStr(Abuch.Preis)+','+
'B.Name ='+QuotedStr(Abuch.name)+' WHERE B.Buch_ID = '+(inttoStr(Abuch.ID));
SaveBuQuery.ExecSQL();
SaveBuTransa.Commit;
except
on e:
Exception do
begin
showMessage ('FEHLER IN SAve buch: '+ e.message);
SaveButransa.Rollback;
end;
end;
SaveBuQuery.Free;
SaveBuTransa.Free;
end;
procedure TDataconnect.SaveSendung(ASendung: TSendung);
var
SaveSeQuery : TIBOQuery;
SaveSeTransa : TIBOTransaction;
begin
SaveSeQuery:= TIBOQuery.Create(nil);
SaveSeQuery.IB_Connection:= FDataBase;
SaveSeTransa:= TIBOTRansaction.Create(nil);
SaveSeTransa.IB_Connection:= FDataBase;
SaveSeQuery.SQL.Text := 'UPDATE Sendungsverfolgung S SET S.Status = '+QuotedStr(ASendung.Status)+','+
'S.Rechnung ='+QuotedStr(ASendung.Rechnung)+' WHERE S.Sendungsverfolgung_ID = '''+(inttoStr(ASendung.ID))+ ''';';
SaveSeQuery.ExecSQL();
SaveSeTransa.Commit;
SaveSeQuery.Free;
SaveSeTransa.Free;
end;
procedure TDataconnect.LegeKundeAn(Akunde: TKunde);
var
neuerKuQuery: TIBOQuery;
neuerKuTransa:TIBOTransaction;
begin
neuerKuQuery:= TIBOQuery.Create(nil);
neuerKuTransa:= TIBOTransaction.Create(nil);
neuerKuTransa.IB_Connection:=neuerKuQuery.IB_Connection ;
try
neuerKuQuery.IB_Connection:= FDataBase;
neuerKuTransa.IB_Connection:= FDatabase;
try
neuerKuQuery.SQL.Text:= 'INSERT INTO KUNDEN (Vorname,Nachname) Values ('+QuotedStr(Akunde.Vorname)+','+QuotedStr(Akunde.Nachname)+') returning Kunden_ID';
neuerKuQuery.ExecSQL;
Akunde.ID := neuerKuQuery.ParamByName('Kunden_ID').AsInteger;
neuerKuTransa.Commit;
Except
on E :
Exception do
begin
ShowMessage('FEHLER IN Lege Kunde AN');
neuerKuTransa.Rollback;
end;
end;
Finally
neuerKuQuery.Free;
neuerKuTransa.Free;
end;
end;
procedure TDataconnect.ORDERbyID(AKunde: TKunde);
var
groupKuQuery : TIBOQuery;
groupKuTrans : TIBOTransaction;
begin
groupKuQuery:=TIBOQuery.Create(nil);
groupKuTrans:=TIBOTransaction.Create(nil);
try
groupKuQuery.IB_Connection:= FDataBase;
groupKuTrans.IB_Connection:=FDataBase;
try
groupKuQuery.SQL.Text:= 'Select * FROM KUNDEN ORDER BY Kunden_ID';
groupKuQuery.ExecSQL;
groupKuTrans.Commit;
except
on E:
Exception do
begin
showMessage ('FEHLER IN ORDERBYID AN');
groupKuTrans.Rollback ;
end;
end;
finally
groupKuQuery.Free;
groupKuTrans.free;
end;
end;
procedure TDataconnect.DeleteAutor(AAutor: TAutor);
var
delAuQuery: TIBOQuery;
delAuTransa: TIBOTransaction;
begin
delAuQuery:= TIBOQuery.Create(nil);
try
delAuQuery.IB_Connection:=FDataBase;
delAuTransa:= TIBOTransaction.Create(nil);
try
delAuTransa.IB_Connection:= FDataBase;
try
delAuQuery.SQL.Text :=' DELETE FROM Autoren A WHERE a.Autor_ID = :id';
delAuQuery.ParamByName('id').value := AAutor.ID;
delAuQuery.ExecSQL();
delAuTransa.Commit;
except
on E:
Exception do
begin
ShowMessage('FEHLER IN delete Autor AN');
delAuTransa.Rollback;
end;
end;
finally
delAuTransa.Free;
end;
finally
delAuQuery.Free;
end;
end;
procedure TDataconnect.DeleteBuecher(ABuch: TBuch);
var
delBuQuery: TIBOQuery;
delBuTransa: TIBOTransaction;
begin
delBuQuery:= TIBOQuery.Create(nil);
try
delBuQuery.IB_Connection:=FDataBase;
delBuTransa:= TIBOTransaction.Create(nil);
try
delBuTransa.IB_Connection:= FDataBase;
try
delBuQuery.SQL.Text :=' DELETE FROM Buecher B WHERE B.Buch_ID = :id';
delBuQuery.ParamByName('id').value := ABuch.ID;
delBuQuery.ExecSQL();
delBuTransa.Commit;
except
on E:
Exception do
begin
ShowMessage('FEHLER IN delete buecher AN');
delBuTransa.Rollback;
end;
end;
finally
delBuTransa.Free;
end;
finally
delBuQuery.Free;
end;
end;
procedure TDataconnect.DeleteSendung(Asendung: TSendung);
var
delSeQuery: TIBOQuery;
delSeTransa: TIBOTransaction;
begin
delSeQuery:= TIBOQuery.Create(nil);
try
delSeQuery.IB_Connection:=FDataBase;
delSeTransa:= TIBOTransaction.Create(nil);
try
delSeQuery.IB_Connection:= FDataBase;
try
delSeQuery.SQL.Text :=' DELETE FROM Sendungsverfolgung WHERE Sendungsverfolgung_ID = :id';
delSeQuery.ParamByName('id').Value := Asendung.ID;
delSeQuery.ExecSQL();
delSeTransa.Commit;
except
on E:
Exception do
begin
ShowMessage('FEHLER IN DELETE SENDUNG');
delSeTransa.Rollback;
end;
end;
finally
delSeTransa.Free;
end;
finally
delSeQuery.Free;
end;
end;
procedure TDataconnect.LegeAutorAn(AAutor: TAutor);
var
neuerAuQuery: TIBOQuery;
neuerAuTransa:TIBOTransaction;
begin
neuerAuQuery:= TIBOQuery.Create(nil);
neuerAuTransa:= TIBOTransaction.Create(nil);
neuerAuTransa.IB_Connection:=neuerAuQuery.IB_Connection ;
try
neuerAuQuery.IB_Connection:= FDataBase;
neuerAuTransa.IB_Connection:= FDatabase;
try
neuerAuQuery.SQL.Text:= 'INSERT INTO Autoren (Vorname,Nachname) Values ('+QuotedStr(AAutor.Vorname)+','+QuotedStr(AAutor.Nachname)+') returning Autor_ID';
neuerAuQuery.ExecSQL;
neuerAuTransa.Commit;
Except
on E :
Exception do
begin
ShowMessage('FEHLER IN LEGE AUTOR AN');
neuerAuTransa.Rollback;
end;
end;
Finally
neuerAuQuery.Free;
neuerAuTransa.Free;
end;
end;
procedure TDataconnect.LegeBuchAn(ABuch: TBuch);
var
neuerBuQuery: TIBOQuery;
neuerBuTransa:TIBOTransaction;
begin
neuerBuQuery:= TIBOQuery.Create(nil);
neuerBuTransa:= TIBOTransaction.Create(nil);
neuerBuTransa.IB_Connection:=neuerBuQuery.IB_Connection ;
try
neuerBuQuery.IB_Connection:= FDataBase;
neuerBuTransa.IB_Connection:= FDatabase;
try
////////////////////////////////////////////Buecher funktionieren wegen des Preises nicht//////////////////////////////////////////////////////////////////////
neuerBuQuery.SQL.Text:= 'INSERT INTO Buecher(Preise,Name) Values ('+IntToStr(ABuch.Preis)+','+QuotedStr(ABuch.name)+') returning Buch_ID';
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
neuerBuQuery.ExecSQL;
neuerBuTransa.Commit;
Except
on E :
Exception do
begin
ShowMessage('FEHLER IN LEGE Buch AN');
neuerBuTransa.Rollback;
end;
end;
Finally
neuerBuQuery.Free;
neuerBuTransa.Free;
end;
end;
procedure TDataconnect.LegeSendungAn(ASendung: TSendung);
var
neuerSeQuery: TIBOQuery;
neuerSeTransa:TIBOTransaction;
begin
neuerSeQuery:= TIBOQuery.Create(nil);
neuerSeTransa:= TIBOTransaction.Create(nil);
neuerSeTransa.IB_Connection:=neuerseQuery.IB_Connection ;
try
neuerSeQuery.IB_Connection:= FDataBase;
neuerSeTransa.IB_Connection:= FDatabase;
try
neuerSeQuery.SQL.Text:= 'INSERT INTO Sendungsverfolgung (Status,Rechnung) Values ('+QuotedStr(ASendung.Status)+','+QuotedStr(ASendung.Rechnung)+') returning Sendungsverfolgung_ID';
neuerSeQuery.ExecSQL;
neuerSETransa.Commit;
Except
on E :
Exception do
begin
ShowMessage('FEHLER IN LEGE Sendung AN');
neuerSeTransa.Rollback;
end;
end;
Finally
neuerSeQuery.Free;
neuerSeTransa.Free;
end;
end;
function TDataconnect.getSendungByKunde(AKunde:TKunde): TSendungList;
var
KundInfoQuery : TIBOQuery;
KundInfoTrans : TIBOTransaction;
newSendung: TSendung;
sendungID: integer;
sendungStatus, sendungRechnung: string;
begin
try
Result := TSendungList.create();
KundInfoQuery:=TIBOQuery.create(nil);
KundInfoTrans:=TIBOTransaction.create(nil);
KundInfoQuery.IB_Connection:=FDataBase;
KundInfoTrans.IB_Connection:=FDataBase;
KundInfoQuery.IB_Connection:= KundInfoTrans.IB_Connection;
try
KundInfoQuery.SQL.Text:='Select * From Sendungsverfolgung S where S.Kunden_ID= :id';
KundInfoQuery.ParamByName('id').Value := AKunde.ID;
KundInfoQuery.Open();
// Hier wird das Ergebnis ausgelesen
while not KundInfoQuery.Eof do
begin
sendungID := KundInfoQuery.FieldByName('Sendungsverfolgung_ID').AsInteger;
sendungStatus := KundInfoQuery.FieldByName('Status').AsString;
sendungRechnung := KundInfoQuery.FieldByName('Rechnung').AsString;
newSendung := TSendung.Create(sendungID, sendungStatus, sendungRechnung);
Result.addSendung(newSendung);
KundInfoQuery.Next();
end;
KundInfoQuery.Close();
except
on E:
Exception do
begin
showMessage('FEHLER IN GETKUNDENINFORMATION' + e.Message)
end;
end;
finally
KundInfoQuery.Free;
KundInfoTrans.Free;
end;
end;
procedure TDataconnect.getpreis(ASendung: TSendung);
var
saveKuInfoQuery: TIBOQuery;
SaveKuInfoTrans: TIBOTransaction;
begin
try
SaveKuInfoQuery:= TIBOQuery.Create(nil);
SaveKuInfoQuery.IB_Connection:= FDataBase;
SaveKuInfoTrans:= TIBOTRansaction.Create(nil);
SaveKuInfoTrans.IB_Connection:= FDataBase;
SaveKuInfoQuery.SQL.Text :='SELECT k.Kunden_ID, b.name, Sum( B.preise ), count( b.preise )from RECHNUNGEN r Inner join kunden k on (K.kunden_id = r.kunden_id) Inner join Buecher b on (B.buch_id= r.buch_id) where r.Sendungsverfolgung_ID = :id group by K.kunden_id, b.Name';
saveKuInfoQuery.ParamByName('id').Value:= Asendung.id;
SaveKuInfoQuery.ExecSQL();
SaveKuInfoTrans.Commit;
except
SaveKuInfoQuery.Free;
SaveKuInfoTrans.Free
end;
end;
end.