stedydgoodsoutcost.pas

详细的ERP设计资料

  begin
    ADOQuery.Close;
    ADOQuery.SQL.Text :='select sum(Isnull(GoalQuantity,0)*Isnull(ModeDC,1)*'
     +' Isnull(ModeC,1)) '
     +' as GoalQuantity from '
     +' YDGoodsOutDetail a '
     +' Left outer join YDGoodsOutMaster b on b.ID=a.MasterID '
     +' where b.Recordstate <>'+Quotedstr('删除')
     +' and b.Recordstate <>'+Quotedstr('作废')
     +' and GoodsID='+adsDetail.fieldbyname('GoodsID').AsString;
    ADOQuery.Open;
    TotalGoalQuantity :=ADOQuery.fieldbyname('GoalQuantity').AsFloat;
    ADOQuery.Close;
    ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
          +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#TempCostPrice')
          +' )) DROP TABLE #TempCostPrice ' ;
    ADOQuery.ExecSQL;
    ADOQuery.Close;
    ADOQuery.SQL.Text :='select a.ID,GoodsID, '
     +' Isnull(GoalQuantity,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) as GoalQuantity ,'
     +' PriceGoal,'
     +' Isnull(Amount,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) as Amount '
     +' into #TempCostPrice '
     +' from '
     +' PCGoodsInDetail  a '
     +' Left outer join PCGoodsInMaster b on b.ID=a.MasterID '
     +' where GoodsID='+adsDetail.fieldbyname('GoodsID').AsString
     +' and b.Recordstate <>'+Quotedstr('删除')
     +' and b.Recordstate <>'+Quotedstr('作废')
     +' Order By a.ID ' ;
    ADOQuery.ExecSQL;
    ADOQuery.Close;
    ADOQuery.SQL.Text :='select * from #TempCostPrice ';
    ADOQuery.open;
    ADOQuery.First;
    while not ADOQuery.Eof do
    begin
      if TotalGoalQuantity>ADOQuery.fieldbyname('GoalQuantity').AsFloat then
      begin
        TotalGoalQuantity :=TotalGoalQuantity-ADOQuery.fieldbyname('GoalQuantity').AsFloat ;
        ADOQuery.Delete ;
      end else
      begin
        ADOQuery.edit;
        ADOQuery.fieldbyname('GoalQuantity').AsFloat :=
           ADOQuery.fieldbyname('GoalQuantity').AsFloat-TotalGoalQuantity;
        Break;
      end;
    end;
    TotalGoalQuantity :=adsDetail.FieldByName('GoalQuantity').AsFloat ;
    TotalAmount :=0;
    ADOQuery.First;
    while not ADOQuery.Eof do
    begin
      if TotalGoalQuantity>adsDetail.FieldByName('GoalQuantity').AsFloat then
      begin
        TotalAmount :=TotalAmount+adsDetail.FieldByName('GoalQuantity').AsFloat*
          ADOQuery.FieldByName('PriceGoal').AsFloat;
        TotalGoalQuantity :=TotalGoalQuantity-adsDetail.FieldByName('GoalQuantity').AsFloat;
      end else
      begin
        TotalAmount :=TotalGoalQuantity*ADOQuery.FieldByName('PriceGoal').AsFloat;
        Break;
      end;
    end;
  adsDetail.Edit;
  adsDetail.FieldByName('Amount').AsFloat := TotalAmount;
  adsDetail.FieldByName('PriceGoal').AsFloat :=TotalAmount/adsDetail.FieldByName('GoalQuantity').AsFloat;
  end;
begin
  TpcPanel.Visible :=true;
  TpcPanel.Repaint;
  BitBtn2.Enabled :=False;
  adsMaster.Edit;
  adsMaster.FieldByName('Apportion').AsString :=BitBtn2.Caption;

  DetailDataSource.DataSet :=nil;
  adsDetail.First;
  while not adsDetail.Eof do
  begin
    FindNewPrice;
    adsDetail.Next;
  end;
  DetailDataSource.DataSet :=adsDetail;
  BitBtn2.Enabled :=true;
  TpcPanel.Visible :=False;
end;


procedure TSTEdYDGoodsOutCostForm.BitBtn4Click(Sender: TObject);
begin
  adsMaster.Edit;
  adsMaster.FieldByName('Apportion').AsString :=BitBtn4.Caption;
end;

procedure TSTEdYDGoodsOutCostForm.SpeedButton1Click(Sender: TObject);
begin
  TpcPanel.Visible :=true;
  TpcPanel.Repaint;
  SpeedButton1.Enabled :=False;

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

  ADOQuery.Close;//计算领料开单商品的移动平均（指定时间段内）
  ADOQuery.sql.text :=' select GoodsID, '
    +' sum(isnull(a.GoalQuantity,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1)) as GoalQuantity ,'
    +' sum(isnull(a.Amount,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1)) as PriceCost , '
    +' sum(isnull(a.Amount,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1)) as Amount '
    +' into #YDGoodsOutCostPrice '
    +' from PCGoodsInDetail a '
    +' left outer join PCGoodsInMaster b on b.ID=a.MasterID'
    +' where b.Date<=' +Quotedstr(datetostr(adsMaster.fieldbyname('ClearDate').asdatetime))
    +' and b.RecordState<>'+Quotedstr('删除')
    +' and b.RecordState<>'+Quotedstr('作废')
    +' group by GoodsID order by GoodsID ';
  ADOQuery.ExecSQL;


  ADOQuery.Close;
  ADOQuery.sql.text :=' update #YDGoodsOutCostPrice set PriceCost='
    +' Isnull(Amount,0)/GoalQuantity where Isnull(GoalQuantity,0)<>0 ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.sql.text :=' update #YDGoodsOutCostPrice set PriceCost=0'
    +'  where Isnull(GoalQuantity,0)=0 ';
  ADOQuery.ExecSQL;

//  BitBtn1Click(sender);//默认加权平均法计算单价

  TempQry.Close;
  TempQry.sql.text :=' select GoodsID,c.UnitID GoalUnitID,a.MasterID OriginID,'
    +' (b.Code) as Memo,a.PriceGoal, '
    +' sum(isnull(a.GoalQuantity,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1)) as GoalQuantity, '
    +' sum(isnull(a.Amount,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1)) as Amount '
    +' from YDGoodsOutDetail a '
    +' left outer join YDGoodsOutMaster b on b.ID=a.MasterID'
    +' left outer join DAGoods         c on c.ID=a.GoodsID'
    +' where b.Date<=' +Quotedstr(datetostr(adsMaster.fieldbyname('ClearDate').asdatetime))
    +' and b.RecordState<>'+Quotedstr('删除')+' and b.RecordState<>'+Quotedstr('作废')
    +' and MasterID not in (Select OriginID from STCALCostListMaster '
    +' where OriginTable=' +Quotedstr('YDGoodsOutMaster')+' ) '
    +' group by a.GoodsID,c.UnitID,a.MasterID,b.Code,a.PriceGoal ';
  TempQry.Open;
  DetailDataSource.DataSet :=nil;
  while not adsDetail.Eof do adsDetail.Delete;
  while not TempQry.Eof do
  begin
    if adsDetail.Eof then adsDetail.Append;
    adsDetail.FieldByName('GoodsID').AsInteger := TempQry.fieldbyname('GoodsID').AsInteger;
    adsDetail.FieldByName('GoalUnitID').AsInteger := TempQry.fieldbyname('GoalUnitID').AsInteger;
    adsDetail.FieldByName('GoalQuantity').AsFloat := TempQry.fieldbyname('GoalQuantity').AsFloat;
    adsDetail.FieldByName('Amount').AsFloat := TempQry.fieldbyname('Amount').AsFloat;
    adsDetail.FieldByName('PriceGoal').AsFloat :=
      adsDetail.FieldByName('Amount').AsFloat/TempQry.fieldbyname('GoalQuantity').AsFloat;
    adsDetail.FieldByName('OriginID').AsFloat := TempQry.fieldbyname('OriginID').AsFloat;
    adsDetail.FieldByName('Memo').AsString    := '领料单:'+Trim(TempQry.fieldbyname('Memo').AsString);
    TempQry.Next;
  end;
  adsDetail.Append;
  DetailDataSource.DataSet :=adsDetail;
  TpcPanel.Visible :=False;
  if adsMaster.FieldByName('Apportion').AsString='加权平均' then BitBtn1Click(Sender);
  if adsMaster.FieldByName('Apportion').AsString='先进先出' then BitBtn2Click(Sender);
  if adsMaster.FieldByName('Apportion').AsString='后进先出' then BitBtn3Click(Sender);

  TempQry.Close;
  TempQry.sql.text :='select top 1 Apportion  from '
    +' STYDGoodsOutCostMaster where RecordState<>'
    + Quotedstr('删除') +' and RecordState<>'+Quotedstr('作废');
  TempQry.Open;
  if TempQry.IsEmpty then
  begin
    BitBtn1.Enabled :=True ;
    BitBtn2.Enabled :=True ;
    BitBtn3.Enabled :=True ;
  end;


  SpeedButton1.Enabled :=True;
  SpeedButton2.Enabled :=True;
end;

procedure TSTEdYDGoodsOutCostForm.SpeedButton3Click(Sender: TObject);
begin
  if adsDetail.IsEmpty then exit;
  TpcPanel.Visible :=true;
  TpcPanel.Repaint;
  TempQry.Close;//登记已计算领料成本的领料开单记录
  TempQry.SQL.Text :=' Insert into STCALCostListMaster '
    +' ( OriginID,OriginTable) '
    +' select ID,'+ Quotedstr('YDGoodsOutMaster')
    +' as OriginTable from YDGoodsOutMaster'
    +' where Date<=' +Quotedstr(Datetostr(adsMaster.fieldbyname('ClearDate').asdatetime))
    +' and RecordState<>'+Quotedstr('删除')+' and RecordState<>'+Quotedstr('作废')
    +' and ID not in (Select OriginID from STCALCostListMaster '
    +' where OriginTable='+Quotedstr('YDGoodsOutMaster')+' )';
  TempQry.Execsql;

  TempQry.Close;     //更新领料入库商品成本单价 YDGoodsOutMaster
  TempQry.SQL.Text :=' Update YDGoodsOutDetail set '

    +' YDGoodsOutDetail.PriceGoal=STYDGoodsOutCostDetail.PriceCost,'
    +' YDGoodsOutDetail.Amount= '
    +' STYDGoodsOutCostDetail.PriceCost*YDGoodsOutDetail.GoalQuantity, '
    +' YDGoodsOutDetail.PriceBase= '
    +' STYDGoodsOutCostDetail.PriceCost*YDGoodsOutDetail.GoalQuantity/ '
    +' YDGoodsOutDetail.Quantity '
    +' from YDGoodsOutDetail left outer join STYDGoodsOutCostDetail  '
    +' on STYDGoodsOutCostDetail.GoodsID=YDGoodsOutDetail.GoodsID '
    +' and STYDGoodsOutCostDetail.OriginID=YDGoodsOutDetail.MasterID '
    +' where YDGoodsOutDetail.MasterID in (select OriginID from '
    +' STYDGoodsOutCostDetail where MasterID='
    + adsMaster.fieldbyname('ID').AsString   + ' )'  ;
  TempQry.Execsql;
  TpcPanel.Visible :=False;
end;

procedure TSTEdYDGoodsOutCostForm.SpeedButton2Click(Sender: TObject);
var
  Point: TPoint;
begin
  with TControl(Sender) do
    Point := Parent.ClientToScreen(Types.Point(Left, Top + Height));
  FeeAportPopupMenu.Popup(Point.X, Point.Y);

  if adsDetail.IsEmpty then exit;
end;

procedure TSTEdYDGoodsOutCostForm.MenuItem1Click(Sender: TObject);
var TtlAmount,SundryFee :real;
begin
  adsMaster.Edit;
  adsMaster.fieldbyname('Memo').AsString :='按金额分摊';
  TpcPanel.Visible :=True;
  TpcPanel.Repaint ;
  SundryFee :=adsMaster.fieldbyname('SundryFee').AsFloat+adsMaster.fieldbyname('IndirectFee').AsFloat;
  TtlAmount := DBGrid.AggregateList.Aggregates.FindAggregate(atSum, 'Amount').AggregateValue;
  if TtlAmount=0 then TtlAmount :=1;
  DetailDataSource.DataSet :=nil;
  adsDetail.first;
  while not adsDetail.eof do
  begin
    adsDetail.edit;
    adsDetail.FieldByName('SundryFee').AsFloat :=
       SundryFee*(adsDetail.fieldbyname('Amount').AsFloat/TtlAmount);
    adsDetail.next;
  end;
  DetailDataSource.DataSet :=adsDetail;
  TpcPanel.Visible :=False;
end;

procedure TSTEdYDGoodsOutCostForm.MenuItem2Click(Sender: TObject);
var TtlAmount,SundryFee :real;
begin
  adsMaster.Edit;
  adsMaster.fieldbyname('Memo').AsString :='按数量分摊';
  TpcPanel.Visible :=True;
  TpcPanel.Repaint ;
  SundryFee :=adsMaster.fieldbyname('SundryFee').AsFloat+adsMaster.fieldbyname('IndirectFee').AsFloat;
  TtlAmount :=DBGrid.AggregateList.Aggregates.FindAggregate(atSum, 'GoalQuantity').AggregateValue;
  if TtlAmount=0 then TtlAmount :=1;
  DetailDataSource.DataSet :=nil;
  adsDetail.first;
  while not adsDetail.eof do
  begin
    adsDetail.edit;
    adsDetail.FieldByName('SundryFee').AsFloat :=
       SundryFee*(adsDetail.fieldbyname('GoalQuantity').AsFloat/TtlAmount);
    adsDetail.next;
  end;
  DetailDataSource.DataSet :=adsDetail;
  TpcPanel.Visible :=False;
end;

procedure TSTEdYDGoodsOutCostForm.ClearSundryFeeActClick(Sender: TObject);
begin
  adsMaster.Edit;
  adsMaster.fieldbyname('Memo').AsString :='';
  DetailDataSource.DataSet :=nil;
  adsDetail.first;
  while not adsDetail.eof do
  begin
    adsDetail.edit;
    adsDetail.FieldByName('SundryFee').value :=null;
    adsDetail.next;
  end;

  DetailDataSource.DataSet :=adsDetail;
end;

procedure TSTEdYDGoodsOutCostForm.adsDetailSundryFeeChange(Sender: TField);
begin
  adsDetail.FieldByName('PriceCost').AsFloat :=
    (adsDetail.fieldbyname('Amount').AsFloat+adsDetail.fieldbyname('SundryFee').AsFloat)/
    adsDetail.fieldbyname('GoalQuantity').AsFloat;
end;

procedure TSTEdYDGoodsOutCostForm.SaveActionExecute(Sender: TObject);
var MessStr :Real;
begin
  TpcPanel.Visible :=true;
  TpcPanel.Repaint;

  MessStr := DBGrid.AggregateList.Aggregates.FindAggregate(atSum, 'SundryFee').AggregateValue;
//  showmessage(' 差额为:'+Floattostr(MessStr));
  if  MessStr=0 then
      MessStr :=adsMaster.FieldByName('SundryFee').AsFloat+adsMaster.FieldByName('IndirectFee').AsFloat
     else MessStr :=MessStr-adsMaster.FieldByName('SundryFee').AsFloat-adsMaster.FieldByName('IndirectFee').AsFloat;

  if  MessStr<>0 then
  begin
    showmessage('分摊后金额合计有差异,差额为:'+
    Copy(Floattostr(MessStr),1,Pos( '.',Trim(Floattostr(MessStr)))+3)) ;
    adsMaster.edit;
    adsMaster.FieldByName('Memo').asstring := Trim(adsMaster.FieldByName('Memo').asstring)
      +', 分摊差异:'+Copy(Floattostr(MessStr),1,Pos( '.',Trim(Floattostr(MessStr)))+3);
  end;
  inherited;
  SpeedButton3Click(Sender);
  TpcPanel.Visible :=False;

end;

procedure TSTEdYDGoodsOutCostForm.adsDetailPriceGoalChange(Sender: TField);
begin
  adsDetail.FieldByName('Amount').AsFloat :=
    adsDetail.fieldbyname('PriceGoal').AsFloat*adsDetail.fieldbyname('GoalQuantity').AsFloat;

  adsDetail.FieldByName('PriceCost').AsFloat :=
    (adsDetail.fieldbyname('Amount').AsFloat+adsDetail.fieldbyname('SundryFee').AsFloat)/
    adsDetail.fieldbyname('GoalQuantity').AsFloat;
end;

procedure TSTEdYDGoodsOutCostForm.DBEdit4Exit(Sender: TObject);
begin
 if adsMaster.FieldByName('IndirectFee').AsFloat<>0 then SpeedButton2.Enabled :=True;

end;

end.