jiangjinfafang.pas

类似于安利公司的奖金制度下的会员层次关系、会员级别、会员奖金等

unit jiangjinfafang;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, DBCtrls, Mask, Grids, DBGrids, OleServer,
  Word2000, Excel2000;

type
  TForm8 = class(TForm)
    Label3: TLabel;
    Label2: TLabel;
    ComboBox1: TComboBox;
    Label4: TLabel;
    ComboBox2: TComboBox;
    Label5: TLabel;
    StringGrid1: TStringGrid;
    Label1: TLabel;
    Label6: TLabel;
    Button5: TButton;
    Button6: TButton;
    Button7: TButton;
    Button8: TButton;
    Button1: TButton;
    ExcelApplication1: TExcelApplication;
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button7Click(Sender: TObject);
    procedure Button8Click(Sender: TObject);
    function getname(member_id:string):string;
    procedure Button6Click(Sender: TObject);
    procedure ChangeFonts(OldFormWidth:integer;NewFormWidth:integer);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

type
  jsdata = Record
  member_id,name,jj_type,date_valid:string;
  jj_amount:Extended;
end;

var
  Form8: TForm8;
  Var_mode,Num:integer;
  amount_total:Extended;
implementation

{$R *.dfm}
uses
    Data_Unit1,report1;

type
  TFooClass = class(TControl); { needed to get at protected }
                             { font property }
const
ScreenWidth: LongInt = 800; {I designed my form in 800x600 mode.}
ScreenHeight: LongInt = 600;

procedure TForm8.ChangeFonts(OldFormWidth:integer;NewFormWidth:integer);
var
i: integer;
begin
  NewFormWidth := longint(width) * longint(screen.width) div ScreenWidth;
  for i := ControlCount - 1 downto 0 do
    TFooClass(Controls[i]).Font.Size :=
      (NewFormWidth div OldFormWidth) *
      TFooClass(Controls[i]).Font.Size;
end;

procedure TForm8.FormCreate(Sender: TObject);
var
  year,month,day:word;
begin
  scaled := true;
  if (screen.width <> ScreenWidth) then
  begin
    height := longint(height) * longint(screen.height) div ScreenHeight;
    width := longint(width) * longint(screen.width) div ScreenWidth;
    scaleBy(screen.width, ScreenWidth);
  end;
  ChangeFonts(ScreenWidth,width);

  StringGrid1.RowCount := 10;
  StringGrid1.Cells[0,0] := '会员编号';
  StringGrid1.Cells[1,0] := '会员名称';
  StringGrid1.Cells[2,0] := '奖金类型';
  StringGrid1.Cells[3,0] := '金额';
  StringGrid1.Cells[4,0] := '生效日期';
  Decodedate (date,year,month,day);
  ComboBox1.Text := inttostr(year);
  ComboBox2.Text := inttostr(month);
end;

procedure TForm8.Button2Click(Sender: TObject);
begin
   Form8.close;
end;

function TForm8.getname(member_id:string):string;
begin
    with DataModule1.ADOQuery_upgrade2 do begin
      sql.Clear;
      sql.Add('Select name from member where member_id=:AA1');
      Parameters.ParamValues['AA1'] := member_id;
      open;
      if recordset.RecordCount = 0 then Result := ''
      else Result := FieldValues['name'];
    end;
end;

procedure TForm8.Button1Click(Sender: TObject);
var
  rangeE:Excel2000.Range;
  I,J:integer;
begin
  // creat and show
  ExcelApplication1.Visible[0] := True;
  ExcelApplication1.Workbooks.Add(NULL,0);

  case   Var_mode of
  0:begin  // 查询本月明细
      //fill is the first row with field titles 第一行为标题
      RangeE := ExcelApplication1.ActiveCell;    //选择单元格
//      for I := 0 to 4 do
//      begin
        RangeE.Value := '会员编号';
        RangeE := RangeE.Next;
        RangeE.Value := '会员名称';
        RangeE := RangeE.Next;
        RangeE.Value := '奖金类型';
        RangeE := RangeE.Next;
        RangeE.Value := '金额';
        RangeE := RangeE.Next;
        RangeE.Value := '生效日期';
        RangeE := RangeE.Next;
//      end;
      // add field data in following rows 添加数据到各行
      for J := 1 to Num do begin
        RangeE := ExcelApplication1.Range['A'+inttostr(J+1),'A'+inttostr(J+1)];
        for I := 0 to 4 do begin
          RangeE.Value := StringGrid1.Cells[I,J];
          RangeE := RangeE.Next;
        end;
      end;
      // the last row is the total amount 最后一行为合计
      RangeE := ExcelApplication1.Range['C'+inttostr(Num+2),'C'+inttostr(Num+2)];
      RangeE.Value := '合计';
      RangeE := ExcelApplication1.Range['D'+inttostr(Num+2),'D'+inttostr(Num+2)];
      RangeE.Value := floattostr(amount_total);
      // format the section
      RangeE := ExcelApplication1.Range['A1','E'+inttostr(Num+2)];
      RangeE.AutoFormat(2,NULL,NULL,NULL,NULL,NULL,NULL);
    end;
  1:begin  //按机构汇总查询
      //fill is the first row with field titles
      RangeE := ExcelApplication1.ActiveCell;    //选择单元格
      for I := 0 to 3 do
      begin
        if I <> 2 then begin
          RangeE.Value := StringGrid1.Cells[I,0];
          RangeE := RangeE.Next;
        end;
      end;
      // add field data in following rows
      for J := 1 to Num do begin
        RangeE := ExcelApplication1.Range['A'+inttostr(J+1),'A'+inttostr(J+1)];
        for I := 0 to 3 do
          if I <> 2 then begin
            RangeE.Value := StringGrid1.Cells[I,J];
            RangeE := RangeE.Next;
          end;
      end;
      // the last row is the total amount
      RangeE := ExcelApplication1.Range['B'+inttostr(Num+2),'B'+inttostr(Num+2)];
      RangeE.Value := '合计';
      RangeE := ExcelApplication1.Range['C'+inttostr(Num+2),'C'+inttostr(Num+2)];
      RangeE.Value := floattostr(amount_total);
      // format the section
      RangeE := ExcelApplication1.Range['A1','C'+inttostr(Num+2)];
      RangeE.AutoFormat(2,NULL,NULL,NULL,NULL,NULL,NULL);
    end;
  2:begin  //按奖金类型汇总查询
      //fill is the first row with field titles
      RangeE := ExcelApplication1.ActiveCell;    //选择单元格
      for I := 2 to 3 do
      begin
        RangeE.Value := StringGrid1.Cells[I,0];
        RangeE := RangeE.Next;
      end;
      // add field data in following rows
      for J := 1 to Num do begin
        RangeE := ExcelApplication1.Range['A'+inttostr(J+1),'A'+inttostr(J+1)];
        for I := 2 to 3 do begin
          RangeE.Value := StringGrid1.Cells[I,J];
          RangeE := RangeE.Next;
        end;
      end;
      // the last row is the total amount
      RangeE := ExcelApplication1.Range['A'+inttostr(Num+2),'A'+inttostr(Num+2)];
      RangeE.Value := '合计';
      RangeE := ExcelApplication1.Range['B'+inttostr(Num+2),'B'+inttostr(Num+2)];
      RangeE.Value := floattostr(amount_total);
      // format the section
      RangeE := ExcelApplication1.Range['A1','B'+inttostr(Num+2)];
      RangeE.AutoFormat(2,NULL,NULL,NULL,NULL,NULL,NULL);
    end;
  end;
end;

procedure TForm8.Button6Click(Sender: TObject);     //// 查询本月明细
var
    year,month,day:word;
    date1,date2:TdateTime;
    i:integer;
begin
   Var_mode := 0;
   Num := 0;
   year := strtoint(ComboBox1.Text);
   month := strtoint(ComboBox2.Text);
   if (month=4) or (month=6) or (month=9) or (month=11) then day :=30
   else if month=2 then day := 28
   else day := 31;
   date1 := EncodeDate(year,month,1);
   date2 := EncodeDate(year,month,day);
   StringGrid1.RowCount := 1;
   StringGrid1.Refresh;
   amount_total := 0;
   with DataModule1.ADOQuery_jiangjin do begin
     sql.Clear;
     sql.Add('Select a.member_id,a.jj_type,a.amount,a.date_valid,b.name from jiangjin as a inner join member as b on a.member_id = b.member_id where (a.date_valid between :AA2 and :AA3) order by a.member_id,a.date_valid');
     Parameters.ParamValues['AA2'] := date1;
     Parameters.ParamValues['AA3'] := date2;
     open;
     if RecordCount > 0 then begin
       StringGrid1.RowCount := RecordCount+1;
       Num := RecordCount;
       i:=1;
       Recordset.MoveFirst;
       repeat
         amount_total := amount_total + Recordset.Fields.Item['amount'].Value;
         StringGrid1.Cells[0,i] := Recordset.Fields.Item['member_id'].Value;
         StringGrid1.Cells[1,i] := Recordset.Fields.Item['name'].Value;
         StringGrid1.Cells[2,i] := Recordset.Fields.Item['jj_type'].Value;
         StringGrid1.Cells[3,i] := floattostr(Recordset.Fields.Item['amount'].Value);
         StringGrid1.Cells[4,i] := FormatDateTime('yyyy-mm-dd',Recordset.Fields.Item['date_valid'].Value);
         inc(i);
         Recordset.MoveNext;
       until Recordset.EOF;
     end;
     close;
   end;
   Label6.Caption := floattostr(amount_total);
end;

procedure TForm8.Button7Click(Sender: TObject);       //按机构汇总查询
var
    jsdata1,jsdata0:jsdata;
    year,month,day:word;
    date1,date2:TdateTime;
    i:integer;
begin
   Num := 0;
   Var_mode := 1;
   year := strtoint(ComboBox1.Text);
   month := strtoint(ComboBox2.Text);
   if (month=4) or (month=6) or (month=9) or (month=11) then day :=30
   else if month=2 then day := 28
   else day := 31;
   date1 := EncodeDate(year,month,1);
   date2 := EncodeDate(year,month,day);
   StringGrid1.RowCount := 1;
   StringGrid1.Cells[0,0] := '会员编号';
   StringGrid1.Cells[1,0] := '会员名称';
   StringGrid1.Cells[2,0] := '奖金类型';
   StringGrid1.Cells[3,0] := '金额';
   StringGrid1.Cells[4,0] := '生效日期';
   amount_total := 0;
   with DataModule1.ADOQuery_jiangjin do begin
     sql.Clear;
     sql.Add('Select member_id,amount from jiangjin  where (date_valid between :AA2 and :AA3) order by member_id');
     Parameters.ParamValues['AA2'] := date1;
     Parameters.ParamValues['AA3'] := date2;
     open;
     if RecordCount > 0 then begin
       i:=1;
       Recordset.MoveFirst;
       jsdata0.member_id :=  Recordset.Fields.Item['member_id'].Value;
       jsdata0.jj_amount :=  Recordset.Fields.Item['amount'].Value;
       jsdata0.name :=  getname(jsdata0.member_id);
       jsdata0.jj_type :=  '所有类型';
       jsdata0.date_valid :=  FormatDateTime('yyyy-mm-dd',date2);
       amount_total := amount_total + jsdata0.jj_amount;
       Recordset.MoveNext;
       if not(Recordset.EOF) then
       repeat
         jsdata1.member_id :=  Recordset.Fields.Item['member_id'].Value;
         jsdata1.jj_amount :=  Recordset.Fields.Item['amount'].Value;
         amount_total := amount_total + jsdata1.jj_amount;
         if jsdata1.member_id = jsdata0.member_id then begin
            jsdata0.jj_amount := jsdata0.jj_amount + jsdata1.jj_amount;
         end
         else begin
           StringGrid1.RowCount := i+1;
           StringGrid1.Cells[0,i] := jsdata0.member_id;
           StringGrid1.Cells[1,i] := jsdata0.name;
           StringGrid1.Cells[2,i] := jsdata0.jj_type;
           StringGrid1.Cells[3,i] := floattostr(jsdata0.jj_amount);
           StringGrid1.Cells[4,i] := jsdata0.date_valid;
           inc(i);
           jsdata0.member_id :=  jsdata1.member_id;
           jsdata0.jj_amount :=  jsdata1.jj_amount;
           jsdata0.name :=  getname(jsdata0.member_id);
         end;
         Recordset.MoveNext;
       until Recordset.EOF;
       StringGrid1.RowCount := i+1;
       StringGrid1.Cells[0,i] := jsdata0.member_id;
       StringGrid1.Cells[1,i] := jsdata0.name;
       StringGrid1.Cells[2,i] := jsdata0.jj_type;
       StringGrid1.Cells[3,i] := floattostr(jsdata0.jj_amount);
       StringGrid1.Cells[4,i] := jsdata0.date_valid;
       Num := i;
     end;
     close;
   end;
   Label6.Caption := floattostr(amount_total);
end;

procedure TForm8.Button8Click(Sender: TObject);  //按奖金类型汇总查询
var
    jsdata1,jsdata0:jsdata;
    year,month,day:word;
    date1,date2:TdateTime;
    i:integer;
begin
   Num := 0;
   Var_mode := 2;
   year := strtoint(ComboBox1.Text);
   month := strtoint(ComboBox2.Text);
   if (month=4) or (month=6) or (month=9) or (month=11) then day :=30
   else if month=2 then day := 28
   else day := 31;
   date1 := EncodeDate(year,month,1);
   date2 := EncodeDate(year,month,day);
//   StringGrid1.Destroy;
//   StringGrid1.Create(Form8);
   StringGrid1.RowCount := 1;
   StringGrid1.Cells[0,0] := '会员编号';
   StringGrid1.Cells[1,0] := '会员名称';
   StringGrid1.Cells[2,0] := '奖金类型';
   StringGrid1.Cells[3,0] := '金额';
   StringGrid1.Cells[4,0] := '生效日期';
   amount_total := 0;
   with DataModule1.ADOQuery_jiangjin do begin
     sql.Clear;
     sql.Add('Select jj_type,amount from jiangjin  where (date_valid between :AA2 and :AA3) order by jj_type');
     Parameters.ParamValues['AA2'] := date1;
     Parameters.ParamValues['AA3'] := date2;
     open;
     if RecordCount > 0 then begin
       i:=1;
       Recordset.MoveFirst;
       jsdata0.jj_type :=  Recordset.Fields.Item['jj_type'].Value;
       jsdata0.jj_amount :=  Recordset.Fields.Item['amount'].Value;
       jsdata0.name :=  '所有会员';
       jsdata0.member_id :=  '----';
       jsdata0.date_valid :=  FormatDateTime('yyyy-mm-dd',date2);
       amount_total := amount_total + jsdata0.jj_amount;
       Recordset.MoveNext;
       if not(Recordset.EOF) then
       repeat
         jsdata1.jj_type :=  Recordset.Fields.Item['jj_type'].Value;
         jsdata1.jj_amount :=  Recordset.Fields.Item['amount'].Value;
         amount_total := amount_total + jsdata1.jj_amount;
         if jsdata1.jj_type = jsdata0.jj_type then begin
            jsdata0.jj_amount := jsdata0.jj_amount + jsdata1.jj_amount;
         end
         else begin
           StringGrid1.RowCount := i+1;
           StringGrid1.Cells[0,i] := jsdata0.member_id;
           StringGrid1.Cells[1,i] := jsdata0.name;
           StringGrid1.Cells[2,i] := jsdata0.jj_type;
           StringGrid1.Cells[3,i] := floattostr(jsdata0.jj_amount);
           StringGrid1.Cells[4,i] := jsdata0.date_valid;
           inc(i);
           jsdata0.jj_type :=  jsdata1.jj_type;
           jsdata0.jj_amount :=  jsdata1.jj_amount;
         end;
         Recordset.MoveNext;
       until Recordset.EOF;
       StringGrid1.RowCount := i+1;
       StringGrid1.Cells[0,i] := jsdata0.member_id;
       StringGrid1.Cells[1,i] := jsdata0.name;
       StringGrid1.Cells[2,i] := jsdata0.jj_type;
       StringGrid1.Cells[3,i] := floattostr(jsdata0.jj_amount);
       StringGrid1.Cells[4,i] := jsdata0.date_valid;
       Num := i;
     end;
     close;
   end;
   Label6.Caption := floattostr(amount_total);
end;

end.