fnbrcredence.pas

详细的ERP设计资料

    +' and a.date<='
    +Quotedstr(DateToStr(FEndDate))+' and a.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('PCPurchaseMaster')+' ) ) as a ' ;
  VouchTranTemp.Open;//汇总转换金额
  AmountA := VouchTranTemp.FieldByName('AmountA').AsFloat ;
  AmountB := VouchTranTemp.FieldByName('AmountB').AsFloat ;

  NewVoucherCode :=GetMaxCode('Code','FNCredMaster',number);
  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' Insert into FNCredMaster ( '
    +' CreateUserID,Code,Date,BillMode,ModeDC,ModeC,Brief,Memo)'
    +' Values ( '+Inttostr(Guarder.UserID)+' , '+ Quotedstr(NewVoucherCode)
    +' , '+Quotedstr(DateToStr(FEndDate))+' , '+ Quotedstr('自动转换')
    +' , 1, 1 ,'+ Quotedstr('自动转换')+' , '+ Quotedstr('采购开单自动转换凭证')
    +' ) ';
  VouchTranTemp.ExecSQL;  //插入凭证头
  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' select ID from FNCredMaster where Code='
    +Quotedstr(NewVoucherCode);
  VouchTranTemp.Open;
  NewVoucherID :=Inttostr(VouchTranTemp.fieldbyname('ID').AsInteger);
  VouchTranTemp.Close;  //插入子表贷方金额---
  VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
    +' MasterID,GoodsID, PriceCost,GoalUnitID, OriginTable,GoodsSpec )'
    +' select  MasterID ,GoodsID, sum(isnull(PriceBase,0)) as PriceBase ,'
    +' GoalUnitID,OriginTable,memo  from ( '

    +' select '+NewVoucherID+' as MasterID ,'
    +' e.ID as GoodsID, '
    +' (sum(isnull(a.Amount,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)) )                  '
    +' as PriceBase, b.ID  as GoalUnitID,               '
    +Quotedstr('PCPurchaseMaster')+' as OriginTable , ( '
    +Quotedstr('采购单号:')+' +b.code) as Memo '
    +' from PCPurchaseDetail  a                                     '
    +' left outer join PCPurchaseMaster b on b.ID=a.MasterID       '
    +' left outer join DaClient c on c.ID=b.ClientID           '
    +' left outer join DASubject d on d.ID=c.GeneralACID       '
    +' Left outer join DASubject e on                          '
    +'   e.RelativeTable='+Quotedstr('DAClient')+ ' and        '
    +'   e.RelativeID=b.ClientID and substring(d.subCode,1,4)='
    +'   substring(e.subCode,1,4)'
    +' where b.RecordState<>'+Quotedstr('删除')
    +' and b.RecordState<>'+ Quotedstr('作废')
    +' and b.date<='
    +Quotedstr(DateToStr(FEndDate))+' and b.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('PCPurchaseMaster')+' )'
    +' group by b.ID,e.ID, b.Code  '

    +' union all '
    +' select '+NewVoucherID+' as MasterID ,'
    +' e.ID as GoodsID, '
    +' (sum(isnull(a.SundryFee,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)))        '
    +' as PriceBase, a.ID  as GoalUnitID,               '
    +Quotedstr('PCPurchaseMaster')+' as OriginTable , ( '
    +Quotedstr('采购单号:')+' +a.code) as Memo '
    +' from PCPurchaseMaster  a                                     '
    +' left outer join DaClient c on c.ID=a.ClientID           '
    +' left outer join DASubject d on d.ID=c.GeneralACID       '
    +' Left outer join DASubject e on                          '
    +'   e.RelativeTable='+Quotedstr('DAClient')+ ' and        '
    +'   e.RelativeID=a.ClientID and substring(d.subCode,1,4)='
    +'   substring(e.subCode,1,4)'
    +' where a.RecordState<>'+Quotedstr('删除')
    +' and a.RecordState<>'+ Quotedstr('作废')
    +' and a.date<='
    +Quotedstr(DateToStr(FEndDate))+' and a.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('PCPurchaseMaster')+' )'
    +' group by a.ID,e.ID, a.Code  ) as a '
    +' Group by a.MasterID , a.GoodsID, a.GoalUnitID,a.OriginTable,a.memo ';
//  Memo1.Text := VouchTranTemp.SQL.Text;
  VouchTranTemp.ExecSQL; //插入子表贷方金额---

  if AmountA<>0 then
  begin
    VouchTranTemp.Close;  //插入子表借方金额====
    VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
      +' MasterID,GoodsID, PriceBase,GoodsSpec )'
      +' Values ( '+NewVoucherID+'   ,'
      + Inttostr(VourchTranIniADS.FieldByName('AccountFID').AsInteger)
      +' , ' + Floattostr(AmountA)+' , '
      +Quotedstr('汇总采购')+' ) ';
    VouchTranTemp.ExecSQL; //插入子表借方金额 ====
  end;
  if AmountB<>0 then
  begin
    VouchTranTemp.Close;  //插入子表借方金额****
    VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
      +' MasterID,GoodsID, PriceBase,GoodsSpec,Memo )'
      +' Values ( '+NewVoucherID + ' ,'
      + Inttostr(VourchTranIniADS.FieldByName('AccountGID').AsInteger)
      + ' , '   + Floattostr(AmountB)+' , '
      +Quotedstr('汇总附加费用')+' , '
      +Quotedstr('附加费用')+' ) ';
    VouchTranTemp.ExecSQL; //插入子表借方金额****
  end;
end;

procedure TFNCredenceBrowseForm.DoingCase2;
begin
  if VourchTranIniADS.FieldByName('Code').AsString<>'是' then Exit;
// 转换收款结算
  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' select ID from FNClearSLMaster '
    +' where RecordState<>'+Quotedstr('删除')
    +' and RecordState<>'+ Quotedstr('作废')
    +' and ( isnull(AmountD,0)+isnull(AmountRed,0) )<>0 and  '
    +' ID not in ( select GoalUnitID from FNCredDetail where '
    +' OriginTable=' +Quotedstr('FNClearSLMaster')
    +' ) and  Date<=' +Quotedstr(DateToStr(FEndDate));

  VouchTranTemp.Open;

  if VouchTranTemp.IsEmpty then exit;
  NewVoucherCode :=GetMaxCode('Code','FNCredMaster',number);
  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' Insert into FNCredMaster ( '
    +' CreateUserID,Code,Date,BillMode,ModeDC,ModeC,Brief,Memo)'
    +' Values ( '+Inttostr(Guarder.UserID)+' , '+ Quotedstr(NewVoucherCode)
    +' , '+Quotedstr(DateToStr(FEndDate))+' , '+ Quotedstr('自动转换')
    +' , 1, 1 ,'+ Quotedstr('自动转换')+' , '+ Quotedstr('收款结算自动转换凭证')
    +' ) ';
  VouchTranTemp.ExecSQL;  //插入凭证头

  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' select ID from FNCredMaster where Code='
    +Quotedstr(NewVoucherCode);
  VouchTranTemp.Open;
  NewVoucherID :=Inttostr(VouchTranTemp.fieldbyname('ID').AsInteger);

  VouchTranTemp.Close;  //插入子表借方金额---（收款帐户）
  VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
    +' MasterID,GoodsID, PriceBase,GoalUnitID, OriginTable,GoodsSpec )'
    +' select '+NewVoucherID+' as MasterID , '
    +' e.ID as GoodsID, '
    +' sum(isnull(a.AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1))            '
    +' as PriceBase, a.ID  as GoalUnitID,               '
    +Quotedstr('FNClearSLMaster')+' as OriginTable , ( '
    +Quotedstr('收款结算:')+' +a.code) as Memo '
    +' from FNClearSLMaster a                                     '
    +' left outer join FNAccounts  b on b.ID=a.AccountsID       '
    +' left outer join DASubject d on d.ID=b.GeneralACID       '
    +' Left outer join DASubject e on                          '
    +'   e.RelativeTable='+Quotedstr('FNAccounts')+ ' and        '
    +'   e.RelativeID=a.AccountsID and substring(d.subCode,1,4)='
    +'   substring(e.subCode,1,4)'
    +' where a.RecordState<>'+Quotedstr('删除')
    +' and a.RecordState<>'+ Quotedstr('作废')
    +' and isnull(a.AmountD,0)<>0 and a.date<='
    +Quotedstr(DateToStr(FEndDate))+' and a.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('FNClearSLMaster')+' )'
    +' group by a.ID,e.ID, a.Code  ';
  VouchTranTemp.ExecSQL; //插入子表借方金额---

  VouchTranTemp.Close;  //插入子表借方金额===(折扣冲帐)
  VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
    +' MasterID,GoodsID, PriceBase,GoalUnitID, OriginTable,GoodsSpec,Memo )'
    +' select '+NewVoucherID+' as MasterID , '
    + Inttostr(VourchTranIniADS.FieldByName('AccountFID').AsInteger)
    + 'as GoodsID, '
    +' sum(isnull(a.AmountRed,0)*Isnull(ModeDC,1)*Isnull(ModeC,1))            '
    +' as PriceBase, a.ID  as GoalUnitID,               '
    +Quotedstr('FNClearSLMaster_1')+' as OriginTable , ( '
    +Quotedstr('收款结算:')+' +a.code) as GoodsSpec, '
    +Quotedstr('折扣冲帐')+' as Memo '
    +' from FNClearSLMaster a                                     '
    +' where a.RecordState<>'+Quotedstr('删除')
    +' and a.RecordState<>'+ Quotedstr('作废')
    +' and isnull(a.AmountRed,0)<>0 and  a.date<='
    +Quotedstr(DateToStr(FEndDate))+' and a.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('FNClearSLMaster_1')+' )'
    +' and a.ID in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('FNClearSLMaster')+' )'
    +' group by a.ID, a.Code  ';
  VouchTranTemp.ExecSQL; //插入子表借方金额===

  VouchTranTemp.Close;  //插入子表贷方金额===（应收帐款）
  VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
    +' MasterID,GoodsID, PriceCost,GoalUnitID, OriginTable,GoodsSpec )'
    +' select '+NewVoucherID+' as MasterID , '
    + ' e.ID as GoodsID, '
    +' ( sum(isnull(a.AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1))    +        '
    +' sum(isnull(a.AmountRed,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)) )           '
    +' as PriceCost, a.ID  as GoalUnitID,               '
    +Quotedstr('FNClearSLMaster_2')+' as OriginTable , ( '
    +Quotedstr('收款结算:')+' +a.code) as Memo '
    +' from FNClearSLMaster a                   '
    +' left outer join DaClient c on c.ID=a.ClientID           '
    +' left outer join DASubject d on d.ID=c.GeneralACID       '
    +' Left outer join DASubject e on                          '
    +'   e.RelativeTable='+Quotedstr('DAClient')+ ' and        '
    +'   e.RelativeID=a.ClientID and substring(d.subCode,1,4)='
    +'   substring(e.subCode,1,4)'
    +' where a.RecordState<>'+Quotedstr('删除')
    +' and a.RecordState<>'+ Quotedstr('作废')
    +' and (isnull(a.AmountD,0)+isnull(a.AmountRed,0))<>0 and a.date<='
    +Quotedstr(DateToStr(FEndDate))+' and a.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('FNClearSLMaster_2')+' )'
    +' and a.ID in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('FNClearSLMaster')+' )'
    +' group by a.ID,e.ID, a.Code  ';
  VouchTranTemp.ExecSQL; //插入子表贷方金额===
end;

procedure TFNCredenceBrowseForm.DoingCase3;
begin
  if VourchTranIniADS.FieldByName('Code').AsString<>'是' then Exit;
// 转换付款结算
  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' select ID from FNClearPCMaster '
    +' where RecordState<>'+Quotedstr('删除')
    +' and RecordState<>'+ Quotedstr('作废')
    +' and ( isnull(AmountC,0)+isnull(AmountRed,0) )<>0 and  '
    +' ID not in (select GoalUnitID from FNCredDetail where '
    +' OriginTable=' +Quotedstr('FNClearPCMaster')
    +' ) and  Date<=' +Quotedstr(DateToStr(FEndDate));

  VouchTranTemp.Open;

  if VouchTranTemp.IsEmpty then exit;
  NewVoucherCode :=GetMaxCode('Code','FNCredMaster',number);
  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' Insert into FNCredMaster ( '
    +' CreateUserID,Code,Date,BillMode,ModeDC,ModeC,Brief,Memo)'
    +' Values ( '+Inttostr(Guarder.UserID)+' , '+ Quotedstr(NewVoucherCode)
    +' , '+Quotedstr(DateToStr(FEndDate))+' , '+ Quotedstr('自动转换')
    +' , 1, 1 ,'+ Quotedstr('自动转换')+' , '+ Quotedstr('付款结算自动转换凭证')
    +' ) ';
  VouchTranTemp.ExecSQL;  //插入凭证头

  VouchTranTemp.Close;
  VouchTranTemp.SQL.Text :=' select ID from FNCredMaster where Code='
    +Quotedstr(NewVoucherCode);
  VouchTranTemp.Open;
  NewVoucherID :=Inttostr(VouchTranTemp.fieldbyname('ID').AsInteger);

  VouchTranTemp.Close;  //插入子表贷方金额---（付款帐户）
  VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
    +' MasterID,GoodsID, PriceCost,GoalUnitID, OriginTable,GoodsSpec )'
    +' select '+NewVoucherID+' as MasterID , '
    +' e.ID as GoodsID, '
    +' sum(isnull(a.AmountC,0)*Isnull(ModeDC,1)*Isnull(ModeC,1))            '
    +' as PriceCost, a.ID  as GoalUnitID,               '
    +Quotedstr('FNClearPCMaster')+' as OriginTable , ( '
    +Quotedstr('付款结算:')+' +a.code) as Memo '
    +' from FNClearPCMaster a                                     '
    +' left outer join FNAccounts  b on b.ID=a.AccountsID       '
    +' left outer join DASubject d on d.ID=b.GeneralACID       '
    +' Left outer join DASubject e on                          '
    +'   e.RelativeTable='+Quotedstr('FNAccounts')+ ' and        '
    +'   e.RelativeID=a.AccountsID and substring(d.subCode,1,4)='
    +'   substring(e.subCode,1,4)'
    +' where a.RecordState<>'+Quotedstr('删除')
    +' and a.RecordState<>'+ Quotedstr('作废')
    +' and isnull(a.AmountC,0)<>0 and   a.date<='
    +Quotedstr(DateToStr(FEndDate))+' and a.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('FNClearPCMaster')+' )'
    +' group by a.ID,e.ID, a.Code  ';
  VouchTranTemp.ExecSQL; //插入子表贷方金额---

  VouchTranTemp.Close;  //插入子表贷方金额===(折扣冲帐)
  VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
    +' MasterID,GoodsID, PriceCost,GoalUnitID, OriginTable,GoodsSpec,Memo )'
    +' select '+NewVoucherID+' as MasterID , '
    + Inttostr(VourchTranIniADS.FieldByName('AccountFID').AsInteger)
    + 'as GoodsID, '
    +' sum(isnull(a.AmountRed,0)*Isnull(ModeDC,1)*Isnull(ModeC,1))            '
    +' as PriceCost, a.ID  as GoalUnitID,               '
    +Quotedstr('FNClearPCMaster_1')+' as OriginTable , ( '
    +Quotedstr('付款结算:')+' +a.code) as GoodsSpec, '
    +Quotedstr('折扣冲帐')+' as Memo '
    +' from FNClearPCMaster a                                     '
    +' where a.RecordState<>'+Quotedstr('删除')
    +' and a.RecordState<>'+ Quotedstr('作废')
    +' and isnull(a.AmountRed,0) <>0 and  a.date<='
    +Quotedstr(DateToStr(FEndDate))+' and a.ID not in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('FNClearPCMaster_1')+' )'
    +' and a.ID in '
    +' ( select GoalUnitID from FNCredDetail where OriginTable='
    +Quotedstr('FNClearPCMaster')+' )'
    +' group by a.ID, a.Code  ';
  VouchTranTemp.ExecSQL; //插入子表贷方金额===

  VouchTranTemp.Close;  //插入子表借方金额===（应付帐款）
  VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
    +' MasterID,GoodsID, PriceBase,GoalUnitID, OriginTable,GoodsSpec )'
    +' select '+NewVoucherID+' as MasterID , '
    + ' e.ID as GoodsID, '
    +' ( sum(isnull(a.AmountC,0)*Isnull(ModeDC,1)*Isnull(ModeC,1))    +        '
    +' sum(isnull(a.AmountRed,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)) )           '
    +' as PriceBase, a.ID  as GoalUnitID,               '
    +Quotedstr('FNClearPCMaster_2')+' as OriginTable , ( '
    +Quotedstr('付款结算:')+' +a.code) as Memo '
    +' from FNClearPCMaster a                   '
    +' left outer join DaClient c on c.ID=a.ClientID           '
    +' left outer join DASubject d on d.ID=c.GeneralACID       '
    +' Left outer join DASubject e on                          '
    +'   e.RelativeTable='+Quotedstr('DAClient')+ ' and        '
    +'   e.RelativeID=a.ClientID and substring(d.subCode,1,4)='
    +'   substring(e.subCode,1,4)'
    +' where a.RecordState<>'+Quotedstr('删除')
    +' and a.RecordState<>'+ Quotedstr('作废')
    +' and (isnull(a.AmountC,0)+isnull(a.AmountRed,0)<>0 ) and a.date<='
    +Quotedstr(DateToStr(FEndDate))+' and a.ID not in '