slsaleforecast0.pas

详细的ERP设计资料

unit SLSaleForeCast;

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, WNADOCQuery;

type
  TSLSaleForeCastForm = class(TWSVoucherBrowseForm)
    ToolButton4: TToolButton;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    ToolButton7: TToolButton;
    ToolButton8: TToolButton;
    ToolButton9: TToolButton;
    ToolButton11: TToolButton;
    ToolButton12: TToolButton;
    ToolButton13: TToolButton;
    adsMaster: TADODataSet;
    Panel2: TPanel;
    ADOQuery: TADOQuery;
    ADOQuery2: TADOQuery;
    GroupBox1: TGroupBox;
    DateTimePicker1: TDateTimePicker;
    Label1: TLabel;
    Panel3: TPanel;
    Panel5: TPanel;
    Button2: TButton;
    GroupBox2: TGroupBox;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    CheckBox4: TCheckBox;
    CheckBox5: TCheckBox;
    CheckBox6: TCheckBox;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    TempAds: TADODataSet;
    StringField1: TStringField;
    StringField2: TStringField;
    BCDField1: TBCDField;
    BCDField2: TBCDField;
    BCDField3: TBCDField;
    BCDField4: TBCDField;
    BCDField5: TBCDField;
    BCDField6: TBCDField;
    BCDField7: TBCDField;
    BCDField8: TBCDField;
    IntegerField1: TIntegerField;
    WNADOCQuery1: TWNADOCQuery;
    CheckBox3: TCheckBox;
    RadioGroup1: TRadioGroup;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    ComboBox1: TComboBox;
    Label2: TLabel;
    Panel4: TPanel;
    Button1: TButton;
    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);
    procedure Button2Click(Sender: TObject);
    procedure RadioButton2Click(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure CheckBox4Click(Sender: TObject);

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

var
  SLSaleForeCastForm: TSLSaleForeCastForm;

implementation

uses CommonDM,FNReceiptPayLeger,QLDBFlt;

{$R *.dfm}

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


procedure TSLSaleForeCastForm.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;