---------------------------------7.0.2T-------------------------------
---------------------------------对接服务员点餐先支付-------------------------------
IF NOT EXISTS ( SELECT  *
                FROM    Meal_TakeAwayPlatTypes 
                WHERE   TakeAwayPaltName  = '服务员点餐' ) 
INSERT INTO dbo.Meal_TakeAwayPlatTypes
        ( Id ,
          TakeAwayPaltName ,
          InterFacePlatName
        )
VALUES  ( 13 , -- Id - int
          N'服务员点餐' , -- TakeAwayPaltName - nvarchar(50)
          N'WA'  -- InterFacePlatName - nvarchar(50)
        )
Go
ALTER    VIEW [dbo].[Meal_V_MKTakeAwayOrder] --结账客人按付款方式分组统计
AS
		  SELECT [Id]
			  ,[CreateTime]
			  ,[OrderTotalAmount]
			  ,[DishAmount]
			  ,[PackAmount]
			  ,[DeliveryAmount]
			  ,[Name]
			  ,[Phone]
			  ,[Address]
			  ,[DeliveryTime]
			  ,[PayType]--支付方式
			  ,PayTypeMessage=CASE WHEN paytype=0 THEN '餐到付款'
			  WHEN paytype=1 THEN '支付宝扫码支付'
			  WHEN paytype =2 THEN '微信扫码支付'
			  WHEN paytype=4 THEN '在线支付'
			  ELSE '已支付'
			  END
			  ,[Remark]
			  ,[MKDisheDetailsId]
			  ,[DeliverType]--配送方式
			  ,DeliverTypeMessage=CASE WHEN deliverType=0 THEN '商家配送'
			  WHEN deliverType=1 THEN '到店自取'
			  END
			  ,[DeliveryNumber]
			  ,[PeopleNumber]
			  ,[State]
			  ,StateMessage =CASE WHEN STATE=0 THEN '等待接单'
			  WHEN STATE =1 THEN '已接单'
			  WHEN STATE=2 THEN '已入账'
			  WHEN STATE =-1 THEN '退单'
			  WHEN STATE =-2 THEN '退单'
			  END
			  ,[MemberPhone]
			  ,[ReceivablePrice]
			  ,[DiscountPrice]
			  ,[OrderNo]
			  ,[RealPrice]
			  ,[PayTime]
			  ,[PayState]--支付状态
			  ,PayStateMessage =CASE WHEN PayState=0 THEN '未支付'
			  WHEN PayState=1 THEN '已支付'
			  END
			  ,[BillId]
			  ,'米客外卖' AS TakeAwayType
			  ,daySeq
			  ,RestaurantIncome
			  ,'' AS DiningTableNo
		  FROM [dbo].[Meal_MKTakeAwayOrder]
		  WHERE TakeAwayType='9'--米客外卖
		  UNION ALL
		  SELECT o.[Id]
			  ,[CreateTime]
			  ,[OrderTotalAmount]
			  ,[DishAmount]
			  ,[PackAmount]
			  ,[DeliveryAmount]
			  ,[Name]
			  ,[Phone]
			  ,[Address]
			  ,[DeliveryTime]
			  ,[PayType]--支付方式
			  ,t.TakeAwayPaltName+'已支付'
			  ,[Remark]
			  ,[MKDisheDetailsId]
			  ,[DeliverType]--配送方式
			  ,DeliverTypeMessage=CASE WHEN deliverType=0 THEN '商家配送'
			  WHEN deliverType=1 THEN '到店自取'
			  END
			  ,[DeliveryNumber]
			  ,[PeopleNumber]
			  ,[State]
			  ,StateMessage =CASE WHEN STATE=0 THEN '等待接单'
			  WHEN STATE =1 THEN '已接单'
			  WHEN STATE=2 THEN '已入账'
			  WHEN STATE =-1 THEN '退单'
			  WHEN STATE =-2 THEN '退单'
			  END
			  ,[MemberPhone]
			  ,[ReceivablePrice]
			  ,[DiscountPrice]
			  ,[OrderNo]
			  ,RestaurantIncome--代表平台实付
			  ,[PayTime]
			  ,[PayState]--支付状态
			  ,PayStateMessage =CASE WHEN PayState=0 THEN '未支付'
			  WHEN PayState=1 THEN '已支付'
			  END
			  ,[BillId]
			  ,t.TakeAwayPaltName
			  ,daySeq
			  ,RestaurantIncome
			  ,'' AS DiningTableNo
		  FROM [dbo].[Meal_MKTakeAwayOrder] AS o LEFT JOIN Meal_TakeAwayPlatTypes AS T ON o.TakeAwayType=t.Id
		  WHERE T.id IN ('10','11','12')--美团外卖，饿了么外卖，百度外卖
		  UNION ALL
		    SELECT o.[Id]
			  ,[CreateTime]
			  ,[OrderTotalAmount]
			  ,[DishAmount]
			  ,[PackAmount]
			  ,[DeliveryAmount]
			  ,[Name]
			  ,[Phone]
			  ,[Address]
			  ,[DeliveryTime]
			  ,[PayType]--支付方式
			  ,'自助点餐'
			  ,[Remark]
			  ,[MKDisheDetailsId]
			  ,[DeliverType]--配送方式
			  ,DeliverTypeMessage=CASE WHEN t.Id=5 THEN '扫码点餐'
			  WHEN t.Id=6 THEN '秒吃'
			  WHEN t.Id=7 THEN '自取'
			  WHEN t.Id=8  THEN '外送'
			  WHEN t.id=13 THEN '服务员点餐'
			  END
			  ,[DeliveryNumber]
			  ,[PeopleNumber]
			  ,[State]
			  ,StateMessage =CASE WHEN STATE=0 THEN '等待接单'
			  WHEN STATE =1 THEN '已接单'
			  WHEN STATE=2 THEN '已入账'
			  WHEN STATE =-1 THEN '退单'
			  WHEN STATE =-2 THEN '退单'
			  END
			  ,[MemberPhone]
			  ,[ReceivablePrice]
			  ,[DiscountPrice]
			  ,[OrderNo]
			  ,RestaurantIncome--代表平台实付
			  ,[PayTime]
			  ,[PayState]--支付状态
			  ,PayStateMessage =CASE WHEN PayState=0 THEN '未支付'
			  WHEN PayState=1 THEN '已支付'
			  END
			  ,[BillId]
			  ,t.TakeAwayPaltName
			  ,daySeq
			  ,RestaurantIncome
			  ,ISNULL(DiningTableNo,'')
		  FROM [dbo].[Meal_MKTakeAwayOrder] AS o LEFT JOIN Meal_TakeAwayPlatTypes AS T ON o.TakeAwayType=t.Id
		  WHERE T.id IN ('5','6','7','8','13')
		  
GO

ALTER     PROCEDURE [dbo].[Meal_GetReportBills]
    @beginBusinessDate DATETIME ,
    @endBusinessDate DATETIME ,
    @businessHourNo VARCHAR(2) = '%' ,
    @IsCheckout BIT = 1 ,
    @tableNo VARCHAR(5) = '%' ,
    @operatorNo VARCHAR(5) = '%' ,
    @saleType INT = 0 ,--0 全部，1 堂吃，2 外卖，4外带，9 外卖平台，5扫码点餐，6 秒吃，7 自取，8 外送,13 服务员点餐
    @serialNo VARCHAR(5) = '%' ,
    @ClubCardNo VARCHAR(16) = '%'
AS 
    CREATE TABLE #ReportBills
        (
          Id UNIQUEIDENTIFIER NOT NULL ,
          DiningAreaId UNIQUEIDENTIFIER NOT NULL ,
          DiningAreaName NVARCHAR(20) NOT NULL ,
          DiningTableId UNIQUEIDENTIFIER NOT NULL ,
          DiningTableNo VARCHAR(5) NOT NULL ,
          DiningTableName NVARCHAR(20) NOT NULL ,
          BillNo VARCHAR(13) NOT NULL ,
          BusinessDate SMALLDATETIME NOT NULL ,
          BusinessHourId INT NOT NULL ,
          BusinessHourName NVARCHAR(20) NOT NULL ,
          ArrangeDatetime DATETIME NOT NULL ,
          NumberOfPersons INT NULL ,
          CompanyId UNIQUEIDENTIFIER NULL ,
          CustomerId UNIQUEIDENTIFIER NULL ,
          ClubCardTypeId INT NULL ,
          ClubCardNo VARCHAR(15) NULL ,
          SalesManageId UNIQUEIDENTIFIER NULL ,
          VolumeOfConsumption DECIMAL(11, 2) NOT NULL ,
          GratisValue DECIMAL(9, 2) NOT NULL ,
          Charge4Room DECIMAL(9, 2) NOT NULL ,
          ChargeLimit DECIMAL(9, 2) NOT NULL ,
          ServiceCharge DECIMAL(9, 2) NOT NULL ,
          OvertimePremium DECIMAL(9, 2) NOT NULL ,
          DiscountCategory TINYINT NOT NULL ,
          DiscountValue DECIMAL(9, 2) NOT NULL ,
          PricePromotionDis NUMERIC(9, 2) NULL ,
          DiscountApproverId UNIQUEIDENTIFIER NULL ,
          DerateValue DECIMAL(9, 2) NOT NULL ,
          DerateApproverId UNIQUEIDENTIFIER NULL ,
          DerationId INT NULL ,
          Oddment DECIMAL(3, 2) NOT NULL ,
          PayAmount DECIMAL(12, 2) NOT NULL ,
          IsCheckedOut BIT NOT NULL ,
          IsInvoiced BIT NOT NULL ,
          InvoiceNo VARCHAR(20) NULL ,
          InvoiceAmount DECIMAL(12, 2) NULL ,
          IsCloseOut BIT NOT NULL ,
          PayDatetime DATETIME NULL ,
          PrintedTimes TINYINT NOT NULL ,
          OperatorId UNIQUEIDENTIFIER NULL ,
          OperatorName NVARCHAR(50) NULL ,
          SaleType INT NULL ,
          SaleTypeName VARCHAR(20) NULL ,
          SerialNo VARCHAR(30) NULL,
          TakeAwayPlatTypeId INT NULL,
        )
    INSERT  INTO #ReportBills
            ( Id ,
              DiningAreaId ,
              DiningAreaName ,
              DiningTableId ,
              DiningTableNo ,
              DiningTableName ,
              BillNo ,
              BusinessDate ,
              BusinessHourId ,
              BusinessHourName ,
              ArrangeDatetime ,
              NumberOfPersons ,
              CompanyId ,
              CustomerId ,
              ClubCardTypeId ,
              ClubCardNo ,
              SalesManageId ,
              VolumeOfConsumption ,
              GratisValue ,
              Charge4Room ,
              ChargeLimit ,
              ServiceCharge ,
              OvertimePremium ,
              DiscountCategory ,
              DiscountValue ,
              PricePromotionDis ,
              DiscountApproverId ,
              DerateValue ,
              DerateApproverId ,
              DerationId ,
              Oddment ,
              PayAmount ,
              IsCheckedOut ,
              IsInvoiced ,
              InvoiceNo ,
              InvoiceAmount ,
              IsCloseOut ,
              PayDatetime ,
              PrintedTimes ,
              OperatorId ,
              OperatorName ,
              SaleType ,
              SaleTypeName ,
              SerialNo,
              TakeAwayPlatTypeId
            )  
            EXECUTE dbo.Meal_GetBills @beginBusinessDate, @endBusinessDate,
                @businessHourNo, @IsCheckout, @tableNo, @operatorNo
                --0 全部，1 堂吃，2 外卖，4外带，9 外卖平台，5扫码点餐，6 秒吃，7 自取，8 外送  SELECT*   FROM    dbo.Meal_V_Bills4GetBills 
  
  IF ( @saleType = 0 ) 
  BEGIN 
  --查询全部
            SELECT  *
            FROM    #ReportBills
            WHERE   IsCheckedOut = 1
                    AND SerialNo LIKE @serialNo + '%'
                    AND ClubCardNo LIKE @ClubCardNo
  END  
  IF(@saleType =1)
  BEGIN 
  -- 查询堂吃
    SELECT  *
     FROM    #ReportBills
    WHERE   IsCheckedOut = 1
            AND SerialNo LIKE @serialNo + '%'
            AND ClubCardNo LIKE @ClubCardNo
            AND TakeAwayPlatTypeId = 1
  END  
  IF(@saleType =2)
  BEGIN 
  -- 查询外卖--2 外卖，9 mk外卖，10 美团外卖，11饿了么
    SELECT  *
     FROM    #ReportBills
    WHERE   IsCheckedOut = 1
            AND SerialNo LIKE @serialNo + '%'
            AND ClubCardNo LIKE @ClubCardNo
            AND TakeAwayPlatTypeId IN ('2','9','10','11','12')
  END  
   IF(@saleType =4)
  BEGIN 
  -- 查询外带
    SELECT  *
     FROM    #ReportBills
    WHERE   IsCheckedOut = 1
            AND SerialNo LIKE @serialNo + '%'
            AND ClubCardNo LIKE @ClubCardNo
            AND TakeAwayPlatTypeId =4
  END  
   IF(@saleType =5)
  BEGIN 
  -- 查询扫码点餐
    SELECT  *
     FROM    #ReportBills
    WHERE   IsCheckedOut = 1
            AND SerialNo LIKE @serialNo + '%'
            AND ClubCardNo LIKE @ClubCardNo
            AND TakeAwayPlatTypeId =5
  END  
   IF(@saleType =6)
  BEGIN 
  -- 查询秒吃
    SELECT  *
     FROM    #ReportBills
    WHERE   IsCheckedOut = 1
            AND SerialNo LIKE @serialNo + '%'
            AND ClubCardNo LIKE @ClubCardNo
            AND TakeAwayPlatTypeId =6
  END  
   IF(@saleType =7)
  BEGIN 
  -- 查询自取
    SELECT  *
     FROM    #ReportBills
    WHERE   IsCheckedOut = 1
            AND SerialNo LIKE @serialNo + '%'
            AND ClubCardNo LIKE @ClubCardNo
            AND TakeAwayPlatTypeId =7
  END  
  IF(@saleType =8)
  BEGIN 
  -- 查询外送
    SELECT  *
     FROM    #ReportBills
    WHERE   IsCheckedOut = 1
            AND SerialNo LIKE @serialNo + '%'
            AND ClubCardNo LIKE @ClubCardNo
            AND TakeAwayPlatTypeId =8
  END  
    IF(@saleType =13)
  BEGIN 
  -- 查询服务员点餐
    SELECT  *
     FROM    #ReportBills
    WHERE   IsCheckedOut = 1
            AND SerialNo LIKE @serialNo + '%'
            AND ClubCardNo LIKE @ClubCardNo
            AND TakeAwayPlatTypeId =13
  END  
  
    DROP TABLE #ReportBills

GO

ALTER    PROCEDURE [dbo].[Meal_GetBills]
    @beginBusinessDate DATETIME ,
    @endBusinessDate DATETIME ,
    @businessHourNo VARCHAR(2) = '%' ,
    @IsCheckout BIT = 1 ,
    @tableNo VARCHAR(5) = '%' ,
    @operatorNo VARCHAR(5) = '%'
AS
    SELECT  Id ,
            DiningAreaId ,
            DiningAreaName ,
            DiningTableId ,
            DiningTableNo ,
            DiningTableName ,
            BillNo ,
            BusinessDate ,
            BusinessHourId ,
            BusinessHourName ,
            ArrangeDatetime ,
            NumberOfPersons ,
            CompanyId ,
            CustomerId ,
            ClubCardTypeId ,
            ISNULL(ClubCardNo, '') ,
            SalesManageId ,
            VolumeOfConsumption ,
            GratisValue ,
            ConsumeValue AS Charge4Room ,
            ( CASE WHEN TakeAwayPlatTypeId = 1
                   THEN FoodClubDis + ConsumeClubDis
                   WHEN TakeAwayPlatTypeId = 2
                   THEN FoodClubDis + ConsumeClubDis
                   WHEN TakeAwayPlatTypeId = 4
                   THEN FoodClubDis + ConsumeClubDis
                   WHEN TakeAwayPlatTypeId = 5
                   THEN VolumeOfConsumption - PayAmount
                   WHEN TakeAwayPlatTypeId = 6
                   THEN VolumeOfConsumption - PayAmount
                   WHEN TakeAwayPlatTypeId = 7
                   THEN VolumeOfConsumption - PayAmount
                   WHEN TakeAwayPlatTypeId = 8
                   THEN VolumeOfConsumption - PayAmount
                   WHEN TakeAwayPlatTypeId = 9
                   THEN FoodClubDis + ConsumeClubDis
                   WHEN TakeAwayPlatTypeId = 10
                   THEN FoodClubDis + ConsumeClubDis
                   WHEN TakeAwayPlatTypeId = 11
                   THEN FoodClubDis + ConsumeClubDis
                   WHEN TakeAwayPlatTypeId = 12
                   THEN FoodClubDis + ConsumeClubDis
                   WHEN TakeAwayPlatTypeId = 13
                   THEN VolumeOfConsumption - PayAmount
                   ELSE FoodClubDis + ConsumeClubDis
              END ) ChargeLimit ,--会员卡优惠
            ServiceCharge ,
            OvertimePremium ,
            DiscountCategory ,
            ( CASE WHEN TakeAwayPlatTypeId = 1
                   THEN DiscountValue + ConsumeDiscountValue
                   WHEN TakeAwayPlatTypeId = 2
                   THEN DiscountValue + ConsumeDiscountValue
                   WHEN TakeAwayPlatTypeId = 4
                   THEN DiscountValue + ConsumeDiscountValue
                   WHEN TakeAwayPlatTypeId = 5 THEN 0
                   WHEN TakeAwayPlatTypeId = 6 THEN 0
                   WHEN TakeAwayPlatTypeId = 7 THEN 0
                   WHEN TakeAwayPlatTypeId = 8 THEN 0
                   WHEN TakeAwayPlatTypeId = 9
                   THEN FoodClubDis + ConsumeClubDis
                   WHEN TakeAwayPlatTypeId = 10
                   THEN DiscountValue + ConsumeDiscountValue
                   WHEN TakeAwayPlatTypeId = 11
                   THEN DiscountValue + ConsumeDiscountValue
                   WHEN TakeAwayPlatTypeId = 12
                   THEN DiscountValue + ConsumeDiscountValue
                   WHEN TakeAwayPlatTypeId = 13 THEN 0
                   ELSE FoodClubDis + ConsumeClubDis
              END ) AS DiscountValue ,
            PricePromotionDis ,
            DiscountApproverId ,
            DerateValue ,
            DerateApproverId ,
            DerationId ,
            Oddment ,
            PayAmount ,
            IsCheckedOut ,
            IsInvoiced ,
            InvoiceNo ,
            ScoreExchangeFoodAmount + ScoreCompensateAmount AS InvoiceAmount ,
            IsCloseOut ,
            PayDatetime ,
            PrintedTimes ,
            CashierId AS OperatorId ,
            CashierName AS OperatorName ,
            SaleType ,
            SaleTypeName ,
            SerialNo ,
            TakeAwayPlatTypeId
    FROM    dbo.Meal_V_Bills4GetBills AS a
    WHERE   ( BusinessDate BETWEEN @beginBusinessDate
                           AND     @endBusinessDate )
            AND BusinessHourNo LIKE @businessHourNo
            AND CashierNo LIKE @operatorNo
    ORDER BY PayDatetime DESC; 
    RETURN 0;
GO

ALTER    VIEW [dbo].[Meal_V_Bills4GetBills]  --全部有效的结账单
AS
    SELECT  b.Id ,
            h.Id AS DiningAreaId ,
            h.No AS DiningAreaNo ,
            h.DiningAreaName --区域
            ,
            a.DiningTableId ,
            t.No AS DiningTableNo ,
            t.DiningTableName  --台位
            ,
            b.No AS BillNo                --账单号
            ,
            b.BusinessDate ,
            b.BusinessHourId ,
            hr.No AS BusinessHourNo ,
            hr.BusinessHourName    --时段
            ,
            a.ArrangeDatetime ,
            ISNULL(a.NumberOfAdults, 0) + ISNULL(NumberOfChildren, 0) AS NumberOfPersons --就餐时间及人数
            ,
            b.CompanyId --,ISNULL(c.CompanyNo,'') AS CompanyNo,ISNULL(CompanyName ,'') AS CompanyName  --单位
            ,
            b.CustomerId --,ISNULL(c.No,'') AS CustomerNo,ISNULL(CustomerName,'') AS CustomerName --客人
            ,
            ClubCardTypeId ,
            ClubCardNo ,
            b.SalesManageId --,ISNULL(em.No,'') AS SalesManageNo,ISNULL(em.EmployeeName,'') AS SalesManageName
            ,
            VolumeOfConsumption    --菜金
            ,
            GratisValue            --赠送
            ,
            b.Charge4Room          --包房费
            ,
            b.ChargeLimit          --最低消费收费(不足最低消费的部分)
            ,
            ServiceCharge ,
            OvertimePremium ,
            DiscountCategory ,
            FoodClubDis ,
            DiscountValue         --折扣
            ,
            DiscountApproverId ,
            DerateValue          --减免
            ,
            DerateApproverId ,
            DerationId ,
            Oddment              --抹零
            ,
            PayAmount            --结账金额
            ,
            IsCheckedOut         --是否结账
            ,
            IsInvoiced ,
            InvoiceNo ,
            InvoiceAmount ,
            IsCloseOut           --
            ,
            PayDatetime ,
            PrintedTimes ,
            ConsumeValue ,
            ConsumeClubDis ,
            ConsumeDiscountValue ,
            a.AssociatedOrderId,
            b.OperatorId AS CashierId ,
            e.No AS CashierNo ,
            e.EmployeeName AS CashierName ,
            IsOwnDrinks ,
            b.ScoreExchangeFoodAmount ,
            b.ScoreCompensateAmount ,
            b.PricePromotionDis ,
            b.DiscountPromotionId ,
            b.SaleType ,
            ( CASE WHEN b.TakeAwayPlatTypeId = 1 THEN '堂吃'
                   WHEN b.TakeAwayPlatTypeId = 2 THEN '外卖'
                   WHEN b.TakeAwayPlatTypeId = 4 THEN '外带'
                   WHEN b.TakeAwayPlatTypeId=5 THEN '扫码点餐'
                   WHEN b.TakeAwayPlatTypeId=6 THEN '秒吃'
                   WHEN b.TakeAwayPlatTypeId=7 THEN '自取'
                   WHEN b.TakeAwayPlatTypeId=8 THEN '外送'
                   WHEN b.TakeAwayPlatTypeId=9 THEN '外卖'
                   WHEN b.TakeAwayPlatTypeId=10 THEN '外卖'
                   WHEN b.TakeAwayPlatTypeId=11 THEN '外卖'
                    WHEN b.TakeAwayPlatTypeId=12 THEN '外卖'
                    WHEN b.TakeAwayPlatTypeId=13 THEN '服务员点餐'
                   ELSE ''
              END ) SaleTypeName ,
            ISNULL(s.SerialNo, '') AS SerialNo,
            b.TakeAwayPlatTypeId
    FROM    dbo.Meal_Bills AS b 
            LEFT JOIN Employees AS e ON e.Id = b.OperatorId ,
            dbo.Meal_ArrangedTables AS a
            LEFT JOIN dbo.Meal_SerialNo AS s ON s.ArrangedTableId = a.Id ,
            DiningTables AS t ,
            DiningAreas AS h ,
            BusinessHours AS hr
    WHERE   b.Id = a.BillId
            AND t.Id = a.DiningTableId
            AND h.Id = t.DiningAreaId
            AND hr.Id = b.BusinessHourId
            AND a.Id = a.PrimaryArrangedTableId  --并台时只取主台位
    UNION ALL
    SELECT  b.Id ,
            h.Id AS DiningAreaId ,
            h.No AS DiningAreaNo ,
            h.DiningAreaName --区域
            ,
            a.DiningTableId ,
            t.No AS DiningTableNo ,
            t.DiningTableName  --台位
            ,
            b.No AS BillNo                --账单号
            ,
            b.BusinessDate ,
            b.BusinessHourId ,
            hr.No AS BusinessHourNo ,
            hr.BusinessHourName    --时段
            ,
            a.ArrangeDatetime ,
            ISNULL(a.NumberOfAdults, 0) + ISNULL(NumberOfChildren, 0) AS NumberOfPersons --就餐时间及人数
            ,
            b.CompanyId --,ISNULL(c.CompanyNo,'') AS CompanyNo,ISNULL(CompanyName ,'') AS CompanyName  --单位
            ,
            b.CustomerId --,ISNULL(c.No,'') AS CustomerNo,ISNULL(CustomerName,'') AS CustomerName --客人
            ,
            ClubCardTypeId ,
            ClubCardNo ,
            b.SalesManageId --,ISNULL(em.No,'') AS SalesManageNo,ISNULL(em.EmployeeName,'') AS SalesManageName
            ,
            VolumeOfConsumption    --菜金
            ,
            GratisValue            --赠送
            ,
            b.Charge4Room          --包房费
            ,
            b.ChargeLimit          --最低消费收费(不足最低消费的部分)
            ,
            ServiceCharge ,
            OvertimePremium ,
            DiscountCategory ,
            FoodClubDis ,
            DiscountValue         --折扣
            ,
            DiscountApproverId ,
            DerateValue          --减免
            ,
            DerateApproverId ,
            DerationId ,
            Oddment              --抹零
            ,
            PayAmount            --结账金额
            ,
            IsCheckedOut         --是否结账
            ,
            IsInvoiced ,
            InvoiceNo ,
            InvoiceAmount ,
            IsCloseOut           --
            ,
            PayDatetime ,
            PrintedTimes ,
            ConsumeValue ,
            ConsumeClubDis ,
            ConsumeDiscountValue ,
            a.AssociatedOrderId,
            b.OperatorId AS CashierId ,
            e.No AS CashierNo ,
            e.EmployeeName AS CashierName ,
            IsOwnDrinks ,
            b.ScoreExchangeFoodAmount ,
            b.ScoreCompensateAmount ,
            b.PricePromotionDis ,
            b.DiscountPromotionId ,
            b.SaleType ,
            ( CASE WHEN b.TakeAwayPlatTypeId = 1 THEN '堂吃'
                   WHEN b.TakeAwayPlatTypeId = 2 THEN '外卖'
                   WHEN b.TakeAwayPlatTypeId = 4 THEN '外带'
                   WHEN b.TakeAwayPlatTypeId=5 THEN '扫码点餐'
                   WHEN b.TakeAwayPlatTypeId=6 THEN '秒吃'
                   WHEN b.TakeAwayPlatTypeId=7 THEN '自取'
                   WHEN b.TakeAwayPlatTypeId=8 THEN '外送'
                   WHEN b.TakeAwayPlatTypeId=9 THEN '外卖'
                   WHEN b.TakeAwayPlatTypeId=10 THEN '外卖'
                   WHEN b.TakeAwayPlatTypeId=11 THEN '外卖'
                   WHEN b.TakeAwayPlatTypeId=12 THEN '外卖'
                    WHEN b.TakeAwayPlatTypeId=13 THEN '服务员点餐'
                   ELSE ''
              END ) SaleTypeName ,
            ISNULL(s.SerialNo, '') AS SerialNo,
            b.TakeAwayPlatTypeId
    FROM    dbo.Meal_History_Bills AS b 
            LEFT JOIN Employees AS e ON e.Id = b.OperatorId ,
            dbo.Meal_History_ArrangedTables AS a
            LEFT JOIN dbo.Meal_History_SerialNo AS s ON s.ArrangedTableId = a.Id ,
            DiningTables AS t ,
            DiningAreas AS h ,
            BusinessHours AS hr
    WHERE   b.Id = a.BillId
            AND t.Id = a.DiningTableId
            AND h.Id = t.DiningAreaId
            AND hr.Id = b.BusinessHourId
            AND a.Id = a.PrimaryArrangedTableId
  --并台时只取主台位
GO


ALTER    PROCEDURE [dbo].[Meal_Bill4PayMentQuery]
    @BeginTime DATETIME ,--查询开始时间
    @EndTime DATETIME ,--查询结束时间
    @WorkNo VARCHAR(10) ,--班次编号
    @PayMothedNo VARCHAR(10) ,--付款方式编号
    @EmployeeNo VARCHAR(50) --操作员编号
AS
   BEGIN
   --整理付款方式当前和历史数据
    IF OBJECT_ID('Tempdb..#MealPaymentListdtable') IS NOT NULL
            DROP TABLE #MealPaymentListdtable; --如果有存在就删除临时表
        CREATE TABLE #MealPaymentListdtable
            (
              Id UNIQUEIDENTIFIER ,
              BillId UNIQUEIDENTIFIER ,
              PaymentMethodId [int],
              PaymentMethodNo [varchar] (10) ,
              PaymentMethodName [nvarchar] (20),
              PayAmount [decimal] (9, 2)
            );  
            
          INSERT  INTO #MealPaymentListdtable--当前表插入
          SELECT p.Id,p.BillId,p.PaymentMethodId,pm.No,pm.PaymentMethodName,p.PayAmount
          FROM dbo.Meal_PaymentList AS p LEFT JOIN dbo.PaymentMethods AS pm ON p.PaymentMethodId=pm.Id
            
          INSERT  INTO #MealPaymentListdtable--历史表插入
          SELECT p.Id,p.BillId,p.PaymentMethodId,pm.No,pm.PaymentMethodName,p.PayAmount
          FROM dbo.Meal_History_PaymentList AS p LEFT JOIN dbo.PaymentMethods AS pm ON p.PaymentMethodId=pm.Id
           
   
        IF OBJECT_ID('Tempdb..#BillPayQuerymidtable') IS NOT NULL
            DROP TABLE #BillPayQuerymidtable; --如果有存在就删除临时表
        CREATE TABLE #BillPayQuerymidtable
            (
              Id UNIQUEIDENTIFIER ,
              BillNo VARCHAR(20) ,
              PayDatetime DATETIME ,
              PayAmount DECIMAL(18, 2) ,
              OperatorId UNIQUEIDENTIFIER ,
              OperatorNo VARCHAR(255) ,
              OperatorName VARCHAR(255) ,
              BusinessDate DATETIME ,
              BusinessHourId INT ,
              BusinessHourNo VARCHAR(5) ,
              BusinessHourName VARCHAR(255) ,
              Common VARCHAR(255),
              SaleTypeName VARCHAR(50)
            );
        --插入当前表记录
        INSERT  INTO #BillPayQuerymidtable
        SELECT b.id ,
               b.no,
               b.PayDatetime,
               b.PayAmount,
               b.OperatorId,
               e.No,
               e.EmployeeName,
               b.BusinessDate,
               b.BusinessHourId,
               bh.no,
               bh.BusinessHourName,
               '' ,
               ( CASE WHEN b.TakeAwayPlatTypeId = 1 THEN '堂吃'
                   WHEN b.TakeAwayPlatTypeId = 2 THEN '外卖'
                   WHEN b.TakeAwayPlatTypeId = 4 THEN '外带'
                   WHEN b.TakeAwayPlatTypeId=5 THEN '扫码点餐'
                   WHEN b.TakeAwayPlatTypeId=6 THEN '秒吃'
                   WHEN b.TakeAwayPlatTypeId=7 THEN '自取'
                   WHEN b.TakeAwayPlatTypeId=8 THEN '外送'
                   WHEN b.TakeAwayPlatTypeId=9 THEN '外卖'
                   WHEN b.TakeAwayPlatTypeId=10 THEN '美团'
                   WHEN b.TakeAwayPlatTypeId=11 THEN '饿了么'
                   WHEN b.TakeAwayPlatTypeId=13 THEN '服务员点餐'
                   ELSE ''
              END ) SaleTypeName 
               FROM dbo.Meal_Bills AS b 
                    LEFT JOIN dbo.Employees AS e ON b.OperatorId=e.Id
                    LEFT JOIN dbo.BusinessHours AS bh ON bh.id=b.BusinessHourId
               WHERE   b.PayDatetime BETWEEN @BeginTime AND @EndTime
                        AND bh.No LIKE @WorkNo
                        AND e.No LIKE @EmployeeNo
                        AND  b.Id IN (SELECT BillId FROM #MealPaymentListdtable WHERE PaymentMethodNo LIKE @PayMothedNo)
                ORDER BY BusinessDate,b.No;
        
        --插入历史表记录
        INSERT  INTO #BillPayQuerymidtable
        SELECT b.id ,
               b.no,
               b.PayDatetime,
               b.PayAmount,
               b.OperatorId,
               e.No,
               e.EmployeeName,
               b.BusinessDate,
               b.BusinessHourId,
               bh.no,
               bh.BusinessHourName,
               '',
                ( CASE WHEN b.TakeAwayPlatTypeId = 1 THEN '堂吃'
                   WHEN b.TakeAwayPlatTypeId = 2 THEN '外卖'
                   WHEN b.TakeAwayPlatTypeId = 4 THEN '外带'
                   WHEN b.TakeAwayPlatTypeId=5 THEN '扫码点餐'
                   WHEN b.TakeAwayPlatTypeId=6 THEN '秒吃'
                   WHEN b.TakeAwayPlatTypeId=7 THEN '自取'
                   WHEN b.TakeAwayPlatTypeId=8 THEN '外送'
                   WHEN b.TakeAwayPlatTypeId=9 THEN '外卖'
                   WHEN b.TakeAwayPlatTypeId=10 THEN '美团'
                   WHEN b.TakeAwayPlatTypeId=11 THEN '饿了么'
                   WHEN b.TakeAwayPlatTypeId=13 THEN '服务员点餐'
                   ELSE ''
              END ) SaleTypeName 
               FROM dbo.Meal_History_Bills AS b 
                    LEFT JOIN dbo.Employees AS e ON b.OperatorId=e.Id
                    LEFT JOIN dbo.BusinessHours AS bh ON bh.id=b.BusinessHourId
               WHERE   b.PayDatetime BETWEEN @BeginTime AND @EndTime
                        AND bh.No LIKE @WorkNo
                        AND e.No LIKE @EmployeeNo
                        AND  b.Id IN (SELECT BillId FROM #MealPaymentListdtable WHERE PaymentMethodNo LIKE @PayMothedNo)
                ORDER BY BusinessDate,b.No;
            
          
        DECLARE @BillId UNIQUEIDENTIFIER;
        DECLARE @Common VARCHAR(255);
        SET @Common = '';
        DECLARE @PaymentMethodName VARCHAR(255);
        DECLARE @PaymentMoney DECIMAL(9, 2);
            
        DECLARE cardCur CURSOR
        FOR
            SELECT  Id ,
                    Common
            FROM    #BillPayQuerymidtable;
                  
        OPEN cardCur; 
        FETCH NEXT FROM cardCur INTO @BillId, @Common; 
        WHILE @@fetch_status = 0
            BEGIN 
                DECLARE DetailsCur CURSOR
                FOR
                    SELECT  PaymentMethodName ,
                            PayAmount
                    FROM    #MealPaymentListdtable
                    WHERE   BillId = @BillId;
                    
                OPEN DetailsCur;
                FETCH NEXT FROM DetailsCur INTO @PaymentMethodName,
                    @PaymentMoney;
                WHILE @@fetch_status = 0
                    BEGIN
                        SET @Common = @Common + @PaymentMethodName 
                            + CONVERT(VARCHAR(255), @PaymentMoney)+' ';
                        FETCH NEXT FROM DetailsCur INTO @PaymentMethodName,
                            @PaymentMoney;
                    END;
                UPDATE  #BillPayQuerymidtable
                SET     Common = @Common
                WHERE   Id = @BillId;
                CLOSE DetailsCur; 
                DEALLOCATE DetailsCur;  
                FETCH NEXT FROM cardCur INTO @BillId, @Common;
            END;
        CLOSE cardCur; 
        DEALLOCATE cardCur;      
        SELECT  Id ,
                BillNo ,
                PayDatetime ,
                PayAmount ,
                OperatorId ,
                OperatorNo ,
                OperatorName ,
                BusinessDate ,
                BusinessHourId ,
                BusinessHourNo ,
                BusinessHourName ,
                Common,
                SaleTypeName
        FROM    #BillPayQuerymidtable;
                
        DROP TABLE  #BillPayQuerymidtable;
    END;
    RETURN;
GO

       
                
ALTER   FUNCTION [dbo].[Meal_ufnReportCenter_SundryCollectionsNew]
    (
      @BeginDate DATETIME ,
      @EndDate DATETIME ,
      @DiningAreaN0 VARCHAR(3) , --厅
      @BusinessHourNo VARCHAR(2) , --营业时段
      @CashierNo VARCHAR(5)  --收银员
    )
RETURNS @returntable TABLE
    (
      SundryNo VARCHAR(5) ,
      SundryName NVARCHAR(50) ,
      Volumes DECIMAL(18, 2)
    )
AS 
    BEGIN
		DECLARE @PlatOrderFoodMoney DECIMAL (9,2)--记录平台的菜例价格合计,用于消费合计统计
		DECLARE @PlatOrderFoodDiscount DECIMAL(9,2)--记录平台折扣金额
		DECLARE @PlatOrderCustomerTimers INT --记录平台客次,就餐人数
        DECLARE @HourId INT
        DECLARE @CashierId UNIQUEIDENTIFIER
        IF ( @BusinessHourNo <> '%' ) 
            BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    dbo.BusinessHours
                                WHERE   No = @BusinessHourNo ) 
                    BEGIN
                        RETURN
                    END
                SET @HourId = ( SELECT TOP 1
                                        Id
                                FROM    dbo.BusinessHours
                                WHERE   No = @BusinessHourNo
                              )
            END
        IF ( @CashierNo <> '%' ) 
            BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    dbo.Employees
                                WHERE   No = @CashierNo ) 
                    BEGIN
                        RETURN
                    END
                SET @CashierId = ( SELECT TOP 1
                                            Id
                                   FROM     dbo.Employees
                                   WHERE    No = @CashierNo
                                 )
            END
      --临时表记录查询时间范围内的所有结账单（历史+当前）
      DECLARE  @CheckOutBills TABLE
    (
       [Id] UNIQUEIDENTIFIER NOT NULL 
      ,[BusinessDate]SMALLDATETIME NOT NULL 
      ,[BusinessHourId]INT NOT NULL 
      ,[BusinessHourNo]VARCHAR (2) NOT NULL 
      ,[NumberOfPersons]INT NULL 
      ,[VolumeOfConsumption]numeric(11,2) NOT NULL 
      ,[GratisValue]numeric(9,2)NOT NULL 
      ,[FoodClubDis]numeric(9,2)NOT NULL 
      ,[DiscountValue]DECIMAL(9,2)NOT NULL 
      ,[DiscountApproverId]UNIQUEIDENTIFIER NULL 
      ,[DerateValue]DECIMAL(9,2)NOT NULL 
      ,[DerateApproverId]UNIQUEIDENTIFIER NULL 
      ,[Oddment]DECIMAL(9,2)NOT NULL 
      ,[PayAmount]DECIMAL(12,2)NOT NULL 
      ,[IsCheckedOut]BIT NOT NULL 
      ,[ConsumeValue]numeric(9,2) NULL 
      ,[ConsumeClubDis]numeric(9,2) NULL 
      ,[ConsumeDiscountValue]numeric(9,2) NULL 
      ,[CashierId]UNIQUEIDENTIFIER NOT NULL 
      ,[EmployeeNo]VARCHAR(5) NULL 
      ,[ScoreExchangeFoodAmount]numeric(9,2)NOT NULL 
      ,[ScoreCompensateAmount]numeric(9,2)NOT NULL 
      ,[SaleType]INT NULL 
      ,TakeAwayPlatTypeId int NULL
      ,SuitDisAmount decimal(9,2) NULL
      ,ProductionAmount decimal(9,2)NULL
    )
      INSERT @CheckOutBills
      SELECT  [Id] 
      ,[BusinessDate]
      ,[BusinessHourId]
      ,[BusinessHourNo]
      ,[NumberOfPersons]
      ,[VolumeOfConsumption]
      ,[GratisValue]
      ,[FoodClubDis]
      ,[DiscountValue]
      ,[DiscountApproverId]
      ,[DerateValue]
      ,[DerateApproverId]
      ,[Oddment]
      ,[PayAmount] 
      ,[IsCheckedOut]
      ,[ConsumeValue]
      ,[ConsumeClubDis]
      ,[ConsumeDiscountValue]
      ,[CashierId]
      ,[EmployeeNo]
      ,[ScoreExchangeFoodAmount]
      ,[ScoreCompensateAmount]
      ,[SaleType]
      ,TakeAwayPlatTypeId
      ,SuitDisAmount 
      ,ProductionAmount
      FROM Meal_ufnGetAllCheckOutBillsNew( @BeginDate , @EndDate) 
       
        DECLARE @MidTable TABLE
            (
              VolumeOfConsumption DECIMAL(18, 2) ,
              ConsumeValue DECIMAL(18, 2) ,
              FoodClubDis DECIMAL(18, 2) ,
              DiscountValue DECIMAL(18, 2) ,
              ConsumeDiscountValue DECIMAL(18, 2) ,
              FoodDerateValue DECIMAL(18, 2) ,
              DerateValue DECIMAL(18, 2) ,
              Oddment DECIMAL(18, 2) ,
              GratisValue DECIMAL(18, 2) ,
              ScoreExchangeFoodAmount DECIMAL(18, 2) ,
              ScoreCompensateAmount DECIMAL(18, 2) ,
              PayAmount DECIMAL(18, 2) ,
              CustomerTimes DECIMAL(18, 2) ,
              OneTablePay DECIMAL(18, 2) ,
              NumberOfPersons DECIMAL(18, 2)
            )
            
        IF ( @CashierId IS NOT NULL
             AND @HourId IS NOT NULL
           ) 
            BEGIN
                INSERT  @MidTable
                        SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                SUM(ConsumeValue) ,
                                -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                -1
                                * SUM(CASE WHEN DiscountApproverId IS NULL
                                           THEN 0
                                           ELSE DiscountValue
                                                + ConsumeDiscountValue
                                      END) ,
                                -1
                                * SUM(CASE WHEN DiscountApproverId IS NULL
                                           THEN DiscountValue
                                                + ConsumeDiscountValue
                                           ELSE 0
                                      END) ,
                                -1
                                * SUM(CASE WHEN DerateApproverId IS NULL
                                           THEN 0
                                           ELSE DerateValue
                                      END) ,
                                -1 * SUM(DerateValue) ,
                                -1 * SUM(Oddment) ,
                                -1 * SUM(GratisValue) ,
                                -1 * SUM(ScoreExchangeFoodAmount) ,
                                -1 * SUM(ScoreCompensateAmount) ,
                                SUM(PayAmount) ,
                                COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                ( SUM(PayAmount)
                                  / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                        WHEN 0 THEN 1
                                        ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                      END ) ) ,
                                SUM(ISNULL(a.NumberOfPersons, 0))
                        
                          FROM @CheckOutBills AS a 
                                 WHERE a.BusinessHourId = @HourId
                                 AND a.CashierId  = @CashierId 
                                 AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客,13 服务员点餐
				 
				 
				 	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
            END
        ELSE 
            IF ( @CashierId IS NOT NULL
                 AND @HourId IS NULL
               ) 
                BEGIN
                    INSERT  @MidTable
                            SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                    SUM(ConsumeValue) ,
                                    -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                    -1
                                    * SUM(CASE WHEN DiscountApproverId IS NULL
                                               THEN 0
                                               ELSE DiscountValue
                                                    + ConsumeDiscountValue
                                          END) ,
                                    -1
                                    * SUM(CASE WHEN DiscountApproverId IS NULL
                                               THEN DiscountValue
                                                    + ConsumeDiscountValue
                                               ELSE 0
                                          END) ,
                                    -1
                                    * SUM(CASE WHEN DerateApproverId IS NULL
                                               THEN 0
                                               ELSE DerateValue
                                          END) ,
                                    -1 * SUM(DerateValue) ,
                                    -1 * SUM(Oddment) ,
                                    -1 * SUM(GratisValue) ,
                                    -1 * SUM(ScoreExchangeFoodAmount) ,
                                    -1 * SUM(ScoreCompensateAmount) ,
                                    SUM(PayAmount) ,
                                    COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                    ( SUM(PayAmount)
                                      / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                            WHEN 0 THEN 1
                                            ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                          END ) ) ,
                                    SUM(ISNULL(a.NumberOfPersons, 0))
                         -- FROM Meal_V_Bills4GetBills AS a
                          FROM @CheckOutBills AS a 
                                 WHERE   a.CashierId  = @CashierId 
                                 AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                      
                            
                   	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                                        
                END
            ELSE 
                IF ( @CashierId IS NULL
                     AND @HourId IS NOT NULL
                   ) 
                    BEGIN
                        INSERT  @MidTable
                                SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                        SUM(ConsumeValue) ,
                                        -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN 0
                                                   ELSE DiscountValue
                                                        + ConsumeDiscountValue
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN DiscountValue
                                                        + ConsumeDiscountValue
                                                   ELSE 0
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DerateApproverId IS NULL
                                                   THEN 0
                                                   ELSE DerateValue
                                              END) ,
                                        -1 * SUM(DerateValue) ,
                                        -1 * SUM(Oddment) ,
                                        -1 * SUM(GratisValue) ,
                                        -1 * SUM(ScoreExchangeFoodAmount) ,
                                        -1 * SUM(ScoreCompensateAmount) ,
                                        SUM(PayAmount) ,
                                        COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                        ( SUM(PayAmount)
                                          / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                                WHEN 0 THEN 1
                                                ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                              END ) ) ,
                                        SUM(ISNULL(a.NumberOfPersons, 0))
                           FROM @CheckOutBills AS a 
                                 WHERE a.BusinessHourId = @HourId
                                  AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                                        
                  	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                                        
                    END
                ELSE 
                    BEGIN
                        INSERT  @MidTable
                                SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                        SUM(ConsumeValue) ,
                                        -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN 0
                                                   ELSE DiscountValue
                                                        + ConsumeDiscountValue
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN DiscountValue
                                                        + ConsumeDiscountValue
                                                   ELSE 0
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DerateApproverId IS NULL
                                                   THEN 0
                                                   ELSE DerateValue
                                              END) ,
                                        -1 * SUM(DerateValue) ,
                                        -1 * SUM(Oddment) ,
                                        -1 * SUM(GratisValue) ,
                                        -1 * SUM(ScoreExchangeFoodAmount) ,
                                        -1 * SUM(ScoreCompensateAmount) ,
                                        SUM(PayAmount) ,
                                        COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                        ( SUM(PayAmount)
                                          / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                                WHEN 0 THEN 1
                                                ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                              END ) ) ,
                                        SUM(ISNULL(a.NumberOfPersons, 0))
                          
                          FROM @CheckOutBills AS a   
                                 WHERE a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                            
                            
                   	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客          
                    END
        
     
        --计算平台外卖到账金额
		 DECLARE @TakeAwayMoney DECIMAL (9,2)
					  SELECT  @TakeAwayMoney= SUM(ISNULL(TakeAwayPlatPayShopMoney,0))
					  FROM      dbo.Meal_PlatTakeAwayBillsDispose
					  WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
								AND BusinessHourNo LIKE @BusinessHourNo
								AND OperatorNo LIKE @CashierNo
								AND IsInMealReversedBillsTable=0
								AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
            IF(@PlatOrderFoodDiscount IS null)
            SET @PlatOrderFoodDiscount=0
	    INSERT  @returntable
                SELECT  SundryNo ,
                        SundryName ,
                        SUM(Volumes) AS Volumes
                FROM    (
                          SELECT    '01' AS SundryNo ,
                                    '*消费总额' AS SundryName ,
                                    (ISNULL(VolumeOfConsumption,0) +ISNULL(@PlatOrderFoodMoney,0) ) AS Volumes--消费+外卖菜例金额
                          FROM      @MidTable AS a
                          WHERE     ISNULL(VolumeOfConsumption,0)<> 0 OR ISNULL(@PlatOrderFoodMoney,0)<>0
                          UNION ALL
                          SELECT    '02' AS SundryNo ,
                                    '*优惠总额' AS SundryName ,
                                    ISNULL(SUM(FoodClubDis)+SUM(DiscountValue)+SUM(ConsumeDiscountValue)+SUM(FoodDerateValue)
                                    -@PlatOrderFoodDiscount+ SUM(Oddment)+SUM(GratisValue)+SUM(ScoreExchangeFoodAmount)
                                    +SUM(ScoreCompensateAmount),0)AS Volumes
                          FROM      @MidTable AS a
                          UNION ALL
                          SELECT    '025' AS SundryNo ,
                                    ' 会员优惠' AS SundryName ,
                                    FoodClubDis AS Volumes
                          FROM      @MidTable AS a
                          WHERE     FoodClubDis <> 0
                          UNION ALL
                          SELECT    '03' AS SundryNo ,
                                    ' 批准折扣' AS SundryName ,
                                    DiscountValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     DiscountValue <> 0
                          UNION ALL
                          SELECT    '042' AS SundryNo ,
                                    ' 会员折扣' AS SundryName ,
                                    ConsumeDiscountValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ConsumeDiscountValue <> 0
                        
                          UNION ALL
                          SELECT    '043' AS SundryNo ,
                                    ' 单品折扣' AS SundryName ,
                                    FoodDerateValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     FoodDerateValue <> 0
                          UNION ALL 
                          SELECT '044' AS SundryNo ,
                                    ' 平台折扣' AS SundryName ,
                                    0 - isnull(@PlatOrderFoodDiscount,0)
                                    WHERE isnull(@PlatOrderFoodDiscount,0)<>0
                          UNION ALL
                          SELECT    '06' AS SundryNo ,
                                    '     抹零' AS SundryName ,
                                    Oddment AS Volumes
                          FROM      @MidTable AS a
                          WHERE     Oddment <> 0
                          UNION ALL
                          SELECT    '07' AS SundryNo ,
                                    '     赠送' AS SundryName ,
                                    GratisValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     GratisValue <> 0
                          UNION ALL
                          SELECT    '071' AS SundryNo ,
                                    ' 积分兑换' AS SundryName ,
                                    ScoreExchangeFoodAmount AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ScoreExchangeFoodAmount <> 0
                          UNION ALL
                          SELECT    '072' AS SundryNo ,
                                    ' 积分抵扣' AS SundryName ,
                                    ScoreCompensateAmount AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ScoreCompensateAmount <> 0
                          UNION ALL
                          SELECT    '08' AS SundryNo ,
                                    '*实收金额' AS SundryName ,
                                    (ISNULL(PayAmount,0)+ISNULL(@TakeAwayMoney,0) )AS Volumes--实收+外卖平台到账
                          FROM      @MidTable AS a
                          WHERE     ISNULL(PayAmount,0) <> 0 OR ISNULL(@TakeAwayMoney,0)<>0
                          UNION ALL
                          SELECT    '09' AS SundryNo ,
                                    '*客次' AS SundryName ,
                                    CustomerTimes+ISNULL(@PlatOrderCustomerTimers,0)
                          FROM      @MidTable AS a
                          WHERE     CustomerTimes <> 0
                          UNION ALL
                          SELECT    '11' AS SundryNo ,
                                    '*客单' AS SundryName ,
                                    OneTablePay 
                          FROM      @MidTable AS a
                          WHERE     OneTablePay <> 0
                          UNION ALL
                          SELECT    '10' AS SundryNo ,
                                    '*就餐人数' AS SundryName ,
                                    NumberOfPersons+ISNULL(@PlatOrderCustomerTimers,0)
                          FROM      @MidTable AS a
                          WHERE     NumberOfPersons <> 0
                          UNION ALL
                          SELECT    '15' AS SundryNo ,
                                    '*退菜金额' AS SundryName ,
                                    SUM(Charge4Food) AS Volumes
                          FROM      dbo.Meal_V_OrderFoods-----------------------------------------------------------------------
                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                    AND DiningAreaNo LIKE @DiningAreaN0
                                    AND BusinessHourNo LIKE @BusinessHourNo
                                    AND CashierNo LIKE @CashierNo
                                    AND IsReturnSale = 1
                          GROUP BY  DiningAreaNo ,
                                    DiningAreaName ,
                                    BusinessDate ,
                                    BusinessHourNo ,
                                    BusinessHourName ,
                                    CashierNo ,
                                    CashierName
                          UNION ALL
                          SELECT    '15' AS SundryNo ,
                                    '*退菜金额' AS SundryName ,
                                    SUM(RealDues) AS Volumes
                          FROM      dbo.Meal_SpeFoodSaleCollections----------------------------------------------------
                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                    AND BusinessHourNo LIKE @BusinessHourNo
                                    AND CashierNo LIKE @CashierNo
                                    AND SpeType = 1
                          GROUP BY  BusinessDate ,
                                    BusinessHourNo ,
                                    BusinessHourName ,
                                    CashierNo ,
                                    CashierName
                        ) a
                GROUP BY SundryNo ,
                        SundryName
                ORDER BY SundryNo
--------------------------------------------------------------------------------------------------------------- 
                
--如果有外带或外卖则分别统计(堂食，外卖，外带 针对消费总额和实收总额的处理)-----------------
        DECLARE @TakeAwayFlag INT
        SET @TakeAwayFlag = 0
        SELECT  @TakeAwayFlag = COUNT(SaleType)
        FROM    @CheckOutBills
        WHERE   BusinessHourNo LIKE @BusinessHourNo
                AND EmployeeNo LIKE @CashierNo
                AND TakeAwayPlatTypeId IN ( 2, 4 ,5,6,7,8,9,10,11,12,13)
       
        IF @TakeAwayFlag > 0 --则首先生成堂食数据
            BEGIN
                INSERT  @returntable
                        SELECT  SundryNo ,
                                SundryName ,
                                SUM(Volumes) AS Volumes
                        FROM    ( SELECT    BusinessDate ,
                                            BusinessHourNo ,
                                            EmployeeNo ,
                                            '011' AS SundryNo ,
                                            '     堂食' AS SundryName ,
                                            SUM(ISNULL(VolumeOfConsumption, 0)) AS Volumes--消费合计
                                  FROM      @CheckOutBills
                                  WHERE     BusinessHourNo LIKE @BusinessHourNo
                                            AND EmployeeNo LIKE @CashierNo
                                            AND TakeAwayPlatTypeId = 1
                                  GROUP BY  BusinessDate ,
                                            BusinessHourNo ,
                                            EmployeeNo
                                  HAVING    SUM(ISNULL(VolumeOfConsumption, 0)) <> 0
                                  UNION ALL
                                  SELECT    BusinessDate ,
                                            BusinessHourNo ,
                                            EmployeeNo ,
                                            '081' AS SundryNo ,
                                            '     堂食' AS SundryName ,
                                            SUM(PayAmount) AS Volumes--堂食实付
                                  FROM      @CheckOutBills
                                  WHERE     BusinessHourNo LIKE @BusinessHourNo
                                            AND EmployeeNo LIKE @CashierNo
                                            AND TakeAwayPlatTypeId = 1
                                  GROUP BY  BusinessDate ,
                                            BusinessHourNo ,
                                            EmployeeNo
                                  HAVING    SUM(PayAmount) <> 0
                                ) AS a
                        GROUP BY SundryNo ,
                                SundryName
                        ORDER BY SundryNo
                --  处理外卖
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND EmployeeNo LIKE @CashierNo
                        AND TakeAwayPlatTypeId IN  (2,8,9)
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo ,
                                                    '014' AS SundryNo ,
                                                    '     外卖' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo
                                                    AND TakeAwayPlatTypeId IN( 2,9)
                                          GROUP BY  
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo 
                                          HAVING    SUM(ISNULL(VolumeOfConsumption, 0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo ,
                                                    '084' AS SundryNo ,
                                                    '     外卖' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo
                                                    AND TakeAwayPlatTypeId IN( 2,9)
                                          GROUP BY 
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                        
                    END  
                    --处理外带
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND EmployeeNo LIKE @CashierNo
                        AND TakeAwayPlatTypeId IN ( 4,7)
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo ,
                                                    '015' AS SundryNo ,
                                                    '     外带' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo
                                                    AND TakeAwayPlatTypeId IN ( 4)--店内外带，和手机订外带
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo 
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,
                                                    BusinessHourNo ,                                                   
                                                    EmployeeNo ,                                                   
                                                    '085' AS SundryNo ,
                                                    '     外带' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM     @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo                                                   
                                                    AND TakeAwayPlatTypeId IN ( 4)
                                          GROUP BY 
                                                    BusinessDate ,
                                                    BusinessHourNo ,                                                  
                                                    EmployeeNo
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    --扫码点餐处理
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND EmployeeNo LIKE @CashierNo                      
                        AND TakeAwayPlatTypeId BETWEEN 5 AND 8 
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,                                                   
                                                    BusinessHourNo ,                                                 
                                                    EmployeeNo ,                                                 
                                                    '012' AS SundryNo ,
                                                    '   好易点' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM     @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo                                                   
                                                    AND (TakeAwayPlatTypeId BETWEEN 5 AND 8 OR TakeAwayPlatTypeId=13)
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,
                                                     EmployeeNo
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,                                                   
                                                    BusinessHourNo ,                                                   
                                                    EmployeeNo ,                                                   
                                                    '082' AS SundryNo ,
                                                    '   好易点' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo
                                                    AND  (TakeAwayPlatTypeId BETWEEN 5 AND 8 OR TakeAwayPlatTypeId=13)
                                          GROUP BY 
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    
                
                    
                    --自助点餐
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(TakeAwayPlatPayShopMoney)
                FROM    dbo.Meal_PlatTakeAwayBillsDispose
                WHERE   BusinessDate BETWEEN @BeginDate AND @EndDate
                        AND BusinessHourNo LIKE @BusinessHourNo
                        AND OperatorNo LIKE @CashierNo
                        AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
                        
                DECLARE  @platOrderFoodMoney4All DECIMAL(9,2) 
                SET @platOrderFoodMoney4All=0
                --统计平台的菜例价格合计,用于消费合计统计
					SELECT @platOrderFoodMoney4All=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                               
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( 
                                         SELECT     '016' AS SundryNo ,
                                                    '     平台' AS SundryName ,
                                                    ISNULL(@platOrderFoodMoney4All,0) AS Volumes --平台菜例消费总额
                                          
                                          UNION ALL
                                          SELECT   
                                                    '086' AS SundryNo ,
                                                    '     平台' AS SundryName ,
                                                    SUM(ISNULL(TakeAwayPlatPayShopMoney,
                                                              0)) AS Volumes
                                          FROM      dbo.Meal_PlatTakeAwayBillsDispose
                                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                                    AND BusinessHourNo LIKE @BusinessHourNo
                                                    AND OperatorNo LIKE @CashierNo
                                                    AND IsInMealReversedBillsTable=0  AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,
                                                    OperatorNo
                                          HAVING    SUM(ISNULL(TakeAwayPlatPayShopMoney,
                                                              0)) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    
                    
                    
            END          
        DECLARE @realdues DECIMAL(18, 2)
        DECLARE @saletables DECIMAL(18, 2)
        DECLARE @salenum DECIMAL(18, 2)
        SET @realdues = ( SELECT    Volumes
                          FROM      @returntable
                          WHERE     SundryName = '*实收金额'
                        )
        SET @saletables = ( SELECT  Volumes
                            FROM    @returntable
                            WHERE   SundryName = '*客次'
                          )
        SET @salenum = ( SELECT Volumes
                         FROM   @returntable
                         WHERE  SundryName = '*就餐人数'
                       )
        
        IF ( @saletables IS NULL
             OR @saletables = 0
           ) 
            BEGIN
                SET @saletables = 1
            END
        IF ( @salenum IS NULL
             OR @salenum = 0
           ) 
            BEGIN
                SET @salenum = 1
            END 
        ------将就餐人数项改为人均消费
        INSERT  INTO @returntable
        VALUES  ( '15', '*人均消费', 0 )  
        IF NOT ( @realdues IS NULL ) 
            BEGIN
                UPDATE  @returntable
                SET     Volumes = @realdues / @saletables
                WHERE   SundryName = '*客单'
                UPDATE  @returntable
                SET     Volumes = @realdues / @salenum
                WHERE   SundryName = '*人均消费'
            END
        
        RETURN
    END
GO
ALTER    FUNCTION [dbo].[Meal_ufnReportCenter_SundryCollections_Current]
    (
      @BeginDate DATETIME ,
      @EndDate DATETIME ,
      @DiningAreaN0 VARCHAR(3) , --厅
      @BusinessHourNo VARCHAR(2) , --营业时段
      @CashierNo VARCHAR(5)  --收银员
    )
RETURNS @returntable TABLE
    (
      SundryNo VARCHAR(5) ,
      SundryName NVARCHAR(50) ,
      Volumes DECIMAL(18, 2)
    )
AS 
    BEGIN
		DECLARE @PlatOrderFoodMoney DECIMAL (9,2)--记录平台的菜例价格合计,用于消费合计统计
		DECLARE @PlatOrderFoodDiscount DECIMAL(9,2)--记录平台折扣金额
		DECLARE @PlatOrderCustomerTimers INT --记录平台客次,就餐人数
        DECLARE @HourId INT
        DECLARE @CashierId UNIQUEIDENTIFIER
        IF ( @BusinessHourNo <> '%' ) 
            BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    dbo.BusinessHours
                                WHERE   No = @BusinessHourNo ) 
                    BEGIN
                        RETURN
                    END
                SET @HourId = ( SELECT TOP 1
                                        Id
                                FROM    dbo.BusinessHours
                                WHERE   No = @BusinessHourNo
                              )
            END
        IF ( @CashierNo <> '%' ) 
            BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    dbo.Employees
                                WHERE   No = @CashierNo ) 
                    BEGIN
                        RETURN
                    END
                SET @CashierId = ( SELECT TOP 1
                                            Id
                                   FROM     dbo.Employees
                                   WHERE    No = @CashierNo
                                 )
            END
      --临时表记录查询时间范围内的所有结账单（历史+当前）
      DECLARE  @CheckOutBills TABLE
    (
       [Id] UNIQUEIDENTIFIER NOT NULL 
      ,[BillNo] VARCHAR (13) NOT NULL 
      ,[BusinessDate]SMALLDATETIME NOT NULL 
      ,[BusinessHourId]INT NOT NULL 
      ,[BusinessHourNo]VARCHAR (2) NOT NULL 
      ,[BusinessHourName]NVARCHAR (20) NOT NULL 
     -- ,[ArrangeDatetime]DATETIME NOT NULL 
      ,[NumberOfPersons]INT NULL 
      ,[SalesManageId]UNIQUEIDENTIFIER NULL 
      ,[VolumeOfConsumption]numeric(11,2) NOT NULL 
      ,[GratisValue]numeric(9,2)NOT NULL 
      ,[Charge4Room]numeric(9,2)NOT NULL 
      ,[ChargeLimit]numeric(9,2)NOT NULL 
      ,[ServiceCharge]DECIMAL(9,2)NOT NULL 
      ,[OvertimePremium]DECIMAL(9,2)NOT NULL 
      ,[DiscountCategory]TINYINT NOT NULL 
      ,[FoodClubDis]numeric(9,2)NOT NULL 
      ,[DiscountValue]DECIMAL(9,2)NOT NULL 
      ,[DiscountApproverId]UNIQUEIDENTIFIER NULL 
      ,[DerateValue]DECIMAL(9,2)NOT NULL 
      ,[DerateApproverId]UNIQUEIDENTIFIER NULL 
      ,[DerationId]INT NULL 
      ,[Oddment]DECIMAL(9,2)NOT NULL 
      ,[PayAmount]DECIMAL(12,2)NOT NULL 
      ,[IsCheckedOut]BIT NOT NULL 
      ,[IsInvoiced]BIT NOT NULL 
      ,[InvoiceNo]VARCHAR(20)NULL 
      ,[InvoiceAmount]DECIMAL(12,2) NULL 
      ,[IsCloseOut]BIT NOT NULL 
      ,[PayDatetime]DATETIME NULL 
      ,[PrintedTimes]TINYINT NOT NULL 
      ,[ConsumeValue]numeric(9,2) NULL 
      ,[ConsumeClubDis]numeric(9,2) NULL 
      ,[ConsumeDiscountValue]numeric(9,2) NULL 
      --,[AssociatedOrderId]UNIQUEIDENTIFIER NULL 
      ,[CashierId]UNIQUEIDENTIFIER NOT NULL 
      ,[CashierNo]VARCHAR(5) NULL 
   --   ,[CashierName]NVARCHAR(50)NULL 
      ,[IsOwnDrinks]BIT NOT NULL 
      ,[ScoreExchangeFoodAmount]numeric(9,2)NOT NULL 
      ,[ScoreCompensateAmount]numeric(9,2)NOT NULL 
      ,[PricePromotionDis]numeric(9,2) NULL 
      ,[DiscountPromotionId]UNIQUEIDENTIFIER NULL 
      ,[SaleType]INT NULL 
      ,[SerialNo]VARCHAR (30) NOT NULL 
      ,TakeAwayPlatTypeId int NULL
      ,SuitDisAmount decimal(9,2) NULL
      ,ProductionAmount decimal(9,2)NULL
    )
      INSERT @CheckOutBills
      SELECT  [Id] 
      ,[BillNo]
      ,[BusinessDate]
      ,[BusinessHourId]
      ,[BusinessHourNo]
      ,[BusinessHourName]
     -- ,[ArrangeDatetime]
      ,[NumberOfPersons]
      ,[SalesManageId]
      ,[VolumeOfConsumption]
      ,[GratisValue]
      ,[Charge4Room]
      ,[ChargeLimit]
      ,[ServiceCharge]
      ,[OvertimePremium] 
      ,[DiscountCategory] 
      ,[FoodClubDis]
      ,[DiscountValue]
      ,[DiscountApproverId]
      ,[DerateValue]
      ,[DerateApproverId]
      ,[DerationId] 
      ,[Oddment]
      ,[PayAmount] 
      ,[IsCheckedOut]
      ,[IsInvoiced]
      ,[InvoiceNo]
      ,[InvoiceAmount]
      ,[IsCloseOut]
      ,[PayDatetime]
      ,[PrintedTimes]
      ,[ConsumeValue]
      ,[ConsumeClubDis]
      ,[ConsumeDiscountValue]
   --   ,[AssociatedOrderId]
      ,[CashierId]
      ,[CashierNo]
     -- ,[CashierName]
      ,[IsOwnDrinks]
      ,[ScoreExchangeFoodAmount]
      ,[ScoreCompensateAmount]
      ,[PricePromotionDis]
      ,[DiscountPromotionId]
      ,[SaleType]
      ,[SerialNo]
      ,TakeAwayPlatTypeId
      ,SuitDisAmount 
      ,ProductionAmount
      FROM Meal_ufnGetAllCheckOutBills_Current( @BeginDate , @EndDate) 
       
        DECLARE @MidTable TABLE
            (
              VolumeOfConsumption DECIMAL(18, 2) ,
              ConsumeValue DECIMAL(18, 2) ,
              FoodClubDis DECIMAL(18, 2) ,
              DiscountValue DECIMAL(18, 2) ,
              ConsumeDiscountValue DECIMAL(18, 2) ,
              FoodDerateValue DECIMAL(18, 2) ,
              DerateValue DECIMAL(18, 2) ,
              Oddment DECIMAL(18, 2) ,
              GratisValue DECIMAL(18, 2) ,
              ScoreExchangeFoodAmount DECIMAL(18, 2) ,
              ScoreCompensateAmount DECIMAL(18, 2) ,
              PayAmount DECIMAL(18, 2) ,
              CustomerTimes DECIMAL(18, 2) ,
              OneTablePay DECIMAL(18, 2) ,
              NumberOfPersons DECIMAL(18, 2)
            )
            
        IF ( @CashierId IS NOT NULL
             AND @HourId IS NOT NULL
           ) 
            BEGIN
                INSERT  @MidTable
                        SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                SUM(ConsumeValue) ,
                                -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                -1
                                * SUM(CASE WHEN DiscountApproverId IS NULL
                                           THEN 0
                                           ELSE DiscountValue
                                                + ConsumeDiscountValue
                                      END) ,
                                -1
                                * SUM(CASE WHEN DiscountApproverId IS NULL
                                           THEN DiscountValue
                                                + ConsumeDiscountValue
                                           ELSE 0
                                      END) ,
                                -1
                                * SUM(CASE WHEN DerateApproverId IS NULL
                                           THEN 0
                                           ELSE DerateValue
                                      END) ,
                                -1 * SUM(DerateValue) ,
                                -1 * SUM(Oddment) ,
                                -1 * SUM(GratisValue) ,
                                -1 * SUM(ScoreExchangeFoodAmount) ,
                                -1 * SUM(ScoreCompensateAmount) ,
                                SUM(PayAmount) ,
                                COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                ( SUM(PayAmount)
                                  / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                        WHEN 0 THEN 1
                                        ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                      END ) ) ,
                                SUM(ISNULL(a.NumberOfPersons, 0))
                        
                          FROM @CheckOutBills AS a 
                                 WHERE a.BusinessHourId = @HourId
                                 AND a.CashierId  = @CashierId 
                                 AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客 13服务员点餐
				 	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
            END
        ELSE 
            IF ( @CashierId IS NOT NULL
                 AND @HourId IS NULL
               ) 
                BEGIN
                    INSERT  @MidTable
                            SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                    SUM(ConsumeValue) ,
                                    -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                    -1
                                    * SUM(CASE WHEN DiscountApproverId IS NULL
                                               THEN 0
                                               ELSE DiscountValue
                                                    + ConsumeDiscountValue
                                          END) ,
                                    -1
                                    * SUM(CASE WHEN DiscountApproverId IS NULL
                                               THEN DiscountValue
                                                    + ConsumeDiscountValue
                                               ELSE 0
                                          END) ,
                                    -1
                                    * SUM(CASE WHEN DerateApproverId IS NULL
                                               THEN 0
                                               ELSE DerateValue
                                          END) ,
                                    -1 * SUM(DerateValue) ,
                                    -1 * SUM(Oddment) ,
                                    -1 * SUM(GratisValue) ,
                                    -1 * SUM(ScoreExchangeFoodAmount) ,
                                    -1 * SUM(ScoreCompensateAmount) ,
                                    SUM(PayAmount) ,
                                    COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                    ( SUM(PayAmount)
                                      / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                            WHEN 0 THEN 1
                                            ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                          END ) ) ,
                                    SUM(ISNULL(a.NumberOfPersons, 0))
                         -- FROM Meal_V_Bills4GetBills AS a
                          FROM @CheckOutBills AS a 
                                 WHERE   a.CashierId  = @CashierId 
                                 AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                      
                            
                   	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                                        
                END
            ELSE 
                IF ( @CashierId IS NULL
                     AND @HourId IS NOT NULL
                   ) 
                    BEGIN
                        INSERT  @MidTable
                                SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                        SUM(ConsumeValue) ,
                                        -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN 0
                                                   ELSE DiscountValue
                                                        + ConsumeDiscountValue
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN DiscountValue
                                                        + ConsumeDiscountValue
                                                   ELSE 0
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DerateApproverId IS NULL
                                                   THEN 0
                                                   ELSE DerateValue
                                              END) ,
                                        -1 * SUM(DerateValue) ,
                                        -1 * SUM(Oddment) ,
                                        -1 * SUM(GratisValue) ,
                                        -1 * SUM(ScoreExchangeFoodAmount) ,
                                        -1 * SUM(ScoreCompensateAmount) ,
                                        SUM(PayAmount) ,
                                        COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                        ( SUM(PayAmount)
                                          / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                                WHEN 0 THEN 1
                                                ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                              END ) ) ,
                                        SUM(ISNULL(a.NumberOfPersons, 0))
                           FROM @CheckOutBills AS a 
                                 WHERE a.BusinessHourId = @HourId
                                  AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客,13服务员点餐
                         
                                       
                                        
                  	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                                        
                    END
                ELSE 
                    BEGIN
                        INSERT  @MidTable
                                SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                        SUM(ConsumeValue) ,
                                        -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN 0
                                                   ELSE DiscountValue
                                                        + ConsumeDiscountValue
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN DiscountValue
                                                        + ConsumeDiscountValue
                                                   ELSE 0
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DerateApproverId IS NULL
                                                   THEN 0
                                                   ELSE DerateValue
                                              END) ,
                                        -1 * SUM(DerateValue) ,
                                        -1 * SUM(Oddment) ,
                                        -1 * SUM(GratisValue) ,
                                        -1 * SUM(ScoreExchangeFoodAmount) ,
                                        -1 * SUM(ScoreCompensateAmount) ,
                                        SUM(PayAmount) ,
                                        COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                        ( SUM(PayAmount)
                                          / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                                WHEN 0 THEN 1
                                                ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                              END ) ) ,
                                        SUM(ISNULL(a.NumberOfPersons, 0))
                          
                          FROM @CheckOutBills AS a 
                                 WHERE  a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客,13服务员点餐
                            
                            
                   	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                               
                                        
                    END
        
     
        --计算平台外卖到账金额
		 DECLARE @TakeAwayMoney DECIMAL (9,2)
					  SELECT  @TakeAwayMoney= SUM(ISNULL(TakeAwayPlatPayShopMoney,0))
					  FROM      dbo.Meal_PlatTakeAwayBillsDispose
					  WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
								AND BusinessHourNo LIKE @BusinessHourNo
								AND OperatorNo LIKE @CashierNo
								AND IsInMealReversedBillsTable=0
								AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
            IF(@PlatOrderFoodDiscount IS null)
            SET @PlatOrderFoodDiscount=0
	    INSERT  @returntable
                SELECT  SundryNo ,
                        SundryName ,
                        SUM(Volumes) AS Volumes
                FROM    (
                          SELECT    '01' AS SundryNo ,
                                    '*消费总额' AS SundryName ,
                                    (ISNULL(VolumeOfConsumption,0) +ISNULL(@PlatOrderFoodMoney,0) ) AS Volumes--消费+外卖菜例金额
                          FROM      @MidTable AS a
                          WHERE     ISNULL(VolumeOfConsumption,0)<> 0 OR ISNULL(@PlatOrderFoodMoney,0)<>0
                          UNION ALL
                          SELECT    '02' AS SundryNo ,
                                    '*优惠总额' AS SundryName ,
                                    ISNULL(SUM(FoodClubDis)+SUM(DiscountValue)+SUM(ConsumeDiscountValue)+SUM(FoodDerateValue)
                                    -@PlatOrderFoodDiscount+ SUM(Oddment)+SUM(GratisValue)+SUM(ScoreExchangeFoodAmount)
                                    +SUM(ScoreCompensateAmount),0)AS Volumes
                          FROM      @MidTable AS a
                          UNION ALL
                          SELECT    '025' AS SundryNo ,
                                    ' 会员优惠' AS SundryName ,
                                    FoodClubDis AS Volumes
                          FROM      @MidTable AS a
                          WHERE     FoodClubDis <> 0
                          UNION ALL
                          SELECT    '03' AS SundryNo ,
                                    ' 批准折扣' AS SundryName ,
                                    DiscountValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     DiscountValue <> 0
                          UNION ALL
                          SELECT    '042' AS SundryNo ,
                                    ' 会员折扣' AS SundryName ,
                                    ConsumeDiscountValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ConsumeDiscountValue <> 0
                        
                          UNION ALL
                          SELECT    '043' AS SundryNo ,
                                    ' 单品折扣' AS SundryName ,
                                    FoodDerateValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     FoodDerateValue <> 0
                          UNION ALL 
                          SELECT '044' AS SundryNo ,
                                    ' 平台折扣' AS SundryName ,
                                    0 - isnull(@PlatOrderFoodDiscount,0)
                                    WHERE isnull(@PlatOrderFoodDiscount,0)<>0
                          UNION ALL
                          SELECT    '06' AS SundryNo ,
                                    '     抹零' AS SundryName ,
                                    Oddment AS Volumes
                          FROM      @MidTable AS a
                          WHERE     Oddment <> 0
                          UNION ALL
                          SELECT    '07' AS SundryNo ,
                                    '     赠送' AS SundryName ,
                                    GratisValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     GratisValue <> 0
                          UNION ALL
                          SELECT    '071' AS SundryNo ,
                                    ' 积分兑换' AS SundryName ,
                                    ScoreExchangeFoodAmount AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ScoreExchangeFoodAmount <> 0
                          UNION ALL
                          SELECT    '072' AS SundryNo ,
                                    ' 积分抵扣' AS SundryName ,
                                    ScoreCompensateAmount AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ScoreCompensateAmount <> 0
                          UNION ALL
                          SELECT    '08' AS SundryNo ,
                                    '*实收金额' AS SundryName ,
                                    (ISNULL(PayAmount,0)+ISNULL(@TakeAwayMoney,0) )AS Volumes--实收+外卖平台到账
                          FROM      @MidTable AS a
                          WHERE     ISNULL(PayAmount,0) <> 0 OR ISNULL(@TakeAwayMoney,0)<>0
                          UNION ALL
                          SELECT    '09' AS SundryNo ,
                                    '*客次' AS SundryName ,
                                    CustomerTimes+ISNULL(@PlatOrderCustomerTimers,0)
                          FROM      @MidTable AS a
                          WHERE     CustomerTimes <> 0
                          UNION ALL
                          SELECT    '11' AS SundryNo ,
                                    '*客单' AS SundryName ,
                                    OneTablePay 
                          FROM      @MidTable AS a
                          WHERE     OneTablePay <> 0
                          UNION ALL
                          SELECT    '10' AS SundryNo ,
                                    '*就餐人数' AS SundryName ,
                                    NumberOfPersons+ISNULL(@PlatOrderCustomerTimers,0)
                          FROM      @MidTable AS a
                          WHERE     NumberOfPersons <> 0
                          UNION ALL
                          SELECT    '15' AS SundryNo ,----------------------------------------------------------
                                    '*退菜金额' AS SundryName ,
                                    SUM(Charge4Food) AS Volumes
                          FROM     (SELECT      b.No AS BillNo ,
												b.IsCheckedOut AS IsCheckedOut  ,      --账单及是否结账           
												b.BusinessDate ,--------------------------------------------
												b.BusinessHourId ,------------------
												hr.No AS BusinessHourNo ,--------------------------------------
												BusinessHourName , --营业日期取结账日期
												f.CaptainsOrderId ,
												f.Charge4Food,	   --单价、数量、折扣及金额------------------------------
												f.IsReturnSale ,-----------------------------------------------------       
												b.OperatorId AS CashierId ,
												e.No AS CashierNo 
										FROM    dbo.Meal_Bills b 
												LEFT OUTER JOIN dbo.Employees AS e ON b.Id = b.OperatorId
												LEFT JOIN dbo.Meal_Meal_CaptainsOrders AS o ON b.Id = o.ArrangedTableId
												LEFT JOIN dbo.Meal_OrderFoods AS f ON o.Id = f.CaptainsOrderId
												LEFT JOIN dbo.BusinessHours AS hr ON b.businessHourId = hr.Id
										WHERE   f.IsDeleted = 0)AS s
                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                    AND BusinessHourNo LIKE @BusinessHourNo
                                    AND CashierNo LIKE @CashierNo
                                    AND IsReturnSale = 1
                          GROUP BY  BusinessDate ,
                                    BusinessHourNo ,
                                    CashierNo  
                          UNION ALL
                          SELECT    '15' AS SundryNo ,
                                    '*退菜金额' AS SundryName ,
                                    SUM(RealDues) AS Volumes
                          FROM      dbo.Meal_SpeFoodSaleCollections
                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                    AND BusinessHourNo LIKE @BusinessHourNo
                                    AND CashierNo LIKE @CashierNo
                                    AND SpeType = 1
                          GROUP BY  BusinessDate ,
                                    BusinessHourNo ,
                                    CashierNo 
                        ) a
                GROUP BY SundryNo ,
                        SundryName
                ORDER BY SundryNo
               
                
--如果有外带或外卖则分别统计(堂食，外卖，外带 针对消费总额和实收总额的处理)-----------------
        DECLARE @TakeAwayFlag INT
        SET @TakeAwayFlag = 0
        SELECT  @TakeAwayFlag = COUNT(SaleType)
        FROM    @CheckOutBills
        WHERE   BusinessHourNo LIKE @BusinessHourNo
                AND CashierNo LIKE @CashierNo
                AND TakeAwayPlatTypeId IN ( 2, 4 ,5,6,7,8,9,10,11,12,13)
       
        IF @TakeAwayFlag > 0 --则首先生成堂食数据
            BEGIN
                INSERT  @returntable
                        SELECT  SundryNo ,
                                SundryName ,
                                SUM(Volumes) AS Volumes
                        FROM    ( SELECT    BusinessDate ,
                                            BusinessHourNo ,
                                            CashierNo ,
                                            '011' AS SundryNo ,
                                            '     堂食' AS SundryName ,
                                            SUM(ISNULL(VolumeOfConsumption, 0)) AS Volumes--消费合计
                                  FROM      @CheckOutBills
                                  WHERE     BusinessHourNo LIKE @BusinessHourNo
                                            AND CashierNo LIKE @CashierNo
                                            AND TakeAwayPlatTypeId = 1
                                  GROUP BY  BusinessDate ,
                                            BusinessHourNo ,
                                            CashierNo 
                                  HAVING    SUM(ISNULL(VolumeOfConsumption, 0)) <> 0
                                  UNION ALL
                                  SELECT    BusinessDate ,
                                            BusinessHourNo ,
                                            CashierNo ,
                                            '081' AS SundryNo ,
                                            '     堂食' AS SundryName ,
                                            SUM(PayAmount) AS Volumes--堂食实付
                                  FROM      @CheckOutBills
                                  WHERE     BusinessHourNo LIKE @BusinessHourNo
                                            AND CashierNo LIKE @CashierNo
                                            AND TakeAwayPlatTypeId = 1
                                  GROUP BY  BusinessDate ,
                                            BusinessHourNo ,
                                            CashierNo 
                                  HAVING    SUM(PayAmount) <> 0
                                ) AS a
                        GROUP BY SundryNo ,
                                SundryName
                        ORDER BY SundryNo
                --  处理外卖
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND CashierNo LIKE @CashierNo
                        AND TakeAwayPlatTypeId IN  (2,8,9)
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,
                                                    BusinessHourNo ,                                                  
                                                    CashierNo ,                                              
                                                    '014' AS SundryNo ,
                                                    '     外卖' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo
                                                    AND TakeAwayPlatTypeId IN( 2,9)
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                 
                                                    CashierNo                                                    
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,                                                   
                                                    BusinessHourNo ,                                                   
                                                    CashierNo ,                                                   
                                                    '084' AS SundryNo ,
                                                    '     外卖' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo                                                  
                                                    AND TakeAwayPlatTypeId IN( 2,9)
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                  
                                                    CashierNo
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                        
                    END  
                    --处理外带
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND CashierNo LIKE @CashierNo                       
                        AND TakeAwayPlatTypeId IN ( 4,7)
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,                                                    
                                                    BusinessHourNo ,                                                  
                                                    CashierNo ,                                                   
                                                    '015' AS SundryNo ,
                                                    '     外带' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo                                                   
                                                    AND TakeAwayPlatTypeId IN ( 4)--店内外带，和手机订外带
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                   
                                                    CashierNo                                                   
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,                                                    
                                                    BusinessHourNo ,
                                                    CashierNo ,   
                                                    '085' AS SundryNo ,
                                                    '     外带' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo                                                   
                                                    AND TakeAwayPlatTypeId IN ( 4)
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                  
                                                    CashierNo                                                   
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    --扫码点餐处理
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND CashierNo LIKE @CashierNo                       
                        AND TakeAwayPlatTypeId BETWEEN 5 AND 8 
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,                                                   
                                                    BusinessHourNo ,
                                                    CashierNo ,
                                                    '012' AS SundryNo ,
                                                    '   好易点' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo                                                  
                                                    AND TakeAwayPlatTypeId BETWEEN 5 AND 8 
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                  
                                                    CashierNo 
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,                                                   
                                                    BusinessHourNo ,
                                                    CashierNo ,
                                                    '082' AS SundryNo ,
                                                    '   好易点' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo
                                                    AND TakeAwayPlatTypeId  BETWEEN 5 AND 8 
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                 
                                                    CashierNo                                                   
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    
                
                    
                    --处理平台外卖
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(TakeAwayPlatPayShopMoney)
                FROM    dbo.Meal_PlatTakeAwayBillsDispose
                WHERE   BusinessDate BETWEEN @BeginDate AND @EndDate
                        AND BusinessHourNo LIKE @BusinessHourNo
                        AND OperatorNo LIKE @CashierNo
                        AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
                        
                DECLARE  @platOrderFoodMoney4All DECIMAL(9,2) 
                SET @platOrderFoodMoney4All=0
                --统计平台的菜例价格合计,用于消费合计统计
					SELECT @platOrderFoodMoney4All=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo                                
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                               
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( 
                                         SELECT     '016' AS SundryNo ,
                                                    '     平台' AS SundryName ,
                                                    ISNULL(@platOrderFoodMoney4All,0) AS Volumes --平台菜例消费总额
                                          
                                          UNION ALL
                                          SELECT   
                                                    '086' AS SundryNo ,
                                                    '     平台' AS SundryName ,
                                                    SUM(ISNULL(TakeAwayPlatPayShopMoney,
                                                              0)) AS Volumes
                                          FROM      dbo.Meal_PlatTakeAwayBillsDispose
                                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                                    AND BusinessHourNo LIKE @BusinessHourNo
                                                    AND OperatorNo LIKE @CashierNo
                                                    AND IsInMealReversedBillsTable=0  AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,
                                                    OperatorNo
                                          HAVING    SUM(ISNULL(TakeAwayPlatPayShopMoney,
                                                              0)) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    
                    
                    
            END          
        DECLARE @realdues DECIMAL(18, 2)
        DECLARE @saletables DECIMAL(18, 2)
        DECLARE @salenum DECIMAL(18, 2)
        SET @realdues = ( SELECT    Volumes
                          FROM      @returntable
                          WHERE     SundryName = '*实收金额'
                        )
        SET @saletables = ( SELECT  Volumes
                            FROM    @returntable
                            WHERE   SundryName = '*客次'
                          )
        SET @salenum = ( SELECT Volumes
                         FROM   @returntable
                         WHERE  SundryName = '*就餐人数'
                       )
        
        IF ( @saletables IS NULL
             OR @saletables = 0
           ) 
            BEGIN
                SET @saletables = 1
            END
        IF ( @salenum IS NULL
             OR @salenum = 0
           ) 
            BEGIN
                SET @salenum = 1
            END 
        ------将就餐人数项改为人均消费
        INSERT  INTO @returntable
        VALUES  ( '15', '*人均消费', 0 )  
        IF NOT ( @realdues IS NULL ) 
            BEGIN
                UPDATE  @returntable
                SET     Volumes = @realdues / @saletables
                WHERE   SundryName = '*客单'
                UPDATE  @returntable
                SET     Volumes = @realdues / @salenum
                WHERE   SundryName = '*人均消费'
            END
        
        RETURN
    END
GO

ALTER     FUNCTION [dbo].[Meal_ufnReportCenter_SundryCollections_Current]
    (
      @BeginDate DATETIME ,
      @EndDate DATETIME ,
      @DiningAreaN0 VARCHAR(3) , --厅
      @BusinessHourNo VARCHAR(2) , --营业时段
      @CashierNo VARCHAR(5)  --收银员
    )
RETURNS @returntable TABLE
    (
      SundryNo VARCHAR(5) ,
      SundryName NVARCHAR(50) ,
      Volumes DECIMAL(18, 2)
    )
AS 
    BEGIN
		DECLARE @PlatOrderFoodMoney DECIMAL (9,2)--记录平台的菜例价格合计,用于消费合计统计
		DECLARE @PlatOrderFoodDiscount DECIMAL(9,2)--记录平台折扣金额
		DECLARE @PlatOrderCustomerTimers INT --记录平台客次,就餐人数
        DECLARE @HourId INT
        DECLARE @CashierId UNIQUEIDENTIFIER
        IF ( @BusinessHourNo <> '%' ) 
            BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    dbo.BusinessHours
                                WHERE   No = @BusinessHourNo ) 
                    BEGIN
                        RETURN
                    END
                SET @HourId = ( SELECT TOP 1
                                        Id
                                FROM    dbo.BusinessHours
                                WHERE   No = @BusinessHourNo
                              )
            END
        IF ( @CashierNo <> '%' ) 
            BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    dbo.Employees
                                WHERE   No = @CashierNo ) 
                    BEGIN
                        RETURN
                    END
                SET @CashierId = ( SELECT TOP 1
                                            Id
                                   FROM     dbo.Employees
                                   WHERE    No = @CashierNo
                                 )
            END
      --临时表记录查询时间范围内的所有结账单（历史+当前）
      DECLARE  @CheckOutBills TABLE
    (
       [Id] UNIQUEIDENTIFIER NOT NULL 
      ,[BillNo] VARCHAR (13) NOT NULL 
      ,[BusinessDate]SMALLDATETIME NOT NULL 
      ,[BusinessHourId]INT NOT NULL 
      ,[BusinessHourNo]VARCHAR (2) NOT NULL 
      ,[BusinessHourName]NVARCHAR (20) NOT NULL 
     -- ,[ArrangeDatetime]DATETIME NOT NULL 
      ,[NumberOfPersons]INT NULL 
      ,[SalesManageId]UNIQUEIDENTIFIER NULL 
      ,[VolumeOfConsumption]numeric(11,2) NOT NULL 
      ,[GratisValue]numeric(9,2)NOT NULL 
      ,[Charge4Room]numeric(9,2)NOT NULL 
      ,[ChargeLimit]numeric(9,2)NOT NULL 
      ,[ServiceCharge]DECIMAL(9,2)NOT NULL 
      ,[OvertimePremium]DECIMAL(9,2)NOT NULL 
      ,[DiscountCategory]TINYINT NOT NULL 
      ,[FoodClubDis]numeric(9,2)NOT NULL 
      ,[DiscountValue]DECIMAL(9,2)NOT NULL 
      ,[DiscountApproverId]UNIQUEIDENTIFIER NULL 
      ,[DerateValue]DECIMAL(9,2)NOT NULL 
      ,[DerateApproverId]UNIQUEIDENTIFIER NULL 
      ,[DerationId]INT NULL 
      ,[Oddment]DECIMAL(9,2)NOT NULL 
      ,[PayAmount]DECIMAL(12,2)NOT NULL 
      ,[IsCheckedOut]BIT NOT NULL 
      ,[IsInvoiced]BIT NOT NULL 
      ,[InvoiceNo]VARCHAR(20)NULL 
      ,[InvoiceAmount]DECIMAL(12,2) NULL 
      ,[IsCloseOut]BIT NOT NULL 
      ,[PayDatetime]DATETIME NULL 
      ,[PrintedTimes]TINYINT NOT NULL 
      ,[ConsumeValue]numeric(9,2) NULL 
      ,[ConsumeClubDis]numeric(9,2) NULL 
      ,[ConsumeDiscountValue]numeric(9,2) NULL 
      --,[AssociatedOrderId]UNIQUEIDENTIFIER NULL 
      ,[CashierId]UNIQUEIDENTIFIER NOT NULL 
      ,[CashierNo]VARCHAR(5) NULL 
   --   ,[CashierName]NVARCHAR(50)NULL 
      ,[IsOwnDrinks]BIT NOT NULL 
      ,[ScoreExchangeFoodAmount]numeric(9,2)NOT NULL 
      ,[ScoreCompensateAmount]numeric(9,2)NOT NULL 
      ,[PricePromotionDis]numeric(9,2) NULL 
      ,[DiscountPromotionId]UNIQUEIDENTIFIER NULL 
      ,[SaleType]INT NULL 
      ,[SerialNo]VARCHAR (30) NOT NULL 
      ,TakeAwayPlatTypeId int NULL
      ,SuitDisAmount decimal(9,2) NULL
      ,ProductionAmount decimal(9,2)NULL
    )
      INSERT @CheckOutBills
      SELECT  [Id] 
      ,[BillNo]
      ,[BusinessDate]
      ,[BusinessHourId]
      ,[BusinessHourNo]
      ,[BusinessHourName]
     -- ,[ArrangeDatetime]
      ,[NumberOfPersons]
      ,[SalesManageId]
      ,[VolumeOfConsumption]
      ,[GratisValue]
      ,[Charge4Room]
      ,[ChargeLimit]
      ,[ServiceCharge]
      ,[OvertimePremium] 
      ,[DiscountCategory] 
      ,[FoodClubDis]
      ,[DiscountValue]
      ,[DiscountApproverId]
      ,[DerateValue]
      ,[DerateApproverId]
      ,[DerationId] 
      ,[Oddment]
      ,[PayAmount] 
      ,[IsCheckedOut]
      ,[IsInvoiced]
      ,[InvoiceNo]
      ,[InvoiceAmount]
      ,[IsCloseOut]
      ,[PayDatetime]
      ,[PrintedTimes]
      ,[ConsumeValue]
      ,[ConsumeClubDis]
      ,[ConsumeDiscountValue]
   --   ,[AssociatedOrderId]
      ,[CashierId]
      ,[CashierNo]
     -- ,[CashierName]
      ,[IsOwnDrinks]
      ,[ScoreExchangeFoodAmount]
      ,[ScoreCompensateAmount]
      ,[PricePromotionDis]
      ,[DiscountPromotionId]
      ,[SaleType]
      ,[SerialNo]
      ,TakeAwayPlatTypeId
      ,SuitDisAmount 
      ,ProductionAmount
      FROM Meal_ufnGetAllCheckOutBills_Current( @BeginDate , @EndDate) 
       
        DECLARE @MidTable TABLE
            (
              VolumeOfConsumption DECIMAL(18, 2) ,
              ConsumeValue DECIMAL(18, 2) ,
              FoodClubDis DECIMAL(18, 2) ,
              DiscountValue DECIMAL(18, 2) ,
              ConsumeDiscountValue DECIMAL(18, 2) ,
              FoodDerateValue DECIMAL(18, 2) ,
              DerateValue DECIMAL(18, 2) ,
              Oddment DECIMAL(18, 2) ,
              GratisValue DECIMAL(18, 2) ,
              ScoreExchangeFoodAmount DECIMAL(18, 2) ,
              ScoreCompensateAmount DECIMAL(18, 2) ,
              PayAmount DECIMAL(18, 2) ,
              CustomerTimes DECIMAL(18, 2) ,
              OneTablePay DECIMAL(18, 2) ,
              NumberOfPersons DECIMAL(18, 2)
            )
            
        IF ( @CashierId IS NOT NULL
             AND @HourId IS NOT NULL
           ) 
            BEGIN
                INSERT  @MidTable
                        SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                SUM(ConsumeValue) ,
                                -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                -1
                                * SUM(CASE WHEN DiscountApproverId IS NULL
                                           THEN 0
                                           ELSE DiscountValue
                                                + ConsumeDiscountValue
                                      END) ,
                                -1
                                * SUM(CASE WHEN DiscountApproverId IS NULL
                                           THEN DiscountValue
                                                + ConsumeDiscountValue
                                           ELSE 0
                                      END) ,
                                -1
                                * SUM(CASE WHEN DerateApproverId IS NULL
                                           THEN 0
                                           ELSE DerateValue
                                      END) ,
                                -1 * SUM(DerateValue) ,
                                -1 * SUM(Oddment) ,
                                -1 * SUM(GratisValue) ,
                                -1 * SUM(ScoreExchangeFoodAmount) ,
                                -1 * SUM(ScoreCompensateAmount) ,
                                SUM(PayAmount) ,
                                COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                ( SUM(PayAmount)
                                  / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                        WHEN 0 THEN 1
                                        ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                      END ) ) ,
                                SUM(ISNULL(a.NumberOfPersons, 0))
                        
                          FROM @CheckOutBills AS a 
                                 WHERE a.BusinessHourId = @HourId
                                 AND a.CashierId  = @CashierId 
                                 AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客,13服务员点餐
				 	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
            END
        ELSE 
            IF ( @CashierId IS NOT NULL
                 AND @HourId IS NULL
               ) 
                BEGIN
                    INSERT  @MidTable
                            SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                    SUM(ConsumeValue) ,
                                    -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                    -1
                                    * SUM(CASE WHEN DiscountApproverId IS NULL
                                               THEN 0
                                               ELSE DiscountValue
                                                    + ConsumeDiscountValue
                                          END) ,
                                    -1
                                    * SUM(CASE WHEN DiscountApproverId IS NULL
                                               THEN DiscountValue
                                                    + ConsumeDiscountValue
                                               ELSE 0
                                          END) ,
                                    -1
                                    * SUM(CASE WHEN DerateApproverId IS NULL
                                               THEN 0
                                               ELSE DerateValue
                                          END) ,
                                    -1 * SUM(DerateValue) ,
                                    -1 * SUM(Oddment) ,
                                    -1 * SUM(GratisValue) ,
                                    -1 * SUM(ScoreExchangeFoodAmount) ,
                                    -1 * SUM(ScoreCompensateAmount) ,
                                    SUM(PayAmount) ,
                                    COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                    ( SUM(PayAmount)
                                      / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                            WHEN 0 THEN 1
                                            ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                          END ) ) ,
                                    SUM(ISNULL(a.NumberOfPersons, 0))
                         -- FROM Meal_V_Bills4GetBills AS a
                          FROM @CheckOutBills AS a 
                                 WHERE   a.CashierId  = @CashierId 
                                 AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                      
                            
                   	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                                        
                END
            ELSE 
                IF ( @CashierId IS NULL
                     AND @HourId IS NOT NULL
                   ) 
                    BEGIN
                        INSERT  @MidTable
                                SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                        SUM(ConsumeValue) ,
                                        -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN 0
                                                   ELSE DiscountValue
                                                        + ConsumeDiscountValue
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN DiscountValue
                                                        + ConsumeDiscountValue
                                                   ELSE 0
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DerateApproverId IS NULL
                                                   THEN 0
                                                   ELSE DerateValue
                                              END) ,
                                        -1 * SUM(DerateValue) ,
                                        -1 * SUM(Oddment) ,
                                        -1 * SUM(GratisValue) ,
                                        -1 * SUM(ScoreExchangeFoodAmount) ,
                                        -1 * SUM(ScoreCompensateAmount) ,
                                        SUM(PayAmount) ,
                                        COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                        ( SUM(PayAmount)
                                          / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                                WHEN 0 THEN 1
                                                ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                              END ) ) ,
                                        SUM(ISNULL(a.NumberOfPersons, 0))
                           FROM @CheckOutBills AS a 
                                 WHERE a.BusinessHourId = @HourId
                                  AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                         
                                       
                                        
                  	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                                        
                    END
                ELSE 
                    BEGIN
                        INSERT  @MidTable
                                SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                        SUM(ConsumeValue) ,
                                        -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN 0
                                                   ELSE DiscountValue
                                                        + ConsumeDiscountValue
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN DiscountValue
                                                        + ConsumeDiscountValue
                                                   ELSE 0
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DerateApproverId IS NULL
                                                   THEN 0
                                                   ELSE DerateValue
                                              END) ,
                                        -1 * SUM(DerateValue) ,
                                        -1 * SUM(Oddment) ,
                                        -1 * SUM(GratisValue) ,
                                        -1 * SUM(ScoreExchangeFoodAmount) ,
                                        -1 * SUM(ScoreCompensateAmount) ,
                                        SUM(PayAmount) ,
                                        COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                        ( SUM(PayAmount)
                                          / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                                WHEN 0 THEN 1
                                                ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                              END ) ) ,
                                        SUM(ISNULL(a.NumberOfPersons, 0))
                          
                          FROM @CheckOutBills AS a 
                                 WHERE  a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                            
                            
                   	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                               
                                        
                    END
        
     
        --计算平台外卖到账金额
		 DECLARE @TakeAwayMoney DECIMAL (9,2)
					  SELECT  @TakeAwayMoney= SUM(ISNULL(TakeAwayPlatPayShopMoney,0))
					  FROM      dbo.Meal_PlatTakeAwayBillsDispose
					  WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
								AND BusinessHourNo LIKE @BusinessHourNo
								AND OperatorNo LIKE @CashierNo
								AND IsInMealReversedBillsTable=0
								AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
            IF(@PlatOrderFoodDiscount IS null)
            SET @PlatOrderFoodDiscount=0
	    INSERT  @returntable
                SELECT  SundryNo ,
                        SundryName ,
                        SUM(Volumes) AS Volumes
                FROM    (
                          SELECT    '01' AS SundryNo ,
                                    '*消费总额' AS SundryName ,
                                    (ISNULL(VolumeOfConsumption,0) +ISNULL(@PlatOrderFoodMoney,0) ) AS Volumes--消费+外卖菜例金额
                          FROM      @MidTable AS a
                          WHERE     ISNULL(VolumeOfConsumption,0)<> 0 OR ISNULL(@PlatOrderFoodMoney,0)<>0
                          UNION ALL
                          SELECT    '02' AS SundryNo ,
                                    '*优惠总额' AS SundryName ,
                                    ISNULL(SUM(FoodClubDis)+SUM(DiscountValue)+SUM(ConsumeDiscountValue)+SUM(FoodDerateValue)
                                    -@PlatOrderFoodDiscount+ SUM(Oddment)+SUM(GratisValue)+SUM(ScoreExchangeFoodAmount)
                                    +SUM(ScoreCompensateAmount),0)AS Volumes
                          FROM      @MidTable AS a
                          UNION ALL
                          SELECT    '025' AS SundryNo ,
                                    ' 会员优惠' AS SundryName ,
                                    FoodClubDis AS Volumes
                          FROM      @MidTable AS a
                          WHERE     FoodClubDis <> 0
                          UNION ALL
                          SELECT    '03' AS SundryNo ,
                                    ' 批准折扣' AS SundryName ,
                                    DiscountValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     DiscountValue <> 0
                          UNION ALL
                          SELECT    '042' AS SundryNo ,
                                    ' 会员折扣' AS SundryName ,
                                    ConsumeDiscountValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ConsumeDiscountValue <> 0
                        
                          UNION ALL
                          SELECT    '043' AS SundryNo ,
                                    ' 单品折扣' AS SundryName ,
                                    FoodDerateValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     FoodDerateValue <> 0
                          UNION ALL 
                          SELECT '044' AS SundryNo ,
                                    ' 平台折扣' AS SundryName ,
                                    0 - isnull(@PlatOrderFoodDiscount,0)
                                    WHERE isnull(@PlatOrderFoodDiscount,0)<>0
                          UNION ALL
                          SELECT    '06' AS SundryNo ,
                                    '     抹零' AS SundryName ,
                                    Oddment AS Volumes
                          FROM      @MidTable AS a
                          WHERE     Oddment <> 0
                          UNION ALL
                          SELECT    '07' AS SundryNo ,
                                    '     赠送' AS SundryName ,
                                    GratisValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     GratisValue <> 0
                          UNION ALL
                          SELECT    '071' AS SundryNo ,
                                    ' 积分兑换' AS SundryName ,
                                    ScoreExchangeFoodAmount AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ScoreExchangeFoodAmount <> 0
                          UNION ALL
                          SELECT    '072' AS SundryNo ,
                                    ' 积分抵扣' AS SundryName ,
                                    ScoreCompensateAmount AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ScoreCompensateAmount <> 0
                          UNION ALL
                          SELECT    '08' AS SundryNo ,
                                    '*实收金额' AS SundryName ,
                                    (ISNULL(PayAmount,0)+ISNULL(@TakeAwayMoney,0) )AS Volumes--实收+外卖平台到账
                          FROM      @MidTable AS a
                          WHERE     ISNULL(PayAmount,0) <> 0 OR ISNULL(@TakeAwayMoney,0)<>0
                          UNION ALL
                          SELECT    '09' AS SundryNo ,
                                    '*客次' AS SundryName ,
                                    CustomerTimes+ISNULL(@PlatOrderCustomerTimers,0)
                          FROM      @MidTable AS a
                          WHERE     CustomerTimes <> 0
                          UNION ALL
                          SELECT    '11' AS SundryNo ,
                                    '*客单' AS SundryName ,
                                    OneTablePay 
                          FROM      @MidTable AS a
                          WHERE     OneTablePay <> 0
                          UNION ALL
                          SELECT    '10' AS SundryNo ,
                                    '*就餐人数' AS SundryName ,
                                    NumberOfPersons+ISNULL(@PlatOrderCustomerTimers,0)
                          FROM      @MidTable AS a
                          WHERE     NumberOfPersons <> 0
                          UNION ALL
                          SELECT    '15' AS SundryNo ,----------------------------------------------------------
                                    '*退菜金额' AS SundryName ,
                                    SUM(Charge4Food) AS Volumes
                          FROM     (SELECT      b.No AS BillNo ,
												b.IsCheckedOut AS IsCheckedOut  ,      --账单及是否结账           
												b.BusinessDate ,--------------------------------------------
												b.BusinessHourId ,------------------
												hr.No AS BusinessHourNo ,--------------------------------------
												BusinessHourName , --营业日期取结账日期
												f.CaptainsOrderId ,
												f.Charge4Food,	   --单价、数量、折扣及金额------------------------------
												f.IsReturnSale ,-----------------------------------------------------       
												b.OperatorId AS CashierId ,
												e.No AS CashierNo 
										FROM    dbo.Meal_Bills b 
												LEFT OUTER JOIN dbo.Employees AS e ON b.Id = b.OperatorId
												LEFT JOIN dbo.Meal_Meal_CaptainsOrders AS o ON b.Id = o.ArrangedTableId
												LEFT JOIN dbo.Meal_OrderFoods AS f ON o.Id = f.CaptainsOrderId
												LEFT JOIN dbo.BusinessHours AS hr ON b.businessHourId = hr.Id
										WHERE   f.IsDeleted = 0)AS s
                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                    AND BusinessHourNo LIKE @BusinessHourNo
                                    AND CashierNo LIKE @CashierNo
                                    AND IsReturnSale = 1
                          GROUP BY  BusinessDate ,
                                    BusinessHourNo ,
                                    CashierNo  
                          UNION ALL
                          SELECT    '15' AS SundryNo ,
                                    '*退菜金额' AS SundryName ,
                                    SUM(RealDues) AS Volumes
                          FROM      dbo.Meal_SpeFoodSaleCollections
                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                    AND BusinessHourNo LIKE @BusinessHourNo
                                    AND CashierNo LIKE @CashierNo
                                    AND SpeType = 1
                          GROUP BY  BusinessDate ,
                                    BusinessHourNo ,
                                    CashierNo 
                        ) a
                GROUP BY SundryNo ,
                        SundryName
                ORDER BY SundryNo
               
                
--如果有外带或外卖则分别统计(堂食，外卖，外带 针对消费总额和实收总额的处理)-----------------
        DECLARE @TakeAwayFlag INT
        SET @TakeAwayFlag = 0
        SELECT  @TakeAwayFlag = COUNT(SaleType)
        FROM    @CheckOutBills
        WHERE   BusinessHourNo LIKE @BusinessHourNo
                AND CashierNo LIKE @CashierNo
                AND TakeAwayPlatTypeId IN ( 2, 4 ,5,6,7,8,9,10,11,12,13)
       
        IF @TakeAwayFlag > 0 --则首先生成堂食数据
            BEGIN
                INSERT  @returntable
                        SELECT  SundryNo ,
                                SundryName ,
                                SUM(Volumes) AS Volumes
                        FROM    ( SELECT    BusinessDate ,
                                            BusinessHourNo ,
                                            CashierNo ,
                                            '011' AS SundryNo ,
                                            '     堂食' AS SundryName ,
                                            SUM(ISNULL(VolumeOfConsumption, 0)) AS Volumes--消费合计
                                  FROM      @CheckOutBills
                                  WHERE     BusinessHourNo LIKE @BusinessHourNo
                                            AND CashierNo LIKE @CashierNo
                                            AND TakeAwayPlatTypeId = 1
                                  GROUP BY  BusinessDate ,
                                            BusinessHourNo ,
                                            CashierNo 
                                  HAVING    SUM(ISNULL(VolumeOfConsumption, 0)) <> 0
                                  UNION ALL
                                  SELECT    BusinessDate ,
                                            BusinessHourNo ,
                                            CashierNo ,
                                            '081' AS SundryNo ,
                                            '     堂食' AS SundryName ,
                                            SUM(PayAmount) AS Volumes--堂食实付
                                  FROM      @CheckOutBills
                                  WHERE     BusinessHourNo LIKE @BusinessHourNo
                                            AND CashierNo LIKE @CashierNo
                                            AND TakeAwayPlatTypeId = 1
                                  GROUP BY  BusinessDate ,
                                            BusinessHourNo ,
                                            CashierNo 
                                  HAVING    SUM(PayAmount) <> 0
                                ) AS a
                        GROUP BY SundryNo ,
                                SundryName
                        ORDER BY SundryNo
                --  处理外卖
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND CashierNo LIKE @CashierNo
                        AND TakeAwayPlatTypeId IN  (2,8,9)
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,
                                                    BusinessHourNo ,                                                  
                                                    CashierNo ,                                              
                                                    '014' AS SundryNo ,
                                                    '     外卖' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo
                                                    AND TakeAwayPlatTypeId IN( 2,9)
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                 
                                                    CashierNo                                                    
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,                                                   
                                                    BusinessHourNo ,                                                   
                                                    CashierNo ,                                                   
                                                    '084' AS SundryNo ,
                                                    '     外卖' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo                                                  
                                                    AND TakeAwayPlatTypeId IN( 2,9)
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                  
                                                    CashierNo
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                        
                    END  
                    --处理外带
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND CashierNo LIKE @CashierNo                       
                        AND TakeAwayPlatTypeId IN ( 4,7)
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,                                                    
                                                    BusinessHourNo ,                                                  
                                                    CashierNo ,                                                   
                                                    '015' AS SundryNo ,
                                                    '     外带' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo                                                   
                                                    AND TakeAwayPlatTypeId IN ( 4)--店内外带，和手机订外带
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                   
                                                    CashierNo                                                   
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,                                                    
                                                    BusinessHourNo ,
                                                    CashierNo ,   
                                                    '085' AS SundryNo ,
                                                    '     外带' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo                                                   
                                                    AND TakeAwayPlatTypeId IN ( 4)
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                  
                                                    CashierNo                                                   
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    --扫码点餐处理
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND CashierNo LIKE @CashierNo                       
                        AND TakeAwayPlatTypeId BETWEEN 5 AND 8 
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,                                                   
                                                    BusinessHourNo ,
                                                    CashierNo ,
                                                    '012' AS SundryNo ,
                                                    '   好易点' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo                                                  
                                                    AND TakeAwayPlatTypeId BETWEEN 5 AND 8 
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                  
                                                    CashierNo 
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,                                                   
                                                    BusinessHourNo ,
                                                    CashierNo ,
                                                    '082' AS SundryNo ,
                                                    '   好易点' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo
                                                    AND TakeAwayPlatTypeId  BETWEEN 5 AND 8 
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,                                                 
                                                    CashierNo                                                   
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    
                
                    
                    --处理平台外卖
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(TakeAwayPlatPayShopMoney)
                FROM    dbo.Meal_PlatTakeAwayBillsDispose
                WHERE   BusinessDate BETWEEN @BeginDate AND @EndDate
                        AND BusinessHourNo LIKE @BusinessHourNo
                        AND OperatorNo LIKE @CashierNo
                        AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
                        
                DECLARE  @platOrderFoodMoney4All DECIMAL(9,2) 
                SET @platOrderFoodMoney4All=0
                --统计平台的菜例价格合计,用于消费合计统计
					SELECT @platOrderFoodMoney4All=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo                                
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                               
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( 
                                         SELECT     '016' AS SundryNo ,
                                                    '     平台' AS SundryName ,
                                                    ISNULL(@platOrderFoodMoney4All,0) AS Volumes --平台菜例消费总额
                                          
                                          UNION ALL
                                          SELECT   
                                                    '086' AS SundryNo ,
                                                    '     平台' AS SundryName ,
                                                    SUM(ISNULL(TakeAwayPlatPayShopMoney,
                                                              0)) AS Volumes
                                          FROM      dbo.Meal_PlatTakeAwayBillsDispose
                                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                                    AND BusinessHourNo LIKE @BusinessHourNo
                                                    AND OperatorNo LIKE @CashierNo
                                                    AND IsInMealReversedBillsTable=0  AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,
                                                    OperatorNo
                                          HAVING    SUM(ISNULL(TakeAwayPlatPayShopMoney,
                                                              0)) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    
                    
                    
            END          
        DECLARE @realdues DECIMAL(18, 2)
        DECLARE @saletables DECIMAL(18, 2)
        DECLARE @salenum DECIMAL(18, 2)
        SET @realdues = ( SELECT    Volumes
                          FROM      @returntable
                          WHERE     SundryName = '*实收金额'
                        )
        SET @saletables = ( SELECT  Volumes
                            FROM    @returntable
                            WHERE   SundryName = '*客次'
                          )
        SET @salenum = ( SELECT Volumes
                         FROM   @returntable
                         WHERE  SundryName = '*就餐人数'
                       )
        
        IF ( @saletables IS NULL
             OR @saletables = 0
           ) 
            BEGIN
                SET @saletables = 1
            END
        IF ( @salenum IS NULL
             OR @salenum = 0
           ) 
            BEGIN
                SET @salenum = 1
            END 
        ------将就餐人数项改为人均消费
        INSERT  INTO @returntable
        VALUES  ( '15', '*人均消费', 0 )  
        IF NOT ( @realdues IS NULL ) 
            BEGIN
                UPDATE  @returntable
                SET     Volumes = @realdues / @saletables
                WHERE   SundryName = '*客单'
                UPDATE  @returntable
                SET     Volumes = @realdues / @salenum
                WHERE   SundryName = '*人均消费'
            END
        
        RETURN
    END
GO
-------------
UPDATE dbo.Foods
SET IsNeedSubmit=0
WHERE FoodName IN ('配送费','打包费')
GO
ALTER   FUNCTION [dbo].[Meal_New_OrderFoodsStatNew]
    (
      @BarNo VARCHAR(3) = '%' ,
      @beginBusinessDate DATETIME ,
      @endBusinessDate DATETIME ,
      @businessHourNo VARCHAR(10) = '%' ,
      @IsShowSpeci BIT = 1 ,--是否显示多规格
      @IsNotIncludeSuit INT = 1 ,--是否含套餐菜例  1:全部，2 ：只统计单菜销售 3：只统计套餐中菜例销售
      @EatType VARCHAR(3) = '%'--就餐方式
    )
RETURNS @returntable TABLE
    (
      FoodId UNIQUEIDENTIFIER NOT NULL ,  --菜品或与部门联合主键
      FoodTypeId UNIQUEIDENTIFIER NOT NULL ,
      FoodTypeNo VARCHAR(2) NOT NULL ,
      FoodTypeName NVARCHAR(20) NOT NULL ,
      FoodNo VARCHAR(8) NOT NULL ,
      FoodName NVARCHAR(50) NOT NULL ,
      Specification NVARCHAR(10) NOT NULL ,
      SpecificationNo VARCHAR(2) NOT NULL ,
      SaleQuantity NUMERIC(9, 2) NOT NULL ,
      saleCharge4Food NUMERIC(12, 2) NOT NULL ,
      sumall NUMERIC(9, 2) NOT NULL
    )
AS
    BEGIN
    
        DECLARE @Suit TABLE--套餐临时表
            (
              CaptainsOrders4SuitId UNIQUEIDENTIFIER NOT NULL ,--套餐单id
              TakeawayPlatTypeId INT NOT NULL--销售类型
            );
        INSERT  INTO @Suit
                SELECT  su.Id ,
                        b.TakeAwayPlatTypeId
                FROM    dbo.Meal_Meal_CaptainsOrders4Suits AS su
                        LEFT JOIN dbo.Meal_Meal_CaptainsOrders AS c ON su.Id = c.Id
                        LEFT JOIN dbo.Meal_Bills AS b ON b.Id = c.ArrangedTableId
                WHERE   b.PayDatetime BETWEEN @beginBusinessDate
                                      AND     @endBusinessDate
                UNION ALL
                SELECT  su.Id ,
                        b.TakeAwayPlatTypeId
                FROM    dbo.Meal_History_Meal_CaptainsOrders4Suits AS su
                        LEFT JOIN dbo.Meal_History_Meal_CaptainsOrders AS c ON su.Id = c.Id
                        LEFT JOIN dbo.Meal_History_Bills AS b ON b.Id = c.ArrangedTableId
                WHERE   b.PayDatetime BETWEEN @beginBusinessDate
                                      AND     @endBusinessDate; 

    
        DECLARE @Meal_VALLOrderFoods TABLE--菜例销售明细临时表
            (
              [OrderFoodId] UNIQUEIDENTIFIER NOT NULL ,
              [FoodTypeId] UNIQUEIDENTIFIER NOT NULL ,
              [FoodTypeNo] CHAR(2) NULL ,
              [FoodTypeName] VARCHAR(20) NOT  NULL ,
              [FoodId] UNIQUEIDENTIFIER NOT NULL ,
              [FoodNo] VARCHAR(8) NOT  NULL ,
              [FoodName] NVARCHAR(50) NOT NULL ,
              [Specification] NVARCHAR(10) NOT  NULL ,
              [SpecificationNo] NVARCHAR(10) NOT  NULL ,
              [Quantity] DECIMAL(9, 2) NOT NULL ,
              [Charge4Food] DECIMAL(10, 2) NOT NULL ,
              CaptainsOrderName VARCHAR(255) NOT NULL
            );
        IF ( @EatType = '%' )--就餐方式
            BEGIN 
                INSERT  INTO @Meal_VALLOrderFoods
                        SELECT  [OrderFoodId] ,
                                [FoodTypeId] ,
                                [FoodTypeNo] ,
                                [FoodTypeName] ,
                                [FoodId] ,
                                [FoodNo] ,
                                [FoodName] ,
                                [Specification] ,
                                [SpecificationNo] ,
                                [Quantity] ,
                                [Charge4Food] ,
                                CaptainsOrderName
                        FROM    Meal_V_AllOrderFoodsNew
                        WHERE   ( OrderDatetime BETWEEN @beginBusinessDate
                                                AND     @endBusinessDate )
                                AND BusinessHourNo LIKE @businessHourNo;
            END;
  
        IF ( @EatType = '1' )
            BEGIN 
                INSERT  INTO @Meal_VALLOrderFoods
                        SELECT  [OrderFoodId] ,
                                [FoodTypeId] ,
                                [FoodTypeNo] ,
                                [FoodTypeName] ,
                                [FoodId] ,
                                [FoodNo] ,
                                [FoodName] ,
                                [Specification] ,
                                [SpecificationNo] ,
                                [Quantity] ,
                                [Charge4Food] ,
                                CaptainsOrderName
                        FROM    Meal_V_AllOrderFoodsNew
                        WHERE   ( OrderDatetime BETWEEN @beginBusinessDate
                                                AND     @endBusinessDate )
                                AND BusinessHourNo LIKE @businessHourNo
                                AND CaptainsOrderId IN (
                                SELECT  Id
                                FROM    dbo.Meal_V_AllBillsId
                                WHERE   TakeAwayPlatTypeId BETWEEN 1 AND 9 OR TakeAwayPlatTypeId=13);--13 服务员点餐
		 
                INSERT  INTO @Meal_VALLOrderFoods--处理套餐中的
                        SELECT  [OrderFoodId] ,
                                [FoodTypeId] ,
                                [FoodTypeNo] ,
                                [FoodTypeName] ,
                                [FoodId] ,
                                [FoodNo] ,
                                [FoodName] ,
                                [Specification] ,
                                [SpecificationNo] ,
                                [Quantity] ,
                                [Charge4Food] ,
                                CaptainsOrderName
                        FROM    Meal_V_AllOrderFoodsNew
                        WHERE   ( OrderDatetime BETWEEN @beginBusinessDate
                                                AND     @endBusinessDate )
                                AND BusinessHourNo LIKE @businessHourNo
                                AND CaptainsOrderId IN (
                                SELECT  CaptainsOrders4SuitId
                                FROM    @Suit
                                WHERE   TakeawayPlatTypeId BETWEEN 1 AND 9 OR TakeAwayPlatTypeId=13);
	
		 		 
            END;
        IF ( @EatType = '2' )
            BEGIN 
                INSERT  INTO @Meal_VALLOrderFoods
                        SELECT  [OrderFoodId] ,
                                [FoodTypeId] ,
                                [FoodTypeNo] ,
                                [FoodTypeName] ,
                                [FoodId] ,
                                [FoodNo] ,
                                [FoodName] ,
                                [Specification] ,
                                [SpecificationNo] ,
                                [Quantity] ,
                                [Charge4Food] ,
                                CaptainsOrderName
                        FROM    Meal_V_AllOrderFoodsNew
                        WHERE   ( OrderDatetime BETWEEN @beginBusinessDate
                                                AND     @endBusinessDate )
                                AND BusinessHourNo LIKE @businessHourNo
                                AND CaptainsOrderId IN (
                                SELECT  Id
                                FROM    dbo.Meal_V_AllBillsId
                                WHERE   TakeAwayPlatTypeId BETWEEN 10 AND 12 );
		 
		 
                INSERT  INTO @Meal_VALLOrderFoods--处理套餐中的
                        SELECT  [OrderFoodId] ,
                                [FoodTypeId] ,
                                [FoodTypeNo] ,
                                [FoodTypeName] ,
                                [FoodId] ,
                                [FoodNo] ,
                                [FoodName] ,
                                [Specification] ,
                                [SpecificationNo] ,
                                [Quantity] ,
                                [Charge4Food] ,
                                CaptainsOrderName
                        FROM    Meal_V_AllOrderFoodsNew
                        WHERE   ( OrderDatetime BETWEEN @beginBusinessDate
                                                AND     @endBusinessDate )
                                AND BusinessHourNo LIKE @businessHourNo
                                AND CaptainsOrderId IN (
                                SELECT  CaptainsOrders4SuitId
                                FROM    @Suit
                                WHERE   TakeawayPlatTypeId BETWEEN 10 AND 12 );
	
            END;
  
        DECLARE @sumall DECIMAL(9, 2);
  
        IF ( @IsNotIncludeSuit = 1 )--统计所有的包含单品销售和套餐内菜例销售
            BEGIN
                IF ( @IsShowSpeci = 1 )
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,    --菜类
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo ,  --,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Charge4Food) AS sumCharge4Food , --金额（折后，赠送为0）        
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,    --菜类
                                        FoodId ,
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo  --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                                
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;
                ELSE
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,    --菜类
                                        FoodNo ,
                                        FoodName ,
                                        '份' ,
                                        '0' ,--,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Charge4Food) AS sumCharge4Food --金额（折后，赠送为0）    
                                        ,
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,   --菜类
                                        FoodId ,
                                        FoodNo ,
                                        FoodName --,Specification    --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;        
            END;
         IF ( @IsNotIncludeSuit = 2 )
            BEGIN
                IF ( @IsShowSpeci = 1 )
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,   --菜类
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo ,   --,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Charge4Food) AS sumCharge4Food --金额（折后，赠送为0）    
                                        ,
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                WHERE   f.CaptainsOrderName <> '套餐单'
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,    --菜类
                                        FoodId ,
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo  --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;
                ELSE
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,   --菜类
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo ,   --,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Charge4Food) AS sumCharge4Food --金额（折后，赠送为0） 
                                        ,
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                WHERE   f.CaptainsOrderName <> '套餐单'
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,  --菜类
                                        Specification ,
                                        SpecificationNo ,  --,Price  菜例
                                        FoodId ,
                                        FoodNo ,
                                        FoodName --,Specification    --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;
            END; 
            
			 IF ( @IsNotIncludeSuit = 3 )--只统计套餐中菜例销售
            BEGIN
                IF ( @IsShowSpeci = 1 )
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,   --菜类
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo ,   --,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Charge4Food) AS sumCharge4Food --金额（折后，赠送为0）    
                                        ,
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                WHERE   f.CaptainsOrderName = '套餐单'
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,    --菜类
                                        FoodId ,
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo  --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;
                ELSE
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,   --菜类
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo ,   --,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Charge4Food) AS sumCharge4Food --金额（折后，赠送为0） 
                                        ,
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                WHERE   f.CaptainsOrderName = '套餐单'
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,  --菜类
                                        Specification ,
                                        SpecificationNo ,  --,Price  菜例
                                        FoodId ,
                                        FoodNo ,
                                        FoodName --,Specification    --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;
            END; 
            
        UPDATE  @returntable
        SET     sumall = @sumall;
        RETURN
    END
GO
ALTER  FUNCTION [dbo].[Meal_ufnCurrentTimeBusinessReport]  --快餐当前营业日当前的销售分析 
    (
      @BusinessDate DATETIME   --营业日期
    )
RETURNS @returntable TABLE
    (
      BillType NVARCHAR(20) NULL   --账单类型
      ,
      BillTypeCount INTEGER NULL  --账单类型数量
      ,
      VolumeOfConsumption DECIMAL(11, 2) NOT NULL   --今日消费金额
      ,
      PayAmount DECIMAL(18, 2) NOT NULL       --今日实收金额
      ,
      DiscountAmount DECIMAL(18, 2) NOT NULL       --优惠金额
    )
AS
    BEGIN
        INSERT  INTO @returntable
                SELECT  CASE TakeAwayPlatTypeId
                          WHEN 1 THEN '堂食'
                          WHEN 2 THEN '外卖'
                          WHEN 4 THEN '外带'
                          WHEN 5 THEN '扫码点餐'
                          WHEN 6 THEN '秒吃'
                          WHEN 7 THEN '自取'
                          WHEN 8 THEN '外送'
                          WHEN 9 THEN '米客外卖'
                          WHEN 10 THEN '美团外卖'
                          WHEN 11 THEN '饿了么'
                          WHEN 12 THEN '百度外卖'
                           WHEN 13 THEN '服务员点餐'
                          ELSE '其他'
                        END ,
                        COUNT(TakeAwayPlatTypeId) ,
                        SUM(VolumeOfConsumption) ,
                        SUM(PayAmount) ,
                        SUM(VolumeOfConsumption) - SUM(PayAmount)
                FROM    dbo.Meal_Bills
                WHERE   BusinessDate = @BusinessDate
                GROUP BY TakeAwayPlatTypeId;
      
        RETURN; 
    END;
GO
ALTER  FUNCTION [dbo].[Meal_ufnReportCenter_SundryCollections]
    (
      @BeginDate DATETIME ,
      @EndDate DATETIME ,
      @DiningAreaN0 VARCHAR(3) , --厅
      @BusinessHourNo VARCHAR(2) , --营业时段
      @CashierNo VARCHAR(5)  --收银员
    )
RETURNS @returntable TABLE
    (
      SundryNo VARCHAR(5) ,
      SundryName NVARCHAR(50) ,
      Volumes DECIMAL(18, 2)
    )
AS 
    BEGIN
		DECLARE @PlatOrderFoodMoney DECIMAL (9,2)--记录平台的菜例价格合计,用于消费合计统计
		DECLARE @PlatOrderFoodDiscount DECIMAL(9,2)--记录平台折扣金额
		DECLARE @PlatOrderCustomerTimers INT --记录平台客次,就餐人数
        DECLARE @HourId INT
        DECLARE @CashierId UNIQUEIDENTIFIER
        IF ( @BusinessHourNo <> '%' ) 
            BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    dbo.BusinessHours
                                WHERE   No = @BusinessHourNo ) 
                    BEGIN
                        RETURN
                    END
                SET @HourId = ( SELECT TOP 1
                                        Id
                                FROM    dbo.BusinessHours
                                WHERE   No = @BusinessHourNo
                              )
            END
        IF ( @CashierNo <> '%' ) 
            BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    dbo.Employees
                                WHERE   No = @CashierNo ) 
                    BEGIN
                        RETURN
                    END
                SET @CashierId = ( SELECT TOP 1
                                            Id
                                   FROM     dbo.Employees
                                   WHERE    No = @CashierNo
                                 )
            END
      --临时表记录查询时间范围内的所有结账单（历史+当前）
      DECLARE  @CheckOutBills TABLE
    (
       [Id] UNIQUEIDENTIFIER NOT NULL 
      ,[BillNo] VARCHAR (13) NOT NULL 
      ,[BusinessDate]SMALLDATETIME NOT NULL 
      ,[BusinessHourId]INT NOT NULL 
      ,[BusinessHourNo]VARCHAR (2) NOT NULL 
      ,[BusinessHourName]NVARCHAR (20) NOT NULL 
      ,[ArrangeDatetime]DATETIME NOT NULL 
      ,[NumberOfPersons]INT NULL 
      ,[SalesManageId]UNIQUEIDENTIFIER NULL 
      ,[VolumeOfConsumption]numeric(11,2) NOT NULL 
      ,[GratisValue]numeric(9,2)NOT NULL 
      ,[Charge4Room]numeric(9,2)NOT NULL 
      ,[ChargeLimit]numeric(9,2)NOT NULL 
      ,[ServiceCharge]DECIMAL(9,2)NOT NULL 
      ,[OvertimePremium]DECIMAL(9,2)NOT NULL 
      ,[DiscountCategory]TINYINT NOT NULL 
      ,[FoodClubDis]numeric(9,2)NOT NULL 
      ,[DiscountValue]DECIMAL(9,2)NOT NULL 
      ,[DiscountApproverId]UNIQUEIDENTIFIER NULL 
      ,[DerateValue]DECIMAL(9,2)NOT NULL 
      ,[DerateApproverId]UNIQUEIDENTIFIER NULL 
      ,[DerationId]INT NULL 
      ,[Oddment]DECIMAL(9,2)NOT NULL 
      ,[PayAmount]DECIMAL(12,2)NOT NULL 
      ,[IsCheckedOut]BIT NOT NULL 
      ,[IsInvoiced]BIT NOT NULL 
      ,[InvoiceNo]VARCHAR(20)NULL 
      ,[InvoiceAmount]DECIMAL(12,2) NULL 
      ,[IsCloseOut]BIT NOT NULL 
      ,[PayDatetime]DATETIME NULL 
      ,[PrintedTimes]TINYINT NOT NULL 
      ,[ConsumeValue]numeric(9,2) NULL 
      ,[ConsumeClubDis]numeric(9,2) NULL 
      ,[ConsumeDiscountValue]numeric(9,2) NULL 
      ,[AssociatedOrderId]UNIQUEIDENTIFIER NULL 
      ,[CashierId]UNIQUEIDENTIFIER NOT NULL 
      ,[CashierNo]VARCHAR(5) NULL 
      ,[CashierName]NVARCHAR(50)NULL 
      ,[IsOwnDrinks]BIT NOT NULL 
      ,[ScoreExchangeFoodAmount]numeric(9,2)NOT NULL 
      ,[ScoreCompensateAmount]numeric(9,2)NOT NULL 
      ,[PricePromotionDis]numeric(9,2) NULL 
      ,[DiscountPromotionId]UNIQUEIDENTIFIER NULL 
      ,[SaleType]INT NULL 
      ,[SerialNo]VARCHAR (30) NOT NULL 
      ,TakeAwayPlatTypeId int NULL
      ,SuitDisAmount decimal(9,2) NULL
      ,ProductionAmount decimal(9,2)NULL
    )
      INSERT @CheckOutBills
      SELECT  [Id] 
      ,[BillNo]
      ,[BusinessDate]
      ,[BusinessHourId]
      ,[BusinessHourNo]
      ,[BusinessHourName]
      ,[ArrangeDatetime]
      ,[NumberOfPersons]
      ,[SalesManageId]
      ,[VolumeOfConsumption]
      ,[GratisValue]
      ,[Charge4Room]
      ,[ChargeLimit]
      ,[ServiceCharge]
      ,[OvertimePremium] 
      ,[DiscountCategory] 
      ,[FoodClubDis]
      ,[DiscountValue]
      ,[DiscountApproverId]
      ,[DerateValue]
      ,[DerateApproverId]
      ,[DerationId] 
      ,[Oddment]
      ,[PayAmount] 
      ,[IsCheckedOut]
      ,[IsInvoiced]
      ,[InvoiceNo]
      ,[InvoiceAmount]
      ,[IsCloseOut]
      ,[PayDatetime]
      ,[PrintedTimes]
      ,[ConsumeValue]
      ,[ConsumeClubDis]
      ,[ConsumeDiscountValue]
      ,[AssociatedOrderId]
      ,[CashierId]
      ,[CashierNo]
      ,[CashierName]
      ,[IsOwnDrinks]
      ,[ScoreExchangeFoodAmount]
      ,[ScoreCompensateAmount]
      ,[PricePromotionDis]
      ,[DiscountPromotionId]
      ,[SaleType]
      ,[SerialNo]
      ,TakeAwayPlatTypeId
      ,SuitDisAmount 
      ,ProductionAmount
      FROM Meal_ufnGetAllCheckOutBills( @BeginDate , @EndDate) 
       
        DECLARE @MidTable TABLE
            (
              VolumeOfConsumption DECIMAL(18, 2) ,
              ConsumeValue DECIMAL(18, 2) ,
              FoodClubDis DECIMAL(18, 2) ,
              DiscountValue DECIMAL(18, 2) ,
              ConsumeDiscountValue DECIMAL(18, 2) ,
              FoodDerateValue DECIMAL(18, 2) ,
              DerateValue DECIMAL(18, 2) ,
              Oddment DECIMAL(18, 2) ,
              GratisValue DECIMAL(18, 2) ,
              ScoreExchangeFoodAmount DECIMAL(18, 2) ,
              ScoreCompensateAmount DECIMAL(18, 2) ,
              PayAmount DECIMAL(18, 2) ,
              CustomerTimes DECIMAL(18, 2) ,
              OneTablePay DECIMAL(18, 2) ,
              NumberOfPersons DECIMAL(18, 2)
            )
            
        IF ( @CashierId IS NOT NULL
             AND @HourId IS NOT NULL
           ) 
            BEGIN
                INSERT  @MidTable
                        SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                SUM(ConsumeValue) ,
                                -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                -1
                                * SUM(CASE WHEN DiscountApproverId IS NULL
                                           THEN 0
                                           ELSE DiscountValue
                                                + ConsumeDiscountValue
                                      END) ,
                                -1
                                * SUM(CASE WHEN DiscountApproverId IS NULL
                                           THEN DiscountValue
                                                + ConsumeDiscountValue
                                           ELSE 0
                                      END) ,
                                -1
                                * SUM(CASE WHEN DerateApproverId IS NULL
                                           THEN 0
                                           ELSE DerateValue
                                      END) ,
                                -1 * SUM(DerateValue) ,
                                -1 * SUM(Oddment) ,
                                -1 * SUM(GratisValue) ,
                                -1 * SUM(ScoreExchangeFoodAmount) ,
                                -1 * SUM(ScoreCompensateAmount) ,
                                SUM(PayAmount) ,
                                COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                ( SUM(PayAmount)
                                  / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                        WHEN 0 THEN 1
                                        ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                      END ) ) ,
                                SUM(ISNULL(a.NumberOfPersons, 0))
                        
                          FROM @CheckOutBills AS a 
                                 WHERE a.BusinessHourId = @HourId
                                 AND a.CashierId  = @CashierId 
                                 AND IsCheckedOut = 1
                                 AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
				 
				 
				 	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND IsCheckedOut = 1
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
            END
        ELSE 
            IF ( @CashierId IS NOT NULL
                 AND @HourId IS NULL
               ) 
                BEGIN
                    INSERT  @MidTable
                            SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                    SUM(ConsumeValue) ,
                                    -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                    -1
                                    * SUM(CASE WHEN DiscountApproverId IS NULL
                                               THEN 0
                                               ELSE DiscountValue
                                                    + ConsumeDiscountValue
                                          END) ,
                                    -1
                                    * SUM(CASE WHEN DiscountApproverId IS NULL
                                               THEN DiscountValue
                                                    + ConsumeDiscountValue
                                               ELSE 0
                                          END) ,
                                    -1
                                    * SUM(CASE WHEN DerateApproverId IS NULL
                                               THEN 0
                                               ELSE DerateValue
                                          END) ,
                                    -1 * SUM(DerateValue) ,
                                    -1 * SUM(Oddment) ,
                                    -1 * SUM(GratisValue) ,
                                    -1 * SUM(ScoreExchangeFoodAmount) ,
                                    -1 * SUM(ScoreCompensateAmount) ,
                                    SUM(PayAmount) ,
                                    COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                    ( SUM(PayAmount)
                                      / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                            WHEN 0 THEN 1
                                            ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                          END ) ) ,
                                    SUM(ISNULL(a.NumberOfPersons, 0))
                         -- FROM Meal_V_Bills4GetBills AS a
                          FROM @CheckOutBills AS a 
                                 WHERE   a.CashierId  = @CashierId 
                                 AND IsCheckedOut = 1
                                 AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                      
                            
                   	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND IsCheckedOut = 1
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                                        
                END
            ELSE 
                IF ( @CashierId IS NULL
                     AND @HourId IS NOT NULL
                   ) 
                    BEGIN
                        INSERT  @MidTable
                                SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                        SUM(ConsumeValue) ,
                                        -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN 0
                                                   ELSE DiscountValue
                                                        + ConsumeDiscountValue
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN DiscountValue
                                                        + ConsumeDiscountValue
                                                   ELSE 0
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DerateApproverId IS NULL
                                                   THEN 0
                                                   ELSE DerateValue
                                              END) ,
                                        -1 * SUM(DerateValue) ,
                                        -1 * SUM(Oddment) ,
                                        -1 * SUM(GratisValue) ,
                                        -1 * SUM(ScoreExchangeFoodAmount) ,
                                        -1 * SUM(ScoreCompensateAmount) ,
                                        SUM(PayAmount) ,
                                        COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                        ( SUM(PayAmount)
                                          / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                                WHEN 0 THEN 1
                                                ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                              END ) ) ,
                                        SUM(ISNULL(a.NumberOfPersons, 0))
                           FROM @CheckOutBills AS a 
                                 WHERE a.BusinessHourId = @HourId
                                 AND IsCheckedOut = 1
                                  AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                         
                                       
                                        
                  	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND IsCheckedOut = 1
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                                        
                    END
                ELSE 
                    BEGIN
                        INSERT  @MidTable
                                SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                        SUM(ConsumeValue) ,
                                        -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN 0
                                                   ELSE DiscountValue
                                                        + ConsumeDiscountValue
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN DiscountValue
                                                        + ConsumeDiscountValue
                                                   ELSE 0
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DerateApproverId IS NULL
                                                   THEN 0
                                                   ELSE DerateValue
                                              END) ,
                                        -1 * SUM(DerateValue) ,
                                        -1 * SUM(Oddment) ,
                                        -1 * SUM(GratisValue) ,
                                        -1 * SUM(ScoreExchangeFoodAmount) ,
                                        -1 * SUM(ScoreCompensateAmount) ,
                                        SUM(PayAmount) ,
                                        COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                        ( SUM(PayAmount)
                                          / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                                WHEN 0 THEN 1
                                                ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                              END ) ) ,
                                        SUM(ISNULL(a.NumberOfPersons, 0))
                          
                          FROM @CheckOutBills AS a 
                                 WHERE  IsCheckedOut = 1
                                 AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                            
                            
                   	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND IsCheckedOut = 1
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                               
                                        
                    END
        
     
        --计算平台外卖到账金额
		 DECLARE @TakeAwayMoney DECIMAL (9,2)
					  SELECT  @TakeAwayMoney= SUM(ISNULL(TakeAwayPlatPayShopMoney,0))
					  FROM      dbo.Meal_PlatTakeAwayBillsDispose
					  WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
								AND BusinessHourNo LIKE @BusinessHourNo
								AND OperatorNo LIKE @CashierNo
								AND IsInMealReversedBillsTable=0
								AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
            IF(@PlatOrderFoodDiscount IS null)
            SET @PlatOrderFoodDiscount=0
	    INSERT  @returntable
                SELECT  SundryNo ,
                        SundryName ,
                        SUM(Volumes) AS Volumes
                FROM    (
                          SELECT    '01' AS SundryNo ,
                                    '*消费总额' AS SundryName ,
                                    (ISNULL(VolumeOfConsumption,0) +ISNULL(@PlatOrderFoodMoney,0) ) AS Volumes--消费+外卖菜例金额
                          FROM      @MidTable AS a
                          WHERE     ISNULL(VolumeOfConsumption,0)<> 0 OR ISNULL(@PlatOrderFoodMoney,0)<>0
                          UNION ALL
                          SELECT    '02' AS SundryNo ,
                                    '*优惠总额' AS SundryName ,
                                    ISNULL(SUM(FoodClubDis)+SUM(DiscountValue)+SUM(ConsumeDiscountValue)+SUM(FoodDerateValue)
                                    -@PlatOrderFoodDiscount+ SUM(Oddment)+SUM(GratisValue)+SUM(ScoreExchangeFoodAmount)
                                    +SUM(ScoreCompensateAmount),0)AS Volumes
                          FROM      @MidTable AS a
                          UNION ALL
                          SELECT    '025' AS SundryNo ,
                                    ' 会员优惠' AS SundryName ,
                                    FoodClubDis AS Volumes
                          FROM      @MidTable AS a
                          WHERE     FoodClubDis <> 0
                          UNION ALL
                          SELECT    '03' AS SundryNo ,
                                    ' 批准折扣' AS SundryName ,
                                    DiscountValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     DiscountValue <> 0
                          UNION ALL
                          SELECT    '042' AS SundryNo ,
                                    ' 会员折扣' AS SundryName ,
                                    ConsumeDiscountValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ConsumeDiscountValue <> 0
                        
                          UNION ALL
                          SELECT    '043' AS SundryNo ,
                                    ' 单品折扣' AS SundryName ,
                                    FoodDerateValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     FoodDerateValue <> 0
                          UNION ALL 
                          SELECT '044' AS SundryNo ,
                                    ' 平台折扣' AS SundryName ,
                                    0 - isnull(@PlatOrderFoodDiscount,0)
                                    WHERE isnull(@PlatOrderFoodDiscount,0)<>0
                          UNION ALL
                          SELECT    '06' AS SundryNo ,
                                    '     抹零' AS SundryName ,
                                    Oddment AS Volumes
                          FROM      @MidTable AS a
                          WHERE     Oddment <> 0
                          UNION ALL
                          SELECT    '07' AS SundryNo ,
                                    '     赠送' AS SundryName ,
                                    GratisValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     GratisValue <> 0
                          UNION ALL
                          SELECT    '071' AS SundryNo ,
                                    ' 积分兑换' AS SundryName ,
                                    ScoreExchangeFoodAmount AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ScoreExchangeFoodAmount <> 0
                          UNION ALL
                          SELECT    '072' AS SundryNo ,
                                    ' 积分抵扣' AS SundryName ,
                                    ScoreCompensateAmount AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ScoreCompensateAmount <> 0
                          UNION ALL
                          SELECT    '08' AS SundryNo ,
                                    '*实收金额' AS SundryName ,
                                    (ISNULL(PayAmount,0)+ISNULL(@TakeAwayMoney,0) )AS Volumes--实收+外卖平台到账
                          FROM      @MidTable AS a
                          WHERE     ISNULL(PayAmount,0) <> 0 OR ISNULL(@TakeAwayMoney,0)<>0
                          UNION ALL
                          SELECT    '09' AS SundryNo ,
                                    '*客次' AS SundryName ,
                                    CustomerTimes+ISNULL(@PlatOrderCustomerTimers,0)
                          FROM      @MidTable AS a
                          WHERE     CustomerTimes <> 0
                          UNION ALL
                          SELECT    '11' AS SundryNo ,
                                    '*客单' AS SundryName ,
                                    OneTablePay 
                          FROM      @MidTable AS a
                          WHERE     OneTablePay <> 0
                          UNION ALL
                          SELECT    '10' AS SundryNo ,
                                    '*就餐人数' AS SundryName ,
                                    NumberOfPersons+ISNULL(@PlatOrderCustomerTimers,0)
                          FROM      @MidTable AS a
                          WHERE     NumberOfPersons <> 0
                          UNION ALL
                          SELECT    '15' AS SundryNo ,
                                    '*退菜金额' AS SundryName ,
                                    SUM(Charge4Food) AS Volumes
                          FROM      dbo.Meal_V_OrderFoods
                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                    AND DiningAreaNo LIKE @DiningAreaN0
                                    AND BusinessHourNo LIKE @BusinessHourNo
                                    AND CashierNo LIKE @CashierNo
                                    AND IsReturnSale = 1
                          GROUP BY  DiningAreaNo ,
                                    DiningAreaName ,
                                    BusinessDate ,
                                    BusinessHourNo ,
                                    BusinessHourName ,
                                    CashierNo ,
                                    CashierName
                          UNION ALL
                          SELECT    '15' AS SundryNo ,
                                    '*退菜金额' AS SundryName ,
                                    SUM(RealDues) AS Volumes
                          FROM      dbo.Meal_SpeFoodSaleCollections
                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                    AND BusinessHourNo LIKE @BusinessHourNo
                                    AND CashierNo LIKE @CashierNo
                                    AND SpeType = 1
                          GROUP BY  BusinessDate ,
                                    BusinessHourNo ,
                                    BusinessHourName ,
                                    CashierNo ,
                                    CashierName
                        ) a
                GROUP BY SundryNo ,
                        SundryName
                ORDER BY SundryNo
               
                
--如果有外带或外卖则分别统计(堂食，外卖，外带 针对消费总额和实收总额的处理)-----------------
        DECLARE @TakeAwayFlag INT
        SET @TakeAwayFlag = 0
        SELECT  @TakeAwayFlag = COUNT(SaleType)
        FROM    dbo.Meal_V_Bills4GetBills
        WHERE   BusinessDate BETWEEN @BeginDate AND @EndDate
                AND DiningAreaNo LIKE @DiningAreaN0
                AND BusinessHourNo LIKE @BusinessHourNo
                AND CashierNo LIKE @CashierNo
                AND IsCheckedOut = 1
                AND TakeAwayPlatTypeId IN ( 2, 4 ,5,6,7,8,9,10,11,12,13)
       
        IF @TakeAwayFlag > 0 --则首先生成堂食数据
            BEGIN
                INSERT  @returntable
                        SELECT  SundryNo ,
                                SundryName ,
                                SUM(Volumes) AS Volumes
                        FROM    ( SELECT    BusinessDate ,
                                            DiningAreaNo ,
                                            DiningAreaName ,
                                            BusinessHourNo ,
                                            BusinessHourName ,
                                            CashierNo ,
                                            CashierName ,
                                            '011' AS SundryNo ,
                                            '     堂食' AS SundryName ,
                                            SUM(ISNULL(VolumeOfConsumption, 0)) AS Volumes--消费合计
                                  FROM      dbo.Meal_V_Bills4GetBills
                                  WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                            AND DiningAreaNo LIKE @DiningAreaN0
                                            AND BusinessHourNo LIKE @BusinessHourNo
                                            AND CashierNo LIKE @CashierNo
                                            AND IsCheckedOut = 1
                                            AND TakeAwayPlatTypeId = 1
                                  GROUP BY  DiningAreaNo ,
                                            DiningAreaName ,
                                            BusinessDate ,
                                            BusinessHourNo ,
                                            BusinessHourName ,
                                            CashierNo ,
                                            CashierName
                                  HAVING    SUM(ISNULL(VolumeOfConsumption, 0)) <> 0
                                  UNION ALL
                                  SELECT    BusinessDate ,
                                            DiningAreaNo ,
                                            DiningAreaName ,
                                            BusinessHourNo ,
                                            BusinessHourName ,
                                            CashierNo ,
                                            CashierName ,
                                            '081' AS SundryNo ,
                                            '     堂食' AS SundryName ,
                                            SUM(PayAmount) AS Volumes--堂食实付
                                  FROM      dbo.Meal_V_Bills4GetBills
                                  WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                            AND DiningAreaNo LIKE @DiningAreaN0
                                            AND BusinessHourNo LIKE @BusinessHourNo
                                            AND CashierNo LIKE @CashierNo
                                            AND IsCheckedOut = 1
                                            AND TakeAwayPlatTypeId = 1
                                  GROUP BY  DiningAreaNo ,
                                            DiningAreaName ,
                                            BusinessDate ,
                                            BusinessHourNo ,
                                            BusinessHourName ,
                                            CashierNo ,
                                            CashierName
                                  HAVING    SUM(PayAmount) <> 0
                                ) AS a
                        GROUP BY SundryNo ,
                                SundryName
                        ORDER BY SundryNo
                --  处理外卖
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    dbo.Meal_V_Bills4GetBills
                WHERE   BusinessDate BETWEEN @BeginDate AND @EndDate
                        AND DiningAreaNo LIKE @DiningAreaN0
                        AND BusinessHourNo LIKE @BusinessHourNo
                        AND CashierNo LIKE @CashierNo
                        AND IsCheckedOut = 1
                        AND TakeAwayPlatTypeId IN  (2,8,9)
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,
                                                    DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName ,
                                                    '014' AS SundryNo ,
                                                    '     外卖' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      dbo.Meal_V_Bills4GetBills
                                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                                    AND DiningAreaNo LIKE @DiningAreaN0
                                                    AND BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo
                                                    AND IsCheckedOut = 1
                                                    AND TakeAwayPlatTypeId IN( 2,9)
                                          GROUP BY  DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,
                                                    DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName ,
                                                    '084' AS SundryNo ,
                                                    '     外卖' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      dbo.Meal_V_Bills4GetBills
                                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                                    AND DiningAreaNo LIKE @DiningAreaN0
                                                    AND BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo
                                                    AND IsCheckedOut = 1
                                                    AND TakeAwayPlatTypeId IN( 2,9)
                                          GROUP BY  DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                        
                    END  
                    --处理外带
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    dbo.Meal_V_Bills4GetBills
                WHERE   BusinessDate BETWEEN @BeginDate AND @EndDate
                        AND DiningAreaNo LIKE @DiningAreaN0
                        AND BusinessHourNo LIKE @BusinessHourNo
                        AND CashierNo LIKE @CashierNo
                        AND IsCheckedOut = 1
                        AND TakeAwayPlatTypeId IN ( 4,7)
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,
                                                    DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName ,
                                                    '015' AS SundryNo ,
                                                    '     外带' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      dbo.Meal_V_Bills4GetBills
                                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                                    AND DiningAreaNo LIKE @DiningAreaN0
                                                    AND BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo
                                                    AND IsCheckedOut = 1
                                                    AND TakeAwayPlatTypeId IN ( 4)--店内外带，和手机订外带
                                          GROUP BY  DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,
                                                    DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName ,
                                                    '085' AS SundryNo ,
                                                    '     外带' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      dbo.Meal_V_Bills4GetBills
                                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                                    AND DiningAreaNo LIKE @DiningAreaN0
                                                    AND BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo
                                                    AND IsCheckedOut = 1
                                                    AND TakeAwayPlatTypeId IN ( 4)
                                          GROUP BY  DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    --扫码点餐处理
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    dbo.Meal_V_Bills4GetBills
                WHERE   BusinessDate BETWEEN @BeginDate AND @EndDate
                        AND DiningAreaNo LIKE @DiningAreaN0
                        AND BusinessHourNo LIKE @BusinessHourNo
                        AND CashierNo LIKE @CashierNo
                        AND IsCheckedOut = 1
                        AND TakeAwayPlatTypeId BETWEEN 5 AND 8 
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,
                                                    DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName ,
                                                    '012' AS SundryNo ,
                                                    '   好易点' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      dbo.Meal_V_Bills4GetBills
                                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                                    AND DiningAreaNo LIKE @DiningAreaN0
                                                    AND BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo
                                                    AND IsCheckedOut = 1
                                                    AND TakeAwayPlatTypeId BETWEEN 5 AND 8 
                                          GROUP BY  DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,
                                                    DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName ,
                                                    '082' AS SundryNo ,
                                                    '   好易点' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      dbo.Meal_V_Bills4GetBills
                                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                                    AND DiningAreaNo LIKE @DiningAreaN0
                                                    AND BusinessHourNo LIKE @BusinessHourNo
                                                    AND CashierNo LIKE @CashierNo
                                                    AND IsCheckedOut = 1
                                                    AND TakeAwayPlatTypeId  BETWEEN 5 AND 8 
                                          GROUP BY  DiningAreaNo ,
                                                    DiningAreaName ,
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    BusinessHourName ,
                                                    CashierNo ,
                                                    CashierName
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    
                
                    
                    --处理平台外卖
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(TakeAwayPlatPayShopMoney)
                FROM    dbo.Meal_PlatTakeAwayBillsDispose
                WHERE   BusinessDate BETWEEN @BeginDate AND @EndDate
                        AND BusinessHourNo LIKE @BusinessHourNo
                        AND OperatorNo LIKE @CashierNo
                        AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
                        
                DECLARE  @platOrderFoodMoney4All DECIMAL(9,2) 
                SET @platOrderFoodMoney4All=0
                --统计平台的菜例价格合计,用于消费合计统计
					SELECT @platOrderFoodMoney4All=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  dbo.Meal_V_Bills4GetBills AS b
                        WHERE   b.BusinessDate BETWEEN @BeginDate AND @EndDate
                                AND b.BusinessHourNo LIKE  @BusinessHourNo
                                AND IsCheckedOut = 1
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                               
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( 
                                         SELECT     '016' AS SundryNo ,
                                                    '     平台' AS SundryName ,
                                                    ISNULL(@platOrderFoodMoney4All,0) AS Volumes --平台菜例消费总额
                                          
                                          UNION ALL
                                          SELECT   
                                                    '086' AS SundryNo ,
                                                    '     平台' AS SundryName ,
                                                    SUM(ISNULL(TakeAwayPlatPayShopMoney,
                                                              0)) AS Volumes
                                          FROM      dbo.Meal_PlatTakeAwayBillsDispose
                                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                                    AND BusinessHourNo LIKE @BusinessHourNo
                                                    AND OperatorNo LIKE @CashierNo
                                                    AND IsInMealReversedBillsTable=0  AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,
                                                    OperatorNo
                                          HAVING    SUM(ISNULL(TakeAwayPlatPayShopMoney,
                                                              0)) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    
                    
                    
            END          
        DECLARE @realdues DECIMAL(18, 2)
        DECLARE @saletables DECIMAL(18, 2)
        DECLARE @salenum DECIMAL(18, 2)
        SET @realdues = ( SELECT    Volumes
                          FROM      @returntable
                          WHERE     SundryName = '*实收金额'
                        )
        SET @saletables = ( SELECT  Volumes
                            FROM    @returntable
                            WHERE   SundryName = '*客次'
                          )
        SET @salenum = ( SELECT Volumes
                         FROM   @returntable
                         WHERE  SundryName = '*就餐人数'
                       )
        
        IF ( @saletables IS NULL
             OR @saletables = 0
           ) 
            BEGIN
                SET @saletables = 1
            END
        IF ( @salenum IS NULL
             OR @salenum = 0
           ) 
            BEGIN
                SET @salenum = 1
            END 
        ------将就餐人数项改为人均消费
        INSERT  INTO @returntable
        VALUES  ( '15', '*人均消费', 0 )  
        IF NOT ( @realdues IS NULL ) 
            BEGIN
                UPDATE  @returntable
                SET     Volumes = @realdues / @saletables
                WHERE   SundryName = '*客单'
                UPDATE  @returntable
                SET     Volumes = @realdues / @salenum
                WHERE   SundryName = '*人均消费'
            END
        
        RETURN
    END

GO
-----------------------------------------------KC7.0.3T-----------------------------------------------
-----------------------------------------------交班功能设置，竖屏机设置项-----------------------------------------------
IF NOT EXISTS ( SELECT  * FROM    Meal_RouterProxy WHERE   ParamCode  = 'GoodPeopleBranch' ) 
INSERT INTO Meal_RouterProxy
VALUES('GoodPeopleBranch','获取好会员商户id','System.String','api-app/v1/shop/md5id/','获取好会员商户id')
GO

IF NOT EXISTS ( SELECT  * FROM    Meal_RouterProxy WHERE   ParamCode  = 'GoodPeopleSearch4AutoOrder' ) 
INSERT INTO Meal_RouterProxy
VALUES('GoodPeopleSearch4AutoOrder','储值会员查询（点餐机）','System.String','api-app/v1/prepaid/user','储值会员查询（点餐机）')
GO

IF NOT EXISTS ( SELECT  * FROM    Meal_RouterProxy WHERE   ParamCode  = 'GoodPeopleRight' ) 
INSERT INTO dbo.Meal_RouterProxy
VALUES  ( 'GoodPeopleRight' , '查询全部会员权益' , 'System.String' ,'api-app/v1/level/uid/' ,'查询全部会员权益')
GO
        
IF NOT EXISTS ( SELECT  * FROM    Meal_RouterProxy WHERE   ParamCode  = 'FoodPromotionOnLine' ) 
INSERT INTO dbo.Meal_RouterProxy VALUES  ( 'FoodPromotionOnLine' ,'线上会员价' ,'System.String' ,'api-app/v1/shop/food-promotion' ,'线上会员价')
GO

IF NOT EXISTS ( SELECT  * FROM    Meal_RouterProxy WHERE   ParamCode  = 'multiPay' ) 
INSERT INTO dbo.Meal_RouterProxy VALUES  ( 'multiPay' ,'好会员支付' ,'System.String' ,'api-app/v1/pay/multi-pay' ,N'好会员支付')
GO

IF NOT EXISTS ( SELECT  * FROM    Meal_RouterProxy WHERE   ParamCode  = 'GoodPeoplePayRules' ) 
INSERT INTO dbo.Meal_RouterProxy VALUES  ( 'GoodPeoplePayRules' ,'好会员储值规则' ,'System.String' ,'api-app/v1/uid/' ,'好会员储值规则')
GO
IF NOT EXISTS ( SELECT  * FROM    Meal_RouterProxy WHERE   ParamCode  = 'GoodPeopleReCharge' ) 
INSERT INTO dbo.Meal_RouterProxy VALUES  ( 'GoodPeopleReCharge' ,'好会员充值' ,'System.String' ,'api-app/v1/uid/' ,'好会员充值')
GO
IF NOT  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Meal_GoodPeopleCheckMessageStatus]') AND type IN (N'U'))
CREATE TABLE [dbo].[Meal_GoodPeopleCheckMessageStatus](
	[Id] [UNIQUEIDENTIFIER] NOT NULL,
	[BillId] [UNIQUEIDENTIFIER] NULL,
	[BillNo] [VARCHAR](50) NULL,
	[GoodPeopleOnLineId] [VARCHAR](50) NULL,
	[FoodCharge] [DECIMAL](9, 2) NULL,
	[Status] [INT] NULL,
	[OccurDate] [DATETIME] NULL,
	[BillUpStatus][INT] NULL,
 CONSTRAINT [PK_Meal_GoodPeopleCheckMessageStatus] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

IF NOT EXISTS ( SELECT  *
                FROM    Meal_EnvironmentSettings 
                WHERE   ParamCode  = 'AntiFake' ) 
   INSERT INTO Meal_EnvironmentSettings 
           ( ParamCode ,
             ParamName ,
             ParamDataType ,
             ParamValue ,
             Description
           )
   VALUES  ( 'AntiFake' ,
             N'防伪字符' ,
             N'System.String' , 
             N'' , 
             N'领取菜品凭证单打印此防伪字符' 
           )
GO
--DELETE  Meal_EnvironmentSettings 
--                WHERE   ParamCode  = 'UseWorkShift' 

IF NOT EXISTS ( SELECT  *
                FROM    Meal_EnvironmentSettings 
                WHERE   ParamCode  = 'UseWorkShift' ) 
	BEGIN
	
   INSERT INTO Meal_EnvironmentSettings 
           ( ParamCode ,
             ParamName ,
             ParamDataType ,
             ParamValue ,
             Description
           )
   VALUES  ( 'UseWorkShift' ,
             N'启用交班功能' ,
             N'System.String' , 
             N'Use' , 
             N'非24小时营业的店面可以不启用交班功能 USE 表示用，NOUSE表示不用' 
           )
END     
GO
-----------------------------------------------估清优化-----------------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Meal_GetEstimate4SaleFoodsCount]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Meal_GetEstimate4SaleFoodsCount]
GO
CREATE  PROCEDURE [dbo].[Meal_GetEstimate4SaleFoodsCount]
    @businessHourId INT,
    @businessHourNo VARCHAR(10) ,
    @beginTime DATETIME,
    @endTime DATETIME
AS
    BEGIN
        DECLARE  @FoodSaleListdtable TABLE 
            (
              FoodId UNIQUEIDENTIFIER ,
              Quantity [DECIMAL](9, 2)
            );  
        INSERT  INTO @FoodSaleListdtable--当前表插入
                SELECT  FoodId ,
                        ISNULL(SUM(Quantity),0)
                FROM    dbo.Meal_OrderFoods AS o ,
                        dbo.Meal_ArrangedTables AS a
                WHERE   o.CaptainsOrderId = a.Id
                        AND IsDeleted = 0
                        AND BusinessHourId = @businessHourId
                        AND DATEDIFF(DAY, BusinessDate, @beginTime) = 0
                GROUP BY FoodId;
        DECLARE  @EstimateListdtable TABLE
            (
              [Id] [UNIQUEIDENTIFIER] NOT NULL ,
              [FoodTypeId] [UNIQUEIDENTIFIER] NOT NULL ,
              FoodTypeNo NVARCHAR(2) NOT NULL ,
              FoodTypeName NVARCHAR(20) NOT NULL ,
              FoodId [UNIQUEIDENTIFIER] NOT NULL ,
              FoodNo NVARCHAR(8) NOT NULL ,
              FoodName NVARCHAR(50) NOT NULL ,
              Spell NVARCHAR(20) NULL ,
              Specification NVARCHAR(10) NOT NULL ,
              Price DECIMAL(9, 2) NOT NULL ,
              EstimateQuantity DECIMAL(9, 2) NOT NULL ,
              BusinessHourId INT NULL ,
              BusinessHourNo NVARCHAR(2) NOT NULL ,
              BusinessHourName NVARCHAR(20) NULL ,
              BusinessDate DATETIME NOT NULL ,
              Comment NVARCHAR(100)
            );  
        INSERT  INTO @EstimateListdtable
                SELECT  Id ,
                        FoodTypeId ,
                        FoodTypeNo ,
                        FoodTypeName ,
                        FoodId ,
                        FoodNo ,
                        FoodName ,
                        Spell ,
                        Specification ,
                        Price ,
                        EstimateQuantity ,
                        BusinessHourId ,
                        BusinessHourNo ,
                        BusinessHourName ,
                        BusinessDate ,
                        Comment
                FROM    dbo.Meal_V_EstimateFoods
                WHERE   ( BusinessHourNo = @businessHourNo )
                        AND ( BusinessDate BETWEEN @beginTime
                                           AND     @endTime )
                ORDER BY BusinessDate DESC ,
                        BusinessHourNo ,
                        FoodTypeNo ,
                        FoodNo;
        SELECT  Id ,
                FoodTypeId ,
                FoodTypeNo ,
                FoodTypeName ,
                a.FoodId ,
                FoodNo ,
                FoodName ,
                Spell ,
                Specification ,
                Price ,
                ISNULL(EstimateQuantity,0) AS EstimateQuantity,
                ISNULL(f.Quantity,0) AS SaleQuantity ,
                ISNULL(EstimateQuantity - ISNULL(f.Quantity,0),0) AS RemainQuantity ,
                BusinessHourId ,
                BusinessHourNo ,
                BusinessHourName ,
                BusinessDate ,
                Comment
        FROM    @EstimateListdtable AS a
                LEFT JOIN @FoodSaleListdtable AS f ON a.FoodId = f.FoodId;

        RETURN; 
    END;
GO
-----------------------------------------------大唐智盘-----------------------------------------------
IF NOT EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[Meal_CorrelationFoodAndPlate]')
                    AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) 
    CREATE TABLE [dbo].[Meal_CorrelationFoodAndPlate]
        (
          [Id] [int] IDENTITY(1, 1) NOT NULL ,
          [FoodId] [uniqueidentifier] NOT NULL ,
          [PlateUId] [nvarchar](50) NOT NULL ,
          CONSTRAINT [PK_Meal_CorrelationFoodAndPlate] PRIMARY KEY CLUSTERED
            ( [Id] ASC ) ON [PRIMARY]
        )
    ON  [PRIMARY]
GO
IF NOT EXISTS(SELECT * FROM dbo.Empowerment_Actions
WHERE ControlName = 'SignServicePlate')
INSERT dbo.Empowerment_Actions
        ( Id ,
          No ,
          ControlName ,
          ActionName ,
          AssemblyName ,
          OperateModule ,
          PriorLevelId ,
          Comment ,
          HopesoftBusiness ,
          Tag
        )
VALUES  ( '16A95576-FF0E-4B26-9FD4-B4444809BB17' , -- Id - uniqueidentifier
          '00200' , -- No - varchar(5)
          'SignServicePlate' , -- ControlName - varchar(255)
          N'标记餐盘' , -- ActionName - nvarchar(50)
          N'BaseInfo' , -- AssemblyName - nvarchar(255)
          N'BaseInfo' , -- OperateModule - nvarchar(50)
          '082B0575-2704-4837-9FB4-61CA8B31EC11', -- PriorLevelId - uniqueidentifier
          N'All' , -- Comment - nvarchar(255)
          0 , -- HopesoftBusiness - tinyint
          N''  -- Tag - nvarchar(10)
        )
GO
-----------------------------------------------对接膳易-----------------------------------------------
IF NOT EXISTS ( SELECT  *
                FROM    dbo.Meal_EnvironmentSettings
                WHERE   ParamCode = 'ErpShanYiAddress' )
    BEGIN 
        INSERT  INTO Meal_EnvironmentSettings
                ( ParamCode ,
                  ParamName ,
                  ParamDataType ,
                  ParamValue ,
                  Description
                )
        VALUES  ( 'ErpShanYiAddress' ,
                  '膳易供应链通讯地址' ,
                  'System.String' ,
                  '' ,
                  ''
                )
    END
GO

IF NOT EXISTS ( SELECT  *
                FROM    dbo.Meal_EnvironmentSettings
                WHERE   ParamCode = 'ErpShanYiOrgNo' )
    BEGIN 
        INSERT  INTO Meal_EnvironmentSettings
                ( ParamCode ,
                  ParamName ,
                  ParamDataType ,
                  ParamValue ,
                  Description
                )
        VALUES  ( 'ErpShanYiOrgNo' ,
                  '膳易供应链商户号' ,
                  'System.String' ,
                  '' ,
                  ''
                )
    END
GO

IF NOT EXISTS ( SELECT  *
                FROM    dbo.Meal_EnvironmentSettings
                WHERE   ParamCode = 'ErpShanYiBrandNo' )
    BEGIN 
        INSERT  INTO Meal_EnvironmentSettings
                ( ParamCode ,
                  ParamName ,
                  ParamDataType ,
                  ParamValue ,
                  Description
                )
        VALUES  ( 'ErpShanYiBrandNo' ,
                  '膳易供应链门店号' ,
                  'System.String' ,
                  '' ,
                  ''
                )
    END
GO
--增加菜品销售统计Meal_ufnErpShanYiOrderFoods

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[Meal_ufnErpShanYiOrderFoods]')
                    AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ) )
    DROP FUNCTION [dbo].[Meal_ufnErpShanYiOrderFoods]
GO
CREATE FUNCTION [dbo].[Meal_ufnErpShanYiOrderFoods]    --一段时期内的菜例销售统计
    (
      @beginBusinessDate DATETIME ,
      @endBusinessDate DATETIME 
    )
RETURNS @returntable TABLE
    (
      FoodId UNIQUEIDENTIFIER NOT NULL ,  --菜品或与部门联合主键
      DepartmentId UNIQUEIDENTIFIER NOT NULL ,  --菜例制作部门
      DepartmentNo VARCHAR(3) NOT NULL ,
      DepartmentName NVARCHAR(50) NOT NULL ,
      FoodTypeId UNIQUEIDENTIFIER NOT NULL ,
      FoodTypeNo VARCHAR(2) NOT NULL ,
      FoodTypeName NVARCHAR(20) NOT NULL ,
      FoodNo VARCHAR(8) NOT NULL ,
      FoodName NVARCHAR(50) NOT NULL ,
      Specification NVARCHAR(10) NOT NULL ,
      SaleQuantity NUMERIC(9, 2) NOT NULL ,
      Price NUMERIC(9, 2) NOT NULL ,
      saleDues4Food NUMERIC(12, 2) NOT NULL ,
      saleCharge4Food NUMERIC(12, 2) NOT NULL
    )
AS
    BEGIN
        SET @endBusinessDate = DATEADD(SECOND, -30, @endBusinessDate)
        INSERT  @returntable
                SELECT  FoodId ,
                        DepartmentId ,
                        DepartmentNo ,
                        f.DepartmentName ,
                        FoodTypeId ,
                        FoodTypeNo ,
                        FoodTypeName ,    --菜类
                        FoodNo ,
                        FoodName ,
                        f.Specification ,
                        SUM(Quantity) AS sumQuantity ,       --数量
                        f.Price ,
                        SUM(Dues4Food) AS sumDues4Food ,--金额
                        SUM(Charge4Food) AS sumCharge4Food  --金额（折后，赠送为）                                     
                FROM    dbo.Meal_V_AllOrderFoods AS f
                        LEFT JOIN dbo.DiningAreas AS a ON a.Id = f.DiningAreaId
                        LEFT JOIN Departments AS d ON a.BarId = d.Id
                WHERE   ( f.BusinessDate BETWEEN @beginBusinessDate
                                         AND     @endBusinessDate )
                        AND f.IsCheckedOut = 1
                GROUP BY f.DepartmentId ,
                        f.DepartmentNo ,
                        f.DepartmentName ,--制作部门
                        FoodTypeId ,
                        FoodTypeNo ,
                        FoodTypeName ,    --菜类
                        FoodId ,
                        FoodNo ,
                        FoodName ,
                        f.Specification ,
                        f.Price
                HAVING  SUM(Quantity) > 0                 
                
        RETURN 
    END


GO
--基本信息增加权限
IF NOT EXISTS ( SELECT  *
                FROM    [dbo].[Empowerment_Actions]
                WHERE   ControlName = 'ErpShanYiSetting' )
    INSERT  INTO Empowerment_Actions
            ( Id ,
              No ,
              ControlName ,
              ActionName ,
              AssemblyName ,
              OperateModule ,
              PriorLevelId ,
              Comment
            )
    VALUES  ( '126893BC-338F-4442-9F69-45806469F97F' ,
              '00028' ,
              'ErpShanYiSetting' ,
              '供应链设置' ,
              'BaseInfo' ,
              'BaseInfo' ,
              '27F9BEA3-1A4B-4478-84D7-C22BFBAEAE91' ,
              'All'
            )
GO

-----------------------------------------------20191105(7.0.4T)-----------------------------------------------
----------------------------------修改注册-------------------------------------
ALTER PROCEDURE [dbo].[RegisterPhysicalAddress] 
	@flag int --1 注册 0 是获取信息
AS 
  DECLARE @midtable TABLE
            (
              id INT IDENTITY(1, 1) ,
              info NVARCHAR(4000)
            )
INSERT  INTO @midtable
EXEC xp_cmdshell 'ipconfig /all'; 

declare @returnstring nvarchar(4000)
set @returnstring=''
if (@flag = 0)
begin
	declare m_cursor cursor scroll for
	select  distinct info from  @midtable
	WHERE info LIKE '%物理地址%' 
	AND info not like '%00-00-00-00-00-00-00-E0%' 
	open m_cursor
	declare @ip varchar(500)
	fetch next from m_cursor into @ip
	while @@FETCH_STATUS=0
	begin
		if @ip is not null 
		begin 
			set @returnstring=@returnstring+@ip
	
		end
		
    fetch next from m_cursor into @ip
	end
	close m_cursor
	deallocate m_cursor
end
else
begin
	declare @id int
	set @id = (select top 1 id from @midtable where info like '%以太网适配器%' or info like '%Ethernet adapter%')


	set @returnstring = (select top 1 info from @midtable where id > @id 
							AND (info like '%物理地址%' or info like '%Physical Address%') 
							AND info not like '%00-00-00-00-00-00-00-E0%')
end
 DECLARE @returnTable TABLE
            (
              info NVARCHAR(4000)
            )
            insert into @returnTable
            select  @returnstring 
            
            select info from  @returnTable
GO
------------------------------------------20191231(7.0.5T)------------------------------------------
------------------------------------------标签打印时间不对 ------------------------------------------
UPDATE dbo.Meal_SaveModel
SET ItemName='datatime'
WHERE ModelName='标签纸40X20'AND  ItemName='datetime'

------------------------------------------20200330(7.0.6T)------------------------------------------
------------------------------------------对接V2支付------------------------------------------
IF NOT EXISTS ( SELECT  *
                FROM    dbo.HotelEnvironmentSettings
                WHERE   ParamCode = 'ZFVersion' ) 
    BEGIN
        INSERT  INTO dbo.HotelEnvironmentSettings
                ( ParamCode ,
                  ParamName ,
                  ParamDataType ,
                  ParamValue ,
                  Description
                )
        VALUES  ( 'ZFVersion' ,
                  N'支付版本号' ,
                  N'System.String' ,
                  N'V2' ,
                  N'支付版本号'  
                )
    END
GO
IF NOT EXISTS(SELECT * FROM dbo.PaymentMethods WHERE PaymentMethodName = '手机支付')
BEGIN
  DECLARE @No Int
  DECLARE @CNo VARCHAR(2)
   SELECT @No = MAX(CAST(No AS INT))+1 FROM DBO.PaymentMethods
    
    SELECT @CNo = RIGHT(REPLICATE('0',2)+CAST(@No AS varchar(2)),2) FROM PaymentMethods
    
  INSERT INTO PaymentMethods (No,PaymentMethodName,Spell,IsInActive)
  VALUES(@CNo,'手机支付','SJZF',0)
END
GO
--------------正式地址
IF NOT EXISTS ( SELECT  *
                FROM    dbo.HotelEnvironmentSettings
                WHERE   ParamCode = 'ZFServerAddressV2' ) 
    BEGIN
        INSERT  INTO dbo.HotelEnvironmentSettings
                ( ParamCode ,
                  ParamName ,
                  ParamDataType ,
                  ParamValue ,
                  Description
                )
        VALUES  ( 'ZFServerAddressV2' ,
                  N'支付服务地址V2' ,
                  N'System.String' ,
                  N'http://www.hocyun.com:9073' ,
                  N''  
                )
    END
GO
IF NOT EXISTS ( SELECT  *
                FROM    Meal_RouterProxy
                WHERE   paramcode = 'MobilePayList' ) 
    BEGIN
        INSERT  INTO Meal_RouterProxy
                ( paramcode ,
                  paramname ,
                  paramdatatype ,
                  paramvalue ,
                  description
                )
        VALUES  ( 'MobilePayList' ,
                  '移动支付列表' ,
                  'System.String' ,
                  'https://www.hocyun.com/index.php/knockout/pay/orders' ,
                  '移动支付列表'
                )
    END
GO
------------------------------------------打印汇总单调整------------------------------------------
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MobilePay_ufnSumMobilePayReport]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[MobilePay_ufnSumMobilePayReport]
GO
CREATE FUNCTION [dbo].[MobilePay_ufnSumMobilePayReport]  --移动支付日报表
    (
      @beginDate DATETIME , --开始时间
      @endDate DATETIME   --结束时间
    )
RETURNS @returntable TABLE
    (
      sumTotalAmount DECIMAL(11, 2) NULL ,  --移动支付订单总额
      sumReceiptAmount DECIMAL(11, 2) NULL , --商户实收
      sumBuyerPayAmount DECIMAL(11, 2) NULL , --顾客实付
      sumSellerDiscount DECIMAL(11, 2) NULL , --商家折扣
      PayType NVARCHAR(20) NULL ,  ----支付宝收款次数
      Operate NVARCHAR(20) NULL ,----操作类型
      Times INT NULL  ----次数
    )
AS 
    BEGIN
        INSERT  @returntable
                SELECT  ISNULL(SUM(PayAmount),0) AS sumTotalAmount ,
                        ISNULL(SUM(ReceiptAmount),0) AS sumReceiptAmount ,
                        ISNULL(SUM(BuyerPayAmount),0) AS sumBuyerPayAmount ,
                        ISNULL(SUM(SellerDiscount),0) AS sumSellerDiscount ,
                        'ALIPAY' AS PayType ,
                        '支付宝[收]' AS Operate ,
                        ISNULL(COUNT(0),0) AS Times
                FROM    MobilePaymentLogs
                WHERE AiliPayDateTime BETWEEN @beginDate AND @endDate AND PayType='支付宝刷卡'AND Status IN (100,-100)
         INSERT  @returntable
                SELECT  ISNULL(SUM(PayAmount),0) AS sumTotalAmount ,
                        ISNULL(SUM(ReceiptAmount),0) AS sumReceiptAmount ,
                        ISNULL(SUM(BuyerPayAmount),0) AS sumBuyerPayAmount ,
                        ISNULL(SUM(SellerDiscount),0) AS sumSellerDiscount ,
                        'ALIPAY' AS PayType ,
                        '支付宝[退]' AS Operate ,
                        ISNULL(COUNT(0),0) AS Times
                FROM    MobilePaymentLogs
                WHERE AiliPayDateTime BETWEEN @beginDate AND @endDate AND PayType='支付宝刷卡'AND Status=-100
        INSERT  @returntable
                SELECT  ISNULL(SUM(PayAmount),0) AS sumTotalAmount ,
                        ISNULL(SUM(ReceiptAmount),0) AS sumReceiptAmount ,
                        ISNULL(SUM(BuyerPayAmount),0) AS sumBuyerPayAmount ,
                        ISNULL(SUM(SellerDiscount),0) AS sumSellerDiscount ,
                        'WECHAT' AS PayType ,
                        '微信[收]' AS Operate ,
                        ISNULL(COUNT(0),0) AS Times
                FROM    MobilePaymentLogs
                WHERE AiliPayDateTime BETWEEN @beginDate AND @endDate AND PayType='微信刷卡'AND Status IN (100,-100)
         INSERT  @returntable
                SELECT  ISNULL(SUM(PayAmount),0) AS sumTotalAmount ,
                        ISNULL(SUM(ReceiptAmount),0) AS sumReceiptAmount ,
                        ISNULL(SUM(BuyerPayAmount),0) AS sumBuyerPayAmount ,
                        ISNULL(SUM(SellerDiscount),0) AS sumSellerDiscount ,
                        'WECHAT' AS PayType ,
                        '微信[退]' AS Operate ,
                        ISNULL(COUNT(0),0) AS Times
                FROM    MobilePaymentLogs
                WHERE AiliPayDateTime BETWEEN @beginDate AND @endDate AND PayType='微信刷卡'AND Status=-100
        
        RETURN 
    END

GO


/****** Object:  StoredProcedure [dbo].[Meal_CancelABill]    Script Date: 06/01/2020 14:29:31 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Meal_CancelABill]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Meal_CancelABill]
GO

----------------------------
CREATE PROCEDURE [dbo].[Meal_CancelABill]
    @billId UNIQUEIDENTIFIER ,   --账单Id
    @reverseReason NVARCHAR(255) , --冲账原因
    @operatorId UNIQUEIDENTIFIER,  --操作员Id	
    @type BIT = 1  ---1原作废流程 0返位到堂食
AS 
    DECLARE @clubCardLog UNIQUEIDENTIFIER
    DECLARE @billNo NVARCHAR(13)
    DECLARE @cardNo NVARCHAR(15)         --会员卡
    DECLARE @SavingAmount DECIMAL(9, 2)  --会员卡储值处理
    DECLARE @Score DECIMAL(9, 2)  --积分处理
    DECLARE @scoreBizId UNIQUEIDENTIFIER
    DECLARE @errMsg NVARCHAR(2000)	  	
    DECLARE @OnTicksAmount MONEY
    DECLARE @enableTick BIT
    DECLARE @CustomerId UNIQUEIDENTIFIER
    DECLARE @RC INT
    DECLARE @ContractId UNIQUEIDENTIFIER
    DECLARE @IsPartPay BIT
    DECLARE @ErrorLogId INT              --错误Id
    DECLARE @ReturnScore DECIMAL(9, 2)
    
    IF NOT EXISTS ( SELECT  *
                    FROM    dbo.Meal_Bills
                    WHERE   Id = @billId ) 
        BEGIN
            RAISERROR(N'账单已经日结，不能作废',16,1)
            RETURN 0
        END
    DECLARE @localTrans BIT
    SET @localTrans = 0
    IF ( @@TRANCOUNT = 0 ) --如果是被外部调用，并且调用时外部已经建立事物，则使用外部事物，否则使用内部事物
        BEGIN
            BEGIN TRANSACTION
            SET @localTrans = 1
        END
	
	SET @IsPartPay = (SELECT ISNULL(IsPartPay,0) FROM dbo.Meal_Bills
    WHERE Id = @billId)
	
    IF EXISTS ( SELECT  Id
                FROM    dbo.Meal_PaymentList
                WHERE   BillId = @billId
                        AND ( PaymentMethodId = 5
                              AND PayAmount > 0
                            ) ) 
        BEGIN
            SELECT  @billNo = No , --账单号
                    @cardNo = ClubCardNo    --会员卡号
            FROM    dbo.Meal_bills
            WHERE   Id = @billId
		--会员卡储值使用记录,多次红冲，取最后一条记录
            DECLARE @savelogs TABLE
                (
                  clubCardLog UNIQUEIDENTIFIER ,
                  Clubcardno VARCHAR(15) ,
                  ConsumeNo VARCHAR(20) ,
                  OccurDateTime DATETIME
                )
		
            IF @cardNo IS NOT NULL        --使用会员卡
                BEGIN
                    SET @ReturnScore = 0		
		--积分
                    SELECT  @Score = AdjustmentValue    --积分记录,多次红冲，取最后一条记录
                    FROM    dbo.CRM_ScoreAdjustLogs
                    WHERE   BusinessType = 4      --餐饮消费
                            AND ConsumeNo = @billNo
                            AND ClubCardNo = @cardNo
                            AND ScoreAdjustmentid = 3
                    ORDER BY OccurDateTime    --DESC
                    SET @ReturnScore = -1 * @Score
                END
		
            INSERT  INTO @savelogs
                    SELECT  NEWID() ,
                            ClubCardNo ,
                            ConsumeNo ,
                            MAX(OccurDateTime) AS OccurDateTime
                    FROM    dbo.CRM_ClubCardSavingLogs
                    WHERE   BusinessType = 4      --餐饮消费
                            AND ConsumeNo = @billNo
                    GROUP BY ClubCardNo ,
                            ConsumeNo
            UPDATE  @savelogs
            SET     clubCardLog = c.Id
            FROM    @savelogs AS s
                    LEFT JOIN dbo.CRM_ClubCardSavingLogs AS c ON c.ConsumeNo = s.ConsumeNo
                                                              AND c.OccurDateTime = s.OccurDateTime
                                                              AND c.ClubCardNo = s.ClubCardNo
		
            DECLARE paylist_cursor CURSOR FORWARD_ONLY
            FOR
                SELECT  Clubcardno ,
                        clubCardLog
                FROM    @savelogs AS s
            OPEN paylist_cursor
            FETCH NEXT FROM paylist_cursor 
		INTO @cardNo, @clubCardLog
            WHILE @@FETCH_STATUS = 0 
                BEGIN
                    IF NOT EXISTS ( SELECT  *
                                    FROM    ClubCards
                                    WHERE   ( No = @cardNo )
                                            AND ( Status = 2 ) ) 
                        BEGIN
                            SET @errMsg = N'会员卡' + @cardNo + '已经退卡，单据不再能作废'
                            RAISERROR(@errMsg,16,1)
                            CLOSE paylist_cursor
                            DEALLOCATE paylist_cursor 
                            ROLLBACK TRANSACTION
                            RETURN 0 
                        END 
                    EXECUTE dbo.CRM_ReverseASavingLog @savingLogId = @clubCardLog   --红冲的Id
                        , @SavingAdjustmentId = 3               --储值变量原因:返位结算
                        , @reason = @reverseReason           --冲账原因；
                        , @BusinessType = 3                --BusinessType:餐饮返位
                        , @OperatorId = @operatorId               --操作员
                        , @ErrorLogId = @ErrorLogId OUTPUT    
                    IF @ErrorLogId IS NOT NULL 
                        BEGIN
                            SET @errMsg = N'会员卡' + @cardNo + '消费储值红冲时失败'
                            RAISERROR (@errMsg,  --11036
									16, -- Severity，通过异常处理11-19
									2 -- State.
									) -- Second substitution argument. 
                            CLOSE paylist_cursor
                            DEALLOCATE paylist_cursor 
                            ROLLBACK TRANSACTION
                            RETURN 0 
                        END  
                    FETCH NEXT FROM paylist_cursor 
			INTO @cardNo, @clubCardLog
                END
            CLOSE paylist_cursor
            DEALLOCATE paylist_cursor
        END
	-----将单据转入返位表
    EXECUTE dbo.Meal_CloneABill @billId, @reverseReason, @operatorId,
        @ErrorLogId OUTPUT	
    IF @ErrorLogId IS NOT NULL 
        BEGIN
            SET @errMsg = N'账单作废失败'
            RAISERROR (@errMsg,  
							16, -- Severity，通过异常处理11-19
							2 -- State.
							) -- Second substitution argument.   
        END  
    -------------------------------------好会员处理数据
   	--清除未上传好会员记录
    UPDATE  dbo.Meal_WhyUpdateMsg
    SET     IsUpdate = 1 ,
            IsReverse = 1 ,
            ReverseDatetime = dbo.GETDATE()
    WHERE   BillId = @billId
            AND IsUpdate = 0
            AND IsReverse = 0
            AND IsHappenErr = 0
            
	DECLARE @whyid TABLE
        (
          WhyId UNIQUEIDENTIFIER
        )
    INSERT  @whyid
            ( WhyId
            )
            SELECT  Id
            FROM    dbo.Meal_WhyCardPayMents
            WHERE   BillId = @billId
                    AND IsReverse = 0
	
    INSERT  dbo.Meal_WhyCardPayMents
            ( Id ,
              BillId ,
              CardNo ,
              CustomerName ,
              CardPossWord ,
              CustomerGrade ,
              point ,
              CustomerStore ,
              NeedPassWord ,
              CardNum ,
              CustomerMobile ,
              IsPay ,
              WshBillNo ,
              AddPoint ,
              IsDingYun ,
              InterfaceType ,
              totalBalance ,
              score ,
              deltaAmount ,
              consumeAmount ,
              deltaTimes ,
              consumeTimes ,
              lastDeltaDate ,
              lastConsumeDate ,
              validDate ,
              openChannel ,
              cardstatus ,
              statusRemark ,
              UseAmount ,
              PayMoney ,
              PayDateTime ,
              OperatorId ,
              IsReverse,
              ArrangedTableId,
              WhyPayNo
	        )
            SELECT  NEWID() ,
                    BillId ,
                    CardNo ,
                    CustomerName ,
                    CardPossWord ,
                    CustomerGrade ,
                    point ,
                    CustomerStore ,
                    NeedPassWord ,
                    CardNum ,
                    CustomerMobile ,
                    1 ,
                    WshBillNo ,
                    AddPoint ,
                    IsDingYun ,
                    InterfaceType ,
                    totalBalance + UseAmount ,
                    score ,
                    deltaAmount ,
                    consumeAmount ,
                    deltaTimes ,
                    consumeTimes ,
                    lastDeltaDate ,
                    lastConsumeDate ,
                    validDate ,
                    openChannel ,
                    cardstatus ,
                    statusRemark ,
                    0 ,
                    PayMoney ,
                    PayDateTime ,
                    OperatorId ,
                    0,
              ArrangedTableId,
              WhyPayNo
            FROM    dbo.Meal_WhyCardPayMents AS a ,
                    @whyid AS b
            WHERE   a.Id = b.WhyId
	
    UPDATE  dbo.Meal_WhyCardPayMents
    SET     IsReverse = 1
    FROM    dbo.Meal_WhyCardPayMents AS a ,
            @whyid AS b
    WHERE   a.Id = b.WhyId
	
    UPDATE  Meal_WhyUseCoupons
    SET     IsReverse = 1
    WHERE   BillId = @billId
    --积分减少
    IF ( @Score > 0 ) 
        BEGIN
            SET @Score = -1 * @Score 
            EXEC dbo.CRM_AdjustScore @CardNo = @cardNo,   --会员卡号   
                @ScoreAdjustmentId = 4,  --储值变动原因Id:返位
                @ScoreValue = @Score,  --变动值
                @BusinessType = 3,        --业务类别:餐饮返位
                @ConsumeNo = @billNo,       --业务单据号
                @OperatorId = @operatorId, --操作员
                @Comment = '餐饮返位',       --备注
                @bizId = @scoreBizId, --返回此次的业务Id 
                @ErrorLogId = @ErrorLogId OUTPUT
				
            IF @ErrorLogId IS NOT NULL 
                BEGIN
                    SET @errMsg = N'会员卡' + @cardNo + '积分红冲时失败,另见错误记录：'
                        + CAST(@ErrorLogId AS VARCHAR(10))
                    RAISERROR (@errMsg,  --11036
									16, -- Severity，通过异常处理11-19
									2 -- State.
									) -- Second substitution argument.   
                END  
        END 
        
        
	-----清除账单信息
	IF (@type = 1)---作废账单
		BEGIN
			DELETE  dbo.Meal_SerialNo
			WHERE   ArrangedTableId = @billid
			DELETE  dbo.Meal_BalanceSerialNo
			WHERE   BillId = @billid
			DELETE  dbo.Meal_PaymentList
			WHERE   BillId = @billid
			DELETE  dbo.Meal_OrderFoods
			WHERE   CaptainsOrderId IN ( SELECT Id
										 FROM   dbo.Meal_Meal_CaptainsOrders
										 WHERE  ArrangedTableId = @billid )
			DELETE  dbo.Meal_Meal_CaptainsOrders4Suits
			WHERE   Meal_CaptainsOrderId IN ( SELECT    Id
											  FROM      dbo.Meal_Meal_CaptainsOrders
											  WHERE     ArrangedTableId = @billid )
			DELETE  dbo.Meal_Meal_CaptainsOrders
			WHERE   Id IN ( SELECT  Id
							FROM    dbo.Meal_Meal_CaptainsOrders
							WHERE   ArrangedTableId = @billid )
			DELETE  dbo.Meal_ArrangedTables
			WHERE   id = @billid
			DELETE  dbo.Meal_Bills
			WHERE   id = @billid
		END
	ELSE  --返位
		BEGIN
			--设置原单据为未结账状态
		UPDATE dbo.Meal_Bills
		SET IsCheckedOut=0,ConsumeValue = 0,ConsumeDiscountValue = 0,
			payamount = payamount - ConsumeValue + ConsumeDiscountValue,
			ScoreCompensateAmount = 0,IsReturnSale = 1
		WHERE Id=@billId
		
		
		--修改付款明细
		IF (@IsPartPay = 0)--- 非部分付款的台位已付款标识设为零
		BEGIN
			UPDATE dbo.Meal_PaymentList SET IsPayed = 0
			WHERE BillId = @billId
		END
		
		
		
		 --已经付款的支付宝结账置为分次收款已结
		IF EXISTS ( SELECT  *
					FROM    dbo.Meal_PaymentList AS a ,
							dbo.PaymentMethods AS b
					WHERE   a.PaymentMethodId = b.Id
							AND b.PaymentMethodName = '支付宝'
							AND a.BillId = @billId
							AND a.IsPayed = 0 )
			BEGIN
				IF EXISTS ( SELECT  *
							FROM    dbo.Meal_AliPayLogs
							WHERE   BillId = @billId
									AND Status = 100 )
					BEGIN
						UPDATE  dbo.Meal_PaymentList
						SET     IsPayed = 1
						FROM    dbo.Meal_PaymentList AS a ,
								dbo.PaymentMethods AS b
						WHERE   a.PaymentMethodId = b.Id
								AND b.PaymentMethodName = '支付宝'
								AND a.BillId = @billId
								AND a.IsPayed = 0
	                        
						UPDATE  dbo.Meal_Bills
						SET     IsPartPay = 1
						WHERE   Id = @billId
					END
			END
		
			--已经付款的微信支付结账置为分次收款已结
			IF EXISTS ( SELECT  *
						FROM    dbo.Meal_PaymentList AS a ,
								dbo.PaymentMethods AS b
						WHERE   a.PaymentMethodId = b.Id
								AND b.PaymentMethodName = '微信支付'
								AND a.BillId = @billId
								AND a.IsPayed = 0 )
				BEGIN
					IF EXISTS ( SELECT  *
								FROM    dbo.Meal_AliPayLogs
								WHERE   BillId = @billId
										AND Status = 100 )
						BEGIN
							UPDATE  dbo.Meal_PaymentList
							SET     IsPayed = 1
							FROM    dbo.Meal_PaymentList AS a ,
									dbo.PaymentMethods AS b
							WHERE   a.PaymentMethodId = b.Id
									AND b.PaymentMethodName = '微信支付'
									AND a.BillId = @billId
									AND a.IsPayed = 0
		                        
							UPDATE  dbo.Meal_Bills
							SET     IsPartPay = 1
							WHERE   Id = @billId
						END
				END
	
				--已经付款的微信支付结账置为分次收款已结
				IF EXISTS ( SELECT  *
							FROM    dbo.Meal_PaymentList AS a ,
									dbo.PaymentMethods AS b
							WHERE   a.PaymentMethodId = b.Id
									AND b.PaymentMethodName = '手机支付'
									AND a.BillId = @billId
									AND a.IsPayed = 0 )
					BEGIN
						IF EXISTS ( SELECT  *
									FROM    dbo.Meal_AliPayLogs
									WHERE   BillId = @billId
											AND Status = 100 )
							BEGIN
								--UPDATE  dbo.Meal_PaymentList
								--SET     IsPayed = 1
								--FROM    dbo.Meal_PaymentList AS a ,
								--		dbo.PaymentMethods AS b
								--WHERE   a.PaymentMethodId = b.Id
								--		AND b.PaymentMethodName = '手机支付'
								--		AND a.BillId = @billId
								--		AND a.IsPayed = 0
			                        
								--UPDATE  dbo.Meal_Bills
								--SET     IsPartPay = 1
								--WHERE   Id = @billId
								update Meal_AliPayLogs set Status = 101 where BillId = @billId
							END
					END
	
				--删除非线上支付
				DELETE FROM dbo.Meal_PaymentList  WHERE IsPayed = 0 AND BillId = @billId	
		END
    IF @localTrans = 1 
        IF @@Error = 0 
            BEGIN
                COMMIT TRANSACTION
            END
    IF @@Error <> 0 
        BEGIN

            IF @localTrans = 1 
                ROLLBACK TRANSACTION
            EXECUTE dbo.InsertAErrorLog @ErrorLogID
	  	
        END
    RETURN 0




GO

/****** Object:  View [dbo].[Meal_V_TangShiBill]    Script Date: 05/11/2020 16:09:36 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Meal_V_TangShiBill]'))
DROP VIEW [dbo].[Meal_V_TangShiBill]
GO


/*------------------------------------------20180625(5.0.50)------------------------------------*/
CREATE VIEW [dbo].[Meal_V_TangShiBill]
AS
SELECT     b.Id, b.No, b.PayDatetime, s.SerialNo, a.NumberOfAdults, ISNULL(b.InvoiceNo, '') AS InvoiceNo, b.IsPartPay, b.IsReturnSale
FROM         (SELECT     Id, No, PayDatetime, InvoiceNo, IsPartPay, IsReturnSale
                       FROM          dbo.Meal_Bills
                       WHERE      (IsCheckedOut = 0)) AS b LEFT OUTER JOIN
                          (SELECT     Id, NumberOfAdults
                            FROM          dbo.Meal_ArrangedTables) AS a ON a.Id = b.Id LEFT OUTER JOIN
                      dbo.Meal_SerialNo AS s ON b.Id = s.Id

GO


IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Meal_ufnGetAliPayLogs4Up]'))
DROP FUNCTION [dbo].[Meal_ufnGetAliPayLogs4Up]
GO

CREATE FUNCTION [dbo].[Meal_ufnGetAliPayLogs4Up]
    (
      @BranchNo [varchar](50),
      @BillId UNIQUEIDENTIFIER 
    )
RETURNS @returntable TABLE
    (
      [AliPayLogsId] [int] NOT NULL ,
      [BranchNo] [varchar](50) NULL ,
      [ArrangedTableId] [uniqueidentifier] NOT NULL ,
      [BillId] [uniqueidentifier] NOT NULL ,
      [BillNo] [varchar](50) NOT NULL ,
      [PayAmount] [decimal](9, 2) NOT NULL ,
      [PayText] [nvarchar](500) NOT NULL ,
      [Status] [int] NOT NULL ,
      [OperatorId] [uniqueidentifier] NOT NULL ,
      [OperateDatetime] [datetime] NOT NULL ,
      [DiningTableId] [uniqueidentifier] NULL ,
      [DiningTableNo] [varchar](10) NULL ,
      [DiningTableName] [nvarchar](20) NULL ,
      [AliPayDateTime] [datetime] NULL ,
      [ArrangedDateTime] [datetime] NULL ,
      [VolumeOfConsumption] [decimal](11, 2) NULL ,
      [SerialNo] [varchar](10) NULL ,
      [MealBillNo] [varchar](20) NULL ,
      [WaiterId] [uniqueidentifier] NULL ,
      [ReactOnId] [int] NULL ,
      [PayType] [varchar](20) NULL ,
      [MealPaymentListId] [uniqueidentifier] NULL ,
      [PaymentMethodId] [int] NULL ,
      [IsPhonePay] [bit] NULL ,
      [TelCreateNo] [varchar](100) NULL ,
      [ReceiptAmount] [decimal](9, 2) NULL ,
      [BuyerPayAmount] [decimal](9, 2) NULL ,
      [PointAmount] [decimal](9, 2) NULL ,
      [SellerDiscount] [decimal](9, 2) NULL ,
      [PlatformDiscount] [decimal](9, 2) NULL
    )
AS
    BEGIN
        INSERT  @returntable
                SELECT  Id ,
                        @BranchNo ,
                        ArrangedTableId ,
                        BillId ,
                        BillNo ,
                        PayAmount ,
                        PayText ,
                        Status ,
                        OperatorId ,
                        OperateDatetime ,
                        DiningTableId ,
                        DiningTableNo ,
                        DiningTableName ,
                        AliPayDateTime ,
                        ArrangedDateTime ,
                        VolumeOfConsumption ,
                        SerialNo ,
                        MealBillNo ,
                        WaiterId ,
                        ReactOnId ,
                        PayType ,
                        MealPaymentListId ,
                        PaymentMethodId ,
                        IsPhonePay ,
                        TelCreateNo ,
                        ReceiptAmount ,
                        BuyerPayAmount ,
                        PointAmount ,
                        SellerDiscount ,
                        PlatformDiscount
                FROM    ( SELECT    Id ,
                                    ArrangedTableId ,
                                    BillId ,
                                    BillNo ,
                                    PayAmount ,
                                    PayText ,
                                    Status ,
                                    OperatorId ,
                                    OperateDatetime ,
                                    DiningTableId ,
                                    DiningTableNo ,
                                    DiningTableName ,
                                    AliPayDateTime ,
                                    ArrangedDateTime ,
                                    VolumeOfConsumption ,
                                    SerialNo ,
                                    MealBillNo ,
                                    WaiterId ,
                                    ReactOnId ,
                                    PayType ,
                                    MealPaymentListId ,
                                    PaymentMethodId ,
                                    IsPhonePay ,
                                    TelCreateNo ,
                                    ReceiptAmount ,
                                    BuyerPayAmount ,
                                    PointAmount ,
                                    SellerDiscount ,
                                    PlatformDiscount
                          FROM      Meal_AliPayLogs
                          UNION ALL
                          SELECT    Id ,
                                    ArrangedTableId ,
                                    BillId ,
                                    BillNo ,
                                    PayAmount ,
                                    PayText ,
                                    Status ,
                                    OperatorId ,
                                    OperateDatetime ,
                                    DiningTableId ,
                                    DiningTableNo ,
                                    DiningTableName ,
                                    AliPayDateTime ,
                                    ArrangedDateTime ,
                                    VolumeOfConsumption ,
                                    SerialNo ,
                                    MealBillNo ,
                                    WaiterId ,
                                    ReactOnId ,
                                    PayType ,
                                    MealPaymentListId ,
                                    PaymentMethodId ,
                                    IsPhonePay ,
                                    TelCreateNo ,
                                    ReceiptAmount ,
                                    BuyerPayAmount ,
                                    PointAmount ,
                                    SellerDiscount ,
                                    PlatformDiscount
                          FROM      Meal_History_AliPayLogs
                        ) AS a
                WHERE   a.BillId = @BillId
        RETURN 
    END

GO

                
ALTER   FUNCTION [dbo].[Meal_ufnReportCenter_SundryCollectionsNew]
    (
      @BeginDate DATETIME ,
      @EndDate DATETIME ,
      @DiningAreaN0 VARCHAR(3) , --厅
      @BusinessHourNo VARCHAR(2) , --营业时段
      @CashierNo VARCHAR(5)  --收银员
    )
RETURNS @returntable TABLE
    (
      SundryNo VARCHAR(5) ,
      SundryName NVARCHAR(50) ,
      Volumes DECIMAL(18, 2)
    )
AS 
    BEGIN
		DECLARE @PlatOrderFoodMoney DECIMAL (9,2)--记录平台的菜例价格合计,用于消费合计统计
		DECLARE @PlatOrderFoodDiscount DECIMAL(9,2)--记录平台折扣金额
		DECLARE @PlatOrderCustomerTimers INT --记录平台客次,就餐人数
        DECLARE @HourId INT
        DECLARE @CashierId UNIQUEIDENTIFIER
        IF ( @BusinessHourNo <> '%' ) 
            BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    dbo.BusinessHours
                                WHERE   No = @BusinessHourNo ) 
                    BEGIN
                        RETURN
                    END
                SET @HourId = ( SELECT TOP 1
                                        Id
                                FROM    dbo.BusinessHours
                                WHERE   No = @BusinessHourNo
                              )
            END
        IF ( @CashierNo <> '%' ) 
            BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    dbo.Employees
                                WHERE   No = @CashierNo ) 
                    BEGIN
                        RETURN
                    END
                SET @CashierId = ( SELECT TOP 1
                                            Id
                                   FROM     dbo.Employees
                                   WHERE    No = @CashierNo
                                 )
            END
      --临时表记录查询时间范围内的所有结账单（历史+当前）
      DECLARE  @CheckOutBills TABLE
    (
       [Id] UNIQUEIDENTIFIER NOT NULL 
      ,[BusinessDate]SMALLDATETIME NOT NULL 
      ,[BusinessHourId]INT NOT NULL 
      ,[BusinessHourNo]VARCHAR (2) NOT NULL 
      ,[NumberOfPersons]INT NULL 
      ,[VolumeOfConsumption]numeric(11,2) NOT NULL 
      ,[GratisValue]numeric(9,2)NOT NULL 
      ,[FoodClubDis]numeric(9,2)NOT NULL 
      ,[DiscountValue]DECIMAL(9,2)NOT NULL 
      ,[DiscountApproverId]UNIQUEIDENTIFIER NULL 
      ,[DerateValue]DECIMAL(9,2)NOT NULL 
      ,[DerateApproverId]UNIQUEIDENTIFIER NULL 
      ,[Oddment]DECIMAL(9,2)NOT NULL 
      ,[PayAmount]DECIMAL(12,2)NOT NULL 
      ,[IsCheckedOut]BIT NOT NULL 
      ,[ConsumeValue]numeric(9,2) NULL 
      ,[ConsumeClubDis]numeric(9,2) NULL 
      ,[ConsumeDiscountValue]numeric(9,2) NULL 
      ,[CashierId]UNIQUEIDENTIFIER NOT NULL 
      ,[EmployeeNo]VARCHAR(5) NULL 
      ,[ScoreExchangeFoodAmount]numeric(9,2)NOT NULL 
      ,[ScoreCompensateAmount]numeric(9,2)NOT NULL 
      ,[SaleType]INT NULL 
      ,TakeAwayPlatTypeId int NULL
      ,SuitDisAmount decimal(9,2) NULL
      ,ProductionAmount decimal(9,2)NULL
    )
      INSERT @CheckOutBills
      SELECT  [Id] 
      ,[BusinessDate]
      ,[BusinessHourId]
      ,[BusinessHourNo]
      ,[NumberOfPersons]
      ,[VolumeOfConsumption]
      ,[GratisValue]
      ,[FoodClubDis]
      ,[DiscountValue]
      ,[DiscountApproverId]
      ,[DerateValue]
      ,[DerateApproverId]
      ,[Oddment]
      ,[PayAmount] 
      ,[IsCheckedOut]
      ,[ConsumeValue]
      ,[ConsumeClubDis]
      ,[ConsumeDiscountValue]
      ,[CashierId]
      ,[EmployeeNo]
      ,[ScoreExchangeFoodAmount]
      ,[ScoreCompensateAmount]
      ,[SaleType]
      ,TakeAwayPlatTypeId
      ,SuitDisAmount 
      ,ProductionAmount
      FROM Meal_ufnGetAllCheckOutBillsNew( @BeginDate , @EndDate) 
       
        DECLARE @MidTable TABLE
            (
              VolumeOfConsumption DECIMAL(18, 2) ,
              ConsumeValue DECIMAL(18, 2) ,
              FoodClubDis DECIMAL(18, 2) ,
              DiscountValue DECIMAL(18, 2) ,
              ConsumeDiscountValue DECIMAL(18, 2) ,
              FoodDerateValue DECIMAL(18, 2) ,
              DerateValue DECIMAL(18, 2) ,
              Oddment DECIMAL(18, 2) ,
              GratisValue DECIMAL(18, 2) ,
              ScoreExchangeFoodAmount DECIMAL(18, 2) ,
              ScoreCompensateAmount DECIMAL(18, 2) ,
              PayAmount DECIMAL(18, 2) ,
              CustomerTimes DECIMAL(18, 2) ,
              OneTablePay DECIMAL(18, 2) ,
              NumberOfPersons DECIMAL(18, 2),
              SuitDisAmount DECIMAL(18,2)
            )
            
        IF ( @CashierId IS NOT NULL
             AND @HourId IS NOT NULL
           ) 
            BEGIN
                INSERT  @MidTable
                        SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                SUM(ConsumeValue) ,
                                -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                -1
                                * SUM(CASE WHEN DiscountApproverId IS NULL
                                           THEN 0
                                           ELSE DiscountValue
                                                + ConsumeDiscountValue
                                      END) ,
                                -1
                                * SUM(CASE WHEN DiscountApproverId IS NULL
                                           THEN DiscountValue
                                                + ConsumeDiscountValue
                                           ELSE 0
                                      END) ,
                                -1
                                * SUM(CASE WHEN DerateApproverId IS NULL
                                           THEN 0
                                           ELSE DerateValue
                                      END) ,
                                -1 * SUM(DerateValue) ,
                                -1 * SUM(Oddment) ,
                                -1 * SUM(GratisValue) ,
                                -1 * SUM(ScoreExchangeFoodAmount) ,
                                -1 * SUM(ScoreCompensateAmount) ,
                                SUM(PayAmount) ,
                                COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                ( SUM(PayAmount)
                                  / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                        WHEN 0 THEN 1
                                        ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                      END ) ) ,
                                SUM(ISNULL(a.NumberOfPersons, 0)),
                                -1*SUM(ISNULL(a.SuitDisAmount,0))
                        
                          FROM @CheckOutBills AS a 
                                 WHERE a.BusinessHourId = @HourId
                                 AND a.CashierId  = @CashierId 
                                 AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客,13 服务员点餐
				 
				 
				 	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
            END
        ELSE 
            IF ( @CashierId IS NOT NULL
                 AND @HourId IS NULL
               ) 
                BEGIN
                    INSERT  @MidTable
                            SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                    SUM(ConsumeValue) ,
                                    -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                    -1
                                    * SUM(CASE WHEN DiscountApproverId IS NULL
                                               THEN 0
                                               ELSE DiscountValue
                                                    + ConsumeDiscountValue
                                          END) ,
                                    -1
                                    * SUM(CASE WHEN DiscountApproverId IS NULL
                                               THEN DiscountValue
                                                    + ConsumeDiscountValue
                                               ELSE 0
                                          END) ,
                                    -1
                                    * SUM(CASE WHEN DerateApproverId IS NULL
                                               THEN 0
                                               ELSE DerateValue
                                          END) ,
                                    -1 * SUM(DerateValue) ,
                                    -1 * SUM(Oddment) ,
                                    -1 * SUM(GratisValue) ,
                                    -1 * SUM(ScoreExchangeFoodAmount) ,
                                    -1 * SUM(ScoreCompensateAmount) ,
                                    SUM(PayAmount) ,
                                    COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                    ( SUM(PayAmount)
                                      / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                            WHEN 0 THEN 1
                                            ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                          END ) ) ,
                                    SUM(ISNULL(a.NumberOfPersons, 0)),
                                    -1*SUM(ISNULL(a.SuitDisAmount,0))
                         -- FROM Meal_V_Bills4GetBills AS a
                          FROM @CheckOutBills AS a 
                                 WHERE   a.CashierId  = @CashierId 
                                 AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                      
                            
                   	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                                        
                END
            ELSE 
                IF ( @CashierId IS NULL
                     AND @HourId IS NOT NULL
                   ) 
                    BEGIN
                        INSERT  @MidTable
                                SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                        SUM(ConsumeValue) ,
                                        -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN 0
                                                   ELSE DiscountValue
                                                        + ConsumeDiscountValue
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN DiscountValue
                                                        + ConsumeDiscountValue
                                                   ELSE 0
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DerateApproverId IS NULL
                                                   THEN 0
                                                   ELSE DerateValue
                                              END) ,
                                        -1 * SUM(DerateValue) ,
                                        -1 * SUM(Oddment) ,
                                        -1 * SUM(GratisValue) ,
                                        -1 * SUM(ScoreExchangeFoodAmount) ,
                                        -1 * SUM(ScoreCompensateAmount) ,
                                        SUM(PayAmount) ,
                                        COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                        ( SUM(PayAmount)
                                          / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                                WHEN 0 THEN 1
                                                ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                              END ) ) ,
                                        SUM(ISNULL(a.NumberOfPersons, 0)),
                                        -1*SUM(ISNULL(a.SuitDisAmount,0))
                           FROM @CheckOutBills AS a 
                                 WHERE a.BusinessHourId = @HourId
                                  AND a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                                        
                  	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                                        
                    END
                ELSE 
                    BEGIN
                        INSERT  @MidTable
                                SELECT  SUM(ISNULL(VolumeOfConsumption, 0)) ,
                                        SUM(ConsumeValue) ,
                                        -1 * SUM(FoodClubDis + ConsumeClubDis) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN 0
                                                   ELSE DiscountValue
                                                        + ConsumeDiscountValue
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DiscountApproverId IS NULL
                                                   THEN DiscountValue
                                                        + ConsumeDiscountValue
                                                   ELSE 0
                                              END) ,
                                        -1
                                        * SUM(CASE WHEN DerateApproverId IS NULL
                                                   THEN 0
                                                   ELSE DerateValue
                                              END) ,
                                        -1 * SUM(DerateValue) ,
                                        -1 * SUM(Oddment) ,
                                        -1 * SUM(GratisValue) ,
                                        -1 * SUM(ScoreExchangeFoodAmount) ,
                                        -1 * SUM(ScoreCompensateAmount) ,
                                        SUM(PayAmount) ,
                                        COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID)) ,
                                        ( SUM(PayAmount)
                                          / ( CASE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                                WHEN 0 THEN 1
                                                ELSE COUNT(DISTINCT CONVERT(VARCHAR(64), a.ID))
                                              END ) ) ,
                                        SUM(ISNULL(a.NumberOfPersons, 0)),
                                        -1*SUM(ISNULL(a.SuitDisAmount,0))
                          
                          FROM @CheckOutBills AS a   
                                 WHERE a.TakeAwayPlatTypeId IN (1,2,4,5,6,7,8,9,13)--1 堂食,2 电话外卖,4 外带,5 扫码点餐，6秒吃，7自取，8外送，9 米客
                            
                            
                   	--统计平台的菜例价格合计,用于消费合计统计
					SELECT @PlatOrderFoodMoney=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客          
                    END
        
     
        --计算平台外卖到账金额
		 DECLARE @TakeAwayMoney DECIMAL (9,2)
					  SELECT  @TakeAwayMoney= SUM(ISNULL(TakeAwayPlatPayShopMoney,0))
					  FROM      dbo.Meal_PlatTakeAwayBillsDispose
					  WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
								AND BusinessHourNo LIKE @BusinessHourNo
								AND OperatorNo LIKE @CashierNo
								AND IsInMealReversedBillsTable=0
								AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
            IF(@PlatOrderFoodDiscount IS null)
            SET @PlatOrderFoodDiscount=0
	    INSERT  @returntable
                SELECT  SundryNo ,
                        SundryName ,
                        SUM(Volumes) AS Volumes
                FROM    (
                          SELECT    '01' AS SundryNo ,
                                    '*消费总额' AS SundryName ,
                                    (ISNULL(VolumeOfConsumption,0) +ISNULL(@PlatOrderFoodMoney,0) ) AS Volumes--消费+外卖菜例金额
                          FROM      @MidTable AS a
                          WHERE     ISNULL(VolumeOfConsumption,0)<> 0 OR ISNULL(@PlatOrderFoodMoney,0)<>0
                          UNION ALL
                          SELECT    '02' AS SundryNo ,
                                    '*优惠总额' AS SundryName ,
                                    ISNULL(SUM(FoodClubDis)+SUM(DiscountValue)+SUM(ConsumeDiscountValue)+SUM(FoodDerateValue)
                                    -@PlatOrderFoodDiscount+ SUM(Oddment)+SUM(GratisValue)+SUM(ScoreExchangeFoodAmount)
                                    +SUM(ScoreCompensateAmount)+SUM(SuitDisAmount),0)AS Volumes
                          FROM      @MidTable AS a
                          UNION ALL
                          SELECT    '025' AS SundryNo ,
                                    ' 会员优惠' AS SundryName ,
                                    FoodClubDis AS Volumes
                          FROM      @MidTable AS a
                          WHERE     FoodClubDis <> 0
                          UNION ALL
                          SELECT    '03' AS SundryNo ,
                                    ' 批准折扣' AS SundryName ,
                                    DiscountValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     DiscountValue <> 0
                          UNION ALL
                          SELECT    '042' AS SundryNo ,
                                    ' 会员折扣' AS SundryName ,
                                    ConsumeDiscountValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ConsumeDiscountValue <> 0
                        
                          UNION ALL
                          SELECT    '043' AS SundryNo ,
                                    ' 单品折扣' AS SundryName ,
                                    FoodDerateValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     FoodDerateValue <> 0
                          UNION ALL 
                          SELECT '044' AS SundryNo ,
                                    ' 平台折扣' AS SundryName ,
                                    0 - isnull(@PlatOrderFoodDiscount,0)
                                    WHERE isnull(@PlatOrderFoodDiscount,0)<>0
                          UNION ALL
                          SELECT    '06' AS SundryNo ,
                                    '     抹零' AS SundryName ,
                                    Oddment AS Volumes
                          FROM      @MidTable AS a
                          WHERE     Oddment <> 0
                          UNION ALL
                          SELECT    '07' AS SundryNo ,
                                    '     赠送' AS SundryName ,
                                    GratisValue AS Volumes
                          FROM      @MidTable AS a
                          WHERE     GratisValue <> 0
                          UNION ALL
                          SELECT    '071' AS SundryNo ,
                                    ' 积分兑换' AS SundryName ,
                                    ScoreExchangeFoodAmount AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ScoreExchangeFoodAmount <> 0
                          UNION ALL
                          SELECT    '072' AS SundryNo ,
                                    ' 积分抵扣' AS SundryName ,
                                    ScoreCompensateAmount AS Volumes
                          FROM      @MidTable AS a
                          WHERE     ScoreCompensateAmount <> 0
                          UNION ALL
                          SELECT    '08' AS SundryNo ,
                                    '*实收金额' AS SundryName ,
                                    (ISNULL(PayAmount,0)+ISNULL(@TakeAwayMoney,0) )AS Volumes--实收+外卖平台到账
                          FROM      @MidTable AS a
                          WHERE     ISNULL(PayAmount,0) <> 0 OR ISNULL(@TakeAwayMoney,0)<>0
                          UNION ALL
                          SELECT    '09' AS SundryNo ,
                                    '*客次' AS SundryName ,
                                    CustomerTimes+ISNULL(@PlatOrderCustomerTimers,0)
                          FROM      @MidTable AS a
                          WHERE     CustomerTimes <> 0
                          UNION ALL
                          SELECT    '11' AS SundryNo ,
                                    '*客单' AS SundryName ,
                                    OneTablePay 
                          FROM      @MidTable AS a
                          WHERE     OneTablePay <> 0
                          UNION ALL
                          SELECT    '10' AS SundryNo ,
                                    '*就餐人数' AS SundryName ,
                                    NumberOfPersons+ISNULL(@PlatOrderCustomerTimers,0)
                          FROM      @MidTable AS a
                          WHERE     NumberOfPersons <> 0
                          UNION ALL
                          SELECT    '15' AS SundryNo ,
                                    '*退菜金额' AS SundryName ,
                                    SUM(Charge4Food) AS Volumes
                          FROM      dbo.Meal_V_OrderFoods-----------------------------------------------------------------------
                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                    AND DiningAreaNo LIKE @DiningAreaN0
                                    AND BusinessHourNo LIKE @BusinessHourNo
                                    AND CashierNo LIKE @CashierNo
                                    AND IsReturnSale = 1
                          GROUP BY  DiningAreaNo ,
                                    DiningAreaName ,
                                    BusinessDate ,
                                    BusinessHourNo ,
                                    BusinessHourName ,
                                    CashierNo ,
                                    CashierName
                          UNION ALL
                          SELECT    '15' AS SundryNo ,
                                    '*退菜金额' AS SundryName ,
                                    SUM(RealDues) AS Volumes
                          FROM      dbo.Meal_SpeFoodSaleCollections----------------------------------------------------
                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                    AND BusinessHourNo LIKE @BusinessHourNo
                                    AND CashierNo LIKE @CashierNo
                                    AND SpeType = 1
                          GROUP BY  BusinessDate ,
                                    BusinessHourNo ,
                                    BusinessHourName ,
                                    CashierNo ,
                                    CashierName
                        ) a
                GROUP BY SundryNo ,
                        SundryName
                ORDER BY SundryNo
--------------------------------------------------------------------------------------------------------------- 
                
--如果有外带或外卖则分别统计(堂食，外卖，外带 针对消费总额和实收总额的处理)-----------------
        DECLARE @TakeAwayFlag INT
        SET @TakeAwayFlag = 0
        SELECT  @TakeAwayFlag = COUNT(SaleType)
        FROM    @CheckOutBills
        WHERE   BusinessHourNo LIKE @BusinessHourNo
                AND EmployeeNo LIKE @CashierNo
                AND TakeAwayPlatTypeId IN ( 2, 4 ,5,6,7,8,9,10,11,12,13)
       
        IF @TakeAwayFlag > 0 --则首先生成堂食数据
            BEGIN
                INSERT  @returntable
                        SELECT  SundryNo ,
                                SundryName ,
                                SUM(Volumes) AS Volumes
                        FROM    ( SELECT    BusinessDate ,
                                            BusinessHourNo ,
                                            EmployeeNo ,
                                            '011' AS SundryNo ,
                                            '     堂食' AS SundryName ,
                                            SUM(ISNULL(VolumeOfConsumption, 0)) AS Volumes--消费合计
                                  FROM      @CheckOutBills
                                  WHERE     BusinessHourNo LIKE @BusinessHourNo
                                            AND EmployeeNo LIKE @CashierNo
                                            AND TakeAwayPlatTypeId = 1
                                  GROUP BY  BusinessDate ,
                                            BusinessHourNo ,
                                            EmployeeNo
                                  HAVING    SUM(ISNULL(VolumeOfConsumption, 0)) <> 0
                                  UNION ALL
                                  SELECT    BusinessDate ,
                                            BusinessHourNo ,
                                            EmployeeNo ,
                                            '081' AS SundryNo ,
                                            '     堂食' AS SundryName ,
                                            SUM(PayAmount) AS Volumes--堂食实付
                                  FROM      @CheckOutBills
                                  WHERE     BusinessHourNo LIKE @BusinessHourNo
                                            AND EmployeeNo LIKE @CashierNo
                                            AND TakeAwayPlatTypeId = 1
                                  GROUP BY  BusinessDate ,
                                            BusinessHourNo ,
                                            EmployeeNo
                                  HAVING    SUM(PayAmount) <> 0
                                ) AS a
                        GROUP BY SundryNo ,
                                SundryName
                        ORDER BY SundryNo
                --  处理外卖
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND EmployeeNo LIKE @CashierNo
                        AND TakeAwayPlatTypeId IN  (2,8,9)
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo ,
                                                    '014' AS SundryNo ,
                                                    '     外卖' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo
                                                    AND TakeAwayPlatTypeId IN( 2,9)
                                          GROUP BY  
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo 
                                          HAVING    SUM(ISNULL(VolumeOfConsumption, 0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo ,
                                                    '084' AS SundryNo ,
                                                    '     外卖' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo
                                                    AND TakeAwayPlatTypeId IN( 2,9)
                                          GROUP BY 
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                        
                    END  
                    --处理外带
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND EmployeeNo LIKE @CashierNo
                        AND TakeAwayPlatTypeId IN ( 4,7)
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo ,
                                                    '015' AS SundryNo ,
                                                    '     外带' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo
                                                    AND TakeAwayPlatTypeId IN ( 4)--店内外带，和手机订外带
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo 
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,
                                                    BusinessHourNo ,                                                   
                                                    EmployeeNo ,                                                   
                                                    '085' AS SundryNo ,
                                                    '     外带' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM     @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo                                                   
                                                    AND TakeAwayPlatTypeId IN ( 4)
                                          GROUP BY 
                                                    BusinessDate ,
                                                    BusinessHourNo ,                                                  
                                                    EmployeeNo
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    --扫码点餐处理
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(SaleType)
                FROM    @CheckOutBills
                WHERE   BusinessHourNo LIKE @BusinessHourNo
                        AND EmployeeNo LIKE @CashierNo                      
                        AND TakeAwayPlatTypeId BETWEEN 5 AND 8 
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( SELECT    BusinessDate ,                                                   
                                                    BusinessHourNo ,                                                 
                                                    EmployeeNo ,                                                 
                                                    '012' AS SundryNo ,
                                                    '   好易点' AS SundryName ,
                                                    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) AS Volumes
                                          FROM     @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo                                                   
                                                    AND (TakeAwayPlatTypeId BETWEEN 5 AND 8 OR TakeAwayPlatTypeId=13)
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,
                                                     EmployeeNo
                                          HAVING    SUM(ISNULL(VolumeOfConsumption,
                                                              0)) <> 0
                                          UNION ALL
                                          SELECT    BusinessDate ,                                                   
                                                    BusinessHourNo ,                                                   
                                                    EmployeeNo ,                                                   
                                                    '082' AS SundryNo ,
                                                    '   好易点' AS SundryName ,
                                                    SUM(PayAmount) AS Volumes
                                          FROM      @CheckOutBills
                                          WHERE     BusinessHourNo LIKE @BusinessHourNo
                                                    AND EmployeeNo LIKE @CashierNo
                                                    AND  (TakeAwayPlatTypeId BETWEEN 5 AND 8 OR TakeAwayPlatTypeId=13)
                                          GROUP BY 
                                                    BusinessDate ,
                                                    BusinessHourNo ,
                                                    EmployeeNo
                                          HAVING    SUM(PayAmount) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    
                
                    
                    --自助点餐
                SET @TakeAwayFlag = 0
                SELECT  @TakeAwayFlag = COUNT(TakeAwayPlatPayShopMoney)
                FROM    dbo.Meal_PlatTakeAwayBillsDispose
                WHERE   BusinessDate BETWEEN @BeginDate AND @EndDate
                        AND BusinessHourNo LIKE @BusinessHourNo
                        AND OperatorNo LIKE @CashierNo
                        AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
                        
                DECLARE  @platOrderFoodMoney4All DECIMAL(9,2) 
                SET @platOrderFoodMoney4All=0
                --统计平台的菜例价格合计,用于消费合计统计
					SELECT @platOrderFoodMoney4All=SUM(ISNULL(VolumeOfConsumption,0)) ,--消费总额
					       @PlatOrderFoodDiscount=SUM(ISNULL(DiscountValue,0)),--平台优惠金额
					       @PlatOrderCustomerTimers=ISNULL(COUNT(VolumeOfConsumption),0)--客次,就餐人数
					FROM  @CheckOutBills AS b
                        WHERE   b.BusinessHourNo LIKE  @BusinessHourNo
                                AND b.TakeAwayPlatTypeId IN (10,11,12)--1 堂食,2 电话外卖,4 外带,9 米客
                               
                IF ( @TakeAwayFlag > 0 ) 
                    BEGIN                
                        INSERT  @returntable
                                SELECT  SundryNo ,
                                        SundryName ,
                                        SUM(Volumes) AS Volumes
                                FROM    ( 
                                         SELECT     '016' AS SundryNo ,
                                                    '     平台' AS SundryName ,
                                                    ISNULL(@platOrderFoodMoney4All,0) AS Volumes --平台菜例消费总额
                                          
                                          UNION ALL
                                          SELECT   
                                                    '086' AS SundryNo ,
                                                    '     平台' AS SundryName ,
                                                    SUM(ISNULL(TakeAwayPlatPayShopMoney,
                                                              0)) AS Volumes
                                          FROM      dbo.Meal_PlatTakeAwayBillsDispose
                                          WHERE     BusinessDate BETWEEN @BeginDate AND @EndDate
                                                    AND BusinessHourNo LIKE @BusinessHourNo
                                                    AND OperatorNo LIKE @CashierNo
                                                    AND IsInMealReversedBillsTable=0  AND TakeAwayType NOT IN ('扫码点餐','秒吃','自取','外送','服务员点餐')
                                          GROUP BY  BusinessDate ,
                                                    BusinessHourNo ,
                                                    OperatorNo
                                          HAVING    SUM(ISNULL(TakeAwayPlatPayShopMoney,
                                                              0)) <> 0
                                        ) AS a
                                GROUP BY SundryNo ,
                                        SundryName
                                ORDER BY SundryNo
                    END 
                    
                    
                    
            END          
        DECLARE @realdues DECIMAL(18, 2)
        DECLARE @saletables DECIMAL(18, 2)
        DECLARE @salenum DECIMAL(18, 2)
        SET @realdues = ( SELECT    Volumes
                          FROM      @returntable
                          WHERE     SundryName = '*实收金额'
                        )
        SET @saletables = ( SELECT  Volumes
                            FROM    @returntable
                            WHERE   SundryName = '*客次'
                          )
        SET @salenum = ( SELECT Volumes
                         FROM   @returntable
                         WHERE  SundryName = '*就餐人数'
                       )
        
        IF ( @saletables IS NULL
             OR @saletables = 0
           ) 
            BEGIN
                SET @saletables = 1
            END
        IF ( @salenum IS NULL
             OR @salenum = 0
           ) 
            BEGIN
                SET @salenum = 1
            END 
        ------将就餐人数项改为人均消费
        INSERT  INTO @returntable
        VALUES  ( '15', '*人均消费', 0 )  
        IF NOT ( @realdues IS NULL ) 
            BEGIN
                UPDATE  @returntable
                SET     Volumes = @realdues / @saletables
                WHERE   SundryName = '*客单'
                UPDATE  @returntable
                SET     Volumes = @realdues / @salenum
                WHERE   SundryName = '*人均消费'
            END
        
        RETURN
    END
GO

ALTER    VIEW [dbo].[Meal_V_AllOrderFoodsNew]
AS
    SELECT  f.Id AS OrderFoodId ,
            b.BusinessHourId ,
            hr.No AS BusinessHourNo ,
            hr.BusinessHourName ,
            o.CaptainsOrderName ,
            f.FoodTypeId ,
            ft.No AS FoodTypeNo ,
            f.FoodTypeName ,
            f.FoodId ,
            f.FoodNo ,
            f.FoodName ,
            f.Specification ,
            f.Quantity ,
            f.Dues4Food,
            f.Charge4Food ,
            f.OrderDatetime ,
            f.SpecificationNo ,--规格编号
            b.TakeAwayPlatTypeId,
            f.CaptainsOrderId
    FROM    dbo.Meal_OrderFoods AS f
            LEFT JOIN dbo.Meal_Meal_CaptainsOrders AS o ON f.CaptainsOrderId = o.id
            LEFT JOIN dbo.Meal_ArrangedTables AS a ON o.ArrangedTableId = a.id
            LEFT JOIN dbo.Meal_Bills AS b ON a.BillId = b.Id
            LEFT JOIN dbo.FoodTypes AS ft ON f.foodtypeId = ft.id
            LEFT JOIN dbo.BusinessHours AS hr ON b.BusinessHourId = hr.Id
    WHERE   ( f.IsDeleted = 0 )
    UNION ALL
   SELECT  f.Id AS OrderFoodId ,
            b.BusinessHourId ,
            hr.No AS BusinessHourNo ,
            hr.BusinessHourName ,
            o.CaptainsOrderName ,
            f.FoodTypeId ,
            ft.No AS FoodTypeNo ,
            f.FoodTypeName ,
            f.FoodId ,
            f.FoodNo ,
            f.FoodName ,
            f.Specification ,
            f.Quantity ,
            f.Dues4Food,
            f.Charge4Food ,
            f.OrderDatetime ,
            f.SpecificationNo ,--规格编号
            b.TakeAwayPlatTypeId,
            f.CaptainsOrderId
    FROM    dbo.Meal_history_OrderFoods AS f
            LEFT JOIN dbo.Meal_history_Meal_CaptainsOrders AS o ON f.CaptainsOrderId = o.id
            LEFT JOIN dbo.Meal_History_ArrangedTables AS a ON o.ArrangedTableId = a.id
            LEFT JOIN dbo.Meal_history_Bills AS b ON a.BillId = b.Id
            LEFT JOIN dbo.FoodTypes AS ft ON f.foodtypeId = ft.id
            LEFT JOIN dbo.BusinessHours AS hr ON b.BusinessHourId = hr.Id
    WHERE   ( f.IsDeleted = 0 )
            AND a.status <> 2

GO

ALTER   FUNCTION [dbo].[Meal_New_OrderFoodsStatNew]
    (
      @BarNo VARCHAR(3) = '%' ,
      @beginBusinessDate DATETIME ,
      @endBusinessDate DATETIME ,
      @businessHourNo VARCHAR(10) = '%' ,
      @IsShowSpeci BIT = 1 ,--是否显示多规格
      @IsNotIncludeSuit INT = 1 ,--是否含套餐菜例  1:全部，2 ：只统计单菜销售 3：只统计套餐中菜例销售
      @EatType VARCHAR(3) = '%'--就餐方式
    )
RETURNS @returntable TABLE
    (
      FoodId UNIQUEIDENTIFIER NOT NULL ,  --菜品或与部门联合主键
      FoodTypeId UNIQUEIDENTIFIER NOT NULL ,
      FoodTypeNo VARCHAR(2) NOT NULL ,
      FoodTypeName NVARCHAR(20) NOT NULL ,
      FoodNo VARCHAR(8) NOT NULL ,
      FoodName NVARCHAR(50) NOT NULL ,
      Specification NVARCHAR(10) NOT NULL ,
      SpecificationNo VARCHAR(2) NOT NULL ,
      SaleQuantity NUMERIC(9, 2) NOT NULL ,
      saleDues4Food NUMERIC(9,2) NOT NULL,
      saleCharge4Food NUMERIC(12, 2) NOT NULL ,
      sumall NUMERIC(9, 2) NOT NULL
    )
AS
    BEGIN
    
        DECLARE @Suit TABLE--套餐临时表
            (
              CaptainsOrders4SuitId UNIQUEIDENTIFIER NOT NULL ,--套餐单id
              TakeawayPlatTypeId INT NOT NULL--销售类型
            );
        INSERT  INTO @Suit
                SELECT  su.Id ,
                        b.TakeAwayPlatTypeId
                FROM    dbo.Meal_Meal_CaptainsOrders4Suits AS su
                        LEFT JOIN dbo.Meal_Meal_CaptainsOrders AS c ON su.Id = c.Id
                        LEFT JOIN dbo.Meal_Bills AS b ON b.Id = c.ArrangedTableId
                WHERE   b.PayDatetime BETWEEN @beginBusinessDate
                                      AND     @endBusinessDate
                UNION ALL
                SELECT  su.Id ,
                        b.TakeAwayPlatTypeId
                FROM    dbo.Meal_History_Meal_CaptainsOrders4Suits AS su
                        LEFT JOIN dbo.Meal_History_Meal_CaptainsOrders AS c ON su.Id = c.Id
                        LEFT JOIN dbo.Meal_History_Bills AS b ON b.Id = c.ArrangedTableId
                WHERE   b.PayDatetime BETWEEN @beginBusinessDate
                                      AND     @endBusinessDate; 

    
        DECLARE @Meal_VALLOrderFoods TABLE--菜例销售明细临时表
            (
              [OrderFoodId] UNIQUEIDENTIFIER NOT NULL ,
              [FoodTypeId] UNIQUEIDENTIFIER NOT NULL ,
              [FoodTypeNo] CHAR(2) NULL ,
              [FoodTypeName] VARCHAR(20) NOT  NULL ,
              [FoodId] UNIQUEIDENTIFIER NOT NULL ,
              [FoodNo] VARCHAR(8) NOT  NULL ,
              [FoodName] NVARCHAR(50) NOT NULL ,
              [Specification] NVARCHAR(10) NOT  NULL ,
              [SpecificationNo] NVARCHAR(10) NOT  NULL ,
              [Quantity] DECIMAL(9, 2) NOT NULL ,
              [Dues4Food] DECIMAL(9,2) NOT NULL,
              [Charge4Food] DECIMAL(10, 2) NOT NULL ,
              CaptainsOrderName VARCHAR(255) NOT NULL
            );
        IF ( @EatType = '%' )--就餐方式
            BEGIN 
                INSERT  INTO @Meal_VALLOrderFoods
                        SELECT  [OrderFoodId] ,
                                [FoodTypeId] ,
                                [FoodTypeNo] ,
                                [FoodTypeName] ,
                                [FoodId] ,
                                [FoodNo] ,
                                [FoodName] ,
                                [Specification] ,
                                [SpecificationNo] ,
                                [Quantity] ,
                                [Dues4Food],
                                [Charge4Food] ,
                                CaptainsOrderName
                        FROM    Meal_V_AllOrderFoodsNew
                        WHERE   ( OrderDatetime BETWEEN @beginBusinessDate
                                                AND     @endBusinessDate )
                                AND BusinessHourNo LIKE @businessHourNo;
            END;
  
        IF ( @EatType = '1' )
            BEGIN 
                INSERT  INTO @Meal_VALLOrderFoods
                        SELECT  [OrderFoodId] ,
                                [FoodTypeId] ,
                                [FoodTypeNo] ,
                                [FoodTypeName] ,
                                [FoodId] ,
                                [FoodNo] ,
                                [FoodName] ,
                                [Specification] ,
                                [SpecificationNo] ,
                                [Quantity] ,
                                [Dues4Food],
                                [Charge4Food] ,
                                CaptainsOrderName
                        FROM    Meal_V_AllOrderFoodsNew
                        WHERE   ( OrderDatetime BETWEEN @beginBusinessDate
                                                AND     @endBusinessDate )
                                AND BusinessHourNo LIKE @businessHourNo
                                AND CaptainsOrderId IN (
                                SELECT  Id
                                FROM    dbo.Meal_V_AllBillsId
                                WHERE   TakeAwayPlatTypeId BETWEEN 1 AND 9 OR TakeAwayPlatTypeId=13);--13 服务员点餐
		 
                INSERT  INTO @Meal_VALLOrderFoods--处理套餐中的
                        SELECT  [OrderFoodId] ,
                                [FoodTypeId] ,
                                [FoodTypeNo] ,
                                [FoodTypeName] ,
                                [FoodId] ,
                                [FoodNo] ,
                                [FoodName] ,
                                [Specification] ,
                                [SpecificationNo] ,
                                [Quantity] ,
                                [Dues4Food],
                                [Charge4Food] ,
                                CaptainsOrderName
                        FROM    Meal_V_AllOrderFoodsNew
                        WHERE   ( OrderDatetime BETWEEN @beginBusinessDate
                                                AND     @endBusinessDate )
                                AND BusinessHourNo LIKE @businessHourNo
                                AND CaptainsOrderId IN (
                                SELECT  CaptainsOrders4SuitId
                                FROM    @Suit
                                WHERE   TakeawayPlatTypeId BETWEEN 1 AND 9 OR TakeAwayPlatTypeId=13);
	
		 		 
            END;
        IF ( @EatType = '2' )
            BEGIN 
                INSERT  INTO @Meal_VALLOrderFoods
                        SELECT  [OrderFoodId] ,
                                [FoodTypeId] ,
                                [FoodTypeNo] ,
                                [FoodTypeName] ,
                                [FoodId] ,
                                [FoodNo] ,
                                [FoodName] ,
                                [Specification] ,
                                [SpecificationNo] ,
                                [Quantity] ,
                                [Dues4Food],
                                [Charge4Food] ,
                                CaptainsOrderName
                        FROM    Meal_V_AllOrderFoodsNew
                        WHERE   ( OrderDatetime BETWEEN @beginBusinessDate
                                                AND     @endBusinessDate )
                                AND BusinessHourNo LIKE @businessHourNo
                                AND CaptainsOrderId IN (
                                SELECT  Id
                                FROM    dbo.Meal_V_AllBillsId
                                WHERE   TakeAwayPlatTypeId BETWEEN 10 AND 12 );
		 
		 
                INSERT  INTO @Meal_VALLOrderFoods--处理套餐中的
                        SELECT  [OrderFoodId] ,
                                [FoodTypeId] ,
                                [FoodTypeNo] ,
                                [FoodTypeName] ,
                                [FoodId] ,
                                [FoodNo] ,
                                [FoodName] ,
                                [Specification] ,
                                [SpecificationNo] ,
                                [Quantity] ,
                                [Dues4Food],
                                [Charge4Food] ,
                                CaptainsOrderName
                        FROM    Meal_V_AllOrderFoodsNew
                        WHERE   ( OrderDatetime BETWEEN @beginBusinessDate
                                                AND     @endBusinessDate )
                                AND BusinessHourNo LIKE @businessHourNo
                                AND CaptainsOrderId IN (
                                SELECT  CaptainsOrders4SuitId
                                FROM    @Suit
                                WHERE   TakeawayPlatTypeId BETWEEN 10 AND 12 );
	
            END;
  
        DECLARE @sumall DECIMAL(9, 2);
  
        IF ( @IsNotIncludeSuit = 1 )--统计所有的包含单品销售和套餐内菜例销售
            BEGIN
                IF ( @IsShowSpeci = 1 )
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,    --菜类
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo ,  --,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Dues4Food) as sumDues4Food,
                                        SUM(Charge4Food) AS sumCharge4Food , --金额（折后，赠送为0）        
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,    --菜类
                                        FoodId ,
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo  --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                                
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;
                ELSE
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,    --菜类
                                        FoodNo ,
                                        FoodName ,
                                        '份' ,
                                        '0' ,--,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Dues4Food) as sumDues4Food,
                                        SUM(Charge4Food) AS sumCharge4Food --金额（折后，赠送为0）    
                                        ,
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,   --菜类
                                        FoodId ,
                                        FoodNo ,
                                        FoodName --,Specification    --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;        
            END;
         IF ( @IsNotIncludeSuit = 2 )
            BEGIN
                IF ( @IsShowSpeci = 1 )
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,   --菜类
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo ,   --,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Dues4Food) as sumDues4Food,
                                        SUM(Charge4Food) AS sumCharge4Food --金额（折后，赠送为0）    
                                        ,
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                WHERE   f.CaptainsOrderName <> '套餐单'
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,    --菜类
                                        FoodId ,
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo  --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;
                ELSE
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,   --菜类
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo ,   --,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Dues4Food) as sumDues4Food,
                                        SUM(Charge4Food) AS sumCharge4Food --金额（折后，赠送为0） 
                                        ,
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                WHERE   f.CaptainsOrderName <> '套餐单'
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,  --菜类
                                        Specification ,
                                        SpecificationNo ,  --,Price  菜例
                                        FoodId ,
                                        FoodNo ,
                                        FoodName --,Specification    --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;
            END; 
            
			 IF ( @IsNotIncludeSuit = 3 )--只统计套餐中菜例销售
            BEGIN
                IF ( @IsShowSpeci = 1 )
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,   --菜类
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo ,   --,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Dues4Food) as sumDues4Food,
                                        SUM(Charge4Food) AS sumCharge4Food --金额（折后，赠送为0）    
                                        ,
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                WHERE   f.CaptainsOrderName = '套餐单'
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,    --菜类
                                        FoodId ,
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo  --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;
                ELSE
                    BEGIN
                        INSERT  @returntable
                                SELECT  FoodId ,
                                        FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,   --菜类
                                        FoodNo ,
                                        FoodName ,
                                        Specification ,
                                        SpecificationNo ,   --,Price 菜例
                                        SUM(Quantity) AS sumQuantity ,      --数量
                                        SUM(Dues4Food) as sumDues4Food,
                                        SUM(Charge4Food) AS sumCharge4Food --金额（折后，赠送为0） 
                                        ,
                                        0
                                FROM    @Meal_VALLOrderFoods AS f
                                WHERE   f.CaptainsOrderName = '套餐单'
                                GROUP BY FoodTypeId ,
                                        FoodTypeNo ,
                                        FoodTypeName ,  --菜类
                                        Specification ,
                                        SpecificationNo ,  --,Price  菜例
                                        FoodId ,
                                        FoodNo ,
                                        FoodName --,Specification    --,Price  菜例
                                HAVING  SUM(Quantity) > 0; 
                        SELECT  @sumall = SUM(saleCharge4Food)
                        FROM    @returntable;
                    END;
            END; 
            
        UPDATE  @returntable
        SET     sumall = @sumall;
        RETURN
    END
GO

----------------------------------------------20181010（5.0.54）--------------------------------------------

ALTER    PROCEDURE [dbo].[Meal_newSortOrderFoods]   --菜例销售合计
    @beginBusinessDate DATETIME ,
    @endBusinessDate DATETIME ,
    @DepartmentNo VARCHAR(3) = '%' ,  --部门，为空表示全部
    @FoodTypeName NVARCHAR(20) = '%' ,   --菜类，为空表示全部
    @businessHourNo VARCHAR(10) = '%' ,
    @IsShowSpeci BIT = 1 , --是否显示多规格
    @SortByAmount BIT = 1 ,-- 是否按销售金额排序
    @IsNotIncludeSuit INT = 1 ,--是否含套餐菜例 1:全部，2 ：只统计单菜销售 3：只统计套餐中菜例销售
    @EatType VARCHAR(3) = '%'--就餐方式
AS
    SELECT  FoodTypeName    --菜类
            ,
            FoodId ,
            FoodNo ,
            FoodName ,
            Specification,    --,Price 菜例            
            SaleQuantity AS sumQuantity,       --数量
            saleDues4Food as sumDues4Food,
            saleCharge4Food AS sumCharge4Food
    INTO    #foodsalelist
    FROM    dbo.Meal_New_OrderFoodsStatNew('%', @beginBusinessDate,
                                           @endBusinessDate, @businessHourNo,
                                           @IsShowSpeci, @IsNotIncludeSuit,
                                           @EatType) --,1：原为已结账，时间为结账时间
    WHERE   ( FoodTypeName LIKE @FoodTypeName );
    
    
    DECLARE @allAmount NUMERIC(9, 2);
    DECLARE @allquantity NUMERIC(9, 2);
    DECLARE @allDues4Amount NUMERIC(9,2)
    SET @allAmount = ( SELECT   SUM(sumCharge4Food)
                       FROM     #foodsalelist AS a
                     );
    SET @allquantity = ( SELECT SUM(sumQuantity)
                         FROM   #foodsalelist AS a
                       );
    IF(@allDues4Amount IS NULL OR @allDues4Amount = 0)
		set @allDues4Amount = 1;
    IF ( @allAmount IS NULL
         OR @allAmount = 0
       )
        SET @allAmount = 1;
    IF ( @allquantity IS NULL
         OR @allquantity = 0
       )
        SET @allquantity = 1;
        
    --统计每个菜类的销售总金额和总数量    
    SELECT  FoodTypeName ,
            SUM(sumCharge4Food) AS typeamount ,
            SUM(sumDues4Food) as typeDues4Food,
            SUM(sumQuantity) AS typequantity
    INTO    #typelist
    FROM    #foodsalelist AS a
    GROUP BY FoodTypeName;
    
    
    IF ( @SortByAmount = 1 )
        BEGIN
            SELECT  a.FoodTypeName    --菜类
                    ,
                    a.FoodId ,
                    a.FoodNo ,
                    a.FoodName ,
                    a.Specification    --,Price 菜例
                    ,
                    a.sumQuantity ,
                    a.sumDues4Food,
                    a.sumCharge4Food ,
                    CONVERT(NUMERIC(9, 4), ( a.sumQuantity
                                             / ( CASE ISNULL(typequantity, 1)
                                                   WHEN 0 THEN 1
                                                   ELSE ISNULL(typequantity, 1)
                                                 END ) )) AS pretypequantity ,                                      
                    CONVERT(NUMERIC(9, 4), a.sumCharge4Food
                    / ( CASE ISNULL(typeamount, 1)
                          WHEN 0 THEN 1
                          ELSE ISNULL(typeamount, 1)
                        END )) AS pretypeamount ,
                    CONVERT(NUMERIC(9, 4), a.sumQuantity / @allquantity) AS preallquantity ,
                    CONVERT(NUMERIC(9, 4), a.sumCharge4Food / @allAmount) AS preallamount
            FROM    #foodsalelist AS a
                    LEFT JOIN #typelist AS b ON a.FoodTypeName = b.FoodTypeName
            ORDER BY a.FoodTypeName ,
                    a.sumCharge4Food DESC;
        END;
    ELSE
        BEGIN
            SELECT  a.FoodTypeName    --菜类
                    ,
                    a.FoodId ,
                    a.FoodNo ,
                    a.FoodName ,
                    a.Specification,    --,Price 菜例
                    a.sumQuantity ,
                    a.sumDues4Food,
                    a.sumCharge4Food ,
                    CONVERT(NUMERIC(9, 4), ( a.sumQuantity
                                             / ( CASE ISNULL(typequantity, 1)
                                                   WHEN 0 THEN 1
                                                   ELSE ISNULL(typequantity, 1)
                                                 END ) )) AS pretypequantity ,
                    CONVERT(NUMERIC(9, 4), a.sumCharge4Food
                    / ( CASE ISNULL(typeamount, 1)
                          WHEN 0 THEN 1
                          ELSE ISNULL(typeamount, 1)
                        END )) AS pretypeamount ,
                    CONVERT(NUMERIC(9, 4), a.sumQuantity / @allquantity) AS preallquantity ,
                    CONVERT(NUMERIC(9, 4), a.sumCharge4Food / @allAmount) AS preallamount
            FROM    #foodsalelist AS a
                    LEFT JOIN #typelist AS b ON a.FoodTypeName = b.FoodTypeName
            ORDER BY a.FoodTypeName ,
                    a.sumQuantity DESC;
        END

    RETURN 0

GO
IF NOT  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Meal_ThirdCertificate]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[Meal_ThirdCertificate](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ArrangedTableId] [uniqueidentifier] NOT NULL,
	[BillId] [uniqueidentifier] NOT NULL,
	[OrderNo] [varchar](32) NOT NULL default(''),
	[Title] [varchar](255) NOT NULL default(''),
	[isok] [bit] NOT NULL default(0),
	[Status] [nvarchar](10) NOT NULL default(''),
	[OperDatetime] [datetime] NULL,
	[Channel] [varchar](10) NOT NULL,
	[VerifyId] [varchar](32) NULL,
	[CertificateId] [varchar](32) NULL,
	[OriginalAmount] [decimal](9, 2) NOT NULL default(0),
	[MarketPrice] [decimal](9, 2) NOT NULL default(0),
                 [SubChannel] [varchar](25) NOT NULL DEFAULT (''),
 CONSTRAINT [PK_Meal_ThirdCertificate] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Certificates]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[Certificates](
	[Id] [uniqueidentifier] NOT NULL,
	[CertificateName] [nvarchar](200) NOT NULL,
	[Channel] [varchar](10) NOT NULL DEFAULT (''),
	[SubChannel] [varchar](25) NOT NULL DEFAULT (''),
	[IsInActive] [bit] NOT NULL DEFAULT (0),
 CONSTRAINT [PK_Certificates] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Certificate_Foods]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[Certificate_Foods](
	[Id] [uniqueidentifier] NOT NULL,
	[CertificateId] [uniqueidentifier] NOT NULL,
	[FoodId] [uniqueidentifier] NOT NULL,
	[Qty] [decimal](8, 2) NOT NULL DEFAULT (1),
	[IsSuit] [bit] NOT NULL DEFAULT (0)
) ON [PRIMARY]

GO

