egmaterialbalance.pas

详细的ERP设计资料

    +' and GoalQuantity<>0 ' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;  // 调出记录
  ADOQuery.SQL.Text :=' INSERT INTO #EGMaterialBalanceDM ('
    +' ID,Date,Code,ClientID,EmployeeID,                         '
    +' BillMode,ModeDC,ModeC,Brief,                             '
    +' GoodsID,GoodsSpec, Quantity,QuantityPcs,PackUnitID,      '
    +' PriceBase,AmountOut,GoalUnitID,GoalQuantityOut,Discount,PriceCost,'
    +' [ProjectName],[Employee],[Goods],[GoodsClass],[DAArea],  '
    +' [Unit],[WareHouse],GoalQuantity )'
    +' select  b.id,b.Date,b.Code,b.WareHouseID,b.EmployeeID,           '
    +' b.BillMode,b.ModeDC,b.ModeC,b.Brief,                     '
    +' a.GoodsID,a.GoodsSpec, a.Quantity,a.QuantityPcs,a.PackUnitID, '
    +' a.PriceBase,a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1),'
    +' a.GoalUnitID,a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1), Discount,0,'
    +' c.name Client,d.Name Employee,e.Name Goods,f.Name GoodsClass, '
    +' g.Name DAArea,h.name Unit ,j.name WareHouse,0.00000000001'
    +' from STGoodsOutInDetail  AS a                                       '
    +' LEFT OUTER JOIN STGoodsOutInmaster as b  on a.MasterID=b.id         '
    +' LEFT OUTER JOIN STWareHouse     as c  on b.WareHouseID=c.id         '
    +' LEFT OUTER JOIN MSEmployee   as d  on b.EmployeeID=d.id       '
    +' LEFT OUTER JOIN DAGoods      as e  on a.GoodsID=e.id          '
    +' LEFT OUTER JOIN DAGoodsClass as f  on e.GoodsClassID=f.id     '
    +' LEFT OUTER JOIN DAarea       as g  on c.ID=g.id           '
    +' LEFT OUTER JOIN MSUnit       as h  on E.UnitID=h.id       '
    +' LEFT OUTER JOIN STWareHouse  as j  on b.ClientID=j.id       '
    +' where b.Recordstate<>'+Quotedstr('删除')
    +' and GoodsID<>0 and not GoodsID is null'
    +' and GoalQuantity<>0 ' ;
  ADOQuery.ExecSQL;

  {ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #EGMaterialOutDM set '
        +' GrossProfit =GoalQuantity*Isnull(ModeDC,1)*Isnull(ModeC,1)*(PriceBase-PriceCost)';
  ADOQuery.ExecSQL;  }


  ADOQuery.Close;
  ADOQuery.SQL.Text :=' if exists (select * from dbo.sysobjects where '
      +' id = object_id '
      +' (' +Quotedstr('[dbo].[TempSaleMD]')+') and OBJECTPROPERTY(id,'
      +Quotedstr('IsUserTable')+' ) = 1) drop table [dbo].[TempSaleMD] ';
  ADOQuery.ExecSQL;
  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] (30)  NULL , '
    +' [GoodsClass] [varchar] (30)  NULL ,[DAArea] [varchar] (30)  NULL , '
    +' [WareHouse] [varchar] (30)  NULL , '
    +' [ProjectName] [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 TEGMaterialBalanceForm.N1Click(Sender: TObject);
var E:string;
begin
  if adsMaster.IsEmpty then exit;
end;

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


procedure TEGMaterialBalanceForm.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 TEGMaterialBalanceForm.CheckBox1Click(Sender: TObject);
begin
  inherited;
  if not CheckBox1.Checked and
     not CheckBox2.Checked   and not CheckBox3.Checked
     and not CheckBox4.Checked
     then CheckBox1.Checked:=not CheckBox1.Checked;
end;

procedure TEGMaterialBalanceForm.Button1Click(Sender: TObject);
var I :integer;
begin
  inherited;
  Button1.Tag :=1;
  Panel6.Visible :=True;
  Panel6.Repaint;
  adsMaster.Close;
  adsMaster.CommandText:=' select date [日期],'
  +' code [编号],BillMode [业务类型],Client [进料单位],'
  +' ProjectName [用料项目],'
  +' WareHouse [仓库名称],GoodsClass [材料类别],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 #EGMaterialBalanceDM    '
  +WhereStr ;
  adsMaster.open;

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

procedure TEGMaterialBalanceForm.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+' GoodsClass  ,' ;
    SelectStr1 :=SelectStr1+' GoodsClass [材料类别],' ;
    GroupStr :=GroupStr+' GoodsClass , '             ;
  end;
  if CheckBox4.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 #EGMaterialBalanceTtl';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' insert into #EGMaterialBalanceTtl ( '
    + 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 #EGMaterialBalanceDM  '
    + WhereStr +' group by '+GroupStr;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #EGMaterialBalanceTtl set  '
    + ' Price = Amount/GoalQuantity where GoalQuantity<>0 '
    +' and GoalQuantity is not null ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #EGMaterialBalanceTtl set  '
    + ' PriceOut = AmountOut/GoalQuantityOut where GoalQuantityOut<>0 '
    +' and GoalQuantityOut is not null ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' update #EGMaterialBalanceTtl 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 #EGMaterialBalanceTtl  ' ;
  adsMaster.open;

  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 CheckBox3.Checked then DBGrid.hint := DBGrid.hint+CheckBox3.Caption+',';
  if trim(DBGrid.hint)<>'' then
    DBGrid.hint :='汇总项目:'+Copy(Trim(DBGrid.hint),1,Length(Trim(DBGrid.hint))-1 );
end;

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

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

end.