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.1 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;
    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;
    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.