OPMS/02-系統分析、設計階段/DBScript/20151112/OSP_Common_DDL_GetAlltipsListByUserId.sql

------------------------------------
--�γ~�G�d�߸��ƫH�� 
--���ئW�١GMidWeb
--Create�GTed
--�ɶ��G2014/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   '�ݿ��q��:' + 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   '�а��ӽмf�֦^��:'
                            + ( 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   '�M�ץ��ȫ���:' + EventName AS EventName ,
                            EventID ,
                            'PrjTaskPop' ,
                            '' ,
                            'N' AS IsRead
                   FROM     OTB_PRJ_TaskDetail
                   WHERE    OrganizationID = @OrganizationID
                            AND Owner = @UserID
                            AND Status = 'U'
                   UNION ALL
                   SELECT   '�M�סy'
                            + dbo.OFN_SYS_ProjectNameByProjectNumber(ProjectNumber)
                            + '�z�ݨD�ܧ����T�{  �D���G' + 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   '�{���y'
                            + dbo.OFN_SYS_ProgramNameByProgramID(ProgramID)
                            + '�zSD�T�{�]�M�סG'
                            + 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   '���ƾ��ܽСy' + dbo.OFN_SYS_MemberNameByMemberID(UserID,
                                                              @OrganizationID)
                            + '�z�]�D���G' + [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 �����j�����e�ɶ�
                   UNION ALL
                   SELECT   '�~�Ⱦ��|�l��:' + ( 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  '�ݿ��q��:' + 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 '�~�Ⱦ��|�l?:' + (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()