ststockchange0.pas

详细的ERP设计资料

        +' b.Isnull(ModeDC,1)*Isnull(ModeC,1)*a.Amount as AmountIn, 0.00 as AmountOut from STGoodsCountOffDetail a   '
        +' left outer join STGoodsCountOffMaster b on b.ID=a.MasterID '
        +' where  b.BillMode='+Quotedstr('库存盘盈')  //库存盘点表 (盘盈入库仓库)
        +' ) AS c'
        +' WHERE c.DATE>'+Quotedstr(Datetostr(DateTimePicker1.Date))
        + ' and c.DATE<='+Quotedstr(Datetostr(DateTimePicker2.Date))
        +' and c.recordstate<>'+Quotedstr('删除') ;
   ADOQuery.ExecSQL;
   ADOQuery.Close;
   ADOQuery.SQL.Text :=' TRUNCATE TABLE #TEMPSTOCKE' ;
   ADOQuery.ExecSQL;
   ADOQuery.Close;
   ADOQuery.SQL.Text :=' INSERT INTO  #TEMPSTOCKE ('
       +' warehouseID,GoodsID,GoalUnitID,'
       +' FGoalQuantity ,FAmount,BillMode , '
       +' GoalQuantityIn, AmountIn , '
       +' GoalQuantityOut ,AmountOut , '
       +' EGoalQuantity  , EAmount )'
       +' select warehouseID,GoodsID,GoalUnitID,'
       +' Sum(Isnull(FGoalQuantity,0)) , Sum(Isnull(FAmount,0)) ,  '
       +' BillMode , '
       +' Sum(Isnull(GoalQuantityIn,0)) , Sum(Isnull(AmountIn,0))  , '
       +' Sum(Isnull(GoalQuantityOut,0)) ,Sum(Isnull(AmountOut,0))  , '
       +' Sum(Isnull(GoalQuantityIn,0))+Sum(Isnull(FGoalQuantity,0))-'
       +' Sum(Isnull(GoalQuantityOut,0))  as EGoalQuantity,'
       +'  Sum(Isnull(FAmount,0))+  Sum(Isnull(AmountIn,0))-'
       +'  Sum(Isnull(AmountOut,0)) as EAmount'
       +' from #TEMPSTOCK '
       +' group by warehouseID,GoodsID,GoalUnitID ,BillMode order by warehouseID';
   ADOQuery.ExecSQL;

   ADOQuery.Close;
   ADOQuery.SQL.Text :=' update #TEMPSTOCKE set FPriceBase= '
       +' abs(FAmount/FGoalQuantity) where FGoalQuantity<>0';
   ADOQuery.ExecSQL;
   ADOQuery.Close;
   ADOQuery.SQL.Text :=' update #TempStockE set PriceIn= '
       +' abs(AmountIn/GoalQuantityIn)  where GoalQuantityIn<>0';
   ADOQuery.ExecSQL;

   ADOQuery.SQL.Text :=' update #TempStockE set PriceOut= '
       +' Abs(AmountOut/GoalQuantityOut) where GoalQuantityOut<>0';
   ADOQuery.ExecSQL;

   ADOQuery.Close;
   ADOQuery.SQL.Text :=' update #TempStockE set EPriceBase= '
       +' abs(EAmount/EGoalQuantity) where EGoalQuantity<>0';
   ADOQuery.ExecSQL;
   adsStockChange.Close;
   adsStockChange.CommandText :=' select e.name  as [仓库名称]  , '
        +' d.name as [商品名称], f.name as [标准单位] ,  '
        +' FGoalQuantity as [期初数量] ,   '
        +' FPriceBase as [期初单价]  ,           '
        +' FAmount as [期初金额]    ,   '
        +' BillMode as [本期变动方式] ,          '
        +' GoalQuantityIn as  [本期入库数量] ,   '
        +' PriceIn as [本期入库单价]  ,           '
        +' AmountIn as [本期入库金额]  ,      '
        +' GoalQuantityOut as  [本期出库数量] ,  '
        +' PriceOut as [本期出库单价]  ,           '
        +' AmountOut as [本期出库金额]  ,      '
        +' EGoalQuantity as [期末数量]  ,   '
        +' EPriceBase as [期末单价]  ,           '
        +' EAmount as [期末金额]    '
        +' from #TEMPSTOCKE a  '
        +' left outer join dagoods d on d.id=a.goodsid '
        +' left outer join STWarehouse e on e.id=a.warehouseID '
        +' left outer join msunit f on f.id=a.GoalunitID '
        +' where GoodsID<>0 and (Abs(GoalQuantityIn)+abs(GoalQuantityOut))<>0';
   adsStockChange.Open;
   UpdateDBGrid;
end;

procedure TSTStockChangeFrom.FormCreate(Sender: TObject);
var year,month,day :word;
begin
  inherited;
  DecodeDate(DateTimePicker1.Date,year, month, day);
  DateTimePicker1.Date :=EnCodeDate(year,month,1);
  DateTimePicker2.Date :=Date;
//  DateTimePicker2.Date :=EndOfTheMonth(Date);
end;

procedure TSTStockChangeFrom.FormActivate(Sender: TObject);
begin
//  inherited;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' create table #TempStock ('
      +'	[ID] [int] IDENTITY (1, 1) NOT NULL ,'
      +'	[date] [datetime] null,              '
      +'	[warehouseID] [float] NULL ,      '
      +'	[GoodsID] [int] NULL ,               '
      +'	[GoalUnitID] [int] NULL ,            '
      +'	[FGoalQuantity] [float] NULL ,   '
      +'	[FPriceBase] [money] NULL ,           '
      +'	[FAmount] [float] NULL   ,   '
      +'	[BillMode] [varchar] (16) ,          '
      +'	[GoalQuantityIn] [float] NULL ,   '
      +'	[AmountIn] [float] NULL ,      '
      +'	[PriceIn] [money] NULL ,           '
      +'	[GoalQuantityOut] [float] NULL ,  '
      +'	[AmountOut] [float] NULL ,      '
      +'	[PriceOut] [money] NULL ,           '
      +'	[EGoalQuantity] [float] NULL ,   '
      +'	[EPriceBase] [float] NULL ,           '
      +'	[EAmount] [float] NULL   )     ';
  ADOQuery.ExecSQL;
  ADOQuery.SQL.Text :=' create table #TempStockE ('
      +'	[ID] [int] IDENTITY (1, 1) NOT NULL ,'
      +'	[date] [datetime] null,              '
      +'	[warehouseID] [float] NULL ,      '
      +'	[GoodsID] [int] NULL ,               '
      +'	[GoalUnitID] [int] NULL ,            '
      +'	[FGoalQuantity] [float] NULL ,   '
      +'	[FPriceBase] [money] NULL ,           '
      +'	[FAmount] [float] NULL   ,   '
      +'	[BillMode] [varchar] (16) ,          '
      +'	[GoalQuantityIn] [float] NULL ,   '
      +'	[AmountIn] [float] NULL ,      '
      +'	[PriceIn] [money] NULL ,           '
      +'	[GoalQuantityOut] [float] NULL ,  '
      +'	[AmountOut] [float] NULL ,      '
      +'	[PriceOut] [money] NULL ,           '
      +'	[EGoalQuantity] [float] NULL ,   '
      +'	[EPriceBase] [float] NULL ,           '
      +'	[EAmount] [float] NULL   )     ';
  ADOQuery.ExecSQL;
  BitBtn1Click(Sender);
end;

procedure TSTStockChangeFrom.FormDeactivate(Sender: TObject);
begin
//  inherited;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#TempStock')
        +' )) DROP TABLE #TempStock '  ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#TempStockE')
        +' )) DROP TABLE #TempStockE '  ;
  ADOQuery.ExecSQL;
end;

procedure TSTStockChangeFrom.DBGridCellClick(Column: TColumn);
begin
//  UpdateDBGrid;
end;

procedure TSTStockChangeFrom.DBGridDblClick(Sender: TObject);
begin
//  inherited;
//  UpdateDBGrid;
end;

procedure TSTStockChangeFrom.UpdateDBGrid;
var  I: Integer;
begin
  with DBGrid do
  begin
    FooterRowCount := 0;
    Columns[0].Footer.ValueType := fvtStaticText;
    Columns[0].Footer.Value := '合计:';
    Columns[0].Footer.Alignment := taCenter;
    Columns[0].Title.Alignment:= taCenter;
    Columns[0].Width :=130;
    for I := 1 to Columns.Count - 1 do
    begin
      Columns[i].Width :=90;
      if Pos('日',Columns[I].FieldName)>0 then Columns[i].Width :=70;
      if Pos('编',Columns[I].FieldName)>0 then Columns[i].Width :=70;
      Columns[i].Title.Alignment:= taCenter;
      if Columns[I].Field is TNumericField then
      if Pos('Price',Columns[I].FieldName)<=0 then
        Columns[I].Footer.ValueType := fvtSum;
    end;
    FooterRowCount := 1;
  end;
end;

procedure TSTStockChangeFrom.DBGridTitleClick(Column: TColumn);
begin
  inherited;
  UpdateDBGrid;
end;

end.