------------------------------------
--用途：查詢資料信息 
--項目名稱：MidWeb
--Create：Ted
--時間：2014/05/26
--exec OSP_Common_DDL_GetModuleListByUserGroupId 'apadmin'
------------------------------------
ALTER PROCEDURE [dbo].[OSP_Common_DDL_GetAlltipsListByUserId]
    @UserID VARCHAR(50) ,
    @OrganizationID VARCHAR(50)
AS 
    WITH    AlltipsList ( TipsName, TipsID, PageID, PKID, IsRead )
              AS ( SELECT   '待辦通知:' + EventName AS EventName ,
                            EventID ,
                            'Task_Upd' ,
                            'EventID' ,
                            'N' AS IsRead
                   FROM     OTB_MNG_Task
                   WHERE    OrganizationID = @OrganizationID
                            AND Owner = @UserID
                            AND ( ReadTime = ''
                                  OR ReadTime IS NULL
                                )
                            AND IsAlert = 'Y'
                            AND AlertTime <= GETDATE()
                   UNION ALL
                   SELECT   '請假申請:'
                            + ( CASE WHEN LEN(AskUser) > 30
                                     THEN LEFT([dbo].OFN_SYS_GetMemberListNameByMemberIDList(AskUser,
                                                              @OrganizationID),
                                               10) + '...'
                                     ELSE [dbo].OFN_SYS_GetMemberListNameByMemberIDList(AskUser,
                                                              @OrganizationID)
                                END ) + '('
                            + CONVERT(VARCHAR(100), LeaveDateStart, 111) + '~'
                            + CONVERT(VARCHAR(100), LeaveDateEnd, 111) + ')' AS EventName ,
                            AskLeaveGuid ,
                            'AskLeave_Upd' ,
                            'AskLeaveGuid' ,
                            Checkerread AS IsRead
                   FROM     dbo.OTB_HR_AskLeave AL
                   WHERE    OrganizationID = @OrganizationID
                            AND CHARINDEX(@UserID,
                                          ( SELECT  CheckUserID + ','
                                            FROM    dbo.OTB_HR_CheckAskLeave
                                            WHERE   OrganizationID = @OrganizationID
                                                    AND AskLeaveGuid = AL.AskLeaveGuid
                                          FOR
                                            XML PATH('')
                                          )) > 0
                            AND CheckStatue <> 'AO'
                            AND CheckStatue <> 'AN'
                            AND IsMailChecker = 'Y'
                            AND LeaveDateStart <= DATEADD(d, 3, GETDATE())
                   UNION ALL
                   SELECT   '請假申請審核回覆:'
                            + ( CASE WHEN LEN(AskUser) > 30
                                     THEN LEFT([dbo].OFN_SYS_GetMemberListNameByMemberIDList(AskUser,
                                                              @OrganizationID),
                                               10) + '...'
                                     ELSE [dbo].OFN_SYS_GetMemberListNameByMemberIDList(AskUser,
                                                              @OrganizationID)
                                END ) + '('
                            + CONVERT(VARCHAR(100), LeaveDateStart, 111) + '~'
                            + CONVERT(VARCHAR(100), LeaveDateEnd, 111) + ')' AS EventName ,
                            AskLeaveGuid ,
                            'AskLeave_Upd' ,
                            'AskLeaveGuid' ,
                            'M' AS IsRead
                   FROM     dbo.OTB_HR_AskLeave AL
                   WHERE    OrganizationID = @OrganizationID
                            AND CHARINDEX(@UserID, AskUser) > 0
                            AND IsMailAsker = ''
                   UNION ALL
                   SELECT   '專案任務指派:' + EventName AS EventName ,
                            EventID ,
                            'PrjTaskPop' ,
                            '' ,
                            'N' AS IsRead
                   FROM     OTB_PRJ_TaskDetail
                   WHERE    OrganizationID = @OrganizationID
                            AND Owner = @UserID
                            AND Status = 'U'
                   UNION ALL
                   SELECT   '專案『'
                            + dbo.OFN_SYS_ProjectNameByProjectNumber(ProjectNumber)
                            + '』需求變更單確認  主旨：' + OrderTitle AS EventName ,
                            ECOrderNumber AS EventID ,
                            'RequestOrderMaintain_Upd' ,
                            'ECOrderNumber' ,
                            'N' AS IsRead
                   FROM     OTB_PRJ_RequestOrder
                   WHERE    OrganizationID = @OrganizationID
                            AND Undertaker = @UserID
                            AND Orgread = ''
                   UNION ALL
                   SELECT   '程式『'
                            + dbo.OFN_SYS_ProgramNameByProgramID(ProgramID)
                            + '』SD確認（專案：'
                            + dbo.OFN_SYS_ProjectNameByProjectNumber(ProjectNumber)
                            + '）' AS EventName ,
                            ProgramSequence AS EventID ,
                            'DesignDocument_Upd' ,
                            'ProgramSequence' ,
                            'N' AS IsRead
                   FROM     OTB_DEV_DesignDocument
                   WHERE    OrganizationID = @OrganizationID
                            AND CreateUser = @UserID
                            AND Orgread = ''
                   UNION ALL
                   SELECT   '行事曆邀請『' + dbo.OFN_SYS_MemberNameByMemberID(UserID,
                                                              @OrganizationID)
                            + '』（主旨：' + [Subject] + '）' AS EventName ,
                            EventID ,
                            'Calendar_CheckPop' ,
                            'EventID' ,
                            ( CASE CHARINDEX(@UserID, ISNULL(ReadMember, ''))
                                WHEN 0 THEN 'N'
                                ELSE 'Y'
                              END ) AS IsRead
                   FROM     [OTB_MNG_Calendar]
                   WHERE    OrganizationID = @OrganizationID
                            AND CHARINDEX(@UserID, ISNULL(RqMember, '')) > 0
                            AND CHARINDEX(@UserID, ISNULL(RqMemberCheck, '')) = 0
                            AND CHARINDEX(@UserID, ISNULL(RqMemberCheckNo, '')) = 0
                            AND EndDate > GETDATE()--Add by Alina 20151016 必須大於當前時間
                   UNION ALL
                   SELECT   '業務機會追蹤:' + ( SELECT    ProjectName
                                          FROM      dbo.OTB_SAL_BusinessChance
                                          WHERE     CaseID = cr.CaseID
                                        ) + '('
                            + ( SELECT  ContractCustomers
                                FROM    dbo.OTB_SAL_BusinessChance
                                WHERE   CaseID = cr.CaseID
                              ) + ')'
                            + ( CASE WHEN LEN(ToDoMemo) > 10
                                     THEN LEFT(ToDoMemo, 10) + '...'
                                     ELSE ( CASE ISNULL(ToDoMemo, '')
                                              WHEN '' THEN ''
                                              ELSE ToDoMemo
                                            END )
                                END ) AS EventName ,
                            CaseID ,
                            'BusinessChance_Upd' ,
                            'CaseID' ,
                            'N' AS IsRead
                   FROM     dbo.OTB_SAL_ContactRecords AS cr
                   WHERE    OrganizationID = @OrganizationID
                            AND ( SELECT    Person
                                  FROM      dbo.OTB_SAL_BusinessChance
                                  WHERE     CaseID = cr.CaseID
                                ) = @UserID
                            AND ( ReadTime = ''
                                  OR ReadTime IS NULL
                                )
                            AND Remind = 'Y'
                            AND cr.NextTime <= GETDATE()
                 )
        SELECT  *
        FROM    AlltipsList
             
    SELECT  '待辦通知:' + EventName AS TipsName ,
            EventID AS TipsID ,
            'Task_Upd' AS PageID ,
            'EventID' AS PKID ,
            CONVERT(VARCHAR(100), AlertTime, 121) AS AlertTime
    FROM    OTB_MNG_Task
    WHERE   OrganizationID = @OrganizationID
            AND Owner = @UserID
            AND ( ReadTime = ''
                  OR ReadTime IS NULL
                )
            AND AlertTime > GETDATE()
					--UNION ALL
     --              SELECT  DISTINCT '業務機會追?:' + (SELECT ProjectName FROM dbo.OTB_SAL_BusinessChance WHERE CaseID=cr.CaseID)+'('+(SELECT ContractCustomers FROM dbo.OTB_SAL_BusinessChance WHERE CaseID=cr.CaseID)+')' AS TipsName ,
     --                       CaseID AS TipsID,
     --                       'BusinessChance_Upd' AS PageID ,
     --                       'CaseID' AS PKID ,
					--        CONVERT(VARCHAR(100), RemindTime, 121) AS AlertTime
     --              FROM     dbo.OTB_SAL_ContactRecords AS cr
     --              WHERE    OrganizationID = @OrganizationID
     --                       AND (SELECT Person FROM dbo.OTB_SAL_BusinessChance WHERE CaseID=cr.CaseID) = @UserID
     --                       AND ( ReadTime = '' OR ReadTime IS NULL
     --                           )
     --                       AND Remind='Y'
     --                       AND cr.NextTime > GETDATE()