Глава 10. СОЗДАЕМ ОТЧЕТЫ

Полный листинг проекта «Отчет о продажах»

unit sale_report;
interface
uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ExtCtrls, ComCtrls,DBGrids, IBQuery, DB,IBCustomDataSet,StdCtrls,ToolEdit,
  RXDBCtrl,DBChart, Series,xlReport, Buttons;
type
  TSaleReportForm = class(TForm)
    TopPanel: TPanel;
    PageControl1: TPageControl;
    tsDiagram: TTabSheet;
    tsGrid: TTabSheet;
    DBGrid1: TDBGrid;
    qryReport: TIBQuery;
    dsrReport: TDataSource;
    qryReportGROUP_NAME1: TIBStringField;
    qryReportDOC_COUNT1: TIntegerField;
    qryReportQUANTITY1: TLargeintField;
    qryReportSALES1: TIBBCDField;
    qryReportCOST1: TIBBCDField;
    qryReportEARNINGS1: TIBBCDField;
    qryReportRATE1: TFloatField;
    rgGroupMode: TRadioGroup;
    btnQuery: TButton;
    DateEdit1: TDateEdit;
    DateEdit2: TDateEdit;
    Label1: TLabel;
    DBChart1: TDBChart;
    Series1: TPieSeries;
    Label2: TLabel;
    cbSeriesValues: TComboBox;
    xlReport1: TxlReport;
    btnExcel: TBitBtn;
    procedure qryReportCalcFields(DataSet: TDataSet);
    procedure FormCreate(Sender: TObject);
    procedure btnQueryClick(Sender: TObject);
    procedure xlReport1Progress(Report: TxlReport; const Position, Max: Integer);
    procedure btnExcelClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;
var
  SaleReportForm: TSaleReportForm;
implementation
{$R *.DFM}
const
  SQL_SALE_REPORT =
      'select'#13+
      '  O.SHORT_NAME GROUP_NAME,'#13+
      '  COUNT(*) DOC_COUNT,'#13+
      '  SUM(SI.QUANTITY) QUANTITY,'#13+
      '  SUM(SI.AMOUNT_S) SALES,'#13+
      '  SUM(SI.COST_S) COST,'#13+
      '  SUM(SI.AMOUNT_S - SI.COST_S) EARNINGS'#13+
      'from'#13+
      '  SALE_ITEM SI,'#13+
      '  SALE S,'#13+
      '  GOODS G,'#13+
      '  OBJECT_NAMES O'#13+
      'where'#13+
      '  SI.ID = S.ID and'#13+
      '  S.HAS_ENTRY = 1 and'#13+
      '  S.ENTRY_DATE BETWEEN :DATE1 and :DATE2 and'#13+
      '  SI.GOODS = G.ID and'#13+
      '  G.%s = O.OBJECT_ID'#13+  //здесь будет подстановка имени поля
      'group by'#13+
      '  O.SHORT_NAME'#13+
      'order by'#13+
      '  O.SHORT_NAME';
procedure TSaleReportForm.qryReportCalcFields(DataSet: TDataSet);
begin
  with DataSet do
  if FieldByName('COST').AsCurrency <> 0 then
     FieldByName('RATE').AsCurrency :=
        FieldByName('SALES').AsCurrency/FieldByName('COST').AsCurrency;
end;
procedure TSaleReportForm.FormCreate(Sender: TObject);
var
  i: integer;
begin
  DateEdit1.Date := EncodeDate(YearOf(Date), MonthOf(Date), 1);
  DateEdit2.Date := Date;
  for i := 1 to qryReport.Fields.Count - 1 do
    cbSeriesValues.Items.Add(qryReport.Fields[i].DisplayLabel);
  cbSeriesValues.ItemIndex := 0;
end;
procedure TSaleReportForm.btnQueryClick(Sender: TObject);
begin
  qryReport.Close;
  case rgGroupMode.ItemIndex of
  0: qryReport.SQL.Text := Format(SQL_SALE_REPORT, ['GOODS_KIND']);
  1: qryReport.SQL.Text := Format(SQL_SALE_REPORT, ['GOODS_MARK']);
  end;
  {назначаем поле в качестве источника Y значений для диаграммы}
  Series1.YValues.ValueSource :=
     qryReport.Fields[cbSeriesValues.ItemIndex + 1].FieldName;
  qryReport.ParamByName('DATE1').AsDateTime := DateEdit1.Date;
  qryReport.ParamByName('DATE2').AsDateTime := DateEdit2.Date;
  qryReport.Open;
end;
procedure TSaleReportForm.xlReport1Progress(Report: TxlReport; const Position, Max: Integer);
begin
  if Position = 0 then
    StatusBarDisplay(clBlack, Position, 0, Max, '', '', '')
  else
    StatusBarDisplay(clLime, Position, 0, Max, '', 'Экспорт в Excel...', '');
end;
procedure TSaleReportForm.btnExcelClick(Sender: TObject);
begin
  XLReport1.ParamByName('DATE1').Value := DateEdit1.Date;
  XLReport1.ParamByName('DATE2').Value := DateEdit2.Date;
  XLReport1.Report;
end;
end.