monthreport.~pas

delphi制作的图书管理系统

unit MonthReport;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, StdCtrls, Grids, DBGrids, ExtCtrls, DBCtrls,DB,DBTables,StrUtils;

type
  TMonthReportFrm = class(TForm)
    LblQueryYear: TLabel;
    LblFrmTitle: TLabel;
    DBGridBookInfo: TDBGrid;
    BtnClose: TButton;
    EdtMonth: TEdit;
    LblQueryMonth: TLabel;
    BtnCreateReport: TButton;
    EdtYear: TEdit;
    BtnOutputReport: TButton;
    procedure BtnCloseClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
    procedure BtnCreateReportClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure BtnOutputReportClick(Sender: TObject);
  private
      IFExist:Boolean;                       //判断临时表是否存在
      Function CreateMRID:String;           //生成表报号
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MonthReportFrm: TMonthReportFrm;

implementation

uses DataModuel, Main, Depository, RPTMonthReport;

{$R *.dfm}

procedure TMonthReportFrm.BtnCloseClick(Sender: TObject);
begin
  MainForm.Sub_MReport.Enabled:=True;           //主界面的月报表可用
  close;
end;

procedure TMonthReportFrm.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Action:=caFree;
end;


procedure TMonthReportFrm.FormDestroy(Sender: TObject);
begin
  MonthReportFrm:=nil;
end;

//******按相应内容进行查询
procedure TMonthReportFrm.BtnCreateReportClick(Sender: TObject);
Var
  MYear:String;                        //要生成报表的年份
  MMonth:String;                      //要生成报表的月份
  PriorMonth:String;                  //要统计报表的上一个月
  PriorYear:String;
  CurBookID:String;                     //CurBookID,CurQuantity临时存放图书编号和相应的数量变量
  CurQuantity:integer;
  NumMonth:Integer;
  CurMonth:Integer;                     //当前月份
  MRID:String;                         //记录报表号
begin

  DM.PurchaseAccountDSource.DataSet:=Nil;

  MYear:=Trim(EdtYear.Text);
  MMonth:=Trim(EdtMonth.text);

  //***判断所输入的年份和月份***
  if (MYear='') then
  begin
    MessageDlg('年份不能为空!',mtInformation,[mbOK],0);
    exit;
  end;
  if (MMonth='') then
  begin
     MessageDlg('月份不能为空!',mtInformation,[mbOk],0);
     exit;
  end;

  if AnsiCompareText(FormatDateTime('yyyy',Date()),MYear)<0 then
  begin
    MessageDlg('年份输入超出范围!',mtInformation,[mbOK],0);
    Exit;
  end;
  try
    NumMonth:=StrToInt(MMonth);
  except
    MessageDlg('月份输入出错!',mtInformation,[mbOK],0);
    exit;
  end;
  if not ((NumMonth>=1) and (NumMonth<=12)) then
  begin
    MessageDlg('输入月份超出范围!',mtError,[mbOK],0);
    exit;
  end;

  CurMonth:=StrToInt(FormatDateTime('mm',Date()));
  if CurMonth=12 then
     CurMonth:=13;
  if (NumMonth>=CurMonth) then
  begin
    MessageDlg('只能对此月之前的月份进行报表输出!',mtError,[mbOK],0);
    exit;
  end;

  //***对月报表主表进进查询,是否输入的月份的报表是否存在.如存在则直接显示
  with DM.PurchaseQry do
  begin
    close;
    sql.Clear;
    sql.Text:='select MRID from MReportMaster where Year=:Year and Month=:Month';
    params.ParamByName('year').Value:=MYear;
    Params.ParamByName('month').Value:=MMonth;
    open;
  end;

  if DM.PurchaseQry.RecordCount<>0 then
  begin
     MRID:=DM.PurchaseQry.FieldValues['MRID'];

    with DM.PurchaseQry do
    begin
      close;
      sql.Clear;
      sql.Text:='Select BI.BookID,BookName,Author,Press,LMonthHave,TMonthPurchase,TMonthSales,TMonthHave from MReprotSub R,BookInfo BI';
      sql.Add('where (MRID=:MRID) and (BI.BookID=R.BookID)');
      params.ParamByName('MRID').Value:=MRID;
      Open;
    end;
    DM.PurchaseAccountDSource.DataSet:=DM.PurchaseQry;
    exit;
  end;


  //***如月报表不存在,则进行生成
  MRID:='';
  if MMonth='1' then
  begin
     PriorMonth:='12';
     PriorYear:=IntToStr(StrToInt(MYear)-1);
  end
  else
  begin
    PriorMonth:=IntToStr(StrToInt(MMonth)-1);
    PriorYear:=MYear;
  end;


  if IFExist then
    with DM.QryDepository do
    begin
      close;
      SQL.Clear;
      SQL.Text:='Drop Table #Report';
      ExecSql;
    end;


  with DM.PurchaseAccountQry do
  begin
    close;
    SQL.Clear;
    SQL.Text:='Create Table #Report(BookID Varchar(15),LMonthHave int Default 0 ,TMonthPurchase int Default 0,TMonthSales int Default 0,TMonthHave int Default 0)';
    ExecSQL;
    IFExist:=True;
  end;

  with DM.PurchaseQry do
  begin
    close;
    SQL.Clear;
    SQL.Text:='Select * from BookNum';
    open;
  end;

  DM.PurchaseQry.First;
  while not DM.PurchaseQry.Eof do
    begin
    with DM.PurchaseAccountQry do
    begin
      close;
      SQL.Clear;
      SQL.Text:='Insert into #Report(BookID)';
      SQL.Add('Values');
      SQL.Add('(:BookID)');
      Params.ParamByName('BookID').Value:=DM.PurchaseQry.FieldValues['BookID'];
      ExecSQl;
    end;
    DM.PurchaseQry.next;
  end;
  DM.PurchaseQry.Close;

  CurBookID:='';
  CurQuantity:=0;
  with DM.PurchaseAccountQry do
  begin
    close;
    SQL.Clear;
    SQL.Text:='Select BookID,Sum(PurhchaseQuantity) as PTotalNum from PurchaseSub,PurchaseMaster where';
    SQL.Add('(PurchaseSub.PurchaseID=PurchaseMaster.PurchaseID) and');
    SQL.Add('(DatePart(yy,PurchaseDate)=:MYear) and (DatePart(mm,PurchaseDate)=:MMonth)');
    SQL.Add('Group by BookID');
    Params.ParamByName('MYear').Value:=MYear;
    Params.ParamByName('MMonth').Value:=MMonth;
    Open;


    if RecordCount<>0 then
    begin
      First;
      while not Eof do
      begin
        CurBookID:=Fields[0].Value;
        CurQuantity:=Fields[1].Value;
        with DM.PurchaseQry do
        begin
           close;
           SQL.Clear;
           SQL.Text:='Update #Report set TMonthPurchase=:TMonthPurchase';
           SQL.Add('Where BookID=:BookID');
           Params.ParamByName('BookID').Value:=CurBookID;
           Params.ParamByName('TMonthPurchase').Value:=CurQuantity;
           ExecSql;
           close;
        end;
        Next;
      end;
    end;
    close;
  end;

  CurBookID:='';
  CurQuantity:=0;
  with DM.PurchaseAccountQry do
  begin
    close;
    SQL.Clear;
    SQL.Add('Select BookID,Sum(SaleQuantity) as STotalNum from SalesSub,SalesMaster where');
    SQL.Add('(SalesMaster.SaleID=SalesSub.SaleID) and');
    SQL.Add('(DatePart(yy,SaleDate)=:MYear) and (DatePart(mm,SaleDate)=:MMonth)');
    SQL.Add('Group by BookID');
    Params.ParamByName('MYear').Value:=MYear;
    Params.ParamByName('MMonth').Value:=MMonth;
    Open;

    if RecordCount<>0 then
    begin
      First;
      while not Eof do
      begin
        CurBookID:=Fields[0].value;
        CurQuantity:=Fields[1].value;
        with DM.PurchaseQry do
        begin
           close;
           SQL.Clear;
           SQL.Text:='Update #Report set TMonthSales=:TMonthSales';
           SQL.Add('Where BookID=:BookID');
           Params.ParamByName('TMonthSales').Value:=CurQuantity;
           Params.ParamByName('BookID').Value:=CurBookID;
           ExecSql;
           close;
        end;
        Next;
      end;
    end;
    close;
  end;

  CurBookID:='';
  CurQuantity:=0;
  with DM.PurchaseAccountQry do
  begin
    close;
    SQL.Clear;
    SQL.Add('Select BookID,Sum(SaleQuantity) as STotalNum from SalesSub,SalesMaster where');
    SQL.Add('(SalesMaster.SaleID=SalesSub.SaleID) and');
    SQL.Add('(DatePart(yy,SaleDate)=:MYear) and (DatePart(mm,SaleDate)=:MMonth)');
    SQL.Add('Group by BookID');
    Params.ParamByName('MYear').Value:=MYear;
    Params.ParamByName('MMonth').Value:=MMonth;
    Open;
    if RecordCount<>0 then
    begin
      First;
      while not Eof do
      begin
        CurBookID:=Fields[0].value;
        CurQuantity:=Fields[1].value;
        with DM.PurchaseQry do
        begin
           close;
           SQL.Clear;
           SQL.Text:='Update #Report set TMonthSales=:TMonthSales';
           SQL.Add('Where BookID=:BookID');
           Params.ParamByName('TMonthSales').Value:=CurQuantity;
           Params.ParamByName('BookID').Value:=CurBookID;
           ExecSql;
           close;
        end;
        Next;
      end;
    end;
    close;
  end;

  CurBookID:='';
  CurQuantity:=0;
  with DM.PurchaseAccountQry do
  begin
    close;
    SQL.Clear;
    SQL.Add('Select BookID,TMonthHave from MReprotSub,MReportMaster where');
    SQL.Add('(MReprotSub.MRID=MReportMaster.MRID) and');
    SQL.Add('(Year=:MYear) and (Month=:MMonth)');
    Params.ParamByName('MYear').Value:=PriorYear;
    Params.ParamByName('MMonth').Value:=PriorMonth;
    Open;
    if RecordCount<>0 then
    begin
      First;
      while not Eof do
      begin
        CurBookID:=Fields[0].value;
        CurQuantity:=Fields[1].value;
        with DM.PurchaseQry do
        begin
           close;
           SQL.Clear;
           SQL.Text:='Update #Report set LMonthHave=:LMonthHave';
           SQL.Add('Where BookID=:BookID');
           Params.ParamByName('LMonthHave').Value:=CurQuantity;
           Params.ParamByName('BookID').Value:=CurBookID;
           ExecSql;
           close;
        end;
        Next;
      end;
    end;
    close;
  end;

  //计算本月结存
  with DM.PurchaseQry do
    begin
       close;
       SQL.Clear;
       SQL.Text:='Update #Report set TMonthHave=TMonthPurchase+LMonthHave-TMonthSales';
       SQL.Add('Where 1=1');
       ExecSql;
       close;
     end;


  //**查询的信息存放到MReportMaster和MReprotSub中
  MRID:=CreateMRID;
  with DM.PurchaseQry do
  begin
    close;
    SQL.Clear;
    SQL.Text:='Insert Into MReportMaster(MRID,Year,Month)';
    SQL.ADD('Values');
    SQL.Add('(:MRID,:Year,:Month)');
    Params.ParamByName('MRID').Value:=MRID;
    Params.ParamByName('Year').Value:=MYear;
    Params.ParamByName('Month').Value:=MMonth;
    ExecSQL;
  end;

  with DM.PurchaseAccountQry do
  begin
    close;
    SQL.Clear;
    SQL.Text:='SELECT * FROM #Report';
    Open
  end;
  DM.PurchaseAccountQry.First;
  while not DM.PurchaseAccountQry.Eof do
  begin
    with DM.PurchaseQry do
    begin
      close;
      sql.Clear;
      sql.Text:='Insert into MReprotSub(MRID,BookID,LMonthHave,TMonthPurchase,TMonthSales,TMonthHave)';
      sql.Add('Values');
      sql.Add('(:MRID,:BookID,:LMonthHave,:TMonthPurchase,:TMonthSales,:TMonthHave)');
      params.ParamByName('MRID').Value:=MRID;
      params.ParamByName('BookID').Value:=DM.PurchaseAccountQry.FieldValues['BookID'];
      Params.ParamByName('LMonthHave').Value:=DM.PurchaseAccountQry.FieldValues['LMonthHave'];
      Params.ParamByName('TMonthPurchase').Value:=DM.PurchaseAccountQry.FieldValues['TMonthPurchase'];
      Params.ParamByName('TMonthSales').Value:=DM.PurchaseAccountQry.FieldValues['TMonthSales'];
      Params.ParamByName('TMonthHave').Value:=DM.PurchaseAccountQry.FieldValues['TMonthHave'];
      ExecSQL;
    end;
    DM.PurchaseAccountQry.next;
  end;



  //***显示所查询的结果
  With DM.PurchaseAccountQry do
  begin
    close;
    SQL.Clear;
    SQL.Text:='Select BI.BookID,BookName,Author,Press,LMonthHave,TMonthPurchase,TMonthSales,TMonthHave from #Report R,BookInfo BI';
    SQL.Add('Where');
    SQL.Add('BI.BookID=R.BookID');
    Open;
  end;
  DM.PurchaseAccountDSource.DataSet:=DM.PurchaseAccountQry;
end;

procedure TMonthReportFrm.FormCreate(Sender: TObject);
begin
  IFExist:=False;
  DM.PurchaseAccountQry.Active:=False;
  DM.PurchaseQry.Active:=False;
end;

Function TMonthReportFrm.CreateMRID:String;
var
  Perent:TDateTime;
  Year, Month, Day, Hour, Min, Sec,MSec: Word;
begin
  Perent:=Now();
  DecodeDate(Perent,Year,Month,Day);
  DecodeTime(Perent,Hour,Min,Sec,MSec);
  Result:=IntToStr(Year)+IntToStr(Month)+IntToStr(Day)+IntToStr(Hour)+IntToStr(Min);
end;


//**打印所对应年月的报表******
procedure TMonthReportFrm.BtnOutputReportClick(Sender: TObject);
var
  PrtYear:String;
  PrtMonth:String;            //要打印的年月
  PrtFrm:TPrtFrm;             //打印窗体变量
begin
  PrtYear:=Trim(EdtYear.Text);
  PrtMonth:=Trim(EdtMonth.Text);

  //**查询相应年月的信息，以便打印
  with DM.PurchaseQry do
  begin
    close;
    sql.Clear;
    sql.Text:='Select BookInfo.BookID,BookName,Author,Press,LMonthHave,TMonthPurchase,';
    sql.Add('TMonthSales,TMonthHave from (');
    sql.Add('Select BookID,LMonthHave,TMonthPurchase,TMonthSales,TMonthHave from MReprotSub MS,MReportMaster MM where (MS.MRID=MM.MRID) and');
    sql.Add('(MM.Year=:Year) and (MM.Month=:Month)');
    sql.Add(') R,BookInfo  where R.BookID=BookInfo.BookID');
    params.ParamByName('Year').Value:=PrtYear;
    Params.ParamByName('month').Value:=PrtMonth;
    Open;
  end;

  PrtFrm:=TPrtFrm.Create(self);
  PrtFrm.QuickRep1.DataSet:=DM.PurchaseQry;

  PrtFrm.QRLblYearMonth.Caption:='';
  PrtFrm.QRLblYearMonth.Caption:=PrtYear+'年'+PrtMonth+'月';
  PrtFrm.QBookID.DataSet:=DM.PurchaseQry;
  PrtFrm.QBKName.DataSet:=DM.PurchaseQry;
  PrtFrm.QAuthor.DataSet:=DM.PurchaseQry;
  PrtFrm.QPress.DataSet:=DM.PurchaseQry;
  PrtFrm.QLHave.DataSet:=DM.PurchaseQry;
  PrtFrm.QTPur.DataSet:=DM.PurchaseQry;
  PrtFrm.QTSale.DataSet:=DM.PurchaseQry;
  PrtFrm.QTHave.DataSet:=DM.PurchaseQry;

  PrtFrm.QuickRep1.Preview;


end;

end.