fnaccountage.pas

详细的ERP设计资料

unit FNAccountAge;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, WSVoucherBrowse, DB, ActnList, Grids,WSEdit, DBGrids, QLDBGrid,
  ComCtrls, ExtCtrls, ToolWin,DateUtils, ADODB, StdCtrls, Buttons, GEdit,
  DBCtrls, Menus;

type
  TFNAccountAgeForm = class(TWSVoucherBrowseForm)
    ToolButton4: TToolButton;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    ToolButton7: TToolButton;
    ToolButton8: TToolButton;
    ToolButton9: TToolButton;
    ToolButton11: TToolButton;
    ToolButton12: TToolButton;
    ToolButton13: TToolButton;
    adsMaster: TADODataSet;
    Panel2: TPanel;
    Label1: TLabel;
    DateTimePicker1: TDateTimePicker;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    Label2: TLabel;
    ADOQuery: TADOQuery;
    ComboBox1: TComboBox;
    ADOQuery2: TADOQuery;
    adsMasterClient: TStringField;
    adsMasterEmployee: TStringField;
    adsMasterPeriodA: TBCDField;
    adsMasterPeriodB: TBCDField;
    adsMasterPeriodC: TBCDField;
    adsMasterPeriodD: TBCDField;
    adsMasterPeriodE: TBCDField;
    adsMasterPeriodF: TBCDField;
    adsMasterPeriodG: TBCDField;
    adsMasterPeriodTTL: TBCDField;
    adsMasterClientID: TIntegerField;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure UpdateDBGrid;
    procedure FormShow(Sender: TObject);
    procedure DBGridTitleClick(Column: TColumn);
    procedure Label2Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure DBGridDblClick(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }
  protected
    function CreateEditForm: TWSEditForm; override;
  end;

var
  FNAccountAgeForm: TFNAccountAgeForm;

implementation

uses CommonDM ,FNReceiptPayLeger;

{$R *.dfm}

function TFNAccountAgeForm.CreateEditForm: TWSEditForm;
begin
//  Result := TSLEdClearBillAForm.Create(Self);
end;


procedure TFNAccountAgeForm.BitBtn1Click(Sender: TObject);
var  DateStr :STRING;
begin
//  DecodeDate(DateTimePicker1.Date,year, month, day); +Quotedstr(DateStr)+
  DateStr :=DateTOSTR(DateTimePicker1.Date);
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#FNAccountAgeRP')
        +' )) DROP TABLE #FNAccountAgeRP ' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' CREATE TABLE #FNAccountAgeRP  ('
    +' [ClientID] [int] NULL ,[EmployeeID] [int] NULL, '
    +' [PeriodA] [float] NULL,   '
    +' [PeriodB] [float] NULL,   '
    +' [PeriodC] [float] NULL,   '
    +' [PeriodD] [float] NULL,   '
    +' [PeriodE] [float] NULL,   '
    +' [PeriodF] [float] NULL,   '
    +' [PeriodG] [float] NULL,   '
    +' [PeriodTTL] [float] NULL )   ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' TRUNCATE TABLE #FNAccountAgeRP';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' INSERT INTO #FNAccountAgeRP ('
    +' [ClientID],[EmployeeID],[PeriodA] ,   '
    +' [PeriodB] ,[PeriodC] ,[PeriodD],'
    +' [PeriodE], [PeriodF] ,[PeriodG] ,   '
    +' [PeriodTTL] )'
    +' SELECT ClientID,EmployeeID,                                '
    +' PeriodA ,PeriodB,PeriodC,PeriodD,    '
    +' PeriodE ,PeriodF,PeriodG,PeriodTTL FROM  '
    +' (SELECT ClientID,EmployeeID,                                '
    +' ISNULL(AMOUNT,0) PeriodA ,0 PeriodB,0 PeriodC,0 PeriodD,    '
    +' 0 PeriodE ,0 PeriodF,0 PeriodG,ISNULL(AMOUNT,0) PeriodTTL   '
    +' FROM #FNAccountAge WHERE DATEDIFF(Month, cleardate,'
    +Quotedstr(DateStr)+' ) <1                                     '
    +' UNION ALL                                                   '
    +' SELECT ClientID,EmployeeID,                                 '
    +' 0 PeriodA ,ISNULL(AMOUNT,0) PeriodB,0 PeriodC,0 PeriodD,    '
    +' 0 PeriodE ,0 PeriodF,0 PeriodG,ISNULL(AMOUNT,0) PeriodTTL   '
    +' FROM #FNAccountAge WHERE DATEDIFF(Month, cleardate,'
    +Quotedstr(DateStr)+' ) =1                                     '
    +' UNION ALL                                                   '
    +' SELECT ClientID,EmployeeID,                                 '
    +' 0 PeriodA ,0 PeriodB,ISNULL(AMOUNT,0) PeriodC,0 PeriodD,    '
    +' 0 PeriodE ,0 PeriodF,0 PeriodG,ISNULL(AMOUNT,0) PeriodTTL   '
    +' FROM #FNAccountAge WHERE DATEDIFF(Month, cleardate,'
    +Quotedstr(DateStr)+' ) =2                                     '
    +' UNION ALL                                                   '
    +' SELECT ClientID,EmployeeID,                                 '
    +' 0 PeriodA ,0 PeriodB,0 PeriodC,ISNULL(AMOUNT,0) PeriodD,    '
    +' 0 PeriodE ,0 PeriodF,0 PeriodG,ISNULL(AMOUNT,0) PeriodTTL   '
    +' FROM #FNAccountAge WHERE DATEDIFF(Month, cleardate,'
    +Quotedstr(DateStr)+' ) =3                                     '
    +' UNION ALL                                                   '
    +' SELECT ClientID,EmployeeID,                                 '
    +' 0 PeriodA ,0 PeriodB,0 PeriodC,0 PeriodD,                   '
    +' ISNULL(AMOUNT,0) PeriodE ,0 PeriodF,0 PeriodG,ISNULL(AMOUNT,0) PeriodTTL '
    +' FROM #FNAccountAge WHERE DATEDIFF(Month, cleardate,'
    +Quotedstr(DateStr)+' ) =4                                                  '
    +' UNION ALL                                                                '
    +' SELECT ClientID,EmployeeID,                                              '
    +' 0 PeriodA ,0 PeriodB,0 PeriodC,0 PeriodD,                                '
    +' 0 PeriodE ,ISNULL(AMOUNT,0) PeriodF,0 PeriodG,ISNULL(AMOUNT,0) PeriodTTL '
    +' FROM #FNAccountAge WHERE DATEDIFF(Month, cleardate,'
    +Quotedstr(DateStr)+' ) =5                                                  '
    +' UNION ALL                                                                '
    +' SELECT ClientID,EmployeeID,                                              '
    +' 0 PeriodA ,0 PeriodB,0 PeriodC,0 PeriodD,                                '
    +' 0 PeriodE ,0 PeriodF,ISNULL(AMOUNT,0) PeriodG,ISNULL(AMOUNT,0) PeriodTTL '
    +' FROM #FNAccountAge WHERE DATEDIFF(Month, cleardate,'
    +Quotedstr(DateStr)+' ) =6                                                  '
    +' UNION ALL                                                                '
    +' SELECT ClientID,EmployeeID,                                              '
    +' 0 PeriodA ,0 PeriodB,0 PeriodC,0 PeriodD,                                '
    +' 0 PeriodE ,0 PeriodF,ISNULL(AMOUNT,0) PeriodG,ISNULL(AMOUNT,0) PeriodTTL '
    +' FROM #FNAccountAge WHERE DATEDIFF(Month, cleardate,'
    +Quotedstr(DateStr)+' ) >6                                                  '
    +' ) AS A ' ;
  ADOQuery.ExecSQL;

  adsMaster.Close;
  adsMaster.CommandText :=' select a.ClientID,b.name Client,c.name Employee,'
    +' SUM(ISNULL(PeriodA,0)) PeriodA ,    '
    +' SUM(ISNULL(PeriodB,0)) PeriodB ,    '
    +' SUM(ISNULL(PeriodC,0)) PeriodC ,    '
    +' SUM(ISNULL(PeriodD,0)) PeriodD ,    '
    +' SUM(ISNULL(PeriodE,0)) PeriodE ,    '
    +' SUM(ISNULL(PeriodF,0)) PeriodF ,    '
    +' SUM(ISNULL(PeriodG,0)) PeriodG ,    '
    +' SUM(ISNULL(PeriodTTL,0)) PeriodTTL   '
    +' from #FNAccountAgeRp AS A   '
    +' LEFT OUTER JOIN DAClient as b  on a.ClientID=b.id '
    +' LEFT OUTER JOIN MSEmployee as c  on a.EmployeeID=c.id'
    +' GROUP BY a.ClientID,b.name, c.name';
  adsMaster.Open;
  UpdateDBGrid;
  DBGrid.Columns[1].Title.Caption :='一个月帐龄';
  DBGrid.Columns[2].Title.Caption :='二个月帐龄';
  DBGrid.Columns[3].Title.Caption :='三个月帐龄';
  DBGrid.Columns[4].Title.Caption :='四个月帐龄';
  DBGrid.Columns[5].Title.Caption :='五个月帐龄';
  DBGrid.Columns[6].Title.Caption :='六个月帐龄';
  DBGrid.Columns[7].Title.Caption :='长于六个月';
end;

procedure TFNAccountAgeForm.FormCreate(Sender: TObject);
begin
  inherited;
  DateTimePicker1.DateTime :=date;
//  DateTimePicker1.Date :=EndoftheMonth(date);
//  DateTimePicker2.Date :=EndoftheMonth(date);
end;

procedure TFNAccountAgeForm.UpdateDBGrid;
var  I: Integer;
begin
  with DBGrid do
  begin
    FooterRowCount := 0;
    Columns[0].Footer.ValueType := fvtStaticText;
    Columns[0].Footer.Value := '合计:';
    Columns[0].Footer.Alignment := taCenter;
    Columns[0].Title.Alignment:= taCenter;
    for I := 1 to Columns.Count - 1 do
    begin
      Columns[i].Width :=90;
      if Pos('日',Columns[I].FieldName)>0 then Columns[i].Width :=70;
      if Pos('编',Columns[I].FieldName)>0 then Columns[i].Width :=70;
      Columns[i].Title.Alignment:= taCenter;
      if Columns[I].Field is TNumericField then
      if Pos('Price',Columns[I].FieldName)<=0 then
        Columns[I].Footer.ValueType := fvtSum;
    end;
    FooterRowCount := 1;
  end;
end;

procedure TFNAccountAgeForm.FormShow(Sender: TObject);
begin
  inherited;
//  BitBtn1Click(sender);
 DateTimePicker1.DateTime :=Date;

end;

procedure TFNAccountAgeForm.DBGridTitleClick(Column: TColumn);
begin
  inherited;
   UpdateDBGrid;
end;

procedure TFNAccountAgeForm.Label2Click(Sender: TObject);
begin
  inherited;
  ComboBox1.SetFocus;
end;

procedure TFNAccountAgeForm.FormActivate(Sender: TObject);
var ReduceAmount:real;
begin
  inherited;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#FNAccountAge')
        +' )) DROP TABLE #FNAccountAge ' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' CREATE TABLE #FNAccountAge  ('
    +' [id] [int] NULL ,[Date] [datetime] NULL , '
    +' [Code] [varchar] (20) ,[ClientID] [int] NULL ,'
    +' [EmployeeID] [int] NULL ,[cleardate] [datetime] NULL , '
    +' [amount] [float]NULL   ) ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ReduceAmount')
        +' )) DROP TABLE #ReduceAmount ' ;
  ADOQuery.ExecSQL;