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 2023 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.