mirror of
https://gitlab.com/freepascal.org/fpc/source.git
synced 2025-08-11 15:47:51 +02:00
361 lines
10 KiB
ObjectPascal
361 lines
10 KiB
ObjectPascal
unit rptmasterdetaildataset;
|
|
|
|
|
|
{$mode objfpc}{$H+}
|
|
{$I demos.inc}
|
|
|
|
interface
|
|
|
|
uses
|
|
Classes,
|
|
SysUtils,
|
|
fpreport,
|
|
fpreportdb,
|
|
db,
|
|
sqldb,
|
|
IBConnection,
|
|
udapp;
|
|
|
|
type
|
|
|
|
{ TMasterDetailDatasetDemo }
|
|
|
|
TMasterDetailDatasetDemo = class(TReportDemoApp)
|
|
private
|
|
IBConnection1: TIBConnection;
|
|
SQLTransaction1: TSQLTransaction;
|
|
ProjectDS: TDataSource;
|
|
qryProject: TSQLQuery;
|
|
qryEmployee: TSQLQuery;
|
|
qryProjBudget: TSQLQuery;
|
|
ReportMasterData: TFPReportDatasetData;
|
|
ReportDetailData: TFPReportDatasetData;
|
|
ReportBudgetData: TFPReportDatasetData;
|
|
Protected
|
|
procedure CreateReportDesign; override;
|
|
procedure InitialiseData; override;
|
|
public
|
|
constructor Create(AOwner : TComponent); override;
|
|
destructor Destroy; override;
|
|
Class function Description : string; override;
|
|
end;
|
|
|
|
|
|
implementation
|
|
|
|
uses
|
|
fpTTF;
|
|
|
|
const
|
|
// use alias, it is pre-defined in Firebird installations
|
|
cDatabase = 'localhost:employee';
|
|
// cDatabase = 'localhost:/usr/share/doc/firebird2.5-common-doc/examples/empbuild/employee.fdb';
|
|
// cDatabase = '/opt/firebird/examples/empbuild/employee.fdb';
|
|
|
|
{ TMasterDetailDatasetDemo }
|
|
|
|
procedure TMasterDetailDatasetDemo.CreateReportDesign;
|
|
var
|
|
p: TFPReportPage;
|
|
TitleBand: TFPReportTitleBand;
|
|
MasterDataBand: TFPReportDataBand;
|
|
DetailDataBand: TFPReportDataBand;
|
|
ProjBudgetBand: TFPReportDataBand;
|
|
Memo: TFPReportMemo;
|
|
DataHeader: TFPReportDataHeaderBand;
|
|
BudgetDataHeader: TFPReportDataHeaderBand;
|
|
begin
|
|
Inherited;
|
|
rpt.Author := 'Graeme Geldenhuys';
|
|
rpt.Title := 'FPReport Demo 11 - Master/Detail using datasets';
|
|
p := TFPReportPage.Create(rpt);
|
|
p.Orientation := poPortrait;
|
|
p.PageSize.PaperName := 'A4';
|
|
{ page margins }
|
|
p.Margins.Left := 30;
|
|
p.Margins.Top := 20;
|
|
p.Margins.Right := 30;
|
|
p.Margins.Bottom := 20;
|
|
p.Data := ReportMasterData;
|
|
p.Font.Name := 'LiberationSans';
|
|
|
|
// ======== ReportTitle band ===========
|
|
TitleBand := TFPReportTitleBand.Create(p);
|
|
TitleBand.Layout.Height := 20;
|
|
{$ifdef ColorBands}
|
|
TitleBand.Frame.Shape := fsRectangle;
|
|
TitleBand.Frame.BackgroundColor := clReportTitleSummary;
|
|
{$endif}
|
|
|
|
Memo := TFPReportMemo.Create(TitleBand);
|
|
Memo.Layout.Left := 0;
|
|
Memo.Layout.Top := 0;
|
|
Memo.Layout.Width := TitleBand.Layout.Width;
|
|
Memo.Layout.Height := 15;
|
|
Memo.UseParentFont := False;
|
|
Memo.Font.Name := 'LiberationSans-Bold';
|
|
Memo.Font.Size := 18;
|
|
Memo.Text := 'FPReport Demo 11' + LineEnding + 'Master/Detail using datasets';
|
|
Memo.TextAlignment.Vertical := tlCenter;
|
|
Memo.TextAlignment.Horizontal := taCentered;
|
|
|
|
// ======== MasterData band ===========
|
|
MasterDataBand := TFPReportDataBand.Create(p);
|
|
MasterDataBand.Layout.Height := 8;
|
|
{$ifdef ColorBands}
|
|
MasterDataBand.Frame.Shape := fsRectangle;
|
|
MasterDataBand.Frame.BackgroundColor := clDataBand;
|
|
{$endif}
|
|
MasterDataBand.Data:=ReportMasterData;
|
|
|
|
Memo := TFPReportMemo.Create(MasterDataBand);
|
|
Memo.Layout.Left := 5;
|
|
Memo.Layout.Top := 2;
|
|
Memo.Layout.Width := 50;
|
|
Memo.Layout.Height := 5;
|
|
Memo.Text := '[reportmasterdata.proj_id] - [reportmasterdata.proj_name]';
|
|
Memo.TextAlignment.Vertical := tlCenter;
|
|
Memo.Frame.Shape := fsRectangle;
|
|
Memo.Frame.BackgroundColor := clLtGray;
|
|
|
|
// ======== DataHeader band for DetailData ===========
|
|
DataHeader := TFPReportDataHeaderBand.Create(p);
|
|
DataHeader.Layout.Height := 8;
|
|
{$ifdef ColorBands}
|
|
DataHeader.Frame.Shape := fsRectangle;
|
|
DataHeader.Frame.BackgroundColor := clDataHeaderFooter;
|
|
{$endif}
|
|
DataHeader.Data:=ReportDetailData;
|
|
|
|
Memo := TFPReportMemo.Create(DataHeader);
|
|
Memo.Layout.Left := 15;
|
|
Memo.Layout.Top := 3;
|
|
Memo.Layout.Width := 15;
|
|
Memo.Layout.Height := 5;
|
|
Memo.Text := 'Emp No.';
|
|
Memo.TextAlignment.Vertical := tlCenter;
|
|
Memo.Options := [moDisableWordWrap];
|
|
Memo.Frame.Shape := fsRectangle;
|
|
Memo.Frame.Color := clBlack;
|
|
Memo.Frame.BackgroundColor := clLtGray;
|
|
|
|
Memo := TFPReportMemo.Create(DataHeader);
|
|
Memo.Layout.Left := 30;
|
|
Memo.Layout.Top := 3;
|
|
Memo.Layout.Width := 50;
|
|
Memo.Layout.Height := 5;
|
|
Memo.Text := 'Employee Name';
|
|
Memo.TextAlignment.Vertical := tlCenter;
|
|
Memo.TextAlignment.Horizontal := taCentered;
|
|
Memo.Frame.Shape := fsRectangle;
|
|
Memo.Frame.Color := clBlack;
|
|
Memo.Frame.BackgroundColor := clLtGray;
|
|
|
|
// ======== DetailData band ===========
|
|
DetailDataBand := TFPReportDataBand.Create(p);
|
|
DetailDataBand.Layout.Height := 5;
|
|
DetailDataBand.Data := ReportDetailData;
|
|
{ associate this band with the MasterData band }
|
|
DetailDataBand.MasterBand := MasterDataBand;
|
|
{ associate DataHeader band }
|
|
DetailDataBand.DisplayPosition := 0;
|
|
{$ifdef ColorBands}
|
|
DetailDataBand.Frame.Shape := fsRectangle;
|
|
DetailDataBand.Frame.BackgroundColor := clChildBand;
|
|
{$endif}
|
|
|
|
Memo := TFPReportMemo.Create(DetailDataBand);
|
|
Memo.Layout.Left := 15;
|
|
Memo.Layout.Top := 0;
|
|
Memo.Layout.Width := 15;
|
|
Memo.Layout.Height := 5;
|
|
Memo.Text := '[reportdetaildata.emp_no]';
|
|
Memo.TextAlignment.Vertical := tlCenter;
|
|
Memo.Frame.Shape := fsRectangle;
|
|
Memo.Frame.Color := clBlack;
|
|
|
|
Memo := TFPReportMemo.Create(DetailDataBand);
|
|
Memo.Layout.Left := 30;
|
|
Memo.Layout.Top := 0;
|
|
Memo.Layout.Width := 50;
|
|
Memo.Layout.Height := 5;
|
|
Memo.Text := '[reportdetaildata.first_name] [reportdetaildata.last_name]';
|
|
Memo.TextAlignment.Vertical := tlCenter;
|
|
Memo.Frame.Shape := fsRectangle;
|
|
Memo.Frame.Color := clBlack;
|
|
|
|
|
|
// ======== DataHeader band for DetailData ===========
|
|
BudgetDataHeader := TFPReportDataHeaderBand.Create(p);
|
|
BudgetDataHeader.Layout.Height := 8;
|
|
{$ifdef ColorBands}
|
|
BudgetDataHeader.Frame.Shape := fsRectangle;
|
|
BudgetDataHeader.Frame.BackgroundColor := clDataHeaderFooter;
|
|
{$endif}
|
|
BudgetDataHeader.Data:=ReportBudgetData;
|
|
|
|
Memo := TFPReportMemo.Create(BudgetDataHeader);
|
|
Memo.Layout.Left := 15;
|
|
Memo.Layout.Top := 3;
|
|
Memo.Layout.Width := 15;
|
|
Memo.Layout.Height := 5;
|
|
Memo.Text := 'Year';
|
|
Memo.TextAlignment.Vertical := tlCenter;
|
|
Memo.TextAlignment.Horizontal := taCentered;
|
|
Memo.Frame.Shape := fsRectangle;
|
|
Memo.Frame.Color := clBlack;
|
|
Memo.Frame.BackgroundColor := clLtGray;
|
|
|
|
Memo := TFPReportMemo.Create(BudgetDataHeader);
|
|
Memo.Layout.Left := 30;
|
|
Memo.Layout.Top := 3;
|
|
Memo.Layout.Width := 50;
|
|
Memo.Layout.Height := 5;
|
|
Memo.Text := 'Budget';
|
|
Memo.TextAlignment.Vertical := tlCenter;
|
|
Memo.TextAlignment.Horizontal := taCentered;
|
|
Memo.Frame.Shape := fsRectangle;
|
|
Memo.Frame.Color := clBlack;
|
|
Memo.Frame.BackgroundColor := clLtGray;
|
|
|
|
|
|
// ======== Project Budget band ===========
|
|
ProjBudgetBand := TFPReportDataBand.Create(p);
|
|
ProjBudgetBand.Layout.Height := 5;
|
|
ProjBudgetBand.Data := ReportBudgetData;
|
|
{ associate this band with the MasterData band }
|
|
ProjBudgetBand.MasterBand := MasterDataBand;
|
|
ProjBudgetBand.DisplayPosition := 1;
|
|
{$ifdef ColorBands}
|
|
ProjBudgetBand.Frame.Shape := fsRectangle;
|
|
ProjBudgetBand.Frame.BackgroundColor := clChildBand;
|
|
{$endif}
|
|
|
|
Memo := TFPReportMemo.Create(ProjBudgetBand);
|
|
Memo.Layout.Left := 15;
|
|
Memo.Layout.Top := 0;
|
|
Memo.Layout.Width := 15;
|
|
Memo.Layout.Height := 5;
|
|
Memo.Text := '[ReportBudgetData.FISCAL_YEAR]';
|
|
Memo.TextAlignment.Vertical := tlCenter;
|
|
Memo.TextAlignment.Horizontal := taCentered;
|
|
Memo.Frame.Shape := fsRectangle;
|
|
Memo.Frame.Color := clBlack;
|
|
|
|
Memo := TFPReportMemo.Create(ProjBudgetBand);
|
|
Memo.Layout.Left := 30;
|
|
Memo.Layout.Top := 0;
|
|
Memo.Layout.Width := 50;
|
|
Memo.Layout.Height := 5;
|
|
Memo.Text := '[formatfloat(''#,##0'', ReportBudgetData.PROJECTED_BUDGET)]';
|
|
Memo.TextAlignment.Vertical := tlCenter;
|
|
Memo.TextAlignment.Horizontal := taRightJustified;
|
|
Memo.Frame.Shape := fsRectangle;
|
|
Memo.Frame.Color := clBlack;
|
|
end;
|
|
|
|
procedure TMasterDetailDatasetDemo.InitialiseData;
|
|
begin
|
|
SQLTransaction1 := TSQLTransaction.Create(Self);
|
|
|
|
IBConnection1 := TIBConnection.Create(Self);
|
|
with IBConnection1 do
|
|
begin
|
|
LoginPrompt := False;
|
|
DatabaseName := cDatabase;
|
|
KeepConnection := False;
|
|
Password := 'masterkey';
|
|
Transaction := SQLTransaction1;
|
|
UserName := 'sysdba';
|
|
Connected := True;
|
|
end;
|
|
|
|
qryProject := TSQLQuery.Create(Self);
|
|
with qryProject do
|
|
begin
|
|
Database := IBConnection1;
|
|
Transaction := SQLTransaction1;
|
|
ReadOnly := True;
|
|
SQL.Text := 'select * from PROJECT order by PROJ_NAME';
|
|
Active := True
|
|
end;
|
|
|
|
ProjectDS := TDataSource.Create(Self);
|
|
ProjectDS.DataSet := qryProject;
|
|
|
|
qryEmployee := TSQLQuery.Create(Self);
|
|
with qryEmployee do
|
|
begin
|
|
Database := IBConnection1;
|
|
Transaction := SQLTransaction1;
|
|
ReadOnly := True;
|
|
SQL.Text :=
|
|
'SELECT ' +
|
|
' e.* ' +
|
|
'FROM EMPLOYEE e ' +
|
|
' INNER JOIN EMPLOYEE_PROJECT ep on e.EMP_NO = ep.EMP_NO ' +
|
|
'where ep.proj_id = :proj_id ' +
|
|
'order by e.EMP_NO';
|
|
DataSource := ProjectDS;
|
|
Active := True
|
|
end;
|
|
|
|
qryProjBudget := TSQLQuery.Create(Self);
|
|
with qryProjBudget do
|
|
begin
|
|
Database := IBConnection1;
|
|
Transaction := SQLTransaction1;
|
|
ReadOnly := True;
|
|
SQL.Text :=
|
|
'SELECT ' +
|
|
' b.FISCAL_YEAR, b.PROJECTED_BUDGET ' +
|
|
'FROM PROJ_DEPT_BUDGET b ' +
|
|
'where b.proj_id = :proj_id ' +
|
|
'order by b.FISCAL_YEAR';
|
|
DataSource := ProjectDS;
|
|
Active := True
|
|
end;
|
|
|
|
ReportMasterData.DataSet:= qryProject;
|
|
ReportDetailData.DataSet:= qryEmployee;
|
|
ReportBudgetData.DataSet:= qryProjBudget;
|
|
rpt.ReportData.AddReportData(ReportMasterData);
|
|
rpt.ReportData.AddReportData(ReportDetailData);
|
|
rpt.ReportData.AddReportData(ReportBudgetData);
|
|
end;
|
|
|
|
constructor TMasterDetailDatasetDemo.Create(AOwner: TComponent);
|
|
begin
|
|
Inherited;
|
|
ReportMasterData := TFPReportDatasetData.Create(Self);
|
|
ReportMasterData.Name := 'ReportMasterData';
|
|
ReportDetailData := TFPReportDatasetData.Create(Self);
|
|
ReportDetailData.Name := 'ReportDetailData';
|
|
ReportBudgetData := TFPReportDatasetData.Create(Self);
|
|
ReportBudgetData.Name := 'ReportBudgetData';
|
|
end;
|
|
|
|
destructor TMasterDetailDatasetDemo.Destroy;
|
|
begin
|
|
IBConnection1.Close();
|
|
FreeAndNil(qryEmployee);
|
|
FreeAndNil(qryProject);
|
|
FreeAndNil(ReportMasterData);
|
|
FreeAndNil(ReportDetailData);
|
|
FreeAndNil(ReportBudgetData);
|
|
FreeAndNil(ProjectDS);
|
|
FreeAndNil(SQLTransaction1);
|
|
FreeAndNil(IBConnection1);
|
|
inherited Destroy;
|
|
end;
|
|
|
|
class function TMasterDetailDatasetDemo.Description: string;
|
|
begin
|
|
Result:='Demo for support of master-detail relations with datasets';
|
|
end;
|
|
|
|
|
|
|
|
end.
|
|
|