sql 查询

USE [FeiPiao_BookHotelRoom]
GO
/****** Object:  StoredProcedure [dbo].[FP_PR_OrderTicketList]    Script Date: 01/24/2014 11:26:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
 
-- =============================================
ALTER PROCEDURE [dbo].[FP_PR_t2]
 @OrderCode varchar(60),--订单号
 @OrderSource int,--订单来源
 @BusinessName varchar(100),--关联商户
 @CheckInUser VARCHAR(50),--预约姓名
 @CheckInUserPhone varchar(50),--预约手机号
 @PayBeginDate varchar(40),--订单付款开始时间
 @PayEndDate varchar(40),--订单付款结束时间
 @FsId INT,--供应商
 @ProductCode varchar(50),--产品编号
 @FinancialCode varchar(50),--结算编码
 @PNRCode varchar(50),--PNR码
 @bsID INT,--预约状态
 @FlightStatus int,--航班状态
 @FlightTicketCheckOutBeginDate varchar(40),--出票开始时间
 @FlightTicketCheckOutEndDate varchar(40),--出票结束时间
 @PayCode varchar(50),--支付编号
 @PayStatus int,--支付状态
 @ApplyPayBeginDate varchar(40),--申请支付开始时间
 @ApplyPayEndDate varchar(40),--申请支付结束时间
 @MakeUpOrderCode varchar(100),
 @PageSize int,
 @PageIndex int,
 @Total int output  
AS
BEGIN
     IF(@PayEndDate<>'')
     BEGIN
      SET @PayEndDate=@PayEndDate+' 23:59:59.999'
     END
  IF(@FlightTicketCheckOutEndDate<>'')
  BEGIN
   SET @FlightTicketCheckOutEndDate=@FlightTicketCheckOutEndDate+' 23:59:59.999'
  END
   IF(@ApplyPayEndDate<>'')
     BEGIN
      SET @ApplyPayEndDate=@ApplyPayEndDate+' 23:59:59.999'
     END
 
 
 IF(@PageSize=0 AND @PageIndex=0)
        BEGIN
                SET @Total=0
                SELECT *,ROW_NUMBER() OVER (order by FlightTicketCheckOutDate DESC) as cc FROM
                (
    SELECT f.TripStatus,f.LeaveFlightPrice,f.LeaveSettlementPrice,f.ReturnFlightPrice,f.ReturnSettlementPrice,f.Returnfnid,o.Ordercode,o.Productcode,o.OrderSource,uo.OrderTitle,p.FinancialCode,stb.CheckInUser,stb.CheckInUserPhone,sts.BookStatus,stb.Status,
    s.fsName AS Flightstatus,o.CreateDate,f.FlightPrice,f.FlightSettlementPrice,fs.Fs_Name,f.PNRCode,
                (SELECT MO.MakeUpOrderCode FROM FP_MakeUpOrderPrice_VM AS MO WHERE MO.OrderProcessID=f.OrderProcessID)AS MakeUpOrderCode,f.PayCode,f.PayDate,f.PayStatus,(CASE WHEN f.PayStatus=1 THEN '申请结算' WHEN f.PayStatus=2 THEN '已支付' ELSE '未结算' END) AS PayStatusName,f.OrderProcessID,f.RefundPrice,
                (f.FlightPrice+ISNULL((select SUM(Amountmakeup) from FP_OrderFlightsMakeUpExpand where OrderProcessID=f.OrderProcessID AND IsDelete=0),0)-f.FlightSettlementPrice-ISNULL(f.RefundPrice,0)) as Maori,
                ISNULL((select SUM(Amountmakeup) from FP_OrderFlightsMakeUpExpand where OrderProcessID=f.OrderProcessID AND IsDelete=0),0) AS Amountmakeup,f.FlightTicketCheckOutDate
                FROM FP_OrderFlightsMakeUp AS f INNER JOIN FP_ScenicTicketsBook AS stb ON f.OrderProcessID=stb.OrderProcessID
                INNER JOIN FP_OrderProcess o ON f.OrderProcessID=o.OrderProcessID 
                LEFT JOIN FP_FlightsSupplier AS fs ON f.FsId=fs.FsId
                LEFT JOIN FP_ScenicTicketStatus AS sts ON stb.Status=sts.bsID
                LEFT JOIN FP_OrderFlight_Status AS s ON f.FlightStatus=s.fsId
                LEFT JOIN TaoBao_Product AS p ON p.productcode=o.ProductCode LEFT JOIN FP_ProLinkBus_VW AS pv ON p.ProductCode=pv.ProductCode
                LEFT JOIN dbo.TaoBao_UserOrder AS uo ON uo.OrderCode=o.OrderCode
                WHERE  o.IsDelete=0 AND
                  CASE @OrderCode WHEN '' THEN @OrderCode ELSE o.OrderCode END = @OrderCode AND
                  CASE @BusinessName WHEN '' THEN @BusinessName ELSE pv.businesss END like'%'+@BusinessName+'%' AND
                  CASE @PNRCode WHEN '' THEN @PNRCode ELSE f.PNRCode END like '%'+@PNRCode+'%' AND
                  CASE @CheckInUser WHEN '' THEN @CheckInUser ELSE stb.CheckInUser END like @CheckInUser+'%' AND
      CASE @CheckInUserPhone WHEN '' THEN @CheckInUserPhone ELSE stb.CheckInUserPhone END like @CheckInUserPhone+'%' AND
      CASE @PayBeginDate WHEN '' THEN @PayBeginDate ELSE o.CreateDate END >=@PayBeginDate AND
      CASE @PayEndDate WHEN '' THEN @PayEndDate ELSE o.CreateDate END <=@PayEndDate AND
      CASE @FsId WHEN 0 THEN @FsId ELSE f.FsId END =@FsId AND
      CASE @ProductCode WHEN '' THEN @ProductCode ELSE o.ProductCode END = @ProductCode AND
      CASE @FinancialCode WHEN '' THEN @FinancialCode ELSE p.FinancialCode END = @FinancialCode AND
      CASE @bsID WHEN -1 THEN @bsID ELSE stb.Status END =@bsID AND
      CASE @FlightStatus WHEN -1 THEN @FlightStatus ELSE f.FlightStatus END=@FlightStatus AND
      CASE @OrderSource WHEN -1 THEN @OrderSource ELSE o.OrderSource END=@OrderSource AND
      CASE @PayStatus WHEN -1 THEN @PayStatus ELSE f.PayStatus END=@PayStatus AND
      CASE @PayCode WHEN '' THEN @PayCode ELSE f.PayCode END like '%'+@PayCode+'%' AND
      CASE @ApplyPayBeginDate WHEN '' THEN @ApplyPayBeginDate ELSE f.PayDate END >=@ApplyPayBeginDate AND
      CASE @ApplyPayEndDate WHEN '' THEN @ApplyPayEndDate ELSE f.PayDate END <=@ApplyPayEndDate  AND
      CASE @FlightTicketCheckOutBeginDate WHEN '' THEN @FlightTicketCheckOutBeginDate ELSE f.FlightTicketCheckOutDate END >=@FlightTicketCheckOutBeginDate AND
      CASE @FlightTicketCheckOutEndDate WHEN '' THEN @FlightTicketCheckOutEndDate ELSE f.FlightTicketCheckOutDate END <=@FlightTicketCheckOutEndDate
     
      UNION ALL
     
    SELECT f.TripStatus,f.LeaveFlightPrice,f.LeaveSettlementPrice,f.ReturnFlightPrice,f.ReturnSettlementPrice,f.Returnfnid,o.Ordercode,o.Productcode,o.OrderSource,uo.OrderTitle,p.FinancialCode,stb.CheckInUser,stb.CheckInUserPhone,sts.BookStatus,stb.Status,
    s.fsName AS Flightstatus,o.CreateDate,f.FlightPrice,f.FlightSettlementPrice,fs.Fs_Name,f.PNRCode,
                (SELECT MO.MakeUpOrderCode FROM FP_MakeUpOrderPrice_VM AS MO WHERE MO.OrderProcessID=f.OrderProcessID)AS MakeUpOrderCode,f.PayCode,f.PayDate,f.PayStatus,(CASE WHEN f.PayStatus=1 THEN '申请结算' WHEN f.PayStatus=2 THEN '已支付' ELSE '未结算' END) AS PayStatusName,f.OrderProcessID,f.RefundPrice,
                (f.FlightPrice+ISNULL((select SUM(Amountmakeup) from FP_OrderFlightsMakeUpExpand where OrderProcessID=f.OrderProcessID AND IsDelete=0),0)-f.FlightSettlementPrice-ISNULL(f.RefundPrice,0)) as Maori,
                ISNULL((select SUM(Amountmakeup) from FP_OrderFlightsMakeUpExpand where OrderProcessID=f.OrderProcessID AND IsDelete=0),0) AS Amountmakeup,f.FlightTicketCheckOutDate
                FROM FP_OrderFlightsMakeUp AS f INNER JOIN FP_ScenicTicketsBook AS stb ON f.OrderProcessID=stb.OrderProcessID
                INNER JOIN FP_OrderProcess_History o ON f.OrderProcessID=o.OrderProcessID 
                LEFT JOIN FP_FlightsSupplier AS fs ON f.FsId=fs.FsId
                LEFT JOIN FP_ScenicTicketStatus AS sts ON stb.Status=sts.bsID
                LEFT JOIN FP_OrderFlight_Status AS s ON f.FlightStatus=s.fsId
                LEFT JOIN TaoBao_Product AS p ON p.productcode=o.ProductCode LEFT JOIN FP_ProLinkBus_History_VW AS  pv ON p.ProductCode=pv.ProductCode
                LEFT JOIN dbo.TaoBao_UserOrder AS uo ON uo.OrderCode=o.OrderCode 
    WHERE  o.IsDelete=0 AND
                  CASE @OrderCode WHEN '' THEN @OrderCode ELSE o.OrderCode END = @OrderCode AND
                  CASE @BusinessName WHEN '' THEN @BusinessName ELSE pv.businesss END like'%'+@BusinessName+'%' AND
                  CASE @PNRCode WHEN '' THEN @PNRCode ELSE f.PNRCode END like '%'+@PNRCode+'%' AND
                  CASE @CheckInUser WHEN '' THEN @CheckInUser ELSE stb.CheckInUser END like @CheckInUser+'%' AND
      CASE @CheckInUserPhone WHEN '' THEN @CheckInUserPhone ELSE stb.CheckInUserPhone END like @CheckInUserPhone+'%' AND
      CASE @PayBeginDate WHEN '' THEN @PayBeginDate ELSE o.CreateDate END >=@PayBeginDate AND
      CASE @PayEndDate WHEN '' THEN @PayEndDate ELSE o.CreateDate END <=@PayEndDate AND
      CASE @FsId WHEN 0 THEN @FsId ELSE f.FsId END =@FsId AND
      CASE @ProductCode WHEN '' THEN @ProductCode ELSE o.ProductCode END = @ProductCode AND
      CASE @FinancialCode WHEN '' THEN @FinancialCode ELSE p.FinancialCode END = @FinancialCode AND
      CASE @bsID WHEN -1 THEN @bsID ELSE stb.Status END =@bsID AND
      CASE @FlightStatus WHEN -1 THEN @FlightStatus ELSE f.FlightStatus END=@FlightStatus AND
      CASE @OrderSource WHEN -1 THEN @OrderSource ELSE o.OrderSource END=@OrderSource AND
      CASE @PayStatus WHEN -1 THEN @PayStatus ELSE f.PayStatus END=@PayStatus AND
      CASE @PayCode WHEN '' THEN @PayCode ELSE f.PayCode END like '%'+@PayCode+'%' AND
      CASE @ApplyPayBeginDate WHEN '' THEN @ApplyPayBeginDate ELSE f.PayDate END >=@ApplyPayBeginDate AND
      CASE @ApplyPayEndDate WHEN '' THEN @ApplyPayEndDate ELSE f.PayDate END <=@ApplyPayEndDate  AND
      CASE @FlightTicketCheckOutBeginDate WHEN '' THEN @FlightTicketCheckOutBeginDate ELSE f.FlightTicketCheckOutDate END >=@FlightTicketCheckOutBeginDate AND
      CASE @FlightTicketCheckOutEndDate WHEN '' THEN @FlightTicketCheckOutEndDate ELSE f.FlightTicketCheckOutDate END <=@FlightTicketCheckOutEndDate    
    ) AS T  WHERE CASE @MakeUpOrderCode WHEN '' THEN @MakeUpOrderCode ELSE MakeUpOrderCode END like'%'+@MakeUpOrderCode+'%'   
        END
   ELSE
       BEGIN
          --数量
              SELECT @Total=ISNULL(COUNT(1),0) FROM
              (
                SELECT * FROM (
                SELECT (SELECT MO.MakeUpOrderCode FROM FP_MakeUpOrderPrice_VM AS MO WHERE MO.OrderProcessID=f.OrderProcessID)AS MakeUpOrderCode  FROM FP_OrderFlightsMakeUp AS f INNER JOIN FP_ScenicTicketsBook AS stb ON f.OrderProcessID=stb.OrderProcessID
                INNER JOIN FP_OrderProcess  AS o ON f.OrderProcessID  =o.OrderProcessID 
                LEFT JOIN FP_FlightsSupplier AS fs ON f.FsId=fs.FsId
                LEFT JOIN FP_ScenicTicketStatus AS sts ON stb.Status=sts.bsID
                LEFT JOIN FP_OrderFlight_Status AS s ON f.FlightStatus=s.fsId
                LEFT JOIN TaoBao_Product AS p ON p.productcode=o.ProductCode LEFT JOIN FP_ProLinkBus_VW pv ON p.ProductCode=pv.ProductCode
                LEFT JOIN dbo.TaoBao_UserOrder AS uo ON uo.OrderCode=o.OrderCode
                WHERE   o.IsDelete=0 AND
                  CASE @OrderCode WHEN '' THEN @OrderCode ELSE o.OrderCode END = @OrderCode AND
                  CASE @BusinessName WHEN '' THEN @BusinessName ELSE pv.businesss END like'%'+@BusinessName+'%' AND
                  CASE @PNRCode WHEN '' THEN @PNRCode ELSE f.PNRCode END like '%'+@PNRCode+'%' AND
                  CASE @CheckInUser WHEN '' THEN @CheckInUser ELSE stb.CheckInUser END like @CheckInUser+'%' AND
      CASE @CheckInUserPhone WHEN '' THEN @CheckInUserPhone ELSE stb.CheckInUserPhone END like @CheckInUserPhone+'%' AND
      CASE @PayBeginDate WHEN '' THEN @PayBeginDate ELSE o.CreateDate END >=@PayBeginDate AND
      CASE @PayEndDate WHEN '' THEN @PayEndDate ELSE o.CreateDate END <=@PayEndDate AND
      CASE @FsId WHEN 0 THEN @FsId ELSE f.FsId END =@FsId AND
      CASE @ProductCode WHEN '' THEN @ProductCode ELSE o.ProductCode END = @ProductCode AND
      CASE @FinancialCode WHEN '' THEN @FinancialCode ELSE p.FinancialCode END = @FinancialCode AND
      CASE @bsID WHEN -1 THEN @bsID ELSE stb.Status END =@bsID AND
      CASE @FlightStatus WHEN -1 THEN @FlightStatus ELSE f.FlightStatus END=@FlightStatus AND
      CASE @OrderSource WHEN -1 THEN @OrderSource ELSE o.OrderSource END=@OrderSource AND
      CASE @PayStatus WHEN -1 THEN @PayStatus ELSE f.PayStatus END=@PayStatus AND
      CASE @PayCode WHEN '' THEN @PayCode ELSE f.PayCode END like '%'+@PayCode+'%' AND
      CASE @ApplyPayBeginDate WHEN '' THEN @ApplyPayBeginDate ELSE f.PayDate END >=@ApplyPayBeginDate AND
      CASE @ApplyPayEndDate WHEN '' THEN @ApplyPayEndDate ELSE f.PayDate END <=@ApplyPayEndDate  AND
      CASE @FlightTicketCheckOutBeginDate WHEN '' THEN @FlightTicketCheckOutBeginDate ELSE f.FlightTicketCheckOutDate END >=@FlightTicketCheckOutBeginDate AND
      CASE @FlightTicketCheckOutEndDate WHEN '' THEN @FlightTicketCheckOutEndDate ELSE f.FlightTicketCheckOutDate END <=@FlightTicketCheckOutEndDate  
      UNION ALL
       SELECT (SELECT MO.MakeUpOrderCode FROM FP_MakeUpOrderPrice_VM AS MO WHERE MO.OrderProcessID=f.OrderProcessID)AS MakeUpOrderCode FROM FP_OrderFlightsMakeUp AS f INNER JOIN FP_ScenicTicketsBook AS stb ON f.OrderProcessID=stb.OrderProcessID
                INNER JOIN FP_OrderProcess_History o ON f.OrderProcessID=o.OrderProcessID 
                LEFT JOIN FP_FlightsSupplier AS fs ON f.FsId=fs.FsId
                LEFT JOIN FP_ScenicTicketStatus AS sts ON stb.Status=sts.bsID
                LEFT JOIN FP_OrderFlight_Status AS s ON f.FlightStatus=s.fsId
                LEFT JOIN TaoBao_Product AS p ON p.productcode=o.ProductCode LEFT JOIN FP_ProLinkBus_History_VW AS  pv ON p.ProductCode=pv.ProductCode
                LEFT JOIN dbo.TaoBao_UserOrder AS uo ON uo.OrderCode=o.OrderCode 
                WHERE   o.IsDelete=0 AND
                  CASE @OrderCode WHEN '' THEN @OrderCode ELSE o.OrderCode END = @OrderCode AND
                  CASE @BusinessName WHEN '' THEN @BusinessName ELSE pv.businesss END like'%'+@BusinessName+'%' AND
                  CASE @PNRCode WHEN '' THEN @PNRCode ELSE f.PNRCode END like '%'+@PNRCode+'%' AND
                  CASE @CheckInUser WHEN '' THEN @CheckInUser ELSE stb.CheckInUser END like @CheckInUser+'%' AND
      CASE @CheckInUserPhone WHEN '' THEN @CheckInUserPhone ELSE stb.CheckInUserPhone END like @CheckInUserPhone+'%' AND
      CASE @PayBeginDate WHEN '' THEN @PayBeginDate ELSE o.CreateDate END >=@PayBeginDate AND
      CASE @PayEndDate WHEN '' THEN @PayEndDate ELSE o.CreateDate END <=@PayEndDate AND
      CASE @FsId WHEN 0 THEN @FsId ELSE f.FsId END =@FsId AND
      CASE @ProductCode WHEN '' THEN @ProductCode ELSE o.ProductCode END = @ProductCode AND
      CASE @FinancialCode WHEN '' THEN @FinancialCode ELSE p.FinancialCode END = @FinancialCode AND
      CASE @bsID WHEN -1 THEN @bsID ELSE stb.Status END =@bsID AND
      CASE @FlightStatus WHEN -1 THEN @FlightStatus ELSE f.FlightStatus END=@FlightStatus AND
      CASE @OrderSource WHEN -1 THEN @OrderSource ELSE o.OrderSource END=@OrderSource AND
      CASE @PayStatus WHEN -1 THEN @PayStatus ELSE f.PayStatus END=@PayStatus AND
      CASE @PayCode WHEN '' THEN @PayCode ELSE f.PayCode END like '%'+@PayCode+'%' AND
      CASE @ApplyPayBeginDate WHEN '' THEN @ApplyPayBeginDate ELSE f.PayDate END >=@ApplyPayBeginDate AND
      CASE @ApplyPayEndDate WHEN '' THEN @ApplyPayEndDate ELSE f.PayDate END <=@ApplyPayEndDate  AND
      CASE @FlightTicketCheckOutBeginDate WHEN '' THEN @FlightTicketCheckOutBeginDate ELSE f.FlightTicketCheckOutDate END >=@FlightTicketCheckOutBeginDate AND
      CASE @FlightTicketCheckOutEndDate WHEN '' THEN @FlightTicketCheckOutEndDate ELSE f.FlightTicketCheckOutDate END <=@FlightTicketCheckOutEndDate
      )AS MOP WHERE CASE @MakeUpOrderCode WHEN '' THEN @MakeUpOrderCode ELSE MakeUpOrderCode END like'%'+@MakeUpOrderCode+'%'
      ) AS T  
             
   --查询数据
   SELECT * FROM (
   SELECT *,ROW_NUMBER() OVER (order by FlightTicketCheckOutDate DESC) as cc FROM (
    SELECT f.TripStatus,f.LeaveFlightPrice,f.LeaveSettlementPrice,f.ReturnFlightPrice,f.ReturnSettlementPrice,f.Returnfnid,o.Ordercode,o.Productcode,o.OrderSource,uo.OrderTitle,p.FinancialCode,stb.CheckInUser,stb.CheckInUserPhone,sts.BookStatus,stb.Status,s.fsName AS Flightstatus,o.CreateDate,f.FlightPrice,f.FlightSettlementPrice,fs.Fs_Name,
                f.PNRCode,f.OrderProcessID,f.PayCode,f.PayDate,f.PayStatus,(CASE WHEN f.PayStatus=1 THEN '申请结算' WHEN f.PayStatus=2 THEN '已支付' ELSE '未结算' END) AS PayStatusName,f.RefundPrice,
                (f.FlightPrice+ISNULL((select SUM(Amountmakeup) from FP_OrderFlightsMakeUpExpand where OrderProcessID=f.OrderProcessID AND IsDelete=0),0)-f.FlightSettlementPrice-ISNULL(f.RefundPrice,0)) as Maori,
                (SELECT MO.MakeUpOrderCode FROM FP_MakeUpOrderPrice_VM AS MO WHERE MO.OrderProcessID=f.OrderProcessID)AS MakeUpOrderCode,
                ISNULL((select SUM(Amountmakeup) from FP_OrderFlightsMakeUpExpand where OrderProcessID=f.OrderProcessID AND IsDelete=0),0) AS Amountmakeup,
                f.ofmuId,f.FlightSnsStatus,f.FlightDate,f.FlightNumber,f.FlightTicketCheckOutDate
                FROM FP_OrderFlightsMakeUp AS f INNER JOIN FP_ScenicTicketsBook AS stb ON f.OrderProcessID=stb.OrderProcessID
                INNER JOIN FP_OrderProcess  AS o ON f.OrderProcessID  =o.OrderProcessID 
                LEFT JOIN FP_FlightsSupplier AS fs ON f.FsId=fs.FsId
                LEFT JOIN FP_ScenicTicketStatus AS sts ON stb.Status=sts.bsID
                LEFT JOIN FP_OrderFlight_Status AS s ON f.FlightStatus=s.fsId
                LEFT JOIN TaoBao_Product AS p ON p.productcode=o.ProductCode LEFT JOIN FP_ProLinkBus_VW pv ON p.ProductCode=pv.ProductCode
                LEFT JOIN dbo.TaoBao_UserOrder AS uo ON uo.OrderCode=o.OrderCode
                WHERE  o.IsDelete=0 AND
                  CASE @OrderCode WHEN '' THEN @OrderCode ELSE o.OrderCode END = @OrderCode AND
                  CASE @BusinessName WHEN '' THEN @BusinessName ELSE pv.businesss END like'%'+@BusinessName+'%' AND
                  CASE @PNRCode WHEN '' THEN @PNRCode ELSE f.PNRCode END like '%'+@PNRCode+'%' AND
                  CASE @CheckInUser WHEN '' THEN @CheckInUser ELSE stb.CheckInUser END like @CheckInUser+'%' AND
      CASE @CheckInUserPhone WHEN '' THEN @CheckInUserPhone ELSE stb.CheckInUserPhone END like @CheckInUserPhone+'%' AND
      CASE @PayBeginDate WHEN '' THEN @PayBeginDate ELSE o.CreateDate END >=@PayBeginDate AND
      CASE @PayEndDate WHEN '' THEN @PayEndDate ELSE o.CreateDate END <=@PayEndDate AND
      CASE @FsId WHEN 0 THEN @FsId ELSE f.FsId END =@FsId AND
      CASE @ProductCode WHEN '' THEN @ProductCode ELSE o.ProductCode END = @ProductCode AND
      CASE @FinancialCode WHEN '' THEN @FinancialCode ELSE p.FinancialCode END = @FinancialCode AND
      CASE @bsID WHEN -1 THEN @bsID ELSE stb.Status END =@bsID AND
      CASE @FlightStatus WHEN -1 THEN @FlightStatus ELSE f.FlightStatus END=@FlightStatus AND
      CASE @OrderSource WHEN -1 THEN @OrderSource ELSE o.OrderSource END=@OrderSource AND
      CASE @PayStatus WHEN -1 THEN @PayStatus ELSE f.PayStatus END=@PayStatus AND
      CASE @PayCode WHEN '' THEN @PayCode ELSE f.PayCode END like '%'+@PayCode+'%' AND
      CASE @ApplyPayBeginDate WHEN '' THEN @ApplyPayBeginDate ELSE f.PayDate END >=@ApplyPayBeginDate AND
      CASE @ApplyPayEndDate WHEN '' THEN @ApplyPayEndDate ELSE f.PayDate END <=@ApplyPayEndDate  AND
      CASE @FlightTicketCheckOutBeginDate WHEN '' THEN @FlightTicketCheckOutBeginDate ELSE f.FlightTicketCheckOutDate END >=@FlightTicketCheckOutBeginDate AND
      CASE @FlightTicketCheckOutEndDate WHEN '' THEN @FlightTicketCheckOutEndDate ELSE f.FlightTicketCheckOutDate END <=@FlightTicketCheckOutEndDate  
     UNION ALL
    
     SELECT f.TripStatus,f.LeaveFlightPrice,f.LeaveSettlementPrice,f.ReturnFlightPrice,f.ReturnSettlementPrice,f.Returnfnid,o.Ordercode,o.Productcode,o.OrderSource,uo.OrderTitle,p.FinancialCode,stb.CheckInUser,stb.CheckInUserPhone,sts.BookStatus,stb.Status,s.fsName AS Flightstatus,o.CreateDate,f.FlightPrice,f.FlightSettlementPrice,fs.Fs_Name,
                f.PNRCode,f.OrderProcessID,f.PayCode,f.PayDate,f.PayStatus,(CASE WHEN f.PayStatus=1 THEN '申请结算' WHEN f.PayStatus=2 THEN '已支付' ELSE '未结算' END) AS PayStatusName,f.RefundPrice,
                (f.FlightPrice+ISNULL((select SUM(Amountmakeup) from FP_OrderFlightsMakeUpExpand where OrderProcessID=f.OrderProcessID AND IsDelete=0),0)-f.FlightSettlementPrice-ISNULL(f.RefundPrice,0)) as Maori,
                (SELECT MO.MakeUpOrderCode FROM FP_MakeUpOrderPrice_VM AS MO WHERE MO.OrderProcessID=f.OrderProcessID)AS MakeUpOrderCode,
                ISNULL((select SUM(Amountmakeup) from FP_OrderFlightsMakeUpExpand where OrderProcessID=f.OrderProcessID AND IsDelete=0),0)AS Amountmakeup,
                f.ofmuId,f.FlightSnsStatus,f.FlightDate,f.FlightNumber,f.FlightTicketCheckOutDate
                FROM FP_OrderFlightsMakeUp AS f INNER JOIN FP_ScenicTicketsBook AS stb ON f.OrderProcessID=stb.OrderProcessID
                INNER JOIN FP_OrderProcess_History  AS o ON f.OrderProcessID  =o.OrderProcessID 
                LEFT JOIN FP_FlightsSupplier AS fs ON f.FsId=fs.FsId
                LEFT JOIN FP_ScenicTicketStatus AS sts ON stb.Status=sts.bsID
                LEFT JOIN FP_OrderFlight_Status AS s ON f.FlightStatus=s.fsId
                LEFT JOIN TaoBao_Product AS p ON p.productcode=o.ProductCode LEFT JOIN FP_ProLinkBus_History_VW AS  pv ON p.ProductCode=pv.ProductCode
                LEFT JOIN dbo.TaoBao_UserOrder AS uo ON uo.OrderCode=o.OrderCode
    WHERE  o.IsDelete=0 AND
                  CASE @OrderCode WHEN '' THEN @OrderCode ELSE o.OrderCode END = @OrderCode AND
                  CASE @BusinessName WHEN '' THEN @BusinessName ELSE pv.businesss END like'%'+@BusinessName+'%' AND
                  CASE @PNRCode WHEN '' THEN @PNRCode ELSE f.PNRCode END like '%'+@PNRCode+'%' AND
                  CASE @CheckInUser WHEN '' THEN @CheckInUser ELSE stb.CheckInUser END like @CheckInUser+'%' AND
      CASE @CheckInUserPhone WHEN '' THEN @CheckInUserPhone ELSE stb.CheckInUserPhone END like @CheckInUserPhone+'%' AND
      CASE @PayBeginDate WHEN '' THEN @PayBeginDate ELSE o.CreateDate END >=@PayBeginDate AND
      CASE @PayEndDate WHEN '' THEN @PayEndDate ELSE o.CreateDate END <=@PayEndDate AND
      CASE @FsId WHEN 0 THEN @FsId ELSE f.FsId END =@FsId AND
      CASE @ProductCode WHEN '' THEN @ProductCode ELSE o.ProductCode END = @ProductCode AND
      CASE @FinancialCode WHEN '' THEN @FinancialCode ELSE p.FinancialCode END = @FinancialCode AND
      CASE @bsID WHEN -1 THEN @bsID ELSE stb.Status END =@bsID AND
      CASE @FlightStatus WHEN -1 THEN @FlightStatus ELSE f.FlightStatus END=@FlightStatus AND
      CASE @OrderSource WHEN -1 THEN @OrderSource ELSE o.OrderSource END=@OrderSource AND
      CASE @PayStatus WHEN -1 THEN @PayStatus ELSE f.PayStatus END=@PayStatus AND
      CASE @PayCode WHEN '' THEN @PayCode ELSE f.PayCode END like '%'+@PayCode+'%' AND
      CASE @ApplyPayBeginDate WHEN '' THEN @ApplyPayBeginDate ELSE f.PayDate END >=@ApplyPayBeginDate AND
      CASE @ApplyPayEndDate WHEN '' THEN @ApplyPayEndDate ELSE f.PayDate END <=@ApplyPayEndDate  AND
      CASE @FlightTicketCheckOutBeginDate WHEN '' THEN @FlightTicketCheckOutBeginDate ELSE f.FlightTicketCheckOutDate END >=@FlightTicketCheckOutBeginDate AND
      CASE @FlightTicketCheckOutEndDate WHEN '' THEN @FlightTicketCheckOutEndDate ELSE f.FlightTicketCheckOutDate END <=@FlightTicketCheckOutEndDate      
   ) AS SER WHERE CASE @MakeUpOrderCode WHEN '' THEN @MakeUpOrderCode ELSE MakeUpOrderCode END like'%'+@MakeUpOrderCode+'%'     
   )AS c
   WHERE cc > (@PageIndex - 1) * @PageSize and cc <=@PageIndex * @PageSize
       END
END