jiesuan_month.pas

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

unit jiesuan_month;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls;

type
  TForm9 = class(TForm)
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    Button5: TButton;
    Button1: TButton;
    Label1: TLabel;
    Label2: TLabel;
    procedure Button5Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ChangeFonts(OldFormWidth:integer;NewFormWidth:integer);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form9: TForm9;

implementation

uses Data_Unit1;

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 TForm9.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;

{$R *.dfm}

procedure TForm9.Button5Click(Sender: TObject);
begin
  Form9.close;
end;

procedure TForm9.Button1Click(Sender: TObject);
var
    Var_year,Var_month,Var_day:integer;
    date1,date2:TdateTime;
    i:integer;
    amount,amount0,kaituo_total,fudao_total,guanli_total:Extended;
    member_id,member_id0:string;
begin
 Var_year := strtoint(ComboBox1.Text);
 Var_month := strtoint(ComboBox2.Text);
 if (Var_month=4) or (Var_month=6) or (Var_month=9) or (Var_month=11) then Var_day :=30
 else if Var_month=2 then Var_day := 28
 else Var_day := 31;
 date1 := EncodeDate(Var_year,Var_month,1);
 date2 := EncodeDate(Var_year,Var_month,Var_day);
 if date <= date2 then begin
     showmessage ('还不到结算日期！');
 end
 else begin
   with DataModule1.ADOQuery_jiesuan do begin
     sql.Clear;
     sql.Add('Select id from jiesuan_month where year_js=:AA2 and month_js=:AA3');
     Parameters.ParamValues['AA2'] := Var_year;
     Parameters.ParamValues['AA3'] := Var_month;
     open;
   end;
   if DataModule1.ADOQuery_jiesuan.RecordCount > 0 then begin
     DataModule1.ADOQuery_jiesuan.close;
     showmessage ('本月已结算！');
   end
   else begin
     DataModule1.ADOQuery_jiesuan.close;
     kaituo_total := 0;
     fudao_total := 0;
     guanli_total := 0;
     amount0 := 0;

// 开始结算开拓奖
     with DataModule1.ADOinsert_jiesuan do begin
       sql.Clear;
       sql.Add('insert into jiesuan_month_kaituo (member_id,amount,year_js,month_js,date_valid,sign_year) VALUES (:E,:A,:Y,:M,:D,:S)');
     end;
     with DataModule1.ADOQuery_jiangjin do begin
       sql.Clear;
       sql.Add('Select member_id,amount from jiangjin where (date_valid between :AA2 and :AA3) and jj_type=:AA1 order by member_id');
       Parameters.ParamValues['AA1'] := '开拓奖';
       Parameters.ParamValues['AA2'] := date1;
       Parameters.ParamValues['AA3'] := date2;
       open;
       if RecordCount > 0 then begin
         Recordset.MoveFirst;
         member_id0 := Recordset.Fields.Item['member_id'].Value;
         amount0 := Recordset.Fields.Item['amount'].Value;
         kaituo_total := kaituo_total + amount0;
         Recordset.MoveNext;
       end;
     end;
     for i:= 1 to DataModule1.ADOQuery_jiangjin.RecordCount-1 do begin
         member_id :=  DataModule1.ADOQuery_jiangjin.Recordset.Fields.Item['member_id'].Value;
         amount :=  DataModule1.ADOQuery_jiangjin.Recordset.Fields.Item['amount'].Value;
         kaituo_total := kaituo_total + amount;
         if member_id = member_id0 then begin
            amount0 := amount0 + amount;
         end
         else begin
           with DataModule1.ADOinsert_jiesuan  do begin
             close;
             Parameters.ParamValues['E'] := member_id0;
             Parameters.ParamValues['A'] := amount0;
             Parameters.ParamValues['Y'] := Var_year;
             Parameters.ParamValues['M'] := Var_month;
             Parameters.ParamValues['D'] := date2;
             Parameters.ParamValues['S'] := 0;
             ExecSQL;
           end;
           amount0 := amount;
           member_id0 := member_id;
         end;
         DataModule1.ADOQuery_jiangjin.Recordset.MoveNext;
     end;
     if DataModule1.ADOQuery_jiangjin.RecordCount > 0 then begin
       with DataModule1.ADOinsert_jiesuan  do begin
         close;
         Parameters.ParamValues['E'] := member_id0;
         Parameters.ParamValues['A'] := amount0;
         Parameters.ParamValues['Y'] := Var_year;
         Parameters.ParamValues['M'] := Var_month;
         Parameters.ParamValues['D'] := date;
         Parameters.ParamValues['S'] := 0;
         ExecSQL;
       end;
     end;
     DataModule1.ADOQuery_jiangjin.close;

// 开始结算辅导奖
     with DataModule1.ADOinsert_jiesuan do begin
       sql.Clear;
       sql.Add('insert into jiesuan_month_fudao (member_id,amount,year_js,month_js,date_valid,sign_year) VALUES (:E,:A,:Y,:M,:D,:S)');
     end;
     with DataModule1.ADOQuery_jiangjin do begin
       sql.Clear;
       sql.Add('Select member_id,amount from jiangjin where (date_valid between :AA2 and :AA3) and jj_type=:AA1 order by member_id');
       Parameters.ParamValues['AA1'] := '辅导奖';
       Parameters.ParamValues['AA2'] := date1;
       Parameters.ParamValues['AA3'] := date2;
       open;
       if RecordCount > 0 then begin
         Recordset.MoveFirst;
         member_id0 := Recordset.Fields.Item['member_id'].Value;
         amount0 := Recordset.Fields.Item['amount'].Value;
         fudao_total := fudao_total + amount0;
         Recordset.MoveNext;
       end;
     end;
     for i:= 1 to DataModule1.ADOQuery_jiangjin.RecordCount-1 do begin
         member_id :=  DataModule1.ADOQuery_jiangjin.Recordset.Fields.Item['member_id'].Value;
         amount :=  DataModule1.ADOQuery_jiangjin.Recordset.Fields.Item['amount'].Value;
         fudao_total := fudao_total + amount;
         if member_id = member_id0 then begin
            amount0 := amount0 + amount;
         end
         else begin
           with DataModule1.ADOinsert_jiesuan  do begin
             close;
             Parameters.ParamValues['E'] := member_id0;
             Parameters.ParamValues['A'] := amount0;
             Parameters.ParamValues['Y'] := Var_year;
             Parameters.ParamValues['M'] := Var_month;
             Parameters.ParamValues['D'] := date2;
             Parameters.ParamValues['S'] := 0;
             ExecSQL;
           end;
           amount0 := amount;
           member_id0 := member_id;
         end;
         DataModule1.ADOQuery_jiangjin.Recordset.MoveNext;
     end;
     if DataModule1.ADOQuery_jiangjin.RecordCount > 0 then begin
       with DataModule1.ADOinsert_jiesuan  do begin
         close;
         Parameters.ParamValues['E'] := member_id0;
         Parameters.ParamValues['A'] := amount0;
         Parameters.ParamValues['Y'] := Var_year;
         Parameters.ParamValues['M'] := Var_month;
         Parameters.ParamValues['D'] := date;
         Parameters.ParamValues['S'] := 0;
         ExecSQL;
       end;
     end;
     DataModule1.ADOQuery_jiangjin.close;

// 开始结算管理奖
     with DataModule1.ADOinsert_jiesuan do begin
       sql.Clear;
       sql.Add('insert into jiesuan_month_guanli (member_id,amount,year_js,month_js,date_valid,sign_year) VALUES (:E,:A,:Y,:M,:D,:S)');
     end;
     with DataModule1.ADOQuery_jiangjin do begin
       sql.Clear;
       sql.Add('Select member_id,amount from jiangjin where (date_valid between :AA2 and :AA3) and jj_type=:AA1 order by member_id');
       Parameters.ParamValues['AA1'] := '管理奖';
       Parameters.ParamValues['AA2'] := date1;
       Parameters.ParamValues['AA3'] := date2;
       open;
       if RecordCount > 0 then begin
         Recordset.MoveFirst;
         member_id0 := Recordset.Fields.Item['member_id'].Value;
         amount0 := Recordset.Fields.Item['amount'].Value;
         guanli_total := guanli_total + amount0;
         Recordset.MoveNext;
       end;
     end;
     for i:= 1 to DataModule1.ADOQuery_jiangjin.RecordCount-1 do begin
         member_id :=  DataModule1.ADOQuery_jiangjin.Recordset.Fields.Item['member_id'].Value;
         amount :=  DataModule1.ADOQuery_jiangjin.Recordset.Fields.Item['amount'].Value;
         guanli_total := guanli_total + amount;
         if member_id = member_id0 then begin
            amount0 := amount0 + amount;
         end
         else begin
           with DataModule1.ADOinsert_jiesuan  do begin
             close;
             Parameters.ParamValues['E'] := member_id0;
             Parameters.ParamValues['A'] := amount0;
             Parameters.ParamValues['Y'] := Var_year;
             Parameters.ParamValues['M'] := Var_month;
             Parameters.ParamValues['D'] := date2;
             Parameters.ParamValues['S'] := 0;
             ExecSQL;
           end;
           amount0 := amount;
           member_id0 := member_id;
         end;
         DataModule1.ADOQuery_jiangjin.Recordset.MoveNext;
     end;
     if DataModule1.ADOQuery_jiangjin.RecordCount > 0 then begin
       with DataModule1.ADOinsert_jiesuan  do begin
         close;
         Parameters.ParamValues['E'] := member_id0;
         Parameters.ParamValues['A'] := amount0;
         Parameters.ParamValues['Y'] := Var_year;
         Parameters.ParamValues['M'] := Var_month;
         Parameters.ParamValues['D'] := date;
         Parameters.ParamValues['S'] := 0;
         ExecSQL;
       end;
     end;
     DataModule1.ADOQuery_jiangjin.close;

// 开始进行汇总结算

     with DataModule1.ADOinsert_jiesuan  do begin
       close;
       sql.Clear;
       sql.Add('insert into jiesuan_month (amount_kaituo,amount_fudao,amount_guanli,year_js,month_js,date_valid,sign_year) VALUES (:A,:A2,:A3,:Y,:M,:D,:S)');
       Parameters.ParamValues['A'] := kaituo_total;
       Parameters.ParamValues['A2'] := fudao_total;
       Parameters.ParamValues['A3'] := guanli_total;
       Parameters.ParamValues['Y'] := Var_year;
       Parameters.ParamValues['M'] := Var_month;
       Parameters.ParamValues['D'] := date;
       Parameters.ParamValues['S'] := 0;
       ExecSQL;
     end;

// 将奖金表中已结算的项目置位

     with DataModule1.ADOQuery_jiangjin  do begin
       close;
       sql.Clear;
       sql.Add('update jiangjin set date_jiesuan=:AA4,sign_month=:AA5 where date_valid between :AA2 and :AA3');
       Parameters.ParamValues['AA2'] := date1;
       Parameters.ParamValues['AA3'] := date2;
       Parameters.ParamValues['AA4'] := date;
       Parameters.ParamValues['AA5'] := 1;
       ExecSQL;
     end;

     showmessage ('结算完成！');

   end;
 end;
end;

procedure TForm9.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);

  Decodedate (date,year,month,day);
  ComboBox1.Text := inttostr(year);
  ComboBox2.Text := inttostr(month);
end;

end.