维富友ERP配货发出情况统计表

USE FY_LL082523
GO
/****** Object:  StoredProcedure [dbo].[p_yhj_ShotTimrationofPercentOrder]    Script Date: 2017-8-27 21:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_yhj_ShotTimrationofPercentOrder]
AS
    BEGIN
        DECLARE @ls_xsphdh NVARCHAR(30)= '';
        DECLARE @ls_xtwpdm NVARCHAR(30)= '';
        DECLARE @ls_xtwldm NVARCHAR(30)= '';	
        DECLARE @ls_xsphdhTemp NVARCHAR(30)= '';
		DECLARE @ls_xsphdhlast NVARCHAR(30)= '';
        DECLARE @ls_phsl INT= 0;
        DECLARE @ls_phsl_ori INT= 0;
        DECLARE @ls_phrows INT= 0;
        DECLARE @ls_i_fistdh INT= 0;
        DECLARE @ls_xsphdh1 NVARCHAR(30)= '';
        DECLARE @ls_xtwpdm1 NVARCHAR(30)= '';
        DECLARE @ls_xtwldm1 NVARCHAR(30)= '';	
        DECLARE @ls_xsphdhTemp1 NVARCHAR(30)= '';
        DECLARE @ls_phsl1 INT= 0;	DECLARE @ls_phcount INT= 0;	
        DECLARE @ls_return_i INT= 0;	
		DECLARE @ls_courrentIntOfPh INT= 0;
        DECLARE @ls_return_i1 INT= 0;	
        DECLARE @ls_int_temp INT= 0;	
        DECLARE @ls_int_temp1 INT= 0;
        DECLARE @ls_int_slOfPerPhd INT= 0;	
        DECLARE @ls_int_FirstRunInCursor INT= 0;	 
	
        DECLARE cursor_phdj CURSOR
        FOR
            SELECT  xst14.xsphdh ,
                    xst15.xtwpdm ,
                    xst15.xtwldm ,
                    CAST(SUM(xst15.xsphsl) AS INT) sl
            FROM    xst14 ,
                    xst15
            WHERE   xst14.xsczhm = xst15.xsczhm
                    AND xslrrq > '2017-08-16'
                    AND xst14.xtwldm IN ( SELECT    xtwldm
                                          FROM      xtm14
                                          WHERE     xtgsdm = '00'
                                                    AND xtkhlx IN ( '4', '5' )
                                                    AND xtwlzt = 'H' )
                     --AND LTRIM(RTRIM(xst14.xsphdh)) IN ('CZPH1708000394')  --,'CZPH1708000507'
            GROUP BY xst14.xsphdh ,
                    xst15.xtwpdm ,
                    xst15.xtwldm
            ORDER BY xst14.xsphdh ,
                    xst15.xtwpdm ,
                    xst15.xtwldm  
             SET @ls_i_fistdh = 0;
                 
				 --SELECT * FROM dbo.xst14

        OPEN cursor_phdj 
        FETCH cursor_phdj INTO @ls_xsphdh, @ls_xtwpdm, @ls_xtwldm, @ls_phsl; 
		--IF @ls_xsphdhTemp<>@ls_xsphdh
		--	SET @ls_xsphdhTemp=@ls_xsphdh 
	
    --IF ( @ls_int_FirstRunInCursor <> 0 )
    --    BEGIN
    --        SELECT  @ls_int_slOfPerPhd = CAST(SUM(xst15.xsphsl) AS INT)
    --        FROM    xst14 ,
    --                xst15
    --        WHERE   xst14.xsczhm = xst15.xsczhm
    --                AND xsshrq > '2016-08-16'
    --                AND xst14.xtwldm IN ( SELECT    xtwldm
    --                                      FROM      xtm14
    --                                      WHERE     xtgsdm = '00'
    --                                                AND xtkhlx IN ( '4', '5' )
    --                                                AND xtwlzt = 'H' )
    --                AND xsphdh = @ls_xsphdh	
    --    END
	
        WHILE ( @@fetch_status = 0 )
            BEGIN  
            
                IF @ls_i_fistdh=0
                 BEGIN
					 SET @ls_i_fistdh = 1
					 set @ls_xsphdhTemp = @ls_xsphdh
					 SET @ls_xsphdhlast=@ls_xsphdh;
				 end
				
			SELECT @ls_phcount=ISNULL(COUNT(*),0)  --当前配货单共有多少行，用于当前配货单 最后一行后，输出文字作准备。--@ls_phcount=
            FROM    xst14,xst15
            WHERE   xst14.xsczhm = xst15.xsczhm               
                     AND xst14.xsphdh =@ls_xsphdh --'CZDB1708000394'  --@ls_xsphdh

					 --SELECT * FROM xst14 WHERE  xst14.xsphdh ='CZDB1708000394' 
					 --SELECT * FROM xst15 WHERE xsczhm='001708003921'                
				
				 
                SELECT  @ls_return_i = ISNULL(SUM(AA.kcczsl),0)  --给某店的 条码 两位维度判断有多少出库。
                FROM     cz1436N.FY_ERP_NEW_125.dbo.kct03 AA
                WHERE   AA.kcczlx = '140'
                        AND AA.kcckdm = '20'
                        AND AA.kcczrq > '2017-08-16'
                        AND AA.xtwldm IN ( SELECT  xtwldm
                                        FROM    xtm14
                                        WHERE   xtgsdm = '00'
                                                AND xtkhlx IN ( '4', '5' )
                                                AND xtwlzt = 'H' )
                        AND LTRIM(RTRIM(AA.xtwldm)) = LTRIM(RTRIM(@ls_xtwldm))
                        AND LTRIM(RTRIM(AA.xtwpdm)) = LTRIM(RTRIM(@ls_xtwpdm))   
		
                SET @ls_phrows = @ls_phrows + 1; --当前配货单 计算到第几行了
                SET @ls_phsl_ori = @ls_phsl_ori + @ls_phsl;
						
                IF @ls_return_i > 0
                    BEGIN
                        SET @ls_int_temp = @ls_int_temp + @ls_return_i                        
                    END	

                FETCH cursor_phdj INTO @ls_xsphdh, @ls_xtwpdm, @ls_xtwldm,@ls_phsl; 
                    
                IF (@ls_xsphdhTemp <> @ls_xsphdh and @ls_i_fistdh<>0 OR @ls_phrows=@ls_phcount)
                    BEGIN
                        SET @ls_xsphdhTemp = @ls_xsphdh 
                        PRINT '配货单号:' + CAST(@ls_xsphdhlast AS NVARCHAR(30))
                            + '行数:' + CAST(@ls_phrows AS NVARCHAR(4))
                            + '需求数量：' + CAST(@ls_phsl_ori AS NVARCHAR(4))
                            + '实际配发数量：' + CAST(@ls_int_temp AS NVARCHAR(4))
                        SET @ls_phsl_ori = 0;
                        SET @ls_int_temp = 0;
                        SET @ls_i_fistdh = 0;
						SET @ls_phrows= 0;
						SET @ls_courrentIntOfPh=@ls_courrentIntOfPh+1;
                    END                   
            END 
        CLOSE cursor_phdj 
        DEALLOCATE cursor_phdj

    END
	--USE FY_LL082523

--EXEC p_yhj_ShotTimrationofPercentOrder