stnewstockrep.pas

详细的ERP设计资料

    +' [标准单位] [出库包装单位] , '
    +' sum(isnull([出库标准数量],0)) [出库包装数量] , '
    +' sum(0.00) [出库包装单价]  , '
    +' sum(isnull([入库标准数量],0)) [入库标准数量] , '
    +' sum(0.00) [入库标准单价]  ,'
    +' sum(isnull([入库金额],0)) [入库金额] , '
    +' [标准单位] [入库包装单位] , '
    +' sum(isnull([入库标准数量],0)) [入库包装数量] , '
    +' sum(0.00)  [入库包装单价]   '
    +' from #ExpenseList '
    +' where [日期] <'+Quotedstr(Datetostr(DateTimePicker1.Date))
    +' Group by [商品名称], [商品类别],[标准单位]' ;
  ADOQuery.ExecSQL ;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Insert into  #ExpenseList0 '
    +' ( [商品名称],[日期],[编号],[业务类别],[商品类别] )'
    +' select Distinct [商品名称],  '
    + Quotedstr(Datetostr(DateTimePicker1.Date-1))
    +' as [日期],'+Quotedstr('----')+' , '+Quotedstr('期初结转')
    +' , [商品类别]'
    +' from #ExpenseList '
    +' where [商品名称] not in ( select distinct [商品名称] from #ExpenseList0  '
    +' where [业务类别]='+ Quotedstr('期初结转')+' ) '    ;
  ADOQuery.ExecSQL ;


  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
    +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ExpenseList')
    +' )) DROP TABLE #ExpenseList' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select * into #ExpenseList from #ExpenseList0'
    +' where [日期] is not null '
    +' order by [日期]  ' ;
  ADOQuery.ExecSQL;
  if Button1.Tag=1 then Button1Click(sender)
    else Button2Click(sender);

end;

procedure TSTNewStockRepForm.ToolButton1Click(Sender: TObject);
var BalanceAmount,BalanceQuantity:real;
    I:Integer;
    FieldStr :string;
begin
//  Exit;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select * from #ExpenseList where [业务类别]='+Quotedstr('期初结转');
  ADOQuery.Open;
  if ADOQuery.IsEmpty then
  begin
    ShowMessage('请在“高级..”设置对帐日期，“确定”后，再执行此功能!');
//    Button4.OnClick(sneder);
    Exit;
  end;
  I :=0;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select distinct [商品名称] from #ExpenseList ' + WhereStr;
  ADOQuery.Open;
  ADOQuery.First;
  while not ADOQuery.Eof do
  begin
    ADOQuery2.Close;
    ADOQuery2.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ClientAccountList')
        +' )) DROP TABLE #ClientAccountList' ;
    ADOQuery2.ExecSQL;
    ADOQuery2.Close;
    ADOQuery2.SQL.Text :=' select *  into  #ClientAccountList  '
      +' from #ExpenseList where 1=2 '   ;
    ADOQuery2.ExecSQL;
    ADOQuery2.Close;
    ADOQuery2.SQL.Text :=' select * from  #ClientAccountList ';
    ADOQuery2.open;
    FieldStr :='';
    for I:=0 to ADOQuery2.FieldCount-1 do
    begin
      FieldStr :=Trim(FieldStr)+','+Trim(ADOQuery2.Fields[I].FieldName);
    end;
    FieldStr :=Copy(Trim(FieldStr),2,length(Trim(FieldStr))-1);

    ADOQuery2.Close;
    ADOQuery2.SQL.Text :=' Truncate Table #ClientAccountList ';
    ADOQuery2.ExecSQL;

    ADOQuery2.Close;
    ADOQuery2.SQL.Text :=' ALTER TABLE  #ClientAccountList ADD [ID] '
      +' [int] IDENTITY (1, 1) NOT NULL , '
      +' [结存数量] float NULL ,[结存单价] float NULL,[结存金额] float NULL ';
    ADOQuery2.ExecSQL;
    ADOQuery2.Close;
    ADOQuery2.SQL.Text :=' Insert Into #ClientAccountList ( '+ FieldStr
      +' ) select '+  FieldStr +' from #ExpenseList where [商品名称]='
      +Quotedstr(Trim(ADOQuery.fieldbyname('商品名称').AsString))
      +' order by [日期]';
    ADOQuery2.ExecSQL;
    ADOQuery2.Close;
    ADOQuery2.SQL.Text :=' select * from  #ClientAccountList order by [日期],[ID]';
    ADOQuery2.open;
    BalanceQuantity :=0;
    BalanceAmount :=0;
    while not ADOQuery2.Eof do
    begin
      BalanceQuantity :=BalanceQuantity
        +ADOQuery2.FieldByName('入库标准数量').AsFloat-ADOQuery2.FieldByName('出库标准数量').AsFloat ;
      BalanceAmount :=BalanceAmount
        +ADOQuery2.FieldByName('入库金额').AsFloat-ADOQuery2.FieldByName('出库金额').AsFloat ;
      ADOQuery2.Edit;
      ADOQuery2.FieldByName('结存数量').AsFloat :=BalanceQuantity;
      ADOQuery2.FieldByName('结存金额').AsFloat :=BalanceAmount;
//      ADOQuery2.Edit;
      ADOQuery2.FieldByName('结存单价').AsFloat :=
      ADOQuery2.FieldByName('结存金额').AsFloat/ADOQuery2.FieldByName('结存数量').AsFloat;
      ADOQuery2.Next;

    end;
    AccountListQry.Close;
    AccountListQry.SQL.Text :=' Alter Table #ClientAccountList DROP COLUMN  [ID] ';
    AccountListQry.ExecSQL;

//    AccountListQry.Close;
//    AccountListQry.SQL.Text :=' select *   '
//      +' from  #ClientAccountList order by [日期] ';
//    AccountListQry.open;
//    DataSource1.DataSet := AccountListQry;
//    DBGrid1.DataSource :=  DataSource1;
//    DBGrid1.Visible :=True;
//    ShowMessage('请查看--'+ADOQuery.FieldByName('商品名称').AsString+' --对帐流水结果!');
    adsMaster.Close;
    adsMaster.CommandText :='select * from #ClientAccountList';
    adsMaster.Open;
    UpdateDBGrid;
    DBGrid.hint :='当前商品:'+Trim(ADOQuery.fieldbyname('商品名称').AsString)+'明细帐';

    //在此循环打印对帐单结果，完成后可以将 DBGrid1，DataSource1删除
    {
    with TQLDBGridReportBuilder.Create(Self) do
    try
      DBGrid := DBGrid1;
      AutoWidth := True;
      Report := TSLCheckReckoningReport.Create(Self);
      TSLCheckReckoningReport(Report).qrdbtClientName.DataSet := ADOQuery;
      TSLCheckReckoningReport(Report).qrlCheckCourse.Caption :=
        '对帐期间: ' + DateToStr(DateTimePicker1.Date) + ' ~ ' +
        DateToStr(DateTimePicker2.Date);
      SummaryFields.Add('销售金额=SUM([销售金额])');
      SummaryFields.Add('回款金额=SUM([回款金额])');
      SummaryFields.Add('余额='+floattostr(BalanceF));
      AutoOrientation := False;
      Active := True;
    if I<1 then  Report.PreviewModal
      else  Report.Print;
    finally
      Free;
    end;           }
    ADOQuery.Next;
    I :=I+1;
//    DBGrid1.Visible :=False;
  end;

end;

procedure TSTNewStockRepForm.adsMasterAfterOpen(DataSet: TDataSet);
begin
  inherited;
  RefreshAvailableFields;
end;

procedure TSTNewStockRepForm.DeadStockActExecute(Sender: TObject);
var RateStr:string;
  RateStrF :Integer;
begin
  RateStr :='30';
  if InputQuery('呆滞周期', '请输入周期天数:', RateStr) then
    while not TryStrToint(RateStr,RateStrF) do
     if not InputQuery('呆滞周期', '请输周期天数:', RateStr) then exit;
  if RateStrF=0 then exit;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
      +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#DeadStockList')
      +' )) DROP TABLE #DeadStockList' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select [商品名称],'
    +' [结存标准数量] [结存数量],[结存金额] ,'
    +' [结存标准数量] ['+inttostr(RateStrF*1)+'天呆滞数量],'
    +' [结存金额] ['+inttostr(RateStrF*1)+'天呆滞金额],'
    +' [结存标准数量] ['+inttostr(RateStrF*2)+'天呆滞数量],'
    +' [结存金额] ['+inttostr(RateStrF*2)+'天呆滞金额],'
    +' [结存标准数量] ['+inttostr(RateStrF*3)+'天呆滞数量],'
    +' [结存金额] ['+inttostr(RateStrF*3)+'天呆滞金额],'
    +' [结存标准数量] ['+inttostr(RateStrF*4)+'天呆滞数量],'
    +' [结存金额] ['+inttostr(RateStrF*4)+'天呆滞金额],'
    +' [结存标准数量] ['+inttostr(RateStrF*5)+'天呆滞数量],'
    +' [结存金额] ['+inttostr(RateStrF*5)+'天呆滞金额],'
    +' [结存标准数量] ['+inttostr(RateStrF*6)+'天呆滞数量],'
    +' [结存金额] ['+inttostr(RateStrF*6)+'天呆滞金额] '
    +' into #DeadStockList from #StockListTtl '
    +' where Isnull([结存标准数量],0)+Isnull([结存金额],0)<>0 ' ;
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #DeadStockList set '
    +' ['+inttostr(RateStrF*1)+'天呆滞数量]=Null,'
    +' ['+inttostr(RateStrF*1)+'天呆滞金额]=Null,'
    +' ['+inttostr(RateStrF*2)+'天呆滞数量]=Null,'
    +' ['+inttostr(RateStrF*2)+'天呆滞金额]=Null,'
    +' ['+inttostr(RateStrF*3)+'天呆滞数量]=Null,'
    +' ['+inttostr(RateStrF*3)+'天呆滞金额]=Null,'
    +' ['+inttostr(RateStrF*4)+'天呆滞数量]=Null,'
    +' ['+inttostr(RateStrF*4)+'天呆滞金额]=Null,'
    +' ['+inttostr(RateStrF*5)+'天呆滞数量]=Null,'
    +' ['+inttostr(RateStrF*5)+'天呆滞金额]=Null,'
    +' ['+inttostr(RateStrF*6)+'天呆滞数量]=Null,'
    +' ['+inttostr(RateStrF*6)+'天呆滞金额]=Null '  ;
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #DeadStockList set '
    +' #DeadStockList.['+inttostr(RateStrF*1)+'天呆滞数量]= '
    +' #StockListTtl.[结存标准数量] , '
    +' #DeadStockList.['+inttostr(RateStrF*1)+'天呆滞金额]= '
    +' #StockListTtl.[结存金额]       '
    +' from #DeadStockList '
    +' left outer join #StockListTtl on '
    +' #StockListTtl.[商品名称]= #DeadStockList.[商品名称] '
    +' where #DeadStockList.[商品名称] not in (select [商品名称] '
    +' from #ExpenseList where [日期]>='
    +Quotedstr(datetostr(date-RateStrF*1))
    +' and [业务类别]<>'+Quotedstr('预警资料')+'  )';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #DeadStockList set '
    +' #DeadStockList.['+inttostr(RateStrF*2)+'天呆滞数量]= '
    +' #StockListTtl.[结存标准数量] , '
    +' #DeadStockList.['+inttostr(RateStrF*2)+'天呆滞金额]= '
    +' #StockListTtl.[结存金额]       '
    +' from #DeadStockList '
    +' left outer join #StockListTtl on '
    +' #StockListTtl.[商品名称]= #DeadStockList.[商品名称] '
    +' where #DeadStockList.[商品名称] not in (select [商品名称] '
    +' from #ExpenseList where [日期]>='
    +Quotedstr(datetostr(date-RateStrF*2))
    +' and [业务类别]<>'+Quotedstr('预警资料')+'  )';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #DeadStockList set '
    +' #DeadStockList.['+inttostr(RateStrF*3)+'天呆滞数量]= '
    +' #StockListTtl.[结存标准数量] , '
    +' #DeadStockList.['+inttostr(RateStrF*3)+'天呆滞金额]= '
    +' #StockListTtl.[结存金额]       '
    +' from #DeadStockList '
    +' left outer join #StockListTtl on '
    +' #StockListTtl.[商品名称]= #DeadStockList.[商品名称] '
    +' where #DeadStockList.[商品名称] not in (select [商品名称] '
    +' from #ExpenseList where [日期]>='
    +Quotedstr(datetostr(date-RateStrF*3))
    +' and [业务类别]<>'+Quotedstr('预警资料')+'  )';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #DeadStockList set '
    +' #DeadStockList.['+inttostr(RateStrF*4)+'天呆滞数量]= '
    +' #StockListTtl.[结存标准数量] , '
    +' #DeadStockList.['+inttostr(RateStrF*4)+'天呆滞金额]= '
    +' #StockListTtl.[结存金额]       '
    +' from #DeadStockList '
    +' left outer join #StockListTtl on '
    +' #StockListTtl.[商品名称]= #DeadStockList.[商品名称] '
    +' where #DeadStockList.[商品名称] not in (select [商品名称] '
    +' from #ExpenseList where [日期]>='
    +Quotedstr(datetostr(date-RateStrF*4))
    +' and [业务类别]<>'+Quotedstr('预警资料')+'  )';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #DeadStockList set '
    +' #DeadStockList.['+inttostr(RateStrF*5)+'天呆滞数量]= '
    +' #StockListTtl.[结存标准数量] , '
    +' #DeadStockList.['+inttostr(RateStrF*5)+'天呆滞金额]= '
    +' #StockListTtl.[结存金额]       '
    +' from #DeadStockList '
    +' left outer join #StockListTtl on '
    +' #StockListTtl.[商品名称]= #DeadStockList.[商品名称] '
    +' where #DeadStockList.[商品名称] not in (select [商品名称] '
    +' from #ExpenseList where [日期]>='
    +Quotedstr(datetostr(date-RateStrF*5))
    +' and [业务类别]<>'+Quotedstr('预警资料')+'  )';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #DeadStockList set '
    +' #DeadStockList.['+inttostr(RateStrF*6)+'天呆滞数量]= '
    +' #StockListTtl.[结存标准数量] , '
    +' #DeadStockList.['+inttostr(RateStrF*6)+'天呆滞金额]= '
    +' #StockListTtl.[结存金额]       '
    +' from #DeadStockList '
    +' left outer join #StockListTtl on '
    +' #StockListTtl.[商品名称]= #DeadStockList.[商品名称] '
    +' where #DeadStockList.[商品名称] not in (select [商品名称] '
    +' from #ExpenseList where [日期]>='
    +Quotedstr(datetostr(date-RateStrF*6))
    +' and [业务类别]<>'+Quotedstr('预警资料')+'  )';
  ADOQuery.ExecSQL;

  adsMaster.Close;
  adsMaster.CommandText :=' select * from #DeadStockList';
  adsMaster.Open;
  UpdateDBGrid;
  DBGrid.hint :='库存呆滞商品报表' ;
end;

procedure TSTNewStockRepForm.TheWareHouseGoodsActExecute(Sender: TObject);
var I:Integer;
begin
  for I:=0 to adsMaster.FieldCount-1 do
  begin
    if Pos('仓库名称',adsMaster.Fields[I].FieldName)>0 then
    begin
      WhereStr :=' Where [仓库名称]= '+Quotedstr(adsMaster.fieldbyname('仓库名称').AsString);
      if Button1.Tag=1 then Button1Click(sender)
        else Button2Click(sender);
      Memo1.Clear;
      Memo1.Text :='当前条件：[仓库名称]='+Quotedstr(adsMaster.fieldbyname('仓库名称').AsString);
    end;
  end;
end;

end.