refresh MatchRelationship sql

USE [H_SO_DB_test]
GO
/****** Object:  StoredProcedure [dbo].[sp_check_compare_system]    Script Date: 2021/12/28 10:42:54 ******/
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]
 -- Add the parameters for the stored procedure here
-- <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
-- <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
 @DistributorCode varchar(max)
AS
BEGIN
   Declare @strSql varchar(max);
    --1:创建临时商品匹配源数据表
    CREATE TABLE #Goods(
    DistributorCode varchar(200)  collate Chinese_PRC_CI_AS NOT NULL,
    GoodsCode varchar(200)  collate Chinese_PRC_CI_AS NOT NULL ,
    GoodsName varchar(500) collate Chinese_PRC_CI_AS NULL,
    GoodsSpecification varchar(500) collate Chinese_PRC_CI_AS NULL,
    GoodsBarCode varchar(500) collate Chinese_PRC_CI_AS NULL,
	royalGoodsCode varchar(500) collate Chinese_PRC_CI_AS NULL,
	isONL int 
	
	)


    CREATE TABLE #Customer(
    DistributorCode varchar(200)  collate Chinese_PRC_CI_AS NOT NULL ,
    CustomerCode varchar(200)  collate Chinese_PRC_CI_AS NOT NULL ,
    CustomerName varchar(500) collate Chinese_PRC_CI_AS NULL,
    CustomerAddress varchar(500) collate Chinese_PRC_CI_AS NULL,
    CustomerTel varchar(200) collate Chinese_PRC_CI_AS NULL,
    Contacts varchar(200) collate Chinese_PRC_CI_AS NULL)  
	
 CREATE TABLE #GoodsUnit(
    DistributorCode varchar(200)  collate Chinese_PRC_CI_AS NOT NULL ,
    GoodsCode varchar(200)  collate Chinese_PRC_CI_AS NOT NULL ,
    GoodsName varchar(500) collate Chinese_PRC_CI_AS NULL,
    GoodsSpecification varchar(500) collate Chinese_PRC_CI_AS NULL,
    GoodsUnit varchar(200)  collate Chinese_PRC_CI_AS NOT NULL ,
    StockPrice decimal(28, 2) NULL,
    SalesPrice decimal(28, 2) NULL,
    InventoryPrice decimal(28, 2) NULL)
    CREATE TABLE #stockinfo(
 [distributorcode] [VARCHAR](10) NOT NULL,
 [stock_code] [VARCHAR](30) NULL,
 [stock_name] [VARCHAR](60) NULL)

 --2：准备商品匹配的源数据
 begin
 --print('2：准备商品匹配的源数据')
 set @strSql=' insert into #Goods
 select  *  from (
  select ISNULL(a.DISTRIBUTOR,b.DISTRIBUTOR) distributor,
      ISNULL(a.COM_CODE,b.COM_CODE) com_code,
      ISNULL(a.COM_NAME,b.COM_NAME) com_name,
      b.Standard,
      a.STR_1,b.royalGoodsCode,isONL
  from DISTRIBUTOR_GOODS  a 
  full join
    (select b.DISTRIBUTOR,
      b.COM_CODE,
      MAX(b.COM_NAME) COM_NAME,royalGoodsCode,isONL,Standard
     from (select a.DISTRIBUTOR,
				a.COM_CODE,
				MAX(a.COM_NAME) com_name, royalGoodsCode,
			case when DataType is not null then  1 else 0 end isONL ,a.Standard  as Standard
			from REPORT_SALE_FULL a   
			where a.RQ >= (GETDATE() - 60)