我认为够长!!!~~:>

/*--=============================
功能：拜访类统计
输入：
返回：
作者：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