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