fmqryreceiveu.pas

小型库存管理,希望有帮助,小型库存管理,希望有帮助

unit FMQryReceiveU;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, FMBaseQryU, ImgList, DB, ADODB, ComCtrls, ToolWin, ExtCtrls,
  Grids, DBGrids, StdCtrls, DBCtrls, Provider, DBClient;

type
  TFMQryReceive = class(TFMBaseQry)
    Panel1: TPanel;
    DataGrid: TDBGrid;
    Label1: TLabel;
    Label2: TLabel;
    BegDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    Label3: TLabel;
    Label4: TLabel;
    DBfSupNo: TDBLookupComboBox;
    DBfClassNo: TDBLookupComboBox;
    adoData: TADOQuery;
    adoSupplier: TADOQuery;
    dsData: TDataSource;
    dsSupplier: TDataSource;
    adoClass: TADOQuery;
    dsClass: TDataSource;
    cdsData: TADODataSet;
    dbClient: TDataSource;
    cdsDatafproductno: TWideStringField;
    cdsDatafproductname: TWideStringField;
    cdsDatafSpec: TWideStringField;
    cdsDatafSaleunit: TWideStringField;
    cdsDatafqty: TFloatField;
    cdsDatafamt: TFloatField;
    cdsDatafbuyprice: TFloatField;
    procedure FormCreate(Sender: TObject);
    procedure btnQueryClick(Sender: TObject);
    procedure btnExcleClick(Sender: TObject);
  private
    { Private declarations }
    strBegDate,strEndDate :string ;
  public
    { Public declarations }
    aCompany,aUserId :string ;
  end;

var
  FMQryReceive: TFMQryReceive;

implementation

{$R *.dfm}

procedure TFMQryReceive.FormCreate(Sender: TObject);
begin
  inherited;
  BegDate.Date :=StrToDate(FormatDateTime('YYYY'+DateSeparator+'MM',Date())+DateSeparator+'01');
  EndDate.Date :=Date();

  adoData.Active :=false ;adoData.Active :=true ;
  adoSupplier.Active :=false ;adoSupplier.Active :=true ;
  adoClass.Active :=false ;adoClass.Active :=true ;
end;

procedure TFMQryReceive.btnQueryClick(Sender: TObject);
var
  SQL :string ;
  Save_Cursor:TCursor;
begin
  inherited;
  strBegDate :=UFormatDate(DateToStr(BegDate.Date));
  strEndDate :=UFormatDate(DateToStr(EndDate.Date));

  SQL:='SELECT max(c.fproductno) AS fproductno, max(c.fproductname) AS fproductname, max(c.fspec) AS fSpec, max(c.fsaleunit) AS fSaleunit,'
   +' sum(b.fqty) AS fqty, sum(b.fqty*b.fbuyprice) AS famt,sum(b.fqty*b.fbuyprice)/sum(b.fqty) as fbuyprice '
   +' FROM ts_receive_head AS a, ts_receive_detail AS b, ts_item AS c '
   +' WHERE a.fbillid=b.fbillid and b.fproductid=c.fproductid '
   +' and a.fState=3 and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#' ;

  if adoData.FieldByName('fSupNo').AsString <>'' then
     SQL :=SQL+ ' and a.fSupNo='+#39+adoData.fieldByName('fSupNo').AsString+#39 ;
  if adoData.FieldByName('fClassNo').AsString <>'' then
     SQL :=SQL+ ' and c.fClass like '+#39+adoData.fieldByName('fClassNo').AsString+'%'+#39 ;
  SQL :=SQL +' GROUP BY b.fproductid ';

  Save_Cursor := Screen.Cursor;
  Screen.Cursor := crSQLWait;
  try
   cdsData.Active :=false ;
   cdsData.CommandText :=SQL ;
   cdsData.Active :=true ;
  finally
     Screen.Cursor := Save_Cursor;
  end ;
end;

procedure TFMQryReceive.btnExcleClick(Sender: TObject);
begin
  inherited;
  if not cdsData.Active then exit ;
  if DataGrid.DataSource.DataSet.RecordCount =0 then exit ;
  sCCompanyName :=aCompany ;
  SaveExcel('验收统计报表','统计日期:'+strBegDate+'-'+strEndDate,DataGrid);
end;

end.