/*--=============================
功能：拜访类统计
输入：
返回：
作者：ding_0910
时间：2006/06/10 17:44
说明：创建
*/--=============================
CREATE PROCEDURE dbo.StatFollow_F
@UserId int,
@BeginDate datetime,
@EndDate datetime,
@BeginLunarMonth int,
@BeginLunarDay int,
@EndLunarMonth int,
@EndLunarDay int
AS
/*---示例参数值
declare @UserId int
select @UserId = 47
declare @BeginDate datetime
select @BeginDate = '2006-04-01'
declare @EndDate datetime
select @EndDate = '2007-03-01'
declare @BeginLunarMonth int
select @BeginLunarMonth = 0
declare @BeginLunarDay int
select @BeginLunarDay = 0
declare @EndLunarMonth int
select @EndLunarMonth = 0
declare @EndLunarDay int
select @EndLunarDay = 0
*/
--//获取拜访参数//
declare @spanNewCu int
declare @spanAfterBuy int
declare @spanUseUp int
declare @spanNoContact int
declare @switchNewCU bit
declare @switchAfterBuy bit
declare @switchUseUp bit
declare @switchNoContact bit
declare @switchAnniversary bit
declare @switchBirthday bit
declare @switchAllFollow bit
SELECT @spanNewCu=[FollowParam_NewCustomer],
@spanAfterBuy=[FollowParam_AfterBuy],
@spanUseUp=[FollowParam_UseUp],
@spanNoContact=[FollowParam_NoContact],
@switchAnniversary=[FollowParam_Anniversary],
@switchBirthday=[FollowParam_Birthday],
@switchNewCU=[switchNewCU],
@switchAfterBuy=[switchAfterBuy],
@switchUseUp=[switchUseUp],
@switchNoContact=[switchNoContact],
@switchAllFollow=[switchAllFollow]
FROM [dbo].[FW_FollowParam] where [User_ID]=@UserId
--//新进客户
SELECT '新进客户' AS FollowCause, COUNT(Customer_ID) AS FollowNum
FROM dbo.CU_Customer
WHERE DATEADD(day,@spanNewCu,Customer_AddDate)< @EndDate+1
AND DATEADD(day,@spanNewCu,Customer_AddDate)>= @BeginDate
--AND Customer_NewGJFlag In (0,1,2) --0:未跟进,1:已跟进,2:有待再次更进
--AND XJ_ID=@XJ_Id AND CustomerType_ID=@CU_Type
AND [User_ID]=@UserId And @switchNewCU=1 And @switchAllFollow=1
Union All
--//售后关怀
SELECT '售后关怀' AS FollowCause, COUNT(O.ProductOUT_ID) AS FollowNum
FROM dbo.PR_ProductOUT O
INNER JOIN dbo.CU_Customer C ON O.Customer_ID=C.Customer_Id
WHERE DATEADD(D,@spanAfterBuy,ProductOUT_Date)<= @EndDate
AND DATEADD(D,@spanAfterBuy,ProductOUT_Date)>= @BeginDate
AND ProductOUT_NewGJFlag In(1,2) --产品购买后跟进标记（0:不跟进;1:跟进;2已经跟进）
AND InventoryType_Id=dbo.GetInventoryTypeID(@UserId,10)--只有销售的东西才会跟进
AND O.[User_ID]=@UserId And @switchAfterBuy=1 And @switchAllFollow=1
Union All
--//购买提醒
SELECT '购买提醒' AS FollowCause, COUNT(O.ProductOUT_ID) AS FollowNum
FROM dbo.PR_ProductOUT O
INNER JOIN dbo.CU_Customer C ON O.Customer_ID=C.Customer_Id
WHERE DATEADD(D,0-@spanUseUp,DATEADD(D,ProductOut_CostDay,ProductOUT_Date))<= @EndDate
AND DATEADD(D,0-@spanUseUp,DATEADD(D,ProductOut_CostDay,ProductOUT_Date))>= @BeginDate
AND ProductOUT_FinishGJFlag In(1,2) --产品使用完成后跟进标记（0:不跟进;1:跟进;2已经跟进）
AND InventoryType_Id=dbo.GetInventoryTypeID(@UserId,10)--只有销售的东西才会跟进
AND O.[User_ID]=@UserId And @switchUseUp=1 And @switchAllFollow=1
/*
Union All
--//久未联系
SELECT '久未联系' AS FollowCause, COUNT(C.Customer_ID) AS FollowNum
FROM dbo.CU_Customer C
WHERE DATEADD(D,@spanNoContact,Customer_LastContactDate)<= @EndDate+1
AND DATEADD(D,@spanNoContact,Customer_LastContactDate)>= @BeginDate
AND C.[User_ID]=@UserId And @switchNoContact=1 And @switchAllFollow=1
*/
Union All
--//记念日祝贺
SELECT '记念日祝贺' AS FollowCause, COUNT(Customer_ID) AS FollowNum
From (
SELECT C.Customer_ID --/结婚记念日
FROM dbo.CU_CustomerFamily F INNER JOIN dbo.CU_Customer C ON F.Customer_ID = C.Customer_ID
WHERE
(
CustomerFamily_MarryType=1 --公历跨年
And (month(@BeginDate)*100+day(@BeginDate))>(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_MarryYear)*100+day(CustomerFamily_MarryYear))
Between (month(@BeginDate)*100+day(@BeginDate)) And (12*100+31) --开始日期至上年底
OR
(month(CustomerFamily_MarryYear)*100+day(CustomerFamily_MarryYear))
Between (1*100+1) And (month(@EndDate)*100+day(@EndDate)) --本年初至结束日期
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_MarryType=1 --公历不跨年
And (month(@BeginDate)*100+day(@BeginDate))<(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_MarryYear)*100+day(CustomerFamily_MarryYear))
Between (month(@BeginDate)*100+day(@BeginDate)) And (month(@EndDate)*100+day(@EndDate))
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_MarryType=0 --农历跨年
And (@BeginLunarMonth*100+@BeginLunarDay)>(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_MarryLunarMonth*100+CustomerFamily_MarryLunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (12*100+31)
OR
(CustomerFamily_MarryLunarMonth*100+CustomerFamily_MarryLunarDate)
Between (1*100+1) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_MarryType=0 --农历不跨年
And (@BeginLunarMonth*100+@BeginLunarDay)<(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_MarryLunarMonth*100+CustomerFamily_MarryLunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
UNION ALL
SELECT C.Customer_ID --/其他记念日1
FROM dbo.CU_CustomerFamily F INNER JOIN dbo.CU_Customer C ON F.Customer_ID = C.Customer_ID
WHERE
(
CustomerFamily_OtherAnniversary1Type=1 --公历跨年
And (month(@BeginDate)*100+day(@BeginDate))>(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_OtherAnniversary1)*100+day(CustomerFamily_OtherAnniversary1))
Between (month(@BeginDate)*100+day(@BeginDate)) And (12*100+31) --开始日期至上年底
OR
(month(CustomerFamily_OtherAnniversary1)*100+day(CustomerFamily_OtherAnniversary1))
Between (1*100+1) And (month(@EndDate)*100+day(@EndDate)) --本年初至结束日期
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary1Type=1 --公历不跨年
And (month(@BeginDate)*100+day(@BeginDate))<(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_OtherAnniversary1)*100+day(CustomerFamily_OtherAnniversary1))
Between (month(@BeginDate)*100+day(@BeginDate)) And (month(@EndDate)*100+day(@EndDate))
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary1Type=0 --农历跨年
And (@BeginLunarMonth*100+@BeginLunarDay)>(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_OtherAnniversary1LunarMonth*100+CustomerFamily_OtherAnniversary1LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (12*100+31)
OR
(CustomerFamily_OtherAnniversary1LunarMonth*100+CustomerFamily_OtherAnniversary1LunarDate)
Between (1*100+1) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary1Type=0 --农历不跨年
And (@BeginLunarMonth*100+@BeginLunarDay)<(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_OtherAnniversary1LunarMonth*100+CustomerFamily_OtherAnniversary1LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
UNION ALL
SELECT C.Customer_ID --/其他记念日2
FROM dbo.CU_CustomerFamily F INNER JOIN dbo.CU_Customer C ON F.Customer_ID = C.Customer_ID
WHERE
(
CustomerFamily_OtherAnniversary2Type=1 --公历跨年
And (month(@BeginDate)*100+day(@BeginDate))>(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_OtherAnniversary2)*100+day(CustomerFamily_OtherAnniversary2))
Between (month(@BeginDate)*100+day(@BeginDate)) And (12*100+31) --开始日期至上年底
OR
(month(CustomerFamily_OtherAnniversary2)*100+day(CustomerFamily_OtherAnniversary2))
Between (1*100+1) And (month(@EndDate)*100+day(@EndDate)) --本年初至结束日期
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary2Type=1 --公历不跨年
And (month(@BeginDate)*100+day(@BeginDate))<(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_OtherAnniversary2)*100+day(CustomerFamily_OtherAnniversary2))
Between (month(@BeginDate)*100+day(@BeginDate)) And (month(@EndDate)*100+day(@EndDate))
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary2Type=0 --农历跨年
And (@BeginLunarMonth*100+@BeginLunarDay)>(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_OtherAnniversary2LunarMonth*100+CustomerFamily_OtherAnniversary2LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (12*100+31)
OR
(CustomerFamily_OtherAnniversary2LunarMonth*100+CustomerFamily_OtherAnniversary2LunarDate)
Between (1*100+1) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary2Type=0 --农历不跨年
And (@BeginLunarMonth*100+@BeginLunarDay)<(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_OtherAnniversary2LunarMonth*100+CustomerFamily_OtherAnniversary2LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
) tmpAn
Union All
--//生日祝贺
SELECT '生日祝贺' AS FollowCause, COUNT(Customer_ID) AS FollowNum
From
(
SELECT [User_ID], Customer_ID, Customer_Name, Customer_NickName,
'本人' AS Customer_FamilyRelation, '' AS CustomerFamily_Name,
isnull(Customer_Birthday,'') AS Birthday, Customer_BirthType AS BirthType,
Isnull(Customer_LunarYear,'') AS LunarYear,
Isnull(Customer_LunarMonth,'') AS LunarMonth,
Isnull(Customer_LunarDate,'') AS LunarDate,
Isnull(Customer_LunarBirthday,'') AS LunarBirthday, '1' AS OrderId
, [Customer_HomePhone], [Customer_Mobile], [Customer_FirmPhone], Isnull([Customer_OtherTel],'') AS Customer_OtherTel
FROM dbo.CU_Customer
WHERE [User_ID]=@UserId --47 --and Customer_ID=286
UNION
SELECT C.[User_ID], C.Customer_ID, C.Customer_Name, C.Customer_NickName,
'配偶' AS Customer_FamilyRelation, CustomerFamily_MateName AS CustomerFamily_Name,
isnull(CustomerFamily_MateBirthday,'') AS Birthday, CustomerFamily_MateBirthType AS BirthType,
Isnull(CustomerFamily_MateLunarYear,'') AS LunarYear,
Isnull(CustomerFamily_MateLunarMonth,'') AS LunarMonth,
Isnull(CustomerFamily_MateLunarDate,'') AS LunarDate,
Isnull(CustomerFamily_MateLunarBirthday,'') AS LunarBirthday, '2' AS OrderId
, C.[Customer_HomePhone], C.[Customer_Mobile], C.[Customer_FirmPhone], Isnull(C.[Customer_OtherTel],'') AS Customer_OtherTel
FROM dbo.CU_CustomerFamily F
INNER JOIN dbo.CU_Customer C ON F.Customer_ID = C.Customer_ID
WHERE C.[User_ID]=@UserId --47 --and Customer_ID=286
UNION
SELECT C.[User_ID], C.Customer_ID, C.Customer_Name, C.Customer_NickName,
[Customer_FamilyRelation], [CustomerFamily_Name],
[CustomerFamily_Birthday] AS Birthday, [CustomerFamily_BirthType] AS BirthType,
[CustomerFamily_LunarYear] AS LunarYear,
[CustomerFamily_LunarMonth] AS LunarMonth,
[CustomerFamily_LunarDay] AS LunarDate,
[CustomerFamily_LunarBirthday] AS LunarBirthday, '3' AS OrderId
, C.[Customer_HomePhone], C.[Customer_Mobile], C.[Customer_FirmPhone], Isnull(C.[Customer_OtherTel],'') AS Customer_OtherTel
FROM dbo.CU_Customer2Family F
INNER JOIN dbo.CU_Customer C ON F.Customer_ID = C.Customer_ID
WHERE C.[User_ID]=@UserId --@UserId --47 --and Customer_ID=286
) tabBirthday
Where Birthday<>'1900-01-01' And
((
BirthType=1 --公历跨年
And (month(@BeginDate)*100+day(@BeginDate))>(month(@EndDate)*100+day(@EndDate))
And (
(month(Birthday)*100+day(Birthday))
Between (month(@BeginDate)*100+day(@BeginDate)) And (12*100+31) --开始日期至上年底
OR
(month(Birthday)*100+day(Birthday))
Between (1*100+1) And (month(@EndDate)*100+day(@EndDate)) --本年初至结束日期
)
)
OR
(
BirthType=1 --公历不跨年
And (month(@BeginDate)*100+day(@BeginDate))<(month(@EndDate)*100+day(@EndDate))
And (
(month(Birthday)*100+day(Birthday))
Between (month(@BeginDate)*100+day(@BeginDate)) And (month(@EndDate)*100+day(@EndDate))
)
)
OR
(
BirthType=0 --农历跨年
And (@BeginLunarMonth*100+@BeginLunarDay)>(@EndLunarMonth*100+@EndLunarDay)
And (
(LunarMonth*100+LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (12*100+31)
OR
(LunarMonth*100+LunarDate)
Between (1*100+1) And (@EndLunarMonth*100+@EndLunarDay)
)
)
OR
(
BirthType=0 --农历不跨年
And (@BeginLunarMonth*100+@BeginLunarDay)<(@EndLunarMonth*100+@EndLunarDay)
And (
(LunarMonth*100+LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (@EndLunarMonth*100+@EndLunarDay)
)
)) And [User_ID]=@UserId And @switchBirthday=1 And @switchAllFollow=1
Union All
--//预约拜访
SELECT '预约拜访' AS FollowCause, COUNT(C.Customer_ID) AS FollowNum
FROM dbo.CU_Customer C
INNER JOIN [dbo].[FW_UDFollow] UD ON UD.[Customer_ID]=C.Customer_ID AND UD.[IsFollowed]=0
INNER JOIN [dbo].[FW_FollowCause] FC ON UD.[FollowCause_ID]=FC.[FollowCause_ID] and FC.FollowCause_SysTag=2
WHERE UD.[UD_Date] Between @BeginDate And @EndDate AND C.[User_ID]=@UserId
Union All
--//活动计划
SELECT '活动计划' AS FollowCause, COUNT(Schedule_ID) AS FollowNum
FROM dbo.SU_Schedule S
INNER JOIN [dbo].[ST_User2MainPermission] M
ON S.[User_ID]=M.[User_ID] AND M.[MainPermission_ID]='1011' And M.[Status]=1
WHERE
(S.[User_ID]=@UserId)
And
(
(Schedule_BeginTime<@BeginDate And Schedule_EndTime>=@BeginDate)
Or
(Schedule_BeginTime Between @BeginDate And @EndDate)
)
GO
功能：拜访类统计
输入：
返回：
作者：ding_0910
时间：2006/06/10 17:44
说明：创建
*/--=============================
CREATE PROCEDURE dbo.StatFollow_F
@UserId int,
@BeginDate datetime,
@EndDate datetime,
@BeginLunarMonth int,
@BeginLunarDay int,
@EndLunarMonth int,
@EndLunarDay int
AS
/*---示例参数值
declare @UserId int
select @UserId = 47
declare @BeginDate datetime
select @BeginDate = '2006-04-01'
declare @EndDate datetime
select @EndDate = '2007-03-01'
declare @BeginLunarMonth int
select @BeginLunarMonth = 0
declare @BeginLunarDay int
select @BeginLunarDay = 0
declare @EndLunarMonth int
select @EndLunarMonth = 0
declare @EndLunarDay int
select @EndLunarDay = 0
*/
--//获取拜访参数//
declare @spanNewCu int
declare @spanAfterBuy int
declare @spanUseUp int
declare @spanNoContact int
declare @switchNewCU bit
declare @switchAfterBuy bit
declare @switchUseUp bit
declare @switchNoContact bit
declare @switchAnniversary bit
declare @switchBirthday bit
declare @switchAllFollow bit
SELECT @spanNewCu=[FollowParam_NewCustomer],
@spanAfterBuy=[FollowParam_AfterBuy],
@spanUseUp=[FollowParam_UseUp],
@spanNoContact=[FollowParam_NoContact],
@switchAnniversary=[FollowParam_Anniversary],
@switchBirthday=[FollowParam_Birthday],
@switchNewCU=[switchNewCU],
@switchAfterBuy=[switchAfterBuy],
@switchUseUp=[switchUseUp],
@switchNoContact=[switchNoContact],
@switchAllFollow=[switchAllFollow]
FROM [dbo].[FW_FollowParam] where [User_ID]=@UserId
--//新进客户
SELECT '新进客户' AS FollowCause, COUNT(Customer_ID) AS FollowNum
FROM dbo.CU_Customer
WHERE DATEADD(day,@spanNewCu,Customer_AddDate)< @EndDate+1
AND DATEADD(day,@spanNewCu,Customer_AddDate)>= @BeginDate
--AND Customer_NewGJFlag In (0,1,2) --0:未跟进,1:已跟进,2:有待再次更进
--AND XJ_ID=@XJ_Id AND CustomerType_ID=@CU_Type
AND [User_ID]=@UserId And @switchNewCU=1 And @switchAllFollow=1
Union All
--//售后关怀
SELECT '售后关怀' AS FollowCause, COUNT(O.ProductOUT_ID) AS FollowNum
FROM dbo.PR_ProductOUT O
INNER JOIN dbo.CU_Customer C ON O.Customer_ID=C.Customer_Id
WHERE DATEADD(D,@spanAfterBuy,ProductOUT_Date)<= @EndDate
AND DATEADD(D,@spanAfterBuy,ProductOUT_Date)>= @BeginDate
AND ProductOUT_NewGJFlag In(1,2) --产品购买后跟进标记（0:不跟进;1:跟进;2已经跟进）
AND InventoryType_Id=dbo.GetInventoryTypeID(@UserId,10)--只有销售的东西才会跟进
AND O.[User_ID]=@UserId And @switchAfterBuy=1 And @switchAllFollow=1
Union All
--//购买提醒
SELECT '购买提醒' AS FollowCause, COUNT(O.ProductOUT_ID) AS FollowNum
FROM dbo.PR_ProductOUT O
INNER JOIN dbo.CU_Customer C ON O.Customer_ID=C.Customer_Id
WHERE DATEADD(D,0-@spanUseUp,DATEADD(D,ProductOut_CostDay,ProductOUT_Date))<= @EndDate
AND DATEADD(D,0-@spanUseUp,DATEADD(D,ProductOut_CostDay,ProductOUT_Date))>= @BeginDate
AND ProductOUT_FinishGJFlag In(1,2) --产品使用完成后跟进标记（0:不跟进;1:跟进;2已经跟进）
AND InventoryType_Id=dbo.GetInventoryTypeID(@UserId,10)--只有销售的东西才会跟进
AND O.[User_ID]=@UserId And @switchUseUp=1 And @switchAllFollow=1
/*
Union All
--//久未联系
SELECT '久未联系' AS FollowCause, COUNT(C.Customer_ID) AS FollowNum
FROM dbo.CU_Customer C
WHERE DATEADD(D,@spanNoContact,Customer_LastContactDate)<= @EndDate+1
AND DATEADD(D,@spanNoContact,Customer_LastContactDate)>= @BeginDate
AND C.[User_ID]=@UserId And @switchNoContact=1 And @switchAllFollow=1
*/
Union All
--//记念日祝贺
SELECT '记念日祝贺' AS FollowCause, COUNT(Customer_ID) AS FollowNum
From (
SELECT C.Customer_ID --/结婚记念日
FROM dbo.CU_CustomerFamily F INNER JOIN dbo.CU_Customer C ON F.Customer_ID = C.Customer_ID
WHERE
(
CustomerFamily_MarryType=1 --公历跨年
And (month(@BeginDate)*100+day(@BeginDate))>(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_MarryYear)*100+day(CustomerFamily_MarryYear))
Between (month(@BeginDate)*100+day(@BeginDate)) And (12*100+31) --开始日期至上年底
OR
(month(CustomerFamily_MarryYear)*100+day(CustomerFamily_MarryYear))
Between (1*100+1) And (month(@EndDate)*100+day(@EndDate)) --本年初至结束日期
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_MarryType=1 --公历不跨年
And (month(@BeginDate)*100+day(@BeginDate))<(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_MarryYear)*100+day(CustomerFamily_MarryYear))
Between (month(@BeginDate)*100+day(@BeginDate)) And (month(@EndDate)*100+day(@EndDate))
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_MarryType=0 --农历跨年
And (@BeginLunarMonth*100+@BeginLunarDay)>(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_MarryLunarMonth*100+CustomerFamily_MarryLunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (12*100+31)
OR
(CustomerFamily_MarryLunarMonth*100+CustomerFamily_MarryLunarDate)
Between (1*100+1) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_MarryType=0 --农历不跨年
And (@BeginLunarMonth*100+@BeginLunarDay)<(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_MarryLunarMonth*100+CustomerFamily_MarryLunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
UNION ALL
SELECT C.Customer_ID --/其他记念日1
FROM dbo.CU_CustomerFamily F INNER JOIN dbo.CU_Customer C ON F.Customer_ID = C.Customer_ID
WHERE
(
CustomerFamily_OtherAnniversary1Type=1 --公历跨年
And (month(@BeginDate)*100+day(@BeginDate))>(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_OtherAnniversary1)*100+day(CustomerFamily_OtherAnniversary1))
Between (month(@BeginDate)*100+day(@BeginDate)) And (12*100+31) --开始日期至上年底
OR
(month(CustomerFamily_OtherAnniversary1)*100+day(CustomerFamily_OtherAnniversary1))
Between (1*100+1) And (month(@EndDate)*100+day(@EndDate)) --本年初至结束日期
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary1Type=1 --公历不跨年
And (month(@BeginDate)*100+day(@BeginDate))<(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_OtherAnniversary1)*100+day(CustomerFamily_OtherAnniversary1))
Between (month(@BeginDate)*100+day(@BeginDate)) And (month(@EndDate)*100+day(@EndDate))
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary1Type=0 --农历跨年
And (@BeginLunarMonth*100+@BeginLunarDay)>(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_OtherAnniversary1LunarMonth*100+CustomerFamily_OtherAnniversary1LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (12*100+31)
OR
(CustomerFamily_OtherAnniversary1LunarMonth*100+CustomerFamily_OtherAnniversary1LunarDate)
Between (1*100+1) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary1Type=0 --农历不跨年
And (@BeginLunarMonth*100+@BeginLunarDay)<(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_OtherAnniversary1LunarMonth*100+CustomerFamily_OtherAnniversary1LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
UNION ALL
SELECT C.Customer_ID --/其他记念日2
FROM dbo.CU_CustomerFamily F INNER JOIN dbo.CU_Customer C ON F.Customer_ID = C.Customer_ID
WHERE
(
CustomerFamily_OtherAnniversary2Type=1 --公历跨年
And (month(@BeginDate)*100+day(@BeginDate))>(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_OtherAnniversary2)*100+day(CustomerFamily_OtherAnniversary2))
Between (month(@BeginDate)*100+day(@BeginDate)) And (12*100+31) --开始日期至上年底
OR
(month(CustomerFamily_OtherAnniversary2)*100+day(CustomerFamily_OtherAnniversary2))
Between (1*100+1) And (month(@EndDate)*100+day(@EndDate)) --本年初至结束日期
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary2Type=1 --公历不跨年
And (month(@BeginDate)*100+day(@BeginDate))<(month(@EndDate)*100+day(@EndDate))
And (
(month(CustomerFamily_OtherAnniversary2)*100+day(CustomerFamily_OtherAnniversary2))
Between (month(@BeginDate)*100+day(@BeginDate)) And (month(@EndDate)*100+day(@EndDate))
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary2Type=0 --农历跨年
And (@BeginLunarMonth*100+@BeginLunarDay)>(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_OtherAnniversary2LunarMonth*100+CustomerFamily_OtherAnniversary2LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (12*100+31)
OR
(CustomerFamily_OtherAnniversary2LunarMonth*100+CustomerFamily_OtherAnniversary2LunarDate)
Between (1*100+1) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
OR
(
CustomerFamily_OtherAnniversary2Type=0 --农历不跨年
And (@BeginLunarMonth*100+@BeginLunarDay)<(@EndLunarMonth*100+@EndLunarDay)
And (
(CustomerFamily_OtherAnniversary2LunarMonth*100+CustomerFamily_OtherAnniversary2LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (@EndLunarMonth*100+@EndLunarDay)
)
AND C.[User_ID]=@UserId And @switchAnniversary=1 And @switchAllFollow=1
)
) tmpAn
Union All
--//生日祝贺
SELECT '生日祝贺' AS FollowCause, COUNT(Customer_ID) AS FollowNum
From
(
SELECT [User_ID], Customer_ID, Customer_Name, Customer_NickName,
'本人' AS Customer_FamilyRelation, '' AS CustomerFamily_Name,
isnull(Customer_Birthday,'') AS Birthday, Customer_BirthType AS BirthType,
Isnull(Customer_LunarYear,'') AS LunarYear,
Isnull(Customer_LunarMonth,'') AS LunarMonth,
Isnull(Customer_LunarDate,'') AS LunarDate,
Isnull(Customer_LunarBirthday,'') AS LunarBirthday, '1' AS OrderId
, [Customer_HomePhone], [Customer_Mobile], [Customer_FirmPhone], Isnull([Customer_OtherTel],'') AS Customer_OtherTel
FROM dbo.CU_Customer
WHERE [User_ID]=@UserId --47 --and Customer_ID=286
UNION
SELECT C.[User_ID], C.Customer_ID, C.Customer_Name, C.Customer_NickName,
'配偶' AS Customer_FamilyRelation, CustomerFamily_MateName AS CustomerFamily_Name,
isnull(CustomerFamily_MateBirthday,'') AS Birthday, CustomerFamily_MateBirthType AS BirthType,
Isnull(CustomerFamily_MateLunarYear,'') AS LunarYear,
Isnull(CustomerFamily_MateLunarMonth,'') AS LunarMonth,
Isnull(CustomerFamily_MateLunarDate,'') AS LunarDate,
Isnull(CustomerFamily_MateLunarBirthday,'') AS LunarBirthday, '2' AS OrderId
, C.[Customer_HomePhone], C.[Customer_Mobile], C.[Customer_FirmPhone], Isnull(C.[Customer_OtherTel],'') AS Customer_OtherTel
FROM dbo.CU_CustomerFamily F
INNER JOIN dbo.CU_Customer C ON F.Customer_ID = C.Customer_ID
WHERE C.[User_ID]=@UserId --47 --and Customer_ID=286
UNION
SELECT C.[User_ID], C.Customer_ID, C.Customer_Name, C.Customer_NickName,
[Customer_FamilyRelation], [CustomerFamily_Name],
[CustomerFamily_Birthday] AS Birthday, [CustomerFamily_BirthType] AS BirthType,
[CustomerFamily_LunarYear] AS LunarYear,
[CustomerFamily_LunarMonth] AS LunarMonth,
[CustomerFamily_LunarDay] AS LunarDate,
[CustomerFamily_LunarBirthday] AS LunarBirthday, '3' AS OrderId
, C.[Customer_HomePhone], C.[Customer_Mobile], C.[Customer_FirmPhone], Isnull(C.[Customer_OtherTel],'') AS Customer_OtherTel
FROM dbo.CU_Customer2Family F
INNER JOIN dbo.CU_Customer C ON F.Customer_ID = C.Customer_ID
WHERE C.[User_ID]=@UserId --@UserId --47 --and Customer_ID=286
) tabBirthday
Where Birthday<>'1900-01-01' And
((
BirthType=1 --公历跨年
And (month(@BeginDate)*100+day(@BeginDate))>(month(@EndDate)*100+day(@EndDate))
And (
(month(Birthday)*100+day(Birthday))
Between (month(@BeginDate)*100+day(@BeginDate)) And (12*100+31) --开始日期至上年底
OR
(month(Birthday)*100+day(Birthday))
Between (1*100+1) And (month(@EndDate)*100+day(@EndDate)) --本年初至结束日期
)
)
OR
(
BirthType=1 --公历不跨年
And (month(@BeginDate)*100+day(@BeginDate))<(month(@EndDate)*100+day(@EndDate))
And (
(month(Birthday)*100+day(Birthday))
Between (month(@BeginDate)*100+day(@BeginDate)) And (month(@EndDate)*100+day(@EndDate))
)
)
OR
(
BirthType=0 --农历跨年
And (@BeginLunarMonth*100+@BeginLunarDay)>(@EndLunarMonth*100+@EndLunarDay)
And (
(LunarMonth*100+LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (12*100+31)
OR
(LunarMonth*100+LunarDate)
Between (1*100+1) And (@EndLunarMonth*100+@EndLunarDay)
)
)
OR
(
BirthType=0 --农历不跨年
And (@BeginLunarMonth*100+@BeginLunarDay)<(@EndLunarMonth*100+@EndLunarDay)
And (
(LunarMonth*100+LunarDate)
Between (@BeginLunarMonth*100+@BeginLunarDay) And (@EndLunarMonth*100+@EndLunarDay)
)
)) And [User_ID]=@UserId And @switchBirthday=1 And @switchAllFollow=1
Union All
--//预约拜访
SELECT '预约拜访' AS FollowCause, COUNT(C.Customer_ID) AS FollowNum
FROM dbo.CU_Customer C
INNER JOIN [dbo].[FW_UDFollow] UD ON UD.[Customer_ID]=C.Customer_ID AND UD.[IsFollowed]=0
INNER JOIN [dbo].[FW_FollowCause] FC ON UD.[FollowCause_ID]=FC.[FollowCause_ID] and FC.FollowCause_SysTag=2
WHERE UD.[UD_Date] Between @BeginDate And @EndDate AND C.[User_ID]=@UserId
Union All
--//活动计划
SELECT '活动计划' AS FollowCause, COUNT(Schedule_ID) AS FollowNum
FROM dbo.SU_Schedule S
INNER JOIN [dbo].[ST_User2MainPermission] M
ON S.[User_ID]=M.[User_ID] AND M.[MainPermission_ID]='1011' And M.[Status]=1
WHERE
(S.[User_ID]=@UserId)
And
(
(Schedule_BeginTime<@BeginDate And Schedule_EndTime>=@BeginDate)
Or
(Schedule_BeginTime Between @BeginDate And @EndDate)
)
GO