You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
189 lines
9.8 KiB
189 lines
9.8 KiB
|
|
------------------------------------
|
|
--用途：查詢資料信息
|
|
--項目名稱：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()
|
|
|
|
|