stat.pas

文具仓库管理系统2.0（源码）delphi+access main in Mainmain.pas {FrmMain}, Login in LoginLogin.pas {FrmLogin},

unit Stat;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, StdCtrls, Buttons, ExtCtrls, Grids, DBGrids,ADODB,
  FR_Class, FR_DSet, FR_DBSet;

type
  TFrmStat = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    TabSheet3: TTabSheet;
    TabSheet4: TTabSheet;
    TabSheet5: TTabSheet;
    Panel1: TPanel;
    Label1: TLabel;
    DTInStatFrom: TDateTimePicker;
    Label2: TLabel;
    DTInStatTo: TDateTimePicker;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BtnExit: TBitBtn;
    BitBtn4: TBitBtn;
    DBGrid1: TDBGrid;
    Panel2: TPanel;
    Label3: TLabel;
    Label4: TLabel;
    DTOutStatFrom: TDateTimePicker;
    DTOutStatTo: TDateTimePicker;
    BitBtn3: TBitBtn;
    BitBtn5: TBitBtn;
    BitBtn6: TBitBtn;
    BitBtn7: TBitBtn;
    DBGrid2: TDBGrid;
    Panel3: TPanel;
    Label5: TLabel;
    Label6: TLabel;
    DTOutDeptFrom: TDateTimePicker;
    DTOutDeptTo: TDateTimePicker;
    BitBtn8: TBitBtn;
    BitBtn9: TBitBtn;
    BitBtn10: TBitBtn;
    BitBtn11: TBitBtn;
    DBGrid3: TDBGrid;
    Panel4: TPanel;
    Label7: TLabel;
    Label8: TLabel;
    DTOutPersonFrom: TDateTimePicker;
    DTOutPersonTo: TDateTimePicker;
    BitBtn12: TBitBtn;
    BitBtn13: TBitBtn;
    BitBtn14: TBitBtn;
    BitBtn15: TBitBtn;
    DBGrid4: TDBGrid;
    DBGrid5: TDBGrid;
    Panel5: TPanel;
    BitBtn16: TBitBtn;
    BitBtn17: TBitBtn;
    BitBtn18: TBitBtn;
    BitBtn19: TBitBtn;
    Label9: TLabel;
    StatusBar1: TStatusBar;
    Progressbar1: TProgressBar;
    CmbYear: TComboBox;
    Label10: TLabel;
    CmbMonth: TComboBox;
    Label11: TLabel;
    Panel6: TPanel;
    Label12: TLabel;
    Label13: TLabel;
    SaveDialog1: TSaveDialog;
    frDBDataSet1: TfrDBDataSet;
    FrInStat: TfrReport;
    FrOutStat: TfrReport;
    frOutDept: TfrReport;
    frMonthSumup: TfrReport;
    procedure FormCreate(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BtnExitClick(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn8Click(Sender: TObject);
    procedure BitBtn12Click(Sender: TObject);
    procedure BitBtn16Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure BitBtn9Click(Sender: TObject);
    procedure BitBtn13Click(Sender: TObject);
    procedure BitBtn17Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn7Click(Sender: TObject);
    procedure BitBtn11Click(Sender: TObject);
    procedure BitBtn19Click(Sender: TObject);
    procedure CmbYearChange(Sender: TObject);
    procedure TabSheet5Show(Sender: TObject);
  private
    { Private declarations }
    procedure AddToComboboxYearMonth;
    procedure DeleteInStat(sTableNmae:string);
    procedure InsertIntoInStat;
    procedure ShowInStat;
    procedure InsertIntoOutStat;
    procedure ShowOutStat;
    procedure ShowOutStatDept;
    procedure ShowOutStatPerson;
    procedure ShowMonthSumupStat;
    function BOFM(Tdate:TDateTime):TDateTime;  //月初
    function EOFM(Tdate:TDateTime):TDateTime;  //月末
    procedure InsertSumupAmount(sYm,sGoods:string;sLast,sIn,sOut,sCurrent:integer);
    function ReadGoodsLastMonthAmount(sYm,sGoods:string):integer;
    function ReadGoodsInAmount(sYm,sGoods:string):integer;
    function ReadGoodsOutAmount(sYm,sGoods:string):integer;
    function LastMonthStr(sYm:string):string;
    procedure StatMonthNumUp(sYm:string);
    function IsExistMonthStat(sYm:string):boolean;
    procedure DeleteExistMonthStat(sYm:string);
  public
    { Public declarations }
  end;

var
  FrmStat: TFrmStat;

implementation

uses global,dm, Logo, comobj;

{$R *.dfm}

procedure TFrmStat.FormCreate(Sender: TObject);
begin
self.Caption :=SFormStat;
//将进度条设置到状态栏里面
ProgressBar1.Parent:= StatusBar1;
//设置进度条位置
ProgressBar1.SetBounds(0,0,StatusBar1.Width,StatusBar1.Height);
//将所以统计页的日期默认设置为本月的第一天后最后一天
DTInStatFrom.Date :=BOFM(now);
DTInStatTo.Date :=EOFM(now);
DTOutStatFrom.Date :=DTInStatFrom.Date;
DTOutStatTo.Date :=DTInStatTo.Date;
DTOutDeptFrom.Date :=DTInStatFrom.Date;
DTOutDeptTo.Date :=DTInStatTo.Date;
DTOutPersonFrom.Date :=DTInStatFrom.Date;
DTOutPersonTo.Date:=DTInStatTo.Date;
AddToComboboxYearMonth;  //向月结存统计中增加年月
with FrmLogo do
  begin
    LMsg1.Caption :=sMsgLoadInfo5;
    LMsg2.Caption :=sMsgLoadInfo5;
    LMsg1.Update;
    LMsg2.Update;
  end;
//如果上月的月结存统计不存在则自动进行统计，
if not IsExistMonthStat(LastMonthStr(CmbYear.Text +'年'+CmbMonth.Text +'月')) then
  StatMonthNumUp(LastMonthStr(CmbYear.Text +'年'+CmbMonth.Text +'月'));
ShowMonthSumupStat;//显示己有的月结存数据
end;

procedure TFrmStat.ShowInStat;
begin
with DM_Wjckgl.ADOQryInStat do
  begin
    close;
    sql.Text :=sSQLShowStatIn;
    open;
  end;
end;

procedure TFrmStat.BitBtn1Click(Sender: TObject);
begin
DeleteInStat(STInStockStat);   //删除原有的统计数据
InsertIntoInStat;   //进统计数据插出到
ShowInStat;     //显示统计的数据
end;

procedure TFrmStat.BtnExitClick(Sender: TObject);
begin
close;
end;

procedure TFrmStat.ShowOutStat;
begin
with DM_Wjckgl.ADOQryOutStat do
  begin
    close;
    sql.Text := sSQLShowStatOut;
    open;
  end;
end;

procedure TFrmStat.BitBtn3Click(Sender: TObject);
begin
DeleteInStat(STOutStockStat);
InsertIntoOutStat;
ShowOutStat;
end;

procedure TFrmStat.ShowOutStatDept;
begin
with DM_Wjckgl.ADOQryOutDept do
  begin
    close;
    sql.Text :=format(sSQLStatOutDept,[DateToStr(DTOutDeptFrom.Date),DateToStr(DTOutDeptTo.Date)]);
    open;
  end;
end;

procedure TFrmStat.BitBtn8Click(Sender: TObject);
begin
ShowOutStatDept;
end;

procedure TFrmStat.ShowOutStatPerson;
begin
with DM_Wjckgl.ADOQryOutPerson do
  begin
    close;
    sql.Text :=format(sSQLStatOutPerson,[DateToStr(DTOutPersonFrom.Date),DateToStr(DTOutPersonTo.Date)]);
    open;
  end;
end;

procedure TFrmStat.BitBtn12Click(Sender: TObject);
begin
ShowOutStatPerson;
end;

procedure TFrmStat.InsertIntoInStat;
begin
with DM_Wjckgl.ADOQry do
  begin
    close;
    sql.Text :=format(sSQLInsertStatIn,[DateToStr(DTInStatFrom.Date),DateToStr(DTInStatTo.Date)]);
    execsql;
  end;
end;

procedure TFrmStat.DeleteInStat(sTableNmae:string);
begin
with DM_Wjckgl.ADOQry do
  begin
    close;
    sql.Text :=format(SSQLDeleteDataTable,[sTableNmae]);
    execsql;
  end;
end;

procedure TFrmStat.InsertIntoOutStat;
begin
with DM_Wjckgl.ADOQry do
  begin
    close;
    sql.Text :=format(sSQLInsertStatOut,[DateToStr(DTOutStatFrom.Date),DateToStr(DTOutStatTo.Date)]);
    execsql;
  end;
end;

function TFrmStat.BOFM(Tdate: TDateTime): TDateTime;
var
	wYear,wMonth,wDay:word;
begin
  DecodeDate(tDate, wYear, wMonth, wDay);
  Result := EncodeDate(wYear, wMonth, 1);
end;

function TFrmStat.EOFM(Tdate: TDateTime): TDateTime;
var
	wYear,wMonth,wDay:word;
begin
	DecodeDate(tDate, wYear, wMonth, wDay);
  Result := EncodeDate(wYear, wMonth, MonthDays[IsLeapYear(wYear), wMonth]);
end;

procedure TFrmStat.AddToComboboxYearMonth;
var
  i:integer;
  y,m,d:word;
begin
CmbYear.Clear;
CmbMonth.Clear;
DecodeDate(now,y,m,d);  //将日期打散
for i:=1996 to 2020 do
  CmbYear.Items.Add(IntTostr(i));
for i:=1 to 12 do
  CmbMonth.Items.Add(IntTostr(i));
//将两个 Combobox 值设置为 当前年月
CmbYear.ItemIndex := CmbYear.Items.IndexOf(IntToStr(y));
CmbMonth.ItemIndex :=CmbMonth.Items.IndexOf(IntToStr(m)); 
end;

procedure TFrmStat.InsertSumupAmount(sYm,sGoods:string;sLast,sIn,sOut,sCurrent:integer);
begin
with DM_Wjckgl.ADOQry2 do
  begin
    close;
    sql.Text :='insert into 月结存表'+
               '(年月,物品名称,上月结存,本月进仓数,本月出仓数,本月结存) '+
               'Values(:pYm,:pGoods,:pLast,:pIn,:pOut,:pCurrent)';
    parameters.ParamByName('pYm').Value :=sYm;
    parameters.ParamByName('pGoods').Value :=sGoods;
    parameters.ParamByName('pLast').Value :=sLast;
    parameters.ParamByName('pIn').Value :=sIn;
    parameters.ParamByName('pOut').Value :=sOut;
    parameters.ParamByName('pCurrent').Value :=sCurrent;
    execsql;
  end;
end;

function TFrmStat.ReadGoodsLastMonthAmount(sYm, sGoods: string): integer;
begin
With DM_wjckgl.ADOQry1 do
  begin
    close;
    sql.Text :='select 年月,物品名称,本月结存 '+
               'from 月结存表 '+
               'where 年月=:pYm and 物品名称=:pGoods';
    parameters.ParamByName('pYm').Value :=LastMonthStr(sYm);
    parameters.ParamByName('pGoods').Value :=sGoods;
    open;
    if RecordCount=0 then
      Result:=0
      else
      Result:=FieldValues['本月结存'];
    close;
  end;
end;

function TFrmStat.ReadGoodsInAmount(sYm, sGoods: string): integer;
begin
With DM_wjckgl.ADOQry1 do
  begin
    close;
    sql.Text :='select 年月,物品名称,数量 '+
               'from 进仓统计 '+
               'where 年月=:pYm and 物品名称=:pGoods';
    parameters.ParamByName('pYm').Value :=sYm;
    parameters.ParamByName('pGoods').Value :=sGoods;
    open;
    if RecordCount=0 then
      Result:=0
      else
      Result:=FieldValues['数量'];
    close;
  end;
end;

function TFrmStat.LastMonthStr(sYm: string): string;
var
  sY,sM:string;
begin
  sY:=copy(sYm,1,4);
  sM:=copy(sYm,7,Length(sYm)-8);
  if sm='1' then
    begin
      sY:=IntToStr(StrToInt(sY)-1);
      sM:='12';
    end
    else
      sM:=IntToStr(StrToInt(Sm)-1);
  Result:=sY+'年'+sM+'月';
end;

function TFrmStat.ReadGoodsOutAmount(sYm, sGoods: string): integer;
begin
With DM_wjckgl.ADOQry1 do
  begin
    close;
    sql.Text :='select 年月,物品名称,数量 '+
               'from 出仓统计 '+
               'where 年月=:pYm and 物品名称=:pGoods';
    parameters.ParamByName('pYm').Value :=sYm;
    parameters.ParamByName('pGoods').Value :=sGoods;
    open;
    if RecordCount=0 then
      Result:=0
      else
      Result:=FieldValues['数量'];
    close;
  end;
end;

procedure TFrmStat.BitBtn16Click(Sender: TObject);
var
  sYm:string;
begin
sYm:=CmbYear.Text +'年'+CmbMonth.Text +'月';
if IsExistMonthStat(sYm) then
  if Msgbox(format(sMsgStatIsExist,[sYm,sYm]),sTitleConfirm,3)=IDNo then
    exit
    else
    DeleteExistMonthStat(sYm);
//做月统计表时，数据是从 进/出仓统计表 中来的，所以先对 进/出仓数据进行统计
DTInStatFrom.Date :=BOFM(StrToDate(CmbYear.Text+'-'+CmbMonth.Text+'-1'));
DTInStatTo.Date :=EOFM(StrToDate(CmbYear.Text+'-'+CmbMonth.Text+'-1'));
DTOutStatFrom.Date :=DTInStatFrom.Date;
DTOutStatTo.Date :=DTInStatTo.Date;
BitBtn1Click(Sender);  //进仓统计
BitBtn3Click(Sender);  //出仓统计
StatMonthNumUp(sYm);
//重表打开数据表
with DM_Wjckgl.ADOQryMonthSumup do
  begin
    close;
    open;
  end;
end;

procedure TFrmStat.ShowMonthSumupStat;
begin
with DM_Wjckgl.ADOQryMonthSumup do
  begin
    close;
    sql.Text :='select 年月,物品种类,物品名称.物品名称,月结存表.物品名称,规格,单位,单价,'+
               '上月结存,本月进仓数,本月出仓数,本月结存 '+
               'from 物品名称,月结存表 '+
               'where 物品名称.物品名称=月结存表.物品名称 '+
               'order by 年月';
    open;
  end;
end;

procedure TFrmStat.StatMonthNumUp(sYm:string);
var
  sGoods:string;
  iCurrent,iLast,iIn,iOut:integer;
begin
sGoods:='';
Progressbar1.Min :=0;
Progressbar1.Position :=0;
with DM_Wjckgl.ADOQry do
  begin
    close;
    sql.Text :='select 物品名称,根编号 from 物品名称 '+
               'where 根编号<>-1 order by 物品名称' ;
    open;
    first;
    Progressbar1.Max :=RecordCount ;
    while not eof do
      begin
        if FieldValues['物品名称']<> null then
          begin
            sGoods:=FieldValues['物品名称'];
            iLast:=ReadGoodsLastMonthAmount(sYm,sGoods);
            iIn:=ReadGoodsInAmount(sYm,sGoods);
            iOut:=ReadGoodsOutAmount(sYm,sGoods);
            iCurrent:=iLast+iIn-iOut;
            InsertSumupAmount(sYm,sGoods,iLast,iIn,iOut,iCurrent);
          end;
        next;
        Progressbar1.StepBy(1);
      end;
  end;
  Progressbar1.Position:=0; 
end;


procedure TFrmStat.BitBtn2Click(Sender: TObject);
var
  fn:string;
  i,j:integer;
  xls:OleVariant;
begin
  if SaveDialog1.Execute then
    fn:=SaveDialog1.FileName;
  if trim(fn)='' then exit;
  if (Dm_Wjckgl.ADOQryInStat.Active =false) or
     (DM_Wjckgl.ADOQryInStat.RecordCount =0) then