Автор: Cryogen2003
Дата сообщения: 16.06.2010 08:55
vladman
По поводу шедулера смотрел исключительно все на демках девок, так что в принципе пока там наработок нет. Что мне требуется уже говорил.
По поводу ПивотГрида тут немного изменилась ситуация - чуть-чуть изменилось задание, так стала сейчас стала просто выгрузка в excel для работы, но в принципе проблема такая же осталась, так как в новом отчете (в ПивотГриде) идет все примерно такое же.
Итак стало быть есть какой-то датасет с данными из базы.
[more=Часть селекта с данными]
SELECT DISTINCT agreetype, regionname, department, department_today,
period, card_period, SUM (total) AS total,
SUM (noncontact_begindate) AS noncontact_begindate,
SUM (noncontact_finishdate) AS noncontact_finishdate,
(CASE
WHEN SUM (total) != 0
THEN ROUND ( ( SUM (noncontact_finishdate)
/ SUM (total)
)
* 100,
2
)
ELSE 0
END
) AS quality_noncontact
FROM
(
......
)
GROUP BY agreetype,
regionname,
department,
department_today,
period,
card_period
ORDER BY agreetype NULLS LAST,
regionname NULLS LAST,
department NULLS LAST,
department_today NULLS LAST,
period NULLS LAST,
card_period NULLS LAST[/more]
[more=Код action, где запускается все обработчики:]
Procedure TfrmRegistry.acRepDynamicNonContactExecute(Sender: TObject);
Var
frmStatus: TfrmStatus;
frmRepeatedContracts: TfrmRepeatedContracts;
RepIVRParams: TRepIVRParams;
RepIVRParamsDebt: TRepIVRParamsDebt;
RepPivotGrid: TRepPivotGrid;
Begin
//Динамика по неконтактным клиентам
frmRepeatedContracts := TfrmRepeatedContracts.Create(Self);
Try
frmRepeatedContracts.EnableExportType(True);
If frmRepeatedContracts.ShowModal = mrOk Then
Begin
RepIVRParams.IVR := EmptyStr;
RepIVRParams.StartDate := frmRepeatedContracts.StartDate;
RepIVRParams.FinishDate := frmRepeatedContracts.FinishDate;
RepIVRParams.Region := Number0;
RepIVRParams.Department := Number0;
RepIVRParams.RepType := frmRepeatedContracts.ExportType;
dmdMain.Worked := True;
RepIVRParamsDebt.RepIVRParams := RepIVRParams;
RepIVRParamsDebt.MT := TkbmMemTable.Create(Self);
Try
RepIVRParamsDebt.MT.Performance := mtpfSmall;
frmStatus := TfrmStatus.Create(Application);
Try
frmStatus.RunStatusThread(dmdMain.RepDynamicNonContact,
@RepIVRParamsDebt, ReportDynamicNonContactConst);
Finally
dmdMain.Worked := False;
FreeAndNil(frmStatus);
End;
If (RepIVRParamsDebt.MT.Active) And
(Not RepIVRParamsDebt.MT.IsEmpty) Then
Begin
Case RepIVRParamsDebt.RepIVRParams.RepType Of
rtDebt:
Begin
RepPivotGrid := TRepPivotGrid.Create;
Try
RepPivotGrid.Caption := ReportDynamicNonContactConst;
RepPivotGrid.AddRowFields(AgreeTypeConst, TypeAgreeStrConst,
Number150);
RepPivotGrid.AddRowFields(DepartmentConst,
DepartmentStrConst1, Number150);
RepPivotGrid.AddRowFields(Department_TodayConst,
Department_TodayStrConst, Number150);
RepPivotGrid.AddRowFields(PeriodConst,
PeriodStrConst, Number150);
RepPivotGrid.AddRowFields(Card_PeriodConst,
Card_PeriodStrConst, Number150);
RepPivotGrid.AddDataFields(TotalConst, TotalStrConst,
Number100);
RepPivotGrid.AddDataFields(NonContact_BeginDateConst,
NonContact_BeginDateStrConst, Number130);
RepPivotGrid.AddDataFields(NonContact_FinishDateConst,
NonContact_FinishDateStrConst, Number130);
RepPivotGrid.AddColumnFields(RegionNameConst,
RegionNameStrConst, Number150);
RepPivotGrid.BeginUpdate;
Try
RepPivotGrid.DataSet := RepIVRParamsDebt.MT;
Finally
RepPivotGrid.EndUpdate;
End;
RepPivotGrid.Execute;
Finally
FreeAndNil(RepPivotGrid);
End;
End;
rtExcel:
Begin
frmStatus := TfrmStatus.Create(Application);
Try
frmStatus.RunStatusThread
(dmdMain_Excel.RepDynamicNonContact, @RepIVRParamsDebt,
ReportDynamicNonContactConst);
Finally
FreeAndNil(frmStatus);
End;
End;
End;
End
Else
MyMessageDlgWarnOk(NoDataForReport);
Finally
FreeAndNil(RepIVRParamsDebt.MT);
End;
End;
Finally
dmdMain.Worked := False;
FreeAndNil(frmRepeatedContracts);
End;
End;
[/more]
Код, где выгрузки в excel и получение TkbmMemTable не привожу, так как ничего там интересного нет.
[more=Класс TRepPivotGrid (написан для некоторой оптимизации кода в программе, так как отчеты часто бывают примерно одни и те же:]
Unit
uPivotGrid;
Interface
Uses
fmRepPivotGrid,
cxCustomPivotGrid,
kbmMemTable;
Type
TRepPivotGrid = Class(TObject)
Private
frmRepPivotGrid: TfrmRepPivotGrid;
FCaption: String;
FDataSet: TkbmMemTable;
Procedure SetCaption(Const Value: String);
Procedure SetDataSet(Const Value: TkbmMemTable);
Public
Constructor Create;
Destructor Destroy; Override;
Procedure BeginUpdate;
Procedure EndUpdate;
Procedure AddFields(FieldName: String; Caption: String; Width: Integer;
Area: TcxPivotGridFieldArea);
Procedure AddRowFields(FieldName: String; Caption: String; Width: Integer);
Procedure AddDataFields(FieldName: String; Caption: String; Width: Integer);
Procedure AddColumnFields(FieldName: String; Caption: String;
Width: Integer);
Procedure SetDateGroupField(FieldName: String);
Procedure Execute;
Published
Property Caption: String Read FCaption Write SetCaption;
Property DataSet: TkbmMemTable Read FDataSet Write SetDataSet;
End;
Implementation
{ TRepPivotGrid }
Uses
SysUtils,
uConsts,
uConsts_Str,
DevExpressCEB,
CEBUtils,
StringUtils,
cxDBPivotGrid;
Procedure TRepPivotGrid.AddColumnFields(FieldName, Caption: String;
Width: Integer);
Begin
AddFields(FieldName, Caption, Width, faColumn);
End;
Procedure TRepPivotGrid.AddDataFields(FieldName, Caption: String;
Width: Integer);
Begin
AddFields(FieldName, Caption, Width, faData);
End;
Procedure TRepPivotGrid.AddFields(FieldName, Caption: String; Width: Integer;
Area: TcxPivotGridFieldArea);
Var
Field: TcxDBPivotGridField;
Begin
Field := frmRepPivotGrid.dbgRepPivotGrid.CreateField As TcxDBPivotGridField;
Field.Visible := True;
Field.DataBinding.FieldName := FieldName;
Field.Area := Area;
Field.Caption := Caption;
Field.Name := frmRepPivotGrid.dbgRepPivotGrid.Name + LowMinusChar +
FieldStrConst + IntToStr(frmRepPivotGrid.dbgRepPivotGrid.FieldCount);
End;
Procedure TRepPivotGrid.AddRowFields(FieldName, Caption: String;
Width: Integer);
Begin
AddFields(FieldName, Caption, Width, faRow);
End;
Procedure TRepPivotGrid.BeginUpdate;
Begin
frmRepPivotGrid.dbgRepPivotGrid.BeginUpdate;
End;
Constructor TRepPivotGrid.Create;
Begin
frmRepPivotGrid := TfrmRepPivotGrid.Create(Nil);
End;
Destructor TRepPivotGrid.Destroy;
Begin
FreeAndNil(frmRepPivotGrid);
Inherited;
End;
Procedure TRepPivotGrid.EndUpdate;
Begin
frmRepPivotGrid.dbgRepPivotGrid.EndUpdate;
End;
Procedure TRepPivotGrid.Execute;
Begin
frmRepPivotGrid.N10.Enabled := frmRepPivotGrid.DateGroupField <> Nil;
frmRepPivotGrid.ShowModal;
End;
Procedure TRepPivotGrid.SetCaption(Const Value: String);
Begin
FCaption := Value;
frmRepPivotGrid.Caption := Value;
End;
Procedure TRepPivotGrid.SetDataSet(Const Value: TkbmMemTable);
Begin
FDataSet := Value;
frmRepPivotGrid.dsRepPivotGrid.DataSet := Value;
End;
Procedure TRepPivotGrid.SetDateGroupField(FieldName: String);
Var
I: Integer;
Index: Integer;
Begin
If IsNotClearText(FieldName) Then
Begin
Index := NumberM1;
For I := Number0 To Pred(frmRepPivotGrid.dbgRepPivotGrid.FieldCount) Do
If QuickSameText((frmRepPivotGrid.dbgRepPivotGrid.Fields[I] As
TcxDBPivotGridField).DataBinding.FieldName, FieldName) Then
Begin
Index := I;
Break;
End;
If Index > NumberM1 Then
Begin
frmRepPivotGrid.DateGroupField :=
(frmRepPivotGrid.dbgRepPivotGrid.Fields[Index] As TcxDBPivotGridField);
frmRepPivotGrid.DateGroupField.GroupInterval := giDate;
End;
End;
End;
End.
[/more]
[more=Код pas для frmRepPivotGrid]
Unit
fmRepPivotGrid;
Interface
Uses
Windows,
Messages,
SysUtils,
Variants,
Classes,
Graphics,
Controls,
Forms,
Dialogs,
cxGraphics,
cxControls,
cxLookAndFeels,
cxLookAndFeelPainters,
cxClasses,
cxCustomData,
cxStyles,
cxEdit,
dxSkinsCore,
dxSkinBlack,
dxSkinBlue,
dxSkinCaramel,
dxSkinCoffee,
dxSkinDarkRoom,
dxSkinDarkSide,
dxSkinFoggy,
dxSkinGlassOceans,
dxSkiniMaginary,
dxSkinLondonLiquidSky,
dxSkinMcSkin,
dxSkinMoneyTwins,
dxSkinPumpkin,
dxSkinSeven,
dxSkinSharp,
dxSkinSilver,
dxSkinSpringTime,
dxSkinStardust,
dxSkinSummer2008,
dxSkinValentine,
dxSkinXmas2008Blue,
cxCustomPivotGrid,
cxDBPivotGrid,
cxExportPivotGridLink,
DB,
cxGridCustomPopupMenu,
cxGridPopupMenu,
Menus;
Type
TfrmRepPivotGrid = Class(TForm)
dbgRepPivotGrid: TcxDBPivotGrid;
dsRepPivotGrid: TDataSource;
PopupMenu: TPopupMenu;
N2: TMenuItem;
N3: TMenuItem;
N4: TMenuItem;
N5: TMenuItem;
Excel1: TMenuItem;
HTML1: TMenuItem;
XML1: TMenuItem;
ExcelSaveDialog: TSaveDialog;
HTMLSaveDialog: TSaveDialog;
XMLSaveDialog: TSaveDialog;
N9: TMenuItem;
N10: TMenuItem;
N11: TMenuItem;
N12: TMenuItem;
N13: TMenuItem;
N14: TMenuItem;
N15: TMenuItem;
dbgRepPivotGridField1: TcxDBPivotGridField;
Procedure N2Click(Sender: TObject);
Procedure N3Click(Sender: TObject);
Procedure N4Click(Sender: TObject);
Procedure FormCreate(Sender: TObject);
Procedure Excel1Click(Sender: TObject);
Procedure HTML1Click(Sender: TObject);
Procedure XML1Click(Sender: TObject);
Procedure N11Click(Sender: TObject);
Procedure N12Click(Sender: TObject);
Procedure N13Click(Sender: TObject);
Procedure N14Click(Sender: TObject);
Procedure N15Click(Sender: TObject);
Private
FDateGroupField: TcxDBPivotGridField;
Published
Property DateGroupField: TcxDBPivotGridField Read FDateGroupField
Write FDateGroupField;
End;
Implementation
{$R *.dfm}
Uses
dmMain;
Procedure TfrmRepPivotGrid.Excel1Click(Sender: TObject);
Begin
If ExcelSaveDialog.Execute Then
cxExportPivotGridToExcel(ExcelSaveDialog.FileName, dbgRepPivotGrid);
End;
Procedure TfrmRepPivotGrid.FormCreate(Sender: TObject);
Begin
dmdMain.dxComponentPrinter1Link1.Component := dbgRepPivotGrid;
FDateGroupField := Nil;
End;
Procedure TfrmRepPivotGrid.HTML1Click(Sender: TObject);
Begin
If HTMLSaveDialog.Execute Then
cxExportPivotGridToHTML(HTMLSaveDialog.FileName, dbgRepPivotGrid);
End;
Procedure TfrmRepPivotGrid.N11Click(Sender: TObject);
Begin
If FDateGroupField <> Nil Then
FDateGroupField.GroupInterval := giDate;
End;
Procedure TfrmRepPivotGrid.N12Click(Sender: TObject);
Begin
If FDateGroupField <> Nil Then
FDateGroupField.GroupInterval := giDateMonth;
End;
Procedure TfrmRepPivotGrid.N13Click(Sender: TObject);
Begin
If FDateGroupField <> Nil Then
FDateGroupField.GroupInterval := giDateYear;
End;
Procedure TfrmRepPivotGrid.N14Click(Sender: TObject);
Begin
If FDateGroupField <> Nil Then
FDateGroupField.GroupInterval := giDateDay;
End;
Procedure TfrmRepPivotGrid.N15Click(Sender: TObject);
Begin
If FDateGroupField <> Nil Then
FDateGroupField.GroupInterval := giDateDayOfWeek;
End;
Procedure TfrmRepPivotGrid.N2Click(Sender: TObject);
Begin
dmdMain.dxComponentPrinter1.PageSetup(Nil);
End;
Procedure TfrmRepPivotGrid.N3Click(Sender: TObject);
Begin
dmdMain.dxComponentPrinter1.Preview(True, Nil);
End;
Procedure TfrmRepPivotGrid.N4Click(Sender: TObject);
Begin
dmdMain.dxComponentPrinter1.Print(True, Nil, Nil);
End;
Procedure TfrmRepPivotGrid.XML1Click(Sender: TObject);
Begin
If XMLSaveDialog.Execute Then
cxExportPivotGridToXML(XMLSaveDialog.FileName, dbgRepPivotGrid);
End;
End.
[/more]
[more=Код dfm для frmRepPivotGrid]
object frmRepPivotGrid: TfrmRepPivotGrid
Left = 0
Top = 0
ClientHeight = 304
ClientWidth = 643
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
Position = poScreenCenter
WindowState = wsMaximized
OnCreate = FormCreate
PixelsPerInch = 96
TextHeight = 13
object dbgRepPivotGrid: TcxDBPivotGrid
Left = 0
Top = 0
Width = 643
Height = 304
Align = alClient
DataSource = dsRepPivotGrid
Groups = <>
LookAndFeel.Kind = lfFlat
OptionsBehavior.CellHints = True
OptionsData.AnsiSort = True
OptionsDataField.IsCaptionAssigned = True
OptionsDataField.Caption = #1044#1072#1085#1085#1099#1077
OptionsView.FilterFields = False
PopupMenu = PopupMenu
TabOrder = 0
object dbgRepPivotGridField1: TcxDBPivotGridField
AreaIndex = 0
Visible = True
end
end
object dsRepPivotGrid: TDataSource
Left = 368
Top = 88
end
object PopupMenu: TPopupMenu
Left = 320
Top = 152
object N2: TMenuItem
Caption = #1053#1072#1089#1090#1088#1086#1081#1082#1072' '#1087#1077#1095#1072#1090#1080
OnClick = N2Click
end
object N3: TMenuItem
Caption = #1055#1088#1077#1076#1087#1088#1086#1089#1084#1086#1090#1088
OnClick = N3Click
end
object N4: TMenuItem
Caption = #1055#1077#1095#1072#1090#1100
OnClick = N4Click
end
object N5: TMenuItem
Caption = '-'
end
object Excel1: TMenuItem
Caption = #1069#1082#1089#1087#1086#1088#1090' '#1074' Excel'
OnClick = Excel1Click
end
object HTML1: TMenuItem
Caption = #1069#1082#1089#1087#1086#1088#1090' '#1074' HTML'
OnClick = HTML1Click
end
object XML1: TMenuItem
Caption = #1069#1082#1089#1087#1086#1088#1090' '#1074' XML'
OnClick = XML1Click
end
object N9: TMenuItem
Caption = '-'
end
object N10: TMenuItem
Caption = #1058#1080#1087' '#1075#1088#1091#1087#1087#1080#1088#1086#1074#1082#1080
object N11: TMenuItem
Caption = #1055#1086' '#1076#1072#1090#1077
OnClick = N11Click
end
object N12: TMenuItem
Caption = #1055#1086' '#1084#1077#1089#1103#1094#1072#1084
OnClick = N12Click
end
object N13: TMenuItem
Caption = #1055#1086' '#1075#1086#1076#1072#1084
OnClick = N13Click
end
object N14: TMenuItem
Caption = #1055#1086' '#1076#1085#1102
OnClick = N14Click
end
object N15: TMenuItem
Caption = #1055#1086' '#1076#1085#1102' '#1085#1077#1076#1077#1083#1080
OnClick = N15Click
end
end
end
object ExcelSaveDialog: TSaveDialog
DefaultExt = '.xls'
Filter = #1060#1072#1081#1083#1099' Excel (*.xls)|*.xls|'#1042#1089#1077' '#1092#1072#1081#1083#1099'|*.*'
Title = #1069#1082#1089#1087#1086#1088#1090' '#1074' Excel'
Left = 496
Top = 152
end
object HTMLSaveDialog: TSaveDialog
DefaultExt = '.html'
Filter = #1060#1072#1081#1083#1099' HTML (*.html)|*.html|'#1042#1089#1077' '#1092#1072#1081#1083#1099'|*.*'
Title = #1069#1082#1089#1087#1086#1088#1090' '#1074' HTML'
Left = 496
Top = 184
end
object XMLSaveDialog: TSaveDialog
DefaultExt = '.xml'
Filter = #1060#1072#1081#1083#1099' XML (*.html)|*.xml|'#1042#1089#1077' '#1092#1072#1081#1083#1099'|*.*'
Title = #1069#1082#1089#1087#1086#1088#1090' '#1074' XML'
Left = 496
Top = 216
end
end
[/more]
Вроде приложил все что только можно.
Итак, стало быть нужно отобразить еще поле quality_noncontact в ПивотГриде (в части Data) и что при различных группировках в Row quality_noncontact было верным.
Как бы, как я понимаю, в принципе надо все это пересчитывать внутри Пивотгрида и само значение из датасета как бы и не совсем нужно, так как по идее легко все можно расчитать все на клиенте, но я не понял как это сделать в пивотгриде.