qlrc_database/maindb-function.sql

USE [MainDB]
GO

/****** Object:  UserDefinedFunction [dbo].[_51JobRegionToOurID]    Script Date: 2018/12/13 18:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--sean 2013-6-25
CREATE FUNCTION [dbo].[_51JobRegionToOurID]
(
	@51JobRegin VARCHAR(50)
)
RETURNS VARCHAR(6)
AS
BEGIN
	DECLARE @Id VARCHAR(6), @Id2 VARCHAR(6), @Des VARCHAR(20)
	SELECT TOP 1 @id = ID, @Des = DESCRIPTION
	FROM dcRegion WHERE Description LIKE LEFT(@51JobRegin, 2) + '%'
	AND GRADE = 1
	ORDER BY ID
	
	IF @Id IS NULL
		SELECT TOP 1 @id = ID, @Des = DESCRIPTION
		FROM dcRegion WHERE Description LIKE LEFT(@51JobRegin, 2) + '%'
		AND GRADE = 2
		ORDER BY ID
	ELSE
	BEGIN
		SET @Des = REPLACE(@Des, '市', '')
		SET @Des = REPLACE(@Des, '省', '')
		SET @51JobRegin = REPLACE(@51JobRegin, @Des, '')
		SET @51JobRegin = REPLACE(@51JobRegin, '-', '')

		SET @51JobRegin = REPLACE(@51JobRegin, @Des, '')
		SET @51JobRegin = REPLACE(@51JobRegin, '-', '')
		SELECT TOP 1 @id2 = ID
		FROM dcRegion 
		WHERE Description LIKE '%' + LEFT(@51JobRegin, 2) + '%'
		AND ID LIKE @Id + '%'
		ORDER BY Id
	END

	IF @Id2 > ''
		RETURN @ID2
	ELSE
		RETURN @ID
	RETURN ''
END
GO

/****** Object:  UserDefinedFunction [dbo].[AgeToBirth]    Script Date: 2018/12/13 18:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--######################################3
--创建：Sean 2011-2-25
--年龄to出生年月，用于简历搜索
--######################################3
CREATE FUNCTION [dbo].[AgeToBirth]
(
	@Age TINYINT
)
RETURNS INT
AS
BEGIN
	DECLARE @Year AS INT
	DECLARE @Month AS INT
	SET @Year = YEAR(GETDATE()) - @Age
	SET @Month = MONTH(GETDATE())
	RETURN @Year * 100 + @Month
END


GO

/****** Object:  UserDefinedFunction [dbo].[BinToINT]    Script Date: 2018/12/13 18:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[BinToINT]
(
	@BinaryChar char(10)
)
RETURNS INT
AS  
BEGIN 
    DECLARE @stringLength int,@ReturnValue int,@Index int
    DECLARE @CurrentChar char(1)
    SET @Index = 0
    SET @ReturnValue = 0
    SET @stringLength = LEN(@BinaryChar)
    While @Index<@stringLength
        BEGIN
            SET @Index = @Index + 1
            SET @CurrentChar = SUBSTRING(@BinaryChar,@Index,1)
            IF(@CurrentChar='1' or @CurrentChar='0')
                BEGIN
                    SET @ReturnValue = @ReturnValue + (CAST(@CurrentChar as int) * POWER(2,@stringLength - @Index))
                END
        END
    RETURN @ReturnValue
END
GO

/****** Object:  UserDefinedFunction [dbo].[BirthToAge]    Script Date: 2018/12/13 18:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--######################################3
--创建：Sean 2011-2-25
--年龄to出生年月，用于简历搜索
--######################################3
CREATE FUNCTION [dbo].[BirthToAge]
(
	@BirthDay INT
)
RETURNS INT
AS
BEGIN	
	RETURN (CONVERT(VARCHAR(6), GETDATE(), 112) - @BirthDay) / 100
END

GO

/****** Object:  UserDefinedFunction [dbo].[CheckCvPrivi]    Script Date: 2018/12/13 18:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--修改：harry 2018-4-18
--修改：对企业个人参加同一场校园招聘会，投递的非开放简历进行显示
CREATE FUNCTION [dbo].[CheckCvPrivi]
(
	@cvMainID INT,
	@cpMainID INT
)
RETURNS BIT
BEGIN
	DECLARE @MemberType TINYINT
	SELECT @MemberType = MemberType 
	FROM cpMain WITH(NOLOCK)
	WHERE ID = @cpMainID
	
	IF @MemberType < 2
		RETURN 0
	
	DECLARE @inActiveLog TINYINT, @inDailyGiftLog TINYINT, @inApplyPassed TINYINT, @paMainID INT
	SELECT @inActiveLog = 0, @inDailyGiftLog = 0, @inApplyPassed = 0
	IF EXISTS(SELECT TOP 1 'X'
		FROM caDailyGiftLog WITH(NOLOCK INDEX(PK_caDailyGiftLog))
		WHERE cpMainID = @cpMainID
		AND cvMainID = @cvMainID
	)
	BEGIN
		SET @inDailyGiftLog = 1
	END


	IF @inDailyGiftLog = 0
	BEGIN
		SELECT @paMainId = paMainId FROM cvMain WITH(NOLOCK) WHERE ID = @cvMainID
		DECLARE @t AS TABLE(ID INT)
		INSERT INTO @t
		SELECT ID FROM cvMain WITH(NOLOCK) WHERE paMainId = @paMainID

		IF EXISTS(SELECT TOP 1 'X'
			FROM caActiveLog WITH(NOLOCK INDEX(IX_caActiveLog_cpMainID))
			WHERE cpMainID = @cpMainID
			AND cvMainID IN(SELECT ID FROM @t)
		)
		BEGIN
			SET @inActiveLog = 1
		END
	END

	IF @inDailyGiftLog = 0 AND @inActiveLog = 0
	BEGIN
		IF EXISTS(SELECT TOP 1 'X'
			FROM ExJobApply a WITH(NOLOCK), Job b 
			WHERE a.JobID = b.ID 
			AND a.IsPassed = 1 
			AND a.cvMainID IN(SELECT ID FROM @t)
			AND b.cpMainID = @cpMainID
		)
		BEGIN
			SET @inApplyPassed = 1
		END
	END

	IF @inActiveLog + @inDailyGiftLog + @inApplyPassed = 0
	BEGIN
		--判断 个人给企业投递了简历,个人与企业都报名参加了同一场招聘会--harry2018-4-18
		IF EXISTS(SELECT TOP 1 'X'
			FROM ExJobApply a WITH(NOLOCK), Job b 
			WHERE a.JobID = b.ID 
			AND a.IsPassed = 0--非开放 
			AND a.cvMainID IN(SELECT ID FROM @t)
			AND b.cpMainID = @cpMainID
		)
		BEGIN
			--是否同时参加了一场校园招聘会
			IF EXISTS( SELECT TOP 1 'x'
				FROM marketdb..rmcompany a, marketdb..rmperson b
				WHERE a.cpMainId =@cpMainID
				AND b.paMainId=@paMainID
				AND a.recruitmentId= b.recruitmentId)
			BEGIN
				RETURN 1
			END
			ELSE
			BEGIN
				RETURN 0
			END
		END
		ELSE
		BEGIN
			RETURN 0
		END
	END
		 
	RETURN 1
END


GO

/****** Object:  UserDefinedFunction [dbo].[CleanAddress]    Script Date: 2018/12/13 18:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################3
--创建：Sean 2011-2-25
--年龄to出生年月，用于简历搜索
--######################################3
CREATE FUNCTION [dbo].[CleanAddress]
(
	@Address NVARCHAR(100),
	@dcRegionId VARCHAR(6)
)
RETURNS NVARCHAR(100)
AS
BEGIN
	DECLARE @r VARCHAR(6), @A NVARCHAR(100), @l INT
	SELECT @a = @address
	SELECT @r = Description FROM dcRegion WHERE Id = LEFT(@dcRegionId, 2)
	SET @l = LEN(@r)
	IF(LEFT(@a, @l) = @r)
		SET @a = RIGHT(@a, LEN(@a) - @l)
	BEGIN
		IF RIGHT(LEFT(@a, @l), 1) NOT IN('路', '街', '道')
			IF(LEFT(@a, @l - 1) = LEFT(@r, @l - 1))
				SET @a = RIGHT(@a, LEN(@a) - @l + 1)
	END

	IF(LEN(@dcRegionId) > 2)
	BEGIN
		SELECT @r = Description FROM dcRegion WHERE Id = LEFT(@dcRegionId, 4)
		SET @l = LEN(@r)
		IF(LEFT(@a, @l) = @r)
			SET @a = RIGHT(@a, LEN(@a) - @l)
		ELSE IF(LEFT(@a, @l-1) = LEFT(@r, @l - 1))
			SET @a = RIGHT(@a, LEN(@a) - @l + 1)
	END
	IF(LEN(@dcRegionId) > 4)
	BEGIN
		SELECT @r = Description FROM dcRegion WHERE Id = LEFT(@dcRegionId, 6)
		SET @l = LEN(@r)
		IF(LEFT(@a, @l) = @r)
			SET @a = RIGHT(@a, LEN(@a) - @l)
	END

	RETURN @a
END
GO

/****** Object:  UserDefinedFunction [dbo].[ClearChinese]    Script Date: 2018/12/13 18:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE function [dbo].[ClearChinese]
(
	@str VARCHAR(MAX)
)
RETURNS VARCHAR(Max)
AS
BEGIN
	DECLARE @I AS INT, @str1 AS VARCHAR(MAX), @c AS NCHAR(1)
	SELECT @I = 1, @str1 = ''
	WHILE @I <= LEN(@str)
	BEGIN
		SELECT @c = SUBSTRING(@str, @i, 1)
		IF DATALENGTH(@c) = LEN(@c)
			SELECT @str1 = @str1 + @c
		SELECT @I = @I + 1
	END
	RETURN @str1
END
GO

/****** Object:  UserDefinedFunction [dbo].[clearhtml]    Script Date: 2018/12/13 18:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[clearhtml]
(
	@maco varchar(MAX)
)
returns varchar(MAX)  
as  
begin  
    declare @randchar_one nvarchar(200)  
    declare @randchar_two nvarchar(200)  
       if(charindex('<<',@maco)>0)  
              begin  
                     set @randchar_one='D4678B36-B958-4274-B81E-BBA636CFB427';  
                     set @randchar_two='49E374CC-9E1A-4850-897C-27074DE32E7F';  
                     set @maco=replace(@maco,'<<',@randchar_one)  
                     set @maco=replace(@maco,'>>',@randchar_two)  
              end  
    declare @i int  
    while 1 = 1  
    begin  
       set @i=len(@maco)  
       set @maco=replace(@maco, substring(@maco,charindex('<',@maco),  
       charindex('>',@maco)-charindex('<',@maco)+1),space(0))  
       if @i=len( @maco )  
       break  
    end  
   
    set @maco=replace(@maco,' ','')  
    set @maco=replace(@maco,'&nbsp;','')  
    set @maco=ltrim(rtrim(@maco))  
    set @maco=replace(@maco,char(9),'')  
    set @maco=replace(@maco,char(10),'')  
    set @maco=replace(@maco,char(13),'')  
    if(charindex(@randchar_one,@maco)>0)  
    begin  
       set @maco=replace(@maco,'D4678B36-B958-4274-B81E-BBA636CFB427','<<')  
       set @maco=replace(@maco,'49E374CC-9E1A-4850-897C-27074DE32E7F','>>')  
    end  
    return (@maco)  
end  
GO

/****** Object:  UserDefinedFunction [dbo].[ClearHTMLTag]    Script Date: 2018/12/13 18:28:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ClearHTMLTag]
(
	@Cont VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @Txt VARCHAR(MAX), @I INT, @J INT
	SET @Txt = REPLACE(ISNULL(@Cont, ''), '&nbsp;', ' ')
	SELECT @i = CHARINDEX('<', @Txt, 0), @j = CHARINDEX('>', @Txt, 1)

	WHILE @i < @j
	BEGIN
		SELECT @Txt = REPLACE(@Txt, SUBSTRING(@txt, @i, @j - @i + 1), '')
		SELECT @i = CHARINDEX('<', @Txt, 0), @j = CHARINDEX('>', @Txt, 1)
	END

	RETURN @Txt
END
GO

/****** Object:  UserDefinedFunction [dbo].[CompanyNameNoCheck]    Script Date: 2018/12/13 18:28:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--信息员推广企业是，企业名称免检
--######################################################3
CREATE FUNCTION [dbo].[CompanyNameNoCheck]
(
	@CompanyName NVARCHAR(50)
)
RETURNS BIT
AS
BEGIN
	IF @CompanyName LIKE '%香港%'
		RETURN 1

	IF @CompanyName LIKE '%台湾%'
		RETURN 1

	IF @CompanyName LIKE '%澳门%'
		RETURN 1

	RETURN 0
END



GO

/****** Object:  UserDefinedFunction [dbo].[ComputeCvLevel]    Script Date: 2018/12/13 18:28:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ComputeCvLevel]
(
	@cvLevel CHAR(10),
	@Pos TINYINT,
	@Val CHAR(1)
)   
RETURNS CHAR(10)
AS
BEGIN
	DECLARE @Level AS CHAR(10)
	SET @Level = LEFT(@cvLevel, @Pos - 1) + @Val + RIGHT(@cvLevel,10 - @Pos)
	--IF LEFT(@cvLevel, 1) <> '1'
	--	SET @Level = '1' + RIGHT(@Level, 9)
	RETURN @Level
END

GO

/****** Object:  UserDefinedFunction [dbo].[ComputeJobValid]    Script Date: 2018/12/13 18:28:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[ComputeJobValid]
(	
	@IsDelete BIT,
	@VerifyResult TINYINT,
	@IssueDate SMALLDATETIME,
	@IssueEnd SMALLDATETIME
)
RETURNS BIT
AS
BEGIN
	IF @IsDelete = 1
		RETURN 0
	
	IF @VerifyResult<>1
		RETURN 0

	IF NOT(@IssueDate<GETDATE() AND @IssueEnd>GETDATE())
		RETURN 0

	RETURN 1
END

GO

/****** Object:  UserDefinedFunction [dbo].[CutWord]    Script Date: 2018/12/13 18:28:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Function [dbo].[CutWord]
(
	@InWord NVARCHAR(20)
)
RETURNS NVARCHAR(20)
AS
BEGIN
	IF CHARINDEX('select', @InWord) > 0
		RETURN ''
	IF CHARINDEX('script', @InWord) > 0
		RETURN ''
	IF CHARINDEX('delete', @InWord) > 0
		RETURN ''
	IF CHARINDEX('update', @InWord) > 0
		RETURN ''
	IF CHARINDEX('drop', @InWord) > 0
		RETURN ''

	IF LEN(@InWord) > 4
		IF EXISTS(SELECT 'x' FROM cpMainPublish WITH(NOLOCK) WHERE Name = @InWord)
			RETURN @InWord

	DECLARE @OutWord NVARCHAR(20), @Word NVARCHAR(20), @Word1 NVARCHAR(20), @Word2 NVARCHAR(20), @Word3 NVARCHAR(20), @Id INT, @WordType INT
	SELECT @OutWord = ' ', @Id = 0

	DECLARE @t AS TABLE(Id INT IDENTITY(1, 1), Word NVARCHAR(20))

	INSERT INTO @t
	SELECT * FROM dbo.FnSplit(@InWord, ' ')
	WHILE EXISTS(SELECT 'x' FROM @t WHERE Id > @Id )
	BEGIN
		SELEcT TOP 1 @Id = Id, @Word = Word FROM @t WHERE Id > @Id ORDER BY Id
		IF EXISTS(SELECT 'x' FROM @t WHERE Id < @Id AND WOrd = @Word)
			CONTINUE
		IF LEN(@Word) < 4
		BEGIN
			SET @OutWord = @OutWord + ' ' + @Word
			CONTINUE
		END

		IF EXISTS(SELECT 'x' FROM WordList WHERE Word = @Word)
			SET @OutWord = @OutWord + ' ' + @Word
		ELSE
		BEGIN
			SET @Word1 = ''
			SELECT TOP 1 @WordType = WordType, @Word1 = Word FROM WordList WHERE CHARINDEX(Word, @Word) = 1 ORDER BY WordType, ID
			IF @Word1 > ''
			BEGIN
				SET @Word2 = RIGHT(@Word, LEN(@Word) - LEN(@Word1))
				IF @WordType = 1
				BEGIN
					SET @WordType = 0
					SELECT TOP 1 @WordType = WordType FROM WordList WHERE Word = @Word2
					IF @WordType = 1
						SET @OutWord = @OutWord + ' ' + @Word
					ELSE
						IF @WordType = 0
						BEGIN
							SELECT TOP 1 @WordType = WordType, @Word3 = Word 
							FROM WordList WHERE CHARINDEX(Word, @Word2) = 1 ORDER BY WordType DESC, ID
							IF @WordType < 2
								SET @OutWord = @OutWord + ' ' + @Word
							ELSE
								SET @OutWord = @OutWord + ' ' + @Word1 + ' ' + @Word3 + ' ' + REPLACE(@Word2, @Word3, '')
						END
						ELSE
							SET @OutWord = @OutWord + ' ' + @Word1 + ' ' + @Word2
				END
				ELSE IF @WordType > 1
				BEGIN
					IF LEN(@Word) - LEN(@Word1) < 2
						SET @OutWord = @OutWord + ' ' + @Word
					ELSE
						SET @OutWord = @OutWord + ' ' + @Word1 + ' ' + RIGHT(@Word, LEN(@Word) - LEN(@Word1))
				END
				ELSE
					SET @OutWord = @OutWord + ' ' + @Word
			END
			ELSE
				SET @OutWord = @OutWord + ' ' + @Word
		END
	END

	RETURN LTRIM(@OutWord)
END
GO

/****** Object:  UserDefinedFunction [dbo].[cvMainValid]    Script Date: 2018/12/13 18:28:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[cvMainValid]
(	
	@ID INT
)
RETURNS TINYINT
AS
BEGIN
	DECLARE @cvValid AS TINYINT
	SELECT @cvValid = (
		case [IsCvHidden] 
			when (1) then (0) 
			else 
				case left([cvLevel],(6))+rtrim(CONVERT([char](2),[VerifyResult],(0))) 
					when '1111111' then 
						case left([cvLevelEng],(6))+rtrim(CONVERT([char](2),[VerifyResultEng],(0))) 
							when '1111111' then (2) 
							when '1111011' then (2) 
							else (1) 
						end 
					when '1111011' then 
						case left([cvLevelEng],(6))+rtrim(CONVERT([char](2),[VerifyResultEng],(0))) 
							when '1111111' then (2) 
							when '1111011' then (2) 
							else (1) 
						end 
					else 
						case left([cvLevelEng],(6))+rtrim(CONVERT([char](2),[VerifyResultEng],(0))) 
							when '1111111' then (3) 
							when '1111011' then (3) 
							else (0) 
						end 
				end 
		end)
	FROM cvMain WITH(NOLOCK) WHERE ID=@ID
	RETURN @cvValid
END
GO

/****** Object:  UserDefinedFunction [dbo].[DateTo]    Script Date: 2018/12/13 18:28:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--######################################3
--创建：Richard 2011-4-27
--日期转换
--######################################3
create FUNCTION [dbo].[DateTo]
(
	@d INT
)
RETURNS INT
AS
BEGIN	
	RETURN ((@d) / 60)*100+((@d) % 60)
END

GO

/****** Object:  UserDefinedFunction [dbo].[DateToReportDay]    Script Date: 2018/12/13 18:28:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################3
--创建：Lambo 2013-2-4
--日期转换 将日期转换成天数 2012-1-1为1
--######################################3
CREATE FUNCTION [dbo].[DateToReportDay]
(
	@d SMALLDATETIME
)
RETURNS INT
AS
BEGIN	
	RETURN DATEDIFF(DAY,'2012-1-1',@d)
END
GO

/****** Object:  UserDefinedFunction [dbo].[DeCrypt]    Script Date: 2018/12/13 18:28:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--实现用户名解密的函数
--######################################################3
CREATE FUNCTION [dbo].[DeCrypt]
(
	@strEncrypted VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
	IF @strEncrypted IS NULL
		RETURN ''
	SET @strEncrypted = LTRIM(RTRIM(@strEncrypted))
	DECLARE @GKey AS CHAR(69)
	DECLARE @I AS INT
	DECLARE @iC AS INT
	DECLARE @iK AS INT
	DECLARE @iD AS INT
	DECLARE @strDecrypted AS VARCHAR(50)

	SET @strDecrypted = ''
	SET @I = 1
	SET @GKey = Char(1) + Char(3) + Char(2) + Char(4) + Char(2) + Char(4) + Char(1) + Char(3) + Char(2) + Char(4) + Char(1) + Char(3) + Char(2) + Char(4) + Char(3) + Char(1) + Char(4) + Char(2) + Char(3) + Char(1) + Char(2) + Char(2) + Char(4) + Char(1) + Char(4) + Char(1) + Char(4) + Char(3) + Char(2) + Char(3) + Char(2) + Char(4) + Char(1) + Char(4) + Char(2) + Char(3) + Char(4) + Char(1) + Char(3) + Char(1) + Char(4) + Char(2) + Char(4) + Char(4) + Char(2) + Char(3) + Char(1) + Char(3) + Char(2) + Char(4) + Char(3) + Char(1) + Char(4) + Char(2) + Char(4) + Char(1) + Char(3) + Char(2) + Char(4) + Char(1) + Char(3) + Char(2) + Char(4) + Char(3) + Char(1) + Char(4) + Char(1) + Char(3) + Char(4)
	
	WHILE @I <= LEN(@strEncrypted)
	BEGIN
		SET @iC = ASCII(SUBSTRING(@strEncrypted, @I, 1))
		SET @iK = ASCII(SUBSTRING(@GKey, @I, 1))
		SET @iD = @iK ^ @iC
		SET @strDecrypted = @strDecrypted + CHAR(@iD)
		SET @I = @I + 1
	END
	RETURN @strDecrypted
END
GO

/****** Object:  UserDefinedFunction [dbo].[ExistUserName]    Script Date: 2018/12/13 18:28:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################3
--创建：Sean 2010-12-2
--推广时企业用户名是否可用
--######################################3
CREATE FUNCTION [dbo].[ExistUserName]
(
	@UserName VARCHAR(50)
)
RETURNS BIT
AS
BEGIN
	IF EXISTS(SELECT 'x' FROM caMain WITH(NOLOCK) WHERE UserNameLower = @UserName)
		RETURN 1
		
	IF EXISTS(SELECT 'x' FROM Company_xinxi WITH(NOLOCK) WHERE UserNameLower = @UserName)
		RETURN 1

	RETURN 0
END


GO

/****** Object:  UserDefinedFunction [dbo].[f_add]    Script Date: 2018/12/13 18:28:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[f_add](@date datetime,@i int)
returns datetime
as
begin
  declare @rt datetime
  set @rt=@date
  while @i>0
  begin
    set @rt=dateadd(d,1,@rt)
    if datepart(w,@rt) in(1,7) set @rt=dateadd(d,1,@rt)  --7为周六,1为周日
    if datepart(w,@rt) in(1,7) set @rt=dateadd(d,1,@rt)  --7为周六,1为周日
    set @i=@i-1
  end
  return @rt
end
GO

/****** Object:  UserDefinedFunction [dbo].[f_Convert]    Script Date: 2018/12/13 18:28:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[f_Convert]
(
	@str NVARCHAR(4000), 		--要转换的字符串
	@flag bit					--转换标志, 0转换成半角, 1转换成全角
)
RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @pat nvarchar(8), @step int, @i int, @spc int
    IF @flag  =  0
        SELECT @pat = N'%[！-～]%', @step = -65248, 
            @str = REPLACE(@str, N'　', N' ')
    ELSE
        SELECT @pat = N'%[!-~]%', @step = 65248, 
            @str = REPLACE(@str, N' ', N'　')
    SET @i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @str)
    WHILE @i > 0
		SELECT @str = REPLACE(@str,
			SUBSTRING(@str, @i, 1), 
            NCHAR(UNICODE(SUBSTRING(@str, @i, 1))+@step)), 
			@i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @str)

    RETURN(@str)
END
GO

/****** Object:  UserDefinedFunction [dbo].[FnctManageruserSuperior]    Script Date: 2018/12/13 18:28:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create  function   [dbo].[FnctManageruserSuperior](@sender int,@receiver int)   
  returns   int   
  as   
  begin   
declare @superior int,@r1 int,@r2 int,@r3 int,@s1 int,@s2 int,@s3 int


select @s1=superior from manageruser with(nolock) where id=@sender
select @r1=superior from manageruser with(nolock) where id=@receiver

select @s2=superior from manageruser with(nolock) where id=@s1
select @r2=superior from manageruser with(nolock) where id=@r1

select @s3=superior from manageruser with(nolock) where id=@s2
select @r3=superior from manageruser with(nolock) where id=@r2

if @s1=@r1
  set @superior=@s1
else if @s1=@r2
  set @superior=@s1
else if @s2=@r1
  set @superior=@r1
else if @s1=@r3
  set @superior=@s1
else if @r1=@s3
  set @superior=@r1
else if @r2=@s2
  set @superior=@s2
else if @r2=@s3
  set @superior=@r2
else if @s2=@r3
  set @superior=@s2
else if @r3=@s3
  set @superior=@s3
else
  set @superior=1
    return   @superior  
  end 
GO

/****** Object:  UserDefinedFunction [dbo].[fnRanString]    Script Date: 2018/12/13 18:28:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[fnRanString]
(
	@Length int
)
returns varchar(100)  
begin  
	DECLARE @RandomID varchar(32), @counter smallint, @RandomNumber float, @ValidCharactersLength int,
		@RandomNumberInt tinyint, @CurrentCharacter varchar(1), @ValidCharacters varchar(255)

	SET @ValidCharacters = 'abcdefghijklmnopqrstuvwxyz0123456789'
	SET @ValidCharactersLength = len(@ValidCharacters)
	SELECT @CurrentCharacter = '', @RandomNumber = 0, @RandomNumberInt = 0, @RandomID = '', @counter = 1
	WHILE @counter < (@Length + 1)  
	BEGIN  
		select @RandomNumber = re from v_rand  
		SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength -1) * @RandomNumber + 1));  
		SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1);  
		SET @counter = @counter + 1;  
		SET @RandomID = @RandomID + @CurrentCharacter;  
	END  

	Return @RandomID;  
end  
GO

/****** Object:  UserDefinedFunction [dbo].[FormatID]    Script Date: 2018/12/13 18:28:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#############################333
--格式化id，去掉所有不是数字的字符
--#############################333
CREATE FUNCTION [dbo].[FormatID]
(
	@s VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	IF @s IS NULL
		RETURN ''
	
	DECLARE @i AS INT
	DECLARE @c AS CHAR(1)
	DECLARE @R AS VARCHAR(MAX)
	SET @R = ''

	SET @i = 0
	WHILE @i < LEN(@s)
	BEGIN
		IF SUBSTRING(@s, @i + 1, 1) IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
		SET @R = @R + SUBSTRING(@s, @i + 1, 1)
		SET @i = @i +1
	END
	RETURN @R
END

GO

/****** Object:  UserDefinedFunction [dbo].[FormatIDS]    Script Date: 2018/12/13 18:28:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#############################333
--格式化id，去掉所有不是数字和，的字符
--#############################333
create FUNCTION [dbo].[FormatIDS]
(
	@s VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	IF @s IS NULL
		RETURN ''
	
	DECLARE @i AS INT
	DECLARE @c AS CHAR(1)
	DECLARE @R AS VARCHAR(MAX)
	SET @R = ''

	SET @i = 0
	WHILE @i < LEN(@s)
	BEGIN
		IF SUBSTRING(@s, @i + 1, 1) IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', ',')
		SET @R = @R + SUBSTRING(@s, @i + 1, 1)
		SET @i = @i +1
	END
	RETURN @R
END

GO

/****** Object:  UserDefinedFunction [dbo].[FormatMobile]    Script Date: 2018/12/13 18:28:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[FormatMobile]
(
	@str AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@str, '－', '-'), '０', '0'), '１', '0'), '２', '2'), '３', '3'), '４', '4'), '５', '5'), '６', '6'), '７', '7'), '８', '8'), '９', '9'), '（', '('), '）', ')'), '——', '-'), '—', '-'),'，', ','), '、', ',')	
END
GO

/****** Object:  UserDefinedFunction [dbo].[Full2Half]    Script Date: 2018/12/13 18:28:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Full2Half]
(
	@str NVARCHAR(4000), --要转换的字符串
	@flag bit              --转换标志,0转换成半角,1转换成全角
)
RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @pat nvarchar(8), @step int, @i int,@spc int
    IF @flag = 0
        SELECT @pat = N'%[！-～]%', @step = -65248,
            @str = REPLACE(@str, N'　', N' ')
    ELSE
        SELECT @pat = N'%[!-~]%', @step = 65248,
            @str = REPLACE(@str, N' ', N'　')
    SET @i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @str)
    WHILE @i > 0
        SELECT @str = REPLACE(@str,
                SUBSTRING(@str, @i, 1),
                NCHAR(UNICODE(SUBSTRING(@str, @i, 1)) + @step)),
				@i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @str)

    RETURN(@str)
END
GO

/****** Object:  UserDefinedFunction [dbo].[fullRegionName]    Script Date: 2018/12/13 18:28:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE FUNCTION [dbo].[fullRegionName]
(
	@Id VARCHAR(6)
)
RETURNS NVARCHAR(20)
BEGIN
 	RETURN (SELECT FullName FROM dcRegion WITH(NOLOCK) WHERE ID = @ID)
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetBeiSenCvText]    Script Date: 2018/12/13 18:28:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetBeiSenCvText]
(
	@cvMainId INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @paName NVARCHAR(10), @Sex NVARCHAR(2), @BirthDay NVARCHAR(20), @LivePlace NVARCHAR(30), @Mobile VARCHAR(20),
		@Email VARCHAR(100), @Career NVARCHAR(30), @WorkYear NVARCHAR(10), @Salary VARCHAR(50), @JobType VARCHAR(100),
		@Industry VARCHAR(100), @WorkPlace VARCHAR(100), @Speciality NVARCHAR(500)

	SELECT @paName = ISNULL(a.Name, ''), @Sex = CASE WHEN Gender = 0 THEN '男' ELSE '女' END, @BirthDay = ISNULL(BirthDay, ''), 
		@LivePlace = ISNULL((SELECT FullName FROM dcRegion WHERE Id = LivePlace), ''), 
		@Mobile = ISNULL(Mobile, ''), @Email = Email,
		@Career = ISNULL((SELECT Description FROM dcCareerStatus WHERE Id = dcCareerStatus), 1),
		@WorkYear =  LTRIM(STR(ISNULL(RelatedWorkYears, 0))) + '年', 
		@Salary = ISNULL((SELECT Description FROM dcSalary WHERE id = dcSalaryId), ''),
		@Speciality = ISNULL(Speciality, '')
	FROM paMain a WITH(NOLOCK), cvMain b WITH(NOLOCK)
	WHERE b.Id = @cvMainId
	AND b.paMainId = a.Id
	
	IF LEN(@BirthDay) = 6
		SET @BirthDay = LEFT(@BirthDay, 4) + '年' + RIGHT(@BirthDay, 2) + '月'

	SELECT @Industry = ISNULL(@Industry, '') + DESCRIPTION + ' '
	FROM dcIndustry a, cvIndustry b
	WHERE a.Id = b.dcIndustryId
	AND b.cvMainId = @cvMainID

	SELECT @WorkPlace = ISNULL(@WorkPlace, '') + DESCRIPTION + ' '
	FROM dcRegion a, cvJobPlace b
	WHERE a.Id = b.dcRegionId
	AND b.cvMainId = @cvMainID 

	SELECT @JobType = ISNULL(@JobType, '') + DESCRIPTION + ' '
	FROM dcJobtype a, cvJobType b
	WHERE a.Id = b.dcJobTypeId
	AND b.cvMainId = @cvMainID

	DECLARE @CvText NVARCHAR(MAX)
		SET @CvText = 
'<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>个人简历</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style>
tr{height:22px;}
td{font-size:10.0pt;padding-left:2px;padding-right:2px}
body{tab-interval:21.0pt}
dd{margin:0; padding:0; float:left; clear:both;}dl{margin:0; padding:0;}
</style>
</head>
<body>
  <h3>个人信息</h3>
  <table cellpadding="0" cellspacing="0" border="1" width="658" >
    <tr>
      <td><label>姓名：</label><span>' + ISNULL(@paName, '') + '</span></td>
      <td><label>性别：</label><span>' + ISNULL(@Sex, '') + '</span></td>
    </tr>
    <tr>
      <td><label>出生年月：</label><span>' + ISNULL(@BirthDay, '') + '</span></td>
      <td><label>现居住地：</label><span>' + ISNULL(@LivePlace, '') + '</span></td>
    </tr>
    <tr>
      <td><label>手机号：</label><span>' + ISNULL(@Mobile, '') + '</span></td>
      <td><label>电子邮箱：</label><span>' + ISNULL(@Email, '') + '</span></td>
    </tr>
  </table>'
	IF @Salary > '' AND @jobType > ''
		SET @cvText = @cvText + '
  <h3>求职意向</h3>
  <table cellpadding="0" cellspacing="0" border="1" width="658" >
    <tr>
      <td><label>求职状态：</label><span>' + ISNULL(@Career, '') + '</span></td>
      <td><label>相关工作经验：</label><span>' + ISNULL(@WorkYear, '') + '</span></td>
    </tr>
    <tr>
      <td><label>期望月薪：</label><span>' + ISNULL(@Salary, '') + '</span></td>
	  <td><label>期望职位类别：</label><span>' + ISNULL(@jobType, '') + '</span></td>
    </tr>
    <tr>
      <td><label>期望工作地点：</label><span>' + ISNULL(@WorkPlace, '') + '</span></td>
      <td colspan="2"><label>期望从事行业：</label><span>' + ISNULL(@Industry, '') + '</span></td>
    </tr>
  </table>'
	-----------------------------------
	DECLARE @GraduateCollage AS VARCHAR(100), @EduDetail AS NVARCHAR(500), @Graduation AS VARCHAR(20), 
		@Degree VARCHAR(10), @MajorName NVARCHAR(50)
	SELECT TOP 1 @GraduateCollage = ISNULL(GraduateCollage, ''), 
		@EduDetail = ISNULL(LEFT(Details, 500), ''),
		@Graduation = ISNULL(Graduation, ''),
		@Degree = ISNULL((SELECT Description FROM dcEducation WHERE id = Degree), ''),
		@MajorName = ISNULL(MajorName, '')
	FROM cvEducation WHERE cvMainId = @cvMainId ORDER BY Degree DESC, Graduation DESC
	IF LEN(@Graduation) = 6
		SET @Graduation = LEFT(@Graduation, 4) + '年' + RIGHT(@Graduation, 2) + '月'

	SET @cvText = @cvText + '
  <h3>教育背景</h3>
  <table cellpadding="0" cellspacing="0" border="1" width="658" >
    <tr>
      <td><label>学校名称：</label><span>' + ISNULL(@GraduateCollage, '') + '</span></td>
      <td><label>毕业时间：</label><span>' + ISNULL(@Graduation, '') + '</span></td>
    </tr>
    <tr>
      <td><label>学历：</label><span>' + ISNULL(@Degree, '') + '</span></td>
      <td><label>专业：</label><span>' + ISNULL(@MajorName, '') + '</span></td>
    </tr>'
	IF LEN(ISNULL(@EduDetail, '')) > 0
	SET @cvText = @cvText + '
    <tr>
      <td colspan="2"><label>学习经历：</label><span>' + @EduDetail + '</span></td>
    </tr>'
	SET @cvText = @cvText + '
  </table>'
	--------------------------------------------------
	DECLARE @CompanyName AS VARCHAR(100), @companyIndustry AS VARCHAR(50), @CompanyPost AS VARCHAR(50), @BeginDate VARCHAR(10), 
		@EndDate VARCHAR(10), @CompanyDesc NVARCHAR(500)
	SELECT TOP 1 @CompanyName = ISNULL(CompanyName, ''),
		@companyIndustry = ISNULL((SELECT Description FROM dcIndustry WHERE Id = dcIndustryId), ''),
		@CompanyPost = ISNULL(JobName, ''),
		@BeginDate = ISNULL(BeginDate, ''),
		@EndDate = ISNULL(EndDate, ''),
		@CompanyDesc = ISNULL(LEFT(Description, 500), '')
	FROM cvExperience WHERE cvMainId = @cvMainId ORDER BY BeginDate DESC, EndDate DESC
	IF LEN(@BeginDate) = 6 AND LEN(@EndDate) = 6
	BEGIN
		SET @cvText = @cvText + '
  <h3>工作经历</h3>
  <table cellpadding="0" cellspacing="0" border="1" width="658" >
    <tr>
      <td><label>企业名称：</label><span>' + ISNULL(@CompanyName, '') + '</span></td>
      <td><label>所属行业：</label><span>' + ISNULL(@companyIndustry, '') + '</span></td>
    </tr>
    <tr>
      <td><label>岗位名称：</label><span>' + ISNULL(@CompanyPost, '') + '</span></td>
      <td><label>工作时间：</label><span>' + LEFT(@BeginDate, 4) + '年' + RIGHT(@BeginDate, 2) + '月至' + CASE WHEN @EndDate = 999999 THEN '今' ELSE LEFT(@EndDate, 4) + '年' + RIGHT(@EndDate, 2) + '月' END + '</span></td>
    </tr>'
		IF @CompanyDesc > ''
			SET @cvText = @cvText + '
	<tr>
      <td colspan="2"><label>工作描述：</label><span>' + @CompanyDesc + '</span></td>
    </tr>'
		SET @cvText = @cvText + '
  </table>'
	END
	IF @Speciality > ''
		SET @cvTExt = @cvText + '
  <h3>工作能力</h3>
  <table cellpadding="0" cellspacing="0" border="1" width="658" >
    <tr>
      <td colspan="2"><label>工作描述：</label><span>' + @Speciality + '</span></td>
    </tr>
  </table>'
SET @CvText = @CvText + '
</body>
</html>'
	RETURN @cvText
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetBit]    Script Date: 2018/12/13 18:28:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create FUNCTION [dbo].[GetBit](@val INT, @pos INT)
RETURNS BIT
AS
BEGIN
	RETURN (@Val / POWER(2, @pos - 1)) & 1
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetCaName]    Script Date: 2018/12/13 18:28:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetCaName] 
(
	@caMainID INT
)
RETURNS VARCHAR(50)
AS
BEGIN
	DECLARE @Name VARCHAR(20)
	DECLARE @Gender BIT
	DECLARE @IsNameHide BIT
	SELECT @Name = Name, @Gender = Gender, @IsNameHide = IsNameHide
	FROM caMain WHERE ID = @caMainID
	IF @IsNameHide = 1
	BEGIN
		IF @Gender = 1
			SELECT @Name = LEFT(@Name,1) + '女士'
		ELSE
			SELECT @Name = LEFT(@Name,1) + '先生'
	END
	RETURN @Name
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetCaOnlineStatus]    Script Date: 2018/12/13 18:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetCaOnlineStatus]
(
	@caMainID INT
)
RETURNS BIT
AS
BEGIN
	DECLARE @IsOnline AS BIT
	SELECT @IsOnline = IsOnline FROM caOnline WITH(NOLOCK) WHERE caMainID = @caMainID
--	IF EXISTS(SELECT 'X' FROM caOnline WHERE caMainID = @caMainID AND (DATEDIFF(s, RefreshDate, GETDATE()) < 120))
--		SELECT @IsOnline = 1
	RETURN ISNULL(@IsOnline, 0)
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetChatPrivi]    Script Date: 2018/12/13 18:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetChatPrivi] 
(
    @cvMainID INT,
    @cpMainID INT
)
RETURNS BIT
AS
BEGIN
    IF EXISTS(
        SELECT TOP 1 'X' FROM ChatOnline WITH(NOLOCK) WHERE caMainID IN(
            SELECT ID FROM caMain WITH(NOLOCK) WHERE cpMainID = @cpMainID
        ) AND cvMainID = @cvMainID AND Initiative = 1
    )
    BEGIN
	IF EXISTS(SELECT TOP 1 'X' FROM cpMain WITH(NOLOCK) WHERE ID = @cpMainID AND MemberType < 2)
		RETURN 0
	ELSE
	        RETURN 1
    END

    RETURN dbo.CheckCvPrivi(@cvMainID, @cpMainID)
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetContactPrivi]    Script Date: 2018/12/13 18:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].[GetContactPrivi]
(
	@caMainID INT,
	@cvMainID INT
)
RETURNS TINYINT	--0无权限； 1显示手机号，email； 2显示手机号；3、显示手机号、email
AS
BEGIN
	DECLARE @cpMainID AS INT,
		@MemberType AS TINYINT,
		@Valid AS TINYINT,
		@VerifyResult AS TINYINT,
		@VerifyResultEng AS TINYINT,
		@IsPassed AS BIT,
		@IsCvHidden AS BIT

	SELECT @cpMainID = cpMainID FROM caMain WITH(NOLOCK) WHERE ID = @caMainId
	SELECT @MemberType = MemberType FROM cpMain WITH(NOLOCK) WHERE ID = @cpMainID

	SELECT @Valid = Valid, @VerifyResult = VerifyResult, @VerifyResultEng = VerifyResultEng, @IsCvHidden = IsCvHidden
	FROM cvMain WITH(NOLOCK) WHERE ID = @cvMainID

	----有问题
	IF @VerifyResult = 10 OR @VerifyResultEng = 10
		RETURN 0

	----未认证会员
	IF @MemberType IN(0, 1)
		RETURN 0

	----普通会员
	IF @MemberType IN(2, 3)
	BEGIN
		IF EXISTS(SELECT 'x' FROM exJobApply WITH(NOLOCK)
				WHERE cvMainID = @cvMainID AND IsPassed = 1
				AND JobID IN(SELECT ID FROM Job WITH(NOLOCK) WHERE cpMainID = @cpMainID))
			RETURN 1

		IF EXISTS(SELECT 'x' FROM caActiveLog WITH(NOLOCK)
				WHERE cpMainID = @cpMainID
				AND cvMainID = @cvMainID)
			RETURN 1

		IF EXISTS(SELECT 'x' FROM caDailyGiftLog WITH(NOLOCK)
				WHERE cpMainID = @cpMainID
				AND cvMainID = @cvMainID)
			RETURN 1

		RETURN 0
	END

	----正式会员
	IF @MemberType = 10
	BEGIN
		IF EXISTS(SELECT 'x' FROM exJobApply WITH(NOLOCK)
				WHERE cvMainID = @cvMainID AND IsPassed = 1
				AND JobID IN(SELECT ID FROM Job WITH(NOLOCK) WHERE cpMainID = @cpMainID))
			RETURN 3

		IF @IsCvHidden = 1
			RETURN 0

		RETURN 2		
	END

	----储值会员
	IF @MemberType = 11
	BEGIN
		IF EXISTS(SELECT 'x' FROM caCvQuotaLog WITH(NOLOCK)
				WHERE cvMainID = @cvMainID
				AND cpMainID = @cpMainID)
			RETURN 3

		IF @IsCvHidden = 1
			RETURN 0

		RETURN 2		
	END

	RETURN 0
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetCpIndustry]    Script Date: 2018/12/13 18:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetCpIndustry]
(
	@cpMainID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @strIndustry NVARCHAR(100)
	SET @strIndustry = ''
	SELECT @strIndustry = @strIndustry + RTRIM(LTRIM(b.Description)) + '，' FROM cpIndustry a WITH(NOLOCK),dcIndustry b  WITH(NOLOCK) WHERE a.dcIndustryID=b.ID AND a.cpMainID=@cpMainID
	IF LEN(@strIndustry) > 0
		SET @strIndustry = LEFT(@strIndustry,LEN(@strIndustry)-1)
	RETURN @strIndustry
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetCpIndustryId]    Script Date: 2018/12/13 18:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetCpIndustryId]
(
	@cpMainID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @strIndustry NVARCHAR(100)
	SET @strIndustry = ''
	SELECT @strIndustry = @strIndustry + RTRIM(LTRIM(dcIndustryID)) + ',' FROM cpIndustry WITH(NOLOCK)
	WHERE cpMainID = @cpMainID
	IF LEN(@strIndustry) > 0
		SET @strIndustry = LEFT(@strIndustry, LEN(@strIndustry)-1)
	RETURN @strIndustry
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetCpLogo]    Script Date: 2018/12/13 18:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetCpLogo]
(
	@cpMainID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @strLogoPath NVARCHAR(1000)
	SELECT @strLogoPath = ISNULL(LogoFile, '')
	FROM cpMain WITH(NOLOCK)
	WHERE ID = @cpMainID

	RETURN @strLogoPath
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetCvJobType]    Script Date: 2018/12/13 18:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetCvJobType]
(
	@cvMainID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @strJobType NVARCHAR(100)
	SET @strJobType = ''
	SELECT @strJobType = @strJobType + RTRIM(LTRIM(b.Description)) + '，' FROM cvJobType a,dcJobType b WHERE a.dcJobTypeID=b.ID AND a.cvMainID=@cvMainID
	IF LEN(@strJobType) > 0
		SET @strJobType = LEFT(@strJobType,LEN(@strJobType)-1)
	RETURN @strJobType
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetCvMatch]    Script Date: 2018/12/13 18:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--######################################3
--创建：Lambo 20120329	
--计算简历和职位的匹配度
--######################################3
CREATE FUNCTION [dbo].[GetCvMatch]
(
	@cvMainID INT,
	@JobID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN	
	DECLARE @JobTypeMatch AS DECIMAL(18,2)	--职位类别的匹配度
	DECLARE @RegionMatch AS DECIMAL(18,2)		--办公室地点的匹配度
	DECLARE @ExperienceMatch AS DECIMAL(18,2) --相关工作经验的匹配度
	DECLARE @SalaryMatch AS DECIMAL(18,2)		--月薪的匹配度
	DECLARE @EducationMatch AS DECIMAL(18,2)	--学历的匹配度
	DECLARE @IndustryMatch AS DECIMAL(18,2)	--行业的匹配度
	DECLARE @AgeMatch AS DECIMAL(18,2)		--年龄的匹配度

	SET @JobTypeMatch = 0 
	SET @RegionMatch = 0 
	SET @ExperienceMatch = 0 
	SET @SalaryMatch = 0 
	SET @EducationMatch = 0 
	SET @IndustryMatch = 0 
	SET @AgeMatch = 0 
	
	DECLARE @JobTypeID AS INT			--职位的主要职位类别
	DECLARE @dcRegionID AS INT			--办公室地点
	DECLARE @cpIndustry AS VARCHAR(100)	--企业所属行业
	DECLARE @MinExperience INT			--相关工作经验
	DECLARE	@dcSalaryID INT				--最低月薪
	DECLARE @dcEducationID AS INT		--学历要求
	DECLARE @MinAge AS INT				--最小年龄	
	DECLARE @MaxAge AS INT				--最大年龄
	SELECT 
		@JobTypeID=dcJobTypeID,
		@dcRegionID = dcRegionID,
		@MinExperience = MinExperience,
		@dcSalaryID = dcSalaryID,
		@dcEducationID = dcEducationID,
		@MinAge = MinAge,
		@MaxAge = MaxAge,
		@cpIndustry = ','+STUFF((SELECT ','+LTRIM(a.dcIndustryid)+',c'+LTRIM(b.CateGoryId) FROM cpIndustry a,dcIndustryCategory b WHERE a.dcIndustryid = b.dcIndustryid AND cpMainid = JOB.cpMainID FOR XML PATH('')),1,1,'')+','
	FROM JOB WITH(NOLOCK)
	WHERE ID = @JobID

	DECLARE @cvJobType AS INT	--期望职位类别
	DECLARE @cvJobPlace AS VARCHAR(100)	--期望工作地点
	DECLARE @cvIndustry AS VARCHAR(100)	--期望从事行业
	DECLARE @cvRelatedWorkYears AS INT	--个人工作经验			
	DECLARE @cvDcSalaryID AS INT		--期望月薪
	DECLARE @IsNegotiable AS BIT		--是否面议
	DECLARE @Degree AS INT				--个人学历
	DECLARE @Age AS INT					--个人年龄

	DECLARE @INTTemp AS INT		--临时变量
	DECLARE @INTTemp2 AS INT

	--匹配职位类别
	SELECT @JobTypeMatch = ISNULL(MAX(
		CASE 
			WHEN dcJobTypeID=@JobTypeID OR @JobTypeID LIKE LTRIM(dcJobTypeID)+'%'  THEN 1 
			WHEN LEFT(LTRIM(@JobTypeID),2) = LEFT(dcJobTypeID,2) THEN 0.5
		END),'0') 
	FROM  cvJobType WITH(NOLOCK) WHERE cvMainID=@cvMainID
/*
	IF @JobTypeMatch <> 1 
		SELECT @JobTypeMatch = ISNULL(Max(a.Coefficient)/10.0,@JobTypeMatch) 
		FROM dcJobTypeSimilar a WITH(NOLOCK) ,cvJobType b WITH(NOLOCK) 
		WHERE b.cvMainID=@cvMainID
		AND (
				(
				a.dcJobTypeID = @JobTypeID
				AND a.SimilarId = b.dcJobTypeID
				)
			OR 
				(
				SimilarId = @JobTypeID 
				AND a.dcJobTypeID = b.dcJobTypeID
				) 
			)
*/
	--匹配工作地点	
	IF EXISTS( SELECT top 1 'x' FROM cvJobPlace WITH(NOLOCK) 
				WHERE cvMainID = @cvMainID 
				AND (
					dcRegionID = @dcRegionID 
					OR 
					(
						@dcRegionID LIKE ''+LTRIM(dcRegionID)+'%' AND (LEN(dcRegionID)=4 Or LEN(dcRegionID)=2)
					)
				)
			 )
		SET @RegionMatch = 1

	--循环匹配行业	
	DECLARE @iIndex AS INT
	SET @iIndex = 0
	DECLARE #Industry CURSOR FOR 
		SELECT a.dcIndustryID,b.CategoryID 
		FROM  cvIndustry a WITH(NOLOCK) ,dcIndustryCategory b WITH(NOLOCK) 
		WHERE a.dcIndustryid = b.dcIndustryid 
		AND  cvMainID=@cvMainID 
		ORDER BY a.dcIndustryID
	OPEN #Industry
	FETCH NEXT FROM  #Industry INTO @INTTemp,@INTTemp2
	WHILE @@FETCH_STATUS = 0 AND @IndustryMatch<>1
	BEGIN 
		SET @iIndex = @iIndex + 1
		IF CHARINDEX(','+LTRIM(@INTTemp)+',',LTRIM(@cpIndustry))>0
			SET @IndustryMatch=1
		ELSE IF CHARINDEX(',c'+LTRIM(@INTTemp2)+',',LTRIM(@cpIndustry))>0
			SET @IndustryMatch=0.5
		FETCH NEXT FROM  #Industry INTO @INTTemp,@INTTemp2
	END
	CLOSE #Industry
	DEALLOCATE #Industry
	IF @iIndex = 0
		SET @IndustryMatch = 1

	--取简历数据
	SELECT
		@Degree = Degree,
		@cvRelatedWorkYears = ISNULL(RelatedWorkYears,0),
		@cvDcSalaryID = dcSalaryID,
		@IsNegotiable = IsNegotiable,
		@Age = dbo.BirthToAge(b.Birthday)
	From cvMain a WITH(NOLOCK), paMain b WITH(NOLOCK)
	WHERE a.id=@cvMainID 
	AND a.paMainID = b.ID

	--匹配工作经验
	IF @MinExperience = 0
		SET @ExperienceMatch = 1
	ELSE IF @MinExperience = 1
	BEGIN
		IF @cvRelatedWorkYears<1
			SET @ExperienceMatch = 	0.5
		ELSE IF @cvRelatedWorkYears>2
			SET @ExperienceMatch = 	1 + (@cvRelatedWorkYears-2) * -0.1
		ELSE
			SET @ExperienceMatch = 1	
	END	
	ELSE IF @MinExperience = 2
	BEGIN
		IF @cvRelatedWorkYears<3
		BEGIN
			IF @cvRelatedWorkYears >=1 AND @cvRelatedWorkYears <=2
				SET @ExperienceMatch = 	0.5
		END
		ELSE IF @cvRelatedWorkYears>5
			SET @ExperienceMatch = 	1 + (@cvRelatedWorkYears-2) * -0.1
		ELSE
			SET @ExperienceMatch = 1	
	END
	ELSE IF @MinExperience = 3
	BEGIN
		IF @cvRelatedWorkYears<6
		BEGIN
			IF @cvRelatedWorkYears >=3 AND @cvRelatedWorkYears <=5
				SET @ExperienceMatch = 	0.5
		END
		ELSE IF @cvRelatedWorkYears>10
			SET @ExperienceMatch = 	1 + (@cvRelatedWorkYears-2) * -0.1
		ELSE
			SET @ExperienceMatch = 1	
	END
	ELSE IF @MinExperience = 4
	BEGIN
		IF @cvRelatedWorkYears<10
		BEGIn
			IF @cvRelatedWorkYears >=6 AND @cvRelatedWorkYears <=10
				SET @ExperienceMatch = 	0.5
		END
		ELSE
			SET @ExperienceMatch = 1	
	END
	ELSE IF @MinExperience = 5
		SET @ExperienceMatch = 1 +(ISNULL(@cvRelatedWorkYears,0) * -0.1)
	ELSE
		SET @ExperienceMatch = 1	

	--匹配月薪
	IF @dcSalaryID = @cvDcSalaryID OR @dcSalaryID = 100 OR @IsNegotiable = 1
		SET @SalaryMatch = 1
	ELSE IF @dcSalaryID - @cvDcSalaryID = 1 OR @dcSalaryID - @cvDcSalaryID = -1 
		SET @SalaryMatch = 0.5
		
	--匹配学历	
	IF @Degree = @dcEducationID 
		SET @EducationMatch = 1
	ELSE IF @Degree - @dcEducationID = 1
		SET @EducationMatch = 0.8
	ELSE IF @Degree - @dcEducationID = 2
		SET @EducationMatch = 0.5
		
	--匹配年龄
	IF (@MinAge = 99 AND @MaxAge = 99) OR (@MinAge = 0 AND @MaxAge = 0)
		SET @AgeMatch = 1
	ELSE IF @Age < @MinAge
	BEGIN
		IF @MinAge - @Age = 1
			SET @AgeMatch = 0.8
		ELSE IF @MinAge-@Age = 2
			SET @AgeMatch = 0.5
	end
	ELSE IF @Age > @MaxAge
	BEGIN
		IF @Age - @MaxAge = 1
			SET @AgeMatch = 0.8
		ELSE IF @Age - @MaxAge = 2
			SET @AgeMatch = 0.5
	END
	ELSE
		SET @AgeMatch = 1
	RETURN CONVERT(INT,(@JobTypeMatch+@RegionMatch+@ExperienceMatch+@SalaryMatch+@EducationMatch+@IndustryMatch+@AgeMatch)/7*100)
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetJobFullRegionName]    Script Date: 2018/12/13 18:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetJobFullRegionName]
(
	@JobID INT
)
RETURNS NVARCHAR(100)
AS
BEGIN
	DECLARE @strRegionID NVARCHAR(100)
	SET @strRegionID = ''
	
	SELECT @strRegionID = @strRegionID + RTRIM(LTRIM(b.fullname)) + ','
	FROM JobRegion a WITH(NOLOCK), 
		dcRegion b WITH(NOLOCK)
	WHERE a.JobID = @JobID
	AND a.dcRegionID = b.ID

	IF LEN(@strRegionID) > 0 
		SET @strRegionID = LEFT(@strRegionID, LEN(@strRegionID) - 1)
	
	RETURN @strRegionID
END

GO

/****** Object:  UserDefinedFunction [dbo].[GetJobIds]    Script Date: 2018/12/13 18:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetJobIds]
(
	@cpMainID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @ids AS VARCHAR(MAX)
	SET @ids = ''
	SELECT TOP 10 @ids = @ids + 
		CASE LEN(@ids) WHEN 0 THEN '' ELSE ',' END +
		--ISNULL((SELECT IsFollow FROM MarketDb..PageBaiduInclude WHERE PageType = 2 AND PageId = SecondID), '') +
		LTRIM(STR(ID))
	FROM Job WITH(NOLOCK)
	WHERE cpMainId = @cpMainId
	AND Valid = 1
	ORDER BY DisplayNo, CONVERT(VARCHAR(4), dcJobtypeId)
	
	RETURN @ids
END

GO

/****** Object:  UserDefinedFunction [dbo].[GetJobIdsNew]    Script Date: 2018/12/13 18:28:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetJobIdsNew]
(
	@cpMainID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @ids AS VARCHAR(MAX)
	SET @ids = ''
	SELECT TOP 20 @ids = @ids + CASE LEN(@ids) WHEN 0 THEN '' ELSE ',' END +
		--(SELECT IsFollow FROM MarketDb..PageBaiduInclude WHERE PageType = 2 AND PageId = ID) + 
		LTRIM(STR(ID))
	FROM Job WITH(NOLOCK)
	WHERE cpMainId = @cpMainId
	AND Valid = 1
	ORDER BY DisplayNo, CONVERT(VARCHAR(4), dcJobtypeId)
	
	RETURN @ids
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetJobIdsOld]    Script Date: 2018/12/13 18:28:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].[GetJobIdsOld]
(
	@cpMainID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @ids AS VARCHAR(MAX)
	SET @ids = ''
	SELECT TOP 20 @ids = @ids + CASE LEN(@ids) WHEN 0 THEN '' ELSE ',' END + LTRIM(STR(ID))
	FROM Job WITH(NOLOCK)
	WHERE cpMainId = @cpMainId
	AND Valid = 1
	ORDER BY DisplayNo, CONVERT(VARCHAR(4), dcJobtypeId)
	
	RETURN @ids
END




GO

/****** Object:  UserDefinedFunction [dbo].[GetJobNames]    Script Date: 2018/12/13 18:28:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetJobNames]
(
	@cpMainID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @ids AS VARCHAR(MAX)
	SET @ids = ''
	SELECT TOP 10 @ids = @ids + CASE LEN(@ids) WHEN 0 THEN '' ELSE ',' END + REPLACE(Name, ',', '，')
	FROM Job WITH(NOLOCK)
	WHERE cpMainId = @cpMainId
	AND Valid = 1
	ORDER BY DisplayNo, CONVERT(VARCHAR(4), dcJobtypeId), ID
	
	RETURN @ids
END




GO

/****** Object:  UserDefinedFunction [dbo].[GetJobSecondIds]    Script Date: 2018/12/13 18:28:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetJobSecondIds]
(
	@cpMainID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @ids AS VARCHAR(MAX)
	SET @ids = ''
	SELECT TOP 10 @ids = @ids + 
		CASE LEN(@ids) WHEN 0 THEN '' ELSE ',' END +
		--ISNULL((SELECT '_' FROM MarketDb..PageBaiduInclude WITH(NOLOCK) WHERE PageType = 2 AND PageId = SecondID AND IsFollow = 'n'), '') + 
		SecondId
	FROM Job WITH(NOLOCK)
	WHERE cpMainId = @cpMainId
	AND Valid = 1
	ORDER BY DisplayNo, CONVERT(VARCHAR(4), dcJobtypeId), ID
	
	RETURN @ids
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetLotteryGiftType]    Script Date: 2018/12/13 18:28:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetLotteryGiftType]
(
	@IsAgent TINYINT,
	@GiftNo INT,
	@IsVip TINYINT
)
RETURNS TINYINT
AS
BEGIN
	--奖品类型：1、50职位并发  2、50份简历 3、记事本（100份简历） 4、100条短信数 5、2周六分之一图片 6、2周知名企业 7、2周职位置顶  8、200次职位刷新（2周职位自动刷新）  9、2周六分之二 10、蓝皮书（Vip会员—季度） 11、满1000-50优惠券 12、电热水壶 99、没中奖
	DECLARE @GiftType TINYINT
	IF @IsAgent = 0
	BEGIN
		IF @IsVip = 0
		BEGIN
			IF @GiftNo BETWEEN 1 AND 100			--六分之一广告 10%概率
				SET @GiftType = 5
			ELSE IF @GiftNo BETWEEN 101 AND 250		--2周知名企业 15%概率
				SET @GiftType = 6
			ELSE IF @GiftNo BETWEEN 251 AND 300		--两周职位置顶 5%概率
				SET @GiftType = 7
			ELSE IF @GiftNo BETWEEN 301 AND 450		--200次职位刷新 15%概率
				SET @GiftType = 8
			ELSE IF @GiftNo BETWEEN 451 AND 550		--六分之二广告 10%概率
				SET @GiftType = 9
			ELSE						--满1000-50优惠券 45%概率
				SET @GiftType = 11
		END
		ELSE
		BEGIN
			IF @GiftNo BETWEEN 1 AND 150 			--50职位并发 15%概率
				SET @GiftType = 1
			ELSE IF @GiftNo BETWEEN 151 AND 300		--50份简历 15%概率
				SET @GiftType = 2
			ELSE IF @GiftNo BETWEEN 301 AND 430		--100条短信数 13%概率
				SET @GiftType = 4
			ELSE IF @GiftNo BETWEEN 431 AND 530		--六分之一广告 10%概率
				SET @GiftType = 5
			ELSE IF @GiftNo BETWEEN 531 AND 630		--2周知名企业 10%概率
				SET @GiftType = 6
			ELSE IF @GiftNo BETWEEN 631 AND 680		--两周职位置顶 5%概率
				SET @GiftType = 7
			ELSE IF @GiftNo BETWEEN 681 AND 880 		--200次职位刷新 20%概率
				SET @GiftType = 8
			ELSE IF @GiftNo BETWEEN 881 AND 980		--六分之二广告 10%概率
				SET @GiftType = 9
			ELSE						--满1000-50优惠券 10%概率
				SET @GiftType = 11
		END
	END
	ELSE
	BEGIN
		IF @GiftNo BETWEEN 1 AND 300 			--50职位并发 30%概率
			SET @GiftType = 1
		ELSE IF @GiftNo BETWEEN 301 AND 500		--50份简历 20%概率
			SET @GiftType = 2
		ELSE IF @GiftNo BETWEEN 501 AND 600		--100条短信数 10%概率
			SET @GiftType = 4
		ELSE 						--200次自动刷新 50%概率
			SET @GiftType = 8
	END
	RETURN @GiftType
END

GO

/****** Object:  UserDefinedFunction [dbo].[GetMemberTypeWithRealName]    Script Date: 2018/12/13 18:28:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################3
--创建：Sean 2010-11-16
--判断企业用户类型
--######################################3
CREATE FUNCTION [dbo].[GetMemberTypeWithRealName]
(
	@ID INT
)
RETURNS TINYINT
AS
BEGIN
	DECLARE @HasLicence AS BIT, @BalanceDate AS SMALLDATETIME, @UnlimitedDate AS SMALLDATETIME,
		@Address AS VARCHAR(100), @Name AS NVARCHAR(6), @Gender AS BIT,
		@MemberType AS TINYINT, @IsDelete AS BIT, @Mobile VARCHAR(100), @RealName TINYINT

	SELECT @HasLicence = ISNULL(HasLicence, 0),
		@BalanceDate = ISNULL(BalanceDate, '1900-1-1'),
		@UnlimitedDate = ISNULL(UnlimitedDate, '1900-1-1'),
		@Address = ISNULL(Address, ''),
		@IsDelete = IsDelete, @RealName = RealName
	FROM cpMain WITH(NOLOCK)
	WHERE ID = @ID
 
	SELECT @Name = Name, @Gender = Gender, @Mobile = ISNULL(Mobile, ISNULL(TelePhone, '')) FROM caMain WITH(NOLOCK)
	WHERE cpMainID = @ID AND AccountType = 1
  
	----企业资料不完整,=0
	IF ISNULL(@Address, '') = '' OR @IsDelete = 1 OR ISNULL(@Name, '') = '' OR @Gender IS NULL OR @Mobile = ''
		SET @MemberType = 0
	----已经上传营业执照,只能是普通会员：2、无限制：10、储值：11、vip：3之一
	ELSE IF @HasLicence = 0
		SET @MemberType = 1
	----检查订单
	ELSE IF EXISTS(SELECT 'x' FROM caOrder WITH(NOLOCK)
			WHERE cpMainID = @ID AND OrderType = 8
			AND BeginDate <= GETDATE() AND EndDate > DATEADD(MINUTE, -4, GETDATE())
			AND IsDeleted = 0
			)
	BEGIN
		IF @RealName = 1
			SET @MemberType = 31
		ELSE
		SET @MemberType = 3
	END
	ELSE
	BEGIN
		IF @RealName = 1
			SET @MemberType = 21
		ELSE
			SET @MemberType = 2
	END

	RETURN @MemberType
END













GO

/****** Object:  UserDefinedFunction [dbo].[GetNextMSSchedule]    Script Date: 2018/12/13 18:28:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create function [dbo].[GetNextMSSchedule]
(
	@msScheduleId AS INT
)
RETURNS SMALLDATETIME
AS
BEGIN
	DECLARE @FreqType AS TINYINT,
		@FreqINTerval AS TINYINT,
		@FreqSubdayType AS TINYINT,
		@FreqSubdayInterval AS SMALLINT,
		@ActiveStartDate AS VARCHAR(8),
		@ActiveEndDate AS VARCHAR(8),
		@ActiveStartTime AS VARCHAR(4),
		@ActiveEndTime AS VARCHAR(4),
		@StartDate AS SMALLDATETIME,
		@EndDate AS SMALLDATETIME,
		@NextRunTime AS SMALLDATETIME,
		@PlanTime AS SMALLDATETIME,
		@I AS INT,
		@II AS INT,
		@Exceeding AS INT,
		@IsOk AS INT,
		@Now AS SMALLDATETIME
	--获取计划任务数据
	SELECT @FreqType = FreqType, 
		@FreqInterval = FreqInterval,
		@FreqSubdayType = FreqSubdayType,
		@FreqSubdayInterval = FreqSubdayInterval,
		@ActiveStartDate = CONVERT(VARCHAR(8), ActiveStartDate),
		@ActiveEndDate = CONVERT(VARCHAR(8), ActiveEndDate),
		@ActiveStartTime = CONVERT(VARCHAR(4), ActiveStartTime),
		@ActiveEndTime = CONVERT(VARCHAR(4), ActiveEndTime),
		@IsOk = 0,
		@Now = CONVERT(VARCHAR(16), GETDATE(),120)
	FROM MainDb..msSchedule WITH(NOLOCK)
	WHERE ID = @msScheduleId

	--计算开始时间和结束时间
	SELECT @StartDate = dbo.MergeDate(@ActiveStartDate, @ActiveStartTime),
		@EndDate = dbo.MergeDate(@ActiveEndDate, @ActiveEndTime)

	IF @EndDate <= @Now		--如果结束时间<当前，直接退出
	BEGIN
		SELECT @NextRunTime = NULL
	END
	ELSE IF @FreqType = 1			--执行一次
	BEGIN
		--如果开始时间>当前，则返回开始时间，否则返回null
		IF @StartDate > @Now
			SELECT @NextRunTime = @StartDate
		ELSE
			SELECT @NextRunTime = NULL
	END
	ELSE IF @FreqType = 4			--每日执行
	BEGIN
		IF @StartDate > @Now	--如果开始时间>当前，下一次=开始时间
			SELECT @PlanTime = @StartDate
		ELSE
		BEGIN
			--计算从今天起再过几天（@Exceeding）是下次执行日期
			SELECT @Exceeding = DATEDIFF(DAY, @StartDate, @Now) % @FreqInterval
			IF(@Exceeding > 0)
				SELECT @Exceeding = @FreqInterval - @Exceeding

			--得出自今日-点期最近一次执行日期
			SELECT @PlanTime = dbo.MergeDate(@Now + @Exceeding, @ActiveStartTime)
			IF @PlanTime > @Now
				SELECT @NextRunTime = @PlanTime, @IsOk = 1
			ELSE
			BEGIN
				--下次执行日期<当前
				--如果间隔内循环 分钟
				IF @FreqSubdayType >= 4
				BEGIN
					IF @FreqSubdayType = 8
						SELECT @FreqSubdayInterval = @FreqSubdayInterval * 60
					--计算到下次执行，间隔多少分钟
					SELECT @Exceeding = DATEDIFF(MINUTE, @PlanTime, @Now) % @FreqSubdayInterval
					IF(@Exceeding > 0)
						SELECT @Exceeding = @FreqSubdayInterval - @Exceeding

					SELECT @NextRunTime = DATEADD(MINUTE, @Exceeding, @Now)
					IF @NextRunTime <= @Now	--如果是当前一分钟，则+1分钟
						SELECT @NextRunTime = DATEADD(MINUTE, @FreqSubdayInterval, @NextRunTime)
					IF @NextRunTime < CONVERT(VARCHAR(10), @Now + 1, 120)
						SELECT @IsOk = 1
				END
				IF(@IsOk = 0)	--间隔内不循环或者本日无法循环，则到下一执行日期
					SET @NextRunTime = @PlanTime + @FreqInterval
			END
		END
	END
	ELSE IF @FreqType = 8	--按周间隔，周、月都是执行一次
	BEGIN
		DECLARE @w AS INT, @TodayPower AS INT
		SELECT @w = DATEPART(W, @Now)	--1周日，2 周一，。。。7周六
		SELECT @TodayPower = POWER(2, @w - 1), @i = 8, @Exceeding = 7

		IF (@FreqInterval & @TodayPower = @TodayPower)	--今日是执行日期
		BEGIN
			SELECT @PlanTime = dbo.MergeDate(@Now, @ActiveStartTime)
			IF @PlanTime > @Now		--执行时间
				SELECT @NextRunTime = @PlanTime, @IsOk = 1
		END

		IF(@IsOk = 0)	--本日不执行，或者本日时间已过，则到下一执行日期
		BEGIN
			WHILE @I < 15
			BEGIN
				IF (@FreqInterval & POWER(2, @I - 8) = POWER(2, @I - 8))
				BEGIN
					SELECT @II = (@i - @w) % 7
					IF(@Exceeding > @II AND @II > 0)
						SET @Exceeding = @II
				END
				SET @i = @i + 1
			END
			SELECT @NextRunTime = dbo.MergeDate(@Now, @ActiveStartTime) + @Exceeding, @IsOk = 1
		END
	END
	ELSE IF @FreqType = 16	--按月间隔，周、月都是执行一次
	BEGIN
		SELECT @Exceeding = DATEDIFF(MONTH, @StartDate, @Now) / @FreqInterval, @I = 1
		SELECT @PlanTime = DATEADD(MONTH, @Exceeding * @FreqInterval, @StartDate)
		WHILE @PlanTime < @Now
		BEGIN
			SELECT @PlanTime = DATEADD(MONTH, @FreqInterval, @PlanTime)
			SELECT @I = @I + 1	
		END
		SET @NextRunTime = @PlanTime
	END
	RETURN @NextRunTime
END


GO

/****** Object:  UserDefinedFunction [dbo].[GetNextSchedule]    Script Date: 2018/12/13 18:28:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[GetNextSchedule]
(
	@schScheduleId AS INT
)
RETURNS SMALLDATETIME
AS
BEGIN
	DECLARE @FreqType AS TINYINT,
		@FreqINTerval AS TINYINT,
		@FreqSubdayType AS TINYINT,
		@FreqSubdayInterval AS SMALLINT,
		@ActiveStartDate AS VARCHAR(8),
		@ActiveEndDate AS VARCHAR(8),
		@ActiveStartTime AS VARCHAR(4),
		@ActiveEndTime AS VARCHAR(4),
		@StartDate AS SMALLDATETIME,
		@EndDate AS SMALLDATETIME,
		@NextRunTime AS SMALLDATETIME,
		@PlanTime AS SMALLDATETIME,
		@I AS INT,
		@II AS INT,
		@Exceeding AS INT,
		@IsOk AS INT,
		@Now AS SMALLDATETIME
	--获取计划任务数据
	SELECT @FreqType = FreqType, 
		@FreqInterval = FreqInterval,
		@FreqSubdayType = FreqSubdayType,
		@FreqSubdayInterval = FreqSubdayInterval,
		@ActiveStartDate = CONVERT(VARCHAR(8), ActiveStartDate),
		@ActiveEndDate = CONVERT(VARCHAR(8), ActiveEndDate),
		@ActiveStartTime = CONVERT(VARCHAR(4), ActiveStartTime),
		@ActiveEndTime = CONVERT(VARCHAR(4), ActiveEndTime),
		@IsOk = 0,
		@Now = CONVERT(VARCHAR(16), GETDATE(),120)
	FROM MainDb..schSchedule WITH(NOLOCK)
	WHERE ID = @schScheduleId

	--计算开始时间和结束时间
	SELECT @StartDate = dbo.MergeDate(@ActiveStartDate, @ActiveStartTime),
		@EndDate = dbo.MergeDate(@ActiveEndDate, @ActiveEndTime)

	IF @EndDate <= @Now		--如果结束时间<当前，直接退出
	BEGIN
		SELECT @NextRunTime = NULL
	END
	ELSE IF @FreqType = 1			--执行一次
	BEGIN
		--如果开始时间>当前，则返回开始时间，否则返回null
		IF @StartDate > @Now
			SELECT @NextRunTime = @StartDate
		ELSE
			SELECT @NextRunTime = NULL
	END
	ELSE IF @FreqType = 4			--每日执行
	BEGIN
		IF @StartDate > @Now	--如果开始时间>当前，下一次=开始时间
			SELECT @PlanTime = @StartDate
		ELSE
		BEGIN
			--计算从今天起再过几天（@Exceeding）是下次执行日期
			SELECT @Exceeding = DATEDIFF(DAY, @StartDate, @Now) % @FreqInterval
			IF(@Exceeding > 0)
				SELECT @Exceeding = @FreqInterval - @Exceeding

			--得出自今日-点期最近一次执行日期
			SELECT @PlanTime = dbo.MergeDate(@Now + @Exceeding, @ActiveStartTime)
			IF @PlanTime > @Now
				SELECT @NextRunTime = @PlanTime, @IsOk = 1
			ELSE
			BEGIN
				--下次执行日期<当前
				--如果间隔内循环 分钟
				IF @FreqSubdayType >= 4
				BEGIN
					IF @FreqSubdayType = 8
						SELECT @FreqSubdayInterval = @FreqSubdayInterval * 60
					--计算到下次执行，间隔多少分钟
					SELECT @Exceeding = DATEDIFF(MINUTE, @PlanTime, @Now) % @FreqSubdayInterval
					IF(@Exceeding > 0)
						SELECT @Exceeding = @FreqSubdayInterval - @Exceeding

					SELECT @NextRunTime = DATEADD(MINUTE, @Exceeding, @Now)
					IF @NextRunTime <= @Now	--如果是当前一分钟，则+1分钟
						SELECT @NextRunTime = DATEADD(MINUTE, @FreqSubdayInterval, @NextRunTime)
					IF @NextRunTime < CONVERT(VARCHAR(10), @Now + 1, 120)
						SELECT @IsOk = 1
				END
				IF(@IsOk = 0)	--间隔内不循环或者本日无法循环，则到下一执行日期
					SET @NextRunTime = @PlanTime + @FreqInterval
			END
		END
	END
	ELSE IF @FreqType = 8	--按周间隔，周、月都是执行一次
	BEGIN
		DECLARE @w AS INT, @TodayPower AS INT
		SELECT @w = DATEPART(W, @Now)	--1周日，2 周一，。。。7周六
		SELECT @TodayPower = POWER(2, @w - 1), @i = 8, @Exceeding = 7

		IF (@FreqInterval & @TodayPower = @TodayPower)	--今日是执行日期
		BEGIN
			SELECT @PlanTime = dbo.MergeDate(@Now, @ActiveStartTime)
			IF @PlanTime > @Now		--执行时间
				SELECT @NextRunTime = @PlanTime, @IsOk = 1
		END

		IF(@IsOk = 0)	--本日不执行，或者本日时间已过，则到下一执行日期
		BEGIN
			WHILE @I < 15
			BEGIN
				IF (@FreqInterval & POWER(2, @I - 8) = POWER(2, @I - 8))
				BEGIN
					SELECT @II = (@i - @w) % 7
					IF(@Exceeding > @II AND @II > 0)
						SET @Exceeding = @II
				END
				SET @i = @i + 1
			END
			SELECT @NextRunTime = dbo.MergeDate(@Now, @ActiveStartTime) + @Exceeding, @IsOk = 1
		END
	END
	ELSE IF @FreqType = 16	--按月间隔，周、月都是执行一次
	BEGIN
		SELECT @Exceeding = DATEDIFF(MONTH, @StartDate, @Now) / @FreqInterval, @I = 1
		SELECT @PlanTime = DATEADD(MONTH, @Exceeding * @FreqInterval, @StartDate)
		WHILE @PlanTime < @Now
		BEGIN
			SELECT @PlanTime = DATEADD(MONTH, @FreqInterval, @PlanTime)
			SELECT @I = @I + 1	
		END
		SET @NextRunTime = @PlanTime
	END
	RETURN @NextRunTime
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetNumber]    Script Date: 2018/12/13 18:28:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetNumber]
(
	@Text NVARCHAR(100)
)
RETURNS INT
AS
BEGIN
	DECLARE @I INT, @chr NCHAR(1), @Id VARCHAR(10)
	SELECT @i = 0, @Id = ''
	WHILE @i < LEN(@Text)
	BEGIN
		SET @i = @i + 1
		SET @chr = SUBSTRING(@Text, @i, 1)
		IF @Chr IN('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
			SET @Id = @Id + @Chr
		ELSE
			IF LEN(@Id) > 0
				BREAK 
	END

	IF LEN(@Id) > 0
		SET @i = CONVERT(INT, @Id)
	ELSE
		SET @i = 0

	RETURN @i
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetOldManagerUserName]    Script Date: 2018/12/13 18:28:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[GetOldManagerUserName]
(
	@ManagerUserId INT,
	@Date DATETIME
)
RETURNS NVARCHAR(10)
AS
BEGIN
	DECLARE @N AS NVARCHAR(10)
	SELECT TOP 1 @n = a.Name
	FROM OaDb..WorkUser a, Oadb..Contract b
	WHERE a.ManagerUserId = @ManagerUserId
	AND a.Id = b.WorkUserId
	AND @Date BETWEEN b.BeginDate AND b.RealEndDate
	--ORDER BY ISNULL(AnnualDate, AddDate + 365) DESC

	IF ISNULL(@N, '') = ''
		SELECT @n = Name FROM MainDb..ManagerUser WHERE Id = @ManagerUSerId

	RETURN @n
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetPaName]    Script Date: 2018/12/13 18:28:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--#########################################################33
--Lambo 20110221
--Lucifer 20180425
--#########################################################33
CREATE FUNCTION [dbo].[GetPaName] 
(
	@cvMainID INT,
	@cpMainID INT
)
RETURNS NVARCHAR(20)
AS
BEGIN
	DECLARE @ReturnName NVARCHAR(20),
		@cpMemeberType INT,
		@Name NVARCHAR(20),
		@JobName NVARCHAR(50),
		@IsNameHiden BIT,
		@Gender BIT,
		@showType INT,
		@SecondId VARCHAR(10)

	SELECT TOP 1 @Name = b.Name,
		@Gender = b.Gender,
		@JobName = a.JobName,
		@IsNameHiden = a.IsNameHidden ,
		@SecondId = a.SecondId
	FROM cvMain a WITH(NOLOCK)
	INNER JOIN paMain b WITH(NOLOCK) ON a.paMainID = b.ID
	WHERE a.ID = @cvMainID

	SET @showType = dbo.GetChatPrivi(@cvMainID, @cpMainID)
	IF @showType = 0
	BEGIN
		SET @ReturnName = ISNULL(@JobName, @SecondId)
	END
	ELSE
	BEGIN
		IF EXISTS (SELECT 'x' FROM ExJobApply a WITH(NOLOCK),Job b 
			WHERE a.JobID = b.ID 
			AND a.cvMainID = @cvMainID
			AND b.cpMainID = @cpMainID
		)
			SET @ReturnName = @Name
		ELSE
		BEGIN
			IF @IsNameHiden = 1
			BEGIN
				IF @Gender = 1 
					SET @ReturnName = LEFT(@Name, 1) + '女士/小姐'
				ELSE
					SET @ReturnName = LEFT(@Name, 1) + '先生'
			END
			ELSE
				SET @ReturnName = @Name
		END
	END
	RETURN @ReturnName
END

GO

/****** Object:  UserDefinedFunction [dbo].[GetPaOnlineStatus]    Script Date: 2018/12/13 18:28:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetPaOnlineStatus]
(
	@paMainID INT
)
RETURNS BIT
AS
BEGIN
	DECLARE @IsOnline AS BIT
	SELECT @IsOnline = IsOnline FROM paOnline With(NOLOCK) WHERE paMainID = @paMainID

	RETURN ISNULL(@IsOnline, 0)
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetPaPhotoByCpView]    Script Date: 2018/12/13 18:28:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetPaPhotoByCpView]
(
	@cvMainID INT,
	@cpMainID INT
)
RETURNS VARCHAR(50)
AS
BEGIN
	DECLARE @PhotoPath VARCHAR(50)
	
	DECLARE @MemberType TINYINT
	
	SELECT @MemberType = MemberType 
	FROM cpMain WITH(NOLOCK)
	WHERE id = @cpMainID
	
	IF @MemberType < 2
		RETURN ''
	
	DECLARE @isInActiveLog INT, @isInDailyGiftLog INT, @isApplyCount INT, @paMainID INT

	SELECT @isInActiveLog = 0, @isInDailyGiftLog = 0, @isApplyCount = 0

	IF EXISTS(SELECT TOP 1 'x'
		FROM cpCvPrivi WITH(NOLOCK)
		WHERE cpMainID = @cpMainID
		AND cvMainID = @cvMainID)
		SET @isInDailyGiftLog = 1

--sean modify at 2018-3-6
--	IF @isInDailyGiftLog = 0
--		IF EXISTS(SELECT TOP 1 'x'
--			FROM caDailyGiftLog WITH(NOLOCK INDEX(PK_caDailyGiftLog))
--			WHERE cpMainID = @cpMainID
--			AND cvMainID = @cvMainID)
--			SET @isInDailyGiftLog = 1
--
--	IF @isInDailyGiftLog = 0
--	BEGIN
--		DECLARE @t AS TABLE(ID INT)
--		INSERT INTO @t
--		SELECT ID FROM cvMain WITH(NOLOCK) WHERE paMainId = @paMainID
--
--		IF EXISTS(SELECT TOP 1 'x'
--			FROM caActiveLog WITH(NOLOCK INDEX(IX_caActiveLog_cpMainID))
--			WHERE cpMainID = @cpMainID
--			AND cvMainID IN(SELECT ID FROM @t))
--			SET @isInActiveLog = 1
--		
--		IF @isInActiveLog = 0
--			IF EXISTS(SELECT TOP 1 'x'
--				FROM ExJobApply a WITH(NOLOCK), Job b 
--				WHERE a.JobID = b.ID 
--				AND a.IsPassed = 1 
--				AND a.cvMainID IN(SELECT ID FROM @t)
--				AND b.cpMainID = @cpMainID)
--				SET @isApplyCount = 1
--	END

	IF @isInActiveLog + @isInDailyGiftLog + @isApplyCount = 0 
		RETURN ''

	SELECT @paMainId = paMainId FROM cvMain WITH(NOLOCK) WHERE ID = @cvMainID

	SELECT @PhotoPath = PhotoProcessed 
	FROM paPhoto a WITH(NOLOCK INDEX(IX_paPhoto_paMainId)) 
	WHERE paMainID = @paMainId
	
	RETURN @PhotoPath
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetPersonName]    Script Date: 2018/12/13 18:28:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#########################################################33
--Lucifer 20131202
--Lucifer 20180425
--#########################################################33
CREATE FUNCTION [dbo].[GetPersonName] 
(
	@cvMainID INT,
	@cpMainID INT
)
RETURNS VARCHAR(50)
AS
BEGIN
	RETURN dbo.GetPaName(@cvMainID, @cpMainID)
	--DECLARE @ReturnName NVARCHAR(20)
	--DECLARE @cpMemeberType INT
	--DECLARE @Name NVARCHAR(20)
	--DECLARE @JobName NVARCHAR(20)
	--DECLARE @IsNameHiden BIT
	--DECLARE @Gender BIT
	--DECLARE @showType INT
	--DECLARE @showDownload INT

	--SELECT TOP 1 @Name = b.Name,
	--	@Gender = b.Gender,
	--	@JobName = a.JobName,
	--	@IsNameHiden = a.IsNameHidden 
	--FROM cvMain a WITH(NOLOCK)
	--INNER JOIN paMain b WITH(NOLOCK) ON a.paMainID = b.ID
	--WHERE a.ID = @cvMainID

	--SELECT @cpMemeberType = MemberType FROM cpMain WITH(NOLOCK) WHERE ID = @cpMainID

	--SET @showType = 1		--0姓名；1职位, 2不隐藏姓名
	--SET @showDownload = 0
	--IF EXISTS (
	--	SELECT 'x' FROM ExJobApply a WITH(NOLOCK),Job b 
	--	WHERE a.JobID = b.ID 
	--	AND a.cvMainID = @cvMainID
	--	AND b.cpMainID = @cpMainID
	--)	
	--	SET @showType = 2
	--ELSE IF @cpMemeberType IN(10, 11)
	--BEGIN
	--	IF EXISTS(
	--		SELECT 'x' FROM CaCvQuotaLog WITH(NOLOCK)
	--		WHERE cpMainID = @cpMainID
	--		AND cvMainID = @cvMainID
	--	)
	--		SET @showType = 0
	--	ELSE
	--	BEGIN
	--		IF @cpMemeberType = 11
	--			IF EXISTS(
	--				SELECT 'x' FROM caBalanceLog WITH(NOLOCK)
	--				WHERE cpMainID = @cpMainID
	--				AND cvMainID = @cvMainID
	--				AND CONVERT(VARCHAR(100), AddDate, 23) = CONVERT(VARCHAR(100), GETDATE(), 23)
	--			) 
	--				SET @showType = 0
	--	END
	--END
	--ELSE
	--BEGIN
	--	DECLARE @isInActiveLog INT
	--	DECLARE @isInDailyGiftLog INT
	--	----日赠送中有下载
	--	SELECT @isInDailyGiftLog = COUNT(1) FROM caDailyGiftLog WITH(NOLOCK)
	--	WHERE cpMainID = @cpMainID
	--	AND cvMainID = @cvMainID

	--	IF @isInDailyGiftLog > 0
	--	BEGIN
	--		SET @showType = 0
	--		SET @showDownload = 1
	--	END
	--	ELSE
	--	BEGIN
	--		----简历下载中有下载
	--		SELECT @isInActiveLog = COUNT(1) FROM caActiveLog WITH(NOLOCK)
	--		WHERE cpMainID = @cpMainID
	--		AND cvMainID = @cvMainID

	--		If @isInActiveLog>0
	--		BEGIN
	--			SET @showType = 0
	--			SET @showDownload = 1
	--		END
	--		ELSE
	--		BEGIN
	--			----申请职位是的新状态是pass
	--			IF EXISTS (
	--				SELECT 'x' FROM ExJobApply a WITH(NOLOCK),Job b WITH(NOLOCK)
	--				WHERE a.JobID = b.ID 
	--				AND a.IsPassed = 1 
	--				AND a.cvMainID = @cvMainID
	--				AND b.cpMainID = @cpMainID
	--			)
	--				SET @showType = 0
	--		END
	--	END
	--END

	--IF @showType = 0	--姓名
	--BEGIN
	--	SET @ReturnName = @Name
	--	IF @IsNameHiden = 1 
	--		IF @Gender = 1 
	--			SET @ReturnName = LEFT(@ReturnName, 1) + '女士/小姐'
	--		ELSE
	--			SET @ReturnName = LEFT(@ReturnName, 1) + '先生'
	--END
	--ELSE IF @showType = 2	--姓名
	--	SET @ReturnName = @Name
	--ELSE
	--	SET @ReturnName = ISNULL(@JobName, LTRIM(STR(@cvMainID)))

	--IF @showDownload > 0
	--	SET @ReturnName = '$$##' + @ReturnName

	--RETURN @ReturnName
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetPrivi]    Script Date: 2018/12/13 18:28:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetPrivi]
(
	@ManagerUserID AS SMALLINT,
	@DataSourceID INT,
	@Pos INT
)
RETURNS CHAR(1)
AS
BEGIN
	DECLARE @RoleID AS INT, @Privi AS VARCHAR(20)
	SELECT @RoleID = RoleID FROM ManagerUser WITH(NOLOCK) WHERE ID = @ManagerUserID

	SELECT @Privi = Privi
	FROM Role_DataSource WITH(NOLOCK)
	WHERE RoleID = @RoleID
	AND DataSourceID = @DataSourceID
	
	IF LEN(ISNULL(@Privi, '')) = 0
		RETURN 'x'

	RETURN SUBSTRING(@Privi, @Pos, 1)
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetPyFirst]    Script Date: 2018/12/13 18:28:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetPyFirst](
  @str NVARCHAR(100)
)
RETURNS  NVARCHAR(4000)  
AS
BEGIN  
	DECLARE @intLen INT
	DECLARE @strRet NVARCHAR(4000)  
	DECLARE @temp  NVARCHAR(100)  
	SET @intLen = LEN(@str)  
	SET @strRet = ''  
	WHILE @intLen > 0  
	BEGIN
		SET @temp = ''  
		SELECT @temp = CASE    
		WHEN SUBSTRING(@str,@intLen,1)  >=  '帀' THEN 'Z'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '丫' THEN 'Y'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '夕' THEN 'X'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '屲' THEN 'W'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '他' THEN 'T'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '仨' THEN 'S'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '呥' THEN 'R'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '七' THEN 'Q'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '妑' THEN 'P'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '噢' THEN 'O'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '拏' THEN 'N'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '嘸' THEN 'M'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '垃' THEN 'L'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '咔' THEN 'K'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '丌' THEN   'J'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '铪' THEN 'H'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '旮' THEN 'G'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '发' THEN 'F'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '妸' THEN 'E'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '咑' THEN 'D'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '嚓' THEN 'C'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '八' THEN 'B'  
		WHEN SUBSTRING(@str,@intLen,1)  >=  '吖' THEN 'A'  
		ELSE RTRIM(LTRIM(SUBSTRING(@str,@intLen,1)))  
		END 
		--对于汉字特殊字符，不生成拼音码  
		IF ASCII(@temp) > 127
			SET @temp = ''
		--对于英文中小括号，不生成拼音码  
		IF @temp = '(' OR @temp = ')'
			SET @temp = ''
		SELECT @strRet = @temp + @strRet
		SET @intLen = @intLen - 1
	END
	RETURN LOWER(@strRet)  
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetRegionAll]    Script Date: 2018/12/13 18:28:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[GetRegionAll]
(
	@ID VARCHAR(6)
)
RETURNS NVARCHAR(20)
AS 
BEGIN
	RETURN (SELECT FullName FROM dcREgion WITH(NOLOCK) WHERE ID = @ID)
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetTop5JobIDs]    Script Date: 2018/12/13 18:28:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].[GetTop5JobIDs]
(
	@JobIds AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @i INT, @Num INT, @StrSource VARCHAR(MAX), @s VARCHAR(50)
	SELECT @StrSource = RTRIM(LTRIM(@JobIds))
	SELECT @i = CHARINDEX(',', @StrSource), @Num = 0, @s = ''

	IF @i = 0
		RETURN @StrSource

	WHILE @i >= 1 AND @Num < 5
	BEGIN
		IF LEFT(@StrSource, @i - 1) > '' 
			SELECT @s = @s + ',' + LEFT(@StrSource, @i - 1), @Num = @Num + 1

		SELECT @StrSource = SUBSTRING(@StrSource, @i + 1, LEN(@StrSource) - @i)
		SELECT @i = CHARINDEX(',', @StrSource)
	END

	IF @Num < 5 AND @StrSource <> '' AND @i = 0
		SELECT @s = @s + ',' + @StrSource
	
	IF LEFT(@s, 1) = ','
		SELECT @s = RIGHT(@s, LEN(@s) - 1)
	RETURN @s
END

GO

/****** Object:  UserDefinedFunction [dbo].[HaveChinese]    Script Date: 2018/12/13 18:28:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Function [dbo].[HaveChinese]
(
	@Str varchar(4000)
)
RETURNS BIT
AS
BEGIN
	DECLARE @IsHave BIT
	IF @Str LIKE N'%[吖-咗]%' COLLATE Chinese_PRC_CI_AS
	BEGIN
		SET @IsHave = 1
	END
	ELSE
	BEGIN
		SET @IsHave = 0
	END
	RETURN @IsHave
END
GO

/****** Object:  UserDefinedFunction [dbo].[HtmlPos]    Script Date: 2018/12/13 18:28:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[HtmlPos]
(
	@s VARCHAR(MAX)
)
RETURNS INT
AS
BEGIN
	DECLARE @t AS TABLE(P INT)
	
	INSERT @t SELECT CHARINDEX('<p', @s)
	INSERT @t SELECT CHARINDEX('<strong', @s)
	INSERT @t SELECT CHARINDEX('<div', @s)
	INSERT @t SELECT CHARINDEX('<font', @s)
	INSERT @t SELECT CHARINDEX('<span', @s)
	INSERT @t SELECT CHARINDEX('<b', @s)
	INSERT @t SELECT CHARINDEX('</', @s)

	RETURN ISNULL((SELECT MIN(p) FROM @t WHERE p > 0), 0)
END
GO

/****** Object:  UserDefinedFunction [dbo].[Int2Bin]    Script Date: 2018/12/13 18:28:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].[Int2Bin](@i int)
returns varchar(31)
as
begin
    declare @s varchar(31);
    set @s=''
    while (@i>0)
        select @s=cast(@i%2 as varchar)+@s, @i=@i/2
 
    return(@s)
end
GO

/****** Object:  UserDefinedFunction [dbo].[IpPlace]    Script Date: 2018/12/13 18:28:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Function [dbo].[IpPlace]
(
	@Str varchar(31)
)
RETURNS NVARCHAR(100)
AS
BEGIN
	DECLARE @A1 VARCHAR(3), @A2 VARCHAR(3), @A3 VARCHAR(3), @A4 VARCHAR(3)
	DECLARE @b1 BIGINT, @b2 INT, @B3 INT, @B4 INT
	SELECT @A1 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
	SELECT @A2 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
	SELECT @A3 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
	SELECT @A4 = @str

	SELECT @B1 = CONVERT(INT, REPLACE(@A1, '.', '')),
		@B2 = CONVERT(INT, REPLACE(@A2, '.', '')),
		@B3 = CONVERT(INT, REPLACE(@A3, '.', '')),
		@B4 = CONVERT(INT, REPLACE(@A4, '.', ''))

	SELECT @B1 = @B1 * 256*256*256 + @B2 * 256*256 + @b3 * 256 + @b4

	RETURN (SELECT TOP 1 City + ' ' + Provider FROM dcIpPlace WITH(NOLOCK) WHERE @B1 BETWEEN IpStart AND IpEnd)
END

GO

/****** Object:  UserDefinedFunction [dbo].[IpPlaceToInt]    Script Date: 2018/12/13 18:28:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Function [dbo].[IpPlaceToInt]
(
	@Str varchar(31)
)
RETURNS BIGINT
AS
BEGIN
	IF @Str NOT LIKE '%.%.%.%'
		RETURN 0

	DECLARE @A1 VARCHAR(3), @A2 VARCHAR(3), @A3 VARCHAR(3), @A4 VARCHAR(3)
	DECLARE @b1 BIGINT, @b2 INT, @B3 INT, @B4 INT
	SELECT @A1 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
	SELECT @A2 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
	SELECT @A3 = SUBSTRING(@Str, 1, CHARINDEX('.', @Str)), @Str = SUBSTRING(@Str, CHARINDEX('.', @Str) + 1, LEN(@Str))
	SELECT @A4 = @str

	SELECT @B1 = CONVERT(INT, REPLACE(@A1, '.', '')),
		@B2 = CONVERT(INT, REPLACE(@A2, '.', '')),
		@B3 = CONVERT(INT, REPLACE(@A3, '.', '')),
		@B4 = CONVERT(INT, REPLACE(@A4, '.', ''))

	RETURN @B1 * 256*256*256 + @B2 * 256*256 + @b3 * 256 + @b4
END

GO

/****** Object:  UserDefinedFunction [dbo].[IsAccount]    Script Date: 2018/12/13 18:28:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--检查是否有效账号
--######################################################3
CREATE FUNCTION [dbo].[IsAccount]
(
	@str VARCHAR(50)
)
RETURNS BIT
AS
BEGIN
	DECLARE @ok AS BIT
	SET @ok = 1

	IF @str IS NULL
		SET @ok = 0
	
	IF @ok = 1
	BEGIN
		SET @str = LOWER(LTRIM(RTRIM(@str)))
		IF (LEN(@str) > 50) OR (LEN(@str) < 6)
			SET @ok = 0
	END
	IF @str LIKE '% %'
		RETURN 0

	IF @ok = 1
	BEGIN
		DECLARE @I AS INT
		DECLARE @ValidChars AS VARCHAR(39)
		SET @ValidChars = 'abcdefghijklmnopqrstuvwxyz0123456789-_.'

		SET @I = 1
		WHILE @I <= LEN(@str)
		BEGIN
			IF CHARINDEX(SUBSTRING(@str, @I, 1), @ValidChars) > 0
				SET @I = @I + 1
			ELSE
			BEGIN
				SET @ok = 0
				SET @I = 100
			END
		END
	END
	RETURN @ok
END



GO

/****** Object:  UserDefinedFunction [dbo].[IsChinese]    Script Date: 2018/12/13 18:28:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--检查是否汉字
--######################################################3
CREATE FUNCTION [dbo].[IsChinese]
(
	@str VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
	DECLARE @ok AS BIT
	SET @ok = 1

	IF ISNULL(@str, '') = ''
		SET @ok = 0

	IF @ok = 1
	BEGIN
		DECLARE @I AS INT

		SET @I = 1
		WHILE @I <= LEN(@str)
		BEGIN
			IF dbo.HaveChinese(SUBSTRING(@str, @I, 1)) = 1
				SET @I = @I + 1
			ELSE
			BEGIN
				SET @ok = 0
				SET @I = LEN(@str) + 1
			END
		END
	END

	RETURN @ok
END

GO

/****** Object:  UserDefinedFunction [dbo].[IsEmail]    Script Date: 2018/12/13 18:28:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--检查是否有效邮箱
--######################################################3
CREATE FUNCTION [dbo].[IsEmail]
(
	@str VARCHAR(100)
)
RETURNS BIT
AS
BEGIN
	DECLARE @ok AS BIT
	SET @ok = 1

	IF @str IS NULL
		SET @ok = 0
	
	IF @ok = 1
	BEGIN
		SET @str = LOWER(LTRIM(RTRIM(@str)))
		IF LEN(@str) > 50 OR LEN(@str) < 6
			SET @ok = 0
	END

	IF @Str LIKE '% %'
		RETURN 0

	IF @ok = 1
	BEGIN
		DECLARE @t AS TABLE(ID INT IDENTITY(1, 1), S VARCHAR(50))
		INSERT INTO @t (s)
		SELECT * FROM dbo.fnSplit(@str, '@')

		IF(SELECT COUNT(1) FROM @t) <> 2
			SET @ok = 0
		ELSE
		BEGIN
			DECLARE @str1 AS VARCHAR(50)
			SELECT @str1 = S FROM @t WHERE ID = 1
			IF dbo.IsEmailText(@str1, 1) = 0
				SET @ok = 0
			ELSE
			BEGIN
				SELECT @str1 = S FROM @t WHERE ID = 2
				IF dbo.IsEmailText(@str1, 2) = 0
					SET @ok = 0
			END
		END
	END
	RETURN @ok
END

GO

/****** Object:  UserDefinedFunction [dbo].[IsEmailText]    Script Date: 2018/12/13 18:28:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--检查是否有效邮箱文本,@前后的一部分
--######################################################3
CREATE FUNCTION [dbo].[IsEmailText]
(
	@str VARCHAR(50),
	@Pos INT		--1@前，2@后
)
RETURNS BIT
AS
BEGIN
	DECLARE @ok AS BIT
	SET @ok = 1

	IF @str IS NULL
		SET @ok = 0

	
	IF @ok = 1
	BEGIN
		SET @str = LOWER(LTRIM(RTRIM(@str)))
		IF (LEN(@str) > 45) OR (LEN(@str) < 1)
			SET @ok = 0
	END

	IF @ok = 1
	BEGIN
		IF @Pos = 1 AND (LEFT(@str, 1) IN ('.', '-', '_') OR RIGHT(@str, 1) IN ('.', '-', '_'))
			SET @ok = 0
		IF @Pos = 2 AND (LEFT(@str, 1) = ('.') OR RIGHT(@str, 1) IN ('.', '-', '_'))
			SET @ok = 0
		IF CHARINDEX('..', @str) > 0
			SET @ok = 0
	END

	IF @ok = 1
		IF @pos = 2
			IF CHARINDEX('.', @str) = 0
				SET @ok = 0
			ELSE
			BEGIN
				DECLARE @t AS TABLE(ID INT IDENTITY(1, 1), S VARCHAR(50))
				INSERT INTO @t(S) SELECT * FROM dbo.fnSplit(@str, '.')
				DECLARE @s AS VARCHAR(50)
				SELECT TOP 1 @s = S FROM @t ORDER BY ID DESC
				IF LEN(@s) < 2 OR LEN(@s) > 8
					SET @ok = 0
			END
	IF @ok = 1
	BEGIN
		DECLARE @I AS INT, @ValidChars AS VARCHAR(39)
		SET @ValidChars = 'abcdefghijklmnopqrstuvwxyz0123456789-_.'

		SET @I = 1
		WHILE @I <= LEN(@str)
		BEGIN
			IF CHARINDEX(SUBSTRING(@str, @I, 1) collate Chinese_PRC_CI_AS_KS_WS, @ValidChars) > 0
				SET @I = @I + 1
			ELSE
			BEGIN
				SET @ok = 0
				SET @I = 100
			END
		END
	END
	
	RETURN @ok
END
GO

/****** Object:  UserDefinedFunction [dbo].[IsEnglish]    Script Date: 2018/12/13 18:28:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--检查是否English
--######################################################3
CREATE FUNCTION [dbo].[IsEnglish]
(
	@str VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
	DECLARE @ok AS BIT
	SET @ok = 1

	IF @str IS NULL
		SET @ok = 0

	IF @ok = 1
	BEGIN
		DECLARE @I AS INT
		DECLARE @ValidChars AS VARCHAR(39)
		SET @ValidChars = 'abcdefghijklmnopqrstuvwxyz'

		SET @I = 1
		WHILE @I <= LEN(@str)
		BEGIN
			IF CHARINDEX(SUBSTRING(@str, @I, 1), @ValidChars) > 0
				SET @I = @I + 1
			ELSE
			BEGIN
				SET @ok = 0
				SET @I = LEN(@str) + 1
			END
		END
	END

	RETURN @ok
END
GO

/****** Object:  UserDefinedFunction [dbo].[IsFullWidth]    Script Date: 2018/12/13 18:28:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--检查是否全角
--######################################################3
CREATE FUNCTION [dbo].[IsFullWidth]
(
	@str VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
	DECLARE @ok AS BIT
	IF DATALENGTH(@str) = LEN(@str) * 2
		SET @ok = 1
	ELSE
		SET @ok = 0

	IF LEN(ISNULL(@str, '')) = 0
		SET @ok = 0

	RETURN @ok
END
GO

/****** Object:  UserDefinedFunction [dbo].[IsINT]    Script Date: 2018/12/13 18:28:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--检查是否INT
--######################################################3
CREATE FUNCTION [dbo].[IsINT]
(
	@str VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
	DECLARE @ok AS BIT
	SET @ok = 1

	IF @str IS NULL
		SET @ok = 0

	IF @ok = 1
	BEGIN
		DECLARE @I AS INT
		DECLARE @ValidChars AS VARCHAR(39)
		SET @ValidChars = '0123456789'

		SET @I = 1
		WHILE @I <= LEN(@str)
		BEGIN
			IF CHARINDEX(SUBSTRING(@str, @I, 1), @ValidChars) > 0
				SET @I = @I + 1
			ELSE
			BEGIN
				SET @ok = 0
				SET @I = LEN(@str) + 1
			END
		END
	END

	RETURN @ok
END



GO

/****** Object:  UserDefinedFunction [dbo].[IsJobApplyFilter]    Script Date: 2018/12/13 18:28:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[IsJobApplyFilter]
(
	@JobID INT,
	@cvMainID INT
)
RETURNS BIT
AS
BEGIN
	--获取职位的过滤条件
	DECLARE @FilterMaxDegree AS TINYINT
	DECLARE @FilterMinDegree AS TINYINT
	DECLARE @FilterMaxWorkYear AS TINYINT
	DECLARE @FilterMinWorkYear AS TINYINT
	DECLARE @FilterMaxAge AS TINYINT
	DECLARE @FilterMinAge AS TINYINT
	DECLARE @FilterGender AS VARCHAR(1)
	DECLARE @FilterJobRegion AS VARCHAR(6)
	DECLARE @FilterCvMainID AS VARCHAR(200)
	DECLARE @cpMainID AS INT
	SELECT 
		@FilterMaxDegree = FilterMaxDegree,
		@FilterMinDegree = FilterMinDegree,
		@FilterMaxWorkYear = FilterMaxWorkYear,
		@FilterMinWorkYear = FilterMinWorkYear,
		@FilterMaxAge = FilterMaxAge,
		@FilterMinAge = FilterMinAge,
		@FilterGender = FilterGender,
		@FilterJobRegion = FilterJobRegion,
		@FilterCvMainID = FilterCvMainID,
		@cpMainID = cpMainID
	FROM Job WITH(NOLOCK)
	WHERE ID = @JobID

	--获取简历的相关条件
	DECLARE	@Gender AS BIT
	DECLARE	@Age AS TINYINT
	DECLARE @LivePlace AS VARCHAR(6)
	DECLARE	@RelatedWorkYears AS TINYINT
	DECLARE @Degree AS TINYINT
	SELECT 
		@LivePlace = b.LivePlace,
		@Gender = b.Gender,
		@Age = dbo.BirthToAge(b.birthday),
		@RelatedWorkYears = a.RelatedWorkYears,
		@Degree = a.Degree
	FROM cvMain a,paMain b WHERE a.ID=@cvMainID AND a.paMainID=b.ID
	
	--判断性别
	IF LEN(@Gender) <> 0 AND LEN(@FilterGender) <> 0
	BEGIN
		IF @Gender <> @FilterGender
			RETURN 1
	END
	--判断年龄
	IF LEN(@Age) <> 0
	BEGIN
		IF LEN(@FilterMaxAge) <> 0 AND @FilterMaxAge <> 99
		BEGIN
			IF @Age > @FilterMaxAge
			BEGIN
				RETURN 1
			END
		END
		IF LEN(@FilterMinAge) <> 0 AND @FilterMinAge <> 99
		BEGIN
			IF @Age < @FilterMinAge
			BEGIN
				RETURN 1
			END
		END
	END
	--判断工作年限
	IF LEN(@RelatedWorkYears) <> 0
	BEGIN
		IF LEN(@FilterMaxWorkYear) <> 0 AND @FilterMaxWorkYear <> 0
			IF @RelatedWorkYears > @FilterMaxWorkYear
				RETURN 1

		IF LEN(@FilterMinWorkYear) <> 0 AND @FilterMinWorkYear <> 0
			IF @RelatedWorkYears < @FilterMinWorkYear
				RETURN 1
	END
	--判断学历
	IF LEN(@Degree) <> 0
	BEGIN
		IF LEN(@FilterMaxDegree) <> 0 AND @FilterMaxDegree <> 0
			IF @Degree > @FilterMaxDegree
				RETURN 1

		IF LEN(@FilterMinDegree) <> 0 AND @FilterMinDegree <> 0
			IF @Degree < @FilterMinDegree
				RETURN 1
	END
	--判断地区
	IF LEN(@LivePlace) <> 0 AND LEN(@FilterJobRegion) <> 0 AND @FilterJobRegion <> 0
	BEGIN
		IF LEN(@LivePlace) >= LEN(@FilterJobRegion)
		BEGIN
			IF SUBSTRING(@LivePlace,1,LEN(@FilterJobRegion)) <> @FilterJobRegion
				RETURN 1
		END
		ELSE
		BEGIN
			IF SUBSTRING(@FilterJobRegion,1,LEN(@LivePlace)) <> @LivePlace
				RETURN 1
		END
	END
	--判断是否在过滤简历表中
	IF EXISTS(SELECT 'X' FROM cpCvShield WITH(NOLOCK) WHERE cpMainID = @cpMainID and cvMainID=@cvMainID)
		RETURN 1

	RETURN 0
END
GO

/****** Object:  UserDefinedFunction [dbo].[ISJobApplyPassed]    Script Date: 2018/12/13 18:28:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################3
--创建：Sean 2010-11-16
--判断是否Vip会员
--######################################3
CREATE FUNCTION [dbo].[ISJobApplyPassed]
(
	@JobID INT,
	@cvMainID INT
)
RETURNS BIT
AS
BEGIN
	DECLARE @cpMainID AS INT
	DECLARE @IsOpen AS BIT
	DECLARE @MemberType AS SMALLDATETIME
	DECLARE @Valid AS INT

	SELECT @IsOpen = IsOpen FROM cvMain WITH(NOLOCK) WHERE ID = @cvMainID
	SELECT @cpMainID = a.ID,
		@MemberType = a.MemberType,
		@Valid = a.Valid
	FROM cpMain a WITH(NOLOCK)
	INNER JOIN Job b WITH(NOLOCK)
	ON a.ID = b.cpMainID
	WHERE b.ID = @JobID

	IF @Valid = 0				------企业信息无效，返回0
		RETURN 0

	IF @MemberType >= 10		------储值会员或无限制会员，不享受套餐vip
		RETURN 0

	IF (@IsOpen = 1 AND @MemberType = 2) OR @MemberType = 3	------开放简历，只要申请，企业就可以查看
		RETURN 1
	RETURN 0
END



GO

/****** Object:  UserDefinedFunction [dbo].[IsMobile]    Script Date: 2018/12/13 18:28:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--######################################################3
--检查是否mobile
--######################################################3
CREATE FUNCTION [dbo].[IsMobile]
(
	@str VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
	DECLARE @ok AS BIT
	SET @ok = 1

	IF @str IS NULL
		RETURN 0

	IF LEN(@str) <> 11
		RETURN 0

	IF LEFT(@str, 2) NOT IN ('13', '14', '15', '16', '17', '18', '19')
		RETURN 0

	--IF CHARINDEX('@', @str) > 0
	--	RETURN 0

	DECLARE @I AS INT
	DECLARE @ValidChars AS VARCHAR(39)
	SET @ValidChars = '0123456789'

	SET @I = 1
	WHILE @I <= LEN(@str)
	BEGIN
		IF CHARINDEX(SUBSTRING(@str, @I, 1), @ValidChars) > 0
			SET @I = @I + 1
		ELSE
			RETURN 0
	END

	RETURN @ok
END
GO

/****** Object:  UserDefinedFunction [dbo].[IsNumeric]    Script Date: 2018/12/13 18:28:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--检查是否Numeric
--######################################################3
CREATE FUNCTION [dbo].[IsNumeric]
(
	@str VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
	DECLARE @ok AS BIT
	SET @ok = 1

	IF @str IS NULL
		SET @ok = 0

	DECLARE @s AS VARCHAR(MAX)
	SET @s = REPLACE(@str, '.', '')
	IF LEN(@s) < LEN(@str) - 1
		SET @ok = 0

	IF @ok = 1
	BEGIN
		DECLARE @I AS INT
		DECLARE @ValidChars AS VARCHAR(39)
		SET @ValidChars = '0123456789'

		SET @I = 1
		WHILE @I <= LEN(@s)
		BEGIN
			IF CHARINDEX(SUBSTRING(@s, @I, 1), @ValidChars) > 0
				SET @I = @I + 1
			ELSE
			BEGIN
				SET @ok = 0
				SET @I = LEN(@s) + 1
			END
		END
	END

	RETURN @ok
END
GO

/****** Object:  UserDefinedFunction [dbo].[IsPassword]    Script Date: 2018/12/13 18:28:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
----检查是否有效密码 = 检查是否有效账号(tmp)
--######################################################3
CREATE FUNCTION [dbo].[IsPassword]
(
	@str VARCHAR(60)
)
RETURNS BIT
AS
BEGIN
	IF LEN(ISNULL(@Str, '')) <> 60
		RETURN 0
	RETURN 1
/*
	DECLARE @ok AS BIT
	SET @ok = 1

	IF @str IS NULL
		SET @ok = 0
	
	IF @ok = 1
	BEGIN
		SET @str = LOWER(LTRIM(RTRIM(@str)))
		IF (LEN(@str) > 50) OR (LEN(@str) < 6)
			SET @ok = 0
	END

	IF @ok = 1
	BEGIN
		DECLARE @I AS INT
		DECLARE @ValidChars AS VARCHAR(39)
		SET @ValidChars = 'abcdefghijklmnopqrstuvwxyz0123456789-_.'

		SET @I = 1
		WHILE @I <= LEN(@str)
		BEGIN
			IF CHARINDEX(SUBSTRING(@str, @I, 1), @ValidChars) > 0
				SET @I = @I + 1
			ELSE
			BEGIN
				SET @ok = 0
				SET @I = 100
			END
		END
	END
	RETURN @ok
*/
END



GO

/****** Object:  UserDefinedFunction [dbo].[IsSafeSQL]    Script Date: 2018/12/13 18:28:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#############################333
--安全的sql语句用于exec
--Sean 2013-8-23
--#############################333
CREATE FUNCTION [dbo].[IsSafeSQL]
(
	@inputText VARCHAR(MAX)
)
RETURNS INT
AS
BEGIN
	IF @inputText IS NULL
		RETURN 1

	DECLARE @sText VARCHAR(Max)
	SET @SText = @inputText
	SET @sText = REPLACE(@sText, '--','——')
	SET @sText = REPLACE(@sText, 'delete ','delete&nbsp;')
	SET @sText = REPLACE(@sText, 'update ','update&nbsp;')
	SET @sText = REPLACE(@sText, 'create ','create&nbsp;')
	SET @sText = REPLACE(@sText, 'drop ','drop&nbsp;')
	SET @sText = REPLACE(@sText, 'insert ','insert&nbsp;')
	SET @sText = REPLACE(@sText, 'chr(','chr(&nbsp;')
	SET @sText = REPLACE(@sText, 'master ','master&nbsp;')
	SET @sText = REPLACE(@sText, 'net user','net&nbsp;user')
	SET @sText = REPLACE(@sText, 'declare ','declare&nbsp;')
	SET @sText = REPLACE(@sText, 'char(','char(&nbsp;')
	SET @sText = REPLACE(@sText, 'mid(','mid(&nbsp;')
	SET @sText = REPLACE(@sText, 'count ','count&nbsp;')
	SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell&nbsp;')
	SET @sText = REPLACE(@sText, 'exec ','exec&nbsp;')
	SET @sText = REPLACE(@sText, 'truncate ','truncate&nbsp;')
	SET @sText = REPLACE(@sText, 'ascii(','ascii(&nbsp;')
	SET @sText = REPLACE(@sText, 'substring(','substring(&nbsp;')
	SET @sText = REPLACE(@sText, 'localgroup ','localgroup&nbsp;')
	SET @sText = REPLACE(@sText, 'administrators ','administrators&nbsp;')
	SET @sText = REPLACE(@sText, 'dbcc ','dbcc&nbsp;')
	SET @sText = REPLACE(@sText, 'checkdb(','checkdb(&nbsp;')
	SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember&nbsp;')
	SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell&nbsp;')

	IF @SText = @inputText
		RETURN 1
	ELSE
		RETURN 0

	RETURN 0
END





GO

/****** Object:  UserDefinedFunction [dbo].[JobApplyName]    Script Date: 2018/12/13 18:28:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[JobApplyName]
(	
	@IsNameHidden BIT,
	@paName VARCHAR(12),
	@Gender BIT
)
RETURNS VARCHAR(50)
AS
BEGIN
	IF @IsNameHidden = 0
		RETURN @paName

	SET @paName = LEFT(@paName, 1)
	IF @Gender = 0
		RETURN @paName + '先生'

	RETURN @paName + '女士/小姐'
END

GO

/****** Object:  UserDefinedFunction [dbo].[JobValid]    Script Date: 2018/12/13 18:28:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[JobValid]
(	
	@IsDelete BIT,
	@VerifyResult TINYINT,
	@IssueDate SMALLDATETIME,
	@IssueEnd SMALLDATETIME
)
RETURNS BIT
AS
BEGIN
	IF @IsDelete = 1
		RETURN 0
	
	IF @VerifyResult<>1
		RETURN 0

	IF @IssueDate<GETDATE() AND @IssueEnd>GETDATE()
		RETURN 1

	RETURN 0
END

GO

/****** Object:  UserDefinedFunction [dbo].[MapBarRegionName]    Script Date: 2018/12/13 18:28:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE FUNCTION [dbo].[MapBarRegionName]
(
	@Id VARCHAR(6)
)
RETURNS NVARCHAR(20)
BEGIN
	RETURN (SELECT MapBarName FROM dcREgion WITH(NOLOCK) WHERE ID = @ID)
END
GO

/****** Object:  UserDefinedFunction [dbo].[MemberType]    Script Date: 2018/12/13 18:28:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################3
--创建：Sean 2010-11-16
--判断企业用户类型
--######################################3
CREATE FUNCTION [dbo].[MemberType]
(
	@ID INT
)
RETURNS TINYINT
AS
BEGIN
	DECLARE @HasLicence AS BIT, @BalanceDate AS SMALLDATETIME, @UnlimitedDate AS SMALLDATETIME,
		@Address AS VARCHAR(100), @Name AS NVARCHAR(6), @Gender AS BIT,
		@MemberType AS TINYINT, @IsDelete AS BIT, @Mobile VARCHAR(100)

	SELECT @HasLicence = ISNULL(HasLicence, 0),
		@BalanceDate = ISNULL(BalanceDate, '1900-1-1'),
		@UnlimitedDate = ISNULL(UnlimitedDate, '1900-1-1'),
		@Address = ISNULL(Address, ''),
		@IsDelete = IsDelete
	FROM cpMain WITH(NOLOCK)
	WHERE ID = @ID
 
	SELECT @Name = Name, @Gender = Gender, @Mobile = ISNULL(Mobile, ISNULL(TelePhone, '')) FROM caMain WITH(NOLOCK)
	WHERE cpMainID = @ID AND AccountType = 1
  
	----企业资料不完整,=0
	IF ISNULL(@Address, '') = '' OR @IsDelete = 1 OR ISNULL(@Name, '') = '' OR @Gender IS NULL OR @Mobile = ''
		SET @MemberType = 0

	----已经上传营业执照,只能是普通会员：2、无限制：10、储值：11、vip：3之一
	ELSE IF @HasLicence = 0
		SET @MemberType = 1

	--无限制
	--ELSE IF @UnlimitedDate >= CONVERT(SMALLDATETIME, GETDATE())
	--	SET @MemberType = 10

	--储值
	--ELSE IF @BalanceDate >= CONVERT(SMALLDATETIME, GETDATE())
	--	SET @MemberType = 11

	----检查订单
	ELSE IF EXISTS(SELECT 'x' FROM caOrder WITH(NOLOCK)
			WHERE cpMainID = @ID AND OrderType = 8
			AND BeginDate <= GETDATE() AND ENDDATE > DATEADD(MINUTE, -4, GETDATE())
			AND IsDeleted = 0
			)
		SET @MemberType = 3
	ELSE
		SET @MemberType = 2

	RETURN @MemberType
END













GO

/****** Object:  UserDefinedFunction [dbo].[MemberType1]    Script Date: 2018/12/13 18:28:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--######################################3
--创建：Sean 2010-11-16
--判断企业用户类型
--######################################3
CREATE FUNCTION [dbo].[MemberType1]
(
	@ID INT
)
RETURNS TINYINT
AS
BEGIN
	DECLARE @HasLicence AS BIT
	DECLARE @BalanceDate AS SMALLDATETIME
	DECLARE @Balance AS INT
	DECLARE @UnlimitedDate AS SMALLDATETIME
	DECLARE @Address AS VARCHAR(100)
	DECLARE @Name AS NVARCHAR(6)
	DECLARE @MemberType AS TINYINT
	DECLARE @IsDelete AS BIT
	SELECT @HasLicence = ISNULL(HasLicence, 0),
		@BalanceDate = ISNULL(BalanceDate, '1900-1-1'),
		@Balance = ISNULL(Balance, 0),
		@UnlimitedDate = ISNULL(UnlimitedDate, '1900-1-1'),
		@Address = ISNULL(Address, ''),
		@IsDelete = IsDelete
	FROM cpMain WITH(NOLOCK)
	WHERE ID = @ID
	
	SELECT @Name = Name FROM caMain WITH(NOLOCK)
	WHERE cpMainID = @ID AND AccountType = 1

	----企业资料不完整，=0
	IF ISNULL(@Address, '') = '' OR @IsDelete = 1
	BEGIN
		SET @MemberType = 0
		GOTO ret
	END
	IF @HasLicence = 0
	BEGIN
		SET @MemberType = 1
		GOTO ret
	END
	----已经上传营业执照，只能是普通会员：2、无限制：10、储值：11、vip：3之一
	IF @HasLicence = 1
	BEGIN
		--无限制
		IF @UnlimitedDate > GETDATE()
		BEGIN
			SET @MemberType = 10
			GOTO ret
		END
		--储值
		IF @BalanceDate > GETDATE() AND @Balance > 0
		BEGIN
			SET @MemberType = 11
			GOTO ret
		END
		----检查订单
		IF EXISTS(SELECT 'x' FROM caOrder WITH(NOLOCK)
				WHERE cpMainID = @ID AND OrderType = 8
				AND BEGINDATE<GETDATE() AND ENDDATE > GETDATE())

			SET @MemberType = 3
		ELSE
			SET @MemberType = 2
			GOTO ret
	END
ret:
	RETURN @MemberType
END
GO

/****** Object:  UserDefinedFunction [dbo].[MergeDate]    Script Date: 2018/12/13 18:28:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[MergeDate]
(
	@D SMALLDATETIME,
	@t INT
)
RETURNS SMALLDATETIME
AS
BEGIN
	DECLARE @c AS VARCHAR(30)
	SET @c = CONVERT(VARCHAR(10), @d, 120) + ' '
	IF @t < 10
		SET @c = @c + '00:0' + LTRIM(STR(@t))
	ELSE IF @t < 100
		SET @c = @c + '00:' + LTRIM(STR(@t))
	ELSE IF @t < 1000
		SET @c = @c + '0' + LEFT(@t, 1) + ':' + RIGHT(@t, 2)
	ELSE
		SET @c = @c + LEFT(@t, 2) + ':' + RIGHT(@t, 2)

	RETURN CONVERT(SMALLDATETIME, @c)
END
GO

/****** Object:  UserDefinedFunction [dbo].[MtNo2RtxId]    Script Date: 2018/12/13 18:28:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[MtNo2RtxId]
(
	@MtNo TINYINT
)
RETURNS INT
AS
BEGIN
	IF @MtNo = 1
		RETURN 1003
	ELSE IF @MtNo = 2
		RETURN 1002
	ELSE IF @MtNo = 3
		RETURN 1004
	ELSE IF @MtNo = 8
		RETURN 1005
	ELSE IF @MtNo = 9
		RETURN 1006

	RETURN 0
END


GO

/****** Object:  UserDefinedFunction [dbo].[photoPath]    Script Date: 2018/12/13 18:28:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[photoPath](@id INT,@fileType INT)
RETURNS varchar(50)
AS
BEGIN 
    DECLARE @result VARCHAR(50)
    DECLARE @path VARCHAR(50),@i INT,@photo VARCHAR(20)
    SET @i = 1
    IF @filetype <=3
    BEGIN
        SET @path = CONVERT(VARCHAR(50),((@id / 100000) + 1) * 100000)
        SET @photo = @path
        WHILE @i<=(9-LEN(@photo))
        BEGIN
            SET @path = '0' + @path
            SET @i = @i + 1
        END
    END
    ELSE
    BEGIN
        SET @path = CONVERT(VARCHAR(50),((@id / 10000) + 1) * 10000)
        SET @photo = @path
        WHILE @i<=(6-LEN(@photo))
        BEGIN
            SET @path = '0' + @path
            SET @i = @i + 1
        END
    END
    SET @path = 'L' + @path
SET @result = @path
RETURN @result
END
GO

/****** Object:  UserDefinedFunction [dbo].[RemoveBR]    Script Date: 2018/12/13 18:28:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[RemoveBR]
(
	@Str VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	RETURN REPLACE(REPLACE(@str, '<br>', ''), '&nbsp;', ' ')
END
GO

/****** Object:  UserDefinedFunction [dbo].[RemoveNullChars]    Script Date: 2018/12/13 18:28:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[RemoveNullChars] 
( 
	@string NVARCHAR(MAX) 
) 
RETURNS NVARCHAR(MAX) 
WITH RETURNS NULL ON NULL INPUT 
AS 
BEGIN 
	DECLARE @Result NVARCHAR(MAX) 
	SET @Result = ''
	DECLARE @counter INT 
	SET @counter = 0 
	WHILE (@counter <= LEN(@string)) 
	BEGIN 
		IF UNICODE(SUBSTRING(@string, @counter,1)) <> 0 
			SET @Result = @Result + SUBSTRING(@string, @counter, 1) 
		SET @counter = @counter + 1 
	END 
	RETURN @Result 
END
GO

/****** Object:  UserDefinedFunction [dbo].[ReplyInterval]    Script Date: 2018/12/13 18:28:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  function   [dbo].[ReplyInterval](@adddate smalldatetime,@replydate smalldatetime)   
  returns   int   
  as   
  begin   
  declare   @result   int   
    if @replydate is null set @replydate=getdate()
    if datepart(w,@adddate)=1
       set @result=datediff(n,cast(cast(year(@adddate+1) as varchar)+'-'+cast(month(@adddate+1) as varchar)+'-'+cast(day(@adddate+1) as varchar)+' 8:30' as smalldatetime),@replydate) 
    else if datepart(w,@adddate)=7 
            and @adddate>cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 17:30' as smalldatetime) 
            and @adddate<=cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 23:59' as smalldatetime)
        set @result=datediff(n,cast(cast(year(@adddate+2) as varchar)+'-'+cast(month(@adddate+2) as varchar)+'-'+cast(day(@adddate+2) as varchar)+' 8:30' as smalldatetime),@replydate) 
    else if @adddate>=cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 0:0' as smalldatetime) 
       and @adddate<cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 8:30' as smalldatetime)
       --没上班之前的
       set @result=datediff(n,cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 8:30' as smalldatetime),@replydate)
    else if @adddate>cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 17:30' as smalldatetime) 
            and @adddate<=cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 23:59' as smalldatetime) --没上班之前的
       set @result=datediff(n,cast(cast(year(@adddate+1) as varchar)+'-'+cast(month(@adddate+1) as varchar)+'-'+cast(day(@adddate+1) as varchar)+' 8:30' as smalldatetime),@replydate)
    else if @adddate>cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 10:00' as smalldatetime) 
            and @adddate<=cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 12:00' as smalldatetime) --10:00-12:00
       set @result=datediff(n,@adddate,@replydate)-60
    else if @adddate>cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 12:00' as smalldatetime) 
            and @adddate<=cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 13:00' as smalldatetime) --12:00-13:00
       set @result=datediff(n,cast(cast(year(@adddate) as varchar)+'-'+cast(month(@adddate) as varchar)+'-'+cast(day(@adddate) as varchar)+' 13:00' as smalldatetime),@replydate)
    else
       set @result=datediff(n,@adddate,@replydate)
    return   @result   
  end
GO

/****** Object:  UserDefinedFunction [dbo].[ReportDayToDate]    Script Date: 2018/12/13 18:28:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--######################################3
--创建：Lambo 2013-2-4
--日期转换 取多少天之前的天数  2012-1-1为1
--######################################3
CREATE FUNCTION [dbo].[ReportDayToDate]
(
	@d int
)
RETURNS INT
AS
BEGIN	
	RETURN DATEDIFF(DAY,'2012-1-1',GETDATE()-@d)
END
GO

/****** Object:  UserDefinedFunction [dbo].[SafeSQL]    Script Date: 2018/12/13 18:28:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#############################333
--安全的sql语句用于exec
--#############################333
CREATE FUNCTION [dbo].[SafeSQL]
(
	@sText VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	IF @sText IS NULL
		RETURN ''

--	SET @sText = REPLACE(@sText, 'and ','and&nbsp;')
--	SET @sText = REPLACE(@sText, 'or ','or&nbsp;')
--	SET @sText = REPLACE(@sText, 'exists','exists&nbsp;')	
--	SET @sText = REPLACE(@sText, 'union ','union&nbsp;')
--	SET @sText = REPLACE(@sText, '<','＜')
--	SET @sText = REPLACE(@sText, '>','＞')
--	SET @sText = REPLACE(@sText, 'from ','from&nbsp;')
--	SET @sText = REPLACE(@sText, 'select ','select&nbsp;')
	SET @sText = REPLACE(@sText, '','')
	SET @sText = REPLACE(@sText, 'script','scripｔ')
	SET @sText = REPLACE(@sText, 'src','srｃ')
	SET @sText = REPLACE(@sText, 'iframe','ｉframe')
	SET @sText = REPLACE(@sText, '--','——')
	SET @sText = REPLACE(@sText, 'delete ','delete&nbsp;')
	SET @sText = REPLACE(@sText, 'update ','update&nbsp;')
	SET @sText = REPLACE(@sText, 'delete	','delete&nbsp;')
	SET @sText = REPLACE(@sText, 'update	','update&nbsp;')
	SET @sText = REPLACE(@sText, 'delete
','delete&nbsp;')
	SET @sText = REPLACE(@sText, 'update
','update&nbsp;')
	SET @sText = REPLACE(@sText, 'create','create&nbsp;')
	SET @sText = REPLACE(@sText, 'drop','drop&nbsp;')
	SET @sText = REPLACE(@sText, 'insert','insert&nbsp;')
	SET @sText = REPLACE(@sText, 'chr(','chr(&nbsp;')
	SET @sText = REPLACE(@sText, 'master','master&nbsp;')
	SET @sText = REPLACE(@sText, 'net user','net&nbsp;user')
	SET @sText = REPLACE(@sText, 'declare','declare&nbsp;')
	SET @sText = REPLACE(@sText, 'char(','char(&nbsp;')
	SET @sText = REPLACE(@sText, 'mid(','mid(&nbsp;')
	SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell&nbsp;')
	SET @sText = REPLACE(@sText, 'exec','exec&nbsp;')
	SET @sText = REPLACE(@sText, 'truncate','truncate&nbsp;')
	SET @sText = REPLACE(@sText, 'ascii(','ascii(&nbsp;')
	SET @sText = REPLACE(@sText, 'substring(','substring(&nbsp;')
	SET @sText = REPLACE(@sText, 'localgroup ','localgroup&nbsp;')
	SET @sText = REPLACE(@sText, 'administrators ','administrators&nbsp;')
	SET @sText = REPLACE(@sText, 'dbcc','dbcc&nbsp;')
	SET @sText = REPLACE(@sText, 'checkdb(','checkdb(&nbsp;')
	SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember&nbsp;')
	SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell&nbsp;')

	RETURN @sText
END
GO

/****** Object:  UserDefinedFunction [dbo].[SafeSQLBack]    Script Date: 2018/12/13 18:28:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#############################333
--安全的sql语句用于exec
--#############################333
CREATE FUNCTION [dbo].[SafeSQLBack]
(
	@sText VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	IF @sText IS NULL
		RETURN ''

--	SET @sText = REPLACE(@sText, 'and ','and&nbsp;')
--	SET @sText = REPLACE(@sText, 'or ','or&nbsp;')
--	SET @sText = REPLACE(@sText, 'from ','from&nbsp;')
--	SET @sText = REPLACE(@sText, 'exists','exists&nbsp;')	
--	SET @sText = REPLACE(@sText, 'select ','select&nbsp;')
--	SET @sText = REPLACE(@sText, 'union ','union&nbsp;')
--	SET @sText = REPLACE(@sText, '<','＜')
--	SET @sText = REPLACE(@sText, '>','＞')
	SET @sText = REPLACE(@sText, '','')
	SET @sText = REPLACE(@sText, 'script','scripｔ')
	SET @sText = REPLACE(@sText, 'src','srｃ')
	SET @sText = REPLACE(@sText, 'iframe','ｉframe')
	SET @sText = REPLACE(@sText, '--','——')
	SET @sText = REPLACE(@sText, 'delete ','delete&nbsp;')
	SET @sText = REPLACE(@sText, 'update ','update&nbsp;')
	SET @sText = REPLACE(@sText, 'delete	','delete&nbsp;')
	SET @sText = REPLACE(@sText, 'update	','update&nbsp;')
	SET @sText = REPLACE(@sText, 'delete
','delete&nbsp;')
	SET @sText = REPLACE(@sText, 'update
','update&nbsp;')
	SET @sText = REPLACE(@sText, 'create ','create&nbsp;')
	SET @sText = REPLACE(@sText, 'drop ','drop&nbsp;')
	SET @sText = REPLACE(@sText, 'insert ','insert&nbsp;')
	SET @sText = REPLACE(@sText, 'chr(','chr(&nbsp;')
	SET @sText = REPLACE(@sText, 'master ','master&nbsp;')
	SET @sText = REPLACE(@sText, 'net user','net&nbsp;user')
	SET @sText = REPLACE(@sText, 'declare ','declare&nbsp;')
	SET @sText = REPLACE(@sText, 'char(','char(&nbsp;')
	SET @sText = REPLACE(@sText, 'mid(','mid(&nbsp;')
	--SET @sText = REPLACE(@sText, 'count ','count&nbsp;')
	SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell&nbsp;')
	SET @sText = REPLACE(@sText, 'exec ','exec&nbsp;')
	SET @sText = REPLACE(@sText, 'truncate ','truncate&nbsp;')
	SET @sText = REPLACE(@sText, 'ascii(','ascii(&nbsp;')
	SET @sText = REPLACE(@sText, 'substring(','substring(&nbsp;')
	SET @sText = REPLACE(@sText, 'localgroup ','localgroup&nbsp;')
	SET @sText = REPLACE(@sText, 'administrators ','administrators&nbsp;')
	SET @sText = REPLACE(@sText, 'dbcc ','dbcc&nbsp;')
	SET @sText = REPLACE(@sText, 'checkdb(','checkdb(&nbsp;')
	SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember&nbsp;')
	SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell&nbsp;')

	RETURN @sText
END
GO

/****** Object:  UserDefinedFunction [dbo].[ScheduleRunTime]    Script Date: 2018/12/13 18:28:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Function [dbo].[ScheduleRunTime]
(
	@RunDate INT,
	@RunTime INT
)
RETURNS DATETIME
AS
BEGIN
	DECLARE @time AS VARCHAR(30)
	SET @Time = LTRIM(STR(@RunTime / 10000)) + ':' 
	SET @RunTime = @RunTime % 10000
	SET @Time = @Time + LTRIM(STR(@RunTime / 100)) + ':00'
	RETURN CONVERT(DATETIME, LTRIM(STR(@RunDate)) + ' ' + @Time)
END
GO

/****** Object:  UserDefinedFunction [dbo].[schGetCaCvExChange]    Script Date: 2018/12/13 18:28:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[schGetCaCvExChange]
(
	@caMainID INT,
	@DateType TINYINT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @JobID INT,@JobName NVARCHAR(50),@Count INT,@StartTime VARCHAR(20),
		@EndTime VARCHAR(20),@Today VARCHAR(10),@Content VARCHAR(MAX)

	SET @Content = ''
	IF @DateType = 1
	BEGIN
		IF DATEPART(HOUR,GETDATE()) > 0 AND DATEPART(HOUR,GETDATE()) < 9
		BEGIN
			SET @StartTime = CONVERT(VARCHAR(10),DATEADD(DAY,-1,GETDATE()),120) + ' 00:00:00'
			SET @EndTime = CONVERT(VARCHAR(10),DATEADD(DAY,-1,GETDATE()),120) + ' 23:59:59'
			SET @Today = CONVERT(VARCHAR(10),DATEADD(DAY,-1,GETDATE()),120)
		END
		ELSE
		BEGIN
			SET @StartTime = CONVERT(VARCHAR(10),GETDATE(),120) + ' 00:00:00'
			SET @EndTime = CONVERT(VARCHAR(10),GETDATE(),120) + ' 23:59:59'
			SET @Today = CONVERT(VARCHAR(10),GETDATE(),120)
		END
	END
	ELSE
	BEGIN
		SET @StartTime = CONVERT(VARCHAR(10),DATEADD(ww,-1,GETDATE()),120) + ' 00:00:00'
		SET @EndTime = CONVERT(VARCHAR(10),GETDATE(),120) + ' 23:59:59'
		SET @Today = CONVERT(VARCHAR(10),DATEADD(ww,-1,GETDATE()),120)
	END

	SELECT @Count=COUNT(1) FROM exMessageP2C WITH(NOLOCK) WHERE caMainID=@caMainID AND AddDate>CONVERT(SMALLDATETIME,@StartTime) And AddDate<CONVERT(SMALLDATETIME,@EndTime)
	IF @Count > 0
		SET @Content = @Content + '<br>&nbsp;&nbsp;您总共收到了' + CONVERT(VARCHAR,@Count) + '条求职者发来的新留言；'
	
	DECLARE curJob CURSOR FOR SELECT TOP 10 ID,Name FROM Job WITH(NOLOCK) WHERE Valid=1 AND caMainID=@caMainID
	OPEN curJob
	FETCH NEXT FROM curJob INTO @JobID,@JobName
	IF @@FETCH_STATUS >= 0
	BEGIN
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT @Count=COUNT(1) FROM exJobApply a WITH(NOLOCK),cvMain b WITH(NOLOCK)
				WHERE a.cvMainID=b.ID AND b.VerifyResult=1 AND a.JobID=@JobID
				AND a.AddDate<CONVERT(SMALLDATETIME,@EndTime) AND a.AddDate>CONVERT(SMALLDATETIME,@StartTime)
			IF @Count > 0
				SET @Content = @Content + '<br>&nbsp;&nbsp;有' + CONVERT(VARCHAR,@Count) + '个求职者给[' + @JobName + ']投递了简历；'

			FETCH NEXT FROM curJob INTO @JobID,@JobName
		END
	END
	CLOSE curJob 
	DEALLOCATE curJob
	IF LEN(@Content) > 0
	BEGIN
		IF @DateType = 1
			SET @Content = '在' + @Today + '的24小时内：' + @Content
		ELSE
			SET @Content = '在' + CONVERT(VARCHAR(10),@StartTime) + '至' + CONVERT(VARCHAR(10),@EndTime) + '的一周内：' + @Content
	END
	RETURN @Content
END
GO

/****** Object:  UserDefinedFunction [dbo].[schGetOrderDesc]    Script Date: 2018/12/13 18:28:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[schGetOrderDesc]
(
	@ServiceType SMALLINT,
	@OrderType SMALLINT,
	@Paid NUMERIC(18,2),
	@TimeTitle VARCHAR(500),
	@ActiveQuota INT,
	@JobNumber INT,
	@UserNumber INT,
	@PayMethod SMALLINT,
	@Receiver VARCHAR(200),
	@Account VARCHAR(200),
	@BankName VARCHAR(200),
	@NeedInvoice BIT,
	@OrderID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @tmp@ServiceType NVARCHAR(20)
	DECLARE @strOrderDesc NVARCHAR(500)
	SET @strOrderDesc = ''
	SET @tmp@ServiceType = ''
	If @ServiceType = 100 
		SET @tmp@ServiceType = '一周'

	If @ServiceType = 1 
		SET @tmp@ServiceType = '一个月'

	If @ServiceType = 2 
		SET @tmp@ServiceType = '一个季度'

	If @ServiceType = 3 
		SET @tmp@ServiceType = '半年'

	If @ServiceType = 4 
		SET @tmp@ServiceType = '一年'

	If @OrderType = 1
		SET @strOrderDesc = @strOrderDesc + '&nbsp;&nbsp;您的订单内容为申请' + @tmp@ServiceType + '正式会员，费用为' + convert(varchar(10),@Paid) + '元，'

	If @OrderType = 2
		SET @strOrderDesc = @strOrderDesc + '&nbsp;&nbsp;您的订单内容为申请储值会员，加值金额为' + convert(varchar(10),@Paid) + '元，'

	If @OrderType = 4
		SET @strOrderDesc = @strOrderDesc + '&nbsp;&nbsp;您的订单内容为申请广告位，费用为' + convert(varchar(10),@Paid) + '元，'

	If @OrderType = 7
		SET @strOrderDesc = @strOrderDesc + '&nbsp;&nbsp;您的订单内容为首页知名企业招聘，费用为' + convert(varchar(10),@Paid) + '元，'

	If @OrderType = 8
		SET @strOrderDesc = @strOrderDesc + '&nbsp;&nbsp;您的订单内容为申请' + @TimeTitle + 'VIP套餐服务，费用为' + convert(varchar(10),@Paid) + '元，'

	If @OrderType = 9
		SET @strOrderDesc = @strOrderDesc + '&nbsp;&nbsp;您的订单内容为购买' + LTRIM(STR(@ActiveQuota)) + '个简历下载数，费用为' + convert(varchar(10),@Paid) + '元，'

	If @OrderType = 10
		SET @strOrderDesc = @strOrderDesc + '&nbsp;&nbsp;您的订单内容为购买' + LTRIM(STR(@JobNumber)) + '个职位并发数，费用为' + convert(varchar(10),@Paid) + '元，'

	If @OrderType = 11
		SET @strOrderDesc = @strOrderDesc + '&nbsp;&nbsp;您的订单内容为购买' + LTRIM(STR(@UserNumber)) + '个用户数，费用为' + convert(varchar(10),@Paid) + '元，'

	SET @strOrderDesc = @strOrderDesc + '为方便您能够及时开通服务，现将付款流程发送给您，如下：<br>'

	If @PayMethod = 1
	Begin
		SET @strOrderDesc = @strOrderDesc + '1、对公汇款<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;收款人：' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;开户银行：' + @BankName + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;帐号：' + @Account + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '&nbsp;汇款用途：会员服务费，订单号' + LTRIM(STR(@OrderID)) + '<br>'   
		SET @strOrderDesc = @strOrderDesc + '2、将银行电汇回单传真至：010-65800851 或者 021-61916451<br>'
		SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在银行电汇回单传真、营业执照齐全后',@NeedInvoice)
	End

	If @PayMethod = 2
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '1、邮局汇款<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;收款人：' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;通信地址：' + @BankName + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;邮政编码：' + @Account + '<br>'    
		SET @strOrderDesc = @strOrderDesc + '&nbsp;附言：订单号' + LTRIM(STR(@OrderID)) + '<br>'  
		SET @strOrderDesc = @strOrderDesc + '2、在邮政汇款收据上，标上订单号，并传真至：010-65800851 或者 021-61916451<br>'
		SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在邮政汇款收据传真、营业执照齐全后',@NeedInvoice)
	End

	If @PayMethod = 6
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '请前来付费：<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;地址：' + @BankName + '<br>'
	End

	If @PayMethod = 7
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '1、工商银行<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;收款人全称：' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;帐号：' + @Account + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '&nbsp;开户银行：' + @BankName + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '2、汇款成功后，请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
		SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全，并确认收到汇款后',@NeedInvoice)
	End

	If @PayMethod = 9
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '1、招商银行<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;收款人全称：' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;帐号：' + @Account + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '&nbsp;开户银行：' + @BankName + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '2、汇款成功后，请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
		SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全，并确认收到汇款后',@NeedInvoice)
	End

	If @PayMethod = 10
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '1、农业银行<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;收款人全称：' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;帐号：' + @Account + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '&nbsp;开户银行：' + @BankName + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '2、汇款成功后，请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
		SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全，并确认收到汇款后',@NeedInvoice)
	End

	If @PayMethod = 19
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '1、支付宝支付（支持全国银行卡，网上付款，自动开通）<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;请到网站在线支付，'
		If @OrderType = 1 Or @OrderType = 2 Or @OrderType = 5 Or @OrderType = 8 Or @OrderType = 9 Or @OrderType = 10 Or @OrderType = 11
			SET @strOrderDesc = @strOrderDesc + '支付完成后，自动开通。' + '<br>'

		If @OrderType = 4
			SET @strOrderDesc = @strOrderDesc + '支付完成后，工作日10分钟之内我们电话与您联系广告制作与播放。' + '<br>'

		If @OrderType = 7
			SET @strOrderDesc = @strOrderDesc + '4、确认收到汇款后，工作日10分钟之内您的单位名称出现在“首页知名企业招聘”。' + '<br>'

		If @NeedInvoice = 1
			SET @strOrderDesc = @strOrderDesc + '汇款到帐后您可以索取发票，在三个工作日内，将您的发票挂号信寄出。<br>'

	End

	If @PayMethod = 12
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '1、建设银行<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;收款人全称：' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;帐号：' + @Account + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '&nbsp;开户银行：' + @BankName + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '2、汇款成功后，请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
		SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全，并确认收到汇款后',@NeedInvoice)
	End

	If @PayMethod = 13
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '1、中国银行<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;收款人全称：' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;帐号：' + @Account + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '&nbsp;开户银行：' + @BankName + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '2、汇款成功后，请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
		SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全，并确认收到汇款后',@NeedInvoice)
	End

	If @PayMethod = 14
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '1、交通银行<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;收款人全称：' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;帐号：' + @Account + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '&nbsp;开户银行：' + @BankName + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '2、汇款成功后，请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
		SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全，并确认收到汇款后',@NeedInvoice)
	End
	
	If @PayMethod = 17
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '1、转帐支票<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;收款人：' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;开户银行：' + @BankName + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;帐号：' + @Account + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '&nbsp;汇款用途：会员服务费，订单号' + LTRIM(STR(@OrderID)) + '<br>'   
		SET @strOrderDesc = @strOrderDesc + '2、您可以将支票快递给我们，也可以将支票亲自送过来。<br>'
		SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在收到转帐支票、营业执照齐全后',@NeedInvoice)
	End
	
	If @PayMethod = 18
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '1、同城转帐<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;收款人：' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;开户银行：' + @BankName + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;帐号：' + @Account + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '&nbsp;汇款用途：会员服务费，订单号' + LTRIM(STR(@OrderID)) + '<br>'   
		SET @strOrderDesc = @strOrderDesc + '2、将同城转帐回单传真至：010-65800851 或者 021-61916451<br>'
		SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在同城转帐回单传真、营业执照齐全后',@NeedInvoice)
	End
	
	If @PayMethod = 20
	BEGIN
		SET @strOrderDesc = @strOrderDesc + '1、邮政储蓄银行<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;收款人全称：' + @Receiver + '<br>'
		SET @strOrderDesc = @strOrderDesc + '&nbsp;帐号：' + @Account + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '&nbsp;开户银行：' + @BankName + '<br>' 
		SET @strOrderDesc = @strOrderDesc + '2、汇款成功后，请立即打电话或者发邮件通知我们订单号以及汇款金额。<br>'
		SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全，并确认收到汇款后',@NeedInvoice)
	End
	RETURN @strOrderDesc
END
GO

/****** Object:  UserDefinedFunction [dbo].[schGetPayDesc]    Script Date: 2018/12/13 18:28:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[schGetPayDesc]
(
	@OrderType TINYINT,
	@strDesc NVARCHAR(200),
	@NeedInvoice BIT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @PayDesc NVARCHAR(500)
	SET @PayDesc = '3、如果贵单位还没有通过营业执照审核，请先上传营业执照，或者传真到 010-65800851 或者 021-61916451<br>'
	If @OrderType = 1 Or @OrderType = 2 Or @OrderType = 8 Or @OrderType = 9 Or @OrderType = 10 Or @OrderType = 11
		SET @PayDesc = @PayDesc + '4、' + @strDesc + '，工作日10分钟之内开通，并通过E-mail通知。<br>'

	If @OrderType = 4
		SET @PayDesc = @PayDesc + '4、' + @strDesc + '，工作日10分钟之内我们电话与您联系广告制作与播放。<br>'

	If @OrderType = 7
		SET @PayDesc = @PayDesc + '4、确认收到汇款后，工作日10分钟之内您的单位名称出现在“首页知名企业招聘”。<br>'

	If @NeedInvoice = 1
		SET @PayDesc = @PayDesc + '5、汇款到帐后您可以索取发票，在三个工作日内，将您的发票挂号信寄出。<br>'

	RETURN @PayDesc
END
GO

/****** Object:  UserDefinedFunction [dbo].[SHA1]    Script Date: 2018/12/13 18:28:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].[SHA1]
(
	@Str VARCHAR(40)
)
RETURNS VARCHAR(40)
AS
BEGIN	
	RETURN RIGHT(SYS.fn_VarBinToHexStr(hashbytes('SHA1', LOWER(@Str))), 40)
END


GO

/****** Object:  UserDefinedFunction [dbo].[ShowContact]    Script Date: 2018/12/13 18:28:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ShowContact]
(
	@cvMainID INT,
	@cpMainID INT
)
RETURNS BIT
AS
BEGIN
	DECLARE @cpMemeberType INT
	SELECT @cpMemeberType = MemberType FROM cpMain WITH(NOLOCK) WHERE ID = @cpMainID
	IF @cpMemeberType IN(3, 10)
		RETURN 1
	ELSE IF @cpMemeberType = 11
	BEGIN

		BEGIN
			IF EXISTS(
				SELECT 'x' FROM caBalanceLog WITH(NOLOCK)
				WHERE cpMainID = @cpMainID
				AND cvMainID = @cvMainID
				AND CONVERT(VARCHAR(100), AddDate, 23) = CONVERT(VARCHAR(100), GETDATE(), 23)
			) OR EXISTS(
				SELECT 'x' FROM CaCvQuotaLog WITH(NOLOCK)
				WHERE cpMainID = @cpMainID
				AND cvMainID = @cvMainID
			)
				RETURN 1
		END
	END
	ELSE
	BEGIN
		DECLARE @isInActiveLog INT
		DECLARE @isInDailyGiftLog INT
		SELECT @isInDailyGiftLog = count(1) FROM caDailyGiftLog WITH(NOLOCK)
			WHERE cpMainID = @cpMainID
			AND cvMainID = @cvMainID
		IF @isInDailyGiftLog > 0 
			RETURN 1
		SELECT @isInActiveLog = count(1) FROM caActiveLog WITH(NOLOCK)
			WHERE cpMainID = @cpMainID
			AND cvMainID = @cvMainID
		If @isInActiveLog > 0  
			RETURN 1
		IF EXISTS (
			SELECT 'x' FROM ExJobApply a WITH(NOLOCK),Job b 
			WHERE a.JobID = b.ID 
			AND a.IsPassed = 1 
			AND a.cvMainID = @cvMainID
			AND b.cpMainID = @cpMainID
		)
		RETURN 1
	END
	RETURN 0
END
GO

/****** Object:  UserDefinedFunction [dbo].[ShowContactByExcel]    Script Date: 2018/12/13 18:28:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ShowContactByExcel]
(
	@cvMainID INT,
	@cpMainID INT
)
RETURNS BIT
AS
BEGIN
	DECLARE @cpMemeberType INT
	SELECT @cpMemeberType = MemberType FROM cpMain WITH(NOLOCK) WHERE ID = @cpMainID
	IF @cpMemeberType = 10 Or @cpMemeberType = 11
	BEGIN
		IF @cpMemeberType = 10
			RETURN 1
		ELSE
		BEGIN
			IF EXISTS(
				SELECT 'x' FROM caBalanceLog WITH(NOLOCK)
				WHERE cpMainID = @cpMainID
				AND cvMainID = @cvMainID
				AND CONVERT(VARCHAR(100), AddDate, 23) = CONVERT(VARCHAR(100), GETDATE(), 23)
			)
				RETURN 1
		END
	END
	ELSE
	BEGIN
		DECLARE @isInActiveLog INT
		DECLARE @isInDailyGiftLog INT
		SELECT @isInDailyGiftLog = count(1) FROM caDailyGiftLog WITH(NOLOCK)
			WHERE cpMainID = @cpMainID
			AND cvMainID = @cvMainID
		IF @isInDailyGiftLog > 0 
			RETURN 1
		SELECT @isInActiveLog = count(1) FROM caActiveLog WITH(NOLOCK)
			WHERE cpMainID = @cpMainID
			AND cvMainID = @cvMainID
		If @isInActiveLog > 0  
			RETURN 1
		IF EXISTS (
			SELECT 'x' FROM cvMain
			WHERE ID = @cvMainID
			AND IsOpen = 1
		)
		RETURN 1
	END
	RETURN 0
END
GO

/****** Object:  UserDefinedFunction [dbo].[StripHTML]    Script Date: 2018/12/13 18:28:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[StripHTML]   
(
	@HTMLText VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)   
AS   
BEGIN   
	DECLARE @Start INT   
	DECLARE @End INT   
	DECLARE @Length INT  
	SET @HTMLText = REPLACE(@HTMLText, '&amp;nbsp;', ' ')
	SET @HTMLText = REPLACE(@HTMLText, '&nbsp;', ' ')
	--SET @HTMLText = REPLACE(LTRIM(RTRIM(@HTMLText)), '  ', '&nbsp; &nbsp; ')
	SET @HTMLText = REPLACE(@HTMLText, '<br>'+Char(13)+Char(10), Char(13)+Char(10))
	SET @HTMLText = REPLACE(@HTMLText, '<br >'+Char(13)+Char(10), Char(13)+Char(10))
	SET @HTMLText = REPLACE(@HTMLText, '<br />'+Char(13)+Char(10), Char(13)+Char(10))
	--SET @HTMLText = REPLACE(@HTMLText, '</p>'+Char(13)+Char(10), '')
	--SET @HTMLText = REPLACE(@HTMLText, '</div>'+Char(13)+Char(10), '')

	SET @HTMLText = REPLACE(@HTMLText, '<br>'+Char(13), Char(13)+Char(10))
	SET @HTMLText = REPLACE(@HTMLText, '<br >'+Char(13), Char(13)+Char(10))
	SET @HTMLText = REPLACE(@HTMLText, '<br />'+Char(13), Char(13)+Char(10))

	SET @HTMLText = REPLACE(@HTMLText, '<br>'+Char(10), Char(13)+Char(10))
	SET @HTMLText = REPLACE(@HTMLText, '<br >'+Char(10), Char(13)+Char(10))
	SET @HTMLText = REPLACE(@HTMLText, '<br />'+Char(10), Char(13)+Char(10))
	--SET @HTMLText = REPLACE(@HTMLText, '</p>'+Char(10), '')
	--SET @HTMLText = REPLACE(@HTMLText, '</div>'+Char(10), '')

	SET @HTMLText = REPLACE(@HTMLText, '<br>', Char(13)+Char(10))
	SET @HTMLText = REPLACE(@HTMLText, '<br >', Char(13)+Char(10))
	SET @HTMLText = REPLACE(@HTMLText, '<br />', Char(13)+Char(10))
	--SET @HTMLText = REPLACE(@HTMLText, '</p>', '')
	--SET @HTMLText = REPLACE(@HTMLText, '</div>', '')
/*
	SET @Start = dbo.HtmlPos(@HTMLText)   
	SET @End = CHARINDEX('>', @HTMLText, @Start)   
	SET @Length = (@End - @Start) + 1 
	WHILE @Start > 0 AND @End > 0 AND @Length > 0   
	BEGIN   
		SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')   
		SET @Start = dbo.HtmlPos(@HTMLText)   
		SET @End = CHARINDEX('>', @HTMLText, @Start)   
		SET @Length = (@End - @Start) + 1   
	END
	SET @HTMLText = REPLACE(@HTMLText, '<', '&lt;')
	SET @HTMLText = REPLACE(@HTMLText, '>', '&gt;')
*/
	RETURN RTRIM(@HTMLText)
END
GO

/****** Object:  UserDefinedFunction [dbo].[TextTrim]    Script Date: 2018/12/13 18:28:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--#############################333
--文本框前导4个空格
--#############################333
CREATE FUNCTION [dbo].[TextTrim]
(
	@sText VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	IF LTRIM(@sText) = ''
		RETURN NULL

	SET @sText = REPLACE(RTRIM(@sText), '&nbsp;', ' ')
	IF LEFT(@sText , 4) = '    '
		SET @sText = '    ' + LTRIM(@sText)

	RETURN @sText
END



GO

/****** Object:  UserDefinedFunction [dbo].[TheMonth]    Script Date: 2018/12/13 18:28:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[TheMonth]
(
	@CountDate INT
)
RETURNS INT   
AS
BEGIN
	DECLARE @Result INT
    IF CAST(RIGHT(STR(@CountDate), 2) AS INT) >= 26
    BEGIN
      DECLARE @dd SMALLDATETIME
      SET @dd = DATEADD(m, 1, LEFT(LTRIM(RTRIM(STR(@CountDate))),4) + '-' + RIGHT(LEFT(LTRIM(RTRIM(STR(@CountDate))),6),2)+'-1 00:00')
      SET @Result=YEAR(@dd)*100+MONTH(@dd)
    END
    ELSE
    BEGIN
      SET @Result=@CountDate/100
    END
    RETURN @Result   
END

GO

/****** Object:  UserDefinedFunction [dbo].[ToJianPin]    Script Date: 2018/12/13 18:28:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ToJianPin](@Words NVARCHAR(2000))  
RETURNS VARCHAR(8000)  
AS 
BEGIN  
    DECLARE @Word nchar(1)  
    DECLARE @PinYin VARCHAR(8000)  
    DECLARE @Index int 
    DECLARE @WordLenth int 
    DECLARE @Unicode int 
    SET @Index = 1  
    SET @Words = LTRIM(RTRIM(@Words))  
    SET @WordLenth = LEN(@Words)  
    WHILE (@Index <= @WordLenth) --循环取字符 
    BEGIN  
    SET @Word = SUBSTRING(@Words, @Index, 1)  
    SET @Unicode = UNICODE(@Word)
    SET @PinYin = ISNULL(@PinYin,'')+   
    (
		CASE WHEN UNICODE(@Word) BETWEEN 19968 AND 19968+20901 THEN   
		(
			SELECT TOP 1 PinYin FROM 
			(  
				SELECT 'a' AS PinYin, '驁' AS Word  
				UNION ALL SELECT 'b', '簿'
				UNION ALL SELECT 'c', '錯'
				UNION ALL SELECT 'd', '鵽'
				UNION ALL SELECT 'e', '樲'
				UNION ALL SELECT 'f', '猤'
				UNION ALL SELECT 'g', '腂'
				UNION ALL SELECT 'h', '夻'
				UNION ALL SELECT 'j', '攈'
				UNION ALL SELECT 'k', '穒'
				UNION ALL SELECT 'l', '鱳'
				UNION ALL SELECT 'm', '旀'
				UNION ALL SELECT 'n', '桛'
				UNION ALL SELECT 'o', '漚'
				UNION ALL SELECT 'p', '曝'
				UNION ALL SELECT 'q', '囕'
				UNION ALL SELECT 'r', '鶸'
				UNION ALL SELECT 's', '蜶'
				UNION ALL SELECT 't', '籜'
				UNION ALL SELECT 'w', '鶩'
				UNION ALL SELECT 'x', '鑂'
				UNION ALL SELECT 'y', '韻'
				UNION ALL SELECT 'z', '咗'
			) Temp   
			WHERE Word >= @Word COLLATE Chinese_PRC_CS_AS_KS_WS   
			ORDER BY Word COLLATE Chinese_PRC_CS_AS_KS_WS ASC
		) 
		ELSE @Word END
	)
	SET @Index = @Index + 1  
    END
    RETURN @PinYin  
END  
GO

/****** Object:  UserDefinedFunction [dbo].[ToPinYin]    Script Date: 2018/12/13 18:28:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].[ToPinYin](@Words NVARCHAR(2000))  
RETURNS VARCHAR(8000)  
AS 
BEGIN  
    DECLARE @Word nchar(1)  
    DECLARE @PinYin VARCHAR(8000)  
    DECLARE @Index int 
    DECLARE @WordLenth int 
    DECLARE @Unicode int 
    SET @Index = 1  
    SET @Words = LTRIM(RTRIM(@Words))  
    SET @WordLenth = LEN(@Words)  
    WHILE (@Index <= @WordLenth) --循环取字符 
    BEGIN  
    SET @Word = SUBSTRING(@Words, @Index, 1)  
    SET @Unicode = UNICODE(@Word)
    SET @PinYin = ISNULL(@PinYin,'')+   
    (
		CASE WHEN UNICODE(@Word) BETWEEN 19968 AND 19968+20901 THEN   
		(
			SELECT TOP 1 PinYin FROM 
			(  
				SELECT 'a' AS PinYin,N'厑' AS Word  
				UNION ALL SELECT 'ai',N'靉' 
				UNION ALL SELECT 'an',N'黯' 
				UNION ALL SELECT 'ang',N'醠' 
				UNION ALL SELECT 'ao',N'驁' 
				UNION ALL SELECT 'ba',N'欛' 
				UNION ALL SELECT 'bai',N'瓸'
				UNION ALL SELECT 'ban',N'瓣' 
				UNION ALL SELECT 'bang',N'鎊' 
				UNION ALL SELECT 'bao',N'鑤' 
				UNION ALL SELECT 'bei',N'鐾' 
				UNION ALL SELECT 'ben',N'輽' 
				UNION ALL SELECT 'beng',N'鏰' 
				UNION ALL SELECT 'bi',N'鼊' 
				UNION ALL SELECT 'bian',N'變' 
				UNION ALL SELECT 'biao',N'鰾' 
				UNION ALL SELECT 'bie',N'彆' 
				UNION ALL SELECT 'bin',N'鬢' 
				UNION ALL SELECT 'bing',N'靐' 
				UNION ALL SELECT 'bo',N'蔔' 
				UNION ALL SELECT 'bu',N'簿' 
				UNION ALL SELECT 'ca',N'囃' 
				UNION ALL SELECT 'cai',N'乲'
				UNION ALL SELECT 'can',N'爘' 
				UNION ALL SELECT 'cang',N'賶' 
				UNION ALL SELECT 'cao',N'鼜' 
				UNION ALL SELECT 'ce',N'簎' 
				UNION ALL SELECT 'cen',N'笒' 
				UNION ALL SELECT 'ceng',N'乽' --硛硳岾猠乽 
				UNION ALL SELECT 'cha',N'詫' 
				UNION ALL SELECT 'chai',N'囆' 
				UNION ALL SELECT 'chan',N'顫' 
				UNION ALL SELECT 'chang',N'韔' 
				UNION ALL SELECT 'chao',N'觘' 
				UNION ALL SELECT 'che',N'爡' 
				UNION ALL SELECT 'chen',N'讖' 
				UNION ALL SELECT 'cheng',N'秤' 
				UNION ALL SELECT 'chi',N'鷘' 
				UNION ALL SELECT 'chong',N'銃' 
				UNION ALL SELECT 'chou',N'殠' 
				UNION ALL SELECT 'chu',N'矗' 
				UNION ALL SELECT 'chuai',N'踹' 
				UNION ALL SELECT 'chuan',N'鶨' 
				UNION ALL SELECT 'chuang',N'愴' 
				UNION ALL SELECT 'chui',N'顀' 
				UNION ALL SELECT 'chun',N'蠢' 
				UNION ALL SELECT 'chuo',N'縒' 
				UNION ALL SELECT 'ci',N'嗭' --賜嗭 
				UNION ALL SELECT 'cong',N'謥' 
				UNION ALL SELECT 'cou',N'輳' 
				UNION ALL SELECT 'cu',N'顣' 
				UNION ALL SELECT 'cuan',N'爨' 
				UNION ALL SELECT 'cui',N'臎' 
				UNION ALL SELECT 'cun',N'籿' 
				UNION ALL SELECT 'cuo',N'錯' 
				UNION ALL SELECT 'da',N'橽' 
				UNION ALL SELECT 'dai',N'靆' 
				UNION ALL SELECT 'dan',N'饏' 
				UNION ALL SELECT 'dang',N'闣' 
				UNION ALL SELECT 'dao',N'纛' 
				UNION ALL SELECT 'de',N'的' 
				UNION ALL SELECT 'den',N'扽' 
				UNION ALL SELECT 'deng',N'鐙' 
				UNION ALL SELECT 'di',N'螮' 
				UNION ALL SELECT 'dia',N'嗲' 
				UNION ALL SELECT 'dian',N'驔' 
				UNION ALL SELECT 'diao',N'鑃' 
				UNION ALL SELECT 'die',N'嚸' --眰嚸 
				UNION ALL SELECT 'ding',N'顁' 
				UNION ALL SELECT 'diu',N'銩' 
				UNION ALL SELECT 'dong',N'霘' 
				UNION ALL SELECT 'dou',N'鬭' 
				UNION ALL SELECT 'du',N'蠹' 
				UNION ALL SELECT 'duan',N'叾' --籪叾 
				UNION ALL SELECT 'dui',N'譵' 
				UNION ALL SELECT 'dun',N'踲' 
				UNION ALL SELECT 'duo',N'鵽' 
				UNION ALL SELECT 'e',N'鱷' 
				UNION ALL SELECT 'en',N'摁' 
				UNION ALL SELECT 'eng',N'鞥' 
				UNION ALL SELECT 'er',N'樲' 
				UNION ALL SELECT 'fa',N'髮' 
				UNION ALL SELECT 'fan',N'瀪' 
				UNION ALL SELECT 'fang',N'放' 
				UNION ALL SELECT 'fei',N'靅' 
				UNION ALL SELECT 'fen',N'鱝' 
				UNION ALL SELECT 'feng',N'覅' 
				UNION ALL SELECT 'fo',N'梻' 
				UNION ALL SELECT 'fou',N'鴀' 
				UNION ALL SELECT 'fu',N'猤' --鰒猤 
				UNION ALL SELECT 'ga',N'魀' 
				UNION ALL SELECT 'gai',N'瓂' 
				UNION ALL SELECT 'gan',N'灨' 
				UNION ALL SELECT 'gang',N'戇' 
				UNION ALL SELECT 'gao',N'鋯' 
				UNION ALL SELECT 'ge',N'獦' 
				UNION ALL SELECT 'gei',N'給' 
				UNION ALL SELECT 'gen',N'搄' 
				UNION ALL SELECT 'geng',N'堩' --亙堩啹喼嗰 
				UNION ALL SELECT 'gong',N'兣' --熕贑兝兣 
				UNION ALL SELECT 'gou',N'購' 
				UNION ALL SELECT 'gu',N'顧' 
				UNION ALL SELECT 'gua',N'詿' 
				UNION ALL SELECT 'guai',N'恠' 
				UNION ALL SELECT 'guan',N'鱹' 
				UNION ALL SELECT 'guang',N'撗' 
				UNION ALL SELECT 'gui',N'鱥' 
				UNION ALL SELECT 'gun',N'謴' 
				UNION ALL SELECT 'guo',N'腂' 
				UNION ALL SELECT 'ha',N'哈' 
				UNION ALL SELECT 'hai',N'饚' 
				UNION ALL SELECT 'han',N'鶾' 
				UNION ALL SELECT 'hang',N'沆' 
				UNION ALL SELECT 'hao',N'兞' 
				UNION ALL SELECT 'he',N'靏' 
				UNION ALL SELECT 'hei',N'嬒' 
				UNION ALL SELECT 'hen',N'恨' 
				UNION ALL SELECT 'heng',N'堼' --堼囍 
				UNION ALL SELECT 'hong',N'鬨' 
				UNION ALL SELECT 'hou',N'鱟' 
				UNION ALL SELECT 'hu',N'鸌' 
				UNION ALL SELECT 'hua',N'蘳' 
				UNION ALL SELECT 'huai',N'蘾' 
				UNION ALL SELECT 'huan',N'鰀' 
				UNION ALL SELECT 'huang',N'鎤' 
				UNION ALL SELECT 'hui',N'顪' 
				UNION ALL SELECT 'hun',N'諢' 
				UNION ALL SELECT 'huo',N'夻' 
				UNION ALL SELECT 'ji',N'驥' 
				UNION ALL SELECT 'jia',N'嗧' 
				UNION ALL SELECT 'jian',N'鑳' 
				UNION ALL SELECT 'jiang',N'謽' 
				UNION ALL SELECT 'jiao',N'釂' 
				UNION ALL SELECT 'jie',N'繲' 
				UNION ALL SELECT 'jin',N'齽' 
				UNION ALL SELECT 'jing',N'竸' 
				UNION ALL SELECT 'jiong',N'蘔' 
				UNION ALL SELECT 'jiu',N'欍' 
				UNION ALL SELECT 'ju',N'爠' 
				UNION ALL SELECT 'juan',N'羂' 
				UNION ALL SELECT 'jue',N'钁' 
				UNION ALL SELECT 'jun',N'攈' 
				UNION ALL SELECT 'ka',N'鉲' 
				UNION ALL SELECT 'kai',N'乫' --鎎乫 
				UNION ALL SELECT 'kan',N'矙' 
				UNION ALL SELECT 'kang',N'閌' 
				UNION ALL SELECT 'kao',N'鯌' 
				UNION ALL SELECT 'ke',N'騍' 
				UNION ALL SELECT 'ken',N'褃' 
				UNION ALL SELECT 'keng',N'鏗' --巪乬唟厼怾 
				UNION ALL SELECT 'kong',N'廤' 
				UNION ALL SELECT 'kou',N'鷇' 
				UNION ALL SELECT 'ku',N'嚳' 
				UNION ALL SELECT 'kua',N'骻' 
				UNION ALL SELECT 'kuai',N'鱠' 
				UNION ALL SELECT 'kuan',N'窾' 
				UNION ALL SELECT 'kuang',N'鑛' 
				UNION ALL SELECT 'kui',N'鑎' 
				UNION ALL SELECT 'kun',N'睏' 
				UNION ALL SELECT 'kuo',N'穒' 
				UNION ALL SELECT 'la',N'鞡' 
				UNION ALL SELECT 'lai',N'籟' 
				UNION ALL SELECT 'lan',N'糷' 
				UNION ALL SELECT 'lang',N'唥' 
				UNION ALL SELECT 'lao',N'軂' 
				UNION ALL SELECT 'le',N'餎' 
				UNION ALL SELECT 'lei',N'脷' --嘞脷 
				UNION ALL SELECT 'leng',N'睖' 
				UNION ALL SELECT 'li',N'瓈' 
				UNION ALL SELECT 'lia',N'倆' 
				UNION ALL SELECT 'lian',N'纞' 
				UNION ALL SELECT 'liang',N'鍄' 
				UNION ALL SELECT 'liao',N'瞭' 
				UNION ALL SELECT 'lie',N'鱲' 
				UNION ALL SELECT 'lin',N'轥' --轥拎 
				UNION ALL SELECT 'ling',N'炩' 
				UNION ALL SELECT 'liu',N'咯' --瓼甅囖咯 
				UNION ALL SELECT 'long',N'贚' 
				UNION ALL SELECT 'lou',N'鏤' 
				UNION ALL SELECT 'lu',N'氇' 
				UNION ALL SELECT 'lv',N'鑢' 
				UNION ALL SELECT 'luan',N'亂' 
				UNION ALL SELECT 'lue',N'擽' 
				UNION ALL SELECT 'lun',N'論' 
				UNION ALL SELECT 'luo',N'鱳' 
				UNION ALL SELECT 'ma',N'嘛' 
				UNION ALL SELECT 'mai',N'霢' 
				UNION ALL SELECT 'man',N'蘰' 
				UNION ALL SELECT 'mang',N'蠎' 
				UNION ALL SELECT 'mao',N'唜' 
				UNION ALL SELECT 'me',N'癦' --癦呅 
				UNION ALL SELECT 'mei',N'嚜' 
				UNION ALL SELECT 'men',N'們' 
				UNION ALL SELECT 'meng',N'霥' --霿踎 
				UNION ALL SELECT 'mi',N'羃' 
				UNION ALL SELECT 'mian',N'麵' 
				UNION ALL SELECT 'miao',N'廟' 
				UNION ALL SELECT 'mie',N'鱴' --鱴瓱 
				UNION ALL SELECT 'min',N'鰵' 
				UNION ALL SELECT 'ming',N'詺' 
				UNION ALL SELECT 'miu',N'謬' 
				UNION ALL SELECT 'mo',N'耱' --耱乮 
				UNION ALL SELECT 'mou',N'麰' --麰蟱 
				UNION ALL SELECT 'mu',N'旀' 
				UNION ALL SELECT 'na',N'魶' 
				UNION ALL SELECT 'nai',N'錼' 
				UNION ALL SELECT 'nan',N'婻' 
				UNION ALL SELECT 'nang',N'齉' 
				UNION ALL SELECT 'nao',N'臑' 
				UNION ALL SELECT 'ne',N'呢' 
				UNION ALL SELECT 'nei',N'内' --嫩焾 
				UNION ALL SELECT 'nen',N'嫩' 
				UNION ALL SELECT 'neng',N'能' --莻嗯鈪銰啱 
				UNION ALL SELECT 'ni',N'嬺' 
				UNION ALL SELECT 'nian',N'艌' 
				UNION ALL SELECT 'niang',N'釀' 
				UNION ALL SELECT 'niao',N'脲' 
				UNION ALL SELECT 'nie',N'钀' 
				UNION ALL SELECT 'nin',N'拰' 
				UNION ALL SELECT 'ning',N'濘' 
				UNION ALL SELECT 'niu',N'靵' 
				UNION ALL SELECT 'nong',N'齈' 
				UNION ALL SELECT 'nou',N'譳' 
				UNION ALL SELECT 'nu',N'搙' 
				UNION ALL SELECT 'nv',N'衄' 
				UNION ALL SELECT 'nue',N'瘧' 
				UNION ALL SELECT 'nuan',N'燶' --硸黁燶郍 
				UNION ALL SELECT 'nuo',N'桛' 
				UNION ALL SELECT 'o',N'鞰' --毮夞乯鞰 
				UNION ALL SELECT 'ou',N'漚' 
				UNION ALL SELECT 'pa',N'袙' 
				UNION ALL SELECT 'pai',N'磗' --鎃磗 
				UNION ALL SELECT 'pan',N'鑻' 
				UNION ALL SELECT 'pang',N'胖' 
				UNION ALL SELECT 'pao',N'礮' 
				UNION ALL SELECT 'pei',N'轡' 
				UNION ALL SELECT 'pen',N'喯' 
				UNION ALL SELECT 'peng',N'喸' --浌巼闏乶喸 
				UNION ALL SELECT 'pi',N'鸊' 
				UNION ALL SELECT 'pian',N'騙' 
				UNION ALL SELECT 'piao',N'慓' 
				UNION ALL SELECT 'pie',N'嫳' 
				UNION ALL SELECT 'pin',N'聘' 
				UNION ALL SELECT 'ping',N'蘋' 
				UNION ALL SELECT 'po',N'魄' 
				UNION ALL SELECT 'pou',N'哛' --兺哛 
				UNION ALL SELECT 'pu',N'曝' 
				UNION ALL SELECT 'qi',N'蟿' 
				UNION ALL SELECT 'qia',N'髂' 
				UNION ALL SELECT 'qian',N'縴' 
				UNION ALL SELECT 'qiang',N'瓩' --羻兛瓩 
				UNION ALL SELECT 'qiao',N'躈' 
				UNION ALL SELECT 'qie',N'籡' 
				UNION ALL SELECT 'qin',N'藽' 
				UNION ALL SELECT 'qing',N'櫦' 
				UNION ALL SELECT 'qiong',N'瓗' 
				UNION ALL SELECT 'qiu',N'糗' 
				UNION ALL SELECT 'qu',N'覻' 
				UNION ALL SELECT 'quan',N'勸' 
				UNION ALL SELECT 'que',N'礭' 
				UNION ALL SELECT 'qun',N'囕' 
				UNION ALL SELECT 'ran',N'橪' 
				UNION ALL SELECT 'rang',N'讓' 
				UNION ALL SELECT 'rao',N'繞' 
				UNION ALL SELECT 're',N'熱' 
				UNION ALL SELECT 'ren',N'餁' 
				UNION ALL SELECT 'reng',N'陾' 
				UNION ALL SELECT 'ri',N'馹' 
				UNION ALL SELECT 'rong',N'穃' 
				UNION ALL SELECT 'rou',N'嶿' 
				UNION ALL SELECT 'ru',N'擩' 
				UNION ALL SELECT 'ruan',N'礝' 
				UNION ALL SELECT 'rui',N'壡' 
				UNION ALL SELECT 'run',N'橍' --橍挼 
				UNION ALL SELECT 'ruo',N'鶸' 
				UNION ALL SELECT 'sa',N'栍' --櫒栍 
				UNION ALL SELECT 'sai',N'虄' --簺虄 
				UNION ALL SELECT 'san',N'閐' 
				UNION ALL SELECT 'sang',N'喪' 
				UNION ALL SELECT 'sao',N'髞' 
				UNION ALL SELECT 'se',N'飋' --裇聓 
				UNION ALL SELECT 'sen',N'篸' 
				UNION ALL SELECT 'seng',N'縇' --閪縇 
				UNION ALL SELECT 'sha',N'霎' 
				UNION ALL SELECT 'shai',N'曬' 
				UNION ALL SELECT 'shan',N'鱔' 
				UNION ALL SELECT 'shang',N'緔' 
				UNION ALL SELECT 'shao',N'潲' 
				UNION ALL SELECT 'she',N'欇' 
				UNION ALL SELECT 'shen',N'瘮' 
				UNION ALL SELECT 'sheng',N'賸' 
				UNION ALL SELECT 'shi',N'瓧' --鰘齛兙瓧 
				UNION ALL SELECT 'shou',N'鏉' 
				UNION ALL SELECT 'shu',N'虪' 
				UNION ALL SELECT 'shua',N'誜' 
				UNION ALL SELECT 'shuai',N'卛' 
				UNION ALL SELECT 'shuan',N'腨' 
				UNION ALL SELECT 'shuang',N'灀' 
				UNION ALL SELECT 'shui',N'睡' 
				UNION ALL SELECT 'shun',N'鬊' 
				UNION ALL SELECT 'shuo',N'鑠' 
				UNION ALL SELECT 'si',N'乺' --瀃螦乺 
				UNION ALL SELECT 'song',N'鎹' 
				UNION ALL SELECT 'sou',N'瘶' 
				UNION ALL SELECT 'su',N'鷫' 
				UNION ALL SELECT 'suan',N'算' 
				UNION ALL SELECT 'sui',N'鐩' 
				UNION ALL SELECT 'sun',N'潠' 
				UNION ALL SELECT 'suo',N'蜶' 
				UNION ALL SELECT 'ta',N'襨' --躢襨 
				UNION ALL SELECT 'tai',N'燤' 
				UNION ALL SELECT 'tan',N'賧' 
				UNION ALL SELECT 'tang',N'燙' 
				UNION ALL SELECT 'tao',N'畓' --討畓 
				UNION ALL SELECT 'te',N'蟘' 
				UNION ALL SELECT 'teng',N'朰' --霯唞朰 
				UNION ALL SELECT 'ti',N'趯' 
				UNION ALL SELECT 'tian',N'舚' 
				UNION ALL SELECT 'tiao',N'糶' 
				UNION ALL SELECT 'tie',N'餮' 
				UNION ALL SELECT 'ting',N'乭' --濎乭 
				UNION ALL SELECT 'tong',N'憅' 
				UNION ALL SELECT 'tou',N'透' 
				UNION ALL SELECT 'tu',N'鵵' 
				UNION ALL SELECT 'tuan',N'褖' 
				UNION ALL SELECT 'tui',N'駾' 
				UNION ALL SELECT 'tun',N'坉' 
				UNION ALL SELECT 'tuo',N'籜' 
				UNION ALL SELECT 'wa',N'韤' 
				UNION ALL SELECT 'wai',N'顡' 
				UNION ALL SELECT 'wan',N'贎' 
				UNION ALL SELECT 'wang',N'朢' 
				UNION ALL SELECT 'wei',N'躛' 
				UNION ALL SELECT 'wen',N'璺' 
				UNION ALL SELECT 'weng',N'齆' 
				UNION ALL SELECT 'wo',N'齷' 
				UNION ALL SELECT 'wu',N'鶩' 
				UNION ALL SELECT 'xi',N'衋' 
				UNION ALL SELECT 'xia',N'鏬' 
				UNION ALL SELECT 'xian',N'鼸' 
				UNION ALL SELECT 'xiang',N'鱌' 
				UNION ALL SELECT 'xiao',N'斆' 
				UNION ALL SELECT 'xie',N'躞' 
				UNION ALL SELECT 'xin',N'釁' 
				UNION ALL SELECT 'xing',N'臖' 
				UNION ALL SELECT 'xiong',N'敻' 
				UNION ALL SELECT 'xiu',N'齅' 
				UNION ALL SELECT 'xu',N'蓿' 
				UNION ALL SELECT 'xuan',N'贙' 
				UNION ALL SELECT 'xue',N'瀥' 
				UNION ALL SELECT 'xun',N'鑂' 
				UNION ALL SELECT 'ya',N'齾' 
				UNION ALL SELECT 'yan',N'灩' 
				UNION ALL SELECT 'yang',N'樣' 
				UNION ALL SELECT 'yao',N'鑰' 
				UNION ALL SELECT 'ye',N'岃' --鸈膶岃 
				UNION ALL SELECT 'yi',N'齸' 
				UNION ALL SELECT 'yin',N'檼' 
				UNION ALL SELECT 'ying',N'譍' 
				UNION ALL SELECT 'yo',N'喲' 
				UNION ALL SELECT 'yong',N'醟' 
				UNION ALL SELECT 'you',N'鼬' 
				UNION ALL SELECT 'yu',N'爩' 
				UNION ALL SELECT 'yuan',N'願' 
				UNION ALL SELECT 'yue',N'鸙' 
				UNION ALL SELECT 'yun',N'韻' 
				UNION ALL SELECT 'za',N'雥' 
				UNION ALL SELECT 'zai',N'縡' 
				UNION ALL SELECT 'zan',N'饡' 
				UNION ALL SELECT 'zang',N'臟' 
				UNION ALL SELECT 'zao',N'竈' 
				UNION ALL SELECT 'ze',N'稄' 
				UNION ALL SELECT 'zei',N'鱡' 
				UNION ALL SELECT 'zen',N'囎' 
				UNION ALL SELECT 'zeng',N'贈' 
				UNION ALL SELECT 'zha',N'醡' 
				UNION ALL SELECT 'zhai',N'瘵' 
				UNION ALL SELECT 'zhan',N'驏' 
				UNION ALL SELECT 'zhang',N'瞕' 
				UNION ALL SELECT 'zhao',N'羄' 
				UNION ALL SELECT 'zhe',N'鷓' 
				UNION ALL SELECT 'zhen',N'黮' 
				UNION ALL SELECT 'zheng',N'證' 
				UNION ALL SELECT 'zhi',N'豒' 
				UNION ALL SELECT 'zhong',N'諥' 
				UNION ALL SELECT 'zhou',N'驟' 
				UNION ALL SELECT 'zhu',N'鑄' 
				UNION ALL SELECT 'zhua',N'爪' 
				UNION ALL SELECT 'zhuai',N'跩' 
				UNION ALL SELECT 'zhuan',N'籑' 
				UNION ALL SELECT 'zhuang',N'戅' 
				UNION ALL SELECT 'zhui',N'鑆' 
				UNION ALL SELECT 'zhun',N'稕' 
				UNION ALL SELECT 'zhuo',N'籱' 
				UNION ALL SELECT 'zi',N'漬' 
				UNION ALL SELECT 'zong',N'縱' 
				UNION ALL SELECT 'zou',N'媰' 
				UNION ALL SELECT 'zu',N'謯' 
				UNION ALL SELECT 'zuan',N'攥' 
				UNION ALL SELECT 'zui',N'欈' 
				UNION ALL SELECT 'zun',N'銌' 
				UNION ALL SELECT 'zuo',N'咗'
			) Temp   
			WHERE Word >= @Word COLLATE Chinese_PRC_CS_AS_KS_WS   
			ORDER BY Word COLLATE Chinese_PRC_CS_AS_KS_WS ASC
		) 
		ELSE @Word END
	)
	SET @Index = @Index + 1  
    END
    RETURN @PinYin  
END  
GO

/****** Object:  UserDefinedFunction [dbo].[TransAreaNo]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--实现功能 的函数 将037155611698 转换为 0371-155611698
--######################################################3
CREATE FUNCTION [dbo].[TransAreaNo]
(
	@TelephoneNo VARCHAR(50)
)

RETURNS VARCHAR(50)

AS
BEGIN

    DECLARE @Temp VARCHAR(50)

    set @TelephoneNo=right(@TelephoneNo,len(@TelephoneNo)-1)

    select @Temp=areano from dctelarea with(nolock)
    where areano like left(@TelephoneNo,len(areano))

    set @TelephoneNo=right(@TelephoneNo,len(@TelephoneNo)-len(@Temp))

	RETURN '0'+@Temp+'-'+@TelephoneNo
END

GO

/****** Object:  UserDefinedFunction [dbo].[Trim]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#############################333
--Trim函数
--#############################333
create FUNCTION [dbo].[Trim]
(
	@sText VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	RETURN LTRIM(RTRIM(@sText))
END



GO

/****** Object:  UserDefinedFunction [dbo].[TrimNULL]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#############################333
--Trim函数, 如果=‘’，则为null
--#############################333
CREATE FUNCTION [dbo].[TrimNULL]
(
	@sText VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	SET @sText = LTRIM(RTRIM(@sText))
	IF LEN(@sText) = 0
		SET @sText = NULL
	RETURN @sText
END




GO

/****** Object:  UserDefinedFunction [dbo].[TruncJobIDs]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[TruncJobIDs]
(
	@JobIds AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @i INT, @Num INT, @StrSource VARCHAR(MAX), @s VARCHAR(50)
	SELECT @StrSource = RTRIM(LTRIM(@JobIds))
	SELECT @i = CHARINDEX(',', @StrSource), @Num = 0, @s = ''

	IF @i = 0
		RETURN @StrSource

	WHILE @i >= 1 AND @Num < 3
	BEGIN
		IF LEFT(@StrSource, @i - 1) > '' 
			SELECT @s = @s + ',' + LEFT(@StrSource, @i - 1), @Num = @Num + 1

		SELECT @StrSource = SUBSTRING(@StrSource, @i + 1, LEN(@StrSource) - @i)
		SELECT @i = CHARINDEX(',', @StrSource)
	END

	IF @Num < 3 AND @StrSource <> '' AND @i = 0
		SELECT @s = @s + ',' + @StrSource
	
	IF LEFT(@s, 1) = ','
		SELECT @s = RIGHT(@s, LEN(@s) - 1)
	RETURN @s
END

GO

/****** Object:  UserDefinedFunction [dbo].[v2018_CheckHasCompleteCv]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--####################################################
--添加人：Peter
--添加时间：2018-8-23
--描述：检查个人是否有可以申请职位的家里
--####################################################
CREATE FUNCTION [dbo].[v2018_CheckHasCompleteCv](@PaMainID INT)
RETURNS BIT 
AS
BEGIN
	IF EXISTS(
		SELECT * 
		FROM cvMain WITH(NOLOCK) 
		WHERE paMainID = @PaMainID
			AND (
				(cvLevel LIKE '111__1%' AND VerifyResult = 1) OR
				(cvLevelEng LIKE '111__1%' AND VerifyResultEng = 1)
			)
	)
	BEGIN
		RETURN 1
	END
	ELSE
	BEGIN
		RETURN 0
	END
	RETURN 0
END


GO

/****** Object:  UserDefinedFunction [dbo].[v2018_SafeParam]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#######################################
--添加人：Lucifer
--添加时间：2018-7-27
--用途：过滤不安全的SQL语法
--#######################################
CREATE FUNCTION [dbo].[v2018_SafeParam]
(
	@sText VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	IF @sText IS NULL
		RETURN ''

--	SET @sText = REPLACE(@sText, 'and ','and&nbsp;')
--	SET @sText = REPLACE(@sText, 'or ','or&nbsp;')
--	SET @sText = REPLACE(@sText, 'exists','exists&nbsp;')	
--	SET @sText = REPLACE(@sText, 'union ','union&nbsp;')
--	SET @sText = REPLACE(@sText, '<','＜')
--	SET @sText = REPLACE(@sText, '>','＞')
--	SET @sText = REPLACE(@sText, 'from ','from&nbsp;')
--	SET @sText = REPLACE(@sText, 'select ','select&nbsp;')
	SET @sText = REPLACE(@sText, '','')
	SET @sText = REPLACE(@sText, 'script','scripｔ')
	SET @sText = REPLACE(@sText, 'src','srｃ')
	SET @sText = REPLACE(@sText, 'iframe','ｉframe')
	SET @sText = REPLACE(@sText, '--','——')
	SET @sText = REPLACE(@sText, 'delete ','delete&nbsp;')
	SET @sText = REPLACE(@sText, 'update ','update&nbsp;')
	SET @sText = REPLACE(@sText, 'delete	','delete&nbsp;')
	SET @sText = REPLACE(@sText, 'update	','update&nbsp;')
	SET @sText = REPLACE(@sText, 'delete
','delete&nbsp;')
	SET @sText = REPLACE(@sText, 'update
','update&nbsp;')
	SET @sText = REPLACE(@sText, 'create','create&nbsp;')
	SET @sText = REPLACE(@sText, 'drop','drop&nbsp;')
	SET @sText = REPLACE(@sText, 'insert','insert&nbsp;')
	SET @sText = REPLACE(@sText, 'chr(','chr(&nbsp;')
	SET @sText = REPLACE(@sText, 'master','master&nbsp;')
	SET @sText = REPLACE(@sText, 'net user','net&nbsp;user')
	SET @sText = REPLACE(@sText, 'declare','declare&nbsp;')
	SET @sText = REPLACE(@sText, 'char(','char(&nbsp;')
	SET @sText = REPLACE(@sText, 'mid(','mid(&nbsp;')
	SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell&nbsp;')
	SET @sText = REPLACE(@sText, 'exec','exec&nbsp;')
	SET @sText = REPLACE(@sText, 'truncate','truncate&nbsp;')
	SET @sText = REPLACE(@sText, 'ascii(','ascii(&nbsp;')
	SET @sText = REPLACE(@sText, 'substring(','substring(&nbsp;')
	SET @sText = REPLACE(@sText, 'localgroup ','localgroup&nbsp;')
	SET @sText = REPLACE(@sText, 'administrators ','administrators&nbsp;')
	SET @sText = REPLACE(@sText, 'dbcc','dbcc&nbsp;')
	SET @sText = REPLACE(@sText, 'checkdb(','checkdb(&nbsp;')
	SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember&nbsp;')
	SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell&nbsp;')

	RETURN @sText
END
GO

/****** Object:  UserDefinedFunction [dbo].[v2018_SetIntNull]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--选填的空值，需要改成Null
--######################################################3
CREATE FUNCTION [dbo].[v2018_SetIntNull]
(
	@str INT
)
RETURNS INT
AS
BEGIN
	IF @str = 0
		SET @str = NULL

	RETURN @str
END

GO

/****** Object:  UserDefinedFunction [dbo].[v2018_SetVarcharNull]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--######################################################3
--选填的空值，需要改成Null
--######################################################3
CREATE FUNCTION [dbo].[v2018_SetVarcharNull]
(
	@Str VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	SET @Str = dbo.v2018_SafeParam(@Str)
	IF LEN(ISNULL(@Str, '')) = 0
		SET @Str = NULL

	RETURN @Str
END
GO

/****** Object:  UserDefinedFunction [dbo].[VipJobNumber]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--######################################3
--创建：Sean 2011-01-25
--贵宾会员的职位数量
--######################################3
CREATE FUNCTION [dbo].[VipJobNumber]
(
	@ID INT
)
RETURNS INT
AS
BEGIN
	DECLARE @Number AS INT, @Sn INT
	SELECT @Number = SUM(JobNumber) FROM caOrder WITH(NOLOCK)
	WHERE cpMainID = @ID
	AND BeginDate <= GETDATE() AND EndDate >= CONVERT(SMALLDATETIME, GETDATE())
	AND OpenDate IS NOT NULL

	SET @Sn = 5
	IF EXISTS(SELECT 'x' FROM cpMain WITH(NOLOCK) WHERE Id = @Id AND RealName = 1)
		SET @Sn = 10
	
	RETURN ISNULL(@Number, 0) + @Sn
END



GO

/****** Object:  UserDefinedFunction [dbo].[VipUserNumber]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--######################################3
--创建：Sean 2011-01-25
--贵宾会员的用户数量
--######################################3
CREATE FUNCTION [dbo].[VipUserNumber]
(
	@ID INT
)
RETURNS INT
AS
BEGIN
	DECLARE @Number AS INT
	SELECT @Number = SUM(UserNumber) FROM caOrder WITH(NOLOCK)
	WHERE cpMainID = @ID
	AND BeginDate <= GETDATE() AND EndDate >= CONVERT(SMALLDATETIME, GETDATE())
	AND OpenDate IS NOT NULL
	RETURN ISNULL(@Number, 0) + 1
END






GO

/****** Object:  UserDefinedFunction [dbo].[WorkDateAdd]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  Function [dbo].[WorkDateAdd]
(
	@Date DATETIME,
	@Hours DECIMAL(9, 2)
)
RETURNS DATETIME
AS
BEGIN
	----检查输入参数是否正确
	IF ISNULL(@Hours, 0) = 0 
		RETURN @Date
	IF @Date IS NULL
		RETURN NULL

	DECLARE @Minutes AS INT, @s AS VARCHAR(8), @StartDate AS DATETIME, @CostMinute AS INT
	----获取第一个工作日
	IF NOT EXISTS(SELECT 'x' FROM OaDb..WorkDate WITH(NOLOCK) WHERE WorkDate = CONVERT(VARCHAR(8), @Date, 112) AND WorkType = 1)
	BEGIN
		SELECT TOP 1 @S = a.WorkDate
		FROM OaDb..WorkDate a WITH(NOLOCK) 
		WHERE a.WorkDate > CONVERT(VARCHAR(8), @Date, 112) 
		AND WorkType = 1 
		ORDER BY a.WorkDate
	
		SET @StartDate = @S
	END
	ELSE
		SET @StartDate = CONVERT(DATETIME, CONVERT(VARCHAR(10), @Date, 120))

	----如果当天是工作日，则计算是已运行的工作时间
	IF CONVERT(VARCHAR(8), @Date, 112) = CONVERT(VARCHAR(8), @StartDate, 112)
	BEGIN
		SET @Minutes = DATEPART(HOUR, @Date) * 100 + DATEPART(MINUTE, @Date)
		
		IF @Minutes < 830
			SELECT @CostMinute = 0
		ELSE IF @Minutes < 1201
			SELECT @CostMinute = DATEDIFF(MINUTE, dbo.MergeDate(@Date, 830), @Date)
		ELSE IF @Minutes < 1300
			SELECT @CostMinute = 210
		ELSE IF @Minutes < 1731
			SELECT @CostMinute = DATEDIFF(MINUTE, dbo.MergeDate(@Date, 1300), @Date) + 210
		ELSE		
			SELECT @CostMinute = 480
	END
	ELSE
		SET @CostMinute = 0
	
	SET @Minutes = @Hours * 60 + @CostMinute	----从第一个工作日开始,需要工作的分钟数

	DECLARE @DayInterval AS INT,	--除8等于天数
		@MinuteLast AS INT,
		@ExpireDate AS DATETIME,
		@TempDate AS DATETIME

	SET @DayInterval = @Minutes / 480
	SELECT @MinuteLast = @Minutes - @DayInterval * 480
	SET @DayInterval = @DayInterval + 1

	DECLARE @t AS TABLE(
		ID INT IDENTITY(1,1) NOT NULL,
		WorkDate VARCHAR(8)
	)
	----函数中无法使用临时表和(),所以只有使用表变量
	INSERT INTO @t(WorkDate)
	SELECT TOP (@DayInterval) WorkDate
	FROM OaDb..WorkDate WITH(NOLOCK) 
	WHERE WorkType = 1
	AND WorkDate >= CONVERT(VARCHAR(8), @StartDate, 112)
	ORDER BY WorkDate

	SELECT TOP 1 @S = WorkDate FROM @t ORDER BY ID DESC

	SET @ExpireDate = @S
	IF @MinuteLast > 210
		SET @MinuteLast = @MinuteLast + 60

	SET @ExpireDate = DATEADD(MINUTE, @MinuteLast, dbo.MergeDate(@ExpireDate, 830)) 

	RETURN @ExpireDate
END
GO

/****** Object:  UserDefinedFunction [dbo].[ZhaopinRegionToOurID]    Script Date: 2018/12/13 18:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--sean 2013-6-25
CREATE FUNCTION [dbo].[ZhaopinRegionToOurID]
(
	@ZhaopinRegin VARCHAR(50)
)
RETURNS VARCHAR(6)
AS
BEGIN
	DECLARE @Id VARCHAR(6), @Id2 VARCHAR(6), @Des VARCHAR(20)
	SELECT TOP 1 @id = ID, @Des = DESCRIPTION
	FROM dcRegion WHERE Description LIKE LEFT(@ZhaopinRegin, 2) + '%'
	AND GRADE = 1
	ORDER BY ID
	
	SET @Des = REPLACE(@Des, '市', '')
	SET @Des = REPLACE(@Des, '省', '')
	SET @ZhaopinRegin = REPLACE(@ZhaopinRegin, @Des, '')
	
	SELECT TOP 1 @id2 = ID
	FROM dcRegion 
	WHERE Description LIKE '%' + RIGHT(@ZhaopinRegin, 2) + '%'
	AND ID LIKE @Id + '%'
	ORDER BY Id

	IF @Id2 > ''
		RETURN @ID2
	ELSE
		RETURN @ID
	RETURN ''
END
GO