report.pas

家庭收支管理

unit Report;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, Series, GridsEh, DBGridEh, TeEngine, ExtCtrls,
  TeeProcs, Chart, DBCtrlsEh, StdCtrls, Mask;

type
  TReportForm = class(TForm)
    GroupBox1: TGroupBox;
    Label2: TLabel;
    Label3: TLabel;
    Button1: TButton;
    Button: TButton;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    Chart3: TChart;
    Chart4: TChart;
    Series3: TBarSeries;
    Series4: TBarSeries;
    Button2: TButton;
    Chart1: TChart;
    Series1: TPieSeries;
    Chart2: TChart;
    Series2: TPieSeries;
    StatusBar1: TStatusBar;
    Shape1: TShape;
    cbx_account: TDBCheckBoxEh;
    cbx_code: TDBCheckBoxEh;
    cmb_account: TDBComboBoxEh;
    cmb_code: TDBComboBoxEh;
    First_date: TDBDateTimeEditEh;
    Last_date: TDBDateTimeEditEh;
    DBGridEh: TDBGridEh;
    procedure ButtonClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Chart1MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Chart2MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure StatusBar1DrawPanel(StatusBar: TStatusBar;
      Panel: TStatusPanel; const Rect: TRect);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure cbx_accountClick(Sender: TObject);
    procedure cbx_codeClick(Sender: TObject);
    procedure WMNCHitTest(var Msg:TMessage);message WM_NCHITTEST;
  private
    { Private declarations }
    ShapeRect: TRect;
  public
    { Public declarations }
    procedure StatusBarValue(Series: TPieSeries; i:Longint);
  end;

var
  ReportForm: TReportForm;

implementation

{$R *.dfm}

uses DataModule;

procedure TReportForm.WMNCHitTest(var Msg:TMessage);
begin
  inherited;
  Msg.Result:=HTCLIENT;
end;

procedure TReportForm.StatusBarValue(Series: TPieSeries; i:Longint);
var tmp:string;
begin
  Series.Marks.Style:=smsLabel;
  tmp:='统计项目名称：'+Series.ValueMarkText[i];
  Series.Marks.Style:=smsValue;
  tmp:=tmp+'   金额：'+Series.ValueMarkText[i];
  tmp:=tmp+'   比例：'+Series.MarkPercent(i,False);
  StatusBar1.Panels[2].Text:=tmp;
end;

procedure TReportForm.ButtonClick(Sender: TObject);
begin
  CLOSE
end;

procedure TReportForm.FormCreate(Sender: TObject);
begin
  cmb_account.Items.Clear;
  cmb_code.Items.Clear;
  DataForm.ABSQuery1.SQL.Clear;
  DataForm.ABSQuery1.SQL.Add('select accounttype,account,accountName from zhanghu order by accounttype,account');
  DataForm.ABSQuery1.Open;
  while not DataForm.ABSQuery1.Eof do
  begin
    cmb_account.Items.Add('('+DataForm.ABSQuery1.FieldByName('account').AsString+')'+DataForm.ABSQuery1.FieldByName('AccountName').AsString);
    DataForm.ABSQuery1.Next;
  end;

  DataForm.ABSQuery1.SQL.Clear;
  DataForm.ABSQuery1.SQL.Add('select * from szType order by szCode');
  DataForm.ABSQuery1.Open;
  while not DataForm.ABSQuery1.Eof do
  begin
    cmb_code.Items.Add('('+DataForm.ABSQuery1.FieldByName('szcode').AsString+')'+DataForm.ABSQuery1.FieldByName('szName').AsString);
    DataForm.ABSQuery1.Next;
  end;
  DataForm.ABSQuery1.Close;
  First_date.Value:=date;
  Last_date.Value:=date;
  DataForm.DataSource.DataSet:=DataForm.ReportQuery;
  DBGridEh.DataSource:=DataForm.DataSource;
end;

procedure TReportForm.Button1Click(Sender: TObject);
var
  shsum,zhsum:real;
begin
  DBGridEh.DataSource.DataSet.Close;

  DataForm.ABSQuery1.SQL.Clear;
  DataForm.ABSQuery1.SQL.Add('Create Table MEMORY tongji (szCode String(6),szName String(20),Income FLOAT,Payout FLOAT,I_Percent FLOAT,P_Percent FLOAT,chae FLOAT)');
  DataForm.ABSQuery1.ExecSQL;

  DataForm.ABSQuery1.SQL.Clear;
  if cbx_account.Checked or cbx_code.Checked then
    DataForm.ABSQuery1.SQL.Add('select szcode,sum(income) as income,sum(payout) as payout into MEMORY lsb from szData where szdate>=:cs1 and szdate<=:cs2')
  else
    DataForm.ABSQuery1.SQL.Add('select szcode,sum(income) as income,sum(payout) as payout into MEMORY lsb from szData where szdate>=:cs1 and szdate<=:cs2 and szcode<>''000000''');
  DataForm.ABSQuery1.ParamByName('cs1').AsString:=First_Date.Text;
  DataForm.ABSQuery1.ParamByName('cs2').AsString:=Last_Date.Text;

  if cbx_account.Checked then
  begin
    DataForm.ABSQuery1.SQL.Add(' and account=:cs3');
    DataForm.ABSQuery1.ParamByName('cs3').AsString:=copy(cmb_account.Text,2,10);
  end;
  if cbx_code.Checked then
  begin
    DataForm.ABSQuery1.SQL.Add(' and szcode=:cs4');
    DataForm.ABSQuery1.ParamByName('cs4').AsString:=copy(cmb_code.Text,2,6);
  end;
  DataForm.ABSQuery1.SQL.Add(' group by szcode');
  DataForm.ABSQuery1.ExecSQL;

  DataForm.ABSQuery1.SQL.Clear;
  DataForm.ABSQuery1.SQL.Add('insert into MEMORY tongji (select a.szcode as szcode,b.szname as szname,a.income as income,a.payout as payout from MEMORY lsb a,szType b where a.szcode=b.szcode)');
  DataForm.ABSQuery1.ExecSQL;

  DataForm.ABSQuery1.SQL.Clear;
  DataForm.ABSQuery1.SQL.Add('update MEMORY tongji set chae=income-payout');
  DataForm.ABSQuery1.ExecSQL;

  DataForm.ABSQuery1.SQL.Clear;
  DataForm.ABSQuery1.SQL.Add('select sum(income),sum(payout) from MEMORY tongji');
  DataForm.ABSQuery1.Open;
  shsum:=DataForm.ABSQuery1.Fields[0].AsFloat;
  zhsum:=DataForm.ABSQuery1.Fields[1].AsFloat;
  DataForm.ABSQuery1.Close;

  if shsum<>0 then
  begin
    DataForm.ABSQuery1.SQL.Clear;
    DataForm.ABSQuery1.SQL.Add('update MEMORY tongji set I_Percent=income*100/:cs');
    DataForm.ABSQuery1.ParamByName('cs').AsFloat:=shsum;
    DataForm.ABSQuery1.ExecSQL;
  end;

  if zhsum<>0 then
  begin
    DataForm.ABSQuery1.SQL.Clear;
    DataForm.ABSQuery1.SQL.Add('update MEMORY tongji set P_Percent=payout*100/:cs');
    DataForm.ABSQuery1.ParamByName('cs').AsFloat:=zhsum;
    DataForm.ABSQuery1.ExecSQL;
  end;

  Series1.Clear;
  Series2.Clear;
  Series3.Clear;
  Series4.Clear;
  DataForm.ABSQuery1.SQL.Clear;
  DataForm.ABSQuery1.SQL.Add('select * from MEMORY tongji');

  DataForm.ABSQuery1.Open;
  DataForm.ABSQuery1.First;
  while not DataForm.ABSQuery1.Eof do
  begin
    if DataForm.ABSQuery1.FieldByName('income').AsFloat <>0 then
    begin
      Series1.Add(DataForm.ABSQuery1.FieldByName('income').AsFloat,DataForm.ABSQuery1.FieldByName('szname').AsString,clTeeColor);
      Series3.Add(DataForm.ABSQuery1.FieldByName('income').AsFloat,DataForm.ABSQuery1.FieldByName('szname').AsString,clTeeColor);
    end;
    if DataForm.ABSQuery1.FieldByName('payout').AsFloat <>0 then
    begin
      Series2.Add(DataForm.ABSQuery1.FieldByName('payout').AsFloat,DataForm.ABSQuery1.FieldByName('szname').AsString,clTeeColor);
      Series4.Add(DataForm.ABSQuery1.FieldByName('payout').AsFloat,DataForm.ABSQuery1.FieldByName('szname').AsString,clTeeColor);
    end;
    DataForm.ABSQuery1.Next;
  end;

  DataForm.ReportQuery.SQL.Clear;
  DataForm.ReportQuery.SQL.Add('select * from MEMORY tongji');
  DataForm.ReportQuery.Open;

  DataForm.ABSQuery1.SQL.Clear;
  DataForm.ABSQuery1.SQL.Add('Drop table MEMORY lsb');
  DataForm.ABSQuery1.ExecSQL;

  DataForm.ABSQuery1.SQL.Clear;
  DataForm.ABSQuery1.SQL.Add('Drop table MEMORY tongji');
  DataForm.ABSQuery1.ExecSQL;

  Shape1.Parent := StatusBar1;
  Shape1.Top:=shaperect.Top;
  Shape1.Left:=shaperect.Left;
  Shape1.Height:=shaperect.Bottom-shaperect.Top;
  Shape1.Width:=shaperect.Right-shaperect.Left;
end;

procedure TReportForm.Button2Click(Sender: TObject);
begin
  if PageControl1.ActivePageIndex=PageControl1.PageCount-1 then PageControl1.ActivePageIndex:=0
  else PageControl1.ActivePageIndex:=PageControl1.ActivePageIndex+1;
end;

procedure TReportForm.Chart1MouseMove(Sender: TObject; Shift: TShiftState;
  X, Y: Integer);
var tmp:Longint;
begin
  tmp:=Series1.Clicked(x,y);
  if tmp=-1 then Chart1.ShowHint:=False
  else
  begin
    Chart1.ShowHint:=True;
    Chart1.Hint:=Series1.LegendString(tmp,ltsRightPercent);
    Shape1.Brush.Color:=Series1.ValueColor[tmp];
    StatusBarValue(Series1,tmp);
  end;
end;

procedure TReportForm.Chart2MouseMove(Sender: TObject; Shift: TShiftState;
  X, Y: Integer);
var tmp:Longint;
begin
  tmp:=Series2.Clicked(x,y);
  if tmp=-1 then Chart2.ShowHint:=False
  else
  begin
    Chart2.ShowHint:=True;
    Chart2.Hint:=Series2.LegendString(tmp,ltsRightPercent);
    Shape1.Brush.Color:=Series2.ValueColor[tmp];
    StatusBarValue(Series2,tmp);
  end;
end;

procedure TReportForm.StatusBar1DrawPanel(StatusBar: TStatusBar;
  Panel: TStatusPanel; const Rect: TRect);
begin
  ShapeRect:=Rect;
end;

procedure TReportForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  DBGridEh.DataSource.DataSet.Close;
  DataForm.DataSource.DataSet:=nil;
  DBGridEh.DataSource:=nil;
  Action:=CaFree;
end;

procedure TReportForm.cbx_accountClick(Sender: TObject);
begin
  cmb_account.Enabled:=cbx_account.Checked
end;

procedure TReportForm.cbx_codeClick(Sender: TObject);
begin
  cmb_code.Enabled:=cbx_code.Checked
end;

end.