while not VPers.Eof
do
begin
with VPers
do
begin
log('
Pers: %-30s %-30s', [FieldByName('
Vorname').AsString, FieldByName('
Nachname').AsString]);
VAbt.Close;
VAbt.CommandText := '
SELECT * FROM (Per p INNER JOIN ZOrgPer zop ON p.pkPerID = zop.fkPerID) INNER JOIN Abt a ON zop.fkAbtID = a.pkAbtID WHERE p.pkPerID = '+QuotedStr(VPers.FieldByName('
pkPerID').AsString);
VAbt.Open;
VMain.Insert;
inc(PersNr);
VMain.FieldByName('
PE_ID').Value := genid('
PE_STAMM');
VMain.FieldByName('
PE_NR').Value := Format('
%.4d', [PersNr]);
VMain.FieldByName('
PE_AB_INDEX').Value := scalarQueryInt(Form3.DBXFirebird, '
SELECT AB_ID FROM AB_STAMM WHERE AB_ID = '+IntToStr(getAbtID(VAbt.FieldByName('
AbtBzng').AsString)));
VMain.FieldByName('
PE_AB_NR').Value := scalarQueryString(Form3.DBXFirebird, '
SELECT AB_NR FROM AB_STAMM WHERE AB_ID = '+IntToStr(getAbtID(VAbt.FieldByName('
AbtBzng').AsString)));
VMain.FieldByName('
PE_AB_NAME').Value := scalarQueryString(Form3.DBXFirebird, '
SELECT AB_NAME_LANG FROM AB_STAMM WHERE AB_ID = '+IntToStr(getAbtID(VAbt.FieldByName('
AbtBzng').AsString)));
if not StringIsEmpty(VPers.FieldByName('
Nachname').AsString)
then
VMain.FieldByName('
PE_NACHNAME').Value := GetString(VPers.FieldByName('
Nachname').Value, 30);
if not StringIsEmpty(VPers.FieldByName('
Vorname').AsString)
then
VMain.FieldByName('
PE_VORNAME').Value := GetString(VPers.FieldByName('
Vorname').Value, 30);
VMain.FieldByName('
PE_EI_TAUGLICH').Value := 1;
VMain.FieldByName('
PE_AS_TAUGLICH').Value := 1;
VMain.FieldByName('
PE_UB_TAUGLICH').Value := 1;
VMain.FieldByName('
PE_EH_TAUGLICH').Value := 1;
VMain.FieldByName('
PE_MASKENBRILLE_MML').Value := 0;
VMain.FieldByName('
PE_FAHRBRILLE_MML').Value := 0;
VMain.Post;
VMain.ApplyUpdates(0);
Next;
end;
end;
function scalarQueryString(dbc: TSQLConnection;
sql:
String;):
String;
var
VQuery: TSQLDataSet;
begin
VQuery := TSQLDataSet.Create(
nil);
VQuery.SQLConnection := dbc;
VQUery.CommandText :=
sql;
VQuery.Open;
VQuery.First;
result := VQuery.Fields[0].AsString
FreeAndNil(VQuery);
end;
function scalarQueryString(dbc: TSQLConnection;
sql:
String;): Integer;
var
VQuery: TSQLDataSet;
begin
VQuery := TSQLDataSet.Create(
nil);
VQuery.SQLConnection := dbc;
VQUery.CommandText :=
sql;
VQuery.Open;
VQuery.First;
result := VQuery.Fields[0].AsInteger
FreeAndNil(VQuery);
end;
function genid(tablename:
String): Integer;
begin
result := scalarQueryInt(Form3.DBXFirebird, Format('
SELECT max(%s_ID) FROM %s', [Copy(tablename, 1, 2), tablename]), true, 0);
Inc(result);
end;