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
评论