unit BudgetForm;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls,
Vcl.Forms, Vcl.Dialogs, DataModule, Data.DB, Vcl.StdCtrls, Vcl.Grids, Vcl.DBGrids, Vcl.ExtCtrls, Vcl.GraphUtil,
FlatDesignColorsFull, TL.Components;
type
TfrmBudget = class(TLForm)
Panel1: TPanel;
LDBGrid1: TLDBGrid;
Panel2: TPanel;
Button1: TButton;
Panel3: TPanel;
LDBGrid2: TLDBGrid;
Panel4: TPanel;
LDBGrid3: TLDBGrid;
procedure Button1Click(Sender: TObject);
procedure LDBGrid3KeyDown(
Sender: TObject;
var Key: Word;
Shift: TShiftState);
procedure LDBGrid3DrawColumnCell(
Sender: TObject;
const Rect: TRect;
DataCol: Integer;
Column: TColumn;
State: TGridDrawState);
procedure LDBGrid2DrawColumnCell(
Sender: TObject;
const Rect: TRect;
DataCol: Integer;
Column: TColumn;
State: TGridDrawState);
private
{ Private declarations }
procedure QueryInsert;
public
{ Public declarations }
procedure InitGrids;
end;
var
frmBudget: TfrmBudget;
implementation
{$R *.dfm}
uses
BudgetEditForm;
procedure TfrmBudget.InitGrids;
begin
Self.LDBGrid1.SetColumnAttr;
Self.LDBGrid2.SetColumnAttr;
Self.LDBGrid3.SetColumnAttr;
Self.LDBGrid2.Columns[0].Width:= Self.LDBGrid3.Columns[0].Width + Self.LDBGrid3.Columns[1].Width;
end;
procedure TfrmBudget.QueryInsert;
var
iAffectedRows: Integer;
JaarID: Integer;
{ TODO 1 -oTL -cFeature :
Als er Subcategorieën worden toegevoegd met eventueel nieuwe Categorieën,
dan automatisch de budgetten aanvullen met deze records.
SET @Subcategorie = 124;
INSERT IGNORE INTO Budget (Budget.JaarID, Budget.CategorieID, Budget.SubcategorieID)
SELECT BudgetJaar.ID, Categorie.ID, Subcategorie.ID
FROM
Categorie
JOIN Subcategorie ON Categorie.ID = Subcategorie.CategorieID
JOIN BudgetJaar
WHERE
Subcategorie.ID = @Subcategorie;
}
begin
dm.CommandAppendBudgetJaar.CommandText:=
'INSERT IGNORE INTO Budget (Budget.JaarID, Budget.CategorieID, Budget.SubcategorieID) ' +
'SELECT :Jaar, Categorie.ID, Subcategorie.ID ' + 'FROM ' + ' Categorie ' +
' JOIN Subcategorie ON Categorie.ID = Subcategorie.CategorieID;';
JaarID:= dm.DataSetBudgetJaar.FieldByName('ID').Value;
dm.CommandAppendBudgetJaar.Execute(
iAffectedRows,
VarArrayOf([JaarID]));
end;
procedure TfrmBudget.Button1Click(Sender: TObject);
var
j: Integer;
begin
try
dm.DataSetBudgetJaar.Last;
j:= dm.DataSetBudgetJaar.FieldByName('Jaar').AsInteger;
if j <> 0 then
inc(j)
else
j:= CurrentYear;
{ TODO 1 -oTL -cFeature :
Init procedure toevoegen
Query maken om alle jaren in het mutatie bestand te vinden
Als er nog geen Budget record voor dat jaar is er een aanmaken
SQL 'SELECT DISTINCT YEAR(Datum) AS Jaar FROM Mutaties ORDER BY Jaar ASC;' }
(* for j:= 2016 to 2024 do
begin
*)
dm.DataSetBudgetJaar.Append;
dm.DataSetBudgetJaar.FieldByName('Jaar').AsInteger:= j;
dm.DataSetBudgetJaar.Post;
QueryInsert;
(*
end;
*)
dm.DataSetBudget.Active:= False;
dm.DataSetBudget.Active:= True;
dm.DataSetBudgetTotaal.Active:= False;
dm.DataSetBudgetTotaal.Active:= True;
dm.DataSetBudgetEdit.Active:= False;
dm.DataSetBudgetEdit.Active:= True;
Self.LDBGrid2.Refresh;
InitGrids;
except
dm.DataSetBudgetJaar.CancelUpdates;
end;
end;
procedure TfrmBudget.LDBGrid2DrawColumnCell(
Sender: TObject;
const Rect: TRect;
DataCol: Integer;
Column: TColumn;
State: TGridDrawState);
var
Field: TField;
Grid: TLDBGrid;
DataSet: TDataSet;
R: TRect;
begin
if Sender is TLDBGrid then
begin
// Create some local variables to write more readable code
Grid:= (Sender as TLDBGrid);
DataSet:= Grid.DataSource.DataSet;
Field:= Column.Field;
R:= Rect;
InflateRect(
R,
-10,
-15);
if Assigned(Field) and (DataCol = 0) then // Only color description column
begin
case DataSet.RecNo of
1: // Inkomsten
if not(gdSelected in State) then
Grid.Canvas.Brush.Color:= clGreen_sea_100
else
Grid.Canvas.Brush.Color:= clGreen_sea_200;
2: // Vaste lasten
if not(gdSelected in State) then
Grid.Canvas.Brush.Color:= clAlizarin_100
else
Grid.Canvas.Brush.Color:= clAlizarin_200;
3: // Variabele lasten
if not(gdSelected in State) then
Grid.Canvas.Brush.Color:= clMidnight_blue_100
else
Grid.Canvas.Brush.Color:= clMidnight_blue_200;
4: // Totaal
if not(gdSelected in State) then
Grid.Canvas.Brush.Color:= clMidnight_blue_100
else
Grid.Canvas.Brush.Color:= clMidnight_blue_200
end;
// Always use black for selected (highlighted) rows in desciption column
if (gdSelected in State) then
begin
Grid.Canvas.Font.Color:= clBlack;
Grid.Canvas.Font.Style:= [fsBold];
end;
end;
Grid.Canvas.FillRect(Rect);
Grid.DefaultDrawColumnCell(
R,
DataCol,
Column,
State);
// Decorate Total row)
if (DataSet.RecNo = 4) then
begin
if (gdSelected in State) and (Field.DataType = ftFloat) then
Grid.Canvas.Font.Color:= clWhite // Default behavior for numbers
else
Grid.Canvas.Font.Color:= clBlack; // Description always black
Grid.Canvas.Font.Style:= [fsBold]; // Total row in Bold
Grid.Canvas.FillRect(Rect);
// Grid.DefaultDrawColumnCell(R, DataCol, Column, State);
// Black line above total row
// Grid.Canvas.Pen.Color:= clBlack; // clpeter_river_500);
// Grid.Canvas.Pen.Width:= 15;
// Grid.Canvas.MoveTo(Rect.Left, Rect.Bottom+5);
// Grid.Canvas.LineTo(Rect.Right, Rect.Bottom+5);
Grid.TotalRow:= True;
end
else
begin // White lines for all other rows
// Grid.Canvas.Pen.Color:= clWhite;
// Grid.Canvas.Pen.Width:= 3;
// Grid.Canvas.MoveTo(Rect.Left, Rect.Top);
// Grid.Canvas.LineTo(Rect.Right, Rect.Top);
end;
end;
end;
procedure TfrmBudget.LDBGrid3DrawColumnCell(
Sender: TObject;
const Rect: TRect;
DataCol: Integer;
Column: TColumn;
State: TGridDrawState);
begin
if Sender is TLDBGrid then
begin
if Assigned(Column.Field) and ((Column.Title.Caption = 'Categorie') or (Column.Title.Caption = 'Subcategorie'))
then
begin
if ((Sender as TLDBGrid).DataSource.DataSet.FieldByName('Inkomsten').AsInteger = 1) then
begin
if not(gdSelected in State) then
(Sender as TLDBGrid).Canvas.Brush.Color:= clGreen_sea_100
else
(Sender as TLDBGrid).Canvas.Brush.Color:= clGreen_sea_200;
end
else if ((Sender as TLDBGrid).DataSource.DataSet.FieldByName('Vaste kosten').AsInteger = 1) then
begin
if not(gdSelected in State) then
(Sender as TLDBGrid).Canvas.Brush.Color:= clAlizarin_100
else
(Sender as TLDBGrid).Canvas.Brush.Color:= clAlizarin_200
end
else
begin
if not(gdSelected in State) then
(Sender as TLDBGrid).Canvas.Brush.Color:= clMidnight_blue_100
else
(Sender as TLDBGrid).Canvas.Brush.Color:= clMidnight_blue_200;
end;
if (gdSelected in State) then
begin
(Sender as TLDBGrid).Canvas.Font.Color:= clBlack;
(Sender as TLDBGrid).Canvas.Font.Style:= [fsBold];
end;
end;
end;
end;
procedure TfrmBudget.LDBGrid3KeyDown(
Sender: TObject;
var Key: Word;
Shift: TShiftState);
var
Bookmark: TBookmark;
begin
if (Key = VK_F2) then
begin
case frmBudgetEdit.ShowModal of
mrOk:
begin
Bookmark:= Self.LDBGrid3.DataSource.DataSet.Bookmark;
if dm.DataSetBudgetEdit.State = dsEdit then
dm.DataSetBudgetEdit.Post;
Self.LDBGrid3.DataSource.DataSet.Active:= False;
Self.LDBGrid3.DataSource.DataSet.Active:= True;
Self.LDBGrid2.DataSource.DataSet.Active:= False;
Self.LDBGrid2.DataSource.DataSet.Active:= True;
InitGrids;
Self.LDBGrid3.DataSource.DataSet.Bookmark:= Bookmark;
end;
mrCancel:
begin
dm.DataSetBudgetEdit.Cancel;
end;
end;
end;
end;
end.
