ststockchange.pas

详细的ERP设计资料

  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Update #ExpenseListTtl set '
    +' 	[期初标准单价]	 =	[期初金额]	   /	[期初标准数量]		 '
    +' where Isnull([期初标准数量],0)<>0 ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Update #ExpenseListTtl set '
    +' 	[入库单价]	 =	[入库金额]	   /	[入库标准数量]		 '
    +' where Isnull([入库标准数量],0)<>0 ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Update #ExpenseListTtl set '
    +' 	[入库包装单价]	 =	[入库金额]	   /	[入库包装数量]		 '
    +' where Isnull([入库包装数量],0)<>0 ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Update #ExpenseListTtl set '
    +' 	[出库单价]	 =	[出库金额]	   /	[出库标准数量]		 '
    +' where Isnull([出库标准数量],0)<>0 ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Update #ExpenseListTtl set '
    +' 	[出库包装单价]	 =	[出库金额]	   /	[出库包装数量]		 '
    +' where Isnull([出库包装数量],0)<>0 ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Update #ExpenseListTtl set '
    +' 	[期末标准单价]	 =	[期末金额]	   /	[期末标准数量]		 '
    +' where Isnull([期末标准数量],0)<>0 ';
  ADOQuery.ExecSQL;

  adsMaster.Close;
  adsMaster.CommandText:=' select * from #ExpenseListTtl ';
  adsMaster.open;

  Panel6.Visible :=False;
  UpdateDBGrid;
  DBGrid.hint :='汇总项目:'+SelectStr1;
end;

procedure TSTStockChangeFrom.N1Click(Sender: TObject);
begin
  ExpSttcCheckListBox.Sorted :=not ExpSttcCheckListBox.Sorted;
end;

procedure TSTStockChangeFrom.N2Click(Sender: TObject);
var I:integer;
begin
  for I := 0 to ExpSttcCheckListBox.Items.Count - 1 do
  begin
    ExpSttcCheckListBox.Checked[I] :=not ExpSttcCheckListBox.Checked[I];
    ExpSttcCheckListBox.ItemIndex := I;
    ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
  end;
end;

procedure TSTStockChangeFrom.N3Click(Sender: TObject);
var I:integer;
begin
  for I := 0 to ExpSttcCheckListBox.Items.Count - 1 do
  begin
    ExpSttcCheckListBox.Checked[I] :=True;
    ExpSttcCheckListBox.ItemIndex := I;
    ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
  end;
end;

procedure TSTStockChangeFrom.N4Click(Sender: TObject);
var I:integer;
begin
  for I := 0 to ExpSttcCheckListBox.Items.Count - 1 do
  begin
    ExpSttcCheckListBox.Checked[I] :=False;
    ExpSttcCheckListBox.ItemIndex := I;
    ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
  end;
end;

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

procedure TSTStockChangeFrom.ExpSttcCheckListBoxClickCheck(
  Sender: TObject);
var CheckStr :string;
begin
  CheckStr :=','+Trim(ExpSttcCheckListBox.Items[ExpSttcCheckListBox.ItemIndex]);
  if (ExpSttcCheckListBox.Checked[ExpSttcCheckListBox.ItemIndex]) and
     (pos(CheckStr,SelectStr)<=0) then  SelectStr :=Trim(SelectStr)+Trim(CheckStr);

  if (not ExpSttcCheckListBox.Checked[ExpSttcCheckListBox.ItemIndex]) and
     (pos(CheckStr,SelectStr)>0) then
     SelectStr :=StringReplace(SelectStr,CheckStr,'',[rfReplaceAll, rfIgnoreCase]);
end;

procedure TSTStockChangeFrom.Button4Click(Sender: TObject);
begin
  inherited;
  Panel2.Enabled :=False;
  DBGrid.Enabled :=False;
  VipsetPanel.Visible :=True;
  VipsetPanel.Repaint;
end;

procedure TSTStockChangeFrom.IniStockChange;
begin
  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 top 1 [日期],[编号],[业务类别], '
    +' [经手人] ,[仓库名称],[商品类别],[商品名称],'
    +' [规格型号],[标准单位],[备注] , '
    +' 	[标准数量]	[期初标准数量]	,	 '
    +' 	[标准数量]	[期初标准单价]	,	 '
    +' 	[标准数量]	[期初金额]	,	 '
    +' 	[标准数量]	[入库标准数量]	,	 '
    +' 	[标准数量]	[入库单价]	,	 '
    +' 	[标准数量]	[入库金额]	,	 '
    +' 	[标准单位]	[入库包装单位]	,	 '
    +' 	[标准数量]	[入库包装数量]	,	 '
    +' 	[标准数量]	[入库包装单价]	,	 '
    +' 	[标准数量]	[出库标准数量]	,	 '
    +' 	[标准数量]	[出库单价]	,	 '
    +' 	[标准数量]	[出库金额]	,	 '
    +' 	[标准单位]	[出库包装单位]	,	 '
    +' 	[标准数量]	[出库包装数量]	,	 '
    +' 	[标准数量]	[出库包装单价]	,	 '
    +' 	[标准数量]	[期末标准数量]	,	 '
    +' 	[标准数量]	[期末标准单价]	,	 '
    +' 	[标准数量]	[期末金额]	         '
    +' into #ExpenseList '
    +' from #InOutlist                    ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Truncate Table #ExpenseList ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Insert Into  #ExpenseList ( '
    +' [日期],[编号],[业务类别], '
    +' [经手人] ,[仓库名称],[商品类别],[商品名称],'
    +' [规格型号],[标准单位],[备注] , '
    +' [期初标准数量],[期初标准单价],[期初金额]	)   '
    +' select '+Quotedstr(DateTostr(DateTimePicker1.Date-1))
    +' [日期], '+Quotedstr('----')+' [编号], '+Quotedstr('期初结存')
    +' [业务类别],'+Quotedstr('----')+' [经手人] ,'
    +  Quotedstr('--------')+' [仓库名称], [商品类别], '
    +' [商品名称],'+Quotedstr('----')+' [规格型号],               '
    +' [标准单位], ' + Quotedstr('结存')+' [备注] , '
    +' sum(Isnull([标准数量],0)*Isnull([出入标志],1)) [期初标准数量] ,  '
    +' sum(Isnull([标准单价],0)) [期初标准单价],                '
    +' sum(Isnull([金额],0)*Isnull([出入标志],1)) [期初金额]  '
    +' from #InOutlist       '
    +' where [日期]<='+ Quotedstr(DateTostr(DateTimePicker1.Date-1))
    +' group by  [商品类别],[商品名称],[标准单位]' ;
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Update #ExpenseList set '
    +' [期初标准单价]= [期初金额]/[期初标准数量]  '
    +' where Isnull([期初标准数量],0)<>0 ' ;
  ADOQuery.ExecSQL;

//本期入库
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Insert into #ExpenseList ( '
    +' [日期], [编号], [业务类别],'
    +' [经手人],[仓库名称],[商品类别],[商品名称], '
    +' [规格型号],[标准单位],[备注] , '
    +' [入库标准数量],[入库单价], [入库金额], '
    +' [入库包装单位],[入库包装数量],[入库包装单价] )'
    +' select '
    +' [日期], [编号], [业务类别],'
    +' [经手人],[仓库名称],[商品类别],[商品名称], '
    +' [规格型号],[标准单位],[备注] , '
    +' [标准数量],[标准单价], [金额], '
    +' [包装单位],[包装数量],[包装单价] '
    +' from #InOutlist                   '
    +' where [日期]>'+ Quotedstr(DateTostr(DateTimePicker1.Date-1))
    +' and [日期]<='+ Quotedstr(DateTostr(DateTimePicker2.Date))
    +' and [出入标志]=1';
  ADOQuery.ExecSQL;

//本期出库
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Insert into #ExpenseList ( '
    +' [日期], [编号], [业务类别],'
    +' [经手人],[仓库名称],[商品类别],[商品名称], '
    +' [规格型号],[标准单位],[备注] , '
    +' [出库标准数量],[出库单价], [出库金额], '
    +' [出库包装单位],[出库包装数量],[出库包装单价] )'
    +' select '
    +' [日期], [编号], [业务类别],'
    +' [经手人],[仓库名称],[商品类别],[商品名称], '
    +' [规格型号],[标准单位],[备注] , '
    +' [标准数量],[标准单价], [金额], '
    +' [包装单位],[包装数量],[包装单价] '
    +' from #InOutlist                   '
    +' where [日期]>'+ Quotedstr(DateTostr(DateTimePicker1.Date-1))
    +' and [日期]<='+ Quotedstr(DateTostr(DateTimePicker2.Date))
    +' and [出入标志]=-1';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Update #ExpenseList set '
    +' [期末标准数量]=Isnull([期初标准数量],0)+Isnull([入库标准数量],0)-'
    +' isnull([出库标准数量],0)  , '
    +' [期末金额]=isnull([期初金额],0)+isnull([入库金额],0)-isnull([出库金额],0)';
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Update #ExpenseList set '
    +' [期末标准单价]= [期末金额]/[期末标准数量]  '
    +' where Isnull([期末标准数量],0)<>0 ' ;
  ADOQuery.ExecSQL;


  //
end;

procedure TSTStockChangeFrom.Button6Click(Sender: TObject);
begin
  VipsetPanel.Visible :=False;
  Panel2.Enabled :=not VipsetPanel.Visible;
  DBGrid.Enabled :=not VipsetPanel.Visible;
end;

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

procedure TSTStockChangeFrom.DateTimePicker2Change(Sender: TObject);
begin
  if DateTimePicker2.Date<=DateTimePicker1.Date then  DateTimePicker1.Date :=DateTimePicker2.Date;
end;

procedure TSTStockChangeFrom.DateTimePicker1Change(Sender: TObject);
begin
  if DateTimePicker1.Date>DateTimePicker2.Date then  DateTimePicker2.Date :=DateTimePicker1.Date;
end;

procedure TSTStockChangeFrom.Button5Click(Sender: TObject);
begin
  IniStockChange;
  VipsetPanel.Visible :=False;
  Panel2.Enabled :=True;
  DBGrid.Enabled :=True;
  if Button1.Tag=1 then Button1Click(sender)
    else Button2Click(Sender);

end;

procedure TSTStockChangeFrom.ListGoodsDetailActExecute(Sender: TObject);
var I:Integer;
  Goods :string;
begin
  for I:=0 to adsMaster.FieldCount-1 do
  begin
    if Pos('商品名称',adsMaster.Fields[I].FieldName)>0 then
    begin
      Goods := adsMaster.FieldByName('商品名称').AsString;
      if Trim(Goods)='' then Exit;
      adsMaster.Close;
      adsMaster.CommandText :='select * from #ExpenseList  where  [商品名称]='+Quotedstr(Goods);
      adsMaster.Open;
      UpdateDBGrid;
      Exit;
    end;
  end;
end;

procedure TSTStockChangeFrom.ListGoodsClassActExecute(Sender: TObject);
var I:Integer;
  Goods :string;
begin
  for I:=0 to adsMaster.FieldCount-1 do
  begin
    if Pos('商品类别',adsMaster.Fields[I].FieldName)>0 then
    begin
      Goods := adsMaster.FieldByName('商品类别').AsString;
      if Trim(Goods)='' then Exit;
      adsMaster.Close;
      adsMaster.CommandText :='select * from #ExpenseList  where  [商品类别]='+Quotedstr(Goods);
      adsMaster.Open;
      UpdateDBGrid;
      Exit;
    end;
  end;
end;

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

end.