存储过程
USE [erp]
GO
/****** Object: StoredProcedure [dbo].[FinancialCheckOrder] Script Date: 2019/7/30 14:49:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FinancialCheckOrder]
@stime nvarchar(30) --选择的开始日期
,@etime nvarchar(30) --选择的结束日期
,@checkno nvarchar(50) --核对批号
,@channelid nvarchar(50) --平台(-1为自营,其他对应各平台)
,@order_type nvarchar(50) --订单类型
AS
declare @sql varchar(max)
declare @sqlwhere nvarchar(3000)
BEGIN
set @sql=''
if(@channelid='-1')
begin
--根据订单号更新order_id(支付宝的负订单)
set @sql='update c set c.order_id=a.order_id from erp_order_payment a with(nolock) left join erp_order b with(nolock) on a.order_id=b.order_id
left join erp_order_financial_blend c with(nolock) on c.order_sn=a.order_payment_trade_no where c.check_no='''+@checkno+''' and b.channel_id<>13
and c.ofb_result=0 and charindex(''支付宝'',c.ofb_paycom)>0 and c.ofb_amount<0 and c.order_id=0 and b.channel_id = '''+@channelid+''' and b.order_add_time between '''+@stime+' 00:00:00'' and '''+@etime+' 23:59:59'' ;'
--通联负单特殊处理
set @sql+='update b set b.order_id=c.order_id,b.order_sn=c.order_sn from erp_order_financial_blend b with(nolock) left join erp_order a with(nolock) on b.order_sn=a.order_sn join erp_order c with(nolock) on c.refid=a.order_id
where b.check_no='''+@checkno+''' and a.channel_id<>13 and b.ofb_result=0 and b.ofb_amount<0 and charindex(''通联'',b.ofb_paycom)>0 and b.order_id=0 and a.channel_id in ('+@channelid+') and a.order_add_time between '''+@stime+' 00:00:00'' and '''+@etime+' 23:59:59'' ;'
end
--根据订单号更新order_id(其他的正订单)
set @sql+='update b set b.order_id=a.order_id from erp_order_financial_blend b with(nolock) left join erp_order a with(nolock) on b.order_sn=a.order_sn
where b.check_no='''+@checkno+''' and a.channel_id<>13 and b.ofb_result=0 and b.ofb_amount>0 and b.order_id=0 and a.channel_id in ('+@channelid+') and a.order_add_time between '''+@stime+' 00:00:00'' and '''+@etime+' 23:59:59'' ;'
if(@channelid='-1')
begin
--根据订单号更新order_id(自营的其他的负订单)
set @sql+='update b set b.order_id=a.refid from erp_order_financial_blend b with(nolock) left join erp_order a with(nolock) on b.order_sn=a.order_sn
where b.check_no='''+@checkno+''' and a.channel_id<>13 and b.ofb_result=0 and b.ofb_amount<0 and b.order_id=0 and a.channel_id=''-1'' and a.channel_id = '''+@channelid+''' and a.order_add_time between '''+@stime+' 00:00:00'' and '''+@etime+' 23:59:59'';'
end
--非自营销售平台负单
set @sql+='update b set b.order_id=a.order_id from erp_order_financial_blend b with(nolock) left join erp_order a with(nolock) on b.order_sn=a.order_sn
where b.check_no='''+@checkno+''' and a.channel_id<>13 and b.ofb_result=0 and b.ofb_amount<0 and b.order_id=0 and b.channel_id<> ''-1'' and a.channel_id in ('+@channelid+') and a.order_add_time between '''+@stime+' 00:00:00'' and '''+@etime+' 23:59:59'' ;'
--开始核对
if(@stime<>'' and @etime<>'' )
set @sqlwhere=' and b.order_add_time between '''+@stime+' 00:00:00'' and '''+@etime+' 23:59:59'' '
--如果金额相等就是1，即核对正确，2金额错误
if(@order_type='1' )
begin
set @sql+='update a set a.ofb_result=case when round(a.ofb_amount,2,1)=round(a.order_true_amount,2,1) then 1 else 2 end
from erp_order_financial_blend a with(nolock) ,erp_order b with(nolock) where a.order_id=b.order_id and a.ofb_result=0'
end
else if(@order_type='2' )
begin
set @sql+='update a set a.ofb_result=case when round(a.ofb_amount,2,1)=-round(a.order_true_amount,2,1) then 1 else 2 end
from erp_order_financial_blend a with(nolock) ,erp_order b with(nolock) where a.order_id=b.order_id and a.ofb_result=0'
end
--核对总体结果
if(@channelid='-1')
begin
set @sql+=';INSERT INTO erp_order_financial_check (check_no,channel_name,not_exists_erp,not_exists_leadin,check_count,correct_count,error_count,start_date,end_date,success_order,fail_order,not_exist,repeat_order)
select check_no,channel_name,SUBSTRING(stuff((select ''|''+order_sn from erp_order_financial_blend with(nolock) where order_id=0 and check_no='''+@checkno+''' for xml path ('''')),1,1,''''),0,4000),
SUBSTRING(stuff((select ''|''+order_sn from erp_order b with(nolock) join erp_channel c with(nolock) on b.channel_id=c.channel_id where c.channel_type<>1 and order_id not in (select order_id from erp_order_financial_blend with(nolock) ) '+@sqlwhere+' for xml path ('''')),1,1,''''),0,4000)
,COUNT(ofb_id),sum(case when ofb_result=1 and order_type=0 then 1 else 0 end),sum(case when ofb_result in (0,2) or order_type=1 then 1 else 0 end),'''+@stime+' 00:00:00'','''+@etime+' 23:59:59''
,stuff((select ''，''+ order_newsn from erp_order_financial_blend with(nolock) where ofb_result=1 and order_type=0 and check_no='''+@checkno+''' FOR XML PATH('''')),1,1,'''')
, stuff((select ''，''+ order_newsn from erp_order_financial_blend with(nolock) where ofb_result =2 and check_no='''+@checkno+''' FOR XML PATH('''')),1,1,'''')
, stuff((select ''，''+ order_newsn from erp_order_financial_blend with(nolock) where ofb_result =0 and order_id=0 and check_no='''+@checkno+''' FOR XML PATH('''')),1,1,'''')
,stuff((select ''，''+ order_newsn from erp_order_financial_blend with(nolock) where order_type=1 and ofb_result=1 and check_no='''+@checkno+''' FOR XML PATH('''')),1,1,'''')
from erp_order_financial_blend with(nolock) where check_no='''+@checkno+''' group by check_no,channel_name'
end
else
begin
set @sql+=';INSERT INTO erp_order_financial_check (check_no,channel_name,not_exists_erp,not_exists_leadin,check_count,correct_count,error_count,start_date,end_date,success_order,fail_order,not_exist,repeat_order)
select check_no,channel_name,SUBSTRING(stuff((select ''|''+order_sn from erp_order_financial_blend with(nolock) where order_id=0 and check_no='''+@checkno+''' for xml path ('''')),1,1,''''),0,4000),
SUBSTRING(stuff((select ''|''+order_sn from erp_order b with(nolock) where order_id not in (select order_id from erp_order_financial_blend with(nolock) ) and b.channel_id in '+'('+@channelid+')'+@sqlwhere+' for xml path ('''')),1,1,''''),0,4000)
,COUNT(ofb_id),sum(case when ofb_result=1 and order_type=0 then 1 else 0 end),sum(case when ofb_result in (0,2) or order_type=1 then 1 else 0 end),'''+@stime+' 00:00:00'','''+@etime+' 23:59:59''
,stuff((select ''，''+ order_newsn from erp_order_financial_blend with(nolock) where ofb_result=1 and order_type=0 and check_no='''+@checkno+''' FOR XML PATH('''')),1,1,'''')
, stuff((select ''，''+ order_newsn from erp_order_financial_blend with(nolock) where ofb_result =2 and check_no='''+@checkno+''' FOR XML PATH('''')),1,1,'''')
, stuff((select ''，''+ order_newsn from erp_order_financial_blend with(nolock) where ofb_result =0 and order_id=0 and check_no='''+@checkno+''' FOR XML PATH('''')),1,1,'''')
,stuff((select ''，''+ order_newsn from erp_order_financial_blend with(nolock) where order_type=1 and ofb_result=1 and check_no='''+@checkno+''' FOR XML PATH('''')),1,1,'''')
from erp_order_financial_blend with(nolock) where check_no='''+@checkno+''' group by check_no,channel_name'
end
print(@sql)
exec(@sql)
END
GO
