stedpcgoodsincost.pas

详细的ERP设计资料

  TpcPanel.Repaint;
  BitBtn2.Enabled :=False;
  adsMaster.Edit;
  adsMaster.FieldByName('Apportion').AsString :=BitBtn2.Caption;
  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 PCGoodsInDetail a '
    +' left outer join PCGoodsInMaster 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('PCGoodsInMaster')+' ) '
    +' 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('PriceGoal').AsFloat    := TempQry.fieldbyname('PriceGoal').AsFloat;
    adsDetail.FieldByName('Amount').AsFloat   := TempQry.fieldbyname('Amount').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;
  BitBtn2.Enabled :=true;
  TpcPanel.Visible :=False;
end;


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

procedure TSTEdPCGoodsInCostForm.SpeedButton1Click(Sender: TObject);
begin
  TpcPanel.Visible :=true;
  TpcPanel.Repaint;
  SpeedButton1.Enabled :=False;
  TempQry.Close;
  TempQry.sql.text :=' select sum(isnull(SundryFee,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)) '
    +' as SundryFee from  '
    +' PCPurchaseMaster   '
    +' 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('PCPurchaseMaster')+' )';
  TempQry.Open;
  adsMaster.Edit;
  adsMaster.FieldByName('SundryFee').AsFloat :=TempQry.FieldByName('SundryFee').AsFloat;
  if adsMaster.FieldByName('SundryFee').AsFloat<>0 then SpeedButton2.Enabled :=True;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#PurchaseCostPrice')
        +' )) DROP TABLE #PurchaseCostPrice ' ;
  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 #PurchaseCostPrice '
    +' from PCPurchaseDetail a '
    +' left outer join PCPurchaseMaster b on b.ID=a.MasterID'
    +' 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('PCPurchaseMaster')+' ) '
    +' group by GoodsID order by GoodsID ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;//补充历史采购商品的移动平均单价
  ADOQuery.sql.text :=' Insert into #PurchaseCostPrice ('
    +' GoodsID,GoalQuantity ,Amount )'
    +' 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 Amount '
    +' from PCPurchaseDetail a '
    +' left outer join PCPurchaseMaster b on b.ID=a.MasterID'
    +' where b.Date<=' +Quotedstr(datetostr(adsMaster.fieldbyname('ClearDate').asdatetime))
    +' and b.RecordState<>'+Quotedstr('删除')
    +' and b.RecordState<>'+Quotedstr('作废')
    +' and GoodsID not in (Select GoodsID from #PurchaseCostPrice ) '
    +' group by GoodsID  order by GoodsID ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.sql.text :=' update #PurchaseCostPrice set PriceCost='
    +' Isnull(Amount,0)/GoalQuantity where Isnull(GoalQuantity,0)<>0 ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.sql.text :=' update #PurchaseCostPrice set PriceCost=0'
    +'  where Isnull(GoalQuantity,0)=0 ';
  ADOQuery.ExecSQL;
  if adsMaster.FieldByName('Apportion').AsString='加权平均' then BitBtn1Click(Sender);
  if adsMaster.FieldByName('Apportion').AsString='先进先出' then BitBtn2Click(Sender);

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

procedure TSTEdPCGoodsInCostForm.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('PCPurchaseMaster')
    +' as OriginTable from PCPurchaseMaster'
    +' 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('PCPurchaseMaster')+' )';
  TempQry.Execsql;
  TempQry.Close;//登记已计算采购成本的采购入库记录
  TempQry.SQL.Text :=' Insert into STCALCostListMaster '
    +' ( OriginID,OriginTable) '
    +' select ID,'+ Quotedstr('PCGoodsInMaster')
    +' as OriginTable from PCGoodsInMaster'
    +' 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('PCGoodsInMaster')+' )';
  TempQry.Execsql;

  TempQry.Close;     //更新采购入库商品成本单价
  TempQry.SQL.Text :=' Update PCGoodsInDetail set '
    +' PCGoodsInDetail.PriceGoal=STPCGoodsInCostDetail.PriceCost,'
    +' PCGoodsInDetail.Amount= '
    +' STPCGoodsInCostDetail.PriceCost*PCGoodsInDetail.GoalQuantity, '
    +' PCGoodsInDetail.PriceBase= '
    +' STPCGoodsInCostDetail.PriceCost*PCGoodsInDetail.GoalQuantity/ '
    +' PCGoodsInDetail.Quantity '
    +' from PCGoodsInDetail left outer join STPCGoodsInCostDetail  '
    +' on STPCGoodsInCostDetail.GoodsID=PCGoodsInDetail.GoodsID '
    +' and STPCGoodsInCostDetail.OriginID=PCGoodsInDetail.MasterID '
    +' where PCGoodsInDetail.MasterID in (select OriginID from '
    +' STPCGoodsInCostDetail where MasterID='
    + adsMaster.fieldbyname('ID').AsString   + ' )'  ;
  TempQry.Execsql;
  TpcPanel.Visible :=False;
end;

procedure TSTEdPCGoodsInCostForm.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 TSTEdPCGoodsInCostForm.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 TSTEdPCGoodsInCostForm.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 TSTEdPCGoodsInCostForm.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 TSTEdPCGoodsInCostForm.adsDetailSundryFeeChange(Sender: TField);
begin
  adsDetail.FieldByName('PriceCost').AsFloat :=
    (adsDetail.fieldbyname('Amount').AsFloat+adsDetail.fieldbyname('SundryFee').AsFloat)/
    adsDetail.fieldbyname('GoalQuantity').AsFloat;
end;

procedure TSTEdPCGoodsInCostForm.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 TSTEdPCGoodsInCostForm.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 TSTEdPCGoodsInCostForm.DBEdit4Exit(Sender: TObject);
begin
 if adsMaster.FieldByName('IndirectFee').AsFloat<>0 then SpeedButton2.Enabled :=True;

end;

end.