⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 stnewstockrep.pas

📁 详细的ERP设计资料
💻 PAS
📖 第 1 页 / 共 4 页
字号:
🔍
1234 
//生产入库==

//生产出库==
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' insert into  #ExpenseList ( '
    +' [日期], [编号],[业务类别],[经手人] , [仓库名称], '
    +' [商品类别], [商品名称],[规格型号],[标准单位],'
    +' [出库标准数量], [出库标准单价],[出库金额],[出库包装单位],    '
    +' [出库包装数量], [出库包装单价]  )'
    +' select b.Date [日期], '
    +' b.Code [编号],                        '
    +' b.BillMode [业务类别],                '
    +' d.name  [经手人] ,                    '
    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' 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  #ExpenseList ( '
    +' [日期], [编号],[业务类别],[经手人] , [仓库名称], '
    +' [商品类别], [商品名称],[规格型号],[标准单位],'
    +' [出库标准数量], [出库标准单价],[出库金额],[出库包装单位],    '
    +' [出库包装数量], [出库包装单价]  )'
    +' select b.Date [日期], '
    +' b.Code [编号],                        '
    +' b.BillMode [业务类别],                '
    +' d.name  [经手人] ,                    '
    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' 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  #ExpenseList ( '
    +' [日期], [编号],[业务类别],[经手人] , [仓库名称], '
    +' [商品类别], [商品名称],[规格型号],[标准单位],'
    +' [出库标准数量], [出库标准单价],[出库金额],[出库包装单位],    '
    +' [出库包装数量], [出库包装单价]  )'
    +' select b.Date [日期], '
    +' b.Code [编号],                        '
    +' b.BillMode [业务类别],                '
    +' d.name  [经手人] ,                    '
    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' 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  #ExpenseList ( '
    +' [日期], [编号],[业务类别],[经手人] , [仓库名称], '
    +' [商品类别], [商品名称],[规格型号],[标准单位],'
    +' [入库标准数量], [入库标准单价],[入库金额],[入库包装单位],    '
    +' [入库包装数量], [入库包装单价]  )'
    +' select b.Date [日期], '
    +' b.Code [编号],                        '
    +' b.BillMode [业务类别],                '
    +' d.name  [经手人] ,                    '
    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' 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  #ExpenseList ( '
    +' [日期], [编号],[业务类别],[经手人] , [仓库名称], '
    +' [商品类别], [商品名称],[规格型号],[标准单位],'
    +' [入库标准数量], [入库标准单价],[入库金额],[入库包装单位],    '
    +' [入库包装数量], [入库包装单价]  )'
    +' select b.Date [日期], '
    +' b.Code [编号],                        '
    +' b.BillMode [业务类别],                '
    +' d.name  [经手人] ,                    '
    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' 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  #ExpenseList ( '
    +' [日期], [编号],[业务类别],[经手人] , [仓库名称], '
    +' [商品类别], [商品名称],[规格型号],[标准单位],'
    +' [出库标准数量], [出库标准单价],[出库金额],[出库包装单位],    '
    +' [出库包装数量], [出库包装单价]  )'
    +' select b.Date [日期], '
    +' b.Code [编号],                        '
    +' b.BillMode [业务类别],                '
    +' d.name  [经手人] ,                    '
    +' c.name [仓库名称],                    '
    +' h.name [商品类别],                    '
    +' e.name [商品名称],                    '
    +' a.GoodsSpec [规格型号],               '
    +' g.name [标准单位],                    '
    +' 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  #ExpenseList ( '
    +' [商品类别], [商品名称],[库存下限数量] ) '
    +' select h.name [商品类别], a.name [商品名称],  '
    +' a.StockMin [库存下限数量] '
    +' from DAGoods  a                   '
    +' left outer join DAGoodsClass  h on h.ID=a.GoodsClassID  '
    +' where a.RecordState<>'+ Quotedstr('删除');
  ADOQuery.ExecSQL;   }



  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 TSTNewStockRepForm.Button1Click(Sender: TObject);
begin
  Panel6.Visible :=True;
  Panel6.Repaint;
  Button1.Tag:=1 ;
  adsMaster.Close;
  adsMaster.CommandText:=' select *,(Isnull([入库标准数量],0)-Isnull([出库标准数量],0)) [结存标准数量], '
    +'(Isnull([入库金额],0)-Isnull([出库金额],0)) [结存金额] '
    +' from #ExpenseList'+WhereStr
    +' order by [日期], [编号]' ;
//  showmessage(adsMaster.CommandText);
  adsMaster.open;
  Panel6.Visible :=False;
  UpdateDBGrid;
  DBGrid.hint :='';
end;

procedure TSTNewStockRepForm.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);
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#StockListTtl')
        +' )) DROP TABLE #StockListTtl ' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select * into #StockListTtl from #ExpenseListTtl ';
  ADOQuery.ExecSQL;

end;

procedure TSTNewStockRepForm.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;

  if  pos('商品名称',SelectStr1)>0 then
  begin
1234

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -