unit MAIN_QUERY_YWYGZ;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, MAIN_QUERY, DB, DBTables, ComCtrls, StdCtrls, TFlatComboBoxUnit,
  TFlatSpeedButtonUnit, Grids, DBGrids, DsFancyButton, ExtCtrls,MYSELF,FRMMAIN;

type
  TFrm_main_query_ywygz = class(TFrm_main_query)
    procedure SB_queryClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_main_query_ywygz: TFrm_main_query_ywygz;

implementation

{$R *.dfm}

procedure TFrm_main_query_ywygz.SB_queryClick(Sender: TObject);
var
   S_begindate,S_enddate:string;
begin
  inherited;
   if not Checkrights(USERRIGHTS,'查看') then
   begin
      messagedlg(Errormsg0006+'0007',mterror,[mbok],0);
      exit;
   end;
   S_begindate:=formatdatetime('yyyy-mm-dd',dtp_begin.Date );
   S_enddate:=formatdatetime('yyyy-mm-dd',dtp_end.Date );
   if S_begindate>S_enddate then            //開始日期大于結束日期
   begin
      messagedlg(Errormsg0012+'0026',mtError,[mbok],0);
      dtp_begin.SetFocus ;
      exit;
   end;
   with query_data do
   begin
      close;
      sql.Clear ;
      sql.Text :='SELECT People.Name, SUM( Sellrecord.NUMBER ) NUMBER, SUM( Sellrecord.NUMBER * Sellrecord.PRICE ) allprice,'
               +' SUM( Sellrecord.NUMBER * Sellrecord.SELLPRICE ) PRICE,'
               +' (SUM(Sellrecord.NUMBER * Sellrecord.SELLPRICE ) * 1000 / SUM(Sellrecord.NUMBER * Sellrecord.PRICE)) BFB'
               +' FROM People INNER JOIN Sellrecord'
               +' ON  (People.Id = Sellrecord.PEOPLEID)'
               +' WHERE (Sellrecord.mydate>="'+S_begindate+'")'
               +' AND (Sellrecord.mydate<="'+S_enddate+'")'
               +' GROUP BY People.Name'
               +' UNION ALL'
               +' SELECT People.Name, SUM( 0 - Xsthrecord.NUMBER ) NUMBER, SUM( 0 - Xsthrecord.NUMBER * Xsthrecord.PRICE ) allprice,'
               +' SUM( 0 - Xsthrecord.NUMBER * Xsthrecord.SELLPRICE ) PRICE,'
               +' (SUM(Xsthrecord.NUMBER * Xsthrecord.SELLPRICE ) * 1000 / SUM(Xsthrecord.NUMBER * Xsthrecord.PRICE)) BFB'
               +' FROM People INNER JOIN Xsthrecord'
               +' ON  (People.Id = Xsthrecord.PEOPLEID)'
               +' WHERE (Xsthrecord.mydate>="'+S_begindate+'")'
               +' AND (Xsthrecord.mydate<="'+S_enddate+'")'
               +' GROUP BY People.Name'
               +' ORDER BY People.NAME';
      try
         open;
      except
         messagedlg(Errormsg0001+'0008+0018+0023',mtError,[mbok],0);
         close;
      end;
   end;

end;

procedure TFrm_main_query_ywygz.FormCreate(Sender: TObject);
begin
  inherited;
   self.Caption :='業務員銷售提成統計';
end;

procedure TFrm_main_query_ywygz.FormShow(Sender: TObject);
begin
  inherited;
   SB_query.Click ;
end;

end.