-- =============================================

-- Author:		<Author,,Name>

-- Create date: <Create Date,,>

-- Description:	<Description,,>

-- =============================================

CREATE PROCEDURE [QuickTeam].[usp_GetWorkSheetsById] 

	@ProjectId nvarchar(50),

	@isReclaimed bit=0

AS

BEGIN

	SELECT

	WorksheetId,

      convert(nvarchar,sum([PaymentPercent])) as PaymentPercent

	  into #receiv

  FROM [QuickTeam].[QuickTeam].[WorksheetPayment] group by WorksheetId



	select t.*

	,(case 

		when t.Finished='02' or t.Finished='03' then N'无回款'

		else 

			case when rs.StatusAlia=N'回款已经确认' and PaymentPercent is null then N'开票已经确认'

			else iif(rs.StatusAlia=N'回款已经确认',rs.StatusAlia+N'(累计回款：'+r.PaymentPercent+'%)',rs.StatusAlia) end

		 end

	) as 'ReceiveStatusName'

	--,rs.StatusAlia

	--,iif(rs.StatusAlia=N'回款已经确认',rs.StatusAlia+N'(累计回款：'+r.PaymentPercent+'%)',rs.StatusAlia) as 'ReceiveStatusName'

	,s.ServiceTypeAlias, r.PaymentPercent,e.Effort,f.Effort CurrentEffort from (

  select  ws.*,''as VersionId,'' as VersionCode,'' as VersionStatus,'' as NextOperator,'' as Operator from [QuickTeam].[QuickTeam].[WorkSheet] ws

  union

  select 

	   ws.[WorkSheetId]

      ,wv.VersionCode as WorkSheetCode

      ,ws.[ProjectId]

      ,ws.[ProjectName]

      ,ws.[SystemName]

      ,ws.[Evaluator]

      ,ws.[TechnicalLeader]

      ,ws.[CustomerLeader]

      ,ws.[StartTime]

      ,ws.[EndTime]

      ,wv.[UpdateTime]

      ,ws.[Status]

	  ,ws.ReceiveStatus

	  ,ws.Finished

	  ,ws.Finishtime

      ,ws.[ServiceType]

      ,ws.[ImplementContent]

      ,ws.[OtherInstructions]

	  ,ws.CurrentVersionId

	  ,ws.isMerge

	  ,ws.DeleteFlag

	  ,ws.Operation 

	  ,ws.OperateUserName

	  ,ws.isReclaimed

	  ,ws.BelongTeam

  ,wv.VersionId,wv.VersionCode,wv.VersionStatus,wv.NextOperator,wv.Operator from [QuickTeam].[QuickTeam].[WorkSheet] ws 

  join [QuickTeam].[WorkSheetVersion] wv

  on ws.WorkSheetId=wv.WorkSheetId 

  

  ) as t

  left join #receiv r on r.WorksheetId=t.WorkSheetId

  left join (

  select distinct ServiceType,ServiceTypeAlias from 

  QuickTeam.ServiceTypeTaskName) s

  on t.ServiceType=s.ServiceType

  left join QuickTeam.WorkSheetReceiveStatus rs on t.receiveStatus=rs.statusid

  left join (SELECT [VersionId]

      ,sum([Effort]) [Effort]

  FROM [QuickTeam].[QuickTeam].[WorkSheetTask]  group by VersionId) e on t.VersionId=e.VersionId

  left join (SELECT [VersionId]

      ,sum([Effort]) [Effort]

  FROM [QuickTeam].[QuickTeam].[WorkSheetTask]  group by VersionId) f on t.CurrentVersionId=f.VersionId

  where projectid=@ProjectId AND t.DeleteFlag<>1 and isReclaimed=@isReclaimed

  order by Finished ,WorkSheetId,VersionId



  drop table #receiv

END