1unit rptmasterdetaildataset;
2
3
4{$mode objfpc}{$H+}
5{$I demos.inc}
6
7interface
8
9uses
10  Classes,
11  SysUtils,
12  fpreport,
13  fpreportdb,
14  db,
15  sqldb,
16  IBConnection,
17  udapp;
18
19type
20
21  { TMasterDetailDatasetDemo }
22
23  TMasterDetailDatasetDemo = class(TReportDemoApp)
24  private
25    IBConnection1: TIBConnection;
26    SQLTransaction1: TSQLTransaction;
27    ProjectDS: TDataSource;
28    qryProject: TSQLQuery;
29    qryEmployee: TSQLQuery;
30    qryProjBudget: TSQLQuery;
31    ReportMasterData: TFPReportDatasetData;
32    ReportDetailData: TFPReportDatasetData;
33    ReportBudgetData: TFPReportDatasetData;
34  Protected
35    procedure   CreateReportDesign; override;
36    procedure   InitialiseData; override;
37  public
38    constructor Create(AOwner : TComponent); override;
39    destructor  Destroy; override;
40    Class function Description : string; override;
41  end;
42
43
44implementation
45
46uses
47  fpTTF;
48
49const
50  // use alias, it is pre-defined in Firebird installations
51  cDatabase = 'localhost:employee';
52//  cDatabase = 'localhost:/usr/share/doc/firebird2.5-common-doc/examples/empbuild/employee.fdb';
53//  cDatabase = '/opt/firebird/examples/empbuild/employee.fdb';
54
55{ TMasterDetailDatasetDemo }
56
57procedure TMasterDetailDatasetDemo.CreateReportDesign;
58var
59  p: TFPReportPage;
60  TitleBand: TFPReportTitleBand;
61  MasterDataBand: TFPReportDataBand;
62  DetailDataBand: TFPReportDataBand;
63  ProjBudgetBand: TFPReportDataBand;
64  Memo: TFPReportMemo;
65  DataHeader: TFPReportDataHeaderBand;
66  BudgetDataHeader: TFPReportDataHeaderBand;
67begin
68  Inherited;
69  rpt.Author := 'Graeme Geldenhuys';
70  rpt.Title := 'FPReport Demo 11 - Master/Detail using datasets';
71  p := TFPReportPage.Create(rpt);
72  p.Orientation := poPortrait;
73  p.PageSize.PaperName := 'A4';
74  { page margins }
75  p.Margins.Left := 30;
76  p.Margins.Top := 20;
77  p.Margins.Right := 30;
78  p.Margins.Bottom := 20;
79  p.Data := ReportMasterData;
80  p.Font.Name := 'LiberationSans';
81
82  // ======== ReportTitle band ===========
83  TitleBand := TFPReportTitleBand.Create(p);
84  TitleBand.Layout.Height := 20;
85  {$ifdef ColorBands}
86  TitleBand.Frame.Shape := fsRectangle;
87  TitleBand.Frame.BackgroundColor := clReportTitleSummary;
88  {$endif}
89
90  Memo := TFPReportMemo.Create(TitleBand);
91  Memo.Layout.Left := 0;
92  Memo.Layout.Top := 0;
93  Memo.Layout.Width := TitleBand.Layout.Width;
94  Memo.Layout.Height := 15;
95  Memo.UseParentFont := False;
96  Memo.Font.Name := 'LiberationSans-Bold';
97  Memo.Font.Size := 18;
98  Memo.Text := 'FPReport Demo 11' + LineEnding + 'Master/Detail using datasets';
99  Memo.TextAlignment.Vertical := tlCenter;
100  Memo.TextAlignment.Horizontal := taCentered;
101
102  // ======== MasterData band ===========
103  MasterDataBand := TFPReportDataBand.Create(p);
104  MasterDataBand.Layout.Height := 8;
105  {$ifdef ColorBands}
106  MasterDataBand.Frame.Shape := fsRectangle;
107  MasterDataBand.Frame.BackgroundColor := clDataBand;
108  {$endif}
109  MasterDataBand.Data:=ReportMasterData;
110
111  Memo := TFPReportMemo.Create(MasterDataBand);
112  Memo.Layout.Left := 5;
113  Memo.Layout.Top := 2;
114  Memo.Layout.Width := 50;
115  Memo.Layout.Height := 5;
116  Memo.Text := '[reportmasterdata.proj_id] - [reportmasterdata.proj_name]';
117  Memo.TextAlignment.Vertical := tlCenter;
118  Memo.Frame.Shape := fsRectangle;
119  Memo.Frame.BackgroundColor := clLtGray;
120
121  // ======== DataHeader band for DetailData ===========
122  DataHeader := TFPReportDataHeaderBand.Create(p);
123  DataHeader.Layout.Height := 8;
124  {$ifdef ColorBands}
125  DataHeader.Frame.Shape := fsRectangle;
126  DataHeader.Frame.BackgroundColor := clDataHeaderFooter;
127  {$endif}
128  DataHeader.Data:=ReportDetailData;
129
130  Memo := TFPReportMemo.Create(DataHeader);
131  Memo.Layout.Left := 15;
132  Memo.Layout.Top := 3;
133  Memo.Layout.Width := 15;
134  Memo.Layout.Height := 5;
135  Memo.Text := 'Emp No.';
136  Memo.TextAlignment.Vertical := tlCenter;
137  Memo.Options := [moDisableWordWrap];
138  Memo.Frame.Shape := fsRectangle;
139  Memo.Frame.Color := clBlack;
140  Memo.Frame.BackgroundColor := clLtGray;
141
142  Memo := TFPReportMemo.Create(DataHeader);
143  Memo.Layout.Left := 30;
144  Memo.Layout.Top := 3;
145  Memo.Layout.Width := 50;
146  Memo.Layout.Height := 5;
147  Memo.Text := 'Employee Name';
148  Memo.TextAlignment.Vertical := tlCenter;
149  Memo.TextAlignment.Horizontal := taCentered;
150  Memo.Frame.Shape := fsRectangle;
151  Memo.Frame.Color := clBlack;
152  Memo.Frame.BackgroundColor := clLtGray;
153
154  // ======== DetailData band ===========
155  DetailDataBand := TFPReportDataBand.Create(p);
156  DetailDataBand.Layout.Height := 5;
157  DetailDataBand.Data := ReportDetailData;
158  { associate this band with the MasterData band }
159  DetailDataBand.MasterBand := MasterDataBand;
160  { associate DataHeader band }
161  DetailDataBand.DisplayPosition := 0;
162  {$ifdef ColorBands}
163  DetailDataBand.Frame.Shape := fsRectangle;
164  DetailDataBand.Frame.BackgroundColor := clChildBand;
165  {$endif}
166
167  Memo := TFPReportMemo.Create(DetailDataBand);
168  Memo.Layout.Left := 15;
169  Memo.Layout.Top := 0;
170  Memo.Layout.Width := 15;
171  Memo.Layout.Height := 5;
172  Memo.Text := '[reportdetaildata.emp_no]';
173  Memo.TextAlignment.Vertical := tlCenter;
174  Memo.Frame.Shape := fsRectangle;
175  Memo.Frame.Color := clBlack;
176
177  Memo := TFPReportMemo.Create(DetailDataBand);
178  Memo.Layout.Left := 30;
179  Memo.Layout.Top := 0;
180  Memo.Layout.Width := 50;
181  Memo.Layout.Height := 5;
182  Memo.Text := '[reportdetaildata.first_name] [reportdetaildata.last_name]';
183  Memo.TextAlignment.Vertical := tlCenter;
184  Memo.Frame.Shape := fsRectangle;
185  Memo.Frame.Color := clBlack;
186
187
188  // ======== DataHeader band for DetailData ===========
189  BudgetDataHeader := TFPReportDataHeaderBand.Create(p);
190  BudgetDataHeader.Layout.Height := 8;
191  {$ifdef ColorBands}
192  BudgetDataHeader.Frame.Shape := fsRectangle;
193  BudgetDataHeader.Frame.BackgroundColor := clDataHeaderFooter;
194  {$endif}
195  BudgetDataHeader.Data:=ReportBudgetData;
196
197  Memo := TFPReportMemo.Create(BudgetDataHeader);
198  Memo.Layout.Left := 15;
199  Memo.Layout.Top := 3;
200  Memo.Layout.Width := 15;
201  Memo.Layout.Height := 5;
202  Memo.Text := 'Year';
203  Memo.TextAlignment.Vertical := tlCenter;
204  Memo.TextAlignment.Horizontal := taCentered;
205  Memo.Frame.Shape := fsRectangle;
206  Memo.Frame.Color := clBlack;
207  Memo.Frame.BackgroundColor := clLtGray;
208
209  Memo := TFPReportMemo.Create(BudgetDataHeader);
210  Memo.Layout.Left := 30;
211  Memo.Layout.Top := 3;
212  Memo.Layout.Width := 50;
213  Memo.Layout.Height := 5;
214  Memo.Text := 'Budget';
215  Memo.TextAlignment.Vertical := tlCenter;
216  Memo.TextAlignment.Horizontal := taCentered;
217  Memo.Frame.Shape := fsRectangle;
218  Memo.Frame.Color := clBlack;
219  Memo.Frame.BackgroundColor := clLtGray;
220
221
222  // ======== Project Budget band ===========
223  ProjBudgetBand := TFPReportDataBand.Create(p);
224  ProjBudgetBand.Layout.Height := 5;
225  ProjBudgetBand.Data := ReportBudgetData;
226  { associate this band with the MasterData band }
227  ProjBudgetBand.MasterBand := MasterDataBand;
228  ProjBudgetBand.DisplayPosition := 1;
229  {$ifdef ColorBands}
230  ProjBudgetBand.Frame.Shape := fsRectangle;
231  ProjBudgetBand.Frame.BackgroundColor := clChildBand;
232  {$endif}
233
234  Memo := TFPReportMemo.Create(ProjBudgetBand);
235  Memo.Layout.Left := 15;
236  Memo.Layout.Top := 0;
237  Memo.Layout.Width := 15;
238  Memo.Layout.Height := 5;
239  Memo.Text := '[ReportBudgetData.FISCAL_YEAR]';
240  Memo.TextAlignment.Vertical := tlCenter;
241  Memo.TextAlignment.Horizontal := taCentered;
242  Memo.Frame.Shape := fsRectangle;
243  Memo.Frame.Color := clBlack;
244
245  Memo := TFPReportMemo.Create(ProjBudgetBand);
246  Memo.Layout.Left := 30;
247  Memo.Layout.Top := 0;
248  Memo.Layout.Width := 50;
249  Memo.Layout.Height := 5;
250  Memo.Text := '[formatfloat(''#,##0'', ReportBudgetData.PROJECTED_BUDGET)]';
251  Memo.TextAlignment.Vertical := tlCenter;
252  Memo.TextAlignment.Horizontal := taRightJustified;
253  Memo.Frame.Shape := fsRectangle;
254  Memo.Frame.Color := clBlack;
255end;
256
257procedure TMasterDetailDatasetDemo.InitialiseData;
258begin
259  SQLTransaction1 := TSQLTransaction.Create(Self);
260
261  IBConnection1 := TIBConnection.Create(Self);
262  with IBConnection1 do
263  begin
264    LoginPrompt := False;
265    DatabaseName := cDatabase;
266    KeepConnection := False;
267    Password := 'masterkey';
268    Transaction := SQLTransaction1;
269    UserName := 'sysdba';
270    Connected := True;
271  end;
272
273  qryProject := TSQLQuery.Create(Self);
274  with qryProject do
275  begin
276    Database := IBConnection1;
277    Transaction := SQLTransaction1;
278    ReadOnly := True;
279    SQL.Text := 'select * from PROJECT order by PROJ_NAME';
280    Active := True
281  end;
282
283  ProjectDS := TDataSource.Create(Self);
284  ProjectDS.DataSet := qryProject;
285
286  qryEmployee := TSQLQuery.Create(Self);
287  with qryEmployee do
288  begin
289    Database := IBConnection1;
290    Transaction := SQLTransaction1;
291    ReadOnly := True;
292    SQL.Text :=
293          'SELECT ' +
294          '  e.* ' +
295          'FROM EMPLOYEE e ' +
296          '  INNER JOIN EMPLOYEE_PROJECT ep on e.EMP_NO = ep.EMP_NO ' +
297          'where ep.proj_id = :proj_id ' +
298          'order by e.EMP_NO';
299    DataSource := ProjectDS;
300    Active := True
301  end;
302
303  qryProjBudget := TSQLQuery.Create(Self);
304  with qryProjBudget do
305  begin
306    Database := IBConnection1;
307    Transaction := SQLTransaction1;
308    ReadOnly := True;
309    SQL.Text :=
310          'SELECT ' +
311          '  b.FISCAL_YEAR, b.PROJECTED_BUDGET ' +
312          'FROM PROJ_DEPT_BUDGET b ' +
313          'where b.proj_id = :proj_id ' +
314          'order by b.FISCAL_YEAR';
315    DataSource := ProjectDS;
316    Active := True
317  end;
318
319  ReportMasterData.DataSet:= qryProject;
320  ReportDetailData.DataSet:= qryEmployee;
321  ReportBudgetData.DataSet:= qryProjBudget;
322  rpt.ReportData.AddReportData(ReportMasterData);
323  rpt.ReportData.AddReportData(ReportDetailData);
324  rpt.ReportData.AddReportData(ReportBudgetData);
325end;
326
327constructor TMasterDetailDatasetDemo.Create(AOwner: TComponent);
328begin
329  Inherited;
330  ReportMasterData := TFPReportDatasetData.Create(Self);
331  ReportMasterData.Name := 'ReportMasterData';
332  ReportDetailData := TFPReportDatasetData.Create(Self);
333  ReportDetailData.Name := 'ReportDetailData';
334  ReportBudgetData := TFPReportDatasetData.Create(Self);
335  ReportBudgetData.Name := 'ReportBudgetData';
336end;
337
338destructor TMasterDetailDatasetDemo.Destroy;
339begin
340  IBConnection1.Close();
341  FreeAndNil(qryEmployee);
342  FreeAndNil(qryProject);
343  FreeAndNil(ReportMasterData);
344  FreeAndNil(ReportDetailData);
345  FreeAndNil(ReportBudgetData);
346  FreeAndNil(ProjectDS);
347  FreeAndNil(SQLTransaction1);
348  FreeAndNil(IBConnection1);
349  inherited Destroy;
350end;
351
352class function TMasterDetailDatasetDemo.Description: string;
353begin
354  Result:='Demo for support of master-detail relations with datasets';
355end;
356
357
358
359end.
360
361