ALTER  FUNCTION [dbo].[GETCONSUMECHECKOUTPAYMENT]
    (
      @_StartDate VARCHAR(20) ,
      @_EndDate VARCHAR(20) ,
      @_UserId VARCHAR(20) ,
      @_aShopId VARCHAR(100)
    )
RETURNS @ConsumePayment TABLE
    (
      F_DATETIME DATETIME ,
      F_SHOPID VARCHAR(10) ,
      F_GOODSTYPE1ID VARCHAR(10) ,
      F_GOODSTYPE1NAME VARCHAR(20) ,
      F_CHECKOUTTYPE VARCHAR(50) ,
      F_CONSUMEID VARCHAR(10) ,
      F_GOODSID VARCHAR(10) ,
      F_GOODSNAME VARCHAR(20) ,
      F_MONEY DECIMAL(9, 2) ,
      F_REALLYMONEY DECIMAL(9, 2) ,
      F_TICKETAMOUNT DECIMAL(9, 2) ,
      F_CONSUMEAMOUNT DECIMAL(9, 2) ,
      F_VIPFACTMONEY DECIMAL(9, 2)
    )
AS
    BEGIN
        DECLARE @DATETIME DATETIME;
        DECLARE @SHOPID VARCHAR(10);
        DECLARE @GOODSTYPE1ID VARCHAR(10);
        DECLARE @GOODSTYPE1NAME VARCHAR(20);
        DECLARE @CONSUMEID VARCHAR(10);
        DECLARE @GOODSID VARCHAR(10);
        DECLARE @GOODSNAME VARCHAR(20);
        DECLARE @CHECKOUTID VARCHAR(20);
        DECLARE @CONSUMEMONEY DECIMAL(9, 2);
        DECLARE @SUMEMONEY DECIMAL(9, 2);
        DECLARE @CONSUMETYPE VARCHAR(20);
        DECLARE @TICKETMONEY DECIMAL(9, 2);
        DECLARE @TICKETOLDMONEY DECIMAL(9, 2);
        DECLARE @TICKETAMOUNT DECIMAL(9, 2);
    
        DECLARE @SUMTICKETMONEY DECIMAL(9, 2);
        DECLARE @SUMTICKETOLDMONEY DECIMAL(9, 2);
       
        DECLARE @CHECKOUTTYPE VARCHAR(20);
        DECLARE @MONEY DECIMAL(9, 2);
        DECLARE @REALLYMONEY DECIMAL(9, 2);
        DECLARE @VIPFACTMONEY DECIMAL(9, 2);
    
        DECLARE @GATHERING DECIMAL(9, 2);
        DECLARE @CONSUME DECIMAL(9, 2);
        DECLARE @GATHERINGSUM DECIMAL(9, 2);
        DECLARE @TICKET DECIMAL(9, 2);
        DECLARE @ODDMENT DECIMAL(9, 2);
        DECLARE @CLIENTFACTREBATE DECIMAL(9, 2);
      
        DECLARE @CONSUMECHECKOUT CURSOR;
        DECLARE @CHECKOUTPAYMENT CURSOR;
        DECLARE @CONSUMETICKET CURSOR;
        DECLARE @flag INTEGER;
        DECLARE @Percent DECIMAL(9, 2);
        DECLARE @ReceivablePercent DECIMAL(9, 2);
        DECLARE @CONSUMEAMOUNT DECIMAL(9, 2);
        DECLARE @REMAINAMOUNT DECIMAL(9, 2);
        DECLARE @SUPPLEMENTMONEY DECIMAL(9, 2);
        DECLARE @SUPPLEMENTYPE VARCHAR(10);  
        DECLARE @CURCHECKOUTID VARCHAR(20);


        DECLARE @CHECKOUT CURSOR;
        DECLARE @CURPAYMENTMONEY DECIMAL(9, 2); 
    
    
        DECLARE @StartDate VARCHAR(20);
        DECLARE @EndDate VARCHAR(20);
        DECLARE @UserId VARCHAR(20);
        DECLARE @aShopId VARCHAR(100);
        DECLARE @CURREALLYMONEY DECIMAL(9, 2); 
		


        DECLARE @temp_checkpayment TABLE
            (
              ID INT ,
              F_TYPE VARCHAR(50) ,
              F_GATHERING DECIMAL(9, 2) ,
              TEMPID INT
            );
        DECLARE @count INT;
    
        SET @StartDate = @_StartDate;
        SET @EndDate = @_EndDate;
        SET @UserId = @_UserId;
        SET @aShopId = @_aShopId;
	    
        SET @CURCHECKOUTID = '';
        SET @SUPPLEMENTMONEY = 0;

        IF @UserId = ''
            BEGIN
                IF @aShopId = ''
                    BEGIN 
                        SET @CHECKOUT = CURSOR FAST_FORWARD FOR 
			            SELECT F_DATETIME,F_SHOPID, F_ID,   F_CLIENTREBATE, F_ODDMENT FROM T_CHECKOUT
		             	WHERE F_DATETIME BETWEEN @StartDate AND @EndDate  ORDER BY F_ID;					  
                    END;
                ELSE
                    BEGIN
                        SET @CHECKOUT = CURSOR FAST_FORWARD FOR 
			             SELECT F_DATETIME,F_SHOPID, F_ID,   F_CLIENTREBATE, F_ODDMENT FROM T_CHECKOUT
		               	WHERE F_DATETIME BETWEEN @StartDate AND @EndDate AND F_SHOPID IN(SELECT col FROM SplitIn(@aShopId,',')) ORDER BY F_ID;	
                    END;
            END;
        ELSE
            BEGIN
                IF @aShopId = ''
                    BEGIN 
                        SET @CHECKOUT = CURSOR FAST_FORWARD FOR 
			            SELECT F_DATETIME,F_SHOPID, F_ID,   F_CLIENTREBATE, F_ODDMENT FROM T_CHECKOUT
		             	WHERE F_DATETIME BETWEEN @StartDate AND @EndDate AND F_USERID = @UserId ORDER BY F_ID;	
                    END;
                ELSE
                    BEGIN
                        SET @CHECKOUT = CURSOR FAST_FORWARD FOR 
			            SELECT F_DATETIME,F_SHOPID, F_ID,   F_CLIENTREBATE, F_ODDMENT FROM T_CHECKOUT
		             	WHERE F_DATETIME BETWEEN @StartDate AND @EndDate AND F_SHOPID IN(SELECT col FROM SplitIn(@aShopId,',')) AND F_USERID = @UserId ORDER BY F_ID;	
                    END;		  
            END;
		   
        SET @CHECKOUT = CURSOR FAST_FORWARD FOR 
			  SELECT F_DATETIME,F_SHOPID, F_ID,   F_CLIENTREBATE, F_ODDMENT FROM T_CHECKOUT
			WHERE F_DATETIME BETWEEN @StartDate AND @EndDate AND F_SHOPID IN(SELECT col FROM SplitIn(@aShopId,',')) ORDER BY F_ID;
        OPEN @CHECKOUT; 

        WHILE @@FETCH_STATUS = 0
            BEGIN
                INSERT  INTO @temp_checkpayment
                        SELECT  ( ROW_NUMBER() OVER ( ORDER BY F_GATHERING  ) ) AS ID ,
                                F_TYPE ,
                                F_GATHERING ,
                                ( ROW_NUMBER() OVER ( ORDER BY F_GATHERING  ) ) AS TEMPID
                        FROM    T_CHECKOUTPAYMENT
                        WHERE   F_CHECKOUTID = @CHECKOUTID ORDER BY F_GATHERING  ;	

                SET @CONSUMECHECKOUT = CURSOR FAST_FORWARD FOR 
			  SELECT F_GOODSTYPE1ID,F_GOODSTYPE1NAME,F_GOODSID, F_GOODSNAME, 
			         F_GATHERINGSUM,F_SUM,F_TYPE,F_ID, F_AMOUNT FROM V_CONSUMECHECKOUT
			WHERE F_DATETIME BETWEEN @StartDate AND @EndDate AND F_CHECKOUTID = @CHECKOUTID ORDER BY  F_GATHERINGSUM;
                OPEN @CONSUMECHECKOUT; 

                FETCH NEXT FROM  @CONSUMECHECKOUT INTO @GOODSTYPE1ID,
                    @GOODSTYPE1NAME, @GOODSID, @GOODSNAME, @CONSUMEMONEY,
                    @SUMEMONEY, @CONSUMETYPE, @CONSUMEID, @CONSUMEAMOUNT;

                WHILE @@FETCH_STATUS = 0
                    BEGIN

                        SET @CONSUMETICKET = CURSOR FAST_FORWARD FOR 
				   SELECT F_MONEY,F_OLDMONEY FROM T_TICKET WHERE F_CONSUMEID = @CONSUMEID AND F_CHECKOUTID = @CHECKOUTID;
                        OPEN @CONSUMETICKET;

                        FETCH NEXT FROM  @CONSUMETICKET INTO @TICKETMONEY,
                            @TICKETOLDMONEY;
                        SET @TICKETAMOUNT = 0; 
                        SET @SUMTICKETMONEY = 0;
                        SET @SUMTICKETOLDMONEY = 0;

                        WHILE @@FETCH_STATUS = 0
                            BEGIN
		
                                SET @SUMTICKETMONEY = @SUMTICKETMONEY
                                    + @TICKETMONEY; 
                                SET @SUMTICKETOLDMONEY = @SUMTICKETOLDMONEY
                                    + @TICKETOLDMONEY; 
                                SET @TICKETAMOUNT = @TICKETAMOUNT + 1; 

                                FETCH NEXT FROM  @CONSUMETICKET INTO @TICKETMONEY,
                                    @TICKETOLDMONEY;
                            END;

                        CLOSE @CONSUMETICKET;
                        DEALLOCATE @CONSUMETICKET;
                        IF @TICKETAMOUNT > 0
                            BEGIN    
				   --原来的消费券金额  真正抵消的消费券金额
                                INSERT  @ConsumePayment
                                VALUES  ( @DATETIME, @SHOPID, @GOODSTYPE1ID,
                                          @GOODSTYPE1NAME, '消费券', @CONSUMEID,
                                          @GOODSID, @GOODSNAME,
                                          @SUMTICKETOLDMONEY, @SUMTICKETMONEY,
                                          @TICKETAMOUNT, 0, 0 ); 
                            END;

                     
		      
                        SET @REMAINAMOUNT = @CONSUMEAMOUNT - @TICKETAMOUNT;
                        IF @REMAINAMOUNT < 0
                            BEGIN
                                SET @REMAINAMOUNT = 0; 
                            END; 
                        SET @MONEY = @SUMEMONEY - @SUMTICKETOLDMONEY;
                        SET @CURREALLYMONEY = @CONSUMEMONEY - @SUMTICKETMONEY
                            - @ODDMENT; 
                        SET @ODDMENT = 0;

                        SELECT  @count = COUNT(1)
                        FROM    @temp_checkpayment;
                        WHILE @count > 0
                            BEGIN
                                SELECT  @CHECKOUTTYPE = F_TYPE ,
                                        @CURPAYMENTMONEY = F_GATHERING
                                FROM    @temp_checkpayment
                                WHERE   ID = @count;


                                IF ( @CURPAYMENTMONEY - @CURREALLYMONEY > 0 )
                                    BEGIN
                                        SET @CURPAYMENTMONEY = @CURPAYMENTMONEY
                                            - @CURREALLYMONEY; 
                                        SET @REALLYMONEY = @CURREALLYMONEY;
                                        SET @CURREALLYMONEY = 0;
                                    END;
                                ELSE
                                    BEGIN
                                        SET @REALLYMONEY = @CURPAYMENTMONEY;  
                                        SET @CURREALLYMONEY = @CURREALLYMONEY
                                            - @REALLYMONEY; 
                                        SET @CURPAYMENTMONEY = 0;
                                    END;

                                SET @VIPFACTMONEY = 0;

                                IF @CHECKOUTTYPE = '储值卡'
                                    BEGIN
                                        SET @REALLYMONEY = @REALLYMONEY; 
                                        SET @VIPFACTMONEY = @REALLYMONEY
                                            * @CLIENTFACTREBATE / 100; 
                                    END;

                                INSERT  @ConsumePayment
                                VALUES  ( @DATETIME, @SHOPID, @GOODSTYPE1ID,
                                          @GOODSTYPE1NAME, @CHECKOUTTYPE,
                                          @CONSUMEID, @GOODSID, @GOODSNAME,
                                          @MONEY, @REALLYMONEY, 0,
                                          @REMAINAMOUNT, @VIPFACTMONEY ); 

                                SET @REMAINAMOUNT = 0;

                                UPDATE  @temp_checkpayment
                                SET     F_GATHERING = @CURPAYMENTMONEY
                                WHERE   ID = @count;

								 --更新表变量的索引
                                IF ( @CURPAYMENTMONEY = 0 )
                                    BEGIN
                                        UPDATE  @temp_checkpayment
                                        SET     TEMPID = TEMPID - 1
                                        WHERE   ID > @count;
                                    END;

                                SET @MONEY = 0;

                                IF ( @CURREALLYMONEY = 0 )
                                    BEGIN

                                        BREAK;
                                    END;

                                SET @count = @count - 1;
                            END;	

                        DELETE  @temp_checkpayment
                        WHERE   F_GATHERING = 0;

                        

	     
                        FETCH NEXT FROM  @CONSUMECHECKOUT INTO @GOODSTYPE1ID,
                            @GOODSTYPE1NAME, @GOODSID, @GOODSNAME,
                            @CONSUMEMONEY, @SUMEMONEY, @CONSUMETYPE,
                            @CONSUMEID, @CONSUMEAMOUNT;
                    END;  

                CLOSE @CONSUMECHECKOUT;
                DEALLOCATE @CONSUMECHECKOUT; 

                FETCH NEXT FROM  @CHECKOUT INTO @DATETIME, @SHOPID,
                    @CHECKOUTID, @CLIENTFACTREBATE, @ODDMENT;
            END;
        RETURN;					
    END;


GO