Check-in [e1b6ee0b86]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:First working version
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e1b6ee0b8653293975f899fab15d713953797e1c
User & Date: MCO 2011-05-04 10:59:50
Context
2011-05-04
10:59
First working version check-in: a9d77b7171 user: MCO tags: trunk
10:59
First working version check-in: e1b6ee0b86 user: MCO tags: trunk
2011-05-01
14:49
initial empty check-in check-in: b03b9b790d user: tinus tags: trunk
Changes

Added src/F_Entry.pas.































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
unit F_Entry;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, Tabs, Grids,
  SQLite3Database;

type
  TfrmEntry = class(TForm)
    Grid: TStringGrid;
    tbsWeek: TTabSet;
    dtpDay: TDateTimePicker;
    procedure FormResize(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure tbsWeekChange(Sender: TObject; NewTab: Integer; var AllowChange: Boolean);
    procedure dtpDayChange(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure GridKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
  private
    { Private declarations }
    FDoneLoading: Boolean;
    FInhibitEvents: boolean;
    FDB: TSQLiteDatabase;
    FActiveDate: TDateTime;
    FLoadQuery: TSQLiteQuery;
    FClearQuery: TSQLiteQuery;
    FSaveQuery: TSQLiteQuery;

    procedure FillHeaders;
    function  SQLDateToStr(Date: TDateTime): string;
    function  SQLDateTimeToStr(Date: TDateTime): string;
    procedure SetDate(NewDate: TDateTime);
    function  AddRow(CurrentDate: TDateTime): Integer;
    procedure SaveGrid(Date: TDateTime);
    procedure LoadGrid(Date: TDateTime);
    procedure InitializeDB;
  public
    { Public declarations }
  end;

var
  frmEntry: TfrmEntry;

implementation
uses
  DateUtils;

{$R *.dfm}

{ ------------------------------------------------------------------------------------------------ }
procedure TfrmEntry.FormCreate(Sender: TObject);
begin
  try
    dtpDay.MaxDate := dtpDay.Date + 7; // eigenlijk niet verder dan a.s. zondag...
    FillHeaders;
    InitializeDB;

    SetDate(Date);
  finally
    FDoneLoading := True;
  end;
end;
{ ------------------------------------------------------------------------------------------------ }
procedure TfrmEntry.FormDestroy(Sender: TObject);
begin
  FreeAndNil(FDB);
end;

{ ------------------------------------------------------------------------------------------------ }
procedure TfrmEntry.FormResize(Sender: TObject);
begin
  // TODO: autosize column 2
end;

{ ------------------------------------------------------------------------------------------------ }
procedure TfrmEntry.GridKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
var
  KeyShift: TShiftState;
begin
  KeyShift := Shift * [ssShift, ssCtrl, ssAlt];
  case Key of
    VK_TAB: begin
      if (KeyShift = []) and (goEditing in Grid.Options)
          and (Grid.Col = Grid.ColCount - 1) and (Grid.Row = Grid.RowCount - 1) then begin
        AddRow(FActiveDate);
      end else if ssCtrl in KeyShift then begin
        if ssShift in KeyShift then begin
          SetDate(FActiveDate - 1);
        end else begin
          if FActiveDate + 2 < dtpDay.MaxDate then
            SetDate(FActiveDate + 1);
        end{if};
      end{if};
    end;
  end{case};
end{TfrmEntry.GridKeyDown};

{ ------------------------------------------------------------------------------------------------ }
procedure TfrmEntry.dtpDayChange(Sender: TObject);
begin
  if not FInhibitEvents then begin
    SetDate(dtpDay.Date);
  end;
end{TfrmEntry.dtpDayChange};

{ ------------------------------------------------------------------------------------------------ }
procedure TfrmEntry.tbsWeekChange(Sender: TObject; NewTab: Integer; var AllowChange: Boolean);
begin
  AllowChange := True;
  if not FInhibitEvents then begin
    if AllowChange then begin
      SetDate(dtpDay.Date + (NewTab - tbsWeek.TabIndex));
    end;
  end;
end{TfrmEntry.tbsWeekChange};


{ ------------------------------------------------------------------------------------------------ }
procedure TfrmEntry.FillHeaders;
begin
  Grid.Cells[0, 0] := 'Tijd';
  Grid.Cells[1, 0] := 'Project';
  Grid.Cells[2, 0] := 'Werkzaamheden';
end;

{ ------------------------------------------------------------------------------------------------ }
procedure TfrmEntry.SetDate(NewDate: TDateTime);
begin
  if FDoneLoading then begin
    // save the current day's data
    SaveGrid(FActiveDate);
  end;

  FInhibitEvents := True;
  try
    dtpDay.Date := NewDate;
    tbsWeek.TabIndex := (DayOfWeek(NewDate) + 5) mod 7;
  finally
    FInhibitEvents := False;
  end;

  FActiveDate := NewDate;

  // load the new day's data into the grid
  LoadGrid(NewDate);

  if SameDate(NewDate, Date) then begin
    Grid.Row := AddRow(NewDate);
    Grid.Col := 1;
    Grid.Options := Grid.Options + [goEditing];
    Grid.EditorMode := True;
//    Grid.SetFocus;
  end else begin
    Grid.Options := Grid.Options - [goEditing];
    Grid.EditorMode := False;
//    Grid.SetFocus;
  end;
end{TfrmEntry.SetDate};

{ ------------------------------------------------------------------------------------------------ }
function TfrmEntry.SQLDateTimeToStr(Date: TDateTime): string;
begin
  Result := FormatDateTime('yyyy-MM-dd hh:nn:ss', Date);
end;

{ ------------------------------------------------------------------------------------------------ }
function TfrmEntry.SQLDateToStr(Date: TDateTime): string;
begin
  Result := FormatDateTime('yyyy-MM-dd', Date);
end;

{ ------------------------------------------------------------------------------------------------ }
function TfrmEntry.AddRow(CurrentDate: TDateTime): Integer;
var
  LastRow, Col: Integer;
begin
  LastRow := Grid.RowCount - 1;
  for Col := 1 to Grid.ColCount - 1 do begin
    if Trim(Grid.Cells[Col, LastRow]) <> '' then begin
      Inc(LastRow);
      Break;
    end;
  end;
  if Grid.RowCount <= LastRow then
    Grid.Objects[0, LastRow] := nil;
  Grid.RowCount := LastRow + 1;
  if CurrentDate >= Date then begin
    Grid.Cells[0, LastRow] := TimeToStr(Now);
  end else begin
    Grid.Cells[0, LastRow] := '';
  end;

  Result := LastRow;
end;

{ ------------------------------------------------------------------------------------------------ }
procedure TfrmEntry.SaveGrid(Date: TDateTime);
var
  Row: Integer;
  Timestamp: TDateTime;
  Project, Activity: string;
begin
  // TODO: check the ID (object of the first row); if present then send that along (do an insert or replace)

  FDB.BeginTransaction;
  try
    // First, throw away the existing records for this date
    FClearQuery.Parameters.Add(':Date', SQLDateToStr(Date));
    FDB.Execute(FClearQuery);

    // Next, save all the new records
    for Row := Grid.FixedRows to Grid.RowCount - 1 do begin
      if TryStrToTime(Grid.Cells[0, Row], Timestamp) then begin
        Timestamp := Trunc(Date) + Frac(Timestamp);
        Project := Trim(Grid.Cells[1, Row]);
        Activity := Trim(Grid.Cells[2, Row]);
        if (Length(Project) > 0) or (Length(Activity) > 0) then begin
          with FSaveQuery do begin
            Parameters.Add(':DateTime', SQLDateTimeToStr(Timestamp));
            Parameters.Add(':Project', Project);
            Parameters.Add(':Activity', Activity);
          end;
          FDB.Execute(FSaveQuery);
        end{if};
      end;
    end{for};

    FDB.Commit;
  except
    FDB.Rollback;
    raise;
  end;
end{TfrmEntry.SaveGrid};

{ ------------------------------------------------------------------------------------------------ }
procedure TfrmEntry.LoadGrid(Date: TDateTime);
var
  Cursor: TSQLiteCursor;
  Col, Row: Integer;
begin
  // Clear the grid first
  Grid.RowCount := Grid.FixedRows + 1;
  Row := Grid.FixedRows;
  Grid.Objects[0, Row] := nil;
  for Col := 0 to Grid.ColCount - 1 do begin
    Grid.Cells[Col, Row] := '';
  end;

  // load the data for this day, and put it into the grid
  FLoadQuery.Parameters.Add(':Date', SQLDateToStr(Date));
  Cursor := FDB.GetCursor(FLoadQuery) as TSQLiteCursor;
  try
    while not Cursor.EOF do begin
      Row := AddRow(Date);
      Grid.Objects[0, Row] := TObject(Cursor.Field['id'].AsInteger);
      Grid.Cells[0, Row] := Cursor.Field['time'].AsString;
      Grid.Cells[1, Row] := Cursor.Field['project'].AsString;
      Grid.Cells[2, Row] := Cursor.Field['activity'].AsString;
      Cursor.Next;
    end;
  finally
    Cursor.Free;
  end;
end{TfrmEntry.LoadGrid};

{ ------------------------------------------------------------------------------------------------ }
procedure TfrmEntry.InitializeDB;
begin
  // TODO: make it possible to move this elsewhere
  FDB := TSQLiteDatabase.Create(ChangeFileExt(ParamStr(0), '.sqlite'));

  if not FDB.TableExists('Entries') then begin
    FDB.Execute('PRAGMA user_version = 1;');

    FDB.Execute(' CREATE TABLE Entries'
              + ' ( id                    INTEGER PRIMARY KEY'
              + ' , datetime              DOUBLE  NOT NULL'
              + ' , project               TEXT    NOT NULL'
              + ' , activity              TEXT'
              + ' , date_inserted         TEXT    DEFAULT CURRENT_TIMESTAMP'
              + ' );');

    FDB.Execute('CREATE INDEX idx_Entries_datetime ON Entries (datetime);');
  end;

  FLoadQuery := FDB.PrepareSQL('   SELECT id'
                             + '   ,      time(datetime) AS time'
                             + '   ,      project'
                             + '   ,      activity'
                             + '     FROM Entries'
                             + '    WHERE datetime >= julianday(:Date)'
                             + '      AND datetime < julianday(:Date, "+1 day")'
                             + ' ORDER BY datetime') as TSQLiteQuery;
  FClearQuery := FDB.PrepareSQL('DELETE FROM Entries'
                              + '      WHERE datetime >= julianday(:Date)'
                              + '        AND datetime < julianday(:Date, "+1 day")') as TSQLiteQuery;
  FSaveQuery := FDB.PrepareSQL('INSERT INTO Entries (datetime, project, activity)'
                             + '     SELECT julianday(:DateTime), :Project, :Activity;') as TSQLiteQuery;
end{TfrmEntry.InitializeDB};

end.