ststockchange.pas

详细的ERP设计资料

    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' -1, b.Memo , '
    +' a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [标准数量],            '
    +' a.PriceGoal [标准单价],                '
    +' a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [金额],                  '
    +' f.name     [包装单位],                '
    +' a.Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [包装数量],     '
    +' a.PriceBase [包装单价]                '
    +' from YDGoodsOutDetail a                   '
    +' left outer join YDGoodsOutMaster     b on b.ID=a.MasterID      '
    +' left outer join STWarehouse         c on c.ID=b.WareHouseID      '
    +' left outer join MSEmployee       d on d.ID=b.EmployeeID    '
    +' left outer join DAGoods          e on e.ID=a.GoodsID       '
    +' left outer join MSUnit           f on f.ID=a.PackUnitID    '
    +' left outer join MSUnit           g on g.ID=e.UnitID    '
    +' left outer join DAGoodsClass     h on h.ID=e.GoodsClassID  '
    +' where b.RecordState<>'+ Quotedstr('删除')
    +' and isnull(a.GoodsID,0) <>0 '
    +' order by  b.date ,b.code,a.ID ';
  ADOQuery.ExecSQL;
//生产出库==

//项目出库==
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' insert into  #InOutlist ( '
    +' [日期], [编号],[业务类别],[经手人] , [仓库名称], '
    +' [商品类别], [商品名称],[规格型号],[标准单位],'
    +' [出入标志] , [备注] , '
    +' [标准数量], [标准单价],[金额],[包装单位],    '
    +' [包装数量], [包装单价]  )'
    +' select b.Date [日期], '
    +' b.Code [编号],                        '
    +' b.BillMode [业务类别],                '
    +' d.name  [经手人] ,                    '
    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' -1, b.Memo , '
    +' a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [标准数量],            '
    +' a.PriceGoal [标准单价],                '
    +' a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [金额],                  '
    +' f.name     [包装单位],                '
    +' a.Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [包装数量],     '
    +' a.PriceBase [包装单价]                '
    +' from EGGoodsOutDetail a                   '
    +' left outer join EGGoodsOutMaster     b on b.ID=a.MasterID      '
    +' left outer join STWarehouse         c on c.ID=b.WareHouseID      '
    +' left outer join MSEmployee       d on d.ID=b.EmployeeID    '
    +' left outer join DAGoods          e on e.ID=a.GoodsID       '
    +' left outer join MSUnit           f on f.ID=a.PackUnitID    '
    +' left outer join MSUnit           g on g.ID=e.UnitID    '
    +' left outer join DAGoodsClass     h on h.ID=e.GoodsClassID  '
    +' where b.RecordState<>'+ Quotedstr('删除')
    +' and isnull(a.GoodsID,0) <>0 '
    +' order by  b.date ,b.code,a.ID ';
  ADOQuery.ExecSQL;
//项目出库==

//库存调拨出库
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' insert into  #InOutlist ( '
    +' [日期], [编号],[业务类别],[经手人] , [仓库名称], '
    +' [商品类别], [商品名称],[规格型号],[标准单位],'
    +' [出入标志] , [备注] , '
    +' [标准数量], [标准单价],[金额],[包装单位],    '
    +' [包装数量], [包装单价]  )'
    +' select b.Date [日期], '
    +' b.Code [编号],                        '
    +' b.BillMode [业务类别],                '
    +' d.name  [经手人] ,                    '
    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' -1, b.Memo , '
    +' a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [标准数量],            '
    +' a.PriceGoal [标准单价],                '
    +' a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [金额],                  '
    +' f.name     [包装单位],                '
    +' a.Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [包装数量],     '
    +' a.PriceBase [包装单价]                '
    +' from STGoodsOutInDetail a                   '
    +' left outer join STGoodsOutInMaster     b on b.ID=a.MasterID      '
    +' left outer join STWarehouse         c on c.ID=b.ClientID      '
    +' left outer join MSEmployee       d on d.ID=b.EmployeeID    '
    +' left outer join DAGoods          e on e.ID=a.GoodsID       '
    +' left outer join MSUnit           f on f.ID=a.PackUnitID    '
    +' left outer join MSUnit           g on g.ID=e.UnitID    '
    +' left outer join DAGoodsClass     h on h.ID=e.GoodsClassID  '
    +' where b.RecordState<>'+ Quotedstr('删除')
    +' and  isnull(b.ClientID,0)<>0  and isnull(a.GoodsID,0)<>0 '
    +' order by  b.date ,b.code,a.ID ';

  ADOQuery.ExecSQL;
//库存调拨出库


//库存调拨入库
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' insert into  #InOutlist ( '
    +' [日期], [编号],[业务类别],[经手人] , [仓库名称], '
    +' [商品类别], [商品名称],[规格型号],[标准单位],'
    +' [出入标志] , [备注] , '
    +' [标准数量], [标准单价],[金额],[包装单位],    '
    +' [包装数量], [包装单价]  )'
    +' select b.Date [日期], '
    +' b.Code [编号],                        '
    +' b.BillMode [业务类别],                '
    +' d.name  [经手人] ,                    '
    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' 1, b.Memo , '
    +' a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [标准数量],            '
    +' a.PriceGoal [标准单价],                '
    +' a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [金额],                  '
    +' f.name     [包装单位],                '
    +' a.Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [包装数量],     '
    +' a.PriceBase [包装单价]                '
    +' from STGoodsOutInDetail a                   '
    +' left outer join STGoodsOutInMaster     b on b.ID=a.MasterID      '
    +' left outer join STWarehouse         c on c.ID=b.WareHouseID      '
    +' left outer join MSEmployee       d on d.ID=b.EmployeeID    '
    +' left outer join DAGoods          e on e.ID=a.GoodsID       '
    +' left outer join MSUnit           f on f.ID=a.PackUnitID    '
    +' left outer join MSUnit           g on g.ID=e.UnitID    '
    +' left outer join DAGoodsClass     h on h.ID=e.GoodsClassID  '
    +' where b.RecordState<>'+ Quotedstr('删除')
    +' and  isnull(b.WareHouseID,0)<>0  and isnull(a.GoodsID,0)<>0 '
    +' order by  b.date ,b.code,a.ID ';
  ADOQuery.ExecSQL;
//库存调拨入库

//盘盈入库
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' insert into  #InOutlist ( '
    +' [日期], [编号],[业务类别],[经手人] , [仓库名称], '
    +' [商品类别], [商品名称],[规格型号],[标准单位],'
    +' [出入标志] , [备注] , '
    +' [标准数量], [标准单价],[金额],[包装单位],    '
    +' [包装数量], [包装单价]  )'
    +' select b.Date [日期], '
    +' b.Code [编号],                        '
    +' b.BillMode [业务类别],                '
    +' d.name  [经手人] ,                    '
    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' 1, b.Memo , '
    +' a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [标准数量],            '
    +' a.PriceGoal [标准单价],                '
    +' a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [金额],                  '
    +' f.name     [包装单位],                '
    +' a.Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [包装数量],     '
    +' a.PriceBase [包装单价]                '
    +' from STGoodsCountOffDetail a                   '
    +' left outer join STGoodsCountOffMaster     b on b.ID=a.MasterID      '
    +' left outer join STWarehouse         c on c.ID=b.WareHouseID      '
    +' left outer join MSEmployee       d on d.ID=b.EmployeeID    '
    +' left outer join DAGoods          e on e.ID=a.GoodsID       '
    +' left outer join MSUnit           f on f.ID=a.PackUnitID    '
    +' left outer join MSUnit           g on g.ID=e.UnitID    '
    +' left outer join DAGoodsClass     h on h.ID=e.GoodsClassID  '
    +' where b.RecordState<>'+ Quotedstr('删除')
    +' and isnull(a.GoodsID,0)<>0 and BillMode='+Quotedstr('库存盘盈')
    +' order by  b.date ,b.code,a.ID ';
  ADOQuery.ExecSQL;
//盘盈入库

//盘亏出库
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' insert into  #InOutlist ( '
    +' [日期], [编号],[业务类别],[经手人] , [仓库名称], '
    +' [商品类别], [商品名称],[规格型号],[标准单位],'
    +' [出入标志] , [备注] , '
    +' [标准数量], [标准单价],[金额],[包装单位],    '
    +' [包装数量], [包装单价]  )'
    +' select b.Date [日期], '
    +' b.Code [编号],                        '
    +' b.BillMode [业务类别],                '
    +' d.name  [经手人] ,                    '
    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' -1, b.Memo , '
    +' a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [标准数量],            '
    +' a.PriceGoal [标准单价],                '
    +' a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [金额],                  '
    +' f.name     [包装单位],                '
    +' a.Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) [包装数量],     '
    +' a.PriceBase [包装单价]                '
    +' from STGoodsCountOffDetail a                   '
    +' left outer join STGoodsCountOffMaster     b on b.ID=a.MasterID      '
    +' left outer join STWarehouse         c on c.ID=b.WareHouseID      '
    +' left outer join MSEmployee       d on d.ID=b.EmployeeID    '
    +' left outer join DAGoods          e on e.ID=a.GoodsID       '
    +' left outer join MSUnit           f on f.ID=a.PackUnitID    '
    +' left outer join MSUnit           g on g.ID=e.UnitID    '
    +' left outer join DAGoodsClass     h on h.ID=e.GoodsClassID  '
    +' where b.RecordState<>'+ Quotedstr('删除')
    +' and isnull(a.GoodsID,0)<>0 and BillMode<>'+Quotedstr('库存盘盈')
    +' order by  b.date ,b.code,a.ID ';
  ADOQuery.ExecSQL;
//盘亏出库

  IniStockChange; //创建初始库存变动明细数据表


  ADOQuery.Close;
  ADOQuery.SQL.Text :=' if exists (select * from dbo.sysobjects where '
      +' id = object_id '
      +' (' +Quotedstr('[dbo].[TempExpenseList]')+') and OBJECTPROPERTY(id,'
      +Quotedstr('IsUserTable')+' ) = 1) drop table [dbo].[TempExpenseList] ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select top 1 * into TempExpenseList from #ExpenseList' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select top 1 * from TempExpenseList' ;
  ADOQuery.open;
  ExpSttcCheckListBox.Columns :=4;
  ExpSttcCheckListBox.Items.Clear;
  ExpSttcCheckListBox.Hint :='';
  WNADOCQuery1.Field.Clear;
  for I := 0 to ADOQuery.Fields.Count - 1 do
  begin
    if not (ADOQuery.Fields[i] is TNumericField)  then
       ExpSttcCheckListBox.Items.Add(Trim(ADOQuery.Fields[i].FieldName))
     else
       ExpSttcCheckListBox.Hint :=ExpSttcCheckListBox.Hint
         +', sum(isnull('+Trim(ADOQuery.Fields[i].FieldName)+',0)) as ['
         +Trim(ADOQuery.Fields[i].FieldName)+'] ';
     WNADOCQuery1.Field.Add(Trim(ADOQuery.Fields[i].FieldName));
  end;
  WhereStr :=' where  1=1 ';
  Memo1.Text :='不限制条件!';
  SelectStr :='';
end;

procedure TSTStockChangeFrom.Button1Click(Sender: TObject);
begin
  Panel6.Visible :=True;
  Panel6.Repaint;
  Button1.Tag:=1 ;
  adsMaster.Close;
  adsMaster.CommandText:=' select *  '
    +' from #ExpenseList'+WhereStr
    +' order by [日期], [编号]' ;
//  showmessage(adsMaster.CommandText);
  adsMaster.open;
  Panel6.Visible :=False;
  UpdateDBGrid;
  DBGrid.hint :='';
end;

procedure TSTStockChangeFrom.FormActivate(Sender: TObject);
begin
  inherited;
  { ADOQuery.Close;
  ADOQuery.SQL.Text :='select max(日期) MDate  from #ExpenseList ';
  ADOQuery.Open;

  if ADOQuery.FieldByName('MDate').IsNull then WhereStr :=Datetostr(date)
    else WhereStr :=Trim(ADOQuery.fieldbyname('MDate').AsString);
  Memo1.ReadOnly :=False;
  Memo1.Clear;
  Memo1.Text :=' 日期 等于'+ Quotedstr(WhereStr);
  Memo1.ReadOnly :=True;
  WhereStr :=' where [日期]='+Quotedstr(WhereStr);
  WhereStr := ' where 1=1 ';            }
  ExpSttcCheckListBox.Checked[6] :=True;
  SelectStr :=','+Trim(ExpSttcCheckListBox.Items[6]);
  Button2Click(sender);
end;

procedure TSTStockChangeFrom.Button2Click(Sender: TObject);
var I :integer;
    SelectStr1:String;
begin
  inherited;
  Panel6.Visible :=True;
  Panel6.Repaint;
  Button1.Tag:=0;

  if Trim(SelectStr) ='' then
    begin
      ExpSttcCheckListBox.Checked[1] :=true;
      ExpSttcCheckListBox.ItemIndex := 1;
      ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
    end;
  SelectStr1 :=Trim(SelectStr);
  while Pos(',', SelectStr1)=1 do  SelectStr1[Pos(',', SelectStr1)] :=' ';

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ExpenseListTtl')
        +' )) DROP TABLE #ExpenseListTtl ' ;
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' select ' + SelectStr1+ ExpSttcCheckListBox.hint
    +' into #ExpenseListTtl from #ExpenseList  '
    +WhereStr +' group by '+SelectStr1;