old_jintiantongye_wms/db/数据库更新20170930.sql

<EFBFBD><EFBFBD>/****** Object:  StoredProcedure [dbo].[p_ubT<EFBFBD>]S<EFBFBD>YXb<EFBFBD>v<EFBFBD>mEmX[>e<EFBFBD>vl<EFBFBD><EFBFBD>^c<EFBFBD>N]    Script Date: 2017/9/30 fgmQ 13:48:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[p_ubT<EFBFBD>]S<EFBFBD>YXb<EFBFBD>v<EFBFBD>mEmX[>e<EFBFBD>vl<EFBFBD><EFBFBD>^c<EFBFBD>N]
(
@<EFBFBD>d\O(u7b
T varchar(20),
@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> VARCHAR(50),
@P<EFBFBD><EFBFBD>[X[>eR:S VARCHAR(50),
@l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe INT,
@b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe INT OUTPUT,
@l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S INT OUTPUT -- ubYagl<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>e<EFBFBD>ԏ<EFBFBD>V,{Nagl<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S
)
AS
/**
 * \R^(W
NM<EFBFBD><EFBFBD>[<EFBFBD>v<EFBFBD>mEm'<EFBFBD>MOXb<EFBFBD>vR_v^0RN<>[<5B>mEm'<EFBFBD>MOX[>e
 * l<EFBFBD><EFBFBD>Q<EFBFBD>^MO<EFBFBD><EFBFBD>HN(W<EFBFBD>m'<EFBFBD>MO<0C><><EFBFBD>HN(WEm'<EFBFBD>MO0
 * Created by jzx on 2015-9-16
 * Used by CkczManager{|
 * Modified by jzx on 2015-10-15 l<EFBFBD><EFBFBD>Q<EFBFBD>^MO<EFBFBD>X<EFBFBD>R <EFBFBD>^MO*Npe W[<EFBFBD>k[{	<EFBFBD><EFBFBD><EFBFBD>N<EFBFBD>S/f&T<EFBFBD><EFBFBD>*g<EFBFBD>[8h<EFBFBD>QeQ<EFBFBD>^USnc`S(u
 * Modified by jzx on 2017-9-30 KNMR*g<EFBFBD>Q<EFBFBD>qysxW[<EFBFBD>k<P:Nnull<EFBFBD>v<EFBFBD>^MO
 */
BEGIN

IF LEN(@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD>)>0
BEGIN
  IF @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE ',%'
    SET @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> = ',' + @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD>;
  IF @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE '%,'
    SET @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> = @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> + ',';
END;

SET NOCOUNT ON

DECLARE c CURSOR FOR
SELECT x.kwbh,x.hdh,r.cpbh,ISNULL(r.lpxx, ''),r.zlzt,r.rksj,cpxx.cfkwFqsx,x.qysx,x.kfwpgd,x.kfwpzl
  , '\R^(W
NM<EFBFBD><EFBFBD>[<5B>v<EFBFBD>mEm'<EFBFBD>MOXb<EFBFBD>vR_v^0RN<EFBFBD>[<EFBFBD>mEm'<EFBFBD>MOX[>e' bz
FROM kwjbxx x join (select kwbh kw,  max(b.cpbh)cpbh,max(b.lpxx)lpxx,max(b.zlzt)zlzt,count(*)jls, min(rksj) rksj
					from cpkcxx b where b.zlzt='2' and b.sl > 0 and isnumeric(b.cpbh)=1
					group by kwbh
					having count(*)=1 -- <EFBFBD>^MO
N<PX[>e<EFBFBD>NN<EFBFBD>y<EFBFBD>N<EFBFBD>T
					)r on x.kwbh=r.kw
	join cpxx on r.cpbh=cpxx.cpbh
WHERE x.zsl>0 AND x.sfsd=0 AND x.xnkw=0 
  AND (wckw IS NOT NULL OR nckw IS NOT NULL)
  AND x.<EFBFBD>S8OMO<EFBFBD><EFBFBD>	g'<EFBFBD> = 0
  AND (@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> IS NULL OR @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE '%,'+CAST(hdh AS varchar(2))+',%')
  AND (@P<EFBFBD><EFBFBD>[X[>eR:S IS NULL OR @P<EFBFBD><EFBFBD>[X[>eR:S=x.qysx)
  AND x.Xb<EFBFBD>v*Npe>0 -- nx<EFBFBD>OXb<EFBFBD>v(W<EFBFBD>^MO
N
  AND NOT EXISTS(SELECT 2 FROM v_*g<EFBFBD>[bc<EFBFBD>N v WHERE v.kwbh=x.kwbh) -- nx<EFBFBD>O<EFBFBD>l	gvQ<EFBFBD>N*g<EFBFBD>[8hUSnc<EFBFBD>d\Odk<EFBFBD>^MO

ORDER BY storeDist desc;

DECLARE @zckw VARCHAR(20), @zrkw VARCHAR(20), @hdh TINYINT, @zlbh INT
, @cpbh type_cpbh, @lpxx type_ph, @zlzt char(1), @P<EFBFBD><EFBFBD>[X[>eR:S<P varchar(20)
, @kfwpgd int, @kfwpzl int, @match char(1), @cntRows INT, @zcyy VARCHAR(200)
, @rksj DATE, @<EFBFBD>^MOR:S<P varchar(20);

SET @cntRows = 0;
SET @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = 0;
SET @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S = NULL;
IF ISNULL(@l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe, 0) < 1
  SET @l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe = 1;

OPEN c;
WHILE 1=1
BEGIN
  FETCH c INTO @zckw, @hdh, @cpbh, @lpxx, @zlzt, @rksj, @P<EFBFBD><EFBFBD>[X[>eR:S<P, @<EFBFBD>^MOR:S<P, @kfwpgd, @kfwpzl, @zcyy;
  IF @@FETCH_STATUS <> 0
    BREAK;

  SET @cntRows = @cntRows + 1;

  -- M<EFBFBD><EFBFBD>S<EFBFBD>^MO@b(W<EFBFBD>]S<EFBFBD><EFBFBD>_	gX[>e	gT<EFBFBD>N<EFBFBD>T<EFBFBD>vY*N<EFBFBD>^MO
  IF NOT EXISTS(select count(*)jls
					from cpkcxx b join kwjbxx k on b.kwbh=k.kwbh
          where b.zlzt='2'
            AND b.sl > 0 -- eQ<EFBFBD>^c<EFBFBD>N<EFBFBD>[bT<EFBFBD>eQ<EFBFBD>^US<EFBFBD>l	g<EFBFBD>[8hMR<EFBFBD>O<EFBFBD>Q<EFBFBD>ssl=0<EFBFBD>v<EFBFBD>`b_
            AND b.sl=k.zsl -- <EFBFBD>^MO
N<EFBFBD>SX[>eN<EFBFBD>y<EFBFBD>N<EFBFBD>T/fb<EFBFBD>z
            AND (wckw IS NOT NULL OR nckw IS NOT NULL)
            AND k.<EFBFBD>S8OMO<EFBFBD><EFBFBD>	g'<EFBFBD> = 0
				    AND k.hdh=@hdh AND b.cpbh=@cpbh AND ISNULL(b.lpxx,'')=@lpxx AND b.zlzt=@zlzt
					having count(*) > 1)
   begin
    PRINT '~b
N0R<EFBFBD>T<EFBFBD>Q<EFBFBD>^<5E>^MO' + @zckw + '
NX[>e<EFBFBD>N<EFBFBD>T<EFBFBD>vT<EFBFBD>v<EFBFBD>^MO'
    CONTINUE;
   end;
  
  SET @zrkw = NULL;
  SET @zlbh = NULL;
  SET @match = 0
  SELECT TOP 1 @zrkw=kwbh
       ,@match = case when k.kw is not null and substring(kwbh,3,4)=substring(kw,3,4) AND hdh=@hdh and nckw is null then 4 
                      when k.kw is not null and substring(kwbh,3,4)=substring(@zckw,3,4) AND hdh=@hdh and nckw is null then 3 
                      when k.kw is not null then 2 
                      when substring(kwbh,3,4)=substring(@zckw,3,4) then 1 
                      else 0 end
    FROM kwjbxx m left join (select distinct k.kwbh kw from cpkcxx k join kwjbxx r on k.kwbh=r.kwbh
                              -- Em'<EFBFBD>MO	gHQeQ<65>^<5E>vTirD<72><44>m<EFBFBD>^MO<0C>OHQX[>e
	                            where k.rksj < @rksj and cpbh=@cpbh and ISNULL(lpxx,'')=@lpxx and k.zlzt=@zlzt and nckw is not null 
                               and ISNULL(r.qysx, @P<EFBFBD><EFBFBD>[X[>eR:S<P)=@P<EFBFBD><EFBFBD>[X[>eR:S<P and xnkw=0 and r.hdh=@hdh
                              )k
                    on m.wckw=k.kw
      WHERE hdh=@hdh AND (ISNULL(qysx,@P<EFBFBD><EFBFBD>[X[>eR:S<P)=@P<EFBFBD><EFBFBD>[X[>eR:S<P) AND zsl=0 AND sfsd=0 AND xnkw=0
	      AND isnull(kfwpgd,0)>=isnull(@kfwpgd,0) AND isnull(kfwpzl,0)>=isnull(@kfwpzl,0)
        AND EXISTS(SELECT 2 FROM cpkcxx x JOIN kwjbxx k ON x.kwbh=k.kwbh
                      WHERE x.kwbh IN(m.wckw,m.nckw) AND x.kwbh != @zckw
                        AND x.sl=k.zsl AND x.sl>0
                        AND k.sfsd = 0
                        AND x.cpbh=@cpbh AND ISNULL(x.lpxx,'')=@lpxx AND x.zlzt=@zlzt
                  )

  ORDER BY 2 desc -- Em'<EFBFBD>MOX[>eTirD<72><44>^MOgOHQ<0C>vQ!kTRB\<5C>^MOOHQ
      , case when wckw is null then 0 else 1 end -- Em'<EFBFBD>MOOHQ
		  , storeDist;

  IF @zrkw IS NOT NULL
  BEGIN
    EXEC p_ubteXb<EFBFBD>vl<EFBFBD><EFBFBD>^c<EFBFBD>N @zlbh output, @zckw, @zrkw, @<EFBFBD>d\O(u7b
T, null, @zcyy;
    SET @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe + 1;
    IF @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S IS NULL
      SET @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S = @zlbh;
    print @zckw + '>>' + @zrkw;
  END
  SET @cntRows = @cntRows + 1;
  --PRINT CAST(@cntRows AS CHAR(4)) + '. ' + @zckw + ' > ' + ISNULL(@zrkw, '      ') + ' M<><4D>[=' + @match + ',' + @zcyy + ', R:S9SM<53><1F>' +  @P<EFBFBD><EFBFBD>[X[>eR:S<P + ' ' + @<EFBFBD>^MOR:S<P 
	-- + ',hdh=' + cast(@hdh as varchar(2)) + ',cffq=' + @P<EFBFBD><EFBFBD>[X[>eR:S<P + ',cpbh=' + @cpbh + ',lpxx=' + @lpxx + ',zlzt=' + @zlzt;

  IF @l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe <= @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe
    BREAK;
END;
CLOSE c;
DEALLOCATE c;

END
go
/****** Object:  StoredProcedure [dbo].[p_ꁨRub	g'<EFBFBD><EFBFBD>^MO<4D>vl<76><6C>^c<>N]    Script Date: 2017/9/30 fgmQ 15:01:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[p_ꁨRub	g'<EFBFBD><EFBFBD>^MO<4D>vl<76><6C>^c<>N]
(
@<EFBFBD>d\O(u7b
T varchar(20),
@l<EFBFBD><EFBFBD>Q<EFBFBD>^MOP<EFBFBD><EFBFBD>[ag<EFBFBD>N INT, -- 1<EFBFBD><EFBFBD>mEm'<EFBFBD>MOX[>eirD<72>
NT<0C>\<5C>m'<EFBFBD>MO'<EFBFBD>irl<EFBFBD>p<EFBFBD>	<09>|2<08>Em'<EFBFBD>MO:Nzz<EFBFBD>^MO<EFBFBD>e<EFBFBD><EFBFBD>m'<EFBFBD>MOXb<EFBFBD>vl<EFBFBD><EFBFBD>Q	<09>|4<08>\R:S
N9SM<EFBFBD><EFBFBD>vXb<EFBFBD>vl<EFBFBD>p<EFBFBD>	<09>
@l<EFBFBD><EFBFBD>Q<EFBFBD>N<EFBFBD>TP<EFBFBD><EFBFBD>[ag<EFBFBD>N VARCHAR(50), 
@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> VARCHAR(50),
@P<EFBFBD><EFBFBD>[X[>eR:S VARCHAR(50),
@l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe INT,
@b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe INT OUTPUT,
@l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S INT OUTPUT -- ubYagl<EFBFBD><EFBFBD>^c<EFBFBD>N/f<EFBFBD>ԏ<EFBFBD>V,{Nagl<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S
)
AS
/**
 * \<EFBFBD>mEm'<EFBFBD>MO
NX[>e
NT'<EFBFBD>ir<EFBFBD>v<EFBFBD>m'<EFBFBD>MObR:S
N9SM<EFBFBD><EFBFBD>vEm'<EFBFBD>MOXb<EFBFBD>vl<EFBFBD>p<EFBFBD>
 * Created by jzx on 2015-9-16
 * Used by  CkczManager{|
 * Modified by jzx on 2015-10-15 l<EFBFBD><EFBFBD>Q<EFBFBD>^MO<EFBFBD>X<EFBFBD>R <EFBFBD>^MO*Npe W[<EFBFBD>k[{	<EFBFBD><EFBFBD><EFBFBD>N<EFBFBD>S/f&T<EFBFBD><EFBFBD>*g<EFBFBD>[8h<EFBFBD>QeQ<EFBFBD>^USnc`S(u
 * Modified by jzx on 2017-9-30 KNMR*g<EFBFBD>Q<EFBFBD>qysxW[<EFBFBD>k<P:Nnull<EFBFBD>v<EFBFBD>`<EFBFBD>Q<EFBFBD><EFBFBD>S<EFBFBD>X<EFBFBD>RN*N<EFBFBD>m'<EFBFBD>MOXb<EFBFBD>vl<EFBFBD>eQEm'<EFBFBD>MO<EFBFBD>vR/e 
 */
BEGIN

  IF LEN(@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD>) > 0
  BEGIN
    IF @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE ',%'
      SET @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> = ',' + @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD>;
    IF @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE '%,'
      SET @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> = @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> + ',';
  END;

  SET NOCOUNT ON

  DECLARE c CURSOR FOR
  SELECT kwbh,hdh,r.cpbh,ISNULL(r.lpxx, ''),r.zlzt,rksj,cpxx.cfkwFqsx,x.qysx,x.kfwpgd,x.kfwpzl
    ,CASE WHEN wckw IS NOT NULL AND (r.cpbh <> r2.cpbh OR ISNULL(r.lpxx,'')<>ISNULL(r2.lpxx,'') OR r.zlzt<>r2.zlzt) THEN '<00>mEm'<EFBFBD>MOX[>eirD<EFBFBD>
NT<EFBFBD>\<EFBFBD>m'<EFBFBD>MO'<EFBFBD>irl<EFBFBD>p<EFBFBD>'
          WHEN wckw IS NOT NULL AND r2.cpbh IS NULL AND r2.zsl=0 THEN 'Em'<EFBFBD>MO:Nzz<EFBFBD>^MO<EFBFBD>e<EFBFBD><EFBFBD>m'<EFBFBD>MOXb<EFBFBD>vl<EFBFBD><EFBFBD>Q' 
          ELSE '\R:S
N9SM<EFBFBD><EFBFBD>vXb<EFBFBD>vl<EFBFBD>p<EFBFBD>' END bz
  FROM kwjbxx x join (select kwbh kw,  max(b.cpbh)cpbh,max(b.lpxx)lpxx,max(b.zlzt)zlzt,count(*)jls, min(rksj) rksj
					  from cpkcxx b where zlzt='2' and sl > 0 and isnumeric(cpbh)=1
					  group by kwbh
					  having count(*)=1
					  )r
			   on x.kwbh=r.kw
    LEFT join (select a.kwbh Em'<EFBFBD>MO, a.zsl, a.sfsd, max(b.cpbh)cpbh,max(b.lpxx)lpxx,max(b.zlzt)zlzt,count(*)jls
					  from kwjbxx a LEFT join cpkcxx b on a.kwbh = b.kwbh
            where nckw is not null
					  group by a.kwbh, a.zsl, a.sfsd
					  )r2
			   on x.wckw=r2.Em'<EFBFBD>MO
	  join cpxx on r.cpbh=cpxx.cpbh
  WHERE x.zsl>0 AND x.sfsd=0 AND x.xnkw=0
           -- X[>e
NTirD<EFBFBD><EFBFBD>vl<EFBFBD>p<EFBFBD>
    AND (  (x.wckw IS NOT NULL AND ((@l<EFBFBD><EFBFBD>Q<EFBFBD>^MOP<EFBFBD><EFBFBD>[ag<EFBFBD>N&1) != 0) AND (r.cpbh <> isnull(r2.cpbh,'') OR r.lpxx<>isnull(r2.lpxx,'') OR r.zlzt<>isnull(r2.zlzt,3)))
           -- Em'<EFBFBD>MO<EFBFBD>l'<EFBFBD>ir<EFBFBD>vl<EFBFBD>p<EFBFBD>
        OR (x.wckw IS NOT NULL AND ((@l<EFBFBD><EFBFBD>Q<EFBFBD>^MOP<EFBFBD><EFBFBD>[ag<EFBFBD>N&2) != 0) AND r2.cpbh IS NULL AND r2.zsl=0 AND r2.sfsd=0)
           -- X[>eTirD<EFBFBD><EFBFBD>FOR:S
N9SM<EFBFBD><EFBFBD>v_Nl<EFBFBD>p<EFBFBD>
        OR (x.qysx<>cpxx.cfkwFqsx AND ((@l<EFBFBD><EFBFBD>Q<EFBFBD>^MOP<EFBFBD><EFBFBD>[ag<EFBFBD>N&4) != 0)) 
        )
    AND (@l<EFBFBD><EFBFBD>Q<EFBFBD>N<EFBFBD>TP<EFBFBD><EFBFBD>[ag<EFBFBD>N IS NULL OR r2.cpbh+'.'+ISNULL(r2.lpxx,'')=@l<EFBFBD><EFBFBD>Q<EFBFBD>N<EFBFBD>TP<EFBFBD><EFBFBD>[ag<EFBFBD>N)
    AND (@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> IS NULL OR @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE '%,'+CAST(hdh AS varchar(2))+',%')
    AND (@P<EFBFBD><EFBFBD>[X[>eR:S IS NULL OR @P<EFBFBD><EFBFBD>[X[>eR:S=ISNULL(x.qysx,@P<EFBFBD><EFBFBD>[X[>eR:S))
    AND x.Xb<EFBFBD>v*Npe>0 -- nx<EFBFBD>OXb<EFBFBD>v(W<EFBFBD>^MO
N
    AND NOT EXISTS(SELECT 2 FROM v_*g<EFBFBD>[bc<EFBFBD>N v WHERE v.kwbh=x.kwbh) -- nx<EFBFBD>O<EFBFBD>l	gvQ<EFBFBD>N*g<EFBFBD>[8hUSnc<EFBFBD>d\Odk<EFBFBD>^MO

  ORDER BY CASE WHEN (x.wckw IS NOT NULL AND r.cpbh <> r2.cpbh OR ISNULL(r.lpxx,'')<>ISNULL(r2.lpxx,'') OR r.zlzt<>r2.zlzt) THEN 0
			          WHEN ISNULL(x.qysx,'')<>ISNULL(cpxx.cfkwFqsx,'') THEN 1
                WHEN (x.wckw IS NOT NULL AND r2.cpbh IS NULL AND r2.zsl=0) THEN 2
                ELSE 3 END
    , CASE WHEN wckw IS NOT NULL THEN 0 ELSE 1 END, storeDist desc;

  DECLARE @zckw VARCHAR(20), @zrkw VARCHAR(20), @hdh TINYINT, @zlbh INT
  , @cpbh type_cpbh, @lpxx type_ph, @zlzt char(1), @P<EFBFBD><EFBFBD>[X[>eR:S<P varchar(20)
  , @kfwpgd int, @kfwpzl int, @match char(1), @cntRows INT, @zcyy VARCHAR(200)
  , @rksj DATE, @<EFBFBD>^MOR:S<P varchar(20);

  SET @cntRows = 0;
  SET @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = 0;
  SET @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S = NULL;
  IF ISNULL(@l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe, 0) < 1
    SET @l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe = 1;

  OPEN c;
  WHILE 1=1
  BEGIN
    FETCH c INTO @zckw, @hdh, @cpbh, @lpxx, @zlzt, @rksj, @P<EFBFBD><EFBFBD>[X[>eR:S<P, @<EFBFBD>^MOR:S<P, @kfwpgd, @kfwpzl, @zcyy;
    IF @@FETCH_STATUS <> 0
      BREAK;

    SET @cntRows = @cntRows + 1;
  
    SET @zrkw = NULL;
    SET @zlbh = NULL;
    SET @match = 0
    SELECT TOP 1 @zrkw=kwbh
         ,@match = case when k.kw is not null and substring(kwbh,3,4)=substring(kw,3,4) AND hdh=@hdh and nckw is null then 4 
                        when k.kw is not null and substring(kwbh,3,4)=substring(@zckw,3,4) AND hdh=@hdh and nckw is null then 3 
                        when k.kw is not null then 2 
                        when substring(kwbh,3,4)=substring(@zckw,3,4) then 1 
                        else 0 end
      FROM kwjbxx m left join (select distinct k.kwbh kw from cpkcxx k join kwjbxx r on k.kwbh=r.kwbh
                                -- Em'<EFBFBD>MO	gHQeQ<65>^<5E>vTirD<72><44>m<EFBFBD>^MO<0C>OHQX[>e
	                              where k.rksj < @rksj and cpbh=@cpbh and ISNULL(lpxx,'')=@lpxx and k.zlzt=@zlzt and nckw is not null 
                                 and r.qysx=@P<EFBFBD><EFBFBD>[X[>eR:S<P and xnkw=0 and r.hdh=@hdh
                                )k
                      on m.wckw=k.kw
        WHERE hdh>=@hdh AND (ISNULL(qysx,@P<EFBFBD><EFBFBD>[X[>eR:S<P)=@P<EFBFBD><EFBFBD>[X[>eR:S<P OR @P<EFBFBD><EFBFBD>[X[>eR:S<P IS NULL)
           AND zsl=0 AND sfsd=0 AND xnkw=0
	        AND isnull(kfwpgd,0)>=isnull(@kfwpgd,0) AND isnull(kfwpzl,0)>=isnull(@kfwpzl,0)
          AND NOT EXISTS(SELECT 2 FROM cpkcxx x WHERE x.kwbh IN(m.wckw,m.nckw)
                          AND (x.cpbh<>@cpbh OR x.lpxx<>@lpxx OR x.zlzt<>@zlzt))

    ORDER BY case hdh when @hdh then 0 else 1 end
        , 2 desc -- Em'<EFBFBD>MOX[>eTirD<72><44>^MOgOHQ<0C>vQ!kTRB\<5C>^MOOHQ
        , case when wckw is null then 0 else 1 end -- Em'<EFBFBD>MOOHQ
		    , storeDist;

    IF @zrkw IS NOT NULL
    BEGIN
      EXEC p_ubteXb<EFBFBD>vl<EFBFBD><EFBFBD>^c<EFBFBD>N @zlbh output, @zckw, @zrkw, @<EFBFBD>d\O(u7b
T, null, @zcyy;
      SET @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe + 1;
      IF @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S IS NULL
        SET @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S = @zlbh;
      PRINT @zckw + '>>' + @zrkw;
    END
    ELSE
      PRINT @zckw + '>>NULL';

    SET @cntRows = @cntRows + 1;
    --PRINT CAST(@cntRows AS CHAR(4)) + '. ' + @zckw + ' > ' + ISNULL(@zrkw, '      ') + ' M<><4D>[=' + @match + ',' + @zcyy + ', R:S9SM<53><1F>' +  @P<EFBFBD><EFBFBD>[X[>eR:S<P + ' ' + @<EFBFBD>^MOR:S<P 
	  -- + ',hdh=' + cast(@hdh as varchar(2)) + ',cffq=' + @P<EFBFBD><EFBFBD>[X[>eR:S<P + ',cpbh=' + @cpbh + ',lpxx=' + @lpxx + ',zlzt=' + @zlzt;
 
    IF @l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe <= @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe
      BREAK;
  END;
  CLOSE c;
  DEALLOCATE c;

  -- 2017-9-30 <EFBFBD>e<EFBFBD>XR/e<EFBFBD><EFBFBD>Y<EFBFBD>g<EFBFBD>l	g<EFBFBD>v<EFBFBD>RꁨR\<EFBFBD>m'<EFBFBD>MO	g'<EFBFBD><EFBFBD>Em'<EFBFBD>MO<EFBFBD>l	g'<EFBFBD><EFBFBD>vXb<EFBFBD>vl<EFBFBD>p<EFBFBD>
  IF @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = 0
  BEGIN
    
    DECLARE c2 CURSOR FOR
    SELECT x.kwbh, x.nckw,x.hdh,r.cpbh,ISNULL(r.lpxx, ''),r.zlzt,rksj,x.kfwpgd,x.kfwpzl, 'ꁨR\Em'<EFBFBD>MOXb<EFBFBD>vl<EFBFBD>eQ<EFBFBD>m'<EFBFBD>MO' AS bz
    FROM kwjbxx x JOIN cpkcxx r ON x.kwbh=r.kwbh
    WHERE x.zsl>0 AND x.sfsd=0 AND x.xnkw=0 AND x.nckw IS NOT NULL
      AND x.<EFBFBD>S8OMO<EFBFBD><EFBFBD>	g'<EFBFBD> = 0
      AND x.Xb<EFBFBD>v*Npe>0 -- nx<EFBFBD>OXb<EFBFBD>v(W<EFBFBD>^MO
N
      AND NOT EXISTS(SELECT 2 FROM v_*g<EFBFBD>[bc<EFBFBD>N v WHERE v.kwbh=x.kwbh) -- nx<EFBFBD>O<EFBFBD>l	gvQ<EFBFBD>N*g<EFBFBD>[8hUSnc<EFBFBD>d\Odk<EFBFBD>^MO
      AND NOT EXISTS(SELECT 2 FROM kwjbxx n WHERE n.kwbh=x.nckw AND n.zsl=0 AND n.sfsd=0 AND n.Xb<EFBFBD>v*Npe=0)

    ORDER BY r.rksj DESC, storeDist desc;

    OPEN c2;
    WHILE 1=1
    BEGIN
      FETCH c2 INTO @zckw, @zrkw, @hdh, @cpbh, @lpxx, @zlzt, @rksj, @kfwpgd, @kfwpzl, @zcyy;
      IF @@FETCH_STATUS <> 0
        BREAK;
      
      SET @zlbh = NULL;
      
      EXEC p_ubteXb<EFBFBD>vl<EFBFBD><EFBFBD>^c<EFBFBD>N @zlbh output, @zckw, @zrkw, @<EFBFBD>d\O(u7b
T, null, @zcyy;
      SET @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe + 1;
      IF @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S IS NULL
        SET @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S = @zlbh;
      PRINT @zckw + '>>>' + @zrkw;
      
      SET @cntRows = @cntRows + 1;
 
      IF @l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe <= @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe
        BREAK;
    END;
    CLOSE c2;
    DEALLOCATE c2;
    
  END;
END
go