ustatistic.pas

来自「DELPHI编程入门篇.从基础入手,浅显易懂,一定物有所值.」· PAS 代码 · 共 285 行

unit Ustatistic;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls, Buttons, ExtCtrls, Grids, RpDefine, RpRave,
  DB, ADODB, ValEdit, ToolWin, ImgList, RpCon, RpConDS,RvCsRpt,RVProj,RVClass,RVCsStd;

type
  Tfrmstatistic = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    DatePicker1: TDateTimePicker;
    DatePicker2: TDateTimePicker;
    ComboBox1: TComboBox;
    Label5: TLabel;
    ComboBox2: TComboBox;
    RvProject1: TRvProject;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    StringGrid1: TStringGrid;
    ImageList1: TImageList;
    CoolBar1: TCoolBar;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    SpeedButton3: TSpeedButton;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    statisticConnection1: TRvDataSetConnection;
    procedure FormCreate(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmstatistic: Tfrmstatistic;

implementation

uses Udatamodule;

{$R *.dfm}

procedure Tfrmstatistic.FormCreate(Sender: TObject);
begin
  datepicker1.Date:=date-7;
  datepicker2.Date:=date;
  stringgrid1.Cells[0,0]:='商品编号';
  stringgrid1.Cells[1,0]:='商品名称';
  stringgrid1.Cells[2,0]:='批发数量';
  stringgrid1.Cells[3,0]:='批发成本';
  stringgrid1.Cells[4,0]:='批发金额';
  stringgrid1.Cells[5,0]:='零售数量';
  stringgrid1.Cells[6,0]:='零售成本';
  stringgrid1.Cells[7,0]:='零售金额';
  stringgrid1.Cells[8,0]:='销售毛利';
  adoquery1.SQL.Text:='select filname from filiale';
  adoquery1.Open;
  while not adoquery1.Eof do
  begin
    combobox1.Items.Add(adoquery1.Fields[0].asstring);
    adoquery1.Next;
  end;
  adoquery1.Close;
  combobox1.ItemIndex:=0;
  adoquery1.SQL.Text:='select classname from classinfo';
  adoquery1.Open;
  while not adoquery1.Eof do
  begin
    combobox2.Items.Add(adoquery1.Fields[0].asstring);
    adoquery1.Next;
  end;
  adoquery1.Close;
  combobox2.ItemIndex:=0;
end;

procedure Tfrmstatistic.SpeedButton3Click(Sender: TObject);
begin
  modalresult:=mrcancel;
end;

procedure Tfrmstatistic.SpeedButton1Click(Sender: TObject);
var
  skind,sfilid:string;
  arow,i:integer;
  scost,ssale:currency;
begin
  coolbar1.SetFocus;
  label10.Caption:='0';
  label11.Caption:='0';
  label12.Caption:='0';
  for i:=1 to stringgrid1.RowCount-1 do
    stringgrid1.Rows[i].Clear;
  stringgrid1.RowCount:=2;
  if combobox2.Text <> '全部' then
  begin
    adoquery1.SQL.Text:='select classid from classinfo where classname = '''+combobox2.Text+'''';
    adoquery1.Open;
    skind:=adoquery1.Fields[0].AsString;
    adoquery1.Close;
  end;
  if combobox1.Text <> '全部分店' then
  begin
    adoquery1.SQL.Text:='select filid from filiale where filname = '''+combobox1.Text+'''';
    adoquery1.Open;
    sfilid:=adoquery1.Fields[0].AsString;
    adoquery1.Close;
  end;
  adoquery1.SQL.Text:='SELECT wholesale1.prid, pluinfo.prname, wholesale1.salenum, wholesale1.price,wholesale1.costprice'
  +' FROM (wholesale INNER JOIN wholesale1 ON wholesale.wholesaleid = wholesale1.wholesaleid)'
  +' INNER JOIN pluinfo ON wholesale1.prid = pluinfo.prid where wholesale.saledate'
  +' between #'+datetostr(DatePicker1.Date)+'# and #'+datetostr(DatePicker2.Date)+'#';

  adoquery2.SQL.Text:='SELECT plusale.prid, pluinfo.prname, plusale.salenum, plusale.costprice,'
  +' plusale.saletotal FROM pluinfo INNER JOIN plusale ON pluinfo.prid = plusale.prid'
  +' where plusale.transdate between #'+datetostr(DatePicker1.Date)+'# and #'
  +datetostr(DatePicker2.Date)+'#';
  if combobox1.Text <> '全部分店' then
  begin
    adoquery1.SQL.Add('and wholesale.filid = '''+sfilid+'''');
    adoquery2.SQL.Add('and plusale.filid ='''+sfilid+'''');
  end;
  if combobox2.Text <> '全部' then
  begin
    adoquery1.SQL.Add('and pluinfo.classid = '''+skind+'''');
    adoquery2.SQL.Add('and pluinfo.classid = '''+skind+'''');
  end;
  adoquery1.SQL.Add('order by wholesale1.prid');
  adoquery2.SQL.Add('order by plusale.prid');
  adoquery1.Open;
  adoquery2.Open;
  while not adoquery1.Eof do
  begin
    with stringgrid1 do
    begin
      if Cells[0,RowCount-1] <> '' then
      RowCount:=RowCount+1;
      arow:=RowCount-1;
      Cells[0,arow]:=adoquery1.FieldValues['prid'];
      Cells[1,arow]:=adoquery1.FieldValues['prname'];
      Cells[2,arow]:=adoquery1.FieldValues['salenum'];
      Cells[3,arow]:=currtostr(adoquery1.FieldValues['costprice']*adoquery1.FieldValues['salenum']);
      Cells[4,arow]:=currtostr(adoquery1.FieldValues['price']*adoquery1.FieldValues['salenum']);
      Cells[5,arow]:='0';
      Cells[6,arow]:='0';
      Cells[7,arow]:='0';
      adoquery1.Next;
      while ((adoquery1.Fields[0].AsString =Cells[0,arow]) and (not adoquery1.Eof)) do
      begin
        cells[2,arow]:=currtostr(strtocurr(cells[2,arow])+adoquery1.FieldValues['salenum']);
        scost:=adoquery1.FieldValues['costprice']*adoquery1.FieldValues['salenum'];
        cells[3,arow]:=currtostr(strtocurr(cells[3,arow])+scost);
        ssale:=adoquery1.FieldValues['price']*adoquery1.FieldValues['salenum'];
        cells[4,arow]:=currtostr(strtocurr(cells[4,arow])+ssale);
        adoquery1.Next;
      end;
      cells[8,arow]:=currtostr(strtocurr(cells[4,arow])-strtocurr(cells[3,arow]));
    end;
  end;
  adoquery1.Close;
  with stringgrid1 do
  begin
    arow:=0;
    with adoquery2 do
    begin
      while not eof do
      begin
        for i:=1 to RowCount-1 do
        begin
          if cells[0,i] = FieldValues['prid'] then
          begin
            arow:=i;
            break;
          end;  
        end;
        if arow <> 0 then
        begin
          cells[5,arow]:=fieldvalues['salenum'];
          cells[6,arow]:=currtostr(fieldvalues['salenum']*fieldvalues['costprice']);
          cells[7,arow]:=fieldvalues['saletotal'];
          next;
          while ((not eof) and (cells[1,arow] = fieldvalues['prid'])) do
          begin
            scost:=fieldvalues['salenum']*fieldvalues['costprice'];
            cells[6,arow]:=currtostr(strtocurr(cells[6,arow])+scost);
            cells[7,arow]:=currtostr(strtocurr(cells[7,arow])+fieldvalues['saletotal']);
            next;
          end;
          cells[8,arow]:=currtostr(strtocurr(cells[8,arow])+strtocurr(cells[7,arow])-strtocurr(cells[6,arow]));
        end
        else begin
          if Cells[0,RowCount-1] <> '' then
            RowCount:=RowCount+1;
          arow:=RowCount-1;
          cells[0,arow]:=fieldvalues['prid'];
          cells[1,arow]:=fieldvalues['prname'];
          cells[2,arow]:='0';
          cells[3,arow]:='0';
          cells[4,arow]:='0';
          cells[5,arow]:=fieldvalues['salenum'];
          cells[6,arow]:=currtostr(fieldvalues['salenum']*fieldvalues['costprice']);
          cells[7,arow]:=fieldvalues['saletotal'];
          cells[8,arow]:=currtostr(strtocurr(cells[7,arow])-strtocurr(cells[6,arow]))
        end;
      end;
      close;
    end;
  end;
  with stringgrid1 do
  begin
    for i:=1 to RowCount-1 do
    begin
      label10.Caption:=currtostr(strtocurr(label10.Caption)+strtocurr(Cells[4,i]));
      label11.Caption:=currtostr(strtocurr(label11.Caption)+strtocurr(Cells[7,i]));
      label12.Caption:=currtostr(strtocurr(label12.Caption)+strtocurr(Cells[8,i]));
    end;
  end;
end;

procedure Tfrmstatistic.SpeedButton2Click(Sender: TObject);
var
  i:integer;
  mypage:travepage;
  mytext:travetext;
begin
  if stringgrid1.Cells[0,1] <> '' then
  begin
    adoquery1.SQL.Text:='create table tbstatistic (prid char(7) not null unique,'
    +'prname char(20),whosalenum float,whcost currency,whsale currency,'
    +'salenum float,scost currency,sprice currency,grprofit currency)';
    adoquery1.ExecSQL;
    try
      rvproject1.Open;
      with rvproject1.ProjMan do
      begin
        mypage:=findravecomponent('report1.page1',nil) as travepage;
        mytext:=findravecomponent('Text3',mypage) as travetext;
        mytext.Text:=datetostr(datepicker1.Date);
        mytext:=findravecomponent('Text5',mypage) as travetext;
        mytext.Text:=datetostr(datepicker2.Date);
        mytext:=findravecomponent('Text7',mypage) as travetext;
        mytext.Text:=combobox1.Text;
        mytext:=findravecomponent('Text9',mypage) as travetext;
        mytext.Text:=combobox2.Text;
      end;
      with stringgrid1 do
      begin
        for i:=1 to RowCount-1 do
        begin
          adoquery1.SQL.Text:='insert into tbstatistic values ('''+cells[0,i]
          +''','''+cells[1,i]+''','+cells[2,i]+','+cells[3,i]+','+cells[4,i]+','
          +cells[5,i]+','+cells[6,i]+','+cells[7,i]+','+cells[8,i]+')';
          adoquery1.ExecSQL;
        end;
        adoquery1.SQL.Text:='select * from tbstatistic';
        adoquery1.Open;
        rvproject1.Execute
      end;
    finally
      adoquery1.Close;
      rvproject1.Close;
      adoquery1.SQL.Text:='drop table tbstatistic';
      adoquery1.ExecSQL;
    end;
  end
  else
    showmessage('请先查询数据');
end;

end.