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.
45 lines
3.6 KiB
45 lines
3.6 KiB
|
|
------------------------------------
|
|
--用途：得到实体对象的详细信息
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/7/9 下午 07:05:11
|
|
------------------------------------
|
|
ALTER PROCEDURE [dbo].[OSP_OTB_Rpt_GetPrjWorkhoursInfoRpt]
|
|
@ProjectNumber VARCHAR(MAX) ,
|
|
@MemberID VARCHAR(MAX) ,
|
|
@OrganizationID VARCHAR(50) ,
|
|
@DateStart VARCHAR(50) ,
|
|
@DateEnd VARCHAR(50)
|
|
AS
|
|
SELECT ROW_NUMBER() OVER ( ORDER BY StartDate ) AS RowID ,
|
|
CONVERT(VARCHAR(100), StartDate, 111) AS CreateDate ,
|
|
SUBSTRING(CONVERT(VARCHAR(100), StartDate, 24), 0, 6) + '~'
|
|
+ SUBSTRING(CONVERT(VARCHAR(100), EndDate, 24), 0, 6) AS DateFromTo ,
|
|
dbo.OFN_SYS_ArgumentValueByArgumentID(Worktype, 'DayWorkTP') AS WorktypeName ,
|
|
[description] ,
|
|
[Subject] ,
|
|
dbo.OFN_SYS_ArgumentValueByArgumentID(Worktype, 'DayWorkTP') AS Worktype ,
|
|
ROUND(( DATEDIFF(MINUTE, StartDate, EndDate) / 60.0 ), 2) AS InfoHours ,
|
|
CASE ISNULL(dbo.OFN_SYS_ProjectNameByProjectNumber(dw.ProjectNumber),
|
|
'')
|
|
WHEN '' THEN N'【其他】(非專案)'
|
|
ELSE ISNULL(dbo.OFN_SYS_ProjectNameByProjectNumber(dw.ProjectNumber),
|
|
'')
|
|
END AS PrjName ,
|
|
dbo.OFN_SYS_MemberNameByMemberID(dw.UserID, dw.OrganizationID) AS UserName
|
|
FROM dbo.OTB_MNG_DayWork AS dw
|
|
WHERE ( CHARINDEX(ProjectNumber, @ProjectNumber) > 0
|
|
OR @ProjectNumber = ''
|
|
)
|
|
AND CHARINDEX(CreateUser, @MemberID) > 0
|
|
AND ( StartDate >= @DateStart
|
|
OR @DateStart = ''
|
|
)
|
|
AND ( EndDate <= @DateEnd
|
|
OR @DateEnd = ''
|
|
)
|
|
AND OrganizationID = @OrganizationID
|
|
ORDER BY CreateDate ,
|
|
StartDate
|
|
|