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,' ','')
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, ''), ' ', ' ')
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:
-- Create date:
-- 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(@IssueDateGETDATE())
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'), '1', '0'), '2', '2'), '3', '3'), '4', '4'), '5', '5'), '6', '6'), '7', '7'), '8', '8'), '9', '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 =
'
个人简历
个人信息
' + ISNULL(@paName, '') + '
' + ISNULL(@Sex, '') + '
' + ISNULL(@BirthDay, '') + '
' + ISNULL(@LivePlace, '') + '
' + ISNULL(@Mobile, '') + '
' + ISNULL(@Email, '') + '
'
IF @Salary > '' AND @jobType > ''
SET @cvText = @cvText + '
求职意向
' + ISNULL(@Career, '') + '
' + ISNULL(@WorkYear, '') + '
' + ISNULL(@Salary, '') + '
' + ISNULL(@jobType, '') + '
' + ISNULL(@WorkPlace, '') + '
' + ISNULL(@Industry, '') + '
'
-----------------------------------
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 + '
教育背景
' + ISNULL(@GraduateCollage, '') + '
' + ISNULL(@Graduation, '') + '
' + ISNULL(@Degree, '') + '
' + ISNULL(@MajorName, '') + '
'
IF LEN(ISNULL(@EduDetail, '')) > 0
SET @cvText = @cvText + '
' + @EduDetail + '
'
SET @cvText = @cvText + '
'
--------------------------------------------------
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 + '
工作经历
' + ISNULL(@CompanyName, '') + '
' + ISNULL(@companyIndustry, '') + '
' + ISNULL(@CompanyPost, '') + '
' + LEFT(@BeginDate, 4) + '年' + RIGHT(@BeginDate, 2) + '月至' + CASE WHEN @EndDate = 999999 THEN '今' ELSE LEFT(@EndDate, 4) + '年' + RIGHT(@EndDate, 2) + '月' END + '
'
IF @CompanyDesc > ''
SET @cvText = @cvText + '
' + @CompanyDesc + '
'
SET @cvText = @cvText + '
'
END
IF @Speciality > ''
SET @cvTExt = @cvText + '
工作能力
' + @Speciality + '
'
SET @CvText = @CvText + '
'
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('
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 ')
SET @sText = REPLACE(@sText, 'update ','update ')
SET @sText = REPLACE(@sText, 'create ','create ')
SET @sText = REPLACE(@sText, 'drop ','drop ')
SET @sText = REPLACE(@sText, 'insert ','insert ')
SET @sText = REPLACE(@sText, 'chr(','chr( ')
SET @sText = REPLACE(@sText, 'master ','master ')
SET @sText = REPLACE(@sText, 'net user','net user')
SET @sText = REPLACE(@sText, 'declare ','declare ')
SET @sText = REPLACE(@sText, 'char(','char( ')
SET @sText = REPLACE(@sText, 'mid(','mid( ')
SET @sText = REPLACE(@sText, 'count ','count ')
SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell ')
SET @sText = REPLACE(@sText, 'exec ','exec ')
SET @sText = REPLACE(@sText, 'truncate ','truncate ')
SET @sText = REPLACE(@sText, 'ascii(','ascii( ')
SET @sText = REPLACE(@sText, 'substring(','substring( ')
SET @sText = REPLACE(@sText, 'localgroup ','localgroup ')
SET @sText = REPLACE(@sText, 'administrators ','administrators ')
SET @sText = REPLACE(@sText, 'dbcc ','dbcc ')
SET @sText = REPLACE(@sText, 'checkdb(','checkdb( ')
SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember ')
SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell ')
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 @IssueDateGETDATE()
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())
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, ' ', ''), ' ', ' ')
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 @adddatecast(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 ')
-- SET @sText = REPLACE(@sText, 'or ','or ')
-- SET @sText = REPLACE(@sText, 'exists','exists ')
-- SET @sText = REPLACE(@sText, 'union ','union ')
-- SET @sText = REPLACE(@sText, '<','<')
-- SET @sText = REPLACE(@sText, '>','>')
-- SET @sText = REPLACE(@sText, 'from ','from ')
-- SET @sText = REPLACE(@sText, 'select ','select ')
SET @sText = REPLACE(@sText, '','')
SET @sText = REPLACE(@sText, 'script','script')
SET @sText = REPLACE(@sText, 'src','src')
SET @sText = REPLACE(@sText, 'iframe','iframe')
SET @sText = REPLACE(@sText, '--','——')
SET @sText = REPLACE(@sText, 'delete ','delete ')
SET @sText = REPLACE(@sText, 'update ','update ')
SET @sText = REPLACE(@sText, 'delete ','delete ')
SET @sText = REPLACE(@sText, 'update ','update ')
SET @sText = REPLACE(@sText, 'delete
','delete ')
SET @sText = REPLACE(@sText, 'update
','update ')
SET @sText = REPLACE(@sText, 'create','create ')
SET @sText = REPLACE(@sText, 'drop','drop ')
SET @sText = REPLACE(@sText, 'insert','insert ')
SET @sText = REPLACE(@sText, 'chr(','chr( ')
SET @sText = REPLACE(@sText, 'master','master ')
SET @sText = REPLACE(@sText, 'net user','net user')
SET @sText = REPLACE(@sText, 'declare','declare ')
SET @sText = REPLACE(@sText, 'char(','char( ')
SET @sText = REPLACE(@sText, 'mid(','mid( ')
SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell ')
SET @sText = REPLACE(@sText, 'exec','exec ')
SET @sText = REPLACE(@sText, 'truncate','truncate ')
SET @sText = REPLACE(@sText, 'ascii(','ascii( ')
SET @sText = REPLACE(@sText, 'substring(','substring( ')
SET @sText = REPLACE(@sText, 'localgroup ','localgroup ')
SET @sText = REPLACE(@sText, 'administrators ','administrators ')
SET @sText = REPLACE(@sText, 'dbcc','dbcc ')
SET @sText = REPLACE(@sText, 'checkdb(','checkdb( ')
SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember ')
SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell ')
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 ')
-- SET @sText = REPLACE(@sText, 'or ','or ')
-- SET @sText = REPLACE(@sText, 'from ','from ')
-- SET @sText = REPLACE(@sText, 'exists','exists ')
-- SET @sText = REPLACE(@sText, 'select ','select ')
-- SET @sText = REPLACE(@sText, 'union ','union ')
-- SET @sText = REPLACE(@sText, '<','<')
-- SET @sText = REPLACE(@sText, '>','>')
SET @sText = REPLACE(@sText, '','')
SET @sText = REPLACE(@sText, 'script','script')
SET @sText = REPLACE(@sText, 'src','src')
SET @sText = REPLACE(@sText, 'iframe','iframe')
SET @sText = REPLACE(@sText, '--','——')
SET @sText = REPLACE(@sText, 'delete ','delete ')
SET @sText = REPLACE(@sText, 'update ','update ')
SET @sText = REPLACE(@sText, 'delete ','delete ')
SET @sText = REPLACE(@sText, 'update ','update ')
SET @sText = REPLACE(@sText, 'delete
','delete ')
SET @sText = REPLACE(@sText, 'update
','update ')
SET @sText = REPLACE(@sText, 'create ','create ')
SET @sText = REPLACE(@sText, 'drop ','drop ')
SET @sText = REPLACE(@sText, 'insert ','insert ')
SET @sText = REPLACE(@sText, 'chr(','chr( ')
SET @sText = REPLACE(@sText, 'master ','master ')
SET @sText = REPLACE(@sText, 'net user','net user')
SET @sText = REPLACE(@sText, 'declare ','declare ')
SET @sText = REPLACE(@sText, 'char(','char( ')
SET @sText = REPLACE(@sText, 'mid(','mid( ')
--SET @sText = REPLACE(@sText, 'count ','count ')
SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell ')
SET @sText = REPLACE(@sText, 'exec ','exec ')
SET @sText = REPLACE(@sText, 'truncate ','truncate ')
SET @sText = REPLACE(@sText, 'ascii(','ascii( ')
SET @sText = REPLACE(@sText, 'substring(','substring( ')
SET @sText = REPLACE(@sText, 'localgroup ','localgroup ')
SET @sText = REPLACE(@sText, 'administrators ','administrators ')
SET @sText = REPLACE(@sText, 'dbcc ','dbcc ')
SET @sText = REPLACE(@sText, 'checkdb(','checkdb( ')
SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember ')
SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell ')
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 0
SET @Content = @Content + ' 您总共收到了' + 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.AddDateCONVERT(SMALLDATETIME,@StartTime)
IF @Count > 0
SET @Content = @Content + ' 有' + 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 + ' 您的订单内容为申请' + @tmp@ServiceType + '正式会员,费用为' + convert(varchar(10),@Paid) + '元,'
If @OrderType = 2
SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为申请储值会员,加值金额为' + convert(varchar(10),@Paid) + '元,'
If @OrderType = 4
SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为申请广告位,费用为' + convert(varchar(10),@Paid) + '元,'
If @OrderType = 7
SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为首页知名企业招聘,费用为' + convert(varchar(10),@Paid) + '元,'
If @OrderType = 8
SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为申请' + @TimeTitle + 'VIP套餐服务,费用为' + convert(varchar(10),@Paid) + '元,'
If @OrderType = 9
SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为购买' + LTRIM(STR(@ActiveQuota)) + '个简历下载数,费用为' + convert(varchar(10),@Paid) + '元,'
If @OrderType = 10
SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为购买' + LTRIM(STR(@JobNumber)) + '个职位并发数,费用为' + convert(varchar(10),@Paid) + '元,'
If @OrderType = 11
SET @strOrderDesc = @strOrderDesc + ' 您的订单内容为购买' + LTRIM(STR(@UserNumber)) + '个用户数,费用为' + convert(varchar(10),@Paid) + '元,'
SET @strOrderDesc = @strOrderDesc + '为方便您能够及时开通服务,现将付款流程发送给您,如下: '
If @PayMethod = 1
Begin
SET @strOrderDesc = @strOrderDesc + '1、对公汇款 '
SET @strOrderDesc = @strOrderDesc + ' 收款人:' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + ' '
SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + ' '
SET @strOrderDesc = @strOrderDesc + ' 汇款用途:会员服务费,订单号' + LTRIM(STR(@OrderID)) + ' '
SET @strOrderDesc = @strOrderDesc + '2、将银行电汇回单传真至:010-65800851 或者 021-61916451 '
SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在银行电汇回单传真、营业执照齐全后',@NeedInvoice)
End
If @PayMethod = 2
BEGIN
SET @strOrderDesc = @strOrderDesc + '1、邮局汇款 '
SET @strOrderDesc = @strOrderDesc + ' 收款人:' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 通信地址:' + @BankName + ' '
SET @strOrderDesc = @strOrderDesc + ' 邮政编码:' + @Account + ' '
SET @strOrderDesc = @strOrderDesc + ' 附言:订单号' + LTRIM(STR(@OrderID)) + ' '
SET @strOrderDesc = @strOrderDesc + '2、在邮政汇款收据上,标上订单号,并传真至:010-65800851 或者 021-61916451 '
SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在邮政汇款收据传真、营业执照齐全后',@NeedInvoice)
End
If @PayMethod = 6
BEGIN
SET @strOrderDesc = @strOrderDesc + '请前来付费: '
SET @strOrderDesc = @strOrderDesc + ' ' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 地址:' + @BankName + ' '
End
If @PayMethod = 7
BEGIN
SET @strOrderDesc = @strOrderDesc + '1、工商银行 '
SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + ' '
SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + ' '
SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。 '
SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
End
If @PayMethod = 9
BEGIN
SET @strOrderDesc = @strOrderDesc + '1、招商银行 '
SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + ' '
SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + ' '
SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。 '
SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
End
If @PayMethod = 10
BEGIN
SET @strOrderDesc = @strOrderDesc + '1、农业银行 '
SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + ' '
SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + ' '
SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。 '
SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
End
If @PayMethod = 19
BEGIN
SET @strOrderDesc = @strOrderDesc + '1、支付宝支付(支持全国银行卡,网上付款,自动开通) '
SET @strOrderDesc = @strOrderDesc + ' 请到网站在线支付,'
If @OrderType = 1 Or @OrderType = 2 Or @OrderType = 5 Or @OrderType = 8 Or @OrderType = 9 Or @OrderType = 10 Or @OrderType = 11
SET @strOrderDesc = @strOrderDesc + '支付完成后,自动开通。' + ' '
If @OrderType = 4
SET @strOrderDesc = @strOrderDesc + '支付完成后,工作日10分钟之内我们电话与您联系广告制作与播放。' + ' '
If @OrderType = 7
SET @strOrderDesc = @strOrderDesc + '4、确认收到汇款后,工作日10分钟之内您的单位名称出现在“首页知名企业招聘”。' + ' '
If @NeedInvoice = 1
SET @strOrderDesc = @strOrderDesc + '汇款到帐后您可以索取发票,在三个工作日内,将您的发票挂号信寄出。 '
End
If @PayMethod = 12
BEGIN
SET @strOrderDesc = @strOrderDesc + '1、建设银行 '
SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + ' '
SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + ' '
SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。 '
SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
End
If @PayMethod = 13
BEGIN
SET @strOrderDesc = @strOrderDesc + '1、中国银行 '
SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + ' '
SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + ' '
SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。 '
SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
End
If @PayMethod = 14
BEGIN
SET @strOrderDesc = @strOrderDesc + '1、交通银行 '
SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + ' '
SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + ' '
SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。 '
SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在营业执照齐全,并确认收到汇款后',@NeedInvoice)
End
If @PayMethod = 17
BEGIN
SET @strOrderDesc = @strOrderDesc + '1、转帐支票 '
SET @strOrderDesc = @strOrderDesc + ' 收款人:' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + ' '
SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + ' '
SET @strOrderDesc = @strOrderDesc + ' 汇款用途:会员服务费,订单号' + LTRIM(STR(@OrderID)) + ' '
SET @strOrderDesc = @strOrderDesc + '2、您可以将支票快递给我们,也可以将支票亲自送过来。 '
SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在收到转帐支票、营业执照齐全后',@NeedInvoice)
End
If @PayMethod = 18
BEGIN
SET @strOrderDesc = @strOrderDesc + '1、同城转帐 '
SET @strOrderDesc = @strOrderDesc + ' 收款人:' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + ' '
SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + ' '
SET @strOrderDesc = @strOrderDesc + ' 汇款用途:会员服务费,订单号' + LTRIM(STR(@OrderID)) + ' '
SET @strOrderDesc = @strOrderDesc + '2、将同城转帐回单传真至:010-65800851 或者 021-61916451 '
SET @strOrderDesc = @strOrderDesc + dbo.schGetPayDesc(@OrderType,'在同城转帐回单传真、营业执照齐全后',@NeedInvoice)
End
If @PayMethod = 20
BEGIN
SET @strOrderDesc = @strOrderDesc + '1、邮政储蓄银行 '
SET @strOrderDesc = @strOrderDesc + ' 收款人全称:' + @Receiver + ' '
SET @strOrderDesc = @strOrderDesc + ' 帐号:' + @Account + ' '
SET @strOrderDesc = @strOrderDesc + ' 开户银行:' + @BankName + ' '
SET @strOrderDesc = @strOrderDesc + '2、汇款成功后,请立即打电话或者发邮件通知我们订单号以及汇款金额。 '
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 '
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通知。 '
If @OrderType = 4
SET @PayDesc = @PayDesc + '4、' + @strDesc + ',工作日10分钟之内我们电话与您联系广告制作与播放。 '
If @OrderType = 7
SET @PayDesc = @PayDesc + '4、确认收到汇款后,工作日10分钟之内您的单位名称出现在“首页知名企业招聘”。 '
If @NeedInvoice = 1
SET @PayDesc = @PayDesc + '5、汇款到帐后您可以索取发票,在三个工作日内,将您的发票挂号信寄出。 '
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, ' ', ' ')
SET @HTMLText = REPLACE(@HTMLText, ' ', ' ')
--SET @HTMLText = REPLACE(LTRIM(RTRIM(@HTMLText)), ' ', ' ')
SET @HTMLText = REPLACE(@HTMLText, ' '+Char(13)+Char(10), Char(13)+Char(10))
SET @HTMLText = REPLACE(@HTMLText, ' '+Char(13)+Char(10), Char(13)+Char(10))
SET @HTMLText = REPLACE(@HTMLText, ' '+Char(13)+Char(10), Char(13)+Char(10))
--SET @HTMLText = REPLACE(@HTMLText, '
'+Char(13)+Char(10), '')
--SET @HTMLText = REPLACE(@HTMLText, ''+Char(13)+Char(10), '')
SET @HTMLText = REPLACE(@HTMLText, ' '+Char(13), Char(13)+Char(10))
SET @HTMLText = REPLACE(@HTMLText, ' '+Char(13), Char(13)+Char(10))
SET @HTMLText = REPLACE(@HTMLText, ' '+Char(13), Char(13)+Char(10))
SET @HTMLText = REPLACE(@HTMLText, ' '+Char(10), Char(13)+Char(10))
SET @HTMLText = REPLACE(@HTMLText, ' '+Char(10), Char(13)+Char(10))
SET @HTMLText = REPLACE(@HTMLText, ' '+Char(10), Char(13)+Char(10))
--SET @HTMLText = REPLACE(@HTMLText, '
'+Char(10), '')
--SET @HTMLText = REPLACE(@HTMLText, ''+Char(10), '')
SET @HTMLText = REPLACE(@HTMLText, ' ', Char(13)+Char(10))
SET @HTMLText = REPLACE(@HTMLText, ' ', Char(13)+Char(10))
SET @HTMLText = REPLACE(@HTMLText, ' ', Char(13)+Char(10))
--SET @HTMLText = REPLACE(@HTMLText, '', '')
--SET @HTMLText = REPLACE(@HTMLText, '', '')
/*
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, '<', '<')
SET @HTMLText = REPLACE(@HTMLText, '>', '>')
*/
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), ' ', ' ')
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 ')
-- SET @sText = REPLACE(@sText, 'or ','or ')
-- SET @sText = REPLACE(@sText, 'exists','exists ')
-- SET @sText = REPLACE(@sText, 'union ','union ')
-- SET @sText = REPLACE(@sText, '<','<')
-- SET @sText = REPLACE(@sText, '>','>')
-- SET @sText = REPLACE(@sText, 'from ','from ')
-- SET @sText = REPLACE(@sText, 'select ','select ')
SET @sText = REPLACE(@sText, '','')
SET @sText = REPLACE(@sText, 'script','script')
SET @sText = REPLACE(@sText, 'src','src')
SET @sText = REPLACE(@sText, 'iframe','iframe')
SET @sText = REPLACE(@sText, '--','——')
SET @sText = REPLACE(@sText, 'delete ','delete ')
SET @sText = REPLACE(@sText, 'update ','update ')
SET @sText = REPLACE(@sText, 'delete ','delete ')
SET @sText = REPLACE(@sText, 'update ','update ')
SET @sText = REPLACE(@sText, 'delete
','delete ')
SET @sText = REPLACE(@sText, 'update
','update ')
SET @sText = REPLACE(@sText, 'create','create ')
SET @sText = REPLACE(@sText, 'drop','drop ')
SET @sText = REPLACE(@sText, 'insert','insert ')
SET @sText = REPLACE(@sText, 'chr(','chr( ')
SET @sText = REPLACE(@sText, 'master','master ')
SET @sText = REPLACE(@sText, 'net user','net user')
SET @sText = REPLACE(@sText, 'declare','declare ')
SET @sText = REPLACE(@sText, 'char(','char( ')
SET @sText = REPLACE(@sText, 'mid(','mid( ')
SET @sText = REPLACE(@sText, 'xp_cmdshell','xp_cmdshell ')
SET @sText = REPLACE(@sText, 'exec','exec ')
SET @sText = REPLACE(@sText, 'truncate','truncate ')
SET @sText = REPLACE(@sText, 'ascii(','ascii( ')
SET @sText = REPLACE(@sText, 'substring(','substring( ')
SET @sText = REPLACE(@sText, 'localgroup ','localgroup ')
SET @sText = REPLACE(@sText, 'administrators ','administrators ')
SET @sText = REPLACE(@sText, 'dbcc','dbcc ')
SET @sText = REPLACE(@sText, 'checkdb(','checkdb( ')
SET @sText = REPLACE(@sText, 'sp_addsrvrolemember','p_addsrvrolemember ')
SET @sText = REPLACE(@sText, 'wscript.shell','wscript.shell ')
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