unit DataModule;

interface

uses
  Winapi.Windows, System.SysUtils, System.Classes, Data.DB, Data.Win.ADODB, Vcl.Forms,

  Winapi.Messages, System.Variants, Vcl.Graphics, Vcl.Controls, Vcl.Dialogs;

const
  { Connection string }
  ConnectionStr = 'Provider=MSDASQL.1; Password=%s; Persist Security Info=True; User ID=%s;' +
    'Extended Properties="DRIVER={MariaDB ODBC 3.2 Driver}; SERVER=%s; DATABASE=FinAdm; PORT=17002;"';

  { User access }
  DefaultUserName = 'user';
  DefaultPassWord = 'password';
  DefaultServer = 'server';

type
  Tdm = class(TDataModule)
    Connection: TADOConnection;
    DataSetMutaties: TADODataSet;
    DataSourceMutaties: TDataSource;
    DataSetSubcategories: TADODataSet;
    DataSourceSubcategories: TDataSource;
    DataSetCategories: TADODataSet;
    DataSourceCategories: TDataSource;
    DataSetCategoriesLU: TADODataSet;
    DataSetSubcategoriesLU: TADODataSet;
    DataSourceCategoriesLU: TDataSource;
    DataSourceSubcategoriesLU: TDataSource;
    DataSetMutatiesCatID: TIntegerField;
    DataSetMutatiesSubID: TIntegerField;
    DataSetMutatiesDatum: TDateField;
    DataSetMutatiesBedrag: TFMTBCDField;
    DataSetMutatiesNaam_tegenpartij: TWideStringField;
    DataSetMutatiesOmschrijving_1: TWideStringField;
    DataSetMutatiesOorspr_bedrag: TFMTBCDField;
    DataSetMutatiesOorspr_munt: TWideStringField;
    DataSetMutatiesKoers: TFMTBCDField;
    DataSetMutatiesCategorie: TStringField;
    DataSetMutatiesSubcategorie: TStringField;
    DataSourceBudgetJaar: TDataSource;
    DataSetBudgetJaar: TADODataSet;
    DataSetBudget: TADODataSet;
    DataSourceBudget: TDataSource;
    DataSetBudgetTotaal: TADODataSet;
    DataSourceBudgetTotaal: TDataSource;
    CommandAppendBudgetJaar: TADOCommand;
    DataSetBudgetEdit: TADODataSet;
    DataSourceBudgetEdit: TDataSource;
    DataSetBudgetEditID: TAutoIncField;
    DataSetBudgetEditCreated: TDateTimeField;
    DataSetBudgetEditModified: TDateTimeField;
    DataSetBudgetEditCategorieID: TIntegerField;
    DataSetBudgetEditSubcategorieID: TIntegerField;
    DataSetBudgetEditJaarID: TIntegerField;
    DataSetBudgetEditJan: TFMTBCDField;
    DataSetBudgetEditFeb: TFMTBCDField;
    DataSetBudgetEditMrt: TFMTBCDField;
    DataSetBudgetEditApr: TFMTBCDField;
    DataSetBudgetEditMei: TFMTBCDField;
    DataSetBudgetEditJun: TFMTBCDField;
    DataSetBudgetEditJul: TFMTBCDField;
    DataSetBudgetEditAug: TFMTBCDField;
    DataSetBudgetEditSep: TFMTBCDField;
    DataSetBudgetEditOkt: TFMTBCDField;
    DataSetBudgetEditNov: TFMTBCDField;
    DataSetBudgetEditDec: TFMTBCDField;
    DataSetReportSubCat: TADODataSet;
    DataSourceReportCat: TDataSource;
    DataSourceReportSubCat: TDataSource;
    CommandImport: TADOCommand;
    DataSetImportHistory: TADODataSet;
    DataSetImportHistoryID: TAutoIncField;
    DataSetImportHistoryCreated: TDateTimeField;
    DataSetImportHistoryModified: TDateTimeField;
    DataSetImportHistoryFileName: TWideStringField;
    DataSetReportDetail: TADODataSet;
    DataSourceReportDetail: TDataSource;
    DataSetReportDetailCatID: TAutoIncField;
    DataSetReportDetailCategorie: TWideStringField;
    DataSetReportDetailSubID: TAutoIncField;
    DataSetReportDetailSubcategorie: TWideStringField;
    DataSetReportDetailDatum: TDateField;
    DataSetReportDetailNaam_tegenpartij: TWideStringField;
    DataSetReportDetailOmschrijving_1: TWideStringField;
    DataSetReportDetailBedrag: TFMTBCDField;
    DataSetReportCat: TADODataSet;
    DataSetAutoAssignCategorie: TADODataSet;
    DataSourceAutoAssignCategorie: TDataSource;
    DatasetReport: TADODataSet;
    DatasetReportCategorie: TWideStringField;
    DatasetReportSubcategorie: TWideStringField;
    DatasetReportDatum: TDateField;
    DatasetReportNaam_tegenpartij: TWideStringField;
    DatasetReportOmschrijving_1: TWideStringField;
    DatasetReportBedrag: TFMTBCDField;
    dsReport: TDataSource;
    procedure DataSetMutatiesCatIDChange(Sender: TField);
    procedure DataSetBeforePost(DataSet: TDataSet);
    procedure DataSetReportCatAfterScroll(DataSet: TDataSet);
    procedure DataSetReportSubCatAfterScroll(DataSet: TDataSet);
  private
    { Private declarations }
  public
    { Public declarations }
    function Connect(Server, UserName, Password: string): boolean;
  end;

var
  dm: Tdm;

implementation

{%CLASSGROUP 'Vcl.Controls.TControl'}

uses MainForm;
{$R *.dfm}
// uses
// MainForm;

procedure Tdm.DataSetMutatiesCatIDChange(Sender: TField);
begin
  dm.DataSetMutatiesSubID.Value:= 0;
end;

procedure Tdm.DataSetReportCatAfterScroll(DataSet: TDataSet);
begin
  for var i:= 0 to dm.DataSetReportSubCat.Parameters.Count - 1 do
    begin
      if dm.DataSetReportSubCat.Parameters[i].Name = 'Cat' then
        dm.DataSetReportSubCat.Parameters[i].Value:= dm.DataSetReportCat.FieldByName('ID').AsInteger;
      if dm.DataSetReportSubCat.Parameters[i].Name = 'VasteKosten' then
        dm.DataSetReportSubCat.Parameters[i].Value:= dm.DataSetReportCat.FieldByName('VK').AsInteger;

      (* if dm.DataSetReportCat.Parameters[i].Name = 'BetaalRekening' then
        dm.DataSetReportCat.Parameters[i].Value := Self.RegVars.Values
        ['BetaalRekening'];
        if dm.DataSetReportCat.Parameters[i].Name = 'SpaarRekening' then
        dm.DataSetReportCat.Parameters[i].Value := Self.RegVars.Values
        ['SpaarRekening'];
      *)

    end;
  dm.DataSetReportSubCat.Active:= False;
  dm.DataSetReportSubCat.Active:= True;
  if Assigned(frmMain) then
    begin
      frmMain.LDBGridSubCat.SetColumnAttr;
      frmMain.LDBGridSubCat.Columns[0].Width:= frmMain.LDBGridCat.Columns[0].Width;
      frmMain.LDBGridSubCatColEnter(frmMain.LDBGridSubCat.Columns[frmMain.LDBGridSubCat.SelectedIndex]);
    end;
end;

procedure Tdm.DataSetReportSubCatAfterScroll(DataSet: TDataSet);
begin
  if Assigned(frmMain) then
    frmMain.LDBGridSubCatColEnter(Self);
end;

function Tdm.Connect(Server, UserName, Password: string): boolean;
begin
  Result:= True;
  Connection.Connected:= False;

  Connection.ConnectionString:= Format(
    ConnectionStr,
    [Password, UserName, Server]);

  { Disable login prompt. }
  Connection.LoginPrompt:= False;

  try
    Connection.Connected:= True;
    DataSetAutoAssignCategorie.Active:= True;
    DataSetBudget.Active:= True;
    DataSetBudgetEdit.Active:= True;
    DataSetBudgetJaar.Active:= True;
    DataSetBudgetTotaal.Active:= True;
    DataSetCategories.Active:= True;
    DataSetCategoriesLU.Active:= True;
    DataSetImportHistory.Active:= True;
    DataSetMutaties.Active:= True;
    DataSetReportCat.Active:= True;
    DataSetReportDetail.Active:= True;
    DataSetReportSubCat.Active:= True;
    DataSetSubcategories.Active:= True;
    DataSetSubcategoriesLU.Active:= True;
  except
    Result:= False;
  end;
end;

procedure Tdm.DataSetBeforePost(DataSet: TDataSet);
begin
  case Application.MessageBox('Save edits to database?', 'Information', MB_YESNOCANCEL) of
    IDCANCEL:
      begin
        DataSet.Cancel;
        Abort;
      end;
    IDYES:
      ;
    IDNO:
      Abort;
  end;
end;

end.