USE [JCS]
GO
/****** Object:  UserDefinedFunction [dbo].[GetLaborTotal]    Script Date: 12/28/2009 14:57:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jeff Hibbs
-- Create date: December 23, 2009
-- Description:	Returns Labor Total from JCPROD
-- =============================================
ALTER FUNCTION [dbo].[GetLaborTotal] 
(
	-- JCPROD cId to be passed into function
	@tcJobNo char(6)
)
RETURNS Numeric(12,2)
AS
BEGIN

	DECLARE @nRetVal Numeric(12,2)

	IF @tcJobNo IS NULL OR LEN(LTRIM(@tcJobNo)) = 0 
		SET @nRetVal = 0.00
	ELSE
		BEGIN
		DECLARE @nTOTOH Numeric(12,2)
        
        ;with cte_Info as (select jcprod.cjobno, 
                                  jcprod.ctype,
                                  jcprod.nhours,
                                  CASE WHEN JCRate.nregular_rate>0 THEN JCRate.nregular_rate 
                                              ELSE DefaultRates.nregular_rate END as nRegular_Rate,
                                  CASE WHEN JCRate.novertime_rate>0 THEN JCRate.novertime_rate 
                                              ELSE DefaultRates.novertime_rate END as nOvertime_Rate
                                  from dbo.JCProd
                                  Left JOIN dbo.DefaultRates DefaultRates ON jcprod.crate_type = DefaultRates.ctype_of_time
                                  Left JOIN dbo.JCRate JCRate ON jcprod.crate_type = JCRate.ctype_of_time and jcprod.cJobNo = JCRate.cJobNo      ),  

              cte_Summary as (select cJobNo, sum(CASE WHEN ctype = 'REG'  
                              THEN nregular_rate * nHours 
                              ELSE nOvertime_Rate * nHours END) as TotalLabor from cte_Info group by cJobNo)


        -- Get sum of labor
        SELECT @nTOTOH = TotalLabor
        from cte_Summary 
        where cte_Summary.cjobno = @tcJobNo
		
		-- Return the total of hours multiplied by rate
		SET @nRetVal = ISNULL(@nTOTOH, 0)
	END

	RETURN @nRetval

END