qlrc_database/maindb-procedure-1.sql

USE [MainDB]
GO

/****** Object:  StoredProcedure [dbo].[agentAddLimitLogin]    Script Date: 2018/12/13 19:03:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[agentAddLimitLogin]
(
	@Key AS VARCHAR(100)
)
AS
--INSERT INTO procLog SELECT 'agentAddLimitLogin', '', GETDATE()
BEGIN
	--sean update at 2015-4-22, update the VerifyInfo text
	--INSERT bsCpStatus(cpMainId, StatusId, VerifyInfo, VerifyMan, VerifyDate)
	--SELECT ID, 9, '由于保险公司被投诉比例上升迅速，我们暂时对保险公司数量进行限制。只有交费企业会员才能正常使用，如需办理交费会员请致电网站客服400-626-5151联系相关事宜，谢谢合作！您的会员编号是' + LTRIM(STR(ID)), 1, GETDATE()
	INSERT bsCpStatus(cpMainId, StatusId, VerifyInfo, VerifyMan, VerifyDate)
	SELECT ID, 9, '您好！您在' + (SELECT TOP 1 WebSiteName FROM dcProvince WHERE Id = dcProvinceId) +
		 '的帐号需要付费使用，详情可致电4006265151联系' + CASE WHEN ConsultantId BETWEEN 600 AND 699 THEN '5' ELSE '6' END + LTRIM(STR(ISNULL(ConsultantId, 0))) + '号顾问，祝您招聘顺利！', 1, GETDATE()
	FROM cpMain WITH(NOLOCK) WHERE HasLicence = 1
	AND Name LIKE '%' + @Key + '%'
	AND IsLimitLogin < 9
	AND MemberType < 3

	IF GETDATE() < '2017-8-26'
		UPDATE cpMain 
		SET IsLimitLogin = 9
		WHERE HasLicence = 1
		AND Name LIKE '%' + @Key + '%'
		AND IsLimitLogin < 9
		AND MemberType < 3
	ELSE
		UPDATE cpMain 
		SET IsLimitLogin = 9,
			RealName = 6
		WHERE HasLicence = 1
		AND Name LIKE '%' + @Key + '%'
		AND IsLimitLogin < 9
		AND MemberType < 3
END
GO

/****** Object:  StoredProcedure [dbo].[BackDataUpdate]    Script Date: 2018/12/13 19:03:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[BackDataUpdate]
(
	@ManagerID INT,
	@Pass INT
)
AS
SET NOCOUNT ON
BEGIN TRAN
	DECLARE @ID AS INT
	BEGIN TRY
		SET @ID=0
		SELECT TOP 1 @ID=ID FROM backdata WITH(NOLOCK) WHERE
			ManagerID=@ManagerID AND CountDate=YEAR(GETDATE())*10000+MONTH(GETDATE())*100+DAY(GETDATE())
		IF @ID=0
			INSERT INTO backdata(ManagerID,CountDate) VALUES(
				@ManagerID,YEAR(GETDATE())*10000+MONTH(GETDATE())*100+DAY(GETDATE())
			)
		
		IF @Pass=1	
			UPDATE backdata SET resume_pass=resume_pass + 1 WHERE
				ManagerID=@ManagerID AND CountDate=YEAR(GETDATE())*10000+MONTH(GETDATE())*100+DAY(GETDATE())
		IF @Pass=10
			UPDATE backdata SET resume_nopass=resume_nopass + 1 WHERE
				ManagerID=@ManagerID AND CountDate=YEAR(GETDATE())*10000+MONTH(GETDATE())*100+DAY(GETDATE())
	END TRY
	BEGIN CATCH
		GOTO ERR
	END CATCH
COMMIT TRAN
RETURN 1
ERR:
BEGIN
	ROLLBACK TRAN
	RETURN 0
END













GO

/****** Object:  StoredProcedure [dbo].[bpActivationCpDetailByCountSelect]    Script Date: 2018/12/13 19:03:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--创建人 Andy
--时间 2015-01-23
--按照条件查询企业激活个数
--bpActivationCpDetailByCountSelect 1,1,'','2013-1-31 13:41:00','2013-1-31 13:41:00'
CREATE PROCEDURE [dbo].[bpActivationCpDetailByCountSelect] 
(		
	@AddManOld INT,
	@AddManNew INT,
	@RegDateOld VARCHAR(10),
	@RegDateNewBegin VARCHAR(10),
	@RegDateNewEnd VARCHAR(10)
)
AS
BEGIN		
	DECLARE @SQL VARCHAR(1000)	
	SET @SQL = 'SELECT COUNT(*) allCount FROM LogDb..ActivationCpDetail WHERE 1=1'	
	IF @AddManOld > 0	
		SET @SQL = @SQL + ' And AddManOld = ' + LTRIM(STR(@AddManOld))
	IF @AddManNew > 0	
		SET @SQL = @SQL + ' And AddManNew = ' + LTRIM(STR(@AddManNew))	
	IF LEN(@RegDateOld) > 0
		BEGIN
			DECLARE @RegDateOldBegin DATETIME
			DECLARE @RegDateOldEnd DATETIME
			SET @RegDateOldBegin = CONVERT(DATETIME, @RegDateOld, 120)
			SET @RegDateOldEnd = @RegDateOldBegin + 1			
			SET @SQL = @SQL + ' And RegDateOld BETWEEN ''' + @RegDateOld + ''' AND ''' + CONVERT(VARCHAR(10), @RegDateOldEnd + 1, 120) + ''''
		END
	IF LEN(@RegDateNewBegin) > 0
		SET @SQL = @SQL + ' And RegDateNew >= ''' + CONVERT(VARCHAR(10), @RegDateNewBegin, 120) + ''''		
	IF LEN(@RegDateNewEnd) > 0
		SET @SQL = @SQL + ' And RegDateNew <= ''' + CONVERT(VARCHAR(10), @RegDateNewEnd, 120) + ''''			  

	--PRINT(@SQL)
	EXEC (@SQL)
END

GO

/****** Object:  StoredProcedure [dbo].[bpActivationCpDetailSelect]    Script Date: 2018/12/13 19:03:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--创建人 Andy
--时间 2015-01-23
--按照条件查询企业激活日志
--bpActivationCpDetailSelect 1,1,'2013-1-31 13:41:00','','2013-1-31 13:41:00'
CREATE PROCEDURE [dbo].[bpActivationCpDetailSelect] 
(		
	@AddManOld INT,
	@AddManNew INT,
	@RegDateOld VARCHAR(10),
	@RegDateNewBegin VARCHAR(10),
	@RegDateNewEnd VARCHAR(10)
)
AS
BEGIN		
	DECLARE @SQL VARCHAR(1000)	
	SET @SQL = '
		SELECT a.*,b.Name NameOld, c.Name NameNew 
		FROM LogDb..ActivationCpDetail a  
		JOIN ManagerUser b ON a.addmanold = b.id   
		JOIN ManagerUser c ON a.addmannew = c.id 
		Where 1 = 1'	
	IF @AddManOld > 0	
		SET @SQL = @SQL + ' And AddManOld = ' + LTRIM(STR(@AddManOld))
	IF @AddManNew > 0	
		SET @SQL = @SQL + ' And AddManNew = ' + LTRIM(STR(@AddManNew))	
	IF LEN(@RegDateOld) > 0
		BEGIN
			DECLARE @RegDateOldBegin DATETIME
			DECLARE @RegDateOldEnd DATETIME
			SET @RegDateOldBegin = CONVERT(DATETIME, @RegDateOld, 120)
			SET @RegDateOldEnd = @RegDateOldBegin + 1	
			SET @SQL = @SQL + ' And RegDateOld BETWEEN ''' + @RegDateOld + ''' AND ''' + CONVERT(VARCHAR(10), @RegDateOldEnd + 1, 120) + ''''
		END
	IF LEN(@RegDateNewBegin) > 0
		SET @SQL = @SQL + ' And RegDateNew >= ''' + CONVERT(VARCHAR(10), @RegDateNewBegin, 120) + ''''
	IF LEN(@RegDateNewEnd) > 0
		SET @SQL = @SQL + ' And RegDateNew <= ''' + CONVERT(VARCHAR(10), @RegDateNewEnd, 120) + ''''		
			  
	SET @SQL = @SQL + ' Order By ID DESC'
	--PRINT(@SQL)
	EXEC (@SQL)
END
GO

/****** Object:  StoredProcedure [dbo].[bpActivationCvDetailByCountSelect]    Script Date: 2018/12/13 19:03:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--创建人 Andy
--时间 2015-01-23
--按照条件个人激活的个数
--bpActivationCvDetailByCountSelect 0,0,'2013-1-31','2013-1-31 13:41:00','2013-3-31 13:41:00'
CREATE PROCEDURE [dbo].[bpActivationCvDetailByCountSelect] 
(		
	@AddManOld INT,
	@AddManNew INT,
	@RegDateOld VARCHAR(10),
	@RegDateNewBegin VARCHAR(10),
	@RegDateNewEnd VARCHAR(10)	
)
AS
BEGIN		
	DECLARE @SQL VARCHAR(1000)	
	SET @SQL = '
		SELECT COUNT(*) allCount FROM LogDb..ActivationCvDetail WHERE 1=1 '		
	IF @AddManOld > 0	
		SET @SQL = @SQL + ' And AddManOld = ' + LTRIM(STR(@AddManOld))
	IF @AddManNew > 0	
		SET @SQL = @SQL + ' And AddManNew = ' + LTRIM(STR(@AddManNew))	
	IF LEN(@RegDateOld) > 0
		BEGIN
			DECLARE @RegDateOldBegin DATETIME
			DECLARE @RegDateOldEnd DATETIME
			SET @RegDateOldBegin = CONVERT(DATETIME, @RegDateOld, 120)
			SET @RegDateOldEnd = @RegDateOldBegin + 1			
			SET @SQL = @SQL + ' And RegDateOld BETWEEN ''' + @RegDateOld + ''' AND ''' + CONVERT(VARCHAR(10), @RegDateOldEnd + 1, 120) + ''''
		END		
	IF LEN(@RegDateNewBegin) > 0
		SET @SQL = @SQL + ' And RegDateNew >= ''' + @RegDateNewBegin + ''''		
	IF LEN(@RegDateNewEnd) > 0
		SET @SQL = @SQL + ' And RegDateNew <= ''' + @RegDateNewEnd + ''''			  
	
	--PRINT(@SQL)
	EXEC (@SQL)
END

GO

/****** Object:  StoredProcedure [dbo].[bpActivationCvDetailSelect]    Script Date: 2018/12/13 19:03:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--创建人 Andy
--时间 2015-01-23
--按照条件查询个人激活日志
--bpActivationCvDetailSelect 0,0,'2013-1-31','2013-1-31 13:41:00','2013-3-31 13:41:00'
CREATE PROCEDURE [dbo].[bpActivationCvDetailSelect] 
(		
	@AddManOld INT,
	@AddManNew INT,
	@RegDateOld VARCHAR(10),
	@RegDateNewBegin VARCHAR(10),
	@RegDateNewEnd VARCHAR(10)	
)
AS
BEGIN		
	DECLARE @SQL VARCHAR(1000)	
	SET @SQL = '
		SELECT TOP 2000 a.*, b.Name NameOld, c.Name NameNew 
		FROM LogDb..ActivationCvDetail a  
		JOIN ManagerUser b ON a.addmanold = b.id   
		JOIN ManagerUser c ON a.addmannew = c.id 
		Where 1 = 1 '		
	IF @AddManOld > 0	
		SET @SQL = @SQL + ' And AddManOld = ' + LTRIM(STR(@AddManOld))
	IF @AddManNew > 0	
		SET @SQL = @SQL + ' And AddManNew = ' + LTRIM(STR(@AddManNew))	
	IF LEN(@RegDateOld) > 0
		BEGIN
			DECLARE @RegDateOldBegin DATETIME
			DECLARE @RegDateOldEnd DATETIME
			SET @RegDateOldBegin = CONVERT(DATETIME, @RegDateOld, 120)
			SET @RegDateOldEnd = @RegDateOldBegin + 1			
			SET @SQL = @SQL + ' And RegDateOld BETWEEN ''' + @RegDateOld + ''' AND ''' + CONVERT(VARCHAR(10), @RegDateOldEnd + 1, 120) + ''''
		END				
	IF LEN(@RegDateNewBegin) > 0
		SET @SQL = @SQL + ' And RegDateNew >= ''' + @RegDateNewBegin + ''''		
	IF LEN(@RegDateNewEnd) > 0
		SET @SQL = @SQL + ' And RegDateNew <= ''' + @RegDateNewEnd + ''''
			  
	SET @SQL = @SQL + ' Order By ID DESC'
	--PRINT(@SQL)
	EXEC (@SQL)
END

GO

/****** Object:  StoredProcedure [dbo].[bpActivationLogBySumSelect]    Script Date: 2018/12/13 19:03:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--bpActivationLogBySumSelect 1,'',''
--创建人 Andy
--时间 2015-01-23
--信息员注册信息统计
create PROCEDURE [dbo].[bpActivationLogBySumSelect] 
(		
	@AddMan INT,	
	@CountDateBegin VARCHAR(8),	--格式：20120101
	@CountDateEnd VARCHAR(8)		--格式：20120101
)
AS
BEGIN		
	SET @CountDateBegin = dbo.SafeSql(@CountDateBegin)
	SET @CountDateEnd = dbo.SafeSql(@CountDateEnd)
	
	DECLARE @SQL VARCHAR(1000)	
	SET @SQL = '
			SELECT SUM(CvCount) CvCount, SUM(AllPerson) AllPerson, SUM(CpCount) CpCount, SUM(AllCompany) AllCompany
			FROM LogDb..ActivationLog a 
			JOIN ( 
				SELECT CountDate, InfoManager_Id, SUM(Companyregnum_Our) AllCompany, SUM(Personregnum_Our) AllPerson 
				FROM info_datereport
				GROUP BY CountDate, InfoManager_Id) b 
			ON a.Countdate = b.Countdate AND a.AddMan = b.InfoManager_Id  
			JOIN ManagerUser c 
			ON a.AddMan = c.id 
			WHERE 1 = 1 '		
	IF @AddMan > 0	
		SET @SQL = @SQL + ' And AddMan = ' + LTRIM(STR(@AddMan))		
	IF LEN(@CountDateBegin) > 0
		SET @SQL = @SQL + ' And a.CountDate >= ''' + @CountDateBegin + ''''		
	IF LEN(@CountDateEnd) > 0
		SET @SQL = @SQL + ' And a.CountDate <= ''' + @CountDateEnd + ''''			  
	
	--PRINT(@SQL)
	EXEC (@SQL)
END
GO

/****** Object:  StoredProcedure [dbo].[bpActivationLogSelect]    Script Date: 2018/12/13 19:03:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--bpActivationLogByAddManSelect 0,'20150111','20130131'
--创建人 Andy
--时间 2015-01-23
--信息员注册信息查询
create PROCEDURE [dbo].[bpActivationLogSelect] 
(		
	@AddMan INT,	
	@CountDateBegin VARCHAR(8),	--格式：20120101
	@CountDateEnd VARCHAR(8)		--格式：20120101
)
AS
BEGIN		
	SET @CountDateBegin = dbo.SafeSql(@CountDateBegin)
	SET @CountDateEnd = dbo.SafeSql(@CountDateEnd)
	
	DECLARE @SQL VARCHAR(1000)	
	SET @SQL = '
			SELECT TOP 2000 a.*, b.*, c.name 
			FROM LogDb..ActivationLog a 
			JOIN ( 
				SELECT CountDate, InfoManager_Id, SUM(Companyregnum_Our) AllCompany, SUM(Personregnum_Our) AllPerson 
				FROM info_datereport 
				GROUP BY CountDate, InfoManager_Id) b 
			ON a.Countdate = b.Countdate AND a.AddMan = b.InfoManager_Id  
			JOIN ManagerUser c 
			ON a.Addman=c.id WHERE 1 = 1 '		
	IF @AddMan > 0	
		SET @SQL = @SQL + ' And AddMan = ' + LTRIM(STR(@AddMan))		
	IF LEN(@CountDateBegin) > 0
		SET @SQL = @SQL + ' And a.CountDate >= ''' + @CountDateBegin + ''''		
	IF LEN(@CountDateEnd) > 0
		SET @SQL = @SQL + ' And a.CountDate <= ''' + @CountDateEnd + ''''			  
	SET @SQL = @SQL + ' ORDER BY a.CountDate DESC, Addman ASC'
	--PRINT(@SQL)
	EXEC (@SQL)
END

GO

/****** Object:  StoredProcedure [dbo].[bpAdverprojectByCompany_DeletedSelect]    Script Date: 2018/12/13 19:03:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--###########################
--创建时间：2014.12.10
--创建人：Nick
--说明：删除单位发送通知
--###########################
CREATE PROCEDURE [dbo].[bpAdverprojectByCompany_DeletedSelect]
(
	@cpMainID INT
)
AS
INSERT INTO procLog SELECT 'bpAdverprojectByCompany_DeletedSelect', '', GETDATE()
BEGIN 
	SELECT TOP 10 b.ProvinceName, b.WebSiteName, b.ID, c.HandleMan 
	FROM adverproject a, dcprovince b, Company_Deleted c 
	WHERE a.province_id = b.id 
	AND a.Companyid = c.id 
	AND a.Endtime > GETDATE() 
	AND CompanyId = @cpMainID
END
GO

/****** Object:  StoredProcedure [dbo].[bpAdverProjectSubmitUpdate]    Script Date: 2018/12/13 19:03:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Mice 123>
-- Create date: <2014-11-20>
-- Description:	<保存广告计划管理记录>
-- Edit:2014-11-21 Mice 增加参数@innews,@imagefilesmall
--      修改 @pids 为 varchar类型， 传入参数可为（31,32,33,36,83）
--2018-04-16 Carl 修改 增添字段 isSelf
-- =============================================
CREATE PROCEDURE [dbo].[bpAdverProjectSubmitUpdate] 
(
	@id INT,
	@pids varchar(200),
	@type INT,
	@innews INT,
	@begintime VARCHAR(50),
	@endtime VARCHAR(50),
	@width INT,
	@height INT,
	@imagefile VARCHAR(50),
	@imagefilesmall VARCHAR(50),
	@orderby INT,
	@url VARCHAR(200),
	@companyid INT,
	@caorderid INT ,
	@description VARCHAR(200),
	@remarks VARCHAR(200),
	@addman INT,
	@isSelf BIT 
)
AS
INSERT INTO procLog SELECT 'bpAdverProjectSubmitUpdate', '', GETDATE()
BEGIN TRAN
	BEGIN TRY
		DECLARE @result AS INT
		SET @result=0

		IF @companyid > 0
		BEGIN
			IF NOT EXISTS( SELECT 'X' FROM MAINDB..cpmain WITH(NOLOCK)  WHERE ID=@companyid )
			BEGIN
				SET @result = -1 --找不到相关公司ID
				GOTO ERR
			END
		END

		IF @caorderid > 0 
		BEGIN
			IF NOT EXISTS( SELECT 'X' FROM MAINDB..caOrder WITH(NOLOCK)  WHERE ordertype=4 AND opendate IS NULL  AND id= @caorderid )
			BEGIN
				SET @result=-2 --找不到相关订单号，或者订单类型不对，或者订单已经开通
				GOTO ERR
			END
		END
		
		IF Len(@url)=0
			SET @url = NULL
		IF @companyid = 0
			SET @companyid = NULL
		IF @caorderid = 0
			SET  @caorderid = NULL
		IF Len(@description)=0
			SET @description = NULL
		IF @type <> 16
			SET @innews = NULL
		IF @type <> 14
			SET @imagefilesmall = NULL
		IF @width = 0
			SET  @width = NULL
		IF @height = 0
			SET  @height = NULL		

		--保存
		IF @id = 0 --新增保存
		BEGIN
			INSERT INTO  maindb..adverproject ([type],valid, innews, province_id,begintime,endtime,width,height,imagefile,orderby,url,companyid,caorderid,[description],remarks,addman,ImageFileSmall,IsSelf)
			SELECT @type, 1, @innews, a, @begintime,@endtime,@width,@height,@imagefile,@orderby,@url,@companyid,@caorderid,@description,@remarks,@addman,@imagefilesmall,@isSelf FROM dbo.fnsplit(@pids,',')
			
			IF @caorderid IS NOT NULL 
			BEGIN
				--修改订单表的开始时间和结束时间
				UPDATE maindb..caOrder SET OpenDate=GetDate(),OpenMan=@addman, BeginDate=@begintime, EndDate=@endtime WHERE ID=@caorderid
				
				DECLARE @Paid AS INT
				SELECT @Paid = Paid   FROM maindb..caOrder WITH(NOLOCK) WHERE id = @caorderid	
				IF @Paid = 0 --如果金额为0则自动款到账
					UPDATE maindb..caOrder SET ReceiveDate=GetDate(),ReceiveMan = @addman WHERE id = @caorderid	
			END
		END
		ELSE --修改保存
		BEGIN
			UPDATE maindb..adverproject SET begintime=@begintime, endtime=@endtime, imagefile=@imagefile, remarks=@remarks, province_id=@pids,width=@width, height=@height, orderby=@orderby, url=@url, companyid=@companyid, [description]=@description,ImageFileSmall=@imagefilesmall,IsSelf=@isSelf WHERE id=@id
		END
	END TRY
	BEGIN CATCH
		GOTO ERR
	END CATCH
COMMIT TRAN
RETURN 1
ERR:
BEGIN
	ROLLBACK TRAN
	RETURN @result
END
GO

/****** Object:  StoredProcedure [dbo].[bpAgentIPSelect]    Script Date: 2018/12/13 19:03:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2015.09.08
--创建人：Nick
--说明：AgentIP
--#####################################################
create PROCEDURE [dbo].[bpAgentIPSelect]
AS
BEGIN
	SELECT * FROM agentip WITH(NOLOCK) ORDER BY adddate DESC
END
GO

/****** Object:  StoredProcedure [dbo].[bpAnswerByIdIndexSelect]    Script Date: 2018/12/13 19:03:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--根据问题id 获取答题人数----------------
--增加人Harry ---------------------------
--增加时间：2015-1-28--------------------
CREATE PROCEDURE [dbo].[bpAnswerByIdIndexSelect]
(
	@id INT, 
	@index INT
)
AS
BEGIN
	SELECT COUNT(1) cnt 
	FROM MainDb..Answer WITH(NOLOCK) 
	WHERE questionid = @id 
	AND EXISTS( 
		SELECT 'x' 
		FROM dbo.fnSplit(answer, ',') 
		WHERE LTRIM(a) = @index
		)
END
GO

/****** Object:  StoredProcedure [dbo].[bpAnswerByIdManIdSelect]    Script Date: 2018/12/13 19:03:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--检查是否完成了问卷调查-----------------
--增加人Harry ---------------------------
--增加时间：2015-1-28--------------------
CREATE PROCEDURE [dbo].[bpAnswerByIdManIdSelect]
(
	@id INT, 
	@manid INT
)
AS
BEGIN 
	SELECT TOP 1 * 
	FROM MainDb..Answer WITH(NOLOCK) 
	WHERE Questionid = @id 
	AND ManId = @manid
END
GO

/****** Object:  StoredProcedure [dbo].[bpAnswerByIdSelect]    Script Date: 2018/12/13 19:03:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--根据问题id 获取答案----------------
--增加人Harry ---------------------------
--增加时间：2015-1-28--------------------
CREATE PROCEDURE [dbo].[bpAnswerByIdSelect]
(
	@id INT
)
AS
BEGIN
	SELECT  a.Assist, b.Id, b.Name 
	FROM MainDb..Answer a WITH(NOLOCK), ManagerUser b WITH(NOLOCK) 
	WHERE a.Manid = b.Id 
	AND LEN(a.Assist)>0 
	AND a.Questionid = @id
END
GO

/****** Object:  StoredProcedure [dbo].[bpAnswerByInvestIdSelect]    Script Date: 2018/12/13 19:03:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--获取调查的参加人数----------------
--增加人Harry -----------------------
--增加时间：2015-1-28---------------
CREATE PROCEDURE [dbo].[bpAnswerByInvestIdSelect]
(
	@id INT
)
AS
BEGIN
	SELECT TOP 1 COUNT(1) cnt  
	FROM MainDb..Answer a WITH(NOLOCK), MainDb..Question b WITH(NOLOCK) 
	WHERE a.QuestionId = b.Id 
	AND b.InvestId = @id 
	GROUP BY a.QuestionId
END
GO

/****** Object:  StoredProcedure [dbo].[bpAnswerInsert]    Script Date: 2018/12/13 19:03:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--添加调查问卷回答的内容-----------------
--增加人Harry ---------------------------
--增加时间：2015-1-28--------------------
CREATE PROCEDURE [dbo].[bpAnswerInsert]
(
	@answer VARCHAR(20), 
	@assist NVARCHAR(200), 
	@questionid INT, 
	@manid INT
)
AS
BEGIN
	INSERT INTO MainDb..Answer (Answer, Assist, Questionid, Manid) 
	VALUES (@answer, @assist, @questionid, @manid)
END
GO

/****** Object:  StoredProcedure [dbo].[bpBackDataByDaySelect]    Script Date: 2018/12/13 19:03:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBackDataByDaySelect]
(
	@Day VARCHAR(8)
)
AS
BEGIN
	SELECT *, 
		(SELECT COUNT(ID) 
		FROM bscpstatus 
		WHERE statusID=9 
		AND CONVERT(VARCHAR(10), AddDate, 112) = @Day 
		AND VerifyMan = x.ManagerID
		) limitCount 
	FROM 
		(SELECT managerid, SUM(reSUMe_pass) reSUMe_pass, SUM(reSUMe_delete) reSUMe_delete, 
		SUM(reSUMe_nopass) reSUMe_nopass, SUM(reSUMe_modify) reSUMe_modify, SUM(photo_pass) photo_pass, 
		SUM(photo_modify) photo_modify, SUM(photo_delete) photo_delete, SUM(companylogo_pass) companylogo_pass, 
		SUM(companylogo_delete) companylogo_delete, SUM(companyvisual_pass) companyvisual_pass, 
		SUM(companyvisual_delete) companyvisual_delete, SUM(black) black, SUM(company_modify) company_modify, 
		SUM(companydelete) companydelete,SUM(Contact_NoCvCount) Contact_NoCvCount,SUM(Contact_NoApplyCount) Contact_NoApplyCount,
		SUM(Contact_Count) Contact_Count,SUM(Contact_CvCount) Contact_CvCount,SUM(Contact_ApplyCount) Contact_ApplyCount 
		FROM backdata WITH(NOLOCK)  
		WHERE CONVERT(VARCHAR(10), CountDate, 112) = @Day 
		GROUP BY ManagerID 
		) x 
	ORDER BY ManagerID
END 
GO

/****** Object:  StoredProcedure [dbo].[bpBackDataByManagerUserIDSelect]    Script Date: 2018/12/13 19:03:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2015.01.06
--创建人：Nick
--说明：根据根据manageruserid获取backdata
--#####################################################
CREATE PROCEDURE [dbo].[bpBackDataByManagerUserIDSelect]
(
	@ManagerUserID INT,
	@BeginDate VARCHAR(40),
	@EndDate VARCHAR(40)
)
AS
BEGIN
	SELECT *,(
		SELECT COUNT(ID) 
		FROM bscpstatus 
		WHERE statusID=9 
		AND CONVERT(VARCHAR(10),adddate,112)=backdata.countdate 
		AND VerifyMan = backdata.ManagerID
	) limitCount 
	FROM backdata WITH(NOLOCK)  
	WHERE countdate>=@BeginDate 
	AND countdate<=@EndDate 
	AND managerid=@ManagerUserID 
	ORDER BY countdate
END 
GO

/****** Object:  StoredProcedure [dbo].[bpBackDataByMonthSelect]    Script Date: 2018/12/13 19:03:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBackDataByMonthSelect]
(
	@BeginDate VARCHAR(8),
	@EndDate VARCHAR(8)
)
AS
BEGIN
	SELECT *,(
		SELECT COUNT(ID) 
		FROM bscpstatus 
		WHERE statusID=9 
		AND CONVERT(VARCHAR(10),adddate,112)>=@BeginDate 
		AND CONVERT(VARCHAR(10),adddate,112)<=@EndDate 
		AND VerifyMan = x.ManagerID
	) limitCount FROM (
		SELECT managerid,Sum(resume_pass) resume_pass,Sum(resume_delete) resume_delete,Sum(resume_nopass) resume_nopass,
		Sum(resume_modify) resume_modify,Sum(photo_pass) photo_pass,Sum(photo_modify) photo_modify,
		Sum(photo_delete) photo_delete,Sum(companylogo_pass) companylogo_pass,Sum(companylogo_delete) companylogo_delete,
		Sum(companyvisual_pass) companyvisual_pass,Sum(companyvisual_delete) companyvisual_delete,Sum(black) black,
		Sum(company_modify) company_modify,Sum(companydelete) companydelete,SUM(Contact_NoCvCount) Contact_NoCvCount,
		SUM(Contact_NoApplyCount) Contact_NoApplyCount,SUM(Contact_Count) Contact_Count,
		SUM(Contact_CvCount) Contact_CvCount,SUM(Contact_ApplyCount) Contact_ApplyCount 
		FROM backdata WITH(NOLOCK)
		WHERE countdate>=@BeginDate
		AND countdate<=@EndDate
		GROUP BY ManagerID
	) x ORDER BY ManagerID
END 
GO

/****** Object:  StoredProcedure [dbo].[bpBackdataByWeekSelect]    Script Date: 2018/12/13 19:03:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBackdataByWeekSelect]
(
	@week INT
)
AS
INSERT INTO procLog SELECT 'bpBackdataByWeekSelect', '', GETDATE()
BEGIN
    SET DATEFIRST 1
    SELECT managerid, SUM(resume_pass) resume_pass,SUM(resume_delete) resume_delete,
	SUM(resume_nopass) resume_nopass,SUM(resume_modify) resume_modify,SUM(photo_pass) photo_pass,
	SUM(photo_modify) photo_modify,SUM(photo_delete) photo_delete,SUM(companylogo_pass) companylogo_pass,
	SUM(companylogo_delete) companylogo_delete,SUM(companyvisual_pass) companyvisual_pass,
	SUM(companyvisual_delete) companyvisual_delete,SUM(black) black,SUM(company_modify) company_modify,
	SUM(companydelete) companydelete,SUM(Contact_NoCvCount) Contact_NoCvCount,SUM(Contact_NoApplyCount) Contact_NoApplyCount,
	SUM(Contact_Count) Contact_Count,SUM(Contact_CvCount) Contact_CvCount,SUM(Contact_ApplyCount) Contact_ApplyCount 
    FROM backdata with(nolock)   
    WHERE countDate >= CONVERT(varchar(8),dateadd(day,1-datepart(weekday,getdate()-@week*7),getdate()-@week*7),112)
    AND countDate < CONVERT(varchar(8),dateadd(day,1-datepart(weekday,getdate()-(@week-1)*7),getdate()-(@week-1)*7),112)
    GROUP BY managerid 
	ORDER BY managerid
END
GO

/****** Object:  StoredProcedure [dbo].[bpBackDataByYearSelect]    Script Date: 2018/12/13 19:03:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBackDataByYearSelect]
(
	@Year VARCHAR(8)
)
AS
BEGIN
	SELECT *,(
		SELECT COUNT(ID) 
		FROM bscpstatus 
		WHERE statusID=9 
		AND CONVERT(VARCHAR(4),adddate,112)=@Year 
		AND VerifyMan = x.ManagerID
	) limitCount 
	FROM ( 
		SELECT managerid,Sum(resume_pass) resume_pass,Sum(resume_delete) resume_delete,Sum(resume_nopass) resume_nopass,
		Sum(resume_modify) resume_modify,Sum(photo_pass) photo_pass,Sum(photo_modify) photo_modify,
		Sum(photo_delete) photo_delete,Sum(companylogo_pass) companylogo_pass,Sum(companylogo_delete) companylogo_delete,
		Sum(companyvisual_pass) companyvisual_pass,Sum(companyvisual_delete) companyvisual_delete,Sum(black) black,
		Sum(company_modify) company_modify,Sum(companydelete) companydelete,SUM(Contact_NoCvCount) Contact_NoCvCount,
		SUM(Contact_NoApplyCount) Contact_NoApplyCount,SUM(Contact_Count) Contact_Count,SUM(Contact_CvCount) Contact_CvCount,
		SUM(Contact_ApplyCount) Contact_ApplyCount 
		FROM backdata WITH(NOLOCK) 
		WHERE LEFT(countdate,4) = @Year GROUP BY managerid 
	) x ORDER BY managerid
END 
GO

/****** Object:  StoredProcedure [dbo].[bpBackDataUpdate]    Script Date: 2018/12/13 19:03:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-26
--创建人：lambo
--功能说明：根据列名和用户编号修改今天的操作记录，每次增加1，如果没有则先增加一条
--用于后台记录用户的操作记录
-----------------
CREATE PROCEDURE [dbo].[bpBackDataUpdate]
(
	@ManagerUserID INT,
	@col VARCHAR(50)
)
AS
INSERT INTO procLog SELECT 'bpBackDataUpdate', '', GETDATE()
BEGIN TRAN
	BEGIN TRY
		SET @col = REPLACE(@col, '''', '')
		DECLARE @SQL AS VARCHAR(1000)
		IF NOT EXISTS (SELECT id FROM backdata WITH (NOLOCK) WHERE managerid=@ManagerUserID AND CountDate=YEAR(GETDATE())*10000+MONTH(GETDATE())*100+DAY(GETDATE()))
			INSERT INTO backdata (managerid,countdate) VALUES(@ManagerUserID,YEAR(GETDATE())*10000+MONTH(GETDATE())*100+DAY(GETDATE()))
		SET @SQL = 'UPDATE backdata SET ' + @col + '=' + @col + '+ 1 WHERE managerid=' + LTRIM(STR(@ManagerUserID)) + 
			' AND CountDate=YEAR(GETDATE())*10000+MONTH(GETDATE())*100+DAY(GETDATE())'
		EXEC(@SQL)
	END TRY
	BEGIN CATCH
		GOTO err
	END CATCH
COMMIT TRAN
RETURN 1
err:
BEGIN
	ROLLBACK TRAN
	RETURN 0
END
GO

/****** Object:  StoredProcedure [dbo].[bpBaiduLogCheck]    Script Date: 2018/12/13 19:03:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE  [dbo].[bpBaiduLogCheck]  
AS
INSERT INTO procLog SELECT 'bpBaiduLogCheck', '', GETDATE()
BEGIN
	SELECT TOP 0 * INTO #t FROM BaiduMapLog
	ALTER TABLE #t ADD Id1 INT
	ALTER TABLE #t ADD Id2 INT
	ALTER TABLE #t ADD Id3 INT
	ALTER TABLE #t ADD JobName1 NVARCHAR(100)
	ALTER TABLE #t ADD JobName2 NVARCHAR(100)
	ALTER TABLE #t ADD JobName3 NVARCHAR(100)

	INSERT INTO #t(JobID, Lng, Lat, Status)
	SELECT Id, Lng, Lat, 9
	FROM cpMainPublish WITH(NOLOCK) 
	WHERE Valid = 1 AND Id IN(SELECT cpMainId FROM JobPublish WITH(NOLOCK) WHERE Valid = 1)
	AND Lng > 0
	

	UPDATE a
	SET a.Address = b.Address,
		a.cpMainName = b.Name,
		a.FullName = b.SecondId,
		a.Status = b.dcProvinceID
	FROM #t a, cpMain b
	WHERE a.JobId = b.Id

	UPDATE a
	SET a.JobUrl = b.ProvinceDomain
	FROM #t a, dcProvince b
	WHERE a.Status = b.Id

	UPDATE #t SET cpMainUrl = 'http://www.' + JobUrl + '/personal/cp' + FullName +'.html'

	UPDATE #t SET Status = 9, JobUrl = cpMainUrl, FullName = NULL

	SELECT cpMainId, Id, Name, dcSalaryId, ROW_NUMBER() OVER(PARTITION BY cpMainId ORDER BY CASE dcSalaryId WHEN 100 THEN 5 ELSE dcSalaryId END DESC, IssueDate DESC, ID DESC) RowNo
	INTO #1
	FROM JobPublish WITH(NOLOCK)
	WHERE Valid = 1

	DELETE #1 WHERE cpMainId NOT IN(SELECT JobId FROM #t)
	DELETE #1 WHERE RowNo > 3

	UPDATE a SET a.Id1 = b.Id, a.JobName1 = b.Name, a.dcSalaryId = b.dcSalaryId FROM #t a, #1 b WHERE a.JobId = b.cpMainId AND RowNo = 1
	UPDATE a SET a.Id2 = b.Id, a.JobName2 = b.Name FROM #t a, #1 b WHERE a.JobId = b.cpMainId AND RowNo = 2
	UPDATE a SET a.Id3 = b.Id, a.JobName3 = b.Name FROM #t a, #1 b WHERE a.JobId = b.cpMainId AND RowNo = 3

	DELETE FROM #t WHERE Id1 IS NULL
	UPDATE #t SET JobName = JobName1
	UPDATE #t SET JobName = JobName + ' | ' + JobName2 WHERE JobName2 IS NOT NULL AND LEN(JobName + ' | ' + JobName2) < 50
	UPDATE #t SET JobName = JobName + ' | ' + JobName3 WHERE JobName3 IS NOT NULL AND LEN(JobName + ' | ' + JobName3) < 51
	UPDATE a
	SET a.Salary = b.Description
	FROM #t a, dcSalary b
	WHERE a.dcSalaryId = b.Id
	---------------------------------------

	INSERT INTO BaiduMapLog(JobId, JobName, Address, cpMainName, Lng, Lat, dcSalaryId, Salary, JobUrl, cpMainUrl, [Status])
	SELECT JobId, JobName, Address, cpMainName, Lng, Lat, dcSalaryId, Salary, JobUrl, cpMainUrl,  1
	FROM #t
	WHERE JobID NOT IN(SELECT JobId FROM BaiduMapLog WITH(NOLOCK))

	DELETE BaiduMapLog
	WHERE JobId IN(SELECT ID FROM cpMain WHERE Lat IS NULL OR Lng IS NULL)

	UPDATE BaiduMapLog
	SET Status = 3
	WHERE JobId NOT IN(
		SELECT b.Id
		FROM Job a WITH(NOLOCK), cpMain b WITH(NOLOCK)
		WHERE a.cpMainId = b.Id
		AND a.Valid = 1 
		AND b.Valid = 1)

	UPDATE b SET b.JobName = a.JobName ,b.Status=2 FROM #t a, BaiduMapLog b WHERE a.JobId = b.JobId AND b.Status = 0 AND a.JobName <> b.JobName
	UPDATE b SET b.Address = a.Address ,b.Status=2 FROM #t a, BaiduMapLog b WHERE a.FullName IS NULL AND a.JobId = b.JobId AND b.Status = 0 AND a.Address <> b.Address
	UPDATE b SET b.cpMainName =a.cpMainName, b.Status=2   FROM #t a, BaiduMapLog b WHERE a.FullName IS NULL AND a.JobId = b.JobId AND b.Status = 0 AND a.cpMainName <> b.cpMainName
	UPDATE b SET b.Lng=a.Lng, b.Status=2 FROM #t a, BaiduMapLog b WHERE a.FullName IS NULL AND a.JobId = b.JobId AND b.Status = 0 AND a.Lng <> b.Lng
	UPDATE b SET b.Lat = a.Lat, b.Status=2 FROM #t a, BaiduMapLog b WHERE a.FullName IS NULL AND a.JobId = b.JobId AND b.Status = 0 AND a.Lat <> b.Lat
	UPDATE b SET b.dcSalaryId = a.dcSalaryId, b.Status=2  FROM #t a, BaiduMapLog b WHERE a.FullName IS NULL AND a.JobId = b.JobId AND b.Status = 0 AND a.dcSalaryId <> b.dcSalaryId 
END 
GO

/****** Object:  StoredProcedure [dbo].[bpBaiduLogSelect]    Script Date: 2018/12/13 19:03:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBaiduLogSelect]
AS
RETURN
BEGIN
/*
	UPDATE a SET
		a.Valid = b.Valid,
		a.OperDate = GETDATE()
	FROM LogDB..BaiduMapLog a,Job b
	WHERE a.JobID=b.ID AND a.Valid <> b.Valid

	UPDATE a SET
		a.RefreshDate = CONVERT(VARCHAR,c.LastLoginDate,112),
		a.OperDate = GETDATE()
	FROM LogDB..BaiduMapLog a,Job b,cpMain c
	WHERE a.JobID=b.ID AND b.cpMainID=c.ID AND a.RefreshDate<CONVERT(VARCHAR,c.LastLoginDate,112) AND a.Valid=1

	UPDATE a SET
		a.Lat = b.Lat,
		a.Lng = b.Lng,
		a.OperDate = GETDATE()
	FROM LogDB..BaiduMapLog a,Job b
	WHERE a.JobID=b.ID AND a.Lng<>b.Lng AND b.Lat<>a.Lat AND a.Valid=1

	UPDATE a SET
		a.Name=b.Name,
		a.CoordType=3,
		a.Industry=(SELECT TOP 1 dcIndustryID FROM cpIndustry WITH(NOLOCK) WHERE cpIndustry.cpMainid=b.cpMainID),
		a.Valid=b.Valid,
		a.Description=(SELECT Description FROM dcSalary WITH(NOLOCK) WHERE dcSalary.ID=b.dcSalaryID),
		a.dcJobTypeID=b.dcJobTypeID,
		a.SecondID=b.SecondID,
		a.RefreshDate=CONVERT(VARCHAR(8),b.RefreshDate,112),
		a.cpMainName=c.Name,
		a.cpMainSecondId=c.SecondID,
		a.JobName=b.Name,
		a.cpMainId=c.ID
	FROM Logdb..BaiduMapLog a,Job b,cpMain c
	WHERE a.JobID=b.ID AND b.cpMainID=c.ID AND OperDate>UploadDate
	
	UPDATE a SET
		a.FullName=(SELECT FullName + c.Address FROM dcRegion WITH(NOLOCK) WHERE dcRegion.ID=b.dcRegionID)
	FROM Logdb..BaiduMapLog a,Job b,cpMain c
	WHERE a.JobID=b.ID AND b.cpMainID=c.ID AND OperDate>UploadDate AND LEN(ISNULL(FullName,''))=0

	SELECT TOP 50000 * FROM Logdb..BaiduMapLog WITH(NOLOCK) WHERE OperDate>UploadDate ORDER BY OperDate DESC
*/
print 1
END
GO

/****** Object:  StoredProcedure [dbo].[bpbsAskClickLogByCountMontySelect]    Script Date: 2018/12/13 19:03:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--bpbsAskClickLogByCountMontySelect '201401'
--创建人 Andy
--时间 2015-01-23
--按月查询求职互助点击量
CREATE PROCEDURE [dbo].[bpbsAskClickLogByCountMontySelect] 
(
	@CountMonth NVARCHAR(6)	
)
AS
BEGIN	
	SELECT b.AddUserID, SUM(a.increase) Cnt
	FROM bsAskClickLog a, bsASKInfo b
	WHERE a.bsAskInfoID = b.ID
	AND a.CountMonth = @Countmonth
	GROUP BY b.AddUserID
	ORDER BY AddUserID
END

GO

/****** Object:  StoredProcedure [dbo].[bpBsAskInfoBySourceSelect]    Script Date: 2018/12/13 19:03:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBsAskInfoBySourceSelect]
AS
SET NOCOUNT ON
BEGIN
	SELECT CountDate, Province_Id, ISNULL(SUM(Page_Count), 0) cnt, southnorth  
	INTO #t
	FROM (SELECT countdate, a.Id as Province_Id, D.Page_Count, southnorth  
		FROM dcProvince a
		LEFT JOIN ( 
			SELECT b.CountDate, c.Page_Count, b.Province_Id 
			From Innerer_CountNew b left join innerer_count_detail c 
			ON c.Innerer_CountId = b.Id  
			WHERE countdate >= CONVERT(VARCHAR(8), GETDATE() - 100, 112)
			AND c.Innerpage=5 
			AND Province_Id < 999  
			AND (LEN(Province_Id)=2 OR LEN(Province_Id)=4 OR Province_Id=0)
			) d ON a.Id  = d.Province_Id
		) E
	GROUP BY CountDate, Province_Id, southnorth 
	ORDER BY CountDate, CASE southnorth WHEN 4 THEN 1 ELSE southnorth END DESC,
		Province_Id

	SELECT DISTINCT CASE southnorth WHEN 4 THEN 1 ELSE southnorth END southnorth, Province_Id 
	INTO #p
	FROM #t
	ORDER BY CASE southnorth WHEN 4 THEN 1 ELSE southnorth END DESC, Province_Id

	DECLARE @temp NVARCHAR(max)
	SELECT @temp=COALESCE(@temp,'')+ '['+  LTRIM(STR(province_id)) +'], '  
	FROM   
	#p  
	set @temp=SUBSTRING(@temp,1,LEN(@temp)-1)  
	--print @temp
	 
	DECLARE @sql NVARCHAR(max)  
	SET @sql='  
	SELECT countdate, ' + @temp + '
	from (select countdate, province_id, cnt from #t) a
	pivot(avg(Cnt)
		for province_id IN('+ @temp+')
	)b ORDER BY CountDATE DESC'  
	--PRINT @sql  
	EXEC(@sql)
END
GO

/****** Object:  StoredProcedure [dbo].[bpbsConsultantFreeOrderInsert]    Script Date: 2018/12/13 19:03:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--添加顾问免费订单记录 Lucifer 2014-12-24
CREATE PROCEDURE [dbo].[bpbsConsultantFreeOrderInsert]
(
	@OrderID INT,
	@ConsultantID SMALLINT
)
AS
INSERT INTO procLog SELECT 'bpbsConsultantFreeOrderInsert', '', GETDATE()
BEGIN
	INSERT INTO MainDb..bsConsultantFreeOrder(ConsultantId,OpenDate,OrderID) SELECT @ConsultantID,GETDATE(),@OrderID
END

GO

/****** Object:  StoredProcedure [dbo].[bpbsConsultantFreeOrderSetSelect]    Script Date: 2018/12/13 19:03:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--查询顾问当月是否还有订单赠送配额 Lucifer 2014-12-24
CREATE PROCEDURE [dbo].[bpbsConsultantFreeOrderSetSelect]
(
	@ConsultantID SMALLINT
)
AS
BEGIN
	SELECT 'X' FROM bsConsultantFreeOrderSet a WHERE (
		SELECT COUNT(*) cnt FROM bsConsultantFreeOrder
		WHERE OpenDate>CONVERT(DATETIME,CONVERT(VARCHAR(8),DATEADD(DAY, -25, GETDATE()),21)+'26') AND
		ConsultantId = a.ConsultantId
	) < a.Number AND [Type]=13 AND ConsultantId = @ConsultantId
END

GO

/****** Object:  StoredProcedure [dbo].[bpbsConsultantFreeOrderSetUpdate]    Script Date: 2018/12/13 19:03:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-------------------------------------------------------
--创建日期：2014-12-1
--创建人：harry
--功能说明：newoamvc单位用户管理-->设置顾问开通权限限额分配
--用于设置后台顾问开通权限限额分配
-------------------------------------------------------
CREATE PROCEDURE [dbo].[bpbsConsultantFreeOrderSetUpdate]
(
    @Number INT,
    @DeptId INT
)
AS
SET NOCOUNT ON
BEGIN
    UPDATE bsConsultantFreeOrderSet 
    SET Number = @Number
    WHERE ConsultantId IN(
        SELECT Id 
        FROM Manageruser WITH(NOLOCK)    
        WHERE DeptId = @DeptId)

	INSERT INTO bsConsultantFreeOrderSet
	SELECT ID, @Number, 13 FROM ManagerUser WHERE DeptId = @DeptId AND STATUS < 3 AND ID NOT IN(SELECT ConsultantId FROM bsConsultantFreeOrderSet)

    RETURN 1
END
GO

/****** Object:  StoredProcedure [dbo].[bpBsCpBackLoginByUnqueIDSelect]    Script Date: 2018/12/13 19:03:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBsCpBackLoginByUnqueIDSelect]
(
	@UID VARCHAR(20)	
)
AS
BEGIN
	DECLARE @CaMainID AS INT
	SELECT @CaMainID = caMainID
	FROM bsCpBackLogin WITH(NOLOCK)
	WHERE UnqueID = @UID
	IF @CaMainID IS NULL
		RETURN 0
	ELSE
		RETURN @CaMainID
END















GO

/****** Object:  StoredProcedure [dbo].[bpBsCpBackLoginInsert]    Script Date: 2018/12/13 19:03:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBsCpBackLoginInsert]
(
	@cpMainID INT,
	@caMainID INT,
	@ManagerUserID INT,
	@UID VARCHAR(20) OUTPUT
)
AS
BEGIN
	DECLARE @T AS VARCHAR(20)
	SET @T = CONVERT(VARCHAR(20), CONVERT(DECIMAL(18,12),GETDATE()))
	SET @T = RTRIM(CONVERT(VARCHAR(12), @cpMainID)) + '_' + RIGHT(@T,8)
	INSERT INTO BsCpBackLogin (UnqueID,cpMainID,caMainID,ManagerUserID)
	VALUES(@T,@cpMainID,@caMainID,@ManagerUserID)
	SET @UID =  @T
END













GO

/****** Object:  StoredProcedure [dbo].[bpbsCpModifyByFilterWordSelect]    Script Date: 2018/12/13 19:03:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2015.1.8
--创建人：Nick
--说明：获取包含过滤词的职位
--#####################################################
CREATE PROCEDURE [dbo].[bpbsCpModifyByFilterWordSelect]
AS
BEGIN 
	SELECT a.Id cpMainID, a.IsLimitLogin, a.consultantid, a.haslicence, a.RegDate, a.Name cpName, b.FilterWord, 
	b.AddDate, Left(b.ColValue, 30) colValue 
	FROM cpMain a With(Nolock), bsCpModify b WITH(NOLOCK) 
	WHERE a.Id = b.cpMainId AND b.FilterWord>'' 
	AND b.verifyinfo IS NULL AND b.AddDate > GETDATE() - 2 AND IsLimitLogin < 9 
	ORDER BY b.AddDate
End
GO

/****** Object:  StoredProcedure [dbo].[bpbsCpModifyPassUpdate]    Script Date: 2018/12/13 19:03:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpbsCpModifyPassUpdate]
(
	@bsCpVerifyID INT,
	@Pass INT
)
AS
BEGIN TRY
	DECLARE @cpMainID AS INT
	SELECT @cpMainID = cpMainID FROM bsCpVerify WITH(NOLOCK) WHERE ID=@bsCpVerifyID
	UPDATE cpMain SET VerifyResult = @Pass WHERE ID = @cpMainID
	
/*		Job全部通过，要么修改 要么删除
	UPDATE Job SET VerifyResult = @Pass WHERE ID IN(
		SELECT DISTINCT SubID FROM bsCpModify WITH(NOLOCK)
		WHERE cpMainID =  @cpMainID
		AND Location Like 'c%'
	)
*/
END TRY
BEGIN CATCH
	GOTO ERR
END CATCH
RETURN 1
ERR:
BEGIN
	RETURN 0
END
GO

/****** Object:  StoredProcedure [dbo].[bpbsCpModifyUpdate]    Script Date: 2018/12/13 19:03:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpbsCpModifyUpdate]
(
	@ID INT,
	@bsCpVerifyID INT,
	@VerifyInfo NVARCHAR(200)
)
AS
BEGIN TRY
	DECLARE @MaxID AS INT
	IF ISNULL(@VerifyInfo, '') =''
		BEGIN
			INSERT INTO bsCpVerifyLog(bsCpVerifyID,SubID,Location,AddDate,OldValue)
				SELECT @bsCpVerifyID,SubID,Location,AddDate,ColValue FROM bsCpModify WITH(NOLOCK) WHERE ID = @ID
			SET @MaxID = @@IDENTITY
			DELETE FROM bsCpModify WHERE ID = @ID
		END
	ELSE
		BEGIN
			INSERT INTO bsCpVerifyLog(bsCpVerifyID,SubID,Location,AddDate,OldValue,VerifyResult,VerifyInfo)
				SELECT @bsCpVerifyID,SubID,Location,AddDate,ColValue,1,@VerifyInfo FROM bsCpModify WITH(NOLOCK) WHERE ID = @ID
			SET @MaxID = @@IDENTITY
			UPDATE bsCpModify SET VerifyInfo=@VerifyInfo WHERE ID=@ID
		END
END TRY
BEGIN CATCH
	GOTO ERR
END CATCH
RETURN 1
ERR:
BEGIN
	RETURN 0
END













GO

/****** Object:  StoredProcedure [dbo].[bpbsCpStatusByCpMainIDSelect]    Script Date: 2018/12/13 19:03:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[bpbsCpStatusByCpMainIDSelect]
(
	@StatusID VARCHAR(20),
	@cpMainID INT
)
AS
BEGIN
	DECLARE @SQL AS VARCHAR(1000)
	SET @StatusID = REPLACE(@StatusID, '''', '')
	SET @SQL = 'SELECT * FROM BsCpStatus WITH(NOLOCK) WHERE cpMainID = ' + RTRIM(CONVERT(CHAR(10), @cpMainID))
	IF LEN(@StatusID) > 0
		SET @SQL = @SQL + '	AND StatusID IN (' + RTRIM(CONVERT(CHAR(10), @StatusID)) + ')'
	SET @SQL = @SQL + '	ORDER BY ID DESC'
	EXEC(@SQL)
END




GO

/****** Object:  StoredProcedure [dbo].[bpBsCpStatusInsert]    Script Date: 2018/12/13 19:03:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：增加企业状态操作记录
--用于后台企业用户管理模块
-----------------
CREATE PROCEDURE [dbo].[bpBsCpStatusInsert]
(
	@cpMainID INT,
	@StatusID INT,
	@ApplyInfo VARCHAR(200),
	@VerifyMan INT,
	@VerifyInfo VARCHAR(500)
)
AS
BEGIN TRY
	DECLARE @MaxID AS INT
	INSERT INTO bsCpStatus (cpMainID,ApplyInfo,StatusID,VerifyMan,VerifyInfo,VerifyDate) 
		VALUES(@cpMainID,@ApplyInfo,@StatusID,@VerifyMan,@VerifyInfo,GETDATE())
	SET @MaxID = @@IDENTITY
END TRY
BEGIN CATCH
	GOTO ERR
END CATCH
RETURN @MaxID
ERR:
BEGIN
	RETURN 0
END













GO

/****** Object:  StoredProcedure [dbo].[bpBsCpVerifyBycpMainIDSelect]    Script Date: 2018/12/13 19:03:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBsCpVerifyBycpMainIDSelect]
(
	@cpMainID INT
)
AS
BEGIN
	SELECT * FROM BsCpVerify WITH(NOLOCK) WHERE cpMainID=@cpMainID
END













GO

/****** Object:  StoredProcedure [dbo].[bpBsCpVerifyInsert]    Script Date: 2018/12/13 19:03:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--修改Harry
--2018-5-15 
--增加审核记录,用于计算审核延误1
CREATE PROCEDURE [dbo].[bpBsCpVerifyInsert]
(
	@cpMainID INT,
	@VerifyMan INT,
	@VerifyResult TINYINT,
	@VerifyInfo NVARCHAR(200)
)
AS
SET NOCOUNT ON
BEGIN
	INSERT INTO bsCpVerify(cpMainID,VerifyMan,VerifyDate,VerifyResult,VerifyInfo)
	VALUES(@cpMainID,@VerifyMan,GETDATE(),@VerifyResult,@VerifyInfo)
	--harry begin
	DECLARE @VerifyId INT,  @submit DATETIME 
        SELECT TOP 1 @submit=adddate FROM bsCpModify  WHERE cpMainID=@cpMainID ORDER BY adddate DESC 
        IF @submit is NULL 
            SET @submit=GETDATE()
	SELECT @VerifyId=@@IDENTITY
	INSERT INTO logdb..VerifyDelayLog(verifyType, relationId, submitDate, verifyDate, verifyMan)
	SELECT 1,@VerifyId,  @submit, GETDATE(), @VerifyMan
	
	RETURN @VerifyId
END

GO

/****** Object:  StoredProcedure [dbo].[bpbsCpVerifyLogBybsCpVerifyIDSelect]    Script Date: 2018/12/13 19:03:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpbsCpVerifyLogBybsCpVerifyIDSelect]
(
    @bsCpVerifyID INT
)
AS
BEGIN
    SELECT * FROM bsCpVerifyLog WITH(NOLOCK) WHERE bsCpVerifyID = @bsCpVerifyID
END













GO

/****** Object:  StoredProcedure [dbo].[bpBsCpVerifyLogSelect]    Script Date: 2018/12/13 19:03:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[bpBsCpVerifyLogSelect]
(
	@ID INT
)
AS
BEGIN
	SELECT * FROM BsCpVerifyLog WITH(NOLOCK) WHERE ID=@ID
END













GO

/****** Object:  StoredProcedure [dbo].[bpBsCpVerifySelect]    Script Date: 2018/12/13 19:03:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[bpBsCpVerifySelect]
(
	@cpMainID INT
)
AS
BEGIN
	SELECT Top 1 * FROM BsCpVerify WITH(NOLOCK) WHERE cpMainID=@cpMainID ORDER BY VerifyDate DESC
END













GO

/****** Object:  StoredProcedure [dbo].[bpbsCvModifyByCvMainIDDelete]    Script Date: 2018/12/13 19:03:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2014.12.26
--创建人：Nick
--说明：删除bsCvModify
--#####################################################
CREATE PROCEDURE [dbo].[bpbsCvModifyByCvMainIDDelete]
(
	@CvMainID INT
)
AS
BEGIN
	DELETE FROM bsCvModify WHERE Location='ac5' And cvMainID = @CvMainID
END 
GO

/****** Object:  StoredProcedure [dbo].[bpBsCvModifyByPaMainIDUpdate]    Script Date: 2018/12/13 19:03:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBsCvModifyByPaMainIDUpdate]
(
	@paMainID INT,
	@bsCvVerifyID INT,
	@Pass INT		--100通过，2不通过
)
AS
BEGIN TRAN
	BEGIN TRY
		DECLARE @bCh AS BIT
		DECLARE @bEng AS BIT
		SET @bCh = 0
		SET @bEng = 0
		IF EXISTS(SELECT 'X' FROM bsCvModify WITH(NOLOCK) WHERE paMainID = @paMainID AND SUBSTRING(Location, 2, 1) = 'c')
			SET @bCh = 1
		IF EXISTS(SELECT 'X' FROM bsCvModify WITH(NOLOCK) WHERE paMainID = @paMainID AND SUBSTRING(Location, 2, 1) = 'c')
			SET @bEng = 1

		IF @Pass = 100
			BEGIN
				INSERT INTO bsCvVerifyLog(bsCvVerifyID,SubID,Location,AddDate,OldValue)
					SELECT @bsCvVerifyID,SubID,Location,AddDate,ColValue FROM bsCvModify WITH(NOLOCK) WHERE paMainID = @paMainID

				UPDATE bsCvVerify SET VerifyResult = 100,VerifyInfo='本次审核所有简历审核通过' WHERE ID = @bsCvVerifyID
				IF @bCH = 1
					BEGIN
						UPDATE cvMain SET VerifyResult=100
							WHERE ID IN (SELECT cvMainID FROM bsCvModify WITH(NOLOCK) WHERE paMainID=@paMainID)
					END
				IF @bEng = 1
					BEGIN
						UPDATE cvMain SET VerifyResultEng=100
							WHERE ID IN (SELECT cvMainID FROM bsCvModify WITH(NOLOCK) WHERE paMainID=@paMainID)
					END
				DELETE FROM bsCvModify WHERE paMainID = @paMainID	
			END
		ELSE
			BEGIN
				INSERT INTO bsCvVerifyLog(bsCvVerifyID,SubID,Location,AddDate,OldValue,VerifyResult,VerifyInfo)
					SELECT @bsCvVerifyID,SubID,Location,AddDate,ColValue,1,'本次审核所有简历审核不通过'
					FROM bsCvModify WITH(NOLOCK) WHERE paMainID = @paMainID
				
				UPDATE bsCvModify SET VerifyInfo='本次审核所有简历审核不通过' WHERE paMainID=@paMainID
				UPDATE bsCvVerify SET VerifyResult = 2,VerifyInfo='本次审核所有简历审核不通过' WHERE ID = @bsCvVerifyID
				IF @bCH = 1
					BEGIN
						UPDATE cvMain SET VerifyResult=@Pass
							WHERE ID IN (SELECT cvMainID FROM bsCvModify WITH(NOLOCK) WHERE paMainID=@paMainID)
					END
				IF @bEng = 1
					BEGIN
						UPDATE cvMain SET VerifyResultEng=@Pass
							WHERE ID IN (SELECT cvMainID FROM bsCvModify WITH(NOLOCK) WHERE paMainID=@paMainID)
					END				
			END
	END TRY
	BEGIN CATCH
		GOTO ERR
	END CATCH
COMMIT TRAN
RETURN 1
ERR:
BEGIN
	ROLLBACK TRAN
	RETURN 0
END













GO

/****** Object:  StoredProcedure [dbo].[bpBsCvModifyUpdate]    Script Date: 2018/12/13 19:03:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBsCvModifyUpdate]
(
	@ID INT,
	@bsCvVerifyID INT,
	@cvMainID INT,
	@VerifyInfo NVARCHAR(200)
)
AS
BEGIN TRY
	DECLARE @MaxID AS INT
	IF ISNULL(@VerifyInfo, '') =''
		BEGIN
			INSERT INTO bsCvVerifyLog(bsCvVerifyID,cvMainID,SubID,Location,AddDate,OldValue)
				SELECT @bsCvVerifyID,@cvMainID,SubID,Location,AddDate,ColValue FROM bsCvModify WITH(NOLOCK) WHERE ID = @ID
			SET @MaxID = @@IDENTITY
			DELETE FROM bsCvModify WHERE ID = @ID
		END
	ELSE
		BEGIN
			INSERT INTO bsCvVerifyLog(bsCvVerifyID,cvMainID,SubID,Location,AddDate,OldValue,VerifyResult,VerifyInfo)
				SELECT @bsCvVerifyID,@cvMainID,SubID,Location,AddDate,ColValue,1,@VerifyInfo FROM bsCvModify WITH(NOLOCK) WHERE ID = @ID
			SET @MaxID = @@IDENTITY
			UPDATE bsCvModify SET VerifyInfo=@VerifyInfo WHERE ID=@ID
		END
END TRY
BEGIN CATCH
	GOTO ERR
END CATCH
RETURN 1
ERR:
BEGIN
	RETURN 0
END













GO

/****** Object:  StoredProcedure [dbo].[bpbsCvReviewByCvMainIDDelete]    Script Date: 2018/12/13 19:03:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2014.12.25
--创建人：Nick
--说明：删除bsCvReview
--#####################################################
CREATE PROCEDURE [dbo].[bpbsCvReviewByCvMainIDDelete]
(
	@CvMainID INT
)
AS
BEGIN
	DELETE FROM bsCvReview WHERE cvMainID = @CvMainID
END 
GO

/****** Object:  StoredProcedure [dbo].[bpbsCvReviewByCvMainIDSelect]    Script Date: 2018/12/13 19:03:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2014.12.24
--创建人：Nick
--说明：根据cvmainid获取bsCvReview
--#####################################################
CREATE PROCEDURE [dbo].[bpbsCvReviewByCvMainIDSelect]
(
	@cvMainID INT
)
AS
BEGIN
	SELECT TOP 10 * FROM bsCvReview WHERE cvMainID = @cvMainID
END 

GO

/****** Object:  StoredProcedure [dbo].[bpbsCvReviewInsert]    Script Date: 2018/12/13 19:03:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2014.12.25
--创建人：Nick
--说明：插入bsCvReview
--#####################################################
CREATE PROCEDURE [dbo].[bpbsCvReviewInsert]
(
	@CvMainID INT,
	@ManagerUserID INT,
	@Reason VARCHAR(500)
)
AS
BEGIN
	INSERT bsCvReview(cvMainID,VerifyMan,VerifyInfo)
	VALUES (@CvMainID,@ManagerUserID,@Reason)
END 
GO

/****** Object:  StoredProcedure [dbo].[bpBsCvVerifyInsert]    Script Date: 2018/12/13 19:03:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--修改Harry
--2017-2-21
--增加审核记录,用于计算审核延误 编号2
---20181102 Sunshine 添加判断
CREATE PROCEDURE [dbo].[bpBsCvVerifyInsert]
(
	@cvMainID INT,
	@VerifyMan INT,
	@VerifyResult TINYINT,
	@VerifyInfo NVARCHAR(200)
)
AS
SET NOCOUNT ON
BEGIN
	DECLARE @bsCvVerifyID AS INT
	INSERT INTO bsCvVerify(cvMainID, VerifyMan, VerifyDate, VerifyResult, VerifyInfo)
	VALUES(@cvMainID, @VerifyMan, GETDATE(), @VerifyResult, @VerifyInfo)
	SET @bsCvVerifyID = @@IDENTITY
    
    --插入审核延误记录表
    ---20181102 Sunshine 添加判断
	IF NOT EXISTS(SELECT TOP 1 AddDate FROM bsCvModify WHERE cvMainID = @cvMainID)
		INSERT INTO logdb..VerifyDelayLog(verifyType, relationId, submitDate, verifyDate, verifyMan)
		SELECT 2,@bsCvVerifyID, GETDATE(), GETDATE(), @VerifyMan
	ELSE 
		INSERT INTO logdb..VerifyDelayLog(verifyType, relationId, submitDate, verifyDate, verifyMan)
		SELECT 2,@bsCvVerifyID,(SELECT TOP 1 AddDate FROM bsCvModify WHERE cvMainID=@cvMainID ORDER BY adddate DESC), GETDATE(), @VerifyMan
	
	--Harry Begin
	IF @VerifyResult = 10
	BEGIN
		DECLARE @wxFansId INT, @paMainId INT
		SELECT @wxFansId = a.id, @paMainId=b.paMainId
		FROM wxFans a WITH(NOLOCK), cvMain b WITH(NOLOCK) 
		WHERE a.paMainId = b.paMainId 
		AND b.Id = @cvMainID 
		AND a.[Status] = 2

		IF @wxFansId > 0
		BEGIN
			--插入到wxMessageSend表中(7. --简历审核未通过)
			INSERT WxMessageSend2(Templatetype, WxFansID, PaMainId, MainTableId, CvMainID, AddDate)
			SELECT 7, @wxFansId, @paMainId, @bsCvVerifyID, @cvMainID, GETDATE()
		END
		--Harry End
	END

	RETURN @bsCvVerifyID
END
GO

/****** Object:  StoredProcedure [dbo].[bpbsCvVerifyLogBybsCvVerifyIDSelect]    Script Date: 2018/12/13 19:03:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpbsCvVerifyLogBybsCvVerifyIDSelect]
(
    @bsCvVerifyID INT
)
AS
BEGIN
    SELECT * FROM bsCvVerifyLog WITH(NOLOCK) WHERE bsCvVerifyID = @bsCvVerifyID
END













GO

/****** Object:  StoredProcedure [dbo].[bpBsCvVerifySelect]    Script Date: 2018/12/13 19:03:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[bpBsCvVerifySelect]
(
	@ID INT
)
AS
BEGIN
	SELECT * FROM BsCvVerify WITH(NOLOCK) WHERE ID=@ID
END













GO

/****** Object:  StoredProcedure [dbo].[bpBsCvVerifyUpdate]    Script Date: 2018/12/13 19:03:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpBsCvVerifyUpdate]
(
	@ID INT,
	@VerifyResult TINYINT
)
AS
BEGIN TRY
	UPDATE bsCvVerify SET VerifyResult=@VerifyResult WHERE ID=@ID
	RETURN 1
END TRY
BEGIN CATCH
	RETURN 0
END CATCH













GO

/****** Object:  StoredProcedure [dbo].[bpbsDeadCompanyByStatisticsSelect]    Script Date: 2018/12/13 19:03:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Mice 123>
-- Create date: <2014-12-09>
-- Description:	<死客户汇总查询>
-- Editer : mice 2014-12-15 增加@strPara条件（权限控制）
-- Editer : mice 2014-12-29 修改激活数量ActiveNum的时间依据为激活时间
-- Editer : mice 2014-12-30 修改激活时间和联系时间差在两个月之内的，激活数有效
-- Editer : mice 2014-12-31 需改bug（left out join 为 full out join）
-- =============================================
CREATE PROCEDURE [dbo].[bpbsDeadCompanyByStatisticsSelect] 
(
	@group INT = 0,
	@value INT = NULL,
	@begintime1 VARCHAR(20) = '',
	@endtime1 VARCHAR(20) = '',
	@begintime2 VARCHAR(20) = '',
	@endtime2 VARCHAR(20) = '',
	@strPara VARCHAR(200) = NULL
)
AS
BEGIN
	DECLARE @SQL AS VARCHAR(3000), @COLS AS VARCHAR(1000), @strWhere AS VARCHAR(300), @activeNumWhere AS VARCHAR(300)
	SELECT @COLS = ' COUNT(c.id) AS totalCOUNT, 
		SUM(CASE ISNULL(c.ActiveDate,0) WHEN 0 THEN 1 ELSE 0 END) AS NotActiveNum, 
		SUM(CASE ISNULL(c.ContactResult,0) WHEN 0 THEN 1 ELSE 0 END) AS COUNT0,
		SUM(CASE c.ContactResult WHEN 1 THEN 1 ELSE 0 END) AS COUNT1,
		SUM(CASE c.ContactResult WHEN 2 THEN 1 ELSE 0 END) AS COUNT2,
		SUM(CASE c.ContactResult WHEN 3 THEN 1 ELSE 0 END) AS COUNT3,
		SUM(CASE c.ContactResult WHEN 4 THEN 1 ELSE 0 END) AS COUNT4, 
		SUM(CASE c.ContactResult WHEN 5 THEN 1 ELSE 0 END) AS COUNT5,
		SUM(CASE c.ContactResult WHEN 6 THEN 1 ELSE 0 END) AS COUNT6 ',
		@strWhere = '',
		@activeNumWhere = ''
	
	IF @begintime1 > '' 
	BEGIN
		SET @strWhere = @strWhere + ' AND c.DeadDate > ''' + @begintime1 +''''
		SET @activeNumWhere = @activeNumWhere + ' AND c.DeadDate > ''' + @begintime1 +''''
	END
	IF @endtime1 > ''
	BEGIN
		SET @strWhere = @strWhere + ' AND c.DeadDate < ''' + @endtime1 +''''
		SET @activeNumWhere = @activeNumWhere + ' AND c.DeadDate < ''' + @endtime1 +''''
	END
	IF @begintime2 > ''
	BEGIN
		SET @strWhere = @strWhere + ' AND c.ContactDate > ''' + @begintime2 +''''
		SET @activeNumWhere = @activeNumWhere + ' AND c.ActiveDate > ''' + @begintime2 +''''
	END
	IF @endtime2 > ''
	BEGIN
		SET @strWhere = @strWhere + ' AND C.ContactDate < ''' + @endtime2 +''''
		SET @activeNumWhere = @activeNumWhere + ' AND c.ActiveDate < ''' + @endtime2 +''''
	END
	IF @strPara > ''
	BEGIN
		SET @strWhere = @strWhere + @strPara
		SET @activeNumWhere = @activeNumWhere + @strPara
	END
	IF @value IS NOT NULL AND @VALUE > 0
	BEGIN
		IF @group = 1 -- 按照地区分组查询显示
		BEGIN
			SET @strWhere = @strWhere + ' AND a.dcProvinceID = ' + CONVERT(VARCHAR(10),@value)
			SET @activeNumWhere = @activeNumWhere + ' AND a.dcProvinceID = ' + CONVERT(VARCHAR(10),@value)
		END
		ELSE IF @group = 2 -- 按照招聘顾问工号分组查询显示
		BEGIN
			SET @strWhere = @strWhere + ' AND a.ConsultantID = ' + CONVERT(VARCHAR(10),@value)
			SET @activeNumWhere = @activeNumWhere + ' AND a.ConsultantID = ' + CONVERT(VARCHAR(10),@value)
		END
		ELSE -- 按照部门分组查询显示（默认显示方式）
		BEGIN
			SET @strWhere = @strWhere + ' AND b.deptID = ' + CONVERT(VARCHAR(10),@value)
			SET @activeNumWhere = @activeNumWhere + ' AND b.deptID = ' + CONVERT(VARCHAR(10),@value)
		END	
	END


	IF @group = 1 -- 按照地区分组查询显示
	BEGIN
		SET @SQL = ' SELECT T1.*,T2.* FROM (
			SELECT a.dcProvinceID as groupID,
			( SELECT x.ProvinceName FROM maindb..dcprovince x WITH(NOLOCK) WHERE x.id=a.dcProvinceID) AS groupName, 
			COUNT(1) AS ActiveNum 
			FROM maindb..cpmain a WITH(NOLOCK) , Maindb..bsDeadCompany c WITH(NOLOCK) 
			WHERE a.id = c.cpMainID  AND c.ActiveDate IS NOT NULL 
			AND c.ContactDate IS NOT NULL  AND DATEADD(MONTH,2,c.ContactDate ) >  c.ActiveDate
			' + @activeNumWhere + '
			GROUP BY a.dcProvinceID ) 
			T1 FULL OUTER JOIN (
			SELECT a.dcProvinceID as groupID2 , 
			( SELECT x.ProvinceName FROM maindb..dcprovince x WITH(NOLOCK) WHERE x.id=a.dcProvinceID) AS groupName2, 
			' + @COLS +'
			FROM maindb..cpmain a WITH(NOLOCK) , Maindb..bsDeadCompany c WITH(NOLOCK) 
			WHERE a.id = c.cpMainID
			' + @strWhere + '
			GROUP BY a.dcProvinceID  ) 
			T2 ON T2.groupID2 = T1.groupID 
			ORDER BY T2.groupID2, T1.groupID '

	END
	ELSE IF @group = 2 -- 按照招聘顾问工号分组查询显示
	BEGIN
		SET @SQL = ' SELECT T1.*,T2.* FROM (
			SELECT a.ConsultantID as groupID,
			( SELECT x.name FROM maindb..manageruser x WITH(NOLOCK) WHERE x.id=a.ConsultantID) AS groupName, 
			COUNT(1) AS ActiveNum 
			FROM maindb..cpmain a WITH(NOLOCK) , Maindb..bsDeadCompany c WITH(NOLOCK) 
			WHERE a.id = c.cpMainID  AND c.ActiveDate IS NOT NULL 
			AND c.ContactDate IS NOT NULL  AND DATEADD(MONTH,2,c.ContactDate ) >  c.ActiveDate
			' + @activeNumWhere + '
			GROUP BY a.ConsultantID ) 
			T1 FULL OUTER JOIN (
			SELECT a.ConsultantID as groupID2 , 
			( SELECT x.name FROM maindb..manageruser x WITH(NOLOCK) WHERE x.id=a.ConsultantID) AS groupName2, 
			' + @COLS +'
			FROM maindb..cpmain a WITH(NOLOCK) , Maindb..bsDeadCompany c WITH(NOLOCK) 
			WHERE a.id = c.cpMainID
			' + @strWhere + '
			GROUP BY a.ConsultantID  ) 
			T2 ON T2.groupID2 = T1.groupID 
			ORDER BY T2.groupID2, T1.groupID '
	END
	ELSE -- 按照部门分组查询显示（默认显示方式）
	BEGIN
		SET @SQL = ' SELECT T1.*,T2.* FROM (
			SELECT b.deptID  as groupID,
			( SELECT x.DeptName FROM maindb..dept x WITH(NOLOCK) WHERE x.id=b.deptID ) AS groupName, 
			COUNT(1) AS ActiveNum 
			FROM maindb..cpmain a WITH(NOLOCK), oadb..manageruser b WITH(NOLOCK), Maindb..bsDeadCompany c WITH(NOLOCK) 
			WHERE a.ConsultantID = b.id AND a.id = c.cpMainID  AND c.ActiveDate IS NOT NULL 
			AND c.ContactDate IS NOT NULL  AND DATEADD(MONTH,2,c.ContactDate ) >  c.ActiveDate
			' + @activeNumWhere + '
			GROUP BY b.deptID ) 
			T1 FULL OUTER JOIN (
			SELECT b.deptID  as groupID2 , 
			( SELECT x.DeptName FROM maindb..dept x WITH(NOLOCK) WHERE x.id=b.deptID ) AS groupName2,
			' + @COLS +'
			FROM maindb..cpmain a WITH(NOLOCK), oadb..manageruser b WITH(NOLOCK), Maindb..bsDeadCompany c WITH(NOLOCK)
			WHERE a.ConsultantID = b.id AND a.id = c.cpMainID
			' + @strWhere + '
			GROUP BY b.deptID  ) 
			T2 ON T2.groupID2 = T1.groupID 
			ORDER BY T2.groupID2, T1.groupID '
	END
	--PRINT @Sql
	EXEC(@SQL)
END
GO

/****** Object:  StoredProcedure [dbo].[bpbsDeadCompanyInsert]    Script Date: 2018/12/13 19:03:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Mice 123>
-- Create date: <2014-12-05>
-- Description:	<死客户记录生成>
-- Editer: 修改Mice 2015-2-5 修改时间格式为 2015-02-05
-- =============================================
CREATE PROCEDURE [dbo].[bpbsDeadCompanyInsert]
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @begindate AS VARCHAR(100), @enddate AS VARCHAR(100), @datetime AS VARCHAR(100)
	SELECT @enddate=CONVERT(VARCHAR(100), DATEADD(MONTH,-6,getDate()), 23) ,
		@begindate=CONVERT(VARCHAR(100), DATEADD(MONTH,-7,getDate()), 23),
		@datetime=CONVERT(VARCHAR(100), DATEADD(MONTH,-1,getDate()), 23)

	IF NOT EXISTS(SELECT 'X' FROM Maindb..bsDeadCompany WITH(NOLOCK))	--初次生成遍历所有的
	BEGIN
		INSERT INTO bsDeadCompany(cpMainID,DeadDate,AddDate)
		SELECT a.ID,a.LastLoginDate,getDate()
		FROM Maindb..cpMain a WITH(NOLOCK)
		WHERE MemberType=2 AND ConsultantDate < @datetime AND LastLoginDate < @enddate
		AND (SELECT COUNT(1) FROM caOrder WITH(NOLOCK) WHERE Paid>0 AND EndDate > @datetime 
			AND (OrderType=1 OR OrderType=2 OR OrderType=8) AND cpMainID=a.id) = 0
		ORDER BY LastLoginDate
	END
	ELSE --遍历新增月份的死客户
	BEGIN
		INSERT INTO bsDeadCompany(cpMainID,DeadDate,AddDate)
		SELECT a.ID,a.LastLoginDate,getDate()
		FROM Maindb..cpMain a WITH(NOLOCK)
		WHERE MemberType=2
		AND ConsultantDate < @datetime 
		AND LastLoginDate < @enddate
		AND LastLoginDate > @begindate 
		AND (SELECT COUNT(1) FROM caOrder WITH(NOLOCK) WHERE Paid>0 AND EndDate > @datetime 
			AND (OrderType=1 OR OrderType=2 OR OrderType=8) AND cpMainID=a.id) = 0
		AND a.Id NOT IN(SELECT cpMainId FROM bsDeadCompany WITH(NOLOCK) WHERE ActiveDate IS NULL)
		ORDER BY LastLoginDate
	END
	SET NOCOUNT OFF
END

GO

/****** Object:  StoredProcedure [dbo].[bpbsDeadCompanySelect]    Script Date: 2018/12/13 19:03:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Author:		<Mice 123>
-- Create date: <2014-12-09>
-- Description:	<死客户列表查询>
-- 修改： 2014-12-12 mice 添加strWhere 查询条件
-- 修改： 2014-12-24 mice 增加查询条件（企业ID、企业名称）
-- 修改： 2014-12-30 mice 增加查询条件激活时间
--                        修改联系时间、激活时间为二选一查询
-- 修改： 2015-01-13 john 增加如果valid=0则ContactResult=0 OR IS NULL
-- 修改： 2015-01-20 NICK 加入按照联系活动搜索
-- 修改： 2015-03-20 Mice 加入查询条件 NOT (ActiveDate IS NOT NULL and ContactResult IS NULL ) 
-- 修改： 2018-5-9 Harry  加入当前会员状态
-- 修改： 2018-6-11 Harry 查询联系状态是按照联系时间排序
-- 修改： 2018-10-12 Alen 死客户列表增加是否限制登陆显示
-- =============================================
-- =============================================
CREATE PROCEDURE [dbo].[bpbsDeadCompanySelect]
(
	@deptid INT = NULL,                --部门ID
	@pid INT = NULL,                   --省份ID
	@valid VARCHAR(5) = NULL,          --联系结果
	@active INT = NULL,                --激活状态
	@manageruserid VARCHAR(50) = NULL, --顾问ID
	@begintime1 VARCHAR(50) = NULL,    --截点时间-起始时间
	@endtime1  VARCHAR(50) = NULL,     --截点时间-终止时间
	@itemtime	INT = 0,               --1.激活时间； 0.联系时间
	@begintime2  VARCHAR(50) = NULL,   --激活/联系时间起始时间
	@endtime2  VARCHAR(50) = NULL,     --激活/联系时间终止时间
	@strWhere  VARCHAR(500) = NULL,    --权限控制语句
	@searchItem INT = 0,               --0.企业ID； 1.企业名称
	@searchText VARCHAR(50) = NULL,    --企业ID/名称
	@Page SMALLINT = 1 ,                --页数
	@ContactID INT
)
AS
BEGIN
	CREATE TABLE #T(
		TitleID BIGINT,
		ID int ,
		cpMainID int,
		ContactDate smalldatetime,
		ContactMan int,
		ContactResult int,
		DeadDate smalldatetime,
		ActiveDate smalldatetime,
		ReMark nvarchar(500),
		AddDate smalldatetime,
		cpName nvarchar(500),
		ConsultantID INT,
		RegDate SMALLDATETIME,
		IsLock BIT,
		LastLoginDate SMALLDATETIME,
		Consultantname nvarchar(50),
		ContactManname nvarchar(50),
		ContactDoneDate SMALLDATETIME,
		ContactDoneID INT,
		ContactDoneMan INT,
		ContactDoneAdd SMALLDATETIME,
		ContactNotDoneDate SMALLDATETIME,
		ContactNotDoneID INT,
		ContactNotDoneMan INT,
		ContactNotDoneAdd SMALLDATETIME,
		TotalNum INT,
		HasPaid BIT,
		MemberType INT,
		IsLimitLogin INT
	)
	DECLARE @SQL AS VARCHAR(3000)
	SET @SQL = ' INSERT INTO #T 
		( TitleID, id ,cpMainID, ContactDate,ContactMan,ContactResult,DeadDate ,ActiveDate, ReMark, AddDate,
		cpName,ConsultantID ,RegDate, IsLock,LastLoginDate,MemberType,IsLimitLogin)
		SELECT TOP 3000 ROW_NUMBER() OVER ( ORDER BY a.DeadDate DESC ) AS TitleID ,a.id ,a.cpMainID, a.ContactDate,a.ContactMan,a.ContactResult,a.DeadDate ,a.ActiveDate, a.ReMark, a.AddDate,
		b.name AS cpName, b.ConsultantID, b.RegDate, b.IsLock, b.LastLoginDate,b.MemberType,b.IsLimitLogin
		FROM Maindb..bsDeadCompany a WITH(NOLOCK) , Maindb..cpMain b WITH(NOLOCK) 
		WHERE a.cpMainID=b.id  AND  NOT (ActiveDate IS NOT NULL and ContactResult IS NULL ) '
	IF @strWhere <> ''
	BEGIN
		SET @SQL = @SQL + @strWhere
	END
	IF @pid > 0
		SET @SQL = @SQL + ' AND b.dcProvinceID = ' + CONVERT(VARCHAR(10),@pid)
	IF @valid <> ''
	BEGIN
		IF @valid = '0'
			SET @SQL = @SQL + ' AND (a.ContactResult = 0 OR a.ContactResult IS NULL)'
		ELSE
			SET @SQL = @SQL + ' AND a.ContactResult = ' + @valid
	END
	IF @active = 1
		SET @SQL = @SQL + ' AND a.ActiveDate IS NOT NULL ' 
	ELSE IF @active = 2
		SET @SQL = @SQL + ' AND a.ActiveDate IS NULL ' 
	IF @manageruserid <> ''
	BEGIN
		SET @SQL = @SQL + ' AND b.ConsultantID = ' + @manageruserid
	END
	ELSE
	BEGIN
		IF @deptid > 0
			SET @SQL = @SQL + ' AND b.ConsultantID  IN ( SELECT x.id FROM maindb..manageruser x WITH(NOLOCK) WHERE x.deptid = ' + CONVERT(VARCHAR(10),@deptid) + ' ) '
	END
	IF @begintime1 <> '' 
	BEGIN
		SET @SQL = @SQL + ' AND a.DeadDate > ''' + @begintime1 +''''
	END
	IF @endtime1 <> ''
	BEGIN
		SET @SQL = @SQL + ' AND a.DeadDate < ''' + @endtime1 +''''
	END
	IF @begintime2 <> ''
	BEGIN
		IF @itemtime = 1
			SET @SQL = @SQL + ' AND a.ActiveDate > ''' + @begintime2 +''''
		ELSE
			SET @SQL = @SQL + ' AND a.ContactDate > ''' + @begintime2 +''''
	END

	--加入按照联系活动情况搜索 nick
	IF @ContactID<>0
	BEGIN
		SET @SQL = @SQL + ' AND (SELECT TOP 1 CloseDate FROM MainDB..contact WITH(NOLOCK) WHERE CompanyId = a.cpMainID ORDER BY CloseDate DESC) < ''' + CONVERT(VARCHAR(50),DATEADD (DAY ,-@ContactID, GETDATE()))+''''
	END
		
	IF @endtime2 <> ''
	BEGIN
		IF @itemtime = 1
			SET @SQL = @SQL + ' AND a.ActiveDate < ''' + @endtime2 +''''
		ELSE
			SET @SQL = @SQL + ' AND a.ContactDate < ''' + @endtime2 +''''
	END
		
	IF @searchText <> ''
	BEGIN
		IF @searchItem = 0
		BEGIN
			SET @SQL = @SQL + '  AND a.cpMainID = '+@searchText
		END
		ELSE
		BEGIN
			SET @SQL = @SQL + '  AND b.Name like  ''%' + @searchText + '%'''
		END
	END
        IF @valid>0
        BEGIN
          SET @SQL = @SQL + ' ORDER BY a.ContactDate DESC'
        END
        ELSE
        BEGIN
           SET @SQL = @SQL + ' ORDER BY a.DeadDate DESC'
        END
	

	EXEC(@SQL)

	UPDATE #T SET Consultantname = (SELECT  c.name FROM Maindb..manageruser c WHERE  c.id = #T.ConsultantID ),
		HasPaid = (SELECT TOP 1 ID FROM Maindb..caOrder WHERE Paid>0 AND OpenDate IS NOT NULL AND cpMainID=#T.cpMainID AND IsDeleted = 0),
		ContactManname = (SELECT  c.name FROM Maindb..manageruser c WHERE  c.id = #T.ContactMan ),
		ContactDoneDate = (SELECT top 1 e.BeginTime FROM maindb..contact e WITH(NOLOCK) WHERE e.companyid=#T.cpMainID AND e.status = 2 ORDER BY e.begintime DESC )  ,
		ContactDoneID = (SELECT top 1 e.ID FROM maindb..contact e WITH(NOLOCK) WHERE e.companyid=#T.cpMainID AND e.status = 2 ORDER BY e.begintime DESC )  ,
		ContactDoneMan = (SELECT top 1 e.ManagerUserID FROM maindb..contact e WITH(NOLOCK) WHERE e.companyid=#T.cpMainID AND e.status = 2 ORDER BY e.begintime DESC ) ,
		ContactDoneAdd = (SELECT top 1 e.AddDate FROM maindb..contact e WITH(NOLOCK) WHERE e.companyid=#T.cpMainID AND e.status = 2 ORDER BY e.begintime DESC ) , 
		ContactNotDoneDate = (SELECT top 1 e.BeginTime FROM maindb..contact e WITH(NOLOCK) WHERE e.companyid=#T.cpMainID AND e.status = 1 ORDER BY e.begintime DESC )  ,
		ContactNotDoneID = (SELECT top 1 e.ID FROM maindb..contact e WITH(NOLOCK) WHERE e.companyid=#T.cpMainID AND e.status = 1 ORDER BY e.begintime DESC )  ,
		ContactNotDoneMan = (SELECT top 1 e.ManagerUserID FROM maindb..contact e WITH(NOLOCK) WHERE e.companyid=#T.cpMainID AND e.status = 1 ORDER BY e.begintime DESC ) ,
		ContactNotDoneAdd = (SELECT top 1 e.AddDate FROM maindb..contact e WITH(NOLOCK) WHERE e.companyid=#T.cpMainID AND e.status = 1 ORDER BY e.begintime DESC )
	WHERE TitleID > (@Page-1)*20 AND TitleID <= @Page*20

	UPDATE  #T SET TotalNum=( SELECT COUNT(*) FROM #T ) WHERE TitleID > (@Page-1)*20 AND TitleID <= @Page * 20

	SELECT * FROM #T WITH(NOLOCK) WHERE TitleID > (@Page-1) * 20 AND TitleID <= @Page * 20
	DROP TABLE #T
END
GO

/****** Object:  StoredProcedure [dbo].[bpbsJobProblemByCpMainIDDelete]    Script Date: 2018/12/13 19:03:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2014.12.18
--创建人：Nick
--说明：限制登陆删除bsJobProblem
--#####################################################
CREATE PROCEDURE [dbo].[bpbsJobProblemByCpMainIDDelete]
(
	@CpmainID INT
)
AS
BEGIN 
	DELETE FROM bsJobProblem WHERE cpMainID = @CpmainID
End

GO

/****** Object:  StoredProcedure [dbo].[bpbsJobProblemByCpMainIDSelect]    Script Date: 2018/12/13 19:03:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2015.1.12
--创建人：Nick
--说明：根据cpmainid获取bsJobProblem
--#####################################################
CREATE PROCEDURE [dbo].[bpbsJobProblemByCpMainIDSelect]
(
	@CpMainID INT
)
AS
BEGIN 
	SELECT TOP 1 Reason,AddMan FROM bsJobProblem 
	WHERE cpMainID=@CpMainID ORDER BY AddDate DESC
End
GO

/****** Object:  StoredProcedure [dbo].[bpbsJobProblemDelete]    Script Date: 2018/12/13 19:03:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2015.1.8
--创建人：Nick
--说明：限制登陆删除bsJobProblem
--#####################################################
CREATE PROCEDURE [dbo].[bpbsJobProblemDelete]
AS
BEGIN 
	DELETE FROM bsJobProblem 
	WHERE cpMainID IN (
		SELECT ID FROM cpMain WITH(NOLOCK) 
		WHERE ID=bsJobProblem.cpMainID 
		AND (
			SELECT COUNT(1) FROM Job WHERE IsDelete=0 AND cpMainID=cpMain.ID
		)=0
	)
End
GO

/****** Object:  StoredProcedure [dbo].[bpbsJobProblemSelect]    Script Date: 2018/12/13 19:03:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2015.1.8
--创建人：Nick
--说明：获取bsJobProblem
--#####################################################
CREATE PROCEDURE [dbo].[bpbsJobProblemSelect]
AS
BEGIN 
	SELECT a.cpMainID,c.IsLimitLogin,a.Addman,c.consultantid,c.haslicence,Reason,c.RegDate,c.Name cpName,
	a.Adddate From bsJobProblem a With(Nolock),cpMain c With(Nolock) 
	WHERE a.cpMainID=c.ID
End
GO

/****** Object:  StoredProcedure [dbo].[bpbsManagerUserActionInsert]    Script Date: 2018/12/13 19:03:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-----------------
--创建日期：2010-11-15
--创建人：lambo
--功能说明：增加后台用户对单位的操作记录
--用于后台企业用户管理模块
-----------------
CREATE PROCEDURE [dbo].[bpbsManagerUserActionInsert]
(
	@cpMainID INT,
	@Action VARCHAR(100),
	@Reason VARCHAR(200),
	@ManagerUserID INT
)
AS
BEGIN TRY
	DECLARE @MaxID AS INT
	INSERT INTO bsManagerUserAction (cpMainID,Action,Reason,ManagerUserID,AddDate) 
		VALUES(@cpMainID,@Action,@Reason,@ManagerUserID,GETDATE())
	SET @MaxID = @@IDENTITY
END TRY
BEGIN CATCH
	GOTO ERR
END CATCH
RETURN @MaxID
ERR:
BEGIN
	RETURN 0
END













GO

/****** Object:  StoredProcedure [dbo].[bpbsManagerUserActionSelect]    Script Date: 2018/12/13 19:03:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--获取招聘顾问分配记录-------------------
--添加人Harry -----------------------
--添加时间：2014-12-15---------------
CREATE PROCEDURE [dbo].[bpbsManagerUserActionSelect]
(
	@WHERE VARCHAR(2000)
)
AS 
BEGIN
	DECLARE @SQL AS VARCHAR(2000)
	SET @WHERE = dbo.SafeSql(@WHERE)
	SET	@SQL='
		SELECT top 2000 *,
			(SELECT Name 
			FROM cpMain WITH(NOLOCK) 
			WHERE cpMain.Id = bsManagerUserAction.cpMainID) CompanyName,
			(SELECT regdate 
			FROM cpMain WITH(NOLOCK) 
			WHERE cpMain.Id = bsManagerUserAction.cpMainID) regdate 
		FROM bsManagerUserAction WITH(NOLOCK) 
		WHERE 1=1 '+ @WHERE
	EXEC(@SQL) 
END  

GO

/****** Object:  StoredProcedure [dbo].[bpbsMobileMessageDelete]    Script Date: 2018/12/13 19:03:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--bpbsMobileMessageDelete 100
--#####################################################
--创建时间：2015-01-28
--创建人：Andy
--说明：删除一条短信推广内容
--#####################################################
CREATE PROCEDURE [dbo].[bpbsMobileMessageDelete]
(		
	@ID INT	
)
AS
BEGIN
	DELETE FROM bsMobileMessage WHERE ID = @ID
END
GO

/****** Object:  StoredProcedure [dbo].[bpbsMobileMessageSave]    Script Date: 2018/12/13 19:03:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--bpbsMobileMessageSave 1009,32, '2015-01-01', '2016-01-01', 'TEST1', 119
--#####################################################
--创建时间：2015-01-28
--创建人：Andy
--说明：保存一条短信推广内容
--#####################################################
CREATE PROCEDURE [dbo].[bpbsMobileMessageSave]
(		
	@ID INT, --如果=0，则是insert	
	@ProvinceID INT,
	@BeginTime DATETIME,
	@EndTime DATETIME,
	@Message NVARCHAR(1000),
	@ManagerUserID INT	
)
AS
BEGIN
	IF @ID = 0
		INSERT INTO bsMobileMessage(dcProvinceId, [Message], BeginDate, EndDate, AddMan, AddDate)
		VALUES(@ProvinceID, @Message, @BeginTime, @EndTime, @ManagerUserID, GETDATE())
	ELSE
		UPDATE bsMobileMessage
		SET dcProvinceID = @ProvinceID, [Message] = @Message, BeginDate = @BeginTime, EndDate = @EndTime 
		WHERE ID = @ID
END

GO

/****** Object:  StoredProcedure [dbo].[bpbsMobileMessageSelect]    Script Date: 2018/12/13 19:03:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--bpbsMobileMessageSelect 1,0,'', '2011-01-01'
--创建人 Andy
--时间 2015-01-28
--查询短信推广内容列表
CREATE PROCEDURE [dbo].[bpbsMobileMessageSelect] 
(		
	@ProvinceID INT,	
	@DateType INT,--0:开始时间，1：结束时间，2：添加时间
	@BeginDate VARCHAR(10),	
	@EndDate VARCHAR(10)	
)
AS
BEGIN			
	DECLARE @SQL VARCHAR(1000)	
	SET @SQL = 'SELECT * FROM bsMobileMessage WITH(NOLOCK) WHERE 1 = 1 '		
	
	IF @ProvinceID > 0	
		SET @SQL = @SQL + ' AND dcProvinceID = ' + LTRIM(STR(@ProvinceID))	
	
	IF @DateType = 0
		BEGIN 		
		IF LEN(@BeginDate) > 0
			SET @SQL = @SQL + ' AND BeginDate >= ''' + @BeginDate + ''''
		IF LEN(@EndDate) > 0
			SET @SQL = @SQL + ' AND BeginDate <= ''' + @EndDate + ''''			  
		END
	IF @DateType = 1
		BEGIN 		
		IF LEN(@BeginDate) > 0
			SET @SQL = @SQL + ' AND EndDate >= ''' + @BeginDate + ''''
		IF LEN(@EndDate) > 0
			SET @SQL = @SQL + ' AND EndDate <= ''' + @EndDate + ''''			  
		END
	ELSE IF @DateType = 2
		BEGIN 		
		IF LEN(@BeginDate) > 0
			SET @SQL = @SQL + ' AND AddDate >= ''' + @BeginDate + ''''
		IF LEN(@EndDate) > 0
			SET @SQL = @SQL + ' AND AddDate <= ''' + @EndDate + ''''	  
		END	
		
	SET @SQL = @SQL + ' ORDER BY EndDate DESC, ID DESC '  
	--PRINT(@SQL)
	EXEC (@SQL)
END
GO

/****** Object:  StoredProcedure [dbo].[bpbsMobileSpreadByCountSelect]    Script Date: 2018/12/13 19:03:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--#####################################################
--创建时间：2015.2.3
--创建人：Andy
--说明：根据条件获取bsMobileSpread的行数
--#####################################################
--bpbsMobileSpreadByCountSelect 1, '20140101'
CREATE PROCEDURE [dbo].[bpbsMobileSpreadByCountSelect]
(	
	@AddMan INT, --被查询的ID
	@ExportDate VARCHAR(8) --20130805	
)
AS
BEGIN	
	SELECT Count(*) Cnt 
	FROM bsMobileSpread 
	WHERE AddMan = @AddMan AND ExportDate = @ExportDate 
END

GO

/****** Object:  StoredProcedure [dbo].[bpbsMobileSpreadByExportMobileNoSelect]    Script Date: 2018/12/13 19:03:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--bpbsMobileSpreadByExportMobileNoSelect ' ', 119
--创建人 Andy
--时间 2015-01-23
--导出用户信息员短信推广内容手机号
CREATE PROCEDURE [dbo].[bpbsMobileSpreadByExportMobileNoSelect] 
(	
	@Where VARCHAR(1000),
	@AddMan INT
)
AS
BEGIN	
	SET @Where = dbo.SafeSql(@Where)

	--查询共有多少条记录
	DECLARE @Cnt INT
	SELECT @Cnt = Count(*) 
	FROM bsMobileSpread 
	WHERE AddMan = @AddMan AND ExportDate = CONVERT(VARCHAR(10), GETDATE(), 112)

	DECLARE @CntLeave INT
	SET @CntLeave = 100 - @Cnt
	IF @CntLeave < 0
		SET @CntLeave = 100
	ELSE IF @CntLeave > 100
		SET @CntLeave = 100

	--更新日期
	DECLARE @SQL VARCHAR(1000)
	SET @SQL = '
		UPDATE bsMobileSpread 
		SET ExportDate = CONVERT(VARCHAR(8), GETDATE(), 112)' 	
	SET @SQL = @SQL + ' WHERE ID IN(SELECT TOP '+ LTRIM(STR(@Cnt)) + ' ID FROM bsMobileSpread ' 
	SET @SQL = @SQL + @Where + ' )'
	--PRINT(@SQL)
	EXEC (@SQL)	
	
	--查出结果
	SELECT MobileNO
	FROM bsMobileSpread 
	WHERE AddMan = @AddMan AND ExportDate = CONVERT(VARCHAR(10), GETDATE(), 112)
	
END
GO

/****** Object:  StoredProcedure [dbo].[bpbsMobileSpreadByExportSelect]    Script Date: 2018/12/13 19:03:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--bpbsMobileSpreadByExportSelect 1,1,1,'2010-01-01', '2011-01-01',1,'II'
--创建人 Andy
--时间 2015-01-23
--查询短信推广内容列表,用于导出
CREATE PROCEDURE [dbo].[bpbsMobileSpreadByExportSelect] 
(
	@Cnt INT,		
	@AddMan INT,
	@ProvinceID INT,	
	@BeginDate VARCHAR(10),	
	@EndDate VARCHAR(10),
	@UserType INT,	
	@CompanyName NVARCHAR(50)	
)
AS
BEGIN	
	SET @CompanyName = dbo.SafeSql(@CompanyName)
	SET @EndDate = dbo.SafeSql(@EndDate)	
	SET @BeginDate = dbo.SafeSql(@BeginDate)	
		
	DECLARE @SQL VARCHAR(1000)	
	SET @SQL = 'SELECT TOP ' + LTRIM(STR(@Cnt)) + ' * FROM bsMobileSpread WITH(NOLOCK) '	
	SET @SQL = @SQL + ' WHERE ExportDate IS NULL AND AddMan = ' + LTRIM(STR(@AddMan))	
	
	IF @ProvinceID > 0	
		SET @SQL = @SQL + ' AND ProvinceID = ' + LTRIM(STR(@ProvinceID))	
	IF LEN(@BeginDate) > 0
		SET @SQL = @SQL + ' AND AddDate >= ' + CONVERT(VARCHAR(10), @BeginDate, 120)
	IF LEN(@EndDate) > 0
		SET @SQL = @SQL + ' AND AddDate <= ' + CONVERT(VARCHAR(10), @EndDate, 120)			  	
	SET @SQL = @SQL + ' AND HasReturn = 0 And AddDate < GETDATE() - 16 '
	IF @UserType >= 0
		SET @SQL = @SQL + ' AND UserType = ' + LTRIM(STR(@UserType))	
	IF LEN(@CompanyName) > 0	
		SET @SQL = @SQL + ' AND CompanyName LIKE ''%' + LTRIM(@CompanyName) + '%'''
	
	SET @SQL = @SQL + ' ORDER BY ID DESC' 
	--PRINT(@SQL)
	EXEC (@SQL)
END

GO

/****** Object:  StoredProcedure [dbo].[bpbsMobileSpreadDelete]    Script Date: 2018/12/13 19:03:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--bpbsMobileSpreadDelete 1
--创建人 Andy
--时间 2015-01-23
--删除短信推广内容
CREATE PROCEDURE [dbo].[bpbsMobileSpreadDelete] 
(		
	@ID INT
)
AS
BEGIN	
	Delete FROM bsMobileSpread WHERE Id = @ID
END
GO

/****** Object:  StoredProcedure [dbo].[bpbsMobileSpreadSelect]    Script Date: 2018/12/13 19:03:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--bpbsMobileSpreadSelect 1,1,1,'2010-01-01', '2011-01-01',1,1,1, '中国','13889898989','ORDER BY ID DESC'
--创建人 Andy
--时间 2015-01-23
--查询短信推广内容列表
--修改：2015-01-28，Andy 修改之前用str函数处理@CompanyName参数的bug
CREATE PROCEDURE [dbo].[bpbsMobileSpreadSelect] 
(		
	@AddMan INT,
	@ProvinceID INT,	
	@DateType INT,--1:注册时间，2：返回时间，3：审核时间
	@BeginDate DATETIME,	
	@EndDate DATETIME,
	@HasReturn INT,
	@HasAccount INT,
	@UserType INT,	
	@CompanyName NVARCHAR(50),
	@MobileNo VARCHAR(11),
	@Order VARCHAR(50)	
)
AS
SET NOCOUNT ON
DECLARE @Id INT
SET @Id = @@IDENTITY
BEGIN	
	SET @CompanyName = dbo.SafeSql(@CompanyName)
	SET @MobileNo = dbo.SafeSql(@MobileNo)
	SET @Order = dbo.SafeSql(@Order)
		
	DECLARE @SQL VARCHAR(1000)	
	SET @SQL = 'SELECT TOP 2000 * FROM bsMobileSpread WITH(NOLOCK) WHERE 1 = 1 '		
	IF @AddMan > 0	
		SET @SQL = @SQL + ' AND AddMan = ' + LTRIM(STR(@AddMan))	
	IF @ProvinceID > 0	
		SET @SQL = @SQL + ' AND ProvinceID = ' + LTRIM(STR(@ProvinceID))		
	
	IF @DateType = 1
		BEGIN 		
		IF @BeginDate > '2010-1-1'
			SET @SQL = @SQL + ' AND AddDate >= ' + CONVERT(VARCHAR(10), @BeginDate, 120)
		IF @EndDate > '2010-1-1'
			SET @SQL = @SQL + ' AND AddDate <= ' + CONVERT(VARCHAR(10), @EndDate, 120)			  
		END
	ELSE IF @DateType = 2
		BEGIN 		
		IF @BeginDate > '2010-1-1'
			SET @SQL = @SQL + ' AND ReturnDate >= ' + CONVERT(VARCHAR(10), @BeginDate, 120)
		IF @EndDate > '2010-1-1'
			SET @SQL = @SQL + ' AND ReturnDate <= ' + CONVERT(VARCHAR(10), @EndDate, 120)	  
		END
	ELSE IF @DateType = 3
		BEGIN 		
		IF @BeginDate > '2010-1-1'
			SET @SQL = @SQL + ' AND AccountDate >= ' + CONVERT(VARCHAR(10), @BeginDate, 120)
		IF @EndDate > '2010-1-1'
			SET @SQL = @SQL + ' AND AccountDate <= ' + CONVERT(VARCHAR(10), @EndDate, 120)
		END	
	IF @HasReturn > 0
		SET @SQL = @SQL + ' AND HasReturn = ' + LTRIM(STR(@HasReturn))		
	IF @HasAccount > 0
		SET @SQL = @SQL + ' AND HasAccount = ' + LTRIM(STR(@HasAccount))		
	IF @UserType >= 0
		SET @SQL = @SQL + ' AND UserType = ' + LTRIM(STR(@UserType))	
	IF LEN(@CompanyName) > 0	
		SET @SQL = @SQL + ' AND CompanyName LIKE ''%' + LTRIM(@CompanyName) + '%'''
	IF LEN(@MobileNo) > 0	
		SET @SQL = @SQL + ' AND MobileNo = ' + @MobileNo 	
	IF LEN(@Order) > 0	
		SET @SQL = @SQL + ' ' + @Order 
	--PRINT(@SQL)
	EXEC (@SQL)
END 
GO

/****** Object:  StoredProcedure [dbo].[bpBsPaBackLoginInsert]    Script Date: 2018/12/13 19:03:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--创建人 Lambo
--20101111
--个人后台登陆
CREATE PROCEDURE [dbo].[bpBsPaBackLoginInsert]
(
	@paMainID INT,
	@ManagerUserID INT,
	@UID VARCHAR(20) OUTPUT
)
AS
BEGIN
	DECLARE @T AS VARCHAR(20)
	SET @T = CONVERT(VARCHAR(20), CONVERT(DECIMAL(18,12),GETDATE()))
	SET @T = RTRIM(CONVERT(VARCHAR(12), @paMainID)) + '_' + RIGHT(@T,8)
	INSERT INTO BsPaBackLogin (UnqueID,paMainID,ManagerUserID)
	VALUES(@T,@paMainID,@ManagerUserID)
	SET @UID =  @T
END













GO

/****** Object:  StoredProcedure [dbo].[bpBsPaStatusSelect]    Script Date: 2018/12/13 19:03:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[bpBsPaStatusSelect]
(
	@paMainID INT,
	@cvMainID INT,
	@StatusID INT
)
AS
BEGIN
	DECLARE @SQL AS VARCHAR(1000)
	
	SET @SQL = 'SELECT * FROM BsPaStatus WITH(NOLOCK) WHERE paMainID = ' + RTRIM(CONVERT(CHAR(10), @paMainID))
	IF @cvMainID > 0
		SET @SQL = @SQL + '	AND cvMainID = ' + RTRIM(CONVERT(CHAR(10), @cvMainID))
	IF @StatusID > 0
		SET @SQL = @SQL + '	AND StatusID = ' + RTRIM(CONVERT(CHAR(10), @StatusID))
	SET @SQL = @SQL + '	ORDER BY cvMainID,StatusID'
	EXEC(@SQL)
END



GO

/****** Object:  StoredProcedure [dbo].[bpcaCvViewLogByCpMainIDCountNoOpenSelect]    Script Date: 2018/12/13 19:03:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：20110421
--创建人：lambo
--功能说明：查询某单位一周内浏览的非开放简历数
--用于后台客户界面
-----------------
CREATE PROCEDURE [dbo].[bpcaCvViewLogByCpMainIDCountNoOpenSelect]
(
	@cpMainID INT
)
AS
BEGIN
	DECLARE @ViewCount INT
	SELECT @ViewCount=COUNT(1) FROM caCvViewLog a WITH(index(IX_CaCvViewLog_caMainId) NOLOCK),caMain b WITH(NOLOCK),cvMain c WITH(NOLOCK)
		WHERE a.caMainID = b.ID 
		AND a.CvMainID = c.ID
		AND a.AddDate>GETDATE()-7 
		AND b.cpMainID=@cpMainID
		AND c.IsOpen = 0
	RETURN @ViewCount
END













GO

/****** Object:  StoredProcedure [dbo].[bpcaCvViewLogByCpMainIDCountSelect]    Script Date: 2018/12/13 19:03:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：查询某单位一周内浏览的简历数
--用于后台企业内容显示页面
-----------------
CREATE PROCEDURE [dbo].[bpcaCvViewLogByCpMainIDCountSelect]
(
	@cpMainID INT
)
AS
BEGIN
	DECLARE @ViewCount INT
	SELECT @ViewCount=COUNT(1) FROM caCvViewLog  a WITH(index(IX_CaCvViewLog_caMainId) NOLOCK),caMain b WITH(NOLOCK)
		WHERE a.caMainID = b.ID 
		AND a.AddDate>GETDATE()-7 
		AND b.cpMainID=@cpMainID
	RETURN @ViewCount
END













GO

/****** Object:  StoredProcedure [dbo].[bpcaCvViewLogDelete]    Script Date: 2018/12/13 19:03:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：删除一条简历浏览记录记录
--用于后台企业浏览简历记录页面
-----------------
CREATE PROCEDURE [dbo].[bpcaCvViewLogDelete]
(
	@cvMainID INT,
	@caMainID INT
)
AS
BEGIN
	DELETE FROM caCvViewLog WHERE cvMainID=@cvMainID AND caMainID=@caMainID 
END













GO

/****** Object:  StoredProcedure [dbo].[bpCaCvViewLogSelect]    Script Date: 2018/12/13 19:03:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：根据条件查询企业浏览简历记录
--用于企业浏览简历记录页面
--sean update 2013-1-18
--harry 2018-8-14 增加一天内查看简历数量
-----------------
CREATE PROCEDURE [dbo].[bpCaCvViewLogSelect]
(
	@WHERE VARCHAR(1000)
)
AS
BEGIN
	DECLARE @SQL VARCHAR(2000)
	SET @Where = dbo.SafeSql(@Where)
	SET @SQL = 
	'SELECT TOP 500 b.ID, COUNT(1) cnt, max(b.Refreshdate) r, SPACE(16) Logincookies
	FROM CaCvViewLog a WITH(NOLOCK INDEX(IX_caCvViewLog_AddDate)),
		cpMain b WITH(NOLOCK),
		caMain c WITH(NOLOCK) 
	WHERE a.caMainid = c.ID 
	AND c.cpMainid = b.ID 
	@where
	GROUP BY b.ID
	HAVING Count(1) > 20
	ORDER BY cnt DESC, b.ID'
	SET @Sql = REPLACE(@Sql, '@where', ISNULL(@Where, ''))
	DECLARE @t AS TABLE(
		ID int,
		Cnt int,
		R DATETIME,
		LoginCookie Varchar(16)
	)
	INSERT @t
	EXEC(@SQL)

	UPDATE a
	SET a.Logincookie = b.Logincookies
	FROM @t a, (
		SELECT f.cpMainId, MAX(e.Logincookies) Logincookies
		FROM caLoginlog e with(nolock),
			caMain f with(nolock) 
		WHERE e.caMainID = f.ID 
		AND f.cpMainId IN(SELECT id FROM @t)
		AND e.LoginCookies <> '注册登录'
		GROUP BY f.cpMainID) b
	WHERE a.ID = b.cpMainId

	SELECT * FROM @t
END














GO

/****** Object:  StoredProcedure [dbo].[bpCaFeePageViewLogSelect]    Script Date: 2018/12/13 19:03:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--获取单位点击收费页面记录-----------
--添加人Harry -----------------------
--添加时间：2014-12-15---------------
CREATE PROCEDURE [dbo].[bpCaFeePageViewLogSelect]
(
	@WHERE VARCHAR(2000)
)
AS 
BEGIN 
	DECLARE @SQL AS VARCHAR(2000)
	SET @WHERE=dbo.SafeSql(@WHERE)
	SET	@SQL='
		SELECT TOP 2000 a.*,b.Name caName,c.MemberType,c.Balance, c.Name cpName,c.ID cpMainID,c.ConsultantID ,c.RegDate,c.LastLoginDate 
		FROM caFeePageViewLog a WITH(NOLOCK) 
		INNER JOIN caMain b WITH(NOLOCK) ON a.caMainID = b.ID 
		INNER JOIN cpMain c WITH(NOLOCK) ON b.cpMainID = c.ID 
		WHERE 1 = 1 ' + @WHERE
	--print @sql
	EXEC(@SQL)  
END  

GO

/****** Object:  StoredProcedure [dbo].[bpCaGetPasswordLogBycpMainIDSelect]    Script Date: 2018/12/13 19:03:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--lambo 20110218
--后台，查询企业取回密码日志
CREATE PROCEDURE [dbo].[bpCaGetPasswordLogBycpMainIDSelect]
(
	@cpMainID INT
)
AS
BEGIN
	SELECT a.* ,b.Name caName, c.Name cpName FROM caGetPasswordLog a WITH (NOLOCK)
		INNER JOIN caMain b  WITH (NOLOCK)
		ON a.caMainID = b.ID
		INNER JOIN cpMain c  WITH (NOLOCK)
		ON b.cpMainID = c.ID
		WHERE c.ID = @cpMainID
END













GO

/****** Object:  StoredProcedure [dbo].[bpCaLoginLogByAddDateCpMainIDSelect]    Script Date: 2018/12/13 19:03:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：根据登陆时间和企业编号企业登陆记录
--用于后台企业登陆明显页面
-----------------
CREATE PROCEDURE [dbo].[bpCaLoginLogByAddDateCpMainIDSelect]
(
	@AddDate VARCHAR(20),
	@cpMainID INT
)
AS
BEGIN
	SELECT b.cpMainID,a.cpMainName,a.AddDate,a.LoginIp,a.LoginCookies 
	FROM caloginlog a WITH(NOLOCK),caMain b WITH(NOLOCK)
	WHERE a.caMainID = b.ID
		AND CONVERT(VARCHAR(200),a.AddDate,112)<=@AddDate
		AND b.cpMainID = @cpMainID
	ORDER BY a.AddDate
END













GO

/****** Object:  StoredProcedure [dbo].[bpCaLoginLogByAddDateLoginCookiesSelect]    Script Date: 2018/12/13 19:03:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：根据登陆时间和机器码查询企业登陆记录
--用于后台非法机器码嫌疑页面
-----------------
CREATE PROCEDURE [dbo].[bpCaLoginLogByAddDateLoginCookiesSelect]
(
	@AddDate VARCHAR(30),
	@LoginCookies VARCHAR(30)
)
AS
BEGIN
	SELECT c.ID,a.cpMainName Name,a.AddDate,a.LoginCookies,a.LoginIP,c.HasLicence,c.dcProvinceID,c.consultantID,c.RegDate,d.HasPassed,d.IP,d.CheckDate,d.ID LicenceID 
	FROM caLoginLog a WITH(NOLOCK) 
		INNER JOIN caMain b WITH(NOLOCK) ON  a.caMainID = b.ID 
		INNER JOIN cpMain c WITH(NOLOCK) ON b.cpMainID = c.ID 
		LEFT JOIN cpLicence d WITH(NOLOCK) ON d.cpMainID = c.ID 
	WHERE a.LoginCookies = @LoginCookies
		AND CONVERT(VARCHAR(20),a.AddDate,112)=CONVERT(VARCHAR(20),@AddDate)
END













GO

/****** Object:  StoredProcedure [dbo].[bpCaLoginLogByAddDateLoginIPSelect]    Script Date: 2018/12/13 19:03:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpCaLoginLogByAddDateLoginIPSelect]
(
	@AddDate VARCHAR(8),
	@IP VARCHAR(31),
	@OrderBy VARCHAR(50)
)
AS
BEGIN
	DECLARE @SQL VARCHAR(2000)
	SET @SQL = 'SELECT c.ID,a.cpMainName Name,a.AddDate,a.LoginCookies,a.LoginIP,c.HasLicence,c.dcProvinceID,c.RegDate,c.consultantID,d.HasPassed,d.IP,d.CheckDate,d.ID LicenceID '
	SET @SQL = @SQL + 'FROM caLoginLog a WITH(NOLOCK) '
	SET @SQL = @SQL + 'INNER JOIN caMain b WITH(NOLOCK) ON  a.caMainID = b.ID '
	SET @SQL = @SQL + 'INNER JOIN cpMain c WITH(NOLOCK) ON b.cpMainID = c.ID '
	SET @SQL = @SQL + 'LEFT JOIN cpLicence d WITH(NOLOCK) ON d.cpMainID = c.ID '
	SET @SQL = @SQL + 'WHERE a.LoginIP = ''' + @IP + ''''
	SET @SQL = @SQL + '	AND CONVERT(VARCHAR(8),a.AddDate,112)=''' + @AddDate + ''''
	IF LEN(@OrderBy) > 0
	BEGIN
		SET @OrderBy = dbo.SafeSql(@OrderBy)
		SET @SQL = @SQL + 'ORDER BY ' + @OrderBy
	END
	EXEC(@SQL)
END













GO

/****** Object:  StoredProcedure [dbo].[bpCaLoginLogBycpMainIDOneSelect]    Script Date: 2018/12/13 19:03:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2014.12.9
--创建人：John
--说明：获取企业用户最新的登录机器码
--#####################################################
CREATE PROCEDURE [dbo].[bpCaLoginLogBycpMainIDOneSelect]
(
	@cpMainID INT
)
AS
BEGIN 
	SELECT TOP 1 LoginIp, LoginCookies
	FROM caLoginLog a, caMain b 
	WHERE a.caMainid = b.id 
	AND cpMainid = @cpMainID 
	ORDER BY a.AddDate DESC
END
GO

/****** Object:  StoredProcedure [dbo].[bpCaLoginLogBycpMainIDSelect]    Script Date: 2018/12/13 19:03:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--###########################
--创建时间：2014.12.10
--创建人：Nick
--说明：根据cpMainID获取caloginlog
--###########################
CREATE PROCEDURE [dbo].[bpCaLoginLogBycpMainIDSelect]
(
	@cpMainID INT
)
AS
BEGIN 
	SELECT TOP 1 LoginIp, LoginCookies
	FROM caLoginLog a, caMain b 
	WHERE a.caMainid = b.id 
	AND b.AccountType = 1 
	AND cpMainid = @cpMainID 
	ORDER BY a.AddDate DESC
END

GO

/****** Object:  StoredProcedure [dbo].[bpCaLoginLogByLastLoginIPSelect]    Script Date: 2018/12/13 19:03:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--Lambo 20101113
--查询单位的上次登陆IP
CREATE PROCEDURE [dbo].[bpCaLoginLogByLastLoginIPSelect]
(
    @cpMainID INT
)
AS
BEGIN
    SELECT TOP 2 a.LoginIP  
	FROM caLoginLog a WITH(NOLOCK) ,caMain b WITH(NOLOCK) 
    WHERE a.caMainID = b.ID
	AND b.cpMainID = @cpMainID
	ORDER BY a.ID DESC
END







GO

/****** Object:  StoredProcedure [dbo].[bpCaLoginLogGroupByCookiesSelect]    Script Date: 2018/12/13 19:03:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：倒序排列某天同一个机器码登录的企业数
--用于后台非法机器码登录嫌疑页面
-----------------
CREATE PROCEDURE [dbo].[bpCaLoginLogGroupByCookiesSelect]
(
	@AddDate VARCHAR(20)
)
AS
BEGIN
	SELECT COUNT(DISTINCT b.cpMainID) cnt,a.loginCookies
	FROM caloginlog a WITH(NOLOCK),caMain b WITH(NOLOCK)
	WHERE a.caMainID = b.ID
	AND CONVERT(VARCHAR(20),a.AddDate,112)=@AddDate
	GROUP BY a.loginCookies HAVING COUNT(DISTINCT b.cpMainID)>1 
	ORDER BY cnt DESC
END













GO

/****** Object:  StoredProcedure [dbo].[bpCaLoginLogGroupByIPAddDateSelect]    Script Date: 2018/12/13 19:03:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpCaLoginLogGroupByIPAddDateSelect]
(
	@Day INT
)
AS
BEGIN
	SELECT TOP 500 COUNT(DISTINCT b.cpMainID) cnt,a.loginIP 
	FROM caloginlog a WITH(NOLOCK),caMain b WITH(NOLOCK)
	WHERE a.caMainID = b.ID
	AND a.AddDate > = GETDATE() - @Day
	GROUP BY a.loginIP HAVING COUNT(DISTINCT b.cpMainID)>1
	ORDER BY cnt DESC
END













GO

/****** Object:  StoredProcedure [dbo].[bpCaLoginLogGroupByIPSelect]    Script Date: 2018/12/13 19:03:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：倒序排列某天同一个IP登录的企业数
--用于后台非法IP登录嫌疑页面
-----------------
CREATE PROCEDURE [dbo].[bpCaLoginLogGroupByIPSelect]
(
	@AddDate VARCHAR(20)
)
AS
BEGIN
	SELECT COUNT(DISTINCT b.cpMainID) cnt,a.loginIP 
	FROM caloginlog a WITH(NOLOCK),caMain b WITH(NOLOCK)
	WHERE a.caMainID = b.ID
	AND CONVERT(VARCHAR(20),a.AddDate,112)=@AddDate
	GROUP BY a.loginIP HAVING COUNT(DISTINCT b.cpMainID)>1
	ORDER BY cnt DESC
END













GO

/****** Object:  StoredProcedure [dbo].[bpCaMainByBeVerifingSelect]    Script Date: 2018/12/13 19:03:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[bpCaMainByBeVerifingSelect]
    @IDS VARCHAR(1000)
AS
BEGIN TRY
	DECLARE @caID Table(ID INT NULL)
	DECLARE @I AS INT
	SET @I= CHARINDEX(',',@IDS)

	WHILE @I >= 1
		BEGIN
			IF LEFT(@IDS, @I - 1) > '' 
				INSERT @caID VALUES(LEFT(@IDS, @I - 1))
			SET @IDS = SUBSTRING(@IDS, @I + 1, LEN(@IDS) - @I)
			SET @I = CHARINDEX(',', @IDS)
		END
	IF @I < 1
	INSERT @caID VALUES(@IDS)

    SELECT
        a.ID,
        RegDate,
        AccountType,
        UserName,
        Name,
        Gender,
        Title,
        Dept,
        TelePhone,
        Mobile,
        Fax,
        EMail,
        IsPause,
        IsDelete,
        LastModifyDate
    FROM caMain a WITH(NOLOCK), @caID b
    WHERE a.ID = b.ID
	AND a.IsDelete = 0
END TRY
BEGIN CATCH
END CATCH













GO

/****** Object:  StoredProcedure [dbo].[bpCaMainByIsPauseUpdate]    Script Date: 2018/12/13 19:03:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--Lambo 20101114
--修改个单位非管理员用户的状态
--用于后台单位显示页面

CREATE PROCEDURE [dbo].[bpCaMainByIsPauseUpdate]
(
	@ID INT,
	@maxUserNumber INT,
	@cpMainId INT
)
AS
BEGIN
	BEGIN TRY
		DECLARE @IsPause BIT
		SELECT @IsPause = IsPause FROM caMain WHERE ID = @ID
		IF @IsPause=1
		BEGIN
			DECLARE @UserNum AS INT
			SELECT @UserNum=COUNT(*) FROM caMain 
				WHERE IsDelete=0 AND IsPause=0 AND AccountType<>1 AND cpMainID=@cpMainID
			IF @maxUserNumber<=@UserNum
				GOTO err
		END
		ELSE
		BEGIN
			UPDATE Job Set IssueEnd = GETDATE()-1 WHERE caMainID = @ID AND cpMainID=@cpMainID
		END 
		UPDATE caMain SET IsPause = IsPause-1 WHERE ID=@ID
		RETURN 1
	END TRY
	BEGIN CATCH
		RETURN 0
	END CATCH
END
err:
RETURN -1













GO

/****** Object:  StoredProcedure [dbo].[bpCaMainByIsShowEmailContactUpdate]    Script Date: 2018/12/13 19:03:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpCaMainByIsShowEmailContactUpdate]
(
	@caMainId INT
)
AS
SET NOCOUNT ON
BEGIN
	UPDATE caMain SET IsShowEmailContact = ISNULL(IsShowEmailContact, 0) - 1 WHERE Id = @caMainId
	DECLARE @Show INT
	SELECT @Show = IsShowEmailContact FROM caMain WITH(NOLOCK) WHERE Id = @caMainId
	SELECT @caMainId caMainId, @Show ShowContact
END
GO

/****** Object:  StoredProcedure [dbo].[bpCaMainByJobIDSelect]    Script Date: 2018/12/13 19:03:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2015.1.12
--创建人：Nick
--说明：删除职位获取相关信息
--#####################################################
CREATE PROCEDURE [dbo].[bpCaMainByJobIDSelect]
(
	@JobID VARCHAR(100)
)
AS
BEGIN 
	SELECT a.ID cpmainID,a.Name,b.Email,a.dcSubSiteID,c.Name JobName 
	FROM cpMain a With(Nolock),caMain b With(Nolock),Job c With(Nolock) 
	WHERE c.cpMainID=a.ID 
	AND a.ID=b.cpMainID 
	AND b.AccountType=1 
	AND c.ID IN(SELECT * FROM dbo.fnSplit(@JobID,','))
End
GO

/****** Object:  StoredProcedure [dbo].[bpCaMainDelete]    Script Date: 2018/12/13 19:03:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--lambo 20101115
--后台删除单位非管理员用户
--用于后台单位显示页面
CREATE PROCEDURE [dbo].[bpCaMainDelete]
	@ID INT
AS
BEGIN TRAN
	BEGIN TRY
		UPDATE Job SET IsDelete=1,valid=0 WHERE caMainID = @ID
		DELETE FROM bsCpModify WHERE caMainID = @ID
		UPDATE caMain SET IsDelete=1 WHERE ID=@ID
	END TRY
	BEGIN CATCH
		GOTO ERR
	END CATCH
COMMIT TRAN
RETURN 1
ERR:
BEGIN
	ROLLBACK TRAN
	RETURN 0
END













GO

/****** Object:  StoredProcedure [dbo].[bpCampusBrochureSelect]    Script Date: 2018/12/13 19:03:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Mice 123>
-- Create date: <2014-12-22>
-- Description:	<获取招聘简章列表>
-- 修改： AttachmentName参数的类型为NVARCHAR
-- =============================================
CREATE PROCEDURE [dbo].[bpCampusBrochureSelect]
(
	@Where VARCHAR(500) = '',
	@Page INT = 1
)
AS
BEGIN
	CREATE TABLE #T(
		TitleID BIGINT,
		ID INT ,
		CompanyID INT,
		Title NVARCHAR(100),
		Description NVARCHAR(MAX),
		EndDate SMALLDATETIME,
		[FileName] VARCHAR(100),
		AttachmentName NVARCHAR(100),
		ClickNumber INT,
		AddMan SMALLINT,
		AddDate SMALLDATETIME,
		Source VARCHAR(500),
		SourceName VARCHAR(100),
		OrderBy INT, 
		addManName NVARCHAR(50),
		SubSiteUrl VARCHAR(100),
		SubSiteNames NVARCHAR(300),
		TotalNum INT
	)

	DECLARE @SQL AS VARCHAR(5000)

	SET @SQL = 'INSERT INTO #T
				( TitleID,  ID,CompanyID,Title,Description,EndDate,[FileName],AttachmentName,ClickNumber,AddMan,AddDate,Source,SourceName,OrderBy )
				SELECT ROW_NUMBER() OVER ( ORDER BY a.ID DESC ) AS TitleID , a.ID, a.CompanyID,a.Title,a.Description,a.EndDate,a.FileName,a.AttachmentName,
				a.ClickNumber,a.AddMan, a.AddDate, a.Source, a.SourceName, a.OrderBy
				FROM marketdb..CampusBrochure a WITH(NOLOCK)   
				WHERE 1 = 1   ' 
	IF @Where <> ''
	BEGIN
		SET @Where = dbo.SafeSql(@Where)
		SET @SQL = @SQL + @Where
	END
	SET @SQL = @SQL + '  ORDER BY a.ID DESC '
	EXEC(@SQL)
	UPDATE #T
	SET  addManName = (SELECT TOP 1 [Name] FROM maindb..manageruser y  WITH(NOLOCK)
	WHERE y.id = #T.AddMan), SubSiteUrl = (SELECT TOP 1 y.SubSiteUrl FROM marketdb..CampusBrochureSite x WITH(NOLOCK),maindb..dcSubSite y WITH(NOLOCK) WHERE x.dcSubsiteid = y.id AND CampusBrochure=#T.id),
		SubSiteNames = STUFF((SELECT ','+CONVERT(VARCHAR,y.SubSIteCity) FROM marketdb..CampusBrochureSite x WITH(NOLOCK), maindb..dcSubSite y WITH(NOLOCK)  WHERE x.dcSubsiteid = y.id AND CampusBrochure=#T.ID FOR XML PATH ('')),1,1,'')
	WHERE TitleID > (@Page - 1) * 20
	AND TitleID <= @Page*20

	UPDATE #T
	SET TotalNum = (SELECT COUNT(ID) FROM #T)
	WHERE TitleID > (@Page-1)*20
	AND TitleID <= @Page*20

	SELECT * FROM #T WITH(NOLOCK) WHERE TitleID > (@Page-1)*20 AND TitleID <= @Page*20
	DROP TABLE #T
END
GO

/****** Object:  StoredProcedure [dbo].[bpCaOperationLogSelect]    Script Date: 2018/12/13 19:03:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpCaOperationLogSelect]
(
    @cpMainID INT,
	@Operation INT
)
AS
BEGIN
    SELECT a.* FROM caOperationLog a WITH(NOLOCK),caMain b WITH(NOLOCK)
    WHERE a.caMainID = b.ID 
		AND b.cpMainID=@cpMainID
		AND a.Operation =@Operation
	ORDER BY a.ID DESC
END













GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderBy7DayValidSelect]    Script Date: 2018/12/13 19:03:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--根据cpmainid获取有效期>7天的订单
--Lucifer 2014-12-19
CREATE PROCEDURE [dbo].[bpcaOrderBy7DayValidSelect] 
(
	@cpMainID INT
) 
AS 
BEGIN 
	SELECT * FROM maindb..caOrder WITH(NOLOCK)
	WHERE IsDeleted = 0 AND BeginDate<GETDATE() AND EndDate>GETDATE()+7 AND cpMainID=@cpMainID
	AND OrderType = 8
END

GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderByActiveQuotaOpenDateUpdate]    Script Date: 2018/12/13 19:03:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--开通简历下载数订单 Lucifer 2014-12-24
CREATE PROCEDURE [dbo].[bpcaOrderByActiveQuotaOpenDateUpdate]
(
	@BeginDate SMALLDATETIME,
	@EndDate SMALLDATETIME,
	@OpenMan SMALLINT,
	@OrderID INT,
	@ActiveQuota INT
)
AS
BEGIN
	UPDATE MainDB..caOrder SET
		BeginDate=@BeginDate,
		EndDate=@EndDate,
		OpenDate=GetDate(),
		OpenMan=@OpenMan,
		ActiveQuota=@ActiveQuota,
		RemainQuota=@ActiveQuota
	WHERE ID=@OrderID
END

GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderByContractUpdate]    Script Date: 2018/12/13 19:03:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--更新合同影像 Lucifer 2014-12-9
CREATE PROCEDURE [dbo].[bpcaOrderByContractUpdate]
(
	@ContractFile VARCHAR(50),
	@OrderID INT
)
AS
BEGIN
	UPDATE MainDB..caOrder SET ContractFile=@ContractFile WHERE ID=@OrderID
END

GO

/****** Object:  StoredProcedure [dbo].[bpCaorderByCpMainIDSelect]    Script Date: 2018/12/13 19:03:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--创建人 Andy
--时间 2014-12-31
--按照条件查询一个企业的订单
CREATE PROCEDURE [dbo].[bpCaorderByCpMainIDSelect]
(		
	@CpMainID INT,
	@OpenDate VARCHAR(20)	
)
AS
BEGIN	
	SELECT ID,OpenDate,Paid,OrderType,EndDate 
	FROM CaOrder WITH(NOLOCK) 
	WHERE cpMainID=@CpMainID AND Paid>0 AND MainOrderID IS NULL AND OpenDate<@OpenDate
	ORDER BY OpenDate DESC	
END
GO

/****** Object:  StoredProcedure [dbo].[bpCaOrderByCpMainIDValidVipSelect]    Script Date: 2018/12/13 19:03:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2014.12.9
--创建人：John
--说明：根据公司编号获取会员订单资费明细编号
--修改harry2016-12-21显示续费订单
--#####################################################
CREATE PROCEDURE [dbo].[bpCaOrderByCpMainIDValidVipSelect]
(
	@cpMainID INT
)
AS
BEGIN
	SELECT dcFeeDetailID
	FROM caOrder WITH(NOLOCK) 
	WHERE cpMainid = @cpMainID
	AND EndDate > GETDATE() - 1 
	AND OrderType = 8
	AND OpenDate IS NOT NULL
	AND IsDeleted = 0
END
 
GO

/****** Object:  StoredProcedure [dbo].[bpCaOrderByDeletedSelect]    Script Date: 2018/12/13 19:03:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--###############################
--Andy.Yin 2014-12-2
--###############################
CREATE PROCEDURE [dbo].[bpCaOrderByDeletedSelect]
(
    @BankType INT,
    @OrderType INT,
    @SearchItem INT,
    @SearchOption INT,
    @SearchText NVARCHAR(200),
    @Where VARCHAR(200)    
)
AS
BEGIN
    SET @SearchText = dbo.SafeSQL(@SearchText)
    SET @Where = dbo.SafeSQL(@Where) 
       
    DECLARE @SQL AS VARCHAR(2000)
    SET @SQL = 'SELECT Top 500 OrderType,PayMethod,ServiceType,id,Paid,Accounts,AddDate,OpenDate,RemittanceDate,manageruserid,companyname,cpMainID,DeletedMan,DeletedDate 
    FROM caOrder with(nolock) WHERE IsDeleted=1 '
    
    --付款类型
    IF @BankType<>0
        SET @SQL = @SQL + ' AND PayMethod =' + CAST(@BankType AS NVARCHAR(10))
    --订单类型
    IF @OrderType<>0
        SET @SQL = @SQL + ' AND OrderType =' + CAST(@OrderType AS NVARCHAR(10))        
    --搜索条件
    IF @SearchItem=1        
    BEGIN
        IF @SearchOption=0--等于
            SET @SQL = @SQL + ' AND ID = ''' +  @SearchText + ''''
        ELSE
            SET @SQL = @SQL + ' AND ID LIKE ''%' +  @SearchText + '%'''
    END
    ELSE IF @SearchItem=2 
    BEGIN
        IF @SearchOption=0--等于
            SET @SQL = @SQL + ' AND CompanyName = ''' +  @SearchText+ ''''
        ELSE
            SET @SQL = @SQL + ' AND CompanyName LIKE ''%' +  @SearchText + '%'''
    END
    ELSE IF @SearchItem=3 
    BEGIN
        IF @SearchOption=0--等于
            SET @SQL = @SQL + ' AND cpMainID = ''' +  @SearchText + ''''
        ELSE
            SET @SQL = @SQL + ' AND cpMainID LIKE ''%' +  @SearchText + '%'''
    END
    ELSE IF @SearchItem=4 
    BEGIN
        IF @SearchOption=0--等于
            SET @SQL = @SQL + ' AND manageruserid = ''' +  @SearchText + ''''
        ELSE
            SET @SQL = @SQL + ' AND manageruserid LIKE ''%' +  @SearchText + '%'''
    END
    ELSE IF @SearchItem=5 
    BEGIN
        IF @SearchText='1'
            SET @SQL = @SQL + ' AND OpenDate Is Not Null'
        ELSE IF @SearchText='0'
            SET @SQL = @SQL + ' AND OpenDate Is Null'
    END
    ELSE IF @SearchItem=6 
    BEGIN
        IF @SearchText='1'
            SET @SQL = @SQL + ' AND ReceiveDate Is Not Null'
        ELSE IF @SearchText='0'
            SET @SQL = @SQL + ' AND ReceiveDate Is Null'
    END
    ELSE IF @SearchItem=7 
    BEGIN
        IF @SearchText='1'
            SET @SQL = @SQL + ' AND NeedInvoice=1'
        ELSE IF @SearchText='0'
            SET @SQL = @SQL + ' AND NeedInvoice=0'
    END        
    
    IF LEN(@WHERE)>0
        SET @SQL=@SQL + ' ' + @WHERE
	
	SET @SQL=@SQL + '  Order By DeletedDate Desc'
    PRINT @SQL
    EXEC(@SQL)    
END
GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderByInvoiceUpdate]    Script Date: 2018/12/13 19:03:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--修改订单发票信息
--Lucifer 2014-12-19
CREATE PROCEDURE [dbo].[bpcaOrderByInvoiceUpdate] 
(
	@InvoiceAskDate SMALLDATETIME,
	@NeedInvoice BIT,
	@InvoiceConfirmDate SMALLDATETIME,
	@InvoiceConfirmMan SMALLINT,
	@InvoiceTitle NVARCHAR(50),
	@MailAddress NVARCHAR(100),
	@Recipient NVARCHAR(100),
	@zip CHAR(6),
	@PostTelephone VARCHAR(50),
	@Tariff VARCHAR(30),
	@RegisterAddress NVARCHAR(100),
	@RegisterTel VARCHAR(50),
	@BankName NVARCHAR(50),
	@BankAccount NVARCHAR(50),
	@OrderID INT
) 
AS
SET NOCOUNT ON
BEGIN
	IF @InvoiceConfirmDate IS NULL
		UPDATE caOrder SET
			InvoiceAskDate=@InvoiceAskDate,
			NeedInvoice=@NeedInvoice,
			InvoiceTitle=@InvoiceTitle,
			MailAddress=@MailAddress,
			Recipient=@Recipient,
			zip=@zip,
			IsBackSubmit=1,
			PostTelephone=@PostTelephone,
			Tariff=@Tariff,
			RegisterAddress=@RegisterAddress,
			RegisterTel=@RegisterTel,
			BankName=@BankName,
			BankAccount=@BankAccount
		WHERE ID=@OrderID
	ELSE
		UPDATE caOrder SET
			InvoiceAskDate=@InvoiceAskDate,
			NeedInvoice=@NeedInvoice,
			InvoiceConfirmDate=@InvoiceConfirmDate,
			InvoiceConfirmMan=@InvoiceConfirmMan,
			InvoiceTitle=@InvoiceTitle,
			MailAddress=@MailAddress,
			Recipient=@Recipient,
			zip=@zip,
			IsBackSubmit=1,
			PostTelephone=@PostTelephone,
			Tariff=@Tariff,
			RegisterAddress=@RegisterAddress,
			RegisterTel=@RegisterTel,
			BankName=@BankName,
			BankAccount=@BankAccount
		WHERE ID=@OrderID
END

GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderByIsDeleteHasOpenUpdate]    Script Date: 2018/12/13 19:03:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--已开通的订单删除 Lucifer 2014-12-9
CREATE PROCEDURE [dbo].[bpcaOrderByIsDeleteHasOpenUpdate]
(
	@OrderID INT,
	@DeletedMan SMALLINT,
	@cpMainID INT,
	@Reason VARCHAR(200)
)
AS
SET NOCOUNT ON
IF NOT EXISTS(SELECT 'x' FROM caOrder WHERE ReceiveDate IS NULL AND ID = @OrderID)
	RETURN
BEGIN TRAN
	DECLARE @UsePoint AS INT, @UseCoin AS INT, @GivePoint INT
	--是否是积分订单
	SELECT @UsePoint = PaidPoint, @GivePoint = GivePoint, @UseCoin = PaidCoin
	FROM caOrder WITH(NOLOCK) 
	WHERE ID = @OrderID

	IF @UsePoint > 0  --积分订单
	BEGIN
		--插入积分记录
		INSERT INTO cpPoint (cpMainID, changeID, point, relativeID, lastModifyDate)
		VALUES(@cpMainID, 16, @UsePoint, @OrderID, GETDATE())
	END

	IF @GivePoint > 0  --积分订单
	BEGIN
		--插入积分记录
		INSERT INTO cpPoint (cpMainID, changeID, point, relativeID, lastModifyDate)
		VALUES(@cpMainID, 24, @GivePoint * -1, @OrderID, GETDATE())
	END

	IF @UseCoin > 0
	BEGIN
		--插入金币记录
		BEGIN
			IF (SELECT COUNT(*) FROM cpCoin WITH(NOLOCK) WHERE caOrderId = @OrderID) = 1
			BEGIN
				DECLARE @Remaincoin INT
				SELECT @Remaincoin = ISNULL(Remaincoin, 0) FROM cpMain WITH(NOLOCK) WHERE Id = @cpMainID

				INSERT INTO cpCoin(cpMainId, caOrderId, Coin, IsCancel, Balance, AddDate)
				SELECT @cpMainId, @OrderID, @UseCoin, 1, @UseCoin + @Remaincoin, GETDATE()
			END
		END
	END

	SET @Reason = dbo.SafeSQL(@Reason)
	UPDATE caOrder SET OpenDate = NULL, IsDeleted=1, DeletedDate=GETDATE(), DeletedMan=@DeletedMan WHERE ReceiveDate IS NULL AND ID = @OrderID
	INSERT INTO caOrderRemarks(ManagerID,Remarks,OrderID) VALUES(@DeletedMan,@Reason,@OrderID)
	EXEC spRedifinePrivilage @cpMainID
COMMIT TRAN
RETURN
ERR:
BEGIN
	ROLLBACK TRAN
	RETURN
END
GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderByIsDeleteUpdate]    Script Date: 2018/12/13 19:03:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--未开通订单删除 Lucifer 2014-12-9
--修改：harry 增加积分，金币返还 2015-7-7
CREATE PROCEDURE [dbo].[bpcaOrderByIsDeleteUpdate]
(
	@OrderID INT, 
	@DeletedMan SMALLINT
)
AS
SET NOCOUNT ON
BEGIN TRAN
	IF NOT EXISTS(SELECT 'x' FROM caOrder WHERE OpenDate IS NULL AND ID=@OrderID)
		RETURN

	DECLARE @UsePoint AS INT, @UseCoin AS INT, @cpMainID AS INT, @GivePoint INT

	--是否是积分订单
	SELECT @UsePoint = PaidPoint, @GivePoint = GivePoint, @UseCoin = PaidCoin, @cpMainID = cpMainID 
	FROM caOrder WITH(NOLOCK) 
	WHERE ID = @OrderID

	IF @UsePoint > 0  --积分订单
	BEGIN
		--插入积分记录
		INSERT INTO cpPoint (cpMainID, changeID, point, relativeID, lastModifyDate)
		VALUES(@cpMainID, 16, @UsePoint, @OrderID, GETDATE())
	END

	IF @GivePoint > 0  --积分订单
	BEGIN
		--插入积分记录
		INSERT INTO cpPoint (cpMainID, changeID, point, relativeID, lastModifyDate)
		VALUES(@cpMainID, 24, @GivePoint * -1, @OrderID, GETDATE())
	END

	IF @UseCoin > 0
	BEGIN
		--插入金币记录
		BEGIN
			IF (SELECT COUNT(*) FROM cpCoin WITH(NOLOCK) WHERE caOrderId = @OrderID) = 1
			BEGIN
				DECLARE @Remaincoin INT
				SELECT @Remaincoin = ISNULL(Remaincoin, 0) FROM cpMain WITH(NOLOCK) WHERE Id = @cpMainID

				INSERT INTO cpCoin(cpMainId, caOrderId, Coin, IsCancel, Balance, AddDate)
				SELECT @cpMainId, @OrderID, @UseCoin, 1, @UseCoin + @Remaincoin, GETDATE()
			END
		END
	END
	UPDATE caOrder SET IsDeleted=1, DeletedDate = GETDATE(), DeletedMan=@DeletedMan 
	WHERE OpenDate IS NULL AND ID=@OrderID
COMMIT TRAN
RETURN 1
ERR:
BEGIN
	ROLLBACK TRAN
	RETURN 0
END
GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderByJobNumberOpenDateUpdate]    Script Date: 2018/12/13 19:03:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--开通职位数订单 Lucifer 2014-12-24
CREATE PROCEDURE [dbo].[bpcaOrderByJobNumberOpenDateUpdate]
(
	@BeginDate SMALLDATETIME,
	@EndDate SMALLDATETIME,
	@OpenMan SMALLINT,
	@OrderID INT,
	@JobNumber INT
)
AS
BEGIN
	UPDATE MainDB..caOrder SET
		BeginDate=@BeginDate,
		EndDate=@EndDate,
		OpenDate=GetDate(),
		OpenMan=@OpenMan,
		JobNumber=@JobNumber
	WHERE ID=@OrderID
END

GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderByJobRefrshQuotaOpenDateUpdate]    Script Date: 2018/12/13 19:03:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--开通职位刷新数订单 Harry 2017-6-7
CREATE PROCEDURE [dbo].[bpcaOrderByJobRefrshQuotaOpenDateUpdate]
(
	@BeginDate SMALLDATETIME,
	@EndDate SMALLDATETIME,
	@OpenMan SMALLINT,
	@OrderID INT,
	@SmsNumber INT
)
AS
SET NOCOUNT ON
BEGIN
	UPDATE MainDB..caOrder SET
		BeginDate = @BeginDate,
		EndDate = @EndDate,
		OpenDate = GetDate(),
		OpenMan = @OpenMan,
		jobRefreshQuota = @SmsNumber,
		remainJobRefreshQuota = @SmsNumber 
	WHERE ID=@OrderID
END
SET NOCOUNT OFF
 
GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderByLatestSelect]    Script Date: 2018/12/13 19:03:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--根据企业ID获取最近的正在执行的某类型订单，订单确认用 Lucifer 2014-12-24
CREATE PROCEDURE [dbo].[bpcaOrderByLatestSelect]
(
	@cpMainID INT,
	@OrderType SMALLINT
)
AS
BEGIN
	SELECT Top 1 BeginDate,EndDate FROM caOrder WITH(NOLOCK)
	WHERE cpMainId=@cpMainID AND OrderType=@OrderType AND EndDate>GETDATE() AND OpenDate IS NOT NULL AND IsDeleted=0
	ORDER BY EndDate DESC
END

GO

/****** Object:  StoredProcedure [dbo].[bpCaOrderByOpenDateSelect]    Script Date: 2018/12/13 19:03:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--创建人 Andy
--时间 2014-12-03
--后台按照时间查询没有招聘顾问的订单
CREATE PROCEDURE [dbo].[bpCaOrderByOpenDateSelect]
(   
    @StartTime Datetime, 
    @EndTime Datetime
)
AS
BEGIN
	--有招聘顾问的
	SELECT Paid, DeptId, ManagerUserId 
	FROM caOrder a WITH(NOLOCK), Manageruser b  WITH(NOLOCK)
	WHERE a.manageruserid=b.id AND a.opendate >= @StartTime and a.opendate<=@EndTime	
	--AND a.Paid > 0 AND a.Isdeleted = 0

	--没有招聘顾问的
	SELECT Paid, b.id 
	FROM MainDB..caOrder a WITH(NOLOCK), MainDB..cpMain b WITH(NOLOCK)
	WHERE a.cpMainID=b.id AND a.opendate >= @StartTime AND a.opendate<=@EndTime AND a.manageruserid IS NULL
	--AND a.Paid > 0 AND a.Isdeleted = 0
END
GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderByOpenDateUpdate]    Script Date: 2018/12/13 19:03:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--开通订单 Lucifer 2014-12-24
CREATE PROCEDURE [dbo].[bpcaOrderByOpenDateUpdate]
(
	@BeginDate SMALLDATETIME,
	@EndDate SMALLDATETIME,
	@OpenMan SMALLINT,
	@OrderID INT
)
AS
BEGIN
	UPDATE MainDB..caOrder SET
		BeginDate=@BeginDate,
		EndDate=@EndDate,
		OpenDate=GetDate(),
		OpenMan=@OpenMan
	WHERE ID=@OrderID
END

GO

/****** Object:  StoredProcedure [dbo].[bpCaOrderByReceiveDateSelect]    Script Date: 2018/12/13 19:03:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--创建人 Andy
--时间 2014-12-03
--后台按照接收时间查询订单
CREATE PROCEDURE [dbo].[bpCaOrderByReceiveDateSelect]
(   
    @StartTime VARCHAR(20), 
    @EndTime VARCHAR(20),
    @Where VARCHAR(200)
)
AS
BEGIN	
	SET @Where = dbo.SafeSQL(@Where)
	
	DECLARE @SQL AS VARCHAR(2000)
    SET @SQL = 'SELECT a.Id ,b.Id CompanyId,b.Name CompanyName,ReceiveDate,b.RegDate,a.AddDate,a.OpenDate,a.Paid,a.OrderType 
	FROM CaOrder a WITH(NOLOCK),CpMain b WITH(NOLOCK) 
	WHERE a.Paid>0 AND a.cpMainID=b.id
	AND ReceiveDate > ''' + @StartTime + '''
    AND ReceiveDate < ''' + @EndTime + ''''
    IF LEN(@Where) > 0
		SET @SQL = @SQL + @Where
	SET @SQL = @SQL + ' ORDER BY a.OpenDate'
	--PRINT @SQL
    EXEC(@SQL)  
END
GO

/****** Object:  StoredProcedure [dbo].[bpCaOrderByReceiveDateUpdate]    Script Date: 2018/12/13 19:03:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--更新订单到账日期和到账人员 Lucifer 2014-12-24
CREATE PROCEDURE [dbo].[bpCaOrderByReceiveDateUpdate]
(
	@OrderID INT,
	@ReceiveMan SMALLINT
)
AS
BEGIN
	UPDATE MainDB..caOrder SET ReceiveDate=GetDate(),ReceiveMan=@ReceiveMan WHERE ID=@OrderID
END

GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderByRemittanceFileUpdate]    Script Date: 2018/12/13 19:03:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2014.12.2
--创建人：Lucifer
--说明：上传汇款凭证更新
--harry 2018-5-19 增加 isdocopen
--#####################################################
CREATE PROCEDURE [dbo].[bpcaOrderByRemittanceFileUpdate]
(
	@OrderID INT,
	@RemittanceFile VARCHAR(100),
        @IsDocOpen INT=0--1 合同图片 0 汇款单图片
)
AS
BEGIN
	DECLARE @CurrentFile AS VARCHAR(100)
	SELECT @CurrentFile = RemittanceFile FROM caOrder WHERE ID = @OrderID
	IF ISNULL(@CurrentFile,'') = ''
		UPDATE caOrder SET RemittanceDate = GETDATE(),RemittanceFile = @RemittanceFile WHERE ID = @OrderID
	ELSE
		UPDATE caOrder SET RemittanceDate = GETDATE(),RemittanceFile = RemittanceFile + '|' + @RemittanceFile WHERE ID = @OrderID
	IF @IsDocOpen=1
		UPDATE caOrder SET IsDocOpen =1 WHERE  ID = @OrderID --合同图片
	IF @IsDocOpen=0
		UPDATE caOrder SET IsDocOpen =2 WHERE  ID = @OrderID AND IsDocOpen =1 --汇款单图片
END
GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderBySmsNumberOpenDateUpdate]    Script Date: 2018/12/13 19:03:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--开通短信数订单 Harry 2017-6-7
CREATE PROCEDURE [dbo].[bpcaOrderBySmsNumberOpenDateUpdate]
(
	@BeginDate SMALLDATETIME,
	@EndDate SMALLDATETIME,
	@OpenMan SMALLINT,
	@OrderID INT,
	@SmsNumber INT
)
AS
SET NOCOUNT ON
BEGIN
	UPDATE MainDB..caOrder SET
		BeginDate = @BeginDate,
		EndDate = @EndDate,
		OpenDate = GetDate(),
		OpenMan = @OpenMan,
		SmsQuota = @SmsNumber,
		RemainSmsQuota = @SmsNumber 
	WHERE ID=@OrderID
END
SET NOCOUNT OFF
GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderBySumSelect]    Script Date: 2018/12/13 19:03:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpcaOrderBySumSelect]
(
    @cpMainID INT
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @ActiveQuota INT
    DECLARE @UserNumber INT
    DECLARE @JobNumber INT
    DECLARE @RemainQuota INT
    DECLARE @RemainSmsQuota INT
    DECLARE @VIPDate DATETIME 

    SELECT @ActiveQuota = SUM(ActiveQuota),@UserNumber=SUM(UserNumber),@JobNumber=SUM(JobNumber),@VIPDate=MAX(EndDate),@RemainQuota=SUM(RemainQuota),@RemainSmsQuota=SUM(RemainSmsQuota)
    FROM caOrder WITH(NOLOCK)
    WHERE BeginDate < GETDATE() 
    AND EndDate > GETDATE()
    AND OrderType IN (8,9,10,11,14)
    AND cpMainID = @cpMainID
    AND OpenDate IS NOT NULL
    
    SELECT @ActiveQuota as ActiveQuota ,@UserNumber as UserNumber,@JobNumber as JobNumber,@VIPDate as VIPDate,@RemainQuota as RemainQuota,@RemainSmsQuota as RemainSmsQuota
END
GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderByUserNumberOpenDateUpdate]    Script Date: 2018/12/13 19:03:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--开通用户数订单 Lucifer 2014-12-24
CREATE PROCEDURE [dbo].[bpcaOrderByUserNumberOpenDateUpdate]
(
	@BeginDate SMALLDATETIME,
	@EndDate SMALLDATETIME,
	@OpenMan SMALLINT,
	@OrderID INT,
	@UserNumber INT
)
AS
BEGIN
	UPDATE MainDB..caOrder SET
		BeginDate=@BeginDate,
		EndDate=@EndDate,
		OpenDate=GetDate(),
		OpenMan=@OpenMan,
		UserNumber=@UserNumber
	WHERE ID=@OrderID
END

GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderRemarksByOrderIDSelect]    Script Date: 2018/12/13 19:03:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--根据cpmainid获取订单备注 Lucifer 2014-12-9
CREATE PROCEDURE [dbo].[bpcaOrderRemarksByOrderIDSelect]
(
	@OrderID INT
)
AS
BEGIN
	SELECT * FROM MainDB..caOrderRemarks WITH(NOLOCK) WHERE OrderID=@OrderID
END
GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderSelect]    Script Date: 2018/12/13 19:03:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--#####################################################
--创建时间：2014.12.1
--创建人：Lucifer
--说明：根据条件获取订单列表
--修改：harry 2018-5-21 增加isdocopen
--修改：sunshine 2018-8-15 增加 dcRegionID
--#####################################################
CREATE PROCEDURE [dbo].[bpcaOrderSelect]
(
	@Where VARCHAR(2000), 
	@Order VARCHAR(200)
)
AS
SET NOCOUNT ON
BEGIN
	DECLARE @SQL AS VARCHAR(MAX)
	SELECT @Where = dbo.SafeSql(@Where), @Order = dbo.SafeSql(@Order)
	SET @SQL = '
	Select Top 1000 Balance, ReceiveDate, FaxFile, OrderType, ProvinceID, PayMethod, ID, Paid, dcFeeDetailID, 
		Accounts, AddDate, OpenDate, RemittanceDate, ManageruserID, CompanyName, cpMainID, ServiceType, PlaceWeek, ContractFile, 
		NeedInvoice, InvoiceConfirmDate, OpenMan, InvoiceCompany, InvoiceMonth, PaidCoin, CancelReason, EndDate, BeginDate,
		(Select ConsultantID From cpMain WITH(NOLOCK) Where ID = caOrder.cpMainID) ConsultantID, isdocopen,(Select dcRegionID From cpMain WITH(NOLOCK) Where ID = caOrder.cpMainID) dcRegionID
	From caOrder With(Nolock)
	Where 1=1' + 
	@Where + 
	@Order
	--insert into a2 select @sql, getdate()
	EXEC(@SQL)
END
GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderSUMSelect]    Script Date: 2018/12/13 19:03:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--Lambo 20101117
--获取企业的购买的简历下载数，职位数，用户数
CREATE PROCEDURE [dbo].[bpcaOrderSUMSelect]
	@cpMainID INT,
	@ActiveQuota INT OUTPUT,
	@UserNumber INT OUTPUT,
	@JobNumber INT OUTPUT,
	@RemainQuota INT OUTPUT,
	@VIPDate DATETIME OUTPUT 
AS
SELECT @ActiveQuota = SUM(ActiveQuota),@UserNumber=SUM(UserNumber),@JobNumber=SUM(JobNumber),@VIPDate=MAX(EndDate),@RemainQuota=SUM(RemainQuota)
FROM caOrder WITH(NOLOCK)
WHERE BeginDate<GETDATE() 
AND EndDate>GETDATE()
AND OrderType IN (8,9,10,11)
AND cpMainID = @cpMainID
AND OpenDate IS NOT NULL













GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderTryInsert]    Script Date: 2018/12/13 19:03:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--添加试用订单
--Lucifer 2014-12-19
--增加短信数harry2017-10-17
CREATE PROCEDURE [dbo].[bpcaOrderTryInsert]
(
	@cpMainID INT,
	@ProvinceID SMALLINT,
	@PayMethod TINYINT,
	@ConsultantID SMALLINT,
	@CompanyName NVARCHAR(50),
	@ManageruserID SMALLINT,
	@LinkMan NVARCHAR(50),
	@Telephone VARCHAR(50),
	@LinkRemark NVARCHAR(1000)
)
AS
SET NOCOUNT ON
BEGIN 
	INSERT INTO maindb..caOrder(
		cpMainID,
		ProvinceID,
		PayMethod,
		OrderType,
		Accounts,
		Paid,
		Manageruserid,
		ActiveQuota,
		RemainQuota,
		JobNumber,
		UserNumber,
		SmsQuota,
		RemainSmsQuota,
		JobRefreshQuota,
		RemainJobRefreshQuota,
		CompanyName,
		NeedInvoice,
		dcFeeDetailID,
		IsBackSubmit,
		BeginDate,
		EndDate,
		OrderStatus,
		OpenDate,
		OpenMan,
		LinkMan,
		Telephone,
		LinkRemark,
		ReceiveDate
	)
	VALUES(
		@cpMainID,
		@ProvinceID,
		@PayMethod,
		8,
		0,
		0,
		@ConsultantID,
		10,
		10,
		5,
		1,
		7,
		7,
		14,
		14,
		@CompanyName,
		0,
		0,
		1,
		GETDATE(),
		GETDATE()+7,
		13,
		GETDATE(),
		@ManageruserID,
		@LinkMan,
		@Telephone,
		@LinkRemark,
		GETDATE()
	)
END
GO

/****** Object:  StoredProcedure [dbo].[bpcaOrderUpdate]    Script Date: 2018/12/13 19:03:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 --更新订单信息
--Lucifer 2014-12-19
CREATE PROCEDURE [dbo].[bpcaOrderUpdate] 
(
	@WHERE VARCHAR(MAX),
	@OrderID INT
)
AS 
BEGIN
	DECLARE @SQL VARCHAR(MAX)
	SET @WHERE = dbo.SafeSQL(@WHERE)
	SET @SQL = 'UPDATE caOrder SET ' + @WHERE + ' WHERE ID=' + CONVERT(VARCHAR(20),@OrderID)
	EXEC(@SQL)
END

GO

/****** Object:  StoredProcedure [dbo].[bpcaPlaintSelect]    Script Date: 2018/12/13 19:03:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-29
--创建人：lambo
--功能说明：根据条件查询记录
--用于后台客户服务模块，单位投诉个人页面
-----------------
CREATE PROCEDURE [dbo].[bpcaPlaintSelect]
(
	@WHERE VARCHAR(1000)
)
AS
BEGIN
	DECLARE @SQL VARCHAR(2000)
	SET @SQL = 'SELECT TOP 5000 e.mobile,e.email paemail,a.*,b.name caName,d.paMainID paMainID,c.consultantID,c.Name,c.dcProvinceID,c.ID cpMainID,c.regDate'
	SET @SQL = @SQL + ' FROM caPlaint a WITH(NOLOCK) '
	SET @SQL = @SQL + ' LEFT JOIN caMain b WITH(NOLOCK) ON a.caMainID = b.ID '
	SET @SQL = @SQL + ' LEFT JOIN cpMain c WITH(NOLOCK) ON  b.cpMainID = c.ID '
	SET @SQL = @SQL + ' INNER JOIN cvMain d WITH(NOLOCK) ON a.cvMainID = d.ID '
    SET @SQL = @SQL + ' INNER JOIN paMain e WITH(NOLOCK) ON d.paMainID = e.ID '
	SET @SQL = @SQL + ' WHERE  1=1 '
	IF LEN(@WHERE) > 0
	BEGIN
		SET @WHERE = dbo.SafeSql(@Where)
		SET @SQL = @SQL + @WHERE
	END
	SET @SQL = @SQL + ' ORDER BY a.AddDate DESC'
	EXEC(@SQL)
END













GO

/****** Object:  StoredProcedure [dbo].[bpCaPlaintUpdate]    Script Date: 2018/12/13 19:03:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-26
--创建人：lambo
--功能说明：根据编号修改一条单位投诉个人的记录
--用于后台单位投诉个人页面的处理文件
-----------------
CREATE PROCEDURE [dbo].[bpCaPlaintUpdate]
(
	@ID INT,
	@ReplyType INT,
	@ReplyMan INT,
	@ProcessMessage VARCHAR(500)
)
AS
BEGIN TRY
	UPDATE CaPlaint SET ReplyType=@ReplyType,ReplyMan=@ReplyMan,ProcessMessage=@ProcessMessage,ProcessDate=GETDATE() WHERE ID=@ID
	RETURN 1
END TRY
BEGIN CATCH
	RETURN 0
END CATCH













GO

/****** Object:  StoredProcedure [dbo].[bpCertificate_UnrealByIDDelete]    Script Date: 2018/12/13 19:03:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--###########################
--创建时间：2014.12.15
--创建人：Nick
--说明：删除虚假营业执照
--###########################
CREATE PROCEDURE [dbo].[bpCertificate_UnrealByIDDelete]
(
	@ID INT
)
AS
BEGIN 
	DELETE FROM  certificate_unreal WHERE id = @ID
END

GO

/****** Object:  StoredProcedure [dbo].[bpCertificate_UnrealByIDInsert]    Script Date: 2018/12/13 19:03:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--###########################
--创建时间：2014.12.15
--创建人：Nick
--说明：插入虚假营业执照
--###########################
CREATE PROCEDURE [dbo].[bpCertificate_UnrealByIDInsert]
(
	@CompanyName NVARCHAR(100),
	@CertFileName VARCHAR(100),
	@ManagerUserID INT,
	@Des VARCHAR(MAX)
)
AS
BEGIN 
	INSERT INTO certificate_unreal (Companyname,certfilename,addman,description) 
	VALUES (@CompanyName,@CertFileName,@ManagerUserID,@Des)
END

GO

/****** Object:  StoredProcedure [dbo].[bpCertificate_UnrealByIDSelect]    Script Date: 2018/12/13 19:03:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--###########################
--创建时间：2014.12.15
--创建人：Nick
--说明：获取虚假营业执照
--###########################
CREATE PROCEDURE [dbo].[bpCertificate_UnrealByIDSelect]
(
	@ID INT
)
AS
BEGIN 
	SELECT certfilename FROM certificate_unreal WITH(NOLOCK) WHERE  id = @ID
END

GO

/****** Object:  StoredProcedure [dbo].[bpCertificate_UnrealSelect]    Script Date: 2018/12/13 19:03:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--###########################
--创建时间：2014.12.15
--创建人：Nick
--说明：获取虚假营业执照案例
--###########################
CREATE PROCEDURE [dbo].[bpCertificate_UnrealSelect]
AS
BEGIN 
	SELECT a.id,a.Companyname,a.certfilename,a.addman,a.AddDate,a.description, b.name
	FROM certificate_unreal a  WITH(NOLOCK) ,ManagerUser b
	WHERE a.AddMan = b.ID
	ORDER BY AddDate DESC
END

GO

/****** Object:  StoredProcedure [dbo].[bpColdCustomerBycpMainIDSelect]    Script Date: 2018/12/13 19:03:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--根据企业ID获取冷客户记录
--Lucifer 2014-12-19
CREATE PROCEDURE [dbo].[bpColdCustomerBycpMainIDSelect] 
(
	@cpMainID INT
) 
AS 
BEGIN 
	SELECT * FROM maindb..ColdCustomer WITH(NOLOCK) WHERE IsValid=1 AND cpMainID=@cpMainID
END

GO

/****** Object:  StoredProcedure [dbo].[bpColdCustomerInsert]    Script Date: 2018/12/13 19:03:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpColdCustomerInsert]
(
	@CompanyName VARCHAR(100),
	@ProvinceID TINYINT,	
	@Email VARCHAR(100),
	@Phone VARCHAR(50),
	@SourceLink VARCHAR(1000),
	@RegType TINYINT,
	@RegUserName VARCHAR(50),
	@RegPassWord VARCHAR(50),
	@IsNameRepeat BIT,
	@IsEmailRepeat BIT,
	@IsMobilerepeat BIT,
	@IsAgree BIT,
	@DisAgreeReason nvarchar(500),
	@Status TINYINT,
	@IsValid BIT,
	@CheckReason nvarchar(500),
	@CheckDate SMALLDATETIME,
	@AddDate SMALLDATETIME,
	@AddMan SMALLINT,
	@AddWorkUserID SMALLINT,
	@LinkMan VARCHAR(50),
	@CpMainIDOld VARCHAR(100),
	@PhotoFile VARCHAR(50),
	@qq VARCHAR(15),
	@cpIndustry VARCHAR(50),
	@dcCompanyKindID INT,
	@dcCompanySizeID INT,
	@dcRegionID VARCHAR(6),
	@Address VARCHAR(120),
	@Brief VARCHAR(6000),
	@OtherContact VARCHAR(100)	
)
AS
SET NOCOUNT ON
BEGIN
	INSERT INTo MainDB..ColdCustomer 
		(CompanyName, ProvinceID, Email, Phone, SourceLink, RegType,
		RegUserName, RegPassWord, IsNameRepeat, IsEmailRepeat, IsMobilerepeat,
        IsAgree, DisAgreeReason, Status, IsValid, CheckReason, CheckDate,
        AddDate, AddMan, AddWorkUserID, LinkMan, CpMainIDOld, PhotoFile, QQ, cpIndustry, Address,
        Brief, dcCompanySizeID, dcRegionID, dcCompanyKindID, OtherContact)
	SELECT @CompanyName, @ProvinceID, @Email, @Phone, @SourceLink, @RegType,
		@RegUserName, @RegPassWord, @IsNameRepeat, @IsEmailRepeat, @IsMobilerepeat,
        @IsAgree, @DisAgreeReason, @Status, @IsValid, @CheckReason, @CheckDate,
        @AddDate, @AddMan, @AddWorkUserID, @LinkMan, @CpMainIDOld, @PhotoFile, @QQ, @cpIndustry, @Address,
        @Brief, @dcCompanySizeID, @dcRegionID, @dcCompanyKindID, @OtherContact

	SELECT @@IDENTITY
END
GO

/****** Object:  StoredProcedure [dbo].[bpColdCustomerReleaseConsultant]    Script Date: 2018/12/13 19:03:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--alen 2018-4-4
--harry 2018-6-7 修改：分配顾问时间大于2个月的才释放
CREATE PROCEDURE [dbo].[bpColdCustomerReleaseConsultant] 
AS
BEGIN
    SET NOCOUNT ON
	--2个月内未注册的，释放
	UPDATE Maindb..ColdCustomer
	SET AddMan = NULL
	WHERE CpMainID IS NULL
	AND AddDate < DATEADD(m, -2, GETDATE())
	AND AddMan IS NOT NULL

	--已注册，注册2个月内未上传营业执照的，释放
	UPDATE a SET a.AddMan = NULL FROM Maindb..ColdCustomer a, cpMain b
	WHERE a.cpMainID = b.ID AND b.HasLicence = 0 AND a.RegisterDate < DATEADD(m, -2, GETDATE())
        AND b.consultantDate< DATEADD(m, -2, GETDATE())
	AND a.AddMan IS NOT NULL
    SET NOCOUNT OFF

	--企业已删除的，改为未注册状态
	UPDATE ColdCustomer SET
		[Status] = 1,
		cpMainID = NULL,
		RegisterDate = NULL,
		LastLoginDate = NULL,
		LicenceDate = NULL,
		PayDate = NULL,
		PayFee = NULL
	WHERE cpMainID > 0 AND cpMainId NOT IN(SELECT ID FROM cpMain)
END
GO

/****** Object:  StoredProcedure [dbo].[bpColdCustomerTryByCompanyNameSelect]    Script Date: 2018/12/13 19:03:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--根据公司名称获取冷客户VIP试用记录
--Lucifer 2014-12-19
CREATE PROCEDURE [dbo].[bpColdCustomerTryByCompanyNameSelect] 
(
	@CompanyName NVARCHAR(100)
) 
AS 
BEGIN 
	SELECT * FROM maindb..ColdCustomerTry WITH(NOLOCK) WHERE CompanyName=@CompanyName
END

GO

/****** Object:  StoredProcedure [dbo].[bpCommentAndInterviewByStatisticsSelect]    Script Date: 2018/12/13 19:03:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

---修改人Harry,修改点评、面经统计错误
CREATE PROCEDURE [dbo].[bpCommentAndInterviewByStatisticsSelect]
(
	@ManagerUserID SMALLINT,
	@YearMonth VARCHAR(6),
	@DeptID SMALLINT
)
AS
BEGIN
	CREATE TABLE #T
	(
		YearMonth VARCHAR(6),
		WorkUserID SMALLINT,
		paMainID INT,
		GoodComment INT,
		CommonComment INT,
		InvalidComment INT,
		GoodInterview INT,
		CommonInterview INT,
		InvalidInterview INT
	)
	IF LEN(ISNULL(@YearMonth,'')) = 0
	BEGIN
		IF DAY(GETDATE()) > 25
		BEGIN
			INSERT INTO #T(YearMonth,WorkUserID,paMainID) SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,1,GETDATE()),112),WorkUserID,PaMainID FROM oadb..opWorkUserPaMain
			INSERT INTO #T(YearMonth,WorkUserID,paMainID) SELECT CONVERT(VARCHAR(6),GETDATE(),112),WorkUserID,PaMainID FROM oadb..opWorkUserPaMain
			INSERT INTO #T(YearMonth,WorkUserID,paMainID) SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,-1,GETDATE()),112),WorkUserID,PaMainID FROM oadb..opWorkUserPaMain
		END
		ELSE
		BEGIN
			INSERT INTO #T(YearMonth,WorkUserID,paMainID) SELECT CONVERT(VARCHAR(6),GETDATE(),112),WorkUserID,PaMainID FROM oadb..opWorkUserPaMain
			INSERT INTO #T(YearMonth,WorkUserID,paMainID) SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,-1,GETDATE()),112),WorkUserID,PaMainID FROM oadb..opWorkUserPaMain
			INSERT INTO #T(YearMonth,WorkUserID,paMainID) SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,-2,GETDATE()),112),WorkUserID,PaMainID FROM oadb..opWorkUserPaMain
		END
	END
	ELSE
	BEGIN
		INSERT INTO #T(YearMonth,WorkUserID,paMainID) SELECT @YearMonth,WorkUserID,PaMainID FROM oadb..opWorkUserPaMain
	END
	IF @ManagerUserID > 0
	BEGIN
		DELETE FROM #T WHERE WorkUserID NOT IN(SELECT ID FROM oadb..WorkUser WHERE ManageruserID=@ManagerUserID)
	END
	IF @DeptID > 0
	BEGIN
		DELETE FROM #T WHERE WorkUserID NOT IN(SELECT a.ID FROM oadb..WorkUser a,maindb..Manageruser b WHERE a.ManageruserID=b.ID AND b.DeptID=@DeptID)
	END
	--优质评论
	UPDATE a SET a.GoodComment=b.cnt FROM #T a,(
		SELECT paMainID,CONVERT(VARCHAR(6),AddDate,112) YearMonth,COUNT(1) cnt FROM(
			SELECT ROW_NUMBER() OVER(PARTITION BY a.PaMainID, a.CpMainID ORDER BY a.ADDDATE DESC) i,a.paMainID,a.AddDate,a.CommentType 
			FROM Comments a, oadb..opWorkUserPaMain b, oadb..opCommentCompany c
			WHERE a.paMainID=b.paMainID AND a.cpMainID=c.cpMainID AND c.WorkUserID=b.WorkUserID AND a.CommentType=2
		) a
		WHERE i<4
		GROUP BY paMainID,CONVERT(VARCHAR(6),AddDate,112)
	) b
	WHERE a.YearMonth=b.YearMonth AND a.paMainID=b.paMainID
	--一般评论
	UPDATE a SET a.CommonComment=b.cnt FROM #T a,(
		SELECT paMainID,CONVERT(VARCHAR(6),AddDate,112) YearMonth,COUNT(1) cnt FROM(
			SELECT ROW_NUMBER() OVER(PARTITION BY a.PaMainID, a.CpMainID ORDER BY a.ADDDATE DESC) i,a.paMainID,a.AddDate,a.CommentType 
			FROM Comments a, oadb..opWorkUserPaMain b, oadb..opCommentCompany c
			WHERE a.paMainID=b.paMainID AND a.cpMainID=c.cpMainID AND c.WorkUserID=b.WorkUserID AND a.CommentType=1
		) a
		WHERE i<4
		GROUP BY paMainID,CONVERT(VARCHAR(6),AddDate,112)
	) b
	WHERE a.YearMonth=b.YearMonth AND a.paMainID=b.paMainID
	--无效评论
	UPDATE a SET a.InvalidComment=b.cnt FROM #T a,(
		SELECT paMainID,CONVERT(VARCHAR(6),AddDate,112) YearMonth,COUNT(1) cnt FROM(
			SELECT ROW_NUMBER() OVER(PARTITION BY a.PaMainID, a.CpMainID ORDER BY a.ADDDATE DESC) i,a.paMainID,a.AddDate,a.CommentType 
			FROM Comments a, oadb..opWorkUserPaMain b, oadb..opCommentCompany c
			WHERE a.paMainID=b.paMainID AND a.cpMainID=c.cpMainID AND c.WorkUserID=b.WorkUserID AND a.CommentType=3
		) a
		WHERE i<4
		GROUP BY paMainID,CONVERT(VARCHAR(6),AddDate,112)
	) b
	WHERE a.YearMonth=b.YearMonth AND a.paMainID=b.paMainID

	--优质面经
	UPDATE a SET a.GoodInterview=b.cnt FROM #T a,(
		SELECT paMainID,CONVERT(VARCHAR(6),AddDate,112) YearMonth,COUNT(1) cnt FROM(
			SELECT ROW_NUMBER() OVER(PARTITION BY a.PaMainID, a.CpMainID ORDER BY a.ADDDATE DESC) i,a.paMainID,a.AddDate,a.InterviewType 
			FROM Interview a, oadb..opWorkUserPaMain b, oadb..opCommentCompany c
			WHERE a.paMainID=b.paMainID AND a.cpMainID=c.cpMainID AND c.WorkUserID=b.WorkUserID AND a.InterviewType=2
		) a
		WHERE i<4
		GROUP BY paMainID,CONVERT(VARCHAR(6),AddDate,112)
	) b
	WHERE a.YearMonth=b.YearMonth AND a.paMainID=b.paMainID
	--一般面经
	UPDATE a SET a.CommonInterview=b.cnt FROM #T a,(
		SELECT paMainID,CONVERT(VARCHAR(6),AddDate,112) YearMonth,COUNT(1) cnt FROM(
			SELECT ROW_NUMBER() OVER(PARTITION BY a.PaMainID, a.CpMainID ORDER BY a.ADDDATE DESC) i,a.paMainID,a.AddDate,a.InterviewType 
			FROM Interview a, oadb..opWorkUserPaMain b, oadb..opCommentCompany c
			WHERE a.paMainID=b.paMainID AND a.cpMainID=c.cpMainID AND c.WorkUserID=b.WorkUserID AND a.InterviewType=1
		) a
		WHERE i<4
		GROUP BY paMainID,CONVERT(VARCHAR(6),AddDate,112)
	) b
	WHERE a.YearMonth=b.YearMonth AND a.paMainID=b.paMainID
	--无效面经
	UPDATE a SET a.InvalidInterview=b.cnt FROM #T a,(
		SELECT paMainID,CONVERT(VARCHAR(6),AddDate,112) YearMonth,COUNT(1) cnt FROM(
			SELECT ROW_NUMBER() OVER(PARTITION BY a.PaMainID, a.CpMainID ORDER BY a.ADDDATE DESC) i,a.paMainID,a.AddDate,a.InterviewType 
			FROM Interview a, oadb..opWorkUserPaMain b, oadb..opCommentCompany c
			WHERE a.paMainID=b.paMainID AND a.cpMainID=c.cpMainID AND c.WorkUserID=b.WorkUserID AND a.InterviewType=3
		) a
		WHERE i<4
		GROUP BY paMainID,CONVERT(VARCHAR(6),AddDate,112)
	) b
	WHERE a.YearMonth=b.YearMonth AND a.paMainID=b.paMainID
	
	DELETE FROM #T WHERE GoodComment IS NULL
		AND CommonComment IS NULL
		AND InvalidComment IS NULL
		AND GoodInterview IS NULL
		AND CommonInterview IS NULL
		AND InvalidInterview IS NULL
	SELECT * FROM #T
	DROP TABLE #T
END

 
GO

/****** Object:  StoredProcedure [dbo].[bpCommentByStatisticsSelect]    Script Date: 2018/12/13 19:03:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--审核人点评和面经审核工作量统计
--创建人：harry
--创建时间：2015/3/18
CREATE PROCEDURE [dbo].[bpCommentByStatisticsSelect]
(
	@YearMonth VARCHAR(6),
	@BeginDate DateTime,
	@EndDate DateTime
)
AS
BEGIN
	DECLARE @t AS TABLE
	(
		YearMonth VARCHAR(6),
		ManagerUserId SMALLINT,
		UserName VARCHAR(10),
		GoodComment INT,
		CommonComment INT,
		InvalidComment INT,
		GoodInterview INT,
		CommonInterview INT,
		InvalidInterview INT
	)
	INSERT INTO @t(ManagerUserId, UserName, YearMonth)
		SELECT DISTINCT verifyman,name,@YearMonth FROM
		(
			(	
				SELECT DISTINCT a.verifyman, b.Name
				FROM Comments a, managerUser b
				WHERE verifyman  IS NOT NULL 
				AND a.verifyman=b.Id
			) 
			UNION 
			(	
				SELECT DISTINCT a.verifyman, b.Name
				FROM interview a, managerUser b
				WHERE verifyman  IS NOT NULL 
				AND a.verifyman=b.Id
			)
		)E
	
	--优质评论
	UPDATE z SET GoodComment=(
		SELECT COUNT(1)
		FROM comments a
		WHERE a.verifyman = z.ManagerUserId 
		AND a.commenttype=2 
		AND a.verifyDate>@BeginDate 
		AND a.verifyDate<@EndDate 
		GROUP BY a.commenttype, a.verifyman)
	FROM @t z

	--一般评论
	UPDATE z SET CommonComment=(
		SELECT  COUNT(1)
		FROM comments a
		WHERE a.verifyman=z.ManagerUserId  
		AND a.commenttype=1 
		AND a.verifyDate>@BeginDate 
		AND a.verifyDate<@EndDate  
		GROUP BY a.commenttype,a.verifyman)
	FROM @t z

	--无效评论		
	UPDATE z SET InvalidComment=(
		SELECT  COUNT(1)
		FROM comments a
		WHERE a.verifyman=z.ManagerUserId 
		AND a.commenttype=3 
		AND a.verifyDate>@BeginDate 
		AND a.verifyDate<@EndDate  
		GROUP BY a.commenttype,a.verifyman)
	FROM @t z

	--优质面经
	UPDATE z SET GoodInterview=(
		SELECT COUNT(1)
		FROM interview a
		WHERE a.verifyman=z.ManagerUserId 
		AND a.interviewtype=2 
		AND a.verifyDate>@BeginDate 
		AND a.verifyDate<@EndDate 
		GROUP BY a.interviewtype,a.verifyman)
	FROM @t z

	--一般面经		
	UPDATE z SET CommonInterview=(
		SELECT COUNT(1)
		FROM interview a
		WHERE a.verifyman=z.ManagerUserId  
		AND a.interviewtype=1 
		AND a.verifyDate>@BeginDate 
		AND a.verifyDate<@EndDate 
		GROUP BY a.interviewtype,a.verifyman)
	FROM @t z

	--无效面经
	UPDATE z SET InvalidInterview=(
		SELECT COUNT(1)
		FROM interview a
		WHERE a.verifyman=z.ManagerUserId AND  a.interviewtype=3 
		AND a.verifyDate>@BeginDate 
		AND a.verifyDate<@EndDate 
		GROUP BY a.interviewtype,a.verifyman)
	FROM @t z

	SELECT * FROM @t
END
GO

/****** Object:  StoredProcedure [dbo].[bpCommentsByCpMainID_CpLicenceUpdate]    Script Date: 2018/12/13 19:03:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--###########################
--创建时间：2014.12.18
--创建人：Nick
--说明：营业执照审核时更新点评 面经信息
--###########################
CREATE PROCEDURE [dbo].[bpCommentsByCpMainID_CpLicenceUpdate]
(
	@cpMainID INT,
	@CompanyNameOld NVARCHAR(50),
	@CompanyNameNew NVARCHAR(50),
	@TypeID INT --1修改执照 2新增执照
)
AS
return
SET NOCOUNT ON
BEGIN 
	IF @TypeID = 1
	BEGIN
		IF @CompanyNameOld<>@CompanyNameNew
		BEGIN
			DELETE FROM Comments WHERE CpmainID = @cpMainID
			DELETE FROM InterView WHERE CpmainID = @cpMainID
			
			--添加营业执照 同步 点评信息
			INSERT INTO Comments 
			SELECT @cpMainID,PaMainID,IP,SessionID,Relation,GradeStar,Remark1,Remark2,Remark3,Title,Position,WorkRegionID,
			EntryDate,QuitDate,AddDate,CommentType,VerifyMan,VerifyDate,ReplyDate,ReplyContent,ReplyVerifyMan,ReplyVerifyDate,
			PraiseCount,LoginPraiseCount,ReplyVerifyResult,0 
			FROM Comments
			WHERE CpMainID IN (
				SELECT TOP 1 CpMainID FROM Comments
				WHERE CpMainID IN (SELECT ID FROM CpMain WHERE Name = @CompanyNameNew AND MemberType > 1)
				AND CpMainID <> @cpMainID
			)
			
			--添加营业执照 同步 面经信息
			INSERT INTO InterView 
			SELECT @cpMainID,PaMainID,IP,SessionID,JobName,InterviewDate,InterViewResult,SalaryMonth,InterViewExperience,
			InterviewFeeling,Title,AddDate,VerifyMan,VerifyDate,InterviewType,ReplyDate,ReplyContent,ReplyVerifyMan,
			ReplyVerifyDate,ReplyVerifyResult,PraiseCount,LoginPraiseCount,0 
			FROM InterView
			WHERE CpMainID IN (
				SELECT TOP 1 CpMainID FROM InterView
				WHERE CpMainID IN (SELECT ID FROM CpMain WHERE Name = @CompanyNameNew AND MemberType > 1)
				AND CpMainID <> @cpMainID
			)
		END
	END
	ELSE
	BEGIN
		--添加营业执照 同步 点评信息
		INSERT INTO Comments 
		SELECT @cpMainID,PaMainID,IP,SessionID,Relation,GradeStar,Remark1,Remark2,Remark3,Title,Position,WorkRegionID,
		EntryDate,QuitDate,AddDate,CommentType,VerifyMan,VerifyDate,ReplyDate,ReplyContent,ReplyVerifyMan,ReplyVerifyDate,
		PraiseCount,LoginPraiseCount,ReplyVerifyResult,0 
		FROM Comments
		WHERE CpMainID IN (
			SELECT TOP 1 CpMainID FROM Comments
			WHERE CpMainID IN (SELECT ID FROM CpMain WHERE Name = @CompanyNameNew AND MemberType > 1)
			AND CpMainID <> @cpMainID
		)
		
		--添加营业执照 同步 面经信息
		INSERT INTO InterView 
		SELECT @cpMainID,PaMainID,IP,SessionID,JobName,InterviewDate,InterViewResult,SalaryMonth,InterViewExperience,
		InterviewFeeling,Title,AddDate,VerifyMan,VerifyDate,InterviewType,ReplyDate,ReplyContent,ReplyVerifyMan,
		ReplyVerifyDate,ReplyVerifyResult,PraiseCount,LoginPraiseCount,0 
		FROM InterView
		WHERE CpMainID IN (
			SELECT TOP 1 CpMainID FROM InterView
			WHERE CpMainID IN (SELECT ID FROM CpMain WHERE Name = @CompanyNameNew AND MemberType > 1)
			AND CpMainID <> @cpMainID
		)
	END
END

GO

/****** Object:  StoredProcedure [dbo].[bpCommentsSelect]    Script Date: 2018/12/13 19:03:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpCommentsSelect]
(
	@Where VARCHAR(MAX)
)
AS
BEGIN
	DECLARE @Sql VARCHAR(MAX)
	SET @Sql = 'SELECT TOP 2000 d.Name CompanyName,e.UserName,a.AddDate,a.CommentType,
		d.SecondID,d.dcProvinceID,f.ManageruserID,f.Name ManageruserName,a.VerifyDate 
	FROM Maindb..Comments a,
		oadb..opCommentCompany b,
		oadb..opWorkUserPaMain c,
		MainDB..cpMain d,
		MainDB..paMain e,
		oadb..WorkUser f 
	WHERE a.cpMainID=b.cpMainID 
	AND b.WorkUserID=c.WorkUserID 
	AND a.paMainID=c.paMainID 
	AND d.ID=a.cpMainID 
	AND e.ID=a.paMainID 
	AND f.ID=b.WorkUserID' + @where + 
	' ORDER BY a.AddDate DESC'
	EXEC(@SQL)
END
GO

/****** Object:  StoredProcedure [dbo].[bpCompany_BlackByCompanyNameSelect]    Script Date: 2018/12/13 19:03:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--###########################
--创建时间：2014.12.10
--创建人：Nick
--说明：根据单位名称判断是否在黑名单
--###########################
CREATE PROCEDURE [dbo].[bpCompany_BlackByCompanyNameSelect]
(
	@CompanyName VARCHAR(100)
)
AS
BEGIN 
	SELECT * FROM Company_Black WITH(NOLOCK) 
	WHERE CHARINDEX(lTrim(rTrim(Companyname)), @CompanyName) > 0
END

GO

/****** Object:  StoredProcedure [dbo].[bpCompany_BlackByReasonUpdate]    Script Date: 2018/12/13 19:03:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：根据编号修改企业黑名单的增加原因
--用于企业黑名单页面
-----------------
CREATE PROCEDURE [dbo].[bpCompany_BlackByReasonUpdate]
(
	@ID INT,
	@Reason VARCHAR(500)
)
AS
BEGIN TRY
	UPDATE Company_Black SET Reason=@Reason WHERE ID=@ID
	RETURN 1
END TRY
BEGIN CATCH
	RETURN 0
END CATCH













GO

/****** Object:  StoredProcedure [dbo].[bpCompany_BlackDelete]    Script Date: 2018/12/13 19:03:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：根据编号删除一条企业黑名单信息
--用于企业黑名单页面
-----------------
CREATE PROCEDURE [dbo].[bpCompany_BlackDelete]
(
	@ID INT
)
AS
BEGIN TRY
	DELETE FROM Company_Black WHERE ID=@ID
	RETURN 1
END TRY
BEGIN CATCH
	RETURN 0
END CATCH













GO

/****** Object:  StoredProcedure [dbo].[bpCompany_BlackInsert]    Script Date: 2018/12/13 19:03:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：插入一条企业黑名单信息
--用于增加企业黑名单页面
-----------------
CREATE PROCEDURE [dbo].[bpCompany_BlackInsert]
(
	@CompanyName VARCHAR(50),
	@ManagerID INT,
	@Email VARCHAR(50),
	@Reason VARCHAR(500),
	@Mobile VARCHAR(11)
)
AS
BEGIN TRY
	INSERT INTO Company_Black
	(	
		CompanyName,
		ManagerID,
		Email,
		Reason,
		Mobile
	)
	VALUES
	(
		LTRIM(RTRIM(@CompanyName)),
		@ManagerID,
		LTRIM(RTRIM(@Email)),
		@Reason,
		LTRIM(RTRIM(@Mobile))
	)
	RETURN @@IDENTITY
END TRY
BEGIN CATCH
	RETURN 0
END CATCH













GO

/****** Object:  StoredProcedure [dbo].[bpCompany_BlackSelect]    Script Date: 2018/12/13 19:03:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：根据条件查询企业黑名单记录
--用于企业黑名单页面
-----------------
CREATE PROCEDURE [dbo].[bpCompany_BlackSelect]
(
	@WHERE VARCHAR(1000)
)
AS
BEGIN
	DECLARE @SQL VARCHAR(2000)
	SET @SQL = 'SELECT * FROM Company_Black WITH(NOLOCK) WHERE 1=1 '
	IF LEN(@WHERE) > 0
	BEGIN
		SET @WHERE = dbo.SafeSql(@Where)
		SET @SQL = @SQL + @WHERE
	END
	SET @SQL=@SQL + ' ORDER BY AddedDate DESC'
	EXEC(@SQL)
END



GO

/****** Object:  StoredProcedure [dbo].[bpCompany_DeleteedSelect]    Script Date: 2018/12/13 19:03:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-----------------
--创建日期：2011-01-28
--创建人：lambo
--功能说明：根据查询条件查询已删除的企业的信息
--用于删除单位记录页面
-----------------
CREATE PROCEDURE [dbo].[bpCompany_DeleteedSelect]
(
	@WHERE VARCHAR(1000)
)
AS
BEGIN
	BEGIN TRY
		DECLARE @SQL AS VARCHAR(2000)
		SET @SQL='SELECT DISTINCT TOP 200 * FROM Company_Deleted a WITH(NOLOCK)'
		IF LEN(@WHERE) > 0
		BEGIN
			SET @WHERE = dbo.SafeSql(@Where)
			SET @SQL = @SQL + @WHERE
		END
		SET @SQL = @SQL + ' ORDER BY AddDate DESC'
		EXEC(@SQL)
	END TRY
	BEGIN CATCH
	END CATCH
END


GO

/****** Object:  StoredProcedure [dbo].[bpCompany_xinxiDelete]    Script Date: 2018/12/13 19:03:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：根据编号删除一条记录
--用于后台临时库查询页面
-----------------
CREATE PROCEDURE [dbo].[bpCompany_xinxiDelete]
(
	@ID INT
)
AS
BEGIN TRY
	DELETE FROM Company_xinxi WHERE ID=@ID
	RETURN 1
END TRY
BEGIN CATCH
	RETURN 0
END CATCH













GO

/****** Object:  StoredProcedure [dbo].[bpCompany_xinxiSelect]    Script Date: 2018/12/13 19:03:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：根据条件查询临时单位库的数据
--用于临时单位库查询页面
--修改：按照注册时间排序harry
-----------------
CREATE PROCEDURE [dbo].[bpCompany_xinxiSelect]
(
	@count INT,
	@WHERE VARCHAR(1000)
)
AS
BEGIN
	DECLARE @SQL AS VARCHAR(2000)
	SET @SQL='SELECT TOP '+LTRIM(str(@count))+' * FROM company_xinxi '
	IF LEN(@WHERE) > 5
	BEGIN
		SET @WHERE = dbo.SafeSql(@Where)
		SET @SQL = @SQL + ' WHERE ' + RIGHT(@WHERE, LEN(@Where) - 5)
	END
	SET @SQL = @SQL + ' ORDER BY regdate DESC, id DESC'
 	EXEC(@SQL)
END

GO

/****** Object:  StoredProcedure [dbo].[bpCompany_xinxiUpdate]    Script Date: 2018/12/13 19:03:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：根据编号修改单位临时库内的一条记录的邮箱和公司名称
--用于后台临时库查询页面
-----------------
CREATE PROCEDURE [dbo].[bpCompany_xinxiUpdate]
(
	@ID INT,
	@email VARCHAR(50),
	@companyname VARCHAR(50)
)
AS
BEGIN TRY
	UPDATE Company_xinxi SET email=@email,companyname=@companyname WHERE ID=@ID
	RETURN 1
END TRY
BEGIN CATCH
	RETURN 0
END CATCH













GO

/****** Object:  StoredProcedure [dbo].[bpCompanyInformAllSelect]    Script Date: 2018/12/13 19:03:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：查询所有的单位分类通知
--用于后台单位分类通知
-----------------
CREATE PROCEDURE [dbo].[bpCompanyInformAllSelect]
AS
BEGIN
	SELECT a.*,b.SubSiteUrl,b.SubSiteCity FROM CompanyInform a WITH(NOLOCK),dcSubSite b WITH(NOLOCK)
	WHERE b.ID=LEFT(a.Province,2)
	ORDER BY AddDate DESC
END













GO

/****** Object:  StoredProcedure [dbo].[bpCompanyInformByStatusUpdate]    Script Date: 2018/12/13 19:03:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：修改单位用户分类通知的状态
--用于单位用户分类通知页面
-----------------
CREATE PROCEDURE [dbo].[bpCompanyInformByStatusUpdate]
	@ID INT
AS
BEGIN TRY
	UPDATE CompanyInform SET Status=Status-1 WHERE ID=@ID
	RETURN 1
END TRY
BEGIN CATCH
	RETURN 0
END CATCH













GO

/****** Object:  StoredProcedure [dbo].[bpCompanyInformDelete]    Script Date: 2018/12/13 19:03:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：删除一条单位分类通知
--用于后台单位分类通知
-----------------
CREATE PROCEDURE [dbo].[bpCompanyInformDelete]
	@ID INT
AS
BEGIN TRY
	DELETE FROM CompanyInform WHERE ID=@ID
END TRY
BEGIN CATCH
	GOTO ERR
END CATCH
RETURN 1
ERR:
BEGIN
	RETURN 0
END 













GO

/****** Object:  StoredProcedure [dbo].[bpCompanyInformSave]    Script Date: 2018/12/13 19:03:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：增加或修改单位分类通知，如果@ID>0则是修改，否则是增加
--用于后台增加或修改单位分类通知
--修改：harry2016-8-17 修改content长度
-----------------
CREATE PROCEDURE [dbo].[bpCompanyInformSave]
(
	@ID INT,
    @Object VARCHAR(50),
    @Title VARCHAR(50),
	@Content VARCHAR(MAX),
	@Type INT,
	@Link VARCHAR(100),
	@Province VARCHAR(100),
	@EndDate VARCHAR(20)
)
AS
BEGIN TRAN
	Declare @MaxId AS INT
	BEGIN TRY
		IF @ID>0
			BEGIN
				UPDATE CompanyInform SET
					Object = @Object,
					Title = @Title,
					Content = @Content,
					Type = @Type,
					AddDate = GETDATE(),
					Link = @Link,
					Province = @Province,
					EndDate = @EndDate
				WHERE ID=@ID
				SET @MaxID = @ID
			End
		ELSE
			BEGIN
				INSERT INTO CompanyInform
					(
						Object,Title,Content,Type,
						Province,EndDate,Link
					)
				VALUES
					(
						@Object,@Title,@Content,@Type,
						@Province,@EndDate,@Link
					)
				SET @MaxID = @@IDENTITY
		END
	END TRY
	BEGIN CATCH
		GOTO ERR
	END CATCH
COMMIT TRAN
RETURN @MaxID
ERR:
BEGIN
	ROLLBACK TRAN
	RETURN 0
END
GO

/****** Object:  StoredProcedure [dbo].[bpCompanyInformSelect]    Script Date: 2018/12/13 19:03:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：根据编号查询一条单位分类通知
--用于后台单位分类通知
-----------------
CREATE PROCEDURE [dbo].[bpCompanyInformSelect]
(
    @ID INT
)
AS
BEGIN
    SELECT * FROM CompanyInform WHERE ID = @ID
END













GO

/****** Object:  StoredProcedure [dbo].[bpCompanyNotifyDelete]    Script Date: 2018/12/13 19:03:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：根据编号删除一条网站消息
--用于后台单位用户管理网站消息页面
-----------------
CREATE PROCEDURE [dbo].[bpCompanyNotifyDelete]
(
	@ID INT
)
AS
BEGIN TRY
	DELETE FROM CompanyNotify WHERE ID = @ID
END TRY
BEGIN CATCH
	RETURN 0
END CATCH
RETURN 1













GO

/****** Object:  StoredProcedure [dbo].[bpCompanyNotifyInsert]    Script Date: 2018/12/13 19:03:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-----------------
--创建日期：2010-9-14
--创建人：lambo
--功能说明：增加一条网站消息
--用于后台单位用户管理网站消息页面
-----------------
CREATE PROCEDURE [dbo].[bpCompanyNotifyInsert]
(
	@CompanyId INT,
	@Type SMALLINT,
	@Content NVARCHAR(600),
	@EndDate VARCHAR(20),
	@ManagerUserID INT,
	@CompanyName VARCHAR(60)
)
AS
BEGIN TRY
	If @Type = 1 
		Set @EndDate = NULL
	INSERT INTO CompanyNotify (CompanyId,[type],[Content],EndDate,ManagerUserID,CompanyName) 
		VALUES(@CompanyId,@Type,@Content,@EndDate,@ManagerUserID,@CompanyName) 
END TRY
BEGIN CATCH
	RETURN 0
END CATCH
RETURN 1













GO

/****** Object:  StoredProcedure [dbo].[bpCompanyNotifySelect]    Script Date: 2018/12/13 19:03:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpCompanyNotifySelect]
(
	@WHERE VARCHAR(1000)
)
AS
BEGIN
	DECLARE @SQL AS VARCHAR(2000)
	SET @SQL='SELECT TOP 300 a.AddDate NotifyAddDate,* FROM CompanyNotify a WITH(NOLOCK) LEFT JOIN Maindb..Manageruser b ON a.ManageruserID=b.ID WHERE 1=1 '
	IF LEN(@WHERE) > 0
	BEGIN
		SET @WHERE = dbo.SafeSql(@Where)
		SET @SQL = @SQL + @WHERE
	END
	SET @SQL = @SQL + ' ORDER BY a.AddDate DESC'
	EXEC(@SQL)
END













GO

/****** Object:  StoredProcedure [dbo].[bpConsultantByRepeatSelect]    Script Date: 2018/12/13 19:03:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--[bpConsultantListSelect] 'And username like ''%aoli%''',12,1
CREATE PROCEDURE [dbo].[bpConsultantByRepeatSelect]
(
	@Where VARCHAR(2000),
	@Page SMALLINT
)
AS
BEGIN
	BEGIN TRY
		SET @WHERE = dbo.SafeSQLBack(@WHERE)
		DECLARE @SQL AS VARCHAR(2000)
		DECLARE @strOrder AS VARCHAR(1000)
		SET @strOrder = ' ORDER BY RegDate DESC'
		
		CREATE TABLE #T(
			TitleID BIGINT,
			ID INT,
			Name NVARCHAR(50),
			HasLicence BIT,
			ziliaodate SMALLDATETIME,
			Quality TINYINT,
			ConsultantID SMALLINT,
			RegDate SMALLDATETIME,
			dcProvinceID TINYINT,
			MemberType TINYINT,
			VerifyResult TINYINT,
			IsAgent BIT,
			LastLoginDate SMALLDATETIME,
			UnlimitedDate SMALLDATETIME,
			Balance SMALLINT,
			BalanceDate SMALLDATETIME,
			IsLimitLogin TINYINT,
			RefreshDate SMALLDATETIME,
			ConsultantDate SMALLDATETIME,
			IsLock BIT,
			ResumeQuota SMALLINT,
			IsDelete BIT,
			JobNumber SMALLINT,
			VerifyMan SMALLINT,
			HasPaid BIT,
			ContactDoneDate SMALLDATETIME,
			ContactDoneMan SMALLINT,
			ContactDoneAdd SMALLDATETIME,
			ContactDoneID INT,
			ContactNotDoneDate SMALLDATETIME,
			ContactNotDoneMan SMALLINT,
			ContactNotDoneAdd SMALLDATETIME,
			ContactNotDoneID INT,
			DownLoadToday SMALLINT,
			JobViewNumber SMALLINT,
			SendResumeNumber SMALLINT,
			SendResumeNumberHigh SMALLINT
		)
		SET @SQL='
			INSERT INTO #T
			(TitleID,ID,Name,HasLicence,Quality,ConsultantID,RegDate,dcProvinceID,MemberType,VerifyResult,
			IsAgent,LastLoginDate,UnlimitedDate,Balance,BalanceDate,IsLimitLogin,RefreshDate,ConsultantDate,IsLock,
			ResumeQuota,IsDelete,JobNumber)
			SELECT ROW_NUMBER() OVER ( ORDER BY b.id DESC, a.ID DESC) AS TitleID,a.ID,a.Name,HasLicence,Quality,	
			ConsultantID,a.RegDate,dcProvinceID,MemberType,VerifyResult,IsAgent,LastLoginDate,UnlimitedDate,Balance,
			BalanceDate,IsLimitLogin,RefreshDate,ConsultantDate,IsLock,ResumeQuota,a.IsDelete,JobNumber
			FROM cpMain a WITH(NOLOCK)
			INNER JOIN (SELECT TOP 500 Name, Max(ID) ID FROM cpMain WITH(NOLOCK) 
				WHERE name <> ''某公司'' ' + @Where + '
				GROUP BY Name HAVING COUNT(*) > 1 
				ORDER BY Max(ID) DESC) b ON a.Name = b.Name
			ORDER BY b.ID DESC'
		EXEC(@SQL)

		UPDATE #T SET ziliaodate = (SELECT TOP 1 CheckDate FROM cpLicence Where cpMainID=#T.ID ORDER BY ID DESC),
			VerifyMan = (Select TOP 1 VerifyMan From bsCpVerify Where bsCpVerify.cpMainID=#T.ID ORDER BY ID DESC),
			HasPaid = (SELECT TOP 1 ID FROM caOrder WHERE Paid>0 AND OpenDate IS NOT NULL AND cpMainID=#T.ID),
			ContactDoneDate = (SELECT top 1 BeginTime FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=2 ORDER BY begintime DESC),
			ContactDoneMan = (SELECT top 1 ManagerUserID FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=2 ORDER BY begintime DESC),
			ContactDoneAdd = (SELECT top 1 AddDate FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=2 ORDER BY begintime DESC),
			ContactDoneID = (SELECT top 1 ID FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=2 ORDER BY begintime DESC),
			ContactNotDoneDate = (SELECT top 1 BeginTime FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=1 ORDER BY begintime DESC),
			ContactNotDoneMan = (SELECT top 1 ManagerUserID FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=1 ORDER BY begintime DESC),
			ContactNotDoneAdd = (SELECT top 1 AddDate FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=1 ORDER BY begintime DESC),
			ContactNotDoneID = (SELECT top 1 ID FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=1 ORDER BY begintime DESC),
			DownLoadToday = (SELECT COUNT(ID) cnt FROM caActiveLog WHERE CONVERT(VARCHAR(10),AddDate,112)=CONVERT(VARCHAR(10),GETDATE(),112) AND cpMainID=#T.ID),
			JobViewNumber = (select COUNT(1) cnt from paJobViewLog a with(nolock),Job b with(nolock) where b.cpMainID=#T.ID and a.JobID=b.ID and b.valid=1 and a.Adddate>getdate()-30)
		WHERE TitleID > (@Page - 1) * 20 AND TitleID <= @Page * 20

		UPDATE a SET SendResumeNumber = AllNumber,
			SendResumeNumberHigh = NotOpenNumber FROM #T a
		INNER JOIN qryJobApplyMonth b ON a.ID=b.cpMainID
		WHERE TitleID > (@Page-1)*20 AND TitleID <= @Page*20
		
		SELECT COUNT(*) FROM #T WITH(NOLOCK)
		SELECT * FROM #T WITH(NOLOCK) WHERE TitleID > (@Page-1)*20 AND TitleID <= @Page*20
		DROP TABLE #T
	END TRY
	BEGIN CATCH
		DROP TABLE #T
	END CATCH
END






GO

/****** Object:  StoredProcedure [dbo].[bpConsultantListSelect]    Script Date: 2018/12/13 19:03:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpConsultantListSelect]
(
	@WHERE VARCHAR(1000),
	@ORDER TINYINT,
	@Page SMALLINT
)
AS
SET NOCOUNT ON
BEGIN
	SET @WHERE = dbo.SafeSQLBack(@WHERE)
	DECLARE @SQL AS VARCHAR(2000)
	DECLARE @strOrder AS VARCHAR(1000)
	SET @strOrder = ''
	IF @ORDER = 1
		SET @strOrder = @strOrder + ' ORDER BY ConsultantDate DESC'
	ELSE IF @ORDER = 2
		SET @strOrder = @strOrder + ' ORDER BY LastLoginDate DESC'
	ELSE IF @ORDER = 3
		SET @strOrder = @strOrder + ' ORDER BY (SELECT TOP 1 CerDate FROM MainDB..cpCertification WITH(NOLOCK) WHERE cpMainID = a.ID ORDER BY CerDate DESC) DESC'
	ELSE IF @ORDER = 4
		SET @strOrder = @strOrder + ' ORDER BY RegDate DESC'
	ELSE IF @ORDER = 5
		SET @strOrder = @strOrder + ' ORDER BY JobNumber DESC'
	ELSE IF @ORDER = 6
		SET @strOrder = @strOrder + ' ORDER BY (SELECT COUNT(1) FROM CaCvQuotaLog WITH(NOLOCK) WHERE cpMainID = a.ID) DESC'
	ELSE IF @ORDER = 12
		SET @strOrder = @strOrder + ' ORDER BY (SELECT COUNT(1) FROM CaActiveLog WITH(NOLOCK) WHERE cpMainID = a.ID AND AddDate > CONVERT(VARCHAR(10), GETDATE(), 23)) DESC, LastLoginDate DESC'
	ELSE IF @ORDER = 7
		SET @strOrder = @strOrder + ' ORDER BY (SELECT TOP 1 AddDate FROM bsCpStatus WITH(NOLOCK) WHERE cpMainID = a.ID AND statusID=9 ORDER BY AddDate DESC) DESC'
	ELSE IF @ORDER = 8
		SET @strOrder = @strOrder + ' ORDER BY (Select TOP 1 VerifyDate From bsCpVerify Where cpMainID=a.ID) DESC'
	ELSE IF @ORDER = 9
		SET @strOrder = @strOrder + ' ORDER BY (SELECT TOP 1 begintime FROM contact WITH(NOLOCK) WHERE status=2 AND companyid=a.ID ORDER BY begintime DESC) DESC'
	ELSE IF @ORDER = 10
		SET @strOrder = @strOrder + ' ORDER BY (SELECT TOP 1 begintime FROM contact WITH(NOLOCK) WHERE status=2 AND companyid=a.ID ORDER BY begintime DESC)'
	ELSE IF @ORDER = 14
		SET @strOrder = @strOrder + ' ORDER BY (SELECT TOP 1 EndDate FROM caOrder WITH(NOLOCK) WHERE OrderType=8 AND BeginDate<GetDate() AND EndDate>GETDATE() AND cpMainId = a.Id AND IsDeleted = 0)'
	ELSE IF @ORDER = 15
		SET @strOrder = @strOrder + ' ORDER BY BalanceDate'
	ELSE IF @ORDER = 13
		SET @strOrder = @strOrder + ' ORDER BY Balance'
	ELSE IF @ORDER = 17
		SET @strOrder = @strOrder +  ' ORDER BY d.Id DESC'
	Else IF @ORDER = 0
		SET @strOrder = @strOrder + ' ORDER BY LastLoginDate DESC'
	ELSE IF @ORDER = 19
		SET @strOrder = @strOrder + ' ORDER BY (SELECT TOP 1 EndDate FROM caOrder WITH(NOLOCK) WHERE OrderType=8 AND BeginDate<GetDate() AND EndDate>GETDATE() AND cpMainId = a.Id AND IsDeleted = 0) DESC'
	ELSE IF @ORDER = 20
		SET @strOrder = @strOrder + ' ORDER BY BalanceDate DESC'
	Else IF @ORDER = 16
		SET @strOrder = @strOrder + ' ORDER BY MemberDate'
	Else IF @ORDER = 21
		SET @strOrder = @strOrder + ' ORDER BY MemberDate DESC'
	Else IF @ORDER = 22
		SET @strOrder = @strOrder + ' order by (select top 1  checkdate from cpimage WITH(NOLOCK) where cpmainid=a.id and imgtype=1 and HasPassed=1 ORDER BY checkdate  DESC) desc'
	Else IF @ORDER = 23
		SET @strOrder = @strOrder + ' ORDER BY (SELECT TOP 1 OpenDate FROM caOrder WITH(NOLOCK) WHERE OrderType=8 AND BeginDate<GetDate() AND EndDate>GETDATE() AND cpMainId = a.Id AND IsDeleted = 0) DESC'

	CREATE TABLE #T(
		TitleID BIGINT,
		ID INT,
		Name VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
		HasLicence BIT,
		ziliaodate SMALLDATETIME,
		Quality TINYINT,
		ConsultantID SMALLINT,
		RegDate SMALLDATETIME,
		dcProvinceID TINYINT,
		MemberType TINYINT,
		VerifyResult TINYINT,
		IsAgent BIT,
		LastLoginDate SMALLDATETIME,
		UnlimitedDate SMALLDATETIME,
		Balance SMALLINT,
		BalanceDate SMALLDATETIME,
		IsLimitLogin TINYINT,
		RefreshDate SMALLDATETIME,
		ConsultantDate SMALLDATETIME,
		IsLock BIT,
		ResumeQuota SMALLINT,
		IsDelete BIT,
		JobNumber SMALLINT,
		VerifyMan SMALLINT,
		HasPaid BIT,
		ContactDoneDate SMALLDATETIME,
		ContactDoneMan SMALLINT,
		ContactDoneAdd SMALLDATETIME,
		ContactDoneID INT,
		ContactNotDoneDate SMALLDATETIME,
		ContactNotDoneMan SMALLINT,
		ContactNotDoneAdd SMALLDATETIME,
		ContactNotDoneID INT,
		DownLoadToday SMALLINT,
		JobViewNumber SMALLINT,
		SendResumeNumber SMALLINT,
		SendResumeNumberHigh SMALLINT,
		memberdate smalldatetime,
		RealName TINYINT,
		HasLogo BIT,
		CerMemberType TINYINT,
		CerType TINYINT,
		CerDate SMALLDATETIME,
		
	)
	SET @SQL='
		INSERT INTO #T
		(TitleID,ID,Name,HasLicence,Quality,ConsultantID,RegDate,dcProvinceID,MemberType,VerifyResult,
		IsAgent,LastLoginDate,UnlimitedDate,Balance,BalanceDate,IsLimitLogin,RefreshDate,ConsultantDate,IsLock,
		ResumeQuota,IsDelete,JobNumber,memberdate,RealName,HasLogo)
		SELECT TOP 4000 ROW_NUMBER() OVER (' + @strOrder + ') AS TitleID,a.ID,a.Name,HasLicence,Quality,
		ConsultantID,a.RegDate,dcProvinceID,a.MemberType,VerifyResult,IsAgent,LastLoginDate,UnlimitedDate,Balance,
		BalanceDate,IsLimitLogin,RefreshDate,ConsultantDate,IsLock,ResumeQuota,a.IsDelete,JobNumber, 
		memberdate,a.RealName,a.HasLogo
		FROM cpMain a WITH(NOLOCK)'

	IF @Order = 17
		SET @SQL = @SQL + ' INNER JOIN (SELECT b.cpMainId, MAX(a.Id) Id 
		FROM caFeePageViewLog a, caMain b
		WHERE a.caMainId = b.Id
		GROUP BY b.cpMainId
		) d ON a.ID=d.cpMainID '
	
	SET @SQL = @SQL + '	WHERE 1=1 '

	IF LEN(@WHERE) > 0
		SET @SQL = @SQL + @WHERE
	--print @SQL
	EXEC(@SQL)

	UPDATE #T SET ziliaodate = (SELECT Top 1 CheckDate FROM cpLicence Where cpMainID=#T.ID ORDER BY ID DESC),
		VerifyMan = (Select TOP 1 VerifyMan From bsCpVerify Where bsCpVerify.cpMainID=#T.ID ORDER BY ID DESC),
		HasPaid = (SELECT TOP 1 ID FROM caOrder WHERE Paid>0 AND OpenDate IS NOT NULL AND cpMainID=#T.ID AND IsDeleted = 0),
		ContactDoneDate = (SELECT top 1 BeginTime FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=2 ORDER BY begintime DESC),
		ContactDoneMan = (SELECT top 1 ManagerUserID FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=2 ORDER BY begintime DESC),
		ContactDoneAdd = (SELECT top 1 AddDate FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=2 ORDER BY begintime DESC),
		ContactDoneID = (SELECT top 1 ID FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=2 ORDER BY begintime DESC),
		ContactNotDoneDate = (SELECT top 1 BeginTime FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=1 ORDER BY begintime DESC),
		ContactNotDoneMan = (SELECT top 1 ManagerUserID FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=1 ORDER BY begintime DESC),
		ContactNotDoneAdd = (SELECT top 1 AddDate FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=1 ORDER BY begintime DESC),
		ContactNotDoneID = (SELECT top 1 ID FROM contact WITH(NOLOCK) WHERE companyid=#T.ID AND status=1 ORDER BY begintime DESC),
		DownLoadToday = (SELECT COUNT(ID) cnt FROM caActiveLog WHERE CONVERT(VARCHAR(10),AddDate,112)=CONVERT(VARCHAR(10),GETDATE(),112) AND cpMainID=#T.ID),
		JobViewNumber = (select COUNT(1) cnt from paJobViewLog a with(nolock),Job b with(nolock) where b.cpMainID=#T.ID and a.JobID=b.ID and b.valid=1 and a.Adddate>getdate()-30),
		CerMemberType = (SELECT TOP 1 MemberType FROM cpCertification WITH(NOLOCK) WHERE cpMainID = #T.ID AND CerStatus = 1 ORDER BY CerDate DESC),
		CerType = (SELECT TOP 1 CerType FROM cpCertification WITH(NOLOCK) WHERE cpMainID = #T.ID AND CerStatus = 1 ORDER BY CerDate DESC),
		CerDate = (SELECT TOP 1 CerDate FROM cpCertification WITH(NOLOCK) WHERE cpMainID = #T.ID AND CerStatus = 1 ORDER BY CerDate DESC) 
	WHERE TitleID > (@Page-1)*20 AND TitleID <= @Page*20
        
	UPDATE #T SET ContactDoneMan = (SELECT top 1 a.AddMan FROM ColdContact a,Coldcustomer  b WITH(NOLOCK) WHERE b.id=a.CustomerID AND  b.CpMainID=#T.ID AND isnull(a.beginTime,'')!='' ORDER BY a.BeginTime DESC),
		ContactDoneDate= (SELECT top 1 a.AddDate FROM ColdContact a,Coldcustomer  b WITH(NOLOCK) WHERE b.id=a.CustomerID AND  b.CpMainID=#T.ID  AND isnull(a.beginTime,'')!='' ORDER BY a.BeginTime DESC), 
		ContactDoneAdd = (SELECT top 1 a.AddDate FROM ColdContact a,Coldcustomer  b WITH(NOLOCK) WHERE b.id=a.CustomerID AND  b.CpMainID=#T.ID  AND isnull(a.beginTime,'')!='' ORDER BY a.BeginTime DESC) 
	WHERE ISNULL(ContactDoneDate ,'')='' and  TitleID > (@Page-1)*20 AND TitleID <= @Page*20

	UPDATE #T SET ContactNotDoneMan = (SELECT top 1 a.AddMan FROM ColdContact a,Coldcustomer  b WITH(NOLOCK) WHERE b.id=a.CustomerID AND  b.CpMainID=#T.ID AND isnull(a.beginTime,'')='' ORDER BY a.AddDate DESC),
		ContactNotDoneDate = (SELECT top 1 a.AddDate FROM ColdContact a,Coldcustomer  b WITH(NOLOCK) WHERE b.id=a.CustomerID AND  b.CpMainID=#T.ID  AND isnull(a.beginTime,'')='' ORDER BY a.AddDate  DESC) ,
		ContactNotDoneAdd = (SELECT top 1 a.AddDate FROM ColdContact a,Coldcustomer  b WITH(NOLOCK) WHERE b.id=a.CustomerID AND  b.CpMainID=#T.ID  AND isnull(a.beginTime,'')='' ORDER BY a.AddDate  DESC) 
	WHERE ISNULL(ContactNotDoneDate ,'')=''  and  TitleID > (@Page-1)*20 AND TitleID <= @Page*20

	UPDATE a SET SendResumeNumber = AllNumber,
		SendResumeNumberHigh = NotOpenNumber FROM #T a
	INNER JOIN qryJobApplyMonth b ON a.ID=b.cpMainID
	WHERE TitleID > (@Page-1)*20 AND TitleID <= @Page*20
	
	SELECT COUNT(*) FROM #T WITH(NOLOCK)
	SELECT a.*, b.MemberType UnFinishedMemberType, b.CerType UnFinishedCerType, c.HasPassed, d.CheckResult PersonalCheckResult, e.CheckResult CompanyEmailCheckResult
	FROM #T a WITH(NOLOCK)
		LEFT JOIN cpCertification b WITH(NOLOCK) ON a.ID = b.cpMainID AND b.CerStatus IS NULL
		LEFT JOIN cpLicence c WITH(NOLOCK) ON b.ID = c.cpCertificationID
		LEFT JOIN cpPersonalAccount d WITH(NOLOCK) ON b.ID = d.cpCertificationID
		LEFT JOIN cpCompanyEmail e WITH(NOLOCK) ON b.ID = e.cpCertificationID
	WHERE TitleID > (@Page-1)*20 AND TitleID <= @Page*20
	DROP TABLE #T
END
GO

/****** Object:  StoredProcedure [dbo].[bpcontactByCompanyIdSelect]    Script Date: 2018/12/13 19:03:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--查询企业联系记录-------------------
--添加人Harry -----------------------
--添加时间：2014-12-16---------------
CREATE PROCEDURE [dbo].[bpcontactByCompanyIdSelect]
(
	@CompanyId VARCHAR(20)
)
AS 
BEGIN 
	SELECT TOP 1 Id, Title, Status, Quality, AddDate 
	FROM Contact WITH(NOLOCK INDEX(IX_Contact_CompanyId))
	WHERE CompanyId = @CompanyId
	ORDER BY ID DESC
END

GO

/****** Object:  StoredProcedure [dbo].[bpContactByCountSelect]    Script Date: 2018/12/13 19:03:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--创建人 Andy
--时间 2015-01-12
--按照条件查询后台联系活动的个数
CREATE PROCEDURE [dbo].[bpContactByCountSelect]
(	
	@ManagerUserID INT,
	@Status INT,
	@BeginTime DATETIME
)
AS
BEGIN		
	SELECT COUNT(1) cnt 
	FROM Contact WITH(NOLOCK)
	WHERE ManagerUserID = @ManagerUserID 
	AND Status = @Status 
	AND BeginTime > @BeginTime 
	AND BeginTime < @BeginTime + 1
END
GO

/****** Object:  StoredProcedure [dbo].[bpContactByCpMainIDSelect]    Script Date: 2018/12/13 19:03:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--2018-1-25 peter
CREATE PROCEDURE [dbo].[bpContactByCpMainIDSelect]
(
    @companyType INT,
    @companyid INT
)
AS
SET NOCOUNT ON
BEGIN
    IF NOT EXISTS(SELECT 'x' FROM coldcontact WHERE CustomerId IN(SELECT Id FROM ColdCustomer WHERE cpMainId = @companyid))
        SELECT TOP 10 * FROM contact WITH(NOLOCK)
        WHERE companyType = @companyType 
        AND companyID = @companyid
        ORDER BY addDate DESC    
    ELSE
        SELECT TOP 10 * 
        FROM (
            SELECT *
            FROM Contact WITH(NOLOCK)
            WHERE companyID = @companyid
            UNION
            SELECT Id, Title, @CompanyId CompanyId, '冷客户：' + Description Description,
                BeginTime, 2 Status, AddDate, AddMan Manageruserid, AddMan Creator,
                NULL Quality, NULL CloseDate, 1 CompanyType, LinkMan, TelePhone, NULL Label
            FROM coldcontact 
            WHERE CustomerId IN(SELECT Id FROM ColdCustomer WHERE cpMainId = @CompanyId)
        ) a
        ORDER BY addDate DESC    
END
GO

/****** Object:  StoredProcedure [dbo].[bpContactByStatisticSelect]    Script Date: 2018/12/13 19:03:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--创建人 Andy
--时间 2015-01-08
--按照条件统计后台客户联系活动
CREATE PROCEDURE [dbo].[bpContactByStatisticSelect]
(		
	@StartTime DATETIME,
	@EndTime DATETIME,	
	@CurManagerUserID INT,--当前登录用户ID		
	@Privi INT--当前用户的权限
)
AS
BEGIN
	DECLARE @t AS TABLE(ManagerUserID int, Status INT, Cnt INT)
	INSERT INTO @t
	SELECT a.ManagerUserID, a.Status, COUNT(*) Cnt	
	FROM Contact a WITH(NOLOCK)
	WHERE a.CompanyId > 0
	AND a.BeginTime >= @StartTime
	AND a.BeginTime <= @EndTime
	AND a.ManagerUserID IN (SELECT ID FROM dbo.LowerUser(@CurManagerUserID, @Privi)) 		
	GROUP BY a.ManagerUserID, a.Status
 
	SELECT DISTINCT ManagerUserID,
		ISNULL((SELECT Cnt FROM @t WHERE ManagerUserID = a.ManagerUserID AND status = 2), 0) AS Finished, 
		ISNULL((SELECT Cnt FROM @t WHERE ManagerUserID =a .ManagerUserID AND status = 1), 0) AS NotFinished 
	FROM @t a
	ORDER BY a.ManagerUserID
END
GO

/****** Object:  StoredProcedure [dbo].[bpContactInsert]    Script Date: 2018/12/13 19:03:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--创建人 Andy
--时间 2015-01-09
--插入一条后台沟通记录
--@CompanyID，0表示没有companyID
--修改：删除参数CloseDate，根据Status来判断CloseDate
CREATE PROCEDURE [dbo].[bpContactInsert]
(
	@Title NVARCHAR(50),	
	@CompanyID INT,
	@Description NVARCHAR(400),
	@Begintime DATETIME,	
	@Status	INT,
	@ManagerUserId INT,
	@Creator INT,
	@Quality INT,	
	@CompanyType INT,
	@LinkMan INT,
	@Telephone nvarchar(50)	
)
AS
BEGIN	
	INSERT INTO Contact(Title, Companyid, Description, BeginTime, Status, ManagerUserId, Creator, Quality, CloseDate, LinkMan, Telephone) 
	VALUES (@Title,	(CASE WHEN @Companyid = 0 THEN NULL ELSE @Companyid END), @Description, @BeginTime, @Status, @ManagerUserId, @Creator, @Quality, (CASE WHEN @Status = 2 THEN GETDATE() ELSE NULL END), @LinkMan, @Telephone)		
	RETURN 1
END
GO

/****** Object:  StoredProcedure [dbo].[bpContactSelect]    Script Date: 2018/12/13 19:03:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--创建人 Andy
--时间 2015-01-08
--按照条件查询后台客户联系活动
create PROCEDURE [dbo].[bpContactSelect]
(		
	@StartTime DATETIME,
	@EndTime DATETIME,
	@ManagerUserID INT,
	@CompanyID INT,
	@Status INT,--完成状态，1：未完成, 2完成
	@CurManagerUserID INT,--当前登录用户ID		
	@Privi INT--当前用户的权限
)
AS
BEGIN	
	DECLARE @SQL VARCHAR(1000)
	SET @SQL = '
		SELECT a.*, b.Name, c.Name CompanyName
		FROM Contact a WITH(NOLOCK), ManagerUser b WITH(NOLOCK), cpMain c WITH(NOLOCK)
		WHERE a.CompanyId = c.ID AND a.ManagerUserId = b.Id  
		AND a.BeginTime >= ''' + CONVERT(VARCHAR(20),@StartTime, 20) + ''' 
		AND a.BeginTime <= ''' + CONVERT(VARCHAR(20),@EndTime, 20) + ''''
	
	IF @ManagerUserID > 0
		SET @SQL = @SQL + ' AND a.ManagerUserId = ' + LTRIM(STR(@ManagerUserID))	
	IF @CompanyID > 0
		SET @SQL = @SQL + ' AND companyid = ' + LTRIM(STR(@CompanyID))	
	IF @Status = 1 
		SET @SQL = @SQL + ' AND a.Status = 1 '
	ELSE IF @Status = 2
		SET @SQL = @SQL + ' AND a.Status = 2 '		
	--权限
	SET @SQL = @SQL + ' AND a.ManagerUserId IN (
		SELECT ID FROM dbo.LowerUser(' + LTRIM(STR(@CurManagerUserID)) + ', ' + LTRIM(STR(@Privi)) + '))
		ORDER BY BeginTime'
	--PRINT(@SQL)
	EXEC (@SQL)
END
GO

/****** Object:  StoredProcedure [dbo].[bpCpActivationLogByCountDateSelect]    Script Date: 2018/12/13 19:03:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--创建人 Andy
--时间 2015-01-23
--按照条件获取企业登录间隔
CREATE PROCEDURE [dbo].[bpCpActivationLogByCountDateSelect] 
(		
	@BeginDate VARCHAR(8),  --格式20140101
	@EndDate VARCHAR(8)		--格式20140101
)
AS
BEGIN		
	SET @BeginDate = dbo.SafeSql(@BeginDate)
	SET @EndDate = dbo.SafeSql(@EndDate)
	
	DECLARE @SQL VARCHAR(1000)	
	SET @SQL = 'SELECT TOP 2000 * FROM LogDb..CpActivationLog Where 1=1 '		
	
	IF LEN(@BeginDate) > 0
		SET @SQL = @SQL + ' And CountDate >= ''' + @BeginDate + ''''		
	IF LEN(@EndDate) > 0
		SET @SQL = @SQL + ' And CountDate <= ''' + @EndDate + ''''			  
	
	--PRINT(@SQL)
	EXEC (@SQL)
END


GO

/****** Object:  StoredProcedure [dbo].[bpCpActivationLogByStatisticsSelect]    Script Date: 2018/12/13 19:03:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--bpCpActivationLogByStatisticsSelect --'20120101','20150101'
--创建人 Andy
--时间 2015-01-23
--按照条件统计企业登录间隔
CREATE PROCEDURE [dbo].[bpCpActivationLogByStatisticsSelect] 
(		
	@BeginDate VARCHAR(8),  --格式20140101
	@EndDate VARCHAR(8)		--格式20140101
)
AS
BEGIN		
	SET @BeginDate = dbo.SafeSql(@BeginDate)
	SET @EndDate = dbo.SafeSql(@EndDate)
	
	DECLARE @SQL VARCHAR(1000)	
	SET @SQL = 'SELECT SUM(day1) day1, SUM(day2) day2, SUM(day3) day3, SUM(day7) day7, SUM(day15) day15, SUM(day30) day30, SUM(dayOther) dayOther 
		FROM LogDb..CpActivationLog 
		Where 1 = 1'		
	
	IF LEN(@BeginDate) > 0
		SET @SQL = @SQL + ' And CountDate >= ''' + @BeginDate + ''''		
	IF LEN(@EndDate) > 0
		SET @SQL = @SQL + ' And CountDate <= ''' + @EndDate + ''''			  
	
	--PRINT(@SQL)
	EXEC (@SQL)
END

GO

/****** Object:  StoredProcedure [dbo].[bpcpAttachmentByCpMainIDSelect]    Script Date: 2018/12/13 19:03:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--根据cpmainid获取单位资料 Lucifer 2014-12-9
CREATE PROCEDURE [dbo].[bpcpAttachmentByCpMainIDSelect]
(
	@cpMainID INT
)
AS
BEGIN
	SELECT TOP 10 * FROM cpAttachment WHERE cpMainID=@cpMainID
END

GO

/****** Object:  StoredProcedure [dbo].[bpcpAttachmentInsert]    Script Date: 2018/12/13 19:03:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--添加单位资料 Lucifer 2014-12-9
CREATE PROCEDURE [dbo].[bpcpAttachmentInsert]
(
	@cpMainID INT,
	@FileName VARCHAR(50),
	@AddMan SMALLINT
)
AS
BEGIN
	INSERT INTO cpAttachment(cpMainID,FileName,AddMan) VALUES(@cpMainID,@FileName,@AddMan)
END

GO

/****** Object:  StoredProcedure [dbo].[bpCpCoinByCaOrderInsert]    Script Date: 2018/12/13 19:03:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--订单积分添加 Lucifer 2014-12-24
CREATE PROCEDURE [dbo].[bpCpCoinByCaOrderInsert]
(
	@OrderID INT,
	@cpMainID INT,
	@OrderType TINYINT
)
AS
SET NOCOUNT ON
BEGIN
	DECLARE @CoinRation NUMERIC(18, 2), @Paid NUMERIC(18, 2), @GivePoint INT
	IF @OrderType = 1
	BEGIN
		SELECT TOP 1 @CoinRation = a.CoinRation, @Paid = c.Paid, @GivePoint = b.GivePoint
		FROM dcVip a, dcVipdetail b, caOrder c
		WHERE c.dcFeeDetailID = b.ID AND b.dcVipID=a.ID AND c.ID = @OrderID
	END
	ELSE
	BEGIN
		SELECT TOP 1 @CoinRation = a.CoinRation, @Paid = c.Paid
		FROM dcFee a,dcFeedetail b,caOrder c
		WHERE c.dcFeeDetailID = b.id AND b.dcFeeID=a.ID AND c.ID = @OrderID
	END
	IF NOT EXISTS(SELECT 'X' FROM cpCoin WHERE caOrderID = @OrderID AND Coin > 0)
	BEGIN
		INSERT INTO cpCoin(cpMainID, caOrderID, Coin, AddDate)
		VALUES(@cpMainID, @OrderID, @CoinRation * @Paid, GETDATE())
	END

	IF @GivePoint > 0
	BEGIN
		INSERT INTO cpPoint (cpMainID, changeID, Point, relativeID, lastModifyDate)
		SELECT @cpMainID, 23, @GivePoint, @OrderID, GETDATE()

		UPDATE caOrder SET GivePoint = @GivePoint WHERE Id = @OrderId
	END
END

GO

/****** Object:  StoredProcedure [dbo].[bpCpCompanyEmailUpdateByVerify]    Script Date: 2018/12/13 19:03:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--企业邮箱审核
CREATE PROCEDURE [dbo].[bpCpCompanyEmailUpdateByVerify]
(
    @cpCompanyEmailID INT,
    @CheckResult INT,
    @CheckMan INT,
	@CheckMessage VARCHAR(100)
)
AS
BEGIN
    DECLARE @cpCertificationID INT
    SELECT TOP 1 @cpCertificationID = cpCertificationID 
    FROM cpCompanyEmail WITH(NOLOCK) 
    WHERE ID = @cpCompanyEmailID
    IF @cpCertificationID IS NULL
        RETURN

    UPDATE cpCompanyEmail 
    SET CheckResult = @CheckResult,CheckMan = @CheckMan,CheckDate = GETDATE(),CheckMessage = @CheckMessage
    WHERE ID = @cpCompanyEmailID
    
    IF @CheckResult = 1
    BEGIN
        UPDATE MainDB..cpCertification 
        SET CerStatus = 1
        WHERE ID = @cpCertificationID
    END
    ELSE
    BEGIN
        INSERT INTO SmsMessageLog (SendMan, caMainId, Mobile, Msg, MsgType, IsCat)
        SELECT TOP 1 @CheckMan, b.ID, a.Mobile, '您的企业邮箱认证未通过，请登录' + d.WebSiteName + '(www.' + d.ProvinceDomain + ')进行查看', 50, 0
        FROM cpCertification a WITH(NOLOCK)
        INNER JOIN caMain b WITH(NOLOCK) ON a.cpMainID = b.cpMainID
        INNER JOIN cpMain c WITH(NOLOCK) ON b.cpMainID = c.ID
        INNER JOIN dcProvince d WITH(NOLOCK) ON c.dcProvinceID = d.ID
        WHERE a.ID = @cpCertificationID AND b.AccountType = 1

		INSERT INTO rtxnotifylog
		SELECT ISNULL(ConsultantID,0),'邮箱审核不通过提醒',Name+'('+LTRIM(STR(ID))+')企业邮箱审核未通过！http://sysback.51rc.com/newoa/company/cp/cpMainInfo?id=' + LTRIM(STR(ID)) + '&code=' + LTRIM(STR(DATEPART(HOUR, RegDate) * 100 + DATEPART(MINUTE, RegDate))),1,0,0,null,getdate()
		FROM cpMain WITH(NOLOCK)
		WHERE ID = (SELECT CpMainID FROM MainDB..cpCertification WITH(NOLOCK) WHERE id = @cpCertificationID)
    END
END
GO

/****** Object:  StoredProcedure [dbo].[bpCpImageByCpIDSelect]    Script Date: 2018/12/13 19:03:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Mice 123>
-- Create date: <2015-1-19>
-- Description:	<企业图片查询>
-- =============================================
CREATE PROCEDURE [dbo].[bpCpImageByCpIDSelect]
(
	@CpMainID VARCHAR(50),
	@ImgType INT --3企业环境照片
)	

AS
BEGIN
	SELECT TOP 20 a.*, b.Name AS cpName 
	FROM cpImage a, cpMain b
	WHERE a.cpMainID = b.id 
	AND a.cpMainID = @CpMainID
	AND a.ImgType = CONVERT(VARCHAR(2),@ImgType)
END

GO

/****** Object:  StoredProcedure [dbo].[bpcpImageByEnviUpdate]    Script Date: 2018/12/13 19:03:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[bpcpImageByEnviUpdate]
(
	@ID INT,
	@cpMainID INT,
	@HasPassed BIT,
	@CheckMan INT,
	@CheckMessage VARCHAR(500),
	@imgType varchar(50)
)
AS
SET NOCOUNT ON
BEGIN
	--插入审核延误记录表 
	INSERT INTO logdb..VerifyDelayLog(verifyType, relationId, submitDate, verifyDate, verifyMan)
	SELECT 6,@ID,(SELECT AddDate FROM cpImage  WHERE cpMainID=@cpMainID AND ID = @ID), GETDATE(), @CheckMan
	
	IF @HasPassed = 1 
	BEGIN
		UPDATE Maindb..cpImage
		SET HasPassed = @HasPassed,
			CheckMan = @CheckMan ,
			Description = @CheckMessage,
			CheckDate = GETDATE()
		WHERE cpMainID=@cpMainID  AND ID=@ID
	END
	ELSE
	BEGIN
		UPDATE Maindb..cpImage
		SET HasPassed = @HasPassed,
			CheckMan = @CheckMan ,
			CheckMessage = @CheckMessage,
			CheckDate = GETDATE()
		WHERE cpMainID=@cpMainID  AND ID=@ID
	END

	if @HasPassed = 1      ---审核通过
	BEGIN
		if @imgType='3'
			INSERT INTO cpPoint(cpMainID, ChangeID, Point, LastModifyDate) VALUES(@cpMainID, 25, 10, GETDATE())
		else if @imgType='4,5'
			INSERT INTO cpPoint(cpMainID, ChangeID, Point, LastModifyDate) VALUES(@cpMainID, 26, 100, GETDATE())
	END
END
GO

/****** Object:  StoredProcedure [dbo].[bpCpImageByFileNameUpdate]    Script Date: 2018/12/13 19:03:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Mice 123>
-- Create date: <2015-1-19>
-- Description:	<企业环境照片更新>
---增加审核记录,用于计算审核延误6
-- =============================================
CREATE PROCEDURE [dbo].[bpCpImageByFileNameUpdate]
(
	@ImgID VARCHAR(50),
	@FileName VARCHAR(100),
	@PhotoDesc NVARCHAR(100),
	@ManageruserID INT
)
AS
SET NOCOUNT ON
BEGIN
	UPDATE Maindb..cpImage
	SET ImgFile = @FileName, AddDate = getDate() , 
		HasPassed = 1, CheckMan = @ManageruserID, 
		CheckDate = GETDATE(), CheckMessage = NULL,
		Description = @PhotoDesc
	WHERE ID = @ImgID
	
	  --插入审核延误记录表 
	INSERT INTO logdb..VerifyDelayLog(verifyType, relationId, submitDate, verifyDate, verifyMan)
	SELECT 6,@ImgID,(SELECT AddDate FROM cpImage  WHERE   ID = @ImgID), GETDATE(), @ManageruserID
	
END 
GO

/****** Object:  StoredProcedure [dbo].[bpCpImageByHasVisualSelect]    Script Date: 2018/12/13 19:03:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--###########################
--创建时间：2014.11.27
--创建人：Nick
--说明：获取后台审核形象图
--###########################
CREATE PROCEDURE [dbo].[bpCpImageByHasVisualSelect]
(
	@WHERE VARCHAR(2000),
	@TypeID INT --1等待审核、2最近审核通过 3 最近审核未通过
)
AS
BEGIN 
	DECLARE @SQL VARCHAR(MAX)
	IF @TypeID = 1
	BEGIN
		SET @SQL = '
			SELECT a.ID ImageID,a.AddDate,b.ID,b.Name companyname,b.RegDate 
			FROM cpImage a With(Nolock),cpMain b With(Nolock) 
			WHERE a.cpMainID=b.ID 
			AND a.HasPassed Is Null 
			AND a.AddDate<DateAdd(mi, -40, GetDate()) 
			AND a.ImgType=2 
			AND b.IsDelete=0 
			AND b.IsLimitLogin<>9 
			AND a.adddate < CONVERT(datetime, CONVERT(varchar(100), GETDATE(), 111)+'' 16:51'')
		'+@WHERE + ' Order By a.AddDate'
	END
	ELSE IF @TypeID = 2
	BEGIN
		SET @SQL = '
			SELECT a.ID imageid,a.CheckDate,b.id,b.Name companyname,a.ImgFile,b.RegDate,a.CheckMan 
			FROM cpImage a With(Nolock),cpMain b With(Nolock) 
			WHERE ImgType=2 
			AND a.cpMainID=b.ID 
			AND b.HasVisual=1 
			AND b.Isdelete<>1 
			AND HasPassed=1
		'+@WHERE + ' ORDER BY a.CheckDate DESC'
	END
	ELSE IF @TypeID = 3
	BEGIN
		SET @SQL = '
			SELECT TOP 100 a.ID ImageID,b.ID,b.Name,a.CheckDate,CheckMessage,ImgFile,CheckMan,b.RegDate 
			FROM cpImage a With(Nolock),cpMain b With(Nolock) 
			WHERE a.cpMainID=b.ID 
			AND b.HasVisual=0 
			AND a.ImgType=2 
			AND HasPassed=0 
			AND b.Isdelete<>1
		'+@WHERE + ' Order By a.CheckDate Desc'
	END
	EXEC(@SQL)
END
GO

/****** Object:  StoredProcedure [dbo].[bpCpImageByIDUpdate]    Script Date: 2018/12/13 19:03:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--Peter 2014-08-25 修改
--审核通过Logo和形象图  添加积分记录
CREATE PROCEDURE [dbo].[bpCpImageByIDUpdate]
(
	@ID INT,
	@cpMainID INT,
	@ImgType INT,
	@HasPassed BIT,
	@CheckMan INT,
	@CheckMessage VARCHAR(500)
)
AS
BEGIN TRY
	UPDATE cpImage 
	SET HasPassed = @HasPassed,
		CheckMan = @CheckMan ,
		CheckMessage = @CheckMessage,
		CheckDate = GETDATE()
	WHERE cpMainID=@cpMainID AND ImgType = @ImgType AND ID=@ID

	IF @ImgType = 1
	BEGIN
		UPDATE cpMain SET HasLogo = @HasPassed WHERE ID = @cpMainID

		IF @HasPassed = 1
			IF NOT EXISTS (SELECT 'x' FROM cpMain WHERE ID = @cpMainID AND HasLogo = 1) --之前没有审核通过的形象图
				IF NOT EXISTS(SELECT 'x' FROM cpPoint WHERE cpMainID = @cpMainID AND ChangeId = 7 AND Point = 1000)
					INSERT INTO cpPoint(cpMainID, ChangeID, Point, LastModifyDate)	--插入积分记录
					VALUES(@cpMainID, 7, CASE WHEN GETDATE() < '2017-4-26' THEN 1000 ELSE 10 END, GETDATE())
	END			
	ELSE IF @ImgType = 2
	BEGIN
		IF @HasPassed = 1
			IF NOT EXISTS (SELECT 'x' FROM cpMain WHERE ID = @cpMainID AND HasVisual = 1) --之前没有审核通过的形象图
				INSERT INTO cpPoint (cpMainID, ChangeID, Point, LastModifyDate)		--插入积分记录
				VALUES(@cpMainID, 9, 10, GETDATE())

		UPDATE cpMain SET HasVisual = @HasPassed WHERE ID = @cpMainID
		IF EXISTS(SELECT 'X' FROM cpImage WHERE cpMainID=@cpMainID AND HasPassed=1 AND ImgType=@ImgType)
			UPDATE cpMain SET HasVisual = 1 WHERE ID = @cpMainID
	END

	--短信发送
	IF @ImgType = 1
		INSERT SmsMessageLog(SendMan, ManagerUSerId, paMainId, caMainId, Mobile, MsgType, IsCat, Msg)
		SELECT  @CheckMan, NULL, NULL, a.ID, a.Mobile,
			CASE @HasPassed 
				WHEN 1 THEN 2
				ELSE 3 
			END
			,0,
			CASE @HasPassed 
				WHEN 1 THEN
					IsNULL(a.Name,'') + '您好，您在' + c.subsitename + c.EmailSite + '上传的logo已通过审核，祝您招聘顺利'
				ELSE
					IsNULL(a.Name,'') + '您好，您在' + c.subsitename + c.EmailSite + '上传的logo未通过审核，请您及时登录网站修改，以免耽误使用'
			END	
		FROM caMain a WITH(NOLOCK), cpMain b WITH(NOLOCK), dcSubSite c WITH(NOLOCK)
		WHERE a.cpMainID = b.ID
		AND b.dcSubSiteID = c.ID
		AND a.AccountType = 1
		AND a.Mobile > ''
		AND b.ID = @cpMainID
		AND a.IsReceiveSms = 1		
	ELSE
		INSERT SmsMessageLog(SendMan, ManagerUSerId, paMainId, caMainId, Mobile, MsgType, IsCat, Msg)
		SELECT  @CheckMan, NULL, NULL, a.ID, a.Mobile, 
			CASE @HasPassed 
				WHEN 1 THEN 4
				ELSE 5
			END
			 ,0,
			CASE @HasPassed 
				WHEN 1 THEN
					IsNULL(a.Name,'') + '您好，您在' + c.subsitename + c.EmailSite + '上传的形象图已通过审核，祝您招聘顺利'
				ELSE
					IsNULL(a.Name,'') + '您好，您在' + c.subsitename + c.EmailSite + '上传的形象图未通过审核，请您及时登录网站修改，以免耽误使用'
			END
		FROM caMain a WITH(NOLOCK), cpMain b WITH(NOLOCK), dcSubSite c WITH(NOLOCK)
		WHERE a.cpMainID = b.ID
		AND b.dcSubSiteID = c.ID
		AND a.AccountType = 1
		AND a.Mobile > ''
		AND b.ID = @cpMainID
		AND a.IsReceiveSms = 1			
END TRY
BEGIN CATCH
	GOTO ERR
END CATCH
RETURN 1
ERR:
BEGIN
	RETURN 0
END