fmqrymonthu.pas

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

unit FMQryMonthU;

interface

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

type
  TFMQryMonth = class(TFMBaseQry)
    Panel1: TPanel;
    Notebook: TNotebook;
    ImfoMemo: TMemo;
    Label1: TLabel;
    BegDate: TDateTimePicker;
    Label2: TLabel;
    EndDate: TDateTimePicker;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    GridBill: TDBGrid;
    adoBill: TADOQuery;
    dsBill: TDataSource;
    dsTotal: 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;
    PageControl: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    GridAmt: TDBGrid;
    GridQty: TDBGrid;
    procedure btnQueryClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btnExpClick(Sender: TObject);
    procedure btnExcleClick(Sender: TObject);
  private
    { Private declarations }
    strId,strType :integer ; 
    strBegDate ,strEndDate,SQL :string ;
    procedure GetBill ;//取未审核单据
    procedure GetMontId ; //取当前月结的ID，日期范围
    procedure SetBill ;//将各业务中数据加入
    procedure ExecSQL(strSQL:string);
  public
    { Public declarations }
    aCompany,aUserId :string ;
  end;

var
  FMQryMonth: TFMQryMonth;

implementation

{$R *.dfm}

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

procedure TFMQryMonth.GetMontId ;
begin
  QrySQL.Active :=false ;
  QrySQL.SQL.Text :='select count(*),max(fid) from tb_month';
  QrySQL.Open ;

  if QrySQL.Fields[0].AsInteger=0 then
   begin
     ImfoMemo.Lines.Add('没有发现期初开帐单，请先确认期初开帐单已经审核才能进行月结');
     MessageBox(handle,'没有发现期初开帐单，请先确认期初开帐单已经审核才能进行月结.','提示',MB_OK+MB_ICONWARNING);
     btnQuery.Enabled :=false ;
     exit ;
   end ;

  //如果序号最大的　 
  strId :=QrySQL.Fields[1].AsInteger ;

  QrySQL.Active :=false ;
  QrySQL.SQL.Text :='select fBegDate,fEndDate,fState from tb_month where fId='+IntToStr(strId);
  QrySQL.Open ;

  if QrySQL.Fields[2].AsInteger=1 then  //未审核
   begin
     BegDate.Date :=QrySQL.Fields[0].AsDateTime ;
     strType :=0 ;   //仍然用这个月的期初做下月的期初
   end
  else                                  //已审核
   begin
     BegDate.DateTime :=QrySQL.Fields[1].AsDateTime + 1 ;
     strId :=strId + 1 ;
     strType :=1 ;  //这个月的期末做为下月的期初
   end ; 

end ;


procedure TFMQryMonth.GetBill ;
begin
  //检查统计日期内未审核的单据
  SQL:='delete from tb_Bill';
  ExecSQL(SQL) ;

  //
  SQL:='INSERT INTO tb_Bill( fBillNo, fAccDate, fType )'
   +' SELECT a.fBillNo,a.fAccDate,''进货验收'''
   +' FROM ts_receive_head AS a '
   +' WHERE a.fState=1 and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#'
   +' order by fBillNo ' ;
  ExecSQL(SQL);

  //
  SQL:='INSERT INTO tb_Bill( fBillNo, fAccDate, fType )'
   +' SELECT a.fBillNo,a.fAccDate,''退货验收'''
   +' FROM ts_return_head AS a '
   +' WHERE a.fState=1 and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#'
   +' order by fBillNo ' ;
  ExecSQL(SQL);

    //
  SQL:='INSERT INTO tb_Bill( fBillNo, fAccDate, fType )'
   +' SELECT a.fBillNo,a.fAccDate,''领料'''
   +' FROM ts_sale_head AS a '
   +' WHERE a.fState=1 and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#'
   +' order by fBillNo ' ;
  ExecSQL(SQL);

    //
  SQL:='INSERT INTO tb_Bill( fBillNo, fAccDate, fType )'
   +' SELECT a.fBillNo,a.fAccDate,''退料'''
   +' FROM ts_salereturn_head AS a '
   +' WHERE a.fState=1 and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#'
   +' order by fBillNo ' ;
  ExecSQL(SQL);

    //
  SQL:='INSERT INTO tb_Bill( fBillNo, fAccDate, fType )'
   +' SELECT a.fBillNo,a.fAccDate,''调整'''
   +' FROM ts_lost_head AS a '
   +' WHERE a.fState=1 and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#'
   +' order by fBillNo ' ;
  ExecSQL(SQL);

  SQL:='INSERT INTO tb_Bill( fBillNo, fAccDate, fType )'
   +' SELECT a.fBillNo,a.fAccDate,''盘点'''
   +' FROM ts_checkstock_head AS a '
   +' WHERE a.fState=1 and a.fAccDate between #'+strBegDate+'# and #'+strEndDate+'#'
   +' order by fBillNo ' ;
  ExecSQL(SQL);

  //
  adoBill.Active :=false ;
  adoBill.SQL.Text :='select * from tb_Bill order by fBillno,fType';
  adoBill.Active :=true ;
end ;

procedure TFMQryMonth.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);

  //期初信息
  if strType=0 then
     SQL :='INSERT INTO tb_biz ( fproductid, fQty, fAmt ) '
      +' SELECT fproductid, fqty, famt '
      +' FROM tb_month_detail where fid='+IntToStr(strId)
  else
     SQL :='INSERT INTO tb_biz ( fproductid, fQty, fAmt ) '
      +' SELECT fproductid, fEndQty, fEndAmt '
      +' FROM tb_month_detail where fid='+IntToStr(strId-1) ;
  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);

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

procedure TFMQryMonth.btnQueryClick(Sender: TObject);
begin
  inherited;
  ImfoMemo.Lines.Clear ; 
  if BegDate.Date > EndDate.Date  then
   begin
     MessageBox(handle,'统计日期范围不正确.','提示',MB_OK+MB_ICONWARNING);
     exit ;
   end ;
  strBegDate :=UFormatDate(DateToStr(BegDate.Date));
  strEndDate :=UFormatDate(DateToStr(EndDate.Date));

  GetBill ;

  if adoBill.RecordCount >0 then
   begin
     Notebook.PageIndex :=1 ; 
     ImfoMemo.Lines.Add('请处理以上未审核的单据.');
     exit ;
   end  ;

   SetBill ;

  if adoTotal.RecordCount>0 then
   begin
     Notebook.PageIndex :=2 ;
     btnExp.Enabled :=true ;
   end
  else
    ImfoMemo.Lines.Add('注意:在此日期范围内未发生业务.'); 
end;

procedure TFMQryMonth.FormCreate(Sender: TObject);
begin
  inherited;
  GetMontId ;
end;


{记账}
procedure TFMQryMonth.btnExpClick(Sender: TObject);
var
  Save_Cursor:TCursor;
begin
  inherited;
  if messagebox(handle,'确定记账吗?','确认',MB_OKCANCEL+MB_ICONINFORMATION)<>IDOK then exit;
  btnExp.Enabled :=false ;
  
  Save_Cursor := Screen.Cursor;
  Screen.Cursor := crSQLWait;

  SQL :='delete from tb_month_detail where fId='+IntToStr(strId) ;
  ExecSQL(SQL) ;

  //
  QrySQL.Active :=false ;
  QrySQL.SQL.Text :='insert into tb_month_detail(fid,fProductid,fSHQty,fTHQty,fLLQty,'
   +' fTLQty,fDZQty,fSHAmt,fTHAmt,fLLAmt,fTLAmt,'
   +' fDZAmt,fQty,fAmt,fEndQty,fEndAmt)'
   +' select '+IntToStr(strId)+',fProductid,fSHQty,fTHQty,fLLQty, '
   +' fTLQty,fDZQty,fSHAmt,fTHAmt,fLLAmt,fTLAmt,'
   +' fDZAmt,fQty,fAmt,fEndQty,fEndAmt '
    +' from tb_bizTotal ' ;
  QrySQL.ExecSQL ;

  SQL :='delete from tb_month where fId='+IntToStr(strId) ;
  ExecSQL(SQL) ;

  //
  QrySQL.Active :=false ;
  QrySQL.SQL.Text :='insert into tb_month(fid,fBegDate,fEndDate,fCreUser,fState,fMemo,fFFU)'
   +' values('+IntToStr(strId)+','+#39+strBegDate+#39+','+#39+strEndDate+#39+','+#39+aUserId+#39+',3,0,0)';
  QrySQL.ExecSQL ;


  Screen.Cursor := Save_Cursor;
  MessageBox(handle,'记账完毕.','提示',MB_OK+MB_ICONINFORMATION);
end;

{导出}
procedure TFMQryMonth.btnExcleClick(Sender: TObject);
begin
  inherited;
  if Notebook.PageIndex<2 then exit  ;
  sCCompanyName :=aCompany ;
  if PageControl.ActivePageIndex =0 then
     SaveExcel('数量帐','统计日期:'+strBegDate+'-'+strEndDate,GridQty)
  else
     SaveExcel('金额帐','统计日期:'+strBegDate+'-'+strEndDate,GridAmt) ;
end;

end.