Check-in [b9e83e2b17]
Not logged in

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

Overview
Comment:Keep track of current project, and publish what's been spent on it so far.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b9e83e2b1754ab7bb7a23be5cb5fe5d5d0ea489f
User & Date: MCO 2012-06-11 16:09:10
Context
2013-07-25
13:30
Added project "ProjectIt", which provides a UI to easily keep track of what the user is currently working on (first version of rewrite using the FireMonkey framework). check-in: b0cbac6608 user: MCO tags: trunk
2012-06-11
16:09
Keep track of current project, and publish what's been spent on it so far. check-in: b9e83e2b17 user: MCO tags: trunk
2012-05-29
15:57
Added project to export ProjectIt.dat to an iCalendar file. check-in: 3bdccd5cd5 user: MCO tags: trunk
Changes

Changes to DAT2ICS/src/ProjectIt_dat2ics.dpr.

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
..
36
37
38
39
40
41
42

43
44
45
46
47
48
49
..
64
65
66
67
68
69
70

71
72
73
74
75
76
77
..
79
80
81
82
83
84
85


86
87
88
89
90
91
92
..
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
...
184
185
186
187
188
189
190



191
192
193
194
195













196
197
198
199
200
201
202
program ProjectIt_dat2ics;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils, Classes, IniFiles, StrUtils,
  Types, MessageDigest_5 in 'C:\Program Files (x86)\Embarcadero\RAD Studio\9.0\source\soap\wsdlimporter\MessageDigest_5.pas',
  SQLite3Database, SQLite3Abstract;

{ ------------------------------------------------------------------------------------------------ }
function LoadData(const DataFile: TFilename; var DB: TSQLiteDatabase): Integer;
var
  Data: TIniFile;
  InsertActivity: TCustomSQLiteQuery;
  Projects, Entries: TStringList;
  InitialChanges: Int64;
  pi, ei: Integer;
  sProject: string;
  sKey: string;
  dtDummy: TDateTime;

begin
  if not FileExists(DataFile) then
    raise EFileNotFoundException.CreateFmt('File "%s" not found.', [DataFile]);

  Data := TIniFile.Create(DataFile);
  try
    if DB = nil then
................................................................................
                + ');');
    if not DB.TableExists('Activities') then
      DB.Execute('CREATE TABLE Activities'
                + '( id INTEGER PRIMARY KEY'
                + ', project_id INTEGER NOT NULL REFERENCES Projects'
                + ', start DOUBLE NOT NULL'
                + ', duration INTEGER NOT NULL'

                + ', UNIQUE (project_id, start)'
                + ');');
    DB.Execute('CREATE VIEW IF NOT EXISTS ProjectActivities AS '
            + ' SELECT p.name AS project, datetime(a.start) AS start, duration, datetime(a.start, "+" || a.duration || " seconds") AS finish'
            + '   FROM Projects p INNER JOIN Activities a ON p.id = a.project_id'
            + ';');
    DB.Execute('CREATE TRIGGER IF NOT EXISTS vi_ProjectActivities '
................................................................................
                                  + 'VALUES(:Project, :Start, :Duration)');
    Projects := TStringList.Create;
    Entries := TStringList.Create;
    try
      Data.ReadSections(Projects);
      InitialChanges := DB.TotalChanges;
      Result := 0;

      DB.BeginTransaction;
      try
        for pi := 0 to Projects.Count - 1 do begin
          sProject := Projects[pi];
          Entries.Clear;
          Data.ReadSection(sProject, Entries);
          for ei := 0 to Entries.Count - 1 do begin
................................................................................
            if TryStrToDateTime(sKey, dtDummy, DB.FormatSettings) then begin
              InsertActivity.Parameters.Named[':Project'].SetValue(sProject);
              InsertActivity.Parameters.Named[':Start'].SetValue(sKey);
              InsertActivity.Parameters.Named[':Duration'].SetValue(Data.ReadInteger(sProject, sKey, 0));
              DB.Execute(InsertActivity);
            end;
          end;


        end;
        Result := DB.TotalChanges - InitialChanges;
        DB.Commit;
      except
        DB.Rollback;
        Result := 0;
        raise;
................................................................................
      Entries.Free;
      Projects.Free;
      InsertActivity.Free;
    end;
  finally
    Data.Free;
  end;















end {LoadData};

{ ------------------------------------------------------------------------------------------------ }
function ExportToICS(const DB: TSQLiteDatabase; const ICSFilename: TFilename): Integer;
var
  SB: TStringBuilder;
  Rows: TCustomSQLiteCursor;
  SS: TStringStream;
  iProject, iStart, iFinish: Integer;
  MD5: IMD5;
  sHash8: UTF8String;
  sHash: string;
begin
  SB := TStringBuilder.Create(10240);
  try
    Rows := DB.GetCursor('  SELECT p.name'
                      + '   ,      strftime("%Y%m%dT%H%M%S", a.start, "utc") || "Z" AS start'
                      + '   ,      strftime("%Y%m%dT%H%M%S", a.start, "+" || a.duration || " seconds", "utc") || "Z" AS finish'

                      + '     FROM Activities a'
                      + '          INNER JOIN Projects p ON a.project_id = p.id'
                      + '    WHERE a.duration >= (60 * 5)'
                      + '      AND p.name != "Pauze"'
                      + ' ORDER BY a.start DESC'
                      + ' ,        a.duration DESC'
                      + ' ,        p.id DESC'
                      + ' ,        a.id DESC;');
    try
      Result := 0;
      if not Rows.EOF then begin
        SB.Append('BEGIN:VCALENDAR').AppendLine;
        SB.Append('PRODID:-//martijn.coppoolse.com/ProjectIt v1 dat2ics//NL').AppendLine;
        SB.Append('VERSION:2.0').AppendLine;
        SB.Append('CALSCALE:GREGORIAN').AppendLine;
        SB.Append('METHOD:PUBLISH').AppendLine;
        SB.Append('X-WR-NAME:ProjectIt!').AppendLine;

        iProject := Rows.FieldIndex('name');
        iStart := Rows.FieldIndex('start');
        iFinish := Rows.FieldIndex('finish');


        while not Rows.EOF do begin
          sHash8 := UTF8String(Rows.Fields[iProject].AsString + '|' + Rows.Fields[iFinish].AsString);
          MD5 := GetMD5;
          MD5.Init;
          MD5.Update(TByteDynArray(sHash8), Length(sHash8));
          sHash := LowerCase(MD5.AsString);

          SB.Append('BEGIN:VEVENT').AppendLine;
          SB.Append('DTSTART:' + Rows.Fields[iStart].AsString).AppendLine;
          SB.Append('DTEND:' + Rows.Fields[iFinish].AsString).AppendLine;



          SB.Append('SUMMARY:' + Rows.Fields[iProject].AsString).AppendLine;
          SB.Append('CATEGORIES:' + Rows.Fields[iProject].AsString).AppendLine;
          SB.Append('UID:' + sHash + '@v1a.projectit.2of4.net').AppendLine;
          SB.Append('LAST-MODIFIED:' + Rows.Fields[iFinish].AsString).AppendLine;
          SB.Append('CLASS:PUBLIC').AppendLine;
          SB.Append('STATUS:CONFIRMED').AppendLine;
          SB.Append('TRANSP:OPAQUE').AppendLine;
          SB.Append('END:VEVENT').AppendLine;

          Inc(Result);
................................................................................

{ ------------------------------------------------------------------------------------------------ }
function ParseParams(var Named, Unnamed: TStringList): Integer;
var
  i: Integer;
  Name, Value: string;
begin



  for i := 1 to ParamCount do begin
    Value := ParamStr(i);

  end;














end {ParseParams};

{ ================================================================================================ }
var
  InputFile, OutputFile: string;
  DB: TSQLiteDatabase;
  Count: Integer;







|











|
<

>







 







>







 







>







 







>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








|


|






>












|








>


|




|



>
>
>
|
|
|







 







>
>
>



<

>
>
>
>
>
>
>
>
>
>
>
>
>







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
..
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
..
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
..
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
..
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
...
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
program ProjectIt_dat2ics;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils, Classes, IniFiles, StrUtils, DateUtils,
  Types, MessageDigest_5 in 'C:\Program Files (x86)\Embarcadero\RAD Studio\9.0\source\soap\wsdlimporter\MessageDigest_5.pas',
  SQLite3Database, SQLite3Abstract;

{ ------------------------------------------------------------------------------------------------ }
function LoadData(const DataFile: TFilename; var DB: TSQLiteDatabase): Integer;
var
  Data: TIniFile;
  InsertActivity: TCustomSQLiteQuery;
  Projects, Entries: TStringList;
  InitialChanges: Int64;
  pi, ei: Integer;
  sProject, sKey, sActiveProject: string;

  dtDummy: TDateTime;
  ID: Int64;
begin
  if not FileExists(DataFile) then
    raise EFileNotFoundException.CreateFmt('File "%s" not found.', [DataFile]);

  Data := TIniFile.Create(DataFile);
  try
    if DB = nil then
................................................................................
                + ');');
    if not DB.TableExists('Activities') then
      DB.Execute('CREATE TABLE Activities'
                + '( id INTEGER PRIMARY KEY'
                + ', project_id INTEGER NOT NULL REFERENCES Projects'
                + ', start DOUBLE NOT NULL'
                + ', duration INTEGER NOT NULL'
                + ', current INTEGER NOT NULL DEFAULT 0'
                + ', UNIQUE (project_id, start)'
                + ');');
    DB.Execute('CREATE VIEW IF NOT EXISTS ProjectActivities AS '
            + ' SELECT p.name AS project, datetime(a.start) AS start, duration, datetime(a.start, "+" || a.duration || " seconds") AS finish'
            + '   FROM Projects p INNER JOIN Activities a ON p.id = a.project_id'
            + ';');
    DB.Execute('CREATE TRIGGER IF NOT EXISTS vi_ProjectActivities '
................................................................................
                                  + 'VALUES(:Project, :Start, :Duration)');
    Projects := TStringList.Create;
    Entries := TStringList.Create;
    try
      Data.ReadSections(Projects);
      InitialChanges := DB.TotalChanges;
      Result := 0;
      sActiveProject := '';
      DB.BeginTransaction;
      try
        for pi := 0 to Projects.Count - 1 do begin
          sProject := Projects[pi];
          Entries.Clear;
          Data.ReadSection(sProject, Entries);
          for ei := 0 to Entries.Count - 1 do begin
................................................................................
            if TryStrToDateTime(sKey, dtDummy, DB.FormatSettings) then begin
              InsertActivity.Parameters.Named[':Project'].SetValue(sProject);
              InsertActivity.Parameters.Named[':Start'].SetValue(sKey);
              InsertActivity.Parameters.Named[':Duration'].SetValue(Data.ReadInteger(sProject, sKey, 0));
              DB.Execute(InsertActivity);
            end;
          end;
          if Data.ReadBool(sProject, 'Active', False) then
            sActiveProject := sProject;
        end;
        Result := DB.TotalChanges - InitialChanges;
        DB.Commit;
      except
        DB.Rollback;
        Result := 0;
        raise;
................................................................................
      Entries.Free;
      Projects.Free;
      InsertActivity.Free;
    end;
  finally
    Data.Free;
  end;

  if Length(sActiveProject) > 0 then begin
    // 1. laatste activity_id van dit project achterhalen
    ID := DB.GetIntValue('SELECT id FROM Projects WHERE name = ?', [sActiveProject]);
    ID := DB.GetIntValue('SELECT id FROM Activities WHERE project_id = ? ORDER BY start DESC, id DESC LIMIT 1', [ID]);
    // 2. dan aantal secondes sinds het starten toewijzen aan de duration (tenzij deze al groter was)
    sKey := DB.GetStringValue('SELECT datetime(start) FROM Activities WHERE id = ?', [ID]);
    if TryStrToDateTime(sKey, dtDummy, DB.FormatSettings) then begin
      DB.BeginTransaction;
      DB.Execute('UPDATE Activities SET duration = ?1, current = 1 WHERE id = ?2',
                  [SecondsBetween(Now, dtDummy), ID]);
      DB.Execute('UPDATE Activities SET current = 0 WHERE id != ?', [ID]);
      DB.Commit;
    end;
  end;
end {LoadData};

{ ------------------------------------------------------------------------------------------------ }
function ExportToICS(const DB: TSQLiteDatabase; const ICSFilename: TFilename): Integer;
var
  SB: TStringBuilder;
  Rows: TCustomSQLiteCursor;
  SS: TStringStream;
  iProject, iStart, iFinish, iCurrent: Integer;
  MD5: IMD5;
  sHash8: UTF8String;
  sHash, sProject: string;
begin
  SB := TStringBuilder.Create(10240);
  try
    Rows := DB.GetCursor('  SELECT p.name'
                      + '   ,      strftime("%Y%m%dT%H%M%S", a.start, "utc") || "Z" AS start'
                      + '   ,      strftime("%Y%m%dT%H%M%S", a.start, "+" || a.duration || " seconds", "utc") || "Z" AS finish'
                      + '   ,      a.current'
                      + '     FROM Activities a'
                      + '          INNER JOIN Projects p ON a.project_id = p.id'
                      + '    WHERE a.duration >= (60 * 5)'
                      + '      AND p.name != "Pauze"'
                      + ' ORDER BY a.start DESC'
                      + ' ,        a.duration DESC'
                      + ' ,        p.id DESC'
                      + ' ,        a.id DESC;');
    try
      Result := 0;
      if not Rows.EOF then begin
        SB.Append('BEGIN:VCALENDAR').AppendLine;
        SB.Append('PRODID:-//martijn.coppoolse.com/ProjectIt v1b dat2ics//NL').AppendLine;
        SB.Append('VERSION:2.0').AppendLine;
        SB.Append('CALSCALE:GREGORIAN').AppendLine;
        SB.Append('METHOD:PUBLISH').AppendLine;
        SB.Append('X-WR-NAME:ProjectIt!').AppendLine;

        iProject := Rows.FieldIndex('name');
        iStart := Rows.FieldIndex('start');
        iFinish := Rows.FieldIndex('finish');
        iCurrent := Rows.FieldIndex('current');

        while not Rows.EOF do begin
          sHash8 := UTF8String(Rows.Fields[iProject].AsString + '|' + Rows.Fields[iStart].AsString);
          MD5 := GetMD5;
          MD5.Init;
          MD5.Update(TByteDynArray(sHash8), Length(sHash8));
          sHash := LowerCase(MD5.AsString);
          sProject := Rows.Fields[iProject].AsString;
          SB.Append('BEGIN:VEVENT').AppendLine;
          SB.Append('DTSTART:' + Rows.Fields[iStart].AsString).AppendLine;
          SB.Append('DTEND:' + Rows.Fields[iFinish].AsString).AppendLine;
          if Rows.Fields[iCurrent].AsInteger <> 0 then
            SB.Append('SUMMARY:' + sProject + '*').AppendLine
          else
            SB.Append('SUMMARY:' + sProject).AppendLine;
          SB.Append('CATEGORIES:' + sProject).AppendLine;
          SB.Append('UID:' + sHash + '@v1b.projectit.2of4.net').AppendLine;
          SB.Append('LAST-MODIFIED:' + Rows.Fields[iFinish].AsString).AppendLine;
          SB.Append('CLASS:PUBLIC').AppendLine;
          SB.Append('STATUS:CONFIRMED').AppendLine;
          SB.Append('TRANSP:OPAQUE').AppendLine;
          SB.Append('END:VEVENT').AppendLine;

          Inc(Result);
................................................................................

{ ------------------------------------------------------------------------------------------------ }
function ParseParams(var Named, Unnamed: TStringList): Integer;
var
  i: Integer;
  Name, Value: string;
begin
  // TODO: read <projectit.dat> <projectit.ics> [/db=<databasename>]

  Result := 0;
  for i := 1 to ParamCount do begin
    Value := ParamStr(i);




    if StartsStr('/', Value) then
      Name := Copy(Value, 2)
    else if StartsStr('--', Value) then
      Name := Copy(Value, 3)
    else if StartsStr('/', Value) then
      Name := Copy(Value, 2)
    else
      Name := '';



  end{for};
end {ParseParams};

{ ================================================================================================ }
var
  InputFile, OutputFile: string;
  DB: TSQLiteDatabase;
  Count: Integer;