IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'V_CONSUMECHECKOUT'))
  DROP VIEW V_CONSUMECHECKOUT
GO

CREATE VIEW [V_CONSUMECHECKOUT] AS 
SELECT
	dbo.T_CONSUME.F_ID,
	dbo.V_CHECKOUT.F_SKIPID,
	dbo.V_CHECKOUT.F_ID AS F_CHECKOUTID,
	dbo.V_CHECKOUT.F_DATETIME,
	dbo.T_CONSUME.F_RECEIVEID,
	dbo.T_CONSUME.F_GOODSID,
	dbo.T_GOODS.F_NAME AS F_GOODSNAME,
	dbo.T_GOODS.F_GOODSTYPEID,
	dbo.T_GOODSTYPE.F_NAME AS F_GOODSTYPENAME,
	dbo.T_GOODS.F_GOODSTYPE1ID,
	dbo.T_GOODSTYPE1.F_NAME AS F_GOODSTYPE1NAME,
	dbo.T_GOODS.F_PRICE AS F_GOODSPRICE,
	dbo.T_CONSUME.F_OLDPRICE,
	dbo.T_CONSUME.F_PRICE,
	dbo.T_CONSUME.F_AMOUNT,
	dbo.T_CONSUME.F_RECEIVABLESUM AS F_SUM,
	dbo.T_CONSUME.F_ISPRESENT,
	dbo.T_CONSUME.F_CANREBATE,
	dbo.T_CONSUME.F_REBATE,
	dbo.T_CONSUME.F_REMARK,
	dbo.V_CHECKOUT.F_USERID,
	dbo.V_CHECKOUT.F_SHOPID,
	dbo.V_CHECKOUT.F_CLIENTID,
	dbo.V_CHECKOUT.F_TYPE,
	dbo.T_CONSUME.F_COST,
	dbo.T_CONSUME.F_STATE,
	dbo.T_GOODS.F_STARTTIMES,
	dbo.T_CONSUME.F_STARTDATETIME,
	dbo.T_CONSUME.F_ENDDATETIME,
	dbo.T_RECEIVE.F_HANDBRANDID,
	dbo.T_RECEIVE.F_ROOMID,
	dbo.T_RECEIVE.F_AMOUNT AS F_PERSONTOTALAMOUNT,
	dbo.T_RECEIVE.F_MALEAMOUNT,
	dbo.T_RECEIVE.F_TRANSMITGROUP,
	dbo.T_CONSUME.F_ISPROMOTION,
	dbo.T_CONSUME.F_ISTOGO,
	dbo.T_CONSUME.F_ISTIME,
	dbo.T_CONSUME.F_TIMEMEMBERID,
	dbo.T_CONSUME.F_CLIENTREBATE,
	dbo.T_CONSUME.F_CLIENTGOODSREBATE,
	dbo.T_CONSUME.F_PROMOTIONREBATE,
	dbo.T_CONSUME.F_CHECKOUTREBATE,
	(
		CASE
		WHEN t_consume.f_istime = 1 THEN
			t_consume.f_oldsum
		ELSE
			t_consume.f_gatheringsum
		END
	) AS F_GATHERINGSUM,
	dbo.T_CONSUME.F_OLDSUM,
	dbo.T_CONSUME.F_RECEIVABLESUM,
	dbo.T_CONSUME.F_WORKERDEDUCT,
	dbo.T_UNIT.F_NAME AS F_UNITNAME,
	dbo.T_CONSUME.F_TURNREMARK,
	dbo.T_CONSUME.F_MINISTERID,
	dbo.T_CONSUME.F_GATHERINGSUM * dbo.T_CONSUME.F_CLIENTREBATE / 100 AS F_PRACTICESUM,
	dbo.V_CHECKOUT.F_HANDWORKID,
	dbo.T_CARDTYPE.F_ID AS F_CARDTYPEID,
	dbo.T_CARDTYPE.F_NAME AS F_CARDNAME,
	CAST (
		COALESCE (
			dbo.V_CONSUMEWORKERFORCONSUME.F_ISNAMED,
			0
		) AS INTEGER
	) AS F_ISNAMED,
	CAST (
		COALESCE (
			dbo.V_CONSUMEWORKERFORCONSUME.F_ISADDCLOCK,
			0
		) AS INTEGER
	) AS F_ISADDCLOCK,
            dbo.T_CONSUME.F_TICKETMONEY,
            T_CLIENT.F_REBATE AS F_CLIENTFACTREBATE,
            T_BathAmount.F_Amount AS F_ALLAMOUNT,
            T_BathAmount.F_GiveAmount,
            T_BathAmount.F_ManJianAmount,
            dbo.T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID ,
            dbo.T_ROOM.F_SHOWID AS F_ROOMSHOWID ,
            dbo.T_GOODS.F_SHOWID AS F_GOODSSHOWID ,
            dbo.T_WORKER.F_ID AS F_MINISTERSHOWID,
			dbo.T_GOODS.F_ISSMALL AS F_ISSMALL 
    FROM    dbo.T_CONSUME
            INNER JOIN dbo.T_RECEIVE ON ( dbo.T_CONSUME.F_RECEIVEID = dbo.T_RECEIVE.F_ID
                                          AND dbo.T_CONSUME.F_GOODSID <> 'StopClock')
            INNER JOIN dbo.V_CHECKOUT ON dbo.T_RECEIVE.F_CHECKOUTID = dbo.V_CHECKOUT.F_ID
            LEFT OUTER JOIN dbo.T_GOODS ON dbo.T_CONSUME.F_GOODSID = dbo.T_GOODS.F_ID
            LEFT OUTER JOIN dbo.T_GOODSTYPE ON dbo.T_GOODS.F_GOODSTYPEID = dbo.T_GOODSTYPE.F_ID
            LEFT OUTER JOIN dbo.T_GOODSTYPE1 ON dbo.T_GOODS.F_GOODSTYPE1ID = dbo.T_GOODSTYPE1.F_ID
            LEFT OUTER JOIN dbo.T_UNIT ON dbo.T_GOODS.F_UNITID = dbo.T_UNIT.F_ID
            LEFT OUTER JOIN dbo.T_CLIENT ON dbo.T_CLIENT.F_ID = dbo.V_CHECKOUT.F_CLIENTID
            LEFT OUTER JOIN dbo.T_CARDTYPE ON dbo.T_CLIENT.F_CARDTYPEID = dbo.T_CARDTYPE.F_ID
            LEFT OUTER JOIN dbo.V_CONSUMEWORKERFORCONSUME ON dbo.V_CONSUMEWORKERFORCONSUME.F_CONSUMEID = dbo.T_CONSUME.F_ID
            LEFT JOIN dbo.T_BathAmount ON ( T_BathAmount.F_CONSUMEID = T_CONSUME.F_ID )
            LEFT OUTER JOIN dbo.T_ROOM ON dbo.T_RECEIVE.F_ROOMID = dbo.T_ROOM.F_ID
            LEFT OUTER JOIN dbo.T_HANDBRAND ON dbo.T_RECEIVE.F_HANDBRANDID = dbo.T_HANDBRAND.F_ID
            LEFT OUTER JOIN dbo.T_WORKER ON dbo.T_CONSUME.F_MINISTERID = dbo.T_WORKER.F_ARCHIVESID;
;

GO


IF EXISTS (select * from dbo.sysobjects where id =object_id(N'GETCONSUMECHECKOUTPAYMENT') and xtype in (N'FN', N'IF', N'TF'))
BEGIN   
  drop function GETCONSUMECHECKOUTPAYMENT;
END

GO

CREATE  FUNCTION 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_ISSMALL, 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