1unit DataModule;
 2
 3interface
 4
 5uses
 6  Winapi.Windows, System.SysUtils, System.Classes, Data.DB, Data.Win.ADODB, Vcl.Forms,
 7
 8  Winapi.Messages, System.Variants, Vcl.Graphics, Vcl.Controls, Vcl.Dialogs;
 9
10const
11  { Connection string }
12  ConnectionStr = 'Provider=MSDASQL.1; Password=%s; Persist Security Info=True; User ID=%s;' +
13    'Extended Properties="DRIVER={MariaDB ODBC 3.2 Driver}; SERVER=%s; DATABASE=FinAdm; PORT=17002;"';
14
15  { User access }
16  DefaultUserName = 'user';
17  DefaultPassWord = 'password';
18  DefaultServer = 'server';
19
20type
21  Tdm = class(TDataModule)
22    Connection: TADOConnection;
23    DataSetMutaties: TADODataSet;
24    DataSourceMutaties: TDataSource;
25    DataSetSubcategories: TADODataSet;
26    DataSourceSubcategories: TDataSource;
27    DataSetCategories: TADODataSet;
28    DataSourceCategories: TDataSource;
29    DataSetCategoriesLU: TADODataSet;
30    DataSetSubcategoriesLU: TADODataSet;
31    DataSourceCategoriesLU: TDataSource;
32    DataSourceSubcategoriesLU: TDataSource;
33    DataSetMutatiesCatID: TIntegerField;
34    DataSetMutatiesSubID: TIntegerField;
35    DataSetMutatiesDatum: TDateField;
36    DataSetMutatiesBedrag: TFMTBCDField;
37    DataSetMutatiesNaam_tegenpartij: TWideStringField;
38    DataSetMutatiesOmschrijving_1: TWideStringField;
39    DataSetMutatiesOorspr_bedrag: TFMTBCDField;
40    DataSetMutatiesOorspr_munt: TWideStringField;
41    DataSetMutatiesKoers: TFMTBCDField;
42    DataSetMutatiesCategorie: TStringField;
43    DataSetMutatiesSubcategorie: TStringField;
44    DataSourceBudgetJaar: TDataSource;
45    DataSetBudgetJaar: TADODataSet;
46    DataSetBudget: TADODataSet;
47    DataSourceBudget: TDataSource;
48    DataSetBudgetTotaal: TADODataSet;
49    DataSourceBudgetTotaal: TDataSource;
50    CommandAppendBudgetJaar: TADOCommand;
51    DataSetBudgetEdit: TADODataSet;
52    DataSourceBudgetEdit: TDataSource;
53    DataSetBudgetEditID: TAutoIncField;
54    DataSetBudgetEditCreated: TDateTimeField;
55    DataSetBudgetEditModified: TDateTimeField;
56    DataSetBudgetEditCategorieID: TIntegerField;
57    DataSetBudgetEditSubcategorieID: TIntegerField;
58    DataSetBudgetEditJaarID: TIntegerField;
59    DataSetBudgetEditJan: TFMTBCDField;
60    DataSetBudgetEditFeb: TFMTBCDField;
61    DataSetBudgetEditMrt: TFMTBCDField;
62    DataSetBudgetEditApr: TFMTBCDField;
63    DataSetBudgetEditMei: TFMTBCDField;
64    DataSetBudgetEditJun: TFMTBCDField;
65    DataSetBudgetEditJul: TFMTBCDField;
66    DataSetBudgetEditAug: TFMTBCDField;
67    DataSetBudgetEditSep: TFMTBCDField;
68    DataSetBudgetEditOkt: TFMTBCDField;
69    DataSetBudgetEditNov: TFMTBCDField;
70    DataSetBudgetEditDec: TFMTBCDField;
71    DataSetReportSubCat: TADODataSet;
72    DataSourceReportCat: TDataSource;
73    DataSourceReportSubCat: TDataSource;
74    CommandImport: TADOCommand;
75    DataSetImportHistory: TADODataSet;
76    DataSetImportHistoryID: TAutoIncField;
77    DataSetImportHistoryCreated: TDateTimeField;
78    DataSetImportHistoryModified: TDateTimeField;
79    DataSetImportHistoryFileName: TWideStringField;
80    DataSetReportDetail: TADODataSet;
81    DataSourceReportDetail: TDataSource;
82    DataSetReportDetailCatID: TAutoIncField;
83    DataSetReportDetailCategorie: TWideStringField;
84    DataSetReportDetailSubID: TAutoIncField;
85    DataSetReportDetailSubcategorie: TWideStringField;
86    DataSetReportDetailDatum: TDateField;
87    DataSetReportDetailNaam_tegenpartij: TWideStringField;
88    DataSetReportDetailOmschrijving_1: TWideStringField;
89    DataSetReportDetailBedrag: TFMTBCDField;
90    DataSetReportCat: TADODataSet;
91    DataSetAutoAssignCategorie: TADODataSet;
92    DataSourceAutoAssignCategorie: TDataSource;
93    DatasetReport: TADODataSet;
94    DatasetReportCategorie: TWideStringField;
95    DatasetReportSubcategorie: TWideStringField;
96    DatasetReportDatum: TDateField;
97    DatasetReportNaam_tegenpartij: TWideStringField;
98    DatasetReportOmschrijving_1: TWideStringField;
99    DatasetReportBedrag: TFMTBCDField;
100    dsReport: TDataSource;
101    procedure DataSetMutatiesCatIDChange(Sender: TField);
102    procedure DataSetBeforePost(DataSet: TDataSet);
103    procedure DataSetReportCatAfterScroll(DataSet: TDataSet);
104    procedure DataSetReportSubCatAfterScroll(DataSet: TDataSet);
105  private
106    { Private declarations }
107  public
108    { Public declarations }
109    function Connect(Server, UserName, Password: string): boolean;
110  end;
111
112var
113  dm: Tdm;
114
115implementation
116
117{%CLASSGROUP 'Vcl.Controls.TControl'}
118
119uses MainForm;
120{$R *.dfm}
121// uses
122// MainForm;
123
124procedure Tdm.DataSetMutatiesCatIDChange(Sender: TField);
125begin
126  dm.DataSetMutatiesSubID.Value:= 0;
127end;
128
129procedure Tdm.DataSetReportCatAfterScroll(DataSet: TDataSet);
130begin
131  for var i:= 0 to dm.DataSetReportSubCat.Parameters.Count - 1 do
132    begin
133      if dm.DataSetReportSubCat.Parameters[i].Name = 'Cat' then
134        dm.DataSetReportSubCat.Parameters[i].Value:= dm.DataSetReportCat.FieldByName('ID').AsInteger;
135      if dm.DataSetReportSubCat.Parameters[i].Name = 'VasteKosten' then
136        dm.DataSetReportSubCat.Parameters[i].Value:= dm.DataSetReportCat.FieldByName('VK').AsInteger;
137
138      (* if dm.DataSetReportCat.Parameters[i].Name = 'BetaalRekening' then
139        dm.DataSetReportCat.Parameters[i].Value := Self.RegVars.Values
140        ['BetaalRekening'];
141        if dm.DataSetReportCat.Parameters[i].Name = 'SpaarRekening' then
142        dm.DataSetReportCat.Parameters[i].Value := Self.RegVars.Values
143        ['SpaarRekening'];
144      *)
145
146    end;
147  dm.DataSetReportSubCat.Active:= False;
148  dm.DataSetReportSubCat.Active:= True;
149  if Assigned(frmMain) then
150    begin
151      frmMain.LDBGridSubCat.SetColumnAttr;
152      frmMain.LDBGridSubCat.Columns[0].Width:= frmMain.LDBGridCat.Columns[0].Width;
153      frmMain.LDBGridSubCatColEnter(frmMain.LDBGridSubCat.Columns[frmMain.LDBGridSubCat.SelectedIndex]);
154    end;
155end;
156
157procedure Tdm.DataSetReportSubCatAfterScroll(DataSet: TDataSet);
158begin
159  if Assigned(frmMain) then
160    frmMain.LDBGridSubCatColEnter(Self);
161end;
162
163function Tdm.Connect(Server, UserName, Password: string): boolean;
164begin
165  Result:= True;
166  Connection.Connected:= False;
167
168  Connection.ConnectionString:= Format(
169    ConnectionStr,
170    [Password, UserName, Server]);
171
172  { Disable login prompt. }
173  Connection.LoginPrompt:= False;
174
175  try
176    Connection.Connected:= True;
177    DataSetAutoAssignCategorie.Active:= True;
178    DataSetBudget.Active:= True;
179    DataSetBudgetEdit.Active:= True;
180    DataSetBudgetJaar.Active:= True;
181    DataSetBudgetTotaal.Active:= True;
182    DataSetCategories.Active:= True;
183    DataSetCategoriesLU.Active:= True;
184    DataSetImportHistory.Active:= True;
185    DataSetMutaties.Active:= True;
186    DataSetReportCat.Active:= True;
187    DataSetReportDetail.Active:= True;
188    DataSetReportSubCat.Active:= True;
189    DataSetSubcategories.Active:= True;
190    DataSetSubcategoriesLU.Active:= True;
191  except
192    Result:= False;
193  end;
194end;
195
196procedure Tdm.DataSetBeforePost(DataSet: TDataSet);
197begin
198  case Application.MessageBox('Save edits to database?', 'Information', MB_YESNOCANCEL) of
199    IDCANCEL:
200      begin
201        DataSet.Cancel;
202        Abort;
203      end;
204    IDYES:
205      ;
206    IDNO:
207      Abort;
208  end;
209end;
210
211end.
212