stactualstock.pas

详细的ERP设计资料

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' CREATE TABLE TempSaleMD  ('
    +' [id] [int] IDENTITY (1, 1) NOT NULL ,'
    +' [Date] [datetime] NULL ,[Code] [varchar] (20)  ,          '
    +' [ClientID] [int] NULL , [EmployeeID] [int] NULL ,         '
    +' [BillMode] [varchar] (16)  ,[ModeDC] [int] NULL ,         '
    +' [ModeC] [int] NULL ,[Brief] [varchar] (30)  ,             '
    +' [GoodsID] [int] NULL ,[GoodsSpec] [varchar] (30)  ,       '
    +' [Quantity] [float] NULL ,[QuantityPcs] [float] NULL ,     '
    +' [PackUnitID] [int] NULL ,[PriceBase] [money] NULL ,       '
    +' [Amount] [float] NULL ,[GoalUnitID] [int] NULL ,          '
    +' [GoalQuantity] [float] NULL ,[Client] [varchar] (50)  NULL ,'
    +' [Employee] [varchar] (30)  NULL ,[Goods] [varchar] (60)  NULL , '
    +' [GoodsClass] [varchar] (30)  NULL ,[DAArea] [varchar] (30)  NULL , '
    +' [WareHouse] [varchar] (30)  NULL , '
    +' [Unit] [varchar] (20)  NULL )' ;
  ADOQuery.ExecSQL;
  Memo1.ReadOnly :=False;
  Memo1.Clear;
  Memo1.Text :=' 当前库存';
  Memo1.ReadOnly :=True;
  WhereStr :=' where 1=1  ';
//  CheckBox2.Checked :=True;
  Button1Click(sender);
  UpdateDBGrid;
end;

procedure TSTActualStockForm.N1Click(Sender: TObject);
var E:string;
begin
  if adsMaster.IsEmpty then exit;
end;

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


procedure TSTActualStockForm.Button3Click(Sender: TObject);
begin
  inherited;
  WNADOCQuery1.TabName :='TempSaleMD';
  WNADOCQuery1.ConnectionString :=CommonData.acnConnection.ConnectionString;
  WNADOCQuery1.Execute(False);
//  showmessage(WNADOCQuery1.QueryTerm);
  if  trim(WNADOCQuery1.QueryTerm)<>'' then
    WhereStr :=' where ' +  trim(WNADOCQuery1.QueryTerm)
      else WhereStr :=' Where 1=1 ' ;
  Memo1.ReadOnly :=False;
  Memo1.Clear;
  if ( copy(trim(WNADOCQuery1.QueryTerm),1,3) ='not' ) or
    ( copy(trim(WNADOCQuery1.QueryTerm),1,3) ='NOT' )then
     Memo1.Text :='全部不满足条件: ' +Copy(trim(WNADOCQuery1.ShowTerm.Text),5,800)
     else Memo1.Text :=Copy(trim(WNADOCQuery1.ShowTerm.Text),5,800);
  if WhereStr =' Where 1=1 ' then
    begin
      Memo1.Clear;
      Memo1.Text :='不限制条件!';
    end;
  Memo1.ReadOnly :=True;
  if  Button1.Tag =1 then Button1Click(Sender)
    else  Button2Click(Sender);
end;

procedure TSTActualStockForm.CheckBox1Click(Sender: TObject);
begin
  inherited;
  if not CheckBox1.Checked and
     not CheckBox2.Checked  and  not CheckBox3.Checked
     then CheckBox1.Checked:=not CheckBox1.Checked;
end;

procedure TSTActualStockForm.Button1Click(Sender: TObject);
var I :integer;
begin
  inherited;
  Button1.Tag :=1;
  Panel6.Visible :=True;
  Panel6.Repaint;
  adsMaster.Close;
  adsMaster.CommandText:=' select date [日期],'
  +' code [编号],BillMode [业务类型],'
  +' WareHouse [仓库名称],Goods [商品名称], '
  +' Unit [单位], isnull(GoalQuantity,0)  [入库数量],'
  +' isnull(Amount,0)/isnull(GoalQuantity,0) [入库单价], isnull(Amount,0) [入库金额], '
  +' isnull(GoalQuantityOut,0)    [出库数量], isnull(AmountOut,0)/isnull(GoalQuantityOut,0) [出库单价], '
  +' isnull(AmountOut,0)  [出库金额], '
  +' ( isnull(GoalQuantity,0)-isnull(GoalQuantityOut,0) ) [结存数量],'
  +' ( isnull(Amount,0)-isnull(AmountOut,0) )/(isnull(GoalQuantity,0)-isnull(GoalQuantityOut,0))[结存单价], '
  +' ( isnull(Amount,0)-isnull(AmountOut,0) ) [结存金额], '
  +' Employee [业务经办]  from #STActualStockDM    '
  +WhereStr ;
  adsMaster.open;

  Panel6.Visible :=False;
  UpdateDBGrid;
  DBGrid.hint :='';
end;

procedure TSTActualStockForm.Button2Click(Sender: TObject);
var I :integer;
    SelectStr,GroupStr,SelectStr1:String;
begin
  inherited;
  Panel6.Visible :=True;
  Panel6.Repaint;
  Button1.Tag :=0;
  GroupStr :='';
  SelectStr :='';
  SelectStr1 :='';
  if CheckBox1.Checked then
  begin
    SelectStr :=SelectStr+' WareHouse ,' ;
    SelectStr1 :=SelectStr1+' WareHouse [仓库名称],' ;
    GroupStr :=GroupStr+' WareHouse , '            ;
  end;
  if CheckBox2.Checked then
  begin
    SelectStr :=SelectStr+' Goods   ,' ;
    SelectStr1 :=SelectStr1+' Goods [商品名称],' ;
    GroupStr :=GroupStr+' Goods , '             ;
  end;
  if CheckBox3.Checked then
  begin
    SelectStr :=SelectStr+' BillMode  ,' ;
    SelectStr1 :=SelectStr1+' BillMode [业务类别],' ;
    GroupStr :=GroupStr+' BillMode , '             ;
  end;
  if CheckBox2.Checked then
    begin
      SelectStr :=SelectStr+'unit ,'  ;
      SelectStr1 :=SelectStr1+' unit [计量单位],' ;
      GroupStr :=Trim(GroupStr)+'unit '      ;
    end else
    begin
      GroupStr :=Copy(Trim(GroupStr),1,Length(Trim(GroupStr))-1)      ;
    end;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Truncate Table #STActualStockTtl';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' insert into #STActualStockTtl ( '
    + SelectStr +' GoalQuantity,Amount,GoalQuantityOut,AmountOut,'
    +' GoalQuantityEnd ,AmountEnd ) '
    +' select  '  +SelectStr
    +' Sum(isnull(GoalQuantity,0)) GoalQuantity,'
    +' Sum(isnull(Amount,0)) Amount , '
    +' Sum(isnull(GoalQuantityOut,0)) GoalQuantityOut,'
    +' Sum(isnull(AmountOut,0)) AmountOut , '
    +' Sum(isnull(GoalQuantity,0)) - Sum(isnull(GoalQuantityOut,0)) GoalQuantityEnd, '
    +' Sum(isnull(Amount,0))-Sum(isnull(AmountOut,0)) AmountEnd '
    +'  from #STActualStockDM  '
    + WhereStr +' group by '+GroupStr;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #STActualStockTtl set  '
    + ' Price = Amount/GoalQuantity where GoalQuantity<>0 '
    +' and GoalQuantity is not null ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #STActualStockTtl set  '
    + ' PriceOut = AmountOut/GoalQuantityOut where GoalQuantityOut<>0 '
    +' and GoalQuantityOut is not null ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #STActualStockTtl set  '
    + ' PriceEnd = AmountEnd/GoalQuantityEnd where GoalQuantityEnd<>0 '
    +' and GoalQuantityEnd is not null ';
  ADOQuery.ExecSQL;

  adsMaster.Close;
  adsMaster.CommandText:=' select  '  +SelectStr1
  +' GoalQuantity [入库数量],'
  +' Price [入库单价],  '
  +' Amount [入库金额], '
  +' GoalQuantityOut [出库数量], '
  +' PriceOut  [出库单价], '
  +' AmountOut [出库金额], '
  +' GoalQuantityEnd [结存数量],'
  +' PriceEnd [结存单价], '
  +' AmountEnd[结存金额]'
  +' from #STActualStockTtl  ' ;
  adsMaster.open;

  

 { with adsMaster do
  begin
    for I :=0 to FieldList.Count -1 do
    begin
      if Fields[i].DataType = TFieldType(ftFloat) then
        begin
          SetStrProp(Fields[I], 'DisplayFormat','#,#.00') ;
          Fields[i].DisplayWidth :=20;
        end;
    end;
  end;  }


  Panel6.Visible :=False;
  UpdateDBGrid  ;
  DBGrid.hint :='';
  if CheckBox1.Checked then DBGrid.hint := DBGrid.hint+CheckBox1.Caption+',';
  if CheckBox2.Checked then DBGrid.hint := DBGrid.hint+CheckBox2.Caption+',';
  if trim(DBGrid.hint)<>'' then
    DBGrid.hint :='汇总项目:'+Copy(Trim(DBGrid.hint),1,Length(Trim(DBGrid.hint))-1 );
end;

procedure TSTActualStockForm.adsMasterBeforeOpen(DataSet: TDataSet);
begin
  inherited;
  adsMaster.IndexFieldNames := '';
end;

procedure TSTActualStockForm.ExportActionExecute(Sender: TObject);
begin
  hint :='统计条件:' +Memo1.Text;
  caption :=caption+'报表';
  inherited;
end;

end.