1unit BudgetForm;
 2
 3interface
 4
 5uses
 6  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls,
 7  Vcl.Forms, Vcl.Dialogs, DataModule, Data.DB, Vcl.StdCtrls, Vcl.Grids, Vcl.DBGrids, Vcl.ExtCtrls, Vcl.GraphUtil,
 8  FlatDesignColorsFull, TL.Components;
 9
10type
11  TfrmBudget = class(TLForm)
12    Panel1: TPanel;
13    LDBGrid1: TLDBGrid;
14    Panel2: TPanel;
15    Button1: TButton;
16    Panel3: TPanel;
17    LDBGrid2: TLDBGrid;
18    Panel4: TPanel;
19    LDBGrid3: TLDBGrid;
20    procedure Button1Click(Sender: TObject);
21    procedure LDBGrid3KeyDown(
22      Sender: TObject;
23      var Key: Word;
24      Shift: TShiftState);
25    procedure LDBGrid3DrawColumnCell(
26      Sender: TObject;
27      const Rect: TRect;
28      DataCol: Integer;
29      Column: TColumn;
30      State: TGridDrawState);
31    procedure LDBGrid2DrawColumnCell(
32      Sender: TObject;
33      const Rect: TRect;
34      DataCol: Integer;
35      Column: TColumn;
36      State: TGridDrawState);
37  private
38    { Private declarations }
39    procedure QueryInsert;
40  public
41    { Public declarations }
42    procedure InitGrids;
43  end;
44
45var
46  frmBudget: TfrmBudget;
47
48implementation
49
50{$R *.dfm}
51
52uses
53  BudgetEditForm;
54
55procedure TfrmBudget.InitGrids;
56begin
57  Self.LDBGrid1.SetColumnAttr;
58  Self.LDBGrid2.SetColumnAttr;
59  Self.LDBGrid3.SetColumnAttr;
60  Self.LDBGrid2.Columns[0].Width:= Self.LDBGrid3.Columns[0].Width + Self.LDBGrid3.Columns[1].Width;
61end;
62
63procedure TfrmBudget.QueryInsert;
64var
65  iAffectedRows: Integer;
66  JaarID: Integer;
67  { TODO 1 -oTL -cFeature :
68    Als er Subcategorieën worden toegevoegd met eventueel nieuwe Categorieën,
69    dan automatisch de budgetten aanvullen met deze records.
70    SET @Subcategorie = 124;
71
72    INSERT IGNORE INTO Budget (Budget.JaarID, Budget.CategorieID, Budget.SubcategorieID)
73    SELECT BudgetJaar.ID, Categorie.ID, Subcategorie.ID
74    FROM
75    Categorie
76    JOIN Subcategorie ON Categorie.ID = Subcategorie.CategorieID
77    JOIN BudgetJaar
78    WHERE
79    Subcategorie.ID = @Subcategorie;
80  }
81begin
82  dm.CommandAppendBudgetJaar.CommandText:=
83    'INSERT IGNORE INTO Budget (Budget.JaarID, Budget.CategorieID, Budget.SubcategorieID) ' +
84    'SELECT :Jaar, Categorie.ID, Subcategorie.ID ' + 'FROM ' + '  Categorie ' +
85    '  JOIN Subcategorie ON Categorie.ID = Subcategorie.CategorieID;';
86  JaarID:= dm.DataSetBudgetJaar.FieldByName('ID').Value;
87  dm.CommandAppendBudgetJaar.Execute(
88    iAffectedRows,
89    VarArrayOf([JaarID]));
90end;
91
92procedure TfrmBudget.Button1Click(Sender: TObject);
93var
94  j: Integer;
95begin
96  try
97    dm.DataSetBudgetJaar.Last;
98    j:= dm.DataSetBudgetJaar.FieldByName('Jaar').AsInteger;
99    if j <> 0 then
100      inc(j)
101    else
102      j:= CurrentYear;
103
104    { TODO 1 -oTL -cFeature :
105      Init procedure toevoegen
106      Query maken om alle jaren in het mutatie bestand te vinden
107      Als er nog geen Budget record voor dat jaar is er een aanmaken
108      SQL 'SELECT DISTINCT YEAR(Datum) AS Jaar FROM Mutaties ORDER BY Jaar ASC;' }
109
110(*    for j:= 2016 to 2024 do
111      begin
112*)
113        dm.DataSetBudgetJaar.Append;
114        dm.DataSetBudgetJaar.FieldByName('Jaar').AsInteger:= j;
115        dm.DataSetBudgetJaar.Post;
116        QueryInsert;
117(*
118      end;
119*)
120    dm.DataSetBudget.Active:= False;
121    dm.DataSetBudget.Active:= True;
122    dm.DataSetBudgetTotaal.Active:= False;
123    dm.DataSetBudgetTotaal.Active:= True;
124    dm.DataSetBudgetEdit.Active:= False;
125    dm.DataSetBudgetEdit.Active:= True;
126
127    Self.LDBGrid2.Refresh;
128    InitGrids;
129  except
130    dm.DataSetBudgetJaar.CancelUpdates;
131  end;
132end;
133
134procedure TfrmBudget.LDBGrid2DrawColumnCell(
135  Sender: TObject;
136  const Rect: TRect;
137  DataCol: Integer;
138  Column: TColumn;
139  State: TGridDrawState);
140var
141  Field: TField;
142  Grid: TLDBGrid;
143  DataSet: TDataSet;
144  R: TRect;
145begin
146  if Sender is TLDBGrid then
147    begin
148      // Create some local variables to write more readable code
149      Grid:= (Sender as TLDBGrid);
150      DataSet:= Grid.DataSource.DataSet;
151      Field:= Column.Field;
152
153      R:= Rect;
154      InflateRect(
155        R,
156        -10,
157        -15);
158
159      if Assigned(Field) and (DataCol = 0) then // Only color description column
160        begin
161          case DataSet.RecNo of
162            1: // Inkomsten
163              if not(gdSelected in State) then
164                Grid.Canvas.Brush.Color:= clGreen_sea_100
165              else
166                Grid.Canvas.Brush.Color:= clGreen_sea_200;
167            2: // Vaste lasten
168              if not(gdSelected in State) then
169                Grid.Canvas.Brush.Color:= clAlizarin_100
170              else
171                Grid.Canvas.Brush.Color:= clAlizarin_200;
172            3: // Variabele lasten
173              if not(gdSelected in State) then
174                Grid.Canvas.Brush.Color:= clMidnight_blue_100
175              else
176                Grid.Canvas.Brush.Color:= clMidnight_blue_200;
177            4: // Totaal
178              if not(gdSelected in State) then
179                Grid.Canvas.Brush.Color:= clMidnight_blue_100
180              else
181                Grid.Canvas.Brush.Color:= clMidnight_blue_200
182          end;
183
184          // Always use black for selected (highlighted) rows in desciption column
185          if (gdSelected in State) then
186            begin
187              Grid.Canvas.Font.Color:= clBlack;
188              Grid.Canvas.Font.Style:= [fsBold];
189            end;
190
191        end;
192
193      Grid.Canvas.FillRect(Rect);
194      Grid.DefaultDrawColumnCell(
195        R,
196        DataCol,
197        Column,
198        State);
199
200      // Decorate Total row)
201      if (DataSet.RecNo = 4) then
202        begin
203
204          if (gdSelected in State) and (Field.DataType = ftFloat) then
205            Grid.Canvas.Font.Color:= clWhite // Default behavior for numbers
206          else
207            Grid.Canvas.Font.Color:= clBlack; // Description always black
208          Grid.Canvas.Font.Style:= [fsBold]; // Total row in Bold
209
210          Grid.Canvas.FillRect(Rect);
211          // Grid.DefaultDrawColumnCell(R, DataCol, Column, State);
212
213          // Black line above total row
214          // Grid.Canvas.Pen.Color:= clBlack; // clpeter_river_500);
215          // Grid.Canvas.Pen.Width:= 15;
216          // Grid.Canvas.MoveTo(Rect.Left, Rect.Bottom+5);
217          // Grid.Canvas.LineTo(Rect.Right, Rect.Bottom+5);
218          Grid.TotalRow:= True;
219        end
220      else
221        begin // White lines for all other rows
222          // Grid.Canvas.Pen.Color:= clWhite;
223          // Grid.Canvas.Pen.Width:= 3;
224          // Grid.Canvas.MoveTo(Rect.Left, Rect.Top);
225          // Grid.Canvas.LineTo(Rect.Right, Rect.Top);
226        end;
227    end;
228end;
229
230procedure TfrmBudget.LDBGrid3DrawColumnCell(
231  Sender: TObject;
232  const Rect: TRect;
233  DataCol: Integer;
234  Column: TColumn;
235  State: TGridDrawState);
236begin
237  if Sender is TLDBGrid then
238    begin
239      if Assigned(Column.Field) and ((Column.Title.Caption = 'Categorie') or (Column.Title.Caption = 'Subcategorie'))
240      then
241        begin
242          if ((Sender as TLDBGrid).DataSource.DataSet.FieldByName('Inkomsten').AsInteger = 1) then
243            begin
244              if not(gdSelected in State) then
245                (Sender as TLDBGrid).Canvas.Brush.Color:= clGreen_sea_100
246              else
247                (Sender as TLDBGrid).Canvas.Brush.Color:= clGreen_sea_200;
248            end
249          else if ((Sender as TLDBGrid).DataSource.DataSet.FieldByName('Vaste kosten').AsInteger = 1) then
250            begin
251              if not(gdSelected in State) then
252                (Sender as TLDBGrid).Canvas.Brush.Color:= clAlizarin_100
253              else
254                (Sender as TLDBGrid).Canvas.Brush.Color:= clAlizarin_200
255            end
256          else
257            begin
258              if not(gdSelected in State) then
259                (Sender as TLDBGrid).Canvas.Brush.Color:= clMidnight_blue_100
260              else
261                (Sender as TLDBGrid).Canvas.Brush.Color:= clMidnight_blue_200;
262            end;
263          if (gdSelected in State) then
264            begin
265              (Sender as TLDBGrid).Canvas.Font.Color:= clBlack;
266              (Sender as TLDBGrid).Canvas.Font.Style:= [fsBold];
267            end;
268        end;
269    end;
270end;
271
272procedure TfrmBudget.LDBGrid3KeyDown(
273  Sender: TObject;
274  var Key: Word;
275  Shift: TShiftState);
276var
277  Bookmark: TBookmark;
278begin
279  if (Key = VK_F2) then
280    begin
281      case frmBudgetEdit.ShowModal of
282        mrOk:
283          begin
284            Bookmark:= Self.LDBGrid3.DataSource.DataSet.Bookmark;
285            if dm.DataSetBudgetEdit.State = dsEdit then
286              dm.DataSetBudgetEdit.Post;
287            Self.LDBGrid3.DataSource.DataSet.Active:= False;
288            Self.LDBGrid3.DataSource.DataSet.Active:= True;
289            Self.LDBGrid2.DataSource.DataSet.Active:= False;
290            Self.LDBGrid2.DataSource.DataSet.Active:= True;
291            InitGrids;
292            Self.LDBGrid3.DataSource.DataSet.Bookmark:= Bookmark;
293          end;
294        mrCancel:
295          begin
296            dm.DataSetBudgetEdit.Cancel;
297          end;
298      end;
299    end;
300
301end;
302
303end.
304