frmffunit.~pas

根据客户对销售人员回款返利和业绩返利的功能需求所开发

unit frmFFUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, frmBaseUnit, DB, StdCtrls, Buttons, ExtCtrls, ADODB, Grids,
  Wwdbigrd, Wwdbgrid, wwdblook, Wwdbdlg;

type
  TfrmFF = class(TfrmBase)
    qryFF: TADOQuery;
    Button1: TButton;
    wwDBGrid1: TwwDBGrid;
    qryFFCOL001: TStringField;
    qryFFCOL002: TBCDField;
    qryFFCOL003: TBCDField;
    qryFFCOL004: TBCDField;
    qryFFCOL005: TBCDField;
    qryFFCOL006: TBCDField;
    qryFFCOL007: TBCDField;
    qryFFCOL008: TBCDField;
    qryFFCOL009: TBCDField;
    qryFFCOL010: TBCDField;
    qryFFCOL011: TBCDField;
    qryFFCOL012: TBCDField;
    qryFFSAL_NO: TStringField;
    Label1: TLabel;
    qrySO: TADOQuery;
    qrySOoS_NO: TStringField;
    qrySOOS_DD: TDateTimeField;
    qrySOCUS_NO: TStringField;
    qrySOSAL_NO: TStringField;
    qrySAL_NAME: TADOQuery;
    qrySAL_NAMESAL_NO: TStringField;
    qrySAL_NAMENAME: TStringField;
    ww1: TwwDBLookupComboDlg;
    ww2: TwwDBLookupComboDlg;
    Label2: TLabel;
    ww3: TwwDBLookupComboDlg;
    ww4: TwwDBLookupComboDlg;
    qryFFSAL_NAME: TStringField;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btn_CloseClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure btn_PrintClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmFF: TfrmFF;

implementation

uses dmUnit;

{$R *.dfm}

procedure TfrmFF.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  inherited;
  action:=caFree;
  frmFF:=NIl;
end;

procedure TfrmFF.btn_CloseClick(Sender: TObject);
begin
  inherited;
   close;
end;

procedure TfrmFF.Button1Click(Sender: TObject);
var
 sql:String;
begin
  inherited;
  sql:='delete from #FF';
  dm.qryExecute(sql);
  sql:='insert into #FF(COL001,SAL_NO) select OS_NO,SAL_NO from MF_POS where 1=1 ';
  if ww1.Text<>'' then
    sql:=sql+' and OS_NO>='''+ww1.Text+'''';
  if ww2.Text<>'' then
    sql:=sql+' and OS_NO<='''+ww2.Text+'''';
  if ww3.Text<>'' then
    sql:=sql+' and SAL_NO>='''+ww3.Text+'''';
  if ww4.Text<>'' then
    sql:=sql+' and SAL_NO<='''+ww4.Text+'''';
  dm.qryExecute(sql);
  sql:='update #FF set COL002=F.QTY  from #FF A, '
      +' (select OS_NO,QTY=SUM(isnull(QTY,0)*isnull(UP,0)) from tf_pos group by OS_NO ) F '
      +' where A.COL001  =F.OS_NO COLLATE database_default';
  dm.qryExecute(sql); //订单金额

   sql:='update #FF set SAL_NAME=B.NAME from #FF A,SALM B where A.SAL_NO=B.SAL_NO COLLATE database_default';
   dm.qryExecute(sql);
   
   sql:='update #FF set COL003=F.QTY from #FF A, '
      +' (select OS_NO,QTY=SUM(isnull(QTY_PRE,0)*isnull(UP,0)) from tf_pos group by OS_NO ) F '
      +' where A.COL001=F.OS_NO COLLATE database_default';
  dm.qryExecute(sql); //退回金额
  sql:='update #FF set COL004=F.qty from #FF A, '
       +' (select A.OS_NO,QTY=SUM(isnull(B.A,0)*(isnull(A.qty,0)-isnull(A.QTY_PRE,0))) '
       +'   from tf_pos A,TF_POS_Z B where A.OS_NO=B.OS_NO AND A.ITM=B.ITM '
       +'   group by A.OS_NO ) F where A.COL001=F.OS_NO COLLATE database_default';
  dm.qryExecute(sql);   // 底价金额

  sql:='update #FF set COL005=isnull(COL002,0)-isnull(COL003,0)-isnull(col004,0) ';
  dm.qryExecute(sql);   //可兑现金额
  sql:='update #FF set COL006=F.qty from #FF A, '
      +' (select A.OS_NO,QTY=sum(isnull(A.QTY,0)*isnull(A.UP,0)) from TF_PSS A '
      +'  group by A.OS_NO ) F where A.COL001=F.OS_NO COLLATE database_default';
  dm.qryExecute(sql);    //销货金额

    sql:='update #FF set COL007=F.qty from #FF A, '
      +' (select A.OS_NO,QTY=sum(isnull(A.QTY_RTN,0)*isnull(A.UP,0)) from TF_PSS A '
      +'  group by A.OS_NO ) F where A.COL001=F.OS_NO COLLATE database_default';
  dm.qryExecute(sql);   //销货退回金额

  sql:=' update #FF set COL008=F.qty from #FF A, '
       +' (select A.OS_NO,QTY=sum((isnull(A.QTY,0)-isnull(A.qty_RTN,0))*isnull(B.A,0)) '
       +' from TF_PSS A,TF_POS_Z B where A.OS_NO=B.OS_NO AND A.EST_ITM=B.ITM '
       +' group by A.OS_NO ) F where A.COL001=F.OS_NO COLLATE database_default';
   dm.qryExecute(sql);     // 销货底价金额
   sql:=' update #FF set COL009=isnull(COL006,0)-isnull(COL007,0)-isnull(COL008,0) ';
   dm.qryExecute(sql);    //应兑现金额

   sql:='update #FF set COL010=F.qty from #FF A, '
      +' (select OS_NO,qty=sum(isnull(QTY,0)) from FYLIST group by OS_NO ) F '
      +' where A.COL001=F.OS_NO COLLATE database_default';
   dm.qryExecute(sql);  //费用

    sql:='update #FF set COL011=F.qty from #FF A, '
      +' (select OS_NO,QTY=SUM(isnull(qty,0)) from dx group by os_no ) F '
      +' where A.COL001=F.OS_NO COLLATE database_default';
   dm.qryExecute(sql);  //已兑现金额

   sql:='update #FF set COL011=isnull(COL009,0)-isnull(COL010,0)-isnull(COL011,0) from #FF A ';
   dm.qryExecute(sql);  //未兑现金额




  qryFF.Active:=False;
  qryFF.Active:=True;
end;

procedure TfrmFF.FormShow(Sender: TObject);
begin
  inherited;
  qrySO.Active:=True;
  qrySAL_NAME.Active:=True;
  qryFF.Active:=True;
end;

procedure TfrmFF.btn_PrintClick(Sender: TObject);
begin
  inherited;
   wwDBGrid1.ExportOptions.FileName:=fSave.FileName+'.csv';
   wwDBGrid1.ExportOptions.Save;
end;

end.