egedmaterialingtp.pas

详细的ERP设计资料

  inherited;
  if (adsDetail.fieldbyname('GoodsID').IsNull) or (adsDetail.fieldbyname('GoodsID').AsInteger=0)
     then  SGoodsID :=1 else  SGoodsID :=adsDetail.fieldbyname('GoodsID').AsInteger;
  TempAds.Close;   //取标准单位和基本单价
  TempAds.CommandText :='select UnitID,PriceSales,PricePurchase  from DAGoods where Id='
      + Inttostr(SGoodsID);
  TempAds.Open;
  if  TempAds.FieldByName('UnitID').IsNull then SunitID :=1
    else SunitID :=TempAds.FieldByName('UnitID').AsInteger;

//  adsDetail.FieldByName('GoodsSpec').ReadOnly :=False;
  adsDetail.FieldByName('GoodsSpec').AsString  :=GoodName.fieldbyname('Spec').AsString;

//  adsDetail.FieldByName('GoalUnitID').ReadOnly :=False;
  adsDetail.FieldByName('GoalUnitID').AsInteger :=SunitID;
//  adsDetail.FieldByName('GoalUnitID').ReadOnly :=True;

//  adsDetail.FieldByName('PriceBase').ReadOnly :=False;
  if adsDetail.FieldByName('PriceBase').AsFloat=0 then
     adsDetail.FieldByName('PriceBase').AsFloat := TempAds.FieldByName('PricePurchase').AsFloat  ;
  if adsDetail.FieldByName('PriceBase').IsNull then adsDetail.FieldByName('PriceBase').AsFloat :=0;

  IsInGoods :=0;
  TpPackUnit.Close;
  TpPackUnit.SQL.Text :='Truncate Table #TempPackUnit5 ';
  TpPackUnit.ExecSQL;
  TpPackUnit.Close;
  TpPackUnit.SQL.Text :=' select a.AddUnitID ID, b.name,a.AddUnitRate '
    +' ExchangeRate,a.UnitID GoalUnitID,b.IsGoalUnit '
    +' from DAGoods a    '
    +' left outer join MSUnit b on b.ID=a.AddUnitID    '
    +' where a.AddUnitID is not null and AddUnitID<>0 and a.AddUnitRate '
    +' is not null and a.AddUnitRate<>0 and a.ID= '+inttostr(SGoodsID) ;
  TpPackUnit.Open;
  if not TpPackUnit.IsEmpty then
  begin
    IsInGoods :=1;
    TpPackUnit.Close;   //将商品资料表的单位换算率插入  #TempPackUnit5
    TpPackUnit.SQL.Text :=' insert into #TempPackUnit5 '
      +' (ID, Name, ExchangeRate, GoalUnitID, IsGoalUnit )'
      +'select a.AddUnitID ID, b.name,a.AddUnitRate '
      +' ExchangeRate,a.UnitID GoalUnitID,b.IsGoalUnit '
      +' from DAGoods a    '
      +' left outer join MSUnit b on b.ID=a.AddUnitID    '
      +' where a.AddUnitID is not null and AddUnitID<>0 and a.AddUnitRate '
      +' is not null and a.AddUnitRate<>0 and a.ID= '+inttostr(SGoodsID) ;
    TpPackUnit.ExecSQL;
  end;
  TpPackUnit.Close;    //将单位资料表的换算率插入  #TempPackUnit5
  TpPackUnit.SQL.Text :=' insert into #TempPackUnit5 '
      +' (ID, Name, ExchangeRate, GoalUnitID, IsGoalUnit )  '
      +' select ID, Name, ExchangeRate, GoalUnitID, IsGoalUnit '
      +' from MSUnit where RecordState<>'+QuotedStr('删除')
      +' Order by GoalUnitID,IsGoalUnit desc ';
  TpPackUnit.ExecSQL;
  PackUnit.Close;  //显示单位列表与换算率
  PackUnit.CommandText :=' select PID, ID, Name, ExchangeRate, GoalUnitID, IsGoalUnit '
      +' from #TempPackUnit5 Order by PID';
  PackUnit.Open;
  if IsInGoods=0 then
  begin
   PackUnit.Locate('ID',Inttostr(SunitID),[]);
   if not PackUnit.Eof then PackUnit.Next;
  end else   PackUnit.First;
  adsDetail.FieldByName('PackUnitID').AsInteger :=PackUnit.FieldByName('ID').AsInteger;
end;


procedure TEGEdMaterialInGTPForm.adsDetailPackUnitIDChange(Sender: TField);
var ExChRate :real;
begin
  PackUnit.Locate('ID', adsDetail.fieldbyname('PackUnitID').AsString, []);
  if PackUnit.fieldbyname('ExchangeRate').IsNull then ExChRate :=1
     else ExChRate :=PackUnit.fieldbyname('ExchangeRate').AsFloat ;
//  adsDetail.FieldByName('Quantity').ReadOnly :=False;
  adsDetail.FieldByName('Quantity').AsFloat :=
       adsDetail.fieldbyname('GoalQuantity').AsFloat/ExChRate;
//  adsDetail.FieldByName('Quantity').ReadOnly :=True;
end;

procedure TEGEdMaterialInGTPForm.adsDetailPriceBaseChange(Sender: TField);
begin
  inherited;
//  adsDetail.FieldByName('Amount').ReadOnly :=False;
  adsDetail.FieldByName('Amount').AsFloat :=
       adsDetail.fieldbyname('GoalQuantity').asfloat
         *adsDetail.fieldbyname('PriceBase').AsFloat;
//  adsDetail.FieldByName('Amount').ReadOnly :=True;
end;


procedure TEGEdMaterialInGTPForm.adsDetailGoalQuantityChange(Sender: TField);
var   ExChRate :real;
begin
  inherited;
  if PackUnit.fieldbyname('ExchangeRate').IsNull then ExChRate :=1
     else ExChRate :=PackUnit.fieldbyname('ExchangeRate').AsFloat;
//  adsDetail.FieldByName('Quantity').ReadOnly :=False;
  adsDetail.FieldByName('Quantity').AsFloat :=
       adsDetail.fieldbyname('GoalQuantity').AsFloat/ExChRate;
//  adsDetail.FieldByName('Quantity').ReadOnly :=True;

//  adsDetail.FieldByName('Amount').ReadOnly :=False;
  adsDetail.FieldByName('Amount').AsFloat :=
       adsDetail.fieldbyname('GoalQuantity').asfloat
         *adsDetail.fieldbyname('PriceBase').AsFloat;
//  adsDetail.FieldByName('Amount').ReadOnly :=True;
end;

procedure TEGEdMaterialInGTPForm.MaterialBalanceClick(Sender: TObject);
begin
  inherited;
  ShowQueryForm(Caption,Hint,
        '  select  d.name [仓库名称], '
     +' e.name [材料名称],                                    '
     +' g.name [标准单位],                                    '
     +' sum(isnull(a.GoalQuantity,0) ) [标准数量] ,           '
     +' sum(isnull(a.Amount,0))/sum(isnull(a.GoalQuantity,1))  [平均单价] , '
     +' sum(isnull(a.Amount,0)) [金额] ,                       '
     +' f.name [包装单位],                                     '
     +' sum(isnull(a.Quantity,0)) [包装数量]                   '
     +' from ( select GoodsID,GoalUnitID,                      '
     +' b.WareHouseID,                                         '
     +' GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) GoalQuantity ,           '
     +' PriceBase,                                             '
     +' Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) Amount,                        '
     +' Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) Quantity,                    '
     +' PackUnitID                                             '
     +' from EGMaterialInDetail a                              '
     +' left outer join EGMaterialInMaster b on b.Id=a.MasterID'
     +' where GoodsID<>0 and RecordState<>'+Quotedstr('删除')
     +' UNION  ALL                                             '
     +' select GoodsID,GoalUnitID,                             '
     +' b.WareHouseID,                                         '
     +' GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1)*(-1) GoalQuantity,       '
     +' PriceBase,                                             '
     +' Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1)*(-1) Amount,                   '
     +' Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1)*(-1) Quantity,               '
     +' PackUnitID                                             '
     +' from EGMaterialOutDetail a                             '
     +' left outer join EGMaterialOutMaster b on b.Id=a.MasterID  '
     +' where GoodsID<>0 and RecordState<>'+Quotedstr('删除')
     +'  ) as a                                                      '
     +' left outer join  STWareHouse   d  on d.ID=a.WareHouseID   '
     +' 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        '
     +' group by d.name ,e.name ,g.name ,f.name                   ')
end;

procedure TEGEdMaterialInGTPForm.SaveActionExecute(Sender: TObject);
var adoTemp: TADOQuery;
    VID,VMasterID,VWarehouseID :string;
begin
  if (adsMaster.FieldByName('ID').AsInteger <0) or
    (adsMaster.FieldByName('ID').IsNull ) then  VID :='-1'
  else VID :=adsMaster.FieldByName('ID').AsString;
  if adsMaster.FieldByName('WarehouseID').IsNull then  VWarehouseID :='0'
    else VWarehouseID :=adsMaster.FieldByName('WarehouseID').AsString;
  inherited;
  if not CheckBox1.Checked then exit;
  adoTemp := TADOQuery.Create(nil);
  adoTemp.Connection := CommonData.acnConnection;
  if VID ='-1' then
  begin
    adoTemp.Close;
    adoTemp.SQL.Text :=' select Max(ID) as ID from EGMaterialInMaster ';
    adoTemp.open;
    if (adoTemp.FieldByName('ID').IsNull) Or  (adoTemp.FieldByName('ID').AsInteger=0 )
      then VID :='-1'
      else VID :=adoTemp.FieldByName('ID').AsString;
  end;
  if VID ='-1' then exit;
  adoTemp.Close;
  adoTemp.SQL.Text :=' select * from EGMaterialOutMaster '
     +' WHERE OriginID = '+VID;
  adoTemp.open;
  if not adoTemp.IsEmpty  then exit;
  adoTemp.Close;
  adoTemp.SQL.Text :=' insert into EGMaterialOutMaster '
        +' ( Date,Code,BillMode,Brief,OriginID,WareHouseID )  VALUES  '
        +' ( '+Quotedstr(datetostr(adsMaster.FieldByName('Date').AsDateTime))+' , '
        + Quotedstr(GetMaxCode('Code','EGMaterialOutMaster',number))+' , '
        + Quotedstr('项目领料')+' , '
        + Quotedstr('直接用料')+' , '
        + VID+' , '
        + VWarehouseID+' ) ';
//  showmessage(adoTemp.SQL.Text);
  adoTemp.ExecSQL;

  adoTemp.Close;
  adoTemp.SQL.Text :=' select ID from EGMaterialOutMaster '
     +' WHERE OriginID = '+VID;
  adoTemp.open;


  if not adoTemp.FieldByName('ID').IsNull then
        VMasterID := adoTemp.FieldByName('ID').AsString
    else VMasterID := '-1';
//  showmessage(VMasterID);
  adoTemp.Close;
  adoTemp.SQL.Text :=' insert into EGMaterialOutDetail '
    +' ( MasterID, GoodsID, GoodsSpec, Quantity, QuantityPcs,          '
    +' PackUnitID, PriceBase, PriceCost, Amount, Discount, TaxAmount,  '
    +' SundryFee, GoalUnitID, GoalQuantity, Memo )                     '
    +' select '+ VMasterID + ' as MasterID, GoodsID, GoodsSpec, Quantity, QuantityPcs,           '
    +' PackUnitID, PriceBase, PriceCost, Amount, Discount, TaxAmount,  '
    +' SundryFee, GoalUnitID, GoalQuantity, Memo                       '
    +' from EGMaterialInDetail                                         '
    +' where MasterID='+VID;
//  showmessage(adoTemp.SQL.Text);

  adoTemp.ExecSQL;
end;

end.