智税集成2.0生成凭证

:::info
💡 整体业务流程
从A9服务器中取数，生成列表数据，写入到对方oracle数据库中。
:::

项目关键点

1.连接数据库

1. 左连接连接本地SQLserver数据库、右连接要链接A9开票服务器的数据库
2. 然后设想用SQLserver 自带的外部连接来连接oracle数据库。

<add key="RConnectionString" value="Provider=PostgreSQL OLE DB Provider;Password=AiSinO_618;User ID=fwkp_320901999999116_0;Data Source=192.168.2.153;Location=fwkp_320901999999116_0" />

用外部连接连接oracle数据库

2.用语句验证取数

1. 获取A9开票服务器数据

SELECT 
':sClientNo' as imTaxMachineNo, 
d.fpmxxh AS idIndex, 
m.fpdm || '-' || m.fphm AS imID, 
m.fpdm || '-' || m.fphm AS imsaleIDlist, 
's' AS imType, 
m.gfmc AS imCrpName, 
'' AS imCrpTaxNo, 
'' AS imCrpAddrTel, 
'' AS imCrpBankNo,
to_char(m.kprq,'YYYY-MM-DD') as imdate,
m.bz AS imremark, 
'' AS imOper, 
'' AS imChecker, 
'' AS imPayee, 
''AS imTaxrate, 
d.spmc as idgoodsname,
d.ggxh AS idModal, 
d.jldw AS idUnit, 
d.sl AS idCount, 
d.dj AS idPrice, 
cast(d.je as VARCHAR) AS idMoney, 
cast(d.se as VARCHAR) AS idTaxMoney, 
0 AS imIncludeTaxFlg, 
0 AS idMoneyIncludeTaxFlg, 
0 AS idPriceIncludeTaxFlg, 
d.flbm  AS idGoodsNo, 
cast (d.slv as numeric(20,2))*cast(100 as numeric(20,2))  AS idTaxRate,
m.gfmc as  idDefine7,
'' as  idDefine8, 
m.fphm  AS imSaleNo, 
m.fpdm || '-' || m.fphm   AS imSaleID, 
m.fpdm || '-' || m.fphm  AS idID, 
m.fpdm || '-' || m.fphm  AS idimID ,
'数据库' as imDefine10,
m.fpdm as imDefine9,
m.fphm  as imDefine8,
m.kpjh  as imDefine7,
m.zfbz  as imStatus,
m.fpzl  as imCurrType,
m.xfmc as imSaleName,
--(select top 1 spmc from xxfpTaxCardD dd where m.fpdm=dd.fpdm and m.fphm=dd.fphm) as  imDefine10,--
'' as imDefine4 --
 FROM skfp  m , skfp_mx  d
where  m.fphm=d.fphm
and   m.kprq between to_date(':s起始日期','YYYY-MM-DD') and to_date(':s截止日期','YYYY-MM-DD')
and m.kpjh= ':s开票点'

2. 验证外部连接插入oracle 数据库

insert
into
openquery(ORCL,'select
"COMPANY"
		,"PREPAREDDATE"
		,"PK_BILL"
		,"ATTACHMENT_NUMBER"
		,"INDEXID"
		,"ACCOUNT_CODE"
		,"ABSTRACT_SM"
		,"DIRECTION"
		,"AMOUNT"
		,"FREETYPE1"
		,"FREEVALUE1"
		,"FREETYPE2"
		,"FREEVALUE2"
		,"FREETYPE3"
		,"FREEVALUE3"
		,"FREETYPE4"
		,"FREEVALUE4"
		,"FREETYPE5"
		,"FREEVALUE5"
		,"NCFLAG"
		,"NCINFO"
		,"TS"
		,"FPTYPE"
from
DF.TEMP_VOUCHER')
select
COMPANY,PREPAREDDATE,PK_BILL,ATTACHMENT_NUMBER,count,ACCOUNT_CODE,ABSTRACT_SM,DIRECTION,AMOUNT,FREETYPE1,FREEVALUE1,FREETYPE2,FREEVALUE2,FREETYPE3,FREEVALUE3,FREETYPE4,FREEVALUE4,FREETYPE5,FREEVALUE5,NCFLAG,NCINFO,TS,FPTYPE
from
TEMP_VOUCHER where bz<>'成功'

3.注意点

1. 防止插入重复数据

select distinct * into #Tmp from TEMP_VOUCHER
drop table TEMP_VOUCHER
select * into TEMP_VOUCHER from #Tmp
drop table #Tmp

alter table TEMP_VOUCHER add idAutoID int identity (1,1)
DBCC CHECKIDENT (TEMP_VOUCHER,reseed,1)
update TEMP_VOUCHER set count=t.RowNumber from (SELECT
  pk_bill+cast(idautoid as varchar(1000)) pkbill ,
  ROW_NUMBER()  OVER  (ORDER BY pk_bill+iddefine2+iddefine3+iddefine1+cast(indexid as varchar(1000)))  as  RowNumber FROM TEMP_VOUCHER where bz=''
   )t where t.pkbill=TEMP_VOUCHER.pk_bill+cast(TEMP_VOUCHER.idautoid as varchar(1000)) and TEMP_VOUCHER.bz=''