unit ZeiterfassungMain;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, ADODB, Grids, Wwdbigrd, Wwdbgrid, ExtCtrls, DBCtrls, StdCtrls;
type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
wwDBGrid1: TwwDBGrid;
ADOTable1: TADOTable;
DataSource1: TDataSource;
ADOTable1TAG: TWideStringField;
ADOTable1KOMMEN: TWideStringField;
ADOTable1PAUSE_START: TWideStringField;
ADOTable1PAUSE_ENDE: TWideStringField;
ADOTable1GEHEN: TWideStringField;
ADOTable1SONSTIGER_ABZUG: TWideStringField;
ADOTable1ZUHAUSE: TWideStringField;
ADOTable1BERSTUNDEN_SALDO: TIntegerField;
ADOTable1BERSTUNDEN: TIntegerField;
ADOTable1FREIER_TAG: TBooleanField;
Panel1: TPanel;
Button1: TButton;
DBNavigator1: TDBNavigator;
ADOTable1WOCHENTAG: TStringField;
ADOTable1USERNAME: TStringField;
ComboBox1: TComboBox;
Label1: TLabel;
Label2: TLabel;
ADOTable1BEMERKUNG: TStringField;
procedure ADOTable1NewRecord(DataSet: TDataSet);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure ADOTable1BeforePost(DataSet: TDataSet);
procedure ADOTable1BERSTUNDEN_SALDOGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
procedure ADOTable1BERSTUNDENGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
procedure wwDBGrid1CalcCellColors(Sender: TObject; Field: TField;
State: TGridDrawState; Highlight: Boolean; AFont: TFont; ABrush: TBrush);
procedure ADOTable1AfterPost(DataSet: TDataSet);
procedure Button1Click(Sender: TObject);
procedure ADOTable1TAGChange(Sender: TField);
procedure ADOTable1SONSTIGER_ABZUGSetText(Sender: TField;
const Text: string);
procedure ADOTable1ZUHAUSESetText(Sender: TField; const Text: string);
procedure ADOTable1GEHENSetText(Sender: TField; const Text: string);
procedure ADOTable1PAUSE_ENDESetText(Sender: TField; const Text: string);
procedure ADOTable1PAUSE_STARTSetText(Sender: TField; const Text: string);
procedure ADOTable1KOMMENSetText(Sender: TField; const Text: string);
procedure FormShow(Sender: TObject);
procedure ADOTable1WOCHENTAGGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
procedure ComboBox1Change(Sender: TObject);
procedure ADOTable1TAGSetText(Sender: TField; const Text: string);
procedure ADOTable1AfterDelete(DataSet: TDataSet);
procedure ADOTable1TAGGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
procedure ADOTable1KOMMENGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
procedure ADOTable1PAUSE_STARTGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
procedure ADOTable1PAUSE_ENDEGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
procedure ADOTable1GEHENGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
procedure ADOTable1SONSTIGER_ABZUGGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
procedure ADOTable1ZUHAUSEGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
procedure ADOTable1BeforeScroll(DataSet: TDataSet);
private
function GueltigeZeile: boolean;
protected
procedure ReorgDataSet;
procedure ReorgAll;
function RegelArbeitszeit: integer;
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
// TODO: Trennstriche zwischen Wochen oder zwischen Urlauben
// IDEE: Wochenend-Multiplikator
// TODO: "Gehen um" Anzeige
uses
DateUtils, StrUtils, IniFiles;
{$REGION 'Hilfsfunktionen'}
function SQL_Escape(const s: string): string;
begin
result := StringReplace(s, '''', '\''', [rfReplaceAll]);
end;
function IstLeer(f: TField): boolean;
begin
result := f.IsNull or (f.AsString = '') or (f.AsString = '00:00:00');
end;
function Minuten(f: TField): integer;
begin
if IstLeer(f) then
begin
result := 0;
end
else
begin
result := MinuteOfTheDay(f.AsDateTime);
end;
end;
function MinutenZuHF_Int(min: integer): string;
var
d: integer;
begin
d := min;
if d < 0 then
begin
result := '-';
d := -d;
end
else
begin
result := '';
end;
result := result + Format('%.2d:%.2d', [d div 60, d mod 60]);
end;
function MinutenZuHF(f: TField): string;
begin
if IstLeer(f) then
begin
result := '';
end
else
begin
result := MinutenZuHF_Int(f.AsInteger);
end;
end;
function EchtesDatum(f: TField): TDate;
begin
if Copy(f.AsString, 5, 1) = '-' then
begin
result := EncodeDate(
StrtoInt(Copy(f.AsString, 1, 4)),
StrtoInt(Copy(f.AsString, 6, 2)),
StrtoInt(Copy(f.AsString, 9, 2))
);
end
else
result := StrToDate(f.AsString);
end;
function WUserName: String;
var
nSize: DWord;
begin
nSize := 1024;
SetLength(Result, nSize);
if GetUserName(PChar(Result), nSize) then
SetLength(Result, nSize-1)
else
RaiseLastOSError;
end;
{$ENDREGION}
function TForm1.RegelArbeitszeit: integer;
var
test: TADOQuery;
begin
test := TADOQuery.Create(nil);
try
test.Connection := ADOConnection1;
test.Close;
test.SQL.Text := 'select MINUTEN from REGELARBEITSZEIT where USERNAME = ''' + SQL_Escape(ComboBox1.Text) + '''';
test.Open;
if test.RecordCount = 0 then
begin
result := 8 * 60;
end
else
begin
result := test.FieldByName('MINUTEN').AsInteger;
end;
finally
test.Free;
end;
end;
procedure TForm1.ReorgAll;
var
saldo: integer;
baks, baku: string;
bakEv: TDataSetNotifyEvent;
dead: boolean;
begin
if ADOTable1TAG.IsNull then
begin
baks := '';
end
else
begin
if Copy(ADOTable1TAG.AsString, 5, 1) = '-' then
baks := ADOTable1TAG.AsString
else
DateTimeToString(baks, 'YYYY-MM-DD', ADOTable1TAG.AsDateTime);
end;
baku := ADOTable1USERNAME.AsString;
ADOTable1.Requery();
bakEv := ADOTable1.AfterPost;
ADOTable1.AfterPost := nil;
ADOTable1.DisableControls;
try
ADOTable1.First;
saldo := 0;
dead := false;
while not ADOTable1.Eof do
begin
ADOTable1.Edit;
if not dead then ReorgDataSet;
dead := dead or ADOTable1BERSTUNDEN.IsNull;
if dead then
begin
ADOTable1BERSTUNDEN_SALDO.Clear;
end
else
begin
saldo := saldo + ADOTable1BERSTUNDEN.AsInteger;
ADOTable1BERSTUNDEN_SALDO.AsInteger := saldo;
saldo := ADOTable1BERSTUNDEN_SALDO.AsInteger;
end;
ADOTable1.Post;
ADOTable1.Next;
end;
finally
if baks <> '' then ADOTable1.Locate('USERNAME;TAG', VarArrayOf([baku, baks]), []);
ADOTable1.AfterPost := bakEv;
ADOTable1.EnableControls;
end;
end;
procedure TForm1.ADOTable1AfterDelete(DataSet: TDataSet);
begin
ReorgAll;
end;
procedure TForm1.ADOTable1AfterPost(DataSet: TDataSet);
begin
ReorgAll;
end;
function TForm1.GueltigeZeile: boolean;
begin
result := false;
if IstLeer(ADOTable1KOMMEN) <> IstLeer(ADOTable1GEHEN) then exit;
if IstLeer(ADOTable1PAUSE_START) <> IstLeer(ADOTable1PAUSE_ENDE) then exit;
if not IstLeer(ADOTable1PAUSE_START) and (ADOTable1PAUSE_START.AsDateTime < ADOTable1KOMMEN.AsDateTime) then exit;
if not IstLeer(ADOTable1PAUSE_ENDE) and (ADOTable1PAUSE_ENDE.AsDateTime < ADOTable1PAUSE_START.AsDateTime) then exit;
if not IstLeer(ADOTable1GEHEN) and (ADOTable1GEHEN.AsDateTime < ADOTable1KOMMEN.AsDateTime) then exit;
if not IstLeer(ADOTable1GEHEN) and not IstLeer(ADOTable1PAUSE_START) and (ADOTable1GEHEN.AsDateTime < ADOTable1PAUSE_START.AsDateTime) then exit;
if not IstLeer(ADOTable1GEHEN) and not IstLeer(ADOTable1PAUSE_ENDE) and (ADOTable1GEHEN.AsDateTime < ADOTable1PAUSE_ENDE.AsDateTime) then exit;
result := true;
end;
procedure TForm1.ReorgDataSet;
var
m: integer;
begin
if GueltigeZeile then
begin
m := (Minuten(ADOTable1GEHEN) - Minuten(ADOTable1KOMMEN))
- (Minuten(ADOTable1PAUSE_ENDE) - Minuten(ADOTable1PAUSE_START))
- Minuten(ADOTable1SONSTIGER_ABZUG)
+ Minuten(ADOTable1ZUHAUSE);
if not ADOTable1FREIER_TAG.AsBoolean then
begin
m := m - RegelArbeitszeit;
end;
ADOTable1BERSTUNDEN.AsInteger := m;
end
else
begin
ADOTable1BERSTUNDEN.Clear;
end;
end;
procedure TForm1.ADOTable1BeforePost(DataSet: TDataSet);
begin
if (ADOTable1.State = dsInsert) and ADOTable1TAG.IsNull then
begin
AdoTable1.Cancel;
Abort;
end;
ReorgDataSet;
end;
procedure TForm1.ADOTable1BeforeScroll(DataSet: TDataSet);
begin
if (DataSet.State = dsInsert) and (not ADOTable1TAG.IsNull) then Dataset.Post;
end;
procedure TForm1.ADOTable1BERSTUNDENGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
begin
Text := MinutenZuHF(ADOTable1BERSTUNDEN);
end;
procedure TForm1.ADOTable1BERSTUNDEN_SALDOGetText(Sender: TField;
var Text: string; DisplayText: Boolean);
begin
Text := MinutenZuHF(ADOTable1BERSTUNDEN_SALDO);
end;
procedure TForm1.ADOTable1GEHENGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
begin
Text := Copy(Sender.AsString, 1, 5);
end;
procedure TForm1.ADOTable1GEHENSetText(Sender: TField; const Text: string);
begin
if Text = '' then
begin
ADOTable1GEHEN.Clear;
end
else
begin
if Pos(':', Text) = 0 then
ADOTable1GEHEN.AsString := Text + ':00'
else
ADOTable1GEHEN.AsString := Text;
end;
end;
procedure TForm1.ADOTable1KOMMENGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
begin
Text := Copy(Sender.AsString, 1, 5);
end;
procedure TForm1.ADOTable1KOMMENSetText(Sender: TField; const Text: string);
begin
if Text = '' then
begin
ADOTable1KOMMEN.Clear;
end
else
begin
if Pos(':', Text) = 0 then
ADOTable1KOMMEN.AsString := Text + ':00'
else
ADOTable1KOMMEN.AsString := Text;
end;
end;
procedure TForm1.ADOTable1NewRecord(DataSet: TDataSet);
var
test: TADOQuery;
begin
ADOTable1FREIER_TAG.AsBoolean := false;
ADOTable1USERNAME.AsString := ComboBox1.Text;
test := TADOQuery.Create(nil);
try
test.Connection := ADOConnection1;
test.Close;
test.SQL.Text := 'select * from TAGE where TAG = ''' + DateToStr(Date) + ''' and USERNAME = ''' + SQL_Escape(ComboBox1.Text) + '''';
test.Open;
if test.RecordCount = 0 then
begin
ADOTable1TAG.AsDateTime := Date;
ADOTable1KOMMEN.AsString := TimeToStr(Time);
ADOTable1FREIER_TAG.AsBoolean := (DayOfWeek(Date) = 1{Sunday}) or
(DayOfWeek(Date) = 7{Saturday});
end;
finally
test.Free;
end;
wwDBGrid1.SelectedField := ADOTable1TAG;
end;
procedure TForm1.ADOTable1PAUSE_ENDEGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
begin
Text := Copy(Sender.AsString, 1, 5);
end;
procedure TForm1.ADOTable1PAUSE_ENDESetText(Sender: TField; const Text: string);
begin
if Text = '' then
begin
ADOTable1PAUSE_ENDE.Clear;
end
else
begin
if Pos(':', Text) = 0 then
ADOTable1PAUSE_ENDE.AsString := Text + ':00'
else
ADOTable1PAUSE_ENDE.AsString := Text;
end;
end;
procedure TForm1.ADOTable1PAUSE_STARTGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
begin
Text := Copy(Sender.AsString, 1, 5);
end;
procedure TForm1.ADOTable1PAUSE_STARTSetText(Sender: TField;
const Text: string);
begin
if Text = '' then
begin
ADOTable1PAUSE_START.Clear;
end
else
begin
if Pos(':', Text) = 0 then
ADOTable1PAUSE_START.AsString := Text + ':00'
else
ADOTable1PAUSE_START.AsString := Text;
end;
end;
procedure TForm1.ADOTable1SONSTIGER_ABZUGGetText(Sender: TField;
var Text: string; DisplayText: Boolean);
begin
Text := Copy(Sender.AsString, 1, 5);
end;
procedure TForm1.ADOTable1SONSTIGER_ABZUGSetText(Sender: TField;
const Text: string);
begin
if Text = '' then
begin
ADOTable1SONSTIGER_ABZUG.Clear;
end
else
begin
if Pos(':', Text) = 0 then
ADOTable1SONSTIGER_ABZUG.AsString := Text + ':00'
else
ADOTable1SONSTIGER_ABZUG.AsString := Text;
end;
end;
procedure TForm1.ADOTable1TAGChange(Sender: TField);
begin
ADOTable1FREIER_TAG.AsBoolean := (DayOfWeek(ADOTable1TAG.AsDateTime) = 1{Sunday}) or
(DayOfWeek(ADOTable1TAG.AsDateTime) = 7{Saturday});
// TODO: "Wochentag" Feld aktualisieren
end;
procedure TForm1.ADOTable1TAGGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
begin
if IstLeer(Sender) then
Text := Sender.AsString
else
Text := DateToStr(EchtesDatum(Sender));
end;
procedure TForm1.ADOTable1TAGSetText(Sender: TField; const Text: string);
var
i, punktCount: integer;
begin
punktCount := 0;
for i := 1 to Length(Text) do
begin
if Text[i] = '.' then inc(punktCount);
end;
if punktCount = 1 then
begin
ADOTable1TAG.AsString := Text + '.' + IntToStr(CurrentYear);
end
else if (PunktCount = 2) and EndsStr('.',Text) then
begin
ADOTable1TAG.AsString := Text + IntToStr(CurrentYear);
end
else
begin
ADOTable1TAG.AsString := Text;
end;
end;
procedure TForm1.ADOTable1WOCHENTAGGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
begin
try
if ADOTable1TAG.AsString <> '' then
Text := ShortDayNames[DayOfWeek(EchtesDatum(ADOTable1TAG))]
else
Text := '';
except
Text := '??';
end;
end;
procedure TForm1.ADOTable1ZUHAUSEGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
begin
Text := Copy(Sender.AsString, 1, 5);
end;
procedure TForm1.ADOTable1ZUHAUSESetText(Sender: TField; const Text: string);
begin
if Text = '' then
begin
ADOTable1ZUHAUSE.Clear;
end
else
begin
if Pos(':', Text) = 0 then
ADOTable1ZUHAUSE.AsString := Text + ':00'
else
ADOTable1ZUHAUSE.AsString := Text;
end;
end;
procedure TForm1.Button1Click(Sender: TObject);
begin
ReorgAll;
end;
procedure TForm1.ComboBox1Change(Sender: TObject);
begin
Label2.Caption := MinutenZuHF_Int(RegelArbeitszeit) + ' Std.';
ADOTable1.DisableControls;
ADOTable1.Active := false;
ADOTable1.ReadOnly := false;
ADOTable1.Filter := 'USERNAME = ''' + SQL_Escape(ComboBox1.Text) + '''';
ADOTable1.Filtered := true;
ADOTable1.Active := true;
ReorgAll;
ADOTable1.Active := false;
ADOTable1.ReadOnly := (ComboBox1.Text <> WUserName) and (WUserName <> 'Administrator');
ADOTable1.Active := true;
ADOTable1.Last;
Button1.Enabled := not ADOTable1.ReadOnly;
ADOTable1.EnableControls;
end;
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
if ADOTable1.State in [dsEdit, dsInsert] then
begin
try
ADOTable1.Post;
except
on E: EAbort do
begin
exit;
end;
end;
end;
end;
procedure TForm1.FormShow(Sender: TObject);
var
test: TADOQuery;
ini: TMemIniFile;
resourcestring
DefaultConnectionString = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ZEITERFASSUNG;' +
'Data Source=SHS\CORA2017,49011;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MARSCHALL;Use Encryption for Data=False;Tag with column collation when possible=False;';
begin
ini := TMemIniFile.Create(IncludeTrailingPathDelimiter(ExtractFilePath(ParamStr(0))) + ChangeFileExt(ExtractFileName(ParamStr(0)), '.ini'));
try
ADOConnection1.ConnectionString := ini.ReadString('Connection', 'ConnectionString', DefaultConnectionString);
finally
ini.Free;
end;
ADOConnection1.Connected := true;
{$REGION 'Username Combobox füllen'}
test := TADOQuery.Create(nil);
try
test.Connection := ADOConnection1;
test.Close;
test.SQL.Text := 'select distinct USERNAME from TAGE';
test.Open;
ComboBox1.Items.Clear;
while not test.EOF do
begin
ComboBox1.Items.Add(test.FieldByName('USERNAME').AsString);
test.Next;
end;
finally
test.Free;
end;
if ComboBox1.Items.IndexOf(WUserName) = -1 then
ComboBox1.Items.Add(WUserName);
ComboBox1.Sorted := true;
ComboBox1.ItemIndex := ComboBox1.Items.IndexOf(WUserName);
ComboBox1Change(ComboBox1);
{$ENDREGION}
if wwDBGrid1.CanFocus then wwDBGrid1.SetFocus;
wwDBGrid1.SelectedField := ADOTable1TAG;
end;
procedure TForm1.wwDBGrid1CalcCellColors(Sender: TObject; Field: TField;
State: TGridDrawState; Highlight: Boolean; AFont: TFont; ABrush: TBrush);
begin
if Highlight then exit;
if (Field.FieldName = ADOTable1WOCHENTAG.FieldName) or
(Field.FieldName = ADOTable1BERSTUNDEN.FieldName) or
(Field.FieldName = ADOTable1BERSTUNDEN_SALDO.FieldName) then
begin
ABrush.Color := clBtnFace;
end;
if (Field.FieldName = ADOTable1BERSTUNDEN.FieldName) then
begin
if ADOTable1BERSTUNDEN.AsInteger < 0 then
begin
AFont.Color := clRed;
end;
end;
if (Field.FieldName = ADOTable1BERSTUNDEN_SALDO.FieldName) then
begin
if ADOTable1BERSTUNDEN_SALDO.AsInteger < 0 then
begin
AFont.Color := clRed;
end;
end;
end;
end.