fmqryallstocku.pas

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

unit FMQryAllStockU;

interface

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

type
  TFMQryAllStock = class(TFMBaseQry)
    Panel1: TPanel;
    Label4: TLabel;
    adoData: TADOQuery;
    dsData: TDataSource;
    adoClass: TADOQuery;
    dsClass: TDataSource;
    adoTotal: TADOQuery;
    adoTotalfProductId: TSmallintField;
    adoTotalfSHQty: TFloatField;
    adoTotalfTHQty: TFloatField;
    adoTotalfLLQty: TFloatField;
    adoTotalfTLQty: TFloatField;
    adoTotalfDZQty: TFloatField;
    adoTotalfSHAmt: TFloatField;
    adoTotalfTHAmt: TFloatField;
    adoTotalfLLAmt: TFloatField;
    adoTotalfTLAmt: TFloatField;
    adoTotalfDZAmt: TFloatField;
    adoTotalfQty: TFloatField;
    adoTotalfAmt: TFloatField;
    adoTotalfEndQty: TFloatField;
    adoTotalfEndAmt: TFloatField;
    adoTotalfproductno: TWideStringField;
    adoTotalfProductName: TWideStringField;
    adoTotalfSpec: TWideStringField;
    adoTotalfSaleUnit: TWideStringField;
    dsTotal: TDataSource;
    BegDate: TDateTimePicker;
    Label1: TLabel;
    GridQty: TDBGrid;
    GridAmt: TDBGrid;
    dxfClass: TdxDBLookupTreeView;
    procedure FormCreate(Sender: TObject);
    procedure btnQueryClick(Sender: TObject);
    procedure btnExcleClick(Sender: TObject);
    procedure GridQtyDblClick(Sender: TObject);
    procedure GridAmtDblClick(Sender: TObject);
    procedure dxfClassKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
    SQL,strBegDate,strEndDate :string ; 
    procedure ExecSQL(strSQL:string);
    procedure SetBill ;
  public
    { Public declarations }
    aCompany,aUserId :string ;
  end;

var
  FMQryAllStock: TFMQryAllStock;

implementation

{$R *.dfm}

procedure TFMQryAllStock.ExecSQL(strSQL:string);
begin
  QrySQL.Active :=false ;
  QrySQL.SQL.Text :=strSQL;
  QrySQL.ExecSQL ;
end ;

procedure TFMQryAllStock.SetBill ;
begin
  //
  SQL :='delete from tb_biz';
  ExecSQL(SQL);

  //
  SQL :='INSERT INTO tb_biz ( fproductid, fSHQty, fSHAmt ) '
   +' SELECT b.fproductid, sum(b.fqty) AS fqty, sum(b.fqty*b.fbuyprice) AS famt '
   +' FROM ts_receive_head AS a, ts_receive_detail AS b '
   +' WHERE a.fbillid=b.fbillid and a.fState=3 '
   +' and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#'
   +' GROUP BY b.fproductid';
  ExecSQL(SQL);

  //
  SQL :='INSERT INTO tb_biz ( fproductid, fTHQty, fTHAmt ) '
   +' SELECT b.fproductid, sum(b.fqty) AS fqty, sum(b.fqty*b.fbuyprice) AS famt '
   +' FROM ts_return_head AS a, ts_return_detail AS b '
   +' WHERE a.fbillid=b.fbillid and a.fState=3 '
   +' and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#'
   +' GROUP BY b.fproductid';
  ExecSQL(SQL);

  //
  SQL :='INSERT INTO tb_biz ( fproductid, fLLQty, fLLAmt) '
   +' SELECT b.fproductid, sum(b.fqty) AS fqty, sum(b.fqty*b.fbuyprice) AS famt '
   +' FROM ts_sale_head AS a, ts_sale_detail AS b '
   +' WHERE a.fbillid=b.fbillid and a.fState=3 '
   +' and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#'
   +' GROUP BY b.fproductid';
  ExecSQL(SQL);

  //
  SQL :='INSERT INTO tb_biz ( fproductid, fTLQty, fTLAmt ) '
   +' SELECT b.fproductid, sum(b.fqty) AS fqty, sum(b.fqty*b.fbuyprice) AS famt '
   +' FROM ts_salereturn_head AS a, ts_salereturn_detail AS b '
   +' WHERE a.fbillid=b.fbillid and a.fState=3 '
   +' and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#'
   +' GROUP BY b.fproductid';
  ExecSQL(SQL);

  //
  SQL :='INSERT INTO tb_biz ( fproductid, fDZQty, fDZAmt ) '
   +' SELECT b.fproductid, sum(b.fqty) AS fqty, sum(b.fqty*b.fbuyprice) AS famt '
   +' FROM ts_lost_head AS a, ts_lost_detail AS b '
   +' WHERE a.fbillid=b.fbillid and a.fState=3 '
   +' and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#'
   +' GROUP BY b.fproductid';
  ExecSQL(SQL);


  //统计数据
  SQL :='delete from tb_bizTotal';
  ExecSQL(SQL);

  SQL :='insert into tb_bizTotal(fproductid,fSHQty,fSHAmt,fTHQty,fTHAmt,'
   +' fLLQty,fLLAmt,fTLQty,fTLAmt,fDZQty,fDZAmt,'
   +' fQty,fAmt,fEndQty,fEndAmt)'
   +' SELECT fproductid,sum(fSHQty),sum(fSHAmt), sum(fTHQty),sum(fTHAmt),'
   +' sum(fLLQty), sum(fLLAmt), sum(fTLQty), sum(fTLAmt),sum(fDZQty),sum(fDZAmt), '
   +' sum(fQty),sum(fAmt),sum(fQty)+sum(fSHQty)-sum(fTHQty)-sum(fLLQty)+sum(fTLQty)+sum(fDZQty),'
   +' sum(fAmt)+sum(fSHAmt)-sum(fTHAmt)-sum(fLLAmt)+sum(fTLAmt)+sum(fDZAmt)'
   +' FROM tb_biz GROUP BY fproductid ';
  ExecSQL(SQL);

  SQL :='select a.*,b.fproductno,b.fProductName,b.fSpec,b.fSaleUnit from tb_bizTotal a,ts_item b where a.fproductid=b.fproductid ';
  if adoData.FieldByName('fClassNo').AsString <>'' then
     SQL :=SQL+ ' and b.fClass like '+#39+adoData.fieldByName('fClassNo').AsString+'%'+#39 ;
  SQL:=SQL+' order by b.fproductno';

  adoTotal.Active :=false ;
  adoTotal.SQL.Text :=SQL ;
  adoTotal.Active :=true ; 
end ;

procedure TFMQryAllStock.FormCreate(Sender: TObject);
begin
  inherited;
  adoData.Active :=false ;adoData.Active :=true ;
  adoClass.Active :=false ;adoClass.Active :=true ;

  GridQty.Align :=alClient ;
  GridAmt.Align :=alClient ;
end;

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

  Save_Cursor := Screen.Cursor;
  Screen.Cursor := crSQLWait;
  try
     setBill ; 
  finally
     Screen.Cursor := Save_Cursor;
  end ;
end;

procedure TFMQryAllStock.btnExcleClick(Sender: TObject);
begin
  inherited;
  if not adoTotal.Active then exit ;
  if GridQty.DataSource.DataSet.RecordCount =0 then exit ;
  sCCompanyName :=aCompany ;
  if GridQty.Visible then
     SaveExcel('数量帐','统计日期:'+strBegDate,GridQty)
  else
     SaveExcel('金额帐','统计日期:'+strBegDate,GridAmt) ;
end;

procedure TFMQryAllStock.GridQtyDblClick(Sender: TObject);
begin
  inherited;
  GridQty.Visible :=false ;
  GridAmt.Visible :=true ; 
end;

procedure TFMQryAllStock.GridAmtDblClick(Sender: TObject);
begin
  inherited;
  GridAmt.Visible :=false ;
  GridQty.Visible :=true ;
end;

procedure TFMQryAllStock.dxfClassKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  inherited;
  if Key=46 then adoData.FieldByName('fClassNo').AsString :=''; 
end;

end.