USE [MarketingDB] GO /****** Object: StoredProcedure [dbo].[sp1_SendFlightEmail_select] Script Date: 06/26/2012 13:21:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* Subsystem : Sp name : [dbo].[sp1_SendFlightEmail_select] Database : marketingDB Description: DB access : DB update : Input : Dependency : ########## Change Log ########## Date Changer Description -------------------------------------------------------------------------------------- 2010-07-23 leil [85981]酒推机订单选择 2010-11-10 LEIL [98071]酒推机订单选择 2010-12-23 LEIL [128102]酒推机订单选择 2011-04-07 fxia [114662]a、订单提交后10分钟及时处理 b、系统判断客户是否预订相同城市的机票 的时间为负24小时 c、增加单独的城市判断，即判断城市是否发机票推荐 -------------------------------------------------------------------------------------- */ CREATE PROCEDURE [dbo].[sp1_SendFlightEmail_select] @diffhours int, ---时间段，单位小时 @PrepayType varchar(50)=NULL, ---支付方式 @lowerlimit INT, ---时间段下限 @upperlimit INT, ---时间段上限 @upperlimit1 INT ---时间段上限1 AS DECLARE @retcode int, @rowcount int SET NOCOUNT ON SET LOCK_TIMEOUT 10000000 IF @PrepayType!='' SELECT DISTINCT TOP (100) PERCENT ho.OrderId, ho.Uid, ho.OrderDate, ho.OrderStatus, ho.CityId, ho.IsSendMail , ce.IsSendEmail, ho.ContactEmail, ho.ContactName, ho.MobilePhone, ho.PrepayType, ho.CreditCardType, ce.district AS DistrictId, ce.PromoteId, ce.CityName, ce.Citycode, ce.ename, ce.CreatedBy, ce.IsFlightSendEmail, CASE ls.uid WHEN ls.uid THEN 'T' ELSE 'F' end AS isInLS,--ISNULL(ls.UID,'F') AS isInLS, CASE lC.uid WHEN lC.uid THEN 'T' ELSE 'F' end AS isInLC FROM dbo.Hotel_Order AS ho WITH (nolock) INNER JOIN dbo.V_City AS ce WITH (nolock) ON ho.CityId = ce.City AND ce.CreatedBy IS NOT NULL AND ce.IsFlightSendEmail=1 LEFT JOIN (SELECT UID FROM dbo.Log_Succeed WITH(NOLOCK) WHERE CreatedOn>=DATEADD(DAY,-15,GETDATE()) AND TemplateId=201 ) ls ON ls.UID=ho.Uid LEFT JOIN (SELECT UID FROM dbo.Log_Contrast WITH(NOLOCK) WHERE CreatedOn>=DATEADD(DAY,-15,GETDATE()) AND TemplateId=201 ) lc ON lc.UID=ho.Uid WHERE (ho.IsSendMail = 0) AND ho.OrderDate<DateAdd(mi,@upperlimit,GetDate()) -- 0 and ho.OrderDate>=DateAdd(mi,@lowerlimit,GetDate()) -- -10分钟 and NOT EXISTS( SELECT 1 FROM dbo.Flight_Order fo1 WITH(nolock) JOIN dbo.Flight_SubOrder fso1 WITH(nolock) ON fo1.OrderID=fso1.OrderId WHERE fo1.UID=ho.Uid AND fo1.OrderDate >=DATEADD(mi,@upperlimit1,ho.orderDate) --24 AND fo1.OrderDate <=DATEADD(mi,@upperlimit,ho.orderDate) --0 AND fso1.ACityID=ho.CityId ) and ho.PrepayType=@PrepayType ORDER BY ho.CITYID ELSE SELECT DISTINCT TOP (100) PERCENT ho.OrderId, ho.Uid, ho.OrderDate, ho.OrderStatus, ho.CityId, ho.IsSendMail, ho.ContactEmail, ho.ContactName, ho.MobilePhone, ho.PrepayType, ho.CreditCardType, ce.district AS DistrictId, ce.PromoteId, ce.CityName, ce.Citycode, ce.ename, ce.CreatedBy, ce.IsFlightSendEmail, CASE ls.uid WHEN ls.uid THEN 'T' ELSE 'F' end AS isInLS,--ISNULL(ls.UID,'F') AS isInLS, CASE lC.uid WHEN lC.uid THEN 'T' ELSE 'F' end AS isInLC FROM dbo.Hotel_Order AS ho WITH (nolock) INNER JOIN dbo.V_City AS ce WITH (nolock) ON ho.CityId = ce.City AND ce.CreatedBy IS NOT NULL AND ce.IsFlightSendEmail=1 LEFT JOIN (SELECT UID FROM dbo.Log_Succeed WITH(NOLOCK) WHERE CreatedOn>=DATEADD(DAY,-15,GETDATE()) AND TemplateId=201 ) ls ON ls.UID=ho.Uid LEFT JOIN (SELECT UID FROM dbo.Log_Contrast WITH(NOLOCK) WHERE CreatedOn>=DATEADD(DAY,-15,GETDATE()) AND TemplateId=201 ) lc ON lc.UID=ho.Uid WHERE (ho.IsSendMail = 0) AND ho.OrderDate<DateAdd(mi,@upperlimit,GetDate()) -- 0 mi and ho.OrderDate>=DateAdd(mi,@lowerlimit,GetDate()) -- -10 mi and NOT EXISTS( SELECT 1 FROM dbo.Flight_Order fo1 WITH(nolock) JOIN dbo.Flight_SubOrder fso1 WITH(nolock) ON fo1.OrderID=fso1.OrderId WHERE fo1.UID=ho.Uid AND fo1.OrderDate >=DATEADD(mi,@upperlimit1,ho.orderDate) -- -24 hour AND fo1.OrderDate <=DATEADD(mi,@upperlimit,ho.orderDate) --0 mi AND fso1.ACityID=ho.CityId ) ORDER BY ho.CITYID GO
评论