Refresh the matching relational stored procedure

USE [H_SO_DB_test]
GO
/****** Object:  StoredProcedure [dbo].[sp_check_compare_system_onl]    Script Date: 2022/2/18 22:46:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  jzx
-- Create date: 2013-9-3
-- Description: 监控平台中，后台刷新匹配数据的过程
-- =============================================
ALTER procedure [dbo].[sp_check_compare_system_onl]
(
 @usercode varchar(50)
)
as 
begin 
----填坑过程，专业填涛哥的坑
----汇总最近两2个月的进销存数据到匹配关系
declare @beginDate date 
select @beginDate=DATEADD(month,-3,getdate())

if(@usercode is not null )
begin 
----单个经销商
---传入了usercode
 --1:创建经销商表，用于存储usercode关联到的直营经销商
  DROP TABLE IF EXISTS #DistributorCodeTB;
  CREATE TABLE #DistributorCodeTB(
   DistributorCode varchar(200)   NOT NULL
  )
  --插入数据（H_MA_DB_test这个表要区别正式库与测试库)
  insert into #DistributorCodeTB
    select   * from 
    ( select  distinct DISTRIBUTOR from REPORT_SALE_FULL
     where DataType='Offtake') tb
	 where tb.DISTRIBUTOR in (
	         SELECT b.DistributorCode 
	         FROM DICTIONARY_DISTRIBUTOR  a 
			 LEFT JOIN H_MA_DB_test.dbo.UserToDistributors b 
			 ON a.CODE=b.DistributorCode 
			 and b.Userid=@usercode
			 where isnull(a.str_15,'Y')='Y' 
	)
	--select * from  #DistributorCodeTB;
delete from GoodsCompare where DistributorCode in (select DistributorCode from #DistributorCodeTB  ) and StandardGoodsCode is null and isONL=1
delete from GoodsUnitCoefficient where DistributorCode in(select DistributorCode from #DistributorCodeTB  ) and StandardGoodsCode is null and isONL=1
delete from CustomerCompare  where DistributorCode in(select DistributorCode from #DistributorCodeTB  ) and StandardCustomerCode is null

delete a  from GoodsCompare a 
left join baseinfo_goods b on a.StandardGoodsCode=b.CODE 
where a.DistributorCode in(select DistributorCode from #DistributorCodeTB  ) and  a.StandardGoodsCode is not null 
and b.code is null and a.isONL=1


delete a  from CustomerCompare a 
left join Baseinfo_customer b on a.DistributorCode=b.DISTRIBUTOR and  a.StandardCustomerCode=b.CUSTOMER_CODE 
where a.DistributorCode in(select DistributorCode from #DistributorCodeTB  ) and  a.StandardCustomerCode is not null 
and b.DISTRIBUTOR is null 

		insert into GoodsCompare(DistributorCode,goodscode,goodsname,GoodsSpecification,GoodsBarCode,isONL,royalGoodsCode)
		SELECT DISTRIBUTOR,COM_CODE,MAX(COM_NAME),null,MAX(COM_CODE2),isONL,royalGoodsCode FROM (
			select distinct DISTRIBUTOR,COM_CODE,COM_NAME,null GoodsSpecification,COM_CODE AS COM_CODE2,1 as isONL,royalGoodsCode from 
			REPORT_SALE_FULL a 
			where a.DISTRIBUTOR in(select DistributorCode from #DistributorCodeTB  ) and  a.RQ>=@beginDate and not exists(
			select 1 from GoodsCompare b where a.DISTRIBUTOR=b.DistributorCode and a.COM_CODE=b.GoodsCode and  a. DataType='Offtake'
			) and  a. DataType='Offtake'
			
		) TAB1 GROUP BY DISTRIBUTOR ,COM_CODE,isONL,royalGoodsCode


		------------------
		insert into GoodsUnitCoefficient(DistributorCode,GoodsCode,GoodsName,GoodsUnit,isONL)
		select DISTRIBUTOR,COM_CODE,max(COM_NAME),Unit1,1 as isONL from (
		select distinct DISTRIBUTOR,COM_CODE,COM_NAME,Unit1 from 
		REPORT_SALE_FULL a 
		where a.DISTRIBUTOR in(select DistributorCode from #DistributorCodeTB  ) and  a.RQ>=@beginDate and   not exists(
		select 1 from GoodsUnitCoefficient b where a.DISTRIBUTOR=b.DistributorCode and a.COM_CODE=b.GoodsCode and  a. DataType='Offtake'
		) and  a. DataType='Offtake'
		) a 
		group by DISTRIBUTOR,COM_CODE,Unit1




		insert into CustomerCompare(DistributorCode,CustomerCode,CustomerName)
		select  DISTRIBUTOR,CUSTOMER_CODE,max(CUSTOMER_NAME)
		from 
		REPORT_SALE_FULL a 
		where a.DISTRIBUTOR in(select DistributorCode from #DistributorCodeTB  )
		and    a.RQ>=@beginDate and not exists(
		select 1 from CustomerCompare b where a.DISTRIBUTOR=b.DistributorCode and a.CUSTOMER_CODE=b.CustomerCode and  a. DataType='Offtake'
		)  and  a. DataType='Offtake'
		group by DISTRIBUTOR,CUSTOMER_CODE


		--exec A_从商品匹配删除失效的商品

end 
else --else分支刷新全部
begin 
--全部经销商
delete from GoodsCompare where  StandardGoodsCode is null  and isONL=1
delete from GoodsUnitCoefficient where StandardGoodsCode is null and isONL=1
delete from CustomerCompare  where  StandardCustomerCode is null 

delete a  from GoodsCompare a 
left join baseinfo_goods b on a.StandardGoodsCode=b.CODE 
where  a.StandardGoodsCode is not null 
and b.code is null  and isONL=1


delete a  from CustomerCompare a 
left join Baseinfo_customer b on a.DistributorCode=b.DISTRIBUTOR and  a.StandardCustomerCode=b.CUSTOMER_CODE 
where a.StandardCustomerCode is not null 
and b.DISTRIBUTOR is null 

			insert into GoodsCompare(DistributorCode,goodscode,goodsname,GoodsSpecification,GoodsBarCode,isONL,royalGoodsCode)
		SELECT DISTRIBUTOR,COM_CODE,MAX(COM_NAME),null,MAX(COM_CODE2),isONL,royalGoodsCode FROM (
			select distinct DISTRIBUTOR,COM_CODE,COM_NAME,null GoodsSpecification,COM_CODE AS COM_CODE2 ,1 as isONL,a.royalGoodsCode  from 
			REPORT_SALE_FULL a 
			left join GoodsCompare b on a.DISTRIBUTOR=b.DistributorCode and a.COM_CODE=b.GoodsCode
			where   b.DistributorCode is null  and a.RQ>=@beginDate and  a. DataType='Offtake'
			
		) TAB1 GROUP BY DISTRIBUTOR ,COM_CODE,isONL,royalGoodsCode



		------------------
		insert into GoodsUnitCoefficient(DistributorCode,GoodsCode,GoodsName,GoodsUnit,isONL)
		select DISTRIBUTOR,COM_CODE,max(COM_NAME),Unit1,1 AS isONL  from (
		select distinct DISTRIBUTOR,COM_CODE,COM_NAME,Unit1 from 
		REPORT_SALE_FULL a 
		left join  GoodsUnitCoefficient b on a.DISTRIBUTOR=b.DistributorCode and a.COM_CODE=b.GoodsCode
		where  b.DistributorCode is null   and a.RQ>=@beginDate  and  a. DataType='Offtake'
		
		) a 
		group by DISTRIBUTOR,COM_CODE,Unit1




		insert into CustomerCompare(DistributorCode,CustomerCode,CustomerName)
		select  DISTRIBUTOR,CUSTOMER_CODE,max(CUSTOMER_NAME)
		from 
		REPORT_SALE_FULL a 
		left join  CustomerCompare b on a.DISTRIBUTOR=b.DistributorCode and a.CUSTOMER_CODE=b.CustomerCode
		where  b.DistributorCode is null  and a.RQ>=@beginDate and  a. DataType='Offtake'
		group by DISTRIBUTOR,CUSTOMER_CODE 


		--exec A_从商品匹配删除失效的商品

end 
end 




--commit