pcedpurchase.pas

详细的ERP设计资料

  adsDetail.FieldByName('PriceBase').AsFloat := TempAds.FieldByName('PriceGoal').AsFloat  ;
end;

procedure TPCEdPurchaseForm.adsDetailQuantityChange(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('Amount').AsFloat :=
    adsDetail.fieldbyname('Quantity').asfloat*adsDetail.fieldbyname('PriceBase').AsFloat;

  adsDetail.FieldByName('GoalQuantity').AsFloat :=adsDetail.fieldbyname('Quantity').AsFloat*ExChRate;
  adsDetail.FieldByName('PriceGoal').AsFloat :=
    adsDetail.fieldbyname('Amount').AsFloat/adsDetail.fieldbyname('GoalQuantity').asfloat;
end;

procedure TPCEdPurchaseForm.ClientQLDBLookupEnter(Sender: TObject);
begin
  inherited;
  GEdit1.Enabled :=true;
  GEdit1.Text :='0';
  GEdit1.Enabled :=false;
end;

procedure TPCEdPurchaseForm.ClientQLDBLookupExit(Sender: TObject);
var  adoTemp: TADOQuery;
begin
  inherited;
  if   (adsMaster.fieldbyname('ClientID').IsNull) or
          (adsMaster.fieldbyname('ClientID').AsInteger=0)  then exit;
  adoTemp := TADOQuery.Create(nil);
  adoTemp.Connection := CommonData.acnConnection;
  with adoTemp do
  begin
    close;
    sql.Text :=' select sum(isnull(AmountD,0))*(-1) as Balance from '
    +' (  select (isnull(a.amount,0)-isnull(a.Discount,0)+isnull(a.TaxAmount,0) '
    +' +isnull(a.SundryFee,0) )*Isnull(ModeDC,1)*Isnull(ModeC,1) as amountD                         '
    +' from  SLSaleDetail a                                                     '
    +' left outer join  SLSaleMaster b on b.ID=a.MasterID                       '
    +' where b.RecordState<>'+Quotedstr('删除')+' and b.ClientID='
    + adsMaster.fieldbyname('ClientID').AsString
    +' UNION ALL                                                   '
    +' select (isnull(a.amount,0)-isnull(a.Discount,0)+isnull(a.TaxAmount,0) '
    +' +isnull(a.SundryFee,0) )*Isnull(ModeDC,1)*Isnull(ModeC,1)*(-1) as amountD                 '
    +' from  PCPurchaseDetail a                                              '
    +' left outer join  PCPurchaseMaster b on b.ID=a.MasterID                '
    +' where b.RecordState<>'+Quotedstr('删除')+' and b.ClientID='
    + adsMaster.fieldbyname('ClientID').AsString
    +' UNION ALL                                                             '
    +' select (Isnull(AmountD,0)+Isnull(AmountRed,0) )*Isnull(ModeDC,1)*Isnull(ModeC,1)*(-1)     '
    +' as AmountD                                                            '
    +' from FNClearPCMaster                                                  '
    +' where RecordState<>'+Quotedstr('删除')+' and ClientID='
    + adsMaster.fieldbyname('ClientID').AsString
    +' UNION ALL                                                             '
    +' select (Isnull(AmountC,0)+Isnull(AmountRed,0) )*Isnull(ModeDC,1)*Isnull(ModeC,1)          '
    +' as AmountD                                                            '
    +' from FNClearPCMaster                                                  '
    +' where RecordState<>'+Quotedstr('删除')+' and ClientID='
    + adsMaster.fieldbyname('ClientID').AsString +'  ) as a ';
    open;
    GEdit1.Enabled :=true;
    if  adoTemp.IsEmpty then     GEdit1.Text :='0'
      else   GEdit1.Text :=fieldbyname('Balance').asstring;
    GEdit1.Enabled :=False;
  end;
end;

procedure TPCEdPurchaseForm.LookupPackUintEnter(Sender: TObject);
var GoodsIDstr :string;
begin
  GoodsIDstr :=adsDetail.fieldbyname('GoodsID').asstring;
  if Trim(GoodsIDstr)='' then  exit;
  LookupPackUint.ListSource := PackUintFltDS;
  PackUintFltAQ.Close;
  PackUintFltAQ.CommandText :=' select ID, Name, ExchangeRate, GoalUnitID,'
      +' IsGoalUnit from MSUnit where RecordState<>'+Quotedstr('删除')
      +' and  GoalUnitID in (select UnitID from DaGoods where ID='
      +GoodsIDstr +' ) order by GoalUnitID,ExchangeRate' ;
  PackUintFltAQ.Open;
end;

procedure TPCEdPurchaseForm.NoCreditSaleClick(Sender: TObject);
begin
  if NoCreditSale.Checked then
  begin
    adsMaster.Edit;
    if pos(Trim(NoCreditSale.Caption),Trim(adsMaster.FieldByName('Memo').AsString))<=0 then
      adsMaster.FieldByName('Memo').AsString :=
          Copy( Trim(NoCreditSale.Caption)+Trim(adsMaster.FieldByName('Memo').AsString),1,60);
    adsMaster.FieldByName('BillMode').AsString :='现款采购';
    RemarkDBEdit.ReadOnly :=True;
  end else
  begin
    adsMaster.Edit;
    if pos(Trim(NoCreditSale.Caption),Trim(adsMaster.FieldByName('Memo').AsString))>0 then
      adsMaster.FieldByName('Memo').AsString :=
        StringReplace( Trim(adsMaster.FieldByName('Memo').AsString) ,
          Trim(NoCreditSale.Caption),'',[rfReplaceAll, rfIgnoreCase]);
    adsMaster.FieldByName('BillMode').AsString :='采购开单';
    RemarkDBEdit.ReadOnly :=False;
  end;
  DBEdit6.ReadOnly :=not NoCreditSale.Checked;
  if DBEdit6.ReadOnly then
  begin
    adsMaster.Edit;
    adsMaster.FieldByName('CashDiscount').Value :=null;
  end;
end;

procedure TPCEdPurchaseForm.LookupPackUintExit(Sender: TObject);
begin
  LookupPackUint.ListSource := dsPackUnit;
end;

procedure TPCEdPurchaseForm.FormActivate(Sender: TObject);
begin
  inherited;
  ClientQLDBLookup.SetFocus;
end;

procedure TPCEdPurchaseForm.InternalSave;
var adoTemp: TADOQuery;
    code,AccountIDStr,IDStr,MasterIDStr:string;
    MasterID:Integer;
begin
  inherited;
  if (adsMaster.fieldbyname('ID').AsInteger=0) or
         (adsMaster.fieldbyname('ID').IsNull)  then exit;
  adoTemp := TADOQuery.Create(nil);
  adoTemp.Connection := CommonData.acnConnection;
  with adoTemp do
  begin
    //--------------判断将要插入的记录是否已经正在于PCGoodsInMaster,不存在就插入
    Close;
    sql.Text :=' select  b.ID from PCPurchaseDetail a '
        +' left outer join PCPurchaseMaster b on a.MasterID=b.ID '
        +' where isnull(a.goodsId,0)<>0   '
        +' and isnull(a.GoalQuantity,0)<>0 '
        +' and b.id=' + adsMaster.fieldbyname('ID').AsString
        +' and b.RecordState<>' + QuotedStr('删除')
        +' and b.ID not in '
        +' (select top 1 OriginID from PCGoodsInMaster where '
        +' OriginTable='+ QuotedStr('PCPurchaseMaster')
        +' and OriginID='+adsMaster.fieldbyname('ID').AsString+'  )';
    open;
    if  not adoTemp.IsEmpty then //判断完毕,开始插入
    begin
        code :=GetMaxCode('Code','PCGoodsInMaster',number);
        {if adsMaster.FieldByName('ModeC').AsInteger=-1 then
           BillMode :='[红字]'+COPY(TRIM(adsMaster.FieldByName('BillMode').AsString),1,10)
            else BillMode :=TRIM(adsMaster.FieldByName('BillMode').AsString);}
        close;
        sql.Text := 'insert into PCGoodsInMaster ( CreateUserID,'
            +' Date, Code, ClientID, EmployeeID, BillMode,  '
            +' ModeDC,ModeC, Brief, ClearDate, SundryFee, Apportion,  '
            +' Deliver, Memo, BillAffix ,OriginID, OriginTable ) '
            +' select CreateUserID,Date, '+ QuotedStr(code)+ ' ,'
            +' ClientID, EmployeeID, BIllMode ,'
            +' ModeDC,ModeC, '+QuotedStr('采购开单自动提交') +' ,'
            +' ClearDate, SundryFee, Apportion, '
            +' Deliver, Memo, BillAffix, ID, '+QuotedStr('PCPurchaseMaster')
            +' from PCPurchaseMaster '
            +' where id=' +adsMaster.fieldbyname('ID').AsString
            +' and RecordState<>'+QuotedStr('删除')
            +' and ID not in '
            +' (select top 1 OriginID from PCGoodsInMaster where '
            +' OriginTable='+ QuotedStr('PCPurchaseMaster')
            +' and OriginID='+adsMaster.fieldbyname('ID').AsString+'  )';
        ExecSQL; //插入出库主表记录完毕
        close;
        sql.Text := 'select top 1 ID from PCGoodsInMaster  where '
            +' OriginTable='+ QuotedStr('PCPurchaseMaster')
            +' and OriginID='+adsMaster.fieldbyname('ID').AsString;
        open;
        if adoTemp.IsEmpty then MasterID :=1
            else MasterID :=adoTemp.FieldByName('ID').AsInteger;
        sql.Text := 'insert into PCGoodsInDetail ( '
              +' MasterID, GoodsID, GoodsSpec,    '
              +' Quantity, QuantityPcs, PackUnitID, PriceBase,PriceGoal, '
              +' Amount, Discount, TaxAmount, SundryFee,     '
              +' GoalUnitID, GoalQuantity, Memo )   '
              +' select '+ Inttostr(MasterID) + ' , a.GoodsID, a.GoodsSpec, '
              +' a.Quantity, a.QuantityPcs, a.PackUnitID, a.PriceBase,a.PriceGoal,'
              +' a.Amount, a.Discount, a.TaxAmount, a.SundryFee, '
              +' a.GoalUnitID, a.GoalQuantity, a.Memo  '
              +' from  PCPurchaseDetail a '
              +' left outer join PCPurchaseMaster b on a.MasterID=b.ID'
              +' where isnull(a.GoodsID,0)<>0 '
              +' and isnull(a.GoalQuantity,0)<>0 '
              +' and a.MasterID='+ adsMaster.fieldbyname('ID').AsString
              +' and b.RecordState<>'+QuotedStr('删除');
        ExecSQL;//插入出库子表记录
    end;
  end;
//判断是否要插入采购结算表
  if ( adsMaster.FieldByName('BillMode').AsString ='现款采购' )  then
  begin
    adoTemp.Close;
    adoTemp.SQL.Text :='select OriginID from FNClearPCMaster'
        +' where OriginTable='+Quotedstr('PCPurchaseMaster')
        +' and RecordState<>'+Quotedstr('删除')+' and OriginID='
        + adsMaster.fieldbyname('ID').AsString ;
    adoTemp.Open;
    if adoTemp.IsEmpty then
    begin
        adoTemp.Close;
        adoTemp.SQL.Text :=' select * from FNAccounts '
            +' where AccountType like '+Quotedstr('%现金%')
            +' and RecordState<>'+Quotedstr('删除') ;
        adoTemp.open;

        AccountIDStr :=adoTemp.fieldbyname('ID').AsString;
        if Trim(AccountIDStr)='' then AccountIDStr :='1';

        IDStr :=adsMaster.fieldbyname('ID').AsString;
        if Trim(IDStr)='' then IDStr :='0';
        adoTemp.Close;
        adoTemp.SQL.Text :=' Insert into FNClearPCMaster ('
            +' CreateUserID,Date,Code,BillMode,ModeDC,ModeC,'
            +' Brief, ClientID, EmployeeID,AccountsID, AmountD,   '
            +' AmountC,AmountRed,Memo, OriginID, OriginTable)     '
            +' select  CreateUserID, Date,                        '
            +Quotedstr(GetMaxCode('Code','FNClearPCMaster',number))+' , '
            +Quotedstr('现购结算')+' BillMode, ModeDC, ModeC, '
            +Quotedstr('现款采购')+' as Brief,         '
            +' ClientID, EmployeeID, '+ AccountIDStr+' as  AccountID , '
            +' 0.00,(isnull(b.Amount,0)+isnull(a.SundryFee,0) ) as AmountD, '
            +' 0.00,  Memo, a.ID, '
            +Quotedstr('PCPurchaseMaster')
            +' from PCPurchaseMaster a                                 '
            +' left outer join                                     '
            +' ( select MasterID,Sum(Isnull(Amount,0)) as Amount   '
            +' from PCPurchaseDetail                                   '
            +' Group by MasterID ) b on B.MasterID=a.ID            '
            +' where a.ID='+IDStr  ;
        adoTemp.ExecSQL;

        AccountIDStr := adsMaster.fieldbyname('ID').AsString;
        if Trim(AccountIDStr)='' then AccountIDStr :='0';

        adoTemp.Close;
        adoTemp.SQL.Text :='select ID from FNClearPCMaster '
            +' where OriginID='+AccountIDStr+' and OriginTable='
            +Quotedstr('PCPurchaseMaster') ;
        adoTemp.Open; //选定新插入记录(FNClearPCMaster表)的ID号
        MasterIDStr :=adoTemp.fieldbyname('ID').AsString;
        if Trim(MasterIDStr)='' then MasterIDStr :='0';

        adoTemp.Close;
        adoTemp.SQL.Text :=' Insert into FNClearPCDetail ('
            +' MasterID,OriginID,Amount)'
            +' select  '+MasterIDStr +'as MasterID,'
            +' a.ID as OriginID, '
            +' (isnull(b.Amount,0)+isnull(a.SundryFee,0) ) as Amount '
            +' from PCPurchaseMaster a                                 '
            +' left outer join                                     '
            +' ( select MasterID,Sum(Isnull(Amount,0)) as Amount   '
            +' from PCPurchaseDetail                                   '
            +' Group by MasterID ) b on B.MasterID=a.ID            '
            +' where a.ID='+IDStr  ;
      adoTemp.ExecSQL;
    end;
  end;
end;

procedure TPCEdPurchaseForm.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('PriceBase').AsFloat := adsDetail.FieldByName('PriceBase').AsFloat*ExChRate;

  if adsDetail.FieldByName('GoalQuantity').AsFloat<>0 then
  begin
    adsDetail.FieldByName('Memo').AsString :=
        Trim(Floattostr(adsDetail.FieldByName('Quantity').AsFloat))
          +'['+Trim(adsDetail.FieldByName('PackUnit').AsString)+']';
   end;
end;

end.