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.
60 lines
7.8 KiB
60 lines
7.8 KiB
|
|
|
|
------------------------------------
|
|
--用途：得到实体对象的详细信息
|
|
--项目名称：
|
|
--说明：
|
|
--时间：2014/7/9 下午 07:05:11
|
|
------------------------------------
|
|
ALTER PROCEDURE [dbo].[OSP_OTB_Rpt_GetPrjWorkhoursRpt]
|
|
@ProjectNumber varchar(50),
|
|
@MemberID varchar(max),
|
|
@OrganizationID varchar(50),
|
|
@DateStart varchar(50),
|
|
@DateEnd varchar(50)
|
|
AS
|
|
|
|
declare @Allsql varchar(max),@str VARCHAR(MAX),@sql2 varchar(max)
|
|
|
|
--SET @str=(SELECT ArgumentValue+',' FROM (SELECT DISTINCT Worktype ,OrganizationID FROM dbo.OTB_MNG_DayWork WHERE (CHARINDEX(ProjectNumber,@ProjectNumber)>0 OR @ProjectNumber='') AND (CHARINDEX(CreateUser,@MemberID)>0 OR @MemberID='') AND (StartDate>=@DateStart OR @DateStart='') AND (EndDate<=@DateEnd OR @DateEnd='') AND OrganizationID=@OrganizationID AND ProjectNumber<>'' AND Worktype<>''
|
|
--) aa LEFT JOIN dbo.OTB_SYS_Arguments Arg ON aa.Worktype=Arg.ArgumentID AND aa.OrganizationID = Arg.OrganizationID AND ArgumentClassID='DayWorkTP' ORDER BY OrderByValue
|
|
-- FOR XML PATH(''))
|
|
SET @str=(SELECT ArgumentValue+',' FROM (SELECT DISTINCT Worktype ,OrganizationID FROM dbo.OTB_MNG_DayWork WHERE (CHARINDEX(ProjectNumber,@ProjectNumber)>0 OR @ProjectNumber='') AND (CHARINDEX(CreateUser,@MemberID)>0 OR @MemberID='') AND (StartDate>=@DateStart OR @DateStart='') AND (EndDate<=@DateEnd OR @DateEnd='') AND OrganizationID=@OrganizationID --AND ProjectNumber<>'' AND Worktype<>''
|
|
) aa LEFT JOIN dbo.OTB_SYS_Arguments Arg ON aa.Worktype=Arg.ArgumentID AND aa.OrganizationID = Arg.OrganizationID AND ArgumentClassID='DayWorkTP' ORDER BY OrderByValue
|
|
FOR XML PATH(''))
|
|
|
|
PRINT @str
|
|
SET @str=REPLACE(@str+',',',,','')
|
|
SET @sql2=')a pivot (sum(a.WorkHours) for Worktype in ('+@str+')) b '
|
|
SET @Allsql='
|
|
SELECT ''序號,人員名稱,專案名稱,'+@str+',備註'' as HeaderName
|
|
SELECT ROW_NUMBER() OVER(Order BY RIGHT(人員名稱,LEN(人員名稱)-CHARINDEX(''（'',人員名稱))) AS ''序號'',*,'''' as ''備註'' FROM (
|
|
SELECT dbo.OFN_SYS_MemberNameByMemberID(CreateUser,OrganizationID)+''（''+CreateUser+''）'' AS 人員名稱, (case ISNULL(ProjectNumber,'''') when '''' then ''【其他】(非專案)'' else dbo.OFN_SYS_ProjectNameByProjectNumber(ProjectNumber) end ) as ''專案名稱'',
|
|
dbo.OFN_SYS_ArgumentValueByArgumentID(Worktype,''DayWorkTP'') AS Worktype,
|
|
cast(round(datediff( MINUTE, StartDate, EndDate )/60.00,2) as numeric(20,2)) AS WorkHours
|
|
|
|
FROM dbo.OTB_MNG_DayWork WHERE (CHARINDEX(ProjectNumber,'''+@ProjectNumber+''')>0 OR '''+@ProjectNumber+'''='''') AND (CHARINDEX(CreateUser,'''+@MemberID+''')>0 OR '''+@MemberID+'''='''') AND (StartDate>='''+@DateStart+''' OR '''+@DateStart+'''='''') AND (EndDate<='''+@DateEnd+''' OR '''+@DateEnd+'''='''') AND OrganizationID='''+@OrganizationID+''' '
|
|
|
|
|
|
--cast((datediff( MINUTE, StartDate, EndDate )/60.0) as decimal(10, 2)) AS WorkHours
|
|
SET @Allsql=@Allsql+@sql2
|
|
PRINT @Allsql
|
|
EXEC (@Allsql)
|
|
|
|
--SELECT '序號','人員名稱','專案名稱','專案工時','備註'
|
|
-- SELECT
|
|
-- ROW_NUMBER() OVER (ORDER BY prj.ProjectCName) AS 序號
|
|
-- ,(SELECT MemberName FROM dbo.OVW_MemberAndSupplyUser WHERE OrganizationID=MIN(dw.OrganizationID) AND MemberID=MIN(dw.CreateUser)) AS 人員名稱
|
|
-- ,ISNULL(prj.ProjectCName,N'【其他】(非專案)') AS 專案名稱
|
|
-- ,SUM(datediff( hour, StartDate, EndDate )) AS 專案工時
|
|
-- ,N'' AS 備註
|
|
-- FROM OTB_MNG_DayWork AS dw
|
|
-- LEFT JOIN dbo.OTB_PRJ_ProjectInfo AS prj ON dw.OrganizationID = prj.OrganizationID AND dw.ProjectNumber = prj.ProjectNumber
|
|
-- --WHERE UserID='christy.li'
|
|
-- WHERE
|
|
-- (CHARINDEX(dw.ProjectNumber,@ProjectNumber)>0 OR @ProjectNumber='')
|
|
-- AND (CHARINDEX(dw.CreateUser,@MemberID)>0 OR @MemberID='')
|
|
-- AND (dw.StartDate>=@DateStart OR @DateStart='')
|
|
-- AND (dw.EndDate<=@DateEnd OR @DateEnd='')
|
|
-- AND dw.OrganizationID=@OrganizationID
|
|
-- GROUP BY prj.ProjectCName
|