📄 fnbrcredence.pas
字号:
+' ( select GoalUnitID from FNCredDetail where OriginTable='
+Quotedstr('FNClearPCMaster_2')+' )'
+' and a.ID in '
+' ( select GoalUnitID from FNCredDetail where OriginTable='
+Quotedstr('FNClearPCMaster')+' )'
+' group by a.ID,e.ID, a.Code ';
VouchTranTemp.ExecSQL; //插入子表借方金额===
// Memo1.Text := VouchTranTemp.SQL.Text;
end;
procedure TFNCredenceBrowseForm.DoingCase4;
begin
if VourchTranIniADS.FieldByName('Code').AsString<>'是' then Exit;
// 转换收款申请
VouchTranTemp.Close;
VouchTranTemp.SQL.Text :=' select ID from FNCashInMaster '
+' 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('FNCashInMaster')
+' ) 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('FNCashInMaster')+' as OriginTable , ( '
+Quotedstr('收款申请:')+' +a.code) as Memo '
+' from FNCashInMaster 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('FNCashInMaster')+' )'
+' 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('FNCashInMaster_1')+' as OriginTable , ( '
+Quotedstr('收款申请:')+' +a.code) as GoodsSpec, '
+Quotedstr('折扣冲帐')+' as Memo '
+' from FNCashInMaster 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('FNCashInMaster_1')+' )'
+' and a.ID in '
+' ( select GoalUnitID from FNCredDetail where OriginTable='
+Quotedstr('FNCashInMaster')+' )'
+' 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('FNCashInMaster_2')+' as OriginTable , ( '
+Quotedstr('收款申请:')+' +a.code) as Memo '
+' from FNCashInMaster a '
+' left outer join MSEmployee c on c.ID=a.ClientID '
+' left outer join MSDepartment I on I.ID=C.DepartmentID '
+' left outer join DASubject d on d.ID=I.GeneralACID '
+' Left outer join DASubject e on '
+' e.RelativeTable='+Quotedstr('MSEmployee')+ ' 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('FNCashInMaster_2')+' )'
+' and a.ID in '
+' ( select GoalUnitID from FNCredDetail where OriginTable='
+Quotedstr('FNCashInMaster')+' )'
+' group by a.ID,e.ID, a.Code ';
VouchTranTemp.ExecSQL; //插入子表贷方金额===
end;
procedure TFNCredenceBrowseForm.DoingCase5;
begin
if VourchTranIniADS.FieldByName('Code').AsString<>'是' then Exit;
// 转换付款申请
VouchTranTemp.Close;
VouchTranTemp.SQL.Text :=' select ID from FNCashOutMaster '
+' 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('FNCashOutMaster')
+' ) 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('FNCashOutMaster')+' as OriginTable , ( '
+Quotedstr('付款申请:')+' +a.code) as Memo '
+' from FNCashOutMaster 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('FNCashOutMaster')+' )'
+' 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('FNCashOutMaster_1')+' as OriginTable , ( '
+Quotedstr('付款申请:')+' +a.code) as GoodsSpec, '
+Quotedstr('折扣冲帐')+' as Memo '
+' from FNCashOutMaster 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('FNCashOutMaster_1')+' )'
+' and a.ID in '
+' ( select GoalUnitID from FNCredDetail where OriginTable='
+Quotedstr('FNCashOutMaster')+' )'
+' 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('FNCashOutMaster_2')+' as OriginTable , ( '
+Quotedstr('付款申请:')+' +a.code) as Memo '
+' from FNCashOutMaster a '
+' left outer join MSEmployee c on c.ID=a.ClientID '
+' left outer join MSDepartment I on I.ID=C.DepartmentID '
+' left outer join DASubject d on d.ID=I.GeneralACID '
+' Left outer join DASubject e on '
+' e.RelativeTable='+Quotedstr('MSEmployee')+ ' 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 '
+' ( select GoalUnitID from FNCredDetail where OriginTable='
+Quotedstr('FNCashOutMaster_2')+' )'
+' and a.ID in '
+' ( select GoalUnitID from FNCredDetail where OriginTable='
+Quotedstr('FNCashOutMaster')+' )'
+' group by a.ID,e.ID, a.Code ';
VouchTranTemp.ExecSQL; //插入子表贷方金额===
end;
procedure TFNCredenceBrowseForm.DoingCase6;
begin
if VourchTranIniADS.FieldByName('Code').AsString<>'是' then Exit;
// 转换费用报销
VouchTranTemp.Close;
VouchTranTemp.SQL.Text :=' select ID from FNExpenseMaster '
+' 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('FNExpenseMaster')
+' ) 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.Amount,0)*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1)) '
+' as PriceBase, b.ID as GoalUnitID, '
+Quotedstr('FNExpenseMaster')+' as OriginTable , ( '
+Quotedstr('费用报销:')+' +b.code) as Memo '
+' from FNExpenseDetail a '
+' left outer join FNExpenseMaster b on b.ID=a.MasterID '
+' left outer join DAExpenseClass c on c.ID=a.ExpenseID '
+' left outer join DASubject d on d.ID=c.GeneralACID '
+' Left outer join DASubject e on '
+' e.RelativeTable='+Quotedstr('DAExpenseClass')+ ' and '
+' e.RelativeID=a.ExpenseID and substring(d.subCode,1,4)='
+' substring(e.subCode,1,4)'
+' where b.RecordState<>'+Quotedstr('删除')
+' and b.RecordState<>'+ Quotedstr('作废')
+' and isnull(a.Amount,0) <>0 and b.date<='
+Quotedstr(DateToStr(FEndDate))+' and b.ID not in '
+' ( select GoalUnitID from FNCredDetail where OriginTable='
+Quotedstr('FNExpenseMaster')+' )'
+' group by b.ID,e.ID, b.Code ';
VouchTranTemp.ExecSQL; //插入子表借方金额===
VouchTranTemp.Close; //插入子表贷方金额===(折扣冲帐)
VouchTranTemp.SQL.Text :=' Insert Into FNCredDetail ( '
+' MasterID,GoodsID, PriceCost,GoalUnitID, OriginTable,GoodsSpec,Memo )'
+' select '+NewVoucherID+' as MasterID , '
+' e.ID as GoodsID, '
+' sum(isnull(a.AmountRed,0)*Isnull(ModeDC,1)*Isnull(ModeC,1)) '
+' as PriceCost, a.ID as GoalUnitID, '
+Quotedstr('FNExpenseMaster_1')+' as OriginTable , ( '
+Quotedstr('费用报销:')+' +a.code) as GoodsSpec, '
+Quotedstr('折扣冲帐')+' as Memo '
+' from FNExpenseMaster a '
+' left outer join MSEmployee s on s.ID=a.ClientID '
+' left outer join MSDepartment b on b.ID=s.DepartMentID '
+' left outer join DASubject d on d.ID=b.GeneralACID '
+' Left outer join DASubject e on '
+' e.RelativeTable='+Quotedstr('MSEmployee')+ ' 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.AmountRed,0)<>0 and a.date<='
+Quotedstr(DateToStr(FEndDate))+' and a.ID not in '
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -